By rsmithh on

It seems that Drupal 4.6.2 likes to try to insert an empty string into a database integer.

Here's the postgresql.log entry when adding a new page ...
LOG: statement: INSERT INTO node (title, body, format, uid, type, teaser, status, moderate, promote, sticky, comment, created, changed, nid) VALUES('asdasdasd', 'my page bodwqeqwy', '1', '2', 'page', 'my page bodwqeqwy', '1', '', '1', '', '2', '1122760641', '1122760641', '8')
ERROR: invalid input syntax for integer: ""

It also seems that MySQL permits this behaviour (bad MySQL!) whilst Postgresql doesn't.

Much as I hate to do it, the following will hack Postgres to make it as sloppy as MySQL.

src/backend/utils/adt/numutils.c
-----------------
93c
if (*badp && *badp != c && *s != 0)
.
77a
}

errno = 0;
.
76c
l=0;} else {
.
74c
errmsg("invalid input syntax for integer: \"%s\" zero length string interpereted as zero",
.
71,72c
if (*s == 0) {
ereport(LOG,
.
-----------------

Specifically it causes Postgresql to interpet an empty string as a numeric 0, putting out a LOG message to warn you what it's done.

It's ugly and I don't condone its use, but it works.

Comments

Rick Turner’s picture

Rick Turner commented

I've been getting a similar error:

warning: pg_query(): Query failed: ERROR: invalid input syntax for integer: "0, 1" in /path-to-drupal-site/drupal-4.6.2/includes/database.pgsql.inc on line 45.

The above came from a recent attempt to get fresh install of 4.6.2 running on top of a PostgreSQL v8.x database. (From the "Welcome to your ..." page. And no header or blocks are displayed.) In fact, I've had zero luck getting 4.6.anything running with PostgreSQL.

I did some investigation of this a while back (when 4.6.0 -- or was it 4.6.1 -- came out) and noticed that there are a slew of column definitions like:

blah integer not null default '0'

then some code will attempt to pass a null string to that column. The database (IMHO) rightfully complains. Specifying a default value is supposed to (as I understood it) cause a column to automatically be filled when it hasn't been specified, not for substituting a valid default value when bogus data is attempted to be loaded.

I'm not sure if I want to implement your fix as it turns a nice PostgreSQL database into a piece of junk. There might be a global setting that permits this behaviour but it just doesn't seem right. (And it ain't the way the big boys do databases. For whatever that's worth.)

Still plugging away...

geekonek’s picture

geekonek commented

I'm using postgresql and had to modify few things. To fix this issue i modified node.module
...

 // Prepare the query:
    foreach ($node as $key => $value) {
      if (in_array((string) $key, $fields)) {
        if ($value == '')
		continue;
	$k[] = db_escape_string($key);
        $v[] = $value;
        $s[] = "'%s'";
      }
    }

so every '' ( empty value ) will be just excluded from insert, and so a default will be used. This works for me :)

Rick Turner’s picture

Rick Turner commented

... than the brute force one I saw on another post. Nice.

I should have posted all of the error message, though. The rest of it was/is:

user error: 
query: SELECT * FROM blocks WHERE status = 1 AND region IN ('0, 1') ORDER BY weight, module in /path-to-drupal-site/drupal-4.6.2/includes/database.pgsql.inc on line 62.

and the '0, 1' value is at fault.

Just what is this query supposed to return?

I get the first login screen (with these error messages at the top of the page) and I can login but can do nothing. No blocks are displayed or accessible.

I'd sure like to get this version working. I won't be upgrading again, though. These upgrades are way, way too painful.

Rick Turner’s picture

Rick Turner commented

After some tweaking where I changed

'all' => '0, 1'

to

'all' => '\'0\',\'1\''

the problem with block was resolved. Along the way, however, I noticed that the system was still running PHP 2.3.10. Upgrading it to 2.3.11 fixed the odd handling of "'all' => '0, 1'" that was causing the trouble. While both forms work, I changed the definition back to the original form (it looks a bit cleaner). Apparently there was a bit of a change in the way quoted strings are handled between .10 and .11.

geekonek’s picture

geekonek commented

was to modify block module. I don't think it's proper to put integers in ''. My fix:

function block_list($region, $regions = array('left' => 0, 'right' => 1, 'all' => '0, 1')) {
.
.
.
$result = db_query("SELECT * FROM {blocks} WHERE status = 1 AND region IN (%s) ORDER BY weight, module", $regions[$region]);
 .
.
.
Cvbge’s picture

Cvbge commented

This fix has been applied to DRUPAL-4-6 branch

--
Polska strona o Drupalu: http://drupal.cvbge.org

kgad’s picture

kgad commented

This fix seems still necessary for drupal 4.6.4 and 4.6.5.

Cvbge’s picture

Cvbge commented

Please test http://drupal.org/node/48591

--
Polska strona o Drupalu: http://drupal.cvbge.org