MySQL error 1292 (datetime) after upgrade to Mezzanine 5.1.3

[BOFH2k]

Something is amiss when trying to administer blog posts after an upgrade to Mezzanine 5.1.3 (and Django 2.2.27) which involved a db switch from PostgreSQL to MariaDB (due to Django requirement of psql minimum version 9.4) via django-admin migrate and django-admin loaddata. settings.py and urls.py have been adjusted to Mezzanine master and the project seems to work, apart from displaying blog posts by date not returning any items. Any ideas?

DatabaseError at /admin/blog/blogpost/

(1292, "1292: Incorrect datetime value: '%Y-01-01'", None)

Request Method: GET
-- | --
https://.../admin/blog/blogpost/
2.2.27
DatabaseError
(1292, "1292: Incorrect datetime value: '%Y-01-01'", None)
/home/lsweb/virtualenv/public_html/blog5/3.9/lib/python3.9/site-packages/mysql/connector/cursor_cext.py in _handle_eof, line 194
/home/lsweb/virtualenv/public_html/blog5/3.9/bin/python3.9_bin
3.9.10
['/home/lsweb/public_html/blog5', '/home/lsweb/public_html/blog5', '/opt/cpanel/ea-ruby27/root/usr/share/passenger/helper-scripts', '/home/lsweb/virtualenv/public_html/blog5/3.9/lib64/python39.zip', '/home/lsweb/virtualenv/public_html/blog5/3.9/lib64/python3.9', '/home/lsweb/virtualenv/public_html/blog5/3.9/lib64/python3.9/lib-dynload', '/opt/alt/python39/lib64/python3.9', '/opt/alt/python39/lib/python3.9', '/home/lsweb/virtualenv/public_html/blog5/3.9/lib/python3.9/site-packages']
Mon, 28 Feb 2022 06:05:31 +0100

Expand traceback

Request info:

CSRF_COOKIE	'...'
CSRF_COOKIE_USED	True
H2PUSH	'on'
H2_PUSH	'on'
H2_PUSHED	''
H2_PUSHED_ON	''
H2_STREAM_ID	'35'
H2_STREAM_TAG	'270-35'
HTTP2	'on'
HTTPS	'on'
HTTP_ACCEPT	'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,/;q=0.8'
HTTP_ACCEPT_ENCODING	'gzip, deflate, br'
HTTP_ACCEPT_LANGUAGE	'de-DE,en-US;q=0.5'
HTTP_COOKIE	('csrftoken=... ' 'mezzanine-rating="\054blog.blogpost.22"; ' 'sessionid=...')
HTTP_DNT	'1'
HTTP_HOST	'...'
HTTP_REFERER	'...'
HTTP_SEC_FETCH_DEST	'document'
HTTP_SEC_FETCH_MODE	'navigate'
HTTP_SEC_FETCH_SITE	'same-origin'
HTTP_SEC_FETCH_USER	'?1'
HTTP_TE	'trailers'
HTTP_UPGRADE_INSECURE_REQUESTS	'1'
HTTP_USER_AGENT	'Mozilla/5.0 (Android 9; Mobile; rv:97.0) Gecko/97.0 Firefox/97.0'
PASSENGER_CONNECT_PASSWORD	'...'
PATH_INFO	'/admin/blog/blogpost/'
QUERY_STRING	''
REMOTE_ADDR	'...'
REMOTE_PORT	'48816'
REQUEST_METHOD	'GET'
REQUEST_URI	'/admin/blog/blogpost/'
SCRIPT_NAME	''
SERVER_NAME	'...'
SERVER_PORT	'443'
SERVER_PROTOCOL	'HTTP/1.1'
SERVER_SOFTWARE	('Apache/2.4.52 (cPanel) OpenSSL/1.1.1m mod_bwlimited/1.4 mod_fcgid/2.3.9 ' 'mod_cpanel/1.4 Phusion_Passenger/6.0.10')
SSL_TLS_SNI	'...'
UNIQUE_ID	'...'
passenger.hijack	None
wsgi.errors	<_io.TextIOWrapper name='' mode='w' encoding='utf-8'>
wsgi.input	<_io.BufferedReader name=20>
wsgi.multiprocess	True
wsgi.multithread	False
wsgi.run_once	False
wsgi.url_scheme	'https'
wsgi.version	(1, 0)

Settings:

