texts: Restoring a MS SQL Server database on Linux

texts:
I am running Manjaro Linux and I am trying to restore a Microsoft SQL Server database from a .bak file.

I am trying to follow the official documentation, but it doesn't seem to work!

➜  ~ sudo sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [bejebeje] FROM DISK = N'/home/jwan/Dropbox/projects/personal/bejebeje/backups/2018-04-13/BejebejeDb2018-04-13.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS =5"
[sudo] password for jwan:
Password:
Msg 3201, Level 16, State 2, Server jwan-pc, Line 1
Cannot open backup device '/home/jwan/Dropbox/projects/personal/bejebeje/backups/2018-04-13/BejebejeDb2018-04-13.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server jwan-pc, Line 1
RESTORE DATABASE is terminating abnormally.

I also tried without the sudo and I got the same thing. What am I doing wrong?

UPDATE

I tried the suggestion in the comments, and it still didn't work, here's what I got:

➜  ~ sqlcmd -S localhost -U SA -Q "RESTORE DATABASE [bejebeje] FROM DISK = N'/home/jwan/sqlbackup/BejebejeDb2018-04-13.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS =5"
Password:
Msg 3201, Level 16, State 2, Server jwan-pc, Line 1
Cannot open backup device '/home/jwan/sqlbackup/BejebejeDb2018-04-13.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Server jwan-pc, Line 1
RESTORE DATABASE is terminating abnormally.
➜  ~ ls -l sqlbackup
total 5720
-rwxrwxrwx 1 jwan jwan 5854720 Apr 15  2018 BejebejeDb2018-04-13.bak
➜  ~


texts: Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?

texts:
If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In this case, I am inserting all data at once, and there will never be any records that are deleted.

What if the scenario is slightly different: the table is WITHOUT ROWID, and the PRIMARY KEY is a clustered index on several columns -- does that change the wisdom here?


texts:
I have a SQL Server table with Customer Names and Phone Numbers. Customers will be an owner or party member.
There are chances where owners and party member give the same mobile number. I wanted to check the table and if the mobile number is duplicated then I need to remove it from the table. How can I achieve it?
CustomerName   MobileNumber
----------------------------
AAA             +447000000000
BBB             +447000000000
CCC             +447000000000

Result I expect:
CustomerName   MobileNumber
----------------------------
AAA             +447000000000
BBB             NULL
CCC             NULL


texts:
I'm planning to upgrade 3 instances of Oracle (all same Oracle Home) from 11.2.0.3 to 11.2.0.4. I'm going through the installer and I get to the question about Upgrade or install just the database software. I only want to upgrade one instance initially and leave the others running on 11.2.0.3 in parallel. My question is, what's the difference between:

Install database software only
Upgrade an existing database

I really don't want to touch the other SIDs on there.


texts: Trigger function to update data from one table to another table with different servers in PostgreSQL

texts:
There are two databases with different servers in PostgreSQL in PGAdmin.
One database db1 of server s1 and another database db2 of server s2.
The condition is: if the field id.table1 of db1 is same as id.table2 of db2 (of the other server) then one of the field 'status' of table2 of db2 which has its value as submit should changes to open in PostgreSQL in PGAdmin.
I need to write trigger function for this.


texts: Seting MAXDOP for SQLserver 2016 and above

texts:
I was going through MS documentation reg: setting MAXDOP starting SQL2016 as that Automatic soft NUMA enabled by default and getting confused on how to make the changes to current SQ2014 and SQL2012 versions:

MSDN link-- https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-2017#Guidelines

Current SQL2012/2014 setup:

Logically Processors =80
Numa Node=4
Physical RAM= 512GB
MAXDOP=8 and CTOP=5
HyperThreading enabled ='True'

So whats the change after moving them to SQL2016 and above? Will MAXDOP of current setup does not hold any more and needs to tune ? if yes what is MS proposing?

Please share your experiences on setting this up.


texts: Different services using different port pointing to same datadir folder

texts:
Let say I have already installed service MySQLa with mysqla.ini (port number=3306) setting and datadir folder in ...\data

Is it possible then, I install another service MySQLb with mysqlb.ini (port number=3307) setting and sharing datadir folder in ...\data
?

Would there be any conflict with the system schema (mysql, information_schema and performance_schema)?

I have tried locating the datadir into different folder (...\dataa and ...\datab) and it is working well.


texts: Very large SQL Server 2016 result sets (over 75 GB) to a CSV file?

texts:
What is the best way to get very large SQL Server 2016 result sets (over 75 GB) to a CSV file? The engineers need this output to look for correlations.

The bcp route for a 73.5 GB file filled up tempdb and started crashing other applications, including the ETL process.

Our users want to export up to 500 GB.

What process would use the least amount of resources so that other applications keep running?


texts: How long can this ALTER TABLE statement take?

texts:
Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL

Suppose

there are 45 GB data space and 2GB index space;
about 3 million rows in this table;
column YYY is varchar(8000) now and can be updated (is writable).
the table has about 30 other columns.
There are about 3000G data from other database & tables on this machine.

Some other information:

99.99% of rows have NULL in this varchar(8000);
the web application may hit this table 5 times per minute;
the hardware is enterprise level (8 core CPU and 256GB RAM).
there are other tables and database on this machine, about 3000GB data.

Relevant @@VERSION details:

  Microsoft SQL Server 2014 - 12.0.4422.0 (X64) Enterprise Edition (64-bit)


texts: Table scan estimates when there are no stats on the table

texts:
Suppose I create a table with 1000 rows but don't gather statistics on the table or run a query that should trigger an automatic statistics creation:

create table test( a int )
go
insert into test select 1
go 1000
select * from test
sp_helpstats 'test','all'

When I check the plan for the select, I can see that the estimate is accurate and it's 1000.  Where does that estimate comes from?  Does it have anything to do with sys.partitions rows column?


texts: Maintain Up-To-Date Copy of Very Large Table if Clustering/Partitioning/Moving Operation Needs to be Killed/Rollback

texts:
I've appended the following information in a previous post but I asked the 'why' instead of the 'how'.

So here's the question...

SQL Server 2014, CU6. 4.5 TB database, Simple recovery. The most important/accessed table is 1.6 TB, located on the primary filegroup. It's currently about 95% fragmented and so most all queries need to do a full scan of its 600M+ rows. Not surprisingly, it's an IO bottleneck and I'm unable to defrag in any acceptable window of time. My goal is to move this table out of the .mdf and cluster/partition onto its own filegroup (ONLINE=ON & SORT_IN_TEMPDB=ON). This new filegroup would be physically isolated on a dedicated disk array.

But I'm very concerned about any issue that may cause (or necessitate) a rollback (things such as a power failure, deadlocking, corruption, unacceptably slow performance during the operation, etc.).

Further, given the length of time this clustering/partitioning/moving process may take, any backups taken during that time would be useless. They'd immediately go into recovery after being restored and, again, rollback/rollfoward time would be unacceptable. Same deal if I were to turn on full logging and use transactional replication or log shipping -- worthless secondary or subscriber.

How would you handle this process?

I've thought about making a copy of the table, triggers on the source to keep the copy up-to-date.

I've got large buffers of space on all volumes involved. I want to pull the trigger. Boss says 'not yet'.

Thanks for reading...

-Greg


texts: Check if two datetimes are between two others

texts:
Today I'm facing with problem, which I can't solve.

I need to check if two datetimes (2019-04-08 13:30:00 - 2019-04-08 15:30:00 ) are between or overlap two others (2019-04-08 14:00:00 - 2019-04-08 16:00).

Imagine that those datetimes are event's date. We can see that there is an event between 14:00 and 16:00 so we cant' have other between.

I've tried something like this:

SELECT * FROM Rezerwacja WHERE
(('2019-04-08 13:30:00.000' between Start and Stop) OR
('2019-04-08 15:30:00.000' between Start and Stop))

How to check if there is an "event" already ?


texts: How do I plan out this MongoDB database?

texts:
Here's a simple hierarchy of how my data would be stored.

jobs/
|---- movie1/
|      |---- shot1/
|      |       |---- shot1_fx/
|      |       |       |---- shot1_fx_v001/
|      |       |                |---- shot1_fx_v001.mov
|      |       |                |---- shot1_fx_v001.project
|      |       |---- shot1_anim/
|      |       |       |---- shot1_anim_v023/
|      |       |                |---- shot1_anim_v023.mov
|      |       |                |---- shot1_anim_v023.scene
...

Now I need to plan out the database

What I have in mind:

Each movie/project gets its own database.
In that database will be 4 collections. To relate it to the tree above:

Collection 1: Shots (eg: shot1/)

Collection 2: Working Types (eg: shot1_anim/)

Collection 3: Working Versions (eg: shot1_anim_v023/)

Collection 4: Working Sources (eg: shot_anim_v023.mov)

I see some potential problems with this setup. One is that, since each project is a separate database I have no way to know what all the active jobs are in my pipeline. There is no database that contains data of all the jobs, only one per job.

So if I decide to instead create a global database that holds everything, then would every project be a collection? And everything else (Collections 2-4) would just be merged into one, and I would have to specify their types as metadata?

I'm kind of stuck on this and wan't to make the best choice before I make the tools and scripts depend on a certain structure.


texts:
Can anyone just help me with a script to find similar SQL jobs in two different instances/servers?

By similar I mean jobs with the same name, which do the same task, with the same frequency and schedule.


texts:
i am facing the  below issue

Table structure :

some_table CREATE TABLE `some_table ` (
`wallet_id` int(11) NOT NULL AUTO_INCREMENT,
`wallet_amount` double(10,2) NOT NULL,
`actual_wallet_amount` double(10,2) NOT NULL,
`customer_id` int(11) NOT NULL,
`last_update_on` datetime NOT NULL,
`redeemption_date` date NOT NULL,
`expiry_date` date NOT NULL,
`wallet_status` varchar(200) NOT NULL,
`order_id` varchar(250) NOT NULL,
PRIMARY KEY (`wallet_id`)
) ENGINE=InnoDB

query :
SELECT *
FROM  some_table
WHERE redeemption_date <= CURDATE( ) - INTERVAL 6 MONTH
AND wallet_status !=  "EXPIRED"

Desired Result:

Need the get all the unique customer_id with latest last_updated_on
Need the get all the records in the table which have only one enter last 6 months and above
when adding group by clause on customer_id i am not getting latest
updated customer_id
i need all the latest unique customer_id

