Setting up database

Setting up database

by James Heideman -
Number of replies: 17
I go through the process and get to the point where moodle builds the database and I get a few errors and it says the database never fully installs.

(mysql): ALTER TABLE mdl_log_display COMMENT='For a particular module/action, specifies a moodle table/field'
1105: Too long comment for table '#sql-e0c_f9'
ErrorScroll to next warning

This is the first error I get I can post the other 7, but till someone request it I will keep it short and simple.

Average of ratings: -
In reply to James Heideman

Re: Setting up database

by Don Sheets -

I am having the same issue. I am installing on the following:

Moodle 1.7

Windows 2000 PRO SP 4

IIS

MySQL 5.x

Any help on getting Moodle installed would be great.

Donald

Average of ratings: -
In reply to Don Sheets

Re: Setting up database

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Donald,

> I am having the same issue.
Is it exactly the same issue? Do you get the same error message about the same comment on the same table? If so, please try the steps given for isolating the problem with the comment.

If you got different error messages, please cut and paste the first error message you got.

regards
Gordon
Average of ratings: -
In reply to Gordon Bateson

Re: Setting up database

by Thomas Haynes -
Here is the error message for me.

I am having problems installing 1.7 on a Windows machine. I am using php5.1.X in ISAPI mode and MySQL 5.0.X

I am using php5.1.X and MySQL 5.0.X with Moodle 1.6.X on another machine with success.

1105: Too long comment for table '#sql-254_cc'

Average of ratings: -
In reply to James Heideman

Re: Setting up database

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
James,
the comment looks fine to me, but I have read about other problems with comments and MySQL 5 (here and here).

Let's try to isolate what's wrong with the comment.

Please could you use your favorite DB management tool, e.g. phpMyAdmin, to access the database directly and run the following SQL queries:
  1. confirm the current comment is unacceptable:
    CREATE TABLE `mdl_log_display` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `module` varchar(20) NOT NULL default '',
    `action` varchar(40) NOT NULL default '',
    `mtable` varchar(30) NOT NULL default '',
    `field` varchar(50) NOT NULL default '',
    PRIMARY KEY (`id`)
    ) TYPE=MyISAM COMMENT='For a particular module/action, specifies a moodle table/field.';

  2. remove the slash from the comment
    CREATE TABLE `mdl_log_display` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `module` varchar(20) NOT NULL default '',
    `action` varchar(40) NOT NULL default '',
    `mtable` varchar(30) NOT NULL default '',
    `field` varchar(50) NOT NULL default '',
    PRIMARY KEY (`id`)
    ) TYPE=MyISAM COMMENT='For a particular module action, specifies a moodle table field.';

  3. make the comment shorter:
    CREATE TABLE `mdl_log_display` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `module` varchar(20) NOT NULL default '',
    `action` varchar(40) NOT NULL default '',
    `mtable` varchar(30) NOT NULL default '',
    `field` varchar(50) NOT NULL default '',
    PRIMARY KEY (`id`)
    ) TYPE=MyISAM COMMENT='Map     a module and action to a table and field';

  4. make the comment a blank string
    CREATE TABLE `mdl_log_display` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `module` varchar(20) NOT NULL default '',
    `action` varchar(40) NOT NULL default '',
    `mtable` varchar(30) NOT NULL default '',
    `field` varchar(50) NOT NULL default '',
    PRIMARY KEY (`id`)
    ) TYPE=MyISAM COMMENT='';

  5. do not define a comment
    CREATE TABLE `mdl_log_display` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `module` varchar(20) NOT NULL default '',
    `action` varchar(40) NOT NULL default '',
    `mtable` varchar(30) NOT NULL default '',
    `field` varchar(50) NOT NULL default '',
    PRIMARY KEY (`id`)
    ) TYPE=MyISAM;
Please let us know which, if any, of the above queries is successful.
Gordon
Average of ratings: -
In reply to Gordon Bateson

Re: Setting up database

by Jos Winkel -

I am having the same problems and executed the queries. Numbers 1 and 2 were not succesful. Error message both times: "Too long comment for table mdl_log_display".
The third one did execute without any problems.
Configuration: IIS 6, MySQL 5.0.27, PHP 5.2
I hope this can be fixed. Thanks!

Average of ratings: -
In reply to Jos Winkel

Re: Setting up database

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
HI Jos,
thanks for the feedback.

