What will be with MySQL cluster if network problems between nodes? I have 2 datacenters, each DC has MGT, SQL and Data nodes.

How will MySQL NDB Cluster work if there are network problems between 2 DC?

In this case 2 DC will work well, but they don't know anything about other node.

Who ll be the main and who ll be the reserve, how will they decide it?

Is there a quorum server for MySQL Cluster?

It will be decided by an arbitrator node which is normally a MGM node.
It would be advisable to put this arbitrator node in a 3rd DC.

In the case of 2 DCs the DC where the arbitrator resides will survive
if the network between the DCs is broken. With a 3rd DC with the arbitrator
node, the one to first ask for arbitration will fail.


Official PostgreSQL Capitalization conventions Is there an official PostreSQL convention regarding capitalization in DB, Table and field names?

The examples on the official site suggest a lowercase and _ word separation, and I wonder whether this policy is official.

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

I'm going to basically mirror Verace's comments and state this, making it semi-official:

There is no one best practice that will cover every circumstance. What follows makes the following assumptions (and what to do if you haven't done this):

You have already discussed this with your team (people working by themselves often just have to make up their mind)
There is no formalized style definition for SQL already on your team (Otherwise you wouldn't be asking us)
There is no formalized style definition for any code (Follow the same basic conventions already established for other languages and formalize a style)

So the rest of this is somewhat opinionated but based on experience

When it comes to table names

You should go for singular entity names (this makes documentation easier)
You should use Pascal Case here

When it comes to field names

Use camelCase on your field names
Use short singular names unless the definition definitely makes sense as a plural (it almost never does)

When it comes to your own function or stored procedure names

Use underscore_separation
Use field naming for the parameterization

When it comes to built in database functions or language names (eg SELECT)

Unless there's a requirement for it to be capitalized a certain way, use ALL CAPS
Know the APIs for your language to know what's reasonable or required

When it comes to spacing

A lot of folks use column alignment for keywords and indentation for things which are not keywords
A lot of folks use commas at the beginning of the row when the fields are separated on each line (This makes it easier to comment out a specific field from a selection list)
Never use spaces as part of the names of things, not even for return value headers.

When it comes to punctuation

Parentheses - USE THEM. They're FREE. I promise.
Semicolons - USE THEM. They're not going to break you. They force you to think out your code. And they're good hygiene.
Carriage Returns - Once again, they're free ;-) And make your code readable.

You should also recognize that while I'm trying to help you apply a generic style guide, that the community for Postgres generally doesn't use camelCase or PascalCase but instead uses underscore_separation. The really important bit is to ensure that you establish and use a specific style everywhere to be consistent.


Taking MySQL dump from another machine I have a MySQL db on Machine A and I want to access the mysqldump from machine B.

Both machines are running on Linux and I also have the credentials of both, but I can't access the mysqldump. What can I try?

In General if you have the credentials for accessing MySql from Machine B.

You should have MySQL user name , host name and Password.Then You can write the Command for taking backup

On Machine B Write

mysqldump -h Your_host_name -u user_name -p password --all-databases > backup.sql

If You Face issues there may be some network issue try some things as

Use ping with ip address to check that DNS isn't broken. eg. ping Machine A
Use mysql client to connect from B to A. eg mysql -u user -pPASS --host=Host_Name --port=3306 (substitute whatever port you are connecting to the master on)


why would command text not execute at all? in my report i have::

<CommandText>SELECT

    column1,
        column2,
        'poop'
from mytable
</CommandText>

i am using sql server profiler to see exactly what statement is being set.

i have set only two filters:

databaseName

yet after running the report, no statement gets intercepted.

i suspect that because i am a beginner at SSRS, i am missing something crucial here.

for what reason would commandtext not be executed at all?

i did follow this question, to make sure that i am using sql profiler correctly, and indeed, i am: https://stackoverflow.com/questions/9107383/sql-server-profiler-capture-calls-to-your-databases-stored-procs-during-ssrs

another bit of important information is although the chart shows no data:

i actually am indeed showing data when i run the commandtext from ssms!

Based on past interactions, I'm pretty sure that you're at least on SQL Server 2008R2. You could try filtering on the output of an Extended Event session. Here's a basic one to get you started.

  CREATE EVENT SESSION query_check ON SERVER
    ADD EVENT sqlserver.sql_statement_starting(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sp_statement_starting(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION (sqlserver.database_id, sqlserver.sql_text))
    ADD TARGET package0.asynchronous_file_target(SET filename=N'D:\temp\monitor.xel',max_file_size=(5),max_rollover_files=(4))
    --,ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON);

You can start it like this:

ALTER EVENT SESSION query_check ON SERVER STATE=START;

Run your query a few times and then stop the event session:

ALTER EVENT SESSION query_check ON SERVER STATE=STOP;

Then you can query it like this:

SELECT
    fired_event = event_data.value('(/event/@name)[1]','nvarchar(25)'),
    fired_event_time = event_data.value('(/event/@timestamp)[1]','datetime2(0)'),
    event_database_id = event_data.value('(/event/action[@name=''database_id''])[1]','int'),
    event_sql_text = event_data.value('(/event/action[@name=''sql_text''])[1]','nvarchar(max)')
FROM (
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file(N'D:\temp\monitor*.xel',
                                            N'D:\temp\monitor*.xem',
                                            NULL, NULL)
    ) events

You can capture more data, you can choose what you'd like from here:

SELECT p.name AS package_name,
       o.name AS action_name,
       o.description
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects AS o
     ON p.guid = o.package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
  AND o.object_type = 'action'

So if you wanted to capture login information, you'd modify your ACTIONs from

ACTION (sqlserver.database_id, sqlserver.sql_text)),

to

ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.username)),

I'd actually recommend against filtering on the query text because it's expensive. Instead, I'd try to find the correct username. If you don't think this will work, then I'd filter based on the query text of the result XML file. In fact, only in SQL Server 2012 can you filter on the statement text anyway (it's called a predicate).

If you wanted to perhaps capture data from a specific username you'd add WHERE to each EVENT that you'd like to filter.

ADD EVENT sqlserver.sql_statement_starting(
    ACTION (sqlserver.database_id, sqlserver.sql_text)
    WHERE (sqlserver.username = N'{{ your username here }}')
)


Can't create trigger in MySQL to make a simple audit I have a table sales:

Create table sales ( sales_id BIGINT PRIMARY KEY,
                     customer_id BIGINT,
                     sales_amount DOUBLE ) ENGINE=INNODB;

Then to audit this:

Create table audit_log ( log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
                         sales_id BIGINT,
                         previous_amount DOUBLE,
                         new_amount DOUBLE ) ENGINE=INNODB;

And I've tried for many, many ways of make this trigger:

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount)//
DELIMITER ;

The output I've for this is: (nothing) I other words the prompt disapeear, I change for this:

I tried with semicolon after insert but the result is the same the prompt disapeear, I have to quit with Ctrl+C

I'm using MySQL 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)

; at the end of INSERT statement is lost.

Use

CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log ( sales_id,     previous_amount,  new_amount)
VALUES                ( NEW.sales_id, OLD.sales_amount, NEW.sales_amount) ;

DELIMITER reassign is excess for single-statement trigger.


How to shutdown postgres through psql? The recommended way to shutdown postgres is to send a signal representing the different shutdown modes (fast, smart, immediate) or using init managers (initd, systemd, etc.) or their equivalent on non-Linux OS. In a containerized environment it's sometimes more convenient to gain access through psql than docker exec and thus more convenient to stop the server through psql. Is there a way to do that (given that the privileges have been granted)?

The solution can be hacky since this will mostly be used to stop a postgres to delete a PostgreSQL data directory in a development environment.

The documentation tells you to send a signal to the master postgres process, or to let pg_ctl do this.

In SQL, you can extract the PID of the master process from pg_read_file('postmaster.pid'), but pg_cancel_backend() does not accept this PID.

However, you should be able to execute these commands with COPY (depending on what rights the postgres OS user has):

COPY (SELECT 1) TO PROGRAM 'kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`';

COPY (SELECT 1) TO PROGRAM 'pg_ctl -D /usr/local/pgsql/data stop';


Functional dependencies and candidate key example Hi there I'm still learning for my examination and I got this exercise:

A table is given:

F-Name,           F-Nation,    Team-Name,   Team-Nation
-------------------------------------------------------
Lance Armstrong,     USA,   Discovery Channel,   USA
Jose Luis Rubiera,   ESP,   Discovery Channel,   USA
Jan Ullrich,         GER,   Team Telekom,        GER
Andreas Klöden,      GER,   Team Telekom,        GER
Davide Rebellin,     IT,    Team Gerolsteiner,   GER

a) Which are the candidate keys?

b) Which non-trivial functional dependencies existing?

c) Is the table in 2NF (second normal form)? If not modify the table to be in 2NF.

d) Is the table in 3NF (third normal form)? If not modify the table to be in 3NF.

a) I would say that the candidate keys are (F-Name, Team-Name) or (F-Name, Team-Nation)

b) The dependencies are:

Team-Name -> Team-Nation
F-Name -> F-Nation

c) no it is not in 2NF because Team-Nation is not dependant from the F-Name which is part of the key candidate.

Table after normalization to 2NF:

Table driver

F-Name, F-Nation, Team-Name

Table teams

Team-Name, Team-Nation

d) I couldn't find any transitive relations so I guess the solution of c) would be in 3NF also am I right?

Ok so I can say on a) there are two solutions. If F-Name is Unique the candidate key is F-Name otherwise F-Name, Team-Name

b) The dependencies are:

F-Name -> F-Nation, Team-Name
Team-Name -> Team-Nation

and my solutions on c) and d) are right?


Dynamic execution query produces error when called from the procedure, but works when executed manually create procedure createTable(@tableName varchar(25), @columnName varchar(25), @columnType varchar(25))
as
    begin

    declare @sqlQuery as varchar(MAX)
    set @sqlQuery = 'create table ' + @tableName + '(' + @columnName + ' ' + @columnType + ' primary key)'

    print (@sqlQuery)
    exec @sqlQuery

    end
