MySQL

MySQL: Write All Executed Queries Into a Log File

Submitted by Peter Majmesku on Tue, 05/23/2017 - 13:54
  1. Open the MySQL config file, which is located at /etc/mysql/my.cnf on Linux systems.
  2. Then write the following lines at the end of that file.

[mysqld]
general_log_file = /var/log/mysql/general_mysql.log
general_log = on

Restart your MySQL server (e.g. by executing "sudo service mysql restart" on Ubuntu Linux systems). Now you can follow all new queries as they are executed by this command:

tail -f /var/log/mysql/general_mysql.log

You can leave this program in command line by pressing CTRL+C.

For disabling, just set "general_log" to "off" or comment the out the previous lines in /etc/mysql/my.cnf by writing the # character at the beginng of the lines, save and restart the MySQL server again.

Tags

MySQL: Fixing Foreign Key Constraint Errors

Submitted by Peter Majmesku on Wed, 05/17/2017 - 19:12

Login to your MySQL server via command line. Then execute the following statement:

SHOW ENGINE INNODB STATUS;

You will get a error message like:

Foreign key constraint fails for table `my-table`.`JobExchange`:
,
  CONSTRAINT `FK_42760B1EE7A1254A` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON DELETE SET NULL
Trying to add to index IDX_42760B1EE7A1254A tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 8000000d; asc     ;;

But the parent table `my-table`.`contact`
or its .ibd file does not currently exist!
 

This shows you, that MySQL cannot find the corresponding database table. This can be due to lowercase-uppercase mismatch of the table names. Check your MySQL server settings:

show variables like 'lower%';

This will show something like this:

+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0,01 sec)

Show detailed information about the primary-foreign key references

mysql> SELECT
-> TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
-> FROM
-> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-> WHERE
-> REFERENCED_TABLE_SCHEMA = 'my-table' AND
-> REFERENCED_TABLE_NAME = 'JobExchange';


+-----------------------------------+----------------+---------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-----------------------------------+----------------+---------------------+-----------------------+------------------------+
| JobExchangePdf | jobexchange_id | FK_7A971E966193B1C5 | jobexchange | id |
| jobexchange_jobemploymentcategory | jobexchange_id | FK_F5E32C9C6193B1C5 | jobexchange | id |
+-----------------------------------+----------------+---------------------+-----------------------+------------------------+
2 rows in set (3,59 sec)

MySQL Workbench Vs. PhpStorm

MySQL Workbench shows the lower-case named table names correctly. PhpStorm does not. PhpStorm is guessing the right table name.

Possible fix

Drop the foreign key constraint and create a new one. F.e. with MySQL Workbench. You can drop the foreign key with the following command:

ALTER TABLE `my-table`.`JobExchange`
DROP FOREIGN KEY `FK_42760B1E6C7B4691`;
ALTER TABLE `my-table`.`JobExchange`
DROP INDEX `IDX_42760B1E6C7B4691` ;

The foreign key will be added like by this command:

ALTER TABLE JobExchange ADD CONSTRAINT FK_42760B1E6C7B4691 FOREIGN KEY (jobtype_id) REFERENCES JobExchangeCategory (id) ON DELETE SET NULL;
CREATE INDEX IDX_42760B1E6C7B4691 ON JobExchange (jobtype_id);
Tags

MySQL: Import and export compressed data

Submitted by Peter Majmesku on Mon, 08/29/2016 - 19:24

The export (dump)

This command will generate you a compressed dump file with the date information in the filename.

mysqldump -u username -p"password" database_name | gzip > $(date +"%Y-%m-%d")-database_name.sql.gz

Import the database dump

Firstly make sure that the "empty" database exists. Then import it:

mysql -uUSERNAME -p"PASSWORD" database_name < dump-filename.sql

Import a Gzip compressed database dump file:

zcat database-dump-file.sql.gz | mysql -uroot -ppassword database-name
Tags
Subscribe to MySQL