Tag Archives: SQL

[Solved] Mybatis Error: Could not find resource mybatis-conf.xml

Mybatis reports an error: could not find resource mybatis-conf.xml

Screenshot of error reporting:

Error content:

java.io.IOException: Could not find resource mybatis-conf.xml at org.apache.ibatis.io.Resources.getResourceAsStream(Resources.java:114) at org.apache.ibatis.io.Resources.getResourceAsStream(Resources.java:100) at org.apache.ibatis.io.Resources.getResourceAsReader(Resources.java:160) at MybatisAdvancedTest.testQueryByNo(MybatisAdvancedTest.java:23)

The default solution is that his article is compiled in the ide-src-jebat.xml directory, which is not posted on my ide-jebat When the XML file is in the resources directory, debug goes in to watch the process and finds resources Getresourceasreader is not loaded into this XML configuration file at all

Code diagram:

// Query a student based on their school number @Test public void testQueryByNo() throws IOException { String resource = "mybatis-conf.xml"; Reader reader = Resources. getResourceAsReader(resource); SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader); SqlSession session = sessionFactory.openSession(); // pass in the StudentMapper interface, return the mapper proxy object studentMapper studentMapper = session.getMapper( StudentMapper.class);//interface //pass the mapper proxy object studentMapper to call the methods in the ISTudentMapper interface Student student = studentMapper.queryStudentByNo(1); System.out.println(student+"****"); session.close(); }

Solution 1:

Replace this part of the code in the test method above:

String resource = "mybatis-conf.xml"; Reader reader = Resources.getResourceAsReader(resource);

Replace with

Reader reader = Resources.getResourceAsReader("mybatis-conf.xml");

If the xml is not compiled properly by idea, such as IDEA development, but not mybatis-config.xml in the resources directory, for example, in src/main/java, then add the build code at the end of pom.xml to tell idea to compile our configuration file:

Solution 2:

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

Solution 3:

Move the configuration file to the resources directory and rebuild our project

[Solved] Oracle Delete the Archive Error: RMAN-08137

Rman-08137 reports an error when Oracle deletes the archive.

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938000_1004292720.dbf thread=1 sequence=938000
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938001_1004292720.dbf thread=1 sequence=938001
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938002_1004292720.dbf thread=1 sequence=938002
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938003_1004292720.dbf thread=1 sequence=938003
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938004_1004292720.dbf thread=1 sequence=938004
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938005_1004292720.dbf thread=1 sequence=938005
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938006_1004292720.dbf thread=1 sequence=938006
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938007_1004292720.dbf thread=1 sequence=938007

The error message shows that the archive to be deleted cannot be deleted because the backup database still needs to be deleted. Check the archive number applied to the backup database

SQL> select open_mode,database_role from v$database;

OPEN_MODE                           DATABASE_ROLE
----------------------------------- ------------------------------------------------
READ ONLY WITH APPLY                PHYSICAL STANDBY

SQL> select process,sequence# from v$managed_standby;

PROCESS                      SEQUENCE#
--------------------------- ----------
DGRD                                 0
ARCH                            939246
DGRD                                 0
ARCH                            939252
ARCH                            939248
ARCH                            939253
RFS                                  0
MRP0                            939254
DGRD                                 0
RFS                                  0
RFS                                  0

Deleting 938000 from the main database shows that the standby database still needs to be, and the standby database query has been applied to the 939254 archive
query the status of the standby database on the primary database:

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
READ WRITE                                    PRIMARY

SQL> select dest_name,PROTECTION_MODE,GAP_STATUS,APPLIED_THREAD#,APPLIED_SEQ# from gV$ARCHIVE_DEST_STATUS where type='PHYSICAL';

DEST_NAME                 PROTECTION_MODE                                              GAP_STATUS                APPLIED_THREAD# APPLIED_SEQ#
------------------------- ------------------------------------------------------------ ------------------------- --------------- ------------
LOG_ARCHIVE_DEST_2        MAXIMUM PERFORMANCE                                          RESOLVABLE GAP                          1       939258

The main library shows that the backup library has GAP and the status is RESOLVABLE_GAP
Through the related information query, the solution is:

  1. Mount the primary database.

    Issue the following SQL statement at the primary database:
    SQL> ALTER SYSTEM FLUSH REDO TO <target_db_name>;
    target_db_name is the db_unique_name of the backup database

    View the backup db_unique_name

SQL> select open_mode,database_role from v$database;

OPEN_MODE                           DATABASE_ROLE
----------------------------------- ------------------------------------------------
READ ONLY WITH APPLY                PHYSICAL STANDBY

SQL> show parameter db_unique_name

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_unique_name                       string                            standby

Discuss the downtime with the business and restart the production database to mount status

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
MOUNTED                                          PRIMARY

SQL> ALTER SYSTEM FLUSH REDO TO standby;

System alted.

SQL> alter database open;
 
 Database alted.

Query gap status of Zhu Bei database on the main database

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
READ WRITE                                    PRIMARY

SQL> select dest_name,PROTECTION_MODE,GAP_STATUS,APPLIED_THREAD#,APPLIED_SEQ# from gV$ARCHIVE_DEST_STATUS where type='PHYSICAL';

DEST_NAME                 PROTECTION_MODE                                              GAP_STATUS                APPLIED_THREAD# APPLIED_SEQ#
------------------------- ------------------------------------------------------------ ------------------------- --------------- ------------
LOG_ARCHIVE_DEST_2        MAXIMUM PERFORMANCE                  NO GAP                          1       939258

GAP_STATUS is now NO GAP, continue to execute the archive delete command, the deletion is normal

Oracle18c Error: ORA-12012: error on auto execute of job

For the newly installed Oracle 18C database, the alert log keeps making errors:

ORA-12012: error on auto execute of job

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_222"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 49538
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763
ORA-06512: at "SYS.DBMS_STATS", line 49526
2022-02-28 01:27:20.762000 +08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_104148.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_224"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 49538
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763
ORA-06512: at "SYS.DBMS_STATS", line 49526
2022-02-28 01:37:21.758000 +08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_104738.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_226"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 49538
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763
ORA-06512: at "SYS.DBMS_STATS", line 49526

 

Solution:
Call the initialization package manually: go to sqlplus as administrator
1. sqlplus / as sysdba

2. check the current auto task belongs to the user already exists, if not then initialize the package

3. EXEC dbms_stats.init_package();

4. Confirm again

SQL> column name format A35
SQL> set linesize 120
SQL> select name, ctime, how_created from sys.wri$_adv_tasks where owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

no rows selected

SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

SQL> select name, ctime, how_created from sys.wri$_adv_tasks where owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                                CTIME     HOW_CREATED
----------------------------------- --------- ------------------------------
AUTO_STATS_ADVISOR_TASK             28-FEB-22 CMD
INDIVIDUAL_STATS_ADVISOR_TASK       28-FEB-22 CMD

[Solved] Mysql Close safe-updates Mode Error: Error Code: 1175

When this problem occurs in MySQL database:

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.

MySQL has entered the safe mode, which is the safe updates mode

Solution:


SHOW VARIABLES like 'sql_safe_updates';  # Query the status of the current safe-updates mode
SET sql_safe_updates=0; # Turn off safe-updates mode

ORA-04088: error during execution of trigger [How to Solve]

ORA-00001: unique constraint XXXXXXX violated
ORA-06512: at “XXXXXXXXX”, line 5
ORA-04088: error during execution of trigger ‘TRRIGER_ NAME’

This is mostly because trrigger is in use. This error will be reported if it performs merge into operation or inserts or updates the target table.

Solution: close trrigger and execute merge into, insert and update statements.

alter trigger trriger_name disable;

After executing the statement, turn on trrigger.

alter trigger trriger_name enable;

Disadvantages:

Data loss may occur between disable and enable.

[Solved] Mybatis insert Error: Cause: java.sql.SQLException: SQL String cannot be empty

Mybatis insert error cause: Java sql. SQLException: SQL String cannot be empty

1. Error description

Scenario reproduction: when using mybatis to import a list for batch insertion, the code is as follows:

mapper

void insertTest(List<Test> list);

mapper.xml

