Tag Archives: SQL

Dbeaver connects hive to solve the problem that hive custom UDF functions cannot be used in SQL queries in dbeaver

1. Emergence of problems

Today, connect hive with dbeaver and test several SQL executed on the hive client yesterday. There are custom UDF, udtf, udaf, etc. in the SQL, but when the execute button is pressed in dbeaver, an error is reported, saying that it is an invalid function. But it has been registered as a permanent function in hive and has been run. How can it be invalid in dbeaver?

2. Settle

1.Put the create permanent function statement executed at the hive command line into Dbeaver and execute it again

(1) The statement to create a permanent function is as follows:

create function testudf as 'test.CustomUDF' using jar 'hdfs://cls:8020/user/hive/warehouse/testudf/TESTUDF.jar';

3.Cause (not carefully verified)

1. Because my hive client uses hive commands to connect and register functions, and because Dbeaver connects to hive with hiveserver2 service, which is beeline connection. It is said that hive client registration hiveserver2 cannot be used.
2. In the actual operation process, when I execute the instruction to register the permanent function in Dbeaver, the execution result reports that the function already exists, and it will be fine when I execute the sql statement again. So I think it’s possible that the function information was refreshed, because the function was reported to be invalid at the beginning of the execution, indicating that the sql was also executed.

(error when importing database): MySQL 2006-mysql server has gone away

Method 1 (verified to be correct):

Find the my.ini configuration file in the installation directory and add the following code:

max_allowed_packet=500M

wait_timeout=288000

interactive_timeout = 288000

The three parameters are annotated as follows:

max_ allowed_ Packet is the largest packet allowed by mysql, that is, the request you send;

wait_ Timeout is the maximum waiting time. You can customize this value. However, if the time is too short, a MySQL server has gone away #2006 error will appear after the timeout.

max_ allowed_ The packet parameter is used to control the maximum length of its communication buffer

Method 2 (online extract is not verified temporarily):

Open tools in the menu of Navicat, select server monitor, then select in the left column, and click variable in the right column to find max_ allowed_ Packet and increase its value.

In the Chinese version: in the menu, tools – & gt; Server monitoring – & gt; Tick in front of the database list on the left – & gt; Find Max in the variable on the right_ allowed_ Packet, increase the value, such as 999999999

MySQL settings are case insensitive:

MySQL under Linux is case sensitive by default. You can make MySQL case insensitive by the following settings:
1. Log in with root and modify/etc/my.cnf
2. Under the [mysqld] node, add a line: lower_ case_ table_ Names = 1
3. Restart MySQL;

Where lower_ case_ table_ The name = 1 Parameter defaults to 1 in windows and 0 in UNIX. Therefore, the problem will not be encountered in windows. Once it reaches Linux, the cause of the problem will occur (especially when MySQL names the table with uppercase letters, but when querying with uppercase letters, there will be an error that cannot be found, which is really puzzling)

View the maximum length of the communication buffer:

show global variables like ‘max_ allowed_ packet’;

The default maximum is 1m. You can modify the maximum length of the communication buffer to 16m:

set global max_ allowed_ packet=1024102416;

Query again:

Next, import again. Import succeeded!

Note: the modification is only valid for the current. If MySQL is restarted, it will still restore the original size. If you want to take effect permanently, you can modify the configuration file and add or modify the configuration in my.ini (under Windows) or my.cnf (under Linux):

max_ allowed_ packet = 16M

Restart MySQL service

Mybatis plus configuration console prints complete SQL statement with parameters

Solution
if it is application.yml

#mybatis-plus configures the console to print full SQL statements with parameters
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

If it is application.properties, add:

#mybatis-plus configures the console to print full SQL statements with parameters
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

[Solved] MybatisPlusException: Error: Method queryTotal execution error of sql

Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :

Error reason:
the user-defined SQL is written in mapper, where is added too much, and the user-defined SQL is transferred into querywrapper, where cannot be added
error code:

String customSql="select * from ("+queryAll+") as q where ${ew.customSqlSegment}";
    @Select(customSql)
    IPage<BranchBasic> baseQuery(Page<Object> objectPage, @Param(Constants.WRAPPER)QueryWrapper queryWrapper);
   

