fuel/core — Issue #586: \DBUtil::modify_fields() doesn't work as expected
Author: crynobone
Created: 2011-10-18 12:00:38+00:00
State: open

Given the example from and I were to run/use the following code:

array('constraint' => 11, 'type' => 'int', 'auto_increment' => true),
'name' => array('type' => 'text'),
'email' => array('constraint' => 50, 'type' => 'varchar'),
'title' => array('constraint' => 50, 'type' => 'varchar', 'default' => 'mr.'),
'password' => array('constraint' => 125, 'type' => 'varchar'),
), array('id'), false, 'InnoDB', 'utf8_unicode_ci');

\DBUtil::modify_fields('users', array(
'name' => array('constraint' => 100, 'type' => 'varchar', 'charset' => 'utf8_general_ci'),
'title' => array('constraint' => 50, 'type' => 'varchar', 'default' => 'mrs.'),
));
}
}

The second command wouldn't work and will throw \Database_Exception error:

> Fuel\Core\Database_Exception [ Error ]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `title` varch' at line 2 with query: "ALTER TABLE `users` CHANGE `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `title` varchar(50) DEFAULT 'mrs.' NOT NULL"

This is due to invalid use of ALTER TABLE ... CHANGE where in this case we are actually trying to ALTER TABLE ... MODIFY. Secondly MODIFY need to be defined for each column change.

So based on above example:

ALTER TABLE `users`
CHANGE `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`title` varchar(50) DEFAULT 'mrs.' NOT NULL

Need to be changed to:

ALTER TABLE `users`
MODIFY `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
MODIFY `title` varchar(50) DEFAULT 'mrs.' NOT NULL