Archive

Archive for the ‘mySQL troubleshooting’ Category

mysql_secure_installation showed ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

April 4, 2014 Leave a comment

Today, I secured the mysql db with the mysql_secure_installation. It showed the following errors.

root@slave1 mysql]# ./bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we’ll need the current
password for the root user.  If you’ve just installed MySQL, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
Enter current password for root (enter for none):
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
Enter current password for root (enter for none):

Caused:

Since I have set the mysqld socket to /opt/mysql/run/mysqld.sock in /etc/my.cnf as the following, not the default path /tmp/mysql.sock.

[mysqld]
# basic setting
datadir = /opt/mysql/data
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock

Fixed:

I added one more line “/opt/mysql/run/mysqld.sock” into /usr/local/mysql/bin/mysql_secure_installation at make_config section as the following. Then the problem got fixed.

sub make_config {
my $password = shift;

my $esc_pass = basic_single_escape($rootpass);
write_file($config,
“# mysql_secure_installation config file”,
“[mysql]”,
“user=root”,
“password=’$esc_pass'”,
“connect-expired-password”,
“socket=/opt/mysql/run/mysqld.sock”);
}

 

 

Advertisements
Categories: mySQL troubleshooting

InnoDB: Unable to lock ./ibdata1, error: 37

May 14, 2012 1 comment

Today, I have start mysql with a central NAS storage. The error messages file show message following

InnoDB: Unable to lock ./ibdata1, error: 37
120514 16:41:29  InnoDB: Operating system error number 37 in a file operation.
InnoDB: Error number 37 means ‘No locks available’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: Error in creating or opening ./ibdata1
120514 16:41:29 InnoDB: Could not open or create data files.
120514 16:41:29 InnoDB: If you tried to add new data files, and it failed here,
120514 16:41:29 InnoDB: you should now edit innodb_data_file_path in my.cnf back
120514 16:41:29 InnoDB: to what it was, and remove the new ibdata files InnoDB created
120514 16:41:29 InnoDB: in this failed attempt. InnoDB only wrote those files full of
120514 16:41:29 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
120514 16:41:29 InnoDB: remove old data files which contain your precious data!
120514 16:41:29 [ERROR] Plugin ‘InnoDB’ init function returned error.
120514 16:41:29 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
120514 16:41:29 [ERROR] Unknown/unsupported storage engine: InnoDB
120514 16:41:29 [ERROR] Aborting
120514 16:41:29 [Note] /usr/sbin/mysqld: Shutdown complete

Solution: Add nolock option into nfs mount entry in /etc/fstab as below and restart netfs. The mysql can starts.

wtt.isilon.local:/ifs/wtt/dbdata                /dbdata  nfs     rsize=65536,wsize=65536,nolock

 

Categories: mySQL troubleshooting

ERROR 1005 (HY000): Can’t create table ‘socialmm.#sql-7993_d’ (errno: 121)

March 15, 2012 Leave a comment

I run a patching script for my MySQL DB, it shows the following errors. I search the scripts and find no create table ddl. Why the errors comes out ?.

ERROR 1005 (HY000): Can’t create table ‘socialmm.#sql-7993_d’ (errno: 121)

Finally,  I find out some hints from ” http://www.linuxidc.com/Linux/2011-09/43489.htm”. try to run the command below. The cause of the problem can be found out.

MYSQL> SHOW ENGINE INNODB STATUS\G

 

————————

LATEST FOREIGN KEY ERROR

————————

120314 15:53:49 Error in foreign key constraint creation for table `socialmm`.<result 2 when explaining filename ‘#sql-7993_d’>.

A foreign key constraint of name `socialmm`.`fk_CASE_REPLIES_CASES`

already exists. (Note that internally InnoDB adds ‘databasename’

 

Categories: mySQL troubleshooting

ERROR 1146 (42S02)

March 15, 2012 Leave a comment

Today, I duplicate a production database for testing updating script. Once I apply the script, it shows  errors below. I am sure that all tables are existing.

mysql> source 1b2aScriptAlter.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1146 (42S02): Table ‘socialMM.TEAM_USER’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.AUDIT_ACTIONS’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.MSGSTATE_0001’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.SIP_OFFLINE_MSG’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.PERSONAL_MESSAGES’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.PRIVILEGE’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.USERS’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.IM_ACCOUNTS’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.IM_ACCOUNTS’ doesn’t exist

ERROR 1146 (42S02): Table ‘socialMM.REALM_USER’ doesn’t exist

Finally, I compare the setting between my testing db and the production database. The variable “lower_case_table_names” in the production is set to “1”, but in my testing db default value is “0”. Finally, I see that default table_names is case sensitive. So that I set the variable to ‘1’ and restart the testing database. The errors are gone.

 

 

 

 

 

Categories: mySQL troubleshooting

Fix “InnoDB: Unable to lock ./ib_logfile0, error: 11”

February 27, 2012 Leave a comment

Symptoms

1. The mySQL database hung

2. Reboot the server try to restart the mySQL database

3. After that, the mySQL database couldn’t start, and showed following errors

120226 12:23:59 [Note] Plugin ‘FEDERATED’ is disabled.

120226 12:23:59 InnoDB: The InnoDB memory heap is disabled

120226 12:23:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins

120226 12:23:59 InnoDB: Compressed tables use zlib 1.2.3

120226 12:23:59 InnoDB: Using Linux native AIO

120226 12:23:59 InnoDB: Initializing buffer pool, size = 8.0G

120226 12:23:59 InnoDB: Completed initialization of buffer pool

InnoDB: Unable to lock ./ib_logfile0, error: 11

InnoDB: Check that you do not already have another mysqld process

InnoDB: using the same InnoDB data or log files.

InnoDB: Error in opening ./ib_logfile0

120226 12:23:59 [ERROR] Plugin ‘InnoDB’ init function returned error.

120226 12:23:59 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

120226 12:23:59 [ERROR] Unknown/unsupported storage engine: InnoDB

120226 12:23:59 [ERROR] Aborting

 

Fixed Procedures

1. Go to the database directory

2. Backup the ./ib_logfile0 and copy a new one

# mv ib_logfile0 ib_logfile0.bkup

# cp -a ib_logfile0.bkup ib_logfile0

3 Restart the mySQL database

# /etc/init.d/mysql.server start

 

Categories: mySQL troubleshooting