Passing passwords more securely to mysql utilities on the command line This question is related to another question I subsequently asked here: Using the output from mysqldiff.exe to transform database2 to match database1.

I have been trying to use mysqldiff.exe, part of MySQL Utilities group of tools. The idea is to identify schema differences between two databases, or other contained objects (tables, routines, triggers, etc).

Basically this command:

mysqldiff.exe --server1=root:password@localhost:3306
--server2=root:password@localhost:3306 db1:db2

seems to work, though I'm yet to fully grasp how to use this program's output for the purpose of transforming the one db to match the other! I guess I might have to ask that question differently further down the line.

My immediate issue is:

This utility screams: # WARNING: Using a password on the command line interface can be insecure., yet I am unable to find a means to avoid spilling out my passwords out there! There is no such option from the available help.

With regular MySQL binaries (mysqld.exe, mysql, mysqladmin, etc), I am able to use --login-path=xxxx, having configured the credentials using mysql_config_editor.exe. With Mysql Utilities, how can I solve this issue?

PS: I realize that my question's tags are a bit imprecise, but I stuff like "mysql-utilities" and "mysqldiff" are not available!

You can put the password in the my.cnf (or my.ini for Windows) and then login without specifying the password "over the wire". Obviously you want to keep that file secure, but in any decent setup, this should not be a problem.

LOGGING IN

[pol@localhost inst]$ ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u root  -- **NOTE: no password is needed here.**
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

CONTENTS OF MY.INI OR MY.CNF

[client]
port        = 3306
socket      = /home/pol/Downloads/software/mysql/5.6/inst/mysqld.sock
host            = 127.0.0.1
user        = pol
password    = dba   --  **<<-----NOTE password in client section**

PASSWORD NOT VISIBLE

mysql> \! ps -ef |  grep mysql
pol       2173  2122  0 Apr19 pts/0    00:00:19 ./bin/mysqld --defaults-file=./my.cnf
pol       9294  2197  0 00:09 pts/1    00:00:00 ./bin/mysql --defaults-file=./my.cnf -S ./mysql.sock -u root   **NOTE - no password visible "over the wire"
pol       9360  9294  0 00:16 pts/1    00:00:00 sh -c  ps -ef |  grep mysql
pol       9362  9360  0 00:16 pts/1    00:00:00 grep mysql


Postgres random text in JSONB column Following is the query I'm using to scrub some fields in the JSONB column. I'm trying to radomize the first and last name so would like to use something like md5(random()::text) as values.
    update people set
    data = to_jsonb(data) || '{"firstName": "random_text", "lastName": "random_text"}'
    where id = 'b3c09005-7afb-4ad6-922d-76078875e59e';

I tried replacing random_text with md5(...) but I get an error

"DETAIL:  Token "md5" is invalid.".

I also tried using || to concat but that didn't work either.

You have to use the md5 hash as a part of the string so you have to concat it

CREATE TABLE people("id" varchar(50), "data" jsonb,"firstName" text, "lastName" text)

    update people set
    data = to_jsonb(data) || ('{"firstName": "' || MD5('PostgreSQL MD5') || '", "lastName": "' || MD5('PostgreSQL MD5') || '"}')::jsonb
    where id = 'b3c09005-7afb-4ad6-922d-76078875e59e';

db<>fiddle here
still md5 is old and not very secure, so you should use a more secure version


How to manage constants within an Oracle Database This question touches on a few different parts of Oracle that I'm not particularly familiar with so bear with me:

I am trying to redesign some of the procedures and functions within the database I manage. One of the most annoying things is the use of integers to express the status of rows.

For example rows that need to be processed get the number 3, rows that have been processed get the number 0 and rows that are in the middle of processing get number 1. This is a simplified example.

What I was hoping to do was code those integers into constants so when the procedures are written the words would be self-documenting...

I've tried to use packages and functions to manage these constants. Everything comes out a bit messy. The cleanest I've found is:

CREATE OR REPLACE FUNCTION PROCESSED RETURN NUMBER AS BEGIN RETURN 0;    END;
/

This allows you to type the sql below which looks relatively neat.

SELECT rows
FROM table
WHERE status = PROCESSED;

The problem I've found is that these columns are indexed to return quickly but the functions mean the indexes aren't used.

With that background my question is: How should constants be managed in Oracle effectively? What solution has the best trade off for visual simplicity, logical organisation and database performance.

This seems to be an open and shut case for placing the meaningful codes into another table with a foreign key to it from this column.

It restricts the values that can be used and provides a meaning for the numbers.


Data Modeling for different roles This is my first post here, so I hope its clear enough. I want to make an application for people who sell, buy, rent and fix a certain product.

I need to create a database that would keep the data of the product and of the accounts (with logins and personal information). My problem is here, an “account” can be a seller, a buyer, a renter, a fixer, it can be all, none, or some. The sellers, buyers, renter and fixer would have different fields though, like a seller would be able to post products, and have save some information that only a seller would have, a fixer would just be able to put his listing, but a seller, can also be a fixer. A buyer would be able to sell products too and favorite others. The difference between a seller and a buyer is that a seller has to be a company, etc…

My idea of the databases would be something like this:

Product

product_id
product_name
owner_id (account_id)

Accounts

account_id
email
password

Seller

seller_id
account_id
business_name
address
[some fields that only sellers would have...]

Buyer

buyer_id
account_id
first_name
last_name
address
[some fields that only buyer would have...]

Renter

renter_id
account_id
business_name
address
[some fields that only renter would have...]

Fixer

fixer_id
account_id
business_name
address
years_experience
[some fields that only fixers would have...]

Then other tables like account_favorite (to keep info on which products have been chosen as favorite).

Now, I feel that the way I'm doing it is not the correct way. Also thinking that if in the future, I think about something else like for example "collector" I would have to create a new table. Because this is an application that will be used by a lot of people I have to care about speed, but I also have to care about people maintaining and analyzing the data. I hope this post was clear, if it's not, please ask me.

If the roles that users can play are quite distinct, then yes you're on the right track.  Where they have fields in common, then put them in the shared Accounts table.  If that leaves just a few fields in the various role tables, you should probably ditch them and just have a single Accounts table with some spare columns.

The trade-off is greater complexity with more tables but (potentially) better performance with a few narrow table than a single wide and long table.  My normal inclination would be to start with a simpler model and then optimize for performance only if necessary, but given the pain in restructuring a database, going with the more flexible model you've described from the beginning is probably worthwhile.


What is the maximum number of local variables that can participate in SET operation? I have a stored procedure that contains business logic. Inside it I have around 1609 variables (don't ask me why, this is how the engine works). I try to SET a variable to the concatenated value of all other variables. As a result during creation I get the error:

  Msg 8631, Level 17, State 1, Procedure XXX, Line YYY Internal error:
  Server stack limit has been reached. Please look for potentially deep
  nesting in your query, and try to simplify it.

I figured out that the error is due to the number of variables that I need to use in the SET operation. I can perform the assignment by splitting it in two.

My question is are there some restrictions in this area? I checked, but I did not find any.

We checked the error described in this KB, but this is not our case. We don't use any CASE expressions inside our code. We use that temporary variable to prepare a list of values that have to be replaced using a CLR function. We updated our SQL Server to SP3 CU6 (latest up to date), but we still experience the error.

Msg 8631, Level 17, State 1, Line xxx
Internal error: Server stack limit has been reached.
Please look for potentially deep nesting in your query, and try to simplify it.

This error occurs with long SET or SELECT variable assignment concatenation lists due to the way SQL Server parses and binds this type of statement - as a nested list of two-input concatenations.
For example, SET @V = @W + @X + @Y + @Z is bound into a tree of the form:
ScaOp_Arithmetic x_aopAdd
    ScaOp_Arithmetic x_aopAdd
        ScaOp_Arithmetic x_aopAdd
            ScaOp_Identifier @W
            ScaOp_Identifier @X
        ScaOp_Identifier @Y
    ScaOp_Identifier @Z

Each concatenation element after the first two results in an extra level of nesting in this representation.
The amount of stack space available to SQL Server determines the ultimate limit to this nesting. When the limit is exceeded, an exception is raised internally, which eventually results in the error message shown above. An example process call stack when the error is thrown is shown below:

Repro
DECLARE @SQL varchar(max);

SET @SQL = '
    DECLARE @S integer, @A integer = 1;
    SET @S = @A'; -- Change to SELECT if you like

SET @SQL += REPLICATE(CONVERT(varchar(max), ' + @A'), 3410) +';'; -- Change the number 3410

-- SET @S = @A + @A + @A...
EXECUTE (@SQL);

This is a fundamental limit due to the way multiple concatenations are handled internally. It affects SET and SELECT variable assignment statements equally.
The workaround is to limit the number of concatenations performed in a single statement. This will also typically be more efficient, since compiling deep query trees is resource-intensive.


Upgrading SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3 We need to upgrade our production SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3. So which one would be more stable out of those 2 service packs and also how could this be achieved with a detailed step if possible.

Before upgrading the Production Server directly, we plan to upgrade our test servers which have SQL Server 2012 Enterprise 11.0.2100.60 version on it.

Microsoft recommends you update to both the latest SP and the latest CU which means SP3 and CU2. As a side note if you used WSUS with the right classifications ticked this is how it would be updated "automatically". I wouldn't recommend this but it does help when explaining your update policy to users.

For future versions you can also check http://sqlserverupdates.com/ which is managed by the Brent Ozar company. It's incorrect on some of the older versions of SQL Server but perfectly fine for SQL 2012+ (I've notified them and they thanked me but didn't fix the problem).

It's always highly recommended to test updates on lower environments first. I started keeping a list for fun and found literally 20 different ways for the update to fail. Problems occur and not infrequently, though you can almost always recover the situation without resorting to the backups.

Which reminds me, always, and especially before any update, make sure you have valid working backups of everything, especially including your system databases. If you use encryption keys and certificates (or SSRS) you should export and save those as well.

As to the process:

If you have a simple standalone install of SQL Server then it's just a matter of running it and clicking through; or running it from the command line with options to update all instances and shared components.
But if you use other features of SQL Server such as Replication, Clustering, Availability Groups, then you need to look up more specific instructions because the servers should be done in a specific order.
Also if you use Master Data Services you MUST check it's working afterwards as if the update touches its components it will stay non-functional until you go into the GUI and click to update, and this can't be tested or automated; thanks Microsoft.

I suggest starting to keep your own list of your update process and document the failures and resolutions you find as you go along, because you or your successor may need to refer to it later.


How to get a working and complete PostgreSQL DB backup and test I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users.

From what I've read, and it could be wrong, finding a good PostgreSQL blog has been challenging so please feel free to recommend some to me, I need to figure out how this app works so I can have trust in my backups and Slony replication.  I had a developer restore a backup I took from PgadminIII via custom, directory, and tar format while selecting OIDs but he said two of them didn't load, tar did but it was only the directory, not the data.  I'm really confused now.

I am using PGAdminIII, it has a pg_dump and pg_dumpall option.  I want to back everything up that I need to test restore this database somewhere and verify that yes, all the data we need and our backup is good.  Eventually I want to write an auto-restore script but one day at a time.

pg_dumpall apparently has a -globals option that's supposed to backup everything, but the help for pg_dumpall shows a -g, --globals-only          dump only global objects, no databases, not a --globals option.

I thought pg_dumpall would at least backup foreign keys, but even that seems to be an 'option'.  According to the documentation, even with pg_dumpall I need to use a -o option to backup foreign keys, I can't really imagine when I wouldn't want to backup foreign keys and this would make more sense as a default options.

How would I take care of orphaned users and validate I have everything?  I'd like to actually restore my backup file on another server and verify everything works.  If anyone has any suggestions on how to take a real backup in PostgreSQL and restore, I'd be very grateful.

I had a PostgreSQL server but I still can't fathom why the app would not backup OIDs by default!  It seems like 99.9% of the time you would want that.

UPDATE 1:

The Postgres documentation mentions that the globals option I was looking for seems to be a default option on this version, but it still needs the -o option.  If someone can verify or give me an example command to restore a single database elsewhere with everything it needs I'd appreciate it.

Edit: being asked by site to show the uniqueness of this question by editing my question.  This question raises the issue and get's clarity on OIDs in backups, the difference between globals and non globals, as well as testing restores recommendations to ensure the backup is good as opposed to just backing up.  Due to the answers I was able to backup, figure out globals/oids, and started a test restore process nightly on Postgres using cron jobs.  Thanks for the help!

You can dump the whole PostgreSQL cluster with pg_dumpall. That's all the databases and all the globals for a single cluster.  From the command line on the server, I'd do something like this.  (Mine's listening on port 5433, not on the default port.) You may or may not need the --clean option.

$ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql

This includes the globals--information about users and groups, tablespaces, and so on.

If I were going to backup a single database and move it to a scratch server, I'd dump the database with pg_dump, and dump the globals with either

pg_dumpall --globals-only, or
pg_dumpall --roles-only (if you only need roles)

like this.

$ pg_dump -U postgres -h localhost -p 5433 --clean --file=sandbox.sql sandbox
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --globals-only --file=globals.sql

Outputs are just text files.

After you move these files to a different server, load the globals first, then the database dump.

$ psql -U postgres -h localhost -p 5433 < globals.sql
$ psql -U postgres -h localhost -p 5433 < sandbox.sql

  I thought pg_dumpall would at least backup foreign keys, but even that
  seems to be an 'option'. According to:
  http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even
  with pg_dumpall I need to use a -o option to backup foreign keys

No, that reference says "Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used." (Emphasis added.) I think it's unlikely that your application references the OID columns. You don't need to use this option to "backup foreign keys".  (Read the dump file in your editor or file viewer.)


Why is replication attempting to distribute NULL values into Identity columns We are sporadically getting this error:

Explicit value must be specified for identity column

in our replication environment. I have checked the transaction command that is reporting the error and it contains three inserts for three different tables. All three inserts DO HAVE the identity value in them. If I remove the primary keys from the tables and allow nulls then three records with NULL in what was the primary key field are inserted AS WELL AS three records with the correct primary key values (as seen using sp_browsereplcmds).

I remove the null value records and re-enable the primary keys, identity and not for replication flag and replication continues as normal.

I'm really stumped and can't figure out what is going on, any suggestions appreciated.

Currently I'm considering modifying the automatically created MSSPs so that if it finds a null in what would be the primary key value it ignores it, but this shouldn't be necessary.

Cheers

After much trial and error we think the answer was and has always been triggers! Despite being disabled in the subscriber they kept on being re-enabled which was causing a second insert command to run for affected replicated commands.

Basically check your triggers!


Get only overall summary WITH ROLLUP and GROUP BY for multiple fields Performing a WITH ROLLUP when grouping by multiple fields, MySQL returns a rollup row for each group, as well as the overall summary:

CREATE TABLE test (name VARCHAR(50), number TINYINT);
INSERT INTO test VALUES
    ('foo', 1), ('foo', 1), ('foo', 2), ('foo', 3), ('foo', 3),
    ('bar', 1), ('bar', 2), ('bar', 2), ('bar', 2), ('bar', 3),
    ('baz', 1), ('baz', 2), ('bar', 2);
SELECT name, number, COUNT(1) FROM test GROUP BY name, number WITH ROLLUP;

+------+--------+----------+
| name | number | count(1) |
+------+--------+----------+
| bar  |      1 |        1 |
| bar  |      2 |        3 |
| bar  |      3 |        1 |
| bar  |   NULL |        5 |
| baz  |      1 |        1 |
| baz  |      2 |        2 |
| baz  |   NULL |        3 |
| foo  |      1 |        2 |
| foo  |      2 |        1 |
| foo  |      3 |        2 |
| foo  |   NULL |        5 |
| NULL |   NULL |       13 |
+------+--------+----------+

I'm not inerested in the rollups for foo/bar/baz, only the overall summary. What's the most efficient way to achieve this?

SELECT * FROM
(
    SELECT name, number, COUNT(1) `count` FROM test
    GROUP BY name, number WITH ROLLUP
) A WHERE (ISNULL(name) + ISNULL(number)) <> 1;

or

SELECT * FROM
(
    SELECT name, number, COUNT(1) `count` FROM test
    GROUP BY name, number WITH ROLLUP
) A WHERE ISNULL(name) = ISNULL(number);


Selecting row grouped by an "add-on" indicator I have a (pre-existing, no I did not design it like this!) schema / dataset like this:

CREATE TABLE t(id,name,seq,addon,kind)
AS VALUES
    ( 1234::int, 'A' , 345::int, 'f'::bool, 'c'  ),
    ( 1235     , NULL, 346     , 't'      , NULL ),
    ( 1236     , NULL, 347     , 't'      , NULL ),
    ( 2345     , 'B' , 348     , 'f'      , 'x'  ),
    ( 2346     , NULL, 349     , 't'      , NULL ),
    ( 3456     , 'C' , 350     , 'f'      , 'c'  ),
    ( 3457     , NULL, 351     , 't'      , NULL );

Where:

The "id" field is just a SQL sequence (order not guaranteed)
The "seq" field is guaranteed to be sequential throughout the table, e.g. table is normally sorted on this column.
The "addon" field is indicating a group of related entries, e.g. TRUE entries are related to the nearest FALSE entry with a lower "seq".

I want to SELECT all the rows with "kind" = 'x' and all of their associated "addon" rows, without getting any other "addon" rows for other kinds.

So for instance:

SELECT * FROM t WHERE kind = 'x' AND ???

Would give me:

  id  | name | seq  |addon | kind |
------+------+------+------+------+...
 2345 | "B"  | 348  |  f   | "x"  |
 2346 |      | 349  |  t   |      |

I have PostgreSQL 9.6...

You just need to create a real grouping. Here we use a Window Function count(x) which counts all things NOT NULL. Try it with this simplified example,

SELECT count(a) OVER ()
FROM ( VALUES (true),(false) ) AS t(a);

In order to count just what we want, we use OR which evaluates the rhs if the lhs is not true. So here we test if the row IS FALSE (returning true if the value is false), if the condition does not evaluate to true (meaning the value is true or null) we return null (so count(x) skips it). here is the full query,

SELECT *,
  count((addon IS FALSE) OR NULL) OVER (ORDER BY seq) AS grp
FROM t

  id  | name | seq | addon | kind | grp
------+------+-----+-------+------+-----
 1234 | A    | 345 | f     | c    |   1
 1235 |      | 346 | t     |      |   1
 1236 |      | 347 | t     |      |   1
 2345 | B    | 348 | f     | x    |   2
 2346 |      | 349 | t     |      |   2
 3456 | C    | 350 | f     | c    |   3
 3457 |      | 351 | t     |      |   3
(7 rows)

now you can easily do anything you want..

WITH t AS (
  SELECT *,
    count((addon IS FALSE) OR NULL) OVER (ORDER BY seq) AS grp
  FROM t
)
SELECT *
FROM t AS t1
WHERE EXISTS (
  SELECT 1
  FROM t AS t2
  WHERE addon IS FALSE
    AND kind = 'x'
    AND t1.grp = t2.grp
)
ORDER BY seq;

To see this pattern elsewhere, check out the gaps-and-islands and postgresql, using this search


Table that is a relation of one field to every other similar field So, I have two tables, tblOwners and tblNonOwners. These tables are filled with users. I need a third table that keeps track of if any one user has 'seen' any other user from the other.

What I though is having a tblSeen that has three records, the userID who is using the app, and the profileID that the user has seen/not seen, and a boolean fldSeen as 0 or 1.

How would I populate these tables automatically? I could create records for each user that signs up, but how do I create a record for every single relationship?

Solved this after some clever googling.

The Query is

INSERT INTO tblSeen(UserID, ProfileId)
SELECT 'some_user', pmkID FROM tblOwners

This will create as many rows as it can and fill it out, so I just have to do this when the user goes to the page.


Use Group Managed Service Account for SSRS We have a production environment running SQL Reporting Services 2014. The service is running as a group managed service account domain\SSRSProdSvc$
We are now trying to change our test environment to use managed service accounts for SSRS, however I can't change it.
If I go into Reporting Services Configuration Manager and try and change the service account I get an error

Setting Windows Service Identity to Windows Account
The Windows account information used to run the Windows service has failed to update. Previously designated settings are being used instead.

If I go into Windows Services I can change the account to domain\SSRSTestSvc$ and the service starts, however in the Reporting Services Configuration Manager it still comes up with the previous domain account.
I'm not sure how it was configured on Live, but when I look at the Reporting Services Configuration Manager it's service account is domain\SSRSProdSvc$
Any ideas how to fix test?

Try removing the managed service account like so by running the following command on your local SQL/SSRS Server:

Remove-ADComputerServiceAccount -Identity [Your Server Name] -ServiceAccount domain\SSRSProdSvc$

https://technet.microsoft.com/en-us/library/dd378979(v=ws.10).aspx

And if this hasn't been done, add the new managed account to the local server:

  On the domain controller, click Start, and then click Run. In the Open box, type dsa.msc, and then click OK to open the Active Directory Users and Computers snap-in. Confirm that the Managed Service Account container exists.
  Click Start, click All Programs, click Windows PowerShell 2.0, and then click the Windows PowerShell icon.
  Run the following command: New-ADServiceAccount [-SAMAccountName ] [-Path ].
  Associate the new MSA to the computer account by running the following command: Add-ADComputerServiceAccount [-Identity] <ADComputer> <ADServiceAccount[]>

  See Add-ADComputerServiceAccount for the complete syntax.

per Microsoft:
https://technet.microsoft.com/en-us/library/dd548356(v=ws.10).aspx


Questionable duplicate index suggested by optimization Good day, I have the following sql server database table:

Please note the compound primary key.  This was done for 3 reasons:

Prevent duplicate entries
Improve query performance as all queries will have all of thoes 3 keys.
We needed and index, and I did not want to introduce a random ID.

Please also note that this table was designed with its size in mind, This table is going to store millions and millions of rows of data.

OK now for my actual question. I am using azure sql server to host this db. and I have enabled automatic tuning. And strangely enough I see that it then went and created a new index. (see below)

Now In my mind this seems to be a duplicate index, as the same columns are being indexed.

So I now have two indexes on my table:

Original (My PK):

ALTER TABLE [dbo].[SensorDataRaw] ADD  CONSTRAINT [PK_SensorDataRaw] PRIMARY KEY CLUSTERED
(
    [DateTime] ASC,
    [SensorId] ASC,
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Newly Added (Auto Created by azure tuning):

CREATE NONCLUSTERED INDEX [nci_wi_SensorDataRaw_DC9789077DA75B4440AC8BFE3E2AA198] ON [dbo].[SensorDataRaw]
(
    [Key] ASC,
    [SensorId] ASC,
    [DateTime] ASC
)
INCLUDE (   [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Observations:

The order of the coloumns has been reversed in the new index.
The new index is NOT unique
the new index includes the value column.

Please note my knowledge on indexes is not advanced, hence me asking this.

So my question is:

Can someone explain why the newly added index is better than my intially created one.
How can I remove the two indexes and just create one that covers both cases. With this being such a massive database I cannot afford the space that both these indexes will take up.
Is the maybe a better design alternative?

Additional Info:

I'm assuming the type of queries becomes important here, So I have listed some examples.

All queries include DateTime, SensorId, and Key.

Simple Queries:

Select SensorId Where average value for key w is greater than x where time between (y,z)

Graphing Data:

SELECT AVG([Value]) AS 'AvgValue',
    DATEADD( MINUTE,
    (DATEDIFF(MINUTE, '1990-01-01T00:00:00', [dbo].[SensorDataRaw].
[DateTime]) / @IntervalInMinutes) * @IntervalInMinutes,
        '1990-01-01T00:00:00'
    )      AS 'TimeGroup'
 FROM [dbo].[SensorDataRaw]
 where
    [dbo].[SensorDataRaw].[SensorId] = @SensorId
    and [dbo].[SensorDataRaw].[Key] = @KeyValue
    and [dbo].[SensorDataRaw].[DateTime] Between @DateFrom and @DateTo
    and [dbo].[SensorDataRaw].[Value] IS NOT NULL
  GROUP BY (DATEDIFF(MINUTE, '1990-01-01T00:00:00', [dbo].[SensorDataRaw].
    [DateTime]) / @IntervalInMinutes)

The index suggested by the system is a much better fit for the query you have shown. You should aim to have columns with equality predicates as the leading columns.

Consider a phone book ordered by lastname, firstname. If your rquirement is to find all people with surnames between "Brown" and "Yates" and a first name of "John" then you need to read most of the phone book. If the phonebook was instead ordered by firstname, lastname you could easily find the "John" section and the first "Brown" in the section then all you need to do is read all the names until the lastname is after "Yates" or a new firstname is encountered.

It might not be the ideal index. Potentially you should just change the key columns in the clustered index to this order rather than creating a new one though. You need to evaluate this based on knowledge of your workload.


Email address unique or primary key? I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in complex joins and if an email changes I'd have to change all the foreign keys which requires a lot of effort.

But if my users table requires every user to have an email address and each of those email address should be unique, will adding a unique index on the email column suffice? Because afaik unique fields allow null values, whereas I require every user to have an email address, not allowing null values. Is there something I'm missing here? Or I'm suppose to make email column unique and make sure during data validation on the server that user does enter an email address so that every user has one?

Let's first distinguish between keys and indexes, key is part of the logical model and is often implemented with an unique index. You can however create a unique index without creating a key, but that can not be referenced by a foreign key.

A candidate key is something that uniquely identifies a row in a table, in SQL one of the candidate keys are normally used as a primary key (I never really understood why one of the c.k. is concidered "better" than the others, but that's another story), and the remaining c.k becomes unique constraints.

A unique constraint can be used the same way as a primary key can. Consider:

create table A ( x ... not null
               , y ... not null
               , z ... not null
               ,     unique (x)
               ,     primary key (y,z) );

create table B ( x ...
               ,   ...
               ,     foreign key (x) references A (x) );

create table C ( y ...
               , z ...
               ,   ...
               ,     foreign key (y, z) references A (y, z) );

B references the unique constraint and C references the primary key constraint.

NOT NULL is yet another kind of constraint. In your case you can enforce this for email without declaring it unique.

The next aspect of your post concerns the stability of a key, a key should be stable (but that doesn't mean it can never change, it does not have to be immutable). Some DBMS implements ON UPDATE CASCADE that can be of help for such operation, still if the key is distributed around your model it will be a pain updating it.

In your case I would probably choose another candidate key as the primary key, and declare email to be NOT NULL and UNIQUE.


Is there a way to alias columns for reference in subqueries? I have a SQL statement in PostgreSQL that takes the following form:

SELECT * FROM db WHERE (b + 1) IN (SELECT b FROM db WHERE a = 7)

This query returns each record whose b value, incremented by 1, is in the set of all b values of the records matching a = 7. However, I'm looking for a way to write this in the form of an EXISTS statement, since that may be more optimal. Something of the form

SELECT * FROM db WHERE EXISTS (SELECT 1 FROM db WHERE a = 7 and b = b* + 1)

where b* somehow references the b attribute not inside the subquery, but outside it. Is there a way to write the first query without IN and with EXISTS?

You need a table alias for both occurrences of the table and then prefix every column with the corresponding table alias to make the column references unambiguous.

select t1.*
from the_table t1
where exists (select *
              from the_table t2
              where t2.a = 7
                and t2.b = t1.b + 1);

Note that you probably want something like and t2.primary_key <> t1.primary_key in the sub-select as well.


Receiving the following error in step failure of an agent job: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x00040EDA
Description:
"Warning: Null value is eliminated by an aggregate or other SET operation.".

Please help to find the root cause for this temporary failure and that what can be done to avoid this in future.

Annoying isn't it. I've done some research and it turns out it is incorrectly reporting a ANSI warning as an error (resolved in SQL Server 2012, no patch available for previous versions).

One way to stop the error is to go through all your code, and wherever you have an aggregate function (e.g. SUM), use ISNULL to ensure the aggregated value can never be NULL:

i.e. change

SUM(Field1+Field2)

to

SUM(ISNULL(Field1,0)+ISNULL(Field2,0))

This makes the error go away but might be impractical.


Is there any collection of PowerShell scripts that help the task of SQL Server maintenance? I've been studying PowerShell and I've been (slowly) getting it into my day-to-day DBA tasks.

So far I've seen a bunch of disconnected PowerShell scripts for many disparate administration activities.

What I'd like to know is if there is an official (or recognizable) PowerShell scripts collection or repository that people usually go to for SQL Server PowerShell scripts for common DBA activities (something like Ola Hallengren's T-SQL scripts).

I don't know that anyone has written something along the lines of Ola's script within PowerShell. I know that PowerShell scripts for SQL Server are slowly growing over at the TechnNet Script Center. Then MSSQLTips.com also has started publishing tips working with SQL Server and PowerShell.

Then you also have SQL Server PowerShell Extenstions project over on CodePlex. I don't guess you would consider it a repository but a good number of folks blog on how to use it and such. Some task within SQL Server are made much easier using SQLPSX.

I also tend to check out a few MVP blog sites that tend to focus on PowerShell scripts:
Allen White
Aaron Nelson
Sean McCown (MidnightDBA) (Click on the PowerShell tab for all the videos on the subject)


Partitioning mysql table on the basis of fulltxt(datatype:text) column I want to partition below mysql table on the basis of fulltxt field having datatype is text. Here is my table structure.

CREATE TABLE links_copy (
  link_id int(11) NOT NULL AUTO_INCREMENT,
  site_id int(11) DEFAULT NULL,
  url varchar(255) NOT NULL,
  fulltxt text,
  indexdate date DEFAULT NULL,
  PRIMARY KEY (link_id),
  KEY url (url),
  KEY md5key (md5sum),
  KEY fulltext2 (fulltxt(100)),
  KEY indexdate (indexdate)
);
Partition By range(fulltxt)(
PARTITION p0 VALUES IN ('California'),
PARTITION p1 VALUES IN ('City'),
PARTITION p2 VALUES IN ('Council'),
PARTITION p3 VALUES IN ('Meeting'),
PARTITION p4 VALUES IN ('Agenda'));

Question:
Is it possible to partition on the basis of fulltxt, not on basis of primary key link_id?

Cannot do it.  Syntax errors.  Would not help if you could do it.

Show us the SELECTs that you hope to speed up via PARTITIONing.  (I will probably point out why PARTITIONing will not help any of them.)


sql server minus one day I have a server with SQL Server 2008 R2 installed in it. This server is used as a D -1 Database, in other words, the database it is one day behind from production server.

The problem is that my production database has more than 300GB and the daily restore is becoming an issue for the team who needs this server, because the job isn't finished before the morning day.

Is there some solution that replace the daily full backup, some kind of replication with delay ?

Instead of restoring a daily full backup, you could:

Take weekly full backups of production, restoring them to the D-1 server the next day.
Take daily differential backups of production, restoring them to the D-1 server the following day.

The daily restores from the differential backup will be much faster than restoring the entire database each day.

I'd advise against using replication for this; as @Mark Storey-Smith said, you will now have two problems to manage instead of one.


SQL Server stored procedure producing data related to months between two dates I'm stuck on the following problem.

I have a start range date and end range date calculated by a query and I have three columns also produced by another query, the outcome of the last query is:

PRJ_ID | START_DATE | END_DATE

I want to produce an output using a stored procedure that considering all the months between start range date and end range date produces an output like this:

PRJ_ID | MONTHS1-YEAR | MONTHS2-YEAR | ... | MONTHSN-YEAR
XXXX   | YES     | YES     | ... | NO

The yes or no is obtained checking if the current month is in between the start_date and end_date of the project (table obtained above).

I'm able to produce all the months between two dates with this code:

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'
        ,@EndDate   = '20110801';

SELECT
    DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM
    master.dbo.spt_values x
WHERE
    x.type = 'P'
    AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

But I'm stuck on producing the last result.

Here is my try, but I'm getting an error on @prjdates format that is not datetime:

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME,
        @minStart    DATETIME,
        @orig_estimated_MaxEnd    DATETIME,
        @maxEnd    DATETIME,
        @curr_planned_MaxEnd         DATETIME;

SELECT   @StartDate = '20110501'
        ,@EndDate   = '20150801';

SET @minStart = (SELECT MIN(start_date)
                 FROM [PPM].[dbo].[prj_details]
                 WHERE start_date IS NOT NULL
                   AND start_date <> '1900-01-01') ;

SET @orig_estimated_MaxEnd = (SELECT MAX( DATEADD(day,orig_estimated_duration*7, start_date)) As FinishDate
                              FROM [PPM].[dbo].[prj_details]
                              WHERE start_date IS NOT NULL);

SET @curr_planned_MaxEnd = (SELECT MAX(curr_planned_LF)
                            FROM [PPM].[dbo].[status_and_updates]
                            WHERE curr_planned_LF IS NOT NULL);

SET @maxEnd = (SELECT
                   CASE
                      WHEN @orig_estimated_MaxEnd > @curr_planned_MaxEnd
                        THEN @orig_estimated_MaxEnd
                      ELSE @curr_planned_MaxEnd
                   END AS MinValue);

print @minStart;
print @orig_estimated_MaxEnd;
print @curr_planned_MaxEnd;
print @maxEnd;

DECLARE @prjDates VARCHAR(MAX);

DECLARE @prjDatesTable TABLE
(
    [prjDate] DATETIME
);

DECLARE  @from datetime,
@to datetime;

SET @from = '20110501';
SET @to = '20150501';

;WITH Numbers (Number) AS
(
    SELECT
        row_number() over (order by object_id)
    FROM
        sys.all_objects)
INSERT INTO @prjDatesTable
    SELECT
        dateadd(month, number, @from) AS months
    FROM Numbers
    WHERE number <= datediff(month, @from, @to);

SELECT @prjDates = COALESCE(@prjDates + '],[', '') +
      (LEFT(CONVERT(VARCHAR, [prjDate], 101),6) + RIGHT(YEAR([prjDate]),2))
FROM @prjDatesTable ORDER BY [prjDate] DESC;

DECLARE @projects TABLE(PRJ_ID varchar(200),  START_DATE DATEtime, END_DATE datetime)
INSERT INTO @projects
    SELECT p.id,start_date,curr_planned_LF as END_DATE
    from [PPM].[dbo].[prj_details]as p
    left join [PPM].[dbo].[status_and_updates] as s
    on p.id=s.id
    where curr_planned_LF is not null
    and start_date IS NOT NULL;

--select * from @projects;
select *
from
    @projects
pivot
(
  count(prj_id)
  for start_date in  ([' + @prjDates + '])
) piv;

I would approach this by first creating a permanent dates table I can use to help create the desired output.  Then I would create a pivot table using dynamic T-SQL to allow for varying column names in the output.

Setup the test-bed in TEMPDB:

USE tempdb;

IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 'DatesTable')
BEGIN
    DROP TABLE dbo.DatesTable;
END

CREATE TABLE dbo.DatesTable
(
    dDate DATETIME
        CONSTRAINT PK_DatesTable
        PRIMARY KEY CLUSTERED
    , iDay TINYINT
    , iMonth TINYINT
    , iYear SMALLINT
);

INSERT INTO DatesTable (dDate, iDay, iMonth, iYear)
SELECT TOP(90000) DatesList.dDate, DATEPART(DAY, DatesList.dDate), DATEPART(MONTH, DatesList.dDate), DATEPART(YEAR, DatesList.dDate)
FROM (
    SELECT dDate = DATEADD(DAY, -1, ROW_NUMBER() OVER (ORDER BY o.object_id))
    FROM sys.objects o, sys.objects o1, sys.objects o2
    ) DatesList;

IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 't')
BEGIN
    DROP TABLE dbo.t;
END

CREATE TABLE dbo.t
(
    PRJ_ID INT NOT NULL
    , [START_DATE] DATETIME NOT NULL
    , END_DATE DATETIME NOT NULL
);

INSERT INTO dbo.t (PRJ_ID, START_DATE, END_DATE)
VALUES (1, '2015-08-01', '2015-08-03')
    , (2, '2015-07-01', '2015-08-03')
    , (3, '2015-10-01', '2015-10-03');

Here's the good bit:

DECLARE @PivotStmt NVARCHAR(MAX);
DECLARE @PivotCols NVARCHAR(MAX);

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20150501'
        ,@EndDate   = '20151101';

SET @PivotCols = '';

/* Here we are dynamically creating the month-columns for the pivot */
SELECT @PivotCols = @PivotCols + CASE WHEN @PivotCols = '' THEN '' ELSE ', ' END + '[' + STUFF(CONVERT(VARCHAR(30), CONVERT(DATETIME, CONVERT(VARCHAR(4), dt.iYear) + '-' + CONVERT(VARCHAR(2), dt.iMonth) + '-01 00:00:00'), 106),1,3,'') + ']'
FROM dbo.DatesTable dt
WHERE dt.dDate >= (SELECT MIN(t.START_DATE) FROM dbo.t)
    AND dt.dDate <= (SELECT MAX(t.START_DATE) FROM dbo.t)
GROUP BY dt.iMonth
    , dt.iYear
ORDER BY dt.iYear
    , dt.iMonth;

SET @PivotStmt = 'SELECT PRJ_ID
    , ' + @PivotCols + '
FROM
(
    SELECT t.PRJ_ID
        , MonthYear = STUFF(CONVERT(VARCHAR(30), CONVERT(DATETIME, CONVERT(VARCHAR(4), dt.iYear) + ''-'' + CONVERT(VARCHAR(2), dt.iMonth) + ''-01 00:00:00''), 106),1,3,'''')
        , [Yes] = ''YES''
    FROM dbo.t
        INNER JOIN DatesTable dt ON t.START_DATE <= dt.dDate AND t.END_DATE >= dt.dDate
    GROUP BY t.PRJ_ID
        , dt.iMonth
        , dt.iYear
) SourceTable
PIVOT (
    MIN([Yes])
    FOR MonthYear IN (' + @PivotCols + ')
) as pvt
ORDER BY PRJ_ID;';

/* print out the generated pivot statement for debugging */
PRINT (@PivotStmt);
/* execute the dynamically created pivot statement */
EXEC (@PivotStmt);

My example code above returns the following result set:

Notice, Sept 2015 is included in the output even though none of the data in dbo.t contains projects in September 2015.


Redshift throws Value too long for character type character varying(100) even when values are within the range I am aware what this error message Value too long for character type character varying(100) means. So I often look for the rows which cause the trouble and fix them appropriately as deemed fit by the requirement.

But I encountered an odd issue today where the error happens even if there isn't a rough row.

Failing insert query:

INSERT INTO training.archive_temp1 (id, booking, email, pcd_temp, property_id)
WITH x_pcd AS (
    SELECT e.id,
        e.booking,
        e.email,
        CASE
            WHEN LENGTH(e.pch) > 0 THEN (e.pch || ':' || e.pcd)
            ELSE e.pcd
        END AS pcd_temp,
        e.pcd
    FROM public.extracts_temp AS e WHERE e.id BETWEEN 274939128 AND 275083166
)
SELECT x.id,
    x.booking,
    x.email,
    x.pcd_temp,
    COALESCE(c2.property_id, c.property_id)
FROM x_pcd AS x
         LEFT JOIN public.property_codes AS c ON x.pcd_temp = c.code
         LEFT JOIN public.property_codes AS c2 ON x.pcd = c2.code
WHERE COALESCE(c2.property_id,c.property_id, 0) <> 0;

If I change x.email to x.email::varchar(100) it works.

Here's the catch.

SELECT max(length(email)) FROM training.archive_temp1;
-- returns 64

Weird. So I checked

SELECT max(length(email)) FROM (
SELECT e.id,
        e.booking,
        e.email,
        CASE
            WHEN LENGTH(e.pch) > 0 THEN (e.pch || ':' || e.pcd)
            ELSE e.pcd
        END AS pcd_temp,
        e.pcd
    FROM public.extracts_temp AS e WHERE e.id BETWEEN 274939128 AND 275083166
)
-- returns 66

If no row is crossing the character limit of 100, why does it throw the error? What is happening here?

If you need me to share results from any of your queries, please let me know. Since the rows are in 100000 range, can't share the entire data here and if I could share a minimum verifiable example for the case, I wouldn't be asking this question.

Redshift can store multi byte strings into varchar field. But if you define your field as varchar(100) it does not mean 100 characters. Instead it means 100 bytes. So if all the characters in the string are two byte characters, then the field can store at max 50 characters.

From the documentation,

  Use a VARCHAR or CHARACTER VARYING column to store variable-length strings with a fixed limit. These strings are not padded with blanks, so a VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters.

The problem is LENGTH function only returns the number of characters and not number of bytes excluding the trailing blanks. So getting the length on a multi byte character only returns 1. This is documented here.

The alternative OCTET_LENGTH can return the number of bytes instead of number of characters.

Running OCTET_LENGTH revealed trouble maker and it is fixed now.


Why is the result cache always disabled in Oracle 11g I'm trying to enable the result cache in Oracle 11g. I have used Enterprise Manager to change the result_cache_max_size and result_cache_max_result values to '1M' and '100' respectively (maybe these aren't optimal settings, but I'm just trying to get it to work!).

After I make these changes I use the following query to see if the changes have really been made:

select * from V$PARAMETER where LOWER("NAME") like '%result_cache%';

I can see that both have been set correctly. However, when I run queries with the result_cache hint, I can see from the Autotrace that the cache isn't being used. If I then run the following query:

SELECT dbms_result_cache.status() FROM dual;

I can see that it's status is 'DISABLED'. Restarting the database just makes the result_cache_max_size parameter go back to 0 (disabled).

How can I enable the result cache?

Are you using Enterprise Edition? It looks like this is a EE feature:

http://docs.oracle.com/cd/B28359_01/license.111/b28287/editions.htm

CTRL + F the docs and search for result cache and supported editions.


Does this medical database look correct? I'm pretty new to databases and I'm trying to create some schemas.

I've been working away at this one for a while, but I can't help feeling that it doesn't look right.

Essentially I have the following situation:

A given medical trial (of which there can be many) takes place in a varying number of medical centres.
Each centre has patients and practitioners.
Practitioners and patients have a consultation, and data is created.

It seems simple enough, but I've a feeling I've made a mess of it. The resultant database will be MySQL if that's relevant.

Does that look right?

As a very basic structure it is close.

The one glaring issue is that the relationship between trial and centre is the wrong way around.  You should have a CentreId on the Trial table, as I believe there can by multiple trials from 1 centre and not vice versa.

Also you may want to consider adding some extra details to Centre like name etc.

Lastly, the Patient -> Trial relationship may well be a many-to-many relationship and thus you  would need to add a table to encapsulate this relationship.


Passing array parameters to a stored procedure I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed.  I can indicate this with a big list of IDs.  I'm trying to avoid the "updates in a loop" pattern, so I'd like to find a more efficient way to send this bag of ID's into a MS SQL Server 2008 stored proc.

Proposal #1 - Table Valued Parameters.  I can define a table type w/ just an ID field and send in a table full of IDs to update.

Proposal #2 - XML parameter (varchar) with OPENXML() in proc body.

Proposal #3 - List parsing.  I'd rather avoid this, if possible, as it seems unwieldy and error-prone.

Any preference among these, or any ideas I've missed?

The best ever articles on this matter are by Erland Sommarskog:

Arrays and Lists in SQL Server 2008 - Using Table-Valued Parameters
Arrays and Lists in SQL Server 2005 and Beyond- When TVPs Do Not Cut it
Arrays and Lists in SQL Server 2000 and Earlier

He covers all options and explains pretty well.

Sorry for the shortness of the answer, but Erland's article on Arrays is like Joe Celko's books on trees and other SQL treats :)


SQL Server backup. PowerShell vs Python I'm about to refactor inherited scripts deployed on multiple servers that backup many other servers. They are a mixture of cmd, vbs and sql. I'm pondering what benefits would bring doing it with Python since I find it friendlier to script?
Mainly i'm thinking that with PS I can use something that could be considered native like Invoke-Sqlcmd or sqlps.
Is it just a matter of taste or might I stumble upon something nasty and unforeseen by using python?

I am not familiar with Python, but for your current requirement (i.e. backup).
I'd strongly recommend you to use PowerShell for two reasons:

MS in its SQLPS module (since SQL Server 2012), has a native cmdlet called

Backup-SQLDatabase
You may run this cmdlet on one central server to do the backup against multiple sql instances at once because this Backup-SQLDatabase can accept multiple sql instances, like the following example

Backup-SQLDatabase -Database master -Server "my_server1", "my_server2" ...

This 2nd point may help you to centralize all those backup scripts to one administration server (dedicated for DBA use) instead of sparsely stored on each sql server instance.

Also with MS providing Backup-SQLDatabase, you can be assured that it will be supported for all future new features. (For example, using this cmdlet to support Azure SQL Database etc.)


Can I restore a SQL Server .bak files without SQL Server? I have some large .bak files from a SQL Server 2005 dump.

Can I restore these without using SQL Server, either to PostgreSQL, MySQL, or to flat text files?

An open source solution would be most useful.

Here is what I suggest:

build a virtual machine running Windows, with enough disk space to hold the backup. Copy the backup file there. If you don't already have the ability to build virtual machines, you could do so with free products like Oracle VirtualBox.
download and install the evaluation edition of SQL Server. Make sure you include both the database engine and Management Tools - Complete.
if the VM has enough space to hold the backup but not enough space to also restore it, you can
perform a "virtual restore" using the trial version of a product
from Red-Gate with the same name (which allows you to interact with the backup file as if it had been restored). Otherwise, restore the database the normal way.
Once the database is available (either through a normal restore or virtual restore), you can generate scripts for the schema and data in the following way:

Open Management Studio and connect to your instance.
Open Object Explorer.
Right-click your newly restored database, choose Tasks > Generate Scripts...
Click Next, Click Next
On the "Choose Script Options" page, scroll down and set "Script Data" to True
Click Next
Check all of the relevant objects and click Next
Check the tables you want and click Next
Choose to script to a file. Now you'll have a file that contains all your objects and data using SQL Server insert syntax, you will have to play with the output to get it in a format that works for Postgres (I am not privy to any minor syntax differences).

Alternatively you can try and play with the bcp utility to extract data to CSV files or similar, but you'll have to do this table-by-table or use some clever scripting (PowerShell, T-SQL, C#/SMO, etc.) to generate all of the bcp commands for you. Once in CSV files, it should be trivial to bulk load the data into Postgres (but you will still have some work to generate the tables).

As a final suggestion, if the .bak file is not ginormous, and the data is not confidential, I am more than willing to try and generate files for you in the format you need. I have plenty of Windows VMs with space, the challenge would be getting the .BAK file to a place where I can retrieve it - especially if it's larger than most of the file-sharing services support.


SQL table constraint limit number of same domain < 5 I have a table "Applies" with one of its attribute "applicantNum".

Is there a way to set a constraint to check the value inside a domain has less than 5 repeated value?

E.g.

insert into applies(applicantNum) values ('s12345');<-- 1st insertion
insert into applies(applicantNum) values ('s12345');<-- 2nd insertion
insert into applies(applicantNum) values ('s12345');<-- 3rd insertion
insert into applies(applicantNum) values ('s12345');<-- 4th insertion
insert into applies(applicantNum) values ('s12345');<-- 5th insertion
insert into applies(applicantNum) values ('s12345'); <-- error

You need to normalize the database, or you'll have to use a trigger. There is a method of doing what you want, it can be done with F671, “Subqueries in CHECK constraints” however Oracle doesn't implement them. For that, you'll have to turn to everyone's favorite database, Microsoft Access.

To normalize create a table like this with a check constraint on the amount of attempts, then increase the amount of attempts. The CHECK constraint will resist incrementing past 5.

CREATE TABLE applicant (
  applicantNum     int        PRIMARY KEY,
  applicantentries smallint   CHECK ( applicantentries < 5 )
);


Inserting filtered data from one table to another I need to find the number of employees who are not in all departments.

I have one more table T2 in which same department column is present. Now I want to insert only those values of department which are not in T1.Department for those employees whose name doesn't appear in all 37 departments.

I'm using MySQL.

Do I need to make any procedure for this?

Reference to : Need to find the number of employee who are not in all department

Tables

create table T1 (empname, department)
create table T2 (department)

You can insert the result of a SELECT using INSERT ... SELECT syntax. To get rows from one table that do not have corresponding rows in another you can use the A not in B LEFT JOIN MySQL idiom or a similar one with NOT EXIST subquery (though the latter is less efficient).

Please give your table details for more specific query.


Took a full backup of a running mariadb server and restored, but none of the databases are visible I'm setting up a mariadb galera cluster to ultimately switch production to. However, I don't have the ability to stop the production instance so I'm trying to set this up using various workarounds I've come across. The current setup relies on a pair of mariadb servers as a master/slave pair. I've added a 3rd mariadb server and I'm restoring a full backup to third, empty server.

To backup the server I executed the following:

mysqldump -u root -p --all-databases --master-data=2 | gzip > backup.sql.gz

once the new server was ready, I restored the database:

zcat backup.sql.gz  | mysql -u root --port=3309 --socket=/var/run/mysqld/mysqld400.sock

When I log into mysql and do a show databases, I don't see anything other than the default db's (information, performance, and mysql). I stopped and restarted mariadb and it still the same. Is there another step I need to point to the data that was just restored?

No matter what approach you take there will be at least a brief downtime.

You have M->S, and want G-G-G, correct?

To get another slave from M->S, stop the slave, clone the disk, change server_id, start them up, let replication catch up.  The master does not go down, but you will have S<-M->S.  But that is still not what you need.

I think (but am not sure) that this is the approach.  (There is not a lot of Galera help here, so I will give you more than nothing.)

Optionally keep read access going to your 1 slave.
Stop the Master.
Bootstrap the Master into the first Galera Node.
Stop the Slave.
Turn the Slave into the second Galera Node.
If you have the other Slave do 4&5 to it.  If not give Galera an empty machine and let it build the 3rd node.

Step 6 could be replaced with setting up a "garbd" node -- This is probably a lot faster since no data is needed.  You could do a Step 7 to build a 'real' node and get rid of the garbd.


How to return a dataset only if an unrelated username and password are correct? I have two unrelated tables.  One is Reports the other is Users.  My current queries are:

Select * from Reports;
Select userid from Users where username = @username and password = @password;

Note: Yes, I do salt and encrypt the password in reality.

I want to restate the fact that these tables are not related - that is to say there is no user ID, username, etc. in the Reports table.

My question is: Is it possible to combine these so that the first query will only return a result if the username and password had a match?

Is it possible to combine these so that the first query will only return a result if the username and password had a match?

Yes, it's pretty simple; an EXISTS subquery (a semi-join):

-- query 1
select *
from Reports
where exists
      ( select * from Users where username = @username and password = @password ) ;

  Would it be better as a cross join?

Not really. I suppose you mean something like that - with either from Reports cross join Users or from Reports, Users:

-- query 2
select r.*
from Reports as r cross join Users as u
where u.username = @username
  and u.password = @password ;

While this would give you the same results, it would only if there is a UNIQUE constraint on Users (username). If there is no such constraint, you may have (however unlikely it may sound) 2 rows with same username and password and in that case get all results duplicated. The EXISTS subquery wouldn't have such a problem.
But my main preference for the exists approach is that it is much cleaner about its intentions (a semijoin, not a cross join). The FROM clause has the table that you intend to have in the result set. The cross join has both tables in the FROM clause and if the developer is not careful enough (i.e. to use SELECT r.* and not SELECT *, the user details will leak in the result. Only if you do want to include the user details in the result set, then yes, the cross join should be preferred.

I'd use aliases in all required places anyway and write the query like this. Note that queries 1 and 3 are identical, except for the use of aliases and formatting:

-- query 3
select r.*
from Reports as r
where exists
      ( select *
        from Users as u
        where u.username = @username
          and u.password = @password
      ) ;


PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same SQL query. A query plan shows that an index (a BTree one, we do not use hash indexes at all) is used to get the result so it looks like an index is in inconsistent or incomplete state on the replica server.
The query on the master server:

db1=# select id from users where email='xxxx@xxxx.net';
   id
---------
 1698116
(1 row)

db1=#

The query on the replica server:

db1=> select id from users where email='xxxx@xxxx.net';
 id
----
(0 rows)

db1=> select created_at from users where id=1698116;
         created_at
----------------------------
 2013-03-04 10:40:05.221214
(1 row)

db1=>

As you can see the replica DB already contains a user with proper ID so the data is in place but just not indexed yet for some reason. We double checked the replica was in receiving/reapplying state so this was not a temporary outage. The user never got indexed. We also used to experience similar problems with PostgreSQL 9.0 on CentOS 5.6 so we don't think this is something FreeBSD- or PostgreSQL 9.1-specific.

We use the replica server to run lots of heavy SQL queries, can this be a root of the problem? Anyway how can we efficiently detect and prevent situations like this in the future? The replica was not down today and there was no single error line in logs so we detected this inconsistency only by occasion.

Assuming the locale of the database is en_GB.UTF-8 both in Ubuntu (master) and FreeBSD (slave), I believe the differences in sort semantics alone may account for the fact that the index is unusable on the slave.

Here's an example of how they sort differently:

On Ubuntu 12.04:

$ export LANG=en_GB.UTF-8
$ cat >file
"0102"
0102
$ sort file
0102
"0102"

On FreeBSD 9.0-RELEASE:

$ export LANG=en_GB.UTF-8
$ cat >file
"0102"
0102
$ sort file
"0102"
0102

This shows that the same locale orders differently the two strings "0102" and 0102 (even though they do not even contain any character outside the US-ASCII set...)

Here's a test that I suggest you try on your own dataset:

On the master:

$ psql -d dbname -Atc 'select email from users' | LC_COLLATE=en_GB.UTF-8 >email.master

On the slave:

$ psql -d dbname -Atc 'select email from users' | LC_COLLATE=en_GB.UTF-8 >email.slave

Now compare email.master and email.slave with diff or cmp. I suspect you'll find that they are not identical. In which case it demonstrates that the index replica can't be used, since its build rules on the master differ from the scanning rules on the slave.


Why isn't Unicode character REPLACEd in some cases? Running this results in "test", as expected:

SELECT
    REPLACE(NCHAR(1234), NCHAR(1234), N'test');

However, running this results in "aӒa", which doesn't have "test" in it:

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a', NCHAR(1234), N'test');

I thought it might have to do with the string concatenation happening for the haystack but not the needle, but when I tried this, it still didn't "work":

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a', N'' + NCHAR(1234) + N'', N'test');

Result: "aӒa"

I suspected it might have to do with how it's interpreting the characters, so I tried specifying a binary collation... and that "fixed" the issue:

SELECT
    REPLACE(N'a' + NCHAR(1234) + N'a' COLLATE Latin1_General_100_BIN2, NCHAR(1234), N'test');

Result: "atesta".

Why?

This behavior seems to exist for some characters, but not others.

SELECT
    REPLACE(N'a' + NCHAR(23423) + N'a', NCHAR(23423), N'test');

Result: "atesta" ("works")

SELECT
    REPLACE(N'a' + NCHAR(5342) + N'a', NCHAR(5342), N'test');

Result: "aᓞa" (doesn't "work")

Why?

This behavior is simply due to the fact that you are executing the query in a Database that has a pre-"100" series default Collation, in which case a great many characters do not have any sort weight. No sort weight means that they equate to empty string. They have a value of 0. So they always equal each other when nothing else having a sort weight is involved. They also equate to empty string. The version 100 Collations (starting in SQL Server 2008 which is version 10.0 or 100 without the decimal / minor version) added sort weights for most characters. So, just force the collation by adding COLLATE Latin1_General_100_CI_AS_SC. A binary Collation of any version (one ending in _BIN2, don't use Collations ending in just _BIN) also works as there are no such things as sort weights in binary Collations.

SELECT 1 WHERE NCHAR(1234) = '' COLLATE SQL_Latin1_General_CP1_CI_AS;
-- 1

SELECT 2 WHERE NCHAR(1234) = '' COLLATE Latin1_General_CI_AS;
-- 2

SELECT 3 WHERE NCHAR(1234) = '' COLLATE Latin1_General_100_CI_AS;
-- (no results)

SELECT 4 WHERE NCHAR(1234) = '' COLLATE Latin1_General_BIN2;
-- (no results)

Even multiple characters with no weight still equate to empty string (or any number of characters with no sort weight):

SELECT 5 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
       = N'' COLLATE Latin1_General_CS_AS_KS_WS;
-- 5

In this next test, NCHAR(1234) equates to itself only when using a version 100 (or newer) Collation. Pre-version 100 Collations assign no sort weight to NCHAR(1234) such that it doesn't equate to anything, hence it was previously not found in the first expression.

SELECT REPLACE(N'a' + NCHAR(1234) + N'a',
               NCHAR(1234) COLLATE Latin1_General_100_CI_AS,
               N'test');
-- atesta

Continuing example # 5 above, we can prove that 3 characters having no weight equate to 2 different characters that also have no weight:

SELECT 6 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
             = NCHAR(5342) + NCHAR(5342) COLLATE Latin1_General_CS_AS_KS_WS;
-- 6

But switching to a version 100 Collation changes that:

SELECT 7 WHERE NCHAR(1234) + NCHAR(1234) + NCHAR(1234)
             = NCHAR(5342) + NCHAR(5342) COLLATE Latin1_General_100_CS_AS_KS_WS;
-- (no results)

SELECT 8 WHERE NCHAR(1234) = NCHAR(5342) COLLATE Latin1_General_100_CS_AS_KS_WS;
-- (no results)

Please see the "Supplementary Characters" characters section of the following post of mine. In that section I show similar behavior as it relates to supplementary characters, and at the end of the section I show how many BMP characters (i.e. non-supplementary characters) match one of these "missing weights" characters per Collation version.

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Identifiers, Part 3 of 2 (Delimited Identifiers)

For more info on working with Collations, please visit: Collations.info


Verify all Stored Procedures How can I verify all stored procedures in a SQL Server database? I need to verify if all stored procedures still work, after some tables/views etc. have been deleted or changed.

No, there is no built-in way to do this within SQL Server. As Marian stated, you could recompile all of your procedures to be sure they're still valid, but this doesn't prove they'll still work (and don't forget that deferred name resolution makes this validation less than stellar anyway). However you can use other tools such as SQL Server Data Tools (SSDT) to help facilitate unit testing.

Just like changes to your application, if you change your schema, you need to test those changes. How automated you make your unit testing depends on the complexity of your schema, the consuming application(s), and the number of permutations possible for stored procedure outcomes (including both explicit input of different parameters or different parameter values, and implicit input such as time of day, state of the system, specific data at rest, etc.). You have to keep in mind that in some cases you will expect the stored procedure output to stay the same, but in others you will actually expect it to be different.

Some potentially helpful links (a couple culled from a question on SO geared to testing a single stored procedure):

TSQLUnit for unit tests
T.S.T. the T-SQL Test Tool


Shared Lock issued on IsolationLevel.ReadUncommitted I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock:

  Resource_Type: HOBT
  Request_Mode: S (Shared)

What is a HOBT lock? Something related to HBT (Heap or Binary Tree lock)?

Why would I still get a S lock?

How do I avoid shared locking when querying without turning on the isolation level snapshot option?

I am testing this on SQLServer 2008, and the snapshot option is set to off. The query only performs a select.

I can see that Sch-S is required, although SQL Server seems not to be showing it in my lock query. How come it still issues a Shared Lock?  According to:

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

  Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction.

So I am a little confused.

What is HOBT lock?

A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.

  Why would I still get a S lock?

This happens on heaps. Example

SET NOCOUNT ON;

DECLARE @Query nvarchar(max) =
   N'DECLARE @C INT;
     SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';

/*Run once so compilation out of the way*/
EXEC(@Query);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;

PRINT 'READ UNCOMMITTED';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC(@Query);

PRINT 'READ COMMITTED';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC(@Query);

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

Output READ UNCOMMITTED

Process 56 acquiring Sch-S lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on HOBT: 1:72057594038910976 [BULK_OPERATION] (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 1:1163151189:0

Output READ COMMITTED

Process 56 acquiring IS lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring IS lock on PAGE: 1:1:169 (class bit0 ref1) result: OK

Process 56 releasing lock on PAGE: 1:1:169

Process 56 releasing lock on OBJECT: 1:1163151189:0

According to this article referencing Paul Randal the reason for taking this BULK_OPERATION shared HOBT lock is to prevent reading of unformatted pages.


How to get the spatial index in CREATE TO script? Is it possible to get the spatial index using the create to script using SQL Server 2012?

When I write out a table using SCRIPT TABLE AS > CREATE TO in Microsoft SQL Server Management Studio 2012, I get the primary key index, but the spatial index does not carry over.

Thank you!

The spatial index, as with other objects, have their own script <object> as menus and need to be done separately.  This can be done to the clipboard and pasted to the query window generated by the script table as command.

I can't confirm at the moment, but I suspect that the primary key and other constraints come out in the script table as options since the are an integral part of the table definition, whereas indexes are not.

The generate and publish scripts wizard can also be used, but it is a lot of steps if you are just doing a few items.
Technet, How to: Generate a Script


Using JOIN statement with a WHERE This is probably a basic question, but I'm trying to build an SQL query for an access database that returns orders from a certain customer and I want to cross reference the customerID with the customer name from the customers table.

This is the query that I'm using

SELECT OrderTable.OrderNumber, OrderTable.CustomerID, OrderTable.OrderDate
FROM [OrderTable] INNER JOIN [CustomerTable]
    ON CustomerTable.CustomerID = OrderTable.CustomerID
WHERE OrderTable.CustomerID = 1

I don't get an error and it returns the correct records (all orders w/ customer ID = 1), however it does not return the customer name...it just returns the customerID for each order (always 1 as expected).

What am I doing incorrectly?

Thank you!

You are specifically asking for the OrderNumber, CustomerId and OrderDate columns.  If you want the customers name you have to include that in your field list.

SELECT OrderTable.OrderNumber, OrderTable.CustomerID, OrderTable.OrderDate,
     CustomerTable.Name
FROM [OrderTable] INNER JOIN [CustomerTable]
    ON CustomerTable.CustomerID = OrderTable.CustomerID
WHERE OrderTable.CustomerID = 1

A basic SELECT statement looks something like this:

SELECT FieldList
FROM TableOne
INNER JOIN TableTwo
    ON JoinCondition between TableOne and TableTwo
WHERE [Conditions that restrict returned rows]

You will only be returned those columns that you explicitly list in the FieldList unless you use a * (returns all rows) or TableName.* (returns all rows for TableName).

Note: It is recommended that you list out the fields you want back explicitly for a number of reasons including:

Performance : Pulling one (or more) columns that you actually need requires less data transferred than pulling all of them.
Code stability : If you add a column to the table you will get back columns in your query you may not want and could break your code.


Quering date from date and time field I have a table which is like this.

CREATE TABLE `candidate` (
  `candidate_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL DEFAULT '0',
  `last_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `middle_name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_home` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_cell` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_work` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8_unicode_ci,
  `city` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `source` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_available` datetime DEFAULT NULL,
  `can_relocate` int(1) NOT NULL DEFAULT '0',
  `notes` text COLLATE utf8_unicode_ci,
  `key_skills` text COLLATE utf8_unicode_ci,
  `current_employer` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `entered_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Created-by user.',
  `owner` int(11) DEFAULT NULL,
  `date_created` varchar(250) COLLATE utf8_unicode_ci DEFAULT '',
  `date_modified` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email1` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email2` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `web_site` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `import_id` int(11) NOT NULL DEFAULT '0',
  `is_hot` int(1) NOT NULL DEFAULT '0',
  `eeo_ethnic_type_id` int(11) DEFAULT '0',
  `eeo_veteran_type_id` int(11) DEFAULT '0',
  `eeo_disability_status` varchar(5) COLLATE utf8_unicode_ci DEFAULT '',
  `eeo_gender` varchar(5) COLLATE utf8_unicode_ci DEFAULT '',
  `desired_pay` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `current_pay` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  `is_admin_hidden` int(1) DEFAULT '0',
  `best_time_to_call` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And some of the data is like this.

INSERT INTO `candidate` (`candidate_id`, `site_id`, `last_name`, `first_name`, `middle_name`, `phone_home`, `phone_cell`, `phone_work`, `address`, `city`, `state`, `zip`, `source`, `date_available`, `can_relocate`, `notes`, `key_skills`, `current_employer`, `entered_by`, `owner`, `date_created`, `date_modified`, `email1`, `email2`, `web_site`, `import_id`, `is_hot`, `eeo_ethnic_type_id`, `eeo_veteran_type_id`, `eeo_disability_status`, `eeo_gender`, `desired_pay`, `current_pay`, `is_active`, `is_admin_hidden`, `best_time_to_call`) VALUES
(1, 1, 'Faiyaj', 'Mohammad', '', '0959927638,0888227677,0520110007', '', '', '', 'Bagh-110025,BR-0520110007368', '', 'undefined', '', NULL, 0, 'This resume was parsed automatically. You should review it for errors.', '', '', 1, 1, '2018-07-17T16:50:04+05:30', '2018-07-17T16:50:04+05:30', 'faiz_akimam@yahoo.com', '', '', 0, 0, 0, 0, '', '', '', '', 1, 0, ''),

The query which i am running is this

SELECT candidate_id,date_created, first_name,phone_cell, email1, notes, city, current_pay, desired_pay, key_skills, source, current_employer
FROM candidate
WHERE entered_by = '1' AND date_created = '2018-08-23'

The problem is this this query is not returning anything. the date_created is in date time format.

From DDL:

`date_created` varchar(250) COLLATE utf8_unicode_ci DEFAULT '',

First question - why? Why not DATETIME?

From query:

AND date_created = '2018-08-23'

Second question - are you sure there are records containing '2018-08-23' value in this field DIRECTLY? without timepart?

Recommendations:

Alter datatype of the field to DATETIME and use proper condition for this datatype.

AND date_created BETWEEN '2018-08-23 00:00:00' AND  '2018-08-23 23:59:59'

If not, replace the condition with

AND date_created LIKE '2018-08-23%'


What is the best way to get a random ordering? I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How can I ensure a random row order?

I understand that it will likely not be "truly" random, pseudo-random is good enough for my needs.

ORDER BY NEWID() will sort the records randomly. An example here

SELECT *
FROM Northwind..Orders
ORDER BY NEWID()


Designing a database for a video game business domain with multiple many-to-many relationships I am relatively new to database design, and I decided to make my own hypothetical database for practice. However, I am having trouble modeling and normalizing it, as I esteem that there are numerous many-to-many (M:N) relationships.
General scenario description
The database is meant to retain data about various People that have worked on the Zelda series. I want to keep track of the Console(s) that a Game can be played on, Employees that have had a part in the Games development, the Jobs the Employee had (many Employees worked on different Jobs across multiple Games), etc.
Business rules

Multiple Employees can work on multiple Games.
Multiple Games can be on the same Console.
Multiple Consoles can be a platform for the same Game.
Multiple Employees can have the same Job.
An Employee can have multiple Jobs.
A Game can have multiple Employees.
A Game can have multiple types of Jobs in it's development
Multiple Games can have the same type of Job attached.
A Console can have multiple People working on it.
A Person can work on multiple Consoles.

Attribute names and sample values

Employee Name, which can be split into First and Last (for example “John” and “Doe” )
Game Title (for example “Ocarina of Time”)
Job Title (for example “Level Design”, “Director”, “Composure”, “Level Designer”, “Programmer”, “Localization”, etc.).
Console Name (for example “Game Boy Advance”)

The issue
So far, it seems no matter what I design there are data redundancies and M:N relationships between the entity types of interest everywhere. However I feel that database designers must run into this kind of problem all the time, so there must be a solution.

Note: I am well able to find the data to fill the table, the problem is organizing it into a database with tables in a normalized form.

Yes, the identification of many-to-many (M:N for brevity) associations or relationships is a situation that a database practitioner faces quite commonly when laying out a conceptual schema. Associations of said cardinality ratios come about in business environments of very different nature, and when properly represented at the logical level by means of, e.g., a SQL-DDL arrangement, they do not introduce harmful redundancies.
In this way, the objective of a database modeling exercise should be to mirror the relevant characteristics of the business context of interest with high precision; therefore, if you identify correctly that there are numerous M:N associations, then you must express them in (a) the conceptual schema and also in (b) the respective logical-level declarations, no matter how many connections of that —or any other— kind of cardinality ratios have to be addressed.
Business rules
You have supplied a well-contextualized question, and have also clarified that the database you are working on is purely hypothetical, which is an important point since I esteem that a “real world” business scenario like the one under consideration would be much more extensive and, hence, would imply more complex informational requirements.
I decided to (1) make a few modifications and expansions to the business rules you have provided in order to (2) produce a more descriptive conceptual schema —although still rather hypothetical—. Here are some of the formulations that I put together:

A Party1 is either a Person or an Organization
A Party is classified by exactly-one PartyType
A PartyType classifies zero-one-or-many Parties
An Organization develops zero-one-or-many Products
A Product is either a System or a Game
A Product is classified by exactly-one ProductType
A System is catalogued by exactly-one SystemType
A Game can be played via one-to-many Systems
A System is used to play one-to-many Games
A Game is classified by zero-one-or-many Genres
A Genre classifies zero-one-or-many Games
A Product originates one-to-many Jobs
A Job is fulfilled by zero-one-or-many People, who are playing the Role of Collaborators
A Person is a Collaborator in zero-one-or-many Jobs

1 Party is a term used in legal contexts when referring to either an individual or a group of individuals that compose a single entity, so this denomination is suitable to represent People and Organizations.

IDEF1X diagram
Subsequently, I created the IDEF1X2 diagram shown in Figure 1 (make sure to click the link to see it in a higher resolution), consolidating in a single graphical device the business rules presented above (along with some others that seem relevant):

2 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is based on (a) the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view of data, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

As you can see, I have depicted only three M:N associations by way of the corresponding associative entity types, i.e.:

Collaborator
SystemGame
GameGenre

Among other aspects, there are two distinct supertype-subtype structures, where:

Person and Organization are mutually exclusive entity subtypes of Party, their entity supertype

Product is the supertype of System and Game, which in turn are mutually exclusive subtypes

In case you are not familiar with supertype-subtype associations, you might find of help, e.g., my answers to the questions entitled:

“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”
“How to model an entity type that can have different sets of attributes?”
“Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money”
“Modelling a database structure for multiple user types and their contact information”

Illustrative logical SQL-DDL layout
Successively, we must make sure that, at the logical level:

Each entity type is represented by an individual base table
Each single property of the applicable entity type is denoted by a particular column
An exact data type is fixed for each column in order to ensure that all the values it contains belong to a particular and well defined set, be it INT, DATETIME, CHAR, etc. (of course, when using, e.g., Firebird or PostgreSQL, you might like to employ the more powerful DOMAINs)
Multiple constraints are configured (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined at the conceptual level

So I declared the following DDL arrangement based on the IDEF1X diagram previously shown:
CREATE TABLE PartyType ( -- Stands for an independent entity type.
    PartyTypeCode CHAR(1)  NOT NULL, -- To retain 'P' or 'O'.
    Name          CHAR(30) NOT NULL, -- To keep 'Person' or 'Organization'.
    --
    CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);

CREATE TABLE Party ( -- Represents an entity supertype.
    PartyId         INT       NOT NULL,
    PartyTypeCode   CHAR(1)   NOT NULL, -- To hold the value that indicates the type of the row denoting the complementary subtype occurrence: either 'P' for 'Person' or 'O' for 'Organization'.
    CreatedDateTime TIMESTAMP NOT NULL,
    --
    CONSTRAINT Party_PK            PRIMARY KEY (PartyId),
    CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
        REFERENCES PartyType (PartyTypeCode)
);

CREATE TABLE Person ( -- Denotes an entity subtype.
    PersonId        INT      NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    BirthDate       DATE     NOT NULL,
    --
    CONSTRAINT Person_PK PRIMARY KEY        (PersonId),
    CONSTRAINT Person_AK UNIQUE             (FirstName, LastName, GenderCode, BirthDate), -- Composite ALTERNATE KEY.
    CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
        REFERENCES Party (PartyId)
);

CREATE TABLE Organization ( -- Stands for an entity subtype.
    OrganizationId  INT      NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Name            CHAR(30) NOT NULL,
    FoundingDate    DATE     NOT NULL,
    --
    CONSTRAINT Organization_PK        PRIMARY KEY (OrganizationId),
    CONSTRAINT Organization_AK        UNIQUE      (Name), -- Single-column ALTERNATE KEY.
    CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
        REFERENCES Party (PartyId)
);

CREATE TABLE ProductType ( -- Represents an independent entity type.
    ProductTypeCode CHAR(1)  NOT NULL, -- To enclose the values 'S' and 'G' in the corresponding rows.
    Name            CHAR(30) NOT NULL, -- To comprise the values 'System' and 'Person' in the respective rows.
    --
    CONSTRAINT ProductType_PK PRIMARY KEY (ProductTypeCode)
);

CREATE TABLE Product ( -- Denotes an entity supertype.
    OrganizationId  INT      NOT NULL,
    ProductNumber   INT      NOT NULL,
    ProductTypeCode CHAR(1)  NOT NULL, -- To keep the value that indicates the type of the row denoting the complementary subtype occurrence: either 'S' for 'System' or 'G' for 'Game'.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Product_PK               PRIMARY KEY (OrganizationId, ProductNumber), -- Composite PRIMARY KEY.
    CONSTRAINT ProductToOrganization_FK FOREIGN KEY (OrganizationId)
        REFERENCES Organization (OrganizationId),
    CONSTRAINT ProductToProductType_FK  FOREIGN KEY (ProductTypeCode)
        REFERENCES ProductType (ProductTypeCode)
);

CREATE TABLE SystemType ( -- Stands for an independent entity type.
    SystemTypeCode CHAR(1)  NOT NULL,
    Name           CHAR(30) NOT NULL,
     --
    CONSTRAINT SystemType_PK PRIMARY KEY (SystemTypeCode)
);

CREATE TABLE MySystem ( -- Represents a dependent entity type.
    OrganizationId   INT      NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    SystemNumber     INT      NOT NULL,
    SystemTypeCode   CHAR(1)  NOT NULL,
    ParticularColumn CHAR(30) NOT NULL,
    --
    CONSTRAINT System_PK              PRIMARY KEY (OrganizationId, SystemNumber),
    CONSTRAINT SystemToProduct_FK     FOREIGN KEY (OrganizationId, SystemNumber)
        REFERENCES Product (OrganizationId, ProductNumber),
    CONSTRAINT SystemToSystemType_FK  FOREIGN KEY (SystemTypeCode)
        REFERENCES SystemType (SystemTypeCode)
);

CREATE TABLE Game ( -- Denotes an entity subtype.
    OrganizationId INT      NOT NULL, -- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    GameNumber     INT      NOT NULL,
    SpecificColumn CHAR(30) NOT NULL,
    --
    CONSTRAINT Game_PK          PRIMARY KEY (OrganizationId, GameNumber),
    CONSTRAINT GameToProduct_FK FOREIGN KEY (OrganizationId, GameNumber)
         REFERENCES Product (OrganizationId, ProductNumber)
);

CREATE TABLE Genre ( -- Stands for an independent entity type.
    GenreNumber INT      NOT NULL,
    Name        CHAR(30) NOT NULL,
    Description CHAR(90) NOT NULL,
    --
    CONSTRAINT Genre_PK  PRIMARY KEY (GenreNumber),
    CONSTRAINT Genre_AK1 UNIQUE      (Name),
    CONSTRAINT Genre_AK2 UNIQUE      (Description)
);

CREATE TABLE SystemGame ( -- Represents an associative entity type or M:N association.
    SystemOrganizationId INT      NOT NULL,
    SystemNumber         INT      NOT NULL,
    GameOrganizationId   INT      NOT NULL,
    GameNumber           INT      NOT NULL,
    CreatedDateTime      DATETIME NOT NULL,
    --
    CONSTRAINT SystemGame_PK         PRIMARY KEY (SystemOrganizationId, SystemNumber, GameOrganizationId, GameNumber), -- Composite PRIMARY KEY.
    CONSTRAINT SystemGameToSystem_FK FOREIGN KEY (SystemOrganizationId, SystemNumber) -- Multi-column FOREIGN KEY.
        REFERENCES MySystem (OrganizationId, SystemNumber),
    CONSTRAINT SystemGameToGame_FK   FOREIGN KEY (SystemOrganizationId, GameNumber) -- Multi-column FOREIGN KEY.
        REFERENCES Game (OrganizationId, GameNumber)
);

CREATE TABLE GameGenre ( -- Denotes an associative entity type or M:N association.
    GameOrganizationId INT      NOT NULL,
    GameNumber         INT      NOT NULL,
    GenreNumber        INT      NOT NULL,
    CreatedDateTime    DATETIME NOT NULL,
    --
    CONSTRAINT GameGenre_PK        PRIMARY KEY (GameOrganizationId, GameNumber, GenreNumber), -- Composite PRIMARY KEY.
    CONSTRAINT GameGenreToGame_FK  FOREIGN KEY (GameOrganizationId, GameNumber)
        REFERENCES Game (OrganizationId, GameNumber), -- Multi-column FOREIGN KEY.
    CONSTRAINT GameGenreToGenre_FK FOREIGN KEY (GenreNumber)
        REFERENCES Genre (GenreNumber)
);

CREATE TABLE Job ( -- Stands for an associative entity type or M:N association.
    OrganizationId  INT      NOT NULL,
    ProductNumber   INT      NOT NULL,
    JobNumber       INT      NOT NULL,
    Title           CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Job_PK          PRIMARY KEY (OrganizationId, ProductNumber, JobNumber), -- Composite PRIMARY KEY.
    CONSTRAINT Job_AK          UNIQUE      (Title), -- Single-column ALTERNATE KEY.
    CONSTRAINT JobToProduct_FK FOREIGN KEY (OrganizationId, ProductNumber) -- Multi-column FOREIGN KEY.
        REFERENCES Product (OrganizationId, ProductNumber)
);

CREATE TABLE Collaborator ( -- Represents an associative entity type or M:N association.
    CollaboratorId   INT      NOT NULL,
    OrganizationId   INT      NOT NULL,
    ProductNumber    INT      NOT NULL,
    JobNumber        INT      NOT NULL,
    AssignedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Collaborator_PK         PRIMARY KEY (CollaboratorId, OrganizationId, ProductNumber, JobNumber), -- Composite PRIMARY KEY.
    CONSTRAINT CollaboratorToPerson_FK FOREIGN KEY (CollaboratorId)
    REFERENCES Person (PersonId),
    CONSTRAINT CollaboratorToJob_FK    FOREIGN KEY (OrganizationId, ProductNumber, JobNumber) -- Multi-column FOREIGN KEY.
       REFERENCES Job (OrganizationId, ProductNumber, JobNumber)
);

It is opportune to stress that there are declarations of composite PRIMARY KEY constraints across several tables, which stand for the hierarchy of connections that take place among conceptual entity types, arrangement that can be very beneficial with respect to data retrieval when, e.g., expressing SELECT operations that include JOIN clauses to obtain derived tables.
Yes, (i) every M:N association and (ii) every one of the associated entity types are denoted by (iii) the corresponding table in the logical DDL structure, so pay special attention to the PRIMARY and FOREIGN KEY constraints (and the notes I left as comments) of tables representing these conceptual elements, because they assist in ensuring that the connections between the relevant rows meet the applicable cardinality ratios.
The usage of composite keys was introduced by Dr. E. F. Codd from the very origin of the relational paradigm, as demonstrated in the examples he included in his 1970 seminal paper entitled A Relational Model for Large Shared Data Banks (which, precisely, also presents the most elegant method to handle conceptual M:N associations).
I put up  a db<>fiddle and a SQL Fiddle, both running on Microsoft SQL Server 2014, so that the structure can be tested “in action”.
Normalization
Normalization is a logical-level procedure that implies, basically speaking:

Eliminating non-atomic columns via first normal form so that data manipulation and constriction are much easier to cope with by the data sublanguage of use (e.g., SQL).

Getting rid of undesirable dependencies among the columns of a specific table by virtue of the successive normal forms to avoid update anomalies.

Naturally, one has to take into account the meaning carried by the table(s) and column(s) at issue.
I like to think of normalization as a test founded on science that a designer applies to the pertinent elements once he or she has delineated a stable logical-level arrangement in order to determine whether its items comply with every one of the normal forms or not. Then, if needed, the designer takes the appropriate correcting measures.
Redundancy
In the relational model, while duplication of values contained in columns is not only acceptable but expected, duplicate rows are forbidden. To that extent, as far as I can see, duplicate rows and other kinds of harmful redundancies are prevented in all the tables comprised in the logical layout exposed before, perhaps you would like to clarify your concern in this regard.
Anyway, you can certainly (a) evaluate on your own said structure by dint of the normal forms to define if it meets the requirements and (b) modify it if necessary.
Related resources

In this series of posts I present some deliberations about a straightforward M:N association that can interrelate the instances of two different entity types.
In this other one I propose an approach to handle an occurrence of the “Bill of Materials” or “Parts Explosion” construct, in which I describe how to connect distinct instances of the same type of entity.

Ternary associations
There is another important aspect that you brought up via comments (posted in an now-deleted answer):

Every time I try to make a bridge the elements in that bridge also have a Many to Many, I'm under the impression that isn't allowed or at least discouraged.

That circumstance seems to indicate that one of your concerns has to do with conceptual ternary associations. Basically speaking, this sort of associations comes about when there exists (1) a relationship involving (2) two other relationships, in other words “a relationship between relationships” —a typical situation too, since a relationship is an entity in its own right—.
These arrangements, when suitably managed, do not bring harmful redundancies either. And, yes, if there is a certain use case where you identify that such relationships present themselves among “real world” entity types, you have to (i) model and (ii) declare them with accuracy at the logical level.

Here is a question and answer were we analyze a domain of discourse about surveys, which includes an example of a ternary association.
In this very good answer, @Ypercube presents a diagram and the respective DDL structure for an interesting diamond shaped relationship, which is very similar to this class of scenarios.


How do I upload an .mdf file to Azure Data Studio for Mac? How could I access multiple databases at once using Azure Data Studio and import an existing .mdf file as one of those database, with tables and data that had been stored in that .mdf file?

Azure Data Studio is like SQL Server Management Studio: these are client programs that access SQL Server, but ADS and SSMS don't touch the MDF files directly.

What you need is SQL Server to open the MDF. Try downloading SQL Server Developer Edition - it's free. Once it's installed, follow the instructions on how to attach a database. Attaching a database is kinda like opening a .doc file in Word - you need Word installed first.


postgresql update with a subquery limit 1 that has joins sometimes doesn't respect the limit? As per Postgres UPDATE ... LIMIT 1 I'm trying to update a single record but based on a join.  However, ~30% of the time on the pre-installed postgresql 9.5.5 from travis-ci the following SQL actually returns multiple records.  Is there something I'm misunderstanding about this query like it's running one instance of the subquery for each tuple the update sees or is this a potential bug in postgresql?

UPDATE "users" SET touched_at = now()
FROM (
   SELECT "some_table".*
   FROM "some_table"
     INNER JOIN "users" ON "users"."id" = "some_table"."user_id"
   WHERE "some_table"."handled_at" IS NULL
     AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21')
  ORDER BY "some_table"."created_at" ASC
  LIMIT 1
  FOR UPDATE OF "users"
) dt
WHERE "users".id = dt.user_id
AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21')
RETURNING dt.*

In March Erwin Brandstetter revised his answer to mention "The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT": https://dba.stackexchange.com/posts/69497/revisions

There appear to be two options that actually work for limiting the number of rows updated:

an uncorrelated scalar subquery when you only need a single row, e.g. UPDATE users SET touched_at = now()  WHERE users.id = (SELECT id FROM users LIMIT 1)

OR

if you need multiple rows then a common table expression (CTE) which is always materialized once and is a fence to the optimizer in postgresql (https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/), e.g. WITH cte AS (SELECT id FROM users LIMIT 10) UPDATE users SET touched_at = now()  WHERE users.id = cte.id