当前位置：首页 > backend >正文

解决Mawell1.29.2启动SQLException: You have an error in your SQL syntax问题

backend 2025/5/17 11:01:34

问题背景

此前在openEuler24.03 LTS环境下的Hive使用了MySQL8.4.2，在此环境下再安装并启动Maxwell1.29.2时出现如下问题

[ERROR] Maxwell: SQLException: 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 'MASTER STATUS' at line 1

原因分析及解决方案

从Maxwell1.42.0版本才开始支持MySQL8.4版本。

而从Maxwell1.30.0开始，不支持JDK8，需要JDK11，也就是说Maxwell1.42.0也需要JDK11，如果要使用JDK8，需要使用比Maxwell1.30.0以下的版本，例如：Maxwell 1.29.2。

但通过解压tar包方式安装Hive，支持的Java版本最高为JDK8。

综合以上：Hive最高只能支持JDK8，在安装有Hive的环境下同时安装Maxwell，就只能安装Maxwell1.30.0以下，例如：Maxwell1.29.2；Hive和Maxwell同时需要MySQL，而Maxwell1.29.2不支持MySQL8.4，就需要降低MySQL版本，例如：MySQL8.0.42。

降低MySQL版本

降低MySQL版本为MySQL8.0.42，可参考：openEuler24.03 LTS下安装MySQL8.0.42

修复Hive

修改Hive MySQL驱动

查看MySQL8.0.x可用的驱动

https://mvnrepository.com/artifact/com.mysql/mysql-connector-j

看到MySQL8.0.33为8.0.x的最新驱动，点击图上8.0.33

点击图片箭头指向的jar，下载得到mysql-connector-j-8.0.33.jar，并将下载得到的jar上传到Linux Hive的lib目录(/opt/module/hive-3.1.3/lib/)下

[liang@node1 lib]$ ls | grep mysql-conn
mysql-connector-j-8.0.33.jar
mysql-connector-j-8.4.0.jar

删除原来的mysql-connector-j-8.4.0.jar

[liang@node1 lib]$ rm -rf mysql-connector-j-8.4.0.jar
[liang@node1 lib]$ ls | grep mysql-conn
mysql-connector-j-8.0.33.jar

初始化Hive元数据库

创建metastore数据库，metastore作为Hive的元数据库

[liang@node1 lib]$ mysql -uroot -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.42 Source distributionCopyright (c) 2000, 2025, 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 metastore;
Query OK, 1 row affected (0.00 sec)mysql> quit;
Bye
[liang@node1 lib]$

初始化Hive元数据库有两种方法：

方法1.如果此前有备份元数据，直接导入之前的备份了元数据库的数据，这样此前的Hive元数据不会丢失，能查到此前存在的表。

方法2.使用schematool命令初始化元数据库，此时元数据库是新的，不能查到此前存在的表。

根据实际情况选择其中一个方法操作，这里使用方法2演示。

$ schematool -initSchema -dbType mysql -verbose

初始化成功后，部分输出如下

0: jdbc:mysql://node1:3306/metastore> /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
No rows affected (0.001 seconds)
0: jdbc:mysql://node1:3306/metastore> !closeall
Closing: 0: jdbc:mysql://node1:3306/metastore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
beeline>
beeline> Initialization script completed
schemaTool completed

到此Hive修复完成！

设置Maxwell

说明：

1.这里使用的Maxwell版本为Maxwell1.29.2；

2.此前已完成下载Maxwell并解压Maxwell，同时设置了Maxwell的环境变量，配置了config.properties，参考：安装Maxwell教程

开启bin-log

创建需要开启bin-log的数据库，例如：test

[liang@node1 ~]# mysql -uroot -p000000
...
mysql> create database test;

修改mysql-server.cnf

[liang@node1 ~]$ sudo vim /etc/my.cnf.d/mysql-server.cnf

注意：这里修改的是/etc/my.cnf.d下的cnf文件。不建议直接修改/etc/my.cnf文件，否则可能会造成MySQL重启失败。

重启MySQL

[liang@node1 my.cnf.d]$ sudo systemctl restart mysqld

查看MySQL状态

