SQLSTATE[23502] when DB_CONNECTION set to pgsql

[ntoenk1186]

Hi,

I currently used PostgreSQL in my project. When I try to setup Polr using PostgreSQL, I found some query error. There are :

• When insert data user admin account :

[2016-09-13 11:05:33] lumen.ERROR: exception 'PDOException' with message 'SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "role" violates not-null constraint
DETAIL:  Failing row contains (1, admin, $2y$10$Mk3yXHToPcfFefGhOOwL4ODzIa3aJo6f3OizqAdWXN.lgXSoPjU96, admin@localhost, ::1, ad1ff4e525b5545b17804fd152445db3bbe915a4eac7dc44d510e98ad9013d9b..., null, 1, 0, f, 60, 2016-09-13 11:05:33, 2016-09-13 11:05:33, null).' in /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Connection.php:321
Stack trace:
#0 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Connection.php(321): PDOStatement->execute(Array)
#1 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Connection.php(644): Illuminate\Database\Connection->Illuminate\Database\{closure}(Object(Illuminate\Database\PostgresConnection), 'insert into "us...', Array)
#2 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Connection.php(611): Illuminate\Database\Connection->runQueryCallback('insert into "us...', Array, Object(Closure))
#3 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Connection.php(324): Illuminate\Database\Connection->run('insert into "us...', Array, Object(Closure))
#4 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Connection.php(298): Illuminate\Database\Connection->select('insert into "us...', Array, false)
#5 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Query/Processors/PostgresProcessor.php(20): Illuminate\Database\Connection->selectFromWriteConnection('insert into "us...', Array)
#6 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Query/Builder.php(1827): Illuminate\Database\Query\Processors\PostgresProcessor->processInsertGetId(Object(Illuminate\Database\Query\Builder), 'insert into "us...', Array, 'id')
#7 [internal function]: Illuminate\Database\Query\Builder->insertGetId(Array, 'id')
#8 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Eloquent/Builder.php(949): call_user_func_array(Array, Array)
#9 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Eloquent/Model.php(1654): Illuminate\Database\Eloquent\Builder->__call('insertGetId', Array)
#10 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Eloquent/Model.php(1654): Illuminate\Database\Eloquent\Builder->insertGetId(Array, 'id')
#11 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Eloquent/Model.php(1623): Illuminate\Database\Eloquent\Model->insertAndSetId(Object(Illuminate\Database\Eloquent\Builder), Array)
#12 /Users/untung/EclipseProjects/polr/vendor/illuminate/database/Eloquent/Model.php(1514): Illuminate\Database\Eloquent\Model->performInsert(Object(Illuminate\Database\Eloquent\Builder), Array)
#13 /Users/untung/EclipseProjects/polr/app/Factories/UserFactory.php(24): Illuminate\Database\Eloquent\Model->save()
#14 /Users/untung/EclipseProjects/polr/app/Http/Controllers/SetupController.php(215): App\Factories\UserFactory::createUser('admin', 'admin@localhost', 'password', 1, '::1')
#15 [internal function]: App\Http\Controllers\SetupController->finishSetup(Object(Illuminate\Http\Request))
#16 /Users/untung/EclipseProjects/polr/vendor/illuminate/container/Container.php(507): call_user_func_array(Array, Array)
#17 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1399): Illuminate\Container\Container->call(Array, Array)
#18 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1364): Laravel\Lumen\Application->callControllerCallable(Array, Array)
#19 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1335): Laravel\Lumen\Application->callLumenController(Object(App\Http\Controllers\SetupController), 'finishSetup', Array)
#20 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1303): Laravel\Lumen\Application->callControllerAction(Array)
#21 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1288): Laravel\Lumen\Application->callActionOnArrayBasedRoute(Array)
#22 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1207): Laravel\Lumen\Application->handleFoundRoute(Array)
#23 [internal function]: Laravel\Lumen\Application->Laravel\Lumen\{closure}(Object(Illuminate\Http\Request))
#24 /Users/untung/EclipseProjects/polr/vendor/illuminate/pipeline/Pipeline.php(139): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#25 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Http/Middleware/VerifyCsrfToken.php(43): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#26 [internal function]: Laravel\Lumen\Http\Middleware\VerifyCsrfToken->handle(Object(Illuminate\Http\Request), Object(Closure))
#27 /Users/untung/EclipseProjects/polr/vendor/illuminate/pipeline/Pipeline.php(124): call_user_func_array(Array, Array)
#28 /Users/untung/EclipseProjects/polr/vendor/illuminate/session/Middleware/StartSession.php(62): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#29 [internal function]: Illuminate\Session\Middleware\StartSession->handle(Object(Illuminate\Http\Request), Object(Closure))
#30 /Users/untung/EclipseProjects/polr/vendor/illuminate/pipeline/Pipeline.php(124): call_user_func_array(Array, Array)
#31 /Users/untung/EclipseProjects/polr/vendor/illuminate/cookie/Middleware/EncryptCookies.php(59): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#32 [internal function]: Illuminate\Cookie\Middleware\EncryptCookies->handle(Object(Illuminate\Http\Request), Object(Closure))
#33 /Users/untung/EclipseProjects/polr/vendor/illuminate/pipeline/Pipeline.php(124): call_user_func_array(Array, Array)
#34 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))
#35 /Users/untung/EclipseProjects/polr/vendor/illuminate/pipeline/Pipeline.php(103): call_user_func(Object(Closure), Object(Illuminate\Http\Request))
#36 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1439): Illuminate\Pipeline\Pipeline->then(Object(Closure))
#37 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1213): Laravel\Lumen\Application->sendThroughPipeline(Array, Object(Closure))
#38 /Users/untung/EclipseProjects/polr/vendor/laravel/lumen-framework/src/Application.php(1153): Laravel\Lumen\Application->dispatch(NULL)
#39 /Users/untung/EclipseProjects/polr/public/index.php(28): Laravel\Lumen\Application->run()
#40 {main}

• When creating short url :

SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "creator" violates not-null constraint
DETAIL:  Failing row contains (1, jQ3WY, http://www.facebook.com, ::1, null, 0, null, f, f, f, 2016-09-13 11:20:48, 2016-09-13 11:20:48). (SQL: insert into "links" ("short_url", "long_url", "ip", "is_custom", "is_api", "updated_at", "created_at") values (jQ3WY, http://www.facebook.com, ::1, 0, 0, 2016-09-13 11:20:48, 2016-09-13 11:20:48) returning "id")

I've created pull request #223 to solve the error above and also I've put some code to support user DB_CONNECTION between MySQL (default) and PostgreSQL.

regards.

[cydrobolt]

thanks, sounds good. will dig into why pgsql errors out for NOT NULL whereas mysql seems to be alright

[marco44]

Hi, I'm interested in installing polr over PostgreSQL too. I'm a DBA and not a developper though. Maybe I can help on what I'm good at :)

MySQL usual "fixes" data without returning errors. I don't know what is generated as a schema in both databases, but MySQL accepts inserting null into a not-null column… many of these are documented here http://sql-info.de/mysql/gotchas.html#1_1 . Most other SQL databases (Oracle, PostgreSQL, DB2, SQL Server) would reject those insertions, as they violate the not null constraint.