texts: LDAP Authentication - SQL Server 2017 on Linux

texts:
We have installed SQL Server 2017 on Linux server. I am able to connect SQL Server locally as well remotely but with local user access.

I have my AD account created and have AD server information. Can you please help me how can I connect using my AD account to SQL Server 2017 on Linux remotely? I heard something about LDAP Authentication. Is it possible to use this concept without adding Linux server to AD domain? Our security team does not allow to add Linux server in Active Directory group.


texts: Why is INSERT INTO ... SELECT not copying all rows?

texts:
I have two servers, SERVER-01 and SERVER-00 (not their real names). SERVER-00 is a SQL Server 2005 Standard instance, SERVER-01 is a SQL Server 2014 Standard instance. I have this query that runs in a SQL Agent Job every night on SERVER-00:

truncate table DataWarehouse.dbo.documents
set identity_insert DataWarehouse.dbo.documents ON

INSERT INTO [DataWarehouse].[dbo].[documents]
    (
     [documentID]
    ,[poNumber]
    ,[soldTo]
    ,[shipTo]
    )
SELECT
     [documentID]
    ,[poNumber]
    ,[soldTo]
    ,[shipTo]
FROM [server-01].[DataWarehouse].[dbo].[documents]

set identity_insert DataWarehouse.dbo.documents OFF

(The actual query includes more columns, I've trimmed it for readability.)

On SERVER-01, documents is a view. On SERVER-00, documents is a table. In this query [server-01] is a linked server connection on SERVER-00 (it uses the credentials of a sysadmin to connect to SERVER-01).

Only about half the rows are being INSERTed into documents on SERVER-00. There are no error messages or warnings being logged by the job - it always succeeds. How on earth can this be happening?


texts: Error when trying to apply Always Encrypted encryption on aspnet_Membership table

texts:
I'm trying to encrypt the Email and LoweredEmail columns on a SQL database using Always Encrypted encryption using SSMS and I always get the same error:

Lock request time out period exceeded. There is no user table matching the input name '[dbo].[aspnet_Membership]' in the current database or you do not have permission to access the table.
I still get the same error when I log in as the SA account, sio I guess that it's not a permissions problem.

EDIT *** Added the PowerShell Script
Generated by SQL Server Management Studio at 10:47 on 06/05/2021
Import-Module SqlServer
Set up connection and database SMO objects
$password = ""
$sqlConnectionString = "Data Source=GALADRIEL\SKYLINE;Initial Catalog=SkylineMembership;User ID=sa;Password=$password;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio""
$smoDatabase = Get-SqlDatabase -ConnectionString $sqlConnectionString
If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:
* Prompt for a username and password:
#Add-SqlAzureAuthenticationContext -Interactive
* Enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '' -Secret '' -Tenant ''
Change encryption schema
$encryptionChanges = @()
Add changes for table [dbo].[aspnet_Membership]
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.aspnet_Membership.Email -EncryptionType Deterministic -EncryptionKey "CEK_Auto1"
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.aspnet_Membership.LoweredEmail -EncryptionType Deterministic -EncryptionKey "CEK_Auto1"
Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase


texts:
I'm running SQL Server 2008 R2 SP1, on a Windows Server 2008 box. I have a .NET script running from Visual Studio 2010 that does the following:

Reaches into the database
Makes a change
Iterates

The total number of times it will iterate is 150, however it is stopping at 100 connections and I can't figure out why. I could adjust my script to just use a single thread, but I'd prefer to know where I'm missing a max connection setting as that will be more useful to know for future reference.

Here's where I've checked so far:

SQL Connection String in Visual Studio 2010 (it's set to 1000)
SSMS Database instance connection properties (it's set to 0 [infinity] user connections)
Googled some information on Server 2008, it looks like it can handle more than 100 connections
Stepped through my code alongside SP_WHO2 which gives more information on logical connections, seeing that the # of connections starts at 52 and the script errors with the "Max Pooled Connections reached" error at 152 logical connections.
Changed the connection string to use Data Source=PerfSQL02;Initial Catalog=Masked;Integrated Security=True;Max Pool Size=1000

I'm not sure where else to check, I know I have a lot of moving parts here but I'm getting the feeling I'm just missing a max pool setting somewhere.


texts:
Design Question- During data insertion, if MongoDB failed, can I try multiple retries? I have read somewhere it will retry only one time.

What will happen if retry fail?
Can I keep the failed record somewhere in server memory?


texts: Different datatypes different execution plans bitmap scan vs index scan POSTGRESQL

texts:
I am currently working on a small presentation showing when postgresql will switch from index scan to bitmap scan to sequence scan.
First example (hint all index scan…):
Loosely based on a similar example of Schönig in Mastering Postgres by Packt.
create table t_test (id serial, name text);

-- add data
insert into t_test (name) select 'hans' from generate_series(1, 5000000);
insert into t_test (name) select 'paul' from generate_series(1, 5000000);

CREATE INDEX idx_x ON t_test(id);
analyze t_test;

--Index Scan using idx_x on t_test  (cost=0.43..1431.45 rows=42058 width=9)
explain SELECT * FROM t_test WHERE id = 42353;

-- Index Scan using idx_x on t_test  (cost=0.43..1431.45 rows=42058 width=9)
explain SELECT * FROM t_test WHERE id < 42353;

-- Index Scan using idx_x on t_test  (cost=0.43..21.77 rows=419 width=9)
explain SELECT * FROM t_test WHERE id < 423;

So this example is not very good in showing when postgres will switch from index scan to bitmap scan.
explain SELECT * FROM t_test WHERE id < 6002353;
At around 6 mio retrieved lines pg will switch do seq scan.
Second example:
drop table if exists t_test;

create table t_test (id numeric, name text);

-- add data
insert into t_test (name, id) select 'hans', random() * 10000 from generate_series(1, 5000000);
insert into t_test (name, id) select 'paul', random() * 10000 from generate_series(1, 5000000);

CREATE INDEX idx_x ON t_test(id);
analyze t_test;

-- Index Scan using idx_x on t_test  (cost=0.43..8.45 rows=1 width=16)
explain SELECT * FROM t_test WHERE id = 42353;

-- Seq Scan on t_test  (cost=0.00..179078.41 rows=9999873 width=16)
explain SELECT * FROM t_test WHERE id < 42353;

-- Bitmap Heap Scan on t_test  (cost=9955.65..69408.57 rows=429834 width=16)
explain SELECT * FROM t_test WHERE id < 423;

So this example is exactly what I wanted. But why the difference?
The example is almost identical. But I had to change the id.


texts: Storing no of players the last seven days in an online game

texts:
I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how to store and update this information in an efficient way.

The easiest (but probably not so efficient) way I can think of is just having three columns, "time_fetched", "server", "no_players". Every 15 minutes it inserts 50 new rows and deletes the 50 oldest rows.

Maybe I could store weekday, hours and minute instead? So instead of deleting and inserting rows it just updates the correct rows? This would mean that I always have a fixed number of rows in the table (50 * 4 * 24 * 7 = 33600).

Also; would it be more efficient to store the server name as an enum instead of just strings (or some other way, maybe one table for each server)?


texts: Ola Hallengren scripts: after a job failure, how to skip the databases that worked and backup only the remaining ones?

texts:
In our organization we have more than 150 databases in one instance and full backup should run for every day.
Our backup script should be in such a way that if backup job fails while taking 100th database and if I rerun the job again, it should start from the failed database(100th) and exclude the backups which are already taken and the script should take the backup of the remaining databases which are not backuped up for the day. I think the current backup script won't serve this purpose.


texts: Separate differential backup from one restored baseline

texts:
I work on a team that customises software, part of this customisation is stored as records in a database, part is customisations to the schema itself and there are often some specific records in the database too.

I'd like to create a baseline version of the database and back it up normally. Then for each customer I restore the baseline database under the customer's name and customise it as usual. I'd then like to do a differential backup of the database from the baseline point to the current point and shelve the differential backup for that customer, I expect about 0.1-0.2% changed data. When I need to do more work for that customer I restore the baseline backup and their differential backup, do the work then backup again, replacing the shelved baseline backup.

This is similar to Differential databases (not backups) but using backups.

Can I have different differential backups that can be restored against a single baseline?

Can I do a new differential backup from the same starting point or would I need to do incremental backups.

Any problems or experiences with this sort of approach?


texts: Cannot create SSRS subscription for a report using SSAS as datasource

texts:
My questions is : how should I config SSRS subscription a report, which consumes SSAS datasource.
I already googled and lookup stackoverflow a lot and still couldn't make it work.

Problems:
I developed and deployed a SSRS report to the server, and next I need to make subscription for that report to send emails.

And the report is using SSAS as datasource.
I learnt that SSAS required to use windows credentials and I already setup
the datasource .

However, when I attempted to click [New subscription], it alerts:


texts: MySQL 8.0 Replication filters lost between restarts

texts:
I don't know if I fell on a bug or a feature, I read the documentation several times but haven't found the information.

I have two MySQL servers, both on 8.0.15 and hosted on a Windows Server (x64). I'll name these SRV1 and SRV2.

Out of 5 databases on SRV1, I want to replicate only 3 of them on SRV2.

I setup the replication filter with the following query, made to ignore the two remaining databases, so that in the future the replication process will pickup any new database created.

CHANGE REPLICATION FILTER
     REPLICATE_IGNORE_DB = (ignore1,ignored2);

The replication process worked as expected and I saw the three databases created and fully replicated on SRV2. So far so good.

The result of SHOW SLAVE STATUS \G; was correct, showing

[...]
    Replicate_Do_DB:
Replicate_Ignore_DB: ignore1,ignore2
[...]

But a wild Windows Update appeared. And made SRV2 restart during the night. I detected today that the replication process stopped because the replication user tried to do a query on ignore1.

One more SHOW SLAVE STATUS \G; confirmed it, my replication filters are not here anymore.

[...]
    Replicate_Do_DB:
Replicate_Ignore_DB:
[...]

Wanting to troubleshooting it, I put again my replication filters, restarted the slave process, waited that Seconds_Behind_Master went to 0. I then asked Windows to stop the MySQL service, and start it again, as it would do on a normal OS restart.

Once again the replication filters got lost, and the slave process stopped because it's trying to replicate a database that doesn't exist on SRV2

Before going on 8.0.15, I went on 5.7 during 3 years with the exact same setup, but never encountered a single issue like that during server restarts. Is it something intended (that I would have missed in the 8.0 release notes?) or a bug in my configuration?

I know that I can fix it by adding the --replicate-ignore-db argument to the MySQL service binary path, but I still want to know if there is another way.


texts: Risks of Switching Between Simple & Full Recovery in SQL Server 2005

texts:
Ok, so we have several instances on several nodes in a clustered environment. One of them is for JD Edwards stuff and has had an issue lately with a job failing because there was not space in the tempdb to complete it. The code for this job hasn't changed in YEARS, and yet I realize the data size may have.

Anyway, after setting the tempdb to some Autogrowth that wouldn't bork that drive I started digging deeper on this install. It seems there are some scheduled jobs that are switching the recovery mode back and forth from Simple to Full with backups supposed to be happening around those for some reason. I believe this is what caused the T-Logs backup to fail at some point and thus not have enough room for the tempdb itself to complete these long-standing JDE jobs.

So questions:

Am I totally ignorant to think that switching back and forth from Simple to Full is a bad idea on a regular basis?
If I am right that this is a bad idea what other scenarios other than the one I have already described can I use to illustrate this to my Sr. DBA who must have created this situation?

TIA


texts: Selecting Records from a duplicate column whose value in another column is less than

texts:
Below is an example of my table:

  Column A         Column B       Column C
    Tom              Blue           10
    Tom              Orange         12
    Alex             Blue           30
    Alex             Orange         29
    Lisa             Orange         15
    Lisa             Blue           14

Output:

Column A      Blue        Orange
 Tom           10           12
 Lisa          14           15

The output above returns when there are two duplicate values in Column A, Column B Blue value in Column C is less than Column B Orange value in Column C.

So far I have... select Column A, Column B as "Blue", Column B as "Orange" from table where Blue < Orange;. Obviously where Blue < Orange doesn't work but that's the logic.

I think I need to write a subquery but I cannot figure this out.


texts: what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again?

texts:
what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again ?

I know the below points exist means, there won't be any reset happen in binlogs.

I would like to know whats the maximum value for the bin-log files.
In case, the expire_log_days not provided, then it should not replace the first file, throws 'file-exist' error.  The logs will be kept forever unless you specify an expiry.
If the binary log master  index files having the entry, I don't think it will replace.

Then what will happen eventually?


texts: SQL Always on High Availabity Groups Multi Subnet

texts:
SQL 2017 Standard in SQL always on two node Basic AG.
There are two DNS records for the listener with the different subnet IP's
The servers are across subnets and we have hit issues with an application seeing to randomly time out when talking to the database, other times it responds in milliseconds. After running Wireshark we can see the application is trying to talk to the database via the failover partner listener IP address, the none readable replica.
This information I have read explains the 21 second timeout.
https://techcommunity.microsoft.com/t5/sql-server-support/improved-multisubnet-listener-behavior-with-newly-released-sql/ba-p/318664
MultiSubnetFailover=TRUE seemed like the resolution but the application is using an older version of .NET is there anything else I can do to resolve or work around this?


texts: Is there any command to optimize all MySQL tables one by one automatically?

texts:
I execute below command line statement to optimize a table:
optimize table tablename;

Is there any command or statement which can optimize all the tables one by one of the selected database?


texts: How to index a query with `WHERE field IS NULL`?

texts:
I have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, setting uploaded_at to current date.

How should I index the table?

I understand that I should use a partial index like:

CREATE INDEX foo ON table (uploaded_at) WHERE uploaded_at IS NULL

Or smth like that. I'm a bit confused though if it is correct to index on a field that is always NULL. Or if it is correct to use a b-tree index. Hash looks like a better idea, but it is obsolete and is not replicated via streaming hot-standby replication. Any advice would be greatly appreciated.

I've experimented a bit with the following indices:

"foo_part" btree (uploaded_at) WHERE uploaded_at IS NULL
"foo_part_id" btree (id) WHERE uploaded_at IS NULL

and the query planer seems to always choose the foo_part index. explain analyse also yields slightly better result for the foo_part index:

Index Scan using foo_part on t1  (cost=0.28..297.25 rows=4433 width=16) (actual time=0.025..3.649 rows=4351 loops=1)
   Index Cond: (uploaded_at IS NULL)
 Total runtime: 4.060 ms

vs

Bitmap Heap Scan on t1  (cost=79.15..6722.83 rows=4433 width=16) (actual time=1.032..4.717 rows=4351 loops=1)
   Recheck Cond: (uploaded_at IS NULL)
   ->  Bitmap Index Scan on foo_part_id  (cost=0.00..78.04 rows=4433 width=0) (actual time=0.649..0.649 rows=4351 loops=1)
 Total runtime: 5.131 ms


texts:
I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server).
The orders from the website are inserted into the Master Mysql database and which are replicated into the Slave Mysql database. These orders need to be pushed to the Oracle Db. I have arrived on 4 methods to do this.

Use mysql UDF for http communication that would send the rows on a Insert Trigger on the slave to the Oracle webservices on Oracle server

Use cron jobs(with a low interval may be 5 mins) with Php script that would get the new orders from mysql and send to the Oracle db via Oracle services/Php services on Oracle hosted server.

Use sys_exec() udf to invoke php script that inserts into Oracle db

Use memcached with MySql and let Php poll the memcached to retrieve data and send it to Oracle server, but unsure whether we could migrate existing Mysql version to new version Mysql 5.6

I already have the UDF's in place and tested them, they are good to go. but still in dilemma regarding data integrity and reliability in case of using UDF's with triggers.
Is there a better method for doing this. Or else which method shall I follow to do the same.
I am aware of the security threats of UDF's, you can't restrict it to any user
One more thing I am not allowed to introduce new changes to the existing website php code for the operation.


texts: Database Mirroring login attempt failed with error: 'Connection handshake failed. The handshake verification failed. State 36.'

texts:
While performing a rolling upgrade of an Availability Group from SQL Server 2016 CU1 to 2016 SP1 we noticed an issue. Immediately after we updated the secondary replica replication stopped working for that node and the SQL logs indicated there was an issue with the Database Mirroring login:

  Database Mirroring login attempt by user 'DOMAIN\SERVICEACCOUNT.'
  failed with error: 'Connection handshake failed. The handshake
  verification failed. State 36.'. [CLIENT: 10.1.2.3]

What causes this issue and how can we resolve it?


texts: Installing SQL Server with Virtual Account / (managed local accounts)

texts:
I am installing SQL Server 2016 by using virtual accounts (which I suppose is managed local accounts) and I cannot see any limitation, drawback of using this simpler method vs. using MSAs.

Would a more experience DBA educate us?

My understanding is that: as long as you do not have any cross-server operations, you are fine. I will not cross servers, but I will cross SQL SERVER INSTANCES (on the same server). Are there any issues that I may face?

I've read these resources and another similar question.


texts: Database version is higher than SSMS and throwing error when attempted to create table

texts:
I have installed VS 2017. After that I have installed SQL Server 2014.

Whenever I create a DB using SSMS and attempt to create a table it returns the following error:

  This backend version is not supported to design database diagrams or
  tables. (MS Visual Database Tools).

When creating the database using SSMS the compatibility level has no value to select from.

Version of the SQL Server is 2017 and version of SSMS is 2014. How do I resolve this?

As there any way to use SQL Server 2017 with SSMS 2014?


texts:
I have to link all the books in series together but I'm not sure.

I have a SERIES_ID under which there is a name for a series, author, number of books and number book in the series.

I'm not sure how to have a list of all the other books in a series or at least previous and next book.

So if I had a copy of the 2nd book in a trilogy the first and third book would appear.

Any advice?


texts: Copy table with separate sequences for serial columns

texts:
While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool that I ultimately intend to use on the table parts I created this new tool to duplicate parts so I'd end up with a parts1 table. When I thought I had finally gotten all the issues ironed out I encountered the following error when the column tool deletes the table:

  ERROR: cannot drop table parts because other objects depend on it
  DETAIL: default for table parts1 column id depends on sequence
  parts_id_seq1

I've spent the greater part of my day working towards this solution so in short can I simply use string functions to rename the SEQUENCE_NAME variable to disassociate the parts table from the parts table or is it a more convoluted issue than that? Here is the query:

DO $$
  DECLARE
    SEQUENCE_NAME VARCHAR;
  BEGIN
    SELECT s.relname INTO SEQUENCE_NAME
    FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid
    INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid
    INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
    INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace
    WHERE s.relkind = 'S'
    AND n.nspname = 'public'
    AND t.relname='parts';

    LOCK TABLE parts;
    CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
    INSERT INTO parts1 SELECT * FROM parts;
    PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
  END;
$$ LANGUAGE plpgsql;


texts: 70-761 Practice exam Question about @@Trancount final value

texts:
There is this question in the practice 70-761 exam that states after executing the following script, what is the value of @@Trancount?

Exam script:

CREATE PROCEDURE dbo.up_CreateSalesInvoice
(
    @Date DATE,
    @CustomerID INT,
    @StockitemId INT,
    @Quantity INT,
    @UnitPrice DECIMAL(8, 2),
    @InvoiceID INT OUT
)
AS
BEGIN
    DECLARE @RETVAL INT;
    BEGIN TRANSACTION;
    BEGIN TRY

        BEGIN TRANSACTION;
        INSERT INTO dbo.SalesInvoice(InvoiceDate, CustomerID)
            VALUES (@Date, @CustomerID);
        SET @InvoiceID = SCOPE_IDENTITY();
        COMMIT TRANSACTION;

        BEGIN TRANSACTION;
        INSERT INTO dbo.SalesInvoiceLine(InvoiceID, StockItemID, Quantity, UnitPrice)
            VALUES (@InvoiceID, @StockItemID, @Quantity, @UnitPrice);
        COMMIT TRANSACTION;

        COMMIT TRANSACTION;
        SET @RETVAL = 0;
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @RETVAL = 1;
    END CATCH

    RETURN @RETVAL;
END;

The answer I came into conclusion is 0, but the expected answer is 1, so i moved to replicate the script behavior with the following script, and the result returned from @@Trancount is 0, unless my script is doing something different with the nested transactions if anyone can explain if the expected answer is wrong or if my logic emulating the nested transaction of the script i got something wrong witch gives me a different result

Script emulating the behavior:

CREATE TABLE #TBL(
    ID INT,
    TANK VARCHAR(12)
)

INSERT INTO #TBL VALUES (1, 'T-55-MA')

PRINT 'TRANCOUNT INICIAL :: ' +  CAST(@@TRANCOUNT AS VARCHAR)

BEGIN TRANSACTION  -- +1
PRINT 'BEGIN TRANSACTION 1 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
BEGIN TRY
    BEGIN TRANSACTION  -- +1
    PRINT 'NESTED BEGIN TRANSACTION 1 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
    INSERT INTO #TBL VALUES (2, 'M4A3-W(76)')
    COMMIT TRANSACTION  -- -1
    PRINT 'NESTED COMMIT 1 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)

    BEGIN TRANSACTION -- +1
    PRINT 'NESTED BEGIN TRANSACTION 2 _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
    INSERT INTO #TBL VALUES (3, 'LEOPARD-A1')
    COMMIT TRANSACTION -- -1
    PRINT 'NESTED COMMIT 2 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)

    COMMIT TRANSACTION -- -1
    PRINT 'NESTED COMMIT 3 TRANSACTION _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'ROLLBACK EN CATCH _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR)
