MySQL Tips and Tricks, Errors and Solutions

MySQL

=============================================================================================================

To Rename a Table and ADD a Column

ALTER TABLE tablename CHANGE COLUMN old col name new col name VARCHAR(50) NOT NULL;

ALTER TABLE contacts ADD email VARCHAR(60);

=============================================================================================================

ERROR 1025 (HY000): Error on rename of Ö (errno: 150) or use Heidi sql to drop the constraint

FYR, TABLE_NAME IS = categories

SHOW CREATE TABLE categories; (to show the name of constraint)

Most probably it will be categories_ibfk_1

Use the name to drop the foreign key first and the column then:

ALTER TABLE categories DROP FOREIGN KEY categories_ibfk_1;
ALTER TABLE categories DROP COLUMN assets_id;

=============================================================================================================

Adding Foreign Key to a Table
Syntax:

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

ALTER TABLE PAYMENT
ADD FOREIGN KEY (Invoice_ID, Store_ID) REFERENCES INVOICE (Invoice_ID, Store_ID); —-Composite

Example:

ALTER TABLE ValidationChecksmapping ADD FOREIGN KEY fk_validationchecksmapping(RulecheckID)REFERENCES ValidationCheck(ID);

ALTER TABLE ValidationChecksmapping ADD FOREIGN KEY fk_validationchecklistID(CheckListID)REFERENCES ValidationChecklist(CheckListID);

=============================================================================================================

Modifying Primary Key from one column to another

ALTER TABLE ValidationCheck add column ID varchar(38) NOT NULL;

ALTER TABLE ValidationCheck MODIFY COLUMN RuleCheckId varchar(38);

ALTER TABLE ValidationCheck ADD PRIMARY KEY (ID);

=============================================================================================================

How to update the max_connections setting in MySQL
1st Method:

show variables like ‘%max_connections%’;

show variables like “max_connections”;

shutdown server— service mysqld stop
service mysqld status

In /etc/my.cnf under the [mysqld] section add:

max_connections = 500

start server— service mysqld start
service mysqld status

2nd Method:

SHOW VARIABLES LIKE “%version%”;

SET GLOBAL max_connections = 1000; in MySQL

or restart MySQL.

show variables like ‘%max_connections%’;

SET GLOBAL open_files_limit = 1024;

show variables like ‘%open_files_limit%’

echo “show variables like ‘max_connections’;” | mysql

SHOW VARIABLES LIKE “%version%”;

show processlist;

show processlist\G

If you are getting “too many connections” errors in MySQL you can change the max_connections setting to allow more connections, assuming you have enough RAM to handle the increased number.

Note that increasing the number of connections that can be made will increase the potential amount of RAM required for MySQL to run. Increase the max_connections setting with caution!

=============================================================================================================

To Know mysql 3306 port open or not from server

netstat -an | grep 3306

=============================================================================================================

Starting MySQL ERROR! The server quit without updating PID file

Sometimes you may see this error in your server. But donít panic, just rename or move away the /etc/my.cnf in your server,
and try to restart the MySQLd again.

[[email protected]:~ ] $ service mysqld start
Starting MySQL… ERROR! The server quit without updating PID file (/var/lib/mysql/server.pelayan.com.pid).
[[email protected]:~ ] $ mv /etc/my.cnf /etc/my.cnf.old
[[email protected]:~ ] $ service mysqld restart
Shutting down MySQL…. SUCCESS!
Starting MySQL.. SUCCESS!
[[email protected]:~ ] $

Most of the time happen while upgrading MySQL version

Stopping mysqld …
Shutting down MySQL…. SUCCESS!
Upgrading MySQL 5.0 to 5.5
Preparing… ########################################### [100%]
1:MySQL-shared ########################################### [ 25%]
2:MySQL-client ########################################### [ 50%]
3:MySQL-devel ########################################### [ 75%]
4:MySQL-server ########################################### [100%]
Starting MySQL… ERROR! The server quit without updating PID file (/var/lib/mysql/server.pelayan.com.pid).

=============================================================================================================

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