go

Above code produces below output and error:
create table proba2(ceva int primary key)
Msg 2812, Level 16, State 62, Procedure createTable, Line 9 [Batch Start Line 13]
Could not find stored procedure 'create table proba2(ceva int primary key)'.

Strangely, if I copy the first line in the output(which is the printed query itself) and execute it, it works; the table will be created. Nonetheless, the exec in the stored procedure does not. As far as I know, the exec call of the procedure should work. It should even work without specifying 'primary key' in the end of the string.

Replacing exec @sqlQuery with exec (@sqlQuery) must work. However, sp_executesql is recommended in your case as it has more flexibility.
Following is a typical example of sp_executesql:
Declare   @DatabaseName nvarchar(128) = 'MyDB'
        , @schema_name nvarchar(128) = 'dbo'
        , @table_name nvarchar(128) = 'MyTbl'
        , @column_name nvarchar(128) = 'MyTblColumn'
        , @ReadColumnInfo nvarchar(max)
        , @datatypename varchar(128);

select @ReadColumnInfo = 'Use ' + QUOTENAME(@DatabaseName) + '; ';
set @ReadColumnInfo =  @ReadColumnInfo + '
SELECT @datatype = TYPE_NAME(c.user_type_id)
FROM sys.objects AS o
JOIN sys.columns AS c  ON o.object_id = c.object_id
WHERE o.name = @TblName and o.schema_id = SCHEMA_ID (@scmName) and c.name = @columnName';

exec sp_executesql
          @ReadColumnInfo -- Statement
        , N'@TblName varchar(128), @columnName varchar(128), @scmName varchar(128), @datatype varchar(128) OUTPUT' --- Params within Statement
        , @TblName = @table_name, @columnName = @column_name, @scmName = @schema_name, @datatype = @datatypename OUTPUT;  --- Param Values within Statement
select @datatypename;


PostgreSQL user can not connect to server after changing password I've met this with 4 roles I created:
After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more.
pgAdmin III show error message:

An error has occurred:

Error connecting to the server: FATAL:  password authentication failed for user "sam"
FATAL:  password authentication failed for user "sam"

My system: Postgresql 9.2 on Ubuntu 12.04

Is there any way to fix this?

(1): login with account postgres, right click user in Login Roles, go to tab 'Definition' and enter password

It's possible that you're being bitten by this PgAdmin bug (changelog):

  2012-11-28 AV  1.16.1  Date picker controls returns a full timestamp
  by
                         default, which can cause inadvertent date changes
                         on jobs and role validty dates. Ignore the time part.

This bug has been seen to set password expiry dates far in the past, such as 1/1/1970. In this case the error message when trying to connect is no different than with a wrong password.

You can check these expiry dates with:

SELECT usename,valuntil FROM pg_user;

and if they're wrong, reset them with:

ALTER USER username VALID UNTIL 'infinity';

and upgrade pgAdmin.


What are the approaches for refreshing materialized views in Oracle, when underlying tables are updated often? I am a web developer, maintaining a web app that tracks orders, customers, products, etc, that my client uses internally. I use Oracle 12c, hosted on AWS RDS. My client has just switched some other systems, so we are at a point where data structures have changed, and I am using a new schema in Oracle to store new data in the new structures.

In order that the web app doesn't have to be re-engineered to work with new data structures, a decision was made to implement materialized views in Oracle that union the new data from the new schema (manipulated into the "legacy structure") together with the legacy data.

So now I have to deal with refreshing these materialized views so that the web app constantly has access to the latest data. Ideally the relevant materialized view(s) would be refreshed whenever I receive a new record into the new schema, but during working hours, I receive new data every few seconds maybe. A compromise is OK - if the materialized views are stale by a few minutes (maybe 5 or (less ideally) 10 minutes), that might be an acceptable situation.

My question is, what approach should I have for refreshing these materialized views? I don't want to overload Oracle with constant refreshes, and the web app should provide users with a good user experience when reading/writing data from/to Oracle. I'm far from being an Oracle/DB expert, so I am not really sure what options there are. I guess I could just have a cron job that runs every 5 minutes or something to refresh stale materialized views one by one, but I am wondering if this approach is a bit naive.

In reality, I am dealing with 14 materialized views (for now), and in my testing, some of them take up to 2.5 minutes to do a complete refresh.

If you can, use fast refresh instead of complete refresh - it will save you a lot of compute time. you'll have to create materialized view logs on the source tables, but if you run the refreshes frequently you will keep them small and only have to deal with the incremental updates. If you're using 12.2 you can also use "real-time" materialized views, as illustrated here: https://www.youtube.com/watch?v=k5nhZfyPHdY


Conditional WHERE Clause in a Table Valued Function I have a table valued function

    CREATE FUNCTION [dbo].[DateRange]
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25),
        @Device varchar(25)
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT *
        FROM MyTable
        WHERE Date < @EndDate AND
              Date > @StartDate AND
              Location = @Location AND
              Device = @Device

    );

Now I would like to give the user the option to use NULL as location parameter and or device parameter so he/she gets all data independent of the location and device back

What would be the most elegant and efficient way to do this ?

Like so :

CREATE FUNCTION [dbo].[DateRange]
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT *
        FROM MyTable
        WHERE
          Date < @EndDate
          AND Date > @StartDate
          AND ( @Location IS NULL OR (@Location IS NOT NULL AND Location = @Location))
          AND ( @Device IS NULL OR (@Device IS NOT NULL AND Device = @Device ))

    )

IF the device and location columns can actually be NULL, you might want to consider this option though:

CREATE FUNCTION [dbo].[DateRange]
    (
        @StartDate date,
        @EndDate date,
        @Location varchar(25) = NULL,
        @Device varchar(25) = NULL
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT *
        FROM MyTable
        WHERE
          Date < @EndDate
          AND Date > @StartDate
          AND ( @Location IS NULL OR Location = @Location)
          AND ( @Device IS NULL OR Device = @Device )

    )

This would work if you're using a UD table type for the location, as you asked about in your comment. Just make sure the input parameter is Read Only.
However, you cannot pass NULL into a table type parameter, you have to pass in an empty parameter object of that type :

CREATE TYPE LocationsType AS TABLE
( LocationName VARCHAR(25));

CREATE TABLE MyTable (Date datetime, Location varchar(25), Device varchar(25));

CREATE FUNCTION [dbo].[DateRange]
    (
        @StartDate date,
        @EndDate date,
        @Location LocationsType READONLY,
        @Device varchar(25) = NULL
    )
    RETURNS TABLE
    AS
    RETURN
    (
        SELECT *
        FROM MyTable
        WHERE
          Date < @EndDate
          AND Date > @StartDate
          AND ( ((select count(*) FROM @Location) = 0) OR Location IN (select LocationName from @Location))
          AND ( @Device IS NULL OR Device = @Device )

    )

Call it:

declare @l LocationsType

select *
from [dbo].[DateRange] (getDate(), GetDate(), @l,NULL)


How to map a drive in a SQL Server script I am trying to execute below script to Network a Drive

use master
go
exec sp_configure 'show advanced options', 1;
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1;
go
reconfigure
go

EXEC XP_CMDSHELL 'net use Z: \\RemoteServerName\SharedName'
EXEC XP_CMDSHELL 'Dir Z:'

Is there any script that  I can use to connect the network drive in long term

I agree that you could use \\\server\share\Whatever without needing to map a drive at all (so UNC path only) but the security context running the T-SQL will need appropriate permissions to the SHARE and the NTFS of the full path to ensure it can do whatever commands you are running.

You could use PUSHD and POPD as:

PUSHD
\\server\share
Command \subfolder1\*.txt
POPD

You could also use:

IF NOT EXIST Z: NET USE Z: \\server\share
command z:\folder\files.txt

Or perhaps something like the below with plugging in your variables:

DECLARE @folder AS VARCHAR(1000) = '\\server\share\folder1\folder2\finalfolder\'

DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)

-- create global temp table
IF OBJECT_ID('tempdb..##globalname') IS NOT NULL
DROP TABLE ##globalname

CREATE TABLE ##globalname (
StepResultID VARCHAR(50)
,StartTime VARCHAR(50)
,StopTime VARCHAR(50)
,ReturnCode INT
)

INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command


How to add deleted user in sql server? I have set up sql server 2008 in windows server 2008 r2 and it works fine. However, I have deleted the my user from logins in the sql server accidentally and now I cannot find a way to log in to the sql server and I believe I did not set up any other user well. Think of it as I just installed and removed the user.

Is there anyway that I can add an account?

You can try below alternatives:

If you installed SQL Server then you will be having "sa" - if mixed mode authentication is enabled. You can use "sa" account to create a login and map that login to a database.
Alternatively, you can use "DAC" (if you have enabled it as a part of post installation step).
By default, BUILTIN\Administrators has access to sql server 2008 instance. If you are part of BUILTIN\Administrators group then you will have access.
Last resort : Recover access to a SQL Server instance by Aaron Bertrand wherein he shows how to use PsExec to get into sql server.

Once you gain access to sql server, then create a login and map that login to a database.

Make sure to NOT delete it again :-)


database connection time expired issue I have a server machine and four client machine for database connectivity.

I installed sqlexpress 2008 in server and one machine and all remaining systems, they have connected the database through the EMS (SQL Manager 2008 for SQL Server ).

The issue is that when I connect the database from that m/c which has sqlexpress 2008 then it gives "Timeout Expired"

But all remaining systems connect to server easily.

Why it gives an error for that particular system.

Some things I'd check:

When you connect from SQL Server Management Studio 2008 on the box with Express, are you using the right server name (and possibly port number)?
Have you got Windows Firewall enabled on any of the machines?
Can you ping the SQL Server?  Do you get the same IP address for it that you get on the other machines?
Do you have any aliases set up in SQL Server Configuration Manager?
Have you disabled any client protocols in SSCM?


