Has anyone gotten Export Directly to MySQL Working?

jarmenia · 04-01-2017, 08:59 PM

I'm able to connect to the MySQL server running on my machine using MySQL workbench. When I configure OOTP to connect to it I get an error that its unable to connect. Has anyone gotten this option to work?

MrMoose · 03-15-2018, 06:40 PM

I, too, have MySQL installed on both my Windows & Mac platform PCs and would like to export some of the OOTP data to MySQL to analyze some of it in my own way.

The Import/Export section of the manual makes reference to exporting a MySQL dump file, but I can't even get it to show the supposed (referenced) options to do so, let alone the much larger selection menu you screen shot into your post.

Where in the world did you find that menu?? I'd really like to use it! If I can, I might be able to experiment with your question, too!

Thanks a bunch in advance!

stealofhome · 03-15-2018, 07:59 PM

It should be available under game settings, database tab, then database tools drop down

jarmenia · 03-16-2018, 08:45 AM

You need to be logged in as commissioner to have access to that screen.

MrMoose · 03-16-2018, 03:35 PM

Just a quick thought without any review/testing...with the menu option title being export data directly, I'm wondering if that one only works if the database schema structure already exists in your MySQL instance. Do you already have the schema set up in your MySQL instance? Just a SWAG (Silly Wild-Arsed Guess)!

jarmenia · 03-16-2018, 03:46 PM

For the record, I have never actually gotten this to work and I consider myself fairly technical.

NoOne · 03-16-2018, 07:26 PM

the exported file should crete tables and add data...

isn't it just a massive sql statement? or is it some proprietery thing?

instead try to use create sql dump for mysql...

access can use that too. or d/l hiedisql (free) and then run that in a query tab.. *** make sure it's creating tables and such within a dB, or oyu have to create a dB first, then run teh sql command. all that info will be at top of sql query

can copy/paste or load from file (name.sql) etc..

*** i have never tried to do this, for the record.. .but took a class 20 years ago and also do similar stuff with an eqemu dB.. so not just theory.

NoOne · 03-16-2018, 08:09 PM

while replying to another post i came across this:

db_structure_complete_ootp18_mysql.txt

it is a file in

<ootp install folder>\data\database

or wherever your data folder resides. no idea of default anymore.. i don't let it get scattered into 2 directories on same drive... each on 2 different drives is viable reason for such a thing... but not on the same drive. (probably has to do with program files being a "system" directory and easier if located elsewhere??)

Anyway, i iopened it up and it is the EXACT sql statemetns you need to create a new db.. there's one for access too.

a snippet:

CREATE TABLE IF NOT EXISTS `leagues` (`league_id` INT, `name` VARCHAR(50), `abbr` VARCHAR(50), `nation_id` INT, `language_id` INT, `gender` <SNIP CUT short>

jarmenia · 03-16-2018, 08:24 PM

Quote:

Originally Posted by NoOne

the exported file should crete tables and add data...

isn't it just a massive sql statement? or is it some proprietery thing?

instead try to use create sql dump for mysql...

access can use that too. or d/l hiedisql (free) and then run that in a query tab.. *** make sure it's creating tables and such within a dB, or oyu have to create a dB first, then run teh sql command. all that info will be at top of sql query

can copy/paste or load from file (name.sql) etc..

*** i have never tried to do this, for the record.. .but took a class 20 years ago and also do similar stuff with an eqemu dB.. so not just theory.

Not sure if you were replying to me but I was vague so I will clarify. I have never got the direct connection to MySQL to work. I have gotten the MySQL dump to *.sql files to be created and successfully import. I was looking forward to the direct connection to save me a step but was never able to resolve the issue.

I would also love if the dumps were available to online league users but they are not, only to solo and commishes of online leagues.

NoOne · 03-17-2018, 03:49 PM

it says you can make it write a log file for sql errors -- good place to start.

is the port open and correct in sql.ini? or mysql.ini etc?

correct login and password associated with a dB user... like root/password (if this is open to internent in any way don't use 'that' lol) you must create a user... it won't just "be there" you can google the sql statemnt needed to make one from a sql CLI or a front-end like access.

database name is case sensitive, if notmistaken, too.

try 127.0.0.1 then "localhost" no quotes. if you are on a LAN with a static local address, you can use your 192.168.0.51 or w/e... but, that can often change each time you connect to the router.. i think newer routers attempt to maintain the same address, but you can also lock them to a mac address in the routers home-page. check router manual for URL settings.

shouldn't need firewall setting if bouncing off localhost loop. but, just in case you can make one and set the security for LAN only and not be too concerned about a security problem.

if none of this works, it's only 2 seconds longer to import the exported data files to to replace/update table data etc. it's still 95% automated. in fact i think you can "synchronize" wiht the sql files.. then after you export from game as normal (it uses same named file in same location) you load up mysql and synchronize with the sql files. probably can make it occur each time you open it up or anytime file changes? anyway, roundabout way to make it basically the same thing. (url at bottom)

i guess you could post the log here and could further troubleshoot, but no gaurantees. maybe i'll try to do it later, but i don't want ot screw up my other dB, lol. my knowledge is more accurately defined as "dangerous" than anything else relative to DBs.

https://dev.mysql.com/doc/workbench/...onization.html

04-01-2017, 08:59 PM	#1
jarmenia Hall Of Famer Join Date: Mar 2002 Location: Tampa, FL USA Posts: 4,503	Has anyone gotten Export Directly to MySQL Working? I'm able to connect to the MySQL server running on my machine using MySQL workbench. When I configure OOTP to connect to it I get an error that its unable to connect. Has anyone gotten this option to work? __________________ When is good enough, good enough?

03-15-2018, 06:40 PM	#2
MrMoose Bat Boy Join Date: Mar 2018 Posts: 15	Where Do You Even Find This Menu? I, too, have MySQL installed on both my Windows & Mac platform PCs and would like to export some of the OOTP data to MySQL to analyze some of it in my own way. The Import/Export section of the manual makes reference to exporting a MySQL dump file, but I can't even get it to show the supposed (referenced) options to do so, let alone the much larger selection menu you screen shot into your post. Where in the world did you find that menu?? I'd really like to use it! If I can, I might be able to experiment with your question, too! Thanks a bunch in advance!

03-16-2018, 08:45 AM	#4
jarmenia Hall Of Famer Join Date: Mar 2002 Location: Tampa, FL USA Posts: 4,503	You need to be logged in as commissioner to have access to that screen. __________________ When is good enough, good enough?

03-16-2018, 03:46 PM	#6
jarmenia Hall Of Famer Join Date: Mar 2002 Location: Tampa, FL USA Posts: 4,503	For the record, I have never actually gotten this to work and I consider myself fairly technical. __________________ When is good enough, good enough?

03-16-2018, 07:26 PM	#7
NoOne Banned Join Date: Apr 2015 Posts: 7,273 Infractions: 1/2 (4)	the exported file should crete tables and add data... isn't it just a massive sql statement? or is it some proprietery thing? instead try to use create sql dump for mysql... access can use that too. or d/l hiedisql (free) and then run that in a query tab.. * make sure it's creating tables and such within a dB, or oyu have to create a dB first, then run teh sql command. all that info will be at top of sql query can copy/paste or load from file (name.sql) etc.. * i have never tried to do this, for the record.. .but took a class 20 years ago and also do similar stuff with an eqemu dB.. so not just theory. Last edited by NoOne; 03-16-2018 at 07:28 PM.

03-15-2018, 07:59 PM	#3
stealofhome Hall Of Famer Join Date: Apr 2014 Posts: 2,282	It should be available under game settings, database tab, then database tools drop down

03-16-2018, 03:35 PM	#5
MrMoose Bat Boy Join Date: Mar 2018 Posts: 15	Just a quick thought without any review/testing...with the menu option title being export data directly, I'm wondering if that one only works if the database schema structure already exists in your MySQL instance. Do you already have the schema set up in your MySQL instance? Just a SWAG (Silly Wild-Arsed Guess)!

03-16-2018, 08:09 PM	#8
NoOne Banned Join Date: Apr 2015 Posts: 7,273 Infractions: 1/2 (4)	while replying to another post i came across this: db_structure_complete_ootp18_mysql.txt it is a file in <ootp install folder>\data\database or wherever your data folder resides. no idea of default anymore.. i don't let it get scattered into 2 directories on same drive... each on 2 different drives is viable reason for such a thing... but not on the same drive. (probably has to do with program files being a "system" directory and easier if located elsewhere??) Anyway, i iopened it up and it is the EXACT sql statemetns you need to create a new db.. there's one for access too. a snippet: CREATE TABLE IF NOT EXISTS `leagues` (`league_id` INT, `name` VARCHAR(50), `abbr` VARCHAR(50), `nation_id` INT, `language_id` INT, `gender` <SNIP CUT short>

03-17-2018, 03:49 PM	#10
NoOne Banned Join Date: Apr 2015 Posts: 7,273 Infractions: 1/2 (4)	it says you can make it write a log file for sql errors -- good place to start. is the port open and correct in sql.ini? or mysql.ini etc? correct login and password associated with a dB user... like root/password (if this is open to internent in any way don't use 'that' lol) you must create a user... it won't just "be there" you can google the sql statemnt needed to make one from a sql CLI or a front-end like access. database name is case sensitive, if notmistaken, too. try 127.0.0.1 then "localhost" no quotes. if you are on a LAN with a static local address, you can use your 192.168.0.51 or w/e... but, that can often change each time you connect to the router.. i think newer routers attempt to maintain the same address, but you can also lock them to a mac address in the routers home-page. check router manual for URL settings. shouldn't need firewall setting if bouncing off localhost loop. but, just in case you can make one and set the security for LAN only and not be too concerned about a security problem. if none of this works, it's only 2 seconds longer to import the exported data files to to replace/update table data etc. it's still 95% automated. in fact i think you can "synchronize" wiht the sql files.. then after you export from game as normal (it uses same named file in same location) you load up mysql and synchronize with the sql files. probably can make it occur each time you open it up or anytime file changes? anyway, roundabout way to make it basically the same thing. (url at bottom) i guess you could post the log here and could further troubleshoot, but no gaurantees. maybe i'll try to do it later, but i don't want ot screw up my other dB, lol. my knowledge is more accurately defined as "dangerous" than anything else relative to DBs. https://dev.mysql.com/doc/workbench/...onization.html Attached Images Last edited by NoOne; 03-17-2018 at 05:29 PM.