SQL Server Query column from Select Can anyone please guide me what's wrong in this SQL Server query:
SELECT
    A.*,
    (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) Name
FROM A;

It seems that A.Name is inaccessible in the query.
I want to populate a column in select query with data from another table. I can't use INNER JOIN since there isn't any FK relationship between tables.
Actually I want to insert data from table A into another table C, but for insert I need to populate a NOT NULL Column in table C, that I am trying to populate with this query (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) as column.
Any help would be really appreciated.

The problem is just as the error message explains. The second argument of CONTAINS must be a literal string or a variable. It’s not the case that everywhere a string is allowed in T-SQL that you can also use a column name.
You could do something kludgy like this, but performance would likely be abominable.
CREATE FUNCTION DoesContain(@arg VARCHAR(30))
RETURNS tinyint AS BEGIN
  RETURN
    CASE WHEN EXISTS (
      SELECT * FROM B
      WHERE CONTAINS([Name],@arg)
    ) THEN 1 ELSE 0 END
END;
GO

SELECT
  A.*,
  (SELECT TOP 1 B.Name FROM B WHERE dbo.DoesContain(A.Name)=1) as [Name]
FROM A;


How can I store a pdf in PostgreSQL I have to store .pdf files in a table.

I have a table, state, with columns:

id_state,
name,
pdffile (bytea)

I want to store the pdf files in the pdffile column.

How can I do this?

First, I store PDF's in the db as bytea's.  They are easier to manage than large objects and you really dont get very much out of the streaming API (now, streaming video, that should go into lobs).  The one real problem with doing this is that it takes extra memory to unescape the data when it comes back to the database and you really can't stream it effectively.

The key thing is that you have to unescape the data when it comes back.  libpq provides functions for that as do other programming languages.  A few (like Perl) provide db drivers which automatically handle these cases.

You are on to the right structure here.  Store it as a bytea and unescape it on retrieval.


Estimating resource utilization for one database I've been tasked with migrating a database and I want to figure out how much resource in terms of CPU and IO does that database need before migrating it.  The target server to which the database is planned to be moved to is critical and I want to figure out if the target server can handle the load that the new db brings.
Things which I am considering

Use plan cache to find the IO and CPU, but I am not sure how to
translate to real CPU and IO usage on the server.
Use profiler/extended events to track activities on the DB.  NOt sure how to translate it to real CPU / IO usage

What is the normal things to do in such situation?

Cheers

"SQLServer:Databases" perfmon counter will tell you Log activity.

If you can classify your connections (sql login user_a goes to database app_a, or connections from server B goes to database app_b, etc) then you can use a resource governor classifier function to assign connections to different workload groups.  After that, you can use the "SQLServer:Workload Group Stats" perfmon counter to see CPU usage.

Glenn Berry has DMV queries for CPU USage, IO Usage and Total Buffer Usage By Database.  https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-january-2018/


use parameters in a Cte I want to use parameters in the below cte but I get the below error messages

Msg 102, Level 15, State 1, Procedure X82 7 Days/by Day, Line 5
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure X82 7 Days/by Day, Line 21
Must declare the scalar variable "@startDate".

Once I can get the perameters to work I will turn the static pivot into a dynamic one

    ALTER Procedure [dbo].[X82 7 Days/by Day]
            (
            @StartDate As datetime = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0)
            ,@EndDate As datetime = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0)
            )
    As
    Begin

    ;with cte as(

    select  [CAB Part No.]
        ,[RFT/Scrap/Rework]
        ,[Process No.]
        ,[Process]
        ,[Date & Time]
        ,[Quantity]
        ,cast([Date & Time] as date) UtcDay
    from    [SFDLOG]
    where   [Date & Time] between @startDate and @EndDate
    )
    select  [CAB Part No.]
        ,[RFT/Scrap/Rework]
        ,[Process No.]
        ,[Process]
        ,[Quantity]
        ,UtcDay
    into #TempDates
    from cte

    select *
    from
    (
    select   [Quantity]
        ,[utcday]
        ,[CAB Part No.]
        ,[Process No.]
        ,[Process]
        ,[RFT/Scrap/Rework]
    from #tempDates
     ) x
    pivot
    (
    count([Quantity])
    for [utcDay]
    In ([2015-02-01],[2015-02-02],[2015-02-03],[2015-02-04],[2015-02-05],[2015-02-06],[2015-02-07])
    )p

    Order By [CAB Part No.],[Process No.]

    drop table #tempdates

End

The first error is on Line 5, telling you that you cannot use Functions in your parameter's default values.

Pass in NULL as the default value, then sort the parameters out inside the Stored Procedure:

CREATE PROCEDURE dbo.Test
(
  @StartDate As datetime = NULL
  ,@EndDate As datetime = NULL
)
AS

BEGIN

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -8, CAST(GETDATE() AS DATE))
IF @StartDate IS NULL
  SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0);

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -1, @StartDate)
IF @EndDate IS NULL
  SET @EndDate = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0);

END

Once you have fixed this issue, and capitalise the @startDate parameter to read @StartDate, you should be fine.

For reference, there's an open Connect Item (currently 7 years old) that is Microsoft acknowledging it's on their 'ToDo' list.


How force specific table to not logging (for all DML)? We using Oracle 9i .
I'm still learn about admin Oracle (I'm DBA but other vendor).

We implement manually at our databases a trigger over LOGON and LOGOFF to save some information about our users sessions.

We execute an insert at LOGON and an update at LOGOFF over our table.
This table was created as NOLOGGING.

Our problem is the amount of archive generated by day, growth a lot.
After check with log miner I detect is the updates over this table the reason.

I research little and found the information the NOLOGGING work only for very specific situations and all UPDATEs still logging.

I don't need keep safe this table or backup it. We extract the information all days... if I loose some days I can live with it. But I can't keep this overhead .

Is there no way to force a table to not logging for all DMLs in Oracle?
Any alternative solution? (where I keep the triggers and my own table)

Regards
Cesar

If you are so concerned about the redo logging volume, you can consider writing the logon/logoff audit records to a file instead, using UTL_FILE for example.

As an aside, I'd be hard pressed to believe that updates of the audit table generate more logs than other activities in the database. This would mean that your users mostly log on to the system and then immediately log off, without doing much else.


How to set statement timeout per user? I have multiple users in Postgres. I would like to set up different statement timeouts for different users.
Eg: Guest 5 minutes and Admin 10 minutes.
Is it possible in Postgres 11.11?

Yes, Postgres allows settings per user or even per database and user, including statement_timeout:
ALTER ROLE foo SET statement_timeout = 12345;  -- milliseconds

Related:

How does the search_path influence identifier resolution and the “current schema”

To see the currently active setting for the session:
SHOW statement_timeout;

Or get more details from the view pg_settings (including how it was set):
SELECT *
FROM   pg_settings
WHERE  name = 'statement_timeout';

To check current settings for a role:
SELECT rolname, rolconfig
FROM   pg_roles
WHERE  rolname = 'foo';

rolconfig is an array, unnest it to get one setting per row:
SELECT rolname, unnest(rolconfig) AS setting
FROM   pg_roles
WHERE  rolname = 'foo';


calculate or get price of parent items on bill of materials i want to calculate or get the price of parent items on bill of materials query
here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6
please note in the second row, betax has qty = 2 and the other parent items could behave the same
qty of the first row, always will be 1
what i need is to get the 'price' and 'subtotal' for every parent

the price will surely be null if you tell it to. Initialize the base case for the CTE with 0 instead:
with recursive cte_main as
(
    select 'ALFA001'::character varying as id_father
         , 'ALFA001'::character varying as id_son
         , 'ALFA001' as tree
         , 1::double precision as qty
         , 0::double precision as price
         , 0::double precision as subtotal
         , 'ALFA001'::character varying as sort
         , 0 as levell

Second, you need to handle cases where the table contains null. You can use COALESCE for that purpose:
    union all
    select e.id_father
         , e.id_son
         , repeat('......',levell+1) || e.id_son as tree
         , e.qty
         , coalesce(e.price,0)
         , e.qty * coalesce(e.price,0) as subtotal
         , sort || ' a- ' || e.id_son::character varying as sort
         , levell+1
    from public.main as e
    inner join cte_main
        on e.id_father = cte_main.id_son
)
select * from cte_main
order by sort

Since all your attributes may contain null, you need to take that into consideration for the other attributes as well.


How to Set Recovery Mode in SQL Server Always On Availability Group without downtime? I am running a SQL Server 2016 2-node Always On Availability Group, and I want to do some backups.  I normally run this query to do my backup:

-- To permit log backups, before the full database backup, modify the database to use the full recovery model.
USE master;
ALTER DATABASE dbname
   SET RECOVERY FULL;

BACKUP DATABASE dbname
   TO URL = 'https://bloblname.blob.core.windows.net/databasebackup/backup.bak'
   WITH CREDENTIAL = 'credentialname';

My question is, can I do this part on an Availability Group Primary Node without incurring downtime?

ALTER DATABASE dbname
   SET RECOVERY FULL;

I am worried that if I run this command on the availability group primary it is going to somehow cause synchronization to stop, or perhaps have other effects that could be detrimental.

Another related question is - do you advise running these commands on the primary or the secondary?

Any help with this would be greatly appreciated.

Availability groups require databases to be in full recovery mode (see Availability Database Prerequisites and Restrictions), so there should be no need to switch it to full prior to running a backup.  The database should not be switched out of full recovery mode if it is in an availability group.

As to where to run the backup, on the primary or secondary, it is a matter of  load distribution, and currency.  If you don't want the load of the backup to run on the primary, then you'll need to run it on the secondary.  If they are in asynchronous replication, the backup may not contain all of the latest data if some of the database replication is backed up in the log send queue.


Installing OE (Order Entry) schema in Oracle Express I'd like to know if it's possible to install OE (Order Entry) schema in Oracle Express. If so, how can I do that?

SQL should be present in $ORACLE_HOME/demo/schema/order_entry.

This link gives all the info:

Oracle 11g Release 1 Database Sample Schemas

It can be downloaded from:

Sample Models and Scripts


How can a datapump export have its resource usage limited on windows? Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how can I limit the disk I/O? I am not running the operation in parallel and have already read Oracle's Datapump Performance documentation. Is there anything else I can do?

