Category Archives: MYSQL

[Solved] MySQL Add New Field Error: ERROR 1118 — Row size too large. The maximum row size for the used table type

error message

1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.
You have to change some columns to TEXT or BLOBs

Error reason

When MySQL builds a table, there is a limit on the maximum length of a single row: the total number of bytes set in all fields in a table is not more than 65535 bytes

Note 1: when the size of a single field exceeds 65535, it is converted to text
note 2: the sum of other fields does not exceed 65535 bytes (excluding blob/text)
note 3: the database uses UTF-8 encoding, and one character = three bytes
error reporting example: there are 10 varchar fields in the database, If each size is 3000, the current calculation length of the database single line is 3000 * 10 * 3 = 90000 & gt; 65535

Solution: set the large field type of the database table to text, or reduce the length of some parts that can be reduced to less than 65535

[Solved] MYSQL Workbench Error: ssl is required but the server doesn’t support it

 

Problem Description:

1. Click testconnection to report an error:

2. The direct connection error after saving is as follows:

3. Use Navicat to connect successfully

4. Using MySQL command line connection error

ERROR 1045 (28000): Access denied for user 'xx'@'xxx.xxx.xxx.xxx:52594' (using password: Yes)
yl@LAPTOP-TQLOQ2A3:~$

Problem-solving:

1. It is observed that Navicat does not use SSL, as shown in the figure:

2. MySQL workbench has the option to use SSL, and there is no option not to use SSL, as shown in the figure:

3. According to the query data, my MySQL workbench version is 8.0.27. Remove the relevant options, uninstall and reinstall 8.0.26. You can see the relevant options (no and if available), as shown in the figure:

4. If it is modified to no or if available, an error is reported:   Access denied for user ‘xx’@’xxx.xxx.xxx.xxx:55839’ (using password: Yes)

5. Uninstall version 8.0.26 and reinstall the older version. Here is 6.3.7 (download address). The connection is successful!

MySQL connection error no suitable driver found [How to Solve]

I thought the driver was not loaded before, so I added it

Class.forName("com.mysql.cj.jdbc.Driver");

After discovering the error, I read the error code again: no suitable driver found for localhost: 3306/upload_file

It turned out that the URL was misspelled and not prefixed: jdbc:mysql://

Attach the complete URL, and remember to add the suffix parameter

jdbc:mysql://localhost:3306/upload_file?&useSSL=false&serverTimezone=UTC

MySQL Executenamy: How to Write a batch of data

Here is a simple method for you to write a batch of data in MYSQL Executenamy.
Examples:
if isinstance(item,MmzItem):
            data = {'xinxi':item['xinxi']}
            xinxia = data['xinxi']
            print(len(xinxia),type(xinxia))
            #print("ffffffffffffff",xinxi[0],xinxi[0][8])
            try:
                con.executemany('insert ignore into xi(zhi,gon,go,gon1,nian2,di2,xue2,jin2,rq2,w2z)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',xinxia)
            except pymysql.Error as e:
                print(e)
                connect.rollback()
                connect.close()
                sys.exit(0)
            connect.commit()
            connect.close()
            #connect.commit()
            print("Saving.........................")

Expression #1 of ORDER BY clause is not in SELECT list, references column ‘xxxx’ which is not in SELECT list; this is incompatible with DISTINCT

Access system error after MySQL upgrade to 5.7.31.
Solution:
Modify /etc/my.cnf
file at the bottom to append :
[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

[Solved] CentOS 7.9 Install MySQL Error: 2003- cannot connect to mysql server on ‘10.10.1.202’(10060 “Unknown error”)

 

After installation, the remote connection reports an error:

2003- cannot connect to mysql server on ‘10.10.1.202’(10060 “Unknown error”)

Solution:

Open 3306 ports

firewall-cmd --zone=public --add-port=3306/tcp --permanent
#Command Meaning
--zone #Role 
--add-port=3306/tcp #Add a port in the format of: port/communication protocol 
--permanent #Permanent

Restart the firewall

systemctl restart firewalld.servic

perhaps

 service firewalld restart

perhaps

firewall-cmd --reload

[Solved] MySQL Error: ERROR! The server quit without updating PID file

The server quit without updating PID file (/usr/local/MySQL/data/ xxxxx.pid) error.
mysql does not have read/write access to the data directory.

Mac：sudo chmod -R a+rwx /usr/local/mysql/data/
Linux：chown -R mysql:mysql /var/lib/mysql
Then Restart:sudo usr/local/mysql/support-files/mysql.server start

Done!

Lock wait timeout exceeded; try restarting transaction-Mysql [How to Solve]

1. Origin of the problem

Now I’m working as the background of a small program, written in Java and MySQL used in the database. I’ve been doing well in debugging before. Today, when I was debugging, I suddenly reported an error:

### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; Try restarting transaction
#### the error may involve defaultparametermap
### the error occurred while setting parameters
### SQL: update table name set update_time = now(), pet_state = ?, first_ interaction_time = now(), stock_count = stock_count + 1, update_name = ? Where pet_id = ?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

Because the business operation is complex, it is just one of the operations, including adding, querying and modifying. In short, there are a lot of operations.

After writing the code, it is also the first time for me to test. How can I suddenly report this error? The general meaning is: the lock waiting timeout is exceeded; Attempt to restart the transaction.

2. Problem analysis

Get this bug and immediately use the search engine to search. Netizens gave some answers. Some said they added and modified the same data in the same transaction.

Some say that a transaction takes too long to execute. Just kill it. In a word, this operation is carried out in a transaction for unknown reasons

This leads to long execution and eventually problems. I have debugged myself for two or three times, and the results are the same. I simply hit a breakpoint to debug and see what the problem is. Fortunately, I found it soon

The cause of the problem. I accidentally wrote an infinite loop in a piece of code, and the infinite loop was executed in a transaction. Finally, there was a problem
III. solutions

After finding the cause of the problem, modify the code immediately, test again, and solve the problem. Although the problem is caused by carelessness when writing code, let yourself learn

At a new knowledge point, MySQL transaction execution has a set duration. If it exceeds this duration, problems in the title may occur. A fall into a ditch makes you wiser.

[Solved] MYSQL8 group by Error: Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY

Reason: myql8 enable only_FULL_GROUP_BY

1. Select @global.sql_mode   A value indicates that group by mode is enabled

2. Turn off this mode:

Add a line of instructions under [mysqld] in my.cnf configuration file

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_ BY_ZERO,NO_ENGINE_SUBSTITUTION

3. Restart MySQL service