PostgreSQL and table inheritance I'm not sure if this is the right place for this, so please feel free to tell me if it is off-topic or there is a better place to post.

I am working on a fantasy sports based project, and the number of tables is getting unruly. I have tables like nba_players, nfl_players, etc, and nba_players_to_teams, nfl_players_to_teams, etc. (NBA and NFL are basketball and american football leagues, respectively). These all share the same table schema and code-base, but have their foreign keys on different tables.

My question is, does using table inheritance make sense for situations like this? The "base" table would sit empty, and would only be used to enforce the schema. Will changes to the "base" table propagate to tables that inherit from it? At this point the schemas are pretty stable, so that's not a huge concern.

Any thoughts or concerns that I haven't addressed? I'm still reading about table inheritance and the pros and cons, but any resources you have would be greatly appreciated.

There is nothing in your question that would speak against inheritance.

One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to individual tables, not including children. But that seems to match what you need exactly: Create PK and FK constraints on each child table individually.

nba_players, nfl_players etc. could conveniently inherit from a general parent table players (possibly in a separate schema for empty parent tables). ALTER TABLE on the parent cascades to all children (except for  ALTER TABLE ... RENAME). This makes it easy to run queries on all player tables together by targeting the parent.

If you never need to look at all player tables together or cascade changes to all tables, it might be better to just keep a template table (possibly in a separate schema for template tables) and create instances with CREATE TABLE ... (LIKE ...):

CREATE TABLE nba_players(LIKE players);


Detect circular relations in a distribution table I have the following two tables, that will define distribution rates from items on table A to other items on table A (I think I can say B defines a many-to-many relationship for A with itself).

create table A (
    code char(5) not null primary key,
    desc varchar(50) not null
)

create table B (
    code1 char(5) not null,
    code2 char(5) not null,
    perc numeric(6,2) not null,

    constraint pk_B primary key (code1, code2),
    constraint fk_B_1 foreign key (code1) references A (code) on update cascade on delete cascade,
    constraint fk_B_2 foreign key (code2) references A (code) on update cascade on delete cascade
)

I'd like to have a simple way to check if a new record being inserted on B will cause a circular reference to be created.

This example illustrates the problem:

insert into B (code1, code2, perc) values ('01.10', '02.10', 100);
insert into B (code1, code2, perc) values ('02.10', '02.11', 50);
insert into B (code1, code2, perc) values ('02.10', '02.12', 50);
insert into B (code1, code2, perc) values ('02.11', '04.10', 50);
insert into B (code1, code2, perc) values ('02.11', '02.12', 50);
insert into B (code1, code2, perc) values ('04.10', '01.10', 50);
insert into B (code1, code2, perc) values ('04.10', '04.11', 50);

This set of data would generate the following circular reference:

I'm using Firebird 2.5, but I'd prefer to use only standard sql, because portability is something I'm concerned. We might be changing DBMS soon.

Following @ypercube comment to the question, I came up with this solution:

with recursive A as (
    select code1, code2 from B
    where code1 = new.code2
    union all
    select a2.code1, a2.code2 from B a2
    join A on a2.code1 = A.code2
)

select * from A
where code2 = new.code1

If any records are returned, then new.code2 references new.code1 and there would be circular reference if this new record was inserted.

For Firebird specifically, this is what I used:

CREATE EXCEPTION EX1 'circular reference exception';

SET TERM ^ ;

CREATE TRIGGER TRIGGER_B_CIRCULAR FOR B
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
DECLARE CODE char(5);
BEGIN
    FOR with recursive A as (
            SELECT CODE1, CODE2 FROM B
            WHERE CODE1 = new.CODE2
            UNION ALL
            SELECT a2.CODE1, a2.CODE2 FROM B a2
            JOIN A ON a2.CODE1 = A.CODE2
        )

        SELECT CODE2 FROM A WHERE CODE2 = new.CODE1
        INTO :CODE DO
            exception EX1;
END^

SET TERM ; ^


PostgreSQL, display role members Is there a way to visualize which users are mebers of a role in PostgreSQL / pgAdminIII, like

role: council_stuff
members:
    Harry
    Ben
    Steve
    Melinda

To get all members of all roles:

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolcanlogin
ORDER BY 1;

Add r1.rolname='council_stuff' to filter on only that one.

Be aware that users themselves are "roles" in postgresql parlance (there used to be "users and groups" in older versions, but this have been unified into roles).

Here the difference is made between a user and a role with rolcanlogin boolean column, which corresponds o the interpretation that a user is a role that has the LOGIN permission.

The two JOIN can be turned into LEFT JOIN to add the users who aren't member of any role:

SELECT r.rolname as username,r1.rolname as "role"
FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
ON (m.member = r.oid)
LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)
WHERE r.rolcanlogin
ORDER BY 1;


SQL Server database generate script how to avoid adding PRINT statements I need to create Insert scripts from the content of a table. To do that, I used the database tasks -> generate scripts.

The table is quite big and in the generated script, it adds PRINT command after each 100 number of rows and a GO statement. For example:

print 'Processed 9200 total records'
GO

For some reasons I can't have GO and PRINT command in the script, repeated in the middle. Is there any way to avoid creating them in the generated script? It is really time consuming to go one by one and delete all PRINT xxxx statements.

Thanks.

I would not remove the GO statements that break up large batches.  There are various reasons for breaking large inserts

size (duration) of transactions
memory requirements
transaction log management
mirror synchronicity
error isolation (by batch)
etc...

As for removing the status updates, that can be done easily.  Press Ctrl-H or from the menu, Edit -> Find and Replace -> Quick Replace, fill it in as shown in the image below making sure to tick "Use" Regular Expressions, then click on "Replace All" to zap them all.


upgrading from postgres 9.1 to 9.3 on ubuntu server I have my production server(ubuntu 13.10) running with postgresql 9.1.

I want to use few features of 9.3, hence want to upgrade.

Could someone help me with upgrading from 9.1 to 9.3 so that there is a downtime of not more than 30 mins. or so?

Prime concern is preventing a data loss or file redundancy.

There are basically three ways of upgrading PostgreSQL from different major versions (e.g. 9.1 to 9.3).

Upgrading with pg_dump

The first one, and recommended if possible, is to do a dump of the old (9.1) version using the binary of the newer (9.3) version and restore it on a new cluster created of the newer version.

This approach is, generally, the slower one, but also the most feasible. One tip to make it more fast, is using concurrency. To dump with parallel jobs, you can do:

$ pg_dump --format=directory --jobs=4 --no-synchronized-snapshots --file=/path/to/mydump mydatabase

You'll have to do it for each database you have, adjust --jobs=4 value to any value (test some values from 2 to number of cores, and see which gives better speed). Also, during this phase, nobody should be conected to the database, any modification will result in a corrupted dump (because of the non-secure option --no-synchronized-snapshots).

After that, you can restore you dump into the new instance using pg_restore:

$ createdb <options> -T template0 mydatabase
$ pg_restore --exit-on-error --jobs=4 --dbname=mydatabase /path/to/mydump

After that, it is recommended to run ANALYZE on your database:

$ vacuumdb --analyze-only mydatabase

(if you can afford the time, run only --analyze to also VACUUM the database and update the visibility maps)

Upgrading with pg_upgrade

Another option, is to use the contrib pg_upgrade. Using the --link method it provides a really fast way of upgrading PostgreSQL.

Before using you have to make a backup of the entire data directory, because in --link mode, if something goes wrong, you may loose both data (new and old). Also, read the entire docs and specially the notes at the bottom (there are some limitations for pg_upgrade).

UPDATE:
Please use the --check option before run the definitive command. Also, for large databases is recommendable to run this command in a screen session.

Upgrade using a trigger based replication tool

Another option to upgrade a version, is using a replication tool based on trigger. Like Slony, Bucardo and Londiste.

This is the option that requires the least downtime possible, but it is the hardest one to work on.

To do that you need to build a master-slave where the master is your current version (9.1) and the slave is the new version (9.3). You then, wait the first sync (with the system still in production), after that you close everyone connected to the database (the downtime starts here), wait for the slave to catch-up, promote it (the slave) to master and redirect all clients/applications to this new version. And you're done.

The Slony documentation provides a step-by-step to upgrade PostgreSQL using Slony.

Which one to choose

Well, as always depends, resuming:

The dump+restore is the most reliable, but generally the most slow one (the parallelism can give pretty good results though)
The pg_upgrade is one of the best options for little downtime (if you can use, see the limitations), it often takes only a few minutes, even for big databases
The trigger replication, is with no doubt the one that gives the least downtime possible (near zero), but it is really hard to achieve and I recommend only for experience people (on both PostgreSQL and the replication tool).

I hope I could help. Good luck.


Table not dropped correctly in SQL Server Express 2008 I've dropped a table but it is still listed in sys.tables. What's more confusing is that I can create the table again as one user, but if I log in as another user with the same permissions, I can't (I get a message that the table already exists). I need to recreate the table as this second user.

