Category Archives: MySQL

[Solved] Daily further: database error: unknown column ‘model’ in ‘field list‘

  If you encounter this problem today, record that you have created this’ model ‘field in the database and used querywrapper to initialize the query,   And then I reported this mistake,     Then I go to the database to query with SQL statements executed by the system;   It turned out that it was still a mistake   1054 – Unknown column ‘model’ in ‘field list’   ,
After that, use “*”   To query can directly find the data;   The field “model” also exists,   I don’t know what this is,   But locate the problem, start to modify the field. Then there is no problem
Other tables I created have the ‘model’ field, but this problem does not occur
But this is still a question,   After reading the explanation of other blogs, I found that I didn’t have the answer I wanted

Hope to have big guys see trouble to teach younger brother, also hope in the future I can give myself a direct answer~

Wrong report

2021-06-10 09:52:09.327 [http-nio-8083-exec-2] ERROR o.jeecg.common.exception.JeecgBootExceptionHandler:57 - 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
### The error may exist in org/jeecg/modules/scw/scwEquipment/mapper/EquipmentMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT  id,create_by,create_time,update_by,update_time,ip_address,housen_no,house_name,date_time,temp_data,max_num,min_num,aver_num,outhumi_data,inhumi_data,outtemp_data,intemp_data,status,model  FROM scw_equipment     WHERE (housen_no = ?) ORDER BY date_time DESC
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
### The error may exist in org/jeecg/modules/scw/scwEquipment/mapper/EquipmentMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,create_by,create_time,update_by,update_time,ip_address,housen_no,house_name,date_time,temp_data,max_num,min_num,aver_num,outhumi_data,inhumi_data,outtemp_data,intemp_data,status,model  FROM scw_equipment     WHERE (housen_no = ?) ORDER BY date_time DESC
### Cause: java.sql.SQLSyntaxErrorException: 
    Unknown column 'model' in 'field list'; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
	............(useless information in the middle is omitted)
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'model' in 'field list'
    ............(useless information in the middle is omitted)

Then I go to the database and query with the SQL statement executed by the system

It turned out to be a mistake   1054 – Unknown column ‘model’ in ‘field list’

After that, use “*”   To query, you can directly find the data. This field “model” also exists,

Other tables I created have the ‘model’ field, but they can be found directly

postgresql-Database query problem-ERROR: column “t.domainid“ must appear in the GROUP BY clause or be used in an a

ERROR: column “t.domainid” must appear in the GROUP BY clause or be used in an aggregate function

The data table is as follows.

 select * from td_link_1h_d_20210427 limit 10;
 
    time    | linkid | domainid | ipversion |    upbps    |    dnbps    | updisbps | dndisbps  |  upmaxbps   |  dnmaxbps   |  upminbps   |  dnminbps   
------------+--------+----------+-----------+-------------+-------------+----------+-----------+-------------+-------------+-------------+-------------
 1619452800 |   4401 |    10001 |         4 |   147856388 |     5923764 |        0 |         0 |   166378265 |    12533696 |   123212832 |     2247349
 1619452800 |   5002 |    10001 |         6 |           0 |           0 |        0 |         0 |           4 |           4 |           0 |           0
 1619452800 |   5503 |    10001 |         6 |           0 |         149 |        0 |         0 |           2 |         194 |           0 |         102
 1619452800 |   8724 |    10001 |         6 |         238 |           0 |        0 |         0 |         609 |           2 |          23 |           0
 1619452800 |   1201 |    10001 |         4 |   255570975 |  4111481619 |  3035634 |  19703184 |   292787968 |  4751690795 |   209619623 |  3334699801
 1619452800 |   4700 |    10001 |         4 | 19626007220 |  8697825644 |        0 |         0 | 23504765993 |  9930189669 | 16586678202 |  7205462505
 1619452800 |  13249 |    10001 |         6 |     3066257 |      262560 |        0 |        12 |     5817708 |      339458 |      954397 |      217159
 1619452800 |   9156 |    10001 |         4 | 19773923700 |  2984265593 |        0 |         0 | 22335976464 |  3388045463 | 17124116138 |  2543622015
 1619452800 |  11741 |    10001 |         4 |  5628620627 | 20052950343 | 51741713 | 318854796 |  6958633624 | 23485369390 |  4357751762 | 15861228522
 1619452800 |   2700 |    10001 |         4 |   657502770 |  2246692965 |        0 |         0 |   845823645 |  2394743374 |   506699006 |  1880772827
