SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict KJedi's Avatar
    Join Date
    Sep 2005
    Location
    Ukraine, Nikolaev
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL SELECT bug with InnoDB tables

    Hi,
    I have quite complex DB, it was running OK when there was a few testing records. To get more realistic results, I populated tables with random data (around 260 records in CategoryTag table)
    Here is the table:
    Code MySQL:
    SHOW CREATE TABLE `CategoryTag`;
    ----------------
    CREATE TABLE `CategoryTag` (
    `categoryTagID` int(11) NOT NULL auto_increment,
    `intName` varchar(45) collate utf8_unicode_ci NOT NULL,
    `categoryID` int(5) NOT NULL,
    `type` enum('fabric','style') collate utf8_unicode_ci NOT NULL default 'fabric',
    `text` varchar(55) collate utf8_unicode_ci NOT NULL,
    `clicksNum` bigint(20) NOT NULL,
    `popularity` float(2,1) NOT NULL,
    PRIMARY KEY (`categoryTagID`),
    UNIQUE KEY `intName` (`intName`),
    KEY `fk_CategoryTag_Category` (`categoryID`),
    CONSTRAINT `fk_CategoryTag_Category` FOREIGN KEY (`categoryID`) REFERENCES `category` (`categoryID`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    When I run
    Code MySQL:
    SELECT * FROM `CategoryTag`
    It outputs normally.
    When I run query like
    Code MySQL:
    SELECT * FROM `CategoryTag` WHERE `categoryTagID`=1;
    SELECT * FROM `CategoryTag` WHERE `type`='fabric';
    and similar it's OK.
    An when I run queries:
    Code MySQL:
    SELECT * FROM `CategoryTag` WHERE `intName`='bla-bla';
    SELECT * FROM `CategoryTag` WHERE `text`='bla-bla';
    MySQL returns zero rows!
    Sure, there is record with 'bla-bla'

    I never came across with such issue!
    I tried optimizing table, deleting indexes, adding them back - nothing helps, it still returns zero rows!

    Any ideas are highly appreciated!

    Info from phpMyadmin:
    Server version: 5.0.51b-community
    MySQL charset: UTF-8 Unicode (utf8)
    MySQL connection collation - utf8_unicode_ci
    It SHOULD work - but it doesn't!

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,909
    Mentioned
    96 Post(s)
    Tagged
    0 Thread(s)
    Have you double checked that bla-bla actually exists? I tried that table structure as a test. It only matches with that query when the TEXT field is "bla-bla", any other text in the entry and it doesn't match as it's not exactly like "bla-bla"
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you provide some INSERT statements with your table, including ones that should match your query but don't?

    have you also tried:
    Code:
    SELECT * FROM `CategoryTag` WHERE `intName` like '%bla-bla%'; SELECT * FROM `CategoryTag` WHERE `TEXT` LIKE '%bla-bla%';
    to make sure you don't have any special characters in the row throwing it off.

    also note that TEXT is a reserved word and you should not use it as the name of a column.

  4. #4
    SitePoint Addict KJedi's Avatar
    Join Date
    Sep 2005
    Location
    Ukraine, Nikolaev
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I don't have this problem on the tables with other structure, but having the same VARCHAR fields named "text".

    Yes, I know it's reserved word, but putting it into back-quotes works fine.

    Yes, I double-checked this, you can see it in attached phpMyAdmin screens First is "browse" tab; second - results of query. You can see, that the word "reconstructions" really exists in the intName column! Why doesn't it return it???

    However, when I add 1-2 rows, it works fine. When I import around 270 rows, it becomes strange....
    Attached Images Attached Images

  5. #5
    SitePoint Addict KJedi's Avatar
    Join Date
    Sep 2005
    Location
    Ukraine, Nikolaev
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    have you also tried:
    Code:
    SELECT * FROM `CategoryTag` WHERE `intName` like '%bla-bla%'; SELECT * FROM `CategoryTag` WHERE `TEXT` LIKE '%bla-bla%';
    to make sure you don't have any special characters in the row throwing it off.
    YES!!! That worked!!! I was populating the records taking random words from ductionary file with \r\n endings, but I splitted records by \n, so \r remained not visible to anyone.

    Thanks a lot for pointing me to the right direction!

    I should have checked that thing first before posting this silly post. Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •