Category Archives: MYSQL

Mysql Auto Downtime ERROR: InnoDB: Cannot allocate memory for the buffer pool [Solved]

When I went to work this morning, I found that I couldn’t access the website built by WordPress. I reported the following error:

Error establishing a database connection

Quickly log on to your own Alibaba cloud server (centos7.4) to view the MySQL service. Through PS – EF | grep mysql, you find that there is no MySQL process

The first thing to do is to check the MySQL log and find the corresponding error exception through VI/var/log/mysqld.log

2018-12-26T02:06:16.148626Z 0 [Note] Event Scheduler: Purging the queue. 0 events
"/var/log/mysqld.log" 623L, 59293C
2019-02-01T07:56:07.052989Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-02-01T07:56:07.052991Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-02-01T07:56:07.052994Z 0 [Note] InnoDB: Using Linux native AIO
2019-02-01T07:56:07.053336Z 0 [Note] InnoDB: Number of pools: 1
2019-02-01T07:56:07.053443Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-02-01T07:56:07.055009Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-02-01T07:56:07.055061Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2019-02-01T07:56:07.055067Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-02-01T07:56:07.055073Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2019-02-01T07:56:07.055081Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2019-02-01T07:56:07.055085Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2019-02-01T07:56:07.055088Z 0 [ERROR] Failed to initialize builtin plugins.
2019-02-01T07:56:07.055091Z 0 [ERROR] Aborting

2019-02-01T07:56:07.055108Z 0 [Note] Binlog end
2019-02-01T07:56:07.055159Z 0 [Note] Shutting down plugin 'CSV'
2019-02-01T07:56:07.055380Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

It can be seen that there is not enough memory, resulting in the oom problem. Continue to view the memory with free – M and find the following:

free -m
              total        used        free      shared  buff/cache   available
Mem:            992         270         107          50         614         496
Swap:             0           0           0

There is not enough memory, only 107m space is available, swap free space is 0

So there are two solutions:

1: Modify the/etc/my.inf file to delete InnoDB_ buffer_ pool_ Size down

innodb_buffer_pool_size = 32M

2: Add swap file

For specific steps, see Adding swap swap space on CentOS 7

So far, the problem has been solved

—————————————–

There is no fear of problems, the solution is very important

MYSQL Incorrect usage of UNION and ORDER BY [How to Solve]

The statement with this error is invalid

select xxx from aaa order by xxx
union all
select yyy from bbb order by yyy

The reason for the error is that if you use union all and order by at the same time, the clause of union all needs to add brackets

So the following writing will not be reported error

(select xxx from aaa order by xxx)
union all
(select yyy from bbb order by yyy)

incorrect integer value for column [How to Solve]

Recently, I was using the Zend framework and installed a project, and found that

general error: 1366 incorrect integer value: ‘for column’ user appears when registering_ ID ‘at row 1

it’s strange to find that this ID is growing automatically. I found many blogs and finally found out the problem

this kind of problem usually appears on MySQL 5. X. I use MySQL 5.1. The official explanation says: I know that the new version of MySQL has a “bug” in inserting null values. To remove the default check “enable strict SQL mode” when installing mysql, what should we do if we have installed MySQL?The solution is to change the configuration in MySQL and find SQL mode in my.ini

my.ini, The default is: SQL mode = strict_ TRANS_ TABLES,NO_ AUTO_ CREATE_ USER,NO_ ENGINE_ “Substitution”, and modify it to

sql mode = “no”_ AUTO_ CREATE_ USER,NO_ ENGINE_ “Subscription”,

after restarting MySQL, what if it is a virtual host or space. If you can let space business help you change, that is the best, if not, then you can only change the program. Be more standard. Write null instead of null

[Solved] Cause: java.sql.SQLException: Field ‘id‘ doesn‘t have a default value

When doing the product upload function, we reported the error of cause: Java. SQL. Sqlexception: field ‘ID’ doesn’t have a default value (the main error is as follows). After query, it was caused by setting ID self growth in mapper.xml, but not in the database

Solutions:

Step 1: check whether my ID is int type, because I set string type, so I can’t find the self incrementing check box when using Navicat

