SQLite install issue

[SinoBoeckmann]

Hi,

I've a little setup here on my Mac. Apache2, SQLite3, PHP 5.5.
Everything is working fine as far as I see.

After doing the first steps with bower and composer I'm getting the following error:

#$ php artisan migrate --seed
  [Illuminate\Database\QueryException]                                                                                           
  SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (SQL: alter table "accounts" add column "work_phone" varchar not null) 

That is not a real error in my case, because to fix this I've edited the following lines:

app/database/migrations/2014_03_03_155556_add_phone_to_account.php

17: $table->string('work_phone');
18: $table->string('work_email');

to

17: $table->string('work_phone')->default('');
18: $table->string('work_email')->default('');

After this I get another SQLite Error:

#$ php artisan migrate --seed
Migrated: 2014_03_03_155556_add_phone_to_account
Migrated: 2014_03_19_201454_add_language_support
Running DatabaseSeeder
Seeded: UserTableSeeder

  [Illuminate\Database\QueryException]                                                                                           
  SQLSTATE[HY000]: General error: 1 no such table: payment_types (SQL: insert into "payment_types" ("name") values (Apply Credit))

My question is:
Could it be, that the production.sqlite file in the database directory is just to old? Do I miss some migrations files?

Thanks for any kind of helping hand. :)

[hillelcoren]

It looks like you may have missed a migration file. try running migrate:reset and then another migrate.

[SinoBoeckmann]

That doesn't seem to fit.
I've deleted all the files. Cloned a clean version of this repository.
And still got those errors:

#$ php artisan migrate --seed

  [Illuminate\Database\QueryException]                                                                                                                           
  SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (SQL: alter table "accounts" add column "work_phone" varchar not null)  

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

I also installed "doctrine/dbal:dev-master" with composer just to be sure. But this doesn't help either.

Could you send me a running and migrated version with sqlite as standard sql driver?

[hillelcoren]

I think the problem may be caused by the app being developed using MySQL without strict mode. http://www.palantir.net/blog/beware-mysql-51-my-son

If you're able to use MySQL I believe it will work, it could take some time to resolve the issue with SQLite (assuming this is the problem).

[hillelcoren]

More info on strict mode...

Field '...' doesn't have a default value error
Try running SET GLOBAL sql_mode = 'ANSI'; in the MySQL console. If that doesn't work follow this guide to disable MySQL strict mode: https://shopplugin.net/kb/mysql-strict-mode-issues/

[delijati]

I have the same problem here and its definitely an sqlite error. Sqlite is expecting default for not Null fields.

I've played a bit around by setting them by hand (created_at, updated_at, ...) so i was able to get the migration phase running, but i run into more errors in the seed phase.

I think the easiest way would it be to just add a working empty production.sqlite and add a note to the installation.

Here is a diff of what i have done:

