Tag Archives: SQL

Execution Error SQL_ERROR_INFO: near “.“: syntax error

I made the simplest mistake when doing SQL problems today

update  salaries set salaries.salary = salaries.salary *1.1 
where salaries.emp_no = (select emp_no from emp_bonus ) and to_date = '9999-01-01'

Then the idea reports an error. The error is near “.”: syntax error. In fact, when I update the salary table here, I don’t need to add “.” to index the elements in the table. I can call it directly.

update  salaries set salary = salary *1.1 
where emp_no = (select emp_no from emp_bonus ) and to_date = '9999-01-01'

Just change this.

MYSQL Safe Upgraded Error: You are using safe update mode and you tried to update

Error:

21:55:14 update users set realname=‘Lisi2’ where username=‘ls’ Error
Code: 1175. You are using safe update mode and you tried to update a
table without a WHERE that uses a KEY column. To disable safe mode,
toggle the option in Preferences -> SQL Editor and reconnect. 0.000
sec

Solution:
Execution of this statement will result in: SET SQL_SAFE_UPDATES=0;

After switching the tidb database, an error could not commit JDBC transaction appears from time to time

1. Exception description

org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException: Write conflict, txnStartTS=426985930345676879,

After switching the tidb database, the above error reports occur from time to time;

2. Problem analysis

It is found that there is a write conflict by searching the online data according to the error content. The optimistic lock is used by default under version 3.0 of tidb database. This problem will occur in the case of multiple concurrent modifications;

3. Solution

(1) Add & amp; database connection; sessionVariables=tidb_ txn_ Change mode = pessimistic to pessimistic lock

(2) Break big things apart

4. Subsequent optimization selection

Invalid bound statement (not found) of custom SQL in mybatisplus

1、 Mybatisplus only configures mapper in YML, and the path scanning is not enough

