texts:
Last week I asked a question that was answered below... I need to improve the question I asked earlier (in quotes below) and give a little more context. The answer below did help the repair performance but I still believe we may have done something wrong or missed a step that is causing the full repair to go very slow.
Previous Question:
"We have a 32 node cluster with an average of approximately 150GB of data on each node. When running a full repair (not doing primary range) it is taking approximately 26 hours to complete. This seems very slow to complete.. any ideas on why this type of repair is going so slow? Are there any settings I may tweak to make it go faster?"
More detail about question:

Our system is running on Cassandra 3.x
16x16 nodes
Replication 3x3
Previously doing incremental repairs
150GB per node

What happened was the following:

node bootstrapped on 3.x
started repair ( incremental ) : assuming this was full repair first time
lost 2 nodes -- (VMs crashed)
removed the two nodes from the cluster.
started full repair on every node (not doing primary range).

Yes running the full repair (primary range) did improve the total time.
New questions:

Did we miss a step (see above) in bringing our cluster back to normal.. such that it caused our full repair (not doing primary range) to take an extremely long time?
Is it typical that full repair (not doing primary range) takes more than twice the time to complete than a full primary range repair?

Answer to previous question:
How to make repairs go faster:

Use the -pr flag. This repairs only the primary range, which prevents the unnecessary repairs of the additional ranges. If you run your repairs on all nodes (and you should) then you'll repair everything anyway, but without repairing everything an extra time for each replica.
Focus repairs only on the tables/keyspaces that need it. Some tables may be static enough that you don't really need to repair them. For instance, if you don't regularly add new users, you probably don't have to repair system_auth. Nodetool repair allows you to specify keyspaces and tables to run a repair on, and you should definitely use it.
Avoid streaming repairs from different physical data centers. This was a big "win" for us. Nodetool repair allows you to specify a list of hosts to include in the repairs. If you have multiple data centers, you should limit your node to only repair itself from nodes which are "local" to it.
Focus repairs only on specific token ranges. You probably won't always have to do this, but sometimes you only have a window that allows you repair for so long. To programmatically repair say only one third or quarter of your data, you can further focus your repair tasks by specifying certain token ranges. I don't typically like to do this one, because repairing all data for a table can stretch out for a few days. But if you have only a small window to run a repair, this might help.

tl;dr :
Don't repair the same data multiple times.
Repair only where you need to.
Stream repairs only from specific, local nodes.
Focus your repairs as much as you can.


texts:
Let's say I have a table which stores different bills.

There are multiple departments using this database and every department  needs to use their own incremental numbering for the bills.

I do not want to use multiple tables, so I have an issue what the best method for this issue is.

I have come up with an suggestion by myself, but I think this is not a good solution. Namely keep a column DepartmentBillId and keep track of the numbering in PHP (this will be the easiest way though, but I have multiple users so this could actually cause conflicts).

I hope somebody can guide me in the best direction!


texts:
everyone.

I'm deeply sorry if this is an amateurish question, but I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We have not enough DB free space in order to store the full backed-up table.

Which is the best way? Is there a way to do this by blocks? I mean something like: backing up the first 100K rows from the original table, updating those 100K rows in the original table, deleting those 100K rows from the back-upped table, backing up the following 100K rows from the original table, and proceeding analogously. Is this feasible?

Thank you very much in advance.
Martín.


texts: Oracle Database Copy Failed Using SQL Developer

texts:
A few days ago while I tried perform database copy, using the database copy feature in SQL Developer, from a remote server to a local server. I got some warnings and one of them was like this:

  "Error occurred executing DDL for TABLE:MASTER_DATA".

And then I clicked yes, but the result of database copy was unexpected, there were only few tables has been copied.

When I tried to see DDL from SQL section/tab on one of table, I got this kind of information:

  -- Unable to render TABLE DDL for object COMPANY_DB_PROD.MASTER_DATA with DBMS_METADATA attempting internal generator.

I also got this message and I believe this message showed up because there's something wrong with DDL on my database so tables won't be created.

  ORA-00942: table or view does not exist

I've never encountered this problem before and I always perform database copy every day since two years ago.

For the record before this problem occurred, I have removed old .arch files manually not by RMAN and I never using any RMAN commands. I also have removed old .xml log files, because these two type of files have made my remote server storage full.

How to trace and fix this kind of problem? Is there any corruption on my Oracle?


texts:
Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc?

How much of a performance improvement one can expect for not enforcing database integrity through internal database tools?


texts: Which database is best for storing mouse movement data for analytic stuff?

texts:
Problem

I want to track the user behavior on website. I am tracking the mouse movement and after tracking certain number of mouse movements, i am planning to send this data to my server, where i will code an API which stores this data into database.

The data will be like an array and each cell of array have this:

{x: some-value, y: some-value, some-other-properties:{}}

My solutions

Initially i was thinking to store this data into SQL database. Then i thought this data is more like a non-structured data, it will also grow rapidly with time as i have many websites in future and number of users will grow very fast,  i should store it in MongoDB (NOSQL database). The other option in my mind is influxDB which is used to store time-series data. Now, we see the mouse-movement is more like a time-series data. Hence i am just confused and need some expert opinion.

I want to store data efficiently. This is my main concern.


texts: How can I drop a stored procedure whose name is in a variable?

texts:
Given a variable that contains a stored procedure name:

DECLARE @stored_procedure_name sysname;
SET @stored_procedure_name = 'some_stored_procedure_name';

How can I drop the stored procedure?


texts: Change Data Capture SSIS not picking changes

texts:
I am trying to implement change data capture in SQL Server 2012. I enabled it at database and table level. when i try to update the table, CDC_CT captures the changes. However when i am using SSIS CDC components, it doesnt pick anything.

the version of SQL server is:
Microsoft SQL Server 2012 (SP3-CU10-GDR) (KB4057121) - 11.0.6615.2 (X64)
    Jan  9 2018 21:26:48
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Can someone please guide?

More information here: https://stackoverflow.com/questions/51959201/change-data-capture-cdc-ssis-not-working


texts: Help with optimising a sql query on two partitioned tables

texts:
I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise.

The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate indexes.

The tables are as follows:

and

Here is the query:

DECLARE @day as varchar(max) = N'20141007'
DECLARE @dayStart as varchar(MAX) = CONCAT(@day,N' 00:00:00.000')
DECLARE @dayEnd as varchar(MAX) = CONCAT(@day,N' 23:59:59.999')

SELECT
    messageId, time, direction, hasRouting, deviceType,
    unitId, accountCode, clientId, data
FROM
    Logs AS A
WHERE
    EXISTS (SELECT * FROM Acks AS B
            WHERE (A.messageId = B.messageId)
              AND (B.direction = 0) AND (B.isNack = 0))
    AND (A.unitId LIKE 'TEST')
    AND (A.direction = 1) AND (A.time BETWEEN @dayStart AND @dayEnd)
ORDER BY
    time ASC

I must admit that I have not had a lot of experience with queries from two tables i.e. joins. The way I interpret this query is that it is going to first filter out all of the entries for the log table, and then go and do a "where" query on the entire log table for each row in the log table.

Now, each table is about 50GB and you can imagine if it's not utilising the partitioning scheme correctly this query is going to iterate through the entire table.

My concerns with the query are:

Performance. The query is taking way too long to execute
It is not fully utilising the partition function when searching

I have noticed that it looks in every single file group when executing the query, even when the query only spans 1 day. I'm not sure if this is due to the Exists.

Some advice and tips on how to structure this query would be greatly appreciated. And possibly also explain what is wrong with my current one so I can learn from my mistakes.

{EDIT}

Reworked version:

DECLARE @day as datetime2(2) = N'20141007'
DECLARE @dayStart as datetime2(2) = CONCAT(@day,N' 00:00:00.000')
DECLARE @dayEnd as datetime2(2) = CONCAT(@day,N' 23:59:59.999')

SELECT
   messageId, time, direction, hasRouting, deviceType,
   unitId, accountCode, clientId, data
FROM
   Logs
INNER JOIN
   Acks ON Logs.messageId = Acks.messageId
WHERE
   (Acks.direction = 0)
   AND (Acks.isNack = 0)
   AND (Logs.unitId LIKE 'DEVMB1')
   AND (Logs.direction = 1)
   AND (Logs.time BETWEEN @dayStart AND @dayEnd)
   AND (Acks.time BETWEEN @dayStart AND @dayEnd)
ORDER BY
   Logs.time ASC

Added execution plan for reworked query:

Execution Plan


texts: MySQL Count rows from another table for each record in table

texts:
SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID

I am trying to count the last row but instead it counts all the results and returns one result

I am getting something like

as there are multiple records because there are two entries in Attendance ID for K1052280 I want to count those and return the number. Something like


texts: Migrate from SQLite to MySQL (then back)

