MySQL: mysqldump and dropping database connections

I'm using MySQL as the database to store a lot of measurement data coming from the mobile phones of around twenty users. The server backend is written in Java and it connects to the database using a connection pool. Every day at 3AM a copy of the entire database is made using mysqldump.

As time progressed we would see an enormous amount of SQL error messages in the logs, about the connection between Java and the database being dropped, and 'too many connections' errors from the database pool. Lines like these:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.

The last packet successfully received from the server was 8,532 milliseconds ago.  The last packet sent successfully to the server was 8,532 milliseconds ago.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

At first I couldn't figure out what the cause was (tried to fiddle with validation queries, pool sizes etc) until I finally noticed that all these errors happened just after 3AM. That's where it clicked: mysqldump by default locks the entire database you're trying to backup for the duration of the backup, to ensure that it generates a consistent dump. As the size of the data grew (the backup is about 750 Mb at the moment) it took mysqldump a lot longer to generate the backup.

This progressed up to the point where the Java connections to the database would timeout causing all kinds of problems. Furthermore the 'too many connections' errors were cause by the fact that whilst the database is locked, the SQL connections block, and are therefor not returned to the pool: thus the pool keeps on creating new connections until it maxes out and  starts throwing these errors.

The solution for me was to add the following parameters to the mysqldump command:

mysqldump --quick --single-transaction ...

The --quick options just speeds up the process of retrieving the data from the database (see here), the real improvement comes from the --single-transaction option: instead of locking the database, mysqldump then uses the transaction mechanism to isolate the backup from concurrent changes to the database. Note that this only works if you're using InnoDB tables.

ImageView.setImageURI does not work for file URIs

The following code fails:

File f = ...

ImageView imageView = ...

imgView.setImageURI(Uri.fromFile(f));

Workaround:

imgView.setImageDrawable(Drawable.createFromPath(f.getAbsolutePath()));

MySQL root password reset

The other day MySQL Workbench somehow set the password for the localhost root account from None to some other value while I tried to change the password of another user. Don't know exactly what happened, but to rest the password you should:

1. start mysql
2. type "use mysql"
3. type "update user set password='' where user='root';"
4. type "flush privileges" 

After this, the root account has no password. Note that without step 4 changes to the user table will have no effect whatsoever. Took me 30 minutes to find out ...

N97 Homescreen API for Python

The Homescreen API lets you put small widgets on the home screen of the Nokia N97. I'm not sure if any other phone supports the API or if any other phone ever will, but for a project we created a Python 2.0 extension that lets you create such a widget from Python.

The SIS file can be found here, the sources can be found here.

Android Bluetooth scanning

Discovering Bluetooth devices in Android is fairly simple, there are a lot of examples on the internet. What not all of those examples show you, and what got me into trouble was the bold bit in the code snippet below: I didn't realise that BluetoothAdapter.getDefaultAdapter() will return null when the user turns Bluetooth of in the phone settings. That got me a nice NullPointerException ...

BluetoothAdapter bluetoothAdapter = BluetoothAdapter.getDefaultAdapter();
if (bluetoothAdapter == null) {
    // bluetooth disabled/not available
}
else {
    context.registerReceiver(new BluetoothListener(), new IntentFilter(BluetoothDevice.ACTION_FOUND));

    bluetoothAdapter.startDiscovery();
}

private class BluetoothListener extends BroadcastReceiver {
    @Override
    public void onReceive(Context c, Intent intent) {
        BluetoothDevice rbd = (BluetoothDevice)intent.getParcelableExtra(BluetoothDevice.EXTRA_DEVICE);

        // ...
    }
}

Download an Android apk file from your site

This will probably never happen to you but, if you're in a similar situation as me, which is:

• you've got a website
• the website uses HTTPS and a self-signed certificate
• you try to download an APK file from that website directly onto your phone
  

Then you'll notice that the download starts but never completes, you'll get a perpetual "Waiting for data connection..." message. Took me a while to find out, but this is a known issue.

Install Android USB Driver for Windows to connect to a HTC Wildfire

I tried to install the Android USB Driver for Windows whilst my HTC Wildfire was connected to my PC. This failed. After some head-scratching and searching, I found out that I had to change the android_winusb.inf file (which is part of the installation files) so that it would recognize the device:

[Google.NTx86]
; HTC Dream
%SingleAdbInterface% = USB_Install, USB\VID_0BB4&PID_0C01
%CompositeAdbInterface% = USB_Install, USB\VID_0BB4&PID_0C02&MI_01
%SingleBootLoaderInterface% = USB_Install, USB\VID_0BB4&PID_0FFF
; HTC Magic
%CompositeAdbInterface% = USB_Install, USB\VID_0BB4&PID_0C03&MI_01
; HTC Wildfire
%SingleAdbInterface% = USB_Install, USB\VID_0BB4&PID_0C8B
%CompositeAdbInterface% = USB_Install, USB\VID_0BB4&PID_0C8B&MI_01
%SingleBootLoaderInterface% = USB_Install, USB\VID_0BB4&PID_0C8B

The bold lines are the ones I added: VID_0BB4 means Vendor ID = HTC, PID_0C8B means Product ID = Wildfire.