Error,Syntax and Solutions

=============================================================================================================
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.

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

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 root@’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:

[ahmed@amayem ~]$ 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:

[ahmed@amayem ~]$ telnet 127.0.0.1 3306
Trying 127.0.0.1…
telnet: connect to address 127.0.0.1: Connection refused
[ahmed@amayem ~]$ mysql -h 127.0.0.1
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
[ahmed@amayem ~]$ mysql -h 127.0.0.1 -P 3306
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
[ahmed@amayem ~]$ 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)
[ahmed@amayem ~]$ 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

[ahmed@amayem ~]$ cd /etc/init.d/
[ahmed@amayem init.d]$ ./mysqld stop
Stopping mysqld: [ OK ]
[ahmed@amayem init.d]$ sudo ./mysqld start
Starting mysqld: [ OK ]

Basically we just restarted mysqld. Now the error changes:

[ahmed@amayem 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.

[ahmed@amayem 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 ‘root’@’localhost’ (using password: NO)
=============================================================================================================

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

Resolution:
[root@server ~]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
[root@server ~]# mysqld_safe –skip-grant-tables &

[root@server ~]# 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

[root@server ~]# /etc/init.d/mysqld restart

root@server ~]# 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

[root@bigboy tmp]# service mysqld stop

[root@bigboy 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.

[root@bigboy 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.

[root@bigboy 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
[root@bigboy 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

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: