MySQL auto_upgrade! failing

[Dan D]

I'm migrating an app from sqlite to mysql. auto_upgrade! works great with sqlite, but crashes with mysql saying that the table already exists.

C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/adapters/dm-do-adapter.rb:100:in `execute_non_query': Table 'inv' already exists (DataObjects::SyntaxError)

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/adapters/dm-do-adapter.rb:100:in `create_model_storage'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/adapters/dm-do-adapter.rb:98:in `each'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/adapters/dm-do-adapter.rb:98:in `create_model_storage'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-do-adapter-1.2.0/lib/dm-do-adapter/adapter.rb:276:in `with_connection'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/adapters/dm-do-adapter.rb:93:in `create_model_storage'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/adapters/dm-do-adapter.rb:57:in `upgrade_model_storage'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/auto_migration.rb:73:in `upgrade_model_storage'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/auto_migration.rb:145:in `auto_upgrade!'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/auto_migration.rb:47:in `send'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/auto_migration.rb:47:in `repository_execute'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-core-1.2.0/lib/dm-core/support/descendant_set.rb:64:in `each'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-core-1.2.0/lib/dm-core/support/subject_set.rb:210:in `each'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-core-1.2.0/lib/dm-core/support/ordered_set.rb:319:in `each'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-core-1.2.0/lib/dm-core/support/ordered_set.rb:319:in `each'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-core-1.2.0/lib/dm-core/support/subject_set.rb:210:in `each'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-core-1.2.0/lib/dm-core/support/descendant_set.rb:63:in `each'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/auto_migration.rb:46:in `repository_execute'

        from C:/Ruby/lib/ruby/gems/1.8/gems/dm-migrations-1.2.0/lib/dm-migrations/auto_migration.rb:27:in `auto_upgrade!'

        from hwinv.rb:93

*** model.rb ***

DataMapper.setup(:default, {

  :adapter  => 'mysql',

  :host     => 'localhost',

  :username => 'root',

  :password => '******',

  :database => 'itinv'

})

class Inv

include DataMapper::Resource

storage_names[:default] = 'Inv'

property :id, Serial

property :ComputerName, Text, :field => 'ComputerName'

property :UserName, Text, :field => 'UserName'

end

*** app.rb ***

require 'rubygems'

require 'dm-core'

require 'dm-migrations'

require './model/model'

DataMapper.auto_upgrade!

[kristian]

frst turn on the logger then you see what SQL is going to DB

DataMapper::Logger.new($stdout, :debug)

does the same error pop up with a different storage name ?

[Dan D]

Same error with different table name.

Output of dm.log:

Wed, 27 Mar 2013 23:02:11 GMT  ~  info  ~  Logfile created

 ~ (0.000000) SET sql_auto_is_null = 0

 ~ (0.000000) SET SESSION sql_mode = 'ANSI,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,TRADITIONAL'

 ~ (0.000000) SHOW TABLES LIKE 'Inv1'

 ~ (0.000000) SET sql_auto_is_null = 0

 ~ (0.000000) SET SESSION sql_mode = 'ANSI,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,TRADITIONAL'

 ~ (0.000000) SHOW VARIABLES LIKE 'character_set_connection'

 ~ (0.000000) SHOW VARIABLES LIKE 'collation_connection'

 ~ (0.000000) CREATE TABLE `Inv1` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ComputerName` TEXT, `UserName` TEXT, `FQUserName` TEXT, `Domain` TEXT, `Site` TEXT, `KioskVersion` TEXT, `KioskLastUpdated` TEXT, `LastCheckedInForCommands` TEXT, `LastBootTime` TEXT, `LastUpdated` TEXT, `Manufacturer` TEXT, `ProductName` TEXT, `BiosVendor` TEXT, `BiosVersion` TEXT, `BiosReleaseDate` TEXT, `ChassisType` TEXT, `SerialNumber` TEXT, `OperatingSystemInstallDate` TEXT, `OperatingSystem` TEXT, `OperatingSystemBuild` TEXT, `OperatingSystemExtra` TEXT, `OSArchitecture` TEXT, `VideoCard` TEXT, `Monitor` TEXT, `TotalDiskSpace` FLOAT, `FreeDiskSpace` FLOAT, `FileSystem` TEXT, `CPUQuantity` INTEGER, `CPUVirtualQuantity` INTEGER, `CPU` TEXT, `CPUSpeed` TEXT, `CPUMaxSpeed` TEXT, `CPUArchitecture` TEXT, `Memory` FLOAT, `MemoryFree` FLOAT, `IPAddress` TEXT, `SubnetMask` TEXT, `NetworkConnType` TEXT, `DefaultIPGateway` TEXT, `MACAddress` TEXT, `SSID` TEXT, `DHCPServer` TEXT, `DNSServer` TEXT, `WINSServer` TEXT, `RebootPending` TEXT, `InternetExplorerVersion` TEXT, `TrendServer` TEXT, `TrendProgramVersion` TEXT, `TrendBuildNumber` TEXT, `TrendPatternDate` TEXT, `TrendPatternVersion` TEXT, `JavaVersions` TEXT, `JavaAutoUpdateEnabled` TEXT, `site_id` TEXT, PRIMARY KEY(`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci

 ~ Table 'inv1' already exists (code: 1050, sql state: 42S01, query: CREATE TABLE `Inv1` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ComputerName` TEXT, `UserName` TEXT, `FQUserName` TEXT, `Domain` TEXT, `Site` TEXT, `KioskVersion` TEXT, `KioskLastUpdated` TEXT, `LastCheckedInForCommands` TEXT, `LastBootTime` TEXT, `LastUpdated` TEXT, `Manufacturer` TEXT, `ProductName` TEXT, `BiosVendor` TEXT, `BiosVersion` TEXT, `BiosReleaseDate` TEXT, `ChassisType` TEXT, `SerialNumber` TEXT, `OperatingSystemInstallDate` TEXT, `OperatingSystem` TEXT, `OperatingSystemBuild` TEXT, `OperatingSystemExtra` TEXT, `OSArchitecture` TEXT, `VideoCard` TEXT, `Monitor` TEXT, `TotalDiskSpace` FLOAT, `FreeDiskSpace` FLOAT, `FileSystem` TEXT, `CPUQuantity` INTEGER, `CPUVirtualQuantity` INTEGER, `CPU` TEXT, `CPUSpeed` TEXT, `CPUMaxSpeed` TEXT, `CPUArchitecture` TEXT, `Memory` FLOAT, `MemoryFree` FLOAT, `IPAddress` TEXT, `SubnetMask` TEXT, `NetworkConnType` TEXT, `DefaultIPGateway` TEXT, `MACAddress` TEXT, `SSID` TEXT, `DHCPServer` TEXT, `DNSServer` TEXT, `WINSServer` TEXT, `RebootPending` TEXT, `InternetExplorerVersion` TEXT, `TrendServer` TEXT, `TrendProgramVersion` TEXT, `TrendBuildNumber` TEXT, `TrendPatternDate` TEXT, `TrendPatternVersion` TEXT, `JavaVersions` TEXT, `JavaAutoUpdateEnabled` TEXT, `site_id` TEXT, PRIMARY KEY(`id`)) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci, uri: mysql:root@localhostitinv?username=root&adapter=mysql&host=localhost&database=itinv&password=TEST)

The table does exist after the first time the app is ran:

mysql> show tables like 'Inv1';

+------------------------+

| Tables_in_itinv (Inv1) |

+------------------------+

| inv1                   |

+------------------------+

1 row in set (0.00 sec)

                                   

[Dan D]

I seem to have fixed it.

I noticed that there was a storage_exists? function in both dm-do-adapter.rb and dm-mysql-adapter.rb. 

Commenting out this block in dm-mysql-adapter.rb solves my issue:

21       def storage_exists?(storage_name)

22         select('SHOW TABLES LIKE ?', storage_name).first == storage_name

23       end

Tested adding a column and auto_upgrade! now creates the additional column.