0

I have created custom table. Now I want to change datatype for a particular column in table. Column name is 'session_number' and its current type is varchar. I want to change it from varchar to int. I used this to do so :

function mymodule_update_7053(&$sandbox) {
  db_change_field('abc', 'session_number', 'session_number', array(
    'type' => 'int',
    'size' => '255',
    'not null' => TRUE,
  ));
  drupal_flush_all_caches();
}

It doesn't change existing type from varchar to int. How to do it please guide. Huge thanks in advance.

  • Has data in the field, right? – Jimmy Ko Aug 2 '16 at 10:35
  • yes ! there is data already and don't want to loss data. – Rajender Rajan Aug 2 '16 at 12:01
1

You must have some error message if the conversion is not accepted.

You first have to validate that this field is not a key or an index. See the db_change_field documentation about this.

After, I think 255 is not a valid value for the size key. The Schema API documentation tell :

'size': The data size: 'tiny', 'small', 'medium', 'normal', 'big'. This is a hint about the largest value the field will store and determines which of the database engine specific datatypes will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT). 'normal', the default, selects the base type (e.g. on MySQL, INT, VARCHAR, BLOB, etc.). Not all sizes are available for all data types. See DatabaseSchema::getFieldTypeMap() for possible combinations.

And finally, I'm not sure if the DB engine can support the VARCHAR to INT conversion. Then it means your column will be empty and cannot support the 'not null' => TRUE property.

Maybe you have to do this in more than one step.

  • You could always set a default for the column, that would solve the not null problem – Clive Aug 2 '16 at 11:52
  • i have used 'default' => 0 before but doesn't have any error. – Rajender Rajan Aug 2 '16 at 12:02
  • Is there some key or index on this column of your table ? Are the data already store in this field convertible (only digital data) ? Did you had a look at your server error log, or even in the Drupal DBLog to try to find a potential error during the update process ? And finally, is your update process called by update.php script and is it working ? You can add a watchdog() in your code. – TytooF Aug 2 '16 at 12:49
  • In database tables data is already existed. There is no error in logs. Moreover, update.php run without any breaking. I used db_query("ALTER TABLE woxgo_user_stats CHANGE session_number session_number INT( 255 ) NOT NULL COMMENT 'Session number to track how many times user is logging in.'"), and its working fine and convert datatype to int . But i want to do it with above mention way. Is it possible ? – Rajender Rajan Aug 2 '16 at 17:17
  • Can you maybe put the full schema of this table in your question ? And are you sure the update method is running ? Because normally it can run only once, when the version number of the module is not up to date. – TytooF Aug 3 '16 at 8:20
0

According to Data Types, for the column type int, size only allows following value:

  • tiny
  • small
  • medium
  • big
  • normal

If you put 256 as value, you will get below error message.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that [error] corresponds to your MySQL server version for the right syntax to use near 'NOT NULL' at line 1

So the correct one should be:

function mymodule_update_7053(&$sandbox) {
  db_change_field('abc', 'session_number', 'session_number', array(
    'type' => 'int',
    'size' => 'normal',
    'not null' => TRUE,
  ));
  drupal_flush_all_caches();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.