DB Error: You have an error in your SQL syntax near 'USING

[Jordi Prats]

Dear colleagues,

I have a problem when I try to collect metadata of a repository.

When I have the first collect operation the system runs ok, but when I execute /tools/harvest.php 1, I receive the following error message:

Selected archive: Universitat Politècnica de Catalunya - DSpace.E-prints
Not flushing an existing 128 records.
Fetching records...
<h1>DB Error: You have an error in your SQL syntax near 'USING entries, entry_attributes WHERE entries.record_id = '1' AND entry_attribut' at line 1</h1>Harvester2: DB Error: You have an error in your SQL syntax near 'USING entries, entry_attributes WHERE entries.record_id = '1' AND entry_attribut' at line 1

It happens to me the same when I try to execute flush metadata.

I have a Metadata Collector in a system:

OS platform Linux
PHP version 4.3.11
Apache version Apache/2.0.49 (Unix) mod_perl/1.99_17 Perl/v5.8.5 mod_ssl/2.0.49 OpenSSL/0.9.7e PHP/4.3.11 mod_jk/1.2.6
Database driver mysql
Database server version 3.23.58

Thanks in advance for your help,

[asmecher]

Hi Jordi,

The SQL statement that is causing the problem is in classes/record/RecordDAO.inc.php, in the deleteEntriesByRecordId function:

DELETE FROM entry_attributes USING entries, entry_attributes WHERE entries.record_id = ? AND entry_attributes.entry_id = entries.entry_id

I suspect the version of MySQL you're using is too old to support this kind of delete operation. I'd suggest upgrading your MySQL version; if this isn't possible, it's easy to re-implement this function to use a simpler delete. However, be warned that delete performance might be extremely poor with a simpler delete.

Regards,
Alec Smecher
Public Knowledge Project Team

[Jordi Prats]

Hi Alec

Thank you very much for your "fast" answer.

We'll try to update our MySQL, but meanwhile we have been able to solve the problem.