Asked  7 Months ago    Answers:  5   Viewed   26 times

Is there any query/way to show the last queries executed on ALL servers?

 Answers

51

For those blessed with MySQL >= 5.1.12, you can control this option globally at runtime:

  1. Execute SET GLOBAL log_output = 'TABLE';
  2. Execute SET GLOBAL general_log = 'ON';
  3. Take a look at the table mysql.general_log

If you prefer to output to a file instead of a table:

  1. SET GLOBAL log_output = "FILE"; the default.
  2. SET GLOBAL general_log_file = "/path/to/your/logfile.log";
  3. SET GLOBAL general_log = 'ON';

I prefer this method to editing .cnf files because:

  1. you're not editing the my.cnf file and potentially permanently turning on logging
  2. you're not fishing around the filesystem looking for the query log - or even worse, distracted by the need for the perfect destination. /var/log /var/data/log /opt /home/mysql_savior/var
  3. You don't have to restart the server and interrupt any current connections to it.
  4. restarting the server leaves you where you started (log is by default still off)

For more information, see MySQL 5.1 Reference Manual - Server System Variables - general_log

Tuesday, June 1, 2021
 
VostanAzatyan
answered 7 Months ago
90

If you want to select the ID of the most recently inserted row in a table with an AUTO_INCREMENT column, you will likey be interested in MySQL's LAST_INSERT_ID function.

Wednesday, March 31, 2021
 
nighter
answered 9 Months ago
45

Assuming you are on a nix os, the best approach would actually to tail the mysql log itself.
You might learn some interesting things out of it.

log in Ubuntu when installing from repository

tail -f  /var/log/mysql/mysql.log

As mentioned below, this is a huge performance killer (well, all logs have some performance impact). So, make sure you use it only on your dev/QA machines and only for short periods on your production machine.

Saturday, May 29, 2021
 
waylaidwanderer
answered 7 Months ago
15

I've been doing a bit of research on this this morning and looks like I have come across much of the same search results as you. Ultimately it looks to me like there's no way to loop over all table columns and reference the corresponding old/new values. I'm settling on explicitly checking each column and then logging:

IF (NEW.fld1 <> OLD.fld1) OR (NEW.fld1 IS NOT NULL AND OLD.fld1 IS NULL) OR (NEW.fld1 IS NULL AND OLD.fld1 IS NOT NULL) THEN
 INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
 VALUES ("tblname", "fld1", OLD.fld1, NEW.fld1); 
END IF; 

IF (NEW.fld2 <> OLD.fld2) OR (NEW.fld2 IS NOT NULL AND OLD.fld2 IS NULL) OR (NEW.fld2 IS NULL AND OLD.fld2 IS NOT NULL) THEN
 INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
 VALUES ("tblname", "fld2", OLD.fld2, NEW.fld2); 
END IF; ...

I found an inkling of another solution here. In theory you could have 3 delimited lists, one for column names, one for old vals and one for new vals. You would have to explicitly reference the old and new vals, but that would be one line (easier to maintain or copy/paste to implement on other tables) and you could then loop. So in pseudo code it would look something like this:

fields_array = concat_ws(",", "fld1", "fld2");
old_vals_array = concat_ws(",", OLD.fld1, OLD.fld2);
new_vals_array = concat_ws(",", NEW.fld1, NEW.fld2);

foreach fields_array as key => field_name
     INSERT INTO `fld_audit` (`table`, `fldname`, `oldval`, `newval`)
     VALUES ("tblname", field_name, old_vals_array[key], vew_vals_array[key]);

I haven't thought this through too much. You might need to call into a stored procedure rather than set variables. But it might be worth looking into. I've spent enough time on my triggers already. Not sure I could validate (to my boss) trial and error time on a more elegant solution.

Wednesday, August 11, 2021
 
iceduck
answered 4 Months ago
75

After any query the last mysqli error is stored in the connection resourece. To retrieve it:

echo mysqli_error($con);

If you want to kill the script directly after the query if there is an error:

mysqli_query($con, "some query") or die(mysqli_error($con));

There is also error number if you ever have the need:

echo mysqli_errno($con);

Example:

mysqli_select_db($con, "something") or die(mysqli_error($con));
//if the database is not found it'd print out 'Unknown database "something"'
Friday, October 22, 2021
 
Aniket Thakur
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share