DBD-mysql
DBD-mysql copied to clipboard
Published
20 hours ago •
perl5-dbi
perl5-dbi
t/40keyinfo.t fails when default engine NDB cluster [rt.cpan.org #53845]
trafficstars
Migrated from rt.cpan.org#53845 (status was 'open')
Requestors:
From [email protected] on 2010-01-20 16:17:38:
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
From [email protected] on 2010-01-20 16:49:58:
Looking at the version created when using InnoDB we can see the Sub_part
10 for the column in question
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 | | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
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
From [email protected] on 2010-01-20 17:09:54:
On Wed Jan 20 11:17:38 2010, UNDERMINE wrote:
> 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
From [email protected] on 2010-01-20 17:29:45:
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:
> 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
From [email protected] on 2010-01-20 17:30:06:
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
From [email protected] on 2010-01-20 17:34:57:
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
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
From [email protected] on 2010-01-20 17:40:55:
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.
From [email protected] on 2010-01-20 18:26:36:
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:
> 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.
From [email protected] on 2010-01-21 10:24:33:
From [email protected] on 2010-01-21 10:27:04:
Summary from previous large post for quick reference
On Wed Jan 20 13:26:36 2010, UNDERMINE wrote:
> 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
