EQEmulator Forums - MySql Database issues

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Development::Database/World Building (https://www.eqemulator.org/forums/forumdisplay.php?f=596)

- - MySql Database issues (https://www.eqemulator.org/forums/showthread.php?t=20877)

MySql Database issues

Does anyone else have trouble running what seems to be simple queries in MySql?

I going through and trying to clean up some stuff in the datbase. Particularly faction tables right now. My Problem is that any of the queries that I want to run to give me the information I need just lock up my server. These queries are really simple and should not have this effect esp when I am able to run what seems to be more complicated queries.

For example:

Code:

SELECT

  npc_faction_id,

  npc_faction.name

FROM

  npc_types

  INNER JOIN spawnentry

    ON npcID=npc_types.id

  INNER JOIN spawn2

    ON spawnentry.spawngroupID=spawn2.spawngroupID

  INNER JOIN  npc_faction

    ON npc_faction.id=npc_types.npc_faction_id

WHERE

  spawn2.zone='qeynos'

GROUP BY

  npc_faction_id,

  npc_faction.name

This query will run with out a problem at all. but if I try and run this:

Code:

SELECT

  npc_faction.id,

  npc_faction.name

FROM

  npc_faction

  LEFT JOIN npc_types

    ON npc_types.npc_faction_id=npc_faction.id

WHERE

  npc_types.id IS NULL

It will lock up my server.

But I can run this query which is the same type of concept:

Code:

SELECT

  id,

  name

FROM

  faction_list

  LEFT JOIN npc_faction_entries

    ON faction_id=id AND npc_faction_id = 2613

WHERE

  npc_faction_id IS NULL

ORDER BY

  name

I have tried doing some searching on the internet but can not find anything as to the cause of this. I have tried this on MySql 4 and 5 and have had problem with other queries as well. Usually they have something to do with LEFT or RIGHT JOINS looking for NULL values. Any information on this would be appreciated

ok try

SELECT
npc_faction.id,
npc_faction.name
FROM
npc_faction,npc_types
WHERE (npc_types.npc_faction_id=npc_faction.id)
AND npc_types.id is null

I'm wondering why you would check for 'is null' when the schema dissallows null entries in ncp_types.id

GeorgeS

The point of the Query is to find all Faction Tables (npc_faction entries) that are not referenced in npc_types.npc_faction_id.

By doing a LEFT JOIN to npc_types I am telling it I want all npc_faction.name and id regardless of whether or not it is in npc_types (hard for me to explain this so I hope you understand what I mean.)

So I am not really checking for NULLs in the npc_types table but rather for rows that a return a NULL in place of a npc_type.id because that means they are not being referenced in the table. It would be that same as this query:

Code:

SELECT

  npc_faction.id,

  npc_faction.name

FROM

  npc_faction

WHERE

  npc_faction.id NOT IN(SELECT npc_types.npc_faction_id FROM npc_types)

This query however also locks up my server and MySql 4 does not support sub queries so even if it did work it would not do me any good in the end.

ok figured out the partial problem. I ran the query in my program and the reason I think it locked up is because it used up all memory and took forever to run.

I've got a compiler which creates the standalone .exe from a query, but it does not work as an .exe.

I solved this problem by running the query recursively, but it took me about an hour to run. It did find many 'null's however, which suprised me.

When I get this thing to work, i'll fwd you the solution.
George

Yeah, I can run the query and it will take about an hour for it to run. I just wanted to know if anyone else was having this issue or if it was just me. I know this is possible to do and I know how to do it programatically I was just hoping that I would not have to do it that way because of the time it will take, it is kind of hard to make it a filter in your DB editor if it takes that long to run. I am just used to working with MS SQL Server and running a query like this never gives me a problem but that is a discussion for a different time and place. Anyways thanks for the feedback and confirmation of my problem.