Ticket #1101 (new defect)

Opened 9 years ago

Last modified 4 years ago

[OGR - WFS] SQL VRT with WFS

Reported by: jlacroix Owned by: mapserverbugs
Priority: high Milestone:
Component: WFS Server Version: 4.4
Severity: normal Keywords:
Cc:

Description 

A VRT data source containing a SQL Query may have problem with WFS. If the SQL
query returns a binary field, OGR will return a gml file containing illegal
characters (Binary data).

For example, I have a MySQL query on a table (SELECT * FROM table1) that
contains a blob with binary data in it. When I try to query those data via WFS,
the gml returned by MapServer contains a tag
<geom>[insert_fuzzy_characters_here]</geom>. This make the WFS client (MapServer
again) to fail. A ogrinfo on the GML file returns:
FAILURE:
Unable to open datasource
`/opt/fgs/tmp/ms_tmp//64b2afb384715e6ca9a49d6c393713e7.tmp.gml' with the
following drivers.

A proposed solution is to use CDATA in the XML. See:
http://www.w3schools.com/xml/xml_cdata.asp


Paul Spencer wrote:
AFAIK you can use CDATA in any XML document to indicate 'character data' inside
an element, it is used as follows:

<element><![CDATA[this is my data to be treated exactly as is, you can use
invalid characters like & < and > ...]]></element>

The only rules appears to be:

* the delimiter ']]>' must be continuous, no spaces, no linebreaks inside this
delimiter

* you can't use ]]> inside a cdata

Attachments

testcase1101.tgz Download (1.8 KB) - added by nsavard 4 years ago.
map files, virtual data file, sql dump

Change History

Changed 9 years ago by fwarmerdam 

JS, 

Could you attach information on how to produce a Mysql table that
demonstrates this problem?  With most databases the default select
on binary fields turns them into hex encoded text which is why I am
surprised you are running into this problem.

Changed 9 years ago by jlacroix 

Originally the geom field was a geometry field, butmy installation of MySQL does
not support this type so I changed it to blob.

CREATE TABLE `cimtLocations` (
  `locationID` int(10) unsigned NOT NULL auto_increment,
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `lat` float NOT NULL default '0',
  `longitude` float NOT NULL default '0',
  `geom` blob,
  PRIMARY KEY  (`locationID`)
);

INSERT INTO `cimtLocations` VALUES (1, '2004-11-14 00:00:00', '40', '-99',
0xe610000001010000000000000000388fc00000000000388fc0);
INSERT INTO `cimtLocations` VALUES (2, '2004-11-14 00:00:00', '-37.9275',
'-123', 0xe61000000101000000931804560e855ec01f85eb51b8764240);
INSERT INTO `cimtLocations` VALUES (3, '2004-11-14 00:00:00', '38.9273',
'-112.078', 0xe61000000101000000a245b6f3fd845ec058ca32c4b1764240);
INSERT INTO `cimtLocations` VALUES (4, '2004-11-14 00:00:00', '43.9272',
'-122.078', 0xe61000000101000000a245b6f3fd845ec0f46c567dae764240);
INSERT INTO `cimtLocations` VALUES (5, '2003-11-19 00:00:00', '56.9282',
'-64.082', 0xe610000001010000006891ed7c3f855ec0d712f241cf764240);
INSERT INTO `cimtLocations` VALUES (6, '2003-11-19 00:00:00', '76.928',
'-83.081', 0xe6100000010100000077be9f1a2f855ec0105839b4c8764240);
INSERT INTO `cimtLocations` VALUES (7, '2003-11-19 00:00:00', '26.9278',
'-102.08', 0xe6100000010100000085eb51b81e855ec0499d8026c2764240);
INSERT INTO `cimtLocations` VALUES (8, '2003-11-19 00:00:00', '26.9275',
'-92.08', 0xe6100000010100000085eb51b81e855ec01f85eb51b8764240);
INSERT INTO `cimtLocations` VALUES (9, '2003-11-19 00:00:00', '27.9273',
'-72.079', 0xe61000000101000000931804560e855ec058ca32c4b1764240);
INSERT INTO `cimtLocations` VALUES (10, '2003-11-19 00:00:00', '41.9272',
'-67.078', 0xe61000000101000000a245b6f3fd845ec0f46c567dae764240);

Changed 4 years ago by tomkralidis

Julien: is this still valid?

Changed 4 years ago by nsavard 

<?xml version='1.0' encoding="ISO-8859-1" ?>
<wfs:FeatureCollection
   xmlns:ms="http://mapserver.gis.umn.edu/mapserver"
   xmlns:wfs="http://www.opengis.net/wfs"
   xmlns:gml="http://www.opengis.net/gml"
   xmlns:ogc="http://www.opengis.net/ogc"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://www.opengis.net/wfs http://ogc.dmsolutions.ca/wfs/1.0.0/WFS-basic.xsd 
                       http://mapserver.gis.umn.edu/mapserver http://localhost:9090/cgi-bin/mswfs_ticket1101?SERVICE=WFS&amp;VERSION=1.0.0&amp;REQUEST=DescribeFeatureType&amp;TYPENAME=cimtlocations&amp;OUTPUTFORMAT=XMLSCHEMA">
      <gml:boundedBy>
      	<gml:Box srsName="EPSG:4326">
      		<gml:coordinates>-122.078000,26.927500 -67.078000,43.927200</gml:coordinates>
      	</gml:Box>
      </gml:boundedBy>
    <gml:featureMember>
      <ms:cimtlocations>

        <gml:boundedBy>
        	<gml:Box srsName="EPSG:4326">
        		<gml:coordinates>-99.000000,40.000000 -99.000000,40.000000</gml:coordinates>
        	</gml:Box>
        </gml:boundedBy>
        <ms:msGeometry>
        <gml:Point srsName="EPSG:4326">
          <gml:coordinates>-99.000000,40.000000</gml:coordinates>

        </gml:Point>
        </ms:msGeometry>
        <ms:locationID>1</ms:locationID>
        <ms:time>2004/11/14  0:00:00</ms:time>
        <ms:lat>          40</ms:lat>
        <ms:longitude>         -99</ms:longitude>

        <ms:geom>E610000001010000000000000000388FC00000000000388FC0</ms:geom>
      </ms:cimtlocations>
    </gml:featureMember>
    <gml:featureMember>
      <ms:cimtlocations>
        <gml:boundedBy>
        	<gml:Box srsName="EPSG:4326">
        		<gml:coordinates>-112.078000,38.927300 -112.078000,38.927300</gml:coordinates>

        	</gml:Box>
        </gml:boundedBy>
        <ms:msGeometry>
        <gml:Point srsName="EPSG:4326">
          <gml:coordinates>-112.078000,38.927300</gml:coordinates>
        </gml:Point>
        </ms:msGeometry>
        <ms:locationID>3</ms:locationID>

        <ms:time>2004/11/14  0:00:00</ms:time>
        <ms:lat>          39</ms:lat>
        <ms:longitude>        -112</ms:longitude>
        <ms:geom>E61000000101000000A245B6F3FD845EC058CA32C4B1764240</ms:geom>
      </ms:cimtlocations>
    </gml:featureMember>

    <gml:featureMember>
      <ms:cimtlocations>
        <gml:boundedBy>
        	<gml:Box srsName="EPSG:4326">
        		<gml:coordinates>-122.078000,43.927200 -122.078000,43.927200</gml:coordinates>
        	</gml:Box>
        </gml:boundedBy>
        <ms:msGeometry>

        <gml:Point srsName="EPSG:4326">
          <gml:coordinates>-122.078000,43.927200</gml:coordinates>
        </gml:Point>
        </ms:msGeometry>
        <ms:locationID>4</ms:locationID>
        <ms:time>2004/11/14  0:00:00</ms:time>
        <ms:lat>          44</ms:lat>

        <ms:longitude>        -122</ms:longitude>
        <ms:geom>E61000000101000000A245B6F3FD845EC0F46C567DAE764240</ms:geom>
      </ms:cimtlocations>
    </gml:featureMember>
    <gml:featureMember>
      <ms:cimtlocations>
        <gml:boundedBy>

        	<gml:Box srsName="EPSG:4326">
        		<gml:coordinates>-102.080000,26.927800 -102.080000,26.927800</gml:coordinates>
        	</gml:Box>
        </gml:boundedBy>
        <ms:msGeometry>
        <gml:Point srsName="EPSG:4326">
          <gml:coordinates>-102.080000,26.927800</gml:coordinates>
        </gml:Point>

        </ms:msGeometry>
        <ms:locationID>7</ms:locationID>
        <ms:time>2003/11/19  0:00:00</ms:time>
        <ms:lat>          27</ms:lat>
        <ms:longitude>        -102</ms:longitude>
        <ms:geom>E6100000010100000085EB51B81E855EC0499D8026C2764240</ms:geom>

      </ms:cimtlocations>
    </gml:featureMember>
    <gml:featureMember>
      <ms:cimtlocations>
        <gml:boundedBy>
        	<gml:Box srsName="EPSG:4326">
        		<gml:coordinates>-92.080000,26.927500 -92.080000,26.927500</gml:coordinates>
        	</gml:Box>

        </gml:boundedBy>
        <ms:msGeometry>
        <gml:Point srsName="EPSG:4326">
          <gml:coordinates>-92.080000,26.927500</gml:coordinates>
        </gml:Point>
        </ms:msGeometry>
        <ms:locationID>8</ms:locationID>
        <ms:time>2003/11/19  0:00:00</ms:time>

        <ms:lat>          27</ms:lat>
        <ms:longitude>         -92</ms:longitude>
        <ms:geom>E6100000010100000085EB51B81E855EC01F85EB51B8764240</ms:geom>
      </ms:cimtlocations>
    </gml:featureMember>
    <gml:featureMember>

      <ms:cimtlocations>
        <gml:boundedBy>
        	<gml:Box srsName="EPSG:4326">
        		<gml:coordinates>-72.079000,27.927300 -72.079000,27.927300</gml:coordinates>
        	</gml:Box>
        </gml:boundedBy>
        <ms:msGeometry>
        <gml:Point srsName="EPSG:4326">

          <gml:coordinates>-72.079000,27.927300</gml:coordinates>
        </gml:Point>
        </ms:msGeometry>
        <ms:locationID>9</ms:locationID>
        <ms:time>2003/11/19  0:00:00</ms:time>
        <ms:lat>          28</ms:lat>

        <ms:longitude>         -72</ms:longitude>
        <ms:geom>E61000000101000000931804560E855EC058CA32C4B1764240</ms:geom>
      </ms:cimtlocations>
    </gml:featureMember>
    <gml:featureMember>
      <ms:cimtlocations>
        <gml:boundedBy>

        	<gml:Box srsName="EPSG:4326">
        		<gml:coordinates>-67.078000,41.927200 -67.078000,41.927200</gml:coordinates>
        	</gml:Box>
        </gml:boundedBy>
        <ms:msGeometry>
        <gml:Point srsName="EPSG:4326">
          <gml:coordinates>-67.078000,41.927200</gml:coordinates>
        </gml:Point>

        </ms:msGeometry>
        <ms:locationID>10</ms:locationID>
        <ms:time>2003/11/19  0:00:00</ms:time>
        <ms:lat>          42</ms:lat>
        <ms:longitude>         -67</ms:longitude>
        <ms:geom>E61000000101000000A245B6F3FD845EC0F46C567DAE764240</ms:geom>

      </ms:cimtlocations>
    </gml:featureMember>
</wfs:FeatureCollection>



<?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
<!DOCTYPE ServiceExceptionReport SYSTEM "http://ogc.dmsolutions.ca/wms/1.1.0/exception_1_1_0.dtd">
<ServiceExceptionReport version="1.1.0">
<ServiceException>
msDrawMap(): Image handling error. Failed to draw layer named &#39;cimtlocations&#39;.
msWFSLayerWhichShapes(): WFS connection error. WFS request produced unexpected output (junk?) for layer cimtlocations.
msBuildWFSLayerPostRequest(): WFS connection error. MapServer supports only WFS 1.0.0 or 0.0.14 (please verify the version metadata wfs_version).
</ServiceException>
</ServiceExceptionReport>

Changed 4 years ago by nsavard

Ref comment:1

The gml in the first part of the previous comment produces the service exception report of the second part. The request that generated the gml is:  http://127.0.0.1:9090/cgi-bin/mswfs_ticket1101?version=1.0.0&service=wfs&request=getfeature&typename=cimtlocations and the request that generated the service exception is:  http://localhost:9090/cgi-bin/mswfs_client_ticket1101?service=wms&version=1.1.0&request=getmap&layers=cimtlocations&srs=EPSG:4326&bbox=-180.0,-90.0,180.0,90.0&format=image/png&width=400&height=300 .

The sql database does not have a password. The command line to verify the mysql database is: ogrinfo MySQL:ticket1101,user=nsavard,host=localhost,port=3306 tables=cimtLocations -al. The command line to access the database is: mysql -D ticket1101 -u nsavard -h localhost .

Add the following symbolic links in the /path/to/fgs/www/cgi-bin directory: 

mswfs_client_ticket1101 -> /home/nsavard/products/mapserver/bugs_testing/mswfs_client_ticket1101
mswfs_ticket1101 -> /home/nsavard/products/mapserver/bugs_testing/mswfs_ticket1101

If you get the following error when accessing mysql database, add a symbolic link from /tmp/mysql.sock to /var/run/mysqld/mysqld.sock ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2)

This error occurs because mysql server is listening on /var/run/mysqld/mysqld.sock but mysql client is sending the data on /tmp/mysql.sock. It happens within fgs environment.

I'll attach the map files, the sql dump and the virtual data file.

Changed 4 years ago by nsavard

map files, virtual data file, sql dump

Note: See TracTickets for help on using tickets.