Hot answers tagged

10

[mysqld] # Set Slow Query Log long_query_time = 1 slow_query_log = 1 slow_query_log_file = /usr/log/slowquery.log log_queries_not_using_indexes = 1 #Set General Log general_log = on general_log_file=/usr/log/general.log Note that enabling general_log on a production server has overhead you should avoid it. You can check problematic queries from slow log.


8

You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well In SQL Server 2012 and up, you will get a nice error Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_dboption'. Its there lying for backward compatibility ONLY. In SQL Server 2000 and up, ...


7

This means that your binary log format binlog_format is STATEMENT, which is the default binary log format. STATEMENT-based format logs the statement issued, while ROW-based format logs how individual tables were changed. The problem with STATEMENT logging is certain statements cannot guarantee that the same data written on the Master will end up on the ...


6

I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables: CREATE TABLE dbo.Users ( UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users Username NVARCHAR(128) NOT NULL UNIQUE, /* , other columns */ ); CREATE TABLE dbo.Tables ( ...


6

Going by your other questions, it sounds like you have a short archive_timeout so your WAL archives are mostly empty, but are still the full pre-allocated 16MB file. On most platforms you just gzip them, e.g. archive_command = 'gzip -c < "%p" > /archive/path/%f"' On Windows this won't work natively due to the lack of the gzip command. You'll need ...


5

Question asked There is a built-in way to log all statements inside plpgsql functions: auto-explain LOAD 'auto_explain'; SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries SET auto_explain.log_nested_statements = ON; -- statements inside functions Details under this closely related question: Postgres query plan of a UDF ...


5

Locate your postgresql.conf in the datadir Find the section that looks like this #------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - #log_destination = 'stderr' # Valid values are ...


4

If you have shell access to the server on which mysql is running, you can use tcpdump + pt-query-digest. First, do a tcpdump with the appropriate switches necessary for pt-query-digest: tcpdump -i [your interface] port [3306,etc] -s 65535 -x -nn -q -tttt > tcpdump.out Then, after you are done with your packet capture, do this: pt-query-digest tcpdump.out ...


4

What was suggested to you thus far is what can be done to bring the database to a consistent state. Here is what you need to know about InnoDB. First of all here is the InnoDB Architecture in Pictorial Form Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)? The Double Write Buffer has ...


4

Now that you've changed the recovery model on the database from full to simple and back to full again, the database is running in 'pseudo-simple' mode. Taking a database backup is required now before a log backup will do any meaningful work. See here for some further details. To summarize: In order for a log backup to be useful for a restore, there needs ...


4

Should I turn to simple recovery model and truncate? That would be the worst thing to do it would break away mirroring and if you have big database you would again have to follow the whole process of creating mirroring again that could well be a tedious task. Should I stop mirroring, do what I should do to principal database and then enable ...


4

I don't know how to do it for a client host, but for a single database user, you can use this (as a superuser): ALTER ROLE someone SET log_statement TO 'all'; Which will then turn on log_statement every time that that someone connects in the future. For a single connection which is cooperating with you, you could create this function (as a superuser): ...


3

If you use these two lines in my.cnf, I find that it works fine. This will log all queries to the server, from any source, not just PHP/PHPMyAdmin. Paul... [mysqld] general_log = on general_log_file=/users/ugrad/linehanp/mydb/logfile.txt Be careful though - enabling the general log can place a heavy load on your server. To be used sparingly for short ...


3

The problem is indeed as @Phil hinted at. To fix it, set the permissions for the _mysql user: > cd ~ > touch mysql.log > sudo chown _mysql:wheel mysql.log Restart MySQL after that and the log file will be used.


3

There is nothing native in SQL Server that is going to track that level of information. Based on what version of SQL Server you are using there is SQL Server Audit (SQL Server 2008 or higher) that can track database level access, but it does require Enterprise Edition. You could also roll out your own method of logging using Profiler or Extended Events that ...


3

Using the ALTER ROLE ... SET parameter; command, it was possible to tailor user-specific log parameters. Note that the parameter only takes effect after logout. Setting log_min_duration_statement=-1 (1st login): psql console P:\>psql -U postgres -h 132.156.208.45 -d my_db my_db=# SHOW log_min_duration_statement; log_min_duration_statement ...


2

There is another reason for “Statement may not be safe to log in statement format” warning My employer has a DB Client who is always running a query like this DELETE FROM mydb.mytable WHERE ... LIMIT 1; This query will succeed on a DB Server. However, if you are using MySQL Replication, this could present data inconsistency problems because when either ...


2

Looks like that is a default setting in postgresql 8.5 onwards to guarantee recovery of higher precisions floats.. http://archives.postgresql.org/pgsql-bugs/2009-09/msg00171.php


2

If you are using MySQL v5.6 because Performance_Schema has lot of metadata about your query/table and how the processing of a query happened (almost everything that is helpful for you to debug a problem/query internals) You can have a look at my existing answer here: Is there a way to get slow-query-log-like profiling information on a single query? ...


2

Crash-safe logged tables require at least two writes for every transaction: A write to the write-ahead log (WAL) recording the data the server intends to write to the table; then at some later stage, a write to the real table to apply the change. The real write can occur after commit, but the WAL write must happen before the commit returns (unless ...


2

LOGPRIMARY, LOGSECOND and LOGFILSIZ combine to define the maximum size of the transaction log on your server using the following formula: Total Size = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096 bytes In your case, this results in (13 + 15) * 4096 * 4096 = 469762048 bytes. The maximum transaction log size limits 2 things: 1) the absolute size of a ...


2

UPDATE: I figured it out! Because the ola hallengren scripts check for log shipping roles and skip log backups if it finds the current db in either msdb.dbo.log_shipping_primary_databases or msdb.dbo.log_shipping_secondary_databases AND because the databases I have been fighting with recently DID have old log shipping entries that were not proiperly ...


2

After some major league Googling all over the planet, I think I found the answer. http://dbaspot.com/mysql/70551-how-disable-any-logging-mysql.html https://lists.drupal.org/pipermail/support/2014-June/025447.html From these posts I saw something interesting 13679547 Connect @localhost as anonymous on test 140612 12:33:07 3 Connect gk_admin at ...


2

RAISE NOTICE is the right method. The reason RAISE NOTICE output does not appear in clog.log is that your standard error redirection is wrong at the shell level. You want: psql --log-file=plog.log -f file.sql >clog.log 2>&1 That way it redirects the standard output into clog.log and then the standard error to that standard output. The way ...


2

the logger logs every statement Then that behavior comes from log_statement: log_statement (enum) Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data definition statements,


2

This is a common case where autonomous transactions would be helpful. In a situation where you have a transaction the might or not fail, you'll want to log the error, but as an independent transaction, without affecting the main one. Example from Tom Kyte: create or replace procedure log_error( ..... ) as pragma autonomous_transaction; begin insert ...


2

Please then modify your database to be in 'SIMPLE' recovery mode instead of 'FULL' or 'BULK LOGGED'. The full recovery mode never truncates the transaction log, unless you do a transaction log backup. Only after it's backed up will SQL Server reuse the empty space in the transaction log. Now setting the DB to simple mode, taking a full backup, and ...


2

I cant offer you to dump this to the errorlog. For that you need to do some serious research and both these options are lightweight and can leave you with a simple file to look at. In SQL Server 2005 you need to create a server side trace on transaction filtering on eventsubclass 2. In SQL Server 2008R2 and later you are better off creating an extended ...


1

mysql.log files is where some distros store the MySQL error log. It contains errors, warnings, notes and every time that MySQL has started and stopped. mysql.log.3.gz is the third archived log, and if there is no mysql.log.4.gz, the latest before the currently being written now. So, it is probably not the latest (current) log. If you had a UNIX console, ...


1

This log just indicates that you didn't shut down correctly, so upon startup, InnoDB had to do a "crash recovery". It's not the crash recovery that would have lost data -- it's your operating system or hardware. Crash recovery is designed actually to avoid losing data due to crashes (that's its entire purpose). The log message indicates a severe problem: ...



Only top voted, non community-wiki answers of a minimum length are eligible