Forums

I’ve moved an sql file onto my pythonanywhere acc. Username: mar0093

Path: home/mar0093/database/pickledb_pyany.sql

I want to create a database on pythonanywhere with this file. I’ve tried the following line from the help forum in my database console:

use mar0093$pickledb_pyany; source home/mar0093/database/pickledb_pyany.sql;

I get the error:

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

and underneath that I get the following for each of my tables.

ERROR 1146 (42S02): Table 'mar0093$pickledb_pyany.znt' doesn't exist

Where znt is just one of my tables.

I’ve also tried going straight through the bash console but I get a similar error. Running this line:

(my-virtualenv) 02:46 ~/database $ mysql -u mar0093 -h mar0093.mysql.pythonanywhere-services.com 
'mar0093$pickledb_pyany'  < pickledb_pyany.sql

I get this error:

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

Any help would be greatly appreciated.

It looks like your exported file is from a more recent version of MySQL than we use; as far as I can see from the docs, support for the utf8mb4_0900_ai_ci collation type was introduced in 8.0, while we're on 5.6 (and will upgrade to 5.7 for new accounts soon).

Is that collation type important to you? If not, you just need to edit the .sql file to change the collations to ones that are supported in 5.6; I think utf8mb4_general_ci would be the closest match.

Hello,

I have been working on setting up a MySQL database for my website using SQLAlchemy. I have followed your basic blog and I have located all of the .sql files in the system, which are located under ./usr/lib/grass80/ect/sql, that have the .sql file type and have used grep -r “utf8mb4_0900_ai_ci” to find if any of the files have this string in it to change, and non of then have that string in it to change.

However, when I tried to create the database using ipython3 by from website import app, db with app.app_context(): db.create_all(), I received a DatabaseError: (MySQL.connector.errors.DatabaseErrorr) 1273 (HY000): unknown collation ‘utf8mb4_0900_ai_ci’.

I have also tried added app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHMEY_DATABASE_URI'?charset=utf8mb4&collation=utf8mb4_unicode_ci' to the end and changed ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, but I still can't get db.create_all() to work.

I am wondering if I need to upgrade for this problem with the database to work or if there is something else that needs to be done to fix the issue.

I give you permission to look at my code to see if i if I have done anything wrong? My site works up to the registration page now that I’ve added the login and register pages but when i try to register my name i get this ‘utf8mb4_0900_ai_ci’, and i cant figure it out. All my others pages work

but I am having a problem with the CSRF TOKENS with i will figure out slowly, if you have any feed back on the MYSQL database please help.

I have all the .sql file on the system under utf8mb4_0900_ai_ci.txt.

Thank you for your assistance.

Our MySQL databases are on version 5.7 and utf8mb4_0900_ai_ci was added in 8.0. We expect to have it available soon.

We are now able to migrate users to MySQL 8 on request. The migration takes a few minutes plus the time it takes for us to dump and reload your databases on the new server. So larger databases will take longer. Your database will not be available during the migration. You will need to be on either the glastonbury or the haggis system image (https://help.pythonanywhere.com/pages/ChangingSystemImage/), so please make sure that you are on one of those before making the request. Send a request to support@pythonanywhere.com with an indication of a time when it will be convenient for you (between 11am and 6pm UTC on a weekday).