diff --git a/app/config/database.php b/app/config/database.php
index c0e8564..f2213bb 100755
--- a/app/config/database.php
+++ b/app/config/database.php
@@ -26,7 +26,7 @@ return array(
    |
    */

-   'default' => 'mysql',
+   'default' => 'sqlite',

    /*
    |--------------------------------------------------------------------------
diff --git a/app/database/migrations/2014_03_03_155556_add_phone_to_account.php b/app/database/migrations/2014_03_03_155556_add_phone_to_account.php
index 1de12fb..f7a7aee 100644
--- a/app/database/migrations/2014_03_03_155556_add_phone_to_account.php
+++ b/app/database/migrations/2014_03_03_155556_add_phone_to_account.php
@@ -14,8 +14,8 @@ class AddPhoneToAccount extends Migration {
    {
        Schema::table('accounts', function($table)
        {
-           $table->string('work_phone');
-           $table->string('work_email');
+           $table->string('work_phone')->default('');;
+           $table->string('work_email')->default('');;
        });
    }

@@ -33,4 +33,4 @@ class AddPhoneToAccount extends Migration {
        });
    }

-}
\ No newline at end of file
+}
diff --git a/app/database/migrations/2014_03_20_200300_create_payment_libraries.php b/app/database/migrations/2014_03_20_200300_create_payment_libraries.php
index d4c7855..574b416 100644
--- a/app/database/migrations/2014_03_20_200300_create_payment_libraries.php
+++ b/app/database/migrations/2014_03_20_200300_create_payment_libraries.php
@@ -23,8 +23,14 @@ class CreatePaymentLibraries extends Migration {
             $t->boolean('visible')->default(true);
         }); 

-       DB::table('payment_libraries')->insert(['name' => 'Omnipay']);
-       DB::table('payment_libraries')->insert(['name' => 'PHP-Payments']);
+        DB::table('payment_libraries')->insert(['name' => 'Omnipay',
+            'created_at' => 'Sun, Feb 2, 2014 7:17 PM',
+            'updated_at' => 'Sun, Feb 2, 2014 7:17 PM']
+        );
+        DB::table('payment_libraries')->insert(['name' => 'PHP-Payments',
+            'created_at' => 'Sun, Feb 2, 2014 7:17 PM',
+            'updated_at' => 'Sun, Feb 2, 2014 7:17 PM']
+        );

        Schema::table('gateways', function($table)
        {
@@ -58,4 +64,4 @@ class CreatePaymentLibraries extends Migration {
         Schema::dropIfExists('payment_libraries');  
    }

-}
\ No newline at end of file
+}
diff --git a/app/database/migrations/2014_03_25_102200_add_sort_and_recommended_to_gateways.php b/app/database/migrations/2014_03_25_102200_add_sort_and_recommended_to_gateways.php
index 535636f..c075582 100644
--- a/app/database/migrations/2014_03_25_102200_add_sort_and_recommended_to_gateways.php
+++ b/app/database/migrations/2014_03_25_102200_add_sort_and_recommended_to_gateways.php
@@ -14,9 +14,13 @@ class AddSortAndRecommendedToGateways extends Migration {
    { 
        Schema::table('gateways', function($table)
        {
-           $table->unsignedInteger('sort_order')->default(10000);
-           $table->boolean('recommended');
-           $table->string('site_url', 200);
+            if (!Schema::hasColumn('gateways', 'sort_order'))
+            {
+               //$table->dropColumn('sort_order');
+                $table->unsignedInteger('sort_order')->default(10000);
+            }
+           $table->boolean('recommended')->default(0);
+           $table->string('site_url', 200)->default('');
        });
    }

diff --git a/app/database/migrations/2014_04_03_191105_add_pro_plan.php b/app/database/migrations/2014_04_03_191105_add_pro_plan.php
index 05f74eb..8506822 100644
--- a/app/database/migrations/2014_04_03_191105_add_pro_plan.php
+++ b/app/database/migrations/2014_04_03_191105_add_pro_plan.php
@@ -14,7 +14,7 @@ class AddProPlan extends Migration {
    {
        Schema::table('accounts', function($table)
        {
-           $table->date('pro_plan_paid');
+           $table->date('pro_plan_paid')->default('Sun, Feb 2, 2014 7:17 PM');
        });     
    }

diff --git a/app/database/migrations/2014_04_17_145108_add_custom_fields.php b/app/database/migrations/2014_04_17_145108_add_custom_fields.php
index ac88c40..6177aef 100644
--- a/app/database/migrations/2014_04_17_145108_add_custom_fields.php
+++ b/app/database/migrations/2014_04_17_145108_add_custom_fields.php
@@ -14,20 +14,20 @@ class AddCustomFields extends Migration {
    {
        Schema::table('accounts', function($table)
        {
-           $table->string('custom_label1');
-           $table->string('custom_value1');
+           $table->string('custom_label1')->default('');
+           $table->string('custom_value1')->default('');

-           $table->string('custom_label2');
-           $table->string('custom_value2');
+           $table->string('custom_label2')->default('');
+           $table->string('custom_value2')->default('');

-           $table->string('custom_client_label1');         
-           $table->string('custom_client_label2');
+           $table->string('custom_client_label1')->default('');            
+           $table->string('custom_client_label2')->default('');
        }); 

        Schema::table('clients', function($table)
        {
-           $table->string('custom_value1');
-           $table->string('custom_value2');
+           $table->string('custom_value1')->default('');
+           $table->string('custom_value2')->default('');
        });
    }

diff --git a/app/database/migrations/2014_04_29_174315_add_advanced_settings.php b/app/database/migrations/2014_04_29_174315_add_advanced_settings.php
index 589bc6a..2e80a1e 100644
--- a/app/database/migrations/2014_04_29_174315_add_advanced_settings.php
+++ b/app/database/migrations/2014_04_29_174315_add_advanced_settings.php
@@ -14,8 +14,8 @@ class AddAdvancedSettings extends Migration {
    {
        Schema::table('accounts', function($table)
        {
-           $table->string('primary_color');
-           $table->string('secondary_color');
+           $table->string('primary_color')->default('');
+           $table->string('secondary_color')->default('');
        });

        Schema::table('payments', function($table)
diff --git a/app/database/migrations/2014_05_17_175626_add_quotes.php b/app/database/migrations/2014_05_17_175626_add_quotes.php
index 5079117..6299ad1 100644
--- a/app/database/migrations/2014_05_17_175626_add_quotes.php
+++ b/app/database/migrations/2014_05_17_175626_add_quotes.php
@@ -14,7 +14,7 @@ class AddQuotes extends Migration {
    {
        Schema::table('invoices', function($table)
        {
-           $table->boolean('is_quote');            
+           $table->boolean('is_quote')->default(0);            
            $table->unsignedInteger('quote_id')->nullable();
            $table->unsignedInteger('quote_invoice_id')->nullable();            
        });
diff --git a/app/database/migrations/2014_07_17_205900_support_hiding_quantity.php b/app/database/migrations/2014_07_17_205900_support_hiding_quantity.php
index d055e4e..0f97ef6 100644
--- a/app/database/migrations/2014_07_17_205900_support_hiding_quantity.php
+++ b/app/database/migrations/2014_07_17_205900_support_hiding_quantity.php
@@ -14,23 +14,23 @@ class SupportHidingQuantity extends Migration {
    {
        Schema::table('accounts', function($table)
        {
-           $table->boolean('hide_quantity');
-           $table->boolean('hide_paid_to_date');
+           $table->boolean('hide_quantity')->default(0);
+           $table->boolean('hide_paid_to_date')->default(0);

-           $table->string('custom_invoice_label1');
-           $table->string('custom_invoice_label2');            
+           $table->string('custom_invoice_label1')->default('');
+           $table->string('custom_invoice_label2')->default('');           

-           $table->boolean('custom_invoice_taxes1');
-           $table->boolean('custom_invoice_taxes2');
+           $table->boolean('custom_invoice_taxes1')->default(0);
+           $table->boolean('custom_invoice_taxes2')->default(0);
        });

        Schema::table('invoices', function($table)
        {
-           $table->decimal('custom_value1', 13, 2);
-           $table->decimal('custom_value2', 13, 2);
+           $table->decimal('custom_value1', 13, 2)->default(0);
+           $table->decimal('custom_value2', 13, 2)->default(0);

-           $table->boolean('custom_taxes1');
-           $table->boolean('custom_taxes2');           
+           $table->boolean('custom_taxes1')->default(0);
+           $table->boolean('custom_taxes2')->default(0);           
        });     
    }

diff --git a/app/database/production.sqlite b/app/database/production.sqlite
old mode 100755
new mode 100644
index 4380c89..04c9aed
Binary files a/app/database/production.sqlite and b/app/database/production.sqlite differ

[hillelcoren]

This should now be fixed.

[andrenam]

There are still (or new) issues with SQLite in the latest master branch:

$php artisan migrate --seed

  [Illuminate\Database\QueryException]                                                                      
  SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (SQL: alter table  
   "invoices" add column "custom_value1" float not null)

After changing the following things:

diff --git a/app/database/migrations/2014_07_17_205900_support_hiding_quantity.php b/app/database/migrations/2014_07_17_205900_support_hiding_quantity.php
index 5a8ed68..c7104ef 100644
--- a/app/database/migrations/2014_07_17_205900_support_hiding_quantity.php
+++ b/app/database/migrations/2014_07_17_205900_support_hiding_quantity.php
@@ -26,11 +26,11 @@ class SupportHidingQuantity extends Migration {

                Schema::table('invoices', function($table)
                {
-                       $table->decimal('custom_value1', 13, 2);
-                       $table->decimal('custom_value2', 13, 2);
+                       $table->decimal('custom_value1', 13, 2)->default(0);
+                       $table->decimal('custom_value2', 13, 2)->default(0);

-                       $table->boolean('custom_taxes1');
-                       $table->boolean('custom_taxes2');                       
+                       $table->boolean('custom_taxes1')->default(0);
+                       $table->boolean('custom_taxes2')->default(0);                   
                });             
        }

and resetting the production.sqlite database (and rolling back all migrations using php artistan migrate:reset,
I got this message:

$ php artisan migrate --seed
Migrated: 2013_11_05_180133_confide_setup_users_table
Migrated: 2013_11_28_195703_setup_countries_table
Migrated: 2014_02_13_151500_add_cascase_drops
Migrated: 2014_02_19_151817_add_support_for_invoice_designs
Migrated: 2014_03_03_155556_add_phone_to_account
Migrated: 2014_03_19_201454_add_language_support
Migrated: 2014_03_20_200300_create_payment_libraries
Migrated: 2014_03_23_051736_enable_forcing_jspdf
Migrated: 2014_03_25_102200_add_sort_and_recommended_to_gateways
Migrated: 2014_04_03_191105_add_pro_plan
Migrated: 2014_04_17_100523_add_remember_token
Migrated: 2014_04_17_145108_add_custom_fields
Migrated: 2014_04_23_170909_add_products_settings
Migrated: 2014_04_29_174315_add_advanced_settings
Migrated: 2014_05_17_175626_add_quotes
Migrated: 2014_06_17_131940_add_accepted_credit_cards_to_account_gateways
Migrated: 2014_07_13_142654_one_click_install
Migrated: 2014_07_17_205900_support_hiding_quantity
Migrated: 2014_07_24_171214_add_zapier_support
Migrated: 2014_10_05_141856_track_last_seen_message
Migrated: 2014_10_06_195330_add_invoice_design_table
Migrated: 2014_10_13_054100_add_invoice_number_settings
Running DatabaseSeeder
Seeded: UserTableSeeder



  [Illuminate\Database\QueryException]                                                                           
  SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: gateways.created_at (SQL: ins  
  ert into "gateways" ("name", "provider") values (Authorize.Net AIM, AuthorizeNet_AIM))