Error 1304 when creating a procedure after a drop if exists I have some php code that runs queries stored in a database to update some thousand client databases. Now, for the first time, I needed to create a stored procedure because the update is complex. I'm executing the following queries:

DROP PROCEDURE IF EXISTS updateProc100;
DELIMITER $$
CREATE PROCEDURE updateProc100()
  MODIFIES SQL DATA
  SQL SECURITY INVOKER
BEGIN
...
...
END$$
DELIMITER ;
CALL updateProc100();
DROP PROCEDURE updateProc100;

This code is executed the first time each client accesses the system, in its own database, and is executed only one time.

My problem is that I'm receiving error messages from a small number of client systems with the following MySQL error:

SQLSTATE[42000]: Syntax error or access violation: 1304 PROCEDURE updateProc100 already exists

No other procedures with that name exists in any of the databases and even if they existed, they should be dropped by the first statement. The number of error messages is somewhat low, but even so, I don't understand how this can be. I've manually checked each of the databases and no stored procedure exists.

I've googled about this problem, but all the answers that I find say to do what I'm already doing, executing a DROP IF EXISTS before the CREATE.

Does anyone have any idea for the cause of the error?
Thanks

Is every client's updateProc100 identical?  Or is that proc generated by code had has code specific to the client?

If it is identical to all clients, suggest creating, but not dropping, a single copy of it in some 'common' DATABASE.  Then only do

 CALL common.updateProc100();

If there is something tailored to each client, could it be an argument?  And then do prepare+execute to build and run the specific stuff?

Back to what you currently have...

Procs are specific to a DATABASE.
Is each client specific to that database?  That is, are you sure there was a USE (or equivalent) before doing the drop+create+call+drop?
What happens at "intervals after" the login?
Can a client be logged in from two places?
Does the Procedure have an interlock so that it cannot be executed twice?
What is the current mechanism for saying "client 'X' has already called 'proc100'"?


What is the meaning of WAL file name? We know WAL file locate in the $PGDATA/pg_xlog directory,  but I don't know the meaning of WAL file ,such as a WAL file which named "0000000A00005283000000E0",

I see some's blog say WAL file name  respect a format name subdivided into 3 sequences of 8 hexa digits defining:

Timeline ID

Block ID

Segment ID

But I still don't understand this. anyone can explain this?

--query

francs=> select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 5283/D9C2A320
(1 row)

francs=> select pg_xlogfile_name(pg_current_xlog_insert_location());
     pg_xlogfile_name
--------------------------
 0000000A00005283000000E0

WAL file naming is really an implementation detail. See the source code - starting with the implementation of pg_xlogfile_name in src/backend/access/transam/xlogfuncs.c, which uses XLogFileName in src/include/access/xlog_internal.h:

#define XLogFileName(fname, tli, logSegNo)  \
    snprintf(fname, MAXFNAMELEN, "%08X%08X%08X", tli,       \
             (uint32) ((logSegNo) / XLogSegmentsPerXLogId), \
             (uint32) ((logSegNo) % XLogSegmentsPerXLogId))

From there you can see that in current PostgreSQL versions the archive name is eight zero-padded hex digits of timeline ID, then a somewhat oddly formatted value for the segment that works out to the high 32 bits of a 64-bit segment number zero-padded out to 8 hex digits, then the low 32 bits zero padded out to 8 hex digits. That format is really a historical quirk.

You can work that out because of the definition of XLogSegmentsPerXLogId:

#define XLogSegmentsPerXLogId   (UINT64CONST(0x100000000) / XLOG_SEG_SIZE)

which is 1 << 32 i.e. 2^32, so really the XLogFileName is just taking the high and low 32 bits.

That said, you shouldn't need to do anything with WAL based on the file names, except use them to uniquely identify a WAL file, so I'm wondering why you're asking this. What're you trying to achieve?


SQL Server 2012 - Large backup file size We have the following setup:

Once a week (Sunday at midnight), a full database backup is performed. A new BAK file is created for this purpose, let's call it Backup1.bak.
From then, a transactional backup is performed every hour. This is appended to Backup1.bak.
Once a day at midnight (except Sundays) a differential backup is performed. This is also appended to Backup1.bak.
The following Sunday at midnight, Backup1 is zipped and moved to external media, a new full backup is created and is placed in Backup2.bak. Backup1.bak is deleted.

The database is running in full recovery mode, and all backups only operate on one database in one instance.

This works well in SQL Server 2008. However, we are now starting to roll out SQL Server 2012 implementations, and the problem we are encountering is that the BAK file is growing exponentially. A typical MDF file size rarely exceeds 15Gb and LDF rarely exceeds 50Gb, but the BAK file is sometimes as big as 900Gb by Saturday. This is naturally causing capacity constraints on client sites with 1Tb HDDs.

Is there a different way of doing transactional and differential backups in 2012 as compared to 2008?

Did you use INIT as a parameter to the backup command?

Otherwise, the dump is appended to the backup file.


Striped Ola Backup Job Not Running If I run the backup in a job this works:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LL_DB -Q "
EXECUTE [dbo].[DatabaseBackup] @Databases = 'AVAILABILITY_GROUP_DATABASES',
@Directory = N'\\backup\SQL\',
@BackupType = 'FULL', @ChangeBackupType = 'Y', @Verify = 'Y', @CleanupTime = 48, @CheckSum = 'Y', @LogToTable = 'Y'" -b

If I stripe the backup like so:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LL_DB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'AVAILABILITY_GROUP_DATABASES',
@Directory = N'\\backup\SQL\Drive1\, \\backup\SQL\Drive2\, \\backup\SQL\Drive3\,
\\backup\SQL\Drive4\, \\backup\SQL\Drive5\, \\backup\SQL\Drive6\,
\\backup\SQL\Drive7\, \\backup\SQL\Drive8\, \\backup\SQL\Drive9\, \\backup\SQL\Drive10\',
@BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y',
@LogToTable = 'Y'" -b

I get the following error:

  Msg 105, Level 15, State 1, Server SQLSERVER, Line 1
  Unclosed quotation mark after the character string 'EXECUTE [dbo].[DatabaseBackup] @Databases = 'AVAILABILITY_GROUP_DATABASES',
  '.

I can execute the same code as T-SQL and it works, also if I put the T-SQL in a job it works (although it gives me error 50000, but still completes!), I've been over it a few times and can't see what I might be missing, do I need to put extra quotation marks in the SQLCMD job?

Everything needs to be on one line...not multiple so we humans can read it....same as if you were typing it into SQLCMD.  The second you hit return it starts executing from there.


Streaming Replication in PostgreSQL I'm trying to set up two PostgreSQL servers on one machine and perform a streaming replication. I have succeeded once, but when i've tried again following exactly the same steps it doesn't work.. Those are the steps:
I have $PGDATA = home/postgresql/9.1/data
and $STANDBY = home/postgresql/9.1/data2

Set up two nodes:
initdb -D $PGDATA
initdb -D $STANDBY
In the master node create a user for replication. I do that in pgAdmin (it does have superuser privileges)
In the master node in pg_hba.conf add the part that allows standby to connect:
host   replication     repuser         127.0.0.1/0            md5
In the master node in postgresql.conf set:max_wal_senders = 1
archive_mode = on
archive_command = 'cp %p ~/postgresql/backup/archivedir/%f'
wal_level = archive
wal_keep_segments = 32
Start the master node and do the base backup:psql -d dellstore2 -c "SELECT pg_start_backup('backup for replication',   true)"
rsync -av ${PGDATA}/ $STANDBY --exclude postmaster.pid
psql -d dellstore2 -c "select pg_stop_backup()"
pg_stop_backup says that everything is fine, all the WAL files were archived
In the standby (data2) node I create recovery.conf with:standby_mode = 'on'
primary_conninfo = 'host=127.0.0.1 port=5432 user=repuser password=haslo'
trigger_file = '/home/michau/postgresql/replication.trigger'
restore_command = 'cp /home/michau/postgresql/backup/archivedir/%f "%p"'
Start the master node, then start the standby node - replication should start and standby should catch up with the master. That was exactly what happened the first time.
Now when I start the standby I get: "Address already in use" error.
Of course both standby and master have the same port specified in postgresql.conf (they have exactly the same postgresql.conf files). If I change the port in standby to let's say 5433 then I get:
LOG:  database system was shut down in recovery at 2012-06-12 19:48:01 CEST
LOG:  entering standby mode
cp: cannot stat /home/michau/postgresql/backup/archivedir/000000010000000000000007: No such file or directory
LOG:  consistent recovery state reached at 0/7000070
LOG:  record with zero length at 0/7000070
cp: cannot stat /home/michau/postgresql/backup/archivedir/000000010000000000000007: No such file or directory
LOG:  streaming replication successfully connected to primary
LOG:  redo starts at 0/7000070

And it just hangs here. Running ps -ef | grep postgresql yields:
michau    2491  1898  0 19:46 pts/0    00:00:00 postgres -D /home/michau/postgresql/9.1/data
michau    2493  2491  0 19:46 ?        00:00:01 postgres: writer process
michau    2494  2491  0 19:46 ?        00:00:00 postgres: wal writer process
michau    2495  2491  0 19:46 ?        00:00:00 postgres: autovacuum launcher process
michau    2496  2491  0 19:46 ?        00:00:00 postgres: archiver process   last was 000000010000000000000008
michau    2497  2491  0 19:46 ?        00:00:00 postgres: stats collector process
michau    2571  2214  0 19:49 pts/1    00:00:00 postgres -D /home/michau/postgresql/9.1/data2
michau    2572  2571  0 19:49 ?        00:00:01 postgres: startup process   recovering 000000010000000000000009
michau    2575  2571  0 19:49 ?        00:00:01 postgres: writer process
michau    2578  2571  0 19:49 ?        00:00:02 postgres: wal receiver process   streaming 0/99782DC
michau    2579  2491  0 19:49 ?        00:00:00 postgres: wal sender process repuser 127.0.0.1(42142) streaming 0/99782DC
michau    2586  2491  0 19:51 ?        00:00:00 postgres: michau postgres ::1(49941) idle
michau    2587  2491  0 19:51 ?        00:00:01 postgres: michau dellstore2 ::1(49942) idle

The recovering 0000000010000009 where changing for a while, but for half an hour it doesn't anymore.

I'm sure there is something I must have done the first time and not written down or something, but I am at a complete loss to say what it was. I would appreciate any help.

PostgreSQL replicas never finish recovering.  This is by design.  Basically a replica is always in "recovering from disaster" mode except that it is using receiving the WAL segments from the master rather than on disk.

