Simple way to Drop Test databases and restore from Prod databases on 2008r2 I have a production database instance on a production server and a test database instance on a test server. I need to restore the production databases to the test instance daily.

What I need:

LOOP through Prod DB
If DB name exists in Test Drop the test database
Restore DB from Prod
Next in LOOP

SQLServers Copy Databsase Wizard seemed to do what I wanted but it errors on custom database roles, apparently an issue with 2008r2.

Apex Backup is also getting an error.

Backups are made of the production databases daily so I could also just drop the test databases and restore from those BAK files.

Is there an easy way to do what I need to do?

PS: I'm not a DBA, I'm a dev who has to do DBA stuff, so forgive me my ignorance.

As a recommendation, I would suggest switching to Ola Hallengren maintenance solution for the backups and some other administrative tasks, is really easy to configure. Run the main script Maintenance Solution, schedule required jobs and off you go. I hope that backups are copied to a different server than production server.

Then, schedule restore jobs from the test server to restore those daily backups. The job would have minimum 2 steps, drop database and restore it. Maybe you will want to add some cleaning step to clean up or mask some critical data that shouldn't be available for testing, replacing emails to avoid sending emails to clients and stuff like that. Maybe even deleting some data from bigger tables to have smaller databases. Heck, maybe for some cases is enough to just have the skeleton of the databases.

If there are few databases, you can handle it manually. If not, you could make use of dbatools as commented, is a set of powershell scripts really helpful for administrative tasks. Or you can search online, there are plenty of options available, 3rd party tools, scripts that you will need to tune for your needs, etc.


SQL Server: cold backup, shutting down services? One of our collagues told me that they are doing "cold" backups for SQL Server so that they are shutting down services and then taking a backup somehow.  I think this is all wrong! Am I right? I think that they should take full bakcups, differential backups and log backups, while their databases are on FULL mode.

They are also having a problem when they start SQL Server services after a backup, they cannot find .mdf files. Do you have any idea why this is happening to them?

Never heard such rubbish.

FULL or BULK LOGGED mode means you require log backups. SIMPLE means you don't.
You always require full backups.

Copying the MDF and LDF files isn't a backup and can potentially lose** you data if you are in a transaction as you shut SQL Server down.

If the mdf files are missing it means they aren't there because of finger trouble or this "backup" method. You can't lose mdfs unless someone removes the files or removes a drive when SQL Server is shut down

** Edit:

By losing data I mean "open transactions won't be committed". Typically, client code won't handle a retry and, say, log the error but not the data to be written.

Any data in committed transactions, no matter how old or new, will be safe


How can I enable my-huge file on windows server? I installed Windows Server 2008 and MySQL 5.5.
Now I need configure MySQL for huge InnoDB tables and a lot of connections.

I see that on my Windows Server, there is a my-huge file(configs files that come with mysql :my, my-huge,my-large .ini) with configuration inside the mysql directory.

How can the my-huge configuration be enabled?

You need to stop mysqld, swap in my-huge.ini, and start up mysqld

cd C:\"Program Files\MySQL\MySQL 5.5"
net stop mysql
rename my.ini my.oldini
copy my-huge.ini my.ini
net start mysql

This will only work if you do not have to resize the log files.

If you have to resize ib_logfle0 and ib_logfile1, rename them before startup

cd C:\"Program Files\MySQL\MySQL 5.5"
net stop mysql
rename my.ini my.oldini
copy my-huge.ini my.ini
cd data
rename ib_logfile0 ib_logfile0.bak
rename ib_logfile1 ib_logfile1.bak
net start mysql

Give it a Try !!!


Do you prefer to store confirm password property in database? I am designing a database and it has came to my mind to check entered password (for register page) with confirmed password in database as well as in programming layer.
Do you recommend it?
Any comments would be appreciated.

Maybe I misunderstood your question, but let's consider a signup form. It is customary to ask for password confirmation, then when the user submits the form, the backend checks that both password fields do match. If there was some issue like incomplete data, you show a message back to the user.
Otherwise, if no other errors were found, you proceed to save the record to the DB - the password of course should be hashed and not stored in plain text.
But until the form is submitted without errors, you save nothing to the DB. At least I don't see why you would.
You can save session state though, that's what cookies are for.
To sum up, only one field is need for the password (in hashed form). The password confirmation field is simply to minimize the risk of incorrect input, but it's no longer useful after signup has completed.


IOPS needs of Root Volume vs Database/Log Files Volume I'm setting up a database server via AWS EC2, using:

Root EBS Volume, General-purpose SSD
EBS Volume, Provisioned IOPS

And intending to configure PostgreSQL to have the database and log files on the PIOPS volume.

I'm wondering how much IOPS strain that the root volume will have, since the base install of the DB engine and instance will be there, hence still handling the flow of data for the networking and database logic.

For example, if I have 2000 IOPS on the DB volume and 3 IOPS on the root volume, that database server would certainly be bottlenecked by the root IOPS. What does a good ratio look like?

A 'gp2' EBS volume will have plenty of performance for use as a root volume. There won't be any problems.

See details on EBS gp2 volumes on Amazon's documentation.

  General Purpose (SSD) volumes [... have ...] the ability to burst to 3,000 IOPS for extended periods of time, and a baseline performance of 3 IOPS/GiB up to a maximum of 10,000 IOPS (at 3,334 GiB).

...

  Volumes earn I/O credits every second at a baseline performance rate of 3 IOPS per GiB of volume size. For example, a 100 GiB General Purpose (SSD) volume has a baseline performance of 300 IOPS.

You'll be fine. Your root volume shoudn't be doing tons of I/O anyway. Frequently used data like program binaries will be cached in the kernel buffer caches in instance RAM. You're mainly looking at write activity for log files, etc, and it's unlikely that'll be a problem.

Do make sure, if you log via syslog, that flushes are not forced after each log write though. That usually involves prefixing the log output line with a -.


How to combine multiple EXEC statements into a single SELECT? I run this every month to extract database logs, and I have to change the dates as well.
DECLARE @start DATETIME
SET @start = CONVERT(DATETIME, '2020-08-01 00:00');

DECLARE @end DATETIME
SET @end   = CONVERT(DATETIME, '2020-08-31 23:59');

DECLARE @searchString1 NVARCHAR(256) = 'BACKUP';
DECLARE @searchString2 NVARCHAR(256) = '';

EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 2, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 3, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 4, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 5, 1, @searchString1, @searchString2, @start, @end;

However, I get multiple results that I have to manually sift through and combine
How can I do something like this:
SELECT * FROM (EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end)
UNION ALL
SELECT * FROM (EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end)
UNION ALL
etc

Obviously, this does not work. I get

Incorrect syntax near the keyword 'EXEC'.

Is there a better way?

I don't think you can combine several logs in one EXEC, so you are most likely stuck with several executions. As suggested nu Mo64, you can put it all in a temp table and then query that temp table. I have a utility that does exactly that, here.


Should we consider "sqlplus" as an Oracle client? I am a newbie of SQL*Plus and Oracle, and I have 2 questions about them.

(1) Executing "sqlplus / as sysdba" command:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 18 05:09:38 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Does the sqlplus connect an Oracle Instance now?

(2) When use sqlplus connect the local Oracle Instance, like this:

$ sqlplus username/password

Should we also consider sqlplus as a client? If it is true, does the sqlplus also read tnsnames.ora file? And the requests also forward to listener process?

SQL*Plus is a client that is already available for a very long time. If you use sqlplus username/password then it does not use the tnsnames.ora. It connects to the local database that is defined in the ORACLE_SID environment variable. If you use sqlplus username/password@SID then it will search in the tnsnames.ora file to find out the location of the SID database.


Table design - one table or hundreds of small ones I have the following problem:

I have measuring devices, each one measure always the same quantities from the list of five different ones. Each device can measure different quantities from this list. I update the data only sometimes (once a month), but read them online based on time frames (no caching on app side).
Every device has aproximatelly 20K entries up to date.
New device will be added only in special cases, so devices can be seen as fixed.

My question is:

Should I have one big table or multiple (hunderds) of small ones.

Advantages / Disadvantages I see:

Single table

-) waste of space, since some columns are not filled, because device not measure certain quantity

+) safer select - device ID is not part of table name and can be passed safely with PDO or other library to prevent injections

+) easier to select multiple devices at once (however, this is not an usuall user case)

Multiple tables

+) "space" friendly

-) more complicated select - table name must be checked manually (from schema table) before select to prevent injections

Another solution is one table per quantity.

+-----------+------------+-----------+
| device ID | date       | quantity1 |
+-----------+------------+-----------+
|         A | 2020/06/03 |        10 |
+-----------+------------+-----------+

+-----------+------------+-----------+
| device ID | date       | quantity2 |
+-----------+------------+-----------+
|         B | 2020/06/04 |        50 |
+-----------+------------+-----------+

+-----------+------------+-----------+
| device ID | date       | quantity3 |
+-----------+------------+-----------+
|         A | 2020/06/03 |       100 |
+-----------+------------+-----------+
|         B | 2020/06/04 |       100 |
+-----------+------------+-----------+

The wide table with NULLs can be re-created by joining these tables. Joseph's tall table can be created by unioning them.

In terms of storage, NULLs are quite efficiently represented. They need not take up the full byte count of the column's declared data type. Don't be afraid of storing NULLs.

If space overhead is a concern then likely the one-column-per-quantity will have the best key-to-data ratio, followed by table-per-quantity then the single table.

If you're using compressing or columnar storage this can be more disk-efficient, too, but depends on the storage engine. Adjust the table design and data sorting to best suit the engine.

My suggestion is that you think about the use you will make of the data and consider how queries will be written for each design. For a given device & date do you need several quantities or one only? Each design can support all queries, just that some designs make the queries simpler to write and faster to run.

Whatever the table design additional consideration must be given for reporting on devices that recorded no quantity on a given day or days where a device is absent.


Improve performance for my plsql block First of all let me make kind of a disclaimer here:

1) i'm a developer, not an administrator;

2) and i dont have admin rights on my database.

3) table specs :

-- oulp_path_attendance_base_t - 5.437.370 rows

-- ota_gsi_delegate_bookings_t@DISEULPROD (over dblink) - 3.135.400 rows

--inted_parted_events_t@DISEULPROD (over dblink) 683.912 rows

That being said , to the code we go!

CREATE OR REPLACE TYPE  "OULP_AVIDS" as table of varchar2(50)
/

