Forums

Can't connect to MySQL

I'm trying to create a uMap with following instruction.

https://umap-project.readthedocs.io/en/latest/install/

so, I'm stuck at the Create the tables command "umap migrate".

(umap) 05:59 ~ $ umap migrate
Loaded local config from /home/sansaaan/local.py
System check identified some issues:
WARNINGS:
?: (mysql.W002) MySQL Strict Mode is not set for database connection 'default'
HINT: MySQL's Strict Mode fixes many data integrity problems in MySQL, such as data truncation upon insertion, by escalating warnings into error
s. It is strongly recommended you activate it. See: https://docs.djangoproject.com/en/2.2/ref/databases/#mysql-sql-mode
Operations to perform:
 Apply all migrations: admin, auth, contenttypes, sessions, sites, social_django, umap
Running migrations:
    ：
    ：
File "/usr/lib/python3.8/site-packages/MySQLdb/connections.py", line 226, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'jsonb NULL' at line 1")

DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.mysql',
        'NAME': '[MyID]$umap',
        'USER': '[MyID]',
        'PASSWORD': '[MyPassword]',
        'HOST': '[MyID].mysql.pythonanywhere-services.com',
        'PORT': '',
      } }

I got an error "django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'jsonb NULL' at line 1")"

Do you have any ideas to resolve that.

I have some worries.

  1. uMap needs a geo aware database. I use mysql but I haven't installed GEOS and GDAL because I don't have a permission and I thought they are already in PhythonAnywhere. https://docs.djangoproject.com/en/dev/ref/contrib/gis/install/

  2. I choose mySQL and Python3.8 but not 2.x. However, sometimes, default python seems 2.x on Bash

  3. I used "python3.8 -m venv umap", insted of "virtualenv umap" in the instruction since I choose Python3.8.

Best regards

Beloved premium user sansaaan | 18 posts | April 10, 2021, 6:52 a.m. | permalink

How was your virtual environment created? It looks like you are running your command outside that venv.

Staff fjl | 4352 posts | PythonAnywhere staff | April 10, 2021, 8:19 p.m. | permalink

I tried with virtualenv again, but i doesn't seem to work.. What's Virtualenv on Dashboard? I tried set on it but it tells "This virtualenv seems to have the wrong Python version (2.7 instead of 3.8)."

00:28 ~/umap/.vm $ virtualenv umap                                                                                                                      
New python executable in /home/sansaaan/umap/.vm/umap/bin/python2.7
Also creating executable in /home/sansaaan/umap/.vm/umap/bin/python
Installing setuptools, pip, wheel...
done.
00:28 ~/umap/.vm $ source umap/bin/activate
(umap) 00:32 ~/umap/.vm $ pip3.8 install --user umap-project
Looking in links: /usr/share/pip-wheels
Collecting umap-project

：
：

Successfully installed Django-2.2.17 Pillow-8.0.1 django-agnocomplete-1.0.0 django-appconf-1.0.4 django-compressor-2.4 psycopg2-2.8.4 python3-openid-3.2
.0 rcssmin-1.0.6 requests-2.23.0 rjsmin-1.1.0 social-auth-app-django-3.1.0 social-auth-core-3.3.2 umap-project-1.2.3

[copy local.py which I created last time]

(umap) 00:37 ~/umap/.vm $ umap migrate
Loaded local config from /home/sansaaan/umap/.vm/local.py
System check identified some issues:
WARNINGS:
?: (mysql.W002) MySQL Strict Mode is not set for database connection 'default'
        HINT: MySQL's Strict Mode fixes many data integrity problems in MySQL, such as data truncation upon insertion, by escalating warnings into error
s. It is strongly recommended you activate it. See: https://docs.djangoproject.com/en/2.2/ref/databases/#mysql-sql-mode
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, sites, social_django, umap
Running migrations:
  Applying umap.0006_auto_20190407_0719...Traceback (most recent call last):

：
：

  File "/usr/lib/python3.8/site-packages/MySQLdb/connections.py", line 226, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near 'jsonb NULL' at line 1")
Beloved premium user sansaaan | 18 posts | April 11, 2021, 1:24 a.m. | permalink

Looks like you created python2.7 venv and then run your commands with python3.8. You need to create python3.8 venv and run your commands there.

Staff fjl | 4352 posts | PythonAnywhere staff | April 11, 2021, 6:42 p.m. | permalink

I tried it again but it's still the same. I created python3.8 virtualenv and run commands with python3.8 as well. Any ideas?

11:01 ~ $ virtualenv -p python3.8 vmap                                                                                                                  
Running virtualenv with interpreter /usr/local/bin/python3.8
Already using interpreter /usr/local/bin/python3.8
Using base prefix '/usr'
New python executable in /home/sansaaan/vmap/bin/python3.8
Also creating executable in /home/sansaaan/vmap/bin/python
Installing setuptools, pip, wheel...
done.
11:02 ~ $ source vmap/bin/activate
(vmap) 11:04 ~ $ pip3.8 install umap-project                                                                                                            
Looking in links: /usr/share/pip-wheels
Collecting umap-project
　：
　：
Successfully built psycopg2 rcssmin rjsmin
Installing collected packages: urllib3, pycparser, idna, chardet, certifi, sqlparse, requests, pytz, oauthlib, defusedxml, cffi, six, requests-oauthlib,
 python3-openid, PyJWT, Django, cryptography, social-auth-core, rjsmin, rcssmin, django-appconf, social-auth-app-django, psycopg2, Pillow, django-compre
ssor, django-agnocomplete, umap-project
Successfully installed Django-2.2.17 Pillow-8.0.1 PyJWT-2.0.1 certifi-2020.12.5 cffi-1.14.5 chardet-3.0.4 cryptography-3.4.7 defusedxml-0.7.1 django-agn
ocomplete-1.0.0 django-appconf-1.0.4 django-compressor-2.4 idna-2.10 oauthlib-3.1.0 psycopg2-2.8.4 pycparser-2.20 python3-openid-3.2.0 pytz-2021.1 rcssm
in-1.0.6 requests-2.23.0 requests-oauthlib-1.3.0 rjsmin-1.1.0 six-1.15.0 social-auth-app-django-3.1.0 social-auth-core-3.3.2 sqlparse-0.4.1 umap-project
-1.2.3 urllib3-1.25.11

(vmap) 11:06 ~/vmap $ export UMAP_SETTINGS=`pwd`/local.py
(vmap) 11:10 ~/vmap $ pip3.8 install mysqlclient
Looking in links: /usr/share/pip-wheels
　：
　：
Installing collected packages: mysqlclient
Successfully installed mysqlclient-2.0.3

(vmap) 11:11 ~/vmap $ umap migrate                                                                                                                      
Loaded local config from /home/sansaaan/vmap/local.py
System check identified some issues:
WARNINGS:
?: (mysql.W002) MySQL Strict Mode is not set for database connection 'default'
        HINT: MySQL's Strict Mode fixes many data integrity problems in MySQL, such as data truncation upon insertion, by escalating warnings into error
s. It is strongly recommended you activate it. See: https://docs.djangoproject.com/en/2.2/ref/databases/#mysql-sql-mode
Operations to perform:
　：
　：
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near 'jsonb NULL' at line 1")
(vmap) 11:12 ~/vmap $ ls
bin  include  lib  local.py  manage.py  media  share  static  vmap
(vmap) 11:20 ~/vmap $ python --version
Python 3.8.0
Beloved premium user sansaaan | 18 posts | April 16, 2021, 11:32 a.m. | permalink

What was the full traceback of your last error?

Also, it looks like you have your code mixed with your virtual environment code, it may make things difficult to follow.

Staff fjl | 4352 posts | PythonAnywhere staff | April 16, 2021, 3:57 p.m. | permalink

It seems like that's not a DB connection problem but a SQL command problem. From the stack trace, umap.0006_auto_20190407_0719, switching from DictField to JSONField, seems the cause of it. I suppose it is trying to use "JSONB" in MySQL.

field=django.contrib.postgres.fields.jsonb.JSONField(blank=True, default=dict, null=True, verbose_name='settings'),

https://github.com/umap-project/umap/blob/88cd3e8cf0b24516ba62fbea4a7f58e6038e2e0f/umap/migrations/0006_auto_20190407_0719.py

Probably, I should change MySQL to Postgres as my DB. Give me your oppinions.

That uMap-project with OpenStreetMap requires a geo aware database. For PostgreSQL, the required libraries are GEOS, GDAL, PROJ and PostGIS, so the supported version is 10+. I'd like to ask you if these are already installed in the payed pythonanywhere environment. https://docs.djangoproject.com/en/dev/ref/contrib/gis/install/

Best regards, 

(vmap) 23:58 ~/vmap $ umap migrate                                                                                                                      
Loaded local config from /home/sansaaan/vmap/local.py
System check identified some issues:
WARNINGS:
?: (mysql.W002) MySQL Strict Mode is not set for database connection 'default'
        HINT: MySQL's Strict Mode fixes many data integrity problems in MySQL, such as data truncation upon insertion, by escalating warnings into error
s. It is strongly recommended you activate it. See: https://docs.djangoproject.com/en/2.2/ref/databases/#mysql-sql-mode
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, sites, social_django, umap
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying sessions.0001_initial... OK
  Applying sites.0001_initial... OK
  Applying sites.0002_alter_domain_unique... OK
  Applying social_django.0001_initial... OK
  Applying social_django.0002_add_related_name... OK
  Applying social_django.0003_alter_email_max_length... OK
  Applying social_django.0004_auto_20160423_0400... OK
  Applying social_django.0005_auto_20160727_2333... OK
  Applying social_django.0006_partial... OK
  Applying social_django.0007_code_timestamp... OK
  Applying social_django.0008_partial_timestamp... OK
  Applying umap.0001_initial... OK
  Applying umap.0002_tilelayer_tms... OK
  Applying umap.0003_add_tilelayer... OK
  Applying umap.0004_add_licence... OK
Applying umap.0005_remove_map_tilelayer... OK
  Applying umap.0006_auto_20190407_0719...Traceback (most recent call last):
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'jsonb NULL' at line 1")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/home/sansaaan/vmap/bin/umap", line 8, in <module>
    sys.exit(main())
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/umap/bin/__init__.py", line 12, in main
    management.execute_from_command_line()
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 232, in handle
    post_migrate_state = executor.migrate(
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/migrations/migration.py", line 124, in apply
   operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/migrations/operations/fields.py", line 249, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 534, in alter_field
    self._alter_field(model, old_field, new_field, old_type, new_type,
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 680, in _alter_field
    self.execute(
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/sansaaan/vmap/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'jsonb NULL' at line 1")
(vmap) 00:00 ~/vmap $
Beloved premium user sansaaan | 18 posts | April 17, 2021, 1:02 a.m. | permalink

That's really weird -- as you say, it looks like Django is trying to use Postgres-specific syntax even though you're configured to use a MySQL database.

You could potentially address it by switching to Postgres, yes, but it would be interesting to know why Django is doing it given that it's so clearly the wrong thing for it to do! The only hit I got while looking for similar errors was this GitHub issue -- are you using python-social-auth, or some other thing that might inadvertently inject a Postgres dependency into Django?

Staff giles | 11800 posts | PythonAnywhere staff | April 17, 2021, 4:58 p.m. | permalink

Thank you for giving me your advice anytime. I don' t think I use python-social-auth though, I should ask umap-project on github first.

Can't migrate on mySQL

Beloved premium user sansaaan | 18 posts | April 18, 2021, 1:15 a.m. | permalink

Good plan -- it'll be interesting to see what they say.

Staff giles | 11800 posts | PythonAnywhere staff | April 18, 2021, 3:15 p.m. | permalink

Finally, I changed DB form mySQL to PostgreSQL with being a paid member in pythonanywhere, so this issue was fixed. I guess uMap project doesn't support mySQL.

I have an another question. How can I do runserver command for a Django project? https://www.pythonanywhere.com/forums/topic/29667/

Beloved premium user sansaaan | 18 posts | May 1, 2021, 8:09 a.m. | permalink

@sansaaan -- glad you got db issued solved! Regarding your runserver question, I've already replied to it on the other thread.

Staff pafk | 2973 posts | PythonAnywhere staff | May 1, 2021, 9:32 a.m. | permalink