WordPress Database error

Hello,

Glad to hear that your site is growing fast.

My first guess about this problem is that you should check how much space is available on your host for the MySQL database. If you are on a managed host then you should ask your hosting provider. Increasing the available space should resolve the problem.

As a temporary solution you could enable “Old transaction aggregation” in the cron settings. This will batch together old transactions and will save some space. But ultimately this is a fundamental limitation of the plugin – if you are saving transactions on your database, then you are obviously limited by the free space available for that database.

Please let me know if increasing the space allotted to your database has fixed your issue.

kind regards

Hi,

1. We are on a dedicated server with a very large MySQL database – we already have 140k + transactions in the database. The server is 128 GB Memory / 3 TB Disk – cpu is only running at 33% at most. So I doubt that the limitation is with our server.

2. I have already setup old transaction aggregation in the cron job. Doesn’t seem to help at all.

Regards,
EOT Wallet

Thanks for the additional info.

CPU and memory capacity are irrelevant. It’s good to know that you have ample disk space. But just because you have a 3TB disk does not mean that it is necessarily available for use by the DB. Could you check that no quotas are in effect?

I will release a patch where the plugin will write out relevant error messages in the case, where DB inserts fail. This should shed some light to your situation.

In the meantime, could you please check the disk space that your DB takes up and let me know what you find?

kind regards

Hello again,

The latest version 3.3.3 of the plugin writes out error messages in the event when a transaction fails to be inserted to the DB.

Could you please report this error to me? In your logs there will be lines of the form:

action_wallets_transaction Last DB error: plus some error message.

The error message should be indicative of the problem you are facing.

Thanks

Some errors I am getting in apache2 error log

[Tue May 22 12:18:42.307574 2018] [:error] [pid 1449] [client 207.154.197.212:54660] WordPress database error Duplicate entry ‘568eda84a986129efaa87575216e13875cdfc7d14118e1f384d9fe643d578214’ for key ‘uq_tx_idx’ for query INSERT INTO wp_wallets_txs (blog_id, category, account, address, extra, txid, symbol, amount, fee, created_time, updated_time, confirmations, status, retries) VALUES (1, ‘deposit’, 9805, ‘EUfr4jUPVKW3khmHnZ9ZYDeCfCVPVvHRJq’, ”, ‘568eda84a986129efaa87575216e13875cdfc7d14118e1f384d9fe643d578214’, ‘EOT’, ‘2.0000000000’, ‘0’, ‘2018-05-22T12:14:38+0000’, ‘2018-05-22 12:18:42’, 1, ‘pending’, 255) made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Cron->cron, Dashed_Slug_Wallets_Cron->call_cron_on_all_adapters, Dashed_Slug_Wallets_Coin_Adapter_RPC->cron, Dashed_Slug_Wallets_Coin_Adapter_RPC->cron_scrape_listtransactions, do_action(‘wallets_notify_wallet_EOT’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Multi_Coin_Adapter->action_wallets_notify_wallet, Dashed_Slug_Wallets_Coin_Adapter_RPC->action_wallets_notify_wallet, do_action(‘wallets_transaction’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_TXs->action_wallets_transaction, W3TC\\DbCache_Wpdb->insert, W3TC\\DbCache_WpdbInjection_QueryCaching->insert, W3TC\\_CallUnderlying->insert, W3TC\\DbCache_Wpdb->insert, W3TC\\DbCache_WpdbInjection->insert, W3TC\\DbCache_Wpdb->default_insert, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_Wpdb->default_query, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1526991510.1889190673828125000000
[Tue May 22 12:18:42.309991 2018] [:error] [pid 1449] [client 207.154.197.212:54660] action_wallets_transaction Transaction could not be inserted! Array\n(\n [blog_id] => 1\n [category] => deposit\n [account] => 9805\n [address] => EUfr4jUPVKW3khmHnZ9ZYDeCfCVPVvHRJq\n [extra] => \n [txid] => 568eda84a986129efaa87575216e13875cdfc7d14118e1f384d9fe643d578214\n [symbol] => EOT\n [amount] => 2.0000000000\n [fee] => 0\n [created_time] => 2018-05-22T12:14:38+0000\n [updated_time] => 2018-05-22 12:18:42\n [confirmations] => 1\n [status] => pending\n [retries] => 255\n)\n, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1526991510.1889190673828125000000
[Tue May 22 12:18:42.311921 2018] [:error] [pid 1449] [client 207.154.197.212:54660] action_wallets_transaction Last DB error: Duplicate entry ‘568eda84a986129efaa87575216e13875cdfc7d14118e1f384d9fe643d578214’ for key ‘uq_tx_idx’, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1526991510.1889190673828125000000
[Tue May 22 13:38:41.387791 2018] [:error] [pid 7192] [client 207.154.197.212:46156] WordPress database error BIGINT UNSIGNED value is out of range in ‘(eotwallet_wordpress.wp_wallets_txs.retries – 1)’ for query \n\t\t\t\t\t\tUPDATE\n\t\t\t\t\t\t\twp_wallets_txs\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\tstatus = ‘pending’,\n\t\t\t\t\t\t\tretries = retries – 1\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tid = 766363\n\t\t\t\t\t\t made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_TXs->cron, Dashed_Slug_Wallets_TXs->execute_pending_withdrawals, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection_QueryCaching->query, W3TC\\_CallUnderlying->query, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_Wpdb->default_query, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1526996312.6995460987091064453125
[Tue May 22 13:38:42.210531 2018] [:error] [pid 8032] [client 207.154.197.212:46042] WordPress database error BIGINT UNSIGNED value is out of range in ‘(eotwallet_wordpress.wp_wallets_txs.retries – 1)’ for query \n\t\t\t\t\t\tUPDATE\n\t\t\t\t\t\t\twp_wallets_txs\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\tstatus = ‘failed’,\n\t\t\t\t\t\t\tretries = retries – 1\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tid = 766363\n\t\t\t\t\t\t made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_TXs->cron, Dashed_Slug_Wallets_TXs->execute_pending_withdrawals, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection_QueryCaching->query, W3TC\\_CallUnderlying->query, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_Wpdb->default_query, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1526996311.9633519649505615234375
[Tue May 22 13:38:43.140770 2018] [:error] [pid 7327] [client 207.154.197.212:46352] WordPress database error BIGINT UNSIGNED value is out of range in ‘(eotwallet_wordpress.wp_wallets_txs.retries – 1)’ for query \n\t\t\t\t\t\tUPDATE\n\t\t\t\t\t\t\twp_wallets_txs\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\tstatus = ‘failed’,\n\t\t\t\t\t\t\tretries = retries – 1\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tid = 766363\n\t\t\t\t\t\t made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_TXs->cron, Dashed_Slug_Wallets_TXs->execute_pending_withdrawals, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection_QueryCaching->query, W3TC\\_CallUnderlying->query, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_Wpdb->default_query, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1526996313.4660971164703369140625

OK thank you.

Can you also please show me the output of this SQL query?

SHOW INDEX FROM wp_wallets_txs;

The reason I’m asking the above question is because the wp_wallets_txs table should have a unique constraint on txid, address, and symbol.

Previously the constraint only involved txid and symbol. It is possible that the recent schema upgrade failed for you because your tables are large.

IF that is the case, then you could do the following:

1. Deactivate the plugin.
2. Backup your database.
3. Recreate the unique constraint with:
ALTER TABLE wp_wallets_txs DROP INDEX uq_tx_idx, ADD UNIQUE KEY uq_tx_idx(txid,address,symbol);
4. Activate the plugin.

If you are not sure whether you need to do this, please share with me the output of the SHOW INDEX query and I’ll let you know.

kind regards

mysql> SHOW INDEX FROM wp_wallets_txs;
+—————-+————+————-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+—————-+————+————-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| wp_wallets_txs | 0 | PRIMARY | 1 | id | A | 157047 | NULL | NULL | | BTREE | | |
| wp_wallets_txs | 0 | uq_tx_idx | 1 | txid | A | 110339 | NULL | NULL | YES | BTREE | | |
| wp_wallets_txs | 0 | uq_tx_idx | 2 | address | A | 127234 | NULL | NULL | | BTREE | | |
| wp_wallets_txs | 0 | uq_tx_idx | 3 | symbol | A | 146973 | NULL | NULL | | BTREE | | |
| wp_wallets_txs | 1 | account_idx | 1 | account | A | 9816 | NULL | NULL | | BTREE | | |
| wp_wallets_txs | 1 | blogid_idx | 1 | blog_id | A | 1 | NULL | NULL | | BTREE | | |
+—————-+————+————-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
6 rows in set (0.00 sec)

Thank you.

It seems that your schema upgrade has succeeded. You do not need to rebuild the constraint.

I noticed on the EOT block explorer that at least one of the problematic transactions has multiple outputs, I’m guessing to multiple users on your system.

You had requested that feature here and it was implemented in 3.3.2, however it is possible that there was some bug in that patch.

I will have to investigate further.

Will get back to you as soon as possible.

OK it turns out that there was a bug in the code after all. This is fixed in 3.3.4.

My fault, apologies for the trouble and thanks very much for assisting me to resolve this.

Do let me know if you continue to face problems after upgrading, although it is very unlikely.

kind regards

After running for a while with the new update, got the database error again:

[Tue May 22 16:57:50.083965 2018] [:error] [pid 2292] [client 207.154.197.212:58282] action_wallets_transaction Last DB error: Duplicate entry ‘7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a’ for key ‘uq_tx_idx’, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1527008236.4876289367675781250000
[Tue May 22 16:57:50.087500 2018] [:error] [pid 2292] [client 207.154.197.212:58282] WordPress database error Duplicate entry ‘7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a’ for key ‘uq_tx_idx’ for query INSERT INTO wp_wallets_txs (blog_id, category, account, address, extra, txid, symbol, amount, fee, created_time, updated_time, confirmations, status, retries) VALUES (1, ‘deposit’, 10440, ‘EcZ2NPqkT8Fv4KYorwAp8pPrcX9FRn9YDL’, ”, ‘7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a’, ‘EOT’, ‘6.0000000000’, ‘0’, ‘2018-05-22T16:54:58+0000’, ‘2018-05-22 16:57:50’, 1, ‘pending’, 255) made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Cron->cron, Dashed_Slug_Wallets_Cron->call_cron_on_all_adapters, Dashed_Slug_Wallets_Coin_Adapter_RPC->cron, Dashed_Slug_Wallets_Coin_Adapter_RPC->cron_scrape_listtransactions, do_action(‘wallets_notify_wallet_EOT’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Multi_Coin_Adapter->action_wallets_notify_wallet, Dashed_Slug_Wallets_Coin_Adapter_RPC->action_wallets_notify_wallet, do_action(‘wallets_transaction’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_TXs->action_wallets_transaction, W3TC\\DbCache_Wpdb->insert, W3TC\\DbCache_WpdbInjection_QueryCaching->insert, W3TC\\_CallUnderlying->insert, W3TC\\DbCache_Wpdb->insert, W3TC\\DbCache_WpdbInjection->insert, W3TC\\DbCache_Wpdb->default_insert, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_Wpdb->default_query, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1527008236.4876289367675781250000
[Tue May 22 16:57:50.087551 2018] [:error] [pid 2292] [client 207.154.197.212:58282] action_wallets_transaction Transaction could not be inserted! Array\n(\n [blog_id] => 1\n [category] => deposit\n [account] => 10440\n [address] => EcZ2NPqkT8Fv4KYorwAp8pPrcX9FRn9YDL\n [extra] => \n [txid] => 7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a\n [symbol] => EOT\n [amount] => 6.0000000000\n [fee] => 0\n [created_time] => 2018-05-22T16:54:58+0000\n [updated_time] => 2018-05-22 16:57:50\n [confirmations] => 1\n [status] => pending\n [retries] => 255\n)\n, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1527008236.4876289367675781250000
[Tue May 22 16:57:50.087560 2018] [:error] [pid 2292] [client 207.154.197.212:58282] action_wallets_transaction Last DB error: Duplicate entry ‘7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a’ for key ‘uq_tx_idx’, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1527008236.4876289367675781250000
[Tue May 22 16:57:50.089672 2018] [:error] [pid 2292] [client 207.154.197.212:58282] WordPress database error Duplicate entry ‘7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a’ for key ‘uq_tx_idx’ for query INSERT INTO wp_wallets_txs (blog_id, category, account, address, extra, txid, symbol, amount, fee, created_time, updated_time, confirmations, status, retries) VALUES (1, ‘deposit’, 9805, ‘EUfr4jUPVKW3khmHnZ9ZYDeCfCVPVvHRJq’, ”, ‘7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a’, ‘EOT’, ‘7.0000000000’, ‘0’, ‘2018-05-22T16:54:58+0000’, ‘2018-05-22 16:57:50’, 1, ‘pending’, 255) made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Cron->cron, Dashed_Slug_Wallets_Cron->call_cron_on_all_adapters, Dashed_Slug_Wallets_Coin_Adapter_RPC->cron, Dashed_Slug_Wallets_Coin_Adapter_RPC->cron_scrape_listtransactions, do_action(‘wallets_notify_wallet_EOT’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Multi_Coin_Adapter->action_wallets_notify_wallet, Dashed_Slug_Wallets_Coin_Adapter_RPC->action_wallets_notify_wallet, do_action(‘wallets_transaction’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_TXs->action_wallets_transaction, W3TC\\DbCache_Wpdb->insert, W3TC\\DbCache_WpdbInjection_QueryCaching->insert, W3TC\\_CallUnderlying->insert, W3TC\\DbCache_Wpdb->insert, W3TC\\DbCache_WpdbInjection->insert, W3TC\\DbCache_Wpdb->default_insert, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_Wpdb->default_query, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1527008236.4876289367675781250000
[Tue May 22 16:57:50.089716 2018] [:error] [pid 2292] [client 207.154.197.212:58282] action_wallets_transaction Transaction could not be inserted! Array\n(\n [blog_id] => 1\n [category] => deposit\n [account] => 9805\n [address] => EUfr4jUPVKW3khmHnZ9ZYDeCfCVPVvHRJq\n [extra] => \n [txid] => 7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a\n [symbol] => EOT\n [amount] => 7.0000000000\n [fee] => 0\n [created_time] => 2018-05-22T16:54:58+0000\n [updated_time] => 2018-05-22 16:57:50\n [confirmations] => 1\n [status] => pending\n [retries] => 255\n)\n, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1527008236.4876289367675781250000
[Tue May 22 16:57:50.089732 2018] [:error] [pid 2292] [client 207.154.197.212:58282] action_wallets_transaction Last DB error: Duplicate entry ‘7bbd3bc4685e77745fc660f835750f89f21496734da8ff17cb368f21a40c768a’ for key ‘uq_tx_idx’, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1527008236.4876289367675781250000

Even with your latest version 3.3.5

Fri May 25 08:28:46.452073 2018] [:error] [pid 26872] [client 207.154.197.212:46450] WordPress database error BIGINT UNSIGNED value is out of range in ‘(eotwallet_wordpress.wp_wallets_txs.retries – 1)’ for query \n\t\t\t\t\t\tUPDATE\n\t\t\t\t\t\t\twp_wallets_txs\n\t\t\t\t\t\tSET\n\t\t\t\t\t\t\tstatus = ‘pending’,\n\t\t\t\t\t\t\tretries = retries – 1\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tid = 837503\n\t\t\t\t\t\t made by shutdown_action_hook, do_action(‘shutdown’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Cron::trigger_cron, do_action(‘wallets_periodic_checks’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_TXs->cron, Dashed_Slug_Wallets_TXs->execute_pending_withdrawals, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection_QueryCaching->query, W3TC\\_CallUnderlying->query, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_Wpdb->default_query

This is probably because, due to the previous error, you have some transactions that are pending with zero retries left. If this is the case, it can be fixed very easily.

But first, could you please send me the result of this query?

SELECT category,status,retries,COUNT(*) FROM wp_wallets_txs GROUP BY category,status,retries;

Thanks

Hello again,

Never mind, you don’t have to run the query above.

I was able to reproduce the problem on my end. It’s not a critical issue, but it is now fixed in patch 3.3.6.

Again thank you for your help!

kind regards
Alex

Hello,

Even after upgrade to version 3.3.6 I am still facing the same issue:

Tue May 29 06:58:06.217334 2018] [:error] [pid 6900] [client 207.154.197.212:60858] WordPress database error Duplicate entry ‘b5b669117771cd4a4a6a21c945a22784d4ff7889af37ab2fcb4237a66fde8880’ for key ‘uq_tx_idx’ for query INSERT INTO wp_wallets_txs (blog_id, category, account, address, extra, txid, symbol, amount, fee, created_time, updated_time, confirmations, status, retries) VALUES (1, ‘deposit’, 10307, ‘ERShErn3h2iJgwUuJAcLDeDhJGKQoGBeEs’, ”, ‘b5b669117771cd4a4a6a21c945a22784d4ff7889af37ab2fcb4237a66fde8880’, ‘EOT’, ‘3.0000000000’, ‘0’, ‘2018-05-29T06:54:40+0000’, ‘2018-05-29 06:58:06’, 2, ‘pending’, 255) made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Cron->cron, Dashed_Slug_Wallets_Cron->call_cron_on_all_adapters, Dashed_Slug_Wallets_Coin_Adapter_RPC->cron, Dashed_Slug_Wallets_Coin_Adapter_RPC->cron_scrape_listtransactions, do_action(‘wallets_notify_wallet_EOT’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_Multi_Coin_Adapter->action_wallets_notify_wallet, Dashed_Slug_Wallets_Coin_Adapter_RPC->action_wallets_notify_wallet, do_action(‘wallets_transaction’), WP_Hook->do_action, WP_Hook->apply_filters, Dashed_Slug_Wallets_TXs->action_wallets_transaction, W3TC\\DbCache_Wpdb->insert, W3TC\\DbCache_WpdbInjection_QueryCaching->insert, W3TC\\_CallUnderlying->insert, W3TC\\DbCache_Wpdb->insert, W3TC\\DbCache_WpdbInjection->insert, W3TC\\DbCache_Wpdb->default_insert, W3TC\\DbCache_Wpdb->query, W3TC\\DbCache_WpdbInjection->query, W3TC\\DbCache_Wpdb->default_query, referer: https://eotwallet.com/wp-cron.php?doing_wp_cron=1527577080.2362229824066162109375