Server Version#: 1.23.1.4528
Player Version#: Web 4.57.2
I’ve made a fresh install of Plex Media Server. I moved from one server Ubuntu Server 18.04 to another Ubuntu Server 20.04lts, and I’ve followed these instructions (Move Viewstate/Ratings from One Install to Another | Plex Support) to move just the Viewstats, but it failed:
Error: near line 3: UNIQUE constraint failed: metadata_item_settings.id
Error: near line 4: UNIQUE constraint failed: metadata_item_settings.id
Error: near line 5: UNIQUE constraint failed: metadata_item_settings.id
Error: near line 6: UNIQUE constraint failed: metadata_item_settings.id
etc
etc
Any ideas?
Yes…
PMS no longer can use distribution SQLITE3.
I don’t know when the documentation should have been updated but it’s been this way for a few versions now.
the solution is simple.
Instead of typing sqliite3
type "/usr/lib/plexmediaserver/Plex Media Server --sqlite"
This invokes PMS in “SQLite shell” mode. It gives you all the SQLite plug-ins which PMS uses.
Sorry for being so “Basic”, but this is how I should run it?
Source:
echo ".dump metadata_item_settings" | "/usr/lib/plexmediaserver/Plex Media Server --sqlite" com.plexapp.plugins.library.db | grep -v TABLE | grep -v INDEX > settings.sql
Destination:
cat settings.sql | "/usr/lib/plexmediaserver/Plex Media Server --sqlite" com.plexapp.plugins.library.db
If you’re in /var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases
Then yes. that’s it with one correction
"/usr/lib/plexmediaserver/Plex Media Server" --sqlite
you don’t want to glob the --sqlite option into ‘arg[0]’. that needs to be arg 1
I just did it, but I’m still getting the same issue 
root@furflix:/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases# cat settings.sql | "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db
Error: near line 3: UNIQUE constraint failed: metadata_item_settings.id
Error: near line 4: UNIQUE constraint failed: metadata_item_settings.id
Error: near line 5: UNIQUE constraint failed: metadata_item_settings.id
Error: near line 6: UNIQUE constraint failed: metadata_item_settings.id
Error: near line 7: UNIQUE constraint failed: metadata_item_settings.id
...
...
...
I asked & learned. I apologize. I didn’t know this
The new server already has viewstates. SQLite3 won’t let you mash the two tables together the way it’s trying to do.
You need to “.drop metadata_items_settings” in the new DB.
I’m sorry Chuck, not sure what do you mean by "“.drop metadata_items_settings” in the new DB. Do you mind to explain me as if I am 5?
- Open the new
com.plexapp.plugins.library.db (on the system you’re moving the data to)
sudo bash
cd "/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases"
"/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db
.drop metadata_items_settings
.quit
Now you can do the cat | step
Is it possible that my sqlite has not .drop option?
root@furflix:/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .help
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump ?OBJECTS? Render database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in spreadsheet
.exit ?CODE? Exit this program with return-code CODE
.expert EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
.filectrl CMD ... Run various sqlite3_file_control() operations
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help ?-all? ?PATTERN? Show help text for PATTERN
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of indexes
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues.
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode
.nullvalue STRING Use STRING in place of NULL values
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdout if FILE is omitted
.parameter CMD ... Manage SQL parameter bindings
.print STRING... Print literal STRING
.progress N Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILE Read input from FILE
.recover Recover as much data as possible from corrupt db.
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.selftest ?OPTIONS? Run tests defined in the SELFTEST table
.separator COL ?ROW? Change the column and row separators
.sha3sum ... Compute a SHA3 hash of database content
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats ?ARG? Show stats or turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
.testcase NAME Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ... Run various sqlite3_test_control() operations
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace ?OPTIONS? Output each SQL statement as it is run
.vfsinfo ?AUX? Information about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set minimum column widths for columnar output
sqlite> .drop metadata_items_settings
Error: unknown command or invalid arguments: "drop". Enter ".help" for help
sqlite>
drop metadata_items_settings;
No dot.
I’m sorry for being stup*d, but I’m doing something wrong and I don’t know what:
root@furflix:/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> drop metadata_items_settings
...> .quit
...> .quit
...> quit
...>
After drop, it’s doing nothing when I put .quit, neither quit (no dot).
you forgot the EOL (the semicolon)
I’m gonna die 
root@furflix:/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> drop metadata_items_settings;
Error: near "metadata_items_settings": syntax error
sqlite>
Sorry for bothering you that much
NO WORRIES!
I’m not a database guy. I’m the OS guy (desktop & NAS).
@anon18523487 is guru with this.
He had to step out for a minute but will be back shortly and jump in to bail us both out
While waiting, I think I need swimming lessons 
1 Like
Three cheers for @anon18523487 and @ChuckPa — hip, hip, hurray !
furflix getting in panic in 5… 4…
@anon18523487 please don’t forget about me 
Hey there! Anybody is out there? I really need help with this and I cant find anything on internet
Hi Chuck, do you know something about MovieFan? Do you think he will be able to help me today?
Sorry, got tied up with something else. Instead of drop, let’s try this command.
delete * from metadata_item_settings;
That will just delete the data instead of removing the entire table. Then use this to confirm the delete worked.
select count(id) from metadata_item_settings;
You should get a 0.
Then continue with the import command.
