MySQL updates field to function result

Question

mysql> CREATE FUNCTION test () -> RETURNS CHAR(16) -> NOT DETERMINISTIC -> BEGIN -> RETURN 'IWantThisText'; -> END$$ Query OK, 0 rows affected (0.00 sec) mysql> SELECT test(); +------------------+ | test() | +------------------+ | IWantThisText | +------------------+ 1 row in set (0.00 sec) mysql> UPDATE `table` -> SET field = test() -> WHERE id = 1 Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------+ | Warning | 1265 | Data truncated for column 'test' at row 1 | +---------+------+----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT field FROM table WHERE id = 1; +------------------+ | field | +------------------+ | NULL | +------------------+ 1 row in set (0.00 sec) 

What am I doing wrong? I just want field be set to return value test() Recall that field is VARCHR (255)

Answer 1

(Not a real answer, but too long for comment)
Can you try this standalone example?

 use test; CREATE TEMPORARY TABLE soFoo (id int auto_increment, field varchar(255), primary key(id)); delimiter $$ CREATE FUNCTION soTest () RETURNS CHAR(16) NOT DETERMINISTIC BEGIN RETURN 'IWantThisText'; END$$ delimiter ; INSERT INTO soFoo (field) VALUES ('abc'),(NULL); UPDATE `soFoo` SET field = soTest() WHERE id = 1; UPDATE `soFoo` SET field = soTest() WHERE id = 2; SELECT * FROM soFoo; 

works fine on my machine:

 C:\web\xampp\mysql\bin>mysql -u volker -p Enter password: *************** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.8 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Database changed mysql> CREATE TEMPORARY TABLE soFoo (id int auto_increment, field varchar(255), primary key(id)); Query OK, 0 rows affected (0.00 sec) mysql> delimiter $$ mysql> CREATE FUNCTION soTest () -> RETURNS CHAR(16) -> NOT DETERMINISTIC -> BEGIN -> RETURN 'IWantThisText'; -> END$$ Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> INSERT INTO soFoo (field) VALUES ('abc'),(NULL); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> UPDATE `soFoo` SET field = soTest() WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE `soFoo` SET field = soTest() WHERE id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM soFoo; +----+---------------+ | id | field | +----+---------------+ | 1 | IWantThisText | | 2 | IWantThisText | +----+---------------+ 2 rows in set (0.00 sec) mysql> 

And what version of server are you using?

Answer 2

I'm not sure why, but I did r & d and got a great result with the following function code:

 mysql> CREATE FUNCTION test () -> RETURNS CHAR(16) -> NOT DETERMINISTIC -> RETURN 'IWantThisText'; 

The other code of you is good.