MySql (Pomelo)
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
Comments
-
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?
-
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 TABLEdvds
.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
),
KEYCode_Director
(Code_Director
),
KEYCode_Genre
(Code_Genre
),
KEYCode_Category
(Code_Category
),
CONSTRAINTdvds_ibfk_1
FOREIGN KEY (Code_Director
) REFERENCESdirectors
(Code
),
CONSTRAINTdvds_ibfk_2
FOREIGN KEY (Code_Genre
) REFERENCESgenres
(Code
),
CONSTRAINTdvds_ibfk_3
FOREIGN KEY (Code_Category
) REFERENCEScategories
(Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Regards
Philippe -
This works for me... can you provide a complete repro?
-
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... -
It will make no difference whether or not you're running a registered version.
Can you script the entire schema?
-
Here they are :
/* Table CATEGORIES */
DROP TABLE IF EXISTSdvds
.categories
;
CREATE TABLEdvds
.categories
(Code
int(11) NOT NULL,Libelle
varchar(64) DEFAULT NULL,
PRIMARY KEY (Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;/* Table DIRECTORS */
DROP TABLE IF EXISTSdvds
.directors
;
CREATE TABLEdvds
.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 EXISTSdvds
.dvds
;
CREATE TABLEdvds
.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
),
KEYCode_Director
(Code_Director
),
KEYCode_Genre
(Code_Genre
),
KEYCode_Category
(Code_Category
),
CONSTRAINTdvds_ibfk_1
FOREIGN KEY (Code_Director
) REFERENCESdirectors
(Code
),
CONSTRAINTdvds_ibfk_2
FOREIGN KEY (Code_Genre
) REFERENCESgenres
(Code
),
CONSTRAINTdvds_ibfk_3
FOREIGN KEY (Code_Category
) REFERENCEScategories
(Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;/* Table GENRES */
DROP TABLE IF EXISTSdvds
.genres
;
CREATE TABLEdvds
.genres
(Code
varchar(2) NOT NULL,Libelle
varchar(64) DEFAULT NULL,
PRIMARY KEY (Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;/* TAble VISIONS */
DROP TABLE IF EXISTSdvds
.visions
;
CREATE TABLEdvds
.visions
(Code
int(11) NOT NULL,Code_Dvd
int(11) DEFAULT NULL,Date
datetime DEFAULT NULL,
PRIMARY KEY (Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; -
Still can't reproduce. Can you please run the following script:
https://share.linqpad.net/ul5gejmp.linqThis 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.
-
I run it. Still the same error message ...
Hope it will be helpfulUnable to execute the following command
SHOW CREATE TABLEcategories
;
SHOW CREATE TABLEdirectors
;
SHOW CREATE TABLEdvds
;
SHOW CREATE TABLEgenres
;
SHOW CREATE TABLEvisions
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 TABLEdirectors
;
SHOW CREATE TABLEdvds
;
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... -
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`
-
I used MySqlQuery Browser.
It works fine ... Here are the resultsCREATE TABLE
categories
(Code
int(11) NOT NULL,Libelle
varchar(64) DEFAULT NULL,
PRIMARY KEY (Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE 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=latin1CREATE 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
),
KEYCode_Director
(Code_Director
),
KEYCode_Genre
(Code_Genre
),
KEYCode_Category
(Code_Category
),
CONSTRAINTdvds_ibfk_1
FOREIGN KEY (Code_Director
) REFERENCESdirectors
(Code
),
CONSTRAINTdvds_ibfk_2
FOREIGN KEY (Code_Genre
) REFERENCESgenres
(Code
),
CONSTRAINTdvds_ibfk_3
FOREIGN KEY (Code_Category
) REFERENCEScategories
(Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE
genres
(Code
varchar(2) NOT NULL,Libelle
varchar(64) DEFAULT NULL,
PRIMARY KEY (Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE
visions
(Code
int(11) NOT NULL,Code_Dvd
int(11) DEFAULT NULL,Date
datetime DEFAULT NULL,
PRIMARY KEY (Code
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1May 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 -
If you execute those CREATE TABLE statements into a new empty database, can you then query that database with LINQPad?
-
Hi,
I created a new database DVDS2.
I created a new table CATEGORIES.
DROP TABLE IF EXISTS
dvds2
.categories
;
CREATE TABLEdvds2
.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 TABLEdvds2
.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 ?
-
I've changed the delimiters in 8.4.3 - let me know whether it makes any difference.
https://www.linqpad.net/linqpad8.aspx#betaI'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.
-
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?
-
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.
-
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 ... -
Try
cmd.CommandText = "SHOW CREATE TABLE `" + table + "`";
instead of
cmd.CommandText = table;
-
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(); }
-
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 -
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) -
Are you giving up ?
-
Sorry - I don't think the fix made 8.4.5. Try 8.4.7 and let me know (LINQPad will automatically update).
-
It works! Thank you very much for your time