[liang@node1 my.cnf.d]$ sudo systemctl status mysqld
● mysqld.service - MySQL 8.0 database serverLoaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: disa>Active: active (running) since Thu 2025-05-08 00:52:19 CST; 7s agoProcess: 6909 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status>Process: 6935 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (co>Main PID: 6972 (mysqld)Status: "Server is operational"Tasks: 38 (limit: 21353)Memory: 361.9M ()CGroup: /system.slice/mysqld.service└─6972 /usr/libexec/mysqld --basedir=/usr5月 08 00:52:18 node1 systemd[1]: Starting MySQL 8.0 database server...
5月 08 00:52:19 node1 systemd[1]: Started MySQL 8.0 database server.

按q返回Linux命令行。

查看master logs

mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       157 | No        |
+------------------+-----------+-----------+
1 row in set (0.00 sec)

创建maxwell数据库、创建maxwell用户并赋予其必要权限

mysql> CREATE DATABASE maxwell;
Query OK, 1 row affected (0.00 sec)mysql> CREATE USER 'maxwell'@'%' IDENTIFIED BY 'maxwell';
Query OK, 0 rows affected (0.01 sec)mysql> GRANT ALL ON maxwell.* TO 'maxwell'@'%';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'maxwell'@'%';
Query OK, 0 rows affected (0.00 sec)mysql>

启动ZooKeeper

$ zkServer.sh start

启动Kafka

$ kafka-server-start.sh -daemon $KAFKA_HOME/config/server.properties

启动Maxwell

[liang@node1 maxwell-1.29.2]$ bin/maxwell --config config.properties
Using kafka version: 1.0.0
01:12:46,841 INFO  Maxwell - Starting Maxwell. maxMemory: 786956288 bufferMemoryUsage: 0.25
01:12:46,883 INFO  SchemaStoreSchema - Creating maxwell database
01:12:47,042 INFO  ProducerConfig - ProducerConfig values:acks = 1batch.size = 16384bootstrap.servers = [node1:9092]buffer.memory = 33554432client.id =compression.type = snappyconnections.max.idle.ms = 540000enable.idempotence = falseinterceptor.classes = nullkey.serializer = class org.apache.kafka.common.serialization.StringSerializerlinger.ms = 0max.block.ms = 60000max.in.flight.requests.per.connection = 5max.request.size = 1048576metadata.max.age.ms = 300000metric.reporters = []metrics.num.samples = 2metrics.recording.level = INFOmetrics.sample.window.ms = 30000partitioner.class = class org.apache.kafka.clients.producer.internals.DefaultPartitionerreceive.buffer.bytes = 32768reconnect.backoff.max.ms = 1000reconnect.backoff.ms = 50request.timeout.ms = 30000retries = 0retry.backoff.ms = 100sasl.jaas.config = nullsasl.kerberos.kinit.cmd = /usr/bin/kinitsasl.kerberos.min.time.before.relogin = 60000sasl.kerberos.service.name = nullsasl.kerberos.ticket.renew.jitter = 0.05sasl.kerberos.ticket.renew.window.factor = 0.8sasl.mechanism = GSSAPIsecurity.protocol = PLAINTEXTsend.buffer.bytes = 131072ssl.cipher.suites = nullssl.enabled.protocols = [TLSv1.2, TLSv1.1, TLSv1]ssl.endpoint.identification.algorithm = nullssl.key.password = nullssl.keymanager.algorithm = SunX509ssl.keystore.location = nullssl.keystore.password = nullssl.keystore.type = JKSssl.protocol = TLSssl.provider = nullssl.secure.random.implementation = nullssl.trustmanager.algorithm = PKIXssl.truststore.location = nullssl.truststore.password = nullssl.truststore.type = JKStransaction.timeout.ms = 60000transactional.id = nullvalue.serializer = class org.apache.kafka.common.serialization.StringSerializer01:12:47,076 INFO  AppInfoParser - Kafka version : 1.0.0
01:12:47,076 INFO  AppInfoParser - Kafka commitId : aaa7af6d4a11b29d
01:12:47,121 INFO  Maxwell - Maxwell v1.29.2 is booting (MaxwellKafkaProducer), starting at Position[BinlogPosition[mysql-bin.000003:157], lastHeartbeat=0]
01:12:47,229 INFO  AbstractSchemaStore - Maxwell is capturing initial schema
01:12:47,396 INFO  BinlogConnectorReplicator - Setting initial binlog pos to: mysql-bin.000003:157
01:12:47,418 INFO  BinaryLogClient - Connected to node1:3306 at mysql-bin.000003/157 (sid:6379, cid:24)
01:12:47,418 INFO  BinlogConnectorReplicator - Binlog connected.

看到能正常启动了，说明Maxwell启动问题已解决！

完成！enjoy it！

查看全文

http://www.xdnf.cn/news/6419.html