I've managed to log in as the sa user and run DBCC checkdb, but it hasn't done anything to resolve the problem. How can I remove the record in sys.tables permanently (I'm assuming this is the cause of the problem).

Whether using dbo or not, always specify the schema prefix explicitly. Otherwise, if you have users with different default schemas, you could end up with objects in a schema you didn't expect. One user creates a table in one schema, and the other can't see it. Here is a quick example:

CREATE SCHEMA floob AUTHORIZATION dbo;
GO

CREATE USER blat1
  WITHOUT LOGIN WITH DEFAULT_SCHEMA = floob;

CREATE USER blat2
  WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;

GRANT CREATE TABLE TO blat1, blat2;
GRANT ALTER ON SCHEMA::floob TO blat1, blat2;
GRANT ALTER ON SCHEMA::dbo TO blat1, blat2;

EXECUTE AS USER = 'blat1';
GO
CREATE TABLE Employees(ID INT);
GO
REVERT;

SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N'Employees';

EXECUTE AS USER = 'blat2';
GO
CREATE TABLE Employees(ID INT);
GO
REVERT;

SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N'Employees';

Results:

-----  ---------
floob  Employees

-----  ---------
dbo    Employees
floob  Employees


Strange behavior when combining CONVERT(CHAR()) with RIGHT() When I was trying to create a report that included a decimal-implied number column that needs to be a fixed length of six characters, I chose to go the route of converting to char, replacing the decimal with '', and then using RIGHT() to pad zeros on the left hand side, turning 15.52 into 001552, 1.11 into 000111, etc...  My code looked like:

  SELECT RIGHT( '000000' + REPLACE(CONVERT(CHAR(8), ISNULL([TotalModePremium], 0)), '.', ''), 6) AS TotalModePremium
  FROM <Database>.<Schema>.<Table>

This created unexpected behavior where it would clip off my results, making a max length of 3 characters. 15.52 would become 552. However, rows with 0 would become 00--only two characters. When I would change the CHAR(8) to CHAR(7), 15.52 would become 1552, but 0 would become 000.

I then change the CHAR(n) to VARCHAR(n), and it now works as desired. 15.52 becomes 001552, 0 becomes 000000, etc...

My question is, why would increasing or decreasing the value in CHAR(n) create those sorts of results when combined with the RIGHT() function to pad zeros, but the use of VARCHAR() eliminates that behavior?

It's because of how the CHAR data type works.

Take a look at this example:

 DECLARE @d decimal(15,2)
 SET @d = 15.52

 SELECT CAST(@d AS CHAR(8)) AS CharD
 SELECT DATALENGTH(CAST(@d AS CHAR(8))) AS LengthCharD

 SELECT CAST(@d AS VARCHAR(8)) AS VarcharD
 SELECT DATALENGTH(CAST(@d AS VARCHAR(8))) AS LengthVarcharD

Notice the length of @d is 8 when it's CHAR but 5 when it's VARCHAR. CHAR is fixed width and thus padded with blanks.

Once you add '000000' to it and remove the decimal, you've now got a 13 character string: '0000001552< blank>< blank>< blank>.
When the RIGHT function gets just the right 6 characters, the result is 552< blank>< blank>< blank>.

'000000' is VARCHAR. VARCHAR + your CHAR string will result in a VARCHAR string but still includes the blanks from the CHAR.

VARCHAR does not pad with blanks and thus avoids this < blank> issue.


Preventing mongodb crashes I am using mongodb with the web server.

Recently, I observed that mongodb server crashed after "no space left" for journalling.

So, after some online help I could repair and restart the DB.

But, for production, I am worried now.

Is there a way in mongodb - when bound to one machine - to truncate the journal files and prealloc files to get truncated without restart of mongodb or any other manual intervention?

The journal will be 3 x 1GB files by default, so really you should just make sure that you have at least 3GB space for the journal when you are starting up.  You can use the smallfiles option to reduce that to 3 x 128MB (384MB) instead, however I would not recommend that for any system with significant volume.

Similarly you can turn off pre-allocation using the noprealloc option but again I would not recommend doing so due to the potential performance issues you can hit as a result.

Both of these options must be configured when the process starts, there is no way to (re)configure them after the fact.


Loop and array and sub array I am working on a script that needs to loop an array and loop a sub array. I want to take in rows from a table (that piece works fine) that look like:

invoice | item
inv1    | it_A
inv1    | it_C
inv2    | it_D
inv2    | it_B
inv3    | it_A

and display the JSON (also works) like pseudo code

inv1

   it_C
   it_D

inv2

   it_D
   it_B

inv3

   it_A

but I have the loop wrong, thanks for any assistance

$result = $conn->query($sql);

if ($result->num_rows > 0) {

echo "rows found";
    while($row = $result->fetch_assoc()) {

$invid = $row[invoice_number];
echo $invid;

foreach($invid as $invoicelines)
{

$sql1 = $invoicelines[item_code];

echo $sql1;

Using the SQL will get you the invoice and json aggregated items in an array:

SELECT invoice, JSON_ARRAYAGG(item)
FROM tablename
GROUP BY invoice


Best practices for service name / aliases in tnsnames I have this idea that service names / aliases given to connections descriptors in a tnsnames.ora (not the one in the server that dblinks use, but the one for the public) file should in no way be related to the SID and much less the name of the server.

Suposse the name of the sever is "myserver" and the SID of the instance is "myinstance".

I think giving the alias "myinstance-at-myserver" to the connection string is not a good idea because you are somehow coupling something logical to something physical.

Am I right or wrong and why ?

Is this the best practice?:

# one server-instance-named descriptor
MYINSTANCE-AT-MYSERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

Or is this ?

# several business-named descriptors pointing to the same listener
RRHH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

FINANCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

SALES =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

I certainly wouldn't want to include the server name in the TNS alias.  It would seem highly probably that this would change over time as databases move from one server to another and as organizations move to things like RAC where there would be multiple servers.

Assuming that your service names are chosen meaningfully, I would expect that the service name would match the TNS alias since both are logical names for the same thing.  That's not a hard and fast rule, of course.  Some organizations may have reasons to have two separate logical names for a single service.  For example, you may want a single TNS alias FINANCE that points to the FINUSA service for users in the US and the FINFRA service for users in France.  But for the vast majority of situations, if the TNS alias would be FINANCE, the service name ought to be FINANCE as well.


Troubleshooting Resource Governor / Classification Function I've been trying to configure / troubleshoot the resource governor on a sql server 2014 instance. I can't get any work into anything but the default resource pool. I've been following this troubleshooting guide: http://technet.microsoft.com/en-us/library/cc627395(v=sql.105).aspx

My classification function, originally conditional, now simply returns my resource pool name in all cases in an attempt to get something to run in it.

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RGClassifier]    Script Date: 7/25/2014 7:26:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[RGClassifier] ()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @GroupName SYSNAME
SET @GroupName = N'in-memory'

RETURN @GroupName

END

Results of some of the troubleshooting follow:

USE master
SELECT
  object_schema_name(classifier_function_id) AS [schema_name],
  object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

Returns:

schema_name function_name
dbo RGClassifier

Queries of sys.dm_resource_governor_configuration and sys.resource_governor_configuration indicate it is enabled and no reconfiguration is pending.

More or less stuck in the mud at this point. If anyone has configured the resource governor successfully before I'd love some pointers.

Make sure your group name returned matches (identically) to your actual workload group name. Some times, I've found, it's helpful to reconfigure even if it doesn't say it needs to be done.

use master
GO

-- Create a test DB which our CF will be based on
CREATE DATABASE RGDB;
GO

-- enable RG
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

CREATE FUNCTION dbo.RGClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    IF (ORIGINAL_DB_NAME() = 'RGDB')
        RETURN N'OriginalDBWG'

    RETURN N'Default'
END
GO

-- Create a new Pool
CREATE RESOURCE POOL OriginalDBPool
WITH (
MAX_CPU_PERCENT = 10
)
GO

--Create a new WG
CREATE WORKLOAD GROUP OriginalDBWG USING OriginalDBPool
GO

ALTER RESOURCE GOVERNOR WITH ( CLASSIFIER_FUNCTION = dbo.RGClassifier)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Open a new query window after running this and check the pool name by running:

SELECT wg.name FROM sys.dm_exec_sessions s inner join sys.dm_resource_governor_workload_groups wg on s.group_id = wg.group_id where s.session_id = @@SPID

Then, change the connection and add in an additional parameter

database=RGDB;

Then run the query again. You should see the workload group name change.


UPDATE query periodically is slower by factor of 5000x I run a site (Magento) which have a problem with a specific UPDATE query.

Under heavy load, this query runs ~200 times/minute, and takes in average 1 ms (stdev 0.2 ms). However, at intervals of 1-2 hours, the query starts to take a lot of time to complete, 5-35 seconds. Restarting the database server (mysql) does not stop the issue, nor does restarting php-fpm. The only thing that restores the normal state is rebooting the server.

The query looks rather innocent to me:

UPDATE {table} SET {54 columns are set} WHERE {primary key = value}

The table is ~5.000-20.000 rows big. I have tried to check memory usage of mysql, and there is plenty of available ram and htop gives Mysql memory usage as 7.5G and mysqltuner says InnoDB data size is 5.9 G with a buffer pool of 10G. Mysqltuner also says open file limit used is 0% (8 of 5k).

Do you have any ideas what I can investigate to find the issue?

Since your server is under heavy load try to increase its mysql thread concurrency.

Try to set:

innodb_thread_concurrency at 0 (infinite)

innodb_read_io_threads at 64 (Maximum)

innodb_write_io_threads at 64 (Maximum)

Sources:

https://dba.stackexchange.com/a/2948/33029

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_concurrency


SSIS Package execution fails when started by user logging in with FreeTDS I am attempting to let a login kick off an SSIS package via the built-in SSISDB stored procedures instead of doing so through SQL Agent.

The login will be connecting through an Airflow DAG on a Ubuntu box, so we're utilizing FreeTDS / pyodbc to allow it to authenticate as a Windows Login, which is working fine.

However the following happens when launching the package:

Package is launched using [SSISDB].[catalog].[start_execution] @execution_id. Tried running both synchronously and asynchronously.
An execution ID is generated, along with an entry in [SSISDB].[catalog].[executions] with a status of 5 ("Pending")
~1 minute later the the execution "completes" and the execution ID is wiped from all SSISDB tables and does not show up in any of the built-in reports.

Even when adding exec parameters to create dump files, none are created during the package execution.

Bonus problem: During the ~1 minute timeframe, other packages being launched from SQL Agent that generally work fine often fail with this error message:

  The operation failed because the execution timed out.

Notes:

The SQL Server installation is on Windows and is SQL Server 2016 Ent.
This process works fine when using my Windows Authentication login from a Windows box.
The Windows account being used by the DAG is the agent service account, so has all necessary permissions.
The two SID fields in [SSISDB].[catalog].[executions] for the pending execution match the SIDs of other working executions being handled by SQL Agent for the same service account.
We are using TDS version 7.2 and the latest FreeTDS version.
No difference when using autocommit True/False
No blocking seen during execution

I am guessing the login is being re-authenticated when it is used to launch the external SSIS process, but am hoping there may be some way to make this work still.

Calling via CLR is an undesirable option due to the added complexity and security implications.

I am also currently testing launching them via dtexec via xp_cmdshell (looking promising) but it is unclear to me how/why that would work and using the stored procedures does not.

SSIS catalog executions impersonate the connected login, so probably correct that it's an auth issue.  Easy workaround is to provision a SQL Agent Proxy for the identity you want to run the package, and configure an Agent Job to run the package, invoking the job with sp_start_job.

This is similar to, but more secure than, using xp_cmdshell to run dtexec directly, or call the package execution procedures from powershell.  In both cases a local identity is used to execute the package.


How to export a single schema from Oracle EE to Oracle XE? I would like to export a schema (DDL or DDL+data) from Oracle EE (Enterprise Edition) to Oracle XE (Express Edition) for development purpose (in our case: for use as test databases for Java developers).

The source schema contains some features, that are not available in Oracle XE, but none of them is required in development. A target schema can be modified to be compatible with XE.

In particular:

partition declarations can be omitted,
storage declarations omitted or simplified (e.g. as little as possible initial space reserved for tables),
tablespace declarations omitted or replaced (a single tablespace used in XE),
materialized views with some advanced features can be simplified or replaced with simple views,
jobs can be omitted,
java functions (scarcely used but present in one pl/sql package) can be disabled or imported with compilation errors.

One way to achieve it is to export source DDL to an sql script and then modify it by hand. But I'm curious if you see any other, more efficient way.

Oracle version: 11g / 12c
size of an sql file containing DDL: 7MB

Note: There already exists a similar question, but in my case I accept some differences between source and target schema.

There is no such one command to do this kind of migration of a schema because as you mentioned, Oracle Express edition doesn't support all the features that are used in the Enterprise edition. However, I tried to collect some of the useful parameters which can be used during expdp/impdp to modify or include/exclude (un)wanted objects and features.

   partition declarations can be omitted.

In that case you can use PARTITION_OPTIONS parameter in impdp.

PARTITION_OPTIONS=[NONE | DEPARTITION | MERGE].

PARTITION_OPTIONS

   storage declarations omitted or simplified (e.g. as little as possible initial space reserved for tables).

TRANSFORM parameter in impdp enables you to alter object creation DDL for objects being imported.

TRANSFORM = transform_name:value[:object_type]

TRNASFORM

   tablespace declarations omitted or replaced (a single tablespace used in XE).

You can use REMAP_TABLESPACE parameter during impdp on Oracle XE instance.

REMAP_TABLESPACE=source_tablespace:target_tablespace

REMAP_TABLESPACE

  materialized views with some advanced features can be simplified or replaced with simple views.

I think you need to import DDL of such materialized views into a SQL FILE then create view manually.

  jobs can be omitted.

You can use EXCLUDE parameter to exclude jobs but currently, Data Pump Export/Import can exclude/include jobs which were created with DBMS_JOB.

Reference

  java functions (scarsely used but present in one pl/sql package) can be disabled or imported with compilation errors.

You can use EXCLUDE to ignore such java classes.

EXCLUDE=JAVA_CLASS

Now you can combine these parameters to form your expdp/impdp command.

Note: All included reference links are related to Oracle 11g version, please refer to the Oracle documentation related to your version.


Postgresql import user from linux I install postgresql 9.2 on a Ubuntu 12.10 machine

I tried to import linux's user to postgresql

but only way I can figure out is

CREATE USER Jack WITH PASSWORD '1234';

and do it 10 times (if there are 10 users)!!

Is there any faster way to do it?

Why don't you try doing it from webmin. If you install the PostgreSQL module, under the postgres users section there is an option Add a new PostgreSQL user when a Unix user is added.


MySQL Count(Table1.ColId) Where IN (any of Table2.Col1 ,Table2.Col2) I have 2 Tables

Table 1 has Id, Name
Table 2 has Id, Col1_IdVal, Col2_IdVal,Col3_IdVal,Col4_IdVal

I would like simple solution for

Count(T1.Id) WHERE T1.Id In (T2.Col1_IdVal,T2.Col2_IdVal,T2.Col3_IdVal,T2.Col4_IdVal)

Something I have tried does not return correct result set , too few in the result :

SELECT COUNT(T1.Id) FROM Table1 As T1
LEFT JOIN Table2 AS T2_1 ON T2_1.T2Column_1 = T1.Id
LEFT JOIN Table2 AS T2_2 ON T2_2.T2Column_2 = T1.Id
LEFT JOIN Table2 AS T2_3 ON T2_3.T2Column_3 = T1.Id
LEFT JOIN Table2 AS T2_4 ON T2_4.T2Column_4 = T1.Id
LEFT JOIN Table2 AS T2_5 ON T2_5.T2Column_5 = T1.Id;

Table 1 has Unique Values - it is Primary Key, Table 2 can have any number of values MANY.  Table2 columns are not foreign keys of Table1 - they are values that must exist in Table1 but other than that - they are not FKeys.

So basically Count(Id) Where Id Value Is in T2, Cols 1..n

Look for

SELECT COUNT(t1.Id) FROM Table1 As t1
{ INNER | LEFT } JOIN Table2 AS t2 ON t1.Id IN (t2.T2Column_1,
                                                t2.T2Column_2,
                                                t2.T2Column_3,
                                                t2.T2Column_4,
                                                t2.T2Column_5);


How to find values from one table missing in another? I am a Junior Developer. I have been asked to create a subquery to solve this problem. If the question is not clear then please let me know so I can provide more details.

I have two tables,Article and Author. I have been tasked with doing the following:

  Select distinct Author values that don't exist in the Author table. The result should not contain any Authors that are NULL or Empty String. The purpose is to find any Author names that ARE in the Article table but NOT in the Author table.

I have Author column in both Tables. Can this problem be solved by joining these two tables?

I have tried

select distinct name
from author
where name NOT IN ( select author
                    from article
                    where author IS NOT NULL)

It doesn't give me the result that I want :(

select author from article where author IS NOT NULL and author <> ''
except
select name   from author

since any comparison the null is false I think below works

select author from article where author <> ''
except
select name   from author


Is it possible to alias a column based on the result of a select+where? I have a table like this:

TB1:

COD | A001 | A002 | A003
 1    cars   baby   nasa

and then a second table:

TB2:

COD | NO_COL_TB1 | DESCRIPTION |
 1  |    A001    |  Something
 2  |    A002    |   lasagna

What I'm trying to do is something like this ( obviously this doesnt work )

select A001 as (select description from TB2 WHERE no_col_tb1= A001 )
      ,A002 AS (select description from TB2 WHERE no_col_tb1= A002 )
from TB1

i tried with inner joins and some dynamic sql but I just can't think in a logic for this. There's some questions about this but none of them could help me. I think this is impossible to do with a single statement and only possible with a Dynamic SQL.

EDIT:

The correct result would be:

SELECT COD,
       TB1.A001 AS 'Something',
       TB1.A002 AS 'Lasagna'
FROM TB1

and the result would be:

COD |SOMETHING | LASAGNA |
 1      cars       baby

Sorry to say but your table structure is difficult to work with considering what you want to do. There are various ways you can probably get the result, one way would be to use UNPIVOT and PIVOT, but it's ugly.

You could start by UNPIVOTing the data in TB1 from your columns to rows:

select *
from tb1 t1
unpivot
(
    val
    for col in (A001, A002, A003)
) u

This is going to return the data in the format of:

| COD |  val |  col |
|-----|------|------|
|   1 | cars | A001 |
|   1 | baby | A002 |
|   1 | nasa | A003 |

Then you could take that result and join it to TB2:

select
    d.COD,
    d.val,
    t2.DESCRIPTION
from
(
    select *
    from tb1 t1
    unpivot
    (
        val
        for col in (A001, A002, A003)
    ) u
)d
inner join tb2 t2
    on d.col = t2.NO_COL_TB1;

Which gives the result:

| COD |  val | DESCRIPTION |
|-----|------|-------------|
|   1 | cars |   Something |
|   1 | baby |     lasagna |

Now you have your new column names in the Description and the val in rows, but you want them in columns, so now you can apply the PIVOT function to it:

select *
from
(
    select
        d.COD,
        d.val,
        t2.DESCRIPTION
    from
    (
        select *
        from tb1 t1
        unpivot
        (
            val
            for col in (A001, A002, A003)
        ) u
    )d
    inner join tb2 t2
        on d.col = t2.NO_COL_TB1
) x
pivot
(
    max(val)
    for description in (Something, Lasagna)
) p;

This generates the final result you want:

| COD | Something | Lasagna |
|-----|-----------|---------|
|   1 |      cars |    baby |

Now all that is great if you know all the columns that you need to UNPIVOT and then PIVOT, but if you don't then you'll need to use dynamic SQL to solve it. Which will look something like this:

DECLARE
    @colsUnpivot AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsUnpivot
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'tb1' and
                 C.column_name like 'A%'
           for xml path('')), 1, 1, '')

select @colsPivot
  = stuff((select ','+quotename([DESCRIPTION])
           from Tb2
           for xml path('')), 1, 1, '')

set @query = 'select *
            from
            (
                select
                    d.COD,
                    d.val,
                    t2.DESCRIPTION
                from
                (
                    select *
                    from tb1 t1
                    unpivot
                    (
                        val
                        for col in ('+ @colsUnpivot +')
                    ) u
                )d
                inner join tb2 t2
                    on d.col = t2.NO_COL_TB1
            ) x
            pivot
            (
                max(val)
                for description in ('+@colsPivot+')
            ) p;';

exec sp_executesql @query;

It's long, but it should give you the same result. (dbfiddle demo)


Avoid multiple Case When - MySQL I have a query:
Select <CASE WHEN>, myfunction(param1, <CASE WHEN>)
FROM table

Both the Case When instances are the same set of conditions. So here I am clearly repeating the same conditions.
Question: Is there a way to not repeat the same Case When block efficiently?

For this you can use user defined Varaible
Select @res := CASE WHEN A = 1 then a END, myfunction(param1, @res)
FROM table1;

Or in mysql 8
WITH myselectanswer as
(Select  CASE WHEN A = 1 then a END myresult
FROM table1)
Select myselectanswer.myresult, myfunction(param1, myselectanswer.myresult)
FROM myselectanswer


Slow PostgreSQL query I'm building a search interface for a web site that currently searches through approximately 2 million pieces of real estate with approximately 60 million attributes (address, city, state, ZIP, beds, baths, assessed value, etc).

The tables are laid out so that there is a LEAD table (2 million rows) with one entry for each piece of real estate and a corresponding LEAD_DETAIL table (60 million rows) that holds name/value pairs for the attributes.

The LEAD table has the following structure:

LEAD_ID BIGINT PRIMARY KEY
LEAD_TYPE_NAME VARCHAR(50)
CREATED_DATE_TIME TIMESTAMP
MODIFIED_DATE_TIME TIMESTAMP
RECEIVED_EMAIL_ID BIGINT
NEW_LEAD SMALLINT
FDOLD_ID INTEGER

And the LEAD_DETAIL table has the following structure:

LEAD_DETAIL_ID BIGINT PRIMARY KEY
LEAD_ID BIGINT
NAME VARCHAR(100)
VALUE_STRING VARCHAR(1000)
VALUE_NUMERIC NUMERIC
VALUE_DATE DATE

The LEAD table has the following indexes (I've tried lots of indexing strategies to try and get this to work):

"lead_pkey" PRIMARY KEY, btree (lead_id)
"lead_created_date_time_idx" btree (created_date_time)
"lead_created_date_time_idx1" btree (created_date_time DESC)
"lead_created_date_time_lead_id_idx" btree (created_date_time DESC, lead_id)
"lead_fdold_id_idx" btree (fdold_id)
"lead_lead_id_created_date_time_idx" btree (lead_id, created_date_time DESC)
"lead_lead_id_lead_type_name_created_date_time_idx" btree (lead_id, lead_type_name, created_date_time DESC)
"lead_modified_date_time_idx" btree (modified_date_time)

And the LEAD_DETAIL table has the following indexes:

"lead_detail_pkey" PRIMARY KEY, btree (lead_detail_id)
"lead_detail_lead_id_name_key" UNIQUE CONSTRAINT, btree (lead_id, name)
"lead_detail_name_value_date_idx" btree (name, value_date)
"lead_detail_name_value_numeric_idx" btree (name, value_numeric)
"lead_detail_name_value_string_idx" btree (name, value_string)
"lead_detail_upper_value_string_idx" btree (name, upper(value_string::text))

Here is the query that I'm generating:

SELECT   lead.lead_id
FROM     lead, lead_detail
WHERE    lead_detail.name = 'State'
AND      UPPER(lead_detail.value_string) = 'FL'
AND      lead.lead_id = lead_detail.lead_id
ORDER BY lead.created_date_time DESC
LIMIT 5000

Now, the distribution of the data is such that currently every lead is in the state of FL, so this is not exactly a highly "selective" query.  More selective queries, e.g. for a small county, are quite fast.

Here is the explain plan on explain.depesz.com

My question is, given that we are not preventing users from issuing non-selective queries, how can we quickly return the top-n results for large results?  I was hoping to use an INDEX on the CREATED_DATE_TIME column in conjunction with an ORDER BY and a LIMIT to produce the results quickly.

I've been working on this for hours and could really use some help.  Thanks in advance!

Looks like (I'm by no means a Postgres expert) you have about two million rows in LEAD_DETAIL table that satisfy the state condition... those two million rows are retrieved, hash joined to leads, the result sorted to return the top 5000 rows. Could you move the state to leads table, so that it becomes a column there? Create and index on (upper(state), created_date_time) and try again (of course, rewriting your query to use the new state column in leads table).

And by the way, most of your indexes on leads table are useless. Why have lead_created_date_time_idx and lead_created_date_time_idx1 - they are the same, an ascending index can be used also for DESC sorts. Furthermore, why include lead_id in indexes (except the one supporting the primary key constraint)?


Order by taking less time than max() in oracle I am using oracle db and I am running 2 queries to find the maximum id from an indexed column.

1st query -

select max(id)
from tableName

Explain Plan:

Plan hash value: 3791258597

ID
Operation
Name
Rows
Bytes
Cost(%CPU)
Time

0
 SELECT STATEMENT

1
6
1(0)
00:00:01

1
 SORT AGGREGATE

1
6

2
  INDEX FULL SCAN (MIN/MAX)
Index Name
1
6
1(0)
00:00:01

It takes 2.839 seconds to execute on a table having nearly 80000000 rows.

2nd query -

select id
from (
   select id
   from tableName
   order by id desc)
where rownum < 2

Explain Plan:

Plan hash value: 1514191988

ID
Operation
Name
Rows
Bytes
Cost(%CPU)
Time

0
 SELECT STATEMENT

1
13
27876 (1)
00:05:35

* 1
 COUNT STOPKEY

2
VIEW

84M
1046M
27876 (1)
00:05:35

3
 INDEX FULL SCAN
INDEX Name
84M
482M
27876 (1)
00:05:35

It takes 2.6 seconds to execute on the same table.

When I see the explain plan of the 1st query, I see that it is using SORT(AGGREGATE). Now I don't understand is that since the column is indexed, it should directly return the max value instead of using SORT(AGGREGATE) function.

Why max() is taking more time than order by?


getting oracle error ora-00103 when I try to execute a package in oracle 10g I have to delete and repopulate tables on a remote database.To access the remote tables I have created synonyms. my package is as follows

create or replace
PACKAGE eco_push IS
PROCEDURE waters_data_push;
END eco_push;

Package body is as follows

create or replace
PACKAGE BODY eco_push is

procedure waters_data_push
   IS

 --DELETING FROM WATERS_SITE

DELETE FROM watersitesyn;  -------------   error line (from)

-- Insert into table WATERS_SITE
     INSERT INTO watersitesyn
            (SITE_ID,SITE_NAME,
            CLI_NAME,ECOTRAC_NO,
            APP_REF,SEQ_NO,
            ACTIV_CODE,LOT_PLAN,
            FACIL_LOC,LAT,
            LON
            )

    SELECT DISTINCT
           st.ID,
           st.NAME,
           cli.NAME,
           cli.ECOTRACKNO,
          /*app.APPROVALREF,*/  CASE WHEN to_date(appst.VALIDTO)IS NOT NULL THEN (select app.APPROVALREF from APPROVALSITE appst, APPROVAL app where  SYSDATE BETWEEN to_date(appst.VALIDFROM) and to_date(appst.VALIDTO)and appst.site_id = st.ID)
                                     WHEN to_date(appst.VALIDTO) IS NULL THEN (select app.APPROVALREF from APPROVALSITE appst, APPROVAL app where  SYSDATE >to_date(appst.VALIDFROM) and appst.site_id = st.ID)
                                END,
           app.APPROVALSEQ,
           luact.CODE,
           CONCAT(lp.LOT,lp.PLAN),
           st.LOCATION,
           geop.LATITUDE,
           geop.LONGITUDE
     FROM  SITE st,
           CLIENT cli,
           APPROVAL app,
           APPROVALSITE appst,
           LU_ACTIVITY luact,
           LOTPLAN lp,
           GEOPOINT geop
     WHERE
          sysdate  between  to_date(appst.VALIDFROM) and to_date(appst.VALIDTO)
          AND  geop.ID = st.GEOPOINT_ID
          AND  lp.SITE_ID = st.ID
          AND  appst.APPROVAL_ID = app.ID;

......

I am getting the following error

Error: PLS-00103: Encountered the symbol "FROM" when expecting one of the following:     constant exception <an identifier>    <a double-quoted delimited-identifier> table long double ref    char time timestamp interval date binary national character    nchar

How to solve the error?

Thanks in advance

The DELETE FROM watersitesyn; code is still in the declarative part of your procedure.
You need to add a BEGIN to end the declarative part and begin the executable part as described in this section of the Oracle 11.2 Database 2 Day Developer's Guide.


Enhance performance of update query I have a query that is running against a table with 50 million rows.  The phone number is stored as a CHAR(10) and an ETL process is changing the phone number if it has certain values.  We are seeing poor performance of this query and was wondering if anyone has any suggestions on improving performance.  No index currently exists with the phone number.

UPDATE Table SET phone='' WHERE ( phone IS NULL
              OR phone = ''
              OR phone = '0000000000'
              OR phone = '0'
              OR phone like '111%'
              OR phone like '222%'
              OR phone like '333%'
              OR phone like '444%'
              OR phone like '555%'
              OR phone like '666%'
              OR phone like '777%'
              OR phone like '888%'
              OR phone like '999%'
              OR phone like '800%'
              OR phone like '900%'
            )

It depends on what you mean by improve performance.  You can't make that query faster, it needs to scan the table.  But you can keep it from using too many resources and blocking other processes by chunking it, which is essentially iterating through batches of  rows to perform multiple smaller updates.

There must be some option to just write it to the target table instead of updating in place.

You could also forego the scrub altogether and use a non-persisted computed column in the target database to reformat the phone number if you don't need to index it.


List all Oracle procedures/functions (including in packages) I want to list all procedures/functions in an Oracle server, including procedures/functions in packages. Am I doing it right?

SELECT
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE up.OBJECT_NAME END AS name,
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.OBJECT_NAME ELSE NULL END AS package
FROM USER_PROCEDURES up
LEFT JOIN USER_IDENTIFIERS ui ON up.PROCEDURE_NAME = ui.NAME
WHERE up.OBJECT_TYPE = 'PROCEDURE' OR ui.TYPE = 'PROCEDURE';

SELECT
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE up.OBJECT_NAME END AS name,
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.OBJECT_NAME ELSE NULL END AS package
FROM USER_PROCEDURES up
LEFT JOIN USER_IDENTIFIERS ui ON up.PROCEDURE_NAME = ui.NAME
WHERE up.OBJECT_TYPE = 'FUNCTION' OR ui.TYPE = 'FUNCTION';


How to access pg_settings from separate connection I am trying to access the pg_settings table from a separate connection belonging to a legacy application.

I know how to identify the connection's procid from pg_stat_activity, but I am unsure how to get to its pg_settings table from here.

I've checked pg_stat_all_tables, but that has no reference to any pg_settings tables.

information_schema.tables has a single row for pg_settings with table_type set to VIEW which indicates to me that I should be looking for a view rather than a table.

How can I access pg_settings belonging to a separate connection?

pg_settings is a view, not a table.

And every session has its own version of it. Per documentation:

  The change only affects the value used by the current session.

The attempt to access it from a different connection (session) is futile.


SQL Server Jobs - syspolicy_purge_history fails The job syspolicy_purge_history fails at the 3rd step. The error that I get is the following one:

  Executed as user: UserABC.
   A job step received an error at line 1 in a PowerShell script.
   The corresponding line is 'set-executionpolicy RemoteSigned -scope process  -Force'.
   Correct the script and reschedule the job.
   The error information returned by PowerShell is: 'Security error.  '.   Process Exit Code -1.  The step failed.

Was wondering if you guys have faced this before and how to go about fixing this.

Thanks to the comment of @Tony Hinkle.  I was able to resolve the issue based on the comment at the bottom of this thread.

SQL Server 2012 syspolicy_purge_history fails step 3 "Erase Phantom System Health Records."

Under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps130 I changed the key ExecutionPolicy value to Unrestricted.

I did not make any changes to keys under HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqltoolsps130.


ST_GeomFromGeoJSON() Causes postgres to crash I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries.
After some debugging, using the examples provided by postgis, it appears the ST_GEOMFROMGeoJSON() function causes to server to crash.

Crashes:

SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As wkt;

Operates normal:

SELECT ST_AsText(
         ST_Transform(
               ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
                                         743265 2967450,743265.625 2967416,743238 2967416))',2249)
         ,4326)
        ) As wgs_geom;

When looking into the log I find these entries related to the crash:

2014-11-21 11:27:46 CET LOG:  server process (PID 2377) was terminated by signal 11: Segmentation fault
2014-11-21 11:27:46 CET DETAIL:  Failed process was running: SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As wkt;
2014-11-21 11:27:46 CET LOG:  terminating any other active server processes
2014-11-21 11:27:46 CET WARNING:  terminating connection because of crash of another server process
2014-11-21 11:27:46 CET DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2014-11-21 11:27:46 CET HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2014-11-21 11:27:46 CET LOG:  all server processes terminated; reinitializing
2014-11-21 11:27:46 CET LOG:  database system was interrupted; last known up at 2014-11-21 11:24:11 CET
2014-11-21 11:27:46 CET LOG:  database system was not properly shut down; automatic recovery in progress
2014-11-21 11:27:46 CET LOG:  record with zero length at 18/34BCAD4
2014-11-21 11:27:46 CET LOG:  redo is not required
2014-11-21 11:27:46 CET LOG:  database system is ready to accept connections
2014-11-21 11:27:46 CET LOG:  autovacuum launcher started

Searching the web did not yield into a solution. Maybe I´m not searching for the right problem?

I tried to restart the server as maybe the filesystem has some faults, without results.
The server itself is a Ubuntu 12.04 machine as a VPS.
The postgresql server is 9.3.5 and Postgis 2.1.4

Update
As Craig Ringer suggested I used gdb to see what happens when the process crashes. This is the first output:

Program received signal SIGSEGV, Segmentation fault.
0xb70dda59 in _IO_vfprintf_internal (s=0xbfde7150, format=<optimized out>,  ap=0xbfde7298 "") at vfprintf.c:1630
1630    vfprintf.c: No such file or directory.

Also this is the output ofpostgis_full_verion():

POSTGIS="2.1.4 r12966" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009"
GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
LIBJSON="UNKNOWN" TOPOLOGY RASTER

Am I right to say something is wrong with vfprintf.c? eg it does not exists?

It says LIBJSON="UNKNOWN" could that be of influence?

From the osgeo ticket

  The root our our crash problem was library version mismatch. The build system was using 0.9, but I was deploying on a system with 0.11. The RPM packages only declared their requirement as "json-c" without any version specificity. Apparently json is not forward binary compatible, so packagers beware: you have to peg your json-c version in your package dependency declarations.

followed by

Resolution set to invalid
Status changed from new to closed
Closing because this is a build problem.


After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB I have a table: OrderDetails.

For 5 years, only the odd Deadlock has occured.
If it happened, it happened on indexes which was on OrderDetails table
Now all of a sudden, deadlocks are everywhere - happens very minute.
We have not made any changes to the application or database - the indexes rebuild every week.

The problem is that the table OrderDetails gets locked on occasion now and all hell breaks loose

Could someone please help and see if they spot anything from the Logs:
How can I get rid of the objectLock on the whole OrderDetails Table?
Why would the table lock -in the past it was only the Indexes that caused deadlocks.
Does this mean that the Indexes failed to find the appropriate records and now the whole table is locked?

It is an Update and a Select that are deadlocking here, and the rows that are locking are not related - they don't have the same Reference number or OrderID

 spid18s,Unknown,waiter id=process91bb58 mode=X requestType=convert
 spid18s,Unknown,waiter id=process9a9018 mode=S requestType=convert
 spid18s,Unknown,waiter-list
 spid18s,Unknown,owner id=process9a9018 mode=IS
 spid18s,Unknown,owner id=process91bb58 mode=IX
 spid18s,Unknown,owner-list
 spid18s,Unknown,objectlock lockPartition=0 objid=1194487334 subresource=FULL dbid=5 objectname=CompZ.dbo.OrderDetails id=lock32163640 mode=IX associatedObjectId=1194487334
 spid18s,Unknown,resource-list
 spid18s,Unknown,Proc [Database Id = 5 Object Id = 711009614]
 spid18s,Unknown,inputbuf
 spid18s,Unknown,OrderNoRef = @OrderNoRef and ClientID = @ClientID and  OrderNo <> @OrderNo
 spid18s,Unknown,where
 spid18s,Unknown,select  Distinct OrderNoRef from OrderDetails
 spid18s,Unknown,frame procname=CompZ.dbo.SP_Duplicate_Ref line=12 stmtstart=530 sqlhandle=0x030005004e25612a5f3d2a0194a100000100000000000000
 spid18s,Unknown,executionStack
 spid18s,Unknown,process id=process9a9018 taskpriority=0 logused=0 waitresource=OBJECT: 5:1194487334:0  waittime=8640 ownerId=17099323 transactionname=SELECT lasttranstarted=2013-06-11T14:34:34.230 XDES=0x2567fb58 lockMode=S schedulerid=2 kpid=15548 status=suspended spid=76 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2013-06-11T14:34:26.570 lastbatchcompleted=2013-06-11T14:34:26.570 clientapp=.Net SqlClient Data Provider hostname=WEBSVR hostpid=16664 loginname=*** isolationlevel=read committed (2) xactid=17099323 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
 spid18s,Unknown,Proc [Database Id = 5 Object Id = 562101043]
 spid18s,Unknown,inputbuf
 spid18s,Unknown,Update OrderDetails set OrderState = 15 where OrderNo = @OrderNo And OrderState in (1,2,3)
 spid18s,Unknown,frame procname=CompZ.dbo.SP_Insert_OrderDet line=59 stmtstart=1726 stmtend=1890 sqlhandle=0x0300050033fb8021a4e1360011a100000100000000000000
 spid18s,Unknown,executionStack
 spid18s,Unknown,process id=process91bb58 taskpriority=0 logused=0 waitresource=OBJECT: 5:1194487334:0  waittime=8609 ownerId=17099322 transactionname=UPDATE lasttranstarted=2013-06-11T14:34:34.230 XDES=0x3a2844e0 lockMode=X schedulerid=1 kpid=23896 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-06-11T14:34:25.347 lastbatchcompleted=2013-06-11T14:34:25.327 clientapp=.Net SqlClient Data Provider hostname=WEBSVR hostpid=16664 loginname=*** isolationlevel=read committed (2) xactid=17099322 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
 spid18s,Unknown,process-list
 spid18s,Unknown,deadlock victim=process9a9018
 spid18s,Unknown,deadlock-list

One of the Indexes had the page and row locks removed (was not documented so I did not know of this - I apologize for stating that nothing changed in the title)- is it correct to say that if both the row and page locks for a Index is disabled that it would then lock the table which uses that index? (Yes, if row and page locks are turned off, then only the table lock is available.)

Use SQL Profiler to trace your deadlocks and find a few example queries that are the root cause.

Then copy that SQL to SSMS and view the execution plan for it.

Look to see what kind of locks are being held when the query runs.

It's almost certain that you are experiencing table locks - actually, as you state, someone removed row-level locks and page-level locks.

You have options:

Modify your SELECT statement to use READ UNCOMMITTED or (nolock). This means this query can read dirty data. Only you will know whether this will be acceptable with your data set - very often, it is fine to have dirty reads and doesn't impact the functionality of the system.
Use row-level locks. You can force these using a query hint.
Use row-versioning - this option means the updates operate on a different version of the rows to the ones that the SELECT statement is reading from avoiding deadlocks altogether.

You can find out more details about row versioning and other deadlock reducing techniques here.


Field both NOT NULL and DEFAULT NULL This MySQL table had me perplexed for a moment:

mysql> desc quux;
+---------------------------+-----------------------+------+-----+---------+----------------+
| Field                     | Type                  | Null | Key | Default | Extra          |
+---------------------------+-----------------------+------+-----+---------+----------------+
| foobar                    | int(11)               | NO   |     | NULL    |                |
(...)

mysql> show create table quux;
(...)
`foobar` int(11) NOT NULL,
(...)

Since the foobar field was never created with a DEFAULT clause, it gets automatically assigned DEFAULT NULL. However, at a first sight this looks like contradicting the fact that it was also defined as NOT NULL.

Then I realized that this schema aims to force to insert a value (and a non-NULL one) in foobar when adding a new record.

Is this an acceptable way to do so, or there are better ways?

That's perfectly acceptable, it basically states:

  You have to provide a value for this, but I have no clue what that would be.

If you define a default value, you claim that this is a sensible value for any row that doesn't supply a value - which is not really feasible for most attributes.

Think about a column named first_name - you want to make sure a value is supplied but there is no way you can come up with a sensible default value for that. Or a column named salary in an employee table.


MySQL - Best long read query solution within a replication based system I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 minute execution times, and they will be run in parallel, say 10-15 at a time, but they will each be mutually exclusive and unaware of any sibling processes.
QUESTIONS

Is it better to stop replication, issue the read query, then restart replication ? If so, what happens to a currently running read query if the replication is suddenly turned back on (by a sibling process)?
is it safe to allow simultaneous read queries and replication processing ?

You need to be aware of query results and query behavior with replication running. While there are a minimum of two threads for MySQL Replication, it is the SQL thread that can get in the way of SELECT queries. Why?
MyISAM
Each time an INSERT, an UPDATE, or a DELETE is executed, a full table locked is issued. That can block SELECTs. The only exception is when you have concurrent_insert set to 2 and the MyISAM table experiences no UPDATEs or DELETEs.
Please see my earlier posts on MyISAM behavior.

Dec 04, 2013 : Should we explicitly lock MyISAM tables or is it implicit....?
Nov 25, 2011 : MySQL MyISAM Locking question

AFFECTS ON REPLICATION : You should be a little concerned here because the SQL thread only processes INSERTs, UPDATEs, DELETEs, and various DDL commands. Should any of these command get issue by the SQL thread during MySQL Replication on a MyISAM table you are reading, there will be some latency due to the momentary table lock.
InnoDB
InnoDB can be a little more forgiving in allowing you to read from an InnoDB table that is being written, but it comes at a price. What is the price ???
InnoDB allows for MVCC (Multiversion Concurrency Control) and Transaction Isolation. This means InnoDB will write gobs of housecleaning and point-in-time information so that you can read rows from an InnoDB table without blocking incoming writes, and vice versa.
Here is a pictorial representation of InnoDB's Architecture

All that housecleaning and point-in-time information sits inside the Undo Space and Rollback Segments inside the system tablespace.
AFFECTS ON REPLICATION : When it comes to MySQL Replication, INSERTs, UPDATEs and DELETEs against an InnoDB from an active SQL thread will not block SELECTs from that same InnoDB tables. Of course, any DDL against an InnoDB table will block SELECTs just the same as a MyISAM table would.
MySQL REPLICATION
MySQL Replication can also be affected for the same reasons I mentioned. The shoe would be on the other foot: the SQL thread would get locked if it needs to do an INSERT, UPDATE, or DELETE against a MyISAM table. For InnoDB, MVCC info would be written up to permit SELECTs on the Slave.
EPILOGUE
If the Slave has hundreds of DB Connections, one more DB Connection (the SQL thread) may not be a lot to worry about
If you do know that the Slave may experience an onslaught of INSERTs, UPDATEs, and DELETEs coming from the Slave's  SQL Thread, you could run STOP SLAVE; to disconnect the SQL thread and stop processing SQL commands and also stop downloading Master binlog events. As an alternative, you could run STOP SLAVE SQL_THREAD; to disconnect the SQL thread but allow incoming master binlog event to continue to be downloaded to its realy logs and await processing when the START SLAVE; is issued later. If the number of SQL commands coming from MySQL Replication are few and far between, then MySQL Replication can be left running.


Size of record pointer in InnoDB? I am trying to put on a scale redundant attributes and performance gain. Although storing redundant information causes many anomalies, I am solely focusing on the size of a redundant attribute vs. storing a pointer (or reference) to another tuple in different table.

So what is the size of pointer to a tuple in InnoDB Mysql systems? I found that MyISAM uses 6 bytes. Is it the same for InnoDB? For reference, 6 bytes makes storing 3 SMALLINTs (2 bytes each) in a separate table pointless in terms of storage. Perhaps an equivalent question would be: what is the size of a record address?

MyISAM defaults to 6 bytes for a data pointer in an index.  And defaults to 5 bytes for index pointers.  The "6" you are referring to is a byte offset from the .MYI file into the .MYD file.  That is not necessarily relevant to the rest of your question.

InnoDB is much more complex.  It does not use pointers, only columns.

The data is stored in a BTree that is ordered by the PRIMARY KEY.  (Actually a B+Tree.)

A secondary index is stored in a separate BTree that has the PK in each node record.

FKs are simply the indicated column(s), and are used to do a lookup in a suitable index in the other table.

id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

takes 1 byte, plus some overhead

id VARCHAR(255) CHARSET utf8mb4 PRIMARY KEY,
stuff1 VARCHAR(255) CHARSET utf8mb4,
stuff2 VARCHAR(255) CHARSET utf8mb4,
INDEX(stuff1, stuff2)

This last secondary index takes up to 3*(2+255)*4+? bytes --

3 columns (stuff1, stuff2, id)
2 -- length
255 -- max length in characters for utf8mb4
4 -- max bytes/character in utf8mb4
+? -- There is overhead; this varies.

Back to "pointers".  There are some "pointers" at lower levels:

4 byte block pointer (16KB blocks), hence a limit of 64TB on table size.
20 byte pointer to overflow data (used to point to the 'rest' of a large TEXT or BLOB.
Some 1-byte and 2-byte lengths or pointers inside a block.
? bytes for a transaction id.  (See MVCC, etc)

As a Rule of Thumb, an InnoDB table takes 2-3 times the disk space as the equivalent MyISAM table.

  So what is the size of pointer to a tuple in InnoDB Mysql systems?

If TINYINT UNSIGNED suffices (up to 255 items in the other table), then think of it as 1 byte in each table, plus 1 byte for each secondary key in the target table.
INT is often used (with a usually more-than-adequate 2-billion limit), think of it as 4 bytes each.

Perhaps a more important question is whether to have a 'surrogate' (AUTO_INCREMENT) id on a table.  Count the number of references in this table and from other tables to judge its bulk.  And use the smallest flavor of INT that will be safe.

That brings up "burning" of ids.  In almost all cases, INSERT (and its variants) will first allocate the auto_inc ids that it might need.  If it does not use them all (eg for INSERT IGNORE), the ids are lost (burned).  This can lead to using up, say, TINYINT much faster than anticipated.  For bulk normalization, I provide this technique:  http://mysql.rjweb.org/doc.php/staging_table#normalization


Copy Postgres databases with indexes between instances How do you copy a single Postgres database between installations without using pg_dump and without regenerating indexes?

We have an application we deploy which uses Postgres 8.4.7 database in Linux. We have a rather large static data set (4GB) which is is indexed.

Using pg_dump we have to index the data once it is restored to the second Postgres instance. Indexing this data can take up to 80 minutes, so we would rather deploy the database with the index already generated.

From postgres documentation it appears that databases are contained in sub-directories under postgresql/8.4/main/base based on their OID.

My first attempt was to create an empty database in Postgres, shutdown the instance, then copy the data from the existing instance to the directory of the OID for the empty database. This failed as my indexes were not working. Preforming a vacuum gave me the following error:

WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation "pg_type" page 6
WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation "pg_depend" page 39
ERROR:  xlog flush request 0/8B199518 is not satisfied --- flushed only to 0/8AF81680
CONTEXT:  writing block 39 of relation base/17004/2608

If you want to copy a complete PostgreSQL database within its cluster, the fastest method is to use it as TEMPLATE in a CREATE DATABASE statement. I quote the manual:

  By default, the new database will be created by cloning the standard
  system database template1. A different template can be specified by
  writing TEMPLATE name. In particular, by writing TEMPLATE template0,
  you can create a virgin database containing only the standard objects
  predefined by your version of PostgreSQL. This is useful if you wish
  to avoid copying any installation-local objects that might have been
  added to template1.

CREATE DATABASE db_copy TEMPLATE db_org;

This effectively copies underlying files around like you tried manually. Except that is sets everything up to work correctly.

You may want to clean up your original before you do (depends):

VACUUM FULL ANALYZE;


Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date):

| COLUMN_NAME   | TYPE        |
|---------------|-------------|
| serial        | varchar     |
| lot           | varchar     |
| model         | varchar     |
| line          | varchar     |
| machine       | varchar     |
| datetimestamp | timestamptz |

Example values:

| serial | lot        | model  | line | machine | datetimestamp       |
|--------|------------|--------|------|---------|---------------------|
| 1      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:39 |
| 2      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:42 |
| 3      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:45 |

I have created a function to get data from table model201707 and insert it into table tb_moldsummary:

| COLUMN_NAME   | TYPE      |    |    |     |
|---------------|-----------|----|----|-----|
| machine_name  | varchar   | NN | PK |     |
| model         | varchar   | NN | PK | UNQ |
| mold          | varchar   | NN | PK | UNQ |
| cavity        | varchar   | NN | PK | UNQ |
| totalshots    | int4      |    |    |     |
| datetimestamp | timestamp |    |    |     |

Example values:

| machine | model  | m_id | cav_id  | totalshots | datetimestamp       |
|---------|--------|------|---------|------------|---------------------|
| 1       | sample | 16   | F22_F23 | 3          | 2017-02-28 07:22:45 |

Function:

CREATE OR REPLACE FUNCTION insert_data_func()
RETURNS TRIGGER AS
$BODY$

EXECUTE 'INSERT INTO tb_moldsummary '
    ||  'SELECT NEW.machine, NEW.model, split_part(NEW.lot, ''_'', 1), '
    ||  'SUBSTRING(NEW.lot FROM (POSITION(''_'' in NEW.lot)+1) FOR LENGTH(NEW.lot)), COUNT(lot), NEW.datetimestamp '
    ||  'FROM model' || to_char(CURRENT_TIMESTAMP, 'YYYYMM')
    ||  ' WHERE lot = NEW.lot AND machine = NEW.machine GROUP BY machine, model, lot '
    ||  'ON CONFLICT ON CONSTRAINT tb_summary_unique '
    ||  'DO UPDATE SET '
    ||  'machine = NEW.machine, '
    ||  'totalshots = tb_moldsummary.totalshots + 1, '
    ||  'datetimestamp = NEW.datetimestamp ';

RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER insert_data_trigger
AFTER INSERT ON model201707
FOR EACH ROW
EXECUTE PROCEDURE insert_data_func();

Error:

[Err] ERROR:  missing FROM-clause entry for table "new"
LINE 1: INSERT INTO tb_moldsummary SELECT NEW.machine, NEW.model, sp...

I already checked the following links, but I'm confused:

PLPGSQL STATEMENTS EXECUTING DYNAMIC SQL

insert-record-into-a-table-in-a-trigger-function

What's the correct syntax for this dynamic UPSERT?

Multiple problems. This should work:

CREATE OR REPLACE FUNCTION insert_data_func()
  RETURNS TRIGGER AS
$func$
BEGIN  -- !
   EXECUTE
      $x$INSERT INTO tb_moldsummary AS t
         SELECT $1.machine                               -- !
              , $1.model
              , split_part(lot, '_', 1)
              , right(lot, position('_' IN lot) * -1)    -- ! simpler
              , COUNT(lot)
              , $1.datetimestamp
         FROM   model$x$ || to_char(now(), 'YYYYMM') || '
         WHERE  lot = $1.lot
         AND    machine = $1.machine
         GROUP  BY machine, model, lot
         ON     CONFLICT ON CONSTRAINT tb_summary_unique
         DO     UPDATE
         SET    machine = $1.machine                     -- !
              , totalshots = t.totalshots + 1
              , datetimestamp = $1.datetimestamp'
   USING NEW;                                            -- !

   RETURN NEW; -- can be RETURN NULL for AFTER trigger
END
$func$  LANGUAGE plpgsql;

I marked lines with important fixes.

Major points

Pass NEW to EXECUTE with the USING clause (as instructed in the answers to the question you referenced):

Insert RECORD into a table in a trigger function?

Dollar-quoting helps to simplify the syntax for string literals containing single quotes.
Replace

SUBSTRING(NEW.lot FROM (POSITION('_' in NEW.lot)+1) FOR LENGTH(NEW.lot))

with this simpler and faster equivalent expression:

right(NEW.lot, position('_' IN NEW.lot) * -1)


Partition : understanding files and file groups We are trying to partition a large history table. Being new to partitioning, I was trying to understand the concept of files and file groups. File groups can be set up in following three ways :

1. Each file group on different disk
2. File groups on same disk
3. One file group on one disk with all files

MSDN says that you can take advantage of parallel I/O access in first method, if the disk has RAID set up, thus enhancing performance. Second method does not have additional performance benefit, however, maintenance is faster as each file group separates out the file from rest of the database. I searched for performance benefits of the third method.  I did not get the definite picture. For the third method, since the files are not divided in file groups, will it affect maintenance, thereby increasing downtime?  Does it have any performance gain over the second method?

There shouldn't be a performance gain by the third option over the second. When you think about it, it's almost the same as having all your user objects in the PRIMARY filegroup. One performance advantage you can achieve with the second option is to flag the older historical filegroups as read-only once you've confirm no additional data modifications are expected, indexes are rebuilt and statistics updated. Once read-only has been implemented, locking doesn't become an issue when querying data in those filegroups.


Make use of LAG analytic function

SELECT xID, MAX(xDATE),xValue FROM
(
SELECT t.*,
       CASE
            WHEN xvalue = 1 AND LAG(xvalue) OVER(
                 PARTITION BY xid
                 ORDER BY xdate
            ) = 0 THEN 1
            ELSE 0
       END
  AS cond
FROM test t
) s WHERE s.cond = 1
GROUP BY xID,xValue

Demo


I am going to take a very wild guess on this one
My guess is that you upgraded to MySQL 5.7 but did not upgrade the the mysql schema.
I wrote about the mysql.user table having a different number of columns per version:

Oct 10, 2014 : MySQL service stops after trying to grant privileges to a user
Aug 07, 2013 : MySQL - ERROR 1045 (28000): Access denied for user : permission issue
May 24, 2013 : How often should MySQL (stock, Percona, etc.) be upgraded?
May 01, 2013 : How often should MySQL (stock, Percona, etc.) be upgraded?
Apr 12, 2012 : Cannot GRANT privileges as root (I actually show the differences in the column layouts in MySQL 5.6, 5.5, 5.1, 5.0, 4.x)

Now for the shocker
Did you know the following about mysql.user in MySQL 5.7 ?

There are 45 columns
There is no longer any column named password
The column authentication_string stores the password now
I mentioned this Oct 28, 2015 : Install MySQL for Windows from .zip and reset root password

At this point, you are probably asking : Why in the world did create user 'testing'@'localhost' identified by 'password'; work ?
Look in my Apr 12, 2012 post. Please note the the column layout for mysql.user. The column authentication_string column appears in MySQL 5.6 and MySQL 5.5.
Please run this query
select Host, User, password_expired,Password,authentication_string from mysql.user;

You should see the encrypted password in the authentication_string column
What makes me believe this to be the case was the clue you provided
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

The command SHOW GRANTS; no longer shows the encrypted password in MySQL 5.7. I ran in to this issue even with pt-show-grants.
What should you do ?
Perhaps you should run mysqld --upgrade to fix the column layout. It looks like this in 5.7:
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

mysql>


Maximum granularity of user permissions (certain rows within table?) Our current system architecture is fairly simple. There's a global list of devices, which move between gateways. The idea is such that the global list of devices is stored in one table, such that any gateway can read the information about any device, but can only write to the devices resident on their specific gateway.

At the moment, this must be enforced in client logic, which is obviously not quite what I'd want, for a few important (and less important) reasons. I'd much prefer it enforced at the server level.

Does there exist a common database which makes this sort of permissions granularity definable?

You can grant access to views and stored procedures without granting direct access to the underlying tables (see https://stackoverflow.com/questions/4134740/grant-select-permission-on-a-view-but-not-on-underlying-objects amongst similar questions for some detail). This will certainly work for reading from views, though I'm not sure about writing to them (it would be difficult for it to know if they should be writing specific things, so you may have to manage this with INSTEAD OF triggers instead of it being done for you).

With the permissions correctly set the users can only access what the procedures/views expose to them, so you can control their access by giving different users different procs/views that each have different filtering clauses. You can even give the different views/procs the same name if you give each user (or class of user) its own schema, so you don't need different code/configuration elsewhere for User1, User2, ..., UserN as they can all run SELECT <something> FROM FilteredView and get results (User1 might see results from the view u1.FilterdeView, User2 and User3 might have reports as their default schema so they get results from reports.FilteredView instead, and so forth).

My knowledge of this is MSSQL specific, but I'm pretty sure other major DMBSs (such as Oracle or postgres) have very similar access granularity controls.


PolyLang WP Update to replace one string to another I have to replace one string to another one in post_content column in table wp_post ( Wordpress ).
My query works only then when I use SELECT but when I want to use UPDATE command with REPLACE then I have error. My query:
UPDATE wp_posts SET post_content = REPLACE(post_content, 'str1', 'str2') FROM wp_posts WHERE ID IN (
SELECT ID
FROM wp_posts
WHERE ID IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description, '"ar";i:', -1), ';',1) FROM wp_term_taxonomy WHERE taxonomy = 'post_translations'))

