Tag Archives: mysql error

RECEIVED ERROR PACKET: ERRNO = 1236, SQLSTATE = HY000 ERRMSG = COULD NOT FIND FIRST LOG FILE NAME IN BINARY LOG INDEX FILE

When using canal to synchronize mysql data, the problem of Could not find first log file name in binary log index file cannot find the binlog file. The detailed error is as follows

java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102) ~[canal.parse-1.1.6.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:237) [canal.parse-1.1.6.jar:na]
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262) [canal.parse-1.1.6.jar:na]
at java.lang.Thread.run(Thread.java:750) [na:1.8.0_333]
java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102) ~[canal.parse-1.1.6.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:237) ~[canal.parse-1.1.6.jar:na]
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262) ~[canal.parse-1.1.6.jar:na]
at java.lang.Thread.run(Thread.java:750) [na:1.8.0_333]
ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:example[java.io.IOException: Received error packet: err
no = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:102)
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:237)
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$1.run(AbstractEventParser.java:262)
at java.lang.Thread.run(Thread.java:750)
]

 

This problem is mainly caused by the logfile being overwritten. You can view the meta.dat file under canal/conf/example to check the location of the currently read binlog file
"postion":{"gtid":"","included":false,"journalName":"mys
ql-bin.000591","position":25928,"serverId":21381487,"timestamp":1663232397000}}}],"destination":"example"}[[email protected]fjagtpbh97ar0zj6cZ example]

 

Then compare the current MySQL binlog file and position
Use show master status to view in MySQL
Solution:
1. Stop the canal server first
2. Delete canal/conf/example/meta.dat
3. Update the canal/conf/example/instance.properties file

canal.instance.master.journal.name=xxx
canal.instance.master.position=xxx
These two fields correspond to the file and position in the current database respectively
4. Restart the canal server
Then you can view canal/logs/example/example.log to check if there is still a problem of not finding the binlog file

[Solved] MYSQL Error: ERROR! MySQL server PID file could not be found!

After restarting the MySQL database on the Linux server, various errors were found, causing the MySQL database to fail to work normally.

No matter stop, start or mysql connection, there are different error messages. The specific error messages are:

# service mysqld stop
ERROR! MySQL server PID file could not be found!

# service mysqld start
Starting MySQL.. ERROR! The server quit without updating PID file (/data/mysql/mysql.pid).

# mysql -uroot -p
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

 

1. Error reason:

In fact, the above three different MySQL errors are all caused by one reason: the root directory disk is full.

2. Check the large files that cause the disk to be full:

1. First, use the df command to check the disk usage:
# df
You may see in the output:
/dev/mapper/VolGroup-lv_root 39841176 37788792 28552 100% /

2. Use # du – sh * to view large files. For example, the root directory is data. Use the command to view:
# cd/data
# du – sh *
You will find that the mysql folder occupies more than n gigabytes, and then view which files:
# cd mysql
# du – sh *
You will find many large files, such as
581M mysql-bin.000029
28K mysql-bin.000030
7.6G mysql-bin.000031
4.0K mysql-bin.000032

These files are MySQL Binary Log binary files, which are mainly used for data recovery and master-slave replication of master-slave servers.

Since I have no master and slave servers, I decided to delete them.

3. Solution:

1. Delete these large files:
# rm -rf mysql-bin.000031
~~~

2. After deleting them, open my.cnf under /etc/, find
log-bin=mysql-bin
binlog_format=mixed
Comment out these two lines, that is, add the “#” sign in front:
#log-bin=mysql-bin
#binlog_format=mixed

3. Restart the MySQL server, and it is OK
service mysqld restart

[Solved] MySQL Error: “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”

Project scene:

Recently, after deploying the project, an error occurred when running:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘grades.order_id’ 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

Problem Description

Using the GROUP BY statement violates sql_mode=only_full_group_by. Because the default mode after mysql version 5.7 is ONLY_FULL_GROUP_BY.


Cause Analysis:

Check the official documentation and find that starting from MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL did not detect functional dependencies, and ONLY_FULL_GROUP_BY was not enabled by default.) This may cause some sql statements to fail.


solution:

Execute the command vim /etc/mysql/conf.d/mysql.cnfto modify the configuration file

If there is sql_mode configuration in my.cnf, remove ONLY_FULL_GROUP_BY.

If not, put the following content in the corresponding place

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
BASHcopyfull screen

After saving, execute the command to service mysql restartrestart mysql.

