Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 53845
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: paddy [...] firedrake.org
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 4.013
Fixed in: (no value)



Subject: t/40keyinfo.t fails when default engine NDB cluster
Tests fail if database uses NDB as the mysql servers default storage engine for tables and force install is required. Failed Test Stat Wstat Total Fail Failed List of Failed ------------------------------------------------------------------------------- t/40keyinfo.t 255 65280 7 10 142.86% 3-7 1 test skipped. Failed 1/36 test scripts, 97.22% okay. 5/745 subtests failed, 99.33% okay. make: *** [test_dynamic] Error 255 /usr/bin/make test -- NOT OK The line that causes the issue is :- CREATE TABLE $table (a int, b varchar(20), c int, primary key (a,b(10)), key (c)); This returns ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys However the following versions do create the table :- CREATE TABLE $table (a int, b varchar(20), c int, primary key (a,b), key (c)); CREATE TABLE $table (a int, b varchar(20), c int, primary key (a,b(10)), key (c)) engine=innodb; So the problem is NDB not supporting partial column primary key indexes. Hope that helps Paddy
Looking at the version created when using InnoDB we can see the Sub_part 10 for the column in question Show quoted text
mysql> show table status;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | $table | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 4194304 | NULL | 2010-01-20 16:41:46 | NULL | NULL | latin1_swedish_ci | NULL | | | +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ Show quoted text
mysql> show indexes from $table;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | $table | 0 | PRIMARY | 1 | a | A | 0 | NULL | NULL | | BTREE | | | $table | 0 | PRIMARY | 2 | b | A | 0 | 10 | NULL | | BTREE | | | $table | 1 | c | 1 | c | A | 0 | NULL | NULL | YES | BTREE | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ But when we look at the returns the test script is expecting we see :- my $sth= $dbh->primary_key_info(undef, undef, $table); ok($sth, "Got primary key info"); my $expect= [ [ undef, undef, $table, 'a', '1', 'PRIMARY' ], [ undef, undef, $table, 'b', '2', 'PRIMARY' ], ]; is_deeply($key_info, $expect, "Check primary_key_info results"); is_deeply([ $dbh->primary_key(undef, undef, $table) ], [ 'a', 'b' ], "Check primary_key results"); I see no point in the test script that tests for use of sub_part in the key. Now is this an omission in the test or a limitation of DBD and if it is a limitation should the table be created using a sub_part in a test? Any thoughts? Paddy
Download (untitled) / with headers
text/plain 502b
On Wed Jan 20 11:17:38 2010, UNDERMINE wrote: Show quoted text
> However the following versions do create the table :- > CREATE TABLE $table (a int, b varchar(20), c int, primary key (a,b), > key > (c)); > > CREATE TABLE $table (a int, b varchar(20), c int, primary key > (a,b(10)), > key (c)) engine=innodb; > > So the problem is NDB not supporting partial column primary key > indexes.
engine=MEMORY and engine=MyISAM are also successful for this creation statement only fails if your default engine is NDB. Paddy
Download (untitled) / with headers
text/plain 2.6k
How to detect the use of NDB cluster as the default engine SHOW VARIABLES has the following values of interest Variable_name Value storage_engine ndbcluster have_ndbcluster YES t/mysql.dbtest does not currently have a section for ndbcluster under sub have_transactions () Basic patch to t/mysql.dbtest may look like the following but not tested it atm so got no idea of cascade effect this has. --- mysql.dbtest 2009-09-15 02:57:53.000000000 +0100 +++ patch/mysql.dbtest 2010-01-20 17:22:32.000000000 +0000 @@ -58,6 +58,11 @@ $have_transactions = "gemini"; last; } + if ($row->{'Variable_name'} eq 'have_ndbcluster' && + $row->{'Value'} eq 'YES') { + $have_transactions = "ndbcluster"; + last; + } } } return $have_transactions; For the initial bug this should be enough : --- 40keyinfo.t 2009-09-15 02:57:53.000000000 +0100 +++ patch/40keyinfo.t 2010-01-20 17:24:38.000000000 +0000 @@ -26,7 +26,7 @@ # Non-primary key is there as a regression test for Bug #26786. ok($dbh->do("CREATE TABLE $table (a int, b varchar(20), c int, - primary key (a,b(10)), key (c))"), + primary key (a,b), key (c))"), "Created table $table"); my $sth= $dbh->primary_key_info(undef, undef, $table); Hope this helps Paddy On Wed Jan 20 11:17:38 2010, UNDERMINE wrote: Show quoted text
> Tests fail if database uses NDB as the mysql servers default storage > engine for tables and force install is required. > > Failed Test Stat Wstat Total Fail Failed List of Failed >
------------------------------------------------------------------------------- Show quoted text
> t/40keyinfo.t 255 65280 7 10 142.86% 3-7 > 1 test skipped. > Failed 1/36 test scripts, 97.22% okay. 5/745 subtests failed, 99.33% > okay. > make: *** [test_dynamic] Error 255 > /usr/bin/make test -- NOT OK > > > The line that causes the issue is :- > CREATE TABLE $table (a int, b varchar(20), c int, primary key > (a,b(10)), > key (c)); > > This returns > ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a > string, the used length is longer than the key part, or the storage > engine doesn't support unique prefix keys > > However the following versions do create the table :- > CREATE TABLE $table (a int, b varchar(20), c int, primary key (a,b), > key > (c)); > > CREATE TABLE $table (a int, b varchar(20), c int, primary key > (a,b(10)), > key (c)) engine=innodb; > > So the problem is NDB not supporting partial column primary key > indexes. > > Hope that helps > > Paddy
Download (untitled) / with headers
text/plain 447b
If a test fails using a particular engine, it has to be an issue with the server. The Perl driver has nothing to do with what engine is being used for the test. I don't have NDB cluster set up to verify this, but I'm 99.88% certain this is not a DBD::mysql issue. I would turn on logging, run the test, capture the SQL the test produces and run it straight through the MySQL client to check. If it's a bug in MySQL, log it at http://bugs.mysql.com
Download (untitled) / with headers
text/plain 737b
On Wed Jan 20 12:30:06 2010, CAPTTOFU wrote: Show quoted text
> If a test fails using a particular engine, it has to be an issue with > the server. The Perl driver has nothing to do with what engine is being > used for the test. I don't have NDB cluster set up to verify this, but > I'm 99.88% certain this is not a DBD::mysql issue. I would turn on > logging, run the test, capture the SQL the test produces and run it > straight through the MySQL client to check. If it's a bug in MySQL, log > it at http://bugs.mysql.com
I would agree that NDB not supporting sub_parts in primary keys is a problem with MySQL NDB cluster engine but should the DBD::mysql driver installation be dependant on a feature that DBD::mysql's tests do not appear use? Paddy
Download (untitled) / with headers
text/plain 714b
On Wed Jan 20 12:30:06 2010, CAPTTOFU wrote: Show quoted text
> If a test fails using a particular engine, it has to be an issue with > the server. The Perl driver has nothing to do with what engine is being > used for the test. I don't have NDB cluster set up to verify this, but > I'm 99.88% certain this is not a DBD::mysql issue. I would turn on > logging, run the test, capture the SQL the test produces and run it > straight through the MySQL client to check. If it's a bug in MySQL, log > it at http://bugs.mysql.com
Ok, thanks for the patch! I'll test it out. I've never dealt with NDB so I'd have to set it up to test. Though, if this patch fixes things for you and all tests pass with it applied, I could make a release.
Download (untitled) / with headers
text/plain 1.2k
NDB Cluster is not your normal engine ;) I'm running with the latest version from mysql.com MySQL Cluster 7.0.9 but you need a mininium of 2 VMs just to install it. Works really nicely when its set up correctly but need bandwidth between the nodes. Will spend some time tomorrow doing some testing and checking everything passes with all test on all the engines I have. Not sure how many tests will pass with blackhole engine but we will try for completeness. Paddy On Wed Jan 20 12:40:55 2010, CAPTTOFU wrote: Show quoted text
> On Wed Jan 20 12:30:06 2010, CAPTTOFU wrote:
> > If a test fails using a particular engine, it has to be an issue with > > the server. The Perl driver has nothing to do with what engine is being > > used for the test. I don't have NDB cluster set up to verify this, but > > I'm 99.88% certain this is not a DBD::mysql issue. I would turn on > > logging, run the test, capture the SQL the test produces and run it > > straight through the MySQL client to check. If it's a bug in MySQL, log > > it at http://bugs.mysql.com
> > Ok, thanks for the patch! I'll test it out. I've never dealt with NDB so > I'd have to set it up to test. Though, if this patch fixes things for > you and all tests pass with it applied, I could make a release.
Download (untitled) / with headers
text/plain 13.2k

Message body is not shown because it is too large.

Download (untitled) / with headers
text/plain 1024b
Summary from previous large post for quick reference On Wed Jan 20 13:26:36 2010, UNDERMINE wrote: Show quoted text
> NDB Cluster is not your normal engine ;) > > I'm running with the latest version from mysql.com MySQL Cluster 7.0.9 > but you need a mininium of 2 VMs just to install it. > > Works really nicely when its set up correctly but need bandwidth between > the nodes. > > Will spend some time tomorrow doing some testing and checking everything > passes with all test on all the engines I have. > > Not sure how many tests will pass with blackhole engine but we will try > for completeness. >
So in summary NDB - Passes (Failed 28/36 when using invalid backed as you would expect) InnoDB - Passes MyISAM - Passes MEMORY - Failed 3/36 BLACKHOLE - Failed 16/36 CSV - Failed 24/36 ARCHIVE - Failed 12/36 Normally you would only consider using NDB, InnoDB, MyISAM, MEMORY and BLACKHOLE as the defaults. It would be nice if MEMORY could be supported but BLACKHOLE is a special case anyway. Hope this covers everything Paddy


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.