MySQL Server 5.1 install fails to set EVENT and TRIGGER permissions in remote root login

I’ve just installed MySQL Server 5.1 on a Windows Server 2003 machine to create an environment similar to the one being used by a customer reporting a problem with our software.

The installer presented an option to allow remote access using the root login and I clicked this to enable it.

The first thing I needed to do after installing MySQL was restore a copy of the customer’s database. I used MySQL Workbench from my desktop PC to do this but the restore failed with an error message indicating that the root@% account did not have TRIGGER permission to create the triggers used by one of the tables being restored.

When I checked the account’s global privileges I could see that all were enabled except for EVENT and TRIGGER. The root@localhost account had all global privileges enabled. So, it appears that the installer had not correctly enabled all global privileges on the remote account.

I thought I’d try enabling the EVENT and TRIGGER privileges from within MySQL Workbench on my desktop PC but, logically enough, this isn’t permitted. So, I installed Workbench on the server only to find that the program would not start as it expects a version of Windows that equates to Windows 7, or higher. In desperation I tried installing a copy of the now defunct MySQL Administrator but this would not show the root@% account.

So I did what I should have done first:

1. Open a command window on the server.

2. Change the current directory to C:\Program Files\MySQL\MySQL Server 5.1\bin

3. Run mysql.exe with the command line arguments for the root account and password: mysql -uroot -paardvark

4. At the MySQL prompt enter the command to grant the TRIGGER global privilege to the root account when used remotely:

GRANT TRIGGER ON *.* TO 'root'@'%';

and again for the EVENT privilege:

GRANT EVENT ON *.* TO 'root'@'%';

I retried the restore in MySQL Workbench and it completed with no errors.