Solved

only want the last query (looking for mysql answer)

Posted on 2013-01-30
10
412 Views
Last Modified: 2013-02-01
I want a mysql (not php ) answer for this question

list of tablenames in osticket php mysql email ticket managment system.

api_key
canned_attachment
canned_response
config
department
email
email_template
faq
faq_attachment
faq_category
faq_topic
file
file_chunk
filter
filter_rule
group_dept_access
groups
help_topic
session
sla
staff
syslog
team
team_member
ticket
ticket_attachment
ticket_email_info
ticket_event
ticket_lock
ticket_priority
ticket_thread
timezone


when I add a ticket, I want to see in what tables are inserted and what values

I do not want to look throught the php code yet
and the forums on osticket wont answer
and there is no manual

so I want to do a before and after .sql backup file and use a text comparison tool

should I do just the database or (database and information schema)

or would the last insert be stored in information schema and I just do
select last query from information schema
0
Comment
Question by:rgb192
[X]
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
10 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 38837360
Here are both answers but they may not be what you want.  MySQL keeps track of the last insert 'id' number of the last AUTO_INCREMENT column in your query.  If it does Not contain an AUTO_INCREMENT column, it does not keep track of it.

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
http://www.php.net/manual/en/function.mysql-insert-id.php

As for the general case of which tables have been used, I don't think you can find an answer for that unless it is in the MySQL logs.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38838374
Turn on MySQL slow query log and configure it to log all queries.  I suggest you do this on your local machine where you have full control (if you do this on a server where other people are accessing the database or app you will create a very big log file and you won't be able to figure out which SQL statements where caused by you):

set global slow_query_log_file='/path/to/where/you/want/the/log/file';
set global long_query_time = 0;
set global slow_query_log = 1;

Open the created log file to check what is there before you do the ticket insert.  Close it, do the ticket insert, then open it again.  You should see what statements were run.  Alternatively you can run tail or whatever equivalent to the unix tail command on the OS you are using on the file to observe it being updated in real time.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 38839860
If you have command-line access, you can use mysqldump to create the complete backups.  Then, like you say, use a text comparison tool.  That may not be the most efficient solution, but would be comprehensive.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:rgb192
ID: 38840444
>>
set global slow_query_log_file='/path/to/where/you/want/the/log/file';
set global long_query_time = 0;
set global slow_query_log = 1;



Hello,
Thank you for contacting HostGator.

You requested a log file for MySQL.

I am sorry, but there is only a single MySQL process running on the shared server. We cannot create
a log file which only shows your account's activity. If you need information about your individual
MySQL queries, you will need to have a VPS or dedicated hosting account.

If you would like more information about our VPS and dedicated hosting accounts, please let us know
and we will be happy to provide it for you.

Please feel free to reply to this ticket if you have any questions or concerns about this.




could I do this on localhost wamp
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38840932
see my first paragraph above
0
 

Author Comment

by:rgb192
ID: 38842208
on wamp

set global slow_query_log_file='c:/mysql-logs/sample.txt';
set global long_query_time = 0;
set global slow_query_log = 1;


insert into sent(posting_id) values('sample');



and the sample.txt stays as

c:\xampp\mysql\bin\mysqld.exe, Version: 5.5.16 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: C:/xampp/mysql/mysql.sock
Time                 Id Command    Argument
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38842296
Sometimes it doesn't appear right away and you might need to log off the mysql session and log back on before it takes effect.
0
 

Author Comment

by:rgb192
ID: 38842806
i am using mysql workbench query editor
how to log off mysql session and log back on
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 38842844
Have you bothered checking the manual?  The simplest way I can think of is to close the app then run it again.
0
 

Author Closing Comment

by:rgb192
ID: 38843386
thanks

start and restart works

I have a followup question about what appears in the logs (I want the logs to only show the sql commands)

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

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

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…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

724 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