Unknown Column " In 'where Clause' (v5.1 Beta 1)

[Versatile]

Hi, i've found a bug regarding deleting rows through an HTTP tunneler.

The message is as discribed in the subject.

I've connected to an host, selected a database, selected an table, clicked “3 table data”, selected rows i wanted to delete ..and pressed the icon to delete selected rows .. and gives me the error.

[peterlaursen]

Does any of the the column-names in the table contain special (non-english) characters?

Could you please paste in (could be a sreen dump) of the exact error message!

It it exactly like Unknown Column '' … (empty string)

read this: http://www.webyog.com/forums/index.php?showtopic=1915

I can reporduce! But right now I can't upload an image (because of migration proces to another server)

If there is no Primary Key SQLyog generates SQL like

Code:

delete from `test2`.`lem_pl` where `lem`='æøå' and `code`='ÆØÅ' and `parent`='1' and `word_id`='2'

If there is a Primary Key SQL looks like:

Code:

delete from `test2`.`lem_pl` where `lem_id`='5341205'

In the above link I sometimes the the err msg *query is empty* when INSERTING data with Danish characters. That means such string looks empty after being tunneled. That would fully explain it!

If you can create a autoincrement PK with the table that will solve the problem.

I don't exclude there the is an issue with SQLyog (encoding transformation from SQLyog ANSI environment through tunneling to a non-ANSI environment). But I think there is an issue with MySQL/and or PHP too. Try follow the link to bugs.mysql.com in the thread I linked to. I have no problems with MySQL 3.23.58 and php 4.3.2. But with MySQL >= 4.0 and php 4.4.2 and 5.1.2 I have!

[Versatile]

That's my table;

Table Create Table

---

---

Uploaded_MP_Files CREATE TABLE `Uploaded_MP_Files` (

`id` int(6) NOT NULL auto_increment,

`upload_file` varchar(100) default NULL,

`upload_orig` varchar(100) default NULL,

`upload_type` char(3) default NULL,

`upload_by` int(6) default NULL,

`upload_for` int(6) default NULL,

PRIMARY KEY (`id`)

) TYPE=MyISAM

That's the error

Also with an insert.

Text is plain english

[peterlaursen]

What does the DELETE statment look like in HISTORY pane?

[Versatile]

peterlaursen wrote on Feb 24 2006, 12:37 PM:

What does the DELETE statment look like in HISTORY pane?

[post=”8899″]<{POST_SNAPBACK}>[/post]

Doesn't display anything .. it probably breaks before it logs

[peterlaursen]

Confirmed

with 5.02/5.03 I can insert and delete these data using HTTP, with 5.1 I can't.

EDIT: WRONG! Broken with 5.0!

The DELETE is not logged to HISTRY (@ritesh: why not?)

The inset looks like:

Code:

insert into `uploaded_mp_files` (“,“,“,“,“,“) values ( '24','test.jpg','','','2','502')

researching ….

[peterlaursen]

And this table

Code:

create database if not exists `test`;
USE `test`;

