texts: Creating separate table as data retrieval queue

texts:
I am storing pedigree data in a single table and each entry has reference to its two parents. Each time I add an entry I discover two new ones I need to collect. I am on the fence between two options to handle this:

Create a separate table that acts as a queue so that retrieval processes can simply delete an entry an fetch it, then add the two new entries to the end.
Add two flag bits to the main table: complete and processing. When an entry is fetched, we fill in its row and create two rows for its parents that have the completed flag set false. The processing flag is for parallel processing purposes.

Which method makes more sense?


texts: Can't connect through sqldeveloper because locale not recognized even though my language/regional settings are English/US

texts:
On a certain machine I consistently cannot connect to an oracle db due to the locale not being recognized:

Other people have noted the locale or language settings may not be compatible with the oracle db driver but my laptop is US/English and I can connect to this same db w/o a problem from similar laptops. Moreover, I tried uninstalling sqldeveloper and wiping out all sqldeveloper folders under /AppData/Roaming/ so that I could not reimport settings during the reinstall but to no avail. Oracle db is 18c but this happens with 12c as well.  Sqldevelopers can be any version (ie 3.x, 4.x, 18.x, 19.x...yes, I've tried them all :( )


texts:
|    payments    |  | transactions |  | transaction_items |
|:--------------:|  |:------------:|  |:-----------------:|
|       id       |  |      id      |  |         id        |
|      date      |  |    number    |  |   transaction_id  |
|     amount     |  |     date     |  |    description    |
| transaction_id |  |      val     |  |       price       |
                                      |      discount     |
                                      |      quantity     |

I'm trying to display a list of payments made on transactions and show the current balance after each payment. Below is an example of expected result

| number | DATE(p.date) | total   | paid    | balance |
| 1355   | 2016-10-31   | 899.00  | 450.00  | 449.00  |
| 1355   | 2016-12-06   | 899.00  | 449.00  | 0.00    |
| 1359   | 2016-09-28   | 4045.00 | 1515.00 | 2530    |
| 1359   | 2016-10-24   | 4045.00 | 35.00   | 2495.00 |
| 1361   | 2016-09-28   | 1548.00 | 1548.00 | 0.00    |

and here is my query so far, but have an error in the where clause

select
    t.number,
    DATE(p.date),
    ti.total 'total',
    SUM(p.amount) 'paid',
    ti.total - paid.total 'balance'
from payments p
left join transactions t
on p.transaction_id = t.id
left join (
    select inner_ti.transaction_id, sum((inner_ti.price - inner_ti.discount) * inner_ti.quantity)  'total'
    from transaction_items inner_ti
    group by inner_ti.transaction_id
) ti on t.id = ti.transaction_id
left join (
    select inner_p.transaction_id, sum(inner_p.amount) 'total'
    from payments inner_p
    where inner_p.date <= p.date -- error unknown column p.date
    group by inner_p.transaction_id
) paid on t.id = paid.transaction_id
group by t.number, DATE(p.date), ti.total, paid.total
order by DATE(p.date) ASC

Please note that I'm grouping by p.date since our concern is total payments made within the day.

Can someone please enlighten me why I'm getting that error? And is there any workaround to achieve the expected result?


texts: foreign key violation within trigger on insert of 'new' id in other table as foreign key

texts:
Within a trigger I am inserting the id of the table the trigger is assigned to as a foreign key in another table, but the 'new' id doesn't exist yet, so I get a foreign key constraint violation.

Here is a simplified version:

  CREATE or replace FUNCTION trg_func()
            RETURNS trigger
            LANGUAGE 'plpgsql'
        AS $BODY$
        DECLARE

        BEGIN

        INSERT INTO other_tbl (
        my_tbl_fk
        ,my_calc
        )
    SELECT new.id -- <- this one is the issue. Is fk in other_tbl & pk in my_tbl
        ,a.some_val / b.some_val calc
    FROM (...subquery here...) a
        ,(...subquery here...) b
    WHERE a.some_id = b.some_id;

        RETURN new ;
        END ;
        $BODY$ ;

        CREATE TRIGGER my_tbl_ins_upd_trg
            BEFORE INSERT OR UPDATE
            ON my_tbl
        FOR EACH ROW
        EXECUTE PROCEDURE trg_func();

I tried temporarily removing the fk before the insert then adding it back, but it doesn't seem to help.

I can remove the fk, trigger the trigger, then add the fk back again, so I know my insert values don't violate the constraint if applied generally.

I am guessing there is a method or strategy for doing this because it seems like something that would be done a lot for calculated fields via trigger.

I am trying to do the 'Discarding 3NF With Triggers' section of this link:
http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html

PostgreSQL 9.3


texts:
As the documentation says, rowversion data type is auto-updated every time a row is updated using a global auto-incremented number within the entire database scope.

My question is the following: Say I have 10000 concurrent updates on tables that have a rowversion column. Since all try to access and update atomically that global number, I'd imagine that all of them are processed in a 'serial' way which may reduce performance compared to tables that doesn't need to access that number.

Is this correct?.


texts: Understanding connection between different database concurrency control protocols

texts:
I have came across following diagram related to timestamp based concurrency control protocols in database systems (as summarized in these sldes or detailed in the book by Korth et al.):

I don't know whether its correct or not.

As per my interpretation, it means all schedules possible under protocol of particular oval is also possible under protocol of enclosing oval.

However as long as I know, both multiversion timestamp and timestamp ordering protocols ensures conflict serializability, whereas Thomas write rule ensures view serializability.

So I guess, the diagram correctly depicts the relation between Thomas write rule and timestamp ordering protocol. But I am unsure whether it correctly depicts the relation of multiversion timestamp ordering protocol correctly in relation with Thomas write rule. (I believe relation between multiverstion timestamp ordering protocol and timestamp ordering protocol is also correctly shown).

Should it be something like this:

I have came with above diagram because I feel it correctly captures following points:

All schedules possible under timestamp ordering are also possible under multiversion timestamp
All schedules possible under timesstamp ordering are also possible under thomas write rule
Some schedules (specifically view serializable ones) possible under Thomas write rule are not possible under multi version timestamp protocol and vice versa (schedules involving different versions of data items).

Am I right with all of these?


texts: Database Blocking, kill the session which is responsible after 5 minutes

texts:
I have been struggling with database blocking at my database server from last so many months. Have tried to use many ways to capture them and also aware that who are leaders of blocking. This application uses typical primary key-foreign key relationship and all the tables are tightly coupled. Few tables have as many as 30 foreign keys and hence whenever there is an insert/update/delete, all of those underlying tables get blocked. I have communicated this to vendor and they also have tried to remove PK-FK for possible tables and deployed in the test environment however its taking ages to go to production as lot needs to be tested and its failing in some or other scenario.

Since, I am supporting vendor based application and any change needs to be tested by multiple stakeholders, I don't know when will it really go to production. Blocking becomes so severe at time that whole portal goes down and I am left with no option but to kill the blocking SPID.

I am using below query to check the blocking by using sp_whoisactive procedure Mr. Adam Machanic:

EXEC sp_WhoIsActive
    @find_block_leaders = 1,
    @sort_order = '[blocked_session_count] DESC',

So far, I was working on reactive approach meaning, I was killing SPID who are responsible for blocking however this doesn't seem to go well and sometime blocking escalates and I have to run like fire-fighter. I would like to switch my side from reactive to proactive and planning to put a procedure in place which will take input as SPID from above procedure, check the duration and if blocking is more than 5 minutes, kill it.

At times, I have seen there are many blockers responsible for blocking as shown below:

Don't really know how will these kind of blocking be handled. Read something similar by Mr. Jason Hall at this link and second one at this forum however I am looking for something having base of sp_whoisactive.

This is to add very pertinent point about database isolation that I have changed database isolation level from default to "Read Committed Snapshot". On the very same topic, I had asked this question when I joined this forum however it was closed as I was very new to this forum and was unclear on my question.

Any input/help on this will be appreciated.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)   Sep  7 2018 01:37:51   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3  (Build 14393: ) (Hypervisor)


texts: Read Only Users on secondary replica AlwaysOn

texts:
Greeting .
I have a question. I have set up an AlwaysOn availability groups configuration for DR.
Meaning I have a node1 in Site A and a Node2 in site B acting as a DR geographical let say 50 mile apart. The Mode is set Async, Can i read SSRS report from the Node2 ? Am using SQL Server 2016 enterprise
If so how ?
Thanks in advance


texts: Aurora postgres grant permission to pg_attribute

texts:
In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. For example students and student_history, where the history table store the historical stages of the students.

When deleting a column in students, the corresponding column does not delete in students_history.
Which c
I clever college of mine(who found a better job) found a solution of changing the 'pg_attribute.attislocal'(see script below) for the table which solved the problem.

UPDATE pg_attribute
SET attislocal=false
WHERE attrelid = '{parent_table_name}_history'::regclass
AND NOT attisdropped
AND attnum > 0
AND attinhcount > 0;

So I run the script user the super user account I get:

permission denied for relation pg_attribute

So how do I grant he super user permissions to change the pg_attribute or is there anything else I'm overlooking?


texts:
I installed Oracle 11g on my win xp machine. Now I want to connect to the database.  I was told that to connect to the database from command prompt I should use
SQLPLUS "/ AS SYSDBA". But I want to connect as sysdba using a gui tool. So what is the equivalent USERNAME/PASSWORD which I can use to connect to the database (I think sysdba is just a role and has nothing to do with username or password. The '/' defaults to some value)

Also I tried to grant SYSDBA TO a user(say TESTUSER/TESTPASSWORD) using GRANT command. But at the next login the access seems to get reverted somehow.

Need your suggestion to resolve this issue .

Thanks in advance


texts:
UPDATE:
Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1

Table structure (some fields are hidden):

+---------+---------+------------+-------------+-------+-------+
| user_id | kind_id | country_id | province_id | value | score |
+---------+---------+------------+-------------+-------+-------+
|       1 |     201 |          1 |           1 |     1 |     5 |
|       1 |     102 |          1 |           1 |    31 |   155 |
|       1 |     201 |          1 |           1 |     6 |    30 |
|       1 |     109 |          1 |           1 |    33 |   165 |
|       2 |     202 |          1 |           4 |    76 |   380 |
|       2 |     201 |          1 |           4 |     5 |    25 |
|       2 |     209 |          1 |           4 |     9 |    45 |
|       2 |     302 |          1 |           4 |     8 |    40 |
|       2 |     201 |          1 |           4 |    14 |    70 |
|       3 |     201 |          1 |           9 |     7 |    35 |
|       3 |     201 |          1 |           9 |     9 |    45 |
|       3 |     201 |          1 |           9 |     2 |    10 |
|       3 |     201 |          1 |           9 |     5 |    25 |
|       3 |     201 |          1 |           9 |     7 |    35 |
|     ... |     ... |        ... |         ... |   ... |   ... |
+---------+---------+------------+-------------+-------+-------+

Rank is calculated based on sum of score of user's activities.
I saw many questions here and on Stackoverflow but my problem is more complex.

I need 3 rank values per user: global, country (for users in same country), province (same as country). For example a rank value for user 1 may be like this {"global": 9, "country": 3, "province": 1}. I do not need rank of all users but for one individual user.

I tried a dozen queries but can't calculate this three with one query. or, at least, three clean and efficient queries.

I will cache the result for some time, but still efficiency is important since the activities table contains many records and will grow as time passes.

How can I do this?

  Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6


texts: How does STRICT_ALL_TABLES work with multi-row statements and partial updates?

texts:
In the docs for STRICT_ALL_TABLES it states,

  For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

What does this mean though?

CREATE TABLE foo ( a int NOT NULL );
INSERT INTO foo VALUES (NULL), (NULL);

This does what everyone would reasonably expect a totally insane database to do,

SELECT * FROM foo;
+---+
| a |
+---+
| 0 |
| 0 |
+---+
2 rows in set (0.00 sec)

But, I expected a multi-row INSERT to insert the non-violating rows that are seen first,

SET sql_mode='STRICT_ALL_TABLES';
INSERT INTO foo VALUES (5), (NULL);

To insert just one row with 5, as per the docs in STRICT_ALL_TABLES. Instead it does,

  ERROR 1048 (23000): Column 'a' cannot be null

How do I interpret the docs to STRICT_ALL_TABLES when they say earlier rows .. partial update and "single-row statements". And why is MySQL doing something less horrible then documented? I'm pleasantly surprised.


texts: MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

texts:
I have a basic table  :

