Moodle
  1. Moodle
  2. MDL-37420

Installation and restoring courses backup won't work if it use one postgres database with many schemas (others than public schema)

    Details

    • Rank:
      47048

      Description

      Postgres DB support schemas like oracle does and you can create many schemas in one Database, your tables will be grouped by schemas. The default schema in postgres is "public".
      Until moodle 1.9.x, i can install many moodles using only one postgres database, with many postgres db schemas ("moodle1","moodle2",...) for example.
      For this i used this commands:

      CREATE USER "moodle-1";
      CREATE SCHEMA "moodle-1" ;
      ALTER SCHEMA "moodle-1" OWNER TO "moodle-1";
      GRANT ALL ON SCHEMA "moodle-1" TO "moodle-1"
      ALTER USER "moodle-1" SET search_path TO "moodle-1";

      For moodle 2.x or above, it's seems the installation process ignores the command:
      SET search_path TO "moodle-1", and tries to use the first schema that it found, give this installation errors:
      "Config table does not contain version, can not continue, sorry."
      "Coding error detected, it must be fixed by a programmer: block_manager has not yet loaded the blocks, to it is too soon to request the information you asked for."

      But it's can be fixed by installing moodle on a new database, with only one schema and after installation, dump it and restore on the other database, moodle works fine!

      The same thing occurs when i try to restore a course backup, if moodle db has many schemas it's goes wrong ("DDL" or "DTL" error, something like that), if not, works fine. With same moodle version, with same DB data, only difference is one with many DB schemas and other with only one.

        Activity

        Hide
        Petr Škoda added a comment -

        Thanks for the report.

        Show
        Petr Škoda added a comment - Thanks for the report.
        Hide
        Petr Škoda added a comment -

        Eloy: could you please review my patch? It looks like this is the cause of those remaining 2.x weird upgrade errors, I suppose we should get that into the next 2.2.x stable release next week...

        Show
        Petr Škoda added a comment - Eloy: could you please review my patch? It looks like this is the cause of those remaining 2.x weird upgrade errors, I suppose we should get that into the next 2.2.x stable release next week...
        Hide
        Eloy Lafuente (stronk7) added a comment - - edited

        Wow, some questions:

        1) pg_my_temp_schema() is to get the temp schema where temp tables are created (that I assume is different from the current one always).

        2) For people not using schemas, not having search_path defined... what does current_schema() return, "public" or "" (it sounds to me that it was returning empty or I read it somewhere) and everything continues working the same, ie. ns.nspname matches that "public" or "" ?

        3) Why do you need to specify the schema in the config of the auth/enrol tests? because it's ADOdb and it does not support setting the schema in another, explicit way? What if the "remote" DB has schemas? (offtopic thought.... some day we should start using our moodle2 drivers at least for the big four).

        Show
        Eloy Lafuente (stronk7) added a comment - - edited Wow, some questions: 1) pg_my_temp_schema() is to get the temp schema where temp tables are created (that I assume is different from the current one always). 2) For people not using schemas, not having search_path defined... what does current_schema() return, "public" or "" (it sounds to me that it was returning empty or I read it somewhere) and everything continues working the same, ie. ns.nspname matches that "public" or "" ? 3) Why do you need to specify the schema in the config of the auth/enrol tests? because it's ADOdb and it does not support setting the schema in another, explicit way? What if the "remote" DB has schemas? (offtopic thought.... some day we should start using our moodle2 drivers at least for the big four).
        Hide
        Petr Škoda added a comment - - edited

        1) yes, pg_my_temp_schema() returns id of the temp schema for current user or 0
        2) there is always some schema, the docs says the current_schema() returns some name, so I guess '' is not possible
        3) adodb DML can not specify schema explicitly, it is using current schema by default; in DDL it has a setSchema() method

        Show
        Petr Škoda added a comment - - edited 1) yes, pg_my_temp_schema() returns id of the temp schema for current user or 0 2) there is always some schema, the docs says the current_schema() returns some name, so I guess '' is not possible 3) adodb DML can not specify schema explicitly, it is using current schema by default; in DDL it has a setSchema() method
        Hide
        Petr Škoda added a comment -

        the unittests will fail if you setup default schema for pg user without setting the dboptions, I did not find any elegant workaround yet, but the real moodle should work just fine with any schema setting

        Show
        Petr Škoda added a comment - the unittests will fail if you setup default schema for pg user without setting the dboptions, I did not find any elegant workaround yet, but the real moodle should work just fine with any schema setting
        Hide
        Eloy Lafuente (stronk7) added a comment -

        oki, here we go! Thanks!

        Show
        Eloy Lafuente (stronk7) added a comment - oki, here we go! Thanks!
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Integrated, 22, 23, 24 and master. Surely testing should happen in all branches to avoid any surprise with incoming releases.

        Show
        Eloy Lafuente (stronk7) added a comment - Integrated, 22, 23, 24 and master. Surely testing should happen in all branches to avoid any surprise with incoming releases.
        Hide
        Adrian Greeve added a comment -

        I was able to create and install the schemas with no trouble, but when running unit tests on the schema branches I get these failures:

        1) auth_db_testcase::test_plugin
        Failed asserting that false is an instance of class "ADORecordSet".
        
        /home/adrian/moodles/integration_master_schema1/moodle/auth/db/tests/db_test.php:164
        /home/adrian/moodles/integration_master_schema1/moodle/lib/phpunit/classes/advanced_testcase.php:76
        
        To re-run:
         /usr/bin/phpunit auth_db_testcase auth/db/tests/db_test.php
        
        2) enrol_database_testcase::test_sync_user_enrolments
        Failed asserting that 0 matches expected 2.
        
        /home/adrian/moodles/integration_master_schema1/moodle/enrol/database/tests/sync_test.php:237
        /home/adrian/moodles/integration_master_schema1/moodle/lib/phpunit/classes/advanced_testcase.php:76
        
        To re-run:
         /usr/bin/phpunit enrol_database_testcase enrol/database/tests/sync_test.php
        

        I haven't had the chance to run the simple tests yet on 2.2, I'll do that and post the result later.

        Show
        Adrian Greeve added a comment - I was able to create and install the schemas with no trouble, but when running unit tests on the schema branches I get these failures: 1) auth_db_testcase::test_plugin Failed asserting that false is an instance of class "ADORecordSet" . /home/adrian/moodles/integration_master_schema1/moodle/auth/db/tests/db_test.php:164 /home/adrian/moodles/integration_master_schema1/moodle/lib/phpunit/classes/advanced_testcase.php:76 To re-run: /usr/bin/phpunit auth_db_testcase auth/db/tests/db_test.php 2) enrol_database_testcase::test_sync_user_enrolments Failed asserting that 0 matches expected 2. /home/adrian/moodles/integration_master_schema1/moodle/enrol/database/tests/sync_test.php:237 /home/adrian/moodles/integration_master_schema1/moodle/lib/phpunit/classes/advanced_testcase.php:76 To re-run: /usr/bin/phpunit enrol_database_testcase enrol/database/tests/sync_test.php I haven't had the chance to run the simple tests yet on 2.2, I'll do that and post the result later.
        Hide
        Petr Škoda added a comment -

        The problem is that you did not set "$CFG->dboptions['dbschema'] = 'yournewschemaname';" in your config.php, the trouble is that adodb does not have special support for schemas either, I tried to hack around it a bit by adding schema option to db configuration. I was getting the same errors before added that hack.

        Show
        Petr Škoda added a comment - The problem is that you did not set "$CFG->dboptions ['dbschema'] = 'yournewschemaname';" in your config.php, the trouble is that adodb does not have special support for schemas either, I tried to hack around it a bit by adding schema option to db configuration. I was getting the same errors before added that hack.
        Hide
        Adrian Greeve added a comment -

        I did add $CFG->dboptions['dbschema'] = 'yournewschemaname'; to the config.php file.

        Show
        Adrian Greeve added a comment - I did add $CFG->dboptions ['dbschema'] = 'yournewschemaname'; to the config.php file.
        Hide
        Petr Škoda added a comment -

        Oh, strange, it works fine for me here. The failure indicates problem in the enrol|auth tests that use adodb, not the schema support in our DB driver.

        Show
        Petr Škoda added a comment - Oh, strange, it works fine for me here. The failure indicates problem in the enrol|auth tests that use adodb, not the schema support in our DB driver.
        Hide
        Adrian Greeve added a comment -

        Okay, no failures on 2.2 with the simple tests. I'll repeat again on the other branches and see if I can replicate the failures.

        Show
        Adrian Greeve added a comment - Okay, no failures on 2.2 with the simple tests. I'll repeat again on the other branches and see if I can replicate the failures.
        Hide
        Michael de Raadt added a comment -

        Adrian asked me to run these tests using an alternate PostgreSQL schema. I also was presented with the failures listed above.

        Show
        Michael de Raadt added a comment - Adrian asked me to run these tests using an alternate PostgreSQL schema. I also was presented with the failures listed above.
        Hide
        Adrian Greeve added a comment -

        Further updates:

        master - The same two failures as above.
        2.4 - Only the second problem recorded as a failure.
        2.3 - No failures.
        2.2 - No failures.

        Show
        Adrian Greeve added a comment - Further updates: master - The same two failures as above. 2.4 - Only the second problem recorded as a failure. 2.3 - No failures. 2.2 - No failures.
        Hide
        Petr Škoda added a comment -

        Here is a bit different patch, does it work for you?
        https://github.com/skodak/moodle/compare/72a504d...w51_MDL-37420_m25_schematest

        Show
        Petr Škoda added a comment - Here is a bit different patch, does it work for you? https://github.com/skodak/moodle/compare/72a504d...w51_MDL-37420_m25_schematest
        Hide
        Carlos Alexandre S. da Fonseca added a comment -

        I think could be test using a database with more than one schema, because in installation process try to install in the first schema that's found, not in the default schema (public), for example, create a schema aaaa, and other schema zzz, and try to install on zzz schema.

        Show
        Carlos Alexandre S. da Fonseca added a comment - I think could be test using a database with more than one schema, because in installation process try to install in the first schema that's found, not in the default schema (public), for example, create a schema aaaa, and other schema zzz, and try to install on zzz schema.
        Hide
        Eloy Lafuente (stronk7) added a comment - - edited

        Pushed extra commit (master) and reseting back to testing.

        Show
        Eloy Lafuente (stronk7) added a comment - - edited Pushed extra commit (master) and reseting back to testing.
        Hide
        Adrian Greeve added a comment -

        Hooray! I don't get any failures on master any more.
        After talking with Eloy, I tested it by adding the config dboption setting and also by setting up everything in the DB.

        Please note, only tested on master.

        Show
        Adrian Greeve added a comment - Hooray! I don't get any failures on master any more. After talking with Eloy, I tested it by adding the config dboption setting and also by setting up everything in the DB. Please note, only tested on master.
        Hide
        Eloy Lafuente (stronk7) added a comment -

        I've partially backported the master fix (92b00c3) to 22, 23 and 24. Testing them a bit...

        Show
        Eloy Lafuente (stronk7) added a comment - I've partially backported the master fix (92b00c3) to 22, 23 and 24. Testing them a bit...
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Tested under 23 and 24, everything seems to work as expected. (22 solution is 100% the same than 23).

        Show
        Eloy Lafuente (stronk7) added a comment - Tested under 23 and 24, everything seems to work as expected. (22 solution is 100% the same than 23).
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Found one more place in phpunit tests where support for dbschema is missing:

        There was 1 error:
        
        1) core_adodb_testcase::test_read_table
        Trying to get property of non-object
        
        /Users/stronk7/git_moodle/integration/enrol/database/tests/adodb_test.php:100
        /Users/stronk7/git_moodle/integration/lib/phpunit/classes/advanced_testcase.php:76
        
        To re-run:
         /opt/local/bin/phpunit core_adodb_testcase enrol/database/tests/adodb_test.php
        
        FAILURES!
        Tests: 1390, Assertions: 25422, Errors: 1.
        
        Show
        Eloy Lafuente (stronk7) added a comment - Found one more place in phpunit tests where support for dbschema is missing: There was 1 error: 1) core_adodb_testcase::test_read_table Trying to get property of non-object /Users/stronk7/git_moodle/integration/enrol/database/tests/adodb_test.php:100 /Users/stronk7/git_moodle/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: /opt/local/bin/phpunit core_adodb_testcase enrol/database/tests/adodb_test.php FAILURES! Tests: 1390, Assertions: 25422, Errors: 1.
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Fixed, the sync_test.php file was named adodb_test.php in 2.3, so I did not backported 92b00c3 properly to 23. Done now and passing phpunit tests.

        Ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Fixed, the sync_test.php file was named adodb_test.php in 2.3, so I did not backported 92b00c3 properly to 23. Done now and passing phpunit tests. Ciao
        Hide
        Eloy Lafuente (stronk7) added a comment -

        And your fantastic code has met core, hope they become good friends for a long period.

        Closing, thanks!

        Show
        Eloy Lafuente (stronk7) added a comment - And your fantastic code has met core, hope they become good friends for a long period. Closing, thanks!
        Hide
        Mary Cooch added a comment -

        If anyone thinks this needs to be added to the user docs, please feel free to do so as I don't really understand this enough to do it; otherwise I will remove the docs_required label.

        Show
        Mary Cooch added a comment - If anyone thinks this needs to be added to the user docs, please feel free to do so as I don't really understand this enough to do it; otherwise I will remove the docs_required label.
        Hide
        Mary Cooch added a comment -

        Removing docs_required as nobody seems to have strong feelings about the need to document this

        Show
        Mary Cooch added a comment - Removing docs_required as nobody seems to have strong feelings about the need to document this

          People

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

            Dates

            • Created:
              Updated:
              Resolved: