Group COUNT by month less than variable In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus:

CREATE TABLE [dbo].[Employee]
(
    [employeeID] [int] IDENTITY(9500,1) NOT NULL,
    [firstName] [nvarchar](50) NOT NULL,
    [middleName] [nvarchar](50) NULL,
    [lastName] [nvarchar](50) NOT NULL,
    [suffix] [nvarchar](50) NULL,
    [createdDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_createdDate]  DEFAULT (getdate()),

    CONSTRAINT [PK_Employee]
        PRIMARY KEY CLUSTERED ([employeeID] ASC)
             WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[EmployeeStatus]
(
    [employeeStatusID] [int] IDENTITY(1,1) NOT NULL,
    [employeeID] [int] NOT NULL,
    [validEmployeeStatusID] [int] NOT NULL,
    [statusChangeBeginDate] [datetime2](7) NOT NULL
         CONSTRAINT [DF_EmployeeStatus_statusChangeBeginDate]  DEFAULT (getdate()),
    [statusChangeEndDate] [datetime2](7) NULL,
    [createdDate] [datetime2](7) NOT NULL
         CONSTRAINT [DF_EmployeeStatus_createdDate]  DEFAULT (getdate()),

    CONSTRAINT [PK_EmployeeStatus]
         PRIMARY KEY CLUSTERED ([employeeStatusID] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I'd like to get COUNT(distinct e.employeeID) for each month less than the @month variable. Here's what I've tried:

declare @year int = 2016, @month int = 7

select
    month(es.statusChangeEndDate) as Month,
    count(distinct e.employeeID) as EligibleEmployees
from
    Employee e
left join
    EmployeeStatus es on e.employeeID = es.employeeID
where
    ((es.validEmployeeStatusID is null) or
        not exists (select 1
                    from Employee emp
                    join EmployeeStatus emps on emp.employeeID = emps.employeeID
                    where
                        (emps.statusChangeEndDate is null or
                        (year(emps.statusChangeEndDate) = @year
                            and month(emps.statusChangeEndDate) > @month))
                ))
    group by month(es.statusChangeEndDate)

The employeeID column serves as foreign key on the EmployeeStatus table. An employee can have 0 or more statuses on the EmployeeStatus table. Note that I'm excluding employees who have a null value for statusChangeEndDate or the  month of their statusChangeEndDate is greater than @month variable (in this case 7).

What I'd like to get is a result set like this:

Month  EligibleEmployees
=====  ===================
1      2567
2      2763
3      2595
4      2673
5      2792
6      2499

However, it returns something like this:

Month  EligibleEmployees
==========================
NULL   2567

Your helps is appreciated. Thanks. [I had this question on the main Stack Overflow but realized this is a better place for it. Deleted that question.]

Your not exists sub-query doesn't work. It will always return a 1 if ANY employee exists that has a null statusChangeEndDate or that meets the criteria you are testing so basically will always exist. To continue using a Not Exists you'd need to make it correlated sub-query by adding e.employeeID = emp.employeeID so it is checking to see if that employee doesn't have a null record or a record for later than this year. Doing it that way probably wouldn't scale well for performance as the correlated sub-query needs to be run for every line in the results.

You could change it to

e.employeeid not in ( select emp.employeeid from Employee emp
join EmployeeStatus emps on emp.employeeID = emps.employeeID
        where
        (emps.statusChangeEndDate is null or
             (year(emps.statusChangeEndDate) = @year
               and month(emps.statusChangeEndDate) > @month))

Your attempt at SQL seems to have more criteria than your description as it is including people that have nothing in the EmployeeStatus table (left join and is null) and is trying to exclude people who have a change end date after the entered date or have a null change end date. That is subtly different than finding people who have a change end date before your entered date since people can have multiple changes.


Dropped Database Connections in SQL Server 2008R2 We are having an annoying intermittent problem where database connections from our client application on a variety of stations will get dropped.  We have applications that use ODBC, Borland Database Engine, and ADO and all get dropped.  When connections do get dropped, it isn't everyone - some remain connected while others get dropped.  Looking in the logs of one of the applications, I see messages like the following:

TCP Provider: The semaphore timeout period has expired
TCP Provider: An existing connection was forcibly closed by the remote host
Query timeout expired
TCP Provider: No such host is known

I don't see any error messages in the SQL error log or in the Windows events on either the client or the SQL Server.  Is there anyway to determine if this is a database or network issue from the SQL Server side of things?  For example, is there something I can turn on to monitor database connections and get reports when they are dropped?  This happens maybe once or twice a day but not at any specific time (not when backups run or something).

The issue seems to be network related and out of scope for this forum.

It can be SQL related, not just OS and hardware:

https://support.microsoft.com/en-us/kb/2491214


Remove all instances of a character from a column within tag I don't know much about writing SQL and a client of mine has an erroneous semicolon appearing in his database that he'd like removed. It's in 1 specific column, but it should only be removed if it is within an  tag. For example:

MyColumn
--------

This is okay; but  is not

Can this be done?

Short answer - yes, it could be done

Correct and full answer depends from other information

For single (or predefined number of tags) - You can prepare UPDATE SQL for XML nodes and use REPLACE T-SQL function for updated parameters

The is will work if You need remove all ";" from TAG, if logic more complicated - solution will not work.


Use sp_DatabaseRestore for restore when database does not exist I am trying to use sp_DatabaseRestore from the SQL Server First Responder Kit to restore some databases. It works fine if I first restore the database in a normal way using SSMS, but if the database does not previously exist, it says that it could not find the file specified (for both data an log-files).

It also ends with the following message:

  The database "DatabaseName" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.

But the first path is for the full backup, so I am not sure what this means.

Is there some particular use of the parameters that would help me use this even when the database does not exist, or am I forced to create them first?

Any suggestions appreciated.

Edit:
Code that is used (paths and names have been changed). The code fails if the database does not exist. If I create the database, then it works:

DECLARE @VersionDate datetime;
EXEC dbo.sp_DatabaseRestore @Database = N'DatabaseName',
                            @BackupPathFull = N'\\SERVERNAME\Backup\servername-wfcs$ServerNameAG01\DatabaseName\FULL\',
                            @BackupPathLog = N'\\SERVERNAME\Backup\servername-wfcs$ServerNameAG01\DatabaseName\LOG\',
                            @MoveFiles = 0,
                            @ContinueLogs = 0,
                            @RunRecovery = 1,
                            @ForceSimpleRecovery = 0,
                            @Debug = 0,
                            @VersionDate = @VersionDate OUTPUT

Edit 2: Turns out there was a difference in the data-file folder names between the old and the new server. The old server had a version-number lower than the actual version of the SQL Server, probably due to an earlier in place upgrade. When trying to restore from a backup with correct data folder, there is no problem.


Identifying postgres db health tool similiar to mysqlcheck I'd like to run a check against databases in postgresql to verify that everything is ok after the server crash. On mysql it would be mysqlcheck. I would like to know the postgres equivalent.

As of 9.2, there is no such tool, and probably wont be for some time.   There have been efforts to put checksum per page but that has not happened yet due to what has been referred to as a set of "deadly traps."  Until that happens, I doubt that such tools will even be feasible beyond some very basic cases.  So the answer, I am afraid is that the infrastructure required to do this efficiently is not part of the system yet.

Assuming working hardware, drives that don't lie about write completion, sane settings, etc. it should be impossible for something like an unclean shutdown to cause corrution in PostgreSQL but I have seen it a few times (overheating machines, and the like).  So I do agree with you about the need.  It's just worth noting that it is not that easy to do.


How to use a variable for object's owner (schema)? In Oracle we can use a variable instead of the object's owner. The value of this variable will be prompted by Oracle when the scripts starts.

Something like CREATE TABLE **&owner.**MY_TABLE.

I'm trying to accomplish the same thing with SQL Server but I failed to find how to do it.

In modern versions of SQL Server, this is actually the schema, not the owner. You can't parameterize this and must concatenate a command:

DECLARE @schema sysname = N'owner';

DECLARE @sql nvarchar(max);
SET @sql = N'CREATE TABLE ' + QUOTENAME(@schema) + N'.my_table(cols...);';
EXEC sys.sp_executesql @sql;

If this is being specified by an end user, you may want to validate that it is a proper schema first, e.g.:

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = @schema)
BEGIN
  RAISERROR(N'Invalid schema %s!', 11, 1, @schema);
  RETURN;
END

A really lazy way is to set the callers default schema to the target, and then don't specify the schema name. This is a bad idea but I'm mentioning it for completeness.


Use SPLIT_STRING with OPENROWSET Is there a way to pass the result from OPENROWSET BULK into a variable so i can use STRING_SPLIT to divide it into rows?

Something like:

    DECLARE @MyString = FROM OPENROWSET (BULK 'C:\Temp\File.csv', SINGLE_CLOB) MyFile

SELECT * FROM STRING_SPLIT ( @MyString , '\n' )

Here is one solution.

I used REPLACE to replace the CR/LF with a single character I knew would not be in my file (tilde) and then used that in STRING_SPLIT

DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @BulkColumn VARCHAR(max)

SELECT @BulkColumn = replace(BulkColumn, @CrLf, '~')
FROM OPENROWSET(BULK 'C:\Temp\z.txt', SINGLE_CLOB) MyFile

SELECT *
FROM string_split(@BulkColumn, '~')

| value |
|-------|
| one   |
| two   |
| three |


postgresql won't install dependencies Please help me figure out why postgresql isn't playing nice with updating.  I can't find anything on SO or Google that remedies this problem.  This is a production server and a distribution reinstall is not really an option.  apt-get install -f and apt-get autoclean/check will tell you to use apt-get install -f.

root@vps1484:/home# apt-get install -f
Reading package lists... Done
Building dependency tree
Reading state information... Done
Correcting dependencies... Done
The following package was automatically installed and is no longer required:
  libaio1
Use 'apt-get autoremove' to remove it.
The following extra packages will be installed:
  postgresql-9.1
Suggested packages:
  oidentd ident-server locales-all
The following packages will be upgraded:
  postgresql-9.1
1 upgraded, 0 newly installed, 0 to remove and 14 not upgraded.
1 not fully installed or removed.
Need to get 0 B/4317 kB of archives.
After this operation, 193 kB disk space will be freed.
Do you want to continue [Y/n]? y
(Reading database ... 98727 files and directories currently installed.)
Preparing to replace postgresql-9.1 9.1.13-0ubuntu0.13.10 (using .../postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb) ...
 * Stopping PostgreSQL 9.1 database server                                                                                [ OK ]
Unpacking replacement postgresql-9.1 ...
dpkg: error processing /var/cache/apt/archives/postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb (--unpack):
 trying to overwrite '/usr/share/postgresql/9.1/man/man1/pg_basebackup.1.gz', which is also in package postgresql-client-9.1 9.1.13-0ubuntu0.13.10
Errors were encountered while processing:
 /var/cache/apt/archives/postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@vps1484:/home# sudo rm /usr/share/postgresql/9.1/man/man1/*
root@vps1484:/home# sudo rm /var/cache/apt/archives/*
rm: cannot remove '/var/cache/apt/archives/partial': Is a directory
root@vps1484:/home# sudo rmdir /var/cache/apt/archives
rmdir: failed to remove '/var/cache/apt/archives': Directory not empty
root@vps1484:/home# apt-get install -f
Reading package lists... Done
Building dependency tree
Reading state information... Done
Correcting dependencies... Done
The following package was automatically installed and is no longer required:
  libaio1
Use 'apt-get autoremove' to remove it.
The following extra packages will be installed:
  postgresql-9.1
Suggested packages:
  oidentd ident-server locales-all
The following packages will be upgraded:
  postgresql-9.1
1 upgraded, 0 newly installed, 0 to remove and 14 not upgraded.
1 not fully installed or removed.
Need to get 4317 kB of archives.
After this operation, 193 kB disk space will be freed.
Do you want to continue [Y/n]? y
Get:1 http://ca.archive.ubuntu.com/ubuntu/ precise-security/main postgresql-9.1 amd64 9.1.15-0ubuntu0.12.04 [4317 kB]
Fetched 4317 kB in 0s (5940 kB/s)
(Reading database ... 98727 files and directories currently installed.)
Preparing to replace postgresql-9.1 9.1.13-0ubuntu0.13.10 (using .../postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb) ...
 * Stopping PostgreSQL 9.1 database server                                                                                [ OK ]
Unpacking replacement postgresql-9.1 ...
dpkg: error processing /var/cache/apt/archives/postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb (--unpack):
 trying to overwrite '/usr/share/postgresql/9.1/man/man1/pg_basebackup.1.gz', which is also in package postgresql-client-9.1 9.1.13-0ubuntu0.13.10
Errors were encountered while processing:
 /var/cache/apt/archives/postgresql-9.1_9.1.15-0ubuntu0.12.04_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@vps1484:/home# >

This is how I fixed my issue:

`dpkg -l | grep postgres

will show you the postgresql packages that have been installed.  Run:

 sudo apt-get --purge remove

with each package name, separated by a space, following remove.

root@vps1484:/home# dpkg -l | grep postgres
ii  postgresql                                                  9.3+146really9.1+148             all          object-relational SQL database (supported version)
ii  postgresql-9.1                                              9.1.13-0ubuntu0.13.10            amd64        object-relational SQL database, version 9.1 server
ii  postgresql-client                                           9.3+146really9.1+148             all          front-end programs for PostgreSQL (supported version)
ii  postgresql-client-9.1                                       9.1.13-0ubuntu0.13.10            amd64        front-end programs for PostgreSQL 9.1
ii  postgresql-client-common                                    148                              all          manager for multiple PostgreSQL client versions
ii  postgresql-common                                           148                              all          PostgreSQL database-cluster manager
ii  postgresql-contrib                                          9.3+146really9.1+148             all          additional facilities for PostgreSQL (supported version)
iU  postgresql-contrib-9.1                                      9.1.15-0ubuntu0.12.04            amd64        additional facilities for PostgreSQL
root@vps1484:/home# sudo apt-get --purge remove postgresql postgresql-9.1 postgresql-client postgresql-client-9.1 postgresql-client-comment postgresql-common postgresql-contrib postgresql-contrib-9.1
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgresql-client-comment
root@vps1484:/home# sudo apt-get --purge remove postgresql postgresql-9.1 postgresql-client postgresql-client-9.1 postgresql-client-common postgresql
-common postgresql-contrib postgresql-contrib-9.1
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following package was automatically installed and is no longer required:
  libaio1
Use 'apt-get autoremove' to remove it.
The following packages will be REMOVED:
  postgresql* postgresql-9.1* postgresql-client* postgresql-client-9.1* postgresql-client-common* postgresql-common* postgresql-contrib*
  postgresql-contrib-9.1*
0 upgraded, 0 newly installed, 8 to remove and 8 not upgraded.
1 not fully installed or removed.
After this operation, 16.3 MB disk space will be freed.
Do you want to continue [Y/n]? y
(Reading database ... 98720 files and directories currently installed.)
Removing postgresql-contrib ...
Removing postgresql-contrib-9.1 ...
Removing postgresql ...
Removing postgresql-9.1 ...
 * Stopping PostgreSQL 9.1 database server                                                                                [ OK ]
Purging configuration files for postgresql-9.1 ...
Dropping cluster main...
Removing postgresql-client ...
Removing postgresql-client-9.1 ...
update-alternatives: warning: alternative /usr/share/postgresql/9.1/man/man1/psql.1.gz (part of link group psql.1.gz) doesn't exist; removing from list of alternatives
update-alternatives: warning: /etc/alternatives/psql.1.gz is dangling; it will be updated with best choice
Removing postgresql-common ...
 * No PostgreSQL clusters exist; see "man pg_createcluster"
Removing 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Purging configuration files for postgresql-common ...
dpkg: warning: while removing postgresql-common, directory '/var/lib/postgresql' not empty so not removed
Removing postgresql-client-common ...
Purging configuration files for postgresql-client-common ...
Processing triggers for man-db ...


Mysql: Possible to increase the priority of your query on a database? In mySQL is it possible to increase the priority of your query when competing with other users without caching your query before hand?

As has been pointed out, if you are using MyISAM then SELECT HIGH_PRIORITY might give you some advantage... but if you need performance under concurrency, you shouldn't be using MyISAM since the only level of locking available is table-level locking, and table locking occurs implicitly with a significant proportion of queries (hence the rationale for the old high/low priority stuff).

Or you could obtain an explicit lock on the tables involved while you run your queries, but that's probably not going to be a solution.

The question raised by your suggestion that you are "competing with other users" is whether you know for a fact that this is the major cause of your performance issues or, alternatively, whether you are dealing with a query that isn't properly optimized.

For all practical purposes, MySQL does not have any mechanisms for prioritization like this.  Every query is executed with comparable priority, subject to the limits of disk I/O, CPU clock speed and number of cores, available memory (and how it's allocated)... which means if you're not getting a level of performance consistent with the quality of your code, your server needs more of one or more kinds of resources, or you need to scale out with a read replica, or migrate some of the databases to a different machine... or, of course, if the machine isn't dedicated exclusively to running MySQL, then that's potentially problematic as well.

See also: Is there a way to achieve Workload Management in MySQL?


Oracle Text: query scoring with CTXCAT index? If a text column is indexed with a CTXSYS.CONTEXT index it can return a score showing how well the text matches the query.

create index ix on t(text) indextype is ctxsys.context parameters ('');
select id, score(1) from t where contains(text,'my query',1) > 0;

How can I see the score for text columns index with a CTXSYS.CTXCAT index?

create index ix on t(text) indextype is ctxsys.ctxcat parameters ('');
select id, ??? from t where catsearch(text, 'my query',null) > 0;

The score can only be accessed with a CONTENT index using a query like this

SELECT ID, TEXT,SCORE(1) FROM t WHERE CONTAINS(text, 'my query',1) > 0
ORDER BY SCORE(99) DESC;

By Oracle 11 everything you can do with a CTXCAT index you can do more with a CONTENT index.  Why not use that?

Edit: The op asks if CONTENT indexes must be synced.  Yes, this is correct.  I find the time and load factor to do this is entirely acceptable for smaller records sets.  Your mileage may vary based on record size.
Further reference:

Oracle how to
Text index how to


How do I recover 350 Gigabytes of unallocated space I was left with a scenario where my Database Initial size is 370GB.

When I checked the EXEC sp_spaceused, I can see almost 360GB of unallocated space .

Does unallocated space means unused space? If it is unused space, why this is causing the issue for storage?

What options do I have apart from shrinking the .mdf file with 360GB of unallocated space?

Also, as this is a huge database, what level of increments I can mention in the place of 'x' below - can I start from 10,000 MB every time I shrink?

USE Demo
GO
DBCC SHRINKFILE (N ‘Demo’, x)
GO

Is there anything else I should consider in this type of situations?

Would adding a new data file help?

Do you know everything that goes on with this app / database?

Imagine this scenario:

There's a Very Important Person at your company that needs to get (daily | monthly | quarterly | annual) financial reports
These reports are generated by a scheduled job
This job generates hundreds of GBs of data in the database - creating work tables, crunching numbers, etc
Afterwards, the job "cleans up" after itself by dropping (or truncating + rebuilding) the work tables

This results in a lot of unused space in the database - but the size of the files doesn't change

One day, a DBA comes along and shrinks this database
Some day in the future, this reporting job tries to run - but it grinds to a halt as the file has to grow over and over and over
Maybe the job even fails, or is delayed so long that it starts to affect performance during normal business hours
Very Important Person is mad, because they don't have their important reports, and everyone is complaining to them about the system being slow

You don't want that to happen, do you?

All that being said, if you're sure that the database:

really doesn't need this space, and
will never use this space because it doesn't gain data that quickly, and
you have an actual need for the space on this drive (paying by the GB to a cloud vendor, expensive SAN that other apps need space on, etc),

then by all means, shrink it.

Maybe there was a typo in the filesize when this database was first created (someone tacked a 0 onto the end of "37" - poof, 370 GB database file).

Maybe there was a one-time data load / deletion that will never happen again that caused the DB to grow to this size.

Make sure to give some thought to the "target size" you use with SHRINKFILE if you do this.  You say it's only 10 GB of used space, but I wouldn't immediately set it to 10 GB - you are putting some data into this database, right?


What are implications for changing encryption from TRIPLE_DES to AES_128? As title asks, what are implications for changing encryption from TRIPLE_DES to AES_128?

I have an older database I've been tasked with hardening, and I need to updated the algorithm used for encryption on it.

Running this query:

  SELECT name, key_length, algorithm_desc, create_date, modify_date FROM
  sys.symmetric_keys;

returns a result with theh name: ##MS_ServiceMasterKey##, using the TRIPLE_DES algorithm.

I didn't find any asymmetric keys used any of the database on this server.

Thanks for any help!

As title asks, what are implications for changing encryption from TRIPLE_DES to AES_128?

The title is actually quite misleading. Changing from a symmetric to asymmetric key in of itself doesn't change too much, potentially the amount of processing power in order to encrypt or decrypt the key and associated data.

  ... returns a result with theh name: ##MS_ServiceMasterKey##, using the TRIPLE_DES algorithm.

This is a the SMK or Service Master Key for the instance and can't have the algorithm type changed. This is where the additional context is key, because currently there isn't anything you can do to change it except upgrade to a newer version. You're using sql-server-2008 which has about 9 months of extended support left (July 2019) and is over a decade old - in terms of cryptography that's quite the lifetime. Newer versions of SQL Server, starting with 2012 use AES for the Service Master Key (SMK).

  I need to updated the algorithm used for encryption on it.

Your only option, in this case, is to upgrade to something made in this decade (2012+).


Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted Whenever I try to do a DB query with a particular DB user (myuser) I get the following error:

  Access denied for user 'myuser'@'localhost' to database 'mydatabase' [1044]

I decided to check the grants for that user and saw that one of the grants could maybe benefit from an update so I tried to update it and got the following error:

  Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted

I Google'd the error and found Cannot load from mysql.proc. The table is probably corrupted
, which recommends doing mysql_upgrade -u root -pxxx. I tried that and got this error:

  Error occurred: The mysql.session exists but is not correctly configured. The mysql.session needs SELECT privileges in the performance_schema database and the mysql.db table and also SUPER privileges.

I Google that error and found MySQL Upgrade Issue but the answer there isn't applicable - there appears to be just one mysql.session user when I do SELECT * FROM mysql.user;. So I figured maybe I'll just create the GRANTs myself:

GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `mysql`.`db` TO 'mysql.session'@'localhost';
GRANT SUPER ON *.* TO 'mysql.session'@'localhost';

Only problem: when I attempt to do so I get the same "Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted" that I got before.

My question is...  what can I do to fix this? I have a full DB backup that I restored from without issue a few months ago and if there have been any DB changes that have occurred in the DB since then I'm okay with losing. Maybe my best option is to simply uninstall MySQL and re-install it? And if that is the case then are there any steps I can take to prevent this issue from cropping up again?

I'm running MySQL 5.7.25-0ubuntu0.16.04.2 on Ubuntu 16.04.5 LTS on a Digital Ocean droplet.

Any ideas?

edit: I also get this error when I try to create a new MySQL user:

CREATE USER username IDENTIFIED BY 'password';

I tried mysqlcheck -uroot mysql db and got this back:

mysql.db                                           OK

I tried ls -latr /var/lib/mysql/mysql/db* and got this back:

-rw-r----- 1 mysql mysql   65 Nov  8 03:55 db.opt
-rw-r----- 1 mysql mysql  440 Nov 10 17:51 db.MYD
-rw-r----- 1 mysql mysql 5120 Feb 11 11:54 db.MYI
-rw-r----- 1 mysql mysql 9582 Feb 11 11:54 db.frm

I note that there's not an *.ibd file idk.

SELECT * FROM mysql.db, while logged in as both SSH root / MySQL, works without issue, but all the "Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted" errors are also coming to me while logged in as SSH root / MySQL root so idk.

edit2: I can't even do apt-get remove mysql-server as it gives me the same "mysql.session exists but is not correctly configured" that I was getting before...

I have seen this error many times when installing earlier versions of MySQL 5.7. I am stunned you are still getting this error.

Prior to MySQL 8.0, some of the tables in the mysql schema are MyISAM.

Unfortunately, MyISAMtables get corrupt very easily.

EXAMPLE: The header of the .MYD file holds the count of open file handles. If a MyISAM table is closed and has a nonzero value for open file handles, the MyISAM storage engine considers the table corrupt.

Solution: Just run the following command:

mysql> REPAIR TABLE mysql.db;

I suggested this  before : Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?


mariadb - EXPLAIN works but ANALYZE throws ERROR 1064 (42000) error in SQL syntax I want to see the execution plan of a query to find out why it is slow, but ANALYZE does not seem to work, it throws an error on the same query that EXPLAIN accepts.

MariaDB [dbx]> explain select `id` from `pics` where `user_id` = '3';
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | pics  | ref  | user_id       | user_id | 4       | const | 1087 |       |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [dbx]> analyze select `id` from `pics` where `user_id` = '3';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select `id` from `pics` where `user_id` = '3'' at line 1

I use mysql  Ver 15.1 Distrib 10.0.14-MariaDB, for Linux (x86_64) using readline 5.1

EXPLAIN will get the execution plan for a query

ANALYZE TABLE recalculates the index statistics. So just run

ANALYZE TABLE `pics`;

Give it a Try !!!


In DB2, how can I disable NLJOIN? I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query.

I've been looking for this for a while without any success. I looked for a property that could be set through the db2set command, but the only one referring NLJ is DB2_INLIST_TO_NLJN.

The closest I've found is through the usage of optimization profiles but those are very specific and what I need is a general setting that would apply to any statement being optimized.

You might wish to check out this article on join methods and how DB2 chooses which one when.

Based on this documentation (which is for DB2 8 and I'm assuming it is similar for 9+, though not necessarily), nested loop joins are always available to the optimizer to use. DB2 can select merge sort and hash as the optimizer level is upped from 1 and other things are present (read the article to see which).


Best way to create dynamic table for search There is a table of documents:

CREATE TABLE `docs` (
  `doc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_uuid` binary(16) NOT NULL,
  `sender_uuid` varchar(40) NOT NULL,
  `receiver_uuid` varchar(40) DEFAULT NULL,
  `chain_id` int(10) unsigned NOT NULL,
  `status` tinyint(4) NOT NULL COMMENT 'open,sent,inbox,read,etc',
  `date_created` int(10) unsigned NOT NULL DEFAULT '0',
  `date_changed` int(10) unsigned NOT NULL DEFAULT '0',
  `date_read` int(10) unsigned NOT NULL,
  `doc_date` int(10) unsigned NOT NULL,
  `doc_number` varchar(50) NOT NULL,
  `is_archive` tinyint(1) NOT NULL DEFAULT '0',
  `doc_hash` binary(16) NOT NULL COMMENT 'md5 of document data,
  PRIMARY KEY (`doc_id`),
  ... keys ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

which contains the mandatory fields in the document.

And I have a tables with document "indexes"

CREATE TABLE `docs_idexes` (
  `doc_id` int(10) unsigned NOT NULL,
  `created_from` binary(16) DEFAULT NULL,
  `is_signed` tinyint(1) NOT NULL DEFAULT '0',
  `encrypted` tinyint(1) NOT NULL DEFAULT '0',
  `file_name` varchar(255) DEFAULT NULL,
  `file_date` int(10) unsigned DEFAULT NULL,
  `inv_date` int(10) unsigned DEFAULT NULL,
  `inv_num` varchar(20) DEFAULT NULL,
  `error_code` int(10) unsigned DEFAULT NULL,
  `sub_doc_type_id` smallint(5) unsigned DEFAULT NULL,
  `sub_status_id` smallint(5) unsigned DEFAULT NULL,
  `action` tinyint(3) unsigned DEFAULT NULL,
  `buyer_uuid` varchar(40) DEFAULT NULL,
  `consignee_uuid` varchar(100) DEFAULT NULL,
  `consignor_uuid` varchar(40) DEFAULT NULL,
  `customer_uuid` varchar(40) DEFAULT NULL,
  `performer_uuid` varchar(40) DEFAULT NULL,
  `supplier_uuid` varchar(40) DEFAULT NULL,
  `delivery_place_uuid` varchar(40) DEFAULT NULL,
  `contract_date` date DEFAULT NULL,
  `contract_number` varchar(35) DEFAULT NULL,
  `delivery_date` int(10) unsigned DEFAULT NULL,
  `order_number` varchar(35) DEFAULT NULL,
  `order_date` int(10) unsigned DEFAULT NULL,
  `return_date` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`doc_id`),
  ... keys ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The problem is that the indexes will be added every month. And run ALTER TABLE is bad idea.

What is best way to create a database architecture without ALTER TABLE when I add a new index?

You could use an unstructured column type like XML or JSON. But MySQL doesn't support these types AFAIK. You could store these types as a string in MySQL but I wouldn't recommend that. The reason for that is if you need to change one value in the XML/JSON you would need to change the whole string or somehow parse it with with RegEx. Nasty.

So to use an unstructered type you would need to move to a different RDBMS. MariaDB has some JSON support. Switching from MySQL to MariaDB should be an easy migration, see this guide from DigitalOcean.

If you don't want to change RDBMS then I think the EAV model mentioned by Phil is the best choice although these tend to degrade in performance as the data grows. Filtered indexes can help but they too AFAIK are not available in MySQL.


how to create an explain plan for the below query? I'm getting ORA-00900: invalid SQL statement error I'm getting ORA-00900: invalid SQL statement error when i tried. but the sql query is running fine without explain.

Query: create or replace view TaskView as
   select T1.PersonID,T2.ID ,T2.Task from Table1 T1,Table2 T2
   where T1.TaskID=T2.ID;

A CREATE VIEW statement has no execution plan as the SELECT will not be evaluated when you create the view. You need to first create the view, then run EXPLAIN on a query using the view:

create or replace view TaskView
as
select T1.PersonID,T2.ID ,T2.Task
from Table1 T1,Table2 T2
where T1.TaskID=T2.ID;

Then

explain plan
  set statement_id = 'TASK'
select *
from taskview;

or simply explain the underlying query:

explain plan
  set statement_id = 'TASK'
select T1.PersonID,T2.ID ,T2.Task
from Table1 T1,Table2 T2
where T1.TaskID=T2.ID;


grant select in just one DMV instead of grant VIEW SERVER STATE Is there some way to grant select in just one sys.table.. for example select in sys.dm_hadr_availability_group_states without to grant VIEW SERVER STATE?

I think you can wrap the select against the specific DMV in a stored procedure and then use Execute As to elevate the permissions.
whether you choose to sign the SP with a certificate, execute as SA or as Owner is something you will need to determine according to your own security needs.


Merge Only Events that Start immediately after the Previous Event without any Gap How can i merge only the dates occur subsequently.?

(i.e, The Next Event begins immediately after the previous event completed).

Here, we need to consider some other Columns also for getting the Output.

Here is the Attached Screenshot of Sample Events Gantt Chart.

The following table contains events (M_ID, Y_ID, T_ID, E_ID, BeginDate, EndDate respectively)

CREATE TABLE #EventTable
(
    M_ID INT,
    Y_ID INT,
    T_ID INT,
    E_ID INT,
    BeginDate DATE,
    EndDate DATE
);

The Below Insert Statements for Sample Data Setup.

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 2, 7, 3, '01 Oct 2017', '31 Oct 2017'),
(135709, 2, 7, 3, '01 Dec 2017', '31 Dec 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 Oct 2017', '31 Oct 2017'),
(134560, 5, 8, 3, '01 Nov 2017', '30 Nov 2017'),
(135678, 3, 6, 2, '01 Jan 2017', '31 Mar 2017'),
(135678, 3, 6, 2, '01 Apr 2017', '30 Jun 2017'),
(135678, 3, 6, 2, '01 Oct 2017', '31 Oct 2017'),
(135678, 3, 6, 2, '01 Nov 2017', '31 Dec 2017'),
(123457, 4, 2, 3, '01 May 2017', '31 Oct 2017');

The Required Output Dates (Marked as RED) is mentioned in the above Screenshot.

Example:

For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 1;

The 2nd Event starts on (01 Apr 2017) immediately after the 1st Event completed on (31 Mar 2017).

So, In this case, The Output should be BeginDate of 1st Event(01 Jan 2017) and EndDate of 2nd event(30 Jun 2017).

Similarly,
For M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 3;

The 2nd Event starts on (01 Feb 17) immediately after the 1st Event completed on (31 Jan 17).

The 3rd Event starts on (01 May 17) immediately after the 2nd Event completed on (30 Apr 17).

So, In this case, The Output should be BeginDate of 1st Event (01 Jan 17) and EndDate of 3rd Event (30 Jun 17).

And As there is GAP (i.e, 4th event doesn't start next to 3rd event) between the 4th event StartDate and 3rd Event EndDate, The Dates should remain same.

Can Someone help me out.?

It's a bit convoluted, but I think this gets the result you're after:

;WITH src AS
(
SELECT et.M_ID
    , et.Y_ID
    , et.T_ID
    , et.E_ID
    , et.BeginDate
    , Marker = CASE WHEN et.BeginDate <= DATEADD(DAY, 1, LAG(et.EndDate, 1, et.BeginDate) OVER (PARTITION BY et.M_ID
            , et.Y_ID
            , et.T_ID
            , et.E_ID
             ORDER BY et.BeginDate))
        THEN 1
        ELSE 0
        END
    , et.EndDate
FROM #EventTable et
)
, src2 AS
(
SELECT src.*
    , rn1 = ROW_NUMBER() OVER (PARTITION BY src.M_ID
            , src.Y_ID
            , src.T_ID
            , src.E_ID
            , src.Marker
             ORDER BY src.BeginDate)
    , rn2 = ROW_NUMBER() OVER (PARTITION BY src.M_ID
            , src.Y_ID
            , src.T_ID
            , src.E_ID
            , src.Marker
             ORDER BY src.BeginDate DESC)
FROM src
)
SELECT src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , StartDate = MIN(src2.BeginDate)
    , EndDate = MAX(src2.EndDate)
FROM src2
WHERE src2.rn1 = 1
    OR src2.rn2 = 1
GROUP BY src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , src2.Marker
ORDER BY src2.M_ID
    , src2.Y_ID
    , src2.T_ID
    , src2.E_ID
    , StartDate
    , EndDate;

Output:

╔════════╦══════╦══════╦══════╦════════════╦════════════╗
║  M_ID  ║ Y_ID ║ T_ID ║ E_ID ║ StartDate  ║  EndDate   ║
╠════════╬══════╬══════╬══════╬════════════╬════════════╣
║ 134560 ║    5 ║    8 ║    3 ║ 2017-04-01 ║ 2017-08-31 ║
║ 135709 ║    2 ║    7 ║    1 ║ 2017-01-01 ║ 2017-06-30 ║
║ 135709 ║    2 ║    7 ║    3 ║ 2017-01-01 ║ 2017-05-31 ║
║ 135709 ║    2 ║    7 ║    3 ║ 2017-07-01 ║ 2017-08-31 ║
║ 135709 ║    5 ║    8 ║    1 ║ 2017-02-01 ║ 2017-07-31 ║
╚════════╩══════╩══════╩══════╩════════════╩════════════╝

The code uses the LAG() aggregate to inspect the next logical row to see if the start date is prior to or adjacent-by-one-day to the end date in the current row.  It then uses the two ROW_NUMBER() aggregates to get the first-and-last rows of each group.


Create and use Fulltext Index in a Stored Procedure I want to create and use a fulltext index within a single procedure. For example:

CREATE PROC [dbo].[dbp_PCL_to_Demo_Matching_Mason] AS

--IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID('Medicaid_State_Claims'))
    CREATE FULLTEXT INDEX ON dbo.Medicaid_State_Claims(Scrubbed_Account_Number)
      KEY INDEX nci_Seq_No;

select * from dbo.Medicaid_State_Claims
  where CONTAINS(Scrubbed_Account_Number, 'blahblah');

However, this fails with the error message:

  Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'dbo.Medicaid_State_Claims' because it is not full-text indexed.

Uncommenting the "IF NOT EXISTS" condition makes no difference. However, if the index already exists and I uncomment that line and create the stored procedure, it works. But I'd rather not create it ahead of time, I want to create it within the stored procedure. How can I create the fulltext index and use it in the same stored procedure?

I don't believe you can, at least not within the same proc.

Stored procs are compiled before running, so that command is going to be checked for validity before it executes.

Full text is an entirely separate executable, and while you can certainly issue SQL commands that create a new full text index, or initiate a rebuild of one, or whatever, those commands aren't going to work like normal SQL commands (they can't be within an ATOMic transaction, they can't possibly roll back, etc.).

This means that just because the CREATE FULLTEXT INDEX statement succeeds, that doesn't guarantee the index is populated and/or useful.

It's like issuing a msdb.dbo.sp_start_job to run a SQL agent job. The statement finishes quickly and returns control, but the agent could very well still be running (for a very long time).

Maybe you can trick it by calling separate stored procs, one that creates the full text index and kicks off a full build, and then another that checks the status of that build and loops till it is complete, before calling a proc that runs the query?

It seems likely to me that you will return a value faster by doing a normal WHERE Scrubbed_Account_Number LIKE '%blahblah%', even though that will do a full table scan.

In fact, I guarantee that will be faster, since it will have to do a full table scan to build the full text index.


Filtering data in a publication I'm looking to replicate a table that has a particular column for deciding if a row should be replicated. If the column is set to 'Y', then replicate. From my searching, Static Row filters are my friend here, but I have a question on them.

All of the rows will be initially inserted with the column set to 'N'. Then, a script is run that identifies which rows are supposed to be replicated and updates those rows to 'Y' (the logic behind which rows is complex and not easy to identify at insertion time). Additionally, a subsequent run of the the script may find the a row is no longer valid for replication and will update the 'Y' back to 'N'.

My question is this: If my action on the source table is an update action, won't that break on the subscriber side when the update transaction tries to run against a row that doesn't exist on the published table (since the row was not valid for replication until the update occurred?) Similarly, if I update the row to 'N', will that update trigger a delete on the published table (since the row would no longer meet the filter criteria)? Or is the SQL server replication agent smart enough to turn that update into a insert/delete respectively?

Assuming you are running transaction replication. This is how it works in your scenario

When initial records are inserted with replicated column value as 'N' on publisher no records will be replicated to subscriber.
Once you update the some of the records with replicated column value as 'Y' based on your logic on publisher only those 'Y' records will replicated to subscriber.
And for last question once you update any of the replicated column 'Y' value records on publisher back to 'N' in subsequent runs of your logic those records will be deleted from the subscriber.

All of these operations can be monitor by executing the EXEC sp_browsereplcmds on distribution database. In the execution results look for command column to see what exact is performed by replication process.


How to generate unique and sequential Id values We have an entity called Task which has properties like id, vendor id, name. So I created the following table to represent it:
CREATE TABLE `Task` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned NOT NULL,
  `name` varchar(30) NOT NULL,
  CONSTRAINT `task_vendor_id_foreign` FOREIGN KEY (`vendor_Id`)
  REFERENCES `Vendor` (`vendor_id`))
  ENGINE=InnoDB AUTO_INCREMENT=1
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

We have multiple Vendors, and we want that the id values should be sequential starting from 1 for each Vendor.
Example:

Vendor 1: ids: 1, 2, 3

Vendor 2: ids : 1, 2, 3

One of the solutions that clicks me is to, whenever a Task is added, we find out the max(id) for that vendor and then increment it by 1 and assign it to the corresponding row, e.g.:
INSERT INTO `Task` (`id`, `vendor_Id`, `name)
VALUES ((select max(id) + 1 from Task where vendor_Id = 1), '1', 'Test');

But I get an error message with that approach:

You can't specify target table 'Task' for update in FROM clause

From MySQL docs:

You cannot update a table and select from the same table in a subquery.

Questions

How do I go about solving the problem with this approach?

Are there other options?

A simple rearrangement:

INSERT INTO `Task`
       (`id`, `vendor_Id`, `name`)
    SELECT MAX(id) + 1, 123, 'Test'
        FROM Task
        WHERE vendor_Id = 123;

(I changed the vendor_id to make it clearer.)


Suggestion for a contact management database design I am currently designing a contact management database for a chamber of commerce. The goal of the database is to store all the person (except our own staff), all recorded companies (regular companies and member of the chamber), addresses of the person and companies, tasks that the staff are currently responsible for, a list of our staff (user) and the roles within the chamber.

Business Rules

one person works for one company
one company have multiple person
person and company can have multiple address
one company can be in multiple industry
one industry can have multiple company
one company can have multiple membertype
one membertype can have multiple company
one user can play multiple role
one role can be assigned to mutliple user
one user can have multiple task
one task can be worked on by multiple user
one task can target multiple person
one person can be targeted by multiple task
one person can only be added by one user
one user can add multiple person
one company can have 0 or 1 parent_company
one parent_company can have multiple child company

I have come up with the following design and it has undergone some changes:

Issue

Are there better ways to display the user-task-person relationship?
For example, if a person can have only one email but can have multiple tel, should I make an extra table just for tel while email is still in the persontable? Would it considered to be "unclean"?
For the table membertype, should company_id and typename both be PK?
How does this schema look now? Are there still some normalisations to be done?

I'm a newbee at database, there are definitely some design flaws or errors, it would be nice if you guys could give me some suggestions so that I can correct and improve this design. Thank you ^~^

I'm seeing a few misses.
First, the role table.  If one user can belong to more than one role, you're going to need another table between role and user.  1 table to store the roll(like you have already) and another to store a junction table which will store the relation between a role and a user.  That table would probably only have two columns, role_id and user_id.  The user table would not need to identify the role unless it was a primary role or default role.  That could also be accomplished in the junction table.

Similar situation with Company - Member Type.  Since you only have a single column in the company table, you will only be able to store one member type per company.  If the requirement is to have companies be able to be assigned to more than one member type, you will need a junction table to store company|member_type relationships.

One last thing I noticed, is there is currently no relation between Employee and Company other than the address.  That will definitely get confusing.
Since you want contacts to be associated with multiple companies, you will need another junction table to store the contact_id | company_id relationship. There might be a clever solution using your employee table.  Since that essentially is the only direct connection between employee and company, maybe just change that to the junction table, company_employee.  Then add a boolean/bit column (IsEmployee) to indicate whether the contact is actually an employee of the company.


How can I populate a table with a foreign key? For my GCSE I need to Create and populate a third table called entries using query scripts. This table should contain foreign keys to allow sensible links to be made with the other two tables, together with the dates of each exam.

Whenever I try to insert records into my entries table it says I am not able to due to the foreign key.

Any help would be great thanks

Basically is a syntax "misplacement":

INSERT INTO Entries VALUES (entry_id, subject_id, student_id)

Column names should be placed before VALUES clause or, if you supply values for all columns in the table, you can avoid to use it.

INSERT INTO Entries (entry_id, subject_id, student_id) VALUES

Try it on this way:

INSERT INTO Entries VALUES (1,4,1,'date'), (2,8,2,'date'), ...


Does Oracle Partition by Reference optimizes join queries Oracle 11g has a new feature in partitioning tables called partition by reference.

I want to know whether Oracle joins (nested-loop-joins or hash-join or ...) rows from proper partitions when joining two tables or now?

How can one be sured of that?

I think the answer to your question is "Yes".

The Oracle optimizer understands reference partitioning. See the Oracle documentation at http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#BJECHCAG

Also see http://www.orafaq.com/node/2591


Error when start to attach mdf - : parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control) Got the following error when first attempted to attach a database

  parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control)

