Slashdot Mirror

you might not have been degrading (you kind of are with your last statement), but plenty of postgres, mssql, and oracle users are. i hate going into ##sql on freenode, asking a question about SQL 92 joins and I get kicked because I said I'm using MySQL. WTF does mysql have anything to do with SQL92 syntax?

and here's where you start to get slightly condescending...

When you clean up enough messes left by people who have no business touching a database, it gets fustrating. Especially when things like this are said:

and

Or are those examples only good for Fortune 10(tm) enterprises and not some piddly organization like yours.

i asked you a valid question, how does innodb not maintain referential integrity? you bring up myisam. i didn't mention myisam. i know myisam isn't ACID. i asked you how innodb isn't. and as for error reporting, http://dev.mysql.com/doc/refman/5.1/en/error-handling.html. so again, how does innodb not take referential integrity and error reporting seriously?

by the way, when you say

piddly organizations like yours

, are you referring to companies like google, yahoo, or alcatel-lucent? need i really go on?

so even though you say you aren't being condescending, you still maintained to act like an elitist pretentious ass. congratulations. thats exactly the type of attitude us mysql users are fed up with from you non-mysql users.

and yes, i agree with you that mysql's not null default data is complete bullshit, but that was never part of your original argument

you might not have been degrading (you kind of are with your last statement), but plenty of postgres, mssql, and oracle users are. i hate going into ##sql on freenode, asking a question about SQL 92 joins and I get kicked because I said I'm using MySQL. WTF does mysql have anything to do with SQL92 syntax?

and here's where you start to get slightly condescending...

When you clean up enough messes left by people who have no business touching a database, it gets fustrating. Especially when things like this are said:

and

Or are those examples only good for Fortune 10(tm) enterprises and not some piddly organization like yours.

i asked you a valid question, how does innodb not maintain referential integrity? you bring up myisam. i didn't mention myisam. i know myisam isn't ACID. i asked you how innodb isn't. and as for error reporting, http://dev.mysql.com/doc/refman/5.1/en/error-handling.html. so again, how does innodb not take referential integrity and error reporting seriously?

by the way, when you say

piddly organizations like yours

, are you referring to companies like google, yahoo, or alcatel-lucent? need i really go on?

so even though you say you aren't being condescending, you still maintained to act like an elitist pretentious ass. congratulations. thats exactly the type of attitude us mysql users are fed up with from you non-mysql users.

and yes, i agree with you that mysql's not null default data is complete bullshit, but that was never part of your original argument

And if there is a problem, it's well-documented.

Good luck finding it. MySQL has horrible documentation. The whole structure of it is a mess.

Plus, when you say "and if there is a problem, it is documented". Yeah, that is great, but most of the "well-documented" problems are long-standing bugs for insanely stupid shit.

It does make me laugh though, because honestly, I agree that mysql is well documented. Every random question I google for usually has a hit. And funny enough, the top listing is usually a page here--failing that, at least on the first page of results.

Why would I choose use anything else?

Because you know better.

And if there is a problem, it's well-documented.

Good luck finding it. MySQL has horrible documentation. The whole structure of it is a mess.

Plus, when you say "and if there is a problem, it is documented". Yeah, that is great, but most of the "well-documented" problems are long-standing bugs for insanely stupid shit.

It does make me laugh though, because honestly, I agree that mysql is well documented. Every random question I google for usually has a hit. And funny enough, the top listing is usually a page here--failing that, at least on the first page of results.

Why would I choose use anything else?

Because you know better.

At the moment, the top listing is www.mysql.com - Duh.

still, I'd prefer not to handle mysql structural data, as it mangles text removing spaces

What version of MySQL are you talking about? This page claims that MySQL 5.0.3 and later preserve trailing spaces in VARCHAR columns, and all versions preserve trailing spaces in TEXT columns.

Now, for the citation needed folks: http://sql-info.de/mysql/gotchas.html

From the linked page: "Update: Apparently this behaviour will be corrected in the planned 5.0 release", and it was.