/*Table structure for table `t1` */

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (
 `id` bigint(20) NOT NULL auto_increment,
 `txt` varchar(50) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

does exactly the same when delete the single row in there (using HTTP)!

[peterlaursen]

HTTP-connection insert: insert into `t1` (“,“) values ( '','a')

Direct connection: insert into `t1` (`id`,`txt`) values ( '','a')

That must have been broken with PUCLIC beta! I don't believe the non-public ones that I tested did this!

EDIT: obviously wrong. read on!

[peterlaursen]

One issue more (MySQL 5.1.6):

With HTTP this

Code:

insert into `t1` (`id`,`txt`) values ( '','c')

generates the error

Quote:

Error Code : 1264

Out of range value for column 'id' at row 1

(when executed from SQL-pane)

4.1 wrote

Quote:

insert into `test`.`t1` ( `id`, `txt` ) values (  NULL,  'd' )

.. and that works even with HTTP

I'll repeat: skip autocrement colums with the insert! 😀

Why code around problems when you simply can SKIP coding – even a coder should see the logic! But this SHOULD work with HTTP of course!

[peterlaursen]

And finally: no DELETE-issue with 4.1 and HTTP with my example or Versatile's

[Versatile]

peterlaursen wrote on Feb 24 2006, 01:41 PM:

And finally: no DELETE-issue with 4.1 and HTTP with my example or Versatile's

[post=”8905″]<{POST_SNAPBACK}>[/post]

Thanks for finding it all and wich versions it does and does not apply to.

[peterlaursen]

Please note this change:

Confirmed

with 5.02/5.03 I can insert and delete these data using HTTP, with 5.1 I can't.

EDIT: WRONG! Broken with 5.0!

All tests on MySQL 5.1.6, PHP 5.1.2.

[peterlaursen]

exact the same same with MySQL 4.1.18 and PHP 4.4.2

[peterlaursen]

and MySQL 4.0 too.

So no SET NAMES issue and no INFOMATION_SCHEMA issue either.

[peterlaursen]

VERY IMPORTANT, I believe!

++++++++++++++++++++

On the new Webyog server running MySQL 4.1 and PHP 4.3.9:

NO PROBLEMS!

All in this thread and all in 'Turkish characters' -thread works on this server.

Looks like some change took place with with PHP 4.4 and 5.1 in relation to 4.3 (don't know about 5.0). But strange to me that it can affect the SQL that SQLyog writes.

Maybe just some flag or test or function call that we need in SQLyogTunnel.

[peterlaursen]

Now … more research .. and it gets hairier!

I installed php 4.3.11 on my local.

Versatile issue does not occur!

The 'Turkish' issue doesnot either

But the 'value out of range' with a autoincrement does!

Next I tried PHP 4.3.9 .. but My Apache 2.0.55 won't start with it!

Summary:

*No issue with SQLyog 4.1 on either PHP version anywhere

*No issue with any SQLyog on Webyo server Apache 2.0.54, PHP 4.3.9

* on my local with Apache 2.0.55 and php 4.3.11 “'value out of range' with a autoincrement” but not Versatile and Turkish with SQLyog 5.0+

* on my local with Apache 2.0.55 and php 4.4.2 and 5.1.2 all three issues occur with SQLyog 5.0+.

* on my personal webhost Apache 1.3.34 and php 4.4.2 all three issues occur with SQLyog 5.0+

(I don't claim to have it all – there could be configurations, active PEAR scripts, Zend extensions etc. that all have things to say here!)

[peterlaursen]

Status:

Things here are now fully sorted out and explained. Ritesh found one issue, and I another. One is a bug in 5.1 beta, another involved setting sql_mode over a tunnelled connection. Pretty complicated as things were 'intertwined' quite a lot.

One fix is implemented in 5.1 code tree, the other probably later today!

[peterlaursen]

The problem with tunnelling to MySQL version 5 server that have not

Code:

@@sql_mode = ''

as default setting for sql_mode

can be solved by inserting into tunnelling file after

Code:

   /* Function will execute setnames in the server as it does in SQLyog client */
   SetName ( $mysql );

Code:

   /* each connect must set sql_mode */    
   $sqlmode = mysql_query(“set sql_mode = ''”, $mysql);

another fix is to replace mysql_connect() with mysql_pconnect(). _pconnect means 'persistent connection'. But not all ISP's/web hostings allow for it.

Probably Ritesh will post a more professional solution soon. It could be necessary to test for MySQL version with mysql_get_server_info() (if the statement raises error on MySQL < 5.0. But this fix and the fix that Ritesh allready implemented solves it all with all MySQL and PHP versions, I believe!

[lazylester]

peterlaursen wrote on Mar 1 2006, 03:19 AM:

can be solved by inserting into tunnelling file after

Code:

   /* Function will execute setnames in the server as it does in SQLyog client */
   SetName ( $mysql );

Code:

   /* each connect must set sql_mode */    
   $sqlmode = mysql_query(“set sql_mode = ''”, $mysql);

Hi Peter,

this fix in the tunneling script didn't solve the problem for me… I'm on mySQL 4.1.14, Linux 2.6.9, Apache 1.3.34 with the Sqlyog 5.1 Beta 1

A couple of posts ago you mentioned that there's a fix in the 5.1 code tree. Does that mean there's a new beta available to install? How can I test the fix that's in the code tree? Maybe I just have to wait for 5.1beta2? Please clarify.

many thanks

Les

[peterlaursen]

There are two issues here.

1)

One is fixed by Ritesh in BETA 2 code tree. It simply generated empty column names with INSERTs and UPDATES and HTTP connections. This issue/bug affects 5.1 BETA1 only

2)

With MySQL version 5 running in some STRICT MODE as server default, SQLyog must issue the command set sql_mode = ''. It does! Actually the code is in the Tunnelling file allready, but this function in the PHP-code is not invoked! This is a bug! I believe this is fixed allredy too, though ritesh has not confirmed this to me. But it is pretty simple to see this, once you understand the structure of the tunnelling script! It simply must call the function in the script immediately after (or before) calling the function that does the SET NAMES. This issue affects all SQLyog versions when connecting with HTTP to a MySQL version 5.x server running STRICT MODE. However this is a bug with the tunnelling script only – not the SQLyog or SJA executables – and anyone who knows how to use NOTEPAD can easily fix it herself!

Your issue is the first one! set sql_mode = '' has only effect with MySQL 5. With 4.1 sql_mode must be specified with the connection string, and can't be changed in the midst of a connection. Read about sql_modes: http://www.webyog.com/faq/28_72_en.html.

Conclusion is that you will have to wait for BETA2 to INSERT and UPDATE using HTTP-tunnel.

What does your MySQL return if you execute:

Code:

SELECT @@sql_mode;

??

[peterlaursen]

And let me add that I also believe that I found the reason for the 'Turkish' issue. I have mailed details to Ritesh. It would take to much space here to explain, and would have to be documented with a lot of examples. But Ritesh has all this now.

But basically it is an issue with the GRIDs of DATA and RESULT panes. It was pretty tricky since PHP up to 4.3 (and possibly 5.0) accepted and executed the SQL anyway. But PHP 4.4 and 5.1 don't.

I believe all significant issues with SQLyog 5 (5.1 BETA and previous) – except the slow export that I just reported – are now fully understood. It can't be long till we'll see BETA 2. I also know about significant improvements in auto-complete, so it is going to be a terrific release, I think!

[lazylester]

peterlaursen wrote on Mar 2 2006, 06:37 PM:

What does your MySQL return if you execute: 

Code:

SELECT @@sql_mode;

??

[post=”8996″]<{POST_SNAPBACK}>[/post]

Peter my server returns a single row with a single column which is blank

Les

[peterlaursen]

That was what I expected (would normally be so if not server is running in ANSI-mode).

So with you server there is no need to/idea in setting sql_mode = '' – becuase it is already. So this is not what your are missing!

You can do this to understand your issue: Simply try to insert a row using HTTP-tunnel. Yo get the error message and nothin is inserted. However the query is logged to HISTORY. Look at it and you'll se that column names are empty strings.

[Ritesh]

peterlaursen wrote on Mar 3 2006, 01:36 PM:

That was what I expected (would normally be so if not server is running in ANSI-mode).

So with you server there is no need to/idea in setting sql_mode = '' – becuase it is already.  So this is not what your are missing! 

You can do this to understand your issue: Simply try to insert a row using HTTP-tunnel.  Yo get the error message and nothin is inserted.  However the query is logged to HISTORY.  Look at it and you'll se that column names are empty strings.

[post=”9003″]<{POST_SNAPBACK}>[/post]

I don't think his problem is related to sql_mode. For some reason the Base64 encoded data sent by SQLyog is getting changed before it reaches SQLyogTunnel.php for processing.

[peterlaursen]

@ ritesh: whose problem are you now talking about?

Lacylester's problems is the empty columns names. Simply. no base 64 thing.

I just wanted to be sure that there was no issue with a rare sql_mode too.

I'll repeat:

We have four issues:

1) SQLyog beta 1 generates empty column names with HTTP-tunnelling. You toul me that was fixed in BETA2 tree.

2) SQLyougtunnel.php did not call the function doing the set_names

