Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

only want the sql commands in a log

Posted on 2013-02-01
4
262 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 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