Problem
You would like to check and enable the slow query log for MySQL.
To check if the log is on run the following query
show global variables like '%slow_queries'To switch it on if the above shows it as OFF, use the following:
set @@GLOBAL.log_slow_queries = 'ON'You are using the dump files from MySQL (earlier version than 5.7 – that has -skip-definer option in mysqlpump), that outputs the DEFINER keyword in Functions and Views, which causes en error when trying to import in a different database that the users do not exist.
Use the following in your database dump to remove these keywords:
For FUNCTIONS
sed -i 's/DEFINER=\S*\sFUNCTION/FUNCTION/' mysql_dump
For VIEWS
sed -i '/50013\sDEFINER/d' mysql_dumpYou 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