Category Archives: MySQL

[How to Fix]Mybatisplus ignores mapped fields

In the development, we may encounter that mybatisplus uses entity class attribute for SQL operation. The entity has this attribute, but the database table does not have this field (that is, entity class attribute is not a database table field). If you don’t handle it, you will report an error.

FIRE

@TableName: Database Table Related

@TableId: table primary key identifier

@TableField: table field identifier

@TableLogic: Table field logical processing annotation (logical deletion

)
Solution

@TableField(exists = false): indicates that the property is not a database table field, but is required to be used.
 
@TableField(exists = true): indicates that the property is a database table field.

After adding this annotation to the attributes of the entity class, this field will not map to the database.

   @TableField(exist = false)
    private String deptName;

The attributes of Oracle batch modification sequence (such as cache)_size, increment_(by et al.)

Oracle self-growing columns can be set as sequences, such as self-growing IDs.

Create a sequence

--Create Sequence dept_deptid_seq
create sequence dept_deptid_seq
    increment by 10
    start with 1
    maxvalue 9990
    nocycle
    nocache

--Create default sequence no_no_seq
create sequence no_no_seq

Sequence (start with n) as the value of the first sequence, the sequence value (increment by n) for each of the increase, the maximum sequence as the maxvalue (n), the sequence of the minimum value for (minvalue n)
cycle said if the sequence of maximum value is greater than the start from the minimum cycle (the default is not cycle), database cache n said pre-allocated n values stored in memory (the default) 20
The query sequence
Note: After a sequence has been created, the value of the current sequence cannot be directly queryed.nextVal must be used to query the value of the next sequence before using currVal to query the value of the current sequence.

select sequence_name ,last_number, min_value,max_value,increment_by,cache_size from user_sequences

Modify the sequence

alter sequence sequence_name increment by 1 cache 3000

Batch modification sequence

--bulk change sequence
declare
  v_sql varchar2(2000);
  CURSOR seqs IS select sequence_name from user_sequences where user_sequences.CACHE_SIZE<=20  and user_sequences.LAST_NUMBER>10000;
begin
  FOR seq IN seqs LOOP
    v_sql:='alter sequence '||seq.sequence_name
           ||' increment by 1 cache 3000';
    dbms_output.put_line(v_sql);
    execute immediate v_sql;
  end loop;
end;

The cursor places the sequence to be modified. Loop out the sequence name and modify it.

The setobject() method reports an error. The parameter index out of range and MySQL syntax error exception report an error

SetObject() method, error Parameter index out of range and com. The MySQL. JDBC. Exceptions. Jdbc4. MySQLSyntaxErrorException error

Learn how to use the setObject() method in PreparedStatement for placeholders in SQL procedures. , appear when to replace the Parameter index out of range error:

1, the number of placeholders, and transfer the Object [] array element number
2, different placeholders used in Chinese?Rather than?

If the above situation is modified, the com. Mysql. JDBC. Exceptions. Jdbc4. MySQLSyntaxErrorException error
see first in the use of setObject () method before and after the difference between a PreparedStatement object:

Object[] objects = new Object[]{"name","age","xixi","23"};
sql = "insert into student (?,?) values(?,?);";
//Get the execution object (connection as Connection object)
preparedStatement = connection.preparedStatement(sql);
System.out.println(preparedStatement.toString());
// determine if the parameter exists, and replace it
if (obs!=null&&obs.length>0){
	for (int i = 0; i < obs.length; i++) {
		preparedStatement.setObject(i+1,obs[i]);
	}
}
System.out.println(preparedStatement.toString());

Perform before:
com. Mysql. JDBC. JDBC42PreparedStatement @ 443 b7951:
insert into student (* * NOT SPECIFIED, the NOT SPECIFIED) values (NOT SPECIFIED, the NOT SPECIFIED * *);

com after execution. Mysql. JDBC. JDBC42PreparedStatement @ 443 b7951:
insert into student (” name “, “age”) values (‘ hee hee ‘, ’23’);
All ** Not SPECIFIED ** placeholders are replaced by elements in the Objects[] array with single quotes.
in the mysql database, the parameters need to add single quotation marks, it is enclosed within a string value. Column names, table names, and so on use backquotes to distinguish them from special keywords (the symbol to the left of the 1 digit).
So if you want to replace non-attribute values, you should choose other methods such as concatenation SQL statements to prevent errors.

[How to Fix] Database can’t open file (errno:24)

Error: Unable to open database files while the database is running.
One, the problem report error two, the solution idea three, the treatment method

First, the problem report error
Can’t open file (errno:24)
Second, the solution
First we need to look at the error log of the database. Then determine whether the table is corrupt or a permission problem. There may also be insufficient disk space to properly access the table; Operating system limitations are also a concern; Use the perl error tool to see specific errors!

linux:/usr/local/mysql/bin # ./perror 24 
OS error code  24:  Too many open files 

Maximum number of open files exceeded! The maximum number of open files on the system is 65535. It cannot be exceeded! The maximum number of open files in the database must be out of limit! Show variables like ‘open_files_limit’; MySQL> reset MySQL = 2048; MySQL> reset MySQL = 2048;
Three, the treatment method

repair table ;

chown mysql Permissions

Clean up junk data from the disk

SQL Server Group sort de duplication row_ number() over ( PARTITION BY t1.col_ 2 ORDER BY 1 )

