Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
Replies
13 replies
Subscribers
9 subscribers
Views
4982 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
Administration
Hi All, here's an issue I have with a MySQL database table name. I have a ta
kevind605
over 11 years ago
Hi All, here's an issue I have with a MySQL database table name. I have a table called msg_in defined in the database. I added the JPA annotation in my xsd but Appian is still doing something weird with the table name. The error says: "The data source schema does not match the type mappings: Missing table: msgintype_msg (APNX-2-4056-000)".
Here's the entire xsd for the table in question. Most of this was generated by XMLSpy. The only stuff I added were the target namespace and the couple of JPA annotations.
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:miemonitor="
www.questdiagnostics.com"
xmlns:xs="
www.w3.org/.../XMLSchema"
targetNamespace="
www.questdiagnostics.com">
<xs:complexType name="msg_in">
<xs:annotation>
<xs:appinfo source="appian.jpa">
@Table(name="msg_in")
</xs:appinfo>
</xs:annotation>
<xs:sequence>
<xs:element name="msgkey">
<xs:annotation>
<xs:appinfo source="appian.jpa">...
OriginalPostID-63807
OriginalPostID-63807
Discussion posts and replies are publicly visible
0
kevind605
over 11 years ago
...
@Id
</xs:appinfo>
</xs:annotation>
<xs:simpleType>
<xs:restriction base="xs:long">
<xs:minInclusive value="-9223372036854775808"/>
<xs:maxInclusive value="9223372036854775807"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="acknak" default="2">
<xs:simpleType>
<xs:restriction base="xs:byte">
<xs:minInclusive value="-128"/>
<xs:maxInclusive value="127"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="msg">
<xs:simpleType>
<xs:restriction base="xs:base64Binary">
<xs:maxLength value="2147483647"/>
<xs:minLength value="1"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="filename" nillable="true" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="250"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="msgstate" default="1">
<xs:simpleType>
<xs:restriction base="xs:byte">
<xs:minInclusive value="-128"/>
<xs:maxInclusive v...
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
kevind605
over 11 years ago
...alue="127"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="tstamp" type="xs:dateTime">
<xs:annotation>
<xs:appinfo source="appian.jpa">
@Column(columnDefinition="DATETIME NOT NULL")
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="mrtstamp" type="xs:dateTime">
<xs:annotation>
<xs:appinfo source="appian.jpa">
@Column(columnDefinition="DATETIME NOT NULL")
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:schema>
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Bishnu Panigrahi
Appian Employee
over 11 years ago
Do you have another data store entity that maps an XSD to a table called msgintype_msg? If so, before adding a data store entity that corresponds to the msg_in table, save and publish the data store and make sure all of the entity mappings are being verified. If the entity mappings aren't being verified, the issue could be with the msgintype_msg XSD, and not the msg_in XSD.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
kevind605
over 11 years ago
Nope I don't have any other datastore entities that map to any table called msgintype_msg. That table doesn't exist in the database, nor is it in the XSD, nor is there any entity with that name. I have found that Appian requires the JPA annotations to be used for table names in the XSD. If you don't use the JAP annotations, Appian changes the table names in some circumstances. But I am using the JPA annotation to force it to use the correct table name. But it keeps changing the name anyway. It seems to be the underscore in the table name that is causing the issue. But I can't change the table name in the database because it's already in production.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Sathya Srinivasan
Appian Employee
over 11 years ago
Can you check if its the table that's causing the issue and not any other attribute? One way to check this is to download the DDL that Appian provides when you try to map the CDT and cehck that all the entnties (such as the PK, sequence, FK etc) are exactly aligned. This is a quick way to check.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
kevind605
over 11 years ago
Yep I downloaded the DDL and below is what it looks like. I don't understand where it's coming up with the table called msgin_msg. That table isn't in the database and it isn't in the XSD. But when I import the XSD and then download the DDL, that's what gets included.
/*
Data Store: valley_lab
Created: 2013-04-09T22:00:58.348Z
By: Administrator
Appian Version: 7.0.0.0
Target Database: MySQL 5.5.17
Database Driver: MySQL-AB JDBC Driver mysql-connector-java-5.0.8 ( Revision: ${svn.Revision} )
*/
/* UPDATE DDL */
create table `msgin_msg` (
`msgin_msg_msgkey` bigint not null,
elt tinyint,
`msgin_msg_idx` integer not null,
primary key (`msgin_msg_msgkey`, `msgin_msg_idx`)
) ENGINE=InnoDB;
alter table `msgin_msg`
add index FKCD540C28779F644D (`msgin_msg_msgkey`),
add constraint FKCD540C28779F644D
foreign key (`msgin_msg_msgkey`)
references `msg_in` (`msgkey`);
/* DROP AND CREATE DDL */
/* WARNING: The DDL commented out below will drop and re-create all tables.
alter table `msgin_msg`
drop
foreign key FKCD540C28779F644D;
drop table if exists `msg_in`;
drop table if exists `msgin_msg`;
create table `msg_in` (
`msgkey` bigint not null,
`acknak` tinyint,
`filename` varchar(250),
`msgstate` tinyint,
`tstamp` DATETIME NOT NULL,
`mrtstamp` DATETIME NOT NULL,
primary key (`msgkey`)
) ENGINE=InnoDB;
create table `msgin_msg` (
`msgin_msg_msgkey` bigint not null,
elt tinyint,
`msgin_msg_idx` integer not null,
primary key (`msgin_msg_msgkey`, `msgin_msg_idx`)
) ENGINE=InnoDB;
alter table `msgin_msg`
add index FKCD540C28779F644D (`msgin_msg_msgkey`),
add constraint FKCD540C28779F644D
foreign key (`msgin_msg_msgkey`)
references `msg_in` (`msgkey`);
*/
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Sathya Srinivasan
Appian Employee
over 11 years ago
Try this
Delete the CDT definition.
Update the XSD with a different table name ( e.g TBL_DATA1)
restart JBoss (Just in case)
now, try and import the updated CDT
Map this to the datasource and see if this works
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Eduardo Fuentes
Appian Employee
over 11 years ago
This is because you declared the msg element as xs:base64Binary. Appian will create a separate table to store this type of data and associate it with the primary table (msg_in) through a foreign key constraint.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Eduardo Fuentes
Appian Employee
over 11 years ago
And it is easily reproducible with a simple XSD with an element like this
<xs:element name="msg" type="xs:base64Binary"></xs:element>
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Eduardo Fuentes
Appian Employee
over 11 years ago
If you don't want the extra table change the type to xs:string and add an annotation to indicate the data type of this column in the table, for instance, the following syntax allowed me to get rid of the extra table when using your XSD to test:
<xs:element name="msg" type="xs:string">
<xs:annotation>
<xs:appinfo source="appian.jpa">
@Column(columnDefinition="BLOB")
</xs:appinfo>
</xs:annotation>
</xs:element>
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
>