MIssing Column in Table

135 views
Skip to first unread message

dougth...@hotmail.com

unread,
Jun 12, 2017, 12:12:32 PM6/12/17
to AtoM Users
User got the following error when trying to link a digitized object to an archival description in Atom 2.3.

500 | Internal Server Error | PropelException
[wrapped: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rights_i18n.IDENTIFIER_VALUE' in 'field list']


I had a look at the database and the rights.i18n table doesn't have that column.

Just wondering if any has had this issue before.

Dan Gillean

unread,
Jun 12, 2017, 12:43:37 PM6/12/17
to ICA-AtoM Users
Hi Doug,

A couple of quick questions:
  • Is this version 2.3.0 or 2.3.1? Can you provide the full version number (which includes the migration number) as found in Admin > Settings at the top of the page?
  • Was this a new installation from scratch or were you upgrading from an earlier version? How did you install - from the tarball available on AtoM's download page, or from our GitHub code repository?

One thing you could try to do is run the SQL upgrade task, which should ensure that your database is updated to the proper database schema. I would strongly recommend that you make a database backup first - see:

After that, you can try running the upgrade task from the root AtoM directory like so:

  • php symfony tools:upgrade-sql

I would suggest that you also try restarting services such as your webserver and PHP-FPM after, and then clear the cache and re-index. If you are installed on Ubuntu 16.04 using Nginx as your webserver, you can use the following commands to restart services:

  • sudo systemctl reload nginx
  • sudo systemctl restart php7.0-fpm

On Ubuntu 14.04, try:

  • sudo service nginx restart
  • sudo service php5-fpm restart

Then you can clear the application cache and re-index:

  • php symfony cc
  • php symfony search:populate

Let us know if that resolves the issue.


Regards,


Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/0f151b90-ac96-4843-bd48-02625c091e85%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

dougth...@hotmail.com

unread,
Jun 13, 2017, 7:01:26 AM6/13/17
to AtoM Users, dougth...@hotmail.com
Dan,

It is version 2.3.0 - 138.  It was an upgrade from an earlier version.   It was installed from the tarball on the download page.   I will try your suggestions and let you know how we make out.

Doug

dougth...@hotmail.com

unread,
Jun 13, 2017, 7:43:14 AM6/13/17
to AtoM Users, dougth...@hotmail.com
Dan,  

I had Jannah try again after running through your suggestions below.   She got the same error.   When I check the rights.i18n table it still doesn't have an IDENTIFIER_VALUE column.

Doug

On Monday, June 12, 2017 at 1:12:32 PM UTC-3, dougth...@hotmail.com wrote:

dougth...@hotmail.com

unread,
Jun 13, 2017, 9:22:31 AM6/13/17
to AtoM Users, dougth...@hotmail.com
Dan, 

I had a look at a file called lib.model.schema.sql and noticed that the rights_i18n table was missing three columns.   "identifier_value", "identifier_type", and "identifier_role".    I added these to columns to the database.    Did the php symfony cc and php symfony search:populate.   I then had Jannah retry what she was trying to do.   She didn't get the error message.   I'm not 100% sure what she was trying to do.   Below is what she sent to me.

AtoM allows for digitized objects to be linked to archival descriptions, and I was trying to test this out.  We don't publishing images in our databases if they are under copyright but we want to make the database record itself visible so it can be discoverable.  To allow a record to be displayed publicly but not the image, AND to allow the record and the image to be displayed to an authenticated user, set up the rights area as follows (substituting basis/copyright for whatever your restriction is): 


We are still running Atom 2.2 in production.   The above was done on a test instance running Atom 2.3.0 on Ubuntu 16.04.   I'll have a look at the Atom 2.2 database but I suspect the same column's are probably missing as well.

Doug
 

On Monday, June 12, 2017 at 1:12:32 PM UTC-3, dougth...@hotmail.com wrote:

Dan Gillean

unread,
Jun 13, 2017, 1:06:18 PM6/13/17
to ICA-AtoM Users
Hi Doug,

Interesting. I'm glad that you have gotten it working, but I am not sure why the columns are missing - I worry that it might mean that a previous installation or upgrade did not go well, or that there could be some corruption in the data that affects the database? I will ask our development team if they have any other ideas.

Regards,

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/e96dcca2-fced-4716-b32c-b294991ff48e%40googlegroups.com.

Dan Gillean

unread,
Jun 13, 2017, 1:13:11 PM6/13/17
to ICA-AtoM Users
One more thought:

You could try purging the current database, and then running the SQL upgrade command again - you could even try manually dropping and recreating the database.

Before you do any of this you want to make sure you have your data backed up, so you can reload it into the database again after.

Purging the current data - this DELETES EVERYTHING:
  • php symfony tools:purge

Dropping and recreating the database:

  • mysql -u root -proot -e 'drop database atom; create database atom character set utf8 collate utf8_unicode_ci;'

In the above example I have used root as the database user and pass, and atom as the database name - remember to make the appropriate changes based on what credentials you used during installation.

Now you could try running the sql-upgrade task again, reloading your sqldump, and re-indexing.


Cheers,


Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Tue, Jun 13, 2017 at 1:05 PM, Dan Gillean <d...@artefactual.com> wrote:
Hi Doug,

Interesting. I'm glad that you have gotten it working, but I am not sure why the columns are missing - I worry that it might mean that a previous installation or upgrade did not go well, or that there could be some corruption in the data that affects the database? I will ask our development team if they have any other ideas.

Regards,

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Tue, Jun 13, 2017 at 9:22 AM, <dougth...@hotmail.com> wrote:
Dan, 

I had a look at a file called lib.model.schema.sql and noticed that the rights_i18n table was missing three columns.   "identifier_value", "identifier_type", and "identifier_role".    I added these to columns to the database.    Did the php symfony cc and php symfony search:populate.   I then had Jannah retry what she was trying to do.   She didn't get the error message.   I'm not 100% sure what she was trying to do.   Below is what she sent to me.

AtoM allows for digitized objects to be linked to archival descriptions, and I was trying to test this out.  We don't publishing images in our databases if they are under copyright but we want to make the database record itself visible so it can be discoverable.  To allow a record to be displayed publicly but not the image, AND to allow the record and the image to be displayed to an authenticated user, set up the rights area as follows (substituting basis/copyright for whatever your restriction is): 


We are still running Atom 2.2 in production.   The above was done on a test instance running Atom 2.3.0 on Ubuntu 16.04.   I'll have a look at the Atom 2.2 database but I suspect the same column's are probably missing as well.

Doug
 

On Monday, June 12, 2017 at 1:12:32 PM UTC-3, dougth...@hotmail.com wrote:
User got the following error when trying to link a digitized object to an archival description in Atom 2.3.

500 | Internal Server Error | PropelException
[wrapped: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rights_i18n.IDENTIFIER_VALUE' in 'field list']


I had a look at the database and the rights.i18n table doesn't have that column.

Just wondering if any has had this issue before.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-ato...@googlegroups.com.

dougth...@hotmail.com

unread,
Jun 14, 2017, 1:01:04 PM6/14/17
to AtoM Users, dougth...@hotmail.com
Dan,

I checked back on our mysql database server and our version 2.1.2 database didn't have those three identifier columns in the rights_i18n table.    I guess the php symfony tools:upgrade-sql doesn't add columns if they don't exist.   I believe atom 2.1.2 was the first version we started using.    I didn't do the original install but did do the upgrades to 2.2.0 then to 2.2.1.    I have also installed 2.3.0 but it is in testing mode right now.   I added those three columns into the Atom 2.2.1 version Jannah is using in production and the error went away there as well.  I checked the table after Jannah tested it and those three columns all have a NULL value.   So it doesn't look like anything is actually stored in them.


On Monday, June 12, 2017 at 1:12:32 PM UTC-3, dougth...@hotmail.com wrote:

Dan Gillean

unread,
Jun 14, 2017, 1:06:22 PM6/14/17
to ICA-AtoM Users
Hi Doug,

Those columns don't exist in 2.1.2 because the functionality for the PREMIS rights module was added in 2.2, I believe. Normally, if you are following our recommended installation instructions, then you are installing a new AtoM instance alongside your old one, loading your data, and then running the sql upgrade task - which should add the necessary database columns for new fields, etc. It seems possible to me that the previous upgrade didn't quite go as expected? However, the schema version sounds right for 2.3.0 - it should be v138. Time to check with the developers!

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.

Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/525abf1f-7f72-4f00-96f4-305f9a23a649%40googlegroups.com.

Dan Gillean

unread,
Jun 14, 2017, 1:42:05 PM6/14/17
to ICA-AtoM Users
Hi again Doug,

I still think you should try purging your data (after backing it up), dropping and recreating the database, and then running the SQL-upgrade task again - I'm still hopeful it can resolve things.

Barring that, one of our developers has pointed me to the relevant migration in 2.2 - you might want to check out specifically lines 65-69 where the database is modified, to make sure your local changes conform to this.

Regards,


Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Wed, Jun 14, 2017 at 1:05 PM, Dan Gillean <d...@artefactual.com> wrote:
Hi Doug,

Those columns don't exist in 2.1.2 because the functionality for the PREMIS rights module was added in 2.2, I believe. Normally, if you are following our recommended installation instructions, then you are installing a new AtoM instance alongside your old one, loading your data, and then running the sql upgrade task - which should add the necessary database columns for new fields, etc. It seems possible to me that the previous upgrade didn't quite go as expected? However, the schema version sounds right for 2.3.0 - it should be v138. Time to check with the developers!

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Wed, Jun 14, 2017 at 1:01 PM, <dougth...@hotmail.com> wrote:
Dan,

I checked back on our mysql database server and our version 2.1.2 database didn't have those three identifier columns in the rights_i18n table.    I guess the php symfony tools:upgrade-sql doesn't add columns if they don't exist.   I believe atom 2.1.2 was the first version we started using.    I didn't do the original install but did do the upgrades to 2.2.0 then to 2.2.1.    I have also installed 2.3.0 but it is in testing mode right now.   I added those three columns into the Atom 2.2.1 version Jannah is using in production and the error went away there as well.  I checked the table after Jannah tested it and those three columns all have a NULL value.   So it doesn't look like anything is actually stored in them.


On Monday, June 12, 2017 at 1:12:32 PM UTC-3, dougth...@hotmail.com wrote:
User got the following error when trying to link a digitized object to an archival description in Atom 2.3.

500 | Internal Server Error | PropelException
[wrapped: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rights_i18n.IDENTIFIER_VALUE' in 'field list']


I had a look at the database and the rights.i18n table doesn't have that column.

Just wondering if any has had this issue before.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-ato...@googlegroups.com.

Doug Thomson

unread,
Jun 15, 2017, 7:24:30 AM6/15/17
to ica-ato...@googlegroups.com

Dan,


I had a look at the arMigration0113.class.php file.   The changes highlighted are basically what I did.   Instead of renaming the column I created the three columns mentioned.   It's funny because the 2.2.1 version we are running has those changes in that file.   As does the 2.3.0 version yet neither database had the columns added.   But the 2.2.1 and 2.3.0 databases do have the granted_right table which is created in the lines above those column changes in the php file.  But I guess the granted_right could have been created elsewhere.


Doug

From: ica-ato...@googlegroups.com <ica-ato...@googlegroups.com> on behalf of Dan Gillean <d...@artefactual.com>
Sent: June 14, 2017 2:41:43 PM
To: ICA-AtoM Users
Subject: Re: [ica-atom-users] Re: MIssing Column in Table
 
You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/wJvYASwBVEI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-user...@googlegroups.com.

To post to this group, send email to ica-ato...@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.

Dan Gillean

unread,
Jun 15, 2017, 11:56:21 AM6/15/17
to ICA-AtoM Users
Hi Doug,

Well... if everything is working as expected, and the changes you have manually implemented match the end product of the migration, then you should be okay?

I'm still a bit baffled why the migration didn't work for you. If you do try purging, recreating, and reloading again, let us know if it works this time.

Regards,

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Thu, Jun 15, 2017 at 7:24 AM, Doug Thomson <dougth...@hotmail.com> wrote:

Dan,


I had a look at the arMigration0113.class.php file.   The changes highlighted are basically what I did.   Instead of renaming the column I created the three columns mentioned.   It's funny because the 2.2.1 version we are running has those changes in that file.   As does the 2.3.0 version yet neither database had the columns added.   But the 2.2.1 and 2.3.0 databases do have the granted_right table which is created in the lines above those column changes in the php file.  But I guess the granted_right could have been created elsewhere.


Doug

To unsubscribe from this group and all its topics, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.

Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/DM5PR2201MB10811B9561EC7676D6FBDDDCBEC00%40DM5PR2201MB1081.namprd22.prod.outlook.com.

dougth...@hotmail.com

unread,
Jun 16, 2017, 10:55:04 AM6/16/17
to AtoM Users, dougth...@hotmail.com
Dan,  I may try the purge, recreate, reload option on our test system.

Just wanted to make sure I had the process right.   If I am going to drop and recreate the database do I need to do the php symfony tools:purge command?

If I do drop and recreate the database when do I do the php symfony tools:upgrade-sql command?   Before or After I reload the sql dump into the recreated database.   The upgrade instructions seem to tell me to do it after the reload of the data?

Doug

On Monday, June 12, 2017 at 1:12:32 PM UTC-3, dougth...@hotmail.com wrote:

Dan Gillean

unread,
Jun 16, 2017, 12:37:51 PM6/16/17
to ICA-AtoM Users
Hi Doug,

I will double-check with our developers to determine what is necessary vs. what is overkill and what the best order of operations is, but for now, I'll share what I do: I use our public Vagrant box for all testing, with a copy of our demo data which I load via a simple bash script. Whenever I'm resetting my environment, this is the order that the script runs all the tasks:

  1. Purge the database
  2. Remove the uploads directory
  3. Re-copy over the saved uploads directory
  4. Drop and recreate the database
  5. Load my saved sql-dump
  6. Run the upgrade-sql command
  7. Regenerate derivatives
  8. Clear the cache, re-index, and restart services
There might be some overkill in there (i.e. unecessary or repetitive steps), but this always works for me. For easy reference, below are the actual commands that my little bash script runs - you can change the paths depending on where you have your resources stored and make sure to use your database credentials, but this should give you an idea - it always works for me to reset my environment, which is especially useful when I am testing on a branch that includes a database migration.
  • php symfony tools:purge
  • rm -rf /usr/share/nginx/atom/uploads
  • rsync -av /vagrant/uploads/r /usr/share/nginx/atom/uploads
  • mysql -u root -proot -e 'drop database atom; create database atom character set utf8 collate utf8_unicode_ci;'
  • mysql -u root -proot atom < /vagrant/atom2_demo.sql
  • php symfony tools:upgrade-sql
  • php symfony digitalobject:regen-derivatives
  • php symfony cc
  • php symfony search:populate
  • sudo service php5-fpm restart
  • sudo service nginx restart
  • php symfony cc

The sql-upgrade task should definitely be run after you have loaded your database backup.

Cheers,

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/9ffac990-d550-4a98-96ec-b1efae792c90%40googlegroups.com.

Dan Gillean

unread,
Jun 16, 2017, 12:58:36 PM6/16/17
to ICA-AtoM Users
Hi again Doug,

I have confirmed that the following order is the best way to ensure the process proceeds smoothly:
  • Back up your data
  • Run the tools:purge command
  • Drop/recreate the database
  • Load your data
  • Run the sql-upgrade command

RE: restarting services - apparently restarting Nginx is not necessary. The command to restart PHP-FPM will also be different if you are running PHP7 on Ubuntu 16.04 - in that case, use:

  • sudo systemctl restart php7.0-fpm

Cheers,


Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

To post to this group, send email to ica-ato...@googlegroups.com.

Doug Thomson

unread,
Jun 19, 2017, 12:56:31 PM6/19/17
to ica-ato...@googlegroups.com

Dan,


I'm trying to get Atom 2.3.1 put on the server that we were using for 2.3.0.   The database is going onto a MySQL 5.6 server.    I've go the Atom 2.3.1 app copied over and it starts the install but after I put in the database information and the search settings I get a 

Unable to execute INSERT statement. [wrapped: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'culture' at row 1]

I googled the newsgroup and it mentions strict settings on MySQL 5.7.   I tried these even though its 5.6.   Then I noticed someone had increased the culture fields in three files under the data/sql folder.  This seemed to work for me as I got further.   I didn't get the error but I got a 500 Gateway timed out error.    But now I can't seem to get by the 1406 Data too long error no matter what I try.   I've dropped the database, flushed the cache, deleted the config.php file.     Any suggestions.

Doug

From: ica-ato...@googlegroups.com <ica-ato...@googlegroups.com> on behalf of Dan Gillean <d...@artefactual.com>
Sent: June 16, 2017 1:58:13 PM

To: ICA-AtoM Users
Subject: Re: [ica-atom-users] Re: MIssing Column in Table
You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/wJvYASwBVEI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-user...@googlegroups.com.

To post to this group, send email to ica-ato...@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.

Dan Gillean

unread,
Jun 19, 2017, 6:09:21 PM6/19/17
to ICA-AtoM Users
Hi Doug,

Our developers think that you were on the right track here - in fact maybe it sounds like the issue is already resolved? Apparently if strict mode is on, this is the kind of error you might encounter. In 5.6 the defaults are different from both 5.5 and 5.7 I believe, so the instructions for changing the settings might also be different.

If you run the following, you should be able to see the existing mode:
  • mysql -e "SELECT @@sql_mode;"

Because the defaults are different however, be cautious - an empty string may have a different meaning in 5.6 than 5.7!

Sorry, running out the door - hopefully the above will help, if not I will follow up more with our developers for you!




Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Mon, Jun 19, 2017 at 12:56 PM, Doug Thomson <dougth...@hotmail.com> wrote:

Dan,


I'm trying to get Atom 2.3.1 put on the server that we were using for 2.3.0.   The database is going onto a MySQL 5.6 server.    I've go the Atom 2.3.1 app copied over and it starts the install but after I put in the database information and the search settings I get a 

Unable to execute INSERT statement. [wrapped: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'culture' at row 1]

I googled the newsgroup and it mentions strict settings on MySQL 5.7.   I tried these even though its 5.6.   Then I noticed someone had increased the culture fields in three files under the data/sql folder.  This seemed to work for me as I got further.   I didn't get the error but I got a 500 Gateway timed out error.    But now I can't seem to get by the 1406 Data too long error no matter what I try.   I've dropped the database, flushed the cache, deleted the config.php file.     Any suggestions.

Doug

To unsubscribe from this group and all its topics, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.

Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/DM5PR2201MB10815228F5AD31C65E10EE49BEC40%40DM5PR2201MB1081.namprd22.prod.outlook.com.

Doug Thomson

unread,
Jun 20, 2017, 6:55:03 AM6/20/17
to ica-ato...@googlegroups.com

Dan,


Here is what I was able to determine.   If I install atom 2.3.1 from scratch.    I create the empty database and run through the install the install times out after selecting the search option.   But it does create the tables in the database and it does have the identifier columns in the rights_i18n table.   But if I drop the database, import my data from 2.3.0 and then run the php symfony tools:upgrade-sql it tells me 

>> upgrade-sql Successfully upgraded to Release 2.3.1 v138 but the three identifier columns aren't in the rights_i18n table.


So it looks like the tools:upgrade-sql doesn't actually check to make sure that table has the right columns.


Doug

From: ica-ato...@googlegroups.com <ica-ato...@googlegroups.com> on behalf of Dan Gillean <d...@artefactual.com>
Sent: June 19, 2017 7:08:59 PM
To unsubscribe from this group and all its topics, send an email to ica-atom-user...@googlegroups.com.

To post to this group, send email to ica-ato...@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.

Doug Thomson

unread,
Jun 20, 2017, 10:44:23 AM6/20/17
to ica-ato...@googlegroups.com

Dan,


Finally got that mysql strict table error fixed.   For some ungodly reason mysql on Ubuntu uses /usr/my.cnf instead of /etc/my.cnf.     The only entry in the /usr/my.cnf was for setting the SQL mode which was the default of STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION.    Even if I set it in the /etc/my.cnf it never got picked up.  I changed it in the /usr/my.cnf to "NO_ENGINE_SUBSTITUTION" and mysql picked it up right away and the  Atom 2.3.1 install sailed through.   Letting you know in case anyone else is having the same issue.


Doug

From: ica-ato...@googlegroups.com <ica-ato...@googlegroups.com> on behalf of Doug Thomson <dougth...@hotmail.com>
Sent: June 20, 2017 7:54:59 AM
To: ica-ato...@googlegroups.com

Dan Gillean

unread,
Jun 20, 2017, 11:30:30 AM6/20/17
to ICA-AtoM Users
Hi Doug,

That's great! And very useful to know. Thanks for sticking with it, and for letting us know how you finally solved this!

Regards,

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Tue, Jun 20, 2017 at 10:44 AM, Doug Thomson <dougth...@hotmail.com> wrote:

Dan,


Finally got that mysql strict table error fixed.   For some ungodly reason mysql on Ubuntu uses /usr/my.cnf instead of /etc/my.cnf.     The only entry in the /usr/my.cnf was for setting the SQL mode which was the default of STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION.    Even if I set it in the /etc/my.cnf it never got picked up.  I changed it in the /usr/my.cnf to "NO_ENGINE_SUBSTITUTION" and mysql picked it up right away and the  Atom 2.3.1 install sailed through.   Letting you know in case anyone else is having the same issue.


Doug


Sent: June 20, 2017 7:54:59 AM

Subject: Re: [ica-atom-users] Re: MIssing Column in Table

Dan,


Here is what I was able to determine.   If I install atom 2.3.1 from scratch.    I create the empty database and run through the install the install times out after selecting the search option.   But it does create the tables in the database and it does have the identifier columns in the rights_i18n table.   But if I drop the database, import my data from 2.3.0 and then run the php symfony tools:upgrade-sql it tells me 

>> upgrade-sql Successfully upgraded to Release 2.3.1 v138 but the three identifier columns aren't in the rights_i18n table.


So it looks like the tools:upgrade-sql doesn't actually check to make sure that table has the right columns.


Doug

From: ica-atom-users@googlegroups.com <ica-atom-users@googlegroups.com> on behalf of Dan Gillean <d...@artefactual.com>
Sent: June 19, 2017 7:08:59 PM
To: ICA-AtoM Users
Subject: Re: [ica-atom-users] Re: MIssing Column in Table
 
Hi Doug,

Our developers think that you were on the right track here - in fact maybe it sounds like the issue is already resolved? Apparently if strict mode is on, this is the kind of error you might encounter. In 5.6 the defaults are different from both 5.5 and 5.7 I believe, so the instructions for changing the settings might also be different.

If you run the following, you should be able to see the existing mode:
  • mysql -e "SELECT @@sql_mode;"

Because the defaults are different however, be cautious - an empty string may have a different meaning in 5.6 than 5.7!

Sorry, running out the door - hopefully the above will help, if not I will follow up more with our developers for you!




Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Mon, Jun 19, 2017 at 12:56 PM, Doug Thomson <dougth...@hotmail.com> wrote:

Dan,


I'm trying to get Atom 2.3.1 put on the server that we were using for 2.3.0.   The database is going onto a MySQL 5.6 server.    I've go the Atom 2.3.1 app copied over and it starts the install but after I put in the database information and the search settings I get a 

Unable to execute INSERT statement. [wrapped: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'culture' at row 1]

I googled the newsgroup and it mentions strict settings on MySQL 5.7.   I tried these even though its 5.6.   Then I noticed someone had increased the culture fields in three files under the data/sql folder.  This seemed to work for me as I got further.   I didn't get the error but I got a 500 Gateway timed out error.    But now I can't seem to get by the 1406 Data too long error no matter what I try.   I've dropped the database, flushed the cache, deleted the config.php file.     Any suggestions.

Doug

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-ato...@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.

--
You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/wJvYASwBVEI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.

--
You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/wJvYASwBVEI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-users+unsubscribe@googlegroups.com.
To post to this group, send email to ica-atom-users@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/DM5PR2201MB1081DEDC994F0AF61FDF568FBEC50%40DM5PR2201MB1081.namprd22.prod.outlook.com.

Doug Thomson

unread,
Jun 20, 2017, 11:57:33 AM6/20/17
to ica-ato...@googlegroups.com

No problem.   There is still the issue of the missing columns in the rights_i16n table.   Maybe you can check with your developers and see if the php symfony tools:upgrade-sql should be creating those tables if they don't exist?  I can get by with creating them manually but I'd like to make sure nothing else was missed.    The install of both 2.3.0 and 2.3.1 probably add the columns but when you drop the database to do the upgrade you probably lose those columns and since they didn't exist in your sql dump they aren't created during the sql restore.


Doug

From: ica-ato...@googlegroups.com <ica-ato...@googlegroups.com> on behalf of Dan Gillean <d...@artefactual.com>
Sent: June 20, 2017 12:30:06 PM
To unsubscribe from this group and all its topics, send an email to ica-atom-user...@googlegroups.com.

To post to this group, send email to ica-ato...@googlegroups.com.
Visit this group at https://groups.google.com/group/ica-atom-users.

sbr...@artefactual.com

unread,
Jun 20, 2017, 2:24:20 PM6/20/17
to ica-ato...@googlegroups.com, dougth...@hotmail.com
Hi Doug

Yes - you are correct: installing 2.3.1 would add the columns, but they would be subsequently removed during the drop and load of the prior version.

At this point I am unsure why those columns are failing to be added in that migration.  I can't remember if you stated this in an earlier message, but were there any error messages displayed during the sql-update task?  It is this task that should have added those columns.  Also, in the AtoM root directory there is a log folder (atom/log) and in there is the log file 'qubit_cli.log' - are there any messages in there after the sql-update task is performed?

In the migration file that Dan mentioned previously, there is a section above where the columns are added to the rights_i18n table that should add a new table 'granted_right'.  This happens here:


Is this table present in your post sql-updated database?

EDIT: After fixing the STRICT mode error you were experiencing - did you try performing the upgrade-sql with this fix in place?  I am wondering if with strict mode fixed, if the rights_i18n columns are added correctly?


Steve


On Tuesday, June 20, 2017 at 8:57:33 AM UTC-7, Doug Thomson wrote:

No problem.   There is still the issue of the missing columns in the rights_i16n table.   Maybe you can check with your developers and see if the php symfony tools:upgrade-sql should be creating those tables if they don't exist?  I can get by with creating them manually but I'd like to make sure nothing else was missed.    The install of both 2.3.0 and 2.3.1 probably add the columns but when you drop the database to do the upgrade you probably lose those columns and since they didn't exist in your sql dump they aren't created during the sql restore.


Doug

From: ica-ato...@googlegroups.com <ica-ato...@googlegroups.com> on behalf of Dan Gillean <d...@artefactual.com>
Sent: June 20, 2017 12:30:06 PM
To: ICA-AtoM Users
Subject: Re: [ica-atom-users] Re: MIssing Column in Table
 
Hi Doug,

That's great! And very useful to know. Thanks for sticking with it, and for letting us know how you finally solved this!

Regards,

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Tue, Jun 20, 2017 at 10:44 AM, Doug Thomson <dougth...@hotmail.com> wrote:

Dan,


Finally got that mysql strict table error fixed.   For some ungodly reason mysql on Ubuntu uses /usr/my.cnf instead of /etc/my.cnf.     The only entry in the /usr/my.cnf was for setting the SQL mode which was the default of STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION.    Even if I set it in the /etc/my.cnf it never got picked up.  I changed it in the /usr/my.cnf to "NO_ENGINE_SUBSTITUTION" and mysql picked it up right away and the  Atom 2.3.1 install sailed through.   Letting you know in case anyone else is having the same issue.


Doug


Sent: June 20, 2017 7:54:59 AM

Subject: Re: [ica-atom-users] Re: MIssing Column in Table

Dan,


Here is what I was able to determine.   If I install atom 2.3.1 from scratch.    I create the empty database and run through the install the install times out after selecting the search option.   But it does create the tables in the database and it does have the identifier columns in the rights_i18n table.   But if I drop the database, import my data from 2.3.0 and then run the php symfony tools:upgrade-sql it tells me 

>> upgrade-sql Successfully upgraded to Release 2.3.1 v138 but the three identifier columns aren't in the rights_i18n table.


So it looks like the tools:upgrade-sql doesn't actually check to make sure that table has the right columns.


Doug

Sent: June 19, 2017 7:08:59 PM
To: ICA-AtoM Users
Subject: Re: [ica-atom-users] Re: MIssing Column in Table
Hi Doug,

Our developers think that you were on the right track here - in fact maybe it sounds like the issue is already resolved? Apparently if strict mode is on, this is the kind of error you might encounter. In 5.6 the defaults are different from both 5.5 and 5.7 I believe, so the instructions for changing the settings might also be different.

If you run the following, you should be able to see the existing mode:
  • mysql -e "SELECT @@sql_mode;"

Because the defaults are different however, be cautious - an empty string may have a different meaning in 5.6 than 5.7!

Sorry, running out the door - hopefully the above will help, if not I will follow up more with our developers for you!




Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056
@accesstomemory

On Mon, Jun 19, 2017 at 12:56 PM, Doug Thomson <dougth...@hotmail.com> wrote:

Dan,


I'm trying to get Atom 2.3.1 put on the server that we were using for 2.3.0.   The database is going onto a MySQL 5.6 server.    I've go the Atom 2.3.1 app copied over and it starts the install but after I put in the database information and the search settings I get a 

Unable to execute INSERT statement. [wrapped: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'culture' at row 1]

I googled the newsgroup and it mentions strict settings on MySQL 5.7.   I tried these even though its 5.6.   Then I noticed someone had increased the culture fields in three files under the data/sql folder.  This seemed to work for me as I got further.   I didn't get the error but I got a 500 Gateway timed out error.    But now I can't seem to get by the 1406 Data too long error no matter what I try.   I've dropped the database, flushed the cache, deleted the config.php file.     Any suggestions.

Doug

To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To post to this group, send email to ica-at...@googlegroups.com.


--
You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/wJvYASwBVEI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-use...@googlegroups.com.
To post to this group, send email to ica-at...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To post to this group, send email to ica-at...@googlegroups.com.

--
You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/wJvYASwBVEI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-use...@googlegroups.com.
To post to this group, send email to ica-at...@googlegroups.com.

--
You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/wJvYASwBVEI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-use...@googlegroups.com.
To post to this group, send email to ica-at...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To post to this group, send email to ica-at...@googlegroups.com.
To view this discussion on the web visit