8000 Cannot import from CSV to postgresql · Issue #1947 · shlinkio/shlink · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Cannot import from CSV to postgresql #1947

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
Closed
tdashworth opened this issue Dec 16, 2023 · 6 comments · Fixed by #1948
Closed

Cannot import from CSV to postgresql #1947

tdashworth opened this issue Dec 16, 2023 · 6 comments · Fixed by #1948
Labels
bug
Milestone
3.7.1

Comments

@tdashworth
Copy link

Shlink version

3.7.0

PHP version

?

How do you serve Shlink

Openswoole Docker image

Database engine

PostgreSQL

Database version

16.1 (Debian 16.1-1.pgdg120+1)

Current behavior

After testing out with SQLite, I wanted to move over to postresql. I first exported the sqlite version to CSV (from the web app). Then, in docker-compose, I spun up a db container and linked the two with environment variables. Next, added a volume mount for the csv file and ran docker exec -it shlink-shlink-1 shlink short-url:import csv after answering the questions I get:

Importing short URLs
====================


In ExceptionConverter.php line 87:
                                                                                                                            
  An exception occurred while executing a query: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syn  
  tax for type bigint: ""                                                                                                   
  CONTEXT:  unnamed portal parameter $1 = ''                                                                                
                                                                                                                            

In Exception.php line 28:
                                                                                                    
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bigint: ""  
  CONTEXT:  unnamed portal parameter $1 = ''                                                        
                                                                                                    

In Statement.php line 130:
                                                                                                    
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bigint: ""  
  CONTEXT:  unnamed portal parameter $1 = ''

Docker Compose:

version: '3.0'

services:
  shlink:
    image: shlinkio/shlink:stable
    restart: unless-stopped
    networks:
      - cloudflared_proxy
      - default
    ports:
      - 8080:8080
    volumes:
      - ./backup.csv:/backup.csv:ro
    environment:
      DEFAULT_DOMAIN: [redacted]
      IS_HTTPS_ENABLED: true
      GEOLITE_LICENSE_KEY: ${GEOLITE_LICENSE_KEY}
      INITIAL_API_KEY: ${INITIAL_API_KEY}
      DB_DRIVER: postgres
      DB_NAME: shlink
      DB_USER: postgres
      DB_PASSWORD: ${DB_PASSWORD}
      DB_HOST: db

  db:
    image: postgres
    restart: unless-stopped
    networks:
      - default
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}

networks:
  cloudflared_proxy:
    name: cloudflared_proxy
    external: true
  default:
    driver: bridge

Expected behavior

The 15 or so links to be imported.

How to reproduce

  1. Step a standard docker instance.
  2. Add links
  3. Export links to csv
  4. Add postgresql db to docker instance
  5. Try to import csv via cli
@tdashworth tdashworth added the bug label Dec 16, 2023
@tdashworth
Copy link
8000 Author

I just tired importing back into SQLite - it worked.
I just tried importing into a new shlink (postresql) instance from the original (SQLite) instance - failed, same reason.

It seems like postgresql is the issue.

I also tested the postgresql instance by adding a link via the web app - it worked.

@acelaya
Copy link
Member
acelaya commented Dec 16, 2023

Thanks!

There are some variations on how stuff is mapped into native database types. I guess Postgres is being more strict about some specific value.

I think I should be able to reproduce with all the details you provided.

@acelaya acelaya added this to the 3.7.1 milestone Dec 16, 2023
@acelaya
Copy link
Member
acelaya commented Dec 16, 2023 •
edited
Loading

I have managed to reproduce it.

EDIT: As I thought, it's an instruction in particular which Postgres does not like, but the other engines do.

It doesn't look like a hard thing to address.

@acelaya acelaya mentioned this issue Dec 16, 2023
Merged
@acelaya acelaya moved this to In review in Shlink Dec 16, 2023
@github-project-automation github-project-automation bot moved this from In review to Done in Shlink Dec 16, 2023
@acelaya
Copy link
Member
acelaya commented Dec 16, 2023

This is now fixed. It will release with Shlink 3.7.1 before end of year.

@acelaya
Copy link
Member
acelaya commented Dec 17, 2023

I have just released v3.7.1.

Docker images will be available once this build finishes: https://github.com/shlinkio/shlink/actions/runs/7240461338

@tdashworth
Copy link
Author

Migration worked perfectly, thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
Shlink
Archived in project
Milestone
3.7.1
Development

Successfully merging a pull request may close this issue.

Fix error when importing short URLs while using Postgres acelaya-forks/shlink
2 participants
@acelaya @tdashworth
0