search

anvc / scalar

Born-digital, open source, media-rich scholarly publishing that’s as easy as blogging.
Other
232 stars 73 forks source link

ERROR 1061 (42000) at line 26: Duplicate key name 'book_id' #125

Open g7morris opened 4 years ago

g7morris commented 4 years ago

Currently attempting to install Scalar per the instructions in the INSTALL.txt file. 

root@5cf8a1674e70:/# mysql -u scalar_user -p scalar_store < scalar_store.sql
Enter password: 
ERROR 1061 (42000) at line 26: Duplicate key name 'book_id'

This only creates one table called scalar_db_books and the remaining tables within the sql file are not created.

Additionally as a result, the enduser cannot connect to the site due to the lack of missing tables and is greeted with this error:

Fatal error: Uncaught Error: Call to a member function num_rows() on boolean in /var/www/html/system/libraries/Session.php:233 Stack trace: #0 /var/www/html/system/libraries/Session.php(107): CI_Session->sess_read() #1 /var/www/html/system/core/Loader.php(1100): CI_Session->__construct() #2 /var/www/html/system/core/Loader.php(976): CI_Loader->_ci_init_class('session', '', NULL, NULL) #3 /var/www/html/system/core/Loader.php(217): CI_Loader->_ci_load_class('Session', NULL, NULL) #4 /var/www/html/system/application/core/MY_Controller.php(42): CI_Loader->library('session') #5 /var/www/html/system/application/controllers/system.php(33): MY_Controller->__construct() #6 /var/www/html/system/core/CodeIgniter.php(309): System->__construct() #7 /var/www/html/codeigniter.php(203): require_once('/var/www/html/s...') #8 {main} thrown in /var/www/html/system/libraries/Session.php on line 233

Any troubleshooting insight would be greatly appreciated. Thanks!

g7morris commented 4 years ago

I've also been able to replicate this issue using PHP 7.2 & MySQL 5.5.6.

craigdietrich commented 4 years ago

Hi @g7morris

Sorry you're experiencing these errors!

Is there a chance your database is forcing InnoDB (which respects foreign key restrains)? scalar_store should be using MyISAM (which does). I've never accounted that kind of error on my end, unfortunately. Do things behave different if you create the DB via phpMyAdmin as opposed to on the command line?

The last commit to scalar_store.sql was a pull request by @arthurian ... Arthur, curious if you have any thoughts?

g7morris commented 4 years ago

Thanks for the reply @craigdietrich and the tip on MyISAM. As an aside, since MySQL version 5.5 release in 2010, InnoDB has been the default engine. I'll test changing the default engine to MyISAM and see what happens. For the future, would it be an idea to document this somewhere (e.g. INSTALL.txt and this repo's README.md) along with general system requirements & dependencies for end-users especially first time users like myself? One other tip, mycrypt is no longer in PHP 7.2. Endusers will have to compile it to use it. Articles like this https://www.techrepublic.com/article/how-to-install-mcrypt-for-php-7-2/ & and / or https://lukasmestan.com/install-mcrypt-extension-in-php7-2/ can be helpful for Scalar users attempting to install as well. Thanks

craigdietrich commented 4 years ago

Actually, as of Scalar v2.5.5, the mcrypt library is no longer needed. Do we say somewhere that it is?

Well, scalar_store.sql includes ENGINE=MyISAM in each statement, so I've always assumed that does the trick. Maybe that's changed -- maybe there's some sort of override at the DB level now that takes precedence?

Let us know if any of the above works for you

g7morris commented 4 years ago

If you do and I've clearly missed it then my apologies; I'd love to see that documentation. I've yet to find anywhere what PHP dependencies are actually required. My builds so far have been a cobbled together understanding gleaned from Google, searches of various existing Scalar projects, git repos and Dockerfiles. I can appreciate the focus is on getting the project going and fixing bugs but perhaps a documentation sprint to streamline things would be warranted in the future. Thanks for confirming mcrypt, I'll take it out! :)

craigdietrich commented 4 years ago

Sorry, I'm a little confused: all you need to do to install Scalar is download the most current Release:

https://github.com/anvc/scalar/releases

... then put it on a LAMP server. From there install the DB and add some values to a few config files (described in INSTALL.txt). There aren't any dependencies,

Now, if you don't have a LAMP server set up or are using an alternative approach, like Docker, I'll have to defer, we haven't done those types of installs on our end so unfortunately can't be of help.

g7morris commented 4 years ago

I do not disagree that simplicity is what you and your team are aiming for however "LAMP server" in this context and in your argument above is an assumption that isn't explained. The steps that go into setting one up depending on the OS distro can mean many things to different users.

sudo apt-get install -y php7.2 apache2 python-mysqldb libxml2-dev libapache2-mod-php7.2 libcurl4-openssl-dev php7.2 php7.2-cli php7.2-json php7.2-common php7.2-readline php-pear php7.2-curl php7.2-mbstring php7.2-xmlrpc php7.2-dev php7.2-ldap php7.2-xml php7.2-mysql php7.2-soap php7.2-xsl php7.2-zip php7.2-bcmath php7.2-intl php-uploadprogress libicu-dev php7.2-gd libxslt1.1 libxslt1-dev libfreetype6-dev libjpeg-turbo8-dev libpng-dev

So by using those three slight examples above, by no means am I faulting choices of tools nor abilities; you've stated clearly some of the challenges in other tickets e.g. https://github.com/anvc/scalar/issues/91 . I'm merely trying to point out knowing what to use is key and just trying to say gently and without malice, perhaps spelling out more of the steps in a piece of documentation might foster and encourage even more Scalar usage within a wider community. Getting folks past the install level is usually the first hurdle and obstacle in my experience.

However on a different note, no luck on changing the MySQL engine. The script still errors.

I can see that on the MySQL server that MyISAM is default

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

I can also see that the only table created is also MyISAM

mysql> SELECT TABLE_NAME, ENGINE FROM   information_schema.TABLES WHERE  TABLE_SCHEMA = 'scalar_store';
+-----------------+--------+
| TABLE_NAME      | ENGINE |
+-----------------+--------+
| scalar_db_books | MyISAM |
+-----------------+--------+
1 row in set (0.00 sec)

Alas I cannot get the script to go further than creating the one table.

craigdietrich commented 4 years ago

Sorry to hear our documentation isn't thorough enough for your needs. We'd be happy to accept a pull request if you sort things out and want to contribute to future users.

Unfortunately, installing PHP, its modules, and debugging MySQL import errors is beyond what are little team can provide. Maybe someone from outside the Scalar team camp can jump in.

g7morris commented 4 years ago

@craigdietrich No worries. I'd be glad to offer a pull request on that front for your review and for other users once I can get it working ;).

I can fully understand your team's challenges. I appreciate anyone looking into the issue when / if they can.

g7morris commented 4 years ago

@craigdietrich I think I might have gotten it sorted after all and I'm just leaving this here for any further feedback if warranted or any others having challenges.

Per your good suggestion above, I started using a MySQL GUI instead of the command line. I pulled apart the script and ran the CREATE TABLES commands one by one, noting errors and the like.

Most ran but only two were hold outs and posted errors.

The first holdout appeared to be MySQL 5.7 specific: ERROR 1067 (42000): Invalid default value for 'datetime' which apparently is a result of constraints outlined in detail here https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

