Category Archives: MYSQL

[Solved] Access denied for user ‘Administrator’@’localhost’ (using password: YES)

The reason is that there is also a username property in the system. At this time, the system variable overwrites the value in Properties. At this time, the username value is obtained as the system user name Administrator, and the password is the password in properties to query the database. At this time, the user name and password If it does not match, an error will be reported. When Spring completes the injection, it uses the “${..}” method to get the value to complete the injection. And through this expression, the JVM system properties can also be directly obtained……….

 

Solution 1: Replace the username in the properties file with user or other strings to successfully obtain a connection to access the database. Suggestion: When username is a sensitive word, try not to use username for the sake of safety.

Solution 2: Modify the Spring configuration file to: <context:property-placeholder location=”classpath:/jdbc.properties” system-properties-mode=”FALLBACK / NEVER”/>

Add a system-properties-mode property

This attribute has three values:

FALLBACK — default value, overwrite if it does not exist

NEVER — do not cover

OVERRIDE — cover

[Solved] mysqlslap: Error when connecting to server: 1064 Reach limit of connections

Background: today, mysqlslap is used to test the performance. When the concurrency number is 100, an error is reported, as shown in the figure

Script to run:

mysqlslap -h172.xx.xx.xx  -P9030 -uxx  --concurrency=100 --iterations=1 --create-schema=bds --query=/home/zhangjun/test.sql --engine=innodb --number-of-queries=1 --debug-info

Error message:

Solution:

Set the maximum number of user connections to solve this problem

Database Backup Error: mysqldump: Got error: 1146: Table ‘enwei_hq.tb_classification’ doesn’t exist when using LOCK TABLES

The fixed script for regularly backing up the database has been running, but it is found that the database backed up in these two days is abnormal, the size is only 4.4m, the normal size is 64M, and the database backup is abnormal.

When the backup script is manually executed, there is an error message: mysqldump: got error: 1146: table ‘Enwei_hq.tb_classification’ doesn’t exist when using LOCK TABLES

Prompt database Enwei during backup_Table tb in HQ_Classification does not exist.

It is also possible for me to enter the database to view the table data, but I can’t view the specific contents in the table. The error report also means that it doesn’t exist

 

According to the query data prompt, you need to enter the directory of the database, find the table associated file, and then delete it

After deletion, I use the table data in the previously backed up database to recover, and then query the data. The display can be normal.

Then manually execute the database backup script. There is no error prompt. The backup database is 64M and the size is normal.

[Solved] C# connect SQLite error: {“trying to load a program with incorrect format. (exception from HResult: 0x8007000b)”}

1. Errors are reported as follows:

2. Reason: because the two imported DLL files are written in C + +, the target platform needs to be selected. If it is any CPU, it will choose whether to compile to x64 or x86 (32-bit) according to your configuration

3. Solution: right-click Project Properties generate

[Solved] JSP Use MySQL database Error: java.lang.classnotfoundexception: com.mysql.jdbc.driver

error

In the JSP of JavaWeb, the MySQL database reports an error, and the com.Mysql.JDBC.Driver cannot be found.

The error log is as follows:

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1858)
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1709)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Class.java:264)
	at utils.DBUtils.getConnection(DBUtils.java:14)
	at dao.UserDao.selectAll(UserDao.java:27)
	at servlet.UserServlet.doPost(UserServlet.java:28)
	at servlet.UserServlet.doGet(UserServlet.java:47)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)

reason

Experienced people understand that there is no third-party package added, but it is added in this project, but it is not found.

To view the project structure:

The third-party package is added under external libraries in the following way:

This cannot be taken in Java Web, and the path of jar package cannot be read.

Solution:

Create a libfolder in the Web/WEB-INF directory, and then copy the jar package to this folder to successfully use the database.

[Warning] Using a password on the command line interface can be insecure. (Solved)

Solution:

Method 1: modify my.cnf configuration file

Edit the/etc/my.cnf configuration file

