Category Archives: MYSQL

[Solved] Error when connecting to MySQL when creating Django project

After changing the data configuration in the setting file of the project:

DATABASES = {

    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'cesi_DB',    #Your database name
        'USER': 'sheng', # your database username
        'PASSWORD': 'xxoo', # your database password
        'HOST': '10.0.0.11', # your database host, leave blank to default to localhost
        'PORT': '3306', # your database port
    }
}

Error Message:

    'Did you install mysqlclient or MySQL-python?' % e
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named 'MySQLdb'.
Did you install mysqlclient or MySQL-python?

  Solution:

The engine that needs to change the database:

MYsqlDB ==》pymysql

In application (app01)__init__Add to the document:

import pymysql

pymysql.install_as_MySQLdb()

 

.NET core 2.0 preview uses Mysql to report errors [Solved]

Shotscreen

Development environment:

vs2017 preview 15.3+MySql.Data.EntityFrameworkCore 7.0.7-m61

Error reporting:

Happened System.TypeLoadException
HResult=0x80131522
Message=Method ‘Clone’ in type ‘MySQL.Data.EntityFrameworkCore.Infraestructure.Internal.MySQLOptionsExtension’ from assembly ‘MySql.Data.EntityFrameworkCore, Version=7.0.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d’ does not have an implementation.
Source=<Unable to calculate the source of the anomaly>
StackTrace:
at MySQL.Data.EntityFrameworkCore.Extensions.MySQLDbContextOptionsExtensions.UseMySQL(DbContextOptionsBuilder optionsBuilder, String connectionString, Action`1 MySQLOptionsAction)
at testproj.MemberPoint.Startup.<ConfigureServices>b__4_0(DbContextOptionsBuilder options) in D:\testproj\testprojSVN\CommonFile\trunk\NetCore\Business\testproj.MemberPoint\Startup.cs:line 28
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass0_0`1.<AddDbContext>b__0(IServiceProvider p, DbContextOptionsBuilder b)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.DbContextOptionsFactory[TContext](IServiceProvider applicationServiceProvider, Action`2 optionsAction)
at Microsoft.Extensions.DependencyInjection.EntityFrameworkServiceCollectionExtensions.<>c__DisplayClass5_0`1.<AddCoreServices>b__0(IServiceProvider p)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactoryService(FactoryService factoryService, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitSingleton(SingletonCallSite singletonCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.<>c__DisplayClass17_0.<RealizeService>b__0(ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType)
at Microsoft.Extensions.Internal.ActivatorUtilities.GetService(IServiceProvider sp, Type type, Type requiredBy, Boolean isDefaultParameterRequired)
at Microsoft.AspNetCore.Mvc.Controllers.ControllerActivatorProvider.<>c__DisplayClass5_0.<CreateActivator>b__0(ControllerContext controllerContext)
at Microsoft.AspNetCore.Mvc.Controllers.ControllerFactoryProvider.<>c__DisplayClass5_0.<CreateControllerFactory>g__CreateController0(ControllerContext controllerContext)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__14.MoveNext()

 

After various searches, I found the answer here: https://blogs.msdn.microsoft.com/dotnet/2017/05/12/announcing-ef-core-2-0-preview-1/

Note the passage.

If you are using a third party database provider, then check to see if they have released an update that depends on 2.0.0-preview1-final. If they have, then just upgrade to the new version. If not, then you will not be able to upgrade since version 2.0 contains several breaking changes and 1.* providers are not expected to work with it.

So the answer is: there is no answer. Sit back and wait for MySQL.Data.EntityFrameworkCore to be updated.

Error in Python compilation on MAC no module named mysqldb

Compile error for python on Mac: No module named MySQLdb

You installed python

You did brew install mysql

You did export PATH=$PATH:/usr/local/mysql/bin

And finally, you did pip install MySQL-Python

appear:

Collecting mysql-python
Downloading MySQL-python-1.2.5.zip (108kB)
100% |████████████████████████████████| 112kB 324kB/s
Complete output from command python setup.py egg_info:
sh: mysql_config: command not found
Traceback (most recent call last):
File “<string>”, line 1, in <module>
File “/private/tmp/pip-build-fwrZHD/mysql-python/setup.py”, line 17, in <module>
metadata, options = get_config()
File “setup_posix.py”, line 43, in get_config
libs = mysql_config(“libs_r”)
File “setup_posix.py”, line 25, in mysql_config
raise EnvironmentError(“%s not found” % (mysql_config.path,))
EnvironmentError: mysql_config not found

Solution:

Reinstall MYSQL

 

MySQLAccess denied for user ‘root’@’localhost’ [How to Solve]

The error information is as follows:

[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Cause of the problem.

There are empty users in the database

 

Solution:

1 Disable mysql service: # service mysql stop

2 Enter the command: # mysqld_safe –user=mysql –skip-grant-tables –skip-networking &

3 Login to the database: # mysql -u root mysql

4 mysql> use mysql;

5 mysql> select user,host,password from user;

6 Delete the empty user from the above query: mysql> delete from user where user=”;

7 Quit the database: mysql> quit

8 Start mysql service: # service mysql start

9 Log back in: # service mysql start # mysql -u root -p

 

[How to Solve] SQLite database error: no such column

Add columns in the SQLite database creation statement, and an error is reported after running: no such column

On the premise of grammatical norms, i.e

//SQLite database creation, comma and space must be strict
String CREATE_NOTE = "create table "
			+ NotepadEntity.TABLE_NOTE
			+ " (" + NotepadEntity.NOTE_ID + " integer primary key autoincrement,"
			+ NotepadEntity.NOTE_TITLE + " varchar not null," //此列为修改语句后增加
			+ NotepadEntity.NOTE_CONTENT + " text not null,"
			+ NotepadEntity.NOTE_CREATE_DATE + " text);";

SQLiteDatabase db;
db.execSQL(CREATE_NOTE);

It still fails to compile because the SQLite create command has been executed once, and the database file already exists. Even if the statement is changed, it is not executed again

Solution:

Open the DDMS tool and locate the file   Explorer “, navigate to the database file list in the project file (expand to/data/data/& lt; package name>/ databases）

 

(you can export the database file to the computer, and use SQLite database browser and other tools to check whether there are columns of data not created)

Delete the database file and its journal file, return to the project and run again. If the program detects that the database is empty, execute the create command and compile

At this time, export the database file under DDMS, view the list structure, create the new column successfully, and solve the problem

Error in installing MySQL Python on MAC [Solved]

Error Message:

EnvironmentError: mysql_config not found

Command “python setup.py egg_info” failed with error code 1 in /private/var/folders/nh/j05szmpj19368dd9_y6whgy40000gq/T/pip-install-n9Jonx/mysql-python/

I looked up the command to execute an environment variable online:

export PATH=$PATH:/usr/local/mysql/bin

Then another error was reported:

#include “my_config.h”
^~~~~~~~~~~~~
1 error generated.
error: command ‘cc’ failed with exit status 1

Finally, I used two methods

One is to first execute :

brew install mysql

Be sure to install through brew

Second, download directly:

https://dev.mysql.com/downloads/connector/python/

After downloading, you can access:

/ usr/local/mysql/bin    In this folder, you can see that there is a file called mysql_Config file

 

Since I did both things at the same time, I can’t judge which is right and which is wrong, or both, and then execute it again:

export PATH=$PATH:/usr/local/mysql/bin

Latest run: Good install MySQL-Python

 

Oracle ORA-00936: missing expression [How to Solve] 

INSERT INTO S_USR_MEMBER_TEMP
       (SESSION_ID
    ,USR_CD
     ,SEQ_NO
    ,MEMBER_KNAME
       ,MEMBER_KNAME2
       ,MEMBER_NAME
       ,MEMBER_NAME2
       ,MEMBER_ZIP
       ,MEMBER_ADR1
       ,MEMBER_ADR2
       ,MEMBER_ADR4
       ,MEMBER_ADR5
       ,MEMBER_TEL
       ,MEMBER_KEITAITEL
       ,MEMBER_YEAR
       ,MEMBER_MON
       ,MEMBER_DAY
       ,KINMUSAKI_KNAME
       ,KINMUSAKI_NAME
       ,KINMUSAKI_ZIP
       ,KINMUSAKI_ADR1
       ,KINMUSAKI_ADR2
       ,KINMUSAKI_ADR4
       ,KINMUSAKI_ADR5
    ,UPDATE_DATE)
 (
SELECT  '686321766'
      ,'00000000000000000048'
      ,(1+(row_number() over(order by S_USR_MEMBER.SEQ_NO))) rowno
      ,MEMBER_KNAME
      ,MEMBER_KNAME2
      ,MEMBER_NAME
      ,MEMBER_NAME2
      ,MEMBER_ZIP
      ,MEMBER_ADR1
      ,MEMBER_ADR2
      ,MEMBER_ADR4
      ,MEMBER_ADR5
      ,MEMBER_TEL
      ,MEMBER_KEITAITEL
      ,MEMBER_YEAR
      ,MEMBER_MON
      ,MEMBER_DAY
      ,KINMUSAKI_KNAME
      ,KINMUSAKI_NAME
      ,KINMUSAKI_ZIP
      ,KINMUSAKI_ADR1
      ,KINMUSAKI_ADR2
      ,KINMUSAKI_ADR4
      ,KINMUSAKI_ADR5
      ,20180903
  FROM S_USR_MEMBER 
 WHERE USR_CD = '00000000000000000048'
ORDER BY S_USR_MEMBER.SEQ_NO )

This code is the correct code. A value was added before select, and an error was always reported

There are only two methods to be determined:

There are only two kinds of syntax for insert
1. Insert into table 1 (field name 1, field name 2,…) values
2. Insert into table 1 select field from table 2 where

 

MySQL 5.7 error reporting from database 1032 [How to Solve]

1. Introduction:

During MySQL 5.7 multi-source replication and master-slave synchronization, the master database updates a piece of data, and the slave database has been deleted, resulting in the master-slave disconnection

Last_Errno: 1032

The 1032 error of MySQL master-slave synchronization generally means that the data to be changed does not exist and SQL_The log extracted by thread cannot be applied, so an error is reported, resulting in synchronization failure

(update, delete, insert a piece of deleted data). The 1032 error itself has no impact on data consistency, and the biggest impact is the synchronization failure

2. Solution:

MySQL version 5.7.15-log, binlog mode is row

Show slave status \ g, you can see the following error reports:

 1  Last_Errno: 1032
 2                    Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction '1699
 3 7e5e-5707-11e7-a702-94188201787c:161982297' at master log mysql-bin.000309, end_log_pos 774308000. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.                 Skip_Counter: 0
 4           Exec_Master_Log_Pos: 774271552
 5               Relay_Log_Space: 1266940262
 6               Until_Condition: None
 7                Until_Log_File: 
 8                 Until_Log_Pos: 0
 9            Master_SSL_Allowed: No
10            Master_SSL_CA_File: 
11            Master_SSL_CA_Path: 
12               Master_SSL_Cert: 
13             Master_SSL_Cipher: 
14                Master_SSL_Key: 
15         Seconds_Behind_Master: NULL
16 Master_SSL_Verify_Server_Cert: No
17                 Last_IO_Errno: 0
18                 Last_IO_Error: 
19                Last_SQL_Errno: 1032
20                Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction '1699
21 7e5e-5707-11e7-a702-94188201787c:161982297' at master log mysql-bin.000309, end_log_pos 774308000. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

 

See error log:

vim error.log

2018-10-26T01:55:00.133136+08:00 16179 [ERROR] Slave SQL for channel 'master_6': Worker 3 failed executing transaction '16997e5e-5707-11e7-a702-94188201787c:161982297' at 
master log mysql-bin.000309, end_log_pos 774308000; Could not execute Update_rows event on table test.test_log; Can't find record in 'test_log', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000309, end_log_pos 774308000, Error_code: 1032

Method 1: skip errors

1) Skip this error first and let the master-slave synchronization return to normal（ Or n events (skip one by one)

set global sql_slave_skip_counter=1;

start slave sql_thread for channel ‘master_6’;

After the slave is up, recover the data and re insert it from the database

2）The most recent failure being: Worker 3 failed executing transaction ‘169921 7e5e-5707-11e7-a702-94188201787c:161982297′

In gtid mode, you can also:

set gtid_next=’169921 7e5e-5707-11e7-a702-94188201787c:161982297′;

begin;

commit;

set gtid_next=’automatic’;

stop slave   sql_thread for channel ‘master_6’;

 

start slave sql_thread for channel ‘master_6’;

Method 2: restore the deleted data

According to the error prompt, find the SQL of this data through mysqlbinglog. The location of this event in the master binlog of the master server is mysql-bin.000309, end_log_pos77430800

 1 [root@xxxxx binlog]# mysqlbinlog  --no-defaults --stop-position=774308000 mysql-bin.000309 -vv |grep -A 20 774308000
 2 #181026  1:55:00 server id 3663306  end_log_pos 774308000 CRC32 0x2969563a     Update_rows: table id 52745 flags: STMT_END_F
 3 
 4 BINLOG '
 5 dAPSWxPK5TcAWgAAAIr+Ji4AAAnOAAAAAAEAB21vbml0b3IAEWpvYl9leGVjdXRpb25fbG9nAAsP
 6 Dw8PDwMPDwMRERCgAJAB/AP8A8gAUACAPgAAgAbDz1ww
 7 dAPSWx/K5TcAFgIAAKAAJy4AAAnOAAAAAAEAAgAL/////4D8JDZlOGJjNGZlLTlmYzktNDllYS1h
 8 NjliLWE4YmNjMWZjOGJlZiQAb21zVXBkYXRlV2FyZWhvdXNlT3JkZXJUYXNrU3RhdHVzSm9iZgBv
 9 bXNVcGRhdGVXYXJlaG91c2VPcmRlclRhc2tTdGF0dXNKb2JALUAwQC1AUkVBRFlALUAxMC4xNTcu
10 MzIuMzlALUA2NTYyYzQ5YS0zMjQyLTQyNWQtODdhZC1lZTA4N2M0NmYzN2UXAGRwcHZkb2NzZXIx
11 OC5zZXBob3JhLmNuDDEwLjE1Ny4zMi4zOQAAAAAOTk9STUFMX1RSSUdHRVIAAAAAW9IDdID4JDZl
12 OGJjNGZlLTlmYzktNDllYS1hNjliLWE4YmNjMWZjOGJlZiQAb21zVXBkYXRlV2FyZWhvdXNlT3Jk
13 ZXJUYXNrU3RhdHVzSm9iZgBvbXNVcGRhdGVXYXJlaG91c2VPcmRlclRhc2tTdGF0dXNKb2JALUAw
14 QC1AUkVBRFlALUAxMC4xNTcuMzIuMzlALUA2NTYyYzQ5YS0zMjQyLTQyNWQtODdhZC1lZTA4N2M0
15 NmYzN2UXAGRwcHZkb2NzZXIxOC5zZXBob3JhLmNuDDEwLjE1Ny4zMi4zOQAAAAAOTk9STUFMX1RS
16 SUdHRVIBAAAAW9IDdFvSA3Q6Vmkp
17 '/*!*/;
18 ### UPDATE `test`.`test_log`
19 ### WHERE
20 ###   @1='6e8bc4fe-9fc9-49ea-a69b-a8bcc1fc8bef' /* VARSTRING(160) meta=160 nullable=0 is_null=0 */
21 ###   @2='omsUpdateWarehouseOrderTaskStatusJob' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
22 ###   @3='omsUpdateWarehouseOrderTaskStatusJob@-@0@-@READY@-@1@-@6562c49a-3242-425d-87ad-ee087c46f37e' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */

 

The statement for the transaction is: UPDATE `test`. `test_log` where @1=”, @2=” (@1 is the first field, @2 is the second field)

To be missing this data from the library, change the update in the above binglog to insert

Do not log binlog.

set sql_log_bin=0;

insert  into `test`.`test_log`   values() ;

set sql_log_bin=1;

start slave sql_thread for channel ‘master_6’;

[Solved] Python Connet MYSQL Error: MySQL dB_exceptions.OperationalError: (2059, < NULL>).

When Python connects to MySQL data:
an error message will prompt MySQL dB_exceptions.OperationalError: (2059, < NULL>)。

Python package:

mysqlclient

Analysis reason:
mysql8.0 provides a new authentication encryption method, caching_sha2_Password, it is recommended to update to the latest connector and client. However, it also provides another option, that is, to use the encryption method MySQL of the previous version_native_password。 One of the solutions to mysqlexceptions.operationalerror: (2059, null):

Solution
modify the password and use MySQL instead_native_Password encryption method. After flush privileges

 

Oracle Error: ORA-01843: not a valid month [Solved]

Client: select * from sys.nls_session_parameters;
server: select * from sys.nls_database_parameters;

Error in executing script: ora-01843: not a valid month
confirmation problem: client NLS_date_Language is American
solution: modify NLS_date_The language parameter is Chinese. Data such as’ November may – 18 10.56.50.139000 am ‘is successfully imported
— this command is only useful for the current session
alter session set NLS_date_language=’SIMPLIFIED CHINESE’;