MySQL Bugs: #64149: Behavior change from 5.1 to 5.5

Bug #64149	Behavior change from 5.1 to 5.5
Submitted:	27 Jan 2012 15:57	Modified:	17 Mar 2012 17:22
Reporter:	Bharath G	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S2 (Serious)
Version:	5.5.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	5.5

Description:
I recently upgraded my MySql server from 5.1 to 5.5, after that I see suddenly few changes in the query formations. 

In my previous mysql versions, the query was printed as 

INSERT INTO xyz(column1, column2)values(_latin1'2011-12-21 17:04:14',_latin1'    ') - this is perfect

From 5.5, the query is printed as 

INSERT INTO xyz(column1, column2)values(_binary'2009-09-19 08:48:28',_binary 0x) - This is causing the problem for me.

Can i know why this behavior change in this MySql 5.5.

How can we bring back to _latin1 format.

Is there anything to do with my configuration settings?

How to repeat:
Not sure what to type here

Please, send the output of:

show variables like 'char%';

from your environment.

It would be nice to know what exact queries one should execute to see the problem, and where exactly one should check for the resulting "look" of INSERTs (in the binary log, in some other log)?

The output of show variables like 'char%'; in 5.5 and 5.1 are same as below:

'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
'character_sets_dir', 'C:\Program Files (x86)\MySQL\MySQL Server 5.1\share\charsets\'

Please, send the output of SHOW CREATE TABLE in 5.1 and 5.5. Where that INSERT statements are logged?

To summarize, we need a complete, repeatable test, with all the details.

I have problems with columns 'ExternFacilities', 'InternFacilities' which are defined as binary.

SHOW CREATE TABLE ticket

'ticket', 'CREATE TABLE "ticket" (
  "InitialStartDateTime" datetime NOT NULL,
  "TicketId" int(11) NOT NULL AUTO_INCREMENT,
  "CallCount" int(11) NOT NULL DEFAULT '1',
  "CalledNumber" varchar(30) DEFAULT NULL,
  "CallingNumber" varchar(30) DEFAULT NULL,
  "CallType" tinyint(3) unsigned DEFAULT NULL,
  "ChargeUnits" smallint(6) DEFAULT NULL,
  "NetworkAndTrunkNode" int(11) DEFAULT NULL,
  "TrunkGroupIdentity" varchar(10) DEFAULT NULL,
  "EntityId" int(11) DEFAULT NULL,
  "PersonalOrBusiness" tinyint(3) unsigned DEFAULT NULL,
  "WaitingDuration" smallint(6) DEFAULT '0',
  "EffectiveCallDuration" int(11) DEFAULT NULL,
  "ComType" tinyint(3) unsigned DEFAULT NULL,
  "CostInfo" double DEFAULT NULL,
  "InitialDialledNumber" varchar(30) DEFAULT NULL,
  "Carrier" varchar(5) DEFAULT NULL,
  "UserToUserVolume" smallint(6) DEFAULT '0',
  "StartDateTime" datetime DEFAULT NULL,
  "Duration" int(11) DEFAULT NULL,
  "RedirectedCallIndicator" tinyint(3) unsigned DEFAULT NULL,
  "Subaddress" varchar(20) DEFAULT NULL,
  "HighLevelComp" tinyint(3) unsigned DEFAULT NULL,
  "CostType" tinyint(3) unsigned DEFAULT NULL,
  "TrunkIdentity" smallint(6) DEFAULT NULL,
  "SpecificChargeInfo" char(7) DEFAULT NULL,
  "BearerCapability" tinyint(3) unsigned DEFAULT NULL,
  "DataVolume" int(11) DEFAULT NULL,
  "AdditionalEntityId" int(11) DEFAULT NULL,
  "FirstCarrierCost" double NOT NULL,
  "FirstCarrierCostT" double DEFAULT NULL,
  "SecondCarrierCost" double NOT NULL,
  "SecondCarrierCostT" double DEFAULT NULL,
  "FacilityCost" double NOT NULL,
  "FacilityCostT" double DEFAULT NULL,
  "FacturedCost" double DEFAULT NULL,
  "FacturedCostT" double DEFAULT NULL,
  "SubscriptionCost" double NOT NULL DEFAULT '0',
  "SubscriptionCostT" double DEFAULT NULL,
  "FirstCarrierId" int(11) DEFAULT NULL,
  "SecondCarrierId" int(11) DEFAULT NULL,
  "FirstCarrierDirectionId" int(11) DEFAULT NULL,
  "SecondCarrierDirectionId" int(11) DEFAULT NULL,
  "FirstCarrierCcnId" int(11) DEFAULT NULL,
  "SecondCarrierCcnId" int(11) DEFAULT NULL,
  "ActingExtensionNumber" varchar(30) DEFAULT NULL,
  "TransitTrunkGroupIdentity" varchar(5) DEFAULT NULL,
  "NodeTimeOffset" smallint(6) DEFAULT NULL,
  "ExternFacilities" binary(5) DEFAULT NULL,
  "InternFacilities" binary(5) DEFAULT NULL,
  "TicketOrigin" tinyint(3) unsigned DEFAULT '0',
  "TimeDlt" int(11) DEFAULT NULL,
  PRIMARY KEY ("TicketId"),
  UNIQUE KEY "IX_Ticket" ("TicketId"),
  KEY "IX2_Ticket" ("EntityId"),
  KEY "IX3_Ticket" ("InitialStartDateTime"),
  KEY "IX4_Ticket" ("StartDateTime")
)'

