Category Archives: MYSQL

MySQL Error Number 1005 Can’t create table (Errno:150)

mysql database 1005 error solution

MySQL Error Number 1005 Can’t create table ‘.\mydb\#sql-328_45.frm’ (errno: 150)

MySQL Error Number 1005
Can’t create table ‘.\mydb\#sql-328_45.frm’ (errno: 150)

If you get this error while trying to create a foreign key, it can be pretty frustrating. The error about not being able to create a .frm file seems like it would be some kind of OS file permission error or something but this is not the case. This error has been reported as a bug on the MySQL developer list for ages, but it is actually just a misleading error message.

In every case this is due to something about the relationship that MySQL doesn’t like. Unfortunately it doesn’t specify what the exact issue is.

First Steps:

If you have admin permission on the server, you may want to start by running the MySQL command “SHOW INNODB STATUS” (or MySQL 5.5 “SHOW ENGINE INNODB STATUS”)immediately after receiving the error. This command displays log info and error details. (Thanks Jonathan for the tip)

If your script runs fine on one server, but gives an error when you try to run it on a different server, then there is a good chance that #6 is the problem. Different versions of MySQL have different default charset setting and you may haveunknowinglyassigneddifferentcharsets on the different servers.

Known Causes:

Below is a running list of known causes that people have reported for the dreaded errno 150:

The two key fields type and/or size is not an exact match. For example, if one is INT(10) the key field needs to be INT(10) as well and not INT(11) or TINYINT. You may want to confirm the field size using SHOW CREATE TABLE because Query Browser will sometimes visually show just INTEGER for both INT(10) and INT(11). You should also check that one is not SIGNED and the other is UNSIGNED. They both need to be exactly the same. (More about signed vs unsigned here).

One of the key field that you are trying to reference does not have an index and/or is not a primary key. If one of the fields in the relationship is not a primary key, you must create an index for that field. (thanks to Venkatesh and Erichero and Terminally Incoherent for this tip)

The foreign key name is a duplicate of an already existing key. Check that the name of your foreign key is unique within your database. Just add a few random characters to the end of your key name to test for this. (Thanks to Niels for this tip)

One or both of your tables is a MyISAM table. In order to use foreign keys, the tables must both be InnoDB. (Actually, if both tables are MyISAM then you won’t get an error message – it just won’t create the key.) In Query Browser, you can specify the table type.

You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values. (Thanks to Sammy and J Jammin)

Make sure that the Charset and Collate options are the same both at the table level as well as individual field level for the key columns. (Thanks to FRR for this tip)

You have a default value (ie default=0) on your foreign key column (Thanks to Omar for the tip)

One of the fields in the relationship is part of a combination (composite) key and does not have it’s own individual index. Even though the field has an index as part of the composite key, you must create a separate index for only that key field in order to use it in a constraint. (Thanks to Alex for this tip)

You have a syntax error in your ALTER statement or you have mistyped one of the field names in the relationship (Thanks to Christian & Mateo for the tip)

The name of your foreign key exceeds the max length of 64 chars. (Thanks to Nyleta for the tip)

The MySQL documentation includes apage explaining requirements for foreign keys. Though they don’t specifically indicate it, these are all potential causes of errno 150. If you still haven’t solved your problem you may want to check there for deeper technical explainations.

If you run into this error and find that it’s caused by something else, please leave a comment and I’ll add it to the list.

SQL Developer:Unable to find a Java Virtual Machine [Solved]

The 64 bit Oracle database and 32-bit Oracle client are installed. When the SQL developer of the client is opened for the first time in the start menu, I will be prompted to enter the path of java.exe. I choose the JDK path of the Oracle database. After confirming, I will report an error

Baidu found a solution, because the JDK in the 64 bit Oracle database is 64 bit, but the installed Oracle client is 32-bit, so it is not compatible

There are generally two ways to solve the problem

1) Download Oracle SQL developer x64 from the Internet( http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html), and then replace the original directory: D:: (APP/Oracle/product/11.1.0) dB_ 1. 32 bit Oracle SQL developer under sqldeveloper. In this way, restart Oracle SQL developer and make the path of java.exe
2) install JDK6 x86, which is 32-bit JDK. Although our system is 64 bit, it is also compatible with 32-bit JDK

Because of other needs, I have to use a 32-bit client, so I chose the second method and downloaded the 32-bit JDK from the Oracle official website to complete the installation. After that, modify the java.exe startup path of SQL developer. At this time, you can’t click SQL developer to define the java.exe path in the start menu, because the path has been initialized. Once you click SQL developer, you will report the above error. At this point, to modify the java.exe path, you need to modify the configuration file

1. Find the sqldeveloper.conf file in the following path

D:\app\mattran\product\11.2.0\client_ 1\sqldeveloper\sqldeveloper\bin

2. Open the file

