Query Help, please.

[lishawj]

If you want to receive support we need this info to help you properly.
» Find Revision
L2J Revision Number:3216
L2JDP Revision Number:6302

Please tell me what I am missing here guys. Since my last post regarding my antivirus program detecting malware/virus in the L2J_datapack.zip file I had decided to start up a second fresh test server just for testing purposes. Everything is working so far but I am unable to INSERT values into the accessLevels column inside the admin_command_access_rights table. Previously, a friend had given me a query file to add GM Access levels but I had misplaced that file. Below are the steps I had already taken and please let me know if I missed something.


1) Granted Select, Insert, Update access rights for the table Admin_Command_Access_Rights.

2) Granted Select, Insert, Update, access rights for the column AccessLevels.

3) Punched in these two query:
query #1: INSERT IGNORE INTO `admin_command_access_rights` VALUES ('admin_admin','2');
result: Success, 0 row affected

Punched in another query:
query #2: INSERT IGNORE INTO `admin_command_access_rights` (adminCommand, accessLevels) VALUES ('admin_admin','2');
result: Success, 0 row affected

4)I am able to manual change the value using NaviCat or any other graphical user interface. However, doing this by hand is going to take way too much time and I had created a series of query per GM Access Level. I simply want to run these queries and be done with it in a few seconds.

So, what am I missing here? Why is my query not inserting the values into Accesslevels? I double check and am positive I granted Select, Insert, and Update rights to the table and column. Thank you for the second pair of eyes...so late here and so tired....

[KenshinX]

If you want to receive support we need this info to help you properly.
» Find Revision
L2J Revision Number:3216
L2JDP Revision Number:6302

Please tell me what I am missing here guys. Since my last post regarding my antivirus program detecting malware/virus in the L2J_datapack.zip file I had decided to start up a second fresh test server just for testing purposes. Everything is working so far but I am unable to INSERT values into the accessLevels column inside the admin_command_access_rights table. Previously, a friend had given me a query file to add GM Access levels but I had misplaced that file. Below are the steps I had already taken and please let me know if I missed something.


1) Granted Select, Insert, Update access rights for the table Admin_Command_Access_Rights.

2) Granted Select, Insert, Update, access rights for the column AccessLevels.

3) Punched in these two query:
query #1: INSERT IGNORE INTO `admin_command_access_rights` VALUES ('admin_admin','2');
result: Success, 0 row affected

Punched in another query:
query #2: INSERT IGNORE INTO `admin_command_access_rights` (adminCommand, accessLevels) VALUES ('admin_admin','2');
result: Success, 0 row affected

4)I am able to manual change the value using NaviCat or any other graphical user interface. However, doing this by hand is going to take way too much time and I had created a series of query per GM Access Level. I simply want to run these queries and be done with it in a few seconds.

So, what am I missing here? Why is my query not inserting the values into Accesslevels? I double check and am positive I granted Select, Insert, and Update rights to the table and column. Thank you for the second pair of eyes...so late here and so tired....

Don't use IGNORE keyword you wont be able to know if you statement has an error. I've tested your SQL statement on mine results are the same, but when i remove keyword IGNORE it say's "MySQL Database Error: Duplicate entry 'admin_admin' for key 'PRIMARY'"

Just use simple Insert statement.

Friendly advice:
If you want try to use Toad for MySQL very nice and it's freeware. And Awesome for me

[lishawj]

Don't use IGNORE keyword you wont be able to know if you statement has an error. I've tested your SQL statement on mine results are the same, but when i remove keyword IGNORE it say's "MySQL Database Error: Duplicate entry 'admin_admin' for key 'PRIMARY'"

Just use simple Insert statement.

Friendly advice:
If you want try to use Toad for MySQL very nice and it's freeware. And Awesome for me

Thank you for your response. I had removed the IGNORE keyword from the statement and I do see the error now but I am still unable to insert the value required into AccessLevels via query. I may had set access to the table and column incorrectly but what else is there to set beside granting access for Insert, Select, Create, etc? How else may I structure the query statement so that I do not get the duplicate entry for key PRIMARY?

Thank you.

[janiii]

the duplicate enrtry error means, that there is already such data in the table. if you want to replace it, then make REPLACE instead of INSERT.

[lishawj]

the duplicate enrtry error means, that there is already such data in the table. if you want to replace it, then make REPLACE instead of INSERT.

Thanks for the suggestion Janii and I did use the replace keyword but it replaces the current value in Accesslevels. What I wanted to do originally was this:

1. I created several SQL batch query to add specific adminCommand to a certain GM group.
2. I wanted these new query to append, add-on, etc, but does not replaces the master access level group 1.
3. INSERT does not work, REPLACE overrides the master access value already set.

All in all, I guess the time I spent trying to get the query to work as intended I could've manually punched in all the group access into Toad. However, I wanted this done so I can give it to other GM from my team who may need to change GM accesslevel without having to punch in value manually. Trying very hard to reduce human errors here and failing miserably at it.

I had also tried this query:
REPLACE INTO admin_command_access_rights (adminCommand, accessLevels), (admin_announce_menu, 1), (admin_announce_menu, 3);

However, the last the REPLACE value overrides the first REPLACE value. I need both value of 1 and 3 to be inserting into AccessLevels. What am I doing wrong?

A final thought is that perhaps I need to add two new columns for each GM group into the admin_command_access_rights table instead of concatening the value?


Any other suggestions?

[janiii]

you cant add more than 1 access level to 1 command. that is why you have childAccess in the access_level table.

if access level 1 has childAccess 3, and you set a command to access level 3, that means that the command can be used by gm with access level 3 and 1 (has 3 as childAccess).

[lishawj]

you cant add more than 1 access level to 1 command. that is why you have childAccess in the access_level table.

if access level 1 has childAccess 3, and you set a command to access level 3, that means that the command can be used by gm with access level 3 and 1 (has 3 as childAccess).

ROFL,

OMG thank you Janii! I should really go and shoot myself now. This is way too funny because I HAD TOTALLY FORGOTTEN about the Access_Levels table. It never occurred to me that setting access to a specific adminCommand is read by the Access_levels table with childaccess.

Thank you, thank you, LOL. Time to take a break after all this....gonna go smell the roses a bit now.

This reminds me of the saying, 'it's right under your nose" and can't be more true for my situation...lol.

Once again, a BIG thank you Janii.

[janiii]

ROFL,

OMG thank you Janii! I should really go and shoot myself now. This is way too funny because I HAD TOTALLY FORGOTTEN about the Access_Levels table. It never occurred to me that setting access to a specific adminCommand is read by the Access_levels table with childaccess.

Thank you, thank you, LOL. Time to take a break after all this....gonna go smell the roses a bit now.

This reminds me of the saying, 'it's right under your nose" and can't be more true for my situation...lol.

Once again, a BIG thank you Janii.

np just take your time and relax a bit too gl mastering it!

[KenshinX]

you cant add more than 1 access level to 1 command. that is why you have childAccess in the access_level table.

if access level 1 has childAccess 3, and you set a command to access level 3, that means that the command can be used by gm with access level 3 and 1 (has 3 as childAccess).

Hi janiii,

I know this is already too late to ask, I've been quite busy on doing some torture test
Anyways, tell me if I got you right or wrong on what you been explaining to lishawj about table name access_levels and admin_command_access_rights are you saying the relationship of those two table are like this...

see image:




Also I've been trying to search on l2jdp forums if there is any documentation regarding table relation for l2j datapack or database documentation. Could you help me out point on direction where I could find it? If there's any...


thank you for enlightening us with those two tables.