Category Archives: MySQL

[Solved] Sequelize DatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP

Error report after project deployment

SequelizeDatabaseError: ER_ WRONG_ FIELD_ WITH_ GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘mysql.category.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_ full_ group_ by

To put it simply, the output result is called target list, which is the field followed by select. There is another place, group by column, which is the field followed by group by. Because only_FULL_GROUP_By is turned on set, so if a field does not appear in the target list and group by fields at the same time, or the value of the aggregate function, the SQL query is considered illegal by MySQL and an error will be reported.

Error source code:

// Find All
const categoryAll = async (ctx) => {
  const resData = await Category.findAll({
    attributes: ['name', 'id', [sequelize.fn('COUNT', sequelize.col('name')), 'count']],
    // group: 'name', // Key
    where: {
      articleId: { [Op.not]: null }
    },
    order: [[sequelize.fn('COUNT', sequelize.col('name')), 'desc']]
  })
  ctx.body = successResult(resData)
}

Solution:

Global SQL modification is mentioned in several reference articles_ Mode, but I failed to modify it with this method. Even if it succeeds, it is temporary and will reappear after MySQL is restarted
so I chose another method to modify the MySQL configuration file and add SQL manually_Mode is mandatory. Only_FULL_GROUP_By is not required property
1. Confirm which configuration file MySQL loads. You can use the following methods to confirm

mysql –verbose –help | grep my.cnf

Directory where my configuration is located:

2. Modify the configuration and add the following content at the end of the configuration file

[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

Note: there is no keyword [mysqld] in my configuration file. If there is no keyword, an error will be reported
then restart MySQL.

[Solved] Docker Desktop Start MongoDb Error: Error: spawn C:\Windows\system32\cmd.exe; ENOENT

Complete error reporting information

Complete error reporting information:
cannot start docker-compose application. Reason: error invoking remote method ‘compose action’: error: spawn C:\windows\system32\cmd.exe enoent

Solving process

When installing MongoDB, the installation method is to run the command in the CMD window to pull the MongoDB image for installation, and habitually delete the configuration file after installation. Therefore, it is thought that this configuration file may be missing. Therefore, MongoDB is deleted from the docker desktop, and a path is selected for reinstallation.

Solution (for reference only)

Create a folder under any path (here is my path)

D:\Program Files\docker-mongodb

And put the docker-compose.yml configuration file in the folder (the following is the content of the configuration file)

version: '3.7'
services:
  mongodb_container:
    image: mongo:latest
    environment:
      MONGO_INITDB_ROOT_USERNAME: root
      MONGO_INITDB_ROOT_PASSWORD: rootpassword
    ports:
      - 27017:27017
    volumes:
      - mongodb_data_container:/data/db

volumes:
  mongodb_data_container:

Open CMD as an administrator and enter the path to the docker-compose.yml configuration file, that is, execute the command docker-compose up – d
in the folder just created. Open the docker desktop again and you can use mongodb normally
enter Mongo admin – U root – P password for testing

be careful

The mongodb version number and root password here can be modified in the configuration file of. YML

[Solved] Django configures MySQL Error: NameError: name ”_MySQL ‘is not defined

first install mysql, and then modify Django’s setting. Py
The original:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR/'db.sqlite3',
    }
}

Amend to read:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',  # Database engine
        'NAME': 'database name',
        'USER': 'User name',
        'PASSWORD': 'password',
        'HOST': '127.0.0.1', # ip of the host where mysql service is located
        'PORT': '3306', # mysql service port
    }
}

add environment variables (address NameError: name ‘_mysql’ is not defined)
Add to the environment variable:

export DYLD_LIBRARY_PATH=/usr/local/mysql/lib/

Then it’s done

How to Solve postgres Errror: ERROR #42883 operator does not exist: smallint ~~ unknown

Problem background

What is written in the code is general. After an error is reported, it is found that the value in like is actually an integer, but the code is spliced with strings. Therefore, in fact, when postgre queries, SQL is executed with integer columns and string parameters, and this error is reported.

Namely column LIKE ‘%0%’

The same is true for SQL queries:

Solution:

Strings can be queried vaguely, but when querying, if the column is an integer, just use the equal sign directly. Judge in the code. If the column is an integer, splice it=  Don’t use like

Elasticsearch imports MySQL data Error when executing the bin/logstash – f command

 

Error report 1

Problem Description:

  Unable to connect to database. Tried 1 times {:error_message=>”Java::JavaSql::SQLException: Access denied for user ‘root’@’localhost’ (using password: YES)”}

Solution:

Unable to connect to database. It is possible that the configuration file, database name or password of the imported database are incorrect (this is the wrong password)

Check the jdbc.conf file configured in the logstash decompression directory

Error report 2

Problem Description:

Logstash could not be started because there is already another instance using the configured data directory.   If you wish to run multiple instances, you must change the “path.data” setting.

