1 Topic by write4saini

  • write4saini
  • Member
  • Offline
  • Registered: 2010-10-30
  • Posts: 80

Topic: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Hi iRedadmin-pro


i have geting error when i release/delete mails from quarantined.


tell what is that error, plz fix it asap .

ThankX
==== Provide basic information to help troubleshoot ====
- iRedMail version:
- Linux/BSD distribution name and version:
- Any related log? Log is helpful for troubleshooting.
====

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Could you please paste error log here to help troubleshoot?

3 Reply by write4saini

  • write4saini
  • Member
  • Offline
  • Registered: 2010-10-30
  • Posts: 80

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

i have resolved that problem.

==== Provide basic information to help troubleshoot ====
- iRedMail version:
- Linux/BSD distribution name and version:
- Any related log? Log is helpful for troubleshooting.
====

4 Reply by micentosh

  • micentosh
  • Member
  • Offline
  • Registered: 2011-02-18
  • Posts: 18

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

hey, can you share your solution. i have the same problem

==== Provide basic information to help troubleshoot ====
- iRedMail version:
- Linux/BSD distribution name and version:
- Any related log? Log is helpful for troubleshooting.
====

5 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Hi @micentosh,

Could you please paste error log here to help troubleshoot?

6 Reply by micentosh

  • micentosh
  • Member
  • Offline
  • Registered: 2011-02-18
  • Posts: 18

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

This is the error what i'm getting.  i'm not sure which log i should post. mysqld log shows no errors.

Error
SQL query:

DELETE FROM  `msgs` WHERE  `msgs`.`partition_tag` =0 AND CONVERT(  `msgs`.`mail_id` USING utf8 ) = CAST( 0x5338364d70702b77436c4656 AS BINARY ) LIMIT 1

MySQL said:

#1205 - Lock wait timeout exceeded; try restarting transaction

sql prosses list:


mysql> show full processlist;
+------+---------+-----------------+-----------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id   | User    | Host            | db        | Command | Time | State        | Info                                                                                                                                                                                                                |
+------+---------+-----------------+-----------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|    3 | policyd | localhost:48705 | policyd   | Sleep   |    7 |              | NULL                                                                                                                                                                                                                |
| 2891 | root    | localhost       | amavisd   | Sleep   | 1857 |              | NULL                                                                                                                                                                                                                |
| 2931 | root    | localhost       | amavisd   | Sleep   | 1256 |              | NULL                                                                                                                                                                                                                |
| 3010 | root    | localhost       | amavisd   | Query   |    0 | NULL         | show full processlist                                                                                                                                                                                               |
| 3097 | root    | localhost:39086 | iredadmin | Sleep   |  215 |              | NULL                                                                                                                                                                                                                |
| 3098 | root    | localhost:39089 | amavisd   | Sleep   |  189 |              | NULL                                                                                                                                                                                                                |
| 3100 | root    | localhost       | amavisd   | Sleep   |   23 |              | NULL                                                                                                                                                                                                                |
| 3102 | root    | localhost:39108 | amavisd   | Query   |  188 | Sending data | DELETE FROM msgrcpt WHERE mail_id IN (SELECT mail_id FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C')                         AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
                ) |
| 3103 | root    | localhost       | amavisd   | Sleep   |    7 |              | NULL                                                                                                                                                                                                                |
+------+---------+-----------------+-----------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

micentosh wrote:

| 3102 | root    | localhost:39108 | amavisd   | Query   |  188 | Sending data | DELETE FROM msgrcpt WHERE mail_id IN (SELECT mail_id FROM msgs WHERE content IN ('U', 'M', 'H', 'O', 'C')                         AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
                ) |

Same issue with this forum post:
www.iredmail.org/forum/topic2370-login-to-iredadminpro-is-too-slow.html

Here's solution:

- Create one more SQL index in Amavisd database:

$ mysql -uroot -p
mysql> USE amavisd;
mysql> ALTER TABLE `msgs` ADD INDEX `msgs_idx_content` (`content`);

- Download attached patch "delete_amavisd_db.patch". It's patch for iRedAdmin-Pro-LDAP-1.6.2 or iRedAdmin-Pro-MySQL-1.3.0.

- Copy or upload this patch to your mail server which has iRedAdmin-Pro running. Assume it's /root/delete_amavisd_db.patch.

- Open shell terminal, and change current working directory to the root directory of the iRedAdmin-Pro. For example, on RHEL/CentOS/Scientific Linux:

# cd /var/www/iRedAdmin-Pro-xxx/

Replace iRedAdmin-Pro-xxx with the real edition name and version number, e.g. iRedAdmin-Pro-LDAP-1.6.2, or iRedAdmin-Pro-MySQL-1.3.0.

- Try to patch it with command 'patch --dry-run':

# patch -p4 --dry-run < /root/delete_amavisd_db.patch
patching file libs/amavisd/log.py

You should get the same output message "patching file libs/amavisd/log.py" as above command. If it's not the same, do *NOT* execute below commands, and reply this forum post to let me know it doesn't work for you.

- If you got the same output message, it's safe to apply this patch immediately (without '--dry-run')

# patch -p4 < /root/delete_amavisd_db.patch
patching file libs/amavisd/log.py

- Restarting Apache web server is required.

Please reply this forum post to share with us whether it works for you.

Post's attachments

delete_amavisd_db.patch 1 kb, 85 downloads since 2011-10-21 

You don't have the permssions to download the attachments of this post.

8 Reply by micentosh

  • micentosh
  • Member
  • Offline
  • Registered: 2011-02-18
  • Posts: 18

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

at paching i got this message:

Hunk #1 FAILED at 38.
1 out of 1 hunk FAILED -- saving rejects to file libs/amavisd/log.py.rej

9 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Hi @micentosh,

Which version of iRedAdmin-Pro do you use? This patch is for iRedAdmin-Pro-LDAP-1.6.2 and iRedAdmin-Pro-MySQL-1.3.0.

10 Reply by micentosh

  • micentosh
  • Member
  • Offline
  • Registered: 2011-02-18
  • Posts: 18

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

for iRedAdmin-Pro-LDAP-1.6.2

11 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Then you must patched before, could you please try to edit file libs/amavisd/log.py manually with attached patch?

12 Reply by micentosh

  • micentosh
  • Member
  • Offline
  • Registered: 2011-02-18
  • Posts: 18

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Thanks ZhangHuangbin, after I edited libs/amavisd/log.py manuali my problem was solved.
U r the best

13 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

micentosh wrote:

after I edited libs/amavisd/log.py manuali my problem was solved.

Glad to hear that, i will commit this fix into next release of iRedAdmin-Pro.
Thanks for your feedback. smile

14 Reply by luciono

  • luciono
  • Member
  • Offline
  • From: Italy
  • Registered: 2011-04-04
  • Posts: 21

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

thanks a lot Zhang!!

bye bye!!
Luciano

15 Reply by web2works

  • web2works
  • Member
  • Offline
  • Registered: 2010-03-25
  • Posts: 62

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

micentosh wrote:

Hunk #1 FAILED at 38.
1 out of 1 hunk FAILED -- saving rejects to file libs/amavisd/log.py.rej

==== Provide basic information to help troubleshoot ====
- iRedMail version: LDAP 1.6.2
- Linux/BSD distribution name and version: Debain Squeeze
- Any related log? Log is helpful for troubleshooting.
====

I received the same error as above.

I then manually edited: amavisd/log.py.rej and restart apache, when I try to access iredadmin again I get "internal server error"

I do not know how to read a .patch file so I am guessing I change this code?

