Page MenuHomePhabricator

GeoData Error Upon Adding Coordinates to a Page: 1054 Unknown column 'gt_lat_int' in 'field list'
Closed, DeclinedPublic

Description

Author: oguloncel

Description:
Hi! I have to let you know that I am a non-specialist, knowing only Matlab programming. Please excuse me if my description is not very helpful. I can provide further information if needed.

"
A database query error has occurred. This may indicate a bug in the software.
Query:
INSERT INTO mw_geo_tags (gt_page_id,gt_id,gt_lat,gt_lon,gt_globe,gt_primary,gt_dim,gt_type,gt_name,gt_country,gt_region,gt_lat_int,gt_lon_int) VALUES ('14',NULL,'26.0666666667','42.0833333333','1','1','1000',NULL,NULL,NULL,NULL,'261','421')
Function: GeoDataHooks::doSmartUpdate
Error: 1054 Unknown column 'gt_lat_int' in 'field list' (localhost)
"
another error:
"Notice: Uncommitted DB writes (transaction from DatabaseBase::query (GeoData::getAllCoordinates)). in /home/username/public_html/includes/db/Database.php on line 3944"

I searched the web but couldn't find a solution to this. Also played around with some parameters but nothing changed. Does anyone have any idea about this problem? Thanks a lot in advance!


Version: REL1_22-branch
Severity: normal
OS: Windows 8
Platform: PC

Details

Reference
bz62127

Event Timeline

bzimport raised the priority of this task from to Needs Triage.Nov 22 2014, 2:51 AM
bzimport added a project: GeoData.
bzimport set Reference to bz62127.
bzimport added a subscriber: Unknown Object (MLST).

Thanks for taking the time to report this!

Which MediaWiki version, which GeoData version, and which database backend is this about?

Quick glance at the code would suggest you're using the database schema that is used when you use it with solr, rather than using the mysql backend.

What is $wgGeoDataBackend set to?

oguloncel wrote:

Thank you for your attention Andre and Sam! My wiki is operated on Godaddy.com hosting.

MediaWiki: 1.22.3
PHP: 5.4.23 (cgi-fcgi)
MySQL: 5.5.32-cll-lve
GeoData: master(latest version)

$wgGeoDataBackend parameter is not set in localsettings.php. On extension page it says default is "db" and not "solr" so I thought it should be okay?

oguloncel wrote:

Upon your message, I have deleted the extension, installed again with $wgGeoDataBackend parameter set to 'db' and updated the database again. The errors stay the same. But on the update report it also said "geo tag table already exists".

Ogul, you'll need to drop that table to let the installer to create it in a proper form. I just checked - GeoData works just fine after installation with default settings.

oguloncel wrote:

Max, do you mean erasing the table by dropping? Can you tell me how I should properly remove it, I'm not sure how to do it.

The thing I don't get is that; why it didn't work properly at first installation? There were no tables during first installation and everything I did was fresh and default? Could this be pointing to some other error?

oguloncel wrote:

Max, I was trying to learn the process you wrote. I am checking phpmyadmin>structure. There are 2 relevant tables:

mw_geo_killlist
mw_geo_tags
mw_geo_updates

with "Browse,Structure,Search,Insert,Empty,Drop" options. Should I "drop" all these files and re-install the GeoData again?

oguloncel wrote:

Max, the problem is not solved. Here what I have done:

  1. Removed require once from localsettings.php
  2. Deleted the GeoData folder from extensions
  3. Dropped the three tables from database via phpmyadmin

mw_geo_killlist
mw_geo_tags
mw_geo_updates

  1. Re-installed GeoData Extension
  2. Added require once to localsettings.php
  3. Updated the software via webupdater. Seen "geo tables created"
  4. Received the same error:

"A database query error has occurred. This may indicate a bug in the software.
Query:
INSERT INTO mw_geo_tags (gt_page_id,gt_id,gt_lat,gt_lon,gt_globe,gt_primary,gt_dim,gt_type,gt_name,gt_country,gt_region,gt_lat_int,gt_lon_int) VALUES ('31',NULL,'26.0666666667','178.766666667','earth','1','1000',NULL,NULL,NULL,NULL,'261','1788')
Function: GeoDataHooks::doSmartUpdate
Error: 1054 Unknown column 'gt_lat_int' in 'field list' (localhost)"

"Notice: Uncommitted DB writes (transaction from DatabaseBase::query (WikiPage::pageData)). in /home/username/public_html/includes/db/Database.php on line 3944"

Any idea, what might be wrong?

oguloncel wrote:

Additional Information: I checked mw_table_geo_tags. There are only 11 parameters there:(gt_page_id,gt_id,gt_lat,gt_lon,gt_globe,gt_primary,gt_dim,gt_type,gt_name,gt_country,gt_region)

But the error message returns 13 parameters, adding (gt_lat_int,gt_lon_int) at the end which doesn't exist in database table. I think this is the source of trouble but I don't know how or why this is happening. I hope this helps.

oguloncel wrote:

Sorry for posting too much here, but I wanted to share what I found for one last time: I checked GeoData/sql/db-backed.sql file in localsettings.php. And there I found the two lost files (gt_lat_int,gt_lon_int) mentioned. So there is a contradiction between the actual database and localsettings.php file. Does this ring a bell?

oguloncel wrote:

Partial Solution(?): I have recognized that during creation of geo tags table, two rows are omitted all the time. I don't know if it's to do with software or my hosting settings. gt_lat_int,gt_lon_int do not show up in sql database. So I added them manually with my hand through phpmyadmin which eliminated the warning. Yet I am not sure if I configured it correctly. My configuration for both are as follows:

type=smallint(6)
null=no
default=none

oguloncel wrote:

Another manifestation of the problem:

"Search around the point with coordinates" api.php?action=query&list=geosearch&gsradius=10000&gscoord=50.923275|6.916073 does not work and returns the following error:

<?xml version="1.0"?>
<api>

<error code="internal_api_error_DBQueryError" info="Exception Caught: A database error has occurred. Did you forget to run maintenance/update.php after upgrading?  See: https://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script&#10;Query: SELECT  page_id,page_namespace,page_title,gt_lat,gt_lon,gt_primary,gt_globe  FROM `mw_page`,`mw_geo_tags` FORCE INDEX (gt_spatial)   WHERE page_namespace = &#039;0&#039; AND gt_globe = &#039;earth&#039; AND (gt_page_id = page_id) AND gt_primary = &#039;1&#039; AND gt_lat_int = &#039;51&#039; AND gt_lon_int = &#039;7&#039; AND (gt_lat>=&#039;50.8333429806&#039;) AND (gt_lat<=&#039;51.0132070194&#039;) AND (gt_lon>=&#039;6.77340540165&#039;) AND (gt_lon<=&#039;7.05874059835&#039;)  &#10;Function: ApiQueryGeoSearchDb::run&#10;Error: 1176 Key &#039;gt_spatial&#039; doesn&#039;t exist in table &#039;mw_geo_tags&#039; (localhost)&#10;" xml:space="preserve">

#0 /home/richardwakefield/public_html/includes/db/Database.php(1039): DatabaseBase->reportQueryError('Key 'gt_spatial...', 1176, 'SELECT page_id...', 'ApiQueryGeoSear...', false)
#1 /home/richardwakefield/public_html/includes/db/Database.php(1496): DatabaseBase->query('SELECT page_id...', 'ApiQueryGeoSear...')
#2 /home/richardwakefield/public_html/includes/api/ApiQueryBase.php(274): DatabaseBase->select(Array, Array, Array, 'ApiQueryGeoSear...', Array, Array)
#3 /home/richardwakefield/public_html/extensions/GeoData/api/ApiQueryGeoSearchDb.php(41): ApiQueryBase->select('ApiQueryGeoSear...')
#4 /home/richardwakefield/public_html/extensions/GeoData/api/ApiQueryGeoSearch.php(13): ApiQueryGeoSearchDb->run()
#5 /home/richardwakefield/public_html/includes/api/ApiQuery.php(275): ApiQueryGeoSearch->execute()
#6 /home/richardwakefield/public_html/includes/api/ApiMain.php(829): ApiQuery->execute()
#7 /home/richardwakefield/public_html/includes/api/ApiMain.php(380): ApiMain->executeAction()
#8 /home/richardwakefield/public_html/includes/api/ApiMain.php(351): ApiMain->executeActionWithErrorHandling()
#9 /home/richardwakefield/public_html/api.php(73): ApiMain->execute()
#10 {main}

</error>
</api>

But, "get a list of coordinates of an article" works well:

You are looking at the HTML representation of the XML format.
HTML is good for debugging, but is unsuitable for application use.
Specify the format parameter to change the output format.
To see the non HTML representation of the XML format, set format=xml.
See the complete documentation, or API help for more information.
<?xml version="1.0"?>
<api>

<query>
  <normalized>
    <n from="Çevrimiçi_Bilgi_Kaynakları" to="Çevrimiçi Bilgi Kaynakları" />
  </normalized>
  <pages>
    <page pageid="31" ns="0" title="Çevrimiçi Bilgi Kaynakları">
      <coordinates>
        <co lat="50.9233" lon="6.91607" primary="" globe="earth" />
      </coordinates>
    </page>
  </pages>
</query>

</api>

oguloncel wrote:

Hey Guys!

I have noticed that the database does not create gt_spatial INDEX. So I also added it manually. And now everything seems working fine, also MobileFrontEnd!

To sum up, the changes I made:

  1. Added gt_lat_int to mw_geo_tags table.
  2. Added gt_lon_int to mw_geo_tags table.
  3. Added gt_spatial with 4 columns ( gt_lat_int, gt_lon_int, gt_lon, gt_primary ) to mw_geo_tags Indexes.

I still don't know if this is a bug or a problem with my personal settings/hosting. I can provide more information if you have any questions.

massimiliano.pisani wrote:

Hi!
I have the same issue, I sent you an email to ask some information

massimiliano.pisani wrote:

(In reply to ogul oncel from comment #13)

Hey Guys!

I have noticed that the database does not create gt_spatial INDEX. So I also
added it manually. And now everything seems working fine, also
MobileFrontEnd!

To sum up, the changes I made:

  1. Added gt_lat_int to mw_geo_tags table.
  2. Added gt_lon_int to mw_geo_tags table.
  3. Added gt_spatial with 4 columns ( gt_lat_int, gt_lon_int, gt_lon,

gt_primary ) to mw_geo_tags Indexes.

I still don't know if this is a bug or a problem with my personal
settings/hosting. I can provide more information if you have any questions.

Hi!
I tried to make the changes you suggested, but I have not figured out exactly how to create tables...
I sent you two pictures through e-mail so you can see them and tell me where I'm wrong.