Logstash cannot be started because another instance already uses the configured data directory. If you want to run multiple instances, you must change the “path. Data” setting.

Solution:

You need to specify your path.data and add it after starting the Import command   — path.data=/root/

  [ root@redis logstash-7.4.2]# bin/logstash -f /usr/local/logstash/logstash-7.4.2/jdbc.conf –path.data=/root/

How to Solve Nacos startup error

Error 1:

Unable to start web server; nested exception is org.springframework.boot.web.server.WebServerExcepti

Solution:
Open the startup file, and different operating systems open different files. Open startup.cmd on windows, open startup.sh for others, find set MODE, and change it to standalone mode.

Error 2:

Error creating bean with name ‘memoryMonitor’ defined in URL [jar: File:/D:/Nacos/nacos203/target/Nacos server. Jar!/boot-inf/lib/nacos-config-2.0.3. Jar
Solution:

Database connection If there is a problem, the correction method is as follows:
open the application.properties file in the nacos/conf/ folder , find the database connection address, pay attention to whether there is &serverTimezone=UTC after it, and add it if not.
db.url.0=jdbc:mysql: …/lamp_nacos?characterEncoding=utf8&connectTimeout=1000&socketTimeout=3000&autoReconnect=true &serverTimezone=UTC

Mysqlclient Error: ERROR: No matching distribution found for mysqlclient/OSError: mysql_config not found

Error: no matching distribution found for mysqlclient solution

Red hat/CentOS system

sudo yum install python3-devel mysql-devel

Debian/Ubuntu system

 sudo apt-get install python3-dev default-libmysqlclient-dev build-essential

Install mysqlclient

pip isntall mysqlclient

OSError: mysql_config not found Solution

  ERROR: Command errored out with exit status 1:
   command: /usr/local/python3/bin/python3.9 -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-ubc4i40h/mysqlclient_b88fc9c0c9484503a326b0399e91838e/setup.py'"'"'; __file__='"'"'/tmp/pip-install-ubc4i40h/mysqlclient_b88fc9c0c9484503a326b0399e91838e/setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(__file__) if os.path.exists(__file__) else io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-pip-egg-info-me3mh3fr
       cwd: /tmp/pip-install-ubc4i40h/mysqlclient_b88fc9c0c9484503a326b0399e91838e/
  Complete output (10 lines):
  /bin/sh: mysql_config: command not found
  Traceback (most recent call last):
    File "<string>", line 1, in <module>
    File "/tmp/pip-install-ubc4i40h/mysqlclient_b88fc9c0c9484503a326b0399e91838e/setup.py", line 17, in <module>
      metadata, options = get_config()
    File "/tmp/pip-install-ubc4i40h/mysqlclient_b88fc9c0c9484503a326b0399e91838e/setup_posix.py", line 47, in get_config
      libs = mysql_config("libs_r")
    File "/tmp/pip-install-ubc4i40h/mysqlclient_b88fc9c0c9484503a326b0399e91838e/setup_posix.py", line 29, in mysql_config
      raise EnvironmentError("%s not found" % (mysql_config.path,))
  OSError: mysql_config not found

Ubuntu system

sudo apt-get install libmysqlclient-dev

centOS7

yum install mysql-devel gcc gcc-devel python-deve

MySQL Workbench: Error Code: 1175 [How to Solve]

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.   To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Error code: 1175. You are using secure update mode, and you try to update a table that uses key columns without where. To disable safe mode, in preferences – & gt; Switch this option in the SQL editor and reconnect.

Solution: it can be solved with one statement.

SET SQL_SAFE_UPDATES = 0;

After executing the statement, execute the update operation again.

Docker: How to Solve MYSQL8 & Navicat remote connection error

1. Download Image

docker pull mysql:8.0.26

2. Operation container

docker run -p 3306:3306 --restart=always -e MYSQL_ROOT_PASSWORD=ang123 --name mysql -d mysql:8.0.26

3. At this time, an error will be reported when using Navicat to log in, because the encryption methods of mysql8 and 5 are different, and the encryption method needs to be modified

4. Enter MySQL container

docker exec -it mysql bash

5. Log in to MySQL and modify the encryption method

mysql -uroot -pang123;

use mysql;

#Modify encryption method
alter user 'root'@'%' identified with mysql_native_password by 'root';

#View modified data
select host,user,plugin from user;

Revised as follows:

6.At this time, the local Navicat remote connection to MySQL in Linux still reports an error 1045, but the password is correct, and the grant authorization does not solve the problem

The final solution is as follows. Just change the password again. The password is still the original password

ALTER USER 'root'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'ang123';

#Make changes effective
flush privileges;

7. At this time, Navicat can be used for remote connection

How to Solve Azkaban startup error

Environment.
One virtual machine (web and exector both on one machine)
MySQL 8.x (most of the later problems are due to him)
Hive-3.1.2
Azkaban-exec-server-3.84.4
Azkaban-web-server-3.84.4
1. SLF4J problems:
ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Class path contains multiple SLF4J bindings.

2021/09/30 10:02:31.820 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Class path contains multiple SLF4J bindings.
2021/09/30 10:02:31.820 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Found binding in [jar:file:/opt/module/azkaban/azkaban-exec/lib/slf4j-log4j12-1.7.21.jar!/org/slf4j/impl/StaticLoggerBinder.class]
2021/09/30 10:02:31.820 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Found binding in [jar:file:/data/soft/apache-hive-3.1.2-bin/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
2021/09/30 10:02:31.820 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
2021/09/30 10:02:31.823 +0800 ERROR [StdOutErrRedirect] [Azkaban] SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

Cause: conflict with log4j package in hive
solution: delete Azkaban exec/lib/slf4j-log4j12-1.7.21.jar or add. Bak suffix
command:

cd /azkaban-exec/lib
mv slf4j-log4j12-1.7.21.jar slf4j-log4j12-1.7.21.jar.bak

2. DB connection problems(MySQL 8)
ERROR [MySQLDataSource] [Azkaban] Failed to find write-enabled DB connection.

2021/09/30 10:23:33.973 +0800 ERROR [MySQLDataSource] [Azkaban] Failed to find write-enabled DB connection. Wait 15 seconds and retry. No.Attempt = 1
java.sql.SQLException: Cannot create PoolableConnectionFactory (java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long)
        at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2294)

Reason: there is a problem with the MySQL driver package. The MySQL driver package provided by Azkaban cannot connect to MySQL 8. Just change to mysql-connector-java-5.1.47, and then a usessl error will be reported. This can be changed to MySQL. Database = Azkaban?Usessl = false in azkaban.properties. (both web and exector will report this error. Just modify it in turn)

3.
ERROR [PluginCheckerAndActionsLoader] [Azkaban] plugin path plugins/triggers doesn’t exist!
ERROR [ExecutorManager] [Azkaban] No active executors found

2021/09/30 10:47:52.799 +0800 ERROR [PluginCheckerAndActionsLoader] [Azkaban] plugin path plugins/triggers doesn't exist!
2021/09/30 10:47:52.838 +0800 INFO [AzkabanWebServer] [Azkaban] Setting timezone to Asia/Shanghai
2021/09/30 10:47:52.838 +0800 INFO [AzkabanWebServer] [Azkaban] Registering MBeans...
2021-09-30T10:47:52,869 INFO [main] azkaban.server.MBeanRegistrationManager - Bean azkaban.jmx.JmxJettyServer registered.
2021-09-30T10:47:52,871 INFO [main] azkaban.server.MBeanRegistrationManager - Bean azkaban.jmx.JmxTriggerManager registered.
2021-09-30T10:47:52,890 INFO [main] azkaban.server.MBeanRegistrationManager - Bean azkaban.jmx.JmxExecutorManager registered.
2021-09-30T10:47:52,915 INFO [main] azkaban.server.MBeanRegistrationManager - Bean org.apache.log4j.jmx.HierarchyDynamicMBean registered.
2021/09/30 10:47:52.915 +0800 INFO [AzkabanWebServer] [Azkaban] ************* loginLoggerObjName is null, make sure there is a logger with name azkaban.webapp.servlet.LoginAbstractAzkabanServlet
2021/09/30 10:47:52.916 +0800 INFO [ExecutorManager] [Azkaban] Initializing executors from database.
2021/09/30 10:47:52.941 +0800 ERROR [ExecutorManager] [Azkaban] No active executors found
2021/09/30 10:47:52.942 +0800 ERROR [StdOutErrRedirect] [Azkaban] Exception in thread "main"
2021/09/30 10:47:52.942 +0800 ERROR [StdOutErrRedirect] [Azkaban] azkaban.executor.ExecutorManagerException: No active executors found
2021/09/30 10:47:52.942 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.executor.ActiveExecutors.setupExecutors(ActiveExecutors.java:52)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.executor.ExecutorManager.setupExecutors(ExecutorManager.java:192)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.executor.ExecutorManager.initialize(ExecutorManager.java:127)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.executor.ExecutorManager.start(ExecutorManager.java:141)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.webapp.AzkabanWebServer.launch(AzkabanWebServer.java:234)
2021/09/30 10:47:52.943 +0800 ERROR [StdOutErrRedirect] [Azkaban]       at azkaban.webapp.AzkabanWebServer.main(AzkabanWebServer.java:227)

ERROR [PluginCheckerAndActionsLoader] [Azkaban] plugin path plugins/triggers doesn’t exist!
(you can refer to this.) https://blog.csdn.net/liumu243/article/details/81288884 ）
this error can be ignored or run. The main problem here is that the exector is not activated

curl -G "localhsot:12321/executor?action=activate" && echo
//Appears to prove successful activation
{status:success}

Administrative Region:
https://192.168.xx.xx:8443
http://192.168.xx.xx:8081