I notice that you're using phpMyAdmin and the 'mysql' adapter - what adapter are you using for the framework? Pdo_Mysql or Mysqli? What happens if you try to use either of those adapters with that query?

The exception you're seeing indicates a driver-level exception rather than a Zend exception, so it would be good to narrow this down.

Hello Simon.

I am currently using the Pdo_Mysql adapter.

Switching to the Mysqli adapter yields:

'Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli prepare error: Operand should contain 1 column(s)'

Thanks for your assistance.

MySQL DB Dump:

-- phpMyAdmin SQL Dump -- version 2.11.4

-- http://www.phpmyadmin.net

-- Host: localhost -- Generation Time: May 23, 2008 at 04:08 PM -- Server version: 5.0.51 -- PHP Version: 5.2.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--

-- Database: zfapp

--

-- Table structure for table groups

DROP TABLE IF EXISTS groups; CREATE TABLE IF NOT EXISTS groups ( groupid char(36) collate latin1_general_ci NOT NULL, groupname varchar(48) collate latin1_general_ci NOT NULL, groupdescription text collate latin1_general_ci NOT NULL, PRIMARY KEY (groupid) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Dumping data for table groups

INSERT INTO groups (groupid, groupname, groupdescription) VALUES('2ce78951-7974-102b-8a2c-d23ebefec1c9', 'admin', 'This is the administrative group');

--

-- Table structure for table permissions

DROP TABLE IF EXISTS permissions; CREATE TABLE IF NOT EXISTS permissions ( permid char(36) collate latin1_general_ci NOT NULL, permname varchar(48) collate latin1_general_ci NOT NULL, permdescription text collate latin1_general_ci NOT NULL, PRIMARY KEY (permid) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Dumping data for table permissions

INSERT INTO permissions (permid, permname, permdescription) VALUES('991cdf89-7974-102b-8a2c-d23ebefec1c9', 'view', 'View things');

--

-- Table structure for table permissions2groups

DROP TABLE IF EXISTS permissions2groups; CREATE TABLE IF NOT EXISTS permissions2groups ( pid char(36) collate latin1_general_ci NOT NULL COMMENT 'permission id', gid char(36) collate latin1_general_ci NOT NULL COMMENT 'group id', PRIMARY KEY (pid,gid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Dumping data for table permissions2groups

INSERT INTO permissions2groups (pid, gid) VALUES('991cdf89-7974-102b-8a2c-d23ebefec1c9', '2ce78951-7974-102b-8a2c-d23ebefec1c9');

--

-- Table structure for table permissions2users

DROP TABLE IF EXISTS permissions2users; CREATE TABLE IF NOT EXISTS permissions2users ( pid char(36) collate latin1_general_ci NOT NULL COMMENT 'permission id', uid char(36) collate latin1_general_ci NOT NULL COMMENT 'user id', PRIMARY KEY (pid,uid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Dumping data for table permissions2users

--

-- Table structure for table users

DROP TABLE IF EXISTS users; CREATE TABLE IF NOT EXISTS users ( userid char(36) collate latin1_general_ci NOT NULL, status int(1) NOT NULL default '0', username varchar(36) collate latin1_general_ci NOT NULL, peaches char(40) collate latin1_general_ci NOT NULL, PRIMARY KEY (userid), KEY status (status) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Dumping data for table users

INSERT INTO users (userid, status, username, peaches) VALUES('5bc40bc6-7974-102b-8a2c-d23ebefec1c9', 1, 'travis', '5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8');

--

-- Table structure for table users2groups

DROP TABLE IF EXISTS users2groups; CREATE TABLE IF NOT EXISTS users2groups ( uguid char(36) collate latin1_general_ci NOT NULL COMMENT 'user id', uggid char(36) collate latin1_general_ci NOT NULL COMMENT 'group id', PRIMARY KEY (uguid,uggid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--

-- Dumping data for table users2groups

INSERT INTO users2groups (uguid, uggid) VALUES('5bc40bc6-7974-102b-8a2c-d23ebefec1c9', '2ce78951-7974-102b-8a2c-d23ebefec1c9');

Any thoughts on this issue?

I cannot see how this issue was resolved with 'not an issue'. I just ran into this error too (1.7.x). If you google the error, you only get results referring to ZF, which makes it obvious that it has got to be somehow related to ZF. Will try to reproduce later.

Can you please provide a small reproduction script of this? Something that will demonstrate behavior as well as with the db table it its querying against. As small as possible to trigger the error.

Thanks!

Hi,

I have the same error with this query on 1.8.1.

(I know, it's a baaaad query, very bad...)

{quote} $sql = << NOW()) as story_media_count FROM newsroom_has_source Inner Join story ON newsroom_has_source.source_id = story.source_id Inner Join story_text ON (story.story_id = story_text.story_id AND story_text.lang_iso = '$lang_iso') Inner Join story_has_term ON story.story_id = story_has_term.story_id Inner Join term ON (story_has_term.term_id = term.term_id AND term.lang_iso = '$lang_iso') WHERE newsroom_has_source.newsroom_id = '$newsroom_id' AND story.story_published = 1 AND story.story_publication_date < NOW() AND story.story_expiration_date > NOW() AND ( LOWER(story.story_town) LIKE LOWER('%$query%') OR LOWER(story.story_country) LIKE LOWER('%$query%') OR LOWER(story.story_reference) LIKE LOWER('%$query%') OR LOWER(story_text.field_value) LIKE LOWER('%$query%') OR LOWER(term.term_value) LIKE LOWER('%$query%') ) EOT;

    $res_search = $this->_dba->fetchAll($sql);

{/quote}

I have tried by removing the sub select and replace it with a simple column like term.term_id, but same error.

I use Pdo as adpater.

The query run in phpmyadmin and navicat 8.

Any idea ?

Thx!

Pv

There must be a shorter test case than what you provided? Can we get that down to just a few simple statements, also will need the db to test against.

-ralph

Yep sorry I was too lazy,

here is a shorter test:

    $sql = <<<EOT
                SELECT DISTINCT(story.story_id),
                    (SELECT COUNT(media.media_id) FROM media
                        WHERE media.story_id = story.story_id) as story_media_count
                FROM
                    story
                WHERE
                (
                    LOWER(story.story_reference) LIKE  LOWER('%$query%')
                )

EOT; $res_search = $this->_dba->fetchAll($sql);

and here is my table def:

-- Table structure for media

DROP TABLE IF EXISTS media; CREATE TABLE media ( media_id int(11) NOT NULL AUTO_INCREMENT, story_id int(11) NOT NULL, media_reference varchar(255) DEFAULT NULL, media_duration varchar(20) DEFAULT NULL, media_origin varchar(255) DEFAULT NULL, media_publication_date datetime DEFAULT NULL, media_expiration_date datetime DEFAULT NULL, media_order int(11) DEFAULT NULL, media_embedded tinyint(1) DEFAULT NULL, PRIMARY KEY (media_id), KEY fk_media_story (story_id), CONSTRAINT fk_media_story FOREIGN KEY (story_id) REFERENCES story (story_id) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- Table structure for story

DROP TABLE IF EXISTS story; CREATE TABLE story ( story_id int(11) NOT NULL AUTO_INCREMENT, story_town text, story_country text, story_reference varchar(255) DEFAULT NULL, PRIMARY KEY (story_id) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- Records

INSERT INTO media VALUES ('1', '1', 'test1', null, null, null, null, null, null); INSERT INTO media VALUES ('2', '1', 'test2', null, null, null, null, null, null); INSERT INTO media VALUES ('3', '2', 'test11', null, null, null, null, null, null); INSERT INTO media VALUES ('4', '2', 'test22', null, null, null, null, null, null); INSERT INTO media VALUES ('5', '2', 'test33', null, null, null, null, null, null); INSERT INTO story VALUES ('1', 'test', 'TEST', 'TEST'); INSERT INTO story VALUES ('2', 'test2', 'test2', 'test2');

Thx!

Pv

I confirm. ZF 1.8.1, following query:

SELECT hoardings_id, COUNT(camhashoa.hoardings_id) AS hoardings_count FROM campaigns_has_hoardings AS camhashoa LEFT JOIN hoardings AS hoa USING(hoardings_id)

            GROUP BY
                hoardings_id

It is being caused by the group function COUNT(...) - no matter if I specify one column, or all of them (*). The query simply joins two tables in a 1:N relation and counts the occurences of that "N" rows ... this is really a basic mysql stuff(!)

I have this query stored in $query string. When I execute this query with

$this->fetchRow($select);

then the error occurs. When I use

$this->getAdapter()->fetchAll($select, array(), Zend_Db::FETCH_OBJ);

everything runs fine. The query is also valid in every other usage environment, except zend framework. I tried to use the getAdapter->fetchAll hack, but then ran into trouble since I've been using the Zend_Paginator, which obviously doesn't except this bug here and fails.

Please, fix it ... I'm here to provide additional info.

I have the same error with ZF 1.8.4

My SQL query work really great when I run it in phpMyAdmin, but it's impossible within the ZF without the hack of Jan Juricek (getAdapter->fetchAll ...).

Any update about this bug?

Can you please supply a reproduction case similar to this: http://ralphschindler.com/2010/02/…

Attempt to keep the sql schema as small as possible to demonstrate the issue.

I have the same problem with this code: {quote}<?php $return = $db->update('#form_save', array('time' => new Zend_Db_Expr('NOW()'),'data' => $data), $db->quoteInto('form_id = ? AND form_url = ? AND user_id = ?', array($formId, $formUrl, $userId))); ?>{quote} With Zf 1.11.7.

Any update about this bug?