1. Enter Mysql to view secure_ file_ The value of print
$mysql -u root -p
mysql> SHOW VARIABLES LIKE “secure_ file_ priv”;
secure_ file_ Prive = null — restrict mysqld from importing and exporting
secure_ file_ Priv =/TMP/– restrict the import and export of mysqld to/TMP/
secure_ file_ Priv = ‘– no restrictions on the import and export of mysqld
Error: [error code] 1290 – the MySQL server is running with the — secure file priv option
mysql>show variables like '%secure%';;
secure_file_prive=null --Restrict mysqld to disallow imports and exports
secure_file_priv=/tmp/ -- Restrict mysqld import and export to occur only in the /tmp/ directory
secure_file_priv=' ' -- do not restrict mysqld import/exportSolution:
Open my.ini file: add: Secure File priv = “D/JB” to the file
1. Import the test.csv file into mysql
load data infile 'D:/jb/a.csv' -- CSV file storage path
into table test -- the name of the table into which the data is to be imported
fields terminated by ',' optionally enclosed by '"' escaped by '"' -- fields separated by commas, strings enclosed by double quotes, strings themselves enclosed by two double quotes
lines terminated by '\r\n'; -- data lines are separated by \r\nThe successful results are as follows:
2. Import tes.csv file into MySQL (including Chinese)
a. Open the CSV file with a text editor, save it in utf8 format, and then import it
load data infile 'D:/jb/a.csv' -- CSV file storage path
into table test character set 'utf8' -- the name of the table to import the data into, set the encoding
fields terminated by ',' optionally enclosed by '"' escaped by '"' -- fields separated by commas, strings enclosed by double quotes, strings themselves enclosed by two double quotes
lines terminated by '\r\n'; -- data lines are separated by \r\n3. Export the data in the library to a CSV file (including Chinese)
select * from test
into outfile 'D:/jb/b.csv' character set 'gbk'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';The results are as follows:
Error code: 1093 error occurs when executing the following SQL statement:
update car set tag = 1 where id in (select id from car where brand_id=182 and tag=0);
The reason for the error is that the modified table and the queried table are the same table, which is not allowed in MySQL. We can solve the problem by querying again in the middle
update car set tag = 1 where id in (select id from (select id from car where brand_id=182 and tag=0) As temp);The first time you encounter a problem, you have to keep a diary
MySQL error prompt:
This report is translated as follows:
The details field has no default value; That is to say, we have not assigned a value to it, and there is no default value set for this field in the table. This is something that was created after MySQL 5 came out. Take a serious look at my.ini file
I used the pagoda to check the following MySQL configuration:
#This paragraph
sql-model=NO_ ENGINE_ SUBSTITUTION,STRICT_ TRANS_ TABLES
Take a look at No_ ENGINE_ Division official explanation:
When using alter table or create table to specify engine, the required storage engine is disabled or not compiled. What should be done. Enable no_ ENGINE_ When substitution , the error will be thrown directly; When this value is not set, create is replaced by the default storage engine, atler does not change and throws a warning
Take another look at strict_ TRANS_ Official explanation of tables:
Indicates that strict mode is enabled
Look again: No_ AUTO_ CREATE_ USER
Grant is not allowed to create users with empty password
Baidu solved the problem in a circle. If you forget what you found, you can write a record by yourself. The reason for the problem is that MySQL uses strict verification method
Solution: directly change the SQL model mode to the following figure:
sql-mode=NO_ AUTO_ CREATE_ USER,NO_ ENGINE_ SUBSTITUTION
Problem encountered: error 1265 when Python inserts data to MySQL
I saw some posts on the Internet. They were all about the insufficient length of the table field and the type discrepancy. I checked the data I wanted to insert and found that it was really a data problem
user = users(id='0009', email='小9', password='小小', admin=2, name='小小', image='小小', created_at=22)
The data you want to insert is as above, but after initialization, the data saved in userargs is not saved according to (ID, email, password, admin, name, image, created)_ At) the data in this order is [name, ID, email, created]_ At, image, password, admin, etc
def __init__(self, **kwargs):
userargs = []
# print "lenofkwargs", len(kwargs)
if len(kwargs) >= 0:
for attr, value in kwargs.iteritems():
setattr(self, attr, value)
userargs.append(value)
Although we don’t know the reason, we have established the corresponding dict by using the setattr function__ init__ Function can be processed as follows:
self.usertuple = (self.id, self.email, self.password, self.admin, self.name, self.image, self.created_at)
This way, there will be no errors in the insertion
ERROR 1130 (HY000): Host ‘Lenovo-PC’ is not allowed to connect to this MySQL server
MySQL Authorization Allowed Connection ERROR 1130 (HY000)
C:\Users\Lenovo>mysql-h10.255.9.79-uroot-p034039
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
ERROR1130(HY000):Host'Lenovo-PC'isnotallowedtoconnecttothisMySQLserver
C:\Users\Lenovo>mysql-hlocalhost-uroot-p034039
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis54
Serverversion:5.6.21MySQLCommunityServer(GPL)
Copyright(c)2000,2014,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>You can see from the above information that when the h parameter is the local IP address, MySQL does not allow the client to connect. Why
Look at the following SQL query:
mysql>usemysql
Databasechanged
mysql>selectuser,hostfromuser;
+------+-----------+
|user|host|
+------+-----------+
|root|127.0.0.1|
|root|::1|
||localhost|
|root|localhost|
+------+-----------+
4rowsinset(0.00sec)the root user is only allowed to connect on 127.0.0.1 and localhost, but is not allowed to connect using the local IP in LAN
This explains why connections are not allowed
The following is to give root the corresponding login permissions
mysql>GRANTALLPRIVILEGESON*.*TO'root'@'10.255.9.79'IDENTIFIEDBY'034039'WITHGRANTOPTION;
QueryOK,0rowsaffected(0.23sec)
mysql>selectuser,hostfromuser;
+------+-------------+
|user|host|
+------+-------------+
|root|10.255.9.79|
|root|127.0.0.1|
|root|::1|
||localhost|
|root|localhost|
+------+-------------+
5rowsinset(0.00sec)
mysql>Through this authorization statement
mysql>GRANTALLPRIVILEGESON*.*TO'root'@'10.255.9.79'IDENTIFIEDBY'034039'WITHGRANTOPTION;it is allowed to use the root user and password 034039 to connect all MySQL databases at the address of 10.255.9.79, and pay the select, insert, update and delete permissions
Finally, refresh the permissions:
mysql>flushprivileges;
QueryOK,0rowsaffected(0.68sec)OK, this completes the authorization
C:\Users\Lenovo>mysql-h10.255.9.79-uroot-p034039
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis55
Serverversion:5.6.21MySQLCommunityServer(GPL)
Copyright(c)2000,2014,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>A small problem I encountered today when creating a MYSQL database.
create table booktype
(
btid int(5) unsigned zerofill auto_increment not null primary key,
btname varchar(100) not null unique,
btnote text
);
create table books
(
bid int(5) unsigned zerofill auto_increment not null primary key,
bname char(30) not null,
isbn char(50) not null,
author char(30) not null,
press text,
summary text,
bcount int not null default 0,
btid int,
foreign key(btid) references booktype(btid)
);
error:
ERROR 1005 (HY000): Can’t create table ‘.\bookdata\books.frm’ (errno: 150)
The main problem is that
foreign key(btid) references booktype(btid) in books table btid is int and booktype table btid set associated field type does not match, books table btid corrected to: btid int(5) unsigned zerofill, it will not report an error, creating tables and modifying tables often forget this in one small step.
On CentOS, start MySQL with the command: Service mysqld restart
# service mysqld restart
Stopping mysqld: [ OK ]
MySQL Daemon failed to start.
Starting mysqld: [FAILED]Enter the MySQL related directory to view:
# cd /var/lib/mysql/
# ls
hhw_global ibdata1 ib_logfile0 ib_logfile1 localhost.localdomain.err mysql mysql.sock testTo view the server startup log:
# tail localhost.localdomain.err
190610 20:16:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
190610 20:16:57 [ERROR] You need to use --log-bin to make --binlog-format work.
190610 20:16:57 [ERROR] Aborting
190610 20:16:57 [Note] /usr/libexec/mysqld: Shutdown complete
190610 20:16:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid endedAs you can see, the two lines reported an error saying that the — binlog format option was turned on, but bin log was not turned on, so the MySQL process could not be started. Use the following command to view the location of the configuration:
# mysqld --verbose --help|grep -A 1 'Default options'Or:
# mysql --verbose --help|grep -A 1 'Default options'Query results:
# mysql --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnfFind the corresponding my.cnf, modify the corresponding configuration item, and restart mysqld service
[Err] 1052 – Column ‘modify_time’ in where clause is ambiguous
Error statement:
SELECT AVG(T.se)%60
FROM
( SELECT TIMESTAMPDIFF(SECOND,first_transfer_time,modify_time) se
FROM xes_appeals
INNER JOIN xes_appeal_templates
WHERE xes_appeals.app_type = xes_appeal_templates.app_type
AND xes_appeals.app_type = 313
AND xes_appeals.first_transfer_time >= ‘2018-11-23 00:00:00’
AND xes_appeals.first_transfer_time <= ‘2018-11-23 23:59:59’
AND modify_time >= ‘2018-11-23 00:00:00’
AND modify_time <= ‘2018-11-23 23:59:59′
AND xes_appeals.`status` =4
AND xes_appeals.operater_id <> 0
) T
The column’ID’ is repeated in the field list. In fact, the two tables have the same field, but the table name is not added before the name of the table field when used, resulting in unknown reference
after adjustment:
SELECT AVG(T.se)%60
FROM
( SELECT TIMESTAMPDIFF(SECOND,first_transfer_time,xes_appeals.modify_time) se
FROM xes_appeals
INNER JOIN xes_appeal_templates
WHERE xes_appeals.app_type = xes_appeal_templates.app_type
AND xes_appeals.app_type = 313
AND xes_appeals.first_transfer_time >= ‘2018-11-23 00:00:00’
AND xes_appeals.first_transfer_time <= ‘2018-11-23 23:59:59’
AND xes_appeals.modify_time >= ‘2018-11-23 00:00:00’
AND modify_time <= ‘2018-11-23 23:59:59’
AND xes_appeals.`status` =4
AND xes_appeals.operater_id <> 0
) T
After installing and initializing MySQL database under Linux, use mysqld_ Safe starts the MySQL database, as shown below, the startup fails
[ root@SVNServer bin]# ./mysqld_ safe –user=mysql&
Or
[ root@SVNServer bin]#/etc/init.d/mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/AY14020816093477605eZ.pid).
Restart MySQL will throw the above red font error
You can only see the specific reasons for the failure of starting mysql. You need to check the. Err file in the database directory and the. Err file. The contents are as follows:
140726 00:18:10 mysqld_ safe mysqld from pid file /data/mysql/AY14020816093477605eZ.pid ended
140726 00:31:19 mysqld_ safe Starting mysqld daemon with databases from /data/mysql
/usr/local/mysql/bin/mysqld: File ‘./mysql-bin.index’ not found (Errcode: 13)
140726 0:31:19 [ERROR] Aborting
140726 0:31:19 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
This error report is marked in red, errcode13, which is generally a permission problem. Whether MySQL users have permission to write all the files in the database directory, check the permissions, and modify the permissions of users and user groups in the MySQL directory
chown -R mysql:mysql /usr/local/mysql
Check the permissions in the/usr/local/MySQL directory, as shown in the following figure
I found the problem. It’s really a permission problem. The following troubleshooting is simple. Assign the owner of the data directory to the MySQL user
in RedHat system, : chown mysql.mysql – R/usr/local/MySQL can be used
in CentOS system, : chown can be used mysql:mysql -R /usr/local/mysql
OK, now start MySQL again, as shown in the figure below, there is no error
[ root@SVNServer bin]#/etc/init.d/mysqld start
If the error is still reported, it may be the permission setting problem of/usr/local/MySQL directory. The permission setting is as follows:
chmod 755 /usr/local/mysql
After setting, the permissions are as follows: ll/usr/local/MySQL
Drwxr-xr-x 9 MySQL MySQL 12288 July 26 09:50 MySQL
Try, can connect, as shown in the figure, OK
