MySql (Pomelo)

PhilippeChessa · May 6

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

JoeAlbahari · May 7

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?

PhilippeChessa · May 7

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

JoeAlbahari · May 8

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

PhilippeChessa · May 8

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...

JoeAlbahari · May 9

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

Can you script the entire schema?

PhilippeChessa · May 9

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;

JoeAlbahari · May 10

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.

PhilippeChessa · May 10

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...

JoeAlbahari · May 11

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`

PhilippeChessa · May 11

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

JoeAlbahari · May 13

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

PhilippeChessa · May 14

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 ?

JoeAlbahari · May 14

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.

PhilippeChessa · May 15

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?

JoeAlbahari · May 15

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.

PhilippeChessa · May 16

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 ...

JoeAlbahari · May 17

Try

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

instead of

cmd.CommandText = table;

PhilippeChessa · May 17

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();
    }

JoeAlbahari · May 20

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

PhilippeChessa · May 20

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)

PhilippeChessa · May 23

Are you giving up ?

JoeAlbahari · May 24

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

PhilippeChessa · May 24

It works! Thank you very much for your time

MySql (Pomelo)

Comments