[ root@localhost local]# vi /etc/my.cnf

Add the following to the configuration file

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4
host = localhost ip　　　　　　　　
user = database username
password = 'database password'　　

Import and export using the command (specify load profile)

Export database

Mysqldump — defaults extra file =/etc/my.cnf database name > Database name_$ (date +%Y%m%d_%H%M%S).sql

Import database

MySQL — defaults extra file =/etc/my.cnf database name <Database name_$ (date +%Y%m%d_%H%M%S).sql

Method 2: add MySQL environment directly to Linux environment

Edit the/etc/profile configuration file

[ root@localhost local]# vi /etc/profile

Add the following content on the last side, save and exit

export MYSQL_PWD=database password

Make the configuration effective

[ root@localhost local]# source /etc/profile

When you use the mysqldump command to back up the database, you can omit the – P password parameter, and the execution script will not report an error

Mysqldump – H hostname – U username database name > /Usr/local/dbbackup/database name_$ (date +%Y%m%d_%H%M%S).sql

[Solved] Oracle sql Use sys_guid() to Generate 32-bit id garbled

The main question
select sys_guid() from dual;

 

As you can see, the code is directly messed up.

Reason: SYS_GUID returns a globally unique identifier in the form of a 16-bit RAW type value

Solution:
Use the rawtohex() function method.

 

hextoraw(): convert a hexadecimal string to raw.

rawtohex(): convert raw string to hexadecimal.

Messy code solution.

select rawtohex(sys_guid()) from dual;

 

You can see that the garbled code is gone.

To lowercase.
select lower(rawtohex(sys_guid())) from dual;

MySQL Error: The server quit without updating PID file [How to Solve]

According to Google, there are many possible reasons for the problem. The best way to find out the specific reasons is to check the error log first:

1. The/usr/local/MySQL/data/mysql.pid file may not have write permission
solution: give permission, execute “chown – R MySQL: MySQL/var/data” “Chmod – R 755/usr/local/MySQL/data”, and then restart mysqld!

2. There may already be a MySQL process in the process
solution: use the command “PS – ef|grep mysqld” to check whether there is a mysqld process. If so, use “kill – 9 process number” to kill, and then restart mysqld!

3. It may be the second time MySQL is installed on the machine. There is residual data that affects the startup of the service
solution: go to the MySQL data directory/data. If mysql-bin.index exists, delete it as soon as possible. It is the culprit. I use the third method to solve it!

4. The/etc/my.cnf configuration file will be used when MySQL does not specify a configuration file at startup. Please open this file to see if a data directory (dataDir) is specified under the [mysqld] section
solution: please set this line under [mysqld]: dataDir =/usr/local/MySQL/data

5. Skip federated field problem
solution: check the/etc/my.cnf file for uncommented skip federated fields. If so, comment them out immediately.

6. The error log directory does not exist
solution: use the “chown” and “Chmod” commands to give MySQL owners and permissions

7. SELinux causes trouble. If it is CentOS system, SELinux will be enabled by default
solution: close it, open/etc/SELinux/config, change SELinux = enabling to SELinux = disabled, save the disk, exit and try restarting the machine.

8. In /etc/my.cnf  Add the parameter user = root in [mysqld]

ERROR 1820 (HY000): Unknown error 1820 [How to Solve]

mysql> update user set authentication_string=password(‘mysql123′) where user=’root’ ;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
After changing the password and restarting, prompt this:
mysql> show databases ;
ERROR 1820 (HY000): Unknown error 1820
mysql> show databases;
ERROR 1820 (HY000): Unknown error 1820
mysql> select version();
ERROR 1820 (HY000): Unknown error 1820
You need to execute this command to change the password to take effect.
mysql> SET PASSWORD = PASSWORD(‘mysql123’) ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privilges ;
ERROR 1064 (42000):
mysql> flush privileges ;
Query OK, 0 rows affected (0.00 sec)