Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error 500 - Pathfinder Database issues #701

Closed
anesbit opened this issue Oct 9, 2018 · 15 comments
Closed

Error 500 - Pathfinder Database issues #701

anesbit opened this issue Oct 9, 2018 · 15 comments
Assignees
@exodus4d
Labels
major bug
Milestone
v1.4.3

Comments

@anesbit
Copy link

anesbit commented Oct 9, 2018

I am trying to get the pathfinder database installed when I get...

https://prnt.sc/l3wgqp

Error - 500
'Column 'description' of type TEXT can't have a default value.'

Database Type:
MariaDB v.10.2

Any help fixing this would be awesome!

Thanks
DJDeath
@anesbit
Copy link
Author

anesbit commented Oct 9, 2018

The only two tables that I am missing that have a 'description' column is the system Table and the system_signature Table.... just a little digging I did backend...

@WildStrawberryEVE
Copy link

Does that:
ALTER TABLE system MODIFY COLUMN description text NOT NULL DEFAULT '';
fix your problem?
' ' without spaces (an empty string) :)

@anesbit
Copy link
Author

anesbit commented Oct 9, 2018

@WildStrawberryEVE where would I find that line?!? I cant find the file that contains that...

@WildStrawberryEVE
Copy link

@anesbit it's a mysql command. You would have to log in to your pathfinder database with mysql client and to issue that command

@anesbit
Copy link
Author

anesbit commented Oct 9, 2018 •
edited
Loading

I get this error on database creation... during the setup tables in the /setup directory of my server

@anesbit
Copy link
Author

anesbit commented Oct 9, 2018

@WildStrawberryEVE

According to MySQL the tables were never created...

Database changed
MariaDB [pathfinder]> show tables;
+--------------------------+
| Tables_in_pathfinder |
+--------------------------+
| alliance |
| alliance_map |
| character |
| character_authentication |
| character_log |
| character_map |
| character_status |
| connection_scope |
| corporation |
| corporation_map |
| corporation_right |
| corporation_structure |
| map |
| map_scope |
| map_type |
| right |
| role |
| sessions |
| structure |
| structure_status |
| system_neighbour |
| system_status |
| system_type |
| user |
| user_character |
+--------------------------+
25 rows in set (0.00 sec)

@anesbit
Copy link
Author

anesbit commented Oct 9, 2018

I need to figure out where the creation SQL is stored so i can edit it... but I don't know where to find it

@hagbartx
Copy link

same error for since update from 1.4.0 to 1.4.2

General error: 1364 Field 'description' doesn't have a default value [/var/www/pathfinder/app/lib/db/sql.php:226]

@anesbit
Copy link
Author

anesbit commented Oct 11, 2018

@hagbartx if you are doing a update you should be able to run this mysql command within your pathfinder database and fix the error:

ALTER TABLE system MODIFY COLUMN description text NOT NULL DEFAULT '';

It dosen't work for me because its a fresh install.

@hagbartx
Copy link

hagbartx commented Oct 11, 2018 •
edited
Loading

@anesbit i tried:

mysql> ALTER TABLE system MODIFY COLUMN description text NOT NULL DEFAULT ''; ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'description' can't have a default value

I fixed it by deleting the column and create a new description column with the same type as the description column in system_signature table.

@anesbit
Copy link
Author

anesbit commented Oct 11, 2018

Glad to hear @hagbartx

If anyone else is installing a fresh install you can login to your mysql database and use your 'pathfinder' database and apply this code in the console to fix this error.

CODE

DROP TABLE IF EXISTS `system`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `system` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NULL DEFAULT current_timestamp(),
  `updated` timestamp NULL DEFAULT current_timestamp(),
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `mapId` int(11) DEFAULT NULL,
  `systemId` int(11) DEFAULT NULL,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `alias` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `regionId` int(11) DEFAULT NULL,
  `region` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `constellationId` int(11) DEFAULT NULL,
  `constellation` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `effect` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `typeId` int(11) DEFAULT NULL,
  `security` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `trueSec` float NOT NULL DEFAULT 1,
  `statusId` int(11) NOT NULL DEFAULT 1,
  `locked` tinyint(1) NOT NULL DEFAULT 0,
  `rallyUpdated` timestamp NULL DEFAULT NULL,
  `rallyPoke` tinyint(1) NOT NULL DEFAULT 0,
  `description` text COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `posX` int(11) NOT NULL DEFAULT 0,
  `posY` int(11) NOT NULL DEFAULT 0,
  `createdCharacterId` int(11) DEFAULT NULL,
  `updatedCharacterId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `system___mapId__systemId` (`mapId`,`systemId`),
  KEY `system___created` (`created`),
  KEY `system___updated` (`updated`),
  KEY `system___active` (`active`),
  KEY `system___mapId` (`mapId`),
  KEY `system___systemId` (`systemId`),
  KEY `system___regionId` (`regionId`),
  KEY `system___constellationId` (`constellationId`),
  KEY `system___typeId` (`typeId`),
  KEY `system___statusId` (`statusId`),
  KEY `system___createdCharacterId` (`createdCharacterId`),
  KEY `system___updatedCharacterId` (`updatedCharacterId`),
  CONSTRAINT `fk_system___createdCharacterId___character___id` FOREIGN KEY (`createdCharacterId`) REFERENCES `character` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_system___mapId___map___id` FOREIGN KEY (`mapId`) REFERENCES `map` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_system___statusId___system_status___id` FOREIGN KEY (`statusId`) REFERENCES `system_status` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_system___typeId___system_type___id` FOREIGN KEY (`typeId`) REFERENCES `system_type` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_system___updatedCharacterId___character___id` FOREIGN KEY (`updatedCharacterId`) REFERENCES `character` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25252 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

@hilocz
Copy link

hilocz commented Oct 12, 2018

Thanks for hint @exodus4d
I had in my.cnf sql-mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES" after deleting STRICT_TRANS_TABLES and restart of MySQL , I was able do ALTER TABLE system MODIFY COLUMN description text NOT NULL DEFAULT '';

@fivesixzero
Copy link

fivesixzero commented Oct 18, 2018 •
edited
Loading

I ran into this error while setting up a fresh Pathfinder instance on a clean image of Ubuntu 18.10 w/ MySql (5.7.23-2ubuntu1, installed via apt). The error popped up when clicking the "Setup tables" button on the /setup page.

Here's what the Nginx error logs had:

PHP message: [index.php:23] Base->run()" while reading response header from upstream, client: 68.47.8.215, server: chitlin.space, request: "GET /setup?action=bootstrapDB&db=PF HTTP/2.0", upstream: "fastcgi://unix:/run/php/php7.2-fpm.sock:", host: "chitlin.space", referrer: "https://chitlin.space/setup"
2018/10/18 21:41:14 [error] 16851#16851: *63 FastCGI sent in stderr: "PHP message: Column `description` of type TEXT can't have a default value.
PHP message: [app/lib/db/cortex.php:396] DB\SQL\TableCreator->build()
PHP message: [app/main/model/basicmodel.php:984] DB\Cortex::setup()
PHP message: [app/main/model/systemmodel.php:789] Model\BasicModel::setup()
PHP message: [app/main/controller/setup.php:222] Controller\Setup->bootstrapDB()
PHP message: [index.php:23] Base->run()
PHP message: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'pathfinder.system' doesn't exist [/var/www/pf/app/lib/db/sql.php:226]
PHP message: [app/lib/db/sql.php:226] PDOStatement->execute()
PHP message: [app/lib/db/sql/schema.php:988] DB\SQL->exec()
PHP message: [app/main/model/basicmodel.php:945] DB\SQL\TableModifier->listIndex()
PHP message: [app/main/model/basicmodel.php:964] Model\BasicModel::indexExists()
PHP message: [app/main/model/systemmodel.php:792] Model\BasicModel::setMultiColumnIndex()
PHP message: [app/main/controller/setup.php:222] Controller\Setup->bootstrapDB()

Within the db the system table hadn't been created yet, which makes sense given the error.

I ended up using a dump of an old system table, almost identical to the one @anesbit shared above, but I changed the description column type from text to varchar(512), since varchar can have a default value.

After doing this the "Setup tables" and "Fix columns/keys" steps worked fine.

Is this a legit workaround, or will it cause other issues down the line?

Edit 1

The system.description column looks like it was 'fixed' to be a TEXT type with no default at some point during my troubleshooting. This led to a failure to add new systems, leading to a 500 error logout in the UI and this log data in the php-fpm logs:

[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "Stack trace:"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#0 /var/www/pf/app/lib/db/sql.php(226): PDOStatement->execute()"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#1 /var/www/pf/app/lib/db/sql/mapper.php(495): DB\SQL->exec(Array, Array)"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#2 /var/www/pf/app/lib/db/cursor.php(245): DB\SQL\Mapper->update()"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#3 /var/www/pf/app/lib/db/sql/session.php(95): DB\Cursor->save()"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#4 [internal function]: DB\SQL\Session->write('9nesamb5g0msh2k...', 'SETUP|a:1:{s:10...')"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#5 /var/www/pf/app/lib/base.php(2091): session_commit()"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#6 [internal function]: Base->unload('/var/www/pf')"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#7 {main}"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "  thrown in /var/www/pf/app/lib/db/sql.php on line 226"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: SQLSTATE[HY000]: General error: 1364 Field 'description' doesn't have a default value [/var/www/pf/app/lib/db/sql.php:226]"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/lib/db/sql.php:226] PDOStatement->execute()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/lib/db/sql/mapper.php:439] DB\SQL->exec()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/lib/db/cortex.php:2246] DB\SQL\Mapper->insert()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/lib/db/cortex.php:1192] DB\Cortex->insert()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/main/model/basicmodel.php:831] DB\Cortex->save()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/main/model/abstractmaptrackingmodel.php:111] Model\BasicModel->save()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/main/model/mapmodel.php:1285] Model\AbstractMapTrackingModel->save()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/main/controller/api/map.php:1076] Model\MapModel->saveSystem()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [app/main/controller/api/map.php:890] Controller\Api\Map->updateMapData()"
[19-Oct-2018 02:11:11] WARNING: [pool www] child 26720 said into stderr: "NOTICE: PHP message: [index.php:23] Base->run()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: SQLSTATE[HY000]: General error: 1364 Field 'description' doesn't have a default value [/var/www/pf/app/lib/db/sql.php:226]"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/lib/db/sql.php:226] PDOStatement->execute()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/lib/db/sql/mapper.php:439] DB\SQL->exec()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/lib/db/cortex.php:2246] DB\SQL\Mapper->insert()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/lib/db/cortex.php:1192] DB\Cortex->insert()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/main/model/basicmodel.php:831] DB\Cortex->save()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/main/model/abstractmaptrackingmodel.php:111] Model\BasicModel->save()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/main/model/mapmodel.php:1285] Model\AbstractMapTrackingModel->save()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/main/controller/api/map.php:1076] Model\MapModel->saveSystem()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [app/main/controller/api/map.php:890] Controller\Api\Map->updateMapData()"
[19-Oct-2018 02:11:31] WARNING: [pool www] child 25672 said into stderr: "NOTICE: PHP message: [index.php:23] Base->run()"

Edit 2

Altering the column to be varchar(512) with a blank default got things working as expected.

alter table system modify description varchar(512) default '';

This at least allows system creation to function properly. My users don't use the description feature much, if at all, so I'm guessing this workaround will have low impact even if we might miss out on some cool stuff that's been going on there.

@fivesixzero
Copy link

Once I got this going, I saw a similar message pop up while trying to build system indexes:

[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "NOTICE: PHP message: PHP Fatal error:  Uncaught PDOException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'data' at row 1 in /var/www/pf/app/lib/db/sql.php:226"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "Stack trace:"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#0 /var/www/pf/app/lib/db/sql.php(226): PDOStatement->execute()"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#1 /var/www/pf/app/lib/db/sql/mapper.php(495): DB\SQL->exec(Array, Array)"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#2 /var/www/pf/app/lib/db/cursor.php(245): DB\SQL\Mapper->update()"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#3 /var/www/pf/app/lib/db/sql/session.php(95): DB\Cursor->save()"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#4 [internal function]: DB\SQL\Session->write('9nesamb5g0msh2k...', 'SETUP|a:1:{s:10...')"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#5 /var/www/pf/app/lib/base.php(2091): session_commit()"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#6 [internal function]: Base->unload('/var/www/pf')"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "#7 {main}"
[19-Oct-2018 01:34:34] WARNING: [pool www] child 25667 said into stderr: "  thrown in /var/www/pf/app/lib/db/sql.php on line 226"

Running this in mysql CLI (after using the pathfinder db) fixed things though:

alter table `sessions` alter column `data` LONGTEXT;

I'm guessing that these issues are related to some changes in the way TEXT types are handled between MySQL and MariaDB and others?

Here's a list of version numbers for the core environment binaries I'm running on.

Ubuntu: 18.10 (Cosmic / 4.18.0-10-generic)
MySQL: Ver 5.7.23-2ubuntu1 for Linux on x86_64
Ngnix: nginx/1.15.5 (Ubuntu)
PHP: PHP 7.2.10-0ubuntu1
PCRE: 8.39
Redis: 4.1.1 (extension) / v=4.0.11 (server)
ZeroMQ: 1.1.3 (extension) / 4.2.5 (installation)
LibEvent/Event: 2.4.1

All packages were installed via apt-get and composer install got the dependencies set up just fine. The only package I had to install more manually was the PHP Event library, which required a bit of finagling ( mostly dev dependencies so that pecl install event could build the binary).

@exodus4d exodus4d added this to the v1.4.3 milestone Nov 17, 2018
@exodus4d
Copy link
Owner

The description default value issue should be fixed with my commits a few weeks ago
07d5be7

@fivesixzero interesting thing with the sessions table. I haven´t seen this because i use Redis as Session storage (not MySQL). Ill check if I can find an issue here.

@exodus4d exodus4d self-assigned this Nov 17, 2018
This was referenced Nov 17, 2018
Closed
Closed
@exodus4d exodus4d mentioned this issue Dec 2, 2018
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@exodus4d exodus4d

Labels
major bug
Projects
None yet
Milestone
v1.4.3
Development

No branches or pull requests
6 participants
@anesbit @exodus4d @hilocz @fivesixzero @WildStrawberryEVE @hagbartx