Windows Server 2016 Active Directory-Detached Cluster - Cannot add a Client Access Point I am setting up a Windows Server 2016 cluster detached from an active directory (so called Workgroup Cluster, i.e. nodes are not added to an active directory). I cannot add a Client Access Point to the cluster. The error is Unable to determine if the computer 'client_access_point_name' exists in the domain 'WORKGROUP'.

How a client access point can be added to the cluster?

P.S.: I need to add a client access point to use it later in SQL Server Availability Group Listener, to use it for auto-failover between mirrors.

How a client access point can be added to the cluster?

You'll have to use PowerShell. No AD means no AD and you lose all of the great functionality and brevity it offers.

  I need to add a client access point to use it later in SQL Server Availability Group Listener, to use it for auto-failover between mirrors.

Between replicas, you mean? Again, you'll need to use PowerShell.

There is an upcoming blog post [(placeholder)] blog post I wrote detailing this. When it is officially published, I'll update the answer with a link. In the interim, here is the basics of what you'll need to do this.

You may want to re-think using clusters like this (workgroup) as this is just the tip of the administrative iceberg you'll be dealing with.

Add-ClusterResource -Name "IPAddress1" -ResourceType "IP Address" -Group "WGAG"
Get-ClusterResource -Name IPAddress1 | Set-ClusterParameter -Multiple @{"Network" = "Cluster Network 1";"Address" = "20.250.250.9";"SubnetMask" = "255.0.0.0";"EnableDHCP" = 0}
Add-ClusterResource -Name "IPAddress2" -ResourceType "IP Address" -Group "WGAG"
Get-ClusterResource -Name IPAddress2 | Set-ClusterParameter -Multiple @{"Network" = "Cluster Network 2";"Address" = "30.250.250.9";"SubnetMask" = "255.0.0.0";"EnableDHCP" = 0}
Add-ClusterResource -Name "TestName" -Group "WGAG" -ResourceType "Network Name"
Get-ClusterResource -Name "TestName" | Set-ClusterParameter -Multiple @{"DnsName" = "TestName";"RegisterAllProvidersIP" = 1}
Set-ClusterResourceDependency -Resource TestName -Dependency "[IPAddress1] or [IPAddress2]"
Start-ClusterResource -Name TestName -Verbose

Updated with comments:

  ... now we are trying to setup cluster on SQL Server 2016 and Windows Server 2016 without AD (just to make everything simplier).

This does not make things simple. It actually creates much more manual of an environment. It is possible to automate things, but most of the common automation tools or powershell scripts aren't going to work as most assume either a standard login on each server (security issue) or implicitly use Windows Authentication which you now, won't have.

Additionally there is the aspect of SQL Server where you'll lose all the support of AD. No Kerberos, all SQL logins (or local windows logins - which are widely not useful in these scenarios) and no groups, etc. Setting up the AG will need to be completed through certificate endpoints if you want to keep things secure and you won't be able to use any GUIs in order to set it up end to end.

To reiterate, it doesn't make anything simple - only more complex. These types of configurations should only be used where they are expressly needed.

If you want to continue going this way, that's your prerogative... I can only give advice and answers.


How to show multiple names for one record but not duplicate the record? I have a database structure like so:

Car
 - id
 - carname
 - image
 - category
 - status

Parts
 - partid
 - partname

CarParts
 - carpartid
 - carid(fk)
 - partid(fk)
 - amountid(fk)

Category
 - id
 - categoryname

Amount
 - amountid
 - amountvalue

Right now this is what I am working on:

SELECT * FROM carparts
INNER JOIN car on carparts.carpartid = car.id

INNER JOIN parts on parts.partid = carparts.carpartid

INNER JOIN amount on amount.amountid = carparts.amountid where status = 1

This returns duplicate records when there is more than one carpart in the carparts table. How can I combine the carparts.id and car.id to one row but still have multiple partname for that one record?

So what has to happen is that each car could have multiple car parts but when the query runs if there is more than one car part for one record it shows duplicate records with different partnames is there a way to have to one car with many partnames for that record?

Do i have to change the database structure or the query to do this?

you structure looks good . except that you could merge the amount to the part table . as every part is strictly associates with price.

also, only reason you will have duplicate rows is if any of your dimensions tables do not follow strict primary constraints.

Car
 - id (pk)
 - carname
 - image
 - category
 - status

Parts
 - partid (pk)
 - partname
 - amountValue

CarParts
 - carpartid (pk)
 - carid(fk)
 - partid(fk)
 - amountid(fk)

Category
 - id (pk )
 - categoryname


Best approach to calculation problem I am re-developing an application that has been running on Excel. One of the reports I need to recreate (which will be published in Power BI and needs to be a direct query) is as follows;

Column "Approved Est" is calculated from a specific table, so is "Rec Value Based on Ordered Works".  Column "+/- Approved Est" is a simple calculation based on the first two.   This is where i need advice.  There are many ways I know of to get the result, and here is what I have considered or tried.

1) Create a function that has a temporary table holding the initial columns so I can run one update that calculates the reliant columns without having to do the select for the original columns again, and ultimately returns one table.

2) Create a view with multiple unions that calculates each column.  Down side is that I have to re-run the select for the original columns to give the calculated ones.

3) Create separate functions that return the calculated value for any address

Any advice gratefully received.

For PowerBI DirectQuery you'll need to write a single view that returns all the data.  Internally you might use a table-valued function if that is convenient.

If the execution of the queries against the view is too expensive, you'll have to cache the results, either by loading them into a table, or by switching from DirectQuery to Import mode.

David


How to convert strings extracted from JSON to a numeric type for aggregates? I am struggling to extract data from this JSON object in PostgreSQL:
"temperature": {
"boiler_temp": {
  "values": [
    "1",
    "2",
    "3",
    "3",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "6",
    "6",
    "6",
    "4",
    "7",
    "7",
    "7",
    "7",
    "4",
    null,
    "4",
    "5",
    "5",
    "5",
    "6",
    "6",
    "6",
    "6",
    "6",
    "6",
    "6",
    "6",
    "6",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "5",
    "2",
    "2"
  ]
}
}

