| Bug #5203 | mysql returns unterminated unicode strings | ||
|---|---|---|---|
| Submitted: | 25 Aug 2004 11:44 | Modified: | 30 Nov 2004 16:24 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.1.3b | OS: | Windows (Windows XP) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[25 Aug 2004 23:15]
Hartmut Holzgraefe
I have not been able to reproduce this behaviour yet. Can you please upload table dumps or at least add EXPLAIN output for the example queries?
[26 Aug 2004 10:26]
[ name withheld ]
I change the VARCHAR length to 120 and everything works now. Because VARCHAR is limited to 255 characters, normally 1 Byte for each character. So this results to a maximum of 255 Bytes. Unicode uses 2 Byte per character, so may there be the limit at 128 characters in unicode???
[2 Sep 2004 15:42]
[ name withheld ]
Run this SQL Script to reproduce the error
-- Create tables
CREATE TABLE tblType (
TypeID INT NOT NULL AUTO_INCREMENT UNIQUE,
TypeName VARCHAR (250) UNICODE NOT NULL,
TypeDesc VARCHAR (250) UNICODE NOT NULL,
TypeStatus TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (TypeID)
) TYPE=InnoDB;
CREATE TABLE tblTestTypeLnk (
TestID INT NOT NULL,
TypeID INT NOT NULL,
TestCount INT NOT NULL
) TYPE=InnoDB;
CREATE TABLE tblTest (
TestID INT NOT NULL AUTO_INCREMENT UNIQUE,
TestName VARCHAR (250) UNICODE NOT NULL,
TestDesc VARCHAR (250) UNICODE NOT NULL,
PRIMARY KEY (TestID)
) TYPE=InnoDB;
CREATE TABLE tblPerson (
PersonID INT NOT NULL AUTO_INCREMENT UNIQUE,
PersonName VARCHAR (250) UNICODE NOT NULL,
PRIMARY KEY (PersonID)
) TYPE=InnoDB;
CREATE TABLE tblPersonTestLnk (
PersonID INT NOT NULL,
TestID INT NOT NULL
) TYPE=InnoDB;
-- add foreign keys
ALTER TABLE tblTestTypeLnk ADD CONSTRAINT FK_tblTTL_tblType FOREIGN KEY (TypeID) REFERENCES tblType(TypeID);
ALTER TABLE tblTestTypeLnk ADD CONSTRAINT FK_tblTTL_tblTest FOREIGN KEY (TestID) REFERENCES tblTest(TestID);
ALTER TABLE tblPersonTestLnk ADD CONSTRAINT FK_tblPTL_tblPerson FOREIGN KEY (PersonID) REFERENCES tblPerson(PersonID);
ALTER TABLE tblPersonTestLnk ADD CONSTRAINT FK_tblPTL_tblTest FOREIGN KEY (TestID) REFERENCES tblTest(TestID);
-- insert some test data
INSERT INTO tblType ( TypeID, TypeName, TypeDesc, TypeStatus ) VALUES ( 1, 'Dong', 'Dong desc', 1 );
INSERT INTO tblType ( TypeID, TypeName, TypeDesc, TypeStatus ) VALUES ( 2, 'Bla', 'Bla desc', 1 );
INSERT INTO tblTest ( TestID, TestName, TestDesc ) VALUES ( 1, 'Test1', 'testdesc1' );
INSERT INTO tblTest ( TestID, TestName, TestDesc ) VALUES ( 2, 'Test2', 'testdesc2' );
INSERT INTO tblTest ( TestID, TestName, TestDesc ) VALUES ( 3, 'Test3', 'testdesc3' );
INSERT INTO tblTest ( TestID, TestName, TestDesc ) VALUES ( 4, 'Test4', 'testdesc4' );
INSERT INTO tblTest ( TestID, TestName, TestDesc ) VALUES ( 5, 'Test5', 'testdesc5' );
INSERT INTO tblTest ( TestID, TestName, TestDesc ) VALUES ( 6, 'Test6', 'testdesc6' );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 1, 1 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 2, 1 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 3, 1 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 4, 1 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 5, 1 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 6, 1 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 1, 2 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 2, 2 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 3, 2 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 4, 2 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 5, 2 );
INSERT INTO tblTestTypeLnk ( TestID, TypeID ) VALUES ( 6, 2 );
INSERT INTO tblPerson ( PersonID, PersonName ) VALUES ( 1, 'Paul');
INSERT INTO tblPersonTestLnk ( PersonID, TestID ) VALUES ( 1, 1 );
INSERT INTO tblPersonTestLnk ( PersonID, TestID ) VALUES ( 1, 2 );
INSERT INTO tblPersonTestLnk ( PersonID, TestID ) VALUES ( 1, 3 );
INSERT INTO tblPersonTestLnk ( PersonID, TestID ) VALUES ( 1, 4 );
INSERT INTO tblPersonTestLnk ( PersonID, TestID ) VALUES ( 1, 5 );
INSERT INTO tblPersonTestLnk ( PersonID, TestID ) VALUES ( 1, 6 );
-- lauch this query to get the error
SELECT typename, typedesc, testname, testdesc FROM tbltest, tbltype, tbltesttypelnk, tblpersontestlnk WHERE tbltest.TestID = tbltesttypelnk.TestID AND tbltype.TypeID = tbltesttypelnk.TypeID AND tbltest.Testid = tblpersontestlnk.PersonID = tblpersontestlnk.PersonID = 1 ORDER BY typename DESC
[11 Sep 2004 3:34]
MySQL Verification Team
I tested your script against the 4.1.4 and I got an empty set:
mysql> SELECT typename, typedesc, testname, testdesc FROM tbltest, tbltype,
-> tbltesttypelnk, tblpersontestlnk WHERE tbltest.TestID = tbltesttypelnk.TestID
-> AND tbltype.TypeID = tbltesttypelnk.TypeID AND tbltest.Testid =
-> tblpersontestlnk.PersonID = tblpersontestlnk.PersonID = 1 ORDER BY typename DESC;
Empty set (0.01 sec)
[13 Sep 2004 7:07]
[ name withheld ]
Sorry there was an error in the query? I corrected the query: SELECT typename, typedesc, testname, testdesc FROM tbltest, tbltype, tbltesttypelnk, tblpersontestlnk WHERE tbltest.TestID = tbltesttypelnk.TestID AND tbltype.TypeID = tbltesttypelnk.TypeID AND tbltest.Testid = tblpersontestlnk.PersonID AND tblpersontestlnk.PersonID = 1 ORDER BY typename DESC
[30 Oct 2004 16:24]
Heikki Tuuri
Hi! Please test again with 4.1.7. Look from http://www.innodb.com if you need to rebuild your table. Regards, Heikki
[14 Feb 2005 22:54]
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".
Description: If I try to order a query like the one below by an unicode varchar field, it seems that the returned strings are notterminated. If I order the same query by a non unicode field it works correctly. This effect occurs, if I do a join and sort over a unicode column where multiple records, of a single record in the original table, have to be displayed do perform the join. If i do such order queries not using joins, everything goes correct. mysql> SELECT MyName, TransTime FROM tblMy, tbllog WHERE tbllog.MyId = tblMy.MyId ORDER BY 1 DESC limit 0,10; +----------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -------------+---------------------+ | MyName | TransTime | +----------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -------------+---------------------+ | MyData 3TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 10:33:02 | | MyData 3TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 17:02:59 | | MyData 3TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-24 18:23:09 | | MyData 2TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 17:09:36 | | MyData 2TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 17:10:08 | | MyData 2TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 17:10:29 | | MyData 1TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 10:33:02 | | MyData 1TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 17:02:59 | | MyData 1TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 17:10:08 | | MyData 1TB †††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† †††††††††††† | 2004-08-23 17:10:29 | +----------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -------------+---------------------+ 10 rows in set (0.00 sec) mysql> SELECT MyName, TransTime FROM tblMy, tbllog WHERE tbllog.MyId = tblMy.MyId ORDER BY 2 DESC limit 0,10; +-----------------+---------------------+ | MyName | TransTime | +-----------------+---------------------+ | Audio | 2004-08-25 09:33:56 | | Audio | 2004-08-25 09:31:05 | | Audio | 2004-08-25 09:30:55 | | Audio | 2004-08-25 09:30:49 | | MyData 3TB | 2004-08-24 18:23:09 | | MyData2 | 2004-08-24 12:31:21 | | Realtime | 2004-08-23 17:48:35 | | MyData 1TB | 2004-08-23 17:10:29 | | Realtime | 2004-08-23 17:10:29 | | MyData2 | 2004-08-23 17:10:29 | +-----------------+---------------------+ 10 rows in set (0.00 sec) // field definition CREATE TABLE tblMy ( ... MyName VARCHAR (250) UNICODE NOT NULL, ... PRIMARY KEY (MyID) ) TYPE=InnoDB; for example if I add to the query "LENGTH(MyName)" it works sometimes, or returns the value 500 for the column "LENGTH(MyName)" How to repeat: see description Suggested fix: strings have to be terminated correctly