Results 1 to 23 of 23
  1. #1
    Join Date
    Oct 2002
    Location
    Hong Kong
    Posts
    166

    SELECT =... SELECT LIKE not the same result?

    I have a table urls with the following data in column url running on MySQL

    //components/com_productbook/img_thumbnails/tn_toyota1.jpg

    select * from urls where url = '//components/com_productbook/img_thumbnails/tn_toyota1.jpg';

    This returns an empty set (very strange)

    select * from urls where url LIKE '%//components/com_productbook/img_thumbnails/tn_toyota1.jpg';

    This returns one record. My question.. why does the first not return
    anything?

    -= EDIT =-

    Its working again. Placing an EXPLAIN in front of the query yielded the following message
    "Impossible WHERE noticed after reading const tables", leaving to think that the primary
    key is corrupt. After fixing the index both queries returned the same results.
    Last edited by woutersteven; 10-09-2006 at 09:15 AM.

  2. #2
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    And people wonder why I don't trust, or use, MySql.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  3. #3
    Join Date
    Jun 2006
    Posts
    76
    lol MySql is the best
    listen , i faced this problem before , nothing wrong with MySQL , the problem in the way you enter the data ... looks you copy these urls from excel file or something like is , so when you copy these urls .. you copy it with some invisible characters , so when you try to select its give you empty result , to fix that you need to export your database and open it with Notepad and then you will able to see that characters , so remove them by find and replace , and re import your table , or you can used the function chop to get these characters off ..

    Regards
    Majdi Al-Omari

  4. #4
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    LOL Postgres scales better and has been a 'grown up' database longer. For years Postgres has had advanced SQL and other features (including Unicode support). Sure, MySql is catching up, but why take second best?

    Performance under load: http://tweakers.net/reviews/646/14

    Your comment about 'invisible' characters is a valid issue, but doesn't appear to be the case here. Assuming the original poster didn't change the data at all, if the issue were "invisible characters", the OP's actions with the index wouldn't have changed the outcome.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  5. #5
    Join Date
    Dec 2005
    Location
    London
    Posts
    279
    We only use MSSQL, especially with the Express Edition now avaliable! Nothing can match it in features!
    Walker

  6. #6
    Join Date
    Sep 2006
    Posts
    34
    *cough*

    the PostgreSQL developers themself said they are using mySQL for some of their website stuff (ad tracking I think it was) because PostgreSQL could not handle the load.

  7. #7
    If MySQL is so bad compared to PostgreSQL - how come that PostgreSQL isn't the most popular db out there?

    Regarding MySQL and the load - Flickr uses MySQL, and that site's got some load on its back.

  8. #8
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    MySql gained popularity because it was simple and, for whatever reason, adopted early by a great many web hosts.

    For years you couldn't even do moderately complex operations with MySql. That it only fairly recently can do things like stored procedures is a sign of immaturity.

    There's nothing wrong with using MySql, but I have very good reasons for not using it.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  9. #9
    Join Date
    Nov 2003
    Posts
    691
    Somehow, I don't think bashing MySQL for its stored procedure support is going to help this person with their SELECT statement...

  10. #10
    Bashing any product won't help, but MySQL is far from bad as some people are trying to point out. Yes there are better products (better in SOME aspects), no one argues that. Calling MySQL immature because it didn't offer advanced features from the start doesn't mean the product is immature, it's widely adopted and used at sites with more than high load and it works like a charm. Also, the problem with the query seems solved. Should we rename this topic to "MySQL vs Postgres"?

  11. #11
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    This didn't start out as a MySql bashing discussion, I merely commented that this sort of thing:

    Its working again. Placing an EXPLAIN in front of the query yielded the following message "Impossible WHERE noticed after reading const tables", leaving to think that the primary key is corrupt. After fixing the index both queries returned the same results.
    ... is not the sign of a product I'd use.


    Then someone else decided to postulate a different explanation for the discrepancy, but I pointed out that if rebuilding a corrupt index fixed the issue, as the OP poined out, the issue couldn't possibly be due to 'invisible' characters.

    To me a database that doesn't return a result, under simple conditions, that it should, is... immature. Your mileage may vary.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  12. #12
    There can be any number of reasons why an index went corrupt, few of them not even databases' fault. Labeling mysql immature based on this bad experience does not mean it's immature as there are thousands of sites using it without problems. I have never encountered an "immature" problem, but I did optimize my mysql installation to suit my needs.

    If one toyota car breaks down, it doesn't mean that all of them will. I'm not a fanboy of mysql by any means, but I find it at least funny when I read such a comment..ok, you don't use mysql. There's no reason why you should be labeling it immature.

  13. #13
    Quote Originally Posted by maxymizer
    If MySQL is so bad compared to PostgreSQL - how come that PostgreSQL isn't the most popular db out there?
    I could never quite peg it before, but now I understand why Windows is head-and-shoulders the best operating system around.

  14. #14
    Quote Originally Posted by pergesu
    I could never quite peg it before, but now I understand why Windows is head-and-shoulders the best operating system around.


    I'm sorry PostgreSQL guys, you win.

    MySQL

  15. #15
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    Quote Originally Posted by maxymizer
    I'm not a fanboy of mysql by any means, but I find it at least funny when I read such a comment..ok, you don't use mysql. There's no reason why you should be labeling it immature.
    Using the terminology "immature" with MySql is more than appropriate. I didn't say it was unusable; just that I won't use it or recommend it.

    I'm not a "fanboy" of Postgres either. Its a good, solid, reliable tool. The relative immaturity of MySql has nothing to do with the relative maturity of Postgres or Sybase or Oracle or MS SQL Server or... they all have their issues.

    But, and this is important, all of the above relational databases have supported much more of the SQL standard for many years longer than MySql. They've supported large and small applications using those features for years. Hundreds of thousands of developer-years have gone into those applications, providing the opportunity to surface bugs and deficiencies.

    So yes, compared to any of the others, MySql is indeed "immature".

    But note, I didn't say "unusable".
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  16. #16
    Join Date
    Nov 2003
    Posts
    691
    It's pretty amusing how convinced you are that this is the fault of the "immature" MySQL when you really have no idea whether it was caused by filesystem problems, corruption, or a thousand other possibilities. If this is actually a MySQL bug, feel free to post the source code to back it up.
    Last edited by brendandonhu; 10-10-2006 at 09:15 PM.

  17. #17
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    Read the original post and in particular the resolution the poster noted.

    I think its amusing that you'd defend MySql without knowing the source of the fault.

    I don't really have to defend my contention that MySql is immature - its a simple fact, nor should this reality cause anyone any grief.

    My original comment was appropriate; I was indicating what I would do and have done, and I think the issue the OP noted was a fine example of bizzare and unpredictable behaviour that one really shouldn't be seeing from a mature database.

    I can't help that MySql fanboys started popping out of the woodwork now, can I?

    Cheers. Time to end this thread, the OP is long gone!
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  18. #18
    Yes, a database that's been used with enterprise level sites is immature.
    The key point to prove it is that an index went corrupt. But it's databases' fault, OS couldn't have had any impact on this, or a bad sector on hdd.

    Come on..let's find all database glitches that can be caused by other factors and let's call them all immature.

    Let's call all OS' immature because they go down or are hackable. Let's call all hardware immature because they break down.

    </sarcasm>

    See my/brendandonhu' point?

  19. #19
    Join Date
    Nov 2003
    Posts
    691
    Quote Originally Posted by mwatkins
    I think its amusing that you'd defend MySql without knowing the source of the fault.
    I did not say whether or not the problem was MySQL's fault, since I have no way of knowing that. I believe you declared that the problem must be caused by MySQL when you really have no way of knowing whether that's true or not.

    My original comment was appropriate; I was indicating what I would do and have done, and I think the issue the OP noted was a fine example of bizzare and unpredictable behaviour that one really shouldn't be seeing from a mature database.
    As has been said several times, you do not know whether this problem was caused by the database, filesystem, hardware, or any other problem. If this is a MySQL bug, why don't you post the source and show us?

  20. #20
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,422
    Quote Originally Posted by brendandonhu
    It's pretty amusing how convinced you are that this is the fault of the "immature" MySQL when you really have no idea whether it was caused by filesystem problems, corruption, or a thousand other possibilities. If this is actually a MySQL bug, feel free to post the source code to back it up.
    The focus of my comments on immaturity center around how MySql failed to alert the user of the problem. SELECT something from a DB and you *must* expect an accurate result from the DB that meets your criteria. The OP did a query that on any functioning DB would return a result yet his LIKE query returned nothing. That is not a mature DB.

    Did the DB return the right result? No.

    Did the DB return an error condition? No. It magically overlooked whatever error condition was present and didn't complain at all. Perhaps you consider that "robust" but I do not.

    EXPLAIN knew there was something wrong but a straight query just failed silently... that is the very definition of immature when it comes to what should be a mature, ACID compliant, SQL database in this day and age.

    Imagine a database that returns inconsistent result sets that doesn't get detected (as the OP did). No errors. See any reason to feel extra confident in that?

    Ooops, sorry Mr. Patient, we didn't know you'd already had 100 doses. We'll send your widow a bill. Oops, sorry Mr. Grieving Widow, our MySql database didn't return a result, and as a result, we sent two planes into one another. Sorry, sonny, your mommy won't be coming home any more - she drank from a botulism-laden carton of mango juice and died; its not our fault really, we did a SELECT * from MANGO_SHIPMENT_DETAIL where product_delivered like '%mango juice%' ... and nothing was returned. Oops.

    The balance of my comments regarding maturity have nothing to do with this specific error but everything to do with the actual, physical, maturity of MySql compared to many other relational databases in common use today. That MySql has only recently introduced certain key features is not an opinion but a statement of fact. Whether you use those features or not is irrelevant, for my purposes - and yes I've written many a stored proc over the years - maturity of the broader feature set matters.
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  21. #21
    Quote Originally Posted by maxymizer


    I'm sorry PostgreSQL guys, you win.

    MySQL
    You wrote the non sequitor, dude. I just satirized it.

  22. #22
    It's ok "dude", you won, eod, everybody happy.

  23. #23
    Join Date
    Nov 2005
    Posts
    282
    http://www-css.fnal.gov/dsg/external...-vs-mysql.html

    Eventually though, both will probably be the same, from my reading mysql went for the ease of use + speed route and later features - and PostgreSQL went features first and then focused on speed.

    So in comparing databases - just match what you want to do with whatever database you are considering, and go with the database that is most optimal for your use.

    As of this point, both database engines are 'immature' for different reasons.

Posting Permissions

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