-
1. Re: Howto move jbm and quartz tables to postgresql schema?
How many *-ds.xml files do you have? If only one, what is the jndi name within it? Is it DeafultDS?
I usually recommend that you retain DefaultDS for the JBoss services and use a second data source (in a second *-ds.xml) for your databases.
For postgresql-persistence.xml, you need to make two changes: set "Clustered" to false (unless you are running the 'all' configuration), and comment out the depends on ChannelFactoryName.
The only other thing needed to be done is change the SQL statements for Quartz to be valid for PostgreSQL, and it looks like you did that.
Post the contents of your *-ds.xml files. Remember to enclose the XML text with 'code' tags (select the text, click the Code button above the editor window). -
2. Re: Howto move jbm and quartz tables to postgresql schema?
At first, the files can be seen below.
And yes, it sounds like a good idea to have 2 -ds.xml files, but still, I do not understand how to make the setup. To clarify a little bit more:
There is only one -ds.xml (postgres-ds.xml) file. I used the one from the "docs/examples/jca" folder. I changed PostgresDS to DefaultDS and "PostgreSQL 7.2" to "PostgreSQL" in the type-mapping att..
postgresql-persistence-service.xml:
I got the file from jboss-messaging-1.4.2.GA.zip. It is located in:
jboss-messaging-1.4.2.GA/examples/config
"Clustered" is set to false (Im using default).
"ChannelFactoryName" does not appear in the file (hmm).
ejb-timer-service.xml:
As said, I found it at jira.jboss and it seems to work (no Quartz table errors).
The link is:
https://jira.jboss.org/jira/browse/EJBTHREE-1432;jsessionid=F801E89A959EDC267D23E273F4A0ED8A?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel
Thanks.
/nikolaj
And the files:
----------------------------------------------------------------------------
postgres-dx.xml:<local-tx-datasource> <jndi-name>DefaultDS</jndi-name> <connection-url>jdbc:postgresql://127.0.0.1:5432/mydb</connection-url> <driver-class>org.postgresql.Driver</driver-class> <user-name>mydb_user</user-name> <password>test1234</password> <metadata> <type-mapping>PostgreSQL</type-mapping> </metadata> </local-tx-datasource> </datasources>
----------------------------------------------------------------------------
postgresql-persistence-service.xml:<?xml version="1.0" encoding="UTF-8"?> <!-- Postgresql persistence deployment descriptor. Tested with PostgresSQL 8.2.3 $Id$ --> <server> <!-- Persistence Manager MBean configuration ======================================== --> <mbean code="org.jboss.messaging.core.jmx.JDBCPersistenceManagerService" name="jboss.messaging:service=PersistenceManager" xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml"> <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends> <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends> <!-- The datasource to use for the persistence manager --> <attribute name="DataSource">java:/DefaultDS</attribute> <!-- If true will attempt to create tables and indexes on every start-up --> <attribute name="CreateTablesOnStartup">true</attribute> <!-- If true then we will automatically detect and reject duplicate messages sent during failover --> <attribute name="DetectDuplicates">true</attribute> <!-- The size of the id cache to use when detecting duplicate messages --> <attribute name="IDCacheSize">500</attribute> <attribute name="SqlProperties"><![CDATA[ CREATE_DUAL=CREATE TABLE JBM_DUAL (DUMMY INTEGER, PRIMARY KEY (DUMMY)) CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (MESSAGE_ID BIGINT, CHANNEL_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INTEGER, SCHED_DELIVERY BIGINT, PRIMARY KEY(MESSAGE_ID, CHANNEL_ID)) CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID, STATE) CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY SMALLINT, TYPE SMALLINT, HEADERS BYTEA, PAYLOAD BYTEA, PRIMARY KEY (MESSAGE_ID)) CREATE_TRANSACTION=CREATE TABLE JBM_TX (NODE_ID INTEGER, TRANSACTION_ID BIGINT, BRANCH_QUAL BYTEA, FORMAT_ID INTEGER, GLOBAL_TXID BYTEA, PRIMARY KEY (TRANSACTION_ID)) CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME)) CREATE_ID_CACHE=CREATE TABLE JBM_ID_CACHE (NODE_ID INTEGER, CNTR INTEGER, JBM_ID VARCHAR(255), PRIMARY KEY(NODE_ID, CNTR)) INSERT_DUAL=INSERT INTO JBM_DUAL VALUES (1) CHECK_DUAL=SELECT 1 FROM JBM_DUAL INSERT_MESSAGE_REF=INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?) DELETE_MESSAGE_REF=DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C' UPDATE_MESSAGE_REF=UPDATE JBM_MSG_REF SET TRANSACTION_ID=?, STATE='-' WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C' UPDATE_PAGE_ORDER=UPDATE JBM_MSG_REF SET PAGE_ORD = ? WHERE MESSAGE_ID=? AND CHANNEL_ID=? COMMIT_MESSAGE_REF1=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+' COMMIT_MESSAGE_REF2=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='-' ROLLBACK_MESSAGE_REF1=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='+' ROLLBACK_MESSAGE_REF2=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='-' LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND PAGE_ORD BETWEEN ? AND ? ORDER BY PAGE_ORD LOAD_UNPAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? AND PAGE_ORD IS NULL ORDER BY ORD LOAD_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? ORDER BY ORD UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND CHANNEL_ID=? SELECT_MIN_MAX_PAGE_ORD=SELECT MIN(PAGE_ORD), MAX(PAGE_ORD) FROM JBM_MSG_REF WHERE CHANNEL_ID = ? UPDATE_DELIVERY_COUNT=UPDATE JBM_MSG_REF SET DELIVERY_COUNT = ? WHERE MESSAGE_ID = ? AND CHANNEL_ID = ? UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ? MOVE_REFERENCE=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE MESSAGE_ID = ? AND CHANNEL_ID = ? LOAD_MESSAGES=SELECT MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, TYPE FROM JBM_MSG INSERT_MESSAGE=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) VALUES (?, ?, ?, ?, ?, ?, ?, ?) INSERT_MESSAGE_CONDITIONAL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE) SELECT ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?) UPDATE_MESSAGE_4CONDITIONAL=UPDATE JBM_MSG SET HEADERS=?, PAYLOAD=? WHERE MESSAGE_ID=? INSERT_MESSAGE_CONDITIONAL_FULL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?) MESSAGE_ID_COLUMN=MESSAGE_ID DELETE_MESSAGE=DELETE FROM JBM_MSG WHERE MESSAGE_ID = ? AND NOT EXISTS (SELECT JBM_MSG_REF.MESSAGE_ID FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = ?) INSERT_TRANSACTION=INSERT INTO JBM_TX (NODE_ID, TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?, ?) DELETE_TRANSACTION=DELETE FROM JBM_TX WHERE NODE_ID = ? AND TRANSACTION_ID = ? SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JBM_TX WHERE NODE_ID = ? SELECT_MESSAGE_ID_FOR_REF=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '+' ORDER BY ORD SELECT_MESSAGE_ID_FOR_ACK=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '-' ORDER BY ORD UPDATE_COUNTER=UPDATE JBM_COUNTER SET NEXT_ID = ? WHERE NAME=? SELECT_COUNTER=SELECT NEXT_ID FROM JBM_COUNTER WHERE NAME=? FOR UPDATE INSERT_COUNTER=INSERT INTO JBM_COUNTER (NAME, NEXT_ID) VALUES (?, ?) SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNEL_ID) FROM JBM_MSG_REF UPDATE_TX=UPDATE JBM_TX SET NODE_ID=? WHERE NODE_ID=? UPDATE_ID_IN_CACHE=UPDATE JBM_ID_CACHE SET JBM_ID = ? WHERE NODE_ID = ? AND CNTR = ? INSERT_ID_IN_CACHE=INSERT INTO JBM_ID_CACHE (NODE_ID, CNTR, JBM_ID) VALUES (?, ?, ?) LOAD_ID_CACHE=SELECT CNTR, JBM_ID FROM JBM_ID_CACHE WHERE NODE_ID = ? ]]></attribute> <!-- The maximum number of parameters to include in a prepared statement --> <attribute name="MaxParams">500</attribute> </mbean> <!-- Messaging Post Office MBean configuration ========================================= --> <mbean code="org.jboss.messaging.core.jmx.MessagingPostOfficeService" name="jboss.messaging:service=PostOffice" xmbean-dd="xmdesc/MessagingPostOffice-xmbean.xml"> <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer</depends> <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends> <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends> <!-- The name of the post office --> <attribute name="PostOfficeName">JMS post office</attribute> <!-- The datasource used by the post office to access it's binding information --> <attribute name="DataSource">java:/DefaultDS</attribute> <!-- If true will attempt to create tables and indexes on every start-up --> <attribute name="CreateTablesOnStartup">true</attribute> <attribute name="SqlProperties"><![CDATA[ CREATE_POSTOFFICE_TABLE=CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID INTEGER, QUEUE_NAME VARCHAR(255), COND VARCHAR(1023), SELECTOR VARCHAR(1023), CHANNEL_ID BIGINT, CLUSTERED CHAR(1), ALL_NODES CHAR(1), PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME)) INSERT_BINDING=INSERT INTO JBM_POSTOFFICE (POSTOFFICE_NAME, NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES) VALUES (?, ?, ?, ?, ?, ?, ?, ?) DELETE_BINDING=DELETE FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? AND QUEUE_NAME=? LOAD_BINDINGS=SELECT QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? ]]></attribute> <!-- This post office is non clustered. If you want a clustered post office then set to true --> <attribute name="Clustered">false</attribute> <!-- All the remaining properties only have to be specified if the post office is clustered. You can safely comment them out if your post office is non clustered --> <!-- The JGroups group name that the post office will use --> <attribute name="GroupName">${jboss.messaging.groupname:MessagingPostOffice}</attribute> <!-- Max time to wait for state to arrive when the post office joins the cluster --> <attribute name="StateTimeout">30000</attribute> <!-- Max time to wait for a synchronous call to node members using the MessageDispatcher --> <attribute name="CastTimeout">30000</attribute> <!-- Set this to true if you want failover of connections to occur when a node is shut down --> <attribute name="FailoverOnNodeLeave">false</attribute> <!-- JGroups stack configuration for the data channel - used for sending data across the cluster --> <!-- By default we use the TCP stack for data --> <attribute name="DataChannelConfig"> <config> <TCP start_port="7900" loopback="true" recv_buf_size="20000000" send_buf_size="640000" discard_incompatible_packets="true" max_bundle_size="64000" max_bundle_timeout="30" use_incoming_packet_handler="true" use_outgoing_packet_handler="false" down_thread="false" up_thread="false" enable_bundling="false" use_send_queues="false" sock_conn_timeout="300" skip_suspected_members="true"/> <MPING timeout="4000" mcast_addr="${jboss.messaging.datachanneludpaddress,jboss.partition.udpGroup:228.6.6.6}" mcast_port="${jboss.messaging.datachanneludpport:45567}" ip_ttl="${jboss.messaging.ipttl:8}" num_initial_members="2" num_ping_requests="1"/> <MERGE2 max_interval="100000" down_thread="false" up_thread="false" min_interval="20000"/> <FD timeout="10000" max_tries="5" down_thread="false" up_thread="false" shun="true"/> <FD_SOCK down_thread="false" up_thread="false"/> <VERIFY_SUSPECT timeout="1500" down_thread="false" up_thread="false"/> <pbcast.NAKACK max_xmit_size="60000" use_mcast_xmit="false" gc_lag="0" retransmit_timeout="300,600,1200,2400,4800" down_thread="false" up_thread="false" discard_delivered_msgs="true"/> <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000" down_thread="false" up_thread="false" max_bytes="400000"/> <pbcast.GMS print_local_addr="true" join_timeout="3000" down_thread="false" up_thread="false" join_retry_timeout="2000" shun="false" view_bundling="true"/> </config> </attribute> <!-- JGroups stack configuration to use for the control channel - used for control messages --> <!-- We use udp stack for the control channel --> <attribute name="ControlChannelConfig"> <config> <UDP mcast_addr="${jboss.messaging.controlchanneludpaddress,jboss.partition.udpGroup:228.7.7.7}" mcast_port="${jboss.messaging.controlchanneludpport:45568}" tos="8" ucast_recv_buf_size="20000000" ucast_send_buf_size="640000" mcast_recv_buf_size="25000000" mcast_send_buf_size="640000" loopback="false" discard_incompatible_packets="true" max_bundle_size="64000" max_bundle_timeout="30" use_incoming_packet_handler="true" use_outgoing_packet_handler="false" ip_ttl="${jboss.messaging.ipttl:8}" down_thread="false" up_thread="false" enable_bundling="false"/> <PING timeout="2000" down_thread="false" up_thread="false" num_initial_members="3"/> <MERGE2 max_interval="100000" down_thread="false" up_thread="false" min_interval="20000"/> <FD_SOCK down_thread="false" up_thread="false"/> <FD timeout="10000" max_tries="5" down_thread="false" up_thread="false" shun="true"/> <VERIFY_SUSPECT timeout="1500" down_thread="false" up_thread="false"/> <pbcast.NAKACK max_xmit_size="60000" use_mcast_xmit="false" gc_lag="0" retransmit_timeout="300,600,1200,2400,4800" down_thread="false" up_thread="false" discard_delivered_msgs="true"/> <UNICAST timeout="300,600,1200,2400,3600" down_thread="false" up_thread="false"/> <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000" down_thread="false" up_thread="false" max_bytes="400000"/> <pbcast.GMS print_local_addr="true" join_timeout="3000" use_flush="true" flush_timeout="3000" down_thread="false" up_thread="false" join_retry_timeout="2000" shun="false" view_bundling="true"/> <FRAG2 frag_size="60000" down_thread="false" up_thread="false"/> <pbcast.STATE_TRANSFER down_thread="false" up_thread="false" use_flush="true" flush_timeout="3000"/> <pbcast.FLUSH down_thread="false" up_thread="false" timeout="20000" auto_flush_conf="false"/> </config> </attribute> </mbean> <!-- Messaging JMS User Manager MBean config ======================================= --> <mbean code="org.jboss.jms.server.plugin.JDBCJMSUserManagerService" name="jboss.messaging:service=JMSUserManager" xmbean-dd="xmdesc/JMSUserManager-xmbean.xml"> <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends> <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends> <attribute name="DataSource">java:/DefaultDS</attribute> <attribute name="CreateTablesOnStartup">true</attribute> <attribute name="SqlProperties"><![CDATA[ CREATE_USER_TABLE=CREATE TABLE JBM_USER (USER_ID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USER_ID)) CREATE_ROLE_TABLE=CREATE TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID)) SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JBM_USER WHERE USER_ID=? POPULATE.TABLES.1 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('guest', 'guest') POPULATE.TABLES.2 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('j2ee', 'j2ee') POPULATE.TABLES.3 = INSERT INTO JBM_USER (USER_ID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample') POPULATE.TABLES.4 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('nobody', 'nobody') POPULATE.TABLES.5 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('dynsub', 'dynsub') POPULATE.TABLES.6 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('guest','guest') POPULATE.TABLES.7 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('j2ee','guest') POPULATE.TABLES.8 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('john','guest') POPULATE.TABLES.9 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('subscriber','john') POPULATE.TABLES.10 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','john') POPULATE.TABLES.11 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','dynsub') POPULATE.TABLES.12 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','john') POPULATE.TABLES.13 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','dynsub') POPULATE.TABLES.14 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('noacc','nobody') ]]></attribute> </mbean> </server>
----------------------------------------------------------------------------
ejb3-timer-service.xml:<?xml version="1.0" encoding="UTF-8"?> <server> <!-- TODO: the ejb deployer must depend on this --> <!-- ================================================ --> <!-- Defines the Quartz configuration for --> <!-- the EJB3 Timer Service --> <!-- ================================================ --> <mbean code="org.jboss.ejb3.timerservice.quartz.jmx.EJB3TimerService" name="jboss.ejb:service=EJB3TimerService"> <depends>jboss:service=Naming</depends> <depends>jboss:service=TransactionManager</depends> <depends optional-attribute-name="DataSource">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends> <attribute name="Properties"> org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreCMT org.quartz.jobStore.nonManagedTXDataSource=myDS org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate org.quartz.jobStore.tablePrefix=QRTZ_ org.quartz.jobStore.dataSource=myDS # To get it to work with hypersonic # FIXME: this doesn't lock the row org.quartz.jobStore.selectWithLockSQL=SELECT * FROM QRTZ_LOCKS WHERE lock_name = ? # from quartz.properties org.quartz.scheduler.instanceName=JBossEJB3QuartzScheduler org.quartz.scheduler.rmi.export=false org.quartz.scheduler.rmi.proxy=false org.quartz.scheduler.wrapJobExecutionInUserTransaction=false org.quartz.threadPool.class=org.quartz.simpl.SimpleThreadPool org.quartz.threadPool.threadCount=10 org.quartz.threadPool.threadPriority=5 org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread=true org.quartz.jobStore.misfireThreshold=60000 </attribute> <attribute name="SqlProperties"> CREATE_DB_ON_STARTUP = TRUE CREATE_TABLE_JOB_DETAILS = CREATE TABLE QRTZ_JOB_DETAILS(JOB_NAME VARCHAR(80) NOT NULL, JOB_GROUP VARCHAR(80) NOT NULL, \ DESCRIPTION VARCHAR(120) NULL, JOB_CLASS_NAME VARCHAR(128) NOT NULL, IS_DURABLE BOOL NOT NULL, \ IS_VOLATILE BOOL NOT NULL, IS_STATEFUL BOOL NOT NULL, REQUESTS_RECOVERY BOOL NOT NULL, \ JOB_DATA BYTEA NULL, PRIMARY KEY (JOB_NAME,JOB_GROUP)) CREATE_TABLE_JOB_LISTENERS = CREATE TABLE QRTZ_JOB_LISTENERS(JOB_NAME VARCHAR(80) NOT NULL, JOB_GROUP VARCHAR(80) NOT NULL, \ JOB_LISTENER VARCHAR(80) NOT NULL, PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER), FOREIGN KEY (JOB_NAME,JOB_GROUP) \ REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP)) CREATE_TABLE_TRIGGERS = CREATE TABLE QRTZ_TRIGGERS(TRIGGER_NAME VARCHAR(80) NOT NULL, TRIGGER_GROUP VARCHAR(80) NOT NULL, \ JOB_NAME VARCHAR(80) NOT NULL, JOB_GROUP VARCHAR(80) NOT NULL, IS_VOLATILE BOOL NOT NULL, DESCRIPTION VARCHAR(120) NULL, \ NEXT_FIRE_TIME BIGINT NULL, PREV_FIRE_TIME BIGINT NULL, TRIGGER_STATE VARCHAR(16) NOT NULL, \ TRIGGER_TYPE VARCHAR(8) NOT NULL, START_TIME BIGINT NOT NULL, END_TIME BIGINT NULL, CALENDAR_NAME VARCHAR(80) NULL, \ MISFIRE_INSTR SMALLINT NULL, JOB_DATA BYTEA NULL, PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (JOB_NAME,JOB_GROUP) \ REFERENCES QRTZ_JOB_DETAILS(JOB_NAME,JOB_GROUP)) CREATE_TABLE_SIMPLE_TRIGGERS = CREATE TABLE QRTZ_SIMPLE_TRIGGERS(TRIGGER_NAME VARCHAR(80) NOT NULL, \ TRIGGER_GROUP VARCHAR(80) NOT NULL, REPEAT_COUNT BIGINT NOT NULL, REPEAT_INTERVAL BIGINT NOT NULL, \ TIMES_TRIGGERED BIGINT NOT NULL, PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) \ REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)) CREATE_TABLE_CRON_TRIGGERS = CREATE TABLE QRTZ_CRON_TRIGGERS(TRIGGER_NAME VARCHAR(80) NOT NULL, \ TRIGGER_GROUP VARCHAR(80) NOT NULL, CRON_EXPRESSION VARCHAR(80) NOT NULL, TIME_ZONE_ID VARCHAR(80), \ PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) \ REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)) CREATE_TABLE_BLOB_TRIGGERS = CREATE TABLE QRTZ_BLOB_TRIGGERS(TRIGGER_NAME VARCHAR(80) NOT NULL, \ TRIGGER_GROUP VARCHAR(80) NOT NULL, BLOB_DATA BYTEA NULL, PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), \ FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)) CREATE_TABLE_TRIGGER_LISTENERS = CREATE TABLE QRTZ_TRIGGER_LISTENERS(TRIGGER_NAME VARCHAR(80) NOT NULL, \ TRIGGER_GROUP VARCHAR(80) NOT NULL, TRIGGER_LISTENER VARCHAR(80) NOT NULL, \ PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER), FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) \ REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)) CREATE_TABLE_CALENDARS = CREATE TABLE QRTZ_CALENDARS(CALENDAR_NAME VARCHAR(80) NOT NULL, CALENDAR BYTEA NOT NULL, \ PRIMARY KEY (CALENDAR_NAME)) CREATE_TABLE_PAUSED_TRIGGER_GRPS = CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS(TRIGGER_GROUP VARCHAR(80) NOT NULL, \ PRIMARY KEY (TRIGGER_GROUP)) CREATE_TABLE_FIRED_TRIGGERS = CREATE TABLE QRTZ_FIRED_TRIGGERS(ENTRY_ID VARCHAR(95) NOT NULL, TRIGGER_NAME VARCHAR(80) NOT NULL, \ TRIGGER_GROUP VARCHAR(80) NOT NULL, IS_VOLATILE BOOL NOT NULL, INSTANCE_NAME VARCHAR(80) NOT NULL, \ FIRED_TIME BIGINT NOT NULL, STATE VARCHAR(16) NOT NULL, JOB_NAME VARCHAR(80) NULL, JOB_GROUP VARCHAR(80) NULL, \ IS_STATEFUL BOOL NULL, REQUESTS_RECOVERY BOOL NULL, PRIMARY KEY (ENTRY_ID)) CREATE_TABLE_SCHEDULER_STATE = CREATE TABLE QRTZ_SCHEDULER_STATE(INSTANCE_NAME VARCHAR(80) NOT NULL, \ LAST_CHECKIN_TIME BIGINT NOT NULL, CHECKIN_INTERVAL BIGINT NOT NULL, RECOVERER VARCHAR(80) NULL, \ PRIMARY KEY (INSTANCE_NAME)) CREATE_TABLE_LOCKS = CREATE TABLE QRTZ_LOCKS(LOCK_NAME VARCHAR(40) NOT NULL, PRIMARY KEY (LOCK_NAME)) INSERT_TRIGGER_ACCESS = INSERT INTO QRTZ_LOCKS values('TRIGGER_ACCESS') INSERT_JOB_ACCESS = INSERT INTO QRTZ_LOCKS values('JOB_ACCESS') INSERT_CALENDAR_ACCESS = INSERT INTO QRTZ_LOCKS values('CALENDAR_ACCESS') INSERT_STATE_ACCESS = INSERT INTO QRTZ_LOCKS values('STATE_ACCESS') INSERT_MISFIRE_ACCESS = INSERT INTO QRTZ_LOCKS values('MISFIRE_ACCESS') </attribute> </mbean> </server> -
3. Re: Howto move jbm and quartz tables to postgresql schema?
In the postgresql-persistence-service.xml file, where did you get these two items from, they do not appear in the file I am looking at:
<!-- By default we use the TCP stack for data --> <attribute name="DataChannelConfig"> ... </attribute> <!-- We use udp stack for the control channel --> <attribute name="ControlChannelConfig"> ... </attribute>
Also, in the *-ds.xml file, change the mapping to "PostgreSQL 8.0". The server/xxx/conf/standardjbosscmp-jdbc.xml file contains the valid mapping names. Of course, this is only an issue if you are using EJB2.x entity beans with container-managed persistence.
Regarding quartz, see http://www.jboss.com/index.html?module=bb&op=viewtopic&t=141835
For a working example, go to http://www.manning.com/jbossinaction, download the source zip file. Modify the build properties in jbia-src/ch00/build.properties for your environment. (Open the jbia-src/index.html file for full instructions and information.) "cd" into the jbia-src/ch15 directory and run "ant 03" It will create an app server configuration using PostgreSQL (or MySQL, depending on build.properties) as the database. -
4. Re: Howto move jbm and quartz tables to postgresql schema?
Hi PeterJ,
Ive tried the Jboss In Action example that you pointed me to, but it does not solve the problem. The jbm_* and qrtz_* tables are still located in Public instead of my own defined Postgresql schema (database). The tables related to the application are located correctly in the schema using the "schema" attribute in the persistence.xml:<property name="hibernate.default_schema" value="smsgateway"/>
Any help are appreciated. Thanks.
/nikolaj -
5. Re: Howto move jbm and quartz tables to postgresql schema?
I am sorry, it appears that I have completely misunderstood your original question. If I now understand correctly, you are asking how to get JBoss AS to use a schema for its tables, is that correct?
Hibernate has the luxury of construction SQL statements on-the-fly and thus can support the syntax needed to specify the schema name in every SQL statement.
It would seem to me that the ideal location for specifying the desired schema would be in the connection URL. But I could find no such option in the PosgreSQL JDBC driver documentation. One would think that one of these options would be allowed:
jdbc:postgresql://host:port/database/schema
or
jdbc:postgresql://host:port/database&schema=xxx
But it seems that neither is allowed.
One of your options is modify all of the SQL statements for messaging and quartz to include the schema name. (Yes, I know it is ugly.)
But I just had an idea. According to the PostgreSQL documentation, you can specify a schema search path using syntax like:
SET search_path TO xxx,public;
Once that is done, PostgreSQL will look in schema xxx first. One way to set this is by specifying a check-valid-connection-sql entry in you *-ds.xml file:<check-valid-connection-sql>SET search_path TO xxx,public;</check-valid-connection-sql>
-
6. Re: Howto move jbm and quartz tables to postgresql schema?
Yes, I was a bit mystified about your answer :-). Anyway, I was jumping around when I saw the jbm_ and qrtz_ tables end up in the schema. But sorry to say, its not 100% bulletproof (yet). When making several jboss restarts a jbm_dual table suddenly goes into Public. Also Ive seen all the qrtz_ tables end up there. What I did will follow below.
To be consistent with your setup Im using the jbia files as you recommend:
postgres-ds.xml
ejb3-timer-service.xml
postgresql-persistence-service.xml
Ive tried several things, but ended up with leaving the ejb3-timer-service.xml and the postgresql-persistence-service.xml files as is - only adding the search path to the postgres-ds.xml file. Trying to prefix the jbm_ and qrtz_ code will make strange things happen when starting jboss. Sometimes it works - sometimes not, as if the jboss team does not wait on startup events. Ive also tried to fiddle with "org.quartz.jobStore.tablePrefix=QRTZ_" in the ejb3-timer-service.xml file, but it also give some strange errors.
As said, on your recommendation Ive added the below to postgres-ds.xml:<check-valid-connection-sql>SET search_path TO xxx,public;</check-valid-connection-sql>
Also, here is my persistence.xml:<?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xm l/ns/persistence/persistence_1_0.xsd" version="1.0"> <persistence-unit name="smsgatewayUnit"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <jta-data-source>java:DefaultDS</jta-data-source> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/> <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" /> <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/smsgateway" /> <property name="hibernate.connection.username" value="smsgateway_user" /> <property name="hibernate.connection.password" value="test1234" /> <property name="hibernate.hbm2ddl.auto" value="create"/> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.default_schema" value="smsgateway"/> </properties> </persistence-unit> </persistence>
Just to be sure - the postgres-ds.xml file:<?xml version="1.0" encoding="UTF-8"?> <datasources> <local-tx-datasource> <jndi-name>DefaultDS</jndi-name> <connection-url>jdbc:postgresql://127.0.0.1:5432/smsgateway</connection-url> <driver-class>org.postgresql.Driver</driver-class> <check-valid-connection-sql>SET search_path TO smsgateway,public;</check-valid-connection-sql> <user-name>smsgateway_user</user-name> <password>test1234</password> <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation> <min-pool-size>5</min-pool-size> <max-pool-size>20</max-pool-size> <idle-timeout-minutes>15</idle-timeout-minutes> <metadata> <type-mapping>PostgreSQL 8.0</type-mapping> </metadata> </local-tx-datasource> </datasources>
Again, thanks for the help.
/nikolaj