What does this mean and how do I correct ?

Try to login with Admin account (not sql account)


Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan? This Question brings a situation where the Query Optimizer chooses poorly the seek predicate among the existing predicates of a simple query. After running some tests I got to the conclusion that the poor decision is due to the use of a local temp table instead of a global temp table or a regular table.
db fiddle: Local Temp Table, Global Temp Table, Regular Table.

I couldn't find any characteristic on the Temporary Tables doc that would explain the different behavior we see when using a local temporary table instead of a global temporary table or a regular table. Is there a logical reason for this or could it be a bug?

This just looks like a quirk with the optimisation of that query producing a slightly different tree shape when simple parameterisation is attempted vs when it isn't.
This is not attempted in the case of the local temp table but is for the global temp table and permanent table.
The auto parameterisation does not succeed in any case but when attempted on this query it results in a slightly different tree shape with the order of the predicates retained from the query text (rather than with the <> first as happens when it is not attempted).
My supposition is that this ordering the predicates end up in before cost based optimisation somewhat arbitrarily determines what will be the seek predicate and what will be the residual in this case (i.e. the competing options aren't considered during cost based optimisation).
Adding and 1=1 is one way of preventing this and the plan reverts.


Temporarily disable SQL Server Mirroring We've got a migration going on where a SQL Server 2012 mirroring configuration running in high safety with auto failover (primary/mirror/witness) is being completely shut down and restarted to move hardware.

Shutdown is being done by automation so the order of shutdown isn't going to be tightly controlled.  We would like to prevent role switching during the shutdown/restart, so we need to "remove" the witness.  Seems like this could be done either via explicitly removing the witness via TSQL on each mirrored database (there are about 5), then re-adding after everything restarts/resyncs or by just disabling the SQL Server service on the witness.  The second option seems like less fiddling around, but most of the references I see say to remove the mirror from each session, then go back and re-add after.

Is there a reason not to just stop and disable the witness SQL server service until the maintenance is complete?  Without the witness there will be no failover and all it takes is restarting the service to recover.

We are also planning on disabling tlog backups on the primary just before shutdown to make sure the mirror can resync automatically after restart.

From the documentation, you could just shutdown the witness instead of removing it from all the servers.
It says that the mirroring will then be a "partner-to-partner" quorum, and that no automatic failover will occur.
Of course, I would test it on a dev setup before trying it in production.

A partner-to-partner quorum that consists of the two partners.
As long as the partners retain quorum, the database continues in a SYNCHRONIZED state, and manual failover remains possible. Without the witness, automatic failover is not possible; but when the witness regains quorum, the session resumes regular operation, and automatic failover is supported again.


On a dedicated database server, how much memory to reserve for the OS? Assuming you have a dedicated server explicitly for database functions -- how much memory should you reserve for the operating system?

I realize this will probably vary somewhat depending on the specific OS, the specific database software, and so forth. But, as memory is so important to database performance, I want the database to have the maximum reasonable level of memory, without starving the host OS.

So

what's a good rule of thumb to start with?
what counters or performance indicators should we look at to determine if we've gone too far and the host OS is being starved somehow by the database?

Assuming Windows and SQL Server...

There are two schools of thought.

Leave 2-4 Gigs for Windows (depending on what is installed besides SQL Server).
Leave 10% of your available memory free.  As you get over 64 Gigs this gets to be a crazy large amount of memory to leave for the OS, which it probably won't need.

Personally I'm in the first group.  Windows usually just needs 2-4 gigs, sometimes up to 6.


How to take single database backup using mysqlbackup command in Mysql 8.0.18 version DB I have a mysql instance(8.0.18-commercial MySQL enterprise server) with 8 databases in it and out of those I only need to take the backup of DB named 'Test1'.

Please note that its 300 GB sized DB and I want to take backup and restore it using mysqlbackup command. Is there any option for that, please guide me.

If you create the backup using the --tts option, when restoring your instance you can include/exclude selected tables. A whole database is included/excluded by selecting all of its tables with --include/exclude="^databasename\."
The MySQL official documentation explains it very well:

TLR: https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/restore.partial.html
Restoring a backup created with "tts" option: https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/restore-use-tts.html

Hope it helps.


What is the difference between these invocations of COUNT()? can anyone please let me know difference between count(*), count(1), count(0), count('FAST')

COUNT

  If you specify expr, then COUNT returns the number of rows where expr
  is not null. You can count either all rows, or only distinct values of
  expr.

  If you specify the asterisk (*), then this function returns all rows,
  including duplicates and nulls. COUNT never returns null.

0, 1, 'FAST' are all constant expressions, they are never null, so the query returns all rows.

* also returns all rows by definition.

So they all do the same.


How to get old and new values on Redis notification? I need to implement a feature like: once any specific key in Redis is updated, call a C function with the old and new values as arguments.

Currently, I know Redis notification  and I could use hiredis to subscribe a keyspace event. While Redis notification doesn't tell me the old or new value. My current solution is getting old and new value in another session rathen than the one subscrbing the event. Once event occurs, get the new value then.

But I believe this solution may have potential bug. Say if the value is updated twice in a very short interval, I could get notified, but when I call get value, I may only get the second value, since the first value has been flushed very quickly.

Is my worry necessary? Is there any solution to implement Redis notification with old and new values?

Thanks in advance.

As a workaround, you could have a field OLD_VALUE that is filled by your application logic together with the real field in a transaction. Doing so, you would also get a notification for the old value


How to SELECT a column in a row and return another row if it doesn't exists? I want to write a query with two SELECT Statements in an order that if the first SELECT Statement doesn't return anything then only the second select statement runs.

SELECT col1 FROM MyTable WHERE col2=5

and if this doesn't return anything(there is no col2=5) then:

SELECT col3 FROM myTable WHERE col4=10

How should it be done?

Well this is incredibly inefficient and I fail to see the point, but...

IF EXISTS (SELECT 1 FROM ... WHERE ... <1st clause>)
BEGIN
  SELECT ... FROM ... WHERE <1st clause>;
END
ELSE
BEGIN
  SELECT ... FROM ... WHERE <2nd clause>;
END

Or if the types of col1 and col3 are compatible then...

SELECT ... FROM ... WHERE <1st clause>
UNION ALL
SELECT ... FROM ... WHERE <2nd clause>
  AND NOT EXISTS (SELECT ... FROM ... WHERE <1st clause>);

I don't know of any other way for SQL Server to determine there will be no rows from a query without actually running the query.


I'd like to make this relational database into a star schema. Any help is appreciated So basically, I want to make this relational database into a star schema. This would revolve around a sales fact table. Although I am confused as how to do so because there are two ways in which a sale is made. Either through a store sale or internet sale. That part is kind of messing with how I would build my facts and dimensions table. Do I just make the sales fact table include the store id and leave it as null if it were a internet sale, or do I make those two items a dimension such as internet sale dim and store sale dim?

You could add a new row to your store dimension table for „website“. And add a column StoreType that says „Physical“ and „Web“ for instance. Then all sales go into the same fact table, no matter whether they occur in a physical store or online.


PostgreSQL read only user We have a PostgreSQL database 'db1', which is having around 500 schemas.
I am trying to create a read-only user for this particular PostgreSQL database.

I could find the following command for creating a user and granting permission schema wise

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO readonly_user;

but

1) We have around 500 schemas, so granting permission to each schema is difficult.