This is on Windows 2008 R2 x64.

Send it over the network

While extensive searching found no way to limit disk io natively (and serverFault will probably have more details on that) a "natural" way of limiting IO is to send the output directly across the network without ever touching the local disk. This then allows you to take advantage of all the network IO throttling resources out there. (If you want to be really sneaky, try to set up the network resource on the local machine, but that may be an overly-cute solution.)


Postgresql reset_xlog I just mistakenly moved the pg_xlog folder of a live, running postgres server. When I restarted the server shortly after, it failed to start because of a WAL problem (obviously).

For non-technical reasons, the server needed to be immediately brought back up, with literally minutes to work out a solution, I found and used pg_resetxlog - just restart the WAL archives. My reasoning was that the server was not in active use - it being a weekend, so there was nothing much that could be lost.

According to the documentation (http://www.postgresql.org/docs/9.3/static/app-pgresetxlog.html ) pg_resetxlog should really not be used, and when it is used, a DB dump should be taken and restored immediately after.

My question is, is this a concern for any transactions that occurred in the last seconds - or is it a concern that could ultimately hurt the server as a whole? Is it possible that on an inactive server (with a few routine logs written via cron the most that could have possibly been lost), there was no harm done?

The database will be in heavy use for the next day or so, can I do the dump and restore after that if it is necessary?

Don't use pg_resetxlog except as a last resort. (I think I'll submit a patch to add an --i-have-read-the-manual option to pg_resetxlog, I see it abused too often.)

You should dump the server and restore the dump.

The transactions logs are crucial for data integrity. Without them, you might have incompletely written transactions, transactions that were committed but never actually made it to the main database, transactions out of order, and more.

So it's not as simple as losing a few recent transactions. You lose the write-ordering that preserves data integrity.

Index blocks might not match the data in the heap. Visibility maps etc may be invalid. Queries could produce incorrect results, disk blocks could be split (resulting in errors on read), and more.

It's really a very good idea to dump, re-initdb, and restore. Keep a copy of the old damaged database around just in case. Even then, your database is damaged - the dump won't magically fix it if data is inconsistent and wrong, and it's possible you may have to repair the dump to get foreign keys to restore correctly, etc.


Finding rows in a table with a foreign key pointing back at itself where the referenced row differs in a certain column I can't formulate my question, so I will try to explain here and hopefully change the title later on.

I have a table

Organisations (
   id,
   platform_id,
   parent_id
)

Here is the tricky part: organisation.id represents a number for said organisation, but the same goes for the parent_id, it is basically another organisation. platform_id is either 1 or 2.

Now what I'm trying to do is to select all organisations where the parent_id is not null, but also where platform_id of the two organizations represented by organisation.id and organisation.parent_id is different.

How can I structure it, so that I can use the parent_id to search it as normal id, so that I can get its platform_id as well?

I will try to explain better and shorter perhaps:

I want to be able to filter those organisations, whose parent_id have different platform_ids, for that I would need to perhaps do a second select somehow...

sorry it got even more confusing.

This is the same table, parent_id is just another id that is linked to it, so that means that I can somehow access parent.id as normal id and get its platform_id...

