You want to change the storage engine in one or more of your MySQL tables, for example when you want to use a FULLTEXT index, that is not supported in INNODB tables in MySQL versions before 5.6.
Use the following to change the storage engine
ALTER TABLE table_name ENGINE = MyISAM;Trying to access or set up the mysql-workbench tool in linux you get the error about forgot_password.
Install the gnome-keyring
sudo apt install gnome-keyring
You want to reset the root password for MySQL in your linux Ubuntu installation.
Follow the steps described https://linuxconfig.org/how-to-reset-root-mysql-password-on-ubuntu-18-04-bionic-beaver-linux
Problem
You have a MySQL table that does not have any timestamp information (creating, updating) and you want to add a column that automatically adds a timestamp every time the record is created.
Solution
Add the column for timestamp that creates a timestamp every time a new record is created (INSERT) by using the following:
ALTER TABLE table_name ADD created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
More information here
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:
SELECT table_schema AS database_name, table_name AS table_name, round(((data_length + index_length) / 1024 / 1024), 2) AS size_mb FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;
Take from the answer here
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
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’.
** (Mariaex.Error) (1054): Unknown column 'p0.id' in 'field list'
Solution
You can define the primary key using the @primary_key as in:
@primary_key {:id_other_name, :integer, []}
schema "db_table" do
field :description, :string
...
end
Taken from here
Problem
You would like to copy a table with its data from one database to another.
Solution
You can use the following:
INSERT INTO database2.table SELECT * from database1.table
taken from here
Problem
You want to export a MySQL database table as a csv with something like:
mysql> select * from table_name into outfile '/tmp/table_name.csv';
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:
mysql> show variables like 'secure_file_priv'; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.00 sec)
and then use the path to change the outfile path (ie /var/lib/mysql-files/table_name.csv).
Taken from the answer here
Problem
You want to create a MySQL table that contains spaces, but when you try you get the following error message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-afterdash' at line 1
Solution
Use the backticks to create the table:
mysql> create database `table-afterdash`; Query OK, 1 row affected (0.00 sec)