3. Change the path at the red line to the path of the 32-bit JDK installed just now, as follows:

4. Save and exit

Then click Oracle – oraclient11g in the start menu_ SQL developer under home1 can be started successfully

Solve the problem that SQL Server blocks the state’openrowset/opendatasourc of component ‘ad hoc distributed queries’

1.1 connection

select * from openrowset( 'SQLOLEDB ', 'sql server name '; 'username '; 'password ',database name.dbo.table name)

1.2 error prompt

Message 15281, level 16, status 1, line 3
sql server has blocked access to the state “openrowset/opendatasource” of the component “ad hoc distributed queries” because the component has been shut down as part of the security configuration of this server. System administrators can use sp_ Configure enables “ad hoc distributed queries”. For more information about enabling ad hoc distributed queries, search for ad hoc distributed queries in SQL Server Books Online

2. Solutions

2.1. Open the ad hoc distributed queries component and execute the following statements in the SQL query editor:

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

2.2. Close the ad hoc distributed queries component and execute the following statements in the SQL query editor:

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

MySQL Error: the solution of command not found

When building zrlog under Tomcat architecture, MySQL will report an error. As follows:

When entering the MySQL command

Error report

# mysql: command not found
   This command does not exist

When restarting MySQL service

# /etc/init.d/mysqld restart

Error report

# mysqld_safe A mysqld process already exists

Try

First # PS aux | grep grep , and then # kill - 9 PID , kill MySQL related processes, no, report an error

First # kill MySQL , and then restart, or not, report an error

Modify the/etc/my.cnf file, clear the relevant rules, restart mysql, or not

Solution:

MySQL is clearly installed and running normally. How can this problem occur

Method 1 (palliative treatment)

Run the command as an absolute path. For example, my native path is /usr/local/MySQL/bin/MySQL

# /usr/local/mysql/bin/mysql restart

Method 2 (cure the root cause)

# ln -s /usr/local/mysql/bin/mysql /usr/bin

It is equivalent to creating a linked file- S for soft link

# mysql -uroot -p

The solution of job failed to start when modifying MySQL character encoding

Found the following from the web: $sudo gedit /etc/mysql/my.cnf

Under [client] add.

default-character-set=utf8

Under [mysqld] add.

default-character-set=utf8

 Then save and exit

$ /etc/init.d/mysql restart

 The result appears:

Since the script you are attempting to invoke has been converted to an Upstart job, you may also use the stop(8) and then start(8) utilities, e.g. stop mysql ; start mysql. The restart(8) utility is also available. start: Job failed to start

It may be a version problem, check the method of modifying the character encoding method for versions after 5.5, and found that the method of modifying [mysqld] has changed to

[mysqld] added under should read.

character-set-server=utf8

collation-server=utf8_general_ci

save and quit

$ /etc/init.d/mysql restart

success

To access the MySQL console.

show variables like 'character%';

+———————————–+———————————–+

| Variable_name | Value |

+———————————–+———————————–+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+————————————+———————————–+

8 rows in set (0.00 sec)

[How to Solve] Starting MySQL.. ERROR! The server quit without updating PID file

Today, I found that MySQL was suddenly unable to access, and the startup service reported an error

Starting MySQL.. ERROR! The server quit without updating PID file[FAILED]....pid

Check the MySQL process and report an error

