重要提示:此文章将持续更新,建议收藏
1、java.sql.SQLException: The server time zone value ‘�й���ʱ��’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the ‘serverTimezone’ configuration property) to use a more specifc time zone value if you want to utilize time zone support.
解决:
第一种:
语句:show VARIABLES like ‘%time_zone%’
结果: system_time_zone
time_zone SYSTEM
修改时区:中国是中8区 set global time_zone=’+8:00’;第二种: 连接后加上时区:&serverTimezone=Asia/Shanghai
url: jdbc:mysql://localhost:3303/general_test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
2、mysql数据库,FUNCTION wx_driving.to_char does not exist
解决: 日期转为字符串使用 date_format(create_time,’%Y-%m-%d %H:%i:%s’)
如:“2021-01-14 10:31:13”
3、Error No.1130 Host ‘192.168.25.3’ is not allowed to connect to this MySQL server
解决:
改表法
可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑登入MySQL后,更改"MySQL"数据库"user"表里的"host"项,将"localhost"改成"%"mysql -u root -pyourpwd
mysql>use mysql;
mysql>update user set host =’%’ where user =‘root’;
mysql>select host, user from user;
4、ORA-12514:TNS:监听程序当前无法识别连接描述符中请求的服务
原因: 刚安装完11.2.0.1.0后,使用pl/sql连接时出现
解决:
更改监听文件listener.ora,下面红色部分为添加内容I:\app\xxx\virtual\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
SID_LIST_LISTENER = (SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = I:\app\supenghui\virtual\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = “EXTPROC_DLLS=ONLY:I:\app\supenghui\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll”)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = I:\app\supenghui\virtual\product\12.2.0\dbhome_1)
(SID_NAME = ORCL)
)
)
5、ORA-00600: 内部错误代码, 参数: [qctcte1], [0], [], [], [], [], [], [], [], [], [], []
场景复现:
部署一个后台项目,在云服务器上,配置为tomcat7,oracle版本为11.2.0.1.0,编译好项目,从本地的tomcat的webapp下,压缩编译后的项目,上传到云服务器中tomcat的webapp下,解压后,启动tomcat, 登录系统,其他节点没问题,在访问测试节点时,出现如下图错误:
一开始以为数据泵文件导入出错了,之前导入出现过一次卡顿,后来又重新建表空间建用户授权等,又导了一遍数据库,结果又出现了这个错误;
解决思路:
遇事先百度,大浪淘沙般,终于看到一句有用的话,说是出现这种错误大概率是order by语句问题,后来查询了原sql,的确有一个order by语句,先把它注了,然后重启tomcat,进入测试节点,测试数据正常显示了;问题算解决了,但是想着不能就这样算了,程序是正常了,但是心里总有点疑惑,为啥会出现这个错误,咋又能给解决了,之前咋没出现这个错误呢?
带着疑问,然后想着,使用oracle出错,会有个alter文件,因为是第一次查看alter文件,先是百度了一下,怎么查看oracle的alter文件,如下步骤,即可找到alter文件所在
使用命令行登录数据库: sqlplus / as sysdba (超级管理员)
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string c:\app\administrator\diag\rdbm
s\orcl\orcl\trace
core_dump_dest string c:\app\administrator\diag\rdbm
s\orcl\orcl\cdump
max_dump_file_size string unlimited
shadow_core_dump string none
user_dump_dest string c:\app\administrator\diag\rdbm
s\orcl\orcl\trace
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string c:\app\administrator\diag\rdbm
s\orcl\orcl\trace
SQL>
或者在客户端执行以下sql:
select value from v$parameter where name=‘background_dump_dest’;
在数据库服务器找到上图显示的目录:
打开alert_orcl.log文件,全局搜索ORA-00600,可见有多条
把错误sql,拷贝出来,放到pl/sql去执行,然后这个语句就复现了服务器后台的错误日志,如下图:
分析这个sql,在原sql外套了一层select count(0) from (...) tmp_count
,而执行括号内的语句是没有问题的,因此问题出现在,原sql+外层
套的这个sql语句了,经分析,在最下面order by语句,order by 24 desc,25 desc
,去掉就好使了;
外层的sql是在代码中做了封装,导致里面的sql语句加上order by 后就报错
这种现象目前只在oracle11.2.0.1.0出现,11.2.0.3.0(生产版本)是没有,由此可见可能是低版本导致的原因;重装测试环境oracle为生产版本或者改动sql
6、[Err] 1206 - The total number of locks exceeds the lock table size
在mysql数据库的innodb引擎中,对一个大数据量级的表进行update/insert/delete时,可能会导致[Err] 1206。这是由于innodb_buffer_pool_size的值太小的原因,通过更改其环境变量,重启mysql后,再执行相关语句即可。
操作步骤
登录mysql
mysql -uroot -p666666
查询
show variables like "%_buffer%";
可以看到innodb_buffer_pool_size 的值为 134217728,单位为字节
更改变量值
SET GLOBAL innodb_buffer_pool_size=10737418240;
重启mysql
service mysqld restart
[root@kfcs ~]# mysql -uroot -p666666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1072074
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like "%_buffer%";
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
24 rows in set (0.05 sec)
mysql> SET GLOBAL innodb_buffer_pool_size=10737418240;
Query OK, 0 rows affected (0.04 sec)
mysql> exit
Bye
[root@kfcs ~]# service mysqld restart
Shutting down MySQL........... SUCCESS!
Starting MySQL. SUCCESS!
如果上面不生效,需要在/etc/my.cnf配置文件中添加上innodb_buffer_pool_size=10737418240
然后再重启mysql,就可以了
10737418240为10G10241024*1024
author:su1573
鄙人记录生活点滴,学习并分享,请多指教!!!
如需交流,请联系 sph1573@163.com,鄙人看到会及时回复