Tag Archives: mysql

An error was reported during MySQL 6.0 installation: start service failed

Existing problem:
an error is reported during MySQL 6.0 installation: start service failed, as shown in the figure

Solution:
you need to delete the relevant registry and reinstall it
1) uninstall MySQL that failed to install, uninstall MySQL in control panel → programs and functions, and delete relevant folders and files under the installation path
2) delete the MySQL related directory under the programdata directory on disk C. if it cannot be found, you need to “show hidden folder” operation
3) delete the relevant registry
(1) start → run → CMD → regedit to enter the registry list
(2) delete the following three places in sequence:

HKEY_ LOCAL_ MySQL directory under machine/system/controlset001/services/eventlog/applications; HKEY_ LOCAL_ MySQL directory under machine/system/controlset002/services/eventlog/applications; HKEY_ LOCAL_ MySQL directory under machine/system/currentcontrolset/services/eventlog/applications.

4) Restart the computer and reinstall mysql6.0.

Mysql database error (communications link failure)

Reason: mysql5 defaults the wait_timeout of its connection to 8 hours
modify the configuration file. The steps are as follows:

【1】 Log in to MySQL and CMD
MySQL – H host address – U user name – P user password (for example, MySQL – hlocalhost – uroot – p123456)

【2】 View wait_ Timeout, CMD command:
show global variables like ‘wait_ timeout’;

[3] if wait_ Timeout is too small to be modified. CMD command:
set global wait_ timeout=604800;
set global interactive_ timeout=604800;
after modification, you can test.

How to Solve Mysql8 load data error

First, when executing the load data command, a message appears, loading local data is disabled; this must be enabled on both the client and server sides
Set needs to be set on the server side   local_infile=1

And set MySQL — local infile = 1 – uroot – P when connecting to the client

The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

The mysql.ini file already exists in the installed MySQL directory. You need to create a new my.ini file and add

[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
secure-file-priv=

 

[Solved] ERROR 1396 (HY000): Operation ALTER USER failed for ‘root‘@‘localhost‘

MySQL Connect database error:
1251 client does not support authentication protocol requested by server; consider upgrading Mysql client ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@’localhost’

Pre-registered mysql

mysql -u root -p

Input password

mysql> use mysql;
mysql> select user,host from user;

Note that my root and host are ‘%’
you may execute:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

Change to:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

Operation record:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Mysql ERROR 1067: Invalid default value for ‘date’ [How to Solve]

When adding fields to a table, I suddenly find that the default value of a field of date type is wrong, which is depressing~

After troubleshooting, it turns out that there is a problem with MySQL configuration. Under Wamp, SQL is not set in MySQL 5.7_ Mode.

1. Find [mysqld] in my.ini file

2. If there is no SQL_Mode, add it and modify it if necessary

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
or
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3.Restart MySQL;
use the following commands to operate mysql:
systemctl restart mysqld.service
systemctl start mysqld.service
systemctl stop mysqld.service

Pip3 install Mysqlclient Error: Command “python setup.py egg_info“ failed with error

pip3 install mysqlclient error Command “python setup.py egg_info” failed with error
1. Software description

    centos 8.0python3.6

2. Problem description
Use the command

pip3 install mysqlclient
error
[root@z ~]# pip3 install mysqlclient
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Collecting mysqlclient
  Using cached https://files.pythonhosted.org/packages/3c/df/59cd2fa5e48d0804d213bdcb1acb4d08c403b61c7ff7ed4dd4a6a2deb3f7/mysqlclient-2.0.3.tar.gz
    Complete output from command python setup.py egg_info:
    /bin/sh: mysql_config: Command not found
    /bin/sh: mariadb_config: Command not found
    /bin/sh: mysql_config: Command not found
    mysql_config --version
    mariadb_config --version
    mysql_config --libs
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-build-8k22usrq/mysqlclient/setup.py", line 15, in <module>
        metadata, options = get_config()
      File "/tmp/pip-build-8k22usrq/mysqlclient/setup_posix.py", line 70, in get_config
        libs = mysql_config("libs")
      File "/tmp/pip-build-8k22usrq/mysqlclient/setup_posix.py", line 31, in mysql_config
        raise OSError("{} not found".format(_mysql_config_path))
    OSError: mysql_config not found
    
    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-8k22usrq/mysqlclient/

3. Solutions

yum install python3-devel mysql-devel

MySQL server has gone away Error [How to Solve]

Reference: solution to MySQL server has gone away error – time blog
When we use Mysql to import large file SQL, we may report the MySQL server has gone away error. The problem is the default value setting of  max_allowed_packet configuration is too small. You only need to increase the value of this item and import it again to succeed. This item is used to limit the size of the package received by the MySQL server. Therefore, if the imported file is too large, it may exceed the value set in this item, resulting in unsuccessful import! Let’s take a look at how to view and set the value of this item.

View the value of Max_allowed_packet

show global variables like 'max_allowed_packet';


+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

You can see that the size of this item is only 4m by default. Next, set the value to 150m (1024 * 1024 * 150)

set global max_allowed_packet=157286400;

View the size again

show global variables like 'max_allowed_packet';

By increasing this value, generally speaking, importing SQL with a large amount of data should be successful again. If an error is still reported, continue to increase it. Please note that setting in the command line is only valid for the current time. After restarting the MySQL service, restore the default value, but you can modify the configuration file (you can add max_allowed_packet = 150m in the configuration file my.cnf) To achieve the purpose of permanent validity, but in fact, we do not often import such a large amount of data, so I think the current configuration can take effect through the command, and there is no need to modify the configuration file.

[Solved] Error: read ECONNRESET at TCP.onStreamRead Cannot enqueue Query after fatal error

Error analysis

This error often occurs when I use node to connect to MySQL. I wonder if it is because I do not close the connection every time I query the database after creating a MySQL connection, so the database is always connected and does not respond for a long time, resulting in the following error
error1:

error2:

 

Solution:

error1:

const mysql = require('mysql');
const connInfo = require('./config');

function queryMysql(sql, callback){
    const conn = mysql.createConnection(connInfo)
    conn.query(sql, (err, result)=>{
        if(err){
            console.log(err);
        }else {
            callback(result)
        }
        conn.end(err => {
            if(err){
                console.log(err)
            }
        })
    })
}

module.exports = queryMysql;

Error2:
We only need to configure useConnectionPooling : true when instantiating SessionStore . for example:

var sessionStore = new SessionStore({
    host: 'localhost' ,
    port: 3306 ,
    user: 'root' ,
    password: 'root' ,
    database: 'session' ,
    useConnectionPooling: true 
});

[Solved] WordPress Upgrade PHP 5.6 to 7.x Fatal error: Uncaught Error: Call to undefined function mysql_connect()

Error reporting when upgrading PHP version 5.6 to 7. X under WordPress (resolved)

Cause of event:

When using WordPress, the theme needs to be upgraded. The PHP version required for viewing the theme is 7.1+

When the website is not backed up, the PHP version is changed to 7.2, which directly leads to the website error

Fatal error: Uncaught Error: Call to undefined function mysql_ connect()

Check many online tutorials, which are MySQL in wp-db.php_Change connect() to mysqli_Connect(), no result

At first, I thought that the database reported an error, checked the database in detail, wrote checkdb.php, locally connected to the database, and checked that the database server was running well

Solution:

Set define ('wp_use_ext_mysql ', true) in wp-config.php delete

error explanation:

define('WP_USE_EXT_MYSQL',true);

define(‘WP_USE_EXT_MYSQL’, true); Force WP to use MySQL by default instead of MySQL Li
so please delete this line and the problem should be solved.

Otherwise, you can check nd in the PHP 7 configuration_Mysqli extension, and disable mysqli extension – > Select the PHP version.