1. Find XXX user and find that it does not exist
select user,host from mysql. user;
The XXX user who created the view before has been deleted
2. Find these views and modify the definer information
select concat(“alter_definer=`root`@`%` view “, table_name, ” as “, view_definition, “;”)
from information_ schema. views
where table_schema='< Your database name> ‘;
The above SQL will generate a statement to modify the definer, which can be executed
Get the time value from the database and report an error: Java sql. Timestamp cannot be cast to java. lang.Long
1. Problem description
Convert the queried timestamp type data in the database into long type and report an error.
String type = result.getClass().getName();
if ("java.sql.Timstamp".equalsIgnoreCase(type)) {
return new Date((Long) result);
}
2. Solution
Because Java sql.Timestamp is Java util. Subclass of date;
So, just put Java sql.Timestamp to Java util.Date type is enough.
String type = result.getClass().getName();
if ("java.sql.Timestamp".equalsIgnoreCase(type)) {
return (Date)result;
}
Or convert the data to string type output:
String type = result.getClass().getName();
// Convert Timestamp type to String type (yyyy-MM-dd HH:mm:ss)
if ("java.sql.Timestamp".equalsIgnoreCase(type)) {
//java.sql.Timestamp processing logic
return DateUtil.timeToYmdHmsString((Date)result);
}
The dateutil tool class is as follows:
public class DateUtil {
private static String defaultYmdHmsPattern = "yyyy-MM-dd HH:mm:ss";
/**
* Convert Date to String, format: yyyy-MM-dd HH:mm:ss
* @param date date type
* @return String Date format string
*/
public static String timeToYmdHmsString(Date date) {
SimpleDateFormat formatter = new SimpleDateFormat(defaultYmdHmsPattern);
return formatter.format(date);
}
}
Recently, I imported a library and found that the function reported an error. At first glance, it was a view error. At first glance, Navicat reported an error directly. The user specified as a definer (‘root’@’%’) does not exist。
Solution:
Because the person who created the view
Solution 1
If you only have the current user and you do not have the password of the root user, it is recommended that you delete the current view, copy the statement and re-establish it.
Solution 2
If you have power user privileges, enter commands
grant all privileges on *.* to root@"%" identified by "."; flush privileges;
Replace root with the user you want to authorize.
Add one thing
For the security of the view, please supplement.
definer
When it is defined as a determiner, the user specified by the determiner must exist in the database and have the corresponding operation permission before it can be executed successfully. It has nothing to do with whether the current user has permission.
invoker
When it is defined as invoker, it can be executed successfully as long as the executor has execution permission.
[Err] 1071 – Specified key was too long; max key length is 767 bytes
This will appear on mysq5 7 or below because InnoDB is not enabled_large_prefix
MySQL 5.6：
MySQL 5.7：
Simple method: if the requirement is not very high, you can also directly set the table field varchar (255) to varchar (64)
The other is to modify the configuration (the service will become invalid after restarting)
mysql> show variables like '%innodb_large_prefix%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | OFF | +---------------------+-------+ 1 row in set mysql> set global innodb_large_prefix=on; Query OK, 0 rows affected mysql> show variables like '%innodb_large_prefix%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | ON | +---------------------+-------+ 1 row in set
######################
Background:
After importing the data of MySQL version 5.5 into MySQL 5.7, mysql The user table is broken
There is a problem: MySQL My watch is broken
[email protected] ((none)) > grant all privileges on *.* to 'tmp'@'localhost' identified by 'tmp' with grant option; ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted Mon Dec 13 17:31:40 2021
Solution: upgrade mysql_upgrade
[[email protected]]$ ./bin/mysql_upgrade -utmp -ptmp -h10.10.10.10 -P3307 mysql_upgrade: [Warning] Using a password on the command line interface can be insecure. Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.general_log OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK The sys schema is already up to date (version 1.5.1). Found 0 sys functions, but expected 22. Re-installing the sys schema. Upgrading the sys schema. Checking databases. dba_metadata.cluster OK dba_metadata.karma OK galaxy_availability_tjwq.EMQ_AVAILABILITY_DAY OK galaxy_availability_tjwq.EMQ_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.EMQ_FUSION_AVAILABILITY_DAY OK galaxy_availability_tjwq.EMQ_FUSION_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.EMR_AVAILABILITY_DAY OK galaxy_availability_tjwq.EMR_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.EMR_FUSION_AVAILABILITY_DAY OK galaxy_availability_tjwq.EMR_FUSION_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.FDS_AVAILABILITY_DAY OK galaxy_availability_tjwq.FDS_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.FDS_BACKUP_AVAILABILITY_DAY OK galaxy_availability_tjwq.FDS_BACKUP_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.FDS_FUSION_AVAILABILITY_DAY OK galaxy_availability_tjwq.FDS_FUSION_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.FDS_FUSION_BACKUP_AVAILABILITY_DAY OK galaxy_availability_tjwq.FDS_FUSION_BACKUP_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.SDS_AVAILABILITY_DAY OK galaxy_availability_tjwq.SDS_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.SDS_FUSION_AVAILABILITY_DAY OK galaxy_availability_tjwq.SDS_FUSION_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.SDS_FUSION_S0_AVAILABILITY_DAY OK galaxy_availability_tjwq.SDS_FUSION_S0_AVAILABILITY_MINUTE OK galaxy_availability_tjwq.SDS_S0_AVAILABILITY_DAY OK galaxy_availability_tjwq.SDS_S0_AVAILABILITY_MINUTE OK spark_thriftserver.param_default OK spark_thriftserver.spark_cluster OK spark_thriftserver.spark_thriftserver_queue OK spark_thriftserver.spark_version_setting OK spark_thriftserver.trino_cluster OK sparksql.grant_history OK sparksql.history OK sparksql.kudu_table_reviews OK sparksql.sparksql_queue OK sparksql.sparksql_queue_review OK sparksql_manager.param_default OK sparksql_manager.spark_cluster OK sparksql_manager.spark_thriftserver_queue OK sparksql_manager.spark_version_setting OK sparksql_manager.trino_cluster OK sys.sys_config OK tjwq_resource_management._apps_old OK tjwq_resource_management.admin OK tjwq_resource_management.appInfo OK tjwq_resource_management.appTrackingUrl OK tjwq_resource_management.apps OK tjwq_resource_management.auth_team OK tjwq_resource_management.auth_user OK tjwq_resource_management.bills OK tjwq_resource_management.bills_v3 OK tjwq_resource_management.cluster_apps OK tjwq_resource_management.cluster_availability OK tjwq_resource_management.cluster_state OK tjwq_resource_management.clusters OK tjwq_resource_management.containers OK tjwq_resource_management.containers_summary OK tjwq_resource_management.monitor OK tjwq_resource_management.operations OK tjwq_resource_management.queue_types OK tjwq_resource_management.queues OK tjwq_resource_management.reviews OK tjwq_resource_management.schedules OK tjwq_resource_management.xiaomi_teams OK tjwq_yarn_platform.admin OK tjwq_yarn_platform.app_info OK tjwq_yarn_platform.apps OK tjwq_yarn_platform.auth_team OK tjwq_yarn_platform.auth_user OK tjwq_yarn_platform.bills OK tjwq_yarn_platform.cluster_apps OK tjwq_yarn_platform.cluster_availability OK tjwq_yarn_platform.cluster_queue_mapping OK tjwq_yarn_platform.cluster_state OK tjwq_yarn_platform.clusters OK tjwq_yarn_platform.monitor OK tjwq_yarn_platform.operations OK tjwq_yarn_platform.queue_adjustment_whitelist OK tjwq_yarn_platform.queue_priority OK tjwq_yarn_platform.queue_types OK tjwq_yarn_platform.queues OK tjwq_yarn_platform.regions OK tjwq_yarn_platform.reviews OK tjwq_yarn_platform.reviews_transfer OK tjwq_yarn_platform.schedules OK tjwq_yarn_platform.xiaomi_teams OK tjwqstaging_resource_management._apps_old OK tjwqstaging_resource_management.admin OK tjwqstaging_resource_management.appInfo OK tjwqstaging_resource_management.appTrackingUrl OK tjwqstaging_resource_management.apps OK tjwqstaging_resource_management.auth_team OK tjwqstaging_resource_management.auth_user OK tjwqstaging_resource_management.bills_v2 OK tjwqstaging_resource_management.bills_v3 OK tjwqstaging_resource_management.cluster_apps OK tjwqstaging_resource_management.cluster_availability OK tjwqstaging_resource_management.cluster_state OK tjwqstaging_resource_management.clusters OK tjwqstaging_resource_management.containers OK tjwqstaging_resource_management.containers_summary OK tjwqstaging_resource_management.monitor OK tjwqstaging_resource_management.operations OK tjwqstaging_resource_management.queue_types OK tjwqstaging_resource_management.queues OK tjwqstaging_resource_management.reviews OK tjwqstaging_resource_management.rm_app_attempt_tjwqstaging_hdd OK tjwqstaging_resource_management.rm_app_tjwqstaging_hdd OK tjwqstaging_resource_management.schedules OK tjwqstaging_resource_management.xiaomi_teams OK tjwqstaging_yarn_platform.admin OK tjwqstaging_yarn_platform.app_info OK tjwqstaging_yarn_platform.apps OK tjwqstaging_yarn_platform.auth_team OK tjwqstaging_yarn_platform.auth_user OK tjwqstaging_yarn_platform.bills OK tjwqstaging_yarn_platform.cluster_apps OK tjwqstaging_yarn_platform.cluster_availability OK tjwqstaging_yarn_platform.cluster_queue_mapping OK tjwqstaging_yarn_platform.cluster_state OK tjwqstaging_yarn_platform.clusters OK tjwqstaging_yarn_platform.monitor OK tjwqstaging_yarn_platform.operations OK tjwqstaging_yarn_platform.queue_adjustment_whitelist OK tjwqstaging_yarn_platform.queue_priority OK tjwqstaging_yarn_platform.queue_types OK tjwqstaging_yarn_platform.queues OK tjwqstaging_yarn_platform.regions OK tjwqstaging_yarn_platform.reviews OK tjwqstaging_yarn_platform.reviews_transfer OK tjwqstaging_yarn_platform.schedules OK tjwqstaging_yarn_platform.xiaomi_teams OK Upgrade process completed successfully. Checking if update is needed.
Error message:
init datasource error, url: jdbc: mysql://localhost:3306/book (error initializing database)
Cause: pay attention to the parameter information when connecting to the database to ensure that it is correct. This error occurs when the Oracle database is replaced with MySQL. Only the connection information is modified and the port is forgotten. This low-level error occurs
Question:
django.db.utils.OperationalError: (2026, ‘SSL connection error: unknown error number’)
Solution:
MySQL 5.7 does not enable SSL by default, 8 does
mysql8. 0.25 turn off SSL
Close in docker:
Modify the configuration file:/etc/MySQL/conf.d/mysql.com cnf
[mysqld]
skip_ssl
2021-12-08T09:36:39.612332+08:00 44213799 [Note] [MY-011825] [InnoDB] Parallel scan: 4 2021-12-08T09:36:39.625789+08:00 44213799 [Note] [MY-011825] [InnoDB] ranges: 268 max_threads: 4 split: 268 depth: 2 2021-12-08T09:36:40.833297+08:00 44213799 [Note] [MY-011825] [InnoDB] n: 1607540 2021-12-08T09:36:40.833339+08:00 44213799 [Note] [MY-011825] [InnoDB] n: 1684951 2021-12-08T09:36:40.833345+08:00 44213799 [Note] [MY-011825] [InnoDB] n: 1592299 2021-12-08T09:36:40.833351+08:00 44213799 [Note] [MY-011825] [InnoDB] n: 1754693
How to Solve:
This is the log generated by the new parallel query feature in mysql 8.0.14.
The above error log, which logs information about parallel scans of aggregated indexes, has been removed again in 8.0.20.
Problem Description:
Execute insert or update, delete, error code: 1205. Lock wait timeout exceeded; try restarting transaction
Solution:
#1. View transaction table
select * from information_schema.innodb_trx
TRX was found in the results_ mysql_ thread_ ID has value xxxx (number)
#2. Operation– There will be a piece of data corresponding to xxxx (number) in the result
show processlist
#3. Execute in SQL
kill xxxx
Cause analysis:
The InnoDB storage engine of MySQL supports transactions. After the transaction is started, it is not actively committed. This causes the resource to be occupied for a long time. When other transactions preempt the resource, the preemption fails due to the lock of the previous transaction! Therefore, lock wait timeout exceeded appears
Errno: 1782 error occurred in a set of MySQL 8.0.16 master-slave test environment today. The detailed error reports are as follows:
Worker 1 failed executing transaction 'NOT_YET_DETERMINED' at master log mysql-bin.000029, end_log_pos 33350454; Error executing row event: '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.'
This problem is common in versions 5.7 and earlier: https://bugs.mysql.com/bug.php?id=85480
Through the site analysis of the relay log, it is found that the log contains a commit operation that does not generate gtid and begin, resulting in anonymous transactions, resulting in replication exceptions. The log contents are as follows.
From the log information, when the commit is executed, the gtid is not set_Next, so MySQL considers that the transaction is not anonymous, and the SQL thread stops abnormally
There is little information about the error “row event for unknown table” on the Internet. The information found is explained to the effect that it is caused by the loss of information about the establishment of table map.
The above situation is to skip the two parts “SET @@SESSION.GTID_NEXT” and “BEGIN”, and if only “SET @@SESSION.GTID_NEXT” is skipped, the following error will be reported when BEGIN is executed:
Last_Errno: 1782 Last_Error: Error '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.' on query. Default database: ''. Query: 'BEGIN'
The GTID at the error site is “7efd338e-ee5e-11ea-8957-000c29bed658:5634856”, and the GTID executed from the library is “7efd338e-ee5e-11ea-8957-000c29bed658:5634856”. Due to the lack of GTID_NEXT identifying the transaction, The slave library cannot determine whether the transaction has been executed on the slave library, and the transaction cannot be automatically skipped.
Try to repair, this problem can be fixed by restarting GTID, so that the master and slave can synchronize normally:
#close GTID stop slave sql_thread; SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE'; SET GLOBAL GTID_MODE = 'ON_PERMISSIVE'; SET GLOBAL GTID_MODE = 'ON'; start slave sql_thread; #At this time, the start slave will have an Errno: 1781 exception #open GTID SET GLOBAL GTID_MODE = OFF_PERMISSIVE; SET GLOBAL GTID_MODE = ON_PERMISSIVE; SET GLOBAL GTID_MODE = ON; start slave;
However, because the above error shows that the GTID executed first is greater than the GTID generated later, it is impossible to ensure whether the data is different (of course, you can use pt-checksum to compare the data). To be on the safe side, decide to use hot spare redo copy to quickly restore.