I was trying to install the stocked MySQL 5.6.11 from mysql.com instead of using the default shipped MySQL with RHEL6.

Post-installation, the temporary password gets stored in /root/.mysql_secret. You login to MySQL Database using:

mysql -u root -pC7hCdlti

Then connect to database:

mysql> connect mysql;
Connection id:    2
Current database: mysql

If you try any further operation, youíll see an error:

mysql> select * from user;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

To get past this, set the password for root like this:

mysql>  SET PASSWORD for [email protected]ílocalhostí = PASSWORD(ësecretí);
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Now try logging in using the newly set password and youíre good to go.

====================================================================================================================

Solving MySQL ERROR 2002 (HY000): Can’t connect to local MySQL server through socket /var/lib/mysql/mysql.sock(2)

I came upon this error after installing the latest version of MySQL using yum and securing it.
The Error

With mysqld running I tried to run mysql as follows:

[[email protected] ~]$ mysql
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

Different failed attempts:

Here are more attempts to get it working:

[[email protected] ~]$ telnet 127.0.0.1 3306
Trying 127.0.0.1…
telnet: connect to address 127.0.0.1: Connection refused
[[email protected] ~]$ mysql -h 127.0.0.1
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
[[email protected] ~]$ mysql -h 127.0.0.1 -P 3306
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
[[email protected] ~]$ mysql -h 127.0.0.1 -P 3306 -u root
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
[[email protected] ~]$ mysql -h 127.0.0.1 -P 3306 -u root -p
Enter password:
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)

The Solution

[[email protected] ~]$ cd /etc/init.d/
[[email protected] init.d]$ ./mysqld stop
Stopping mysqld: [ OK ]
[[email protected] init.d]$ sudo ./mysqld start
Starting mysqld: [ OK ]

Basically we just restarted mysqld. Now the error changes:

[[email protected] init.d]$ mysql
ERROR 1045 (28000): Access denied for user ‘ahmed’@’localhost’ (using password: NO)

This is good, now we just need to use a user that has access, like root that was made when we secured the install.

[[email protected] init.d]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.

And we are in.
The Reason (Conjecture)

Since it started working after manually restarting mysqld, I would have to guess that when it was restared it made/var/lib/mysql/mysql.sock, which might have been missing. Unfortunately I didnít check on its existence before, so I canít confirm. If anyone has a reason please mention it in the comment section.

=============================================================================================================

Access denied for user [email protected] (using password: NO)

This error pops us mostly while trying to access your MYSQL database

Resolution:
[[email protected] ~]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
[[email protected] ~]# mysqld_safe ñskip-grant-tables &

[[email protected] ~]# mysql -u root

mysql> show databases;

mysql> use mysql;

mysql> show tables;

mysql> update user set password=PASSWORD(ìnew_paswdî) where User=írootí;

mysql> flush privileges;

mysql> quit

[[email protected] ~]# /etc/init.d/mysqld restart

[email protected] ~]# mysql -u root -p

mysql> quit

======================================================================================================================
Recovering / Changing Your MySQL Root Password
=============================================================================================================

Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are:

1) Stop MySQL

[[email protected] tmp]# service mysqld stop

[[email protected] tmp]#

2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.

[[email protected] tmp]# mysqld_safe –skip-grant-tables –skip-networking &

3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.

[[email protected] tmp]# mysql -u root

mysql>

4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password.

mysql> use mysql;

Database changed
mysql> UPDATE user SET Password=PASSWORD(“ack33nsaltf1sh”) WHERE User=”root”;

mysql>

5) Exit MySQL and restart the mysqld daemon.

mysql> exit
Bye
[[email protected] tmp]# service mysqld restart

That’s it, The MySQL root user will now be able to manage MySQL using this new password.

===============================================================================================================================

my.cnf of mysql in redhat linux MISSING in MySQL

if youíve installed mysql in redhat linux or any linux system then when you try to search for the configuration file & unfortunately couldnít find my.cnf in /etc folder. donít worry about this problem.. open your shell and try to search any files that has .cnf extension first.
use this command:

cd /
find ./ -type f -name ì*.cnfî

find: is a searching command.
./: will start search from a current directory.
-type f : means for file type not directory
-name : is case sensitive. perform better than -iname which is incase sensitive
ì*.cnfî : * means any name which has .cnf as its extension
you may found something like:
my-huge.cnf <-for huge data try use this file my-large.cnf my-small.cnf (if there is no file of cnf! -> search google and copy it from there)
if you found it, quickly go to that directory
copy it and put it in /etc folder as my.cnf (rename it, in some linux like ubuntu you can find/etc/mysql folder)
stop and restart mysql so that it takes effect.
you may as well change the maximum allowed of data that can be restored in mysql db frommy.cnf configuration

===============================================================================================================================

Login / MySQL Space Issue

mysql -uroot -proot

/etc/init.d/mysqld start
/etc/init.d/mysqld stop
/etc/init.d/mysqld restart
mysqladmin -u root -p status
datadir=/var/lib/mysql

mysql> show variables like ‘%datadir%;

In MySQL Space Issue: (1) Delete /var/log/mysqld.log files

===============================================================================================================================

How to alter a column and change the default value?

The ALTER syntax for setting a column default

Syntax:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'literal';

Example:

ALTER TABLE MonitoringActivityLog ALTER COLUMN Source SET DEFAULT 'CDR';

===============================================================================================================================

To get all Triggers in a database / all databases

To get all the trigger list irrespective of the database name:

SELECT * FROM information_schema.TRIGGERS;

To get list of trigger against a specific database:

SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA=’database_name’;

===============================================================================================================================

MySQL “show users” – how to show/list the users in a MySQL database

select host, user, password from mysql.user;

===============================================================================================================================

To know MySQL Version from Terminal

mysql –version

===============================================================================================================================

RENAME TABLE Syntax

RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] …

This statement renames one or more tables. The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running.

For example, a table named old_table can be renamed to new_table as shown here:

RENAME TABLE old_table TO new_table;

This statement is equivalent to the following ALTER TABLE statement:

ALTER TABLE old_table RENAME new_table;

If the statement renames more than one table, renaming operations are done from left to right. If you want to swap two table names, you can do so like this (assuming that tmp_table does not already exist):

RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;

==========================================================================

Mysql error “28 from storage engine” – means “not enough disk space”.

To show disc space use command below.

myServer# df -h

Results must be like this.

Filesystem Size Used Avail Capacity Mounted on
/dev/vdisk 13G 13G 46M 100% /
devfs 1.0k 1.0k 0B 100% /dev

Note:

1.You will get same error if /tmp filesystem reach 100% of usage. ñ Kumar Feb 26 at 5:32


2.Even if you have your Mysql data_dir in a different partition (or disk) which do have free space, you must have free space on / and /tmp (of course you could have last ones on a different partition/disk) 

Ref: http://stackoverflow.com/questions/10631387/1030-got-error-28-from-storage-engine

=========================================================================================================

MariabDB To CentOS in CentOS 7

Centos 7 comes with MariaDB instead of MySQL. MariaDb is a open source equivalent to MySQL and can be installed with yum -y install mariadb-server mariadb. If you must have mysql you need to add the mysql-community repo sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm and then you can install MySQLl like you normally do.

=========================================================================================================

Killing MySQL User Connections

SELECT CONCAT(‘KILL ‘,ID,’;’)
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE USER = ‘testuser’
AND COMMAND = ‘Query’
AND TIME < 10;

   OR

select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=’testuser’;

============================================================================================================

How to adjust display settings of mysql command line?

There are a number of ways you can make results more readable:

Using limit to only view a few rows:

SELECT * FROM table LIMIT 10;

Only returning select columns:

SELECT a,b,c FROM table;

Terminating your queries with \G to send the current statement to the server to be executed and display the result using vertical format.

SELECT * FROM table \G

======================================================================================

How to find configuration file MySQL uses?

A customer called me today asking for help with locating the configuration file used by one of their production MySQL instances. From the description I was given it appeared that their server had at least six different copies of my.cnf file in different locations on disk. And all were similar enough that each could actually be the one. All superfluous files were the result of a bit negligent system administration. So what turned to be the quickest and the least destructive way to find the correct one?

Initially suspecting the server was simply running more than just one MySQL instance, I logged in to take a deeper look. But I found only one mysqld process and, indeed, several configuration files.

All of them seemed good candidates:

/etc/my.cnf
/etc/mysql/my.cnf
/var/lib/mysql/my.cnf

In many cases you could simply check system process list using ps:

server ~ # ps ax | grep ‘[m]ysqld’
10801 ? Ssl 0:27 /usr/sbin/mysqld –defaults-file=/etc/mysql/my.cnf –basedir=/usr –datadir=/var/lib/mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock

In many cases, because it doesnít really have to work every time. If configuration file was not specified explicitly by an init script starting the MySQL instance, then database would used the compiled-in default and such information would not be visible in the ps output. This could happen if for example the database instance was launched ìby handî from shell. The file information would also not be visible if the process line was truncated for any.

An alternative method could be examining information in /proc. /proc is the place where Linux kernel exposes a lot of internal information about itself, hardware and running processes through a bunch of virtual files and directories. Specifically each process has its own directory there that takes the name after the process id (or PID). Learning MySQL PID is as easy as running pidof mysqld.

One of the files we need is called cmdline. It contains the full command that started certain process.

server ~ # cat /proc/$(pidof mysqld)/cmdline | tr ‘\0’ ‘\n’
/usr/sbin/mysqld
–defaults-file=/etc/mysql/my.cnf

The configuration information is clearly visible. The tr command simply converts any \0 characters into line breaks and is there just for readability.

Yet another approach could be browsing the process environment information. It can also be found in /proc in a file called environ. Sometimes a startup script may leave some information there:

server ~ # tr ‘\0’ ‘\n’ < /proc/$(pidof mysqld)/environ | grep -i cnf
MY_CNF=/etc/mysql/my.cnf

Finally you can try figuring out the compiled-in defaults, but it wonít necessarily tell you which configuration was actually used. This method is also not 100% safe as it means attempting to start another MySQL instance, even if only to print help message, because MySQL does not seem to handle this very well and it may produce some conflicts:

server ~ # /usr/sbin/mysqld –help –verbose –skip-networking –pid-file=$(tempfile) 2> /dev/null | grep -A1 ‘Default options are read’
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Specifying –pid-file here is essential as otherwise the new mysqld may overwrite the PID file of the running instance.

All in all I was able to help my customer. But there is no foolproof way. It might happen that in certain circumstances figuring out the true my.cnf location may not be possible.

(OR)

If there is slocate package installed in the system, then itís possible to locate all copies of my.cnf with locate my.cnf command. It does not tell, of course, which was used by MySQL for options, but at least it is possible to find all candidates. Note that locate database is usually updated nightly, so it represents the state of a system from the preceding night.

If locate command isnít available, then find / -name ‘my.cnf’ could be the way to go, but it will be reading the entire directory tree.

================================================================================================

How to Check MySQL Database & Tables Size

MySQL is an Relational Database Management System, widely used as database system for Linux systems. This article will help you to calculate size of tables and database in MySQL or MariaDB servers though sql queries. MySQL stored all the information related to tables in database in information_schema database. We will use information_schema table to find tables and databases size.
Check Single Database Size in MySQL:

This query will calculate size of single database in mysql server. Please change ëmydbë with your actual database name.

SELECT table_schema “Database Name”, SUM( data_length + index_length)/1024/1024
“Database Size (MB)” FROM information_schema.TABLES where table_schema = ‘mydb’;

+—————+——————–+
| Database Name | Database Size (MB) |
+—————+——————–+
| mydb | 0.15625000 |
+—————+——————–+
1 row in set (0.04 sec)

Check ALL Database Size in MySQL:

This query will calculate size of all databases in mysql server.

SELECT table_schema “Database Name”, SUM(data_length+index_length)/1024/1024
“Database Size (MB)” FROM information_schema.TABLES GROUP BY table_schema;

