Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Stratos DB Migration fails on Postgres #4736

Closed
2 of 9 tasks
ukleeberger opened this issue Nov 2, 2020 · 2 comments · Fixed by #4738
Closed
2 of 9 tasks

Stratos DB Migration fails on Postgres #4736

ukleeberger opened this issue Nov 2, 2020 · 2 comments · Fixed by #4738
Assignees
@ikapelyukhin
Labels
bug community Community Raised Issue
Milestone
4.3.0

Comments

@ukleeberger
Copy link

Stratos Version

4.2

Frontend Deployment type

  • Cloud Foundry Application (cf push)
  • Kubernetes, using a helm chart
  • Docker, single container deploying all components
  • npm run start
  • Other (please specify below)

Backend (Jet Stream) Deployment type

  • Cloud Foundry Application (cf push)
  • Kubernetes, using a helm chart
  • Docker, single container deploying all components
  • Other (please specify below)

Actual behaviour

After upgrading from Version 4.1.0 to 4.2.x the Deployment fails while performing the 20201005105400_AnalysisUpstream script with the following error : Apply() failed:pq: syntax error at or near "user"

Steps to reproduce the behavior

We are using PostgreSQL 11.7

Log output covering before error and any error statements

2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] ========================================     
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] === Stratos Jetstream Backend Server ===     
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] ========================================     
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020]                                              
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Initialization started.                      
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Configuration loaded.                        
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Stratos Version: 4.2.0                       
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] HTTP client initialized.                     
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Encryption key set.                          
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Found 1 database service instances           
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Using first database service instance: console_db 
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Attempting to apply Cloud Foundry database service config from VCAP_SERVICES credentials 
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Applied Cloud Foundry database service config (provider: pgsql) 
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Using Cloud Foundry DB service               
   2020-11-02T08:00:02.08+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] DB Connection string: dbname='console_db' host='10.180.11.127' port=31445 connect_timeout=10 
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Database appears to now be available.        
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Database connection pool created.            
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping DB migration => not index 0 (0)     
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT WARN[Mon Nov  2 07:00:02 UTC 2020] When running in production, ensure you set SESSION_STORE_SECRET to a secure value 
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] ========================                     
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] = Stratos DB Migration =                     
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] ========================                     
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Database provider: postgres                  
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Current 20200902162200                       
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Target: 20201007113503                       
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Running migrations ....                      
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20170818120003           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20170818162837           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20170829154900           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20171108102900           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20180413135700           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20180627111300           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20180703142800           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20180813110300           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20180824092600           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20180831104300           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20180907123000           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20181129140500           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20190305144600           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20190307115301           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20190515133200           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20190522121200           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20190621212700           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20190918092300           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20190930092500           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20191008121900           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20200117152200           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20200206090600           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20200210105400           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20200814140918           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20200819184800           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Skipping migration: 20200902162200           
   2020-11-02T08:00:02.09+0100 [APP/PROC/WEB/0] OUT INFO[Mon Nov  2 07:00:02 UTC 2020] Running migration: 20201005105400_AnalysisUpstream 
   2020-11-02T08:00:02.10+0100 [APP/PROC/WEB/0] OUT ERRO[Mon Nov  2 07:00:02 UTC 2020] Apply() failed:pq: syntax error at or near "user" 
   2020-11-02T08:00:02.10+0100 [APP/PROC/WEB/0] OUT FATA[Mon Nov  2 07:00:02 UTC 2020] pq: syntax error at or near "user"           
   2020-11-02T08:00:02.15+0100 [APP/PROC/WEB/0] OUT Exit status 1

Possible Implementation

I think the problem is using a column named "user" in postgres. Following this advice https://stackoverflow.com/a/10891404 escaping the user column sholud solve the problem.
@ikapelyukhin ikapelyukhin self-assigned this Nov 2, 2020
@ikapelyukhin ikapelyukhin mentioned this issue Nov 2, 2020
Merged
@ikapelyukhin
Copy link
Contributor

I think the problem is using a column named "user" in postgres.

This is indeed the case, thanks for reporting this.

Following this advice https://stackoverflow.com/a/10891404 escaping the user column sholud solve the problem.

Escaping it with double quotes works for Postgres, but sadly doesn't work for MySQL. Renaming the column is probably the way to go.

@richard-cox richard-cox added the community Community Raised Issue label Nov 2, 2020
@richard-cox richard-cox added this to the 4.3.0 milestone Nov 2, 2020
@ikapelyukhin
Copy link
Contributor

@ukleeberger the fix will come out with 4.3.0 release next week.

@richard-cox richard-cox added the bug label Nov 4, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ikapelyukhin ikapelyukhin

Labels
bug community Community Raised Issue
Projects
None yet
Milestone
4.3.0
Development

Successfully merging a pull request may close this issue.

Fix analysis table migration errors on Postgres cloudfoundry/stratos
3 participants
@ikapelyukhin @ukleeberger @richard-cox