Tag Archives: SQL

Clickhouse error: XXXX.XXXX_local20211009 (8fdb18e9-bb4c-42d8-8fdb-18e9bb4c02d8): auto…

Code: 49, e.displayText() = DB::Exception: Part 20211009_67706_67706_0 is covered by 20211009_67118_67714_12 but should be merged into 20211009_67706_67715_1. This shouldn’t happen often., Stack trace (when copying this message, always include the lines below):
Error Messages:
XXXX.XXXX_local20211009 (8fdb18e9-bb4c-42d8-8fdb-18e9bb4c02d8): auto DB::StorageReplicatedMergeTree::processQueueEntry(ReplicatedMergeTreeQueue::SelectedEntryPtr)::(anonymous class)::operator()(DB::StorageReplicatedMergeTree::LogEntryPtr &) const: Code: 49, e.displayText() = DB::Exception: Part 20211009_67706_67706_0 is covered by 20211009_67118_67714_12 but should be merged into 20211009_67706_67715_1. This shouldn’t happen often., Stack trace (when copying this message, always include the lines below):
Solution 1.
1. Try to delete the local table and the distributed table XXXX.XXXX_local20211009

Mapper.xml Error: Error setting non null for parameter #3 with JdbcType null.

Record the problems encountered in learning SSM framework 3
Exception Reporting
Message Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property=‘id’, mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId=‘null’, jdbcTypeName=‘null’, expression=‘null’}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
Description The server encountered an unexpected condition that prevented it from completing the request.
The problem occurs mainly in the mapper.xml file and can be due to several reasons.
1. two #’s are written together and will be identified incorrectly (my error)

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set>
            <include refid="News_update"/>
        </set>
        where id=##{id}
    </update>

2. JavaEE comments appear inside/**/

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set> /*  */
            <include refid="News_update"/>
        </set>
        where id=#{id}
    </update>

3. #{id} covered with quotation marks

<update id="updateById" parameterType="main.java.com.dy.domain.News">
        update news
        <set>
            <include refid="News_update"/>
        </set>
        where id='#{id}'
    </update>

All the above detailed errors may lead to errors in the execution of SQL statements, so we should be as careful as possible in the future development process.

Error creating bean with name ‘dataSourceScriptDatabaseInitializer‘ defined in class path resource [

Error creating bean with name ‘dataSourceScriptDatabaseInitializer’ defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Unsatisfied dependency expressed through method ‘dataSourceScriptDatabaseInitializer’ parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘dataSource’ defined in class path resource

Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Unsatisfied dependency expressed through method 'dataSourceScriptDatabaseInitializer' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource 

The reason for my error is that I made an error when using @springboottest automatic injection when I first learned spring data JPA

@SpringBootTest

//springbootTest The server is not turned on by default,

public class MainTest {
    @Autowired
UserRepository userRepository;

@Test
    void t1(){
    System.out.println(userRepository);
}
}

I searched the Internet for several hours and debugged it. I found that the datasource was always empty, because it was tested and run in the test method at that time, and the errors given were always the same as above. However, I clearly configured the database in application.yml, and later found it during overall debugging, Failed to load driver class com.mysql.cj.jdbc.driver in either of hikariconfig class loader or thread context classloader

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

Of course, it may also be due to other errors. If you also encounter this error, you can see whether you have added MySQL dependencies to your dependencies

SQL editor and reconnect [an exception when MySQL (workbench) updates data] [error code: 1175]

Abnormal conditions:

  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 -> Query Editor and reconnect.

When using the secure update mode, the update statement must use a keyword column attribute to limit the scope of the update. If you do not use keyword columns to limit the scope or update all records, you cannot use safe update mode.

The solution is to execute the following statement to set it to non secure update mode.

#Non-secure mode
SET SQL_SAFE_UPDATES=0;
#secure mode
SET SQL_SAFE_UPDATES=1;

Bulk Update Error: #Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the m

Error:
mybatis plus foreach batch insert is OK. Change it to update and keep reporting errors. It is OK to copy the SQL and execute it separately.
reason:
the insert statement supports batch and can be written in one statement:

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...), (value1,value2,value3,...), (value1,value2,value3,...), (value1,value2,value3,...), (value1,value2,value3,...),(value1,value2,value3,...);

Update does not support batch. There are multiple statements corresponding to cyclic batch

update table set c1=v1;
update table set c1=v2;
update table set c1=v3;
.
.
.
update table set c1=v...;

