Solved

only want the sql commands in a log

Posted on 2013-02-01
4
258 Views
Last Modified: 2013-02-13
this question is a followup to question
http://www.experts-exchange.com/Database/MySQL/Q_28014676.html

# Time: 130201  7:22:28
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.283016  Lock_time: 0.258015 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359721348;
insert into sent(posting_id) values('sample2');

Open in new window


I run one query
I only want row 5 in the log file

how to customize the log to show the query command instead of detailed time and detailed db information
0
Comment
Question by:rgb192
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 38843533
You cannot customize the log - well, not the way you want it.  You will need to create your own script to parse the logs to extract the queries.  All slow query log parsers I know create summary reports from the logs to show how well/badly each query performed.

Well, maybe what I wrote as a tool before can help:

https://github.com/johanntagle/modified_query_profiler

It will save the query stats to a database, and on one table there is a column for the SQL statements.  Note though that (1) they are not listed in the order they were run; and (2) they are rewritten to remove literals and combine similar queries i.e.

select * from table_name where column_name=1;

Open in new window


and

select * from table_name where column_name=2;

Open in new window


will be stored as just one:

select * from table_name where column_name=X;

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 38849154
thanks

now I need to know how to install the software on wamp

http://www.experts-exchange.com/Database/MySQL/Q_28018440.html
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38866685
I learned something new today which I think will make your life easier.  Configure MySQL to log to a database table instead.  Again you can only do this on a machine you have full control of (not on a shared hosting server):

set global log_output='TABLE';

set global long_query_time=0;

set global slow_query_log=ON;

Open in new window


log out and login back again.  Do your queries, etc.

mysql> select start_time, sql_text from mysql.slow_log where sql_text!='';

Open in new window


You will get an output like:

MariaDB [mysql]> select start_time, sql_text from slow_log where sql_text!='';
+----------------------------+---------------------------------------------------------------------------------------+
| start_time                 | sql_text                                                                              |
+----------------------------+---------------------------------------------------------------------------------------+
| 2013-02-08 10:29:25.773653 | select @@version_comment limit 1                                                      |
| 2013-02-08 10:29:34.520785 | select * from a                                                                       |
+----------------------------+---------------------------------------------------------------------------------------+

Open in new window


If you have clarifications on this please post a new question.
0
 

Author Comment

by:rgb192
ID: 38885359
almost worked

this is the new question:

http://www.experts-exchange.com/Database/MySQL/Q_28030458.html
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now