Database collation issue - Revision history

Sarjona: This page will not be migrated to new devdocs

2025-02-10T08:14:57Z

This page will not be migrated to new devdocs

← Older revision		Revision as of 08:14, 10 February 2025
Line 1:		Line 1:
			{{Template:WillNotMigrate}}

	{{Infobox Project		{{Infobox Project
	\|name = Database collation issue		\|name = Database collation issue

TimHunt: /* The problems */

2013-02-15T09:17:28Z

The problems

← Older revision		Revision as of 09:17, 15 February 2013
Line 36:		Line 36:
	* MDL-33655 MySQL case insensitivity breaks get_directory_files (MySQL)		* MDL-33655 MySQL case insensitivity breaks get_directory_files (MySQL)
	* MDL-34271 improve mysql collation handling		* MDL-34271 improve mysql collation handling

			=== Human-readable case-sensitive strings ===

			* MDL-22117 Units in numerical questions are acting case sensitive, but not functioning that way

	=== MySQL only has localised collations that are case-insensitive ===		=== MySQL only has localised collations that are case-insensitive ===

Markn86: /* Transitional period (minor) */

2012-11-13T10:07:31Z

Transitional period (minor)

← Older revision		Revision as of 10:07, 13 November 2012
Line 53:		Line 53:
	=== Transitional period (minor) ===		=== Transitional period (minor) ===

	In the transitional period, when the table definitions have been updated to use a collation like utf8_bin, but before we have found and fixed all SQL queries in Moodle core (and in all third-party plugins) to use sql_order_by_text, then the worst that can happen is that the results of some reports appear in a slightly odd order for some users. The reports ~~wills till~~ basically work.		In the transitional period, when the table definitions have been updated to use a collation like utf8_bin, but before we have found and fixed all SQL queries in Moodle core (and in all third-party plugins) to use sql_order_by_text, then the worst that can happen is that the results of some reports appear in a slightly odd order for some users. The reports will still basically work.

	Whenever a problem like this is detected, it will be easy to add the necessary sql_order_by_text call to the code to fix it.		Whenever a problem like this is detected, it will be easy to add the necessary sql_order_by_text call to the code to fix it.

TimHunt at 07:34, 25 October 2012

2012-10-25T07:34:24Z

← Older revision		Revision as of 07:34, 25 October 2012
Line 2:		Line 2:
	\|name = Database collation issue		\|name = Database collation issue
	\|state = Specification in progress		\|state = Specification in progress
	\|tracker = ~~TBA~~		\|tracker = MDL-36208
	\|discussion = ~~TBA~~		\|discussion = https://moodle.org/mod/forum/discuss.php?d=214564
	\|assignee =		\|assignee = ''Hero required''
	}}		}}

TimHunt at 07:25, 25 October 2012

2012-10-25T07:25:13Z

← Older revision		Revision as of 07:25, 25 October 2012
Line 1:		Line 1:
	~~{{Work in progress}}~~
	{{Infobox Project		{{Infobox Project
	\|name = Database collation issue		\|name = Database collation issue
Line 33:		Line 32:
	* machine-readable identifiers like user.username, course.idnumber or file.filepath		* machine-readable identifiers like user.username, course.idnumber or file.filepath

	For machine-readable identifiers, the 'x' == 'X', or worse 'e' == 'é' behaviour is really bad, ~~and~~ has lead to bugs~~, for example~~:		For machine-readable identifiers, the 'x' == 'X', or worse 'e' == 'é' behaviour is really bad, particularly if you want to put a unique index on the column in question. It has lead to bugs like:
	* MDL-29332 Calculated Questions Require Case Sensitive Data Tables (MySQL)		* MDL-29332 Calculated Questions Require Case Sensitive Data Tables (MySQL)
	* MDL-33655 MySQL case insensitivity breaks get_directory_files (MySQL)		* MDL-33655 MySQL case insensitivity breaks get_directory_files (MySQL)
Line 61:		Line 60:

	The amount of time required to upgrade all tables to a new collation is going to be comparable to the time required for the change that changed all unsigned int columns to signed. This was an issue for some large sites.		The amount of time required to upgrade all tables to a new collation is going to be comparable to the time required for the change that changed all unsigned int columns to signed. This was an issue for some large sites.

			Like dropping unsigned columns, this is probably a necessary evil that we can live with.

	=== Backwards compatibility of identifiers (severe) ===		=== Backwards compatibility of identifiers (severe) ===

			This is the most serious problem. It affects areas like:
			* What are valid user names? Are user names case-sensitive.
			* For people using integrations like the database authentication and enrolment plugins, or CVS import, it may be that at the moment, the identifiers currently match using case-insensitive collation, but if we changed the database collation to utf8_bin, then things would not longer match. This could cause a real mess.

			So, we may have to add an admin option for these areas, where admins can choose if they want case-sensitive or case-insenstivive collations. Then, if they choose case-insensitive collation for backwards compatibility, we will have to add explicit LOWER(...) or COLLATE calls to the code, to maintain the current behaviour.

			Here the risk is not just wrong sorting of reports, but it could badly break sites data integration. Hence, the risks if we get this wrong is very severe.

			We do not yet have a good solution to this problem.

	[[Category:Project]]		[[Category:Project]]

TimHunt at 07:15, 25 October 2012

2012-10-25T07:15:46Z

← Older revision		Revision as of 07:15, 25 October 2012
Line 44:		Line 44:
	== Outline solution ==		== Outline solution ==

			The proposed solution is simple in outline:
			# Store all data in database tables that use a case-sentistive collation (e.g. utf8_bin).
			# Change all ORDER BY clauses in all SQL that sort text, to specify the correct sort collation, based on the current language.

			To support that second part, we will need some helper code, for example, each language pack must specify the right collation to use, for each supported database, for example using new strings in langconfig.php. We will also need an $DB->sql_order_by_text($column) helper function to return the right SQL fragment like "columnname COLLATE utf8_czech_ci".

	== Difficulties with the proposed solution ==		== Difficulties with the proposed solution ==

			=== Transitional period (minor) ===

			In the transitional period, when the table definitions have been updated to use a collation like utf8_bin, but before we have found and fixed all SQL queries in Moodle core (and in all third-party plugins) to use sql_order_by_text, then the worst that can happen is that the results of some reports appear in a slightly odd order for some users. The reports wills till basically work.

			Whenever a problem like this is detected, it will be easy to add the necessary sql_order_by_text call to the code to fix it.

			=== Time to do the upgrade (moderate) ===

			The amount of time required to upgrade all tables to a new collation is going to be comparable to the time required for the change that changed all unsigned int columns to signed. This was an issue for some large sites.

			=== Backwards compatibility of identifiers (severe) ===



	[[Category:Project]]		[[Category:Project]]

TimHunt at 07:01, 25 October 2012

2012-10-25T07:01:43Z

← Older revision		Revision as of 07:01, 25 October 2012
Line 8:		Line 8:
	}}		}}

	== ~~The problem~~ ==		== Background ==

			Database store text data using a particular '''character set'''. Moodle uses UTF-8, which is a good thing, since UTF-8 can represent almost any character ever.

			Text data is also stored with an associated '''collation'''. This affects how the values are sorted and compared. For example, different languages have different sort-orders, which depend on which alphabet they use. For example in German the '''ß''' character should come just after '''S''' (and '''s''').

	=~~== Related bugs ===~~		Collations typically come in two forms, case sensitive, and case-insensitive. If you are using a case-insensitive collation, then not only do 'x' and 'X' get sorted the same, but actually, the database thinks that 'x' = 'X'.

			Operations like JOINs are typically much faster if both tables (columns) use the same collation.

			== The problems ==

			=== Collation should depend on current language ===

			On multi-lingual sites like [https://moodle.org/ moodle.org], different users will all expect reports to be sorted according to their language's collation rules. If we just specify the collation in database table definition, then all queries for all users have to use the same collation.

			Typical bugs:
			* MDL-30446 Glossary order issues for some collation settings (Postgres)

			=== Machine-readable identifiers ===

			Moodle stores two sorts for text data:
			* user-visible strings like user.firstname, user.lastname or course.fullname
			* machine-readable identifiers like user.username, course.idnumber or file.filepath

			For machine-readable identifiers, the 'x' == 'X', or worse 'e' == 'é' behaviour is really bad, and has lead to bugs, for example:
	* MDL-29332 Calculated Questions Require Case Sensitive Data Tables (MySQL)		* MDL-29332 Calculated Questions Require Case Sensitive Data Tables (MySQL)
	* MDL-33655 MySQL case insensitivity breaks get_directory_files (MySQL)		* MDL-33655 MySQL case insensitivity breaks get_directory_files (MySQL)
	* MDL-30446 Glossary order issues for some collation settings (Postgres)
	* MDL-34271 improve mysql collation handling		* MDL-34271 improve mysql collation handling

			=== MySQL only has localised collations that are case-insensitive ===

			If you look at the list of [https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-sets.html MySQL collations], you will see that the only collations that give correctly locale-aware ordering are case-insensitive (the ci bit in utf8_czech_ci), and so suffer from the 'x' == 'X' and 'e' == 'é' problem. The only collation that does not have this problem is utf8_bin, which is not the order people want to see.

	== Outline solution ==		== Outline solution ==


	=== Difficulties ===
			== Difficulties with the proposed solution ==



			[[Category:Project]]

TimHunt: Created page with "{{Work in progress}} {{Infobox Project |name = Database collation issue |state = Specification in progress |tracker = TBA |discussion = TBA |assignee = }} == The problem == ..."

2012-10-25T06:37:04Z

Created page with "{{Work in progress}} {{Infobox Project |name = Database collation issue |state = Specification in progress |tracker = TBA |discussion = TBA |assignee = }} == The problem == ..."

New page

{{Work in progress}}
{{Infobox Project
|name = Database collation issue
|state = Specification in progress
|tracker = TBA
|discussion = TBA
|assignee =
}}

== The problem ==

=== Related bugs ===

* MDL-29332 Calculated Questions Require Case Sensitive Data Tables (MySQL)
* MDL-33655 MySQL case insensitivity breaks get_directory_files (MySQL)
* MDL-30446 Glossary order issues for some collation settings (Postgres)
* MDL-34271 improve mysql collation handling

== Outline solution ==

=== Difficulties ===