SQL Server and ASP.NET - Backup Log database with Application Pool in Always Running I have a database on SQL Server Express used for 3 Asp.net applications. The Recovery Model is Full.
Every day I execute a backup database and log with this code
BACKUP DATABASE @databaseName TO DISK = @file WITH INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
BACKUP LOG @databaseName TO DISK = @fileLog WITH NOFORMAT, NOINIT,  NAME = N'Database-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

When the Start Mode of Application Pools is on OnDemand the LDF is reduced every day; when I use AlwaysRunning the log is not reduced.
I want to use application pools with always running. Can I use a shrink to reduce log size? Even if users are using websites?
ALTER DATABASE [DatiMachinaWeb] SET RECOVERY SIMPLE
DBCC SHRINKFILE(DatiMachinaWeb_log)
ALTER DATABASE [DatiMachinaWeb] SET RECOVERY FULL

Am I wrong? How can I solve it? Thanks

Can I use a shrink to reduce log size? Even if users are using websites?

No, users will be blocked while the shrink operation is occurring.  Plus, you'll have to do it over and over again unless you address the root cause of the growth.

For a really thorough treatment of why the log file keeps growing, see Why Does the Transaction Log Keep Growing or Run Out of Space?

When your websites are always running, you're producing more log records than the size of the log file in a single day, resulting in the file growth.
You have a few options:

Leave the database in the SIMPLE recovery model

With this approach, you don't take log backups at all.  You take the full backup once per day, as you always do.  If there were a crash or some other need to recover from a backup, you could lose up to one day of data.
ALTER DATABASE [DatiMachinaWeb]
SET RECOVERY SIMPLE;

Take log backups more often

You can take as many log backups as you like between full backups.  Taking a log backup will free up space in the log (referred to as "truncation") and prevent the log file from growing.  This is really the best, and most common, option for dealing with log file growth.

Permanently increase the log file size to accommodate the once-per-day log backups

The default log file size in SQL Server 2019 is 8 MB.  If your system does more than 8 MB worth of transactions in a day, that log file will grow (by default, it will grow by 64 MB).
Assuming the amount of transactions your system does is somewhat consistent from day to day, the growth of the log file will eventually normalize.  After the first growth event, the log will be 72 MB - if you don't do more than 72 MB of transactions in a day, the log file should stay that size.

Lower likelihood: investigate long-running transactions

It's possible that your websites are causing the log file to not be truncated when they are in this "always running" mode.  Maybe they start a transaction on startup, and don't commit or rollback until they shut down.
If the log file continues to grow, even with frequent log backups or a sufficiently large log file, you can run this query to see what is preventing log file truncation:
SELECT
    d.[name],
    d.log_reuse_wait,
    d.log_reuse_wait_desc
FROM sys.databases d
WHERE
    d.[name] = 'DatiMachinaWeb';


TEMP Tablespace Removal Oracle Version: 12.1.0.2

We have a discrepancy between our PROD and DR TEMP tablespace data files.

PROD = 30G + 30G + 30G data files
DR   = 30G + 30G + 30G + 21G + 2G + 1G + 15G data files

I want both my environments to only have the 30 GB TEMP tablespace data files only (In total 90 GB). I need to remove the additional 21G +2G +1G +15G tablespace data files in the DR environment.

It is critically important for me to know who added the other non standard tablespaces. Do we have a query to find the culprit?

Also: How safe is it to remove the other non-standard TEMP tablespace data files? Is there a safe way or an Oracle defined process to do that?

