| Bug #65551 | mysqlbinlog -v -v does wrong data types | ||
|---|---|---|---|
| Submitted: | 7 Jun 2012 11:26 | Modified: | 25 Sep 2012 11:52 |
| Reporter: | Oli Sennhauser | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.5.24, 5.5.26 | OS: | Linux (Linux master 2.6.35-32-generic #67-Ubuntu SMP Mon Mar 5 19:39:49 UTC 2012 x86_64 GNU/Linux) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | attribute demotion, attribute promotion, Data Type, Event, mysqlbinlog | ||
[7 Jun 2012 11:26]
Oli Sennhauser
### UPDATE test.users
### WHERE
### @1='secret' /* VARSTRING(384) meta=384 nullable=0 is_null=0 */
### @2='shinguz' /* VARSTRING(45) meta=45 nullable=0 is_null=0 */
### @3='oli.sennhauser@fromdual.com' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @4='' /* VARSTRING(75) meta=75 nullable=0 is_null=0 */
### @5='' /* VARSTRING(75) meta=75 nullable=0 is_null=0 */
### @6=2012-06-04 00:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
### @7=446163 /* INT meta=0 nullable=0 is_null=0 */
### @8=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @9='0000:00:00' /* DATE meta=0 nullable=0 is_null=0 */
### @10=NULL /* DATE meta=96 nullable=1 is_null=1 */
### @11=2011-12-04 08:54:08 /* DATETIME meta=0 nullable=0 is_null=0 */
### @12=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @13=NULL /* ENUM(1 byte) meta=60 nullable=1 is_null=1 */
### @14=NULL /* ENUM(1 byte) meta=0 nullable=1 is_null=1 */
### @15='shinguz' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @16=NULL /* VARSTRING(60) meta=770 nullable=1 is_null=1 */
### @17=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @18='60.169.73.140' /* VARSTRING(117) meta=117 nullable=1 is_null=0 */
### @19=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @20=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @21=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @22=NULL /* TINYINT meta=0 nullable=1 is_null=1 */
### @23=NULL /* TINYINT meta=45 nullable=1 is_null=1 */
### @24=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @25=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @26='en' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @27=0 /* INT meta=0 nullable=1 is_null=0 */
### @28=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @29=100 /* MEDIUMINT meta=0 nullable=0 is_null=0 */
### @30=0 /* MEDIUMINT meta=0 nullable=0 is_null=0 */
### @31=0 /* MEDIUMINT meta=0 nullable=0 is_null=0 */
### @32=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @33='63EECFFF-2219-AC68-17C477EF174EFFCB' /* STRING(105) meta=65129 nullable=0 is_null=0 */
### SET
### @1='secret' /* VARSTRING(384) meta=384 nullable=0 is_null=0 */
### @2='shinguz' /* VARSTRING(45) meta=45 nullable=0 is_null=0 */
### @3='oli.sennhauser@fromdual.com' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @4='' /* VARSTRING(75) meta=75 nullable=0 is_null=0 */
### @5='' /* VARSTRING(75) meta=75 nullable=0 is_null=0 */
### @6=2012-06-04 00:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
### @7=446163 /* INT meta=0 nullable=0 is_null=0 */
### @8=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @9='0000:00:00' /* DATE meta=0 nullable=0 is_null=0 */
### @10=NULL /* DATE meta=96 nullable=1 is_null=1 */
### @11=2011-12-04 08:54:08 /* DATETIME meta=0 nullable=0 is_null=0 */
### @12=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @13=NULL /* ENUM(1 byte) meta=60 nullable=1 is_null=1 */
### @14=NULL /* ENUM(1 byte) meta=0 nullable=1 is_null=1 */
### @15='shinguz' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @16=NULL /* VARSTRING(60) meta=770 nullable=1 is_null=1 */
### @17=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @18='60.169.75.53' /* VARSTRING(117) meta=117 nullable=1 is_null=0 */
### @19=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @20=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @21=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @22=NULL /* TINYINT meta=0 nullable=1 is_null=1 */
### @23=NULL /* TINYINT meta=45 nullable=1 is_null=1 */
### @24=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @25=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @26='en' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @27=0 /* INT meta=0 nullable=1 is_null=0 */
### @28=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @29=100 /* MEDIUMINT meta=0 nullable=0 is_null=0 */
### @30=0 /* MEDIUMINT meta=0 nullable=0 is_null=0 */
### @31=0 /* MEDIUMINT meta=0 nullable=0 is_null=0 */
### @32=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @33='63EECFFF-2219-AC68-17C477EF174EFFCB' /* STRING(105) meta=65129 nullable=0 is_null=0 */
@01 `password` varchar(128) NOT NULL,
@02 `UserName` varchar(15) NOT NULL DEFAULT '',
@03 `Email` varchar(100) NOT NULL DEFAULT '',
@04 `Firstname` varchar(25) NOT NULL DEFAULT '',
@05 `Surname` varchar(25) NOT NULL DEFAULT '',
@06 `LastLogin` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
@07 `UserID` int(8) unsigned zerofill NOT NULL AUTO_INCREMENT,
@08 `Newsletter` enum('yes','no') NOT NULL DEFAULT 'no',
@09 `DOB` date NOT NULL DEFAULT '0000-00-00',
@10 `ProfPassword` varchar(32) DEFAULT NULL,
@11 `Signupdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
@12 `Emailmessages` enum('yes','no') NOT NULL DEFAULT 'yes',
@13 `MemberTitle` varchar(20) DEFAULT NULL,
@14 `lastupload` date DEFAULT NULL,
@15 `domain` varchar(20) DEFAULT NULL,
@16 `rank` decimal(3,2) DEFAULT NULL,
@17 `autosave` tinyint(1) DEFAULT '1',
@18 `IPAddress` varchar(39) DEFAULT NULL,
@19 `countryID` smallint(3) unsigned NOT NULL DEFAULT '0',
@20 `affiliate_ID` smallint(3) unsigned NOT NULL DEFAULT '0',
@21 `EmailPhotoComments` tinyint(1) NOT NULL DEFAULT '1',
@22 `facebook_uid` bigint(20) DEFAULT NULL,
@23 `bebo_uid` varchar(15) DEFAULT NULL,
@24 `warnonnocoords` tinyint(1) NOT NULL DEFAULT '1',
@25 `isEmailVerified` tinyint(1) NOT NULL DEFAULT '0',
@26 `languages` varchar(100) NOT NULL DEFAULT 'en',
@27 `forumCount` int(11) DEFAULT '0',
@28 `isLimited` tinyint(1) NOT NULL DEFAULT '0',
@29 `storageAllowance` mediumint(6) NOT NULL DEFAULT '100',
@30 `storageUsed` mediumint(6) NOT NULL DEFAULT '0',
@31 `extraStorageAllowance` mediumint(6) NOT NULL DEFAULT '0',
@32 `watchlistNotify` tinyint(1) NOT NULL DEFAULT '1',
@33 `salt` char(35) NOT NULL DEFAULT '',
[7 Jun 2012 12:47]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with 5.5.26 on Mac OS X also: ... ### UPDATE test.users ### WHERE ### @1='secret' /* VARSTRING(384) meta=384 nullable=0 is_null=0 */ ### @2='shinguz' /* VARSTRING(45) meta=45 nullable=0 is_null=0 */ ### @3='oli.sennhauser@fromdual.com' /* VARSTRING(300) meta=300 nullable=0 is_ null=0 */ ### @4='' /* VARSTRING(75) meta=75 nullable=0 is_null=0 */ ### @5='' /* VARSTRING(75) meta=75 nullable=0 is_null=0 */ ### @6=2012-06-04 00:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */ ### @7=446163 /* INT meta=0 nullable=0 is_null=0 */ ### @8=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @9='0000:00:00' /* DATE meta=0 nullable=0 is_null=0 */ ### @10=NULL /* DATE meta=96 nullable=1 is_null=1 */ ### @11=2011-12-04 08:54:08 /* DATETIME meta=0 nullable=0 is_null=0 */ ### @12=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @13=NULL /* ENUM(1 byte) meta=60 nullable=1 is_null=1 */ ### @14=NULL /* ENUM(1 byte) meta=0 nullable=1 is_null=1 */ ### @15='shinguz' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### @16=NULL /* VARSTRING(60) meta=770 nullable=1 is_null=1 */ ### @17=1 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @18='60.169.73.140' /* VARSTRING(117) meta=117 nullable=1 is_null=0 */ ### @19=0 /* SHORTINT meta=0 nullable=0 is_null=0 */ ### @20=0 /* SHORTINT meta=0 nullable=0 is_null=0 */ ### @21=1 /* TINYINT meta=0 nullable=0 is_null=0 */ ### @22=NULL /* TINYINT meta=0 nullable=1 is_null=1 */ ### @23=NULL /* TINYINT meta=45 nullable=1 is_null=1 */ ### @24=1 /* TINYINT meta=0 nullable=0 is_null=0 */ ### @25=0 /* TINYINT meta=0 nullable=0 is_null=0 */ ### @26='en' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */ ### @27=0 /* INT meta=0 nullable=1 is_null=0 */ ### @28=0 /* TINYINT meta=0 nullable=0 is_null=0 */ ### @29=100 /* MEDIUMINT meta=0 nullable=0 is_null=0 */ ### @30=0 /* MEDIUMINT meta=0 nullable=0 is_null=0 */ ### @31=0 /* MEDIUMINT meta=0 nullable=0 is_null=0 */ ### @32=1 /* TINYINT meta=0 nullable=0 is_null=0 */ ### @33='63EECFFF-2219-AC68-17C477EF174EFFCB' /* STRING(105) meta=65129 nullab le=0 is_null=0 */ ...
[25 Sep 2012 11:52]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
[25 Sep 2012 11:53]
Jon Stephens
Fixed in trunk, currently tagged 5.7.0.
Description: We recently got a replication error of the following type: Last_Errno: 1677 Last_Error: Column 15 of table 'test.users' cannot be converted from type 'decimal(0,?)' to type 'decimal(3,2)' When digging in the binary log we found that mysqlbinlog -v -v does not show the attribute data types correctly. If it is related to the replication error we did not find out. We could not reproduce the bug. But the mysqlbinlog utility error was reproducible How to repeat: Attributes # 10, 13, 14, 16, 22, 23 are wrong! #16 is the one mentioned in the error message... select @@binlog_format; ROW CREATE TABLE `users` ( `password` varchar(128) NOT NULL, `UserName` varchar(15) NOT NULL DEFAULT '', `Email` varchar(100) NOT NULL DEFAULT '', `Firstname` varchar(25) NOT NULL DEFAULT '', `Surname` varchar(25) NOT NULL DEFAULT '', `LastLogin` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `UserID` int(8) unsigned zerofill NOT NULL AUTO_INCREMENT, `Newsletter` enum('yes','no') NOT NULL DEFAULT 'no', `DOB` date NOT NULL DEFAULT '0000-00-00', `ProfPassword` varchar(32) DEFAULT NULL, `Signupdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `Emailmessages` enum('yes','no') NOT NULL DEFAULT 'yes', `MemberTitle` varchar(20) DEFAULT NULL, `lastupload` date DEFAULT NULL, `domain` varchar(20) DEFAULT NULL, `rank` decimal(3,2) DEFAULT NULL, `autosave` tinyint(1) DEFAULT '1', `IPAddress` varchar(39) DEFAULT NULL, `countryID` smallint(3) unsigned NOT NULL DEFAULT '0', `affiliate_ID` smallint(3) unsigned NOT NULL DEFAULT '0', `EmailPhotoComments` tinyint(1) NOT NULL DEFAULT '1', `facebook_uid` bigint(20) DEFAULT NULL, `bebo_uid` varchar(15) DEFAULT NULL, `warnonnocoords` tinyint(1) NOT NULL DEFAULT '1', `isEmailVerified` tinyint(1) NOT NULL DEFAULT '0', `languages` varchar(100) NOT NULL DEFAULT 'en', `forumCount` int(11) DEFAULT '0', `isLimited` tinyint(1) NOT NULL DEFAULT '0', `storageAllowance` mediumint(6) NOT NULL DEFAULT '100', `storageUsed` mediumint(6) NOT NULL DEFAULT '0', `extraStorageAllowance` mediumint(6) NOT NULL DEFAULT '0', `watchlistNotify` tinyint(1) NOT NULL DEFAULT '1', `salt` char(35) NOT NULL DEFAULT '', PRIMARY KEY (`UserID`), UNIQUE KEY `UserName` (`UserName`), KEY `Firstname` (`Firstname`), KEY `Surname` (`Surname`), KEY `countryID` (`countryID`), KEY `affiliate_ID` (`affiliate_ID`), KEY `Signupdate` (`Signupdate`), KEY `facebook_uid` (`facebook_uid`), KEY `email` (`Email`) ) ENGINE=InnoDB AUTO_INCREMENT=491235 DEFAULT CHARSET=utf8 COMMENT='user''s details' ; INSERT INTO users VALUES ( 'secret' , 'shinguz' , 'oli.sennhauser@fromdual.com' , '' , '' , '2012-06-04 00:00:00' , 446163 , 1 , '0000:00:00' , NULL , '2011-12-04 08:54:08' , 1 , NULL , NULL , 'shinguz' , NULL , 1 , '60.169.73.140' , 0 , 0 , 1 , NULL , NULL , 1 , 0 , 'en' , 0 , 00 , 100 , 0 , 0 , 1 , '63EECFFF-2219-AC68-17C477EF174EFFCB' ); UPDATE users SET ipaddress = '60.169.75.53' where userid = 446163; select * from users where userid= 00446163; mysqlbinlog -v -v bin-log.000001 | less Suggested fix: data type should be displayed correctly.