I am using jsonb_array_elements() to extract it but they are returned as a string like '1', '2' and so on and so forth. My goal is to try and find the "avg": 5,"max": 7,"min": 1 for this data set however because I am returning them as string I am getting a value of null and an error:
My current code:
(select MIN(x::text::int) from jsonb_array_elements((data#>>'{temperature,boiler_temp}')::jsonb->'values') AS x) as mininum_temp

And I am getting an error:

ERROR: Invalid Syntax for integer :""1""

You need to extract them as text using jsonb_array_elements_text(), then you can cast it to an integer:
select min(x.val::int), max(x.val::int), avg(x.val::int)
from the_table
  cross join jsonb_array_elements_text(data #> '{temperature,boiler_temp,values}') as x(val)


Restoring a single database from mysql enterprise full backup I am having command to restore all databases from full dump taken by enterprise full backup .Can anyone help me how to restore single database from full dump .

Try this:

mysql -u root -p --one-database destdbname < alldatabases.sql

In the above code substitute destdbname with the database name you want to restore, and alldatabases.sql with the name of your full DB backup.

If you would rather just extract the database dump of the single database from the --all-databases dump file, you can do this with sed using this command:

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

Where dbname is replaced with the database name of the database to extract, and alldatabases.sql is the name of your dump file. The result will be saved into the file output.sql.


Waterfall join in Oracle SQL How can I best do a waterfall join in SQL

SELECT * FROM A
LEFT JOIN B
ON A.col1=B.col1 AND
   A.col2=B.col2

but for the ones where there's no match only join on col1 and ignore col2:

LEFT JOIN B
   ON A.col1=B.col1 AND

Never heard of a waterfall join, but based on your description:

SELECT A.*, COALESCE(B.col3, C.col3) AS col3 ...
  FROM A
  LEFT JOIN B
    ON A. col1 = B.col1
    AND A.col2 = B.col2
  LEFT JOIN B AS C
    ON A.col1 = C.col1
    AND B.col1 IS NULL;


Comparing the location of values in different tables New to SQL. Hope to find distinct pizza sold by restaurants that are located in the same area as bob (customer).

Database schema:

Customers(cname, area)

Restaurants(rname, area)

Pizzas(pizza)

Sells(rname,pizza,price)

Likes(cname, pizza)

   SELECT distnct pizza
   FROM Sells
   WHERE rname in (
      SELECT rname
      FROM Restaurants
      WHERE area.....
   -- This is the part where i don't know how to continue.
   -- How do i write it so that i compare the location of the restaurant with bob's location?
   );

Seems to me that a combination of an INNER JOIN and EXISTS should do:

SELECT DISTINCT(s.pizza)
FROM dbo.Sells s
INNER JOIN dbo.Restaurants r
    ON s.rname = r.rname
WHERE EXISTS(SELECT 1 FROM dbo.Customers
             WHERE cname = 'bob'
             AND area = r.area)
;


backup a specific set of tables to be moved to a remote system I need to generate either backups or data dumps for about 30 tables.  I want to generate a file of some sort that contains the data for these tables and transfer them to a different system.  Each table has 3.8-4.5 million rows.  also a large number of these rows have columns with text that has newline characters which need to be handled correctly.

I'm using Sql Server 2012 and have SQL Server Management Studio.

Due to security and other particular reasons I cannot just use SQL Server Managment Studio's Import/Export Wizard for simply exporting tables from one database to another.  I have a requirement to get a dump of the data to transfer to a remote system.

I have tried SQL Server Management Studio's Generate Scripts capability, which does appear to handle the newline characters, however There is another issue.  In some of the fields the text is similar to "something blah \".  that slash causes a problem with the script that gets generated, because the Generate Scripts process wraps that text in ''  and with \' it basically escapes the single quote which corrupts the remaining data.

I have also tried to generate flat files via SQL Server Management Studio's Import/Export wizard and this doesn't appear to handle the newline characters correctly and again corrupting the remaining data.

Any help would be greatly appreciated.  and I can clarify any information that is missing.

Just take a full backup (with compression, if possible), restore it on the other server, and then you can select into or insert/select without having to worry about what happens to carriage returns, line feeds, and other characters that cause problems for files. You want to move (parts of) a database, not files.

Trying to just extract some 120 million rows from a database sounds like more trouble than it's worth. You're making this harder than it should be, for what? To save a few bytes in data transfer?


Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur? If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully?  I have several databases, with one log file per database.

Is it good practice to not leave any space on the drive available, i.e. allocate it all to the log files? (The drive is dedicated to the log files in this case, data and the OS live on their own partitions.)

Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your house key and the backup on the same key ring). But a slightly different question, do you expect none of your log files to ever grow? Leaving a bit of space at least gives you some canary time where you can deal with a log file that is growing atypically. With no space leftover, as soon as the log file needs to grow, you're down.


Foreign Keys doesn't work as I expect Here are my tables;

CREATE TABLE `borclular` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tc` char(11) COLLATE utf8_turkish_ci NOT NULL,
  `borclu` varchar(255) COLLATE utf8_turkish_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tc` (`tc`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci

 CREATE TABLE `dosyalar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kurum_id` int(11) NOT NULL,
  `borclu_id` int(11) NOT NULL,
  `hizmetno` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `kurum_id_index` (`kurum_id`),
  KEY `borclu_id_index` (`borclu_id`),
  CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`),
  CONSTRAINT `kurum_id_foreign` FOREIGN KEY (`kurum_id`) REFERENCES `kurumlar` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci

When I delete a row from borculular, I want related row from dosyalar to be also deleted.However, when I try to delete a row from borclular, I get the following error;

Cannot delete or update a parent row: a foreign key constraint fails (`iletisim`.`dosyalar`, CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`))

As pointed out by Mr Brownstone (this would just be a comment but it turned out too long) the behaviour you are expecting can be achieved if you define the key as ON DELETE CASCADE.

I would be very very careful with ON DELETE CASCADE, in fact I almost never use it and generally recommend against it. This counts for triggers that take action based upon rows being deleted too. If a naive programmer later implements UPSERT of a parent row by instead performing a DELETE followed by an INSERT (this is a very common pattern in my experience) then the child data is all lost because the delete is cascaded and the rows removed by the cascade are obviously not replaced by the subsequent insert.

Also if your UI allows the user, due to a bug, to try delete something they really shouldn't because rows elsewhere depend upon it, you (or the user) get an error instead of the database silently deleting the wrong thing and its children.

Not having the cascade option turned on sometimes means you need extra work in your data access or business logic layer to manually delete child objects before parents, but I consider this a small price to pay usually.

Caveats: Some consider my position to be one of unnecessary paranoia. Your mileage may vary.


Could anyone please help me understand MSSQL Compatibility Level? I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012.
So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3).

Executed query on New database:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyNewDatabase';

Result:

compatibility_level
-------------------
90

But System db's are showing Compatibility_Level as '110' of MSSQL-Server-2012.

Let me conclude my problems with below questions:

If Compatibility_level = 90 is for SQL Server 2005, then has the database really been upgraded to SQL Server 2012 or not?
Why does the restored database show Compatibility_Level as '90' and not '110'?
Also, if it is only contains features of SQL Server 2005, then what is the better way to upgrade it to SQL Server 2012?

Is the upgrade as simple as "ALTER DB"? Like this:

ALTER DATABASE MyNewDatabase
SET COMPATIBILITY_LEVEL = '110';

Maybe Compatibility_level = 90 is of MSSQL-2005, then is it really been upgraded to MSSQL-Server-2012 or not? how may I know?

If it's running on the 2012 instance, then that database's metadata has been upgraded. So you're good.

  Could anyone please explain me why restored database also showing Compatibility_Level as '90' and not '110'?

When you restore a database onto a newer version (called an upgrade), it's compatibility level is set to the lowest supported version for that instance.  In this case, 90 is still functional on the 2012 instance, so it was left as-is.

  Also, If it is still contains featured of MSSQL-Server-2005 only, then what is the better way to upgrade it to MSSQL-Server-2012?

  Upgrade is as much simple as "ALTER DB"?? like

Yes, it really is just as easy as ALTER DATABASE ... SET COMPATIBILITY_LEVEL.

You can remain at the lower compatibility level as long as you like.  However, to use newer features introduced since version 90, you'd need to update to the later compatibility levels.  You can see a list of some of the changes here:

Differences Between Lower Compatibility Levels and Levels 110 and 120

Also note that, starting with SQL Server 2014 (level 120), the 90 compatibility level is not available.  So the next time you upgrade, your database will automatically be updated to compatibility level 100.  You can see a table of supported levels on each engine version here:

ALTER DATABASE (Transact-SQL) Compatibility Level - Arguments


Postgres 9.0 recovery from inadvertent drop-all-tables event Due to a accidental config file check-in, a unit test wiped out our entire production database (did a drop, then re-created the tables).  Trying to restore from backup revealed the recent backups were corrupted and the only valid backup we have is just over 1 year old.

What are my options for recovering the data (if any exist)?  Autovacuum may have been turned on. I saw a tool pg_dirtyread, but it appears to require Postgres 9.1 or higher, and from what I can tell does not help with drop/create table scenarios.

Your only chance would be to restore that one year old backup and recover that using all the wall files that are archived since then. For that, you need to run archived log mode have all those files. With a little luck they are in filesystem backups.
It would need some time to process.


In SQL Server, how do you create a default constraint for a column using the result or return value from a Stored procedure? In SQL Server, can you create a default constraint for a column using the result or return value from a Stored procedure, and if so how?

So I am thinking of something along the lines of this, but with valid SQL.

ALTER TABLE [dbo].[MyTable]
    ADD  CONSTRAINT [DF_MyTable_MyColumn]
    DEFAULT (EXEC [dbo].[GetNextNumber]) FOR [MyColumn]
GO

My reason for this is I have a table which has the sole responsibility of providing the next number to use, and a table which the default value must use this next number every time a new record is entered.

You can't use a SP in a default, but you can use a function. But presumably your SP code has to increment the value in the NextNumber table, so that won't work for you.

Your best bet for doing this within SQL is probably to have an INSERT trigger on MyTable which calls the SP and sets MyColumn.

However you'll have to carefully consider the behaviour of your SP code when multiple concurrent inserts are involved.
For example, you'll probably need to select the current value from the NextNumber table using an UPDLOCK hint to prevent another user reading and using the same value. (Note that the UPDLOCK hint will only prevent reads in this way if it's within a transaction [which triggers run within in by default], and if other selects on the NextNumber table use UPDLOCK as well.)


NULLs in a composite primary key - SQL Server I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the purposes of simplification - that I have four main tables - Cats, Dogs, Pets and Owners - and one association table: OwnersToPets:

create table Cats (
    ID_Cats int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    miceEaten int NULL
)

create table Dogs (
    ID_Dogs int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    favouriteToy nvarchar(50) NULL
)

create table Owners (
    ID_Owners int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
)

create table Pets (
    ID_Pets int NOT NULL IDENTITY PRIMARY KEY,
    ID_Cats int references Cats(ID_Cats) NULL,
    ID_Dogs int references Dogs(ID_Dogs) NULL
)

create table OwnersToPets (
    ID_Owners int references Owners(ID_Owners) NOT NULL,
    ID_Pets int references Pets(ID_Pets) NOT NULL
)

When I create my entity objects by importing from the database, I get the following:

The problem is the relationship between Pets and Cats / Dogs. Each Cat / Dog has a single Pet reference. Each Pet object should have one Cat reference and one Dog reference, one of which will always be null. Conceptually, at least, there is a 1-to-0..1 between Cats / Dogs and Pets.

However, the entity data model created 0..1-to-many relationship between Cats / Dogs and Pets.

Any suggestions? Obviously, my database already exists and is currently in use with version 1 of the application I'm writing version 2 of, so I'd prefer not to have to change the database too much.

There is a lot of info missing in your question

Can a pet have multiple owners? eg we have a pet, not I have a pet
Do you expect more pet types (eg Rabbit, Bird, Fish)?

The problem with your current model (as shown) is the Pets table: it adds no value currently. Of course, your simplified design now hides information that probably makes it useful...

I'll throw in some ideas with assumptions though.

Note: This is correct design, whether EF can deal with it or not. That is, you design the model, implement the database, then make your client work with it

Option 1:

Assuming an owner can have 0..n dogs and/or 0..n pets, then you'd need 3 object tables and 2 link tables. You don't need Pet.

Owner, Cat, Dog
Link tables for CatOwner and DogOwner

This allows multiple owners per Dog and Cat. You can of course just have an OwnerID in Dog and Cat to restrict to one owner.

You can have different attributes for Cat and Dog here.

As you add more types, this becomes unwieldy (no matter how you model owners)

Option 2:

Do you need to distinguish Cat or Dog as separate entities?
Isn't this just a type attribute of Pet?

**Pet
PetID, PK,
PetType, FK,
PetName
OwnerID, FK (optional, see below)
...

You can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).

This requires each Cat or Dog (or Rabbit, Snake etc) object to have the same attributes.

Option 3:

The final way is superkey/subtype.

This is is useful because if combines elements of options 1 (different attributes per Cat and Dog) and option 2 (keeps the idea of Pet)

This defines one Pet object that has exactly one sub type of Dog, Cat, Rabbit, Snake etc. The super key comes from the extra UQ1 in Pet to enforce subtype. Pet as a table also stores common attributes.

**Pet
PetID, PK, UQ1
PetType, UQ1, 'Dog' or 'Cat'
PetName
OwnerID, FK (optional, see below)
...

**Dog
PetID, PK, FK1
PetType, PK, FK1, constraint = 'Dog'
Breed  -- per Dog,not per Pet
CatsChased
...

**Cat
PetID, PK, FK1
PetType, PK, FK1, constraint = 'Cat'
Breed -- per Cat, not per Pet
MiceEaten
...

Note: UQ1 is a unique constraint used by the FKs in Cat and Dog. It is a "super key" on Pet that allows sub-types via the FK

Again, you can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).

Note, PetType can be a constant derived column in Dog and Cat

Edit: added tables for Pet, Cat and Dog.
I assume Owner is obvious...

CREATE TABLE PetSpecies (
    PetSpeciesID tinyint NOT NULL IDENTITY (1,1),
    Species varchar(100) NOT NULL,

    CONSTRAINT PK_PetSpecies PRIMARY KEY CLUSTERED (PetSpeciesID),
    CONSTRAINT UQ_PetSpecies UNIQUE (Species)
);
INSERT PetSpecies (Species) VALUES ('Dog');
INSERT PetSpecies (Species) VALUES ('Cat');

CREATE TABLE Pet (
    PetID int NOT NULL IDENTITY (1,1),
    PetSpeciesID tinyint NOT NULL,
    PetName varchar(100) NOT NULL
    OwnerID int NOT NULL,
    ...

    CONSTRAINT PK_Pet PRIMARY KEY CLUSTERED (PetID),
    CONSTRAINT FK_Pet_PetSpecies FOREIGN KEY (PetSpeciesID)
                                 REFERENCES PetSpecies (PetSpeciesID),
    CONSTRAINT FK_Pet_Owner FOREIGN KEY (OwnerID) REFERENCE Owner (OwnerID),
    CONSTRAINT UQ_SuperKey UNIQUE (PetID, PetSpeciesID)
    );

CREATE TABLE Dog (
    PetID int NOT NULL,
    PetSpeciesID tinyint NOT NULL,
    CatsChased int NOT NULL,

    CONSTRAINT PK_Dog PRIMARY KEY CLUSTERED (PetID, PetSpeciesID),
    -- CONSTRAINT UQ_Dog UNIQUE (PetID),
    CONSTRAINT CK_Cat CHECK (PetSpeciesID = 1 /*dog*/),
    CONSTRAINT FK_Dog_Pet FOREIGN KEY (PetID, PetSpeciesID)
                          REFERENCES Pet(PetID, PetSpeciesID)
    );

CREATE TABLE Cat (
    PetID int NOT NULL,
    PetSpeciesID tinyint NOT NULL,
    MiceEaten int NOT NULL,

    CONSTRAINT PK_Cat PRIMARY KEY CLUSTERED (PetID, PetSpeciesID),
    -- CONSTRAINT UQ_Cat UNIQUE (PetID),
    CONSTRAINT CK_Cat CHECK (PetSpeciesID = 2 /*cat*/),
    CONSTRAINT FK_Cat_Pet FOREIGN KEY (PetID, PetSpeciesID)
                          REFERENCES Pet(PetID, PetSpeciesID)
    );

Notes:

The PK of cat and dog can be PetID or PetID, PetSpeciesID. The latter is better to match the foreign key.
The unique constraint on cat and dog  is optional, really, as the index is contained within the PK and PetID is unique anyway if PetSpeciesID is constrained to a single value.


Hotel Management database I need help with the design of my schema. I'm almost new at this and I'm not sure whether my relationships between the tables ROOM, GUEST and RESERVATION below are correct.

Also, I have problems figuring out what to do regarding the possibility of ROOM CHANGE: An example:

Michael Keaton has one reservation in which he took two rooms. The first time, he didn't like the room so the hotel moved him to another room, and the second time there was a plumbing problem in the same room so they moved him again. My database needs to keep record about every room change.

Here's the entity relationship diagram:

Sounds like you need a history table for room changes that should be made most likely through the app layer.  I know it's tempting to use built in triggers to just have it log it, but a simple app layer should do the trick.

If you're really concerned about that and you have the CDC option feel free to use that, but I would go with a transaction that logs the current record elsewhere, updates it, and commits with rollback error handling if it fails and a nice error for the end user to take some other step for the customer in the mean time while this error is looked at.

Edit:
Updating based on question.  To do this, you could write a stored procedure that you call from your app which:

-begins a transaction

-copies the current hotel number and occupant id along with a timestamp to a 'getRoomChangeHistory' type stored procedure

-then issue a 'updateRoomChange' type stored procedure so the new value is updated

-Finally COMMIT the transaction.  Add Add error handling for failures.  If you want a solid auditable way of doing it, this is it.  If you want easy, it won't be dependable.  For that, why not just save it as a document store somewhere else then if you don't care about ACID compliance?

Here's some more on the matter for SQL 2012 and above using TRY CATCH from Books OnLine to achieve this:
https://msdn.microsoft.com/en-us/library/ms175976.aspx


postgresql.conf file found in both /usr/share/postgresql and /etc/postgresql/ While troubleshooting another issue I noticed that in my Ubuntu 14.04 system there are two postgresql directories containing a postgresql.conf file:

/usr/share/postgresql
/etc/postgresql/9.3/main/

With the following directory structure:

$ tree -d /usr/share/postgresql
/usr/share/postgresql
├── 9.3
│   ├── extension
│   ├── man
│   │   ├── man1
│   │   └── man7
│   ├── timezonesets
│   └── tsearch_data
└── 9.4
    └── man
        ├── man1
        └── man7

... and:

$ tree -d /etc/postgresql/9.3/main/
/etc/postgresql/9.3/main/

With the postgresql.conf files residing in the following locations:

/usr/share/postgresql/9.3/postgresql.conf
/etc/postgresql/9.3/main/postgresql.conf

By using SHOW config_file; I have verified which configuration file my server is using:

postgres=# SHOW config_file;
               config_file
------------------------------------------
/etc/postgresql/9.3/main/postgresql.conf
(1 row)

My question is why do I find the postgresql.conf file in two locations and is the above an indication of some unused installation or other unused artifacts I ought to purge?

Files below /usr/share/postgresql/ are supposed to be data files that are not meant to be edited. They are not live configuration files.
The FHS spec at https://wiki.debian.org/FilesystemHierarchyStandard
defines /usr as:

/usr/
Secondary hierarchy for shareable, read-only data (formerly from UNIX source repository, now from UNIX system resources)
(files that are not-required to boot or rescue the system)

[...]
/usr/share/ : Architecture-independent (shared) data
[...]

On the other hand, files under /etc/postgresql/X.Y/main are the live configuration files for a PostgreSQL cluster named main with version X.Y, such as created by the command pg_createcluster. Each cluster (=each postgres instance) has its own postgresql.conf that is generated with it, as the other writable configuration and data files of that cluster.
Personally, on my Ubuntu 14.04, I don't find a postgresql.conf under /usr.
There is however a template file at /usr/share/postgresql/9.3/postgresql.conf.sample, but note the .sample suffix.
That file belongs to the postgresql-9.3 package, as revealed by dpkg -S:
$ dpkg -S /usr/share/postgresql/9.3/postgresql.conf.sample
postgresql-9.3: /usr/share/postgresql/9.3/postgresql.conf.sample

It's also consistent with
http://packages.ubuntu.com/trusty/amd64/postgresql-9.3/filelist
You may try dpkg -S postgresql.conf on your system and check if /usr/share/postgresql/9.3/postgresql.conf is part of the results, and if yes, to what package it belongs, and then what is the origin of that package. Maybe for some reason it's the same as the sample postgresql.conf, except it doesn't have the .sample suffix.


Which database system can break me free? I am currently running a MySQL Database for logging and analyzing those logs.

My current table schema looks like this:

