ads' corner

Ever tried to rename a database in MySQL? Obviously not.

Why not? MySQL does not support renaming databases. Funny, eh?

They once tried to implement this feature but failed and removed the code again. Note the sudden major feature shifts in minor releases.

So, how to rename a database? Dump the database, create a new database and import the dump. Both steps can be executed together:

mysqldump old_database | mysql new_database

A website shows the following error message:

SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_1898_0.MYI'; try to repair it

So, MySQL: which table is to repair? The error message is not a table, it's just the temp file.

Stupid and useless error message ...

This blog post of mine about the new MySQL Emulation Layer in PostgreSQL 9.0 was (more of less obvious) an april joke. Most readers have guessed right.

On the other hand the idea, that this joke is based, is very real: during the last conferences and exhibitions which I have attended, a lot visitors asked us (the PostgreSQL booth staff) about the future of MySQL.

Guess what - we don't know! Please ask Larry ;-)

The next question was usually about porting a MySQL database to PostgreSQL. Many companies selling products with MySQL inside are irritated - and frustrated: "Can i still buy a MySQL license in a year or two?" - "And if yes: how many bucks will Oracle request for this?". "Will Oracle still offer support or will they try to convience everyone to use Oracle database?" At this point many companies start to look for some other database - and PostgreSQL is there, happy to help.

I would like to thank David Fetter at this point, he did the proofreading for my post.

PostgreSQL 9.0.0, released today, contains the MySQL Emulation Layer.

To enable this feature, set the mysql_compatible option GUC to "on".

postgres=# SELECT * FROM pg_settings WHERE name = 'mysql_compatible';
-[ RECORD 1 ]----------------------------------------------------------------
name         | mysql_compatible
setting       | ON
unit            |
category     | Version AND Platform Compatibility / Other Platforms AND Clients
short_desc | Enable MySQL Emulation Layer
extra_desc |
context       | backend
vartype      | bool
SOURCE        | DEFAULT
min_val      |
max_val     |

Happened on IRC, just minutes ago:

22:35:09 -!- michi7x7 has joined #postgresql
22:35:14 < michi7x7> hi all
22:35:44 < michi7x7> i have a problem with a mysql-query, but #mysql is full of mysql-lover-noobs

Users start asking the PostgreSQL-guys for MySQL-support. Times are changing ;-)

If you run MySQL and this error occurs:

 SQLSTATE[HY000]: General error: 3 Error writing file '/tmp/MYh0VgDS' (Errcode: 28)

then check if your server isn't using the famous "overflow" filesystem for /tmp, limited to 1 MB space.

The MySQL error message is - as usual - misleading and non-descriptive.

There are many other possible causes for this error code, so this one here might just be a hint.

After issuing a REPAIR TABLE:

 mysql> repair table search_archive_count_checked;
+----------------------------------+--------+----------+----------------------------------------------+
| Table                            | Op     | Msg_type | Msg_text                                     |
+----------------------------------+--------+----------+----------------------------------------------+
| xxx.search_archive_count_checked | repair | warning  | Number of rows changed from 335540 to 365874 |
| xxx.search_archive_count_checked | repair | status   | OK                                           |
+----------------------------------+--------+----------+----------------------------------------------+
2 rows in set (36.75 sec)

I'm surprised: this is the first database which is able to create rows out of nothing. Violation of the conversation of energy data, eh? ;-)

Now Kris Kennaway did some tests on his own and posted the results to the FreeBSD performance list. The resulting graphs look very similar.

Got a nice problem: a table, which has the following primary key: id, id.

You can use or dump this table without problems ... but you cannot reinsert the dump into the same (4.1) or a newer (5.0) mysql version.

This is the table dump, created with "SHOW CREATE TABLE tablename":

----- cut -----
CREATE TABLE tablename (
  id int(11) NOT NULL auto_increment,
  title_en text,
  ...
  PRIMARY KEY  (id,id)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
----- cut -----

Now i try to reinsert this table into another database:

----- cut -----
mysql> CREATE TABLE tablename (
    ->   id int(11) NOT NULL auto_increment,
    ->   title_en text,
  ...
    ->   PRIMARY KEY  (id,id)
    -> ) TYPE=MyISAM DEFAULT CHARSET=latin1;
ERROR 1060 (42S21): Duplicate column name 'id'
----- cut -----

Postgres Storage Engine for MySQL, Stranger then Fiction

Imagine, which problems could be solved in an instance ...

Best thing: the collapses for requests per seconds if Mysql has a concurrency greater than 7. Oh, and by the way, they don't state the table type for Mysql so we don't know, if it is the fast and forgetfully MyIsam or the better but slower InnoDB type.

I just answered a question in the usenet about triggers in Mysql and discovered something very scary:

You can have only one trigger action time and event at a time for a table. So for example you can only have one "BEFORE INSERT" trigger.

The article can be found at:

http://www.devx.com/dbzone/Article/20743/0/page/1