Causes:
When two fields are trying to be subtracted, if one or both fields are of type unsigned, an error is reported if the value to be subtracted is less than 0 (BIGINT UNSIGNED value is out of range)
.
Handling:
Example: select a – b from table
Change:select if(a >= b, a – b, – (b – a)) from table
The two tables are:
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户编号（自增字段）',
`userName` varchar(32) NOT NULL DEFAULT '' COMMENT '用户昵称',
# ...
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8 COMMENT='用户表';
CREATE TABLE `tb_sms_message` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机号',
# ...
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8;
Error Message:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
SELECT * FROM `tb_sms_message`
where tel not IN (select userName from tb_user);The collates of the two associated fields are inconsistent and cannot be directly associated
Here, we can convert the proofreading set of the field to consistent:
CONVERT(tel USING utf8) COLLATE utf8_general_ci
Modified SQL:
SELECT * FROM `tb_sms_message`
where CONVERT(tel USING utf8) COLLATE utf8_general_ci not IN (select userName from tb_user);
The reason for this error is to connect to MySQL through pymysql, and there is no operation to close the connection, so there will be no problems in a short time, and the connection will be confused if the connection is maintained for a long time. Although looking at my code is correct, I still report
pymysql.err.InterfaceError: (0,”) error. So this connection is either connected and then closed when it is used up. Or just use the following code to check if the connection exists, and reconnect if disconnected.
db = pymysql.connect(host = ' 127.0.0.1 ' ,port=3306,user = ' user ' , passwd = ' pwd ' , db = ' db_name ' , charset = ' utf8 ' ) sql ='select * from table_name' db.ping(reconnect = True) cur.execute(sql) db.commit()
end!
Got fatal error 1236 from master when reading data from binary log:’Could not find first log file name in binary log index file’ occurs in master-slave synchronization, it is caused by inconsistent master-slave log version numbers. The solution is as follows:
View the main log number MariaDB [(none)] > Flush logs; (At this time, the main server will recreate a binlog file;) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+---- --------------+ | mysql-bin.000003 | 385 | | | +------------------+----------+--------------+---- --------------+ 1 row in set (0.000 sec) View the slave log file number from show slave status\G File :mysql-bin.000005 It was found that the master and slave log file numbers were different. Stop slave from it;
MariaDB [(none)]> change master to master_log_file ='dbmaster-bin.000005',master_log_pos=120;
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.220.130 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 385 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes
Solution:
Add the following configuration under the mysql configuration file /etc/my.cnf. Add under the
[mysqld] node.
explicit_defaults_for_timestamp = ON
Restart the mysql database and try the configuration to take effect.
Restart command service mysqld restart
Reason: When the hive data is imported into mysql, the content of a field exceeds the field length set by mysql, so an error is reported
Solution:
yarn logs -applicationId application_1621320191765_0037
Check the corresponding applicationid log, find the field that exceeds the length, and reset the field content
Backup restore or data import error 1153: Got a packet bigger than’max_allowed_packet’bytes problem
There are 2 solutions to this problem:
1. Temporary modification:
mysql>set global max_allowed_packet=524288000; #修改 512M2. To modify my.cnf (my.ini under windows), mysql needs to be restarted.
Add a sentence in the [MySQLd] section (if it exists, adjust its value):
max_allowed_packet=256M (adjust the value according to the actual situation)
You can use the command:
show VARIABLES like '%max_allowed_packet%’;Check whether the modification is successful!
MYSQL error as below:
#1046 - No database selected
Solution:
Select a database before importing sql files, otherwise the system can’t determine which database your sql files are actually going to be imported to, so it will report an error as above.
An error encountered when deploying the company’s web project to myeclipse: Access denied for user’ODBC’@’localhost’ (using password: NO), it seems that mysql does not allow remote access by default.
For the record, the solution is as follows :
1. Stop mysql service
2. Find my.ini in the mysql installation directory, find [mysqld] inside and add this sentence below: skip_grant_tables (skip the authorization table authentication when starting the MySQL service)
Remember to save and save.
3. Find the bin directory of myql in cmd, and enter the command: mysql -u root -p
No need to enter a password, press Enter
4. After entering, enter: use mysql
5. Modify the password input: update mysql.user set authentication_string = password(“newly set password”) where user=”root”; (My mysql version is 5.7, and the attribute of the password column is called authentication_string; 5.1 is password );
6. Flush privileges: flush privileges;
7. Exit: quit;
8. Remove the skip_grant_tables in my.ini and restore the authorization table authentication when logging in
9. Restart the mysql service, and then log in with root and the modified password: mysql -u root -p
Then enter the password to verify. Successful entry means success.
The above are the specific steps, I hope to help you with the problem,
When installing MySQL in CentOS, an error is reported when executing the command mysqld — initialize/lib/LD Linux. So. 2: bad elf interpreter: no such file or directory
This is because a 32-bit program is installed in a 64 bit system
Install glibc.i686:
yum install glibc.i686After executing the glibc.i686 installation command, we continue to execute mysqld — initialize and find that the system still reports an error:
error while loading shared libraries: libz.so.1:
cannot open shared object file: No such file or directory
This is because the zlib.i686 component is missing in the system. Let’s continue to install zlib.i686:
yum install zlib.i686 --setopt=protected_multilib=false <!--The setpot parameter handles multiple library coexistence conflicts-->At this point, if you continue to execute mysqld — initialize, the package still lacks i686 components. Later, the blogger continued to install various missing components, and found that this is not the way to do it. It is estimated that there are hundreds of such components missing. I don’t know when the installation will end. Later, I continued to search for methods on the Internet, Finally, we found a way to install all the components needed by 32-bit programs
yum install xulrunner.i686 <!--ia32-libs.i686 //is ubuntu series, and after 13.10 ubuntu also seems to have no this-->