texts:
For various reasons, I must use a SQLite database with a local application. However, I wrote a Flask app that enables users to more easily classify items in the database, and it runs very slow on pythonanywhere hosting (which I believe is hosting its services on AWS) because of the SQLite. They support MySQL databases, and I believe it would be a significant performance boost for my users to migrate the data to MySQL. However, I need to then pull the data back out of MySQL and write it back into SQLite to work with the local applications. My concern is that the BLOB data would get messed up by creating a dump script (it's binary pickled data that contains Python data structures the application works with once unpickled).

Currently updating the local copy is as easy as downloading the SQLite database from the server. What is an optimal way to migrate this data to MySQL in such a way that it will be easy to migrate it back to SQLite? Should I?


texts: When is should/shouldn't the Entity Attribute Value (EAV) pattern be used?

texts:
Possible Duplicate:
  Is there a name for this database structure?

The Entity Attribute Value (EAV) pattern usually has three columns: foreign key, attribute, and value. Attributes of data are stored in rows instead of columns. This avoids the need to add a new column to a table whenever a new attribute needs to be stored.

Though I often see the EAV pattern, I rarely feel like it was a good choice.

When is the EAV pattern the correct solution? When is it the wrong solution?


texts: Way to set all queries to run through SSEM to READ UNCOMMITTED?

texts:
We have a hosted application where clients do not get SQL Access.  We have a new client that needs access to SSMS to write queries for reports.  We are installing SSMS on a utility box for the client to log into and use.  The clients accounts will be in the data_reader group.  I want to force all queries to run READ UNCOMMITTED
So not to cause blocking of the application with badly written queries


texts: How Plan Explorer Group Operators in Query Columns Tab

texts:
The two Index Seek (highlighted in green and red) are for filtering, they did not group in Query Columns tab. But the two Object3 highlighted in red are grouped together instead. Here is the XML Plan.

Does anyone has idea about why PE group the two Object3 highlighted in red and leave the Object3 highlighted in green in another group. I'm sorry asking such a stupid question, because I am a newbie in SQL tuning.

Here is the "Query Columns" tab I am talking about. Plan Explorer grouped the Index Seek and Index Scan. Actually, Plan Explorer will group Index Seek/Scan and Key/RID Lookup together. In this case, I understand the purpose. It help us considering adding the lookup column as an INCLUDE column to an index.

But I don't understand the purpose behind why PE grouped the highlighted Index Seek and Index Scan together!


texts: Do Sql Server backups contain transacitons during backup?

texts:
I have a database that automatically takes full backup weekly, differential backup daily, and transaction log backup every 5 minutes. The full backup takes about half an hour so its runtime often overlaps with the differential backup and any transaction log backups happens during.
What happens to the data modified during each types of backup? Would they be included in the backup or the backup contains only data up to the point of BackupStartDate or other attributes in the backup? I want to be able to calculate when trying to restore to a point of time.
If the Diff backup occurs after a full backup process has started and finishes after the full backup has finished, would it base on the last full backup or this one?
I haven't found a authoratative explanation on the web. Appreciate if anyone can point me to the right direction.


texts: Problems while building plpgsql_check on Windows

texts:
I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, I got following errors:

error LNK2001: unresolved external symbol _exec_get_datum_type
error LNK2001: unresolved external symbol _plpgsql_compile
error LNK2001: unresolved external symbol _plpgsql_build_datatype
error LNK2001: unresolved external symbol _plpgsql_stmt_typename
error LNK2001: unresolved external symbol _plpgsql_parser_setup

In addition, there are two IntelliSense errors: expected a ')' in plpgsql.h (lines 923 and 953 - both in datum-related functions) - I expect these to be false alerts (if they are important, how to solve them?).

After some googling I found that someone had a similar problem on OS X. The problem was that plpgsql.so either wasn't linked or didn't work. I have no plpgsql.so on my computer, but I have plpgsql.dll which should correspond to it. So I linked it and the error changed:

error LNK1107: invalid or corrupt file: cannot read at 0x2A8

This dynamic library is part of the PostgreSQL distribution, so it shouldn't be corrupt. However, I'm afraid that what Pavel wrote in the linked discussion applies here:

  It needs link against plpgsql.so. I didn't test it against clang - I
  remember a talk with Tom Lane, he was surprised so this code works and
  probably on some platforms can be unavailable.

  ...

  Nobody expected so plpgsql will be used from other extensions - and
  environment is not prepared for it :(

The worst case would be that plpgsql checker extension is simply not available under Windows, because it is too low-mana area for such a mighty magic. :-) If I just need a different approach, please point me to it.


texts: Count number of occurrences of a phrase in ntext

texts:
I am trying to create a function for counting the number of occurrences of a phrase in a ntext value.

This my function's code:

alter FUNCTION [dbo].[PhraseCounter] ( @pInput NTEXT, @pSearchPhrase NVARCHAR(100) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = DataLength(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
    IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchPhrase
        SET @vCount = @vCount + 1

    SET @vIndex = @vIndex + 1
END

RETURN @vCount

END

But it can't find anything in the input string.

Where is the problem in my function?


texts: What is the data type of the ‘ctid’ system column in Postgres?

texts:
The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns.

That page mentions that oid values “are 32-bit quantities”. And that page says the same about transaction identifiers. So I will assume that means oid, tableoid, xmin, cmin, xmax, and cmax are all 32-bit integers.

But that leaves the ctid system column.

  The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

➡ What is the data type of the ctid column?

Specifically I am interested in version Postgres 10.3, but if it has changed over past versions, that would be good to know.


texts: SQL Server Authentication vs Windows Authentication

texts:
Good Afternoon,

I've been working on a problem where connecting to a remote default SQL instance (using the server's IP address) via Windows Authentication has been taking a lot longer than SQL Authentication.

Originally I thought that the problem was down to reverse DNS lookups but now I am not too sure. There was a record in the forward lookup zone for the server so connections using the server's name were quick. Once a record for the server was entered into the reverse lookup zone, the connections using the server's IP address were much faster.

So the issue appears to be fixed but I'm not so sure. Why wouldn't SQL authenticated connections have a similar problem? Do SQL authenticated connections not resolve against DNS?

Does anyone have any links to documentation on the internals of the two types of connections? I've been doing some research but have come up short.

Any links or advice would most appreciated.

Andrew


texts: Is there any performance gain due to not setting a default for a new column in Mysql?

texts: So, I need to add a new column of type tinyint to a database table with millions of records. Possible values for the column are 1 or 0. The default value I would want is 1. But, would setting a default value affect the database performance while the alter table query is running? Or is it better not to set any default value and then backfill by writing a script that backfills the column in batches?


texts: Handling INSERT race condition for generated unique column value

texts:
I am generating a unique slug during creation of a record like so (based in this answer)

INSERT INTO ks.shares (
    , slugbase
    , slugindex
    , slug
    , userid
)
SELECT
    , Pslugbase
    , COALESCE((SELECT(max(slugindex) + 1)
            FROM  ks.shares s
            WHERE s.slugbase = Pslugbase), 0)
    , Pslugbase
        || COALESCE((SELECT '-'::text || (max(slugindex) + 1)::text
            FROM  ks.shares s
            WHERE s.slugbase = Pslugbase), '')
    , Puserid
RETURNING id, modified, slug INTO Vnewshare;

When testing under load this can raise a unique_violation as two concurrent threads both assess what it he current slug index before one inserts a record with the next available slug, leaving the second thread with a now invalid slug.

I attempted to solve the issue by LOOPING over the insert and catching and unique constraint violations:

LOOP
BEGIN
    INSERT INTO ks.shares (
        , slugbase
        , slugindex
        , slug
        , userid
    )
    SELECT
        , Pslugbase
        , COALESCE((SELECT(max(slugindex) + 1)
                FROM  ks.shares s
                WHERE s.slugbase = Pslugbase), 0)
        , Pslugbase
            || COALESCE((SELECT '-'::text || (max(slugindex) + 1)::text
                FROM  ks.shares s
                WHERE s.slugbase = Pslugbase), '')
        , Puserid
    RETURNING id, modified, slug INTO Vnewshare;

    -- INSERT into two other tables

    RETURN json_build_object (
        'data',json_build_object (
            'id',lpad(Vnewshare.id::text,public.padding_constant(),'0'),
            'modified',Vnewshare.modified,
            'slug',Vnewshare.slug
        )
    );
    EXCEPTION WHEN unique_violation THEN
        -- do nothing, and loop to try the INSERT again
END;
END LOOP;

(I took this pattern from an answer on implementing UPSERT but can't find it!)

However, when I deployed this function my DB instance got stuck twice running long queries that involved this function being called concurrently, so I seemed to have somehow replaced a race condition with a deadlock or an infinite loop

pid  |    duration     |                                      query                                      | state
------+-----------------+---------------------------------------------------------------------------------+--------
8187 | 01:47:35.477316 | select ks.post_share($1::text,$2::text,$3::bigint) | active
1188 | 01:57:56.955747 | select ks.post_share($1::text,$2::text,$3::bigint) | active
(2 rows)

Is the approach to use a LOOP here the wrong way to go?
Is there any way to understand what is causing the query to get stuck? It does not happen during load testing but appeared twice on production (which seems counter intuitive)
Should I just return the conflict to the calling process and let the application handle retry?

Any thoughts would be greatly appreciated.


texts: Tool to administer Postgres user rights?

texts:
I think it is incredibly tedious to administer user rights in Postgres. If I for example want to give editing access to an updatable view, then I need to ensure that the user has the right accesses to, the database, the schema, the underlying table, the sequences used (for inserts), all the columns. So often something goes wrong, also due to my lack of understanding.

However, I don't really see any need to have a deep understanding of this. I can imagine a tool where I could just grant access to the view and the tool would somehow cascade (is that the right expression) the user rights to all needed objects. I understand that this could get difficult to do if giving access to, say, a pl/pgsql function, which touches 100s of objects under various conditions, but for basic objects like views, tables, etc. this should really save me a lot of time.

Please tell me if this question should have been in the softwarerecs StackExchange, I felt that this was the right place, since it is so specific to postgres and, I believe, of general interest.


texts: Implementing Pub/Sub initiated from the database

texts:
I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is adding unnecessary load to my SQL Server 2008 databases. I'd like to implement something akin to a publish & subscribe pattern where the database; through some mechanism; publishes messages to indicate when certain state changes occur.

This brings me to my question... What mechanisms reside within SQL Server 2008 natively; if any; which could support this pattern?

I’m open to other ideas, assuming I can get away from blind polling. As an extra bit of context, the message will need to carry only basic information to describe the nature of the change and what; by id; has changed.

Note: I’ve considered using SSIS packages and triggers but don’t think it’s appropriate, especially during times when the change rate is higher.


texts: PostgreSQL use NEW in query for INSTEAD OF trigger

texts:
I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario:

CREATE TABLE Product (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR
);
CREATE TABLE Purchase (
  purchase_id SERIAL PRIMARY KEY,
  product_id INT REFERENCES Product,
  when_bought DATE
);

CREATE VIEW PurchaseView AS
SELECT purchase_id, product_name, when_bought
FROM Purchase LEFT JOIN Product USING (product_id);

I'd like to be able to create INSTEAD OF triggers to allow me to insert directly into PurchaseView, e.g.:

INSERT INTO Product(product_name) VALUES ('foo');
INSERT INTO PurchaseView(product_name, when_bought) VALUES ('foo', NOW());

What I had in mind was something along the lines of:

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
BEGIN
  INSERT INTO Purchase(product_id, when_bought)
  SELECT product_id, when_bought
  FROM NEW
  LEFT JOIN Product USING (product_name)
  RETURNING * INTO NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
  INSTEAD OF INSERT
  ON PurchaseView
  FOR EACH ROW
  EXECUTE PROCEDURE insert_purchaseview_func();

However the above trigger function gives errors (relation "new" does not exist) when executed. I know I can write queries that explicitly use attributes from NEW in the WHERE and SELECT clauses, but sometimes it would be convenient to be able to include NEW in a join. Is there a way to do this?

Current (unsatisfactory) solution

The closest I can get to what I want is

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
  RETURNS trigger AS
$BODY$
DECLARE
tmp RECORD;
BEGIN
    WITH input (product_name, when_bought) as (
       values (NEW.product_name, NEW.when_bought)
    )
    INSERT INTO Purchase(product_id, when_bought)
    SELECT product_id, when_bought
    FROM input
    LEFT JOIN Product USING (product_name)
    RETURNING * INTO tmp;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

This is a little unsatisfactory for several reasons:

I need to explicitly write all the attributes of NEW in CTE WITH query, which for large views (especially those whose attributes are automatically determined with SELECT *) gets unwieldy;
The returned result doesn't have the SERIAL type product_id updated, so you don't get the expected result for:

INSERT INTO PurchaseView(product_name, when_bought)
VALUES ('foo', NOW())
RETURNING *;


texts: Monitoring real-time statistics on PostgreSQL

texts:
I need to monitor the DB performance on a distributed system with multiple databases. Some of the database servers use pgbouncer to pool connections and in those servers I can connect to pgbouncer and execute a SHOW STATS; command to retrieve certain statistics, I'm interested in the following:

 avg_req | avg_recv | avg_sent | avg_query
------------------------------------------
     563 |  1080453 |  2060908 |     57718

The columns means:

avg_req
Average requests per second in last stat period.
avg_recv
Average received (from clients) bytes per second.
avg_sent
Average sent (to clients) bytes per second.
avg_query
Average query duration in microseconds.

Now my question is, is there a way of gathering this information without using pgbouncer on a postgresql server?


texts: Pros/Cons of using multiple databases vs using a single database

texts:
I was working on a new project which has the requirement to use 7 databases, arguing that performance, stability, optimization are more easily implemented.

While I don't agree, I'm having trouble collecting good arguments to use a single database (splitting the tables into logical domains).

One argument I have so far is data integrity (I can't use foreign keys between databases).

What are good pros/cons for using a single or multiple databases?

[summary so far]

Arguments against multiple databases:

Losing data integrity (can't use foreign keys over databases)
Losing restore integrity
Gaining complexity (db users/roles)
Small odds server/database will go down

Solutions:

Use schemas to separate domains.
POC: Use dummy data to prove the point in 7/1 db's execution plans


texts: Merge Replication From Multiple Express to Central

texts:
So I am trying to setup a merge replication from multiple remote SQL 2016 Express installations to a central SQL 2016 Server. This would be for reporting purposes so I would need all the express installations to report information to the central server daily.

The trouble I am having is that the main server is setup as the distributor and publisher (publisher is a blank database with the 41 tables we are wanting to report information into), and the express installs are all subscribers. Is this even possible and if it is, can someone point me in the right direction for documentation as I am not finding any for this purpose.

Thanks for all help!


texts: Cursor Based Pagination for Timestamp field with non-unique values

texts:
We are trying to create a query for the following table :

 CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(60) NOT NULL,
  `assigned_To` varchar(40) DEFAULT NULL,
  `updated_At` datetime(3) NOT NULL,
  `completed_At` datetime(3) DEFAULT NULL,
  `deleted_At` datetime(3) DEFAULT NULL,
  `created_At` datetime(3) NOT NULL,
  `deadline` datetime(3) NOT NULL,
  `notes` varchar(400) DEFAULT NULL,
  `completed` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `action_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `action_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1

We want to show the Actions using pagination based on deadline field in Ascending order. But deadline can have same values and is creating problem for us to implement cursor based pagination on deadline field.

Action can be created for any future date. We thought of using created_At and deadline but it fails as we can  create Action on 22 Nov with deadline 25 Nov and another action on 23 Nov with deadline 24 Nov. I mean we there is no relationship between two of them. and exactly similar thing happens with the id and deadine column.

How can I tackle this problem of showing Actions using pagination with deadline in ASC order.


texts: SQL Linked Server Passing Date Parameters

texts:
I'm using SQL Server 2008 R2 and have a linked SQL 2012 Express database.  On the 2008 box I am creating a stored procedure to be used in a report.  I am wanting to pass a date variable so the user can enter a to and from date in reporting services.  However, I have been trying to get the openquery parameter code correct and it's just not happening.  Here is my code:

@Custvar VARCHAR(1000),
@fromperiod datetime,
@toperiod datetime

@fromperiod varchar(8),
@toperiod varchar(8)

AS

SET NOCOUNT ON

DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000)

DECLARE @fromperiodvar VARCHAR(8)
DECLARE @toperiodvar VARCHAR(8)

--SET @fromperiodvar=(CONVERT(CHAR(8), @fromperiod, 112))
--SET @toperiodvar=(CONVERT(CHAR(8), @toperiod, 112))

SELECT * FROM OPENQUERY
([ServerName\SQLEXPRESS],'
SELECT
    (DateDiff(DAY ,otrs.dbo.ticket.create_time , otrs.dbo.ticket.change_time) ) AS Open_to_close_days_duration ,
    otrs.dbo.ticket.create_time ,
    otrs.dbo.ticket.change_time,
    otrs.dbo.queue.name
FROM
    otrs.dbo.ticket
    INNER JOIN otrs.dbo.queue
        ON otrs.dbo.ticket.queue_id = otrs.dbo.queue.id
WHERE
    ((((DateDiff(DAY ,otrs.dbo.ticket.create_time ,otrs.dbo.ticket.change_time))) > 0)
        AND((otrs.dbo.ticket.ticket_state_id) = 3 OR(otrs.dbo.ticket.ticket_state_id) = 2 OR(otrs.dbo.ticket.ticket_state_id) = 10))
        AND otrs.dbo.ticket.create_time >= ''''+ @fromperiodvar + '''' and otrs.dbo.ticket.create_time <=''''+ @toperiodvar + ''''
ORDER BY
    otrs.dbo.ticket.queue_id;
')
GO

So where am I going wrong?  I am unable to have SQL prompt me for the to and from dates?

Any help would be greatly appreciated.

Regards,

Michael


texts: What are the pros and cons of using Postgres over SQLite for tiny and small projects?

texts:
I'm a fullstack junior programmer with a few side projects, every with a few tables and a few hundred or thousand rows at max.
I like to use a dockerized Postgres even for tiny projects, since it's more fully-featured (from small things such as the built-in regexp function, which SQLite does not have, to bigger, such as Row Based Access Control - all of which I can do without, but they are convenient) and my knowledge will carry over to bigger projects. Its memory footprint seems to be pretty manageable even for 1GB VMs as well, as the Postgres container seems to consume around 100/200 MBs at max, therefore, at least as far as my reason goes, it's definitely not a problem resource-wise.
SQLite, on the other hand, obviously uses even less resources and it seems to be heavily implied it should be used for such tiny projects for other reasons I'm not fully aware of.
What would be the potential reasons to use SQLite over Postgres for small projects?


texts: DBCC DBINFO incorrect dbi_dbccLastKnownGood

texts:
I'm trying to determine why DBCC DBINFO is returning different results than what is contained within the error log. Here's the setup:

Secondary Replica (read only access -- Asynchronous Commit AlwaysOn HA AG)

Job ran at at 02/05/2018 09:00:00 PM successfully, with a duration of 02:22:21
Only step in the job is DBCC CHECKDB(myDB)
When I run DBCC DBINFO('myDB') WITH TABLERESULTS I see dbi_dbccLastKnownGood  02/05/2018 00:00:40.113. This should be 02/05/2018 11:22 PM.
When I check the error log, I see 02/05/2018 11:22:09 PM DBCC CHECKDB(myDB) executed by myAccount found 0 errors and repaired 0 errors. Elapsed time : 2 hours 22 minutes 9 seconds. Internal database snapshot has split point LSN ... and first LSN ...

I decided to check the PRIMARY REPLICA where DBCC CHECKDB is also running (this is all set up by the vendor).

Subplan ran at 02/05/2018 00:00:00 AM successfully, with a duration of 02:14:40
When I run DBCC DBINFO('myDB') WITH TABLERESULTS I see dbi_dbccLastKnownGood  02/05/2018 00:00:40.113 which is the same as the RoR but it should be 02/05/2018 02:15 AM.
When I check the error log, I see 02/05/2018 02:15:12 AM DBCC CHECKDB(myDB) WITH no_infomsgs exextued by myAccount found 0 error and repaired 0 errors. Elapsed time : 2 hours 14 minutes 40 seconds. Internal database snapshot has split point LSN ... and first LSN ...

I can't figure out where this DBCC DBINFO results is coming from, and why it doesn't match the SQL logs. I set up a job to send me an alert when the dbi_dbccLastKnownGood is > 24 hours.

Any ideas? Let me know if more information is needed.


texts: Create a "barrier" (prevent parallel execution) in Oracle PL/SQL?

texts:
I'm maintaining some stored procedures in an Oracle 11g RAC environment that are part of the pipeline of steps for data-interchange with other systems.  One in particular has been producing duplicate records.  Files get loaded into a set of staging-tables by an ETL job,  which then calls this stored procedure, which has no parameters (except an OUT parameter that gets ignored by the calling ETL code,  but that's another issue).  The body of the procedure starts with a statement like

For I in ( SELECT * from  SOME_STAGING_TABLE where status = 'NEW' order by X_ID) LOOP

However, the ETL job that calls this is in turn called by another ETL job that finds all "files" that haven't been processed and calls it, once per file, in parallel.  I think what's happening is:

Master ETL job runs, finds two files,  invokes two worker jobs
Worker ETL job A runs, gets through first phase
Worker ETL job B runs, gets through first phase
Worker ETL job A invokes stored procedure
Stored procedure A' gets two files' worth of records to process
Worker ETL job B invokes stored procedure
Stored procedure B' gets two files' worth of records to process

One solution might be to enforce serialization in the ETL service.  Another might be to change the calling-convention of the stored procedure so it takes a file ID as a parmeter.  However,  let's say I need to do it in the database.  Is there any way to set up a stored procedure so that only one connection can be executing a designated block of code inside it at a time?

(In C or Java this would be called a "mutex" or "synchronized block", and I know MySQL has a "LOCK TABLES" statement that could be used for that purpose;  does Oracle 11g have anything like that?)


texts: Can I disable binlogs to save space temporarily

texts:
I am using percona mysql 8.
I do not use any type of replication, but I read that binlogs are useful for data recovery. I would like to turn of binlogging and flush them while I run pt-online-schema-change to do non-impacting OPTIMIZE table.
After it is done, I want to turn binlogging back on (and then make efforts to move to a server with more space).
Is this safe and recommended? I need to optimize a table and can't go offline and making a copy of the table will make me run out of space unless I remove the 50GB of binlogs


texts: High tempdb disk I/O during merge replication of BLOBs

texts:
Having a merge publication for replicating BLOBs (type image), got very high tempdb disk I/O for my size of data. Publication is download-only and has no filters.

High disk I/O is caused by synchronization (when no subscribers are synchronizing, everything is ok), strongly correlated with number of subscribers. It happens even when no data is changed at Publisher between synchronizations, and that bothers me.

Size of replicated table: 7MB (total count of rows is about 100)
tempdb I/O : ~30 MB/sec for write (log and data files)
Number of subscribers: slightly over 100, each synchronizing every 30
minutes (more or less evenly).
Retention period set to 14 days

Using SQL Server 2008 at Publisher, SQL Server 2005-2008R2 at Subscribers.
All subscribers use Web Synchronization.

Additionally, synchronization at subscriber takes a lot of time, with multiple occurrences in replmerg.log like these:

DatabaseReconciler, 2015/04/21 12:13:40.348, 3604, 25088,  S2,
INFO: [WEBSYNC_PROTOCOL]
Sending client ReconcilerPhase WebSyncReconcilerPhase_RegularDownload

DatabaseReconciler, 2015/04/21 12:13:47.063, 3604, 25194,  S2,
INFO: [WEBSYNC_PROTOCOL]
Received server ReconcilerPhase WebSyncReconcilerPhase_LastRegularDownload

Tried setting @stream_blob_columns on and off with no effect.

The question is: Is it a good idea to use merge replication to send these blobs to subscribers? We have other publications (though they have no BLOB columns) with a lot of data without tempdb problem. Is it an SQL Server flaw, or bad setup?

Publisher and Distributor are on the same instance, SQL Server 2008 SP4, cannot be sure about Subscribers, some of them maybe not up-to-date. Anyway, I can prepare a test environment with few subscribers having controlled versions, if it seems to help.

Confirmed, that excessive tempdb usage caused by execution of sys.sp_MSenumgenerations90. Checked MSMerge_genhistory table, found over 1.2 millions of records where pubid is null.

Found this conversation with replication guru:

Executed sp_mergemetadataretentioncleanup with no effect.

Found a remark on a case like this (too much rows in MSmerge_genhistory) where deleting rows where pubid is null and genstatus=1 helped to fix replication.

Deleted rows where pubid is null (implying that all Subscribers are synchronized, and which are not - will be reinitialized in "manual mode") and disk IO is back to normal again!

I have a feeling, that this situation could be caused by the fact, that all of my Subscribers are anonymous via WebSync and most of them have the same hostname. I'll try to check, if -hostname key helps not to multiply records in MSmerge_genhistory.


texts: Can't drop table after creating table with wrong engine

texts:
I'm trying to drop a table containing several hundred thousand column-based records. Normally when creating the database I use a column-based engine (infinidb) but in this case I forgot to include the ENGINE statement. So the database is pretty much unusable for my needs. Now I have a database full of tables that are taking forever to drop (it's been twenty minutes and nothing has happened). I tried the ALTER TABLE table ENGINE=INFINIDB command but again, it's taking forever (see above re: twenty minutes).

Is there another way to get rid of this database? E.g. manually going into the /mysql/ directory and deleting the database? I guess I could just rename it and leave it, but I'd rather get rid of it entirely so it's not taking up space.


texts: Why some files in data directory of a Postgresql cluster are symlink to files of another cluster?

texts:
I did use pg_upgrade.
I had a cluster of Postgresql with version 9.6 on my machine, and then I did start another cluster with version of v12. Each of them had separate data directories, in a directory scheme like this:
./postgresql/
├── 12
│   └── main
└── 9.6
    └── main

Then I turned off the 9.6 cluster and started using the v12 one. But now, by accident, I found out that some of the files in the base directory of the v12 cluster are not files, but symlinks to files in base directory of the 9.6 one; For example:
./postgresql/12/main/base/16401/35093 -> ./postgresql/9.6/main/base/16402/35093

Why is it so? And, more importantly, how is it so?! I mean, I thought that the files inside base directory are so internal to each DB that this could not be possible at all.
And, can I do something to disable using symlinks?


texts: Strategy for standby reporting with different database versions

texts:
I am trying to come up with solution for restoring database with one full backup file and then with hourly transaction logs that are going to be created each hour thru FTP site.
So, here is a scenario.
I connect to data provider's FTP site, and get a full backup file first and restore database with 24 transaction logs on a daily basis. So, I will do a nightly run (around 12 am) to update all transaction logs from previous day's data.
I learned that there is an option called Standby, so I tried full back up with standby, but it appears that I cannot do this because database needs to be upgraded. I got this error when I tried Restore Full backup with "Standby" mode:

This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY confirms that. Now, as standby option is not an option, what should be the way to read data from the database if we are trying to continue doing the transaction log restore everyday (with 24 files)?
I am coming back to the way of restoring database using NORECOVERY for full backup and then for all other transaction log files (because with RECOVERY mode, I cannot restore additional ones later).
But, I need to access database to read data on a regular basis and to query the data. I am not sure if there is a way to read the data (during work hour) when we try to update database with transaction logs. It is not like we could change from RECOVERY mode during work hours to NORECOVERY mode during nightly batch, correct? Do I have to set up like two separate databases (one for restore & other one to somehow grab data from other database). I am not sure that is possible.
What should I do?
Basically, we need to get the updated data from other company thru FTP. After we get the data, developer needs to use it to create reports.


texts: How can I select the last edited version of the post?

texts:
I have a question and answer website like SO. Also I have a table which contains both the questions and answers and their edited version. Here is my table structure:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 1  | title1  | question content          | NULL    | 0    | NULL      |
| 2  |         | answer content            | 1       | 1    | NULL      |
| 3  | title2  | question content          | NULL    | 0    | NULL      |
| 4  |         | answer content            | 3       | 1    | NULL      |
| 5  |         | answer content            | 1       | 1    | NULL      |
| 6  |         | answer content (edited)   | NULL    | 1    | 2         |
| 7  | title3  | question content          | NULL    | 0    | NULL      |
| 8  | title1  | question content (edited) | NULL    | 0    | 1         |
| 9  |         | answer content            | 7       | 1    | NULL      |
| 10 | title1  | question content (edited) | NULL    | 0    | 1         |
| 11 | title3  | question content (edited) | NULL    | 0    | 7         |
+----+---------+---------------------------+---------+------+-----------+

Column explanations:

related column:

NULL for both questions and edited version of questions/answers
{the id of its own question} for answers

type column:

0 for questions
1 for answers

edited_id column: (the id of original post)

NULL means it is a original question/answer
{any number} means it is a edited version of a question/answer.

Now I need a query to select a question and all its answers. Noted that I need to select the last edited version of them (if they have been edited).

Example1: I have this value: :id = 1 and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 10 | title1  | question content (edited) | NULL    | 0    | 1         |
| 6  |         | answer content (edited)   | NULL    | 1    | 2         |
| 5  |         | answer content            | 1       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Example2: I have this value: :id = 3 and I want this output:

+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 3  | title2  | question content          | NULL    | 0    | NULL      |
| 4  |         | answer content            | 3       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Example2: I have this value: :id = 7 and I want this output:

// QandA
+----+---------+---------------------------+---------+------+-----------+
| id |  title  |            body           | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 11 | title3  | question content (edited) | NULL    | 0    | 7         |
| 9  |         | answer content            | 7       | 1    | NULL      |
+----+---------+---------------------------+---------+------+-----------+

Here is my current query:

SELECT *
FROM QandA
WHERE (id = :id AND type = 0) OR
      (related = :id AND type = 1)
ORDER BY type -- noted that the order of answers doesn't matter

As you see, my query doesn't support edited version. Anyway, how can I replace edited version of posts when there is a edited row of that post?

Note: Please don't tell me "don't keep both the questions and answers in the same table", Because I know it. But now I need to solve problem above.


texts: Database design scenario for budget (whole and daily)

texts:
Scenario: Each user add funds to his account, for example 100 USD, and he defines that the system (our website) could spend up-to 10 USD per day for showing his ads in the website. each time his ad is shown in or website, it's gonna cost him 1 USD.

P.S: we're not inserting a record into the database every time his ad is shown, we just deduct the amount for showing his ad (1 USD) from his whole budget.

because we're showing too many ads, it's not possible to insert a record for each ad show into the database.

I have 2 columns in users table, whole_budget and daily_budget, so for the above user whole_budget is 100 and daily_budget is 10.

in the above scenario, how can we manage daily budget? so when an ad is shown, how we're gonna deduct 1 USD from his account?

how can we know if he has left daily budget? so we can decide to show his ad for the rest of the day or not.

I would appreciate your help

EDIT ---------------

This could be simply achieved by having a table called shown_ads, and every time we show an ad, we insert a record in this table with the respected DATE() so we can later on calculate the amount of his budget already spent today. but imagine we're showing 1M ads each day, so what I believe is that it's not good to insert 1M records every day! It's just a case-study, I want to know how would you implement this.


texts: How do you connect to SQL Server 2008 Express when SA is disabled?

texts:
I'm trying to create a database in SQL Server 2008 Express (as installed by Visual Studio) but I don't seem to have any permissions to do so. There must be a way to do this, or there would be no point in VS.NET installing it.

Internet articles suggest adding my domain login to the sysadmin role but I'm failing at the first hurdle:

C:\>sqlcmd -S .\sqlexpress
Sqlcmd: '-S': Unknown Option. Enter '-?' for help.

OK... so I then run with "-?" to get help and it says:

C:\>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 10.0.5500.0 NT x64

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]

Notice how -S is a valid option! I don't understand why the help text doesn't seem to match up with the error text. Is -S an option or not? And if not, what is the command line to specify the server name?


texts: MySQL Transform multiple rows into a single row in same table (reduce by merge group by)

texts:
Hy, i want reduce my table and updating himself (group and sum some columns, and delete rows)
Source table "table_test" :
+----+-----+-------+----------------+
| id | qty | user  | isNeedGrouping |
+----+-----+-------+----------------+
|  1 |   2 | userA |              1 | <- row to group + user A
|  2 |   3 | userB |              0 |
|  3 |   5 | userA |              0 |
|  4 |  30 | userA |              1 | <- row to group + user A
|  5 |   8 | userA |              1 | <- row to group + user A
|  6 |   6 | userA |              0 |
+----+-----+-------+----------------+

Wanted table : (Obtained by)
DROP TABLE table_test_grouped;
SET @increment = 2;
CREATE TABLE table_test_grouped
SELECT id, SUM(qty) AS qty, user, isNeedGrouping
FROM table_test
GROUP BY user, IF(isNeedGrouping = 1, isNeedGrouping, @increment := @increment + 1);
SELECT * FROM table_test_grouped;

+----+------+-------+----------------+
| id | qty  | user  | isNeedGrouping |
+----+------+-------+----------------+
|  1 |   40 | userA |              1 | <- rows grouped + user A
|  3 |    5 | userA |              0 |
|  6 |    6 | userA |              0 |
|  2 |    3 | userB |              0 |
+----+------+-------+----------------+

Problem : i can use another (temporary) table, but i want update initial table, for :

grouping by user and sum qty
replace/merge rows into only one by group

The result must be a reduce of initial table, group by user, and qty summed.
And it's a minimal exemple, and i don't want full replace inital from table_test_grouped, beacause in my case, i have another colum (isNeedGrouping) for decide if y group or not.
For flagged rows "isNeedGrouping", i need grouping.
For this exemple, a way to do is sequentialy to :
CREATE TABLE table_test_grouped SELECT id, SUM(qty) AS qty, user, isNeedGrouping FROM table_test WHERE isNeedGrouping = 1 GROUP BY user ;
DELETE FROM table_test WHERE isNeedGrouping = 1 ;
INSERT INTO table_test SELECT * FROM table_test_grouped ;

Any suggestion for a simpler way?


texts: What is the difference between COUNT(*) AND COUNT([attribute]) in the following scenario?

texts:
I have the following tables :

DEPARTMENT

department_id
department_name
manager_id
location_id

and

EMPLOYEES

employee_id
first_name
last_name
email
phone_number
hire_date
job_id
salary
commission_pct
manager_id
department_id

I want to count the number of employees working in each department.

I have used the following query :

SELECT department_name, count(*)
FROM departments d LEFT OUTER JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name;

But in the results the employee which has no employees does not show up in the results. I want the number of employees to show up as 0. How do I do that?


texts:
I need to clean up space and do a full backup of database.
Old backups are safe on tape.

My backups are on 2 specifics directories (2 channels).

So I did:

DELETE EXPIRED BACKUP;
DELETE OBSOLETE;
DELETE BACKUP;
DELETE DATAFILECOPY ALL;

Next LIST BACKUP did not found nothing.

Still, in my directories I have about 4 TB of data. Database is 8 TB.

File has names like:

20160226historyotqusdq7_1_1.bck
20151105historydata_D-HISTDB_I-3526265592_TS-history_2013_FNO-186_pmqlg4jl.bck

And so on, some hundreds of files.
HISTDB is the sid, history_2013 is one of the tablespaces.

How can I indentify what that files are and clean them in a safe way?

Oracle is version 11.2.0.2 on redhat linux, kernel version 2.6.18-238.el5


texts: Is it possible to edit the "Missing Index Details" template in SSMS?

texts:
When you turn on "Include Actual Execution Plan" and run a query in SSMS, the resultant plan can include information about missing index(es) that would make the query more performant. In that case, right-clicking the plan and selecting "Missing Index Details..." will script out that missing recommended index to a new window.

I'd like to modify the template for generating the missing index to conform to my organisation's SQL style guide, but I can't see any where or how to edit how that index is generated. I'm aware of SSMS templates which can be tweaked, but can't find any reference to this particular template anywhere. Does anyone know if it's available for customisation, and if so where?


texts: Efficient workflow/queries to save unique information?

texts:
I am saving company information in a database. There is a single table with an auto-incrementing key that is unique to each company. There are separate tables for emails, websites, phone numbers, and company names. When a new company is added to the database, a check should be performed across the various tables to see if unique data already exists.

For instance, if a new phone number, email, and website are added, a check should to be performed to see if they are all already present in the database. If the phone number and email are present (a unique combination), then I want to grab the unique company id for those two entries and add a row to the website table indicating that the website is now associated with that company id.

However, if the phone number and email exist for one company id while the email and website exist for a different company id, I want to merge the two ids into one.

I personally hate this setup, but I can't think of a cleaner way to save all this data. For instance, it's not guaranteed a phone number, email, or website will always be passed. Moreover, some of the data is fairly subjective. I can easily query email addresses and phone numbers from multiple tables:

SELECT id FROM companies LEFT JOIN emails using(id) LEFT JOIN phones using(id) WHERE phone = "123" AND email = "john@smith.com"

However comparing organization names, a WHERE query won't return correct results. I wrote a function in PHP to compare businesses names using similar_text and soundex, but those can't easily be incorporated into a MySQL query. Similarly, one check I have in place to differentiate businesses is a longitude/latitude distance check. Consequently, my envisioned workflow (as of now) is:

Loop through each unsaved company profile in PHP
Get all saved, unique information from the database:

SELECT id, email, position, address, zip, organization_name, phone, website FROM companies left join emails using(id) left join locations using(id) left join organization_names using(id) left join phones using(id) left join websites using(id)
In PHP, loop through each row, attempting to find a match between the saved values and unsaved value.
If a unique entry exists, update the database. If a unique entry does not exist, add the appropriate rows to the database.

I can see this being incredibly inefficient given the number of companies saved in the database, which is why I am here asking for help. Database design is not my forte, so any advice is appreciate.


texts: For SQL Server 2012 where is better way to restrict rows (WHERE or JOIN)?

texts:
I still don't get it - is it better for performance to restrict data inside WHERE clause or inside JOIN clause. Which is the better way? I've read a long time ago that there is no difference, but I am still not sure. I am speaking about INNER JOIN - in which case the result is the same.

EXAMPLE:

SELECT * FROM A JOIN B ON (A.X = B.Y) WHERE (A.Z = 5);

vs

SELECT * FROM A JOIN B ON (A.X = B.Y) AND (A.Z = 5);


texts: Redirect all sysmail emails to one address for testing purposes

texts:
I want to test sending emails from multiple stored procedures but redirect them all to one email instead of spamming real users.

Is it possible to configure sysmail to only send email to one address, whatever the specified recipients?


texts: How can I write results to SAME results set?

texts:
I have automatically created a script with lots (15K) of statements.

How can I get the results to all be in the same results set, temp table or file?

I want to be able to look at the results easily:

select top 1 [utcDT] from [Mrix].[Pnl].[Specified_Prd_2_DS1M_1] order by [utcDT] desc;
...
select top 1 [utcDT] from [Mrix].[Pnl].[Specified_Prd_2_DS1M_2] order by [utcDT] desc;


texts: How triggers are called when we have two triggers?

texts: In a table I have two triggers. When it is called for the first time the first trigger does not fires while the second trigger's got fired.  On the next call both first and second trigger action take place. So in my case when calling the trigger first time the first trigger should be executed then the second one. Any suggestions?


texts:
Why common schema is not available in MySQL 5.6? What is the significance of it? So If there is a need for common schema for an application, what is the workaround to have common schema in 5.6 or should I go back to 5.5?

Also, Can I download the common_schema from code google and install it in 5.6? Are there any implications as such?


texts: Why is this faster and is it safe to use? (WHERE first letter is in the alphabet)

texts:
Long story short, we're updating small tables of people with values from a very large table of people. In a recent test, this update takes ca 5 minutes to run.

We stumbled upon what seems like the silliest optimization possible, that seemingly works perfectly! The same query now runs in less than 2 minutes and produces the same results, perfectly.

Here is the query. The last line is added as "the optimization". Why the intense decrease in query time? Are we missing something? Could this lead to problems in the future?

UPDATE smallTbl
SET smallTbl.importantValue = largeTbl.importantValue
FROM smallTableOfPeople smallTbl
JOIN largeTableOfPeople largeTbl
    ON largeTbl.birth_date = smallTbl.birthDate
    AND DIFFERENCE(TRIM(smallTbl.last_name),TRIM(largeTbl.last_name)) = 4
    AND DIFFERENCE(TRIM(smallTbl.first_name),TRIM(largeTbl.first_name)) = 4
WHERE smallTbl.importantValue IS NULL
-- The following line is "the optimization"
AND LEFT(TRIM(largeTbl.last_name), 1) IN ('a','à','á','b','c','d','e','è','é','f','g','h','i','j','k','l','m','n','o','ô','ö','p','q','r','s','t','u','ü','v','w','x','y','z','æ','ä','ø','å')

Technical notes: We are aware that the list of letters to test might need a few more letters. We are also aware of the obvious margin for error when using "DIFFERENCE".

Query plan (regular): https://www.brentozar.com/pastetheplan/?id=rypV84y7V
Query plan (with "optimization"): https://www.brentozar.com/pastetheplan/?id=r1aC2my7E


texts: How to get sum of 3 sub count queries and group them using an attribute

texts:
I have 3 queries as follows:

For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries.

Q1

SELECT count(*)
FROM XAXBXC_charge_info_ABC_tbl
WHERE date = '2019-12-02'
AND cg_response_code = 0

Q2=>

SELECT count(*)
FROM XAXBXC_charge_info_ABC_tbl
WHERE date = '2019-12-02'
  AND topup_id = 10
  AND topup_cg_response_code LIKE '0'

Q3=->

SELECT count(*)
FROM XAXBXC_charge_info_ABC_tbl
WHERE date = '2019-12-02'
  AND topup_id = 11
  AND topup_cg_response_code LIKE '0'

I am trying to calculate and display sum as follows,

Date      | Sum
2019-12-02|  234

eg:-

For a particular date, sum should be equal to the sum of Q1+Q2+Q3 and it must be grouped by the date column.

Can someone help me to achieve this? I have been stuck here for while now.


texts: Cannot drop nonexistent constraint and cannot create it either

texts:
While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands:

ALTER TABLE A_DUP_CALLE
DROP CONSTRAINT A_DUP_CALLE_UK1;

ALTER TABLE A_DUP_CALLE
ADD CONSTRAINT A_DUP_CALLE_UK1 UNIQUE (
    CONTROL_ID,
    CALLE_AYTO_DUPL
)
ENABLE;

The DROP command worked fine but the ADD one failed. Now, I'm into a vicious circle. I cannot drop the constraint because it doesn't exist (initial drop worked as expected):

  ORA-02443: Cannot drop constraint  - nonexistent constraint

And I cannot create it because the name already exists:

  ORA-00955: name is already used by an existing object

I type A_DUP_CALLE_UK1 into SQL Developer's Search box and... there it is! Owner, table name, tablescape... everything matches: it isn't a different object with the same name, it is my original constraint. The table appears in the constraint details but the constraint does not appear in the table's details.

My questions:

What's the explanation for this?
How can I ensure it won't happen when I make the real upgrade in live server?

(Server is 10g XE, I don't have enough reputation to create the tag.)


texts:
With SQL Server we can trace for a period of time, to get what queries are running.

How can I do this with Firebird? I'm reading something on their website but there's only user trace like if the login failed or not.

Found something here:

https://stackoverflow.com/questions/30248937/how-to-capture-firebird-sql-queries

Will test it.


texts:
Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that the remote server's open a connection, read/write/update and then close it. Also the remote server reads a lot.

What can I do to reduce my cpu consumption. FYI, I am using 2 core CPU with 4GB RAM.

[client]
port          = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice          = 0

[mysqld]
user          = mysqluser
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port          = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
wait_timeout    = 20
interactive_timeout = 60

bind-address            = <IP-ADDRESS>
key_buffer            = 16M
max_allowed_packet  = 16M
thread_stack            = 192K
thread_cache_size   = 8
myisam-recover      = BACKUP
max_connections     = 300

query_cache_limit   = 20M
query_cache_size        = 128M

log_error = /var/log/mysql/error.log
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 4
log-queries-not-using-indexes

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

Also pasting output from mysqltuner.

>>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 2G (Tables: 26)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 26

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads)
[!!] Maximum possible memory usage: 13.4G (347% of installed RAM)
[!!] Slow queries: 18% (6K/37K)
[OK] Highest usage of available connections: 0% (16/5000)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Query cache efficiency: 20.2% (7K cached / 36K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 25% (54 on disk / 215 total)
[OK] Thread cache hit rate: 99% (16 created / 31K connections)
[OK] Table cache hit rate: 25% (74 open / 289 opened)
[OK] Open file limit used: 0% (49/25K)
[OK] Table locks acquired immediately: 100% (29K immediate / 29K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/2.9G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 2G)


texts: Pattern matching and substring replacement in PostgreSQL

texts:
I would like to do pattern matching and substring replacement in PostgreSQL with a "select-case-when-then-update-query".
Example:

tableA

field1               |field2          | field3
---------------------+----------------+-----------
(varchar)bla abla 123|(varchar)abla   |(varchar)123
(varchar)blub 456    |(varchar)blub   |(varchar)456

remaining string in field1 after performing the substring replacement should be "bla" in row 1 and "blub 456" in row 2 (see Note below):

tableA

field1               |field2          | field3
---------------------+----------------+-----------
(varchar)bla         |(varchar)abla   |(varchar)123
(varchar)blub 456    |(varchar)blub   |(varchar)456

So, I would like to update field1 if the following condition is true:

Pseudocode:

if string from field2 and/or string from field3  is substring of field1
then replace substring field2, field3 from field1 if remaining string in field1 > 0

Note: If there is no string or token left in field1 after substring removal the string should not be touched.


texts: MYSQL Optimize tables on Innodb in production environment advice

texts:
According to mysqltunner.pl I have 177 Fragmented tables. I have over 5000 tables and 200 databases.

If I run optimize table on each of them, do you think I should take down the application to prevent reads and writes while I do this? I know that optimize locks the table, but if it is a pretty quick operation I don't know if it was cause any problems.


texts: Trying to solve "outside tablespace bounds" MySQL error, but recovering table with mysqldump fails

texts:
I am running MySQL on a Windows 2008 server. One of the tables (> 1 million records) seems to be corrupted. The mysql error log shows this:

InnoDB: Error: trying to access page number 197105 in space 89,
InnoDB: space name dbname/tablename,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.

Based on this error message, I checked my.cnf for the relevant lines:

innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"

This is exactly where the data resides. ibdata1 is about 208 MB in size, the tablename.ibd is about 4 GB in size. I've been using this server succesfully for many months.

After searching on this site and elsewhere, I tried to perform recovery as follows:

Set innodb_force_recovery=1 and then up to 6.
Next, dump the specific table with mysqldump. I am using this
command: mysqldump dbname tablename --user=root --password=blahblah.
During this dump, for recovery levels 1 through 3, mysql crashes ('goes away'). At 4, it managed to dump the first 23992 records, but then it said

  Lost connection to MySQL server during query when dumping table tablename at row: 23992.

The log has some additional lines preceding what I showed above:

InnoDB: Failed to find tablespace for table "dbname"."tablename"
in the cache. Attempting to load the tablespace with space id 89.
InnoDB: Error: trying to access page number 0 in space 89,
InnoDB: space name dbname/tablename,
InnoDB: which is outside the tablespace bounds.
It seems as soon as I touch or (select) query this table, either through php, python or phpMyAdmin, mysql crashes.

Does anybody have any other suggestions, possibly based on the tablespace bounds error above?

Thanks in advance!


texts: Sql combination matching criteria - The icrecream kiosk issue

texts:
Let's create this case
Icecream type   | Available |Normally eats (person) | Price
Small cup         2          1                        1.50
Medium cup        0          1.5                      2.00
Big cup           3          2.0                      2.50
Big cup wt cream  1          2.0                      3.00

The head of the children group comes at the kiosk, saying "We're 4, give us the cheapest combination".
You have these option, in order:
 - 2 big cup (5 eur)
 - You can take 2 small and 1 big cup (5.50)
 - etc.. up to 20 results..

Is sql efficient vs existing algorithms in discover the possible matches? And what's the best way to obtain this result?
Thanks!


texts: Migrate SQL Server binary datadump to postgresql

texts:
I just got a very big dataset (+/-55GB) in a binary SQL Server format.

Is there an easy way to import this file into a Postgresql Server? I found some converter-programs, but they 'only' convert from server to server and not file to file or file to server.

I don't have access to a SQL Server, so importing it and using this temporary as a source is not an option. Nor am I an expert in SQL Server programs...

I hope somebody here has a solution....


texts: Configuring a non-nullable foreign key uniqueidentifier with a default Guid.Empty value that exists in foreign table

texts:
Project Details:

MS SQL 2008 R2

I have already asked what amounts to one half of the question here, but I am hoping someone in this forum can answer the other half, involving MS SQL server specifically.

So I am doing a code-first ASP.NET MVC 5 project, and there is a business requirement that involves two very specific types of foreign keys to lookup tables. Since these lookup tables must always be loaded with a default value already selected, there is one value in these lookup tables that will always have a Guid.Empty value (00000000-0000-0000-0000-000000000000) as its primary key, so that it can be easily identified as the default value.

I am trying to find a way to have the foreign keys that make use of these two lookup tables to default to an empty Guid if they are passed a null value. This is added insurance just in case I miss a spot in the code itself where I am supposed to replace a null value with an empty Guid, but don’t. Plus, in this project in particular there will be scheduled weekly imports from an external database, and I want to be able to have all imported data have this default value in its foreign key without having to do extra work. Having the foreign key automatically assume this empty Guid as a default value when passed a null value would be very handy from several different angles.

And yes, I use Guids as uniqueidentifier GUIDs, not as nvarchar(128) strings. These foreign keys are non-nullable uniqueidentifier fields.

My other question tried to focus on the MVC Code-First migration itself, but I cannot wait around for an answer in terms of this project (although it will be useful for future projects), so this question on this forum involves the MSSQL server itself. I would like to know how to modify a foreign key uniqueidentifer field so that it will automatically use a null guid (00000000-0000-0000-0000-000000000000) when passed a null value. I have already tried adding (00000000-0000-0000-0000-000000000000) and ((00000000-0000-0000-0000-000000000000)) to the Default Value or Binding property for the foreign key, but both attempts came back with “Error validating the default for column 'column'. Do you want to cancel your changes?”

I am hoping that someone might be able to help me to add a default value to a non-nullable uniqueidentifier foreign key.


texts: Proper Shutdown of SQL Servers in a Cluster

texts:
I was just wondering what the best approach should be for shutting down an Active/Active Cluster where both nodes need to be shutdown for disk maintenance.

I was thinking of this process:

Open Failover Cluster Manager
Right-click on the cluster name, select ‘More Actions’, then “Shut Down Cluster…”
When prompted if you are sure you want to shut down the cluster, click “Yes”
Shutdown both Nodes
Do Disk Maintenance
Power on both Nodes
Fail Cluster resources back to their preferred node

Does anyone see any issues with that? How do you ensure the Cluster Resources come back up on the correct node considering they'll come up at a different time?


texts: List all databases not attached to SQL server

texts: I am moving databases from one instance to another (by detaching databases from the first instance, move MDF and log files to another location, and attach them to the new instance), unfortunately could not attach some of them, and miss calculate number of databases that have issues, how to check all MDF files that is not attached to SQL server from specific directory.


texts: Install SQL Server 2014 without SQL Server 2012 Native Client Tools

texts:
I am trying to install SQL 2014 through command line (via my installer setup to be precise).

Below is the INSTALL Command I use for a silent Installation of my SQL Server.

InstallCommand="/ACTION=Install /Q /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS /HIDECONSOLE /NPENABLED=1 /TCPENABLED=1 /FEATURES=SQLEngine /INSTANCENAME=$(var.SqlServerInstance) /UpdateEnabled=0 /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /ADDCURRENTUSERASSQLADMIN=1  /SECURITYMODE=SQL /SAPWD=P4nd4123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS"

As can be seen, I am installing the named instance (PANDA) and FEATURE is SQLEngine

Now everything runs fine unless I have an existing SQL 2012/2014 installation.

Upon researching, I found that Any installation of SQL 2012/2014 also installs SQL 2012 Native Client Tools. And when that exists, the setup fails.

I can not ask users to uninstall it first. Instead, if there is a way where we can ask our install to skip installing SQL 2012 Native Client Tools so that the setup may continue.

I checked and it looks like there is a Feature command "SNAC_SDK"which installs the required tools. However I have not specified that in command.

Any reason why it could be failing and if I am doing something wrong.

Please let me know.

TIA

Ashutosh


texts: Tricky condition for PostgreSQL UPDATE query

texts:
I need help with a PostgreSQL query.
I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to represent this: links_link and links_publicreply.
I am to delete aged rows from links_publicreply. That's easy enough:
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';

But some of these are referenced in the links_link table, hence the query fails. The reference in the links_link table is:
latest_reply = models.ForeignKey
    (
        'links.Publicreply',
        blank=True,
        null=True,
        on_delete=models.SET_NULL
    )

My first question is: How do I delete these foreign key references too?
My hunch is:
begin;
UPDATE links_link SET latest_reply=NULL where latest_reply_id in
    (SELECT id FROM links_publicreply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';
commit;

Does this look right?
If you read the update query above closely, you'll notice it sets latest_reply to null wherever it pointed to an aged links_publicreply row.
I don't actually want to do that. I ideally want to set latest_reply to the next available links_publicreply record (associated to the particular links_link record), which didn't get deleted by the query:
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';

How do I do that? That's my second question.

Tables
PG tables, as described in my Django app's models.py, are as follows:
class Link(models.Model):
    description = models.TextField(validators=[MaxLengthValidator(500)])
    submitter = models.ForeignKey(User)
    submitted_on = models.DateTimeField(auto_now_add=True)
    reply_count = models.IntegerField(default=0)
    latest_reply = models.ForeignKey
        ('links.Publicreply', blank=True, null=True, on_delete=models.SET_NULL)

class Publicreply(models.Model):
    submitted_by = models.ForeignKey(User)
    answer_to = models.ForeignKey(Link)
    submitted_on = models.DateTimeField(auto_now_add=True)
    description = models.TextField(validators=[MaxLengthValidator(250)])


texts: Urgent help with TempDB log file growth mystery

texts:
I need some urgent guidance for a tempdb issue for past last week:
The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down after 10 or so hours.
I have used various queries listed here but nothing shows up as in that duration there is no long running transaction.
Queries of mix workload keeps coming and going. There is no stuck transaction as such.
The user databases on this instance are all part of Log shipping setup and log backups happen every 15 mins.
In addition i added the Xevent
CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER ADD EVENT
sqlserver.database_file_size_change(SET collect_database_name=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE ([database_id]=(2))) ADD TARGETpackage0.event_file(SET filename=N'C:\ExtendedEvents\TempDBGrowth.xel',max_file_size=(100),max_rollover_files=(25)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

Nothing gets captured even in XE and its just blank in those 10 hours.
I have no clue what is eating my tempDB log as none of the queries or XE gets me the data. I have never seen this thing and generally get database growth using XE but here i am just clueless.
I am running DBCC SQLPERF(logspace) and i can see log used just keeps increasing by 1 Gb every 5 mins or so. We have TempDB log set to max 200 GB but by 10 hours its almost 160-180 GB and then suddenly comes down.
Running below query almost every time gives ACTIVE TRANSACTION.
SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases d
WHERE database_id = 2;

Please help what i might be doing wrong and why i cant see any transaction using TempDB log file usage?
Update- As requested in answer from @J.D, i checked and verified that there is no database mail or Service broker is being used internally to cause the growth:
However over last week i see the growth in log file of tempdb is zig zag and the trend of that zig zag growth is bit higher:
I need to help understand :-
How is the transaction log growth increasing upto 80% and then lowering down back to 0 and then increasing and growing and so on , with reaching lmoast max 90-95%.
I confirmed there is no manual shrinking going on. Could there be multiple processes contributing as this is OLTP server with tempdb usage throughout the day. Just dont understand how in last week it has grown near its max capacity?
thanks


texts: How do I safely import MySQL data in a directory to a running MariaDB server?

texts:
We're integrating another office into ours.  That includes some of their data.

I have a new installed MariaDB 5.5 server.  The data dir = /home/data/MariaDB.  I've done a new/fresh install into that directory:

ls -1 /home/data/MariaDB
    ibdata1
    ib_logfile0
    ib_logfile1
    multi-master.info
    mysql/
    mysql_upgrade_info
    performance_schema/
    test/

The server is up & running.

I inherited some data from a now-defunct MySQL server.  It's in:

ls -1 /home/data/MySQL-OLD
    auto.cnf
    ibdata1
    ib_logfile0
    ib_logfile1
    mysql/
    mysql_upgrade_info
    performance_schema/
    proj_DB_2136/
    proj_DB_4216/
    proj_DB_6217/
    test/

I want to move data for three DBs --  "proj_DB_2136/", "proj_DB_4216/", and "proj_DB_6217/" -- into my working MariaDB server.  Same names, same data, everything.

I just need to do this one time, so I can start working on the old data on my new server.

I'd like to do it the right way, without losing anything!

mysqldump makes sense for me, but only for working databases already IN the running server.

What's the correct way to get this data safely brought over?


texts: Unable to connect from remote computer, while local connection works

texts:
I have a Windows 2008 R2 64-bit server with SQL server 2008 R2 installed on it.
I manage to log to SQL server with SSMS with the sa SQL account and with a test SQL account.
But when I try to connect from another computer on the same network I have the following error for both accounts:

Cannot connect to 192.168.x.x.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

From the Event Viewer I have the following detailed errors

Login failed for user 'sa'.
Reason: Password did not match that for the login provided. [CLIENT: 192.168.x.y]
Login failed for user 'test'.
Reason: Could not find a login matching the name provided. [CLIENT: 192.168.x.y]

The error with the sa account implies I entered the wrong password, while I know I entered the good one.
The error with the test account implies the test account doesn't exist while it exists.
Remote connections are allowed on the SQL server.
Name pipes and TCP/IP are also enabled.


texts: SQLIOSIM fails to run when using UNC path

texts:
I'm trying to run SQLIOSIM on NAS cluster's NTFS shared folder.
Here's the command line:

  sqliosim.com -dir \\c16\vol1cifs -size 512 -d 600

But it repeatedly fails to create files during the setup due to following error:

  Error: 0x80070057  Error Text:   Description: Unable to get volume
  name for mount point \\c16\vol1cifs\

vol1cifs - NTFS shared folder, that can be opened and edited on the same client where
SQLIOSIM is installed, without any problem.
According to SQLIOSIM description it does supports UNC path.

What I'm doing wrong?

Thanks for help.
Sam


texts: Will restarting SQL Server clear my transaction log?

texts:
I have a client that restarts his SQL Server instance to clear an application problem he is encountering.

I find this is very odd, I don't think you should ever restart the database in normal operation. I believe his transaction log is simply full and restarting may be counting as a checkpoint? Is this a valid theory? He's in simple recovery mode - what I can do to check if this is the case?

Do we know of any other reasons why restarting the SQL Server service would make a problem disappear for a few days?


texts: Updating large table (~6.5 million rows) & indexes

texts:
I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all rows based on the data in a column in another table.

UPDATE  TOP (20000) c
SET     c.NewColumn = ISNULL(p.Col1, p.Col2)
FROM    dbo.Child c
    INNER JOIN dbo.Parent p on c.FKId = p.Id
WHERE   c.NewColumn IS NULL

in a loop, like this article.  The update was still running after 2.5 hours.  I'm wondering if disabling indexes on dbo.Child would make an impact.  NewColumn has no indexes, nor will it, but there are other indexes (about 5) on dbo.Child

Is SQL Server smart enough to see that it doesn't need to update the other indexes (as they are not part of the UPDATE), or would we benefit from temporarily disabling the indexes while we do the update statements?

This is SQL Server 2012 but the DB in question is in 2008 compatibility mode.


texts: (mySQL Aggregate function, JOINS, and GROUP BY) sakila database project


texts: Whole numbers from sql column using SSIS

texts:
Below is the sample SQL.

From Source1 tb, I need only whole numbers of Value column into ValueNEW of Destination1 tb as shown in the output pic.

--Source1
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Source1]') AND type in (N'U'))
DROP TABLE [dbo].[Source1]
GO
CREATE TABLE [dbo].[Source1](
    [ID] [int] NOT NULL,
    [Value] [varchar](255) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Source1] VALUES(1, '45.212')
INSERT INTO [dbo].[Source1] VALUES(2, '12/45')
INSERT INTO [dbo].[Source1] VALUES(3, '6')
INSERT INTO [dbo].[Source1] VALUES(4, '100.56')
INSERT INTO [dbo].[Source1] VALUES(5, 'ADFJJ 12/34 F=10.4')
INSERT INTO [dbo].[Source1] VALUES(6, '+. 0.5')
INSERT INTO [dbo].[Source1] VALUES(7, '300')
INSERT INTO [dbo].[Source1] VALUES(8, '90')
INSERT INTO [dbo].[Source1] VALUES(9, '80.5')
INSERT INTO [dbo].[Source1] VALUES(10, '13): @FD E 19.50 32')

--Destination
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Destination1]') AND type in (N'U'))
DROP TABLE [dbo].[Destination1]
GO
CREATE TABLE [dbo].[Destination1](
    [ID] [int] NOT NULL,
    [Value] [varchar](255) NULL,
    [ValueNEW] [varchar](255) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Destination1] VALUES(1, '45.212', NULL)
INSERT INTO [dbo].[Destination1] VALUES(2, '12/45', NULL)
INSERT INTO [dbo].[Destination1] VALUES(3, '6', '6')
INSERT INTO [dbo].[Destination1] VALUES(4, '100.56', NULL)
INSERT INTO [dbo].[Destination1] VALUES(5, 'ADFJJ 12/34 F=10.4', NULL)
INSERT INTO [dbo].[Destination1] VALUES(6, '+. 0.5', NULL)
INSERT INTO [dbo].[Destination1] VALUES(7, '300', 300)
INSERT INTO [dbo].[Destination1] VALUES(8, '90', 90)
INSERT INTO [dbo].[Destination1] VALUES(9, '80.5', NULL)
INSERT INTO [dbo].[Destination1] VALUES(10, '13): @FD E 19.50 32', NULL)

 SELECT * FROM [dbo].[Source1]
 SELECT * FROM [dbo].[Destination1]

ValueNEW needed as output.


texts: Securely deleting data from SQL Server logs

texts:
Is there a good way to purge data from a particular DB's *.LDF?

A little background... I'm a SysAd, not a DBA. I'm running SQL Server 2008 R2 for one of my applications and I'm not particularly concerned about the size of the logs. However, in order to meet certain DSS compliance, I occasionally have to purge certain data from the database and ensure that it is in no way recoverable.

I'm under the impression that if I go in and DELETE the particular entries I should be fine as far as the *.MDF is concerned. But I still need to make sure there is no way to recover the data from logs.


texts: Improve performance by combining two quieries

texts:
I have following submission table. A submission is an image.

create table submission
(
    md5 varchar(255) not null
        constraint submission_pkey
            primary key,
    user_name varchar(255)
        constraint fkmc6ld10rt5dgfsypoanfxifaj
            references users,
    copy_of varchar(255),
    create_date timestamp
);

copy_of is the submission_pkey (md5) of another user who has posted the same submission (image).
In other words if two users post the same image the later submission will contain an copy_of to the submission_pkey of the first image.

Now I am looking for a query which given one user_name will return a set of other usernames who have posted the same submission.
In this list I also want the usernames which the current user_name has copied the submissions from.

For this I have created two queries which together return the desired result set:

First:

select (select name
        from users
                 inner join submission s2 on users.name = s2.user_name
        where s2.md5 = s.copy_of) as other
from users
         inner join submission s on users.name = s.user_name
where copy_of is not null
  and name = ?1
group by name, other;

Second:

select (select user_name from submission where copy_of = s.md5 limit 1)
from users
         inner join submission s on users.name = s.user_name
where name = ?1
  and md5 in (select copy_of from submission s2);

Both (especially the second) queries run quite slow. Is there a way to improve those queries and ideally combine them to return one distinct list of user_name? I feel like the join with the users table should not be needed since all the relevant data is in the submission table but I have not found a way without it.


texts: What is transaction-managed data blocks?

texts:
While reading the Database Concepts (Oracle 12c) I found the following:

  Block header: This part contains general information about the block,
  including disk address and segment type. For blocks that are
  transaction-managed, the block header contains active and historical
  transaction information.

I want to know what's is a transaction-managed block?


texts:
I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities.

There are 8 columns in this table , out of which 6 columns are clubbed to make a primary key. These columns could be individual foreign keys but due to performance we have made this change.

Below is the show create table output

 CREATE TABLE `hybrid_exp_trait_dtl` (
  `HYBRID_NUMBER` varchar(100) NOT NULL,
  `TRIAL_STATUS` char(4) NOT NULL,
  `LOCATION_CODE` char(5) NOT NULL,
  `EXPERIMENT_ID` char(20) NOT NULL,
  `REPLICATION_NUMBER` int(3) unsigned NOT NULL,
  `TRAIT_NAME` varchar(30) NOT NULL,
  `TRAIT_VALUE` varchar(50) DEFAULT NULL,
  `MACHINE_NO` char(2) NOT NULL,
  `USER_ID` char(8) NOT NULL,
  `MYTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `FK_HYBRID_EXP_TRAIT_DTL_2` (`LOCATION_CODE`),
  KEY `FK_hybrid_exp_trait_dtl_3` (`USER_ID`),
  KEY `Index_4` (`HYBRID_NUMBER`, `EXPERIMENT_ID`, `TRIAL_STATUS`, `LOCATION_CODE`, `REPLICATION_NUMBER`, `TRAIT_NAME`, `USER_ID`) USING BTREE,
  CONSTRAINT `FK_HYBRID_EXP_TRAIT_DTL_2` FOREIGN KEY (`LOCATION_CODE`) REFERENCES `location_mst` (`LOCATION_CODE`),
  CONSTRAINT `FK_hybrid_exp_trait_dtl_3` FOREIGN KEY (`USER_ID`) REFERENCES `user_data` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

As you  can see I have four indexes

KEY FK_HYBRID_EXP_TRAIT_DTL_2 (LOCATION_CODE)
KEY FK_hybrid_exp_trait_dtl_3 (USER_ID)
KEY Index_4 (HYBRID_NUMBER, EXPERIMENT_ID, TRIAL_STATUS, LOCATION_CODE, REPLICATION_NUMBER, TRAIT_NAME, USER_ID) USING BTREE

Below are the two queries which take long time 4.26 and 0.64 respectively.

select  distinct
        location_code,
        Max(replication_number) as replication
from hybrid_exp_trait_dtl  t
where t.experiment_id='NSKOK12K1102' and t.trial_status='MLT';

And this one

SELECT *
FROM  hybrid_exp_trait_dtl h
where h.experiment_id='NSKOK12K1102'
  and h.TRAIT_NAME='YLD' ;

In order to improve performance i modified 3rd index and added one more index , so below are the indexes for my tables

KEY FK_HYBRID_EXP_TRAIT_DTL_2 (LOCATION_CODE)
KEY FK_hybrid_exp_trait_dtl_3 (USER_ID)
KEY Index_4 (EXPERIMENT_ID,TRIAL_STATUS,LOCATION_CODE,REPLICATION_NUMBER)
KEY hbrid (HYBRID_NUMBER,EXPERIMENT_ID,TRAIT_NAME)

Now performance has increased significantly for both the queries and now performance time is .45 and .3 sec respectively.

Is this indexing correct , will it have effect on my update and insert statement as i will be doing bulk update.


texts: Does timestamp protocol following thomas's write rule allow non-view-serializable schedules in some cases?

texts:
I have come across the following line in a text book (Database System Concepts
Textbook by Avi Silberschatz, Henry F. Korth, and S. Sudarshan $6e$) page no. 686:

  Thomas’ write rule allows schedules that are not conflict serializable
  but are nevertheless correct. Those non-conflict-serializable
  schedules allowed satisfy the definition of view serializable
  schedules (see example box).

What I understood from the above lines is that every schedule generated by timestamp protocol following Thomas's write rule is view serializable.

Now let's take the following little schedule: $S: R_1(X), W_2(X), W_1(X)$.

This schedule $S$ is allowed under timestamp protocol which follows Thomas's write rule.

And serialization order is $R_1(X), W_1(X).$

But I was not able to prove that it is view serializable.

Actually I think that it is non-view serializable because,

Consider serial order as $T_1, T_2$

Now final value of $X$ is being written by $T_2$. So not equivalent.
Next alternative serial order is $T_2, T_1$

here, $R_1(X)$ will read value of $X$ written by $T_1$ not original value
which was there before start of both transaction. So this too is not
view-equivalent.

What is going wrong here? Please help me with this one.


texts: how to avoid duplicated column in two different tables?

texts:
OK, I'm inexperienced in terms of database design, so hopefully my question isn't too silly.

I have 2 tables users and user_profiles these two tables has one-to-one relation between them. so(obviously) each row in users table will be linked to a row in user_profiles table.

And, when I'm displaying a user's profile in the web app I'm creating I have to do two SELECT statements(or JOIN) to get the required data, which is -somehow- overkill for me, and I'm looking for an alternative to it, I'm trying to get the required data with one SELECET statment from one table.

users schema:

CREATE TABLE IF NOT EXISTS `users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(50) COLLATE utf8_bin NOT NULL,
   `password` varchar(255) COLLATE utf8_bin NOT NULL,
   `email` varchar(100) COLLATE utf8_bin NOT NULL,
   PRIMARY KEY (`id`)
)

user_profiles schema:

CREATE TABLE IF NOT EXISTS `user_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `nickname` varchar(50) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
)

So for example, if I want to grab the nickname, user and email I'll definitely use JOIN or double SELECT. Which will lead me to use two tables for such a simple query.

So, I have currently three solutions in mind:

Use JOIN or SELECT and hopefully my website won't be too slow
because of this.    Will this have huge impact on the performance of
my web app anyway?
Remove user_profiles table and keep all user's personal data in
one table.
Duplicate columns in two tables, so for example the email column will be present in users and user_profiles. Which will result in double INSERTS at the time of user registration.

Looking forward to hear your opinions.


texts: How can I join one table or other depending on a field value?

texts:
I have one table and I'd like to decide which table join depending on the value of a field.
I have the table T and there is a field Type, so if Type=1 then I have to join with Table2 and if Type=2 I have to join with Ta


texts: Possible to automate updating development database on a VM?

texts:
Using MySQL 5.6 and VM Workstation Pro 12.1.1.

At my job, the way things are set up is that my development environment is a locally hosted Virtual Machine.  Every day the first thing I need to do is to export a copy of the current database, bring it over to the VM, then import it on the VM's MySQL Server.  It takes about 15 minutes every day, not to mention sometimes I will get a ticket about a newly created item that was created after I updated the dev MySQL in the morning and I have to repeat the process.

Is there a way to bundle these actions in a script I can just have called automatically at say 6 am before I come into work?  And then again whenever need be?

To make things easier/harder, I know our database takes backups at midnight every night and the exact location/file name it will be, its on a network drive that requires a password however.