Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Possible MySQL 8.0.0 issue with GIS display #12865

Closed
ibennetch opened this issue Dec 31, 2016 · 5 comments
Closed

Possible MySQL 8.0.0 issue with GIS display #12865

ibennetch opened this issue Dec 31, 2016 · 5 comments
Assignees
@williamdes
Labels
Bug A problem or regression with an existing feature has-pr An issue that has a pull request pending that may fix this issue. The pull request may be incomplete
Projects
issues
Milestone
4.9.0.1

Comments

@ibennetch
Copy link
Member

ibennetch commented Dec 31, 2016 •
edited

I'll start off my saying that my client library doesn't match my MySQL version, so that definitely could be the cause of this, but I’m trying PMA master with MySQL 8.0.0 and GIS is broken. It would be great if someone with a better configuration could confirm or refute this.

  1. Browse a table with GIS data
  2. At the bottom of the results, click the "Visualize GIS data" link

MySQL: 8.0.0-dmr MySQL Community Server
Database client version: libmysql - 5.5.53
PHP extension: mysqli curl mbstring
PHP version: 5.6.29-0+deb8u1

This works okay with my MySQL 5.5 installation

Warning in ./libraries/dbi/DBIMysqli.php#257
mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

Backtrace

./libraries/dbi/DBIMysqli.php#257: mysqli_fetch_array(
boolean false,
integer 1,
)
./libraries/DatabaseInterface.php#2425: PMA\libraries\dbi\DBIMysqli->fetchAssoc(boolean false)
./libraries/gis/GISVisualization.php#214: PMA\libraries\DatabaseInterface->fetchAssoc(boolean false)
./libraries/gis/GISVisualization.php#140: PMA\libraries\gis\GISVisualization->_fetchRawData()
./libraries/gis/GISVisualization.php#88: PMA\libraries\gis\GISVisualization->__construct(
string 'SELECT * FROM `capitals`',
array,
integer 25,
string '0',
)
./libraries/controllers/table/TableGisVisualizationController.php#152: PMA\libraries\gis\GISVisualization::get(
string 'SELECT * FROM `capitals`',
array,
integer 25,
string '0',
)
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#589
Undefined index: maxX

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#589
Undefined index: minX

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#590
Undefined index: maxY

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#590
Undefined index: minY

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#602
Undefined index: minX

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#604
Undefined index: maxY

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#604
Undefined index: minY

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#611
Undefined index: minX

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#612
Undefined index: maxX

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#613
Undefined index: minY

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#614
Undefined index: maxY

Backtrace

./libraries/gis/GISVisualization.php#302: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/gis/GISVisualization.php#318: PMA\libraries\gis\GISVisualization->_svg()
./libraries/gis/GISVisualization.php#505: PMA\libraries\gis\GISVisualization->asSVG()
./libraries/controllers/table/TableGisVisualizationController.php#207: PMA\libraries\gis\GISVisualization->toImage(string 'svg')
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#589
Undefined index: maxX

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#589
Undefined index: minX

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#590
Undefined index: maxY

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#590
Undefined index: minY

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#602
Undefined index: minX

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#604
Undefined index: maxY

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#604
Undefined index: minY

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#611
Undefined index: minX

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#612
Undefined index: maxX

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#613
Undefined index: minY

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
Notice in ./libraries/gis/GISVisualization.php#614
Undefined index: maxY

Backtrace

./libraries/gis/GISVisualization.php#421: PMA\libraries\gis\GISVisualization->_scaleDataSet(array)
./libraries/controllers/table/TableGisVisualizationController.php#208: PMA\libraries\gis\GISVisualization->asOl()
./tbl_gis_visualization.php#43: PMA\libraries\controllers\table\TableGisVisualizationController->indexAction()
nijel added a commit that referenced this issue Jan 2, 2017
@nijel
Check if query succeeded before processing results
945fba1 
Issue #12865

Signed-off-by: Michal Čihař <michal@cihar.com>
@nijel nijel mentioned this issue Jan 2, 2017
Closed
@nijel nijel added the Bug A problem or regression with an existing feature label Jan 2, 2017
@ibennetch
Copy link
Member Author