CREATE TABLE `mylogs` (
`transfer_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`client_id` INT UNSIGNED NOT NULL ,
`client_ip` INT UNSIGNED NOT NULL ,
`server_1_ip` INT UNSIGNED NOT NULL ,
`server_2_ip` INT UNSIGNED NOT NULL ,
`service` ENUM(  'service1',  'service2',  'service3',  '...',  'service500' ) NOT NULL ,
`mb_transferred` FLOAT UNSIGNED NOT NULL ,
`time` TIMESTAMP NOT NULL
) ENGINE = MYISAM ;

I run a service where I deliver a lot of downloads that go over 1 extra hosts between the destination and origin host.
They are represented by the integer interpretation of the 32 bit IP address.

My system currently handles about 500 inserts/second during peak hours.
I run a master-slave system. The master has an apache webserver with a PHP file that gets called from remote hosts and inserts a line into the log table.
Then the changes get replicated to the slaves where queries happen.

My queries are primarily aggregations over the mb_transferred field  over a range in the time field filtered by client_id.

SELECT SUM(mb_transferred) FROM mylogs WHERE client_id = 123 AND time > '2012-01-01 00:00:00'

The maser server runs an apache webserver with a simple php file that does the insert and is called by other servers.

My server is now almost at the limit. I already upgraded to big hardware.

I thought about using a GUID as primary key and using master master replication, that will for sure relieve something, but I think its short sighted, because it does not decrease the insert amount per server.

I am expecting higher trough-puts in the future and I am also worried about database size.

Also in future I plan to have a second table which defines "weights" for certain services.

Something like:

CREATE TABLE  `mylogs`.`service_weight` (
`plan` TINYINT NOT NULL ,
`service_name` ENUM(  'service_1',  'service_2' ) NOT NULL ,
`weight` FLOAT NOT NULL ,
PRIMARY KEY (  `plan` ,  `service_name` )
) ENGINE = MYISAM ;

Then I want to run join queries against this table and multiply the mb_transferred field with a weight factor.

I also want to add fields like "transfer_duration" to the logs table to calculate the speed of downloads and run queries to get statistical data how how well/bad the connection between certain networks, or certain servers for certain hosters is.

The point is. The data structure is simple, its just a huge amount of rows.

I have a lot of aggregation functions. This makes a light bulb in the "map reduce" section of my brain flashing.

I thougth about doing vertical shards and use client_id as a breaking point. For example if I have 10 server send every user to its userid mod 10 server.
This would be easy and relieve the load. But scaling will probably be awkward.

So i think with the size of the project that I am expecting to reach soon with the current growth I cannot do anything but turn towards a distributed database system.

I already tried to examine cassandra, project voldemort, amazon dynamodb and hbase but no matter how much I read I seem to run against walls.

I think the long years of relational thinking are somehow blockading my mind.

Can someone point me into the right direction on that?
What database system would be suited for my use case and why?

I dont need absolute up to the second data, a replication delay is acceptable.
I will not have updates, only inserts.
I would like to have a fault tolerant system
The data amount is getting huge so a sharded system would be nice
I have heavy I/O load

There's a lot to consider. I'm going to narrow this down to a single problem and offer one solution, although there's many approaches.

Problem: You need AD-HOC queries on lots of rows.

First, setup a server to handle writes and a replicated slave (running some flavor of MySQL 5.5.x) to handle reads. My personal preference is Percona Server. Your mileage may vary.

Once you have replication caught up, ALTER your slave table to: engine=InnoDB, ROW_FORMAT=COMPRESSED, partition by date, and index the data to optimize your query WHERE clauses. Each of these have an optimization for your case and there is further tuning required.

Finally, setup a schedule to archive/shard the data when it falls out of scope. Say 2-3 years. Depends on your SLA for providing back reporting. You can always union it if you have to.

MySQL 5.6 will have Memcached built in which will allow you to store pre-processed results. Map/Reduce is great, as long as it's not the first run of the reduce function. NoSQL is great as long as the data is already processed. Neither is really your problem.

Additions 2012-06-13:
Since it appears my recommendations have stirred another response, I'll submit my reasons:

Why InnoDB in this case? With the constant insertions of many rows,
you can use InnoDB to avoid implicit read locks at the table level.
Why COMPRESSED + Barracuda? "The compression means less data is transferred between disk and memory, and takes up less space in memory. The benefits are amplified for tables with secondary indexes, because index data is compressed also."
Why partition by date? Reduce scan breadth.
Why index properly? Decrease search times by reducing cpu bottleneck through elimination of full table scans.


Should I clean up my old table statistics? I've been cleaning up my indexes but now realized I have over 150 statistics that remain that are not used.  Should I drop them?  I'm not noticing any performance issues from them.  I have 9 indexes and 159 statistics.

Check out this article.
I think it will answer your questions and give you the tools to deal with over-inflated number of auto-statistics.
The author suggests dropping ALL auto-created stats occasionally, as the one that are required will be recreated when the first query that needs it is executed. It explains the risks / benefits.


How can I repair a large myisam database when diskspace is low? MariaDB 10 | CentOS 5.x

I have a large myisam database with some tables that are marked as crashed.  The collective size in /var/lib/mysql/fooDatabase/ is 17 GB.  I have 4 GB of space left.

Initially, I tried running mysqlcheck --verbose --auto-repair fooDatabase but that failed and in logs I see:

[Warning] mysqld: Disk is full writing '.fooDatabase/foo_TABLE.TMD' (Errcode:28 "No space left on device"_. Waiting for someone to free space...)

I restarted mariadb and reclaimed the disk space but I'm back to square one here.

I'm now running repair table foo_TABLE; but I'm not sure if I'll experience the same problem or not.

At this point, I've squeezed as much free disk space out of the system as I can.  Are there any other options?  Are there any repair operations that are more sparing with temporary disk space?  Any other options?

You don't have many options here.

Since you're running MyISAM storage engine, you may try to snapshot your files, e.g. with mysqlhotcopy, repair the snapshot on another host with much more space, then copy the repaired data files back to the original host.


How can I use an ETL? I know that those letters mean Extract, Transform, and Load.

But, when I used it at first, I thought that during the Transform phase I could do plenty of different joins on data that I've extracted from data sources, later on I realized that doing a join on a different ETL is not that handy.

So what do we do in Transform phase ?
Calculate and output the result ?
String transformation?
Should input data sources only be csv, xml or plain file?
If joins are not that handy, should we only do high level transformation within an ETL ?

Thank you

Extract Transform and Load is the preparation of foreign data to be inserted into your database or data warehouse
Looking at the basics of the ETL, noted Data Warehouse Designer Bill Inmon notes:

Once upon a time in the not so distant past, there was no ETL (extract, transform and load) software. If you wanted to build a data warehouse, you had to write code in order to get data from one source to the appropriate target. There was lots of code – lots of repetitive code.
After you wrote your code, you had to maintain it. Every time a legacy system changed, you did manual maintenance to your code. Every time a target definition changed, you had to do manual maintenance to your code. Every time an end user wanted something new, you had to do maintenance to your code.

From here, a plethora of ETL products proliferated, as Inmon describes in his brief history of ETL products. They were popular because they were software tools designed to extract data from changing systems, transform it according to specific rules, and load it into data warehouses. This software process meant that humans were involved only in the critical element of the loop: untangling loaded data errors. By automating to the maximum possible extent, the ETL process provided companies a seamless way of not only loading their current databases into a data warehouse, but the ability to load future data sets of the same databases in, so that the data warehouse can continuously provide future results.
To answer your question specifically, different databases provide different extracts. Transformations are applied to normalize the data. Normalization is both in the database-specific sense, changing the patterns of the data to match the receiving data warehouse, but also in the human sense, insuring that the same data in different systems appears the same to the incoming system.
Data sources can be anything you can code a transform for, as the purpose of the transform is to apply rules to the incoming data such that it fits your data model. Joining different data sets should only be performed if necessary. Rely on your recipient database instead to synchronize results.


Tracker issue database suggestion Good morning everyone, i made this database i would like to know your opinions usertable and projecttable have a many to many relationship , ProjectCreatedByUser have two FKs (userID, projectID) , same for usertable and issuesID,IssueCreatedByUser have two FKS( IssueID and UserID) in every project it can have one or too many issues hence why i added projectID as a FK in the IssueTABLE

Check back on my comments to your three previous questions on designing an accounting application schema (avoid cross and or repeat-posting BTW).

Schema  design  is an iterative process. There must be a billion issue tracker programmes out there - search for "open source issue/bug/ticket/problem tracker" and you'll get loads of schemas with which to experiment.

Personal recommendation on this one, at least for initial study: D. Richard Hipp's bug tracker called Fossil - he uses an SQLite database to store the schema. From the Fossil site "Fossil is a simple, high-reliability, distributed software configuration management system" and the software that Hipp produces is stellar!

Look at those and then design your own schema. I don't know what development environment you're using, but take your first pass at a schema, and then run some code against it. Then fix the bugs (there will be bugs) and do the same thing again. Rinse and repeat until you get a programme that works to your own satisfaction.

If this is for some commercial need, then I suggest that you use an already available F/LOSS programme, unless your company has some compelling requirement that can only be fulfilled by a COTS product. You haven't mentioned any particular database server - that could be a factor in any choice of COTS and indeed F/LOSS. Some companies offer free products - Oracle has a bug tracker written in Apex for which the source is available and it's interface is very slick - your choices are virtually limitless in this domain.

If this is a school/uni project and your professor seems to enjoy handing out schema design projects (IMHO, a bookkeeping system is a bit much for a first project), then you should a) tell us this up front and b) show us that you have made some effort - which you have - your ERD above shows that you've at least thought about the problem.


Installing SQL Server version 10.50.4000 is failing I am attempting to install SQL Server 2008 R2 version 10.50.4000 on a virtual machine running Microsoft Windows Server 2008 R2.

When I try to install SQL Server, I get an error that, when I look it up, says I need to install and configure .NET Framework 3.5 first.

When I try to install via add features, it fails without telling my why, and I have to restart the server.

When I try to install manually, it says I have to use the installer.

When I try to install via PowerShell (Import-Module ServerManager and Add-WindowsFeature as-net-framework), I get the following error:

  Add-WindowsFeature : Installation of [.NET Framework 3.5.1 Features]
  .NET Framework 3.5.1 failed. The server needs to b e restarted to undo
  the changes. At line:1 char:19
  + Add-WindowsFeature <<<<  as-net-framework
      + CategoryInfo          : OperationStopped: (:) [Add-WindowsFeature], Exception
      + FullyQualifiedErrorId : FeatureMessageDetail,Microsoft.Windows.ServerManager.Commands.AddWindowsFeatureCommand

There has been more failed attempts, but I can't remember all that I have done that has failed, I have had people breathing down my neck all day, so I skipped the recording what I have tried step in the name of speed... Any help would be appreciated.

I got it working. This is how I got it working... I used a CD.

I don't know why it worked. I didn't even have to use a key, and it was the same version I downloaded multiple times from the Microsoft website...


Amount of used cache / free cache I need to find out how much cache is used, and if there is free cache available.

Background:

Queries on a production server take a lot of time when the server is under heavy load. I suspect that the available cache is too small to serve a high number of different queries.

Ensure you've set the max server memory (mb) and min server memory (mb) options via sys.sp_configure appropriately for your server.  See my question to determine what those numbers should be.

Check Page Life Expectancy to see if your buffer pool is too small.  Depending on the size of your buffer pool, a commonly mentioned number is 300 seconds (5 minutes), but this will vary depending on your needs.  This code should work well enough:

DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(4000);
SET @cmd = '';
IF @VersionINT >= 9
BEGIN
    SET @cmd =
'SET NOCOUNT ON;

DECLARE @SecondsSinceRestart INT;
DECLARE @cpu_count INT;
DECLARE @physical_memory_kb INT;
DECLARE @committed_target_kb INT;
DECLARE @committed_kb INT;

DECLARE @BootTime DATETIME;
DECLARE @output TABLE
(
    RowNum INT NOT NULL IDENTITY(1,1)
    , [output] VARCHAR(1000) NULL
);
INSERT INTO @output ([output])
EXEC sys.xp_cmdshell @command = ''wmic os get lastbootuptime'';

;WITH c AS (
    SELECT Year = SUBSTRING(o.output, 1, 4)
        , Month = SUBSTRING(o.output, 5, 2)
        , Day = SUBSTRING(o.output, 7, 2)
        , Hour = SUBSTRING(o.output, 9, 2)
        , Minute = SUBSTRING(o.output, 11, 2)
        , Second = SUBSTRING(o.output, 13, 2)
    FROM @output o
    WHERE o.RowNum = 2
)
SELECT @BootTime = CONVERT(DATETIME, c.Year + ''-'' + c.Month + ''-'' + c.Day + ''T'' + c.Hour + '':'' + c.Minute + '':'' + c.Second, 126)
FROM c;

SET @SecondsSinceRestart = DATEDIFF(SECOND, @BootTime, GETDATE());

';
IF @VersionINT = 11
SET @cmd = @cmd + '
SELECT @cpu_count = cpu_count
        , @physical_memory_kb = physical_memory_kb
        , @committed_target_kb = committed_target_kb
        , @committed_kb = committed_kb
FROM sys.dm_os_sys_info dosi
'
ELSE
SET @cmd = @cmd + '
SELECT @cpu_count = cpu_count
        , @physical_memory_kb = physical_memory_in_bytes / 1024
        , @committed_target_kb = bpool_commit_target * 8
        , @committed_kb = bpool_committed * 8
FROM sys.dm_os_sys_info dosi
';

SET @cmd = @cmd + '
;WITH c AS (
    SELECT ServerName = @@SERVERNAME
        , pc.object_name
        , pc.counter_name
        , pc.instance_name
        , pc.cntr_value
        , BootTime = @BootTime
        , SecondsSinceRestart = @SecondsSinceRestart
        , cpu_count = @cpu_count
        , physical_memory_kb = @physical_memory_kb
        , committed_target_kb = @committed_target_kb
        , committed_kb = @committed_kb
    FROM sys.dm_os_performance_counters pc
    WHERE pc.object_name = ''MSSQL$'' + CONVERT(VARCHAR(255), SERVERPROPERTY(''InstanceName'')) + '':Buffer Node                                                                                ''
    AND pc.counter_name = ''Page life expectancy                                                                                ''
    AND pc.instance_name IS NOT NULL
)
SELECT RunNum = 7
    , c.ServerName
    , c.counter_name
    , c.object_name
    , c.instance_name
    , c.cntr_value
    , c.SecondsSinceRestart
    , c.BootTime
    , Ratio = c.cntr_value / CONVERT(DECIMAL(10,0), c.SecondsSinceRestart)
    , cpu_count
    , physical_memory_kb
    , committed_target_kb
    , committed_kb
    , ServerVersion = SERVERPROPERTY(''ProductVersion'')
FROM c;';
    EXEC sys.sp_executesql @cmd;
END

You can see what objects are occupying memory using the code shown in a recent blog post I wrote over at SQLServerScience.com.


Daily snapshot fact table I have a list of products per costumer. Costumers can delete or add products to their list. I want to know in each day what is the list of products (and count) by costumer.

My first approach is to have a fact table with all products by client each day.

Is this the best approach?
This fact table will grow at the same rate that the products are added to costumers list.

btw i'm using Pentaho.

You could do that. I'm thinking something along the lines of having an active and inactive date on your fact table for each product for each customer. Then you could mark a previous product with an inactive date.

This should allow you to query it for date ranges that are include a specific date to get what their product list was for a specific point in time.


postgresql explain analyze from sql file? I had wrote a long query for postgresql, and I want to use postgresql explain ANALYZE to find out runnig time / parallel workers information about that query.
I can run that sql command by using

psql -d database -f myquery.sql

but I don't know how to explan/analyze that sql file in command.
and because it is a really long query , it's hard to paste in psql console.
so , is there any other way to run explain/analyze from the sql file ?

You could use cat to create a command you pass to psql. For example like this:

psql -d database -c "explain analyze $( cat myquery.sql )"

Editing a file and adding those statements at the beginning are an option too.


How to see amount of reusable space for a table I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?

check out pg_freespacemap module: http://www.postgresql.org/docs/9.3/static/pgfreespacemap.html


MySQL command querying all MyISAM database I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.

Below is the Query to find all the tables which have MyISAM Engine

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');

Above Query will list all the tables having MyISAM Engine.

For how to convert your existing MyISAM tables to InnoDB
Below is the Query that will Return ALTER Statements to convert existing MyISAM Tables to InnoDB.

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` ENGINE = InnoDB;') FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');

You can execute these statements to convert engines.


Social network comments and posts model in PostgreSQL 9.6 I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post.

The thing is that each element in the array only holds an id of the comment author, not the actual user info of the author. What would be the best way to perform a join on every element of that array to replace the author id for their data, or if not what would be a better way to do this?

For example, one of my post table rows might look like:

id: 5
author: 1
body: "Hello word"
comments: [{author:0, body: "It's me"}]

Where the comments column is a JSONB array.

My users table might look like:

id: 0
username: Dux

I do need threaded comments.

First the author table, that's easy.

CREATE TABLE author (
    authorid   serial  PRIMARY KEY,
    username   text    UNIQUE
);

Now we use a self-referential post table to create the hierarchical structure.

CREATE TABLE posts (
    id           serial  PRIMARY KEY,
    parent_post  int     REFERENCES posts,
    authorid     int     NOT NULL REFERENCES author,
    body         text    NOT NULL
);

Test data...

INSERT INTO author (authorid,username) VALUES
  (0, 'ecarroll'),
  (1, 'jbob');
INSERT INTO posts (id,parent_post,authorid,body) VALUES
  (0, null, 0, 'HALLO WORLD'),
  (1 ,0,    1, 'HALLO EVAN' );

Recursive query to get it working.

WITH RECURSIVE t(chain,author_un,text,id) AS (
   SELECT ARRAY[id], username, body, id
   FROM posts
   INNER JOIN author USING (authorid)
   WHERE parent_post IS NULL
   UNION ALL
     SELECT t.chain||p.id, username, p.body, p.id
     FROM t
     INNER JOIN posts AS p
       ON (t.id = p.parent_post)
     INNER JOIN author AS a
       USING (authorid)
)
SELECT *
FROM t;

 chain | author_un |    text     | id
-------+-----------+-------------+----
 {0}   | ecarroll  | HALLO WORLD |  0
 {0,1} | jbob      | HALLO EVAN  |  1
(2 rows)

For more information on this method, see my post here

How to build a table for a private messaging system that supports replies?


Resource Hosting Subsystem was terminated which caused Availability group to fail The following error was occurred in cluster events and the availability group was failed which resulted the databases in non-synchronizing state.

A component on the server did not respond in a timely fashion. This caused the cluster resource 'AG' (resource type 'SQL Server Availability Group', DLL 'hadrres.dll') to exceed its time-out threshold. As part of cluster health detection, recovery actions will be taken. The cluster will try to automatically recover by terminating and restarting the Resource Hosting Subsystem (RHS) process that is running this resource.

Please help me to find the root cause (A component on the server did not respond in a timely fashion).

That error means the AG failed one of the health detection timeouts (lease, session, or health check) summarized in this table from the docs:
Mechanics and guidelines of lease, cluster, and health check timeouts for Always On availability groups - Summary of Timeout Guidelines
Your first stop should be to review the SQL Server error log on each node in the AG, to see if there was something going on that might have caused the instance to stop responding to the cluster AG resource.  For instance, you might have crash dumps, deadlocked schedulers, etc.
To dig into the cluster-related details, you'll need to call the PowerShell Get-ClusterLog cmdlet (see here) to get the cluster log file from each node in the AG.  Then find the time associated with the failure error message you mentioned in the question.  Review the cluster log files around that time period for errors (search for "ERR") or any messages of interest from the AG resource DLL itself (which will include "[RES]" or "[hadrag]").
I would expect you'll find messages that include "timed out" or "IsAlive" showing the timing of the failures.  At this point, you should be able to figure out which server was not responding, and investigate that directly.
Figuring these things can be difficult.  Microsoft support has released a tool that can be used to categorize some types of failovers, which might be useful to you:
Failover Detection Utility - Availability Group Failover Analysis Made Easy


Return all except some fields using findOne() in mongodb for node.js I am trying to use the following script to query for a document but the returned document still contains the field (password) that I don't want.

const client = await MongoClient.connect(
                      MONGODB_CONNECTION_STRING,
                      { useUnifiedTopology: true }
                   );
const db = client.db(DB_NAME);
const collection = db.collection(collectionName);
const data = await collection
                .find(
                    {
                        _id: someId
                    },
                    {
                        password: 0
                    }
                );

It supposed to work as I have read in the mongodb documentation but I didn't find any documentation on findOne in mongodb for node.js.

I think you need to use project(). See the example here - Projections


Login with readonly to all databases The built in SA login on a server has full access too all databases. Is there a way to have a login, that has read-only access to all databases, current and future databases? So like sa, but with readonly access?

You can grant db_datareader to any user (map to a login) that you want. This will address the "all actual databases".
For the future database, as all new databases are created based on the [model] database, if you create a user in the [model] DB that has the "db_Datareader" role, then when a new DB will be created, it will contains that user as well.
If your new database come from backup restore, then this won't work and you may want to look at something like a trigger.


Loop through all tables and detect changed rows using checksum or hashbytes All the tables have an extra row called checksum which is stored by doing INSERT INTO example_table VALUES ('a','b', BINARY_CHECKSUM('a','b')).

Suppose someone change a row value, obviously the checksum will change, and when I run Select CHECKSUM_AGG(*) FROM example_table, that value will differ from the stored checksum.
How can I detect, looping through all tables, what row or table was changed?
Can I do this with a Stored Procedure? This is an SQL SERVER database.

select *
from table
where BINARY_CHECKSUM('a','b') <> checksumCol

But if they can change a or b they can change the checksumCol


Maria/MySQL Possible to allow user to GRANT drop though not use DROP? I'm setting up an SQL user (setup process for new websites) which will need to GRANT the DROP command to another user (admin user (for authenticated admins), not the public-facing user) though is there a way to prevent the user from using the DROP command itself (through SQL)?

No, I can not use an existing administrator-permission SQL user as the setup process is public facing.

I've read that an SQL user can not GRANT permissions it does not have though this was listed on a third-party website and not Maria's documentation.

True, you cannot use the GRANT command to give away grants you don't own as stated in the MySQL Documentation under the GRANT privilege:

  The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

The only alternative would be to create a user that have the power to the following:

INSERT IGNORE INTO mysql.db SET
    db = '...',
    user = '...',
    host = '...'
;
UPDATE mysql.db SET Drop_priv = 'Y' WHERE ... ;`
FLUSH PRIVILEGES;`

The INSERT IGNORE adds the initial entry into mysql.db in case it did not already exist. Then, the UPDATE sets the drop_priv column to 'Y'. FLUSH PRIVILEGES must be done manually is needed since no GRANT command is ever executed (GRANT always executes FLUSH PRIVILEGES; automatically)

Create a Stored Procedure that accepts a database, user and host and executes these commands.

For the sake of example, suppose the following

Database is mydb
User is 'john'@'10.20.30.40

The commands to run would be

INSERT IGNORE INTO mysql.db SET
    db = 'mydb',
    user = 'john',
    host = '10.20.30.40'
;
UPDATE mysql.db SET Drop_priv='Y' WHERE db='mydb' AND user='john' AND host='10.20.30.40';
FLUSH PRIVILEGES;

Place these commands in a stored procedure

First, login as root@localhost, then run the following:

USE mydb
DROP PROCEDURE IF EXISTS GiveAwayDrop;
DELIMITER $$
CREATE PROCEDURE GiveAwayDrop (givenDB VARCHAR(64),usr VARCHAR(64),hst VARCHAR(64))
BEGIN
    INSERT IGNORE INTO mysql.db SET
        db   = givenDB,
        user = usr,
        host = hst
    ;
    UPDATE mysql.db SET Drop_priv='Y' WHERE db=givenDB AND user=usr AND host=hst;
    FLUSH PRIVILEGES;
END $$
DELIMITER ;

You must give away the EXECUTE grant to the user, so that user can run stored procedures:

GRANT EXECUTE on mydb.* TO 'john'@'10.20.30.40';

See my old post how to GRANT EXECUTE on MySQL for further clarification.

Then, user 'john'@'10.20.30.40' can execute

CALL mydb.GiveAwayDrop('givendb','fred','10.20.%');

to give out the DROP privilege to 'fred'@'10.20.%'. This should work if givendb database exists and 'fred'@'10.20.%' already exists in mysql.user.

GIVE IT A TRY !!!


Script DB Level Permission Before Refresh to Non Prod Environment I am looking for a script that I can use to script out all database level permissions.

Script users \ permissions
Restore DB From Prod to Non Prod
Drop Prod Users and Run script to apply original permissions

There are a lot of scripts out there. But i am unsure which one to use. Would be great if someone can point me in the right direction.

You should obviously try using native SSMS functionality.
To scripts database users and their permissions use 'Generate script' context menu option of the database (right click db - tasks - generate scripts).
to restore database from Production to non-Production, again, use Script the action task and then modify it to suit your specific needs (right-click 'Databases' node - restore database - Script button on the upleft of the General tab).


rebuilding indexes and logshipping? We all know that index rebuilding creates lot of log growth..so what changes would you consider to your logshipping side to accomodate this log growth?

Do we increase the frequency of the logbackups?
ship the logs to an external storage such as SAN?

Please give your valuable suggestions.

My first suggestion would be to use intelligent re-indexing rather than just blindly re-indexing everything. This will help to minimise the impact on the logs un-necessarily. Ola Hallengren's offers one such solution.  See http://ola.hallengren.com/

External storage on a SAN that uses either cheap discs (SATA), or dedupe technology, makes it more affordable than expensive SAN discs.

Increasing the frequency of the log backups,and the copy and restore operations, makes sense to me. Ensure the maintenance is out of hours so that any learning curve about capacity minimises any pain.


Permissions for a MySQL user which will monitor replication status? I'd like to use a script to monitor the replication status of a MySQL database like in this bash script: https://gist.github.com/wesdeboer/1791632

I'd like to create a user which will only be used to query the status of the replication. So basically this user would just need to run the command:

"SHOW SLAVE STATUS \G"

What is the minimum set of permissions I need to grant a user to allow this?

(Is this even something which can be GRANTed?

According to the MySQL Documentation on SHOW SLAVE STATUS\G

Either SUPER or REPLICATION CLIENT should do it. I would go with the minimum:

GRANT REPLICATION CLIENT ON *.* to user@host;


Rows missing after online conversion from MyISAM to InnoDB We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down.

Now that the conversion is done, a lot of intermittent rows seem to be missing. Is this possibly due to operations during conversion? Or is the issue somewhere else?

Performing an ALTER to change storage engines won't make rows disappear. However, let me offer some advice since you said you're 'database noobs' in your question.

When modifying existing schema or doing anything that could affect data, here's some basic advice:

Make a backup first.
Have a change plan.
Test your plan on an offline host.
Have a test plan to compare before and after data.
Schedule and take a downtime.
Take a backup and snapshot immediately after your downtime goes into effect and you verify traffic has stopped.
If you're running MYISAM, use 'CHECK TABLE' to evaluate what you're dealing with before you ALTER.
Copy the table locally in addition to your backup, just in case.
Proceed with caution, enable "--show warnings" and other output so you have the full picture as you make your changes.
If the data is important to you, hire a DBA, even if just to consult during the migration so you have a seasoned veteran by your side.

There's probably a lot more I could get into, but the above will provide you with options when something goes wrong.

As far as your missing data/rows, there's no way to know w/o a "before/after" snapshot to compare. You can compare against your latest backup to at least verify that much.


is this time to remove the databases from the availability group? which ones? While patching one of my servers that is part of an availability group,
as a secondary, I had some problems  - the real problem was that the host machine of one of the virtual servers in the availability group - run out of disk space for a brief minute or two.

It does not matter what was the problem, for this question.

The facts are:

I had 2 machines on the availability group, lost the secondary.
The availability group was involved in replication and I was getting the following error message:

  Replicated transactions are waiting for next Log backup or for
  mirroring partner to catch up

which was solved by starting the following trace flag:

DBCC TRACEON(1448,-1)

I find some info about my availability group, and I can see one server is down according to the scripts below:

select * from sys.dm_hadr_cluster_members

SELECT
gs.primary_replica as 'Primary Server',
DB_NAME(dd.database_id) as 'Database Name',
secondary_recovery_health_desc as 'Secondary Server Health Status',
synchronization_state_desc as 'Synchronization State',
database_state_desc as 'Database State',
--suspend_reason_desc as 'Suspended Reason',
--last_sent_time as 'Last Data Send Time',
--last_received_time as 'Last Data Received TIme',
--last_hardened_time as 'Last Hardened Time',
--last_redone_time as 'Last Redone Time',
--log_send_queue_size as 'Log Send Queue Size',
--log_send_rate as 'Log Send Rate',
--redo_queue_size as 'Redo Queue Size',
--redo_rate as 'Rate of Redo',
--filestream_send_rate as 'Filestream Send Rate',
last_commit_time as 'Last Commit Time',
low_water_mark_for_ghosts as 'Low Water Mark for Ghosts'
FROM sys.dm_hadr_availability_group_states as gs
left outer JOIN sys.dm_hadr_database_replica_states as dd ON gs.group_id = dd.group_id
ORDER BY gs.primary_replica DESC, DB_NAME(dd.database_id) DESC

The always On script at this question does not tell me much either

QUESTION:

How much far behind am I in the transaction log, catching up with a replica that will never come back?

I need information to solve this question:

Should I remove the databases from the availability group?

or should I build a new server, add it to the availability group, wait for the logs to catch up?

I have decided to remove the databases from the availability group, on this occasion.
for this I have used the following scripts:

-- https://blog.pythian.com/list-of-sql-server-databases-in-an-availability-group/
-- How do we know that this instance is part of Availability Group(s):
SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

-- https://blog.pythian.com/list-of-sql-server-databases-in-an-availability-group/
-- How do we get some information about the databases in the Availability Group:
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined],
remove_database='USE [master]' + char(13) + 'ALTER AVAILABILITY GROUP ' + quotename(AG.name ) + char(13) + 'REMOVE DATABASE ' + quotename(dbcs.database_name) + char(13)
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
   ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name


Add database to availibility group I have two replicas in availability group. Also I have additional instance instance3, with dummy databases that are not in availability group.
As time has passed, some new things started to be stored on that dummy database and now I have to add it to Availability group.

I followed user guide as described here:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database
but when I get to specify new database, in wizard, that I want to add to availability group, I don't see that database in the list.

Did I miss some step in the middle or I am doing it completely wrong?

Additional note is that there is a database with same name but from another instance in availability group. Will that maybe create problem? I guess there can't be two DB with same name and now is little late to change name of DB.

It sounds like you are trying to add a database to your AG from a node other than the primary (is instance3 even part of your cluster), you can't do that.  You would have to fail over to instance3 and then add it.  You could also take a backup of your database, restore it to the primary, then go through the steps to add it again (you'll have to do a full backup and log backup from the primary).

Your biggest problem is going to be the name.  You are correct, you cannot have two databases with the same name.  It will have to change or you'll have to move it to a different cluster.


MySQL 5.7 Can slave be ahead of master during replication From my understanding, once commit() is received from client, transactions are written to binlog first before any commit is done to InnoDB.

In this case, is it possible for slaves to be ahead of master if master is suffering from some performance issues?

On one occasion, I helped a company that had a replica ahead of its master. It was a very odd situation. It isn't supposed to be possible.

What caused it was that their master server's hard drives were failing, and data was not able to be written to disk. So the data committed to the InnoDB redo log and tablespace wasn't being saved.

But the transactions were committed as far as MySQL knew, and it was writing to the binary logs, which used buffered I/O, so the operating system still had a portion of the file in RAM, trying to sync to disk.

Then the replica requested the latest binary log events, and the master sent them, and the replica faithfully applied them to its database. Since the replica's disk was not having faults, it saved to disk fine.

The master server finally rebooted after many disk I/O faults. It came back up and was able to start processes. MySQL read its InnoDB redo log file, trying to do crash recovery, but the latest transactions had never been written to the disk, so they were not able to be recovered.

But the replica had gotten those events!

Thus the replica was slightly ahead of its master.


Recording history of ranks Once per day, this is being executed to rank everyone in the database based on points.

    SET @r=0;
    UPDATE ranks SET previousRank=rank, rank= @r:= (@r+1) ORDER BY points DESC;

The field previousRank contains their previous rank. However, we are now wanting to record the history of the rank.

If we change previousRank to a STRING, is it possible to modify the query to append "rank," to the end of field previousRank instead of replace it? For example, it would look like this: 21,22,20,18,24,.

If so, how would this be done, and are there any steps that can be taken to remove the end comma without slowing the query?

If you want previousRank to end with a comma

SET @r=0;
UPDATE ranks SET
    previousRank=CONCAT(previousRank,rank,','),
    rank= @r:= (@r+1)
    ORDER BY points DESC
