0

Whenever I enter python .\manage.py runserver on Django, this error: django.db.utils.OperationalError: (2000, 'Unknown MySQL error') appears. I think it is a client-side error as other people running the same project do not have this error.

Does anyone know what the error is and potentially how to fix it?

I also use a virtual environment with Django and mysqlclient installed.

Here is the full error:

(virtualenv) C:\Users\lonyi\OneDrive\Documents\Gitlab\habit-app\Habit>manage.py runserver
Performing system checks...

System check identified no issues (0 silenced).
(0.016) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.015) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.032) SHOW FULL TABLES; args=None
(0.015) None; args=()
Exception in thread django-main-thread:
Traceback (most recent call last):
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\mysql\base.py", line 73, in 
execute
    return self.cursor.execute(query, args)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\cursors.py", line 206, in execute      
    res = self._query(query)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\cursors.py", line 319, in _query       
    db.query(q)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\connections.py", line 259, in query    
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2000, 'Unknown MySQL error')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\threading.py", line 916, in _bootstrap_inner
    self.run()
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\threading.py", line 864, in run
    self._target(*self._args, **self._kwargs)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\utils\autoreload.py", line 53, in wrapper
    fn(*args, **kwargs)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\management\commands\runserver.py", 
line 121, in inner_run
    self.check_migrations()
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\management\base.py", line 459, in check_migrations
    executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\executor.py", line 18, in 
__init__
    self.loader = MigrationLoader(self.connection)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\loader.py", line 53, in __init__
    self.build_graph()
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\loader.py", line 216, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\recorder.py", line 78, in 
applied_migrations
    return {(migration.app, migration.name): migration for migration in self.migration_qs}
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\query.py", line 287, in __iter__
    self._fetch_all()
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\query.py", line 1308, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\sql\compiler.py", line 1156, in execute_sql
    cursor.execute(sql, params)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 98, in execute
    return super().execute(sql, params)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\utils.py", line 90, in __exit__      
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\mysql\base.py", line 73, in 
execute
    return self.cursor.execute(query, args)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\cursors.py", line 206, in execute      
    res = self._query(query)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\cursors.py", line 319, in _query       
    db.query(q)
  File "C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\MySQLdb\connections.py", line 259, in query    
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (2000, 'Unknown MySQL error')
Iluvsnow
  • 1
  • 3
  • 1
    Kindly share your full **error traceback** it will help to understand your error better. – Mubashar Javed Feb 13 '21 at 14:22
  • @Mubasharjaved I have just added the full error traceback on the post – Iluvsnow Feb 13 '21 at 15:06
  • It's a broad field. A few questions / attempts: Do you use a local MySQL-Server? Are you able to connect to the db-server using the credentials provided in settings.py (using a standalone client)? Did `./manage.py migrate` run successfully? – Mike Feustel Feb 13 '21 at 18:21
  • @MikeF. This is a University db-server. I am able to connect to the server using a separate django project that I created to test the connection which ran successfully. However with the same credentials in settings.py on this new project, the error pops up after makemigrations, migrate and runserver. What makes it more confusing is that it works for other members of the project, I even reinstalled everything such as Python to ensure that my version matches up with theirs but the error still persists. – Iluvsnow Feb 13 '21 at 20:21
  • It sounds promising that the connection works in another project (do ALL parameters really match?). So, it really seems to be the curent project. As the next step I would [increase the DB log level](https://stackoverflow.com/questions/4375784/log-all-sql-queries), test the connection with a freshly checked out source code and at least test a local DB server. That should bring more clues to the cause. However, extensive debugging of your environment is outside of the scope of this platform. – Mike Feustel Feb 14 '21 at 11:14
  • @MikeF. I have edited the post with a increased DB log level for the same error – Iluvsnow Feb 15 '21 at 14:22
  • It looks like reading the migration status fails after initializing the DB connection succeeds (not using venv!). In `"C:\Users\lonyi\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\recorder.py", line 78` django iterates over the result of the previously executed `SHOW FULL TABLES;` statement. This fails for some reason. Possible packet loss, connection dropping or character set problems? What is the result of `SHOW FULL TABLES;`? Check the commands in MySQL standalone client / Workbench. Again, try using a local DB-Server to eliminate external connection trouble. – Mike Feustel Feb 15 '21 at 17:25
  • @MikeF. I have tested the same project on a local DB-Server with the project working, I have also tried the project on a linux mint virtual machine with the original university db-server with that working flawlessly but the 2000 unknown mysql error still persists on my main machine on windows 10 but works on other members machines on windows 10 – Iluvsnow Feb 15 '21 at 19:52
  • What's the output of `SHOW FULL TABLES;` Are there any charset difficulties? Compare also your `venv`, try using their [requirements.txt](https://pip.pypa.io/en/stable/reference/pip_freeze/). (Dont forget to activate the `venv`) – Mike Feustel Feb 15 '21 at 20:28
  • @MikeF. Its already filled as other members of my project did runserver sucessfully SHOW FULL TABLES auth_group BASE TABLE auth_group_permissions BASE TABLE auth_permission BASE TABLE auth_user BASE TABLE auth_user_groups BASE TABLE auth_user_user_permissions BASE TABLE backend_habits BASE TABLE backend_optional BASE TABLE backend_userfriends BASE TABLE backend_userhabits BASE TABLE backend_users BASE TABLE django_admin_log BASE TABLE django_content_type BASE TABLE django_migrations BASE TABLE django_session BASE TABLE – Iluvsnow Feb 15 '21 at 20:34
  • @MikeF. I also use the same venv as the others as we created the venv in our git repository – Iluvsnow Feb 15 '21 at 20:36
  • Compare the output of `SHOW FULL TABLES` with the other ones. You should debug the iteration in `recorder.py` line 78. Don't share the `venv`. Please create a new one and install the requirements with the generated `requirements.txt`. – Mike Feustel Feb 15 '21 at 20:57
  • 3
    @MikeF. I got it working now, it appears the charset used by the university was the error. It is now utf8_general_ci – Iluvsnow Feb 17 '21 at 15:13

1 Answers1

0

You should set 'OPTIONS' 'init_command' in your database settings. It worked for me.

i.e:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'DATABASE_NAME',
        'USER': 'DATABASE_USER',
        'PASSWORD': 'DATABASE_PASS',
        'HOST': 'DATABASE_HOST',
        'PORT': 'DATABASE_PORT',
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"
        }
    }
}
Simas Joneliunas
  • 2,890
  • 20
  • 28
  • 35
LUCAS RODRIGUES FERREIRA
  • 1
  • 1