LOAD DATA LOCAL INFILE '/root/Desktop/tablename.csv' INTO TABLE TABLE-NAME ;
Category Archives: My SQL
create table with primary key
CREATE TABLE temporary_products ( skuId int NOT NULL PRIMARY KEY, sku varchar(255) NOT NULL);
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;