mssql server set up fail with errror on debian linux

I was istalling it on a docker container based on the debian:buster image,and i had the following error:
root@25d7455d0dfc:/# /opt/mssql/bin/mssql-conf setup
Traceback (most recent call last):
  File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 361, in <module>
    main()
  File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 357, in main
    processCommands()
  File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 262, in processCommands
    COMMAND_TABLE[args.which]()
  File "/opt/mssql/bin/../lib/mssql-conf/mssql-conf.py", line 75, in handleSetup
    mssqlconfhelper.setupSqlServer(eulaAccepted, noprompt=args.noprompt)
  File "/opt/mssql/lib/mssql-conf/mssqlconfhelper.py", line 971, in setupSqlServer
    if not checkInstall():
  File "/opt/mssql/lib/mssql-conf/mssqlconfhelper.py", line 941, in checkInstall
    return runScript(checkInstallScript, True) == 0
  File "/opt/mssql/lib/mssql-conf/mssqlconfhelper.py", line 930, in runScript
    return subprocess.call([sudo, "-EH", pathToScript])
  File "/usr/lib/python2.7/subprocess.py", line 172, in call
    return Popen(*popenargs, **kwargs).wait()
  File "/usr/lib/python2.7/subprocess.py", line 394, in __init__
    errread, errwrite)
  File "/usr/lib/python2.7/subprocess.py", line 1047, in _execute_child
    raise child_exception
OSError: [Errno 2] No such file or directory

So visited the python script at line 930:
vim +930 /opt/mssql/lib/mssql-conf/mssqlconfhelper.py
And I saw this if condition:
if (runAsRoot):
    if(checkSudo() == False):
        printError(_("Elevated privileges required for this action. Please run in 'sudo' mode."))
        return (errorExitCode)
    return subprocess.call([sudo, "-EH", pathToScript])
else:
    return subprocess.call([pathToScript])

Since I'm running as root and it enters the first condition 'runAsRoot' the script run the following return subprocess.call([sudo, "-EH", pathToScript])
I changed that line to:
return subprocess.call([pathToScript])
After that, the setup command worked for me.
/opt/mssql/bin/mssql-conf setup


Pass .csv file as parameter file for expdp Oracle 11.2.0.4.0

No you cannot.  I'm not entirely sure what you are after here - I'm assuming that you want to unload a table in CSV format ?  If that's the case, then an easy way would be to download sqcl

http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html

It's free and small (10mb), and then its just a case of running a query against your table

SQL> spool my_output_file.dat
SQL> select /*csv*/ * from scott.emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17/DEC/80,800,,20
7499,"ALLEN","SALESMAN",7698,20/FEB/81,1600,300,30
7521,"WARD","SALESMAN",7698,22/FEB/81,1250,500,30
7566,"JONES","MANAGER",7839,02/APR/81,2975,,20
7654,"MARTIN","SALESMAN",7698,28/SEP/81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/MAY/81,2850,,30
7782,"CLARK","MANAGER",7839,09/JUN/81,2450,,10
7788,"SCOTT","ANALYST",7566,09/DEC/82,3000,,20
7839,"KING","PRESIDENT",,17/NOV/81,5000,,10
7844,"TURNER","SALESMAN",7698,08/SEP/81,1500,,30
7876,"ADAMS","CLERK",7788,12/JAN/83,1100,,20
7900,"JAMES","CLERK",7698,03/DEC/81,950,,30
7902,"FORD","ANALYST",7566,03/DEC/81,3000,,20
7934,"MILLER","CLERK",7782,23/JAN/82,1300,,10

14 rows selected.


Join static file xml with ID field with Database table ID field

What would be easiest probably is to build a string list of the IDs that you need to filter on, in PHP first, and pass that in as a parameter to a procedure that uses that parameter with the FIND_IN_SET() function like so:
SELECT *
FROM SomeOtherTable
WHERE FIND_IN_SET(IdField, IdListParameter)


SSMS Disk usage reports reports no data file for online database

Yes, this report fails when the database is over 2 TB.
See this connect item: SQL Server Management Studio Standard Reports - Disk Usage

Create a new 2 TB database and run the Disk Usage report to display
results.

For Disk Space Used by Data Files, rather than displaying the Filegroup Name, Logical File Name, Physical File Name, Space Reserved,
Space Used, I get a message of "There is no data file for
database."


How to create a 'keywords' table from a column's values

To query the Title column you may like to investigate Full Text Search.

To create the autocomplete list you could split the Title on space character and remove the small words, which you would keep in a separate pre-defined list, often called a "stop list."


Will insert and update wait while a table is being vacuumed?

The documentation says the following:

  ROW EXCLUSIVE

  Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

  The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other
  referenced tables). In general, this lock mode will be acquired by any
  command that modifies data in a table.

  SHARE UPDATE EXCLUSIVE

  Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode
  protects a table against concurrent schema changes and VACUUM runs.

  Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE.

There is no conflict between the two lock types involved.  This means that neither INSERT/UPDATE blocks VACUUM, nor the other way around.  This means furthermore that your queries should succeed without timeouts.  Most probably the I/O activity of vacuuming won't do you any harm.


PostgreSQL on RDS: How to bulk import data with FK constraints?

Here's a workaround that works for me:

Create the schema then dump the tables only, in order such that no FK constraint checks fail:

pg_dump --username=postgres --data-only --table=table_1 --table=table_2 --table=table_3 --table=... --format=custom foo > foo.dump

pg_restore will now work.


Varchar2 datatypes are longer than expected

My guess would be a combination of your NLS_LENGTH_SEMANTICS = 'CHAR' system parameter and a character set defined that is 4 bytes per character.

Can you check on what your NLS settings are?


Oracle.exe (SHAD) process keeps increasing

If you're on Windows and have DCD (dead connection detection) enabled then this could be relevant to your case.  There is a note on Oracle support that talks about poor implementation of DCD on windows leaving behind a bunch of shadow processes.  Without going into details as I don't think I can republish that info here, the solution was to disable DCD.  So keep a monitor on the number of shad processes and disable DCD in sqlnet.ora.  Fairly quick and painless to test if that is the cause..


Turning off auto growth for particular file

I don't believe I can force a table to be in a particular file when they are all in the same filegroup

That's correct, you do not have the direct granular control over file allocations, only filegroup.

With that in mind, you have a few options.  The first one would be to create a new filegroup and isolate whatever tables you need to in order to give you your desired end result of not filling up the drive.  Another one, which I'd argue is probably one of the better options, is to put the file in question in a location with more space, or tack on more space to the existing volume.  I'm guessing you wouldn't be asking this question if that was a possibility, though.

Another route would be to take advantage of the proportional fill behavior that SQL Server implements (which dictates it'll attempt to fill up all filegroup files at the same time) and size out (larger) the other files in the filegroup.  That should translate to a larger percent of the allocations in those other files, as they'll demand more by the ratio of free space (reference this BOL document to further understand proportional fill).  Although I personally think that's a hack and a workaround at best, and will only prolong the inevitable of filling up your drive.


Querying with multiple where conditions and multiple columns

If I understand correctly you can get it by using an OR condition in WHERE clause:

create table Policies
(
    policyId int,
    status   varchar(100),
    pendDate date,
    issueDate date
);

insert into Policies values
(1, 'Pending', '20171001', null),
(2, 'Pending', '20171002', null),
(3, 'Pending', '20171003', null),
(4, 'Pending', '20171101', null),
(5, 'Pending', '20171102', null),
(6, 'Issue', null, '20171002'),
(7, 'Issue', null, '20171003'),
(8, 'Isuue', null, '20171101');

✓

8 rows affected

select policyId,
       status,
       pendDate,
       issueDate
from   Policies
where  (status = 'Pending' and extract(year from pendDate) = 2017 and extract(month from pendDate) = 10)
or     (status = 'Issue' and extract(year from issueDate) = 2017 and extract(month from issueDate) = 10)

policyid | status  | penddate   | issuedate
-------: | :------ | :--------- | :---------
       1 | Pending | 2017-10-01 | null
       2 | Pending | 2017-10-02 | null
       3 | Pending | 2017-10-03 | null
       6 | Issue   | null       | 2017-10-02
       7 | Issue   | null       | 2017-10-03

dbfiddle here


Improving INSERT Performance with Direct-Path INSERT

Example:

alter table foo nologging;
alter session force parallel query parallel 8;
alter session force parallel dml parallel 8;
insert /*+ append */ into foo select * from bar where some_unimportant=condition;


ORA-00201: control file version 12.1.0.2.0 incompatible with ORACLE version 11.2.0.0.0

(I accept that this is not quite technically accurate, but is close enough)

The error message is telling you that the database was created in a higher version than the one you are trying to open it in.  All the database files are marked with this version.

This database was created in v12, you're trying to open it in v11.  This will not work.  There is nothing you can do except use a v12 database home to mount/open the database.

It is possible to create a database in v12 which is compatible with v11, by using the COMPATIBLE parameter.  This tells the database engine what version the database should remain compatible with, and what database features to use.


Error message when importing data into SQL (Import & Export Data Wizard)

Import it as a string and do a derived column to convert it to a float. Whatever rows fail conversion will be re-directed to the error output and you can at least see what the problem is.

Flat File connections fail completely when they encounter problems. It's generally more productive to bring them in as loosely as possible and adding structure inside SSIS.

You almost certainly don't want to be using floats anyway. Use 'numeric' once they're inside the package.


PowerShell Subsystem max_worker_threads reverts back to default after applying cumulative update for SQL Server 2016

Create an agent job that runs on start up to set the number of threads. Set the schedule option to Start Automatically when SQL server agent starts.


How to set up Oracle 19c and/or RHEL so new files created using UTL_FILE have -rw-rw-r-- (664) permissions on Linux

This depends on how you use UTL_FILE and what setup you have. Do you create files using UTL_FILE from scheduled jobs/preallocated databases processes or from databases processes spawned through the listener? Is this a RAC or single instance database? If single instance, is Grid Infrastructure in use or not?
Simplest case: single instance without Grid Infrastructure. Here just set umask to the desired value before starting the listener and the database interactively in the shell or the script used for that.
Different Permission When the Data File is Created/Added Remotely (Doc ID 961568.1)
With Grid Infrastructure: set the necessary setting as described in How to set the permission of the files created by listener using UTL_FILE on RAC(11.2-) env. (Doc ID 1980622.1)
srvctl setenv listener -t ORA_RDBMS_UMASK=002

Then restart. This sets the umask only for remote sessions connecting through the listener which spawns their processes. For scheduled jobs or shared servers or DRCP, you will need more than this.
srvctl setenv database -d orcl -t ORA_RDBMS_UMASK=002

I never tried this for the database resource in 19c. I guess it should work, but I leave the testing for you now.


Option 3 sounds like your best option as it gives you all the flexibility you need, fits into a RDBMS perfectly and follows normalisation.

Option 1: requires schema changes when new alerts are added to the system.

Option 2: More joins = slower queries.

Option 4: This does give you the flexibility you need but if you were going to do it this way then maybe consider using a document store like MongoDB.


How do I find all roles that grant access to objects under a particular schema?

I believe I got it - replying to my own question in case anyone needs the info:

select * from role_tab_privs where owner = 'SCHEMA_NAME';


Dropped column still has value when recreated with Postgres table of 150M rows

This can only be if ...

you did not actually commit your transaction (yet) and running the second query in a different transaction.
or you have other transactions writing to the table in the meantime
or something is seriously broken
or you are dealing with two different tables:

It's worth mentioning that your ALTER TABLE commands are on "public"."WorkoutExercises", while your SELECT is on "WorkoutExercises".

Can it be, there is a second table named "WorkoutExercises" in a different schema, and that schema shows up in your current search_path before public (or public is not in the search_path at all)?

To check:

SELECT n.nspname AS schemaname, c.relname, c.relkind
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  relname = 'WorkoutExercises';

And:

SHOW search_path;


While similar queries with = or < or <= will consider (and depending on cardinality estimates will use) an index on (state), a query with a condition of this type will not use an index.:

WHERE state <> 'done'

Things you can do instead:

write the condition as:

WHERE (state < 'done' OR state > 'done')

use UNION:

SELECT a.*
FROM assessments AS a
WHERE state < 'done'

UNION ALL

SELECT a.*
FROM assessments AS a
WHERE state > 'done' ;

write the condition with IN / OR:

WHERE state IN  ('draft', 'proofing', 'amends', ...)    -- everything except 'done'

WHERE (state = 'draft' OR state = 'proofing' OR state = 'amends' OR ...)


Syntax error in SQL Server while making Dynamic SQL

from the comments of @sepupic and @Scott Hodgin, I put this together:

conditional order by

DECLARE @sql nvarchar(MAX);
DECLARE @params nvarchar(100);
declare @sort_col nvarchar(20) = 'name_voter'
declare @sort_dir nvarchar(20) = 'asc'

SET @sql =
     N'SELECT id, name_voter, home_street_address_1,home_address_city
       FROM dbo.base_voter
       WITH(NOLOCK)' + NCHAR(10) +
      N'ORDER BY ' + @sort_col + SPACE(2) + @sort_dir + NCHAR(10) +
      N'OFFSET  @offset  ROWS ' + NCHAR(10) +
      N'FETCH NEXT  @limit  ROWS ONLY; '

SET @params = N' @offset int, @limit int';

--print @sql

EXECUTE sp_executesql @sql, @params, 0, 50;


Error after applying SQL Server 2008 R2 Upgrade 10.50.6542 (Latest Build)

Finally! after 3 days of trying everything under the sun I found the root cause of my problem.

The aha! moment was when I got together with a group trying to understand why this patch was deploying just fine in other servers while not in this particular one.

We noticed that something different (among other things... that we listed in a neatly organized notepad) was that this SQL Server 2008 R2 was configured for encryption in transit (a.k.a. SSL encryption) via certificates and some settings on SQL Server Configuration manager namely:

On the Protocols for [Instance_Name] and then Properties (certificates tab)
we had select the certificate we have created for the purpose of encrypted communications.
On that same window (flags tab) we set ForceEncryption option for the Database Engine to Yes

So we started to think... what if the problem is the certificate itself?. Then we went ahead and remove the encryption configuration (the patch for SQL Server Build 10.50.6542 still applied) and the connection started working, confirming that the combination of the patching plus our SSL configuration was bonkers.

After more investigation and via a process of elimination, we narrowed down the problem to the certificates we were using. The certificate was created back then with makecert (to create a self signed certificate) and was NOT using the -a parameter. This parameter tells makecert what Hash algorithm to use. If not provided, makecert will use the default MD5 signature hash algorithm which is not compatible with TLS 1.2.

Also on the TLS 1.2 RFC you can see that explicitly…

MD5
  MD5 [MD5] is a hashing function that converts an arbitrarily long
  data stream into a hash of fixed size (16 bytes).  Due to
  significant progress in cryptanalysis, at the time of publication
  of this document, MD5 no longer can be considered a 'secure'
  hashing function.

So, in order to fix this problem, the solution is to add the -a SHA256 parameter to makecert (make sure you are using makecert version 6.3.9600.17298 and not the old one 5.1313790.0 because that one does not support SHA256 as a parameter).

I'm writing all these since the whole conundrum took me 3 days to figure out and I hope some day someone is going to be benefited from this. If only Microsoft would have included in BIG BOLD letters that MD5 is no longer supported right in the article where the patch is described!

Thanks to @Mr.Brownstone and @sepupic for all their help!


SQL Server R2 and HyperThreading - Max Worker Threads and performance

I've seen failure to manage worker threads imperil more than one instance on various versions of SQL Server, at multiple businesses. The net result is memory pressure (which can manifest in different ways). In your case you can see clearly that a worker didn't yield, and what would be forcing it to yield is memory pressure. I haven't seen that result in a total FE like you've got, but again, memory pressure can manifest differently.

Set your workers to the appropriate level based on this MSDN article. Once you do, I would not be surprised to learn it solved your problem completely.

As for disabling hyperthreading, I don't think this is a good idea but opinions vary greatly on the topic. For sure I don't think it's related to your problem though.


I'm pretty sure you're leaving something out:

(n)varchar(max) fields CANNOT BE INDEXED

From MSDN:

  Columns that are of the large object (LOB) data types ntext, text,
  varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be
  specified as key columns for an index. Also, a view definition cannot
  include ntext, text, or image columns, even if they are not referenced
  in the CREATE INDEX statement.

Did you get an error when you tried to create the index?

Both your queries are the same because they are the same - since you can't index that field, they are both creating table scans.


How to select/count rows where the same value exists in a column but not in other column all at the same table

SELECT A.*
FROM default_relations_users A
LEFT JOIN default_relations_users B
ON  A.id_user_rq = B.id_user_ap
AND A.id_user_ap = B.id_user_rq
WHERE B.id_user_rq IS NULL;

I loaded you sample data (I added an additional index)

mysql> use Reynierpm
Database changed
mysql> DROP TABLE IF EXISTS `default_relations_users`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_relations_users` (
    ->   `id_user_rq` int(11) NOT NULL,
    ->   `id_user_ap` int(11) NOT NULL,
    ->   UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`),
    ->   UNIQUE KEY `rusers_ap_eq_idx` (`id_user_ap`,`id_user_rq`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
    -> (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from default_relations_users;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          1 |
|          2 |          2 |
|          2 |          3 |
+------------+------------+
6 rows in set (0.00 sec)

mysql>

Here is the result of my answer

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
|          2 |          3 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL
    -> AND A.id_user_rq = 1;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
+------------+------------+
2 rows in set (0.01 sec)

mysql>


The multi-part identifier "d.dept_name" could not be bound

First of all, after applying PIVOT, all columns of the pivoted set must be referenced using the alias specified for the PIVOT clause. As per the manual:

  When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.

So, if everything else was correct, it would need to be

SELECT pivotab.dept_name
from dept d
inner join emp e
on e.dept_ID=d.ID

Pivot
(
  count(e.ID)
  for d.dept_Name
  in([pROD],[elect],[inst],[fin],[otherdepartment])

  )
  as pivotab

Alternatively, you could omit the alias altogether since in this case you are not joining the pivoted set to other datasets and so all columns available would be only those coming from pivotab.

However, one other problem is that you are referencing a column that is no longer available after the pivot: your dept_name column is used to produce columns [pROD],[elect],[inst],[fin],[otherdepartment] and itself does not exist in the result set.

You could try selecting all columns (*) to see the output. I suspect that you will find that your output is not what you expect, because you likely have too many grouping columns coming from the result of the join between dept and emp. How you should resolve that may need to be asked as a separate question, though.


What is the difference between Com_xxx and handler_xxx?

Com_delete is number of times a user sent DELETE command to MySQL.

Handler_delete is number of times MySQL server sent delete handler to a storage engine via internal API

To illustrate the difference here's an example:

mysql> delete from t1 where id < 100;
Query OK, 88 rows affected (0.00 sec)

mysql> show status like 'Com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Handler_delete';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Handler_delete | 88    |
+----------------+-------+
1 row in set (0.00 sec)


SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?

There are various subtle differences.

Simple SQL functions can be inlined when nested in an outer query - provided they meet some preconditions. Not possible for a PL/pgSQL function.

All statements in the same SQL function are planned at once at call time, while PL/pgSQL function treat nested SQL DML statements much like prepared statements - only planning each when executed the first time, and possibly saving the query plan for the duration of a session. This is a major difference that influences behavior and performance.

For example, you cannot create a (temporary) table and use it later in the same SQL function, because it would have to exist at call time for the later statements to be planned. You need PL/pgSQL for that.

Code branches in PL/pgSQL functions that are not reached and thus never planned or executed may harbor undetected bugs until finally executed. PL/pgSQL function are only tested superficially at creation time.

Also, an SQL function only returns the output of the last query. The manual:

  When an SQL function is declared as returning SETOFsometype, the function's final query is executed to completion, and each row it outputs is returned as an element of the result set.

Bold emphasis mine. (You might use UNION (ALL) to combine multiple query results.)

RETURN QUERY, on the other hand, appends to the result and can be called multiple times. The manual:

  RETURN NEXT and RETURN QUERY do not actually return from the function
  — they simply append zero or more rows to the function's result set.
  Execution then continues with the next statement in the PL/pgSQL
  function. As successive RETURN NEXT or RETURN QUERY commands are
  executed, the result set is built up. A final RETURN, which should
  have no argument, causes control to exit the function (or you can just
  let control reach the end of the function).

Related:

FUNCTION syntax error

Passing arguments works pretty much the same for either PL. (There used to be more differences in old versions, like SQL functions couldn't refer to  parameter names. But that's history.)

There are other, more or less subtle differences. See:

Difference between language sql and language plpgsql in PostgreSQL functions
Function Performance
EXPLAIN ANALYZE shows no details for queries inside a plpgsql function
SQL injection in Postgres functions vs prepared queries


Which is the best tool to monitor replication on MySQL 5.5.20?

Checkout Percona toolkit. Look at pt-heartbeat, and some of their other tools as well.


Efficency of stored procedures vs raw queries

It is less so in SQL Server 2008 and higher, but it is still there.  What it comes down to is the execution plan cache and SQL Server being able to auto-parametrize queries which are sent in.  When using stored procedures (that don't have dynamic SQL within them) the queries are already parametrized so SQL Server doesn't need to generate a plan for each query when it's run as the plans are already stored in the plan cache.

And don't forget about the security issues (dynamic SQL, minimum permissions, etc.) that go away when using stored procedures.

When the app is using dynamic SQL against the base tables to select, insert, update and delete the data in the tables the application needs to have rights to all those objects directly.  So if someone uses SQL Injection to get onto the server they will have rights to query, change or delete all the data in those tables.

If you are using stored procedures they only have the rights to execute the stored procedures getting back just the information which the stored procedure would return.  Instead of issuing a quick delete statement and blowing everything away they would need to figure out what procedures can be used to delete data then figure out how to use the procedure to do so.

Given that SQL Injection is the easiest way to break into a database, this is kind of important.


I am trying to represent the value of gender field with 'male' if 1 and 'female' is 0

The problem is that you have two different return types, depending on the value of [GenderIsMale]; the two WHEN conditions return a string while the ELSE returns a BIT. It is easier to see if you format the code nicely:

Select case
    when CAST([GenderIsMale] AS bit)=1 then 'Male'   -- return type = VARCHAR
    when CAST([GenderIsMale] AS bit)=0 then 'Female' -- return type = VARCHAR
    else CAST([GenderIsMale] AS bit)                 -- return type = BIT
end

You can test this as follows:

DECLARE @GenderIsMale INT = 1;

Select case
    when CAST(@GenderIsMale AS bit)=1 then 'Male'
    when CAST(@GenderIsMale AS bit)=0 then 'Female'
    else CAST(@GenderIsMale AS bit)
end

Returns:

  Msg 245, Level 16, State 1, Line 28
  Conversion failed when converting the varchar value 'Male' to data type bit.

You can fix this by returning a string (or literal NULL) in the ELSE condition (pick one of those ELSE conditions, depending on what you are trying to accomplish):

SELECT CASE CAST([GenderIsMale] AS BIT)
    WHEN 1 THEN 'Male'
    WHEN 0 THEN 'Female'
    ELSE 'other string'
    --ELSE CONVERT(VARCHAR(50), [GenderIsMale]) -- if column is a numeric type
    --ELSE [GenderIsMale] -- if column is a string type
    --ELSE NULL
END;

Please note:

Depending on the datatype of the GenderIsMale column and the data in it, it is possible that there is an additional source of the error: the CAST of the value into BIT. For example, if the datatype is VARCHAR, then values of empty string, 'true', 'false', and strings of just digits 0 - 9 (no spaces, commas, periods, or minus sign) will convert, but anything else will error. Since you are using SQL Server 2012, you can switch to using TRY_CAST (or even TRY_CONVERT) to get around that.
The commented-out ELSE conditions are mainly to show the possible variations for getting the resulting expression to be consistent across all branches of the CASE statement. But in the scenario here of having a BIT / boolean value, if the datatype of the GenderIsMale column is a number, then the only value that could fall through to the ELSE is a NULL, hence you would use ELSE NULL. But if the datatype is a string, then you would use ELSE [GenderIsMale].


What is the best solution to implement to replicate a database, with a purging process is in place

If I understand you correctly the data is purged from the publisher database on a weekly basis - correct?
In this case I would set up replication to a subscriber and then transfer the data from the subscriber to a data warehouse, as Max Vernon suggested.

Hope this helps

Yomet


Answer to the original output:
This is not caused by autotrace. You will get the same error even without autotrace, just by repeating the same direct path insert. Issue a commit after the first insert.
This is a documented restriction: ORA-12700 to ORA-19400

ORA-12838: cannot read/modify an object after modifying it in parallel
Cause: Within the same transaction, an attempt was made to add read or
modification statements on a table after it had been modified in
parallel or with direct load. This is not permitted.
Action: Rewrite
the transaction, or break it up into two transactions: one containing
the initial modification and the second containing the parallel
modification operation.

Answer to the new output, after edit:
Ok, so you have a rollback, and you started a new transaction. In this case, autotrace really is the culprit, indirectly. The above rule still applies, and as the above description says, even a read reference will cause the above error. If autotrace is enabled, it implicitly runs something like this (after your original statement was finished):
EXPLAIN PLAN SET STATEMENT_ID='PLUSxxxxxx' FOR insert /*+ append */ into emp_big select * from emp_big_temp;
You can easily confirm this by enabling the errorstack trace for the error 12838 (it is fine in a sandbox environment for this short demo, but this is not how I would do this on a real database):
alter system set events '12838 trace name errorstack level 3';
If I run this now:
SQL> create table t1 as select * from dba_objects where 1=2;

Table created.

SQL> alter system set events '12838 trace name errorstack level 3';

System altered.

SQL> set autotrace on
SQL> insert /*+ append */ into t1 select * from dba_objects;

20079 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

I will find the error and a trace file in the alert log. The trace file contains this:
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-12838: cannot read/modify an object after modifying it in parallel
----- Current SQL Statement for this session (sql_id=5x12x8czsd3t9) -----
EXPLAIN PLAN SET STATEMENT_ID='PLUS730007' FOR insert /*+ append */ into t1 select * from dba_objects

And that is what causes the error, because it is still the same transaction. You can test it even without autotrace:
SQL> rollback;

Rollback complete.

SQL> set autotrace off
SQL> insert /*+ append */ into t1 select * from dba_objects;

20079 rows created.

SQL> explain plan for insert /*+ append */ into t1 select * from dba_objects;
explain plan for insert /*+ append */ into t1 select * from dba_objects
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Trying to restore SQL file from mysqldump, results in syntax error

I got 2 comments that hinted me that:

This problem might not be reproducible again if I do a second mysqldump.
The copy of the file from one place to another might have corrupted the file.

So then to ensure (1) was not the case, I tried to mysqldump again, and to make sure (2) didn't happen, I compiled the file before transferring it to another computer (so that if it gets corrupted, at least I would know when trying to decompress it).

The result was: success. It works now. So I suspect it was a glitch in the matrix.
Thanks for all the people that helped.


Ways to Upgrade Production MySQL db server from 5.5.42 to 5.7.13 with zero downtime

1) No. You have to restart MySQL and that means downtime.

2) To minimize the downtime you need an identical server which you can switch to and back. The procedure on high level:

you can build a passive master using percona-xtrabackup for example without downtime
Set up master-master replication
Upgrade the passive master to 5.6 and wait for it to catch up in replication
Switch active master to be the upgraded one (running 5.6)
Stop and rebuild the old master with the same process you did in step #1 but now from the new version (5.6)
Repeat the procedure to upgrade for 5.7
If you don't need it trash the passive master


Sql Server - Get database constraints of all tables ( default and check constraints)

Hope this script includes all you are trying to retrieve.

This script retrieving CHECK and DEFAULT constraints. You can use INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE to retrieve PK & FK columns.

    DECLARE @Constraint TABLE (
        [Database] [nvarchar](128) NULL,
        [Schema Name] [nvarchar](64) NULL,
        [Table Name] [sysname] NOT NULL,
        [Column Name] [nvarchar](128) NULL,
        [Data Type] [nvarchar](64) NULL,
        [Constraint Name] [sysname] NOT NULL,
        [Constraint Type] [varchar](7) NOT NULL,
        [definition] [varchar](512) NULL,
        [Create Date] [datetime] NULL
    )

    insert into @Constraint
    exec sp_MSforeachdb 'use ?
    select db_name() [Database],schema_name(t.schema_id) [Schema Name],t.name [Table Name],ac.name [Column Name],dt.name [Data Type],dc.name [Constraint Name],''Default'' [Constraint Type],dc.definition,dc.create_date [Create Date]
    from
                sys.tables t
    inner join  sys.all_columns ac
    on          t.object_id=ac.object_id
    inner join  sys.types dt
    on          ac.user_type_id=dt.user_type_id
    inner join  sys.default_constraints dc
    on          t.object_id=dc.parent_object_id and ac.column_id=dc.parent_column_id
    where t.type=''U''
    union all
    select db_name() [Database],schema_name(t.schema_id) [Schema Name],t.name [Table Name],ac.name [Column Name],dt.name [Data Type],cc.name [Constraint Name],''Default'' [Constraint Type],cc.definition,cc.create_date [Create Date]
    from
                sys.tables t
    inner join  sys.all_columns ac
    on          t.object_id=ac.object_id
    inner join  sys.types dt
    on          ac.user_type_id=dt.user_type_id
    inner join  sys.check_constraints cc
    on          t.object_id=cc.parent_object_id and ac.column_id=cc.parent_column_id
    where t.type=''U'''

    select * from @Constraint

Note: You can filter the required result by using where condition with select * from @Constraint.
Thanks!


enq: TX - row lock contention creating session waits

You are holding locks due to an UPDATE statemnt. Those locks are released when you commit your transaction.

To solve this issue, make sure you commit your transactions as soon as possible.


What do I need to sync a local site office SQL Server with the Head Office SQL Server

I ran into a similiar problem for an accounting package. Instead of trying to work out a way to move the SQL data around it proved to be much easier (and more cost effective) to provide a way to remotely access the application.

I opted for a TS 2008 box.  This includes a large number of international users with terrible bandwidth compared to the states.

EDIT - As syneticon-dj points out there are still potential issues associated with using TS on a satellite link. You would definitely need to look at the latency of your link for a TS solution to work. Some satellite links provide around 300 millisecond latency, others may be over a second.


Why did creating this new index improve performance so much when existing index included all columns in new index?

Order of columns in an index is important. If filtering requires column 1 and 4 from index, the index is not going to help. It's only useful when filtering by the first N consecutive columns.

This is because index is a tree. You can't efficiently select all nodes of the tree where column3 = something, because they are scattered all other the place, belonging to different values of column1 and column2. But if you know column1 and column2 as well, locating the right branch in the tree is a no brainer.


Collecting VLF information on all versions of SQL server

The first problem goes away if you solve the second with a Signed Stored Procedure installed in Master.  If there are any version differences required, you can have different versions of the procedure for different SQL Server versions.
Then the script is a just a call to the procedure.
exec sp_GatherVLFStats


Does osql.exe lock tables while exporting?

osql.exe ( and sqlcmd.exe ) are just programs to send T-SQL commands to the engine.  Whether or not your tables will be blocked during your export will depend on what exactly it is you're doing to export them.  If you're running SELECT statements into a .csv with transaction isolation at no higher than the READ COMMITTED default level, you should only be able to encounter blocking on tables that include BLOB data ( like VARCHAR( MAX ), for instance ).  Of course, if you're just spitting out to .csv you might want to look at using bcp.exe instead anyway.

There will be locks placed on the tables regardless of how you're exporting the data ( Schema locks at least ), but those locks won't be an issue and cause blocking if you don't overdo your isolation level.  If you're doing a metadata only export, the locks will be placed on the metadata tables instead of the actual tables themselves.


Need both a Readable Secondary and High Availability

@sp_BlitzErik is correct.  AGs need planning and care.  AGs don't have the particular issues you struggle with in Transactional Replication, but they have others.

If you are comfortable with Windows Server Failover Clusters (WSFC), then you have conquered the biggest hurdle to deploying and running a SQL Server Availability Group.  AGs use WSFC for the quorum model, and for a floating IP that moves between the nodes.

If you are willing to not have automatic failover, you can use the new "Clusterless" AGs in SQL Server 2017.  See Read-scale availability groups.  They are called "Read-scale" AGs to emphasize that they are not intended for HA, but they do support synchronous (zero data loss) replicas, and manual failover.

    For a small shop with no DBA, what solution(s) should we consider?

Also must mention that Azure SQL Database is ideal for a small shop with no DBA.  And enables you to create readable replicas in the standard tier, and premium tier databases get an automatic readable replica.


Does SQL Server / T-SQL support line-continuation to break up long strings?

Thankfully there is support for line-continuation in T-SQL via the \ (backslash) character. Just place that at the end of of a line, right before the newline / carriage-return + line-feed, and the newline will be ignored.

For text strings, this behaves as follows:

PRINT 'Line1\
Line2';

returns (in the "Messages" tab):

Line1Line2

For binary / hex strings, this behaves as follows:

PRINT 0x1234\
5678;

returns (in the "Messages" tab):

0x12345678;

In order to format binary files (Assemblies, Certificates) into text hex-byte strings for use in SQL scripts, I wrote a command-line utility called BinaryFormatter that I have released as open-source on GitHub. Not only does it convert binary file into a text representation, but it also uses line-continuation to spread long VARBINARY literals across as many rows are needed, based on the specified number of character to use per each row. The result is something along the lines of:

4D5A09DE34F178313345A4\
00007F4E39782EFC48D842\
00000000

which I then copy and paste into my script, as shown in the {...} area below:

CREATE ASSEMBLY [AssemblyName]
FROM 0x\
{output from BinaryFormatter}
;

For additional details on this topic, please see my blog post:
Line-Continuation in T-SQL


Why does SQL Server resume mirroring after server is restarted?

Is that normal?

This is the default behavior to automatically resume mirroring when the mirror comes up (provided no errors encountered). It will be in suspended state if there is a REDO error.

  I want to resume it manually after my verification...

If it resumes by itself then everything is good. You dont have to worry. If it does not then there is a problem and you will start troubleshooting.


MySQL lower_case_table_names - Tables vs Views

I guess this is correct behaviour, don't you think?

0 : Table names are stored as specified and comparisons are case sensitive.
1 : Table names are stored in lowercase on disk and comparisons are not case sensitive.
2 : Table names are stored as given but compared in lowercase.

Last line explains the case.
Referred: Dealing with MySQL case-sensitivity
Update: For views and case-sensitivity, refer this bug report.


This is from source code:

// If many clients are used, the per-thread caches become smaller and chances of
// rebalancing of free space during critical sections increases. In such situations,
// it is better to release memory when it is likely the thread will be blocked for
// a long time.

And when numOpenSessions is over 40 and threadCacheSizeBytes (GetThreadCacheSize()) goes under 0x10000, system frees memory.

So, to "prevent" this, you should have less (concurrent) open sessions OR more (cache) memory. You can define (wiredTiger) bigger (than default) cache size, with current memory. But, yes, it's better give more memory than define bigger cache size, because when mongodb have more memory, cache size is automatically bigger.


Why Clustered Index property is enabled on few columns and not on others?

Primary key and clustered index are two different things, though they are often used in conjunction. A couple of potential reasons your table ended up this way:

(a) it could have been created with a non-clustered primary key using PRIMARY KEY NONCLUSTERED, then the clustered index was added.

(b) the clustered index could have been added, then the primary key added (in which case it can't be clustered since there is already a clustered index).

Either of those could have been intentional design decisions, accidents, changes in afterthought, etc.

Ignore the property in Management Studio's design view. The clustered index is the table, but it is defined by the column(s) specified. The other columns are still technically part of the clustered index, they're just not key columns in the index. Calling that "enabled/disabled" is misleading.


how can we configure our DNS Alias to automatically resolve to the remote listener to hit the remote as primary with either CNAME or other DNS options

So you don't want to configure applications to connect to the AG Listener's Hostname?

If you use a CNAME pointing to the Listener hostname, it should always return both IP addresses, and clients will handle picking between the IP addresses.


Is there anything wrong with linking two identical primary keys in two different tables?

It looks like your model is a one-to-one relationship between OrderNumber and OrderInfo, with OrderInfo.OrderID as the OrderInfo primary key and as a foreign key referencing OrderNumber.OrderID.

The error messages suggests you have defined OrderInfo.OrderID as an IDENTITY column. It should not be IDENTITY - the assigned OrderID value from thre related OrderNumber row should be used when inserting the related OrderInfo row to maintain the foreign key relationship. That should allow you to specify DELETE CASCADE.


Cannot `create function` in plpython3u, permission denied

First don't make plpython3u into a trusted language.  This is bad.  You are giving access to all kinds of things on the database, the filesystem, and the like in ways that could be horribly abused.  Please reconsider.

Now from your comments you want to keep the admin permission separate from the normal user permission.  The way to do this is to make sure the superuser is NOINHERIT and the user is granted the role.  You can:

 CREATE ROLE mydba WITH SUPERUSER NOINHERIT;

 GRANT mydba TO myuser;

Then logged in as myuser then you can:

 SET ROLE mydba;

And you get superuser privileges.  You can use this to then to temporarily escalate your permissions in a way similar to sudo on *nix systems.


Difference between is_published and is_merge_published in Sql Server?

As per the comments, you are referring to the columns in sys.databases

As per SQL Server Docs

  is_published  bit

  1 = Database is a publication database in a
  transactional or snapshot replication topology

  0 = Is not a publication database

  is_merge_published    bit

  1 = Database is a publication database in a
  merge replication topology

  0 = Is not a publication database in a
  merge replication topology


MySQL multiple table joins and multiple data levels - best practice

See if this gives you the correct values:

SELECT t1.id_a,
        ( SELECT  sum(occ)
            from  t2
            WHERE  t2.id_a = t1.id_fh
            group by  t2.id_a
        ) AS sum_mc
    FROM xyz AS t1

Note: I am assuming that t1.id_a is unique, so that the GROUP BY is not needed.

(It is confusing to have id_a in both tables, yet not be JOINing on it.)


Access 2010 After Insert data macro to update another table

An "After Insert" data macro can certainly modify records in other tables. You can use the LookupRecord, EditRecord, and CreateRecord Data Blocks as required.

However, a data macro cannot run a VBA script. Your actions are limited to those available in the data macro environment itself.


I created an index approx 5gb in size but the file system doesn't show any difference in free space

I'm suspecting/assuming that some data file had a lot of free space
  inside it, and the new index simply occupied that already allocated
  space. Is this likely to be what has happened?

Based on your description of events yes this must have been the case.

The free space might have arisen either due to the initial size of the database files leaving more than 5GB free, or last time it needed to grow the increment was big enough to leave at least 5GB free, or space was reclaimed from deleted rows or dropped items in the data file or some combination of these.


Performance of a single table versus single table with jsonb versus multiple tables?

Options

Add another table to store all URLs with a foreign key back to the main table for each
Use a single table and match the URLs with a regular expression
Use a single table and store the URLs in a jsonb column as an array

Overview

Good idea. Totally ok.
If 2 means storing the URLs as a compound text field and processing with a regex, than that's utterly insane. Worst idea of the day insane. ;)
3 is also a very poor idea. Using JSONB to store an array is way overkill.

Conclusion

Store them in a separate table, or
Store them on the same table using a PostgreSQL Native Array Type

Querying

The 1nf-form method.

SELECT t1.*, t2.*
FROM t1
JOIN t2 USING (id)
WHERE t2.url = 'http://someting.example.com/foo';

This is how you query the table with an array type.

SELECT t1.*
FROM t1
WHERE urls @> ARRAY['http://someting.example.com/foo']

Performance implications

Separate-table (1nf) are that

adding URL's, and updating URLs are faster (you're writing less data).
joining slows things down a bit.

Performance implications for the column-array

Depending on the query, indexing can be more complex.
Adding or updating URLs requires writing the whole row.
Not having to join makes this potentially faster.
Arrays are less supported with ORMs and libraries.

Maintenance

  I need to match rows by the URL. But I'd also like to keep maintenance to a minimum.

Neither of these approaches is less-maintenance. However, for what it's worth, this is an ideal case for using an array. But make sure you normalize urls before you store them.


Why doesn't my trigger put data into my table?

What learning_DBAdmin is saying makes sense...
if this is the only script you are running, it will only create the trigger...
when you insert or update the table tAttackPlan, it will trigger the trAttack trigger.

Now let's analyze the trigger... If an insert or update is done on the tAttackPlan table the trigger will insert into tAttackPlan the attack date and domainID... Is this what you really want? It seems to be recursive to me but not sure of the whole story behind this piece of code. ... What I see is if your trigger is triggered it will insert into the tAttackPlan table which will trigger the trAttack again, and so on and so forth.

Also, note that a trigger has 2 internal tables inserted and deleted, which define the new value of the insert/update (in inserted) and the old value or records to delete (in deleted)...

Please check Microsoft sites to see how triggers work.


SQL Server and SQL anywhere compatbility

There's no reason why you should have a problem running SQL Anywhere and SQL Server on the same machine - so long as you have the appropriate resources to meet the needs of both. That will depend entirely on how busy the various databases are. We have many machines running both.

NB There is no connection between the code bases of SQL Anywhere and SQL Server. SQL Anywhere is a development of WATCOM SQL and although it was owned by Sybase and now SAP, it is an entirely separate product from Sybase (now SAP) ASE - Adaptive Server Enterprise, which did originally have a common origin with MS SQL Server.

If you run into any problems it might be worth posting on http://sqlanywhere-forum.sap.com/questions/ which is a specialist SQL Anywhere forum.


Calculate Running Sum of Each Row From Start, Even When Filtering Records

Just wrap it in a subselect.

SELECT *
FROM (
  SELECT id, date, amount, SUM(amount) OVER (ORDER BY date, id) AS balance
  FROM entries
  GROUP BY id, date, amount
)
WHERE date >= '2017-12-03'
ORDER BY date, id;

I'm also confused about a few things if you have three rows you don't have to group by it, see if this gives you the same result, it should be a lot faster.

SELECT *
FROM (
  SELECT id, date, amount, SUM(amount) OVER (ORDER BY date, id) AS balance
  FROM entries
)
WHERE date >= '2017-12-03'
ORDER BY date, id;


SHOW FULL PROCESSLIST for some host does not show `Client port`

The reason is that it's not connected via TCP/IP, so there isn't a port.

MySQL allows local clients to connect via a "socket file", which is located on the file system (/var/lib/mysql/mysql.sock, for example`).


Ensuring relationship between child tables exists prior to SQL insert

Recommended scheme:
Existing tables (PK == Primary key):
user (user_id PK, etc.)
assignment (assignment_id PK, etc.)
test (test_id PK, etc.)

Relations tables (PK == Primary key, FK == Foreign key):
user_assignment (user_assignment_id PK, user_id FK, assignment_id FK, etc.)

This table creates the relation "user to assignment" which would check to see if the user and assignment exist.
user_test (user_test_id PK, user_assignment_id FK, test_id FK, etc.)

This table creates the relation "user with existing assignment to test".


if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail?

If logshipping paths are same on the primary and secondary servers then adding an ndf will be seamless.

If the paths are different on primary and secondary servers then

Stop the backup, copy and restore jobs related to the database that you are adding a secondary file.
Add the ndf file on primary using ALTER DATABASE db_name ADD FILE..
run the backup job manually on the primary server. This will generate a T-log backup. Make sure that all the previous log backups are applied to the secondary.
Run the copy job manually so that the T-log with above changes is shipped to secondary server.
Manually restore the log file (from above step) using the

RESTORE log db_name FROM      Disk='path_T-log_from_Above_Step.trn'
with norecovery,  ---> Very Important .. !!
MOVE 'logical_file_name_of_ndf'
TO 'physical_path\secondary_file_name.ndf'

Once above steps are successful, enable all the backup, copy and restore jobs for the database. All the subsequent backup, copy and restore jobs should be successful now.


Datepart logic for custom sql agent job schedule

Here is my logic:

BEGIN
--FULL REFRESH Cycle
declare @EightDayofMonth datetime =(DATEADD(DAY, 8-DAY(GETDATE()), CONVERT(date, GETDATE())));
declare @actuladayofmonth datetime = CONVERT(date, getdate());

----checking for 8th day --condition 1 ( 8th of month and NOT Tuesday and not sunday) if any other day job executes
IF (@actuladayofmonth=@EightDayofMonth) AND (DATEPART(WEEKDAY,GETDATE())<>3 OR DATEPART(WEEKDAY,GETDATE())<>1)

BEGIN
Print 'disabe daily job'
print 'executing full job'
    END

--checking for monthly 8th is tuesday logic
ELSE IF (@actuladayofmonth=@EightDayofMonth) AND DATEPART(WEEKDAY,GETDATE())=3
BEGIN

Print 'Disable Tuesday Weekly'
print 'Disable Daily on tuesday'
Print 'Executing full job'
    END

ELSE IF (@actuladayofmonth=@EightDayofMonth) AND DATEPART(WEEKDAY,GETDATE())=1
BEGIN
Print 'Execute disable sunday'
 END
  END


What the error message suggests is a bit flipped in this pg_toast_16431418 relation on the exact place where the size is stored (the data size has the information that it's toasted in its upper bits). See TOAST in the 8.4 doc for details.

This does not suggest a problem with the page header, or with an index, or with the catalog.

BUG #5929 indicates it may be caused by a hardware problem, especially if the server doesn't use ECC memory.

You might want to try pageinspect to peek at the faulty data. If the damage is just a flipped bit, in theory it may be flipped back by writing directly at the data page as the last resort.

There are many steps involved:

do a file-level copy with the db shut down and work on the copy. Turn off autovacuum or anything that may touch the data.
find the main table corresponding to the pg_toast table. See Postgres pg_toast in autovacuum - which table?
Select each row of this table one by one until the failure occurs. Note the ctid of the row. It contains the offset of the page containing the row, and the offset of the row withing the page.
Dump the page with get_raw_page(relname text, blkno int) and examine the row. Use postgres doc to parse the data. You want to extract the pointer into the pg_toast_NNN relation.
Convert the pointer into a page (I don't know how to do that. I'd need to dive into the doc). Dump the corresponding page of pg_toast_NNN with get_raw_page() again.

It's no small feat if you don't have any prior knowledge of any of that stuff.
All the information is in the documentation, but in a compact form.


I have been going through this exercise for a large amount of servers. I am not going to explain why I am doing this due privacy reason.

I will give you high level steps and let me know if this is not enough.

Get binaries for the version you want to change to.
Start setup.ext
Chose maintenance-->Edition upgrade
Follow standard installation process
If you have more than one instnace on a single host choose the
intance you want to change key.
For me it normally takes about 5 minutes.
Will not ask for a recycle of SQL Server engine in most cases.Microsoft engineers recommend you do a recycle anyway.  You can stage the change before you schedule maintenance window to avoid an extra downtime.


Create DB2 on networked drive but managing from Windows

As Lennart says, you cannot access the data without the engine. You must install the engine on an OS somewhere. Can this be a central server? Most definitely. That is how the businesses I have worked for have set up there databases, regardless of whether they were DB2 LUW, DB2 for i, or SQL Server.

So you will want to pick the OS your DB2 installs on. IMHO, I'd stay away from Windows. That pushes you to Linux or AIX. My experience was with AIX and Windows. But I have heard good things about DB2 on Linux.

Anyway....you will need to install DB2 on a central server. Data Studio is great for access and/or some management. The DB2 Express-C will be great for you to play around with most things locally before moving them to the server. Keep in mind, not every feature is available on Express-C.


Tables with hierarchy: create a constraint to prevent circularity through foreign keys

I have seen 2 main ways of enforcing this:
1, the OLD way:
CREATE TABLE Foo
    (FooId BIGINT PRIMARY KEY,
     ParentFooId BIGINT,
     FooHierarchy VARCHAR(256),
     FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

The FooHierarchy column would contain a value like this:
"|1|27|425"

Where the numbers map to the FooId column.  You would then enforce that the Hierarchy column ends with "|id" and the rest of the string matches the FooHieratchy of the PARENT.
2, the NEW way:
SQL Server 2008 has a new datatype called the HierarchyID, that does all of this for you.
It operates on the same principal as the OLD way, but it is handled efficiently by SQL Server, and is suitable for use as a REPLACEMENT for your "ParentID" column.
CREATE TABLE Foo
    (FooId BIGINT PRIMARY KEY,
     FooHierarchy HIERARCHYID )


Error creating database on new storage in SQL Server 2008 R2 Failover Cluster

Operating system error 5 is a simple Access is denied you can always check those errors in the command prompt net helpmsg 5.

Now Volume mount points are sometimes a bother. In Windows server 2000 the calcs.exe had an error and could not set the permissions on the underlying drive and the only way to do it was to mount the volume using a drive letter, set permissions on the root folder and then create a mountpoint.

Later versions of windows should be able set the permissions on the mountpoint by using the /M switch so that

  cacls j:\data /g DOMAIN\ACCOUNT:f /M /E

Will grant the domain\account full permissions on the root of the drive mounted at j:\data, I still haven't leard to do this with icacls.exe and a comment how to do so will be well appriciated.

As you have found out creating a subfolder on the mount point makes it possible for you to create an accesslist for that folder but there still seem to be issues with setting permissions on the root folder of the drive using the GUI.

Usually I dont bother with the calcs command and if I need to set permissions on the root I'll add a driveletter, set the permissions using the GUI and then remove the drive letter.


Size each and every column appropriately.  Do NOT use a "standard" size for each column.  If you only need 30 characters, why create a column that can handle 255?  I'm so glad you're not advocating using varchar(max) for your string columns.

This is especially prudent advice if you ever need to index a column, or if you are using a column as a primary key and it has foreign key references.  SQL Server uses the size of each column in it's query optimizer to understand estimated memory requirements for query processing.  Having oversized columns can be detrimental to performance.

Indexes on columns that are oversize can result in errors being generated:

CREATE TABLE dbo.WideIndex
(
    col1 varchar(255) NOT NULL
    , col2 varchar(255) NOT NULL
    , col3 varchar(600) NOT NULL
);

CREATE INDEX IX_WideIndex_01
ON dbo.WideIndex (col1, col2, col3);

The attempt to create the index above results in this warning:

  Warning! The maximum key length is 900 bytes. The index 'IX_WideIndex_01' has maximum length of 1110 bytes. For some combination of large values, the insert/update operation will fail.

900 bytes is the maximum key size for clustered indexes (and non-clustered indexes on SQL Server 2012 and older).  1700 bytes is the maximum key size for non-clustered indexes on newer versions of SQL Server.  If you design columns with a generic width, such as (255), you may run into this warning far more often than expected.

In case you are interested in storage internals, you can use the following tiny test to better understand how SQL Server stores uncompressed row-store data.

First, we'll create a table where we can store columns of various sizes:

IF OBJECT_ID(N'dbo.varchartest', N'U') IS NOT NULL
DROP TABLE dbo.varchartest;
GO

CREATE TABLE dbo.varchartest
(
    varchar30 varchar(30) NOT NULL
    , varchar255 varchar(255) NOT NULL
    , varchar256 varchar(256) NOT NULL
);

Now we'll insert a single row:

INSERT INTO dbo.varchartest (varchar30, varchar255, varchar256)
VALUES (REPLICATE('1', 30), REPLICATE('2', 255), REPLICATE('3', 256));

This query uses the undocumented, and unsupported, functions sys.fn_RowDumpCracker and sys.fn_PhyslocCracker to show some interesting details about the table:

SELECT rdc.*
    , plc.*
FROM dbo.varchartest vct
CROSS APPLY  sys.fn_RowDumpCracker(%%rowdump%%) rdc
CROSS APPLY sys.fn_physlocCracker(%%physloc%%) plc

Output will look similar to this:

╔═════════════════════╦════════════╦═════════╦══════════╦══════════════════════════╦══════════╦═════════════╦═════════════╦═════════╦═════════╦═════════╗
║ partition_id        ║ colName    ║ IsInrow ║ IsSparse ║ IsRecordPrefixCompressed ║ IsSymbol ║ PrefixBytes ║ InRowLength ║ file_id ║ page_id ║ slot_id ║
╠═════════════════════╬════════════╬═════════╬══════════╬══════════════════════════╬══════════╬═════════════╬═════════════╬═════════╬═════════╬═════════╣
║ 1729382263096344576 ║ varchar30  ║ 1       ║ 0        ║ 0                        ║ 0        ║ 0           ║ 30          ║ 1       ║ 1912    ║ 0       ║
║ 1729382263096344576 ║ varchar255 ║ 1       ║ 0        ║ 0                        ║ 0        ║ 0           ║ 255         ║ 1       ║ 1912    ║ 0       ║
║ 1729382263096344576 ║ varchar256 ║ 1       ║ 0        ║ 0                        ║ 0        ║ 0           ║ 256         ║ 1       ║ 1912    ║ 0       ║
╚═════════════════════╩════════════╩═════════╩══════════╩══════════════════════════╩══════════╩═════════════╩═════════════╩═════════╩═════════╩═════════╝

As you can see, the InRowLength for each value is shown, along with the physical storage location of each row - the "file_id", "page_id", and "slot_id".

If we take the file_id and page_id values from the query results above and run DBCC PAGE with them, we can see the actual physical page contents:

DBCC TRACEON (3604); --send display to the client
DBCC PAGE (tempdb, 1, 1912, 3); --database, file_id, page_id, 3 to show page contents
DBCC TRACEOFF (3604);--reset display back to the error log

The results from my machine are:

PAGE: (1:1912)

BUFFER:

BUF @0x00000000FF5B2E80

bpage = 0x0000000024130000          bhash = 0x0000000000000000          bpageno = (1:1912)
bdbid = 2                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 32497                       bstat = 0x10b
blog = 0x212121cc                   bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000024130000

m_pageId = (1:1912)                 m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 98834 m_indexId (AllocUnitId.idInd) = 7936
Metadata: AllocUnitId = 2233785421652951040
Metadata: PartitionId = 1945555045333008384                              Metadata: IndexId = 0
Metadata: ObjectId = 34099162       m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 4                         m_slotCnt = 1                       m_freeCnt = 7538
m_freeData = 652                    m_reservedCnt = 0                   m_lsn = (35:210971:362)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x41 ALLOCATED  50_PCT_FULL
DIFF (1:6) = NOT CHANGED            ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 556

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 556
Memory Dump @0x000000005145A060

0000000000000000:   30000400 03000003 002d002c 012c0231 31313131  0........-.,.,.11111
0000000000000014:   31313131 31313131 31313131 31313131 31313131  11111111111111111111
0000000000000028:   31313131 31323232 32323232 32323232 32323232  11111222222222222222
000000000000003C:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000050:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000064:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000078:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
000000000000008C:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000A0:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000B4:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000C8:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000DC:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
00000000000000F0:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000104:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
0000000000000118:   32323232 32323232 32323232 32323232 32323232  22222222222222222222
000000000000012C:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000140:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000154:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000168:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
000000000000017C:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000190:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001A4:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001B8:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001CC:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001E0:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
00000000000001F4:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
0000000000000208:   33333333 33333333 33333333 33333333 33333333  33333333333333333333
000000000000021C:   33333333 33333333 33333333 33333333           3333333333333333

Slot 0 Column 1 Offset 0xf Length 30 Length (physical) 30

varchar30 = 111111111111111111111111111111

Slot 0 Column 2 Offset 0x2d Length 255 Length (physical) 255

varchar255 = 2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
222222222222222222222222222222222222222222

Slot 0 Column 3 Offset 0x12c Length 256 Length (physical) 256

varchar256 = 3333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
33333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
3333333333333333333333333333333333333333333


Sql Server Management Studio for 2008 Express?

If you are licensed to use the full Management Studio, I would install that. It has more features that will come in handy if you ever need to manage other SQL Server instances other than your local development/Express version. If you're not licensed or you know this is not a requirement, you can download Management Studio Express from here:

http://msdn.microsoft.com/en-us/library/ms365247.aspx

Yes, that's for 2008 R2; probably better to get the most recent release, which will work just fine with 2008.

Whether you end up using the full version or the Express version, can't hurt to install Service Pack 1 once SSMS is installed:

http://www.microsoft.com/download/en/details.aspx?id=26727

(Probably more appropriate for the full version, since there is a greater chance that issues are fixed since it includes more features.)


Creating Production -> staging flow, how to skip email addresses when doing mysqldump?

Native mysqldump doesn't have a feature to ignore columns. But you can do this by exporting that specific tables as CSV then import it to your staging.

Example

DB: bhuvi
Total tables: 3
No. of tables I need to ignore some columns: 1

testtable has some secret data.

select * from testtable;
+------+----------+-----------------+
| id   | name     | email           |
+------+----------+-----------------+
|    1 | sqladmin | sql@admin.com   |
|    2 | bhuvi    | bhuvi@gmail.com |
|    3 | dba      | dba@xyz.com     |
|    4 | stack    | ex@stack.com    |
|    5 | user     | user@test.com   |
+------+----------+-----------------+

View for showing dummy emails

create view secret as ( select id, name , 'test@test.com' as email from testtable);
select * from secret;
+------+----------+---------------+
| id   | name     | email         |
+------+----------+---------------+
|    1 | sqladmin | test@test.com |
|    2 | bhuvi    | test@test.com |
|    3 | dba      | test@test.com |
|    4 | stack    | test@test.com |
|    5 | user     | test@test.com |
+------+----------+---------------+

Export the data as CSV from shell

mysql -u root -p bhuvi -e "select * from secret" | sed 's/\t/"|"/g;s/^/"/;s/$/"/;s/\n//g' > testtable.csv

Dump all the tables with schema only

 mysqldump --no-data -u root -p bhuvi > bhuvi.sql

Dump the table which are import as it is

DBTODUMP=bhuvi
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('testtable')"
TBLIST=`mysql -uroot -ppass -AN -e"${SQL}"`
mysqldump -uroot -ppass ${DBTODUMP} ${TBLIST} > bhuvi_tables.sql

Restore the DB:

Restore schema:

mysql -u root -p stage < bhuvi.sql

Restore full tables:

mysql -u root -p stage < bhuvi_tables.sql

Restore CSV file:

 mysqlimport --ignore-lines=1 --fields-terminated-by="|"  --fields-enclosed-by='"'  --verbose --local -u root -p stage testtable.csv

Check the data:

mysql> select * from testtable;
+------+----------+---------------+
| id   | name     | email         |
+------+----------+---------------+
|    1 | sqladmin | test@test.com |
|    2 | bhuvi    | test@test.com |
|    3 | dba      | test@test.com |
|    4 | stack    | test@test.com |
|    5 | user     | test@test.com |
+------+----------+---------------+

Or simple method: Purge the email column after restore

udpate testtable set email='test@test.com'


PostgreSQL - How to create a function to validate a field is DateTime?

Try something like:

to_timestamp('21/12/2008 12:34:23','dd/mm/yyyy HH:MI:SS')

I assume you want to catch any exception and return null instead, so something like:

create or replace function my_to_timestamp(arg text)
returns timestamp language plpgsql
as $$
begin
    begin
        return to_timestamp(arg, 'dd/mm/yyyy HH:MI:SS');
    exception when others then
        return null;
    end;
end $$;

Example:

postgres=# select my_to_timestamp('21/12/2008 12:34:14');
   my_to_timestamp
---------------------
 2008-12-21 00:34:14
(1 row)

postgres=# select my_to_timestamp('21/12/2008 12:x:14');
 my_to_timestamp
-----------------

(1 row)

You can find all valid/invalid rows as:

with t(s) as ( values ('21/12/2008 12:34:14')
                    , ('21/12/2008 12:Tx:14')
)
select s from T
where my_to_timestamp(s) is [not] null;


Why is Oracle's dbms_metadata.get_ddl returning different results?

You can control whether or not the storage parameter is included by using DBMS_METADATA.SET_TRANSFORM_PARAM. It's possible that you changed this in one of your sessions.

To suppress the storage parameter, execute the following and then run the GET_DDL again:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

When you're finished, you can set it back to the default by running this:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');


how to get the marks in ‘Math’ of ‘English’ topper for each class in mysql for student result data

This question intrigued me - the requirement to have the mark for Mathematics for the class (regardless of role) which (who?) obtained the best mark in English.

I decided to go one better and obtain the results for all subjects for the best in class in English - fiddle available here.

The SQL:

SELECT t1.class, t1.roll_number,
       t1.sci_mark,
       t1.comp_mark,
       t1.math_mark,
       t1.eng_mark
FROM
(
  SELECT
    class,
    roll_number,
    SUM(CASE WHEN subjects = 'math'
      THEN marks ELSE 0 END) AS math_mark,
    SUM(CASE WHEN subjects = 'english'
      THEN marks ELSE 0 END) AS eng_mark,
    SUM(CASE WHEN subjects = 'science'
      THEN marks ELSE 0 END) AS sci_mark,
    SUM(CASE WHEN subjects = 'computer'
      THEN marks ELSE 0 END) AS comp_mark
  FROM resultdata
  GROUP BY class, roll_number
) AS t1
JOIN
(
  SELECT class, MAX(marks) AS maxmark
  FROM resultdata
  WHERE subjects = 'english'
  GROUP BY class
) AS t2
ON
  t1.class = t2.class AND
  t1.eng_mark = t2.maxmark
ORDER BY class, eng_mark DESC;

Result:

class   roll_number sci_mark    comp_mark   math_mark   eng_mark
    8             2       87           99          78         89
    9             1       85           85          90         85
   10             2       65           85          50         95

A glance at the other solution here shows that it matches on the Mathematics score for the highest English mark. Inspection shows that it works for the two other subjects (Science and Computers) also. As pointed out here MySQL does not have a PIVOT function, but I got this technique from that site.


There are ways to do it which involve attaching a debugger to the SQL Server and basically crashing the SQL Server so that you can get into the memory that SQL Server is using which will have the plain text password in memory.

It'll be much easier to get the password from the .NET source code.


Set non-negative floor for rolling sum, in PostgreSQL

This is how I solved a similar problem on Teradata using nested OLAP-functions:

SELECT dt.*,
   -- find the lowest previous CumSum < 0
   -- and adjust the current CumSum to zero
   Max(CASE WHEN CumSum < 0 THEN -CumSum ELSE 0 end)
       Over (PARTITION BY groupid
             ORDER BY pkid
             ROWS Unbounded Preceding)
   + CumSum AS AdjustedSum
FROM
 (
   SELECT pkid, numvalue, groupid,
      -- calculate a standard cumulative sum
      Sum(numvalue)
      Over (PARTITION BY groupid
            ORDER BY pkid
            ROWS Unbounded Preceding) AS CumSum
   FROM foo
 ) AS dt


You deleted the *.ibd files, but entries in the InnoDB dictionary remain.

Create empty temporary database. Let it be tmp1234
Move all tables from the original database to tmp1234
Drop the original database (it’s empty by now, all tables are in
tmp1234)
Create the original database again
Move all tables from the temporary database to the original one.
Drop the empty temporary database.

See more details on a similar case https://twindb.com/resolving-error-1050-42s01-at-line-1-table-sakilasql-ib712-already-exists/


SSD used for tablespace died. How can I recover data?

In my hands, just creating an empty tablespace directory allows recovery to proceed and the database to open.  Either change your mounts so that the existing path of the tablespace now maps to a valid directory again, or delete and re-create the symlink  "PGDATA/pg_tblspc/16394" so that it points to a valid empty directory on some other mountpoint.

I think that this should much safer than monkeying around with pg_resetwal.


Postgres query slow despite index being used

with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
      from lead
      left join last_activity_with_client last_activity_join
                 on  lead.client_id = last_activity_join.client_id
                 and lead.id = last_activity_join.lead_id
      left join lead_last_response_time lead_last_response_time
                 on lead.id = lead_last_response_time.lead_id
    where lead.insert_date >= to_char(current_date-30,'YYYYMMDD')::integer
      and lead.client_id = 13189),
     TempCount AS (
         select COUNT(*) as total_rows
         from TempResult
     )
select *
from TempResult, TempCount
order by last_response_time desc NULLS LAST
limit 25 offset 1;

Possibly "TempCount" can be dropped and replaced by a window function "count() over ()".
with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
      from lead
      left join last_activity_with_client last_activity_join
                 on  lead.client_id = last_activity_join.client_id
                 and lead.id = last_activity_join.lead_id
      left join lead_last_response_time lead_last_response_time
                 on lead.id = lead_last_response_time.lead_id
    where lead.insert_date >= to_char(current_date-30,'YYYYMMDD')::integer
      and lead.client_id = 13189)
select *, count(*) over () TempCount
from TempResult
order by last_response_time desc NULLS LAST
limit 25 offset 1;


Another way to accomplish this without the creating database objects is to use the below query taken from the sp_spaceused stored procedure itself:

declare @dbsize bigint
declare @logsize bigint

select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles

select ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + ' MB') as Database_Size


Mongo initial sync failing with InvalidSyncSource

This looks like known bug what is gonna be fixed in version 3.4.5 what hopefully comes out before end of this month.


Seems like it was some long-running transactions involving those views, which when rolling back somehow reverted back their names to the name used when creating them, i.e. the ones ending in ViewView (which was a typo in the create statement) then renamed to the correct name while some transactions were already started, and possibly renamed back on rollback.


In SQL Server Management Studio, rt-click on your instance and select Properties. Then select Processors. Uncheck "Automatically set..." and set CPU affinity how you would like.


How to order databases by size? (For use in full backups)

I also order my databases by size, and use sys.master_files

This query would show your largest databases first, get rid of the desc and you will see the smallest first.

select
DatabaseName,
sizeGB=REPLACE(CONVERT(VARCHAR(20),CAST( SUM(radhe.sizeGB) AS MONEY),1), '.00','')
from
(
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size*8)/1024.0/1024.0 SizeGB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
) radhe
GROUP BY DATABASENAME
order by SUM(radhe.sizeGB) desc

this query shows all database files and order them by size starting by the largest ones:

select
DatabaseName,
Logical_Name,
Physical_Name,
sizeGB=REPLACE(CONVERT(VARCHAR(20),CAST(radhe.sizeGB AS MONEY),1), '.00','')
from
(
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size*8)/1024.0/1024.0 SizeGB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
) radhe
order by radhe.sizeGB desc

You could also use the following query that uses sys.dm_os_volume_stats:

SELECT * FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

You could instead use the backup files to calculate which database restore first,
as per these questions below, but you would have to adapt your logic to them:

Having the backup file, how to work out the total disk space required to do the restore?

T-SQL query for date of last full backup, size and location

For further considerations in your restores, please have a look at this beautiful answer:

Running out of disk space mid-restore


PostgreSQL GiST for compressed entry type

Since you already store the values (rmin, rmax, gmin, gmax, bmin, bmax) for the image column, a btree index on those covers equality checks just fine:

CREATE INDEX foo1 ON icons (rmin, rmax, gmin, gmax, bmin, bmax);

This query will use the index:

SELECT COUNT(*)
FROM   avatars a
JOIN   icons   i
WHERE  a.id = 123
AND   (a.rmin, a.rmax, a.gmin, a.gmax, a.bmin, a.bmax)
    = (i.rmin, i.rmax, i.gmin, i.gmax, i.bmin, i.bmax);

Of course, you need another index on avatars.id, or possibly on (id, rmin, rmax, gmin, gmax, bmin, bmax) to allow index-only scans.


Is it always faster to create indexes after loading data?

Back in the day we would bulk load our data in this way:

Drop indexes
Load data in the order for which the clustered index would be built (i.e., you export the data in a precise way)
After the load is completed, create the clustered index
Next, create any additional non-clustered indexes
Miller time (this was before I could afford decent beer)

That method always proved faster than leaving the indexes in place. However, this was for Sybase and SQL Server. I imagine other systems would be similar, but I can't say for certain.


Advice on scripting a "whitewashed" and downsized development db from main db

What you're referring to is usually called Test Data Management. There are several commercial solutions, e.g. IBM Optim Test Data Management (disclosure: I work for IBM, but don't speak for them). I'm not aware of any free products that can perform that function, so I'm afraid you'll have to stick to your Python scripts unless you have deep pockets.

On a side note, I would suggest using the same database platform, i.e. PostgreSQL, for both production and development -- it will probably save you some troubleshooting time later.


Slow query while selecting a higher offset from millions of rows

You're seeing the intrinsic drawback of OFFSET pagination. Using LIMIT, you force your DB to sort all the records and count them all the way up to your OFFSET base.

Keyset pagination may correspond more closely to your actual needs here and will yield much better performance. As described in the above linked article, simply paginate based on the last key you've seen:

SELECT *
FROM securityData
WHERE id > ?last_seen_id
ORDER BY log_time
LIMIT 50;

This allows your DB to leverage the index over id to directly jump to the first relevant row instead of going through all 5000753 rows preceeding it.


Multiple filegroups vs. multiple files in a single filegroup

Both multiple files in a filegroup and multiple filegroups are tools for managing your database. The first lets you manage your IO and both will let you manage your backups.

It is possible to backup a single file of a database as well as a single filegroup. Be sure to backup the tail of the transaction log when you do if you are planning on restoring it somewhere.

Database files allow your multi-core CPUs to have multiple read/write streams to the database without hitting higher disk queuing values.

It may help to think of the filegroup as a logical division and the file as a physical division. If you have multiple filegroups you will automatically have multiple files as a file can only belong to one filegroup. It can also help (if you have enough cores on your server) to have multiple files in your filegroups. That can give you the best of both worlds. You assign database objects to a filegroup not a file. You can put the files on different physical disk arrays. When I first started doing database work it was common knowledge that you put your data and log on separate disks. If you had the budget you would put your non-clustered indexes on another disk. It's tough to get that these days with SAN technology everywhere. However, SAN is a management tool not a performance tool.

As you pointed out having different filegroups will allow you to isolate high traffic tables from each other and from lower traffic tables. It will also allow you a limited additional protection from a corrupted database potentially limiting data corruption to a smaller part of the data.


What you want is only possible with filesystem-level backup. Index entries ultimately contain references to tuple IDs (TID) of table rows, which are essentially physical pointers to records within database files. Every row update will move it to a different location with a different TID, making the old TID useless unless updated concurrently.

A table restored from a dump will have (possibly all) row TIDs different from the original; if you somehow managed to duplicate the original index and use it to access this new table, results would be undefined.

Only a filesystem-level backup of the index files together with the corresponding table file(s) can guarantee that TIDs remain unchanged.


Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.)

On the flip side read committed snapshot will give only the latest committed version of data

Incorrect. Snapshot will give you the data that was committed at the moment the snapshot was taken. This moment means either when you issued BEGIN TRAN if you use true SNAPSHOT isolation level, or the moment your statement started if you use read_committed_snapshot RCSI. That means that if multiple updates commit, on the same record, you will not see the latest, but instead the one that was committed when your 'took the snapshot'. Think of it a a photo: you take a picture of the data and from then on you can only see the picture, no matter how the 'real' scenery changes behind.

Snapshot is absolutely robust. If your DBA has objections, then he/she should be able to articulate them so that they can be addresses or dismissed, as is the case. Everything has trade offs and gotchas. Indexes have trade offs and gotchas, does that mean your DBA does not use indexes?


How to recover auto committed transaction in Oracle with normal privilage

You cannot recover the committed transaction with a normal privilege.
However, you may be able to
ask your DBA to flashback(If the database is set up with flashback enabled for tables and UNDO_RETENTION has set approximately)the table to a specific point in time, or,assumin they are using hot backups, Database Point-In Time Recovery(This option will also rollback changes made by others on the database) could be an alternative for flashback.

For details:Rewinding a Table with Flashback Table


my ssh is ok but masterha_check_ssh is fail

I just encountered the above error and discovered where I got it wrong.

The content of id_rsa.pub file on slave3 (which is your manager node) also but be copied over to your authorized_keys file to enable passwordless ssh into slave3. Once I copied public ssh key (id_rsa.pub) into authorized_key, the error was gone. To confirm this, you should run ssh root@192.168.233.134 on slave3 to confirm you are able to ssh into slave3 as root without a password.


How to retrieve a view column derivation in PostgreSQL or Greenplum

From the docs

  Views in PostgreSQL are implemented using the rule system.

So essentially, they get rewritten to simply SELECT statemetns. Nothing in the catalog stores the column expression. Internally, when you run \d on a view, it calls pg_catalog.pg_get_viewdef. You can't go further from that with the system catalog. You're going to have to parse that query to get to the column expression, perhaps with something like libpg_query.


Empty Table: Insert into Table WITH (TABLOCK)

Like many things database-related this option solves some problems but can cause others.

By using TABLOCK you will avoid lock escalation. By using TABLOCKX you may even avoid lock conversion. The downside is that other activity against that table must wait for this lock to be released. You say there is no concurrent activity, so that shouldn't be an actual problem in real life. Taking locks is quite cheap and fast, so I don't really think that would be a problem either.

So my answer is - what problem are you trying to solve? Will the work of changing 100+ stored procedures be repaid by the improvement you see?


INSTALL PLUGIN server_audit SONAME 'server_audit.so';

According to the documentation, the SONAME should include the extension - '.so' in this case.


Detailed optimal column order with large text and bytea

Does that mean that columns should be ordered from most space occupation to least?

No, not necessarily. You can play "column tetris" to minimize padding and thereby save some space. The rule of thumb I gave and you quoted is one simple strategy for basic types that require alignment.

As I mentioned in the quoted answer, you can test the actual storage size (excluding item identifier) with pg_column_size() on the whole row.

text and related varchar and char types do not require padding, so there is nothing to gain. The same is true for your bytea columns.

Concerning storage size for:

  bytea columns that always have constant 16-byte, 32-byte, or 64-byte lengths

The manual page on bytea tells us :

Storage Size
1 or 4 bytes plus the actual binary string

That means, the actual space required for a bytea column of 16-byte, 32-byte, or 64-byte
length is 17 or 20 byte, 33 or 36 byte etc. respectively.

As demonstrated in this SQL Fiddle, a bytea variable always has an overhead of 4 bytes. When stored in a column, however, it starts out with just 1 byte of overhead and switches to 4 bytes for values of 127 bytes length or more.
24 bytes of overhead are added for the row type.
Another 4 bytes are needed for the item identifier per tuple in the data page. Details in this related answer:

Configuring PostgreSQL for read performance

As for alignment requirements of bytea, per documentation:

  Values with single-byte headers aren't aligned on any particular boundary, either.

I would suggest you read that whole chapter - probably a couple of times, it's a tough read.


How to group by continuous days in mysql

SELECT from_id,to_id,
  CONCAT_WS(' - ',
    MIN(dt),
    CASE WHEN MAX(dt)>MIN(dt) THEN MAX(dt) END
  ) As intervals
FROM (
  SELECT from_id,to_id,
    CASE WHEN dt=@last_ci+INTERVAL 1 DAY THEN @n ELSE @n:=@n+1 END AS g,
    @last_ci := dt As dt
  FROM
    t, (SELECT @n:=0) r
  ORDER BY
    dt
) s
GROUP BY
  g

FIDDLE


PostgreSQL - triggers to fire only on replicated db

You can do that with logical replication, but not with streaming (physical) replication, because in the latter case the standby server is read-only.

For logical replication, you have to define the trigger as replica trigger:

CREATE TRIGGER tgname ... ON TABLE atable ...;
ALTER atable ENABLE REPLICA TRIGGER tgname;


SQL Server Analysis Services - who uses them?

Analysis Services is a very large and widely used part of the MSSQL stack, your question is entirely too broad to give an accurate answer. I will tell you that a typical SSAS project involves more than just OLAP; you have to shape your data first. A typical scenario starts with one or more sources of data which have disparate structures. An ETL process (often times performed using a tool like Integration Services or SSIS)  is used to gather that data, perform operations such as cleansing and ultimately drops that data into a data warehouse/mart which is a central database typically designed using a star or snowflake schema. (this is a world unto itself with lots of debate but think of a more flattened data design). From this warehouse you use SSAS to design cubes which are built from the data in said warehouse and once they are complete you browse those cubes with everything from Excel up to an including highly specialized reporting environments.

Again, your question lacks specificity so it is hard to explain things beyond that without writing a small book.


Fetching rows from million rows table(Optimization)

To do anything takes a finite amount of time.
To do the same thing 45K times, takes [at least] 45K times as long.

This is a classic 1+n query.
Retrieve a list of ids, then process each in turn with another query.

It runs like a slug.
It's completely unscaleable.
It's complete untunable.  Indeed, your database will be thoroughly enjoying itself, being asked to retrieve a single record based on single value that it can find in a unique index!

Rewrite your code to retrieve the whole Set of data, then loop through the rows within it to process it or, in this case, simply select the data you want, for all the rows you want, in a single query.  I assume you used another query to retrieve the values in your "list": use the "WHERE" clause from that query to retrieve [all] the data you need:

(PseudoCode)

resultlist =
   SELECT column_1, ... column_n
   from TABLE
   WHERE condition-you-originally-used-to-get-the-list-of-ids
   ORDER BY column_1

No; it's not very Object Oriented.

It's Set Based and, for Relational Databases, there's nothing better.


The only sensible solution is to normalize your model and get rid of those dreaded comma separated values. Then you can use a proper foreign key.

Something like:

create table users
(
    user_id   serial not null primary key,
    user_name text not null
);

create table groups
(
    group_id   serial not null primary key,
    group_name text not null
);

create table members
(
    user_id  integer not null,
    group_id integer not null,
    primary key (user_id, group_id),
    foreign key (user_id) references users (user_id),
    foreign key (group_id) references groups (group_id)
);

If for some reason you would like to have the comma separated lists (for display convenience) you can always create a view that returns this:

create view v_members
as
select g.group_id,
       g.group_name,
       string_agg(u.user_name) as group_members
from members m
  join users u on u.user_id = m.user_id
  join groups g on g.group_id = m.group_id
group by g.group_id, g.group_name
;


Should I add an arbitrary length limit to VARCHAR columns?

The answer is no.
Related advice in the Postgres Wiki.
Don't add a length modifier to varchar if you don't need it. (Most of the time, you don't.) Just use text for all character data. Make that varchar (standard SQL type) without length modifier if you need to stay compatible with RDBMS which don't have text as generic character string type.
Performance is almost the same, text is a bit faster in rare situations, and you save the cycles for the check on the length. Related:

Any downsides of using data type “text” for storing strings?

If you actually need to enforce a maximum length, varchar(n) is a valid choice, but I would still consider text with a CHECK constraint like:
ALTER TABLE tbl ADD CONSTRAINT tbl_col_len CHECK (length(col) < 51);

You can modify or drop such a constraint at any time without having to mess with the table definition and depending objects (views, functions, foreign keys, ...). And you can enforce other requirements in the (same) constraint.
Length modifiers used to cause problems like this or this or this ...
PostgreSQL 9.1 introduced a new feature to alleviate the pain somewhat. The release notes:

Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in
appropriate cases (Noah Misch, Robert Haas)
For example, converting a varchar column to text no longer requires a
rewrite of the table. However, increasing the length constraint on a
varchar column still requires a table rewrite.

More issues with varchar(n) have been fixed in later releases.