1. Error information
2. Edit file
# open the configuration file
vim /etc/my.cnf
# add this line in the end
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
3. Restart MySQL
systemctl restart mysqld
4. Test
# Start
nohup java -jar xxx.jar &
# Checnk the console
tail -f nohup.out
My error report is like the following picture
The prompt is as follows: an error is reported
2021-12-26T12:28:24.321789Z 0 [System] [MY-010116] [Server] C:\mysql-8.0.26-winx64\bin\mysqld.exe (mysqld 8.0.26) starting as process 8168
2021-12-26T12:28:24.323688Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2021-12-26T12:28:24.335659Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-12-26T12:28:24.337392Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2021-12-26T12:28:24.337508Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2021-12-26T12:28:24.337618Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2021-12-26T12:28:24.337878Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-12-26T12:28:24.337978Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-12-26T12:28:24.338395Z 0 [System] [MY-010910] [Server] C:\mysql-8.0.26-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.26) MySQL Community Server - GPL.Reason guess:
The MySQL service is always turned on (it starts automatically after startup), so when it is turned on, an error will be reported when the command line is started
Solution:
Enter the command prompt in administrator mode and enter the following command to close the MySQL service
net stop mysql
So far, the problem has been solved. Entering mysqld — console again will open the MySQL service normally
CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
xxxx/MYSQL_INCLUDE_DIR
Solution: yum install mysql-devel
Other missing boost/cmake etc. can be installed by compiling the source code.
cmake-3.8.2.tar.gz
boost_1_78_0.tar.gz
An error occurred when exporting and importing data from hive warehouse into MySQL database with sqoop tool in Ubuntu pseudo distributed HDFS.
Give it a try
In the folder generated in ~/TMP/sqoop Chen/compile/jar. java. Copy the calss file to the/usr/local/sqoop/lib/folder, and then run the export and Import command again
The above sqoop-chen directory is sqoop user name
Export-Import command:
The above solution:
After running again, the following figure shows that the export and import succeeded:
View MySQL:
hive sql error: ParseException in subquery source
org.apache.hadoop.hive.ql.parse.ParseException:line 368:18 cannot recognize input near ‘group’ ‘by’ ‘order_phone_num’ in subquery source
sql：
customer_Flag as (
select order_phone_num,
concat_ws(';', collect_list(c)) as a,
sum(customer_flag) as b
from (
select order_phone_num,
customer_flag,
concat_ws(',', collect_list(cast(r_diff as string))) as c
from add_payment_period
group by order_phone_num,
customer_flag
)
group by order_phone_num
),
Solution: give the outermost group by order_phone_Num plus an alias C1
customer_Flag as (
select order_phone_num,
concat_ws(';', collect_list(c)) as a,
sum(customer_flag) as b
from (
select order_phone_num,
customer_flag,
concat_ws(',', collect_list(cast(r_diff as string))) as c
from add_payment_period
group by order_phone_num,
customer_flag
) c1
group by order_phone_num
),
the reason for the error is very simple: MySQL should be used instead of MySQL when starting the command server
mac brew install mysql
Install MySQL with homebrew on Mac to perfectly solve error 2002 (HY000):
Can’t connect to local MySQL server through socket ‘/tmp/mysql.Sock ‘(2) error
1. Start the command mysql server
the startup method of using MySQL command directly is wrong
This problem occurs when you directly call the MySQL command. It is said on the Internet that it is because of MySQL Caused by CNF configuration file.
the correct startup method is mysql server
➜ support-files mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
➜ support-files pwd
/opt/homebrew/opt/mysql/support-files
➜ support-files ls
mysql-log-rotate mysql.server mysqld_multi.server
2. my.CNF file location
my.CNF file location
➜ homebrew cd etc
➜ etc ls
bash_completion.d ca-certificates dump.rdb my.cnf [email protected]
redis-sentinel.conf redis.conf
➜ etc pwd
/opt/homebrew/etc
3. MySQL installation location
➜ homebrew cd opt
➜ opt ls
ca-certificates jasper libevent libpng lz4 [email protected] pzstd six
devil jpeg libjpeg libtiff mysql protobuf redis zlib
icu4c lcms2 libjpg little-cms2 [email protected] [email protected] [email protected] zstd
➜ opt cd mysql
➜ mysql ls
INSTALL_RECEIPT.json LICENSE.router README.router homebrew.mxcl.mysql.plist lib support-files
LICENSE README bin homebrew.mysql.service mysqlrouter-log-rotate
LICENSE-test README-test docs include share
➜ mysql pwd
/opt/homebrew/opt/mysql
➜ mysqlRemember a mysql query problem, associated query the order table, Mysql ERROR 1032 (HY000): Can’t find record in order_form appeared, after checking the data, no problem was found.
Execute: REPAIR TABLE order_form USE_FRM The problem is solved after the repair.
PS: Before repairing, please remember to back up the table to be repaired to prevent data loss, remember! !
An error is reported when using idea to connect to the company database
Add the following codes in the Advanced, It may be OK.
"-Djdk.tls.disabledAlgorithms=SSLv3, TLSv1.1, RC4, DES, MD5withRSA, DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, include jdk.disabled.namedCurves"
You can connect normally
Situation: write data to the database and report out of range value for column
reason: the database field is too large
Solution:
1. If – 1 is passed in, modify the database field to signed int.
2. If too large a number is passed in, try changing the field to bigint or increasing the length.
When using Mysql to execute update, if the primary key is not used in the where condition, the following error will be reported and the update cannot be executed.
Exception content: error code: 1175 You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
Solution:
this is because MySQL runs in the safe updates mode. Modify the database security mode to allow the update or delete commands to be executed under non primary key conditions.
Execute command:
SET SQL_SAFE_UPDATES = 0;
Background:
Execute the following statement to report an error:
mysqldump.exe -uroot -p --master-data=2 --single-transaction --routines --triggers --events mc_orderdb > aa.sqlTried Methods:
1. Modify the configuration file and add the following configuration
#Enable binary logging
log-bin=mysql-binThen restart the service
It doesn’t work
2. Delete — master data = 2 parameter
Export succeeded
1. Error message
When executing the following SQL statement, an error message appears: error 1067 (42000): invalid default value for ‘end’_ time’
CREATE TABLE seckill1(
`seckill_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Commodity inventory id',
`name` varchar(120) NOT NULL COMMENT'product name',
`number` int NOT NULL COMMENT'Stock quantity',
`start_time` timestamp NOT NULL COMMENT'second kill start time',
`end_time` timestamp NOT NULL COMMENT'second kill end time',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
PRIMARY KEY(seckill_id),
key idx_start_time(start_time),
key idx_end_time(end_time),
key idx_create_time(create_time)
)ENGINE = InnoDB AUTO_INCREMENT = 1000 DEFAULT CHARSET = utf8 COMMENT = 'Spike inventory table';The error message is as follows:
2. Solution
(1) View SQL_mode :
show session variables like '%sql_mode%(2) Modify sql_mode(remove NO_ZERO_IN_DATE,NO_ZERO_DATE) :
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';(3) Re-execute the SQL statement in 1:
the table creation success is displayed.
the screenshot of the three steps is as follows:
3. Reasons for error reporting
(1) Error explanation: the first timestamp column in the
table (that is, the start_time row in the SQL statement of 1) (if it is not declared null or the default or on update clause is displayed) will automatically assign the default current_timestamp and on update current_timestamp attributes.
the timestamp column after the first one (that is, the end_time line in the SQL statement of 1) if it is not declared null or the default clause is displayed), the default ‘0000-00-00 00:00’ (zero timestamp) will be automatically allocated, which does not meet the no_zero_date in sql_mode and an error will be reported.
(2) solution (three) Note: the solution in 2 is only for the created table
method 1: execute select @ @ sql_mode, copy the queried value, delete the no_zero_date, and then execute set sql_mode = 'modified value'. This method only takes effect in the current session
method 2: execute select @ @ global.sq first l_Mode , copy the queried value and put no in it_ZERO_Delete date and execute set global SQL_Mode = 'modified value', this method takes effect in the current service, and becomes invalid after re MySQL service
method 3: open my.In the MySQL installation directory Ini or my CNF file, add the following line,
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION， Then restart mysql. This method is permanent