asked on

only want the last query (looking for mysql answer)

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

Dave Baldwin

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.

johanntagle

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.

Daniel Wilson

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.

rgb192

ASKER

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

johanntagle

see my first paragraph above

rgb192

ASKER

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

johanntagle

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.

rgb192

ASKER

i am using mysql workbench query editor
how to log off mysql session and log back on

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

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)

https://www.experts-exchange.com/questions/28016743/only-want-the-sql-commands-in-a-log.html