Cross-database view - Not able to access database under the current security context We have two databases, Adb and Bdb. In Bdb, we create a view that references another view in Adb, like so: CREATE VIEW Bdb.dbo.Bview AS SELECT * FROM Adb.dbo.Aview.

We have an SQL Authenticated Blogin, mapped to Buser, both on Adb and Bdb, with at least the db_datareader role on both.

The following doesn't work:

USE Bdb;
EXECUTE AS USER = 'Buser';
SELECT * FROM Bdb.dbo.Bview;
SELECT * FROM Adb.dbo.Aview;

The following error is thrown for both selects:

Msg 916, Level 14, State 1, Line 4
The server principal "Buser" is not able to access the database "Adb" under the current security context.

However, this works:

USE Adb;
EXECUTE AS USER = 'Buser';
SELECT * FROM Adb.dbo.Aview;
SELECT * FROM Bdb.dbo.Bview;

I have noticed that when I first USE Bdb and switch to Buser, I can't see any other databases:

USE Bdb;
EXECUTE AS USER = 'Buser';
SELECT * FROM sys.databases; -- only master, tempdb and Bdb is shown

But, when I USE Adb first, I see all of them, even ones that don't have the Buser and can't be accessed by it:

USE Adb;
EXECUTE AS USER = 'Buser';
SELECT * FROM sys.databases; -- all DBs on the server are shown

What could be causing this issue? What should I check?

First things first: DO NOT ENABLE TRUSTWORTHY!! There is absolutely no reason to open up such a large security hole. (note: msdb has TRUSTWORTHY enabled and that is fine as it is a Microsoft-supplied DB; User created DBs never need TRUSTWORTHY enabled)

Now, if this works when impersonating a User instead of a Login, it is due to your [Adb] Database already being enabled as TRUSTWORTHY ON, which removes the default quarantine that exists when using Database-level Impersonation. You can see this by executing the following:

SELECT db.is_trustworthy_on, *
FROM   sys.databases db
WHERE  db.[name] IN (N'Adb', N'Bdb');

Assuming that it is the case that Adb is enabled for TRUSTWORTHY and Bdb is not, then still please do not enable TRUSTWORTHY for Bdb. It would be best to disable TRUSTWORTHY for Adb and use Module Signing to accomplish this:

ALTER DATABASE [Adb] SET TRUSTWORTHY OFF;

For an example of doing this cross-database access via Module Signing, please see the following answer of mine (here on DBA.SE):

Access view based on table in another database without account in that other database

For more info on why you should use Module Signing instead of TRUSTWORTHY (or even Cross-Database Ownership Chaining), please see the following post of mine:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

For more info on Module Signing in general, please see:

https://ModuleSigning.info/

As @Nic mentioned in a comment on the question, it is best to use EXECUTE AS LOGIN instead of EXECUTE AS USER when testing. Logins are at the Server-level and will have access to Database's that a User has been created in for that Login. This will be just like logging into SQL Server as that account.

The reason for the difference is stated in the Microsoft documentation page for Extending Database Impersonation by Using EXECUTE AS

  Understanding Impersonation Scope

  ...

  However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

Also, there is a lot of good information on the "Extending Database Impersonation by Using EXECUTE AS" MSDN page (linked above) that explains authenticators and the reasoning behind these rules.

Given that these two Databases are vendor-supplied (info added after I submitted this answer), then it is probably best to just switch to EXECUTE AS LOGIN and not make any changes to the Databases (for Module Signing).


MYSQL variable in function Can anybody tell why the value of @URlID is always -1 even when i know the query will return a result? I have tried any possible type of variables and any possible way of inserting into the variable yet no effect.

    DELIMITER $$
    CREATE FUNCTION getShortURLID(URL varchar(1200))
    RETURNS INT
    DETERMINISTIC
    READS SQL DATA
    MODIFIES SQL DATA
    BEGIN
        SET @newShortURL:=NULL;
        SELECT SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(URL, "http://", ""), "https://", ""), "www.", ""), '/', 1) INTO @newShortURL ;
    set @URLID:= -1;
    set @URLID := (SELECT ifnull(id,0) FROM url_productivity WHERE url = @newShortURL LIMIT 1);

    IF @URLID <=> 0 then
        INSERT INTO urls(url) VALUES(@newShortURL);
        RETURN LAST_INSERT_ID();
    ELSE
        RETURN @URLID ;
    END IF;
    END$$

This line

set @URLID := (SELECT ifnull(id,0) FROM url_productivity WHERE url = @newShortURL LIMIT 1);

isn't returning any rows at all, therefore @URLID retains the value set in the preceding line i.e. -1.


Timestamp to milisecond conversion I am a beginner to mysql.

I would like to convert a timestamp like this "12:00:00 AM.260" to miliseconds.

The reference for the conversion will be "12:00:00 AM.000".

How should i go about this ?

Thank you very much !

You should take a look the TIMESTAMPDIFF function.

From the example

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885

You could use the microsecond unit and divide by 1000 - MySQL doesn't appear
to support milliseconds. HTH.

[EDIT in response to OP]

Take a  look at the code below - notice the 1 millisecond difference in the two
returned values. I tested for up to 50 years and it works fine on my 64 bit fedora linux system.

It is even accurate to 1 microsecond in 50 years - but you can use ROUND to
get suitable values.

mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2014-12-10 13:00:00.000', '2014-12-11 13:56:00.000')/1000 AS Milliseconds_Elapsed FROM Dual;
+----------------------+
| Milliseconds_Elapsed |
+----------------------+
|        89760000.0000 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2014-12-10 13:00:00.000', '2014-12-11 13:56:00.001')/1000 AS Milliseconds_Elapsed FROM Dual;
+----------------------+
| Milliseconds_Elapsed |
+----------------------+
|        89760001.0000 |
+----------------------+
1 row in set (0.00 sec)

mysql>

50 years (+ 1 microsecond)

mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2014-12-10 13:00:00.000', '2062-12-11 13:56:00.001')/1000 AS Milliseconds_Elapsed FROM Dual;
+----------------------+
| Milliseconds_Elapsed |
+----------------------+
|   1514854560001.0000 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2014-12-10 13:00:00.000', '2062-12-11 13:56:00.000001')/1000 AS Milliseconds_Elapsed FROM Dual;
+----------------------+
| Milliseconds_Elapsed |
+----------------------+
|   1514854560000.0010 |
+----------------------+
1 row in set (0.00 sec)

mysql>


How to add comment to a column in a table in Postgres? I'm new to Postgres. I would like add a comment to my column mod in a table called app-user-bookings

I have tried this sql code:

alter table app_user_bookings
modify column mod
int default 1
comment "1# mobile booking,    2# admin booking,    3# web booking, 4# tell call";

But which didn't help me, can anyone help me?

To update comment, don't use alter command.

Here is the PostgreSQL syntax:

comment on column app_user_bookings.mod is '1# mobile booking,    2# admin booking,    3# web booking, 4# tell call'

It makes sense to add a check to prevent wrong values appearing:

ALTER TABLE app_user_bookings
ADD CHECK (mod IN (1,2,3,4));


T-SQLRevoke permissions to all tables in the sql server in one go How can we revoke a certain permission to all tables?

I've tried (T-SQL):

REVOKE SELECT on schema::DBO TO [public] AS [dbo]

But nothing happens.. All tables still have Select permission.

I can take one by one

REVOKE SELECT on  dbo.table TO [public] AS [dbo]

One by one works ok.

How to revoke permissions from all tables of a specific schema in one go?

Do I need to build  a cursor for this?

you can try

EXEC sp_MSforeachtable @command1 = 'PRINT (''REVOKE SELECT on ? TO [Role] AS [dbo]'')'

and instead of print

EXEC sp_MSforeachtable @command1 = 'REVOKE SELECT on ? TO [Role] AS [dbo]'


dbcc checktable will write in my dbcc page (x,9,3) for the field dbi_dbccLastKnownGood If I run DBCC CHECKALLOC,DBCC CHECKCATALOG and DBCC CHECKTABLE in all objects it will update my internal page with the actual date in dbi_dbccLastKnownGood field?

The main reason of the question is for a db with 14 TB and my window is short so I need lot of windows.

According to Erin Stellato's blog, What Checks Update dbccLastKnownGood?:

  Breaking out the checks between the different commands does not update
  dbccLastKnownGood – running CHECKDB is the only thing that does

Erin also notes there that DBCC CHECKFILEGROUP and CHECKDB with the PHYSICAL_ONLY option will update dbccLastKnownGood.


Design db schema in correct way for parent child kind of requirement I am creating schema of tables. But not able to understand best way to do it.

Following are the tables:

  Car Model - It contains all car models with types.  For eg. Mercedes(brand) - GLA 250(model) - Sedan(type)
  Car Panel - It will contain all car panels. For eg. Fender(panel name)
  Car Panel Price By Type - It will contain car panel price by type. For eg. Sedan(type) - Fender(car panel id) - 2500(price)
  Car Panel Price By Model - It will contain car panel price by Model. For eg. Mecedes GLA 250(car model id) - Fender(car panel id) -
  2700(price)
  Booking Package - It will contain all the order related fields. For eg. Order Id - Mercedes GLA 250 (car model id)
  Booking Package Panels  - It will contain all the panels in order along with their price. For eg. Order id - Car Part Price By Model - Car Part Price By Type

Here, It will contain car panel price by model if it exists other wise car panel price by type. Because it can be possible that for some models, we don't add the price and use the type level pricing.

Another way to store Order panels.

  For eg. Order id - Fender (panel id) - 2500(price from model or type)

Please suggest which is better way to design this order panel table.

This is an impressive ERD. Are you sure you want to have every table inherit from CreatedAtAbstractBase?

The tables CarTypePanelPrice and CarModelPanelPrice are almost the same, differing only by car_model identifier. I would have the car_model field be optional (NULLable) and merge both tables. You'll know if one record is one type or the other if that field is NULL or NOT NULL. The orders/bookings seem alright.


SQL Server JOIN not working I am not sure why the below join query is not returning any results.

SELECT * FROM table1 JOIN table2  ON table1.fieldid = table2.idfield

The below 2 queries return results so I am not sure why the join wont pick up any rows when i can clearly see that both tables have join field values in common.

SELECT *  FROM table1 WHERE fieldid =  900399530  (returns rows)
SELECT *  FROM table2 WHERE idfield =  900399530  (returns rows)

Any ideas?

