Category Archives: MySQL

MYSQL8 Startup Error: mysqld_safe error: log-error set to ‘/var/log/mariadb/mariadb.log‘

Starting MySQL.2021-11-17T11:04:25.022108Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/data/mysql/data/localhost.localdomain.pid).

After msyql8 compilation and installation, start MySQL with/etc/init.d/mysqld start command. The above errors are reported, and then I rechecked the content of my.cnf, as follows:

[mysqld]
server-id=1
port=3306
datadir=/data/mysql/data
basedir=/usr/local/mysql
socket=/usr/local/mysql/mysql.sock
skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[client]
socket=/usr/local/mysql/mysql.sock


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

There is no problem with the overall configuration of my.cnf, and then the startup fails all the time. Finally, it is found that it is the log directory configured by mysqld_safe was not created

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

So create authorization directly

Note: the path of log error I configured here is under/var/log/MariaDB. The created path is configured according to its own actual path. Be sure not to forget to create mariadb.log, otherwise an error will be reported

mkdir /var/log/mariadb     
touch /var/log/mariadb/mariadb.log

MySQL users and users authorize directories

chown -R mysql:mysql /var/log/mariadb/

Start the MySQL service again

/usr/local/mysql/support-files/mysql.server start

perhaps

/etc/init.d/mysqld start

Other startup methods are implemented according to their actual situation

[Solved] Python import Error: pip –upgrade Error: ERROR: Cannot uninstall ‘dnspython‘. It is a distutils installed

Background:
today we use package to install the module dnspython

[[email protected] ~] wget http://www.dnspython.org/kits/1.9.4/dnspython-1.9.4.tar.gz
[[email protected] ~] tar -xvf dnspython-1.9.4.tar.gz
[[email protected] ~] cd dnspython-1.9.4/
[[email protected] dnspython-1.9.4] python3 setup.py install

Solution process
1. Enter Python and import reports an error (problem found)

[[email protected] ~] python3
Python 3.8.5 (default, Nov  7 2021, 21:47:38) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import dns.resolver
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/python3/lib/python3.8/site-packages/dns/resolver.py", line 26, in <module>
    import dns.message
  File "/usr/local/python3/lib/python3.8/site-packages/dns/message.py", line 175
    return '<DNS message, ID ' + `self.id` + '>'
                                 ^
SyntaxError: invalid syntax

2. Try upgrading (– upgrade) and find that it still can’t be installed

[[email protected] ~] pip3 install dnspython --upgrade
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Requirement already satisfied: dnspython in /usr/local/python3/lib/python3.8/site-packages (1.9.4)
Collecting dnspython
  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/f5/2d/ae9e172b4e5e72fa4b3cfc2517f38b602cc9ba31355f9669c502b4e9c458/dnspython-2.1.0-py3-none-any.whl (241 kB)
     |████████████████████████████████| 241 kB 898 kB/s            
Installing collected packages: dnspython
  Attempting uninstall: dnspython
    Found existing installation: dnspython 1.9.4
ERROR: Cannot uninstall 'dnspython'. It is a distutils installed project and thus we cannot accurately determine which files belong to it which would lead to only a partial uninstall.

3. A new error occurred while attempting to uninstall

[[email protected] ~] pip3 uninstall dnspython
Found existing installation: dnspython 1.9.4
ERROR: Cannot uninstall 'dnspython'. It is a distutils installed project and thus we cannot accurately determine which files belong to it which would lead to only a partial uninstall.

4. If the command cannot be unloaded, delete the file manually

[[email protected] ~] cd /usr/local/python3  #Go to the directory where python is installed
[[email protected] python3] find ./-name "*package*" #Fuzzy search package
./lib/python3.8/site-packages
./lib/python3.8/site-packages/setuptools/package_index.py
./lib/python3.8/site-packages/setuptools/__pycache__/package_index.cpython-38.pyc
./lib/python3.8/site-packages/setuptools/__pycache__/package_index.cpython-38.opt-1.pyc
......
[[email protected] python3] cd lib/python3.8/site-packages/ #Find site-packages from the above results and go to this directory
[[email protected] site-packages] ll  
total 96
drwxr-xr-x. 4 root root  4096 Nov 16 17:18 dns
-rw-r--r--. 1 root root  1277 Nov  9 22:31 dnspython-1.9.4-py3.8.egg-info
....
[[email protected] site-packages] rm -rf dnspython-1.9.4-py3.8.egg-info   #Delete all files containing the module name

5. Reinstall and import successfully after installation