Mysql said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM wp_posts WHERE ID IN (
SELECT ID
FROM wp_posts
WHERE ID IN (SELECT S...' at line 1

Basically, you constuction to get the right ID was doubles and UPDAT has no fROM clause
There WHERE clause you should try first in a SELECT if you get all IDs
UPDATE wp_posts
SET post_content = REPLACE(post_content, 'str1', 'str2')
WHERE ID IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description, '"ar";i:', -1), ';',1) FROM wp_term_taxonomy WHERE taxonomy = 'post_translations')


DEFAULT CONSTRAINT, worth it? I usually design my databases following next rules:

Nobody else than db_owner and sysadmin have access to the database tables.
User roles are controlled at application layer. I usually use one db role to grant access to the views, stored procedures and functions, but in some cases, I add a second rule to protect some stored procedures.
I use TRIGGERS to initially validate critical information.

CREATE TRIGGER <TriggerName>
ON <MyTable>
[BEFORE | AFTER] INSERT
AS
    IF EXISTS (SELECT 1
               FROM   inserted
               WHERE  Field1 <> <some_initial_value>
               OR     Field2 <> <other_initial_value>)
    BEGIN
        UPDATE MyTable
        SET    Field1 = <some_initial_value>,
               Field2 = <other_initial_value>
        ...
    END

