COmanage Developers List

[Benn Oshrin <>]

I started writing some documentation to explain this, but now I'm 
wondering if we should instead just put the SQL into the upgradeVersion 
command. There is already precedent for executing raw SQL (DROP VIEW), 
and this would simplify the upgrading process.

The slightly tricky part is the SQL to alter a column is different 
between Postgres and MySQL, but we can detect if we're using Postgres 
and just run the SQL there.

If this seems reasonable, can you open a JIRA?

Thanks,

-Benn-

On 12/2/19 2:51 PM, Scott Koranda wrote:
> Hi,
>
> I am upgrading from version 3.2.x to 3.3.0 (head of develop) where
> PostgreSQL 9.6.12 is the database.
>
> If I follow the instructions at
>
> https://spaces.at.internet2.edu/display/COmanage/Upgrading+Registry
>
> precisely then the
>
> upgradeVersion
>
> command will not run to completion because of the (documented) problem
> with the 'type' column in the table 'cm_ssh_keys'. I see this:
>
> Error: SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too 
> long for type character varying(16)
> #0 /srv/comanage-registry/lib/Cake/Model/Datasource/DboSource.php(489): 
> PDOStatement->execute(Array)
> #1 /srv/comanage-registry/lib/Cake/Model/Datasource/DboSource.php(455): 
> DboSource->_execute('UPDATE "public"...', Array)
> #2 /srv/comanage-registry/lib/Cake/Model/Datasource/DboSource.php(2168): 
> DboSource->execute('UPDATE "public"...')
> #3 /srv/comanage-registry/lib/Cake/Model/Model.php(2672): DboSource->update(Object(SshKey), 
> '"type" = 'ecdsa...', NULL, ' WHERE "type" =...')
> #4 
> /srv/comanage-registry/app/Plugin/SshKeyAuthenticator/Model/SshKeyAuthenticator.php(241):
>  Model->updateAll(Array, Array)
> #5 /srv/comanage-registry/app/Console/Command/UpgradeVersionShell.php(402): 
> SshKeyAuthenticator->_ug330(2)
> #6 /srv/comanage-registry/app/Console/Command/UpgradeVersionShell.php(279): 
> UpgradeVersionShell->post330()
> #7 /srv/comanage-registry/lib/Cake/Console/Shell.php(462): 
> UpgradeVersionShell->main()
> #8 /srv/comanage-registry/lib/Cake/Console/ShellDispatcher.php(219): 
> Shell->runCommand('-f', Array)
> #9 /srv/comanage-registry/lib/Cake/Console/ShellDispatcher.php(66): 
> ShellDispatcher->dispatch()
> #10 /srv/comanage-registry/app/Console/cake.php(41): 
> ShellDispatcher::run(Array)
> #11 {main}
>
> I read at
>
> https://spaces.at.internet2.edu/display/COmanage/Upgrading+Registry+to+v3.3.0
>
> that I need to run (along with other command)
>
> alter table cm_ssh_keys alter column type type varchar(32);
>
> But the documentation to do that comes *after* the documentation to run
> upgradeVersion.
>
> Since we know upgradeVersion is going to fail for a good number of
> deployments that use PostgreSQL, shouldn't the documentation for
> upgrading explain updating the column before running upgradeVersion?
>
> Thanks,
>
> Scott