Find the table sizes in MySQL database

Problem

You would like to have a list with all the tables in an MySQL database, sorted with their sizes (actual table and indices).

Solution

You could run the following query to give you a list of all the tables sorted by size:

Take from the answer here

Exporting SQL structure commands from MySQL Workbench

Problem

You would like to export the structure of some specific MySQL tables using MySQL Workbench (ie to create same tables in another database).

Solution

Go to the table name, right click, select ‘Copy To Clipboard’, then ‘Create Statement’ and finally go to a new file or editor and paste the command from the clipboard.
Just have in mind that you may need to delete the AUTO_INCREMENT from the SQL.
Solution was described here

Using a primary key not named id with Ecto and Mysql

Problem

You would like to use a legacy or a database you don’t have control over, with Phoenix, but the primary key of the table it is not named ‘id’.

Solution

You can define the primary key using the @primary_key as in:

Taken from here

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

Problem

You want to export a MySQL database table as a csv with something like:

but you get the error that MySQL cannot execute this command.

Solution

Find out the location that MySQL can use to export files by running the following:

and then use the path to change the outfile path (ie /var/lib/mysql-files/table_name.csv).

Taken from the answer here

Create a MySQL table with dashes

Problem

You want to create a MySQL table that contains spaces, but when you try you get the following error message:

Solution

Use the backticks to create the table:

Using tables with dashes in MySQL queries

Problem

You have some databases in MySQL that contain dashes in their names, and when you try to use them (ie grant access to them), you get the following error:

Solution

To be able to use them, enclose the database name in backticks (`) like:

Find out the tables storage engine in your MySQL database

Problem

You would like to check the storage engine in a MySQL database.

Solution

Use the following query after logging in to the server:

Taken from here

rails `require’: cannot load such file — mysql2/mysql2 (LoadError)

Problem

You want to use a different ruby version from the one you have initially installed and build your application with, but when you change it in your rvm installation you get the following error:

Solution

To make it work again you will have to uninstall the mysql2 gem and install it again in the new ruby version with the option –platform=ruby.