DML is executed using stored procedures:

sp_MyTable_Insert(@Field1, @Field2, @Field3, ...);
sp_MyTable_Delete(@Key1, @Key2, ...);
sp_MyTable_Update(@Key1, @Key2, @Field3, ...);

Do you think that, on this scenario, worth it to use DEFAULT CONSTRAINTs, or I'm adding an extra and unnecessary job to the DB server?

Update

I understand that by using DEFAULT constraint I'm giving more information to someone else that must to administer the database. But I'm mostly interested on performance.

I assume that the database is checking always default values, even if I supply the correct value, hence I'm doing the same job twice.

For example, Is there a way to avoid DEFAULT constraint within a trigger execution?

I assume that the database is checking always default values, even if I supply the correct value, hence I'm doing the same job twice.

Um, why would you assume that? ;-). Given that Defaults exist to provide a value when the column that they are attached to is not present in the INSERT statement, I would assume the exact opposite: that they are completely ignored if the associated column is present in the INSERT statement.

Fortunately, neither of us needs to assume anything due to this statement in the question:

  I'm mostly interested on performance.

Questions about performance are nearly always testable. So we just need to come up with a test to allow SQL Server (the true authority here) to answer this question.

SETUP

Run the following once:

SET NOCOUNT ON;

-- DROP TABLE #HasDefault;
CREATE TABLE #HasDefault
(
  [HasDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NULL,
  [SomeDate] DATETIME NOT NULL DEFAULT (GETDATE())
);

-- DROP TABLE #NoDefault;
CREATE TABLE #NoDefault
(
  [NoDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NULL,
  [SomeDate] DATETIME NOT NULL
);

-- make sure that data file and Tran Log file are grown, if need be, ahead of time:
INSERT INTO #HasDefault ([SomeInt])
  SELECT TOP (2000000) NULL
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;

Execute tests 1A and 1B individually, not together as that skews the timing. Run each one several times to get a sense of the average timing for each one.

Test 1A

TRUNCATE TABLE #HasDefault;
GO

PRINT '#HasDefault:';
SET STATISTICS TIME ON;
INSERT INTO #HasDefault ([SomeDate])
  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;
SET STATISTICS TIME OFF;
GO

Test 1B

TRUNCATE TABLE #NoDefault;
GO

PRINT '#NoDefault:';
SET STATISTICS TIME ON;
INSERT INTO #NoDefault ([SomeDate])
  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;
SET STATISTICS TIME OFF;
GO

Execute tests 2A and 2B individually, not together as that skews the timing. Run each one several times to get a sense of the average timing for each one.

Test 2A

TRUNCATE TABLE #HasDefault;
GO

DECLARE @Counter INT = 0,
        @StartTime DATETIME,
        @EndTime DATETIME;

BEGIN TRAN;
--SET STATISTICS TIME ON;
SET @StartTime = GETDATE();
WHILE (@Counter < 100000)
BEGIN
  INSERT INTO #HasDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
  SET @Counter = @Counter + 1;
END;
SET @EndTime = GETDATE();
--SET STATISTICS TIME OFF;
COMMIT TRAN;
PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

Test 2B

TRUNCATE TABLE #NoDefault;
GO

DECLARE @Counter INT = 0,
        @StartTime DATETIME,
        @EndTime DATETIME;

BEGIN TRAN;
--SET STATISTICS TIME ON;
SET @StartTime = GETDATE();
WHILE (@Counter < 100000)
BEGIN
  INSERT INTO #NoDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
  SET @Counter = @Counter + 1;
END;
SET @EndTime = GETDATE();
--SET STATISTICS TIME OFF;
COMMIT TRAN;
PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

You should see that there is no real difference in timing between tests 1A and 1B, or between tests 2A and 2B. So, no, there is no performance penalty to have a DEFAULT defined but not used.

Also, besides merely documenting intended behavior, you need to keep in mind that it is mostly you who cares about the DML statements being completely contained within your stored procedures. Support folks do not care. Future developers might not be aware of your desire to have all DML encapsulated within those stored procedures, or care even if they do know. And whoever maintains this DB after you are gone (either another project or job) might not care, or might not be able to prevent the use of an ORM no matter how much they protest. So, Defaults, can help in that they give folks an "out" when doing an INSERT, especially an ad-hoc INSERT done by a support rep, as that is one column they no need to include (which is why I always use defaults on audit date columns).

AND, it just occurred to me that it can be shown rather objectively whether or not a DEFAULT is checked when the associated column is present in the INSERT statement: simply provide an invalid value. The following test does just that:

-- DROP TABLE #BadDefault;
CREATE TABLE #BadDefault
(
  [BadDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NOT NULL DEFAULT (1 / 0)
);

INSERT INTO #BadDefault ([SomeInt]) VALUES (1234); -- Success!!!
SELECT * FROM #BadDefault; -- just to be sure ;-)

INSERT INTO #BadDefault ([SomeInt]) VALUES (DEFAULT); -- Error:
/*
Msg 8134, Level 16, State 1, Line xxxxx
Divide by zero error encountered.
The statement has been terminated.
*/
SELECT * FROM #BadDefault; -- just to be sure ;-)
GO

As you can see, when a column (and a value, not the keyword DEFAULT) is provided, the Default is 100% ignored. We know this because the INSERT succeeds. But if the Default is used, there is an error as it is finally being executed.

  Is there a way to avoid DEFAULT constraint within a trigger execution?

While needing to avoid Default Constraints (at least in this context) is completely unnecessary, for the sake of completeness it can be noted that it would only be possible to "avoid" a Default Constraint within an INSTEAD OF Trigger, but not within an AFTER Trigger. According to the documentation for CREATE TRIGGER:

  If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.

Of course, using an INSTEAD OF Trigger would require:

Disabling the Default Constraint
Creating an AFTER Trigger that enables the Constraint

However, I wouldn't exactly recommend doing this.


Weak entity having n instances as the 'one' I'm struggling with a question, I'm just trying to figure is it even valid , if not where does it violates the rules of a weak entity relationship, I think it's not valid because how can a weak entity have n weak entities.
so is it valid ?

You can combine B and C to one new key that is used in A and also have a key from B directly, it is no problem as you have two keys.
If you don't add something new to the key you would have the same key, but referring to different entities A and C.
Ok as long as you don't try to 3NF or higher, it is at least possible but redundant.

so C being weak can take n instances of A which is also weak and attached to the same owner?

Your relationship T is wrong. A weak relationship is always 1 to n or 1 to 1, never n:m. The essential part is that the weak entity has to know which key it relates to, see http://infolab.stanford.edu/~ullman/fcdb/slides/slides2.pdf, so if it where a 1:n , it is possible.


Country specific fields in database schema What would be the best practice to design a database schema that can have specific country fields.

e.g. a sample customer table

id
name
email
social_security_number (USA person ID)
curp (Mexico person ID)
cpf (Brazil person ID)
zip (USA postal code)
cep (Brazil postal code)
postcode (UK postal code)

But as we have more specific fields for each country the table will end up with a lot of fields.

The other approach would be to use a code instead

id
field001 > name
field002 > email
field003 > social security number for any country
field004 > postal code for any country

I understand that each approach has it's pros and cons.


70-432 : SQL Server 2008 Implementation and Maintenance Has anybody taken this certification exam?  If so, how many years of experience do you have as a SQL Server DBA and how hard did you find the exam?

You can learn more reading Guide To MCITP: SQL Server 2008 Administration, but the impression I got was that the 70-432 is an exam to test your basic skills and can be handled by even an "accidental DBA" with the right amount of studying and exploration in SQL Server 2008 Developer's Edition

  70-432 is the basic exam and covers a range of general SQL Server
  administration areas. 70-450 is more advanced and covers all the areas
  of 70-432, plus higher-level DBA tasks such as high availability and
  infrastructure design. Be aware that all questions are multiple-choice
  (more on this later).


How to get random samples from a table? I don't want repeated records between samples I have this problem, I want to get some random extracts from a table and I dont want repeated records between those extracts, in order that when I join all the different samples together, they look as the main table.

I am working with sql server 2012

thanks in advance

TABLESAMPLE can't provide the mutually exclusive nature between samples that you need even with its REPEATABLE seed.  So I would consider rolling your own, using something like NTILE.  eg assuming your table has a primary key and you want 10 samples, create a table which contains the sample Id and the primary key of the table.  Use it as a 'master' to create your samples from, then you're guaranteed they will not overlap.  Here's a simple example:

-- Create your sample master table; I'm using a temp table here
-- but you could use a permanent one
SELECT NTILE(10) OVER( ORDER BY NEWID() ) sampleId, yourPrimaryKey
INTO #tmp
FROM dbo.yourTable

-- Create the sample, eg sample 3 of 10
SELECT m.*
FROM dbo.yourTable m
WHERE EXISTS
    (
    SELECT *
    FROM #tmp t
    WHERE t.sampleId = 3
      AND m.yourPrimaryKey = t.yourPrimaryKey
    )


Problem With Loading txt File in mySql 5.6 Command Line Client I have run this to enable local infile:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile';

And indeed after that the local infile in on, but when I try loading file again, I still get notification, saying that my file does not exist.

Can someone tell me what should be done ?

If you already enabled LOAD DATA INFILE and verified its SET to ON, most likely the command you are using or the path you specify for the text file might be wrong.

The command would be mysql> LOAD DATA LOCAL INFILE '/path/filename.txt' INTO TABLE tablename;

Refer to Load Data Infile.


Using SELECT in the WHERE clause of another SELECT I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, it happens that I call something like 40 select clause (over tcpip).

I have reminiscences from SQL-Server reminding me to minimize the number of interactions between my remote application and the database. Having analyzed my selects, I do think  I could reduce this number to 3 SELECT clauses, using joins. But I don't remember the syntax for using the result of a SELECT in another SELECT.

E.g.:

SELECT * FROM individual
INNER JOIN publisher
ON individual.individual_id = publisher.individual_id
WHERE individual.individual_id = 'here I would like to use the results of a another select'

This other SELECT would be simply of the kind:

SELECT identifier FROM another_table WHERE something='something'

Here is the simplified tables layout, declined a number of times for different item_types ... (3 totally different types, hence the 3 SQL queries if optimized).

table passage
  id_passage PK
  business_field_passage bytea

table item
  id_item PK
  id_passage FK
  business_field_item text

table item_detail
  id_item_detail PK
  id_item FK
  business_field_item_detail text
  image_content bytea

There are several id_item for one id_passage.
There are several id_item_detail for one id_item.

How would you write that?
What is the name for describing the action of redirecting one select into another (if any)?

Is this what you're aiming for? Make sure the fields that are being compared are comparable (i.e. both fields are numeric, text, boolean, etc).

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId = (SELECT someID FROM table WHERE blahblahblah)

If you wish to select based on multiple values:

SELECT * FROM Individual
INNER JOIN Publisher
ON Individual.IndividualId = Publisher.IndividualId
WHERE Individual.IndividualId IN (SELECT someID FROM table WHERE blahblahblah)


ORA-01722: The specified number was invalid when truncating timestamp Query:

SELECT
    first_load_time
FROM
    v$sql;

Result:

2017-02-16/07:45:25
2017-02-17/06:47:36
2017-02-17/02:05:31
2017-02-15/17:39:13

Query:

SELECT
    to_char(FIRST_LOAD_TIME, 'YYYY-MM-DD')
FROM
    v$sql;

Result:

  ORA-01722: *Cause:    The specified number was invalid.

SELECT
    trunc(FIRST_LOAD_TIME)
FROM
    v$sql;

  ORA-01722: *Cause:    The specified number was invalid.

Why does oracle give invalid number error when truncating the timestamp? I need to do a group by with date on this view.

The FIRST_LOAD_TIME column is actually a VARCHAR2 (which is a bit strange to me) so;

select to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS') from v$sql

Will show it as a date by applying the function TO_DATE.
Simply add a TRUNC to the beginning to get the value you want;

select TRUNC(to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS')) from v$sql


Recover Oracle Database until cancel and noresetlogs I have to recover an Oracle database from a backup with a controlfile. I want to avoid that archivelogs written after the backup are taken for a recovery. I want to a avoid resetlogs.

I have to switch from rman to sqlplus to issue this statement.

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

Is there a way to do this from rman and to avoid resetlogs?

If you want to cancel, you have no way of using noresetlogs. You have create a new incarnation of your database.
Ps: I don't know what your problem was with switching to sqlplus, but you can execute sql statements (alter database.....) in rman .


Unable to mount oracle instance I am trying to shutdown the system, then I want to mount it. I have tried this:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

I have googled for solution but I can't found the exact reason that causes it to go wrong.

Exit sqlplus.

Make sure your os user is part of the DBA group (ORA_DBA in Windows).

Make sure ORACLE_SID is set to the SID of the database and unset TWO_TASK:

Windows:

set ORACLE_SID=ORCL
set TWO_TASK=

or

Unix/Linux

ORACLE_SID=ORCL
TWO_TASK=
export TWO_TASK

Now, connect as sysdba:

sqlplus / as sysdba

Now, startup the database:

SQL> startup mount;

SQL> alter database open;