Problem with newer versions of MySQL

NiChE

Hi.

I've my website running on MySQL 4.1.21-community-nt and we're planning a database upgrade to MySQL 5.x, mostly because the stored procedures and triggers features.

However, doing some tests, I found out that there seems to be a problem with MySQL 5.0.x, 5.1.x and 6.x

Basically, I'm trying to execute the following code:

$idtoken=$POST['idtoken'];
$sql="UPDATE TOKEN SET NOMBRE='".$POST['nombre']."', LEYENDA='".$POST['leyenda']."', CONTENIDO='".$POST['contenido']."', AREA=".$POST['area'].", FUENTE='".$POST['fuente']."', AUTOR=".$POST['autor'].", FECHA='".$POST['fecha']."', WIKITOKEN='".$_POST['wiki']."' WHERE IDTOKEN='$idtoken'";

The table creation script is the following:

CREATE TABLE TOKEN (
IDTOKEN INT NOT NULL,
NOMBRE TEXT NULL,
LEYENDA TEXT NULL,
CONTENIDO TEXT NULL,
AREA INT NULL,
FUENTE TEXT NULL DEFAULT NULL,
AUTOR INT NULL,
FECHA DATE NULL DEFAULT NULL,
HITS INT NULL,
WIKITOKEN TEXT NULL,
PRIMARY KEY(IDTOKEN),
INDEX TOKEN_FKIndex1(AREA),
INDEX TOKEN_FKIndex2(AUTOR)
);

I've echoed $idtoken and the variable is showing the expected value.

The thing is that it works on 4.1.x and not on 5.0.x or newer versions.

I read somewhere that starting on MySQL 5.0.x there's a revision of the SQL standard (2003 compliance), but I don't know if this has something to do with this.

Unfortunalety, if I don't solve this problem, we cannot perform our database upgrade, and since on version 6.0 won't work, there may be a long way to go.

Am I having some stupid syntax error? is there a new PHP syntax?, is there some MySQL trick?, please, help!

Thanks.

By the way, I'm using PHP 5.2.3

Roger_Ramjet

So, what is the problem? Are you getting an error, or just no data inserted?

NiChE

I'm getting an error.

Well, in fact it would be TWO errors.

One, treating $idtoken as a string, will give me "Unknown column 'p' in 'where clause'" (there's no 'p' column on any of my tables) and the second, changing the syntax treating $idtoken as a number (as it should be) will give me "Truncated incorrect DOUBLE 'value_of_$idtoken "

I don't understand what's happening.

Roger_Ramjet

Of course there is no column 'p': that is why it tells you 'p' is unknown.

But what you must have is some error with your input data.

debugging 101: echo the query string to see what it is you are actually sending to the database - the whole string not just the input vars.

NiChE

Hmmm...

I'm starting to think this may be a syntax problem.

At some point in another page, the following code executes:

$hits=$vectorResultado['HITS'];
$hits++;
$sql="UPDATE TOKEN SET HITS='$hits' WHERE IDTOKEN=".$tk;
$resultado=mysql_query($sql, $conexion) or die ("<br><p>Error ");

When I check the value of the HITS colunm on TOKEN the query gets executed normally.

However, in this case, the value for IDTOKEN gets concatenated as a string and it works, but it shouldn't, because IDTOKEN is INT type. That's why I tried out the string trick as posted before 😃

I hope you can help me figure it out.

Bye

NiChE

I've passed debugging 101!

I did the echo $sql and the string was passing fine.

It still works on 4.1, but not on 5.x or newer.

Roger_Ramjet

so paste it here