[[email protected] site-packages] pip3 install dnspython #reinstall
[[email protected] site-packages] python3
Python 3.8.5 (default, Nov  7 2021, 21:47:38) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import dns.resolver
>>> exit

[Linux Docker Mirror] MYSQL Run sql Script Error: Failed to open file ‘/home/mydatabase.sql‘, error: 2

Failed to open file ‘/ home/mydatabase. SQL’, error: 2 when running SQL script in docker image MySQL in Linux

Today, an error occurred when using docker in centos7 to start MySQL and run SQL script files. The error information is as follows:

mysql> source /home/mydatabase.sql;
ERROR: 
Failed to open file '/home/mydatabase.sql', error: 2

After checking the Internet, the cause of the error should be the problem of path matching. The default path is the installation path of MySQL, so MySQL can only access all directories and files under its source directory. The solution is to directly specify the SQL script file to run when logging in. The specific steps are as follows:
first log in to MySQL and create the database to be used. The name of the database I created is Mydatabase1

[email protected]: mysql -uroot -p 
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database mydatabase1;

Exit MySQL and log in again by running SQL script files

# Here mydatabase1 is the database created in the first step, and the file after'<' is the path of the sql script file to be run
[email protected]: mysql -uroot -p mydatabase1 < /home/mydatabase.sql

log in again and find that the file has run successfully

[email protected]: mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| mydatabase1        |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mydatabase1;
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> show tables;
+-----------------------------+
|    Tables_in_mydatabase1    |
+-----------------------------+
| t_disk                      |
| t_crl                       |
| t_menu                      |
| t_user                      |
+-----------------------------+
4 rows in set (0.00 sec)

Supplement:

# Docker start MySQL command under CentOS7
docker pull mysql:5.7
docker run -tid --name mysql -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:5.7
docker exec -it mysql /bin/bash
mysql -uroot -p
Enter password:

C# Connect MYSQL Error: MySql.Data.MySqlClient.MySqlException:“SSL Connection error.”

To solve this exception, you need to ensure normal operation

MySqlConnection conn;// Represents an open connection to a MySQL database. This class cannot be inherited
MySqlCommand comm;// Represents the SQL statement to execute on the MySQL database. This class cannot be inherited
MySqlDataAdapter adapter;// Represents a set of data commands and database connections for populating datasets and updating MySQL databases. This class cannot be inherited.

The exceptions of error reporting are as follows

MySql.Data.MySqlClient.MySqlException:“SSL Connection error.”

IOException: unable to read data from the transport connection: an established connection was aborted by the software in your host

Connection string (exception)

string mysqlconn = "server=127.0.0.1;port=3306;user=root;password=root;database=wpfstudy;";

Connection string (normal)

string mysqlconn = "server=127.0.0.1;port=3306;user=root;password=root;database=wpfstudy;SslMode = none;";

Add the following statement to the connection string

SslMode = none;

When using. Net/C # to operate MySQL database, the default value of sslmode is preferred. If the server supports it, please use SSL

SSL (secure socket layer) uses data encryption, authentication and message integrity verification mechanisms to provide security assurance for application layer protocols based on reliable connections such as TCP. If the user’s transmission is not through SSL, the data in the network is transmitted in plaintext, which brings an opportunity for people with ulterior motives. Therefore, many large websites now have SSL enabled. Similarly, in our database, if the client connects to the server to obtain data instead of using SSL connection, the data may be stolen during transmission.

The functions provided by SSL protocol mainly include:

1. Confidentiality of data transmission: use symmetric key algorithm to encrypt the transmitted data
2. Authentication mechanism: use digital signature method to authenticate the server and client based on certificate, and the authentication of the client is optional
3. Message integrity verification: MAC algorithm is used to verify message integrity during message transmission.

[Solved] java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corres

0. Foreword: this error is an error reported by using mybatis in the spring boot project to query a piece of data in the database according to the ID value

1. Error message

2021-11-14 15:37:38.168 ERROR 9936 --- [nio-8086-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
### The error may exist in file [E:\smallTools\idea\code\spring\springboot-new-2021_11_09\springboot-new-mybatis\target\classes\mybatis\mapper\BookMapper.xml]
### The error may involve com.feng.mybatis.mapper.BookMapper.getBookById-Inline
### The error occurred while setting parameters
### SQL: select * from book where id={#id}
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1] with root cause

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.27.jar:8.0.27]

2. Mapper.xml file content

