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

"postion":{"gtid":"","included":false,"journalName":"mys
ql-bin.000591","position":25928,"serverId":21381487,"timestamp":1663232397000}}}],"destination":"example"}[root@iZ2vcfjagtpbh97ar0zj6cZ example]

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

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

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

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.