Results 1 to 23 of 23
-
10-09-2006, 09:07 AM #1WHT Addict
- 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.
-
10-09-2006, 09:30 AM #2Web Hosting Master
- 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.”
-
10-10-2006, 01:49 PM #3Junior Guru Wannabe
- 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
-
10-10-2006, 02:11 PM #4Web Hosting Master
- 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.”
-
10-10-2006, 02:27 PM #5Web Hosting Guru
- 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
-
10-10-2006, 04:33 PM #6Junior Guru Wannabe
- 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.
-
10-10-2006, 04:40 PM #7Web Hosting Evangelist
- Join Date
- Apr 2005
- Posts
- 522
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.
-
10-10-2006, 05:59 PM #8Web Hosting Master
- 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.”
-
10-10-2006, 06:18 PM #9Web Hosting Master
- 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-2006, 06:46 PM #10Web Hosting Evangelist
- Join Date
- Apr 2005
- Posts
- 522
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"?
-
10-10-2006, 07:57 PM #11Web Hosting Master
- 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.
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.”
-
10-10-2006, 08:50 PM #12Web Hosting Evangelist
- Join Date
- Apr 2005
- Posts
- 522
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.
-
10-10-2006, 08:53 PM #13Web Hosting Master
- Join Date
- Jun 2004
- Posts
- 789
Originally Posted by maxymizer
-
10-10-2006, 09:00 PM #14Web Hosting Evangelist
- Join Date
- Apr 2005
- Posts
- 522
Originally Posted by pergesu
I'm sorry PostgreSQL guys, you win.
MySQL
-
10-10-2006, 09:04 PM #15Web Hosting Master
- Join Date
- Nov 2001
- Location
- Vancouver
- Posts
- 2,422
Originally Posted by maxymizer
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.”
-
10-10-2006, 09:11 PM #16Web Hosting Master
- 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.
-
10-10-2006, 09:30 PM #17Web Hosting Master
- 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.”
-
10-10-2006, 09:37 PM #18Web Hosting Evangelist
- Join Date
- Apr 2005
- Posts
- 522
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?
-
10-10-2006, 09:44 PM #19Web Hosting Master
- Join Date
- Nov 2003
- Posts
- 691
Originally Posted by mwatkins
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.
-
10-10-2006, 11:02 PM #20Web Hosting Master
- Join Date
- Nov 2001
- Location
- Vancouver
- Posts
- 2,422
Originally Posted by brendandonhu
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.”
-
10-11-2006, 02:27 AM #21Web Hosting Master
- Join Date
- Jun 2004
- Posts
- 789
Originally Posted by maxymizer
-
10-11-2006, 05:52 AM #22Web Hosting Evangelist
- Join Date
- Apr 2005
- Posts
- 522
It's ok "dude", you won, eod, everybody happy.
-
10-12-2006, 03:54 PM #23Web Hosting Guru
- 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.