sql = '''content IN ('U', 'M', 'H', 'O', 'C') \
        AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))
''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS

self.db.delete(
    'msgrcpt',
    where='''mail_id IN (SELECT mail_id FROM msgs WHERE %s)''' % sql,
)
self.db.delete(
    'msgs',
    where='%s' % sql,
)

16 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Hi @web2work,

I mailed you a modified file, you can replace libs/amavis/log.py directly.

17 Reply by peterkorsuize

  • peterkorsuize
  • Member
  • Offline
  • Registered: 2011-09-26
  • Posts: 3

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

It does appear that the patch is specifically for ldap and not mysql. Any chance to get a patch for mysql?

18 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Hi @peterkorsuize,

Just mailed you with patched file, please let me know if it doesn't work for you.

19 Reply by chadm

  • chadm
  • Member
  • Offline
  • Registered: 2011-07-30
  • Posts: 2

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Can you also send me the proper log.py for iRedAdmin-Pro-MySQL-1.3.0?

The attached patch file is specifically created for the LDAP version.

--- a/admin/ldap/1.6.2/libs/amavisd/log.py    Thu Oct 13 21:38:28 2011 +0800
+++ b/admin/ldap/1.6.2/libs/amavisd/log.py    Fri Oct 21 17:06:57 2011 +0800

20 Reply by mike.f

  • mike.f
  • Member
  • Offline
  • Registered: 2011-04-21
  • Posts: 84

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

==== Provide basic information to help troubleshoot and get quick answer ====
- iRedMail version: iRedAdmin-Pro-LDAP-1.6.2
- Linux/BSD distribution name and version: debian 6.0.2
- Any related log? Log is helpful for troubleshooting.
====

trying to patch but getting errors ... somehow the line do not fit alltogether

to be shure patching the right file

/usr/share/apache2/iRedAdmin-Pro-LDAP-1.6.2/libs/amavisd/log.py
MD5 5319f6a1fcb8ec04ef6222799a730b83
SHA1 9fc42418a2837b06c5b21a65d9bd5250830109ba
SHA256 94b05835a8aa1dcc54bd91c31d01e0abc1bfbfa8db12bc47dff5f82c4d5de53f

these are the line using "nl" that would be patched

    25                    #   - U: unchecked
    26                    #   - M: bad-mime
    27                    #   - H: bad-header
    28                    #   - O: oversized
    29                    #   - C: clean
       
    30                    sql = '''content IN ('U', 'M', 'H', 'O', 'C') \
    31                            AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %d DAY))
    32                    ''' % settings.AMAVISD_REMOVE_MAILLOG_IN_DAYS
       
    33                    self.db.delete(
    34                        'msgrcpt',
    35                        where='''mail_id IN (SELECT mail_id FROM msgs WHERE %s)''' % sql,
    36                    )
    37                    self.db.delete(
    38                        'msgs',
    39                        where='%s' % sql,
    40                    )
    41                    session['RemovedSentReceived'] = True
    42                except:
    43                    pass
       
    44            # Always return True.
    45            return True

21 Reply by mayday

  • mayday
  • Member
  • Offline
  • Registered: 2011-04-28
  • Posts: 7

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

can i also have the patch for mysql pls

22 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Hi @mayday,

I sent you patched file, you can override original file directly.

Also, new versions of iRedMail (0.7.4) and iRedAdmin-Pro will be available soon. smile

23 Reply by bow

  • bow
  • Member
  • Offline
  • Registered: 2011-12-23
  • Posts: 1

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

Hi

I am stuck at this  sql command.
It does not went through,  how long does this Alter command takes??

$ mysql -uroot -p
mysql> USE amavisd;
mysql> ALTER TABLE `msgs` ADD INDEX `msgs_idx_content` (`content`);

24 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,770

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

bow wrote:

It does not went through,  how long does this Alter command takes??

mysql> ALTER TABLE `msgs` ADD INDEX `msgs_idx_content` (`content`);

It depends on how many records you have in this table. The more records you have, the longer you have to wait.

25 Reply by mike.f

  • mike.f
  • Member
  • Offline
  • Registered: 2011-04-21
  • Posts: 84

Re: [SOLVED] Error: (1205, 'Lock wait timeout exceeded

please send me a patched file too smile