Category Archives: MySQL

pymysql Error: File “/usr/local/lib/python2.7/site-packages/PyMySQL-1.0.2-py2.7.egg/pymysql/connections.py”, line 167 SyntaxError: invalid syntax

raceback (most recent call last):
  File "log2user-real.py", line 49, in <module>
    db = create_engine("mysql+pymysql://root:{m_pass}@10.157.2.25:8306/log_user?charset=utf8".format(m_pass=m_pass))
  File "<string>", line 2, in create_engine
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/create.py", line 548, in create_engine
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/pymysql.py", line 68, in dbapi
    return __import__("pymysql")
  File "build/bdist.linux-x86_64/egg/pymysql/__init__.py", line 59, in <module>
  File "/usr/local/lib/python2.7/site-packages/PyMySQL-1.0.2-py2.7.egg/pymysql/connections.py", line 167
    *,
     ^
SyntaxError: invalid syntax
Reason: The latest version of PyMySQL does not support Python 2.7

Solution: Specify the PyMySQL version as 0.10.1, and write in the requirements.txt file:PyMySQL==0.10.1

pip install pymysql==0.10.1

[Solved] ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /desdb_j000_26964.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

 

Error analysis:
The external table is not deleted normally,There is still information left in the data dictionary,The table does not actually exist.
An error is reported when the database runs the collect statistics job to collect these external tables.

 

Solution:

1. Find the object starting with ET:

select owner,
       object_name,
       object_type,
       status,
       to_char(created, 'yyyy-mm-dd hh24:mi:ss') created,
       to_char(last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') last_ddl_time
  from dba_objects
 where object_name like 'ET$%';

2. Confirm that it belongs to the external table generated by dump

select owner,table_name,default_directory_name,access_type from dba_external_tables order by 1,2;

3. Delete

drop table SYSTEM.ET$02D805830001;

drop table SYSTEM.ET$03B700030001;

[Solved] Job for mysqld.service failed because the control process exited with error code

When closing the virtual machine, and then reopening the virtual machine, The following error will appear when executing the command systemctl start mysqld to start the mysql service: Job for mysqld.service failed because the control process exited with error code. See “systemctl status mysqld.service” and “journalctl -xe” for details.
 Insert picture description here
The above situation occurs because the virtual machine is shutting down, mysql service-related control process error, After my own learning and experience, I got a solution: First enter the specified directory/run, create a mysqld file in this directory, Then authorize this file, In this way, the mysql service can be started normally
Insert picture description here
In fact, when we install mysql, start When mysql service, use the command systemctl enable mysqld to set the boot to start automatically, the above error will not occur.

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] 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] sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, “You have an error in your SQ

Error Messages;

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "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 'fromt_user' at line 1")

 

The error message is like this, which means that my SQL syntax is wrong

Today, when pandas was connecting to the MySQL database, there was an error in the last run

Source code:

from sqlalchemy import create_engine

def query(table):
    host = 'localhost'
    user = 'root'
    password = '123456'
    database = 'ConstructionDB'
    port = 3306

    conn = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(user, password, host, port, database))

    sql = 'select * from' + table
    results = pd.read_sql(sql, conn)
    return results

Through debugging, it is found that there is no space in the original SQL statement when it is spliced, resulting in the last syntax of SQL

sql = [select * fromt_user]

Solved it!

def query(table):
    host = 'localhost'
    user = 'root'
    password = '123456'
    database = 'ConstructionDB'
    port = 3306
    conn = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(user, password, host, port, database))
    sql = 'select * from' +' ' + table
    results = pd.read_sql(sql, conn)
    return results

Pay attention to the SQL statement and splice a space in the middle of the code. The problem is solved

[Solved] QuestDB Exception–ERROR: Cannot insert rows out of order.

I am trying to migrate the data and insert the history into QuestDB. I create the table as

create table records(
type INT,
interval INT,
timestamp TIMESTAMP,
name STRING) timestamp(timestamp)

And inserts data through JDBC.

1. Question details

I received the error “cannot insert rows out of order”. I read that QuestDB supports are out of order, but somehow I can’t make it work.

Caused by: org.postgresql.util.PSQLException: ERROR: Cannot insert rows out of order. Table=/root/.questdb/db/dwd_robot_running_data
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)
	... 19 more

2. Cause of problem

You can only insert rows in the partitioned table out of order, create a new partitioned table and copy the data into it

3. Solution

Adding partitions during table creation

create table records2(
 type INT,
 interval INT,
 timestamp TIMESTAMP,
 name STRING
) 
timestamp(timestamp) partition by DAY

insert into records2
select * from records

drop table records

rename table records2 to records

After that, you can insert out of order into the table records

[Solved] MYSQL Import Error: ERROR 1118 (42000): Row size too large (> 8126)

When doing database restore recently, I encountered the following problems

ERROR 1118 (42000) at line 79532: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRnt row format, BLOB prefix of 768 bytes is stored inline.

 

 

Solution:

Modify the mysql configuration file my.cnf:

innodb_file_format=Barracuda 
innodb_file_per_table=1 

