Category Archives: MySQL

[Solved] mariadb Startup Error: Job for mariadb.service failed because the control process exited with error code.

The arm architecture CentOS MariaDB starts with an error job for MariaDB service failed because the control process exited with error code.

As a branch of MySQL, MariaDB is installed differently from mysql, but the specific startup principles are similar

[root@ecs-6ab1 bin]# systemctl start mysql
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

MySQL installed on Kunpeng server (ARM Architecture) suddenly hangs up, thinking it is a problem with the configuration file. Therefore, modify the my.ini file to restart, but this problem will occur when executing systemctl start MySQL command.

    1. this error message can’t be seen in real time. The specific error is just a prompt of startup failure. However, when the installation is OK, the startup failure is generally a problem with the configuration file. Since systemctl start MySQL is a global startup method, it is displayed in/var/log/MariaDB/MariaDB There is no specific error message in the log (mariadb.log has a default configuration in my.ini under etc or in my.cnf.d folder. My configuration is in/etc/my.cnf.d/mariadb-server.cnf, which is true on my server, or there may be something wrong with my global boot configuration, and I didn’t go deep into it)
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

If you can’t see the log, you can start the MySQL startup program to find the location where the program starts

[root@ecs-6ab1 bin]# find /usr /home -name mysqld_safe
/usr/bin/mysqld_safe

After finding the location, you can use mysqld_ Start safe

./mysqld_safe  --user=root --basedir=/var/lib/mysql  --datadir=/var/lib/mysql &

I should also introduce the problem here. After the restart, the loss of the sock leads to a startup failure. After the startup, check the log every time and solve the problem step by step
until there is no error in the log. Use the command line to connect

[root@ecs-6ab1 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Success!

[Solved] MYSQL Command Execute Error: Can ‘t connect to local MySQL server through socket ‘/tmp/mysql.sock ‘(2) “

Description

Today, when adding a MySQL slave node to the online production environment, the following error was reported when executing the command to back up MySQL data:

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

The general meaning of error reporting is that MySQL under TMP cannot be found Sock file

Solution:

1. First find mysql Directory where the sock is located

find/-name mysql.sock

2. Create/TMP/MySQL Sock soft connect to the file
establish a soft connection (write an absolute path):

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

If the manual residual soft connection is wrong, you can delete it with the following command:

rm -rf /tmp/mysql.sock

Re-execute MySQL command, perfect solution!!

[Solved] Starting MySQL… ERROR The server quit without updating PID file

An error is reported when starting MySQL after the Linux server is restarted. The error information is as follows:

Starting MySQL… ERROR! The server quit without updating PID file (/usr/local/mysql/data/VM_0_9_centos.pid).

Solution:

1. Modify the MySQL configuration file in/etc/my cnf。 Add the following configuration:

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log-error = /usr/local/mysql/error.log
pid-file = /usr/local/mysql/mysql.pid
user = mysql
tmpdir = /tmp

2. Initialize and execute some commands in/usr/local/MySQL/scripts:

./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysql.pid --tmpdir=/tmp

This database can be started normally.

However, due to the reinitialization of the database, you will be prompted that you do not have permission when accessing the previous database.

Two commands are required:

chown -R mysql:mysql /usr/local/mysql/
chmod -R 755 /usr/local/mysql/

Tip: the directories here are all my real MySQL installation directories. The directories installed by each person may be different.

ERROR 1261 (01000): Row 1 doesn‘t contain data for all columns

1. Error Messages:

MariaDB [baidd]> select * from t1  into outfile '/var/lib/mysql/t1.txt';
Query OK, 1 row affected (0.000 sec
MariaDB [baidd]>  load data infile '/var/lib/mysql/t1.txt' into table t2;
ERROR 1261 (01000): Row 1 doesn't contain data for all columns
2. Reason
t2 has more fields than t1, so the import fails.

[Solved] Sqlyog always reports an error when creating a new connection.

Case 1:

In versions before MySQL 8, the encryption rule is mysql_native_Password, and after MySQL 8, the encryption rule is caching_sha2_password。 There are two ways to solve the problem. The first is to upgrade the graphical interface tool version, and the second is to restore the MySQL user login password encryption rules to MySQL_native_password.
the second solution is as follows. After logging into MySQL database with the command line, execute the following command to modify the user password encryption rules and update the user password. Here, modify the user name as“ root@localhost ”The user password rule of is “mysql_native_password”, and the password value is “OK”, as shown in the figure.

//Using mysql database
USE mysql; 

//change the password rule and password for the 'root'@'localhost' user
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ok'; 

//refresh permissions
FLUSH PRIVILEGES;

Situation II

The virtual machine MySQL is connected, and you need to perform the authorization operation
authorize the root user:
grant all on. To ‘root’ @ ‘%’ identified by ‘use password’
flush privileges;

How to Solve c3p0 connect mysql8.0 Error

Environmental Science:

JDK1.7
mysql-connector-java 8.0.16

C3p0 configuration file

Error message:

Incompatibility between the JDK version and the jdbc driver
java.lang.unsupported classversionerror 

Exception in thread "com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2" 
java.lang.UnsupportedClassVersionError: com/mysql/cj/jdbc/Driver :
Unsupported major.minor version 52.0

Solve the problem: change the JDK to 1.8, restart, or report an error. Replace the configuration information of the URL successfully. Details are as follows:

Connect to the database using a profile

Create c3p0-config.xml in the SRC folder. The name and address cannot be changed

Profile code, note & amp; To escape to & amp; amp

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/webdemo?useSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config>
</c3p0-config>

Test code

package cn.wahll.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class c3p0Demo {
    @Test
    public void c3p0PoolTest() throws Exception {
        //Find the default configuration directly under the configuration file
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection conn = dataSource.getConnection();
        String sql = "INSERT INTO category VALUES('bsafvb','asdgg')";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.executeUpdate();
    }
}

[Solved] MySQL Startup Error: The server quit without updating PID file

Specific reports are as follows:
Starting MySQL
. Error! The server quit without updating PID file (/opt/homebrew/was/mysql/QdeMacBook-Pro.local.pid).

Resolution programme:

linux:
sudo chmod -R 777 /usr/local/var/mysql/

Start:
systemctl restart mysqld

Mac: homebrew installed.

chmod -R 777 /opt/homebrew/was/mysql

Start:
sudo mysql.server restart

Error! MySQL server PID file could not be found!
Starting MySQL
SUCCESS!

MySQL 8.0.12 runs SQL error #1055 [How to Solve]

1. Error:

An error is reported when opening the database through phpstudy;

2. This error is due to the MySQL version problem

3. Solution

(1) Close the error box and view the SQL used by the current database_ mode：

Enter select in the SQL editor  @@ sql_Mode operation

(2) Modify the MySQL configuration file. The configuration file under windows is my.ini. Find out SQL_Mode, copy this value, and add a configuration item in my.in (delete the option only_full_group_by for the queried value, and copy everything else)

In my.in file, Modify SQL after [mysqld]_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_ DATE,ERROR_ FOR_ DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

If the row is not added directly

(3) Restart Mysql to take effect