Tbale isn't created

[GoogleCodeExporter]

What steps will reproduce the problem?
1. [myuser@mycomputer ~]$ mycheckpoint --host=centralserver --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_centralserver 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and strack trace.
--

2. In mysql database we can find 13 just created tables :
mysql> show tables;
+-----------------------+
| Tables_in_mcp_webprod |
+-----------------------+
| alert                 |
| alert_condition       |
| alert_pending         |
| charts_api            |
| custom_query          |
| custom_query_view     |
| html_components       |
| metadata              |
| numbers               |
| status_variables      |
| sv_diff               |
| sv_latest             |
| sv_sample             |
+-----------------------+
13 rows in set (0.01 sec)

3.

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

What is the output when running with the "--verbose --debug" options?
1. [myuser@mycomputer ~]$ mycheckpoint --host=centralserver --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_centralserver 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug
-- mycheckpoint rev 208, build 201011041330. Copyright (c) 2009-2010 by Shlomi 
Noach
-- database is mcp_centralserver
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table exists
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
-- status_variables_aggregated_hour table exists
(1146, "Table 'mcp_centralserver.status_variables_aggregated_hour' doesn't 
exist")
Traceback (most recent call last):
  File "/usr/local/bin/mycheckpoint", line 4854, in ?
    deploy_schema()
  File "/usr/local/bin/mycheckpoint", line 4767, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "/usr/local/bin/mycheckpoint", line 3856, in create_status_variables_views_and_aggregations
    upgrade_status_variables_hour_aggregation_table()
  File "/usr/local/bin/mycheckpoint", line 1968, in upgrade_status_variables_hour_aggregation_table
    return upgrade_status_variables_aggregation_table("status_variables_aggregated_hour")
  File "/usr/local/bin/mycheckpoint", line 1941, in upgrade_status_variables_aggregation_table
    existing_columns = [row["Field"] for row in get_rows(query, write_conn)]
  File "/usr/local/bin/mycheckpoint", line 267, in get_rows
    cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 
'mcp_centralserver.status_variables_aggregated_hour' doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and strack trace.
--

What version of the mycheckpoint are you using?
mycheckpoint-208
I made some try with Python simple script and noarch.rpm archive. Both are same

What version of the MySQL are you using? (SELECT VERSION())
mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.16-log |
+------------+
1 row in set (0.00 sec)

What version of Python are you using? (python --version)
Python 2.4.3

On what operating system?
Linux RedHat 5 Enterprise - Kernel 2.6.18-164.el5

Please provide any additional information below.
Any idea ?? Thanks

Original issue reported on code.google.com by tda...@gmail.com on 7 Jun 2012 at 4:40

[GoogleCodeExporter]

Is this an upgrade from a previous version of mycheckpoint? From a previous 
version of MySQL?
Or is this a fresh, first time install?

There is an inconsistency in the report: once the database is "mcp_webprod", 
second time it is "mcp_centralserver". Which of the two? Could this be the 
source of trouble? 

If this is a fresh install, please try DROP DATABASE

Original comment by shlomi.n...@gmail.com on 8 Jun 2012 at 3:29

[GoogleCodeExporter]

Hi,
This is a fresh install od mycheckpoint.
Mysql centralserver and local one are in production from several months.

mcp_webprod is the real name of the local mysql, I replace his name as a try of 
anonynization of trace, but I failed ... :(

Original comment by tda...@gmail.com on 8 Jun 2012 at 9:06

[GoogleCodeExporter]

I dropped mcp_webprod database and recreate it empty.
Then re-run mycheckpoint with : mycheckpoint --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock

(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and strack trace.
--

With --verbose --debug :

[dba@frlusv112 ~]$ mycheckpoint --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug
-- mycheckpoint rev 208, build 201011041330. Copyright (c) 2009-2010 by Shlomi 
Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table exists
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
-- status_variables_aggregated_hour table exists
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
Traceback (most recent call last):
  File "/usr/local/bin/mycheckpoint", line 4854, in ?
    deploy_schema()
  File "/usr/local/bin/mycheckpoint", line 4767, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "/usr/local/bin/mycheckpoint", line 3856, in create_status_variables_views_and_aggregations
    upgrade_status_variables_hour_aggregation_table()
  File "/usr/local/bin/mycheckpoint", line 1968, in upgrade_status_variables_hour_aggregation_table
    return upgrade_status_variables_aggregation_table("status_variables_aggregated_hour")
  File "/usr/local/bin/mycheckpoint", line 1941, in upgrade_status_variables_aggregation_table
    existing_columns = [row["Field"] for row in get_rows(query, write_conn)]
  File "/usr/local/bin/mycheckpoint", line 267, in get_rows
    cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'mcp_webprod.status_variables_aggregated_hour' 
doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and strack trace.
--

Thanks

Original comment by tda...@gmail.com on 8 Jun 2012 at 9:09

[GoogleCodeExporter]

Still have 13 only tables created in target database

Original comment by tda...@gmail.com on 8 Jun 2012 at 9:10

[GoogleCodeExporter]

No worries about anonymousness :D, pretty standard DB name.

As this is a fresh install, I will ask you to further play with it (as I do not 
have an immediate insight to what went wrong):
again, please DROP DATABASE and this time:
mycheckpoint -u ... -p ... --skip-aggregation

If this works, then database is built without aggregation tables.
Next, issue:

mycheckpoint -u ... -p ... --rebuild-aggregation

What happens?

Original comment by shlomi.n...@gmail.com on 8 Jun 2012 at 9:15

[GoogleCodeExporter]

Sorry, 1st command end with error ...

[dba@frlusv112 ~]$ mycheckpoint --host=172.16.34.44 --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug 
--skip-aggregation
-- mycheckpoint rev 208, build 201011041330. Copyright (c) 2009-2010 by Shlomi 
Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table created
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
(1055, "'sv_sample.ts' isn't in GROUP BY")
Traceback (most recent call last):
  File "/usr/local/bin/mycheckpoint", line 4854, in ?
    deploy_schema()
  File "/usr/local/bin/mycheckpoint", line 4767, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "/usr/local/bin/mycheckpoint", line 3859, in create_status_variables_views_and_aggregations
    create_status_variables_hour_view()
  File "/usr/local/bin/mycheckpoint", line 2114, in create_status_variables_hour_view
    act_query(query)
  File "/usr/local/bin/mycheckpoint", line 246, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1055, "'sv_sample.ts' isn't in GROUP BY")
--
-- Re-execute with --verbose --debug for detailed message and strack trace.
--

13 database tables created. Do you want a database dump ?

Original comment by tda...@gmail.com on 8 Jun 2012 at 9:48

[GoogleCodeExporter]

Bummer. Will further investigate.

Original comment by shlomi.n...@gmail.com on 8 Jun 2012 at 11:22

[GoogleCodeExporter]

OK, you got an error in table creation, but this was not properly verbosed 
(rather, it was assumed the error was due to the table already existing). Will 
send over a more verbose version to see why you got an error

Original comment by shlomi.n...@gmail.com on 8 Jun 2012 at 11:28

[GoogleCodeExporter]

Could you grab the following code:
http://mycheckpoint.googlecode.com/svn-history/r220/trunk/src/mycheckpoint.py

You don't have to replace existing install, you can simple run from command 
line, notice the ".py" extension, so:

python mycehckpoint.py --verbose --debug ...

There should now appear a detailed stack trace for the reason the CREATE query 
is rejected. Please paste the SQL error.

Original comment by shlomi.n...@gmail.com on 8 Jun 2012 at 11:44

[GoogleCodeExporter]

Typo:

python mycheckpoint.py --verbose --debug ...

Original comment by shlomi.n...@gmail.com on 8 Jun 2012 at 11:44

[GoogleCodeExporter]

Hi,
Thanks for your time doing investigation about my case.
Please find here needed trace with your code modification (first trace don't 
use --skip-aggregation option) :

[dba@frlusv112 ~]$ ./mycheckpoint.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table exists
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
Traceback (most recent call last):
  File "./mycheckpoint.py", line 2260, in create_status_variables_hour_aggregation_table
    act_query(query)
  File "./mycheckpoint.py", line 247, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1067, "Invalid default value for 'end_ts'")
-- status_variables_aggregated_hour table exists
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
Traceback (most recent call last):
  File "./mycheckpoint.py", line 4950, in ?
    deploy_schema()
  File "./mycheckpoint.py", line 4847, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "./mycheckpoint.py", line 3914, in create_status_variables_views_and_aggregations
    upgrade_status_variables_hour_aggregation_table()
  File "./mycheckpoint.py", line 2021, in upgrade_status_variables_hour_aggregation_table
    return upgrade_status_variables_aggregation_table("status_variables_aggregated_hour")
  File "./mycheckpoint.py", line 1994, in upgrade_status_variables_aggregation_table
    existing_columns = [row["Field"] for row in get_rows(query, write_conn)]
  File "./mycheckpoint.py", line 268, in get_rows
    cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'mcp_webprod.status_variables_aggregated_hour' 
doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and stack trace.
--

[dba@frlusv112 ~]$ ./mycheckpoint.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug 
--skip-aggregation
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table exists
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
(1055, "'sv_sample.ts' isn't in GROUP BY")
Traceback (most recent call last):
  File "./mycheckpoint.py", line 4950, in ?
    deploy_schema()
  File "./mycheckpoint.py", line 4847, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "./mycheckpoint.py", line 3917, in create_status_variables_views_and_aggregations
    create_status_variables_hour_view()
  File "./mycheckpoint.py", line 2167, in create_status_variables_hour_view
    act_query(query)
  File "./mycheckpoint.py", line 247, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1055, "'sv_sample.ts' isn't in GROUP BY")
