CREATE TABLE temporary_products ( skuId int NOT NULL PRIMARY KEY, sku varchar(255) NOT NULL);
Category Archives: My SQL
Disable ONLY_FULL_GROUP_BY
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
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
XAMPP Reset Mysql Password
Login to MySQL using the current use details and run the following command.
UPDATE mysql.user SET Password=PASSWORD('Root@123') WHERE User='root';
FLUSH PRIVILEGES;
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
Looks like mysqld is not started . Run the following command to fix the issue.
service mysqld start
Enable MySQL Query Caching
You can setup them in /etc/my.cnf (CentOS 7)
vi /etc/my.cnf
Append config directives as follows:
query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576
In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.
Restart the mysql service.
service mysqld restart
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;
Unbuffered Queries
Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as “use result”.