Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.1
    • Fix Version/s: 2.2
    • Component/s: Database SQL/XMLDB
    • Labels:

      Description

      In 1.7 we still supported ancient MySQL that did not support varchars longer than 255, unfortunately text filed can not be used everywhere thanks to restrictions in oracle and mssql.

      Places that could use longer varchars (in separate issues later):

      • user preference value - breaks very very often in gradebook
      • extrnal URLs (rss client block, user profile url)
      • any multilang field (course full name translated to 20 languages for example) - the multilang syntax has big overhead

      What is the proper limit now:

      It looks like the reasonable maximum is either 2048 or 4000 chars.

      Hopefully people with higher unicode chars do not use oracle, anyway anybody who counts string length in bytes these days is crazy...

        Gliffy Diagrams

          Issue Links

            Activity

            Hide
            Petr Skoda added a comment -
            • DB2 has a limit depending on page size - 4kb, 8kb, etc.
            • sqlite does not seem to have any limit
            • informix LVARCHAR is 32k

            I vote for 2048 limit for now because we need this for storage of URLs which should not imo be stored as text fields.

            Show
            Petr Skoda added a comment - DB2 has a limit depending on page size - 4kb, 8kb, etc. sqlite does not seem to have any limit informix LVARCHAR is 32k I vote for 2048 limit for now because we need this for storage of URLs which should not imo be stored as text fields.
            Hide
            Petr Skoda added a comment - - edited

            ohlala, does current oracle driver support storage of longer 255 utf-8 chars in our "VARCHAR2(255)"? I will have to write some new unit tests...

            Show
            Petr Skoda added a comment - - edited ohlala, does current oracle driver support storage of longer 255 utf-8 chars in our "VARCHAR2(255)"? I will have to write some new unit tests...
            Hide
            Petr Skoda added a comment -

            I have moved the max size to the xmldb_field class, please update.

            Show
            Petr Skoda added a comment - I have moved the max size to the xmldb_field class, please update.
            Hide
            Eloy Lafuente (stronk7) added a comment -

            The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week.

            TIA and ciao

            Show
            Eloy Lafuente (stronk7) added a comment - The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week. TIA and ciao
            Hide
            Eloy Lafuente (stronk7) added a comment -

            Integrated, thanks!

            Show
            Eloy Lafuente (stronk7) added a comment - Integrated, thanks!
            Hide
            Sam Hemelryk added a comment -

            Tested on Postgres -> pass

            Show
            Sam Hemelryk added a comment - Tested on Postgres -> pass
            Hide
            Sam Hemelryk added a comment -

            Tested on MSSQL -> pass

            Show
            Sam Hemelryk added a comment - Tested on MSSQL -> pass
            Hide
            Sam Hemelryk added a comment -

            Ok everything tested now except for the functional DB tests on Oracle. Apu is running those presently ...

            Show
            Sam Hemelryk added a comment - Ok everything tested now except for the functional DB tests on Oracle. Apu is running those presently ...
            Hide
            Aparup Banerjee added a comment -

            I've attached two saved pages of unit tests i am seeing. one of present moodle.git/master and one of present integration.git/master. moodle.git test were run then updated to integration.git.

            I'm seeing that i have an exception that been introduced which might possibly have to be another issue to be created.

            Exception: lib/ddl/simpletest/testddl.php / ► ddl_test / ► test_char_size_limit
            Unexpected exception of type [dml_read_exception] with message [Error reading from database] in [C:\server\workspace\oracle\lib\dml\moodle_database.php line 394]

            Debug info:

            ORA-01007: variable not in select list
            SELECT * FROM m_testtable WHERE id = :o_id
            [array (
            'o_id' => 1,
            )]

            line 268 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end()
            line 1096 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
            line 1290 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_records_sql()
            line 1032 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->get_record_sql()
            line 1262 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_record_sql()
            line 1242 of \lib\dml\moodle_database.php: call to moodle_database->get_record_select()
            line 1628 of \lib\ddl\simpletest\testddl.php: call to moodle_database->get_record()
            line ... of ...
            line 53 of \admin\tool\unittest\ex_simple_test.php: call to TestSuite->run()
            line ... of ...
            line 113 of \admin\tool\unittest\dbtest.php: call to autogroup_test_coverage->run_with_external_coverage()

            Show
            Aparup Banerjee added a comment - I've attached two saved pages of unit tests i am seeing. one of present moodle.git/master and one of present integration.git/master. moodle.git test were run then updated to integration.git. I'm seeing that i have an exception that been introduced which might possibly have to be another issue to be created. Exception: lib/ddl/simpletest/testddl.php / ► ddl_test / ► test_char_size_limit Unexpected exception of type [dml_read_exception] with message [Error reading from database] in [C:\server\workspace\oracle\lib\dml\moodle_database.php line 394] Debug info: ORA-01007: variable not in select list SELECT * FROM m_testtable WHERE id = :o_id [array ( 'o_id' => 1, )] line 268 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->query_end() line 1096 of \lib\dml\oci_native_moodle_database.php: call to oci_native_moodle_database->query_end() line 1290 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_records_sql() line 1032 of \lib\dml\oci_native_moodle_database.php: call to moodle_database->get_record_sql() line 1262 of \lib\dml\moodle_database.php: call to oci_native_moodle_database->get_record_sql() line 1242 of \lib\dml\moodle_database.php: call to moodle_database->get_record_select() line 1628 of \lib\ddl\simpletest\testddl.php: call to moodle_database->get_record() line ... of ... line 53 of \admin\tool\unittest\ex_simple_test.php: call to TestSuite->run() line ... of ... line 113 of \admin\tool\unittest\dbtest.php: call to autogroup_test_coverage->run_with_external_coverage()
            Hide
            Aparup Banerjee added a comment -

            attached exceptions have been magically transformed into a separate issue. This issue might actually pass!

            Show
            Aparup Banerjee added a comment - attached exceptions have been magically transformed into a separate issue. This issue might actually pass!
            Hide
            Eloy Lafuente (stronk7) added a comment -

            I can confirm all tests are passing here for mysql, postgresql, mssql and oracle drivers. So only sqlsrv is missing to verify.

            If nobody does it before, I'll try to rebuild my Win32 apache server to test it later today (4h from now, aprox).

            Ciao

            Show
            Eloy Lafuente (stronk7) added a comment - I can confirm all tests are passing here for mysql, postgresql, mssql and oracle drivers. So only sqlsrv is missing to verify. If nobody does it before, I'll try to rebuild my Win32 apache server to test it later today (4h from now, aprox). Ciao
            Hide
            Eloy Lafuente (stronk7) added a comment -

            ah, rereading this... so you tested it already under mssql or sqlsrv, Aparup?

            Show
            Eloy Lafuente (stronk7) added a comment - ah, rereading this... so you tested it already under mssql or sqlsrv, Aparup?
            Hide
            Eloy Lafuente (stronk7) added a comment -

            Assuming it passes under sqlsrv. After all this is master we can break it (nah, I really think this would be working ok).

            Ciao

            Show
            Eloy Lafuente (stronk7) added a comment - Assuming it passes under sqlsrv. After all this is master we can break it (nah, I really think this would be working ok). Ciao
            Hide
            Eloy Lafuente (stronk7) added a comment -

            And this code has been spread to all Moodle git and cvs repositories. Many thanks! Closing.

            Ciao

            Show
            Eloy Lafuente (stronk7) added a comment - And this code has been spread to all Moodle git and cvs repositories. Many thanks! Closing. Ciao
            Hide
            Heiko Schach added a comment -

            Hello

            Has this been implemented yet for multilang database fields?
            I checked the Moodle 2 database and install.xml files but couldn't find this applied to any multilang fields.

            Many courses at our institution are making extensive use of the Multi-language content filter and many of them are still using the old notation. We would like to update all multilang strings on our site to the new notation but we are facing problems because of database field length restrictions. Adding 'class="multilang"' there are 18 additional characters for every language.

            Many database fields allowing multilang content are restricted to 255 chars.
            With multilang one can run out of space very quickly, especially with 3 or more languages in use.
            The multilang tags alone require 41 chars per language in the database.

            Relevant fields include the following, but I am sure there are many more:
            mdl_assignment.name, mdl_course.fullname, mdl_forum.name, mdl_glossary.name, mdl_grade_items.itemname, mdl_grade_letters.letter, mdl_lesson.name, mdl_quiz.questiontext, mdl_quiz.name, mdl_resource.name

            Basically, in order for multilang to be usable, all multilang capable fields should be made longer than 255 chars.

            Show
            Heiko Schach added a comment - Hello Has this been implemented yet for multilang database fields? I checked the Moodle 2 database and install.xml files but couldn't find this applied to any multilang fields. Many courses at our institution are making extensive use of the Multi-language content filter and many of them are still using the old notation. We would like to update all multilang strings on our site to the new notation but we are facing problems because of database field length restrictions. Adding 'class="multilang"' there are 18 additional characters for every language. Many database fields allowing multilang content are restricted to 255 chars. With multilang one can run out of space very quickly, especially with 3 or more languages in use. The multilang tags alone require 41 chars per language in the database. Relevant fields include the following, but I am sure there are many more: mdl_assignment.name, mdl_course.fullname, mdl_forum.name, mdl_glossary.name, mdl_grade_items.itemname, mdl_grade_letters.letter, mdl_lesson.name, mdl_quiz.questiontext, mdl_quiz.name, mdl_resource.name Basically, in order for multilang to be usable, all multilang capable fields should be made longer than 255 chars.

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: