Category Archives: MYSQL

[Solved] MYSQL Error: “ Every derived table must have its own alias”

Every derived table must have its own alias

This statement means that each derived table must have an alias of its own

This error usually occurs when querying multiple tables.

Because the result of the subquery is used as a derived table for the higher level query when doing nested queries, the result of the subquery must have an alias

Change the MySQL statement to: select count(*) from (select * from ……) as total;

The problem is solved, although only an alias total is added which has no effect, but this alias is necessary

select name1 name, java, jdbc, hibernate,total
from (select sc1.name name1, sc1.mark java
from student_course2 sc1
where sc1.course=’java’)as a,
(select sc2.name name2, sc2.mark jdbc
from student_course2 sc2
where sc2.course=’jdbc’)as b,
(select sc3.name name3, sc3.mark hibernate
from student_course2 sc3
where sc3.course=’hibernate’)as c,
(select sc4.name name4,sum(sc4.mark) total
from student_course2 sc4 group by sc4.name)as d
where name1=name2 and name2=name3 and name3=name4 order by total ASC;

The results are correct:

+———-+——+——+———–+——-+
| name| java | jdbc | hibernate | total |
+———-+——+——+———–+——-+
| wangwu|40 |30 |20 |90 |
| lisi|70 |60 |50 |180 |
| zhangsan |100 |90 |80 |270 |
+———-+——+——+———–+——-+
3 rows in set (0.02 sec)

Mysql import Error Code: 2013 – Lost connection to MySQL server during query

First view Win + R, call out services.msc and open the service dialog box

Search for MySQL services, right-click — > Property to view the path of the INI file of MySQL

It’s easy to find the wrong place without using the service to search the MySQL execution file path. I found a mydefault.ini file last time, and the result was wrong

Search for “Max” under the file_ allowed_ “Packet”, add in the search section:

lower_case_table_names = 1
wait_timeout=2880000
interactive_timeout = 2880000
max_allowed_packet = 50M

If there is a function in the imported SQL file, an error may be reported:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de….

This function is executed in SQLYOG:

set global log_bin_trust_function_creators=TRUE;

Sqlog recovery database error solution [error code: 2006 – MySQL server has gone away]

 

SQLyog is reporting an error when importing the database

Error Code: 2006 – MySQL server has gone away

The max_allowed_packet (a MySQL parameter) is not set to a large enough value.

Then I’ll change it.

In Windows:

In the MySQL server installation directory,
in my.ini file, add the following line under [mysqld] in SERVER SECTION.

max_allowed_packet = 16M

In Linux:

Copy the my-xxx.cnf file from /usr/share/mysql to /etc as my.cnf

xxx can be small, medium, large, huge … depending on the requirement.

$ cp /usr/share/mysql/my-xxx.cnf /etc/my.cnf

In the my.cnf file, change the default
max_allowed_packet = 1M
to
max_allowed_packet = 16M

Save the file and restart MySQL server.

Today, when importing .sql files, I got Error Code: 2006 – MySQL server has gone away error, it turns out that the imported sql file is larger than the default max_allowed_packet value of the system. I found the my.cfg file and modified other files, so I used the sql statement to modify it directly.

SET GLOBAL max_allowed_packet=67108864;

This is quite good, no need to restart the service after the change, it works directly

[Solved] MYSQL Error [Error Code] 1290 – The MySQL server is running with the –secure-file-priv option

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

 

[Solved] MySQL Import csv File[Error Code] 1290 – The MySQL server is running with the –secure-file-priv option

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/export

Solution:

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\n

The 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\n

3. 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:

[Solved] MySQL Error Code: 1093. You can’t specify target table ‘car’ for update in …

Error code: 1093. You can’t specify target table ‘car’ for update in from clause

 

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);

MySQL ERROR 1005: Can’t create table (errno: 150) [How to Solve]

When creating a reference constraint in mysql, the error message MySQL error 1005: can’t create table (errno: 150) will appear. As a result, the reference constraint cannot be created

General situation of the problem:

The reference types of foreign keys are different. For example, the primary key is int and the foreign key is char

The column referenced in the main table could not be found

The character encoding of primary key and foreign key is inconsistent, and the storage engine may be different

MYSQL Insert: SQLSTATE[HY000]: General error: 1364 Field ‘xxxxx’ doesn’t have a default value

The first time you encounter a problem, you have to keep a diary

MySQL error prompt:

SQLSTATE[HY000]: General error: 1364 Field ‘xxxxx’ doesn’t have a default value

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

How to Solve Mysql missing ERROR 1265: Data truncated for

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

Solutions to MySQL error 1142: Error:ALTERcommanddeniedtouser’xxxx’@’localhost’fortable’uc_notelist’

 

Error:ALTERcommanddeniedtouser'xxxx'@'localhost'fortable'uc_notelist'
Errno:1142
SQL::ALTERTABLEuc_notelistADDCOLUMNapp1tinyintNOTNULL

Explain that user xxxx does not have ALTER permission.
Go to mysql and
mysql>SELECTalter_privFROMuserWHEREUser='xxxx';
I found that alter_priv='N',sure enough there is no permission.
Modify the privileges.
mysql>UPDATEuserSETalter_priv='Y'WHEREUser='xxxx';
mysql>flushprivileges;