Databases: Errors after functional db test

Databases: Errors after functional db test

by brian avery -
Number of replies: 9

I have a newly upgraded 2.0+ site running on hostgator (linux). I have just run a functional DB test from inside moodle 2.0, partly because everything is so slow and also because a few things aren't working properly since upgrading from 1.9.10+.  I got the following 3 errors and 1 exception:

Fail: lib/dml/simpletest/testdml.php / dml_test / test_unique_index_collation_trouble
Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation. at [/home/username/public_html/insegnanti-inglese.com/moodle/lib/dml/simpletest/testdml.php line 2769]
Exception: lib/dml/simpletest/testdml.php / dml_test / test_unique_index_collation_trouble
Unexpected exception of type [dml_write_exception] with message [Error writing to database] in [/home/username/public_html/insegnanti-inglese.com/moodle/lib/dml/moodle_database.php line 394] 
Debug info:
Duplicate entry 'aäa' for key 'mdl_unittabl_nam_uix'
INSERT INTO mdl_unit_table (name) VALUES(?)
[array (
  0 => 'aäa',
)]
  • line 843 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 885 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 2763 of /lib/dml/simpletest/testdml.php: call to mysqli_native_moodle_database->insert_record()
  • line ... of ...
Fail: lib/dml/simpletest/testdml.php / dml_test / test_sql_binary_equal
SQL operator "=" is expected to be accent sensitive at[/home/username/public_html/insegnanti-inglese.com/moodle/lib/dml/simpletest/testdml.php line 2797]
Fail: lib/dml/simpletest/testdml.php / dml_test / test_sql_binary_equal
SQL operator "=" is expected to be case sensitive at[/home/username/public_html/insegnanti-inglese.com/moodle/lib/dml/simpletest/testdml.php line 2800]
2/2 test cases complete: 962 passes, 3 fails and 1 exceptions.
Run at Monday, 6 December 2010, 09:22 PM. Time taken: 9 secs. Using SimpleTest version 1.0.1.

 

Can anyone say what these relate to and if they can be fixed?

If not, I'm debating deleting the whole install and the contents of my database and starting again. I can go to phpadmin in cpanel and look inside the database.   Rather than set another database up from scratch, is it possible just to delete all the tables from inside the db (so it's empty) before installing moodle again?   It's just that if there is some sort of error in the database I don't want it to carry over into a new install.

Thanks.

Brian

Average of ratings: -
In reply to brian avery

Re: Databases: Errors after functional db test

by Steve Iman -

Dear Brian: I have little skill at this, but a friend spent a couple of days getting php up to speed and setting up a new install of Moodle 2. I get the same errors. Same962 passes, 3 fails and 1 exception though the precise terms used to describe my 'exceptions' vary slightly from yours.

Average of ratings: -
In reply to Steve Iman

Re: Databases: Errors after functional db test

by brian avery -

Hi,

Well, this post and another similar to it hasn't received any sort of response with info so I guess that means either nobody reads these posts or the errors are insignificant. Interesting that two installs have similar problems. As the errors have been created in a previously empty database it must be something moodle is doing. When the next release is available I'll wipe this install and try the newer version and see if it's the same. I'm wary of adding any sort of content because I have no idea if these database errors will screw thinsg up in the future. Nobody else seems to be complaining, so...

If you fix your errors I'd love to know how. What I know about databases could be printed on a fly's ear.

Average of ratings: -
In reply to brian avery

Re: Databases: Errors after functional db test

by brian avery -

Can someone please offer some sort of answer to this question?I posted it 4 days ago but am still none the wiser.

I have a fresh install (Build: 20101130) and I've got what appears to be a database error the very first time I tried to import something (a glossary).

I have just tried importing glossary entries into 2.0. I located the file on my pc, uploaded it and then when the upload is complete, I select 'submit' as is required. After thinking about it, moodle displays the message  "Field "lower(concept)" does not exist in table "glossary_entries".

Does the current release of 2.0+ contain errors in the db or not? I'd like to know if I'm wasting my time adding content. It seems so.

Thank you.

Average of ratings: -
In reply to brian avery

Re: Databases: Errors after functional db test

by Eloy Lafuente (stronk7) -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi Brian,

regarding the tests not passing in your site, I'd say it's normal to get them if you are using any of the utf8_xxxx_ci collations for your DB/tables/columns in MySQL. All those collations are case insensitive (ci) and it means (basically) that "A" and "a" are considered to be the same character when performing comparisons and other operations.

MySQL only offers one case sensitive collation, utf8_bin, that will make those tests to pass but, as a drawback, it will break ordering if you are using characters different from the English ones (accents, non-latin ones...) in your site. If your site is entirely English and you don't care about being slightly incorrect when ordering user names, I would recommend to start from scratch and configure you db/table/columns to be utf8_bin.

About the "Field "lower(concept)" does not exist in table "glossary_entries" error, it was detected and fixed hours after release so current downloads of Moodle 2.0+ should have it fixed when adding new entries.

Ciao smile

Average of ratings: Useful (1)
In reply to Eloy Lafuente (stronk7)

Re: Databases: Errors after functional db test

by brian avery -

Eloy,

Thanks for that. I take it the errors you refer to relate to all the 4 flagged by moodle?  As for names, I'm creating English language courses for overseas users..and at present I'm aiming to use Italian, Spanish, Catalan, French and German as languages. Obviously, almost all will have non-English names. Still, whatever collocations are being used (I have no idea how to check or tell) they must have been set by moodle at setup.

As for the glossary issue, I also got a reply in the glossary forum and although the item was flagged and 'fixed' quickly (MDL-25418), it seems that the fix was only partial and still needs sorting properly. That's why I didn't find the issue when I searched - I only looked for reported but not fixed issues!

Anyway, thank you for taking the time to help.

Brian

Average of ratings: -
In reply to brian avery

Re: Databases: Errors after functional db test

by Eloy Lafuente (stronk7) -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi Brian,

regarding "collations" Moodle never sets any collation at all. It simply uses the one defined as default (in the whole MySQL server or in the particular DB you are using to install Moodle). So it is something that must be defined before starting the installation process. In any case, don't worry too much about that, (personal opinion), you will find everything works reasonably (98%) ok, no matter of the collation used.

About the glossary problem (MDL-25418), correct, after fixing it, two more-related problems were found (same error, different place within glossary activity), so i has been reopened, "refixed" and reclosed (hopefully forever) tongueout

Ciao smile

Average of ratings: -
In reply to Eloy Lafuente (stronk7)

Re: Databases: Errors after functional db test

by brian avery -

Hi,

Thanks for that. I've had several problems with moodle 2 so was concerned that a database error was going to cause future problems if I ignored it. I have an inbuilt resistance to ignoring stuff when I know it's broken. Obsessive, my partner calls it!

At least the glossary thing is a minor problem. Now I can move on and break something else...

Brian

Average of ratings: -
In reply to Eloy Lafuente (stronk7)

Re: Databases: Errors after functional db test

by Jean-Philippe Gaudreau -

Hi Eloy,

Thx! Finally I found an answer about the CI/CS UTF8 collations for Moodle 2! It seems, from what I understand, that I need to use utf8_XXXXX_ci because the Moodle we have is not only in english but also in french?

Can you tell me what is the risk of using accent insensitive collation? Why is there some unit tests about that?

Average of ratings: -