Bug #49172 Scheduling a backup job in Administrator does not work: No creates, no inserts.

• Submitted: 27 Nov 2009 21:11
• Modified: 30 Nov 2009 15:44
• Reporter: Cheong Gan
• Status: Duplicate
• Category: MySQL Administrator
• Severity: S1 (Critical)
• Version: 8.41
• OS: Linux (ubuntu5.4)
• CPU Architecture: Any
• Tags: Backup, Connection, mabackup, password:NO, quick fix, schedule, workaround

[27 Nov 2009 21:11] Cheong Gan

Description:
Scheduling a backup job in MySQL Administrator does not work: the output file contains only headers with creates and inserts. The same backup project works fine with [Start Backup].

How to repeat:
1. Setup a backup project with a pre-defined connection name/profile with user and password supplied.
2. [Start Backup] works fine: the file contains creates and inserts.
3. Schedule the project to run at a specified time.
4. **ERROR** At specified time, a backup file is generated but contains only the header part: no create, no insert.

If you enable the mysql.log (re /etc/mysql/my.cnf) you see:
Access denied for user 'root@localhost' (using password: NO).

It is as though password was not used by the cron job.

5. Try to invoke "mabackup -d <mydir> -x bkup -c bkupConnection bkupJob" results in 'Segmentation failure'. 

6 invoke "sudo mabackup -d <mydir> -x bkup -c bkupConnection bkupJob" results in 'Backup error: Cannot set ANSI quotes'.

[27 Nov 2009 21:16] Cheong Gan

The output file did not contain any Create's nor Insert's. The last line in the file is: 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

[28 Nov 2009 14:14] Valeriy Kravchuk

Looks like a duplicate of bug #36986.

[30 Nov 2009 15:44] Cheong Gan

WORKAROUND:

The problem is because the password in the saved connection profile was not set when password_storage_type=1 which explains the 'Access denied ...(using password: NO)' error in /var/log/mysql/mysql.log.

I workaround this problem by changing ~/.mysqlgui/mysqlx_user_connectinos.xml as follows:
- change <password_storage_type>1</password_storage_type> to <password_storage_type>0</password_storage_type>
- change </password> to <password>myrootpassword</password>

So my mysqlx_user_connectinos.xml  looks like:
<?xml version="1.0"?>
<user_connections>
  <last_connection>0</last_connection>
  <password_storage_type>0</password_storage_type>
  <user_connection>
    <connection_name>myConnection0</connection_name>
    <username>root</username>
    <hostname>localhost</hostname>
    <port>3306</port>
    <schema>myschema</schema>
    <advanced_options/>
    <storage_path></storage_path>
    <notes></notes>
    <connection_type>0</connection_type>
    <storage_type>1</storage_type>
    <password_storage_type>0</password_storage_type>
    <password>myrootpassword</password>
  </user_connection>
</user_connections>

Note: you have to look hard to locate hidden dir ./mysqlgui. Mine is /home/myuser/.mysqlgui

Hope this help others looking for a quick fix.