(10 rows)

The following error occurred when querying the database by grouping statistics:

select linkid as "ID",domainid as "Domain",to_char(time::abstime,'YYYYMMDD') as "DATE",to_char(time::abstime,'HH24') as "Time",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then updisbps else 0 end )/1000/1000,3) as "上行丢弃速率Mbps" from (((select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210531 UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210601) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210607) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210608)T where (time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int )  or  (time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int) and (ipversion in (4,6)) group by linkid,time order by linkid,time;

ERROR:  column "t.domainid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select linkid as "ID",domainid as "Domain",to_char(time::abs...
                                   ^

After checking a lot of data, it is found that the error means that the domainid field must appear in group by or be used for aggregation function

so I modify the SQL statement, add the domainid field to group by, and then query again

 select linkid as "ID",domainid as "Doamin",to_char(time::abstime,'YYYYMMDD') as "DATE",to_char(time::abstime,'HH24') as "TIME",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "上行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "上行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then updisbps else 0 end )/1000/1000,3) as "上行丢弃速率Mbps" from (((select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210531 UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210601) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210607) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210608)T where (time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int )  or  (time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int) and (ipversion in (4,6)) group by linkid,time,domainid order by linkid,time,domainid;

MYSQL Enter password:ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost:3306‘

[mysqld]
# Set port 3306
port=3306
# Set the mysql installation directory
basedir=E:\\software\\\mysql\\mysql-8.0.11-winx64 # Remember to use a double slash here \\\, single slash I will get an error here, but look at other people's tutorials, some are single slash. Try it yourself
# Set the mysql database data storage directory
datadir=E:\software\\\mysql\\\mysql-8.0.11-winx64\\\Data # Same as above here
# Maximum number of connections allowed
max_connections=200
# The number of failed connections allowed. This is to prevent someone from that host from trying to attack the database system
max_connect_errors=10
# The default character set used on the server is UTF8
character-set-server=utf8
# Default storage engine to be used when creating new tables
default-storage-engine=INNODB
# Default authentication with "mysql_native_password" plugin
default_authentication_plugin=mysql_native_password
[mysql]
# Set the default character set for mysql client
default-character-set=utf8
client]
# Set the default port used by mysql clients when connecting to the server
port=3306
default-character-set=utf8

Remote connection to MySQL database error: is not allowed to connect to this MYSQL server solution

1. Change the table method.

It may be that your account does not allow remote login, only localhost. At this time, as long as you are on the localhost computer, after logging in to MySQL, change the “host” item in the “user” table in the “mysql” database, and change the name from “localhost” to “%”

mysql - u root - p

mysql > use mysql;

mysql > update  user  set host =  ' % '  where  user  =  ' root ' ;

mysql > select host, user  from  user ;

 

 

Note: Personally feel that it is not suitable!

 

[Error content]: SQL Error (2013): Lost connection to MySQL server at’waiting for initial communication packet’, system error: 0

[Error generation process]: Appears when connecting to MySQL.

[Solution]: Open my.ini, find the [mysqld] item, add a sentence after it: skip-name-resolve, save, and restart the mysql service~

 

The above is the full text introduction to solve MySQL Error (2013): Lost connection to MySQL server at waiting for initial communication packet, I hope it will be helpful for you to learn and use the database.

 

2. Authorization law.

 

1) For example, if you want myuser to use mypassword to connect to the mysql server from any host.

GRANT  ALL  PRIVILEGES  ON  * . *  TO  ' myuser ' @ ' % ' IDENTIFIED BY  ' mypassword '  WITH  GRANT  OPTION ;

FLUSH    PRIVILEGES ;

 

2) If you want to allow the user myuser to connect to the mysql server from the host whose ip is 192.168.1.6, and use mypassword as the password