<insert id="insertTest" parameterType="java.util.List">
	<if test="list != null and list.size() > 0"> 
		INSERT INTO test (test1, test2)
                VALUES
                <foreach collection="list" index="index" item="item" separator=",">
                    (#{item.test1}, #{item.test2})
                </foreach>
	</if>
</insert>

The reason for the error is that the list is passed in The list with size () 0 causes the SQL statement to be empty and an error is reported

Solution:

1. Make non empty judgment (list! = null &&! List. Isempty()) before using mapper, and set mapper If statement removal in XML

2. Use the choose, when and otherwise tags to judge. If it is empty, give a statement to query the empty string

Specific examples are as follows

	<insert id="insertTest" parameterType="java.util.List">
        <choose>
            <when test="list != null and list.size() > 0">
                INSERT INTO test (test1, test2)
                VALUES
                <foreach collection="list" index="index" item="item" separator=",">
                    (#{item.test1}, #{item.test2})
                </foreach>
            </when>
            <otherwise>
                select ""
            </otherwise>
        </choose>

    </insert>

If the scenario needs to implement the insert statement multiple times, it will not be elegant to judge the space multiple times in the code. You can consider using the following solutions for reference only. If there are better methods, you can exchange and discuss them

How to Solve Pycharm SQL Union Error

Question

Today, when writing MySQL code in pycharm, I always encounter errors when using Union. Although the syntax itself is correct, the red horizontal line is annoying

It’s just that you have to change union to union all to eliminate the error report.

Press setting -> editor-> Inspections, then find no data sources configured under the SQL on the right, and cancel all related to the red exclamation mark, but it is still fruitless.

However, inspired by the article, the reason for the problem is found in the location of the configuration database

Solution:

The configuration database is in file -> Settings -> Languages & Frameworks -> SQL conversations
the reason for the problem is that I configured the database before. At that time, Clickhouse was configured, which is different from MySQL in the usage of union,
so just change Clickhouse to MySQL.

before change

modified

This will return to normal~

MYSQL Create TIMESTAMP and Save Error: ERROR 1067 (42000): Invalid default value for ‘last_updated_on’

ERROR 1067 (42000): Invalid default value for ‘last_updated_on’

Solution:
Remove the values: NO_ZERO_IN_DATE,NO_ZERO_DATE in sql_mode.

show variables like 'sql_mode';

Outcome:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Modify sql_mode:
set session
sql_mode=’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Execute successfully!

Flink SQL contains aggregation operators Error: you cannot print directly

Cannot print directly when Flink SQL contains aggregation operators

Exception in thread "main" org.apache.flink.table.api.TableException: AppendStreamTableSink doesn't support consuming update and delete changes which is produced by node Rank(strategy=[UndefinedStrategy], rankType=[ROW_NUMBER], rankRange=[rankStart=1, rankEnd=1], partitionBy=[category], orderBy=[sales DESC], select=[category, sales])
    at org.apache.flink.table.planner.plan.optimize.program.FlinkChangelogModeInferenceProgram$SatisfyModifyKindSetTraitVisitor.createNewNode(FlinkChangelogModeInferenceProgram.scala:355)
    at org.apache.flink.table.planner.plan.optimize.program.FlinkChangelogModeInferenceProgram$SatisfyModifyKindSetTrai 

reason:

In normal circumstances, toappendstream is used by default for table stream conversion, while the aggregation operation involves delete operation, which can not be satisfied by appendstream alone. Therefore, retractstream or upsertstream are considered.

Solution:

Use tableenvironment Toretractstream() for output
for example:

Table table = tEnv.sqlQuery(
                        "SELECT\n" +
                        "    userName,\n" +
                        "    product,\n" +
                        "    amount\n" +
                        "FROM\n" +
                        "    orders,\n" +
                        "    user_table\n" +
                        "WHERE\n" +
                        "    orders.userId = user_table.userId");
Table table = tEnv.sqlQuery("SELECT userId,sum(amount) as boughtSum " +
                            "FROM orders group by userId");
tEnv.toRetractStream(table, TypeInformation.of(new TypeHint<Tuple2<String, Integer>>() {
})).print();

[Solved] ERROR #42601 syntax error at or near “)“

 

Background

orm:go-pg

Execute whereIn error: Error #42601} syntax} error at} or “near”)

As shown in the figure

The query is as follows:

err = db.Model(&model.Abc{}).WhereIn(“id in (?)”, ids).Column(“id”).Column(“name”).Select(&records)

If IDS is an empty slice, this error will be reported at this time

Solution:

The slices passed in should not be empty. From the business point of view, the length of the list of condition ids to be checked is 0 then the results must not be checked, so you can make a judgment in the outer layer, ids length is 0 then do not take the sql query can be.

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

org.apache.ibatis.exceptions.PersistenceException: 
### Error building SqlSession.
### The error may exist in mappers/user.xml
### Cause: org.apache.ibatis.builder.BuilderException: Error parsing SQL Mapper Configuration. Cause: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is 'mappers/user.xml'. Cause: org.apache.ibatis.builder.BuilderException: Error resolving class. Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias ''.  Cause: java.lang.ClassNotFoundException: Cannot find class: 

This error occurs in the mybatis test for many reasons,
1: it may be the header information error of the mapping file. Correct:
2: there may also be an error in the label of the mapping file. Most of the errors reported in the mapping file are code errors. Just check the code
3: I made an error today because I wrote an extra tag, but the content in the tag was not written

4: The same type of error I encountered yesterday is because I wrote a comment in the where tag

<select>
	<where>
		Write comment here. This error has been encountered twice, as soon as a comment is written inside a tag, an error is reported
	</where>
</select>

These are the mistakes and solutions I met in learning mybatis.