Wikir1. Installing on Mac OS X (10.4.x)
Using DarwinPorts, mysql (v4.1.15) does not install proper permissions. Out of the box it won't start - not sure if this is a ports-specific problem.
You get this:
051025 03:31:10 mysqld started 051025 3:31:10 [Warning] Setting lower_case_table_names=2 because file system for /opt/local/var/db/mysql/ is case insensitive 051025 3:31:10 [ERROR] Can't start server : Bind on unix socket: Permission denied 051025 3:31:10 [ERROR] Do you already have another mysqld server running on socket: /opt/local/var/run/mysqld/mysqld.sock ? 051025 3:31:10 [ERROR] Aborting 051025 3:31:10 [Note] /opt/local/libexec/mysqld: Shutdown complete 051025 03:31:10 mysqld ended
The solution is:
sudo chown mysql:mysql /opt/local/var/run/mysqld sudo chown -R mysql:mysql /opt/local/var/db/mysql
2. MySQL Cursors (v4.1.x)
In a nutshell, they don't exist. If you are writing a long-running tool, then you have some problems. Say you are using Python. Say you decide to save some memory on your tool, so you decide to use server side cursors and use a generator to efficiently fetch a batch of rows at a time before processing them one at a time. So far so good.
Sadly, if your processing takes a significant amount of time, your connection may be "lost" and a helpful error message returned:
Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query') in <bound method SSCursor.__del__ of <MySQLdb.cursors.SSCursor object at 0x41e11f4c>> ignored
What this actually means is that the MySQL server timed out trying to write to your client. Apparently, there is no actual server side cursor - it just sits there and tries to write to your client - it has nothing to do with how many rows you specify. So, if you take your sweet time processing each row, you will experience write timeouts. The way around this, which is still a hack, is to boost net_write_timeout to a sufficiently large value.
3. Reconnect bug in MySQLdb 1.2.1 (Python client for 4.1.x)
By default, a new connection has autocommit turned off. However, if you experience a connection timeout like this:
Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')
the mysql client will internally reconnect. The next call to this connection object will succeed. Unfortunately, in this case autocommit will be on! This is ultra crappy, bizarre and dangerous. The work around it to use the init_command keyword in the connection constructor to ensure autocommit stays off.
c = MySQLdb.Connection(user='...', passwd='...', db='testing', host='127.0.0.1', init_command="set autocommit=0")
Further reconnect attempts will keep autocommit turned off.
I should further caution that the MySQLdb code actually runs the init_command before it sets autocommit. The code checks the capabilities of the mysql server and if it appears to support transactions, it turns autocommit off.
This situation is somewhat mitigated after MySQL 5.0.3 because auto reconnect is turned off by default.