?
Solved

only want the last query (looking for mysql answer)

Posted on 2013-01-30
10
Medium Priority
?
413 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 84

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 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