SQL ERROR [10004] [42000]: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 64:0 Invalid table alias or column reference ‘T4’: (possible column names are: order_id, order_status, update_time, charge_id, charge_status, station_id, station_name, soc, totalpower, i_a, i_b, i_c, u_a, u_b, u_c, pri_opr_id)
Change to:
ORDER BY
a,
b[error] – initialize specified but the data directory has files in it. About.:
Problem Description:
MySQL is installed in Linux. Because it has been installed and uninstalled before, the uninstallation is not clean. When installing again, the following error occurs when initializing the password
2021-11-23T02:08:16.247240Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-11-23T02:08:16.248753Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2021-11-23T02:08:16.248780Z 0 [ERROR] Aborting
Cause analysis:
reason: the old version of MySQL software does not delete the original data file, that is, the content under the path/var/lib/MySQL/
Solution: delete all contents under the path, including files and folders.
[root@hadoop102 mysql]# rm -rf /var/lib/mysql/
After successful execution, initialize the password:
[root@hadoop102 opt]# mysqld --initialize --user=mysql
[root@hadoop102 opt]# cat /var/log/mysqld.log
2021-11-23T02:17:47.821574Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-11-23T02:17:47.988844Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-11-23T02:17:48.010498Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-11-23T02:17:48.066559Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 8d49959d-4c03-11ec-be82-000c29bdc989.
2021-11-23T02:17:48.067296Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-11-23T02:17:48.067886Z 1 [Note] A temporary password is generated for root@localhost: UBX6u)ekR:kB
Because there is no data saved in my data, direct deletion will not affect subsequent tasks, but!!! If you have data, you must back it up first~
Reason for resolution:
In the process of multi-level query, you need to give the table alias.
Code example
Wrong query method:
select * from (select s.sno from student s where s.ssex='woman');Correct query method:
select * from (select s.sno from student s where s.ssex='woman') as mid_sno;In fact, the difference lies in whether there is an as mid behind it_sno. This is to ensure that each derived table needs to have its own alias.
An error is reported when implementing MySQL master-slave replication configuration, as shown in the figure:
Reason: it is not allowed to change when starting MySQL master-slave replication
Solution: first turn off MySQL master-slave replication, then refresh, then make changes, and then turn on MySQL master-slave replication
① turn off MySQL master-slave replication
stop slave;
② Refresh
flush privileges;
③ Enable MySQL master-slave replication
start slave;
Navicat premium link sqlserver
[im002] [Microsoft] [ODBC driver manager] did not find the data source name and did not specify a default driver
Install sqlncli that comes with Navicat_X64.msi is in the installation directory. The problem is solved after installation!
Create two tables tbltesta and tbltesb, and delete tbltesta
create table dbo.TblTestA(
Id bigint primary key identity(1, 1),
name varchar(20) unique,
)
create table dbo.TblTestB(
Id bigint primary key identity(1, 1),
TestAId bigint not null foreign key references
Status varchar(1),
)Error reported: it is referenced by a foreign key constraint
Reason: other tables refer to the foreign key of this table
Solution:
1. Find the foreign key of this table referenced by other tables
exec sp_Helpconstraint ‘table name’;
2. Find the associated foreign key constraint table name
select name
from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id
where f.parent_object_id=object_ID (‘foreign key association table name ‘)
3. Delete the foreign key constraint
Alter table foreign key constraint table, Drop constraint name (constraint name found above)
4. Delete table (events can be added)
BEGIN TRY
BEGIN TRANSACTION
ALTER TABLE TblTestB DROP CONSTRAINT FK__TblTestB__TestAI__635CD8E4
DROP TABLE dbo.TblTestA
COMMIT TRANSACTION
PRINT 'commit sucesss'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'rollback sucesss'
END CATCH
#Error message:
the reason for this error is the encryption rule MySQL in versions before MySQL 8_native_Password, and the encryption rule after MySQL 8 is caching_sha2_password. There are two ways to solve this problem. One is to update the Navicat driver to solve this problem, and the other is to modify the encryption rules of MySQL user login to MySQL_native_password. The second method is adopted here
# solution
log in to the database
set the password to never expire
alter user 'root'@'localhost' identified by 'root' password expire never;
Set the encryption rule to MySQL_native_password
alter user 'root'@'localhost' identified with mysql_native_password by 'root';
Access Navicat again and you will be prompted that the connection is successful
Starting MySQL.2021-11-17T11:04:25.022108Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
ERROR! The server quit without updating PID file (/data/mysql/data/localhost.localdomain.pid).After msyql8 compilation and installation, start MySQL with/etc/init.d/mysqld start command. The above errors are reported, and then I rechecked the content of my.cnf, as follows:
[mysqld]
server-id=1
port=3306
datadir=/data/mysql/data
basedir=/usr/local/mysql
socket=/usr/local/mysql/mysql.sock
skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[client]
socket=/usr/local/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.dThere is no problem with the overall configuration of my.cnf, and then the startup fails all the time. Finally, it is found that it is the log directory configured by mysqld_safe was not created
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pidSo create authorization directly
Note: the path of log error I configured here is under/var/log/MariaDB. The created path is configured according to its own actual path. Be sure not to forget to create mariadb.log, otherwise an error will be reported
mkdir /var/log/mariadb
touch /var/log/mariadb/mariadb.logMySQL users and users authorize directories
chown -R mysql:mysql /var/log/mariadb/Start the MySQL service again
/usr/local/mysql/support-files/mysql.server startperhaps
/etc/init.d/mysqld startOther startup methods are implemented according to their actual situation
Background:
today we use package to install the module dnspython
[root@makel ~] wget http://www.dnspython.org/kits/1.9.4/dnspython-1.9.4.tar.gz
[root@makel ~] tar -xvf dnspython-1.9.4.tar.gz
[root@makel ~] cd dnspython-1.9.4/
[root@makel dnspython-1.9.4] python3 setup.py install
Solution process
1. Enter Python and import reports an error (problem found)
[root@makel ~] python3
Python 3.8.5 (default, Nov 7 2021, 21:47:38)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import dns.resolver
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/python3/lib/python3.8/site-packages/dns/resolver.py", line 26, in <module>
import dns.message
File "/usr/local/python3/lib/python3.8/site-packages/dns/message.py", line 175
return '<DNS message, ID ' + `self.id` + '>'
^
SyntaxError: invalid syntax
2. Try upgrading (– upgrade) and find that it still can’t be installed
[root@makel ~] pip3 install dnspython --upgrade
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Requirement already satisfied: dnspython in /usr/local/python3/lib/python3.8/site-packages (1.9.4)
Collecting dnspython
Downloading https://pypi.tuna.tsinghua.edu.cn/packages/f5/2d/ae9e172b4e5e72fa4b3cfc2517f38b602cc9ba31355f9669c502b4e9c458/dnspython-2.1.0-py3-none-any.whl (241 kB)
|████████████████████████████████| 241 kB 898 kB/s
Installing collected packages: dnspython
Attempting uninstall: dnspython
Found existing installation: dnspython 1.9.4
ERROR: Cannot uninstall 'dnspython'. It is a distutils installed project and thus we cannot accurately determine which files belong to it which would lead to only a partial uninstall.
3. A new error occurred while attempting to uninstall
[root@makel ~] pip3 uninstall dnspython
Found existing installation: dnspython 1.9.4
ERROR: Cannot uninstall 'dnspython'. It is a distutils installed project and thus we cannot accurately determine which files belong to it which would lead to only a partial uninstall.
4. If the command cannot be unloaded, delete the file manually
[root@makel ~] cd /usr/local/python3 #Go to the directory where python is installed
[root@makel python3] find ./-name "*package*" #Fuzzy search package
./lib/python3.8/site-packages
./lib/python3.8/site-packages/setuptools/package_index.py
./lib/python3.8/site-packages/setuptools/__pycache__/package_index.cpython-38.pyc
./lib/python3.8/site-packages/setuptools/__pycache__/package_index.cpython-38.opt-1.pyc
......
[root@makel python3] cd lib/python3.8/site-packages/ #Find site-packages from the above results and go to this directory
[root@makel site-packages] ll
total 96
drwxr-xr-x. 4 root root 4096 Nov 16 17:18 dns
-rw-r--r--. 1 root root 1277 Nov 9 22:31 dnspython-1.9.4-py3.8.egg-info
....
[root@makel site-packages] rm -rf dnspython-1.9.4-py3.8.egg-info #Delete all files containing the module name
5. Reinstall and import successfully after installation
[root@makel site-packages] pip3 install dnspython #reinstall
[root@makel site-packages] python3
Python 3.8.5 (default, Nov 7 2021, 21:47:38)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import dns.resolver
>>> exit
Failed to open file ‘/ home/mydatabase. SQL’, error: 2 when running SQL script in docker image MySQL in Linux
Today, an error occurred when using docker in centos7 to start MySQL and run SQL script files. The error information is as follows:
mysql> source /home/mydatabase.sql;
ERROR:
Failed to open file '/home/mydatabase.sql', error: 2
After checking the Internet, the cause of the error should be the problem of path matching. The default path is the installation path of MySQL, so MySQL can only access all directories and files under its source directory. The solution is to directly specify the SQL script file to run when logging in. The specific steps are as follows:
first log in to MySQL and create the database to be used. The name of the database I created is Mydatabase1
root@4994c041aa3a: mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> create database mydatabase1;
Exit MySQL and log in again by running SQL script files
# Here mydatabase1 is the database created in the first step, and the file after'<' is the path of the sql script file to be run
root@4994c041aa3a: mysql -uroot -p mydatabase1 < /home/mydatabase.sql
log in again and find that the file has run successfully
root@4994c041aa3a: mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| mydatabase1 |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mydatabase1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------------------+
| Tables_in_mydatabase1 |
+-----------------------------+
| t_disk |
| t_crl |
| t_menu |
| t_user |
+-----------------------------+
4 rows in set (0.00 sec)
Supplement:
# Docker start MySQL command under CentOS7
docker pull mysql:5.7
docker run -tid --name mysql -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:5.7
docker exec -it mysql /bin/bash
mysql -uroot -p
Enter password:
To solve this exception, you need to ensure normal operation
MySqlConnection conn;// Represents an open connection to a MySQL database. This class cannot be inherited
MySqlCommand comm;// Represents the SQL statement to execute on the MySQL database. This class cannot be inherited
MySqlDataAdapter adapter;// Represents a set of data commands and database connections for populating datasets and updating MySQL databases. This class cannot be inherited.
The exceptions of error reporting are as follows
MySql.Data.MySqlClient.MySqlException:“SSL Connection error.”
IOException: unable to read data from the transport connection: an established connection was aborted by the software in your host
Connection string (exception)
string mysqlconn = "server=127.0.0.1;port=3306;user=root;password=root;database=wpfstudy;";
Connection string (normal)
string mysqlconn = "server=127.0.0.1;port=3306;user=root;password=root;database=wpfstudy;SslMode = none;";
Add the following statement to the connection string
SslMode = none;
When using. Net/C # to operate MySQL database, the default value of sslmode is preferred. If the server supports it, please use SSL
SSL (secure socket layer) uses data encryption, authentication and message integrity verification mechanisms to provide security assurance for application layer protocols based on reliable connections such as TCP. If the user’s transmission is not through SSL, the data in the network is transmitted in plaintext, which brings an opportunity for people with ulterior motives. Therefore, many large websites now have SSL enabled. Similarly, in our database, if the client connects to the server to obtain data instead of using SSL connection, the data may be stolen during transmission.
The functions provided by SSL protocol mainly include:
1. Confidentiality of data transmission: use symmetric key algorithm to encrypt the transmitted data
2. Authentication mechanism: use digital signature method to authenticate the server and client based on certificate, and the authentication of the client is optional
3. Message integrity verification: MAC algorithm is used to verify message integrity during message transmission.
0. Foreword: this error is an error reported by using mybatis in the spring boot project to query a piece of data in the database according to the ID value
1. Error message
2021-11-14 15:37:38.168 ERROR 9936 --- [nio-8086-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: 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 '' at line 1
### The error may exist in file [E:\smallTools\idea\code\spring\springboot-new-2021_11_09\springboot-new-mybatis\target\classes\mybatis\mapper\BookMapper.xml]
### The error may involve com.feng.mybatis.mapper.BookMapper.getBookById-Inline
### The error occurred while setting parameters
### SQL: select * from book where id={#id}
### Cause: java.sql.SQLSyntaxErrorException: 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 '' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 '' at line 1] with root cause
java.sql.SQLSyntaxErrorException: 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 '' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.27.jar:8.0.27]
2. Mapper.xml file content
<select id="getBookById" parameterType="com.feng.mybatis.bean.Book">
select * from book where id={#id}
</select>
3. Error reason: the SQL statement is written incorrectly. It should be where id = #{id}
<select id="getBookById" parameterType="com.feng.mybatis.bean.Book">
select * from book where id=#{id}
</select>