3) With recent PHP versions there is an problem when SQL is generated from the GRIDs.

4) The base64 issue that I shal not comment

Those four issues may occur individually or at the same same. Hence the confusion. Do you agree? Did you verify 3) on my server?

To me it seems like you are completely confused abot this! Lets have BETA 2 ASAP so we can see what is left of it!

[Ritesh]

peterlaursen wrote on Mar 4 2006, 02:54 PM:

@ ritesh: whose problem are you now talking about?

Lacylester's problems is the empty columns names. Simply. no base 64 thing.

I just wanted to be sure that there was no issue with a rare sql_mode too.

I'll repeat:

We have four issues:

1) SQLyog beta 1 generates empty column names with HTTP-tunnelling.  You toul me that was fixed in BETA2 tree.

2) SQLyougtunnel.php did not call the function doing the set_names

3) With recent PHP versions there is an problem when SQL is generated from the GRIDs.

4) The base64 issue that I shal not comment

Those four issues may occur individually or at the same same.  Hence the confusion. Do you agree?  Did you verify 3) on my server?

To me it seems like you are completely confused abot this!  Lets have BETA 2 ASAP so we can see what is left of it!

[post=”9011″]<{POST_SNAPBACK}>[/post]

Indeed lazylesters issue is regarding the Base 64 encoding. Read: http://www.webyog.com/forums/index.php?showtopic=1930. He has this issue regarding INVALID WHERE CLAUSE too. This is a bug and has already been fixed in v5.1 BETA tree.

1.) Solved

2.) Solved

3.) Working on it.

4.) Working on it.

[peterlaursen]

hmmm…

http://bugs.mysql.com/bug.php?id=17938

[peterlaursen]

@Versatile

Fixed with Beta2. Can you confirm the fix?

[Author]

Posts