2) These schemas will be dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.

Is there any way to give the read-only permission for a whole database instead of schema?

In MySQL, I can do it using the following command

grant select on *.* to 'user_name'@'IP';

I am looking for a similar command in PostgreSQL

We are using PostgreSQL 10.

We have around 500 schemas, so granting permission to each schema is difficult.

No.  It's tedious to do by hand, but it's also a prime candidate for automation, through scripting.

  ... dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.

I disagree.  You're doing all the "difficult" stuff every day - dropping, recreating and repopulating all these schemas.  I would suggest that adding this single statement to the end of that process is relatively trivial!

  Is there any way to give the read-only permission for a whole database instead of schema?

Even if there is, you should not do so.

It sounds to me like these schemas are discrete entities (otherwise why not combine them into one?).  Granting access across all of them strikes me as a Bad Idea.


App Engine - Datastore ; Exclude columns on backup I want to backup a table without some columns. Can I exclude column/s? Is there any way to do this?

That wouldn't be a backup of your table; you wouldn't be able to recover the table because the structure of the resulting table is different, and you wouldn't be able to recover the data either.

It is possible to save only the result of a selection into a file, e.g.

mysql -e "select column1,column2 from table" -u user -pxxx mydatabase > partialbackup.txt

If the reason for this is that you have a table containing a very large amount of data, only part of it being truly important, you should reconsider your database structure: divide the important data and additional data to separate tables and dump the important table more frequently.


