Can't import correctly the gplanet.sql.zip

[martingrand]

There will always be an error for direct import the 'geoplanet.sql.zip':

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`que' at line 3

If I run the php import method it say I'ts complete, but the database geo_places table will get just one row, whith NULL values, and the geo_placenames wil uploaded by same kind of empty or 0 values. The geo_countries is good! there is ~2,598 row. Butever the api doesn't work. forexample /webservice/get.php?woeid=23424739 will return an empty answer. What can I do?

thx for help!

[twbell]

Thanks for the notice. The SQL was generated using mysqldump, so I suspect some sort of version conflict. Will investigate.

What output are you getting for the import method? It should echo status to stdout.

[twbell]

Try the SQL file I've uploaded temporarily to https://www.dropbox.com/s/6m1lhepltm4vjfg/gplplanet.sql.zip

It was generated using the command:

mysqldump  --max_allowed_packet=1GB --compatible=mysql40 --compact

If it solves your problem, I'll upload it to the repo. Let me know how you get on.

[martingrand]

I was tryed it, but we have a same problem unfortunetly. The import method with php was told me, everything is good, but most of my tables are empty. I was tryed to use directly the sql file, but Mysql told me that notice:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the 
right syntax to use near 'TYPE=MyISAM COMMENT='Cached disambiguation queries from Geoplanet web service'' at line 7 

also this message, when I was try to run sql with mysql40 compatibility mode.

after all, i was tryed the php import method again (with the partially filled database) and it was told me following output, and nothing was changed at the stock of the database.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Import Files Verified Database geo already exists. No modifications Removing old temp files... Importing Yahoo Geoplanet Data Populating placetypes... complete Importing adjacencies data from gplplanet.sql... complete Importing place data from gplplanet.sql... complete Importing alias data from gplplanet.sql... complete Populating places... complete Adding placetype codes to places.. -dropping string placetype index... 
Fatal error: Uncaught exception 'Exception' with message 'db::query Can't DROP 'placetypename_idx'; check that column/key exists (ALTER TABLE `geo_places` DROP INDEX `placetypename_idx`)' in C:\__localhost\_sites\w\engine\geo\class.db.php:73 Stack trace: #0 C:\__localhost\_sites\w\engine\geo\class.geoengine.php(659): db->query('ALTER TABLE `ge...') #1 C:\__localhost\_sites\w\engine\geo\import\class.geoimport.php(237): geoengine->query('ALTER TABLE `ge...') #2 C:\__localhost\_sites\weather\engine\geo\import\import.php(151): geoimport->addPlaceTypeCodes() #3 {main} thrown in C:\__localhost\_sites\w\engine\geo\class.db.php on line 73

[twbell]

Can you share with me the mysql import command you are using please.

[martingrand]

I was used phpMyAdmin. Is it a problem?

[twbell]

I think that could be the problem -- it's a big file and I would not trust a web interface not to choke. You OK to try the cmdln?

[martingrand]

it's ok but never tried earlier. there is my full console history. Was I did something wrong? (the geo database was created manually. At this status it's full empty)

C:\Users\Martin Grand>cd ..\..\Program Files (x86)\EasyPHP-DevServer-13.1VC9\bin
aries\mysql\bin

C:\Program Files (x86)\EasyPHP-DevServer-13.1VC9\binaries\mysql\bin>mysql -u roo
t -p --max_allowed_packet=1GB geo < ..\..\..\data\databases\uploaddir\gplplanet.
sql
Enter password:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma
nual that corresponds to your MySQL server version for the right syntax to use n
ear 'TYPE=MyISAM COMMENT='Cached disambiguation queries from Geoplanet web servi
ce'' at line 7

[martingrand]

I know what maybe the problem. There is no charset informations at the sql. two diferent sql. The first is work, the second is not:

CREATE TABLE IF NOT EXISTS `cache_disambiguate` (
  `q` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'query string',
  `focus` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'focus of query as woeid',
  `woeid` int(10) unsigned NOT NULL COMMENT 'Most likely place returned',
  PRIMARY KEY (`q`,`focus`) USING BTREE,
  KEY `focus_idx` (`focus`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Cached disambiguation queries from Geoplanet web service';

CREATE TABLE `cache_disambiguate` (
  `q` varchar(255) NOT NULL COMMENT 'query string',
  `focus` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'focus of query as woeid',
  `woeid` int(10) unsigned NOT NULL COMMENT 'Most likely place returned',
  PRIMARY KEY (`q`,`focus`),
  KEY `focus_idx` (`focus`)
) TYPE=MyISAM COMMENT='Cached disambiguation queries from Geoplanet web service';

The second is copyed from the sql

[martingrand]

I was created a little snippet to upload the database correctly: Maybe it's helpful:

<?php
set_time_limit(0);
$handle = @fopen("./gplplanet.sql", "r");

$query = '';

if ($handle) {
    $i = 0;
    while (($buffer = fgets($handle, 4096)) !== false) {
        $i++;
        $query .= $buffer;
        if(substr($buffer, -2) === ";\n") {

            $link = mysql_connect('localhost', 'root', '') or die(mysql_error());
            mysql_select_db('geo', $link) or die(mysql_error());

            $query = str_replace("TYPE=MyISAM", "ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci", $query);
            $query = str_replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS", $query);
            $query = str_replace("PRIMARY KEY (`q`,`focus`)", "PRIMARY KEY (`q`,`focus`) USING BTREE", $query);

            $q = mysql_query($query) or die($query . "\n\n" .mysql_error());
            mysql_close($link);
            $query = '';
        }
    }
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
}
?>

[twbell]

Link to SQL version with complete create table syntax: https://www.dropbox.com/sh/kmucg7f929c7lpo/BoCR7Dl7W9

mysqldump --max_allowed_packet=1GB --create-options

[martingrand]

Thank you, it's work, until the mysql tell this:

ERROR 2006 (HY000) at line 702: MySQL server has gone away

It was happend when mysql was tryed to fill geo_descendants. 30,953 row is there, but no more, it always crashing here (for me). I think, this is maybe, becouse that query string is too large. Is it possible? Can I break it to smaller particles some how?

[twbell]

Try increasing your max packet to 2gb