+——————–+——————–+
| Database Name | Database Size (MB) |
+——————–+——————–+
| demodb | 0.15625000 |
| information_schema | 0.00976563 |
| mydb | 0.15625000 |
| mysql | 0.81098557 |
| performance_schema | 0.00000000 |
+——————–+——————–+
5 rows in set (0.01 sec)

Check Single Table Size in MySQL Database:

This query will calculate size of single table in a database in mysql server. Please change ëmydbë with your actual database name and ëtable_oneë with your actual table name.

SELECT table_name “Table Name”, table_rows “Rows Count”, round(((data_length + index_length)/1024/1024),2)
“Table Size (MB)” FROM information_schema.TABLES WHERE table_schema = “mydb” AND table_name =”table_one”;

+———————+————+—————–+
| Table Name | Rows Count | Table Size (MB) |
+———————+————+—————–+
| archive_one | 8 | 0.09 |
+———————+————+—————–+
1 row in set (0.00 sec)

Check All Table Size in MySQL Database:

This query will calculate size of all tables in a database in mysql server. Please change ëmydbë with your actual database name. It will also list number of rows in each table.

SELECT table_name “Table Name”, table_rows “Rows Count”, round(((data_length + index_length)/1024/1024),2)
“Table Size (MB)” FROM information_schema.TABLES WHERE table_schema = “mydb”;

+———————-+————+—————–+
| Table Name | Rows Count | Table Size (MB) |
+———————-+————+—————–+
| table_one | 8 | 0.09 |
| table_two | 0 | 0.02 |
| table_three | 0 | 0.02 |
| table_four | 174 | 0.03 |
+———————-+————+—————–+
4 rows in set (0.00 sec)

===================================================================================================

Rename Root @ local host username mySQL

UPDATE mysql.user set user = where user = ‘root’;
flush privileges;

========================================================

‘The server quit without updating PID file ‘

try to find your log file with suffix “.err”, there should be more info. It might be in:

/usr/local/var/mysql/your_computer_name.local.err
It’s probably problem with permissions

check if any mysql instance is running

ps -ef | grep mysql
if yes, you should stop it, or kill the process

kill -9 PID
where PID is the number displayed next to username on output of previous command

check ownership of /usr/local/var/mysql/

ls -laF /usr/local/var/mysql/
if it is owner by root you should change it mysql or your_user

sudo chown -R mysql /usr/local/var/mysql/

======================================

Viewing Indexes from database

To see the index for a specific table use SHOW INDEX:

SHOW INDEX FROM yourtable;

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = ‘your_schema’;
Removing the where clause will show you all indexes in all schemas.

OR

use information_schema;
SELECT * FROM statistics;

=======Drop multiple tables in one shot in mysql===========

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS a,b,c;
SET foreign_key_checks = 1;

========================================

ERROR 1146 (42S02): Table doesn’t exist

So some of you might have run across the following errors when installing MySQL 5.6 :
ERROR 1146 (42S02): Table ‘mysql.innodb_index_stats’ doesn’t exist
ERROR 1146 (42S02): Table ‘mysql.innodb_table_stats’ doesn’t exist
ERROR 1146 (42S02): Table ‘mysql.slave_master_info’ doesn’t exist
ERROR 1146 (42S02): Table ‘mysql.slave_relay_log_info’ doesn’t exist
ERROR 1146 (42S02): Table ‘mysql.slave_worker_info’ doesn’t exist
You are likely amazed that you see this error on a fresh database install. You are not alone. The issue is fixable though.

The safest thing to do is to reinstall the mysql database via the following command: mysql_install_db
http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html


ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

The default root password is blank (i.e. empty string) not root. So you can just login as:

$ mysql -u root
use mysql;
UPDATE mysql.user SET Password=PASSWORD(‘root’) WHERE User=’root’;
flush privileges;


SQLYOG Import Error –

Error log file is too large that’s why notepad is not responding. you should clean your error log and then re-import so that you can check error logs. Error may be due to max_allowed_packet or table / database already exists etc etc..