I'm using a MySQL client connection to issue FLUSH TABLES WITH READ LOCK and then create an LVM snapshot. I'd like the higher-level script (Ruby) to capture the return code of the snapshot creation, but cannot determine how to capture that within the MySQL client. To simplify:
mysql> system pwd
/root
I would expect that system to return 0.
mysql> system foo
sh: 1: foo: not found
I would expect that system to return 127 in bash, etc.
Is there some way to retrieve that error code within the MySQL client and return it when the MySQL client exits, write it to a file to read by the controlling script, etc.
To run FTWRL in a context where you're also running system commands, and they depend on each other, indicates you want to flush, then snapshot, then decide what to do based on that before releasing the table lock.
You'll need to do that in the same script to make sure they work together, and you're trying to do that in the SQL script. It's far easier to do it the other way around, by running the SQL script from inside the system script, because you're making decisions based on shell conditions rather than SQL conditions.
Here's how I do that (in Perl):
#!/usr/bin/perl -w
use DBI;
my $d = DBI->connect('dbi:mysql:mysql','root','***');
$d->do('FLUSH TABLES WITH READ LOCK') or die ("Can't flush tables in MySQL");
# Here's the important bit: Capture the status in $s and branch on the result
my $s = system('lvm lvcreate -s -n lv_datadirbackup -L 4G /dev/VG_MYSQL/lv_datadir');
if($s == 0) {
print ("Snapshot created.\n");
$d->do('UNLOCK TABLES') or die("Can't unlock tables");
... # etc
That way you can create the table lock take the snapshot, and use your script to control what happens next based on the snapshot status.