MySQL vs. Microsoft Access I'm trying to aggregate about 500-1000 MS Excel files that deal with various small business issues (inventory, purchase orders, etc) and I'm currently trying to decide what kind of RDMS I should use. To give you more detail, the database will be mainly used by 1 maybe 2 people hosted on network server. Currently, I've been fooling around with MariaDB and Microsoft Access. Excel data is easily imported into MS Access and I have some road bumps when it comes to importing that same data using MariaDB. The DB must be easily query-able and must have the capability to easily import data from a spreadsheet.

I'm not sure what direction to go in, do I continue fenagling with MariaDb and MySQL or move onto MS Access. Or, should I be using something else?

MS Access Vs MySQL

MS Access works only on Windows whereas MySQL works on almost all platforms.
MS Access does not support Partitioning feature whereas MySQL has composite, Range partitioning support.
MySQL is opensource so no cost involved.
Performance in MySQL is way better than MS Access
MS Access database is more suitable for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Microsoft Access over SQL Server is for compatibility/sharing. You might need to email someone a copy of your database. People are more likely to have Access on their desktop computer than SQL Server.
Security - MS Access is limited to security in terms of user-name/password on the database. Where as MySQL is much more secured and you can configured it with SSL support.
MySQL is the most famous or rather more loved amongst the professional folks because of it's multiple choice in custom storage engines. You can have custom storage engines developed in MySQL based on your requirements or choose amongst the inbuilt ones
Actually there is no comparison between MySQL as a real commercial heavy duty database, and MS Access which is typically a local use, giveaway in Microsoft Office. It is no good for remote access, its' connection system is not solid. It is generally useless for multiple user purposes.And you are far better using a web based interface for mysql, VB is also limited in functions and power. Consider using one of the free distributions of the Apache Web server which carries php as the scripting language, and the mysql database pre-configured.

Go ahead with MySQL and experience it.


Changing password doesn't work I need to login to a database. I have good credentials, but when I was trying to login with SQL Developer, I got this error message:

  ORA-28001: the password has expired

Then I tried to login with SQL*Plus, which worked fine, I connected successfully and I was asked to change my password.

So I typed in a new password and then retyped it. Then I got other message:

  ORA-01017: invalid username/password; logon failed

Can you help me how can I change this password? I don't have administrative permissions.

I suggest the following:

At first login as SYSDBA and check the ACCOUNT_STATUS of your user (SYSTEM in the sample below).

> sqlplus / as sysdba
SQL> select username, account_status, lock_date from dba_users where username='SYSTEM';

If this statement doesn't return a record, then your account does not exist.

If the account is locked (maybe because you entered a wrong password several time) then you have to unlock the account:

SQL> alter user SYSTEM account unlock;

To reset the password you can use this statement:

SQL> alter user SYSTEM identified by NEW_SYSTEM_PWD;

If you are working with a development system, then you can ignore case sensitive logons and make sure passwords never expire again:

SQL> alter system set sec_case_sensitive_logon=false scope=both;
SQL> alter profile default limit password_life_time unlimited;

See Oracle documentation for further infos regarding DBA_USERS


Indexed view not used in actual execution plan? I've got a modest use case for an indexed view, which collapses out a column and sums all entries from a large table:
CREATE VIEW dbo.Losses_CombinedPortfolio WITH SCHEMABINDING
AS
    SELECT [Category], [Year], SUM(ISNULL(Loss,0)) AS [Loss], COUNT_BIG(*) as [Count]
    FROM dbo.Sub_Portfolio_Losses
    GROUP BY [Category], [Year]
