texts: What is the best way to check for matching sets of rows?

texts:
I was watching this video yesterday on how unlikely it is to shuffle a deck of cards the same way twice:

https://youtu.be/czSdEhtXwm4?t=300

And I thought to myself "My SAN adminsitrator still has some hair left."

So I built a quick deck of cards:

        declare @card int
        create table deck (card int)

        set @card = 1
        while @card <= 52
        begin
        insert into deck (card)
        values (@card)
        set @card += 1
        end

Shuffled them up:

        declare @shuffleID bigint
        create table shuffles (shuffleID bigint, position int, card int)

        select @shuffleID = isnull(max(shuffleid),0)+ 1 from shuffles

        while @shuffleID <= 10000

        begin

        insert into shuffles (shuffleID, position, card)
        select @shuffleID, rank() over (order by newid()), card from deck
        order by newid()

        set @shuffleID += 1

        end

Then saw how closely I could match them:

        select s1.shuffleID, s2.shuffleID, count(1) as match_count from shuffles s1
        inner join shuffles s2 on s1.position = s2.position and s1.card = s2.card
            and s1.shuffleID != s2.shuffleID
        group by  s1.shuffleID, s2.shuffleID
        order by count(1) desc

The whole script looks like this:

        if object_id('dbo.deck') is not null begin drop table deck end
        if object_id('dbo.shuffles') is not null begin drop table shuffles end

        create table deck (card int)
        create table shuffles (shuffleID bigint, position int, card int)
        CREATE NONCLUSTERED INDEX [huehuehue]
        ON [dbo].[shuffles] ([position],[card])
        INCLUDE ([shuffleID])

        declare @card int,
        @shuffleID bigint,
        @sql int
        set @card = 1
        while @card <= 52
        begin
        insert into deck (card)
        values (@card)
        set @card += 1
        end

        select @shuffleID = isnull(max(shuffleid),0)+ 1 from shuffles

        while @shuffleID <= 1000

        begin

        insert into shuffles (shuffleID, position, card)
        select @shuffleID, rank() over (order by newid()), card from deck
        order by newid()

        set @shuffleID += 1

        end

        select s1.shuffleID, s2.shuffleID, count(1) as match_count from shuffles s1
        inner join shuffles s2 on s1.position = s2.position and s1.card = s2.card
            and s1.shuffleID != s2.shuffleID
        group by  s1.shuffleID, s2.shuffleID
        order by count(1) desc

The highest match I've got out of 1,000 shuffles is 8.

Question: What would be the fastest way to shuffle a deck of cards the same way twice?


texts: Visualization of MySQL/PostgreSQL table structure as a graph/UML

texts:
Is there any program, source code or API that will take a MySQL or PostgreSQL schema and
spit out a UML or some other graph visualizing of it?


texts: Get all descendants from 3 hierarchical tables

texts:
I have the following tables within a database:

+-------------+   +-------------+-------------+   +---------------------------+
|  project1   |   |          project2         +   |         project3          |
+-------------+   +-------------+-------------+   +---------------------------+
| project1_id |   | project2_id | project1_id |   | project3_id | project2_id |
+-------------+   +-------------+-------------+   +---------------------------+
|    hnc1     |   |    hnc4     |     hnc1    |   |     hnc7    |    hnc4     |
|    hnc2     |   |    hnc5     |     hnc1    |   |     hnc8    |    hnc4     |
|    hnc3     |   |    hnc6     |     hnc3    |   |     hnc9    |    hnc6     |
+-------------+   +-------------+-------------+   +---------------------------+

I need to retrieve all descendants of a given ID from all three tables - including the ID itself if it is found in any table.

For example, if I query using the ID 'hnc4' it should get:

+-------------+
| descendants |
+-------------+
|    hnc7     |
|    hnc8     |
|    hnc4     |
+-------------+

If I query using the ID 'hnc1':

+-------------+
| descendants |
+-------------+
|    hnc4     |
|    hnc5     |
|    hnc7     |
|    hnc8     |
|    hnc1     |
+-------------+

The query must work even if the ID is only in the last table project3.

I was searching and found that a recursive CTE could be useful but I don't know how to make it with 3 tables involved.

Any guidance on how to get with the right results?


texts:
I am attempting to design a simple reporting feature for some financial market data.

I have a few sets of data, and I am trying to figure out what will be the best way to organize the tables, in particular for reporting.

I am using Microsoft SQL Server Express, which I am comfortable with for simple tasks and I've designed a few reports.

I have one table with Trade data, which is basically a log, with Trade #, Entry Date, Entry Price, Exit Date, Profit and some other information. Simple and easy.

I want to link each trade entry with some data specific to that entry. For example, in my Trade Table, I might have 200 entries. Each entry, however has pricing data from before and after the trade date that I would like to get.

I want to produce a report that lists the trade data, adjusted for parameters which I can do.  The report could display a graph to show trade specific data that is more detailed than can just be fit in columns from the current record.

Any ideas or references to check out would be greatly appreciated.


texts:
I have huge postgres table (10GB of data - 160M records). Table is static and there are no write operations on it performed. I want to duplicate it, perform writes, reindex it and then with single fast transaction delete the old one and rename the new one to original name.

What is the fastest way to duplicate such huge table?


texts: Changing a variable using SET GLOBAL doesn't impacts sessions variable?

texts:
I am bit confused between setting the global and session parameters , I am trying to set wait_timeout

The default settings are

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                 28800 |                  28800 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

The below SQL will change the session setting

SET wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Check the values , which is pretty clear as well

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                 28800 |                     10 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

The below SQL will change it globally

SET GLOBAL wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Check the values , which is pretty clear as well

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                    10 |                     10 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

Now , I am closing the MySQL thread and opening a new one , I find the below data

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                    10 |                  28800 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

I am not able to relate at this point why the session settings have been changed as I already changed the value using SET GLOBAL.


texts: Failed to start of service MongoDB community4.2 using Homebrew in Mac OS Mojave 10.14

texts:
Error message while starting the mongoDB service.
Error:

  Permission denied @ rb_sysopen -
  /Users/syedahmed/Library/LaunchAgents/homebrew.mxcl.mongodb-community.plist

I installed/reinstall the mongoDB couple times.
But I am getting same problem

  " error : permission denied"

I am using the community@4.2 edition in Mac OS Mojave 10.14

I have tried different ways after reading suggestions in stack overflow but nothing has worked.

I have installed following the instruction from mongoDB.

The services doesn't start.

how to config the username and password using config?  I am not sure.

while installing from brew it did not ask for config any password or username.

brew services start mongodb-community@4.2

if I start service typing just:  mongo
Following error message comes:

  MongoDB shell version v4.2.0 connecting to:
  mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
  2019-09-01T23:23:31.392-0400 E QUERY [js] Error: couldn't connect to
  server 127.0.0.1:27017, connection attempt failed: SocketException:
  Error connecting to 127.0.0.1:27017 :: caused by :: Connection refused
  : connect@src/mongo/shell/mongo.js:341:17 @(connect):2:6
  2019-09-01T23:23:31.397-0400 F - [main] exception: connect failed
  2019-09-01T23:23:31.397-0400 E - [main] exiting with code 1 –


texts: Export entire Oracle schema to ASCII and import into DB2

texts: I'm looking for a solution to export a number of schemas from Oracle to ASCII or other flat file format. The data then has to be read by DB2 import. Does anyone know the what oracle export commands would give me full schema export to ASCII on a Unix platform. My testing database is about 30GB in size. I would have to run this via command line on the box.


texts:
Could someone please show or describe to me how to implement soft deletes?

I have table of vouchers with these fields: id, username, password, serial.

I want to display a given number of rows depending on the customer's request
and once displayed they should be deleted so that the are not displayed again. (They are already invalid.)

I presume soft delete will ensure that no voucher is displayed twice.
If you have any other idea on how I would do it I would appreciate.


texts: mysqldbcompare showing AUTO_INCREMENT differences but there are no missing records

texts:
I am encountering a strange issue when using mysqldbcompare to compare two MySQL DBs(one is a master, the other its slave). I noticed that the tool notes that the AUTO_INCREMENT values of this specific table are different for both DBs. However, the table contents are exactly the same for this table in both DBs (i.e. no missing records, and the primary key id of the last record in the table for each DB is the same! ), which is very strange.

What would cause the AUTO_INCREMENT values to be different then? Is it something that has to do with replication or is it caused by mysqldbcompare when it is running the comparison?

This is the output for that specific table from mysqldbcompare :

# TABLE     special_table                            FAIL    pass    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  pass
#
# Transformation for --changes-for=server1:
#

ALTER TABLE `production`.`special_table`
  DROP INDEX index_id_device_id,
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`id`),
  ADD UNIQUE INDEX index_id_device_id (index_id,device_id),
AUTO_INCREMENT=542876164;

Thanks


texts: Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

texts:
Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like:

CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle VARCHAR(200) NOT NULL
, PRIMARY KEY (BookID)
) ;

CREATE TABLE Tag
( TagID INT NOT NULL
, TagName VARCHAR(50) NOT NULL
, PRIMARY KEY (TagID)
) ;

CREATE TABLE BookTag
( BookID INT NOT NULL
, TagID INT NOT NULL
, PRIMARY KEY (BookID, TagID)
, FOREIGN KEY (BookID)  REFERENCES Book (BookID)
, FOREIGN KEY (TagID)   REFERENCES Tag (TagID)
) ;

CREATE TABLE Aspect
( AspectID INT NOT NULL
, AspectName VARCHAR(50) NOT NULL
, PRIMARY KEY (AspectID)
) ;

CREATE TABLE TagAspect
( TagID INT NOT NULL
, AspectID INT NOT NULL
, PRIMARY KEY (TagID, AspectID)
, FOREIGN KEY (TagID)   REFERENCES Tag (TagID)
, FOREIGN KEY (AspectID)  REFERENCES Aspect (AspectID)
) ;

and the issue is how to define the BookAspectRating table and to enforce referential integrity, so one cannot add a rating for a (Book, Aspect) combination that is invalid.

AFAIK, complex CHECK constraints (or ASSERTIONS) that involve subqueries and more than one table, that could possibly solve this, are not available in any DBMS.

Another idea is to use (pseudocode) a view:

CREATE VIEW BookAspect_view
  AS
SELECT DISTINCT
    bt.BookId
  , ta.AspectId
FROM
    BookTag AS bt
  JOIN
    Tag AS t  ON t.TagID = bt.TagID
  JOIN
    TagAspect AS ta  ON ta.TagID = bt.TagID
WITH PRIMARY KEY (BookId, AspectId) ;

and a table that has a Foreign Key to the above View:

CREATE TABLE BookAspectRating
( BookID INT NOT NULL
, AspectID INT NOT NULL
, PersonID INT NOT NULL
, Rating INT NOT NULL
, PRIMARY KEY (BookID, AspectID, PersonID)
, FOREIGN KEY (PersonID)   REFERENCES Person (PersonID)
, FOREIGN KEY (BookID, AspectID)
    REFERENCES BookAspect_view (BookID, AspectID)
) ;

Three questions:

Are there DBMS that allow a (possibly materialized) VIEW with a PRIMARY KEY?
Are there DBMS that allow a FOREIGN KEY that REFERENCES a VIEW (and not only a base TABLE)?
Could this integrity problem be solved otherwise - with available DBMS features?

Clarification:

Since there is probably no 100% satisfying solution - and the Django question is not even mine! - I'm more interested in a general strategy of possible attack on the problem, not a detailed solution. So, an answer like "in DBMS-X this can be done with triggers on table A" is perfectly acceptable.


texts: Should a SKU be associated to warehouse or supply table?

texts:
This may be a trivial question for any of you experienced in supply chain models. The model I am building in PosgreSQL has 3 tables:
create table supply (
  id       serial primary key,
  barcode  varchar,
  title    varchar
  ...
);

create table warehouse (
  id     serial primary key,
  title  varchar,
  ...
);

create table warehouse_supply (
  id            serial primary key,
  warehouse_id  bigint not null,
  supply_id     bigint not null,
  ...
  foreign key (supply_id) references supply (id),
  foreign key (warehouse_id) references warehouse (id)
);

I was tempted to include an SKU (Stock Keeping Unit) column at table supply, but the very definition of SKU made me think it is a warehouse right to state their own SKU naming policy (this could occur with 3rd party warehouses).
Am I right? Should I include an sku varchar column at warehouse_supply instead of supply?


texts: Backing up a really huge SQL Server database

texts:
We have a SQL Server 2008 database of size over 200GB. There is no disk space on the server to take a backup. I really need to take a backup or make another copy of this database. what would be the best way to achieve this?

Thanks


texts:
I Create a user in my database with following command :

CREATE USER Test WITHOUT LOGIN

I created a login with the Test Name.

I want to link Test user with Test Login.

When I use following command :

EXEC sp_change_users_login 'Update_One', 'Test', 'Test'

SQL Server raised bellow error :

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'Test' is absent or invalid.

And when I use following command :

ALTER USER Test WITH LOGIN = Test

SQL Server Raised Bellow error :

Msg 33016, Level 16, State 1, Line 2
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

How can I link a Test user with Test login.

My purpose of this link :
I want to restore a database from a server to another server and after complete restoring, map users with existing login on the server.

thanks in advance.


texts: Automatic MySQL Replication Fail-over Strategy

texts: I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the process , i.e if replication is down when no DBA is available then how the automatic fail-over will take place.


texts:
I'm given the following ER model.

It's unclear to me how I'd identify copies. For the image entity I think it's sufficient to take the 'ArtistName' and 'ImageName' as a combined primary key.

Another problem is that if I am going to use this combined primary key, any table referencing this entity would need 2 columns to identify the image. It all seems easier using simple IDs. So considering (1) disk space and (2) performance (comparing 2 string keys vs 1 integer key) unique IDs seem to be the superior option.

Because the 'buys' relationship contains an attribute and in the exercise we're explicitly told to avoid NULL-attributes I can't really add a 'boughtBy' attribute to the copy entity (as it is perhaps not bought yet)

So using an auxiliary table I clearly need to have a primary key for copies. The easiest way would be to simply add an CopyID attribute but from the exercise it's not clear to me if I should only construct PKs from existing attributes only.

If there's an in-between solution I'm grateful hearing your suggestions. :)


texts: Efficient method of inserting a column into a table based on another table

texts:
Consider the following tables

CREATE TABLE Main (
    id INT PRIMARY KEY,
    state VARCHAR(255) DEFAULT NULL
)

CREATE TABLE ColorTable (
    color VARCHAR(255),
    state  VARCHAR(255)
)

INSERT INTO Main (id, state) VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'B'),
    (5, 'C'),
    (6, 'A'),
    (7, NULL)

INSERT INTO ColorTable (color, state) VALUES
    ('Yellow', 'A'),
    ('Red', 'B'),
    ('Green', 'C'),
    ('Black', NULL)

I would like to add a new column 'color' into the table 'Main'. I want this such that the color for each id is based on the state in 'Main', and the corresponding color in 'ColorTable'.

I can achieve this through the following:

ALTER TABLE main
ADD color VARCHAR(255)

UPDATE main
SET main.color = ColorTable.color
FROM main
LEFT JOIN ColorTable ON main.state = ColorTable.state

UPDATE main
SET color = 'Red'
WHERE state IS NULL

I am curious as to alternative methods of doing this–can I achieve the same result using a SELECT INTO or something else?


texts:
I've been noticing that PgAdmin III isn't as responsive as I expected it to be.

For instance a query to select * ... from a table containing 20,397 rows takes 19.531 seconds in PgAdmin III and 7.383 seconds in DbVisualizer 9.0.7. I initially thought this was a coincidence, but it has been consistent across all queries run.

As such it appears that PgAdmin III takes approximately twice as long to query my database over DbVisualizer. So how can I speed up PgAdmin III Queries?

I am using Heroku Postgres 9.1.9 on the Crane package and PgAdmin 1.16.0.

--EDIT--

I upgraded PgAdmin to version 1.16.1 and query time is 19.038 seconds, so no change with software revision.


texts:
My table looks like this:

CREATE TABLE "Content" (
    "id" VARCHAR(36) NOT NULL,
    "xml" XML
);

I have an index defined

create index "idx_creation_date" on "Content"("xml") generate keys
    using xmlpattern '/*/dateCreated' as sql timestamp;

This index works fine when using it in comparisons (XMLEXISTS), but now I want to use that index to sort my table according with the date my content was created and select the first 20 items.

Sadly, this didn't work

SELECT "Content".* FROM "Content"
    ORDER BY XMLCAST(XMLQUERY('$xml/*/dateCreated' PASSING "xml" as "xml")
    as TIMESTAMP) DESC FETCH FIRST 20 ROWS ONLY

because XMLQUERY doesn't use indexes. The same goes for