END CATCH

PRINT 'FINAL TRANCOUNT _ TRANCOUNT :: ' + CAST(@@TRANCOUNT AS VARCHAR) --This returns 0
SELECT * FROM #TBL

DROP TABLE #TBL

Edit posting the reasoning for the answer provided by the exam:

The reasoning for the answer to be 1 provided by the exam is the following:

  @@TRANCOUNT is set to 1, When the store procedure is run,
  just one transaction is executed, which increments @@TRANCOUNT to 1.
  Although there is a nested transaction within the outer transaction,
  SQL Server does not actually create a nested transaction.

But running the whole script and printing @@TRANCOUNT on every commit and begin tran it always prints 0 once it reach the end of the script, the
nested transaction commits are decreasing the @@TRANCOUNT value while
the nested BEGIN TRAN are increasing the value, though is true that
SQL Server point nested transactions to the top transaction.

Thanks in advance


texts: Cannot reset MySQL default root password after initial installation

texts:
I have installed mysql 5.7 on a Red Hat Enterprise Linux 5.5 server and when I tried to run following command after installation it is asking for a password for root@localhost.

/usr/bin/mysql_secure_installation

I have never set a root password and I'm logged in to Linux as root user.

Then I have found following documentation for resetting root password and followed that one too.

https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

But even after starting the server with following command it is asking for a password

/etc/init.d/mysqld start --skip-grant-tables &

I have tried starting the server with init-file option, but no success.

Does anyone know how to reset the password correctly or default MySQL password?


texts:
How can I upgrade SQL Server cluster 2012 to SQL Server 2014? Version – Standard. We have 2 nodes and 2 instances on each node. Have you manuals for upgrade 2 instances on cluster SQL Server 2014

We have 2 nodes (node1 and node2) and 2 instances(inst1 and inst2). I started to upgrade on passive node2 instance inst1. After this SQL don't allow to upgrade instance inst2 on node2.

If I try upgrade inst2 then SQL show message

  upgrade instance is pending.

Therefore I try to upgrade node1 instance inst1. After this I upgrade instance inst2 on each nodes similarly. But system don't allow and show message “upgrade instance is pending”. Why? Can you explain me?


texts: Finding which DATE columns have no time data using TRUNC

texts:
I'm in the process of converting an Oracle db to a PostgreSQL one.

The script I used converted all Oracle's DATE columns into TIMESTAMPS, but I suspect that most of those do not hold any time data and could be converted to PostgreSQL DATE data type.

To make sure of it, I used the following query on Oracle:

SELECT myDate, TRUNC(myDate, 'DDD') from myTable where TRUNC(myDate, 'DDD') <> myDate;

This seems to work fine; in most cases I get 0 lines returned. For some tables though, I get a handfull of results that make no sense:

trunc(…     myDate
17/07/95    17/07/95
24/08/42    24/08/42
29/06/02    29/06/02
26/07/88    26/07/88

Update: My SQL client wasn't showing the full values, some lines did have some time data. The above lines, however are actually:

trunc(…                myDate
17/07/5595 00:00:00    17/07/5595 00:00:00
24/08/5542 00:00:00    24/08/5542 00:00:00
29/06/5602 00:00:00    29/06/5602 00:00:00
26/07/5588 00:00:00    26/07/5588 00:00:00

Clearly those are typos, but I don't know why they would be returned by my query.


texts:
I want to draw the ER model of a store which The scenario is like this: They have list of Goods and Some Seller which sells them to customers they want to know how much they get profit from their selling and also how Much money they have From Selling The Goods And also they have Some Expenditures Every day That are from these money which they earn from their selling.And Mostly Every week they send these money to the bank, Here is the model which I have Drawn

But The problem is here that I don't know Where should I Store The Total money of the Sales So If I insert a row in Expenditures table The Total money Should be decreased. IF you Guys Have any better Model For This Scenario Please SHARE WITH ME


texts: Does 'max_worker_processes' include background process from postmaster?

texts:
I am wondering that max_worker_process include background process from postmaster? I'm going to upgrade Postgres from 9.3 to 9.6 and planning to use parallel query.

-bash-4.2$ ps auxf | grep postgres | grep -v grep
root      3848  0.0  0.2  89560  2440 pts/0    S    02:02   0:00                      \_ su - postgres
postgres  3849  0.0  0.2  15728  2584 pts/0    S    02:02   0:00                          \_ -bash
postgres  6168  0.0  0.1  51016  1880 pts/0    R+   02:34   0:00                              \_ ps auxf
postgres   977  0.0  1.5 257956 15296 ?        Ss   01:48   0:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
postgres   983  0.0  0.1 112960  1576 ?        Ss   01:48   0:00  \_ postgres: logger process
postgres   992  0.0  0.1 257956  1908 ?        Ss   01:48   0:00  \_ postgres: checkpointer process
postgres   993  0.0  0.2 257956  2708 ?        Ss   01:48   0:00  \_ postgres: writer process
postgres   994  0.0  0.1 257956  1684 ?        Ss   01:48   0:00  \_ postgres: wal writer process
postgres   995  0.0  0.2 258408  2808 ?        Ss   01:48   0:00  \_ postgres: autovacuum launcher process
postgres   996  0.0  0.1 112956  1900 ?        Ss   01:48   0:00  \_ postgres: stats collector process

If max_process_worker include those bg process from postmaster, it means that when default settings, backgroup process worker for parallel query can start background processes up to 2?


texts: SQL Server Management Studio Express 2012 "ONLY"

texts:
I have installed SQL Server Express(SSMS) 2012 with management tools. After a few months, the Management studio has stopped working. The error is:

««
Evaluation period has expired. For more on how to upgrade your evaluation software please go to
http://www.microsoft.com/howtobuy
»»

I dont know why this happened with the express edition. Maybe the Management studio is not released as express edition. What should I do now to get SSMS work with SQL Server 2012?
The earlier releases of SSMS do not work with SQL Server 2012 and I must use SSMS 2012.


texts: Should I create new table, or add new columns to existing one for song lyrics?

texts:
I'd like to ask for some advice in whether I should create a new table or just add some new columns to existing table?

Honestly I've been thinking about this whole day and I really don't know.

So basically here's the structure of song table.

+------------+-----------+------+-----+---------+----------------+
| Field      | Type      | Null | Key | Default | Extra          |
+------------+-----------+------+-----+---------+----------------+
| ID         | int(6)    | NO   | PRI | NULL    | auto_increment |
| Title      | char(100) | YES  |     | NULL    |                |
| Type       | char(20)  | YES  |     | NULL    |                |
| Singer     | char(50)  | YES  |     | NULL    |                |
| Youtube    | char(30)  | YES  |     | NULL    |                |
| Score      | double    | YES  |     | 0       |                |
| Ratings    | int(8)    | YES  |     | 0       |                |
| Favourites | int(7)    | YES  |     | 0       |                |
+------------+-----------+------+-----+---------+----------------+

In this I store information about songs obviously. And yesterday I thought I would add a feature on my site to check the lyrics of the available songs. I'm only planning to add the lyrics in 2 different languages, English and German for now.

That being said, I don't know if it would be better to just add some new columns like English_lyrics and German_lyrics to the song table, or I should create a new one, called lyrics, with these two columns and the ID of the song, SongID. This way I would just select the lyrics using INNER JOIN.


texts: OLE DB Driver sql 2008r2 to Oracle failing

texts:
I have a SQL Server R2 Installation running on Windows Server 2008 R2.
I have installed Oracle Instant Client, registered the OraOLEDB.Oracle driver(orasql12.dll), and set the PATH in environment variables to the BIN folder of the Oracle installation.

I can see the OraOLEDB.Oracle provider in SQL Management Studio, however, when I try to connect to the remote datababase using OpenRowSet (see code below), I get the following error:

The OLE DB provider "[OraOLEDB.Oracle]" has not been registered.

I know that I have successfully registered this driver with regsvr32, and I still cant connect? What am I doing wrong?

The code I am using is as follows:

SELECT *
FROM   OPENROWSET('[OraOLEDB.Oracle]','[TNS]';'[UserName]';'[Password]','SELECT DataField, TYPE'); etc...


texts: How to manage log file size in large, heavy-use database

texts:
We are a team of mathematicians (i.e., no DBA experience).

We have a large database in SQL Server 2012.  It has more than 2 TB of data (hundreds of tables, each with millions of rows and hundreds of columns wide).  Each month, we receive a bundle of additions and revisions to the data, requiring us to perform extensive updates to our database by deleting, replacing, or updating most or all of the tables.

Our work is mostly focused on crafting the SQL logic to calculate the results that we need.  We're not running a real-time call center.  We apply a few indexes as needed, and we're pretty happy with the performance.

The problem is the log file.  Naturally, the log file grows and grows with so much data manipulation.  Our log file currently stands at about 1 TB.  We have a good amount of disk space, but it is not infinite.

From what we read on the Internet, we understand that the log file is necessary for transaction integrity, rollbacks, and recovery.  But for our specific purposes, we don't care about any of that.  We will probably never perform a rollback, nor would we ever attempt a recovery.  Worse comes to worse, we would simply download the data files again and create a new database from scratch.

We really just want the log file to go away and never come back.

We set the database recovery mode to Simple, naively thinking that this meant "no recovery mode", but we were quickly cured of those illusions.

We also understand that there are many wrong things not to do (detaching, shrinking, etc.).  We just don't know the right thing to do.

Maybe someone is going to suggest that we set a limit to log file growth.  However, that leaves two issues:
(1)  How do we get rid of the 1 TB which is already there?
(2)  We previously tried that, and as we approached the specified limit we started getting Error 9002 (log file full) here, there, and everywhere.  So now we are afraid of applying a size limit.

How do we tell the database "No log file, please" without any hurt feelings?


texts: Update table_name set column1 from where column2 (column2 mutiple diffirent value)?

texts:
Hi I'm working with this Query to update all the row in the table using a String of multiple values.
I'm trying some Query but i get an error

the error is "Truncated incorrect DOUBLE value"

this is my query

String text = textarea.getText();

      String sql = "UPDATE `table` SET `status`= 'YES' WHERE no_id ='"+text+"'";
      pst = conn.prepareStatement(sql);
      pst.execute();

my string is from a textarea listed all different value i need


texts:
Is there a standard pattern for linking products to manufacturers, when we want to capture the fact that manufacturers are interacting with each other over time (mergers, takeovers, etc)?

In our industry, company A makes product 1, and many people refer to that product with a token like "A-1" when talking about it or trying to look up detailed performance and physical characteristics of that product in our database.

If company B purchases A, we still want to be able to allow people to use a token like "A-1" to look up the performance data of that product, even though the owning company is now B. A new user meanwhile can look at the records and understand that company A made the product and is the former owner, while company B is now the "real" owner.

Is it viable to model this with two manufacturer foreign keys, and a version column that would increment when the manufacturer relationship changes? The version column would be used to allow the older "A owns 1" row to remain in the table for existing records that linked to it at the time A owned 1 before being bought out by B.


texts: Would re-importing reduce the DB/backup size?

texts:
My MariaDB DB size is constantly growing. So is the backup file size.

As I am not a DB admin (just a programmer using this DB) I wonder if I could just export the whole DB, then delete the existing one and re-import it again to decrease its size/backup size?

Idea: I wonder if this would reduce the overall size as all update and change operations would be no longer in the changelog. Some of the increased size can be explained by the number of tuples (growing over time), but I have also tables which have a lot of insert delete operations.

Maybe I am completely naive, but I wonder if this would work?

--- edit ----

As asked below, I create the dump with mysqldump ("my backup") and import it with source mydumfile.sql


texts: Optimal table design for prizes that can be int or str

texts:
I'm storing data about prizes won by competitors during rounds of various events.  Although most prizes are cash, non-cash prizes are awarded too.  A common query will be summing a participant's lifetime cash winnings across events.

A single-table solution with a prize_type attribute doesn't allow me to store cash prizes as INT, so I can't easily sum them.  Using separate tables feels bad.

CREATE TABLE event_prizes_cash (
  event_id INTEGER,
  event_round INTEGER,
  person_id INTEGER,
  place INTEGER,
  amount INTEGER,
  notes TEXT )

CREATE TABLE event_prizes_other (
  event_id INTEGER,
  event_round INTEGER,
  person_id INTEGER,
  place INTEGER,
  prize TEXT,
  notes TEXT )

What's the optimal way to store this data?


texts: Can't start system_health extended events session

texts:
On some SQL Server instances, (I did not find a pattern yet), when I try to manually start this session, I get the following error :

For target,
"5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", the
parameter "filename" passed is invalid. Target parameter at index 0 is
invalid (Microsoft SQL Server, Error: 25641)

If we look into the SQL Server logs, each time the instance starts (i.e. after Windows reboots for patches to get applied), we get :

Error: 25641, Severity: 16, State: 0. For target,
"5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", the
parameter "filename" passed is invalid. Target parameter at index 0 is
invalid
Error: 25710, Severity: 16, State: 1. Event session
"system_health" failed to start. Refer to previous errors in the
current session to identify the cause, and correct any associated
problems.
Error: 25709, Severity: 16, State: 1. One or more event
sessions failed to start. Refer to previous errors in the current
session to identify the cause, and correct any associated problems.

Has anyone observed this issue ? I suspect it's due to permissions, as the definition of the session does not indicate a path, but simply the name of the .xel file. That is, it writes directly to the SQL Server logs folder, where obviously the SQL Server service account is granted full access.
I get the same issue for the AlwaysOn_health session.
Thanks in advance for your help !


texts: Retrieve a count from a one-to-many relationship even if there are no rows at the "many"-end of the relationship

texts:
I have the following table, company_likes:

                     Table "public.company_likes"
   Column   |              Type              |       Modifiers
------------+--------------------------------+------------------------
 company_id | integer                        | not null
 user_id    | integer                        | not null
 created_at | timestamp(0) without time zone | not null default now()
Indexes:
    "company_likes_pkey" PRIMARY KEY, btree (company_id, user_id)
Foreign-key constraints:
    "company_likes_company_id_foreign" FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
    "company_likes_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

The structure of the companies table is not important as you can deduce whatever needed for the purpose of this question from the foreign keys details in the \d company_likes output.

I am trying to fetch the number of likes for a set of companies. I tried the following:

select company_id, count(company_id) as likes_count from company_likes where company_id in (1,2,3,4) group by company_id;

And per this StackOverflow answer, I have also tried this:

select company_id, count(company_id) as likes_count from company_likes
left join companies on companies.id = company_likes.company_id
where company_id in (1,2,3,4)
group by company_id;

However the output in both cases is an empty result. I would have liked something like this

 company_id | likes_count
------------+-------------
 1          | 0
 2          | 0
 3          | 0
 4          | 0

What query should I use to get a result like this?


texts: SQL Server 2014/2016 (Win2K12R2) and Winsock limits - how to cope?

texts:
We're experiencing a problem with connections to SQL Server and after a week of digging through I was able to pinpoint it to Winsock limits. Here's the description:

We have SQL Server 2014 (and 2016) Ent installations running on Windows Server 2012 R2. Virtualized and not virtualized. We observe the problem in both environments.
The clients do not use connection pooling (please don't recommend turning it on, it's root of the problem, I know, but it's something I can't do anything about)
Clients create and close new connections at extremely high rate: normally it's 1500 closed/opened connections per second from a single client. There are 20-25 clients per SQL Server that are doing it at the same time.

Now, at certain points during the business day, we experience traffic bursts, which essentially makes the new connection rate spike up to 2500-3500 connection attempts per second and it continues for around 2-3 seconds.

That's when the fun starts: clients get connection errors during the same 2-3 second bursts.

That is what we see in the network trace for all these connection attempts:

What we have confirmed and done:

It is not port exhaustion, neither on the server nor on the client.
It is not SQL Server worker thread exhaustion. Here're graphs that demonstrate it:

It is not a TIME_WAIT problem, previous connections by the clients are gracefully closed with FIN, ACK -ACK - FIN, ACK - ACK sequence.
A connection does not reach SQL Server and gets rejected by Winsock. Here is a PerfMon graph that demonstrates it:

We tried adjusting the NIC settings and Windows TCP/IP feature settings (RSS, TSO, Scaling Window - enabling/disabling) and it did not fix the issue.
It is not SYN attack protection. We have collected a netsh trace and there were no indications of entering SYN flood attack protection mode.
Hardware resources should not be an issue. We see this problem even on 72 core / 144 thread 4-socket box with 2TB RAM.

Prior to SQL Server 2005 we were able to adjust the backlog window using registry (I'm talking about these instructions). Right now we don't have this kind of ability.

The same document I've linked above says:

  Starting in SQL Server 2005, the network library passes a value of SOMAXCONN as the backlog setting to the listen API. SOMAXCONN allows the Winsock provider to set a maximum reasonable value for this setting.

My understanding is that it happens based on the max connections setting. We have it configured to 0 (unlimited) in SQL Server.

Worth mentioning, we do observe a drop in network traffic (bytes sent/received over the network interface) when we experience the problem. Though there is no increased traffic before/after:

So, here's the question: what can we do to fix the issue? Or are we virtually hitting the limits of Winsock?


texts:
We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the necessary data is stored/cached alongside in the initial table.

I am a strong advocate of triggers that would help me keep all the data up2date absolutely transparently. There are other decision makers in the company who are against using triggers (for several reasons) and suggest building a VIEW instead in hopes that the VIEWs are already optimized for better data retrieval.

Is this the case in MySQL?


texts: How to split one table into two in a single statement?

texts:
I've got an old table I want to split into two, but I'd rather not run two near-identical queries across the whole thing. The following does not work because you can only return column from the table you're inserting into:

WITH comment_insert AS (
    INSERT INTO comments (content)
        SELECT content
          FROM old_schema.comments
          JOIN area ON area.id = old_schema.comments.area_id
        RETURNING id, area.id AS area_id
)
INSERT INTO area_comment (parent_id, area_id)
SELECT comment_insert.id,
       comment_insert.area_id
  FROM transitional_schema.mon_area_comment

Is it possible to do something like this, putting some columns in the "parent" table and the parent ID plus some other columns into another table, using a single query?


texts: Only one administrator can connect at this time (MS SQL Server error 18461)

texts:
I was experimenting the effect of giving SQL Server a small amount of memory I thought it was going to recover.

I configured SQL Server to use 200MB of memory now it does not want to start, I did some searches on the internet and I was advised to start SQL Server in single-user mode. however, I get the error

  Login Failed for user A. Reason: Server is in single user mode. Only
  one administrator can connect at this time (Microsoft SQL Server,
  Error: 18461)

I have stopped the SQL Server Agent.


texts: Update reps table with right values in a trigger after insert on userterritory

texts:
I two DBs veeva and pdone as shown below in the pics

As you can see veeva is simple and hasn't no constraints which relies on duplicate and wrong values. The problem happen exactly on userterritory where a user is assigned to one or more territory. In the new DB pdone that is not allowed so I need to find - from veeva - those reps that has only one territory and I got that through this query:

    SELECT
        count(*) AS reps,
        userid,
        lastmodifieddate,
        territoryid
    FROM
        (
            SELECT
                userid,
                territoryid,
                count(*) AS territories
            FROM
                userterritory
            GROUP BY
                userid
            HAVING
                territories = 1
        ) T1
    INNER JOIN (
        SELECT
            id AS userid,
            lastmodifieddate
        FROM
            `user`
        WHERE
            `user`.`id` IN (
                SELECT
                    userterritory.`userid`
                FROM
                    userterritory
            )
    ) T2 USING (userid)
    GROUP BY
        territoryid
    HAVING
        reps = 1

After each INSERT on userterritory I execute a trigger that update reps table by setting up the right territories_id based on relationship from userterritory. This is the code for the trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
BEGIN
    # [ VARIABLES DECLARATION ]
    DECLARE vUserID VARCHAR(18) DEFAULT '';
    DECLARE vTerritoriRep VARCHAR(45) DEFAULT '';
    DECLARE vTerrytoriLastMod DATETIME DEFAULT '0000-00-00 00:00:00';
    # [ VARIABLES ASIGNATION ]
    SET vUserID         =NEW.userid;
    SET vTerritoriRep   =(select IFNULL(reps.territories_id,'N/A') from pdone.reps where reps.veeva_rep_id=vUserID LIMIT 0,1);
    SET vTerrytoriLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);

IF (NEW.territoryid != vTerritoriRep) THEN
    UPDATE pdone.reps
    SET reps.territories_id = NEW.territoryid,
        reps.lastSyncAt = IF(NOW()>vTerrytoriLastMod,NOW(),reps.lastSyncAt),
        reps.updatedAt=now()
    WHERE reps.veeva_rep_id=vUserID;
END IF;
END

All is working perfectly, but I am getting reps with territories_id nulled and that is not allowed since each reps has to be assigned to one territories. See this pic:

So, how do I apply my query to the trigger in order to get right results? Can any give me some help?


texts: MongoDB not using wildcard nested array index

texts:
I have the following collection:
{
   _id: 12345,
   quizzes: [
     {
        _id: 111111,
        done: true
     }
   ]
},
{
   _id: 78910,
   quizzes: [
     {
        _id: 22222,
        done: false
     }
   ]
}

I want to select the documents where a certain quiz from the quizzes was done and want to make sure that it uses the appropriate index. So I use the following query:
Answer.find({ 'quizzes.0.done': true }).explain('queryPlanner');

Which returns:
queryPlanner: {
  plannerVersion: 1,
  namespace: 'iquiz.answers',
  indexFilterSet: false,
  parsedQuery: { 'quizzes.0.done': [Object] },
  winningPlan: { stage: 'COLLSCAN', filter: [Object], direction: 'forward' },
  rejectedPlans: []
}

The query is not using any index as seen from the output. I have tried the following indexes and none get used:
{ quizzes.done: 1 }
{ quizzes.[$**].done: 1 }
{ quizzes: 1 }

The only 1 that actually gets used:
{ quizzes.0.done: 1 }

However this is not really practical as I may target any quiz from the quizzes array not just the first one. Is there a certain syntax for the index in my case or this is a current limitation of mongodb? Thanks!


texts: MySQL Database Restore Failed With Obscure Error

texts:
I was importing a dump from another system into a MySQL database running on 64-bit Linux Mint when I got the error below.:-

12:42:37 Restoring Patient (Results)
Running: mysql --defaults-extra-file="/tmp/tmpejgN41/extraparams.cnf"  --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments < "/home/etb/dumps/Dump20141112/Patient_Results.sql"
ERROR 1064 (42000) at line 650: 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 ''\0\0\0\0 $Name: rel_1_2_0_rc1b  $\n\0%071116019 ̿\n\0\0\0\0\0\0@ \0?Q \Z\0\0?' at line 1

Operation failed with exitcode 1
12:56:20 Import of /home/mjh/dumps/Dump20141112 has finished with 1 errors

It makes no sense to me as how can I have an SQL syntax error when 606 lines out of approximately 2000 had been imported correctly. Searching the internet has not added anything further so far. I am arranging for the data to be dumped again so I can retry later today.


texts:
I noticed this discussion on NOT using ORDER BY in SQL query unless really necessary.

https://www.brentozar.com/archive/2019/10/how-to-think-like-the-sql-server-engine-adding-an-order-by/amp/

The author suggests that looking at "Query Bucks" which I think in my exec plan is "Estimated CPU cost", that the Sort is very expensive.

Here are the individual operations:

and

So, up to now I have been looking at exec plan and trying to make the most expensive "Table Scan 97%" more efficient.

BUT maybe I should move to sort completely out of the query and into my C#?


texts: how to find out everyone that has permissions to run a specific job?

texts:
I need to grant permissions to a group of users to run a specific job,

when I have a look at the job

use msdb
go
sp_help_job @job_name='ADS Incremental Load Orders & Accounts'

then I can see the job runs several packages.

how can I find using T-SQL, all the users that have permissions to run this job?


texts: SSL connection to MySQL suddenly not working anymore

texts:
SSL connections where running smoothly til last week, now I can't connect anymore.
I have a MySQL installation on a virtual machine running Ubuntu. The machine is hosted on a Windows 2012 R2 Server. MySQL is configured for SSL, we have generated keys and everything I see on the side of MySQL looks fine:

+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/mysql/ca-cert.pem     |
| ssl_capath    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+

On the client side I use FileMaker Pro and a PHP plugin to make a connection. Checking the server from the client with telnet 192.168.87.30 3306 I get the usual answer

5.5.54-0ubuntu0.14.04.1¯`%2nS4IQ└;tZC}o5YBzb\mysql_native_password

As long as I deactivate SSL I can still connect to and work with the database. With SSL active all I get is an error

  Connection to database failed: 2026 (SSL connection error)

The exact same configuration on the client works perfectly well with another MySQL installation on a different server, so the client is certainly not the problem. Something must have changed either on the Ubuntu machine or on the Windows server, but I'm totally lost what this could be.


texts:
We have a Windows 2008 R2 server running SQL 2012 Express RTM (11.0.2100.60).

All three protocols (shared memory, named pipes, TCP/IP) are enabled for the server and both 32-bit and 64-bit clients. No certificate is configured, and encryption is not required. SQL Server is configured for mixed-mode authentication.

A few weeks back, we added the registry key to disable SSLv3 for server software, but didn't restart the server. All three TLS protocols (1.0, 1.1 and 1.2) are enabled.

This morning, after installing the critical MS14-066 patch and restarting, SQL Server would not accept any connections. Using shared memory or named pipes returned the dreaded "no process is on the other end of the pipe" error. Using TCP/IP returned a "connection forcibly closed" error.

Uninstalling the patch made no difference - we were still unable to connect to SQL. Only after re-enabling SSLv3 and restarting the server were we able to reconnect. We have since reinstalled the patch, and the problem has not returned.

Therefore, I can only conclude that SQL Server requires SSLv3 to be enabled, even if the connections are not encrypted. However, I can't find this documented anywhere.

Can anyone else confirm this? Is it a known issue?


texts:
I have two databases/catalogs on the same SQL server and an SQL-user with access to both of them.

Collation on Database01 is Danish_Norwegian_CI_AS

Collation on Database02 is SQL_Latin1_General_CP1_CI_AS

I need to query two views on "Database02". The queries are basically

SELECT * FROM View01 -- (resulting in 43 rows)
SELECT * FROM View02 -- (resulting in 2000 rows)

The two views are rather complex (to say the least) and I do not have access to change them.

If I log onto the SQL server and specify "Database02" as "default catalog", the queries respond in "0.001" and 30 seconds, respectively.

However if I log onto the SQL Server and specify "Database01" as "default catalog" the first query executes in 30 seconds whereas the second query times out after 600 seconds.

The queries executed on Database01 will be something like

SELECT * FROM Database02.View01
SELECT * FROM Database02.View02

Can anyone please explain this behavior? I am not trying to do any joins between the databases, so I would not expect the diffent collations to be an issue.

Slow query: https://www.brentozar.com/pastetheplan/?id=BJgyIUhrG

Fast query: https://www.brentozar.com/pastetheplan/?id=S1-BUIhSG

PARAMETERIZATION is "Simple" on both databases.


texts: How to join same department with multiple staff?

texts:
I have 2 tables.

staff

+----+- ----------+-----------+-------------+------------+
| ID | fullname   | email     | countryCode | departCode |
+----+------------+-----------+-------------+------------+
| 1  |  Richard   | richard@  | 1           | 1          |
| 2  |  Tom       | tom@      | 1           | 1          |
| 3  |  Andy      | andy@     | 2           | 2          |
| 4  |  Elsa      | elsa@     | 2           | 3          |
| 5  |  Leo       | leo@      | 1           | 3          |

department

+------------+- ------  ----+
| departCode | departName   |
+------------+--------------+
| 1          |  Management  |
| 2          |  HR          |
| 3          |  IT          |

The 1st method group_cat for my query the output:

select d.departName ,
GROUP_CONCAT(s.fullName SEPARATOR ' <,> ') 'fullName' ,
GROUP_CONCAT(s.email SEPARATOR ' <,> ') 'email' from staff s inner join department d on s.departCode = d.departCode
where s.departCode and s.countryCode=2
group by d.departCode

Gives the output:

+------------+---------------------+--------------------+
| departName | fullname            | email              |
+------------+---------------------+--------------------+
| Management |  Richard <,> Tom    | richard@ <,> tom@  |
| HR         |  Andy               | andy@              |
| IT         |  Elsa <,> Leo       | elsa@ <,> leo@     |

The 2nd Method I use:

SELECT d.departName, s.fullName, s.position, s.site, s.ext, s.did, s.mobile, s.email from staff s
left join department d on s.departCode = d.departCode
left join country c on s.countryCode = c.countryCode
where c.countryCode = 2

Gives the output:

+------------+---------------------+--------------------+
| departName | fullname            | email              |
+------------+---------------------+--------------------+
| Management |  Richard            | richard@           |
| Management |  Tom                | tom@               |
| HR         |  Andy               | andy@              |
| IT         |  Elsa               | elsa@              |
| IT         |  Leo                | leo@               |

How can I use 2nd method to combine the department with multiple staff and result show multiple staff in one department?


texts: Oracle becomes too slow after system crash

texts:
I am using Oracle 11.2 as a database for my application. For this purpose I should import my data (around 100m records) to this database. For this purpose I used SQL Developer.

Unfortunately at the middle of the import process, my system crashed and forced me to reboot the system. After system boot up everything related to Oracle is strange:

starting up Oracle takes around 10 min (before system crash takes less than 10 sec)
SQL Developer takes too much time to connect to database
and the importing process becomes 10000 times slower!

Would you please tell me what happen and how can I fix that?

P.S: After passing 24 hours from the crash time a performance becomes better but still much less than before crash.


texts: Update Mysql records using Same table but Based on same table record

texts:
I have table in which i want to update records based on it's 'wishlist_id' value.
Sample format of table records.

Expected table record after update.

So can you help to update this record using mysql query?


texts: Postgresql streaming replication and wal archive?

texts:
We have postgresql 9.1 configured for streaming replication with a WAL archive, and this works great. We would like to use a shared WAL archive so we can restore from periodic base backups. The postgresql docs refer to this as a "long-term WAL archive area."

However, we archive to a shared folder (using archive_command), and after a failover, the new master tries to archive the same WAL segment filename as the old master. The contents are different. What is the right way to handle this?

We have tried to allow the new master to overwrite the old file, but then when we test restore from the base backup with a configured restore_command that restores from the shared WAL archive, we get various errors when the restoring server hits the overwritten log files. (errors like "record with zero length at 0/E5003128") So it looks like overwriting is the wrong approach. What is the right approach?

In a restore situation, there is no master and no slave. There is a fresh clean database that is restoring from a basebackup. It also has a restore_command. How are the archive_command and restore_command supposed to work together to let the restore work? (i.e. where does the archive command write after a failover, and where does the restore_command read from?)

A bit more information so this is perfectly clear:

Let's say we have 4 servers. A, B, X, and R. A and B are identical, and are a master/slave pair using streaming replication. They have the same configuration, except as far as recovery.conf v. recovery.done on the slave. X is a separate reliable host where we store backups and archived WAL files. archive_command (on either A or B, whoever is master) archives WAL files to a shared folder on X. restore_command (on either A or B, whoever is slave) restores from that shared folder on X. Periodically, we take a basebackup of the master and store it on X. This works, with one caveat below.

In a disaster situation (or for testing) we want to restore a full copy of the database onto R. We restore one of the basebackups from X on R. R now has the restore_command to read from the shared folder on X. It can easily restore all the WAL files made from the same source as the basebackup, but it fails as soon as it hits a WAL file from the new master after a failover. (see Caveat).

Caveat: if archive_command does as the specs says, and refuses to overwrite a file with the same name, then the new master will fail to archive any WAL file segments. On promotion, it tries to archive everything it has locally, including segments from the old timeline. In fact, it has segments that were never archived by the old master, because they were streamed before the xlog segment was closed. So currently, we allow the archive command to overwrite archived WAL files in this situation. It's these WAL files that fail to restore in a recovery. We know this is wrong, somehow, but don't know what the right behavior should be. With this behavior, though, failover and later fail back work.


texts: SqlServer: can one table's PK be the FK of other two table's?

texts:
I have three tables project, users and proj_user.
The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id)

My question is, is it possible for the project_id and user_id's data to be different, like?:
project_id = '12345' and user_id = 'abc987'

Edit Thanks for the quick response. But just look at this scenario

TABLE project (
    project_id PRIMARY KEY
    ...
)

TABLE users (
    user_id PRIMARY KEY
    ...
)

TABLE proj_user (
proj_user_id
project_id
user_id
PRIMARY KEY (proj_user_id),
FOREIGN KEY (proj_user_id) REFERENCES projects (project_id),
FOREIGN KEY (proj_user_id) REFERENCES projects (user_id)
)

I want the project_id and user_id values to be different.

What do I need to change here?


texts: I tried creating a simple erd for a banking system, I was wondering what can I improve, and where I did wrong. I'll also have to transfer this erd to sql and then try some queries on it. Thanks in advance.


texts: Advice for ETL from Oracle to SQL Server?

texts:
I'm building a data mart in SQL Server which needs to extract each month from an Oracle database.  Currently this is done by exporting to flat files, then running all the inserts/updates in SQL Server...

There are approximately 10 flat files to export then insert before setting off the updates.  Each file contains around 3.5 million rows and 5 to 10 columns per file.

Everything now is done strictly through stored procedures and I'm looking into SSIS assuming the permissions are properly granted this week.  I've also come across Rhino ETL.

Does anyone have advice on efficient though relatively painless ETL from Oracle to SQL Server?

Appreciate the help!


texts:
I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not.  Here's a simplified example:

DECLARE @T TABLE (id smallint identity, BitMask tinyint);
INSERT INTO @T (BitMask) VALUES
  (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT
  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM @T t
OUTER APPLY (
  SELECT * FROM (VALUES
    (t.id, 0, 'Can X'),
    (t.id, 1, 'Can Y'),
    (t.id, 2, 'Can Z')
  ) bm(id, BitNum, Permission)
  WHERE t.BitMask & POWER(2, bm.BitNum) <> 0
) bm

This returns the following info:

id     BitMask BitNum      Permission
------ ------- ----------- ----------
1      0       NULL        NULL
2      1       0           Can X
3      2       1           Can Y
4      3       0           Can X
4      3       1           Can Y
5      4       2           Can Z
6      5       0           Can X
6      5       2           Can Z
7      6       1           Can Y
7      6       2           Can Z
8      7       0           Can X
8      7       1           Can Y
8      7       2           Can Z
9      8       NULL        NULL
10     9       0           Can X

(15 row(s) affected)

So far so good.  The trouble comes when I try to do an aggregate by id, so that I have all the permissions in one field.  I've tried adding the following APPLY clause to do a standard XML list-string-agg, but I get an error Invalid object name 'bm'.:

OUTER APPLY (
  SELECT
   ParamList = STUFF(
     (
       SELECT  '; ' + a.Permission
       FROM bm a WHERE a.id = b.id
       ORDER BY a.BitNum
       FOR XML PATH(''), TYPE).value('.', 'varchar(max)'
     ), 1, 2, ''
   )
  FROM bm b
  GROUP BY b.id
) q

Any ideas?


texts:
I am seeing unexpected behavior with some MySQL performance.

When I run a simple query SELECT 1; on my local host (MySQL 5.6.x) using workbench, it executes in 0.000s, but the same query run on Amazon RDS (medium MySQL 5.5.x) takes almost 0.094s.

What could be causing this behavior?


texts: Postgres pg_basebackup reports directory exists but is not empty

texts:
I have postgreSQL 9.4.1 installed on a Win 2012 Server.

I am looking to setup replication between this and another server of the same spec. So I am following guidance on preparing the master database server for standby servers as per warm standby section 25.2.3 This involves using pg_basebackup.

The PostgreSQL instance on the master contains a database for one of our apps. This database has 3 schemas each of which have their own dedicated tablespace. On windows the tablespace locations were created by using the MKLINK command (as I believe the LOCATION parameter has to point to a symbolic link on windows) so - for example, :

mklink /j C:\tbs1 D:\app\tablespace\tbs1
mklink /j C:\tbs2 D:\app\tablespace\tbs2

So, using the above as an example a tablespace in postgreSQL was created using the following command:

CREATE TABLESPACE app_ops OWNER the_user LOCATION 'C:\tbs1';

Which is all fine. However when I wish to use the pg_basebackup tool, i received the following error/warning - with nothing being written to the backup location:

pg_basebackup -h localhost -U postgres -D C:\master_backup
pg_basebackup: directory "C:\tbs1" exists but is not empty

I don't understand why I am receiving this specific message.

The main point in many of the answers I have seen talks about ensuring that the tablespaces didn't exist within PG_DATA. However they don't in my case  - PG_DATA is set in the executable path of the windows service as -D C:\Program Files\PostgreSQL\9.4\data, as can been seen:

"C:\Program Files\PostgreSQL\9.4\bin\pg_ctl.exe" runservice -N "postgresql-x64-9.4" -D "C:\Program Files\PostgreSQL\9.4\data" -w

I had looked at the tablespace-mapping parameter 'old=new', but this didn't change the result.

I appreciate that there are similar questions and guidance, however I do not seem to be able to find anything additional that may help. I have looked on dba/stackoverflow and www.postgresql.org I would list more of the places I have seen helpful info - but alas my reputation doesn't allow me to add any more

If there are any thoughts or ideas it would be greatly appreciated.

regards.


texts: Efficiently implementing an orders table?

texts:
Orders are typically handled with one large orders database and then status is changed as necessary "active", "fulfilled", etc.

However, my project is scaling quite quickly so we're getting a lot of queries of the orders table and I'm not sure if this is the best way to do it as it continues to grow - as customer service systems, monitoring systems and user-facing order systems are all querying the same table.

I'm thinking about creating an  ACTIVE_ORDERS / OLD_ORDERS table (i.e. certain systems would hit ACTIVE_ORDERS more often and others could hit OLD_ORDERS more often) and the location of these orders could be stored in a bridge table, and their interchange between tables controlled by a transaction.

I suspect I will be making certain tasks quite difficult, for example searching through ALL (active/old) orders with customer name LIKE something might be very slow unless collated through a temporary view.

What do you think?


texts: Postgres pg_restore error: invalid byte sequence for encoding "UTF8": 0xe05c62

texts:
Lucky me, I get to play DBA today!

I am migrating a Postgres 8.4 DB from one server to another (both RHEL6).  I backed up the schema from the old server and restored it to the new server.  Then I did a full database backup and restored it to the new server, but I had two tables that were skipped during the restore.  In both cases I used the format=c option with pg_dump and pg_restore.

I am now focusing on those two tables, and i need help!  I do a backup on the old server:

pg_dump --format=c --file=mytable.sqlc -a --table public.mytable mydatabase

Now I go to the new server and attempt a restore:

pg_restore -d mydatabase -t mytable --format=c -a -c mytable.sqlc

I'm getting the following errors:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2241; 0 23420338 TABLE DATA mytable mydatabase_script
pg_restore: [archiver (db)] COPY failed for table "mytable": ERROR:  invalid byte sequence for encoding "UTF8": 0xe05c62
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY mytable, line 231993
WARNING: errors ignored on restore: 1

The error seems to imply that I have an encoding difference between the old db server and the new one (even though I backed up/restored the schema).  I did a:

select * from pg_database where datname = "mydatabase"

And it shows that both databases are UTF-8 encoded.  What do I need to check next?  Thanks.


texts: SQL Server Performance hit after cache rebuild

texts:
After I rebuild my indexes and update my statistics, SQL Server will have to rebuild the execution plan cache to get up to speed with the current setup. So, will the SQL server do this immediately after my stats are updated or will it wait until a corresponding query is executed to rebuild the cache ?

And if it chooses to wait until a query is executed to rebuild the execution plan cache, will there be any slight performance degradation since it has to build the cache from start again before executing the query ?

Let me know if I am missing anything here. Thanks!


texts:
I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org.
I understand the commands, since I previously used both Oracle and MS-SQL, but all the scripts I'm running return errors when they reach the "COPY FROM" instruction. More precisely, the error is thrown at the first element that should be inserted in the given table.

I've tried running the scripts both as queries and as pgScripts, but in both ways I'm getting an error at the first row after the COPY FROM.

I'm using pgAdminIII and I used StackBuilder to install PostgreSQL 9.2.4.1 as a DB Driver. May I be missing some basic configuration that's preventing me from running this command, or I just did not understand they way it works?

EDIT:
The error is:

ERROR:  syntax error at or near "7"
LINE 5600: 7 4 13 37 2012-03-10 16:41:43.797787 2012-03-10 16:41:43.797...
           ^

********** Error **********

ERROR: syntax error at or near "7"
SQL status: 42601
Char: 140891`

where the text is:

COPY action_abilitations (id, group_action_id, partecipation_role_id, group_id, created_at, updated_at) FROM stdin;
7   4   13  37  2012-03-10 16:41:43.797787  2012-03-10 16:41:43.797787`


texts:
I'm trying to import an Excel spreadsheet into SQL Server 2012 using SSIS. There is only one sheet in the file. The file is a SalesForce export. I believe the Sheet name changes depending on when the export is performed. So i have a nested foreach containers to go through firstly the .xls files then the sheets within so that i don't have to specify the sheet name. The problem is that SSIS seems to think there is more than one sheet in the file. One called 'report1352373663719 1 $' and one called 'report1352373663719 1 $'_

I've opened the file in Excel and there's only one sheet on there and there doesn't appear to be any hidden sheets.

Interestingly i tried to import the 'report1352373663719 1 $'_ sheet using the bog standard import and export wizard and it errors with The Microsoft Jet database engine could not find object 'report1352373663719 1 $'_

The first sheet detected gets imported without any problems but my package errors when trying to process the 2nd (non-existent) sheet.

Does anyone have any idea as to how i can stop my package from erroring?

** Update **

So it seems that this is something to do with the data filter options in Excel. If you create a new sheet you don't get the _ sheet. If you add a filter to the top row and save your sheet, the _ sheet appears. If you then remove the filter again, re-save your sheet the _ sheet remains. Frustrating to say the least.

** End of update **

Thanks,

Simon


texts: SQL Windowing Functions: Are the results ordered?

texts:
If I have a window function which uses something like

SELECT *, row_number() OVER (ORDER BY something) FROM table

should the results be ordered?

I am currently testing out my queries in Microsoft SQL Server, and it certainly seems to be ordered, but I know that this product has the tendency to order rows when it hasn’t been asked.

Is this the standard behaviour?


texts: PostgreSQL - Create table as select with distinct on specific columns

texts:
I need a script to create a table . The problem is my select for creating the table contains equal values in some rows. I need to use distinct for these columns. The other columns' values can come from any matching row.

My current result table has data like this:

|   CITY   |   STREET   |   STREET_NUM   |   VAL_X   |   VAL_Y   |
------------------------------------------------------------------
| CityA    | Street abc |   5            | 11.5      |   0.5     |
| CityA    | Street abc |   5            | 15.4      |   1.8     |
| CityA    | Street abc |   5            | 12.4      |   2.8     |
| CityB    | Street xyz |   18           |  5.4      |   1.9     |
| CityB    | Street xyz |   18           |  8.4      |   1.1     |
| CityC    | Street klm |   55           |  9.6      |   0.8     |

But I need data like this:

|   CITY   |   STREET   |   STREET_NUM   |   VAL_X   |   VAL_Y   |
------------------------------------------------------------------
| CityA    | Street abc |   5            | 11.5      |   0.5     |
| CityB    | Street xyz |   18           |  5.4      |   1.9     |
| CityC    | Street klm |   55           |  9.6      |   0.8     |

For columns city, street and street_num I need to apply distinct. val_x and val_y should be used anyone, for example first of that group with same city, street and street_num.

Can you give me advice how to edit this script?


texts: Cassandra nodetool repair open files issue

texts:
I am using Cassandra 3.6. After nodetool repair Cassandra start takes too much time. The message is:
ViewManager.java:226 - Not submitting build tasks for views in keyspace system_schema as storage service is not initialized

The system stuck hours on this message. Any suggestions, please. The number of the open files raised significantly. From 100k to 1.5 millions.


texts: Not Auto and User Generated Stats in Catalog view of SQL Server

texts:
While querying

select * from sys.stats

I get this result

In the Books Online it is written

But looking at results I can see that there are also some stats which are not created neither automatically nor by a user. Then how were this stats generated? Or who created them?

Thanks for your time!


texts: Are single-instance cross-DB transactions fully protected in Availability Groups as is, or do they require DTC_SUPPORT = PER_DB?

texts:
In a relation to MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group, which is a similar but different question.
We have an SQL Server 2016 for which we are considering creating an Always On availability group for purposes of disaster recovery only.
Currently the server is not a part of any cluster or availability group, and:

The server hosts multiple databases
Most of the operations are cross-database transactions
Many of the operations involve actions not supported in distributed transactions (e.g. save transaction)

From the fact that currently save transactions etc execute successfully, one can conclude that the server is not using distributed transactions.
Now, let us consider the following two quotes from Configure distributed transactions for an Always On availability group:

A transaction with two or more databases in a single instance of the database engine is actually a distributed transaction. The instance manages the distributed transaction internally; to the user, it operates as a local transaction. SQL Server 2017 (14.x) promotes all cross-database transactions to DTC when databases are in an availability group configured with DTC_SUPPORT = PER_DB - even within a single instance of SQL Server.

(That seemingly suggests that our cross-database transactions are technically distributed because they involve multiple databases. Which contradicts the observation that save transaction currently works, so let us call them "kind-of" distributed, not really "true" distributed.)

SQL Server does not prevent distributed transactions for databases in an availability group - even when the availability group is not configured for distributed transactions. However when an availability group is not configured for distributed transactions, failover may not succeed in some situations. Specifically the new primary replica SQL Server instance may not be able to get the transaction outcome from DTC. To enable the SQL Server instance to get the outcome of in-doubt transactions from the DTC after failover, configure the availability group for distributed transactions.

(This promises woes for distributed transactions executed when no DT protection is enabled in an AG. But does that include both the "kind-of" and the "true" distributed transactions, or only the "true" ones?)

Does the second quote address only "true" distributed transactions, or also the "kind of"  distributed transactions that are "distributed" simply because they involve multiple databases, like the first quote explains?
That is, if the only "distributed" transactions that happen are the single-instance ones that just involve several databases from the same server instance, then is it or is it not the case that such "kind-of" distributed transactions are fully protected within an AG as if they were local transactions, even when the AG has DTC_SUPPORT = NONE?


texts: Postgresql sql import Permission denied!

texts:
Can't import db from sql file

[user@localhost ~]$ sudo -u postgres psql
[sudo] password for user:
could not change directory to "/home/user"
Password:
psql (9.2.24)
Type "help" for help.

postgres=# \c db
You are now connected to database "db" as user "postgres".
rob=# \i /home/user/Downloads/dump.sql
/home/user/Downloads/dump.sql: `Permission denied`

[root@localhost user]# ls -Z /home/user/Downloads/dump.sql
-rw-rw-rw-. postgres postgres unconfined_u:object_r:user_home_t:s0 dump.sql

Q: Why Permission denied? How can I fix it?

CentOS 7.2.1511


texts: Difference Between SP with BEGIN/END and without BEGIN/END

texts:
I have found some SP(stored procedure) written as,

CREATE PROCEDURE [dbo].[XXX]
(
   -- Parameters
)
BEGIN

--- Actual Work

END

and some as

CREATE PROCEDURE [dbo].[XXX]
(
   -- Parameters
)

--- Actual Work

What makes it difference?


texts: Why does using a Real data type in a mathematical equation return an incorrect result?

texts:
I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is causing my equation to return an incorrect value.

Can someone explain to me why

SELECT 776.2384 * cast(100 as real) / 100

equals 776.2385 while

SELECT 776.2384 * 100 / 100

equals 776.2384?


texts:
What is the differene between:

BACKUP INCREMENTAL LEVEL 0 DATABASE;

and

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

I read some manuals, but I don't get the idea.


texts:
I have 2 databases with the same structure massfacturation and massfacturation_old and I want to simply combine 2 queries to extract a count like:

> select count(distinct(sciper)) as usercount, date_format(`date`, "%Y") as year from massfacturation_old.jobs group by year;
+-----------+------+
| usercount | year |
+-----------+------+
|       160 | 2017 |
|       262 | 2018 |
|       165 | 2019 |
+-----------+------+
3 rows in set (0.00 sec)

and on the new one

> select count(distinct(sciper)) as usercount, date_format(`date`, "%Y") as year from massfacturation.jobs group by year;
+-----------+------+
| usercount | year |
+-----------+------+
|       170 | 2019 |
+-----------+------+
1 row in set (0.01 sec)

The idea is to end up with one result for 2017, 2018, 2019, combining both results.

Solution

Ended up with:

> select count(distinct(sciper)), year FROM (SELECT sciper, date_format(`date`, "%Y") as year FROM massfacturation.jobs UNION SELECT sciper, date_format(`date`, "%Y") as year FROM massfacturation_old.jobs) AS j GROUP BY year;
+-------------------------+------+
| count(distinct(sciper)) | year |
+-------------------------+------+
|                     160 | 2017 |
|                     262 | 2018 |
|                     188 | 2019 |
+-------------------------+------+
3 rows in set (0.00 sec)

Thanks !


texts: Why do function inputs need an extra set on parens in this Postgres function?

texts:
This works. However, it fails with a syntax error if the "extra" parenthesis in the second function are omitted. Why?

create function epochSeconds(timestamp) returns int as $$
  select extract(epoch from $1)::int;
$$ language sql;

create function tableRowEpochSeconds(integer) returns int as $$
  select epochSeconds((select max(baz.created) from baz where baz.id = $1));
$$ language sql;

Does not work:

<same other code>
select epochSeconds(select max(baz.created) from baz where baz.id = $1);
<same other code>


texts: SQL server restore readonly file group without backup from .ndf file

texts:
We have SQl Server database with ReadOnly file group. Common file groups except readonly are backuped regulary.

We restore the database from backup but readonly file group has not restored, its *.ndf file is not corrupted. Is it posiible to attach this file and reset file status RECOVERY_PENDING?

The example script:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')
    DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1', FILENAME='D:\data\TestDB_Part1.mdf',
SIZE=3, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2', FILENAME ='D:\data\TestDB_Part2.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part3
(NAME = 'TestDB_Part3', FILENAME ='D:\data\TestDB_Part3.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part4
(NAME = 'TestDB_Part4', FILENAME ='D:\data\TestDB_Part4.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part5
(NAME = 'TestDB_Part5', FILENAME ='D:\data\TestDB_Part5.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );
GO
USE TestDB;
GO

CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (10,20,30,40);
GO

USE TestDB;
GO
--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2, TestDB_Part3, TestDB_Part4, TestDB_Part5);
GO

--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (ID);
GO

--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);
GO

--- Step 6 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4
VALUES (32,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (42,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (52,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3
VALUES (22,GETDATE());
GO

--- Step 7 : Test Data from TestTable
SELECT * FROM TestTable;
GO

--- Step 8 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part2] READONLY
GO

-- Backup rw and ro separately
BACKUP DATABASE [TestDB]
READ_WRITE_FILEGROUPS
TO  DISK = N'D:\data\TestDB_RW.bak'
WITH NOFORMAT, INIT,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- list backup files
RESTORE FILELISTONLY from disk =  N'D:\data\TestDB_RW.bak'

RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'D:\data\TestDB_RW.bak'  WITH  FILE = 1,
      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.ndf',
      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.ndf',
      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.ndf',
      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.ndf',
      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.ndf',
      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10
GO


texts: DROP USER takes too long when there are many users

texts:
On a SQL Server 2014 instance with enough RAM and fast disks, there are over 160 users that have access to a database. For some reason unbeknownst to me, running the command DROP USER [username] in this database takes up to 5 seconds per user.
Remapping users to logins and restoring their permissions is very fast.
Within the context of refreshing DEV databases from production, I have to drop and recreate all database users.  So yes dropping the database users and recreating them is necessary.
How do I speed up the DROP USER command?
Remember, I have to run it over 160 times for the instance I'm writing about.
This is the SQL I'm using:
DECLARE drop_user_cur CURSOR FOR
SELECT name FROM #drop_users

OPEN drop_user_cur
FETCH NEXT FROM drop_user_cur INTO @user

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'use [' + @db_name + '] DROP USER [' + @user + ']'
    BEGIN TRY
        print @sql
        EXECUTE(@sql)
    END TRY
    BEGIN CATCH
        print 'ERREUR : ' + @sql
    END CATCH
    FETCH NEXT FROM drop_user_cur INTO @user
END

CLOSE drop_user_cur
DEALLOCATE drop_user_cur

The problem is not coming from cursor; it's the actual DROP USER that's taking up to 5 seconds.
Using sp_whoisactive, the wait_type is NULL.

Don't pay attention to the duration, the DROP and CREATE USER were being run in a WHILE loop which is why it's saying more than a minute.
Profiler shows over 125,000 reads to execute DROP USER.
Service Broker is not enabled.


texts: Speed of WAL send/receive when replicating

texts:
1/ Description:

I have a cluster include two machines

Master: postgresql 9.3 run on Centos
Slave : postgresql 9.3 run on Centos

Master ----> Slave : relpica from Master to Slave by asynchronous method (M send WAL, S receive WAL)

2/ Question:

How can I monitor (catch) speed of WAL (ex: 1MB/s) is sent from Master to Slave ?


texts: Performance of Non Clustered Indexes on Heaps vs Clustered Indexes

texts:
This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap with a non clustered index on the same key columns as the CI table.

Generally the clustered index option performed better in the tests as there is only one structure to maintain and because there is no need for bookmark lookups.

One potentially interesting case not covered by the paper would have been a comparison between a non clustered index on a heap vs a non clustered index on a clustered index. In that instance I would have expected the heap might even perform better as once at the NCI leaf level SQL Server has a RID to follow directly rather than needing to traverse the clustered index.

Is anyone aware of similar formal testing that has been carried out in this area and if so what were the results?


texts: Help to create indexes to improve query performance and reduce report generation time

texts:
The database in SQL Server 2008 has these tables below - the main table being the Master:

Master

A_ID (PK)
SYSSerialNumber
Bootlog
SequenceNumber
FileName
Cfg
Unique(serialnumber,sequencenumber)

SYSTEM  (Master:SYSTEM -1:1)

SYS_ID
A_ID (FK)
SYS_Product
SYS_Source
SYS_iVer

MEMORY (Master:Memory - 1 to N)

MEM_ID
A_ID (FK)
MEM_Instance
MEM_Name
MEM_Size
MEM_CE
MEM_UE
unique(ahs_id,mem_instance)

Processor  (Master:Processor - 1 to N)

PROC_ID
A_ID (FK)
PROC_Instance
PROC_FamilyID
PROC_Version
unique(a_id,proc_instance)

BC (Master:BC - Master.SYSSerialnumber = BC_SerialNumber )

BC_ID
BC_WWSiteName
BC_SerialNumber

The view was created for tables Master,System, BC and Memory: MSBC_Mem

select
    M.a_id, M.SYSSerialNumber, M.FileName, M.Cfg,
    M.SYS_Product, M.SYS_Source, M.SYS_iVer,
    B.BC_WWSiteName, B.BC_SerialNumber,
    MEM_Instance, MEM_Name, MEM_Size, MEM_CE,MEM_UE
from
    dbo.BC AS B
LEFT OUTER JOIN
    dbo.MASTER AS M ON M.SYSSerialNumber = B.BC_SerialNumber
LEFT OUTER JOIN
    dbo.SYSTEM AS S ON M.A_ID = S.A_ID
LEFT OUTER JOIN
    dbo.Memory AS Mem ON M.A_ID = Mem.A_ID_
WHERE
    (M.A_Cfg = 1)

Using the above view as a base for all queries for the report:

Query:

 Select
    count(distinct(sysserialnumber)), BC_WWSitename
 from dbo.MSBC_Mem
 where sys_product = 'xyz'

My question: SYS_Product & SYS_Source are commonly used in the WHERE clause in almost all the queries. What kind of index do I have create on the parameters?
To get a normalized values based on MEM_Name for each SYS_Product where MEM_CE > 0 :

View: NRMEM_NameCE

SELECT
    A.SYS_Product, A.MEM_Name AS NMCEMEM_Name, A.MEM_NameCount,
    CAST(A.MEM_NameCount AS float) / B.MEM_NameCount * 100 AS VendorPercentage,
    CAST(A.MEM_NameCount AS varchar(8)) + ' / ' + CAST(B.MEM_NameCount AS varchar(8)) AS somestring,
    C.MEM_NameCount AS TotalUnits
FROM
    (SELECT
        SYS_Product, MEM_Name,
        SUM(CASE WHEN MEM_CE > 0 THEN 1 ELSE 0 END) AS MEM_NameCount
     FROM
        dbo.MSBC_Mem
     WHERE
        (MEM_Name IS NOT NULL)
     GROUP BY
        SYS_Product, MEM_Name) AS A
INNER JOIN
   (SELECT
       SYS_Product, MEM_Name, COUNT(MEM_Name) AS MEM_NameCount
    FROM
       dbo.MSBC_Mem
    WHERE
       (MEM_Name IS NOT NULL)
    GROUP BY
       SYS_Product, MEM_Name) AS B ON A.MEM_Name = B.MEM_Name AND A.SYS_Product = B.SYS_Product
INNER JOIN
    (SELECT
        SYS_Product, COUNT(MEM_Name) AS MEM_NameCount
     FROM
        dbo.MSBC_Mem
     WHERE
        (MEM_Name IS NOT NULL)
     GROUP BY
        SYS_Product) AS C ON A.SYS_Product = C.SYS_Product

From the results above:

select * from NRMEM_NameCE where SYS_Product='xyz'

The above result which is :

**SYS_Product   NMCEMEM_Name    MEM_NameCount   VendorPercentage somestring TotalUnits**
DL          DIMM             10 0      0              0 / 79    32909
DL          DIMM             4  2      0.04419        2 / 4525  32909
DL          DIMM             8  0      0              0 / 995   32909

This entire query takes long since there are no indexes on the Memory and System tables.

Could you please suggest as to how I can include indexes on the memory table and improve the performance?

Thanks.


texts: ORA-01422 Error Occurred When I Call Procedure with Number Parameter

texts:
I want to write PL/SQL Procedure that updates salary which is less than 2000. I wrote this procedure.And when i call it with integer id 'ORA-01422: exact fetch returns more than requested number of rows' error is thrown by TOAD. My procedure is like below:

DECLARE
PROCEDURE update_salary(ID customers.id%type) is
   c_sal  customers.salary%type;
BEGIN
  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = ID;
   dbms_output.put_line ('Before update operation salary is:' || c_sal);

  --dbms_output.put_line ('Parameter :' || ID);

   IF (c_sal <= 2000) THEN
      UPDATE customers
      SET salary =  salary + 1000
      WHERE id = ID;
      dbms_output.put_line ('Salary updated');
   END IF;

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id=ID;
   dbms_output.put_line ('After update operation salary is:' || c_sal);
END;

BEGIN
update_salary(1);
END;
/

I print parameter id with dbms_output. The parameter is coming correctly. How can i fix this error !


texts: Inefficient queries on partitioned tables

texts:
Consider a scenario in SQL Server that includes a partitioned table with over 500 partitions. The number of partitioning column values in a temp table are less than 10. We join the temp table to the partitioned table directly on the partitioning column. (Not using $PARTITION, just purely column values.) The query includes joins to other dimension tables in a typical star-schema type way.

Furthermore, there are no non-clustered indexes on the table. The leading column in the clustered PK (ignoring the partitioning key id which is always leading) is a column that joins to one of the star-schema dimension tables and will include a filter. However, the filter on the dimension table would not be selective enough to cause an index seek, if there was an index, if the partitions were just a single table.

So, given the above, why is it that queries on this partitioned table do not always use partition elimination? When would it ever be more efficient to scan a partitioned table than just the partitions required?

At worst a partitioned table with one partition would be the same, ignoring the cost to determine the partition elimination etc.


texts: PSQL not executing SQL command - Powershell

texts:
I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the script and should execute automatically). Any help would be greatly appreciated.

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB
.\psql --% -c "copy {'SELECT * FROM myTable';} TO    'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"


texts:
Getting oracle Internal error in oracle database 11gr2:

  ORA 600 [kkpogppi1]

It shows everyday in incidents but whenever i check oracle logs it shows nothing and when i open problem details it shows me:

So is this internal error and if yes then how could i solve this error and also is this any harmful to database?