GO
CREATE UNIQUE CLUSTERED INDEX Idx ON dbo.Losses_CombinedPortfolio([Category], [Year]);

My original goals were more ambitious, but indexed views are so restrive... I'd be happy enough just to get this to work.
Sadly, when I try to do a basic query on this indexed view (SELECT TOP (100) * FROM Losses_CombinedPortfolio ORDER BY Loss DESC), the query is just as slow, and the actual execution plan shows that it's always going back to the source table and computing the aggregations from scratch each time:

I can only assume that this is because my computed "Loss" column isn't getting materialized - but that would surprise me, since the clustered index creation succeeds.

Note that my primary use-case for this view is to sort by Loss descending, but I couldn't manage to explicitly create an index that includes it:
CREATE UNIQUE CLUSTERED INDEX Idx ON dbo.Losses_CombinedPortfolio(Category, Loss DESC, [Year]);

I get the error:

Cannot create index or statistics 'Idx' on view 'dbo.Losses_CombinedPortfolio' because key column 'Loss' is imprecise, computed and not persisted. Consider removing reference to column in view index or statistics key or changing column to be precise. If column is computed in base table consider marking it PERSISTED there.

I tried resolving the "imprecise" by casting the summed loss to types other than float (even tried truncating it to bigint) but it seems this error is stemming from the underlying type used to compute the sum.
I'm confused - I've seen other questions claim that they were able to successfully perform aggregations such as sum in their indexed views, so I'm not sure why this wouldn't work.

You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server, as Enterprise won't expand by default) in order for your query to use the Indexed View (instead of expanding it to the underlying table). This will solve your first issue.
This is how you can use it in your query:
SELECT TOP (100) *
FROM Losses_CombinedPortfolio WITH (NOEXPAND)
ORDER BY Loss DESC

You can see more information in the Query Hints docs by Microsoft. But this is the relevant section to what you're currently experiencing:

The indexed view is not expanded only if the view is directly
referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH
(NOEXPAND, INDEX( index_value [ ,...n ] ) ) is specified.

