Tag Archives: mysql

Mysql cannot allocate memory for the buffer pool solution

Further optimize the reference documentation for problem cause resolution

Cause of the problem
Last night, I modified the database and added a “FULLTEXT” index for the field. However, this morning, I found that the database could not be connected and could not be restarted. So I checked the log file:

cat /var/log/mysqld.log

The following error log was found:

130728  6:50:14 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
130728  6:50:14 InnoDB: Completed initialization of buffer pool
130728  6:50:14 InnoDB: Fatal error: cannot allocate memory for the buffer pool

The short answer is that there is no way to allocate 128M memory for mysql’s Buffer pool.
reason is obvious, the lack of memory, after looking up data found that the original parameter is mainly used to cache innodb table index, data, the buffer when inserting data. My server only has 1 gb of memory, so this parameter crashed mysql when it started allocating the cache for the index.
The solution
The configurations for mysql are inside /ect/ mysqlt.cnf (the Ubuntu directory is slightly different, where you can find it using whereis my.cnf).

vim /etc/my.cnf

You can see the fourth line:

#innodb_buffer_pool_size = 128M

The default configuration shown here is 128M, which is the annotation status. We just need to remove the annotation and change 128 to a smaller size (depending on your personal situation). I will change it to:

innodb_buffer_pool_size = 50M

That’s about it.
Further optimization
Generally appear this kind of problem students, the memory is certainly not large, so we can set swap partition, which is often said virtual memory.
is illustrated the role of the swap here won’t do, simply means can assist Mem points of memory.
direct code:

dd if=/dev/zero of=/swapfile bs=1M count=1024
mkswap /swapfile
swapon /swapfile

Finally, add the code /swapfile swap swap defaults 0 0 to the /etc/fstab file.

free

If you can see Mem and SWAP, it’s successful.
and finally restart mysql.
Reference documentation
http://hongjiang.info/aliyun-vps-mysql-aborting/

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Mysql database reported an error when loading file contents into the table:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
       LINES TERMINATED BY '\r\n';

ERROR 1148: The used command is not allowed with this MySQL version
. If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version
. For security reasons, The default is not allowed to load data remotely from client host.
Solution:
in two steps:

SET GLOBAL local_infile=1；

When connecting to the database

mysql -u Name -p --local-infile=1;

When using

  conn = pymysql.connect(host="localhost",
                           user="root",
                           password="111",
                           db="work2forecast",
                           charset="utf8mb4",
                           local_infile=1)

WordPress appears Too many failed login attempts error solution

Use idle resources to earn pocket money (suitable for school students and housewives)
Solutions:
1. Log in to the database
mysql -uroot -p
Enter the password
2. Select your database instance (e.g., my instance name is Blog)
use blog;
3. Execute the following command (the table name depends on what the prefix is and find the table with the _options suffix).
Update TB_options Set option_value= ‘where option_name=’ WAS_LIMIT_login_lockouts’;

use the idle resources of computer to earn pocket money (suitable for students and housewives)
online generating external chain tools (international version)
online generating external chain tools (Chinese version)

MariaDB startup error: Failed to start MariaDB database server

Failed to start MariADB Database Server

Background: It's strange, mysql has been running well, suddenly can not connect up, check the process, found mysql processes are gone!

[root@host-39-108-217-12 mariadb]# systemctl status mariadb.service
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Fri 2018-07-06 08:54:32 CST; 1min 18s ago
Process: 29269 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)
Process: 29268 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
Process: 29238 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 29268 (code=exited, status=0/SUCCESS)

Jul 06 08:54:31 host-39-108-217-12 systemd[1]: Starting MariaDB database server...
Jul 06 08:54:31 host-39-108-217-12 mariadb-prepare-db-dir[29238]: Database MariaDB is probably initialized in /var/lib/mysql/data already, nothing is done.
Jul 06 08:54:31 host-39-108-217-12 mysqld_safe[29268]: 180706 08:54:31 mysqld_safe Logging to '/var/lib/mysql/logs/mariadb.log'.
Jul 06 08:54:31 host-39-108-217-12 mysqld_safe[29268]: 180706 08:54:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/data
Jul 06 08:54:32 host-39-108-217-12 systemd[1]: mariadb.service: control process exited, code=exited status=1
Jul 06 08:54:32 host-39-108-217-12 systemd[1]: Failed to start MariaDB database server.
Jul 06 08:54:32 host-39-108-217-12 systemd[1]: Unit mariadb.service entered failed state.
Jul 06 08:54:32 host-39-108-217-12 systemd[1]: mariadb.service failed.

View mariadb error messages
[root@host-39-108-217-12 mariadb]#  grep 'ERROR' /var/lib/mysql/logs/mariadb.log 
180706  8:47:23 [ERROR] Plugin 'InnoDB' init function returned error.
180706  8:47:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180706  8:47:23 [ERROR] Unknown/unsupported storage engine: InnoDB
180706  8:47:23 [ERROR] Aborting
180706  8:48:48 [ERROR] Plugin 'InnoDB' init function returned error.
180706  8:48:48 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180706  8:48:48 [ERROR] Unknown/unsupported storage engine: InnoDB
180706  8:48:48 [ERROR] Aborting
180706  8:49:38 [ERROR] Plugin 'InnoDB' init function returned error.
180706  8:49:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180706  8:49:38 [ERROR] Unknown/unsupported storage engine: InnoDB
180706  8:49:38 [ERROR] Aborting
180706  8:54:31 [ERROR] Plugin 'InnoDB' init function returned error.
180706  8:54:31 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180706  8:54:31 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180706  8:54:31 [ERROR] Unknown/unsupported storage engine: InnoDB
180706  8:54:31 [ERROR] Aborting

The analysis found insufficient memory, just restart the server!

DB-Lib error message 20002, severity 9

reprinted
Db-lib error message 20002, severity 9
complete error response: 20002, b ‘db-lib error message 20002, severity 9:\nAdaptive Server connection failed (i0.185.43.12:1433)\n ‘
Problem description:
In Python, using PSMSSQL to connect to a remote database, error reported. It is no problem to connect the remote machine through other machines, and it is also no problem to connect the remote machine with other tools (SQLCMD, Naticat, etc.) on my own machine, and locate the problem in the use of PyMSSQL library on this machine
Solutions:
Specify charset=”CP936″ at each pymssql.connect

conn = pymssql.connect(host='localhost',server='.',user='sa',password='XXXX', database='aaa',charset="CP936")

ubuntu install mysqlclient error ERROR: Failed building wheel for mysqlclient solution

Under Windows error
This site has been written by many predecessors, go to this site to download the corresponding file installation is good I will not teach fish to swim.
An error under ubuntu

(cai_django) ubuntu@VM-0-2-ubuntu:~$ pip3 install mysqlclient
Looking in indexes: http://mirrors.tencentyun.com/pypi/simple
Collecting mysqlclient
  Downloading http://mirrors.tencentyun.com/pypi/packages/d0/97/7326248ac8d5049968bf4ec708a5d3d4806e412a42e74160d7f266a3e03a/mysqlclient-1.4.6.ta
     |████████████████████████████████| 92kB 711kB/s 
Building wheels for collected packages: mysqlclient
  Building wheel for mysqlclient (setup.py) ... error
  ERROR: Command errored out with exit status 1:
   command: /home/ubuntu/.virtualenvs/cai_django/bin/python3 -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-jqj_z28, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' bd
       cwd: /tmp/pip-install-jqj_z28y/mysqlclient/
  Complete output (31 lines):
  running bdist_wheel
  running build
  running build_py
  creating build
  creating build/lib.linux-x86_64-3.6
  creating build/lib.linux-x86_64-3.6/MySQLdb
  copying MySQLdb/__init__.py -> build/lib.linux-x86_64-3.6/MySQLdb
  copying MySQLdb/_exceptions.py -> build/lib.linux-x86_64-3.6/MySQLdb
  copying MySQLdb/compat.py -> build/lib.linux-x86_64-3.6/MySQLdb
  copying MySQLdb/connections.py -> build/lib.linux-x86_64-3.6/MySQLdb
  copying MySQLdb/converters.py -> build/lib.linux-x86_64-3.6/MySQLdb
  copying MySQLdb/cursors.py -> build/lib.linux-x86_64-3.6/MySQLdb
  copying MySQLdb/release.py -> build/lib.linux-x86_64-3.6/MySQLdb
  copying MySQLdb/times.py -> build/lib.linux-x86_64-3.6/MySQLdb
  creating build/lib.linux-x86_64-3.6/MySQLdb/constants
  copying MySQLdb/constants/__init__.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
  copying MySQLdb/constants/CLIENT.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
  copying MySQLdb/constants/CR.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
  copying MySQLdb/constants/ER.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
  copying MySQLdb/constants/FIELD_TYPE.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
  copying MySQLdb/constants/FLAG.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
  running build_ext
  building 'MySQLdb._mysql' extension
  creating build/temp.linux-x86_64-3.6
  creating build/temp.linux-x86_64-3.6/MySQLdb
  x86_64-linux-gnu-gcc -pthread -DNDEBUG -g -fwrapv -O2 -Wall -g -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY6m -I/home/ubuntu/.virtualenvs/cai_django/include/python3.6m -c MySQLdb/_mysql.c -o build/temp.linux-x86_64-3.6/MySQLdb/_mysql.o
  x86_64-linux-gnu-gcc -pthread -shared -Wl,-O1 -Wl,-Bsymbolic-functions -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-Bsymbolic-functions -Wl,-z,re-x86_64-3.6/MySQLdb/_mysql.o -lmysqlclient -lpthread -lz -lm -lrt -latomic -lssl -lcrypto -ldl -o build/lib.linux-x86_64-3.6/MySQLdb/_mysql.cpyth
  /usr/bin/ld: cannot find -lssl
  /usr/bin/ld: cannot find -lcrypto
  collect2: error: ld returned 1 exit status
  error: command 'x86_64-linux-gnu-gcc' failed with exit status 1
  ----------------------------------------
  ERROR: Failed building wheel for mysqlclient
  Running setup.py clean for mysqlclient
Failed to build mysqlclient
Installing collected packages: mysqlclient
    Running setup.py install for mysqlclient ... error
    ERROR: Command errored out with exit status 1:
     command: /home/ubuntu/.virtualenvs/cai_django/bin/python3 -u -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-jqj_zze, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' all-headers /home/ubuntu/.virtualenvs/cai_django/include/site/python3.6/mysqlclient
         cwd: /tmp/pip-install-jqj_z28y/mysqlclient/
    Complete output (31 lines):
    running install
    running build
    running build_py
    creating build
    creating build/lib.linux-x86_64-3.6
    creating build/lib.linux-x86_64-3.6/MySQLdb
    copying MySQLdb/__init__.py -> build/lib.linux-x86_64-3.6/MySQLdb
    copying MySQLdb/_exceptions.py -> build/lib.linux-x86_64-3.6/MySQLdb
    copying MySQLdb/compat.py -> build/lib.linux-x86_64-3.6/MySQLdb
    copying MySQLdb/connections.py -> build/lib.linux-x86_64-3.6/MySQLdb
    copying MySQLdb/converters.py -> build/lib.linux-x86_64-3.6/MySQLdb
    copying MySQLdb/cursors.py -> build/lib.linux-x86_64-3.6/MySQLdb
    copying MySQLdb/release.py -> build/lib.linux-x86_64-3.6/MySQLdb
    copying MySQLdb/times.py -> build/lib.linux-x86_64-3.6/MySQLdb
    creating build/lib.linux-x86_64-3.6/MySQLdb/constants
    copying MySQLdb/constants/__init__.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
    copying MySQLdb/constants/CLIENT.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
    copying MySQLdb/constants/CR.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
    copying MySQLdb/constants/ER.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
    copying MySQLdb/constants/FIELD_TYPE.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
    copying MySQLdb/constants/FLAG.py -> build/lib.linux-x86_64-3.6/MySQLdb/constants
    running build_ext
    building 'MySQLdb._mysql' extension
    creating build/temp.linux-x86_64-3.6
    creating build/temp.linux-x86_64-3.6/MySQLdb
    x86_64-linux-gnu-gcc -pthread -DNDEBUG -g -fwrapv -O2 -Wall -g -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTI3.6m -I/home/ubuntu/.virtualenvs/cai_django/include/python3.6m -c MySQLdb/_mysql.c -o build/temp.linux-x86_64-3.6/MySQLdb/_mysql.o
    x86_64-linux-gnu-gcc -pthread -shared -Wl,-O1 -Wl,-Bsymbolic-functions -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-Bsymbolic-functions -Wl,-z,ux-x86_64-3.6/MySQLdb/_mysql.o -lmysqlclient -lpthread -lz -lm -lrt -latomic -lssl -lcrypto -ldl -o build/lib.linux-x86_64-3.6/MySQLdb/_mysql.cpy
    /usr/bin/ld: cannot find -lssl
    /usr/bin/ld: cannot find -lcrypto
    collect2: error: ld returned 1 exit status
    error: command 'x86_64-linux-gnu-gcc' failed with exit status 1
    ----------------------------------------
ERROR: Command errored out with exit status 1: /home/ubuntu/.virtualenvs/cai_django/bin/python3 -u -c 'import sys, setuptools, tokenize; sys.argvnt/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(cternally-managed --compile --install-headers /home/ubuntu/.virtualenvs/cai_django/include/site/python3.6/mysqlclient Check the logs for full com

The key point is ERROR: Failed Building Wheel for MysqlClient
The solution

sudo apt-get install python3 python-dev python3-dev
sudo apt-get install build-essential libssl-dev libffi-dev
sudo apt-get install libxml2-dev libxslt1-dev zlib1g-dev

Install it all again

pip3 install mysqlclient

The problem is solved.

Connection for controluser as defined in your configuration failed phpmyadmin xampp

Use XMAPP to configure mysql on Mac!!
Maybe I had installed mysql before, and started the installation of XAMPP5.6.12. But after the installation, I found that I could not start mysql. I was so surprised.
Connection for Controluser as defined in your Configuration Failed phpmyadmin Xampp, followed by a long what check Hostname, User, Password do not pull ~~
Various online search answers, stackflow, CSDN turned over, what config. Inc. PHP setting is wrong, set permissions ah,
Sudo rm/Applications/XAMPP/xamppfiles/bin/mysql server
Wait, I’ve tried them all, but it hasn’t worked…
Later, I realized that it was not phpmyAdmin configuration, but mysql that used the same port, so I uninstalled and deleted all the files of XMApp, and just reinstalled the magic one.
Hopefully this will be helpful for new installers, don’t tinker with configuration files!

Centos7 executes the command service mysqld start and prompts Failed to start mysqld.service: Unit mysqld.service not loaded

An error condition
After mysql is installed on Centos7, service Mysqld Start cannot be started and Service Mysqld Stop cannot be stopped
The solution
find/-name mysql.server
The following path is found
/usr/share/mysql/mysql.server
so
/usr/share/mysql/mysql.server start
It can start successfully
Use the/usr/share/mysql/mysql. Server stop
You can close it successfully
 

MySQL failed to start prompt: job failed to start

Today, I modified the character set for the mysql database under Linux. In order to facilitate operation, I modified the permissions for the configuration file sudo Chmod 777 My.cnF

after modification, start: Job failed to start appears.
Nothing is printed in the
error log.

after the character set configuration is removed still prompt cannot start.

finally consider the possibility that only permissions have been modified due to permissions.

sudo chomd o-w my.cnf

sudo chmod g-w my.cnf

and then start MySQL.

After installing MySQL again under Linux, the solution of ‘MySQL module failed to start’ appears

had previously installed Mysql database on CentOS 6.4 and wanted to upgrade to a newer version so I updated the database. However, after reinstallation, run: service mysqld start to restart the database will always appear with the following prompt:

MySQL Daemon failed to start.
Starting mysqld: [failed]

Hint for

. If direct input mysql – root – p landing will appear (HY000)/mysql ERROR 2002: Can ‘t connect to local mysql server through socket’/var/lib/mysql/mysql. The sock ‘(2) errors. In general, the MySql server always fails to start. Many of the solutions offered online have not worked. Finally, the solution is as follows:

runs the following command: 

 rm -fr /var/lib/mysql/*
rm /var/lock/subsys/mysqld 
 killall mysqld

service mysqld restart

then run service mysqld start to launch mysql as shown in the figure below:

then run: service mysqld start

/etc/rc. D/init. D/mysqld status

view the database state as follows

found that the database is working properly problem solved.