Tag Archives: database

mac brew install mysql ‘/tmp/mysql.sock’ (2) [How to Solve]

the reason for the error is very simple: MySQL should be used instead of MySQL when starting the command server

mac brew install mysql

Install MySQL with homebrew on Mac to perfectly solve error 2002 (HY000):

Can’t connect to local MySQL server through socket ‘/tmp/mysql.Sock ‘(2) error

1. Start the command mysql server

the startup method of using MySQL command directly is wrong

This problem occurs when you directly call the MySQL command. It is said on the Internet that it is because of MySQL Caused by CNF configuration file.

the correct startup method is mysql server

➜  support-files mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
➜  support-files pwd
/opt/homebrew/opt/mysql/support-files
➜  support-files ls
mysql-log-rotate    mysql.server        mysqld_multi.server

2. my.CNF file location

my.CNF file location 

➜  homebrew cd etc
➜  etc ls
bash_completion.d   ca-certificates     dump.rdb            my.cnf              [email protected]         
redis-sentinel.conf redis.conf
➜  etc pwd
/opt/homebrew/etc

3. MySQL installation location

➜  homebrew cd opt
➜  opt ls
ca-certificates jasper          libevent        libpng          lz4             [email protected]     pzstd           six
devil           jpeg            libjpeg         libtiff         mysql           protobuf        redis           zlib
icu4c           lcms2           libjpg          little-cms2     [email protected]       [email protected]   [email protected]       zstd
➜  opt cd mysql
➜  mysql ls
INSTALL_RECEIPT.json      LICENSE.router            README.router             homebrew.mxcl.mysql.plist lib                       support-files
LICENSE                   README                    bin                       homebrew.mysql.service    mysqlrouter-log-rotate
LICENSE-test              README-test               docs                      include                   share
➜  mysql pwd
/opt/homebrew/opt/mysql
➜  mysql

[Solved] MySQL Error: Can‘t find record in ‘order_form‘

Remember a mysql query problem, associated query the order table, Mysql ERROR 1032 (HY000): Can’t find record in order_form appeared, after checking the data, no problem was found.

Execute: REPAIR TABLE order_form USE_FRM The problem is solved after the repair.

PS: Before repairing, please remember to back up the table to be repaired to prevent data loss, remember! !

[Solved] Gunicorn timeout error: worker timeout

Gunicorn timeout error: worker timeout

I. Problem Description:

One morning, the developer suddenly reported a failure and the container restarted inexplicably. After checking the business container log, the worker timeout field was found

II. Analysis of error reporting reasons:

It can be seen from the error message that the worker process of gunicorn timed out, causing the process to exit and restart. Check the official website. The official website explains that the default timeout of gunicorn is 30s. If it exceeds 30s, the worker process will be killed and restarted.

III Solution:

Add: -- timeout 600 to gunicorn’s startup command to set the timeout to 600 seconds– Graceful timeout 600 indicates that the graceful timeout is 600 seconds

After the setting is completed, it is verified through kustomize inspection and re-distribution. It is found that the problem does not occur in the follow-up

Oracle Start as DBA Error [How to Solve]

Problem Description:

In the morning, I created Oracle11g on the new machine and reported an error when starting with the startup command:

This error message means:
ora-00119: the initialization parameters of the system are invalid

Ora-00132: unresolved network listener name

My understanding: there is a problem with the listening configuration in the initialization file. It cannot be initialized and listening cannot be started.

Troubleshooting direction:

View the initialization file. Everyone has different installation paths.

The initialization file must exist in the Oracle installation directory orcl. My path:

Open the initialization file in Notepad and find the local listening configuration: local_Listener: the original local listening configuration parameter is listener_ORCL

Now, change it to:

local_listener=(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)

verification:

Load initialization file synchronously at startup:

The database started successfully.

[Solved] MySQL Execute update error: error code: 1175

When using Mysql to execute update, if the primary key is not used in the where condition, the following error will be reported and the update cannot be executed.

Exception content: error code: 1175 You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

Solution:
this is because MySQL runs in the safe updates mode. Modify the database security mode to allow the update or delete commands to be executed under non primary key conditions.

Execute command:

SET SQL_SAFE_UPDATES = 0;

[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

[Solved] mongo Startup Error: ERROR: child process failed, exited with error number 1

Because Mongo is abnormally closed, an error message of “error: child process failed, exited with error number 1” may appear when it is started again.

Step 1: delete the lock file

Find Mongo directory and delete mogod lock

Step 2: create a new log file

Find the log file directory of Mongo and delete or modify the original log file name
[because I don’t want to modify Mongo’s configuration file, I modified the file name and added a file with the same name as the original file. I can also take another name and modify Mongo configuration]

Step 3 try to restart

Generally, the restart is successful at this step.

If it still fails, take a look at Mongo’s log file
the errors reported in my log file are as follows:

2021-12-16T11:58:12.810+0800 I CONTROL  [main] ***** SERVER RESTARTED *****
2021-12-16T11:58:12.815+0800 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
2021-12-16T11:58:12.820+0800 I CONTROL  [main] ERROR: Cannot write pid file to /var/run/mongodb/mongod2.pid: No such file or directory

Then go to the prompt “/var/run” directory, create a new mongodb folder, and restart. Start successfully and solve the problem!

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]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!