drkdidel.be

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;