monline

If you've an autoincrement column in a table and you want to set the next value to the max value + 1 from the actual values in the table, then use something like this ...

It's quite easy actually. In MySQL you can disable all foreign key checks by setting the foreign_key_checks variable to zero.

This blog post helped me a lot ... probably it'd have taken a few hours to figure out why mysql_connect() dies with a "No such file or directory" error. I had a feeling (while I was reading the readme in the Mac installer of the currently available MySQL server, which got obviously outdated years ago) that there're problems to be expected with the MySQL install.

A straight to the point guide on how to configure the built-in Apache (where are the config and command files), etc. As it turns out, it already ships with PHP support and it was already running for some reason. Never mind. The first thing I did after installing the OS was to set up a firewall (ipfw in this case), so having an Apache on the inside did really not matter much (probably took a few MBs of the memory). But now I need it anyway, so I'm not very upset that I didn't have to install it from scratch.

This took me almost an hour to figure out. Using MySQL server v5.0.51a (to be exact, the package version was "5.0.51a-24+lenny2~bpo40+1-log" on a Debian Etch server and using the backports repository) I tried to create a new table from the results of a query (you know: CREATE TABLE ... SELECT ...). I watched the MySQL process state as it changed from "copying tmp table" to "sorting" and then to "sending data". However during the latter I did not see any significant CPU or I/O usage and the SQL statement never finished. I waited a very long time and nothing.

The information_schema in MySQL 5.x gives you the opportunity to dig into the database metadata. Unfortunately it's slow like hell. I've attempted to write a query that returns all foreign keys that have no index defined on their columns. It seems that information_schema needs a lot more tuning, because my query results in the following error message:

I've created a set of SQL queries that can be run on the information_schema of a MySQL 5.x+ server to compare the structure of two databases and create the SQL commands needed to convert the structure of one database to the structure of the other. Use them at your own risk. They worked for me, but they might not cover all aspects of MySQL database comparison. I've tested them on an 5.0.51 MySQL server running on Debian 5.x.

By default MySQL stores InnoDB tables and indexes in a single file called "ibdata1" (which is on Debian in the /var/lib/mysql directory). Unfortunately this file is never "recycled", thus once space is allocated to it, it's never released (see this feature request). Not even after dropping the database that held the given table. However there's an InnoDB option called innodb_file_per_table that allows you to store each InnoDB table in a separate file (just as MyISAM does) and after dropping the table the file is removed and thus the occupied space is recycled.

Xaprb published a nice method for creating sequentially increasing identifiers for groups of records in a query.

A few notes on a couple of gotchas with using batch inserts in Hibernate with a MySQL database.