MySQL is not running,but lock file (/var/lock/subsys/mysql[FAILED]

It’s no use deleting this MySQL file

Finally, it is found that MySQL logs burst disk space, resulting in unable to write logs. Just delete some logs

In fact, you can set the logging policy. If MySQL is a stand-alone server, there is no need to log. Note the logging configuration

Comments in/etc/my.cnf

#log-bin=mysql-bin
#binlog_format=mixed

Or set the log expiration time and keep the log for 7 days

Add in/etc/my.cnf

expire_logs_days = 7

The troubleshooting record is as follows:

The error log could not be found at first. Set the error log to be recorded

/etc/my.cnf

Add content

log_error = /var/log/mysql/error.log

Add error log directory

mkdir /var/log/mysql

Start the service again and find the log

180112 11:11:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180112 11:11:29 [Note] Plugin 'FEDERATED' is disabled.
180112 11:11:29 InnoDB: The InnoDB memory heap is disabled
180112 11:11:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180112 11:11:29 InnoDB: Compressed tables use zlib 1.2.3
180112 11:11:29 InnoDB: Using Linux native AIO
180112 11:11:29 InnoDB: Initializing buffer pool, size = 128.0M
180112 11:11:29 InnoDB: Completed initialization of buffer pool
180112 11:11:29 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
180112 11:11:29  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 831375219, file name ./mysql-bin.000016
180112 11:11:29  InnoDB: Waiting for the background threads to start
180112 11:11:30 InnoDB: 5.5.31 started; log sequence number 9124484533
180112 11:11:30 [Note] Recovering after a crash using mysql-bin
180112 11:11:32 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 428, event_type: 2
180112 11:11:32 [Note] Starting crash recovery...
180112 11:11:32 [Note] Crash recovery finished.
03:11:32 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 134077 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x7aa975]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x6831a4]
/lib64/libpthread.so.0[0x3894c0f710]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
180112 11:11:32 mysqld_safe mysqld from pid file /var/lib/mysql/******.pid ended

Finally, try to find the disk space problem

Run DF – LH

Filesystem                        Size  Used Avail Use% Mounted on
/dev/mapper/vg_research2-lv_root   50G   47G  5.3M 100% /
tmpfs                              16G     0   16G   0% /dev/shm
/dev/sda1                         485M   39M  421M   9% /boot
/dev/mapper/vg_research2-lv_home  484G  308G  152G  67% /home

Delete some MySQL operation logs, and the service starts successfully

[root@cm ~]# df -lh
Filesystem                        Size  Used Avail Use% Mounted on
/dev/mapper/vg_research2-lv_root   50G   44G  3.1G  94% /
tmpfs                              16G     0   16G   0% /dev/shm
/dev/sda1                         485M   39M  421M   9% /boot
/dev/mapper/vg_research2-lv_home  484G  308G  152G  67% /home
[root@cm ~]# service mysql restart
Shutting down MySQL....                                    [  OK  ]
Starting MySQL..                                           [  OK  ]

CentOS7 MYSQL Connect Error: [ERROR] InnoDB: The innodb_system data file ‘ibdata1’ must be writable

MySQL has not been able to connect to my database, enter the password can not enter mysql

Report the error of not connecting to the service

Can ‘t connect to local MySQL server through socket ‘/tmp/mysql.sock ‘(2)

I just checked the MySQL log

cat /var/log/mysqld.log

An error was seen that the file does not have permissions

You can modify the read and write permissions of the file or the permissions of all files in the directory

Solution:

You can enter mysql

The database could not be exclusively locked to perform the operation (SQL Server 5030 error solution)

The database could not be exclusively locked to perform the operation

SQL Server 5030 error solution

Today, when using SQL server, because you forgot to set the collation when creating the database, it is messy to insert Chinese characters into the database. So when you modify the collation in the options of database, the database could not be exclusively locked to perform the operation. You cannot modify the character set to Chinese_ PRC_ 90_ CI_ AS。

It took a long time to find a solution, as follows:

1. Execute SQL alter database db_ database SET SINGLE_ USER WITH ROLLBACK IMMEDIATE

Change to single user mode

2. Then close all the query windows and change the coordination property of options to Chinese_ PRC_ 90_ CI_ AS

3. Execute SQL alter database db_ database SET MULTI_ USER

Then change to multi-user mode

Phpmyadmin Bottom Waring: “The configuration file now needs a secret passphrase (blowfish_secret). ”

Reason:

PhpMyAdmin needs this secret to encrypt cookies based on security, so it always prompts you

In the phpMyAdmin directory, mine is/usr/local/nginx/phpMyAdmin

Implementation

sudo cp config.sample.inc.php config.inc.php     //Copy and rename
sudo chown www-data: config.inc.php //Change the user and group that will be nginx

Local arbitrary generation of a section of SSL code

openssl rand -base64 32

Back

LhmTZbV1HV9t0qRtU6/M0rAZ9fAzGKkNc8XRzSjwxH4=

Copy the random password returned, then open config. Inc. PHP and search $CFG [‘blowfish]_ Paste the copied password to the back of this configuration

$cfg['blowfish_secret'] = 'LhmTZbV1HV9t0qRtU6/M0rAZ9fAzGKkNc8XRzSjwxH4=';

 

[Solved] Tns-12555: TNS: permission denied error resolution

>Oracle 10g + Redhat5, installed and used everything fine, installed user oracle.

Today I started sqlplus with oracle user normally

[abc@rhel4 admin]$ sqlplus/as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Apr 23 12:11:25 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

SQL>

However, when the listener is started, an error is reported.

[abc@rhel4 admin]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 23-APR-2009 12:13:06

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> start

Starting /u01/app/oracle/product/10.2.0/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 10.2.0.1.0 – Production

System parameter file is /u01/app/oracle/product/10.2.0/network/admin/listener.ora

Log messages written to /u01/app/oracle/product/10.2.0/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

TNS-12555: TNS:permission denied

TNS-12560: TNS:protocol adapter error

TNS-00525: Insufficient privilege for operation

Linux Error: 1: Operation not permitted

The log error message shows that it has something to do with permissions, and finally we found a hidden message.

The /var/tmp/.oracle directory is special, if oracle does not have permission to operate this directory, oracle users cannot access the /var/tmp/.oracle folder.

The method is simple: chmod 777 /var/tmp/.oracle

and you’re good to go.