;

If you do not want previousRank to end with a comma

SET @r=0;
UPDATE ranks SET
    previousRank=IF(previousRank='',rank,CONCAT(previousRank,',',rank)),
    rank= @r:= (@r+1)
    ORDER BY points DESC
;

Give it a Try !!!


Catch OLE DB provider "SQLNCLI10" for linked server "[Some Server]" returned message "Query timeout expired" Without changing the settings on servers, etc to the linked server connections, etc, is there a way to catch the dreaded?

"OLE DB provider "SQLNCLI10" for linked server "[Some Server here]" returned message "Query timeout expired""
"OLE DB provider "SQLNCLI11" for linked server "[Some Server here]" returned message "Query timeout expired""

and either have the procedure retry to run or continue on to the next process in a cursor?
We have a dynamic SQL Server system set up to call procedures based on a specific process we're running.  This works fine but every once in a while, one of the steps will throw the above error and of course a traditional Try...Catch block doesn't capture it.
I've searched for several hours on this and I need a way to be able to "capture" this error and either retry the procedure or skip the procedure but not exit the cursor (so go to the next process in line).  We have a 2016 and a 2008 R2 servers communicating with each other.
Right now we have a single cursor running with 20 such steps in it (we are trying to use this as a job task replacement) but eventually we will have 30 or 40 processes, with tasks totaling well into the hundreds using this system.
Please let me know if you have any ideas.

Simply reconfigure the Linked Server to have no timeout.  Or at least a longer timeout value.

EXEC master.dbo.sp_serveroption @server = N'<server_name>'
    , @optname = N'query timeout'
    , @optvalue = N'1200';  --1200 second timeout

To configure the server so that no linked servers ever timeout, make this change:

EXEC sys.sp_configure 'remote query timeout', 0;

Then ensure the linked server has sp_serveroption 'query timeout' is set to 0.


Do you still need both the 32 bit and 64 bit connectors installed for SSIS 2016? We've recently installed SQL Server 2016 on development including SSIS, and we're in the process of migrating our work over to this new server. Part of this work involves migrating packages that involve interfacing with other DBMSs. To do that, I need to install connector drivers again. I know that for past versions of SSIS you had to install both the 32 and 64 bit versions of ODBC connector drivers, as parts of SSIS ran in 32-bit no matter what while other parts might run in 64-bit. Does this behavior and requirement hold for SSIS 2016, or does it suffice to just install the 64-bit drivers now?

In the case of DBMSs that only offer ODBC connectors to interface with SSIS (Among other things) the answer is that only the 32-bit connector needs to be installed to make an ODBC data source that SSIS 2016 can recognize. For my environment this was the case with IBM Informix and Progress OpenEdge.

In the case of Oracle the prevailing wisdom I read back when I was working with SSIS 2012 still applies. I had to install both the 32 and 64 bit instant clients, configure symbolic links so that Windows could leverage both of them as it needed, and make the appropriate TNS files and environment variables for the Oracle connector to function.

MySQL worked with just the most recent, 64-bit .NET connector. The same applied to Teradata. It seems that only ODBC connectors and Oracle need 32-bit access.


Can jsonb do fast search by *value* or even a partial value? Other types? I have a person table where arbitrary data must be inserted. Since I'm using Postgres 9.4, jsonb appears to be the right choice.

Example data:

id: 1, name: "Joe Doe", preferences: { color: "red" , toy: "car"}
id: 2, name: "Jane Doe", preferences: { color: "blue", food: "hamburguer" }

The problem is that I need to query it by the variable values, example:

All the people who has "hamburger" on preferences, and partial queries must be possible too, so searching for "burg" must bring me record 2.

Using json functions, I can search for values, as long as I known the key, right? Or there is a fast way to search for the values?

What I'm thinking to do is to create a tsvector column and dump the json to use fulltext search to do the partial queries.

I suggest a MATERIALIZED VIEW with unnested values and a trigram index as search tools.

hstore

If you don't need nested values, hstore may be even better for you. Use the function svals(hstore) to unnest hstore values.

You need to install the additional module hstore once per database:

CREATE EXTENSION hstore;

Key value pair in PostgreSQL

Table

CREATE TABLE person AS
SELECT * FROM (
   VALUES
     (1, 'Joe Doe', hstore  'toy=>car, color=>red')
   , (2, 'Jane Doe', 'food=>hamburguer, color=>blue')
   ) t(person_id, name, preferences);

Materialized view

CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.preference        -- just pref
FROM   person p, svals(p.preferences) j(preference);

This is an implicit CROSS JOIN LATERAL to the set-returning function svals().

Trigram Index

You need to install the additional module pg_trgm once per database:

CREATE EXTENSION pg_trgm;

Then:

CREATE INDEX person_pref_j_trgm_idx ON person_pref_j
USING gin (preference gin_trgm_ops);

Details:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Query

SELECT *
FROM   person p
WHERE  EXISTS (
   SELECT 1
   FROM   person_pref pp
   WHERE  pp.person_id = p.person_id
   AND    pp.preference ILIKE '%burg%'
   );

Be aware that this is pretty fast.

jsonb

If you have nested values or numeric or boolean values, jsonb may be more efficient. You can do the same as above with jsonb_each_text(jsonb):

CREATE TABLE person AS
SELECT * FROM (
   VALUES
     (1, 'Joe Doe', jsonb  '{"toy": "car", "color": "red"}')
   , (2, 'Jane Doe', '{"food": "hamburguer", "color": "blue"}')
   ) t(person_id, name, preferences);

CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.key, j.preference   -- incl. key or just pref?
FROM  person_j p, jsonb_each_text(p.preferences) j(key, preference);

Same index, same query. You might want to add the key to the MV and search for that, too:

CREATE INDEX person_pref_trgm_idx ON person_pref_j
USING GIN (key gin_trgm_ops, preference gin_trgm_ops);


Performing search on a column by query Up until today I've used LIKE %term% to query for rows on particular column term. Then I've read about FULL TEXT index. And I think I misunderstood how full text index works.

I have an API that returns categories. On the front-end I have an input box that auto-completes the input as the user types. So assuming I have the following categories:

House keeping
Entertainment and recreation
Taxes and bills

When the user types "ta" I'd like to offer them both "Entertainment and recreation" and "Taxes and bills". Needless to say that I've discovered that full text index wont return the given result on the term "ta". Looks like full text index works on actual text like "tax" or "bill" and not on meaningless phrases like "ta" or "ou".

Am I using fulltext index incorrectly and I should use the old good (bad?) LIKE %ta%? Or are there any other ways to achieve described functionality that would also be db efficient (consider the fact the the front-end will query the DB on each character the user inputs).

For references: I'm using PostgreSQL and my query looks like select * from categories where to_tsvector('english', name) @@ plainto_tsquery('english', 'ta')

Take a look at Amazon's search. I type a phrase like "hou" and it offers me results like "house decor" "house of cards" "housewarming". How such search performed? By a regular index on the column + LIKE %term%?

I know there are solutions like elastic search of sphinx. I don't know if they are suited in my case, but for now I want to stay inside the database to minimize costs and time in favor of faster development.

Postgres Text Search supports prefix search

select 'housewarming'::tsvector @@ 'hou:*'::tsquery; --true

I looked at amazon's search, and guess what? ta doesn't return entertainment


Can I do an impdp with a file on my local computer I was given a dump file which is currently on my local computer.  I want to import this to our Oracle 11G DB which is hosted on a server.  Do I need to move this file to the server to do the impdp or is there a way that I can do the impdp while referencing that the file is on my local PC?

Should be possible, provided the user that the DB service is running as can access the file share.

If the above is true, then you would have to create the directory object:

create directory MYIMPORTDIR as '\\mycomp\mydir';

then run your impdp like:

impdp [username]/[pass] directory=MYIMPORTDIR dumpfile=expdat_1234.dat


Error 1290 despite of fixing my.ini file and having FILE permissions I tried loading data from a file but got ERROR 1290.

mysql> show variables like 'secure_file_priv';
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.01 sec)

mysql> load data infile "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\Book1.csv"
into table sample fields terminated by ',';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option
so it cannot execute this statement

I've aready tried the below link but didn't help.

    https://stackoverflow.com/questions/34102562/mysql-error-1290-hy000-secure-file-priv-option

I also have FILE privilege for the current_user.

That's how Book1.csv looks

1,A,A1
2,B,A2
3,C,A3
4,A,A4
5,B,A5
6,C,A6
7,A,A7
8,B,A8
9,C,A9
10,A,A10

I've spent hours. Pls help me out.

In windows, you need to give double slash "\" in the path.

   load data infile "C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\Book1.csv" into table sample fields terminated by ',';

You can give reverse slash "/" as below

   load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Book1.csv" into table sample fields terminated by ',';

Give it a try.


How can I create different sets of sequential values based on another column in a table? I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table.

CREATE TABLE Notes (
   Id int IDENTITY(1,1) NOT NULL,
   Type int  NOT NULL,
   CustomerId int NOT NULL,
   -- etc
)

I am migrating data from a legacy system into this table and there is a requirement that Collection and Delivery notes have their own sequential numbers.

I had previously implemented two sequence tables as

CREATE TABLE CollectionNoteSequence (
    Id int IDENTITY(1,1) NOT NULL,
    NoteId int NOT NULL
)

where the Id column is the unique, sequential Id for collection notes, and then the NoteId foreign key's to Notes.Id.

It's getting towards time to do the final (real) data migration and this setup seems hard to work with.

Is there a way I could bin the two sequence tables and add a NoteNo field to the Notes table such that NoteNo would be sequential depending on the Note.Type ? Is this a composite key or something?

The new table might look like

CREATE TABLE Notes (
   Id int IDENTITY(1,1) NOT NULL,
   NoteNo int NOT NULL,
   Type int  NOT NULL,
   CustomerId int NOT NULL,
   -- etc
)

and the data would look like:

Id    NoteNo    Type    CustomerId
1     4000      1       123
2     4001      1       456
3     15123     2       789
4     4002      1       753
5     15124     2       741

I'm using MS SQL Server 2008.

You could use partition by on the note type, which allows a calculation to be reset by groups.  For example:

create table OldNotes (
       OldNoteRef           varchar (20) not null
      ,OldNoteType          varchar (20)
      -- Other data goes here
)
go

insert OldNotes (OldNoteRef, OldNoteType) values ('CABC123', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC456', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC789', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ001', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ034', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('XZYZ100', 'D')
go

select OldNoteRef
      ,OldNoteType
      ,row_number() over (
          partition by OldNoteType
          order by OldNoteRef
      ) as NewNoteID
  from OldNotes
go

Would produce the following results, which will give you a sequence within the note type.

╔════════════╦═════════════╦═══════════╗
║ OldNoteRef ║ OldNoteType ║ NewNoteID ║
╠════════════╬═════════════╬═══════════╣
║ CABC123    ║ C           ║         1 ║
║ CABC456    ║ C           ║         2 ║
║ CABC789    ║ C           ║         3 ║
║ DXYZ001    ║ D           ║         1 ║
║ DXYZ034    ║ D           ║         2 ║
║ XZYZ100    ║ D           ║         3 ║
╚════════════╩═════════════╩═══════════╝

BTW, table produced by http://www.sensefulsolutions.com/2010/10/format-text-as-table.html


MEMORY_OPTIMIZED table as replacement for temporary tables I use temporary tables for complex processing which is done in several "child" procedures. These procedures are working in the same session, so the temp tables are accessible for them.

Is there a similar option for memory-optimized tables?

In your case, you'd have to use a memory-optimized table variable, and assuming that the child procedures are called by the same SPID, there shouldn't be an issue with visibility. Please see this link:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization

I will caution you that the cardinality estimator still assumes 1 row for memory-optimized table variables, and this can be an issue for performance.

More info at this link:

https://blogs.msdn.microsoft.com/psssql/2017/05/10/memory-optimized-table-variable-and-cardinality-estimate/


Connecting to SQL Server using cached credentials While migrating from SQL authentication (user-password) to integrated security I ran into an unexpected combination of requirements.

SQL clients are services running under a LOCALSYSTEM or NETWORKSERVICE account
SQL clients use integrated security to connect to the local SQL Server
The system is in an AD domain.
Database connections must be successfully established even when network connectivity to the domain controller is down for hours or days at a time.

On the luckier side, the SQL clients and SQL Server (2005 SP4) always run on the same box.

The conclusion of reading, experiments done so far and a support call to MS seems to have been that I cannot meet all these requirements at the same time, regardless of whether Kerberos or NTLM is used.

On the other hand I see some references to cached credentials that are giving me hopes that it might be possible to somehow connect to the database using cached credentials, under some conditions, following a previous successful domain login (service startup, or previously established connection).

Is anyone successfully using cached domain credentials to connect to a local SQL Server from a service running under a domain account, during prolonged DC outages?  How is that configured?  What are the security implications?
Are the four requirements above really incompatible?

Service accounts can't make use of cached credentials.  Only interactive users can use cached credentials.

As everything is running on the same server, create local Windows accounts and setup the services to run under those local Windows accounts.  Local accounts have no requirement that Active Directory actually be online.


Multiple foreign keys on single column I have a table Item with many items of many types. Differently typed items are linked to different resources each of whom have a table with primary key.

Currently to achieve this I have two columns in Item table: Resource type and Resource ID.

The question is, what would be propper way to refactor Item table in a way so I can set up foreign keys to Resource ID?

Preferably without creating many new tables just for this reason or without adding many new columns to Item table, where most of them would be empty.

**EDIT**

Additional note: aside from resource id's, all items also have multiple other fields, some of which may be used only by some Resource type.

As far as I have looked it up, I am seeing 4 options:

Have a table Item with columns Resource type and Resource ID as well as all additional columns (case explained above).

(+) smaller amount of unused cells
(-) some unused cells
(-) no foreign keys on Resource ID

Have a table Item with additional column for each Resource type as well as all additional common columns.

(+) availability of foreign keys on Resource ID
(-) a lot of unused cells

Have a table Item with only common columns (or even with only Item ID) + additional table with columns: ID, Item ID, Value type, Value BIGINT, Value INT, Value STRING. Second table will be used for Resource ID's as well as fields that are unique for one or another Resource type.

(+) smaller and constant amount of unused cells
(+/-) more space per unique field, but overall smaller than in other cases.
(-) no foreign keys on Resource ID

New table for each Resource type

(+) availability of foreign keys on Resource ID
(+) more control over each Resource type
(-) a lot of new tables, number of which will increase as new Resource type's will be introduced.

I am now evaluating, which of these cases will be more beneficial to my case.

It was decided to go with something between second and third options - adding additional table with columns Value type and separate columns for each Resource type.

(+) easier to manage in code
(+) availability of Foreign Keys on Resource ID
(+) no overhead on table count
(+) ability to add multiple different Resource type's to each Item
(-) uses extra space for each unused Resource type for each field
(-) lacks ability to cascade delete related Item, when resource with Resource ID is deleted.


How to fetch the records which is belongs to only one group in postgresql I want to fetch the student_id which is only belongs to one collage
expected op
6 and 7 stud_id's so how can i do this?

 _id | stud_id | collage_id | university_id  | active
-----+---------+------------+----------------+--------
   1 |       2 |          2 |              2 | t
   6 |       2 |          3 |              3 | t
   7 |       6 |          2 |              2 | t
   8 |       7 |          2 |              2 | t
   9 |       8 |          2 |              2 | t
  10 |       8 |          3 |              2 | t

and also if i need fetch the data which is belongs to perticular collage , i mean to use where clause ? thank you

You could use count(distinct collage_id) grouping by stud_id in this way:

select   stud_id
from     tbl
group by stud_id
having   count(distinct collage_id) = 1;

| stud_id |
| ------: |
|       6 |
|       7 |

db<>fiddle here

If you want to fetch rows filtering by collage_id:

select *
from   tbl
where  collage_id = 3
and    stud_id in (select   stud_id
                   from     tbl
                   group by stud_id
                   having   count(distinct collage_id) = 1);

Note I've modified a bit your sample data.

_id | stud_id | collage_id | university_id | active
--: | ------: | ---------: | ------------: | :-----
  8 |       7 |          3 |             2 | t

db<>fiddle here


How to get the type of geometry column in postgres? I need to determine the type of geometry column and doing it with the following query:

SELECT type
FROM   geometry_columns
WHERE  f_table_schema = 'public'
       AND f_table_name = 'table_name'
       AND f_geometry_column = 'col_name'

It works fine for regular geometries, like Point or Polygon. But then there are also some fancy geometries like PointZ. How do I determine if column has it? Above mentioned query returns just POINT for it.

The geometry_columns view takes care to remove any Z/M suffixes.

You can look up the PostGIS type modifier directly in the system tables:

SELECT postgis_typmod_type(atttypmod)
FROM pg_attribute
WHERE attrelid = 'public.table_name'::regclass
  AND attname = 'col_name';

(The same can be done with postgis_typmod_srid().)


SSIS cannot deploy package to SQL server - An error occurred during decryption 15466 After recovering and re-importing a backup of a SQL Server 2019 instance none of my SSIS packages will deploy from Visual Studio.
Whenever I try and deploy I get the error
===================================

An error occurred during decryption. (.Net SqlClient Data Provider)

------------------------------
Server Name: SSRS02\HEALTHAWARE
Error Number: 15466
Severity: 16
State: 9
Line Number: 1

I have tried to reimport the backup of the encryption key that I have through the Report Server Configuration Manager, and have run the following and have seen it succeed, so I am assuming that my master key is correct, and I have the correct decryption password for the master key.
OPEN Master KEY
    DECRYPTION BY PASSWORD = 'xxxxxxxxxxx';
ALTER Master KEY
    DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER Master KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
GO

I cannot however figure out what I need to do in order to get the SSIS Packages to deploy from Visual Studio

i believe you have to encrypt it again using the ssisdb original password in this way:
open master key decryption by password = 'xxxxx' –‘Password used when creating SSISDB’
Alter Master Key Add encryption by Service Master Key


Could database with compat level 100 support columnstore index? I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactOnlineSales ON dbo.FactOnlineSales

The columnstore index was successfully created.
My question is related to the compat level. As far as I know, the columnstore index concept itself was came out in SQL Server 2012. Moreover, the clustered columnstore index was released in SQL Server 2014. How come database with compat level 100 support clustered columnstore index? It seems that database still inherits features of the current instance.

Compat level is there to provide a higher degree of backwards compatibility. We can put this into a few categories:
Language elements that has been removed in a higher version of SQL server can still be available in that version of you run with a lower compat level. There aren't many of those nowadays.
Behavior changes can keep the old behavior with the lower compat level.
New language elements that in a higher version now are keywords/reserved words. I.e., if you have an object named this way ("identifier", formally), you will in this higher version have to [delimit] when you reference that identifier. In order to provide backwards compatibility, in a lower compat level, those new language elements won't be available. Note, however, that there are plenty of new language elements that do not introduce new keywords, and don't have to be blocked in the lower compat level.
Performance. Use a lower compat level, and you are likely to have the same execution plans as if you were running in that version. I.e., no performance surprises when you upgrade the engine version assuming that you keep the old db compat level. Ideally.
There is no need to block new features if allowing them don't introduce backwards compatibility issues. Your columnstore index is such an example.
As for exactly what each compat level do, this is well documented here: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level


Implications of many materialized views in Postgres? I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be calculated for a specific time period using a Postgres function and then stored in a materialized view to avoid having to recalculate every time.

The system I'm working on serves multiple businesses, each with their own calendar and customers. My thought was that a materialized view would be created on a per-business basis. However, if the service is successful, there could be well over a 1000 businesses using the system. That said, are there issues with having that many materialized views? And if so, is there a better pattern that would scale as the service grows?

FYI: I'm running Postgres 9.4

Thank you!

I know of no such limit, although I have never had more than tens of materialized views, not thousands.  However a way of hedging against such a problem is to make one materialised view with the customer as a column and create an index on the customer column.  That way, Postgres will be able to pick out the relevant parts of the view very quickly.  You should have performance that is not significantly worse than having lots and lots of views.  It will also be a lot less work to maintain.