please help thanks in advance.


texts: generate_series for multiple record types in postgresql. Fill missing with last previous value

texts:
my question is closely related to the following question asked in this thread generate_series for multiple record types in postgresql. The only difference is, that instead of only inserting missing values, I want to fill the gaps with the last previous value. To illustrate it with the same example. I have two tables.

CREATE TABLE pests(id,name)
AS VALUES
  (1,'Thrip'),
  (2,'Fungus Gnosts');

CREATE TABLE pest_counts(id,pest_id,date,count)
AS VALUES
  (1,1,'2015-01-01'::date,14),
  (2,2,'2015-01-02'::date,5);

The desired output should be nearly identical. However, I want to fill missing values with previous values, if possible. So the second entry for Thrip should be 14, since a previous value exists and the first value of Fungus Gnats should be 0, since no previous value exists. Is it possible to do this using Postgres?

expected results

name         | date       | count
-------------+------------+-------
Thrip        | 2015-01-01 | 14
Thrip        | 2015-01-02 | **14** <- fill with existing previous value.
....
Fungus Gnats | 2015-01-01 | 0
Fungus Gnats | 2015-01-02 | 5
...


texts: MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows

texts:
I have this mysql table:

CREATE TABLE `codes` (
  `code` bigint(11) unsigned NOT NULL,
  `allocation` int(11) NOT NULL DEFAULT '0',
  `used` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`code`),
  KEY `allocation` (`allocation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When it is fully up and running it will hold anywhere between 100 million to 300 million codes that have been randomly generated from a number between 1 and 10 Trillion.

To fill the table I have this stored procedure:

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_codes_v4`(
    IN bf_codes_to_generate BIGINT,
    IN bf_lower_limit BIGINT,
    IN bf_upper_limit BIGINT,
    IN bf_allocation_num INT
)
BEGIN

    SET @Codes = bf_codes_to_generate;
    SET @Lower = bf_lower_limit;
    SET @Upper = bf_upper_limit;
    SET @Allocation = bf_allocation_num;

    SET @qry_rand = 'SELECT ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) INTO @Random';
    PREPARE qry_rand_stmt FROM @qry_rand;

    SET @qry_insert = 'INSERT IGNORE INTO `codes` (`code`,`allocation`) VALUES ( @Random, @Allocation )';
    PREPARE qry_insert_stmt FROM @qry_insert;

    START TRANSACTION;

    WHILE @Codes > 0 DO

        EXECUTE qry_rand_stmt;
        EXECUTE qry_insert_stmt;

        SET @Codes = @Codes - ROW_COUNT();

    END WHILE;

    COMMIT;

    DEALLOCATE PREPARE qry_rand_stmt;
    DEALLOCATE PREPARE qry_insert_stmt;

END;;
DELIMITER ;

What this does is pick a random number between the given bounds and insert it into the table.

We currently use this stored procedure to insert anywhere between 500K to 5M rows at a time. While it works it starts to get very slow as more rows already exist in the table.

Once we get to 10M rows already in the table the generation process slows to about 1000 rows per second. As we ultimately plan to store 100M to 300M codes in this table the insertion process will take even longer at that point. Basically this table is not scaling well.

Is there anything that can be done to make this process scale better?

Here are some answers to questions I think you might ask

Q: Why an index on allocation column?
A: Each time a batch of rows are inserted we give it an allocation number. We need to be able to quickly get allow rows out that have a given allocation number.

Q: Why the use of a transaction?
A: Apparently this stops the index constantly been flushed to disk while inserting codes and in our testing speeded inserts up significantly. Also, while not implemented yet, we would like to be able to put a kill switch in place that can cancel a batch insert at any point in time.

Q: Why don't you split the table into multiple tables for example 1-1T goes into table one, 1T-2T goes to table two and so on?
A: We may have to look into doing this but I'd like to see if what we have now can be improved.

Q: Is there anything else we should know?
A: This table will be constantly been used as a lookup to check if codes exist and if they have been used and will be SELECT heavy. Any solution mustn't block this table from being read from and try and not slow its read performance too much.


texts: mysql_query() is NOT passing the command to the MySQL server using the C API

texts:
I have this question asked but after a deeper investigation I realized that hanging is not the issue. After reading this thread I decided to ask a new question.

For example I pass this string to the mysql_query() function:

SELECT 1

When I run several thousand of this command via mysql_query() then the command is NOT submitted to the MySQL server!

I checked the MySQL log file and I couldn't believe it but the command wasn't there and therefore the mysql_query() does never return because it doesn't receive any answer from the server.

MySql is writing the command into the log file BEFORE it gets executed. I tried it with SELECT SLEEP(10).

Note:

I know, it does mean that much but it was working for 3 years without any issue on SLES 11.3/XenServer 6.5. Note: This is an MCVE where it doesn't work as well!

Since I moved it to SLES 12.3 and XenServer 7.4 the problems started.

The C++ code is very simple - I have checked it for 3 weeks before I wrote this question. The point is that this issue starts after ~10k queries. The VM has still ~3 GB RAM free.

Note regarding an MCVE:

I created an MCVE which works for more than 500k executions of the MySQL command.

When I insert this MCVE code into my project and comment my existing main() in order to execute the main function from the MCVE then after few thousand [or even only hundred] of executions suddenly the MySQL command is again not submitted to the MySQL server.

To be more specific: Only static objects of my project are initialized before the embedded MCVE runs.

This must be something very strange regarding these static initializations because no other line of code is running.

Question:

Any idea what is going on? Especially the fact that the command is apparently [why should I doubt the MySQL Server log file] not submitted from the C API client to the MySQL server is really disturbing me.

Technical data:

SLES 12.3 running on XenServer 7.4 as VM
Compiled with gcc 7.3 using -std=c++17 and almost every warning option.
Using the default SLES 12.3 glibc 2.22 with all SLES updates installed.
Connecting to MySQL Community Server 5.7.21
C API client 5.7.21


texts: Is this a new common pattern in Oracle Where exists ( Select NULL FROM...)?

texts:
Years ago, it was common to write

where exists (Select * from some_table where some_condition)

Last year I noticed that many t-sql scripts switched to using the number 1 instead of the star

where exists (Select 1 from some_table where some_condition)

just on SO I saw this  Oracle example

WHERE EXISTS (SELECT NULL FROM ...

Is this a common  pattern with Oracle?
And which are the performance arguments to use something like this.


texts: Restore without specifying location for each file

texts:
I am working with a database that has a dynamic filegroup structure. The number of filegroups and filegroup files can and do change.

Is there a way to specify one data and one log folder in a script that will restore all files to those folders?


texts:
I have a two users, user1 and user2.

User1 has sysadmin rights and I have created a database level trigger through sa.

Now I am trying to create a table through user2 who has db-ddladmin rights and db_datareader.

I'm getting the error:

 'Msg 297, Level 16, State 1, Procedure TR_CaptureDBChanges, Line 35
The user does not have permission to perform this action.

Code:

CREATE  TRIGGER [TR_CaptureDBChanges]
ON DATABASE
FOR  CREATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE,CREATE_TABLE, ALTER_TABLE,DROP_TABLE,CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_FUNCTION, ALTER_FUNCTION,
         DROP_FUNCTION
BEGIN
DECLARE @ed XML
SET @ed = EVENTDATA()
DECLARE    @ip VARCHAR(32) =   (SELECT client_net_address FROM sys.dm_exec_connections  WHERE session_id = @@SPID);

    INSERT INTO DBChangesLogs ( EventDate,DBName)VALUES(GetDate(),@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'))
END


texts: split a row into multiple rows in sql server according to a specific column

texts:
I have table a having fields id,val and identical table b having fields id,val.

when i am writing an inner join for different data in rows

  SELECT *  FROM a left join b on a.id = b.id   WHERE ( a.id != b.id or
  a.val != b.val)

i am getting like below

i want to split this table row into two rows. Help me to write a query to split this data and getting an output table like below

Help!


texts: Does sp_droparticle @force_invalidate_snapshot=1 invalidate the entire snapshot?

texts:
Forgive this simple SQL Server question, but if I just want to remove a single stored procedure from snapshop replication (2008 SQL Server R2), if I call sp_droparticle, will it invalidate the entire snapshot, or just the snapshot on that article?

IE, I've got a big database, and I don't want to force a full snapshot generation again, does sp_droparticle only affect the snapshot for that single object?


texts:
We have some AWS RDS SQL Server instances that we are setting up.  Some of these will be Multi-AZ for HADR.

We know that RDS will take system level backups and snapshots.  We are wanting to perform individual backups into S3 buckets so we can use the backups for stage and dev restores on central dev server.

We are using SQL Server Agent jobs to perform these backups but the jobs on SQL Server Agent read successful even if the backup to the S3 bucket fails.

What I am curious about is if anyone has figured out a solution to monitor these backups to the S3 buckets since SQL Server Agent jobs are not showing the failure correctly.  Only possible solution I can think of is Powershell but would love to hear anyone else's solution if they have one.


texts: I'm newbie for SQL DBA , everyday at least once I've got a deadlock issue in SQL server 2012 server which is using Merge statement. There are no clause like NOLOCK, UPDLOCK, HOLDLOCK has been used in the merge statment. Its a multi user environment where the Biztalk reads the xml and save data into SQL Server. Per Minute Biztalk reads 300 xml messages. Since its a production server i can't implement anything just like that without doing research, but i haven't got any idea on how to resolve this issue. Recently i got an issue like Two xml messages are trying to update data to a table and trying to use the same index and error-ed out. Could anyone help me how to get away with this issue?


texts: 01/01/1753 date is corrupt in Excel after export from SSRS

texts:
The date 01/01/1753 appears fine in the SSRS 208R2 report preview, but when the report is exported to Excel 2010, it appears as ########### and the value in the cell is -53689. All other dates come across just fine.

Apparently Excel accepts 01/01/1753 input and displays the dates correctly when manually entered.

Is there any method to display the SSRS reports containing the 01/01/1753 dates in Excel correctly as well?

PS: I was not able to find any relevant information on this using google. It seems to ignore the numbers and return mostly the articles on how to sort the worksheets by date.


texts: Moving Oracle / Oracle datafiles to different file system on Linux

texts:
We have Oracle installed on /root, which is now running out of space due to increasing data in Oracle database. On the same machine we have another file system with free space.

I want to know the mechanism by which we can move Oracle / Oracle data files to this new file system and continue to use the same data files.

Appreciate any formal steps reference.

It is Oracle 12c on RHEL


texts: How mysqlcluster can be durable and consistent?

texts:
I'm little noob and learning on how to use mysqlcluster,i have 1 management 2 datanode and 2 sqlnodes.According to a book "mysql cookbook" -alex davies and localcheckpoints and globalcheckpoints

By default, a running MySQL Cluster will commit a Local Checkpoint (LCP)—a copyof all the local data held on disk—every time 4 MB of changes are made to the cluster (since
the previous LCP) and global checkpoint (all transactions that have occurred since the last LCP) to disk every two seconds. if the data are fragmented and parts of large tables are striped on those datanodes(database "A" is 1000G and may be divided into two 500G parts) ,

Q1) How can a database still consistent and durable if any one of the datanode server is crashed ?

With Reference to restore DB with ndb_restore if i take backup
using "start backup" on management node, it will create backup on the backup directory on each Data Nodes but each backup will only hold the different fragment of table data instead of complete backup(backup taken by mysqldump). I tested backup and it was true where complete database size was around 44G and the backupdir was around 22G on each datanode backup directory. I'am confused and please correct me if I have misconception


texts: Temporal Table that doesn't use system time

texts:
We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables.

For the BeginDate, we want it to be dependent on transaction date and not the current getdate time. We are reprocessing a history of transactions. Example below, customer has gym or bank status, and goes from inactive, to active, or pending, dependent on transaction date.

We currently have this.

CREATE TABLE dbo.Department
(
    CustomerId int primary key,
    MembershipStatus int,
    TransactionDate datetime
 );

We we would like to create a table like this.

CREATE TABLE dbo.DepartmentHistory
(
    CustomerId int primary key,
    MembershipStatus int,
    TransactionDate datetime,
    BeginDatetime datetime,
    EndDatettime datetime
 );

Example usage would be:

first Customer Transaction on 3/5/2018 as Pending P

    +------------+--------+-----------+---------+
    | CustomerId | Status | BeginDate | EndDate |
    +------------+--------+-----------+---------+
    | 1          | P      | 3/5/2018  | NULL    |
    +------------+--------+-----------+---------+

second transaction is 4/21/2018 is Active A

    +------------+--------+-----------+-----------+
    | CustomerId | Status | BeginDate | EndDate   |
    +------------+--------+-----------+-----------+
    | 1          | P      | 3/5/2018  | 4/21/2018 |
    | 1          | A      | 4/21/2018 | NULL      |
    +------------+--------+-----------+-----------+


texts: Best practice for exposing record IDs to API users

texts:
I am extending an API to include the ability to retrieve and update a specific record after the record is created by the user.

Currently, these records are created with an auto generated integer key, for example 49300.

Users need a way to access a specific record but I don't like the idea of exposing this ID to end users, because it will reveal information about my schema, for example I can assume there is also a record of ID 493299.

I'm considering a GUID alongside the auto generated integer, but I have concerns about performance as the table size is ~5 million records, growing by 1 million/year.

Is there reason for concern adding a table this size, or the subsequent SELECT query on the GUID when retrieving/updating the record?

I'm using AWS RDS MySQL.

Thanks!


texts: I use sqlDBx to connect oralce 12c, cannot connect

texts:
My computer is 64 bit.The sqlDbx that I am in the official website to download.

I am a Chinese, hope ace to help me,thanks a lot !


texts: UPDATE vs SELECT returns different Execution Plan

texts:
In my DB table, where ID is the PK, if I use this:
EXPLAIN SELECT * FROM table WHERE ID = 3;

The type and the ref columns of the explain output table are both equal to const.
Now, when I use this:
EXPLAIN UPDATE table SET data =xyz WHERE ID = 3;

The type is range and the ref is NULL. Why is that so? What is the precise meaning of the ref column anyway? I really can't find a proper understandable definition online for that column which explains the different value in this example. The key column uses the primary key and the possible_keys also lists the primary key only in both cases, so I don't understand; I'm using the PK in both cases, but in the UPDATE clause less efficiently, although it's basically exactly the same WHERE clause?


texts: Design a betting system for Fifa World Cup Russia 2018

texts:
Currently, I am working on a betting system for the next Fifa World Cup Russia 2018.
For this system, I have some strict points(constraints):

Authentication will be via external api, so no password or sensitive informations about users.
There are 4 types of betting: bet on a match, a bet on who will win the world cup, and a last one for the winner of each group.
To determine if a user won a bet, there are 3 possibilities: first one if the score he put is exactly the same, then he will win 4pts, the second one if the score is wrong, but the difference of goals is right then 3pts, the last one if one possibility from the last, then we check if he chose the right winner of the match and he gets 2pts. Otherwise, no points.
For matches which can contain penalties (from second roud), there is an extra betting (after typing score for the match, there is possibility to type who will win if they play 120min).
I want to have a statistic for the admin (games won or lost, and users bets ..etc).

As you can see in the picture below, that is what I tried out (not a complete version, i am stuck here) :

What can be the best way to store the extra bets(as said, there are extra bets, such as the winner of this cup, the winner of each group, and the winner in the case the match goes to penalties)?


texts: exceeds the maximum allowed database size when export database oracle 18c

texts:
I have worked on Oracle Database 18c Express Edition, while I try to export database schema by using expd command, I have an issues

  ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

Full log:

Export: Release 18.0.0.0.0 - Production on Fri Sep 20 15:48:06 2019
Version 18.4.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "TEST_DATA"."SYS_EXPORT_SCHEMA_02":  TEST_DATA/******** schemas=TEST_DATA directory=TEST_DATA dumpfile=TEST_DATA.dpdmp logfile=TEST_DATA.log FILESIZE=50GB
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST_DATA"."SYS_EXPORT_SCHEMA_01"        270.9 KB    1464 rows
. . exported "TEST_DATA"."ZP_CR60071"                      0 KB       0 rows
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_DATA [TABLE_DATA:"TEST_DATA"."SYS_EXPORT_SCHEMA_02"]
ORA-00604: error occurred at recursive SQL level 2
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 12372
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_STATS", line 39134
ORA-06512: at "SYS.DBMS_STATS", line 38422
ORA-06512: at "SYS.DBMS_STATS", line 37316
ORA-06512: at "SYS.DBMS_STATS", line 15610
ORA-06512: at "SYS.DBMS_STATS", line 1785
ORA-06512: at "SYS.DBMS_STATS", line 15279
ORA-06512: at "SYS.DBMS_STATS", line 15410
ORA-06512: at "SYS.DBMS_STATS", line 37251
ORA-06512: at "SYS.DBMS_STATS", line 38141
ORA-06512: at "SYS.DBMS_STATS", line 38570
ORA-06512: at "SYS.DBMS_STATS", line 39116
ORA-06512: at "SYS.KUPW$WORKER", line 4200

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FF2F785540     32868  package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
000007FF2F785540     12400  package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
000007FF2F785540      4617  package body SYS.KUPW$WORKER.UNLOAD_DATA
000007FF2F785540     13474  package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
000007FF2F785540      2410  package body SYS.KUPW$WORKER.MAIN
000007FF2BA73120         2  anonymous block

KUPW: Method: 1
KUPW: Parallel: 1
KUPW: Creation level: 0
KUPW: Sequence number: 0
BULK COLLECT
BULK COLLECT
KUPW: In procedure BUILD_OBJECT_STRINGS - non-base info
KUPW: In PROCESS_TABLE_DATA_METRICS
DBMS_STATS.GATHER_TABLE_STATS
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-00604: error occurred at recursive SQL level 2
ORA-06512: at "SYS.DBMS_STATS", line 39134
ORA-06512: at "SYS.DBMS_STATS", line 38422
ORA-06512: at "SYS.DBMS_STATS", line 37316
ORA-06512: at "SYS.DBMS_STATS", line 15610
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.
ORA-06512: at "SYS.DBMS_STATS", line 1785
ORA-06512: at "SYS.DBMS_STATS", line 15279
ORA-06512: at "SYS.DBMS_STATS", line 15410
ORA-06512: at "SYS.DBMS_STATS", line 37251
ORA-06512: at "SYS.DBMS_STATS", line 38141
ORA-06512: at "SYS.DBMS_STATS", line 38570
ORA-06512: at "SYS.DBMS_STATS", line 39116

Job "TEST_DATA"."SYS_EXPORT_SCHEMA_02" stopped due to fatal error at Fri Sep 20 15:51:02 2019 elapsed 0 00:02:55

Does anyone know the reason and how to fix?

Thanks in advances!


texts: Best way to divide CPU along all sql instances on 1 server

texts:
I have a physical server with 40 CPU's (hyperthreaded) with 2 NUMA nodes. On this server I have 20 SQL Server instances installed and I need to make sure each instance has some dedicated CPU assigned into.

The problem is that sometimes one or more of the instances are using all of the CPUs and then about 4 or 5 instances are out of CPU resources and failing.

What is the best way to spread CPU among all of the instances so each instance has some at the minimum?


texts: Inline table-valued function vs inline sql

texts:
I'm seeing some odd behavior in trying to tune an inline table-valued function. Specifically, if I take the guts of the function and it with the same parameters as I'd have called the function with, I see about a 3x performance gain. So something like this (greatly simplified):

create function dbo.FooToDate(@ToDate date)
returns table
as
return (

select f.a, f.b, b.c
    from dbo.[Foo] as f
    left join dbo.Bar as b
       on f.fId = b.fId
    where f.ToDate >= @ToDate

)
go
declare @ToDate date = '2012-12-21'

--runs in about 4 seconds
select f.a, f.b, b.c
from dbo.[Foo] as f
left join dbo.Bar as b
   on f.fId = b.fId
where f.ToDate >= @ToDate

--runs in about 12 seconds
select a, b, c from dbo.FooToDate(@ToDate)

When I look at the query plans for my actual situation, the overall shape is quite different. My understanding of inline TVFs was that the opimizer was able to unroll the function, so this behavior is somewhat odd to me. Is there a simple (or not-so-simple) explanation?


texts: Mysql trigger to update if date match and insert if no match all BEFORE INSERT

texts:
please pardon my English i am using language translator. I have a table summary ,i want a TRIGGER to fire before insert on this table i.e summary and what i want is that, the TRIGGER should check if the incoming date matches with existing date, it should update the table and add incoming value to existing value else it should just insert

`summary`
+------+-------+-------------+------+
| date |income | expenditure | other|
|22/17 | 200   |   50        |  30  |
|22/17 | 100   |   10        |  80  |
|23/17 | 50    |   100       |  0   |
+------+-------+-------------+------+

`summary` // this is how i want it to be
+------+-------+-------------+------+
| date |income | expenditure | other|
|22/17 | 300   |   150       |  30  |
|23/17 | 50    |   100       |  0   |
+------+-------+-------------+------+

my table code

CREATE TABLE `summary` (
  `id_` int(11) NOT NULL,
  `date_` date NOT NULL,
  `income_` text NOT NULL,
  `expenditure_` text NOT NULL,
  `other_` text NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `summary`
(`id_`, `date_`, `income_`, `expenditure_`, `other_`)
VALUES
(1, '2017-12-22', 200, 50, 30),
(2, '2017-12-22', 100, 10, 80),
(3, '2017-12-23', 50, 100, 0);

This is what i have been trying so far, though am a Novist
trigger code

DELIMITER
$$
CREATE TRIGGER inc_trig
BEFORE INSERT
ON income_
FOR EaCH ROW
BEGIN
DECLARE income,expenditure,other INT;

SET income=1; SET expenditure=2; SET other=3;

IF( NEW.date_ != date_ )THEN

IF NEW.name_ = income THEN
INSERT INTO summary (date_,income)
VALUES (new.date_,new.amount);

ELSEIF NEW.name_ = expenditure THEN
INSERT INTO summary (date_,expenditure_)
VALUES (new.date_,new.amount);

ELSEIF NEW.name_ = other THEN
INSERT INTO summary (date_,other_)
VALUES (new.date_,new.amount);

ELSE(new.date_ = date_)THEN

IF NEW.name_ = income_ THEN
UPDATE summary SET income_ = income_ + new.amount);

ELSEIF NEW.name_ = expenditure THEN
UPDATE summary SET expenditure_ = expenditure_ + new.amount);

ELSEIF NEW.name_ = other THEN
UPDATE summary SET other_ = other_ + new.amount);

END IF;

END
$$


texts: RMAN Not restoring data from restore set

texts:
I'll start by saying it has been YEARS since I restored a database with RMAN. That said, I'm trying to restore data provided by a partner company for us to import into our database.

Our database isn't Oracle, but we have an Oracle instance set up for the transition. I was successfully able to restore the initial dataset to an empty database, but we needed a few more data tables that were included in the latest backup set. I would prefer not to delete the database, DBS, but will if this isn't resolved soon.

I have two backup pieces in my catalog, one for the control file and the other for the datafiles. Both have the same tag. When I try to restore the database to that tag, I get the results below.

RMAN> list backupset 14;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    18.87G     DISK        00:00:00     10-AUG-17
        BP Key: 31   Status: AVAILABLE  Compressed: NO  Tag: COLD_20170810
        Piece Name: C:\APP\ORACLE\ADMIN\DBS_20170810_0GSBHQCU_1_1.BK
  List of Datafiles in backup set 14
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\SYSTEM01.DBF
  2       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\SYSAUX01.DBF
  3       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\UNDOTBSP01.DBF
  4       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\USERS01.DBF
  5       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\DATABIG.DBF

RMAN> restore database from tag 'COLD_20170810';

Starting restore at 14-AUG-17
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/14/2017 16:00:59
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>

I set up the run file below:

RUN {
    SET NEWNAME FOR DATAFILE 1 TO "C:\APP\ORACLE\ORADATA\DBS\SYSTEM01.DBF";
    SET NEWNAME FOR DATAFILE 2 TO "C:\APP\ORACLE\ORADATA\DBS\SYSAUX01.DBF";
    SET NEWNAME FOR DATAFILE 3 TO "C:\APP\ORACLE\ORADATA\DBS\UNDOTBSP01.DBF";
    SET NEWNAME FOR DATAFILE 4 TO "C:\APP\ORACLE\ORADATA\DBS\USERS01.DBF";
    SET NEWNAME FOR DATAFILE 5 TO "C:\APP\ORACLE\ORADATA\DBS\DATABIG.DBF";

    #SQL "ALTER DATABASE RENAME FILE ''G:\ORADATA\DBS\redo01.log'' TO ''C:\app\Oracle\ORADATA\DBS\redo01.log'' ";
    #SQL "ALTER DATABASE RENAME FILE ''G:\ORADATA\DBS\redo02.log'' TO ''C:\app\Oracle\ORADATA\DBS\redo02.log'' ";
    #SQL "ALTER DATABASE RENAME FILE ''G:\ORADATA\DBS\redo03.log'' TO ''C:\app\Oracle\ORADATA\DBS\redo03.log'' ";

    set until time "to_date('10-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')";

    RESTORE DATABASE FROM TAG 'COLD_20170810';
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
}

And I get the same results.

Any guidance will be appreciated.


texts: PostgreSQL: date/time field value out of range

texts:
Software:

Linux cjz-eshop1-p 5.4.0-33-generic #37-Ubuntu SMP Thu May 21 12:53:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

I am getting errors due to CSV import into the database on my server.

ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244385 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160
ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244386 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160

But on my local system, the import is working.

I tried almost everything regarding this issue, but no luck (I drew mostly from here).

Any ideas to solve this issue?

UPDATE

Import is done by java. I believe it is not important, because on my local computer, it is working with same OS and same configuration. And as you can see, when I try select related to date (as @Laurenz helped me), it is ok.

postgres@cjz-eshop1-p:~$ psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

postgres=# SELECT '30.06.2020'::date;
    date
------------
 30.06.2020
(1 row)

postgres=#

Before, it was not possible. Do you want some more details?

UPDATE 2

                    if (body[5].length() != 0) {
                        akc_from = "'" + body[5] + "'";
                    } else {
                        akc_from = "null";
                    }

                    if (body[6].length() != 0) {
                        akc_to = "'" + body[6] + "'";
                    } else {
                        akc_to = "null";
                    }

                    if (body[7].length() != 0) {
                        akc_type = body[7];
                    } else {
                        akc_type = "null";
                    }

                    insertPriceCommand = "insert into stg_price(art_no,store_no,sell_pr,akc_price,akc_from,akc_to,akc_type,run_id,proc_flag,proc_note) values(" + art_no + "," + store_no + "," + sell_pr + "," + akc_price + "," + akc_from + "," + akc_to + "," + akc_type + "," + run_id + ",0,null)";
//                System.out.println(insertPriceCommand);
```


texts: How to Insert JSON data to BYTEA column in the table in POSTGRESQL?

texts:
So, I came across the function script which is converting JSON data to BYTEA and then insert as a record in the table in a BYTEA column. (As I assumed what the code is doing)

In Oracle the function utl_raw.cast_to_raw converts the data to blob data and records the data in the table in Blob column. Giving the following output message, "anonymous block completed"

The following is the code,

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
DECLARE
    v1 "TBL1"."COL1"%TYPE;
    v2 "TBL1"."COL2"%TYPE;
BEGIN
v1 := utl_raw.cast_to_raw('{
 "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""
     }');

 v2 := utl_raw.cast_to_raw('{
    "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""
                    }');

 INSERT INTO "TBL1" ("SN","COL1","COL2") values(1,v1, v2);
END;
$$

LANGUAGE 'plpgsql';
COMMIT;

[SOLVED] Edit:  So generally the problem was nothing. So all I had to remove was the utl_raw.cast_to_raw line and execute it as it is. No errors, no problems. THANKS


texts: SQL Server 2012 Standard Edition Disaster Recovery Options?

texts:
Suggest me the best method for My DR plan. Currently we have set of databases (6) used for our application and we in the process of building DR and HA plan. As we are into small business we are using standard edition 2012 and we are unable to make use of AG feature.

Thanks for your help.


texts: HeidiSQL rounds down double values upon insert...select

texts:
I have the following table:

CREATE TEMPORARY TABLE `new_aps` (
  `MinuteStart` datetime NOT NULL,
  `ProxyListID` bigint(20) NOT NULL,
  `SuccessCount` double(22,0) NOT NULL,
  `UnknownCount` double(22,0) NOT NULL,
  `FailureCount` double(22,0) NOT NULL,
  PRIMARY KEY (`ProxyListID`,`MinuteStart`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

When I try to run the following query in HeidiSQL:

INSERT INTO new_aps (MinuteStart, ProxyListID, SuccessCount, UnknownCount, FailureCount)
SELECT NOW(), 1, 0.5, 1.5, 2.5

I get the following entry in new_aps table:

SELECT * FROM new_aps
"2020-06-11 11:58:19"   "1" "0" "1" "2"

What am I doing wrong? I need double-precision floating-point values stored in the table.

MariaDB version is 10.4.13 on Windows 10 64-bit.


texts: BDBID in DBCC PAGE command output in SQL Server

texts:
There is a famous undocumented DBCC PAGE command in SQL Server (as well as in SAP ASE) which if successful sends output to errorlog.
Noticed that it has 4 main sections: Buffer, Page Header, Data, and Offset Table.
The question is we notice dbid in the Page Header section.
Similarly, there is a bdbid in the buffer section.
Can we unquestionably say that both these ids will be the same? If anyone has experience around these.
For reference: https://techcommunity.microsoft.com/t5/sql-server/how-to-use-dbcc-page/ba-p/383094 https://www.itprotoday.com/sql-server/using-dbcc-page


texts: Best way to store few rows of data every minute without creating 1440 columns a day?

texts:
I will be calling a server that returns some JSON data every minute. The values are along the lines of Value, Currency, DateTime, and maybe 1 or 2 more values.

What is the best way to keep save these related values in a table using MySQL?

1440 columns with one column per minute and 1 row per day?
1440 rows with 4 or 5 columns? One row with 1 column as a text/blob with the 1440 minutes of data as a serialized array?

I feel like that last one might be best since it's only one days worth of data and it can be queried by day. I'm just afraid of how large that text column might get and if someone wants to see a years worth of rows, how long would it take to retrieve, unserialize, and return 1440 X 365 worth of arrays. I'm by no means a database admin, so this isn't something I've ever encountered.


texts: How do I join information schema to the table's data?

texts:
I need to join a table using it's column name from the INFORMATION SCHEMA. Is this possible?

I actually found another way to work around it. I am using Innodb. For the structure it's kind of complicated, but this is the query I was working with :

SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
(
    SELECT *
    FROM
    (
        SELECT
            ct.field_name,
            ct.length,
            ct.description,
            IFNULL( mfc.hide, ct.hide) as hide,
            IFNULL(mfc.required, ct.required) as required,
            ct.example
        FROM xxxxxx.config_marketplace ct
        LEFT JOIN xxxxx.merchant_field_config mfc
            ON mfc.field_id = ct.id
            AND mfc.industry = 'MP'
            AND mfc.merchant_id = '1512MP5'
    ) as t
    WHERE hide != 'Y'
) b
ON LOWER(b.field_name) = c.COLUMN_NAME
WHERE
    c.TABLE_SCHEMA = 'XXXXX_1512MP5'
    AND c.TABLE_NAME = 'marketplace_custom';

Basically, config_marketplace holds the default configuration of the field names(column name) that will be shown, while merchant_field_config does the configurations of the fields I would like to hide based on each merchant's requirements. Lastly, marketplace_custom would be placed in the individual merchant's db with the column header which I would like to join to the data in that table.

PS. I know it's not a very good explanation.

Just out of curiosity, is this possible? I am working around it, but it will be a really good thing to know for future usage!


texts: Enable TDE for existing AlwaysOn databases

texts:
I need to do a little benchmarking of AlwaysOn, both with and without TDE enabled. All the instructions I see cover adding encrypted databases to an availability group, but I see no mention of enabling encryption for a database already in an AG.

Can I just restore the server certificate to all nodes, then enable TDE from the primary? I'm hoping I don't need to remove the database from the AG, encrypt it, then reinitialize all nodes, as the database is around 100 GB. We can do that if needed, but it'll certainly slow down our testing a bit.

Update

I just tried this on some lab VMs. I was able to create a certificate at the primary, restore it on all the secondary nodes, then create a database encryption key at the primary using this certificate. All I had to do after that was ALTER DATABASE AdventureWorks SET ENCRYPTION ON at the primary server, and after a minute or two, sys.dm_database_encryption_keys showed encryption_state = 3 at all nodes.

So, followup question: Is there any reason to think that I shouldn't use this procedure? SQL Server appears to be happy with the results, but I haven't seen this approach discussed anywhere. When dealing with encryption and high-availability matters, I don't want to leave things to chance.


texts:
I'm currently building a process in Powershell where we can copy SQL Server Agent jobs from one server to another server just by entering the server names.  The script uses the SMO library to obtain access to both job servers, scripts the jobs that don't exist on the "other" server, then runs those scripts (under a SqlConnection and SqlCommand).  The issue is how to check if a job exists on ServerOne compared to ServerTwo.

I've tried four different approaches: build an array with the jobs from ServerTwo (the compare server) and see if that array contains a job from ServerOne; I also tried the like operator with the same logic; and, using arrays, I've used Compare-Object after loading two arrays with job names from both servers and measuring which jobs don't exist in the other (produces everything too).  Finally, I looped through each job on ServerTwo and looped through each job on ServerOne to see if they equal each other (which is the below code).  This will produce everything because each job doesn't equal each job:

$ServerName = "S1\I"
$ComServer = "S2\I"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
$com = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ComServer

foreach ($a in $com.JobServer.Jobs)
{
    $a = $a.Name.ToString()
    foreach ($j in $srv.JobServer.Jobs)
    {
        $j = $j.Name.ToString()

        if ($a -like $j)
        {
            Write-Host $j
        }
    }
}

Is there another approach to this where I can check if a job exists on one server vs. another (as a note, I already know how to script the job, execute it and so on; the part of making sure the job doesn't already exist is the problem here)?


texts: SQL Server backup to Azure Blob with different backup plan than local backup

texts:
We have a production SQL Server with a number of critical bases. At this time, we have a maintenance plan which performs the following:

creates a FULL backup every Saturday with a TTL of 4 weeks
creates a DIFF backup every night (except Saturday) with a TTL of 2 weeks
creates a LOG backup every hour with a TTL of 1 week

As such, we're able to restore the databases to any Saturday of the past 4 weeks, any day (night) of the past 2 weeks and almost any specific point in time for the past week. Also, we're using the backup scripts by Ola Hallengren, and the databases are for our SharePoint and TFS.

We recognize that 4 weeks is not that long AND those backups are, ultimately, local backups, so if something serious happens to the physical server they are on, we'll be in trouble.

I wanted to utilize Azure Blobs to store online backups (for FULL and DIFF as LOG can remain local), however I have a bit of a problem coming up with a good plan on how to do that. The main problem is that our internet connection isn't very fast (we only have a symmetrical 30/30 at the office) and, well, Azure costs money (Blobs are cheap, but still). But make no mistake, network speed is the primary problem.

I wanted to use a different backup plan for Azure than for local backups, but quickly ran into a problem - DIFF backups become useless if the FULL backups aren't the same in both backup locations and I can't have the same FULL backups in both locations if I want a different schedule for Blobs.

To put that into a more practical example: say I want to save a FULL backup into Azure on the first weekend of every month... if my local backup was the same, that wouldn't be a problem. But now, if I do copy-only FULL backup to Azure, then daily DIFF backups stored in Azure won't work. If I do a non-copy-only FULL backup to Azure, then the LOCAL backups get broken. Besides, the first FULL local backup after the first Azure backup will break the subsequent DIFF backups on Azure anyway.

I think the only thing I can do is take FULL backups less often (say, 1 FULL per month?), increase the TTL accordingly, and then have the same backup plan for Azure (using the MIRROR options in the script). But then I worry that this is not often enough for FULL backups. Alternatively, the script by Ola has a ModificationLevel parameter, but I'm not sure how to use it for optimal storage space utilization.

Alternatively I could utilize a file-sync-to-Azure solution (rather than an SQL-backup-to-Azure solution), but this is really the same problem.

Is there a way to have a different backup plan for Azure than for local SQL backups?


texts: Time Interval Partitioning in SQL Server

texts:
I have a table with time stamp (UTC) and for several reasons, like purging from this table (done at later point of ETL), I am trying to find out the feasibility of partitioning the table by 5 minutes or so. I have records pumped into this table in a real time scenario and my ETL process will be running in parallel.

Below is the schema of my table which I am interested in and my SQL Server version is 2016. Kindly suggest if partitioning can be an optimal way to deal with this scenario.

CREATE TABLE [dbo].[F_DATA_EVENTS]
(
    [RECID] [nvarchar](255) NOT NULL,
    [XMLRECORD] [nvarchar](max) NULL,
    [EVENT_TIMESTAMP] [numeric](38, 6) NULL
)

GO

ALTER TABLE [dbo].[F_DATA_EVENTS] ADD CONSTRAINT [DF_EVENT_TIMESTAMP]
DEFAULT (CONVERT([numeric](30,6),concat(datediff(second,'19700101 00:00:00:000',getdate()),'.',datepart(millisecond,getdate())))) FOR [EVENT_TIMESTAMP]


texts: MySQL cluster with HAProxy balancer outside of local network

texts:
I am building a mysql cluster on local network. I have 3 nodes and would like to load balance them using HAProxy. It works fine when I place HAProxy on one of my nodes inside of the network. I bind its Ip (lets say 192.168.0.1) to the ips of the sql nodes (xxx.xxx.x.2 ,...3,...4).

My question is: how do I bind HAProxy to my nodes if i want to place it outside of my local network, lets say, on AWS micro instance? I want to direct mysql pulls through aws to my 3 nodes that i have at home.

I have already forwarded port 3306 and can access it from outside no problem. But I only have one public Ip. How can I bind haproxy's ip to my public ip so that it connects to all 3 nodes. That is the part I cant wrap my head around.

Thank you for help in advance.


texts:
Background

I have a MySQL database, from which I run mysqldump for backups.

In this database, I have some fields that will contain some backslashes, and when dumping this, it will contain something like

INSERT INTO table1 (col1, col2) VALUES (1, '\frac{1}{2}');

(For reference, this is TeX, used in this case for typesetting mathematics.)

Problem

Now, when I import this backup with

mysql -u username -p database_name < file.sql

the backslash in the col2 value disappears. Likely, instead of inserting it, it's being used to escape the following character.

Question

Is there a way to insert this while maintaining the backslash?

My idea so far is to make a script that plows through the file and replaces every occurrence of \ with \\. Is this reliable?

I would assume that someone already thought of this problem and maybe created some way of dealing with it.

Any help appreciated!


texts: Querying number of tries since last successful log, checking IP as well as user

texts:
I asked this question before on stackoverflow, but it might fit better here, since it is mainly about database performance.

The actual question

With performance in mind, how would you go about querying for username as well as IP address in a single query (or several if more efficient ^^), given a login table like this (just as an example):

login
------------------------------------------------------
LoginId     INT PRIMARY KEY
UserId      INT  (NULLABLE)
UserName    VARCHAR(100)
Status      ENUM('ERROR', 'SUCCESS', 'ATTEMPT', 'LOCKED')
IP          INT
Time        TIMESTAMP

There are several thinks to take into account as far as I understand

Username

Maximum number of tries for a specific UserName
Locking this user via an Status LOCKED entry on his last try (instead of ATTEMPT otherwise)
"Unlocking" him after a specific timespan
Storing/Locking the actual entered Username instead of just the User ID - not giving a hint wether this user actually exists or not (primarily because in my case it is actually an email address instead)

IP

Maximum number of tries from a single IP address
Locking of the IP address only through timespan, not by setting a status (sufficient imho)
(Not sure if this should be also "reset" on a succesful login from the IP - for now not considered)
Since an IP might represent not just a single device, possibly finding a way not to "lock" the IP(?)

Required Query Results

The number of tries for this username, since the last successful login
The number of tries from this IP within a specific time (lets say 5 minutes for simplicity)
If - and more importantly - when the user was locked (he will be allowed to try again after some time)

This is my approach so far, if you have any suggestions/ideas how to do it better, easier, or more efficient, shoot :-)

For example I am not quite sure if it makes sense to lock the user for an hour, if he has tried 2 times some days ago, and now enters the wrong password for the 3rd time, but that is probably a different story.

My query so far

SELECT

       /* Count of login attempts for this user */
       COUNT(CASE WHEN UserName = ?  THEN 1  ELSE NULL END) AS LoginAttempts,

       /* When the user was locked or if at all */
       MAX(CASE WHEN Status='LOCKED' THEN Time  ELSE NULL END) AS UserLocked,

       /* Number of login attempts from this IP */
       (SELECT COUNT(*)  FROM login
           WHERE Status != 'SUCCESS'
           AND   IP = INET_ATON(?)
           AND   Time > NOW()-INTERVAL 5 MINUTE) AS GeneralAttemptsFromThisIP

WHERE

       /* Filter attempts since last successful login */
       LoginId > (SELECT MAX(LoginId) FROM login WHERE UserName=? AND Status='SUCCESS')

       AND  UserName = ?

       AND  Status != 'SUCCESS'

Which is nothing more than two separate queries packed into one, with function calls in both WHEREs.

Possible improvements

Additional handling of IPv6
Not just checking for !='SUCCESS' but specifically ATTEMPT and LOCKED (but since ERROR is reserved for very drastic cases - eg. hacking attempts - it should be of no consequence)
Indexes (but no idea where to place them efficiently in this case, other than putting one multi-column for UserName, Status and possibly Time(?) (which would not help at all for the IP part though))


texts: Random performance of stored procedure across databases

texts:
I have a stored procedure that is installed on 30 databases across 4 servers.  We encountered an issue last night where 3 of these stored procs are now taking over 8 minutes (one 18 minutes!) to execute while all of the others are under 1 second.  2 of the SP's are on databases under the same server, the other is on another server.

I have looked at the following and seen no improvement or rational explanation why.

sp_recompile
update statistics
review indexes fragmentation compared with successful databases but recreated anyways.
change proc for param sniffing problem/solution
Reviewed execution plan for anything shocking (estimated rows was WAY off but came back to normal after #2 above
drop and recreated procs with no changes
watch temp db, as proc uses temp tables nothing jumped out

We have changed the proc to be more efficient and now its executing under 1 second, but I cannot let go as to what is causing the existing SP to be fast in  90% of the situations, but seriously slow in the others.  Especially across 2 servers.

Due to the speed and consistency for months, I am baffled as to what might have caused this.
I have looked at database settings using sys.database comparing one to another.
The only differences I see are
                           Slow setting / Normal Database setting

recovery_model         1 /          3
recovery_model_desc    FULL/        SIMPLE
log_reuse_wait         2    /       0
log_reuse_wait_desc    LOG_BACKUP/  NOTHING

Could this have to the with the above settings?

The proc (which I can provide if needed, along with execution plan) reads data from tables and inserts into a couple of temp tables and returns a dataset.  from existing tables it only performs selects.  The tables are no bigger then 250,000 rows.  Some databases where the proc is running quick are in excess of 800,000.   So the 3 in question actually have a small overall data set.

Any ideas of what to check ?
What else can i do to figure this out?


texts: How restore a specific database from backup using mongorestore command

texts:
I created a backup of all my databases using mongodump command. Now I want to restore a specific database using mongorestore command.

How is this possible, I use this command: --db option then mongodb doesn't restore a specific database.


texts: Decorating an existing database table with information that no one else cares about

texts:
We have a table that many processes read from. Some of them want to keep track of the fact they have processed this data, whereas others simply rely on fetching the data from the table based on user actions.

I think ideally, the consumers would interface with this data indirectly: probably via a web service, and then they could just keep track of what resources (URIs) they have discovered and processed, all internally to themselves.

The other options seem to be either adding a column to the existing table, or adding a new table - either way, one for each consumer. I think adding columns is easier and probably performs the best, but adding a new table keeps the clutter away from the original table.

Aside from those points, are there any others that I should be aware of?

Edit:

To elaborate..

I have a table foo that is populated by an external process, and read:

By a web-service that simply exposes data depending on criteria (show me the latest... show me the one for this particular date, etc)
By a post-processor, that looks for foo records that it has not seen before, processes them (which writes data somewhere else, unrelated to the web service) and then wants to mark them as processed.

In order to mark them as processed, we can either have a column on the foo table (processed = 1), or we can have a foo_processed table (that just has the ids of the foo that have been processed, and use an outer join to find unprocessed foo records)

At this time there is only one post-processor, but I can't guarantee that.


texts: MySQL 5.1.63 - using tuning-primer - Table Cache question

texts:
tuning_primer says the following:

TABLE CACHE
Current table_open_cache = 812 tables
Current table_definition_cache = 256 tables
You have a total of 498 tables
You have 812 open tables.
Current table_cache hit rate is 1%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

I don't understand this.
I only have 498 tables, however my.cnf shows table_cache   = 812.

If I have only 498 tables, should I not DECREASE my table_cache value?

The table_definition_cache is set to its default of 256.  Should this be increased alsO?

Show status:

|Open_table_definitions            | 498         |
|Open_tables                       | 812         |
|Opened_files                      | 291680      |
|Opened_table_definitions          | 0           |
|Opened_tables                     | 0           |

We are running everything with InnoDB.
Any help on this would be appreciated.


texts:
I cannot find the download location for SQL Web Edition.

I can find the Standard / Developer / Express editions, but not the Web edition.

I have tried https://www.microsoft.com/en-us/sql-server/sql-server-downloads,
I have followed all the links on the page, and found various informational resources of SQL Web edition, but not the actual file.

Where/how can I download SQL Web Edition


texts: Mongodb replication breaks if balancer started

texts:
have a 10 shard ( with 3 replicaset) cluster with very heavy traffic. Storage almost 1.5TB per shard in 2000 databases
MongoDB server version is  4.0.3
Since these servers were added at different points on time , the shards are not balanced.
I am using hash sharding

If I start balancer the activity is so intense that my replication stops working across replica sets.
Since these servers have lot of queries on replicas I cannot afford to have replication breaking. I am forced to stop balancers and keep them off

If I manually do a move chunk ( one key at a time ) using sh.movechunk() then this works
So I plan to scriptize this

Query the large shards and get the keys by directly connecting to mongod
Distribute these docs using sh.movechunk
Once some kind of balance is achieved then start balancer

Is that a good idea ?
Does mongodb balancer have some option to run slowly, so that my servers are not “overwhelmed” ? That would be ideal


texts:
We have a master server and replication server running Postgres 9.3.6

Streaming was going slow due to a secondary failover router. It was running behind but still working.

I got a communication error and now I am getting the

ERROR:  requested WAL segment 00000001000003150000001E has already been removed

However looking at the master server that file (and many before it) is still in the WAL directory.

I restarted the replication server and I do get the message

redo starts at 315/1A9CB5A0
[4018 - 2015-09-28 20:57:32 GMT]LOG:  consistent recovery state reached at 315/1ED5E910
[4018 - 2015-09-28 20:57:32 GMT]LOG:  unexpected pageaddr 313/C0D60000 in log segment 00000001000003150000001E, offset 14024704

Is there any way to restart the streaming or will I have to pull down and do another basebackup?


texts: Error after applying SQL Server 2008 R2 Upgrade 10.50.6542 (Latest Build)

texts:
First the problem: Since I applied the latest patch to SQL Server 2008 R2  (released on March 3, 2016) that should have taken my server to build 10.50.6542 I can't connect anymore. This patch is to provide support TLS 1.2.

The error I'm getting (when attempting to connect using a client tool directly in the server) is

"A connection was succesfully established with the server, but then an error occurred during the pre-login handshake"

The server is a Windows Server 2012 R2 and is running NET Framework 4.6.1 (some posts point to older version of .NET framework causing this problem, but this is not my case).

I tried to run the following packages to see if this would fix...

SQL Server Native Client (x86 and x64)
Hotfix rollup 3106993 for the .NET Framework 2.0 SP2 in Windows Server 2012 R2 and Windows 8.1
Hotfix rollup 3106994 for the .NET Framework 4.0 in Windows
Hotfix rollup 3099845 for the .NET Framework 4.5.2, 4.5.1, and 4.5

... None of these were necessary, and nothing helped...

Has anybody experienced this? Any ideas or suggestions?

Thanks!

One Update 24 hours later
I inspected the SQL Server error log looking for entries that include only a Start, 5 failed login attempts and a stop. From all the messages in that time-frame (107 entries in total) the only message that includes anything like an error is

 The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

This message though… has been reported on server startup even before I applied the patch so I am 100% confident that it is a red herring. No logs are inserted in the SQL Server error log as a result of the failed login attempts.

Another Update

Based on suggestions I got from people here, I downloaded and installed the latest version of SSMS (17.3) on the server. When I tried connecting this time, I got a different error so it seems I'm moving further, but I'm not there yet.

So it seems that it is true that old versions of SSMS do target .net 3.5 and even if you have newer versions of the .net framework the client connections will not work, the question is now why is that I still get this "No process is on the other end of the pipe", even when most of the client components have been updated.

Some more things I have investigated (and discarded)

The “client protocol” order in SQL Server Configuration Manager seems to be the correct one, as TCP/IP is at the top. This was suggested as a possible cause of trouble, but in my case it is all appropriately configured (apparently)

Tried to install “Microsoft ODBC Driver for SQL Server” as suggested in the TLS 1.2 support for Microsoft SQL Server but that didn’t make a difference either. After installing, the error message is the same
Tried adding the registry keys at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2 (also as suggested in the same article). Same error…


texts: Why Does SQL Server OPENQUERY To Oracle Return Different Column Types On SQL Server 2016 to 2008

texts:
I have two SQL Server Instances on the same machine, one 2008 and one 2016. Both instances have a linked server connection to the same Oracle 12 Server.

If I run the following query 2008 returns 1.23 as a VARCHAR but 2016 returns it as a float

SELECT * INTO #tst FROM OPENQUERY(JOHPROD,'SELECT 1.23 FROM dual')
SELECT t.Name,* FROM tempdb.sys.columns c
    INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE [object_id] = OBJECT_ID(N'tempdb..#tst');

Both instances are pointing to the same Oracle server and both using the same Oracle ODBC driver.

Both linked servers have this same definition...

EXEC master.dbo.sp_addlinkedserver @server = N'ORAPROD', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORAPROD'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORAPROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'ORAUSER',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORAPROD', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Any idea why these would return different types?


texts: SSIS Package Error when called from SQL Agent Job

texts:
We have SSIS running on its own server under account 'ourserviceaccount'.

We then have SQL Agent running on a separate server under the same account.

I have set up a job that calls an SSIS package, which in turn calls a number of child packages.
However, I get this error...

  Error 0xC0202009 while preparing to load the package. SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code:
  0x%1!8.8X!.

The job is run as the same service account credentials.

In the parent package that calls the child packages, I have set the connection manager to Windows Authentication.

What step am I missing?

Thanks!


texts: Setting location of temp files for Postgresql backend (9.3.10)

texts:
Is it possible to set the location of temp file creation for a Postgresql backend (ver 9.3.10)?

Here's my scenario: my Postgresql DB resides in a dedicated VM with Ubuntu 14.04 OS. My VM comes with 200GB of temporary high-performance SSD storage provided by my infrastructure provider, meant for short-term storage for applications and processes. Here's how it looks like:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       221G  9.9G  200G   5% /mnt

I'm running low on disk space, and have to run some analytical queries that can take me to disk full due to the temporary files creation.

Apart from taking measures like deleting log entries to release more space or setting temp_file_limit in postgresql conf, I'm also interested in knowing whether I can set the location for temp files. After all, I have 220GB available for these kind of scenarios, so do want to put them to good use (swap space is set here too).

An illustrative example of how I can set this up would be very helpful since I'm a neophyte DBA.


texts: Master node freezes when second master unreachable in master-master replication

texts:
I have a 2 node master-master setup in mySQL 8.0.22. I have successfully got the nodes to both be writeable and update each other in normal operation. However, When I disconnect a node, it is not possible to write to the other master, it hangs indefinitely on any db operation.
How can I get my db to be writeable when one of the master nodes goes down? When it comes back up I want the bin log to resync to the other node without my intervention.
Here is node 1 config:
[mysqld]
user            = mysql
server-id               = 101
log_bin                 = /var/log/mysql/mysql-bin.log
max_binlog_size   = 1000M
expire_logs_days = 10
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
group_replication_single_primary_mode=OFF
loose-group_replication_group_name="597e60eb-2f0e-11eb-90c3-366bac9bcf37"
group_replication_ip_whitelist="10.192.3.111/32,10.192.3.112/32,10.50.17.113/32"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "10.192.3.111:33061"
loose-group_replication_group_seeds="10.192.3.111:33061, 10.50.17.113:33061"
loose-group_replication_bootstrap_group=OFF
report_host=10.192.3.111
myisam-recover-options  = BACKUP
auto_increment_increment=2
auto_increment_offset=1
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
key_buffer_size         = 16M

Here is node 2 config:
[mysqld]
user        = mysql
key_buffer_size     = 16M
myisam-recover-options  = BACKUP
bind-address            = 0.0.0.0
log_error = /var/log/mysql/error.log
log_bin         = /var/log/mysql/mysql-bin.log
max_binlog_size   = 1000M
expire_logs_days = 10
server_id=103
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
auto_increment_increment=2
auto_increment_offset=2
group_replication_single_primary_mode=OFF
group_replication_ip_whitelist="10.192.3.111/32,10.50.17.113/32"
loose-group_replication_group_name="597e60eb-2f0e-11eb-90c3-366bac9bcf37"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "10.50.17.113:33061"
loose-group_replication_group_seeds="10.192.3.111:33061, 10.50.17.113:33061"
loose-group_replication_bootstrap_group=OFF
report_host=10.50.17.113
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1


texts:
New Company and new to Amazon RDS.
Several databases have been migrated to the cloud because "everything" is done for us a manager has stated.

I have explained standard DBA tasks are still required such as DBCC Checkdb, index/stats maintenance.

Five RDS DB's
10GB - 20GB In size
Mirrored within RDS

Has anyone had an issues running DBCC CheckDB on a similar setup?

I ask due to the blog post from Brent Ozar, where the job can cause the mirror to failover.
https://www.brentozar.com/archive/2016/10/sql-server-timeouts-backups-checkdb/

We have experienced this issues on an EC2 instance in the past so after anyone's experiences.


texts: Copy Postgres databases with indexes between instances

texts:
How do you copy a single Postgres database between installations without using pg_dump and without regenerating indexes?

We have an application we deploy which uses Postgres 8.4.7 database in Linux. We have a rather large static data set (4GB) which is is indexed.

Using pg_dump we have to index the data once it is restored to the second Postgres instance. Indexing this data can take up to 80 minutes, so we would rather deploy the database with the index already generated.

From postgres documentation it appears that databases are contained in sub-directories under postgresql/8.4/main/base based on their OID.

My first attempt was to create an empty database in Postgres, shutdown the instance, then copy the data from the existing instance to the directory of the OID for the empty database. This failed as my indexes were not working. Preforming a vacuum gave me the following error:

WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation "pg_type" page 6
WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation "pg_depend" page 39
ERROR:  xlog flush request 0/8B199518 is not satisfied --- flushed only to 0/8AF81680
CONTEXT:  writing block 39 of relation base/17004/2608


texts: Replication with no network connectivity

texts: I have SQL Servers on two isolated networks that cannot be connected directly. However, there is a need to keep data in sync between the two systems. Obviously SQL Server replication won't work. It might however be possible to transfer files from one network to the other. So - I am soliciting for any and all ideas to keep the two databases in sync without using replication, and without incurring any major downtime for restores and such, since both databases will be in nearly constant use (ruling out log shipping).


texts: How to connect to a named SQL Server instance without specifying its name?

texts: Is there anyway I can connect to a SQL server that has no default instance, using just the server name? I've tried using SQL CMD, but it seems that the named instance name is required to login, regardless? Does anyone know how this could be acheived and what language?


texts: Running a stored procedure across db Link

texts:
I have a script whose sole propose is just to transfer data from one schema to another via a db link. Part of the process however is that after the data has been loaded, a stored procedure needs to be ran which runs some calculations and updates some additional tables. How can I have my script run a stored procedure across a link? Rough idea of what I'm thinking...

Begin

Insert into schema@myLink
 SELECT * from testSchema
;

commit;

schema@myLink.DoMagicCalulcations();

commit;

dbms_output.put_line("schema has been updated");
END;


texts:
I have this two tables:
Table members
id  full_name   sex
1   John Smith  1
2   Carol Danvers   2
3   Clark Kent  1
4   Lois Lane   2
5   Diana Prince    2

Table member_emails
id  member  email
1   1   jsmith@mail.com
2   1   johnsmith@jsa.org
3   2   danvers@marvels.com
4   4   llane@dailyplanet.com

I would like to know how many distinct member sex count who has emails, like this table:
total   male    female
3       1       2

I've already use
 SELECT
 COUNT(DISTINCT e.member) AS total,
 SUM(CASE
    WHEN m.sex = 1
    THEN 1
    ELSE 0
    END) AS "male",
 SUM(CASE
    WHEN m.sex = 2
    THEN 1
    ELSE 0
    END) AS "female"
FROM member_emails e
  JOIN members m ON m.id = (
      SELECT DISTINCT e.member
      FROM members
      WHERE id = e.member
)

But it resulted
total   male    female
3       2       2

As it can't distinct e.member.
Does anyone have any ideas how to do this?


texts: ProcessUpdate of the dimension triggers processing of all partitions of all measure groups in the cube

texts:
I have Account and Customer dimensions in the cube that are connected to the same measure groups (there are about 15 - 20 measure groups in the cube).

When I run XMLA command to process update these two dimensions, like this:

<Batch>
    <Parallel>
        <Process>
            <Object>
                <DatabaseID>My Database</DatabaseID>
                <DimensionID>Dim Customer</DimensionID>
            </Object>
            <Type>ProcessUpdate</Type>
            <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
        </Process>
    </Parallel>
</Batch>

in the case of Account dimension it finishes in a couple of minutes because it doesn't trigger processing of all partitions of all measure groups. But in the case of Customer dimension it triggers processing of all partitions of all measure groups, so process update of this dimension lasts longer then full processing of entire cube.

I am not sure what can be the reason from which the dimension will trigger all this processing in the case of one dimension and not in the case of the other. For both dimensions Process affected objects is set to Do not process. Where should I look, what to check, can I somehow prevent this reprocessing happen?

Thanks!


texts: Oracle: Can I change the oracle's password in a RAC environment?

texts:
Can I change the oracle and grid (OS users) passwords in RAC nodes?
Changing it could break the RAC?

The Oracle Grid and Database installers asked me to input that passwords, unfortunately that was easy and I have to change it now, but there are instances running on that in production.


texts: How to restore postgreSQL schema dump to schema with different name

texts:
I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema.

The dump type is "custom".

So effectively I want something like:

pg_dump -U postgres --schema "source_schema" --format "c" --create --file "source_schema.bak" my_db
pg_restore -U postgres --exit-on-error --dbname "my_db" "source_schema.bak" --destination-schema "destination_schema"

If that is possible it would be fine if I could copy (and rename) source schema before backup.


texts: What are the advantages of Always Encrypted 2016 vs Encrypted Columns?

texts: I was reading about SQL Encryption methods, I have a good understanding of TDE, then I read about Encrypted Columns and then I read about Always Encrypted. The description of Always Encrypted is very lengthy and detailed, but the text with Encrypted Columns is less in depth but seems to me like it does the same thing. I want to understand the difference between the 2 and if there is any advantage with the new version?


texts: Login system: how is it possible to model multiple user types identified by only one account?

texts:
I am trying to model a database consisting of different entities that need to be authenticated through a login system. Let's assume to have two separate entities (Seller and Manager) just like this image:

as you can see both of them have (only) one account identified by the primary key username. I would like to be able to identify one role by its username, so that my system recognises what to do. If we translate this conceptual model in the physical one the resulting model is the following:

Is this actually correct?

Update:
I translated in english all my entities and decided to upload my physical model here in order to explain why i made this user-centric:

My users are:

SystemAdministrator
Owner
Seller

basically these three roles have logical connection with other entities, how can i keep these logical connections and still have a valid account table (for username, password... info)?

Thanks in advance and best regards.


texts: File locations for Full Text Catalogs using SQL Server 2005 on VMware

texts:
I have an issue with one of our development SQL Server Servers. We have databases that use full text search catalogs. Just some background info, our servers are configured to use separate drives for logs, data, and the default installation location This server is a virtual machine. When I try to create a new full text search catalog, it forces me to create it in the default installation directory, citing that you can't create a catalog on a removable drive. This seems kind of contrived since this is a VM, none of the drives are "physical", and the drive it will let me install it on isn't any less "removable" than the the others.
It does this on any of the test servers, but for this conversation the specifications are:

Windows Server 2008-R2 x64 Standard Edition
SQL Server 2005 9.0.5057
VMware vCenter Server 5.1.0.88016

All of these drives are attached to the vm as Hard disks, not mapped drives.

But when creating a new catalog, I always receive an error unless I put the catalog on the same drive the instance is installed on.

Is this just a quirk with sql server, or is there a work around? I know in newer versions of sql server, the catalogs are stored internally, and so this isn't an issue, but a number of our servers are still and will stay for quite some time on sql server 2005. Has anyone else seen this issue? Did you find a solution?


texts: How to Install/Configure Oracle Instant Client on Ubuntu

texts:
Installing the Oracle Instant Client on Ubuntu/Debian based systems is awkward since Oracle is RPM centric regarding its Linux support.

What's the best way to easily install and configure the Instant Client on Ubuntu?


texts: How will SQL Server distribute filegroup writes in this scenario?

texts: Have a multi TB db with one large data file for the single file group on a SAN drive.  For write performance reasons, it should have multiple data files.  When adding a new file to this filegroup, will SQL write exclusively to the new filegroup file until it's size reaches the same size of the existing filegroup before switching over to round robin writes between the two data files?


texts: How to make a flexible service broker receive in a stored sql procedure, how to pass "FROM" parameter into receive

texts:
a while ago I asked a question about how to make begin dialog and send more flexible so that it can be embedded in a procedure that takes the to, from, parameters as sysname variables.

Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it

however as Rusanu mentioned in the answer, this same technique cannot be used for the From clause of the Receive.

  Actually it will work. Most SSB verbs accept parameters for they arguments (except queue name for RECEIVE, of course). The parameters are of type sysname ...

actually the send side is done and I am now trying to make the RECEIVE flexible in the same way, something like:

CREATE PROCEDURE QueueReceive
  @myTargetQueue SYSNAME
  @cg UNIQUEIDENTIFIER OUTPUT
  @ch UNIQUEIDENTIFIER OUTPUT
  @msg XML OUTPUT
as
    BEGIN TRANSACTION;
    WAITFOR
            ( RECEIVE TOP(1)
                @cg = conversation_group_id,
                @msg = cast(message_body as XML),
                @ch= conversation_handle
              FROM @myTargetQueue
            ), TIMEOUT 3000;
    COMMIT
...

It seems variables of type sysname cannot be used in the from clause of the RECEIVE?  If I have to do it in dynamic SQL, how would I return all the variables, conversation_group_id, conversation_handle, etc. from the dynamic sql execute of a receive function?  is there a better performing technique to accomplish the same thing?

Thank you.

FIX/Update SO FAR:
I am creating a bunch of IF clauses depending on what the passed parameter is, it will just execute a different whole receive statement.  It is not efficient because I have to change procedure code any time I add a new QUEUE, but I guess it will do for now...


texts: am trying to restore a backup, the backup is from a database in the current instance, want to restore the backup with a different name for test purposes, this was possible in 2008 R2 but in 2012 its not possible, anybody know a work around


texts: Rank using one column. If it is the same value in 2 rows rank using another column


texts:
What do you do with the SQL Agent Jobs when the HA environment switches between primary and secondary?
Do they just let the jobs on the secondary fail and create log entries when it can't access the database?


texts: SQL Server Error - "Cannot execute as the database principal because the principal [name]" does not exist"

texts:
I've searched for an answer to this, but nothing I've tried so far has worked. So what happened was my DBA created a copy of a SQL Server database for me. I have several SQL Server Reporting Services (SSRS) reports in a project (in my Visual Studio solution) and they all must connect to the new database, via pre-defined credentials.

The credentials in question  consist of a username and a password. If the the database name is "DbName" and the username in question is "dbUserName", the error I get is this:

  Cannot open database 'DbName' requested by login. The login failed. Login failed for user 'dbUserName'.

When looking at the properties for database "DbName" in SQL Server Management Studio (SSMS), under the "Permissions" menu, I do see an entry for 'dbUserName' as a User. I see it's been granted a Connect permission, but when I click on tab "Effective", I get the following error:

  Cannot execute as the database principal because the principal "dbUserName" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Also, one more piece of information that might be useful. If, in SSMS, I go in the Security folder for database "DbName" and then expand Users, I do see user 'dbUserName' there. Looking at its properties reveals its user type as a "SQL user without login", while in a different database (basically a test copy of 'DbName') the user type is "SQL user with login".

Any idea what's causing the errors in question?


texts:
We have a big reporting database on production server, and we have several SSIS jobs running against it.

Yesterday almost all this-database-related SSIS jobs stuck and running forever. Also the transaction log backup of this database never succeeded. I checked the SQL Server Error Log and I see lots of following errors:

Timeout occurred while waiting for latch: class 'ACCESS_METHODS_HOBT_COUNT', id 00000000EA7449F0, type 2, Task 0x0000000005817DC8 : 12, waittime 66300, flags 0x1018, owning task 0x0000000000000000. Continuing to wait.

Does anyone know what does it mean? And how we can solve it?

Additional info:
We stopped all related SSIS jobs. One of them is rolling back an index rebuild. It's talking forever now and we still receive the latch timeout alert.


texts: BigInt or Varchar for a large number I won't ever sort / calculate on

texts:
I need to store users' Facebook IDs in a column in my DB.

The ID always consists of only digits, and can be as large as a number in excess of 500 million. Considering that I won't ever need to do any arithmetic, sorting or use relational operators on the values, what would be a better choice: BigInt or varchar ?

The value will always be unique and never non-existent (null)

The only operation I do is check for the existence of an ID in the database, i.e. the = operator.


texts: sh.status() error processing query: ns=config.mongosTree

texts:
if I run sh.status in mongos I get the error:

assert: command failed: {
        "ok" : 0,
        "errmsg" : "error processing query: ns=config.mongosTree: ping $gt new Date(1532712420740)\nSort: {}\nProj: {}\n No query solutions",
        "code" : 2
} : aggregate failed
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
printShardingStatus@src/mongo/shell/utils_sh.js:611:13
sh.status@src/mongo/shell/utils_sh.js:78:5
@(shell):1:1

2018-07-27T11:28:05.644-0600 E QUERY    [thread1] Error: command failed: {
        "ok" : 0,
        "errmsg" : "error processing query: ns=config.mongosTree: ping $gt new Date(1532712420740)\nSort: {}\nProj: {}\n No query solutions",
        "code" : 2
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
printShardingStatus@src/mongo/shell/utils_sh.js:611:13
sh.status@src/mongo/shell/utils_sh.js:78:5

I reinstalled all mongo-enterprise packages but still the error persist, all the shards are working correctly, since I can perform find from mongos.

thanks


texts:
I'm trying to figure out a way(using only TSQL) to determine if and how much the PAGEFILE is used (I will capitalize this term if I refer to the windows page file from now on - like pagefileusage% counter).

My first thought is using sys.dm_os_sys_memory. I am a little bit confused here.
The description of the total_page_file_kb is that this is a commit limit, so basically, it's the size of the physical memory (RAM) + size of the PAGEFILE.  So far it is ok.
But what exactly is the available_page_file_kb?
Is it referring to the commit limit or to the PAGEFILE?
I tried to use these calculations:

Assuming that available_page_file_kb referring to the commit limit:

PAGEFILE usage% = (available_page_file_kb-available_physical_memory_kb) / (total_page_file_kb-total_physical_memory_kb)

Assuming that available_page_file_kb is referring to the PAGEFILE

PAGEFILE usage% = (available_page_file_kb) / (total_page_file_kb-total_physical_memory_kb)

Unfortunately none of these calculations show me the right results.
I keep checking the pagefileusage% counter on some of my test servers, and sometimes the first formula is wrong by only 1-3% and the second one by 15-25% or vice versa, the second one by 1-3% and the first one by 15-25%,

What did I miss here? Is it impossible to determine (or even just get a close estimate) the pagefileusage% with sys.dm_os_sys_memory dmv?


texts: Internal MariaDB error code: 1927 ('Connection was killed')

texts:
Edit: filed a bug report at https://jira.mariadb.org/browse/MDEV-14493

Mariadb nodes freeze occasionally with the following error in the logs, any clues?

(drupal app, db freezes on clearing application cache)

[Warning] WSREP: Failed to apply app buffer: seqno: 903792, status: 1
Nov 23 21:42:55 websrv4 mysqld[1725]: #011 at
galera/src/trx_handle.cpp:apply():351
Nov 23 21:42:55 websrv4 mysqld[1725]: Retrying 2th time
Nov 23 21:42:55 websrv4 mysqld[1725]: 2017-11-23 21:42:55 140081879742208
[Warning] WSREP: BF applier failed to open_and_lock_tables: 1927, fatal: 0
wsrep = (exec_mode: 1 conflict_state: 5 seqno: 903792)
Nov 23 21:42:55 websrv4 mysqld[1725]: 2017-11-23 21:42:55 140081879742208
[ERROR] Slave SQL: Error executing row event: 'Connection was killed',
Internal MariaDB error code: 1927


texts: Does the mysql parameter "max_connections" apply for unix socket connections?

texts:
The documentation does not specify if max_connections apply only to TCP connections.

The way it is said, it looks like it does apply for any kind of connection, but I'd like to be sure because otherwise, I could use it when the "Too many connection" problem arises, even if the (last) SUPER connection slot is already in use.


texts: How can I optimise an Update on a large table?

texts:
I am using Oracle 11g, I have to update 7 fields on a table with 14 millions of rows, the updated columns get the new value from functions, the update looks like:

update hslak
set HSLAKSTS =  reverseheb(hslaksts) ,
HSLAKSBSTS=reversehebchars(hslaksbsts),
HSLAKIVTK=reverseheb(HSLAKIVTK),
HSLAKMKOR=reverseheb(HSLAKMKOR),
HSLAKIRGTG=reverseheb(HSLAKIRGTG),
HSLAKMCR= reverseheb(HSLAKMCR),
HSLAKMASL=reverseheb(HSLAKMASL);

it run for 6-7 hours and it is not over yet.

Would it be faster to create a new table rather then update it? Would it be better to create a loop and update 100000 records each time and commit after each iteration?