[Bug] Migration is incomplete on update from 8.2.1 to 8.3.1 #6284

biplobice · 2018-01-10T04:46:34Z

How to reproduce

Download & install concrete5-8.2.1

Run concrete/bin/concrete5 c5:compare-schema

Output:

1 query found
1: ALTER TABLE FailedLoginAttempts CHANGE flaTimestamp flaTimestamp TIMESTAMP NOT NULL COMMENT 'Timestamp of the failed login attempt'

Environment Information:

# concrete5 Version
Core Version - 8.2.1
Version Installed - 8.2.1
Database Version - 20170802000000

# concrete5 Packages
None

# concrete5 Overrides
None

# concrete5 Cache Settings
Block Cache - On
Overrides Cache - On
Full Page Caching - Off
Full Page Cache Lifetime - Every 6 hours (default setting).

# Server Software
nginx/1.12.2

# Server API
fpm-fcgi

# PHP Version
7.1.12

# PHP Extensions
bcmath, bz2, calendar, cgi-fcgi, Core, ctype, curl, date, dba, dom, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, json, ldap, libxml, mbstring, mysqli, mysqlnd, odbc, openssl, pcntl, pcre, PDO, pdo_mysql, PDO_ODBC, pdo_sqlite, Phar, posix, readline, Reflection, session, shmop, SimpleXML, soap, sockets, SPL, sqlite3, standard, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, zlib

# PHP Settings
max_execution_time - 300
log_errors_max_len - 1024
max_file_uploads - 20
max_input_nesting_level - 64
max_input_time - 60
max_input_vars - 1000
memory_limit - 128M
post_max_size - 128M
sql.safe_mode - Off
upload_max_filesize - 128M
ldap.max_links - Unlimited
mysqli.max_links - Unlimited
mysqli.max_persistent - Unlimited
odbc.max_links - Unlimited
odbc.max_persistent - Unlimited
pcre.backtrack_limit - 1000000
pcre.recursion_limit - 100000
session.cache_limiter - <i>no value</i>
session.gc_maxlifetime - 7200
soap.wsdl_cache_limit - 5

Download and update to version 8.3.1

Run concrete/bin/concrete5 c5:compare-schema

Output:

