2

I have this code :

public void saveItems(List<Pair<Item, MapleInventoryType>> items, int id) throws SQLException {
    PreparedStatement ps = null;

    PreparedStatement pse = null;

    try {
        StringBuilder query = new StringBuilder();
        query.append("DELETE FROM `inventoryitems` WHERE `type` = ? AND `");
        query.append(account ? "accountid" : "characterid").append("` = ?");
        Connection con = DatabaseConnection.getConnection();
        ps = con.prepareStatement(query.toString());
        ps.setInt(1, value);
        ps.setInt(2, id);
        ps.executeUpdate();
        ps.close();
        ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        for (Pair<Item, MapleInventoryType> pair : items) {
            Item item = pair.getLeft();
            MapleInventoryType mit = pair.getRight();
            ps.setInt(1, value);
            ps.setString(2, account ? null : String.valueOf(id));
            ps.setString(3, account ? String.valueOf(id) : null);
            ps.setInt(4, item.getItemId());
            ps.setInt(5, mit.getType());
            ps.setInt(6, item.getPosition());
            ps.setInt(7, item.getQuantity());
            ps.setString(8, item.getOwner());
            ps.setInt(9, item.getPetId());
            ps.setInt(10, item.getFlag());
            ps.setLong(11, item.getExpiration());
            ps.setString(12, item.getGiftFrom());
            ps.executeUpdate();

            if (mit.equals(MapleInventoryType.EQUIP) || mit.equals(MapleInventoryType.EQUIPPED)) {
                try (ResultSet rs = ps.getGeneratedKeys()) {

                    if (!rs.next()) {
                        throw new RuntimeException("Inserting item failed.");
                    }

                    pse.setInt(1, rs.getInt(1));
                    rs.close();
                }
                Equip equip = (Equip) item;
                pse.setInt(2, equip.getUpgradeSlots());
                pse.setInt(3, equip.getLevel());
                pse.setInt(4, equip.getStr());
                pse.setInt(5, equip.getDex());
                pse.setInt(6, equip.getInt());
                pse.setInt(7, equip.getLuk());
                pse.setInt(8, equip.getHp());
                pse.setInt(9, equip.getMp());
                pse.setInt(10, equip.getWatk());
                pse.setInt(11, equip.getMatk());
                pse.setInt(12, equip.getWdef());
                pse.setInt(13, equip.getMdef());
                pse.setInt(14, equip.getAcc());
                pse.setInt(15, equip.getAvoid());
                pse.setInt(16, equip.getHands());
                pse.setInt(17, equip.getSpeed());
                pse.setInt(18, equip.getJump());
                pse.setInt(19, 0);
                pse.setInt(20, equip.getVicious());
                pse.setInt(21, equip.getItemLevel());
                pse.setInt(22, equip.getItemExp());
                pse.setInt(23, equip.getRingId());
                pse.executeUpdate();
            }
        }
        pse.close();
        ps.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (ps != null) {
            ps.close();
        }
        if (pse != null) {
            pse.close();
        }
    }
}

