Category Archives: My SQL

check last executed query mysql

You can controll this option at run time.

If you prefer to output to a table:

Execute SET GLOBAL log_output = 'TABLE';
Execute SET GLOBAL general_log = 'ON';
Take a look at the table mysql.general_log

If you prefer to output to a file instead of a table:

SET GLOBAL log_output = "FILE"; the default.
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';

Import a large sql dump file

1) Set network buffer length to a large byte number. The default value may throw errors for such large data files

set global net_buffer_length=1000000;

2) Set maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.

set global max_allowed_packet=1000000000;

3) Disable foreign key checking to avoid delays,errors and unwanted behaviour

SET AUTOCOMMIT = 0;
SET UNIQUE_CHECKS = 0;
SET foreign_key_checks = 0;

4) Import your sql dump file

source FILEPATH/FILE_NAME

5) Enable foreign key checks when import is done.

SET AUTOCOMMIT = 1;
SET UNIQUE_CHECKS = 1;
SET foreign_key_checks = 1;
innodb_buffer_pool_size    = 1G
innodb_log_buffer_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_file_size = 1G

Prepared Statements MySQL

Similar in concept to templates – contain compiled code used to run common sql operations

o Advantages:

 Query only parsed once, but allows for multiple executions, with same or different parameters (performance consideration)

 Related parameters do not need to be quoted (security consideration)

o Only feature pdo will emulate for adapters that do not support prepared statements

Transactions

• Combines individual sql operations into one
• Usually start with begin or begin transaction
• Execute the transaction using commit
• Cancel the transaction using rollback

Example

START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;