GRANT  ALL  PRIVILEGES  ON  * . *  TO  ' myuser ' @ ' 192.168.1.3 ' IDENTIFIED BY  ' mypassword '  WITH  GRANT  OPTION ;

FLUSH    PRIVILEGES ;

 

3) If you want to allow the user myuser to connect to the dk database of the mysql server from the host whose ip is 192.168.1.6, and use mypassword as the password

GRANT  ALL  PRIVILEGES  ON dk. *  TO  ' myuser ' @ ' 192.168.1.3 ' IDENTIFIED BY  ' mypassword '  WITH  GRANT  OPTION ;

FLUSH    PRIVILEGES ;

 

 

The first method I used 1), and finally execute a statement mysql>FLUSH RIVILEGES to make the modification effective.

There is another method, but I haven’t tried it personally, I can find it on csdn.net, you can take a look.

Run on the machine where mysql is installed:

1. d: / mysql / bin /> mysql    - h localhost    - u root   // This should be able to enter the MySQL server

2. mysql > GRANT    ALL    PRIVILEGES    ON    * . *    TO    ' root ' @ ' % '    WITH    GRANT    OPTION   // Give any host access to data

3. mysql > FLUSH    PRIVILEGES   //The modification takes effect

4. mysql > EXIT   // Exit the MySQL server

 

In this way, you can log in as root on any other host!

[Solved] MYSQL 5.7 gruop by eRROR: this is incompatible with sql_mode=only_full_group_by

From the development of a project to the test and online operation, the team’s immature management of the project will affect the development efficiency of the project. Since I just took over the project and set up the PHP environment in Centos alone, I considered using the higher version, choosing Mysql5.7, the local development environment or the Windows inheritance environment, the PHP environment and Centos are unified 7.0, but the Mysql version is 5.3 , The local development went well, and some problems appeared under Centos, which was caused by the inconsistency between the development environment and the test|online environment.

mysql command groop by error this is incompatible with sql_mode=only_full_group_by

Take a look at the meaning of ONLY_FULL_GROUP_BY: For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal, because the column is not in the GROUP BY clause, that is to say, the column found It must appear after the group by, otherwise an error will be reported, or this field appears in the aggregate function.

Next, let’s just talk about the process of solving the problem.

First enter the Mysql client to execute the command

select @@GLOBAL.sql_mode;
+------------------------------------------------- -------------------------------------------------- -----------------------------------+ 
| @@GLOBAL.sql_mode | 
+------ -------------------------------------------------- -------------------------------------------------- ----------------------------+ 
|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 
+------ -------------------------------------------------- -------------------------------------------------- ----------------------------+

See the first paragraph of the above repentant command line —> ONLY_FULL_GROUP_BY;

There are about three solutions given on the Internet. One is to add aggregate functions to the query condition column in the statement of the program code. If you do this, it will be very troublesome, and you can use group by in the development process. You have to work hard on the sentence, and this method is fine.

It is recommended to turn off this configuration item directly.

So how to turn off this item, can it be turned off directly by executing the command on the client side, set @@global.sql_mode = ……., the execution is successful, and then execute the command to check it out. One item is gone, and then we restart the mysql service again, query again, still… haha. .

So, let’s play a trick next, let’s start from the Mysql configuration file.

Find my.cnf, edit it~Put the following commands in the right place, restart the mysql service, OK~the error is correct~

Note: There is not extra set in front.

[mysqld] 
#sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[Solved] Connections could not be acquired from the underlying database

There has been this bug in the past two days. Record the solution and summary.

Solution:

1. Driver configuration error (my bug) 

2. Database connection address error

3. Account password error

4. The database is not started or has no right to access, such as without networking 

5. The version does not correspond to (my bug) 

6. Driver jar package is not introduced into the project

My MySQL jar package is 5.0.8, but my MySQL server is 8.0, so I can change it to 5.1.46!

Mysql-connector-java-5.1.46-bin.jar download address:

Download address

We can find out the reason according to the actual situation.

[Two Methods] Ora-00904: invalid group by error identifier

I believe many students encounter the problem of invalid prompt identifier when using group by. Don’t worry, let’s summarize the common solutions to this problem:

Example: take the information of the youngest employee in each department
my SQL statement is as follows:

select name,min(age),(select deptname from dept d where d.deptid=uif.deptid)deptname from userinfo uif group by name,deptname

When you click execute, the system prompts:

ORA-00904:"deptname":Identifier is invalid

Error reason: the field after group by cannot be an alias (if you want to use an alias, you need to nest it at one level)

Solution 1:

--goup by The department name (alias: deptname ) is directly used in the deptid field of the employee information table
select name,min(age),(select deptname from dept d where d.deptid=uif.deptid)deptname from userinfo uif group by uif.name,uif.deptid

Solution 2:

--Grouping after one level of nesting
select us.name,min(us.age),us.deptname from (select name,age,(select deptname from dept d where d.deptid=uif.deptid)deptname from userinfo uif)us
group by us.name,us.deptname

How about this time?If you have any questions, please leave a message to study together~

MySQL: How to Turn on Functions

I want to customize the function today, but the mysql console reports an error
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
It means I don’t have the function function enabled

show variables like '%func%';		

Here I have turned on the function function as on

setting parameters to turn on the function function

set global log_bin_trust_function_creators=1;

Ubuntu ODBC MySQL 8 OPTION NOT Work [How to Solve]

Error due to FreeSWITCH using ODBC requirements
If you are using mysql, make sure you are using MYODBC 3.51.18 or higher and enable FLAG_MULTI_STATEMENTS

# vim /etc/odbc.ini
[freeswitch]
Driver = MySQL
SERVER = localhost
PORT = 3306
DATABASE = myDatabase
OPTION = 67108864

Note: option = 67108864   Start SQL batch, freeswitch   ODBC mode must be on

ODBC does not configure SQL preprocessing, resulting in an error when executing multiple SQL statements at the same time

ubuntu20.04

Download and install ODBC driver from MySQL website

Note that the version option of 8x is invalid after setting,

Solution demotion

mysql-connector-odbc-5.3.14-linux-ubuntu19.10-x86-64bit.tar.gz

Verification supports preprocessing methods, which are not described here for ODBC configuration

isql -v freeswitch

delete from sip_ registrations where sub_ host is null and hostname=’VM-0-13-ubuntu’ and network_ ip like ‘%’ and network_ port like ‘%’ and sip_ u

Mysql Flashback Warning: C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe

mysql: [ Warning] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe:   ignoring option  ‘–
Before opening MySQL, it was normal. After entering the password today, it has been flashing back. The warning is as follows:

mysql: [ Warning] C:\Program Files\MySQL\MySQL Server  8.0\bin\mysql.exe:   ignoring   option  ‘– no-beep’   due   to   invalid   value  ‘’.**

terms of settlement:

Open the above path (my computer is like this, the default path, if your path is not the same, it is recommended to find the installation path), find my.ini file, it is recommended to save a copy before changing to prevent modification errors. Open it in Notepad and find the following statement:

Delete the “=” in the red underlined statement and click save.

Reopen the MySQL execution box. Generally speaking, 80% of the problem is solved successfully. However, if you still flash back, right-click to find the folder where MySQL is located and enter the. EXE file from the folder chart. I hope these will help you.

Idea connect mysql error Server returns invalid timezone. Go to’Advanced’ tab and set’serverTimezone’ property

the reason

For time zone issues, the MySQL driver defaults to UTC time zone.

solution

  1. Modify time zone
# set time zone mysql> set global time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec) 
# Set the time zone to East 8 mysql> set time_zone = '+8:00'; 
Query OK, 0 rows affected (0.00 sec) 
# Refresh permissions to make the settings take effect immediately mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%time_zone%';
 +------------------+--------+
 | Variable_name | Value |
 +------------------+--------+
 | system_time_zone | EST |
 | time_zone | +08:00 | 
 +------------------+--------+
 2 rows in set (0.00 sec)
  1. Add after the url:?serverTimezone=GMT%2B8