Correct code:
as both case and upper case are OK

String customSql="select * from ("+queryAll+") as q ${ew.customSqlSegment}";
    @Select(customSql)
    IPage<BranchBasic> baseQuery(Page<Object> objectPage, @Param(Constants.WRAPPER)QueryWrapper queryWrapper);

About installing SQL server 0 in Windows 10 20h2 × 84bb0001 is one of the solutions

About installing SQL server 0 in Windows 10 20h2 × 84bb0001 is one of the solutions

Background solutions

Background

A brand new computer, upgrade to 20h2, and want to install a SQL Server 2005, the result is an error “try to execute unauthorized operation, error code 0 × 84BB0001”

resolvent

Those problems on the Internet, such as uninstalling and completely re installing, are different from those I encountered, but the way to report errors is the same. Let’s draw a conclusion directly

Edge fuckin ‘! No updates!!!!! After updating edge, it will be ready. Please refer to the details https://docs.microsoft.com/en-us/answers/questions/148323/windows-10-version-20h2-install-error-code-0x84bb0.html

Solution of Greenplum query calling function error

We may often encounter the need to call custom functions in query, but in Greenplum, if you have query in the function and then call the function in query, you will report it wrong.

Example:
create function

iap=# create or replace function f1() returns text as $$
declare
c1 text;
begin
execute 'select info from tt1 limit 1' into c1;
return c1;
end;
$$ language plpgsql;

Invocation in query:

iap=# select f1() from tt1;
ERROR:  query plan with multiple segworker groups is not supported
HINT:  likely caused by a function that reads or modifies data in a distributed table
CONTEXT:  SQL statement "select info from tt1 limit 1"
PL/pgSQL function f1() line 5 at EXECUTE statement

This is due to the feature of MPP in greenplus. Only part of the data is saved in each node. GP6 supports copying tables. Then we need to change the table into a copying table to ensure that each node has a complete data.

In addition, we need to modify the function to immutable.

Change the table distribution to replicated type

iap=# alter table tt1 set Distributed REPLICATED;
ALTER TABLE

– modify the function to immutable type

iap=# create or replace function f1() returns text as $$
declare
c1 text;
begin
execute 'select info from tt1 limit 1' into c1;
return c1;
end;
$$ language plpgsql immutable;
CREATE FUNCTION

Call again:

iap=# select f1() from tt1 limit 1;
                f1
----------------------------------
 d810ed19ec188ddf3af8a14dbd341c3c
(1 row)

Summary:
, if you need to call UDF function in query, if you encounter “ERROR: query plan with multiple segworker groups is is,” then the solution is as follows:

    modify table to copy table; Modify the function to immutable type

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

[How to Solve] Content with element type ‘mapper’ must match

Today, in the mapper. XML file, I mistakenly annotated it with /* * code description */ and then reported this error
the specific exception is as follows

Caused by: org.xml.sax.SAXParseException; lineNumber: 134; columnNumber: 10, The element type "mapper" must match "(cache-ref|cache|resultMap*|parameterMap*|sql*|insert*|update*|delete*|select*)+"。

error code 

   /** Public conditions */
    <sql id="commonIfWhere">
        <if test='params.projectId != null and params.projectId != ""'>
            and project_id = #{params.projectId}
        </if>
        <if test='params.id!= null and params.id != ""'>
            and id = #{params.id}
        </if>
    </sql>

after correction

   <!-- Public conditions -->
    <sql id="commonIfWhere">
        <if test='params.projectId != null and params.projectId != ""'>
            and project_id = #{params.projectId}
        </if>
        <if test='params.id!= null and params.id != ""'>
            and id = #{params.id}
        </if>
    </sql>

The problem was solved smoothly

[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~

1093 – You can’t specify target table ‘table’ for update in FROM clause

The general meaning is that you can’t select some values in the table first, and then update the table (in the same statement)

 
A temporary table can be introduced for operation

delete from push_ plans WHERE id in  
(
     select id from  
     (
         select id from push_ plans WHERE   area_ id = 0
         
     ) as t
)