Solved

only want the last query (looking for mysql answer)

Posted on 2013-01-30
10
410 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
category table 2 36
MS SQL Update query with connected table data 3 63
Coldfusion/Mysql page error related to dynamic table creation. 9 46
Inserting data into database 10 46
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 …
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…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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