MySQL error after update

[emis]

Hello,

I am quite new to OTRS and I have run into some SQL errors. I hope someone here can help.

Here is some background information on the system
We are migrating to new servers because we found we had a few issues with the old ones and the person who set them up has left the company. The old servers started at OTRS 2.1 and was upgraded with each release to 2.3. It is a 2-tier system that runs on CentOS. I have setup the new system using 2.4.7 on debian servers. We have 2 web interfaces (one internal and one customer facing) and 1 database server. So far things work beautifully except of the errors I have listed below (although we haven't run into any issues in our testing). We are of course using the old system until we feel the new one is stable and error free so if the fix requires a lot of messing with the DB I am not worried. The old system does not have the errors, but I did not check the new system after the install with the fresh DB. I only looked for these errors after I dumped the backup onto the new server and ran the update scripts.

Here is a cut of the syslog:
Fri Apr 30 10:26:31 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND st.until_time >= 1272643891 ORDER BY st.until_time ASC LIMIT 25' at line 1, SQL: 'SELECT DISTINCT st.id, st.tn, st.until_time FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.until_time >= 1272643891 ORDER BY st.until_time ASC LIMIT 25'
Fri Apr 30 10:26:27 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND tw.user_id IN (1) AND st.until_time <= 1272644727 LIMIT 10000' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq , ticket_watcher tw WHERE sq.id = st.queue_id AND st.id = tw.ticket_id AND st.ticket_state_id IN ( ) AND tw.user_id IN (1) AND st.until_time <= 1272644727 LIMIT 10000'
Fri Apr 30 10:26:27 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND st.responsible_user_id IN (1) AND st.until_time <= 1272644727 LIMIT 10000' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.responsible_user_id IN (1) AND st.until_time <= 1272644727 LIMIT 10000'
Fri Apr 30 10:26:27 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND st.queue_id IN (999999) AND st.until_time <= 1272644727 LIMIT 10000' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.queue_id IN (999999) AND st.until_time <= 1272644727 LIMIT 10000'
Fri Apr 30 10:26:27 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND st.ticket_lock_id IN (2) AND st.user_id IN (1) AND st.until_time <= 12726' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.ticket_lock_id IN (2) AND st.user_id IN (1) AND st.until_time <= 1272644727 LIMIT 10000'
Fri Apr 30 10:26:27 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND st.until_time <= 1272644727 LIMIT 10000' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.until_time <= 1272644727 LIMIT 10000'
Fri Apr 30 10:26:27 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND st.ticket_lock_id IN (2) AND st.user_id IN (1) AND st.until_time <= 12726' at line 1, SQL: 'SELECT DISTINCT st.id, st.tn, st.until_time FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.ticket_lock_id IN (2) AND st.user_id IN (1) AND st.until_time <= 1272644727 ORDER BY st.until_time DESC LIMIT 10'
Fri Apr 30 10:26:27 2010 notice OTRS-CGI-11 User: Admin authentication ok (REMOTE_ADDR: 10.0.0.14).
Fri Apr 30 10:24:35 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND st.user_id IN (27) LIMIT 1000' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.user_id IN (27) LIMIT 1000'
Fri Apr 30 10:24:35 2010 error OTRS-CGI-11 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND st.user_id IN (27) LIMIT 1000' at line 1, SQL: 'SELECT DISTINCT count(*) FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN ( ) AND st.user_id IN (27) LIMIT 1000'
Fri Apr 30 10:24:06 2010 notice OTRS-CGI-11 New Ticket [2010043011000012/testing] created (TicketID=7299,Queue=Technical Operations,Priority=2 High,State=open)

I also get these errors but I do not know if they are related:
Fri Apr 30 09:40:30 2010 error OTRS-CGI-11 No UserID found for 'root@localhost'!
Fri Apr 30 09:40:28 2010 error OTRS-CGI-11 The checks of Kernel::System::Support::Cron returned no data!

If anyone has any ideas please let me know. I fully expect this to be an issue from my doing the upgrade or the move to the new system. I wrote down all the steps because we needed work instructions for ISO therefore if you need to know what exactly I did I bet I can tell you with decent accuracy.

Thanks for the Help

[jojo]

please show the steps you did for migration..

[emis]

I used the instructions from our previous OTRS admin for the migration of the data itself. These are the steps I took:

-Pull a DB dump from last nights backup
-enter mysql console on new system
-drop database otrs
-create database otrs
-quit console
-mysql -u root -p otrs < backup.sql
-mysql -u root -p otrs <DBUpdate-to-2.4.mysql.sql
-/opt/otrs/bin/SetPermissions.sh /opt/otrs/ otrs www-data nogroup www-data
-perl /opt/otrs/scripts/DBUpdate-to-2.4.pl
-mysql -u root -p otrs <DBUpdate-to.2.4-post.sql

Once these steps were done I installed the support package and it stated that my tables were set to the wrong character set so I changed them with this:

ALTER DATABASE otrs DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;

As I said, this is a preprod box so taking a new cut of the data and nuking this cut is well within the realm of possibility. It is running on VMware and I took a snapshot after the install but before migrating the data so if needed I can go back to the point when the DB was fresh from install.

Thanks for the help so far

[alx]

Hello
Emis did you have resolve your problem ?
because i have the same error but not when i do an update just when i re-install otrs on a debian with an old data-base from the same version

thanks for help

[alx]

Finally i find a "solution"
i replace the config folder from kernel by the same folder from the installation package
now i don't have errors anymore but all that i have configurate on otrs is lost .

[kugaevsky]

The same problem.
Did anyone find another «solution»? I have many changes in config, so I don't want to lose it.