In short, I want to list all IDs, that have parent_ids where the platform_id is different than the parents_id's platform_id, however I don't know how to get the 'parents_id'splatform_id`.

SELECT t1.*
FROM Organisation t1
JOIN Organisation t2 ON t1.id = t2.parent_id
                    AND t1.platform_id != t2.platform_id;


Sharding based on a range of ids? As we are building a multi-tenant app, we will be using one DB only approach, and having a column with customer_id to isolate accounts data.

The question is, is it possible in postgres to shard data based on a range of column values? (ex: customer_id > n & < n+ 100 )
thus not having all customers into one node? or its only possible to shard a specific customer id data?

It depends on what you understand with "sharding" - a fully automated, declarative way of (automatically) distributing data across different servers?

Then the answer is no, there is nothing built-in.

There are some products/extensions that do this like BDR, Postgres-XL  or Citus (which is the successor of an extension named pg_shard)

If you are just looking for some way to distribute data over multiple servers, this can be implemented using foreign data wrappers combined with inheritance. But that involves many manual steps.

A setup could look like this:

Say you have a "sales" table that you want to distribute over several countries. For each country you have your own Postgres server that only stores the country specific data. Something like this:

create table customer
(
   id integer,
   .... other columns
);

The for each shard node you need to create the same table in the Postgres server for that shard.

Then create foreign servers for each node:

create server server_one foreign data wrapper postgres_fdw options (host 'host_one', port '5432', dbname 'postgres');
create server server_two foreign data wrapper postgres_fdw options (host 'host_two', port '5432', dbname 'postgres');
create server server_three foreign data wrapper postgres_fdw options (host 'host_three', port '5432', dbname 'postgres');

Then on the master server you create one foreign table for each country as a child table of the master table.

create foreign table customer_one() INHERITS (customer) server server_one options ( table_name 'customer' );
create foreign table customer_two() INHERITS (customer) server server_two options ( table_name 'sales' );
create foreign table customer_three() INHERITS (customer) server server_three options ( table_name 'customer' );

In order to tell Postgres that each child table has a distinct set of rows, you create the necessary check constraints:

alter table customer_one add constraint check_id check (id > 0 and id <= 1000);
alter table customer_two add constraint check_id check (id > 1000 and id <= 2000);
alter table customer_three add constraint check_id check (id > 2000 and id <= 3000);

You need to make sure that the ranges in the check constraints do not overlap.

Now if you query the master table, e.g. like this:

select *
from customer
where id = 100;

you will see that Postgres retrieves that data from the first server and that the condition is pushed down to the remote server.

You will also need to write triggers to allow DML statements on the master table that would insert into the approriate "shards".


Get counts grouped by month and year I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year.

SELECT YEAR(crdate) AS y,
       MONTH(crdate) AS m,
       COUNT(*) AS tally
FROM   MESSAGE
WHERE  YEAR(crdate) = 2012
       AND SUBJECT <> 'Welcome' --exclude default messages
GROUP BY
       YEAR(crdate),
       MONTH(crdate)

This is what I have but its limiting in that I need to manually change years. Can this be upgraded to output for all dates in the table in this month/year format?

Removing the year would clump all the months over may years together and distort the data. I am looking to get the counts from that particular month/year.

Wrap this up in a stored procedure then:

CREATE PROCEDURE GetYearCounts
    @year INT
AS
    SELECT YEAR(crdate) AS y,
           MONTH(crdate) AS m,
           COUNT(*) AS tally
    FROM   MESSAGE
    WHERE  YEAR(crdate) = @year
           AND SUBJECT <> 'Welcome' --exclude default messages
    GROUP BY
           YEAR(crdate),
           MONTH(crdate);
GO

This will allow you to specify the year you want to get the data from in the call.  I.e.

EXEC GetYearCounts @year = 2012;
GO

  This is what I have but its limiting in that I need to manually change years.

Given your requirements there is no way to do this automatically.


Can atomic counter in trigger lead to "could not serialize access due to concurrent update"? I have the following code:

create table users (id serial primary key, comments_counter integer default 0);
create table comments (id serial primary key, user_id integer references users(id) not null)

create or replace function users_comments_counter()
returns trigger as $$
begin
  update users set comments_counter = comments_counter + 1
  where id = NEW.user_id;
  return null;
end;
$$ language plpgsql;

create trigger users_comments_counter_trg
after insert on comments
for each row execute procedure users_comments_counter();

Is it possible that inserting a row to the comments table will throw could not serialize access due to concurrent update?

Or maybe in other words: is it possible to use such counter (with ANY of transaction isolation levels) while avoiding situation where "retry" (due transaction serialization error) is needed, and data consistency is guaranteed (counter will behave as expected - will be incremented only if comment row will be successfuly inserted).

The actions taken in the trigger are part of the transaction that fires it.  This means that the counter increment will happen only if the whole transaction (or the subtransaction defined by a SAVEPOINT) succeeds.  This way we can say that the above trigger will increment the counter atomically, from the point of view of the INSERT INTO comments statement.

If this is all you have inside the transaction (INSERT and the increment), you are safe from concurrency issues, and the default isolation level (READ COMMITTED) is enough.

Furthermore, depending on how the INSERT is issued, you might even spare the trigger, which would save you some overhead.  In order to do this, the following conditions must be met:

you can wrap the INSERT INTO comments and UPDATE users statements into a transaction
there is no other way adding a comment than the transaction in the previous point (i. e. no manual editing of the data by an administrator or similar)


SQLite database replication on the same server We have an application written in C and we are looking for a way to do transparent replication of SQLite database on one server (but on different disks). I read lots of guides about replication across multiple servers and most of what I've found is about RDBMSs such as MySQL database.

This post is the only one I've found about replication specifically on SQLite, where some solutions were suggested. For example, someone suggested using Litereplica which seems to be exactly what I am looking for, but after spending about 2,3 days, it didn't work (or maybe I couldn't use it properly). Another solution was Rqlite, but that is not on the same server.

I am not a SQLite expert and I just started using it, and I really stuck on this. Is there any other solution to replicate the SQLite database on the same server? For example, what we need is e.g., what we write in the main db, will be written in the replicated db/dbs as well. Also, for read, if the main db did not work, it should read from the slave/backup db...

UPDATE_1

I used the ATTACH to copy all the data from the main db to the backup db. Here is what I've done in SQLite command prompt:

ATTACH '/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1_backup.db' AS backup;  //This attached a new empty db to the main db

CREATE TABLE backup.fuel_transaction_1 AS SELECT * FROM main.fuel_transaction_1;

Now, Is there any way to sync this two databases? so that e.g., if I delete a row from a table in main db, it deletes the same row from a table in backup db?

Searching for the same topic, I came across a mention in Using SQLite: Small. Fast. Reliable. Choose Any Three.:

  Replication

  SQLite has no internal support for database replication or
  redundancy. Simple replication can be achieved by copying the database
  file, but this must be done when nothing is attempting to modify the
  database. Replication systems can be built on top of the basic
  database API, but such systems tend to be somewhat fragile. Overall,
  if you're looking for real-time replication — especially at a
  transaction-safe level — you'll need to look at a more complex RDBMS
  platform


SQL Server, remove all users' access right to a table object As a clean up job, I am trying to remove all users' (including non public users) access right to a single table object (apart from superusers) and will subsequently grant new access rights. Is there a simple way to remove access rights to a table without using a cursor with system tables?

This code should work below:

declare
    @user_name sysname,
    @deny_cmd nvarchar(500)

declare UserCursor cursor for
select name
from sys.database_principals
where type in ('U', 'S')
and name not in ('dbo', 'information_schema', 'sys')

open UserCursor

fetch next from UserCursor
into @user_name

while @@fetch_status = 0
begin
    set @deny_cmd =
        'deny select, insert, update, delete on object::UberSecureTable
        to ' + @user_name

    exec (@deny_cmd)

    fetch next from UserCursor
    into @user_name
end

close UserCursor
deallocate UserCursor

Let me know if that does what you're looking for.  Change UberSecureTable to your actual table name.


Spliting tables with *almost* same values I have different kind of documents which have common fields (not all of them, but most) and I thought to put them in the same table and use ENUM to differentiate them.
Then I realized that if have thousands of docs of every kind I will have a (very?) slow SELECT/UPDATE's right?

So my question is: should I split the tables and have one table for each document? or is the initial approach the way to go?
Thank you

Certainly not one table for each document.  Perhaps one for each document type.

In general you are correct - reading or writing to larger tables typically takes longer than the same operation on smaller tables.  There are techniques to help with this - indexing for example.  Modern DBMSs can easily handle tables of several million rows, however, so unless you are pushing these limits it is better for your design to match your requirements than to work around the limitations of the technology.

If you commonly update several different documents of different types simultaneously then a single table would be beneficial.  If each document type is processed independently then separate tables would be better.


How to move a Daily Partition to a Monthly Partitioned Table I would like to switch out a partition from a daily partitioned table and merge it into monthly partitioned table.  I am unsure as to how to go about it.  Here's what I have so far.

I have two tables, Event_Daily and Event_Archive_Monthly.  Both tables are partitioned on CreatedDate datetime with Event_Daily being partitioned by Day and Event_Archive_Monthly being partitioned by Month.

Event_Archive_Monthly has the following file groups:

Archive_Monthly_2018_05_01_FG
Archive_Monthly_2018_06_01_FG
Archive_Monthly_2018_07_01_FG

Event_Daily has the following file groups:

Daily_L_NULL_FG
Daily_2018_06_18_FG
Daily_2018_06_19_FG

I have 9 records in Event_Daily in the 2018_06_18 file group and no data on the other partitions.  I have created a SwitchOut table on Daily_2018_06_18_FG  and have issued these commands to switch out the data and merge the boundary point:

PRINT 'switching out previous days data'

ALTER TABLE [dbo].[Event_Daily]
SWITCH PARTITION 2 TO dbo.Event_Daily_SwitchOut;
GO

PRINT 'merging boundary point'

ALTER PARTITION FUNCTION Daily_PF ()
    MERGE RANGE ( '2018-06-18' )
GO

At this point I am unsure as to how to get this data into the archive table.  Ideally, after everything is said and done the file group Daily_2018_06_18_FG is gone.  One option I know I have is to just insert the data and then drop the SwitchOut table and drop the file group.

Is there a way, using the Partitioned Table, to merge the SwitchOut table that is on the filegroup Daily_2018_06_18_FG into the partition on Archive_Monthly_2018_06_01_FG?

Community wiki answer:

  Is there a way, using the Partitioned Table, to merge the SwitchOut table that is on the filegroup Daily_2018_06_18_FG into the partition on Archive_Monthly_2018_06_01_FG?

No, you'll need to use INSERT...SELECT for row processing. MERGE/SPLIT/SWITCH DDL are partition level.


PgAdmin Autogenerate random number Hey guys right now I'm passing one value to my database, it's a random and unique 10 digit number.

Is there a way i could generate this number directly in my database each time a new row is created?

I'm Using PostgreSQL (pgadmin)

It looks something like this

id_Primarykey  Name  random number
1              x     1231233592
2              Y     5234234837
3              Z     3453450394

Said number has to be unique, 10 digits, and random (non sequential)

Getting unique random looking numbers in a given interval

If you can install C code into your database, the permuteseq extension provides a ready-to-use solution to this problem. It encrypts consecutive numbers with format-preserving encryption techniques to get both the non-guessability, the unicity (it's collision-free), and the confinment in a given output range.

To auto-assign the values on insertion, the simplest way  is to set the column's DEFAULT clause.

Example

CREATE EXTENSION permuteseq;

CREATE SEQUENCE seqname MAXVALUE 9999999999; -- 10 digits

CREATE TABLE example(
 id serial,
 col text,
 random_number bigint default
     permute_nextval('seqname'::regclass, 8784354463) -- or any random number
);

INSERT INTO example(id,col) VALUES(1, 'foo');
INSERT INTO example(id,col) VALUES(2, 'bar');
INSERT INTO example(id,col) VALUES(3, 'baz');

SELECT * FROM example;

 id | col | random_number
----+-----+---------------
  1 | foo |    9737615019
  2 | bar |    7519592769
  3 | baz |    8112392939


Master-master database replication on 5 servers My current project requires immediate replication between 5 databases. Servers are physically distributed across the globe. Currently we use Redis with one master server installed on another 6th server. All database writes on any of 5 servers are performed to this 6th master server, and other 5 are slaves of it. This approach has a lot of flaws and i'm looking for solution to replace it. Any suggestions please?

I'm just throwing this out there but have you thought of using a MQ for writes?

We're using RabbitMQ for writes and federating it out to multiple data centers where each one has a flexihash cluster of ~20 Redis servers. So far it's working well for us and you can increase or decrease the size of each cluster independently as long as your rebalancing them appropriately. Hope this helps.


Postgresql partitioned table timestamptz constraint problem The table reports is partitioned table by day like reports_20170414,reports_20170415

Constraint SQL is defined as follow

CHECK (
    rpt_datetime >= '2017-04-14 00:00:00+00'::timestamp with time zone
    AND
    rpt_datetime < '2017-04-15 00:00:00+00'::timestamp with time zone
)

Lets consider two type of query

SELECT SUM(rpt_unique_clicks)
    FROM reports WHERE rpt_datetime >= '2017-04-14 00:00:00';

Above query runs within sub-seconds and everything is fine.

SELECT SUM(rpt_unique_clicks)
    FROM reports WHERE rpt_datetime >=
 date_trunc('day', current_timestamp);

On contrary, above query runs at least 15 seconds.

SELECT date_trunc('day', CURRENT_TIMESTAMP), '2017-04-14 00:00:00';

returns

2017-04-14 00:00:00 +00:00 | 2017-04-14 00:00:00

When I examine why the latter runs long (using explain analyze), I finished the result that it visits and scans every table (~500), but the former visits only reports_20170414 so there is problem with constraint checks.

I want to query for today, without using prepared statements as the latter query does. Why date_trunc('day', CURRENT_TIMESTAMP) is not equivalent to  2017-04-14 00:00:00 ?

I cannot fully respond as to why date_trunc('day', CURRENT_TIMESTAMP) is not equivalent to a constant... even if both CURRENT_TIMESTAMP and date_trunc are defined as IMMUTABLE.

But I think we can make an experimental educated guess: Apparently, the PostgreSQL planner does not evaluate functions. As such, it doesn't have any good way to know which partitions to check, and makes a plan that checks them all.

Experimental check

We create a base (parent) table:

 -- Base table
 CREATE TABLE reports
 (
     rpt_datetime timestamp without time zone DEFAULT now() PRIMARY KEY,
     rpt_unique_clicks integer NOT NULL DEFAULT 1,
     something_else text
 ) ;

We create an auto-partition insert trigger:

 -- Auto-partition using trigger
 -- Adapted from http://blog.l1x.me/post/2016/02/16/creating-partitions-automatically-in-postgresql.html
 CREATE OR REPLACE FUNCTION create_partition_and_insert ()
 RETURNS TRIGGER AS
 $$
 DECLARE
     _partition_date text ;
     _partition_date_p1 text ;
     _partition text ;
 BEGIN
     _partition_date := to_char(new.rpt_datetime, 'YYYYMMDD');
     _partition := 'reports_' || _partition_date ;

     -- Check if table exists...
     -- (oversimplistic: doesn't take schemas into account... doesn't check for possible race conditions)
     if not exists (SELECT relname FROM pg_class WHERE relname=_partition) THEN
        _partition_date_p1 := to_char(new.rpt_datetime + interval '1 day', 'YYYYMMDD');

        RAISE NOTICE 'Creating %', _partition ;

        EXECUTE 'CREATE TABLE ' || _partition ||
              ' (CHECK (rpt_datetime >= timestamp ''' || _partition_date || ''' AND rpt_datetime < timestamp ''' || _partition_date_p1 || '''))' ||
              ' INHERITS (reports)' ;
     end if ;

     EXECUTE 'INSERT INTO ' || _partition || ' SELECT(reports ' || quote_literal(NEW) || ').* ;' ;

     -- We won't insert anything on parent table
     RETURN NULL ;
 END
 $$
 LANGUAGE plpgsql VOLATILE
 COST 1000;

 -- Attach trigger to parent table
 CREATE TRIGGER reports_insert_trigger
 BEFORE INSERT ON reports
 FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert();

Fill the (partitioned) table with some data; and check the partitions the trigger made:

 INSERT INTO
     reports (rpt_datetime, rpt_unique_clicks, something_else)
 SELECT
     d, 1, 'Hello'
 FROM
     generate_series(timestamp '20170416' - interval '7 days', timestamp '20170416', interval '10 minutes') x(d) ;

 -- Check how many partitions we made
 SELECT
     table_name
 FROM
      information_schema.tables
 WHERE
     table_name like 'reports_%'
 ORDER BY
     table_name;

 | table_name       |
 | :--------------- |
 | reports_20170409 |
 | reports_20170410 |
 | reports_20170411 |
 | reports_20170412 |
 | reports_20170413 |
 | reports_20170414 |
 | reports_20170415 |
 | reports_20170416 |

At this point, we check two different queries. The first one does use a constant compared to rpt_datetime:

 EXPLAIN (ANALYZE)
 SELECT
     SUM(rpt_unique_clicks)
 FROM
     reports
 WHERE
     rpt_datetime >= timestamp '20170416' ;

Using a constant timestamp, only 'reports' and the appropriate partition are checked:

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------- |
 | Aggregate  (cost=25.07..25.08 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)                                 |
 |   ->  Append  (cost=0.00..24.12 rows=378 width=4) (actual time=0.009..0.010 rows=1 loops=1)                             |
 |         ->  Seq Scan on reports  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)             |
 |               Filter: (rpt_datetime >= '2017-04-16 00:00:00'::timestamp without time zone)                              |
 |         ->  Seq Scan on reports_20170416  (cost=0.00..24.12 rows=377 width=4) (actual time=0.006..0.007 rows=1 loops=1) |
 |               Filter: (rpt_datetime >= '2017-04-16 00:00:00'::timestamp without time zone)                              |
 | Planning time: 0.713 ms                                                                                |
 | Execution time: 0.040 ms                                                                                |

If we use the equivalent SELECT using a function-call (even if the result of this function call is a constant), the plan is completely different:

 EXPLAIN (ANALYZE)
 SELECT
     SUM(rpt_unique_clicks)
 FROM
     reports
 WHERE
     rpt_datetime >= date_trunc('day', now()) ;

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------- |
 | Aggregate  (cost=245.74..245.75 rows=1 width=8) (actual time=0.842..0.843 rows=1 loops=1)                               |
 |   ->  Append  (cost=0.00..238.20 rows=3017 width=4) (actual time=0.837..0.838 rows=1 loops=1)                           |
 |         ->  Seq Scan on reports  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)             |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |         ->  Seq Scan on reports_20170409  (cost=0.00..29.78 rows=377 width=4) (actual time=0.214..0.214 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170410  (cost=0.00..29.78 rows=377 width=4) (actual time=0.097..0.097 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170411  (cost=0.00..29.78 rows=377 width=4) (actual time=0.095..0.095 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170412  (cost=0.00..29.78 rows=377 width=4) (actual time=0.096..0.096 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170413  (cost=0.00..29.78 rows=377 width=4) (actual time=0.131..0.131 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170414  (cost=0.00..29.78 rows=377 width=4) (actual time=0.098..0.098 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170415  (cost=0.00..29.78 rows=377 width=4) (actual time=0.095..0.095 rows=0 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 |               Rows Removed by Filter: 144                                                                               |
 |         ->  Seq Scan on reports_20170416  (cost=0.00..29.78 rows=377 width=4) (actual time=0.004..0.005 rows=1 loops=1) |
 |               Filter: (rpt_datetime >= date_trunc('day'::text, now()))                                                  |
 | Planning time: 0.298 ms                                                                                |
 | Execution time: 0.892 ms                                                                                |

dbfiddle here


Configure AlwaysOn backups I'm new to AlwaysOn Availability groups and have set it up in my DEV environment.  I currently have it set to do the backups on my primary because we are only using the secondary for read-only reporting purposes. The backups are running but how do I control how often? Currently it does a FULL every night, a DIFF every night at midnight and log backups every hour.  I don't see in the AVG setup where to specify how often...I currently do a FULL at 10pm, DIFF every 4 hours and LOG every 15 minutes.  I also currently use Ola's backup scripts and I'm confused about how to continue to use his scripts when I setup AVG in my production.  I've read that there is a parameter AVAILABILITY_GROUP_DATABASES for Ola's scripts.  Do I use this to be able to control all the backups with Ola's scripts? And then the AVG will not back up the databases...instead they will be controlled by Ola's scripts?

I've tried testing this and no matter what I do, the AVG always does the backups and Ola's scripts are ignored.

I am running Availability Groups and using Ola's backup solution, and here is how I have it set up:

First, Availability Groups do not do their own backups, something has to be scheduled to run a backup.  So make sure there is not a Maintenance Plan already doing backups on the SQL Server
Make sure Ola's scripts are installed and up to date on all of your Availability Group servers. :)
Create a SQL Agent job for your FULL backups that calls Ola's dbo.DatabaseBackup procedure; schedule this to run however often you need.
Repeat step 3 for your DIFF and LOG backups.
Copy the backup jobs you just created to all of your secondary Availability Group servers.

Here's what you need to know about Ola's scripts:

They are what I like to refer to as "Availability Group Aware", meaning that you can run the same backup jobs on both your Primary and Secondary AG servers, and they will act accordingly. (ex: If you run a FULL backup on a Secondary, Ola's script will recognize it is on a secondary and exit gracefully without doing anything)
If you specify ALL_AVAILABILITY_GROUPS as your backup database, it will backup only the databases in ALL of the availability groups on that server. If you specify USER_DATABASES, it will back up ALL user databases on the server (including the ones in the AG).


Mysql Right join with mutiple condtion not working i have two table table1 and table2 and when right joining the table2 with table1 and i am not getting the row. here i have attached the fiddle link also

CREATE TABLE `table1` (
 `orderid` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`orderid`)
);
CREATE TABLE `table2` ( `fields_id` INT NOT NULL AUTO_INCREMENT , `table1_order_id` INT(11) NOT NULL , `field_value` VARCHAR(100) NOT NULL , `fname` VARCHAR(100) NOT NULL , PRIMARY KEY (`fields_id`)) ENGINE = InnoDB;

INSERT INTO `table1` (`orderid`) VALUES (1), (2), (3), (4), (5), (6);
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '1', 'karthi', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '2', 'karthi', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '3', 'selva', 'name');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '1', 'salem', 'city');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '2', 'chennai', 'city');
INSERT INTO `table2` (`fields_id`, `table1_order_id`, `field_value`, `fname`) VALUES (NULL, '3', 'mumbai', 'city');

select table1.*
from table1
right JOIN table2 ON (
        table2.table1_order_id = table1.orderid
        AND
            ( table2.field_value LIKE '%karthi%'
            AND table2.fname = 'name' )
        AND (
            table2.field_value LIKE '%salem%'
            AND table2.fname = 'city'
        )

    )

    where 1 group by table1.orderid

Above i wrote select query but it returns null result, but i am expecting output like below,

orderid 1

table2.fname cannot be both 'city' and 'name' in the same row.

To get both of this EAV model we use two joins on the table2 with a different alias.

Its unclear why you want a RIGHT JOIN. Normal [inner] JOIN is fine.

I've moved the selection to a WHERE criteria just for ease of readability. Because its inner it could be put in the ON criteria.

While you can group by table1.orderid when you do this you cannot SELECT table1.* because by grouping their is only one row per orderid and therefore multiple orders matching the same critera cannot return on the same line (without an aggregate function like GROUP_CONCAT).

select table1.*
from table1
inner JOIN table2 t2name ON
        t2name.table1_order_id = table1.orderid
inner JOIN table2 t2city ON
        t2city.table1_order_id = table1.orderid

where
    t2name.fname = 'name'
    AND t2name.field_value LIKE '%karthi%'
    AND t2city.fname = 'city'
    AND t2city.field_value LIKE '%salem%'

or

select table1.orderid
from table1
inner JOIN table2 t2name ON
        t2name.table1_order_id = table1.orderid
inner JOIN table2 t2city ON
        t2city.table1_order_id = table1.orderid
where
    t2name.fname = 'name'
    AND t2name.field_value LIKE '%karthi%'
    AND t2city.fname = 'city'
    AND t2city.field_value LIKE '%salem%'
group by table1.orderid


Can I use a script to restore multiple SQL databases from .bak files? I'm migrating 100 plus SQL databases over to a new server in a new location. I have the maintenance plan in place to take full back ups and then a script to copy over the .bak files to a temp directory. What I'm looking for is a way to mass restore those .bak files so that I don't have to spend all weekend doing it manually.

I found a decent stored procedure here : SQL Restore

I put it in place and tested and I'm getting the following error:

  Msg 3118, Level 16, State 1, Line 1
  The database "TF058" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

I'm running SQL Server 2012 and the databases are in FULL recovery mode.

You can use

sqlmigration - Start-SqlServerMigration.ps1

This script provides the ability to migrate databases using detach/copy/attach or backup/restore. SQL Server logins, including passwords, SID and database/server roles can also be migrated. In addition, job server objects can be migrated and server configuration settings can be exported or migrated. This script works with named instances, clusters and SQL Express.

Restore Genie - it has both PowerShell and t-SQL version.

I have used both and they are life savers. Highly recommend to test them first. First one is the one that will help in your situation.


Why does server name need to be specified when accessing local instances? Even though I have direct access to the physical database machine, I have to specify dbmachine\sql in the connection field, rather than just the instance name. Why is that?

Quite simply, client tools need to know explicitly which machine you're trying to connect to. This is like trying to access http://localhost/test_site by typing test_site into your browser.

For SQL Server you can indicate the local machine in a variety of ways:

machine_name\instance
(local)\instance
127.0.0.1\instance
.\instance


Text Storage and Database Design Optimization in a SQL Server Database Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to structure this database? Also considering the number of rows (r) the database may grow to in size.

Example: given a table with three columns, one being an auto incrementing integer ID column, another being a Fruit column where acceptable fruits are 'Apple','Banana','Orange', and 'Strawberry', along with the final column being Color with 'Red', 'Orange', 'Green', and 'Yellow' as acceptable values. Let us assume the Fruit and Color column have a data type of varchar(50). Which of the following would be a good practice and suitable database design?

Should each value be directly represented in the table as follows given the previous example. Would it be fair to say that after a large number of rows querying such a table would be very inefficient due to the large storage and computation requirements required on the text?

+-----+--------+--------+
| ID  | Fruit  | Color  |
+-----+--------+--------+
| 1   | Banana | Yellow |
| 2   | Apple  | Green  |
| ... | ...    | ...    |
| 500 | Orange | Orange |
+-----+--------+--------+

OR
For each column that has one of multiple values applicable to it, should that be represented in another table with integer values, and then joined together with an INNER JOIN? Followed by an integer representation in the main table? Does this depend on the length of the text values being stored in that field? For the given example, we would then have three tables.

+----+------------+
| ID | FruitName  |
+----+------------+
|  1 | Apple      |
|  2 | Banana     |
|  3 | Orange     |
|  4 | Strawberry |
+----+------------+
+----+------------+
| ID | FruitColor |
+----+------------+
|  1 | Red        |
|  2 | Green      |
|  3 | Orange     |
|  4 | Yellow     |
+----+------------+

Then followed by:

+-----+-------+-------+
| ID  | Fruit | Color |
+-----+-------+-------+
| 1   | 2     | 4     |
| 2   | 1     | 2     |
| ... | ...   | ...   |
| 500 | 3     | 3     |
+-----+-------+-------+

Is there any mathematical 'rule-of-thumb' when designing a database considering the number of rows and the type of data to be stored in each column? Or should the second schema be followed in general.

If all apples are green and all bananas are yellow a Fruit table (ID int, Name varchar(50), colour varchar(50)) would be appropriate, with your data table having a foreign key to it.

If you have yellow apples and orange bananas (yea for genetic engineering!), but only certain combinations are permitted you will need FruitRainbow(ID int, FruitID, ColourID) with the latter two as FKs to your option 2 tables above and your data table having a FK to FruitRainbow.

If any fruit can occur in any colour, and you don't want to limit those combinations in advance, your option 2 is fine.

If your query is really about resource optimisation rather than relational integrity then you'll have to decide what you want to gain and what you're willing to trade to get it.  By using integer FKs instead of natural name you get a smaller disk footprint at the cost of runtime load.  There are no free lunches. Pick your problem and solve it in the knowledge of the compromises it will entail.


Is it OK to have an entity in an ER diagram without a relationship? I'm creating an ER diagram for a class assignment and I was wondering if it would be possible to create an entity that had no direct relationship to any other entity, and simply use foreign keys to connect them. The diagram below illustrates what I'd like to do

Basically, my diagram is starting to get really complex and it would be great if I didn't have to maintain a connection between every single entity.

FWIW, I'm using Chen and Crow's Foot notation, if that's any help.

An ER diagram is merely a tool to aid you in development. An ER diagram doesn't even by mandate require you to show all the entities in the system.

So to answer your question by one route: It's perfectly acceptable to have an entity on a diagram that has no relationship to any other entity. Consider a settings table that is used by a simple application to store the settings for the program. It would not need any other entities to do its job. But it would still be an object which existed within the scope of the datastore.

So to answer your question by another route: You can display as many or as few diagrams on your ER diagram as are required to show the information you're trying to represent. But if you choose to omit a relationship for purposes of clarity, it would still make sense to make a note that the R is omitted. I would personally but a -{#} with a number inside the related circles for each entity, with a footnote for that # at the bottom of the page. That way other readers won't have to ask if there should be another relation.

That's my $0.05 worth on this one anyways. The point here is that this is partly a matter of style. Some people will tell you that you always have to put the relationships (like, for instance, your boss might require that, and that would trump my advice). But the final truth is what's in the database.


MDX - Sum values greater than then a median calculated measure I'm relatively new to SSAS / MDX. I have a cube with a calculated field which calculates the Median value for a range of prices.

MEDIAN( [DimItems].[Dim Items Id].CurrentMember.Children, [Measures].[Price] )

Included as a measure is a quantity field. I need to calculate the sum of that quantity field where the raw price is greater than, equal to, and less than the calculated Median price.

Take the following data:

Qty Price
183 $100
51  $114
420 $123
80  $137
12  $144
200 $147
51  $152

The Median price is $137. Therefore, to get the sum of the quantity where the Price is less than the Median, I need to sum up the following Qty values (183,51,420) since $100, $114, and $123 are all less than $137. The value of this summation is 654.

The problem is I have no idea how to do this. I cannot figure out how to compare a raw value (eg $114) with a Median value over a range (eg $137). Any help would be appreciated.

You need first to define a measure that will compte the median price across the siblings:

[Measures].[Siblings_median_price]
AS
MEDIAN([DimItems].[Dim Items Id].CurrentMember.Siblings, [Measures].[Price])

The following measure should return what you want:

[Measures].[Sum_below_median]
AS
SUM(
     [DimItems].[Dim Items Id].CurrentMember.Children
   , IIF([Measures].[Price] < [Measures].[Siblings_median_price]
   , [Measures].[Quantity], Null)
)


SQL table for Address and Localization I need to save a list of Places and Persons in a Database including their Addresses and Latitude / Longitude. Sometimes I have an address other times only Latitude / Longitude other times none. So I have:

create table dbo.Persons (
  Id int identity not null primary key clustered (Id),
  AddressId int not null,
  Name nvarchar (100) null,
  Localization geography null
)

create table dbo.Places (
  Id int identity not null primary key clustered (Id),
  AddressId int not null,
  Name nvarchar (100) null,
  Localization geography null
)

create table dbo.Addresses (
  Id int identity not null primary key clustered (Id),
  CityId int not null,
  Street nvarchar (100) null,
  PostalCode nvarchar (100) null,
)

create table dbo.Cities (
  Id int identity not null primary key clustered (Id),
  Name nvarchar (100) null
)

I have two questions:

Should I move the Localization from Places and Persons to Address?
I am doing this way because I can have an address and not a Localization or vice versa.
Is there a way to improve my database scheme?

I'd move localization to Addresses. You'd probably like to know where an address is located anyway.
Some people have many addresses (a fiscal address,
shipping address, multiple houses) And some addresses belong to many people (families, companies...) Consider creating a many to many table that relates Persons and Addresses.


views restored from backup in a different server referring to linked servers I have 3 SQL servers X, Y, Z and 2 databases X.A and Y.B.
There are (many) view in X.A that uses 4-level naming in their definition:

SELECT ... FROM Y.B.SCHEMA.TABLE

From this I infer that X contains a linked server definition for Y.
I am restoring X.A and Y.Bfrom backup files into server Z, so I now have
Z.A and Z.B available in the same server.
But these views obviously fail in Z due to not knowing what Y is.

I would like these views to work in the restored database Z.A, in such a way that they get the data from Z.B (and not from Y.B), without having to change the views (there are many such views). I am not sure if this is possible. I am able to do changes on server Z as needed, but I do not have any access to X and Y. The users of the view also only have access to Z.

I thought about defining a synonym Y in Z to point to localhost but it seems I need to refer at least to a schema or schema.table when creating a new synonym. Or can I have a synonym of just the server name?

I also thought about defining a linked server named Y in Z, but pointing to itself. Here I was only able to make a linked server named localhost pointing to itself, and this does not help solve my problem.

Is this at all possible? Can I convince SQL server to use Z when Y is referred to in the view definition?

Synonyms will not be useful for your need since that will require changing the view definition to specify 2-part names.

  I also thought about defining a linked server named Y in Z, but
  pointing to itself. Here I was only able to make a linked server named
  localhost pointing to itself, and this does not help solve my problem.

It is possible to create a loopback linked server with the name Y using SSMS by selecting "Other data source" (instead of SQL Server) and then specifying a SQL Server provider. This can also be done via T-SQL:

EXEC master.dbo.sp_addlinkedserver
      @server = N'Y'
    , @srvproduct=N''
    , @provider=N'SQLNCLI11'
    , @datasrc=N'.'
    , @catalog=N'YourDatabase';

You'll need to also configure the linked server security context according to your requirements.


Are there any special characters that are never allowed in SQL? I am a Java programmer. We always make database-related things configurable so that they can be easily modified by the DB team as per their requirements.

I want to make some queries as configurable, the problem is I am not able to find a separator for separating queries.

I will later use this separator in the program to separately execute these queries.

Can anyone tell me any separator or a special symbol that is never used in SQL?

Note: As of now I am using comma (,) as the separator but this has a high possibility that my program may fail in future if any query contains comma (,) in it.

The statement terminator in SQL Server is a semi-colon ;.

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

As queries cannot contain a semi-colon, except as the last character to identify the end of the query, this should be enough for you to separate out each statement.


Would db corruption be further minimized if I remove querying on a backend table from the user's front end? I have an MS Access database that was being used by 30 people. They all use it at different times of the day, but within any 8 hour block of time there might be 10 people using it. This database was not being split between front end and back end. The main process was that a user filled out a form and then VBA code would write this information to a table.

I now want to make a new version of this database where the front end is split from the back end. This is because there were numerous times where the whole database became corrupted and all our data, queries, and forms were lost.

I noticed that on one of the forms I have a control that is querying the table that is being moved to the backend. I'm not sure if having each user's local copy of the front end query the backend table just for this one control is worth it. I want to minimize db corruption, so should I just remove any sort of querying on the backend table for small stuff and only allow users one major write to the backend table? Or does this not matter?

If by 'querying' you mean the control is simply running a select statement against the data table, it is doubtful that would corrupt any data.  If your control's query is manipulating data (add, delete, change) then it could very well be corrupting the data.

Thirty users on an Access DB is challenging.  You may want to look into using a more powerful DB for the backend.  If you don't, be sure to repair and compact your Access DB nightly.


Using SSIS to extract query data and present it through a cube The problem: I have a client who is running their application on SQL Server. They use SQL Server Analysis Services for additional reporting based on the cube that's been provided. It turns out, there is one query they are looking to run that is written in SQL and is fairly complex. The cube doesn't give them the data that they are looking for.

The workaround: As the client, they don't have access to directly query the database. Hence, they send me this query, and every week, I send them back the results on a spreadsheet.

Proposed solution: I believe I would be able to use SQL Server Integration Services to work around this whereby, I can automate the creation of a table with the results of said query which they would be able to query using their existing access to Analysis Services. Am I correct in saying this?

If so, I believe I need to create an SSIS package with certain control and data flows. What is the high level structure of what needs to be built?

P.S. In looking so far, I see a control flow task called "Execute SQL Task". I imagine I would have to build on this.

tl;dr;

SSIS moves data. SSAS creates cubes. SSRS displays data.

Given the following problem statements

The client cannot directly query the database.
The client can only query the cube.

Therefore, you're going to need to modify the existing SSAS cube solution to expose the missing data for them to consume. Creating a new table in the database isn't going to directly help as you've specified they cannot query the database.

It might help in that you can materialize the existing query into a table. Whether that's correct approach is beyond the scope of a question here. You would then use SSIS to pop the data into the table(s) needed for reporting.

Since we're talking cubes, you might not have much to do or you have plenty. The general idea being you're going to define a central table that contains the measurable things at the lowest possible grain required. If they need to report a store's daily sales, then you're probably looking at a table that contains a sales amount and then two foreign keys: store and day. This table will be a fact or a measure table.

You have an existing query that helps identify what they want to filter or slice the data by - those are your dimensions. In the previous example, we'd have a date dimension and a store dimension. Dimensional modeling can get complex pretty quick but your saving grace is there is already an existing cube out there. Your predecessor has likely created "conformed dimensions" which means you don't need to create your own store or date dimension, you simply need to ensure you are correctly tying this new fact table into the existing dimensions.

You might even be able to fake out the dimension table by creating a view. In fact, it's usually a good idea to drop a view over your fact tables before presenting it to SSAS as it abstracts away the physical implementation from the logical.


How to select multiple id from same table Here is the simplified version of my problem. I have these tables:

Master table

id - name - surname - fathers name - mothers name

Names

id - name

Surnames

id - surname

I want to query master table but I want to get id's for name, father's name and mother's name from other tables. I want something like;

SELECT
    'id from names table that matches record name'
    , 'id from surnames table that matches record surname'
    , 'id from names table that matches record fathers name'
    , 'id from names table that matches record mothers name'
FROM master_table

My problem is that I don't know how to join same table multiple times.

You can "join the same table multiple times" by aliasing the table in the FROM statement e.g. SELECT xyz FROM names AS n1 (where n1 is the alias for the table [names]). You can refer to the same table as many times as you want by using a different alias for the same table and it will be treated as a 'different' set of data.

You will then need to join between the various aliases to get the conditions you want such as "surnames that matches record surname".

As stated by a commenter above, the structure isn't very clear but you would end up with something like:

SELECT name, s1.surname, s2.surname from names AS n1
JOIN surnames AS s1 ON n1.mothersname = s1.surname
JOIN surnames AS s2 ON n1.fathersname = s2.surname

I'm sure the join isn't exactly this as you seem to have "intermediate" tables for name and surname (and that may be a design issue in itself) but the above should illustrate how to use the same table multiple times in a query.


Can I speed up this slow query which should be limited by MIN/MAX values? The intention here is to look at the user retention over a period of time.

My current solution employs a temporary table, since I do this over several ranges.

CREATE TEMPORARY TABLE user_retention_temp (first DATE, last DATE, amount INT);

INSERT INTO user_retention_temp
(SELECT MIN(ymd) AS first, MAX(ymd) AS last, player_id FROM day_item GROUP BY player_id);

Here I would have liked to limit this query to only match player_id's that has a first/last that's in a specific period of time.

Instead I'm force to do that for the consequent query:

SELECT LEAST(last - first, 7) AS diff, count(*) AS amount
FROM user_retention_temp
WHERE first >= ? AND last < ? GROUP BY diff

This is pretty awful since I'm filling up the temporary table with lots of data I don't need.

Is there a faster way? Assume at least 2-3 million entries in day_item and that everything we need is indexed.

This should work:

CREATE TEMPORARY TABLE user_retention_temp (first DATE, last DATE, amount INT);

INSERT INTO user_retention_temp
(SELECT MIN(ymd) AS first, MAX(ymd) AS last, player_id
   FROM day_item
  GROUP BY player_id
  HAVING first >= ? AND last < ?);


Collation conflict ... Could not use dbo.sysdac_instances After a migration of databases to a new 2014 server with default collation Latin1_General_CI_AS, but where most of the databases have a collation of Latin1_General_BIN, an attempt to import an Excel spreadsheet is throwing the following error:

I've tracked sysdac_instances down to msdb where it is a system view and querying it directly gives the same error. I'm hoping someone will be able to point me in the direction of a straighforward-ish solution to this problem?

Curiously though, running the import via SSMS 2008 works.

The sysdac_instances view is defined as follows:

CREATE VIEW [dbo].[sysdac_instances]
AS
    SELECT
        -- this must be locked down because we use instance_id visability as a security gate
        case
            when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.instance_id
            when sd.owner_sid = SUSER_SID() then dac_instances.instance_id
            else NULL
        end as instance_id,
        dac_instances.instance_name,
        dac_instances.type_name,
        dac_instances.type_version,
        dac_instances.description,
        case
            when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.type_stream
            when sd.owner_sid = SUSER_SID() then dac_instances.type_stream
            else NULL
        end as type_stream,
        dac_instances.date_created,
        dac_instances.created_by,
        dac_instances.instance_name as database_name
    FROM sysdac_instances_internal dac_instances
    LEFT JOIN sys.databases sd
        ON dac_instances.instance_name = sd.name

The view performs a join on instance_name against the sys.databases.name column.  Since the column definition for the sysdac_instances_internal.instance_name does not specify a collation, it will assume the collation of the msdb database.

Perhaps you restored msdb from an instance where the collation was Latin1_General_BIN, which would cause the collation conflict.  If that is the case, you'd need to recreate the MSDB database, since its collation cannot be altered.


Is there an easy way to trigger automatic failover of a single mirrored database? I have 3 mirrored databases on a single instance of sqlserver2008-R2 in a high safety with automatic failover config. Is there a way to trigger automatic failover of just 1 database?

I want to simulate where the database actually is unreachable and not just hit the 'Failover' button from SSMS. I can stop the endpoint on the primary but that would make all 3 databases unreachable as they use the same endpoint.

How do I disable just 1 db?

If your mirroring is configured correctly for high safety/automatic failover, then I would be inclined to think that you could just take the principal database offline with this:

alter database YourDb set offline
with rollback immediate
go

I believe by taking the database offline, you will have automatic failover without affecting the other two databases that you don't want to failover.


PostgreSQL on Ubuntu - Using conf files for recovery I am using PostgreSQL 8.4 on Ubuntu LTS 10.04.02 server version. I use pg_standby to restore the data and for database recovery created a recovery.conf and placed it on /etc/postgresql/8.4/main where rest of conf files present and tried to start the DB using

sudo service postgresql start

expecting the service to recognize that I am doing a recovery using recovery.conf. I found this file was ignored. Then, I had to copy all *.conf file to my data directory (in my case /dbdata/data) and then, instead of using script from init.d, I had to start the database from 'postgres' user with

/usr/lib/postgresql/8.4/bin/pg_ctl -D /dbdata/proddata  -l /var/log/postgresql/postgresql-8.4-main.log start

How I can make my recovery procedure to obey /etc/init.d/postgresql start|stop|restart
or to have pg_ctl command to look for conf files /etc/postgresql/8.4/main ?

Note : I am relatively newbie to PostgreSQL and to database administration in general

The recovery.conf file needs to go into the data directory (/var/lib/postgresql/...).  The other configuration files can stay where they are (/etc/postgresql/...).  Then a normal restart should do what you want.


sqlite3 command does not save database after creation? I'm migrating from MySQL. I'm having trouble scripting a SQLite database creation. Similar code worked well for MySQL.

It appears the database and tables are created, but then sqlite3 command does not save changes to disk.

$ sqlite3 < testdb.sql
$ ls -Al testdb.db
-rw-r--r-- 1 jwalton jwalton 0 Mar 25 04:02 testdb.db

And:

$ cat testdb.sql

ATTACH DATABASE 'testdb.db' AS 'testdb';
PRAGMA foreign_keys = ON;

-- #####################################################

CREATE TABLE IF NOT EXISTS name_number
(
        nn_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_name VARCHAR,
        nn_number VARCHAR
);

CREATE INDEX IF NOT EXISTS number_index ON name_number(nn_number);

-- #####################################################

CREATE TABLE IF NOT EXISTS whitelist
(
        list_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_id INT NOT NULL,
        list_date DATETIME,
        fk1_id INTEGER,
        FOREIGN KEY(fk1_id) REFERENCES name_number(nn_id)
);

-- #####################################################

CREATE TABLE IF NOT EXISTS blacklist
(
        list_id INTEGER PRIMARY KEY AUTOINCREMENT,
        nn_id INT NOT NULL,
        list_date DATETIME,
        fk2_id INTEGER,
        FOREIGN KEY(fk2_id) REFERENCES name_number(nn_id)
);

-- #####################################################

INSERT INTO name_number (nn_name, nn_number) VALUES ("Test User", "4105551212");
INSERT INTO whitelist (nn_id, list_date) VALUES (last_insert_rowid(), datetime('now'));

INSERT INTO name_number (nn_name, nn_number) VALUES ("Test User", "8005551212");
INSERT INTO blacklist (nn_id, list_date) VALUES (last_insert_rowid(), datetime('now'));

How do I tell sqlite3 command to save the database after creation?

There are two databases here: the main database, which does not have a file name, and therefore is a temporary in-memory database, and the database named testdb with the file name testdb.db, which gets attached to the main database.

The SQL script does not mention testdb anywhere except in the ATTACH command, so all tables are created in the main database.

You shold drop the ATTACH command, and create the database file directly:

$ sqlite3 testdb.db < testdb.sql

Alternatively, replace the ATTACH with .open testdb.db to make that the main database. (The .open is not an SQL command and must not be terminated with a semicolon.)


Disabling "SHOW TABLES;" on mysql I have been looking all over the web now, and can not seem to find the option to disable this command. I think this is quite a risky security hole.

There is an option to disable SHOW DATABASES; , but not SHOW TABLES;

Maybe some of you had

As far as I know you cannot disable SHOW TABLES, but if you have only assigned permissions to tables that the user should be able to access, I don't see how there is a security issue.  A user cannot list tables to which he has no permissions.

root@beren [~]# mysql -u root -p
Enter password:

<-- SNIP -->

mysql> use foo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| bar           |
| baz           |
+---------------+
2 rows in set (0.00 sec)

mysql> create user 'quux'@'localhost' identified by '*******';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on table foo.bar to 'quux'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye
root@beren [~]# mysql -u quux -p foo
Enter password:

<-- SNIP -->

mysql> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| bar           |
+---------------+
1 row in set (0.00 sec)

mysql>


SARG cardinality estimate, why not full-scan? Why there is no full-scan (On SQL 2008 R2 and 2012)?

Test data:

DROP TABLE dbo.TestTable
GO
CREATE TABLE dbo.TestTable
(
   TestTableID INT IDENTITY PRIMARY KEY,
   VeryRandomText VarChar(50),
   VeryRandomText2 VarChar(50)
)
Go
Set NoCount ON
Declare @i int
Set @i = 0
While @i < 10000
Begin
   Insert Into dbo.TestTable(VeryRandomText, VeryRandomText2)
      Values(Cast(Rand()*10000000 as VarChar(50)), Cast(Rand()*10000000 as VarChar(50)));
   Set @i = @i + 1;
End
Go
CREATE Index IX_VeryRandomText On dbo.TestTable
(
    VeryRandomText
)
Go

When execute query:

Select * From dbo.TestTable Where VeryRandomText = N'111' -- bad

Get warning (as expected, because comparing nchar data to varchar column):

<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[DemoDatabase].[dbo].[TestTable].[VeryRandomText],0)" />

But then i see execution plan, and i can see, that it is not using full-scan as i would expect, but index seek instead.

Of course, this is kind of good, because in this particular case execution is way faster than if there would be full scan.

But i can not understand how SQL server came to decision to make this plan.

Also- if the server collation would be Windows collations on server level and SQL Server collation database level, then it would cause full scan on the same query.

When comparing values of different datatypes SQL Server follow the Data Type Precedence rules. Since nvarchar has higher precedence than varchar SQL Server has to convert the column data to nvarchar before comparing values. That means applying a function on the column and that would make the query non-sargable.

SQL Server does however do it's best to protect you from your mistakes so it uses a technique described by Paul White in the blog post Dynamic Seeks and Hidden Implicit Conversions to do a seek for a range of values and then doing the final comparison, with the conversion of the column value to nvarchar, in a residual predicate to filter out any false positives.

As you have noted, this does however not work when the collation of the column is a SQL collation. The reason for that, I believe, can be found in the article Comparing SQL collations to Windows collations

Basically, a Windows collation uses the same algorithm for varchar and nvarchar where a SQL collation uses a different algorithm for varchar data and the same algorithm as a Windows collation for nvarchar data.

So going from varchar to nvarchar under a Windows collation will use the same algorithm and SQL Server can produce a range of values from, in your case, a nvarchar literal to get rows from the varchar SQL collation column index. However, when the collation of the varchar column is a SQL Collation that is not possible because of the different algorithm used.

Update:

A demonstration of the different sort orders for varchar columns using windows and sql collation.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table T(C varchar(10));

insert into T values('a-b'),('aa'),('ac');

Query 1:

select C
from T
order by C collate SQL_Latin1_General_CP1_CI_AS;

Results:

|   C |
|-----|
| a-b |
|  aa |
|  ac |

Query 2:

select C
from T
order by C collate Latin1_General_100_CI_AS;

Results:

|   C |
|-----|
|  aa |
| a-b |
|  ac |


Selecting rows with condition on two columns together i have table with columns
Number and VAT
How can i select all rows without row which is Number=123 and VAT=15 ?
I tried something like this

SELECT * FROM tablex WHERE <.. zilions of conditions..> AND (Number=123 AND VAT!=15)

But it does not work, i need every rows and with Number 123 too but not in case if VAT is 15.
Any idea ? Thank you

then u can use this one

SELECT * FROM tablex WHERE Number<>123 OR VAT<>15


import data from csv to mysql - good database structure I have a collection of csv files with measurements with the following 54 columns:

SITE (string)
TIME (integer as yyyymmddhh)
PROD (double)
EST.0 (double)
...
EST.50 (double)

I want to read this to a mysql database. I would prefer a structure that would allow me to compute average and variance of values EST.0 .. EST.50, for each row.

What would be a good database structure for this data? Can I keep the columns as they are, or should I only have one "value" column? And if the former, how can I then get the averages and variances that I want?

From reading related questions, it looked like having so many value columns is not a good idea, so I was thinking about having a "value" column for all the double values, plus an "est_ID" column for an identifier, using -1 or NULL to denote the "PROD" value - does it make sense?

And what about the date-time column - will mysql understand it, or do I need to provide some information about date-time format?

As for handling multiple files, I guess that a small bash script calling mysqlimport should do the job nicely?

Thanks.

Michal

You will want to have two database tables for this.

One table with id int/bigint autoincremenmt, site, time and prod.
id is the primaty key.

And one table with id (same as in previous table but no autoincrement, it refers to the id in the first table)  the number of the EST, and EST . The primary key of this table would span two columns, id and number of the EST (this may require some fiddling with the database table creation tool you are using).

As for date columns, no mysql doesn't understand that format, it has its own date and datetime formats for columns. At our place here we are using unix timestamps and store these in the database as integers or bigints.


SQL or NoSQL for structured clinical data I'm designing a database for a primary health center, the idea

We basically have a Patient with many Encounters, also the Encounters have to be entered from different Users (Health Professionals). Patient info is unlikely to change, the Encounter is the "important" part of this database, already persisted data it's not going to be modified, as we only have to insert new encounters as the patient comes to their controls. Users info it's also (very) unlikely to change. It could be possible that we are going to need to add more columns once a year in case the Health Ministry decides we need to start "logging" something else.

Right now, the "Encounters paper" (tarjetón) is basically a table with around 20 columns, where each row is a new Encounter.

This is a small center, so I'm not going to be dealing with lots of data, concurrency won't be an issue either, since only a couple of professionals are going to "write" to the database.

I started building using mongodb (rails app, using mongoid)

As usual in similar cases, I would strongly recommend that you look at Open Source solutions - well before the design phase. EVEN if you are doing something truly radical, one can always learn by standing on the shoulders of giants.

I have worked as a student on the OpenEHR project, so this interests me. It's been a while, but I would start by looking here, here and here. GIYF :-).

For a pure database recommendation, I would include the big three of Open Source databases - MySQL, PostgreSQL or Firebird. Find a system you wish to adapt. "Good programmers know what to write. Great ones know what to rewrite (and reuse)" and use the database with that - unless you have a particular requirement that precludes the chosen system's database.

There was a time I would not have recommended MySQL, but I have recently had occasion to work with it, and I was pleasantly surprised - though not having CHECK constraints is still a bit of a shock!

I note with interest in your profile that you are an experienced contributor to the stackexchange lists, so I would ask the question as to why you are choosing MongoDB? I'm not saying your choice is incorrect, I'm just wondering what it is that MongoDB has that you can't get from a "classic" RDBMS?

My understanding is that NoSQL solutions are meant to be for coping with web-scale problems (in big data and real-time web applications), and not running simple medical centres. Even if your system was dealing with the entire population of Chile, it still wouldn't start to be "large" on the web-scale. A further point would be that I can see no valid reason whatsoever to sacrifice consistency with medical data - it might be OK for books on Amazon, but not this sort of information ("Many NoSQL stores compromise consistency" [ibid]).

In any case, I wish you well with the project.


SQL Server 2005, Large Binary Storage I'm looking at trying to store a large amount of files in my SQL Server 2005 instead of saving them to a file system.

I recently read a white paper suggesting that anything over 1M should be stored on a file server as it can affect SQL Server performance.

Does anyone have any experience storing large binary data in SQL 05?

Thoughts on why I should or should not do so?

Using SQL Server for small file storage is usually a bad idea for different reasons.

SQL Server costs money. A lot of money. Even if everything works well, you're paying an extra for what a simple file server (if done right) would do for "free".
SQL Server is transactional, and ACID compliancy has it's overhead. It's much slower than writing to disk without a transaction. NTFS has partial write protection, but the overhead is much smaller than with SQL Server.
Writing to a filesystem is straightforward, but to access SQL Server you need to go through a driver, network/shared memory etc. Everything you do to access the files now needs the database in the middle.
It's not meant to be a file store. Eventually, what you get is a huge .mdf file on the disk which you can't do much with. Want to free up space on disk after deletion? You need to shrink the database which can take ages.

There are multiple other reasons.
Perhaps another way to put it - SQL Server is not meant for this kind of thing, and if you start using it for files it's going to grow very fast. Now, managing a small database is one thing, and a huge one is another (number of rows not relevant for this discussion, only data size) - and you don't want to go into that area if you don't have to.


Unable to remotely connect to Postgresql 9.5 instance I am struggling to connect to a remote Postgresql 9.5 instance running on Ubuntu 16.10.
The usual:

  psql: could not connect to server: Connection timed out
    Is the server
  running on host "xx.xx.xx.xx" and accepting   TCP/IP connections on
  port 5432?

My postgresql.conf includes

listen_addresses='*'

confirmed by output from

netstat -nlp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      22981/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      22981/postgres
unix  2      [ ACC ]     STREAM     LISTENING     3320886  22981/postgres       /var/run/postgresql/.s.PGSQL.5432

Apart from my specific IP address & range, I have also tried the following entries in pg_hba.conf:

host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5
host    all             all             all                     md5
host    all             all             *                       md5

I have the following ufw rules:

To                         Action      From
--                         ------      ----
22/tcp (OpenSSH)           ALLOW IN    Anywhere
80,443/tcp (Nginx Full)    ALLOW IN    Anywhere
5432                       ALLOW IN    Anywhere
22/tcp (OpenSSH (v6))      ALLOW IN    Anywhere (v6)
80,443/tcp (Nginx Full (v6)) ALLOW IN    Anywhere (v6)
5432 (v6)                  ALLOW IN    Anywhere (v6)

However, the problem persists even when disabling the firewall.

Scanning with nmap:

nmap xx.xx.xx.xx -p5432

Starting Nmap 7.01 ( https://nmap.org ) at 2017-02-13 09:17 SAST
Nmap scan report for xx.xx.xx.xx
Host is up (0.0074s latency).
PORT     STATE    SERVICE
5432/tcp filtered postgresql

Almost all similar questions on SE seem to be solved with

  listen_addresses='*'

which is not an issue here.

Really confused by this, help would be greatly appreciated.

The machine is not down as I am able to ssh into the machine. Additionally nginx server is also accessible. Telnet to port 5432 times out.

There is no local router in front of the ubuntu server, it is a dedicated server with public network interface that is bound to the xx.xx.xx.xx address. This is a migration from a previous generation box at the same hosting company who does not offer the option of an additional firewall device.  This is a clean Ubuntu 16.10 server install.

Turns out my hosting provider moved the server behind a managed firewall without notice.


Does MariaDB support ST_Distance_Sphere? Running mariadb, I'm getting

  ERROR 1305 (42000): FUNCTION ST_Distance_Sphere does not exist

When I run a basic query like

SELECT ST_Distance_Sphere( POINT(0,0), POINT(0,1) );

However, I'm using a pretty new version.

Server version: 10.1.25-MariaDB-1 Ubuntu 17.10

What's going on here.

Almost unbelievably, MariaDB lacks this ST_Distance_Sphere (MDEV-13467).

Find their support matrix here.

And it's not just that either, it also lacks ST_GeoHash that MySQL has.


Mysql Cluster Exceeding MaxBufferedEpochs I have an mysql cluster which have 4 api nodes, 2 management nodes and 4 data nodes. Today, I was having problems while trying to connect database and all queries was hanging at "Opening tables" state. After inspecting logs i have found these erros on logs:

Api Node Erros:

2015-08-20 19:44:14 15540 [Note] NDB Schema dist: Data node: 5 failed, subscriber bitmask 00
2015-08-20 19:44:14 15540 [Note] NDB Schema dist: Data node: 6 failed, subscriber bitmask 00
2015-08-20 19:44:14 15540 [Note] NDB Schema dist: Data node: 7 failed, subscriber bitmask 00
2015-08-20 19:44:14 15540 [Note] NDB Schema dist: Data node: 8 failed, subscriber bitmask 00
2015-08-20 19:44:14 15540 [Note] NDB Schema dist: cluster failure at epoch 3313124/17.
2015-08-20 19:44:14 15540 [Note] NDB Binlog: ndb tables initially read only on reconnect.
2015-08-20 19:44:14 15540 [ERROR] /opt/mysql/server-5.6/bin/mysqld: Got temporary error 4028 'Node failure caused abort of transaction' from NDBCLUSTER
2015-08-20 19:44:14 15540 [ERROR] /opt/mysql/server-5.6/bin/mysqld: Sort aborted: Got temporary error 4028 'Node failure caused abort of transaction' from NDBCLUSTER
2015-08-20 19:44:14 15540 [ERROR] Got error 4010 when reading table './database_name/table'
2015-08-20 19:44:14 15540 [Note] NDB Binlog: cluster failure for ./database_name/table_name at epoch 3313124/17.

mysql> show processlists;

Id  User    Host    db  Command Time    State   Info
1   system user     NULL    Daemon  1497    Waiting for ndbcluster to start NULL

Data Node Erros:

2015-08-20 19:44:14 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 14229759227592721 nodes: 0000000000000000000000000000040000000000000000000000000000001a00
2015-08-20 19:44:14 [ndbd] WARNING -- ACK wo/ gcp record (gci: 3313124/17) ref: 0fa2000b from: 0fa2000b
2015-08-20 19:44:14 [ndbd] WARNING -- ACK wo/ gcp record (gci: 3313124/17) ref: 0fa2000c from: 0fa2000c
2015-08-20 19:44:14 [ndbd] WARNING -- ACK wo/ gcp record (gci: 3313124/17) ref: 0fa2008a from: 0fa2008a

Management Node Erros:

2015-08-20 19:44:14 [MgmtSrvr] INFO     -- Node 5: Disconnecting lagging nodes '0000000000000000000000000000000000000000000000000000000000000200',
2015-08-20 19:44:14 [MgmtSrvr] WARNING  -- Node 5: Disconnecting node 9 because it has exceeded MaxBufferedEpochs (100 > 100), epoch 3313119/4

Detailed logs and configurations

Data node config:

https://gist.github.com/sdemircan/730fa49fcc14b4376c42

Api node config:

https://gist.github.com/sdemircan/f9d230d32700b86564fd

Management node config:

https://gist.github.com/sdemircan/d6fbd54799daaae01bf2

Api Node logs:

https://gist.github.com/sdemircan/2d62b1c92176de9de9d3

Data Node logs:

https://gist.github.com/sdemircan/d0c97b82457a9c33deaa

Data Node logs:

https://gist.github.com/sdemircan/3faa1e41367bc7655210

Management Node logs:

https://gist.github.com/sdemircan/a026ac57757fafdafaa9

What might bring MaxBufferedEpochs to upper limit?

You may have had a large transaction, a query retrieving many rows, pull back a lot of data and saturate the network connection for node 9.

On reconnect the API nodes are readonly so it has to be a query executed before this

NDB Binlog: ndb tables initially read only on reconnect


Killing a process without login to MySQL Here is the situation:

I was running a very complex query on a database which was expected to execute within a few hours over night(server configuration is not good and data set is large). This query is using table tblA
while the query is being executed, application A (running on the Server) executes  DROP TABLE tblA.
This DDL Statement locks the metadata for tblA since table is in use by another transaction (my query).
Application B comes in and wants to perform an update on tblA.
Update statement does not execute since the DROP TABLE tblA holds the metadata lock.
Application B is multithread and executes another update on tblA
Application B keep doing that until max_connections limit is met and I receive Too Many Connection error every time I try to connect to server.
Right at this moment the application I was using to execute the query (HeidiSQL) stop working and crashes.
I just noticed all the connection to server is through Root user from local host so there is no way to log in to MySQL to kill the DDL statement.

Question is:

Is there anyway to end this without having to kill or stop MySQL Server?

I already tried killing Application A and B but the process still remains in MySQL queue. It also sounds like my query is not willing to end any time soon.

For cases like this, there is "one extra connection available".  Anyone with SUPER priv (eg. "root") can get in to kill the DROP, which would have uncorked the mess.

However, a prerequisite for doing that is to not have users and applications log in as "root".  Instead they should have their own logins, preferable (for security reasons) limited to the database that they need access to.

Reserve "root" for 'admin' actions such as the one you needed in this case.


Why colname as input param of function = not good idea? Someone told me that using text input for column names and formatting it, like I do below, is rarely a good idea. When I asked why, however, an answer wasn't given. That was on the postgresql IRC, and those guys seem to know their stuff. So I'd like to know why is it not advised ? I'm mostly wondering if it opens the door for sql injection.

create or replace function getItemsOrderBy(order_by_p text)
RETURNS TABLE (id int) AS $$

BEGIN

    return query EXECUTE format('
    SELECT id
    FROM items
    ORDER BY %s', order_by_p) ;

END;

He also said to use execute with using instead, so what's the difference between this:

return query EXECUTE format('
SELECT id
FROM items
ORDER BY %s', order_by_p) ;

and this :

return query EXECUTE '
SELECT id
FROM items
ORDER BY $1' USING order_by_p ;

My function is more complex than what is above - the format part is only part of it. I have a choice to either create one function that can deal with multiple cases (for ordering) or create a bunch of them to deal with every ordering. I felt like doing only one was more practical. Having no function at all isn't an option.

I am actually using pg-promise but I was under the impression that since I'm doing a lot of back and forth between the back end and the DB (send something, wait response, compute something else, send again..) I should go with function and let everything happen all at once.

There are two questions here,

What is the difference between EXECUTE .. USING and EXECUTE FORMAT()
Why is wrapping or generating simple SQL statements in a procedural function a bad idea?

The difference between EXECUTE .. USING and EXECUTE FORMAT()
From the docs,

The command string can use parameter values, which are referenced in the command as $1, $2, etc. These symbols refer to values supplied in the USING clause. This method is often preferable to inserting data values into the command string as text: it avoids run-time overhead of converting the values to text and back, and it is much less prone to SQL-injection attacks since there is no need for quoting or escaping. An example is:
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
  INTO c
  USING checked_user, checked_date;

Note that parameter symbols can only be used for data values — if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:
EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

A cleaner approach is to use format()'s %I specification for table or column names (strings separated by a newline are concatenated):
EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

An EXECUTE with a simple constant command string and some USING parameters, as in the first example above, is functionally equivalent to just writing the command directly in PL/pgSQL and allowing replacement of PL/pgSQL variables to happen automatically. The important difference is that EXECUTE will re-plan the command on each execution, generating a plan that is specific to the current parameter values; whereas PL/pgSQL may otherwise create a generic plan and cache it for re-use. In situations where the best plan depends strongly on the parameter values, it can be helpful to use EXECUTE to positively ensure that a generic plan is not selected.

So you have a few arguments here.

You can use both EXECUTE FORMAT() ... USING
USING allows the plan to be cached.
USING allows symbols to stay symbols and stops them from having to be converted to text and re-escaped.
USING can not be used with identifiers, only literals.

Wrapping and generating simple SQL statements in a procedural function is a bad idea.
As for the other part of the question,

So I'd like to know why is it not advised? (Wrapping simple SQL statements in plpgsql.)

There are a lot of reasons for that,

functions as such obscure the costs (plural) to the planner, and require the user to explicitly

set the execution cost (or use a rather silly estimate)
9.6+ establish if it is parallel-safe safe/restricted/unsafe
9.6+ establish if it has side effects strict/immutable/volatile

they obscure the internals to the user.
they prevent predicate-pushdown.
they complicate permissions (now you need access to the function too).
they raise the barrier for maintenance, now you have to define the result set the function returns TABLE (id int)
they present all kinds of problems with ORMs.

And, it's not SQL. You're building a new language on top of a DBMS. Why?
As for the dynamic component, there are other ways to engineer around the problem. Take for instance the exact statement provided, the worst case scenario is where ever you see that
SELECT * FROM getItemsOrderBy($col);

You have to explicitly write out the order-by. As bad as it, it's a better solution in my opinion.
SELECT id FROM items ORDER BY col1
SELECT id FROM items ORDER BY col2
SELECT id FROM items ORDER BY col3

A step even further would be to use a library which provides some kind of assistance for generating , like pg-promise
let args = { orderBy: "col1" };
if ( args.orderBy !== 'col1' ) {
  throw new Error "invalid orderBy Column";
}
db.manyOrNone( 'SELECT id FROM items ORDER BY ${orderBy~}', args );

Or, DBIx::Abstract, or an ORM like DBIx::Class.