This is one of the few query hints you can utilize without worrying about it being bad practice, and actually it's recommended to use for certain reasons as well.
As far as your issue with the "imprecise" error, this is as you guessed it, due to the Loss column being of type FLOAT (which effectively is nondeterministic across different CPU architectures). So even casting it won't help you, you'd have to change the underlying type to something that is precise so that value is materialized in the table before you can index it in the view. (E.g. if you can change the data type to DECIMAL, then you'd be able to add that column to your index.)


Error during the installation of oracle 11gxe When I tried to install Oracle 11g XE into my machine (Windows XP media center edition, version 2002  SP3) I'm getting an error as

0x80040702 Description: Failed to load DLL:chkportDll2

I've googled, but not found any suitable answer.

Don't think MCE are certified to run Oracle 11g XE.

Try

This thread mentions deleting install paths found at

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\Folders

key from the registry. You can try that. As always, ensure you have a working backup before you fiddle with the registry.
Reinstalling SP3


InnoDB forum DB design and Composite Primary Key (Cluster Primary Key) I am designing forum database  using InnoDB and got few questions , Could any one please help me to clarify them?

Posts and Comments table design below

CREATE TABLE `my_posts` (
  `forum_id` mediumint(8) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,          // not an auto increment
   subject   varchar(200) NOT NULL,
  `details` text NOT NULL,
  `access` tinyint(3) unsigned NOT NULL,  //private,friends,public
  `created_by` int(10) unsigned NOT NULL,
  `created_on` int(10) unsigned NOT NULL,
  `updated_on` int(10) unsigned NOT NULL,
  `comment_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `ip_address` int(10) unsigned NOT NULL,
   sphinx_unique_id  int(10) unsigned NOT NULL,  // not an auto increment
   PRIMARY KEY (`forum_id`,`post_id`,created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `my_posts_comments` (
  `forum_id` mediumint(8) unsigned NOT NULL,
  `post_id` int(10) unsigned NOT NULL,          // not an auto increment
  `comment_id` int(10) unsigned NOT NULL,
  `details` text NOT NULL,
  `created_by` int(10) unsigned NOT NULL,
  `created_on` int(10) unsigned NOT NULL,
  `ip_address` int(10) unsigned NOT NULL,
  PRIMARY KEY (`forum_id`,`post_id`,comment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have queries based on forum_id, post_id, created_by and also sorting on updated_on , comment_count

SELECT * FROM my_posts  WHERE access=public ORDER BY updated_on DESC      //OR ASC
SELECT * FROM my_posts  WHERE access=public ORDER BY comment_count DESC      //OR ASC
SELECT * FROM my_posts WHERE forum_id=?  and access=public ORDER BY updated_on DESC      //OR ASC
SELECT * FROM my_posts WHERE forum_id=?  and access=public  ORDER BY comment_count DESC   //OR ASC
SELECT * FROM my_posts WHERE created_by=?  ORDER BY updated_on DESC     //OR ASC
SELECT * FROM my_posts  WHERE created_by=(Friends_ids) AND access=(public,friends)   ORDER BY updated_on DESC      //OR ASC
SELECT * FROM my_posts WHERE forum_id=? AND  post_id=?
SELECT * FROM my_posts_comments WHERE forum_id=? AND post_id=?

1) Since the composite primary key on forum_id, post_id, created_by , I see problem accessing it just conditioning on created_by . What is the best design to query on  created_by? Can I remove created_by from composite key and just add index for it . I will loose Cluster  performance here ..

2) What is the best way to increment post_id on forum level ? Or can I increment at table level . for example

sample data ( Forum Level )

 FORUM_ID  POST_ID
    1         1
    1         2
    1         3
    1         4
    2         1
    2         2
    2         3
    2         4
    2         5

Or table level

 FORUM_ID  POST_ID
1          1
1          2
1          3
1          4
2          5
2          6
2          7
2          8
2          9

If I can use the above table level increment , I can remove  sphinx_unique_id column since I am using it only for sphinx search . Also in comments table , I can remove forum_id column .

3) All the queries I have sorting on  updated_on and on comment_count . These two are not of PK , so the first two queries looks like a major problem ..

Thanks all for your help

You can and will be better served by removing created_by from your primary key and creating a separate index for it.

This will allow those queries which use created_by in the where clause (e.g. where created_by = ?), to be able to use that index to service the query.

There may be a slight, and probably not noticeable, performance hit from managing the primary key and the index on an insert. However this price is nothing to pay for not doing full table scans when executing such queries needing it to be be the primary search in the where clause.


Getting a random row from PostgreSQL? I want to get a random row from my table by id.

My table:

ID|Word     |Dificult|Category_id|
1 |'Dumb'   |'Easy'  | 3         |
2 |'Leopard'|'Medium'| 6         |

If my user selects a category and a difficult, I have pick a random word with user parameters, like:

idRaffle := raffle.id;
   -- raffle.id = An id that postgres will bring me by some raffle function.
d := 'Easy';
c := 3;
select * from words where id=idRaffle and Dificult=d and Category_id=c

raffle.id

I don't know how to get that random row id (raffle.id).

Note that it must follow the user's selection conditions.

To pick a random row, see:

quick random row selection in Postgres

SELECT *
FROM words
WHERE Difficult = 'Easy' AND Category_id = 3
ORDER BY random()
LIMIT 1;

Since 9.5 there's also the TABLESAMPLE option; see documentation for SELECT for details on TABLESAMPLE.


Complicated SSRS layout - is that possible? I have been ordered to do a report with a pretty sophisticated table layout. Okay, it's not that sophisticated, but we all know how SSRS is with those things. I am pretty certain that layout cannot be achieved, but I want some feedback before I start simplifying it.

This is the table layout. I've replaced everything with 'header' and 'data' accordingly. What gives me a tiny bit of hope is that the dataset will always return one row, so I may ignore the header and detail regions assigned by SSRS. I only need to somehow achieve that layout and I'm good.

I have tried using matrix, it lets me merge cells vertically but then it only lets me merge them horizontally if it is the upppermost row. So guys, is there any chance to insert the photo the way it is done in the image (merging 10 rows vertically and 2 rows horizontally altogether in the middle of the table)?

Kind regards,
Mash

If you're using a single dataset which returns a single row, this should be pretty easy to achieve.

Not with subreports, but rather "sub tables". Create separate tables for certain areas, then drag and drop them in to appropriately sized cells within the main table.

Create a table, then another with the content you want to place in a certain position. Size the container cell accordingly (you can merge horizontal cells in the master to achieve this if necessary) or else things start to go a bit weird:

Then drag & drop the sub-table in to place:

The tables have to have a dataset in common, which is fine in this context if I understand your description. And I think there are limits as to which area of a table you can place sub-tables, but other than that it can be a pretty powerful tool.

And I think your approach of ignoring headers and footers is right for this example. Just use detail rows for everything. Create as many as you need.

Hopefully I've given you enough to experiment with. I can expand on this at a later date if you need it.


Update using NonClustered index results in large number of logical reads? While doing some debugging of a frequently called stored procedure, I was testing adding a column to one of the nonclustered indexes to make it cover, but noticed that doing so would dramatically increase the number of logical reads the stored procedure performed (compared to when it used a clustered index scan). Running multiple iterations of the same stored procedure in parallel also resulted in worse CPU time. I was able to reproduce this behavior with multiple builds of SQL Server 2012. Can anyone explain why this is happening?

Example table

CREATE TABLE dbo.TestTable (
    ID INT PRIMARY KEY IDENTITY,
    Condition BIT NOT NULL,
    OtherColumn INT NOT NULL
);

CREATE NONCLUSTERED INDEX NC ON dbo.TestTable(Condition);

-- Generate 1000 rows with Condition = 0 and 1000 with Condition = 1
DECLARE @i INT = 0;
WHILE @i < 1000
BEGIN
    INSERT INTO dbo.TestTable(Condition, OtherColumn) VALUES (0, @i);
    INSERT INTO dbo.TestTable(Condition, OtherColumn) VALUES (1, @i);
    SET @i = @i + 1;
END;

Example update

UPDATE dbo.TestTable SET OtherColumn = 1 WHERE Condition = 1;

Results with the nonclustered index enabled (nonclustered index seek) - Paste The Plan

  Table 'TestTable'. Scan count 1, logical reads 2005, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.

Results with the nonclustered index disabled (clustered index scan) - Paste The Plan

  Table 'TestTable'. Scan count 1, logical reads 7, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.

The difference is due to: Seek(nonclustered) vs. Scan(clustered).

Some (oversimplified) explanation:

When performing seek, SQL Server must lookup every row. To find that row it has to travel down the index tree and access a page on every level.
In your case, with 2000 rows, i believe there is one index level and a data level. This means that for every of 1000 rows, SQL has to access 2 pages (row1 index->data.. row2 index->data ...) which explains ~2k logical reads.

The scan on the other hand works in a way that it looks up the first row and then follows all rows on the page and all the next pages sequentially.
So it would look something like that : root page > index > find 1st data page > 2nd data page ...

This does not mean that Seek is worse and Scan is better (usually the opposite), because more often in the real world would you have large tables and retrieve/update only small portions of it (which seeks are good at). With operations on a large percentage of the table scans will often be more efficient.


No overlapping timestamp with condition currently to stop overlapping timestamps of specific event_type in following table:

    CREATE TABLE foo (
        id                    UUID PRIMARY KEY,
        event_type            TEXT,
        from                  timestamp WITH time zone NOT NULL,
        to                    timestamp WITH time zone NOT NULL,
        deleted               timestamp WITH time zone DEFAULT NULL
    );

i'm using:

    ALTER TABLE foo
        ADD CONSTRAINT no_overlapping_times_for_event_type EXCLUDE
        USING gist (event_type WITH =, tstzrange(from, to, '[)') WITH &&);

Above works fine.

Question:

How do i make it so that the constraint does not kick in if the foo had deleted
value populated? so that i can create delete one and create a new one with same dates without the constraint stopping me.
I only want it to stop user from creating overlapping timerange entries of same event_type only if they're not deleted.

You can add a WHERE clause to the constraint, making it a partial one. It will be checked only for row that have deleted IS NULL. Rows that have deleted populated will be completely ignored by the constraint checks:

ALTER TABLE foo
    ADD CONSTRAINT no_overlapping_times_for_event_type EXCLUDE
    USING gist (event_type WITH =, tstzrange("from", "to", '[)') WITH &&)
    WHERE (deleted IS NULL) ;


Invoke-ProcessASDatabase fails with "Cannot connect to server" Occasionally I'm getting this error:

Cannot connect to server 'myASCube'. Client with IP Address 'x.x.x.x' is not allowed to
access the server. To enable access, use the Firewall settings in Azure Management Portal.
It may take up to 5 minutes for this change to take effect. More information on configuring
firewall and setting IP address ranges can be found here: https://go.microsoft.com/fwlink/?linkid=2099963

I can go into "Analysis Services" -> "Firewall" and add the IP Address, and then it runs again for a few days, before it fails on a new IP address.

This is the Powershell command I'm running

$AzureCred = Get-AutomationPSCredential -Name "CredForRefreshCube"
Add-AzureAnalysisServicesAccount -RolloutEnvironment 'westeurope.asazure.windows.net' -ServicePrincipal -Credential $AzureCred -TenantId "56cb8dc0-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
Invoke-ProcessASDatabase -server "asazure://westeurope.asazure.windows.net/myASCube" -DatabaseName "mydatabase" -RefreshType Full

I've checked if DbaTools can work with Analysis Services, and searched high and low for solutions. I've found https://www.inovex.de/blog/azure-automation-analysis-services/, where I've copied the Powershell code from.

How to I ensure that the Automation Services does not change IP, or should I just allow all IP address access to my cube?

You have two options here, which I can think of.

You can stick with the current setup of your Automation Account and add to firewall of the Azure Analysis Services instance IPs of the Azure Automation.

There is official links to this list, which you can download from here:

Azure IP Ranges and Service Tags – Public Cloud

Second option is to go with  Hybrid Runbook Worker. You can set up a worker designated to run your PowerShell scripts and then you have to white-list only IP of this particular worker. You can even use a machine from the onpremise infrastructure.

Automate resources in your datacenter or cloud by using Hybrid
Runbook Worker


Storing multiple types of users and their respective credentials in separate or same table? So there have been many questions on Stack Exchange DBA and Stack Overflow that go close to answering this, but there's still some dispute within my team on how to properly do a database design.

In our case, we have two different types of accounts - students and sponsors.

The students have the following attributes associated: id, firstname, lastname, gender, university, colelgeyear, major, ethnicity, housing, etc.

The sponsors have the following: id, name, businesstype, firstname,lastname, position, phone, address1, address2, etc.

So there's a reasonable difference in the type of functionality that the two accounts are involved with and the type of information we store about them. The question however has been brought up of storing account credentials i.e. hashedpasword, salt, etc. I know that ideally we would not store this on the same DB and access only via API. But, let's say that we have to in this case for proof of concept reasons.

Should we create a new general account table that contains the following: id, email, hashedpassword, salt, resetpasswordexpires, tokens, verification, role (this determines whether an account is for a student or a sponsor).

The argument for creating this new table so far has been the following:

doing so would allow us to reuse some of our DB queries for all authentication, forgotpassword, tokens and related functionality.
Reduce the number of joins necessary
Reduce the amount of data being queried. E.g. you don't need to know a student's college to reset his/her password.

The argument against creating this new table and instead baking the credentials into the students table and sponsors table
has been the following:

that it adds unnecessary complexity to the DB design
the students and sponsors dont share enough commonality in functionality.
That eventually saving passwords offsite would allow us to easily delete a column the sponsors table and students table and then proceed as we usually would.
That DB Queries have to be written anyways to eventually find out from the accounts table what type of account was authenticated

Here are some related links that I have found but haven't quite answered, and in particular have never addressed the code-reuse argument:

Users and Pass in Same Table?

Users and Pass in Same Table? Part 2

Multiple types of users, different tables?

This question appears to me to be related to another one, concerning generalization/specialization (aka inheritance) vs. association. The "baking" approach is one of the strategies to map inheritance to relational databases (table per concrete class in Hibernate). The separate table approach can also be regarded as such a strategy (table per subclass), or as a mapping of associated, not inherited, objects.

By the way, talking of inheritance, your students and sponsors have quite a bit in common, so they might be generalized to person, which could then also carry the credentials.

That said, let me comment on your criteria. The criteria against don't appear particularly strong to me:

Complexity: About the same in both solutions. Having two instead of three tables still means to have one piece of functionality around credentials which needs to be applied to both students and sponsors.
Commonality: No matter whether the rest of student and sponsor data and functions have much in common, authenticating users certainly is a significant bit of commonality.
Transferring the credentials part to another platform won't get easier if it is included in the student or sponor table.
This will only be significant if there are lots of such queries, or if they are very intricate. Even then you can simulate the separate table by an SQL union, and, the other way round, the "baked" tables by joins.

Unfortunately, the critera for creating the new table aren't compelling either:

Cf. criterium 4 above.
Separating person from credentials appears to me even to introduce a join (if needed) between the two, not to save any joins.
The amount of data, for those few attributes of all involved objecs, should not be a problem.

To sum up, neither of both solutions will break your neck. I have still a favor for the separate table (and, in particular, for separate program constructs to handle credentials), because access to a system is a separate concern. That's why holders of access to a system are sometimes not called users but principals. E.g. there might be credentials for web or rest services of a system that are not bound to any person, or credentials for automatic jobs operating on your data. As you said, granting or refusing access has not much to do with the attributes of a person, like name or age.


Receiving connection timeout error when call PQconnectdbParams to connect to Postgres What are the allowed format for sending "connection string" to the PQconnectdbParams method to connect to Postgres DB?

I am trying the following, but it's not working and receiving timeout errors always. I am not sure what I was doing is wrong.

provider_dsn := 'host=xxx.xxx.us-west-2.rds.amazonaws.com port=5432 dbname=dbname password=my_password user=user_name'

Error message receiving is as follows

ERROR:  could not connect to the postgresql server: timeout expired

I can provide further details if required.

Note: I can connect the DB using psql command from the terminal but can't when I use PQconnectdbParams with same credentials.

You are mixing up the different connection functions.

Your call would be correct with PQconn. PQconnectdbParams takes two parameters per connection string keyword

Replace PQconnectdbParams with PQconn and you should be fine.


Suppress SQL Server Management Studio auto complete Is there a way to temporarily suppress SQL Server Management Studio's auto-complete while typing a query? I don't want to disable auto-complete completely just say hold down some key while typing in a particular word so that it doesn't get in the way.

For example say I had the following query

SELECT Foo, Foo2 FROM SomeTable

As I type Foo and then hit space-bar SQL Server Management Studio's auto-complete kicks in and completes Foo to FooBar.

Hit ESC to close the window when done typing the word before hitting space, but if you hit ESC after 'fo' and then type 'o' it will kick in again.

Another option if you don't use it often would be to disable it (Tools->Options->Text Editor->Transact-SQL->General->Auto list members) and use CTRL+J to bring it up manually when you want to use it.  Red Gate also has a product called SQL Prompt which might offer more control.

In addition there is an "Intellisense Enabled" option under the Query menu and on the Query toolbar if you want to temporarily disable/enable it.


create multi valued variable inside mysql stored procedure I have two IN variables into my MYSQL stored procedure. I pass one into a select statement and the value is taken in a temporary variable which is passed to the second SQL statement.

CREATE PROCEDURE `usp_mystoredproc`(IN sText varchar(25), IN myId int(21))
begin

set @myCid= ( SELECT tabId AS id
FROM            table1
WHERE           IFNULL(tabId , '') != '' and table1_id= myId);

SELECT  searchItem
FROM    table2
WHERE  searchItem LIKE CONCAT(sText , '%') and table2_id =  @myCid
LIMIT                   15 ;
END

Now the issue is, in the above stored procedure, @myCid gets multiple values and it fails.
I need to get all those ids and pass them to the second SQL. How do I handle this?

Join table1 and table2. Something like:

SELECT  t2.searchItem
FROM    table2 as t2
JOIN    table1 as t1
    ON t1.tabid = t2.table2_id
WHERE   t2.searchItem LIKE CONCAT(sText , '%')
  AND IFNULL(tabId , '') != '' and t1.table1_id= myId
LIMIT                   15 ;

Bottom line, you don't need the @myCid variable


Is it okay to define a foreign key referencing a ‘user_id’ column from every table? I have a multi user site which in which

a user can have many restaurants,
each restaurant can have many menus and
each menu can have many items.

For security purposes, I was going to add a user_id foreign key making a reference to the user.user_id column from each of the pertinent tables. That way, when updating the data, I can use the id of the row they are updating in combination with their user_id (stored in a session at the application program level) to ensure they don't maliciously update or view another users content.

Questions

Is this a common practice?
Is it still normalized to do this?

Responses to comments

The code is only running on a website I control. But I want to prevent a user from accessing a row of data that he/she does not own, based on his/her user_id value. For instance, at the application program level, if they send a GET request to /menus/1 it will retrieve the row from the menu table with the id value of 1.

For your first question, I've managed many systems that do this. Some actually defined that Foreign Key relationship. Others had the "foreign key" field, but didn't define it in the database as key. This led to interesting quirks like data loaded outside of the application (pre-populated rows) having "updated by" fields with values that weren't users in the user table. Typically the tables had "ChangedBy" columns that stored User IDs and "ChangedOn" fields that stored a date/time for the change event.

For your second, why wouldn't it be normalized, if you set up the Foreign Key relationship back to a users table? You aren't duplicating the user information at the menu, restaurant, etc. tables, right? Just the foreign key ID field.


Extended Entity-Relationship Diagram: Do the sub classes have foreign keys or primary keys? Consider this example (they are disjoint, by the way):

There are two scenarios which I am  guessing:

While translating it to relational tables,
the rows in PROGRAMMER are unique which do not exist in EMPLOYEE table and it has a PRIMARY KEY
They already exist in EMPLOYEE table and the PROGRAMMER has a foreign key which references it.

Which scenario is correct? or both are wrong?

It's not an either / or situation. The answer for your particular system may exists as a combination of these two options. Let me explain.

If the only attribute in common between the Programmer, Engineer and Admin tables are the Emp# then I'd likely implement them as separate tables. Each would have Emp# as the primary key. I would not implement the Employee table at all since it added no additional value to the run-time system.

If the three sub-types had many columns in common, say Name, HireDate, Department .. HolidayBalance, ContactNumber etc., and only a few sub-type specific values (as you've annotated on your diagram) you could put all columns in Employee and make the inapplicable ones (e.g. a Programmer's EType) NULL. A view can be defined for each sub-type if excluding the NULLs becomes tedious.

Likely the truth lies somewhere between these two - there are enough specific attributes in each sub-type to warrant its own table, but enough common ones to justify an Employee table, too. In this case I'd make Emp# the primary key of Employee and also each sub-type would have the primary key of Emp#, too. Further I'd define Programmer.Emp# to be a foreign key referencing Employee.Emp#, and the same for the other sub-types. In this way uniqueness is maintained within each sub-type and so is integrity between the sub-type and super-type. In other words, I'd make a one-to-one relationship between Employee and each sub-type.

I would not implement a surrogate key in each of the sub-types (for example ProgrammerId) as it adds nothing that Emp# can't already achieve.

Note that it is difficult to declaratively ensure mutual exclusion between the sub-types. This will be enforced in the application or through triggers.


Oracle - abort within a "before insert" trigger without throwing an exception I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these records, for business reasons, we don't want in this table. I was thinking I could write a trigger that "aborts" any of these rows without throwing an error (so that the application doesn't fail in the front end, we want it to think it did its thing). I am intimately familiar with this application and know it won't cause any ugly side effects, we just can't modify it to behave the way we need it to.

So, is this possible, and if not, what would be another way to go about it? I thought about having some sort of Job that routinely cleans the table for records that we don't want, but I would rather for these records to never be there to begin with so there's no degree of "dirtiness" at any one time in the table.

The only way for a trigger on a table to prevent an INSERT operation from completing is to throw an error.

It is, as you have stated, a huge hack, but you could

Rename the table
Create a view that has the same name as the original table
Create an instead of trigger on that view that only does an INSERT on the table if you want the row to be persisted.  Otherwise, the trigger would do nothing.

That should work so long as the application isn't doing a MERGE into the table.  Of course, it's a hack upon a hack so it's definitely not going to win any awards for clean code.