Description:
Hello
We are having serious trouble migrating an application to MySQL Cluster (2 SQL nodes, 2 storage nodes, 1 Mgmt node) from a standard MySQL server. In short, sql queries against the cluster give wildly different results than they do aginst the standard server. I assume this to be a bug.
I have loaded the data from a dump file of the standard server, in which I had replaced the "ENGINE=MyISAM" with "ENGINE=NDB". Further, I had to tweak some indexes, as MySQL Cluster does not support subkeys as primary and does not allow subkeys of blob and text columns in indexes. Most of the tables have blob and text columns.
After modifying the sql file, I first loaded it into a standard mysql server (no ndbcluster definitions in my.cnf), and noticed no difference to the original, i.e. all queries returned the expected, correct results.
Then I loaded the file into a mysql cluster (i.e. copied it to one of the SQL nodes and loaded it with mysql -u root -p < dumpfile). Then logged into the other SQL node and ran "create schema <db name>" for all three databases.
But connecting an existing web application to the cluster showed that some (not all) queries did not return the same data they do when connected to the original host.
The queries follow the scheme: "select column1, column2, ... columnN from table1 where column1=<string>".
Here is a definition for one of the tables in question:
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------------------------------------------------------------+------+-----+-------------------+-------+
| serial | char(255) | NO | PRI | NULL | |
| column1 | char(255) | NO | MUL | NULL | |
| column2 | char(255) | YES | MUL | NULL | |
| column3 | char(14) | NO | | NULL | |
| column4 | char(14) | NO | | NULL | |
| column5 | blob | NO | | NULL | |
| column6 | enum('value1','value2','value3','value4') | NO | MUL | NULL | |
| column7 | char(40) | NO | MUL | NULL | |
| column8 | char(40) | NO | | NULL | |
| column9 | enum('value5','value5','value7','value8','value9') | NO | |
| column10 | char(64) | YES | | NULL | |
| column11 | char(255) | YES | | NULL | |
| column12 | char(14) | YES | | NULL | |
| column13 | text | YES | | NULL | |
| column14 | char(255) | YES | MUL | NULL | |
| column15 | enum('value10','value11','value12')| NO | MUL | NULL | |
| column16 | char(64) | YES | | NULL | |
| column17 | int(11) | YES | | NULL | |
| column18 | char(255) | YES | MUL | NULL | |
| column19 | char(255) | YES | MUL | NULL | |
| timestamp| timestamp | YES | MUL | CURRENT_TIMESTAMP | |
Especially the following cases occur:
1) Queries behave differently depending on upper- or lowercase:
One of our most used queries is for all columns from the above table:
"select serial, column1, column2,....,column19,timestamp from table1 where serial='ABCDEF123456' "
The value in "serial" is stored in uppercase.
Collation is set to
collation_connection | latin1_swedish_ci
collation_database | latin1_swedish_ci
collation_server | latin1_swedish_ci
and
init_connect | SET NAMES latin1
- The query returns correctly if " ...serial='ABCDEF123456' " is in uppercase.
- The query fails if "serial='abcdef123456' " is in lowercase. On the standard server the case was ignored, as I'd expect.
2) Queries behave differently depending on number and type of columns requested
- The query "select serial, column1, column2,....,column19,timestamp from table1 where serial='abcdef123456' " from above fails.
- BUT: it only fails if column5 (the column containing the blob, which is a DER encoded x509 certificate) is selected. If column5 is not requested in the query, i.e. if only character type results are queried, the query succeeds!
I have used the exactly same dumpfile to import the databases into the standard server and cluster. Both servers (Ubuntu 6.06.1 LTS) run the same binary (5.0.22-0ubuntu6.06.2).
my.cnf on both differs only in the following lines:
[mysqld]
ndbcluster
ndb-connectstring="host=<ip of ndb_mgmd>"
[mysql_cluster]
ndb-connectstring="host=<ip of ndb_mgmd>"
Could someone from MySQL please look into this? It is effectively blocking our migration to MySQL Cluster.
How to repeat:
Import existing database containing tables with binary blobs via dumpfile into MySQL Cluster SQL node. Query tables containing those blobs.
Description: Hello We are having serious trouble migrating an application to MySQL Cluster (2 SQL nodes, 2 storage nodes, 1 Mgmt node) from a standard MySQL server. In short, sql queries against the cluster give wildly different results than they do aginst the standard server. I assume this to be a bug. I have loaded the data from a dump file of the standard server, in which I had replaced the "ENGINE=MyISAM" with "ENGINE=NDB". Further, I had to tweak some indexes, as MySQL Cluster does not support subkeys as primary and does not allow subkeys of blob and text columns in indexes. Most of the tables have blob and text columns. After modifying the sql file, I first loaded it into a standard mysql server (no ndbcluster definitions in my.cnf), and noticed no difference to the original, i.e. all queries returned the expected, correct results. Then I loaded the file into a mysql cluster (i.e. copied it to one of the SQL nodes and loaded it with mysql -u root -p < dumpfile). Then logged into the other SQL node and ran "create schema <db name>" for all three databases. But connecting an existing web application to the cluster showed that some (not all) queries did not return the same data they do when connected to the original host. The queries follow the scheme: "select column1, column2, ... columnN from table1 where column1=<string>". Here is a definition for one of the tables in question: | Field | Type | Null | Key | Default | Extra | +----------+-----------------------------------------------------------------------------+------+-----+-------------------+-------+ | serial | char(255) | NO | PRI | NULL | | | column1 | char(255) | NO | MUL | NULL | | | column2 | char(255) | YES | MUL | NULL | | | column3 | char(14) | NO | | NULL | | | column4 | char(14) | NO | | NULL | | | column5 | blob | NO | | NULL | | | column6 | enum('value1','value2','value3','value4') | NO | MUL | NULL | | | column7 | char(40) | NO | MUL | NULL | | | column8 | char(40) | NO | | NULL | | | column9 | enum('value5','value5','value7','value8','value9') | NO | | | column10 | char(64) | YES | | NULL | | | column11 | char(255) | YES | | NULL | | | column12 | char(14) | YES | | NULL | | | column13 | text | YES | | NULL | | | column14 | char(255) | YES | MUL | NULL | | | column15 | enum('value10','value11','value12')| NO | MUL | NULL | | | column16 | char(64) | YES | | NULL | | | column17 | int(11) | YES | | NULL | | | column18 | char(255) | YES | MUL | NULL | | | column19 | char(255) | YES | MUL | NULL | | | timestamp| timestamp | YES | MUL | CURRENT_TIMESTAMP | | Especially the following cases occur: 1) Queries behave differently depending on upper- or lowercase: One of our most used queries is for all columns from the above table: "select serial, column1, column2,....,column19,timestamp from table1 where serial='ABCDEF123456' " The value in "serial" is stored in uppercase. Collation is set to collation_connection | latin1_swedish_ci collation_database | latin1_swedish_ci collation_server | latin1_swedish_ci and init_connect | SET NAMES latin1 - The query returns correctly if " ...serial='ABCDEF123456' " is in uppercase. - The query fails if "serial='abcdef123456' " is in lowercase. On the standard server the case was ignored, as I'd expect. 2) Queries behave differently depending on number and type of columns requested - The query "select serial, column1, column2,....,column19,timestamp from table1 where serial='abcdef123456' " from above fails. - BUT: it only fails if column5 (the column containing the blob, which is a DER encoded x509 certificate) is selected. If column5 is not requested in the query, i.e. if only character type results are queried, the query succeeds! I have used the exactly same dumpfile to import the databases into the standard server and cluster. Both servers (Ubuntu 6.06.1 LTS) run the same binary (5.0.22-0ubuntu6.06.2). my.cnf on both differs only in the following lines: [mysqld] ndbcluster ndb-connectstring="host=<ip of ndb_mgmd>" [mysql_cluster] ndb-connectstring="host=<ip of ndb_mgmd>" Could someone from MySQL please look into this? It is effectively blocking our migration to MySQL Cluster. How to repeat: Import existing database containing tables with binary blobs via dumpfile into MySQL Cluster SQL node. Query tables containing those blobs.