Hello,

I am not sure that this is the right place to post. I am using latest Drupal + CCK from SVN.

could create three custom fields. Then I added a page to test the three fields. After validation, this error was displayed:

Warning: pg_query() [function.pg-query]: Query failed: ERREUR: valeur trop longue pour le type character varying(128) in /home/html/drupal-6.2/includes/database.pgsql.inc on line 138

Warning: ERREUR: valeur trop longue pour le type character varying(128) query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', '%message in %file on line %line.', 'a:4:{s:6:"%error";s:6:"notice";s:8:"%message";s:26:"Undefined index: keywords";s:5:"%file";s:56:"/home/html/drupal-6.2/modules/nodewords/nodewords.module";s:5:"%line";i:464;}', 3, '', 'http://www.foobar.com/drupal-6.2/node/add/news', 'http://www.foobar.com/drupal-6.2/admin/user/captcha/captcha/captcha_poin...', '81.56.186.111', 1215390879) in /home/html/drupal-6.2/includes/database.pgsql.inc on line 159

Warning: pg_query() [function.pg-query]: Query failed: ERREUR: valeur trop longue pour le type character varying(128) in /home/html/drupal-6.2/includes/database.pgsql.inc on line 138

Warning: ERREUR: valeur trop longue pour le type character varying(128) query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', '%message in %file on line %line.', 'a:4:{s:6:"%error";s:6:"notice";s:8:"%message";s:29:"Undefined index: description";s:5:"%file";s:56:"/home/html/drupal-6.2/modules/nodewords/nodewords.module";s:5:"%line";i:464;}', 3, '', 'http://www.foobar.com/drupal-6.2/node/add/news', 'http://www.foobar.com/drupal-6.2/admin/user/captcha/captcha/captcha_poin...', '81.56.186.111', 1215390879) in /home/html/drupal-6.2/includes/database.pgsql.inc on line 159

I connected to PostgreSQL and promoted watchdog->referer from varchar(128) to varchar() which means no limit.
The error was gone.

I don't know if this relies to CCK or some core Drupal module.
This error should be corrected ASAP.

Kind regards,
Jean-Michel

Comments

yched’s picture

yched commented
Title: Error inserting CCK custom page under PostgreSQL » PgSQL error when inserting varchar(n) string longer than n ?
Project: Content Construction Kit (CCK) » Drupal core
Version: 6.x-2.x-dev » 6.x-dev
Component: CCK in core » database system

Not related to CCK in any way. Take a look a at the actual error messages.

2 things in there :
- Your setup produces errors when inserting records in the watchdog table, because, as you found out, the incoming 'referer' data is often longer than 128 chars. MySQL silently truncates varchar(n) values longer than n, I dunno what's the expected behavior with PgSQL - maybe there's a PgSQL setting for this ?
Leaving the status as critical for now, although I doubt it's actually a drupal bug.

- those insert attempts in watchdog are supposed to log PHP notices produced by the nodewords module. Those errors should be reported to that module separately.

[edit:
PS - Friendly advice : A sentence like 'This error should be corrected ASAP' is something you might want to avoid. It does not serve any purpose and there's something demanding in it that is simply counter productive in a community that's a) mainly unpaid volunteers, b) clever enough to acknowledge a critical bug :-)
]

grub3’s picture

grub3 commented
Assigned: Unassigned » grub3

Thanks. I will take a look at this PostgreSQL issue.
Any SQL92 database returns an error when inserting too long values.

A fix would be to substring 128 in PHP.
I self-assigned this issue.

bobsinglar’s picture

bobsinglar commented
Version: 6.x-dev » 6.15

Hello,

I'm facing the same problem using the last versions of Drupal (6.15) and PostgreSQL (8.4) under Ubuntu 9.10.

This message happened while using the "workflow" and "action email role" modules and changing the state of a content from "draft" to "need to be rewieved". Here is the full trace :