xquery
let $items :=
   for $i in db2-fn:xmlcolumn("Content.xml")
   order by $i/*/dateCreated
   return $i
for $i at $count in subsequence($items, 1, 20)
return $i

because let and return can't use indexes.

Anyone has an idea how I can use the existing (and working) index on dateCreated in a ORDER BY query?


texts: Postgres: query multiple records using a list of double keys

texts:
I have 3 tables:

╔═════════════════════╗
║      companies      ║
╠═════════════════════╣
║ id                  ║
║ some_company_attrib ║
╚═════════════════════╝

╔════════════════════╗
║       people       ║
╠════════════════════╣
║ id                 ║
║ some_person_attrib ║
╚════════════════════╝

╔════════════════════════════════════════════════════════════╗
║                            jobs                            ║
╠════════════════════════════════════════════════════════════╣
║ id                                                         ║
║ company_id – foreign key pointing to the companies table   ║
║ person_id  – foreign key pointing to the people table      ║
║ some_job_attrib                                            ║
╚════════════════════════════════════════════════════════════╝

From an external source, I have a big list of pairs of attributes, which reference companies.some_company_attrib and people.some_person_attrib, like so:

('some_company_attrib123', 'some_person_attrib456'),
('some_company_attrib789', 'some_person_attribxyz'),
etc...

where each pair should match a record in the jobs table, so that the pair ('some_company_attrib123', 'some_person_attrib456') gives me back the  record in the jobs which is associated:

to the record in the companies table where some_company_attrib = 'some_company_attrib123'
and to record in the people table where some_person_attrib = 'some_person_attrib456'

After looking around and not finding any solution, I am using arrays in the following query:

SELECT * FROM jobs
  INNER JOIN people ON people.id = jobs.person_id
  INNER JOIN companies ON companies.id = jobs.company_id
WHERE (
    ARRAY[companies.some_company_attrib, people.some_person_attrib]::text[] in (
    '{"some_company_attrib123", "some_person_attrib456"}',
    '{"some_company_attrib789", "some_person_attribxyz"}',
    '{"and_many_many", "more_like_these"}'
);

Although it works, it feels rather clunky and it's actually pretty slow.
I was wondering whether there was an idiomatic and more elegant way to achieve this.


texts: Moving Files in SQL as Blob (Filestream)

texts:
I have setup both of my Servers with Filestream for T-SQL access (and my DBs).
I created tables on both Servers:

CREATE TABLE mytable(
[UI] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT (newid()),
      [Data]      [varbinary](max)FILESTREAM NULL)

I am inserting my File (.bak File, Backup of my DB 5,2GB) as:

INSERT table ( Data )
SELECT * FROM OPENROWSET (BULK 'F:\myDB.bak', SINGLE_BLOB) AS X

I insert it on a same table over Linked Server in my 2nd Server. Then i want to create my .bak file again:

DECLARE @fPath varchar(400) = 'F:\myDB.bak'
, @init int,
@data varbinary(max)

SELECT @data = (Select data from mytable)

EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1;
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources

But my File only comes out as 1,6GB. and after trying to restore it i get:

  Msg 3013, Level 16, State 1, Line 9 RESTORE DATABASE is terminating
  abnormally. Msg 3287, Level 16, State 1, Line 9 The file ID 1 on
  device 'F:\myDB.BAK' is incorrectly formed and can not be read.

What am I doing wrong?


texts:
When I am logged in as rds_user and have following privileges:
GRANT SELECT, INSERT, UPDATE, DROP ONnews.* TO 'rds_user'@'%'

My code runs INSERT command just fine, but when it gives ProgrammingError it executes UPDATE instead. However, UPDATE command errors out with
1142 (42000): UPDATE command denied to user 'rds_user'@'XX.XX.XX.XX' for table 'data'

data is a table in db news

I tried granting privilege again and flushed privileges, still same error. I am using Python's sql connector to send the commands. Genuinely confused on what is going


texts: Why does my table hold duplicate primary keys?

texts:
To my big (and worrying) surprise I just realized that my table with millions of rows, has a few handful of rows, where the primary key (ID) is a duplicate! I don't understand how this can ever happen, and how I might prevent it in the future?

The column holding the primary key is, and has always been, subject to a CONSTRAINT fruits_pkey PRIMARY KEY(id);

I'm running postgreql 9.3.4 on ubuntu.

UPDATE

@Mat: Datatype is integer

@ypercube: Yes, select count(*) from (select count(*) from fruits group by id having count(*) > 1) as t1 returns 41.

@Craig: Yes, I have done a failover before, and my slave is actually 9.3.3


texts: Restore the sql server 2014 database on specific time

texts:
Good day sirs, we encountered a problem in our database (lets call db1) and I hope you can help me with it :(
Last August 19, we generate a backup file but on Aug. 20 we fail do so. August 24 my co-worker update the database db1 and restore a .bak file to it.
Now the problem is the original records are now gone and we have to get all the records on Aug. 20 but we do not have a .bak file for that specific time. Is there a way to rollback the action done last aug. 24?


texts: If my database goes down (let's say memory maxed out) while i'm creating an unlogged table, is the damage limited to just losing that unlogged table or could it prevent recovery of the rest of the database?


texts: Effects of renaming FC and using its original name for AG network name

texts:
We have a Failover Cluster, named MainSQL consisting of MainSQL01 and MailSQL02 which are default named instances and we can connect to whichever is the active node with just simply MainSQL.

If we were to rename the FC to MainSQL-NY then the connection would then be MainSQL-NY\MainSQL as far as I can tell, since the instance cannot be renamed. If we then put this cluster into an Availability Group in a DR site with another cluster (let's say  MainSQL-NJ\MainSQL with its own two nodes) would we be able to re-use the MainSQL name when creating the AG Virtual Network Name, thus allowing connections the AG and its underlying active cluster node to still be made simply using MainSQL as it would once again be MainSQL\MainSQL?

The alternative is to rebuild the clusters or just force all our applications to update their connection strings, but I'm curious if this is feasible.


texts: Lines starting with colons in SQL Server

texts:
Playing around with DB projects, Visual Studio generated a change script with the following lines:

GO
SET ANSI_NULLS, ANSI_PADDING --etc.;
SET NUMERIC_ROUNDABORT OFF;

GO
:setvar DatabaseName "Foo"
:setvar DefaultFilePrefix "Foo"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO

:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END

GO
USE [$(DatabaseName)];

GO
CREATE NONCLUSTERED INDEX [SomeIndex]
    ON [dbo].[SomeTable]([SomeColumn] ASC);

GO

What is the significance of the colons at the beginning of the lines? This Stackoverflow question says that the colon is for bind variables, but then what does the colon in :on error exit do?


texts: SQL Server to MySQL migration using MySQL workbench

texts:
I tried to migrate database from SQL Server 2012 to MySQL v5.5. I used MySQL workbench v6.1 community edition. What I was migrating is just schema only, not for the data. I got this error

Reverse engineer selected schemas....
Reverse engineering dbo from ElectraPortal
- Reverse engineering catalog information
- Reverse engineering User Data Types...
- Preparing...
- Gathered stats for dbo
Reverse engineering 30 tables from dbo
Traceback (most recent call last):
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\modules\db_mssql_grt.py", line 385, in reverseEngineer
    reverseEngineerTables(connection, schema)
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\modules\db_mssql_grt.py", line 546, in reverseEngineerTables
    table_names = [(row[0], row[1]) for row in execute_query(connection, query % schema.name) ]
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\modules\db_mssql_grt.py", line 131, in <lambda>
    con.add_output_converter(-150, lambda value: value if value is None else value.decode('utf-16'))
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\Python\Lib\encodings\utf_16.py", line 16, in decode
    return codecs.utf_16_decode(input, errors, True)
UnicodeDecodeError: 'utf16' codec can't decode byte 0x2e in position 160: truncated data

Traceback (most recent call last):
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\workbench\wizard_progress_page_widget.py", line 192, in thread_work
    self.func()
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\modules\migration_schema_selection.py", line 162, in task_reveng
    self.main.plan.migrationSource.reverseEngineer()
  File "C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\modules\migration.py", line 351, in reverseEngineer
    self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)
SystemError: UnicodeDecodeError("'utf16' codec can't decode byte 0x2e in position 160: truncated data"): error calling Python module function DbMssqlRE.reverseEngineer
ERROR: Reverse engineer selected schemas: UnicodeDecodeError("'utf16' codec can't decode byte 0x2e in position 160: truncated data"): error calling Python module function DbMssqlRE.reverseEngineer
Failed

What should I do?


texts: How to trace steps in SQL profiler for only particular application

texts:
We are using a third party company's software that talks to a SQL Server database and retrieves and inserts all the information.

When a user uses this software sometimes it freezes at a particular step.
I need to troubleshoot it and find out why it happens.

Basically I want to use this UI software and at the same time see what query runs at each step I am performing.

For that I am using SQL Profiler to see what query causes this bottleneck.
But how would I trace steps only for that particular software and only for a particular user? I don't want to see all other information.

I am able to filter NTUserName, which I am assuming is just UserName, correct?

But how can I find out what is the ApplicationName?

If I ran this query I see 13 different applications, including Microsoft Office, Report Server etc. None of them sounds like the name of the software we use. (MGA Systems. Insurance Management System)

select distinct
    program_name
from sys.dm_exec_sessions
where is_user_process = 1;


texts: MySQL restrict specific login to single ip address

texts:
i am trying to setup a mysql server with some logins that can be accessed from anywhere, and another login ( effectively root access ) from a single machine with a static ip. both machines have a single WAN address and are located in the same hosted server facility. the only firewall present is the one built into the operating system.

Host ( 81.1.1.1 / SERVER-4XP5PS5 / mysql_win64 5.5 / svr2012r2 ):

firewall port translation :

netsh interface portproxy add v4tov4 listenport=5001 connectport=3306 connectaddress=81.1.1.1 protocol=tcp
Advanced Firewall Rules->Add inbound open port 'MySQL5001' / port 5001 / tcp / All connection types

mysql remote login :

create user 'testuser'@'82.2.2.2' identified by 'testpassword';
grant all on *.* to 'testuser'@'82.2.2.2';

Client ( 82.2.2.2 / mysql_win32 5.5 / svr2012r2 ):

mysql.exe -h81.1.1.1 -P5001 -utestuser -ptestpassword
ERROR 1045 (28000): Access denied for user 'testuser'@'SERVER-4XP5PS5' (using password: YES)

it seems the mysql server on the host thinks the connection is coming from the local machine, but it is not, i am running the mysql connect command from the client machine.

any ideas how to get mysql to get testuser to connect through port 5001 while restricting testuser connections to ip 82.2.2.2 ?

note : it works fine if i lose the ip address requirement and replace 82.2.2.2 with %, but that defeats the purpose of what i am trying to achieve.


texts:
I have a table with a few dozens of rows. Simplified setup is following

CREATE TABLE #data ([Id] int, [Status] int);

INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);

And I have a query that joins this table to a set of table value constructed rows (made of variables and constants), like

DECLARE @id1 int = 101, @id2 int = 105;

SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (@id1, 'A'),
        (@id2, 'B')
    ) p([Id], [Code])
    FULL JOIN #data d ON d.[Id] = p.[Id];

Query execution plan is showing that optimizer's decision is to use FULL LOOP JOIN strategy, which seems appropriate, since both inputs have very few rows. One thing I noticed (and cannot agree), though, is that TVC rows are being spooled (see area of the execution plan in the red box).

Why optimizer introduces spool here, what is the reason to do it? There is nothing complex beyond the spool. Looks like it is not necessary. How to get rid of it in this case, what are the possible ways?

The above plan was obtained on

  Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)


texts: MySQL Syntax error when trying to create database trigger

texts:
When Trying to create the below trigger, I am receiving a syntax error. It looks good to me and I'm having a terrible time trying to figure it out.

  DELIMITER $$
    CREATE TRIGGER trg_pic_hours
    AFTER INSERT ON CREW
    FOR EACH ROW
    BEGIN
    IF (NEW.CREW_JOB = 'Pilot') THEN
    UPDATE PILOT
    SET PIL_PIC_HRS = TEMP_PIL.PIL_PIC_HRS + CHARTER.CHAR_HOURS_FLOWN
    WHERE CHARTER.CHAR_TRIP = NEW.CHAR_TRIP
    AND PILOT.EMP_NUM = NEW.EMP_NUM;
    ENDIF;
    END$$
    DELIMITER;

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; END' at line 10


texts: How can I pass on a value in the hierarchy based on the value of the parent using an hierarchial query?

texts:
I have a table with categories and another table with a discount for categories per user:

create table category
(
  id NUMBER NOT NULL,
  parent_id NUMBER,
  name nvarchar2(255),
  PRIMARY KEY( id)
);

create table category_discount
(
  user_id NUMBER NOT NULL,
  category_id NUMBER NOT NULL,
  discount NUMBER NOT NULL,
  PRIMARY KEY( user_id, category_id)
);

insert into category( id, parent_id, name ) VALUES( 1, null, 'root');
insert into category( id, parent_id, name ) VALUES( 2, 1, 'C1');
insert into category( id, parent_id, name ) VALUES( 11, 7, 'C11');
insert into category( id, parent_id, name ) VALUES( 12, 7, 'C12');
insert into category( id, parent_id, name ) VALUES( 3, 1, 'C3');
insert into category( id, parent_id, name ) VALUES( 4, 1, 'C4');
insert into category( id, parent_id, name ) VALUES( 5, 4, 'C5');
insert into category( id, parent_id, name ) VALUES( 6, 4, 'C6');
insert into category( id, parent_id, name ) VALUES( 9, 6, 'C9');
insert into category( id, parent_id, name ) VALUES( 10, 6, 'C10');
insert into category( id, parent_id, name ) VALUES( 7, 1, 'C7');

insert into category_discount( user_id, category_id, discount ) VALUES( 1, 1, 30);
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 4, 20);
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 7, 25);

Now when I do this query:

SELECT category.*,
       category_discount.user_id,
       category_discount.discount,
       LEVEL
FROM   category
LEFT JOIN category_discount
       ON category.id = category_discount.category_id
       AND category_discount.user_id = 1
START WITH parent_id is null
CONNECT BY PRIOR id = parent_id;

...I get this output which is fine:

| ID | PARENT_ID | NAME | USER_ID | DISCOUNT | LEVEL |
|----|-----------|------|---------|----------|-------|
|  1 |    (null) | root |       1 |       30 |     1 |
|  2 |         1 |   C1 |  (null) |   (null) |     2 |
|  3 |         1 |   C3 |  (null) |   (null) |     2 |
|  4 |         1 |   C4 |       1 |       20 |     2 |
|  5 |         4 |   C5 |  (null) |   (null) |     3 |
|  6 |         4 |   C6 |  (null) |   (null) |     3 |
|  9 |         6 |   C9 |  (null) |   (null) |     4 |
| 10 |         6 |  C10 |  (null) |   (null) |     4 |
|  7 |         1 |   C7 |  (null) |   25     |     2 |
| 11 |         7 |  C11 |  (null) |   (null) |     3 |
| 12 |         7 |  C12 |  (null) |   (null) |     3 |

My requirement is whenever there is a discount on the parent it should be inherited down the hierarchy as long as the child doesn't haven an explicit discount set. So my final output should look like this:

| ID | PARENT_ID | NAME | USER_ID | DISCOUNT | LEVEL |
|----|-----------|------|---------|----------|-------|
|  1 |    (null) | root |       1 |       30 |     1 |
|  2 |         1 |   C1 |  (null) |       30 |     2 |
|  3 |         1 |   C3 |  (null) |       30 |     2 |
|  4 |         1 |   C4 |       1 |       20 |     2 |
|  5 |         4 |   C5 |  (null) |       20 |     3 |
|  6 |         4 |   C6 |  (null) |       20 |     3 |
|  9 |         6 |   C9 |  (null) |       20 |     4 |
| 10 |         6 |  C10 |  (null) |       20 |     4 |
|  7 |         1 |   C7 |  (null) |       25 |     2 |
| 11 |         7 |  C11 |  (null) |       25 |     3 |
| 12 |         7 |  C12 |  (null) |       25 |     3 |

How can I do this?

I am using Oracle 11g and Oracle 12c.


texts: Using OFFSET in a large table by a simple sub-query

texts:
The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as

SELECT *
FROM table
INNER JOIN (
SELECT id
FROM table
LIMIT 10 OFFSET 100000)
AS results USING(id);

but I wonder what is the bennefit of INNER JOIN over a simple sub-query as

SELECT *
FROM table
WHERE id >
(SELECT id
FROM table
LIMIT 1 OFFSET 100000)
LIMIT 10;

or more convenient

SELECT *
FROM table
WHERE id IN
(SELECT id
FROM table
LIMIT 10 OFFSET 100000);

In the following example, the ORDER BY is assumed the primary key, but obviously other orders or even WHERE clause can be added. My question is about comparison in performance of INNER JOIN or a simple sub-query`.


texts: How can I determine the size of a dump file prior to dumping?

texts: How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?


texts: How to SELECT from three different tables using multiple foreign keys

texts:
I have 3 tables:

languages

id | tag   | region        | language
--   -----   -------------   --------
1    en-US   United States   English
2    es-ES   Spain           Spanish
...

modules

id | module
--   ------
1    header
2    main
3    footer

contents

id | language_id | module_id | content
--   -----------   ---------   -------
1    1             1           This is my header
2    1             3           This is my footer
3    2             1           Este es mi encabezado

I want to select all content for all of the expected modules, even if there's no contents record for that module, (notice I'm missing a "main" module) so I've written:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id

...which nicely returns

module | content
------   -------
header   This is my header
header   Este es mi encabezado
main     null
footer   This is my footer

But now I need to select only the modules with the language_id that maps to 'en-US'. If I amend my JOINs I lose my "main" module in my result:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'en-US'

module | content
------   -------
header   This is my header
footer   This is my footer

I am very new to JOINs. What am I missing?


texts: Oracle Database systemd service: ORA-01031: insufficient privileges

texts:
I'm having troubles starting an oracle database with systemd.
The start and stop script was created in /home/oracle/scripts/
oracledbstart.sh:
#!/bin/bash
export ORACLE_HOME=/opt/oracle/product/11.2.0.1/db_1
$ORACLE_HOME/bin/dbstart $ORACLE_HOME

oracledbstop.sh:
#!/bin/bash
export ORACLE_HOME=/opt/oracle/product/11.2.0.1/db_1
$ORACLE_HOME/bin/dbshut $ORACLE_HOME

If called by the user oracle both scripts work fine.
if the scripts are triggered by systemd, they will fail. The following unit file is used to start and stop the database:
[Unit]
Description=Oracle Datbase Service
after=syslog.target network.target

[Service]
# https://bugzilla.redhat.com/show_bug.cgi?id=754285
LimitMEMLOCK=infinity
LimitNOFILE=6553500

#Type=simple
RemainAfterExit=yes
User=oracle
Group=oinstall
ExecStart=/home/oracle/scripts/oracledbstart.sh
ExecStop=/home/oracle/scripts/oracledbstop.sh

[Install]
WantedBy=multi-user.target

Starting the database with systemd results in this error ($ORACLE_HOME/startup.log):
/opt/oracle/product/11.2.0.1/db_1/bin/dbstart: Starting up database "FISP"
Thu Nov 26 03:12:16 CST 2020

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 26 03:12:16 2020

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> ERROR:
ORA-01031: insufficient privileges

SQL> ORA-01031: insufficient privileges
SQL>
/opt/oracle/product/11.2.0.1/db_1/bin/dbstart: Database instance "FISP" warm started.

Any suggestions how to fix this?


texts: MySQL can't connect to another server with ERROR 2003 (HY000)

texts:
I have two servers:

server1: 192.168.0.2
server2: 192.168.0.3

They are both CentOS 6. I installed MySQL on both two servers.

I can ping server2 from server1

$ ping 192.168.0.3

I can connect MySQL server with host on server2

# (server2)
$ mysql -h 192.168.0.3 -u root -p

But I can't connect server2 from server1:

# (server1)
$ mysql -h 192.168.0.3 -u root -p
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.3' (113)

My my.cnf on server2:

# /etc/my.cnf
[mysqld]
...
bind-address = 0.0.0.0

Off course restart it: service mysqld restart

My iptables on server2:

# /etc/sysconfig/iptables
...
-A INPUT -p tcp --dport 3306 -j ACCEPT
...

Off course restart it: service iptables restart

I did this on server2:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '' WITH GRANT OPTION;
FLUSH PRIVILEGES;

When did mysql_secure_installation on server2, I allowed remote root connect.

Everything I think good. But can't connect server2 from remote. Why?


texts: Normalization: 'zip code' as a primary key

texts:
I've seen frequently that you should not have all address fields (zip, street, city, state, country, etc) in the same table as your user info table (pk_id, name, email... ).

This would be in violation of the 3rd normalization. That makes sense, all the address fields are not based on the user (or user's pk).

However, I have seen more than once that a zip code makes a good primary/foreign key to fix this? Huh? Example: Normalization of Database

Obviously more than one person can live in the same zip code so this does not make sense to me. It seems odd to me to keep the zip code in the user table but leave the rest of the address in another table.

What is the standard best way to have an address table separated, but still abiding by the rules of normalization?


texts: How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?

texts:
I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like:

add_index :users, [:username, :created_at]

That should utilize the index when I do a query like (I think):

User.where("username = ? and created_at > ?", 'jim', DateTime.now.yesterday)

But will it also utilize the index if I only use the username in a query?

User.where("username = ?", 'jim')


texts: Is is possible to combine local variables in a case statement?

texts:
DECLARE @CalcQuery varchar(340)
SET @CalcQuery = (SELECT
    sum(T0.[LineTotal])
FROM
    RDR1 T0
        INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
        INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
        INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]
WHERE
    T3.[Name] = 'Wild Heart SP-1'
    AND
    T0.[Project] = 'ATL Market Molina')

SELECT DISTINCT
T0.[Project],
CASE
   WHEN T0.[Project] = 'ATL Market Molina'
   THEN @CalcQuery
   END [Season Total]
FROM
RDR1 T0
INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN [@SES_SSNSBRG] T2 ON T0.[ItemCode] = T2.[U_SES_ItemCode]
INNER JOIN [@SES_SEASONS] T3 ON T2.[U_SES_SEASON] = T3.[Code]

Is there a way for me to Nest 50 Project codes and 7 Seasons without writing redundant SQL and Case statements. I think that the only way to go would be to declare variables for the project codes and seasons. The only issue is that I'm unsure on how to merge them with @CalcQuery. I wrote out the SQL without the variables and it's long.

The output would be the total for the specific season 'Wild Heart' and project 'ATL Market Molina'. The company that requested this didn't plan ahead and created many order tags. T3.[Name] = 'Wild Heart SP-1' is the season name.


texts: Updating +150,000 rows using .sql file causes ERROR 2013 Lost connection to MySQL server during query

texts:
I am using ubuntu 12.04. I have a .sql file that is around 5 megabytes.

When I use the command: mysql -u -p -v database query.sql
The server prints out the content of the file (because of --verbose) and then stops at the bottom. It never exits.

When I do ctrl+c I get an error:

ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

I am updating a column in a table with different values, which has over 150,000 rows.

My max_allowed_packet variable in the my.cnf is set to 64M

When I check if the update worked only some of the values are updated, not all.

Why am I getting this error?

EDIT: Sample code for query.sql:

UPDATE srs_person_to_org
 SET org_id = CASE
WHEN p_uuid = '1000001' THEN '2'
WHEN p_uuid = '1000002' THEN '2'
WHEN p_uuid = '1000003' THEN '2'
WHEN p_uuid = '1000004' THEN '2'
          .......
WHEN p_uuid = '1152337' THEN '19'
WHEN p_uuid = '1152338' THEN '19'
WHEN p_uuid = '1152339' THEN '19'
ELSE org_id
END;


texts: Why is it bad practice to install SQL Server to the root of a drive

texts:
When I install SQL Server into the root of my drive, for example D:\, I get an error when I run a database deployer from a third party piece of software.

But when I move my SQL Server instance to D:\SQL\ (move it to a folder within the drive) then installation works perfectly.

My question doesn't regard the database deployer but more into the reason why SQL would have an issue with being installed into the root of a drive. Is this bad practice? Why shouldn't I install SQL Server to the root of my drive?


texts: How to populate a 'map' table from words of a text?

texts:
Having the database structure that follows:

CREATE TABLE articles
(
article_id int(11) unsigned NOT NULL AUTO_INCREMENT,
article text,
PRIMARY KEY(article_id)
) ENGINE=InnoDB;

CREATE TABLE words
(
word_id int(11) unsigned NOT NULL AUTO_INCREMENT,
word varchar(255),
PRIMARY KEY(word_id)
) ENGINE=InnoDB;

CREATE TABLE word_map
(
article_id int(11) unsigned NOT NULL,
word_id int(11) unsigned NOT NULL,
FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
FOREIGN KEY(word_id) REFERENCES words(word_id) ON DELETE CASCADE,
PRIMARY KEY(article_id, word_id)
) ENGINE=InnoDB;

How can I split the text column in the article table to find the words which exist in the words table and, then, INSERT the words into word_map?

Now I am doing this in PHP by splitting the text into words, then, finding word_id for each word, and then, INSERT INTO word_map (article_id, word_id).


texts: Can anyone describe the step by step process of recovery , backup and restore in sql server

texts:
Can anyone  describe the step by step process of recovery via backup and restore in SQL Server 2008.

If possible please include some examples.


texts:
Please compare the directions below to my ER Diagram and tell me if anything is wrong with my ER Diagram Please I think its accurate enough.

Oxford City Council is preparing to launch an innovative program that will provide community members with short-term access to bicycles through self-service terminals placed at convenient locations across the city. Each terminal is given a unique identification number, street address, and maximum storage capacity. The city keeps track of the bicycles currently available at each terminal for logistical reasons.
The program will offer subscriptions that include weekly, monthly, and annual memberships. Membership costs range from a few dollars each week to over $100 for an annual membership. Information stored about each member includes full name, email, contact phone numbers, residential address, driver's license number, and membership status. A subscriber may only have one active membership at any given point in time. In addition to memberships, visitors or casual users can access the system by purchasing a $5 one-day pass using a credit card.
The city has made an initial purchase of 700 bicycles, each of which is fitted with a GPS tracking device. The city records the unique ID of each bicycle, along with it's make, model, color, and size. To reduce costs, private-sector companies are invited to sponsor individual bicycles and self-service terminals and, in return for a fee, their logo will be displayed prominently on the sponsored asset. The city tracks the name of each sponsoring company along with their address, contact name, email, telephone number, and a list of the assets that they have chosen to sponsor.
Each rental transaction is fully automated and records details of the user, bicycle, pick-up time and location, drop-off time and location, and total rental length. A user may not borrow more than one bicycle at any given point in time.


texts: One SQL Server 2014 AG node down for 4 days - will it restart into AG?

texts:
We are planning one of our server rooms to be shutdown for 4 days. We have one node of each of our AGs in this server room.

It only just occurred to me that perhaps there may be an Availability Group tombstone setting (similar to replication), whereby an AG node once off line for a while may have a "use by" date and may not restart into the AG. I have looked a lot in MS doco and so far can't find anything that indicates an AG node might be timed out of the AG.

We run SQL Server 2014 SP1 on Windows 2012.


texts: How do delete duplicate key row in mssql server?

texts:
I'm not a great mssql admin so this may be a lot easier, I just don't know where to start looking.

ADO Err Description: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot insert duplicate key row in object 'dbo.posALL'
with unique index 'PK_tposALL'.
Originated at: Business.Cls.DayChange
ADO Err Number: -2147217900
Source: Microsoft OLE DB Provider for ODBC Drivers
SQL State: 23000
Native Err: 2601

When using a program that detects the current day and requests to change it on a new day, fails with the report above.

I'm looking at the database, I can see the table and rows, but I don't know how to see key rows or determine where or why there may be a duplicate, especially for something like which day of the year it is.


texts: MongoDB cluster use x509 for internal authentication only

texts:
I'm setting up a sharded cluster. I've enabled TLS and added certificates to each server in my shards/config replica sets and also my mongos instance. But is it still possible to connect to the mongos instance using username/password?

This is my mongos config:

systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongos.log

net:
  tls:
    mode: requireTLS
    CAFile: /etc/ssl/root-ca.pem
    certificateKeyFile: /etc/ssl/192.168.1.67.pem
  bindIp: 127.0.0.1,192.168.1.67

sharding:
  configDB: config/192.168.1.69:27017

security:
  clusterAuthMode: x509

processManagement:
  fork: true


texts:
We have an old Oracle 12c database server that is not up to date (version 12.1.0.1.0). Unforunately a full version upgrade is out of scope right now, but I was hoping to at least apply the latest patch bundle we have on hand, which would take us to 12.1.0.1.160719 and fix a few vulnerabilities.

I used OPatch and got everything applied without incident, but when I tried to run the datapatch utility afterward I recieved the following errors:

SQL Patching tool version 12.1.0.1.0 on Thu Oct 17 12:10:58 2019
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches:
Currently installed C Patches: 23530410
Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied: 23530410
catconInit: database is not open
Installing patches...
Patch installation complete.  Total patches installed: 1
Validating logfiles...
Patch 23530410 apply:
  logfile:  (no errors)

See support note 1609718.1 for information on how to resolve the above errors

SQL Patching tool complete on Thu Oct 17 12:11:41 2019
catconExec: catconInit has not been runcatconWrapUp: catconInit has not been run

I've found similar errors on Google about 'catconInit: database is not open on the default instance' but nothing that is exactly the same message as mine. In fact, when I Google the last error message 'catconExec: catconInit has not been runcatconWrapUp: catconInit has not been run' I only get one result--the Oracle Support page for this error (Doc ID 1609718.1). Unfortunately, we do not have an ongoing support contract and I cannot see the cause/solution for this.

Also:

There is only one database/instance on this server, and I specify
that with the datapatch parameter
The database is open per V$INSTANCE, and both myself and the application can query it just fine
I've been a DBA for a few years now, but I'm new to Oracle specifically

Does anyone have any ideas? Can anyone relay the information from the support doc? Thanks in advance.

EDIT

OS is Windows Server 2012 R2 Standard
Rerun of 'datapatch -verbose -db MyDatabase' produces the exact same output (just with newer timestamps)
The associated log file from the most recent run of datapatch (D:\oracle\MyDatabase\cfgtoollogs\opatch\opatch2019-11-05_13-29-08PM_1):


texts: How to determine high availability configuration is working correctly

texts:
I am in the process of setting up always on high availability clusters for the first time. Can anyone explain to me from the networking perspective after configuring the group and testing to make sure it is functioning properly, how does SQL know when to fail over, and how would my application connecting to the SQL server know where it is pointing?

So for example I have an application foo connecting to a named instance DB01.FOO. When foo fails and is rolled over to DB02.FOO, how would my application know it should be talking to DB02.FOO?

DB02.FOO is in the same domain but a different server. I am just trying to wrap my head around all aspects of this before attempting it. If there are any links someone would recommend that would be awesome as well.


texts: Identify the version of a collation from ICU in Postgres

texts:
Postgres 10 and later incorporates the International Components for Unicode (ICU) library for text-handling and other internationalization issues.

Changes happen to human languages, such as sorting rules evolving. Therefore collation definitions are versioned in ICU.

➥ How can I identify the version number of a collation?

I know Postgres will report version number as part of a collation version mismatch error message. Is there a way to proactively query for the version rather than wait for an error message?


texts: Azure Elastic Pool - is it supported for MySQL?

texts:
I know that Elastic Pool in Azure is supported for SQL Server.
But I could not find any information if it is supported for MySQL.
I could not find any information in documentation at all what types of databases are supported...
Can you please assist me since I need to provide this info ASAP?
THANKS


texts:
I've setup replication and failover with MySQL Fabric, and everything it's working fine; now I need to give access to the personal that access the database, I'm trying to use MySQL Router, but in every documentation and manual I've found on internet so far, I need to start MySQL router with

  mysqlrouter --config=/path/to/file/my_router.ini

So my server prompts for the password of the admin user of Fabric; when I do that, it starts fine; but I can't figure out how to start it and leave it running in background, so I can disconnect the session and Router keeps running. I found in the Changelog of MySQL Router (https://dev.mysql.com/doc/relnotes/mysql-router/en/mysql-router-news-2-0-2.html):

  The configuration file no longer accepts a password for the Fabric Cache plugin. Instead, MySQL Router prompts for the password at startup.

So I don't know if there is no way to keep it running... Has anyone managed to do this? Or is there another way to connect in a transparent form via MySQL Yog or DBeaver to MySQL Fabric?

Thanks


texts: SQL Server 2016 - ReportServer - sp_BlitzCache

texts: Does it ever make sense to try to tune queries in the ReportServer database behind SSRS? Diagnostic tools such as sp_BlitzCache indicate possible issues, but I've a tendency not to futz with that particular database. Any suggestions?


texts: Installing SQL Server Express instance so common users can access

texts:
I need to install a SQL Server Express instance (as an admin) which can be later on read and modified by regular users.

I run into http://msdn.microsoft.com/en-us/library/ms254504.aspx, and I'm trying to make it work like it says there, with this connection string:

Provider=sqloledb;Data Source=.\\DUBOCALC;Integrated Security=true;User Instance=true;AttachDBFilename=|DataDirectory|\\" + HelperClasses.DuboCalcSettings.dbname + ".mdf;Initial Catalog=DUBOCALC;Trusted_Connection=yes;

The problem I have is that the instance is created under C:\Program Files\Microsoft SQL Server\MSSQL10.INSTANCENAME, but all this under the Administrator's folder, so regular users can't have access there, where the .mdf files are.

In my bootstrapper file, I have the following line to install SQL Express:

<Command PackageFile="SQLEXPR32_x86_ENU.EXE"
         Arguments='/q /hideconsole /action=Install /features=SQL /instancename=DUBOCALC /enableranu=1 /sqlsvcaccount="NT Authority\Network Service" /SQLSYSADMINACCOUNTS="Builtin\Users" /skiprules=RebootRequiredCheck'
         EstimatedInstalledBytes="225000000"
         EstimatedInstallSeconds="420">`

How could I do that, please?

I think it's a very common use, to install a SQL Server Express instance as admin, then running it as a user, but I can't find any useful information which will help me get this working.

thanks a lot everybody in advance,
Manuel.


texts: Migrate a legacy application that uses MySQL replication


texts: Why can not I get any row in a MySQL query from SQL Server?

texts:
The problem I have is that I can not visualize any row with the following query:

SELECT * FROM OPENQUERY ([LINK_MYSQL], 'SELECT * FROM mysql.vw_bd_date;')

The query did not show any error except that it did not throw me any lines either. The consulted view found in MySQL is the following:

create view vw_bd_date
as
SELECT `information_schema`.`TABLES`.`TABLE_SCHEMA` AS `base_de_datos`,MIN(`information_schema`.`TABLES`.`CREATE_TIME`) AS `FECHA_CREACION`
FROM `information_schema`.`TABLES`
WHERE ((`information_schema`.`TABLES`.`TABLE_SCHEMA` NOT IN ('information_schema','sys','mysql','performance_schema','test','test\\_%'))
AND (`information_schema`.`TABLES`.`DATA_LENGTH` IS NOT NULL) AND (`information_schema`.`TABLES`.`INDEX_LENGTH` IS NOT NULL))
GROUP BY `information_schema`.`TABLES`.`TABLE_SCHEMA`

This is the use in a much larger query where I do the join with other views to perform a database inventory, but also as commented, the fields in this particular view throw me NULL.

We tried to do all kinds of cast without success.

Any idea would be very helpful.

Thank you


texts:
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'DELETE_TEXT_TEST_LOG',
        job_type => 'STORED_PROCEDURE',
        job_action => '"DEV"."DELETE_TEXT_TEST_LOG"',
        number_of_arguments => 0,
        start_date => TO_TIMESTAMP_TZ('2020-08-18 06:00:00.000000 ASIA/SINGAPORE','YYYY-MM-DD HH24:MI:SS.FF TZR'),
        repeat_interval => 'FREQ=DAILY;BYHOUR=23',
        end_date => NULL,
        enabled => FALSE,
        auto_drop => FALSE,
        commnets => 'DEV_LOG delete');
        dbms_scheduler.set_job_argument_value(job_name =>'DELETE_TEXT_TEST_LOG', argument_position => 1, argument_value => 5);
        dbms_scheduler.enable('DELETE_TEXT_TEST_LOG');

END;
/

Getting error when i run the above procedure:
ORA-06550: line 2, column 2:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

what is the issue , can someone help on this.


texts:
I need help with a dynamic pivot query. I would like to modify the column name (alias) but I can't get it done in the pivot. Please help. The query itself works perfectly but the username contains the domain\username format. I am trying to drop the "domain\" portion. I cannot control what is in or inserted in the database nor can I make any structure changes. I have tried "replace(x,'domain\','') but again, I can't get it to work in the pivot.

Here is what I have
data table:

requestID dateClosed              closedby
--------- ----------------------- -----------
1         2016-06-12 10:34:09.000 DOMAIN\Joe
2         2016-06-12 23:35:09.000 DOMAIN\Bill
3         2016-06-12 08:02:09.000 DOMAIN\Matt
4         2016-06-13 21:46:09.000 DOMAIN\Joe
5         2016-06-13 17:34:09.000 DOMAIN\Bill
6         2016-06-14 16:39:09.000 DOMAIN\Matt
7         2016-06-14 12:38:09.000 DOMAIN\Joe
8         2016-06-14 12:37:09.000 DOMAIN\Bill
9         2016-06-15 11:36:09.000 DOMAIN\Matt
10        2016-06-16 10:35:09.000 DOMAIN\Joe
11        2016-06-13 10:35:09.000 DOMAIN\Bill
12        2016-06-15 21:46:09.000 DOMAIN\Joe
13        2016-06-15 21:47:09.000 DOMAIN\Joe
14        2016-06-15 21:48:09.000 DOMAIN\Joe

Here is my ouput after PIVOT

date                    DOMAIN\Bill DOMAIN\Joe  DOMAIN\Matt
----------------------- ----------- ----------- -----------
2016-06-12 00:00:00.000 1           1           1
2016-06-13 00:00:00.000 2           1           0
2016-06-14 00:00:00.000 1           1           1
2016-06-15 00:00:00.000 0           3           1
2016-06-16 00:00:00.000 0           1           0

I need to remove the "Domain\" portion from the column alias. Replace() didn't work for me. SQl v2012

Here is my query

--dynamic pivot
declare @cols as nvarchar(max),
    @query as nvarchar(max)

set @colS = STUFF((SELECT DISTINCT ',' + QUOTENAME(r.closedby)
    from MYRequests r
    where r.dateclosed > getdate()-30
    for XML path(''), type
    ).value('.','NVARCHAR(MAX)'),1,1,'')

SET @QUERY = 'select * from (
    select
    DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) as [date],
    closedby
    from MyRequests
    where DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) > getdate()-30
    )
    as s
PIVOT
    (count(closedby) for [closedby] in
    ('+ @cols + '))
    as PVT '

execute(@query)

Here is sample filler data:

create table MYRequests
    ([requestID] smallint,
    [dateClosed] datetime,
    [closedby] varchar(50));

Insert into MyRequests
    ([requestID], [dateclosed], [closedby])
values
    (1,'20160612 10:34:09 AM','DOMAIN\Joe'),
    (2,'20160612 11:35:09 PM','DOMAIN\Bill'),
    (3,'20160612 08:02:09 AM','DOMAIN\Matt'),
    (4,'20160613 09:46:09 PM','DOMAIN\Joe'),
    (5,'20160613 05:34:09 PM','DOMAIN\Bill'),
    (6,'20160614 04:39:09 PM','DOMAIN\Matt'),
    (7,'20160614 12:38:09 PM','DOMAIN\Joe'),
    (8,'20160614 12:37:09 PM','DOMAIN\Bill'),
    (9,'20160615 11:36:09 AM','DOMAIN\Matt'),
    (10,'20160616 10:35:09 AM','DOMAIN\Joe'),
    (11,'20160613 10:35:09 AM','DOMAIN\Bill'),
    (12,'20160615 09:46:09 PM','DOMAIN\Joe'),
    (13,'20160615 09:47:09 PM','DOMAIN\Joe'),
    (14,'20160615 09:48:09 PM','DOMAIN\Joe')

    select * from myrequests

Please help.


texts: Multi-master + slaves replication in MySQL

texts:
I've been playing around with MySQL and tried to create replication topology according to the following picture. Slaves are used as read servers and Masters are used as both read and write servers.

The problem is that it does not work. If there is a write made on one of the masters, the other master does not redistribute the statements/rows to its slave.

What's the problem ? I'll be grateful for any suggestions :)


texts: Forums about getting SQL Server 2000 Administration help?

texts:
What I'm trying to accomplish is that I need to migrate the SQL Server 2000 installation into a  SQL Server 2008 installation, and I would like to say that I am afraid that after this operation I will be unable to use some of my database objects.

What is the best (in your opinion) forum about MS SQL Server 2000 administration?
Is it possible to ask hard questions and get professional answers at those forums you would recommend?


texts: What would be the correct entity-relationship diagram for this CSV file?

texts:
I received this from a customer and I was asked to model this as a database.

    Key Article number  color code  description price   action price    delivery    q1  size    color
    2800104 2   pants   Gaastra    8.00     1-3 working days    baby    104 gray
    00000002groe56  2   pants   Gaastra    8.00     1-3 working days    baby    56  green
    00000002groe62  2   pants   Gaastra    8.00     1-3 working days    baby    62  green
    00000002groe68  2   pants   Gaastra    8.00     1-3 working days    baby    68  green
    00000002groe74  2   pants   Gaastra    8.00     1-3 working days    baby    74  green
    00000002groe80  2   pants   Gaastra    8.00     1-3 working days    baby    80  green
    00000002groe86  2   pants   Gaastra    88.00        1-3 working days    baby    86  green

So far I detected a Color entity type, maybe a Category entity type (either q1attribute or Color Code attribute), and a Product entity type with attributes Key, Article Number, Description, Price and Action Price, maybe Delivery as well.

I would like to hear opinions if my analysis is correct or if I am missing something.


texts: Filtering special characters in to_tsquery

texts:
This question has been asked before here and here. I want to provide full-text support using postgreSQL via to_tsquery. However, since escaping characters leads to complications, I think it is okay to simply filtering special characters that could provoke errors.

What are the characters I need to remove to use to_tsquery safely. So far, I have identified the following:

Input that contains &, |, : might produce syntax errors
Input with * might not work as expected (to_tsquery('a*b') produces 'b')
quotes need to be escaped

What do you suggest in order to take user input in to_tsquery? What are other special characters I need to remove?


texts: How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)?

texts:
We have a keep-growing table which using MYISAM storage engine.

Almost every half year, this table will crash, and need to be repaired.

I checked the defragment result reported by windows defragment program, the most fragmental files are:

--------------------------------------------------------------------------------
碎片 文件大小 最零碎的文件
3,812 213 MB \MySQLData\data\cmszivr\calllog.MYI
24 541 MB \Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf
...

you can see, the index file caused 3812 fragment when it's 213 MB size. compared to SQL Server (24 fragment, 541 MB size), it's too bad.

I don't know if the fragmental issue caused mysql-server/mysql-table crashed, but it's must not be good for storage, and could be a potential reason to make server unstable.

So, is there a way to prevent it? (change config? change storage engine?)


texts: Got an error reading communication packets in MySQL hosted on Docker containers with master slave replication

texts:
I have set up a master and 2 slave MySQL databases in my docker-compose.yml. When starting, they start up fine, but when I run some operations I get the error:

  Got an error reading communication packets

in all 3 (master, slave1 and slave2)

Here are the .cnf files for all 3

master.cnf

[mysqld]
 server-id = 1
 log_bin = /var/log/mysql/mysql-bin.log
 binlog_do_db = common
 bind-address = 0.0.0.0
 character_set_server = utf8
 collation_server = utf8_general_ci

slave-1.cnf

[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_do_db = warehouse_b2b_common
replicate_do_db = warehouse_b2b_common
bind-address = 0.0.0.0
character_set_server = utf8
collation_server = utf8_general_ci

slave-2.cnf

[mysqld]
server-id = 3
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_do_db = warehouse_b2b_common
replicate_do_db = warehouse_b2b_common
bind-address = 0.0.0.0
character_set_server = utf8
collation_server = utf8_general_ci

What am I missing here?


texts: Insert new rows when item is added to another table SQL

texts:
I have to tables. One dimension table with projects and one fact table with projects, value and a period(month).
Looking to add 12 new rows pr project ID into my fact table, when a new project is added to the dimension table. The 12 rows should be the next 12 months. (always the month following the current)
The issue I have is how to write a SQL statement that I can use with a trigger to make this work. I have tried some versions on INSERT INTO, but I'm not experienced enough to know have to write this statement.
example: project 2 has been added to dim table.

project
status

1
A

2
B

Then create a trigger that adds these 12 new rows in my facttable

project
status
Period
Value

2
B
6/1/2021
NULL

2
B
7/1/2021
NULL

2
B
8/1/2021
NULL

2
B
9/1/2021
NULL

2
B
10/1/2021
NULL

2
B
11/1/2021
NULL

2
B
12/1/2021
NULL

2
B
1/1/2022
NULL

2
B
2/1/2022
NULL

2
B
3/1/2022
NULL

2
B
4/1/2022
NULL

2
B
5/1/2022
NULL

Any suggestions on how to write this statement?
Thank you so much!


texts: Why is the "The package can not be saved to the file system since its protection level is server storage" error?

texts:
When I execute

dtutil /sourceserver localhost /SQL "\Maintenance Plans\MaintenancePlan_daily" /copy file;.\MaintenancePlan_daily.dtsx

from the answer to my question "Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)?" I am getting the error:

  "The package \Maintenance Plans\MaintenancePlan_daily can not be saved
  to the file system since its protection level is server storage. Use
  the encrypt action to change the protection level"

What is the reason and how to btetter solve it?

MS SQL Server 2012 R2 Enterprise on Windows Server 2008 R2 Standard

Update:

I have dozens of SQL Servers 2012 R2 with equally set SQLServerAgent jobs using the "same" maintenance plans.
And this is kind of additional obstacle to using multiserver (target and master) administration, see my questions:

How to debug problems with SQL Server multiserver (Master and Target server) administration?
[Can maintenance plans be used in SQL Server multiserver (Master-Target servers) administration?][3]

I saw in my previous questions the recommendation to avoid using maintenance plans in SQL Server Agent jobs but so far I already have them (maintenance plans) set by others...

Let me reformulate the question:
Why don't I have this error in Windows 7 ?
What and where should I change in Windows Server 2008 R2 Standard (or SQL Server 2012 R2 Enterprise) in order to have the same absence of such error?

Update2:

Microsoft support article "SSIS package does not run when called from a SQL Server Agent job step" states:

"Change the SSIS Package ProtectionLevel property to ServerStorage. This setting stores the package in a SQL Server database and allows for access control through SQL Server database roles"

while I am using the sysadmin user-logins only.

Was it changed from SQL Server 2005-2008 to 2012?


texts: Prepare MySQL for big UPDATE query on production server

texts:
Surprisingly I have not found anything relevant on this subject.

What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details for a typical setup:

MySQL 5.6
80/20 read/write workflow ratio
Only InnoDB
Replication Master
Query Cache enabled
Data fits entirely into memory buffer pool with some spare room left
Healthy HDD drives on hardware RAID-1
Millions of rows in the update query
The query only covers single table (1 primary key - INT)

And the questions:

What to look for?
What mysql configuration parameters will be most relevant for the process to go quickly and smoothly?
Is it worth splitting the update into multiple batches?


texts: Multi values for SSRS parameter with Like statement

texts:
IN SSRS, I have a parameter named Comment which I set to Allow multiple values

and specified available values for it.

My problem is that if I select one value, I will get results but if I select more than one, I will get an error.

The problem is probably because I wrote my SQL statement like this:

dbo.tbl_Status.Comments LIKE ('%' + @Comment + '%')

NB:  I need to use LIKE and not IN


texts: Can a full DBCC CHECKDB operation be safely offloaded to a restored backup on a different server?

texts:
We are currently moving towards a 24/7/365 operating model - as such I am investigating our maintenance strategy.

One item I'm having trouble in figuring out is DBCC CHECKDB. I have seen numerous resources report that DBCC can be offloaded by performing a FULL backup, restoring that backup on a separate SQL instance, and running our CHECKDB on that restored copy. I've also found some resources that report this is not safe due to edge-case scenarios.

Is running CHECKDB safe if not run on the production database itself? If not, what parts are safe to run on a restored copy?


texts:
A client fired their DBA. He used OWM to add a wallet to their Oracle database and secure a few necessary columns of tables that they need. I can see the walled using:

orapki wallet display -wallet /opt/oracle/home/owm/root

I do not know the password. The ex-employee will not divulge the password. With root access to the system, is it possible to remove the wallet or change the wallet's password?

NOTE: Not part of this problem, but for reference... Oracle Support told the client they are out of luck. I told them they are out of luck. They want a third (and fourth (and fifth)) opinion.


texts: copy table comments from one MySQL database to another

texts:
I wrote column comments in MySQL database replica made for testing. Now I need to copy these comments to production database. Since comments are stored in table COLUMNS of information_schema database, and information_schema is not writeable, what would be the way to go to copy comments across?
Test database version is 5.5.35 while production database is 5.1.71.

Is there any way to import these comments in production database other than enter them again?

Thank you

Maciej


texts: Is it possible to set DEFAULT TRANSACTION ISOLATION for all connections in SQL Server?

texts:
I want the default Transaction Isolation level to be SNAPSHOT.

How do I set this on a SQL Server 2016 database?


texts: SQL Server Jobs - syspolicy_purge_history fails

texts:
The job syspolicy_purge_history fails at the 3rd step. The error that I get is the following one:

  Executed as user: UserABC.
   A job step received an error at line 1 in a PowerShell script.
   The corresponding line is 'set-executionpolicy RemoteSigned -scope process  -Force'.
   Correct the script and reschedule the job.
   The error information returned by PowerShell is: 'Security error.  '.   Process Exit Code -1.  The step failed.

Was wondering if you guys have faced this before and how to go about fixing this.


texts: Complete list of possible database "objects"?

texts:
When I finished my degree in Programming and Database I remember there being a general list of database "objects" like this:

data types (a list unto itself)
fields
records
tables
views (sort of like stored queries you can re-run)
relations between tables (foreign keys)
transactions (not really an object but something in the database)
connections
users
groups
permissions
triggers
stored procs

Have there been any additions to this list since then?


texts: Crossjoin-like operation except A x {} = A?

texts:
Is there a way to implement operation (let's annotate it ∘) that behaves just like regular cross join except for empty sets it returns non empty side.

∘ - new operation
x - cross join
{} - empty result set

A ∘ B  = A x B
A ∘ {} = A
{} ∘ B = B
{} ∘ {} = {}

Columns of A and B differ and are non empty.


texts: Execute stored procedure in another server from sql agent job

texts:
I am trying execute stored procedure in remote server in sql agent job using sqlserver 2008 r2

  select * from openquery([1.1.1.1],'exec [DB_Name].[dbo].[sp_ProcName]')

Or

EXEC [1.1.1.1].[DB_Name].[dbo].[sp_ProcName]

but I got the folowing error:

Could not find server '1.1.1.1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

(I type the correct IP)
Thanks!


texts: How to get MAX numeric value from a column with char values in MySql

texts:
I would like to know how can I get the max value only within the numeric values in a column with char values in a MySql database:

Supposing the column is as follows:

 col
---------
ALL
USD
USD
RET
RET
ACC
ACC
ALL
ALL
ERR
ERR
PRO
PRO
1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
AY
AY
C
C
CF

I should get 9 as a result of my query.

Thanks in advance.


texts: Unable to restore database in Oracle 10g

texts:
I created a database backup using the expdp command in Oracle 10g.  The backup appears to be created successfully.  However, while restoring using impdp command I get the following error message.

ORA-39083: Object type JOB failed to create with error: ORA-00001:
unique constraint (SYS.I_JOB_JOB) violated Failing sql is:  BEGIN
DBMS_JOB.ISUBMIT( JOB=> 4002, NEXT_DATE=> TO_DATE('2012-07-13
14:29:31', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'sysdate + 10/1440',
WHAT=>
 'wwv_flow_mail.push_queue(wwv_flow_platform.get_preference(''SMTP_HOST_ADDRESS''),wwv_flow_platform.get_preference(''SMTP_HOST_PORT''));',
 NO_PARSE=> TRUE); END;

 Processing object type
 DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Job
 "TMSMV"."SYS_IMPORT_FULL_01" completed with 1255 error(s) at 12:24:24

I used following commands:

expdp tmsmv/password full=y dumpfile=tmsdump20120113-558.dmp logfile=tmslog20120713-558.log

impdp tmsmv/password full=y dumpfile=tmsdump20120113-558.dmp logfile=tmslog20120713-558.log

How can I resolve this issue?


texts: Getting UserInfo data out of Sharepoint via OPENROWSET

texts:
The query below returns most of the data from the SharePoint list that my customers want:

    SELECT *
    FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
                     'WSS;IMEX=1;RetrieveIds=Yes;
                      User id=abc1;Password=xxxxxxx;
                      DATABASE=http://abcdefg/HijkLMno/;LIST=Whatever;',
                     'SELECT * FROM LIST') B

The issue is I need to join the local SharePoint User IDs from the UserInfo table.  How do I construct the DATABASE= string and use OPENROWSET to get the ID, names, etc...?

PS I must use OPENROWSET; Please do not suggest i query these tables directly as I cannot.


texts: Is Oracle Configuration Manager HIPAA compliant?

texts:
Does Oracle's Configuration Manager which according to Oracle provides a "40% faster issue resolution", violate HIPAA or require specific configuration in order to comply?

This question concerns databases that contain Protected Healthcare Information (PHI).  The questions scope only covers OCM and not the broader scope of Oracle support accessing the database.

The following from Oracle describes what OCM collects:

  Oracle Configuration Manager can automatically gather the
  configuration information of Oracle product installs, and upload this
  information onto Oracle’s support systems. The configuration
  information being collected by Oracle Configuration Manager includes:
  • Installed patches
  • Deployment platforms, dates, versions, and type
  • Deployed components and applications
  • Content of configuration files
  • Information about network configurations

  Note that the information collected by Oracle Configuration Manager is
  limited to configuration information. The utility does not collect
  sensitive data such as actual customer data (that is any data other
  than configuration information, including actual applications or
  database transactions), password hash values, log on events, etc. My
  Oracle Support note 728985.1 provides a list of all the data collected
  by Oracle Configuration Manager.

According to hss.gov the following information is protected by HIPAA:

  The Privacy Rule protects all "individually identifiable health
  information" held or transmitted by a covered entity or its business
  associate, in any form or media, whether electronic, paper, or oral.
  The Privacy Rule calls this information "protected health information
  (PHI)."12

  “Individually identifiable health information” is information,
  including demographic data, that relates to:

  • the individual’s past, present or future physical or mental health or condition,
  • the provision of health care to the individual, or
  • the past, present, or future payment for the provision of health care to the individual,

  and that identifies the individual or for which there is a reasonable
  basis to believe it can be used to identify the individual.13
  Individually identifiable health information includes many common
  identifiers (e.g., name, address, birth date, Social Security Number).


texts: Oracle query slow while all data has returned to client

texts:
We have a oracle 11g database
when execute a query with pagination, sometimes it take 170+ seconds to finish.
here is the query:
SELECT
    CUST_ID,
    CUST_ID CUST_NO,
    CASE
        WHEN HO.LEADERFLAG='1'
        THEN '*'||SUBSTR(HO.CUST_NAME,2)||'(行长回收)'
        ELSE '*'||SUBSTR(HO.CUST_NAME,2)
    END CUST_NAME,
    CUST_TYPE,
    '1' MAN_MAIN,
    '10'   MAN_MAIN_PAR,
    CASE
        WHEN HO.ATTENTION_FLAG = '1'
        THEN '1'
        ELSE '0'
    END ATTENTION_FLAG,
    USER_ID,
    FUN_USER_NAME(USER_ID) USER_ID_NAME,
    ORG_FOR,
    FUN_ORG_NAME(ORG_FOR) ORG_FOR_NAME,
    CUST_LVL,
    CUST_LIFECYC,
    FUN_PAR_P_NAME('42000', CUST_LIFECYC) CUST_LIFECYC_NAME,
    CREDIT_P_FLAG,
    FUN_PAR_P_NAME('40000', CREDIT_P_FLAG) CREDIT_P_FLAG_NAME,
    DEPOSIT,
    CUST_IDT_NO,
    FUN_PAR_P_NAME('12000', CUST_IDT )  CUST_IDT,
    FUN_PAR_P_NAME('13000', CUST_ENDU ) CUST_ENDU,
    FUN_PAR_P_NAME('14000', CUST_MARR ) CUST_MARR,
    FUN_PAR_P_NAME('19000', CUST_VOCA ) CUST_VOCA,
    FUN_PAR_P_NAME('11000', CUST_GEND ) CUST_GEND,
    CUST_MOBI,
    CUST_HOME_ADD,
    NVL(FINANCIAL,0)       AS FINANCIAL,
    NVL(CUST_LVL_IN_ALL,0) AS CUST_LVL_IN_ALL,
    NVL(FINANCIAL_YEAR,0)  AS FINANCIAL_YEAR,
    NVL(DEPOSIT_YEAR,0)    AS DEPOSIT_YEAR,
    NVL(PROVIDE_YEAR,0)    AS PROVIDE_YEAR,
    NVL(FINANCIAL_MONTH,0) AS FINANCIAL_MONTH,
    NVL(DEPOSIT_MONTH,0)   AS DEPOSIT_MONTH,
    NVL(DEPOSIT_DAY,0)     AS DEPOSIT_DAY,
    ROW_,
    (
    CASE
        WHEN CUST_TYPE = '1'
        THEN FUN_PAR_P_NAME('200', CUST_DEPART)
        ELSE FUN_PAR_P_NAME('100', CUST_DEPART)
    END) AS CUST_DEPART
FROM
    (   SELECT
            ROWNUM ROW_,
            ho.*
        FROM
            cust_merge_info HO
        WHERE
            1 = 1
        AND man_main = '1'
        AND NOT EXISTS
            (   SELECT
                    CUST_ID
                FROM
                    CUST_MAN_TEM CT
                WHERE
                    (
                        CT.STATUS = '10'
                    OR  CT.STATUS = '20')
                AND CUST_ID = HO.CUST_ID

                UNION ALL

                SELECT
                    CUST_ID
                FROM
                    CUST_UTD_CHK CU
                WHERE
                    CU.CHK_STATUS = '10'
                AND CUST_ID = HO.CUST_ID)
        AND NODE_PATH LIKE '%,00999,%'
        AND HO.USER_ID IS NOT NULL
        AND HO.USER_ID != '00468'
        AND USER_ID !='GZ'||'00468'
        AND USER_ID !=SUBSTR('00468',3)
        AND HO.CUST_LVL= '03'
        AND HO.CUST_TYPE= '1' ) ho
WHERE
    1=1
AND ROW_ >= 1000+1
AND ROW_ <= 2001-1

when I test it in plsql, I find that even all 1000 records has been returned to client, the plsql shows that statement's execution is not finished(the red lightning sign is still on)， after almost 177 seconds, it finished.
Any wrong configuration can cause such problem?  please help


texts: incorrect Estimated Number of Rows vs Actual number of rows

texts: I'm seeing a huge difference in the estimated number of rows vs actual number of rows.  I'm not sure if i'm reading this right, so does this mean that sql server estimated the number of rows returned from this index is 16, but the actual number of rows being returned is 565824?  if so, then is this a concern?  This is for SQL server 2016


texts: How to make a unique column with default value of UUID

texts: I have a key column for internal use which is just a increasing integer, but I would like to have a second unique column which is a UUID, but I don't know how to have a function called for the default value (so that SQL Server is creating the UUID and not java), is there any documentation that one could suggest to me for this?


texts:
I have tables A, B and C where there is a one-to-many relationship between B and A and B and C respectively.

I need a constraint that for every row of C rowC.propertyC is element of

SELECT A.propertyA
 FROM A, B, C
 WHERE A.id_b = B.id AND B.id = rowC.id_b

Now obviously I cannot define a constraint FOREIGN KEY(propertyC) REFERENCES A(propertyA) because the link with table B is missing.

Could introducing a fourth table that contains valid combinations of B.id and propertyA and adding that as a foreign key constraint to C be a solution maybe?

I am really not sure what to search for, pointers in any direction would be much appreciated!


texts: create_time in sys.dm_xe_sessions is in the future

texts:
I have run the following query which shows the running XE sessions on the server:

SELECT  name,
        create_time
FROM    sys.dm_xe_sessions

and I get the results

+-------------------------------+-------------------------+
|             name              |       create_time       |
+-------------------------------+-------------------------+
| hkenginexesession             | 2020-01-04 06:39:14.240 |
| system_health                 | 2020-01-04 06:39:14.313 |
| Deadlocks                     | 2020-01-04 06:39:14.483 |
| sp_server_diagnostics session | 2020-01-04 06:39:14.583 |
| Blocked Processes             | 2019-11-20 14:27:49.593 |
+-------------------------------+-------------------------+

Note that the first four sessions, 3 of which are system, 1 of which is user created (Deadlocks) have a create_time in the future.

I can verify the system time is correct doing a simple

SELECT GETDATE()

The Blocked Processes xe session has been created since the last server restart (which was 2019-11-15 13:36:35.287)

What could have caused this?

SQL Server Version is

Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)   Jul 20 2019 21:42:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


texts: Database Normalization Causing Inefficiency

texts:
Note: I am relatively new to managing an SQL database. I have done my fair share of reading, research, and experimentation, but it is possible that I am missing some fairly basic knowledge. I'm also not sure how to tag this question so if someone could add relevant tags that would be appreciated.

I am currently building an SQL database for a forum like website. Among other things, the website will contain pages with threads, basically a series of posts by different users.

Users on the website are identified by a unique ID (char(36)) that is set once at registration and cannot be changed afterward. The user also has a username (varchar(16)) that is just a screen name. This name is almost always unique between users, but not necessarily. The user can also change the name, but there is a hard limit of two weeks between any two username changes. All of this information, and a few other things like login details, are stored in one Users table with the ID as a primary key.

There are multiple tables for the forum itself, but the one that is important for this question is the Posts table (primary key is 'post_id' int(10) NOT NUll AUTO_INCREMENT PRIMARY KEY. It contains one row for every time somebody makes a post on a thread, including responses to existing posts. As a result, whenever a user requests a thread, I query the posts table for all posts matching that thread (I store a thread ID foreign key). My question now concerns storing a user who is the author of the post. When a thread is requested, I need to send the username (not ID) of the users that made different posts. However, I also need to make sure that changes in usernames do not cause the apparent author of a post to change. As far as I can tell I have two possibilities:

For storing the author of a post, I can store only the ID. However, this means that I will have to make quite a few requests to the Users table for the usernames of the various IDs of the posters. This seems highly inefficient, seeing that the usernames will change very rarely.
I can store both the author ID and the author username in the Posts table. I can then go through the table and change all usernames among the posts (I can even set this up with a trigger, I believe). However, this opens the window for update anomalies, if the username stored for an ID in the Users table is different than in one of the posts. I can still use a trigger to prevent these anomalies at the database level, but from what I understand this should not be necessary in a well-designed database.

Which one of these two solutions (or something entirely different) should I use to both maintain efficiency and database normalization?

If it matters, I'm using the most recent version of MariaDB and accessing using PHP.


texts: How to renew or extend the CMK Certificate in Always Encrypted feature?

texts: I have implemented Always Encrypted feature in SQL Server 2016. The CMK is valid for one year. How do I renew it, or extend the validity?


texts: Visual Explain Not working in MySQL Workbench

texts:
Is there some trick to getting the Visual Explain feature to work in MySQL Workbench?

I am running version 6.3.6 on Windows 7.
I've tried simple queries with just one join. I've tried it with complex queries with 12 Joins.
I've tried it with MySQL 5.5 and 5.7.

But every time I just get Explain Data Not Available For Statement

I've looked on the Bugs for MySQL Workbench > Visual Explain, but there is no recent bugs, making me think it is something I am doing.
But I can't see what. Does anyone have some suggestions?


texts: how to get the information about sleeping processes that are causing blocking?

texts:
I have a procedure that allows me to see what is currently running in my sql server.

This procedure does not use sys.sysprocess because it has been deprecated.

However, I am struggling to find needed information about sleeping processes that are causing blocking.

This is because the DMV sys.dm_exec_requests does not hold any request of sleeping processes.

I don't want to use sp_whoisactive or any monitoring tool.
sp_whoisactive uses sys.sysprocesses.

I have my session 81:

  BEGIN TRANSACTION T1

  SELECT @@TRANCOUNT

  update
  [TableBackups].[dbo].[spstats]
  set execution_count  = 1000
  where dbname = 'master'

  select @@spid

and I have my session 51:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT TOP 1000 *
  FROM [TableBackups].[dbo].[spstats]
  WITH (HOLDLOCK)

These 2 sessions above are running at the same time and consequently, session 81 is blocking the session 51.

I have this select below, that get information about the session 81,
however, I could not get teh dbid, and I am not sure about the Open_transactions and the whole waits information.

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0)
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0)
    + COALESCE(es.writes,0)
    + COALESCE(er.reads,0)
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
where es.is_user_process = 1
  and es.session_id <> @@spid

The question is,
is there any DMV, other than sys.sysprocesses that I can use to get this info?


texts: How to organize MySQL DB to allow an arbitrary counter for clicks, visits, etc in past 24 hours?

texts:
If you're familiar with it, I used to use count.io which has been permanently taken down.   I basically want to create my own version for my own use.

Main question

How do I structure/organize my MySQL DB to allow to not only count page visits, but to also be able to tell how many of them are from the past 24 hours?

What I am trying to acheive

The way it'd work is, every time a user hits some page X, a "hit" is recorded.
The count.io API allowed me to record a hit with JavaScript, like this:

// Timestamp
var timestamp = Math.floor(Date.now());

// Grab product ID
var productNumber = $('#productDetails input[name=product_id]').attr('value');

// Record a hit to the DB under Product[ID]-group, as a timestamp
$.ajax({
    url:'http://count.io/vb/product' + productNumber + '/' + timestamp + '+',
    type: 'POST'
});

...I am not sure how the count.io DB was structured to allow this, but essentially, the count.io DB would receive/store my hits like this:

    - http://count.io/vb/product37/1468038731
    - http://count.io/vb/product37/1468048798
    - http://count.io/vb/product37/1468058931
    - http://count.io/vb/product37/1468069731
    ... potentially hundreds or thousands of such entries for any 1 product

When I'd retrieve it with JavaScript, I was then able to count how many timestamps there were within the week by doing something like this:

// Retrieve current product array
$.post(
    // 24 hour old timestamp
    var dayAgo = timestamp - 86400000;
    httpPrefix + 'http://count.io/vb/product' + productNumber,
    function (data) {
        var timedViewCounter = 0;
        var myStringArray = data.counts;
        var arrayLength = myStringArray.length;
        // Check each "timestamped hit" to see if it's older than 24 hours
        for (var i = 0; i < arrayLength; i++) {
            var itemTimestamp = data.counts[i].item;
            // Count up all visits that are 24 hours old or newer
            if (itemTimestamp >= dayAgo) {
                timedViewCounter++;
            }
        }
        // Show total count in past 24 hours
        var hotItem = '' + timedViewCounter + ' people have viewed this product in the last 24 hours
';
});

Question

How would I organize a MySQL DB that would allow this kind of functionality?  Would I create a new table for each page/product?  (that sounds crazy as there are over 1,500 relevant items)

OR

Would I just record the "timestamp hits" into the same field, and simply separate the values with a comma?

OR

Is there some magical functionality of MySQL that I am not aware of?

What count.io did

  Incrementing

http://count.io/vb/fruit/apples+

  Notice the + sign on the end of the URL.

  Returns this JSON:

{
   "item":"apples",
   "count":1,
   "group":"fruit"
}

  Use the "group" field to tie similar themed counts together. In this
  case it's "fruit."

  If you call the URL again, you'll notice the count is incremented by
  1.

{
   "item":"apples",
   "count":2,
   "group":"fruit"
}

  Get Count

http://count.io/vb/fruit/apples/

  Get Group Sum

http://count.io/vb/fruit/

  Returns this JSON::

{
   "group":"fruit",
   "sum":7,
   "counts":[
      {
         "item":"apples",
         "count":2
      },
      {
         "item":"oranges",
         "count":1
      },
      {
         "item":"bananas",
         "count":4
      }
   ]
}

The way I used it, is instead of "fruits", I'd have the group as the PorudctID and the items inside it were al unique timestamps with a count of 1.  I'd then run through all the timestamp items and see which are from within past 24 hours and that way, Id have the count of visitors in past 24 hours.

I want to recreate this functionality on my own server but have no idea how to structure the queries.   I'm leaning towards putting all timestamps into the same field but separated by commas... ?