--
-- Re-execute with --verbose --debug for detailed message and stack trace.
--

Original comment by tda...@gmail.com on 11 Jun 2012 at 9:59

[GoogleCodeExporter]

Aha! 
OperationalError: (1067, "Invalid default value for 'end_ts'") 

What is your sql_mode?

SELECT @@global.sql_mode

Original comment by shlomi.n...@gmail.com on 11 Jun 2012 at 10:32

[GoogleCodeExporter]

One attempt before final, please try out:

http://mycheckpoint.googlecode.com/svn-history/r221/trunk/src/mycheckpoint.py

Let me know if this works. I do not see anything in the 5.5 changelog that 
would cause this, plus I do not know what your sql_mode is, as yet.
If the above does not fix, then I will easily produce an uglier, but perfectly 
working version.

Original comment by shlomi.n...@gmail.com on 11 Jun 2012 at 10:59

• Changed state: Started

[GoogleCodeExporter]

[dba@frlusv113 ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4549943
Server version: 5.5.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@global.sql_mode;
+-------------------------------------------------------------------------------
--------------------------------------------------------------+
| @@global.sql_mode                                                             
                                                              |
+-------------------------------------------------------------------------------
--------------------------------------------------------------+
| 
ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_
DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------
--------------------------------------------------------------+
| @@session.sql_mode                                                            
                                                              |
+-------------------------------------------------------------------------------
--------------------------------------------------------------+
| 
ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_
DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.00 sec)

