sqlite missing comments

Having trouble installing serendipity?
Post Reply
nealk
Regular
Posts: 26
Joined: Sun Sep 20, 2009 1:09 am

sqlite missing comments

Post by nealk » Thu Apr 09, 2015 9:27 pm

Hi,

I just migrated 1.7.8 from mysql to pdo-sqlite and now I'm not receiving comments.

select * from s_comments;
This returns all existing comments. And the comments show up on the web page.

But when a user submits a new comment... They see the HTML result that says the comment was successfully submitted. I receive an email that the comment was submitted and it contains the comment. But the comment is not found in s_comments and it is not listed for my approval on the admin/comments page.

Any idea how to fix this?

nealk
Regular
Posts: 26
Joined: Sun Sep 20, 2009 1:09 am

Re: sqlite missing comments

Post by nealk » Thu Apr 09, 2015 10:43 pm

Debugging include/functions_comments.inc.php:

Code: Select all

sqlite> INSERT INTO s_comments (entry_id, parent_id, ip, author, email, url, body, type, timestamp, title, subscribed, status, referer) VALUES ('667', '0', '50.xx.xx.xx', 'Test 2', 'test@hackerfactor.com', '', 'Test2', 'NORMAL', '1428612060', '', 'false', 'pending', 'http://www.hackerfactor.com/blog/serendipity_admin.php?serendipity[adminModule]=plugins&serendipity[plugin_to_conf]=serendipity_event_spamblock%3A068c3b8e86d3bb3f9d53e0e5542af298');
Error: NOT NULL constraint failed: s_comments.id
Any idea why, under pdo-sqlite, s_comments.id is null?

Also, include/functions_comments.inc.php does not catch the error case where the insert failed.

User avatar
onli
Regular
Posts: 2217
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: sqlite missing comments

Post by onli » Thu Apr 09, 2015 11:40 pm

Did you create the sqlite database based on a mysql-dump by using a converter skript? For me that error looks like the the autoincrement is missing. This is how the table looks in my testblog:

Code: Select all

sqlite> .schema serendipity_comments
CREATE TABLE serendipity_comments (id INTEGER PRIMARY KEY AUTOINCREMENT,entry_id int(10)  not null default '0',parent_id int(10)  not null default '0',timestamp int(10)  default null,title varchar(150) default null,author varchar(80) default null,email varchar(200) default null,url varchar(200) default null,ip varchar(64) default null,body LONGTEXT,type varchar(100) default 'regular',subscribed BOOLEAN NOT NULL,status varchar(50) not null,referer varchar(200) default null);
CREATE INDEX commentry_idx ON serendipity_comments (entry_id);
CREATE INDEX commpentry_idx ON serendipity_comments (parent_id);
CREATE INDEX commtype_idx ON serendipity_comments (type);
CREATE INDEX commstat_idx ON serendipity_comments (status);
nealk wrote:Also, include/functions_comments.inc.php does not catch the error case where the insert failed
That would be a valid bug I bet applies as well to s9y 2. Would you be so kind and open an issue for that on github, under https://github.com/s9y/Serendipity/issues?

nealk
Regular
Posts: 26
Joined: Sun Sep 20, 2009 1:09 am

Re: sqlite missing comments

Post by nealk » Fri Apr 10, 2015 2:03 am

onli wrote:Did you create the sqlite database based on a mysql-dump by using a converter skript? For me that error looks like the the autoincrement is missing. This is how the table looks in my testblog:
Oh rats... That's the problem. I used one of those "almost good" conversion scripts.

Alright.. Time for Plan C...
Is there an easy way to reset the database? I want a completely empty (minimal) database with all of the tables built. Then I'll just import the INSERT statements.

nealk
Regular
Posts: 26
Joined: Sun Sep 20, 2009 1:09 am

Re: sqlite missing comments

Post by nealk » Fri Apr 10, 2015 5:55 am

Yee haw! Got it fixed and working.

My solution for porting from MySQL to sqlite:

1. On a completely separate (non-production) instance, do a clean install of s9y. Set the database to be odp-sqlite. This will create the database template with all of the necessary tables properly configured. Copy the database to "template.db".

2. Export the SQL from MySQL. Make sure to ONLY export the INSERT rules; not the table creation, truncation. I called it "dump.sql".

3. Edit the SQL export. Change every "INSERT" to "INSERT OR REPLACE".
Replace every "\n" with a real newline.
Remove every "\r".

4. Insert the data into the new database.
cat dump.sql | sqlite3 template.db
This will copy over all of the data. If you have a lot of data, this may take a while. (Mine took an hour to complete.)

5. Replace your database with template.db. Now everything is there and ported and the tables are configured correctly.

User avatar
onli
Regular
Posts: 2217
Joined: Tue Sep 09, 2008 10:04 pm
Contact:

Re: sqlite missing comments

Post by onli » Fri Apr 10, 2015 1:22 pm

I'm happy you got this fixed :)

Post Reply