So what you are seeing is not cause for concern.  If it is not working yet, then you will need to provide a more detailed description of what you are trying to do and what is not working.  But as far as you are posting it seems normal.


Unable to start the mongo services on debian server Mongo DB keep on crashing automatically and all database and collection remove automatically.This is happening randomly and when I check mongod.log I can see it's automatically dropped.

Today I got the error message that mongo service unable to start, and when I start it services it give me the error

Job for mongod.service failed. See 'systemctl status mongod.service' and 'journalctl -xn' for details.

when I type this command

systemctl status mongod.service

Debian Server Configuration are: 40GB HHD 4GB RAM

I have around 2 million data as of now in mongoDB and it will increase day by day.

I have down so much of google but didn't find the perfect solution and as I'm not an expert at server level ,so can some give a suggestion or solution to how can I get rid with this problem.

That first log file tells that "client" connects to the server and gives dropDatabase command, so check your application. Why it executes dropDatabase.

What comes to the second problem, you need to check your mongodb.log, what it tells for the reason, why mongodb cannot start. Wild guess is ownership of some files (data, lock, log), all files must be owned by user what you use to start mongod process (probably mongodb -user)
Name and location of mongodb.log, you can find from /etc/mongod.conf -file


H2 ALTER TABLE ADD COLUMN BEFORE/AFTER According to H2 syntax documentation available here I should be able to execute a query like:

ALTER TABLE EMPLOYEE ADD COLUMN SECOND_NAME VARCHAR (124) AFTER FIRST_NAME;

However apparently only this query works:

ALTER TABLE EMPLOYEE ADD COLUMN SECOND_NAME VARCHAR (124);

Is it a mistake in the documentation or am I missing something?
The BEFORE and AFTER parameters would be useful to me.

It works for me. The H2 change log says it was added in version 1.3.171. Here is the original bug

alter table credentials add column domain VARCHAR(255) after credentialType;

What didn't work for me was having multiple adds inside one alter.


SQL*Plus - interactive mode on windows How can I issue commands through SQL*Plus on Windows?

In Linux is like this:

sqlplus -s /nolog<<EOF
conn username/password@database
EXECUTE some_proc;
EXIT;
EOF

And Windows?

That is a matter of Windows/batch, it is not specific to SQL*Plus.

You can do this:

(echo connect bp/bp@192.168.1.71/min19_o71
echo select * from dual;
echo exit
) | sqlplus -S /nolog

Result:

C:\Users\balaz>(echo connect bp/bp@192.168.1.71/min19_o71
More? echo select * from dual;
More? echo exit
More? ) | sqlplus -S /nolog

D
-
X

C:\Users\balaz>

Or just use a script, that method is specific to SQL*Plus:

C:\Users\balaz>type script.sql
connect bp/bp@192.168.1.71/min19_o71
select * from dual;
exit

C:\Users\balaz>sqlplus -S /nolog @script.sql

D
-
X

C:\Users\balaz>


Fastest way of restoring log shipping of multiple databases Looking for the fastest way of restoring multiple SQL Server standby databases that got out of sync.

Server A is a production server, has 50 databases configured with LSBACKUP jobs.
Server B is a standby server, has 50 databases in READONLY/STANDBY mode getting up to date with LSCOPY and LSRESTORE jobs.

The following error arises after a long maintenance*:

  The log in this backup set begins at LSN 5286000000042100001, which is too recent to apply to the database. An earlier log backup that includes LSN 5286000000039400001 can be restored.

Obviously, the required LSN is nowhere to be found so the way to fix this would be to take a full backup on production database with NORECOVERY and restore it on the standby, then restart the restoring jobs.

The issue here is doing so one by one is a long and tedious process:

What is the fastest approach to solve this problem? I'm not as much looking for a way to make the backups faster than a way to automate the whole.

* We downgraded from Enterprise to Standard and somehow the log shipping chain got broken in the process.

Below is the approach that I would take :

Stop logshipping jobs on both primary and secondary database servers.
If a FULL Backup job is run on the primary server then you have to initialize logshipping by restoring the latest full backup and a diff or subsequent log backups.
If a full NON COPY ONLY backup is not run on primary server, you can follow the rest

Take   differential backups of the databases on primary server.
Move all the backups to the secondary database server. You can use Robocopy or powershell to do this.
Restore the DIFFS on the secondary server using norecovery.
start logshipping jobs on primary and secondary.
At this point your logshipping should be resumed.


Mysql Daemon cannot start after upgrade from 5.1 to Mysql 8 I preinstalled Oracle Enteprise Linux and it automatically installed Mysql Community 5.1. But I wanna update it to 8.
After update the daemon won't start and the /var/log/mysqld.log shows that

  [ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system data file './ibdata1' is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!

Then I change the /etc/my.cnf file and add

  innodb_data_file_path = ibdata1:10M:autoextend

I tried to start it again and the log file said

  [ERROR] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9

because if that, I moved the /var/lib/mysql/ib_logfile* to backup directory and try to start mysqld again.
BUT it still won't start because the log file said

  [ERROR] [MY-012960] [InnoDB] Cannot create log files because data files are corrupt or the database was not shut down cleanly after creating the data files.
  [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
  [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
  [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
  [ERROR] [MY-010119] [Server] Aborting

Any help would be appreciated.

That is a gynormous upgrade.

Plan A:  Oracle recommends doing it in 4 steps 5.1 -> 5.5 -> 5.6 -> 5.7 -> 8.0.  And, at each step, run mysql_upgrade.

Plan B:  Alternatively, you could dump the data and hope that the reload doesn't find some syntax errors.

I fear that you may have already hosed the files too much to have a successful upgrade.


which process has created a load spike on my server last week? O.S.:Linux
MYSQL server

we got a load spike last week.

how can I find the process that has created the load spike on my server last week.

If you have the slow query log active, you might be able to identify a long running query during the timeframe of your load spike. This probably the only way to figure out the issue that has already occurred.

You can configure a script like pt-stalk to try to capture future issues, however.

Here's an example to trigger data collection if you have more than 10 connections from host '10.0.0.23' [src]:

pt-stalk --function processlist --variable Host\
 --match 10.0.0.23 --threshold 10 -- --defaults-file=./my.default.cnf


Audit specific information How can I get logon events from audit table for Feb, June, Oct and Dec?

I tried the query below, but it retrieves a lot of duplicate rows although I am using the distinct keyword in the query. Also this query takes long time.

select   distinct os_username ,username, userhost, terminal, timestamp, action_name,
         logoff_time
from     dba_audit_session
where    timestamp between '01-FEB-2017' and '28-FEB-2017'
order by timestamp;

your timestamp and logoff_time columns contained time part too, please use trunc if you do not wish to consider the time part and then use the distinct.


Setting Select Permission on 100+ tables in a database I got the 'Select permission denied' error for some of my tables in a database.

In MS SQL Server 2012, setting Select Permission on 1 table is easy. I just right click on the table > Properties > Permissions > Select the User/Role and tick the Select Permission in the 'Explicit' tab below.

But I want to do that for over a 100 tables. Is there a better way to do it?

Use GRANT statement with tables delimited by comma like below.

Grant select on tbl_1, tbl2, tbl3, tbl4, ...., tb1100 to [MyRole];


Trigger function using current row and current table name as variables (final part) Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a trigger that calls a trigger function named trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a_timetypespan()
  RETURNS trigger AS
$BODY$
DECLARE
v_timetype character varying;
v_timestmp_timetype timestamp without time zone;
v_timetypespan_resume interval;
v_stmtserial real;
v_sumtimetypespan_fnname interval;

BEGIN

IF NEW.timetype = 'lap' THEN
  SELECT timetype, timestmp, timetypespan FROM tbl_log_a WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'resume') ORDER BY stmtserial DESC LIMIT 1 INTO v_timetype, v_timestmp_timetype, v_timetypespan_resume;
    IF v_timetype = 'start' THEN
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype);
    ELSIF v_timetype = 'resume' THEN
      SELECT timestmp FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timestmp_timetype;
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype) - v_timetypespan_resume;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

ELSIF NEW.timetype = 'resume' THEN
  SELECT timestmp FROM tbl_log_a WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'lap') ORDER BY stmtserial DESC LIMIT 1 INTO v_timestmp_timetype;
    IF FOUND THEN
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype);
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

ELSIF NEW.timetype = 'total' THEN
  SELECT stmtserial FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'total' ORDER BY stmtserial DESC LIMIT 1 INTO v_stmtserial;
  SELECT SUM(timetypespan) FROM (SELECT DISTINCT ON (floor(timeidx)::int) floor(timeidx)::int timeidx, timetypespan
  FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'lap' AND stmtserial > coalesce(v_stmtserial, 0) ORDER BY 1, 2 DESC) a INTO v_sumtimetypespan_fnname;
    IF v_sumtimetypespan_fnname NOTNULL THEN
      NEW.timetypespan := v_sumtimetypespan_fnname;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE;

Trigger definition:

CREATE TRIGGER trfn_tbl_log_a_timetypespan
  BEFORE INSERT ON tbl_log_a
  FOR EACH ROW EXECUTE PROCEDURE trfn_tbl_log_a_timetypespan();

So I have to create 26 trigger functions, one for each tbl_log_%letter%. I am trying to replace all of them with a single, generic trigger function.

We worked out a solution with dynamic SQL under my previous question with a simplified function. Can the same technique be extended to this more complex scenario?