mapper-locations: classpath:com/jack/shale_porosity/mapper/xml/*.xml

In this way, only the basemapper provided by mybatis can be used. The mapper file is not loaded in the target file.

2、 Configure the mapper file scanning path again in the pom.xml file

 <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>

In this way, you can successfully use custom SQL and SQL in basemapper.

Mysql ERROR 1067: Invalid default value for ‘date’ [How to Solve]

When adding fields to a table, I suddenly find that the default value of a field of date type is wrong, which is depressing~

After troubleshooting, it turns out that there is a problem with MySQL configuration. Under Wamp, SQL is not set in MySQL 5.7_ Mode.

1. Find [mysqld] in my.ini file

2. If there is no SQL_Mode, add it and modify it if necessary

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
or
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3.Restart MySQL;
use the following commands to operate mysql:
systemctl restart mysqld.service
systemctl start mysqld.service
systemctl stop mysqld.service

Canal synchronization error target column: name not matched

I. problem description

We have a usage scenario for canal:

Synchronize the same table data from multiple source ends to the same target end for unified data display.

However, it is found that after the field is deleted at source 1, the canal client logs of other sources will report an error:

Target column: name not matched
after that, the SQL operations of this table (such as insert, even if data is not inserted into the deleted field) cannot be synchronized.

II. How to avoid

In the scenario where multiple source ends are performing canal synchronization to the same target end, the drop field is prohibited.

[Solved] Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException

The following errors are reported using Mybatis.

Caused by: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is ‘com/kq/dao/UserMapper.xml’. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias ‘UserMap’. Cause: java.lang.ClassNotFoundException: Cannot find class: UserMap
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parseConfiguration(XMLConfigBuilder.java:122)
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parse(XMLConfigBuilder.java:99)
at org.apache.ibatis.session.SqlSessionFactoryBuilder.build(SqlSessionFactoryBuilder.java:78)
… 25 more
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is ‘com/kq/dao/UserMapper.xml’. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias ‘UserMap’. Cause: java.lang.ClassNotFoundException: Cannot find class: UserMap
at org.apache.ibatis.builder.xml.XMLMapperBuilder.configurationElement(XMLMapperBuilder.java:123)
at org.apache.ibatis.builder.xml.XMLMapperBuilder.parse(XMLMapperBuilder.java:95)
at org.apache.ibatis.builder.xml.XMLConfigBuilder.mapperElement(XMLConfigBuilder.java:377)
at org.apache.ibatis.builder.xml.XMLConfigBuilder.parseConfiguration(XMLConfigBuilder.java:120)
… 27 more
Caused by: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias ‘UserMap’. Cause: java.lang.ClassNotFoundException: Cannot find class: UserMap
at org.apache.ibatis.builder.BaseBuilder.resolveClass(BaseBuilder.java:118)
at org.apache.ibatis.builder.xml.XMLStatementBuilder.parseStatementNode(XMLStatementBuilder.java:102)
at org.apache.ibatis.builder.xml.XMLMapperBuilder.buildStatementFromContext(XMLMapperBuilder.java:138)
at org.apache.ibatis.builder.xml.XMLMapperBuilder.buildStatementFromContext(XMLMapperBuilder.java:131)
at org.apache.ibatis.builder.xml.XMLMapperBuilder.configurationElement(XMLMapperBuilder.java:121)
… 30 more
Caused by: org.apache.ibatis.type.TypeException: Could not resolve type alias ‘UserMap’. Cause: java.lang.ClassNotFoundException: Cannot find class: UserMap
at org.apache.ibatis.type.TypeAliasRegistry.resolveAlias(TypeAliasRegistry.java:120)
at org.apache.ibatis.builder.BaseBuilder.resolveAlias(BaseBuilder.java:149)
at org.apache.ibatis.builder.BaseBuilder.resolveClass(BaseBuilder.java:116)
… 34 more
Caused by: java.lang.ClassNotFoundException: Cannot find class: UserMap
at org.apache.ibatis.io.ClassLoaderWrapper.classForName(ClassLoaderWrapper.java:196)
at org.apache.ibatis.io.ClassLoaderWrapper.classForName(ClassLoaderWrapper.java:89)
at org.apache.ibatis.io.Resources.classForName(Resources.java:261)
at org.apache.ibatis.type.TypeAliasRegistry.resolveAlias(TypeAliasRegistry.java:116)
… 36 more

 

Solution:

1. first look at whether you have used the ResultMap to define the returned result set, if so, look at your configuration of the xmlSQL statement mapping the return set result type is resultType or resultMap type, if it is the resultType type, then it is right, change the resultType to resultMap type, it should be right. 2.
The reason is: when you use the custom resultMap for the returned result set, you did not change the SQL statement in the xml file of the resultType to resultMap, resulting in not finding the id of the result set of your custom resultMap, that is, Cause: java.lang. ClassNotFoundException: Cannot find class: <here is the id name of your custom resultMap>.

[Solved] PostgreSQL enumeration type usage error: operator does not exist error handling 

//Creating Enumeration Classes
CREATE TYPE USER_ROLE AS ENUM ('MALE', 'FEMALE');
//Add conversion rules
CREATE CAST (VARCHAR AS USER_ROLE) WITH INOUT AS IMPLICIT;
//Create table, add fields of enumeration type
create table sys_user
(
    row_id      bigserial          not null
        constraint sys_user_pkey primary key,
    create_time timestamp(6),
    update_time timestamp(6),
    del_flag    smallint default 0 not null,
    role        USER_ROLE      not null,
    user_name   varchar(200)       not null
);

Because the conversion rule is added, you can directly use the varchar type string as the judgment condition query in pgadmin, but if you use mybatis to query the database, the error operator does not exist will be reported

select * from sys_user where del_flag = 0 and role = 'MALE'

Solution: convert varchar type to enumeration type and compare

Method 1
select * from sys_user where del_flag = 0 and role = cast(#{role} as user_role)；
Method 2
select * from sys_user where del_flag = 0 and role = #{role}::user_role;

Error querying database.Cause:java.sql.SQLSyntaxErrorException:ORA-00911:invalid character

This problem is well positioned. At first glance, it is the problem of parameter transmission. Through this error report, the first thing to consider is the like part of the query condition.

  The main idea of this problem is 1. Special characters are passed, such as’ ‘  

                                  2. See if the like query uses #{} or ${}

                                  3. Is there a problem with where involving parameter statements

 

 

SAP SQL error “SQL code: -10692“ occurred while accessing table “ZTXXXX“.

report errors

resolvent

Note：SQL code: -10692 occurred while accessing table < TABLE_ NAME> Use the report cucastat to reset the statement cache for the instance.

The instance list can be viewed through transaction sm51. Execute the program cucastat in transaction se38; Alternatively, call transaction al12 and select monitor – & gt; cursor cache from the menu.

Select this server. Then select the reset button.

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax； check the

There is a problem when adding, deleting, modifying and querying the interface of a table, as shown in figure

  Error codes are as follows:

Cause: java.sql.SQLSyntaxErrorException: 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 'DESCRIBE , CREATE_TIME,
    CREATED, UPDATE_TIME, UPDATED
   
    from renew_rul' at line 3
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 'DESCRIBE , CREATE_TIME,
    CREATED, UPDATE_TIME, UPDATED
   
    from renew_rul' at line 3

Prompt me that my SQL syntax is abnormal. I have searched the Internet for a long time, but I haven’t found a similar problem. I specially record it,

The database used is MySQL and the database graphical interface Navicat.

 

The reason for the problem is that description is a keyword, which needs further processing in mybatis, but it’s strange that status should also be a keyword. Why doesn’t it report an error?

Solution: change the description field to des so that it can be compiled.