ABSOLUTE_URL_OVERRIDES	{}
ALLOWED_HOSTS	['localhost', '127.0.0.1', '[::1]', '...']
APPEND_SLASH	True
AUTHENTICATION_BACKENDS	('mezzanine.core.auth_backends.MezzanineBackend',)
AUTH_PASSWORD_VALIDATORS	'********************'
AUTH_USER_MODEL	'auth.User'
BASE_DIR	'/home/lsweb/public_html/blog5'
CACHES	{'default': {'BACKEND': 'django.core.cache.backends.locmem.LocMemCache'}}
CACHE_MIDDLEWARE_ALIAS	'default'
CACHE_MIDDLEWARE_KEY_PREFIX	'********************'
CACHE_MIDDLEWARE_SECONDS	600
COMMENTS_APP	'mezzanine.generic'
CSRF_COOKIE_AGE	31449600
CSRF_COOKIE_DOMAIN	None
CSRF_COOKIE_HTTPONLY	False
CSRF_COOKIE_NAME	'csrftoken'
CSRF_COOKIE_PATH	'/'
CSRF_COOKIE_SAMESITE	'Lax'
CSRF_COOKIE_SECURE	False
CSRF_FAILURE_VIEW	'django.views.csrf.csrf_failure'
CSRF_HEADER_NAME	'HTTP_X_CSRFTOKEN'
CSRF_TRUSTED_ORIGINS	[]
CSRF_USE_SESSIONS	False
DATABASES	{'default': {'ATOMIC_REQUESTS': False, 'AUTOCOMMIT': True, 'CONN_MAX_AGE': 0, 'ENGINE': 'mysql.connector.django', 'HOST': '127.0.0.1', 'NAME': '...', 'OPTIONS': {'autocommit': True}, 'PASSWORD': '********************', 'PORT': '', 'TEST': {'CHARSET': None, 'COLLATION': None, 'MIRROR': None, 'NAME': None}, 'TIME_ZONE': None, 'USER': '...'}}
DATABASE_ROUTERS	[]
DATA_UPLOAD_MAX_MEMORY_SIZE	2621440
DATA_UPLOAD_MAX_NUMBER_FIELDS	1000
DATETIME_FORMAT	'N j, Y, P'
DATETIME_INPUT_FORMATS	['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S.%f', '%Y-%m-%d %H:%M', '%Y-%m-%d', '%m/%d/%Y %H:%M:%S', '%m/%d/%Y %H:%M:%S.%f', '%m/%d/%Y %H:%M', '%m/%d/%Y', '%m/%d/%y %H:%M:%S', '%m/%d/%y %H:%M:%S.%f', '%m/%d/%y %H:%M', '%m/%d/%y']
DATE_FORMAT	'N j, Y'
DATE_INPUT_FORMATS	['%Y-%m-%d', '%m/%d/%Y', '%m/%d/%y', '%b %d %Y', '%b %d, %Y', '%d %b %Y', '%d %b, %Y', '%B %d %Y', '%B %d, %Y', '%d %B %Y', '%d %B, %Y']
DEBUG	True
DEBUG_PROPAGATE_EXCEPTIONS	False
DECIMAL_SEPARATOR	'.'
DEFAULT_AUTO_FIELD	'django.db.models.BigAutoField'
DEFAULT_CHARSET	'utf-8'
DEFAULT_CONTENT_TYPE	'text/html'
DEFAULT_EXCEPTION_REPORTER_FILTER	'django.views.debug.SafeExceptionReporterFilter'
DEFAULT_FILE_STORAGE	'django.core.files.storage.FileSystemStorage'
DEFAULT_FROM_EMAIL	'webmaster@localhost'
DEFAULT_INDEX_TABLESPACE	''
DEFAULT_TABLESPACE	''
DEV_SERVER	False
DISALLOWED_USER_AGENTS	[]
EMAIL_BACKEND	'django.core.mail.backends.smtp.EmailBackend'
EMAIL_HOST	'localhost'
EMAIL_HOST_PASSWORD	'********************'
EMAIL_HOST_USER	''
EMAIL_PORT	25
EMAIL_SSL_CERTFILE	None
EMAIL_SSL_KEYFILE	'********************'
EMAIL_SUBJECT_PREFIX	'[Django] '
EMAIL_TIMEOUT	None
EMAIL_USE_LOCALTIME	False
EMAIL_USE_SSL	False
EMAIL_USE_TLS	False
FILE_CHARSET	'utf-8'
FILE_UPLOAD_DIRECTORY_PERMISSIONS	None
FILE_UPLOAD_HANDLERS	['django.core.files.uploadhandler.MemoryFileUploadHandler', 'django.core.files.uploadhandler.TemporaryFileUploadHandler']
FILE_UPLOAD_MAX_MEMORY_SIZE	2621440
FILE_UPLOAD_PERMISSIONS	420
FILE_UPLOAD_TEMP_DIR	None
FIRST_DAY_OF_WEEK	0
FIXTURE_DIRS	[]
FORCE_SCRIPT_NAME	None
FORMAT_MODULE_PATH	None
FORM_RENDERER	'django.forms.renderers.DjangoTemplates'
GRAPPELLI_INSTALLED	True
IGNORABLE_404_URLS	[]
INSTALLED_APPS	['mezzanine.boot', 'theme', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.redirects', 'django.contrib.sessions', 'django.contrib.sites', 'django.contrib.sitemaps', 'django.contrib.messages', 'mezzanine.conf', 'mezzanine.core', 'mezzanine.generic', 'mezzanine.pages', 'mezzanine.blog', 'mezzanine.forms', 'mezzanine.galleries', 'mezzanine.accounts', 'filebrowser_safe', 'grappelli_safe', 'django.contrib.admin', 'django.contrib.staticfiles', 'django_comments']
INTERNAL_IPS	[]
LANGUAGES	(('en', 'English'),)
LANGUAGES_BIDI	['he', 'ar', 'fa', 'ur']
LANGUAGE_CODE	'en'
LANGUAGE_COOKIE_AGE	None
LANGUAGE_COOKIE_DOMAIN	None
LANGUAGE_COOKIE_NAME	'django_language'
LANGUAGE_COOKIE_PATH	'/'
LOCALE_PATHS	[]
LOGGING	{'disable_existing_loggers': False, 'formatters': {'simple': {'format': '%(levelname)s %(message)s'}, 'verbose': {'datefmt': '%d/%b/%Y %H:%M:%S', 'format': '[%(asctime)s] %(levelname)s ' '[%(name)s:%(lineno)s] %(message)s'}}, 'handlers': {'file': {'class': 'logging.FileHandler', 'filename': 'blog/blog.log', 'formatter': 'verbose', 'level': 'DEBUG'}}, 'loggers': {'blog': {'handlers': ['file'], 'level': 'DEBUG'}, 'django': {'handlers': ['file'], 'level': 'WARNING', 'propagate': True}}, 'version': 1}
LOGGING_CONFIG	'logging.config.dictConfig'
LOGIN_REDIRECT_URL	'/accounts/profile/'
LOGIN_URL	'/accounts/login/'
LOGOUT_REDIRECT_URL	None
MANAGERS	[]
MEDIA_ROOT	'/home/lsweb/public_html/blog5/media'
MEDIA_URL	'/media/'
MESSAGE_STORAGE	'django.contrib.messages.storage.cookie.CookieStorage'
MIDDLEWARE	('django.contrib.sessions.middleware.SessionMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware', 'mezzanine.core.request.CurrentRequestMiddleware', 'mezzanine.core.middleware.RedirectFallbackMiddleware', 'mezzanine.core.middleware.AdminLoginInterfaceSelectorMiddleware', 'mezzanine.core.middleware.SitePermissionMiddleware', 'mezzanine.pages.middleware.PageMiddleware')
MIGRATION_MODULES	{}
MONTH_DAY_FORMAT	'F j'
NEVERCACHE_KEY	'********************'
NUMBER_GROUPING	0
OPTIONAL_APPS	('debug_toolbar', 'django_extensions', 'compressor', 'filebrowser_safe', 'grappelli_safe')
PACKAGE_NAME_FILEBROWSER	'filebrowser_safe'
PACKAGE_NAME_GRAPPELLI	'grappelli_safe'
PASSWORD_HASHERS	'********************'
PASSWORD_RESET_TIMEOUT_DAYS	'********************'
PREPEND_WWW	False
PROJECT_APP	'blog'
PROJECT_APP_PATH	'/home/lsweb/public_html/blog5/blog'
PROJECT_ROOT	'/home/lsweb/public_html/blog5'
ROOT_URLCONF	'blog.urls'
SECRET_KEY	'********************'
SECURE_BROWSER_XSS_FILTER	False
SECURE_CONTENT_TYPE_NOSNIFF	False
SECURE_HSTS_INCLUDE_SUBDOMAINS	False
SECURE_HSTS_PRELOAD	False
SECURE_HSTS_SECONDS	0
SECURE_PROXY_SSL_HEADER	None
SECURE_REDIRECT_EXEMPT	[]
SECURE_SSL_HOST	None
SECURE_SSL_REDIRECT	False
SEND_BROKEN_LINK_EMAILS	True
SERVER_EMAIL	'...'
SESSION_CACHE_ALIAS	'default'
SESSION_COOKIE_AGE	1209600
SESSION_COOKIE_DOMAIN	None
SESSION_COOKIE_HTTPONLY	True
SESSION_COOKIE_NAME	'sessionid'
SESSION_COOKIE_PATH	'/'
SESSION_COOKIE_SAMESITE	'Lax'
SESSION_COOKIE_SECURE	False
SESSION_ENGINE	'django.contrib.sessions.backends.db'
SESSION_EXPIRE_AT_BROWSER_CLOSE	True
SESSION_FILE_PATH	None
SESSION_SAVE_EVERY_REQUEST	False
SESSION_SERIALIZER	'django.contrib.sessions.serializers.JSONSerializer'
SETTINGS_MODULE	'blog.settings'
SHORT_DATETIME_FORMAT	'm/d/Y P'
SHORT_DATE_FORMAT	'm/d/Y'
SIGNING_BACKEND	'django.core.signing.TimestampSigner'
SILENCED_SYSTEM_CHECKS	[]
SITE_ID	1
STATICFILES_DIRS	[]
STATICFILES_FINDERS	['django.contrib.staticfiles.finders.FileSystemFinder', 'django.contrib.staticfiles.finders.AppDirectoriesFinder']
STATICFILES_STORAGE	'django.contrib.staticfiles.storage.StaticFilesStorage'
STATIC_ROOT	'/home/lsweb/public_html/blog5/static'
STATIC_URL	'/static/'
TEMPLATES	[{'BACKEND': 'django.template.backends.django.DjangoTemplates', 'DIRS': ['/home/lsweb/public_html/blog5/templates'], 'OPTIONS': {'context_processors': ['django.contrib.auth.context_processors.auth', 'django.contrib.messages.context_processors.messages', 'django.template.context_processors.debug', 'django.template.context_processors.i18n', 'django.template.context_processors.static', 'django.template.context_processors.media', 'django.template.context_processors.request', 'django.template.context_processors.tz', 'mezzanine.conf.context_processors.settings', 'mezzanine.pages.context_processors.page'], 'loaders': ['mezzanine.template.loaders.host_themes.Loader', 'django.template.loaders.filesystem.Loader', 'django.template.loaders.app_directories.Loader']}}]
TESTING	False
TEST_NON_SERIALIZED_APPS	[]
TEST_RUNNER	'django.test.runner.DiscoverRunner'
THOUSAND_SEPARATOR	','
TIME_FORMAT	'P'
TIME_INPUT_FORMATS	['%H:%M:%S', '%H:%M:%S.%f', '%H:%M']
TIME_ZONE	'Europe/Berlin'
USE_I18N	False
USE_L10N	False
USE_MODELTRANSLATION	False
USE_THOUSAND_SEPARATOR	False
USE_TZ	True
USE_X_FORWARDED_HOST	False
USE_X_FORWARDED_PORT	False
WSGI_APPLICATION	None
X_FRAME_OPTIONS	'SAMEORIGIN'
YEAR_MONTH_FORMAT	'F Y'

[jerivas]

From the traceback it looks like the dates were exported incorrectly by Postgres or imported incorrectly by MySQL. First, if the problem was Postgres, then you should be able to fix the errors by re-exporting the data or manually editing the JSON file produced by dumpdata.

If that doesn't work or is not feasible I would recommend transferring the database records directly without using Django (no dumpdata/loaddata). This Stack Overflow question has a bunch of candidates (I haven't tried any).

[BOFH2k]

Hmmm, the issue seems to be that my blog posts have no expiry date set:
After django-admin flush and django-admin loaddata of a dump excluding the blog posts, I can create a blog post via the Mezzanine admin interface sucesssfully (with expiry date set). If a create another one without expiry date, the 1292 error is back. And the display of blog posts by date does not work with MariaDB 10.2.43, even with an expiry date set...

[BOFH2k]

The issue seems to lie with missing timezone data in my shared hosting MySQL server (which I can't import myself):
https://docs.djangoproject.com/en/2.2/ref/databases/#time-zone-definitions