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:
    • Rank:
      19322

      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...

        Issue Links

          Activity

          Hide
          Petr Škoda 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 Škoda 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 Škoda 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 Škoda 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 Škoda added a comment -

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

          Show
          Petr Škoda 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: