texts: Add database to existing replication which uses binlog_do_db?

texts:
We have two MySQL 5.5 servers running on Ubuntu 12.04.

We are successfully replicating 3 databases from server01 to server02, but need to add a 4th (new) database.

Our my.cnf on the master has:

binlog_do_db = database1
binlog_do_db = database2
binlog_do_db = database3

How do I add the 4th database to replication? Everything I've read has indicated we'll need to restart MySQL, is there any way around this? Or do we just need to schedule it in?

This is the plan we have so far, but I was hoping for a method which didn't require a restart. Am I overcomplicating this?

GRANT REPLICATION SLAVE ON *.* TO 'slaveusr'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
QUIT;

Edit my.cnf to include the new database:

binlog_do_db = database4

Restart MySQL

  service mysql restart

Backup the database for the initial copy to the slave server

USE database4;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

In a new shell

mysqldump -u root -ppassword --opt database4 > database4.sql
scp database4.sql root@server02:/home

In the old shell

UNLOCK TABLES;
QUIT;

Change my.cnf on the slave server

replicate-do-db = database4

Restart MySQL

service mysql restart

Restore the dumped DB

mysql -u root -ppassword database4 < database4.sql


texts:
I just upgraded from Debian Squeeze to Wheezy and mysql stopped working. Blindly following some advice without any understanding of what I was doing, I removed ibdata1 in an attempt to solve the problem. Unfortunately, it was only after doing so that I realised this was a terrible idea, especially as I was missing this option in my.cnf: innodb_file_per_table = 1.

I found the cause of my problem and now my DB server is working again. However, now, I am left with a mess. My problem is that some (but not all) of my tables, which appear when I do show tables;, are giving me this error:

ERROR 1146 (42S02): Table 'db.tablename' doesn't exist

I have since enabled the setting innodb_file_per_table = 1 (perhaps this was a bad idea?) and before I came to realise that I probably didn't have a chance, I was trying out the innodb_force_recovery flags.

I'm fairly confident that these tables are gone for good so I'm trying to restore my database from earlier dumps. The problem is that the import breaks on these tables that "do but don't" exist. I have tried dropping the database before doing the import but there are clearly some other files lying around. Needless to say I'm reluctant to do any more speculative deletion of files...

Any advice on how to clean up this mess?


texts: How to select a particular element from json array

texts:
I have a 'notifications' table with a column 'dest_user_id_arr' as json with the following value:

id                     dest_user_id_arr
1                      {"users":[83,84,85]}
2                      {"users":[89,83,92]}
3                      {"users":[87,88,83]}

I would like to select those id where user is 83.


texts:
I'm currently working on a wiki-esque application using CouchDB and am trying to implement a document versioning scheme.  The way I see it there are two ways of doing this:

Store each version as a separate document
Store older versions as attachments to a single document.

Right now, I've got a form of #1 working.  When a user edits a document and saves it, the back-end first copies the previous revision to a new document and then saves the new version.  Each document has a 'history' array that contains data on each version (the document _id of the old version, a timestamp, the editor, etc.).

Since this history array could get pretty lengthy for a frequently updated document, I have a view that fetches a document sans history during a normal read (and another view for fetching the history).

My question is this:  I feel uneasy about my current approach and have been thinking about changing to the 'attachment' method.  But I'm not sure.  I'm hoping someone who knows CouchDB better than I (I've only been at this for a couple weeks -- and this is my first project using CouchDB... and NoSQL) can tell me what the pros and cons are of each approach.  Or is there perhaps some other versioning scheme that I'm overlooking?


texts: Old records disappearing after upgrade from 9.4.1 to 9.4.4

texts:
I recently upgraded from 9.4.1 to 9.4.4.  Since then millions of rows have disappeared from several tables containing historical data.

The severity of data loss seems to be directly proportional to the age of the records.  1-2% of the data from 10 days ago is missing, but >90% of the data from 50 days ago is missing.

I haven't seen anything new in the logfile except:
    LOG:  MultiXact member wraparound protections are now enabled
The 9.4.2 release was supposed to fix an issue with MultiXact wraparound.http://www.postgresql.org/about/news/1587/

I've temporarily disabled autovacuum for this weekend.

I'm using OpenBSD 5.8.

EDIT: Tue Sep 29 13:13:08 PDT 2015

I found the following interesting statement in the logfile:

STATEMENT: delete from history_uint where ctid = any(array(select ctid from history_uint where itemid=96902 limit 500))

EDIT: Tue Sep 29 14:04:41 PDT 2015

Resolved.  Not a pgsql bug.  When the sysadmin upgraded the server OS he deleted a configuration file for the application using the DB.  The configuration change enabled a 'housekeeper' task which deletes old records. Mods are welcome to delete this question.


texts: SQL Server - What is the RAISERROR substitution parameter for boolean/bit type?

texts:
I'd like to quickly dump some context into an error message including some boolean flags using the handy-dandy substitution parameters of RAISERROR such as

CREATE PROCEDURE MyProc
(
    @param1 INT = 1,
    @param2 VARCHAR(255) = 'hello world'
) AS BEGIN
    ...
    RAISERROR('Inconsistent state occurred processing @param1=%d @param2=%s', 16, 1, @param1, @param2)
    ...
END

According to the documentation there is no type specification for bit type (boolean), but notes that

  These type specifications are based on the ones originally defined for the printf function in the C standard library.

And as this post notes,

  ... since any integral type shorter than int is promoted to int when passed down to printf()s variadic arguments, you can use %d

But it does not work in SQL, as I get the following error (2748):

  Cannot specify bit data type (parameter ###) as a substitution parameter.

Am I forced to use a helper variable?

Thanks.

P.S. Using compatibility mode for 2008R2.


texts: How to take pg_dump of a very large postgres database?

texts:
I have a production database server running Postgres 8.4 on Cent OS 6.5.The volume of data has increased recently and within few months database now has 18500 tables amounting to approx 350GB database size.

Problem is now I can't even take a pg_dump of this database, Every time I try  I get OutOfSharedMemory error for max_number_of_shared_locks (current value is 256 instead of default 64) ?

How can we take dump of such a huge database ? please help..


texts:
I am trying to login to an Oracle 10g database vía sqlplus.

In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish.

In the database server my NLS_LANG is SPANISH_SPAIN.WE8ISO8859P1.

In the client my NLS_LANG is AMERICAN_AMERICA.UTF8.

I tried setting this with an after logon trigger, but it does not work.
If I change this setting vía ALTER SESSION it works ok, but it is not a solution.

The database server is a Red Hat Enterprise 5.6
The client is a CentOS 7.5.
I am using Oracle Instant client 10.2.0.5.

I do not know how to continue...


texts: Geography data type and Upgrading from 2008 r2 to SQLServer 2014

texts:
I am getting a warning about a data type called geography.

Our system has 1 column of data type geography. I am trying to figure out if the upgrade will fail or just if an app may have an issue.

Warning about client side usage of GEOMETRY, GEOGRAPHY and HIERARCHYID

Anybody have an idea? Under Importance of error there is red ball with an X through it, so I assume the upgrade will fail. Is that correct? It says to fix before. I notice SQL Server 2014 has the geography type still, so I do not know what to think.

From export to csv it shows this:

  Database Server
  PreUpgrade
  Warning about client side usage of GEOMETRY, GEOGRAPHY and HIERARCHYID
  Upgrade Advisor detected usage SQL CLR Types
  Database: Metrics
  Column Name: Location


texts: Index manteinance: does the order of processing make a difference?

texts:
I have an 800GB SQL Server Enterprise database with very bad performance. So, based on a few scripts I found online I'm creating a job on my SQL database to maintain our indexes. Most of my tables have a clustered index and 3 or 4 non-clustered.

Does it make any difference the order in which the indexes are being
rebuild/reorganized?
Is it the same if I first reorganize the clustered index and then the
nonclustered ones or if I do it the other way?


texts: oracle streams apply: how to get a reason why LCR message was not applied

texts:
I've set up bidirectional oracle streams replication (11gR1) using identical scripts on both machines (DB1 and DB2). Although changes from DB1 are being applied to DB2, changes from DB2 to DB1 aren't.

I have only one rule for capture processes that checks for apply tag to prevent cyclic propagation, and have no rules for apply processes. LCRs from DB2 are dequeued at DB1 by apply reader process (update LCRs are among dequeued messages for sure, because when I issue 50 inserts at DB2, at DB1 dequeued messages counter increases by 50), but aren't processed by apply coordinator and servers :

Apply           Apply           Apply Apply
Name            Queue           Tag   Status
--------------- --------------- ----- ------------------------------
FROM_DB2_APP    FROM_DB2_APP_Q  02    ENABLED

Apply                                Messages       Last
Name            State                Dequeued        SCN
--------------- ------------------ ---------- ----------
FROM_DB2_APP    DEQUEUE MESSAGES          102    1118751

Apply                             Trans   Trans   Trans   Trans   Trans   Trans
Name            State             Appld  Assign    Rcvd   Ignrd  Rollbk   Error
--------------- --------------- ------- ------- ------- ------- ------- -------
FROM_DB2_APP    IDLE                  0       0       0       0       0       0

Apply           Server  Messages
Name                ID   Applied State
--------------- ------ --------- ------------------------------
FROM_DB2_APP         1         0 IDLE

As far as I understand, in that case LCRs can be silently ignored (without throwing apply error) only if SCN of LCR is lesser that instantiation SCN for a table, but instantiation SCN is 1114348 (< 1118751):

Source       Object       Object       Instantiation
Database     Owner        Name                   SCN
------------ ------------ ------------ -------------
DB2          DUMMYUSR     DUMMYTBL           1114348

Oracle provides means to deal with errors, but how to check why message was not applied if there was no error?


texts: Help me understanding this Exercise Question

texts:
This question is from a Book "SQL with and Introduction to PL/SQL by Lannes L. and Morris Murphy. This question is from one of its exercises. I cant understand what this question is asking.

  Q. Determine which books generate less than 55 percent profit and how many copies of those books have been sold recently. Summarize your findings for management and include a copy of the query necessary to retrieve the data from the database tables.

To retrieve the results, Books table joins Orderitems on ISBN and Orderitems joins Orders table on ORDER#

BOOKS TABLE

ISBN       TITLE                               PUBDATE        PUBID       COST     RETAIL CATEGORY
---------- ----------------------------------- --------- ---------- ---------- ---------- ------------------
1059831198 BodyBuilding in 10 Minutes a Day    21-JAN-01          4      18.75      39.95 Fitness
0401140733 Revenge of Mickey                   14-DEC-01          1       14.2         22 Family Life
4981341710 Bulding a Car with Toothpicks       18-MAR-02          2       37.8      59.95 Childern
8843172113 Database Implementation             04-JUN-99          3       31.4      55.95 Computer
3437212490 Cooking with Mashrooms              28-FEB-00          4       12.5      19.95 Cooking
3957136468 Holy Grail of Oracle                31-DEC-01          3      24.25      75.95 Computer
1915762492 HandCranked Computers               21-JAN-01          3       21.8         25 Computer
9959789321 E-Business the Easy Way             01-MAR-02          2       37.9       54.5 Computer
2491748320 Painless Child-Rearing              17-JUL-00          5         48      89.95 Family Life
0299282519 The Wok Way to Cook                 11-SEP-00          4         19      28.75 Cooking
8117949391 Big Bear and Little Dove            08-NOV-01          5       5.32       8.95 Childern
0132149871 How to get Faster Pizza             11-NOV-02          4      17.85      29.95 SelfHelp
9247381001 How to Manage the Manager           09-MAY-99          1       15.4      31.95 Business
2148428890 Shortest Poems                      01-MAY-01          5      21.85      39.95 Literature

ORDERS TABLE

ORDER#  CUSTOMER# ORDERDATE SHIPDATE  SHIPSTREET                SHIPCITY             SH SHIPZ
------- --------- --------- --------- ------------------------- -------------------- -- -----
  1000       1005 31-MAR-03 02-APR-03 1201 Orange Ave           Seattle              WA 98114
  1001       1010 31-MAR-03 01-APR-03 114 East Svannah          Atlanta              GA 30314
  1002       1011 31-MAR-03 01-APR-03 58 Tilla Circle           Chicago              IL 60605
  1003       1001 01-APR-03 01-APR-03 958 Magnolia lane         Eastpoint            FL 32328
  1004       1020 01-APR-03 05-APR-03 561 Roundabout Way        Trenton              NJ 08601
  1005       1018 01-APR-03 02-APR-03 1008 Grand Avenue         Macon                GA 31206
  1006       1003 01-APR-03 02-APR-03 558A Capital Hwy          Tallahassee          FL 32307
  1007       1007 02-APR-03 04-APR-03 9153 Main Street          Austin               TX 78710
  1008       1004 02-APR-03 03-APR-03 69821 Southe Avenue       Boise                ID 83707
  1009       1005 03-APR-03 05-APR-03 9 Lightining Rd           Seattle              WA 98110

ORDERITEMS TABLE

ORDER#      ITEM# ISBN         QUANTITY
----------- ----- ---------- ----------
  1000          1 3437212490          1
  1001          1 9247381001          1
  1001          2 2491748320          1
  1002          1 8843172113          2
  1003          1 8843172113          1
  1003          2 1059831198          1
  1003          3 3437212490          1
  1004          1 2491748320          2
  1005          1 2147428890          1
  1006          1 9959789321          1
  1007          1 3957136468          3
  1007          2 9959789321          1
  1007          3 8117949391          1


texts: Low cardinality estimate in hash match operator

texts:
I am trying to fix a performance problem with one of our reporting queries on SQL Server 2008 R2.
I have included the part of the query that is causing low estimate.  This part is further joined with other tables. Since the estimate for this one is so low, further joins end up being nested loop and causing the query to run forever.
select n.Transactionid
from nath n
WHERE StatusId = 3 and
Date IS NOT NULL and
NOT EXISTS (SELECT 1 FROM nath
WHERE Transactionid= n.Transactionid
AND StatusId = 3
AND HistoryId < n.HistoryId)

Estimated plan

The estimate for the hash match is only 1.17 but in reality there are 550K records coming out. Statistics have been updated with full scan.
I ran the exact same query on one of our SQL Server 2014 instances and it produced better results; the estimate was 557K on the hash match operator. I then tried trace flag 9481 to force the old cardinality estimator on 2014 and the estimates were back to 1. So I think the issue is something to do with old CE estimating self joins.
I tried trace flag 4199 on SQL Server 2008 R2 but that did not help.
Actual execution plan
I didn't want the actual tables names to be visible, so I created similar tables with fewer columns and different table and column names.  The estimates are slightly off than mentioned above but the bigger problem still persists.
SQL Server 2014 with TF 9481
(I don't have a SQL Server 2008 R2 test environment):

SQL Server 2014

Please let me know if there is anyway to fix this wrong estimate.
Repro
The issue can be simulated with the below script:
    create table nat ( c1 int identity(1,1) primary key,c2 int)

    declare @a int=1
    declare  @b int =1
     while @a<10000
     begin
        set @b=1
         while @b<=5
         begin
                insert into nat select  @a
                set @b=@b+1
         end
     set @a=@a+1
     end

select * from nat a where not exists (select 1 from nat b where b.c2=a.c2
 and b.c1<a.c1)
 OPTION(QUERYTRACEON 9481); -- estimated no of rows from hash match 1
select * from nat a where not exists (select 1 from nat b where b.c2=a.c2
 and b.c1<a.c1)  -- estimated no of rows from hash match 49995

I have done some testing with the above query on SQL Server 2012 and I am not able to force the new cardinality estimator behaviour with trace flag 4199.
Current testing results:

SQL 2014 - High estimates on Hash match operator
SQL 2014 with TF 9481 - Low(1) estimate
SQL 2012 - Low(1) estimate
SQL 2012  with TF 4199 - Still low estimates

How is it that I am able to replicate old cardinality behavior on 2014, but not able to get new CE estimates on 2012?
Is it that the change is not part of trace flag 4199 and only came about in 2014?
Changing the NOT EXISTS to a left join seems to produce a better estimate.


texts:
In the data directory of Mysql some files are corrupt. Recovering is not working anymore. I have a complete sql backup and want to setup a new db. The complete stuff is installed with the use of wampserver.

I just renamed the :
D:\wamp\bin\mysql\mysql5.6.17\data
to
D:\wamp\bin\mysql\mysql5.6.17\data_old

and made a new folder called
D:\wamp\bin\mysql\mysql5.6.17\data

Changed socket 3306 to 3307 but now getting:

Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

How should I reinit a new db (windows 8 64b)?


texts: Rebuilding system and tables, should I change primary key to int?

texts:
I am in the process of rebuilding a current system (orders, invoicing, payments, Vendors, etc), which includes normalizing the current tables with more tables and lookup tables to spead up queries,reporting, and implement new system features.

Their current customer table using a CustomerNumber as PK with a format like 'A1005'.  The letter is the first letter of the customer name, and then the number is incremented by 1 for each additional customer with that letter, starting at 1000.  The customer wants to keep the CustomerNumber field as part of the new system, so the addition of an int PK field would be for internal purposes only.

Once the new system is build, I have to convert the old data/tables to the new system tables. If I did add an in PK, then I would have to change all the FKs in the other related tables (orders, billing, shipping info, etc) to link instead to the new int PK field.

This user had only about 1500 customers, but has 20k to 100k records in the other tables (orders, billing, etc). Searches are often made on the CustomerNumber or CustomerName.

So I am wondering, should I add a integer primary key to the customer table for all the reasons that using an int PK is a good idea? Would there be any benefit to changing the primary key for this table in the new system? And would those benefits compensate for the more complex conversion I would need to do for the old data to import into the new system?

Thanks!


texts: Add database-level collation to PostgreSQL

texts:
So I have added a en_US collation to my system:

# locale -a
C
en_US.utf8
POSIX
sk_SK.utf8

Based on Postgres' documentation I have added it to my Postgres instance:

postgres=# select * from pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype
------------+---------------+-----------+--------------+-------------+------------
 default    |            11 |        10 |           -1 |             |
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 sk_SK      |            11 |        10 |            6 | sk_SK.utf8  | sk_SK.utf8
 sk_SK.utf8 |            11 |        10 |            6 | sk_SK.utf8  | sk_SK.utf8
 ucs_basic  |            11 |        10 |            6 | C           | C
 en_US      |          2200 |        10 |            6 | en_US.utf8  | en_US.utf8

As you can see the 'collnamespace' of newly added collation is different than the others. My assumption is that this is why I don't have a choice of  'en_US' while creating new database.

Question is: what I need to do to change collnamespace '2200' to '11' or straightly say to create a database with 'en_US' collation? For database creation I'm using pgAdmin tool.


texts: SQL Server: Insufficient system memory in resource pool 'default' when creating a database from mdf/ldf

texts:
I've been struggling to import a database backup I have been given into my local SQL Server environment. In Server Management studio (v18.6) I am attempting to run the following query:
Create database mydbname
On
(Filename='D:\some\path\master.mdf'),
(Filename='D:\some\path\mastlog.ldf')
For attach;

(These files are not large, 4mb and 2mb respectively).
This is the error message I am getting:

Msg 1813, Level 16, State 2, Line 1 Could not open new database
'mydbname'. CREATE DATABASE is aborted.

Msg 701, Level 17, State 192,
Line 1 There is insufficient system memory in resource pool 'default'
to run this query.

I have 32GB of RAM, my "Maximum Server Memory" setting is 16000. (Windows 10 Pro, version 20H2).
If I open the properties dialog of Management > Resource Governer > Resource Pools > System Resource Pools > default, the settings are as follows:
Workload groups for resource pool: Memory Grant % = 25
External resource pools: Maximum Memory % = 50
I tried various different values here to no effect. Looking around for others who have reported this error message hasn't turned up anything very helpful.
Any ideas?
Thanks in advance.
Edit:
SELECT @@VERSION
Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64)   Nov  6 2020 16:50:01   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 19042: )


texts: Why do I keep getting ORA-01033: ORACLE initialization or shutdown in progress when my database is open? (12c)

texts:
I'm running Oracle on a Windows server (maybe that is why it is so buggy?). It was working fine for a bit, but lately I've been having an issue.  I keep getting the ORA-01033: ORACLE initialization or shutdown in progress error.

If go into the server and run SQLPlus and check the v$instance table it says my instance is open.  I'm able to switch to my pluggable database.  v$database shows  that my database is read/write.

I have a linked server set up from my MS SQL Server and it will work for awhile and then return ORA-01033.  So, maybe it's just SQL Server I say and try SQL Developer.  I get the same error!  Just for kicks I create a new connection on SQL Developer, and it works.  For a little while.  Then I get this:

I only have ~30 GB of freespace on the drive where Oracle is loaded.  Could that be causing the issue?  If I reboot the server it works.  For a little while.  Basically, it seems to work until I start hammering on it and pulling a lot of data out.


texts: MySQL Workbench Synchronization False Positives

texts:
I have synchronized a table between a model and a live server database in Workbench, but every time I rerun the "Synchronize With Any Source..." wizard, Workbench detects (falsely) that there is a difference between the model and the server for that table and wants to execute the exact same SQL query.

The SQL in this case is the following:

ALTER TABLE `mydb`.`user_info`
CHANGE COLUMN `last_update` `last_update` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This statement has already been executed in my first run of the Wizard, and I can see that the table user_info is up to date on the live server. Why does it keep detecting it as a difference? Is it a known bug with MySQL Workbench or something...?


texts: I have a large PostgreSQL database running on a server with limited memory. I often use inner joins on only one (unique) column as a way of filtering (is this bad practice?). I’ve noticed that the planner often uses hash joins for my queries, and I wonder if it will hash the whole table even if I only use one column. That would be a problem since I have limited memory. If so, would I be better of using semi joins for filtering?


texts: How should I model a database structure to retain calculation results?

texts:
I am trying to improve the performance of the queries related to one table that I consider "weird", which could be created by the next script:

CREATE TABLE `CalcTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `result` double DEFAULT NULL,
  `calc_dt` datetime DEFAULT NULL,
  `project_id` int(11) NOT NULL,
  `calculation_type_cd` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`));

Currently, we have 17 different calculation types. A calculation type could be:

"current kilowatt per hour",
"expected kilowatt per hour",
"forecast kilowatt per hour",
etc.

The data could grow to 250 different calculation types in the future. Also, a situation that may arise is that a customer could create his own calculation types, as much as he/she wants...

So I tried to understand how can I design a table structure to work with this data on the best way now, and to not crash the database in the future (with 250+ new calculation types).

Below you can find current data sample:

id       result  calc_dt              project_id  calculation_type_cd
-------  ------  -------------------  ----------  -------------------
1906901  0.1028  2016-01-18 15:00:00         188  TypeA
1906902  336.68  2016-01-18 15:00:00         188  TypeB
1906903  341.55  2016-01-18 15:00:00         188  TypeC
1906904  0.4531  2016-01-18 16:00:00         188  TypeD
1906905  368.39  2016-01-18 16:00:00         188  TypeA
1906906  38.080  2016-01-18 16:00:00         188  TypeB
1906907  420.34  2016-01-18 16:00:00         188  TypeF

So you can imagine what would be here in the future... Dozens of rows with some columns containing duplicated data...

I'm thinking about two options:

Create 250 columns for storing results for different calculation types
Create different tables for some groups of calculation types

What do you think about it? Does it make sense?

Maybe you have better approaches - please let me know!

Thanks a lot in advance.

I'm using MySQL 5.7 on AWS.


texts: AlwaysOn Architecture - SQL Server 2012 for creating a dev environment?

texts: I was thinking if the AlwaysOn architecture in SQL Server 2012 can be leveraged somehow to create a replica for DEV and QA environment properly in addition to setting up HADR using the same architecture?


texts: Why is SQL Server using a clustered index scan for a self referencing FK cascade delete

texts:
I have made an example to allow me to show what my issue is:

Setup:

CREATE TABLE [dbo].[Test](
    [TestId] [bigint] IDENTITY(1,1) NOT NULL,
    [ParentTestId] [bigint] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestId] ASC)
)
GO

ALTER TABLE [dbo].[Test]  WITH CHECK ADD  CONSTRAINT [FK_Test_ParentTest]
      FOREIGN KEY([ParentTestId])
REFERENCES [dbo].[Test] ([TestId])
GO

ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_ParentTest]
GO

DECLARE @iter INT
SET @iter = 1
WHILE @iter < 1000
BEGIN
    INSERT INTO dbo.Test ( ParentTestId )
    VALUES  ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
    ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
    ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
    ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
    ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null )
    SET @iter = @iter + 1
END
go

This creates a self referencing table and adds over 40,000 rows to it.

Action:

DELETE FROM dbo.Test WHERE TestId = 200

I then want to delete a row from this table.  If you turn on the actual query plan and run the above statement you can see that 20% of the cost is taken in a Clustered Index Scan for the self referencing key.

This is not a big deal in this scenario, but my real scenario has over 25 million rows in a large table.

So, I have two questions:

Why is it doing an Index Scan?  It has a Primary Key/Clustered Index value.  Why would it not do a Index Seek?
How can I make it do an Index Seek?  (It is taking about 1 min to delete a row.)

Edit:
I thought that this could be due SQL Server looking to see if other rows were referencing the one I was deleting.  But I set "Enforce Foreign Key Constraint" to "No" and it still took the same cost do a clustered index scan.


texts: VM'd a SQL server, having trouble with external HDDs

texts:
First I am not a SQL DB. I inherited an old system and am trying to keep the wheels from falling off.
We have an MS SQL 2000 server (running server Win Server 2000) that we performed a P2V migration on. I did not realize that there are temp files running on an external USB HDD (that was connected to the old physical server) necessary for SQL operations.
Its looking like we can not connect the USB HDDs to the VM (due to age of the OS, and the HDD being a 3.0, doesnt look like VMWare supports 3.0 passthrough to Win Server2000).
So, is it possible to set those external HDDs up as a shared drive on another machine and run those temp SQL operations from the VM? As it is now the DB looks for a physical drive volume. We are considering swapping out the HDDs to USB 2.0, but this seems more time efficient. Thanks for any input


texts:
I'm trying to deduce a value in a select statement, then reuse that calculation a few times per row, as well as group by that value. In my case, I'm doing time_to_sec(timediff(created, lastHit)) a bunch of times:

select
     time_to_sec(timediff(created, lastHit)) diff
    , if(
         time_to_sec(timediff(created, lastHit)) > 1800
         OR time_to_sec(timediff(created, lastHit)) = 0,
         1, 0
      ) newSession
    , sum(1) as sessions
    , sum(
          if(
             time_to_sec(timediff(created, lastHit)) > 1800,
             0, time_to_sec(timediff(created, lastHit))
          )
      ) as totalDuration
    from pageviews
    group by newSession;

How can I improve that query without storing redundant data in my schema (such as the pre-calculated time difference between created and lastHit)?

I thought about using variables (select @diff := timediff(...)), but this post mentions that I should avoid assigning a variable and reading from it within the same statement.


texts: What's the optimal way to query data using another table column as a between filter

texts:
sorry for the ambiguous question title. I don't know what's it called.

The thing is let's say I've a order table storing uid , order_id and ip looks like this

| uid   | order_id |    ip
| 10000 |    1     | 2098957412
| 10001 |    2     | 2869229574
| 10000 |    3     |  825358912

and another ip table storing ip_from , ip_to and country_code

| ip_from    |   ip_to     |  country_code
| 10000      |   200000    |      GM
| 825358910  |  925358912  |      VN
| 2098957412 | 2198957412  |      FR

I'd like to do something like this

select order.uid, order.order_id, order.ip, ip.country_code
from order
left join ip
on  order.ip >= ip.ip_from
and order.ip <= ip.ip_to

to have a result like this

| uid   | order_id |    ip       | country_code
| 10000 |    1     | 2098957412  |     FR
| 10001 |    2     | 2869229574  |    null
| 10000 |    3     |  825358912  |     VN

What's the optimal query to having such a result?


texts: Having problems with concurrency while trying to lock a record in custom messaging system (Queue)

texts:
We are building our own custom messaging system and are having concurrency issues. Here are the rules:

A process (EXE) console application locks 3 records and returns them
No other process running (we have 5 EXEs running) can pick any record that the other processes have already taken.

That simple, but yet, I'm puzzled.

Summary of the SQL Server stored procedure doing a "Lock And Peek":

The idea behind this that we reserve three "NEW" records and change their status to "IN PROGRESS" with a ROWLOCK on the SELECT and UPDATE statements. So in theory these records should be locked for one process so that other processes can't update or even select them. Can someone tell me what I'm doing wrong please?

ALTER PROCEDURE [dbo].[LockAndPeek]
    @Count INT,
    @QueueTypeId INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ListofIDs TABLE(ID INT);
    DECLARE @StatusIDInProgress INT

    SELECT @StatusIDInProgress = ID
    FROM QueueStatuses (NOLOCK)
    WHERE Name = 'In Progress'

    INSERT INTO @ListofIDs (ID)
        SELECT TOP (@Count) Q.ID
        FROM Queues Q (ROWLOCK)
        INNER JOIN QueueStatuses QS (ROWLOCK) ON Q.StatusID = QS.ID
        WHERE QS.Name IN ('New', 'Errored')
          AND Q.TypeID = @QueueTypeID
          AND Q.AvailableTime IS NOT NULL
          AND Q.AvailableTime <= GETUTCDATE()
        ORDER BY Q.ID

    UPDATE Q WITH (ROWLOCK)
    SET STATUSID = @StatusIDInProgress,
        PROCESSED = GETUTCDATE()
    FROM Queues Q (ROWLOCK)
    INNER JOIN QueueStatuses QS (ROWLOCK) ON Q.StatusID = QS.ID
    INNER JOIN @ListofIDs LI ON Q.ID = LI.ID
    WHERE QS.Name IN ('New', 'Errored')

    SELECT
        Q.ID, Q.AvailableTime, Q.NumberOfTries,
        Q.Created, Q.Processed, Q.ErrorData,
        QT.ID QueueTypeID, QT.Name QueueTypeName,
        QS.ID QueueStatusID, QS.Name QueueStatusName,
        Q.Message
    FROM
        Queues Q (NOLOCK)
    INNER JOIN
        QueueStatuses QS (NOLOCK) ON Q.StatusID = QS.ID
    INNER JOIN
        QueueTypes QT (NOLOCK) ON Q.TypeId = QT.ID
    INNER JOIN
        @ListofIDs LI ON Q.ID = LI.ID
END


texts:
For example we have 2 scenarios:

Scenario 1:-
             We have very small buffer pool size of 100MB. My query data is 500MB. Does the         system keep 500MB query data in RAM such that a new query will read from RAM?

Scenario 2:-
           Buffer pool size is 1GB. In this situation will the system always keep 1GB free (in RAM) for MySQL query processing. Can the 1GB be used by other processes?


texts: Login failed for user even though it has read access

texts:
I have a SharePoint server which is using service account "spsharepoint".

For some reason this user has started giving 'Login failed for user DOMAIN\spsharepoint' when I checked SQL Server logs. Turns out it is trying to access 'master' database on which it doesn't have access so the error.

I gave read only access to this user on master database but error is still coming. What could be the issue and how do I fix this?


texts:
I am trying to do error handling as I can do in stored procedures in mysql but functions don't support to support them. Is there a way I can do something like this?

delimiter //

CREATE FUNCTION supports_ft()
    RETURNS INT
    BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' return 0;
    SELECT @@innodb_ft_cache_size;
    return 1;
END;


texts: How do I maintain high INSERT-performance on PostgreSQL

texts:
I'm working on a project which is parsing data from measurement files into a Posgres 9.3.5 database.

At the core is a table (partitioned by month) which contains a row for each measurement point:

