Solved

only want the last query (looking for mysql answer)

Posted on 2013-01-30
10
403 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
10 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:rgb192
Comment Utility
>>
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
Comment Utility
see my first paragraph above
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:rgb192
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 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

16 Experts available now in Live!

Get 1:1 Help Now