[Solved] EOS7.6 Error: Init DB failed [Specified key was too long; max key length is 767 bytes…

Init DB failed![Specified key was too long; max key length is 767 bytes[CREATE INDEX IDX_WFWI_PARTICI ON WFWORKITEM( PARTICIPANT )]
Specified key was too long; max key length is 767 bytes[CREATE INDEX WTRI_EXPAT ON WFTASKRESOURCEINFO(EXCLUDEUNIQUEID)]
Specified key was too long; max key length is 767 bytes[CREATE INDEX WTRI_COMP ON WFTASKRESOURCEINFO(QUEUENAME,STATUS,SERVERID)]
]


– select 'yes' for retry and' no 'for exit

 

If the system variable innodb_large_prefix is enabled (enabled by default, it is off by default on my computer installation of MySQL 5.6.41, and on by default on MySQL 5.7), the index key prefix is limited to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix is limited to 767 bytes for any table in row format.

innodb_large_prefix will be removed and deprecated in future releases. The innodb_large_prefix was introduced in MySQL 5.5 to disable large prefix indexes for compatibility with earlier versions of InnoDB that did not support large index key prefixes.

For InnoDB tables using REDUNDANT or COMPACT row formats, the index key prefix length is limited to 767 bytes. For example, you might reach this limit using a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and a maximum of 3 bytes per character.

Attempts to use an index key prefix length that exceeds the limit will return an error. To avoid such errors in replication configurations, avoid enabling enableinnodb_large_prefix on the master server (if it cannot be enabled on the slave server).

The restrictions that apply to index key prefixes also apply to full column index keys.

Note: The above is 767 bytes, not characters, specifically the number of characters, which is related to the character set. gbk is double-byte, utf-8 is triple-byte

Solutions.

1: Enable the system variable innodb_large_prefix
Note: It is not enough to have this system variable enabled. The following conditions must be met.

2: System variable innodb_large_prefix is ON

3: System variable innodb_file_format is Barracuda

4: ROW_FORMAT is DYNAMIC or COMPRESSED

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%innodb_file_format%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
+--------------------------+----------+
3 rows in set (0.00 sec)

mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_format_max=BARRACUDA;
Query OK, 0 rows affected (0.01 sec)

After completing the above operations, EOS platform 7.6 is successfully installed.

[Solved] MySQL Error: ERROR 1055 (42000)sql_mode=only_full_group_by

environment

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+

Execute query statement

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  4 | 张飞   |        2 |   21 |      1 |
|  5 | 关羽   |        1 |   22 |      2 |
|  6 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+
6 rows in set (0.00 sec)

-- Requirement: After sorting by age, take out the oldest students in each class
select * from (
    select * from my_student order by age desc
) as t group by t.class_id;

report errors

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 't.id' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

reason:

MySQL 5.7.5 and above function dependency detection function

Solution:

-- View current configuration items
select @@global.sql_mode

-- remove ONLY_FULL_GROUP_BY
set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

After setting, if it does not take effect, you can exit and log in again

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

How to Solve MYSQL Error: Failed to start MySQL 8.0 database server

the reason

It appears in the mysql error log: The innodb_system data file’ibdata1′ must be writable, literally: ibdata1 must be writable

Check the log and report an error, the folder has no write permission

cat /var/log/mysqld.log

solve

Find file path

find / -name ibdata1

Grant folder permissions

chmod -R 777 /var/lib/mysql
chown mysql:mysql -R /var/lib/mysql

Start MySQL

systemctl start mysqld.service
// CHECK MySQL STATUS
systemctl status mysqld.service

Caused by: com.ibatis.common.xml.NodeletException: Error parsing XML. Cause: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 281; The content of element type “sqlMapConfig” is incomplete

It can be clearly seen from the error message that this is a problem on sql

Well, in general, there are many reasons for this problem:

1. Maybe the code call method in your service is wrong (this is the lowest level error, and it is not easy to find…), directly above

Originally it was written as MMIC01.delete, which was my careless (no flash, haha)

 

2. It is also possible that one of your SQL statements is wrong (this is easier to find)

 

Compared with the previous question, this error is already executing the SQL statement, so you can put the executed SQL statement in the tool and run it, and you will know where the problem is.

MYSQL reported Fatal error encountered during command execution solution

Add to the connection string

Allow User Variables=True

solve.

Otherwise, errors will be reported from time to time, the stored procedure name will be longer, and the error will be reported, and sometimes it will not be reported. If the parameter is passed 1 digit, it will be normal and 2 digits will be reported as an error…

MySQL Error: Error writing file ‘/tmp/MY4WYVlC‘ (Errcode: 28 – No space left on device)

MySQL Error: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
I. Problem Description
1、MySQL is fine, but suddenly it doesn’t work, with the following information.
System internal error.
Error message.
org.springframework.jdbc.UncategorizedSQLException:
Error querying database. Cause: java.sql.SQLException: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
The error may exist in file [/home/webapps/xxxxl/XXXDao.xml]
The error may involve com.thxxx.getListArea-Inline
The error occurred while setting parameters
SQL: SELECT a.id, ROUND(a.wt* 99/(SELECT max(wt) from xxxx ),2) AS wt, ROUND(a.qt, 2) AS qt, DESC LIMIT ?  …
Cause: java.sql.SQLException: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [3]; Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device); nested exception is java.sql.SQLException: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExcepti

Caused by: java.sql.SQLException: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)

Second, the problem is solved
1、Cause: Errcode: 28 – No space left on device : Insufficient disk space. There is not enough disk space on the server where MySQL is installed.
2、Solution: Expand disk space; delete junk files, etc.
3、Linux steps for reference.
df -lh : Check the disk space distribution
cd xx : Enter the directory that takes up all the space
du -sh * : check the size of each directory file
Find the junk files that take up space
rm xx : Delete the junk files.