create table fullTextTest
(
    id INT(11) NOT NULL,
    superText CHAR(255) NOT NULL,
    superLongText TEXT NOT NULL,
    primary key (`id`),
    FULLTEXT KEY `superText` (`superText`),
    FULLTEXT KEY `superLongtext` (`superLongtext`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into fullTextTest
    set id=1,
    superText="Hi guys, how is it goin'?",
    superLongtext="Please give me some dummy text to search on!!!"
;

show index from fullTextTest;
| fullTextTest |          0 | PRIMARY       |            1 | id            | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| fullTextTest |          1 | superText     |            1 | superText     | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |
| fullTextTest |          1 | superLongtext |            1 | superLongText | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |

Now , let's see if MySQL is using this index correctly :

EXPLAIN select * from fullTextTest where match(superText) AGAINST ("guys" IN BOOLEAN MODE);
| id | select_type | table        | type     | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------------+----------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | fullTextTest | fulltext | superText     | superText | 0       |      |    1 | Using where |

The "Using Where" shows me that EXPLAIN don't understand FULLText.. Which is a shame, really.

Did I miss something ?


texts: SQL Server Standard 2016 Service Pack and Cumulative Update

texts:
We are currently using SQL Server 2016 Standard Edition with ALWAYS-ON High Availability. We have two different clusters of SQL servers. We need to update it to the latest version of SQL (As per my research Cumulative Update package 13 (CU13) for SQL Server 2016 SP)

Cluster 1 - Currently on Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64)

Cluster 2 - Currently on Microsoft SQL Server 2016 (SP1-CU12) (KB4464343) - 13.0.4541.0 (X64)

Query 1 - Can we update SP1-CU12 to directly SP2-CU13

Query 2 - Can we update SP2-CU4 to directly SP2-CU13

Query 3 - What steps should be taken before and after upgrading

Query 4 - What should be the roll-back plan, incase of failure

Any other word of advice or caution for someone doing for the first time will be appreciated.


texts:
I need a big help.

I have two tables (SubCells and Codes)

and

Visually it looks like this (We can count an order)

And i need to get this final result (with SQL or even a C#)

How can i do that ? I think i have to add Order column to my SubCells table, but how can i iterate Codes and get groups and codegroups information ? I prepared test environment and i will glad for all help.

http://sqlfiddle.com/#!3/438a6

Thank you


texts:
When working with virtual machines often it can be very useful to have a base read-only VM then have several smaller VM's that use that larger VM as a base and write their changes to their own writeable copy.

Is something similar available in Microsoft Sql Server?

The situation I am in is we host a demo copy of our product that our clients can connect to for about 30 days or so. When we create a demo account we have to create a new copy of the base database the program uses and have the software point at that. Each image is a little over 2GB, but on average the demo users will only change about maybe 100MB worth of data in the database.

What I would like to do is have a read-only database that acts as a base then have the demo databases create a "differencing database" and write out it's data and log information to that differencing database. Is this possible? I have been searching through the MSDN documentation but I have yet to find anything and unfortunately google searching has been fairly useless as searching for Sql server differential database gets polluted with all the pages about doing differential backups (doing Sql server differential database -backup did not return any useful results either, all I got was information about differential deployment scripts).


texts: Copy Huge Table with Transformations into Another Table

texts:
I have a large table that was imported from a CSV using the import wizard.  I have a query that selects from this table and replaces all of the string "code" columns with integer ids from other dimension tables and performs some transformations on some other columns.  This all works well.

I need to insert the results of this query into a table in the database that has the required foreign key links and indices.  This is also the table that the SQL objects (sprocs, functions, views) use to access the data.

My issue is that the insert takes forever (1.2 hrs) before it eventually dies by complaining about the size of the transaction log.  I don't have access to log onto the server.  I only have access through SSMS with db_owner rights on the database in question.

My questions are:

How can I perform the insert without logging it in the transaction log?  I know there is BULK INSERT and OPENROWSET(BULK ...) but these require a file.
Would the insert go faster if I drop the indices? I think this would break the execution plan of the existing SQL objects that use the destination table. Is this advisable if there is a clustered index on the destination table?
Is not logging the insert the correct way to go or should I use some other method?


texts: MySQL very slow query when changing one WHERE field despite no index/key

texts:
This is quite a confusing issue for me. I have a database full of baseball statistics. Running this query:

SELECT * FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN games ON games.gameName = hits.gameName
WHERE games.type = 'R'
LIMIT 50

Returns:

/* 0 rows affected, 50 rows found. Duration for 1 query: 0.218 sec. */

But running this query:

SELECT * FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN games ON games.gameName = hits.gameName
WHERE games.leagueLevel = 'mlb'
LIMIT 50

Hangs for a long time. The index on the games table is only games.gameName and nothing else.

SELECT DISTINCT type FROM games gives 8 single-character rows (VARCHAR 1) including one NULL.

SELECT DISTINCT leagueLevel FROM games gives 6 three-character rows (VARCHAR 5) including one NULL.

I have no idea why the second query would be extraordinarily slow while the first one runs just fine.

Thanks for your help.


texts: Allow remote access only from certain IP addresses

texts:
If I've understood what I've read, all you need to do in order to allow remote access to SQL Server is to open TCP port 1433 in Windows firewall. But that will leave your server wide open to the entire internet and SQL Server itself provides no further tools to fine-tune the source of connections.

Is there a way to grant access to such port only to connections from a know static IP address?


texts: Merge Replication Update Statement Timeout

texts:
I work for a company that provides web based data collection. Currently we have a SQL Server 2012 server as the publisher for 2 subscribers (planning on expanding up to 5+ subscribers), both SQL 2012 Express on machines running Server 2008 R2. The subscriber and publisher servers also have a .NET application that access and writes to the published tables. The filters on our publication are set up to prevent a subscriber from writing to records that exist on different subscriber, but the publisher application can write to all records. When we first went live with this setup (1-2 months ago) there were no issues and everything was working as expected; however, recently (within the week) we have been receiving application errors indicating SQL query timeout when our application executes update statements to specific tables that are replicated. This error is occurring on both the publisher box and subscriber box, and is specific to 1 or 2 tables that are filtered.

We have looked at the fragmentation levels of the publisher/subscription tables as well as the merge tables, and currently there is nothing greater than 30% fragmentation. Note that we have put in place Reorg for Merge_Contents and Merge_genhistory scheduled job that runs every 30 minutes. Looking at the query execution plan also provides no insight into possible causes of the errors. RAM usage cap has also been checked and currently we have it set to a 25 gig max out of about 34 gigs available on the publisher and a similar ratio set up on the subscriber boxes, so we currently do not think that is the cause.

I have been researching this issue for the last few days and have not been able to find anything that has fixed the issue. Are there any probable causes we should look at or is there a good way to determine the cause of the timeout?

Updated (2013/08/07):
Yesterday evening we saw the timeout issue again and decided to run exec sp_updatestats (as per discussion comments) hot in production. As soon as it finished the error stopped. Since it seemed to work, we added the task to our hourly reorgs. All seemed well for several hours; however, we got a call about production issues early this morning. The same SQL Timeout issues were happening an hour after running that morning's updatestats. During this same time the SQL process was hitting ram cap, other than that there were no apparent issues. No server connections issues in or out of our server (not sure about client side connections to our application), no deadlocks going on, and no long running process eating up CPU. One interesting thing we did see was that the hourly job seemed to be stuck and was not finishing in the expected time frame. We have contacted Microsoft for support, but still do not seem to have a resolution for this issue.

Update (2013/08/14):
We have been seeing the issue consistently over the course of the last week. Currently we have indication that it is caused by merge replication syncs that get "stuck." When we see the issue occurring we can generally find the longest running subscriber sync operation that has still not completed and end the process through the task manager. Upon ending the subscription process, everything begins working as expected for around 2-4 hours when the issue occurs again.

Currently our assumption is that the replication (currently using replmerg.exe) is causing table level locks. If the replication process for some reason begins taking an extended amount of time or gets "stuck" on that table, all updates fail because of that lock. Upon ending the replication that lock gets released and everything begins to work as expected.

The behavior we are seeing is nearly identical to the following: http://us.generation-nt.com/answer/sql-2005-merge-replication-locking-help-97622442.html

Our latest attempted fix was setting the Max_Continuous_Merge option on the publication to 2 in order to limit the number of merges in case a deadlock was occuring between two merge agents; however, that has not fixed the issue.


texts:
I am trying to replace many null date values in a single column using this query:

UPDATE animal SET
  BirthDate = '1900-01-01 06:00:00 AM'
WHERE BirthDate = '''

MySQL shows this error:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE animal SET
    BirthDate = '1900-01-01 06:00:00 AM'
  WHERE BirthDate = ''' at line 1.

How can this be resolved? I use Toad for MySQL interface editor.


texts: Licensing SQL Server 2012 Standard Edition for development and test severs

texts:
Is there an economic way to license SQL Server standard edition on a test server?

Given the following environment:

live: SQL Server 2012 Standard Edition
test: SQL Server 2012 Developer edition
dev: SQL Server 2012 Developer edition

The problem with this scenario is that a developer could code using a feature that works in developer edition, but is not available when deployed to the live server.  The client would be the first to discover the problem!

Thoughts:

Host test database on live server?

Doesn't give the live/test isolation I would like
Uses live server resource (buffers, CPU, etc.)
(Could disconnect test database when not in use to minimise this)

Run a second database instance on the live server

Still poor live/test isolation
More processes using server RAM/CPU


texts:
Is there any performance problem / difference if I have one Foreign Key that points to another table Indexed Fields that is NOT the PK ?

Something like

DECLARE @T1 TABLE
(
    T1_ID INT IDENTITY PRIMARY KEY,
    T1_SOMEID INT NOT NULL,
    OfficeID VARCHAR(3) NOT NULL,
    Data VARCHAR(200) NOT NULL
);

DECLARE @T2 TABLE
(
    T2_ID INT IDENTITY PRIMARY KEY,
    T1_SOMEID_FK INT NOT NULL,
    OfficeID VARCHAR(3) NOT NULL,
    OtherData VARCHAR(100) NOT NULL
);

QUESTION:
Is there a performance drop when I run a query that looks like this:

SELECT t1.T1_ID, t1.Data, t2.T2_ID, t2.OtherData
FROM @T1 t1
    INNER JOIN @T2
       ON t1.T1_SOMEID = t2.T1_SOMEID_FK
          AND t1.OfficeID = t2.OfficeID


texts: Why does MariaDB choose different index use for the same data?

texts:
I have this table (this is the output from show create table on both servers, ie. using diff to compare the output shows only the auto increment value, because one has moved on slightly from the snapshot).

Create Table: CREATE TABLE `logs` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip` varchar(39) CHARACTER SET latin1 NOT NULL,
  `method` varchar(6) CHARACTER SET latin1 NOT NULL,
  `endpoint` varchar(1000) CHARACTER SET latin1 NOT NULL,
  `status` int(3) NOT NULL,
  `params` text,
  `data` mediumtext,
  `client_username` varchar(526) DEFAULT NULL,
  `client_ip` varchar(39) CHARACTER SET latin1 DEFAULT NULL,
  `extra_params` text,
  `error_code` int(10) DEFAULT NULL,
  `request_id` varchar(255) DEFAULT NULL,
  `domain_id` bigint(11) unsigned DEFAULT NULL,
  `admin_id` bigint(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_id` (`request_id`),
  KEY `endpoint` (`endpoint`),
  KEY `method` (`method`),
  KEY `client_username` (`client_username`(255)),
  KEY `domain_id` (`domain_id`),
  KEY `admin_id` (`admin_id`)
) ENGINE=MyISAM AUTO_INCREMENT=685835441 DEFAULT CHARSET=utf8

I have exactly the same data on two different servers (both have the same my.cnf), but I get different behaviour. On one, I get a table scan, no matter what, and on the other I get the expected index use (not just with the domain_id index as in the example below, but with any index).

On the good one:

MariaDB [temp_db]> explain select * from logs where domain_id=298506;
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+
| id   | select_type | table           | type | possible_keys | key       | key_len | ref   | rows   | Extra |
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+
|    1 | SIMPLE      | logs            | ref  | domain_id     | domain_id | 9       | const | 119987 |       |
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+

On the bad one:

MariaDB [temp_db]> explain select * from logs where domain_id=298506;
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
|    1 | SIMPLE      | logs            | ALL  | NULL          | NULL | NULL    | NULL | 166719856 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+

They are both using MariaDB 10.1.29, on Debian. I've done an optimise table and analyse table on both.

Asking MariaDB to force use of the index also doesn't:

MariaDB [temp_db]> explain select * from logs force index (domain_id) where domain_id=298506;
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
|    1 | SIMPLE      | logs            | ALL  | NULL          | NULL | NULL    | NULL | 167509512 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

Is there either some explanation for what causes this behaviour, or some way that I can investigate further?


texts:
Very dumb question, but bear with me, I am learning and am interested in the principles behind.

So: I have a users table and a pages table. In this case, 1 user and 4 pages. If I join the two tables, I get four times the user data, along with the pages data. The result is little legible, when I see a result stream like this I think wtf?

I know 'databases are just made this way'. I'd like to learn some context and meaning.


texts: Execution plan has TOP operator for query without TOP or ORDER BY

texts:
I have product sales by month temporary table.(subset of much larger historical table) I'm running query to find new product not previously sold in the previous months for a specified 6 month range. So take January as base, in Feb show me all new product sold that wasn't in Jan sales. In March all new product not sold in Jan or Feb sales, etc. For first 5 months query returns sub second. On 6th month, it takes ~5 minutes. Comparing execution plans, on that 6th month, it's showing Top operator in my execution plan that's taking the extra time. But I'm not sorting or using Top X in select. What in my query is causing this? I can wait 5 mins since it's one-off request, but I'm more asking from a learning perspective, I want to understand the why it added to Top operator. Thanks in advance.

select divn_nbr, dept_nbr, vendorNumber, pid, nrfcolorNumber, periodcode
from ##tmpSFA a
where periodCode = '201512'
and not exists
(
    select 1 from ##tmpSFA b
    where ISNULL(a.divn_nbr, -99) = isnull(b.divn_nbr, -99)
    and isnull(a.dept_nbr, -99) = isnull(b.dept_nbr, -99)
    and isnull(a.vendorNumber, -99) = isnull(b.vendorNumber, -99)
    and isnull(LTRIM(rtrim(upper(a.pid))), -99) = isnull(LTRIM(rtrim(upper(b.pid))), -99)
    and isnull(a.nrfcolorNumber, -99) = isnull(b.nrfColorNumber, -99)
    and b.periodCode IN ('201507', '201508', '201509', '201510', '201511')
)

Here are the results and plan:


texts: The memory space and ram space allocated to my SQL Server

texts:
How do I find out how much memory space and RAM has been allocated to my SQL Server. SQL Server version is SQL Server 2012 11.0.5343.0.

Please help. I have searched online and all i can find is how much memory is being used by my SQL Server.

I need to find out how much memory and RAM have been allocated to my SQL Server already. So, that I can figure out how much memory and RAM are not being used.

Thanks!


texts: How to use RESTRICT for Foreign Key in mysql?

texts:
In the database structure of

  CREATE TABLE Country (
  name varchar(40) NOT NULL,
  PRIMARY KEY  (name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE City (
  name varchar(40) NOT NULL,
  PRIMARY KEY  (name)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE Map (
  country varchar(40) NOT NULL,
  city varchar(100) NOT NULL,
  PRIMARY KEY  (country,city),
  FOREIGN KEY (country) REFERENCES Country (name) ON DELETE CASCADE,
  FOREIGN KEY (city) REFERENCES City (name) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I expect to delete parent from City by leaving the corresponding value in child intact by these three equal commands

  FOREIGN KEY (city) REFERENCES City (name) ON DELETE NO ACTION
  FOREIGN KEY (city) REFERENCES City (name) ON DELETE RESTRICT
  FOREIGN KEY (city) REFERENCES City (name)

But when using NO ACTION OR RESTRICT or omitting ON DELETE. MySQL does not allow me to delete from parent column with this error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
('test'.'Map', CONSTRAINT 'Map_ibfk_2' FOREIGN KEY ('city') REFERENCES 'City'('name')
 ON DELETE RESTRICT

Where am I wrong? Isn't it the responsibility of the SQL's NO ACTION to delete the parent and leave the child orphan?


texts: Availability Group database stays in Reverting for too long after a failover

texts:
Architecture:
I have 2 Node Sync-Commit AlwaysOn configuration running on Multi-Subnet Failover Cluster. Primary node is in Europe and Secondary node is in US. I have only one database in the Availability Group which is OperationsManager db of SCOM.

Primary and Secondary Hosts are identical.
SQL Server Version on both boxes: 13.0.5237 & Windows Core
Update: I patched both servers to 10.0.5270.0 , it didn't help.
DB VLF count is only 27.

Problem:
When I initiate a failover, database fails over from Primary to Secondary node successfully in seconds. However, new secondary(old primary) database goes into Reverting / In Recovery phase and stays there for 30 minutes approximately.I also experienced same thing while failing back to original primary box so it's a problem which occurs both ways.

Findings:
I searched about this on internet and read documentation to investigate the issue. When role change from Primary to Secondary is finished, new secondary database goes through 3 phases:

Synchronization State: “NOT SYNCHRONIZING” ; Database State: ONLINE

Synchronization State: “NOT SYNCHRONIZING” ; Database State: RECOVERING

Synchronization State: “REVERTING” ; Database State: RECOVERING

In my case, all time has spent on last step. I also monitored the undo process by looking into perfmon counter "SQLServer:Database Replica Log remaining for undo"

I checked primary site before failover tests to spot any long running transactions or open transactions but couldn't find one. After failover, "Log remaining for undo" was around 30MB and it took 30 minutes for secondary database to go back to "Synchronized" state. When taking into consideration that we are running in Sync-Commit mode and there is a little workload on primary, it shouldn't take 30 minutes for redo phase imho.

SQL Server Error Log: I found this strange messages.

Remote harden of transaction 'RECEIVE MSG' (ID 0x000000004d52c65a 0001:01c4e415) started at Feb 22 2019  2:55PM in database 'OperationsManager' at LSN (2558:107841:1) failed.
Remote harden of transaction 'GhostCleanupTask' (ID 0x000000004d6d15aa 0001:01c4eaa0) started at Feb 22 2019  2:59PM in database 'OperationsManager' at LSN (2558:107843:46) failed.

Failover starts:

Failover ends:

All in all

Have you ever seen this problem before? Do you have any recommandations?


texts:
Suppose I have a website, and I want to add "Search a part" button, and there are two options "Dirt" or "ATV-UTV", and if for example the user choose "Dirt" to he need to choose "Beta" or "Gas" or "Honda", and if he choose "ATV-UTV" he should choose "Suzuki" or "mazda"

                                    [Search a part]
                                    /              \
                                   /                \
                                [Dirt]             [ATV-UTV]
                                /  |  \             /      \
                               /   |   \         [Suzuki]   [mazda]
                            [Beta][Gas] [Honda]

Lest's suppose that the user chose Search a part->Dirt->honda then I want to display the user all the available parts for this "Dirt Honda"

      -------------------------------------------
      |Part No| Price| Stock status| Description|
      -------------------------------------------
      | 56    | 25$  | In stock    | Repair Kit |
      -------------------------------------------
      | 21    |76$   | In stock    | Bearing Kit|
      -------------------------------------------
      | 99    | 54$  | Not In Stock| Repair Kit |
      -------------------------------------------

What's the best way for designing the DB for my website? I mean, how many tables should I build? Should I do 5 tables? on for "Beta" one for "Gas", one for "honda" and also for "Suzuki" and "mazda"?

Just need help on how should I design it, thank you for help


texts: Existing maintenance plan backup causing 3rd party SQL backup to fail

texts:
I have a problem with SQL Server backup on the MS SQL Server 2008R2 (SP1) 10.50.2500.

Basically there is a maintenance plan backing up SQL databases into the physical_device_id which is showing up as a string of numbers similar to UUID ({761607B3-XXXX-XXXX-XXXX-XXXXXXXXXXXX}11}), the software_vendor_id = 4608 and the user_name = NT AUTHORITY\SYSTEM.

When trying to access the existing maintenance plan:

use msdb

select * from sysmaintplan_sublpans
select * from sysmaintplan_plans

I have no output.

Any help will be much appreciated!


texts: Large-query optimization: given my "explain" results, what stands out?

texts:
I've got a monster query, which runs quite quick in many cases, but in some cases very much slower.

The EXPLAIN output below is from one of the slow cases.

I'm guessing that the slowdown is related to the very first row, the <derived2> table, which the fast queries do not use; my knowledge however, is limited. For one, I'm curious why that row pops up some times and not others.

To an experienced eye, what stands out here? What might be the first steps in speeding up this query?

+----+-------------+-------------------+--------+---------------+--------------+---------+------------------------------------+--------+----------------+
| id | select_type | table             | type   | possible_keys | key          | key_len | ref                                | rows   | Extra          |
+----+-------------+-------------------+--------+---------------+--------------+---------+------------------------------------+--------+----------------+
|  1 | PRIMARY     | <derived2>        | ALL    | NULL          | NULL         | NULL    | NULL                               | 322477 |                |
|  1 | PRIMARY     | o                 | eq_ref | uuid          | uuid         | 32      | pr.offering_uuid                   |      1 |                |
|  1 | PRIMARY     | pn                | eq_ref | uuid          | uuid         | 30      | brain.o.partnumber_uuid            |      1 |                |
|  1 | PRIMARY     | p                 | eq_ref | uuid          | uuid         | 32      | brain.o.product_uuid               |      1 |                |
|  1 | PRIMARY     | os                | eq_ref | uuid          | uuid         | 108     | brain.o.offering_source_uuid       |      1 | Using index    |
|  1 | PRIMARY     | b                 | eq_ref | uuid          | uuid         | 32      | brain.p.company_uuid_brand         |      1 |                |
|  1 | PRIMARY     | bn                | eq_ref | PRIMARY       | PRIMARY      | 3       | brain.b.company_name_id_legal_name |      1 |                |
|  1 | PRIMARY     | <derived4>        | ALL    | NULL          | NULL         | NULL    | NULL                               |  39895 |                |
|  1 | PRIMARY     | pmr               | ALL    | NULL          | NULL         | NULL    | NULL                               |     51 |                |
|  1 | PRIMARY     | pm                | ALL    | NULL          | NULL         | NULL    | NULL                               |    215 |                |
|  4 | DERIVED     | c                 | ALL    | NULL          | NULL         | NULL    | NULL                               |  39895 | Using filesort |
|  4 | DERIVED     | ct                | ref    | company_uuid  | company_uuid | 108     | brain.c.uuid                       |      1 | Using index    |
|  2 | DERIVED     | <derived3>        | ALL    | NULL          | NULL         | NULL    | NULL                               | 322477 | Using where    |
|  3 | DERIVED     | m3_offering_price | ALL    | NULL          | NULL         | NULL    | NULL                               | 322477 | Using filesort |
+----+-------------+-------------------+--------+---------------+--------------+---------+------------------------------------+--------+----------------+


texts: Is it possible to disable all mysql_connect(): Too many connections emails, but not other errors?

texts:
Right now I get about 4000 emails daily from this error as I run a DB backup that clogs up the DB for a few mins ant these emails are getting enormous.

Wondering if its possible to stop mysql from sending me emails about this


texts: How do I insert a row which contains a foreign key?

texts:
Using PostgreSQL v9.1.  I have the following tables:

CREATE TABLE foo
(
    id BIGSERIAL     NOT NULL UNIQUE PRIMARY KEY,
    type VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE bar
(
    id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    description VARCHAR(40) NOT NULL UNIQUE,
    foo_id BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT
);

Say the first table foo is populated like this:

INSERT INTO foo (type) VALUES
    ( 'red' ),
    ( 'green' ),
    ( 'blue' );

Is there any way to insert rows into bar easily by referencing the foo table?  Or must I do it in two steps, first by looking up the foo type I want, and then inserting a new row into bar?

Here is an example of pseudo-code showing what I was hoping could be done:

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     SELECT id from foo WHERE type='blue' ),
    ( 'another row', SELECT id from foo WHERE type='red'  );


texts:
I've got one table called "Units_Sold" which has these fields:
year, month, week, country, units
and another table called "Dates" which has these other fields:
year, week, month
The dates table has 1 entry per week/month/year
The Units Sold Table has more than 1 record per week, month, year as it is multiple units sales on a given week
What I'd like is to get the missing units sales weeks BY COUNTRY, this is, all the weeks in which there are no unit sales
I'm not sure how to approach this, if I join the 2 tables I get the weeks in which unit sales is present, that's not an issue, how can I get the inverse by country? that is, all weeks for each country in which there were no sales at all...


texts: Correcting and Detecting overlapped dates with CTE


texts: Still wrong to start the name of a user stored procedure with sp_?

texts:
One of my co-workers named a stored procedure in our SQL Server 2008 R2 database sp_something. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this online article that explains why it is wrong, so I could provide my co-worker with an explanation.

In the article (by Brian Moran) it is explained that giving the stored procedure an sp_ prefix makes SQL Server look at the master database for a compiled plan. Because the sp_sproc doesn't reside there, SQL Server will recompile the procedure (and needs an exclusive compile lock for that, causing performance problems).

The following example is given in the article to show the difference between two procedures:

USE tempdb;
GO

CREATE PROCEDURE dbo.Select1 AS SELECT 1;
GO

CREATE PROCEDURE dbo.sp_Select1 AS SELECT 1;
GO

EXEC dbo.sp_Select1;
GO

EXEC dbo.Select1;
GO

You run this, then open the Profiler (add the Stored Procedures -> SP:CacheMiss event) and run the stored procedures again. You're supposed to see a difference between the two stored procedures: the sp_Select1 stored procedure will generate one more SP:CacheMiss event than the Select1 stored procedure (the article references SQL Server 7.0 and SQL Server 2000.)

When I run the example in my SQL Server 2008 R2 environment, I get the same amount of SP:CacheMiss events for both procedures (both in tempdb and in another test database).

So I am wondering:

Can I have done something wrong in my execution of the example?
Is the 'do not name a user sproc sp_something' adagium still valid in newer versions of SQL Server?
If so, is there a good example that shows its validity in SQL Server 2008 R2?

Thanks a lot for your thoughts on this!

EDIT

I found Creating Stored Procedures (Database Engine) on msdn for SQL Server 2008 R2, which answers my second question:

  We recommend that you do not create any stored procedures using sp_ as
  a prefix. SQL Server uses the sp_ prefix to designate system stored
  procedures. The name you choose may conflict with some future system
  procedure. [...]

Nothing is mentioned there about performance problems caused by using the sp_ prefix though. I'd love to know if that's still the case or if they fixed it after SQL Server 2000.


texts: myql performance : extremely slow inserts on a faster box

texts:
tldr; When migrating a stack to its pre-prod environment, i am confronted with a setup script taking 50x the amount of time to execute compared to my dev box. error logs are dead silent !

dev :
    osx, mysql 5.7, SSD, plenty of memory

pre-prod :
    debian 8.6, mysql 5.6, faster SSD, faster/more memory, faster core.

the PHP script:

    private function _addCityFile($fn)
    {
        $this->log->info(__METHOD__ . " start.");

        $this->log->debug("Adding city file [$fn]");

        try {
            /** @var \PDO $pdo */
            $pdo = $this->pdo;
            $this->pdo->beginTransaction();
            // InnoDB table
            $sql  = "INSERT INTO City SET country=:co,city=:ci,state=:st,zip=:zip";
            $stmt = $pdo->prepare($sql);

            $handle             = fopen($fn , "r");
            $csv_time           = 0.0;
            $pdo_time           = 0.0;
            $index              = 0;
            $intermediate_count = 100;           // echo every 100 records
            $total_count        = 0;

            if ($handle) {
                fgets($handle);                 // skip first line
                while (true) {
                    $index++;
                    $total_count++;
                    $start_csv_once = microtime(true);
                    $ary            = fgetcsv($handle);
                    $end_csv_once   = microtime(true);
                    $csv_time_once  = $end_csv_once - $start_csv_once;
                    $csv_time       = $csv_time + $csv_time_once;

                    if ($ary) {
                        $country = $ary[0];
                        $state   = $ary[1];
                        $city    = $ary[2];
                        if (!$city || $city === '') continue;
                        $zip = strtoupper($ary[3]);
                        if (!$zip || $zip === '') continue;
                        $stmt->bindValue('co' , $country , \PDO::PARAM_STR);
                        $stmt->bindValue('ci' , $city , \PDO::PARAM_STR);
                        $stmt->bindValue('st' , $state , \PDO::PARAM_STR);
                        $stmt->bindValue('zip' , $zip , \PDO::PARAM_STR);
                        $start_pdo_once = microtime(true);
                        $stmt->execute();
                        $end_pdo_once  = microtime(true);
                        $pdo_time_once = $end_pdo_once - $start_pdo_once;
                        $pdo_time += $pdo_time_once;
                        $ct = $csv_time * 1000;
                        $pt = $pdo_time * 1000;
                        if ($index >= $intermediate_count) {
                            $this->log->debug("After $total_count : csv [{$ct} ms.], pdo [{$pt}]");
                            $index = 0;
//                          if ($total_count > 1000) break;
                        }

                    } else {
                        break;
                    }
                }
                $start_commit = microtime(true);
                $this->pdo->commit();
                $end_commit = microtime(true);
                $delayInMs  = ($end_commit - $start_commit) * 1000;
                $this->log->debug("Commit took [$delayInMs] ms.");
                fclose($handle);
            }
        } catch (\Exception $e) {
            $this->pdo->rollBack();
            $this->log->error(prettyStack($e , "While processing [$fn]"));
        }
        $this->log->info(__METHOD__ . " end.");

    }

the puzzle:

the script takes about 3.5 seconds per 1000 records on pre-prod, dev takes .07 seconds/1000 records on dev.

Following some guidance i found on SO, i added the beginTransaction / commit.  With that, i get .04 seconds (!!!) /1000 records on pre-prod vs .06 on dev.

the question

There must be some obvious mysql setting that explains this rather large difference. I compared both configurations, but cant find anything fundamentally different except 5.7/5.6. Any suggestions in what direction to look?


texts: How to properly cut/filter binlog file to perform single table PIT?

texts:
I've been trying to perform a single table PIT.
After cutting off a dump the portion related to the table I want to restore in time, I've been trying to do a similar operation with the binlog file isolating first, the period of time of interest and then, the parts related to the specific table.
Regarding "the parts" I think that here lies the source of error that I get when trying to apply the extracted portion of binlog that is:
ERROR 1609 (HY000) at line 8: The BINLOG statement of type
Table_map was not preceded by a format description BINLOG
statement.

My shaped binlog file, looks like this:
/!/;
# at 291
#201227 10:20:01 server id 1  end_log_pos 365 CRC32
0x7bd7d7b7  Table_map: agef.tabelladiprova_pet mapped to
number 199
# at 365
#201227 10:20:01 server id 1  end_log_pos 423 CRC32
0xcd6cb3d7  Write_rows: table id 199 flags: STMT_END_F

BINLOG '
[long string here]
'/!/;
# at 423
#201227 10:20:01 server id 1  end_log_pos 454 CRC32
0x78e6af69  Xid = 167
COMMIT/!/;
# at 454
#201227 10:21:02 server id 1  end_log_pos 519 CRC32
0x4ee9d99e  Anonymous_GTID  last_committed=1
sequence_number=2    rbr_only=yes
/!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED//!/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/;
# at 519
#201227 10:21:02 server id 1  end_log_pos 591 CRC32
0x7487c643  Query   thread_id=2     exec_time=0
error_code=0
SET TIMESTAMP=1609060862/!/;

/!/;
# at 591
#201227 10:21:02 server id 1  end_log_pos 665 CRC32
0x201999c0  Table_map: agef.tabelladiprova_pet mapped to
number 199
# at 665
#201227 10:21:02 server id 1  end_log_pos 719 CRC32
0xa4a6e21d  Write_rows: table id 199 flags: STMT_END_F

BINLOG '
[long string here]
'/!/;
# at 719
#201227 10:21:02 server id 1  end_log_pos 750 CRC32
0x10fbda16  Xid = 168
COMMIT/!/;
# at 750
#201227 10:21:34 server id 1  end_log_pos 815 CRC32
0x33a1e7bf  Anonymous_GTID  last_committed=2
sequence_number=3    rbr_only=yes
/!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED//!/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/;
# at 815
#201227 10:21:34 server id 1  end_log_pos 887 CRC32
0x505d2069  Query   thread_id=2     exec_time=0
error_code=0
SET TIMESTAMP=1609060894/!/;


texts: Error granting SELECT on view that uses ALL_OBJECTS

texts:
I'm in the middle of testing Oracle 12c for our application. I have something similar to this view in a particular schema, which we'll call MY_OWNER_SCHEMA for this question:

CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
    OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
;

The view is a sort of "metadata" view, used to verify (programmatically) that all tables and views have a comment. Since MY_OWNER_SCHEMA is locked except during deployment, we grant permissions to MY_LOGIN_USER to SELECT from it:

GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;

(This GRANT is executed during deploy while logged in as MY_OWNER_SCHEMA.)

This worked just fine in Oracle 11g, and creating it works in 12c as well. However, in 12c only, the GRANT fails with the following message:

SQL Error: ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view or a view was being replaced
           and the grant option was not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view or
           revoke existing grants on the view.

This is fairly odd, since ALL_OBJECTS is a public interface accessible to all users, and it only shows objects to which the user already has some access. Did something change with the permissions model or default permissions settings in Oracle 12c? Is it possible that the database was installed differently? Is there a way to get around this error without giving MY_OWNER_SCHEMA explicit permission to GRANT to this global system view?


texts:
Fiddle: https://www.db-fiddle.com/f/m4vsq4ERyBhiBNdZqALmVP/0
I have two indexes, one for some_other_id and one forcreated_at , some_other_id.
The select query uses the first index on production server (~23M rows) while it uses the second one on my dev machine (1 row).
Server explain:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: symbol_details
         type: ref
possible_keys: symbol_details_some_other_id_index,symbol_details_created_at_some_other_id_index
          key: symbol_details_some_other_id_index
      key_len: 8
          ref: const
         rows: 24152
        Extra: Using where

dev explain:
           id: 1
  select_type: SIMPLE
        table: symbol_details
         type: range
possible_keys: symbol_details_some_other_index,symbol_details_created_at_some_other_id_index
          key: symbol_details_created_at_some_other_id_index
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using where; Using index

Also in fiddle it is not using the second index, while clearly having limits on created_at.
Is is because of the number of rows?
I want to use such queries in production, but this way it is way too slow. A single query like this returns ~100 rows out of ~23M rows, and I thought the second index would cover this query, but it seems it somehow does not.
Any idea on how to use index, or created another index for this query, or modify query to use index.
The best solution would be to not touch the query, since it is generated by ORM. Using hints in query while using ORM is not exactly clean, but doable if it is the only choice.
Thanks!
Dev Mariadb version 10.4.13 and production is 10.4.12 (running in docker btw).
EDIT:
The first index is being used somewhere else, and it needs to be there (index on some_other_id). Maybe I can merge the two indexes into on? IDK yet.
EDIT2 why I chose to have two index:
Since some_other_id's cardinality is ~70k and created_at is ~400k I chose have second index to filter created_at first. Now I'm thinking if I was right to think this way or not.


texts: .NET SQL Server Authentication Schema issue

texts:
I am currently working on a project for a client. The application is written in ASP.NET C#. The Database used is SQL Server 2008. The project has been to migrate the old Oracle database to a new SQL Server database so it connects to the .NET code and works correctly. We used Microsoft Microsoft SQL Server Migration Assistant (SSMA) to convert the Oracle database across to SQL server. This worked for the most part, however we did have to make some changed to the stored procedures and some functions.

The problem is that now when i try and run the .NET code it will not pick up any of the stored procedures or functions as they now have a schema prefix and the .NET code does not pick this up.

The .NET code connects to the SQL Server database using Windowws Authentication and therefore i am unable to add a default schema to the user or login within SQL Server as it is a group.

Is there any way i can get around this?

If you would like more information i will be happy to provide it.

Thanks

Connection string

<add key="DatabaseConnectionString" value="Data Source=DATASOURCE;Initial Catalog=DATABASE;Integrated Security=true;"/>

Stored Procedure Call

// Open a connection to the database
            SqlConnection databaseConnection = GetDatabaseConnection();

            // Initialise the SQL command object
            SqlCommand cmd = new SqlCommand("PKG_PACKAGE_NAME$PR_PROCEDURE_NAME",
               databaseConnection);
            cmd.CommandType = CommandType.StoredProcedure;

Boldonglen


texts: How to get the Total disk space in ms sql 2008 or below

texts:
I am using ms sql 2008, I wanted to get the total space and available free space on my drives.I used xp_fixeddrives. Unfortunately I got only free disk Space.So does anyone have any other solution so as to get both total disk space and available disk space in ms sql 2008 or below?

CREATE TABLE #drives (
    drive char,
    [free] int
)

INSERT INTO #drives
EXEC master..xp_fixeddrives

For ms sql 2008 R2 and above I can get the total drive space using this query:

WITH core AS (
    SELECT DISTINCT
        s.volume_mount_point [Drive],
        CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs]
    FROM
        sys.master_files f
        CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
)


texts: How to get db "state version seq" in postgres?

texts:
I need to run a script on every database change. The script works on diffs, so I need to know if I handled the previous state (the handler script could be down for some time for example).

One way I could implement it is to create a trigger on every change which would increment a sequence. Then I just check if current_sequence_value == previous_sequence_value + 1.

I'm not that happy with making every single write require the sequence update, so the question is: can I get such "state version seq" using some kind of built-in metadata?


texts: Does querying fragmented tables in SQL Server Affect the database?

texts:
On SQL Server 2008, I'm running the following query on a 220GB database to list out the indexes and their levels of fragmentation:

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent, avg_fragment_size_in_pages, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
ORDER BY indexstats.avg_fragmentation_in_percent DESC

It's taken 8 minutes and then I cancelled the query, as it's a production DB (no DEV to test on), on this page someone stated it affects the server and should be run at slow times:

https://serverfault.com/questions/84118/how-long-does-reindexing-take-on-sql-server

I'm just curious how it would impact the server, short of using memory/cpu for the query, is it causing locks on tables I'm not aware of?


texts: SQL - sum columns with same name from diferent tables

texts:
I have 3 Tables:

_tbl1_____
ID  C1  C2
--  --  --
AA   2   3
BB  10  12
CC  20  22

_tbl2_____
ID  C1  C2
--  --  --
BB  10  12
AA   2   3
CC  20  22

_tbl3_____
ID  C1  C2
--  --  --
AA   0   0
BB  10  12
HH  55  66

and I want sum the columns with same name and rows with same id
to get a result like this:

Query result tbl
ID  C1  C2
--  --  --
AA   4   6
BB  30  36
CC  40  44
HH  55  66

Thank you guys for your help.. I really need it.


texts: How to increment identity without add a row and get the skipped value?

texts:
I'm trying to preserve value in identity column in manner to be added later. The value should be used in implicit foreign key when there is no constraint.
For example:
I have this table:
Create table [t](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [value] [varchar](255) Not NULL
)

I want to increment the id without adding line, and get the id, (similar to oracle sequence)
I'm trying to read current sequence value:
Select IDENT_CURRENT('t') as id

result:
id
----
1

trying to increment, use insert with default values:
insert into t default values

result:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'value', table test.dbo.t; column does not allow nulls. INSERT fails.
The statement has been terminated.

I'm trying to read current sequence value:
Select IDENT_CURRENT('t') as id

result:
id
----
2

Means the sequence increment had been succeeded, even though no row was added.
Now I need a way to read the skipped value in a "scope safe" way.
SCOPE_IDENTITY and @@IDENTITY doesn't been changed unless the insertion was succeeded, and IDENT_CURRENT('t') is not "scope safe".
Does anyone have any idea how can I do so?
I prefer a solution that I can use for any table, not limited for a specific table (for specific table: like insert full row and delete it - I need to know the column names).


texts: Why does MySQL store blobs in the direct table rather than off to the side so that if it needs to read the corresponding stuff it will read the stuff off to the side?  Essentially it would create its own file that was protected from everything except itself in a file-folder type of architecture so that it would be easier/quicker to write out a blob link.


texts:
Sorry for probably a very basic question, but in the literature and online, I have came across two different definitions of weak entities, which may sometimes be contradictory.

1) Weak entity is an entity which cannot exist without some other (owner) entity.

2) Weak entity does not have a primary key, but rather a partial key, and can be only uniquely identified by combining this partial key with a foreign key from owner entity.

Which one of these is true? Let us take the example of Customers->Orders relationship, where Orders have a unique OrderID. Here an Order cannot exist without a Customer, however it still has its own primary key. Would it be a strong or a weak entity then?


texts: Microsoft SQL server 2008 and linked servers

texts:
I'm designing a set of databases which might need to use each other's stored procedures or tables. The reason that I'm creating separate databases is that in the future I want to be able to move any of these databases to a dedicated server for itself if required and just create a linked server to the other server(s). however doing so I also need to change all the codes which are accessing the other databases.

does anyone know of any way to avoid this later modification in case of using a linked server ?

or in other words :

Is that possible to have stored procedures which would work regardless of the fact that it needs to access to a linked server or same local server ?

example :

DatabaseA and DatabaseB are both located on SQLInstannceA
Sp on DatabaseB :

SELECT * FROM DataBaseA.dbo.tbx

and now if I move DatabaseB to SQLInstanceB, and create a linked server to SQLInstanceA, I have to change the SP code to the following :

SELECT * from linkedserverName.DatabaseA.dbo.tbx

is there anyway to avoid this change in case of moving databases ?


texts: pulling most recent results for each point based on date field

texts:
so I have tried a few methods now mostly using max(date) and not really understanding row (in) and aggregate. I have two tables in which I am doing a join that requires some field formatting to associate the data. I also have something like 36 fields I need to pull in additionally from both separate tables. I keep stumbling on where to make a change.
this portion I know works:
SELECT a.auto_id, a.circuitname, a.work_verification_status, ...
FROM evm_vp A
LEFT JOIN TAT B
ON UPPER(A.EVM_SYNC_ID) = REPLACE(REPLACE(B.tree_global_id, '{', ''), '}}', '')
WHERE B.TREE_GLOBAL_ID IS NOT NULL

this portion works, however I can't pull all the other fields like I need.
SELECT A.AUTO_ID, MAX(A.EVM_LAST_EDITED), ...
FROM evm_vp A
LEFT JOIN TAT B
ON UPPER(A.EVM_SYNC_ID) = REPLACE(REPLACE(B.tree_global_id, '{', ''), '}}', '')
GROUP BY A.AUTO_ID
ORDER BY A.AUTO_ID

The join is fairly important.


texts: Help understanding database design logic for a secondhand bookstore project in MySQL

texts:
I am currently working on a small eCommerce based project that allows users to buy/sell second hand books to/from other users. I am having a hard time making design decisions on how to model the database. My requirements are as follows;

Users (should be able to both buy and sell books)
- User can sell a book.
- User can buy a book.

Temp Users
- This is for registering a user through email

Orders
- many orders can be made by one user.

Order_Details (I am yet to create it)
- stores information on each product that has been orderd

Products/Books
- many products belong to one category

Here is my first try at the design;

I'm having an issue on how to incorporate buying and selling for one user.I don't want to create a second users table for selling (unless that's the only way to do it.) products only.
Should I create a "sales" table where it holds the users_ID and information that holds the book that is meant to be sold?Would this be a temporary table like the temp_users table?

Any opinions would be helpful.

Regards


texts:
I have a large virtual SQL Server (Full 2008R2).

I run 3 SQL Server instances and would like to relocate the TempdB database file to another location, splitting off from the TempdB log file.

The trouble I am having is that, even though the query ALTER DATABASE executes successfully, and then restarting that particular instance's SQL Server Service, does not relocate that database file.

Do I have to restart the whole server to move this database?


texts: How does MySQL rotate binary log files when number extension reaches its maximum?

texts:
Assume the bin-log file format is: something-bin-log.000122. With each file has a maximum of 1GB size before rotating, and with cap on the number of old binary log files - say 14 days.

What will be the next file when the current one is something-bin-log.999999 and it is about to get rotated?

Will the next binary log file be something-bin-log.000001? If so, how to prevent this from happening and use a bigger numeric extension instead?


texts: How does MySql database handles multiple update operation

texts:
I am new to database and the way they work so my question might be a bit dumb, please be nice.

I have always been thinking about how database update work in general and I think I understand the process kind of, but my real curiosity is that how does database engines in general handle update to an existing record from multiple threads?

To be clear, let say your mysql database got 10 queries at the same time to update a particular same field, how is this operation handled?
From my point of view, If the database got updated at the same time maybe by the help of multi threading, then the data on that field might not be accurate.

Another thing that came to my mind is Synchronization, coming from java point of view, I am thinking the database will queue the request and make sure one operation finishes before another.

I need clarity on this, any help and I will be grateful.

Thanks in advance.


texts: Slowly changing dimensions - data integrity

texts:
When using SCD is it possible to still enforce FK contraints. Consider the following table structure:

customer (data_version_id PK, customer_id PK, name, address1,...etc)
order(order_id PK, customer_id FK, data_version_id FK, order_no, ....etc)

A data version is used as a snapshot of the state of all reference data (e.g. customers, products, suppliers etc) at a given point in time. This allows changes to be made to data without them instantly going live, when all data changes have been checked (usually once a week) they are all pushed out at once. The reference to data_version_id in the order table is basically to store which data_version was used when the order was created. That way we can query what the customers address was at that time, what prices were in place etc.

This currently works well and allows us to enforce contraints ensuring that even if a code fault occurs whereby a query attempts to insert an order for a data version where a customer does not exist (due to a code fault), SQL would error due to the FK relationship.

With data versions in our system incrementing relatively frequently the majority of records in the customer table will simply be duplicated for each data version. We are looking at changing the data structure to more of an SCD structure so we have data_version_start & data_version_end in each reference data table eg:

customer (data_version_start PK, data_version_end PK, customer_id PK,  name, address1,...etc)
order(order_id PK, customer_id , data_version_id, order_no, ....etc)

This would dramatically reduce the number or records in our customer table as we would only require a record for each change but in doing this we can no longer have an FK constraint from order to customer based on data version allowing the potential of erroneous data along with losing core sql functionality e.g. cascade deletes, removing a customer would require all orders to manually be removed.

Are we missing a trick or is this just a compromise of this approach?

We are using SQL 2008 R2 - but are looking to upgrade to SQL 2014 in the near furture (if this makes any difference).


texts:
I have following readings table, it has around 500 million rows.

CREATE TABLE readings (
   id int(11) NOT NULL AUTO_INCREMENT,
   value double NOT NULL,
   created_dt datetime NOT NULL,
   device_id int(11) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),
   KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id),
   CONSTRAINT readings_device_id_6a03c4ab761154d1_fk_device_id FOREIGN KEY (device_id) REFERENCES device (id)
) ENGINE=InnoDB AUTO_INCREMENT=138611438 DEFAULT CHARSET=latin1