Below are the data types for the 2 columns:

  fieldid [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

  idfield [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

As far as it's a CHAR/VARCHAR/NVARCHAR field you should use single quotes:

Maybe there is some extra or leading space.

Try it on this way:

SELECT *  FROM table1 WHERE fieldid =  '900399530';
SELECT *  FROM table2 WHERE idfield =  '900399530';

or remove additional spaces by using TRIM() function.

SELECT * FROM table1 JOIN table2  ON TRIM(table1.fieldid) = TRIM(table2.idfield);

For versions below 2017:

SELECT *
FROM   table1
JOIN   table2
ON     LTRIM(RTRIM(table1.fieldid)) = LTRIM(RTRIM(table2.idfield));


Slower SQL Server performance after allocating more CPU and RAM We have SQL Server 2008 R2 (10.50.1600) running on a virtual Windows 2008 R2 server. After upgrading the CPU from 1 core to 4 and the RAM from 4 gb to 10 gb, we've noticed the performance is worse.

Some observations I see:

A query that took <5 seconds to run is now taking >200 seconds.
The CPU is pegged at 100 with sqlservr.exe as the culprit.
A select count(*) on a table with 4.6 million rows took over 90 seconds.
The processes that are running on the server haven't changed. The only change was to increase the cpu and ram.
Other sql servers have a static paging file where this server is set to manage it on its own.

Has anyone run into this issue before?

Per sp_BlitzErik, I ran

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

Giving me these results.

There's a lot going on here, and most of it is pretty broad and vague.

2008R2 RTM came out on April 21, 2010. It's totally out of support. You'll want to prioritize getting on the latest Service Pack, which came out just about 3 years ago to the day. That way you'll be covered if you're hitting a weird bug or something. Head on over here to figure out what you need to download.
Since you added vCPUs (from 1 to 4) and didn't change any settings, your queries can now go parallel. I know this sounds like they'll all be faster, but hang on!
You may have added RAM, but you may not have changed Max Server Memory so your server can take advantage of it.
Figure out what your server is waiting on. An open source project I work on provides free scripts to help you measure your SQL Server. Head on over here if you wanna give them a try.

You'll wanna grab sp_BlitzFirst to check out your server's wait stats. You can run it a couple ways.

This will show you what your server has been waiting on since it started up.

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

This will show you what queries are waiting on now, during a 30 second window.

EXEC dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;

Once you figure out what queries are waiting on (there's a ton of stuff written about wait stats out there), you can start making changes to get things under control.

If you see them waiting on CXPACKET, that means your queries are going parallel, and maybe trampling over each other. If you hit this, you'll probably want to consider bumping Cost Threshold for Parallelism up to 50, and maybe dropping MAXDOP down to 2.

After this step is when you want to use something like sp_WhoIsActive or sp_BlitzWho (the latter is in the GitHub repo from earlier) to start capturing query plans. Aside from wait stats, they're one of the most important things you can look at to figure out what's going wrong.

You may also want to check out this article by Jonathan Kehayias about VMWare Counters to check out in relation to SQL Server.

Update

Reviewing the wait stats and boy are they weird. There's definitely something up with the CPUs. Your server is mostly sitting around bored, but when things heat up, things get bad. I'll try to break this down easily.

You're hitting a poison wait called THREADPOOL. You don't have a ton of it, but that makes sense because your server isn't terribly active. I'll explain why in a minute.
You have really long average waits on SOS_SCHEDULER_YIELD and CXPACKET. You're on a VM, so you're gonna wanna make sure that the SQL Server has reservations, or that the box isn't horribly oversubscribed. A noisy neighbor can really ruin your day here. You're also going to want to make sure that the server/VM guest/VM host aren't running in Balanced Power mode. This makes your CPUs spin down to unnecessarily low speeds, and they don't immediately spin back up to full speed.
How do they tie in? With 4 CPUs you have 512 worker threads. Keep in mind, you had the same amount with a single CPU, but now that your queries can go parallel, they can consume many more worker threads. In your case 4 threads per parallel branch of a parallel query.

What's going parallel? Most likely everything. The default Cost Threshold for Parallelism is 5. That number was made the default sometime in the late 90s working on a desktop that looked like this.

Granted, your hardware is smaller than most laptops, but you're still a bit ahead of that thing.

When lots of parallel queries get going, you're running out of those worker threads. When that happens, queries just sit around waiting for threads to get going. That's also where SOS_SCHEDULER_YIELD comes in. Queries are stepping off CPUs and not getting back on for a long time. I don't see any blocking waits, so you're most likely just all stuffed up on intra-query parallelism waits.

What can you do?

Make sure nothing is in Balanced Power mode
Change MAXDOP to 2
Change cost threshold for parallelism to 50
Follow the Jon K. article above to validate VM health
Use the script called sp_BlitzIndex to look for any missing index requests.

For more thorough troubleshooting, check out the whitepaper I wrote for Google on hardware sizing in the cloud.

Hope this helps!


Deploy IBM Db2 ODBC driver My Windows 10 users need the ODBC driver for Db2 installed.
I can locate the full driver package:
ibm_data_server_driver_package_win64_v11.1.1.3fp3b.exe
but it is a 100MB+ package.
Would there be a smaller package to distribute?

CLI was the magic keyword which allowed me to locate:
Obtaining the IBM Data Server Driver for ODBC and CLI software
However, it is not updated as the search criteria ended up as:

Several steps later, you can finally locate the fix pack:

and several steps later download the file:
v11.5.5fp1_ntx64_odbc_cli.zip
The install, however, isn't straight forward, thus requires a guide:
Installing the IBM Data Server Driver for ODBC and CLI software on Windows operating systems
To streamline the process, it seems you have to write your own installer.


SQL Server: Is altering collation on existing databases safe? I'm new to SQL Server, and wasn't aware of the possibility that T-SQL may be case insensitive.

I have been normalizing entities by splitting tables and constraints. I'm scared that if I change the collation now, that some of those constraints may be invalid if, for example, a 'case insensitive' foreign key value points to a PK value with different case.

If I were to run this query on the DB (tested on a basic database):

ALTER DATABASE <db_name>
COLLATE SQL_Latin1_General_Cp1_CS_AS ;
GO

What would happen if some constraints were violated?

Is there any other reason that I should be careful making a database-wide change such as changing the collation?

Changing a database's Collation doesn't affect existing columns.  It affects new non-XML string columns created that don't specify the COLLATE clause (including table variables), string literals, and variable values (not variable name resolution, which is determined by the Instance-level Collation). Meaning, something like the following will be affected:

IF (@Variable = 'string')
BEGIN
    ...
END;

This change will also affect Database-level meta-data, such as names of schemas, objects, columns, indexes, etc. Meaning, the following two scenarios will be affected:

SELECT ...
FROM   sys.indexes si
WHERE  si.[name] = N'somename'; -- real name = SomeName

and:

SELECT ...
FROM   dbo.sometable st -- real name = SomeTable

In both of those examples, they would work in a case-insensitive Collation, but return nothing or error, respectively, in a case-sensitive Collation.

Finally, as @JonathanFite was kind enough to remind me of, changing the DB Collation may impact queries involving temporary tables. The default Collation for string columns in temporary tables (not table variables) is the default Collation for [tempdb] (which should be the same as [model], which should be the Instance default, unless someone restored [model] from a server that had a different default Collation), not the Collation of the local Database. Meaning, even though the temp tables get created each time and thus you might expect them to act like "newly created tables" with the new Collation, they will in fact act like "existing tables" and will continue to behave as they did prior to the Collation change. If you need string columns in temporary tables to use the new Collation, you will need to explicitly set their Collation using COLLATE DATABASE_DEFAULT in the CREATE TABLE statements.

Hence you really need to do a lot of testing!

If you want to change existing columns then you will need to drop existing constraints, issue an ALTER TABLE ... ALTER COLUMN and then recreate the Constraints. You will also need to rebuild indexes that use any column that has its Collation changed as the sort order might be different.

Also, it is best to not use Collations starting with SQL_. Instead use Latin1_General_100_CS_AS. The Collations starting with SQL_ have been obsolete (even if not officially deprecated) since SQL Server 2000 was released. Their handling of VARCHAR / 8-bit data is obsolete and is not inline with newer behavior. Unfortunately, for backwards compatibility reasons, the default Collation for US English installations used to be the SQL_Latin1 Collations, as noted in the Using SQL Server Collations MSDN page:

  For backward compatibility, the default English-language (US) collation is SQL_Latin1_General*.

This is also noted in the default Collation chart in the Collation Settings in Setup MSDN page (hit Control-F and paste in sql_latin). I believe this default changed to a Windows Collation starting in SQL Server 2014, but the documentation, even for the SQL Server 2016 setup, still points to the 2008 R2 setup page for Collations.

Below is a script to see some of the behavior differences when changing a Database's Collation:

USE [master];
GO

IF (DB_ID(N'ChangeDatabaseCollationTest') IS NULL)
BEGIN
    CREATE DATABASE [ChangeDatabaseCollationTest] COLLATE Latin1_General_100_CI_AS;
END;
GO

USE [ChangeDatabaseCollationTest];
GO
-- Current DB Collation: Latin1_General_100_CI_AS

EXEC sp_help 'sys.objects';
-- Collation for [name] = Latin1_General_100_CI_AS

IF ('A' = 'a')
BEGIN
    SELECT 'Case INsensitive comparison works.';
END;
ELSE
BEGIN
    SELECT 'Case INsensitive comparison did NOT work.';
END;
-- Case INsensitive comparison works.

CREATE TABLE dbo.CaseTest_a (ID INT); -- success
SELECT * FROM dbo.CaseTest_A; -- success

CREATE TABLE dbo.CaseTest_A (ID INT); -- error:
-- Msg 2714, Level 16, State 6, Line 5
-- There is already an object named 'CaseTest_A' in the database.

ALTER DATABASE [ChangeCollationTest] COLLATE Latin1_General_100_CS_AS; -- success

IF ('A' = 'a')
BEGIN
    SELECT 'Case INsensitive comparison works.';
END;
ELSE
BEGIN
    SELECT 'Case INsensitive comparison did NOT work.';
END;
-- Case INsensitive comparison did NOT work.

SELECT * FROM dbo.CaseTest_A; -- error:
-- Msg 208, Level 16, State 1, Line 56
-- Invalid object name 'dbo.CaseTest_A'.

CREATE TABLE dbo.CaseTest_A (ID INT); -- success

EXEC sp_help 'sys.objects';
-- Collation for [name] = Latin1_General_100_CS_AS

ALTER DATABASE [ChangeCollationTest] COLLATE Latin1_General_100_CI_AS; -- error:
-- Msg 1505, Level 16, State 1, Line 23
-- The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for
--    the object name 'dbo.sysschobjs' and the index name 'nc1'. The duplicate key
--    value is (0, 1, CaseTest_A).
-- Msg 5072, Level 16, State 1, Line 23
-- ALTER DATABASE failed. The default collation of database 'ChangeCollationTest'
--    cannot be set to Latin1_General_100_CI_AS.


Incremental level 1 backup with no Incremental level 0? I have done the following steps:

Backed up database with INCREMENTAL LEVEL 0
Delete INCREMENTAL LEVEL 0 backup without telling controlfile. So deleted it by hand. And did not make crosscheck.
Backed up database with INCREMENTAL LEVEL 1(without making crosscheck).  How surprising it may be, log showed me that INCREMENTAL LEVEL 1 was successfully  done.

Note, I have no INCREMENTAL LEVEL 0 backup, but controlfile doesn't know it. I have just INCREMENTAL LEVEL 1.

I tried to restore database with this backup but of course couldn't. It was telling me that no backup of datafile 1-5 found...

If controlfile had known about EXPIRED backups , then INCREMENTAL LEVEL 1 backup first would do INCREMENTAL LEVEL 0 and after-that INCREMENTAL LEVEL 1.

How to avoid this situation. Do I have to use chrosscheck in my backup script? Or set recovery window in RMAN?

I have Oracle 11g database.
Backup is taken by Netbackup Server. So Netbackup deletes backups itself by its retention.
RMAN retention policy is configured to REDUNDANCY 2.

Please, Advice me.

Yes, if you want to prevent a level 1 from being created without a corresponding level 0 being available, then you need to do a crosscheck in the script so RMAN knows that the level 0 is not available.

Prior to 10g (or with compatibility < 10.0) Oracle would do a level 0 when a level 1 was done without a level 0.  Since you are on 11g, I would expect your compatibility to be >=10.0, so RMAN "copies all blocks that have been changed since the file was created".  This behavior seems to have changed between versions and there is some confusion as to what the current behavior is, but your findings are consistent with the documentation.


Mongodb users in replica 1) I have mongo instances A, B and C, each having a different set of users with different roles. I have used mongo shell to connect with A through an Admin user. Now, to create a replica and add B and C, do I need to pass through auth on B and C. Can you please help me with the commands?

2) I have mongo instances A, B and C, each having a different set of users with different roles. After I create a replica with A, B and C, what happens to the users of individual nodes. Are all the users from each node available in the replica?

So in your case, currently all the nodes(A, B and C) are running as standalone nodes with a different set of data.

Now you are trying to establish a replica with the existing nodes.

As per your question, first, you are trying to convert node A to a replica set member and then adding B and C to the replica set. So mostly, in this case, node A will become primary and B and C will be added as secondary. Since B and C are added as secondary for one time it will replicate all data from A. This one time process is called Initial Sync.

When Initial Sync is happening, mongod removes the contents of the dbPath directory of B and C node.

Please follow the steps in Deploy a Replica Set to set up the replica set.

Answering your second question, this procedure will delete all users and data from Node B and C.


Need listing of all relationships of tables SQL Server 2017 I have a ticket that I need to get a listing of all the relationships in one of my tables, I found that fairly easy here - I need to take it one more step - what my manager wants to see is all the tables that directly reference my main patient table, and all the ways those child tables reference all other tables.. Something like: Patient table -> address table then address table is linked to the shipping table, then the shipping table is linked to xx table... so its a huge spiderweb!  has anyone had to accomplish this before?

This will get all relations discoverable through FK constraints for a database:
USE <your database>
GO

SELECT
  ChildSchema.name AS ChildSchema
 ,Child.name AS ChildTable
 ,fk.name
 ,ParentSchema.name AS ParentSchema
 ,Parent.name AS ParentTable
FROM
 sys.foreign_keys FK
INNER JOIN
  sys.tables Child
    ON Child.object_id = FK.parent_object_id --Parent here means the "parent" of the FK, not the relation
INNER JOIN
  sys.schemas ChildSchema
    ON ChildSchema.schema_id = Child.schema_id
INNER JOIN
  sys.tables Parent
    ON Parent.object_id = FK.referenced_object_id
INNER JOIN
  sys.schemas ParentSchema
    ON ParentSchema.schema_id = Parent.schema_id
ORDER BY
  ChildSchema.name
 ,Child.name
GO


Query to fine "Order_num" that has no two equal "product_name" I have a table with below structure:
create table A_TEST
(
  order_num      int,
  product_name   varchar(50),
  product_amount int,
  product_price  int
);

Sample data :
insert into a_test (order_num , product_name , product_amount , product_price)
values (1,'APPLE',1000,2000),
       (1,'APPLE',2000,5000),
       (1,'APPLE',4000,3000),
       (2,'ORANGE',280,3000),
       (2,'APPLE',200,3000),
       (3,'BANANA',2000,3000),
       (3,'APPLE',200,3000),
       (4,'POTATO',123,3000),
       (4,'POTATO',2000,3000),
       (4,'APPLE',2000,3000),
       (5,'APPLE',360,3000),
       (5,'POTATO',2000,3000),
       (5,'TOMATO',360,3000),
       (5,'BANANA',123,3000),
       (5,'ORANGE',126,3000);

What I want is to find those order_nums that have different product_names so that no two product_name for one order_num should be the same . The desired result would be (2,3,5). I tried to solve this with Exists but it won't return the correct result.
SELECT *
FROM A_TEST A
WHERE EXISTS (SELECT *
              FROM A_TEST B
              WHERE A.order_num = B.order_num
              AND A.product_name <> B.product_name);

I know why this does not return the correct result. The reason is that if it finds at least one product_name which is different from another one, it will return it's order_num. I was wondering if you could suggest better solutions.
Thanks in advance

Classic solution:
SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) > 1;