EXECUTE format($$...

Yes, this should work (untested):
CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan()  -- generic name
  RETURNS trigger AS
$func$
DECLARE
   _timetype varchar;
   _timetypespan_resume interval;
   _ct int;
BEGIN

CASE NEW.timetype
WHEN 'lap' THEN
   EXECUTE format($$
      SELECT timetype, timetypespan, age($1, timestmp)
      FROM   %s
      WHERE  fnname = $2
      AND    timetype IN ('start', 'resume')
      ORDER  BY stmtserial DESC
      LIMIT  1$$
    , TG_RELID::regclass)
   USING NEW.timestmp, NEW.fnname
   INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

   CASE _timetype
   WHEN 'start' THEN  -- do nothing

   WHEN 'resume' THEN
      EXECUTE format($$
         SELECT age($1, timestmp) - _timetypespan_resume
         FROM   %s
         WHERE  fnname = $2
         AND    timetype = 'start'
         ORDER  BY stmtserial DESC
         LIMIT  1$$
       , TG_RELID::regclass)
      USING NEW.timestmp, NEW.fnname
      INTO  NEW.timetypespan;

   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END CASE;

WHEN 'resume' THEN
   EXECUTE format($$
      SELECT age($1, timestmp)
      FROM   %s
      WHERE  fnname = $2
      AND    timetype IN ('start', 'lap')
      ORDER  BY stmtserial DESC LIMIT 1$$
    , TG_RELID::regclass)
   USING NEW.timestmp, NEW.fnname
   INTO  NEW.timetypespan;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END IF;

WHEN 'total' THEN
   EXECUTE format($$
      SELECT COALESCE(SUM(timetypespan), $1)
      FROM (
         SELECT floor(timeidx)::int, max(timetypespan) AS timetypespan
         FROM   %1$s
         WHERE  fnname = $2
         AND    timetype = 'lap'
         AND    stmtserial > coalesce(
                 (SELECT stmtserial
                  FROM   %1$s
                  WHERE  fnname = $2
                  AND    timetype = 'total'
                  ORDER  BY stmtserial DESC
                  LIMIT  1), 0)
         GROUP  BY 1
         ) sub$$
    , TG_RELID::regclass)
   USING NEW.timetypespan, NEW.fnname
   INTO  NEW.timetypespan;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END IF;
END CASE;

RETURN NEW;

END
$func$  LANGUAGE plpgsql;

This is a follow-up to my previous answer to your previous question. Find explanation there:

Trigger function using current row and current table name as variables

While being at it, I simplified a couple of things. Like: the second SELECT in your first ELSIF branch was just a repetition of the SELECT one level above. I merged that for no cost.
I also removed a couple of unnecessary intermediary steps and assigned to fields of NEW directly where applicable. That's why I could remove most of your variables.
Aside: If timeidx only has positive numbers you can use the cheaper trunc(timeidx) instead of floor(timeidx).
To understand the dynamic part more easily ...
If you would implement the same just for tbl_log_a - i.e., the effectively executed code for tbl_log_a (after applying format() and EXECUTE) looks like this:
...

CASE NEW.timetype
WHEN 'lap' THEN
   SELECT timetype, timetypespan, age(NEW.timestmp, timestmp)
   FROM   tbl_log_a
   WHERE  fnname = NEW.fnname
   AND    timetype IN ('start', 'resume')
   ORDER  BY stmtserial DESC
   LIMIT  1
   INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

   CASE _timetype
   WHEN 'start' THEN  -- do nothing

   WHEN 'resume' THEN
      SELECT age(NEW.timestmp, timestmp) - _timetypespan_resume
      FROM   tbl_log_a
      WHERE  fnname = NEW.fnname
      AND    timetype = 'start'
      ORDER  BY stmtserial DESC
      LIMIT  1
      INTO  NEW.timetypespan;

...


Steal ownership on database from user that no longer exists We have a few SQL Server 2008 R2 databases where its member domain was changed (i.e. it was connected to DOMAINA and is now on DOMAINB). We have three databases where the owner of the database is DOMAINA\User, which no longer exists.

I have tried logging on to SQL Server as DOMAINB\Administrator and sa and run the following command:

ALTER AUTHORIZATION ON DestDB TO sa

Error:

  Msg 15151, Level 16, State 1, Line 1
  Cannot find the object 'DestDB', because it does not exist or you do not have permission.

I was under the (obviously incorrect) assumption that sa could do virtually anything on the server.

How can I get ownership of these databases back?

The syntax you're using is attempting to change ownership of the object 'DestDB' within whatever database context you're currently in.  You need to specify that you're changing database ownership by using the class definition:

ALTER AUTHORIZATION ON DATABASE::[DestDB] TO [sa]


Why does my query end up with two seeks instead of one and how do I fix that? I have these two tables:

CREATE TABLE [TaskItems] (
    [ItemId]             UNIQUEIDENTIFIER NOT NULL UNIQUE PRIMARY KEY,
    [LastOperationTime]  DATETIME NULL DEFAULT GETUTCDATE(),
    [OwnerId]            UNIQUEIDENTIFIER NOT NULL
    -- more columns which are irrelevant
)

CREATE INDEX [TaskItemsByOwnerIdAndLastOpTimeIndex]
     ON [TaskItems](OwnerId, LastOperationTime)

CREATE TABLE [TaskOwners] (
    [OwnerId]         UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID() UNIQUE PRIMARY KEY
    -- more columns which are irrelevant
)

and this query:

SELECT
  COUNT( OwnerId ),
  SUM(CASE WHEN LastOperationTime > DATEADD(day, -3, GETUTCDATE()) THEN 1 ELSE 0 END),
  SUM(CASE WHEN LastOperationTime > DATEADD(day, -5, GETUTCDATE()) THEN 1 ELSE 0 END)
FROM
(SELECT
  O.OwnerId,
  MAX( LastOperationTime) as LastOperationTime
    FROM
      TaskOwners AS O
      LEFT OUTER JOIN TaskItems AS TI ON O.OwnerId = TI.OwnerId
  GROUP BY O.OwnerId) AS Whatever

Note that LastOperationTime is declared NULL which is a design error but fixing it would require recreating the index which is kind of problematic in production database. Anyway at any given moment there're no items which have LastOperationTime set to null.

TaskOwner table contains about 25 thousand rows. TaskItems contains about ten million rows and some rare "owners" have a million or so "items" owned while most of the "owners" own no "items" or some of them own hundreds or thousand "items".

So I run the query and request the actual execution plan and it has two index seeks (the two tree items in the lower right).

One of those has "object" TaskItemsByOwnerIdAndLastOpTimeIndex, "predicate" TaskObjects.LastOperationTime IS NULL and takes about 49% time. The other one has "object" TaskItemsByOwnerIdAndLastOpTimeIndex, "predicate" TaskObjects.LastOperationTime IS NOT NULL and takes 47% time. Both have "seek predicates" TaskObjects.OwnerId==TaskOwners.OwnerId and "backwards" direction in the plan tree. Both have "execution count" equal to the number of rows in the TaskOwners table.

The results of those seeks are then filtered (TOP(1)), concatenated, aggregated and fed into the JOIN which is implemented using "nested loops".

The question is why those two seeks are there (instead of one scan). Why does the query engine suddenly care so much about NULL vs NOT NULL that it initiates two separate queries? How do I get rid of those?

Try:

LEFT OUTER JOIN TaskItems AS TI ON O.OwnerId = TI.OwnerId
AND LastOperationTime IS NOT NULL

The engine could be grabbing the Null row separately, as it's a slightly different scenario to not finding one. But as you essentially want the same behaviour, just out the explicit filter in there, within the ON clause of your LEFT JOIN. Then the only NULLs will be as a result of the outer join.


denydatawriter does not work in SQL Server I have a couple of admin roles in a SQL Server 2012.

To prevent myself from changing data in one database, I added my login as a member of denydatawriter role to that database.

It seems does not work, still able to insert

Did I miss anything?

Because you're also a member of sysadmin, all security checks are skipped.


How to verify the restored MS SQL DB from Backup matches with the source? Once you restore the backup, how do you verify that the source and destination are in sync (schema, datatypes, number of records etc)? assuming that the source has not change. Appreciate your insights.

Once the backup is restored to target DB, it will be reflecting the state of source DB at the time of backup. From my understanding, restore is almost a drop and recreate of DB in target server. Normally, if Prod DB is restored to a non-prod - logins/Users accounts may be different, or if any alerts are configured to make any data changes thru triggers/SP's once a DB is restored. Otherwise, there is no possibility of difference. Even if you restore DB on a server with a different collation, it works unless you create some tempdb tables and try joining them to the restored DB. Hope this helps.


Using "Where clause" In OPENROWSET as variable I need to have a where clause in OPENROWSET but it is not always fix.
It might be null sometimes.
I have a procedure as you can see here :

ALTER PROCEDURE [dbo].[OpenExcel] @TableName VARCHAR(2000)
    ,@FileName VARCHAR(2000)
    ,@ExcelName VARCHAR(2000)
    ,@SheetName VARCHAR(100)
    ,@Header VARCHAR(50)
    ,@WhereClause VARCHAR(1000)
    ,@ResultOpenExcel INT OUTPUT
AS
BEGIN
    DECLARE @Detail NVARCHAR(max)
        ,@RowFlag BIT = 0

    SET @ResultOpenExcel = 0

    BEGIN TRY
        BEGIN TRANSACTION OpenExcel

        DECLARE @Provider NVARCHAR(2000);

        SET @Provider = 'Microsoft.ACE.OLEDB.12.0';

        DECLARE @date DATETIME

        SET @date = getdate()

        DECLARE @SQL NVARCHAR(MAX) = ' SELECT * ,' + QUOTENAME(@ExcelName, '''') + ' AS ExcelName ,' + QUOTENAME(@date, '''') + ' AS TimeStamp
   INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName + ';hdr=' + @Header + '' + ''',
                     ''select * from [' + @SheetName + '$]' + @WhereClause + ' );

     insert into ' + @TableName + '
     select *
     from #TTT
     drop Table #TTT'

        PRINT @SQL

        EXEC (@SQL)

        --------------------------------
        DECLARE @RowCount INT = @@ROWCOUNT;

        IF (@RowCount > 1)
            SET @RowFlag = 1
        SET @ResultOpenExcel = 1

        COMMIT TRANSACTION OpenExcel
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION OpenExcel

        SET @ResultOpenExcel = 0

        SELECT @Detail = ErrorMessage
        FROM dbo.GetErrorInfo()

        EXECUTE [TestTest].[dbo].[Sp_Log] @TableName
            ,'[dbo].[OpenExcel]'
            ,@Detail
    END CATCH

    RETURN @ResultOpenExcel
END

I need to pass the "Where Clause" it might sometimes have a content and sometimes not. For instance it might be  like this : Where age >'23'.
I'm testing my procedure but it gives me this error :

Incorrect syntax near 23

What is wrong with the openrowset part of my query?

the @Print part show me this in my example:

"SELECT * ,'SMR1' AS ExcelName ,'Oct  6 2018  4:07PM' AS TimeStamp
   INTO #TTT
     from openrowset('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=C:\NewExcelFile\SMR1.xlsx;hdr=yes',
                     'select * from [Sheet1$]where Age >'23' );"

It seems that my query is correct so what is wrong here?

I've solved my own problem by rewriting my code like this:

DECLARE @date DATETIME

SET @date = getdate()

DECLARE @SQL NVARCHAR(MAX) = ' SELECT * ,' + QUOTENAME(@ExcelName, '''') + ' AS ExcelName ,' + QUOTENAME(@date, '''') + ' AS TimeStamp
    INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName + ';hdr=' + @Header + '' + ''',
                     ''select * from [' + @SheetName + '$]'')' + @WhereClause + ';
     insert into ' + @TableName + '
     select *
     from #TTT
     drop Table #TTT'

PRINT @SQL

EXEC (@SQL)


VACUUM FULL fails with 'ERROR: Check free disk space' I am using Postgres 9.4. I have a database that \l+ tells me is 164 GB. When I try to run VACUUM FULL on the database, I get this error (after many hours):

ERROR:  could not extend file "base/18222/20547.2": wrote only 4096
        of 8192 bytes at block 279347
HINT:  Check free disk space.

I have 320GB of SSD on the server of which 89GB is available:

$ df -h
Filesystem                 Size  Used Avail Use% Mounted on
rootfs                     315G  211G   89G  71% /
udev                        10M     0   10M   0% /dev
tmpfs                      6.4G  192K  6.4G   1% /run
/dev/disk/by-label/DOROOT  315G  211G   89G  71% /
tmpfs                      5.0M     0  5.0M   0% /run/lock
tmpfs                       13G  4.0K   13G   1% /run/shm

I can't easily add more disk space to the server. Is there anything else I can do?

I found this question, so I can try that if needed. But I was wondering if I could tell VACUUM FULL to work with less memory.

According to postgresql documentation:

  VACUUM FULL actively compacts tables by writing a complete new
  version of the table file with no dead space. This minimizes the size
  of the table, but can take a long time. It also requires extra disk
  space for the new copy of the table, until the operation completes.

So if you have one big table vacuum full can easily eat all your disk space. Maybe the best thing would be to do a full backup/restore - the result will be the same as if you did a vacuum full.


How do I monitor PostgreSQL WAL shipping if I'm not using streaming replication? We have a fairly simple setup to replicate from our on-premise master PostgreSQL database to our presentation tier in AWS. We're using WAL shipping using the archive_command setting. Basically the setup looks like this:

    +-------------+
    |   Master    |
    +-------------+
     WAL  |
segments  |
         \|/                     +--------------+
    +-------------+   WAL      +-+------------+ |
    |             |----------->| Hot Standby  | |
    |      S3     |  segments  |   Slaves     | |
    |             |            |              |-+
    +-------------+            +--------------+

This setup seems to generally be fairly robust, but I haven't come up with a good way to detect failures, either the master failing to push archives up or a slave or slaves failing to retrieve the log files. What's a good way to determine if a slave is up-to-date with respect to master? What's a good way to determine if the master has failed to ship a WAL file?

Just to clarify, we are using the slaves strictly as read replicas, we will never failover to them.

Here are a few ways:

Compare pg_current_xlog_location() on the primary with pg_last_xlog_replay_location() on the standby.  That will give you the lag in bytes, which might not be very useful for alerting, but it can be useful to chart it.
Monitor pg_last_xact_replay_timestamp() on the standby against the current time.
Have a cron job on the primary periodically modify a value and then check how long it takes to get to the standby.  That's essentially how it used to be done before pg_last_xact_replay_timestamp() became available.


selecting where two columns are in a set This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

I want to select data where two columns are in a set of pairs.

I'd like to avoid using a subquery, if possible.

Is there a construct in SQL that would allow me to do something like the following:

Yes, there is, almost exactly as you wrote it. Just put col1, col2 inside parentheses:

-- works in PostgreSQL, Oracle, MySQL, DB2, HSQLDB
SELECT whatever
FROM t                               --- you missed the FROM
WHERE (col1, col2)                    --- parentheses here
       IN ((val1a, val2a), (val1b, val2b), ...) ;

If you try it however in a DBMS, you may find that it doesn't work. Because not all DBMS have implemented all the features of the (evolving) SQL standard. This works in latest versions of Oracle, MySQL, Postgres, DB2 and HSQLDB (it was not well optimized in MySQL and not use indexes, so it should be avoided there unless they fixed it in 5.7).

See MySQL documentation about IN operator and Postgres documentation about Row constructors. The two* (or more) values in parentheses is called a row-constructor.

Other ways that express the same idea:

-- works in PostgreSQL, DB2
SELECT whatever
FROM t
WHERE (col1, col2)
       IN ( VALUES (val1a, val2a), (val1b, val2b), ...) ;

SELECT t.whatever
FROM t
  JOIN
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON (x.col1, x.col2) = (t.col1, t.col2) ;

Both work in Postgres and DB2 (afaik). The last one can be modified to work in SQL Server, too:

-- works in PostgreSQL, DB2, SQL Server
SELECT t.whatever
FROM t
  JOIN
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

It can also be modified to work everywhere, by placing the values into a (temporary or permanent) table first:

-- works everywhere
CREATE TABLE values_x
( col1  ...,
  col2  ...) ;

-- use appropriate for the DBMS syntax here
INSERT INTO values_x (col1, col2)
VALUES (val1a, val2a), (val1b, val2b), ... ;

SELECT t.whatever
FROM t
  JOIN values_x  x
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

DROP TABLE values_x ;

And there is always the long way or converting the IN to a long expression with OR that should work everywhere:

-- works in all SQL DBMS
SELECT whatever
FROM t
WHERE col1 = val1a AND col2 = val2a
   OR col1 = val1b AND col2 = val2b
   ---
   ;

*: It can actually be just one value, with ROW(v), see Postgres docs.


what is an Assert in Database world? I searched for Assert and found general explanations:

Assertion (software development)

  In computer programming, an assertion is a statement that a predicate
  (Boolean-valued function, a true–false expression) is expected to
  always be true at that point in the code. If an assertion evaluates to
  false at run time, an assertion failure results, which typically
  causes the program to crash, or to throw an assertion exception.

and

What does the “assert” keyword do?

  Assertions are generally used primarily as a means of checking the
  program's expected behavior. It should lead to a crash in most cases,
  since the programmer's assumptions about the state of the program are
  false. This is where the debugging aspect of assertions come in. They
  create a checkpoint that we simply can't ignore if we would like to
  have correct behavior.

As a database operatior I only saw asserts when data files got corrupt (repair needed, sometimes data loss). Any more examples and definition specific for database systems (both SQL and NoSQL)?

I have zero to none experience in the world of NoSQL so I'll add some perspective from a RDBM:s side of view.

I believe assertions where introduced in SQL92, the following is an example from:  https://mariadb.com/kb/en/sql-99/create-assertion-statement/

CREATE ASSERTION constraint_1
    CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE;

Meaning that CONSTRAINT_1 is violated if the average of the TABLE_1.COLUMN_1 values is less than 41.

However, I'm not sure how many RDBM:s that actually implements ASSERTION. Two substitutes often used is CHECK constraints and validation triggers. Example:

ALTER TABLE Table_1
     ADD CONSTRAINT C1 CHECK (column_1 >= 0);

Such a constraint will prevent any negative values of column_1.

SELECT statements are allowed in CHECK constraints according to standard, but likewise ASSERTIONS I don't think that this is widely implemented. Triggers are commonly use to implement set assertions. Here is one example from the DB2 catalog:

CREATE TRIGGER SYSTOOLS.POLICY_DR
NO CASCADE  BEFORE DELETE  ON SYSTOOLS.POLICY
REFERENCING OLD AS OLD_ROW
FOR EACH ROW
MODE DB2SQL
SECURED
BEGIN ATOMIC
    IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY
           WHERE OLD_ROW.MED = MED
             AND OLD_ROW.DECISION = DECISION ) = 1 ) THEN
        SIGNAL SQLSTATE '85101' ('At least one policy is required.');
    END IF;