It has an index on (created_dt, device_id) and an index on device_id( which is foreign key)

When i run following query

SELECT value, created_dt
FROM readings
WHERE (created_dt BETWEEN '2019-03-17 19:11:00' AND '2019-03-18 19:11:00'
  AND (device_id) IN (10, 11, 12));

Above query takes around 2 minutes.

DESCRIBE query returns the index used is readings_device_id_6a03c4ab761154d1_fk_device_id which is foreign key index on device_id. However if i remove the above index and run the query again, it uses readings_created_dt_483d31a3654ede43_uniq which is an index on both (created_dt, device_id) and retuns data in less than a second.


texts: Oracle 12c how to purge the FGA audit records before a certain date

texts:
please I need to know how to purge the FGA audit records before a certain date, I have an Oracle 12c R1 database in which the audit with FGA is implemented, the tables of this are in a tablespace called ts_audit.dbf that It has already had 3 full datafiles and continues to be filled at a dizzying pace due to the intensive use of the database, for this reason I need to purge the oldest records, let's just leave those of the last week.
Thank you very much for your help.


texts: Column Store Indices versus Dimensional Tables

texts:
I have a Data Warehouse and want to think ahead one year when I may get a database update allowing me to use column store indices.

My dimensional tables are mostly junk dimensions containing columns that categorize the data in the fact tables. I use those dimensional tables mainly to compress the data (x10 roughly) and also get faster range searches.