CREATE TABLE "tblReadings2013-10-01"
(
-- Inherited from table "tblReadings_master":  "sessionID" integer NOT NULL,
-- Inherited from table "tblReadings_master":  "fieldSerialID" integer NOT NULL,
-- Inherited from table "tblReadings_master":  "timeStamp" timestamp without time zone NOT NULL,
-- Inherited from table "tblReadings_master":  value double precision NOT NULL,
  CONSTRAINT "tblReadings2013-10-01_readingPK" PRIMARY KEY ("sessionID", "fieldSerialID", "timeStamp"),
  CONSTRAINT "tblReadings2013-10-01_fieldSerialFK" FOREIGN KEY ("fieldSerialID")
      REFERENCES "tblFields" ("fieldSerial") MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT "tblReadings2013-10-01_sessionFK" FOREIGN KEY ("sessionID")
  REFERENCES "tblSessions" ("sessionID") MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT "tblReadings2013-10-01_timeStamp_check" CHECK ("timeStamp" >= '2013-10-01 00:00:00'::timestamp without time zone AND "timeStamp" < '2013-11-01 00:00:00'::timestamp without time zone)
)

We are in the process of populating the table with data that has already been collected. Each file represents a transaction of around 48,000 points and there are several thousand files. They are imported using an INSERT INTO "tblReadings_master" VALUES (?,?,?,?);

Initially the files import at a rate of 1000+ inserts/sec but after a while (an inconsistent amount but never longer than 30mins or so) this rate plummets to 10-40 inserts/sec and the Postgres process rails a CPU. The only way to recover the original rates is to perform a full vacuum and analyze. This is ultimately going to be storing around 1,000,000,000 rows per monthly table so the vacuum takes some time.

EDIT: Here is an example where it ran for some time on smaller files, and then after larger files started it failed. The larger files look more erratic but I think it is because the transaction is only commited at the end of a file, around 40sec.

There will be a web front end selecting some items but no update or deletes and this is seen with no other active connections.

My questions are:

How can we tell what is causing the slowdown/rail the CPU (this is on Windows)?
What can we do to maintain the original performance?


texts: Optimising a Database Shrink (in terms of speed and reducing fragmentation)

texts:
We have a large database that has had a lot of older data stripped out, leaving  primary datafile (all objects are currently in the primary filegroup) of about 80Gb containing a little less than 20Gb of data. We could do to release a large portion of that unused space back to the underlying filesystem.

Shuffling up to 20Gb of data around is going to be slow as we are using spinning-disk-and-moving-heads type drives, and I'm told that the shrink process can lead to significant structural fragmentation as it puts blocks where they'll fit with little logic regarding putting related blocks near each other.

Am I right in thinking that the following will be both faster and result in a less fragmented result:

Create a new filegroup on a separate array (we currently have three arrays, one containing the system drive and tempdb, one containing data files and one containing transaction logs, I propose the new filegroup goes on the system array)
Migrate all our tables and indexes over to the new filegroup by moving the clustered index over (or creating one on the new filegroup then dropping it, for tables with no clustered index currently) then dropping and recreating other indexes. This is where I expect to see a speed benefit, as the data will be moving between spindles rather than between places on the same disks to there will be less "head thrashing", and if I understand the process it should reduce exiting internal fragmentation too.
Shrink the primary data file
Move the file containing the new filegroup back to the array intended for data files. This would be done either by detaching the database, moving the files, them reattaching it, or by creating another new group and migrating all the data from the current new one over to it (leaving the data in this new filegroup would make this process easier in future - we can't just migrate to a new filegroup in step 2 this way as I'm told you can't do that with the primary group)

There are windows when I can perform the process in a time when it will not be an issue to take the database offline if needed, so a bit of downtime will not be issue.

Am I over complicating this? Is there a better way to shrink the primary filegroup while not introducing the chance of significant extra internal fragmentation? With plenty of RAM in the machine (16Gb currently, with the total data in this and other databases being about 45Gb including archive data that is rarely touched), is the fragmentation likely to matter much anyway? Should I just run a normal shrink operation and twiddle my thumbs while it does its business?!


texts: Correlated subquery to find total larger than state average

texts:
I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal.

I know I first find the avg total per state:

SELECT  VendorState, Avg(InvoiceTotal) AS AvgStateInvoice
from Invoices I join Vendors V on V.VendorID= I.VendorID
group by VendorState

So I now have the list of avg InvoiceTotal by state. Now I need to figure out:

How to do an outer query to select those invoices larger than the state average and here is where I am lost, since I don't remember the syntax to do the comparison . I guess it would be something of the sort:

SELECT InvoiceId from Invoices where InvoiceTotal > .....?

Any ideas, please?


texts: Merge table from master slave replication

texts:
I have a dual master-slave replication (server1 is master AND slave of server2).

After a problem with server1, the replication broke. After some skipped logs the replication came back  to life.
Then I ran a checksum and there was one table with DIFFs:

+----------------+---+-----+--------+------+-------+--------+-----------+
| TS             |ERR|DIFFS|ROWS    |CHUNKS|SKKIPED|TIME    |TABLE
+----------------+---+-----+--------+------+-------+--------+-----------+
| 09-25T21:09:39 | 0 | 2   |5447202 | 181  |0      | 967.269|db1.myTable
+----------------+---+-----+--------+------+-------+--------+-----------+

I don't want to do a simple table sync from server2 to 1 because I'm afraid there might be data in server1 that it isn't in server2.

So, is there a way to make a merge between tables in a master-slave replication?


texts: Removing "duplicates" within a time range

texts:
I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple entries of the same plate for a short period, with slightly different entry times, as in the example below:

PLATE  | Date_in   | TIME_IN
DE54RPY|2013-08-29 | 14-24-30
DE54RPY|2013-08-29 | 14-24-36
DE54RPY|2013-08-29 | 14-24-42
DE54RPY|2013-08-29 | 14-24-48

I found another solution on this site that suggested using this to filter out only the minimum timestamp for each plate:

select b.*
from (
    select plate,date_in,MIN(time_in) time_in
    from tblin
    group by plate,date_in
) A
inner join tblin B
    using (plate,date_in,time_in)

The issue arises when a vehicle leaves the site and returns a while later, producing more entries in the table. These later entries will also be removed by the example solution above.

I need to find a way of filtering out a single instance (minimum or maximum) of a plate detection within a certain time period (e.g: a few minutes), and I'm not sure how to approach this. Could anybody here help me?


texts: Help with best practice of merging databases

texts:
Bad news, our website server (Windows 2003) crashed because of dead RAID controller.

Luckily few hours later our backup server was up and website was live again.

Hopefully tomorrow our original server will be fixed and I'm looking for the best practice to merge our data (SQL Server 2005).

This is the situation right now:

Original server has backed up data till 16th 01:30AM (night.bak)
Original server has data which is not backed up till 17th 00:30AM
so basically there is 23 hours missing on the night.bak

I restored the night.bak to the temporary server and since 17th 03:00 new data is being added to the temporary server.

Tomorrow I wish to take data from the temporary server (17th 03:00 till 19th) and put it back on the original server.

I believe I can't make differential backup on the temporary server and restore it on the original server because last backup on original server has timestamp of 16th 01:30AM but I don't really know so I'm asking here.

My main concern is to preserve data of joined tables that share index keys.

I don't know how to reply to the answer so I'm replying here :

I'm testing Red-Gate's SQL Data Compare (which is by the way 14 days trial and not 30) and it seems good to add / update / delete rows very easily.

But this is the problem:

old database has 120 records on tableA (primary key 1-120) when I left it
new temp database doesn't have all 120 records because only 100 records were backed up so, it has only 100 records (primary key 1-100)

Since we continue using the temp database it now has 140 records (primary key 1-100 from original database and 101-140 from temp database).

The Compare would want to insert 40 new records to the tableA but it cannot use 101-120 key because it's already exists on the original database so I guess it will try to update and destroy them.

And anyway, it cannot insert with correct keys because, for example, I have table tbl_users (code(index), firstname, email) and table tbl_priceoffers (code(index), usercode(from tbl_users), price).

Red-Gate generate script to insert tbl_priceoffers before tbl_users
but even if it was the correct order (I can edit), it cannot insert tbl_priceoffers row without having @@identity from the recently inserted tbl_users row.


texts: What is the difference between the user `dbo` and the owner of the database stored in sys.databases

texts: We recently had a question come up where the user dbo in a database had an sid that didn't match the owner_sid in sys.databases.  I understand how the owner of the database is different from members of the role db_owner but I had always thought the user dbo was the actual owner of the database.  Is that not the case?  And if so are there any real differences between dbo and what's in sys.databases?


texts: MariaDB Out-of-Order Parallel Replication and OPTIMIZE TABLE

texts:
I am using MariaDB 10.4 and have question about Out-of-Order parallel replication.
I am optimizing databases(about 400GB of data) on Master every week. As optimizing DB on master do not have any negative impact, then replicated OPTIMIZE commands on slaves results in huge replication lags (up to half an hour). Can i use Out-of-Order parallel replication (https://mariadb.com/kb/en/parallel-replication/#out-of-order-parallel-replication) and set different gtid_domain_id for the OPTIMIZE commands on master to fix this ?
Please note that tables on master are being updated all the time. I am unsure if this is safe approach or not ?
Maybe it will be safer to NOT replicate OPTIMIZE commands on master and run them independly on all slaves ? Will this affect replication / lag ?


texts: Single query approach to split and copy string values in a column

texts:
This is a part of my previous question.

I have the below logic to split a string value from a column in table tblProjects as below:

SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID
SET @Docs = @Docs + '$'
SET @pos = 0
SET @len = 0

WHILE CHARINDEX('$', @Docs, @pos + 1)>0
BEGIN
  SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos
  SET @value = SUBSTRING(@Docs, @pos, @len)

  IF CHARINDEX('|', @value) >0
  BEGIN
    SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),
            @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)
    INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)
  END

  SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1
END

A sample input would be

insert into tblProjects(ID, Documents)
values('P_1', 'gdgfg|jkkgk$bkgkkj|kjgkgk$vjffj|Khkgjhg$jkgkvghv|kbkghhkgk');

I need to split the Documents string and insert the values into a separate table. Values delimited by | should go into separate columns of the same row, whereas values for a particular row are delimited by $.

Is there a single query method to complete this operation, for all rows in the table, in the minimum possible time?


texts:
I have a 500+ lines stored proccedure which I am writting and testing using the Microsoft SQL Management studio. Today I encounter the following 'strange' case. My query (in pseudocode) look like the following

create table #TempTable1 (Name varchar(30))

-- Some queries involving #TempTable1

drop table #TempTable1

-- Some queries

declare MyCursor cursor fast_forward for
select ......

open MyCursor