Then restart mysql and execute on the target table:
Alter table <table_name> engine=innodb ROW_FORMAT=DYNAMIC; 

 

Finally re-import.

 

If it still doesn’t work, consider adjusting the parameters:

innodb_log_file_size = 256M try to increase this configuration

[Solved] Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘event read from binlog did not pass crc check; the first event

mysql copy error from the library:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'event read from binlog did not pass crc check; the first event '' at 4, the last event read from './mysql-bin.031260' at 506629253, the last byte read from './mysql-bin.031260' at 506637422.'

 

Check the log file on the main library:

mysqlbinlog --verify-binlog-checksum /data/mysqldb/mysql-bin.031260

 

Error:

ERROR: Error in Log_event::read_log_event(): 'Event crc check failed! Most likely there is event corruption.', data_len: 8169, event_type: 30
ERROR: Could not read entry at offset 506629253: Error in log format or read error.
WARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set.
This might be because the last statement was not fully written to the log, or because you are using a --stop-position or --stop-datetime that refers to an event in the middle of a statement.
The event(s) from the partial statement have not been written to output.

 

It can be seen that there is a problem with the binlog on the main library, so this can only be rebuilt from the library. Rebuild master-slave replication.

If there is no error in the verification on the main library, you can disable verification on the slave library to solve the problem:

stop slave;

set global binlog_checksum=0;

start slave;

 

centos Compile and install mysql error: make[2]: *** [storage/perfschema/unittest/pfs_connect_attr-t]

Error Messages:

/opt/install-files/Package/mysql-5.7.38/sql/rpl_binlog_sender.cc:828: undefined reference to `user_var_entry::val_int(char*) const'
collect2: error: ld returned 1 exit status
make[2]: *** [storage/perfschema/unittest/pfs_connect_attr-t] Error 1
make[1]: *** [storage/perfschema/unittest/CMakeFiles/pfs_connect_attr-t.dir/all] Error 2
make: *** [all] Error 2

 

Solution:

Delete the parameter -DWITH_PARTITION_STORAGE_ENGINE=1 in cmake and re-compile

SqlSugar Connect MySql 8.0.29 Error [How to Solve]

1. Background

I exported the database table structure and data from the company server Mysql (version: 8.0.16) and restored it to my local computer Mysql (version: 8.0.29). Just after starting the project, I was prompted with a SqlSugar database connection error. The prompt error is as follows:

Execute Db.Ado.CheckConnection() reports an error
Connection open error . The given key ‘0’ was not present in the dictionary.

note: the connection string: database=stocks;server=127.0.0.1;port=3306;uid=root;pwd=123;

The following is the error report problem of individual table query.

DB.Queryable().Where(p=>true).ToList() error. ToList() error, after analysis, the main problem is the current table field character set and sorting rules are not uniform. The error is reported as follows.
MySqlException: “Fatal error encountered attempting to read the resultset.”
Internal exception MySqlException: Expected end of data packet

2. Solution

1. Solution to connection error

Add charset=utf8mb4, and the connection will no longer report errors note: if you want utf8mb4, I use utf8 locally and still report an error

database=stocks;server=127.0.0.1;port=3306;uid=root;pwd=123;charset=utf8mb4;

2. Modify the inconsistency of database character set

Execute the following SQL script as required.

(1) Change the encoding (character set) of a table and the encoding (character set) of all fields in the table:

ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
·TABLE_NAME replace to your table name.

(2) Change the encoding (character set) of all tables and the encoding (character set) of all fields in the table:

SELECT
	CONCAT(
		'ALTER TABLE ',
		TABLE_NAME,
		' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
	)
FROM
	information_schema.`TABLES`
WHERE
	TABLE_SCHEMA = 'DATABASE_NAME';
·DATABASE_NAME replace to your databese name.

3. Other contents

Query all table names of a database:

SHOW FULL COLUMNS FROM TABLE_NAME;
·TABLE_NAME replace to your table name.

[Solved] public key is not available client side (option serverRsaPublicKeyFile not set)

Error:

An occasional fault is found and an error is reported when initializing the database connection pool:

public key is not available client side (option serverRsaPublicKeyFile not set)

Detailed error reporting contents are as follows:

2022-08-24 16:35:08.008 ERROR 233504 --- [     main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.

java.sql.SQLTransientConnectionException: Could not connect to address=(host=127.0.0.1)(port=3306)(type=master) : RSA public key is not available client side (option serverRsaPublicKeyFile not set)
 at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)
 at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:192)
 at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1372)
 at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:635)

After a long time of troubleshooting, it was finally found that the reason is:
the database used in the project is MySQL 8.0.X, but the connection driver used is MariaDB (MariaDB was used before). pom file dependencies are as follows:

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.6.1</version>
</dependency>

Solution:

Just change the driver to MySQL
pom.xml dependency

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

application.yml driver and URL configuration.

spring:
	datasource:
		driver: driver: com.mysql.cj.jdbc.Driver
		url: jdbc:mysql://127.0.0.1:3306/databaseName?serverTimezone=GMT%2B8&characterEncoding=utf8