Hello,
It seems that the current database schema is not compatible with postgresql > 7.3 because of the usage of '' instead of NULL values.
The error that I got is (with debug set to on):
(postgres7): INSERT INTO mdl_user (confirmed, deleted, username, password, idnumber, firstname, lastname, email, icq, phone1, phone2, institution, department, address, city, country, timezone, firstaccess, lastaccess, lastlogin, currentlogin, lastip, secret, picture, url, description, mailformat, maildisplay, timemodified) VALUES ('1', '0', 'admin', '21232f297a57a5a743894a0e4a801fc3', '', 'Admin', 'User', 'root@localhost', '', '', '', '', '', '', '', '', '99', '0', '0', '0', '0', '', '', '', '', '', '1', '1', '1043819650')
-1: ERROR: pg_atoi: zero-length string
The following thread explains the error and change s in postgresql 7.3. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=15852.63621.790830.748776%40kelvin.csl.co.uk&rnum=1&prev=/groups%3Fq%3Dpg_atoi%253A%2Bzero-length%2Bstring%2B%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
I tied to change the postgres7.sql default value from '' to ' ' (note the space) and recreate the database, but it stops at the same point.
What part of the code can I change to create a insert with ' ' instead of ''?
Are there any consequences of doing so?
TIA
Using the cvs datalib.php version, it gives the following error:
(postgres7): INSERT INTO mdl_course (category, fullname, shortname, summary, format, newsitems, teacher, teachers, student, students, numsections, timemodified) VALUES ('', 'ed', 'ed', 'ed', 'social', '0', 'Teacher', 'Teachers', 'Student', 'Students', '1', '1043858103')
-1: ERROR: pg_atoi: zero-length string
Serious Error! Could not set up the site!
It seems that there is a missing category.
The same thing happens with the nightly build.
(postgres7): INSERT INTO mdl_course (category, fullname, shortname, summary, format, newsitems, teacher, teachers, student, students, numsections, timemodified) VALUES ('', 'ed', 'ed', 'ed', 'social', '0', 'Teacher', 'Teachers', 'Student', 'Students', '1', '1043858103')
-1: ERROR: pg_atoi: zero-length string
Serious Error! Could not set up the site!
It seems that there is a missing category.
The same thing happens with the nightly build.
IMHO, the problem lies on the default_value used on the DDL, such as:
password varchar(50) NOT NULL default ''
The default value is '' not supported on Postgresql 7.3
In the datalib.php file, the insert_record will check if the column has a default value, and it will assign the value if there is a defaul value.
Then, it will populate the column with the values passed.
In the "password" example, if I want to insert a record and I do not specify the password the insert_record function will populate it with the default value, which is ''. I believe that this is where the problem lies.
The possible solutions that I see are:
1) Change the DDL to use default values of ' ' instead of ''.
2) Change the DDL to use NULL values instead of ''
3) Change insert_record function, so it will add a space ' ' instead of an empty string '' if the database type is postgresql7 and the default value is an empty space ''
if ($CFG->dbtype == "postgres7") {
if ($column->name == "''") {
$ddd[$column->name] = $column->' ';
}
}
I haven't test this yet since moodle will not show up with this changes.
Is there any consequences in the application to use a space instead of a empty value?
What solution should we look into?
What solutions do you propose?
TIA
password varchar(50) NOT NULL default ''
The default value is '' not supported on Postgresql 7.3
In the datalib.php file, the insert_record will check if the column has a default value, and it will assign the value if there is a defaul value.
Then, it will populate the column with the values passed.
In the "password" example, if I want to insert a record and I do not specify the password the insert_record function will populate it with the default value, which is ''. I believe that this is where the problem lies.
The possible solutions that I see are:
1) Change the DDL to use default values of ' ' instead of ''.
2) Change the DDL to use NULL values instead of ''
3) Change insert_record function, so it will add a space ' ' instead of an empty string '' if the database type is postgresql7 and the default value is an empty space ''
if ($CFG->dbtype == "postgres7") {
if ($column->name == "''") {
$ddd[$column->name] = $column->' ';
}
}
I haven't test this yet since moodle will not show up with this changes.
Is there any consequences in the application to use a space instead of a empty value?
What solution should we look into?
What solutions do you propose?
TIA
Hello,
I have finally did some changes to the code in order to make it work with a standard Postgresql 7.3.x
The files I had to change from the nightly build version 1.0.9 are:
1) Change postgres7.sql file to spaces instead of empty ones. '' ' '
2) Doing this will work until the site creation section that will complain about inserting an empty value. The solution in my case was to change the followin line in the site.html file.
Doing so, it will insert the 0 value (the defaul) instead of a '' value accoring to the sql statement while using the debug turned on.
Now, I have to test is the site works properly or not. I will keep you posted.
I have finally did some changes to the code in order to make it work with a standard Postgresql 7.3.x
The files I had to change from the nightly build version 1.0.9 are:
1) Change postgres7.sql file to spaces instead of empty ones. '' ' '
2) Doing this will work until the site creation section that will complain about inserting an empty value. The solution in my case was to change the followin line in the site.html file.
Doing so, it will insert the 0 value (the defaul) instead of a '' value accoring to the sql statement while using the debug turned on.
Now, I have to test is the site works properly or not. I will keep you posted.
Another change so we can list the users and solves this error:
(postgres7): SELECT id, username, email, firstname, lastname, city, country, lastaccess FROM mdl_user WHERE username 'guest' AND deleted '1' ORDER BY firstname ASC LIMIT 30,0 -1: ERROR: LIMIT #,# syntax not supported. Use separate LIMIT and OFFSET clauses.
Change in datalib.php file:
function get_users_listing($sort, $dir="ASC", $page=1, $recordsperpage=20) {
global $CFG;
if ($CFG->dbtype == "mysql") {
$limit = "LIMIT $page,$recordsperpage";
} else {
$limit = "LIMIT $recordsperpage OFFSET $page";
(postgres7): SELECT id, username, email, firstname, lastname, city, country, lastaccess FROM mdl_user WHERE username 'guest' AND deleted '1' ORDER BY firstname ASC LIMIT 30,0 -1: ERROR: LIMIT #,# syntax not supported. Use separate LIMIT and OFFSET clauses.
Change in datalib.php file:
function get_users_listing($sort, $dir="ASC", $page=1, $recordsperpage=20) {
global $CFG;
if ($CFG->dbtype == "mysql") {
$limit = "LIMIT $page,$recordsperpage";
} else {
$limit = "LIMIT $recordsperpage OFFSET $page";
I think there might be problems throughout Moodle using a space to replace NULLS or empty strings, as they are not equivalent.
"" != " "
It would be better overall to patch insert_record() to handle these little non-standard quirks of PostgreSQL 7.3. (I wish Postgres had just made their software backward-compatible
)
"" != " "
It would be better overall to patch insert_record() to handle these little non-standard quirks of PostgreSQL 7.3. (I wish Postgres had just made their software backward-compatible