Initialize.sql fails - table doesn't support fulltext indexes for MySQL 5.5 and below

[zo0o0ot]

script fails at this query:

CREATE TABLE IF NOT EXISTS `managers` (   `manager_id` int(11) NOT NULL auto_increment,   `draft_id` int(11) NOT NULL default '0',   `manager_name` text NOT NULL,   `draft_order` tinyint(3) unsigned NOT NULL default '0',   PRIMARY KEY  (`manager_id`),   INDEX `draft_idx` (`draft_id`),   FULLTEXT KEY `manager_idx` (`manager_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

Response:
"Error Code: 1214. The used table type doesn't support FULLTEXT indexes"

[zo0o0ot]

Context- I'm attempting to build from the Digital Ocean LAMP 14.04 Droplet.

MySQL Version: 5.5.46-0ubuntu0.14.04.2

[zo0o0ot]

Stackoverflow seems to suggest an upgrade of MySQL or changing the engine to MyISAM:

http://stackoverflow.com/questions/20964269/1214-the-used-table-type-doesnt-support-fulltext-indexes

[zo0o0ot]

It looks like there's some MySQL 5.6 stuff in here. Maybe the README requirements should be updated.

[mattheworres]

I'd be willing to bet just removing the FULLTEXT index from the Initialize script is a better way to go. It's something MyISAM provides but InnoDB does not.

I made the switch to InnoDB for version 2 for data integrity reasons, and there's still future work to do to allow Silex to leverage that capability fully.

EDIT: If you remove the FULLTEXT index from the Initialize script, and this then fixes the initialize script, feel free to open another PR with this fix (and reference this issue). Thanks!

[zo0o0ot]

Fair enough. I'll take another look at it to see if I can cobble together a script that works. I've been considering deployment to a couple different places in the cloud, including OpenShift, and all of those places seem to default to MySQL 5.5 as the de-facto starting environment. I'll try to remove the fulltext indexes and see if I can get everything to build. Since the the original attempt was on a DigitalOcean instance, I just nuked it from orbit when it didn't work, since those instances aren't free. I'll try this again on OpenShift and see if that makes anything easier.

[mattheworres]

I'd certainly be open to relaxing some database constraints in order to satisfy requirements for one minor version number lower of MySQL. When writing the docs I didn't think I had used anything crazy new for 5.6 (I think PHP Draft 1.3 ran fine on 5.3 and 5.4 but thats just a hunch), but 5.5 is not much older and shouldn't drastically change things much, so I'd be open to making it work for a platform like OpenShift.

[mattheworres]

Had a few minutes to look at this and there are a few other instances of breaking changes between 5.5 and 5.6 that I did not account for (other than the FULLTEXT instances). I'll take a look at it when I have more time and will try to patch this up so it can be run on an OpenShift instance - as I'd like to do the same myself.

[zo0o0ot]

Sweet. It's good to hear that we're have the same outlook on that.

[mattheworres]

@zo0o0ot so I just worked through the issues I had to get PHPDraft up and running on OpenShift. Since it's cloud-based, it's insanely fast (10x faster than my shared hosting, no surprise there). I'm going to eventually add another markdown file detailing instructions on how to install the app on OpenShift, because it's pretty easy. Here's what I found:

1. All FULLTEXT indices need removed from tables using InnoDB.
2. The users table has a default value set for creationTime (a DATETIME column). This was added in MySQL 5.6. I remove the default value, and explicitly set the value to UTC_TIMESTAMP() in the LoginUserRepository's Create method.
3. (this one took me all night) The version of Silex I'm using with Composer is a dev version. Because of this, Composer actually pulls down the repo from Github. Not an issue when I publish releases per se, except when that code is being deployed via git as well. Git treats the Silex folder in /vendor as a submodule, so it won't actually publish those files. I had to monkey with OpenShift shell access in order to trace the logs and do some Googling to figure this out. So to resolve, delete the entire /vendor folder, then tell Composer to install all dependencies with the --prefer-dist flag as so: composer install --prefer-dist
4. I also decided to bump the appsettings back into the root directory. This requirement was a pain in the ass, but I originally intended it to be increased security, but it wasn't worth it. I updated the .htaccess and web.config to disallow access to the file. But with OpenShift, you don't get a subdirectory, so I had to do this.

The last one was a pain (thanks to http://stackoverflow.com/a/14921890/324527 for saving my sanity ), and I had to commit to delete the "submodule" first, then run the Composer install and re-commit the entire directory.

I'll be pushing these changes up as part of 2.0.1, but you're probably savvy enough to get it up and running.

[mattheworres]

I fixed Initialize.sql as well as a few minor fixes elsewhere in 2.0.1, and also added an install guide specific to OpenShift as I think this is a perfect solution for folks who want to run their own installs. Refer to OPENSHIFT_DEPLOYMENT.md for details, and let me know if you have any issues!

[zo0o0ot]

I think this is awesome. Technically, it could be possible to streamline the setup even more, but I'm not certain if it's worth the trouble.

I found openshift through an pretty snazzy auto-deploying Bolt CMS instance.

https://github.com/Atriedes/openshift-boltcms-quickstart

It's possible that this project may be able to borrow from that in order to streamline the process, but as long as I can get phpdraft on openshift and running, you won't be hearing any complaints from me.

I have a mandrill account set up for a different purpose, so I might end up using them for email delivery. If any directions need updating, I'll let you know. I might also use MySQL Workbench instead of phpMyAdmin. I was able to get that going through ssh by checking a couple different setup parameters in the MySQL Workbench local client, and I can share those setup instructions with an update of the Openshift deploy docs if I get things up and working. I'm probably going to give it a whirl tonight.