by default (last I checked, and meaning you don't install innodb, etc), the tables will not be transactional.

Why would one assume InnoDB is turned off? MySQL binaries in Ubuntu 8.04's repository and the MySQL essentials installer for Windows include InnoDB. So does Go Daddy's hosting platform, if one believes this page deprecating Berkeley DB in favor of InnoDB.

Strict mode can be turned off by any application.

Per the MySQL manual: You could try not handing out the SUPER privilege like candy. This way, other apps can't SET GLOBAL sql_mode, and when they try to SET SESSION sql_mode, it won't affect any other apps' connections.

Well, for a start, this bug doesn't exactly inspire confidence.

Even less inspiring was this quote from the former founder that his "main reason for leaving was that I am not satisfied with the way the MySQL server has been developed, as can be seen on my previous blog post. In particular I would have like to see the server development to be moved to a true open development environment that would encourage outside participation and without any need of differentiation on the source code. Sun has been considering opening up the server development, but the pace has been too slow."

Of course, you should be downright worried by the following:

"The reason I am asking you to be very cautious about MySQL 5.1 is that there are still many known and unknown fatal bugs in the new features that are still not addressed.

To prove my points, here is some metrics and critical bugs for 5.1:

* We still have 20 known and tagged crashing and wrong result bugs in 5.1 35 more if we add the known crashing bugs from 5.0 that are likely to also be present in 5.1.
* We still have more than 180 serious bugs (P2) in 5.1. Some of these can be found here.
* We have more than 300 known and verified less critical bugs that are not going to be addressed soon. (The total reported number of bugs to the MySQL server is of course much larger)"

vacuuming (a PostgreSQL-specific database maintenance operation)

PostgreSQL isn't the only SQL DBMS that can defragment a database. SQLite has VACUUM, MySQL has OPTIMIZE TABLE that makes a dummy ALTERation, and Jet has "Compact and Repair".

Why ask Monty? Why not ask every major web player on the planet why they choose 'Microsoft Access level technology'.

You might find yourself laughed out of the room (as Microsoft would be, if they tried to sell Access to Google, Yahoo, Flickr, Slashdot, imdb, SABRE, YouTube, Wikipedia, NASA, US Census, ... and most major MySQL users).

http://mysql.com/why-mysql/case-studies/
http://mysql.com/customers/

Why ask Monty? Why not ask every major web player on the planet why they choose 'Microsoft Access level technology'.

You might find yourself laughed out of the room (as Microsoft would be, if they tried to sell Access to Google, Yahoo, Flickr, Slashdot, imdb, SABRE, YouTube, Wikipedia, NASA, US Census, ... and most major MySQL users).

http://mysql.com/why-mysql/case-studies/
http://mysql.com/customers/

So you're saying you can't call MySQL procedures from PHP? Those would be parameterized and typed. You should still check any character input, but you should be pretty safe from SQL injection at that point.

Sanja Byelkin http://dev.mysql.com/tech-resources/interviews/sanja.html and Sergey Brin http://en.wikipedia.org/wiki/Sergey_Brin did create Russian OS already.

The Perl foundation's move to Git took so long primarily because they had to gather decades of history from scattered sources: "Some of the patch sets were apparently recovered from old hard drives".

For instance, the MySQL project moved to Bazaar last year, apparently very smoothly. Getting started with Bazaar for MySQL.

Patch available here.

And SUN can always roll the communities code into its version. Without cost I might add. So what's the beef?

Not quite. SUN *could* snap up patches from the community that are floating around under the GPL -- but then SUN wouldn't own copyrights to all the code in MySQL (the individual authors whose code they snapped up would retain copyright over the snippets they had written).

This is why SUN's Contributor Agreement explicity states that the contributor must assign copyrights to SUN (you hereby assign to us joint ownership...). SUN wants to retain copyright (or at least joint licensing) to the entirety of the MySQL codebase so that they can sell closed source forks to companies wishing to release a product with MySQL embedded, without having to GPL their whole product, or any part thereof. IANAL.

Interesting. Tell that to Flickr, Facebook, Wikipedia, Google, Nokia and YouTube. Or, how about Slashdot and Digg - capable of bringing down moderately sized web sites with the click of a million mice?

Check out:
http://en.wikipedia.org/wiki/MySQL
http://www.mysql.com/customers/customer.php?id=281
http://www.mysql.com/news-and-events/generate-article.php?type=ss&id=slashdot

Just as a single example, what kind of scalability do most people need beyond Facebook and Wikipedia. I work for a very large internet company that has standardized on Oracle, and we have several well-paid DBAs who spend all day monitoring and tweaking our database servers. My previous job was a different large company that used MySQL as a back end for a very similar infrastructure (Java EE, Spring, Hibernate, Clustered in a similar way) with not a single full-time DBA (the helpdesk manager was the only real DBA other than the deployment engineers).

Now, I'm not a professional DBA. I'm just a programmer, but I was one of the maintainers of the MySQL server (I don't get to touch the Oracle servers here except on my local developers instance). I can tell you from personal experience that MySQL is easier to maintain and administer, faster to start up, and requires far fewer system resources to keep going. Judging by just the performance of Wikipedia and Facebook, it seems to perform quite well under heavy load. So, please tell me what basis you have to place MySQL out of the elite top-tier of database servers?

Interesting. Tell that to Flickr, Facebook, Wikipedia, Google, Nokia and YouTube. Or, how about Slashdot and Digg - capable of bringing down moderately sized web sites with the click of a million mice?

Check out:
http://en.wikipedia.org/wiki/MySQL
http://www.mysql.com/customers/customer.php?id=281
http://www.mysql.com/news-and-events/generate-article.php?type=ss&id=slashdot

Just as a single example, what kind of scalability do most people need beyond Facebook and Wikipedia. I work for a very large internet company that has standardized on Oracle, and we have several well-paid DBAs who spend all day monitoring and tweaking our database servers. My previous job was a different large company that used MySQL as a back end for a very similar infrastructure (Java EE, Spring, Hibernate, Clustered in a similar way) with not a single full-time DBA (the helpdesk manager was the only real DBA other than the deployment engineers).

Now, I'm not a professional DBA. I'm just a programmer, but I was one of the maintainers of the MySQL server (I don't get to touch the Oracle servers here except on my local developers instance). I can tell you from personal experience that MySQL is easier to maintain and administer, faster to start up, and requires far fewer system resources to keep going. Judging by just the performance of Wikipedia and Facebook, it seems to perform quite well under heavy load. So, please tell me what basis you have to place MySQL out of the elite top-tier of database servers?

Thank you for the link and your example code, that helped a lot. I want this to work, but I don't see enough performance improvement yet. Did I do this the way you imagined? I didn't try to finish the query on the full set because it took too long. Here I ran the join on 16 rows.

mysql> create table `geomaddress` (`address` geometry NOT NULL, SPATIAL KEY `address` (`address`)) ENGINE=MyISAM select GeomFromText( concat( 'point(', address, ' 0)' ) ) as `address` from address;
Query OK, 2124 rows affected (0.08 sec)

mysql> CREATE TABLE `polyrange` (`poly` geometry NOT NULL, `id_country` tinyint(3) unsigned default NULL, SPATIAL KEY `poly` (`poly`), KEY `id_country` (`id_country`) ) ENGINE=MyISAM select GeomFromText( concat( 'polygon(( ', begin_num, ' 0, ', end_num, ' 0, ', begin_num, ' 0 ))' ) ) as `poly`, id_country from range;
Query OK, 105920 rows affected (24.07 sec)

mysql> create table `geoma2` (`address` geometry NOT NULL, SPATIAL KEY `address` (`address`)) ENGINE=MyISAM select address from geomaddress limit 16;
Query OK, 16 rows affected (0.00 sec)

mysql> select r.id_country from geoma2 a join polyrange r on MBRContains(r.poly,a.address);
16 rows in set (6.08 sec)

According to EXPLAIN MySQL isn't using the spatial index. It doesn't matter whether I use on or where.

mysql> explain select r.id_country from geoma2 a join polyrange r on MBRContains(r.poly,a.address)\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: address
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*** 2. row ***
id: 1
select_type: SIMPLE
table: r
type: ALL
possible_keys: poly
key: NULL
key_len: NULL
ref: NULL
rows: 105920
Extra: Range checked for each record (index map: 0x1)
2 rows in set (0.00 sec)

6.08 secs/16 = 0.38 secs/row. That would be 13 mins 22 seconds for 2124 rows. Can I do better?

Thank you for the link and your example code, that helped a lot. I want this to work, but I don't see enough performance improvement yet. Did I do this the way you imagined? I didn't try to finish the query on the full set because it took too long. Here I ran the join on 16 rows.

mysql> create table `geomaddress` (`address` geometry NOT NULL, SPATIAL KEY `address` (`address`)) ENGINE=MyISAM select GeomFromText( concat( 'point(', address, ' 0)' ) ) as `address` from address;
Query OK, 2124 rows affected (0.08 sec)

mysql> CREATE TABLE `polyrange` (`poly` geometry NOT NULL, `id_country` tinyint(3) unsigned default NULL, SPATIAL KEY `poly` (`poly`), KEY `id_country` (`id_country`) ) ENGINE=MyISAM select GeomFromText( concat( 'polygon(( ', begin_num, ' 0, ', end_num, ' 0, ', begin_num, ' 0 ))' ) ) as `poly`, id_country from range;
Query OK, 105920 rows affected (24.07 sec)

mysql> create table `geoma2` (`address` geometry NOT NULL, SPATIAL KEY `address` (`address`)) ENGINE=MyISAM select address from geomaddress limit 16;
Query OK, 16 rows affected (0.00 sec)

mysql> select r.id_country from geoma2 a join polyrange r on MBRContains(r.poly,a.address);
16 rows in set (6.08 sec)

According to EXPLAIN MySQL isn't using the spatial index. It doesn't matter whether I use on or where.

mysql> explain select r.id_country from geoma2 a join polyrange r on MBRContains(r.poly,a.address)\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: address
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*** 2. row ***
id: 1
select_type: SIMPLE
table: r
type: ALL
possible_keys: poly
key: NULL
key_len: NULL
ref: NULL
rows: 105920
Extra: Range checked for each record (index map: 0x1)
2 rows in set (0.00 sec)

6.08 secs/16 = 0.38 secs/row. That would be 13 mins 22 seconds for 2124 rows. Can I do better?

Thank you for the link and your example code, that helped a lot. I want this to work, but I don't see enough performance improvement yet. Did I do this the way you imagined? I didn't try to finish the query on the full set because it took too long. Here I ran the join on 16 rows.

mysql> create table `geomaddress` (`address` geometry NOT NULL, SPATIAL KEY `address` (`address`)) ENGINE=MyISAM select GeomFromText( concat( 'point(', address, ' 0)' ) ) as `address` from address;
Query OK, 2124 rows affected (0.08 sec)

mysql> CREATE TABLE `polyrange` (`poly` geometry NOT NULL, `id_country` tinyint(3) unsigned default NULL, SPATIAL KEY `poly` (`poly`), KEY `id_country` (`id_country`) ) ENGINE=MyISAM select GeomFromText( concat( 'polygon(( ', begin_num, ' 0, ', end_num, ' 0, ', begin_num, ' 0 ))' ) ) as `poly`, id_country from range;
Query OK, 105920 rows affected (24.07 sec)

mysql> create table `geoma2` (`address` geometry NOT NULL, SPATIAL KEY `address` (`address`)) ENGINE=MyISAM select address from geomaddress limit 16;
Query OK, 16 rows affected (0.00 sec)

mysql> select r.id_country from geoma2 a join polyrange r on MBRContains(r.poly,a.address);
16 rows in set (6.08 sec)

According to EXPLAIN MySQL isn't using the spatial index. It doesn't matter whether I use on or where.

mysql> explain select r.id_country from geoma2 a join polyrange r on MBRContains(r.poly,a.address)\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: address
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*** 2. row ***
id: 1
select_type: SIMPLE
table: r
type: ALL
possible_keys: poly
key: NULL
key_len: NULL
ref: NULL
rows: 105920
Extra: Range checked for each record (index map: 0x1)
2 rows in set (0.00 sec)

6.08 secs/16 = 0.38 secs/row. That would be 13 mins 22 seconds for 2124 rows. Can I do better?

No GP wasn't me. MySQL 5.0.45 takes 10 mins 45 seconds on 2124 rows (vs. 0 mins 1.3 seconds for PostgreSQL 8.1.11). Slow enough? Also GP misstated: an int(10) unsigned isn't 10 bytes wide, it's 4 bytes wide.

Thank you for this interesting suggestion. I want it to work but I tried what you suggest and I don't see any difference in MySQL's query plan. I created exactly the same tables, except all columns of type int(10) unsigned converted to binary(4). The query plan is identical to the original.

I'd suggest looking into the polygon type. This article may be of some use.

The basic idea is that you create a polygon column and create an entry that corresponds to the start/end points for each row in your table, then you can run a query like this:

SELECT * FROM your_table WHERE MBRContains(polyfield, POINTFROMWKB(POINT(INET_ATON('1.2.3.4'), 0)));

As a point of reference, the above query runs in my local DB here in 0.02 seconds for any IP I can throw at it.

HTH.

1. The example setup is intended as a minimal demonstration, so I left out any keys on address.
2. I played a lot with where clauses, with no benefit. Anyway, shouldn't an industrial-strength RDBMS be able to interpret and optimize the simplest possible range join written as such?
3. I configured the strongest possible indices on range: 2 unique, 1 non-unique. Yes I tried FORCE INDEX, it made no difference (in execution time or EXPLAIN output).

1. The example setup is intended as a minimal demonstration, so I left out any keys on address.
2. I played a lot with where clauses, with no benefit. Anyway, shouldn't an industrial-strength RDBMS be able to interpret and optimize the simplest possible range join written as such?
3. I configured the strongest possible indices on range: 2 unique, 1 non-unique. Yes I tried FORCE INDEX, it made no difference (in execution time or EXPLAIN output).

1. The example setup is intended as a minimal demonstration, so I left out any keys on address.
2. I played a lot with where clauses, with no benefit. Anyway, shouldn't an industrial-strength RDBMS be able to interpret and optimize the simplest possible range join written as such?
3. I configured the strongest possible indices on range: 2 unique, 1 non-unique. Yes I tried FORCE INDEX, it made no difference (in execution time or EXPLAIN output).

• mysql forums post: optimize IP range join - postgresql is 496 times faster
• original mysql performance blog post

• mysql forums post: optimize IP range join - postgresql is 496 times faster
• original mysql performance blog post

• Postgres only just got ENUMs with 8.3 (2008-02-04) [1]
• Oracle still doesn't have them yet.
• MySQL has had them since 2002 if not earlier [2]

ENUMs are an absolutely essential feature when doing data modeling.

[1] http://www.postgresql.org/docs/8.3/static/release-8-3.html

[2] According to MySQL documentation on ENUMs; there are comments there from 2002.

There's also Securing the Initial MySQL Accounts, which provides a pretty straightforward* procedure.
—
*Or so we'd like to think. Of course, suggestions for improving the docs are welcome, via the bugs system. Please submit the bug using the category "MySQL Server:Documentation", and include the URL of the page with the issue, what you think is wrong with it, and what you think would make it better.

There's also Securing the Initial MySQL Accounts, which provides a pretty straightforward* procedure.
—
*Or so we'd like to think. Of course, suggestions for improving the docs are welcome, via the bugs system. Please submit the bug using the category "MySQL Server:Documentation", and include the URL of the page with the issue, what you think is wrong with it, and what you think would make it better.

• mysql forums post: optimize IP range join - postgresql is 496 times faster
• original mysql performance blog post

• mysql forums post: optimize IP range join - postgresql is 496 times faster
• original mysql performance blog post

• mysql forums post: optimize IP range join - postgresql is 496 times faster
• original mysql performance blog post

• mysql forums post: optimize IP range join - postgresql is 496 times faster
• original mysql performance blog post

http://bugs.mysql.com/bug.php?id=37937 ("Assertion bitmap_is_set_all (&table->s->all_set) fails in handler::ha_reset") is marked as a duplicate of Bug 37936.

On top of that, passwords are apparently specific to a certain host string. Bizarre. Do I need to use localhost for the actual machine name for local users? What about remote machine without a reverse DNS entry? What's the order of precedence for '%' vs a more specific name?

The manual seems to describe this in the connection access and request access sections. It answers your question on precedence (most specific to least specific, first match wins), but not the others. You would assume that localhost or 127.0.0.1 would work for connections initiated over the loopback interface (i.e. where the client application connects to localhost or 127.0.0.1), but that you would need to use the real public host name if the application is connecting via that. That's how networking works. Interesting point about reverse name lookups not working. Hopefully it's getting the name from there, and not just using whatever hostname the client asserts when it connects. You'd want to use IP addresses rather than host names to be safer. They mention that wildcard and host/subnet notation is supported.

Okay, so to change [the root password] do I use root@% or root@computer? How do I know I changed the right one and there isn't still some root@something entry? SHOW TABLES is easy enough, how about SHOW USERS? Nope, that's not it.

Well, you now know the precedence. The pages I linked to before also tell you that MySQL determines these based on the values in the user table (i.e. mysql.user), so you could just do a SELECT * FROM user WHERE User = 'root' to see what entries exist. From there, you would either have noticed that the passwords were hashed, and so found the password hashing section and executed an UPDATE user SET PASSWORD = PASSWORD(password) WHERE..., or you would have found the SET PASSWORD documentation, or a quick Google search would likely have yielded the more common advice to just do mysqladmin -u root password NEWPASSWORD.

On top of that, passwords are apparently specific to a certain host string. Bizarre. Do I need to use localhost for the actual machine name for local users? What about remote machine without a reverse DNS entry? What's the order of precedence for '%' vs a more specific name?

The manual seems to describe this in the connection access and request access sections. It answers your question on precedence (most specific to least specific, first match wins), but not the others. You would assume that localhost or 127.0.0.1 would work for connections initiated over the loopback interface (i.e. where the client application connects to localhost or 127.0.0.1), but that you would need to use the real public host name if the application is connecting via that. That's how networking works. Interesting point about reverse name lookups not working. Hopefully it's getting the name from there, and not just using whatever hostname the client asserts when it connects. You'd want to use IP addresses rather than host names to be safer. They mention that wildcard and host/subnet notation is supported.

Okay, so to change [the root password] do I use root@% or root@computer? How do I know I changed the right one and there isn't still some root@something entry? SHOW TABLES is easy enough, how about SHOW USERS? Nope, that's not it.

Well, you now know the precedence. The pages I linked to before also tell you that MySQL determines these based on the values in the user table (i.e. mysql.user), so you could just do a SELECT * FROM user WHERE User = 'root' to see what entries exist. From there, you would either have noticed that the passwords were hashed, and so found the password hashing section and executed an UPDATE user SET PASSWORD = PASSWORD(password) WHERE..., or you would have found the SET PASSWORD documentation, or a quick Google search would likely have yielded the more common advice to just do mysqladmin -u root password NEWPASSWORD.

On top of that, passwords are apparently specific to a certain host string. Bizarre. Do I need to use localhost for the actual machine name for local users? What about remote machine without a reverse DNS entry? What's the order of precedence for '%' vs a more specific name?

The manual seems to describe this in the connection access and request access sections. It answers your question on precedence (most specific to least specific, first match wins), but not the others. You would assume that localhost or 127.0.0.1 would work for connections initiated over the loopback interface (i.e. where the client application connects to localhost or 127.0.0.1), but that you would need to use the real public host name if the application is connecting via that. That's how networking works. Interesting point about reverse name lookups not working. Hopefully it's getting the name from there, and not just using whatever hostname the client asserts when it connects. You'd want to use IP addresses rather than host names to be safer. They mention that wildcard and host/subnet notation is supported.

Okay, so to change [the root password] do I use root@% or root@computer? How do I know I changed the right one and there isn't still some root@something entry? SHOW TABLES is easy enough, how about SHOW USERS? Nope, that's not it.

Well, you now know the precedence. The pages I linked to before also tell you that MySQL determines these based on the values in the user table (i.e. mysql.user), so you could just do a SELECT * FROM user WHERE User = 'root' to see what entries exist. From there, you would either have noticed that the passwords were hashed, and so found the password hashing section and executed an UPDATE user SET PASSWORD = PASSWORD(password) WHERE..., or you would have found the SET PASSWORD documentation, or a quick Google search would likely have yielded the more common advice to just do mysqladmin -u root password NEWPASSWORD.

On top of that, passwords are apparently specific to a certain host string. Bizarre. Do I need to use localhost for the actual machine name for local users? What about remote machine without a reverse DNS entry? What's the order of precedence for '%' vs a more specific name?

The manual seems to describe this in the connection access and request access sections. It answers your question on precedence (most specific to least specific, first match wins), but not the others. You would assume that localhost or 127.0.0.1 would work for connections initiated over the loopback interface (i.e. where the client application connects to localhost or 127.0.0.1), but that you would need to use the real public host name if the application is connecting via that. That's how networking works. Interesting point about reverse name lookups not working. Hopefully it's getting the name from there, and not just using whatever hostname the client asserts when it connects. You'd want to use IP addresses rather than host names to be safer. They mention that wildcard and host/subnet notation is supported.

Okay, so to change [the root password] do I use root@% or root@computer? How do I know I changed the right one and there isn't still some root@something entry? SHOW TABLES is easy enough, how about SHOW USERS? Nope, that's not it.

Well, you now know the precedence. The pages I linked to before also tell you that MySQL determines these based on the values in the user table (i.e. mysql.user), so you could just do a SELECT * FROM user WHERE User = 'root' to see what entries exist. From there, you would either have noticed that the passwords were hashed, and so found the password hashing section and executed an UPDATE user SET PASSWORD = PASSWORD(password) WHERE..., or you would have found the SET PASSWORD documentation, or a quick Google search would likely have yielded the more common advice to just do mysqladmin -u root password NEWPASSWORD.

Except MySQL supports permissions for user accounts, which includes restricting the use of the "drop table" command.

Any reference book that I have purchased previously has ended up collecting dust on my bookshelf. I guess they are good for reading when I am away from the computer, but the internet is better resource for programming, especially with software that is as well documented as MySQL.

I would have a hard time believing this book is better than the Sun documentation.

I would really like to read about a specific bug Monty spoke of, but it looks like they secured this bug information from the public. Bug #37936 "Crash when executing a query containing date expressions" http://bugs.mysql.com/bug.php?id=37936 It seems to me like this is an extremely major bug, and would keep me from using 5.1 altogether.

You call software that can bring down multiple slaves with a drop table statement in a transaction production-ready? Have fun with that.

The thing is that it isn't "Google Beta Quality." It truly is beta quality in the traditional sense. How would you like to cause a crash by simply looping over a view in a stored procedure?

With all due respect to Ryan, I agree with Monty here. Not just 5.1, but the whole MySQL 5.x tree has been shipping with release critical bugs. What's more, some of those bugs (like the one that has been open since 2003) have lowered priority now because "people know about them."

It sounds like MySQL could benefit from a more debian-like release criteria.

JSF, RichFaces, Hibernate, MySQL, developed on NetBeans and served by Apache TomCat on CentOS for a state government contract.

We have to train ourselves, but that's half the fun.

The other half will be when we pull the plug on one legacy Oracle database with a per CPU cycle license the state is paying an obscene amount of money for.

mysql> select * from Users where Username=quote("victim") and Password=quote("x' OR 'e' = 'e");
Empty set (0.00 sec)

why? so you can save .025 seconds by not reloading an index or menu?

there's no good reason to use frames or even iframes in a modern site. it's bad for search engine indexing, and it's bad for usability. that is why major API documentation sites like those for the YUI Library, MySQL, PHP, and even MSDN do not use frames in their layout.

even if there were a need to keep persistent layout elements, you can use AJAX to simulate all of the desirable behaviors of frames/iframes without the drawbacks.