[RESOLVED] Cardinality violation

[Kudose]

Hello all.

So I've got this error:

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

With this SQL

SELECT `login`, `email` FROM `myusers` WHERE `id` = '9f1ac727-587f-4ae4-8008-6054566ef219' LIMIT 1

The table is simple

id
login
password
email

Why would I get that error?

I am thinking it is a Zend Framework bug because the SQL works in phpMyAdmin.

$this->query = 'SELECT `login`, `email` FROM `'.$this->_name.'` WHERE `id` = \''.$this->user->id.'\' LIMIT 1';
      try {
        $userInfo = $this->fetchRow($this->query);
        $this->user->username = $userInfo->login; 
        $this->user->email = $userInfo->email;
        return true;
      } catch(Exception $e) {
        $this->setAdminError($e->getMessage().'<br />'.$this->query, __METHOD__, __LINE__, __FILE__);
        $this->publicMsg = $this->translations->translate('error500');
        return false;
      } 

TIA!

[jkurrle]

What happens if you drop "limit 1" from the sql query. Just curious...

[jkurrle]

is "id" a keyword? I don't remember. If it is, change the select statement to

SELECT `login`, `email` FROM `myusers` WHERE `myusers.id` = '9f1ac727-587f-4ae4-8008-6054566ef219' LIMIT 1

[Kudose]

Thanks for the suggestions, but id is not a reserved word and encapsulating the string in backticks tells MySQL to use the string as a column instead of a keyword.

[NogDog]

I can't see from the query provided how it could result in that error. Usually it occurs when there is a mismatch resulting from incorrect use of parentheses and usually in conjunction with sub-queries, which obviously does not seem to apply here.

I'm not familiar with the Zend framework, but should there be some method call to execute the query prior to the fetchRow() call?

[jkurrle]

I think cardinality has to do with your index:

Cardinality
The number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so it's not necessarily accurate for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

[jkurrle]

SyBase has a PDF that discusses your error in section 4-2.

[Kudose]

*sigh*

I'm back, but this time I have a whole new query!

SELECT `g`.*, `u2g`.* FROM `users2groups` AS `u2g` RIGHT JOIN `groups` AS `g` ON `g`.`groupid` = `u2g`.`uggid` WHERE `u2g`.`uguid` = '5bc40bc6-7974-102b-8a2c-d23ebefec1c9'

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

From the SyBase PDF linked above:

Cardinality violations occur when a query that should return only a
single row returns more than one row to an Embedded SQL™
application.

That makes sense, but it doesn't apply to my situation, from what I can tell.

Can anyone see what I am doing wrong? The SQL executes in phpMyAdmin (and returns only one row), just not through the Zend framework. It shouldn't matter that it is going through ZF though, since it is returning a valid SQL error ZF is doing it's job.

## edit
I found this on page 159, section 5-37 under lock manager messages, code 1241

A semaphore cursor refers to a different semaphore than the one it is linked to.

In English, I think that means:
The column you are accessing refers to a column other than the one you want.

... how would I resolve that?

[Kudose]

Ok, this has to be a ZF bug.

mysql_connect('localhost', 'root', '');
      mysql_select_db('zfapp');
      $q = mysql_query($sql);
      print_r(mysql_fetch_assoc($q));

Produces valid results.

Where $sql equals the SQL in the previous post.

## edit
Reported

[topbit]

Hello all.

... Operand should contain 1 column(s)

With this SQL

SELECT `login`, `email` FROM `myusers` WHERE `id` = '9f1ac727-587f-4ae4-8008-6054566ef219' LIMIT 1

I am thinking it is a Zend Framework bug because the SQL works in phpMyAdmin.

$this->query = 'SELECT `login`, `email` FROM `'.$this->_name.'` WHERE `id` = \''.$this->user->id.'\' LIMIT 1';
      try {
        $userInfo = $this->fetchRow($this->query);
 ....
      } catch(Exception $e) {
      } 

I've just come across this problem - the key to it was in the manual:

$stmt = $db->query('SELECT * FROM bugs');
$rows = $stmt->fetchAll(); 

Run the SQL through query(), and then fetch the rows you want.

[bpat1434]

Kudose, I don't think it's a bug but rather you're missing the actual query to the database.

You have to execute $db->query($this->query) otherwise you're not querying the database. At least that's from my limited knowledge of ZF database stuff.

[Kudose]

You guys are correct.

I was able to get it figured out by creating a Zend_Db_Table_Select object and passing it to Zend_Db_Table

[asitk]

Hi,

I also ran through the same error message while trying to run the "SELECT * FROM <table_name>" query. After a lot search on the net I resolved the error. The query was:

$select = $this->select()->distinct(true);

and I tried to run this built this way:

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

The part I was missing was "getAdapter()" method. I added that:

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

That fixed the problem. Hope this help for the guys facing the same kind of problem.

Thanks
Asit