On your site, it seems that comments on the database tables are not allowed. Maybe this is because you are in ISAPI mode. I don't know. But perhaps we can add a dirty hack to your Moodle scripts, so that Moodle never attempts add comments to your database tables.
  1. open "lib/adodb/adodb.inc.php" with a text editor
  2. locate the following lines (around line 886):
    function &_Execute($sql,$inputarr=false)
    {
  3. change the above lines to the following:
    function &_Execute($sql,$inputarr=false)
    {
    $sql = preg_replace("/COMMENT='.*?'/", "COMMENT=''", $sql);
    i.e. add the last line which removes comments from the $sql
  4. save the modified "lib/adodb/adodb.inc.php"
Now try the install again and let us know how you get on.

regards
Gordon
Average of ratings: -
In reply to Gordon Bateson

Re: Setting up database

by Jos Winkel -

Hi gordon,

The 1105 error does after your hack not show up anymore, but I still cannot set up all the tables. I get following error:

1101: BLOB/TEXT column 'sessdata' can't have a default value
         ADOConnection._Execute(CREATE TABLE mdl_sessions2 (
    sesskey VARCHAR(64) NOT NULL DEFAULT '',
    expiry DATETIME NOT NULL,
    expireref VARCHAR(25..., false) % line  889, file: adodb.inc.php
     ADOConnection.Execute(CREATE TABLE mdl_sessions2
..............................

later followed by more errors because Table 'moodle.mdl_sessions2' doesn't exist but these will be solved when the above one is fixed.

Regards,
Jos

Average of ratings: -
In reply to Jos Winkel

Re: Setting up database

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

HI Jos,
you are installing, right? OK, please try this:

  1. open "lib/db/install.xml" with a text editor
  2. locate the following line:
    <FIELD NAME="sessdata" TYPE="text" LENGTH="big" NOTNULL="false" DEFAULT="" SEQUENCE="false" ENUM="false" PREVIOUS="modified"/>
  3. change the above line to:
    <FIELD NAME="sessdata" TYPE="text" LENGTH="big" NOTNULL="false" SEQUENCE="false" ENUM="false" PREVIOUS="modified"/>
    i.e. remove DEFAULT=""
  4. save the modified file
  5. retry the installation

regards
Gordon

Average of ratings: -
In reply to Gordon Bateson

Re: Setting up database

by Jos Winkel -

Hi Gordon,
Perfect! The installation is running smoothly now with your two changes of the code.
Thanks and regards,
Jos

Average of ratings: -
In reply to Jos Winkel

Re: Setting up database

by Martin Anderson -
I ran into both these same problems when doing an install on a test machine (WinXP, Apache 2.2, MySQL 5.0.27, PHP 5.2.0) using the latest Moodle build (not CVS). The adodb problem seems to have something to do with comment field length being limited in MySQL (atleast in Windows?) and the comments that are called in the XML files being too long. The installer for Moodle, on errors, then tries to reinstall the same information for the section that was being installed (i.e. quizzes, forums, etc) when the comment error was reached and also to reinstall into the log_display table, resulting in duplicate key errors like those reported in forum posts (examples here and here).

The advice Gordon provides does help resolve the issue with the database (adodb) comment fields as well as the other (sessdata) database install problem.

I tried looking for a bug report but I couldn't find one for this issue. I'll try to put one in.
Average of ratings: -
In reply to Gordon Bateson

回复: Re: Setting up database

by xy l -

so perfect~~~~~~~

thanks very much~~~~~~~~

Average of ratings: -
In reply to Gordon Bateson

Re: Setting up database

by Mahendran Balakrishnan -
hi there guys

after doing everything that was mentioned, im left with one single problem

(mysql): ALTER TABLE elearn_forum_queue COMMENT='For keeping track of posts that will be mailed in digest form'
1105: Too long comment for table '#sql-7bc_43d'


That is the only single problem im facing now, ive done the 2 steps Gordon gave, including the Comment string replace..but that particular thing kept coming up,,.
]
where can i edit the SQL statements manually?? any help, please, urgent huhu~
Average of ratings: -
In reply to Mahendran Balakrishnan

Re: Setting up database

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
> where can i edit the SQL statements manually?

The comment you are having trouble with is defined in the following files:
  • moodle/mod/forum/db/mysql.php
  • moodle/mod/forum/db/mysql.sql
good luck!
Gordon
Average of ratings: -
In reply to Gordon Bateson

Re: Setting up database

by Mahendran Balakrishnan -
where can i edit the SQL statements manually?? any help, please, urgent huhu~
thkx, as now im having prblm with only one thing,

Too long comment for table '#sql-7bc_721'
the rest of comments are installed, as i did what Mr Gordon suggested, added the str replace below the line..
Average of ratings: -
In reply to Mahendran Balakrishnan

Re: Setting up database

by Gordon Bateson -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Hi Mahendran Balakrishnan,

I suggest you try Ken Wilson's idea to switch off "strict mode" in MySQL:
http://moodle.org/mod/forum/discuss.php?d=62812&parent=283652
Average of ratings: -
In reply to Gordon Bateson

Re: Setting up database

by Glenn Whitcomb -
I struggled several times with the errors even after I made the modifications suggestion.  What I found finally worked for me is if you have a failed installation delete the database, and the moodle folder and moodle data directories.  Start from scratch, creating your Moodle database, copy the moodle folder into your "www" root and then make the two suggested changes prior to starting the /moodle/install.php installation.  Once I did this it worked perfectly.  Thank you for your tips.
Average of ratings: -
In reply to Gordon Bateson

Re: Setting up database

by Justin P -
I got this same error while trying to install 1.8+. I only had to remove "STRICT_TRANS_TABLES," from the my.ini file, delete the tables and moodle folder, then recreate moodle folder and try install again and it worked! I figured it would be better to only take out what needs to be rather then commenting out that whole line.
Average of ratings: -