The problem is that when a new row is inserted, an error is returned.
type:
mysql> insert into students values(1,"goutou",18,"male",12121);
Returns:
ERROR 1136 (21S01): Column count doesn't match value count at row 1
check statement is ok. Considering the previous operation of deleting a column, two new columns are added, and the input is:
mysql> insert into students(id,name,age,gender,clc_id) values(0,"zhutou",12,"male",12121);
Query OK, 1 row affected (0.06 sec)
SQLCODE: -407, SQLSTATE: 23502: The exact description of this error is such that a NULL value cannot be inserted into a column defined as NOT NULL. This is a violation of the integrity constraint exception.
in development, this error is something that we often encounter. The problem is simply, “Columns that cannot be empty are empty.” For a table with a large number of fields, it is difficult to sift. How to accurately
positioning?It’s actually pretty simple.
We can learn more from the error message, which can help us pinpoint the problem. Common error messages are as follows:
com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3
at com.ibm.db2.jcc.b.sf.d(sf.java:1396)
at com.ibm.db2.jcc.c.jb.l(jb.java:356)
at com.ibm.db2.jcc.c.jb.a(jb.java:64)
at com.ibm.db2.jcc.c.w.a(w.java:48)
at com.ibm.db2.jcc.c.dc.c(dc.java:312)
at com.ibm.db2.jcc.b.tf.cb(tf.java:1723)
at com.ibm.db2.jcc.b.tf.d(tf.java:2315)
at com.ibm.db2.jcc.b.tf.Z(tf.java:1326)
at com.ibm.db2.jcc.b.tf.execute(tf.java:1310)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Message: Integrity constraint exception (
--- The error occurred in D:\------- path omitted-------.
--- The error occurred while applying a parameter map.
--- Check the aaa.sql-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3)observe this error message carefully: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3. You can see that the error message has been located very well
is clear. We only need to query in syscat.columns of the system table. The syscat.columns table is a system table that holds detailed information about the COLUMNS of all tables in a DB2 database. We just need the basis
view, table name, column number can be queried to find which column. COLNO=3 in the error message above is the column number for which the error was reported. As follows:
SELECT
*
FROM
SYSCAT.COLUMNS
WHERE
TABSCHEMA = 'DB' AND
TABNAME = 'TT_PARAM_LOG' AND
COLNO = '3'
The first time to install and use mybatis, problems encountered are as follows:
Question 1: Unkonwn database ‘test’
I created a new test database in my own database, and the corresponding table name, the problem was solved. However, what I want to understand is: how to specify which database and which table to use in the XML configuration file, otherwise, this problem will appear.
Question 2: The server time zone value ‘appears in MySQL й ��� ʱ ‘is unrecognized record
Solution 1: modify the database time zone
set global time_ zone = ‘+8:00’; ## Modify the global time zone of Mysql to Beijing time, that is, the East 8 district where we are
in the database set time_ zone = ‘+8:00’; ## Modify the time zone of the current session
0 flush privileges; # Effective immediately
Solution 2: modify the value of driver and URL.
<?xml version="1.0" encoding="UTF-8" ?>
<!-- Pay attention to this configuration header tag, don't make a mistake -->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="bank">
<environment id="bank">
<!-- Using jdbc transaction management -->
<transactionManager type="JDBC" />
<!-- Database connection pooling -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/test?characterEncoding=utf-8" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!-- This place configures the entity class sql mapping file -->
<mappers>
<mapper resource="pufaSpring\otherMybatis\UserMapper.xml"/>
</mappers>
</configuration>MySQL Error: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
I. Problem Description
1、MySQL is fine, but suddenly it doesn’t work, with the following information.
System internal error.
Error message.
org.springframework.jdbc.UncategorizedSQLException:
Error querying database. Cause: java.sql.SQLException: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
The error may exist in file [/home/webapps/xxxxl/XXXDao.xml]
The error may involve com.thxxx.getListArea-Inline
The error occurred while setting parameters
SQL: SELECT a.id, ROUND(a.wt* 99/(SELECT max(wt) from xxxx ),2) AS wt, ROUND(a.qt, 2) AS qt, DESC LIMIT ? …
Cause: java.sql.SQLException: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [3]; Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device); nested exception is java.sql.SQLException: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExcepti
…
Caused by: java.sql.SQLException: Error writing file ‘/tmp/MY4WYVlC’ (Errcode: 28 – No space left on device)
…
Second, the problem is solved
1、Cause: Errcode: 28 – No space left on device : Insufficient disk space. There is not enough disk space on the server where MySQL is installed.
2、Solution: Expand disk space; delete junk files, etc.
3、Linux steps for reference.
df -lh : Check the disk space distribution
cd xx : Enter the directory that takes up all the space
du -sh * : check the size of each directory file
Find the junk files that take up space
rm xx : Delete the junk files.
This is due to the syntax problem of SQL statements, and everyone’s situation is different
mine is
Original: select teacherno, teachername, vote from t_ Vote
reports an error
change to: select * from t_ Vote
success
Check ORACLE data has a CS trigger compilation error, the problem is that the trigger state is invalid (invalid), but complie and can not pass, to drop and will report object invalid can not drop, so need to register him in sys.objerror$, in order to drop
Do you have this kind of error message ?
SQL> drop user MYUSER cascade;
drop user MYUSER cascade
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kqd-objerror$], [U], [0], [101],
[MYTRIGGER_MYCALCULATION], [], [], [], [], [], [], []
It means that one or more object is invalid. And in the error message you have the object name causing this error and then you can find the ids of instances of this object causing errors.
SQL> select object_id from dba_objects where object_name=‘MYTRIGGER_MYCALCULATION’;
OBJECT_ID
867767
840888
Now you can have logs about these objects.
SQL> select name, type , line, position, text from all_errors where name like ‘MYTRIGGER_MYCALCULATION’;
NAME TYPE LINE POSITION
TEXT
MYTRIGGER_MYCALCULATION TRIGGER 2 6
PLS-00049: bad bind variable ‘NEW.SYSVERSION’
MYTRIGGER_MYCALCULATION TRIGGER 3 4
PLS-00049: bad bind variable ‘NEW.SYSVERSION’
MYTRIGGER_MYCALCULATION TRIGGER 6 4
PLS-00049: bad bind variable ‘NEW.SYSVERSION’
Here we understand the errors are caused by compilation errors on a trigger. As we just want to drop the user object that contains this trigger, we can ignore these errors :
SQL> insert into sys.objerror$ values(867767);
insert into sys.objerror$ values(840888);
1 row created.
SQL> insert into sys.objerror$ values(840888);
insert into sys.objerror$ values(840888);insert into sys.objerror$ values(840888)
1 row created.
Then we can drop the user successfully.
SQL> drop user MYUSER cascade;
User dropped.
Docker mysql8 modify password
Set skip password login
docker exec -it mysql /bin/sh
# Note: The configuration file for editing is docker.cnf
vi /etc/mysql/conf.d/docker.cnf
exit
Restart MySQL container
docker restart mysql
Restart MySQL container
The old version of the command is invalid:
update user set password = password (‘a123456 ‘), where user =’root'</ s>
correct:
UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';
-- Change the password for use without expiration
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
flush privileges;
Error handling
ERROR 2059 (HY000): Authentication plugin ‘caching_ sha2_ password’ cannot be loaded: /usr/lib64/mysql/plugin/caching_ sha2_ password.so: cannot open shared object file: No such file or directory
Maybe the password is not set and will never expire. Maybe the database client version under Linux is too low. I tried to connect to mysql8 of docker in server B on server a, but the connection was successful by using DataGrid in windows.
Creating users and authorizations
create user 'nacos'@'%' identified by '123';
grant all on mid_nacos.* to 'nacos'@'localhost' ;
flush privileges;
Here, all the permissions of the table are granted, and all the items that can be authorized are explained and referenced https://www.cnblogs.com/yinzhengjie/p/10263692.html
Solve the error of operation create user failed for ‘user’ @ ‘%
It may be that the user already exists, you can delete and recreate it to
check whether the user exists: select user from user
delete: drop user 'user' @ '%
Solving com.mysql.cj.exceptions.unabletoconnectexception: public key retrieval is not allowed
Add & amp; after the connection information; allowPublicKeyRetrieval=true
I am transferring from MySQL to BigQuery with Embulk, but when I try to execute it in a certain environment after a long time, an error occurs.
embulk Error: OutputPlugin 'bigquery' is not found
If you take a closer look, you will see that the ruby version does not support faraday with the following error.
[91mERROR: Error installing embulk-output-bigquery:
faraday-net_http requires Ruby version >= 2.4.0.[0m[91m
There was a description in output-bigquery GitHub Issue .
Since it is running on Docker this time, the problem was solved by describing the following in the Docker file.
#Install Embulk
RUN wget -q https://dl.embulk.org/embulk-latest.jar -O /bin/embulk \
&& chmod +x /bin/embulk
# Response to this error
RUN embulk gem install representable -v 3.0.4
RUN embulk gem install faraday -v 0.17.4
# Add the plug-ins you want to use.
RUN embulk gem install embulk-input-mysql
RUN embulk gem install embulk-output-bigquery
・・・ERROR: relation "activities" does not exist
In conclusion, aligning the username and schema name will solve the problem.
SHOW search_path;
If you check with
$user, public
Since user is public, use the schema name.
set search_path to schema_name;The binary installation mysql reports the following error
[root@tzPC sdb1]# mysql -v
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
It is found that /tmp/mysql.sock does not exist. The reason for this is generally due to inconsistent configuration files. If mysqld is started by mistake, mysqld_safe will clear mysql.sock once.
First check whether the current process has a surviving mysql, kill it, and then restart the database
$ ps -ef | grep mysql
$ kill -9 xxxx
$ /etc/init.d/mysqld start
If it still cannot be resolved, check whether the configuration file is correct
$ cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/mnt/sdb1/mysql
datadir=/mnt/sdb1/mysql_data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock
Use the command to find the mysql.sock file
$ find / -name mysql.sock
If not found, re-execute mysql_install_db to rebuild the authorization table
$ mysql/bin/mysql_install_db
Then execute the following command, the mysql.sock file will be there
$ mysql/bin/mysqld_safe &
$ find / -name mysql.sock
/tmp/mysql.sockThere are 5 solutions as follows, I generally use the first one to solve the problem, and the other online searches are organized as follows for reference
1. Kill the existing mysql process
ps -ef | grep mysqld kill -9 xxx
2. Insufficient mysql file permissions
chown -R mysql:mysql /mnt/mysql chmod -R 755 /mnt/mysql /etc/init.d/mysqld restart
3. When mysql is installed for the second time, there is residual data that affects the service startup
Delete the mysql-bin.index file in the /mysql_data directory
4. View the configuration file my.cnf
[root@tzPC mysql]# cat /etc/ my.cnf [mysqld] user = mysql basedir =/mnt/ mysql datadir =/mnt/ mysql_data #Check if there is this line socket =/tmp/ mysql.sock server_id = 6 port = 3306 [mysql] socket =/tmp/mysql.sock
5. Turn off selinux
[root@tzPC mysql]# cat /etc/selinux/config # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of three two values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted
Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: Yes)
This prompt is a password error. If you forget your password, you can use this method to reset your password
* * 1. * * the first step to reset your password is to skip the password authentication process of MySQL
modify the MySQL configuration file
and find it in the directory of C:// program files/MySQL/MySQL server 5.7 my.cnf/my .ini/my- default.ini One of the three is modified
in [mysqld] skip-grant-tables
**2 * * restart MySQL service
net start mysql
If this command reports an error and cannot start MySQL service, run it as an administrator cmd.exe ( cmd.exe In the directory of C: Windows: system32
**3. * * implementation again
mysql -u root -p
You don’t need to enter a password. Press enter to enter mysql
**4 * * Change Password
update user set Password=PASSWORD('888888') where User='root';
FLUSH PRIVILEGES;
Use 888888 to login next time
5. If 4 reports error 1820 (HY000): you must set password before executing this statement, execute the code
SET PASSWORD = PASSWORD('123456');
or SET PASSWORD = '123456';
Then prompt query OK, 0 rows affected (0.00 sec)
and you can log in with 123456 password