I haven't succeeded in finding exactly this problem reported (but similar) for 7.0. When I want to translate a string (into Swedish in this case) and try to go to admin/config/regional/translate/translate, I receive this error (and no real page shows up):

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':db_condition_placeholder_1 ESCAPE '\\') )) subquery' at line 2: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {locales_source} s LEFT OUTER JOIN {locales_target} t ON t.lid = s.lid WHERE ( (s.source LIKE :db_condition_placeholder_0 ESCAPE '\\') OR (t.translation LIKE :db_condition_placeholder_1 ESCAPE '\\') )) subquery; Array ( [:db_condition_placeholder_0] => %% [:db_condition_placeholder_1] => %% ) i PagerDefault->execute() (rad 75 av /xyz/includes/pager.inc).

I test with a clean install of Drupal 7.0 with standard theme and cache cleaned.

Comments

aspilicious’s picture

aspilicious commented
Priority: Critical » Major

Not critical as it doesn't brake your site :)
I translate all the time never saw this issue...

bfroehle’s picture

bfroehle commented

Running the query
SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM locales_source s LEFT OUTER JOIN locales_target t ON t.lid = s.lid WHERE ( (s.source LIKE '%%' ESCAPE '\\') OR (t.translation LIKE '%%' ESCAPE '\\') )) subquery doesn't generate any errors for me.

What version of MySQL are you using?

bengt’s picture

bengt commented

My version is of MySQL is 5.0.67. Only non-core module activated is Backup and Migrate 7.x-2.1. The site is installed as multi-site in settings.php:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'xxx',
      'username' => 'xxx',
      'password' => 'xxx',
      'host' => 'mysql.xxx.se',
      'port' => '',
      'driver' => 'mysql',
        'prefix' => array(
            'default'   => 'main_',
            'users'     => 'shared_',
            'sessions'  => 'shared_',
            'role'      => 'shared_',
            'authmap'   => 'shared_',
        ),
    ),
  ),
);
plach’s picture

plach
he/him
Primary language Italian
Location Venezia
commented
Component: language system » locale.module
damien tournoud’s picture

damien tournoud commented
Status: Active » Postponed (maintainer needs more info)

Marked #1092126: Sql-error when searching for more than one word, because the root cause is probably the same.

Can you give us as much information as possible about your environment (especially the exact version of each component)? The error you are seeing seems to take its source in a broken version of PHP or PDO.

bengt’s picture

bengt commented

I can give you all phpinfo if you want, but here is a digest: 

System 	Linux www16 2.6.24.2-grsec #6 SMP Sun Jan 3 01:55:08 CET 2010 i686
Build Date 	Oct 27 2010 21:53:08
Configure Command 	./configure --with-jpeg-dir=/usr --with-sqlite=shared --with-openssl --with-mysqli=/usr/local/bin/mysql_config --with-sybase=/usr/local --with-pdo-sqlite=shared --enable-memory-limit --enable-calendar --with-xml --enable-gd-imgstrttf --enable-sockets --with-tidy --enable-wddx --enable-pcntl --enable-ftp --enable-fastcgi --with-xmlrpc --enable-soap --with-gettext=/usr --with-fdftk --enable-dbx --enable-bcmath --with-pspell --with-freetype-dir=/usr/local/include/freetype2 --with-pdo-mysql=shared,/usr/local/bin/mysql_config --with-iconv --enable-dbase --with-imap --with-zip --enable-debug=no --with-xsl --enable-trans-sid --with-imap-ssl --with-mysql=/usr/local --with-mcrypt --enable-track-vars --with-zlib --disable-posix-threads --with-pdflib --enable-zip --with-dom --with-libxml-dir=/usr --with-gd --enable-exif --with-tiff-dir=/usr/lib --with-bz2 --with-curl --with-png-dir=/usr --with-config-file-path=/usr/local/lib/fcgi-php5 --enable-mbstring --enable-pdo=shared --enable-gd-native-ttf --with-ming --with-t1lib --with-mhash
Server API 	CGI/FastCGI
Virtual Directory Support 	disabled
Configuration File (php.ini) Path 	/usr/local/lib/fcgi-php5
Loaded Configuration File 	/usr/local/lib/fcgi-php5/php.ini
Scan this dir for additional .ini files 	(none)
additional .ini files parsed 	(none)
PHP API 	20041225
PHP Extension 	20060613
Zend Extension 	220060519
Debug Build 	no
Thread Safety 	disabled
Zend Memory Manager 	enabled
IPv6 Support 	enabled
Registered PHP Streams 	https, ftps, compress.zlib, compress.bzip2, php, file, data, http, ftp, zip
Registered Stream Socket Transports 	tcp, udp, unix, udg, ssl, sslv3, sslv2, tls
Registered Stream Filters 	zlib.*, bzip2.*, convert.iconv.*, string.rot13, string.toupper, string.tolower, string.strip_tags, convert.*, consumed

----------

mysql
MySQL Support	enabled
Active Persistent Links 	0
Active Links 	0
Client API version 	5.0.41
MYSQL_MODULE_TYPE 	external
MYSQL_SOCKET 	/tmp/mysql.sock
MYSQL_INCLUDE 	-I/usr/local/include/mysql
MYSQL_LIBS 	-L/usr/local/lib/mysql -lmysqlclient

