Tag Info

Hot answers tagged

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


5

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


3

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


3

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


3

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


2

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.


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

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


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

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


2

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


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


1

One perfect example why root access even for admins is bad. Maintaining a sudoers file and a centralized database of privileged users and groups(LDAP for example) would help solving problems like this. One note: the wrongdoer might well be a cronjob. I'd check that.


1

For security concern who executed what queries at what time etc.. you should use "Audit Plugins". You can refer my existing answer here http://dba.stackexchange.com/a/62477/6037 Ofcourse you can find "mysqladmin" using shell histories or tracing "General/Error log" of MySQL server. For example if you have General log enabled either in a "Table" or "File" ...


1

Check the secure log in the event it was run via a sudo service call sudo grep mysql /var/log/secure Check to see if there's any mysql related stuff going on in cronjobs sudo grep mysql /var/log/cron Check shell histories for mysqladmin calls cd /home; for u in *: do; sudo grep mysql /home/$u/.bash_history; done Check with people you know that either ...


1

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


1

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


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


1

So, my suggestion as an actual answer: If you need it only in this function, you can do a RAISE LOG '%', your_statement;, or in your actual code: ... DECLARE exec_str text; ... --Set to NULL the contents of the current 'temp_' column exec_str := 'UPDATE '||dataset_1_row.table_name|| 'SET '||dataset_1_row.column_name||'=NULL ...


1

When you enable archive logging in a database, you're telling DB2 that you want to retain a copy of all transaction logs that have been created. LOGPRIMARY and LOGSECOND only control how many active log files can be in use in the active log path at any given time. Please read the documentation on database logging for more details.


1

See disclaimer at the end. First you need to figure out if there is a reason that the log is large/growing. A quick way to do that is to run this query (see http://sqlity.net/en/556/t-sql-tuesday-25-%E2%80%93-sql-server-tips-tricks/ for more info): SELECT log_reuse_wait_desc,name FROM sys.databases; If that comes back with "NOTHING" in the ...


1

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


1

From mysqlperformanceblog Log Sequence Number (LSN) : Log Sequence Numbers correspond to given position in the log files and typically incremented for each log record. Innodb uses number of bytes ever written to the log files however it could be something different. LSNs are often extensively used in recovery check pointing and buffer management ...


1

It sounds like you have a high volume of transactions happening with your replication. If so, that "clean up: distribution" job should probably be running far more often. It sounds to me like it's happening once a day. On my high transaction replication server I have it running every 15 minutes to keep just the latest 36 hours. That way it paces itself and ...


1

You can do that with the help of the additional module dblink. Install once per database (the one where the trigger is executed): CREATE EXTENSION dblink; You need superuser privileges (in the database cluster where the trigger runs) for some of the involved functionality. Since you want to write to the external database, you will probably want to use ...


1

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



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