Original comment by tda...@gmail.com on 11 Jun 2012 at 1:09

[GoogleCodeExporter]

[dba@frlusv112 ~]$ ./mycheckpoint.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table created
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
Traceback (most recent call last):
  File "./mycheckpoint.py", line 2260, in create_status_variables_hour_aggregation_table
    act_query(query)
  File "./mycheckpoint.py", line 247, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1067, "Invalid default value for 'end_ts'")
-- status_variables_aggregated_hour table exists
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
Traceback (most recent call last):
  File "./mycheckpoint.py", line 4950, in ?
    deploy_schema()
  File "./mycheckpoint.py", line 4847, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "./mycheckpoint.py", line 3914, in create_status_variables_views_and_aggregations
    upgrade_status_variables_hour_aggregation_table()
  File "./mycheckpoint.py", line 2021, in upgrade_status_variables_hour_aggregation_table
    return upgrade_status_variables_aggregation_table("status_variables_aggregated_hour")
  File "./mycheckpoint.py", line 1994, in upgrade_status_variables_aggregation_table
    existing_columns = [row["Field"] for row in get_rows(query, write_conn)]
  File "./mycheckpoint.py", line 268, in get_rows
    cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'mcp_webprod.status_variables_aggregated_hour' 
doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and stack trace.
--

Original comment by tda...@gmail.com on 11 Jun 2012 at 1:11

[GoogleCodeExporter]

This issue was closed by revision r222.

Original comment by shlomi.n...@gmail.com on 11 Jun 2012 at 3:56

• Changed state: Fixed

[GoogleCodeExporter]

Please review:

http://mycheckpoint.googlecode.com/svn-history/r221/trunk/src/mycheckpoint.py