<select id="getBookById" parameterType="com.feng.mybatis.bean.Book">
        select * from book where id={#id}
</select>

3. Error reason: the SQL statement is written incorrectly. It should be where id = #{id}

<select id="getBookById" parameterType="com.feng.mybatis.bean.Book">
        select * from book where id=#{id}
</select>

Error running ‘Application‘: Command line is too long [How to Solve]

2021.11.18

When idea starts a new project, a pop-up prompt error is encountered

Error running ‘xxx Application’:Command line is too lang. Shorten command line for Application or aalso for Spring Boot default configuration

Error reason: the springboot project startup command is too long

Solution:

The first method (protest is effective)

Step 1: find the idea/workspace.xml file in the project, and then find <component name="PropertiesComponent"> </component > tag, as shown below

step2:Add a line to the component tag < property name="dynamic.classpath" value="true"/>, as shown below

Restart the project.

[Solved] workbench ssl connection error ssl is required but the server

Background

Workbench connection error SSL connection error SSL is required but the server, as shown in the figure below

Solution:

stay https://stackoverflow.com/questions/69824631/mysql-workbench-ssl-connection-error-ssl-is-required-but-the-server-doesnt-sup/69828778 To find a solution, select if availablefor use SSL. However, it is found that the latest version of MySQL workbench 8.0.27 does not have this option. So I downloaded the old version MySQL workbench 8.0.16 , modified the SSL option, and the test results are shown in the figure below
in consideration of downloading workbench to log in, I have prepared the download address for your convenience.

https://sourceforge.net/projects/generic-software/files/workbench/

[Solved] ERROR com.alibaba.druid.pool.DruidDataSource – init datasource error, url jdbcmysql

Error reporting when deploying an open source project

ERROR com.alibaba.druid.pool.DruidDataSource - init datasource error, url jdbcmysql://

Obviously, there is a database connection error

But after eliminating the error, I can find the real cause of the error

Database connection address error

Network problems

Wrong user name and password

Finally, it was found that there was a problem with the MySQL driver version of the open source project, which was inconsistent with the MySQL I installed

Solution:

Go to POM.XML and delete the version information of MySQL, which can be used by default, as shown below

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

[Solved] Whitelabel Error Page This application has no explicit mapping for /error, so you are seeing this as

Whitelabel Error Page

This application has no explicit mapping for /error, so you are seeing this as a fallback.Mon Nov 15 20:44:52 CST 2021
There was an unexpected error (type=lnternal Server Error, status=500).
Could not open JPA Entity/Manager for transacton, nested exception is org.hibernate.exceptionGenericDBCException:
Unable to acquireJDBCConnection

During the debugging of idea, I encountered a problem on the browser side. I found it for many days and finally solved it. That is, the JDBC version of the connection between idea and MySQL does not match. Let’s share the method below
if you see an error, it is probably a JDBC connection problem. First, check the configured MySQL user name, password and URL

After making sure that everything is correct, check whether my MySQL version corresponds to the idea version. Before I made an error, the MySQL jar package in the idea is 5.0, but my idea version is higher than mysql, so I can’t connect. Then I go to the MySQL 8.0 jar package under the official website, import it into the idea, and then change the JDBC version in pom.xml


  • In the higher version of JDBC, spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver is generally added with a cj.

This is the previous
this is the current

later, there will be no JDBC connection problem when running on the server side! This is my problem solved, others may have more situations. Here only represents my own solution!

[Solved] mysqldump Command Error: Got error: 2002: “Can‘t connect to server on ‘127.0.0.1‘ (36)“ when trying to connect

1. When executing mysqldump command for data backup, the error information is as follows:

~ % mysqldump -udbuser -p123456 -h127.0.0.1 school student  > school_student.sql 
mysqldump: Got error: 2002: "Can't connect to server on '127.0.0.1' (36)" when trying to connect

2. Then try the command line to connect to the database, which is also an error

~ % mysql -udbuser -p123456 -h127.0.0.1
ERROR 2002 (HY000): Can't connect to server on '127.0.0.1' (36)

3. After checking the MySQL service result, I remembered that I used ‘mysql. Server start’ to start the database. This cannot follow the system startup. Each time I restart the Mac, I need to run it manually. You can use brew services to start MariaDB   Start the service with the system.

~ % mysql.server start
Starting MariaDB
.211111 16:04:44 mysqld_safe Logging to '/usr/local/var/mysql/xiaokang.err'.
211111 16:04:44 mysqld_safe Starting mariadbd daemon with databases from /usr/local/var/mysql
 SUCCESS! 

4. After successful restart, execute mysqldump again for backup