fetch next from MyCursor into @CursorValue
while @@fetch_status = 0
   begin

       create table #TempTable1 (Name varchar(30))

       -- Some queries involving #TempTable1

       drop table #TempTable1

       fetch next from MyCursor into @CursorValue

    end

close MyCursor
deallocate MyCursor

My query fails with an error in the second create table

  There is already an object named '#TempTable1' in the database.

I know that due to query optimizations the commands are not running in the sequence I am typing them. However this error still seems strange to me. I can put some go commands to force the execution, but this will create some other problems in my code e.g. I will have to redeclare some variables e.t.c. Renaming the tables will solve the problem but it will harm a little the readabillity of the code. So it is more 'curiocity' than an 'actual problem'.

Any suggestions on why this is happening and how I can avoid it?


texts: troubleshooting login failure when AD group seems to be fine

texts:
I am using the procedure xp_logininfo to find out what groups a user belongs to.

I have created an AD group called SQLDBA, and added myself to it,
then created a login and granted sysadmin to the group.

EXEC xp_logininfo 'mydomain\myname','all';
    GO

But still when I try to login to that SQL Server instance, using windows authentication, it says I don't have a login?

Where to look at in order to troubleshoot this?

the error message:


texts: What is the purpose of the keyword “||” in PostgreSQL?

texts:
I'm new to PostgreSQL and I'm trying to figure out what does the operator || mean?

Google didn't help with that...


texts: MS SQL Server 2016 - Database Security - Login Permissions

texts:
I have an SQL Server 2016 database with Always On structure. I have a login that I called x. My x login's properties are:

Default database: abc
Server roles: public (ticked)
User mapping: Map (ticked), abc database (ticked), default schema (dbo), permissions on abc database: db_owner and public
Guest account enabled for: qaz
Database role membership for qaz: public
Securables: Permissions: CONNECT SQL, VIEW ANY DATABASE
Status: Grant and enabled are ticked.

So my problem is:

When I connect to this user and trying expand databases. I have no problem with my own database. But when I try to expand other databases, sometimes it gives "The database <db_name> is not accesible (true one) and sometimes I can expand a few databases but I can't see any table or objects and I can't operation on this databases (false one).

Normally, it should be the same error ("The database <db_name> is not accesible) all of the other databases because I have no permission except my own database.

How can I check the this case and how can I fix it?

Best regards,


texts: Xp_delete deletes old backup even when the backup job failed

texts:
I run the following scripts for a backup job. After the backup job completes, (xp_delete) deletes the old copy of full backup.

Typically, if the backup fails the BACKUP DATABASE statement on the 2nd line fails which breaks out of the entire script. Now the problem is that the backup job fails and xp_delete command still deletes the old copy of the backup. I don't have any other copy of the backups.

DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10), SYSDATETIME(), 112 ) + N'.bak';

BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10

DECLARE @backupSetId AS INT
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'MYDB' AND backup_set_id = ( SELECT MAX( backup_set_id ) FROM msdb..backupset WHERE database_name=N'MYDB' );

IF @backupSetId IS NULL BEGIN
    RAISERROR( N'Verify failed. Backup information for database ''N'MYDB'' not found.', 16, 1 )
END

RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId

DECLARE @cutoff NVARCHAR(50) = CONVERT(NVARCHAR(25), DATEADD(HOUR, -20, SYSDATETIME()), 120 );

EXECUTE master.dbo.xp_delete_file 0, N'V:\Backup', N'bak', @cutoff

Note: I am using SQL Server 2008R2 standard edition .


texts: SQL SSDT Database Projects: Source Control Lookup Data

texts:
Does SQL Server SSDT Visual Studio 2017 Database Projects support Source control data for lookup tables, or do we still have to utilize messy Post-Deployment Merge Scripts for multiple tables?

Redgate has a more finesse, solution. Wondering when MS will catch up.

Related

Manage lookup data using SQL Server Data Tools (SSDT) (Benjamin Day Consulting)
Pre-Deployment And Post-Deployment Script - SQL Database Project (C# Corner)


texts: LOG backup Always ON using Ola Hallengren database backup script does not carry out a backup

texts:
I am trying to run a LOG backup on the primary node of an Always ON SQL Server 2012 environment. The secondary server is only used for replication. no read or other access permitted. (licensing of microsoft EE).

When I do a full backup using Ola Hallengren scripts, all goes well. A full backup is placed in the location I need.
But when I run the LOG backup, it does not carry out the backup at all.
Testing a Differential backup also went fine.
Doing a LOG backup manually, without Ola's script it does work too.

The backup I try is

USE [master]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[DatabaseBackup]
        @Databases = N'TESTAlwayON',
        @Directory = N'c:\backup',
        @BackupType = N'LOG'

SELECT  'Return Value' = @return_value

GO

The return value is 0, and the message shows all (default) values back, but not the actual backup statement, like it does when doing a FULL backup.

Any thoughts, what I do wrong?

Jimmy

UPDATE : Fixed.
I ran the command

ALTER AVAILABILITY GROUP AG1 SET (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)

And now it keeps the setting on primary and my log backups are running fine.

Thanks for the push in the correct direction.


texts: SSAS OLAP Semi-Additive Measure over Time Dimension

texts:
Issue:
Help, I'm relatively new to building OLAP cubes in Microsoft's SQL Server Analysis Services and I need help with a semi-additive measure over a time dimension.
In other words, I want to show the ending balance as a non-additive measure at quarterly and yearly aggregation points.
Facts:

I have a measure called Ending Balance which is the sum of all the accounts ending balances.

Dimensions:

I have a date dimension called As of Date. This dimension can be aggregated by year, quarter, and show monthly data points.

Desired Outcome:

I am using SQL Server Enterprise edition.
I've made sure that the Time dimension is properly specified with the correct types and attribute relationships. Reference link


texts: How to view the data from a .mysql file?

texts:
I have a .mysql database file. I need to open it and view the data. I've MySQL Server & MySQL Workbench installed into my Ubuntu 14.04.

Now how do I open the .mysql file and view the data inside it?


texts: How large is "too large" for SQL 2008 Web Edition?

texts:
For our soon-to-be-hosted application I need to choose between SQL Server 2008 Web edition and SQL Server 2008 Enterprise edition. There isn't any "must-have" functionality: the application currently runs on SQL Express, but it will exceed the 10GB maximum size that SQL Express imposes.

I'm looking at this comparison chart, and the most significant table in this page "Scalability & Performance". As tables get larger, functionality like "Table and Index Partitioning" would be vital to guarantee performance speeds. But at what table sizes would this be required?

What strategies should I use to determine if the size and usage of my database requires the Enterprise Edition Scalability and Performance functionality?


texts: Setting model title in MySQL Workbench for Forward Engineering

texts:
In MySQL Workbench, when viewing a model, you can select File->Export->Forward Engineer SQL Create Script.

The first four lines of the generated file are a comment, something like this:

  -- MySQL Script generated by MySQL Workbench
  -- Mon Dec 17 11:21:48 2018
  -- Model: New Model    Version: 1.0
  -- MySQL Workbench Forward Engineering

How do you change the model title and version?  Is there any way to have an autoincrement on version, especially with semver options autoincrementing the patch number?

I don't see it in any tab of Model->Model Options.


texts: Complex key constraint: allow only if uuid is new or another column matches

texts:
For my use case I am trying to get something like this to work with Azure Data Factory (ADF)

but I'm also interested in this question from a theoretical perspective. Is it possible to do something like this in a normal SQL engine like postgres?

I want to disallow inserts based on this key: (non_unique_id, timestamp) where timestamp is a unique value for that pair.

for example:

data to insert:

non_unique_id: 0cf6c19c14
timestamp: 1970-01-01 00:00:01

case 1 ALLOWED

before insert:

select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0 results

case 2 ALLOWED

before insert:

select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0cf6c19c14, 1970-01-01 00:00:01
0cf6c19c14, 1970-01-01 00:00:01
0cf6c19c14, 1970-01-01 00:00:01
...

case 3 NOT ALLOWED

before insert:

select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0cf6c19c14, 2038-01-19 03:14:05
0cf6c19c14, 2038-01-19 03:14:05
0cf6c19c14, 2038-01-19 03:14:05
...

Of course there are other columns but this illustrates the simplest cases


texts: Duplicate Same Table for Multiple locations

texts:
We have an "appointments" table with following schema.

appointments(app_id,.....,app_type,location_id)

app_type - type of appointment
location_id - keeping the location for multiple location setup.

We are using MySQL and expect about 20,000-50,000 rows inserted per day around 365 days.

The expected number of operations = number of appointments * 5

For each operation in our app, we'll read this appointment table to get particular appointment details and update certain flags in the same table.

SPEED is our main concern.
My question is, performance wise is it OK to

leave this schema as it is (large number of rows and many reads across it), or
keep separate appointments tables for locations?


texts:
I am working with mysql version 5.7.14 (homebrew installation on OS X El Capitan). My system did not shutdown properly while the mysql was running and after rebooting when i try starting the mysql daemon i am getting the below errors.

2017-02-07T10:11:42.224506Z 0 [Note] mysqld (mysqld 5.7.14) starting as process 18234 ...
2017-02-07T10:11:42.227375Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2017-02-07T10:11:42.228809Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-02-07T10:11:42.228821Z 0 [Note] InnoDB: Uses event mutexes
2017-02-07T10:11:42.228826Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-02-07T10:11:42.228830Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-02-07T10:11:42.229114Z 0 [Note] InnoDB: Number of pools: 1
2017-02-07T10:11:42.229227Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-02-07T10:11:42.230512Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-02-07T10:11:42.239632Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-02-07T10:11:42.270103Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-02-07T10:11:42.270567Z 0 [ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 44002250712 and the end 44002250240.
2017-02-07T10:11:42.270606Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-02-07T10:11:42.577436Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-02-07T10:11:42.577470Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-02-07T10:11:42.577484Z 0 [ERROR] Failed to initialize plugins.
2017-02-07T10:11:42.577488Z 0 [ERROR] Aborting

I tried restarting mysqld with innodb-force-recovery=1 but no success. Can anyone guide me as to how to recover from this state.


texts: Problem when using gtid with MySQL replication

texts:
I am faced with this problem when using gtid in MySQL replication:

  When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it
  to a different value after a COMMIT or ROLLBACK. Please check
  GTID_NEXT variable manual page for detailed explanation. Current
  @@SESSION.GTID_NEXT is 'xxx'"

It makes my slave SQL thread is impossible to run.

I am stuck with it. What is the cause of the problem ?


texts: Delete from table causes the PostgreSQL database to enter recovery mode

texts:
It's becoming common to see this problem in the development environment.
My application is not in production yet, but I'm worried this problem could happen.

As I develop new features, I commonly clear some tables when necessary.
I started to get into this problem with one specific table I try to clean.
I delete everything from other tables that could have foreign constraints, but when I try to delete from that specific table, pgAdmin loses the connection and the database enters in recovery mode.

First I get a warming saying:

  "The application has lost the database connection".

If I try several times to connect and execute again, then I get a message

  "FATAL: the database system is in recovery mode".

One thing that worked was to execute an update setting some field to it's own value on all rows (i.e update table set field = field).
After this I could delete all rows.

I'm actually using c# and EntityFramework 6 with the DevArt.PostgreSql plugin to control the database creation, but I generated the table script from pgAdmin to help understand the table structure. I also added one other table that has a foreign key to the table in question.

The application domain, code and database structure is written in pt-BR, so I tried to translate the table and fields names to help.
It's an application to control order requests and charges.

Table (request_event):

CREATE TABLE public.request_event
(
    id integer NOT NULL DEFAULT nextval('request_event_id_seq'::regclass),
    id_request integer NOT NULL,
    type integer NOT NULL,
    date timestamp without time zone NOT NULL,
    time_informed boolean NOT NULL,
    id_user integer,
    message text COLLATE pg_catalog."default" NOT NULL,
    visible boolean NOT NULL,
    occurrence timestamp without time zone NOT NULL DEFAULT '0001-01-01 00:00:00'::timestamp without time zone,
    CONSTRAINT request_event_pkey PRIMARY KEY (id),
    CONSTRAINT "FK_request_event_request_id_request" FOREIGN KEY (id_request) REFERENCES public.request (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT "FK_request_event_user_id_user" FOREIGN KEY (id_user) REFERENCES public.user (id_person) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.request_event OWNER to postgres;

CREATE INDEX "IX_request_event_id" ON public.request_event USING btree (id) TABLESPACE pg_default;
CREATE INDEX "IX_request_event_id_request" ON public.request_event USING btree (id_request) TABLESPACE pg_default;
CREATE INDEX "IX_request_event_id_user" ON public.request_event USING btree (id_user) TABLESPACE pg_default;

Table (charge_event):

CREATE TABLE public.charge_event
(
    id integer NOT NULL,
    id_charge integer NOT NULL,
    CONSTRAINT charge_event_pkey PRIMARY KEY (id),
    CONSTRAINT "FK_charge_event_charge_id_charge" FOREIGN KEY (id_charge) REFERENCES public.charge (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT "FK_charge_event_request_event_id" FOREIGN KEY (id) REFERENCES public.request_event (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.charge_event OWNER to postgres;
CREATE INDEX "IX_charge_event_id" ON public.charge_event USING btree (id) TABLESPACE pg_default;
CREATE INDEX "IX_charge_event_id_charge" ON public.charge_event USING btree (id_charge) TABLESPACE pg_default;

Delete SQL (fail):

DELETE FROM charge_event;
DELETE FROM request_event;

Delete SQL (worked):

DELETE FROM charge_event;
UPDATE request_event SET type = type;
DELETE FROM request_event;


texts: Big difference between send_request_date and sent_date in sysmail_sentitems after running sp_send_dbmail,

texts:
A send DB mail process was executed on Friday:

placed on the mail queue at 2016-03-04 12:21:22.937,
it wasn't sent until 2016-03-04 16:00:01.000, 1311 seconds later.

Usually DATEDIFF(SECOND,send_request_date,sent_date) is at most a few seconds. What could have caused this massive time gap?

Diagnosed via

SELECT *, DATEDIFF(SECOND,send_request_date,sent_date)
FROM msdb.dbo.sysmail_sentitems;


texts: Which is more efficient, trigger or PHP code?

texts:
My requirement is to move EMR data for a patient to a set of history tables when the patient has been discharged. I proposed to use an after trigger on the table housing the discharge flag, checking new\old values on the flag to ensure I only take action when the flag is initially set to discharged. The Sr. app developer writing the PHP side says that doing this in PHP is at least 4 times faster than the trigger. To me this doesn't make any sense but my background is in SQL Server, not MySQL so I am looking to the community for help.

Additional info:
Table housing the discharge flag is heavily used even after the patient has been discharged. There are currently 7 narrow tables (total of about 70 columns) that would have data inserted by trigger.


texts: Set Oracle Home (install Oracle Home) for Toad 12.6

texts:
I had Toad 11.5 and I'm trying to install Toad 12.6, to connect to Oracle 11g on a 64-bit Windows 7 machine.

No matter what I try, when I start Toad 12.6, I get the error: "No valid Oracle clients found.  You need at least one 64-bit client properly configured."  In the install, I used a downloaded win64_11gR2_client.zip file and it sure looked like it installed.

I've finally blitzed everything I could of Toad 11 and Oracle (using https://superuser.com/questions/256762/how-do-i-completely-remove-oracle-11g/762432#762432).

Somehow, I need to still install Oracle, or somehow set the Oracle Home.  http://documents.software.dell.com/DOC220605 says how to edit the oracle home, but even when I had Toad 11 working, I couldn't get anything when clicking the ... button in 12.6, because "You do not have any Oracle homes installed."

I've tried installing the client directly, and I get an error about the path too long.  I ignore the error and the install dies.  From research, it has said that uninstalling the 32 bit client is the way to go.  It doesn't appear to be the way to go.

What do I try next?


texts: How would you design a table for approval system between User and Sports Club

texts:
I am currently learning database modelling and struggling to model the following requirement. I have a user and sports_club table. User can only join a sports_club if three existing users of the club approve his request. If the number of members in club are less than three then all the existing members (whatever 2, 1 ) need to approve his request. My table looks something like this:

I am struggling to model this approval process in my database? Could anyone help me here? Sports club can have any number of users.


texts:
I have a fairly simple SSIS package (handful of Execute Task and a few Data Flow task). The end result I am looking to have:

I would like to get notified by email when an error occurs anywhere
in the package
I would like that email to include: Package name, failed task, and error information.

As I understand I set this up in the following manner:

Create an Event Handler for OnError for the package level (Excutable is set to the package name).
Configured the SMTP connection and added in an Send Mail Task
Within that task I configured an expression for the Subject and MessageSource. Those two expressions are noted below.

Expressions:
Subject - "Error occurred on " +  @[System::PackageName]
MessageSource - (line breaks added for readability)
(DT_WSTR,250)("An error occurred in execution of the package: " +  @[System::PackageName] + "\n" +
"The task that experienced the error: " +  @[System::TaskName] + "\n" +
"The error information returned:\n" +
"Error Code: " +  (DT_WSTR,50)(@[System::ErrorCode]) + "\n" +
"Error Description: " +  @[System::ErrorDescription])

Now what occurred after I forced an error:

Task failed as expected, but the event handler failed as well.
OnError event fired, except not as expected. First issue is truncation, received error A truncation occurred during evaluation of the expression. This is the content of the email received, which I don't see the expected new lines:

An error occurred in execution of the package:  The task that experienced the error: Failure notification The error information returned:
Error Code: 0
Error Description:

Second error for the same OnError: property "MessageSource" cannot be evaluated. Modify the expression to be valid.
I received two emails when the task failed.

Environment Info: SQL Server 2008 R2, running the package through BIDS only at this time.

Questions:

What do I have wrong in the expression for the MessageSource? I can obviously evaluate the expression successfully in the properties window, and understand that is because the variables are not populated. So how do you configure that were it will not truncate no matter what error is passed in, just set it to some large number length?
I am not looking for some custom method or code to perform this as the package is fairly simple and is only run every blue moon. However, would it be best to simply create an OnError event for each task? As I understand it any child task that fails has the error information passed up to the parent package, so it should work right?


texts:
I need to store some sensitive data in Cassandra and require it to be encrypted at rest. Is it likely Cassandra would function normally on top of a Linux encrypted file system (something like the open source TrueCrypt product)?

The other alternative available is to do the encryption at the application layer before sending the data to Cassandra but that entails writing plumbing code, and security plumbing code at that, which it would be good to avoid if at all possible.


texts: How Can I Convert SQL Server 2012 Version 11.0.3000.0 to SQL Server 2012 Version 11.1.3000.0?

texts:
I have SQL Server 2012 with version 11.0.3000.0 .

I need upgrade my version to SQL Server 2012 SP1 because I need install Cumulation 4 in Sql Server 2012 Cumulative Update 4 for use PowerPivot in Multidimensional Database.

Before install Sql Server 2012 Cumulative Update 4 I need Upgrade my SQL Server to Service Pack 1.

How Can I Update My Version to SP1.

I sow this Link and download SQLServer2012SP1-KB2674319-x64-ENU.exe and SQLServer2012SP1-FullSlipstream-ENU-x64.iso but this not work in my Server.

I have this error :


texts: What is ERROR_STATE() in SQL Server and how it can be used?

texts:
I read that ERROR_STATE() can help to distinguish between different states/locations in the source code where same type of error can occur. But it is not really clear to me that how it can be useful.

MSDN states:

  ERROR_STATE() Returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

How it can be really used? Can some one give me an example, the ones provided in this reference article don't really help explain things well for me?


texts: Generate Data Only Script without Identity SQL 2017

texts:
I am using the script generator in SQL 2017 to create an INSERT script.

When generated, it includes the Identity which I don't want.

I have set Script Foreign Keys and Script Primary Keys to false but it is still including them.

My table is:
ID (Identity), machnineno, amount

what I want to create is an INSERT script that looks like

INSERT tablename (machineno,amount) values (1789,15)
INSERT tablename (machineno,amount) values (3894,22)
INSERT tablename (machineno,amount) values (2097,9)

Currently it is doing

INSERT tablename (id,machineno,amount) values (22,1789,15)
INSERT tablename (id,machineno,amount) values (45,3894,22)
INSERT tablename (id,machineno,amount) values (77, 2097,9)

How can I generate a script without the ID?


texts: Is there a difference in performance between @date and getdate()?

texts:
Usually I use the getdate() function in my where clauses to go back in time.
Something like:

 DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE())

Will SQL Server 2008R2 perform faster queries if I first declare a date parameter and use that in my queries instead?

declare @dateNow date = getdate()
...
where
     DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,@dateNow )


texts: SQLServer - Multiple PIVOT on same column

texts:
I have a table data like the below with Type Regular/Irregular from January to December.
I need a query to do this data like the below table: January with below divided into two-jRegular & jIrregular. whole the year data using the PIVOT or Cross tab.
    CREATE TABLE [dbo].[Details](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Type] [nvarchar](10) NOT NULL,
        [Header] [nvarchar](50) NOT NULL,
        [SubHeader] [nvarchar](50) NOT NULL,
        [January] [decimal](18, 2) NOT NULL,
        [February] [decimal](18, 0) NOT NULL,
        [March] [decimal](18, 2) NOT NULL,
        [April] [decimal](18, 2) NOT NULL,
        [May] [decimal](18, 2) NOT NULL,
        [June] [decimal](18, 2) NOT NULL,
        [July] [decimal](18, 2) NOT NULL ) ON [PRIMARY]

        GO SET IDENTITY_INSERT [dbo].[Details] ON

        GO INSERT [dbo].[Details] ([ID], [Type], [Header], [SubHeader],
    [January], [February], [March], [April], [May], [June], [July])
    VALUES (2, N'Regular', N'Item-1', N'A.Sub-Item1',
            CAST(64.92 AS Decimal(18, 2)), CAST(57 AS Decimal(18, 0)),
            CAST(89.67 AS Decimal(18, 2)),
    CAST(99.20 AS Decimal(18, 2)), CAST(76.34 AS Decimal(18, 2)),
    CAST(23.33 AS Decimal(18, 2)), CAST(32.23 AS Decimal(18, 2)))
    GO

    INSERT [dbo].[Details] ([ID], [Type], [Header], [SubHeader],
    [January], [February], [March], [April], [May], [June], [July])
    VALUES (3, N'Irregular', N'Item-1', N'A.Sub-Item1',
   CAST(43.45 AS Decimal(18, 2)), CAST(65 AS Decimal(18, 0)),
   CAST(65.88 AS Decimal(18, 2)),
    CAST(56.77 AS Decimal(18, 2)), CAST(45.65 AS Decimal(18, 2)),
    CAST(56.87 AS Decimal(18, 2)), CAST(67.55 AS Decimal(18, 2)))
    GO

    SET IDENTITY_INSERT [dbo].[Details] OFF
    GO

    SELECT Header, SubHeader,
      Regular, Irregular,
      FRegular, FIrregular,
      MrRegular, MrIrregular
      FROM (SELECT Header, SubHeader, Type,
           'F'+Type AS FType,
           'Mr'+Type as MType,
           'Ap'+Type AType,
           'Ma'+Type as May,
      [January], [February], [March]
      from [CashFlow].[dbo].[Details] ) p

      PIVOT (MAX(January) FOR Type in (Regular, Irregular)) AS JPVT
      PIVOT (MAX([February]) FOR FType in (FRegular, FIrregular)) AS FPVT
      PIVOT (MAX(March) FOR MType in (MrRegular, MrIrregular)) AS MrPVT

Any one can help me.


texts: SQL Server Error 8632 due to over 100,000 entries in WHERE clause

texts:
My problem (or at least the error message) is very similar to query processor ran out of internal resources - extremely long sql query.

My customer is working with an SQL select-query, containing a where-clause with exactly 100,000 entries.

The query is failing with error 8632 and error message

  Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.)

I find it very peculiar that this error message is thrown, exactly at 100,000 entries, so I wonder if this is a configurable value. Is this the case and in case yes, how can I increase this value to a higher one?

On MSDN, there is the proposal to re-rewrite the query, but I'd like to avoid this.

Meanwhile I've found out that the list of entries I'm talking about contains natural numbers, quite some of them seem to be sequential (something like (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20).

This makes the SQL where-clause something like:

where entry in (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20)

I could transform this into:

where (entry between 1 and 3) OR
      (entry between 6 and 10) OR
      (entry between 12 and 13) OR
      (entry between 15 and 20)

Can this be shortened by:

where entry in (1,...,3,6,...,10,12,13,15,...,20)

...or something similar? (I know it's a long shot, but it would make software updates easier and more readable)

For your information: the data in the where-clause are the result of a calculation, done on another table: first the entries of that table are read and filtered at the beginning, then some extra processing is done (which is impossible to do using SQL), the result of that extra processing is more filtering and the result of that is used in the where-clause. As it was impossible to write the complete filtering in SQL, the mentioned method has been used. Obviously the content of the where-clause might change at every processing, hence the need of a dynamic solution.


texts: Remove all special characters without removing accented characters

texts:
I need to clean up a column that has all types of weird characters with in the string data. The weird characters are pretty much these []!@#$%.&*`~^-_{}:;<>/\|()

I only need to allow [0-9], [a-z] but also accented characters due to some spanish words being included.

I've been looking at https://dev.mysql.com/doc/refman/8.0/en/regexp.html to try to devise a regex that might fit this but my regex foo is weak.

Is there anyway to clean these up without having to look for and replace the special characters individually while still leaving the accented characters?


texts: How to store a version sequence per product

texts:
I need to create a table like the following one:

+----+---------+---------+
| Id | Product | Version |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       2 |       1 |
|  4 |       1 |       3 |
|  5 |       2 |       2 |
+----+---------+---------+

Where ID is an Identity(1,1) and I need the Version to be auto-populated depending on being inserted Product.

A similar effect could be achieved upon SELECT by using ROW_NUMBER() with PARTITION like this:

SELECT ID, Product,
ROW_NUMBER() OVER(PARTITION BY Product ORDER BY ID) AS Version
FROM Products

What are my options?


texts: Connect as postgres user on AWS RDS to install extension to pg_catalog

texts:
I need to connect to our AWS RDS PostgreSQL 9.5 instance so I can add the citext extension to pg_catalog schema so it is available to all schemas (PG prepends pg_catalog to the beginning of the search_path by default).

I'm easily able to do this on my local machine:

> sudo su - postgres
> psql
> \c mydatabase
> CREATE EXTENSION IF NOT EXISTS citext SCHEMA pg_catalog;
> \q
> exit

If I login to the RDS using a direct connection as AWS docs show, I am given permission denied for schema pg_catalog when I try to CREATE EXTENSION:

psql
   --host=myrdsawshostname
   --port=5432
   --username=myusername
   --password
   --dbname=mydatabase

This is the only way I know how to connect and I can only seem to connect as "myusername" not "postgres".

What am I doing wrong here?


texts: Correct sequence of MSSQL Server updates

texts:
What is the correct sequence to follow when updating MSSQL Server to a new service pack?

For example, if I have an instance of MSSQL Server 2016 RTM and I wish to update to SP2, do I need to first update to SP1 and and any of the cumulative updates in between or is it only necessary to update to SP2?

If there were cumulative updates available for SP2, would I need to first update to SP2 before running the latest cumulative update or is it only necessary to run the latest cumulative update?


texts: Improving SELECT queries involving multiple joins

texts:
I have the following query:

SELECT event.* FROM event
LEFT OUTER JOIN invalid ON
    invalid.event_id LIKE event.id
    AND invalid.userid LIKE '<userID>'
LEFT OUTER JOIN owner ON
    owner.id LIKE event.owner_id
LEFT OUTER JOIN location_event ON
    location_event.event_id LIKE event.id
WHERE event.type IN (<listoftypes>)
    AND invalid.id IS NULL
    AND lower(location_event.location_name) LIKE CONCAT('%', '<searchtext>', '%')
ORDER BY event.start_timestamp DESC
LIMIT 0, 10

The WHERE clauses match a large proportion of the total rows in the event table. I'm trying to set an index on event.start_timestamp to improve the query time. However, using EXPLAIN, it does not appear in the possible_keys. EXPLAIN does state that it is Using temporary and Using filesort.

I believe that Using filesort causes the query to not use indexes for the ORDER BY clauses. To avoid it I need to shrink the size of the rows caused by the 3 joins (which are all necessary). Is it possible to reduce the number of columns brought through with a join?

Also, are there any other parts of this query which stick out as being slow/inefficient?


texts: How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement?

texts:
MS SQL Server 2012... with nightly databases backups to the same/local as SQL Server machine...

I am trying to add another SQL Server Agent Job to copy the  .bak files to remote (non-windows, i.e. Linux) share with non-Windows (non-AD) user/password credentials.
I do not have any access to configuring or changing that access which is under control of other, quite remote people.

For this (copying) I created local user with the same user name and password, gave it permissions to the (source or local) backup-folders upon which all perfecly works from command line (Win + К or cmd) if to enter the command manually:

RUNAS /user:UserName /savecred "robocopy d:\SQLBACKUP  \\10.195.xx.yyy\backup /S /purge /MAXAGE:7 /MT:1 /Z"

but fails to run as SQL Server Agent job (type of step is "Operating System(CmdExec)". SQL Service Agent (with standard configuration of running under [NT Service/SQLServiceAgent] account, the job is owned by SA SQL Server superuser).

Can anybody explain me why it is failing and how to correctly make it running (taking into account that I do not have access to domain users configuration)?


texts: Accelerated Database Recovery with AlwaysON -- long recovery time for secondary unexpeccted

texts:
So I was intrigued by the new SQL 2019 Feature Accelerated Database Recovery. It has some great use cases, such as being able to kill a long-running transaction and have it instantly rollback.
I had read that it's supposed to improve database recovery much faster.
I was shocked to find out that in a load test comparison of an ADR-enabled and NON-ADR (default) database, the recovery time for the SECONDARY to begin syncing with the NEW primary was order of magnitude slower.
To date, I have been unable to find much information on ADR with AlwaysOn -- except for a passing mention of it in a SQL 2019 talk he did, in which he claimed it improved the performance of AlwaysOn failover.
While the NEW Primary came up right away (although there was only milliseconds difference between the two databases), it took almost 3 minutes before the ADR-enabled database showed "Synchronized" in SSMS!
I ran several tests (which included serveral very large updates, and even included a large statement with a BEGIN TRAN, but no COMMIT.  All test iterations had similar result:  Recovery/Synchronizing of the SECODARY was order of magnitude long for the ADR-enabled database.
Has anyone else seen this behavior?


texts:
Is there a difference in behavior or performance in choosing FIND_IN_SET over LIKE for a operation that looks like:

SELECT * FROM my_table WHERE column_1 LIKE '%string%';

vs

SELECT * FROM my_table WHERE FIND_IN_SET('string', column_1) > 0;

This is done on Percona Server.


texts: MSSQL - convert list of datetimes in a date range

texts:
I have one system which stores data like:

CREATE TABLE Reservations
    ([UserId] int, [RoomId] int, [TypeId] int, [Date] datetime)
;

Every row in the table means that Room is reserved for User of some Type in given Date

I need to convert data to this system:

CREATE TABLE Reservations
    ([UserId] int, [RoomId] int, [TypeId] int, [StartDate] datetime, [EndDate] datetime)
;

Or to be more precise, I need to just make select statement for selecting data from old system in a new way. StartDate and EndDate must represent continuous date range.

I prepared this sql fiddle. I tried to write select statement but with no success. What am I missing?

;WITH GRP AS
(
    SELECT
    *
    ,ROW_NUMBER() OVER(ORDER BY [Date] ASC)
        -ROW_NUMBER() OVER(PARTITION BY [UserId], [RoomId], [TypeId] ORDER BY [Date] ASC) AS Grp
FROM
    Reservations
)
, MinMax AS
(
    SELECT
        [UserId], [RoomId], [TypeId]
        ,MIN([Date]) AS StartDate
        ,MAX([Date]) AS EndDate
    FROM
        GRP
    GROUP BY
        [UserId], [RoomId], [TypeId]
        ,Grp
)

SELECT
    [UserId], [RoomId], [TypeId]
    ,StartDate
    ,LEAD(StartDate,1,StartDate) OVER(ORDER BY StartDate) AS EndDate
FROM
    MinMax
ORDER BY
    StartDate
    ,EndDate


texts: How to restrict SQL Server database access to application or server running the application

texts:
I have a third-party application that connects to a SQL Server database via a SQL Server Native Client 10 ODBC data source.  The application requires that each user of the application must have read/write/execute access on the database.

My concern is that this allows each user to connect directly to the database (using Microsoft Excel, SSMS or any other database connection tool) and change or view the data in any way they care to without the controls, restrictions or auditing enforced by the application.

I cannot change the requirement for each user to have access to the database (at least not without paying for the application to be re-written somehow!) but I was wondering if there was a way to restrict access so that those users can only connect to the database via the server where the application is hosted, or via that one specific ODBC data source, or when the connection is set up by that specific application?

I suspect that I know the answer as I haven't been able to find anything, but I wanted to throw the question out there before I gave up on this.

Thanks,

Paul.


texts: Should I exclude mysql database from multi master replication?

texts:
Running mysql in a two master replication setup. There are many databases and they are all replicating correctly including the mysql database itself.

Is it safe to replicate mysql or should I exclude it via the my.cnf ?


texts:
I have the following Customers table:

customer_id - int
company_name - nvarchar
street - nvarchar
city - nvarchar
comments - nvarchar

The app will only be used in part of one small country (something like 30 cities). A friend told me I should seperate 'city' into a different table 'Cities' and use only city_id in customers table.

Personally I didn't see much of a benefit from it (except for saving some space on Customers table which seems insignificant to me in this case for the cost of creating another table).

He also mentioned that because I have duplicate columns - city: foo, city: bar, city: foo. (few customers in the same city) this is not considered normalized, is this true?

Who's right? Any enlightenment on the issue?


texts: Importing CSV file into a PostgreSQL table

texts:
I am using Python with pandas to import a CSV file into a table in Postgres

import pandas as pd
import psycopg2
from sqlalchemy import create_engine

df = pd.read_csv('products.csv', sep=';', low_memory=False)
engine = create_engine('postgresql://myuser:mypass@server/postgres')
df.to_sql('new_table', con=engine, if_exists='append', index=False, chunksize=20000)

The .csv file size is ~10GB. I left the script running for 15 hours but it's nowhere near finishing. What better way can I use to push the db to the server?

I can't import the db from the server directly because the compressed file size is larger than the size allowed.


texts:
We recently had a server migration. A new server is now the PROD server, and what used to be the PROD server is now the staging environment.

Apparently, the data was transferred correctly, but the database is missing its PK/FK contraints. Luckily, the database hasn't been touched yet as the connected application is still in migration, which means that if the PK/FK constraints are now enabled, there shouldn't be any issues because no rows were created/altered/deleted.

We stil have the original database (which is now staging). The structure is the same, but the content has already changed numerous times during dev testing.

Is there a way in which I can keep the current PROD database, and add the PK/FK constraints to it (based on the staging database)?

Extra info:

we have no backups readily available unfortunately, else I would've fixed it that way
Using SQL Server 2008 via SSMS
I'm not the one who performed the data migration. It is office procedure to move databases by using .bak files, but I fear someone used the import/export option, hence losing the PK/FK constraints.


texts:
[cross-posted from stack ove
On a Ubuntu 14.04 staging server a MySQL connection protocol had to be installed for full-text searching for a separate application.  This led to installing, uninstalling ad installing anew mysql and sphinxsearch.

After a reboot, another application hits an error because:

ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory

in psql (version 9.3.10) the same error message is getting confirmed upon

SELECT PostGIS_full_version();

notwithstanding that a check on the database returns extension "postgis" already exists.. whereis postgis also returns a blank.

Has postgis been somehow uninstalled?

Attempting to install anew:

sudo apt-get install postgresql-9.3-postgis

getting Package postgresql-9.3-postgis is not available, but is referred to by another package. This may mean that the package is missing, has been obsoleted, or is only available from another source E: Package 'postgresql-9.3-postgis' has no installation candidate

Even attempting to create /etc/apt/sources.list.d/pgdg.listwith

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

then

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.3-postgis

getting the same no installation candidate. Is this server hosed for postgis now?  Would re-installing postgresql impact existing applications on the VPS?


texts: TSQL performance - JOIN on value BETWEEN min and max

texts:
I have two tables in which I store:

an IP range - country lookup table
a list of requests coming from different IPs

The IPs were stored as bigints to improve lookup performance.

This is the table structure:

create table [dbo].[ip2country](
    [begin_ip] [varchar](15) NOT NULL,
    [end_ip] [varchar](15) NOT NULL,
    [begin_num] [bigint] NOT NULL,
    [end_num] [bigint] NOT NULL,
    [IDCountry] [int] NULL,
    constraint [PK_ip2country] PRIMARY KEY CLUSTERED
    (
        [begin_num] ASC,
        [end_num] ASC
    )
)

create table Request(
    Id int identity primary key,
    [Date] datetime,
    IP bigint,
    CategoryId int
)

I want to get the request breakdown per country, so I perform the following query:

select
    ic.IDCountry,
    count(r.Id) as CountryCount
from Request r
left join ip2country ic
  on r.IP between ic.begin_num and ic.end_num
where r.CategoryId = 1
group by ic.IDCountry

I have a lot of records in the tables: about 200,000 in IP2Country and a few millions in Request, so the query takes a while.

Looking at the execution plan, the most expensive part is a Clustered Index Seek on index PK_IP2Country, which is executed many times (the number of rows in Request).

Also, something that I feel a little strange about is the left join ip2country ic on r.IP between ic.begin_num and ic.end_num part (don't know if there's a better way to perform the lookup).

The table structure, some sample data and query are available in SQLFiddle: http://www.sqlfiddle.com/#!3/a463e/3 (unfortunately I don't think I can insert many records to reproduce the problem, but this hopefully gives an idea).

I'm (obviously) not an expert in SQL performance/optimizations, so my question is: Are there any obvious ways in which this structure/query can be improved performance-wise that I am missing?


texts:
I'm OCD person, I think I accidentally dropped something in my mysql.

Problem is I've around 50 databases, and I cannot manually check everything :(

Is there a way to check for these kind of changes? I tried to look at timestamps in /var/lib/mysql but it didn't help, I was looking at directory timestamps.

I also ran:

SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 1 day)

it only returned databases changed in last few secs.


texts: Setting prompt for MariaDB 10.2 in my.cnf?

texts:
I'd like to set the default prompt on MariaDB across all our database servers for everyone. I assume this would be best done in my.cnf. I can set the prompt in a session, but when I try to add something like prompt=name[/d]>/_ in my.cnf, MariaDB fails to start an complains about it.

What is the proper way to do this?


texts: SQL 2005 table data type "text" to nvarchar

texts:
I have this column Remark which is data type text. The longest length is 153.

So is it safe to say that if I change this column from text to nvarchar(200), I won't lose any data?

SELECT LEN(LTRIM(RTRIM( CAST( Remark AS NVARCHAR(MAX) )))) FROM myTable
ORDER BY LEN(LTRIM(RTRIM( CAST( Remark AS NVARCHAR(MAX) )))) DESC


texts: MySQL replication of single DB to same server

texts:
I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source).

I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which in turn locks out other selects (at least I think this is what's happening!)

To alleviate this I would like to replicate one of the databases onto the same server so that the writes can write to the "master" and reads can be done on the "slave". I don't have the luxury of additional hardware.

I have found a lot of guides on the internet describing how to replicate to a different server, and one (http://www.howtoforge.com/one_machine_mysql_replication) describing how to replicate to the same server but it doesn't seem to be geared towards retrofitting this functionality to an existing database with 10s of GB of data in it already. It also talks about compiling MySQL from source.

Does anyone know how I can retrospectively convert my MySQL 5.5 config to replicate one of my databases on the same server?

My existing DB runs on port 3306 so I feel it should be as easy as firing up another instance of MySQL on port 3307 and making some changes to my.cnf but not sure what changes.

Thanks in anticipation.


texts: InnoDB buffer. Should I increase buffer size?

texts:
Database monitor

InnoDB and Key always 100%. How can I increase buffer size ?
can this cause any problems or slow database speed?

My configuration

skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 32M
table_open_cache = 256
sort_buffer_size = 4M
read_buffer_size = M
read_rnd_buffer_size = 10M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_size= 64M
thread_concurrency = 16

#innodb_use_native_aio = 0
innodb_file_per_table


texts: Perl - MySQL/MariaDB - slow with no identifiable bottleneck

texts:
I am running a Perl script (using DBI) which reads from raw files from a hard disk, and updates MySQL database (which is on a separate SSD).  My performance is rather slow (1000 files processed in 30-60 seconds), but I can not find the bottleneck.

CPU. Network, Disk and Memory are all rather unused. I am running Windows 7 64bit on an i7 machine (8 cores). MariaDB is version 10.0.10. My database is around 78G in size with 5M entries, all tables properly indexed.

Perfmon confirms this showing total CPU 6%, Network 0%, HD Disk 140 I/O/s, Memory 10%. None of the CPU cores are used more than 3-4%.

I have experimented with changing all these mysql variables with no success:
    innodb_use_global_flush_log_at_trx_commit
    innodb_buffer_pool_instances
    global.max_connections
    innodb_thread_sleep_delay
    global.innodb_io_capacity
    global.innodb_sync_spin_loops
    innodb_flush_log_at_trx_commit
    wait_timeout

Mysqltuner did not report anything of interest, except for:
Data in InnoDB tables: 78G (Tables: 12)
Total fragmented tables: 10
Query cache is disabled
Thread cache is disabled
InnoDB data size/buffer pool: 78.1G/2.0G

Perl profiling showed that majority of time is taken up by DBI::st::execute (invoking sql).

I have also tried disabling firewall and virus scanner too - no difference.


texts: How can I skip reboot check and remove command line appearing during install of SQL Server?

texts:
While i am installing SQL Server 2008 R2 using the configuration file method I sometimes get an error in installation due to system reboot checking.

After restarting my PC, SQL Server installs properly.  I want SQL Server setup to not check if the PC is rebooted or not. In SQL Server 2005 I can do this by using /norebootchk. How can I do this in SQL Server 2008 R2?

Also while installing in silent mode by using quiet=true I am getting a SQL Server command prompt while installing.

How can I avoid this command prompt appearing when I am installing in quiet mode?