CREATE OR REPLACE PROCEDURE "OULP_UPDATE_ATTENDACE_INCLASS"
AS
   TYPE users_completed_avids IS RECORD(
     avid oulp_path_attendance_base_t.avid%TYPE,
     close_date oulp_path_attendance_base_t.completed_date%TYPE
    );
  TYPE avid_results IS TABLE OF users_completed_avids;

  resulted_avids avid_results;

  v_users OULP_AVIDS;
  v_user_avids OULP_AVIDS;

  v_c SYS_REFCURSOR;
  v_get_avids SYS_REFCURSOR;
  v_get_results SYS_REFCURSOR;

BEGIN
 PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS START');
  OPEN v_c FOR
    SELECT distinct person_id
      FROM oulp_path_attendance_base_t
     WHERE avid is not null;

   LOOP
    FETCH v_c BULK COLLECT INTO v_users LIMIT 100;

    FOR i IN 1..v_users.COUNT LOOP

      OPEN v_get_avids FOR
      SELECT avid
        FROM oulp_path_attendance_base_t
       WHERE avid is not null
         AND person_id = v_users(i);

      FETCH v_get_avids BULK COLLECT INTO v_user_avids;
      CLOSE v_get_avids;

     OPEN v_get_results FOR
      SELECT ev.activity_version_id avid,
             ev.course_end_date close_date
        FROM ota_gsi_delegate_bookings_t@DISEULPROD gsi,
             inted_parted_events_t@DISEULPROD ev
       WHERE gsi.event_id = ev.event_id
         AND gsi.delegate_id_emp = v_users(i)
         AND ota_status IN ('ATTENDED','Attended')
         AND ev.activity_version_id IN (SELECT column_value
                                         FROM TABLE(v_user_avids));
        FETCH v_get_results BULK COLLECT INTO resulted_avids;
        CLOSE v_get_results;

        FORALL j IN 1..resulted_avids.COUNT
        UPDATE oulp_path_attendance_base_t
           SET completed_date = resulted_avids(j).close_date
         WHERE avid = resulted_avids(j).avid
           AND person_id = v_users(i);

    END LOOP;
      EXIT WHEN v_c%NOTFOUND;
   END LOOP;

  CLOSE v_c;
  PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS END');

  EXCEPTION
    WHEN others THEN
  PROCESS_ERROR('OULP_UPDATE_ATTENDACE_INCLASS FAILED');
END;

And the comments :

-- the purpose of this procedure is to updates my local table (oulp_path_attendance_base_t) with the close_dates for some activity_ids (named avids).

-- PROCESS_ERROR() procedure is just a simple logging procedure.

-- im running this via a scheduled job with dbms_scheduler.

Is there any way i can improve the performance here?

The job runs for about 10 hours and needs to run daily.

Cheers,
Alex

If your problem is timing out and you have no way of solving that issue, maybe you might want to process your records in chunks, but also with a single update. Here is a little demo, just to give you the idea, you can use another chunking method other than mod if you don't have an incremental key. And I have to add, for the good practise follow Philip's advice if you can.

create table BIG_UPDATE as
(select
    rownum COL1,
    'data' COL2 from ALL_OBJECTS);

--612 msec
update(
    select COL1, COL2 from BIG_UPDATE
    where
        mod(COL1, 8) = 0
)
set col2 = '1234';

--3secs
update(
    select COL1, COL2 from BIG_UPDATE
)
set col2 = '1234';

rollback;


Similar queries taking different times to complete I had the following query:

SELECT nvl(sum(adjust1),0)
FROM (
  SELECT
    ManyOperationsOnFieldX adjust1,
    a, b, c, d, e
  FROM (
    SELECT
      a, b, c, d, e,
      SubStr(balance, INSTR(balance, '[&&2~', 1, 1)) X
    FROM
      table
    WHERE
      a >= To_Date('&&1','YYYYMMDD')
      AND a < To_Date('&&1','YYYYMMDD')+1
  )
)
WHERE
  b LIKE ...
  AND e IS NULL
  AND adjust1>0
  AND (b NOT IN ('...','...','...'))
  OR  (b = '... AND c <> NULL)

I tried to change it to this:

SELECT nvl(sum(adjust1),0)
FROM (
  SELECT
    ManyOperationsOnFieldX adjust1,
  FROM (
    SELECT
      SubStr(balance, INSTR(balance, '[&&2~', 1, 1))
    FROM
      table
    WHERE
      a >= To_Date('&&1','YYYYMMDD')
      AND a < To_Date('&&1','YYYYMMDD')+1
      AND b LIKE '..'
      AND e IS NULL
      AND (b NOT IN ('..','..','..'))
      OR  (b='..' AND c <> NULL)
  )
)
WHERE
  adjust1>0

My intention is to have all the filtering in the innermost query, and only give the outer ones the field X which is the one I have to use a lot. However, the original query takes a couple of seconds to execute, while the second one won't even finish.  I waited for almost 20 minutes and still no results.

Is there an obvious reason for this to happen that I might be overlooking?

These 2 queries are not the same and don't return the same result .
You end up with following filters:

1st Query:

 WHERE
 (
 a >= To_Date('&&1','YYYYMMDD')
  AND a < To_Date('&&1','YYYYMMDD')+1
 )
 AND
(
    b LIKE ...
   AND e IS NULL
   AND adjust1>0
   AND (b NOT IN ('...','...','...'))
   OR  (b = '... AND c <> NULL)
)

Second:

WHERE
  (
  a >= To_Date('&&1','YYYYMMDD')
  AND a < To_Date('&&1','YYYYMMDD')+1
  AND b LIKE '..'
  AND e IS NULL
  AND (b NOT IN ('..','..','..'))
  )
  OR
 (b='..' AND c <> NULL)

You need to use parenthesis carefully while combining  AND and OR predicates in one condition.


Sql server sqllogship.exe...full docs and/or source code? Are the internals and all the logic of sqllogship documented?

Eg so i can manage it directly, without the wizard? Or replace it with my own tool? (For example, to rewrite it in powershell or C# -- the goal being to instrument it more fully and allow for different transports for the logs [other than a file share].)

I find this syntax (which is real, from a live SQL 2014 instance, but created by the log ship wiz) to be impossible to fully parse:

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\sqllogship.exe" -Backup DB46C321-3453-48FC-9B20-C1823E2B534A -server WIN-2OOC0JPLR5K

What does the guid point to?

(Would be awesome if this were open sourced... Or better yet, ported to powershell and offered to the community...)

I am not sure of any other tool which you are referring to, but i prefer to use this sql logshipping app and yes its documented, refer to below article!

https://msdn.microsoft.com/en-us/library/bb283327.aspx

Not surprisingly, the sqllogship utility is specific to log shipping, which must be enabled and working in order to use the tool. The utility lets you perform copy, restore, and backup operations for an existing log shipping configuration.


How to use pg_archivecleanup on Windows Trying to figure out a proper way to clean old WAL files located in archive directory for point in time recovery purposes.
I know I can use hard-coded name of WAL segment something like this:

pg_archivecleanup.exe H:\Backup\pgArchive 0000000100000001000000C1

but apparantly I would like to automate this process.

Thank you.

Automating the process is simple once you know what you want to delete.

I can give you a few hints:

Typically, you want to remove all WAL archives older than the oldest base backup which you want to retain.
Whenever a base backup is done, PostgreSQL will archive a file called *.backup.

You can use this file as the second argument to pg_archivecleanup to remove everything older than that backup.

So to retain the last three backups, you could look at all *.backup files, sort them by modification time and find the third youngest file, then use pg_archivecleanup with that file as second argument.


How to do a point in time recovery in postgresql? I want to do a point in time recovery in postgresql. This is my recovery.conf

standby_mode = 'off'
primary_conninfo = 'host=localhost user=postgres port=5432 krbsrvname=postgres'
restore_command = 'cp /home/duc/backup/database/postgre_backupdb/%f %p'
recovery_target_time = '2017-06-16 9:00:00'

But when I start the server I always get this error in log

2017-06-17 08:01:53.679 ICT [6984] LOG:  database system was shut down at 2017-06-16 16:27:53 ICT
2017-06-17 08:01:53.679 ICT [6984] LOG:  starting point-in-time recovery to 2017-06-16 09:00:00+07
cp: cannot stat '/home/duc/backup/database/postgre_backupdb/00000002.history': No such file or directory
cp: cannot stat '/home/duc/backup/database/postgre_backupdb/000000020000000C00000042': No such file or directory
2017-06-17 08:01:53.682 ICT [6984] LOG:  invalid checkpoint record
2017-06-17 08:01:53.682 ICT [6984] FATAL:  could not locate required checkpoint record
2017-06-17 08:01:53.682 ICT [6984] HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.6/main/backup_label".
2017-06-17 08:01:53.683 ICT [6983] LOG:  startup process (PID 6984) exited with exit code 1
2017-06-17 08:01:53.683 ICT [6983] LOG:  aborting startup due to startup process failure
2017-06-17 08:01:53.684 ICT [6983] LOG:  database system is shut down

I have try to change the recovery_target_time to other time but still get that error.

How do I do a point in time recovery ?

AFAIK in recovery.conf file apply only for the duration of the recovery
it, this file provide the parameters that PostgreSQL needs to

perform an archive recovery of a database, or to act as a log-streaming.

replication standby.

you need to turn on archive_log
the log show you there is no "cp: cannot stat '/home/duc/backup/database/postgre_backupdb/000000020000000C00000042': No such file or directory" have no that file it mean database couldn't make checkpoint to archive it.
use some tool to do backup such as PG_RMNA (ex: pg_rman restore --recovery-target-time '2017-06-16 9:00:00' -B /BACKUP/pgrman -D $PGDATA -U postgres)


MySQL 5.7 JSON_MERGE combine with GROUP_CONCAT we have table with JSON column
for example simple:

{"a": 1}
{"b": 2}

run JSON_MERGE query

select JSON_MERGE('{"a": 1}','{"b": 2}')

result correct:

{"a": 1, "b": 2}

run the GROUP_CONCAT query

select GROUP_CONCAT('\'',json_text,'\'') from t_json

result

'{"a": 1}','{"b": 2}'

but if try to combine both of them:

SELECT JSON_MERGE((select GROUP_CONCAT('\'',json_text,'\'') from t_json))

return error:

Incorrect parameter count in the call to native function 'JSON_MERGE'

any ways for merge JSON documents from group operation?

Assemble desired JSON using string functions and then cast it into JSON.

Example data

create table item (itemName varchar(200), itemProperties json);

insert into item values
('sword', '{"damage": 20, "durability": 300}'),
('magical sword', '{"damage": 30, "magical damage": {"fire": 5},
                    "durability": 400}'),
('dummy', '{}'),
('spellbook', '{"spell": "lightning bolt", "charge": 10}');

Query to merge all itemProperties together

select cast(
  concat('{',  -- wrap everything in root object '{ ... }'
    group_concat(
      -- strip parenthesis from individual item representation
      -- '{"foo": 1}' -> '"foo": 1'
      substring(itemProperties, 2, length(itemProperties) - 2)),
  '}')
as json) allProperties
from item
-- skip empty JSON values to avoid getting extra comma during
-- group_concat
where itemProperties != JSON_OBJECT();

Resulting JSON

{
  "spell": "lightning bolt",
  "charge": 10,
  "damage": 20,
  "durability": 300,
  "magical damage": {
    "fire": 5
  }
}

Several caveats:

Behaviour of this snippet is different from JSON_MERGE(), for example:

When two or more properties have the same name their values are
overwritten instead of being merged into array of values
It can't merge objects with arrays

Solution as presented only works with objects as a top level entity
and not with arrays. It can be modified to work with arrays.
If relies on string representation of JSON objects beginning and
ending with curly brackets {}. This might change in future versions
of the server.


How can I inspect the security settings of a SQL Server Endpoint? I am in the process of creating a TEST environment for our SQL Server development staff.

In production we have 3 SQL Servers, SQL01 contains several databases that are mirrored to SQL02.  SQL03 acts as the witness in a "high safety with automatic failover" or synchronous configuration.

I've used VMWare P2V to virtualize all three machines onto separate hardware, reconfigured the SID's of the machines, and blackholed the IP addresses of our production servers from these new machines.

I had initially forgotten to blackhole the production witness machine, so the databases on the TEST machines were still using the SQL03 machine as the witness.  Noticing the issue, I decided to reconfigure the databases on TEST to point to the newly virtualized TEST witness, call it TEST03.

To reconfigure the database to use the new witness, I entered the following command on the Primary server TEST01:

ALTER DATABASE [TestDB] SET WITNESS = 'TCP://TEST03.domain.inet:5022';

The response was unexpected:

The ALTER DATABASE command could not be sent to the remote server instance
'TCP://TEST03.domain.inet:5022'.  The database mirroring configuration was
not changed.  Verify that the server is connected, and try again.

I was very perplexed at this error message since the configuration does work on the production machines, and has not been modified in any way on the test machines.

In order to get this to work I needed to create a LOGIN on the test witness:

CREATE LOGIN [DOMAIN\SQLServiceAccount] FOR WINDOWS WITH DEFAULT_DATABASE [Master];

and GRANT it CONNECT rights on the endpoint in question:

GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\SQLServiceAccount];

I was then able to successfully point the mirrored databases on the TEST environment to the new test witness.

How can I inspect the production witness endpoint to see what security is associated with it?

I assume there must be some system catalog that I can inspect, however Books-on-Line does not seem to have anything specific for Endpoints, and Bing is well, Bingless...

Additional Info:

SELECT ep.endpoint_id, p.class_desc, p.permission_name, ep.name, sp.name
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id
WHERE class = '105';

returns:

endpoint_id class_desc  permission_name endpoint_name   principal_name
2           ENDPOINT    CONNECT TSQL    Local Machine   public
3           ENDPOINT    CONNECT TSQL    Named Pipes     public
4           ENDPOINT    CONNECT TSQL    Default TCP     public
5           ENDPOINT    CONNECT TSQL    Default VIA     public

And:

SELECT name, endpoint_id, protocol_desc, type_desc, role_desc
FROM sys.database_mirroring_endpoints;

Returns:

name        endpoint_id  protocol_desc  type_desc           role_desc
Mirroring   65536        TCP            DATABASE_MIRRORING  WITNESS

There appears to be no entry in sys.server_permissions for the mirroring endpoint object.  No major_id and no minor_id matches 65536.  Also, none of the system databases contain any reference to the endpoint.

I'm at a loss.

After spending a portion of the day dealing with moving mirrored databases from server to server in our DEV environment in preparation for our move to SQL Server 2012, I came across this MSDN document that explains that ENDPOINT security is determined solely based on the type of account SQL Server is running under.  If SQL Server is using a domain account, then that account automatically has access to the endpoint.  If SQL Server is using a built-in account such as [Network Service] or [Local System] etc, then the endpoint must be configured with a security certificate, and only holders of the certificate can access the endpoint.

The relevant section from the document:

  Determining the Authentication Type for a Database Mirroring Endpoint

  It is important to understand that the SQL Server service accounts of your server instances determine what type of authentication you can use for your database mirroring endpoints, as follows:

  If every server instance is running under a domain service account, you can use Windows Authentication for your database mirroring endpoints. If all the server instances run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration for the availability databases and is recommended.

  If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created in master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.

  If your server instances use Windows Authentication, you can create database mirroring endpoints by using Transact-SQL, PowerShell, or the New Availability Group Wizard.

  Note:

  If a server instance that is to host an availability replica lacks a database mirroring endpoint, the New Availability Group Wizard can automatically create a database mirroring endpoint that uses Windows Authentication.

  If any server instance is running under a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. If you are using certificates for your database mirroring endpoints, your system administrator must configure each server instance to use certificates on both outbound and inbound connections.

  There is no automated method for configuring database mirroring security using certificates. You will need to use either CREATE ENDPOINT Transact-SQL statement or the New-SqlHadrEndpoint PowerShell cmdlet. For more information, see CREATE ENDPOINT (Transact-SQL).

Remus Rusanu (in his answer) correctly identified how to determine the security in place on a mirroring endpoint; as in:

SELECT ep.name, sp2.name AS Grantee, sp.name AS Grantor, p.permission_name, ep.state_desc
FROM sys.server_permissions p
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantor_principal_id = sp.principal_id
    INNER JOIN sys.server_principals sp2 ON p.grantee_principal_id = sp2.principal_id
WHERE p.class_desc = 'ENDPOINT' AND ep.type_desc = 'DATABASE_MIRRORING'

However in my case on SQL Server 2005 the original mirroring endpoints do not show up in the results of this query - I assume this is because they default security allows CONNECT access to the account SQL Server is running under (as explained above).

So the canonical answer to the question, how do you know who has CONNECT access to an ENDPOINT, is the sum of the query above, and the account SQL Server is using, along with any possible certificates you've configured with access to the endpoint.


How could we start Oracle database in Oracle Exadata which's celldisk has proactive failure? I am trying to open exadata machine but it has error. That EXADATA  Oracle Sun Fire X4720 M3 SAS, Oracle 11gr2 RAC database installed. I was examining celldisks. When i gave command list griddisk I see that result.

cellcli> list griddisk
    DATA_DM01_00_mycelldisk01 proactive failure

We dropped celldisk, we can't recreated griddisk and celldisk, it has error. we can't replace it because we can't get any disk from anywhere. I need to open that exadata and i plan to open database without that celldisk. should i drop physicaldisk?

cellcli> list physicaldisk
    20:0 KERMXM warning - proactive failure, poor performance

when I give command srvctl start database -d mydb it gives error.

ORA-15040: diskgroup is incomplete
ORA-15066: offlining disk "0" in group DATA_DM01 may result in a data loss
ORA-15042: ASM disk "0" is missing from group number "1"

Could i delete disk "0" which is proactive failure and continue my system?
 Thanks

Exadata uses NORMAL or HIGH redundancy diskgroups. It is possible to use EXTERNAL redundancy diskgroups as well, but that is not supported.

Even if you drop a griddisk, and one of the ASM failgroups becomes incomplete, you can still mount the ASM diskgroup and use the mirror side(s). But this will not happen automatically, because the usual MOUNT command throws an error in such a case, and srvctl will not start the database because of that.

You should be able to mount the diskgroup manually with the FORCE option. Log in to the ASM instance as sysasm:

sqlplus / as sysasm

And mount the diskgroup (on all nodes):

alter diskgroup data_dm01 mount force;

Once the diskgroup is mounted, you should be able to start the database.

For the faulty physicaldisk, you should open an SR with Oracle (given that you have valid support contract) for a replacement drive (this usually automatically happens with ASR (Auto Service Request)). The last time I did that, the replacement drive was available in 1-2 days, and their engineer delivered and replaced it in the customers data center.


Stored proc returning customers a user cannot support I've created a stored proc to allocate a customer to a user based on the number and type of customer requests versus the skills of the user. Below is an extract of part of the stored proc - status = 0 means unallocated.

SELECT TOP 1 gdd.customerReference
FROM customerRequests gdd
LEFT OUTER JOIN userSkills us ON us.requestTypeId = gdd.requestTypeId AND us.userId = @pinUserId
LEFT OUTER JOIN requestAttributes dt ON dt.requestTypeId = gdd.requestTypeId
WHERE gdd.status = 0
GROUP BY gdd.requestDateTime, gdd.customerReference, gdd.requestId, gdd.requestTypeId
HAVING COUNT(*) = COUNT(us.userId)

Example data

**Customer Requests**
requestId   requestTypeId   policyNumber    customerReference   requestDateTime userId  status
1   3   Policy A    Customer 1  30/11/2015 10:13        0
2   4   Policy A    Customer 1  30/11/2015 10:33        0
3   11  Policy B    Customer 2  26/11/2015 15:26        0
4   17  Policy B    Customer 2  26/11/2015 15:27        0
5   1   Policy B    Customer 2  27/11/2015 10:05        0

Users Skills

skillId userId  requestTypeId
1   user1   3
2   user1   17
3   user1   11
4   user1   1
5   user2   1
6   user2   3
7   user4   4

Request Attributes

requestTypeId   description priority    tolerance
1   Type A  200 90
2   Type B  999 999999
3   Type C  100 7
4   Type D  100 5
5   Type E  50  5
6   Type F  100 999999
7   Type G  999 999999
8   Type H  999 999999
9   Type I  999 999999
10  Type J  999 999999
11  Type K  100 999999
12  Type L  999 999999
13  Type M  999 999999
14  Type N  999 999999
15  Type O  100 5
16  Type P  100 10
17  Type Q  100 10

By using TOP 1 in the select the query is correctly returning Customer 2 when I substitute @pinUserId with user1. The problem is that if I take TOP 1 out of the query I also see Customer 1 in the result set even though user1 is not able to handle both of Customer 1's requests. If Customer 1's requests came in before Customer 2 user1 would have been incorrectly allocated Customer 1.

Can anyone suggest a solution to this problem.

This will get everything a user is qualified for. Add a TOP 1 and an order by based on priority to get a single request.

select CustomerRequests.customerReference
from CustomerRequests
join userSkills on  userSkills.skillid = CustomerRequests.requestTypeId
and userSkills.userId = 1 --@pinUserId
--join RequestAttributes on RequestTypeId = CustomerRequests.requestTypeId
where requestid not in (
    select CustomerRequests.requestid
    from CustomerRequests
    left outer join userSkills
        on userSkills.skillid = CustomerRequests.requestTypeId
    where userSkills.skillid is null
    )
and CustomerRequests.status = 0


Why did a SQL Server Restart roll-back seemingly committed transactions? I am using SQL Server 2014 Express

It seems that restarting SQL Server when an event such as a Data Lock happens is not (always) such a good idea. http://www.sqlpassion.at/archive/2016/08/08/restarting-sql-server-always-a-good-idea/

I want to ask specifically about the problem that a SQL Server Restart will roll-back uncommitted transactions.

My colleague ran SELECT @@TRANCOUNT and got zero. Therefore he assumed that restarting the SQL Server would not result in any kind of roll-backs. Nevertheless, a position which was moved from one table to another, got moved back after the restart.

Are there any situations where roll-backs would happen even if SELECT @@TRANCOUNT would return zero?

As per docs online:

  Returns the number of BEGIN TRANSACTION statements that have occurred
  on the current connection.

https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql

This is a connection level variable. If there were transactions open for connections other than the one your colleague ran his query on, there could have been open transactions.


Is Transaction Log Filling up Because Log Truncation is Delayed? The volume that holds my transaction log seems to be constantly at 97% full, so I am trying to identify the cause.

DBCC SQLPERF(logspace)

Yields the following results:

There don't seem to be any old transactions that are holding up the transaction log truncation:

SELECT
    database_transaction_begin_time,
    database_transaction_state,
    database_transaction_begin_lsn
FROM sys.dm_tran_database_transactions;

I suspect SQL Server is unable to truncate the transaction log because it cannot take a backup.
I run the following query on my database:

SELECT
    database_id,
    recovery_model_desc,
    log_reuse_wait,
    log_reuse_wait_desc
FROM sys.databases;

The result says that "model" (database_id=3) has log_reuse_wait_desc=LOG_BACKUP and recovery_model_desc=FULL.
This tells me that a log backup is required before the transaction log can be truncated (https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017#Truncation).

Next, I run this query to determine when the last log backup was taken (see https://stackoverflow.com/questions/40050221/sql-server-what-exactly-is-the-backup-finish-date-in-master-sys-databases):

SELECT
    d.dbid,
    b.type,
    MAX(b.backup_finish_date) AS backup_finish_date
FROM master.sys.sysdatabases d
    LEFT OUTER JOIN msdb..backupset b
         ON b.database_name = d.name
            AND b.type = 'L'
GROUP BY d.dbid, b.type
ORDER BY backup_finish_date DESC;

backup_finish_date=null, which tells me that model has never had a log backup.

These queries lead me to conclude

The transaction log cannot be truncated because a backup needs to be taken of "model".
A backup of "model" has never been taken.

Am I interpreting these results correctly?

I am confused because "model" is a System database, and it doesn't seem like I should have get involved with its log backups or truncation.

Can anyone recommend a way to free up space in the transaction log?

Am I interpreting these results correctly?

Yes, you are. Although it's a system database, it doesn't automatically back itself up. You must add these to your backup schedules just like the user databases.

  Can anyone recommend a way to free up space in the transaction log?

As you read, and understood, back it up. Once you back up the TLOG (which will require a FULL BACKUP first if you haven't done that) the % used will drop a lot. Then you can shrink the file. Go ahead and schedule these, or set the DB to SIMPLE MODE if you're good with that.

I should mention none of your log files are that big. They also probably aren’t set to a max growth so they probably won’t fill up until your volume does. Be sure to check them.


How can I select the last edited version of the post? I have a question and answer website like SO. Also I have a table which contains both the questions and answers and their edited version. Here is my table structure:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 1  | title1  | question content          | NULL    | 0    | NULL      |
| 2  |         | answer content            | 1       | 1    | NULL      |
| 3  | title2  | question content          | NULL    | 0    | NULL      |
| 4  |         | answer content            | 3       | 1    | NULL      |
| 5  |         | answer content            | 1       | 1    | NULL      |
| 6  |         | answer content (edited)   | NULL    | 1    | 2         |
| 7  | title3  | question content          | NULL    | 0    | NULL      |
| 8  | title1  | question content (edited) | NULL    | 0    | 1         |
| 9  |         | answer content            | 7       | 1    | NULL      |
| 10 | title1  | question content (edited) | NULL    | 0    | 1         |
| 11 | title3  | question content (edited) | NULL    | 0    | 7         |
+----+---------+---------------------------+---------+------+-----------+

Column explanations:

related column:

NULL for both questions and edited version of questions/answers
{the id of its own question} for answers

type column:

0 for questions
1 for answers

edited_id column: (the id of original post)

NULL means it is a original question/answer
{any number} means it is a edited version of a question/answer.

Now I need a query to select a question and all its answers. Noted that I need to select the last edited version of them (if they have been edited).

Example1: I have this value: :id = 1 and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 10 | title1  | question content (edited) | NULL    | 0    | 1         |
| 6  |         | answer content (edited)   | NULL    | 1    | 2         |
| 5  |         | answer content            | 1       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Example2: I have this value: :id = 3 and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 3  | title2  | question content          | NULL    | 0    | NULL      |
| 4  |         | answer content            | 3       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Example2: I have this value: :id = 7 and I want this output:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 11 | title3  | question content (edited) | NULL    | 0    | 7         |
| 9  |         | answer content            | 7       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Here is my current query:

SELECT *
FROM QandA
WHERE (id = :id AND type = 0) OR
      (related = :id AND type = 1)
ORDER BY type -- noted that the order of answers doesn't matter

As you see, my query doesn't support edited version. Anyway, how can I replace edited version of posts when there is a edited row of that post?

Note: Please don't tell me "don't keep both the questions and answers in the same table", Because I know it. But now I need to solve problem above.

You need a groupwise max.  I think you will need one query to find the "latest" question, then one for the latest answer.  Then UNION ALL the results together in order to get them in the same resultset.


PostgreSQL Failover - What tools should I use? Here is the scenario:

There are two machines running CentOS 6.2 - machine0 and machine1

Both have PostgreSQL 9.1 installed.

One of them should be active, as a master system and through asynchronous streaming replication the other machine, the standby should copy changes to the database from the master system.

Assuming that machine0 is the master and machine1 is the standby at the beginning.

If the master (say machine0) fails (failure here means the postgresql server crashes) the standby should takeover from the master and become the new master.

machine1, the new master handles all the database operations and when the postgresql server in machine0 gets back online, it should become a standby, start synchronizing from the point it lost contact with machine1 and copy all changes to the database and stay in standby mode.

When machine1 fails, the whole cycle repeats.

When the standby fails and comes back online, it should start reading from the master and synchronize data.

I'm confused as to what are tools that I need to use for setting this up as I understand PostgreSQL does not come with failover by default.

If someone can link me to threads/pages that describe how to do what I am trying, I'll be really grateful.

If you are interested in synchronous DB Replication and Failover, I have a suggestion. You could look into using two tools:

DRBD (Disk Replicated Block Device) provides Disk Level Replication (synchronous) between disks on two different servers. The disk changes are replicated over the network. It is best to use a crossover cable on eth2 for the NICs using the 192.168.x.x netblock.
ucarp allows for DBVIP management and failover between two different servers.

You can use them in conjunction as follows:

DBServer1 has IP 10.1.2.30
DBServer2 has IP 10.1.2.40
DB VIP is 10.1.2.70

Setup ucarp on two different servers in such a way that each ucarp instance communicates with the other along a virtual router ID

DBServer1 will have

/usr/local/sbin/ucarp -v 200 -p sometagname --interface=eth2 -a 10.1.2.70 -s 10.1.2.30 -b 3 -r 5 --upscript=/usr/local/sbin/vip-up.sh --downscript=/usr/local/sbin/vip-down.sh --neutral -z -B

DBServer2 will have

/usr/local/sbin/ucarp -v 200 -p sometagname --interface=eth2 -a 10.1.2.70 -s 10.1.2.40 -b 4 -r 5 --upscript=/usr/local/sbin/vip-up.sh --downscript=/usr/local/sbin/vip-down.sh --neutral -z -B

What's the reason -b (broadcasts) is 3 on one server and 4 on the other? In case both servers are restarted, the server with the lowest -b will bring ucarp first. As for -r, that is dead ratio. Thus, DBServer1 will come up in 15 seconds (3X5) and DBServer2 comes up in 20 seconds (4X5).

Let's say DBServer1 crashes. ucarp on DBServer2 will check for 20 seconds for a handshaking fro ucarp on DBServer1. If nothing is detected, the vip-up.sh script on DBServer2 will take control of the DBVIP.

OK all of this is just for failover and DBVIP management. What about the PostgreSQL database? Surprisingly, PostgreSQL is only running on one server at a time. Whoever has the DBVIP should have DRBD in Primary state. This ties back in to the vip-up script. How do you script it?

Here is /usr/local/sbin/vip-down.sh script (conceptual)

#! /bin/sh
exec 2> /dev/null
/sbin/drbdadm disconnect drbd0
/sbin/drbdadm  primary drbd0 &&
/bin/mount postgres-data-folder /mnt/drbd0
/sbin/ip addr add 10.1.2.70/24 dev eth2
/sbin/service postgres start &&
/usr/local/sbin/vipmon.sh &
touch /tmp/vip-up

Here is /usr/local/sbin/vip-down.sh script (conceptual)

#! /bin/sh
exec 2> /dev/null
/sbin/service postgres stop
/bin/umount -l  /dev/drbd0
/sbin/drbdadm secondary drbd0
/sbin/ip addr del 10.1.2.70/24 dev eth2
/bin/rm /tmp/vip-up
/usr/bin/killall -9 ucarp
/usr/local/sbin/vip-down.sh
kill `ps auxww | grep vipmon | awk '{print $2}'`

All you need to do is setup pg_hba.conf to make sure all users come via 10.1.2.70

Essentially, vip-up performs this sequence

Have DRBD go Primary
Mount postgres data folder on /dev/drbd0
Startup postgres
start vipmon process

Contrawise, vip-down performs this sequence

Shutdown postgres
unount /dev/drbd0
Have DRBD go Secondary

What about vipmon.sh? You could script that to check, in an indefinite loop, the state of postgres (it is running), check the DRBD device (can you still write to the posts data folder)

With this setup, you have postgres on the DRBD Primary and a disk level copy of the postgres data folder on the other DBServer (the DRBD Secondary). postgres is not running on the DRBD Secondary.

When a failover happens, you just need time for ucarp to detect is is safe to mount the postgres data, startup postgres, and acivate the vipmon script.

What great about this setup is that in the event of a failover, the DBServer that becomes DRBD Primary should have a 100% disk level copy of the server you failed away from. Thus starting postgres should level you at a consistent state. The transaction logs (in pg_xlog) should be in tact (less the intermittency due to failover)

I hope these suggestions help. Although I am a MySQL DBA, I use MySQL and DRBD at my employer's web hosting company on a regular basis. I installed MySQL/DRBD in the aforementioned manner. I did this once for a client using PostgreSQL/DRBD. It works great. It's Open Source. You just need to perform due diligence in learning DRBD and ucarp. This would include reconnecting DRBD after a failover, handling split brain scenario where both DB Servers go Primary, and things like these.


SQL Server 2008 R2 - Moving nodes in mirrored environment Was hoping that I would find this documented somewhere, but I've searched and searched. Finally asking the question myself:

I have SQL Server 2008 R2 in a HA setup (primary/mirror/witness)

I want to move the mirror and witness servers to different hardware from what they're currently on.

It's only 1 db and about 20GB in size, it uses SQL authentication for the db (not Windows auth) and the Windows SQL service will run with the same domain user on all nodes.

I assume I start by installing SQL Server on the new nodes, and adding the endpoints to each instance.

From there would I just remove mirroring from the primary instance and re-create mirroring, adding the new nodes? Would I need to restore from a backup on the new mirror instance?

Hopefully someone can lay out the steps involved, thank you.

You are correct..

Install SQL Server on the new nodes
Use SP_HELP_REVLOGIN to copy the user account to the secondary node
Take a full backup and a single log backup of the primary server and restore on the secondary with no_recovery
Break the mirror and remove the secondaries from the primary server
Create the endpoints with TSQL or use the Mirroring wizard to configure them and endpoint security
start mirroring


How do we find who used Dedicated Admin Connection Is there any way to find who used Dedicated Admin Connection?

Not active connection but the previous one which is already closed?

SQL Server doesn't maintain this information anywhere; if you're trying to catch someone abusing the DAC, either take away sysadmin from the people who clearly shouldn't have it, or at least set up some kind of polling mechanism to catch them. You can have a table like this:

CREATE TABLE dbo.DBA_DacAccess
(
  ConnectTime      datetime,
  FirstObservance  datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
  LoginName        sysname,
  HostName         sysname,
  AppName          sysname,
  Interface        nvarchar(32),
  ClientNetAddress nvarchar(48)
);

And then determine some reasonable interval to collect info about a DAC connection whenever one exists, and then run the following at that frequency (using a SQL Server Agent job, probably):

INSERT dbo.DBA_DacAccess
(
  ConnectTime,
  LoginName,
  HostName,
  AppName,
  Interface,
  ClientNetAddress
)
SELECT
  c.connect_time,
  s.login_name,
  s.[host_name],
  s.[program_name],
  s.client_interface_name,
  c.client_net_address
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE c.endpoint_id = 1
AND NOT EXISTS
(
  SELECT 1 FROM dbo.DBA_DacAccess
  WHERE connect_time = c.connect_time
);

This will work going forward, but you have to be lucky (or be polling frequently) to catch them if they're in and out quickly, so you may want to fine tune that schedule. Also, in case you wonder why this isn't a LOGON TRIGGER, there are two reasons: (1) DAC bypasses those, by design and necessity, and (2) the rows/data in these DMVs wouldn't exist until they escaped the trigger anyway.

For events in the past, and again if you're lucky, folks who try to access the DAC from SSMS will get bitten by the background connection that tries to connect again, since that failed attempt will be written to the error log, and the IP address is appended to the end of the message (but none of the other information is included). This won't help if someone used remote desktop to the server, or an app other than SSMS that doesn't try to establish additional connections using the same ADMIN: credentials, but should be useful if they used SSMS remotely:

  04/23/2018 08:11:18
  Logon
  Unknown
  Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped,  either by logging off or ending the process. [CLIENT: 192.168.0.99]


Share single index for multiple linearly correlated columns I've got a table with two columns whose values have a perfect linear correlation, for example

CREATE TABLE measurements (
  sensor int PRIMARY KEY,
  num serial PRIMARY KEY,
  time timestamptz DEFAULT now(),
  value float
);
-- many times:
INSERT INTO measurements(sensor, value) VALUES ($1, $2);

Both the time and the num are monotonously increasing, a row with higher num value will also have a larger time value.

Postgres will create a btree index on the primary key columns. Can I somehow tell it to also use the same index when querying for rows by their time instead of by their num? As in

SELECT * FROM measurements WHERE sensor = $1 AND time >= $2 ORDER BY time;

The resulting rows would have exactly the same order as if sorted by num.

Is there a way to let the optimiser know? I've seen many articles on cross column correlation statistics, most of them linked in this StackOverflow topic, but the multi-column statistics seems to only analyze dependencies between individual values, and are unable to do a linear correlation.

I was hoping to achieve the same result as if I created another index on sensor, time, but have postgres need to maintain and store only a single index.

No, you cannot use an index on one column for searches on another column. I second Jasens comment that you should consider doing away with the generated integer and using the timestamp instead.


What happens if Always On High Availability nodes have different connection default languages? I have been working on databases in a Always On High Availability group which - for reasons that I do not understand - have different default languages for an application's login on the primary and secondary nodes.

The particular situation I have at the moment is this:

The language that the application expects is British English. It does not specify the language that it uses when it connects; it uses whatever the default language is for the login. The documentation for the application says that the default language should be British English, presumably because it does not specify a language on connecting.
The application connects to the listener, not to either of the nodes directly.
The application will often save dates as strings (e.g UPDATE FooTable SET BarColumn = '01\02\2020' WHERE IdColumn = 42). The data will be saved in a date or datetime column.
The default language of the login on the database listener is British English.
The default language of the login on the primary node is British English.
The default language of the login on the secondary node is American English

I am no DBA, and certainly no expert in Always On High Availability. What dangers might there be/have been in using this configuration? Does it matter if the languages on the primary and secondary nodes are swapped?

If the application is setting the language on the session when it logs in, then the default language doesn't matter.  If it's not specifically setting the language on the session, then there is risk of date strings being converted incorrectly, and dates being returned in American English format instead of British English.

Unless there is some unstated reason, I recommend simply changing the default language on the login on the secondary to match the primary.


Oracle: Inserting data using a database link I am trying to use a SELECT query to populate a table in DB2 using a database link to DB1. I am using Oracle SQL Developer on a Windows 7 VM. I connect to DB2, and run the following query.

INSERT INTO DB_NAME.TABLE_NAME (SELECT * FROM DB_NAME.TABLE_NAME@LINK_TO_DB1);

Every time, no matter what, I get the following error:

ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 -  "TNS:could not resolve the connect identifier specified"
*Cause:    A connection to a database or other service was requested using
           a connect identifier, and the connect identifier specified could not
           be resolved into a connect descriptor using one of the naming methods
           configured. For example, if the type of connect identifier used was a
           net service name then the net service name could not be found in a
           naming method repository, or the repository could not be
           located or reached.
*Action:
           - If you are using local naming (TNSNAMES.ORA file):
           - Make sure that "TNSNAMES" is listed as one of the values of the
           NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA)
           - Verify that a TNSNAMES.ORA file exists and is in the proper
           directory and is accessible.
           - Check that the net service name used as the connect identifier
           exists in the TNSNAMES.ORA file.
           - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
           file.  Look for unmatched parentheses or stray characters. Errors
           in a TNSNAMES.ORA file may make it unusable.
           - If you are using directory naming:
           - Verify that "LDAP" is listed as one of the values of the
           NAMES.DIRETORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA).
           - Verify that the LDAP directory server is up and that it is
           accessible.
           - Verify that the net service name or database name used as the
           connect identifier is configured in the directory.
           - Verify that the default context being used is correct by
           specifying a fully qualified net service name or a full LDAP DN
           as the connect identifier
           - If you are using easy connect naming:
           - Verify that "EZCONNECT" is listed as one of the values of the
           NAMES.DIRETORY_PATH parameter in the Oracle Net profile
           (SQLNET.ORA).
           - Make sure the host, port and service name specified
           are correct.
           - Try enclosing the connect identifier in quote marks.
Error at Line: 1 Column: 30

I've been googling and attempting different solutions for 2 days with no luck. I've done all of the following:

Set PATH environment variable to C:\Oracle\instantclient_11_2
Set ORACLE_HOME environment variable to C:\Oracle\instantclient_11_2
Set TNS_ADMIN environment variable to C:\Oracle\instantclient_11_2\network\admin
Created a tnsnames.ora file with the correct connection information in C:\Oracle\instantclient_11_2\network\admin
Set the Tnsnames Directory field in SQL Developer (Tools > Preferences > Database > Advanced) to C:\Oracle\instantclient_11_2\network\admin

I am at a loss as to why these database links aren't working. Does anyone have any advice?

If you want to have a database link on DB1 that points to DB2, DB1 would need to be able to resolve whatever TNS alias you specify in the database link definition.  Assuming that you are using tnsnames.ora files everywhere for TNS name resolution, that would mean that the tnsnames.ora file on DB1 would need to have an entry that points to DB2.  The TNS alias you specify in your CREATE DATABASE LINK statement would need to reference that TNS alias (which could be different than the alias for DB2 that you have created on your local machine).  If you have limited access to the machine where DB1 resides, you would most likely need the DBA for DB1 to ensure that the TNS alias gets created appropriately.


3 different queries from 2 tables I have three tables (palettes, boxes, layout) with sample values:

Palettes

|ID_P|sign|
|   1|  P1|
|   2|  P2|
|   3|  P3|
|   4|  P4|

Boxes

|ID_B|sign|
|   1|  B1|
|   2|  B2|
|   3|  B3|
|   4|  B4|

Layout

|ID_L|ID_P|ID_B|
|   1|   1|   1|
|   2|   2|   2|
|   3|   2|   3|
|   4|   3|   4|
|   5|   4|   4|

So I can have 3 cases:

One palete with one box (ID_L = 1)
One palette with more than one boxes on it (ID_L = 2,3)
One big box on more than one palette (ID_L= 4,5)

Now, I need to group it, so I need three separate queries, that will return:

for case 1: all ID_P's of 1:1 "relation"

for case 2: all ID_P's of 1:N "relation"

for case 3: all ID_P's pf N:1 "relation"

Basically I have sollutions for case 2 and 3, but have no idea how to get case 1.

Using sub queries this should extract all ID_Ps which only occur in the Layout table once and then INNER JOIN them with all ID_Bs which only occur in the Layout table once... essentially returning only ID_Ps with no more than one box per palette, and no more than one palette per box.

SELECT x.ID_P FROM
  (SELECT ID_P, ID_B, COUNT(*) as count FROM Layout GROUP BY ID_P) x
  INNER JOIN
  (SELECT ID_P, ID_B, COUNT(*) as count FROM Layout GROUP BY ID_B) y
WHERE x.count = 1 AND y.count = 1 AND x.ID_P = y.ID_P;


Security permission to only read SSAS Cube with Excel I have an SSAS database set up on a SQL Server 2008 server.  I have a Role set up with a group of users that I want to be able to access the Cubes in that database.  I set up "Read definition" and "Read Access" wheverever it says, but that didn't seem to work.

The users were able to open up a pivot table that I had previously set up, but none of the hours would show up, but the dimension data would show up just fine (users, departments, etc).

I then set up an individual user and gave them Full control (Admin), which worked just fine.  What am I missing that allows them to access the Cube properly?  Process database?  Something else?

In order for a specific role to have permissions over a cube data you have to specifically grant cube permissions to that role.
That can be done using the Cube tab in the role properties page: in SSAS

-> Databases -> Your db -> Roles -> your specific role -> Cubes tab.

You can assign specific permissions for:

Access - None, Read, or ReadWrite
LocalCube/DrillthroughAccess - None, Drillthrough/Drillthrough and Local Cube
Process

Same for dimensions and their data and specific cell data.
You can find further info about cube permissions in the following articles:

Permissions and Access Rights (Analysis Services - Multidimensional Data)
Configuring permissions for SQL Server Analysis Services
Security with Analysis Services - Overview


Query Store "Overall Resource Consumption" details I want to know how much total CPU a database has consumed for a given date span, for all queries.

I can see it in SSMS (under Query Store / Overall Resource Consumption), but how can I query for that programmatically?

The Query Store GUI uses this T-SQL:

exec sp_executesql N'WITH DateGenerator AS
(
SELECT CAST(@interval_start_time AS DATETIME) DatePlaceHolder
UNION ALL
SELECT  DATEADD(d, 1, DatePlaceHolder)
FROM    DateGenerator
WHERE   DATEADD(d, 1, DatePlaceHolder) < @interval_end_time
),
UnionAll AS
(
SELECT
    CONVERT(float, SUM(rs.count_executions)) as total_count_executions,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) as total_duration,
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) as total_cpu_time,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) as total_logical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) as total_logical_io_writes,
    ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) as total_physical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2) as total_clr_time,
    ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0) as total_dop,
    ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2) as total_query_max_used_memory,
    ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0) as total_rowcount,
    DATEADD(d, ((DATEDIFF(d, 0, rs.last_execution_time))),0 ) as bucket_start,
    DATEADD(d, (1 + (DATEDIFF(d, 0, rs.last_execution_time))), 0) as bucket_end
FROM sys.query_store_runtime_stats rs
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY DATEDIFF(d, 0, rs.last_execution_time)
)
SELECT
    total_count_executions,
    total_duration,
    total_cpu_time,
    total_logical_io_reads,
    total_logical_io_writes,
    total_physical_io_reads,
    total_clr_time,
    total_dop,
    total_query_max_used_memory,
    total_rowcount,
    SWITCHOFFSET(bucket_start, DATEPART(tz, @interval_start_time)) , SWITCHOFFSET(bucket_end, DATEPART(tz, @interval_start_time))
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY bucket_start ORDER BY bucket_start, total_duration DESC) AS RowNumber
FROM UnionAll
) as UnionAllResults
WHERE UnionAllResults.RowNumber = 1
OPTION (MAXRECURSION 0)',N'@interval_start_time datetimeoffset(7),@interval_end_time datetimeoffset(7)',@interval_start_time='2018-02-19 10:41:32.4646188 -06:00',@interval_end_time='2018-03-19 11:41:32.4646188 -05:00'

The results look like:

╔════════════════════════╦════════════════╦════════════════╦════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════╦═══════════╦═════════════════════════════╦════════════════╦════════════════════════════════╦════════════════════════════════╗
║ total_count_executions ║ total_duration ║ total_cpu_time ║ total_logical_io_reads ║ total_logical_io_writes ║ total_physical_io_reads ║ total_clr_time ║ total_dop ║ total_query_max_used_memory ║ total_rowcount ║        (No column name)        ║        (No column name)        ║
╠════════════════════════╬════════════════╬════════════════╬════════════════════════╬═════════════════════════╬═════════════════════════╬════════════════╬═══════════╬═════════════════════════════╬════════════════╬════════════════════════════════╬════════════════════════════════╣
║                      2 ║           3.25 ║           3.25 ║                      0 ║                       0 ║                       0 ║              0 ║         2 ║                           0 ║              1 ║ 2018-03-18 18:00:00.000 -06:00 ║ 2018-03-19 18:00:00.000 -06:00 ║
╚════════════════════════╩════════════════╩════════════════╩════════════════════════╩═════════════════════════╩═════════════════════════╩════════════════╩═══════════╩═════════════════════════════╩════════════════╩════════════════════════════════╩════════════════════════════════╝

I saw that using a T-SQL statement trace against the server while simultaneous opening the GUI for "Overall Resource Consumption" on my local SQL Server 2016 instance.

I re-factored that above query down a bit, to:

DECLARE @interval_start_time datetimeoffset(7);
DECLARE @interval_end_time datetimeoffset(7);
SET @interval_start_time = '2018-02-19 10:41:32.4646188 -06:00';
SET @interval_end_time = '2018-03-19 11:41:32.4646188 -05:00';

SELECT
      total_count_executions = CONVERT(float, SUM(rs.count_executions))
    , total_duration = ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2)
    , total_cpu_time = ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2)
    , total_logical_io_reads = ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2)
    , total_logical_io_writes = ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2)
    , total_physical_io_reads = ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2)
    , total_clr_time = ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))*0.001,2)
    , total_dop = ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))*1,0)
    , total_query_max_used_memory = ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))*8,2)
    , total_rowcount = ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))*1,0)
    , bucket_start = DATEADD(d, ((DATEDIFF(DAY, 0, rs.last_execution_time))),0 )
    , bucket_end = DATEADD(d, (1 + (DATEDIFF(DAY, 0, rs.last_execution_time))), 0)
FROM sys.query_store_runtime_stats rs
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY DATEDIFF(DAY, 0, rs.last_execution_time)
ORDER BY DATEDIFF(DAY, 0, rs.last_execution_time);

That returns a row for each day between the start end end dates provided.


Implement row-level security as a user table or millions of roles? PostgresSQL 10.4 is our backend database for a consumer-facing app with millions of users. When implementing row-level security so each user can only access their own data, is it best practice to use a separate Postgres role for each user or to have a shared role with a user table?

The documentation always uses examples with separate Postgres roles, whereas e.g. this blog describes how to do it using a user table.

With that many users, it's probably only practical to manage it using a shared role with your own user table. In fact, I know of very few consumer-facing applications that manage their users using database roles. The thing is, you almost certainly need to store more information per user than what Postgres stores for roles. So if you just manage your users as data in your own table, you're done. But if you have to create a role for each user too, then you're increasing the work you need to do. So I'd stick with a user table.


How can I add a numeric version_id column to an existing table? I have a table in a postgres db with rows versioned with a timestamp that I'd like to add a numeric version_id column to.

eg.
existing:

ID  VERSION_DATE
--  -----------------------
A   2015-02-05 21:25:56.123
A   2015-02-06 21:15:56.456
A   2015-02-07 11:25:52.789
B   2015-01-01 13:44:54.223

desired:

ID  VERSION_DATE             VERSION
--  -----------------------  -------
A   2015-02-05 21:25:56.123  1
A   2015-02-06 21:15:56.456  2
A   2015-02-07 11:25:52.789  3
B   2015-01-01 13:44:54.223  1

Is there a query that can calculate and insert the version values?

This is exactly what ROW_NUMBER() window function can do:

select
    id, version_date,
    row_number() over (partition by id order by version_date) as version
from
    your_table ;

I don't see a reason to have these version numbers stored in a column of the table. It would be fairly easy to insert the values the first time but quite complicated to keep them updated after every insert, delete and update operation (probably done via triggers or stored procedures).

It might not be that complicated if no rows are ever deleted and the version_date is never updated. That would mean that only during inserts the new (inserted) values would have to be correctly calculated.

Another option, would be to use a materialized view.


Do certain string patterns affect query performance on a VARCHAR column? I am a beginner on MySQL Administration and performance problems. Now this is confusing me.

I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, user:4 to identify a row in one type of entity.

There are 5 class names for now.

Since the string could be constructed as note:123 or 123:note, either holds the meaning of it, I am wondering does 123:note will have better query performance if I do select * from table where identity='123:note' than the other? I am thinking that 123:note will have more variety from the beginning of string, because the id is at the front.

Or it just doesn't matter?

UPDATE:
the identity field is just a redundant field for query, the type and id are actually stored in other two fields in the same table.

So:

1st, where type='note' and id=123' is definitely faster than where identity in ('note:123', 'user:456'). I think it's definitely faster, right?

2nd, if I have to have this redundant column, note:123 or 123:note which pattern is faster when I do where identity in ('note:123', 'user:456'), and the length of identity is 20.

And here is create info: likeable_identity is the identity field I am talking about.

CREATE TABLE `likes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `likeable_id` int(11) DEFAULT NULL,
  `likeable_type` varchar(255) DEFAULT NULL,
  `likeable_identity` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_likes_on_user_id_and_likeable_id_and_likeable_type` (`user_id`,`likeable_id`,`likeable_type`),
  KEY `index_likes_on_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8814 DEFAULT CHARSET=utf8;

Thanks.

Several points:

1) Store the data in two columns.

2) Normalize 'type' to a separate table, so you'll only put an INT into your index rather than a varchar(255).

3) Switch to UNSIGNED INT for the ids, and maybe TINYINT for the types.

4) Tell the app people to learn about multiple WHERE clauses for their SQL and give them a good index.

3) Once you do those, your question might become, "should my composite index be (id,type) or (type,id)?"


Handle development issues when dealing with different collation for the instance/database setting Looking for some guidance on how we can handle the scripts/schemas when being run on different collation in sql server.
We have a process where multiple stored procs are created lets say with schema sales. Therefore all related tables and procs for that within database are stored as sales.table1, sales.table2 ... etc and stored procs like sales.us_proc1, sales.usp_proc2.... etc.
Now for default collation of database on SQL server instance with default collation the procs runs fine.
However when we deploy same objects/procs on sql server with case sensitive collation instances same scripts starts failing.
What we can do as of now is have 2 sets of scripts. Say 1st for all non case sensitive server and other set for case sensitive.
But problem is these 2 sets of deployment will go on server which adds bunch of unwanted objects/procs etc..
Please suggest how should be handle such scenarios where single set of deployment for such scripts can be done irrespective of what the server/database collation might be?
Edit- Trying to update my question for few questions asked:
Scripts are failing with error message like below on case sensitive databases-

Msg207,Level 16, state1 - Invalid column name Timestamp - say for
proc1
Msg207,Level 16, state1 - Invalid column name state- say for proc2

Yes we did created 2 sets of scripts which ideally does not seem helpful here as lot of objects are just getting deployed twice for case sensitive server
Also we are not creating database as the database would be there and we just need to deploy bunch of these scripts.
Also the collation ends with BIN for TempDB and our database where scripts are deployed.
As mentioned in answers for TempDB, i have gone through most of my scripts and it does not look like we have references to # temp tables in most of the stored procs. HOwever yes we do use local variables and table variables @. Does this also counts for same issues?

Schemas have nothing to do with collation. Also, it would certainly help to know what is meant by "scripts starts failing". Please post the exact and full error message(s) to the question.
For the moment, based on the following two pieces of info:

The scripts are failing, not just returning unexpected / fewer results,
and:

You said that you can have two sets of scripts, not that you already do have (or at least have tried)

I am guessing that the procs have inconsistent references to the table and/or schema names in terms of casing. For example, the actual object being Sales.Table1 but referenced in one or more procs as Sales.table1 (only difference is the t in Table1). That is not something that can be fixed via a COLLATE clause in a query. That requires fixing the code to use the proper casing. In fact, if you are going to deploy to instances that are case-sensitive, then it would be best to update your development (and test / QA / Staging) instances to also use a case-sensitive default collation at the database level. If your code can be deployed on instances using a binary collation, then perhaps use a binary collation. This will help you catch these types of errors very early in the development cycle.
Additionally, using a case-sensitive or even binary collation (ending in _BIN2, not _BIN) for the database and leaving the instance-level collation as case-insensitive (or just something different than the database default collation) will help you identify potential errors in queries related to temp tables.
In the end, you definitely shouldn't need multiple sets of scripts to handle different collations, especially if this is an issue with creating procedures that fail due to using a different casing on columns names but not tables (which results in an immediate error as it will find the table but not the column), or an issue with executing the procedure if there is a difference in casing for the table name as deferred name resolution will allow the proc to be created but will error upon execution.
NOW, regarding temp tables, the answer is not necessarily to use COLLATE DATABASE_DEFAULT. It all depends on what your app is trying to accomplish for each string column affected. (And FYI: this part is the same, even if you deploy the full database setting a specific collation and aren't having a problem running a script that creates schemas / objects in a DB with any random collation).
If any affected column uses the database's default collation (i.e. does not specify a collation in the CREATE TABLE statement), and the app doesn't require a particular collation for this specific operation/query, then yes, use COLLATE DATABASE_DEFAULT next to each predicate / concatenation / reference in the query containing the temp table.
However, if an affected column is given a specific collation when the table is created, or if the operation / query requires a specific collation (i.e. a filter that must be case-insensitive no matter what), then you should use COLLATE {specific_collation_name} next to each predicate / concatenation / reference in the query containing the temp table.
Based on info added to the question:
The scenario is pretty much what I had guessed based on the previous info:

Your scripts are being deployed to existing databases (outside of your control)
The errors are coming from column names being referenced in the stored procedures with different casing than they were created with.

Hence, the advice doesn't really change: You only need one set of scripts, the set with the column references matching the casing used when creating the tables. So if a table has a column named TimeStamp, then referencing that column as Timestamp in the code is an error (as it would be in many / most programming languages). You need to fix your source procs, leaving you will a single, correct set of scripts that will work everywhere.
You should also consider either changing the development instance (or just database at the very least, which is easier to change anyway) to use a binary collation. Or, deploy to an instance (or database) using a binary collation as part of your CI process (if you have one), or just as a matter of practice if you don't have an automated process. In either case, you will catch casing differences much earlier in the development process.
Not using temp tables will avoid collation differences in [tempdb]. As for table variables, those should be fine as their string columns, by default, use the database's default collation. You can still run into an issue if you have a string column set to a collation different from the database's default and compare it to a column in a table variable that wasn't created with that same collation. In that case, you just use COLLATE {collation_name} everywhere that issue is happening, specifying the collation used for the column from the user table.


Speed and size with hidden index in postgresql I have a table with 1M rows without index, and i don't understand why when i go for :  SELECT * from table WHERE id = 954000  i get the result
instantly ! I have no index how its possible to get me the answer
instantly ? To be sure , i run a command to know the size of a possible index :select pg_size_pretty(pg_total_relation_size('geopost') - pg_relation_size('geopost'));
The result suprise me because it outputing me 40kb . I'm litle confuse now , is postgres made a kind of hidden index ?

the query plan:
EXPLAIN ANALYZE
select * from geopost where id = 954000
Gather  (cost=1000.00..14682.07 rows=1 width=38) (actual time=36.633..38.140 rows=1 loops=1)  Workers Planned: 2  Workers Launched: 2  ->  Parallel Seq Scan on geopost  (cost=0.00..13681.98 rows=0 width=38) (actual time=26.701..27.117 rows=0 loops=3)        Filter: (id = 954000)        Rows Removed by Filter: 333333Planning time: 0.087 ms

regards

Computers are fast and 1,000,000 is no longer a very large number.

Also, our query plan cut off the last line, Execution time:.  In my hands, it takes around 40ms to scan 1,000,000 rows with a parallel seq scan.  This is instantaneous in human terms, but is still 500 times longer than the planning time which you are (perhaps) confusing it with.


mySQL foreign key and primary key Consider table A with columns id (primary key), name and table B with columns id, a_id (foreign key linked with table A id column), address. What will be the sequence of columns if the query is:
SELECT * FROM B INNER JOIN A ON B.a_id = A.id;

tables are unsorted by nature
so also the joined tables are unsorted
order them if you need it like you want.
So the columns are more complicated and more nested they become more complicated it gets.
Here are all possibilities  with two tables
the result differs, if you use ON Clause or the USING clause
In the USING clause, only RIGHT JOIN gives you another result

CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);

SELECT * FROM t1 NATURAL JOIN t2;

 j |  i |  k
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 INNER JOIN t2 USING (j);

 j |  i |  k
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 JOIN t2 USING (j);

 j |  i |  k
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 LEFT JOIN t2 USING (j);

 j |  i |  k
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 RiGHT JOIN t2 USING (j);

 j |  k |  i
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 LEFT JOIN t2 ON (t1.j = t2.j);

 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1

SELECT * FROM t1 RIGHT JOIN t2 ON (t1.j = t2.j);

 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1

SELECT * FROM t1 INNER JOIN t2 ON (t1.j = t2.j);

 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1

db<>fiddle here


csv file with fields having commas in them I have a CSV file with fields given as follows.

number1, string1, "string2, string3", number2, string4, "string5, string6"

where number's are numbers and string's are strings.  I am trying to read this as follows, I am ignoring first header line

LOAD DATA LOCAL
  INFILE 'filename.csv'
  INTO TABLE table_name
  FIELDS TERMINATED BY ','
  IGNORE 1 LINES;

But due to embedded commas in some fields, this command is giving causing to
overlap the fields. How would I correctly do this in MySQL?

You just need to add OPTIONALLY ENCLOSED BY '"':

LOAD DATA INFILE 'filename.csv' INTO TABLE table_name
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  IGNORE 1 LINES;

Documentation link.

File:

n1,s1,s23,n2,s4,s56
1,string1,"str2,str3",2,string4,"str5, str6"

Table and test:

mysql> create table tbl_name (
                               n1 integer,
                               s1 varchar(10),
                               s23 varchar(10),
                               n2 integer,
                               s4 varchar(10),
                               s56 varchar(10)
                               );
Query OK, 0 rows affected (0.06 sec)

mysql> LOAD DATA INFILE 'c:\\temp\\data.txt' INTO TABLE tbl_name
    ->   FIELDS TERMINATED BY ','
    ->   OPTIONALLY ENCLOSED BY '"'
    ->   IGNORE 1 LINES;
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0


How to restore MySQL database from Files Primary hard disk where MySQL (version 5.1) was installed is crashed and I had to re install entire OS. I have all the files including ibdata1 file. How do I go about recreating and attaching database to restore it to stage where it was crashed. Do i need log files or I can delete them ?. I am OK if some of the data I cannot restore. Important is I have all tables and most of the data possible.

install the new MySQL
create the db you want, let's say DB_NAME_RESTORE
go to /var/lib/DB_NAME_RESTORE and copy the data files there
chmod on the files if you have to


MS Access: error 3045 while linking to back-end In our environment multiple users each work with their own copied front-end, with tables linked to a single networked back-end (both files are .mdb).

Each user has permissions to read and write to the location of BE, and both FE and BE are set to "shared access".

Recently a single user started getting error: 3045 "Could Not Use Database; File Already in Use" while trying to open forms using linked data.

The same error appears while trying to re-link the tables using the manager. All other users can still normally work.

Tried restarting his workstation, removing local files and re-copying the FE and it didn't work.

There is no .ldb file left after all users turn off the FE, compacting after that and restarting the FE of the unlucky user didn't help either.

Said user is working on AC2010, but some others are working on AC2007 and AC2003.

Please help!

edit: BE is stored on Windows Server 2003.

Turns out the problematic user could create new files and delete them in the location, but did not have permission to delete files made by other users due to changes made by our IT.

As such the problem is now resolved.

Thanks to everyone who read and sorry for taking your time.


PostgreSQL priority between CTE and real table Is there some kind of priority ranks assigned to CTE and table name ? For instance, if I have a table called table_a in the public schema, and I create a CTE table named table_a using WITH, which table will be taken if I use table_a in the SELECT query ?

--as an example
CREATE TABLE table_a (
  id serial
);

WITH table_a AS (
     SELECT id
     FROM another_table
     )
SELECT *
FROM table_a --> which table is this ?
;

PostgreSQL searches the CTE namespace with scanNameSpaceForCTE as the very first thing it does in searchRangeTableForRel

if (!relation->schemaname)
{
    cte = scanNameSpaceForCTE(pstate, refname, &ctelevelsup);
    if (!cte)
        isenr = scanNameSpaceForENR(pstate, refname);
}

If there is no schema

check for a CTE
check for a "Ephemeral Named Relation"

This is similar to Variable Shadowing if there is no namespace, and what the spec otherwise demands.

Everything in PostgreSQL has a namespace, if you want to address the table as compared to the CTE, consider providing (qualifying) the namespace.

CREATE TABLE foo AS VALUES (0);

WITH foo AS ( VALUES (1) )
SELECT *
FROM ( VALUES (2) ) AS foo   -- inline virtual-table
UNION TABLE foo              -- CTE
UNION TABLE public.foo;      -- explicitly qualified the namespace;


Misleading ERROR: relation [“schema_name.table_name”] does not exist Should not it be ERROR: relation [“schema name”].["table_name"] does not exist instead? Lots of people use case sensitive relation names with dots and so forth. Eg:

t=# select * from public.t1;
ERROR:  relation "public.t1" does not exist
LINE 1: select * from public.t1;
                      ^
t=# create table "public.t1" (i int);
CREATE TABLE
t=# select * from public.t1;
ERROR:  relation "public.t1" does not exist
LINE 1: select * from public.t1;

I am used to getting this error so much that I did not notice that it is misleading. And yet today I hit it with relation.name and realized that error should not use double quotes like this. Is it intentional or should I address it as a bug?

You're creating a table with a dot identifier.

CREATE TABLE "public.t1" ( a int );
\d public.t1
Did not find any relation named "public.t1".

But, then if you quote it again there..

\d "public.t1"
   Table "public.public.t1"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |

SELECT table_catalog, table_schema, table_name
FROM information_schema.tables
WHERE table_name LIKE '%t1%';
 table_catalog | table_schema | table_name
---------------+--------------+------------
 test          | public       | public.t1

So instead use this if you want to be goofy,

SELECT * FROM "public.t1";

Or, simply never quote identifiers.

  Quoted identifiers can contain any character other than a double quote itself. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.

And, also periods.

This is agianst the convention of PostgreSQL. Unless you're doing something like an automated migration that needs to avoid collisions, I would higly suggest you avoid quoted identifiers entirely.

Always create things unquoted.
Always references things unquoted.

It's not just special characters either. Quoted identifiers make your schema caps-sensitive. This can make your life needlessly more difficult.


Index tuning question I am tuning some indexes and seeing some issues would like to take your advice

On 1 table there are 3 indexes

dbo.Address.IX_Address_ProfileId
[1 KEY] ProfileId {int 4}
Reads: 0 Writes:10,519

dbo.Address.IX_Address
[2 KEYS] ProfileId {int 4}, InstanceId {int 4}
Reads: 0 Writes:10,523

dbo.Address.IX_Address_profile_instance_addresstype
[3 KEYS] ProfileId {int 4}, InstanceId {int 4}, AddressType {int 4}
Reads: 149677 (53,247 seek) Writes:10,523

1- Do i really need the first 2 indexes, or should i drop them?

2-  there are queries  running that use  condition where profileid = xxxx and other use condition where profileid = xxxx and InstanceID=xxxxxx. Why the
    optimizer  choose the 3rd index not the 1st or 2nd?

Also i am running a query that get the Lock wait on each index. If i am getting these counts, what should i do to tune this index?

Row lock waits: 484; total duration: 59 minutes; avg duration: 7 seconds;
Page lock waits: 5; total duration: 11 seconds; avg duration: 2 seconds;
Lock escalation attempts: 36,949; Actual Escalations: 0.

table structure is

TABLE [dbo].[Address](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressType] [int] NULL,
[isPreferredAddress] [bit] NULL,
[StreetAddress1] [nvarchar](255) NULL,
[StreetAddress2] [nvarchar](255) NULL,
[City] [nvarchar](50) NULL,
[State_Id] [int] NOT NULL,
[Zip] [varchar](20) NULL,
[Country_Id] [int] NOT NULL,
[CurrentUntil] [date] NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[ProfileId] [int] NOT NULL,
[InstanceId] [int] NOT NULL,
[County_id] [int] NULL,
 CONSTRAINT [PK__Address__3214EC075E4BE276] PRIMARY KEY CLUSTERED
(
   [Id] ASC
 )

this is an example (this query created by hibernate so looks strange)

(@P0 bigint)select addresses0_.ProfileId as Profile15_109_1_
, addresses0_.Id as Id1_20_1_
, addresses0_.Id as Id1_20_0_
, addresses0_.AddressType as AddressT2_20_0_
, addresses0_.City as City3_20_0_
, addresses0_.Country_Id as Country_4_20_0_
, addresses0_.County_id as County_i5_20_0_
, addresses0_.CreatedDate as CreatedD6_20_0_
, addresses0_.CurrentUntil as CurrentU7_20_0_
, addresses0_.InstanceId as Instance8_20_0_
, addresses0_.isPreferredAddress as isPrefer9_20_0_
, addresses0_.ProfileId as Profile15_20_0_
, addresses0_.State_Id as State_I10_20_0_
, addresses0_.StreetAddress1 as StreetA11_20_0_
, addresses0_.StreetAddress2 as StreetA12_20_0_
, addresses0_.UpdatedDate as Updated13_20_0_
, addresses0_.Zip as Zip14_20_0_
from dbo.Address addresses0_
where addresses0_.ProfileId=@P0

(@P0 bigint,@P1 bigint)
select addressdmo0_.Id as Id1_20_
, addressdmo0_.AddressType as AddressT2_20_
, addressdmo0_.City as City3_20_
, addressdmo0_.Country_Id as Country_4_20_
, addressdmo0_.County_id as County_i5_20_
, addressdmo0_.CreatedDate as CreatedD6_20_
, addressdmo0_.CurrentUntil as CurrentU7_20_
, addressdmo0_.InstanceId as Instance8_20_
, addressdmo0_.isPreferredAddress as isPrefer9_20_
, addressdmo0_.ProfileId as Profile15_20_
, addressdmo0_.State_Id as State_I10_20_
, addressdmo0_.StreetAddress1 as StreetA11_20_
, addressdmo0_.StreetAddress2 as StreetA12_20_
, addressdmo0_.UpdatedDate as Updated13_20_
, addressdmo0_.Zip as Zip14_20_
from dbo.Address addressdmo0_
left outer join dbo.Profile profiledmo1_
on addressdmo0_.ProfileId=profiledmo1_.Id
where profiledmo1_.Id=@P0 and addressdmo0_.InstanceId=@P1

Answer to question 1:

From what you posted you can drop the first two indexes as the third will cover all of the queries you mention and the query optimizer see that as well when it builds the query plan (based on the plan you posted.)

Answer to question 2:

It's always using the third index because it has more data already in the index with the two additional index keys (InstanceId and AddressType). This keeps SQL from needing to pull InstanceId and AddressType from the primary key (the key lookup part of the execution plan) to satisfy the query.

What I would suggest is drop the first two indexes and rebuild the third with include columns to cover the other columns being requested in the query

Create index IX_Address_profile_instance_addresstype
on dbo.address  (ProfileId, InstanceId, AddressType)
include(<put in the remaining columns comma delimited>)
with (drop_existing=on,sort_in_tempdb=on)

This should help with the queries and should remove the key lookup from the query plan.

See if the locks drop off after these changes and if they don't we can dig in a bit deeper.


Would generate a new table help reducing the overall size I am relatively new to the database design or just database in general so I apologize first if any term or format I use is not super precise.

Scenario: Say I have a People table that contains PersonName, with other attributes say profession, as primary key and other non-primary attributes. And for whatever the reason I am only going to have two distinct people, Databaseguy1 and Databaseguy2, in the table. Normally I would save the PersonName as a char type with max size up to say 12.

So I am wondering that maybe in the People table I can have a PersonID as my primary key and represent it with bit (the smallest data type in SQL server, correct me if I am wrong) and create another table with PersonID(pk) and PersonName.

I believe that by doing so, I only need to record the person as 0 or 1 in the first table. It wont affect the overall number of rows but will save space in each row since now one of the fields is represented with bit data type rather than a variable char data type. And in the new table I am just going to have two rows, 0 to Databaseguy1 and 1 to Databaseguy2. Is this a valid argument or is there a better way to accomplish it?  Will this affect the performance such as retrieving the data since now it has to go to a separate table to fetch the actual name of a person?

Other questions: will the relationship between the second table the People table be one to many relationship?

You would not actually save any space using BIT rahter than TINYINT (and would also give yourself more options to add other entities). This is because 8 or less bits are stored in a byte. If you were using multiple bit entries this could save you space, but it's ultimately very limiting. A TINYINT would use the same 1 byte of storage, but would allow you to store up to 256 different entity values.

Saying this though, from a practicality standpoint, unless you are looking at ridiculously high volumes of data the space savings are not going to be that significant regardless of using TINYINT or SMALLINT (~65k values & 2 bytes).

Using normalization for this sort of thing is very common, and helps prevent excessive bloat (after all in your second table you would store 1 bytes instead of twelve to represent the person entity). Don't forget to add foreign key references.