Table Partition Switch Problem SQL Server I have

Tables Schema:

CREATE TABLE [dbo].[t1](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[id_file] [bigint] NOT NULL,
[id_campo] [bigint] NOT NULL,
[valor] [nvarchar](255) NULL,
[id_doc] [bigint] NULL,
[grupo] [bigint] NULL,
CONSTRAINT [PK_valores_texto_test_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE [dbo].[t2](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[id_file] [bigint] NOT NULL,
[id_campo] [bigint] NOT NULL,
[valor] [nvarchar](255) NULL,
[id_doc] [bigint] NULL,
[grupo] [bigint] NULL),
CONSTRAINT [PK_valores_texto_switch_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

The table t1 has data, meanwhile t2 is empty with the same schema as t1, then I want to partition both tables and switch the new data inserted into t2 to t1.

First, I create the partition function and partition schema.

-- Create the partition function
CREATE PARTITION FUNCTION PF_valores_texto_partition_HASH_BY_VALUE (BIGINT)
AS RANGE LEFT
FOR VALUES (2500000, 5000000, 7500000, 10000000, 12500000, 15000000)
GO

-- Create the partition scheme
CREATE PARTITION SCHEME PS_valores_texto_partition_HASH_BY_VALUE
AS PARTITION PF_valores_texto_partition_HASH_BY_VALUE
ALL TO ([PRIMARY]);
GO

And finally, I partitioning the tables.

CREATE UNIQUE NONCLUSTERED INDEX [partition_by_id_file] ON
t1
(
    id ASC,
    id_file
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON PS_valores_texto_aux_HASH_BY_VALUE([id_file])

CREATE UNIQUE NONCLUSTERED INDEX [partition_by_id_file] ON
t2
(
    id ASC,
    id_file
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON PS_valores_texto_aux_HASH_BY_VALUE([id_file])

But when I try to make a switch bettwen tables with this command:

ALTER TABLE t1 SWITCH PARTITION 1 TO t2 PARTITION 1

It returns me this error:

'ALTER TABLE SWITCH' statement failed. The index 'partition_by_id_file' is partitioned while table 't1' is not partitioned.

You've created partitioned indexes but haven't partitioned the underlying table by creating a partitioned clustered index.

Note that the partitioning column must be part of the key for partitioned unique indexes. The implication here is that you need to add the id_file column to the primary key so that the partitioned table is aligned, which is a requirement in order to use SWITCH.

For partitioned non-unique non-clustered indexes, the partitioning column doesn't need to be part of the key. It will be implicitly included as the row locator in the index leaf nodes, just like a clustered index key.


MySql IDB files not named after tables In my Linux filesystem, there are many ibd files (InnoDB file per table is ON) which are not named after tables. What are these files and what data do they contain? The question has arisen due to space limitations on the server.
4.2G    ./var/lib/mysql/wift/#sql-ib179-1438865579.ibd
4.2G    ./var/lib/mysql/wift/#sql-ib179-1413146901.ibd
4.2G    ./var/lib/mysql/wift/#sql-ib179-1376672335.ibd
4.2G    ./var/lib/mysql/wift/#sql-ib179-1355103119.ibd
4.2G    ./var/lib/mysql/wift/#sql-ib179-1163730678.ibd
128M    ./var/lib/mysql/wift/customers.ibd

That are orphaned temporary files, when mysql crashes during an alter table

Orphan temporary table names begin with an #sql- prefix (e.g., #sql-540_3). The accompanying .frm file has the same base name as the orphan temporary table.

see manual
There you will find also how to deal with them
in mysql 8 they don't appear any more


Backfill new column with values based on foreign key I'm new to RDS and am looking for help in updating a new column with ascending values based on a FK.

Can I and should I be doing this as a sequence of SQL commands that calculates my business logic on server-side?  This seems plausible to me because the business logic (ascending based on FK) seems straightforward.  Or should I be running client code to read rows, calculate business logic on client, then write back to db?

Would I be using function to achieve this?

Specifically, I am adding a new column ordinal to my devices table.  The default value is 0 and I would like to update that column to instead count ascending for a particular user.  So, if there are 3 rows for a given user in devices, those 3 rows would respectively have ordinal set to 1, 2, and 3.

CREATE TABLE devices (
    id character varying PRIMARY KEY,
    user_id uuid REFERENCES users(id) ON DELETE CASCADE,
);
ALTER TABLE "devices" ADD COLUMN "ordinal" integer DEFAULT 0 NOT NULL;

I am running PostgreSQL 9.6.6

Thank you

I have made some headway on a function.  For the update step when I set ordinal, is there any way to set the column through the context of the current iteration?  I.e. something like current.ordinal = new_val?

Inspiration: Looping Through Query Results

CREATE OR REPLACE FUNCTION backfill_device_ordinal(uuid) RETURNS integer AS $$
DECLARE
    device RECORD;
    ordinal INTEGER;
BEGIN
    ordinal = 1;
    FOR device IN SELECT * FROM devices WHERE user_id = $1 LOOP
        -- Now "device" has one record from "devices"
        RAISE NOTICE 'Updating device ordinal: user=%, ordinal=%...', device.user_id, device.seat_ordinal;

        /* is this optimal? */
        EXECUTE format('UPDATE devices SET seat_ordinal = %I WHERE user_id = "%s" AND id = "%s"', ordinal, device.user_id, device.id);

        ordinal = ordinal + 1;
    END LOOP;

    RAISE NOTICE 'Done updating device ordinal.';
    RETURN ordinal;
END;
$$ LANGUAGE plpgsql;

SELECT * from get_next_device('00000000-1111-2222-3333-444444444444')

You can use a correlated subquery to update all rows at once.
To determine the ordinal value, just count how many other rows for the same user come before this one when you sort them:

UPDATE devices
SET seat_ordinal = (SELECT COUNT(*)
                    FROM devices AS d2
                    WHERE d2.user_id  = devices.user_id
                      AND d2.id      <= devices.id);    -- some arbitrary sort order


Trying to write a function that doesn't require dbo. prefix I am trying to write a simple system function so that the dbo. prefix is NOT required to access the function. I have found an example of this for SQL Server 2000 but when I run

EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
go

create function system_function_schema.trim
    (@input varchar(max))
    returns  varchar(max)
as
begin
    declare @result varchar(max)

    select  @result = rtrim(ltrim(@input))

    return  @result
end
go

EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE

This is the error thrown.

  The specified schema name "system_function_schema" either does not exist or you
  do not have permission to use it. Severity 16

The expected usage would be

select trim('   padded value   ')

Does anyone know the approach I should be taking or if I HAVE to create it as a regular user defined function that must be called with the dbo. like :

select dbo.trim('   padded value   ')

Using system_function_schema was an undocumented and unsupported process in SQL 2000. As of SQL 2005, it is no longer possible.

By design, UDFs require that the schema be included in the function call.

There is one exception: calling the function like a procedure doesn't actually require the schema, but also wouldn't be terribly useful for your function.

exec trim @input = ' foo '

Additionally, be sure to carefully test queries with scalar functions--they can have significant impact on performance.


Privileges required to install PostgreSQL I am new to PostgreSQL Database. I need to install it in my machine.

I am not administrator in my system.

Can some one suggest, if  Non-Administrator can install it.

If there is any other way of installing apart from .exe, please let me know.

Thanks In Advance.

Try this Starting PostgreSQL in windows without install.
Follow the steps below:

Get binaries for Windows. You can either copy the postgresql folder
(minus the data folder) from an existing PostgreSQL install, or just
download the PostgreSQL binaries from PostgreSQL Windows. Make sure
to choose the zip archive.
Next copy the below batch file into the root of the postgresql folder
For first time use, unremark out the (initdb step)
Run the batch file:

.

@ECHO ON

REM The script sets environment variables helpful for PostgreSQL

@SET PATH="%~dp0\bin";%PATH%

@SET PGDATA=%~dp0\data

@SET PGDATABASE=postgres

@SET PGUSER=postgres

@SET PGPORT=5439

@SET PGLOCALEDIR=%~dp0\share\locale

REM "%~dp0\bin\initdb" -U postgres -A trust

"%~dp0\bin\pg_ctl" -D "%~dp0/data" -l logfile start

ECHO "Click enter to stop"

pause

"%~dp0\bin\pg_ctl" -D "%~dp0/data" stop


Find all tables that are related to a view recursively sql server I have a view-A that is comprised of other views (View-B View-C) and I would like to list all the tables that used by View-A (which would include the tables used in View-B and View-C).  When I use this code...

-- Get base tables for 'View-A' in Database-A
USE Database-A
GO
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = 'View-A'
ORDER BY view_name, table_name
GO

...I only get the tables that are immediately used by View-A plus the name of the other views (View-B, View-C).  Is there a way to get recursively the names of the tables that are also used by View-B and View-C?

I rewrote using sys tables as follows.

DECLARE @ViewName VARCHAR(MAX);
SET @ViewName = 'YOUR_VIEW_NAME';
WITH deps ( parent, child ) AS ( SELECT v.name ,
            sed.referenced_entity_name
     FROM   sys.sql_expression_dependencies sed
            INNER JOIN sys.views AS v ON sed.referencing_id = v.object_id
     WHERE  v.name = ISNULL(@ViewName, v.name)
     UNION ALL
     SELECT v.name ,
            sed.referenced_entity_name
     FROM   sys.sql_expression_dependencies sed
            INNER JOIN sys.views AS v ON sed.referencing_id = v.object_id
            INNER JOIN deps ON deps.child = v.name ) SELECT   parent ,
         child FROM     deps ORDER BY parent ,
         child;


Cannot connect to . A severe error occurred on the current command Recently, and on more than one occasion now, we have attempted to connect from one of our SQL Server instances to another using SSMS only to be greeted with the following error:

A severe error occurred on the current command. The results, if any, should be discarded.

The following error occurs when we attempt to connect using SSMS locally:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Connections via the Dedicated Administrative Connection (DAC) result in the same errors. SQLCMD reveals the following error:

SQL Server Configuration Manager reports that both the SQL Server service and the SQL Server Agent service are running.

The SQL Server error log stops at the same time as entries in the Event Viewer begin to be logged, reporting connection issues for SQL Server Agent jobs.

SQL Server Scheduled Job 'sp_WhoIsActive' (0x868EBDEC06F71344AC717E0C6A333D13) - Status: Failed - Invoked on: 2018-09-18 10:38:00 - Message: The job failed.  Unable to determine if the owner (sa) of job sp_WhoIsActive has server access (reason: Unable to connect to server - check SQL Server and SQL Server Agent errorlogs).

All of our SQL Server services have been configured to run under Group Managed Service Accounts (gMSA's).

The only solution so far has been to restart the servers, but clearly this is not a satisfactory solution, especially since the issue has now occurred more than once.

The only thing we have to go on is that there are numerous occurrences for the following error message in the SQL Server Error log before the connection issues occur.

2018-09-17 13:31:19.20 Logon       Login failed for user '[domain][gMSA account for SQL Agent]$'. Reason: Failed to open the explicitly specified database '[database_name]'. [CLIENT: [local machine]]
2018-09-17 13:32:03.63 Logon       Error: 18456, Severity: 14, State: 38.

Is it possible that the maximum number of connections has been exceeded as a result of these login issues? If so, why is it still not possible to connect via the DAC?

Is anyone please able to advise?

Further details:

Microsoft SQL Server 2016 (SP1-CU8) (KB4077064) - 13.0.4474.0 (X64)   Feb 24 2018 13:53:17   Copyright (c) Microsoft Corporation  Web Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)
SQL Server Management Studio v17.8.1
Maximum number of user connections is set to unlimited (0).
Shared Memory, TCP/IP and Named Pipes are all enabled.

When applying any changes to the SQL Server services it is advisable to do this via the SQL Server xxxx Configuration Manager to guarantee that any related permissions (files / directories / databases / instances) and policies are set correctly.

Fixing Service Accounts (Permissions and All)

We will reset all the permissions for files, directories and registry settings incl. any policies that may exist according to the requirements of the service accounts by following these quick steps.

Change the Service Accounts to Local Accounts

Open up the SQL Server 2016 Configuration Manager and navigate to the SQL Server Services branch.
In the right pane of the Configuration Manager right-click on the SQL Server service and select Properties.
In the Properties of SQL Server (InstanceName) window check that you are in the Log On tab.
Change the selector to Built-in account and select one of the three options:

Local System
Local Service (preferred; least privileged)
Network Service

Press Apply and when I dialog pops up notifying you that "...any change you have made will not take effect until you reboot" click on OK.
Stop the SQL Server Service (InstanceName)
Start the SQL Server Service (IntanceName)
Double-check the SQL Server ERRORLOG for any issues.
Repeat steps 2 to 8 for the SQL Server Agent (InstanceName) service.

Change the Service Accounts back to Group Managed Service Accounts

Open up the SQL Server 2016 Configuration Manager and navigate to the SQL Server Services branch.
In the right pane of the Configuration Manager right-click on the SQL Server service and select Properties.
In the Properties of SQL Server (InstanceName) window check that you are in the Log On tab.
Change the selector to This account and enter the details of your gMSA for the SQL Server (InstanceName) service.
Press Apply and when a dialog pops up notifying you that "...any change you have made will not take effect until you reboot" click on OK.
Stop the SQL Server Service (InstanceName)
Start the SQL Server Service (IntanceName)
Double-check the SQL Server ERRORLOG for any issues.
Repeat steps 2 to 8 for the SQL Server Agent (InstanceName) service.

The services should now have been re-configured according to the requirements of the individual services.


Algorithm for populating tables I'm writing an algorithm to populate all tables in a database in Postgres. I already get all tables that have no relationship populated, as follows:

I get all the tables with the following query:

SELECT table_name
   FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND
   table_schema NOT IN ('pg_catalog', 'information_schema')

For the list of tables obtained, I make a loop, and for each table I get its properties (column name, data type, accepts null or not, maximum characters):

SELECT COLUMN_NAME, DATA_TYPE,
   IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'TABLE_NAME'

With this list of properties I dynamically create the INSERT

INSERT INTO TABLE_NAME(prop1, prop2, prop3) VALUES(value1, value2, value3)

Each value is randomly generated based on the property type, eg:

Integer: 65422
Character: "Lorem ipsum..."
Date: 2016-12-12 20:00

And so on for each data type accepted by the algorithm. If it finds some kind of data that is not accepted, the application terminates (which is expected).

And finally, with INSERT mounted I execute the query in the database

The steps in this algorithm work perfectly for tables where there are no relationships.

For tables that have relationships (1:N, 1:1, N:N), I would need to first find all the foreign keys and understand which tables they see, to enter the data in this table "Father" save the IDs and then insert In the "Daughters" tables thus associating their foreign keys. But how to do it in a simple way?

It is interesting to note that I can already get the list of all foreign keys through the following query:

SELECT conrelid::regclass AS table_from
      ,conname
      ,pg_get_constraintdef(c.oid)
FROM   pg_constraint c
JOIN   pg_namespace n ON n.oid = c.connamespace
WHERE  contype IN ('f', 'p ')
AND    n.nspname = 'public' -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;

I am grateful for suggestion.

Note: The algorithm is being developed in Python.

You can use a query like below to get all tables with dependencies;

WITH fkeys AS (
SELECT

  c.conrelid AS table_id,
  c_fromtablens.nspname AS schemaname,
  c_fromtable.relname AS tablename,

  c.confrelid AS parent_id,
  c_totablens.nspname AS parent_schemaname,
  c_totable.relname AS parent_tablename

FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace

JOIN pg_class c_fromtable ON c_fromtable.oid = c.conrelid
JOIN pg_namespace c_fromtablens ON c_fromtablens.oid = c_fromtable.relnamespace

JOIN pg_class c_totable ON c_totable.oid = c.confrelid
JOIN pg_namespace c_totablens ON c_totablens.oid = c_totable.relnamespace
WHERE
  c.contype = 'f'
)

SELECT
  t.schemaname,
  t.tablename,
  fkeys.parent_schemaname,
  fkeys.parent_tablename

FROM pg_tables t
LEFT JOIN fkeys ON  t.schemaname = fkeys.schemaname AND
                    t.tablename =  fkeys.tablename
WHERE
  t.schemaname NOT IN ('pg_catalog', 'information_schema')

ORDER BY
  3 NULLS FIRST,
  4 NULLS FIRST

Moreover, the query below provides you foreign key details. It provides relation types by using primary key definition. Note that it does not check unique constrains.

WITH fkey AS (

SELECT
  n.nspname AS fkey_schema_name,
  c.conname AS fkey_name,

  c_fromtablens.nspname AS table_schema_name,
  c_fromtable.relname AS table_name,

  c_totablens.nspname AS foreign_table_schema_name,
  c_totable.relname AS foreign_table_name,

  c.conrelid,
  unnest(c.conkey) AS fkey_field_num,

  c.confrelid,
  unnest(c.confkey) AS fkey_foreign_field_num

FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace

JOIN pg_class c_fromtable ON c_fromtable.oid = c.conrelid
JOIN pg_namespace c_fromtablens ON c_fromtablens.oid = c_fromtable.relnamespace

JOIN pg_class c_totable ON c_totable.oid = c.confrelid
JOIN pg_namespace c_totablens ON c_totablens.oid = c_totable.relnamespace
WHERE
  c.contype = 'f'

),

pkey AS (

SELECT
  c.conrelid,
  unnest(c.conkey) AS conkey
FROM pg_constraint c
WHERE
  c.contype = 'p'
)

SELECT
  fkey.fkey_schema_name,
  fkey.fkey_name,
  fkey.table_schema_name,
  fkey.table_name,
  fkey.foreign_table_schema_name,
  fkey.foreign_table_name,
  a.attname AS field_name,
  a_f.attname AS foreign_field_name,
  CASE
    WHEN pkey.conrelid IS NULL AND pkeyf.conrelid IS NULL THEN 'N-N'
    WHEN pkey.conrelid IS NOT NULL AND pkeyf.conrelid IS NOT NULL THEN '1-1'
    WHEN pkey.conrelid IS NULL AND pkeyf.conrelid IS NOT NULL THEN 'N-1'
  END AS relation_type
FROM fkey
JOIN pg_attribute a ON a.attrelid = fkey.conrelid AND a.attnum = fkey.fkey_field_num
JOIN pg_attribute a_f ON a_f.attrelid = fkey.confrelid AND a_f.attnum = fkey.fkey_foreign_field_num
LEFT JOIN pkey ON pkey.conrelid = a.attrelid AND pkey.conkey = a.attnum
LEFT JOIN pkey pkeyf ON pkeyf.conrelid = a_f.attrelid AND pkeyf.conkey = a_f.attnum


Querying special character from SQL Server When I saved special characters in a column of type nvarchar, I did not use the prefix 'N', so the characters got saved as ??.

Is there any way to retrieve the original special characters from these question marks?

Any kind of suggestion will be appreciated.

Unfortunately no, if you have written special characters on a VARCHAR and not NVARCHAR (by omitting the N), their binary storage representation changed and there is no way to retrieve the original. Please note that this only applies when writting explicit strings as they need the N before the literal, if it's assigned throughout an update (for example) with a NVARCHAR column, the values will still retain their correct values.

You can see differences on the following examples. Here we write unicode chinese characters to an NCHAR and display both NCHAR and CHAR versions (their behaviour is similar to NVARCHAR and VARCHAR respectively, for this matter):

DECLARE @char NCHAR(1) = N'人'

SELECT
    NChar = @char,
    Char = CONVERT(CHAR(1), @char)

Result:

NChar   Char
人       ?

Here you can see the data loss, we show the binary representation of both the NCHAR and the CHAR with 2 different unicode characters. Please note that both are different characters but they are stored as the same on VARCHAR / CHAR data types.

DECLARE @char NCHAR(1) = N'人'
DECLARE @char2 NCHAR(1) = N'物'

SELECT
    NChar = @char,
    Char = CONVERT(CHAR(1), @char),
    NChar2 = @char2,
    Char2 = CONVERT(CHAR(1), @char2)

SELECT
    BinaryNChar = CONVERT(VARBINARY, @char),
    BinaryChar = CONVERT(VARBINARY, CONVERT(CHAR(1), @char)),
    BinaryNChar2 = CONVERT(VARBINARY, @char2),
    BinaryChar2 = CONVERT(VARBINARY, CONVERT(CHAR(1), @char2))

Results:

NChar   Char    NChar2  Char2
人       ?       物       ?

BinaryNChar BinaryChar  BinaryNChar2    BinaryChar2
0xBA4E      0x3F        0x6972          0x3F

NVARCHAR holds 2 bytes for each character while VARCHAR just 1. Considering this, you can't revert the conversion back from VARCHAR to NVARCHAR for special characters.


assignment in trigger function does not work For the table, an AFTER trigger is supposed to update the tsvector column on insert and update. The assignment works outside the function using an update statement. Also, the notice in the BEGIN block appears in the log as desired. However, the assignment is not made when the trigger fires. Columns in the update or insert are updated; only the fts_vector column is empty.

Logged on user is part of a group that has execute privileges on the function and insert/update privileges on the table.

What might be preventing this from working? All answers and suggestions are welcome.

CREATE TABLE customer
(
  name character varying(1000) NOT NULL,
  address character varying(1000),
  customer_uid serial NOT NULL,
  fts_vector tsvector,
  CONSTRAINT customer_pkey PRIMARY KEY (customer_uid);

CREATE OR REPLACE FUNCTION vector_update_customer()
  RETURNS trigger AS
$BODY$
BEGIN
    RAISE NOTICE 'vector_update_customer invoked';
    NEW.fts_vector = to_tsvector('english', coalesce(NEW.name,'')  || ' ' || coalesce(NEW.address,''));
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'vector_update_customer failed';
END
$BODY$
  LANGUAGE plpgsql VOLATILE

CREATE TRIGGER trigger_vector_update_customer
  AFTER INSERT OR UPDATE
  ON customer
  FOR EACH ROW
  EXECUTE PROCEDURE vector_update_customer();

I found that changing the table trigger to a BEFORE trigger allowed the function to work as desired. While I am glad that it works and now understand that NEW is only valid in a BEFORE trigger, I am left with a question: what is the correct way to refer to the inserted or updated row in an AFTER trigger?


Oracle build_plan_xml visualizer Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML

I now have a very nice looking Execution Plan display for SQL Server (as a result of my previous question), and am hoping that lightning could strike twice here and that I might find a similarly high-quality solution for Oracle.

My site SQL Fiddle displays execution plans for each of the queries that you run on it, for each of the database types I support (at the moment - MS SQL, MySQL, PGSQL, and Oracle).  The Oracle execution plan is built in this manner:

explain plan set STATEMENT_ID = '#schema_short_code#/#query_id#' for
select id, type, details from supportContacts

/

select
dbms_xplan.build_plan_xml(statement_id => '#schema_short_code#/#query_id#').getclobval()
AS XPLAN
FROM dual

Where schema_short_code and query_id are obviously variables representing each distinct query.  You can see the result of the above example running here: http://sqlfiddle.com/#!4/ee7da/596

This produces the following XML:

<plan>
  <operation name="SELECT STATEMENT" id="0" depth="0" pos="2">
    <card>2</card>
    <bytes>94</bytes>
    <cost>2</cost>
    <io_cost>2</io_cost>
    <cpu_cost>7501</cpu_cost>
    <time>00:00:01 </time>
  </operation>
  <operation name="TABLE ACCESS" options="FULL" id="1" depth="1" pos="1">
    <object>SUPPORTCONTACTS</object>
    <card>2</card>
    <bytes>94</bytes>
    <cost>2</cost>
    <io_cost>2</io_cost>
    <cpu_cost>7501</cpu_cost>
    <time>00:00:01 </time>
    <project>&quot;ID&quot;[NUMBER,22], &quot;TYPE&quot;[VARCHAR2,20], &quot;DETAILS&quot;[VARCHAR2,40]</project>
    <qblock>SEL$1</qblock>
    <object_alias>SUPPORTCONTACTS@SEL$1</object_alias>
    <other_xml>
      <info type="db_version">11.2.0.2</info>
      <info type="parse_schema"><![CDATA["USER_EE7DA"]]></info>
      <info type="dynamic_sampling">2</info>
      <info type="plan_hash">2812004906</info>
      <info type="plan_hash_2">2012527029</info>
      <outline_data>
        <hint><![CDATA[FULL(@"SEL$1" "SUPPORTCONTACTS"@"SEL$1")]]></hint>
        <hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
        <hint><![CDATA[ALL_ROWS]]></hint>
        <hint><![CDATA[DB_VERSION('11.2.0.2')]]></hint>
        <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]]></hint>
        <hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
      </outline_data>
    </other_xml>
  </operation>
</plan>

Although reading the raw XML is not terrible, it certainly does not look anywhere nearly as nice as what I now am able to show for SQL Server (compare by clicking "View Execution Plan" on this page: http://sqlfiddle.com/#!3/1fa93/1)

Has anyone worked with this XML before?  Is it possible that someone might have built something close to what Justin built for SQL Server (see answerer for the SQL Server question)?  If not, does anyone know where I can find some good direction for building my own XSLT (schema documentation, etc....), and possibly where I can look to see some nice example query plan visualizers for Oracle that I could hope to emulate?

Update

Since there are no answers yet provided, and the bounty is about to expire, I'll happily accept an answer that simply can tell me anything useful about this XML schema, or points me in the direction of any graphical execution plan display for Oracle (even desktop based GUIs).

I think you'd be better off using the text version of the explain plan output rather than XML, as it gives you a pre-formatted readable ascii representation of the query plan.

eg:

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','#schema_short_code#/#query_id#','ALL'));

Failing that, if you have the means to do so server-side, you could use Oracle SQL Developer to give you a graphical representation of the query plan - Though this is really just the same as the ASCII version with a few icons.

Also, take a look at $ORACLE_HOME/rdbms/xml/orarep/xplan/xplSch.xsd , $ORACLE_HOME/rdbms/xml/orarep/xplan/xplHtml.xsl and $ORACLE_HOME/rdbms/xml/xsl/kuplan.xsl - They may help you format your XML output to prettier HTML.


RMAN Restore Validate time equals to actual restore time? I'd like to know if the time to restore the database is equal the time using restore validate database. If not, How can I know how much time the restore will spend?

RESTORE VALIDATE reads the backups, but it skips writing the files to the disk.
If writing files to the disk during restore is the limiting factor, then it will be faster. Otherwise it will be the same.

Depending on the backup technology in use, this may or may not be a valid method for estimating the time of a real restore. For example, when using tape backups, the placement of backups and the number of currently available drives may vary.


How to monitor backup jobs on Oracle 11g? Background:

I'm trying to monitor that backups run as expected on several databases. Zenoss is used to monitor most other stuff.

A couple of KPIs are already monitored using Zenoss in combination with script that runs sqlplus on database host.

What I've found so far:

Views in database:

V$RMAN_STATUS
V$RMAN_BACKUP_JOB_DETAILS;
V$RMAN_BACKUP_SUBJOB_DETAILS;

Stop gap solution:

Sending alerts from Enterprise Manager:

Setup > Monitoring Templates > Create > Target : some Database > Metric Threshold > Failed Job Count

or

Preferences > Rules > Create > Tab "General" - Target Type: Database Instance, Tab "Jobs" - Specific Jobs > Add Specific Jobs: Select the job and Job Status: "Problem"

What I'd like to know:

Does this seem reasonable or is there another preferred way to integrate monitoring of Oracle into a third party nms? (Polling is preferred to traps in the current setting. )
Pitfalls to be aware of.
There seems to be significant differences between the database views I mentioned and what I see in EM backup history. Where should I be looking for the status of past backups?

I might have found a couple of answers on my own. Posting them here in case others need to know:

Google (or my google skills) seems to be getting increasingly less reliable.
Searching the forums at https://forums.oracle.com/ I found this thread: https://forums.oracle.com/forums/thread.jspa?messageID=9956411, which references http://www.morganslibrary.org/reference/dba_best_practices.html. Here is the code mentioned:

SELECT start_time, end_time, input_type, input_type, status
FROM v$rman_backup_job_details
ORDER BY 1;

and
SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status
FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd
WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time
AND vrbjd.input_type <> 'ARCHIVELOG'
ORDER BY 2,1;

As for why the query in Enterprise Manager picked older jobs I learned a nice trick from someone at work: You can read recent queries from the view v$sqlarea. Something like select sql_text from  v$sqlarea order by last_active_time desc should do the trick.


SQL Sentry Plan Explorer - Execution Mode in plan diagram? Does Plan Explorer show the Execution Mode (estimated/actual) in the plan diagram when hovering over an operator? I'm not able to find it. I do see it in the Plan Tree and Top Operations pane.

Below is SSMS where it is shown vs Plan Explorer where it is not.

It doesn't show it in the plan diagram tooltip but in the plan tree tab, go to column chooser and select estimated and actual execution mode.


querying polymorphic relationship I'm not really expert in backend. I have here some columns of notification table which has been used by a polymorphic relationship in Laravel.
+----+------------------+----------------+
| id | sendto_type      |   sendto_id    |
+----+------------------+----------------+
|  1 | App\User         |              1 |
|  2 | App\User         |              2 |
|  3 | App\Station      |              1 |
|  4 | App\Station      |              2 |
+----+------------------+----------------+

I want to query only from sendto_type column that has App\User attribute on it. Is it possible? and if yes, how would I do that.
These  are some columns from the users table which I referred on App\User
+----+------------------+----------------+
| id | firstname        |   lastname     |
+----+------------------+----------------+
|  1 | dummy            |    imdummy     |
|  2 | dummy2           |    imdummy2    |
|  3 | dummy3           |    imdummy4    |
|  4 | dummy4           |    imdummy3    |
+----+------------------+----------------+

I tried querying SELECT * FROM notifications WHERE sendto_type = "App\User". It was successful but returns no result. Please help

your query should work in mysql

CREATE TABLE notification (
  `id` INTEGER,
  `sendto_type` VARCHAR(11),
  `sendto_id` INTEGER
);

INSERT INTO notification
  (`id`, `sendto_type`, `sendto_id`)
VALUES
  ('1', 'App\User', '1'),
  ('2', 'App\User', '2'),
  ('3', 'App\Station', '1'),
  ('4', 'App\Station', '2');

CREATE TABLE user (
  `id` INTEGER,
  `firstname` VARCHAR(6),
  `lastname` VARCHAR(8)
);

INSERT INTO user
  (`id`, `firstname`, `lastname`)
VALUES
  ('1', 'dummy', 'imdummy'),
  ('2', 'dummy2', 'imdummy2'),
  ('3', 'dummy3', 'imdummy4'),
  ('4', 'dummy4', 'imdummy3');

SELECT n.id,u.* FROM notification n inner JOIN user u ON  u.id = n.sendto_id WHERE `sendto_type` = "App\User"

id | id | firstname | lastname
-: | -: | :-------- | :-------
 1 |  1 | dummy     | imdummy
 2 |  2 | dummy2    | imdummy2

db<>fiddle here


How mongoDB projection affects performance? From MongoDB documentation it is mentioned that:

  When you need only a subset of fields from documents, you can achieve
  better performance by returning only the fields you need

How filtering fields affect performance? Is the performance related to the size of data that is transmitted over network? or the size of data that will be hold in memory? How exactly this performance is improved? What is this performance that is been mentioned in documentation?

I have slow MongoDB queries. Is returning a subset affect my slow query (I have compound index on the field)?

By default, queries return all fields in matching documents. If you need all the fields, returning full documents is going to be more efficient than having the server manipulate the result set with projection criteria.

However, using projection to limit fields to return from query results can improve performance by:

removing unneeded fields from query results (saving on network bandwidth)
limiting result fields to achieve a covered query (returning indexed query results without fetching full documents)

When using projection to remove unused fields, the MongoDB server will have to fetch each full document into memory (if it isn't already there) and filter the results to return. This use of projection doesn't reduce the memory usage or working set on the MongoDB server, but can save significant network bandwidth for query results depending on your data model and the fields projected.

A covered query is a special case where all requested fields in a query result are included in the index used, so the server does not have to fetch the full document. Covered queries can improve performance (by avoiding fetching documents) and memory usage (if other queries don't require fetching the same document).

Examples

For demonstration purposes via the mongo shell, imagine you have a document that looks like this:

db.data.insert({
    a: 'webscale',
    b: new Array(10*1024*1024).join('z')
})

The field b might represent a selection of values (or in this case a very long string).

Next, create an index on {a:1} which is a commonly used field queried by your use case:

db.data.createIndex({a:1})

A simple findOne() with no projection criteria returns a query result which is about 10MB:

> bsonsize(db.data.findOne({}))
10485805

Adding the projection {a:1} will limit the output to the field a and the document _id (which is included by default). The MongoDB server is still manipulating a 10MB document to select two fields, but the query result is now only 33 bytes:

> bsonsize(db.data.findOne({}, {a:1}))
33

This query isn't covered because the full document has to be fetched to discover the _id value. The _id field is included in query results by default since it is the unique identifier for a document, but _id won't be included in a secondary index unless explicitly added.

The totalDocsExamined and totalKeysExamined metrics in explain() results will show how many documents and index keys were examined:

 > db.data.find(
     {a:'webscale'},
     {a:1}
 ).explain('executionStats').executionStats.totalDocsExamined
 > 1

This query can be improved using projection to exclude the _id field and achieve a covered query using only the {a:1} index. The covered query no longer needs to fetch a ~10MB document into memory, so will be efficient in both network and memory usage:

 > db.data.find(
     {a:'webscale'},
     {a:1, _id:0}
 ).explain('executionStats').executionStats.totalDocsExamined
 0

 > bsonsize(db.data.findOne( {a:'webscale'},{a:1, _id:0}))
 21

  I have slow MongoDB queries. Is returning a subset affect my slow query (I have compound index on the field)?

This isn't answerable without the context of a specific query, example document, and the full explain output. However, you could run some benchmarks in your own environment for the same query with and without projection to compare the outcome. If your projection is adding significant overhead to the overall query execution time (processing and transferring results), this may be a strong hint that your data model could be improved.

If it's not clear why a query is slow, it would be best to post a new question with specific details to investigate.


"Waiting for initial communication packet" error 0 in MySQL I am using MySQL server 5.1.51 on a Linux machine.

The application was working fine for the last 32 days, but since yesterday a number of clients are not able to connect to the database from our application. They are getting the following error:

-2147467259 [MySQL] [ODBC 5.1 Driver] Lost Connection to MySQL server
       at 'waiting for initial communication packet',system error: 0

What can I do to fix this? Where is my.ini located in Windows XP?

Adding the following database parameter to my.ini solved my problem:

skip_name_resolve

This parameter skips host name resolution during connection establishment with the MySQL database server.


Which SQL Server license is appropriate for my use case? Can I use a free license? Mod note: This question is a poor fit or dba.se, but I am leaving this question because the number of people who are new to the field on an annual basis is still high, and licensing is always the hardest thing to navigate. Sometimes a helpful hand is all that we need to guide us on our journey. Sometimes we need to pay someone to teach us or review our work. Every journey will be different, and so occasionally we make exceptions. ~ jcolebrand

I have couple of databases that are used for training classes every now and then. Do I have to host these on a production server with SQL licenses or can they be treated as a non-production databases for licensing reasons?

Free Edtions
SQL Server 20xx Express Editions
SQL Server 2019 Express Edition is free for production, non-production, and redistribution.  After install you'll find the license terms at
C:\Program Files\Microsoft SQL Server\150\License Terms\SQLServer2019_License_EXPR_1033.rtf
SQL Server 20xx Developer Editions
If you install the free SQL Server 2019 Developer Edition, you'll find the license terms at
C:\Program Files\Microsoft SQL Server\150\License Terms\SQLServer2019_License_DEV_1033.rtf
Which reads in relevant part:

Non-free Editions
For for the non-free editions of SQL Server refer to the licensing guide here.
You can also run these editions of SQL Server for non-production purposes if you have an appropriate Visual Studio Subscription (formerly called MSDN).


Single listener for multiple Oracle homes? I'm in the process of re-hosting two Oracle 12c databases on Unix. In the source environment, the databases were stacked -- they shared the same Oracle home and used the same listener. The listener looked roughly like this:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = foo)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = /oracle)
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = /oracle)
    )

In the new environment, each of the databases has its own Oracle home. I have a listener running out of one of the oracle homes. It looks roughly like this (note the ORACLE_HOMEs):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = newfoo)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = /oracle/DATABASE1)
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = /oracle/DATABASE2)
    )

I would like for each environment to have its own listener running, but that would mean one of the databases would need to change its port (we're not using virtual IPs so I can't have two databases on the same host using the same port).

(For the sake of argument, imagine that the client is running tons of legacy software and they can't easily change which ports they're pointed at. I realize asking them to simply modify their tnsnames is the easiest option, but let's set that aside for the moment.)

Question: Is it problematic to use a single listener for multiple Oracle homes? I get that it's a single point of failure, but is there also a performance hit? Something else I'm not thinking of?

Question: Is there some other way I can run a listener out of each of the new Oracle homes using the same port? Again, we're not using virtual IPs, so I'm stuck with new host "foonew" + port 1521.

Thank for any answers or guidance you can provide.

Question: Is it problematic to use a single listener for multiple
  Oracle homes?

No.  There really is no valid reason to run multiple Listeners.

You just need to make sure each database "registers" with that one Listener.

  I get that it's a single point of failure, but is there
  also a performance hit?

I'd be more concern with your single node databases being a "single point of failure" before I worry about the Listener as a "single point of failure".

  Something else I'm not thinking of?

K.I.S.S.:  Just use a single Listener.
The Listener does not have to be on the same server as the database
I believe you can use Oracle Grid to run a collection of Listeners.

  Question: Is there some other way I can run a listener out of each of
  the new Oracle homes using the same port? Again, we're not using
  virtual IPs, so I'm stuck with new host "foonew" + port 1521.

No.  This is an OS thing, not a Listener thing.


SSL Configuration fails with a warning MySQL 8.0 Hi I am trying to set up a MySQL server 8.0.16 on RHEL 7.5. The configuration I am willing to include for this server contains SSL related options.

The MySQL server is initialized but with a warning related to SSL, which is as follows:

mysqld --defaults-file=/mysql_data/config/options.cnf --initialize --user=mysql &

[root@BFLBRESRV03 ~]#  100
 100 200 300 400 500
 100 200 300 400 500
 100
2019-12-13T13:04:14.071486Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2019-12-13T13:04:14.071739Z 0 [System] [MY-013169] [Server] /mysql_binaries/mysql/mysql-server-8.0.16/bin/mysqld (mysqld 8.0.16-commercial) initializing of server in progress as process 962
2019-12-13T13:04:19.318987Z 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed
2019-12-13T13:04:19.498482Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: geY0lgrNZd+u
2019-12-13T13:04:20.958898Z 0 [System] [MY-013170] [Server] /mysql_binaries/mysql/mysql-server-8.0.16/bin/mysqld (mysqld 8.0.16-commercial) initializing of server has completed

[1]+  Done                    mysqld --defaults-file=/mysql_data/config/options.cnf --initialize --user=mysql

[Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed

The directory does not contain the ca.pem file but it does contain private_key.pem, and public_key.pem.

My options file contains following ssl configuration

[mysqld]

#       SSL     #

ssl-cipher                                          =               DHE-RSA-AES256-SHA

ssl_ca                                               =               /mysql_data/ssl-certs/ca.pem

ssl_cert                                             =               /mysql_data/ssl-certs/server-cert.pem

ssl_key                                             =               /mysql_data/ssl-certs/server-key.pem

require_secure_transport                 =               ON

tls_version                                        =               TLSv1.2

If I initialize the server without ssl-ca, ssl-cert, ssl-key options, the server is successfully initialized without any warnings and the data directory contains the files ca-key.pem,  client-cert.pem, public_key.pem, server-key.pem, ca.pem, client-key.pem, private_key.pem, server-cert.pem.

Kindly help me understand what mistake I am doing. What is the warning about and why the ca.pem certificate file was not created?

Can I create the SSL certificates in directories other than data directory?

I know i can later create the certificates using openssl or mysql_ssl_rsa_setup.

https://serverfault.com/questions/839650/mysql-ssl-ssl-ctx-set-default-verify-paths-failed

My SELinux is in PERMISSIVE mode.

MySQL ssl setup failed

The directory for SSL certificates that I was using /mysql_data/ssl-certs is owned by mysql user and group.

It was a silly mistake.

I was referring to the certificates which didn't exist and I was using wrong directory name.

First, one must create the certificates, using mysql_ssl_rsa_setup/openssl and place them in the designated directory.

mysql_ssl_rsa_setup --datadir=/mysql_data/ssl_certs --uid=mysql

Use following configuration under the [mysqld] section

ssl-ca=/mysql_data/ssl_certs/ca.pem
ssl-key=/mysql_data/ssl_certs/server-key.pem
ssl-cert=/mysql_data/ssl_certs/server-cert.pem

Then initialise the database using

mysqld --defaults-file=/mysql_data/config/options.cnf --user=mysql --initialize

In my case even this did not work.

The mistake I was doing was using incorrect directory name;
I was using /mysql_data/ssl-certs instead of the correct one /mysql_data/ssl_certs.


CPU usage on RDS instance monotonically increasing with no change to query volume I have two RDS instances: a R/W master and a read-only replica.

On 29th June, the replica stopped registering replication data - not sure if this is related.

On July 3rd, the master's CPU usage started increasing monotonically, and drastically:

It's almost at critical 100% now.

The query volume and hasn't really changed, to my knowledge. The only thing which happened around then was a django-celery daemon in my web tier grabbed a whole CPU core - a force kill of that seemed to fix problems on the web tier, but it seems likely that the DB tier problems may be related.

DB size also started increasing monotonically at the same time:

There are no long queries in the processlist, and no INSERTs at all really, so I'm not sure how to find out which tables are growing, and where that CPU is going.

Are there MySQL diagnostics while can show me table size trends? Profile global ongoing queries? Profile global CPU usage?

I've already rebooted the server a couple of times, to no avail.

There's obviously still lots of space left on the server, but when we get to 100% CPU usage, things are going to get ugly, so any help much appreciated!

I have some queries for you regarding table sizes that you can run in MySQL during these spikes

Database size in terms of StorageEngine (MB)

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(REPLACE(FORMAT(B.TSize/
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Database size in terms of Databases (MB)

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT
IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

Database size in terms of Database/StorageEngine (MB)

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(REPLACE(FORMAT(
B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Pay attention to certain markers

Innodb_buffer_pool_pages_dirty
Innodb_data_reads
Innodb_data_writes

I recommend downloading MySQL Administrator (I know, it's old but I still you it for quick and dirty "I WANNA SEE STATS NOW" moments of day) and set it up. I customized my own graphs to watch the size of the InnoDB Buffer Pool and its dirty pages. You could also just use the Connection Health tab.


what is the fastest way to restore MySQL database without using dump files what is the best procedure to quickly restore the MySQL database without using dump files. can any body help me in this regard.
Thanks in advance.

Ofcourse a physical files restore will be quicker than loading a dump. So, if you have full physical backup / snapshot of mysql data-directory - you can go fast!
(Below answer assumes that you have physical backup available.)
Tables are MyISAM:

Stop mysql
Copy database folder to mysql datadir
Correct permissions
Start mysql

Tables are InnoDB:
If we have InnoDB tables in the picture then things are not as simple. Considering you have Xtrabackup.
You will have  to import tables one after another.
Refer Importing-Exporting-Tables-using-Xtrabackup OR how-to-recover-a-single-innodb-table-from-a-full-backup
(For this to work you should have used xtrabackup as backup option.)
Splitting mysqldump
If you want to extract only one database from full mysqldump then you might want to extract single database from mysqldump and load it (saves you from loading full dump).


PostgreSQL: how do I list all triggers that use a specific field? for example i have a student table with (studentid)
i want to get a list of triggers that use this field...

for example if in table X there is a trigger that uses studentid i want it to be shown in the query result.

You can do that by querying pg_proc and pg_trigger,

SELECT t.tgname FROM pg_trigger t, pg_proc p
WHERE t.tgfoid=p.oid AND p.prosrc like '%studentid%';


incomplete sysaux expdp unload (ORACLE) my question is really simple, and does not need any particular documentation.
When I run expdp command like this using Oracle Database 11g Release 11.2.0.4.0 - 64bit:

expdp system/******* directory=DATA_PUMP_DIR dumpfile=SYSAUX.dmp logfile=sysaux.log TABLESPACES=SYSAUX

I see that no table about SYSTEM schema is present.
I know for sure that tables in SYSTEM schema belong to SYSAUX tablespace, like DBA_SEGMENTS and DBA_EXTENTS tell me, but I don't see any log row like this:

.. exported "SYSTEM"."TABLENAME"                       120.2 GB 8396874 rows

No error message with expdp, just missing tables.....
Could anyone please give me suggestions about this?

Actually SYS.AUD$ table CAN be exported but only using old export command exp instead of expdp.

exp \"/ as sysdba\" file=aud_table.dmp tables=sys.aud$;

Since 11g or 10 (not sure) AUD$ table is prohibited to be exported.

The most convenient way is what @Balazs Papp suggested, to create table AUD$_BACKUP as select * from SYS.AUD$


How do I find out if a procedure or function exists in a mysql database? How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)

A generic answer to this type of question is that all MySQL databases include a database called information_schema which includes all the metadata as tables you can just query.

The information you want is in a table called ROUTINES.  For example:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
       ROUTINE_TYPE="PROCEDURE"
   AND ROUTINE_SCHEMA="dbname"
;


How to resolve this SQL Server 2016 master data services authentication error on the web application? I just installed MS SQL server, IIS and Master Data services, 2016 version. Everything seems fine, except the web application doesn't work. I am able to go to the home screen, but there are no models/versions listed in the Model: and Version: dropdown, even though I added two models using the MDSModelDeploy utility. The UI doesn't give me any error, but Chrome dev tools shows "401 Unauthorised" error.

So basically, the url "http://server-name:port/api/Models" is unreachable, giving "authorization has been denied for this request" error.

I've checked the usual places (allowing anonymous auth etc), they all seem correct. What else should I try?

Thanks to @sqlworldwide for pointing me to this question, which worked

https://stackoverflow.com/questions/23358903/troubleshooting-master-data-services-mds-permission-authorization


Is it possible to have a deadlock in this situation I am working with SqlServer and Java.

I have the following (pseudo) code :

begin transaction
  For each item of a list
    select the row in db
    ...
    update the row
  End for
end transaction

Is it possible to have a deadlock in this situation if two transactions are running with the same items ?

My understanding is the following :
For each item the select take a Shared lock on the row, and next the lock is upgraded to a Exclusive lock for the update. The lock are released at the end of the transaction. Is it true ? If yes I think that could explain a deadlock, if the first transaction tries to read a row locked by the second transaction that is trying to read a row already locked too, isn't it ?

Thks

Assuming you're working with read committed isolation level (=the default), then the deadlock will happen when a reads the row, then b reads the row, then a tries to update it and then b tries to update it.

In addition to deadlocks you might have another problem if you're dealing with the same rows and the process isn't that fast, since the second process will be stuck until the first one completes the whole transaction (if deadlock doesn't happen).

If the blocking doesn't matter, you can take an exclusive lock on the row when you're selecting the data by specifying lock hints, most likely the correct ones in this case would be UPDLOCK and HOLDLOCK, see https://msdn.microsoft.com/en-us/library/ms187373.aspx


Is there an actual problem in my storage IO or is the data incorrect? Running SP_Blitz indicated that I have a slow read/write performance on my drives. Reading further, I eventually landed on this query to view the actual statistics of IO occurring on my drives:

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
a.database_id,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
                   WHEN a.file_id = 2 THEN 'Log'
                   ELSE 'Data'
                   END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
ORDER BY a.io_stall DESC

*Row #1 is the database mdf, #2 is transaction log

According to the result, my database should practically be unusable but this isn't the case. We're operating a fairly large OLTP database with hundreds of transactions occurring at any given second, yet there isn't any sign of performance issues (at least none that we heard from our customers).

Should I actually be worried about the above statistics? Is there an actual  problem I need to address or is it simply the case that query is incorrect for our case?

If there is a problem, where should I start?

You need to divide io_stall_read_ms by num_of_reads, and io_stall_write_ms by num_of_writes. This will give you something more sensible as what you are seeing for read and write stalls and sum totals since your instance last stated up.

However, using this DMV in this manner can average out any spikes and hide patters, therefore you need to take samples from it every so often. Also I would recomend putting looking at your page life expectancy in conjunction with read latencies, go off Jonathan Kehayias's formula from SQL Skills.

Note that you need to look at PFE per NUMA node and if your application is OLAP you are unlikely to get a great PFE, your sequential scan rates being the more important metric here.


How should I design a relationship table for friendship? If A is a friend of B, then should I store both values AB and BA, or one is enough? What are the advantages and disadvantages of both methods.

Here is my observation:

If I keep both then I have to update both when receive a request from a friend.
If I don't keep both, then I found it difficult when having to do multiple JOIN with this table.

Currently, I keep the relationship one way.

So what should I do in this case? Any advice?

I would store AB and BA.  A friendship is really a two-way relationship, each entity is linked to another.  Even though intuitively we think of the "friendship" as one link between two people, from a relational point of view it is more like "A has a friend B" and "B has a friend A".  Two relationships, two records.


Symmetric self-referential many-to-many I'm trying to model a hierarchy of categories where a category can have multiple parents ( an overlapping tree model as described in this book )

I have the following tables

video_categories
  int     id
  string  name

video_category_links
  int     parent_id  (foreign_key to video_categories.id)
  int     child_id   (foreign_key to video_categories.id)

and an index on [ parent_id, child_id ] for uniqueness

The issue with this setup is that
A can be parent of B, and B can be parent of A at the same time

Is there a way to specify a db constraint to avoid this or should I ensure it at the application level?

Thank you very much

I would define levels in the hierarchy:

video_categories
  int     id
  int level
  string  name

I would propagate the parent and child levels into your link table:

video_category_links
  int     parent_id
  int parent_level ((parent_id  , parent_level) foreign_key to video_categories(id, level))
  int     child_id
  int child_level((child_id  , child_level) foreign_key to video_categories(id, level))
  check(child_level > parent_level)


SQL 2016 SQL Server Assertion: File: , line=951 Failed Assertion I'm currently upgrading our Data warehouse from SQL 2012 to SQL 2016. I have both my old and new DW's running side by side in parallel.

My ETL process (A framework developed in SSIS by a 3rd party) has run successfully for more than 2 years on 2012 but is failing on 2016. So far the databases and ETL process are identical.

Both Servers are Virtual machines running on VMWare.
Old Server is Win 2008 with 24Gb of RAM. SQL 2012 Std. Max mem set to 16Gb.
New Server is Win 2012 with 64Gb of RAM. SQL 2016 dev. Max mem set to 50Gb.
New DW is running v13.0.1601.5 RTM Developer Edition (64-bit).

While running my ETL process the load steps that use a SQL Merge into either a dimension or fact table fail with the following error.

Full text:

  DESCRIPTION:  SQL Server Assertion: File: , line=951
  Failed Assertion = 'IS_OFF (BUF_MINLOGGED, m_buf->bstat) ||
  pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage
  ()'.  This error may be timing-related. If the error persists after
  rerunning the statement, use DBCC CHECKDB to check the database for
  structural integrity, or restart the server to ensure in-memory data
  structures are not corrupted.

As recommended I have run DBCC and no errors were found. I have also restarted SQL. I then restarted the ETL process and got the same error.

My searches for this error show that is was known error in SQL 2008, 2012 & 2014 and fixed in subsequent hotfixes & cumulative updates. so I'm a bit surprised to see it reappear in 2016.

The links I've found say it affects SSIS when trying to do inserts if database is in Simple or Bulk Logged recovery model. (I'm running in Simple recovery model)

A suggested workaround is to change Db recovery model to FULL. I have tried this and it works, but it's not much of a solution for a Data Warehouse.

Has anybody else encountered this with 2016?

Can anyone suggest alternative workarounds?

Updates:

26/7/2016: I applied the Critical Update KB3164398 (v13.0.1708.0) and the problem still exists.

27/7/2016: I have applied Cumulative Update CU1 KB3164674 (v13.0.2149.0).

3/8/2016: Error occurred overnight on our smallest cube. CU1 did not fix the issue. Today I reported the bug on MS Connect And I have also logged a support call with Microsoft.

12/8/2016: MS-Support responded initially, but the responses was "We don't have a fix for that". The Support guy was going to discuss it with his colleagues and get back to me. 8 days later I haven't heard from him.

Although I don't have a 'fix' we did find a workaround that suited us.  See my posted answer.

29/9/2016. I applied CU2 last week. On Thursay we accidentally ran an old version of the merge which failed again with the same error. So..  CU2 hasnt fixed it either.

23/1/2017: I Applied 2016 SP1 CU1 and I believe this has resolved the issue.  Specifically KB3205964

Looking at the KB you have a couple of options / workarounds:

Switch to FULL recovery model.  You say "this isn't much of an option for a warehouse" but it's really just a matter of setting up the transaction log backups on a regular basis eg 15 minutes and then disposing of them.  SSIS / Maintenance Plans have stock tasks for doing this.  You will lose bulk-logged transactions, but I've never found these have made a great difference in runtimes, just log size.  You can even backup log to nul which I won't describe here.  If you're not sure what to do, ask you local DBA.  Disk space and transaction log backup retention are easier problems to solve than fatal errors.  When this problem eventually gets solved you can switch back.
The KB mentions "multiple BULK INSERT statements in a single-distributed transaction".  It's not clear from your question how your bulk inserts are set up.  Are you using SSIS to run 'Execute SQL' tasks which use the MERGE command?  What does 'multiple BULK INSERTs' mean here?  Is there a way to convert your approach to single BULK INSERTs, one at a time for example?  In SSIS you can set the 'MaxConcurrentExecutables' to 1 temporarily for example, see if that helps.  Tie it in to a config variable so you can change it back later.  Obviously it will slow things down but you prefer your ETL to finish rather than fail quickly.  Doing things in parallel is a good pattern and a real strength of SSIS, but you can only go as fast as your slowest component; say you have 10 dimensions which take a minute and one fact which takes an hour, your ETL finishes in an hour running parallel or 1 hour 10 minutes running serially.
MERGE is nice but does have a few issues.  You could consider converting back to INSERT/UPDATE.  Also you should be using HOLDLOCK with MERGE according to here.  Do you use that hint?  Does it make any difference to this issue if you do?  We had an issue in an early SQL 2014 build where using MERGE with composable DML (OUTPUT clause) into columnstore caused this kind of assertion - I made them take the columnstore indexes off the dimensions, which they had added without telling me.
What kind of transaction handling are you doing?  Sometimes with ETL the position is repeatable by just re-running.  Sometimes you need it to fail and rollback.  How have you implemented this?  Can be altered so it's not "single distributed transaction"?

Good luck.


Could not connect to postgres after installing 9.4 and uninstalling 9.3 I have installed postgresql 9.4 and I removed postgresql 9.3 from my computer. Now when I try to run:

foo@bar:~$ psql

I get the error

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Note:

foo@bar:~$ psql --version
psql (PostgreSQL) 9.4.2

I resolve the problem changin the port from 5433 to 5432


How to stop Firebird SuperClassic (run as application) in Win 7? I run Firebird 2.5.3 SuperClassic as application in Windows 7 64-bit with this command:

fb_inet_server -a -m

Now, how can I stop/shutdown it from command prompt? I know that I could right-click its icon and choose Shutdown, but this is not what I'm looking for.

Thanks in advance.

I don't have enough points to make this a comment, but it looks like based on this page
the answer is net stop fb_inet_server.


When is the right time to use MariaDB instead of MySQL, and Why? What we earn and what we lost with this migration?

What should I expect as drawbacks after the migration?

Is it really unnecessary to change the applications in any situation?

I think now is the time to play with MariaDB (just like now is the time to play with anything like this). Personally, I would hold off until I know a few more devs who have deployed MariaDB onto a live customer site before doing it myself.

Some situations could call for a change of application but something as serious as changing your database engine is not something that should be considered lightly.


How do I diagnose missing rows on a MySQL slave? I'm running traditional master/slave replication on MySQL 5.1.52, my master is set for statement based replication.  A few tables are missing rows on the slave (differing row counts), these tables are insert only on the master using "load data infile ...", with auto-increment Pks.  It is strange that the missing rows on the slave have gaps in the PK values where the rows should be.  Slave is marked read-only so there are no accidental deletes.

The traditional way would be to use pt-table-checksum and pt-table-sync

I like doing things a little different. I immediately run pt-table-sync with the --sync-to-master --print options.

Here is the --sync-to-master option

  --sync-to-master

  Treat the DSN as a slave and sync it to its master.

  Treat the server you specified as a slave. Inspect SHOW SLAVE STATUS,
  connect to the server’s master, and treat the master as the source and
  the slave as the destination. Causes changes to be made on the master.
  Sets --wait to 60 by default, sets --lock to 1 by default, and
  disables --[no]transaction by default. See also --replicate, which
  changes this option’s behavior.

Here is the --print option

  --print

  Print queries that will resolve differences.

  If you don’t trust pt-table-sync, or just want to see what it will do, this is a good way to be safe. These queries are valid SQL and you can run them yourself if you want to sync the tables manually.

This, if you do something like this:

echo "SET SQL_LOG_BIN=0;" > /root/SQLChanges.sql
pt-table-sync --print --sync-to-master h=10.1.2.30,u=username,p=password >> /root/SQLChanges.sql

The file /root/SQLChanges.sql will contain every change you need to execute on the Slave. Once you are satisfied with its contents, just execute the script on the Slave.

With regard to using LOAD DATA INFILE in Replication, @DTest answered this question about that. I further explained how mysql replicates LOAD DATA INFILE.


InnoDB: "Only one log file found" after binary import My first one on DBA...

After successful upgrading a master MySQL server 5.5 to 5.6.17 (CentOS 6 VPS serer), I done and moved my binary backups to a slave server (same version 5.6.17 but different environment on Jelastic). The binary backup was done using Percona Xtrabackup 2.1.7 with preparation. Both systems are x86_64 GNU/Linux

After restoring the binary backups to /var/lib/mysql and starting MySQL I was welcomed with the following error from MySQL log:

140725 15:02:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-07-25 15:02:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-07-25 15:02:13 11854 [Note] Plugin 'FEDERATED' is disabled.
2014-07-25 15:02:13 11854 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-07-25 15:02:13 11854 [Note] InnoDB: The InnoDB memory heap is disabled
2014-07-25 15:02:13 11854 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-07-25 15:02:13 11854 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-07-25 15:02:13 11854 [Note] InnoDB: Using Linux native AIO
2014-07-25 15:02:13 11854 [Note] InnoDB: Using CPU crc32 instructions
2014-07-25 15:02:13 11854 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-07-25 15:02:13 11854 [Note] InnoDB: Completed initialization of buffer pool
2014-07-25 15:02:13 11854 [ERROR] InnoDB: Only one log file found.
2014-07-25 15:02:13 11854 [ERROR] Plugin 'InnoDB' init function returned error.
2014-07-25 15:02:13 11854 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-07-25 15:02:13 11854 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-07-25 15:02:13 11854 [ERROR] Aborting

2014-07-25 15:02:13 11854 [Note] Binlog end
2014-07-25 15:02:13 11854 [Note] Shutting down plugin 'partition'

This is ls /var/lib/mysql

backup-my.cnf
ibdata1
ib_logfile0
xtrabackup_binary
xtrabackup_binlog_info
xtrabackup_binlog_pos_innodb
xtrabackup_checkpoints
mysql
.... OTHER DATABASE FOLDERS .....

This is very strange, and it used to work perfectly fine when I was setting up 5.5 > 5.5 replication exactly the same was.

Why is it failing with 5.6 now?
What does it mean: [ERROR] InnoDB: Only one log file found"
Additionally on the master I can see ib_logfile0 and ib_logfile1 but Xtrabackup gives only ib_logfile0

I tried restarting the server.
I tried doing the backup/restore prcedure twice to make sure I didn't miss something.
I tried removing ib_logfile0 file as suggested somewhere on the official MySQL forum.
I tried explicitly enabling InnoDB in my.cnf with Innodb=ON

EDIT: Answering for questions:

The actual line I'm using for the backup?

ulimit -n 10240 && sudo -u mysql \
    /bin/sh -c "cd /var/lib/mysql && /usr/bin/innobackupex          \
      --user=${MYSQL_BACKUP_USER} --password=${MYSQL_BACKUP_PASS}   \
      --lock-wait-query-type=update --lock-wait-timeout=300         \
      --kill-long-query-type=select --kill-long-queries-timeout=10  \
      --rsync --slave-info --safe-slave-backup ${XB_TARGET_DIR}"

/usr/bin/innobackupex --version

InnoDB Backup Utility v1.5.1-xtrabackup;

cat xtrabackup_binary

xtrabackup_56

cat backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_fast_checksum=0
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_tablespaces=0

Do you have the log files outside of the datadir?

No, log files are within the datadir

Assuming you have run innobackupex --apply-log successfully you either have a configuration problem (probably innodb_log_files_in_group is 1 in the original and 2 -or not set- in the restored server) or you lost ib_logfile1 somewhere in the process.

InnoDB is ok, it is normal for it to fail when it detects a different configuration on disk than on the file.

Please be aware that xtrabackup/innobackupex only copies data files, not the /etc/my.cnf.

Final answer: apply-log wasn't being executed successfully- a mistake while logging was concealing that. The actual error was due to a specific bug in an old version of Percona XtraBackup. Update to a newer version >= 2.1.8 should fix that.


Are there best practices or standardized syntax for writing transformation logic? This may not be the appropriate forum, but its the closest I could find.  I write a lot of source to target mappings for work.  These documents just describe the operations/logic/transformations/etc. needed to get the data from A to B.  I find myself phrasing the same thing different ways, or sometimes the descriptions get convoluted if the logic is complex.

Are there any best practices for writing the transformation logic for relational databases?  For example, how to say 'Join tables A,B, and C for each combination of fields D,E, and F select the maximum value between fields G and H.'  I feel that somebody has probably standardized the natural language for relational database transformation logic, but I can't find anything to that effect.

SQLish

Honestly, your best bet is probably ANSI-compliant SQL, because it can eliminate the ambiguities which you've mentioned.

If there's any trouble with it needing to be written in "natural language", just add in the apprpriate prepositions and such...

Select the maximum paid amount, the customer account number, and the customer phone number
from the 'Invoices' table joined to the 'Customers' table,
joined on the condition that
the 'Invoices'.customer_id column equals the 'Customers'.customer_id column,
grouping by the customer account number and customer phone number.


MySQL returned an empty result I want to get a data of previous year : 2015-12-01

My Query :

  SELECT

          catalog.code,
          Max(catalog.weight) as weight,
          MONTH(product.created_at) as Month,
          YEAR(product.created_at) as year

  FROM product

 LEFT JOIN catalog ON product.code = catalog.code

 WHERE MONTH(product.created_at) = MONTH(2016-01-01 - INTERVAL 1 MONTH) AND YEAR(product.created_at) = year(NOW())

 GROUP BY catalog.code,MONTH(product.created_at),YEAR(product.created_at)

Does anyone have an idea how i can solve this problem?

Thanks for any help.

Your problem is with where condition.

Do have a look at mysql date/datetime formats, this will give you clear idea of how you should use date functions in mysql.

If you need data of previous year, simply change your where condition to:
YEAR(product.created_at) < YEAR(NOW())

Hope this helps!


Mysql Workbench 8 doesn't detect the my.ini file for my MySql 5.7 server. How can I point it to the correct file? I upgraded MySql workbench to version 8.
I'm running MySql Server 5.7.
When I open the options file in workbench it says it cannot locate a file for MySql 8 and that it will create one in the path C:\ProgramData\MySql\MySql Server 8\my.ini
I do not have MySql Server 8 installed.

Why is workbench not finding the file?
How can I point it towards the correct file?
Do I need to downgrade workbench?

MySQL Workbench tries to find the config file at a common location. If there's none then it will create it, when you edit the configuration and save it (and only then).
However, you can set the ini location in your connection details. For that open the connection editor (e.g. by right clicking on a connection tile on the home screen and selecting "Edit Connection...". Once the dialog is open switch to the "System Profile" tab. On this page you will find places where you can set an own config file path and even the config file section, if that differs from the default ("[mysqld]").


Should i separate ContactInfo (sub-entity) from MyTable (main-entity) or Not? I am designing a database of a school management system. I have one to one relationship of MyTable with ContactInfo table. I am confused whether I separate contactinfo from MyTable or not. ContactInfo record will be single against each MyTable record, but ContactInfo can be updated many times.

Should i merge ContactInfo as

or develop a separate ContactInfo as given below

Please justified your help with the solid arguments or rule.
Thanks for your help.
Note: ignore the blur fields.

I do not know exactly what you mean by 'updated many times' but I presume that you mean that the same ContactInfo can be present in multiple MyTable entries. In this case I would split it but put the ContactId in the MyTable entity. This way you are sure that all ContactInfo is the same everywhere it is used in MyTable.

The rule is that you put all fields that are identified by a single primary key in a separate entity. This way you minimize the number of updates when one of the fields that 'belong' to this primary key changes.

If 1 ContactInfo entry has only 1 MyTable entry and 1 MyTable entry has only 1 ContactInfo entry then the separation is not necessary. The only advantages are:

If the length of an entry is big there is less chance that an entry does not fit in the physical block any more and needs to be 'moved' to an other physical block by the RDBMS.
When you rarely need to select the ContactInfo and MyTable at the same time that (with a lot of rows) the fetch in of the rows goes faster since more entries will fit in a physical block.


Error: /var/lib/postgresql/9.3/main is not accessible or does not exist I have a PostgreSQL db that was running on my server.
I tried to do a db_dump and got a weird error message. I eventually thought I would just restart PostgreSQL. However its now stopped and won't restart.

  Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

I get the error above when I try and run:

sudo service postgresql start

Can anyone give me some clue please?

I'm running on PostgreSQL 9.3 on Ubuntu 14.04

If you suspect there's something wrong with our database (cluster), make a copy of your database directory before you continue. Just to be sure. On Ubuntu the default directory would be: /var/lib/postgresql/9.3/main/ - but your installation may differ.

Actually, your command should work (in default Ubuntu installations), because the service command starts the postgres server process with appropriate privileges (as system user postgres)

sudo service postgresql start

Translates to something like:

sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" start

That starts all db clusters you may have. To start a particular one, use the wrapper pg_ctlcluster:

sudo -u postgres pg_ctlcluster 9.3 main start

To get the status of all currently installed db clusters use pg_lsclusters:

sudo -u postgres pg_lsclusters

You must be aware that the data directory is not the same as the config directory in default Debian or Ubuntu Postgres installations. For Postgres 9.3 that would be:

data dir: /var/lib/postgresql/9.3/main/
config dir: /etc/postgresql/9.3/main/

You can check the /etc/postgresql/9.3/main/postgrsql.conf for the actual data dir. It's the line starting with

data_directory =

The error message indicates that Postgres tries to start with the default data dir, but it is not there or the privileges have been changed. Did you check?

Or are you running a non-default installation?


How to mix pairs of rows without JOIN (in a temporary table)? I have a TEMPORARY table containing pairs of key/value as

id   col1      col2        type         pair
1    key1      key2        key          1
2    value1    value2      value        1
3    key3      key4        key          2
4    value3    value4      value        2
5    key5      key6        key          3
6    value5    value6      value        3

I want to mix the key/values as

pair    key_col1     key_col2      value_col1      value_col2
1       key1         key2          value1          value2
2       key3         key4          value3          value4
3       key5         key6          value5          value6

Since, it is a temporary table, I cannot use JOIN. How can I SELECT from this table to have pairs of key/value in each row?

This type of data transformation is known as a PIVOT but unfortunately MySQL does not have a pivot function.  However, this can be replicated using both an aggregate function and a CASE statement:

select pair,
  max(case when type = 'key' then col1 end) Key_col1,
  max(case when type = 'key' then col2 end) Key_col2,
  max(case when type = 'value' then col1 end) value_col1,
  max(case when type = 'value' then col2 end) value_col2
from temp
group by pair

See SQL Fiddle with Demo

The result of the query is:

| PAIR | KEY_COL1 | KEY_COL2 | VALUE_COL1 | VALUE_COL2 |
--------------------------------------------------------
|    1 |     key1 |     key2 |     value1 |     value2 |
|    2 |     key3 |     key4 |     value3 |     value4 |
|    3 |     key5 |     key6 |     value5 |     value6 |


Specify cleanup by file age - PostgreSQL 9.2 I want to keep my wal_files for 30 days, using the pg_archivecleanup.

I've followed these scripts:

https://stackoverflow.com/questions/31346606/postgresql-automatic-archive-purge-not-occuring

and

https://stackoverflow.com/questions/16943599/how-to-specify-cleanup-by-file-age-or-date-with-pg-archivecleanup ...

#!/bin/bash

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
CHECKPOINT=$(find $ARCHIVEDIR -type f -mtime +30 -type f -printf '%f\n' | sort -r | head -1)
cd $ARCHIVEDIR
/usr/bin/pg_archivecleanup $ARCHIVEDIR $CHECKPOINT

find $ARCHIVEDIR -type f -mtime +30 -a -type f -a ! -newer $CHECKPOINT -delete

... but it's not working for me.

I'm getting this error:

pg_archivecleanup: invalid filename input
Try "pg_archivecleanup --help" for more information.
find: `-delete': No such file or directory

my recovery.conf:

archive_cleanup_command = 'pg_archivecleaup_mv.bash -d'

Is there any way to make this work?

UPDATE 1:

I've done some tests...

recovery.conf:

archive_cleanup_command = 'pg_archivecleaup_mv.bash'

pg_archivecleaup_mv.bash:

declare -r -x PATH='/usr/local/bin:/usr/bin:/bin';

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
CHECKPOINT=$(find $ARCHIVEDIR -type f -mmin +3 -type f -printf '%f\n' | sort -r | head -1)
cd $ARCHIVEDIR
exec "pg_archivecleanup" "$ARCHIVEDIR" "$CHECKPOINT" "%r";
find $ARCHIVEDIR -type f -mmin +3 -a -type f -a ! -newer $CHECKPOINT -delete

-mmin +3 for testing purpose...

By doing that, the error changes:

pg_archivecleanup: too many parameters
Try "pg_archivecleanup --help" for more information.
archive_cleanup_command "/var/lib/pgsql/bin/pg_archivecleaup_mv.bash": return code 512

UPDATE 2: Following @Sebastian Webber idea, I changed the pg_archivecleaup_mv.bash script; I'm getting the same issue that I had using the script I wrote:

#!/bin/bash -x

ARCHIVEDIR='/var/lib/pgsql/wal_archive'
LAST_BACKUP=$(ls -lto ${ARCHIVEDIR})

FILE_LIST=$(/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}")

## TEST FIRST
/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" | find -type f -mmin +30 | xargs ls -lh

##THEN UNCOMMENT THIS LINE
#/usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" | find -type f -mmin +30 -delete

recovery.conf:

archive_cleanup_command = 'exec /var/lib/pgsql/bin/pg_archivecleaup_mv.bash -d "%r"'

With or without the "%r" I get the error: pg_archivecleanup: invalid filename input
Without the "%r", I put the %r into the script: /usr/pgsql-9.2/bin/pg_archivecleanup -n "${ARCHIVEDIR}" "${LAST_BACKUP}" "%r"| find -type f -mmin +30 | xargs ls -lh - Then I get the error: pg_archivecleanup: too many parameters

First of all, if you need to retain the archived WAL files it's a good pratice to put in another directory. If you use the archive files to recovery a standby server, i may prefer make a separated copy for it.

You can adjust that in your master server with the parameter archive_command.

Now, if you want to keep just one directory for same purpose, remember that
pg_archivecleanup needs the archived wal location (seems ok with the variable $ARCHIVEDIR) and the last backup file (witch contains the first and last archived wal file for the backup, it contains a .backup extension, eg: 000000010000003700000010.00000020.backup) and that it's missing.

To solve your script, find the latest .backup file on the $ARCHIVEDIR directory and call pg_archivecleanup with the -n option to get a list of wal files to delete then use that list to find the files older than 30 days.

My idea it's something like this:

https://gist.github.com/sebastianwebber/855585ef3ef6a50c50cca7b57bc8d7d3#file-pg_cleanup_oldmorethan_30days-sh

Please, take a look at the documentation for more details:
https://www.postgresql.org/docs/current/static/pgarchivecleanup.html


Timezone considerations when choosing an appropriate datatype We are currently developing a task management system for our bespoke application. After a lot of deliberation we came to the conclusion that we only need users to specify the due date for a task.

I am wondering how it would work when a user in the UK adds a task and a user in Saint Petersburg sees it, considering that Saint Petersburg is at least 2 hours ahead of the UK.

Say that a user in the UK creates a task due on the 20/Apr/2018. At 23:30 (British time) on 19/Apr, a user in St Petersburg opens the task.  However, since it is already 20/Apr in St Petersburg it would look like the task has already completed, but in actual fact is not scheduled to run yet.

If the date field is in fact of type date, I am pretty sure that the user in St Petersburg would see the task marked as overdue, since it is already 20/Apr at his end.

I have seen several other task tools, and pretty much all of them also tend to allow only due dates (not time) for tasks. I wonder however how other systems handle this sort of issue, or if they just don't bother at all.

So being objective around my questions:

We acknowledge this discrepancy and accept it for what it is. If a task is due on the 20/Apr and it is already 20/Apr in Australia but not in California, we just accept that users in Australia will see the task as late and those in California would see it as 'In progress'.
I think that the only way to overcome this issue would be to capture the date in a datetime field (even if we don't allow users to enter the time), and then also allow for a timezone to be defined.

Am I missing something?

EDIT: To answer the question on whether we want the due date of a task to follow the timezone of the user seeing the task, or a fixed timezone when the task has been created, we have opted for a fixed timezone. So a task will be set to be due on 20/Apr UK time will only get to be due when it reaches YYYY-04-20 00:00:00.00 UK time.

To support this, we will allow for users to click a small icon next to the due date field when creating a task, allowing for them to select the timezone. The timezone will be set to a default based on the following workflow:

We will create two settings for the user's account page that allow the user to:

a) Toggle an option for the application to attempt to get their timezone automatically from the client side.
b) An option for them to select their default timezone.

Based on this requirement we can pretty much conclude that a date datatype is out of the question. Now what is not 100% clear to me is whether we should go for a datetime2 (I always use datetime2 instead of datetime) or a datetimeoffset.

Use the datetimeoffset SQL Server data type which supports timezone details:

USE tempdb;

DROP TABLE IF EXISTS dbo.timetest;
CREATE TABLE dbo.timetest
(
    t datetimeoffset(0) NOT NULL
);

INSERT INTO dbo.timetest(t)
VALUES ('2018-04-18 00:00:00 +03:00');

SELECT [Time at CEST] = t AT TIME ZONE 'Central European Standard Time'
    , [Time at RST] = t AT TIME ZONE 'Russian Standard Time'
FROM dbo.timetest;

The results:

╔════════════════════════════╦════════════════════════════╗
║        Time at CEST        ║        Time at RST         ║
╠════════════════════════════╬════════════════════════════╣
║ 2018-04-17 23:00:00 +02:00 ║ 2018-04-18 00:00:00 +03:00 ║
╚════════════════════════════╩════════════════════════════╝

In the above example, we're inserting data in the Russian Time Zone, and then displaying it in both Central European Standard Time, as well as Russian Standard Time.

You can use the SYSDATETIMEOFFSET() function to obtain the current time at the server, including the servers time zone.


Delete duplicate keys with relation algebra Hi I am new to databases and relational algebra. I was wondering if there is a way to remove the tuples from a table using relational algebra that have the same keys but different value.

e.g. I want to keep only [1, 5] and [4, 9] but remove everything else.

Key    | Value
-------|-------
 1     | 5
 2     | 6
 2     | 7
 2     | 8
 4     | 9

Thanks.

I would do something like the following (see the fiddle here):

Create and populate a table:

CREATE TABLE test
(
  key INTEGER NOT NULL,
  value INTEGER NOT NULL
);

INSERT INTO test VALUES
(1, 5), (2, 6), (2, 7), (2, 8), (4, 9);

and then:

SELECT * FROM test;

Result:

key     value
  1         5
  2         6
  2         7
  2         8
  4         9

and then run the following SQL:

SELECT key, value
FROM test WHERE key IN
(
  SELECT key
  FROM test
  GROUP BY key
  HAVING COUNT(key) = 1
  ORDER BY key
);

Result:

key     value
  1         5
  4         9

Which is the desired result. I have used PostgreSQL - the Open Source database which I would recommend that you use to learn relational database principles - it has a very high level of standards compliance and is a great learning tool.

The inner loop of SQL

SELECT key
FROM test
GROUP BY key
HAVING COUNT(key) = 1
ORDER BY key

produces those keys which have a COUNT of only 1 (in this case, the integers 1 and 4) and then the outer loop selects the values corresponding to those two integers (see the fiddle).


MariaDB cluster and external data feed I've just finished setting up the 3 nodes mariadb (galera) cluster (let's call the nodes node1, node2 and node3). In order to have the cluster up to date with currently working mariadb database (let's call it dev_db) we created a replication of this dev_db to node1 so dev_db is a master and node1 is a slave. Replication is working properly so node1 is up to date with dev_db. I thought that replicated data to node1 cluster will be able  also transfered to other nodes so that whole cluster will be 'consistent'.
Unfortunately what is happening is that replicated data from dev_db to node1 is not propagated to the rest of the nodes.

When I add some sample data to node1 I can see that this data is propagated to other nodes (node2 and node3) so cluster is fine.

The question is how to configure this setup so that dev_db will be feeding the cluster with new data so that whole cluster will be up to date.

Many thanks.

You will probably need to enable log_slave_updates so the slave node generates binlog to be used on other nodes.

http://www.severalnines.com/blog/replicate-mysql-server-galera-cluster


Use a view to join tables or a stored procedure for a better execution plan? First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans.

What I want to know is for a stored procedure which the results are pulled from two or more tables using primary and foreign keys to reference them, is it better practice to:

Have a view which performs the joins and contains flattened data for ALL corresponding records from the two (or more) tables and let the stored proc select from and filter that view
Let the stored proc do the join and the filtering.

I ask because in a C# world we would separate out the concerns, so one object or function would be responsible for the joining (in this case the view) and another object or function would be responsible for the filtering (in this case the stored proc).

Which option from above will perform better?
Which option from above is considered best practice?

In C# separation of concerns is generally considered more important than performance until it is proven that performance has become a serious issue with the code. But I don't know who things stack up in the SQL world!

Which option from above will perform better?

Best case, both will produce exactly the same execution plan, with the same runtime performance.

This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from the SQL Server MVP Deep Dives Volume 1 book.

  Which option from above is considered best practice?

This is likely more a matter of opinion that an established best practice either way, but experienced database professionals tend to limit their use of views to scenarios where they offer real and significant benefits. The more complex a view becomes, (especially if views start to become stacked or layered) the harder it is to avoid undesirable side-effects and the more likely it becomes to encounter optimizer limitations.

The query may be easier to read and maintain if it references a view, but "separation of concerns" is not something you will find database professionals discussing when choosing between a view or writing the underlying query out in a stored procedure. There are many established practices and patterns in programming that do not translate well to database work.

Always bear in mind that a (non-indexed/materialized) view is simply a stored query definition. It is expanded into the referencing query exactly as if copy-and-pasted in place manually before query compilation and optimization begins.

You did not ask for a comprehensive critique of the benefits and drawbacks of using views (which would likely be too broad a question anyway) so I will not attempt to offer one. My final word of caution is that even simple views that optimize well today (perhaps making use of Rob's idea) can easily be modified (often in an innocuous-looking way) so that queries referencing them suddenly encounter an optimizer limitation and a performance cliff.


Concatenate Data from different row from same table This is SQL source Table. I need to concatenate this table like the table below,appreciate your help.

PE_CASE   PE_ITEM   PE_HIS_SEQ     PE_COMMENT

200       0001      9989           Comment 1
200       0001      9990           Comment 2
200       0001      NULL           Comment 3
200       0002      9986           Comment 4
200       0002      9987           Comment 5
200       0002      NULL           Comment 6
201       0001      9926           Comment 7
201       0001      9937           Comment 8
201       0001      NULL           Comment 9
201       0002      9933           Comment 10
201       0002      9921           Comment 11
201       0002      NULL           Comment 12

I need to concatenate above table like this.

PE_CASE   PE_ITEM   PE_HIS_SEQ     PE_COMMENT

200       0001      NULL           Comment1 + Comment2 + Comment3 + Comment4
200       0002      NULL           Comment 4  + Comment 5  + Comment 6
201       0001      NULL           Comment 7  + Comment 8  + Comment 9
201       0002      NULL           Comment 10 + Comment 11 + Comment 12

You can use for xml to concatenate values from multiple rows. There are many other ways to concatenate rows as outlined here

For XML example with your data above, first we create and load sample table

    CREATE TABLE [pecomment]([PE_CASE]    int,
                    [PE_ITEM]    varchar(4),
                    [PE_HIS_SEQ] int,
                    [PE_COMMENT] varchar(20));

INSERT INTO [pecomment]
VALUES
      (200, 0001, 9989, 'Comment 1'),
      (200, 0001, 9990, 'Comment 2'),
      (200, 0001, NULL, 'Comment 3'),
      (200, 0002, 9986, 'Comment 4'),
      (200, 0002, 9987, 'Comment 5'),
      (200, 0002, NULL, 'Comment 6'),
      (201, 0001, 9926, 'Comment 7'),
      (201, 0001, 9937, 'Comment 8'),
      (201, 0001, NULL, 'Comment 9'),
      (201, 0002, 9933, 'Comment 10'),
      (201, 0002, 9921, 'Comment 11'),
      (201, 0002, NULL, 'Comment 12');

Then use for xml to concatenate the comments, grouping by PE_CASE and PE_ITEM

SELECT [PE_CASE],
      [PE_ITEM],
      NULL,
      [PE_COMMENT] = SUBSTRING((
      SELECT '+ '+[PE_COMMENT]
      FROM [pecomment] AS [t2]
      WHERE [t1].[PE_CASE] = [t2].[PE_CASE]
           AND [t1].[PE_ITEM] = [t2].[PE_ITEM]
      ORDER BY [PE_CASE],
             [PE_ITEM]
      FOR XML PATH('')
      ), 3, 1000)
FROM [pecomment] AS [t1]
GROUP BY [PE_CASE],
        [PE_ITEM];

This should give you what I think is your example desired result, hence the null in PE_HIS_SEQ.