#30224: Mysql Datetime value is string instead of datetime object
-------------------------------------+-------------------------------------
Reporter: Martin Kuhn | Owner: Rohit Jha
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Mysql | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Baptiste):
I confirm that this issue is related to mysql, in my case with version 5.7
Upgrading to MariaDB solved this issue. However, it can be tricky to do
this kind of upgrade, so I'll give my feedback if it can help others that
struggle to proceed to the upgrade.
== Disclaimer/Environment
**First and foremost, please take into consideration the following
environment/context as I have NOT tested this process for other
OS/version, so if you're not under this environment, you should proceed
CAREFULLY and double-check steps! **
- CentOS 7.5
- MySQL version 5.7
- Django 2.2.28
- Python 3.6.9
- pip 21.0.1
== Prerequisites
I suggest you first to put your server into maintenance. It depends on
your WSGI application/webserver, but the goal here is to prevent any
inbound HTTP connection to your server. In my case, I just configured a
maintenance view from the NGINX server (we have a reverse proxy) in order
to let the users know, and we just stopped all redirections to our
gunicorn instances.
Then :
1. Stop mysql service
{{{
systemctl stop mysql
}}}
2. Take a backup of the databases (it will save all of your Django
applications/projects if you have several). PS: For the sake of the
example, I've stored it on /root but feel free to store anywhere you want,
and also care to specify the root user of your mysql instance to get the
dump.
{{{
cp -apf /var/lib/mysql /var/lib/mysql_backup
cd /root/
mysqldump --all-databases -u <your_user_goes_here> -p > all_databases.sql
}}}
3. Finally, remove all mysql packages
{{{
yum remove mysql-server mysql-client mysql-community-*
}}}
== Install MariaDB server 10.4
4. You need to use the MariaDB official repositories for YUM
{{{
yum install wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup
}}}
5. Before you can install MariaDB, you may need to cleanup your ius and
epel releases repos in your server (in my case, I had to, otherwise it
would generate errors during installing MariaDB). If you feel you can skip
this step, go ahead, you can go back to this step if needed.
{{{
yum erase ius-release
rm /etc/yum.repos.d/ius*.repo*
yum install https://repo.ius.io/ius-release-el7.rpm
https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
}}}
6. Then, install all the packages required for Django to work with MariaDB
(MariaDB-devel was required in my case, otherwise some libraries were
missing for Django to work)
{{{
yum install MariaDB-server MariaDB-client MariaDB-devel
}}}
7. Start the MariaDB services afterwards
{{{
systemctl start mariadb
systemctl enable mariadb
}}}
== Proceed to migration
Welcome to the tricky steps! Good luck...
8. You should start by using the MariaDB's command to upgrade the
database. If you get no errors, good for you.. :)
{{{
mariadb-upgrade -u <your_db_user> -p
}}}
9. If errors appeared at some steps, you can always try to add the --force
flag as per suggested by MariaDB:
{{{
mariadb-upgrade -u <your_db_user> -p --force
}}}
10. If you had no luck with some tables using JSON data type, you may
encounter {{{ Unknown data type "MYSQL_JSON" }}} errors when selecting
data in your DB. Then you need to manually fix all the tables with that
JSON type columns. First, you need to stop the MariaDB instance
{{{
systemctl stop mariadb
}}}
11. Then, open a new mysql instance with the following settings (in my
case, datadir was {{{/var/lib/mysql/}}} and plugin-dir was
{{{/usr/lib64/mysql/plugin}}})
{{{
mysqld --no-defaults --datadir=<Your data dir directory> --lc-
messages_dir=./share --plugin-dir=<path to directory containing
type_mysql_json.so> --plugin-maturity=alpha -u <your_sql_user>
}}}
12. On another terminal, you can then install the mysql_json plugin and
fix the tables with
{{{
install soname 'type_mysql_json';
ALTER TABLE `database_name`.`table_name` FORCE;
}}}
13. You can also use this command if you have lots of tables with JSON
type columns
{{{
SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` FORCE;")
AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "<YOUR_DATABASE_NAME>";
}}}
14. If this, still, does not fix all of your SELECT commands in your DB,
you can also try to retrieve the dump directly:
{{{
mysql -u <your_db_user> -p < all_databases.sql
}}}
15. Don't forget to restart the MariaDB instance, if it was shutdown :)
{{{
systemctl start mariadb
}}}
== Testing & troubleshooting
You should **deeply** test requests (read, create, update and delete) once
everything seem to work again. You can, for instance:
- Run random SELECT on several tables of your databases.
- Start a Django instance
- Dive deeply into your Django Admin, and do some CRUD operations from
there
Here a few bugs that I've encountered after the migration, if it helps:
* **{{{Error loading MySQLdb module. Did you install mysqlclient?}}} when
launching a Django instance**
To solve this, I just had to reinstall mysql client from pip
{{{
pip install --force-reinstall --ignore-installed --no-binary :all:
mysqlclient
}}}
* **{{{EnvironmentError: mysql_config not found}}} when launching a Django
instance**
Make sure that you have all the MariaDB yum required packages (especially
the {{{MariaDB-devel}}} package)
* **For no apparent reasons, some of the tables didn't recovered from the
upgrade (Celery-related issue)**
I have several django-celery-beat instances running, and for some, the
periodic tasks did not recovered from the upgrade. I simply had to
manually restore the tables from Celery (days after migration), and here
some useful tricks if you want to recover from a previous SQL dump without
erasing all your current and accurate data from other tables.
1. From your SQL server, create a temp restore user and assign permissions
on the only tables you'd want to restore
{{{
GRANT SELECT ON django_project.* TO 'admin_restore_temp'@'localhost'
IDENTIFIED BY 'its_pwd';
GRANT ALL ON django_project.table1 TO 'admin_restore_temp'@'localhost';
}}}
2. Restore the data (assuming you're getting a recover from
{{{/data/backup/db1.sql}}} dump file)
{{{
mysql --user admin_restore_temp --password --force < /data/backup/db1.sql
}}}
3. Check that everything is okay, and don't forget to drop the temp user
{{{
DROP USER admin_restore_temp;
}}}
== Useful resources
Hope these instructions can help you to migrate safely from mysql to
MariaDB in order to workaround this issue. Here's some of the resources
I've used:
* [https://mariadb.com/fr/resources/blog/installing-mariadb-10-on-
centos-7-rhel-7/]
* [https://mariadb.com/kb/en/restoring-data-from-dump-files/]
Good luck!
--
Ticket URL: <https://code.djangoproject.com/ticket/30224#comment:19>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070187aff4c467-7d6ef196-ff30-4a6c-aeb6-b0296f5e50a6-000000%40eu-central-1.amazonses.com.
