7 Replies Latest reply on Oct 31, 2003 9:28 AM by wunderkind

    mysql cmp relational integrity MyISAM/InnoDB

    wunderkind Newbie

      Everytime I use cmp relationships and mysql - JBoss is running into trouble because of MySQL's MyISAM default table format. It doesn't support foreign keys. That forces me to create the tables on my own. No real problem but I
      would like to know if there is a workaround?

      The other question is: How can I influence the name of a
      relationship table that jboss is supposed to create for
      cmp?

      thanx, sven alias wunderkind

        • 1. Re: mysql cmp relational integrity MyISAM/InnoDB
          Jochen Cordes Novice

          Hi Sven !

          Here's how to do it. Add

          <post-table-create>
          ALTER TABLE mytable ADD type=InnoDB
          </post-table-create>

          to your jbosscmp-jdbc.xml in the entity section. To modify a relationship tablename put

          <relation-table-mapping>
          <table-name>your_relation_table</table-name>
          </relation-table-mapping>

          behind <ejb-relation-name/> in the same file. But I would rather recommend to use XDoclet (JBossIDE) for these task ;-). It's worth it, especially if you have quite a number of entity beans (my project has over 50 of them).

          HTH,

          Jochen.

          • 2. Re: mysql cmp relational integrity MyISAM/InnoDB
            wunderkind Newbie

            thank you jochen. you really helped a lot. i wasn't aware of the <post-table-create> option in jbosscmp-jdbc.xml.

            But there is an other thing: I actually use x-doclet (your recommendation) and really love it. I cannot think of a "non nuts" ejb developer that doesn't use this nice tool for generative programming. But I haven't found a solution for the <post-table-create> option in @jboss. tags. Do I have to use a merge dir for external files?

            sven alias wunderkind

            • 3. Re: mysql cmp relational integrity MyISAM/InnoDB
              Jochen Cordes Novice

              I think you have to modify the existing jbosscmp_xml.xdt file in xdoclet-module for jboss. Add

              <XDtClass:ifHasClassTag tagName="jboss:persistence" paramName="post-table-create">
              <post-table-create><XDtClass:classTagValue tagName="jboss:persistence" paramName="post-table-create"/></post-table-create>
              </XDtClass:ifHasClassTag>

              following the section for <create-table/>. Maybe it should be more sophisticated, but it's simple and works ;-).

              Bye,

              Jochen.

              • 4. Re: mysql cmp relational integrity MyISAM/InnoDB
                Jochen Cordes Novice

                Grrrr..... I've forgotten the sql-statement tag. So this whole thing should look like

                <XDtClass:ifHasClassTag tagName="jboss:persistence" paramName="post-table-create">
                <post-table-create><sql-statement><XDtClass:classTagValue tagName="jboss:persistence" paramName="post-table-create"/></sql-statement></post-table-create>
                </XDtClass:ifHasClassTag>

                I've noticed that the DTD for jbosscmp-jdbc is not up-to-date. Replace it with the one that came with JBoss 3.2.2.

                This then only works with one alter table statement. I think that someone should place a feature request on sourceforge, maybe a @jboss.post-table-create tag and then something like

                <XDtClass:forAllClassTags tagName="jboss:post-table-create">
                ...
                </XDtClass:forAllClassTags>

                But this are just my .02 ct.

                • 5. Re: mysql cmp relational integrity MyISAM/InnoDB
                  wunderkind Newbie

                  thank you. that helped lot! and big kudos for your
                  sharpness with the dtd :-)

                  At last I want to say that this kind of struggle is something
                  that is really anoying. On the one hand there is this really
                  nice jboss thing for which you can buy really good documentation. But on the other hand there are really
                  simple things that are not documented. INMHO there a really a lot of people that use MySQL out there - and if they use cmr (one of the most important reasons to use
                  ejbs at all) - they definetly will stumble accross such nasty
                  topics like this. Anyway - I love to work with jboss.

                  No I have to catch an other problem concerning autogenerated keys in mysql. the x-doclet tags don't work at the moment. @jboss.entity-command...

                  I will have a look at the .xdt :-)

                  kudos 2 you, sven alias wunderkind

                  • 6. Re: mysql cmp relational integrity MyISAM/InnoDB
                    Darran Lofthouse Master

                    A second possible solution to your foreign key problem would be to add the line

                    default-table-type=InnoDB

                    To the mysql my.ini or my.cnf (Depending on your configuration) file in the [mysqld] section.

                    • 7. Re: mysql cmp relational integrity MyISAM/InnoDB
                      wunderkind Newbie

                      thank you. that is even easier but only usable if i use
                      the mysql server only for jboss or more exactly have
                      adminitrative rights on it. Anyway: Nice solution.

                      sven alias wunderkind