This should do it. Kindly let me know.

Original comment by shlomi.n...@gmail.com on 11 Jun 2012 at 4:05

[GoogleCodeExporter]

Hi,
Thanks for your work

Problem is still there (same error message) with your r221 release)

BUT

I made a test changing mysql sql_mode variable
was: 
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_S
UBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
new: 
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_S
UBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE

==> change is ONLY_FULL_GROUP_BY disabled option
and it works now

[dba@frlusv112 ~]$ ./mycheckpoint_r221.py --host=172.16.34.44 --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug 
--skip-aggregation
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table created
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
-- sv_hour view created
-- sv_day view created
-- sv_param_change view created
-- report views created
-- 24/7 report view created
-- recent reports views created
-- sv_report_sample_recent_aggregated view created
-- reports minmax views created
-- report human views created
-- sv_report_chart_sample_timeseries view created
-- sv_report_chart_hour_timeseries view created
-- sv_report_chart_day_timeseries view created
-- report charts labels views created
-- report charts views created
-- report 24/7 chart view created
-- custom chart views created
-- custom chart flattened views created
-- sv_report_html_24_7 created
-- report html view created
-- custom_query_top_navigation_view created
-- sv_report_html_brief created
-- sv_custom_html created
-- sv_custom_html_brief created
-- alert_view created
-- alert_pending_view created
-- alert_pending_html_view created
-- alert_email_message_items_view created
-- alert_condition query view created
-- Table and views deployed
-- New entry added: id=1; ts=2012-06-12 10:12:53
-- Collecting custom data
-- No alert conditions defined
-- Status variables checkpoint complete

Is it possible to modify your code according this option ?

Original comment by tda...@gmail.com on 12 Jun 2012 at 8:18

[GoogleCodeExporter]

Still an error if I don't specify --skip-aggregation

[dba@frlusv112 ~]$ ./mycheckpoint_r221.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table created
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
Traceback (most recent call last):
  File "./mycheckpoint_r221.py", line 2260, in create_status_variables_hour_aggregation_table
    act_query(query)
  File "./mycheckpoint_r221.py", line 247, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1067, "Invalid default value for 'end_ts'")
-- status_variables_aggregated_hour table exists
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
Traceback (most recent call last):
  File "./mycheckpoint_r221.py", line 4950, in ?
    deploy_schema()
  File "./mycheckpoint_r221.py", line 4847, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "./mycheckpoint_r221.py", line 3914, in create_status_variables_views_and_aggregations
    upgrade_status_variables_hour_aggregation_table()
  File "./mycheckpoint_r221.py", line 2021, in upgrade_status_variables_hour_aggregation_table
    return upgrade_status_variables_aggregation_table("status_variables_aggregated_hour")
  File "./mycheckpoint_r221.py", line 1994, in upgrade_status_variables_aggregation_table
    existing_columns = [row["Field"] for row in get_rows(query, write_conn)]
  File "./mycheckpoint_r221.py", line 268, in get_rows
    cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'mcp_webprod.status_variables_aggregated_hour' 
doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and stack trace.
--

Original comment by tda...@gmail.com on 12 Jun 2012 at 9:17

[GoogleCodeExporter]

Seems to me like you tries r221 instead of r222 - which is my mistake, since I 
posted the wrong link:

http://mycheckpoint.googlecode.com/svn-history/r222/trunk/src/mycheckpoint.py

I will also check about the ONLY_FULL_GROUP_BY

Original comment by shlomi.n...@gmail.com on 12 Jun 2012 at 9:22

[GoogleCodeExporter]

On step forward ...

[dba@frlusv112 ~]$ ./mycheckpoint_r222.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table created
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
Traceback (most recent call last):
  File "./mycheckpoint_r222.py", line 2260, in create_status_variables_hour_aggregation_table
    act_query(query)
  File "./mycheckpoint_r222.py", line 247, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/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 'ts_diff_seconds INT UNSIGNED,         \n            aborted_clients 
