drkdidel.be

Upgrade Apache to 2.4 from 2.2 on Arch Linux, using PHP-FPM

Since the first article, more than six weeks ago, Apache has been updated to version 2.4.9 which allows for a simpler configuration with php-fpm, mod_proxy_fcgi and mod_proxy_handler.

The following setup does not use mod_php, which requires mod_mpm_prefork and ProxyPassMatch directives as shown in my first article.

First, install php-fpm and mod_proxy_handler (the latter comes from AUR).

# yaourt -S php-fpm mod_proxy_handler

Then update /etc/php/php-fpm.conf:

listen = 127.0.0.1:9000
;listen = /run/php-fpm/php-fpm.sock
[...]
listen.allowed_clients = 127.0.0.1

In the main Apache configuration file located at /etc/httpd/conf/httpd.conf, no more need to use mpm_prefork_module and ProxyPassMatch. Just append the following:

LoadModule proxy_handler_module modules/mod_proxy_handler.so
<filesmatch .php="">
    SetHandler "proxy:fcgi://127.0.0.1:9000/"
</filesmatch>

Verify that the following line is active (uncommented) as it should be by default:

LoadModule proxy_fcgi_module modules/mod_proxy_fcgi.so

Edit the dir_module directive:

<ifmodule dir_module="">
    DirectoryIndex index.php index.html
</ifmodule>

Remove or comment out the php module if you had it in your old config:

#LoadModule php5_module modules/libphp5.so
#Include conf/extra/php5_module.conf

Restart apache and php-fpm:

# systemctl restart httpd.service php-fpm.service

If it is your first installation of php-fpm and you use Apache often, you might want to start its daemon automatically:

# systemctl enable php-fpm.service

Source

MariaDB / MySQL: generate a row number

There is currently no built-in method to return row numbers. The solution is to use a variable which is incremented in each row, like this:

@currentRow := @currentRow + 1 AS rowNumber

We can use a JOIN statement to initialise the variable without SET:

JOIN (SELECT @currentRow := 0) row

Another notation, replacing JOIN with a comma:

, (SELECT @currentRow := 0) row

Here is an example of an entire query:

SELECT
    title,
    text,
    @currentRow := @currentRow + 1 AS rowNumber
FROM articles
JOIN (SELECT @currentRow := 0) row

Source

MariaDB / MySQL: export or backup data to a CSV file

It seems there are several ways to do this. Here is one, using the mysql prompt. I used mysql root account because my usual user has not enough permissions to write files (ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)), even after a chmod 777: UPDATE: We need to grant file access to the database user:

$ mysql -u root -p
Enter password:
[...]
MariaDB [(none)]> GRANT FILE ON *.* TO 'username'@'localhost';

GRANT ALL does not include file permission. Now we can execute a query and output it into a CSV file. Formatting options are available:

$ mysql -u username -p database_name
MariaDB [database_name]> SELECT field1, field2, field3 INTO OUTFILE 'database_name_export.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM table_name;

Not specifying a path put the file in /var/lib/mysql/database_name. When I use /tmp/database_name_export.csv, the file is written in /tmp/systemd-private-14da7...-mysqld.service-.../tmp/. This behaviour happens on an encrypted Arch Linux system.

Also note that mysql won't overwrite an existing file, for security reason: ERROR 1086 (HY000): File '/tmp/database_name_export.csv' already exists.

Source

Source update

MariaDB / MySQL: backup and restore a specific table

We just have to specify the name of the table we want to backup in the usual mysqldump command:

$ mysqldump -h hostname -u username -p database_name table_name > backup_table_name.sql

Then, to restore it:

mysql -u username -p database_name

Source

MySQL: "ERROR 1005 (HY000): Can't create table ... (errno: 150)"

There can be a few reasons for this very helpful message. In my case, MySQL's default engine on the production server was MyISAM, while being InnoDB on the development server.

MyISAM does not handle foreign keys, thus the above error. The simple fix is to switch engines:

mysql> ALTER TABLE tableName ENGINE = InnoDB;