How to repeat:

mysql> INSERT INTO Ticket (InitialStartDateTime,CallCount,CalledNumber,CallingNumber,CallType,ChargeUnits,NetworkAndTrunkNode,TrunkGroupIdentity,EntityId,PersonalOrBusiness,WaitingDuration,EffectiveCallDuration,ComType,CostInfo,InitialDialledNumber,Carrier,UserToUserVolume,StartDateTime,Duration,RedirectedCallIndicator,Subaddress,HighLevelComp,CostType,TrunkIdentity,SpecificChargeInfo,BearerCapability,DataVolume,AdditionalEntityId,FirstCarrierCost,FirstCarrierCostT,SecondCarrierCost,SecondCarrierCostT,FacilityCost,FacilityCostT,FacturedCost,FacturedCostT,SubscriptionCost,SubscriptionCostT,FirstCarrierId,SecondCarrierId,FirstCarrierDirectionId,SecondCarrierDirectionId,FirstCarrierCcnId,SecondCarrierCcnId,ActingExtensionNumber,TransitTrunkGroupIdentity,NodeTimeOffset,ExternFacilities,InternFacilities,TicketOrigin,TimeDlt) VALUES (_binary'2009-11-17 11:11:12',1,'------------------------------','******************************',9,0,1000009,'90',986,2,9,6,1,0.00000000000000000e+000,'------------------------------','0',0,_binary'2009-11-17 11:11:12',6,1,NULL,0,2,596,'0000000',2,0,NULL,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,NULL,NULL,NULL,NULL,NULL,NULL,'******************************','32767',0,_binary 0x,_binary 0x,0,0)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '0x,_b
inary 0x,0,0)

-------------------------------------------------------------------------------

INSERT INTO Ticket (InitialStartDateTime,CallCount,CalledNumb' at line 1
mysql> INSERT INTO Ticket (InitialStartDateTime,CallCount,CalledNumber,CallingNu
mber,CallType,ChargeUnits,NetworkAndTrunkNode,TrunkGroupIdentity,EntityId,Person
alOrBusiness,WaitingDuration,EffectiveCallDuration,ComType,CostInfo,InitialDiall
edNumber,Carrier,UserToUserVolume,StartDateTime,Duration,RedirectedCallIndicator
,Subaddress,HighLevelComp,CostType,TrunkIdentity,SpecificChargeInfo,BearerCapabi
lity,DataVolume,AdditionalEntityId,FirstCarrierCost,FirstCarrierCostT,SecondCarr
ierCost,SecondCarrierCostT,FacilityCost,FacilityCostT,FacturedCost,FacturedCostT
,SubscriptionCost,SubscriptionCostT,FirstCarrierId,SecondCarrierId,FirstCarrierD
irectionId,SecondCarrierDirectionId,FirstCarrierCcnId,SecondCarrierCcnId,ActingE
xtensionNumber,TransitTrunkGroupIdentity,NodeTimeOffset,ExternFacilities,InternF
acilities,TicketOrigin,TimeDlt) VALUES (_binary'2009-11-17 11:11:12',1,'--------
----------------------','******************************',9,0,1000009,'90',986,2,
9,6,1,0.00000000000000000e+000,'------------------------------','0',0,_binary'20
09-11-17 11:11:12',6,1,NULL,0,2,596,'0000000',2,0,NULL,0.00000000000000000e+000,
0.00000000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.000
00000000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,0.00000000
000000000e+000,0.00000000000000000e+000,0.00000000000000000e+000,NULL,NULL,NULL,
NULL,NULL,NULL,'******************************','32767',0,_binary 0x0,_binary 0x
0,0,0);
Query OK, 1 row affected (0.06 sec)

Thank you for the feedback.

Please send us output of SELECT @@sql_mode from both 5.1 and 5.5 servers.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".