Category Archives: MySQL

[Solved] com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.

Leave a reply

1. Abnormal information

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_231]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_231]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_231]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_231]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:389) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.Util.getInstance(Util.java:372) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:958) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872) ~[mysql-connector-java-5.1.35.
Caused by: java.lang.NullPointerException: null
    at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:2989) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1873) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1802) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1206) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2239) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2270) ~[mysql-connector-java-5.1.35.jar:5.1.35]
    ... 105 common frames omitted

Two, the solution

1. Check your mysql version

select version();

For example, my version is: 8.0.3

2. Update the version of mysql-connector-java to be consistent with the version of mysql, which is version 8.

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.20</version>
</dependency>

MYSQL 5.7 Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it..

When exporting data with MySQL 5.7, an error was reported. The error was reported as follows:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

According to the wrong information, we found that secure-file-priv will specify the folder as the place where the exported files are stored, so we can find this folder first.

Solution 1:

enter the following command in the MySQL command line interface:

show variables like '%secure%';


Annotated is the correct file path, we will export files in this directory.
For SQL instructions, modify as follows:

SELECT * FROM User details WHERE gender='male'
INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\man.txt'

The file can be successfully exported to this directory.
Solution 2:
Go to the installation path C:\ProgramData\MySQL\MySQL Server 5.7, find my. Ini file and modify the default save path of Secure-file-priv.
Secure_file_prive =null — mysqld does not allow import and export
secure_file_priv=/ TMP/– limits the import and export of mysqld to only occur in the/TMP/directory
secure_file_priv= “” — does not restrict the import and export of mysqld

ORA-29913 ORA-06512 external table query prompt: error

Leave a reply

exp:

SQL> select * from T_TEMP_LZY_QYDJ_1;

ORA-29913: an error occurred when calling out ODCIEXTTABLEFETCH ORA-30653: the
rejection limit has been reached
ORA-06512: in “SYS.ORACLE_LOADER”, line 14
ORA-06512: in line 1

Check the external table log T_TEMP_LZY_QYDJ_1_2696_1384.log, which indicates an error:

error processing column ZCLX in row 63 for datafile e: mplsbd.txt
ORA-01401: inserted value too large for column

It turns out that the column width of the table is insufficient!!

Runtime error occurred: 1843 (ORA-01843: invalid month)

Leave a reply

1) When we use the following SQL statement
INSERT INTO “temptable” (DELIVER_DATE) VALUES (TO_DATE(’27-Jun-2007 15:57:30′,’DD-MON-YYYY HH24:MI: SS’))
Runtime error occurred: 1843 (ORA-01843: invalid month) The 
database will report an ORA-01843 error. This is because the client is in a Chinese environment, and the format mon cannot be written in English. It must be written in Chinese
If you don’t want to modify the sql statement to run in June , you need to use the alter session command to modify nls_date_language to american before executing the statement, as follows:
alter session set nls_date_language=’american’ –display the date in English
2)
Today An error of ORA-01843 has occurred. This error represents an invalid month, which is usually prompted when the date is converted. 
Solution 
alter session set NLS_DATE_FORMAT=’DD-MON-YY’; 

ERROR 1064 (42000): You have an error in your SQL syntax； check the manual that corresponds to your

Error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘stock’(
‘id’ int unsigned auto_increment,
‘number’ int (10) not null,
‘name’ va’ at line 1

The reason for this is that the quotation marks should be used for the reason that the English quotation marks, i.e., the – symbol in the key below the esc key on the keyboard has a ~ and – key, are used as quotation marks.

Mysqldump: Got error: 1045: Access denied for user ‘root‘@‘localhost‘

(using password: NO) when trying to connect,mysqldump: [Warning] Using a password on the command line interface can be insecure,mysqldump Export error,linux export mysql database error mysqldump:got error:1045

mysqld Ver 5.7.24 for Linux on x86_64 (MySQL Community Server (GPL))

Edit mysql configuration file

vim /etc/my.cnf


Analysis of reasons:
Verify that the username and password are correct. The password contains special symbols (such as ~! @$%^& *()_ etc.) Remember to wrap the “password” in quotation marks, or consider changing the password to try to locate the problem.

Mysqli::stmt Fatal error: Call to a member function bind_param() on a non-object in …

$name = “Ming”;
$sex = “male”;
$age=20;
$sql=”insert into student(name,sex,age) values (?,?,?,?) “;
$mysqli_stmt=$mysqli-> prepare($sql);
$mysqli_stmt-> bind_param(‘ssi’, $name,$sex,$age);No syntax error is indicated, but data insertion is always unsuccessful. On checking, $SQL =”insert into Student (name,sex,age) values (?,?,?,?) “; One too many?, there is no corresponding parameter.

Mysql :error 1111. Invalid use of group function

Invalid use of group function Invalid use of group function
The phenomenon of

Oracle executes double-layer sum without Invalid use of group function, executes the same SQL to mysql, and Invalid use of group function


find the cause
check correct

SELECT [DISTINCT|DISINCTROW|ALL] select_expression,... -- Query results
[FROM table_references -- specify the table for the query
[WHERE where_definition] -- where clause, the filtering condition of the query data
[GROUP BY col_name,...] -- group the query results [that match the where clause]
[HAVING where_definition] -- condition on the grouped results
[ORDER BY{unsigned_integer | col_name | formula} [ASC | DESC],...] -- Sort the query results
[LIMIT [offset,] rows] -- Limit the number of rows to be displayed for a query
[PROCEDURE procedure_name] -- query the result set data returned by the procedure
]

Take a closer look at SQL to locate the two-layer SUM cause

Modify separate SUM, problem solved, the outermost package of a layer

Mysql Script Error Code: 1136. Column count doesn’t match value count at row 1

Running mysql script during development, mysql prompts Column count doesn’t match value count at row 1 error. After sticking, it is found that the number of columns in the SQL statement is inconsistent with the number of subsequent values. For example, insert into table names (field1,field2,field3) values(‘a’,’b’).