Category Archives: mysql

Ditch MySQL now


MySQL was “bought” by Sun last year, as you may know. The fact that this is significant sort of illustrates one of the reasons I didn’t like MySQL from the moment I met it in 1997 or so. It’s only like half in the game as far as being free and open source. It is tied way too much to MySQL AB, the company, and the way it is licensed is totally shady. Anyway, now Sun is dying and Larry Ellison is about to scoop it up. Monty, the MySQL guy is whining to the Internet, trying to get the European Commission to block the deal. It’s a sad story, but if Oracle doesn’t buy Sun, they will just need to get bought by someone else or go out of business. The real sad part of the story is the death of Sun, one of the original great Internet companies (my other favorite being SGI which met its fate years ago). In any case, MySQL’s future is dark and cloudy.

That is why I’m just going to go ahead and plug my favorite database, PostgreSQL. You should check it out, it’s awesome. It is way more powerful than MySQL and the licensing is better. It was built from the ground up to be a real database, not some tinkering project like MySQL. If you are running a project, especially an open source one, that is dependent on a database, you should take a serious look at PostgreSQL and consider making it your database of choice.

Advertisements

Dropping tables in MySQL

Say you have access to a MySQL database, and you need to restore it from a previous copy. You could just run the SQL file from the dump, but that is slightly unclean, since it won’t delete tables that it doesn’t know about. Now, let’s assume you don’t have full admin access to the server, so you can’t drop and recreate the db. You have to drop all the tables. Here’s what happens:

mysql> show tables;
+——————————+
| Tables_in_cem |
+——————————+
| academic_periods |
| admin_leases |
| admin_services |
| admins |
| course_adds_kills |
| course_gateways |
| course_organizations |
| course_services |
| course_types |
| courses |
| instructors |
| organization_course_gateways |
| organization_gateways |
| organizations |
| roles |
| section_params |
| sections |
| services |
| transactions |
+——————————+
19 rows in set (0.00 sec)

mysql> drop table academic_periods, admin_leases, admin_services, admins, course_adds_kills, course_gateways, course_organizations, course_services, course_types, courses, instructors, organization_course_gateways, organization_gateways, organizations, roles, section_params, sections, services, transactions;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql> show tables;
+——————+
| Tables_in_cem |
+——————+
| academic_periods |
| course_gateways |
| course_types |
| courses |
| organizations |
| services |
+——————+
6 rows in set (0.00 sec)

mysql> drop table academic_periods, admin_leases, admin_services, admins, course_adds_kills, course_gateways, course_organizations, course_services, course_types, courses, instructors, organization_course_gateways, organization_gateways, organizations, roles, section_params, sections, services, transactions;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql> show tables;
+——————+
| Tables_in_cem |
+——————+
| academic_periods |
| course_types |
+——————+
2 rows in set (0.00 sec)

mysql> drop table academic_periods, admin_leases, admin_services, admins, course_adds_kills, course_gateways, course_organizations, course_services, course_types, courses, instructors, organization_course_gateways, organization_gateways, organizations, roles, section_params, sections, services, transactions;
ERROR 1051 (42S02): Unknown table ‘admin_leases,admin_services,admins,course_adds_kills,course_gateways,course_organizations,course_ser’

mysql> show tables;
Empty set (0.00 sec)

Mission accomplished. You have to run the command n number of times, where n is the depth of the foreign key constraints, but keep feeding mysql the command and the tables will eventually be gone. I would contend that a “real” database would work out the dependencies and drop them all in one feel swoop.

MySQL Feature Bingo

Using newish features in MySQL is kind of like playing bingo. Especially if you don’t control the version of the server you are working with. Here are some excerpts from the official manual that illustrate this point:

  • Support for triggers is included beginning with MySQL 5.0.2.
  • Before MySQL 5.0.10, triggers cannot contain direct references to tables by name.
  • The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. It was added in MySQL 5.0.17.
  • From MySQL 5.0.17 on, MySQL checks trigger privileges like this:
  • In MySQL Server 3.23.44 and up, the InnoDB storage engine supports checking of foreign key constraints
  • As of MySQL 4.1.1, mysqldump generates dump files that take advantage of this capability automatically when they are reloaded.
  • Views (including updatable views) are implemented beginning with MySQL Server 5.0.1.
  • Beginning with MySQL 5.0.3, the grant system takes stored routines into account as follows:
  • As of MySQL 5.0.1, a stored procedure or function is associated with a particular database.
  • Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. This means the client must use a client library from a version of MySQL at least as recent as 4.1.

…and the list goes on of course.

MySQL FTW

This little gem I encountered today speaks for itself…

mysql> select count(*) from Principal where status = ‘ ‘ and status = ”;
+———-+
| count(*) |
+———-+
| 39021 |
+———-+
1 row in set (0.35 sec)

Another post about my favorite database

Relational databases are really cool. When you get experienced with them, you can leverage all sorts of features to make sure your data is solid. A good database schema will provide a good check against buggy code trying to mess up your data. One of my favorite ways to enforce integrity is the usage of NOT NULL constraints. I recently added some to a MySQL database I’m working on, and I noticed something unusual…

mysql> alter table SERVICES add SERVICE_KEY varchar(64) NOT NULL UNIQUE;
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> describe SERVICES;
+------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+----------------+
| SERVICE_ID | bigint(20) | NO | PRI | NULL | auto_increment |
| DESCRIPTION | varchar(128) | YES | | NULL | |
| COMMENTS | varchar(64) | YES | | NULL | |
| ACTIVE | varchar(3) | YES | | NULL | |
| CREATED_BY | varchar(128) | YES | | NULL | |
| CREATED_DATE | datetime | YES | | NULL | |
| UPDATED_BY | varchar(128) | YES | | NULL | |
| UPDATED_DATE | datetime | YES | | NULL | |
| TYPE | varchar(4) | YES | | NULL | |
| URL | varchar(1024) | YES | | NULL | |
| MAX_TRAN_THREADS | smallint(6) | YES | | NULL | |
| MAX_SYNC_THREADS | smallint(6) | YES | | NULL | |
| SERVICE_KEY | varchar(64) | NO | UNI | | |
+------------------+---------------+------+-----+---------+----------------+

As you may notice, the default value is somewhat nebulous on this new column. Normally the default will show NULL, which is good. In this case, it looks like it is either “nothing” which I would equate to NULL, or ”, which is horrible because it breaks the whole purpose of having the constraint. Since I trust MySQL to generally do the wrong thing, I test it:

mysql> insert into SERVICES (SERVICE_ID) values (NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)

And of course, it is taking my null SERVICE_KEY value and turning it into ”. So now, if my app is broken and trying to insert null into SERVICE_KEY, MySQL will save the day and “fix” the data, completely eliminating any errors from being produced that would have revealed the bug in my code.

To quote the famous fashion designer Mugatu, I feel like I’m taking crazy pills! Everyone seems to love MySQL, but it seems completely obvious to me that it is not even a candidate to be considered when writing anything moderately serious.

Two more gripes about mysql

First, this:
mysql> grant all on confluence_2_6_0 to dmitsuper@’%’ identified by ‘xxx’;
[alwold@bug ~]$ gunzip -c confluence_2_3_0.dump.gz | mysql -u dmitsuper -p confluence_2_6_0
Enter password: 
ERROR 1044 (42000): Access denied for user ‘dmitsuper’@’%’ to database ‘confluence_2_6_0’
Second, when I go to fix it (change confluence_2_6_0 to confluence_2_6_0.*), I can use the history in the mysql client to recall the grant line containing the password (so the password is stored in a file somewhere).