Fastest ways to find duplicate records in MySQL. If name is the column you want to find duplicates in: SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1;SELECT col1 FROM table GROUP BY col1 HAVING count(*) > 1; Is also possible to support multiple columns, like this: SELECT col1, col2, COUNT(*) c FROM […]
Remove MySQL duplicates in place, without making a new table: ALTER IGNORE TABLE foobar ADD UNIQUE (email);ALTER IGNORE TABLE foobar ADD UNIQUE (email); *** This will keep the oldest duplicate record and erase the newer ones *** Works if index fits in memory and if MySQL < 5.7.4 *** Works fine with MariaDB 10.1 If […]
View all foreign keys to a table: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_TABLE_NAME = ‘tablename’;SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_TABLE_NAME = ‘tablename’; View all foreign keys to a column: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘dbname’ AND REFERENCED_COLUMN_NAME = ‘columnname’;SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM […]
If you get this MySQL error while you alter a table: ERROR 1114 (HY000): The table ‘site’ is fullage doneERROR 1114 (HY000): The table ‘site’ is fullage done Make sure the partition is not full. Type this command to see the partition status: df -hdf -h
With MariaDB there is a file that contains user\pass for MySQL autologin: /etc/mysql/debian.cnf/etc/mysql/debian.cnf Here is an example output of /etc/mysql/debian.cnf: # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = randomstring socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = randomstring socket = /var/run/mysqld/mysqld.sock […]
Edit /etc/mysql/my.cnf as follow: [mysqld] skip-name-resolve[mysqld] skip-name-resolve Then restart mysql service: /etc/init.d/mysql restart/etc/init.d/mysql restart
Here is an example of PHP MySQLi persistent connection: $mysqli = new mysqli("p:123.123.123.123", "user123", "123", "testdb1");$mysqli = new mysqli("p:123.123.123.123", "user123", "123", "testdb1"); Do not use “localhost” as host.
To make sure MySQL uses all available server RAM: 1) Edit /etc/mysql/my.cnf 2) Increase innodb buffer pool size to 85% of RAM Example, if you have 64GB RAM: innodb_buffer_pool_size = 55Ginnodb_buffer_pool_size = 55G So InnoDB will load as much data it can in the buffer pool. 3) Increase the max connections: This is good for […]
If you get this error message on your PHP script: Warning: mysqli::real_connect(): (HY000/2002): Cannot assign requested address in …Warning: mysqli::real_connect(): (HY000/2002): Cannot assign requested address in … Make sure to use a persistent connection “p:”: $mysqli = new mysqli("p:10.1.1.1", "user", "pass", "db");$mysqli = new mysqli("p:10.1.1.1", "user", "pass", "db");
Use DISTINCT() to select only unique values from a MySQL column: SELECT DISTINCT(Email) AS Email FROM Users ORDER BY Email DESC;SELECT DISTINCT(Email) AS Email FROM Users ORDER BY Email DESC;