Will not work with MariaDB

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Install with maria 5.5.22 Ubuntu 11.04
2.
3.

What is the expected output? What do you see instead?

What is the output when running with the "--verbose --debug" options?

(1060, "Duplicate column name 'handler_icp_attempts'")
--
-- Re-execute with --verbose --debug for detailed message and strack trace.
--
root@aqua:/opt/Suneese/utilities/housekeeping# /usr/local/bin/mycheckpoint 
--debug --verbose --defaults-file=/root/.my-checkpoint.cnf
-- Using /root/.my-checkpoint.cnf as defaults file
-- mycheckpoint rev 208, build 201011041330. Copyright (c) 2009-2010 by Shlomi 
Noach
-- database is mycheckpoint
-- Non matching deployed revision. Will auto-deploy
-- metadata table created
-- numbers table created
-- charts_api table created
-- html_components table created
-- custom_query table created
-- custom_query_view created
-- Global status & variables recorded
-- Master and slave status recorded
-- Non-local monitoring; will not read OS data
-- status_variables table exists
-- Will add the following columns to status_variables: handler_icp_attempts
(1060, "Duplicate column name 'handler_icp_attempts'")
Traceback (most recent call last):
  File "/usr/local/bin/mycheckpoint", line 4854, in <module>
    deploy_schema()
  File "/usr/local/bin/mycheckpoint", line 4763, in deploy_schema
    upgrade_status_variables_table()
  File "/usr/local/bin/mycheckpoint", line 1926, in upgrade_status_variables_table
    act_query(query)
  File "/usr/local/bin/mycheckpoint", line 246, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1060, "Duplicate column name 'handler_icp_attempts'")
--
-- Re-execute with --verbose --debug for detailed message and strack trace.
--

What version of the mycheckpoint are you using? 308
What version of the MySQL are you using? (SELECT VERSION()) maria 5.5.22
On what operating system? Ubuntu 11.04

Please provide any additional information below.

Original issue reported on code.google.com by rasimm...@gmail.com on 8 Apr 2012 at 9:28

[GoogleCodeExporter]

That's interesting. Exactly what should not happen.
Could you issue:

SHOW COLUMNS FROM 'status_variables';

on your monitoring schema ('mycheckpoint' by default)?
I'm especially interested in anything that looks like 'handler_icp_attempts'. 
Perhaps a different capitalization?

Original comment by shlomi.n...@gmail.com on 8 Apr 2012 at 7:40

[GoogleCodeExporter]

Sorry, typo. Should be:

SHOW COLUMNS FROM status_variables;

Original comment by shlomi.n...@gmail.com on 8 Apr 2012 at 7:46

[GoogleCodeExporter]

Before pasting I should point out that in an effort to get this working, I 
installed on a "real" mysql server and exported the schema... then created the 
schema on Maria server......  but the error is exactly as you suggest. 

| handler_icp_attempts                     | bigint(20) unsigned | YES  |     | 
NULL              |                |

Original comment by rasimm...@gmail.com on 9 Apr 2012 at 7:52

[GoogleCodeExporter]

exporting from other flavour should not make a difference. I must look into 
this.

Original comment by shlomi.n...@gmail.com on 9 Apr 2012 at 8:47

[GoogleCodeExporter]

Original comment by shlomi.n...@gmail.com on 9 Apr 2012 at 8:47

• Changed state: Accepted

[GoogleCodeExporter]

The information provided by this utility is excellent so I hope you can find a 
solution to this problem :-)

Original comment by rasimm...@gmail.com on 9 Apr 2012 at 9:19

[GoogleCodeExporter]

Hi Shlomi,

confirming a problem with MariaDB 5.5. Actually, I had the same error, having 
deployed on MariaDB 5.2 before, so the mycheckpoint schema was incompatible 
with the monitored version. So in order to investigate, I did a full redeploy 
using the correct monitored host with MariaDB 5.5, and the following error 
occurs:

ubuntu@dbmaster:~$ mycheckpoint --host=localhost --monitored-host=50.18.114.106 
--database=mycheckpoint_sql1 --verbose --debug deploy
-- Using /etc/mycheckpoint.cnf as defaults file
-- mycheckpoint rev 208, build 201011041330. Copyright (c) 2009-2010 by Shlomi 
Noach
-- database is mycheckpoint_sql1
-- Deploy requested. Will deploy
-- monitored host is: 50.18.114.106
-- monitored host credentials undefined; using write host credentials
-- metadata table created
-- numbers table created
-- charts_api table created
-- html_components table created
-- custom_query table created
-- custom_query_view created
-- Global status & variables recorded
-- Master and slave status recorded
-- Non-local monitoring; will not read OS data
-- status_variables table created
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
(1064, "You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near '_diff,\n 
IF(status_variables2.handler_icp_match < status_variables1.handler_icp_m' at 
line 616")
Traceback (most recent call last):
  File "/usr/bin/mycheckpoint", line 4854, in <module>
    deploy_schema()
  File "/usr/bin/mycheckpoint", line 4767, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "/usr/bin/mycheckpoint", line 3850, in create_status_variables_views_and_aggregations
    create_status_variables_diff_view()
  File "/usr/bin/mycheckpoint", line 2031, in create_status_variables_diff_view
    act_query(query)
  File "/usr/bin/mycheckpoint", line 246, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right syntax to 
use near '_diff,\n IF(status_variables2.handler_icp_match < 
status_variables1.handler_icp_m' at line 616")

I can't see the full query output, but this will probably give you an idea of 
where the error resides.

Original comment by dragonsc...@gmail.com on 22 May 2012 at 6:33

[GoogleCodeExporter]

After more research, it looks like a MariaDB bug:

MariaDB [(none)]> show status like 'Handler_icp_attempts';
Empty set (0.00 sec)

And here's the culprit:

ubuntu@db2:~$ mysql -e "SHOW STATUS" | grep Handler | od -c
0000000   H   a   n   d   l   e   r   _   c   o   m   m   i   t  \t   0
0000020  \n   H   a   n   d   l   e   r   _   d   e   l   e   t   e  \t
0000040   0  \n   H   a   n   d   l   e   r   _   d   i   s   c   o   v
0000060   e   r  \t   0  \n   H   a   n   d   l   e   r   _   i   c   p
0000100   _   a   t   t   e   m   p   t   s      \t   0  \n   H   a   n

Space at the end of the status variable.
I will file a related bug (if it hasn't been corrected already)

Original comment by dragonsc...@gmail.com on 22 May 2012 at 6:44

[GoogleCodeExporter]

Adding trim in the code would probably do the trick to fix it though.

Original comment by dragonsc...@gmail.com on 22 May 2012 at 6:45

[GoogleCodeExporter]

Good investigation! I wouldn't have caught this.

Adding a trim it is, then.

Original comment by shlomi.n...@gmail.com on 23 May 2012 at 5:43