public class FoodEntity {
    /**
     * Product ID
     */
    @TableId
    private String id; // set to String here, but it will still error out
    // The auto-grow type is int
    private int id

Step 2: open Navicat premium and click design table for the table to be operated

Step 3: check auto growth

Then restart the project to achieve the corresponding operation

mysql connection reports java.math.BigInteger cannot be cast to java.lang.Long exception

The following error occurs when using hibernate

java.sql.SQLException: java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:1074)
    at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3590)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2531)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2301)
    at com.mysql.jdbc.ConnectionImpl. < init > (ConnectionImpl.java:834)
    at com.mysql.jdbc.JDBC4Connection. < init > (JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at zttc.itat.user.util.DbUtil.getConnection(DbUtil.java:12)
    at zttc.itat.user.util.AbstractDbUnitTestCase.init(AbstractDbUnitTestCase.java:28)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Later, it was solved by replacing mysql-connector-java-5.1.24.jar with mysql-connector-java-5.1.46.jar.

 

Sqlite Error:com.intellij.execution.ExecutionException: Exception in thread “main” java….

Error when deleting database migrations
Reported error: com.intellij.execution.ExecutionException: Exception in thread “main” java.lang.ClassNotFoundException: org.sqlite.JDBC

Exception in thread “main” java.lang.ClassNotFoundException: org.sqlite.JDBC
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)
at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
at java.base/java.lang.Class.forName0(Native Method)
at java.base/java.lang.Class.forName(Unknown Source)
at com.intellij.database.remote.RemoteJdbcServer.main(RemoteJdbcServer.java:14).
com.intellij.execution.ExecutionException: Exception in thread “main” java.lang.ClassNotFoundException: org.sqlite.JDBC
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)
at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
at java.base/java.lang.Class.forName0(Native Method)
at java.base/java.lang.Class.forName(Unknown Source)
at com.intellij.database.remote.RemoteJdbcServ… (show balloon)

 

 

Solution:

Install the driver

[How to Solve] Library not loaded: libmysqlclient.18.dylib

Exception code:

luasql = require("luasql.mysql");

Exception information:

lua: error loading module 'luasql.mysql' from file '/usr/local/lib/lua/5.3/luasql/mysql.so':
 dlopen(/usr/local/lib/lua/5.3/luasql/mysql.so, 6): Library not loaded: libmysqlclient.18.dylib
 Referenced from: /usr/local/lib/lua/5.3/luasql/mysql.so
 Reason: image not found
stack traceback:
 [C]: in ?
 [C]: in function 'require'
 /Users/daxuan/Documents/LuaWorkspace/22.Lua db.lua:5: in main chunk
 [C]: in ?
[Finished in 0.0s with exit code 1]
[cmd: ['lua', '/Users/daxuan/Documents/LuaWorkspace/22.Lua db.lua']]
[dir: /Users/daxuan/Documents/LuaWorkspace]
[path: /usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:/Applications/VMware Fusion.app/Contents/Public:/usr/local/share/dotnet:~/.dotnet/tools:/Library/Frameworks/Mono.framework/Versions/Current/Commands:/Applications/Xamarin Workbooks.app/Contents/SharedSupport/path-bin]

Solution:

1. First find the location of libmysqlclient.18.dylib file in mysql, and then create a file link through ln command

2. The specific order is as follows

ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/local/lib/libmysqlclient.18.dylib

3. Done!

CentOS7 Install MYSQL8.0.13 error while loading shared libraries: libaio.so.1

Install the database of mysql8.0.13 on centos7, using the installation package compiled on the official website. After downloading and decompressing, you can use it and need to install it

However, an error occurred during database initialization after decompression

/usr/local/mysql/bin/mysqld –initailize –user=mysql –basedir=/usr/local/mysql –datadir=/www/mysql/data

The error after running the command is as follows:

error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

in case of this problem, first check whether the link library file is installed and use the command to check

rpm -qa|grep libaio

After running the command, it is found that there is no link library file in the system

Using the command, yum install libaio-devel.x86_ 64

After the installation is successful, continue to run the initialization command of the database, indicating success

Laravel [1045] Solution Access denied for user’homestead’@’localhost’

In the past few days, learning the Laravel framework encountered database problems.

PDOException in Connector.php line 55:SQLSTATE[HY000] [1045] 
Access denied for user 'homestead'@'localhost' (using password: YES)

The solution to the problem is as follows

1. Confirm that the database.php file is configured correctly.

First check that the information you filled in database.php is correct.

2. Check the .env file

This is the default .env file

APP_ENV=local
APP_KEY=
APP_DEBUG=true
APP_LOG_LEVEL=debug
APP_URL=http://localhost

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

BROADCAST_DRIVER=log
CACHE_DRIVER=file
SESSION_DRIVER=file
QUEUE_DRIVER=sync

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_DRIVER=smtp
MAIL_HOST=mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null

PUSHER_APP_ID=
PUSHER_KEY=
PUSHER_SECRET=

Take part corresponding database
I use the mysql database

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

3. Modify the .env file

Modify the above part as follows

DB_CONNECTION=mysql
DB_HOST=[your db adress]
DB_PORT=[port(3306)]
DB_DATABASE=[db]
DB_USERNAME=[username]
DB_PASSWORD=[password]

Save after modification

4. Restart the service

The initial change was unsuccessful, and it took a long time to find that the cache was not cleared. . .

Clean the cache to restart the service
into the root directory on the command line, issue the following statement.

php artisan cache:clear
php artisan config:clear
php artisan serve

This is enough to refresh.

 

Linux: MySQL Start Error: Starting MySQL.Manager of pid-file quit without updating file.[FAILED]

Just after installing mysql, the following error will be reported at startup:

StartingMySQL.Managerofpid-filequitwithoutupdatingfile.[FAILED]

Solve the starting mysql.manager of PID file quit without updating file. [failed]

Try various methods found from Google in turn, and find that you can solve the problem by executing the following MySQL initialization command:

/usr/local/mysql/bin/mysql_install_db--user=mysql

The following is a collection of solutions to this problem found from Google. Maybe the causes of the problems encountered by everyone will be different for reference:

1、 View error log

The error log is the direction light for us to solve the problem. Without the direction light, we have no direction

I had an error in the installation log once, and there was such an error:

/usr/local/mysql/bin/mysqld:errorwhileloadingsharedlibraries:libstdc++.so.5:cannotopensharedobjectfile:Nosuchfileordirectory

Obviously, the library file libstdc + +. So. 5 is missing. At this time, we can search for the package installation related to libstdc

2、 Maybe it’s SELinux

If it is a CentOS system, SELinux will be turned on by default. At this time, you can turn it off first, open/etc/SELinux/config, change SELinux = enforcing to SELinux = disabled, save the disk, exit and restart the machine

3、 Residual data

It may also be the second time that MySQL is installed on the machine, and some residual data affects the startup of the service. Go to the data directory/data of MySQL, and if there is mysql-bin.index, delete it as soon as possible. It is the culprit

4、 Determine the permissions of the data directory

The data directory is generally & lt; prefix>/ Data, check its ownership. If it is not the owner of MySQL, users and groups of MySQL will be created when installing mysql. At this time, users and groups should be modified

chown-Rmysql:mysql/var/data

5、 No data directory specified

When MySQL does not specify a configuration file, it will use the/etc/my.cnf configuration file. Please open this file to see if there is a specified data directory (dataDir) under the [mysqld] section. If not, set this line under [mysqld]:

datadir=/usr/local/mysql/data

6、 And the MySQL process is running

If this is the second time to install MySQL on the machine, it is very likely that this situation will occur. At this time, there is likely to be a MySQL process running on the system. You can execute the following commands to view the data:

ps-ef|grepmysql

If the result is more than one line, kill it as soon as possible

7、 What’s wrong with the skip federated field

You can check the/etc/my.cnf file to see if there are any skip federated fields that have not been commented out. If so, comment them out immediately

8、 The error log directory does not exist

Maybe you don’t know where the MySQL error log is right now?At this time, you can execute the command to view:

cdmysql/bin
./mysql_safe

At this time, an error will be reported, and the error will be displayed in the error, and the error will be written in the file of the directory. Here, check whether the directory and the file are in, and then solve it according to the log instructions. If not, create the directory and modify the permissions and ownership. Generally, it will be/var/lib/MySQL/MySQL. Error. Note that mysql. Error does not need to be created

cd/var/lib
mkdirmysql
chmod777mysql
chownmysql:mysqlmysql

Now try again. If you still can’t, leave a message for you to see

9、 Maybe it’s data/mysql-bin.index

Antecedent: because the hard disk is full, MySQL can’t start. Log in to have a look and find out why. Delete the log file of MySQL, restart mysql, and find an error: starting mysql.manager of PID file quit without updating file. [failed]

There are many explanations for this reason on the Internet, but none of them are what I want to say. The reason I want to say is actually idiotic: data/mysql-bin.index is not deleted. Data/mysql-bin.index is the file where the index of log file is stored. It is obviously impossible to delete the log file without processing the index file of log file

Delete the data/mysql-bin.index file, and then service mysqld start