Search by tag: mysql
4 articles
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…
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…
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: 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…