END

When we try to delete the last policy an exception is thrown.

Other constraints such as UNIQUE or FOREIGN KEY could also be thought of as ASSERTIONS of the state of the universe.

From a philosophical point an "ASSERTION/CONSTRAINT" in a DBMS world is a stronger "rule" than an ASSERTION in a procedural/oo world. Whereas the latter verifies the state for a particular action. Example from:

https://wiki.python.org/moin/UsingAssertionsEffectively

class MyDB:
[...]
    def by_name(self, name):
        id = self._name2id_map[name]
        assert self._id2name_map[id] == name
        return id

The assertion alone not sufficient to protect MyDB. In addition we also need OO-concepts such as encapsulation, meaning that we must ensure that all possible ways to change the state of MyDB is protected by similar ASSERTIONS. A CONSTRAINT on the other hand guarantees the state no matter what action we take.

ASSERTIONS in RDBM:S and OO both have there merits, but are slightly different concepts and there is not a 1-1 mapping between the two.


SQL firebird dont select rows with 0 This works fine, except in the results, I don't want the rows where STILL_REQUIRED is 0.

I am using firebird.

Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE,
       START_OFFSET_MINS,
       TOTAL_MINUTES_REQ -
             (SELECT coalesce(SUM(TABLE_PROCESSTIMES.END_TIME-TABLE_PROCESSTIMES.START_TIME) / 60, 0)
              FROM TABLE_PROCESSTIMES
              WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
             ) AS STILL_REQUIRED
FROM TABLE_PROCESSES
ORDER BY JOBID, ORDERNUM;

The simplest is to nest it in another select with the appropriate condition:

select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, START_OFFSET_MINS, STILL_REQUIRED
from (
    Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE,
           START_OFFSET_MINS,
           TOTAL_MINUTES_REQ -
                 (SELECT coalesce(SUM(TABLE_PROCESSTIMES.END_TIME-TABLE_PROCESSTIMES.START_TIME) / 60, 0)
                  FROM TABLE_PROCESSTIMES
                  WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
                 ) AS STILL_REQUIRED
    FROM TABLE_PROCESSES
    ORDER BY JOBID, ORDERNUM
) a
where still_required <> 0


How to permanently enable query cache in MySQL? I want to know if there's a way to permanently set the size of my query cache and to make sure it's always enabled.
Right now, I'm just using the set global query_cache_size command to set the size, but when the database is restarted it goes away.

You can set this parameter in /etc/my.cnf on Red Hat or /etc/mysql/my.cnf on Debian (other systems have the my.cnf file in various locations):

# vi /etc/my.cnf

Append config directives as follows:

query_cache_size = 268435456
query_cache_type = 1
query_cache_limit = 1048576

Change the values as appropriate to your system!


Is possible to start a postgresql streaming replication using pg_dump instead of copying the database? Hi i am able to start streaming replication with postgresql, copying the database files with doing theis command:

select pg_start_backup('clone', true);

That's right, but the thing is that i need to copy all the database files in /var/lib/postgresql/data to one server to another. That files are really big because of indexes for example.

I want to know if it is possible to do the same using a simple dump and restoring it on the slave, what is smaller and faster to transfer in the network, it will work? And what should i do to work?

You cannot start streaming replication without a physical copy of the data directory; usually created with the pg_basebackup command.

The reason for this has to do with what streaming replication and WAL shipping replication do. In both cases you are applying the Write Ahead Log to the data directory using postgresql's recovery mechanism. So in order to not break database consistency you must be applying the same change set to the same starting point and you are restricted to replicating the entire database cluster. This is also why you cannot interrupt streaming or drop WAL segments, since once you have broken continuity the database server cannot guarantee that it is in a consistent state.