This occurs on the newly upgraded demo server, so I think it's safe to say it's an actual bug rather than an artifact of my installation.

MySQL 8.0.0-dmr
PHP 7.0.16-3

@nijel
Copy link
Contributor

nijel commented Mar 9, 2017

Probably _modifySqlQuery generates SQL not compatible with 8.0.0...

@peterbaumert
Copy link

peterbaumert commented Sep 3, 2018 •
edited by williamdes

As a workaround you can edit the file "libraries/classes/Gis/GisVisualization.php":

line 175:

- $modified_query .= 'ASTEXT('
-            . Util::backquote($this->_userSpecifiedSettings['spatialColumn'])
-            . ') AS ' . Util::backquote(
-                $this->_userSpecifiedSettings['spatialColumn']
-            )
-            . ', ';`
+ $modified_query .= 'ST_ASTEXT(ST_SRID('
+            . Util::backquote($this->_userSpecifiedSettings['spatialColumn'])
+            . ',0)) AS ' . Util::backquote(
+                $this->_userSpecifiedSettings['spatialColumn']
+            )
+            . ', ';

and line 183:

- $modified_query .= 'SRID('
-            . Util::backquote($this->_userSpecifiedSettings['spatialColumn'])
-            . ') AS ' . Util::backquote('srid') . ' ';`
+ $modified_query .= 'ST_SRID('
+            . Util::backquote($this->_userSpecifiedSettings['spatialColumn'])
+            . ') AS ' . Util::backquote('srid') . ' ';

@williamdes
Copy link
Member

https://demo.phpmyadmin.net/QA_4_8/sql.php?db=opengis&table=gis_all&pos=0

@williamdes williamdes self-assigned this Apr 3, 2019
@williamdes
Copy link
Member

williamdes commented Apr 3, 2019 •
edited

https://dev.mysql.com/doc/refman/5.6/en/gis-general-property-functions.html#function_srid
https://dev.mysql.com/doc/refman/5.6/en/gis-format-conversion-functions.html#function_st-astext
https://mariadb.com/kb/en/library/mysqlmariadb-spatial-support-matrix/
https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html
https://mariadb.com/kb/en/library/st_srid/
https://mariadb.com/kb/en/library/st_astext/

@williamdes williamdes added this to the 4.8.6 milestone Apr 3, 2019
williamdes added a commit to williamdes/phpmyadmintest that referenced this issue Apr 3, 2019
@williamdes
Fix phpmyadmin#14383 phpmyadmin#12865 - Fix GIS functions for mysql 8.0
e281438 
Fixes: phpmyadmin#14383
Fixes: phpmyadmin#12865
Signed-off-by: William Desportes <williamdes@wdes.fr>
@williamdes williamdes mentioned this issue Apr 3, 2019
Merged
@williamdes williamdes added the has-pr An issue that has a pull request pending that may fix this issue. The pull request may be incomplete label Apr 3, 2019
@williamdes williamdes added this to ready to merge in issues Apr 29, 2019
williamdes added a commit that referenced this issue May 1, 2019
@williamdes
Fix #14383 #12865 - Fix GIS functions for mysql 8.0
89f8b16 
Fixes: #14383
Fixes: #12865
Pull-request: #15149
Signed-off-by: William Desportes <williamdes@wdes.fr>
williamdes added a commit that referenced this issue May 1, 2019
@williamdes
Add ChangeLog entry for #14383 and #12865
5e37829 
[ci skip]
Signed-off-by: William Desportes <williamdes@wdes.fr>
issues automation moved this from ready to merge to Closed May 1, 2019
@github-actions github-actions bot locked as resolved and limited conversation to collaborators Jun 22, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees

@williamdes williamdes

Labels
Bug A problem or regression with an existing feature has-pr An issue that has a pull request pending that may fix this issue. The pull request may be incomplete
Projects
issues
  
Closed
Milestone
4.9.0.1
Development

No branches or pull requests
4 participants
@nijel @peterbaumert @ibennetch @williamdes