texts: Database Design for Multi-Level Ownership

texts:
I'm trying to figure out what sort of database and what sort of database structure would make sense for representing ownership of different assets by different constituencies.

Say a group of 5 friends get together to invest.  They each make a different contribution and pool it into two Funds.  The two Funds put money in various accounts to purchase stocks and bonds while investing in other assets directly.
So the entities are:

Owners:
Friend1,
Friend2,
Friend3,
Friend4,
Friend5

Pooled Fund:
Fund1,Fund2.

Accounts:
Account1,
Account2,
Account3.

Instruments:
Stock1,
Stock2,
Bond1,
Bond2,
Cash,
Other3

So a scenario would be:
1) Friend1 through Friend3 invest in Fund1 which has Account1 and Account2 and Account1 buys Stock1 and Bond1, Account2 buys Stock1, Stock2, Bond1, Bond2, and Other.
2) Friend4 and Friend5 invest in both Fund1 and Fund2 and Fund2 owns different things than Fund 1.

Questions to answer:

1) How much of Stock 1 does Friend4 have on a look-through basis? What is his "exposure?"

2) Is it possible to create income statement, balance sheet and cashflow statement at each level in the chain based on the proportional ownership of the instruments?

3) Given that the group of Friends may change over time and the Instruments change over time, what is the best way to capture the changes so that it is possible to see who owned what and when?


texts: Is there a way to allow only DELETES from CASCADES only?

texts: I want to prevent anyone from deleting certain records from a table, except for when the parent record in another table gets deleted, then should it be deleted. Can I use triggers for this purpose?


texts: Slave_IO_Running: Connecting in Master-Slave Replication

texts:
I was trying to implement Master-Slave Database Replication on two systems.
I followed the steps from the following links
https://www.youtube.com/watch?v=DmQWcU2INqQ
and
Master Slave Replication

But in both cases, I ended up with

  Slave_IO_Running: Connecting
  Slave_SQL_Running: Yes

I've tried to check the network connection between the machines and they're able to ping each other successfully.
Disabled the antiviruses and Firewalls on both systems
Added an alias for the slave to access the master machine, Working.
Both machines using 3306 port for mysql.

  Last_IO_error: error connecting to master 'user01@192.168.2.46:3306' -- retry-time 60 retries: 1


texts: Oracle to T-SQL OPENQUERY special character conversion issues

texts:
I'm struggling to figure out where the character encoding issue on my Linked server may be coming from here. The ZPDT_PAT_ALPHA column should have a degrees symbol at the end, as shown by the DUMP.

