Filed under howtos

AWS RDS and MySQL's SUPER privilege

While moving a RackTables database from a classic MySQL server to Amazon's RDS service, I found two things:

  • RackTables requires SUPER privileges on the MySQL DB to create triggers that will run as a different MySQL user than its own and
  • AWS RDS does not provide SUPER privileges.

When I tried to import the data from the old Racktables DB into the RDS DB, I got the error You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable).

AWS provides a setting to enable functions, procedures, and triggers for an instance of MySQL on Amazon RDS.

I followed the guide and created a database parameter group with the option log_bin_trust_function_creators = true for the Racktables DB.

After rerunning the import, the error changed slightly to You do not have the SUPER privilege and binary logging is enabled.

This is caused by the DEFINER statements in the DB dump:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW

On the RDS instance, there was no root user, but a rootuser so I changed the .sql file with the dump to read

/*!50003 CREATE*/ /*!50017 DEFINER=`rootuser`@`localhost`*/ /*!50003 TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW

but the import still failed with the same error so I checked the user privileges in MySQL:

[root@racktables.example.com tmp]$ mysql -u rootuser -h racktablesrdb.example.us-east-1.rds.amazonaws.com -P 3306 -p -e "select User,Host from mysql.user;"
Enter password: 
+----------------+-----------+
| User           | Host      |
+----------------+-----------+
| racktablesuser | %         |
| rootuser       | %         |
| rdsadmin       | localhost |
+----------------+-----------+

Then I remembered that % does not include localhost in MySQL. Changing the .sql file to

/*!50003 CREATE*/ /*!50017 DEFINER=`rootuser`@`%`*/ /*!50003 TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW

allowed the import to complete successfully.

Obviously it's much safer to actually define the hostname rather than using the % wildcard.

Sources

Tagged , ,

Convert text to date in LibreOffice Calc

When I import a .csv that contains a date field into LibreOffice Calc, and I set that column to DATE the content is prefixed with a single quote ('). That's annoying because I can't use functions on it: the single quote makes Calc use the content as free text.

To remove the single quote, select the column with dates and do a search and replace (CTRL + H): search for ^.*$ and replace it with &.

This solution was given by tohuwawohu on StackOverflow.

Tagged

ansible: show the groups of a host

$ ansible -m debug -a var=group_names host.example.com 
host.example.com  | SUCCESS => {                                         
    "group_names": [
        "group1", 
        "group2", 
        "group3", 
        "group4"
    ]
}
Tagged

deluge-web behind an nginx reverse proxy

My nginx configuration to reverse proxy deluge-web needed a few subtle tweaks before it worked correctly, see below.

~/.config/deluge/web.conf

{
  "file": 1,  
  "format": 1
}{
  "port": 8112, 
  "https": false, 
  "base": "/", 
  ... 
}

/etc/nginx/sites-enabled/deluge.conf

...
  location /deluge/ {
    allow                         127.0.0.1;
    allow                         192.168.1.0/24;
    deny                          all;
    proxy_pass                    http://127.0.0.1:8112/;
    proxy_redirect                off;
    proxy_set_header              Host            $host;
    proxy_set_header              X-Real-IP       $remote_addr;
    proxy_set_header              X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header              X-Deluge-Base   "/deluge/";
    proxy_pass_header             Set-Cookie;
    proxy_pass_header             P3P;
  }
...

The trailing slashes are very important for location, proxy_pass and X-Deluge-Base. Also notice that I left base in ~/.config/deluge/web.conf blank.

With this configuration you will be able to access the Deluge web interface via http://www.example.com/deluge (no trailing slash required). Keep in mind that it will also be accessible directly on port 8112, because you can't make it listen on locahost only yet. Unless you patch it in yourself. So don't forget to add a firewall rule.

Sources

Tagged , ,

Rhythmbox: file dialog window stays empty

Remove ~/.gconf/apps/rhythmbox.

Tagged

Python's `pip` behind a proxy

pip is proxy aware, sort of: https://github.com/pypa/pip/issues/1104. It can install packages from behind a proxy, but it cannot search!

To use pip from behind a proxy, you can set the environment variable ${http_proxy} and ${https_proxy}, or use the --proxy address:port switch after the install option.

Tagged ,

PHP-FPM ignores `upload_tmp_dir` setting

Running Apache 2.4 and PHP-FPM 5.4.16, I noticed that the option upload_tmp_dir was ignored when executing PHP code. phpinfo() shows the correct setting, but for instance sys_get_temp_dir() falls back to the OS' environment setting for the temporary directories as if it weren't set.

To fix this, I had to append a few more lines of config to my PHP-FPM's pool's configuration:

php_admin_value[open_basedir] = /tmp/php-fpm 
php_admin_value[upload_tmp_dir] = /tmp/php-fpm 
env[TMP] = /tmp/php-fpm 
env[TMPDIR] = /tmp/php-fpm 
env[TEMP] = /tmp/php-fpm

Sources

Tagged , ,