BIGINT UNSIGN' at line 5")
-- status_variables_aggregated_hour table exists
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
Traceback (most recent call last):
  File "./mycheckpoint_r222.py", line 4950, in ?
    deploy_schema()
  File "./mycheckpoint_r222.py", line 4847, in deploy_schema
    create_status_variables_views_and_aggregations()
  File "./mycheckpoint_r222.py", line 3914, in create_status_variables_views_and_aggregations
    upgrade_status_variables_hour_aggregation_table()
  File "./mycheckpoint_r222.py", line 2021, in upgrade_status_variables_hour_aggregation_table
    return upgrade_status_variables_aggregation_table("status_variables_aggregated_hour")
  File "./mycheckpoint_r222.py", line 1994, in upgrade_status_variables_aggregation_table
    existing_columns = [row["Field"] for row in get_rows(query, write_conn)]
  File "./mycheckpoint_r222.py", line 268, in get_rows
    cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'mcp_webprod.status_variables_aggregated_hour' 
doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and stack trace.
--

Original comment by tda...@gmail.com on 12 Jun 2012 at 9:30

[GoogleCodeExporter]

Just for info, mycheckpoint with --skip-aggregation option start to give 
records in my mcp_webprod database and html reports are generated ...

Original comment by tda...@gmail.com on 12 Jun 2012 at 10:02

[GoogleCodeExporter]

Grr. Bad test on my side! Sorry. Here it is:

http://mycheckpoint.googlecode.com/svn-history/r223/trunk/src/mycheckpoint.py

This one works, but not with ONLY_FULL_GROUP_BY, which I will address now.
Care to open a new bug for that one?

Original comment by shlomi.n...@gmail.com on 12 Jun 2012 at 11:13

[GoogleCodeExporter]

Ok
Find here some execution trace:

[dba@frlusv112 ~]$ ./mycheckpoint_r223.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock 
--skip-aggregation --verbose --debug
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- Global status & variables recorded
-- Master and slave status recorded
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- New entry added: id=42; ts=2012-06-12 14:38:31
-- Collecting custom data
-- No alert conditions defined
-- Status variables checkpoint complete

[dba@frlusv112 ~]$ ./mycheckpoint_r223.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug 
             -- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi 
Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- Global status & variables recorded
-- Master and slave status recorded
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- New entry added: id=43; ts=2012-06-12 14:38:38
-- Collecting custom data
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
Traceback (most recent call last):
  File "./mycheckpoint_r223.py", line 4960, in ?
    write_status_variables_hour_aggregation(status_variables_insert_timestamp)
  File "./mycheckpoint_r223.py", line 4404, in write_status_variables_hour_aggregation
    num_affected_rows = act_query(query)
  File "./mycheckpoint_r223.py", line 247, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'mcp_webprod.status_variables_aggregated_hour' 
doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and stack trace.
--

Original comment by tda...@gmail.com on 12 Jun 2012 at 12:42

[GoogleCodeExporter]

Right,
but this is because you've skipped the creation of aggregation tables.
Spawn again, **just once**, with --rebuild-aggregation

Original comment by shlomi.n...@gmail.com on 12 Jun 2012 at 12:46

[GoogleCodeExporter]

...

[dba@frlusv112 ~]$ ./mycheckpoint_r223.py --host=172.16.34.44 --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug 
--rebuild-aggregation
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- Global status & variables recorded
-- Master and slave status recorded
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- New entry added: id=45; ts=2012-06-12 14:50:52
-- Collecting custom data
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
Traceback (most recent call last):
  File "./mycheckpoint_r223.py", line 4960, in ?
    write_status_variables_hour_aggregation(status_variables_insert_timestamp)
  File "./mycheckpoint_r223.py", line 4404, in write_status_variables_hour_aggregation
    num_affected_rows = act_query(query)
  File "./mycheckpoint_r223.py", line 247, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'mcp_webprod.status_variables_aggregated_hour' 
doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and stack trace.
--

Original comment by tda...@gmail.com on 12 Jun 2012 at 12:51

[GoogleCodeExporter]

...

[dba@frlusv112 ~]$ ./mycheckpoint_r223.py --host=172.16.34.44 --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug 
--rebuild-aggregation
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- Global status & variables recorded
-- Master and slave status recorded
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- New entry added: id=45; ts=2012-06-12 14:50:52
-- Collecting custom data
(1146, "Table 'mcp_webprod.status_variables_aggregated_hour' doesn't exist")
Traceback (most recent call last):
  File "./mycheckpoint_r223.py", line 4960, in ?
    write_status_variables_hour_aggregation(status_variables_insert_timestamp)
  File "./mycheckpoint_r223.py", line 4404, in write_status_variables_hour_aggregation
    num_affected_rows = act_query(query)
  File "./mycheckpoint_r223.py", line 247, in act_query
    num_affected_rows = cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1146, "Table 'mcp_webprod.status_variables_aggregated_hour' 
