Tag Archives: mysql

[Solved] init datasource error, url: jdbc:mysql://localhost:3306/test

Problem description

The following error message is suddenly thrown when starting the project today:

[16:14:32.735][ERROR][com.alibaba.druid.pool.DruidDataSource][main] init datasource error, url: jdbc:mysql://localhost:3306/test
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server

reason

Finally, it is concluded that MySQL needs to specify whether to connect with SSL in the higher version

Solution:

Set usessl = false, and change the connection URL as follows

jdbc:mysql://localhost:3306/test?useSSL=false

mysqldump: Error: Binlogging on server not active [How to Solve]

Background:

Execute the following statement to report an error:

mysqldump.exe -uroot -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb > aa.sql

Tried Methods:

1. Modify the configuration file and add the following configuration

#Enable binary logging
log-bin=mysql-bin

Then restart the service

It doesn’t work

2. Delete — master data = 2 parameter

Export succeeded

[Solved] xtrabackup Error: “Too many open files” (system error number 24)

The following error occurs during a MySQL database backup:

InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means ‘Too many open files’ 
InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html

It is found that there are too many data files:

a total of 90811 data files.

View number of open files:

[root@localhost ~]# ulimit -Sn
1024
[root@localhost ~]# ulimit -Hn
4096

Description of this check command:

ulimit [-HSTabcdefilmnpqrstuvx [limit]]
              Provides  control over the resources available to the shell and to processes started by it, on systems that allow such control.  The -H and -S options specify that the hard or soft
              limit is set for the given resource.  A hard limit cannot be increased by a non-root user once it is set; a soft limit may be increased up to the value of the hard limit. 
  -n     The maximum number of open file descriptors (most systems do not allow this value to be set)

Query the maximum number of files opened by the operating system:

# cat /proc/sys/fs/nr_open
1048576

In the file/etc/security/limits Conf settings:

mysql hard nofile 65535
mysql soft nofile 65535

mysql hard nproc 65535
mysql soft nproc 65535

You can also replace MySQL with *
the problem of re backup is solved.

[Solved] Fatal error C1001 internal error occurred in the compiler openmesh6 three

Internal Compiler Error VS 2015 Update1
VS2015 Update1 An error occurred when compiling the OpenMesh code:

fatal error c1001 An internal error occurred in the compiler OpenMesh6.3
(compiler file ‘f:\dd\vctools\compiler\cxxfe\sl\p1\c\special.c’, line 6211)
1> To work around this problem, try simplifying or changing the program near the locations listed above.

The reason is that codes such as this appear

OpenMesh::Vec3f normal[4];

Solution: change this kind of code to the following.

OpenMesh::Vec3f normal[4] = { {},{},{},{} };

[Solved]ERROR 1067 (42000): Invalid default value for ‘end_time‘ Mysql

1. Error message

When executing the following SQL statement, an error message appears: error 1067 (42000): invalid default value for ‘end’_ time’

CREATE TABLE seckill1(
    `seckill_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Commodity inventory id',
     `name` varchar(120) NOT NULL COMMENT'product name',
     `number` int NOT NULL COMMENT'Stock quantity',
     `start_time` timestamp NOT NULL COMMENT'second kill start time',
     `end_time` timestamp NOT NULL COMMENT'second kill end time',
    `create_time` timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    PRIMARY KEY(seckill_id),
    key idx_start_time(start_time),
    key idx_end_time(end_time),
    key idx_create_time(create_time)
)ENGINE = InnoDB AUTO_INCREMENT = 1000 DEFAULT CHARSET = utf8 COMMENT = 'Spike inventory table';

The error message is as follows:

2. Solution

(1) View SQL_mode :

show session variables like '%sql_mode%

(2) Modify sql_mode(remove NO_ZERO_IN_DATE,NO_ZERO_DATE) :

 set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

(3) Re-execute the SQL statement in 1:
the table creation success is displayed.
the screenshot of the three steps is as follows:

3. Reasons for error reporting

(1) Error explanation: the first timestamp column in the
table (that is, the start_time row in the SQL statement of 1) (if it is not declared null or the default or on update clause is displayed) will automatically assign the default current_timestamp and on update current_timestamp attributes.
the timestamp column after the first one (that is, the end_time line in the SQL statement of 1) if it is not declared null or the default clause is displayed), the default ‘0000-00-00 00:00’ (zero timestamp) will be automatically allocated, which does not meet the no_zero_date in sql_mode and an error will be reported.
(2) solution (three) Note: the solution in 2 is only for the created table
method 1: execute select @ @ sql_mode, copy the queried value, delete the no_zero_date, and then execute set sql_mode = 'modified value'. This method only takes effect in the current session
method 2: execute select @ @ global.sq first l_Mode , copy the queried value and put no in it_ZERO_Delete date and execute set global SQL_Mode = 'modified value', this method takes effect in the current service, and becomes invalid after re MySQL service
method 3: open my.In the MySQL installation directory Ini or my CNF file, add the following line,

 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，　　　

Then restart mysql. This method is permanent

[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.