The alert log will show what user added a data file to your DR tablespace, but most likely they logged on as SYS / AS SYSDBA instead of a user that has SYSDBA privileges (one more reason to assign roles to users, especially DBAs.  The alert log is usually in $ORACLE_BASE/diag/${ORACLE_UNIQ_NAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID} (I may have those ORACLE_UNIQ_NAME and ORACLE_SID backwards).

To clean up the DR TEMP tablespace, just create a new TEMP tablespace TEMP2, alter the database to use the new temporaray tablespace, drop the old TEMP tablespace, then rename the new temp tablespace TEMP2 to TEMP:

create temporary tablespace temp2
  tempfile '/some_dir_path/temp2_01.dbf' size 30g,
  tempfile '/some_dir_path/temp2_02.dbf' size 30g,
  tempfile '/some_dir_path/temp2_03.dbf' size 30g
;

alter database default temporary tablespace temp2;

drop tablespace temp including contents and datafiles;

alter tablespace temp2 rename to temp;


Quickly clone a database in SQL Server Management Studio For testing purposes, I often have to clone databases (onto the same server). Currently I do this by creating a backup and restoring it as a new database.

I would love to have such a functionality available as a simple right-click in SQL Server Management Studio (SSMS): right-click a database, choose "Clone" and be done.

Unfortunately I found no such or a similar function in SSMS.

My question:

Does such a "quick clone" function exist or could I implement one by myself (or maybe through some add-in?)

Update 1:

I've found a SO question and answer on using the Copy wizard.

This looks promising but takes a lot of wizard steps. Maybe it is possible to do this whole thing with a single click?

I just took a look at my ssms. Copy is in SSMS 2012 and 2014 under tasks. I haven't tried it but it is there. What ssms are you using?

SSMS 64 bit: http://download.microsoft.com/download/5/2/9/529FEF7B-2EFB-439E-A2D1-A1533227CD69/SQLManagementStudio_x64_ENU.exe


Table structure for entity versioning For an assignment I have to build an application that is able to store tests. Every test has to be validated by a a different user when created or updated. When it has been validated by an user, the test gets an expiry date of the last time it was validated plus 2 years.

Everytime a test gets updated, it creates a new version: v1, v2, v3, etc. This versioning information is stored in the test_versions table.

The tests itself are all stored in the same tests table with a foreign key to the test_versions table (version_id)table. Thetest` table consists of multiple many-to-many relations with joining-tables. For the sake of simplicity, I didn't include these in my table definition.

create table tests
(
    id                  int auto_increment   primary key,
    name                varchar(255)         not null,
    description         varchar(2048)        not null,
    version_id          int                  not null,
    is_active           tinyint(1) default 1 null comment,
    created_at          timestamp            null,
    updated_at          timestamp            null
)
    collate = utf8_unicode_ci;

Every new test is made with a new revision. The tests table has an version_id column. This is referring to the test_versions table which has the following columns:

version_id
description
original_version_id (The test where this version is made off)
tag (v1, v2, v3, etc.)
is_current_version (An indicator if it's the test's current version)
expiry_date (The expiry date of the current version, after this date, the test would not be showing).

It's possible to have unlimited versions for every test.

Now the problem is that when a new test is created, a new version should also be created in the test_versions table. This is not possible in the current structure since every new record in the test_versions table needs an test_id.

Dropping, or not working with foreign keys would not be an option.

Questions

How can I create the table structure in a way that I can still have unlimited versions without having the foreign key problems? How does the StackExchange site do it for example?
In my structure, every new test needs a version. Can this be done any better or is it efficient to split data over multiple tables?
Could there be another table structure for not working with these FK's in both directions?
Is it efficiënt to store more versions in the same tests table? Is there any other/better way?

My preference is to handle this with a stored procedure rather than a trigger, but that's an option if you have a lot of legacy code running unwrapped INSERTs.

DELIMITER //
CREATE PROCEDURE CreateTest (Name VARCHAR(255), Description VARCHAR(2048))
    BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
        ROLLBACK;
        END;

    START TRANSACTION;

    INSERT INTO test (name, description, version_id) VALUES (Name, Description, 0);
    DECLARE TestID INT;
    SET TestID = LAST_INSERT_ID();

    INSERT INTO test_versions (test_id) VALUES (TestID);
    DECLARE TestVersionID INT;
    SET TestVersionID = LAST_INSERT_ID();

    UPDATE test SET version_id = TestVersionID WHERE id = TestID;

    COMMIT;
    END//
DELIMITER ;

Apologies if I screwed up the MySQL syntax, I've been working in MS SQL 90% of the time for the past ten years.

Note that this code temporarily sets the value of version_id to zero, to satisfy the FK.  You'd need a dummy record in test_versions with such an ID.  However, the field is updated before the transaction commits, so no queries should ever see this value.

Edit: if you are really concerned about the INSERT performance of this sproc, you could simplify it by giving test_version a composite PK on test_id and version_id, where version_id is a sequence (starting at 1) for each test.  You would need to retrieve only one AUTO_INCREMENT value:

DELIMITER //
CREATE PROCEDURE CreateTest (Name VARCHAR(255), Description VARCHAR(2048))
    BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
        ROLLBACK;
        END;

    START TRANSACTION;

    INSERT INTO test (name, description, version_id) VALUES (Name, Description, 1);
    DECLARE TestID INT;
    SET TestID = LAST_INSERT_ID();

    INSERT INTO test_versions (test_id, version_id) VALUES (TestID, 1);

    COMMIT;
    END//
DELIMITER ;

As you can see, version_id can be assumed to be 1 for the first record, so there's no need to UPDATE test.  You also don't need a dummy record #0 in test_version.  However, the broader PK would make indices on test_version slightly bigger.  Using a narrow integer for version_id would help.


Importing Multipe Trace Files To A SQL Server Table I am in the process of importing 200+ trace files (which are massive), and my current approach is to perform a loop and insert the trace data (see the below script).  I looked around to see if there was a faster way to do this, whether through SSIS or C# and it appears that they still call the below function, similar to the script below this.

Anyone have any other methods that they use to import multiple traces?  Don't get me wrong, the below code works, but I'm curious if there's something faster that I'm not considering.

DECLARE @start INT = 1, @stop INT = 223, @sql NVARCHAR(MAX), @path VARCHAR(1000)

WHILE @start <= @stop
BEGIN

       SET @path = 'N:\Traces\TraceFile_' + CAST(@start AS VARCHAR(5)) + '.trc'

       SET @sql = 'INSERT INTO SavedTraces
                   SELECT *
                   FROM ::fn_trace_gettable(''' + @path + ''', default)
                    '

       EXECUTE sp_executesql @sql

       SET @start = @start + 1
       SET @path = ''
       SET @sql = ''

END

Data Notes: 490MB (~.5G), which holds 11,700,000+ rows, requires 13:11 minutes to import.

It will be entirely dependend on where the bottleneck is. Which requires you to measure, and approach this just like any other performance problem.

If the bottleneck is the INSERT performance then you should make sure you achieve minimal logging. The The Data Loading Performance Guide is, of course, mandatory reading.
If the bottleneck is reading the traces fn_trace_gettable then... you have a problem. It could be a slow source disk  (make sure the access is serialized and the head does not get randomized) or fn_trace_gettable itself may be slow (is not, is the disk, but you should measure...). Remember that even if  reading the source TRC files is slow, reading many of them in parallel from non-overlapping input access paths (ie. separate disks) may achieve much better throughput.
If the bottleneck is... hmmm... I don't see what else could be the bottleneck. But of course, you should measure, get perf counters, identify where the time goes.

Ultimately your problem is rather esoteric so the gist of it is that you need to measure and adapt to your findings.


Deleting data from a large table with replication I need to delete all the rows from a given table. The table contains millions or records. The master database is being replicated to several slaves and I wish to do that without creating a replication lag or impacting the performance.

After some research, I tried dropping the table. That took quite a few long seconds, during which time my master DB was locked out.

I know I can gradually delete in smaller batches, just wondering is there's a quicker way.

Thanks,
Z

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table.

EXAMPLE

Suppose the table is called mydb.mytable. Do it like this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

Doing it this way let's mytable become empty immediately and does not get locked during the deletion of the data. Now, this should go quick on the Master and should replicate. The last line DROP TABLE mytable_old; might take the longest. If that concerns you, then run this on both Master and Slave (based on the answer from Jynus)

SET sql_log_bin = 0;
USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;
SET sql_log_bin = 1;

GIVE IT A TRY !!!

CAVEATS

As for deleting data in small chunks, that would not be a good idea if you need to table immediately available after the table data is deleted. Why ?

Doing DELETE FROM mytable.mytable; is a single transaction. All the rows would be prepared for rollback in the InnoDB Architecture. After the DELETE is complete, all that MVCC info has to be discarded. That explains why it takes so long.
Deleting in small chunks would just create more transactions and smaller rollbacks. Notwithstanding, it still creates the same amount of rollback information and work. It may allow you to monitor how much data is left to delete.
Please don't run DELETE FROM mydb.mytable LIMIT 1000;. Using LIMIT on a DELETE without a WHERE clause is not replication safe.


Performance of a View with Hundreds of Left Joins I have a table with 100+ columns, each containing a numeric value that maps to a string value in another table. When trying to create a view for the table that contains these string values, I'm getting a significant performance hit from all the joins. Given that table structures can't be changed, is there a better way to map all these strings in from the other table?

--MyTable--
Id  Name    attr1   attr2   attr3   ... attr200
1   First   1       2       4           2
2   Second  3       3       5           1

--StringMap--
AttrName    Value   Label
attr1       1       Small
attr1       2       Medium
attr1       3       Large
attr2       1       A
attr2       2       B
attr2       3       C
...
attr200     1       Purple
attr200     2       Blue
attr200     3       Green

select
  t.Name,
  s1.Label as attr1name,
  s2.Label as attr2name,
  s3.Label as attr3name,
  ...
  s200.Label as attr200name
from MyTable t
  left join StringMap s1 on s1.AttrName = 'attr1' and s1.Value = t.attr1
  left join StringMap s2 on s2.AttrName = 'attr2' and s2.Value = t.attr2
  left join StringMap s3 on s3.AttrName = 'attr3' and s3.Value = t.attr3
  ...
  left join StringMap s200 on s200.AttrName = 'attr200' and s200.Value = t.attr200

You could try UnPivoting your data and then Pivoting the results:

Tables:

Create Table #tbl
(
Id Int,
[Name] VarChar(20),
attr1 Int,
attr2 Int,
attr3 Int,
attr200 Int
)
Insert Into #tbl Values
(1,'First',1,2,4,2),
(2,'Second',3,3,5,1)

Create Table #StringMap
(
AttrName VarChar(25),
[Value] Int,
[Label] VarChar(25)
)
Insert Into #StringMap Values
('attr1',1,'Small'),
('attr1',2,'Medium'),
('attr1',3,'Large'),
('attr2',1,'A'),
('attr2',2,'B'),
('attr2',3,'C'),
('attr200',1,'Purple'),
('attr200',2,'Blue'),
('attr200',3,'Green')

Query uses CTEs to UnPivot your table to have a single join to your StringMap table.  It then Pivots back the results

;With cte As
(
  Select
        id, [name], attr, vals
  From #tbl
  UnPivot
    (
      vals
      For attr IN (attr1, attr2, attr3, attr200)
    ) As u
  ), cte1 As
(
  Select cte.id, cte.[Name], cte.attr, s.[Label]
  From cte
  Inner Join #StringMap s
    On s.[Value] = cte.vals And s.AttrName = cte.attr
)
Select [Name] As [Name],
       [attr1], [attr2], [attr3], [attr200]
From
(Select [Name], [Label], attr From cte1) As src
Pivot
(
Max(Label) For attr IN ([attr1], [attr2], [attr3], [attr200])

) As piv

Result:

Name    attr1   attr2   attr3   attr200
First   Small   B       NULL    Blue
Second  Large   C       NULL    Purple

Note:
With so many columns, you'll want to search the site for using dynamic Pivots Such as: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query


How to clear all configurations on RMAN How can I clear all configurations listed with the command show all on RMAN to default? I just found how to clear one unique configuration for exemple: CONFIGURE CHANNEL DEVICE TYPE DISK clear

Exactly the same way:  CONFIGURE option_name CLEAR;
There is no 'clear all'.

https://docs.oracle.com/database/121/RCMRF/rcmsynta009.htm#RCMRF113


MySQLDump issue with a special characters Code:
use test;

CREATE TABLE Test_user(Username VARCHAR(15),path VARCHAR(100));

INSERT INTO Test_user VALUES('new1','C:\newfolder\one.xlsx');
INSERT INTO Test_user VALUES('new1','C:/newfolder/one.xlsx');

I have created a .sql file using MySQLDump, which has contents

Filename : Table1.sql

DROP TABLE IF EXISTS test_user;
/*!40101 SET @saved_cs_client     = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE test_user (
  Username varchar(15) DEFAULT NULL,
  path varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES test_user WRITE;
/*!40000 ALTER TABLE test_user DISABLE KEYS /;
INSERT INTO test_user VALUES ('new1','C:\newfolderone.xlsx'),('new1','C:/newfolder/one.xlsx');
/!40000 ALTER TABLE test_user ENABLE KEYS */;
UNLOCK TABLES;

in the above Table1.sql file ('new1','C:\newfolderone.xlsx') backslash before one.xlsx is not displaying , While trying to execute this file, i am getting output like this

new1    C:ewfolderone.xlsx
new2    C:/newfolder/one.xlsx

Where it is not displaying \ at all , suggest me how to resolve it and also what are special characters like this backslash will create this kind of bug.

Thanks in advance..

You should read about string literals in MySQL.

There you’ll learn that a backslash character is used to escape some special strings, and to have literal backslashes in your strings (as in Windows pathnames), you have to double them:

INSERT INTO test_user VALUES ('new1','C:\\newfolderone.xlsx');

The complete list of characters in need of a special treatment is at the above link.

However, it is best to have the intermediate language deal with such problems. For instance, in PHP you would call PDO::quote; other platforms and languages provide similar facilities.


Object Auditing with Availability Groups We are about to implement Availability Groups and it brought up a question.  We currently use auditing to see which objects are no longer used in production.  We also take a backup each night to use the next day for reporting.  Obviously, once we go to Availability Groups we won't need this as we will use a read-only database for reporting.  Because of our current setup, we don't know if an object is only used in the reporting database since the audit isn't on that database.

Question: Will auditing on an Availability Group register when someone uses an object on one of the secondary servers and not the primary?

No, auditing only reflects the one server that is running the audits. If you want to centralize audit results from multiple servers, you'll need to combine that data yourself.

Keep in mind that if someone really wanted to circumvent this, they'd simply add another replica on any server they want, do the querying they need, and then destroy the replica. An audit on the primary would only capture the fact that a replica was added (and even then, only if you're auditing for those events.)


Multiple Cascade Paths (Not really) - Sql Server In my database I have a table with 2 columns referencing the same column in a separate table.

I know this creates a race condition, but I have a check in place to make sure they don't point to the same row. Is there any smart way to achieve a cascading update behavior? Or am I forced to fall back to using triggers?

Maybe if I model the database differently. The model in question is an inventory transfer between stores. It notes the receiving store id and the sending store id, which creates a race condition (in the eyes of the RDBMS, I made sure that doesn't actually happen with a check).

Any input on this?

When SQL was first developed, the idea of cascading updates and deletes made sense. In practice, they are nothing but trouble. Deleting one row of one table or updating one field of one row of one table could lock up the entire database for hours because of the cascading locks. Or generate a race condition.

Plus, cascading actions are, by definition, side effects. You really, really want to minimize the amount of side effects. Nothing good comes from them.

So it is best to not allow the deletion of a row that is referred to by any foreign key(s). Always accept the default "On Cascade" option (No Action). Allow the DBMS to throw an exception and let the app respond correctly (delete/update the referencing rows then the referenced row -- after checking with the user if this is really what they want to do, of course).


Uninstalling 2 instances on 1 node of a 2 node SQL Server 2012 failover cluster instance I need some pointers to uninstalling 2 instances on SQL Server 2012 cluster

We have a SQL Server 2012 Active -Active cluster with instance A on Node 1 and Instance B, Instance C and 3 services on Node 2. I need to uninstall Instance A and B from Node 2. The services could be removed/disabled.So I will have an Active-Active cluster with Instance C on Node 1 and no instance on Node2.

I have never uninstalled an instance on a cluster and we don’t have any test system so I have a couple of questions.

When there is no instance on Node 2, does it become an Active Passive cluster?
Do I have to take the node offline before the uninstall?
Can I get any links to uninstalling an instance? I have googled but have only come up with uninstalling nodes. I got This one that said about uninstalling an instance but at the end of it, uninstalled the node.
Am I right in understanding that you need the installation media to uninstall the instance? And that I cannot use uninstall programme method for this?
I have followed this link with a comment from Perry Whittle. So does it mean if I use the remove node option , it will remove only the instance BUT the node will still be there ?

I had initially disabled the 2 instances on the node ,thinking that would be a safer option but that gave rise to continuous alerts on our Nagios monitoring system and Operations got errors during the monthly patching.

Thanks

When there is no instance on Node 2, does it become an Active Passive cluster?

Let me start by saying there is nothing like Active-Active cluster a more correct term which should be used is multi-instance cluster. When you have single instance of cluster it would simply be called as single instance failover cluster or just failover cluster instance (FCI), people do call it active passive but that is not correct. An active-passive cluster can have 2 instance on one node and other node having no instance which makes it look like active passive but actually it is multi instance failover cluster.

  Do I have to take the node offline before the uninstall?

No, you do not have to take node offline.

  Can I get any links to uninstalling an instance?

It is in the same MSSQLTIPS link you have posted. And also note when you uninstall an instance you have to uninstall it from ALL nodes of the cluster. That is what the link says in line step 9. Please dont confuse by removing node and removing node from WSFC, its removing node from failover cluster instance(FCI).

  Am I right in understanding that you need the installation media to uninstall the instance? And that I cannot use uninstall programme method for this?

When you are removing FCI from Windows server cluster you have to run the setup file and select remove node from failover cluster like shown in the links you are already referring. If you do not have FCI but standalone instance of SQL Server you can go to add remove program and select uninstall, this will uninstall the SQl Server.

  So does it mean if I use the remove node option , it will remove only the instance BUT the node will still be there ?

Yep correct.


Deploy materialized view with database link using another user Our database deployment process logs in as sys to deploy database objects.  We ran into a problem when we wanted to deploy a private database link for another user (AU).  Our solution was to:

Create a procedure owned by AU that uses an execute immediate to create the link
Run the procedure
Drop the procedure.

This worked great!  Unfortunately it did not work when we tried to script the creating of a materialized view owned by AU that uses its private database link.  It appears that it connects to the remote database using the logged in user (sys) not the user that owns the link (AU) and therefore fails:

ORA-04052: error occurred when looking up remote object
AU.Table_Name@Private_Link_Name
ORA-00604: error occurred at recursive SQL level 2
ORA-01017: invalid username/password; login denied
ORA-02063: preceding line from Private_Link_Name
ORA-06512: at "AU.temporary_procedure", line 3
ORA-06512: at line 1

The materialized view creates fine when done by AU directly.  Here is what the procedure looks like:

create procedure AU."tmp_doit_20180509" is
Begin
   execute immediate 'create materialized view m1 as select 1 from dual@private_link_name';
End;
/

The database link does not have credentials defined, so it uses the credentials of the connected user.  When logged in as AU these are the same between databases, but for sys they are different.

Here are some alternatives I have considered:

Manually log in as each user needing to create materialized views with database links - this breaks our automated deployment process.
Use a dbms_scheduler job to create the mview - this also does not work and fails similarly to the procedure method because the job runs in the context of the user that created it even if it is in another schema.
Store credentials for users and login as each specific user to do the deployment - depending on how this is done, it seems that it would either be complex to build to make it secure or complex to maintain, perhaps both.
Connecting as a proxy user - gives the same error.

Here is the solution I am using for now.  I don't like it, but until I find something better, it's the best I can do.

At the start of the deployment a public database link with named credentials is created that uses the same name as the private database link will use.
The materialized view is created and works because it is using the public link with embedded credentials.
A procedure is created owned by "Another User" (AU) that uses an execute immediate to create the private database link.
The procedure is run to create the private database link.
The procedure is dropped.
The public database link is dropped.

Unfortunately this requires the swapping the existence of both the public and private database links before and after any deployment that creates materialized views using links.  The temporary creation of a public database link with embedded credentials is also a security risk and is in fact a problem we are trying to solve.


copy command in postgresql to append data I am executing copy command from a function

execute 'copy (select * from tableName) to ''/tmp/result.txt'''

It works fine. But, tableName is dynamic and will be having more tables (iterating in a loop). result.txt is having only the last iteration (table) data.

I could do, if I had to, via the command line with \o or >> by appending to a file. But, I have to use the function.

I read http://shuber.io/reading-from-the-filesystem-with-postgres/.  It suggests a solution using temporary tables. Can I do something similar, but without temp tables? (I must avoid archive logs).  Will creating a temp table create archive logs?

Is there any way to do appending with copy command in a function?

Assuming a Unix system

with PostgreQSL 9.3 or newer

Use the PROGRAM clause with cat as the program.

execute 'copy (select * from tableName) to PROGRAM ''cat >>/tmp/result.txt'''

This would append the results to the end of /tmp/result.txt instead of overwriting the file.

with older versions of PostgreSQL

A named pipe can be used by a separate process to concatenate results.
Example:

$ mkfifo /tmp/pgfifo
$ while true; do cat /tmp/pgfifo >>/tmp/result.txt; done

This last command will block. Let it run until all the results are accumulated, then it can be terminated with ^C or kill.

In SQL, the fifo has to be fed with:

execute 'copy (select * from tableName) to ''/tmp/pgfifo'''


How to hint Remote Scan operator to estimate more than 10000 rows? I need to read data from a linked server and insert into a local table. I need to remove duplicates in data and I need to do it on a local server, because remote server is overloaded. So, I added DISTINCT clause which does Distinct Sort as I want it to do.
The problem is that Remote Scan operator always estimates the number of rows as 10000, while the real number of rows is around 3M. So, the sorting spills to disk and becomes slow.
If there a way to hint to the optimizer that the real number of rows is much more than 10K?
Should I load raw data into a local staging table and then run DISTINCT off the local table? I didn't want to write to disk twice.
The number of rows that are duplicates is small - few hundred out of 3M.
I mean by this that before the duplicates are removed there are ~3,000,000 rows; after the duplicated are removed there are ~2,999,800 rows. So, removing the duplicates on the remote server would not noticeably reduce the amount of data that is transferred over the network.
The destination table is truncated before insert, so I'm always inserting into an empty table. Also, the destination table doesn't have any indexes, triggers or constraints. There are many columns in the table. About 110 columns. In the query below I wrote ManyManyColumns instead.
The query:
WITH
CTE_Raw
AS
(
SELECT
    [ManyManyColumns]
FROM OpenQuery([remote_server],'
SELECT
    [ManyManyColumns]
FROM
    [DB].[dbo].[remote_view]
')
)
,CTE_Converted
AS
(
    SELECT DISTINCT
        [ManyManyColumns]
    FROM
        CTE_Raw
)
INSERT INTO [dbo].[TestVBFast2]
    ([ManyManyColumns]
    )
SELECT
    [ManyManyColumns]
FROM
    CTE_Converted
;

SQL Server version:
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
    Aug 15 2017 10:23:29
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

I assume ManyManyColumns is really multiple columns and not one column?...I see your comment states it's 110 actually.
10,000 rows is the default cardinality estimation for a Remote Scan operation in your version of SQL Server, so I don't think you can do much to change that, unfortunately.
How slow is slow currently? Keep in mind even with perfectly accurate cardinality estimates, 3 million rows is always going to be a lot of data to pipe across the network / linked server, especially if you have many columns.
The only general ideas I have at the moment is to either pre-stage the DISTINCT data on your remote server, or use a data synchronization feature like replication to copy it over to your local server instead of using a linked server. If I think of anything else, I'll update my answer accordingly.


SQL Log Shipping Job Scheduling If I have SQL Log Shipping scheduled as follows:

Primary Server (VM): Backup Job Schedule - Every 5 Minutes (To LogCopyTo)

Secondary Server (VM): Copy Job - Every 15 Minutes (To LogCopyFrom)

Secondary: Restore Job - Every 25 Minutes No Recovery Mode

If I take a snapshot of the Secondary VM remove it from the network (for 24 hours) and run "Restore With Recovery" on each database and work with the data for testing purposes and restore the snapshot and put it back on the network, will log shipping catch up and continue without issues?

Your best bet is to test it out. You did not told us what would you do to the transaction log backup job on primary will it keep on happening as per schedule?, if so do you have enough space on drive holding transaction log backups ? Also for this exercise it would be better to have secondary database in standby mode before you recover it

If you do not disable the backup job then you have to make sure you keep all the log files intact and after you add the secondary back the mammoth task of restoring all those files would be on restore job and it will sure fire some alerts that log shipping is out of sync but since log chain is not disturbed it should eventually catch up.

Now other scenario can be you disable all the log shipping jobs and is ready to run primary database exposed (which would not be correct thing, its your call) and now you go ahead and remove secondary from log shipping for 24 hours. When you will add it back, subject to condition you can do it successfully, the log shipping should work. The reason I say it should work is I do not see any log chain which is disturbed or broken and after adding the secondary server. The challenge here also is whether you are successfully able to restore the snapshot, please note I have seen many a times this thing failing that is why i stressed on testing it.


SQL Server 2014 - AlwaysOn HA - Always Synchronizing I have a Windows Server 2012 R2 cluster with SQL Server 2014 configured in a HA configuration.

The secondary databases always say Synchronizing when the data movement is happening. Is this normal? Should it say synchronized at some point?

Since you have configured AlwaysON as Asynchronous, it will be in synchronizing state.

  Should it say Synchronized at some point?

If you want to do a planned failover, you should alter your availablity group to synchronous and then do a failover.

When you change the synchronization method to synchronous and when the secondary has catch-up with primary, it will show synchronized.

Refer  to my answer : How far is secondary behind primary ? as well as AlwaysON professional blog - Determine Availability Group Synchronization State, Minimize Data Loss When Quorum is Forced


How to load data from another sql file into a main sql file? - MYSQL I am trying to process the scripts dbcreate_f.sql and dbload_f.sql to create and to load data into a script solution6.sql.
I am doing this in MYSQL command line.
In solution6.sql I have two statements:
source dbcreate_f.sql;
source dbload_f.sql;

These two files contain data to create tables and insert data into those tables. These two files are perfectly fine - no errors.
The problem I have though is when I run the actual solution6.sql script in MYSQL command line.
My steps are as shown:
tee solution6.rpt;
source solution6.sql;

However, when the script processes in the command line I receive this error message everytime data is attempting to be inserted into each tabe:
ERROR 1146 (42S02): Table 'bh576.AIRLINE' doesn't exist

I have run both the dbcreate_f.sql and dbload_f.sql script in mysql workshop and they run perfectly. The  problem only occurs in the command line interface.

POSSIBILITY #1
You need to check the table to see if it is damaged or corrupt
Please run the following:
USE bh576
SHOW TABLES LIKE 'AIRLINE';
SHOW CREATE TABLE AIRLINE \G

The first 2 Lines will verify the presence of AIRLINE.frm
The SHOW CREATE TABLE AIRLINE \G will reach out and attempt to retrieve AIRLINE.frm metadata. If this command complains by giving you that error
ERROR 1146 (42S02): Table 'bh576.AIRLINE' doesn't exist

Then, the table is broken in terms of its attachment to the InnoDB data dictionary.
If you get this to work in MySQL Workshop every time, then a corrupt table is not the problem.
POSSIBILITY #2
You need to make sure you are standing in a database.
It may be possible that the target database is not set in the dbcreate_f.sql script.
Before running the scripts, run this:
mysql> SELECT database();

This should return the name of the database you are loading.
If this comes back as NULL just change to the target database before running
USE mydb
source dbcreate_f.sql
source dbload_f.sql


Indexing a temp table Would it be a good idea to apply indexing on temp tables when it is suggested to do so in the estimated execution plan?

Yes it is a good idea. Temp tables will benefit from indexes in the same way that permanent tables will. Index creation is the same, too, with a few minor exclusions.

The indexes function in the same way as they do on permanent tables, with the exception that temp tables are cached, which can hurt if the workloads are skewed significantly.

The same caveats apply too. Just because an index exists does not mean the optimizer will use it. Writes will be costlier with many indexes in place. Stale statistics can produce poor plans.

Recently I was able to reduce a large batch procedure's run time from over 20 minutes to under three by adding indexes to temp tables. This is a worthwhile task.

See also Indexing Temp Tables by Erik Darling.


a question in tables relations Table A is linked to Table B , and Table B is linked to Table C using primary and foreign keys
My questions:

Should i link Table A to Table C too?? Or Table B can act as intermediate link between both??
If i linked between Table A to Table C will that affect on DB perfomance and data quiring positively or negatively??

Plan 1

If tables COUNTRY, CITY, and STREET were in identifying relationship, then ER diagram would be like below:

With identifying relationship, table STREET already has column country_id, so it has no difficulty to directly join with table COUNTRY.

Plan 2

But if (and as I think is the case) the tables have non-identifying relationship, then ER diagram will be like below:

With this, we cannot join table STREET with table COUNTRY directly because there is no key to connect. So we have to use table CITY in order to join STREET with COUNTRY.

Plan 3

Sometimes we want to avoid this (usually performance issues), so add an additional relationship from table STREET to table COUNTRY. This is called Denormalization. We often do this for performance perspective. Surely, Joining with two tables has better performance than with three tables. But denormalization always bring about many difficulties about maintaining consistency.

In short, we assume that you now have plan2 and you are considering to move to plan3.

Don't. The STREET -> COUNTRY foreign key constraint is redundant.

You can though choose plan1 if you want. You still have only two foreign key constraints (no redundancy) and you can join COUNTRY and STREET directly.

If you are not fond of the compound (multi-column) primary keys or you are afraid of this causing performance issues (in comparison with simple IDENTITY primary keys), you could use a variation on plan1:

Plan 1b

Keep plan1 design (primary and foreign keys) but define the (plan2's) single-column keys as the clustered indexes.


How does the Cassandra distribute data? I cannot find a detailed document on this anywhere, however, that being said, if anyone has an answer to my question, I would be extremely grateful.

In regards to Cassandra's standard node architecture, you are required as the administrator to divide the hash range by the number of nodes in the cluster, thus you have your tokens.

That being said, vnode tokens are generated automagically, and architecturally, I am curious as to how the system maintains these tokens.

To further specify my question for virtual nodes does the cluster re-evaluate tokens for all nodes just because a new host has been added?

To further this thought process, I am inclined to believe that it doesn't, as that would be a very expensive operation moving around any data that would then be in the wrong node due to the recalculation.  Unfortunately, I cannot find out what happens...

Also, if two nodes can hold the same token, doesn't that break the architecture, or at very least doesn't it make designating replication factor almost pointless, as it should be written to all nodes that have the specified vtoken/token?

How is a vnode chosen for a particular token are the tokens assigned serially or randomly?

Lots of difficult questions, however I am counting on any Cassandra hanging around.

Thank you,

Scott

I initially just wanted to point at this as a comment, but my rep is too low. So excuse me if this is not a fully fledged answer. (Anyone feel free to edit or hint in comments)

Looking at the documentation at datastax it appears that data will be evently divided by nodes, depending on the "automagical" tokens. From my understanding it's simple as that a new vnode with new tokens just takes an even portion of each node, like all the other nodes already do. Hell, this is even what the doc says basically word by word:

  Rebalancing a cluster is no longer necessary when adding or removing nodes. When a node joins the cluster, it assumes responsibility for an even portion of data from the other nodes in the cluster. If a node fails, the load is spread evenly across other nodes in the cluster.

And to address your question:

  To further specify my question for virtual nodes does the cluster re-evaluate tokens for all nodes just because a new host has been added?

Sort of. The new node itself takes some partitions (evenly) from all other nodes. If you remove a node every other node will rebalance its partitions from the other nodes, making up for the lost node.


What permissions are required for complex cross-schema materialized view in Oracle? I have a complex cross-schema query that I'd like to create as a materialized view in Oracle 19c (although the database has been ported forward from numerous historical versions of Oracle).  This includes joining a subquery to 5 additional tables across three total schemas, and then unioning to another copy of the query against 3 different schemas.  The account creating the view has DBA and CONNECT roles, and has been granted GLOBAL QUERY REWRITE, UNLIMITED TABLESPACE, CREATE PROCEDURE, CREATE MATERIALIZED VIEW, and CREATE TABLE system privileges.  It also has explicit SELECT permissions on all of the tables in the query.

I can run the query itself without issue.  However, when I add the setup for a materialized view:

CREATE MATERIALIZED VIEW <account_schema>.<materialized_view_name>
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
AS
<SELECT...>

I get ORA-01031, or "insufficient privileges".  What am I missing?  Is there a way to query for a more detailed report of what's missing?  It points to a specific location in the query (the table in the last join), but I don't see any difference between it and the other tables.

The issue appears to be the "ON COMMIT" portion of your DDL. From the documentation:

"To create a refresh-on-commit materialized view (REFRESH ON COMMIT clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege."

So either go with "ON DEMAND" or grant the additional privilege for each source table.


Why these characters are all equal in SQL Server? I just don't get it. See this SQL query:

select nchar(65217) -- ﻁ
select nchar(65218) -- ﻂ
select nchar(65219) -- ﻃ
select nchar(65220) -- ﻄ
if nchar(65217) = nchar(65218)
    print 'equal'
if nchar(65217) = nchar(65219)
    print 'equal'
if nchar(65217) = nchar(65220)
    print 'equal'

Based on transitive relation, it means that SQL Server considers them all to be the same character.

However, in other environments, say for example C#, they're not the same.

What I'm confused about is:

How string comparison works in SQL Server
Why comparison doesn't behave the same on one machine, and one platform, but different environments
These 4 characters represent one human-understandable character. Why they are so abundant in Unicode character map?

This of course results in tremendous problems, because I'm working on a text-processing application and data comes almost from everywhere and I need to normalize text before processing it.

If I know the reason of difference, I might find a solution to handle it.
Thank you.

All character data in SQL Server is associated with a collation, which determines the domain of characters that can be stored as well as the rules used to compare and sort data.  Collation applies to both Unicode and Non-Unicode data.

SQL Server includes 3 broad categories of collations: binary, legacy, and Windows. Collations in the binary category (_BIN suffix) use the underlying code points to compare so equality comparisons return not-equal if the code points differ regardless of the character.  Legacy (SQL_ prefix) and Windows collations provide sorting and comparison semantics for the more natural dictionary rules.  This allows comparisons to consider case, accents, width, and Kana.  Windows collations provide more robust word-sort rules that closely align with the Windows OS whereas legacy collations consider only single characters.

The example below illustrates the differences between Windows and binary collation with the Teth character:

CREATE TABLE dbo.WindowsColationExample
    (
      Character1 nchar(1) COLLATE Arabic_100_CI_AS_SC
    , Character2 nchar(1) COLLATE Arabic_100_CI_AS_SC
    , Character3 nchar(1) COLLATE Arabic_100_CI_AS_SC
    , Character4 nchar(1) COLLATE Arabic_100_CI_AS_SC
    );

CREATE TABLE dbo.BinaryColationExample
    (
      Character1 nchar(1) COLLATE Arabic_100_BIN
    , Character2 nchar(1) COLLATE Arabic_100_BIN
    , Character3 nchar(1) COLLATE Arabic_100_BIN
    , Character4 nchar(1) COLLATE Arabic_100_BIN
    );

INSERT  INTO dbo.BinaryColationExample
VALUES  ( NCHAR(65217), NCHAR(65218), NCHAR(65219), NCHAR(65220) );
INSERT  INTO dbo.WindowsColationExample
VALUES  ( NCHAR(65217), NCHAR(65218), NCHAR(65219), NCHAR(65220) );

--all characters compare not equal
SELECT *
FROM dbo.BinaryColationExample
WHERE
    character1 = character2
    OR character1 = character3
    OR character1 = character4
    OR character2 = character3
    OR character2 = character4
    OR character3 = character4;

--all characters compare equal
SELECT *
FROM dbo.WindowsColationExample
WHERE character1 = character2;
SELECT *
FROM dbo.WindowsColationExample
WHERE character1 = character3;
SELECT *
FROM dbo.WindowsColationExample
WHERE character1 = character4;
SELECT *
FROM dbo.WindowsColationExample
WHERE character2 = character3;
SELECT *
FROM dbo.WindowsColationExample
WHERE character2 = character4;
SELECT *
FROM dbo.WindowsColationExample
WHERE character3 = character4;

Reasons as to why Unicode may contain different code points for identical glyphs are outlined in  http://en.wikipedia.org/wiki/Duplicate_characters_in_Unicode.  I summary, it may be for legacy compatibility or the characters are not canonically equivalent.  Note that the Teth character ﻁ is used in different languages (http://en.wikipedia.org/wiki/Teth).


How to check if databases are consistent in pgsql replication? There is some tool for mysql (from percona I think) which could compare databases by making md5 sums of tables data. We used it in our mysql replication system to check if data is correctly replicated. Do you know something similar that I can use in pgsql environment ?

You could try pg_comparator.
Also older version of pt-table-checksum (or mk-table-checksum) could compare pg data - but I wouldn't recommend these old versions.


Why would i get a duplicate write error of auto-generated primary key? Hi I'm not a DBA so forgive my ignorance. I don't have access to administrator tools etc. However, I do know that we have a live server which is approaching capacity ie 80%-90% full perhaps.

What is happening is that our web-application has started not writing certain records and it appears as though SQLServer is attempting to write a duplicate primary key in certain tables. I don't understand this and as this is an auto-generated column it is completely out of our control.

Is this a possible symptom of the server getting too full? Surely a DBMS should manage this kind of thing safely?

I think this could happen only if manual intervention was done to that primary key. It's possible to manually insert data in the identity column only if option "set identity_insert table_name on" was used, that way you can generate PK errors.
But better check exactly if the PK is only that identity column, and, if yes, if this option is used in any of the stored procedures or scripts that you use.

SQL Server alone would not have any reason to not treat correctly these columns.


Backup and Restore of master KEY and Certificate are not working on same server but different DBs. I have SQL Server 2012 I am following these steps but still not able to DECRYPT the data at destination.

In Source DB I have created Following:

CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Analytics@2020';

CREATE CERTIFICATE Certificate_Customer_Data
   WITH SUBJECT = 'Customer Sensitive Data';

CREATE SYMMETRIC KEY EmailID_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Certificate_Customer_Data;

Then I backup Master Key and Certificate from Source DB to files:

BACKUP MASTER KEY
  TO FILE = 'C:\Shivendoo\MasterKey.bak'
  ENCRYPTION BY PASSWORD = 'Analytics@2020';

BACKUP CERTIFICATE Certificate_Customer_Data TO FILE = 'C:\Shivendoo\DBCertificate.cer'
    WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = 'Analytics@2020',  FILE = 'C:\Shivendoo\PrivateKey.pvk');

Next I restore Back to Destination DB

RESTORE MASTER KEY
  FROM FILE = 'C:\Shivendoo\MasterKey.bak'
  DECRYPTION BY PASSWORD = 'Analytics@2020'
  ENCRYPTION BY PASSWORD = 'Analytics@2020';

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Analytics@2020';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

CREATE CERTIFICATE Certificate_Customer_Data FROM FILE = 'C:\Shivendoo\DBCertificate.cer'
  WITH PRIVATE KEY(FILE='C:\Shivendoo\PrivateKey.pvk',
                   DECRYPTION BY PASSWORD='Analytics@2020')

CREATE SYMMETRIC KEY EmailID_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Certificate_Customer_Data;

Finally I loaded data from Source using this query:

OPEN SYMMETRIC KEY EmailID_Key_01
   DECRYPTION BY CERTIFICATE Certificate_Customer_Data

SELECT ENCRYPTBYKEY(KEY_GUID('EmailID_Key_01'), [EMAIL]) AS [EMAIL]
FROM [dbo].[Source_Table]

Data loaded with encryption to Destination but when I try to Decrypt at destination using below query, It returns as NULL value for column DecryptedEmail

OPEN SYMMETRIC KEY EmailID_Key_01
   DECRYPTION BY CERTIFICATE Certificate_Customer_Data

SELECT CONVERT(NVARCHAR, DECRYPTBYKEY([EMAIL])) AS DecryptedEmail,[EMAIL],* FROM [dbo].[Destination_Table]

Please can you help..?

I am not getting any error but not getting expected outcome at Destination DB when I try to Decrypt Email at Destination which was Encrypted at Source.

The problem here is that your symmetric keys are not the same key, so it cannot be used in the destination database to decrypt the database on another database/instance/machine. Take a look at this link which describes the encryption hierarchy in SQL Server.

Basically, what is happening is you're creating a symmetric key on Source and using that to encrypt the column data. That symmetric key is protected (encrypted) by the certificate, the certificate is protected (encrypted) by the database master key and the DMK is protected (encrypted) by the service master key.

When you restore the DMK and Certificate to the destination database and create the new symmetric key, this doesn't allow you to decrypt the original data because the encryption was done by a different symmetric key.

In order to decrypt the data, you need to create an identical symmetric key. You achieve this by specifying the KEY_SOURCE and IDENTITY_VALUE properties of the symmetric key at creation time. By default, these values are randomly generated unless you specify explicit values.

By providing explicit values, an identical key is created and can be used on a different database/instance/machine to decrypt data encrypted by the source symmetric key. You can even use different certificates and database master keys to protect your destination symmetric key, it won't affect the decryption because the symmetric key is identical to the source key and once opened will decrypt the data.


What is different between SQL-Server versions 2008R2 and 2012 Dear SQL Server experts,

Can you tell me the different between SQL-Server 2008 R2 and SQL-Server 2012 versions? I designed my product with version 2008 but when I tried to buy a licence, Microsoft said they won't sell it anymore, buy 2012 instead.

So I need proof that our system can run with 2012. I know it's backward compatible but I need official proof. Please help me.

A few things you'll want to watch out for:

Changes to memory manager mean some changes to some dynamic management views - https://sqlblog.org/2011/01/08/sql-server-v-next-denali-changes-to-performance-counters
fn_virtualfilestats no longer takes -1 for parameters - https://sqlblog.org/2011/06/28/sql-server-v-next-denali-breaking-change-to-fn_virtualfilestats
you may no longer be able to identify system databases simply by DB_ID between 1and 4 - https://sqlblog.org/2011/07/08/sql-server-v-next-denali-breaking-change-to-system-databases-database_id-db_id
Temp tables now get negative object IDs, so make sure you don't have code that checks for object id > 0 - https://sqlblog.org/2010/11/09/sql-server-v-next-denali-breaking-change-to-temp-tables
sys.dm_exec_sessions has a new column, authenticating_database_id

For an official list (it doesn't include all of the above items, and there may be others that aren't coming to mind):

http://technet.microsoft.com/en-us/library/ms143179(v=SQL.110).aspx

You may also want to check the discontinued features page:

http://technet.microsoft.com/en-us/library/ms144262

And also run the Upgrade Advisor against your instance, this is exactly what it's for:

http://technet.microsoft.com/en-us/library/ms144256.aspx


Sorting ranking by trigger I create a game in which players will gain levels. The basic information is in the table "char" (nickname, lvl, exp, money ...).

I want to make a ranking that will be updated on a regular basis and works in real time. I think it makes no sense to perform the "ORDER BY lvl" operation on the "char" table every time someone enters the "Ranking" tab on the site.

My plan is this:

1) Make an additional table: "ranking" in which the data will be physically sorted.

2) Create a trigger that will update the ranking every time someone reaches a new level or someone creates a new character.
Does it make sense and is more efficient? If so, how can I do it? If not, how else to do it?

There is no such thing as "physical order" in the relational databases. Sometimes queries can produce the ordered results but that should be considered as "ordered by coincidence". There is absolutely no guarantee that you get the same ordered result each time.

If you need the ordered result the ORDER BY clause is a must.


Cost of replaying transaction log on sql server HA replica I've been reading about the sql azure scale out.

I suppose it makes senses to leverage the replicas hardware for read-only workloads.

But it got me wondering, which is actually doing more work? The primary or the replicas?

In particular it's not clear to me how expensive it is for the the replicas to  'catch up' on the transaction log from the primary.

If I understand correctly, if the primary executes a query like UPDATE dbo.LargeTable SET [SomeColumn] = 10, then it would probably do an index scan to update all the rows, but the transaction log will actually contain one entry per row? That would mean the replica would need to do a key lookup for each row instead of an index scan, which would be a lot more expensive.

I'm sure my logic is wrong somewhere but I'd love to understand more about this.

Is this assumed that catching up on the primary is low cost (relative to the cost incurred by the primary to actually run the transaction)?

Also, are the physical pages of the replicas exactly identical to the primary at the byte level? (i.e. fragmentation is the same too) Or are there just logically the same?

But it got me wondering, which is actually doing more work? The primary or the replicas?

The Primary.  The secondary doesn't have to run the SELECT,INSERT,UPDATE, and DELETE statements for the sessions that are modifying the database.  The log records resulting from those operations are sent to the secondary which copies them to its log file and then applies them to the database using the REDO process.

The Secondary can sometimes perform more IO than the primary, as the primary writes the log file, and lets the Checkpoint/Lazywriter write the database.  But overall the Primary does more work.

  That would mean the replica would need to do a key lookup for each row instead of an index scan, which would be a lot more expensive.

The secondary doesn't apply the logical operation.  The log record indicates exactly what modifications are made to the database pages.

  Also, are the physical pages of the replicas exactly identical to the primary at the byte level?

Yes.  In fact a if the primary discovers a corrupt page, it will repair the corruption by getting an uncorrupted copy of the page from a secondary.  This is called Automatic Page Repair


Is two separate index equals to a composite index? For example, this is a sample table

id int PK,
pid int,
cid int,
deleted int,
createdAt timestamp,
UNIQUE KEY pid_cid(pid, cid)

used WHERE clauses are

where pid=$pid and cid=$cid [and deleted = 1];
where pid=$pid [and deleted = 1];
where cid=$pid [and deleted = 1];

since I have indexed UNIQUE KEY pid_cid(pid, cid), should I index on pid and cid separately?

Or selecting by a index pid faster than selecting by a UNIQUE KEY pid_cid(pid, cid)?

No. Two separate index is not the same as a composite. Although MySQL now has index merge other than analytical queries it's not very efficient.
In your case the best to have the unique key and a single column index.
Start the composite with the higher cardinality column. If pid has more distinct values than it's good as it is. If cid has more than I would suggest to use UNIQUE KEY (cid, pid). Also create a separate index which is only the column that is the second in the unique key.
For example
UNIQUE KEY (cid, pid), KEY (pid)
or
UNIQUE KEY (pid, cid), KEY (cid)
This way:

where pid=$pid and cid=$cid [and deleted = 1] will use the UNIQUE KEY

One of these will use the composite unique key the other will use the single column index.

where pid=$pid [and deleted = 1];
where cid=$pid [and deleted = 1];

 Alternative solution
You can make the composite index the PRIMARY KEY and have a single key on the other column:
Pros:

Very fast lookup on PRIMARY KEY
No need for an extra ID
Rows will naturally be clustered around the first column of the PK:

Since you have queries where you only have one of the column cid = X or pid = X these rows will be close(r) to each other so InnoDB needs to read less pages. Which can significantly speed up multiple rows resulting queries.
Cons:

Every index will have the PK concatenated to it so if you have a lot of secondary indexes this can consume some space.
If neither pid or cid are in incrementing order your INSERT throughput will suffer


What is the fastest way to fetch the last row from a table? I have a PostgreSQL table, Prices, with the columns:

price (Decimal)
product_id (Int)

There are also created_at and updated_at columns.

Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table is the current price.

What is the most efficient way to get the last price for a specific product:

Index product_id and query for the last record
Add a third column active (Boolean) to mark the latest price and create a composite index (product_id and active)
Or something else?

You'll need an index on product_id regardless of solution.

Provided you have an index on the updated_at column, and all you need is to fetch "a specific product" as you stated, then I would do:

select *
from Prices
where product_id = ?
order by updated_at desc
limit 1

But if I did not get the results I wanted or if I needed to get the current price for many products, then I would try the option of adding a active column, and setting it to N for all prices other than the new one when doing updates of the prices and then I would create a partial index where active as suggested by a_horse_with_no_name. I would go there only if I needed to as it adds a layer of complexity of updating previous price rows to not be active, etc.


Using transactions in sqlplus I'm coming to oracle from postgresql.  In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction:

dbpool.production=> begin;
BEGIN
dbpool.production=*> delete from foo where account_id like '%1000%';
DELETE 9
dbpool.production=*> commit;
COMMIT
dbpool.production=>

I like to see that I got the count I expected before committing the transaction.

Things don't work like that in sqlplus, the Oracle command-line tool.  When I type "begin;", it just starts reading more input, never apparently doing anything:

SQL> begin;
  2  update opacs_work_orders set customer_id = null;
  3  commit;
  4

How can I, using sqlplus, execute one or more statements with a transaction?

You simply don't use begin in sqlplus if you're just going to issue a series of SQL queries. You're in a transaction already as soon as you issue some SQL. You can't really be outside of a transaction anyway for practical purposes (sure, if you've just logged in, or just committed and haven't started anything else, well, you're not in a transaction).

A few things to be careful with though:

sqlplus does have an autocommit setting. It's off by default in modern versions, but just to make sure:

SQL> show autocommit
autocommit OFF

If it happens to be on:

SQL> set autocommit off

sqlplus commits on exit by default even in modern versions. To disable that:

SQL> set exitcommit off

(That's fairly new, appeared in 11g or 11gR2. Previously always committed on exit.)
DDL commits. (Twice. Once before, once after.) Note that truncate is DDL in Oracle.

Aside from these gotchas, you're in a transaction already when you start sqlplus, and can commit or rollback as you please.

$ sqlplus mat

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 20 19:51:31 2014
...
SQL> insert into abc values (1) ;

1 row created.

SQL> select count(*) from abc;

  COUNT(*)
----------
         1

SQL> rollback;

Rollback complete.

SQL> select count(*) from abc;

  COUNT(*)
----------
         0

You need to use begin/end when you want to run a PL/SQL block. (The short/single line form for that being exec.)


What does a CONSTRAINT have to do with my unique index? I have to indexes on my table.  The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change my CREATE INDEX statement to add that to it.

"customers_phonetype_customer_id_176731583d230ba5_uniq" UNIQUE CONSTRAINT, btree (customer_id, display) ;-- Django created
"customers_phonetype_customer_id_uniq" UNIQUE, btree (customer_id, lower(display::text)) ; -- Manually created

This is how I manually created the second index:

create unique index customers_phonetype_customer_id_uniq on customers_phonetype (customer_id, lower(display));

What is the meaning of the extra word CONSTRAINT in this case? I'm not seeing it in the docs.

The first one is a unique constraint. It can be added to an existing table with:

ALTER TABLE ADD CONSTRAINT ...

Details in the manual here.
It is implemented using a unique index. Per documentation:

  Adding a unique constraint will automatically create a unique btree
  index on the column or group of columns used in the constraint. A
  uniqueness constraint on only some rows can be enforced by creating a partial index.

In addition it allows foreign key references to it.

The second one in a unique index.
It couldn't be a unique constraint because those only allow columns, not expressions. More details:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Discussion on pgsql-general about the difference between constraint and index.


Designing database with Category and Tags for Posts - Seeking for guidance/advice I'm not really a database novice but I'm not an expert either.

Problem:

I'm having this difficulty on designing my Post, Category and Tags relationship.

I've read Normalization many times but could not fully digest them and use it as a guide to what I'm trying to achieve. I've also encountered EAV, read about it and likewise. Or maybe I just can't choose which to use for this scenario.

I have few solutions in mind but I could not settle on one. I'm craving for the "BEST" choice. I'm aiming to be able to understand and design good databases!

Scenario

I have a Post table, like in any other website like wordpress with post capability.

I want that post to be associated with 1 or more Categories and Tags.

I want the category to have a hierarchy where categories like Cats and Dogs are under Animals category.

Option 1

Categories
    id -- PK
    pid -- FK to self (id), allow NULL
    name
    description
    slug
Tags
    id -- PK
    name
    description
    slug
Cat_Rel
    id -- PK
    pid -- FK: Post Id
    cid -- FK: Category Id
Tag_Rel
    id -- PK
    pid -- FK: Post Id
    tid -- FK: Tag Id

Notice how those Categories/Tags and their relationship with Post are very similar?

I have this urge that I think I could just combined them which leads to my problems.

Options 2

PostGroup -- I don't know how to call this currently but this should work for now
    id -- PK
    pid -- FK: category use only so NULL for all tags
    name
    description
    slug
    type -- and we add a type where we could explicitly tell if it's a category or a tag
Group_Rel -- Now we only have 1 relationship table
    id -- PK
    pid -- FK: Post Id
    gid -- FK: Group Id

So, which one should I follow? Or none of those? Other options?

On your reply, can you include DO's and DON'T, PROS and CONS.

Anything that you think I need to know, understand and realize.

One way to understand what's going on with normalization is to find out what normalization buys you.  In other words, what's going to happen if you depart from any given normal form?

For normalization forms second through fifth normal forms, the answer is "update anomalies".  That is, when you insert, update, or delete rows, can you end up leaving things in a bad way?  One particular kind of bad way is a database that contradicts itself, that stores a given assertion somewhere in the database and stores a contradictory assertion somewhere else in the database.

Normalization helps with this by obviating the problem.  That is, it cuts down or eliminates the possibility of contradictory assertions by assuring that facts are stored once, and only once.  The fact is either right or it's wrong, but it's not self contradictory.

There are other ways to avoid self contradictory databases, but they involve careful, disciplined programming.  There are situations where a non normalized approach gives you more benefits than a normalized approach.  But this calls for judgement, and judgement comes with experience.

With regard to EAV, one attraction to EAV is that it allows you to get started without coming up with a logical model for the structure of the data.  The reason is that any logical model whatsoever can conform to EAV.  So  why doesn't everybody do EAV?  Because sometimes, the data that results is relatively worthless compared to the expected value when the database was built.  I'm not saying EAV is always wrong.  I am saying that it often is.  Again, this calls for judgement, and judgement comes with experience.


How do I solve this problem of a correlated subquery We have an employee table with the following attributes :

employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id

Display the manager name and the salary of the lowest paid employee for that manager.
Exclude anyone whose manager is not known.Exclude any groups where the minimum salary
is less than 6000.sort the output in descending order of salary. Use alias name "salary"

I have tried the following :

SELECT manager_id , (SELECT MIN(salary)
                     FROM employees emp
                     WHERE e.manager_id = emp.employee_id
                     GROUP BY employee_id)
FROM employees e
GROUP BY manager_id;

I don't seem to get correct outputs with this

Your query should be like:

SELECT FIRST_NAME, MIN_EMP_SALARY FROM
(
    SELECT  FIRST_NAME , (SELECT MIN(SALARY)
                         FROM EMPLOYEES EMP
                         WHERE E.EMPLOYEE_ID = EMP.EMPLOYEE_ID
                         GROUP BY EMPLOYEE_ID) AS MIN_EMP_SALARY
    FROM  EMPLOYEES E
    GROUP BY FIRST_NAME , EMPLOYEE_ID
) OUTER_TABLE WHERE MIN_EMP_SALARY >= 6000
ORDER BY MIN_EMP_SALARY DESC