Skip Menu |
 

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

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

People
Owner: CAPTTOFU [...] cpan.org
Requestors: atsekhan [...] cox.net
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 4.014
Fixed in: 4.034



Subject: Error in 40server_prepare.t
Download (untitled) / with headers
text/plain 775b
On AIX 5.3 (oslevel - 5300-09-02-0849) with 64-bit PERL 5.8.2 and MySQL Binary distribution 5.1.46 I get this error in make test: t/40server_prepare.t ........ 1/21 # Failed test at t/40server_prepare.t line 67. # Structures begin differing at: # $got->[0][0] = '0' # $expected->[0][0] = '101' # Looks like you failed 1 test of 21. t/40server_prepare.t ........ Dubious, test returned 1 (wstat 256, 0x100) Failed 1/21 subtests Total statistics: Test Summary Report ------------------- t/40server_prepare.t (Wstat: 256 Tests: 21 Failed: 1) Failed test: 20 Non-zero exit status: 1 Files=38, Tests=777, 6 wallclock secs ( 0.37 usr 0.09 sys + 2.45 cusr 0.25 csys = 3.16 CPU) Result: FAIL Failed 1/38 test programs. 1/777 subtests failed.
Thank you for reporting this! I haven't seen this error on Linux, OSX, or other operating systems I have access to. Is there any way I could have access to your machine to investigate this? On Thu May 06 14:40:56 2010, atsekhan wrote: Show quoted text
> On AIX 5.3 (oslevel - 5300-09-02-0849) with 64-bit PERL 5.8.2 and MySQL > Binary distribution 5.1.46 I get this error in make test: > > t/40server_prepare.t ........ 1/21 > # Failed test at t/40server_prepare.t line 67. > # Structures begin differing at: > # $got->[0][0] = '0' > # $expected->[0][0] = '101' > # Looks like you failed 1 test of 21. > t/40server_prepare.t ........ Dubious, test returned 1 (wstat 256, > 0x100) > Failed 1/21 subtests > > Total statistics: > > Test Summary Report > ------------------- > t/40server_prepare.t (Wstat: 256 Tests: 21 Failed: 1) > Failed test: 20 > Non-zero exit status: 1 > Files=38, Tests=777, 6 wallclock secs ( 0.37 usr 0.09 sys + 2.45 > cusr 0.25 csys = 3.16 CPU) > Result: FAIL > Failed 1/38 test programs. 1/777 subtests failed. >
Subject: Re: [rt.cpan.org #57266] Error in 40server_prepare.t
Date: Sun, 16 May 2010 09:38:01 -0400
To: <bug-DBD-mysql [...] rt.cpan.org>
From: "Alex Tsekhansky" <atsekhan [...] cox.net>
Download (untitled) / with headers
text/plain 1.6k
That would be difficult as it's behind firewall, and requires special provisions for access. However, if you want me to run test scripts there, I can do it for you and report results. In a mean time I simply ignored the error and forced the install. I do not have datatypes in my database that cause this, so everything is working fine so far. -------------------------------------------------- From: "Patrick Galbraith via RT" <bug-DBD-mysql@rt.cpan.org> Sent: Sunday, May 16, 2010 8:53 AM To: <atsekhan@cox.net> Subject: [rt.cpan.org #57266] Error in 40server_prepare.t Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=57266 > > > Thank you for reporting this! I haven't seen this error on Linux, OSX, > or other operating systems I have access to. Is there any way I could > have access to your machine to investigate this? > > On Thu May 06 14:40:56 2010, atsekhan wrote:
>> On AIX 5.3 (oslevel - 5300-09-02-0849) with 64-bit PERL 5.8.2 and MySQL >> Binary distribution 5.1.46 I get this error in make test: >> >> t/40server_prepare.t ........ 1/21 >> # Failed test at t/40server_prepare.t line 67. >> # Structures begin differing at: >> # $got->[0][0] = '0' >> # $expected->[0][0] = '101' >> # Looks like you failed 1 test of 21. >> t/40server_prepare.t ........ Dubious, test returned 1 (wstat 256, >> 0x100) >> Failed 1/21 subtests >> >> Total statistics: >> >> Test Summary Report >> ------------------- >> t/40server_prepare.t (Wstat: 256 Tests: 21 Failed: 1) >> Failed test: 20 >> Non-zero exit status: 1 >> Files=38, Tests=777, 6 wallclock secs ( 0.37 usr 0.09 sys + 2.45 >> cusr 0.25 csys = 3.16 CPU) >> Result: FAIL >> Failed 1/38 test programs. 1/777 subtests failed. >>
> > >
resolving this bug.
From: vlmarek [...] volny.cz
Download (untitled) / with headers
text/plain 3.5k
Hi, I'm having the same issue on Sparc, which is big-endian. Moreover I'm compiling 64bit which is probably contributing factor. I have taken some steps in fixing the issue, but I'm nowhere done. I want to post my findings and hope to receive some comments on the approach. I have not tested yet my changes on little-endian machine, so I may be breaking it at the moment :) I'm attaching DBI trace log file from the unchanged sources named 'dbitrace.log.orig'. On line 301 you can see that '101' is being bound. After the test executed the sql table dbd_mysql_t40serverprepare2 contains only four zeros (verified via command line mysql client). -> bind_param for DBD::mysql::st (DBI::st=HASH(0xf118985b58)~0xf1188715e0 1 101 4) thr#f1182eb880 Called: dbd_bind_ph SCALAR type 4 ->0<- IS A INT NUMBER FORCE REBIND: buffer type changed from 254 to 3, sql-type=4 There are two points to note a) line 303 says that the number is '0'. But this is red herring, this is not the issue, but rather broken number printing. dbdimp.c: 4878 char *buffer= NULL; ... buffer=(void*)&(imp_sth->fbind[idx].numeric_val.lval); if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) PerlIO_printf(DBIc_LOGPIO(imp_xxh), " SCALAR type %d ->%ld<- IS A INT NUMBER\n", (int) sql_type, (long) (*buffer)); (long)(*buffer) means 'dereference' char* and convert result to long (I think :) ). If I change the conversion to (*(long*)buffer), the debug line starts to print correctly '101'. But the test outcome is still the same. b) line 304 "FORCE REBIND: buffer type changed from 254 to 3, sql-type=4". I am not sure what that means exactly, but the dbdimp.c has a comment: /* Type of column was changed. Force to rebind */ if (imp_sth->bind[idx].buffer_type != buffer_type) { /* Note: this looks like being another bug: * if type of parameter N changes, then a bind is triggered * with an only partially filled bind structure ?? */ if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) PerlIO_printf(DBIc_LOGPIO(imp_xxh), " FORCE REBIND: buffer type changed from %d to %d, sql-type=%d\n", (int) imp_sth->bind[idx].buffer_type, buffer_type, (int) sql_type); imp_sth->has_been_bound = 0; } That does not look too assuring. To fix the data storing to database I have modified struct imp_sth_phb_st to contain int32_t: typedef struct imp_sth_phb_st { union { long lval; double dval; int32_t i32; } numeric_val; unsigned long length; char is_null; } imp_sth_phb_t; Back at dbdimp.c, I can now use: buffer_length = sizeof imp_sth->fbind[idx].numeric_val.i32; imp_sth->fbind[idx].numeric_val.i32= SvIV(imp_sth->params[idx].value); buffer=(void*)&(imp_sth->fbind[idx].numeric_val.i32); break; ( using long does not work because it's 8 bytes long in my case ). And that stores the numbers mostly corerctly to DB. It does not work for BIGINT (I have tried int64_t, but somehow that did not work for me). Also I can't store 4294967295 to DBI::SQL_INTEGER and 18446744073709551615 to DBI::SQL_BIGINT because it says that the value is too large, but that might be problem elsewhere. I did no investigation on this yet. So storing a number to mysql mostly works for now, another problem is reading it back. I suspect that there is similar problem - using long. But I fell asleep before finding where this happens. Sorry for the erratic report, its work in progress. Any comments welcome! Cheers __ Vlad
Subject: dbitrace.log.orig
Download dbitrace.log.orig
application/octet-stream 57.3k

Message body not shown because it is not plain text.

From: vlmarek [...] volny.cz
Download (untitled) / with headers
text/plain 810b
Show quoted text
> So storing a number to mysql mostly works for now, another problem is > reading it back. I suspect that there is similar problem - using long. > But I fell asleep before finding where this happens.
Yup, if I change struct imp_sth_fbh_st member 'ldata' to int32_t, and in function dbd_st_fetch change (long) to (int32_t) the tests start to pass. if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) PerlIO_printf(DBIc_LOGPIO(imp_xxh), "\t\tst_fetch int data %d, unsigned? %d\n", (int32_t) fbh->ldata, buffer->is_unsigned); My next steps will be to put my patches together and test it on both x86 and sparc. Once that starts working I want to add - tests for minimum and maximum values for all supported INT types - take a look why BIGINT does not work Cheers __ Vlad
From: vlmarek [...] volny.cz
Download (untitled) / with headers
text/plain 909b
I'm attaching the patch I have at the moment. But I found more issues, perl is core dumping on exit with the DBD::mysql module. $ pstack core core 'core' of 818825: /usr/perl5/5.20/bin/perl t/00base.t 0000000000000000 ???????? (fffffe26fe0a95b1, 0, 1, 0, 7ff9b961272c0, 0) 0007ff9b95ef60b8 _exithandle (2880, 7ff9b96124a40, 2800, 100004aac, ffffffffffd0e7b0, 2f1800) + 60 0007ff9b95ee506c exit (0, 0, 7ff9b9585e7b0, 0, 7ff9b95b50000, 0) + 4 0000000100004aac main (0, 7ff9b95b5f8c0, 100106000, 7ff9b9585c0d4, 7ff9b95b5ac38, 100200000) + 1ac 0000000100001c68 _start (0, 0, 0, 0, 0, 100106000) + 108 My suspicion is that there is some function registered via atexit. I added exec "/bin/true"; at the end of the test script and really perl no longer crashes (exec discards any atexit registered calls). The crashing occured even before me starting patching the sources, I just never noticed before :)
Subject: endian.patch
Download endian.patch
text/x-diff 1.1k
--- DBD-mysql-4.033/dbdimp.c 2016-02-16 04:39:12.852079166 -0800 +++ DBD-mysql-4.033/dbdimp.c 2016-02-16 04:38:42.856000275 -0800 @@ -4879,7 +4879,7 @@ int dbd_bind_ph(SV *sth, imp_sth_t *imp_ if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) PerlIO_printf(DBIc_LOGPIO(imp_xxh), " SCALAR type %d ->%ld<- IS A INT NUMBER\n", - (int) sql_type, (long) (*buffer)); + (int) sql_type, *((int32_t *)buffer)); break; case MYSQL_TYPE_DOUBLE: --- DBD-mysql-4.033/dbdimp.h 2016-02-16 04:39:55.405194264 -0800 +++ DBD-mysql-4.033/dbdimp.h 2016-02-16 04:39:48.165925859 -0800 @@ -212,7 +212,7 @@ typedef struct imp_sth_ph_st { typedef struct imp_sth_phb_st { union { - long lval; + int32_t lval; double dval; } numeric_val; unsigned long length; @@ -233,7 +233,7 @@ typedef struct imp_sth_fbh_st { char *data; int charsetnr; double ddata; - long ldata; + int32_t ldata; #if MYSQL_VERSION_ID < FIELD_CHARSETNR_VERSION unsigned int flags; #endif
From: ppisar [...] redhat.com
I stumble on this problem on s390x machine which is 64-bit big-endian platform. Attached is a little more thorough fix. A table at <http://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-fetch.html> explains that MYSQL_TYPE_LONG is 4-byte long, thus you correctly changed the type to int32_t. What bothers me is that if I try to store larger value than a column type allows, it will silently pass and the stored value will be wrapped. But the mysql(1) client works the same way. It only warns about out-of-range values. Maybe it's MysSQL feature. Maybe the DBD::MySQL could check a stored value against requested type that was declared in the bind_param() call and return a failure. Also the fourth value from the the tests inserted by: ok($sth2->bind_param(4, 104, DBI::SQL_INTEGER), "binding bigint"); should be spelled as ok($sth2->bind_param(4, 104, DBI::SQL_BIGINT), "binding bigint"); But even with DBI::SQL_BIGINT, the value is transferred as MYSQL_TYPE_LONG instead of MYSQL_TYPE_LONGLONG. Something is fishy there.
Subject: 0001-Fix-transferring-MYSQL_TYPE_LONG-values-on-64-bit-bi.patch
From 95b210810301f0a5c87adc0d682bc8424dfb41d0 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Petr=20P=C3=ADsa=C5=99?= <ppisar@redhat.com> Date: Fri, 26 Feb 2016 13:32:31 +0100 Subject: [PATCH] Fix transferring MYSQL_TYPE_LONG values on 64-bit big endian systems MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit t/40server_prepare.t test failed on s390x platform. Server-prepared values of types int, smallint, and tinyint are passed to application as 32-bit integer. The same buffer was interpreted as long integer by DBD::MySQL. This caused missaligned read/write and bogus interpretation of the values. https://rt.cpan.org/Public/Bug/Display.html?id=57266 https://bugzilla.redhat.com/show_bug.cgi?id=1311646 http://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-fetch.html Signed-off-by: Petr Písař <ppisar@redhat.com> --- dbdimp.c | 20 +++++++++++++------- dbdimp.h | 5 +++-- 2 files changed, 16 insertions(+), 9 deletions(-) diff --git a/dbdimp.c b/dbdimp.c index d507588..9a1be20 100644 --- a/dbdimp.c +++ b/dbdimp.c @@ -18,6 +18,7 @@ #endif #include "dbdimp.h" +#include <inttypes.h> /* for PRId32 */ #if defined(WIN32) && defined(WORD) #undef WORD @@ -3753,8 +3754,8 @@ int dbd_describe(SV* sth, imp_sth_t* imp_sth) if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) { - PerlIO_printf(DBIc_LOGPIO(imp_xxh),"\t\ti %d col_type %d fbh->length %d\n", - i, col_type, (int) fbh->length); + PerlIO_printf(DBIc_LOGPIO(imp_xxh),"\t\ti %d col_type %d fbh->length %lu\n", + i, col_type, fbh->length); PerlIO_printf(DBIc_LOGPIO(imp_xxh), "\t\tfields[i].length %lu fields[i].max_length %lu fields[i].type %d fields[i].charsetnr %d\n", (long unsigned int) fields[i].length, (long unsigned int) fields[i].max_length, fields[i].type, @@ -4015,8 +4016,8 @@ process: case MYSQL_TYPE_LONG: if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) - PerlIO_printf(DBIc_LOGPIO(imp_xxh), "\t\tst_fetch int data %d, unsigned? %d\n", - (int) fbh->ldata, buffer->is_unsigned); + PerlIO_printf(DBIc_LOGPIO(imp_xxh), "\t\tst_fetch int data %"PRId32", unsigned? %d\n", + fbh->ldata, buffer->is_unsigned); if (buffer->is_unsigned) sv_setuv(sv, fbh->ldata); else @@ -4787,6 +4788,7 @@ int dbd_bind_ph(SV *sth, imp_sth_t *imp_sth, SV *param, SV *value, int buffer_is_null= 0; int buffer_length= slen; unsigned int buffer_type= 0; + IV tmp; #endif D_imp_dbh_from_sth; @@ -4874,12 +4876,16 @@ int dbd_bind_ph(SV *sth, imp_sth_t *imp_sth, SV *param, SV *value, if (!SvIOK(imp_sth->params[idx].value) && DBIc_TRACE_LEVEL(imp_xxh) >= 2) PerlIO_printf(DBIc_LOGPIO(imp_xxh), "\t\tTRY TO BIND AN INT NUMBER\n"); buffer_length = sizeof imp_sth->fbind[idx].numeric_val.lval; - imp_sth->fbind[idx].numeric_val.lval= SvIV(imp_sth->params[idx].value); + + tmp = SvIV(imp_sth->params[idx].value); + if (tmp > INT32_MAX) + croak("Could not bind %ld: Integer too large for MYSQL_TYPE_LONG", tmp); + imp_sth->fbind[idx].numeric_val.lval= tmp; buffer=(void*)&(imp_sth->fbind[idx].numeric_val.lval); if (DBIc_TRACE_LEVEL(imp_xxh) >= 2) PerlIO_printf(DBIc_LOGPIO(imp_xxh), - " SCALAR type %d ->%ld<- IS A INT NUMBER\n", - (int) sql_type, (long) (*buffer)); + " SCALAR type %d ->%"PRId32"<- IS A INT NUMBER\n", + (int) sql_type, *(int32_t *)buffer); break; case MYSQL_TYPE_DOUBLE: diff --git a/dbdimp.h b/dbdimp.h index 8723bcc..1ef5d72 100644 --- a/dbdimp.h +++ b/dbdimp.h @@ -22,6 +22,7 @@ #include <mysqld_error.h> /* Comes MySQL */ #include <errmsg.h> /* Comes with MySQL-devel */ +#include <stdint.h> /* For int32_t */ /* For now, we hardcode this, but in the future, * we can detect capabilities of the MySQL libraries @@ -212,7 +213,7 @@ typedef struct imp_sth_ph_st { typedef struct imp_sth_phb_st { union { - long lval; + int32_t lval; double dval; } numeric_val; unsigned long length; @@ -233,7 +234,7 @@ typedef struct imp_sth_fbh_st { char *data; int charsetnr; double ddata; - long ldata; + int32_t ldata; #if MYSQL_VERSION_ID < FIELD_CHARSETNR_VERSION unsigned int flags; #endif -- 2.5.0
From: vlmarek [...] volny.cz
Download (untitled) / with headers
text/plain 1.2k
On Fri Feb 26 09:02:09 2016, ppisar wrote: Show quoted text
> I stumble on this problem on s390x machine which is 64-bit big-endian > platform. Attached is a little more thorough fix.
Many thanks, I was just about to go tackle the improperly read INT values when I found your patch :) Show quoted text
> What bothers me is that if I try to store larger value than a column > type allows, it will silently pass and the stored value will be > wrapped. But the mysql(1) client works the same way. It only warns > about out-of-range values. Maybe it's MysSQL feature. > > Maybe the DBD::MySQL could check a stored value against requested type > that was declared in the bind_param() call and return a failure.
That would make sense to me, but I guess it depends on DBD::mysql author. Show quoted text
> Also the fourth value from the the tests inserted by: > > ok($sth2->bind_param(4, 104, DBI::SQL_INTEGER), "binding bigint"); > > should be spelled as > > ok($sth2->bind_param(4, 104, DBI::SQL_BIGINT), "binding bigint"); > > But even with DBI::SQL_BIGINT, the value is transferred as > MYSQL_TYPE_LONG instead of MYSQL_TYPE_LONGLONG. Something is fishy > there.
I'm just writing tests for minimal, maximal and over the limit values storing. We'll see if it poses any real problem. Thanks again __ Vlad
From: vlmarek [...] volny.cz
Download (untitled) / with headers
text/plain 412b
Show quoted text
> But even with DBI::SQL_BIGINT, the value is transferred as > MYSQL_TYPE_LONG instead of MYSQL_TYPE_LONGLONG. Something is fishy > there.
Seems to work fine on my side, although it does not appear to be correct on first sight. I am attaching the test file I used to make sure d.various integer types work as expected. So to me the patch from Petr is a final fix. If you like, you may add the tests I created.
Subject: 41int_min_max.t
Download 41int_min_max.t
text/x-perl 2.6k
use strict; use warnings; use bigint; use DBI; use Test::More; use lib 't', '.'; require 'lib.pl'; use vars qw($test_dsn $test_user $test_password); my $dbh; eval {$dbh= DBI->connect($test_dsn, $test_user, $test_password, { RaiseError => 1, PrintError => 1, AutoCommit => 1 });}; if ($@) { plan skip_all => "no database connection"; } if (!MinimumVersion($dbh, '4.1')) { plan skip_all => "ERROR: $DBI::errstr. Can't continue test"; plan skip_all => "SKIP TEST: You must have MySQL version 4.1 and greater for this test to run"; } my $table = 'dbd_mysql_t41minmax'; # name of the table we will be using plan tests => 11*8 + 2; sub test_int_type ($$$$) { my ($perl_type, $mysql_type, $min, $max) = @_; # Disable the warning text clobbering our output local $SIG{__WARN__} = sub { 1; }; # Create the table ok($dbh->do(qq{DROP TABLE IF EXISTS $table}), "removing $table"); ok($dbh->do(qq{CREATE TABLE `$table` (`val` $mysql_type)}), "creating minmax table for type $mysql_type"); my $minmax; ok($minmax = $dbh->prepare("INSERT INTO $table VALUES (?)")); # Insert allowed in and max value ok($minmax->bind_param( 1, $min->bstr(), $perl_type ), "binding minimal $mysql_type"); ok($minmax->execute(), "inserting min data for type $mysql_type"); ok($minmax->bind_param( 1, $max->bstr(), $perl_type ), "binding maximal $mysql_type"); ok($minmax->execute(), "inserting max data for type $mysql_type"); # Try to insert over the limit value ok($minmax->bind_param( 1, ($min-1)->bstr(), $perl_type ), "binding less than minimal $mysql_type"); $@ = ''; eval{$minmax->execute()}; like($@, qr/Out of range value for column 'val'/, "less than min exception thrown for $mysql_type"); ok($minmax->bind_param( 1, ($max+1)->bstr(), $perl_type ), "binding more than maximal $mysql_type"); $@ = ''; eval{$minmax->execute()}; like($@, qr/Out of range value for column 'val'/, "more than max exception thrown for $mysql_type"); } test_int_type(DBI::SQL_TINYINT, 'tinyint signed', -2**7, 2**7-1); test_int_type(DBI::SQL_TINYINT, 'tinyint unsigned', 0, 2**8-1); test_int_type(DBI::SQL_SMALLINT, 'smallint signed', -2**15, 2**15-1); test_int_type(DBI::SQL_SMALLINT, 'smallint unsigned', 0, 2**16-1); test_int_type(DBI::SQL_INTEGER, 'int signed', -2**31, 2**31-1); test_int_type(DBI::SQL_INTEGER, 'int unsigned', 0, 2**32-1); test_int_type(DBI::SQL_BIGINT, 'bigint signed', -2**63, 2**63-1); test_int_type(DBI::SQL_BIGINT, 'bigint unsigned', 0, 2**64-1); ok ($dbh->do("DROP TABLE $table")); ok $dbh->disconnect;
RT-Send-CC: ppisar [...] redhat.com, vlmarek [...] volny.cz
Download (untitled) / with headers
text/plain 776b
Thanks for the patch and tests! The test from Vladimir however fails on little-endian test systems, at least on my Debian laptop and also on Travis: https://travis-ci.org/perl5-dbi/DBD-mysql/jobs/113055676 this is with or without Petrs patch. @Vladimir, can you look into that? -- Michiel On Tue Mar 01 17:25:39 2016, neuron wrote: Show quoted text
> > But even with DBI::SQL_BIGINT, the value is transferred as > > MYSQL_TYPE_LONG instead of MYSQL_TYPE_LONGLONG. Something is fishy > > there.
> > Seems to work fine on my side, although it does not appear to be > correct on first sight. > > I am attaching the test file I used to make sure d.various integer > types work as expected. > > So to me the patch from Petr is a final fix. If you like, you may add > the tests I created.
From: vlmarek [...] volny.cz
Download (untitled) / with headers
text/plain 548b
Show quoted text
> this is with or without Petrs patch. > @Vladimir, can you look into that?
Ah, live and learn. mysql has something called "strict sql" mode. http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_strict_all_tables It seems that in my case strict mode is enabled by default, while on linux it is not. I have updated the tests to go through both strict and no-strict modes, plus I added testing that the over-the-limit value is rounded correctly. Maybe I'm overthinking it, but I would like to do the porting to 64bit right on first try :)
Subject: 41int_min_max.t
Download 41int_min_max.t
text/x-perl 6k
use strict; use warnings; use bigint; use DBI; use Test::More; use lib 't', '.'; use Data::Dumper; require 'lib.pl'; use vars qw($test_dsn $test_user $test_password); my $dbh; eval {$dbh= DBI->connect($test_dsn, $test_user, $test_password, { RaiseError => 1, PrintError => 1, AutoCommit => 1 });}; if ($@) { plan skip_all => "no database connection"; } if (!MinimumVersion($dbh, '4.1')) { plan skip_all => "ERROR: $DBI::errstr. Can't continue test"; plan skip_all => "SKIP TEST: You must have MySQL version 4.1 and greater for this test to run"; } # nostrict tests + strict tests + init/tear down commands plan tests => 19*8 + 17*8 + 4; my $table = 'dbd_mysql_t41minmax'; # name of the table we will be using my $mode; # 'strict' or 'nostrict' corresponds to strict SQL mode sub test_int_type ($$$$) { my ($perl_type, $mysql_type, $min, $max) = @_; # Disable the warning text clobbering our output local $SIG{__WARN__} = sub { 1; }; # Create the table ok($dbh->do(qq{DROP TABLE IF EXISTS $table}), "removing $table"); ok($dbh->do(qq{ CREATE TABLE `$table` ( `id` int not null auto_increment, `val` $mysql_type, primary key (id) ) }), "creating minmax table for type $mysql_type"); my ($store, $retrieve); # statements my $read_value; # retrieved value ok($store = $dbh->prepare("INSERT INTO $table (val) VALUES (?)")); ok($retrieve = $dbh->prepare("SELECT val from $table where id=(SELECT MAX(id) FROM $table)")); ######################################## # Insert allowed min value ######################################## ok($store->bind_param( 1, $min->bstr(), $perl_type ), "binding minimal $mysql_type, mode=$mode"); ok($store->execute(), "inserting min data for type $mysql_type, mode=$mode"); ######################################## # Read it back and compare ######################################## ok{$retrieve->execute()}; ($read_value) = $retrieve->fetchrow_array(); cmp_ok($read_value, 'eq', $min, "retrieved minimal value for $mysql_type, mode=$mode"); ######################################## # Insert allowed max value ######################################## ok($store->bind_param( 1, $max->bstr(), $perl_type ), "binding maximal $mysql_type, mode=$mode"); ok($store->execute(), "inserting max data for type $mysql_type, mode=$mode"); ######################################## # Read it back and compare ######################################## ok{$retrieve->execute()}; ($read_value) = $retrieve->fetchrow_array(); cmp_ok($read_value, 'eq', $max, "retrieved maximal value for $mysql_type, mode=$mode"); ######################################## # Try to insert under the limit value ######################################## ok($store->bind_param( 1, ($min-1)->bstr(), $perl_type ), "binding less than minimal $mysql_type, mode=$mode"); if ($mode eq 'strict') { $@ = ''; eval{$store->execute()}; like($@, qr/Out of range value for column 'val'/, "Error, you stored ".($min-1)." into $mysql_type, mode=$mode\n". Data::Dumper->Dump([$dbh->selectall_arrayref("SELECT * FROM $table")]). Data::Dumper->Dump([$dbh->selectall_arrayref("describe $table")]) ); } else { ok{$store->execute()}; ######################################## # Check that it was rounded correctly ######################################## ok{$retrieve->execute()}; ($read_value) = $retrieve->fetchrow_array(); cmp_ok($read_value, 'eq', $min, "retrieved minimal value for type $mysql_type, mode=$mode"); }; ######################################## # Try to insert over the limit value ######################################## ok($store->bind_param( 1, ($max+1)->bstr(), $perl_type ), "binding more than maximal $mysql_type, mode=$mode"); if ($mode eq 'strict') { $@ = ''; eval{$store->execute()}; like($@, qr/Out of range value for column 'val'/, "Error, you stored ".($max+1)." into $mysql_type, mode=$mode\n". Data::Dumper->Dump([$dbh->selectall_arrayref("SELECT * FROM $table")]). Data::Dumper->Dump([$dbh->selectall_arrayref("describe $table")]) ); } else { ok{$store->execute()}; ######################################## # Check that it was rounded correctly ######################################## ok{$retrieve->execute()}; ($read_value) = $retrieve->fetchrow_array(); cmp_ok($read_value, 'eq', $max, "retrieved maximal value for type $mysql_type, mode=$mode"); }; } # Set strict SQL mode ok($dbh->do("SET SQL_MODE='STRICT_ALL_TABLES'"),"Enter strict SQL mode."); $mode = 'strict'; test_int_type(DBI::SQL_TINYINT, 'tinyint signed', -2**7, 2**7-1); test_int_type(DBI::SQL_TINYINT, 'tinyint unsigned', 0, 2**8-1); test_int_type(DBI::SQL_SMALLINT, 'smallint signed', -2**15, 2**15-1); test_int_type(DBI::SQL_SMALLINT, 'smallint unsigned', 0, 2**16-1); test_int_type(DBI::SQL_INTEGER, 'int signed', -2**31, 2**31-1); test_int_type(DBI::SQL_INTEGER, 'int unsigned', 0, 2**32-1); test_int_type(DBI::SQL_BIGINT, 'bigint signed', -2**63, 2**63-1); test_int_type(DBI::SQL_BIGINT, 'bigint unsigned', 0, 2**64-1); # Do not use strict SQL mode ok($dbh->do("SET SQL_MODE=''"),"Leave strict SQL mode."); $mode = 'nostrict'; test_int_type(DBI::SQL_TINYINT, 'tinyint signed', -2**7, 2**7-1); test_int_type(DBI::SQL_TINYINT, 'tinyint unsigned', 0, 2**8-1); test_int_type(DBI::SQL_SMALLINT, 'smallint signed', -2**15, 2**15-1); test_int_type(DBI::SQL_SMALLINT, 'smallint unsigned', 0, 2**16-1); test_int_type(DBI::SQL_INTEGER, 'int signed', -2**31, 2**31-1); test_int_type(DBI::SQL_INTEGER, 'int unsigned', 0, 2**32-1); test_int_type(DBI::SQL_BIGINT, 'bigint signed', -2**63, 2**63-1); test_int_type(DBI::SQL_BIGINT, 'bigint unsigned', 0, 2**64-1); ok ($dbh->do("DROP TABLE $table")); ok $dbh->disconnect;
Download (untitled) / with headers
text/plain 795b
I added Petrs patch in 4.033_03 but forgot to add it to the change log. I did that now, and added Vladimirs' test as well. Thanks all for your contributions! -- Michiel On Wed 02 Mar 2016 11:14:06, neuron wrote: Show quoted text
> > this is with or without Petrs patch. > > @Vladimir, can you look into that?
> > Ah, live and learn. mysql has something called "strict sql" mode. > http://dev.mysql.com/doc/refman/5.6/en/sql- > mode.html#sqlmode_strict_all_tables > It seems that in my case strict mode is enabled by default, while on > linux it is not. > > I have updated the tests to go through both strict and no-strict > modes, plus I added testing that the over-the-limit value is rounded > correctly. > > Maybe I'm overthinking it, but I would like to do the porting to 64bit > right on first try :)


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.