Custom solutions:
SELECT DISTINCT t1.order_num
FROM a_test t1
JOIN a_test t2 ON t1.order_num = t2.order_num
WHERE t1.product_name != t2.product_name;

WITH cte AS ( SELECT order_num,
                     DENSE_RANK() OVER (PARTITION BY order_num ORDER BY product_name) dr
              FROM a_test )
SELECT DISTINCT order_num
FROM cte
WHERE dr = 2;

...
fiddle

The one that you used 'Join' , again does not return the desired result. It returns those records that have equal order_num but their product_name are different and does not return records with same order_num that their product_name s are the same . Just this – Pantea

Do you mean that you need only orders which (1) have at least 2 products, and (2) have none product twice? If so then add .. AND COUNT(DISTINCT product_name) = COUNT(product_name) to the first query.

SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) > 1
AND COUNT(DISTINCT product_name) = COUNT(product_name);

fiddle


how to explain indexes This question is about teaching:  When I learned databases, we talked about indexes being like the card catalog in the town library.  We had at least an author index, a title index, then the Dewey decimal number was the access path to the book, which is like the whole record in the database.

Books?  What, on paper?  Index cards in drawers?

The youngsters don't really know what I'm talking about, so how do we best explain it nowadays?

(Feel free to enjoy my lawn, just please recognize the difference between the grass and the astroturf at the prep school, ok?)

Best is to refer : Stairway to SQL Server Indexes

You leave your house to run a few errands.  When you return, you find a message from your daughter’s softball coach waiting for you.  Three of the girls, Tracy, Rebecca, and Amy have lost their team caps.  Could you please swing by the Athletic Products Store and buy caps for the girls.  Their parents will reimburse you at the next game.
You know the girls and you know their parents.  But you do not know their hat sizes.  Somewhere in your town are three residences, each containing a piece of information that you need.  No problem, you’ll just call the parents and get the hat sizes.  You reach for your phone, and you reach for an index – the white pages of your telephone directory.
The first residence that you need to reach is that of Helen Meyer.  Estimating that “Meyer” will be located near the middle of the population, you jump to the middle of the white pages; only to discover that you are at the page whose heading says “Kline-Koerber”.  You make a smaller jump forward and reach the “Nagle-Nyeong” page. One even smaller jump backwards puts you at the “Maldonado-Nagle” page.  Realizing that you are now at the correct page, you scan down the page till you reach the “Meyer, Helen” line and obtain the telephone number.  Using the phone number, you reach the Meyer residence and obtain the information you need.
You repeat the process two more times, reach two other residences, and obtain two more hat sizes.
You have just used an index, and you have used it in much the same way that SQL Server uses an index.

For more reading, you can refer to

SQL Server Index Basics
Kimberly's posts on Indexing
Expert Performance Indexing for SQL Server 2012.


Refactoring the query like this gives better performance (from ~7s to ~0.364s on my local):
http://pastebin.com/7VpLGdQB

EXPLAIN now shows this: http://pastebin.com/q8zEkXbx

Joining the node table twice was the bottleneck. The subquery with node and content_type_profile tables is much more efficient.

On the Drupal side I'll find a way to change this using hook_query_alter or by creating a custom block.


Can standby database of a physical database be pluggable or vice versa? We are currently planning to upgrade our databases from 11g to 12c. But I have some confusions regarding 12c. Here are my questions.

Can standby database of a non-container database be pluggable or vice versa?
Suppose there are 2 CDBs on 2 different server. And one has 3 primary PDBs, the other has 3 standby PDBs of the primary with dataguard configuration. If the switchover executed on one primary, will it execute switchover on other 2 PDBs?

Can standby database of a non-container database be pluggable or vice versa?

In order to plugin a non-CDB standby database into a CDB, the non-CDB standby database needs to be of version 12c as well. So existing non-CDB databases will need to be upgraded to 12c before they can be part of a 12c CDB.

  Suppose there are 2 CDBs on 2 different server. And one has 3 primary PDBs, the other has 3 standby PDBs of the primary with dataguard configuration. If the switchover executed on one primary, will it execute switchover on other 2 PDBs?

The dataguard switchover or failover operations are all performed at the CDB level. When the Data Guard is configured with the CDB database, all PDBs are also replicated to the target. Same way when a failover or switchover operation is performed all the PDBs also change role along with the CDB container.

As migration is a serious task I would recommend to take look at Oracle Support Knowledge based documents.(MOS account required)
Step by Step Examples of Migrating non-CDBs and PDBs Using ASM for File Storage (Doc ID 1576755.1)


Distributed database for worldwide LIDAR point cloud I have an idea for a project that would build a free worldwide 3D scan of the real world. You may think of it as a high-definition version of OpenStreetMap (OSM) project. The point cloud data would originate in LIDAR or stereo camera 3D scans. I am thinking of possible database solution for the project.

The main problem would be storing trillions of data primitives (point cloud points). Each point would have few properties (coords - lon, lat, elevation; intensity, RGB values, some semantic tags). I think the scenario is mostly similar to OSM but with way more data (easily 1000 times the OSM data but most likely much more).

Real spatial features are not neededn it the database and only a queries by location (with the help of geohash or b-tree indexes?) are required.

OSM uses plain Postgres (not PostGIS) because they do not need any geometry operations on top of their nodes. Today (October 2016) the OSM project contains 3.5 billion of nodes in a plain Postgres db table that runs on a single server. The whole database (including ways, relations, editing history etc.) has a size of about 6 TB (see server hardware).

Since I need 1000x the scale of OSM it is clear that many servers will be needed. Can someone help to at least roughly outline how the system may look like? I think data fragmentation may be done geographically by dividing Earth's surface into squares and have one table_points (=one database?) per such patch - lets call these "slave servers". There would be a central "master server" (not a db) that would manage queries for slaves (in case data from multiple slaves would be needed) and handle data returns to clients. Would such model be viable?

I can think of some very sophisticated fully distributed database system but I think at the beginning something very simple like simple Postgres slaves (maybe with few read-only replicas for faster reads) could be sufficient for the basic functionality.

Note: I found the pointcloud extension for Postgres (explanatory video here). It seems to group points into "patches" to minimize table row count. Would this make DB faster?

EDIT: I am also exploring distributed NoSQL databases like Cassandra or CouchDB. Also thinking about a DIY solution with people having nodes at home rather than using a datacentre (mostly due to financial reasons - datacentre needs to be paid). But with a "flat worldwide NoSQL" (read: data not fragmented by geo coords) I am not sure how queries would work (speed etc.).

I have dealt with something very similar to this. This is quite a broad problem but I'll try to offer the pointers I can after a year (and then the rest) of dealing with massive geographic data sets.

I'm not saying "do these", I'm saying these ideas will be resourceful depending on your hardware situation and software needs. You clearly already have good ideas about distributing out the data, that's good.

Firstly, I'd make sure not to have monolithic tables with too many sets of datatypes. Normalise out - I've found that splitting your geospatial columns into an entirely separate table.

Secondly, write down the sort of queries you'd expect a typical client to need. There's a 'chicken and egg' situation here, but I always like to write the SQL then design the table and indexes around that. Rick James' blog (former DBA at Yahoo!) always has nuggets of wisdom on this.

Thirdly, shard by location, which you have already touched upon. You can try doing this by 5-10 degree boxes around the world by latitude and longitude, depending on how dense your data is. Or if your data density is not uniform around the world, try a more sophisticated approach based on densities.

Our approach to sharding involved creating multiple Amazon RDSs, and then having a meta data table that mapped a range to the correct database and table. This is a simpler approach if you have the cash - personally I was against this due to the spiralling costs, but the uptime of AWS is top-notch. Something for you to think about.

Good luck, sounds like a cool project!


Unable to connect to pluggable database using TNS but EZCONNECT is OK I installed oracle 12c pn windows following the defaults settings... I end up with ORCL and ORCLPDB...

I can connect to ORCLPDB using EZCONNECT via CONN system/password@//localhost:1521/ORCLPDB . Then I added a service name in my tnsnames.ora like this :

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

But when I try connect system/password@ORCLPDB I get the following error :

  ORA-12154: TNS:could not resolve the connect identifier specified

This is strange because lsnrctl services has the following entry :

  Service "orclpdb" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
      Handler(s):
        "DEDICATED" established:0 refused:0 state:ready
           LOCAL SERVER

What should I do ?

The problem is the TNS name which contains a leading whitespace.

For example:

[Whitespace]ORCLPDB =
  (DESCRIPTION =
...

As we know, the @ in connection string should immediately followed by the TNS name we can't have whitespace in front of a TNS name.


Safe to Restore Database from Untrusted Sources I am asking someone to aggregate around 100GB of data for me. I would prefer this to be in a Postgres database instead of something like CSV. I was thinking pg_dump and pg_restore custom format. Is it safe to restore the database onto my machine? Can they inject something malicious in there?

Is it safe to restore the database onto my machine?

Quite safe if you restore using a non-superuser account. CREATE USER a user for the purpose, CREATE DATABASE ... WITH OWNER ... a db owned by that user, and pg_restore using that user into that database. I'd do it happily enough. The custom format is basically SQL snippets and CSV-like row data in an archive.

To be more careful you can request CSV files and use COPY or psql's \copy to load the CSV files into the database. That's exceedingly safe.

  Can they inject something malicious in there?

Yes, if you restore it using a superuser account like the postgres user. They could do something nefarious like create a plperlu, plpythonu, etc procedure (if you have those languages available in your install) that then runs arbitrary code as the postgres user. It probably can't get outside the postgres operating system user and attack the rest of the system. Probably. But local root exploits aren't as hard to find as they ideally would be.

On Windows you're less likely to have plperl and plpython enabled, but you're likely to run PostgreSQL under NETWORKSERVICE, which is quite privileged. So again, I wouldn't, just in case. They might be able to do something nasty with lo_import and lo_export for example.

In general PostgreSQL tries to make it less than entirely trivial for the database superuser to gain shell access as the user it's running as, at least if no procedural languages other than plpgsql are installed. It's not a strong sandbox though, and it's not at all surprising if you can break out of it and get shell access from the postgres superuser.

Shell access, running code as superuser, etc, from non-superuser accounts should not be possible under any circumstances, though, barring gross misconfiguration like creating a SECURITY DEFINER procedure that runs arbitrary SQL as the superuser.


How to identify the ranges over which a postgres table was partitioned? If I create a table and partitions like this...

CREATE TABLE tab1 (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE tab1_p1 PARTITION OF tab1 FOR VALUES FROM (0) TO (100);
CREATE TABLE tab1_p2 PARTITION OF tab1 FOR VALUES FROM (100) TO (200);

how can I subsequently check the ranges? I've tried browsing the information_schema.tables and information_schema.table_constraints but no luck so far.

You need to look into pg_class. You can use pg_get_expr() to get a readable expression for the partition bounds:

select pg_get_expr(c.relpartbound, c.oid, true)
from pg_class c
where relname = 'tab1_p1';

If you want to see that for all partitions (and sub-partitions) of one table, you can use the following:

select pt.relname as partition_name,
       pg_get_expr(pt.relpartbound, pt.oid, true) as partition_expression
from pg_class base_tb
  join pg_inherits i on i.inhparent = base_tb.oid
  join pg_class pt on pt.oid = i.inhrelid
where base_tb.oid = 'public.tab1'::regclass;


Postgres repmgr - downstream nodes not attached I'd configured repmgr replication on node1 and node3 (primary and standby respectively), and the setup worked sucessfully creating new records and objects on standby as expected.
But after some weeks I'd noticed that replication wasn't working anymore, however some repmgr commands are returning results as the replication are working.
I tried to restart and register again the standby node, but it doesn't worked.

How can I continue to replicate?

Here's status of nodes:

-bash-4.2$ psql -V
psql (PostgreSQL) 10.3

NODE1 - PRIMARY

-bash-4.2$ repmgr node check
Node "node1":
    Server role: OK (node is primary)
    Replication lag: OK (N/A - node is primary)
    WAL archiving: OK (0 pending archive ready files)
    Downstream servers: OK (this node has no downstream nodes)
    Replication slots: OK (node has no replication slots)
-bash-4.2$

NODE3 - STANDBY

-bash-4.2$ repmgr -f /etc/repmgr/10/repmgr.conf node check
Node "node3":
    Server role: OK (node is standby)
    Replication lag: OK (0 seconds)
    WAL archiving: OK (0 pending archive ready files)
    Downstream servers: CRITICAL (1 of 1 downstream nodes not attached; missing: node3 (ID: 3))
    Replication slots: OK (node has no replication slots)

-bash-4.2$ repmgr node status
Node "node3":
    PostgreSQL version: 10.3
    Total data size: 2393 MB
    Conninfo: host=node3 user=repmgr dbname=repmgr connect_timeout=2
    Role: standby
    WAL archiving: disabled (on standbys "archive_mode" must be set to "always" to be effective)
    Archive command: /bin/true
    WALs pending archiving: 0 pending files
    Replication connections: 0 (of maximal 10)
    Replication slots: 0 (of maximal 10)
    Upstream node: node3 (ID: 3)
    Replication lag: 0 seconds
    Last received LSN: 4/AC000000
    Last replayed LSN: 4/AC000140

Some needed wal files to replicate wasn't found on primary.
Then I reinstated the standby cloning it again.

Commands submitted on standby server:

   pg_ctl stop
   repmgr -f /etc/repmgr/10/repmgr.conf --force --rsync-only  -h node1 -d repmgr -U repmgr --verbose standby clone
   pg_ctl start
   repmgr node status
   repmgr node check


Maintenance Plan Job running at additional unscheduled times? I have a situation that has left me really scratching my head. This is a SQL Server 2008r2 instance, used as the database server for a substantial corporate SharePoint service. As my company is providing the DB support for it, one of the things I did was to setup some maintenance plans to take care of the usual things, including nightly full backups of all of the databases at 7PM.

This all works fine, except that it has occasionally also been running sometime in the early morning.

So far this has happened three times, once 11 days ago at 2:47AM, once 4 days ago at 3:43AM and once 2 days ago at 3:49AM. I can find no reason why it should be running at these sporadic random times.

This is causing significant problems for us, as the early morning is when the server hosting provider runs their backup and maintenance jobs. Their execution takes many times longer than normal and are sending out alerts and other notices for this, long IO times and various errors that appear to be because of conflicts with the host SAN/NAS mirroring and backups.

To summarize what I have already checked:

This is the SQL Agent Job running an extra unscheduled time, and NOT just the maintenance plan running twice within the same job.
This does not appear to be to be a VM clone of this node with a SQL Agent running a duplicate of this job against the real SQL Server. I conclude this because the executions are showing up in our SQL Agent's log. (if that's not definitive, let me know how to check it)
This is not a case of the Job just running over for a long time. The regular job runs and completes at the expected scheduled time every day (7PM).
Neither the Server, nor the SQL Server instance have been restarted during the last month.
The SQL Server Job in question has only one schedule, that is set for 7PM every day.
The Maintenance Plan has only one subplan, that shows exactly the same schedule as the Job does.

Any help greatly appreciated.

At Aaron's suggestion, I have added the following Execute SQL as the first task in the sub-plan:

IF (DATEPART(HOUR, GETDATE()) > 2) AND (DATEPART(HOUR, GETDATE()) < 6)
    RAISERROR('Job Running at the Wrong Time!', 16, 1) WITH LOG

Well, mystery solved.

I had previously posted this at another site (SqlServerCentral.com), and then posted here after I had not gotten a response for several hours.  While I was trying Aaron's suggestion here, a Josh posted the following there:

  Did you check the history of the job to see how it was invoked?
  Usually you'll see something like "The job succeeded. The Job was
  invoked by Schedule XXXX (Schedule_X ). The last step to run was step
  1 (XXXXXXXXXXXXXX)."

  Does it look the same for each run?

Though I had previously scoured the Job's history, I had not specifically looked for that message. And lo and behold, the unscheduled ones have the following:

... The Job was invoked by Alert 1.  ...

So someone must have tagged this job to run after an Alert (likely the wrong job).

Naturally now I feel silly for not checking this before.  But I post this here now, so that others in the future may benefit from my oversight.


How can you monitor if MySQL binlog files get corrupted? I was wondering if there was any nice way to continuously monitor mysql master binlog files and detect if get corrupted.

There are four(4) things to look for to see if a binlog is corrupt on not:

Binlog Magic Number
Binlog Size
mysqlbinlog
MySQL Replication

BINLOG MAGIC NUMBER

Back on Dec 26, 2011, I wrote about a based-64 number that sits at the top of any binlog (hex value 0xfe 0x62 0x69 0x6e) regardless of MySQL version and regardless of binlog format (STATEMENT,ROW,MIXED). If this hex value is not in the header of the binlog, it is definitely corrupt.

BINLOG FILESIZE

For any binlog position, there is a a corresponding filesize. In other words, whenever mysqld records a SQL command, it does the following:

retrieves the current filesize to the statement size
calculates the size of the statement it will record (comments included)

Comment contains filesize + statement size
This sum is designated the binlog position

Statement is appended to the binlog
Comments are appended to the binlog

In light of this, you can run SHOW MASTER STATUS; and get something like this:

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000109 | 569025336 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

If there are no writes going on (low writes,high reads), you could compare the filesize of the binlog with the reported binlog position.

Get both values and compare.

If the binlogs are in /var/lib/mysql

BINLOG_INFO=/tmp/binlog.txt
mysql -u... -p... -ANe"show master status" > ${BINLOG_INFO}
BINLOG_FIL=`cat ${BINLOG_INFO} | awk '{print $1}'`
BINLOG_POS=`cat ${BINLOG_INFO} | awk '{print $2}'`
cd /var/lib/mysql
BINLOG_SIZ=`ls -l ${BINLOG_FIL} | awk '{print $5}'`

If there are no writes going on, BINLOG_SIZ should be equal to BINLOG_POS

If there are writes going on, BINLOG_SIZ should be greater or equal to BINLOG_POS

You should never get BINLOG_SIZ greater than BINLOG_POS. If you do, binlog must be corrupt.

mysqlbinlog

The mysqlbinlog utility will dump a text representation of the binlog. If any of the output is garbled, then it it corrupt. However, don't jump to any conclusions if you see this:

BINLOG '
WFxKTRNJAAAAPwAAAKY/YwAAABsAAAAAAAEACHdlYmVkaTMwAA1QYXJ0bmVyQ29uZmlnAAQICA8P
BC0AYwAA
WFxKTRhJAAAAXAAAAAJAYwAAABsAAAAAAAEABP//8BoSAAAAAAAAggMAAAAAAAAJUEFDS0NPVU5U
Azc3MfAaEgAAAAAAAIIDAAAAAAAACVBBQ0tDT1VOVAM3NzI=
'/*!*/;

That is actually a normal signature at the beginning of a mysqlbinlog output. I wrote about this back on Feb 04, 2011 : MySQL master binlog corruption. This is the only exception. Any garbled output from mysqlbinlog that does not look like this is a definite sign of binlog corruption.

MySQL Replication

This is a double-edged sword because of how replication works. Nevertheless, it can still be helpful in two aspects. Keep in mind that a relay log has the exact same characteristics as a binary log.

On a Slave, the IO Thread would parse the incoming binlog entries from the Master and record them in the Slave's current relay log. It is actually running a process like mysqlbinlog. Thus, the mechanism used by the IO Thread is simple:

Read the Comments above the Statement
Get the Expected Position from the Comments
Read the SQL Statement

Subtract Previous Read Position from Last Read Position
This Determines How Many Bytes to Read to Get the SQL Statement

Append Comments and SQL Statement into the Current Relay Log adjusted by Relay Log Size

If Step 3 ever fails, this means the IO thread could not parse in the next incoming SQL.

The Slave will reveal this failure by writing in its error log something like this:

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

The fact that error number 1236 is a registered error condition shows that this impossible position scenario can happen upon failure to successfully read incoming SQL. I wrote about this on Oct 20, 2011 : How to handle the My SQL DB Master to Master replication when there was a disk space outage

The Slave knows what position it needed from the header (Binlog Comments) it read before expecting the incoming SQL. There are two causes for this:

CAUSE #1

If there was any network transmission noise or dropped packets, that slight "burp" interrupts the reading of the SQL statement. Thus, it is possible for the header comments to be written and not the SQL that follows.

CAUSE #2

If the binlog on the Master was corrupt to begin with, the IO thread on the Slave will fail for a more obvious reason: GARBAGE IN, GARBAGE OUT !!!

WORKAROUND

The error message example says Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.. Here is how you can tell which CAUSE happened. Run the following on the Slave:

STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE=''mysql-bin.001067'',
MASTER_LOG_POS=183468345;
START SLAVE;
SELECT SLEEP(10);
SHOW SLAVE STATUS\G

When you run SHOW SLAVE STATUS\G, if you see

       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

replication is fine. It was CAUSE #1

but if you see

       Slave_IO_Running: No
      Slave_SQL_Running: Yes

BANG !!!...The master binlog was corrupt anyway (CAUSE #2)

You would need to mysqldump the Master, load it onto the Slave, and setup replication from scratch.


How to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL? Solution in MS SQL

MS SQL trigger functions have deleted and inserted system tables in which all rows affected by operation are stored. You can count updated rows:

set @updatedCount = (select count(*) from deleted)

or find out minimum value:

set @updatedMinimumCol1 = (select min(col1) from deleted)

Problem with PostgreSQL

For FOR EACH ROW triggers I can use OLD and NEW system records, but they store only 1 row for each call of trigger. Calls of trigger are separated, so if user updates 10 rows, trigger will be called 10 times, but each time I can know only about 1 current row, not about all 10 rows.

For FOR EACH STATEMENT I do not know any mechanism of access to updated rows at all. I use PostgreSQL v9.6, OLD TABLE and NEW TABLE were introduced in v10.

  PostgreSQL does not allow the old and new tables to be referenced in statement-level triggers, i.e., the tables that contain all the old and/or new rows, which are referred to by the OLD TABLE and NEW TABLE clauses in the SQL standard.

Try with transaction_timestamp() additional column

I can add special column with DEFAULT transaction_timestamp() to the main table and then use it to distinguish just-now-updated rows from other, but it is not a solution since multiple INSERTs/UPDATEs can be in one transaction and they will have the same timestamp of transaction. Probably I could clear this timestamp column in trigger after each statement to avoid this problem, but how to do this if such clearing will emit update trigger again - will be infinite update trigger calling.

So, this try was failed.

Bad solution in PostgreSQL

The only way I know is that:

First, use FOR EACH ROW trigger to collect current statistics (min and count) like aggregate functions. I use temp table to store it between calls (this trigger gets called 1 time for each row). But we will not know which row is last (when time will come to use this statistics).

CREATE TEMP TABLE IF NOT EXISTS _stats (
  _current_min   int,
  _current_count int
) ON COMMIT DROP;

IF EXISTS(SELECT 1 FROM _stats LIMIT 1) THEN
  --Current row is not first, there is statistics for previous rows.
  UPDATE _stats
  SET _current_min   = (CASE WHEN NEW.col1 < _current_min THEN NEW.col1
                        ELSE _current_min END)
    , _current_count = _current_count + 1;
ELSE
  --There is no stats because current row is first for this INSERT/UPDATE
  INSERT INTO _stats (_current_min, _current_count)
  VALUES (NEW.col1, 1);
END IF;

Second, use FOR EACH STATEMENT trigger to use collected statistics. Do not forget to clear temp table (if user will run multiple INSERTs/UPDATEs in one transaction, old statistics will remain in temp table and corrupt all next calculations!).

For more complex tasks we can create temp tables inserted and deleted on the same way as _stats.

The workaround

In PostgreSQL we can use RETURNING clause for INSERT/UPDATE/DELETE to obtain new values of all rows affected by operation. Then we can manipulate with them, but each function with INSERTs/UPDATEs have to implement this technology ===> 1. additional code in functions with such INSERTs/UPDATEs - duplication of the RETURNING; 2. we can forget to implement such technology to new function; 3. data will be corrupted since required manipulations will not be called automatically (like triggers will).

The question

Maybe, you know a better way to access all rows affected by INSERT/UPDATE?

See the docs, you should be able to access the old and new records from a statement trigger:

CREATE TRIGGER some_table_update_trigger
  AFTER UPDATE ON some_table
  REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
  FOR EACH STATEMENT
  EXECUTE PROCEDURE do_something_with_newtab_and_oldtab();


Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key Some of my newer tables are now using UUID type fields as PKey and older tables are still using bigserial. I have several tables (let's call them entities) I would like to query returning the primary key in the result (using a UNION). What is the most efficient way to return the entity_id's? I am assuming a simple CAST would do the trick, but this is to handle a sitewide search which requires it to be as quick as possible.

    select entityA.a_id::TEXT as entity_id from entityA
    union
    select entityB.b_uuid::TEXT as entity_id from entityB
    union
    select entityC.c_id::TEXT as entity_id from entityC
    union
    select entityD.d_id::TEXT as entity_id from entityD
    union
    select EntityE.e_id::Text as entity_id from EntityE

Any recommendations?

Casting to text should not add much overhead, however you may want to consider using UNION ALL instead of UNION if you're looking for performance (UNION attempts to filtering duplicates).


Database design and Normalization I have a question regarding database table design.

I have a product master table which has all the details of a product.

User would be adding notes against each product (product number) and for each product there would be multiple notes.

Proposed columns for notes against product.

Subject
Description
Entered BY
Updated By
Entered Date
Updated Date.

Should I create two tables, one to keep the product no and another table to keep the notes and the other columns or one table to keep the product no and other columns?

Two tables, Product and Product Notes. Link them by ProductId, primary key of your Product table.


Putting foreign keys of several tables into the same column I've never come across this situation, not sure how to go about it.

Let's say I have a table Portfolio. I also have two tables: Asset 1, and Asset 2. I need to create a bridge between Portfolio and the Asset tables. A portfolio can have either Asset 1, or Asset 2, or both, or none.

Can I do something like the bridge table in the image above, i.e. where the bridge table (example below) would contain in the same column, the foreign keys to two different tables

-- Asset AST of asset type ATYP exists.
--
asset {AST, ATYP}
   PK {AST}
   SK {AST, ATYP}

CHECK ( ATYP in ('A', 'B') )

-- Asset AST, of asset type "A" exists.
--
asset_a {AST, ATYP}
     PK {AST}

FK {AST, ATYP} REFERENCES asset {AST, ATYP}

CHECK (ATYP = 'A')

-- Asset AST, of asset type "B" exists.
--
asset_b {AST, ATYP}
     PK {AST}

FK {AST, ATYP} REFERENCES asset {AST, ATYP}

CHECK (ATYP = 'B')

-- Portfolio PTF exists.
--
portfolio {PTF}
       PK {PTF}

-- Portfolio PTF contains asset AST.
--
portfolio_asset {PTF, AST}
             PK {PTF, AST}

FK1 {PTF} REFERENCES portfolio {PTF}
FK2 {AST} REFERENCES asset     {AST}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key


Suppress database change message in SQLCMD.exe I am writing a script generation script.
The script is generating list of DDL statments.

The util_generator.sql generation starts with database connection:

USE [TARGET_DATABASE]
GO
...
Many queries
Followed by print statements
...

Since this is a very long script, with unknown length.
We use SQLCMD.exe

sqlcmd -s sa\password -i util_generator.sql -r0 -o util_1.sql

The generated scripts util_1.sql looks something like this:

Changed database context to 'TARGET_DATABASE'.
CREATE TABLE ...
CREATE INDEX ...
CREATE VIEW ...

The problem is the generated script util_1.sql fails on unknown command Changed database context to 'TARGET_DATABASE'.

I need assistance to suppress Changed database context to 'TARGET_DATABASE'. message from SQLCMD.exe

Using SSMS util_generator.sql works good not producing any Changed database context to 'TARGET_DATABASE'. message.

But we cannot run util_generator.sql on SSMS from CLI for unknown length output scripts.

Please asvise how to suppress Changed database context to 'TARGET_DATABASE'. statement in SQLCMD.exe

You can suppress (error) messages with a severity level lower than 10. Add -m10 as  startup option.

to the best of my knowledge, there is no direct way for SQLCMD to suppress that particular message. But perhaps suppressing info-type messages (as per above) will cut it?

Or, of course, use the -d switch as suggested by Dudi.


Diff in Transactional and Non-transactional tables What is the difference between Transactional and Non-transactional tables? And How will the sql query go if I want to create them?

Also when to use transactional or non-transactional tables?.

Transactional table means, if data manipulation done with in transaction then rollback / commit will work.

For Non Transactional table, You need to rollback the changes with manual code. No Impact of rollBack and commit. These tables are useful for performing the statements with high performance.

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.

If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB Cluster), you can disable autocommit mode with the following statement:

SET AUTOCOMMIT=0;


Open Source Multi-master replication ideas? I have multiple geographically separated data centres on a private network and would like some ideas for WAN outage tolerant open-source multi-master replication databases. I'm OK with RAM only dbs.

I need to be able to write to a common table on the local master at each data centre and have that information change in all versions of that table in all the data centres.

During a WAN outage I need to be able to continue to write to this common table at each data centre locally. When WAN connectivity resumes I need it to automatically merge all the changes together with some kind of conflict resolution.

Handling latency degregation would be an optional plus.

Is MySQL Cluster Multi-Site Clustering what I'm looking for? I find it hard to figure it out from their documentation.

I don't need an SQL interface or MySQL specifically, simple key:value would work too.

I really hope some more knowledgeable people could point me in the right direction!

I favor Galera clustering (see Percona's PXC or MariaDB).  Put 1 node in each of 3 different datacenters.  (One node could be a trivial 'arbitrator' if you need to cut corners.)

Galera requires the use of InnoDB, but that is reasonably efficient.

If a node goes down, or is separated from the other two nodes due to a network outage, the system hums along, with the restriction that you can't write to the dead/isolated node.  When if comes back up (or is replaced/repaired), the cluster will repair it automagically.

If no node can talk to any other node (which seems extreme), manual intervention will be required.  (Actually, if more that 50% of the nodes are communicating with each other, the "system" is still alive and usable.)

Transactions are synchronous (read: latency), but there in only one round trip per transaction.

NDB Cluster (which is perhaps what you are referring to) is a distinctly different animal.  It uses an "eventual consistency" for dealing with replication.  This is not at all related to the rest of MySQL replication options.

Key:value can be implemented easily on top of InnoDB or NDB cluster.


SQL Server DOUBLE keyword There is a reserved keyword DOUBLE in SQL Server, but I know that it's not a data type (other languages' double is float or real in SQL Server); does anyone know why this is a keyword?

I've tried to use it in SQL Server 2008 and SQL Server 2012 and I cannot use it as a data type nor a function and I cannot find any info in MSDN pages; only that it is a reserved keyword and it isn't a data type.

This is actually part of the DOUBLE PRECISION type, synonym of float(53).

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


How can I get Elastic IP/host from KIbana interface Through Kibana interface, how can I get Elastic IP/host?
I mean, the Elastic host which kibana is connected to.
Is that even possible? i'm struggling on that for hours and I got nothing :(

Kibana always connects to the elastic host that is mentioned within kibana.yml file that been used during installation/configuration (example value within file as follows):

elasticsearch.hosts: ["http://localhost:9200"]

In case if it configure to points to multiple elastic nodes for high availability of kibana (as follows). Requests to available hosts will be routed in a round robin fashion (top to the bottom of a list and then starting again)

elasticsearch.hosts:
 - http://es-host1:9200
 - http://es-host2:9200

To know available nodes, you may use nodes API from Kibana - Dev tools

GET /_nodes

# or Use following
GET /_cat/nodes?v


configure client ibm_db2 for php I am installing a RHEL server with nginx and php that can connect to a db2 database, download and install the "IBM Data Server Client" package, also install the pecl library through:

  pecl install ibm_db2

however when I do a connectivity test me responds with the following error:

  [IBM] [CLI Driver] SQL10007N Message "0" could not be retrieved.
  Reason code: "3". SQLCODE = -1390

the code that I'm testing is:

<?php
$database = 'mydb';
$user = 'usr';
$password = 'pass';
$hostname = 'ipaddr';
$port = 9501;

$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;" . "HOSTNAME=$hostname;PORT=$port;PROTOCOL=TCPIP;UID=$user;PWD=$password;";
$conn = db2_connect($conn_string, '', '');

if ($conn) {
    echo "Connection succeeded.";
    db2_close($conn);
}
else {
    echo db2_conn_error();
    echo db2_conn_errormsg();
}

am I missing something?

Edit:

searching in the web I found in phpe-ditors this:

  If you created a DB2 instance named db2inst1 in /home/db2inst1/, for
  example, you can add the following line to php.ini:

  ibm_db2.instance_name=db2inst1

  If you do not set this option in php.ini, you must issue the following
  command to modify your environment variables to enable access to DB2:

  bash$ source /home/db2inst1/sqllib/db2profile

  To enable your PHP-enabled Web server to access these functions, you
  must either set the ibm_db2.instance_name configuration option in
  php.ini, or source the DB2 instance environment in your Web server
  start script (typically /etc/init.d/httpd or /etc/init.d/apache).

The problem is that I don't know whats the value for ibm_db2.instance_name

Any idea ?

With the help of @mustaccio achieved do as follows:

First I added the user db2inst1:

  db2inst1 useradd

Then, I generated the instance (whatever it means)

  /opt/ibm/db2/V10.5/instance/db2icrt db2inst1

Edit the php configuration file (/etc/php.ini) for add the instance:

 extension = / usr/lib64/php/modules/ibm_db2.so
 ibm_db2.instance_name = db2inst1

Edit the initialization file of the php-fpm service (/etc/init.d/php-fpm), which in my case is the one who runs php, to do the source of the db2profile script under the user running this service each time it starts.

 #for DB2
 . /opt/ibm/db2/V10.5/cfg/db2profile

And finally restart the service:

  php-fpm restart service

Thank you very much for the comments.


Case when not return more than one value I try this, but the SwitchCase can not return more than one value.

WHERE CP.NumIntAut IN (
    CASE WHEN @NumIntAut IS NOT NULL
        THEN (@NumIntAut)
        ELSE (SELECT NumIntAut FROM @TAB_NumIntAut) END)

How can I do?

I think you are looking to do something like this

WHERE CP.NumIntAut IN (
                       SELECT  CASE WHEN @NumIntAut IS NOT NULL
                                    THEN @NumIntAut
                                    ELSE NumIntAut END
                                    FROM @TAB_NumIntAut )

Assuming that I understand your question correctly.  But it is worth noting that it will return the value @NumIntAut for every record in the @TAB_NumIntAut table if it not NULL. Which may or may not be what you are looking for.


What is the best way to upgrade a MySQL version in a Linux Server I have database around 14 GB running with 8 -15 GB ram(diffrent deployments) .At present MySQL version is 5.5 and I’m planning to move to 5.6 . I did similar activity in past for 5.1 to 5.5 upgrade .

Steps I followed is 1. Resolve any syntactical issue that can be faced in upgrader version of Mysql in code base prior to upgrade.

at the day of upgrade 2. Shut down the MySQL with application; take two backups (one with data and without data) 3. Uninstall and remove old MySQL instance and install new MySQL instance. 4. Import backup without data first and with data second and go live again.

I experience that during above process, it took very long time for importing backup file to database as some of our production servers run with less memory and during this remotely increase the risk as well. This is the only best way I know, is there a best approach I can fallow to upgrade MySQL version in future with very less time . Appreciate your help . Please note that I do not have facility to get another machine to do this, so I have to do in same production server where justifiable downtime is acceptable.

I found the question relevant, however it is answered before elsewhere

You want to turn off you database (take the backup, but only for the reason of having a backup)
Go to you config file (.ini), in there locate the datadir
take a copy of this directory, or make sure it is not removed when reinstalling
Reinstall, and check if everything works, make sure the datadir is the same

This is how i would do it, but i haven't tested this, so you might bump into issues

(this idea/theory is backup up by this guy) http://dacosta9.wordpress.com/2008/10/25/moving-data-files-to-another-disklocation-mysql-sql-server/

And from Server fault
https://serverfault.com/questions/68146


Display the choices even if no rows found in associated table I have the following 3 tables for a small voting/polling feature:

CREATE TABLE IF NOT EXISTS `options` (
  `option_id` tinyint(4) unsigned NOT NULL,
  `poll_id` mediumint(8) unsigned NOT NULL,
  `option` tinytext NOT NULL,
  UNIQUE KEY `option_id_poll_id` (`option_id`,`poll_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Storing poll options/choices';

CREATE TABLE IF NOT EXISTS `questions` (
  `poll_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `question` tinytext NOT NULL,
  `nick` varchar(32) NOT NULL,
  `dated` datetime NOT NULL,
  PRIMARY KEY (`poll_id`),
  KEY `deleted` (`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table to store poll questions/titles';

CREATE TABLE IF NOT EXISTS `votes` (
  `poll_id` mediumint(8) unsigned NOT NULL,
  `option_id` tinyint(4) unsigned NOT NULL,
  `nick` varchar(32) NOT NULL,
  `dated` datetime NOT NULL,
  UNIQUE KEY `poll_id_nick` (`poll_id`,`nick`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and I'm fetching the results of a certain poll_id (represented with %d below) using the following query:

SELECT
    o.option_id AS option_id,
    o.`option` AS `option`,
    COUNT(v.nick) AS total
FROM options o
LEFT JOIN votes v
    USING (poll_id)
WHERE o.poll_id = %d
    AND v.option_id = o.option_id
ORDER BY total DESC, o.option_id ASC

A sample can be seen on sqlfiddle. I'd like to retrieve all the options/choices available for any poll_id and display the COUNT as 0, or NULL; if no user chose that particular option. Thus, for the fiddle example, the result would be:

option_id | option | total
1         |   A    |   3
2         |   B    |   1
3         |   C    |   0

http://sqlfiddle.com/#!9/2a073/6 - "v.option_id = o.option_id" is supposed to be part of join condition so use ON instead of USING and move it there - when it is in WHERE, it works "after" the LEFT of join, so for not existing matches it is never true. And the group by per @ypercube is needed too.


Get position of mysqlbinlog error. I have the error's line number If I use mysqlbinlog to load a binlog into my database like this:

mysqlbinlog --defaults-file=/path/mysqld.cnf bin.000011| mysql -u root

and get an error like this:

ERROR 2006 at line 290: MySQL server has gone away

How do I convert line 290 into a binlog position that I can use to rerun the log, starting at the failed line?

 mysqlbinlog --defaults-file=/path/mysqld.cnf --start-position=<WANT THIS> bin.000011| mysql -u root

I'm using binlog-format=ROW

Or, how can I log the binlog position of the error, and not just the line number?

It seems that there must be a way to recover from an error while updating a database with mysqlbinlog, otherwise the whole database is corrupted.

You can follow this steps/workaround :

Take converted binary logs in separate file.

mysqlbinlog --base64-output=DECODE-ROWS -v hostname_binlog.00XXX > Binary_log.sql

You directly Binary_log.sql file on MySQL server or you can use mysqlbinlog command ( if you have sufficient space)

mysqlbinlog --base64-output=DECODE-ROWS -v hostname_binlog.00XXX | mysql -v -u root

Here using -v ( verbose ), logs will printed on terminal & you can get details of each statement.


Problem with a partitioning switch script Working on some dynamic SQL to create a 13 month rolling partitioned table.  See here for some background info.

The dynamic SQL part is simple enough, and the first partition switches out without a problem.  Unfortunately when I get to the second partition I start getting this error:

Msg 4972, Level 16, State 1, Line 16
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'DBName.dbo.TableName_Partition2' allows values that are not allowed by check constraints or partition function on target table 'DBName.dbo.TableName'.

I'm sure it's something simple but I can't quite figure it out.  Here is the code.

-- Existing structure
USE [SourceDB]
GO

CREATE TABLE [dbo].[SourceTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
);

CREATE CLUSTERED INDEX [SourceTable_Modified] ON [dbo].[SourceTable]
(   [Modified] ASC   )
GO

USE [DestinationDB]
GO

CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](datetime) AS
 RANGE RIGHT FOR VALUES
    (   N'2015.08.01',
        N'2015.09.01',
        N'2015.10.01',
        N'2015.11.01',
        N'2015.12.01',
        N'2016.01.01',
        N'2016.02.01',
        N'2016.03.01',
        N'2016.04.01',
        N'2016.05.01',
        N'2016.06.01',
        N'2016.07.01',
        N'2016.08.01'
 );

CREATE PARTITION SCHEME [ThirteenMonthPartScheme]
    AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY]);

CREATE TABLE [dbo].[DestinationTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified);
GO

CREATE CLUSTERED INDEX [DestinationTable_Modified] ON [dbo].[DestinationTable]
(
    [Modified] ASC
) ON ThirteenMonthPartScheme (Modified);
GO

SELECT *
    INTO [TableName_Partition2]
FROM SourceDB.dbo.[SourceTable]
WHERE Modified >= N'2015.08.01'
  AND Modified < N'2015.09.01';
GO

CREATE CLUSTERED INDEX [TableName_Modified]
    ON [dbo].[TableName_Partition2] ([Modified] ASC);
GO

ALTER TABLE [dbo].[TableName_Partition2]
    ADD CONSTRAINT [CK_TableName_Partition2]
    CHECK ([Modified] >= N'2015.08.01'
      AND  [Modified] < N'2015.09.01');
GO

ALTER TABLE [TableName_Partition2]
    SWITCH TO [DestinationTable] PARTITION 2;
GO

DROP TABLE TableName_Partition2;
GO

I've tried each partition number, I've tried reducing the time range to a single date (ie modified = '2015.09.02') but still get the same error.  I'm stumped.

Thanks for any help.

@wBob got it in one. It's all about those NULLs.

The following script creates the Destination table with a NOT NULL modified column and it alters the same column on the TableName_Partition2 to be NOT NULL after creation.

This allows the SWITCH to run.

CREATE TABLE [dbo].[SourceTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
);

CREATE CLUSTERED INDEX [SourceTable_Modified] ON [dbo].[SourceTable]
(   [Modified] ASC   )
GO

CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](datetime) AS
 RANGE RIGHT FOR VALUES
    (   N'2015.08.01',
        N'2015.09.01',
        N'2015.10.01',
        N'2015.11.01',
        N'2015.12.01',
        N'2016.01.01',
        N'2016.02.01',
        N'2016.03.01',
        N'2016.04.01',
        N'2016.05.01',
        N'2016.06.01',
        N'2016.07.01',
        N'2016.08.01'
 );

CREATE PARTITION SCHEME [ThirteenMonthPartScheme]
    AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY]);

CREATE TABLE [dbo].[DestinationTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NOT NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified);
GO

CREATE CLUSTERED INDEX [DestinationTable_Modified] ON [dbo].[DestinationTable]
(
    [Modified] ASC
) ON ThirteenMonthPartScheme (Modified);
GO

INSERT [dbo].[SourceTable]
VALUES  (1,1,1,1,1,9.99, '2015.08.01', 'James', 'A'),
        (2,1,1,1,1,9.99, '2015.08.02', 'James', 'A'),
        (3,1,1,1,1,9.99, '2015.08.03', 'James', 'A'),
        (4,1,1,1,1,9.99, '2015.09.01', 'James', 'A'),
        (5,1,1,1,1,9.99, '2015.09.02', 'James', 'A'),
        (6,1,1,1,1,9.99, '2015.09.03', 'James', 'A'),
        (7,1,1,1,1,9.99, '2015.10.01', 'James', 'A'),
        (8,1,1,1,1,9.99, '2015.10.02', 'James', 'A')

SELECT *
    INTO [TableName_Partition2]
FROM  SourceTable
WHERE Modified >= N'2015.08.02'
  AND Modified < N'2015.09.01';
GO

ALTER TABLE [TableName_Partition2] ALTER COLUMN Modified datetime NOT NULL
GO

CREATE CLUSTERED INDEX [TableName_Modified]
    ON [dbo].[TableName_Partition2] ([Modified] ASC);
GO

ALTER TABLE [dbo].[TableName_Partition2]
    ADD CONSTRAINT [CK_TableName_Partition2]
    CHECK ([Modified] >= N'2015.08.01'
      AND  [Modified] < N'2015.09.01');
GO

ALTER TABLE [TableName_Partition2]
    SWITCH TO [DestinationTable] PARTITION 2;
GO


Listing databases gives 'ERROR: invalid byte sequence for encoding “UTF8”: 0xe5 0xc6 0xf5' I logged into one of our production databases today and tried listing the databases using \l and got:
ERROR: invalid byte sequence for encoding “UTF8”: 0xe5 0xc6 0xf5

Since I am not touching any content, merely metadata on databases, I am not sure where to begin to attack this. Other than the superfluous similar error, I do not see how answers to similar questions are directly of help. If I were to guess, I would think that one of the names of the databases included chars not representable using utf8.
Clues?
Debugging info
Running Postgresql version 10.4 64bit on Windows Server 2016.
mydb=> SHOW client_encoding;
 client_encoding
-----------------
 WIN1252

mydb=> SHOW server_encoding;
 server_encoding
-----------------
 UTF8

mydb=> SELECT datname::bytea, encoding FROM pg_database;
       datname        | encoding
----------------------+----------
 \x706f737467726573   |        6
 \x74656d706c61746531 |        6
 \x74656d706c61746530 |        6
 \x6e696d626c656462   |        6

Quick fix: change client encoding to match content
I got this working by changing the client encoding to match the server encoding. Changing the client encoding did the trick, as it prevented Postgres from encoding utf8 text when the encoding was cp1252. (I have no idea what the Collate stuff is.)
mydb=> \encoding
WIN1252

mydb=> \l
ERROR:  invalid byte sequence for encoding "UTF8": 0xe5 0x6c 0x5f

mydb=> \encoding utf8;

mydb=> \l
                                                      List of databases
   Name    |    Owner    | Encoding |           Collate           |            Ctype            |      Access privileges
-----------+-------------+----------+-----------------------------+-----------------------------+-----------------------------
 mydb      |     myadmin | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =Tc/myadmin            +
           |             |          |                             |                             | myadmin=CTc/myadmin+
           |             |          |                             |                             | my=c/myadmin
 postgres  | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 |
 template0 | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =c/postgres                +
           |             |          |                             |                             | postgres=CTc/postgres
 template1 | postgres    | UTF8     | Norwegian Bokml_Norway.1252 | Norwegian Bokml_Norway.1252 | =c/postgres                +
           |             |          |                             |                             | postgres=CTc/postgres

Why (an excursion into raw byte territory)
This took a while to dig into, but I used the tip from the comments section that \l is actually equivalent to a SQL query on the pg_catalog.pg_database. By switching which columns to list I was able to find the column that was proving problematic and by showing it as raw bytes I could pinpoint exactly where:

So it was the datcollate column that was proving problematic. According to the docs, this column is of type name and is LC_COLLATE for this database. Obviously there is something that escapes the eye, since there is nothing in the UTF8 encoded output that is "non-ascii", but the output is a bit weird, since the locale name has some chars which seems to have disappeared: the "å" character in "Norwegian Bokml_Norway.1252" is missing (should be "...Bokmål..."). Is it just a coincidence?
Let's compare outputs of standard ascii with the output above:
$ echo -n Bokml | hexdump -C
00000000  42 6f 6b 6d 6c                                    |Bokml|
00000005

If you compare this with the raw output above you see that the psql output has an additional byte: e5 between 6d and 6c. This is not showing.
So this is how å is encoded in my UTF8 terminal:
$ echo -n Bokmål | hexdump -C
00000000  42 6f 6b 6d c3 a5 6c                              |Bokm..l|
00000007

$ printf "%b" "\xc3\xa5"
å

So å uses two bytes (c3a5) in UTF8, while above it is just one byte. Can it be å in WIN1252?
$ printf "m%b" "\xe5"
m�

$ printf "%b" "\xe5" |  iconv -f cp1252 -t utf8
å

Indeed, it was! So the problem is that the locale name is encoded in the internal postgres database as WIN1252, which seems to not be handled correctly when the server encoding is set to UTF8, as the client seems to assume that the data it receives will be all valid utf8.
Bonus: VIEW query issue fixed
Coincidentally, this also fixed another, related issue I was fixing, which happened when trying to list contents from a VIEW that contained UTF8 Norwegian characters:
mydb=> select * from conversation_message limit 20;
ERROR:  character with byte sequence 0xf0 0x9f 0x91 0x8d in encoding "UTF8" has no equivalent in encoding "WIN1252"


Is there an easy way to copy one database to another In MySQL?

Say I have a local database. Now I want the remote database to mimic the local database.

One way I can think of is to simply dump the database, upload and let things work.

Well, database is large.

Another I can think of is connecting straight to the database and fill the line one by one with a dedicated vb.net program.

Sometimes I just want to create a back up database in the same server.

How does the pro do so?

for large database you need to use command line to (clean) dump local database and after that in remote server to import (with ssh connection and also command line mysql-import or directly after connect to mysql), you need to sepcify your environment (windows, mac..)

some sources:

-http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

-http://www.lullabot.com/blog/importexport-large-mysql-databases


how to set timestamp in datetime field if the time is not specified? I have a Stored Procedure having input parameter as @startDate as datetime and I am using this parameter to compare this fields to table column. I want to convert that @startDate field if the timestamp is not passed by the user in SP?

Example: Let's say if user passes @startDate = '01/14/2018 6:30:00 PM' then no it is should be taken as it is in the SP but if @startDate is passed as '01/14/2018' only then it should be converted to '01/14/2018 11:59:59 PM'.

There's a few ways. Here's one.

declare @startDate datetime = '20180114'

if @startDate = cast(@startDate as date)
begin
    set @startDate = dateadd(second,-1,dateadd(day,1,@startdate))
end

select @startDate


updating millions of rows in multiple tables sql server 2005 I have a situation where I am going to need to update millions of rows in 11 different tables. I only have to update 1 column in each of the tables but I was wondering what would be the most efficient way to do this? One thought was to drop the indexes on the tables temporarily and run update statements in batches of 100,000 on 1 table at a time. We are planning to take the database offline to prevent potential locking issues. I would like to know what the fastest way to update all of these tables would be. Our database is Microsoft SQL Server 2005. Below are the counts of rows from the 11 tables that need updated.

Table 1 206,108

Table 2 560,859

Table 3 690,946

Table 4 1,106,173

Table 5 3,159,963

Table 6 3,250,619

Table 7 4,046,048

Table 8 5,638,351

Table 9 8,880,814

Table 10 18,060,095

Table 11 76,294,745

Most efficient is a fuzzy term. You're talking about updating about 122 million rows. If you start running naive updates now, it will probably finish before you can create a useful estimate. (I'd expect it to take about half an hour on my development machine. Updating 50 million rows takes 12 minutes.)

Dropping indexes beforehand is fine if you have your DDL under version control, and you can rebuild the indexes more or less automatically. Dropping all constraints should make the updates run faster still, but again I'd do that only if

execution time were critical,
DDL were under version control, and
I could rebuild all the constraints more or less automatically.

Cascading foreign key constraints can be an issue.

Personally, I try to avoid situations where execution time is critical. I don't usually drop primary key and foreign key constraints. And I always rehearse outside the maintenance window.


What's the point in mapping users in SQL Server Data Collection? In SQL Server 2012 R2 Enterprise, whenever I check Management\Data Collection, right-click --> Configure Management Data  Warehouse , I see whatever users-accounts-logins except the one I am logged in

I cannot understand what's the point in "Map Logins and Users" in Management\DataCollectiion of SQL Server?

When you create the management data warehouse, the current login will be added to all three roles, making it an administrator of the database.

The dialog above lets you add other logins to the roles, to make those people readers, writers or administrators of the database.


Index on a Computed Column(using udf) I am trying to create a computed column using a udf. I keep hitting the cannot be persisted because the column does user or system data access, when i try creating a index for that column or even for trying to make the column persisted.
This is the udf func i am using :
CREATE FUNCTION x.bal(@ID NVARCHAR(128))
RETURNS DECIMAL WITH SCHEMABINDING
AS
BEGIN
DECLARE @SUM DECIMAL ;
    WITH CTE AS (
    SELECT ROW_NUMBER() OVER (ORDER BY date ASC, type DESC) AS ROWNUMBER,[Id]
     T_id
    FROM x.entries
    )

    SELECT @SUM = (SELECT SUM(AMOUNT) FROM CTE L1
    WHERE L1.B_ID = L2.B_ID  AND L1.DATE <= L2.DATE AND L1.ROWNUMBER<L2.ROWNUMBER)
    FROM CTE L2
    WHERE L2.ID = @ID
    RETURN @SUM
END
GO

to add the column :
ALTER TABLE x.Entries
ADD bal AS dbo.bal(id)
CREATE INDEX IDX_bal ON x.Entries(bal)

I have also found that the error is only on 2017 local sql server where as for Azure sql ,I see the following :
If I  run the Column Property check before creating the index, it works but if missed, it fails.

A PERSISTED COMPUTED column can't use Window functions like ROW_NUMBER(). It's one of the limitations because ROW_NUMBER() is not a deterministic function and only deterministic functions can be used with PERSISTED COMPUTED columns.
Paul White does a great write up on how to properly use PERSISTED COMPUTED columns in his article here.
Specifically he mentions how PERSISTED was added only for deterministic columns:

Persisted computed columns were added to the product specifically to
allow indexes to be built on deterministic but "imprecise" (floating
point) columns

If you could add a few more details on what your end goal is, I could try to suggest an alternative solution to solve the problem (e.g. what is the purpose of your UDF)?


Oracle database migration or copy I need to do migrate or copy the oracle 10g database from sun solaris to another sun solaris server (oracle 11g). I was tried with SQL Developer tool. But It is not able copy all the objects in the particular schema. By the same time I should copy objects only, Don't need for data. Is there any procedure or scripts to copy the database or else any Oracle tool ?

The best way to achieve what you want is to do full export/import using data pump technology.

After successful import, you will probably have to recompile invalid objects.


Create a trigger on all the last_modified columns in PostgreSQL In PostgreSQL 9.5, I have tables with columns in the form

prefix_last_modified timestamp without time zone NOT NULL DEFAULT  (clock_timestamp() AT TIME ZONE 'UTC')

I was looking for a way to set the last modified value automatically updated at each update of the rows, and I found this nice post that defined the function:

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

Now, I'd like to know if there is any way to pass the column name to the PostgreSQL function and to execute it to the NEW row? E.g.

CREATE OR REPLACE FUNCTION update_modified_column(varchar column)
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.column = now();
        RETURN NEW;
    END;
$$ language 'plpgsql';

spi module: moddatetime

moddatetime — Functions for Tracking Last Modification Time

  moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table.

  To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

  There is an example in moddatetime.example.

Example / Synopsis

From the above referenced file,

DROP TABLE mdt;

CREATE TABLE mdt (
    id      int4,
    idesc       text,
    moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER mdt_moddatetime
    BEFORE UPDATE ON mdt
    FOR EACH ROW
    EXECUTE PROCEDURE moddatetime (moddate);

INSERT INTO mdt VALUES (1, 'first');
INSERT INTO mdt VALUES (2, 'second');
INSERT INTO mdt VALUES (3, 'third');

SELECT * FROM mdt;

UPDATE mdt SET id = 4
    WHERE id = 1;
UPDATE mdt SET id = 5
    WHERE id = 2;
UPDATE mdt SET id = 6
    WHERE id = 3;

SELECT * FROM mdt;

Your Application

So this is what you would need to.

CREATE EXTENSION moddatetime;

CREATE TRIGGER mdt_table
  BEFORE UPDATE ON table
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (prefix_last_modified);


Table Partitioning and updates to partition subset I am partitioning a large table (2 Billion records ) on an integer say AssetID that has 70,000 unique values, due to partitioning limitation of 15,000 I will create a partition on say 10,000 values using ranges. Each asset will have several thousands of records.

I have a staging table that updates a subset of assets every day. And currently, we are deleting assets and re-inserting them.

If I was able to partition by Asset ID then I could simply swap the partition, but since I am partitioning by asset range it gets a bit more complicated.

Each day I update approximately  2% of the data. Updates can happen on any asset in the dataset.

What is a recommended approach to updating a subset of data in the partition?
Say that I have an update for an asset in patition 10.

Do I have to switch out the partition 10 to a new staging table and perform updates there and then switch it back in?
What would the advantage of this be as compared to updating data directly?

due to partitioning limitation of 15,000 I will create a partition on
  say 10,000 values using ranges.

If by this you mean that you plan to create a partition function with 10000 ranges, then I strongly recommend against doing that. The limit of 15000 is not a goal. The product supports going over 1000 now but the engine support for that isn't great. You should expect to see query performance and other issues if you work with a table with so many partitions.

  If I was able to partition by Asset ID then I could simply swap the
  partition, but since I am partitioning by asset range it gets a bit
  more complicated.

Partitioning a table isn't something to take lightly, especially if you need to convert a table that you consider to be large to the new partition scheme. It is atypical to partition a table on an integer value like you described. Most of the time people partition by date or by a column that represents a date column. Here, it seems as if you are partitioning the table simply to allow switching out a partition, truncating it, and reinserting all of the data.

  What is a recommended approach to updating a subset of data in the
  partition? Say that I have an update for an asset in patition 10.

Issue a T-SQL UPDATE to change your data. If you know that all of the data to be updated resides in a single partition you can filter by the partition function of that function (using syntax similar to $PARTITION.{{partition_function}}(AssetID) = 10) to avoid an unnecessary DML request sort.

  Do I have to switch out the partition 10 to a new staging table and
  perform updates there and then switch it back in? What would the
  advantage of this be as compared to updating data directly?

No, you do not need to switch out a partition to a new table just to perform updates. However, there are some advantages in performing DML against single partitions of a properly partitioned table:

If you'd like to perform an operation that requires a table lock on the target, such as parallel inserts in SQL Server 2016, you can do so against many partitions concurrently if you switch the partitions out first into their own tables.
You can avoid blocking issues without needing partition level lock escalation.
The UPDATE operator in a query cannot run in parallel. However, if you switch out partitions then you can safely and easily issue many concurrent UPDATE queries at once.

It's really unclear what you hope to accomplish by partitioning your table. I recommend reading through some of the resources here and defining your goal to be a bit better. There's nothing wrong with partitioning a table to, in addition to other goals, make an ETL type of process more efficient. however, the partition scheme that you pick can have big impacts on maintenance and query performance against the table.