Mybatis does not support executing multiple statements (multiple semicolons) by default
solution:
add the allowmultiqueries = true parameter to the database connection

Allowmultiqueries = true function:
1. You can carry semicolons after SQL statements to realize multi statement execution
2. You can execute batch processing and issue multiple SQL statements at the same time

[Solved] Pyodbc.ProgrammingError: No results. Previous SQL was not a query.

Call the stored procedure on the remote sqlserver server with Python. Code fragment:


    conn = pyodbc.connect(SERVER=host, UID=user, PWD=password, DATABASE=dbname,
                            DRIVER=driver)
    cur = conn.cursor()
    if not cur:
        raise (NameError, 'Database connection error)
    else:
        cur.execute("EXEC GetLastData")
        resList = list()
        resList = cur.fetchall()

Execution error:

pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

After checking, the stored procedure can be executed normally in the sqlserver environment. It seems that there is a problem when calling pyodbc. A similar problem is found on stackoverflow. The answer is as follows:
the problem was solved by adding set NOCOUNT on; to the beginning of the anonymous code block. That statement suppresses the record count values generated by DML statements like UPDATE … and allows the result set to be retrieved directly.

The problem is solved by adding it to the beginning of the anonymous code block. This statement suppresses the record count value generated by the DML statement and allows the set result to be retrieved directly. SET NOCOUNT ON; UPDATE …

So add a sentence set NOCOUNT on to the stored procedure

CREATE proc [dbo].[GetLastData]
AS
BEGIN

SET NOCOUNT ON

declare @begindate datetime,@enddate datetime
select @begindate=CONVERT(varchar(7),GETDATE(),120)+'-01'
select @enddate=DATEADD(MONTH,1,@begindate)

nested exception is org.apache.ibatis.builder.BuilderException: Error evaluating expression [Solved]

This exception is usually a problem with dynamic SQL. Find the corresponding SQL and check the dynamic SQL syntax according to the following prompt information.

Problem description

Exception information:
needed exception is org.apache.ibatis.builder.builderexception: error evaluating expression 'ides'. Return value (806) was not Iterable.

According to the exception prompt information, find the dynamic SQL statement where ides is located.

<foreach  collection="ides"  index="index" item="ides" open="(" separator="," close=")">
     #{ides}
</foreach>
...
<foreach  collection="ides"  index="index" item="ides" open="(" separator="," close=")">
     #{ides}
</foreach>

Finally, it is found that
two <foreach></ foreach> Statement operates on the same item variable, resulting in the failure of dynamic SQL splicing of the latter.

Solution:

Change the item property in any statement to a different value.

<foreach  collection="ides"  index="index" item="idess" open="(" separator="," close=")">
     #{idess}
</foreach>
...
<foreach  collection="ides"  index="index" item="ides" open="(" separator="," close=")">
     #{ides}
</foreach>

### The error may involve defaultParameterMap ### The error occurred while setting parameters

I encountered an error today. Running rankdao. Selectbyid (ID) directly reported an error to me. I didn’t understand it

Error message:

2021-09-22 10:40:58.824 ERROR 8364 --- [nio-8888-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  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 'rank WHERE id=1' at line 1
### The error may exist in com/brilliantZC/music/dao/MusicRankDao.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=?
### 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 'rank WHERE id=1' at line 1
; 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 'rank WHERE id=1' at line 1] with root cause

At the beginning, Baidu has various methods, some say

    an error is reported when the value of the column in the created table is less than the actual value. The @ resource or @ Autowired annotation is forgotten on the service interface introduced by the controller layer. The method called by the controller layer is private, resulting in distribution failure. Check whether the mapper interface code conforms to the specification (automatically ignored by mybatis plus)

    I tried again, but I still couldn’t, so I took the wrong SQL statement and executed it

    SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=1
    

    Still report an error

    music> SELECT id,song_list_id,consumer_id,score FROM rank WHERE id=1
    [2021-09-22 10:55:58] [42000][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 'rank WHERE id=1' at line 1
    

    Then I was confused. Then I removed the query statement and still reported an error

    SELECT id,song_list_id,consumer_id,score FROM rank
    

    Then Baidu saw that because rank is a keyword, it can not be directly used as a non keyword. It needs to be used with `. It can be executed after replacement

    SELECT id,song_list_id,consumer_id,score FROM `rank`
    

    Then I modified the table name and solved the error!!!

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