CREATE TABLE IF NOT EXISTS `scalar_db_rel_replied` ( `parent_version_id` int(10) unsigned NOT NULL DEFAULT '0', `child_version_id` int(10) unsigned NOT NULL DEFAULT '0', `paragraph_num` int(5) unsigned NOT NULL DEFAULT '0', `datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, KEY `parent_child` (`parent_version_id`,`child_version_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I wasn't able to get it going from suggestions like https://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field however I was able to by using:

SET SQL_MODE='ALLOW_INVALID_DATES'; which may not be ideal but allows me to proceed.

The second error the originally reported ERROR 1061 (42000): Duplicate key name 'book_id' appears to come when I run this:

CREATE TABLE IF NOT EXISTS `scalar_db_content` (
  `content_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `book_id` int(10) unsigned NOT NULL DEFAULT '0',
  `recent_version_id` int(10) unsigned NOT NULL DEFAULT '0',
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `type` enum('composite','media') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'composite',
  `is_live` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `paywall` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `thumbnail` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `background` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `banner` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `custom_style` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `custom_scripts` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `color` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `audio` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category` enum('commentary','review','term') COLLATE utf8_unicode_ci DEFAULT NULL,
  `user` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`content_id`),
  UNIQUE (`book_id`, `slug`),
  KEY `book_id` (`book_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

When I remove the last lineKEYbook_id(book_id), the script completes, the tables are created and I am looking at a new Scalar site! I've been able to login, create books and content ... so far. Neat stuff.

@craigdietrich Can you think of any negative impact offhand from removing that line?

What versions of MySQL do folks tend use typically? If 5.7 is too restrictive, how far back should I install to 5.5.x or 5.4?

Thanks again for all your help and previous suggestions.

craigdietrich commented 4 years ago

Hey thanks @g7morris for sticking with this!

Re the first problem (datetime), I went ahead and updated SQL files to use CURRENT_TIMESTAMP as the DEFAULT, which I think will correct the problem:

https://github.com/anvc/scalar/commit/9b4e00930e27a53292f11d9dbe62c520c4a8f31f https://github.com/anvc/scalar/commit/14f635ea20fc2724a33650470219c20e6e1aeea8

Re the second (UNIQUE), I think we should wait to see what @arthurian says, since he committed that addition and it was needed for the S3 filesystem to work properly.

g7morris commented 4 years ago

Thanks @craigdietrich for the fixes, I'll test shortly. I also appreciate you reaching out to @arthurian for any further insight. Thanks in advance @arthurian for any tips, thoughts etc.

arthurian commented 4 years ago

@craigdietrich @g7morris My apologies for not responding sooner regarding the second error. That's definitely my fault, as I had introduced that particular UNIQUE constraint in PR #123.

The intent was to add a database-level guarantee that all of the slugs in a book are unique. The more important UNIQUE constraint is the one on scalar_db_books, which guarantees the uniqueness of a book slug globally. That's the one that we had to fix to make the S3 filesystem work properly. The additional UNIQUE constraint on scalar_db_content was not strictly necessary, but I think it's still a good idea, because it formalizes the implicit constraints that already exist in the application.

In any case, the issue with the CREATE TABLE statement is that there are two indexes being created on scalar_db_content: an index on book_id and a secondary unique index on the book_id and slug. It appears that since the UNIQUE constraint does not have an assigned name, it is defaulting to book_id (the first of the two columns), which also happens to be the name of the KEY constraint (e.g. the index on book_id). You can't have duplicate index names, hence the error.

One solution is to name the UNIQUE constraint within the CREATE TABLE statement:

UNIQUE `book_id_slug_uq` (`book_id`, `slug`)

So the complete table statement becomes:

CREATE TABLE IF NOT EXISTS `scalar_db_content` (
  `content_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `book_id` int(10) unsigned NOT NULL DEFAULT '0',
  `recent_version_id` int(10) unsigned NOT NULL DEFAULT '0',
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `type` enum('composite','media') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'composite',
  `is_live` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `paywall` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `thumbnail` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `background` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `banner` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `custom_style` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `custom_scripts` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `color` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `audio` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category` enum('commentary','review','term') COLLATE utf8_unicode_ci DEFAULT NULL,
  `user` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`content_id`),
  UNIQUE `book_id_slug_uq` (`book_id`, `slug`),
  KEY `book_id` (`book_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I might also suggest giving the KEY index a name such as book_id_idx to further disambiguate the two index names. But that's not necessary to resolve the problem at hand.

@craigdietrich Do you want me to submit a PR with the fix, or do you want to take it?

craigdietrich commented 4 years ago

Hiya @arthurian

This seems fine to me. That "book_id_slug_uq" is just the identifier for the UNIQUEness, right? It doesn't really show up anywhere?

arthurian commented 4 years ago

@craigdietrich Yeah that’s just the identifier for the unique constraint - it won’t show up anywhere other than when you’re in the database looking at the constraints.