MySql (Pomelo)

PhilippeChessa
PhilippeChessa
in General

Hi,

I have installed the latest version of LinqPad 8 (8.3.7).
Now none of my MySql connections work anymore. I suspect the problem comes from the new MySql (Pomelo) driver.

I can still use these same connections with LinqPad 5.

Any idea how to fix this problem?

Thanks in advance
Philippe

· Share on FacebookShare on Twitter

Comments

  • JoeAlbahari
    JoeAlbahari

    The scaffolder hasn't really changed recently - in fact, it's hardly changed since LINQPad 5. Can you provide any more clues? Do you have any weird characters in your table names?

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    Hi,
    Thanks for the answer.
    Here is the CREATE statements for my table:
    Why dit it worked with the previous version and in LinqPad 5?

    DROP TABLE IF EXISTS dvds.dvds;
    CREATE TABLE dvds.dvds (
    Code int(11) NOT NULL,
    OriginalTitle varchar(128) DEFAULT NULL,
    FrenchTitle varchar(128) DEFAULT NULL,
    Code_Director int(11) DEFAULT NULL,
    Code_Genre varchar(2) DEFAULT NULL,
    Published int(11) DEFAULT NULL,
    Note int(11) DEFAULT NULL,
    Bought datetime DEFAULT NULL,
    Search datetime DEFAULT NULL,
    Price int(11) DEFAULT NULL,
    Count int(11) DEFAULT NULL,
    Zone int(11) DEFAULT NULL,
    Rank int(11) DEFAULT NULL,
    Code_Category int(11) DEFAULT NULL,
    Comment varchar(128) DEFAULT NULL,
    IMDB_ID int(11) DEFAULT NULL,
    Code_Visions int(11) DEFAULT NULL,
    Image varchar(255) DEFAULT NULL,
    Bluray tinyint(1) unsigned NOT NULL DEFAULT '0',
    FirstOfSerie tinyint(1) unsigned NOT NULL DEFAULT '0',
    Viewed tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (Code),
    KEY Code_Director (Code_Director),
    KEY Code_Genre (Code_Genre),
    KEY Code_Category (Code_Category),
    CONSTRAINT dvds_ibfk_1 FOREIGN KEY (Code_Director) REFERENCES directors (Code),
    CONSTRAINT dvds_ibfk_2 FOREIGN KEY (Code_Genre) REFERENCES genres (Code),
    CONSTRAINT dvds_ibfk_3 FOREIGN KEY (Code_Category) REFERENCES categories (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    Regards
    Philippe

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    This works for me... can you provide a complete repro?

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    Can it be that I am not runnning (yet) a registered version ?
    I can send you a backup of my database (created with mysqldump - 950k in zip), but I am not too keen of loading it on the forum.
    How can I proceed differently?
    For what it worth I join my connection property...

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    It will make no difference whether or not you're running a registered version.

    Can you script the entire schema?

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    Here they are :

    /* Table CATEGORIES */
    DROP TABLE IF EXISTS dvds.categories;
    CREATE TABLE dvds.categories (
    Code int(11) NOT NULL,
    Libelle varchar(64) DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    /* Table DIRECTORS */
    DROP TABLE IF EXISTS dvds.directors;
    CREATE TABLE dvds.directors (
    Code int(11) NOT NULL,
    FirstName varchar(32) DEFAULT NULL,
    LastName varchar(32) DEFAULT NULL,
    IMDB_ID int(11) DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    /* Table DVDS */
    DROP TABLE IF EXISTS dvds.dvds;
    CREATE TABLE dvds.dvds (
    Code int(11) NOT NULL,
    OriginalTitle varchar(128) DEFAULT NULL,
    FrenchTitle varchar(128) DEFAULT NULL,
    Code_Director int(11) DEFAULT NULL,
    Code_Genre varchar(2) DEFAULT NULL,
    Published int(11) DEFAULT NULL,
    Note int(11) DEFAULT NULL,
    Bought datetime DEFAULT NULL,
    Search datetime DEFAULT NULL,
    Price int(11) DEFAULT NULL,
    Count int(11) DEFAULT NULL,
    Zone int(11) DEFAULT NULL,
    Rank int(11) DEFAULT NULL,
    Code_Category int(11) DEFAULT NULL,
    Comment varchar(128) DEFAULT NULL,
    IMDB_ID int(11) DEFAULT NULL,
    Code_Visions int(11) DEFAULT NULL,
    Image varchar(255) DEFAULT NULL,
    Bluray tinyint(1) unsigned NOT NULL DEFAULT '0',
    FirstOfSerie tinyint(1) unsigned NOT NULL DEFAULT '0',
    Viewed tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (Code),
    KEY Code_Director (Code_Director),
    KEY Code_Genre (Code_Genre),
    KEY Code_Category (Code_Category),
    CONSTRAINT dvds_ibfk_1 FOREIGN KEY (Code_Director) REFERENCES directors (Code),
    CONSTRAINT dvds_ibfk_2 FOREIGN KEY (Code_Genre) REFERENCES genres (Code),
    CONSTRAINT dvds_ibfk_3 FOREIGN KEY (Code_Category) REFERENCES categories (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    /* Table GENRES */
    DROP TABLE IF EXISTS dvds.genres;
    CREATE TABLE dvds.genres (
    Code varchar(2) NOT NULL,
    Libelle varchar(64) DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    /* TAble VISIONS */
    DROP TABLE IF EXISTS dvds.visions;
    CREATE TABLE dvds.visions (
    Code int(11) NOT NULL,
    Code_Dvd int(11) DEFAULT NULL,
    Date datetime DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    Still can't reproduce. Can you please run the following script:
    https://share.linqpad.net/ul5gejmp.linq

    This reproduces what LINQPad's scaffolder does at the point when it errors, and will allow you to isolate the problem. Let me know the result.

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    I run it. Still the same error message ...
    Hope it will be helpful

    Unable to execute the following command
    SHOW CREATE TABLE categories;
    SHOW CREATE TABLE directors;
    SHOW CREATE TABLE dvds;
    SHOW CREATE TABLE genres;
    SHOW CREATE TABLE visions
    ex.Message
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW CREATE TABLE directors;
    SHOW CREATE TABLE dvds;
    SHOW CREATE TABLE `ge' at line 2
    ex
    MySqlException•••
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW CREATE TAB...

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    That's strange, because the SQL in question looks perfectly valid. Do you have any other tool that lets you execute MySql queries? Try running that SQL, i.e.:

    SHOW CREATE TABLE `categories`;
SHOW CREATE TABLE `directors`;
SHOW CREATE TABLE `dvds`;
SHOW CREATE TABLE `genres`;
SHOW CREATE TABLE `visions`
    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    I used MySqlQuery Browser.
    It works fine ... Here are the results

    CREATE TABLE categories (
    Code int(11) NOT NULL,
    Libelle varchar(64) DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    CREATE TABLE directors (
    Code int(11) NOT NULL,
    FirstName varchar(32) DEFAULT NULL,
    LastName varchar(32) DEFAULT NULL,
    IMDB_ID int(11) DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    CREATE TABLE dvds (
    Code int(11) NOT NULL,
    OriginalTitle varchar(128) DEFAULT NULL,
    FrenchTitle varchar(128) DEFAULT NULL,
    Code_Director int(11) DEFAULT NULL,
    Code_Genre varchar(2) DEFAULT NULL,
    Published int(11) DEFAULT NULL,
    Note int(11) DEFAULT NULL,
    Bought datetime DEFAULT NULL,
    Search datetime DEFAULT NULL,
    Price int(11) DEFAULT NULL,
    Count int(11) DEFAULT NULL,
    Zone int(11) DEFAULT NULL,
    Rank int(11) DEFAULT NULL,
    Code_Category int(11) DEFAULT NULL,
    Comment varchar(128) DEFAULT NULL,
    IMDB_ID int(11) DEFAULT NULL,
    Code_Visions int(11) DEFAULT NULL,
    Image varchar(255) DEFAULT NULL,
    Bluray tinyint(1) unsigned NOT NULL DEFAULT '0',
    FirstOfSerie tinyint(1) unsigned NOT NULL DEFAULT '0',
    Viewed tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (Code),
    KEY Code_Director (Code_Director),
    KEY Code_Genre (Code_Genre),
    KEY Code_Category (Code_Category),
    CONSTRAINT dvds_ibfk_1 FOREIGN KEY (Code_Director) REFERENCES directors (Code),
    CONSTRAINT dvds_ibfk_2 FOREIGN KEY (Code_Genre) REFERENCES genres (Code),
    CONSTRAINT dvds_ibfk_3 FOREIGN KEY (Code_Category) REFERENCES categories (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    CREATE TABLE genres (
    Code varchar(2) NOT NULL,
    Libelle varchar(64) DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    CREATE TABLE visions (
    Code int(11) NOT NULL,
    Code_Dvd int(11) DEFAULT NULL,
    Date datetime DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    May be there is something wrong in my database ?

    For what it worth, I am using Windows 10 (french) :
    Édition Windows 10 Entreprise
    Version 22H2
    Installé le ‎06/‎03/‎2023
    Build du système d’exploitation 19045.4291
    Expérience Windows Feature Experience Pack 1000.19056.1000.0

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    If you execute those CREATE TABLE statements into a new empty database, can you then query that database with LINQPad?

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    Hi,

    I created a new database DVDS2.

    I created a new table CATEGORIES.

    DROP TABLE IF EXISTS dvds2.categories;
    CREATE TABLE dvds2.categories (
    Code int(11) NOT NULL,
    Libelle varchar(64) DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    I tried the connection. It worked fine.

    I added a new table DIRECTORS

    DROP TABLE IF EXISTS dvds2.directors;
    CREATE TABLE dvds2.directors (
    Code int(11) NOT NULL,
    FirstName varchar(32) DEFAULT NULL,
    LastName varchar(32) DEFAULT NULL,
    IMDB_ID int(11) DEFAULT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    Then, I tried the connection, and it failed.

    Can't I just use the same driver as in LinqPad 5 ?

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    I've changed the delimiters in 8.4.3 - let me know whether it makes any difference.
    https://www.linqpad.net/linqpad8.aspx#beta

    I'm kind-of guessing here, because I can't reproduce the error with the same schema. It doesn't help that MySQL's error messages are screwed up - it completely misrepresents the location of the problem.

    You cannot use LINQPad 5's driver because it has dependencies that support only .NET Framework.

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    I have installed the beta version.

    Unfortunately, I have the same error...

    If it makes any sense, can I run the code executed inside LinqPad 8 while opening a connection, in a regular C# program in Visual Studio?

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    Are you a programmer? If so, could you try playing with the query I sent you, i.e.: https://share.linqpad.net/ul5gejmp.linq

    By editing the script, you should be able to isolate the problem. You could try things such as having each SHOW TABLE command execute separately instead of in a batch, and isolate which table causes the issue. It's difficult for me to do this kind of debugging remotely.

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    It's hopeless!

    I run your script inside VisualStudio 2022. I slightly change the code :
    ...
    ```
    foreach (var table in tableNames)
    {
    cmd = cx.CreateCommand();
    //cmd.CommandText = string.Join(";\r\n", tables.ToArray());
    cmd.CommandText = table;
    Console.WriteLine($"Testing table {table}");
    try
    {
    using (var reader = cmd.ExecuteReader())
    {
    Console.WriteLine(reader.ToString());
    }
    }
    catch (Exception ex)
    {
    Console.WriteLine(cmd.CommandText); //.Dump("Unable to execute the following command");
    Console.WriteLine(ex.Message); //.Dump();
    //ex.Dump();
    }
    Console.WriteLine("=====");
    }
    ...
    Same error message, No additional information ...

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    Try

    cmd.CommandText = "SHOW CREATE TABLE `" + table + "`";

    instead of

    cmd.CommandText = table;
    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    That's better :

        cmd = cx.CreateCommand();
    //cmd.CommandText = string.Join(";\r\n", tables.ToArray());
    //cmd.CommandText = table;
    cmd.CommandText = "SHOW CREATE TABLE `" + table + "`";
    Console.WriteLine($"Testing table {table}");
    try
    {
        using (var reader = cmd.ExecuteReader())
        {
            Console.WriteLine(reader.ToString());
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(cmd.CommandText); //.Dump("Unable to execute the following command");
        Console.WriteLine(ex.Message); //.Dump();
                                       //ex.Dump();
    }

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    This looks like a localization bug in the MySQL connector .NET library that prevents batching when a French locale is selected. The reason that it works in LINQPad 5 is that the latter uses the DevArt library instead of the MySQL connector library.

    Try 8.4.5 - it includes a fallback in case batching fails:
    https://www.linqpad.net/linqpad8.aspx#beta

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    Alas, the problem is still there ...
    (Artists and Dvds are two different databases with different tables, thus the problem doesn't not seem to be related to a specific database)


    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    Are you giving up ?

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    Sorry - I don't think the fix made 8.4.5. Try 8.4.7 and let me know (LINQPad will automatically update).

    · Share on FacebookShare on Twitter
  • PhilippeChessa
    PhilippeChessa

    It works! Thank you very much for your time

    · Share on FacebookShare on Twitter

Categories