Recall SQLServer group sort once to reanalyze

row_number() over ( PARTITION BY t1.col_2 ORDER BY 1 )

-- Code
DELETE FROM table_name t 
WHERE t.rowid IN (
	SELECT rid
	FROM(
		SELECT t1.rowid rid,row_number() over ( PARTITION BY t1.col_2 ORDER BY 1 ) rn 
		FROM table_name t1 
	) t1 
	WHERE t1.rn > 1 
);

-- PARTITION BY t1.col_2
-- The first grouping is based on the second column

-- ORDER BY 1
-- then sort by the first column

-- row_number()
-- reassign row numbers to the grouped sorted data
-- similar to a grouped set of numbers
-- for example, the first two steps are divided into three groups
-- row_number() will renumber the first group from 1, 1.2.3.4 ....
-- the second group will still be numbered from 1, 1.2.3.4 ....
-- and so on down the line

-- WHERE t1.rn > 1
-- Filter the grouped sorted data to filter out all rows numbered greater than 1 (i.e. duplicate rows)
-- keep only the first record, filter all others greater than 1 and then use t.rowid IN () to delete them to achieve de-duplication

How to Check the most CPU consuming 50 queries in MYSQL

--Querying the 50 most CPU-intensive queries
SELECT TOP 50 
DB_NAME(dbid) AS DBNAME,
OBJECT_NAME(objectid,dbid) as OBJECTNAME,
total_worker_time/execution_count/1000/1000 AS [CPU Average execution (sec)],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) N'Execution Statements'
         ,st.text N'Full Words'
         ,total_worker_time/1000/1000 AS [Total CPU time consumed (seconds)]
         ,execution_count [number of runs]
 ,qs.total_worker_time/qs.execution_count/1000/1000 AS [average_execution_time_of_CPU(sec)]
 ,last_execution_time AS [last execution time]
 ,max_worker_time /1000/1000 AS [max_execution_time(sec)]
 ,total_physical_reads N 'total_physical_reads'
 ,total_logical_reads/execution_count N'number of logical reads per execution'
 ,total_logical_reads N'total_logical_reads'
 ,total_logical_writes N'total_logical_writes'
 ,*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE last_execution_time> dateadd(minute,-100,getdate())
ORDER BY total_worker_time/execution_count DESC;
GO

Tkmybatis uses Example.SetOrderByClause Use for sorting and multiple sort fields

How to use it when sorting individual fields

example.setOrderByClause("user_type ASC"); //Output sorted by user type in ascending order

Running SQL looks something like this

SELECT ...
FROM tab_sy_user
order by user_type ASC 

The method of using when sorting multiple fields separated by a comma

The query results are sorted by the order in which the fields are sorted, and if the conditions are the same, continue the conditional sort by the next field until the last field sort is complete.

 //First according to user_type ascending ASC sorting, if there is the same condition, in accordance with the level condition descending DESC sorting
example.setOrderByClause("user_type ASC, level DESC");

Running SQL looks something like this

SELECT ...
FROM tab_sy_user
order by user_type ASC, level DESC

MySQL automatically creates partitions through events

Automatic execution using mysql event timing

BEGIN
	set @table_name = concat('p', DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y%m%d'));
	set @max_value  = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y%m%d');
	set @excutesql  = CONCAT('ALTER TABLE TITLE ADD PARTITION (PARTITION ',@table_name,' VALUES LESS THAN ("',@max_value,'"))');
	SELECT @excutesql;
	PREPARE stmt from @excutesql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END

How To Change ASM SYS PASSWORD

To change the ASM SYS Password
Things tried:
SQL> password
Changing password for SYS
Old password:
New password:
Retype new password:
ERROR:
ORA-00600: internal error code, arguments: [15051], [], [], [], [], [], [], []

SQL> select INSTANCE_NAME from v$instance;

INSTANCE_NAME
—————-
+ASM

SQL> ALTER USER sys IDENTIFIED BY <new_password> REPLACE <old_password>;
ALTER USER sys IDENTIFIED BY <new_password> REPLACE <old_password>
*
ERROR at line 1:
ORA-01109: database not open

The following error also might occur:
SQL> alter user sys identified by ;
alter user sys identified by
*
ERROR at line 1:
ORA-01031: insufficient privileges

Solution
We can not change the password for ASM databases via alter user command.
The password should be the one provided when the password file was created,also REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE on all instances.

If you want to change the password then you would need to recreate the password file using the orapwd utility
Recreate the password file for the ASM instance as follows:
1.   Set the ORACLE_HOME and ORACLE_SID to the ASM instance
2.  connect /as sysdba from sqlplus
3.  If the value of the “remote_login_passwordfile” parameter in the pfile or spfile is EXCLUSIVE, you must shutdown your instance
4.  RENAME or DELETE the existing password file PWD<SID>. ora( In Windows) / orapw<SID> ( in UNIX)
5.  Issue the command:
WINDOWS:
orapwd file=<ORACLE_HOME>/database/PWD<SID>. ora password=<sys_password>

UNIX:
orapwd file=<ORACLE_HOME>/dbs/PWD<SID> password=<sys_password>

The passwordfile can be recreated for ASM while ASM instance is up. Usually for normal DB instances, we recommended that DB instances be shutdown before changing the passwordfile.