Database Error: Unknown column 'submission_id' in 'where clause'

Chrizze · August 20, 2018, 1:04pm

I recently moved my OJS installation to a public server. These are the steps I took.

Fresh installation of OJS
Transferred database from local to public
Copied local uploads folder to public server

I then manually replaced missing logo image and cover pic for a published issue. But when clicking on a article within the published issue, I get an an error. And I’m not sure what it means exactly. The column do exist in the submission table.

Error message:

DB Error: Unknown column ‘submission_id’ in ‘where clause’

Stack Trace:

File: /home/christerjo/domains/christerjohansson.net/public_html/hbojs/lib/pkp/classes/db/DAO.inc.php line 200
Function: DAO->retrieve(“SELECT * FROM citations WHERE submission_id = ? ORDER B…”, Array(1), False)

File: /home/christerjo/domains/christerjohansson.net/public_html/hbojs/lib/pkp/classes/citation/CitationDAO.inc.php line 124
Function: DAO->retrieveRange(“SELECT * FROM citations WHERE submission_id = ? ORDER B…”, Array(1), Null)

File: /home/christerjo/domains/christerjohansson.net/public_html/hbojs/pages/article/ArticleHandler.inc.php line 157
Function: CitationDAO->getBySubmissionId(“3”)

File: (unknown) line (unknown)
Function: ArticleHandler->view(Array(2), Object(Request))

File: /home/christerjo/domains/christerjohansson.net/public_html/hbojs/lib/pkp/classes/core/PKPRouter.inc.php line 372
Function: call_user_func(Array(2), Array(2), Object(Request))

File: /home/christerjo/domains/christerjohansson.net/public_html/hbojs/lib/pkp/classes/core/PKPPageRouter.inc.php line 233
Function: PKPRouter->_authorizeInitializeAndCallRequest(Array(2), Object(Request), Array(2), False)

File: /home/christerjo/domains/christerjohansson.net/public_html/hbojs/lib/pkp/classes/core/Dispatcher.inc.php line 134
Function: PKPPageRouter->route(Object(Request))

File: /home/christerjo/domains/christerjohansson.net/public_html/hbojs/lib/pkp/classes/core/PKPApplication.inc.php line 247
Function: Dispatcher->dispatch(Object(Request))

File: /home/christerjo/domains/christerjohansson.net/public_html/hbojs/index.php line 68
Function: PKPApplication->execute()

ajnyga · August 20, 2018, 3:06pm

what do you have in config.inc.php here: https://github.com/pkp/ojs/blob/master/config.TEMPLATE.inc.php#L125

Chrizze · August 20, 2018, 4:11pm

As my host uses MariaDB, I’ve set driver = mysqli
My localhost however, uses mysql, as it is a mysql-db.

How do I solve that?

ajnyga · August 20, 2018, 4:23pm

this is a bit weird error to begin with. I would probably just test what happens when you set that to mysql instead of mysqli. Maybe nothing.

Also, are you sure your newly installed OJS is the same version (3.1.0.1) as with you localhost? Did you try using the same OJS installation files you have on your localhost? I meant that the database and the OJS installation are not from different versions.

(also not related to this error, but some of the files are stored to the public folder, so usually when you move an installation that folder should be copied as well)

Chrizze · August 21, 2018, 3:24pm

Well, I don’t know if these will help. First image is from my local installation, and the second is my public installation. Not sure if an update to the absolute last version would help in this case?

Also, I moved over files from local to public, and the “files” folder outside as well. The error is related to DB, since it complains about not seeing a column that exists. I’ve never had this issue before, and it is a bit worrysome at this stage of a project, right before we populate the site with 1000’s of articles and data.

Local installation - System information
OJS_Local_Install

Public installation - System information
OJS_Public_Install

ajnyga · August 21, 2018, 3:44pm

Hi,

That page will show the version that is stored in the database versions table.

What I meant was that when you say you did a fresh installation of OJS to the new server and then copied the database, did you use the same version as on your localhost (3.1.0.1)? I mean if you used the files from version 3.1.1.2 and the uploaded the database (that was used with 3.1.0.1), that could cause problems.

Chrizze · August 22, 2018, 3:13pm

Just to be sure about this procedure, could you please explain to me how to properly move a OJS installation to a new server?

The fresh installation I used in my step was taken directly from pkp website, for the selected version. I then installed it as usual, and the merely transferred my old database over the new. After that I copied the files in public and the files-folder outside the public web-folder.

What is the correct procedure to move an existing installation of OJS?

ajnyga · August 22, 2018, 3:46pm

Are you sure the files you downloaded are for 3.1.0.1 and not for 3.1.1?

Because looking at the stack trace in your first post, it makes a refenrence to line 124 in the CitationDAO.

That matches the content of that file in 3.1.1: pkp-lib/CitationDAO.inc.php at ojs-stable-3_1_1 · pkp/pkp-lib · GitHub

But it does not match the content of the same file in 3.1.0: pkp-lib/CitationDAO.inc.php at ojs-stable-3_1_0 · pkp/pkp-lib · GitHub

So it does seem that the OJS files you have in the online server are from 3.1.1.

So I suggest you try to copy the whole OJS installation from you test server to make sure the database version and the OJS version match.

ajnyga · August 22, 2018, 3:48pm

that’s really the only possible solution I can see, maybe @asmecher has other ideas?

asmecher · August 23, 2018, 12:36am

Hi all,

I think @ajnyga’s suspicion is correct – the OJS code you’ve uploaded to the server isn’t the same as the code you’re using locally. The best way to check the code version (which can be different from the database version you see in the administration interface) is to look at dbscripts/xml/version.xml.

If it’s possible that you’ve mixed two versions together – e.g. copying a new set of files into an existing OJS directory – things can get very confused, so even the dbscripts/xml/version.xml descriptor can be untrustworthy!

Regards,
Alec Smecher
Public Knowledge Project Team

Chrizze · October 29, 2018, 9:55am

Yes, the suspicion was correct. I had mistaken the version of files, and used the incorrect version. In the end I re-installed OJS completely with the latest version, and it is now working. I will create another thread on my other issue.

Thank you for pointing this out, and helping a poor developer out.