Are those columns in my dimensional tables good candidates to be moved back into the fact table as part of a column store index?


texts: CPU impact, Scan count 9, logical reads 1686, physical reads 0 vs Scan count 1, logical reads 10253, physical reads 20

texts:
I have some 3rd party software that is doing so often the same update query, on 10 million rows table with 1 GB data

(@P1 bigint,@P2 bigint,@P3 bigint,@P4 bigint,@P5 bigint)
UPDATE top(1000) hsi.hspendingitems
   SET status = @P1
WHERE handlertype = @P2
AND status in (@P3,@P4,@P5)

Initial Results (without modifications)

Query 1 plan:

https://www.brentozar.com/pastetheplan/?id=Syv7OxRHW

Query IO statistics

Table 'hspendingitems'. Scan count 9, logical reads 1686, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Additional Index

I then added an index:

CREATE NONCLUSTERED INDEX [ix_test_dba] ON [hsi].[hspendingitems]
(
[handlertype] ASC,
[status] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [DBSpace8]

Results with New Index

Query 2 plan:
https://www.brentozar.com/pastetheplan/?id=S1yjulAS-

Query statistics:

Table 'hspendingitems'. Scan count 1, logical reads 10253, physical reads 20,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Is it better for SQL Server to do less scans but more logical reads?

I noticed that the cost plan is also different:

Initial Cost 102.937 vs with Index 0.0232853 ?

Vendor is aware to add that index, what do you think I should push vendor for adding that index?


texts: Assign value to a field after checking a condition

texts:
I have this query:

ALTER TABLE MERCHANTS
   ADD COLUMN merchant_image bytea,
   ADD COLUMN merchant_logo bytea,
   ADD COLUMN merchant_address VARCHAR(100),
   ADD COLUMN merchant_phone VARCHAR(50),
   ADD COLUMN order_type VARCHAR(15)
      CHECK(order_type IN ('TAKE_AWAY', 'HOME_DELIVERY','NO_ORDERING')),
   ADD COLUMN open_until TIME,
   ADD COLUMN order_until TIME

For order_until I have this condition: if order_type is 'NO_ORDERING' then its default value should be 0. How can I write this, any suggestions?


texts:
I am getting below issues while accessing SSRS.
Can anyone let me know if you have faced this issue before.

  The RSReportServer.config file has been modified.

  Report ServerWindows Service (MSSQLSERVER) cannot connect to the report server
  database. The value for UrlRoot in RSReportServer.config is not valid.
  The default value will be used instead.

  library!DefaultDomain!2b94!12/02/2016-19:48:43:: w WARN: Initializing
  UrlRoot to default value of ''  because it was incorrectly specified
  in Configuration file as ''.

  INFO: Registered url=http://+:81/Reports/, vdir=/Reports,
  pdir=C:\Program Files\Microsoft SQL
  Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager.

  library!DefaultDomain!4c0!12/01/2016-18:35:35:: e ERROR: Throwing
  Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException:
  ,
  Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException:
  The report server cannot open a connection to the report server
  database. A connection to the database is required for all requests
  and processing. ---> System.Data.SqlClient.SqlException: Cannot open
  database "ReportServer" requested by the login. The login failed.

I clicked the "test connection" button in the Reporting Services Configuration Manager and it succeeds.


texts: Options for transferring/transforming from one database to another?

texts:
We have a hosted instance of intersystems-cache.  There is an aggregate log that is near 600 million rows and is rather abstract and complicated.  I am planning to parse this log and store more meaningful events in an on-site database for easier reporting.  I'm trying to figure out what my options are.

The only way I can access the data is over a VPN using ODBC or JDBC and Caché's SQL implementation.

What I'm looking for:

 1. Middleware solution.
    a. Scripting solution?  VBScript, Python, Node?
    b. 3rd Party Software?  Some sort of Service Bus or similar?
    c. Other options?
 2. Target Database.  Postgre? MySQL? Other? Needs to be free.

Regarding the "Scripting Solution", obviously I'm not looking for someone to write the script.  I just want to know if this is a feasible approach.  I have actually started down this path with VBScript with limited success.  I was just hoping to find better options.

I looked for a "transformation" tag or similar but couldn't find it. Sorry if the tags or wrong or if this simply doesn't belong on dba.se.


texts: MySQL installation and data location on Windows

texts:
I'm gonna install MySQL 5.5 in a virtual Windows Server. I'd like to have the data in an separate partition (it may be in a different virtual disk) than the system one. I'm considering two options:

Install MySQL in the default installation path ("C:\Program Files\MySQL\MySQL"), create the data folder in the "D:\mysqldata" and modify the "my.cnf" to point to the new folder
Install MySQL in directly in  "D:\mysql"

Are there any difference between then? Is there a recommended approach?


texts:
I have a form that saves 2 narratives and a select option.  If I place to many characters in the narrative box it will not save to the mysql table, but if I decrease the characters then it will save just fine.

I have set php.ini to allow longer post time var sizes so im not sure if I am missing anything else in the php.ini that anyone can think of.

Thank for any suggestions.


texts: Issuing Inserts/Updates to Progress OpenEdge via ODBC

texts:
I'm attempting to build some ETL to integrate our SQL Server environment with a remotely hosted 3rd party app running Progress OpenEdge. I have a linked server to it set up in SQL Server 2014.
Selects are working well enough, though I find that using openquery is much, much faster than the simpler select * from SERVER.DB.SCHEMA.TABLE syntax.
I'm struggling with writes, however. I've found several references for valid update syntax for linked servers, but these all give permissions errors:
select * from openquery(SERVER,'update FOO.BAR set "COL" = 0 where "COL" = 1')

update fb set [COL] = 0
   from SERVER.DB.FOO.BAR fb
   where [COL] = 1

update openquery(SERVER,'select * from FOO.BAR where "COL" = 1')
       set [COL] = 0

This syntax is the only one I've found that works:
declare @script nvarchar(max) = N'update FOO.BAR set "COL" = 0 where "COL" = 1'
execute (@script) at SERVER

This works for both inserts and updates, but I can't see a way to load a set of data this way, because I can't reference my local tables.
In SSIS, an ODBC connection passes the connectivity test, but fails during initialization:

Error: 0xC0014020 at Package1, Connection manager "SERVER": An ODBC error -1 has occurred.
Error: 0xC0014009 at Package1, Connection manager "SERVER": There was an error trying to establish an Open
Database Connectivity (ODBC) connection with the database server.
Error: 0x20F at Data Flow Task, ODBC Destination [2]: The
AcquireConnection method call to the connection manager SERVER failed with error code 0xC0014009.  There may be
error messages posted before this with more information on why the
AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: ODBC Destination
failed validation and returned error code 0x80004005.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more
component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task
validation.

Ultimately, I'm searching for a better way to merge/upsert a set of data (anything from a few dozen rows to a couple hundred thousand) without resorting to using RPC to insert/update one row at a time.


texts:
I am writing a simple procedure to backup a table-

CREATE PROCEDURE daily_backup()
BEGIN
   DECLARE given_date VARCHAR(25);
   SET given_date = now();

   CREATE TABLE given_date LIKE db1.table1;
   INSERT INTO given_date SELECT * FROM db1.table1;

END

But it creates a table with name given_date . I want to create a table with date as name.
How to do the same ?


texts:
What would account for the difference in table sizes between one PostgreSQL database and another restored from its dump file?

On an Ubuntu 12.04 host, I'm running PostgreSQL 9.1, and I dumped my database using:

pg_dump -c -U myuser --blobs --format=c mydb -n public > mydb.dump

Prior to the dump, with autovacuum enabled, the table mytable was listed in pg_tables as being 31GB in size and has 21961903 rows.

I loaded this dump file onto a separate Ubuntu 12.04 host, using:

pg_restore --jobs=8 -U postgres --create --dbname=mydb mydb.dump

It loaded mostly fine, only reporting one error creating an index because of an "unexpected end of data" on table mytable, which it says it ignored. However, pg_table reports that the size of mytable is only 7.7GB but still has 21961903 rows.

Why the disparity in table size measurements? Should I be concerned with the error creating the index on my table?


texts:
For setting up Always On Availability Groups in SQL Server I know you need to have at least Enterprise Edition for SQL Server 2012, 2014, or 2016.

Do you also need the Datacenter or Enterprise Edition of Windows Server 2012 or 2016 for Always on, or can you set this up properly on Windows Server Standard 2012 or 2016?


texts:
I have been googling for ages and messing around with the package properties but I can't seem to be able to get my package to run as a different user than my current windows login from within Visual Studio.

In particular I want a connection manager to run as a different windows account and not a SQL account.

Can anyone advise?


texts: Is there a way to force Deferred Name Resolution even if the table exists when creating a stored procedure?

texts:
When creating a stored procedure in SQL Server, you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that table (Deferred Name Resolution).

Is it possible to instruct SQL Server to defer the name resolution of all tables referenced in a procedure irrespective of whether they exist or not? I do want to keep the general syntax checking, so even if it were possible, hacking the stored procedure definition into a system table isn't an option.

I expect my asking to do this might seem a little bit weird, so here's some background: I auto generate table definitions and stored procedures from an application written in C# and it's very difficult for me to change the code to order the changes as SQL needs them. My code "guarantees" that the schema is consistent within a transaction, but currently I can't guarantee that the table columns are defined before I define the stored procedure which references them.

Below is a canonical example of the SQL created by the C# which "illustrates" the problem I'm trying to solve.

--Say this table already exists.
CREATE TABLE myTable
(
    a NVARCHAR(MAX)
)
GO

--My C# code creates something like this
BEGIN TRAN
GO

--the stored procedure gets generated first.
CREATE PROCEDURE mySproc
AS
BEGIN
    SELECT a,b FROM myTable
END

--then the table update
ALTER TABLE myTable
    ADD b nvarchar(MAX)

COMMIT TRAN

It is possible for me to fix this in the C# code, but I'm hoping for a simple "magic" tweak I can pull in the SQL. This will save a lot of time for me.


texts: Problem granting user privileges via roles in Oracle 12c

texts:
I'm starting to learn Oracle 12c so please excuse my bad terminology. I wrote this basic script to set up a user and privileges:

CREATE USER wertyq_db_usr IDENTIFIED BY justatest ACCOUNT UNLOCK DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT";
ALTER USER wertyq_db_usr QUOTA 100M ON USERS;
GRANT RESOURCE, CONNECT TO wertyq_db_usr;
GRANT EXECUTE ON CTX_DDL TO wertyq_db_usr;
GRANT EXECUTE ON "CTXSYS"."CTX_DDL" TO wertyq_db_usr;
GRANT UNLIMITED TABLESPACE TO wertyq_db_usr;
CREATE ROLE wertyq_usr_role;
GRANT CREATE PROCEDURE TO wertyq_usr_role;
GRANT CREATE TRIGGER TO wertyq_usr_role;
GRANT CREATE SESSION TO wertyq_usr_role;
GRANT CREATE VIEW TO wertyq_usr_role;
GRANT wertyq_usr_role TO wertyq_db_usr;

The goal is to create a user and a role, then grant privileges to the role, then assign the role to the user so the user inherits the privileges. I should also mention that I can connect to the database as SYSTEM user.

However, a third party application I am also using, which should connect to my Oracle database using this user created above, it behaves like the user doesn't have any privileges and it won't create any tables/views/ etc in the database. So I grant the privileges directly to the user and the third party application functions correctly. So I remove the privileges from the user and grant the role to the user again, and the third party application again won't create tables/views/ etc.

So I am wondering if the third party application has a bug, or if an Oracle user doesn't inherit privileges from roles in the manner I thought it would?  I would appreciate your insights :)

Thank you!


texts: Trigger that will stop from deleting if a foreign key record is not deleted

texts:
I've got two tables: Countries (Id, Name) and Cities (Id, CountryId, Name), CountryId is a Foreign Key to Cities.

I need a trigger that will prevent deleting certain Cities if the Country is not being deleted, in other words certain Cities can only be deleted when Country is being deleted.

CREATE TABLE Countries
(
    Id [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Name [VARCHAR] (100) NOT NULL
);

INSERT INTO Countries VALUES ('Germany'), ('Spain'), ('Italy');

CREATE TABLE Cities
(
    Id [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    CountryId [int],
    Name [VARCHAR] (100) NOT NULL,
    CONSTRAINT FK_Cities_Countries
        FOREIGN KEY (CountryId) REFERENCES Countries(Id)
);

INSERT INTO Cities VALUES
(1, 'Berlin'), (1, 'Dresden'), (2, 'Madrid'), (2, 'Barcelona'), (3, 'Rome'), (3, 'Milano');

ERP system allows to delete records both from Cities and Countries (when Country deletion action is called first records from Cities are deleted then from the Countries. I need to block deletion from Cities if Country is not being deleted i.e.

DELETE FROM Cities WHERE Name='Berlin' --needs to be stopped
DELETE FROM Countries WHERE Name='Germany' --needs to go

Hope it is clear now.


texts: postgresql crash in master node with streaming replication

texts:
We have a master/slave postgresql cluster with streaming replication and pgpool.
Versions in both postgresql servers are.
postgres: 9.4
OS: Debian GNU/Linux 8 (jessie)

PGPOOL server:
version pgpool: pgpool-II version 3.3.3
os: Red Hat Enterprise Linux Server release 6.1
Problem:
We suffer a crash down of the postgresql service at master node with subsequent failover done by pgpool
Could not find the reason of this behavior in master node. We want to know if someone coult tell us, based on the recorded log messages, what could have caused this crash.
Following, some relevant lines from the postgresql and linux log.
postgresql.log
2020-12-20 21:06:04 UYT [25922]: [4-1] user=,db=,app=,client= LOG:  server process (PID 19303) was terminated by signal 11: Segmentation fault
2020-12-20 21:06:04 UYT [25922]: [5-1] user=,db=,app=,client= DETAIL:  Failed process was running: COPY public.act_hi_varinst (id_, proc_def_key_, proc_def_id_, proc_inst_id_, execution_id_, act_inst_id_, case_def_key_, case_def_id_, case_inst_id_, case_execution_id_, task_id_, name_, var_type_, rev_, bytearray_id_, double_, long_, text_, text2_, tenant_id_, state_, create_time_, root_proc_inst_id_, removal_time_) TO stdout;
2020-12-20 21:06:04 UYT [25922]: [6-1] user=,db=,app=,client= LOG:  terminating any other active server processes
2020-12-20 21:06:04 UYT [1910]: [3-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= WARNING:  terminating connection because of crash of another server process
2020-12-20 21:06:04 UYT [1910]: [4-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-12-20 21:06:04 UYT [1910]: [5-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-12-20 21:06:04 UYT [27108]: [3-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= WARNING:  terminating connection because of crash of another server process
2020-12-20 21:06:04 UYT [27108]: [4-1] user=dbusr,db=dbdatabase     ,app=[unknown],client= DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

Error found at /var/log/messages

Dec 20 21:06:04 server-name kernel: [20278231.774013] postgres[19303]: segfault at 7f7740354704 ip 00007f772c8ac700 sp 00007ffdac7ff4e0 error 4 in postgres[7f772c813000+5a1000]

Thank you all.


texts: OLTP - Stretch database vs linked server

texts:
From my understanding, when you enable the StretchDB feature for a table, basically creates a Linked Server locally, which has a remote server as a target to move data to.

If this is correct, does this mean that they will have the same problems? Such as:

Getting all the data and then filtering after
Terrible statistics on the data

Kendra Little has a Youtube video on Brent Ozar's website outlining some of these problem.


texts:
I've got a photo review type of application, where the customer wants to have an option for the user to flag issues with the photos. So, for instance, a photo could be tagged as "fuzzy," "grainy," or "other." If "Other" is selected, a brief text description is required.

I am thinking I should create one table with all the issue types. Another table, will have foreign key relationships to the photo id, and the issue type id. I am thinking this table should also have a text type column for the "other" text if "other" is selected, otherwise that column will be null.

My dilemma, is, will this be a violation of normalization rules? If the "Other" text column is not null, then the assumption can be made that the issue type id will always be the one for "Other." So, that would be a transitive dependency, right? Any ideas on how to implement this without violating 3rd normal form?


texts:
I get a alert email with the title:

  SQL Server Alert System: 'Severity 17 - Potentially Fatal Error
  Insufficient Resources' occurred on myserver as 12:02

when I connect to myserver to check the log
I see the below:

we are on sql server 2005.

and I am not sure what this local connection provider is.

any hints?


texts: Ensuring no unwanted tables have changed after a risky operation is performed

texts:
Sometimes I want to make sure that after a risky operation such as database moving into another server or schema changes via migration scripts no data corruption or loss (due to human error and not the DBMS itself) has occurred to tables that is supposed not to change.

Therefore, I want somehow to check that all my data located in tables are safe and sound in a quick and easy way, therefore I thought it would be good idea to hash each one of my table via a cryptographically secure hash function to current database. Afterwards, once the action has performed, I could recalculate the hashes for each table and check the differences via custom scripts.

The question is how I can hash each table and its data in order to store the hash result?

For example if My database has the following tables:

table1
table2
table3

If I keep hashes on each table then coparing each hashes for each table I could have a quick view which tables has changed. Then if a hash is different on a table that I expect not to, I could smell a data corruption

My database layer is PostgreSQL 11.


texts: Adding a user to MySQL with 'name'@'%' fails with ERROR 1396

texts:
I just tried to add a new user to MySQL using

CREATE USER 'name'@'%' IDENTIFIED BY '...'

However, it fails with the following error:

ERROR 1396 (HY000): Operation CREATE USER failed for 'name'@'%'

Adding the user just for localhost works fine:

CREATE USER 'name'@'localhost' IDENTIFIED BY '...'

I have no clue what could be the problem. I'd be grateful for any ideas.

(I'm using mysql  Ver 14.14 Distrib 5.1.66.)


texts: The curious case of HADR_SYNC_COMMIT waits

texts:
We are noticing an interesting pattern for HADR_SYNC_COMMIT waits in our environment. We have a three replica; one primary, one sync secondary and one async secondary in a datacenter and we just added three more ASYNC replicas in another datacenter (~2400 miles apart).

Ever since, we have started to notice an enormous increase in HADR_SYNC_COMMIT waits. When we look at the active sessions, we see a bunch of COMMIT TRANSACTION queries waiting on the SYNC replica

From the screenshot, we can clearly see there is a jump in HADR_SYNC_COMMIT wait on June 29, and we eventually dropped 'two' of the three async replica in the remote datacenter sometime in the noon on July 1st. That dropped the wait times considerably along with it.

What we have checked so far – Log send queue, Redo queue, last hardened time and last commit time on the remote replicas. We have continuous bursts of small transactions during the business hours, and therefore the send queues are pretty small at a given timestamp (anywhere between 60KB and 1MB). The remote replicas are almost in sync, there is very little difference between the last commit time and last hardened time for any individual lsn on the replicas.

The network pipe is 10G and we modified the transmit buffer size from 256 megs to 2 gigs, this was made under the assumption that the network was dropping packets and re-transmitting them; either way that didn’t seem to help much.

So, I’m wondering what does the ASYNC replicas have to do with HADR_SYNC_COMMIT waits? Shouldn’t the SYNC replica depend alone on this wait type, what am I missing here?


texts: how to change the publication settings via T-SQL?

texts:
I set up a new server to host some database publications.

On the same server I set the distributor db.

All goes on well, then while running a snapshot of a publication on transactional replication:

get an error that the folder where the publication is set to run the snap does not have the privileges

I check where the snapshot is set to be generated within the publication
it is not where it should be

I cannot connect to the server, either to change the publication settings nor to grant the relevant permissions

question?
can I change the publication on that server from my local server and re-run the snap?


texts: How can I deny a user the ability to Alter DB files and options but still allow schema and data modifications?

texts:
I'm establishing a shared development instance for multiple single-database applications.

I'm envisioning granting authorized users permissions to allow any schema and data modifications they need to perform, but want to prevent the same users from issuing ALTER DATABASE commands to ADD or MODIFY FILEs. This is to prevent any one developer from consuming additional disk space resources without consent from the DBA.

Looking at an approach using the least administrative effort, I'm thinking I should grant db_owner to the authorized users and add a database or server trigger to block ALTER DATABASE commands unless they come from DBA sessions.

Would this work or is there a better/safer approach?


texts: How to get session parameters changed by the user in Oracle?

texts:
Oracle 11.2.0.3 .

I need to identify all "alter session" changed for a specific session.
There is some v$session_* where I able to get this information?

My objective is part of investigation what I doing about a problem of sessions hanging at compile time... from Forms Builder.

I already got a trace of the session and have some suspicious over a bug documented at Oracle Support... but I want more details about this session.