Tag Archives: SQL

MySQL — initialize failed to generate temporary password in error log after initialization

1. Version

1) Operating system version

cat /proc/version
Linux version 3.10.0-957.1.3.el7.x86_ 64 ( [email protected] ) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-36) (GCC) ) #1 SMP Thu Nov 29 14:49:43 UTC 2018

2) Database version

mysql –version
mysql Ver 8.0.13 for linux-glibc2.12 on x86_ 64 (MySQL Community Server – GPL)

2. Problem description

In versions after 5.7.6, MySQL will be generated in the error log after initialization (specify the — initialize parameter for initialization) root@localhost Temporary password for the user. However, in this deployment, after starting mysql, it was found that no temporary password was generated in the errorlog

3. Problem solving

Later, it was found that the file in/etc/my.cnf was accidentally deleted log_ The error parameter is commented out, so that when initializing the instance, all information is sent to the standard output. When MySQL is started, it is based on the log_ The default value of error generates a log_ error

[Solved Perfectly] MySQL ERROR 1064 (42000): You have an error in your SQL syntax;

The perfect solution to error 1064

1064 error code appears when setting the root account password in MySQL

at first, I thought the user name and password could not be the same, so I changed it to 123456

and found it still couldn’t.

Solution

Enter as follows:

alter user 'root'@localhost identified by '123456';

By ‘…’ is followed by the new password.

Sqllineage Error: OSError: [Errno 99] Cannot assign requested address

Python’s data kinship tool sqllineage

View the data kinship map according to the file

Note that -h must be connected to hostname, and an error will be reported when connecting to IP OSError: [Errno 99] Cannot assign requested address

sqllineage -g -H sbider-dev-01  -p 60000 -f ./ppn2/update-data/asset_sn_day_partition/asset_sn_day_partition.sql

Help documentation

design sketch

Open the corresponding connection after execution, and you can see the following figure

phpstudy Error while setting value ‘STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

Today, I came to phpstudy. I found that MySQL could not be opened and reported this error. After a round of search, I solved the problem
Modify mysql.ini

sql_ mode =‘STRICT_ TRANS_ TABLES,NO_ ZERO_ IN_ DATE,NO_ ZERO_ DATE,ERROR_ F
OR_ DIVISION_ BY_ ZERO,NO_ AUTO_ CREATE_ USER,NO_ ENGINE_ ‘
I solved it like this. It depends on whether you can do it or not

Others have provided the following solutions
1. Phpstudy upgrade, SQL_ Mode is followed by more spaces. Just delete the spaces

2、 Directly annotate SQL_ Mode line content（ After I do this, MySQL starts and then stops, and goes back and forth ε=( ´ ο｀*))) Alas)

3、 Remove SQL_ No in mode_ AUTO_ CREATE_ USER

4、 SQL_ mode = NO_ AUTO_ CREATE_ USER,NO_ ENGING_ SUBSTITUTION

All the above are modified in mysql.ini

Mybatis-plus: How to Execute Native SQL

Define the method to execute in the mapper file

@Repository
public interface ZbArticleCEIResultPerformanceMapper extends BaseMapper<ZbArticleCEIResultPerformance> {

    @Select({"${sql}"})
    @ResultType(ArrayList.class)
    List<ZbArticleCEIResultPerformance> executeQuery(@Param("sql") String 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

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:

[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

ProgrammerAH

Programmer Guide, Tips and Tutorial