These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Mysql Conversion of 1.6 SDE. Innodb + XLS dump
Author
Previous Topic Next Topic
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#1 - 2012-03-28 20:13:54 UTC  |  Edited by: Steve Ronuken
That's a new copy of the 1.6 dump converted to mysql, then dumped and bzip2ed for people to download.

Individual files, as well as one large one.

Table format is innodb. no additional indexes added.


There's also the individual tables dumped into xls files.


http://www.fuzzwork.co.uk/dump/

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Primitive Complexity
School of Applied Knowledge
Caldari State
#2 - 2012-03-28 20:26:22 UTC
Another MySQL conversion already posted at https://forums.eveonline.com/default.aspx?g=posts&t=87845
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2012-03-28 20:29:06 UTC
Primitive Complexity wrote:
Another MySQL conversion already posted at https://forums.eveonline.com/default.aspx?g=posts&t=87845


Primary difference is table format. That one is MyISAM, mine is InnoDB. These days, InnoDB's actually winning the speed race (in general use cases)

Also, this way I know what's in mine Blink

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Luminocity
The Dark Revenants
PLEASE NOT VIOLENCE OUR BOATS
#4 - 2012-03-28 20:57:38 UTC  |  Edited by: Luminocity
Steve Ronuken wrote:
Primitive Complexity wrote:
Another MySQL conversion already posted at https://forums.eveonline.com/default.aspx?g=posts&t=87845


Primary difference is table format. That one is MyISAM, mine is InnoDB. These days, InnoDB's actually winning the speed race (in general use cases)

Also, this way I know what's in mine Blink
I disagree. While InnoDB might be better for dealing with lots of inserts/updates, from my experience MyISAM is the preferred choice for large databases being mainly used for read-only operations (it is the *static* data dump after all Pirate). Data integrity could be arguably in InnoDB-s favor but I don't see it's benefit for this..

Your conversion also seems to use extended inserts and the latin1 charset for some reason. Otherwise good effort..
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#5 - 2012-03-28 23:21:20 UTC
https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance

Suggested to me that innodb might have taken the lead.

Though I have to admit, in the end, I'm not using it anywhere near enough for it to make a difference.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
6ie
The Kiwis
#6 - 2012-03-29 11:07:47 UTC
My PC locks up all the time, and the recovery ability of INNODB is essential for me :)

Oh - and thanks for this dump!
Dragonaire
Here there be Dragons
#7 - 2012-03-29 14:54:29 UTC  |  Edited by: Dragonaire
If you really want/need scalability you should look at using something like MariaDB with TokuDB engine as they both were designed to over come the bugs and performance issues that you run into with the standard version of MySQL and the current engines. TokuDB is free to use up to 50GB so very few of the current Eve projects I've seen should have to pay to use it Blink Just in some limited test I did with them in the past they even sped up small datasets of a few MB like groups of API data in Yapeal or the SDD.

Finds camping stations from the inside much easier. Designer of Yapeal for the Eve API. Check out the Yapeal PHP API Library thread.
D Program
#8 - 2012-03-30 20:18:12 UTC  |  Edited by: D Program
Thanks for posting!

But...

Quote:
SQL query:

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;


MySQL said:

#1231 - Variable 'time_zone' can't be set to the value of 'NULL'

What is this sorcery?

http://www.eve-cost.eu
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#9 - 2012-03-30 23:15:08 UTC
D Program wrote:
Thanks for posting!

But...

Quote:
SQL query:

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;


MySQL said:

#1231 - Variable 'time_zone' can't be set to the value of 'NULL'



uhhhhh.

How are you trying to import it? phpmyadmin, with a retry on timeout, by any chance? because by the looks of it, it won't work, due to how it continues.

Edit that line out, and it should work. I'd recommend a editor like notepad++ to handle the massive file.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Snarf Aldes
University of Caille
Gallente Federation
#10 - 2012-03-31 12:58:44 UTC
Thnx, worked like a charm.

Creator of Eve Addicts
The Offerer
Doomheim
#11 - 2012-04-01 09:10:03 UTC
Thank you!

I mainly use InnoDB at work so it's easier for me to use it for Eve development.
Vicious Vixxen
Caldari Provisions
Caldari State
#12 - 2012-04-02 20:21:49 UTC
Well done, much better than the one I tried to convert.

It seems that the agent qualities are all 20 in this data, which is annoying. I see that it is this way in the original .BAK file as well. Ugh Is there a source out there that has the correct agent qualities?

