Blog

Change timeouts in my.ini (mysql ini file in xampp) change the following values to:

SET GLOBAL connect_timeout = 600;
SET GLOBAL net_read_timeout = 600;

Now restart mysql in xampp.

Put .sql file to import somewhere where you can find it

Put the .sql file into c:/xampp/mysql/

Now from the command prompt at c:/xampp/mysql/bin, type

mysql -u root -p … and hit enter

Now hit enter at password prompt as no password is needed on XAMPP default root user in mysql

Now type use [database]

Now we want to change the timeout settings within mysql:

SHOW VARIABLES LIKE "%timeout";

we want connect_timeout to be 600 (seconds) and also net_read_timeout

So type

SET GLOBAL connect_timeout = 600; 
SET GLOBAL net_read_timeout = 600;

Now crucially, at the top of the .sql (worldcities) database from simple maps, add this line ABOVE the create table statement

SET NAMES utf8mb4;

This will stop the database table being filled up with garbage characters when there are language accents in the words.

Now in the console at MariaDB[[databasename]]> type

SET autocommit=0 ; source ../[mysqlfilename].sql ; COMMIT;

and the file will start being executed

Now lets create a copy of this only containing the French country

DROP TABLE IF EXISTS worldcitiesfrance;
CREATE TABLE worldcitiesfrance (
  city VARCHAR(120),
  city_ascii VARCHAR(120),
  city_alt VARCHAR(1000),
  lat FLOAT,
  lng FLOAT,
  country VARCHAR(120),
  iso2 VARCHAR(2),
  iso3 VARCHAR(3),
  admin_name VARCHAR(120),
  admin_name_ascii VARCHAR(120),
  admin_code VARCHAR(6),
  admin_type VARCHAR(27),
  capital VARCHAR(7),
  density FLOAT,
  population FLOAT,
  population_proper FLOAT,
  ranking INT,
  timezone VARCHAR(120),
  same_name VARCHAR(5),
  id VARCHAR(10)
);

Now copy only the france data from the original table into the new one.

INSERT INTO worldcitiesfrance
SELECT * FROM worldcities
WHERE worldcities.iso2="FR";

———–

Changing new worldcitiesfrance table to work in pmf

-> = needs to become

city_ascii -> city_name_ascii
admin_name_ascii -> state
lat -> latitude
lng -> longitude
id -> france_full_search_id

On displays of the locations, we will show the names with characters

So

“city” will be used as the city/cillage DISPLAY (this is the country with french accents)
“admin_name” will be used as the state DISPLAY (this is the state with french accents)
“country” will be used as the country DISPLAY (this is FRANCE)

Now rename the original francefullsearch to francefullsearch_original

RENAME TABLE francefullsearch TO francefullsearch_original

now rename the new table to francefullsearch

RENAME TABLE worldcitiesfrance TO francefullsearch