DeadLock Error :

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock foun d when trying to get lock; try restarting transaction at sun.reflect.GeneratedConstructorAccessor15.newInstance(Unknown Source ) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou rce) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) at com.mysql.jdbc.Util.getInstance(Util.java:383) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.ja va:2082) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java :2334) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java :2262) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java :2246) at client.inventory.ItemFactory.saveItems(ItemFactory.java:147) at server.MapleStorage.saveToDB(MapleStorage.java:137) at client.MapleCharacter.saveToDB(MapleCharacter.java:5013) at net.server.channel.handlers.ChangeMapHandler.handlePacket(ChangeMapHa ndler.java:59) at net.MapleServerHandler.messageReceived(MapleServerHandler.java:128) at org.apache.mina.core.filterchain.DefaultIoFilterChain$TailFilter.mess ageReceived(DefaultIoFilterChain.java:690) at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessage Received(DefaultIoFilterChain.java:417) at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1200(Def aultIoFilterChain.java:47) at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.mes sageReceived(DefaultIoFilterChain.java:765) at org.apache.mina.filter.codec.ProtocolCodecFilter$ProtocolDecoderOutpu tImpl.flush(ProtocolCodecFilter.java:407) at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(Prot ocolCodecFilter.java:236) at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessage Received(DefaultIoFilterChain.java:417) at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1200(Def aultIoFilterChain.java:47) at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.mes sageReceived(DefaultIoFilterChain.java:765) at org.apache.mina.core.filterchain.IoFilterAdapter.messageReceived(IoFi lterAdapter.java:109) at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessage Received(DefaultIoFilterChain.java:417) at org.apache.mina.core.filterchain.DefaultIoFilterChain.fireMessageRece ived(DefaultIoFilterChain.java:410) at org.apache.mina.core.polling.AbstractPollingIoProcessor.read(Abstract PollingIoProcessor.java:710) at org.apache.mina.core.polling.AbstractPollingIoProcessor.process(Abstr actPollingIoProcessor.java:664) at org.apache.mina.core.polling.AbstractPollingIoProcessor.process(Abstr actPollingIoProcessor.java:653) at org.apache.mina.core.polling.AbstractPollingIoProcessor.access$600(Ab stractPollingIoProcessor.java:67) at org.apache.mina.core.polling.AbstractPollingIoProcessor$Processor.run (AbstractPollingIoProcessor.java:1124) at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnabl e.java:64) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)

saveItems(ItemFactory.java:147) is ps.executeUpdate(); i have tried to check "SHOW ENGINE INNODB STATUS;" on mysql and i got this note:


'InnoDB', '', '
=====================================
2014-08-24 12:38:54 bcc INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1034 srv_active, 0 srv_shutdown, 1111 srv_idle
srv_master_thread log flush and writes: 2145
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1238
OS WAIT ARRAY INFO: signal count 1208
Mutex spin waits 91061, rounds 86815, OS waits 817
RW-shared spins 443, rounds 11553, OS waits 377
RW-excl spins 78, rounds 1712, OS waits 44
Spin rounds per wait: 0.95 mutex, 26.08 RW-shared, 21.95 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-08-24 12:38:54 1a24 Transaction:
TRANSACTION 2127395, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
34 lock struct(s), heap size 6544, 357 row lock(s), undo log entries 297
MySQL thread id 602, OS thread handle 0x1a24, query id 249063 localhost 127.0.0.1 root update
INSERT INTO `inventoryitems` VALUES (DEFAULT, 4, '595', null, 5390001, 5, 1, 1, '', -1, 0, -1, '')
Foreign key constraint fails for table `israeldev`.`inventoryitems`:
,
  CONSTRAINT `FK_inventoryitems_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE
Trying to add in child table, in index `FK_inventoryitems_1` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000253; asc    S;;
 1: len 4; hex 01d4c23b; asc    ;;;

But in parent table `israeldev`.`characters`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 70; compact format; info bits 0
 0: len 4; hex 80007564; asc   ud;;
 1: len 6; hex 000000204b8d; asc     K ;;
 2: len 7; hex 5a000001ea0110; asc Z      ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000000; asc     ;;
 5: len 6; hex 42696c6c6f77; asc Billow;;
 6: len 4; hex 80000087; asc     ;;
 7: len 4; hex 801a6202; asc   b ;;
 8: len 4; hex 80000000; asc     ;;
 9: len 4; hex 80000004; asc     ;;
 10: len 4; hex 800000ae; asc     ;;
 11: len 4; hex 800001c6; asc     ;;
 12: len 4; hex 80000004; asc     ;;
 13: len 4; hex 80007530; asc   u0;;
 14: len 4; hex 8000751f; asc   u ;;
 15: len 4; hex 80007530; asc   u0;;
 16: len 4; hex 80007530; asc   u0;;
 17: len 4; hex 8e694a2c; asc  iJ,;;
 18: len 4; hex 00000000; asc     ;;
 19: len 4; hex 8000038e; asc     ;;
 20: len 4; hex 80000000; asc     ;;
 21: len 4; hex 80000000; asc     ;;
 22: len 4; hex 80000001; asc     ;;
 23: len 4; hex 80009132; asc    2;;
 24: len 4; hex 80004fbc; asc   O ;;
 25: len 4; hex 8000223c; asc   "

Inventoryitems sql :


'inventoryitems', 'CREATE TABLE `inventoryitems` (
  `inventoryitemid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(3) unsigned NOT NULL,
  `characterid` int(11) DEFAULT NULL,
  `accountid` int(11) DEFAULT NULL,
  `itemid` int(11) NOT NULL DEFAULT '0',
  `inventorytype` int(11) NOT NULL DEFAULT '0',
  `position` int(11) NOT NULL DEFAULT '0',
  `quantity` int(11) NOT NULL DEFAULT '0',
  `owner` tinytext NOT NULL,
  `petid` int(11) NOT NULL DEFAULT '-1',
  `flag` int(11) NOT NULL,
  `expiration` bigint(20) NOT NULL DEFAULT '-1',
  `giftFrom` varchar(26) NOT NULL,
  PRIMARY KEY (`inventoryitemid`),
  KEY `FK_inventoryitems_1` (`characterid`),
  CONSTRAINT `FK_inventoryitems_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31099641 DEFAULT CHARSET=latin1'

InventoryEquipments SQL:


'inventoryequipment', 'CREATE TABLE inventoryequipment (
  inventoryequipmentid int(10) unsigned NOT NULL AUTO_INCREMENT,
  inventoryitemid int(10) unsigned NOT NULL DEFAULT '0',
  upgradeslots int(11) NOT NULL DEFAULT '0',
  level int(11) NOT NULL DEFAULT '0',
  str int(11) NOT NULL DEFAULT '0',
  dex int(11) NOT NULL DEFAULT '0',
  int int(11) NOT NULL DEFAULT '0',
  luk int(11) NOT NULL DEFAULT '0',
  hp int(11) NOT NULL DEFAULT '0',
  mp int(11) NOT NULL DEFAULT '0',
  watk int(11) NOT NULL DEFAULT '0',
  matk int(11) NOT NULL DEFAULT '0',
  wdef int(11) NOT NULL DEFAULT '0',
  mdef int(11) NOT NULL DEFAULT '0',
  acc int(11) NOT NULL DEFAULT '0',
  avoid int(11) NOT NULL DEFAULT '0',
  hands int(11) NOT NULL DEFAULT '0',
  speed int(11) NOT NULL DEFAULT '0',
  jump int(11) NOT NULL DEFAULT '0',
  locked int(11) NOT NULL DEFAULT '0',
  vicious int(11) unsigned NOT NULL DEFAULT '0',
  itemlevel int(11) NOT NULL DEFAULT '1',
  itemexp int(11) unsigned NOT NULL DEFAULT '0',
  ringid int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (inventoryequipmentid),
  KEY inventoryitemid (inventoryitemid),
  CONSTRAINT inventoryequipment_ibfk_1 FOREIGN KEY (inventoryitemid) REFERENCES inventoryitems (inventoryitemid) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8811024 DEFAULT CHARSET=latin1'

Characters :


'characters', 'CREATE TABLE `characters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accountid` int(11) NOT NULL DEFAULT '0',
  `world` int(11) NOT NULL DEFAULT '0',
  `name` varchar(13) NOT NULL DEFAULT '',
  `level` int(11) NOT NULL DEFAULT '1',
  `exp` int(11) NOT NULL DEFAULT '0',
  `gachaexp` int(11) NOT NULL DEFAULT '0',
  `str` int(11) NOT NULL DEFAULT '12',
  `dex` int(11) NOT NULL DEFAULT '5',
  `luk` int(11) NOT NULL DEFAULT '4',
  `int` int(11) NOT NULL DEFAULT '4',
  `hp` int(11) NOT NULL DEFAULT '50',
  `mp` int(11) NOT NULL DEFAULT '5',
  `maxhp` int(11) NOT NULL DEFAULT '50',
  `maxmp` int(11) NOT NULL DEFAULT '5',
  `meso` int(11) NOT NULL DEFAULT '0',
  `hpMpUsed` int(11) unsigned NOT NULL DEFAULT '0',
  `job` int(11) NOT NULL DEFAULT '0',
  `skincolor` int(11) NOT NULL DEFAULT '0',
  `gender` int(11) NOT NULL DEFAULT '0',
  `fame` int(11) NOT NULL DEFAULT '0',
  `hair` int(11) NOT NULL DEFAULT '0',
  `face` int(11) NOT NULL DEFAULT '0',
  `ap` int(11) NOT NULL DEFAULT '0',
  `sp` int(11) NOT NULL DEFAULT '0',
  `map` int(11) NOT NULL DEFAULT '0',
  `spawnpoint` int(11) NOT NULL DEFAULT '0',
  `gm` tinyint(1) NOT NULL DEFAULT '0',
  `party` int(11) NOT NULL DEFAULT '0',
  `buddyCapacity` int(11) NOT NULL DEFAULT '25',
  `createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rank` int(10) unsigned NOT NULL DEFAULT '1',
  `rankMove` int(11) NOT NULL DEFAULT '0',
  `jobRank` int(10) unsigned NOT NULL DEFAULT '1',
  `jobRankMove` int(11) NOT NULL DEFAULT '0',
  `guildid` int(10) unsigned NOT NULL DEFAULT '0',
  `guildrank` int(10) unsigned NOT NULL DEFAULT '5',
  `messengerid` int(10) unsigned NOT NULL DEFAULT '0',
  `messengerposition` int(10) unsigned NOT NULL DEFAULT '4',
  `mountlevel` int(9) NOT NULL DEFAULT '1',
  `mountexp` int(9) NOT NULL DEFAULT '0',
  `mounttiredness` int(9) NOT NULL DEFAULT '0',
  `omokwins` int(11) NOT NULL DEFAULT '0',
  `omoklosses` int(11) NOT NULL DEFAULT '0',
  `omokties` int(11) NOT NULL DEFAULT '0',
  `matchcardwins` int(11) NOT NULL DEFAULT '0',
  `matchcardlosses` int(11) NOT NULL DEFAULT '0',
  `matchcardties` int(11) NOT NULL DEFAULT '0',
  `MerchantMesos` int(11) DEFAULT '0',
  `HasMerchant` tinyint(1) DEFAULT '0',
  `equipslots` int(11) NOT NULL DEFAULT '96',
  `useslots` int(11) NOT NULL DEFAULT '96',
  `setupslots` int(11) NOT NULL DEFAULT '96',
  `etcslots` int(11) NOT NULL DEFAULT '96',
  `familyId` int(11) NOT NULL DEFAULT '-1',
  `monsterbookcover` int(11) NOT NULL DEFAULT '0',
  `allianceRank` int(10) NOT NULL DEFAULT '5',
  `vanquisherStage` int(11) unsigned NOT NULL DEFAULT '0',
  `dojoPoints` int(11) unsigned NOT NULL DEFAULT '0',
  `lastDojoStage` int(10) unsigned NOT NULL DEFAULT '0',
  `finishedDojoTutorial` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `vanquisherKills` int(11) unsigned NOT NULL DEFAULT '0',
  `summonValue` int(11) unsigned NOT NULL DEFAULT '0',
  `reborns` int(11) NOT NULL DEFAULT '0',
  `uniqName` int(11) NOT NULL DEFAULT '0',
  `pvpkills` int(11) NOT NULL DEFAULT '0',
  `pvpdeaths` int(11) NOT NULL DEFAULT '0',
  `familiar` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `accountid` (`accountid`),
  KEY `party` (`party`),
  KEY `ranking1` (`level`,`exp`),
  KEY `ranking2` (`gm`,`job`)
) ENGINE=InnoDB AUTO_INCREMENT=32944 DEFAULT CHARSET=latin1 PACK_KEYS=0'

SQL code .-. when the error happend

-----------------------------
DELETE FROM `inventoryitems` WHERE `type` = ? AND `accountid` = ?
Value : 2
Id : 490
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock foun
d when trying to get lock; try restarting transaction

Please help me to solve this DeadLock :(.

  • Can you edit your question and post SHOW CREATE TABLE inventory and inventoryitems\G. Also, get Java to print out the statements just before they are sent to the server and post them also. – Vérace Aug 24 '14 at 14:11
  • inventory?, there is no table with the name inventory. – user3191447 Aug 24 '14 at 14:40
  • Sorry, I should have written inventoryequipment. Also, are all the tables in your schema InnoDB? – Vérace Aug 24 '14 at 14:54
  • They are, is somthing wrong with my sql table code? – user3191447 Aug 24 '14 at 15:16
  • Can you also print out the Java SQL just before it gets sent to the server? Also, the characters table? – Vérace Aug 24 '14 at 19:57
1

The system is starting to complain about the number of comments.

I'd be interested in seeing the statements as they are sent to the server - i.e. the fully generated SQL statements, i.e. with real values, not ? parameter place holders.

The statement that appears to be causing the issue is this one.

INSERT INTO `inventoryitems` VALUES (DEFAULT, 4, '595', null, 5390001, 5, 1, 1, '', -1, 0, -1, '')

Now, inventory items has a FOREIGN KEY constraint

CONSTRAINT FK_inventoryitems_1 FOREIGN KEY (characterid) REFERENCES characters (id) ON DELETE CASCADE

What is the result of the following query

SELECT COUNT(*) FROM characters WHERE characters.id = 595;

The values here are also of interest.

ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

[EDIT]

I'm starting to get some insight into what may be going on. You appear to be using some sort of framework for your database access (that's the reason why your SQL has '595' for an integer insert instead of 595.

It's possible that the transaction isolation level is being set by the framework to something other than read committed (see here). Try and ensure that your transactions use read committed - set the transaction level. Try adding this code to yours after the line

Connection con = DatabaseConnection.getConnection(); con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // Added line.

Also, check out this and the rest of the thread.

[EDIT] in response to "How do i check it? , can you guide me?"

What I meant was for you to add the line above to your code and rerun your system to the point where you got your original error. Do you still get an error? If you do, then is it different to the original error? If it is different, then post the error text here. HTH.

Could you take a dump of your database and put it online? You can anonymise anything sensitive. Also, zip up your Java code and I'll run that here against your schema. My Java's a bit rusty, but running it on a different system might give us some clues! :-)

[EDIT] in response to OP's comment "can you give me an example of indexes [code example] ?".

See here (you can also use the KEY keyword as a synonym for INDEX - see the docco here. You should add to your inventoryitems CREATE TABLE statement lines such as

INDEX `ii_type_ix` (type), 
INDEX `ii_account_id_ix` (accountid),

As for changing your Java code - I'm not 100% sure, but the problem lies with the database so your efforts should be concentrated there. Where you can change the code is in varying the order of your queries (but keeping the original functionality).

  • I'll check the statement code and post it right away :), – user3191447 Aug 24 '14 at 23:49
  • com.mysql.jdbc.JDBC4PreparedStatement@19facd2f: DELETE FROM inventoryitems WHE RE type = 2 AND accountid = 301 ------------------ com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock foun d when trying to get lock; try restarting transaction , How can i solve it? – user3191447 Aug 25 '14 at 0:03
  • Check edit Connection.TRANSACTION_READ_COMMITTED. Error in first connection transaction isolation level. – Vérace Aug 25 '14 at 1:02
  • How do i check it? , can you guide me? – user3191447 Aug 25 '14 at 10:30
  • i noticed you have been edit your post, well i have read it and i changed the code to this code : ps = con.prepareStatement("DELETE FROM inventoryitems WHERE " + (account ? "accountid" : "characterid") + " = ? IN (SELECT " + (account ? "accountid" : "characterid") + " = ? FROM inventoryitems WHERE type = ?) u"); ps.setInt(1, id); ps.setInt(2, id); ps.setInt(3, value); is it okay?, btw if we could Teamviewer or move to somthing more avalaibe it will be great! – user3191447 Aug 25 '14 at 14:16
0

[Solved] How to fix:


'inventoryitems', 'CREATE TABLE `inventoryitems` (
  `inventoryitemid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(3) unsigned NOT NULL,
  `characterid` int(11) DEFAULT NULL,
  `accountid` int(11) DEFAULT NULL,
  `itemid` int(11) NOT NULL DEFAULT '0',
  `inventorytype` int(11) NOT NULL DEFAULT '0',
  `position` int(11) NOT NULL DEFAULT '0',
  `quantity` int(11) NOT NULL DEFAULT '0',
  `owner` tinytext NOT NULL,
  `petid` int(11) NOT NULL DEFAULT '-1',
  `flag` int(11) NOT NULL,
  `expiration` bigint(20) NOT NULL DEFAULT '-1',
  `giftFrom` varchar(26) NOT NULL,
  PRIMARY KEY (`inventoryitemid`),
  KEY `FK_inventoryitems_1` (`characterid`),
  KEY `FK_inventoryitems_2` (`accountid`) USING BTREE,
  CONSTRAINT `FK_inventoryitems_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_inventoryitems_2` FOREIGN KEY (`accountid`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=40321116 DEFAULT CHARSET=latin1'

Instead of :


'inventoryitems', 'CREATE TABLE `inventoryitems` (
  `inventoryitemid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(3) unsigned NOT NULL,
  `characterid` int(11) DEFAULT NULL,
  `accountid` int(11) DEFAULT NULL,
  `itemid` int(11) NOT NULL DEFAULT '0',
  `inventorytype` int(11) NOT NULL DEFAULT '0',
  `position` int(11) NOT NULL DEFAULT '0',
  `quantity` int(11) NOT NULL DEFAULT '0',
  `owner` tinytext NOT NULL,
  `petid` int(11) NOT NULL DEFAULT '-1',
  `flag` int(11) NOT NULL,
  `expiration` bigint(20) NOT NULL DEFAULT '-1',
  `giftFrom` varchar(26) NOT NULL,
  PRIMARY KEY (`inventoryitemid`),
  KEY `FK_inventoryitems_1` (`characterid`),
  CONSTRAINT `FK_inventoryitems_1` FOREIGN KEY (`characterid`) REFERENCES `characters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31099641 DEFAULT CHARSET=latin1'

  • You're getting this on a test system with a single user? Do a show processlist again. I'm very puzzled by this. What version of MySQL are you running? – Vérace Aug 26 '14 at 8:07
  • Nope, i get it when multi users are saving there items to the sql, and MySQL Version is : 5.6.20, it happends when item type is 2. – user3191447 Aug 26 '14 at 9:00
  • Could you try to change the order that the queries are performed in? I know that some queries will require other ones to be run first, but it may be that the ordering of the access to various tables may affect the system. Put indexes on type and account_id in inventoryitems. – Vérace Aug 26 '14 at 9:27
  • can you give me an example of indexes [code example] ?, i dont understand what you mean by putting indexes on type and account_id :/ , I have changed the public void saveItems to public synchronized saveItems and still notihng, same error. – user3191447 Aug 26 '14 at 10:07
  • Have you had any luck resolving this issue? – Vérace Aug 27 '14 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.