Database migration fails #89

darkpixel · 2014-12-23T21:22:21Z

Changed the database.php file to have the following entry:

    'default' => 'sqlite',

    'connections' => [
        'sqlite' => [
            'driver'   => 'sqlite',
            'database' => '/var/hosting/-redacted-/sfdstatus.db',
            'prefix'   => '',
        ],
    ],

Create a new sqlite3 database (sqlite3 /var/hosting/-redacted-/sfdstatus.db "") and then ran:

# php composer.phar install
Loading composer repositories with package information
Installing dependencies (including require-dev) from lock file
Nothing to install or update
Generating autoload files
Generating optimized class loader
Migration table created successfully.



  [Illuminate\Database\QueryException]                                                                                                                              
  SQLSTATE[HY000]: General error: 1 near "CHANGE": syntax error (SQL: ALTER TABLE `incidents` CHANGE `component` `component_id` TINYINT(4)  NOT NULL  DEFAULT '1')  






  [PDOException]                                                 
  SQLSTATE[HY000]: General error: 1 near "CHANGE": syntax error  



migrate [--bench[="..."]] [--database[="..."]] [--force] [--path[="..."]] [--package[="..."]] [--pretend] [--seed]


Script php artisan migrate handling the post-install-cmd event returned with an error



  [RuntimeException]                                                                                                                                                    
  Error Output:                                                                                                                                                         

    [Illuminate\Database\QueryException]                                                                                                                                
    SQLSTATE[HY000]: General error: 1 near "CHANGE": syntax error (SQL: ALTER TABLE `incidents` CHANGE `component` `component_id` TINYINT(4)  NOT NULL  DEFAULT '1')    


    [PDOException]                                                                                                                                                      
    SQLSTATE[HY000]: General error: 1 near "CHANGE": syntax error                                                                                                       

  migrate [--bench[="..."]] [--database[="..."]] [--force] [--path[="..."]] [--package[="..."]] [--pretend] [--seed]                                                    



install [--prefer-source] [--prefer-dist] [--dry-run] [--dev] [--no-dev] [--no-plugins] [--no-custom-installers] [--no-scripts] [--no-progress] [-v|vv|vvv|--verbose] [-o|--optimize-autoloader] [--ignore-platform-reqs] [packages1] ... [packagesN]

#

The text was updated successfully, but these errors were encountered:

jbrooksuk · 2014-12-23T21:29:37Z

Looks like SQLite doesn't like the ALTER TABLE syntax that was generated from MySQL.

@GrahamCampbell is there a way to change the syntax per-DB driver?

GrahamCampbell · 2014-12-23T21:32:36Z

@jbrooksuk Actually, there's an easier way. Installing doctrine dbal should fix this. Laravel uses it to fill out missing sqlite functionality.

jbrooksuk · 2014-12-23T21:33:38Z

@GrahamCampbell Oh really? Is it easy to get integrated within a migration, or does it just work once installed?

GrahamCampbell · 2014-12-23T21:35:49Z

It should just work. This is an odd case. Usually laravel will complain that doctrine isn't available.

GrahamCampbell · 2014-12-23T21:36:50Z

Oh yes, you can't use raw statements like that in migrations. That will never work.

GrahamCampbell · 2014-12-23T21:37:01Z

https://github.com/cachethq/Cachet/blob/master/app/database/migrations/2014_12_01_121947_AlterTableIncidentsRenameComponentColumn.php - bad idea.

jbrooksuk · 2014-12-23T21:37:18Z

Ok, how do you advise we write migrations? Is there a better way?

darkpixel · 2014-12-23T21:37:19Z

Also tested this against a postgresql database thinking maybe it was a DB specific issue:

# php composer.phar install
Loading composer repositories with package information
Installing dependencies (including require-dev) from lock file
Nothing to install or update
Generating autoload files
Generating optimized class loader
Migration table created successfully.



  [Illuminate\Database\QueryException]                                                                                      
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"                                                      
  LINE 1: ALTER TABLE `incidents` CHANGE `component` `component_id` TI...                                                   
                      ^ (SQL: ALTER TABLE `incidents` CHANGE `component` `component_id` TINYINT(4)  NOT NULL  DEFAULT '1')  






  [PDOException]                                                           
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"     
  LINE 1: ALTER TABLE `incidents` CHANGE `component` `component_id` TI...  
                      ^                                                    



migrate [--bench[="..."]] [--database[="..."]] [--force] [--path[="..."]] [--package[="..."]] [--pretend] [--seed]


Script php artisan migrate handling the post-install-cmd event returned with an error



  [RuntimeException]                                                                                                            
  Error Output:                                                                                                                 

    [Illuminate\Database\QueryException]                                                                                        
    SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"                                                        
    LINE 1: ALTER TABLE `incidents` CHANGE `component` `component_id` TI...                                                     
                        ^ (SQL: ALTER TABLE `incidents` CHANGE `component` `component_id` TINYINT(4)  NOT NULL  DEFAULT '1')    


    [PDOException]                                                                                                              
    SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"                                                        
    LINE 1: ALTER TABLE `incidents` CHANGE `component` `component_id` TI...                                                     
                        ^                                                                                                       

  migrate [--bench[="..."]] [--database[="..."]] [--force] [--path[="..."]] [--package[="..."]] [--pretend] [--seed]            



install [--prefer-source] [--prefer-dist] [--dry-run] [--dev] [--no-dev] [--no-plugins] [--no-custom-installers] [--no-scripts] [--no-progress] [-v|vv|vvv|--verbose] [-o|--optimize-autoloader] [--ignore-platform-reqs] [packages1] ... [packagesN]

#

I'm out of my element with the new PHP packing systems, but I tried:
php composer.phar require doctrine/dbal followed by php composer.phar install and I'm hitting the same errors.

GrahamCampbell · 2014-12-23T21:38:01Z

Is there a better way?

Yes - use laravel's actual functions and classes instead of writing raw code that only works on mysql.

GrahamCampbell · 2014-12-23T21:38:17Z

Sorry - I haven't got time to look into this today for you.

jbrooksuk · 2014-12-23T21:39:14Z

Ok, I didn't know there was inbuilt functions for things like this, cool! :)

darkpixel · 2014-12-23T21:41:12Z

...just tested with MySQL--works perfectly.

jbrooksuk · 2014-12-23T21:44:43Z

Rewriting them now :)

jbrooksuk · 2014-12-23T21:46:02Z

I'm not sure about moving the columns, so I'll drop these migrations as they don't matter really.

jbrooksuk · 2014-12-23T21:49:06Z

@GrahamCampbell I'm not sure how you write a migration (using sans-DB::statement) to change the default value?

darkpixel · 2014-12-24T00:04:45Z

Just pulled down the patched version and tried against postgres:

# php composer.phar install
Loading composer repositories with package information
Installing dependencies (including require-dev) from lock file
Nothing to install or update
Generating autoload files
Generating optimized class loader



  [Illuminate\Database\QueryException]                                                                                          
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"                                                          
  LINE 1: ALTER TABLE `incidents` CHANGE `component_id` `component_id`...                                                       
                      ^ (SQL: ALTER TABLE `incidents` CHANGE `component_id` `component_id` TINYINT(4)  NOT NULL  DEFAULT '0';)  






  [PDOException]                                                           
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"     
  LINE 1: ALTER TABLE `incidents` CHANGE `component_id` `component_id`...  
                      ^                                                    



migrate [--bench[="..."]] [--database[="..."]] [--force] [--path[="..."]] [--package[="..."]] [--pretend] [--seed]


Script php artisan migrate handling the post-install-cmd event returned with an error



  [RuntimeException]                                                                                                                
  Error Output:                                                                                                                     

    [Illuminate\Database\QueryException]                                                                                            
    SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"                                                            
    LINE 1: ALTER TABLE `incidents` CHANGE `component_id` `component_id`...                                                         
                        ^ (SQL: ALTER TABLE `incidents` CHANGE `component_id` `component_id` TINYINT(4)  NOT NULL  DEFAULT '0';)    


    [PDOException]                                                                                                                  
    SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"                                                            
    LINE 1: ALTER TABLE `incidents` CHANGE `component_id` `component_id`...                                                         
                        ^                                                                                                           

  migrate [--bench[="..."]] [--database[="..."]] [--force] [--path[="..."]] [--package[="..."]] [--pretend] [--seed]                



install [--prefer-source] [--prefer-dist] [--dry-run] [--dev] [--no-dev] [--no-plugins] [--no-custom-installers] [--no-scripts] [--no-progress] [-v|vv|vvv|--verbose] [-o|--optimize-autoloader] [--ignore-platform-reqs] [packages1] ... [packagesN]

#

jbrooksuk · 2014-12-24T08:31:30Z

@darkpixel looks like the migration doesn't like back ticks in PgSql?

darkpixel · 2014-12-24T18:08:42Z

Yeah--backticks are non-standard. MySQL doesn't require them, PostgreSQL doesn't support them.

jbrooksuk · 2014-12-24T19:42:20Z

Would you mind making a PR and removing the back ticks?

Sent from my iPhone

On 24 Dec 2014, at 18:08, Aaron C. de Bruyn notifications@github.com wrote:

Yeah--backticks are non-standard. MySQL doesn't require them, PostgreSQL doesn't support them.

—
Reply to this email directly or view it on GitHub.

darkpixel · 2014-12-24T20:58:26Z

I'll give it a shot.

darkpixel · 2014-12-24T21:12:23Z

It looks like the Laraval schema editor doesn't allow changing column types (http://stackoverflow.com/questions/22060398/how-do-i-change-a-column-type-with-laravel-schema-builder) and you have to manually write an SQL alter statement for your databases.

I have PostgreSQL and MySQL available on my test server. I'll give it a shot over the next few days.

darkpixel · 2014-12-24T22:06:29Z

Maybe someone can shed some light on the migration process. It's not working like I think it should.

I create a clean, new Postgres DB with nothing in it. I run php artisian migrate, and it crashes on the raw SQL statement here: https://github.com/cachethq/Cachet/blob/master/app/database/migrations/2014_12_13_121410_AlterTableIncidentsRemoveDefaultComponent.php#L17

...as expected. But when I look at the DB, I don't even see an 'incidents' table. The migrations table shows everything applied up to but excluding 2014_12_13_121410_AlterTableIncidentsRemoveDefaultComponent.php.

Manually reviewing the migration files, I can see the incidents table being created in 2014_11_16_224719_CreateIncidentsTable.php which shows up in the migrations table in the database. None of the other migration files appear to drop the incidents table...

Am I missing some PHP magic?

darkpixel · 2014-12-24T22:29:59Z

Disregard. Found it.

https://github.com/cachethq/Cachet/blob/master/app/database/migrations/2014_12_01_121947_AlterTableIncidentsRenameComponentColumn.php#L17

Line 17 and 29 should be renameColumn instead of rename.

Feature/gh 89 remove backticks

jbrooksuk added the Bug Bugs with Cachet label Dec 23, 2014

jbrooksuk closed this as completed Dec 23, 2014

jbrooksuk reopened this Dec 23, 2014

jbrooksuk mentioned this issue Dec 23, 2014

Fix migrations #90

Merged

jbrooksuk closed this as completed in #90 Dec 23, 2014

jbrooksuk reopened this Dec 24, 2014

jbrooksuk closed this as completed in 593cb91 Dec 25, 2014

jbrooksuk added a commit that referenced this issue Dec 25, 2014

Merge pull request #92 from darkpixel/feature/GH-89-remove-backticks

b67c4e8

Feature/gh 89 remove backticks

GrahamCampbell modified the milestone: V0.1.0 Alpha Jul 25, 2015

TheoBearman mentioned this issue Feb 1, 2016

500 Error When Trying to Edit Component and Viewing Theme, Stylesheet,Localisation and 'Create an Incident Template' Pages #1432

Closed

biovn mentioned this issue Nov 3, 2016

error 500 when subscribe an email from control panel and from subscribe button at bottom right #2228

Closed

raffus mentioned this issue Mar 22, 2018

Team invitation failed #2953

Closed

This was referenced Jun 22, 2018

Error 500 Internal server error #3130

Closed

Error 500 on tyring to login after succesful initial setup #3136

Closed

LordCheta mentioned this issue Jul 4, 2018

Error 500 when trying to Add Subscribers #3162

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Database migration fails #89

Database migration fails #89

darkpixel commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

darkpixel commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

darkpixel commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

darkpixel commented Dec 24, 2014

jbrooksuk commented Dec 24, 2014

darkpixel commented Dec 24, 2014

jbrooksuk commented Dec 24, 2014

darkpixel commented Dec 24, 2014

darkpixel commented Dec 24, 2014

darkpixel commented Dec 24, 2014

darkpixel commented Dec 24, 2014

Database migration fails #89

Database migration fails #89

Comments

darkpixel commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

darkpixel commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

GrahamCampbell commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

darkpixel commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

jbrooksuk commented Dec 23, 2014

darkpixel commented Dec 24, 2014

jbrooksuk commented Dec 24, 2014

darkpixel commented Dec 24, 2014

jbrooksuk commented Dec 24, 2014

darkpixel commented Dec 24, 2014

darkpixel commented Dec 24, 2014

darkpixel commented Dec 24, 2014

darkpixel commented Dec 24, 2014