5 Replies Latest reply: Dec 6, 2012 11:54 PM by ssharma RSS

    ATG 10.1.1 - MySQL foriegn key issues

    Sandeep.R
      Hi All,

      Am trying to setup ATG 10.1.1 in a distributed environment for development purpose.

      I have 2 boxes.
      Windows box: contains MySQL installation and will be treated as DB Server
      Linux box: contains ATG/Weblogic installation and will be treated as APP Server

      All CIM activities of creating schema and importing data to databases (for CRS) along with all other things completed successfully. BCC/Dynamo Admin are up and running successfully.

      When I try to do full deployment for CRS site in BCC i get following error:

           Error'      Deployment Failed      lpxsku2505_14:price      Sep 18, 2012 1:38:32 PM
      Message Detail:
      CONTAINER:atg.deployment.DistributedDeploymentException; SOURCE:CONTAINER:atg.repository.RepositoryException; SOURCE:com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`switchb`.`dcs_price`, CONSTRAINT `dcs_pricpric_lst_f` FOREIGN KEY (`price_list`) REFERENCES `dcs_price_list` (`price_list_id`)) at atg.deployment.repository.RepositoryWorkerThread.processMarkerForAddUpdatePhase(RepositoryWorkerThread.java:273) at atg.deployment.DeploymentWorkerThread.processMarkerPhase(DeploymentWorkerThread.java:562) at atg.deployment.DeploymentWorkerThread.run(DeploymentWorkerThread.java:335)

      Any thoughts? Does ATG 10.1.1 (CRS) even support MySQL?
        • 1. Re: ATG 10.1.1 - MySQL foriegn key issues
          Nitin Khare
          I think MySQL is supported at least for development mode so it should work for you. Just throwing in some generic ideas hoping you may get more clue about the problem:

          1. You may try deployment after configuring certain properties in /atg/deployment/DeploymentManager component. Try restarting your CA server after placing a DeploymentManager.properties under <ATG>\home\localconfig\atg\deployment with contents like this:

          maxThreads=20
          transactionBatchSize=5000
          threadBatchSize=5000

          I guess you should not be encountering it but just in case if you see any issue related to thread deadlock during deployment, try with maxThreads=1. Also, ensure that you have increased the transaction timeout value for your app server. E.g. for JBoss, in the transaction-jboss-beans.xml present under your <JBoss>\<CA_SERVER>\deploy directory as: <property name="transactionTimeout">1800</property>

          2. Connect to your mysql server using mysql command line client and then on mysql prompt issue a command like this:

          show engine innodb status\G

          It will give you details related to the various transactions and other internal state details which might give you more clue about the problem. The output could be pretty huge so you can just focus on the data where your deployment failed.

          3. Apart from usual loggingDebug, DeploymentManager component also includes some additional properties for including debugging information in server logs:

          loggingThreadDebug - for thread-level debugging
          loggingItemDebug - for debugging at repository items level
          loggingPropertyDebug - at repository item property level debugging
          loggingFileDebug - for file assets

          Again setting these debug properties can cause huge log output but may give some clue of the problem.
          • 2. Re: ATG 10.1.1 - MySQL foriegn key issues
            923097
            I am also getting same issue while doing a full deployment. I am using ATG 10.1.1 with CRS , weblogic 10.3.5(11g), Windows 7 and mySQL. In full deployment, BCC is trying to set a pricelist_id "plist3100005" which does not exist in publishing or production database.


            -- Parameters --
            p[1] = {pd} lpxsku2505_14 (java.lang.String)
            p[2] = {pd: version} 1 (java.lang.Integer)
            p[3] = {pd: priceList} plist3100005 (atg.adapter.gsa.SingleValueGSAId)
            p[4] = {pd: productId} null
            p[5] = {pd: skuId} xsku2505_14 (java.lang.String)
            p[6] = {pd: parentSkuId} null
            p[7] = {pd: pricingScheme} 0 (java.lang.Integer)
            p[8] = {pd: listPrice} 129.0 (java.lang.Double)
            [--SQLInsert--]
            com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`catalogb`.`dcs_price`, CONSTRAINT `dcs_pricpric_lst_f` FOREIGN KEY (`price_list`) REFERENCES `dcs_price_list` (`price_list_id`))
            at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
            at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
            at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
            at com.mysql.jdbc.Util.getInstance(Util.java:382)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
            • 3. Re: ATG 10.1.1 - MySQL foriegn key issues
              972931
              Hi, I'm also hitting the same error.
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     -- Parameters --
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     p[1] = {pd} lpxsku2505_14 (java.lang.String)
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     p[2] = {pd: version} 1 (java.lang.Integer)
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     p[3] = {pd: priceList} plist3120005 (atg.adapter.gsa.SingleValueGSAId)
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     p[4] = {pd: productId} null
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     p[5] = {pd: skuId} xsku2505_14 (java.lang.String)
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     p[6] = {pd: parentSkuId} null
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     p[7] = {pd: pricingScheme} 0 (java.lang.Integer)
              **** Error     Wed Nov 14 17:44:25 SGT 2012     1352886265634     /atg/commerce/pricing/priceLists/PriceLists_production     p[8] = {pd: listPrice} 129.0 (java.lang.Double)

              Any ideas?
              • 4. Re: ATG 10.1.1 - MySQL foriegn key issues
                970985
                HI there,

                I am too getting the same problem. Can you please let me out?

                I have installed ATG 10.1. on Weblogic 10.3.5 with JDK1.6_25 on Windows8.


                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production SQL Statement Failed: [++SQLInsert++]
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production INSERT INTO dcs_price(price_id,version,price_list,product_id,sku_id,parent_sku_id,pricing_scheme,list_price)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production VALUES(?,?,?,?,?,?,?,?)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production -- Parameters --
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production p[1] = {pd} lpxsku2505_14 (java.lang.String)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production p[2] = {pd: version} 1 (java.lang.Integer)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production p[3] = {pd: priceList} plist3130005 (atg.adapter.gsa.SingleValueGSAId)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production p[4] = {pd: productId} null
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production p[5] = {pd: skuId} xsku2505_14 (java.lang.String)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production p[6] = {pd: parentSkuId} null
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production p[7] = {pd: pricingScheme} 0 (java.lang.Integer)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production p[8] = {pd: listPrice} 129.0 (java.lang.Double)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production [--SQLInsert--]
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`switchingb`.`dcs_price`, CONSTRAINT `dcs_pricpric_lst_f` FOREIGN KEY (`price_list`) REFERENCES `dcs_price_list` (`price_list_id`))
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
                **** Error Thu Dec 06 17:47:48 EST 2012 1354834068934 /atg/commerce/pricing/priceLists/PriceLists_production at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
                • 5. Re: ATG 10.1.1 - MySQL foriegn key issues
                  ssharma
                  Hi,

                  I an not sure of the exact resolution, but will refer you one link for your information
                  http://docs.oracle.com/cd/E22630_01/Platform.1002/pdf/ATGCAProgGuide.pdf
                  Check the heading : Create the Versioned Schema and then go to its 6th point.

                  I hope it will be somehwat helpful for you.