| Bug #28365 | Data corrupted | ||
|---|---|---|---|
| Submitted: | 11 May 2007 0:36 | Modified: | 23 Nov 2007 10:47 |
| Reporter: | Mikhail Diatchenko | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.15 | OS: | Windows (2000 Server SP4) |
| Assigned to: | CPU Architecture: | Any | |
[14 May 2007 10:25]
Tonci Grgin
Hi Mikhail and thanks for your report. Can you please provide me with fully functional test case exhibiting this error? Test case should include DDL and DML statements needed for reproduction too.
[14 Jun 2007 23:00]
Bugs System
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".
[6 Sep 2007 8:35]
Tonci Grgin
Mikhail, so was this serious or not? Please at least provide DDL statements in for suitable for import via mysql cl client...
[2 Oct 2007 20:58]
Mikhail Diatchenko
Sorry about such a delay. Here are the DDL statements: # # Table Objects for table groups # CREATE TABLE `groups` ( `GroupID` int(11) NOT NULL auto_increment, `Name` varchar(240) default NULL, `Code` varchar(50) default NULL, `Admin` int(11) default NULL, `MemberLimit` int(11) default NULL, `Options` varchar(20) default NULL, `DateDeleted` datetime default NULL, `DateCreated` datetime default NULL, `ParentGroupID` int(11) default NULL, PRIMARY KEY (`GroupID`), KEY `IX_ParentGroupID` (`ParentGroupID`,`GroupID`), KEY `IX_Admin` (`Admin`,`GroupID`), KEY `DateDeleted` (`DateDeleted`), KEY `IX_Code` (`Code`,`GroupID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # Table Objects for table users # CREATE TABLE `users` ( `UserID` int(11) NOT NULL auto_increment, `UserName` varchar(15) default NULL, `Password` varchar(15) default NULL, `FirstName` varchar(50) default NULL, `LastName` varchar(50) default NULL, `UserType` int(11) default NULL, `EMail` varchar(50) default NULL, `AddressID` int(10) default NULL, `DeliveryID` int(10) default NULL, `Region` int(11) default NULL, `PostStreet` varchar(30) default NULL, `PostSuburb` varchar(30) default NULL, `PostCity` varchar(20) default NULL, `PostCountry` int(11) default NULL, `DeliveryStreet` varchar(30) default NULL, `DeliverySuburb` varchar(30) default NULL, `DeliveryCity` varchar(20) default NULL, `DeliveryState` varchar(20) default NULL, `DeliveryCountry` int(11) default NULL, `DeliveryZip` varchar(15) default NULL, `HomePhoneCCode` char(3) default NULL, `HomePhoneACode` varchar(4) default NULL, `HomePhoneNumber` varchar(15) default NULL, `Company` varchar(50) default NULL, `CompanyStreet` varchar(30) default NULL, `CompanySuburb` varchar(30) default NULL, `CompanyCity` varchar(20) default NULL, `CompanyCountry` int(11) default NULL, `CompanyPhoneCCode` char(3) default NULL, `CompanyPhoneACode` varchar(4) default NULL, `CompanyPhoneNumber` varchar(15) default NULL, `CompanyFaxCCode` char(3) default NULL, `CompanyFaxACode` varchar(4) default NULL, `CompanyFaxNumber` varchar(15) default NULL, `Position` varchar(20) default NULL, `AddressLine1` varchar(40) default NULL, `AddressLine2` varchar(40) default NULL, `AddressLine3` varchar(40) default NULL, `State` varchar(20) default NULL, `Zip` varchar(15) default NULL, `PhoneNumber` varchar(15) default NULL, `FaxNumber` varchar(15) default NULL, `Info` tinyint(1) default NULL, `Theme` varchar(20) default NULL, `CardNo` varchar(35) default NULL, `ExpDate` datetime default NULL, `NameOnCard` varchar(50) default NULL, `GroupID` int(11) default NULL, `LastAccess` datetime default NULL, `LoginID` varchar(15) default NULL, `Created` datetime default NULL, `Express` tinyint(1) default '0', `IPAddress` varchar(15) default NULL, `PortalCodeOld` varchar(16) default NULL, `AccountType` int(11) default NULL, `Contacted` int(11) default NULL, `Referrer` varchar(255) default NULL, `Options` varchar(20) default NULL, `Activity` int(11) default NULL, `DateDeleted` datetime default NULL, `ParentUserID` int(11) default NULL, `LastLogin` datetime default NULL, `DistItemID` int(11) default NULL, `EmailInvalid` tinyint(1) default NULL, `Permissions` varchar(161) default NULL, `TimeZoneID` int(11) default NULL, `TimeZoneOffset` decimal(4,2) default NULL, `DefID` int(4) default NULL, `AuthCode` varchar(24) default NULL, PRIMARY KEY (`UserID`), KEY `IX_Username` (`UserName`,`UserID`), KEY `IX_LoginID` (`LoginID`,`UserID`), KEY `DateDeleted` (`DateDeleted`), KEY `IX_GroupID` (`GroupID`,`UserID`), KEY `IX_Email` (`EMail`,`UserID`), KEY `Created` (`Created`), KEY `LastAccess` (`LastAccess`), KEY `IX_DistItemID` (`DistItemID`,`UserID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # Table Objects for table products # CREATE TABLE `products` ( `ProductID` int(11) NOT NULL auto_increment, `Name` varchar(50) default NULL, `Description` varchar(255) default NULL, `HasProfiles` tinyint(1) NOT NULL default '0', `ProductCode` varchar(6) default NULL, `QuestionFile` varchar(20) default NULL, `SortIndex` smallint(6) default NULL, `SheriffProductID` varchar(24) default NULL, `Code1` varchar(19) default NULL, `Code2` varchar(19) default NULL, `Code3` varchar(19) default NULL, `Code4` varchar(19) default NULL, `ProductType` int(11) default NULL, `ProductClass` int(4) default NULL, `Hidden` tinyint(1) default '0', `PostageNZ` decimal(19,4) default NULL, `PostageIntl` decimal(19,4) default NULL, `MaxQty` int(4) default NULL, `PostageID` int(4) default NULL, `ExceedMaxQtyError` tinyint(1) default NULL, `CategoryID` int(11) default NULL, `Picture` varchar(128) default NULL, `ShortName` varchar(64) default NULL, `FullName` varchar(128) default NULL, `About` varchar(255) default NULL, `NewFlag` tinyint(1) default '0', `Page` varchar(16) default NULL, PRIMARY KEY (`ProductID`), KEY `IX_ProdutCode` (`ProductCode`,`ProductID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # Table Objects for table profilesused # CREATE TABLE `profilesused` ( `ProfileID` int(11) NOT NULL auto_increment, `UserID` int(11) default NULL, `ProductID` int(11) default NULL, `FirstName` varchar(50) default NULL, `LastName` varchar(50) default NULL, `Company` varchar(40) default NULL, `Position` varchar(40) default NULL, `GroupName` varchar(40) default NULL, `Date` datetime default NULL, `Gender` char(1) default NULL, `Age` tinyint(4) default NULL, `Nationality` varchar(24) default NULL, `Printed` tinyint(1) default NULL, `Answers` varchar(255) default NULL, `Complete` tinyint(1) default NULL, `Research` tinyint(1) default NULL, `Summary` tinyint(1) default NULL, `Viewed` tinyint(1) default NULL, `Demo` int(1) default '0', `Password` varchar(9) default NULL, `ResponseLinkID` int(11) default NULL, `Hidden` tinyint(1) default NULL, `DateDeleted` datetime default NULL, `Progress` int(4) default NULL, `ProcData` mediumtext, PRIMARY KEY (`ProfileID`), KEY `IX_UserID` (`UserID`,`ProfileID`), KEY `IX_ProductID` (`ProductID`,`ProfileID`), KEY `DateDeleted` (`DateDeleted`), KEY `IX_Password` (`Password`,`ProfileID`), KEY `ResponseLinkID` (`ResponseLinkID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # # Table Objects for table responselinks # CREATE TABLE `responselinks` ( `ResponseLinkID` int(11) NOT NULL auto_increment, `Title` varchar(100) default NULL, `Description` varchar(255) default NULL, `StartDate` datetime default NULL, `EndDate` datetime default NULL, `RespondentCopy` tinyint(1) NOT NULL default '0', `ActivityReport` int(11) default NULL, `CopyEmail` varchar(100) default NULL, `Code` varchar(16) default NULL, `UserID` int(11) NOT NULL default '0', `ProductID` int(11) NOT NULL default '0', `Enabled` tinyint(1) NOT NULL default '0', `Passwords` tinyint(1) NOT NULL default '0', `Instructions` longtext, `InstrSubj` varchar(100) default NULL, `DateCreated` datetime default NULL, `DateDeleted` datetime default NULL, `MemberLimit` int(11) default NULL, `DisableCookies` tinyint(1) default NULL, PRIMARY KEY (`ResponseLinkID`), KEY `UserID` (`UserID`), KEY `Code` (`Code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
[3 Oct 2007 5:46]
Tonci Grgin
Mikhail, thanks for info provided. Can you please try with latest MyODBC GA as we have made quite a lot of fixes lately and inform me of result? Also, if it still fails, please change the version tag.
[4 Nov 2007 0:00]
Bugs System
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".
[23 Nov 2007 10:47]
Tonci Grgin
I have lost several hours trying to fix malformed CSV files and to no avail... Please, if you want your reports to get processed, use standard mysqldump outputs and attach test cases. Closing the report now.
[23 Nov 2007 12:05]
Sveta Smirnova
tables
Attachment: bug28365.zip (application/x-zip-compressed, text), 26.46 KiB.
[23 Nov 2007 12:28]
Tonci Grgin
Thanks to Sveta I can finally see where the problem is...
Using CONCAT and DIV in conjunction with temporary tables led to resulting column being flagged as BINARY, which, in turn, led to AgeGroup being represented as "0x0 - 5":
mysql> SELECT ((Age DIV 5)*5) AS AgeValue, (CONCAT((Age DIV 5)*5, ' - ', (Age DI
V 5 +1)*5)) AS AgeGroup, COUNT(ProfilesUsed.ProfileID) AS ProfileCount
-> FROM (Groups RIGHT JOIN Users ON Groups.GroupID = Users.GroupID)
-> INNER JOIN (Products INNER JOIN ProfilesUsed ON Products.ProductID = Prof
ilesUsed.ProductID) ON Users.UserID = ProfilesUsed.UserID
-> LEFT JOIN ResponseLinks ON (ProfilesUsed.ResponseLinkID = ResponseLinks.R
esponseLinkID)
-> WHERE (ProfilesUsed.DateDeleted IS NULL) AND (Groups.DateDeleted IS NULL)
AND (Users.DateDeleted IS NULL) AND (Groups.Admin=51 OR Users.UserID=51)
-> GROUP BY AgeValue, AgeGroup
-> ORDER BY AgeValue, AgeGroup;
Field 1: `AgeValue`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONG
Collation: binary (63)
Length: 5
Max_length: 1
Decimals: 0
Flags: NUM
Field 2: `AgeGroup`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 14
Max_length: 5
Decimals: 0
Flags: BINARY NUM
Field 3: `ProfileCount`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 21
Max_length: 1
Decimals: 0
Flags: NOT_NULL NUM
+----------+----------+--------------+
| AgeValue | AgeGroup | ProfileCount |
+----------+----------+--------------+
| 0 | 0 - 5 | 3 |
+----------+----------+--------------+
1 row in set (0.00 sec)
and
SQLExecDirect:
In: hstmt = 0x00852098,
szSqlStr = "SELECT ((Age DIV 5)*5) AS AgeValue, (CONCAT((Age DIV 5...", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Data All:
"AgeValue", "AgeGroup", "ProfileCount"
0, 0x0 - 5, 3
1 row fetched from 3 columns.
This is all expected and documented behavior. If you want to work around it, you must use CAST( ... AS CHAR) for AgeValue column:
SELECT ((Age DIV 5)*5) AS AgeValue, CAST( CONCAT((Age DIV 5)*5, ' - ', (Age DIV 5 +1)*5) AS CHAR) AS AgeGroup, COUNT(ProfilesUsed.ProfileID) AS ProfileCount
FROM (Groups RIGHT JOIN Users ON Groups.GroupID = Users.GroupID)
INNER JOIN (Products INNER JOIN ProfilesUsed ON Products.ProductID = ProfilesUsed.ProductID) ON Users.UserID = ProfilesUsed.UserID
LEFT JOIN ResponseLinks ON (ProfilesUsed.ResponseLinkID = ResponseLinks.ResponseLinkID)
WHERE (ProfilesUsed.DateDeleted IS NULL) AND (Groups.DateDeleted IS NULL) AND (Users.DateDeleted IS NULL) AND (Groups.Admin=51 OR Users.UserID=51)
GROUP BY AgeValue, AgeGroup
ORDER BY AgeValue, AgeGroup
SQLExecDirect: In:hstmt = 0x00852098, szSqlStr = "SELECT ((Age DIV 5)*5) AS AgeValue, CAST( CONCAT((Age ...", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Data All:
"AgeValue", "AgeGroup", "ProfileCount"
0, "0 - 5", 3
1 row fetched from 3 columns.
[19 Dec 2007 11:53]
Tonci Grgin
Bug#33363 was marked as duplicate of this one.
Description: I have the an SQL statement* among other things it returns AgeGroup string (e.g. '0-5', '5-10', '10-15', etc) I load the results into a ADODB.Recordset. When i access the value of that column (e.g. Response.Write rs("AgeGroup")), ASP generates an error: Microsoft VBScript runtime (0x800A000D) Type mismatch I have tried converting the value to string using CStr(), and i get these values: ‰?5 ???5 etc. (instead of 0-5, 10-15, etc). Installing driver v3.51.12 solved the problem. Had the same issue with MySQL v5 and v4.1. *SQL Statement: SELECT ((Age DIV 5)*5) AS AgeValue, (CONCAT((Age DIV 5)*5, ' - ', (Age DIV 5 +1)*5)) AS AgeGroup, COUNT(ProfilesUsed.ProfileID) AS ProfileCount FROM (Groups RIGHT JOIN Users ON Groups.GroupID = Users.GroupID) INNER JOIN (Products INNER JOIN ProfilesUsed ON Products.ProductID = ProfilesUsed.ProductID) ON Users.UserID = ProfilesUsed.UserID LEFT JOIN ResponseLinks ON (ProfilesUsed.ResponseLinkID = ResponseLinks.ResponseLinkID) WHERE (ProfilesUsed.DateDeleted IS NULL) AND (Groups.DateDeleted IS NULL) AND (Users.DateDeleted IS NULL) AND (Groups.Admin=51 OR Users.UserID=51) GROUP BY AgeValue, AgeGroup ORDER BY AgeValue, AgeGroup. How to repeat: Error occurs every time.