warning: pg_query() [function.pg-query]: Query failed: ERREUR: valeur trop longue pour le type character varying(16) in /var/www/drupal6_01/includes/database.pgsql.inc on line 139.
user warning: query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (9, 'action_email_role', 'Sent email to %recipient', 'a:1:{s:10:"%recipient";s:22:"pa.cauquil@laposte.net";}', 5, '', 'http://134.246.102.12/drupal6_01/?q=node/3/edit', 'http://134.246.102.12/drupal6_01/?q=node/3/edit', '134.246.102.12', 1264497976) in /var/www/drupal6_01/modules/dblog/dblog.module on line 144.
warning: pg_query() [function.pg-query]: Query failed: ERREUR: valeur trop longue pour le type character varying(16) in /var/www/drupal6_01/includes/database.pgsql.inc on line 139.
user warning: query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (9, 'action_email_role', 'Sent email to %recipient', 'a:1:{s:10:"%recipient";s:21:"pascalcauquil@free.fr";}', 5, '', 'http://134.246.102.12/drupal6_01/?q=node/3/edit', 'http://134.246.102.12/drupal6_01/?q=node/3/edit', '134.246.102.12', 1264497977) in /var/www/drupal6_01/modules/dblog/dblog.module on line 144.
warning: pg_query() [function.pg-query]: Query failed: ERREUR: valeur trop longue pour le type character varying(16) in /var/www/drupal6_01/includes/database.pgsql.inc on line 139.
user warning: query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (9, 'action_email_role', '!emailed users emailed successfuly.', 'a:1:{s:8:"!emailed";i:2;}', 5, '', 'http://134.246.102.12/drupal6_01/?q=node/3/edit', 'http://134.246.102.12/drupal6_01/?q=node/3/edit', '134.246.102.12', 1264497977) in /var/www/drupal6_01/modules/dblog/dblog.module on line 144.

What to do ?

thank you,
bob

grub3’s picture

grub3 commented

This is a normal situation where fields have a shorter size than needed. PostgreSQL reports a problem. You cannot inset a 500 char value in a 255 char field.

The same applies for me here:

INSERT INTO locales_source (location, source, textgroup) VALUES (' uc_store/uc_store.module:2919,2960,2919,2960,2940,2981,2941,2982,2930,2971,2930,2971,2930,2971,2933,2974,2659,2702,2665,2708,2688,2731,2685,2728,2685,2728,2692,2735,1587,1630,2692,2735,1587,1630,1587,1630,1620,1663,1647,1690,1658,1701,1716,1759,1723,1766,1723,1766,1791,1834,1791,1834', 'Source string contains an invalid character (!char)', 'default')

location is a 255 varchar when it should be a text value.

grub3’s picture

grub3 commented

The solution is to turn a number of varchar(255) into text values at database abstraction layer.

grub3’s picture

grub3 commented

I ran this command:

ALTER TABLE locales_source ALTER "location" TYPE text;

To correct type by hand. I can only recommand using it when type is wrong.

grub3’s picture

grub3 commented

I added an issue in the developer manual:

MySQLism: use TEXT values and not VARCHAR(255)
http://drupal.org/node/715726

When inserting TEXT into a VARCHAR(255), MySQL trims the value to the first 255 characters. PostgreSQL complains and returns an error, which the correct behavior.

I hope that Drupal can get fixed on this issue ... As MySQL does not complain, this bug is unseen. It is maybe Drupal most annoying bug, as it trims and destroys data, and noone complains.

grub3’s picture

grub3 commented
Title: PgSQL error when inserting varchar(n) string longer than n ? » Grave bug: MySQL trims and destroys data

Renaming bug to help hackers handle this MySQL problem.

andypost’s picture

andypost
he/him
Primary language Russian
commented
Status: Active » Closed (duplicate)

seems like duplicate of #221020: locales_source.location is just varchar(255) and it is not truncated

also dblog."type" column is related to contrib module and should be fixed in it's own queue