In postgreSQL 10 or later you could use logical replication to replicate only one database in a cluster or even a single table. And in that case you can start with a "logical copy" of the database such as are produced by pg_dump.


Computed Columns Or Views - Database Table Design I want to store values in certain units along with their corresponding conversions. So for example, there would be following columns

  DateTime
  Millimetres
  Centimetres
  Metres

For a given datetime, the user will provide me a value in any one of the units. So I will need to calculate the rest by myself. Is this better to implement using computed columns or views? I suppose if I am using views or computed columns, I will need to always provide one specific unit as input which I can do in code before inserting. Is their any other way and what is the best way to implement such requirement?

You should store any value using its ISO measure unit. Calculations and conversions should be done at the client side. Don't expect any performance gain over client side conversion when using computed columns.

As far as passing in data in any measure unit, I advise against that design: make the client pass in the data in the ISO measure unit and do any conversion on the client.

If you insist passing data in any unit, this is how an INSTEAD OF trigger could work:

CREATE TABLE length_values (
    id int PRIMARY KEY,
    length_m decimal(21,6)
)
GO

CREATE VIEW v_length_values
AS
SELECT
    id,
    length_m,
    (length_m * 1000) AS length_mm,
    (length_m / 1000) AS length_km
FROM length_values;
GO

CREATE TRIGGER TR_length_values ON v_length_values
INSTEAD OF INSERT, UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    MERGE INTO length_values AS trg
    USING INSERTED AS src
        ON trg.id = src.id
    WHEN MATCHED THEN
        UPDATE
        SET length_m = COALESCE(src.length_m, src.length_mm / 1000, src.length_km * 1000)
    WHEN NOT MATCHED THEN
        INSERT (id, length_m)
        VALUES (id, COALESCE(src.length_m, src.length_mm / 1000, src.length_km * 1000));

END

Some data to test:

INSERT INTO v_length_values (id, length_km) VALUES (1, 20);
INSERT INTO v_length_values (id, length_m) VALUES (2, 20);
INSERT INTO v_length_values (id, length_mm) VALUES (3, 1);

Results:

+----+--------------+-----------------+----------------+
| id |   length_m   |    length_mm    |   length_km    |
+----+--------------+-----------------+----------------+
|  1 | 20000.000000 | 20000000.000000 | 20.00000000000 |
|  2 | 20.000000    | 20000.000000    | 0.02000000000  |
|  3 | 0.001000     | 1.000000        | 0.00000100000  |
+----+--------------+-----------------+----------------+


Find percentage of NULL storage in Oracle table I have a table in Oracle 11g Standard One Edition:

table1

col1 col2 col3 col4 col5 col6 col7 col8       col9 col10 col11
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    NULL 2    3    4    5    NULL 1 Jan 2009 19   21    22
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    9    2    3    4    5    A    NULL       19   21    22
1    NULL 2    3    4    5    B    NULL       19   21    22

The table desc is:

Name                 Null Type
-------------------- ---- -------------
COL1                      NUMBER
COL2                      NUMBER
COL3                      NUMBER
COL4                      NUMBER
COL5                      NUMBER
COL6                      NUMBER
COL7                      VARCHAR2(255)
COL8                      DATE
COL9                      DATE
COL10                     DATE
COL11                     VARCHAR2(255)

I need to find out what is the percentage of storage a table consumes with values NULL?

Example: table1 storage consumed is 1 GB, and NULLs inside it consumes 100MB, so, NULL takes up 10% of the storage.

Also, are there alternate representations of NULL in ORACLE?

NULL value

  Absence of a value in a column of a row. Nulls indicate missing,
  unknown, or inapplicable data. A null should not be used to imply any
  other value, such as zero.

There are no alternate definitions of NULL in Oracle.  A column can't take less space than NULL unless the column doesn't exist at all.  Also, if a null value is in the last column of the row, the row will take less space than the same null column as an earlier column.  Here is the wording in the documentation:

  In general, try to place columns that frequently contain nulls last so
  that rows take less space. Note, though, that if the table you are
  creating includes a LONG column as well, then the benefits of placing
  frequently null columns last are lost.

Since in your example data, none of the rows with nulls have them last, we can ignore this space optimization.  We can also not compare the lack of a column to a column with nulls as that would be the true "space" of a null, which according to some block dumps I did is one byte per null per row.

In this case we are actually comparing the space of a null vs. the space of a value.  Because any value will by definition use more space than a null, my answer is that, a null value will decrease not increase the amount of space consumed, therefore creating a percentage compared to the size of the table is meaningless.

You could do a block dump of a row containing a value and then set the value to null and dump the block again to see how much additional space is available, but that number and the resulting ratio you could create with it and the size of the table would be a measure of space used by values rather than space used by NULLs.


Getting duplicate record errors like "Duplicate Entry for key 'PRIMARY', error code 1062" when importing a full backup into slave We're restoring our slave with a full backup from our master, and getting a bunch of duplicate key errors.  Once the slave gets caught up, it does not throw any errors (only when it is behind the master). We replicate all of the database tables.  We use a MariaDB Galera cluster as the master, and just a single MariaDB instance as the slave.

These are the steps that are done:

Get the GTID of the master by checking the variable "gtid_binlog_position".  Save this value.
Take backup of master with the command:

mysql -u -p --routines --triggers --single-transaction --gtid --master-data --dump-slave --add-drop-database <dbnames> | gzip > /tmp/backup.sql.gz.

This backup takes around 20 minutes to complete.
Source the database into the slave.
Run RESET SLAVE ALL, and set the GTID position to the value taken in step one.  This is done with the command SET GLOBAL gtid_slave_pos = "<gtid from step 1>";, and then CHANGE MASTER TO ...

After this, I receive the "Duplicate Entry for key 'PRIMARY', error code 1062" errors.

These are the settings that are used:

 [mysqld]
 innodb_buffer_pool_size = 6G
 gtid_domain_id=100
 log-slave-updates=true
 open_files_limit=1000000
 innodb_large_prefix = on
 innodb_file_format = barracuda
 innodb_file_per_table = on

 [galera]
 binlog_format=row
 default_storage_engine=InnoDB
 innodb_autoinc_lock_mode=2
 log_slave_updates=1
 ## Below was added
 sync_binlog=1
 innodb_doublewrite=1
 query_cache_size=0'
 wsrep_provider_options="gcache.size = 5G"
 wsrep_retry_autocommit=4

I read through the post MySQL replication: 'Duplicated entry for PRIMARY key, but it doesn't seem like the solution applies in this case since I don't have those settings in question.

What would cause these duplicate key errors to appear?

From your steps, you are most likely backing up and starting replication from a different position:

  Get the GTID of the master by checking the variable "gtid_binlog_position". Save this value.

Do not use that value, between that time and the time the backup was taken (even if seconds), more rows could have been changed, which means you will not get an accurate representation of the start position- being the most probable cause of the duplicate key errors. You are taking the backup with:

  --single-transaction --gtid --master-data

You must be using InnoDB for single transaction to work properly, otherwise, your other tables will not be consistent. Then, use the binary log coordinates or the gtid position written near the start of the dump .sql file to kickstart your slave.

The mistake may be because on the official guide it mentions the SELECT of the variable, but that would only work on a running replication if the master is not being written or all the tables are locked. The same guide mentions that using the feature integrated into mysqldump or xtrabackup is a better option.

Here it is a full guide for GTIDs on MySQL (some options and features change, but it may be a better overview for the overal procedure): https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/


Clear out remnant LDF log files of Sharepoint DB not visible on SBS 2008 Domain Server? Typical ways for dealing with LDF files that take up a lot of space, recommend these processes to shrink/ eliminate the drive space being occupied.

http://www.sharepointboris.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/ and http://geekswithblogs.net/RogueCoder/archive/2008/06/03/122588.aspx

As an example of following these steps for SBS Monitoring database visible in Microsoft SQL Server Management Studio Express GUI whose path location on disk is: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data (this path not shown in screenshot)

I followed these commands on the GUI:
DB: SBSMonitoring > Tasks > Delete > Log files and gain about 1-2 GB space.

One the other hand, I find the Sharepoint related DB files under path (shown in screenshot):
C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data
But, this database is not visible in Microsoft SQL Server Management Studio Express GUI.

I've looked at several answers and tutorials on how to clear out the LDF via GUI and SQL commands, but that is assuming the DB is visible.

What steps can I take to properly clear out the LDF log files for this DB?

Do I have to first add/ connect to it via the SQL GUI?
Or clear out without adding via SQL commands?
Or clear it out with simple file delete in explorer {not recommended from what I read most places}?

This is what I'm reading so far, but haven't found a way to add/ access that DB via those Files and then use above mentioned steps & guides.

Screenshot of files, location & DBs in Microsoft SQL Server Management Studio Express GUI:

Update: Adding more information about SQL instances running based on answer, and requesting update to answer on how to add/ access & flush this DB instance.

I have never used SharePoint or worked with its databases specifically, but both links in your question indicate that you should be able to see, and work with, SharePoint databases in SQL Server Management Studio.

The fact that you cannot see them in your particular case most likely means that they are hosted by a different server instance – not the ...SBS\SBSMONITORING one you are connected to, according to your screenshots. If you do not know the name of the instance hosting your SharePoint databases, try looking it up in SQL Server Configuration Manager → SQL Server Services.

Once you have found out the name and connected to the right SQL Server instance, you should be able to follow the instructions you have discovered on the Web. But please read the answers on this question first to make sure you understand what you are doing and what the consequences may be:

Why Does the Transaction Log Keep Growing or Run Out of Space?


MySQL - How to convert an existing int column with duplicates to auto-increment? After adding some thousand records in a table, I noticed that the "Primary" and "Auto-Increment" properties of the main column are removed. Now instead of auto-incremented numbers, most of them (but not all) are just "0".

Any idea how to fix the issue? All "0" values should be updated to: Max(int) of column + 1.

Let's assume I have the following table (id is the row that we want to be autoinc, and name, all other columns):

MariaDB [test]> CREATE TABLE without_auto_inc (id int, name text);
Query OK, 0 rows affected (0.013 sec)

Let's add some sample data, with some 0s:

MariaDB [test]> insert into without_auto_inc VALUES (1, 'a'), (0, 'b'), (3, 'c'), (0, 'd'), (0, 'e'), (5, 'f'), (0, 'g'), (100, 'h');                                             Query OK, 8 rows affected (0.001 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM without_auto_inc;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    0 | b    |
|    3 | c    |
|    0 | d    |
|    0 | e    |
|    5 | f    |
|    0 | g    |
|  100 | h    |
+------+------+
8 rows in set (0.001 sec)

We can simply get the max id, reinsert (duplicate) the rows with id = 0 but with an variable incrementing its value for each selected row, and then delete the 0 rows:

MariaDB [test]> BEGIN;
SELECT max(id) INTO @id FROM without_auto_inc;
INSERT INTO without_auto_inc SELECT @id := @id + 1 as id, name FROM without_auto_inc WHERE id = 0;
DELETE FROM without_auto_inc WHERE id = 0;
COMMIT;
ALTER TABLE without_auto_inc MODIFY id int primary key auto_increment;
Query OK, 0 rows affected (0.000 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 8 rows affected (0.034 sec)
Records: 8  Duplicates: 0  Warnings: 0

You can add an ORDER BY TO the select to create the auto-inc in the order you prefer. You can also do the insert from a file if you prefer there is never duplicate rows. The exact method will depend on your constrains- make sure you are not inserting new values while it runs, or you may have undesired results/data loss (e.g. consider blocking the table for new writes). Sadly, most versions of MySQL don't allow transactional ALTERS (as in part of a larger transaction), so it will force an autocommit before running.

This is the state of the table after running:

MariaDB [test]> SELECT * FROM without_auto_inc;
+-----+------+
| id  | name |
+-----+------+
|   1 | a    |
|   3 | c    |
|   5 | f    |
| 100 | h    |
| 101 | b    |
| 102 | d    |
| 103 | e    |
| 104 | g    |
+-----+------+
8 rows in set (0.001 sec)


Behavior Differences Running SQL 2017 in 130 Compat Mode vs. Running SQL 2016 in 130 Compat Mode? We have a new application which includes ETL scripts, R, and .NET code all actively being developed on SQL 2016 architecture.

I just recently received approval to start setting up new environments with SQL 2017.

I would like to understand if there are any code migration related changes that might need to be made for a SQL 2017 installation or would running on SQL 2017 with SQL 2016 DB compatibility mode behave the same as if it was just a SQL 2016 installation running with the full SQL 2016 (130) compatibility mode?

Reading through this link I found the following

  To upgrade the SQL Server Database Engine to the latest version, while maintaining the database compatibility level that existed before the upgrade and its supportability status, it is recommended to perform static functional surface area validation of the application code in the database, by using the Microsoft Data Migration Assistant tool (DMA). The absence of errors in the DMA tool output, about missing or incompatible functionality, protects application from any functional regressions on the new target version.

Assuming this check passes, is there anything else that I should be doing or looking at?

It will not behave exactly the same. Compatibility mode works at the database level, not the Instance level, which is still 2017. But it will mostly work the same.

I would check the breaking feature list for 2017 as some Instance level changes may still affect your code, despite it being in 2016 compatibility mode. However, the breaking changes from 2017 are relatively minor so it is probably unlikely you'd be affected. In general, some version breaking changes are covered under compatibility mode and some are not.

The documentation gives good examples of this,

  For example, the FASTFIRSTROW hint was discontinued in SQL Server 2012
  (11.x) and replaced with the OPTION (FAST n ) hint. Setting the
  database compatibility level to 110 will not restore the discontinued
  hint.

and

  An example of a breaking change protected by compatibility level is an
  implicit conversion from datetime to datetime2 data types. Under
  database compatibility level 130, these show improved accuracy by
  accounting for the fractional milliseconds, resulting in different
  converted values. To restore previous conversion behavior, set the
  database compatibility level to 120 or lower.

You should also look at the deprecated feature list for 2017 and try to remove any of those areas from future development in order to future-proof your applications. And of course, any new features from 2017 may require updates to take advantage of them as well, but I get the impression you're more concerned about breaking changes.

With all that said, you're probably fine, but should still carefully and thoroughly test upgrading the compatibility mode before moving it to production.


Database design for dynamic Meta fields of an entity that shall be queried intensively I'm designing a database structure for a system at which an entity can have a dynamic set of user-defined meta attributes. The meta fields (e.g.: popularity, conversion etc.) and skill values (int, boolean or string) are dynamically entered by admins.

At the end of the data population, the admin will be able to create queries to filter out the entities based on meta values.

Normally, I would follow wordpress's DB structure of having meta table of the following structure.

Meta => id, entitiy_id, meta_key, meta_data_type, meta_value

However, I'm concerned about the performance and the query-ability of the data since I expect it to be more intensive. Not to mention that I'll be using mysql for the DBMS.

Please advice if there is a better practice than this. If not, is there any pitfalls that I should take note of before I develop this structure.

To my knowledge there are these 2 ways you can go:

If you want performance, simpler queries, easier programming, then you should make a second table with your ID as foreign key and make there a column for each of your properties that you want to store. For any new attribute you would have to change the database schema, what might be a major drawback.
The above won't work, if you need flexibility in your database schema (because your meta properties are not known in advance or are point to change), but it will add to your code and queries complexity (depending on your framework/application) and also lead to poor performance compared to the first alternative. Poor performance can be compensated to a certain extent with more hardware, but that's expensive.

It's a trade off between the two. You must judge your application scenario and decide what you need most and what is a absolute no-go. Then decide on the remaining criteria what you want to use.

I have seen and used both ways I mentioned above, and must say the second one worked better for me (didn't have high volume querying though), even if it is a antipattern from a SQL / database normalization point of view. However, we aren't payed for nice SQL database designs, but for working applications.


Database choices for big data I have many text files, their total size is about 300GB ~ 400GB. They are all in this format

key1 value_a
key1 value_b
key1 value_c
key2 value_d
key3 value_e
....

each line is composed by a key and a value. I want to create a database which can let me query all value of a key. For example, when I query key1, value_a, value_b and value_c are returned.

First of all, inserting all these files into the database is a big problem. I try to insert a few GBs size chunk to MySQL MyISAM table with LOAD DATA INFILE syntax. But it appears MySQL can't utilize multicores for inserting data. It's as slow as hell. So, I think MySQL is not a good choice here for so many records.

Also, I need to update or recreate the database periodically, weekly, or even daily if possible, therefore, insertion speed is important for me.

It's not possible for a single node to do the computing and insertion efficiently, to be efficient, I think it's better to perform the insertion in different nodes parallely.

For example,

node1 -> compute and store 0-99999.txt
node2 -> compute and store 10000-199999.txt
node3 -> compute and store 20000-299999.txt
....

So, here comes the first criteria.

Criteria 1. Fast insertion speed in distributed batch manner.

Then, as you can see in the text file example, it's better to provide multiple same key to different values. Just like key1 maps to value_a/value_b/value_c in the example.

Criteria 2. Multiple keys are allowed

Then, I will need to query keys in the database. No relational or complex join query is required, all I need is simple key/value querying. The important part is that multiple key to same value

Criteria 3. Simple and fast key value querying.

I know there are HBase/Cassandra/MongoDB/Redis.... and so on, but I'm not familiar with all of them, not sure which one fits my needs. So, the question is - what database to use? If none of them fits my needs, I even plan to build my own, but it takes efforts :/

Thanks.

I have been in a similar situation and highly recommend looking into MongoDB or HBase for your needs. The others (Cassandra/Redis) are good too, but HBase and MongoDB have been battle-tested in large environments, have a large community, good documentation (MongoDB's is better) and generally fulfill the three requirements you are looking for.


HA PostgreSQL installation using patroni and pgbouncer I'm not having a problem yet, just searching for advise

I want to build installation of PostgreSQL on 3 nodes using patroni: master and 2 standbys (for failover only). There are PostgreSQL and pgbouncer on each node with same configuration. All applications connect to PostgreSQL via pgbouncer

If PostgreSQL becomes dead, patroni automatically promotes on of the standbys as new master and everything just works fine. But if pgbouncer becomes dead nothing will happen

Is there any good way to make patroni watch pgbouncer (like it is doing for PostgreSQL) or I shouldn't use patroni for this case? What should I use?

I'm really not sure that it is what I want (cause pgbouncer is pretty stable), but here is my considerations:
- If pgbouncer becomes dead, entire node becomes useless for applications. So from application point of view it is the same as PostgreSQL becomes dead
- I don't really care about type of failure, even if pgbouncer restart could fix everything, so standby promotions suits me well

How do I suppose to solve problems like this?

Yes, you can use Consul templates for this. https://github.com/hashicorp/consul-template This will notify pgbouncer that Patroni has elected a new master.
Cheers,
Dave


how to search for a table across catalogs of informix database? I would like find schema in which a table exists in Informix database.

I know that we can use below query. But that searches only with a schema.

select tabname from "informix".systables where tabid > 99 ;

The chances are that you can find what you want by running a query against sysmaster:'informix'.systabnames  – which is a table in the sysmaster database.

SELECT * FROM sysmaster:'informix'.systabnames

Sample output (rather carefully selected rows):

partnum  dbsname    owner     tabname                       collate      dbsnum
INTEGER  CHAR(128)  CHAR(32)  CHAR(128)                     CHAR(32)     INTEGER
1048586  sysmaster  informix  sysusers                      en_US.819    1
1048641  sysmaster  informix  sysextdfiles                  en_US.819    1
1048648  sysuser    informix  sysattrtypes                  en_US.819    1
1048673  sysuser    informix  syssecpolicycomponents        en_US.819    1
1048704  sysadmin   informix  sysconstraints                en_US.819    1
1048723  sysadmin   informix  sysinherits                   en_US.819    1
1048731  sysadmin   informix  syserrors                     en_US.819    1
1048742  sysadmin   informix  sysxasourcetypes              en_US.819    1
1048750  sysadmin   informix  sysseclabelnames              en_US.819    1
1048794  sysadmin   informix  storagepool                   en_US.819    1
1048825  sysadmin   informix   124_51                       en_US.819    1
1048843  sysadmin   informix  mon_users                     en_US.819    1
1048856  sysadmin   informix  mon_prof_idx2                 en_US.819    1
1048861  unlogged   informix  syscolumns                    en_US.819    1
1048878  sysutils   informix  syssynonyms                   en_US.819    1
1048881  sysutils   informix  sysreferences                 en_US.819    1
1048888  sysutils   informix  sysprocauth                   en_US.819    1
1048891  sysutils   informix  systriggers                   en_US.819    1
1048912  sysutils   informix  sysaggregates                 en_US.819    1
1048942  sysutils   informix   102_10                       en_US.819    1
1048952  sysuser    informix  syscolumns                    en_US.819    1
1048991  logged     informix  systabauth                    en_US.819    1
1049051  unlogged   informix  sysprocauth                   en_US.819    1
1049055  mode_ansi  informix  sysprocauth                   en_US.819    1
1049110  mode_ansi  informix  sysinherits                   en_US.819    1
1049128  logged     informix  syslogmap                     en_US.819    1
1049222  utf8       informix  sysobjstate                   en_US.57372  1
1049259  utf8       informix  sysseclabelcomponentelements  en_US.57372  1
1049269  utf8       informix  syssecpolicycomponents        en_US.57372  1
1049297  unlogged   jleffler  dual                          en_US.819    1
1049298  stores     jleffler  dual                          en_US.819    1
1049303  stores     jleffler   100_1                        en_US.819    1
1049309  stores     jleffler  elements                      en_US.819    1
1049313  stores     jleffler   101_4                        en_US.819    1
1049318  stores     jleffler   101_5                        en_US.819    1
1049321  stores     jleffler   101_6                        en_US.819    1
1049341  stores     jleffler  compound                      en_US.819    1
1049343  stores     jleffler   102_18                       en_US.819    1
1049346  stores     jleffler   102_19                       en_US.819    1
1049351  stores     jleffler  compound_component            en_US.819    1
1049353  stores     jleffler   103_22                       en_US.819    1
1049356  stores     jleffler   103_23                       en_US.819    1
1049358  stores     jleffler   103_24                       en_US.819    1
1049367  stores     jleffler  isotopes                      en_US.819    1
1049368  stores     jleffler   104_30                       en_US.819    1
1049371  stores     jleffler   104_31                       en_US.819    1
1049382  stores     jleffler  named_isotopes                en_US.819    1
1049383  stores     jleffler   105_37                       en_US.819    1
1049385  stores     jleffler   105_38                       en_US.819    1

The 'table' such as " 105_38" are not tables but indexes.  There may be other tables or views in sysmaster which satisfy your question better — but it is likely that querying sysmaster is the way to get the information you're after.


List all parents/ascendants of all nodes I have the following structure:
Example Tree:

Example Table:
    [table]

    id  parent
    ----------
    1   NULL
    2   1
    3   1
    4   2
    5   2
    6   2
    7   3
    8   4

For each id, I want to list all of its parents, including its ascendants in a separate row.
Desired Output:
    id  parent
    ----------
    1   NULL
    2   1
    3   1
    4   1
    4   2
    5   1
    5   2
    6   1
    6   2
    7   1
    7   3
    8   1
    8   2
    8   4

I tried to use a CTE, but I can't seem to wrap my head around it.
    with temp(id,parent) as (
    SELECT S.id, S.parent
    FROM [table] as S

    UNION ALL

    SELECT S2.id, S2.parent
    FROM [table] as S2
    inner join temp on S2.id=temp.parent and temp.id is not null
    )

    SELECT * FROM temp order by id

I'm trying to traverse up a hierarchy tree and list all the nodes it hits in a separate line for all starting points of the tree. This is how the sample data was given to me.

You were on the right path and almost had it.  In the select of the second section the parent column should come from the cte instead of S2 and also in the second section the join was backwards (S2.id = p.parent vs S2.parent = p.id).  But that's it!

create table [table]
(
id int,
parent int
);

insert into [table] values(1,NULL),
    (2,1),
    (3,1),
    (4,2),
    (5,2),
    (6,2),
    (7,3),
    (8,4);

;WITH ctetable(id, parent, depth, path) as
(
    SELECT S.id, S.parent, 1 AS depth, convert(varchar(100), S.id) AS path
    FROM [table] as S
    UNION ALL
    SELECT S2.id, p.parent, p.depth + 1 AS depth, convert(varchar(100), (RTRIM(p.path) +'->'+ convert(varchar(100), S2.id)))
    FROM ctetable AS p JOIN [table] as S2 on S2.parent = p.id
    WHERE p.parent is not null
)
SELECT * FROM ctetable ORDER BY id, parent;


Auto-increment ids with multiple inserts in mysql/mariadb Suppose I have a table with an auto_increment column, and then insert multiple values in a single statement:

insert into foo (bar) values ('a'), ('b'), ('c');

From the documentation, last_insert_id() returns the first generated ID for the query, the ID for a in this case.

Does mysql give any guarantees that a b and c will then be assigned sequential values?

In other words, if last_insert_id() returns 1 for a, is there any guarantee that b = 2 and c = 3? Or could there be a scenario where due to multiple concurrent inserts, I could end up with something like "a = 1, b = 5, c = 9"?

Yes, for that particular statement, MySQL knows how many rows are going to be inserted, and a lock and/or a mutex is used to retrieve the ids to be inserted at the start of the query. It is fully deterministic and values will be consecutive.

However, depending on the variable innodb_autoinc_lock_mode, other non-trivial inserts like insert... on duplicate key update and insert... select may not guarantee that deterministic behavior (as they may not know the actual number of rows to be inserted). This is to allow for extra concurrency by sacrifying the consecutive values. In all cases, a unique, monotonically increasing value is guaranteed for the table as a whole, it is just that in some cases there may be gaps and interleavings.

You can know more about innodb autoinc behavior in the manual. And you can also test it in practice by doing:

$ mysql test -e "INSERT INTO test (session) VALUES (1), (sleep(1)), (1)"

while you insert as fast as possible other value in concurrency.

Addendum: I just realized a case in which you cannot assume that: if you use auto_increment_offset :-)


Quorom Configuration for 3 Node Windows cluster We have 2 node onsite and the 3rd node on DR  and there is no disk witness. Due to maintenance I set the DR site to be primary and the other 2 nodes to be manual in asynchronous mode  and after both the 2 node went down together and the whole cluster went down due to quorum could not achieve disk majority. The Dynamic quorum failed I believe since both the on site 2 nodes went simultaneously.

Since this is a 3 node cluster so the node majority quorum will be used, my question is: if I face similar scenario when the on site 2 node will go down together in that case how can I keep the cluster operational on one node / DR site..

if i face similar scenario when the onsite 2 node will go down together in that case how can i keep the cluster operational on one node / Dr site..

With the current configuration you cannot assuming the 2 nodes simultaneous went down. To avoid this you have to add one more node from  the DR side into the WSFC configuration and set up a fileshare witness(preferred). The FS witness should reside on VM or machine which is accessible to both production and DR and should be kept at "third place" which is not affected when either of DC or DR is completely down. I would suggest cloud witness here. Cloud witness do have some lags so please test the scenario with various test cases. With 4 nodes and 1 FS witness even if 2 nodes at DC went  down there are still 3 votes from 2 nodes at DR and one FS witness to keep WSFC up and running.

The quorum for this configuration would be Node and Fileshare majority.

Replying to your question from comments

  So can i have 2 Node on site , 3rd Node DR and a file share at the DR site. In that case I can survive if both the onsite node goes down.

No WSFC would not survive because there are 4 voting members in WSFC and we need more than 2(More than 50%) to be voting for quorum and WSFC to be online.

  Or during the failover before the maintenance can i just remove the 2 onsite nodes from voting rights leaving the DR with 1 vote and thus the cluster will stay alive even the 2 Node onsite went down

Now this is something different from unexpected failover, yes you can remove votes from DR nodes or, if you are using Windows server 2012 r2 and above you can gracefully shutdown the 2 nodes at DC and still the DR node would be online, this is called as  last man standing. This is because of dyanamic quorum and dynamic witness functionality of Windows server 2012 r2 server. But please keep in mind, in this scenario you are doing shutdown in planned manner and that is why dynamic quorum and witness are working if the 2 nodes go down unexpectedly nothing would work and whole WSFC would come down.

For your current configuration if suppose due to unexpected DC shutdown your WSFC is down you can use forcequorum to bring the WSFC online at DR node. This method is not complex and if some downtime can be afforded this is useful method. A simple practice and it would hardly take 5-10 mins to bring WSFC online. This will save cost of additional nodes and additional infrastructure.


Rebalance/move or strink oracle undotablespace I'm running out of space in oradata01 and oradata02. Is there a ways to move or rebalance with oradata03?

/opt/oracle/oradata01/x/undotbs01.dbf   4   100 %
/opt/oracle/oradata02/x/undotbs02.dbf   17  100 %
/opt/oracle/oradata03/x/undotbs03.dbf   25  1 %

You can simply create a new undo Ts and drop the old one .

show parameter undo

NAME TYPE VALUE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '+DATA_NEWPATH' SIZE 100M AUTOEXTEND ON;

http://www.oracle-base.com/articles/9i/AutomaticUndoManagement.php

SQL> alter system set undo_tablespace=undotbs2;

After that can drop UNDOTBS1. Take a look here for more information.

Regards


I Don't have PFILE or SPFILE just have a backup of init.ora and database is down I have lost PFILE  and don't have backup also, just have a backup of init.ora and database is down and not starting up. Does init.ora backup is useful? If yes then how to use it to create pfile or to startup database??
please any solution,
any help will be highly appreciated.

yes, backup of your parameter files is a smart thing to do, like backing up your database is a smart thing to do. If you still have an alert log file of your database, you can extract the parameter settings from there and use them to create a pfile using good old vi or whatever simple text editor you like.
Most important parameters are

instance_name
db_unique_name
control_files

Once your parameters are complete, you can verify them by trying to startup the database.

ORACLE_SID={YOUR_ORACLE_SID}
. oraenv
sqlplus / as sysdba
startup nomount pfile={your_pfile_full_name}

If this succeeds, your parameters are good enough to start an instance. Now see if your control_files parameter is OK:

alter database mount;

if your database mounts, you have access to your control file[s].

alter database open;

you could be back in business by now.
Since it is smarter to use an spfile, now is the time to create your spfile:

create spfile from pfile='{your_pfile_full_name}'

This creates the spfile for this instance with the default name in the default location (${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora). Shutdown your database and start it without specifying a pfile or spfile. It should select the just created spfile and give you back your database as it was before.


File database vs application server database for concurrent performance & bulk data I asked a question about Ripple's database implementation, and received this response:

The ripple server uses SQLite for structured data and a configurable "back end" for unstructured "bulk" storage.
The structured data consists of things like transactions indexed by which accounts they affected. The unstructured data consists of "chunks" of data indexed by hash that constitute portions of network history.
The preferred back end for bulk storage is currently RocksDB on Linux platforms.

This strikes me as strange since Ripple's structure allows the developers to place almost any demand they wish upon the server operators.  In other words, why not use a database server, specifically PostgreSQL?
I found this interesting breakdown of PostgreSQL vs SQLite, and this explanation:

It breaks down to how they implement snapshot isolation.
SQLite uses file locking as a means to isolate transactions, allowing writes to hit only once all reads are done.
Postgres, in contrast, uses a more sophisticated approach called multiconcurrency version control (mvcc), that allows multiple writes to occur in parallel with multiple reads.

First, is it true that the ideal implementation for bulk storage is to use a file database?
Second, is it true that for concurrent reads & writes, PostgreSQL vastly outperforms a file database?
Lastly, when tables approach billions of rows in length, for concurrent performance, is a file database or PostgreSQL superior?
Please assume both alternatives are ideally tuned.

First, is it true that the ideal implementation for bulk storage is to use a flat file database?

If you're talking about SQLite, it isn't a "flat file database". Sure, it's a single file database, but it's highly structured.

  Second, is it true that for concurrent reads & writes, PostgreSQL vastly outperforms a flat file database?

PostgreSQL will typically vastly outperform SQLite for workloads that have simultaneous reads and writes because readers can proceed while the table is being written to. For multiple concurrent readers and writers with non-trivial transactions there's no contest. To make this possible PostgreSQL must write each change at least twice and then do cleanup "vacuum" work later.

Often you can serialize many short read and write transactions, so they appear to execute very quickly even though they each wait a while on a lock. So for trivial reads and writes SQLite may still do very well even with several concurrent clients, so long as the lock durations are short. For long transactions it's totally hopeless.

  In other words, why not use a database server, specifically PostgreSQL?

Probably because it's trivial to set up and run SQLite, so it's way easier to embed in an application.

PostgreSQL doesn't support in-process embedding. While it's easy enough to bundle the database server it's still hassle many devs don't want. Upgrading PostgreSQL is also a pain, as the file format changes with every major version and requires an upgrade step - wheras SQLite's file format is incredibly upward- and downward-compatible.


How do I prevent my Windows Server from being automatically rebooted by SQL Server updates? Some SQL Server updates have happened outside the maintenance window, which have caused the server to be rebooted. How can I stop this from happening again in future?
Below is what I can see when looking at the Windows Update Settings:

To prevent SQL Server updates to be installed automatically, you need to:

open up PowerShell window (Command prompt will also work)
type "sconfig.cmd" and run it
press 5 to select "Windows Update Settings"
set it to "M" which is "Manual"

Important: choose "Manual", NOT "DownloadOnly", because even with "DownloadOnly" setting, some Windows Updates that Microsoft considers very important, may be still installed and machine will be rebooted (I've experienced it myself)
This way you have total control over when and how to install Windows and SQL Server updates


does pg_dumpall run vacuum? Can someone confirm if pg_dumpall does a vacuum? I cannot explain the size differences between a local and dumped database but thinking duplicate/rewritten entries are ignored

350mb inside psql with \l
350mb with du -sch at postgres' folder
158mb with pgdumpall to file

running pgsql 9.5

The size reported by psql -l includes the size of all indexes.

The dump only contains the definition of the indexes, but no data for them, that's why the dump file is (substantially) smaller then the reported size on disk.

In addition to the size of the indexes, the dump only contains the raw data, while the size on disk might include table bloat (free space inside the table's data files)


MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram Here is my database design. I've been doing a lot of reading and still don't really know what I'm doing. Hoping you guys had tips!

My DB will have millions of entries.
Many "People" rows will have null values below idCounty and some rows will be half null.
DB is written to once, never updated. Read many times and mostly queried through exact idPeople

Questions

To make first,middle,last name searchable, should I make them Primary Keys?
How would I allow to search through ItemCode.codeSection and return all "People" that match?
I hope I understand the foreign key concept. It's on the "Item" table so it allows multiple items per "People". Is this the correct way?
There will be separate lists for different counties, should I create a View for this?

Any tips that would make this better in this scenario are welcome.  Please keep in mind that I'm not doing any updates or changes to rows, just the initial record creation and later lookup.

Regarding your questions:

first, middle, and last name should not be made primary keys.  Consider placing indexes on these fields to enable better query performance when querying on them.
To query people that match a certain item code, I'm not sure you can do that just yet.  See my comment on the relationship between item and itemCode below
I think that you have the People-to-Item relationship correct.  It's one-to-many, 1 person to many items
I'm not sure whether you will need a view for counties - why do you think so?

Here are some additional thoughts:

Don't go overboard with the addition of indexes.  Though indexes can speed up query performance, they also slow down row inserts.  Only index what you must.
Consider naming all of your tables in the singular form.  So rather than "People", you would have "Person".
I think that the relationship between People and County is backwards.  1 person has one county, but one county has many persons (people).
The relationship between Item and ItemCode seems to be backwards as well.  Are there many items for a given item code?  If so, then what you're showing in the diagram needs to be reversed.  If you reverse this, then you will effectively have a many-to-many relationship between people and itemCode.  Does that sound right to you?

Keep working at it! Becoming a good database designer takes time and lots of practice.  You will learn more with each project you do.


How to Create Database Audit Specification for a column? I am creating SQL Server 2008 R2 Database audit Specification
for tblPerson.SSN

I selected

Audit Action Type: Update
Object Class: Object
Object Name: tblPerson
Principal name: Public

It captures all changes to the table. How could I see just one column?

I could filter my column out

select statement,* from fn_get_audit_file ('q:\SQLBackup\*',null,null)
where statement like '%SSN%'

I am not sure you can. However it is a bit confusing, MSDN says that it is possible:

http://msdn.microsoft.com/en-us/library/cc280645(v=sql.105)

However following the example that they give I get the following error:

  Msg 33211, Level 15, State 1, Line 0
  A list of subentities, such as columns, cannot be specified for entity-level audits.

The message above seems to be pretty definitive, I have tried their example on 2008, 2008 R2 and 2012 and I get the same message every time.


OFFSET FETCH in SQL Server 2008 I have this query to paginate the results and it was working fine on SQL Server 2012. However I had to move my database to SQL Server 2008 and now my stored procedure is not working. I did some research and got to know that OFFSET does not work in SQL Server 2008. What alternative should I use now? How i achieve the same functionality with SQL Server 2008?

Here is my stored procedure:

CREATE PROCEDURE [dbo].[sp_JobSearch]
    @EnteredKeyword nvarchar(200) = '',
    @EnteredLocation nvarchar(200) = '',
    @PageNumber INT = 1,
    @PageSize   INT = 40
AS
BEGIN
    SELECT
        MasterJob.Title, MasterJob.CompanyName,
        MasterJob.ShortDesc, MasterJob.Url,MasterJob.PostedTime,
        MasterJob.Location, JobBoard.JobBoardName
    FROM
        MasterJob
    LEFT JOIN
        JobBoard ON MasterJob.JobBoardId = JobBoard.JobBoardId
    WHERE
        (MasterJob.Title LIKE '%' + @EnteredKeyword + '%')
        AND (MasterJob.Location LIKE '%' + @EnteredLocation + '%')
    ORDER BY
        [MasterJobId]
        OFFSET @PageSize * (@PageNumber - 1) ROWS
        FETCH NEXT @PageSize ROWS ONLY;
END

Use a Common Table Expression (CTE):

    CREATE PROCEDURE [dbo].[sp_JobSearch]

    @EnteredKeyword nvarchar(200) = '',
    @EnteredLocation nvarchar(200) = '',
    @PageNumber INT = 1,
    @PageSize   INT = 40

    AS
    BEGIN

    WITH CTE AS
    (
      SELECT
        ROW_NUMBER() OVER ( ORDER BY [MasterJobId] ) AS RowNum ,
        MasterJob.Title, MasterJob.CompanyName, MasterJob.ShortDesc,
        MasterJob.Url,MasterJob.PostedTime, MasterJob.Location, JobBoard.JobBoardName
      FROM MasterJob
        LEFT JOIN JobBoard ON MasterJob.JobBoardId = JobBoard.JobBoardId
      WHERE
      (MasterJob.Title LIKE '%' + @EnteredKeyword + '%')
      AND( MasterJob.Location LIKE '%' + @EnteredLocation + '%' )
    )
    SELECT
      Title, CompanyName, ShortDesc, Url, PostedTime, Location, JobBoardName
    FROM CTE
    WHERE
      (RowNum > @PageSize * (@PageNumber - 1) )
      AND
      (RowNum <= @PageSize * @PageNumber )
    Order By RowNum

    END


Returning ssis errors to sql queries I have created a web site using VS 2013 that uses T-SQL queries to execute SSIS packages (created in VS2012). This seems to work however a couple of issues have come up. I must confess this is the first time my organization has attempted to use these 3 "system" together. So, we may be making some architectural mistakes.

1) The ETL package reports success, but doesn't logically complete. For example a lookup may error may occur so all the records are shuffled off to a text file. However, the because the ETL completes the SQL query believes it completes successfully and so doesn't throw an error to the web page.

2) The ETL package has an error but the web page reports successful completion.

A concern is that once the SQL query calls the SSIS package, the SQL query and SSIS package executions become parallel instead of sequential so the SQL query completes and reports success whether or not the SSIS package has actually completed execution. So, then question becomes how can I determine if the SSIS package has successfully complete exiting from the web page?

Regards, Tom

Try SSIS Logging:

You can use SSIS Logging for your SSIS package to push your errors to places you can monitor them. For example, we use SSIS logging to push our SQL maintenance errors to a local database/server, to a remote database/server, and also to a CSV file on a file share as shown in Figure A.

The SSIS logging that get's piped to SQL Server goes to a system table it creates in the database of your choosing called dbo.sysssislog. Once errors are piped to the sysssislog SQL table you can query it directly to check for errors using VS. (sysssislog is too raw a format for me personally. I parse this table to another table that has less and yet more meaningful information via a SQL Server job).

Or alternately, you can use SQL Server mail to continually check the sysssislog table and e-mail or text you if there's an error. If it's relatively low priority but needs to be reviewed, you could create an SSRS report to periodically review the data in the table. This won't fix your error, but at least you'll become more aware of what's going on and maybe give you more information you can use to find the actual underlying issue.

Figure A: SSIS Logging Example--SSIS execution information gets piped to a local database, remote database, and a CSV file.

Note:

From the testing I've done, I've noticed SQL/SSIS has issues if I actually delete the dbo.sysssislog table. So, instead I truncate that table instead of deleting it. Before you accidentally delete this table, make a copy of the schema first--in case you have to manually recreate it.

p.s.

Not knowing your situation, you may need to wrap your multi-point transaction within the distributed transaction coordinator (MSDTC or DTC). That way, if one part of your transaction fails between SQL Server, The web server and SSIS, the whole transaction is rolled back.  Here is an entertaining (and hopefully useful) example(s) using DTC.

https://www.mssqltips.com/sqlservertip/3272/example-using-web-services-with-sql-server-integration-services/

http://blog.arjanfraaij.com/2010/11/enabling-distributed-transaction.html