texts: impdp failing with ORA-01031: insufficient privileges

texts:
I'm trying to import a dmp file provided by our dba to my dev database. I've been able to export the contents to a SQLFILE and the contents look ok. I tried importing the schema and it kept on failing (same errors as shown below); so I tried running the SQL directly from the sqlfile - this created all the schema objects but didn't import the data.

The export was from the expenses schema from our test database. I already have another schema by that name in my dev database that I don't want to touch; so I'm remapping expenses to expenses1.

This schema (expenses1) has READ and WRITE privilege on the data_pump_dir directory. It has, as far as I can tell, all the privileges it needs (create table, view, procedure, sequence, etc) as well as IMP_FULL_DATABASE.

[oradev@DIAS45 dpdump]$ impdp expenses1/expenses1 logfile=expenses1.log
  remap_schema=EXPENSES:EXPENSES1 directory=data_pump_dir
  dumpfile=ossarct_expenses_expdp.dmp
  content=DATA_ONLY schemas=EXPENSES include=TABLE

Import: Release 11.2.0.3.0 - Production on Tue Apr 16 09:28:21 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

[oradev@DIAS45 dpdump]$ cat expenses1.log
;;;
Import: Release 11.2.0.3.0 - Production on Tue Apr 16 09:28:21 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "EXPENSES1"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "EXPENSES1"."SYS_IMPORT_SCHEMA_01":  expenses1/******** logfile=expenses1.log
  remap_schema=EXPENSES:EXPENSES1 directory=data_pump_dir
  dumpfile=ossarct_expenses_expdp.dmp content=DATA_ONLY schemas=EXPENSES include=TABLE
ORA-39097: Data Pump job encountered unexpected error -1031
ORA-39065: unexpected master process exception in DISPATCH
ORA-01031: insufficient privileges

I can't tell from the above what privilege is missing, or if my command is telling impdp to do more than I'm expecting it to. I've tried skipping the schemas and include parameters with no difference.


texts: Troubleshooting slow connection establishment

texts:
The client and the server are running on Windows on different subnets.
I'm facing a 20s delay to establish a connection to a SQL 2012 Always On availability group using .NET Core Sql client using sql authentication.
I have no problem establishing a connection to other SQL Server instances (no availability group). The connection establishment is instantaneous.
I do not have the same problem when using the MS sql command line client (sqlcmd which uses ODBC).
I need help solving this problem. I don't know yet if it is network or "AlwaysOn" configuration issue.
I'm able to provide network traces if requested.

EDIT 1: Wireshark traces
106 42.144914   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
107 42.690863   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
108 42.816460   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
109 44.157170   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
110 44.700425   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
111 44.824821   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
112 48.161575   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
113 48.708869   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
114 48.832817   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
115 50.537018   172.23.10.49    10.150.200.213  TCP 55  [TCP Keep-Alive] 56094 → 1433 [ACK] Seq=8294 Ack=3419 Win=2101760 Len=1
116 50.537561   10.150.200.213  172.23.10.49    TCP 66  [TCP Keep-Alive ACK] 1433 → 56094 [ACK] Seq=3419 Ack=8295 Win=64512 Len=0 SLE=8294 SRE=8295
117 50.585230   10.150.200.213  172.23.10.49    TCP 60  [TCP Keep-Alive] 1433 → 56094 [ACK] Seq=3418 Ack=8295 Win=64512 Len=1
118 50.585293   172.23.10.49    10.150.200.213  TCP 66  [TCP Keep-Alive ACK] 56094 → 1433 [ACK] Seq=8295 Ack=3419 Win=2101760 Len=0 SLE=3418 SRE=3419
119 56.161835   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
120 56.724399   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
121 56.833741   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1


texts: Animals hierarchy use-case: multiple farms, multiple species

texts:
Let's talk about an online service where goat/sheep/cattle/llama farmers would register and keep track of their animals.

One of the features of such a service is tracking parent/child relationship. However, it's not as trivial as adding a parent_id field. Here are the reasons why:

There are many farmers, each with their own account; they're selling animals to each other. This means that not every parent can be entered into the system with all its properties. So there is no parent_id primary key to link to.
There are two IDs, national and internal. None of them, one of them or both of them can be entered. There should be a priority in determining the animal's parent. Internal IDs have priorities. Then comes national IDs. This means that if both are specified, the parent should be determined by an internal ID.
Not all animal cards are made public by their owners. This means that we cannot rely national IDs and look for parents through the whole database and fetch parents' data to display the tree.
Furthermore, the National ID is a NOT a unique field among the database (but is unique for each user and species). This is made so to allow farmer1 enter national ID "ABC" even if farmer2 entered national ID "ABC" for another animal as well (e.g. by mistake).

As you can see, this all makes impossible to rely on a single parent_id column. That's where come various approaches to solve the issue, and I ask your advice.

Approach #1

Introduced the following columns:

mom_national_id (nullable)
mom_internal_id (nullable)
dad_national_id (nullable)
dad_internal_id (nullable)

Unique indexes (user_id, species_id, mom/dad_national_internal_id)

Approach #2

mom_id BIGINT UNSIGNED (nullable)
is_mom_id_national BOOL
dad_id BIGINT UNSIGNED (nullable)
is_dad_id_national BIGINT UNSIGNED (nullable)

So, here are my questions

Q1. Is either of the approaches above correct, or can you recommend a better one?

Q2. If both of them correct, which one would be better in terms of querying speeds for building parent/child trees? Does it matter at all if all is needed is just up to ~50 generations up or down?

Q3. Which indexes are needed for best performance?

Despite the solution should not be specific to database type, the service is being built with using PostgreSQL.


texts: Is it possible to use just one shared folder for log shipping?

texts: I wonder if it is possible to use one shared folder(only in source database) instead of two (one in source db and another in destination db). That's Server1 will store its t-log backups there and Server2 will restore those backups from the same folder. Please see images below to understand what I mean:


texts: Best way to log all changes at column-level for every table in a database?

texts:
I have a database consisting of several tables and in a separate table, I would like to store every column within every table and the last time the data in the columns was modified (i.e. following an UPDATE, INSERT or DELETE) statement.
If I only update one column in a table, the record for that column in my log would then update with the last modified timestamp.
Is this possible and is there a suggested approach for it? My initial thought was potentially to use a database-level trigger but wasn't sure if this could be setup to monitor changes to all columns in all tables. I don't want to have to create an individual trigger on each table but wasn't sure how else I could monitor changes at a column level.
Something I am exploring:
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-ver15


texts: ORDER BY doesn't use an index on SQL Server if CASE WHEN is present

texts:
Firstly, I apologize - I couldn't create an example SQL Fiddle, because the site doesn't seem to be working right now.

Now, onto the problem... I'm getting an inconsistent sort order on SQL Server 2014 when using OFFSET..FETCH on a query sorted over a non-unique NVARCHAR field.

The ORDER BY looks like this:

ORDER BY CASE WHEN [Field] IS NULL THEN 1 ELSE 0 END ASC, [Field] DESC OFFSET 100 ROWS FETCH NEXT 3 ROWS ONLY

Every execution of the statement yields a slightly different combination of the 3 rows. Technically, all results are correctly sorted, but the returned row IDs are not always the same.

The field in question has an index on it, but I've determined that the execution plan doesn't seem to be using it.

Testing showed that the culprit is the CASE WHEN expression. The reason I'm using it is to ensure that NULL values are reliably sorted before (when ASC) or after (when DESC) any other value across all the different DBs that my app works with (aside from SQL Server there's also Oracle, DB2, PostgreSQL, Firebird, and SQLite).

I'm considering removing this rule when generating statements for SQL Server, since it obviously forgoes the index and therefore doesn't yield reliable results. But before I do, I want to ask: is there any other practical solution to keep the sort order stable? Optimally, the solution should just use the index which is there for that very purpose.


texts:
I want to do the following comparison :

IF ( V_C_CRITERE2.DATE_FIN_SOUHAITEE - TO_DATE(V_SEUIL_ALERT) < CURRENT_DATE) THEN

DATE_FIN_SOUHAITEE is a DATE.

But a get this error :

operator does not exist: interval < date

How can i do the comparison?


texts:
I search for a simple way to select all columns except one in psql.

With psql I mean the interactive command line.

I would be happy with a tool that expands to * to a list of quoted column names. Then I could remove the column to remove by hand.

My question is just about the interactive usage of psql. It is not a duplicate of questions of people unhappy with the sql standard and who want to execute something like "select *-foo".


texts: Mapping logins to linked servers for migration

texts:
My team is migrating a SQL 2012 server with an extensive list of linked servers to SQL 2016. My task is to script the creation of linked servers on the new server.
It looks like I have all the information I need to create the script in sys.servers but am having trouble finding where local logins are mapped to the linked servers.


texts: Can we create indexes on views in SQL Server?

texts: Can we create Indexes on a view in SQL Server 2008? I have heard about indexed view, which can give performance improvement, but the problem is data refresh on those views: Can it be auto updated, once data in base tables changes?


texts: While running Mongodb, getting “enabling http interface” message and stopped

texts:
I am getting the following error when I run a freshly installed MongoDB:

$ mongod --directoryperdb --dbpath C:/mongodb/data/db --logpath c:/mongodb/log/mongodb.log --logappend --rest
2015-03-17T17:32:55.359+0530 ** WARNING: --rest is specified without --httpinterface
2015-03-17T17:32:55.360+0530 ** enabling http interface

Can you help me to fix it?

I am using windows 7 machine


texts: SQL Server : parsing with XPath Performance

texts:
I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I generate an XML string that I pass to my stored procedure.

From there, for locations for example I create a temp table and dump all of the locations that were selected by the user into that table which I then join my data on later.

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by.

DECLARE @tmLocations TABLE (
    location VARCHAR (100));
BEGIN
    INSERT INTO @tmLocations
    SELECT ParamValues.x1.value('location[1]', 'VARCHAR(200)')
    FROM   @xml.nodes('data/teammateLocations/locations') AS ParamValues(x1);
END

Is there another method of extracting data from XML or ways to improve the query like above so that it doesn't cost as much to run? Setting up the data is costing more than the actual filtering of the data which is really slowing down performance.


texts: SQL Server 2012 Resource Governor and memory used by CLR

texts:
In SQL 2012 memory used by CLR is now part of what is controlled by "MAX Server Memory"

a) Does Resource Governor in SQL 2012 Enterprise (MAX_MEMORY_PERCENT for resource pool) governs CLR memory?

b) Also, Max_Memory_Percent is the percentage of "Max Server Memory"? So my max server memory is 28 GB on a server with 32 GB RAM. Does that mean setting Max_MEMORY_PERCENT to 10 percent mean I set 2.8 GB for that particular resource pool.


texts: MySQL Stored Procedure - no of times executed

texts:
My question is simple, is there any way to calculate, number of times a stored procedure is called on applications or executed on MySQL server.

For example:

Procedure name: sptest

Executing procedure: call sptest(1);

Is there any way to identify number of time the statement CALL sptest(1) executed on server.

Thanks in advance.


texts:
I am trying to create a report from a cloud based EHR so I cannot share real data and some of these tables are fairly massive. I will try to minimize and share the bare minimum and expand if someone needs more information to help. This should be fairly easy and I'm just having a brain fart I think. I need to combine multiple answers into a single row as separate columns.
Here is my query as it is and it does return all the answers but every answer is generating a separate row. There will only ever be one answer for each question per visit id.
There are a few catches to working with this system. At it's heart it's SQLServer, however queries are restricted to starting with 'select' making temp tables a bit more difficult. There can be no spaces, no blank lines nothing before your select. This is their version of security I guess. All reports are written through a web interface no direct access to the db in any way.
Current Output:

clientvisit_id  |  client_id  |  members_present  |  patient_category

141001          |  2001       |                   |
141001          |  2001       |                   |
141001          |  2001       |  Patient          |
141001          |  2001       |                   |  Adult

Desired output:

clientvisit_id  |  client_id  |  members_present  |  patient_category

141001          |  2001       |  Patient          |  Adult

Select
  cv.clientvisit_id,
  cv.client_id,
  mp.answer as members_present,
  pc.answer as patient_category

From ClientVisit cv
  Inner Join SavedVisitAnswer sva On sva.clientvisit_id = cv.clientvisit_id
  Inner Join Question q On sva.question_id = q.question_id
  Inner Join Category cat On q.category_id = cat.category_id
  Inner Join FormVersion fv On cat.form_ver_id = fv.form_ver_id
  Inner Join Forms On fv.form_id = Forms.form_id
  Inner Join (Select
     a1.answer_id,
     a1.answer
     From Answer a1
     Where a1.question_id = '532096'
  ) as pc on sva.answer_id = pc.answer_id
  Inner Join  (Select
     a2.answer,
     a2.answer_id
     From Answer a2
     Where a2.question_id =  '532093'
  ) as mp on sva.answer_id = mp.answer_id

Where
  Forms.form_id = '246'


texts: What is a storage need for MySQL index on Varchar(100) in InnoDB?

texts: I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example average of emails length is 20 characters and after hash it will be 64 characters), will be there a difference in index storage as well?


texts:
I am new to SSRS and I have been tasked to copy the SSRS reports(from PROD) to another Server(QA).

I found many  documentations that show how to migrate the reports by Restoring ReportServer, ReportServerTempDB and encryption key.

I just want to confirm. After doing above, my main environment(prod) will remain intact and operational?Please confirm. I'm creating a QA environment that I am copying from PROD and I cannot afford for anything to happen to PROD.


texts: Modelling Orders and Messages for an e-Commerce database

texts:
In my e-commerce application I would like that buyers and sellers interact via a messaging system to purchase products.

The process consists of:

The buyer inquiring for a product
The seller responds with a quote
The buyer can negotiate
The seller can create / modify the quote based on negotiations
The buyer accepts quote which is then transformed into an order
The seller sends an invoice to the buyer

It's also possible that a buyer immediately starts an order when he accepts the price listed for the product.

I'm a bit clueless as how to model this in a database. Do I create a table for Enquiries, for Quotes and for Orders? Can I use a status table to maintain the type of object? But with a table I then need to keep versions of it for every change that occurs I think? And then how do I link it to a message?

It may seem a lot of questions, but I simply don't know how to tackle this.

The following diagram is a part of my database scheme so that you have an idea of how my it is designed for the moment:

Update - Add image referred to in comment


texts:
I'm trying to show results for 12 months using this query:

SELECT
    COUNT(DISTINCT user_id) as customers,
    SUM(amount)*.01 as amount,
    SUM(amount_refunded)*.01 as refunded,
    MONTH(MAX(created_at)) as month
FROM `payments`
WHERE `amount` > 0
AND `status` = 'succeeded'
AND `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH
AND `user_id` IN (SELECT `id` FROM `users` WHERE `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH);

Could easily do this programmatically using PHP to output into an HTML table where each row includes shows a different month by incrementing the month and querying the DB again each time.

How would I do that with this query using a MySQL FOR loop instead? Would that even be the right approach?


texts: The query result changes in remote and local database

texts:
The below table is created in local database and remote databases.

CREATE TABLE EMPLOYEE1 ( EMP_ID INTEGER, EMP_NAME VARCHAR(10), EMP_DEPT VARCHAR(10) );

Insert the below rows in tables created in both the databases.

INSERT INTO EMPLOYEE1 (EMP_ID, EMP_NAME,EMP_DEPT)
VALUES (1,'A','IT'), (2,'B','IT'), (3,'C','SALES'), (4,'D','SALES'), (5,'E','ACCOUNTS'), (6,'F','ACCOUNTS'), (7,'G','HR'), (8,'H','HR');

COMMIT;

If i run the below query in local database of my system then the query result is correct.i.e it is returning all the rows in the table as the query exactly has to do. But the same query if i run in remote database then only 4 rows are returned,which is a wrong result.

SELECT * FROM EMPLOYEE1 WHERE (EMP_DEPT NOT IN ('IT','SALES') OR EMP_DEPT IN ('IT','SALES'));

**Can anyone suggest why the query behavior changes?**I am using DB2 9.7 Express C


texts:
There was lot of discussion in this question: What database technologies do big search engines use?

So much discussion that it made me confused. So... what is a database, anyway? Are only relational databases "databases"? Are object-oriented databases "databases"? Is any system that allows me to store and retrieve information (like a map, list, etc) a database?

Or does a database have to store/retrieve information and also have some administration features like Users and Privileges? Was dBase III plus a database, since it wasn't really relational?


texts: How to restore SQL Server database to a specific day?

texts: Suppose I have an SQL Server 2008 which is installed on windows 2008. There is a db MyDb under the instance. MyDb is have full backup every night. Because of storage space on hard driver, only 7 days backup kept. the log on SQL Server is never truncated. Now the log file even is larger than 100GB. Then I am requested to restore the db to a day one month ago. how can I do that? Also can I shrink or truncate log file much smaller?


texts:
I previously asked a question about how to organize a long search query.  I have taken it apart.  I am presently attempting to understand the function of some of its parts.  Specifically, I am attempting to determine how an exists statement works. Does it return a single row or multiple rows?

exists(select ID
       From Address
       Join PersonAddress on PersonAddress.Address = Address.ID
       Where
       PersonAddress.Person = Person.ID
       and ((Description LIKE keyword)
         or(Street LIKE KEYWORD)
         or(City LIKE KEYWORD)
         or(State like KEYWORD)
        ))


texts:
I am trying to create a linked server connection from sql-server to Hana DB. I have downloaded the Hana client and all the drivers along with it.
I have tried creating a system DSN and then adding the Hana database using the sp_addlinkedserver and sp_addlinkedsrvlogin procedures. This creates the linked server but when I expand into the server it show no catalogs (basically shows nothing).
Another way I tried was by hitting new linked server and using the SAP Hana MDX Provider and this shows me a default database. However when I expand the tables or views it gives me an error Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider SAPNEWDBMDXProvider for linked server "NAME".
Any help will be greatly appreciated. Please let me know if you need any more information.
[![enter image description here][1]][1]
Error image:
[1]: https://i.stack.imgur.com/jB9i4.jpg
version: Sql-server-17


texts: During rollback, is lock escalation reversed

texts:
Generally, SQL Server will take locks during updates. It also supports Lock Escalation:

  Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.

During a transaction ROLLBACK, does SQL Server de-escalate the locks, putting back the fine-grained locks?


texts: Rows are not getting deleted from MySQL Table

texts:
We have one innodb table and one Federated table in our environment and while deleting rows from Federated table it shows 24-rows affected with no warning, but rows are not deleted.
There is no trigger or hidden logic involved in background.
We are using it on Windows and MySQL version is 5.6.19.

Can anyone help?

Regards,
Nawaz


texts: Backup of MongoDB collection data and rename the collections

texts:
I want to take a collections data backup on a different host, with renaming the Collections.

I am very new to MongoDB. Please help me with this task.


texts: How do I move all tables from one Postgres schema to another

texts:
I want to move all tables from schema xyz to the default schema public.

I can move individual tables with

ALTER TABLE table_name
    SET SCHEMA public;

And I can get all tables with

SELECT table_name FROM information_schema.tables WHERE table_schema='xyz'

Is there any way to combine these two together?


texts: Matching dates with conditions and filtering in a table


texts:
So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for.

E.g.:

PostgreSQL Replication and Hot Standby – In Practice
http://blog.3dtin.com/2012/07/26/postgresql-replication-and-hot-standby-in-practic/

Is a great tutorial for how to set that up, BUT failover is manual.

Replication and Load Balancing with PostgreSQL and Pgpool2
http://www.keyup.eu/en/blog/89-replication-and-load-balancing-with-postgresql-and-pgpool2

Seems to be perfect only if the pgpool server was not the single point of failure.

So, my question is, if I add a HAProxy cluster, just like any pool of web servers distribute load, would I be able to have the automatic failover to the pgpool and therefore to the postgresql servers??

                    Application Servers
                             |
                             |
          ip1 -------- (floating) ip3 -------- ip2
        HAPROXY   ------- hearbeat -------- HAPROXY
        pgpool II                           pgpool II
         |    \                             /     |
         |     \                           /      |
         |      \                         /       |
        ip4<-----\-----------(to same master ip4) |
    postgres      ip5<------------------(to same slave ip5)
     Master      postgres
                 slave


texts:
I have 5 databases, all of which have the same structure. One is local (MySQL version 5.6.17), the other four are on the same server (MySQL 5.6.11-log). Let's call them A, B, C and C1. C had some of its data imported from B, and C1 is a test database whose structure and data was imported from C.

I have the following query:

SELECT SQL_CALC_FOUND_ROWS
    customergroups.customergroupID AS 'Id',
    customergroups.customergroup AS 'Name'
FROM
    customergroups
ORDER BY customergroups.customergroupID ASC
LIMIT 10;
SELECT FOUND_ROWS();

It works correctly on local, A and B returning expected values (261, 278 and 36 respectively) however it returns 10 for C and C1 (instead of 14 and 16 respectively). Removing ORDER BY customergroups.customergroupID ASC fixes it, same as leaving only SELECT SQL_CALC_FOUND_ROWS customergroups.customergroupID AS 'Id' FROM customergroups

Oddly enough, a similar query on different tables on all databases work correctly:

SELECT SQL_CALC_FOUND_ROWS
    customers.customerID AS 'Id',
    customers.customer AS 'Name'
FROM
    customergroups
ORDER BY customers.customerID ASC
LIMIT 10;
SELECT FOUND_ROWS();

I am aware there is/was a relevant bug in MySQL (https://bugs.mysql.com/bug.php?id=68458) but I would prefer to avoid upgrading MySQL, especially as the query works correctly on other databases on the same server running the same MySQL version and it would take likely several hours if not longer to back everything up and then restore it due to a ton of data in some tables.

I have already compared the structure of the tables and they are identical (data is different though). Any idea what could be causing this weird behaviour and how to fix it? Adding GROUP BY to the statement didn't help, neither did optimize table customergroups.


texts: Imposssible to expand a database in SQL Server Management Studio

texts:
I am experiecing a strange problem. I have a SQL Server 20008 R2 instance that recently (after years of running smoothly) started to become slow.

One symptom that I notice is that when working from SQL Server Management Studio, on the server itself and logged as sa, I click on the + symbol near the database icon, after clicking I get an error and after OK I see that the  "+" is disappeared, you can see in this screenshot how it looks like after I tried on four databases:

The error I get is (in Italian):

Impossibile accedere al database MyDatabase. (ObjectExplorer)
------------------------------
Percorso programma:

   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.DatabaseNavigableItem.get_CanGetChildren()
   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request)
   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

It means "Impossible to access the database MyDatabase".

This is just a symptom as I said, the main issue is that from a client the connection is slow, very slow but working.

I restarted the SQL Service service for this specific instance.

This is a named instance, I have also the default instance that works fine.

Can anyone give me a pointer to a solution please?


texts:
This isn't exactly related to modeling the database but rather about "standardizing" column placement.

Where should I generally put my FK column? As the last column of the table? At the start just after the primary identifier? After a relevant set of columns in the middle of the table?

Are there any industry standards I can hold on to, to make my life easier?

If it means anything, I'm using mysql, but this is a general db design question.


texts:
I need to find out the statements that are unsuccessfully executed in sql, along with their error trace. As of now I created a trigger which is triggered on server error, which stores the sql text and the SQLERRM in a table.

Is that the right way to do? Is there a more efficient way? I searched about AWR and Auditing. But they dont seem to serve this purpose.

I use Oracle SQL by the way.


texts: Why do these queries deadlock and how to work this around in MariaDB?

texts:
The following 2 queries enounter DB deadlock in my program:

DELETE FROM proxies
WHERE NOT EXISTS (
  SELECT 1 FROM proxy_xref_proxy_list AS pxpl
  WHERE pxpl.ProxyID=proxies.ID
    AND (pxpl.DeactivationTS IS NULL OR pxpl.DeactivationTS >= (NOW() - INTERVAL 24 HOUR))
  ORDER BY pxpl.ProxyListID
  LIMIT 1
)
ORDER BY proxies.ID

INSERT IGNORE INTO proxy_xref_proxy_list (ProxyID, ProxyListID)
(SELECT ID, 13 FROM proxies
JOIN new_proxies ON proxies.Url=new_proxies.Url
ORDER BY proxies.ID)
ON DUPLICATE KEY UPDATE DeactivationTS=NULL

However, they are both ordered by proxies.ID. Why do they deadlock and how to solve this?

Tables:

CREATE TABLE `proxies` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `Url` VARCHAR(767) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `Schema` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `Host` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `Port` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `SuccessCount` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `UnknownCount` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `FailureCount` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`) USING BTREE,
    UNIQUE INDEX `UrlUnique` (`Url`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `proxy_xref_proxy_list` (
    `ProxyID` BIGINT(20) NOT NULL,
    `ProxyListID` BIGINT(20) NOT NULL,
    `DeactivationTS` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ProxyID`, `ProxyListID`) USING BTREE,
    UNIQUE INDEX `DisactivationTS_ProxyID_ProxyListID` (`DeactivationTS`, `ProxyID`, `ProxyListID`) USING BTREE,
    INDEX `ProxyXRefProxyList_ProxyListID_FK2` (`ProxyListID`, `DeactivationTS`) USING BTREE,
    CONSTRAINT `ProxyXRefProxyList_ProxyID_FK1` FOREIGN KEY (`ProxyID`) REFERENCES `mydb`.`proxies` (`ID`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `ProxyXRefProxyList_ProxyListID_FK2` FOREIGN KEY (`ProxyListID`) REFERENCES `mydb`.`proxy_lists` (`ID`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TEMPORARY TABLE `new_proxies` (
    `Url` VARCHAR(767) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `Schema` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `Host` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `Port` TINYTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    UNIQUE INDEX `UrlUnique` (`Url`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

Table proxy_lists referenced by the foreign key is linked to proxies via a many-to-many relation.

UPDATE1: Upon the request of @Rick James, here is a query that selects the proxy IDs that need deletion:

SELECT p.ID FROM proxies AS p
LEFT JOIN proxy_xref_proxy_list AS pxpl
  ON p.ID=pxpl.ProxyID
    AND (pxpl.DeactivationTS IS NULL OR pxpl.DeactivationTS >= (NOW() - INTERVAL 24 HOUR))
WHERE pxpl.ProxyID IS NULL


texts: how to upgrade the config servers to use WiredTiger in mongodb 3.0.0

texts:
I am reading the document upgrade config servers to use WiredTiger. In the section of "change config server to use WiredTiger", the second step is "stop the last config server listed in the mongos' configDB setting". But the 5th step is to restart the second config server with WiredTiger. I am confused by the sequence of stopping and restarting. Is there problem on this document?

Another question, why the data in config servers should be exported and uploaded manually? Why the data in replicate set should not be exported and uploaded manually? Instead, the data in replicate set can be imported by "Resync a Member of a Replica Set" please see official doc


texts: mongodb: replica-set processing reads on the primary

texts:
Running a replica-set with 1 primary and 2 secondaries, I wonder why the READs are also done on the primary. Shouldn't the primary mainly do the WRITEs and the secondary can share the READ-load?!

I am using the PHP driver for the mongodb and sending the details for replica set. The PHP is running from the secondaries and in the script the intitial connection goes to the secondary itself which is localhost there.

The php documentation says, that the driver will find the primary and secondaries on its own as soon as it connects to any member of the replica-set, no matter if primary or secondary.

After checking the load and logs, I found out that currently my poor primary has to deal with all the READs and the slaves are bored.


texts: Should pivot tables have more columns (many to many relationship)

texts:
I'm currently thinking about how to organize my tables, and I have some information that I'm not sure were should be, one of them is the quantity of a food in a refrigerator

Is it right if I add quantity in refrigerator_has_food table? Or should this table only have foreign keys?


texts:
My Ubuntu 16.04 server was recently hacked and my host allowed me to recover the file system via FTP, closing all other ports thus disabling me from logging in and stopping any services. I have the files on a local windows filesystem, which were copied while the server and services were still running.
I have a new Ubuntu 18.04 server, where I have installed MySQL but not WordPress (which is powered by MySQL); most of all, I want my WordPress blog back up on my new server, then I can write a post about the importance of CORRECTLY backing up a database. Can anyone tell me if this is possible and what I should do next?


texts: Query table A for any rows that are like any search term in table B

texts:
I need some help writing a select statement (I still don't think in sets!). Here's the scenario:

I have table A with column "Name varchar(20)". I have table B with column "SearchTerm varchar(20)". I want to query A for any rows that are like any search term in B.

Let me give specific data:

Table A has:

Jay
Jim
Frank
George

If table B has 'a', then the results would be:

Jay
Frank

If table B has 'j', then the results would be:

Jay
Jim

If table B has 'y' and 'g' (two rows), then the results would be:

Jay
George

If table B has 'Jay', then the results would be:

Jay

I actually want to be able to apply this idea to be able to search two columns, FirstName and LastName, but I'm guessing that once I see the idea, I'll be able to generalize the solution to search multiple columns.


texts:
I'm new to MySQL. I'm having trouble in joining two tables:

SELECT e.resourceName
 FROM resources_in_room r
 JOIN resource e ON r.resourceId=e.resourceId WHERE r.roomId=2;

Table resources in room:

CREATE TABLE  ``resources_in_room` (
  `id` bigint(20) NOT NULL auto_increment,
  `Quantity` int(11) default NULL,
  `resourseId` int(11) default NULL,
  `roomId` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `resourseId` (`resourseId`,`roomId`),
  KEY `FK36360B9B821B4697` (`roomId`),
  KEY `FK36360B9BC0C935AD` (`resourseId`),
  CONSTRAINT `FK36360B9BC0C935AD`
    FOREIGN KEY (`resourseId`) REFERENCES `resource` (`ResourceId`),
  CONSTRAINT `FK36360B9B821B4697`
    FOREIGN KEY (`roomId`) REFERENCES `room` (`RoomId`)
)

Table resource:

CREATE TABLE  `resource` (
  `ResourceId` int(11) NOT NULL auto_increment,
  `Description` varchar(255) default NULL,
  `Quantity` int(11) default NULL,
  `ResourceName` varchar(255) default NULL,
  PRIMARY KEY  (`ResourceId`)
)


texts: How to resolve "InnoDB data dictionary has tablespace id N"

texts:
I recently messsed up a migration to table-per-file for my dbs.
In the end I (thought) I dropped all dbs, deleted any left over files, and then started over from scratch, importing only dbs I needed as I went.

However, today I find loads of these in my log:

InnoDB: Error: table 'db/table'
InnoDB: in InnoDB data dictionary has tablespace id 384,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

Going to the URL, I find near the end.

 If this occurs, try the following procedure to resolve the problem:

    Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.

    Issue DROP TABLE for the original table. That should successfully drop the table and InnoDB should print a warning to the error log that the .ibd file was missing.

What does "Create a matching .frm file" mean? Since it directly speaks of creating a "file", I would assume this is something like touch [file], but then it doesn't make any sense that I should copy it from one directory to another.

I tried it anyway and mysql complained about the table not existing, so that's not it.


texts: Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?

texts:
I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan.

Populating the table:

CREATE TABLE dbo.Test
(
    RowKey INT NOT NULL PRIMARY KEY,
    SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
    ForeignKey INT NOT NULL
)

INSERT dbo.Test
(
    RowKey,
    ForeignKey
)
SELECT TOP 1000000
    ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
    ABS(CHECKSUM(NEWID()) % 10)
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2

CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey)

Populate a table variable with a single record and attempt to lookup the primary key and the second column by searching on the foreign key column:

DECLARE @Keys TABLE (RowKey INT NOT NULL)

INSERT @Keys (RowKey) VALUES (10)

SELECT
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t
INNER JOIN
    @Keys k
ON
    t.ForeignKey = k.RowKey

Below is the execution plan:

Now the same query using a temp table instead:

CREATE TABLE #Keys (RowKey INT NOT NULL)

INSERT #Keys (RowKey) VALUES (10)

SELECT
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t
INNER JOIN
    #Keys k
ON
    t.ForeignKey = k.RowKey

This query plan uses a seek and bookmark lookup:

Why the optimizer is willing do the bookmark lookup with the temp table, but not the table variable?

The table variable is used in this example to represent data coming through a user-defined table type in a stored procedure.

I realize the index seek might not be appropriate if the foreign key value occurred hundreds of thousands of times. In that case, a scan would probably be a better choice. For the scenario I created, there was no row with a value of 10. I still think the behavior is interesting and would like to know if there is a reason for it.

SQL Fiddle

Adding OPTION (RECOMPILE) did not change the behavior. The UDDT has a primary key.

@@VERSION is SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hypervisor)


texts: How do you export a database schema from SQL Server Management Studio to Excel?

texts: As part of a web migration process, a vendor has asked for our database schema without the data records (for confidential purposes). They want a MS Excel file with each database table. I am completely new to working with databases. Is there an easy way to do this in SQL Server 2014 Management Studio?


texts: How to use a Snapshot for Column Changes with Transactional Replication

texts:
I have a database setup to do Transactional Replication from an On-Premise Database running SQL Server 2014 to an Azure SQL Database.  The replication is set to run continuously and also set to replicate Schema changes.  It is a Push Replication.  The replication works correctly.

The issue I have is that I now need to add a new column to the source table which I want to be replicated to the Azure database.  I tried this in my development environment and it took approximately 3 hours to replicate the change because the table has over 1 million records.  However, it would be ok for me to generate a new snapshot and deliver the data that way to initially get the column changes to Azure.  The column changes infrequently so it will only cause a load on the initial sync.  I've done this in the past and a snapshot will replicate in about 20 minutes.

I think generating a new snapshot to get the data up there will be the fastest approach to replicate the data, but I'm not sure what steps to take to ensure that my changes to the source table are not replicated as soon as they are applied.  Below are the steps that I'd like to take, but I'm just not sure how to accomplish this so that I don't have to wait for the transactions to be replicated for my new snapshot to be applied.

Add my new column to my source table
Let Replication push my schema change to the destination table
Disable transactional replication
Apply updates to the source table column
Re-initialize the subscription with a new snapshot
Enable transactional replication

I'm not 100% sure if I truly need to disable transactional replication.  Ultimately, I just want to ensure that I don't have to wait 3 hours to replicate the change because it maxes out the Azure SQL Database DTUs and makes it unusable.  If I can go the snapshot approach then the 20 minute downtime is acceptable.

I've found the following articles which explain how to reinitialize subscriptions so I think I understand that.  However, when I apply my changes prior to re-initializing my subscription, won't I be stuck waiting the 3 hours for those changes to replicate before the subscription would try to reinitialize?  Or does the re-initialize stop any replication in progress at the subscriber?

Reinitialize Subscription - MS SQL Tips
Reinitialize Subscription - Microsoft


texts: Cleaning up cut and pasted outlook email addresses

texts:
I often have to create or update e-mail distribution lists in SSMS, where the requestor will ask "please add Anne Hathaway, Albert Einstein, and Ash Ketchum to the DL.  Get their email from Outlook."

Once I put their names in outlook and push the "Check Names" function, it validates they're still in the system.  Copying this list then produces the following output.

Anne Hathaway < Anne.Hathaway@Email.com >; Albert Einstein < Albert.Einstein@email.com >; Ash Ketchum < Ash.Ketchum@mail.com >

It's a manual process to remove the original name and < > signs, which of course could open itself to error.

How can I set a variable that, when I paste in the raw data straight from outlook ,it cleans it up so that it gets rid of everything left of the < up to but not including the next encountered ; ?

I'd like the variable to print like so.
'Anne.Hathaway@email.com; Albert.Einstein@email.com; Ash.Ketchum@mail.com'


texts: SQL Database Design: Balance between table scans and row duplication (Address example)

texts:
This question is easily illustrated with an example. Suppose I have an Address table, with the columns: address_id, address_line_1,...,address_line_4, locality, region, postcode, country.

This table will be referenced by:

Users (home, default billing, default shipping etc.)
Venues
Orders (billing, shipping)
Bookings

Any Address referenced more than once will not be updatable/deletable.  For updates in this case, a new Address will be created and linked to as necessary.

I can think of three reasonable ways of storing information in the Address table:

Addresses are unique. Every time a new Address is created (e.g., by a User), the entire table must be scanned to see if the Address already exists.  I imagine this solution having an index supporting address_id, and an index supporting the actual Address columns (address_line_1,...,country).
Addresses are unique at the level of the Users or Venues.  If a new Address is added for a User or Venue, only the Addresses for that User or Venue need to be checked before adding another. Orders/Bookings reference these.
Addresses are not shared at all (i.e., any row in the Address table has precisely one Owner; a User or an Order, etc.). Every time there is a new Order, the appropriate User Addresses are duplicated in the Address table and referenced by the Order.

I don't really like the third solution, but choosing between the other two is harder.

Questions

Is it practical, at scale, to scan an entire table to see if an Address exists before adding it (Option 1)?
Is an index supporting a combination of eight columns reasonable (Option 1)?
Or is there value in striking a balance between row uniqueness and the amount of data to be scanned (as in Option 2)?
Is there a point at which one option becomes better than the other?

I would appreciate any advice.


texts: Paging via Number of Result Record vs COUNT(*)

texts:
I have been working on fixing a piece of code for a customer that has been very slow for them.

I have made that fix, and thats all lovely, but I noticed something odd in the paging.

When the query is executed, it does NOT contain a LIMIT keyword. Then, based on the per-page value and the current page the viewer is on, the result reader is told to fetch from the index of the Record at (perPage * currentPage - 1) to the Record at (perPage * currentPage) - 1, and the total number of Records the result has are used to determine the total number of pages.

So, are there any benefits doing it this way as opposed to using the LIMIT keyword, and a separate COUNT(*) query (possibly only done once per search context)?


texts:
So I have a table bugs

|bugid| title|

the table is lacking a date column. But when creating a new entry, most likely always a new entry gets created in bugnotes as well saying "bug created" with the date.

bugnotes:

| bugnotesid | bugid | datetime | text |

I want to list all bugs, showing the date of the oldest entry in bugnotes if available


texts: What would be a good name for a job schedule?

texts:
I have encountered many different names for job schedules in SQL Server, ranging from the job name where it is used to a short recap of the schedule itself. I would like to give a clear, descriptive and consistent name to new job schedules, so it could be re-used easily and it's easy to see what kind of schedule it is.

Examples of encountered job schedule names:

Run every night
Every 4 weeks on Monday
Schedule job X
21.00hr
DAILY (which then runs only 6 days a week)

I find it hard to think of a good way to name them. So: what would be smart, consistent names to give to a job schedule? How do you name them? Why?


texts:
PostgreSQL has a type called char that's stored as an signed 1 byte int

  The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.

Can we create composite types with "char"? I can create a table with it...

CREATE TABLE pixel ( r "char", g "char", b "char" );

Internally that creates a type which I can use elsewhere,

CREATE TABLE f ( mypixel pixel );

But, can I create a simple TYPE (no table) from it?

CREATE TYPE pixel ( r "char", g "char", b "char" );
ERROR:  syntax error at or near ""char""
LINE 1: CREATE TYPE pixel ( r "char", g "char", b "char" );


texts:
I have some doubts about SET DATEFORMAT command.

1)Does this command is only used for inserting and updating date format and it does not effect the date format of that column? OR does this effect my table after inserting values too?

2) I tried using below command(for date datatype) and got error like This session's YDM date format is not supported when converting from this character string format to date, time, datetime2 or datetimeoffset. Change the session's date format or provide a style to the explicit conversion.
. why the error comes for date datatype(where as for smalldatetime it works as shown in link)?

CREATE TABLE #tempTable (DateFormatSample DATE)
SET DATEFORMAT MDY
INSERT INTO #tempTable
VALUES ('09/28/2007')
SET DATEFORMAT YDM
INSERT INTO #tempTable
VALUES ('2007/28/09')
SET DATEFORMAT YMD
INSERT INTO #tempTable
VALUES ('2007/08/28')
SELECT DateFormatSample
FROM #tempTable
DROP TABLE #tempTable

3)It was mentioned like "The setting of SET DATEFORMAT is set at execute or run time and not at parse time." What does it mean?


texts: Is it possible to pass MySQL variable to shell command within `\!`

texts:
Suppose that I have a MySQL variable that I want to be printed out to the screen with some accompanying text. I could do that like this:

set @some_var := 1024;
select @some_var "My accompanying text";

But is it possible to use that variable within system or \! command somehow, for instance:

set @some_var := 1024;
\! echo "My accompanying text:" @some_var


texts: TimeZone problem with Oracle SQL Developer for Windows on MySQL server

texts:
I am trying to connect to a MySQL database using Oracle SQL Developer, but I get this error: Status: fail, test failed:

  Estado: Fallo:Fallo de la prueba: The server time zone value 'PDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

According to some solutions I should use a connection string such as the following, to override the server TimeZone definition.

jdbc:mysql://mysql.server:3306/db?serverTimezone=UTC

But I cannot find where to define a connection string on Oracle SQL Developer. I only get this screen when configuring (or changing configuration):

(In my test it says localhost but I am doing an SSH tunnel to a remote server.)

There is no way to add extra parameters such as serverTimezone from the configuration box.

Some other solutions propose to create a map of timezones (v.g. mapping PDT to Americas/Los_Angeles).

I cannot find a Java control. It is supposed to be integrated within Oracle SQL Developer. I just cannot find any place I can add any extra configuration. I don't have administrative privileges in the server. I might change to some other MySQL client (I can actually work fine with PHPMyAdmin), but I also need to test on Oracle Servers.


texts:
I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master.

I am trying to alter a table and want to change a column value and also on some tables I want to add a new column. Although this operation is non blocking on MYSQL 5.6 but when it gets replicated to the slaves it causes slaves to lag for entire duration of it, I cannot afford slave lag as we are 24/7 operations.

I tried doing SET sql_log_bin = 0; to skip the query to the slave but RDS do not allow this, otherwise I could have done slave (one each) --> master approach and eventually changing on master.

Anyone has any advise how to go on about this in RDS MYSQL 5.6


texts: Why does SQL Server not perform constant (UNION ALL) branch elimination with OPTION(RECOMPILE) when selecting the result into a scalar variable?

texts:
We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). This normally works well when using option(recompile), as the query planner will eliminate the non-reachable union all paths before selection of a query plan.
However, this constant-folding optimization appears defeated when selecting the result into a scalar variable. Selecting the result into a temporary table variable does not de-optimize the recompilation.
Here is a reproduction case in SQL Server 2017:
-- A table, don't need any data.
create table [test].test_table (col1 int, primary key (col1));

-- A simple 'aggregate' view. Using the same table here is irrelevant and,
-- while the view shows the scenario, it might not be required to reproduce the issue.
create view [test].test_view as
select col1, descrim = 1 from [test].test_table
union all
select col1, descrim = 2 from [test].test_table

Normal query, which results in an optimized query plan touching only one of the union all branches:
declare @descrim int = 2;

select count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here "works"

However, as soon as a "select into scalar variable" is used, the plan becomes de-optimized as it does not eliminate the non-used union. (The plan is still correctly optimized when using a literal value in the query text.)
declare @descrim int = 2;
declare @brokeit int;

select @brokeit = count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here does NOT optimize plan for @descrim!

1. Is this de-optimization "expected"?
2. Where is this significant de-optimization behavior with respect to option(recompile) and/or selecting into a scalar variable documented or otherwise discussed in depth?
3. Is there an simple way to get an recompile-optimized plan with select @x = .. without using a temporary table (variable)?
While during query execution the union all will prevent actual IO access to the secondary artifact, such is still an issue with query plan generation. In the specific error case spawning this question, leaving in multiple tables for consideration prevents SQL Server from choosing an appropriate seek plan and the resulting plan options are very poor choices in the given domain.
The first "good" plan:

The second and "bad" plan:

This "bad" plan also has an implicit conversion warning, making me suspect that the select into a scalar variable might be bypassing many different optimizations - or even ignoring the option(recompile) hint entirely.


texts: How to use select (query)alias on WHERE clause?

texts:
i'm having a problem on my query, this is the working query and

    SELECT res_ID,res_fName,res_mName,res_lName,rs.suffix,rms.marital_Name,rg.gender_Name,rr.religion_Name,ro.occupation_Name,ros.occuStat_Name,res_Date_Record,rc.country_citizenship,res_Bday,TIMESTAMPDIFF(YEAR,res_Bday,CURDATE()) AS age,
(case
 when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=1) then 'Maternal and Newborn'
 when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=1 and TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=12) then 'Babies'
when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=13 and TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=24) then 'Toddlers'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=2 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=4) then 'Preschoolers'
 when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=5 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=8) then 'School Age Children'
 when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=9 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=12) then 'Tweens '
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=13 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=19) then 'Teenager'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=20 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=35) then 'Young Adult'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=36 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=55) then 'Middle-Aged Adults'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=56 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=100) then 'Senior'
   end) Age_Stage
FROM resident_detail rd
LEFT JOIN ref_suffixname rs ON rd.suffix_ID = rs.suffix_ID
LEFT JOIN ref_gender rg ON rd.gender_ID = rg.gender_ID
LEFT JOIN ref_marital_status rms ON rms.marital_ID = rd.marital_ID
LEFT JOIN ref_religion rr ON rr.religion_ID = rd.religion_ID
LEFT JOIN ref_occupation ro ON ro.occupation_ID = rd.occupation_ID
LEFT JOIN ref_occupation_status ros ON ros.occuStat_ID = rd.occuStat_ID
LEFT JOIN ref_country rc ON rc.country_ID = rd.country_ID

this is the Result:

i want is use the alias "Age_Stage" column on WHERE statement,
on the image above Age_Stage has their specific value, i just want is to filter those Age_Stage by value that i want.
but when i add on the query
 WHERE Age_Stage = "Young Adult" on the of query the error is result is

  Unknown column 'Age_Stage' in 'where clause'


texts: Schema and/or reporting SQL for customer acquisition cost?

texts:
This a design question around creating fact/dimension tables to query customer acquisition cost and how to properly model the tables and query them.
Business Rule

We spend some money per day per channel. For example, we spend $500 yesterday with Facebook, $400 with Google. Day before yesterday we may have spent $600 with Facebook and $200 with Google.
Each day some number of customers are acquired and we can attribute the channel at the time of acquisition
We assume that acquisitions from a paid channel are the result of that day's payments

For example, if we paid per the examples above, and suppose yesterday we had 10 customers from Facebook and 5 from Google, yesterday's Customer Acquisition Cost (CAC) would be (500 + 400) / (10 + 5) or $60
For the sake of this question, let's not quibble over the "correctness" of these rules.
The dimensional model where I'm getting stuck
I am new to dimensional modeling, but my understanding is facts represent business things that happen, so I'd expect something like so:
fact_acquisition
----------------
acquired    -- 0 or 1
customer_id -- ref to customer dimension
channel_id  -- ref to acquisition channel dimension
date_id     -- ref to a date dimension as to when this happend

I'd also expect some sort of marketing spend fact:
fact_marketing_spend
--------------------
amount
channel_id
date_id

From this, it's clear to me how to calculate something like spend-per channel or acquisitions per day.
Calculating CAC
To calculate CAC in this schema, it seems I'd need to join the two fact tables somehow (by channel_id) and use aggregate functions to aggregate acquisitions per day over spend per day per channel.  I'm confident I could write SQL for that, but, here is my question:
I'm to understand one should not join fact tables and that doing so can lead to incorrect results.  So how can I produce the needed values for CAC per channel?  Do I write separate queries and do the aggregation inside my reporting tool (e.g. Excel)?
I have googled extensively to find an example schema for this and have looked at the relevant parts of The Data Warehouse Toolkit, and am at a loss.


texts: Invoices kept in document with user information in no sql database is recommended?

texts:
I have a application which has tenant details like

{
"name" : "",
"Address" : "" ,
...
Bills :[
     current : {"rent":"" ,"electricity":"" ,"date":""},
     previous : [{"rent":"" ,"electricity":"" ,"date":""},
                {"rent":"","electricity":"" ,"date":""}]
]
}

I have Bills of a tenant in the tenant document itself ,
is this model recommended / acceptable ?

The reason would be silly ,
I thought since the scope of bill is only viewing and won't do any computations on bills , So keeping it in a separate document would be a extra work . So I kept in this tenant document itself .

So Keeping bills in this type of document is good or bad ?

If I get some clarity it would be nice , Thanks .


texts: MySQL still using one CPU even though extra CPU added

texts: I m SQL server DBA. My company just handover one MySQL server to manage which i m still learning. Recently extra CPU has been added to the server due to CPU is 100% but when checked on the server , MySQL still using one CPU only and doesn't utilized other CPU. Is it anything need to configure so that MySQL will use other CPU as well?


texts: How can one identify if FK's needed to be indexed apart from the NC indexes already residing

texts:
I was going through many articles, which said indexing the FK's is beneficial for good performance, specially for the environment where most of the reporting is done.

I have a similar env where there lots of reads for this one particular database. As a good process, I am trying to find the Foreign Keys which can be indexed , even though NC' indexes are there for those tables.

So, is there a way I can identify those FK's which needs to be indexed, making sure it does not overlap with NC indexes, already existing on those tables for that particular database.

The database in question has quite a number of FK's and queries with lots of joins, comprising a good no of tables. This DB is 95% involved in read and activity and once in a month a file is uploaded which updates the tables.

+Info- We are using SQL server 2012.

Please suggest for any such script or a better way to approach this, thanks!


texts: Unique index by VARCHAR(1023) vs by BINARY(32)

texts:
I have a table of unique urls (~70M records, an average urls length is 51 chars):

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uri` varchar(1023) NOT NULL,
  `uri_hash` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `i_1` (`short_url_code`),
  UNIQUE KEY `i_2` (`uri`(255))
) ENGINE=InnoDB CHARSET=utf8;

uri_hash = hash(uri)

It was created when MySQL did not support long unique indexes. Now we have MySQL 5.7 and I think about optimisation, but I'm not sure wich way is better:

remove uri_hash column and make index i_2 longer (1023), and update uri column to support different cases (upper/lower) CHARACTER SET utf8 COLLATE utf8_bin
remove index i_2 that we don't use and search by uri_hash

a. leave uri_hash varchar(64)

b. convert to uri_hash binary(32)
leave as is

I have done some benchmarks, but I have not found significant difference between these variants: the same 60 reads + 60 writes actions per second in one thread

This table will grow up to ~200M records.

Can you suggest what variant is better?


texts:
My Server has Total RAM 200GB.

I have set 80% of total RAM for SQL Server as it needs more memory.

Just want to know if there is any fixed number or method to finalise amount of memory to be set for SQL Server and then lock pages for SQL Server (or) it depends completely on resource utilization,workload?


texts: Left outer join not returning all rows in a grouping query

texts:
I'm trying to query all users to find the price of orders in the last month. I'm doing this on PostgreSQL. Here's a toy database to show my problem: I have a people table and an orders table. I'm trying to find the sum of orders for all people in the last month.

Here's my data:

select * from people;
 id |     name
----+--------------
  1 | bobby lee
  2 | greg grouper
  3 | Hilldawg Ca
(3 rows)

select * from orders;
 id | person_id |    date    | price
----+-----------+------------+-------
  1 |         3 | 2014-08-20 |  3.50
  2 |         3 | 2014-09-20 |  6.00
  3 |         3 | 2014-09-10 |  7.00
  4 |         2 | 2014-09-10 |  7.00
  5 |         2 | 2014-08-10 |  2.50

And here's the query I'm trying to write:

SELECT p.id, p.name, sum(o.price)
FROM people p
LEFT OUTER JOIN orders o
ON p.id = o.person_id
WHERE o.date between date '2014-09-01' AND date '2014-09-30'
GROUP BY p.id;

Here is the return I get:

 id |     name     |  sum
----+--------------+-------
  2 | greg grouper |  7.00
  3 | Hilldawg Ca  | 13.00
(2 rows)

I'm expecting a third row, with person id 1 (bobby lee) having a sum of zero for the last month's orders (since no orders exist for bobby). What am I doing incorrectly with my query? My impression was that doing a left outer join and grouping by people.id means that the people table should set the number of rows in the output.


texts: Converting Monthly Rolling SUM (YTD) back to Monthly numbers

texts:
I'm having a problem converting rolling SUM by month (monthly year to date) back to only that month's numbers.
For example, see table below. Periods are (YYMM) and a monthly period per year starts with 07 and end with 06:
Name         Period         Amount
AAA          1611             10
BBB          1611             15
CCC          1611             20
AAA          1612             12
BBB          1612             18
CCC          1612             24
AAA          1701             13
BBB          1701             20
CCC          1701             27

The result that we are after is as follows. Period 1611 is the lowest in this example, but can be any YYMM month (07,08,09,10,11,12,01,02,03,04,05,06):
Name         Period         Amount
AAA          1611             10
BBB          1611             15
CCC          1611             20
AAA          1612             2
BBB          1612             3
CCC          1612             4
AAA          1701             1
BBB          1701             2
CCC          1701             3

Basically, it needs to take a higher period's value, minus the next period below it according to the Group By. If it can't find a lower period, then it keeps data the same.
Currently what I did was to Group By the data from the table I'm pulling it from as there would be multiple lines for each combination and we only need unique ones according to the group by.
Select

a.[Actuality], a.[Period], a.[(C) Company Code], a.[(C) Account Code], a.[(C) D1 Code],
a.[(C) D2 Code], a.[(C) D3 Code], a.[(C) D4 Code], a.[(C) Intercompany (To)],
a.[(C) Intercompany (From)], a.[(C) Type], Sum(a.[Amount]) as 'Amount'

From Table1 as a

Group By
a.[Actuality], a.[Period], a.[(C) Company Code],
a.[(C) Account Code], a.[(C) D1 Code], a.[(C) D2 Code]

I was thinking about putting a Where with a difference between periods needing to be 1 (1608-1607) or 89 (1701-1612) or 0 with AND a . [Period] needing to be smallest value for that Group by.
Could I please ask for help in formulating this in SQL?
Additional information

In the table only 1 year data is stored. Next year the data is moving to another table and its starts all over again from Period YY07 to YY06
In a table during the year around 2.5M rows are made
There are no duplicates in the table as a Group by is used to eliminate them
Variance between two lines should be also shown if a line exist 1 month and the next month doesn't: YTD in Period 1611 ABC has 20 and if Period 1612 ABC has 0 the line wouldn't appear in the YTD table, but in the month to date (MTD) would need to show Period 1612 ABC -20  (0-20)


texts: IS_MEMBER() in Azure SQLDB not working for AD groups?

texts:
I am trying set up RLS and want to leverage AD groups.
DB is created in Azure, and I know AD is working as I can connect using AD acount with SSMS

First test Local Machine, is working as expected (img 1)

Now try Azure

The IS_MEMBER() appears to not work as expected in Azure for AD Groups, but it is recognising it a as valid AD domain of the Domain/group (see first 2 lines)

Any ideas??  I want to be able to have a view, that has the ADGroups and then put Row Level Security RLS to filter the view for different groups

Cheers

Mark


texts: Secondary Clustered DTC will not work when Primary goes offline - SQL Server Issues

texts:
I've been working on setting up clustered environment with a two new DTC roles, DTC1 and DTC2. I set up DTC1 which allows both clustered VM's and SQL Server Instances to function correctly. However, when I disable DTC1, DTC2 will remain online but no DTC transactions will work. I'm actually going through this issue with a Microsoft tech right now (without much luck). Both DTC services are set up with similar configurations. Anyone here have an idea as to what might be going on?

Here's my setup:

One clustered environment
Two DTC Roles
Two Separate ISCSI disks, one for each role
Two VM's (same configuration)
Two SQL Server Instances, on on each VM

Networking configuration is allowed, everything looks to be set up properly.


texts: Are there any schema design changes that can take a design from 4NF into a different 4NF design?

texts:
My thought/contention is that once a database schema is in 4NF it by definition can only be changed into something less than 4NF (Denormalisation, 3NF etc) and that there is no alternative 4NF design for it to take.

That is, once a design is in 4NF there is no design choice/change that will take it to some other different 4NF design.

Is this correct (or are there any cases where 2 different 4NF designs are put up for comparison or converted between)?


texts: SQL User Permissions for Continuous Integration via DbUp and Octopus

texts:
Currently, our deverloper group has started using DbUp and Octopus to do Continuous Integration on SQL Database Deployments. We have created a Domain user which will utilized to do the SQL Database deployment via Octopus server.

I wanted to know, what permissions should a Domain user should be granted for Continuous Integration process?

I don't want to give "SysAdmin" permission to this Domain user.

Do people have implemented above combination for there Continuous Integration of DB Deployment?


texts:
I appear to have encountered an obscure codeigniter error, invalid db driver when I move from my remote server, to a local xampp server with default config! It's just a simple script using some mysql queries, the driver is mysqli, any ideas?

thanks!


texts: Teradata 15: install TTU 15, The installation of Shared ICU-32 bit appears to have failed

texts:
I downloaded Teradata Tools and Utilities (TTU 15) from Teradata support site.  After I uninstalled the TTU 15, now I cannot install it again!  I am using Windows 7 Enterprise version.

Here is my error message:

The installation of Shared ICU-32 bit appears to have failed

I completed uninstall the TTU 15 and was looking for Teradata in register.  All are removed from my laptop.

Any suggestions?

Thanks so much!


texts: Default value as expression from other columns

texts:
I have 2 tables:
users

user_id (int ai pk)
premium

premium_subscription

subscription_id (int ai pk)
user_id (int) -> fk to user.user_id
expiration (datetime)

I would like to implement a boolean value based on the fact if there's a subscription and its expiration date is ahead of the time I made the query.
I made this if statement, that works in a SELECT query. However, I'm not able to implement it as the value of a table.
IF(timestampdiff(SECOND, CURRENT_TIMESTAMP, (SELECT expiration FROM premium_subscription WHERE user_id = *)) > 0, 1, 0);

Is there any way to apply this expression to this column? Thanks in regards! ^^


texts:
I get a file from another department that contains an id column. The id column is mostly numbers, but also contains some letters.

I receive the file in XLS format.

When I try to use the import function of SQL, it reads the id column as a double integer type. Is that not incorrect? Can double datatype have letters? Either way, it is correctly importing the number values for the id, but not the values that begin with letters. It also does not advise of any errors or problems.

I have tried to convert it to a flat file and import it that way, but I am fighting with the date column data types as well. (They are in 1/13/2011 format - I don't know why the import wizard can't import this either).

I would like the end result to be a nvarchar. How can I go about getting this file to convert properly?


texts:
I recently repaired a single corrupt page in one of the tables in a 70gb database.

I had a backup which included the data contained in the page so I went about using DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. I then transferred the rows from the old database back to the newly repaired database.

After running a DBCC CHECKDB WITH NO_INFOMSGS to make sure everything was okay again I then ran a full backup. The backup is 10gb less than the previous backups (like the one I took before the repair with CONTINUE_ON_ERRORS).

What is the reason for this? Does the DBCC repair do any reordering of pages which would recover space or something similar? Table sizes look similar to previous but I'm a little worried there could be missing data.


texts:
I have table with two indexes:

latest_channel_snapshots_views_idx (view_count DESC NULLS LAST)
latest_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST)

What I want is to be able to sort by view_count overall, and within specific network. Postgres uses correct index for both of these cases. However, if i want to find record with most views where network_id is NULL, it uses the first index, and filters out the , thus performing really slow:

explain analyze SELECT *
FROM latest_channel_snapshots
WHERE network_id IS NULL
ORDER BY view_count DESC NULLS LAST
LIMIT 5 OFFSET 500000;

                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=39599.04..39599.44 rows=5 width=74) (actual time=1926.271..1926.287 rows=5 loops=1)
   ->  Index Scan using latest_channel_snapshots_views_idx on latest_channel_snapshots  (cost=0.00..42001.59 rows=530336 width=74) (actual time=0.060..1899.224 rows=500005 loops=1)
         Filter: (network_id IS NULL)
         Rows Removed by Filter: 305022
 Total runtime: 1926.309 ms
(5 rows)


texts:
Having following Foo table:

id | name    | group
===|=========|======
1  | "John"  | 1
2  | "Paul"  | 1
3  | "Marry" | 1
4  | "Josh"  | 1
5  | "Mike"  | 2
6  | "Kevin" | 2
7  | "Ramon" | 2
8  | "Kim"   | 2
9  | "Henry" | 3
10 | "Susan" | 3
11 | "Greg"  | 3
12 | "Julie" | 3

I'd like to select it having cycling order on group field. For example, when I would like to select 6 rows, query should return:

id | name    | group
===|=========|======
1  | "John"  | 1
5  | "Mike"  | 2
10 | "Henry" | 3
2  | "Paul"  | 1
6  | "Kevin" | 2
11 | "Susan" | 3

important - there are unlimited groups in the table, so using CASE or any verbose function is pointless.

Is this possible using MySQL? So far I searched for custom order functions. I also thought about to iterate over group ids and use this as sorting, but I'm stuck.


texts: How to store JSON data from APIs and be able to search efficiently

texts:
I am displaying documentation using Swagger, but the documentations are long so I made a side menu and split up the data in the database. Splitting it up, it allows me to search for specific areas and endpoints of the documentation and display it, and only display what is selected instead of everything, every time.

There are different versions of the documentation so some of the information overlaps. I am not sure the best way to store this, and currently, it is separated by version of the same documentation but there is a lot of redundancy since a lot of data overlaps from the previous versions.

Currently, the structure is similar to this:

table api_data

id
tags
general_path
path_content
product_id
search (keywords to index)
overhead_id
version_id

table api_data_overhead

id
product_id
file_overhead

table api_product_versions

id
active
product_id
name

The overhead for the file contains a lot of common information for every path for that documentation file. It is very long but doesnt need to be indexed or searched.

I am looking for a way to improve this database design as it grows and more documentations are added. I am concerned with performance when searching and uploading data. There is a lot of redundancy right now, and wondering if it can be better.


texts: Why are U locks required when using Read Committed Snapshot Isolation

texts:
I consider myself a beginner at Sql Server locking.

My understanding is that when using RCSI, Sql Server doesn't need to issue S locks because it uses row versioning (in most cases). From http://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx we can read the following about U locks:

  Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Knowing this, why does Sql Server need to issue U locks (when using RCSI)?
It seems to me that Sql Server could simply read the rows, and request a X lock directly if an update must be performed.

The reason I've been thinking about this is because of deadlocks I'm experiencing with 2 sessions updating the same table. Something like this (simplified for clarity):

Session 1:

BEGIN TRAN
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value1
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value2
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value3

Session 2:

BEGIN TRAN
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value4
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value5
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value6

From what I gather in the Profiler lock report, both sessions have an X lock on some rows of the table, and are requesting U lock on each other's X-locked row => deadlock.
However, I know for a fact that each concurrent session will always modify different rows because the value X will never be the same in 2 different concurrent sessions.
That is, if those sessions were able to get their requested U lock, sql server would realize it is not necessary to upgrade an X lock anyway.

In conclusion I feel like, even though U locks are supposed to reduce deadlocks, they actually are creating an unnecessary deadlock here.

I understand they can be valuable for other isolation levels, but for RCSI, I don't get it...


texts: I'm just search for advice. I have . Every Product type has its own set of characteristics. The Big problem is when someone add new Product type, aslo need create new table with product characteristics. I can see only one solution without DBA, saving all products characteristics in one table but this is not good.


texts: Problem generating restore scripts for multiple ola backups

texts:
I am using Ola for maintenance solution. I have 300 databases I want restore those databases  using scripts rather than going through one by one.  I prepare the following script to generate restore script.

SELECT
   'RESTORE DATABASE [' + name + ']'  +'
    FROM DISK = ''C:\Backup3\PC1800\'+name+'\FULL\PC1800_'+name+'_FULL_20160504_162534.bak''' + '
    WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10
    GO'
FROM sys.databases

It generates restore scripts for all  databases .

For example for database AdventureWorks2008

RESTORE DATABASE [AdventureWorks2008]FROM DISK =
          'C:\Backup3\PC1800\AdventureWorks2008\FULL\PC1800_AdventureWorks2008_FULL_20160504_162534.bak'
        WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10

GO

Problem:

The problem it is generating _162534 for all databases. But the second part of the backup file name is different from database to database.

Is there  a way I can change the script so it will generate the right number from the backup file not 162534 for all databases?

Exp : PC1800_AdventureWorks2008R2_FULL_20160504_162538


texts: Combined UUID Index + AI PK Insert Performance

texts:
I posted this in stackoverflow but realised it probably belongs here instead.

Imagine the following table:

CREATE TABLE `orders` (
     `ORDERID` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `DATE` date NOT NULL,
     `UUID` binary(16) DEFAULT NULL,
     PRIMARY KEY (`ORDERID`),
     UNIQUE KEY `UUID` (`UUID`),
    );

The UUID and DATE is generated client side. If I insert 100 million rows into this table, the insert time will increase quite a lot because of the UUID unique constraint, as mysql must be sure that the UUID doesn't already exist. And I use the term "quite a lot" in comparison with only using AI PK.

But, if I do like this:

CREATE TABLE `orders` (
     `ORDERID` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `DATE` date NOT NULL,
     `UUID` binary(16) DEFAULT NULL,
     PRIMARY KEY (`ORDERID`),
     UNIQUE KEY `UUID` (`DATE`,`UUID`),
    );

And add the combination of date and UUID as a unique constraint, instead of only the UUID, and increment the date by one day every 10.000 rows or so - will the performance be better because the server only has to check an index of a maximum of 10.000 rows, since it already has an ordered list of dates?

I can only find documentation about the performance of indexes for SELECT - not for INSERT. Can anyone explain to me if the logic in the above question is completely flawed?


texts:
I have successfully created an event in MySQL to update a column called dob in a table called auth.

My problem is that the event won't run. Here is my code:

DELIMITER $$
   CREATE EVENT update_event1
   ON SCHEDULE EVERY 1 MINUTE
   ON COMPLETION PRESERVE
  DO
    BEGIN
      UPDATE auth set dob=DATE_ADD(dob,INTERVAL 1 DAY) ;
    END $$;

How to solve this problem?


texts: Postgresql - roles without linux accounts?

texts:
Basic question:

what I'm reading describes how to create linux users in parallel with postgresql roles.

However, I don't see the value in creating any of these users in linux - I just want to create a postgresql role - and login to that from my application - or when running psql from the terminal using my own login - but in the role of the project.

e.g. - if the project is 'foo' - I want to have the psql -U foo, but no 'foo' user under linux.  After all - what's the point?  It's a role purely for psql - not for linux.

Is this possible in Postgresql - and if so - how do I do this?

I've trivially created a postgresql user role - without a linux account - but then I cannot ever -U role, as I get an error around peer authentication.

So - I assume I need to change how postgresql is handling permissions - and that's where I'm too green with postgresql to know what I'm doing there.

Perhaps a pointer to a clear little document discussing authentication modes or connection modes and setup for postgresql would help me resolve my issues?


texts: Create temporary tables per session in a DB other than tempdb

texts:
Using SQL Server, Is there a way to create a temporary table per user session in a user database in the same exact way they are created in tempdb?

Using tempdb is fine in most cases, but some of the operations I've been seeing generate a lot of IO and in some cases end up saturating the templog file.

So here is a typical situation

-- create the table
create table #some_table(t int)

-- Do some work
declare @rownum int = 1

begin transaction
while 1=1
begin
    insert into #some_table values(@rownum)
    if @rownum > 5000000
        break

    set @rownum = @rownum + 1

end

Work continues on until @rownum reaches 5000000 and because its within a transaction, tempdb can't shrink. In the mean time, alerts start showing up and the LOGS disk fills up. Doing a SHRINKFILE is useless because the transaction is left open.

The work has to be done in tempdb because the process that created that temporary table could be run multiple times or there could be multiple processes that use the same table name.

-- show the contents of #some_table
set statistics io on

select * from #some_table

set statistics io off

-- results
(5000001 row(s) affected)
Table '#some_table_________________________________________________________________________________________________________00000000001F'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So until I find a way to simulate that temporary table per session ability in tempdb, we have to continue tracking down offending queries and basically play air traffic control with tempdb.

Here is the situation I'm currently dealing with

tempdb's log is currently at 40GB+ and climbing fast.


texts:
In the following schema:

Collections
   Upvotes
Reports
   Upvotes
Reviews
   Upvotes

I'm tempted to have a single Upvotes table with a single "entityId" column that stores either the CollectionId, ReportId, or ReviewId.  There will also be an enum for Type - storing either collection, report, or review.

The entityId will always be required, and the logic will always make sure that inserts enforce uniqueness across each type.

The benefits of this is that adding another type is just a matter of expanding the enum.  Everything will live on a single table with no redundancy.

The cost seems to be the added complexity on the logic side, which is contained to the one place in my application logic that inserts new entities into the table.

Practically speaking, is there anything wrong with this approach?  What would be some other reasons to avoid this?


texts: SQL Server 2016 Always ON high availability group listener configuration

texts:
I have 2 servers running Windows Server 2016 with Microsoft SQL Server 2016 Enterprise. I have created a fail over cluster in Windows and joined both nodes without Active Directory. Then I created an availability group with both nodes and everything is fine and the database is synchronized.

Now I created the AG listener, I am not using DHCP, so I assigned a static IP. What I don't understand is how the fail over is going to work; when I connect to the listener everything is fine when Node1 is primary, when I do manual failover to Node2, I can't connect.

How will the listener switch to node2? Should the listener have 2 IPs for both nodes?

BTW, the servers are not on a private LAN.


texts: Is it necessary to copy dump to MySQL 8 replica?

texts:
I am trying to make a simple master-slave replication topology with MySQL 8.
Official documentation says that if source database has data, it is necessary to update replica with source's dump.
So is it possible to skip uploading dump to replica? Can MySQL source synchronize replica automatically?


texts: Searching a database safely using SELECT

texts:
I have a site that allows users to search the database for several items. However I am not that good with SQL and I have read that allowing user to type in boxes that then use "SELECT-statements" is not safe. Code below:

Search-button sends search as parameter:

protected void search_click (object sender, EventArgs e)
        {
            Response.Redirect("Search.aspx?Result=" + TextBoxSearch.Text);
        }

onLoad in my search-page it gets the value and calls getData():

search = Request.QueryString["Result"].ToString();
getData();

getData() method:

protected void getData()
        {
            using (SqlConnection con = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT [categoryId], [category] FROM [Category] WHERE ([category]=@search)";
                    cmd.Parameters.AddWithValue("@search", search);

                    con.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            LabelCat.Text = reader.GetString(reader.GetOrdinal("category"));
                            HiddenFieldID.Value = reader.GetInt32(reader.GetOrdinal("categoryId")).ToString();
                        }
                        else
                        {

                            LabelCat.Text = "Add"; //When search is made but no cateogry found
                        }
                    }
                }
                con.Close();
            }
        }

Is this safe? I feel like it is very weak against sql-injections but then again I don't know much about that. If it's not safe, please suggest a safe way I can do this.

I am building my application in VS12 using ASP.NET and C# webforms. Building my database using SQL management studio.

EDIT: Eventually I will have to search several tables here using join-statements, don't know if that is relevant but thot I should mention it.


texts: Performance optimization: Alternative to LEFT JOIN the same table twice

texts:
I would like to extend the following query with the commented out LEFT JOIN. However, those lines make the query noticeably slower.
The query is used for an API to lookup user IDs by phone number and things need to be fast. So, is there a more performant alternative to joining the same table twice with different ON clauses?

set statistics time on

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE = 'WORK' --WORK PHONE (only adds 35ms to lookup time)
--LEFT OUTER JOIN ADDR_MASTER am2
--  ON am2.ID = ivr.ID
--      AND am2.ADDR_TYPE = 'HOME' --HOME PHONE (fallback if not in IVR, adds 100+ ms :(
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
--OR am2.PHONE = '0123456789'
)

set statistics time off

Here are the plans:

Old
New


texts: Sync two postgres databases from two sperate locations with no network

texts:
I have 2 postgres databases running the same schemes on two different computers, that are not guaranteed to have any network connection while having to handle read/write requests.

Because they operate on two different locations, I'm trying to find a solution to merge them once in a while to keep data updated in both (I can connect them every couple of week, or pass files with a flash drive).

My main concern is that this databases have unique values in some of the tables (Auto Increment Primary Keys), and I'm not sure how to handle this - what if both dbs create the same key, for different data? Are there any tools that can merge automatically and output changes that were done?

(If it matters, using postgres 10, ubuntu env.)

Thanks


texts: Do Log_Slave_Updates in MySQL 5.7 fail the replication in Linux?

texts:
I have a Master Master replication setup in RHEL for 2 Nodes.One server is acting as Master as well as slave for another Server ( which is also Master and Slave )
Let us say A server and B server

I have enabled log_slave_updates on B server so that all the statements receiving from master ( Server A) should get logged to binary logs of server B .

I was expecting Replication , will get fail because :

         1. Created a Table on Server A.
         2. It get replicated to Server B.

Now Binary log from Slave B will go to Server A and this should break the replication saying "table already exist". but it didn't

What is  the logic here ?

Edit Part :

Server id on both servers are different


texts:
I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params and we wanted the optimizer to grab a new plan with each call (not ideal, but it was an attempt to get it to run more reliably faster each time vs. using an old cached plan)"

Is what they saying correct ?
Is there any way they can do that with no recompile

   create PROCEDURE [dbo].[VERIFIER_QUEUE]
   @cas_name varchar(20) = NULL,
   @instance_name varchar(50) = NULL,
   @verifier_id int = NULL,
   @applicant_type VARCHAR(20) = NULL

    WITH RECOMPILE
    AS

   BEGIN
SET NOCOUNT ON

DECLARE @cas_name_x varchar(20)
DECLARE @instance_name_x varchar(50)
DECLARE @verifier_id_x int
DECLARE @InstanceId INT
...............


texts:
This may be a novice question but could it be possible query here.

If I have a compound Index as

{ a: 1, b: 1, c: 1, d: 1 }

Is this query possible?

db.data.find( { a: {$gte:1,$lt:2}, b: { $gt: 3} },c:"test",d:"yes" ).sort( { b: 1 } )

I mean can I use sorting on field b with this compound index and the specified query or do I need to create a separate index on b?


texts: How to connect Powershell to the SQL Server named instance?

texts:
When I run

# Load the SMO assembly
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$ServerName = "[local\s12]";

#Get a server object which corresponds to the **default** instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

I am getting an error

Failed to connect to server local\s12.


texts: Extracting Company from Multi-tenant Shared Schema Database

texts:
We have a mutli-tenant shared schema database architecture. As a form of load distribution we have spread the tenants/companies across 6 SQL 2014 servers and 20 databases. All DBs are virtually the same except they hold about 10K different companies combined. We need the ability to extract entire companies out of one database and move them into another - some 200 tables. Ideally, we would like to be able to move them with minimal downtime. I am exploring ways to make this happen short of just writing custom scripts to copy every single table.

I learned you can setup replication with a filter based on a view (selectively extract company x data), but then I would have to setup replication for 200+ tables, which sounds cumbersome and could adverse impacts on production. Partitioning can do wonders, but not every table has a neat CompanyID reference. We don’t have AG but are planning on it and thought we could then use the replica to extract the company data as to not impact production.


texts:
I want to migrate a production database from one linux server to other.
I am not concern about upgrading my database version, just I want to move the database from one server to other with minimum down time.

The version of my database is 8.3. Its of size 160 GB.

It takes 12 Hours to take the dump and 8 hours to restore it.

Over here the downtime is of 20 Hours, which I cannot afford.
I can afford a down time of 4 hours at max.

So how will I refresh the delta changes that happened in 20 Hours into the production database with the database over the other server.

Please help.


texts: Postgres: Filtering large jsonb arrays coming from a sub-query

texts:
I have a table called external_data in Postgres designed with two columns :

+----+---------------------------------------------+
| id |                    data                     |
+----+---------------------------------------------+
|  1 | [{"firstName":"John","lastName":"Doe"},...] |
|  2 | [{"productName":"Mouse","price":123},...]   |
+----+---------------------------------------------+
id: integer
data: jsonb

The purpose of this table is to save arrays of over a million objects.

So, in order to get a specified jsonb data, I'm just doing a simple query:

SELECT data
FROM external_data
WHERE id = 1
LIMIT 1

but I don't want to get all the entries in the json array, since there are too many of them.
Basically this is what I do to filter the entries:

SELECT json_agg(elem) FROM
(SELECT jsonb_array_elements(data) AS elem FROM external_data WHERE id = 1) x
WHERE elem @> '{"property":"value"}'

This works well, taking 1 to 1.5s to perform, but I wonder if i can optimize this request using indexes or something else ?

I've already read about GIN indexes using jsonb_path_ops, but since the jsonb data is from a subquery, creating an index with this command

CREATE INDEX idx_data on external_data using GIN(data jsonb_path_ops)

my query doesn't use the index to perform faster :

"Aggregate  (cost=2.77..2.78 rows=1 width=32)"
"  ->  Subquery Scan on x  (cost=0.00..2.77 rows=1 width=32)"
"        Filter: (x.elem @> '{"Property": "value"}'::jsonb)"
"        ->  ProjectSet  (cost=0.00..1.52 rows=100 width=32)"
"              ->  Seq Scan on external_data  (cost=0.00..1.01 rows=1 width=32)"
"                    Filter: ((id)::integer = 1)"

And I created some functions using plv8 language and tried to use expression index, and it didn't work. Also, using theses functions would increase the query duration by adding 8 to 9s.

Is there an efficient way to optimize these query or it is the optimal way ?


texts: Can PostgreSQL with synchronous streaming replication get behind on the WAL logs?

texts:
I have 2 Postgres 9.4 servers running on 2 identical VMs that I am using to learn how to setup replication before it gets used in production.  Hot standby isn't required but it would be preferred.  I setup the replication following this guide, though I'm on Ubuntu  14.04 using the Postgres repository, and quickly verified it works by running pgbench and looking at both servers.

Under the documentation for 9.4 for streaming replication, I need to set wal_keep_segments to a high enough value in case the standby server gets behind, but does that only apply to asynchronous streaming replication, or does it also apply to synchronous? Forgive me if I missed that in the docs.  Otherwise I'll have to setup replication slots to make sure the wal doesn't get behind.