Directive	Local Value	Master Value
mysql.allow_persistent	Off	Off
mysql.connect_timeout	60	60
mysql.default_host	no value	no value
mysql.default_password	no value	no value
mysql.default_port	no value	no value
mysql.default_socket	no value	no value
mysql.default_user	no value	no value
mysql.max_links	Unlimited	Unlimited
mysql.max_persistent	0	0
mysql.trace_mode	Off	Off

mysqli
MysqlI Support	enabled
Client API library version 	5.0.41
Client API header version 	5.0.45
MYSQLI_SOCKET 	/tmp/mysql.sock

Directive	Local Value	Master Value
mysqli.default_host	no value	no value
mysqli.default_port	3306	3306
mysqli.default_pw	no value	no value
mysqli.default_socket	no value	no value
mysqli.default_user	no value	no value
mysqli.max_links	Unlimited	Unlimited
mysqli.reconnect	Off	Off

----------

PDO
PDO support	enabled
PDO drivers 	mysql, sqlite2, sqlite

pdo_mysql
PDO Driver for MySQL, client library version	5.0.19

pdo_sqlite
PDO Driver for SQLite 3.x	enabled
PECL Module version 	(bundled) 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6 2006/01/01 12:50:12 sniper Exp $
SQLite Library 	3.3.7

As stated above, the MySQL server version is 5.0.67 (it is run on a web hosting).

Can you see any problems from this information? What more information could be of interest in this case?

Proj’s picture

Proj commented

Subscribe

bengt’s picture

bengt commented
Status: Postponed (maintainer needs more info) » Active

Changed status

sun’s picture

sun
Primary language German
Location Karlsruhe
commented

@bengt: What's the default collation of your database, and what's the collation of locale_* tables?

sun’s picture

sun
Primary language German
Location Karlsruhe
commented
Priority: Major » Normal
Status: Active » Postponed (maintainer needs more info)
bengt’s picture

bengt commented

@sun: I have the following:
Default Character set: "utf8 -- UTF-8 Unicode"
Default Collation: "utf8_general_ci"

If by " locale_*" you mean "locales_source" and "locales_target", then the collation is "utf8_general_ci" for both (all tables have that collation).

The collation for the "MySQL connection" is "utf8_unicode_ci".

bengt’s picture

bengt commented
Status: Postponed (maintainer needs more info) » Active
Flommy’s picture

Flommy commented

I'm getting the very same error message when I try to enter the "Translate"-tab.
Did you solve your problem bengt?

bengt’s picture

bengt commented

@Flommy: No, not yet. My web hosting company (FS Data) have been looking into it for a long time now, but haven't solved it yet. I guess you don't only have problems with Translate, but also searching for more than one word, or?

Flommy’s picture

Flommy commented

@Bengt: Funny thing, we are both using FS-data and translate won't work :P Haven't noticed any problems with searching yet

bengt’s picture

bengt commented
Version: 7.0 » 7.7

Have upgraded to D 7.7, but the problem is still there.

@Flommy: Have you found any solution yet?

Flommy’s picture

Flommy commented

@Bengt: Have also updated and no change. Haven't found any solution, hoping that it will sort itself out. Last resort is to recode the failing query(ies).

bengt’s picture

bengt commented

My web hosting company (FS Data) now confirmed that it is a PHP/PDO error and that it will be solved when they later upgrade to PHP 5.3 (now they have PHP 5.2.14). Does anyone know how to make a quick interims fix by recoding the failing query(ies) (e.g in pager.inc)?

angelg33k’s picture

angelg33k commented

Just FYI, I am also using FS Data and I am having the same problem.

On a side note, they told my client that they would have to contact the .se registrar, just to point the domain name to a different sub folder of the web root, and that it would take two days. Clowns.

ropaolle’s picture

ropaolle commented

EDIT: I also have this problem on FS Data, i.e. I get the error when I try to show the transaltion page or using more then one word in the search field.

@bengt: Did FS Data give you any indication of when they plan to update to 5.3?

aspilicious’s picture

aspilicious commented

Drupal 7: PHP 5.2.5* or higher (5.3 recommended)

You should always verify the minimum specs of drupal and your server before upgrading/installing drupal...

bengt’s picture

bengt commented

@ropaolle: Sometime during "the autumn".

@aspilicious: You are correct, but the version FS Data uses (as stated above), PHP 5.2.14, is a later version than the minimum version of PHP 5.2.5.

ropaolle’s picture

ropaolle commented

@bengt: Ok I see and thanks for the input. I'll also report this issue to FS Data, the more the merrier.

gerhard1176’s picture

gerhard1176 commented

I am having the same problem.
I refer to #18 above. According to a message we got from FS-Data yesterday, they have currently no definite plans to go to PHP 5.3.
Does anybody have a workaround?

gerhard1176’s picture

gerhard1176 commented

To my surprise, the error has magically disappeared! And the site is still running on PHP 5.2.14 (but FS-Data will install PHP 5.3 on request).

ropaolle’s picture

ropaolle commented

FS-Data added a symlink to another nod and asked me to try install Drupal there, but before I tried that I also found that the problem have disappeared. Magic! Maybe we can close this issue. Does it work for you too Bengt?

bengt’s picture

bengt commented

Yes, it seems suddenly to work. But why? I will ask FS Data and if I get an answer I will then close the issue.

bengt’s picture

bengt commented
Status: Active » Closed (works as designed)

FS Data reports that they have made several updates, but they don't know which update did solve this issue. Hmmm... just hope that it will not reappear.