SELECT *, CHAR(176)
FROM  OPENQUERY([R_Pronto], 'SELECT DUMP(ZPDT_PAT_ALPHA), ZPDT_PAT_ALPHA
            FROM LINKED.ZCUSPRODATTR0')

Here are the NLS parameters. NLS_LANG is set in the registry "AMERICAN_AMERICA.WE8MSWIN1252"

And the linked server settings using Microsoft OLE DB Provider for ODBC Drivers

I appreciate any pointers as to what I can adjust to make the special characters appear correctly. I unfortunately don't have access to the Oracle DB to make any changes.

The application that sits on top of this DB is able to show the character - I believe it interfaces directly with Oracle.

Thanks

Edit: Showing the TRANSLATE USING NCHAR_CS

Edit2:


texts: Determining whether a MySQL column is ever used

texts:
How can I determine whether a particular column in a MySQL table is ever SELECTed or explicitly UPDATEd or INSERTed or referenced in a WHERE clause?  It'd be nice if I could determine the db user who is using the column, too.

This is on MySQL 5.0, but I could move that to 5.6 or 5.7.

Background:  I'm maintaining a legacy app with an AUTOINCREMENT field ("id") that will foreseeably reach its maximum INT value before the app is retired.

I suspect, but do not know, that no one actually references this field in SQL at all.  Ideally I'd "monitor" the column for a week or two and, if it appeared in no queries, I'd turn it into a BIGINT or drop it in favor of a natural key.  If the column does appear in someones' queries, I'll need to track that person down and coordinate the change.


texts: InitPersistentVersionStore failed after installing CU2 on multiple instances

texts:
I saw this on another post, but I can't comment on it. So I am opening a new one, but I am having a similar issue that Martin Guth had posted.

It is odd, but I updated about 9 of my instances to CU2 for 2019 a few weeks ago. Every day, around 3 am, I am getting messages in the error log for this:

03/09/2020 03:00:01: RecoveryUnit::InitPersistentVersionStore failed with db [5] name [master]
03/09/2020 03:00:01: RecoveryUnit::InitPersistentVersionStore failed with db [5] name [master]
03/09/2020 03:00:04: RecoveryUnit::InitPersistentVersionStore failed with db [5] name [msdb]
03/09/2020 03:00:04: RecoveryUnit::InitPersistentVersionStore failed with db [5] name [msdb]

I get this for one of my instances, and for the other one, I get this:

03/09/2020 03:00:03: RecoveryUnit::InitPersistentVersionStore failed with db [14] name [master]
03/09/2020 03:00:03: RecoveryUnit::InitPersistentVersionStore failed with db [14] name [master]

I agree with Martin, I checked ADR, and it is not enabled in my environment at all at the moment. So I am not sure where this is coming from or why, but it started after I updated to CU2. I did do it via dbatools in powershell, along with three others, but the others are not exhibiting this behavior.

If anyone has any suggestions, I am open to them. If I do not hear anything, I will open an issue with Microsoft as well.


texts: How to Handle Database for Application which Starts from Scratch Annually

texts:
I am working on an application which will be used by an organisation annually, however each year they will be starting from a clean sheet. Which of the following two approaches (or any other approach) is most suitable:

Run the entire application from one database with a field in all relevant tables for the year it applies for. E.g. User("Mary", "Smith", "mary.smith@example.com", 2012) where 2012 shows that Mary's account is valid in 2012.
Have separate databases for each year and one main database which references which database is related to which year.


texts: MS SQL Server 2014: which stored procedure is my error in?

texts:
As a general question or three on Microsoft SQL Server 2014, that perhaps I should know the answer to already, or even perhaps did once -
When my stored procedures run other stored procedures, how can I find out which stored procedure's code generated the error?
Is there a particular way to handle errors from Transact-SQL that identifies the location of the error in a given stored procedure?
Can program code in a stored procedure obtain the name of the procedure it is in?
I am prepared to put "SET @MyNameIs = N'HastilyWrittenProcedure' " in program code if it's the only way.  Then I can put "PRINT @MyNameIs + ' broke.' " in the error handler in everything.


texts: Does page verification mode affect DBCC CHECKDB behavior?

texts: Does DB page verification mode (torn page vs checksum) affect DBCC CHECKDB behavior?  Have some large (TBs) older db's still with TORN page detection mode and curious if check db works any better / worse / slower / faster based upon page verification mode.


texts: Same query uses 2 different plans in 2 sessions

texts:
I am running same query in 2 different DB sessions but it is ending up picking 2 different plans. One is faster and other takes hours.
Any ideas on how to make them use same plan:

This is Plan for faster query

This is plan for slower query

Only difference between 2 plans is order of collection iterator pickler fetch

EDIT:

Just to add more details, above query is not really a standard query as it uses Table function which accepts a dynamic array list.


texts: Oracle is not using a unique index for a long key

texts:
I have a table with 250K rows in my test database. (There are a few hundred millions in production, we can observe the same issue there.) The table has an nvarchar2(50) string identifier, not null, with a unique index on it (it's not the PK).

The identifiers are made up of a first part that has 8 different values in my test database (and about a thousand in production), then an @ sign, and finally a number, 1 to 6 digits long. For example there could be 50 thousand rows that start with 'ABCD_BGX1741F_2006_13_20110808.xml@', and it is followed by 50 thousand different numbers.

When I query for a single row based on its identifier, the cardinality is estimated as 1, the cost is very low, it works fine. When I query for more than one row with several identifiers in an IN expression or an OR expression, the estimations for the index are completely wrong, so a full table scan is used. If I force the index with a hint, it is very fast, the full table scan is actually executed an order of magnitude slower (and a lot more slower in production). So it is an optimizer problem.

As a test, I duplicated the table (in the same schema+tablespace) with the exact same DDL and exact same content. I recreated the unique index on the first table for good measure, and created the exact same index on the clone table. I did a DBMS_STATS.GATHER_SCHEMA_STATS('schemaname',estimate_percent=>100,cascade=>true);. You can even see that the index names are consecutive. So now the only difference between the two tables is that the first one was loaded in random order over a long time period, with blocks scattered on the disk (in a tablespace together with several other big tables), the second was loaded as one batched INSERT-SELECT. Other than that, I can't imagine any difference. (The original table has been shrinked since the last big deletion, and there hasn't been a single delete after that.)

Here are query plans for the sick and the clone table (The strings under the black brush are the same all over the picture, and also under they gray brush.):

(In this example, there are 1867 rows that start with the identifier that is black brushed. A 2-row query produces a cardinality of 1867*2, a 3-row query produces a cardinality of 1867*3, etc. Can't be a coincidence, Oracle seems to not care about the end of the identifiers.)

What could cause this behavior? Obviously it would be pretty expensive to recreate the table in production.

USER_TABLES: http://i.stack.imgur.com/nDWze.jpg USER_INDEXES: http://i.stack.imgur.com/DG9um.jpg I only changed the schema and tablespace name. You can see that the table and index names are the same as on the query plan screenshot.


texts:
We have a table FOO that has a huge amount of data in it, most of which is only used infrequently and by (slow) user-interaction.

We would like to move all data that is more than 12 months old to an archive table, FOO_ARCHIVE. That will make the frequent reads and writes into FOO fast enough.

There are many complex stored procedures in our application(s) that currently query against FOO, so we need a way to sensibly combine the two tables when reporting.

I have considered using a union, but I'd rather only look in the archive table if I need to, which is when nothing is in the live table.

SELECT Field1, Field2, Field3, FieldN FROM FOO
UNION ALL
SELECT Field1, Field2, Field3, FieldN FROM FOO_ARCHIVE

However, I'd rather only hit archive if I have to, so perhaps create a stored procedure for this:

SELECT Field1, Field2, Field3, FieldN FROM FOO
IF @@ROWCOUNT = 0
BEGIN
    SELECT Field1, Field2, Field3, FieldN FROM FOO_ARCHIVE
END

But I only want one result set returned, and there can be many conditions applied to the query.

The solution must be supportable on SQL Server 2005, due to client deployments.


texts:
I have to go to multiple locations like SQL error log file, Event Log, run sp_whoisactive whenever I am have escalations from customers that SQL Server is slow.
Is there any tool that will quickly give me a snapshot of all 3 in one(SQL Error logfile/Event(app log)/sp_whoisactive)


texts: Does having auto_increment ID degrade performance compared to no auto_increment?

texts:
I have a big table with ID column set as bigint auto_increment. At some time in history we decided to generate IDs on client side as some pseudo-random number.

My question is simple: given that the table column is still set as auto_increment, does this affect performance negatively even though we ALWAYS supply ID in INSERT queries? Is this significant/should I bother to change the schema? The table is EXTREMELY huge and busy, I am afraid I cannot do this without downtime.


texts:
I have the same database on 2 servers. One is with Cpanel running mysql other one its CWP with mariadb. Both databases are identical, tables have the same index, mysql variables are identical but the performance its way different:

SELECT
    DISTINCT item_id
FROM
    site_plugin_products_cache_filters
WHERE
    value_id IN (32)

On MySql I get 1,939 rows (1.284 s)
On MaridDb I get 1,939 rows (0.097 s)

Any idea why this happens ?

When i get to sub-queries the differences are way bigger ( 0.01s vs 100+ seconds )

SELECT
    DISTINCT item_id
FROM
    site_plugin_products_cache_filters
WHERE
    value_id IN (32) AND
    item_id IN (
        SELECT item_id FROM
            site_plugin_products_cache_cats
        WHERE
            cat_id=1
    )

Or

SELECT  DISTINCT item_id  FROM site_plugin_products_cache_cats
WHERE cat_id IN (362)  AND item_id != 2519 ORDER BY rand()
LIMIT 4

4 rows (0.053 s)  vs 4 rows (0.103 s)


texts: How is calculated fill-factor percentage related to size of database?

texts:
I have rebuilt all indexes in my database setting fill-factor of 95 (5% free space) using a maintenance plan. After the reindex the database is almost doubled in size - reported free space is 42%.

How is calculated fill-factor related to the size of the database?

Maybe something is wrong with reindex; what causes so much growth of size?

Some database info after reindex:

Size (MB):            164 983.625
Data Space Used (KB):      82 907 896
Index Space Used (KB):     14 073 320
Space Available (KB):      71 879 024

Generated T-SQL for maintenance plan for one table:

ALTER INDEX [Table1_Index1] ON [dbo].[Table1] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
   IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95)

Additional informations:

Result of sp_spaceused 'dbo.BigTable'

name        rows        reserved    data        index_size  unused
BigTable    58028080    72824296 KB 68393936 KB 4424000 KB  6360 KB


texts: Mysql join heavy query on large tables taking hours

texts:
I'm trying to query our entire database to build a report and I've run into the problem that I have queries like the one below that are taking near 2 hours to run and some take even longer. I've changed the some of the names but the structure is the same.

Person_table has about 5 million rows

check_table is a subset of Person_table and has about 3 million rows

event_table has about 20 million rows

update  check_table as CT
    inner join
        ( SELECT  B1.id
            from  Person_table as PT
            inner join  event_table as B1  ON PT.id = B1.person_id
            inner join  event_type_table as E using (code_id)
            inner join
                ( SELECT  *
                    from  event_table as ET
                    where  ET.code_id in ('XYZ','ABC','DEF','ETC.')
                ) as F  ON ET.id = F.person_id
                       and  B1.Event_date = F.event_date
        ) as B2 using (id) set flag_1=1;

I've tried to get an explain on many of the queries I have to do but it always spits back a "Explain Data not available for statement".


texts: Select,From,Join,Where within 1 query MySQL

texts:
Wondering if its possible to use WHERE within such a big query string?
I cannot seem to achieve what I want once I add Where into the query.

customer
+----+------+------+
| id | type | name |
+----+------+------+
| 1  | boy  | dave |
| 2  | girl | lisa |
+------------------+

order
+----+--------+
| id | status |
+----+--------+
| 1  |  done  |
| 2  |  done  |
+-------------+

SELECT `customer`.*,order.status
FROM `customer`
JOIN `order` on customer.id = order.id
WHERE customer.type = boy

I assumed this would return result

'id' = 1
'type' = boy
'name' = dave
'status' = done

But instead I get errors.

If I do not include Where, I will get result

'id' = 1
'type' = boy
'name' = dave
'status' = done

'id' = 2
'type' = girl
'name' = lisa
'status' = done

Thanks for any help in advance.


texts: How to Get the SUM Value from Grouped by Last value in a table result

texts:
I have this query table,

CREATE TABLE BASE_Purchase
(
PurchaseId BIGINT,
PurchaseDate DATE NOT NULL,
SupplierName VARCHAR(255) NOT NULL,
Amount MONEY NOT NULL,
Outstanding MONEY NULL
PRIMARY KEY(PurchaseId)
)

I have the following result set from the table,

Above image result query ↑

SELECT PurchaseId, PurchaseDate, SupplierName, Amount, Outstanding
FROM BASE_Purchase
ORDER BY SupplierName, PurchaseId

I need the above should be result as follow,

After retrieved the above result and i want to get the SUM of each outstanding


texts: MySQL, temporary on disk table on primary key retrieval?

texts:
MySQL 5.5.24, Windows 7 64-bit.

I've InnoDB table with primary key defined on three columns:

CREATE TABLE `common_descriptions` (
  `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `object_type` varchar(32) NOT NULL DEFAULT '',
  `description` mediumtext NOT NULL,
  `lang_code` char(2) NOT NULL DEFAULT '',
  `object` varchar(12) NOT NULL DEFAULT '',
  `object_holder` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`object_id`,`lang_code`,`object`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And I'm executing fairly simple query which (in my opinion) should use index to return rows

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

yet it creates 10 temporary tables of which 3 are on disk temporary tables (confirmed multiple times by monitoring show status like '%tmp%';). object column seems to trigger creation of temporary tables. When I replace object (varchar(12)) with description (mediumtext) temporary table is not created. explain extended of above query:

mysql> explain extended select SQL_NO_CACHE object_id, lang_code, object from co
mmon_descriptions limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: common_descriptions
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 47
          ref: NULL
         rows: 49765
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Both tmp_table_size and max_heap_table_size are set to 16M.

After reading documentation on temporary tables (http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html) I don't see any good explanation on why temporary tables would be created in this case. Why is it happening?


texts: Best way to generate a report on overall SQL Server performance to show management?

texts:
I support a transactional SQL Server as a dba and have recently been told by a management consultant that they need a new replication server set for reporting only, claiming poor performance on the main server due to too many processes running at once. When I open activity monitor at any given time, I see no more than 5% processor time, 1 mb/sec, etc. I have worked on performance issues with them before but it always comes down to code errors, inefficient queries, or missing indexes (for example there is one index that according to the missing index dmv has an avg_estimated_impact of 33 minutes).

I'm fighting it because it will cost them a lot of money in our org and also I don't believe it will help them with their performance issues that much/at all.

Does anyone have any nice queries or methods they can share to help prove server performance is fine in terms of computing resources? I can screenshot activity monitor but I'd like to provide management with something more concrete (perhaps over 24 hours) that it is not a lack of computing resources that is part of the perceived slowness.


texts: Query best possible matches and order them

texts:
I'm trying to write a query along these lines:

select *
from tbl
where
       col1 = 1
   and col2 = 2
   and col3 = 3
order by
   ...
;

I want first all the results where all 3 WHERE conditions match (3/3), then all the results where any 2 conditions match (2/3), and finally the results where any 1 condition matches (1/3).

Each of these 3 result sets needs to be ordered by (col4, col5, col6).

Can I do that in a single query?

For example:

sample http://img708.imageshack.us/img708/1646/sampletableresult1.jpg

Script to create test data:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
    DROP TABLE [dbo].[MyTable]
GO

CREATE TABLE dbo.MyTable
(
    col1 INT
    , col2 INT
    , col3 INT
    , col4 INT
    , col5 INT
    , col6 INT
)
GO

INSERT dbo.MyTable (col1, col2, col3, col4, col5, col6)
SELECT 1,2,3,2,1,1 UNION ALL
SELECT 1,2,30,1,1,1 UNION ALL SELECT 1,20,30,1,1,1 UNION ALL
SELECT 10,20,3,1,1,1 UNION ALL SELECT 10,2,30,1,1,1 UNION ALL
SELECT 10,2,3,1,1,1 UNION ALL SELECT 10,20,30,1,1,1 UNION ALL
SELECT 1,2,3,1,1,1 UNION ALL SELECT 1,2,3,1,2,2 UNION ALL
SELECT 1,2,3,1,2,3 UNION ALL SELECT 1,20,3,1,1,1
GO


texts: Why MySQL SELECT performance depends on table size?


texts: SQL query based on foreign key relationship

texts:
I have two tables, a Project and an Action:

Project has the following fields:

project_name

Action has the following fields:

action_name
project
status

There is a one-to-many relationship between Action and Project.

I need a query that returns all the Projects that ONLY have actions with statuses greater than 1 OR Projects with no actions at all.

I've never written a query that is dependent on a relationship.  How might this be constructed?


texts: user mapping is droped after swithing from principle server to mirror server

texts:
I have principal server:A and mirror server:B with DB Name: "DBTest"
On the server A there is a username (SQL auth.) call: user1 and is mapped to "DBTest".
the Same is on Server B, same username and same DB Name (but is in recovery mode due to mirroring).
After Fail-over in Principle Server, the Witness switch the DB to the mirror server and everything is fine. except the user: user1 on the server B is not mapped to the "DBTest" on server B.
i should every time reassign manually the user1 to the "DBTest". The same i should do when i switch from server B again to server A.

This issue blocks the systems and applications which are connecting with the DB, because the user1 is not mapped automatically to the DB name after fail-over.
please help!


texts: Adding a Clustered index to a HEAP table on someone else's app

texts:
We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index).  Over the years, these tables have grown badly fragmented (e.g. 99% fragmentation).  I need to defragment them.

Now, in SQL Server 2005, there's no way to do that directly.  I can either:

Create a Clustered index on a prexisting field, and keep it
Create that index, rebuild the table, and then drop it
Add my own field (eg autoincrement key)

Now, this is a large app, written by a vendor - a giant black box.  I'm not eager to mess around with their stuff.  I don't know a lot about how the tables are used.  What's the least impact way to do this?

And, as a follow up: There are many of these HEAP based tables, over a dozen databases used by the application.  Is there a way to automate choice #2 or #3? Or, how should I pick which tables to modify?

UPDATE:
To answer the questions posed by the (very helpful) responses:

Performance has been unacceptable.  The vendor told my client that they this is because the tables are extremely fragmented, and it's the client's responsibility to defragment them regularly
We have two instances of the application: a test one and a production one
I first defragmented all of the Clustered tables, which greatly improve performance
The vendor's support team has been very unhelpful.  They've told us it's our responsibility to defragment the tables.  When I've asked them how they recommend defragmenting HEAP based tables - should I add a Clustered Index? - they've only responded "That's a Microsoft question".

In short, the customer support team has made it clear: You must defrag the tables, how you do it is your business.

As for future versions: Yes, new versions are being developed, and they will eventually migrate to SQL Server 2012.  But they need performance solutions today.

Finally, as for the defragmenting taking too long: It doesn't matter.  They have giant tables with 99% fragmentation; the application isn't used at night; I can easily spend hours at a time defragging them.


texts: GROUPING SETS query with many-to-many relationships

texts:
I'm writing a query that is mostly based on a GROUPING SET. A very simplified version of the query is the following:
SELECT GROUPING(a,b) as grouping, a, b  , COUNT(*)
FROM table
GROUP BY GROUPING SETS ((a,b), ())
ORDER BY count DESC

The real query has more groupings and a bit more stuff around it to translate e.g. one-to-many relations to something understandable. But the core is a bunch of aggregations performed at the same time on that table.
The result of that kind of query are several aggregations at once. And for multi-column grouping sets like in this query, it creates all combinations of those two aggregations.
The problem I have now is how to integrate aggregations that are not based on columns in this table, but on junction tables for many-to-many relationships with this table. For simple aggregations that are only based on a single column, I can do this with a separate query with a simple GROUP BY and UNION ALL that onto the GROUPING SETS query. It's a bit annoying, but that works.
But the problem are the more complex aggregations based on two or more columns. That's extremely easy to do with GROUPING SETS, but my workaround for many-to-many relationships doesn't really work then. I don't know how I'd write that kind of query if one of the columns of the grouping set is in the main table, and the other one in a many-to-many junction table. I also can't just join the junction table in this query, as this duplicates rows which means the aggregations are wrong.
Is there a reasonable way of doing this kind of GROUPING SET query with many-to-many relationships, and especially with combinations of columns in the main table and many-to-many relations?
Here is a minimal example of the kind of query I want to do. Keep in mind that the query I post here is obviously wrong because the join duplicates rows which get then counted wrong in the aggregations. The query I want to perform is essentially last one here, just with correct aggregations.
create table if not exists demo (
    id  serial primary key,
    a   text,
    b   text
);

create table if not exists stuff (
    id  serial primary key,
    c   text
);

create table if not exists demo_stuff (
    demo_id int references demo(id),
    stuff_id int references stuff(id)
);

insert into demo values(1,'x','y') on conflict do nothing;
insert into demo values(2,'x','z') on conflict do nothing;
insert into demo values(3,'x','z') on conflict do nothing;

insert into stuff values(1,'foo') on conflict do nothing;
insert into stuff values(2,'bar') on conflict do nothing;

truncate demo_stuff;
insert into demo_stuff values(1,1);
insert into demo_stuff values(1,2);

-- this query only on the demo table works fine
select grouping(a,b),a,b,count(*)
from demo
group by grouping sets ((a,b),());

-- this query joins the demo_stuff table to be able to
-- aggregate on many-to-many relationships for the demo table
select grouping(a,stuff_id),a,stuff_id,count(*)
from demo
left join demo_stuff on id = demo_id
group by grouping sets ((a,stuff_id),());

-- this query shows how the join leads to wrong aggregate
-- results, as it obviously duplicates rows
-- 'y' appears only once in the table, but is counted twice here
select grouping(b),b,count(*)
from demo
left join demo_stuff on id = demo_id
group by grouping sets ((b),());


texts: I'm new to Oracle and curious of how Oracle Exadata handles database files. Do we have to assign the disk to a database file as we do with Microsoft or is this handled by Exadata?


texts: How to restore a MySQL file to SQL Server 2008

texts: I have a MySQL file (apple.sql) that I need to restore to a SQL server 2008 database or at least be able to access the database through mySQL. I have created a linked server mySQL, created an empty database on mySQL. I am not sure how to restore the backup file (apple.sql) to the empty database?? Any ideas?


texts: Are there other sites forbidding SQL developer to use c style comments?

texts:
My personal opinion is that C style comments should not be used by sql developers.

The problem with C Style comments is, that they do not nest.

C Style comments are one of the lifesavers for the support when they have to do some ad hoc corrections in stored procedures, but that only works well, when they don't have to fight with existing C comments.


texts: Impact of moving the ldf and mdf files temporarily for few hours

texts:
I want to copy my database files(ldf and mdf) into a separate box for few hours as there is a disk space issue in my current sql server box.
Currently i have mdf in one drive(:D) and ldf in another drive(:E).
I have to move both mdf and ldf into same drive,say :D.
I understand that it is recommended to have separate disk drives for LDF and MDF ,but this is a DEV box and to avoid purchase/delay client wants to go with this option.

I have two plans:

Take backups of all systems databases and user databases and save it
in a separate box.
Move MDF and LDF to a separate box for few hours

So after the partitioning/merging of disk drives are completed i can either:

Restore the backups.
Move the MDF and LDF to the merged drive.

For user database ,i understand that the below steps will be sufficient:

Bring the database offline
Move the file(s) to the new location manually.
Update the system catalog settings:

ALTER DATABASE DBNAME
        MODIFY FILE (
            NAME = 'DBNAME',
            FILENAME = 'D:\SQL_Data\DBNAME.mdf'
        )
        GO

ALTER DATABASE DBNAME
    MODIFY FILE (
        NAME = 'DBNAME_log',
        FILENAME = 'D:\SQLLog\DBNAME_log.ldf'
    )
Bring the database online

Can someone explain the dangers of second step or direct me how to do second step for system databases.?


texts:
I have one table in Postgres which has following columns:

processtransactionid bigserial,
TenantID uuid,
ProcessInstanceId bigint
attributename varchar(250),
attributevalue varchar(250),
createddate timestamp,
queueid int

The attributename contains values like 'filename', 'documentstatus', 'batchImportdate', etc. and attributevalue columns contains corresponding values of the attribute, like filename = abc.jpg, documentstatus ='Present' & batchImportdate='2019/08/26 12:57:51'

I want output in the following way:

 ProcessInstanceId filename  documentstatus batchImportdate        queueid
   6008            abc.jpg   Present        '2019/08/26 12:57:51'  2
   6008            abc.jpg   Absent         '2019/08/26 12:57:51'  3

grouping of the records to be done by ProcessInstanceId, queueid

I am able to create query in SQL Server using the PIVOT keyword but facing a problem in creating the same query in Postgres.

Could you please help me write the Postgres query?


texts:
2013-09-09 10:21:44 5776 [Note] Plugin 'FEDERATED' is disabled.
2013-09-09 10:21:44 1624 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-09-09 10:21:44 5776 [Note] InnoDB: The InnoDB memory heap is disabled
2013-09-09 10:21:44 5776 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-09-09 10:21:44 5776 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-09-09 10:21:44 5776 [Note] InnoDB: Not using CPU crc32 instructions
2013-09-09 10:21:44 5776 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2013-09-09 10:21:44 5776 [Note] InnoDB: Completed initialization of buffer pool
2013-09-09 10:21:44 5776 [Note] InnoDB: Highest supported file format is Barracuda.
2013-09-09 10:21:44 5776 [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 19862295 in the ib_logfiles!
2013-09-09 10:21:44 5776 [Note] InnoDB: Database was not shutdown normally!
2013-09-09 10:21:44 5776 [Note] InnoDB: Starting crash recovery.
2013-09-09 10:21:44 5776 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-09-09 10:21:44 5776 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace casualme/scraped_user uses space ID: 2 at filepath: .\casualme\scraped_user.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd
InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

My phpmyadmin is not working. How can I make it work again? I have a very large database.

I have the following files. db.opt , scraped_user.frm , scraped_user.ibd is there a way to recover my database from these files? I just want to get the table's data.

I want to reinstall my xampp but is there a way to back up my db when I can't run it? Like copying this folder 'DBNAME' with files db.opt, scraped_user.frm, scraped_user.ibd.

I tried the first option suggested by InnoDB above. Still not working. In second I cant remove the .ibd file. I don't have a backup of my table. On the third item, I don't know where it is located. Been looking for it.


texts: PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot)

texts:
Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will block and fail if row was modified during the transaction.

Edit: Writes don't actually 'block and fail' under RCSI but rather just block.


texts: Is it possible to use two different drivers for sql server on the same machine. Say 2000 and 2008

texts: I am trying to use two versions of sql server on the same machine. I have sql server 2000 driver installed on the system. Can I also install sql server 2008 driver on the same machine and use both side by side?


texts: Should we explicitly lock MyISAM tables or is it implicit....?

texts:
Does MyISAM tables automatically lock a table for each select or insert statement ....

I mean if I query the database as select * from tablename  then does the MySQL engine first lock it for read automatically and unlock it after showing results or do we have to first mention lock tablename read explicitly and then read the data....??

I have this doubt cause we have a database with some lack of rows and around some 50- 100 rows gets inserted or updated each second. We never used lock statements while inserting or updating and we are still not having any concurrency problems.


texts: Should we have OrderId and separate OrderNumber for E-Commerce Website or we can have only field for both?

texts:
I am working on an Online Grocery Store type of website in which I have one order table to store the order details. Here I am having confusion that whether I should have an OrderId (primary key) and separate user-facing OrderNumber or I can only have an OrderNumber as my primary key as the OrderNumber is going to be unique?
Also, I was considering OrderId to be a UUID type. So can I do the reverse of having only OrderId and considering it as User-Facing Id as well?


texts:
At my job we have a lot of objects that we track versions of.  We are tentatively looking at creating a rather large number of sequence objects in order to keep track of these version.  My concern is that sql server may have a max number of these that we can create, but I am unable to find any such documentation.

So, is there a max number of sequences that can be created in SQL Server 2014 and 2016?


texts: Configure Availability Group in SQL Server 2016

texts:
Can you tell me how you add the second node as the replica to the AG?

I am using this feature on AWS instances, I have followed all the steps as shown in different videos, but when I try to add the secondary node as the replica, it does not work. It only takes one name and that is the SQL Cluster name that I had given during SQL Cluster fail over installation on the primary node.

Can you guide me on this? Also, I am able to login both the SQL instances only with SQL Cluster name as well as Server name.


texts: How to check backup against SQL AG by Get-DbaLastBackup?

texts:
I use the following PowerShell script to check if the last full backup successful daily:

$SQL_Server_list =  Get-Content D:\Tools\DBA\SQLPRD_FINAL.txt
$SQL_Server_list | Get-DbaLastBackup | Where-Object { ($(get-date $_.LastFullBackup) -lt (Get-Date).AddDays(-1)) } | Format-Table -AutoSize | Out-File D:\Tools\DBA\SQL_Check_PRD_Backup.txt -Width 255

It works fine except the SQL AG.

I have SQLAG_Listener, SQLAG_NODE1, and SQLAG_NODE2 in SQLPRD_FINAL.txt. I set full backup prefer secondary on AG and I do get the full backup from SQLAG_NODE2.

But I still get "database not fully backed up in 24 Hours" alerts from  SQLAG_Listener and SQLAG_NODE1. I cannot exclude the SQLAG_Listener or SQLAG_NODE1 since the fail-over might happen.

How can I avoid this?

BTW: If I do the full backup against primary side, I will get the alerts from secondary side instead...

Thanks in advance!


texts: Postgres GROUP BY id works on table directly, but not on identical view

texts:
Lets say I have two tables. One of them references the other in a one-to-many relationship:

CREATE TABLE t (
  id SERIAL PRIMARY KEY
, name text
);

INSERT INTO t (name) VALUES ('one'), ('two'), ('three');

CREATE TABLE y (
  id SERIAL PRIMARY KEY
, tid INTEGER REFERENCES t
, amount INTEGER
);

INSERT INTO y (tid, amount) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(3, 1);

I can query data from these tables in order to get t rows matching a certain condition set on t and y:

SELECT row_to_json(t.*) as "t_nogroup" FROM t
JOIN y ON t.id = y.tid
WHERE t.id = 1
AND y.amount > 1;

This yields

t_nogroup
=========
{"id":1,"name":"one"}
{"id":1,"name":"one"}

Which works, yes, but contains duplicates. I can filter out the duplicates by using a GROUP BY:

SELECT row_to_json(t.*) as "t_group" FROM t
JOIN y ON t.id = y.tid
WHERE t.id = 1
AND y.amount > 1
GROUP BY t.id;

Making:

t_group
=======
{"id":1,"name":"one"}

Great! However, when I try to exchange t and y for non-materialized VIEWs:

CREATE VIEW vt AS SELECT t.* FROM t;
CREATE VIEW vy AS SELECT y.* FROM y;

And run the same queries with the new VIEWs

SELECT row_to_json(vt.*) as "view_t_nogroup" FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1;

SELECT row_to_json(vt.*) as "view_t_group" FROM vt
JOIN vy ON vt.id = vy.tid
WHERE vt.id = 1
AND vy.amount > 1
GROUP BY vt.id;

I then get the error for the second query:

ERROR: column "vt.*" must appear in the GROUP BY clause or be used in an aggregate function

Why does this happen? Whatever logic that saw that * contained id when using the table t should still work for the view vt, right?

Fiddle link: http://sqlfiddle.com/#!17/26b0e/5

I'm running PostgreSQL 9.6. I'm also aware that this is a contrived example, but I am running into this error in a very similar one to many relation where multiple GROUP BY statements are needed and so just using DISTINCT isn't an option, like it would be for this example.


texts: Mysql dump restore doing some funny stuff

texts:
I am using linux platform. I also dump the data into a file and I restore into a server and I ran this command

[root@localhost /]# mysqldump -u root -p fms  < /device.sql

The funny part it keep running and this dump only consist of table data and I see other table data being run when I run this command where is the other table data comes from?


texts: Best approach to delete large table with foreign key constraints

texts: How to purge a table with a billion records that has foreign key to another table? Should one partition the table and the child table and use partition swap?


texts: MS SQL Server Cumulative Updates - Best Practices

texts:
I'm trying to get an idea of what recommended best practices are for the SQL Server Cumulative Updates.

Currently, we run on the idea of "do nothing unless an issue fixed by the CU is one we experience".  That works from an "if it ain't broke, don't fix it" approach, but I'm wondering if that's really a good idea since so many of the CUs have performance enhancements.  We're looking at perhaps adding the CU to the patches applied during our periodic maintenance cycles a month or two after the CU is released.

What do others do, and why?

As an update to the question that affects the answers below, on March 24, 2016, Microsoft's SQL Server team announced they were updating their servicing model.  Microsoft is recommending that all users install all CUs released after January 2016:

  As of January CU releases, these caution messages have been updated,
  we now recommend ongoing, proactive installation of CU’s as they
  become available. You should plan to install a CU with the same level
  of confidence you plan to install SPs (Service Packs) as they are
  released. This is because CU’s are certified and tested to the level
  of SP’s. Also, Microsoft CSS data indicates that a significant
  percentage of customer issues are often previously addressed in a
  released CU, but not applied proactively.  More so, CU’s contain added
  value over and above hotfixes. These also may contain supportability,
  logging, and reliability updates enhancing the overall experience.

  In addition to messaging and guidance updates, we have made updates to
  the CU acquisition model.

  Acquisition changes:

  CUs, of course, have traditionally been made available on the “Hotfix” server (accompanied by the “cautionary language” associated
  with a ‘QFE’ or ‘Hotfix’). The inconsistency here is that CUs are not
  really simple quick hotfixes anymore.  The encompassed updates are
  well tested at individual as well as full system integration levels
  today.
  Therefore, we are now placing the latest CU per mainstream supported baseline (2012 SP2/SP3 and 2014 RTM/SP1 today) on
  microsoft.com/downloads, just as is done for Service Packs today
  Additionally, we will soon release, and maintain, all CUs into the Windows Update Catalog to facilitate acquisition and distribution
  Only interim CU ‘On-Demand’ fixes will be placed on the hotfix server moving forward
  To reduce friction, downloading CUs from the microsoft.com/downloads will not require providing/receiving an email and URL
  We are also evaluating offering the latest CU as an Optional update on Microsoft Update, just like Service Packs today


texts: INSERT INTO remote table on different server with OUTPUT clause gives error

texts:
I have a procedure which makes one or two inserts into a remote table. To accomplish the second insert, I need to know the recently inserted Id of the first insert. To make this work my insert clause fascilitates the OUTPUT clause which let me know the Id of the first insert.

This works fine if the calling database (containing the procedure making the insert) and the receiver database resides on the same SQL Server, which is the case on my development environment. In the production environment, however, the calling database and the receiver database resides on different SQL Servers, which outputs the following error message:

  Msg 405, Level 16, State 1, Procedure ProcedureName, Line
  LineNumber. A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

Edit (the OUTPUT clause into temp table):

    declare @message table(MeddelandeKöId bigint primary key, MessageId bigint)
...
    output
    cast(substring(inserted.ApplicationCustomPublicData, 4, 16) as bigint) MessageQueueId,
    inserted.Id MessageId
    into @message

Which is the best approach to solve the current error? Should I make a simple select, disregarding the output statement, to get the recently inserted row or is there another better solution to this particular problem?

Thanks for your time!


texts: Unexpected (not helpful) result on first run of sp_BlitzIndex

texts:
I just ran sp_BlitzIndex for the first time and got 1 row:

Priority=-1,
Finding=sp_BlitzIndex(TM) v5.7 - September 01, 2017: All Databases,
Database Name=NULL,
Details: schema.table.index(indexed)=http://FirstResponderKit.org,
URL=From Your Community Volunteers

Environment information:

sp_BlitzIndex version: 5.7
SQL Server version: 2008 R2 (SP2), 10.50.4000.0 (X64)
Operating System: Windows 2008 T2, SP1


texts: How to create an index on an integer json property in postgres

texts:
I can't figure out for the life of me how to create an index on a property of my json column which is an integer.

I tried it this way (and also dozens of others)

CREATE INDEX user_reputation_idx ON users(("user"->>'reputation')::int)

It works just fine in a query (e.g. ORDER BY ("user"->>'reputation')::int)

What am I missing?

UPDATE

I'm getting a simple syntax error, however, I really have no clue why.

ERROR:  syntax error at or near "::"
LINE 1: ... user_reputation_idx ON users (("user"->>'reputation')::int)

The table definition is quite simple. It's just one column user of typejson.

So, looks like this:

CREATE TABLE users
(
  "user" json
)


texts: Mysterious merge replication problems creating a subscription

texts:
We have a merge publication which had been mostly working OK for a long time (years). Recently an article was added, we were informed the snapshot was out of date, so we regenerated it. Then we got error messages during initialization and found in the verbose error logs this problem:

  Msg 1934, Level 16, State 1, Procedure DDLTrigger_Index, Line 19
  INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

...fast forward to now. In experiments we found that yes it is possible to create a merge publication on the publisher and subscribe on our subscriber server, but only if using a different database. Went through this just to rule out permissions/etc and to determine it was truly the subscriber db that was the problem.

So, it seemed logical to remove replication from afflicted DB in hopes of cleaning out any buried metadata mess:

sp_removedbreplication @dbname = 'sickDB' @type = 'all'

So far so good. However now, I get the exact same error, but much earlier in the process. Specifically, when executing:

/****** Begin: Script to be run at Subscriber ******/
use [sickDB]
exec sp_addmergepullsubscription
@publisher = N'publisher',
 @publication = N'publication',
 @publisher_db = N'goodDB',
  @subscriber_type = N'Local',
   @subscription_priority = 0,
    @description = N'hope this works', @sync_type = N'Automatic'

(above edited slightly).

Is there some remaining prob lurking in the metadata? The original theory on the ANSI_PADDING stuff was there were in fact some tables in the subscriber DB w/ computed columns thus that seemed related. However some flipping of ANSI_PADDING settings (and Googling) didn't really lead to a conclusive answer.

Thanks so much for reading and let me know of any other info I can provide that may help.

Oh yeah, publisher and subscriber are both SQL Server 2005.


texts: Migrating Standalone named instance to AlwaysON

texts:
Scenario:
I have a named instance such as SERVER1\NAME.

I need to migrate this instance to an AlwaysON, but I'd like to use the same instance name in the listener. Is it possible?

I was planning a side-by-side migration:

Create 2 new instances. (SERVER2\NAME and SERVER3\NAME)
Create a Listener called SERVER1_NEW.
Once everything is ready for the cut-over, just rename the SERVER1 to SERVER1_old and then rename SERVER1_NEW to SERVER1.

Is that going to work?
The plan is to avoid changing the connection strings everywhere.


texts:
I want to copy a VIEW from one server to another. I could copy tables but I can't copy views. Is there a way to do so?
Thanks


texts: Extract date and time separately from a time with time zone column

texts:
I have a table in PostgreSQL 9.3 with the following column

....,
  created_at time with time zone NOT NULL DEFAULT now(),
...,

When I do a select created_at from trails; I get this

"02:32:44.62054-05"

How can I get the date in this format dd-mm-yyyy (01-11-2015)? I have tried select created_at::date from trails; but I get this error

ERROR: cannot cast type time with time zone to date

I changed to select date(created_at) from trails; and got this error

ERROR:  function date(time with time zone) does not exist

I want to do one select statement that will get the date and time separately in the format dd-mm-yyyy and hh:nn:ss in two columns i.e. select date(created_at) as date, time(created_at) as time from trails;


texts:
I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked.

InnoDB is listed under SHOW ENGINES;

Any idea what the cause is or how to fix this so other InnoDB sites can be used?

I had these errors:

MySQL ERROR No: 1030
MysqL Error: Got error -1 from storage engine


texts:
I want to use Sqlite3 with Python and created queries as follows:
import sqlite3
from sqlite3 import Error

db = sqlite3.connect("temp.sqlite")
cursor = db.cursor()

cursor.execute("PRAGMA foreign_keys = ON") #Turns foreign_key support on

cursor.execute("""DROP TABLE IF EXISTS tx""")
cursor.execute("""CREATE TABLE tx (
                    tx_index BIGINT NOT NULL PRIMARY KEY,
                    hash CHAR(64) NOT NULL,
                    rbf INT NOT NULL,
                    tx_in_value BIGINT NOT NULL,
                    tx_out_value BIGINT NOT NULL
                    );""")

cursor.execute("""INSERT INTO tx VALUES ('1','2','3','11','11')""")
cursor.execute("""INSERT INTO tx VALUES ('2','2','3','11','11')""")

cursor.execute("""DROP TABLE IF EXISTS txin""")
cursor.execute("""CREATE TABLE txin (
                    addr BIGINT NOT NULL,
                    value BIGINT NOT NULL,
                    tx_index BIGINT NOT NULL,
                    FOREIGN KEY (tx_index) REFERENCES tx (tx_index)
                    );""")

list_tuple = []
list_tuple.append(('11','12','1'))
list_tuple.append(('12','12','2'))
# Uncommenting the line below will throw an error
#list_tuple.append(('12','12','3'))

cursor.executemany("""INSERT INTO txin VALUES (?,?,?)""", list_tuple)

cursor.execute("""SELECT * FROM tx""")
cursor.execute("""SELECT * FROM txin""")

db.commit()
db.close()

The above queries, when tested without Python here, works fine but in the Python, execution gives following error:
cursor.execute("INSERT INTO txin VALUES (?,?,?)", insert_trx_in)
sqlite3.IntegrityError: FOREIGN KEY constraint failed
It is simpler version of the problem. In real problem, I insert using executemany Any idea?


texts: How can I efficiently search for all records from a given month, or a given year?

texts:
We have a couple queries that involve datetime fields. In particular, we are checking for things like

SELECT field1, field2, ..., field20 FROM table WHERE MONTH(startDate) == 1
SELECT field1, field2, ..., field20 FROM table WHERE YEAR(startDate) == 2014

So for example, maybe I want all January records (regardless of year), or I want all records from a particular year, or maybe I want all records for January 2014.

I have an index on the startDate column, but EXPLAIN tells me that both queries are doing full searches on the table because I'm using the MONTH and YEAR functions.

How can I make these queries more efficient?
Our table is very small, with about 100k records, but it still takes about 200-300 ms to complete.


texts: Do large longtext necessarily slow down a database

texts:
I have a MySQL database that is over a million rows, and has a longtext type data column. Most of these are fairly short, but a few dozen of these rows have more than a MB in the longtext column. No WHERE or LIKE statements are made on the longtext column. Only select statements a few rows at most at a time. It would be nice to keep this data in the database, but the database is slow. I have two tables with a million records, one is 100MB, but this one is 4GB, mostly in the longtext column. I'm not sure if that's a performance concern or not.

We're wondering if removing these rows would result in a performance increase for SELECT, CREATE, UPDATE commands, or if it should not have an effect.


texts: WITH ... AS statement not working as expected

texts:
I get this error:

It arises when I try to execute the following query:

with expensive_service as (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)
select *
from service except expensive_service;

I was trying to implement WITH ... AS (link to PostgreSQL docs).

This query gives me the desired output I'm looking for:

select *
from service except (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)

Any aid directing me where the error lies would be greatly appreciated!


texts: Postgres parallel seq scan making high CPU Utilization

texts:
When I run a select query which runs on a view in Postgres, it is starting 3 processes for a single query, which is causing high CPU utilization when multiple job runs in parallel.

How to avoid using parallel sequential scans and increase my query performance at the same time?


texts:
How does ADDM report work? When I take two snapshots
one for

START: 05-OCT-2011 09:00:00,
END  : 05-OCT-2011 10:00:00

and another one for

START: 06-OCT-2011 09:00:00,
END  : 06-OCT-2011 10:00:00,

what does it do?

Does it show the difference between 05 and 06 October during that hours(9,10)?

Or it will show you the whole interval from 05-OCT-2011 09:00:00 to 06-OCT-2011 10:00:00 compared to some baseline? (Note: I do not have any baseline..is there any default baseline?)

I was confused a little bit when I saw the dates on the generated report, there was written:

Analysis Period: from 05-OCT-2011 10:01 to 06-OCT-2011 10:00

Can you explain me? Thank you very much


texts: What's the difference between Replicate_Wild_Ignore_Table & Replicate_Ignore_Table?

texts:
In my site's master/slave setup:

Replicate_Wild_Ignore_Table = %.cache%, &
Replicate_Ignore_Table is empty.

What's the difference between the two?


texts: Oracle - How can I find the session id of a shell script (Linux)?

texts:
I have a Linux script run periodically from crontab which executes some sql scripts.  How can I get/identify the session of that running job?

thanks


texts: Change collation on partition column (SQL Server 2017)

texts:
I'm trying to change collation on column:

ALTER TABLE [dbo].[yourdad] ALTER COLUMN [yourmam] varchar(36) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL;

and it gives message like this:

  Msg 5074, Level 16, State 1, Line 1 The object 'yourdad' is dependent
  on column 'yourmam'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE
  ALTER COLUMN yourmam failed because one or more objects access this
  column.

It has partition schema and function that depend on this column.

Currently I have like over 40k table with such columns where collation should be changed.

Is it possible to change collation without a table recreation?

Drop the partitioned index(es) - deleted.
But when I'm trying to delete schema:

DROP PARTITION SCHEME yourdad

  Msg 7717, Level 16, State 1, Line 1 The partition scheme
  "yourdad" is currently being used to partition one or more
  tables.

It's a new case insensitive database collation on database I wan't to install, old one was - case sensitive.


texts: sp_executesql not accepting multiple types

texts:
I have the following table:
CREATE TABLE [dbo].[Settings](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Key] [varchar](50) NOT NULL,
   [Value] [nvarchar](max) NULL,
   CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([Id] ASC)
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I'm trying to run this query
exec sp_executesql
   N'INSERT INTO Settings ([key], [value]) VALUES (@k6,@v6), (@k8,@v8)',
   N'@k6 nvarchar(4000), @v6 nvarchar(4000), @k8 nvarchar(4000), @v8 bit',
   @k6=N'DefaultLocale', @v6=N'eb', @k8=N'ReCaptchaIsEnabled', @v8=1

Unfortunately, I'm getting this error:

Conversion failed when converting the nvarchar value 'eb' to data type
bit.

When I remove the bit definition or remove Nvarchar definition it working well. From what I see, I cannot mix different value types in this query.
Can you tell me why?
Thank you


texts:
I have a set of database on full recovery model, my backup schedule looks like this:

1 Weekly Full Backup
1 Daily Log Backup

Full backups are about 10GB, log backups are about 500mb per day.

What happens is that right now, I'm having a DataFile of 10GB but a log file of 30GB...

Could be an option to shrink the log file? Is this a good practice? I have read that is not recommended but I don't know the exact reason.

Thanks!


texts: PostgreSQL schemas, select same table from all schemas

texts:
My app is used by about 80k users every month.  I want to create separate PostgreSQL schemas for each user (a-la SaaS, for protection).

My app allows users to search other user's profiles, via:

SELECT user_id FROM user_table WHERE city = 'NYC';

What is the correct way to implement the same search functionality with unique schemas in place for each user?

Will performance suffer?


texts:
I want to know is it advisable to directly use the database user and passwaord as the user of web application generally i have seen everywhere they use a separate table inside database for managing (add/edit/delete) the users .

My question why NOT use the default database user name and password for my web application ?

Note: I want to reuse the user and role system which postgres already provide instead of making a new table of users. I think this will be good because

reduce the replication of user tables
not to worry about security password mgmt
can use roles of postgres to limit the data access
many other application can directly connect the database (in my case this is important as a desktop application will also access the database still the data will be secured by roles )


texts: MySQL8.0 Replication Got Stuck "Applying batch of row changes (delete)"

texts:
When I configured Master-Slave replication it was working fine but after five weeks it is showing the following error.

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 000.00.0.000
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000285
          Read_Master_Log_Pos: 220266227
               Relay_Log_File: Server-Name-relay-bin.000166
                Relay_Log_Pos: 176520331
        Relay_Master_Log_File: mysql-bin.000057
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 176520117
              Relay_Log_Space: 340974038948
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 818763
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: b0764107-670c-11ea-afde-000000000000
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Applying batch of row changes (delete)
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)


texts: max(max(col1), max(col2), ...) does not work

texts:
I have a table with three integer columns:

select max(col1), max(col2), max(col3) from mytable

I want the max of both three columns. But this does not work:

select max(max(col1), max(col2), max(col3)) from mytable

How to get this done?

I use PostgreSQL, but I hope that this can be solved with standard SQL.


texts: inaccurate statistics in multitenant database

texts:
We are using Azure SQL and have "multi tenant" database. All tables have tenantid + entityid pk. Some tenants have a lot of data and some tenants quite small.
Also auto update statistics is enabled.

Sometimes (quite often recently) after auto update of statistics  we get inaccurate statistics (depending on sample) and according to it tenant has 1 row (but actually 10-100k).
This leads to bad execution plans and performance problem for the whole db.

Maybe somebody has ideas how to fix it.


texts:
order by datetimecolumn orders a datetime column like this:

datetimecolumn
2014-09-04 00:00:00
2014-09-04 00:00:00
2014-09-04 01:00:00
2014-09-05 00:00:00
2014-09-05 01:00:00
2014-09-05 02:00:00

How can I order the data so the the values with 00:00:00 are last, but still order the values ascending by time like so:

datetimecolumn
2014-09-04 01:00:00
2014-09-04 00:00:00
2014-09-04 00:00:00
2014-09-05 01:00:00
2014-09-05 02:00:00
2014-09-05 00:00:00

Dumb business rule, I know I know... basically if "user" does not spec a time, the time does not matter (so it goes to the bottom of the list).


texts: Restore SQL Server database backup to other directory than root

texts:
I have a database backup file (db.bak) which I want to restore and import in my SQL Server 2008 R2 instance. However, the database takes approx 1 GB of space, and my C: partition is really strained at the moment - I don't have that much space left. (Yes, I know this is bad, but there's nothing I can do about it at the moment...)

I do have some space on my D:\ partition - can I somehow restore the backup and use a location on D:\ instead? If so, how?


texts: Trigger to enforce unique constraint on text column (Geometry) to resolve INSERT conflicts

texts:
I have a simple table with a geometry column like so:
CREATE TABLE geomtable
(
  gid serial,
  geom geometry,
  trip_count integer
);

I want a unique constraint on the geometry column, such that if the same geometry is being inserted again then, the ON CONFLICT clause should add the trip_count to the existing row.
So my insert query looks something like this:
INSERT INTO geomtable (geom, trip_count) values (<geom>, 123)
ON CONFLICT ON CONSTRAINT <equality_constraint>
DO UPDATE SET trip_count = geomtable.trip_count + EXCLUDED.trip_count;

I tried the approach to enforcing equality constraint as described here like so:
CREATE FUNCTION equality_constraint_func(
  id INT,
  gm GEOMETRY
)
RETURNS boolean AS
  $$
    SELECT NOT EXISTS (
      SELECT 1
      FROM   geomtable AS a
      WHERE  a.gid <> id
        AND  a.geom && gm
        AND  ST_Equals(a.geom, gm)
    );
  $$
  LANGUAGE sql
;

And adding a CHECK constraint like so :
ALTER TABLE geomtable
  ADD CONSTRAINT equality_constraint
  CHECK (equality_constraint_func(gid, geom))
;

However, this does not help me perform the DO UPDATE part of conflict resolution. It just raises an error on duplicate geometry. I of course can't create a primary key out of my geom column as I get the index row requires 42632 bytes, maximum size is 8191 error. I get a similar error on creating a unique index on the geom column. I tried creating a unique constraint using the above syntax by replacing CHECK with UNIQUE, but I get a syntax error. How would I go writing a trigger to enforce the unique constraint?
Specifically, how do I handle the conflict case? Let the insert happen as usual and then perform a delete etc.
My version information is as under:
PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"


texts: MySQL Update old data after adding new column

texts:
I have a table called review, which looks like that

| id | business_id|
|----|------------|
| 1  |     B1     |
| 2  |     B1     |
| 3  |     B1     |
| 4  |     B2     |
| 5  |     B2     |
| 6  |     B2     |

To this table I have added a new column called review_number, which represents an incremental sequence separately for each business (referenced via business_id) and this is handled with BEFORE INSERT trigger. This means for each business the review_number sequence would start from 1 and each new record increments it by one. The new column is BIGINT type and for default value is set to 0 for all existing records.

| id | business_id| review_number |
|----|------------|---------------|
| 1  |     B1     |       0       |
| 2  |     B1     |       0       |
| 3  |     B1     |       0       |
| 4  |     B2     |       0       |
| 5  |     B2     |       0       |
| 6  |     B2     |       0       |

The update trigger is as simple as it can get:

BEFORE INSERT ON review
FOR EACH ROW BEGIN
    SET NEW.`review_number` = (SELECT MAX(review_number) + 1 FROM review WHERE business_id = NEW.business_id);

Problem

My problem and question is, how would I go about updating all the old records after I would update my database where I have live data already. Desired result would be after updating the old data (and when adding new ones after):

| id | business_id| review_number |
|----|------------|---------------|
| 1  |     B1     |       1       |
| 2  |     B1     |       2       |
| 3  |     B1     |       3       |
| 4  |     B2     |       1       |
| 5  |     B2     |       2       |
| 6  |     B2     |       3       |
| 7  |     B1     |       4       | <--- new record for B1
| 8  |     B2     |       4       | <--- new record for B2

I have tried achieving it with some procedures but haven't found a neat way of helping old records. As much as I know then MyISAM engine supports a way to have multiple auto-increment fields and composite primary key on multiple fields and there the auto-increment would be separate for each PK, which would get me the desired result without triggers, but unfortunately I think I cannot change from InnoDB currently.

Looking for suggestions how to update review_number for all existing records so so these wouldn't be defaulted to 0 after migration scripts finish.


texts: Select Rows within a Group based on a condition

texts:
CREATE TEMPORARY TABLE TempSlotUpdated (
  slotId         INT(10)   NULL,
  slotstarttime  datetime  NULL,
  slotendtime    datetime  NULL,
  totalTime      time      NULL);

CREATE TABLE `Apt` (
  `slot_Id`           int(10) unsigned DEFAULT NULL,
  `apt_Id`            int(10) unsigned DEFAULT NULL,
  `time`              time NOT NULL,
  `assigned_To_Id`    int(10) unsigned NOT NULL,
  `created_At`        datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now, lets say if the Slots are updated so I need to delete some appointments
e.g.

Apt Table :

| slot_Id | apt_Id | time     | assigned_To_Id | created_At
| 2634    | 129    | 00:25:00 | 2              | 2018-10-25 16:00:00
| 2634    | 132    | 00:30:00 | 2              | 2018-10-26 16:40:00
| 2635    | 156    | 00:20:00 | 2              | 2018-10-27 16:00:00
| 2635    | 160    | 00:25:00 | 2              | 2018-10-27 16:40:00

TempSlotUpdated Table has  :

| slotId | slotstarttime          | slotendtime         | totalTime
| 2634   | 2018-10-31 16:00:00    | 2018-10-31 16:30:00 |  00:30:00
| 2635   | 2018-10-30 16:00:00    | 2018-10-30 16:30:00 |  00:30:00

Now, the slotId 2634 which initially lets say had 1 hour totalTime now only has 30 mins totalTime, so I need to find out the latest appointments that have to be reassigned/deleted.

So referring to Apt Table,

it will be record with apt_Id = 132 for slot_Id = 2634
&          record with apt_Id = 160 for slot_Id = 2635

I tried using Group By and Having but I am not sure how to use a condition for set of rows inside a Group.

This is what I Tried :

SET @suma = TIME('00:00:00');

Select * from
         (SELECT @suma:= ADDTIME(@suma, `time`) , CAST(@suma as Time)
          as time2, apt_Id
          FROM Apt
          WHERE slot_Id = 2634
          ORDER BY Apt.created_At ASC)
          as abc
          Where abc.time2 >= TIME('00:30:00');

I tried with hard coded value and this works for one value of slot ie. 2634 and 00:30:00 totalTime
I am now trying to use it with TempSlotUpdated table now which has multiple slots whose totalTime has changed.


texts: check if some tables exist or not in SQL Server database

texts:
What is the best way to check if more than one table exist in a database using SQL Server, I tried with this:

IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = N'Providers') OR NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = N'Customers')

but is there a better way to do it  ?


texts:
We plan to have our build server perform a backup (mongodump) from our Production environment and restore it (mongorestore) to the Test environment for each new release to Test.

Are there mongodump and mongorestore standalone tools that one could use for this or is it best to install the entire MongoDB package on our build server (which feels like an overkill)?

Executing mongodump without the full MongoDB installation:

We are using Windows 10 & MongoDB 3.2.


texts: MySQL Multiple Left Join returns wrong data

texts:
I have an question about MySQL Left Join. When I use only just one Left Join everthings working fine but If I add second Left Join MySQL return wrong results.

This is working example, it's querying last 3 month's payments and listed seperated by month;

SELECT d.thedate AS report_date, IFNULL(SUM(p.price),0) AS total_price
FROM (SELECT DATE(NOW()) AS thedate UNION ALL
    SELECT DATE(DATE_SUB( NOW(), INTERVAL 1 MONTH)) UNION ALL
    SELECT DATE(DATE_SUB( NOW(), INTERVAL 2 MONTH))) d
    LEFT JOIN payments AS p ON DATE_FORMAT(FROM_UNIXTIME(p.paydate),'%Y-%m') = DATE_FORMAT(d.thedate,'%Y-%m')
GROUP BY d.thedate ORDER BY d.thedate ASC

BUT, if I add total users with left join as extra, total_price and total_user return wrong data;

SELECT d.thedate AS report_date, IFNULL(SUM(p.price),0) AS total_price, COUNT(u.id) AS total_user
FROM (SELECT DATE(NOW()) AS thedate UNION ALL
    SELECT DATE(DATE_SUB( NOW(), INTERVAL 1 MONTH)) UNION ALL
    SELECT DATE(DATE_SUB( NOW(), INTERVAL 2 MONTH))) d
    LEFT JOIN payments AS p ON DATE_FORMAT(FROM_UNIXTIME(p.paydate),'%Y-%m') = DATE_FORMAT(d.thedate,'%Y-%m')
    LEFT JOIN users AS u ON DATE_FORMAT(FROM_UNIXTIME(u.registereddate),'%Y-%m') = DATE_FORMAT(d.thedate,'%Y-%m')
GROUP BY d.thedate ORDER BY d.thedate ASC

So, how can I solve this problem ?

Full example with test data: http://sqlfiddle.com/#!9/cd43ca8/1

Thanks.


texts: Large table and poor performance - what to do next?

texts:
We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical data. The table is currently 101 million rows of 35 columns, and growing at the rate of around 250,000 rows a day. Splitting the table into smaller tables unfortunately isn't really an option due to a mass of legacy code.

The database itself (around 100Gb) is held as a single file on a single SSD drive. The server has another two 10K SAS disks used for the boot OS, paging etc, and the server has 22Gb of RAM.

Although everything's running fine, we have a few dozen users who need to query the data in this table. We have limited control over what these queries do: sometimes it's pulling a few hundred rows from yesterday, at other times it's tens of thousands of rows from 6 months ago. 99.9% of the activity is reading rows; there is very little writing apart from the live data being INSERTed throughout the day. At peak times, simple queries that return a lot of data can take half an hour or more to complete.

We have indexes in place that are helping, but the ultimate bottleneck appears to be disk I/O. The SSD in place isn't the fastest, and as a result we're looking at retrofitting a RAID1+0 array of high-end SSD drives to increase performance (we've checked the array card can handle the throughput).

Assuming we have this array in place, what is the best plan to increase read throughput to this database? If we have a super-fast SSD array, is that enough? Or would partitioning the database into separate files on separate logical drives be a better idea, even though they're essentially destined for the same disks? Similarly, would splitting database and log files across logical drives in the same array make any difference?


texts: Slower running Query with better Filter?

texts:
mysql> SELECT  *
    FROM  history_uint h
    WHERE  h.itemid='1825689'
    ORDER BY  h.clock DESC
    LIMIT  2 OFFSET 0 ;
Empty set (0.00 sec)

Explain:

               id: 1
      select_type: SIMPLE
            table: h
       partitions: 2016_02_18,p2016_02_19,p2016_02_21,p2016_02_22,p2016_02_23,p2016_02_24
             type: ref
    possible_keys: history_uint_1
              key: history_uint_1
          key_len: 8
              ref: const
             rows: 1
         filtered: 100.00
            Extra: Using where

This is the problem query, which takes over 40 min to return 0 rows:

mysql> SELECT  *
    FROM  history_uint h
    WHERE  h.itemid='1825689'
      AND  h.clock>1456091116
    ORDER BY  h.clock DESC
    LIMIT  2 OFFSET 0 ;

Explain:

             id: 1
    select_type: SIMPLE
          table: h
     partitions: p2016_02_21,p2016_02_22,p2016_02_23,p2016_02_24
           type: range
  possible_keys: history_uint_1
            key: history_uint_1
        key_len: 12
            ref: NULL
           rows: 1
       filtered: 100.00
          Extra: Using index condition

Here is the table:

mysql> show create table history_uint\G
*************************** 1. row ***************************
       Table: history_uint
Create Table: CREATE TABLE `history_uint` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  `ns` int(11) NOT NULL,
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p2016_02_18 VALUES LESS THAN (1455840000) ENGINE = InnoDB,
 PARTITION p2016_02_19 VALUES LESS THAN (1455926400) ENGINE = InnoDB,
 PARTITION p2016_02_21 VALUES LESS THAN (1456099200) ENGINE = InnoDB,
 PARTITION p2016_02_22 VALUES LESS THAN (1456185600) ENGINE = InnoDB,
 PARTITION p2016_02_23 VALUES LESS THAN (1456272000) ENGINE = InnoDB,
 PARTITION p2016_02_24 VALUES LESS THAN (1456358400) ENGINE = InnoDB) */
1 row in set (0.00 sec)

Why does one query run faster than the other? And why is there such a big difference? Seems/feels like it's doing a full table scan?

The code uses h.itemid = '1825689' and not h.itemid = 1825689 (without quotes, though it is an integer column). It's the way it comes from app, but it shouldn't make a big difference. Execution plans are the same with quotes and without, as well as run time. First one, where is only itemId returns in milliseconds, second one that has itemId and clock runs for 40+ min.

I am using MySQL 5.7.10. I have run many permutations of that query already, and the one specifically without the quotes is still running, 7min + so I am guessing quotes made no difference.

Adding a Link to Explain Plan with JSON Format


texts: Check if property exists in an object inside array in JSONB field in Postgres

texts:
Here is my table data:

Corresponding sqlfiddle - http://sqlfiddle.com/#!17/e3f09

create table jsonData (
    id serial primary key,
    data jsonb
);

insert into jsonData (data) values (

$${
  "b": 2,
  "c": [
    {
      "d": {
        "e": true
      }
    },
    {
      "d": {
        "e": true
      }
    },
    {
      "d": {
        "e": true,
        "f": "I'm here"
      }
    }
  ]
}
$$::jsonb),
(
$${
  "a": 1,
  "c": [
    {
      "d": {
        "e": true
      }
    },
    {
      "d": {
        "e": true
      }
    },
    {
      "d": {
        "e": true
      }
    }
  ]
}$$::jsonb);

The question is - how do I select all records, where "f" property (path is the same everywhere - 'c -> d -> f') exists?


texts: Transaction Log Shipping Removal, Backup and Drop Database

texts:
I have  databases (about 15 different) Production Databases set for Log_Shipping and I would like to take a full backup of it and need to drop from production later on because we have already move it to new environment.

Should I, just end log shipping and back up database?
Should I, just take backup and keep log shipping on until I, drop it from current environment?
How should I remove log shipping from current environment at the time of drop of databases.

Thank You for help and time.


texts: Require Language In PostgreSQL Extension Control File

texts:
I'm developing a custom extension that uses plpythonu in several functions. I'd like to be able to require that plpythonu be installed in order to create my extension, but I don't see anything in the documentation about requiring a language via the control file.

I tried adding plpythonu to the 'requires' statement in the control file but it looks for an extension named plpythonu rather than the language.

Can this be done?


texts: How do I debug max_allowed_packet going to -1?

texts:
I am using Amazon's MySQL Aurora and using JDBI on the client side. Every once in a while, the connection goes bad and I get errors like this -

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (70 > -1).
You can change this value on the server by setting the 'max_allowed_packet' variable.

That -1 feels suspicious and seems like an integer overflow or something. Since I have multiple servers and only one of them randomly sees this, I am guessing this has something do with client-side mysql. The error is too fleeting to be able to debug. Any ideas on why this could be happening?


texts:
I have a sqlite data base with the full permissions:
-rwxrwxrwx 1 ibse ibse 21504 кві 15 16:26  mydb.dat*

When I try to insert into it from the root user, the error attempt to write a readonly database occurs:
ibse@ibse-VirtualBox:/var/internal$ sudo su

root@ibse-VirtualBox:/var/internal# sqlite3  mydb.dat

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

sqlite> INSERT INTO DATA (sent) VALUES (1);
Error: attempt to write a readonly database
sqlite> ;

If I insert into it from the owner of db (ibse) everything is ok, but how I can do it from root without changing data base ownership?
UPDATE:
The folder containing the database file has a sticky bit in permissions. If I remove the sticky bit (chmod -t), I can INSERT into db, but why?
In accordance with the https://en.wikipedia.org/wiki/Sticky_bit the root user can manage all the files in the folder, even if sticky is set


texts: PostgreSQL + pg_xlog folder with very old files and how to purge pg_xlog files

texts:
Under folder - /var/lib/pgsql/DB/pg_xlog

We have very old files as the following:

-rw------- 1 postgres postgres       56 May 31  2018 00000002.history
-rw------- 1 postgres postgres      331 Oct 14  2018 000000020000000A00000015.00000020.backup
-rw------- 1 postgres postgres 16777216 Nov 15  2018 000000020000000B00000028
-rw------- 1 postgres postgres 16777216 Nov 18  2018 000000020000000B00000029
-rw------- 1 postgres postgres 16777216 Nov 19  2018 000000020000000B0000002A
-rw------- 1 postgres postgres 16777216 Nov 19  2018 000000020000000B0000002B
-rw------- 1 postgres postgres 16777216 Nov 19  2018 000000020000000B0000002C
-rw------- 1 postgres postgres 16777216 Nov 21  2018 000000020000000B0000002D
-rw------- 1 postgres postgres 16777216 Nov 22  2018 000000020000000B0000002E

In order to purge this old files we restart the PostgreSQL service

The other problem is the huge size of pg_xlog folder

83G     pg_xlog

we restart the PostgreSQL according to many recombination from many posts
but this restart not decrease the pg_xlog folder size

We set also the wal_keep_segments = 0 in postgresql.conf file , then restart The PostgreSQL
But folder  pg_xlog still not decreased

Any other suggestion how to purge the old files under - /var/lib/pgsql/DB/pg_xlog

Can we simply to delete the files from 2018?


texts: ORA-00990 error while trying to grant priviliges: Why is that?

texts:
After creating a user named suhail I am trying to grant that user some privileges. But I am getting an error saying missing or invalid privilege . Why is that ?

C:\Windows\system32>SQLPLUS / AS SYSDBA

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 5 08:11:35 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL> CREATE USER suhail IDENTIFIED BY InnocentDesire02;
User created.
SQL> GRANT CREATE TABLE,ALTER TABLE,INSERT TABLE,SELECT TABLE,DELETE TABLE,DROP
VIEW,CREATE ROLE,CREATE SESSION,ALTER SESSION TO suhail;

GRANT CREATE TABLE,ALTER TABLE,INSERT TABLE,SELECT TABLE,DELETE TABLE,DROP VIEW,CREATE
ROLE,CREATE SESSION,ALTER SESSION TO suhail

               *
ERROR at line 1:
ORA-00990: missing or invalid privilege


texts: How do I call a collection of tables with normalized (generic) information

texts: In a database design there are many fields with information that needs to be normalized. For instance for an address record countries can be normalized but also the address type (i.e. private address, billing address, business address). This leads to tables with information that, once implemented, only changes on a very occasional basis. What is the generic name to refer to these kind of tables?


texts: How to insert a record returned by a function into a table

texts:
I have a plpgsql function that takes one input parameter and returns multiple columns of data as a record.

CREATE FUNCTION my_func(
    IN id bigint,
    OUT foo bigint,
    OUT bar double precision
    )
  RETURNS RECORD AS
$BODY$
BEGIN
    -- some calculations
    foo = id + 1;
    bar = 42.0;
END;
$BODY$
  LANGUAGE plpgsql;

I want to execute my_func for each row of a table (table_input) with the id column as input parameter and insert the returned row into another table (table_output).
The table table_output has the same columns as the columns return by my_func.

How can I do that?

The query

SELECT my_func(id) INTO table_output FROM table_input;

gives me:

ERROR:  column "my_func" has pseudo-type record

The following query does what I want, but my_func() gets called twice per row:

INSERT INTO table_output
SELECT
 (my_func(table_input.id)).foo,
 (my_func(table_input.id)).bar
FROM table_input;

Any suggestions?


texts: Postgres: why did this trigram index slow down regexp queries?

texts:
I have a TEXT keyvalues column in Postgres:

select * from test5 limit 5;

 id |                      keyvalues
----+------------------------------------------------------
  1 | ^ first 1 | second 3
  2 | ^ first 1 | second 2 ^ first 2 | second 3
  3 | ^ first 1 | second 2 | second 3
  4 | ^ first 2 | second 3 ^ first 1 | second 2 | second 2
  5 | ^ first 2 | second 3 ^ first 1 | second 3

My queries must exclude the ^ character from the middle of the match, so I'm using regular expressions:

explain analyze select count(*) from test5 where keyvalues ~* '\^ first 1[^\^]+second 0';

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=78383.31..78383.32 rows=1 width=8) (actual time=7332.030..7332.030 rows=1 loops=1)
   ->  Gather  (cost=78383.10..78383.30 rows=2 width=8) (actual time=7332.021..7337.138 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=77383.10..77383.10 rows=1 width=8) (actual time=7328.155..7328.156 rows=1 loops=3)
               ->  Parallel Seq Scan on test5  (cost=0.00..77382.50 rows=238 width=0) (actual time=7328.146..7328.146 rows=0 loops=3)
                     Filter: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
                     Rows Removed by Filter: 1666668
 Planning Time: 0.068 ms
 Execution Time: 7337.184 ms

The query works (zero rows match), but is way too slow at > 7 seconds.

I thought indexing with trigrams would help, but no luck:

create extension if not exists pg_trgm;
create index on test5 using gin (keyvalues gin_trgm_ops);

explain analyze select count(*) from test5 where keyvalues ~* '\^ first 1[^\^]+second 0';
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1484.02..1484.03 rows=1 width=8) (actual time=23734.646..23734.646 rows=1 loops=1)
   ->  Bitmap Heap Scan on test5  (cost=1480.00..1484.01 rows=1 width=0) (actual time=23734.641..23734.641 rows=0 loops=1)
         Recheck Cond: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
         Rows Removed by Index Recheck: 5000005
         Heap Blocks: exact=47620
         ->  Bitmap Index Scan on test5_keyvalues_idx  (cost=0.00..1480.00 rows=1 width=0) (actual time=1756.158..1756.158 rows=5000005 loops=1)
               Index Cond: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
 Planning Time: 0.412 ms
 Execution Time: 23734.722 ms

The query with the trigram index is 3x slower! It still returns the correct result (zero rows). I expected the trigram index to figure out immediately there's no second 0 string anywhere, and be super fast.

(Motivation: I want to avoid normalizing the keyvalues into another table, so I'm looking to encode the matching logic in a single TEXT field using text indexing and regexps instead. The logic works, but is too slow, as is JSONB.)


texts: How to enter records with keys to an initially empty B+ tree?

texts:
Show the result of entering the records with keys in the order (1, 2,
  3, 4, 5) to an initially empty B+ –tree  of order m = 3. In case of overflow, split the node and do not re-distribute keys to neighbors. Is it possible to enter the records with keys in a different order to have a tree of less height?

  From Relational DBMS Internals, chapter 5: Dynamic Tree-Structure Organizations, p.50

I'm not good on this yet I tried to do ≤ on the left and > on the right :

Until insertion of 1,2 :

Then as far as we have to split the node and not to redistribute keys to neighbors (I understand it as son-nodes) I inserted only on the right of cell with 2 :

And I continued doing the same with when inserting 5 :

But this is quite odd, I never saw empty nodes like these ... And I don't know if it respects some very basic B-trees properties :

each node has at most (m-1) keys and at least (⌈(m/2)⌉-1) keys unless a key can be empty and I would understand the key as a "pointer".

First attempt : error on the order revealed an ambiguous tree

At the beginning I misunderstood what an "order" was (the max number of children per node). So I thought a node could have three spaces (and therefore 4 children. I was creating a tree of order 4 I think :

Until insertion of 1,2,3 :

Insertion of 4, as far as we have to split the node and not to resdistribute keys to neighbours (which seems contradictory) I would let 1,2,3 and 4,5 on the right leaf after 3 :


texts: Obtaining gratis copy of the SQL specification?

texts: There used to be a public link to the JTC 1/SC 32 working drafts of 9075:2003, but the link I had has not worked since they started on the post-2003 revisions. Given that there are publicly available copies of many of their other specifications (11179-1:2004, for example), I'm wondering if any one knows of any ways to legally get access to the ISO/IEC 9075 specifications. Perhaps there is a link to the working drafts, or a Foundation that provides access to it for something less than the roughly $3000 USD it would cost me to learn SQL from the standards body instead of relying on implementation-specific books and documentation.


texts: mysql UNIQUE KEY multiple columns with one column as NULL

texts:
I understand the way UNIQUE KEY works in Mysql 5.5+, and understand that NULL allows duplicate entries to be created.

In following schema and I would finally want that family_relation should have only a unique (father, mother, kid) relationship with optionally kid (as kid_id can be null). In the INSERT INTO ON DUPLICATE KEY at the end, you will see that two family_relation entries are created for same father and mother due to kid_id=null. How can I prevent this and enforce mysql to have only one relationship?

DROP TABLE IF EXISTS family_relation;
DROP TABLE IF EXISTS person;

CREATE TABLE person (
    person_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

CREATE TABLE family_relation (
    pk INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    father_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (father_id) REFERENCES person(person_id) ON DELETE CASCADE,
    mother_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (mother_id) REFERENCES person(person_id) ON DELETE CASCADE,
    kid_id INTEGER UNSIGNED COMMENT 'family may or may not have kids',
    FOREIGN KEY (kid_id) REFERENCES person(person_id) ON DELETE CASCADE,
    UNIQUE KEY one_father_mother_with_possible_kids (father_id, mother_id, kid_id)
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

INSERT INTO person (name) VALUES ('john the father');
INSERT INTO person (name) VALUES ('ashley the mother');
INSERT INTO person (name) VALUES ('dennis the kid');

INSERT INTO family_relation (father_id, mother_id) VALUES((SELECT person_id from person where name like '%john%'), (SELECT person_id from person where name like '%ashley%'))
ON DUPLICATE KEY UPDATE father_id=VALUES(father_id), mother_id=VALUES(mother_id);

-- This creates a duplicate entry of family_relation due to kid_id = NULL. HOW DO I PREVENT THIS?
INSERT INTO family_relation (father_id, mother_id) VALUES((SELECT person_id from person where name like '%john%'), (SELECT person_id from person where name like '%ashley%'))
ON DUPLICATE KEY UPDATE father_id=VALUES(father_id), mother_id=VALUES(mother_id);
SELECT * from family_relation;

/* Result is as
"pk" "father_id" "mother_id" "kid_id"
"1" "1" "2" \N
"2" "1" "2" \N
*/

here is http://sqlfiddle.com/#!2/834d6/1


texts:
Possible Duplicate:
  What is the difference between select count(*) and select count(any_non_null_column)?

I have heard it commonly stated that when counting the number of rows in a query, you should not do a COUNT(*) but you should do a count on an indexed column.

I've seen DBAs, when counting the number of rows, run SELECT COUNT(1) FROM table;.

What is the 1 in the query?

I've tried putting in other numbers (2, 0, -1) and result is always the same as using 1.

Is this just a shortcut rather than having list a specific column to count?

Are there any performance differences to using a constant versus listing a column name?

I'm currently using MySQl 5.1.60.


texts: Clustered index performance consideration in MySQL & PostgreSQL

texts:
In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write.

Now, in PostgreSQL, there is no such thing as clustered limitation as in MySQL. What are the impact if I pick  UUID as the PK? Does the database write performance killer also exists in PostgreSQL like in MySQL?


texts: Is there a measure of time frame as to which the execution plan of inline queries will cached?


texts:
I run postgres server (10.6) on my pc just for some custom programs. Problem is every time I restore a backup file of 600mb - the pg_wal directory increases in size for 600mb. Is there a way to easily restore + get rid of the wal files?

I have read about pg_archivecleanup utility but I don't think it helps in my case.

EDIT:
Alternatively, is it possible at all to restore without recreating wal files as I don't need them. What will be the config entries for this.


texts:
How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor.

Below is some pseudo code, in this example I have kept the Select statement simple but in real life it is a long query with multiple joins, I have to use the identical SQL twice to join to 2 different tables and as it is quite long and can be changed in the future hence I want to be able reuse it.

I have tried creating a view and storing the results of the select statement in it but it seems I can't create a view inside the cursor loop, when I tried I am getting "Encountered the symbol "CREATE"" error.

DECLARE TYPE cur_type IS REF CURSOR;
CURSOR PT_Cursor IS

    SELECT * FROM Table1

    PT_Cursor_Row   PT_Cursor%ROWTYPE;

BEGIN
 OPEN PT_Cursor;

 LOOP
    FETCH PT_Cursor INTO PT_Cursor_Row;
    EXIT WHEN PT_Cursor%NOTFOUND;

    Select ID From Table2 --this is actually a long complext query
        INNER JOIN Table3 ON Table2.ID = Table3.ID
    WHERE Table2.ID = PT_Cursor_Row.ID

    Select * From Table2 --this is actually a long complext query
        LEFT JOIN Table4 ON Table2.ID = Table4.ID
    WHERE Table2.ID = PT_Cursor_Row.ID

END LOOP;

 CLOSE PT_Cursor;
END;


texts: SQL Server 2008 R2 - Should I be worried at this statistics regarding indices causing locks?

texts:
We have some mild table locking issue during our peak hours so I figured the following query query taken from this site would be a good start. And boy, does it show me some seriously concerning statistics:

SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,row_lock_count
,row_lock_wait_count
,CAST(100. * row_lock_wait_count / NULLIF(row_lock_count,0) AS decimal(6,2)) AS row_block_pct
,row_lock_wait_in_ms
,CAST(1. * row_lock_wait_in_ms / NULLIF(row_lock_wait_count,0) AS decimal(12,2)) AS row_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (7, NULL, NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count + page_lock_wait_count DESC, row_lock_count + page_lock_count DESC

Some of the indices in the database, particularly IX_SRCHGT_3 and IX_SRCHGT_4 seems to be causing huge rowlocks, but I have absolutely no clue what these big numbers mean. What are their values relative to? Is it statistics based on when the indices were created?

Should I be deathly worried about my potentially wrongly placed indices? How do I prevent this from happening?


texts:
I install a mysql 4.1(Ok it's very old version) on debian.
the service start and stop correctly but show this error.

    root@VM03:~# /etc/init.d/mysql start
/etc/init.d/mysql: 63: /etc/init.d/mysql: source: not found
Starting MySQL
/etc/init.d/mysql: 117: /etc/init.d/mysql: log_success_msg: not found

and this

root@VM03:~# /etc/init.d/mysql stop
/etc/init.d/mysql: 63: /etc/init.d/mysql: source: not found
Shutting down MySQL
../etc/init.d/mysql: 117: /etc/init.d/mysql: log_success_msg: not found

i use the mysql.server default.

Someone to help.
Best,
JGois


texts: Loading XML data from SQL Server directly into MarkLogic?

texts:
Has anyone loaded XML data from SQL Server to a MarkLogic document store? This would be preferable to dumping XML to file system and then using mlcp.

Maybe with SSIS?


texts: How to make PostreSQL functions private (inaccessible to end users)?

texts:
When writing a set of PostgreSQL functions with procedural languages, is it possible to make some of the supporting functions private and not accessible by end users?

I wanted to refactor some common patterns into SQL functions, but are concerned about introducing potential SQL injection in the function parameters. For example, I wanted to implement a copy_table(src regclass, target text) function for copying a table to new table (in a previous question), the second parameter can be subject to SQL-injection if unquoted. (Quoting the 2nd parameter has its own issues as discussed there). On the one hand, I wanted to write this function as a helper function for other public-facing functions; on the other, I don't want to expose it to everyone for security concerns.

Is there a way to hide the dangerous supporting functions so that they can only be called by my functions?