+++ This bug was initially created as a clone of Bug #831320 +++ Created attachment 591255 [details] Python Reproducer Script Description of problem: The main problem is that a when calling the method system.config.listFiles() it is taking too long to finish. During our troubleshooting, we had to increase the ProxyTimeout on mod_ajp to 1800 seconds. Afterwards we didn't hit the error 503 and the configuration files were displayed (after +- 8 minutes). We imported the customer database internally and were able to reproduce the same issue shown at the customer environment. Even updating the database schema and the indexes the behavior were kept the same. We wrote a Python script in order to reproducer the issue (see list.py attached on this mail). Below we can see query spent 7min and 54 seconds to render. $ ./list.py <snip> Systems Found: 297 Listing Config Files: abe (1000010239) ------------------------------------------------------------ Time Elapsed on system.config.listFiles(): 0:07:54.090957 Total Files: (373) path: /auto channel: Cindi Common CFMU path: /auto/data channel: Cindi Common CFMU path: /auto/home channel: Cindi Common CFMU path: /boot/grub/device.map channel: Cindi Server RHEL5 path: /boot/grub/grub.conf channel: Cindi Common RHEL5 Version-Release number of selected component (if applicable): spacewalk-java-1.2.39-120.el5sat spacewalk-schema-1.2.21-16.el5sat satellite-schema-5.4.0.19-1.el5sat How reproducible: 100% Steps to Reproduce: 1. connect on the reproducer <snip> 2. execute the query manually or execute the list.py reproducer script Actual results: SQL query is taking too long Expected results: SQL query runs within a reasonable time Additional info: Going further, we were able to reproduce the same behavior running the SQL manually directly on SQLPLUS (ModeFactory.getMode("config_queries", "file_names_for_system")); The results were displayed after 8 minutes and 16 seconds (after updated the DB indexes using db-control utility). SQL> set timing on; SQL> SELECT CFN.id, CR.id AS config_revision_id, CR.revision AS config_revision, CF.id AS config_file_id, CC.id AS config_channel_id, CC.name AS config_channel_name, CCT.label AS config_channel_type, CFT.label AS config_file_type, cc.label as config_channel_label, cr.modified as last_modified_date FROM rhnConfigRevision CR, rhnConfigFile CF, rhnConfigChannel CC, rhnConfigFileName CFN, rhnConfigChannelType CCT, rhnConfigFileType CFT WHERE CFN.id IN (SELECT DISTINCT CFN.id FROM rhnConfigFileName CFN, rhnServerConfigChannel SCC, rhnConfigFile CF, rhnUserServerPerms USP, rhnConfigChannel CC, rhnConfigChannelType CCT WHERE SCC.server_id = 1000010239 AND USP.server_id = SCC.server_id AND USP.user_id = 1 AND SCC.config_channel_id = CF.config_channel_id AND SCC.config_channel_id = CC.id AND CC.confchan_type_id = CCT.id AND CCT.label != 'server_import' AND CF.config_file_name_id = CFN.id ) AND CR.id = ( SELECT NVL( ( SELECT CR.id FROM rhnConfigChannel CC, rhnConfigChannelType CCT, rhnConfigRevision CR, rhnServerConfigChannel SCC, rhnConfigFile CF WHERE SCC.server_id = 1000010239 AND CF.config_channel_id = SCC.config_channel_id AND CF.config_file_name_id = CFNX.id AND SCC.config_channel_id = CC.id AND CC.confchan_type_id = CCT.id AND CCT.label = 'local_override' AND CF.latest_config_revision_id = CR.id) , ( SELECT CR.id FROM rhnConfigRevision CR, rhnServerConfigChannel SCC, rhnConfigFile CF WHERE CF.latest_config_revision_id = CR.id AND CF.config_file_name_id = CFNX.id AND CF.config_channel_id = SCC.config_channel_id AND SCC.server_id = 1000010239 AND SCC.position = ( SELECT min(SCC.position) FROM rhnServerConfigChannel SCC, rhnConfigFile CF WHERE SCC.server_id = 1000010239 AND CF.config_channel_id = SCC.config_channel_id AND CF.config_file_name_id = CFNX.id ) ) ) AS config_revision_id FROM rhnConfigFileName CFNX WHERE CFNX.id = CFN.id ) AND CR.id = CF.latest_config_revision_id AND CR.config_file_type_id = CFT.id AND CF.config_channel_id = CC.id AND CC.confchan_type_id = CCT.id; {....SNIP....} 373 rows selected. Elapsed: 00:08:16.04 --- Additional comment from sherr on 2012-06-19 10:20:45 EDT --- The problem was that the sub queries in the elaborator end up running many, many times. I have a simplified query that will return the information we want while only doing the necessary joins twice. The sub-query is necessary because if a file is in more than one config channel we are subscribed to we want to only list the one in the highest priority config channel. I will switch the api over to using this query. select cc.label as config_channel_label, cft.label AS config_file_type, cr.modified as last_modified_date, cc.name AS config_channel_name, cct.label AS config_channel_type, cfn.path from rhnConfigChannel cc, rhnConfigChannelType cct, rhnConfigFileType cft, rhnConfigRevision cr, rhnConfigFile cf, rhnConfigFileName cfn, rhnServerConfigChannel scc, (select cf.config_file_name_id as id, min(scc.position) as position FROM rhnServerConfigChannel SCC, rhnConfigFile CF, rhnUserServerPerms USP, rhnConfigChannel CC, rhnConfigChannelType CCT WHERE SCC.server_id = 1000010239 AND USP.server_id = SCC.server_id AND USP.user_id = 1 AND SCC.config_channel_id = CF.config_channel_id AND SCC.config_channel_id = CC.id AND CC.confchan_type_id = CCT.id AND CCT.label != 'server_import' group by cf.config_file_name_id) tmp where tmp.id = cfn.id and scc.position = tmp.position and scc.config_channel_id = cc.id and scc.server_id = 1000010239 and cc.confchan_type_id = cct.id and cf.latest_config_revision_id = cr.id and cf.config_channel_id = cc.id and cf.config_file_name_id = cfn.id and cr.config_file_type_id = cft.id order by cfn.path; 373 rows selected. Elapsed: 00:00:00.29
Pushed to spacewalk master: 6bba7b2a49e5aea7eab21533de5bb88cd41857ad
Created attachment 593097 [details] Additional Patch ORA-00911 Hello, Per notes on https://bugzilla.redhat.com/show_bug.cgi?id=831320#c11, this patch attached is also needed. 2012-06-19 18:30:19,107 [TP-Processor3] ERROR com.redhat.rhn.frontend.xmlrpc.BaseHandler - Error calling method: java.lang.reflect.InvocationTargetException at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60) { .... SNIP ... } at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:291) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:775) at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:704) at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:897) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685) at java.lang.Thread.run(Thread.java:736) Caused by: com.redhat.rhn.common.db.WrappedSQLException: ORA-00911: invalid character Best Regards, mmello
Ah, right you are mmello. Oops. I don't know how that snuck in there. Committed to spacewalk master: 67f8efb912dd4bfe7468e1c029addb98352aa2db
Also we need to take into account the possibility that the config channels priority is null, which it is for a server's individual config channel: 869eb498001de015249d0395529a172728d33954
Moving ON_QA. Packages that address this bugzilla should now be available in yum repos at http://yum.spacewalkproject.org/nightly/
Spacewalk 1.8 has been released: https://fedorahosted.org/spacewalk/wiki/ReleaseNotes18