13 queries found
1: ALTER TABLE FailedLoginAttempts CHANGE flaTimestamp flaTimestamp TIMESTAMP NOT NULL COMMENT 'Timestamp of the failed login attempt'
2: ALTER TABLE fileimagethumbnailpaths CHANGE thumbnailFormat thumbnailFormat VARCHAR(5) NOT NULL
3: CREATE TABLE Geolocators (glID INT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT 'Geolocator ID', glHandle VARCHAR(255) NOT NULL COMMENT 'Geolocator handle', glName VARCHAR(255) NOT NULL COMMENT 'Geolocator name', glDescription LONGTEXT NOT NULL COMMENT 'Geolocator description', glConfiguration LONGTEXT NOT NULL COMMENT 'Geolocator configuration options(DC2Type:json_array)', glActive TINYINT(1) NOT NULL COMMENT 'Is this Geolocator the active one?', glPackage INT UNSIGNED DEFAULT NULL, UNIQUE INDEX UNIQ_83BB1614D4F4D416 (glHandle), INDEX IDX_83BB1614D5F6CC40 (glPackage), PRIMARY KEY(glID)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB COMMENT = 'List of all the installed Geolocator services' 
4: ALTER TABLE Geolocators ADD CONSTRAINT FK_83BB1614D5F6CC40 FOREIGN KEY (glPackage) REFERENCES Packages (pkgID) ON DELETE CASCADE
5: ALTER TABLE CalendarEventVersions CHANGE evDateAdded evDateAdded DATETIME NOT NULL, CHANGE evActivateDateTime evActivateDateTime DATETIME DEFAULT NULL
6: ALTER TABLE CalendarEventVersions ADD CONSTRAINT FK_8E260274FD71026C FOREIGN KEY (uID) REFERENCES Users (uID)
7: ALTER TABLE Calendars ADD CONSTRAINT FK_62E00AC521D8435 FOREIGN KEY (siteID) REFERENCES Sites (siteID)
8: ALTER TABLE CalendarEventVersionAttributeValues ADD CONSTRAINT FK_8C835B05B6561A7E FOREIGN KEY (akID) REFERENCES AttributeKeys (akID)
9: ALTER TABLE CalendarEventVersionAttributeValues ADD CONSTRAINT FK_8C835B05A2A82A5D FOREIGN KEY (avID) REFERENCES AttributeValues (avID)
10: ALTER TABLE atAddressSettings ADD akGeolocateCountry TINYINT(1) NOT NULL
11: ALTER TABLE CalendarEventAttributeKeys ADD CONSTRAINT FK_27F477CCB6561A7E FOREIGN KEY (akID) REFERENCES AttributeKeys (akID) ON DELETE CASCADE
12: ALTER TABLE btDocumentLibrary CHANGE displayOrderDesc displayOrderDesc TINYINT(1) DEFAULT '0' NOT NULL
13: ALTER TABLE btContentImage CHANGE openLinkInNewWindow openLinkInNewWindow TINYINT(1) DEFAULT '0'

Environment Information:

# concrete5 Version
Core Version - 8.3.1
Version Installed - 8.3.1
Database Version - 20171218000000

# concrete5 Packages
None

# concrete5 Overrides
None

# concrete5 Cache Settings
Block Cache - On
Overrides Cache - On
Full Page Caching - Off
Full Page Cache Lifetime - Every 6 hours (default setting).

# Server Software
nginx/1.12.2

# Server API
fpm-fcgi

# PHP Version
7.1.12

# PHP Extensions
bcmath, bz2, calendar, cgi-fcgi, Core, ctype, curl, date, dba, dom, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, json, ldap, libxml, mbstring, mysqli, mysqlnd, odbc, openssl, pcntl, pcre, PDO, pdo_mysql, PDO_ODBC, pdo_sqlite, Phar, posix, readline, Reflection, session, shmop, SimpleXML, soap, sockets, SPL, sqlite3, standard, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, zlib

# PHP Settings
max_execution_time - 300
log_errors_max_len - 1024
max_file_uploads - 20
max_input_nesting_level - 64
max_input_time - 60
max_input_vars - 1000
memory_limit - 128M
post_max_size - 128M
sql.safe_mode - Off
upload_max_filesize - 128M
ldap.max_links - Unlimited
mysqli.max_links - Unlimited
mysqli.max_persistent - Unlimited
odbc.max_links - Unlimited
odbc.max_persistent - Unlimited
pcre.backtrack_limit - 1000000
pcre.recursion_limit - 100000
session.cache_limiter - <i>no value</i>
session.gc_maxlifetime - 7200
soap.wsdl_cache_limit - 5

Notes

concrete/bin/concrete5 c5:update can't fix this
I ran the sql manually, but still it shows the queries
I didn't check with other versions.

The text was updated successfully, but these errors were encountered:

c5dragon · 2018-01-10T09:01:27Z

After an update from 8.2.1 to 8.3.1 (and geolocators table patch) I'm still left with:

5 queries found
1: ALTER TABLE FailedLoginAttempts CHANGE flaTimestamp flaTimestamp TIMESTAMP NOT NULL COMMENT 'Timestamp of the failed login attempt'
2: ALTER TABLE FileImageThumbnailPaths CHANGE thumbnailFormat thumbnailFormat VARCHAR(5) NOT NULL
3: ALTER TABLE CalendarEventVersions CHANGE evDateAdded evDateAdded DATETIME NOT NULL, CHANGE evActivateDateTime evActivateDateTime DATETIME DEFAULT NULL
4: ALTER TABLE btDocumentLibrary CHANGE displayOrderDesc displayOrderDesc TINYINT(1) DEFAULT '0' NOT NULL
5: ALTER TABLE btStyledMaps CHANGE infostate infostate TINYINT(1) DEFAULT '1' NOT NULL

aohlrogge · 2018-01-10T21:39:08Z

This also happened for me.

yfsneals · 2018-01-10T22:02:39Z

I am not a DB admin, but the user perspective might be of value.
Here's how I experienced the issue:
I manage several sites with shared cores. After upgrading directly from 8.2.1 to 8.3.1, when I went to the Extend Concrete 5 page and clicked on Details for any installed add-on, I got

An exception occurred while executing 'SELECT t0.glID AS glID_1, t0.glHandle AS glHandle_2, t0.glName AS glName_3, t0.glDescription AS glDescription_4, t0.glConfiguration AS glConfiguration_5, t0.glActive AS glActive_6, t0.glPackage AS glPackage_7 FROM Geolocators t0 WHERE t0.glPackage = ?' with params [21]: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'yfsneals_imcomhq.Geolocators' doesn't exist

1146: Table not found

In Systems and Settings, a new menu item, Geolocation, rendered a similar error.
I tried upgrading to 8.3.0 and then to 8.3.1 on another site, and there was no error.
I tried downgrading to 8.3.0 and then returning to 8.3.1 on one of the affected sites -- the error persisted.
I applied Hutman's solution and the error disappeared.

Hope this helps.

Here's the code:

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Jan 10, 2018 at 10:53 AM
-- Server version: 5.7.20-0ubuntu0.16.04.1
-- PHP Version: 7.0.22-0ubuntu0.16.04.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `concrete_5_8_3_1`
--

-- --------------------------------------------------------

--
-- Table structure for table `Geolocators`
--

CREATE TABLE `Geolocators` (
  `glID` int(10) UNSIGNED NOT NULL COMMENT 'Geolocator ID',
  `glHandle` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Geolocator handle',
  `glName` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Geolocator name',
  `glDescription` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT 'Geolocator description',
  `glConfiguration` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT 'Geolocator configuration options(DC2Type:json_array)',
  `glActive` tinyint(1) NOT NULL COMMENT 'Is this Geolocator the active one?',
  `glPackage` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='List of all the installed Geolocator services';

--
-- Dumping data for table `Geolocators`
--

INSERT INTO `Geolocators` (`glID`, `glHandle`, `glName`, `glDescription`, `glConfiguration`, `glActive`, `glPackage`) VALUES
(1, 'geoplugin', 'geoPlugin', '', '{"url":"http:\\/\\/www.geoplugin.net\\/json.gp?ip=[[IP]]"}', 1, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Geolocators`
--
ALTER TABLE `Geolocators`
  ADD PRIMARY KEY (`glID`),
  ADD UNIQUE KEY `UNIQ_83BB1614D4F4D416` (`glHandle`),
  ADD KEY `IDX_83BB1614D5F6CC40` (`glPackage`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Geolocators`
--
ALTER TABLE `Geolocators`
  MODIFY `glID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Geolocator ID', AUTO_INCREMENT=2;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `Geolocators`
--
ALTER TABLE `Geolocators`
  ADD CONSTRAINT `FK_83BB1614D5F6CC40` FOREIGN KEY (`glPackage`) REFERENCES `Packages` (`pkgID`) ON DELETE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

haeflimi · 2018-01-16T15:25:55Z

I've created a Issue for this a while ago: #6253
According to aembler this should be fixed now. However that fix is not in the 8.3.1 release but only in the develop branch so far.

mlocati · 2018-01-16T15:41:05Z

I'm going to work on this issue

mlocati · 2018-01-18T13:53:41Z

Can somebody check if #6311 fixes this issue?

biplobice · 2018-01-24T02:48:12Z

After updating from 8.3.1 to 8.3.2 :(

15 queries found
1: ALTER TABLE FailedLoginAttempts CHANGE flaTimestamp flaTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'Timestamp of the failed login attempt'
2: ALTER TABLE FileImageThumbnailPaths CHANGE thumbnailFormat thumbnailFormat VARCHAR(5) NOT NULL
3: ALTER TABLE CalendarEventAttributeKeys ADD CONSTRAINT FK_27F477CCB6561A7E FOREIGN KEY (akID) REFERENCES AttributeKeys (akID) ON DELETE CASCADE
4: ALTER TABLE atAddressSettings ADD akGeolocateCountry TINYINT(1) NOT NULL
5: ALTER TABLE CalendarEventVersionAttributeValues ADD CONSTRAINT FK_8C835B05B6561A7E FOREIGN KEY (akID) REFERENCES AttributeKeys (akID)
6: ALTER TABLE CalendarEventVersionAttributeValues ADD CONSTRAINT FK_8C835B05A2A82A5D FOREIGN KEY (avID) REFERENCES AttributeValues (avID)
7: ALTER TABLE CalendarEventVersions ADD CONSTRAINT FK_8E260274FD71026C FOREIGN KEY (uID) REFERENCES Users (uID)
8: ALTER TABLE Calendars ADD CONSTRAINT FK_62E00AC521D8435 FOREIGN KEY (siteID) REFERENCES Sites (siteID)
9: ALTER TABLE Geolocators ADD CONSTRAINT FK_83BB1614D5F6CC40 FOREIGN KEY (glPackage) REFERENCES Packages (pkgID) ON DELETE CASCADE
10: ALTER TABLE btManualNavEntries CHANGE openInNewWindow openInNewWindow TINYINT(1) DEFAULT '0' NOT NULL
11: ALTER TABLE btContentImage CHANGE openLinkInNewWindow openLinkInNewWindow TINYINT(1) DEFAULT '0' NOT NULL
12: ALTER TABLE btAuthorPageList CHANGE includeDate includeDate TINYINT(1) DEFAULT '0' NOT NULL, CHANGE truncateSummaries truncateSummaries TINYINT(1) DEFAULT '0' NOT NULL, CHANGE displayFeaturedOnly displayFeaturedOnly TINYINT(1) DEFAULT '0' NOT NULL, CHANGE displayThumbnail displayThumbnail TINYINT(1) DEFAULT '0' NOT NULL, CHANGE displayAliases displayAliases TINYINT(1) DEFAULT '1' NOT NULL, CHANGE paginate paginate TINYINT(1) DEFAULT '0' NOT NULL, CHANGE useButtonForLink useButtonForLink TINYINT(1) DEFAULT '0' NOT NULL, CHANGE includeName includeName TINYINT(1) DEFAULT '1' NOT NULL, CHANGE includeDescription includeDescription TINYINT(1) DEFAULT '1' NOT NULL
13: ALTER TABLE btAuthorProfile CHANGE displayBasicAttributes displayBasicAttributes TINYINT(1) DEFAULT '0' NOT NULL, CHANGE displayProfileAttributes displayProfileAttributes TINYINT(1) DEFAULT '0' NOT NULL, CHANGE displayMemberListAttributes displayMemberListAttributes TINYINT(1) DEFAULT '0' NOT NULL, CHANGE displayProfilePicture displayProfilePicture TINYINT(1) DEFAULT '0' NOT NULL, CHANGE linkToProfilePage linkToProfilePage TINYINT(1) DEFAULT '0' NOT NULL
14: ALTER TABLE btCustomForm CHANGE notifyMeOnSubmission notifyMeOnSubmission TINYINT(1) DEFAULT '0' NOT NULL
15: ALTER TABLE btFormAnswerSet CHANGE created created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

mlocati · 2018-01-24T06:10:03Z

I'm pretty sure that those differences are caused by some previous migrations that hasn't been executed correctly: could you check that by executing the compare schema command before upgrading to 8.3.2?

BTW I'm going to add an easy way to re-execute previous migrations.

biplobice · 2018-01-24T07:54:19Z

Yes, there was. As mentioned on this issue.

mlocati · 2018-01-25T17:47:38Z

@biplobice Could you check if #6342 fixes your issue?

biplobice · 2018-01-26T07:06:37Z

@mlocati
Wow! It's a great work. As usual :)
But, unfortunately didn't work for me. :(

mlocati · 2018-01-26T07:30:26Z

Let's continue the discussion at #6342

mlocati mentioned this issue Jan 18, 2018

Fix migrations #6311

Merged

aembler closed this as completed Jan 18, 2018

mlocati mentioned this issue Jan 26, 2018

Add a way to re-apply already executed migrations #6342

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Bug] Migration is incomplete on update from 8.2.1 to 8.3.1 #6284

[Bug] Migration is incomplete on update from 8.2.1 to 8.3.1 #6284

biplobice commented Jan 10, 2018

c5dragon commented Jan 10, 2018 •

edited

aohlrogge commented Jan 10, 2018

yfsneals commented Jan 10, 2018 •

edited

haeflimi commented Jan 16, 2018

mlocati commented Jan 16, 2018

mlocati commented Jan 18, 2018

biplobice commented Jan 24, 2018

mlocati commented Jan 24, 2018

biplobice commented Jan 24, 2018

mlocati commented Jan 25, 2018

biplobice commented Jan 26, 2018 •

edited

mlocati commented Jan 26, 2018

[Bug] Migration is incomplete on update from 8.2.1 to 8.3.1 #6284

[Bug] Migration is incomplete on update from 8.2.1 to 8.3.1 #6284

Comments

biplobice commented Jan 10, 2018

How to reproduce

Notes

c5dragon commented Jan 10, 2018 • edited

aohlrogge commented Jan 10, 2018

yfsneals commented Jan 10, 2018 • edited

haeflimi commented Jan 16, 2018

mlocati commented Jan 16, 2018

mlocati commented Jan 18, 2018

biplobice commented Jan 24, 2018

mlocati commented Jan 24, 2018

biplobice commented Jan 24, 2018

mlocati commented Jan 25, 2018

biplobice commented Jan 26, 2018 • edited

mlocati commented Jan 26, 2018

c5dragon commented Jan 10, 2018 •

edited

yfsneals commented Jan 10, 2018 •

edited

biplobice commented Jan 26, 2018 •

edited