doesn't exist")
--
-- Re-execute with --verbose --debug for detailed message and stack trace.
--

Original comment by tda...@gmail.com on 12 Jun 2012 at 12:53

[GoogleCodeExporter]

Some trace over a dropped database :

[dba@frlusv112 ~]$ ./mycheckpoint_r223.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- 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
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- status_variables table created
-- alert_condition table created
-- alert table created
-- alert_pending table created
-- sv_latest view created
-- sv_diff view created
-- sv_sample view created
-- status_variables_aggregated_hour table created
-- status_variables_aggregated_day table created
-- sv_hour view created
-- sv_day view created
-- sv_param_change view created
-- report views created
-- 24/7 report view created
-- recent reports views created
-- sv_report_sample_recent_aggregated view created
-- reports minmax views created
-- report human views created
-- sv_report_chart_sample_timeseries view created
-- sv_report_chart_hour_timeseries view created
-- sv_report_chart_day_timeseries view created
-- report charts labels views created
-- report charts views created
-- report 24/7 chart view created
-- custom chart views created
-- custom chart flattened views created
-- sv_report_html_24_7 created
-- report html view created
-- custom_query_top_navigation_view created
-- sv_report_html_brief created
-- sv_custom_html created
-- sv_custom_html_brief created
-- alert_view created
-- alert_pending_view created
-- alert_pending_html_view created
-- alert_email_message_items_view created
-- alert_condition query view created
-- Table and views deployed
-- New entry added: id=1; ts=2012-06-12 14:52:58
-- Collecting custom data
-- No alert conditions defined
-- Status variables checkpoint complete

Original comment by tda...@gmail.com on 12 Jun 2012 at 12:53

[GoogleCodeExporter]

Please STOP !!
I made some manipulation file error while getting r223 version of mycheckpoint

Things seems to be ok for now !

[dba@frlusv112 ~]$ ./mycheckpoint_r223.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug 
--rebuild-aggregation
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- Global status & variables recorded
-- Master and slave status recorded
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- New entry added: id=4; ts=2012-06-12 14:58:07
-- Collecting custom data
-- 2012-06-12 14:58:07 Entry aggregated into status_variables_aggregated_hour
-- 2012-06-12 14:58:07 Entry aggregated into status_variables_aggregated_day
-- No alert conditions defined
-- Status variables checkpoint complete

Original comment by tda...@gmail.com on 12 Jun 2012 at 1:00

[GoogleCodeExporter]

Please STOP !!
I made some manipulation file error while getting r223 version of mycheckpoint

Things seems to be ok for now !

[dba@frlusv112 ~]$ ./mycheckpoint_r223.py --host=xxx.xxx.xxx.xxx --port=3306 
--user=mycheckpoint --password=mycheckpoint --database=mcp_webprod 
--monitored-host=localhost --monitored-socket=/tmp/mysql.sock --verbose --debug 
--rebuild-aggregation
-- mycheckpoint rev 0, build 0. Copyright (c) 2009-2010 by Shlomi Noach
-- database is mcp_webprod
-- monitored host is: localhost
-- monitored host credentials undefined; using write host credentials
-- Global status & variables recorded
-- Master and slave status recorded
-- OS CPU info recorded
-- OS load average info recorded
-- OS mem info recorded
-- OS mountpoints info recorded
-- OS page io activity recorded
-- New entry added: id=4; ts=2012-06-12 14:58:07
-- Collecting custom data
-- 2012-06-12 14:58:07 Entry aggregated into status_variables_aggregated_hour
-- 2012-06-12 14:58:07 Entry aggregated into status_variables_aggregated_day
-- No alert conditions defined
-- Status variables checkpoint complete

Original comment by tda...@gmail.com on 12 Jun 2012 at 1:02

[GoogleCodeExporter]

So, with your permission, I am changing from "fixed" to "Verified", 
notwithstanding Issue 35.

Original comment by shlomi.n...@gmail.com on 12 Jun 2012 at 4:44

• Changed state: Verified

[GoogleCodeExporter]

Ok for me
Thanks for this work

Original comment by tda...@gmail.com on 13 Jun 2012 at 7:33