BTW +1 for InnoDB
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#13 - 2012-04-02 20:22:51 UTC
Agents are all quality 20 now. Have been for a while. (Since before I started playing)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Vicious Vixxen
Caldari Provisions
Caldari State
#14 - 2012-04-02 22:21:41 UTC
Smodab Ongalot
The Scope
Gallente Federation
#15 - 2012-04-03 15:04:25 UTC
rockin. thanks for xls tables. Means I can put of learning SQL for yet another project! :P
Vicious Vixxen
Caldari Provisions
Caldari State
#16 - 2012-04-06 22:59:58 UTC
I'm having issues with the data, perhaps I don't quite understand how the schema works. I can't seem to find where in the database the material requirements for some of the "invTypes" are. For instance, the control tower materials seem to be missing


mysql> select typeID, typeName from invTypes where typeName = 'caldari control tower medium'
+--------+------------------------------
| typeID | typeName
+--------+------------------------------
| 20061 | Caldari Control Tower Medium
+--------+------------------------------
1 row in set (0.02 sec

mysql> select * from invTypeMaterials where materialTypeID = 20061
Empty set (0.01 sec

mysql> select * from invTypeMaterials where typeID = 20061
Empty set (0.00 sec

So I tried to go about it another way and see what everything that "capital construction parts" is used to makes, which should include control towers and capital ships, but I can't seem to get any of the control towers

mysql> select typeID, typeName from invTypes where typeName = "Capital Construction Parts"
+--------+----------------------------
| typeID | typeName
+--------+----------------------------
| 21037 | Capital Construction Parts
+--------+----------------------------
1 row in set (0.02 sec

mysql> select invTypes.typeName, invTypes.published from invTypes, invTypeMaterials where invTypes.typeID = invTypeMaterials.typeID and invTypeMaterials.materialTypeID = 21037
+------------------------+-----------
| typeName | published
+------------------------+-----------
| Erebus | 1
| Revenant | 1
| Leviathan | 1
| Orbital Command Center | 0
| Avatar | 1
| Revelation | 1
| Naglfar | 1
| Moros | 1
| Phoenix | 1
| Providence | 1
| Charon | 1
| Obelisk | 1
| Fenrir | 1
| Hel | 1
| Archon | 1
| Ragnarok | 1
| Thanatos | 1
| Nyx | 1
| Chimera | 1
| Wyvern | 1
| Aeon | 1
| Nidhoggur | 1
| Rorqual | 1
| Orca | 1
+------------------------+-----------
24 rows in set (0.08 sec

Where are the control towers????
Philderbeast
Native Freshfood
Minmatar Republic
#17 - 2012-04-06 23:32:33 UTC
the materials for control towers are in the ramTypeRequirements table

so the query would look something like

SELECT t.typeName, rt.quantity
FROM invTypes AS t
INNER JOIN ramTypeRequirements AS rt ON t.typeId = rt.requiredTypeID
WHERE rt.typeID =2778
Vicious Vixxen
Caldari Provisions
Caldari State
#18 - 2012-04-06 23:41:01 UTC
very nice, so the invTypeMaterials is only for reprocessing?

ram is Research And Manufacturing... got it.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#19 - 2012-04-07 01:33:44 UTC
It's a little more complex than that. Hence the calculator on my site. Blink

You have Base Materials, which are in invTypeMaterials. These are affected by ME and PE waste.

You have Extra materials which are in ramTypeRequirements. These aren't affected by ME waste. But they are affected by PE waste, if they also exist in the Base Materials.

In addition, if something is in ramTypeRequirements, and is marked as recyclable, you remove the materials to construct it from the base materials, to a minimum of 0.


The two queries:
select typeid,name,greatest(0,sum(quantity)) quantity from (select invTypes.typeid typeid,invTypes.typeName name,quantity from invTypes,invTypeMaterials where invTypeMaterials.materialTypeID=invTypes.typeID and invTypeMaterials.TypeID=? union select invTypes.typeid typeid,invTypes.typeName name,invTypeMaterials.quantity*r.quantity*-1 quantity from invTypes,invTypeMaterials,ramTypeRequirements r,invBlueprintTypes bt where invTypeMaterials.materialTypeID=invTypes.typeID and invTypeMaterials.TypeID =r.requiredTypeID and r.typeID = bt.blueprintTypeID AND r.activityID = 1 and bt.productTypeID=? and r.recycle=1) t group by typeid,name

and
SELECT t.typeName tn, r.quantity qn, r.damagePerJob dmg,t.typeID typeid FROM ramTypeRequirements r,invTypes t,invBlueprintTypes bt,invGroups g where r.requiredTypeID = t.typeID and r.typeID = bt.blueprintTypeID AND r.activityID = 1 and bt.productTypeID=? and g.categoryID != 16 and t.groupID = g.groupID



The bind variables are all the type id of what you're making (2 times in the first query. Once in the second)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter