texts:
I have a table named foo_partition_20210501, which has a size of ~10GB and contains ~100M rows. As you may guess it is a partition of a partitioned table, but I don't think has something to do with the problem I'm encountering, as I'm only working on one partition at a time.
The table has multiple columns, but three are of interest:

item_id: integer
item_date: date
code: custom type enum_code

Many of the records of the table have a code = 'A', and I often filter on the tuple (item_id, item_date). So I've created the following partial index:
CREATE INDEX ix_foo_partition_20210501_partial_index
ON public.foo_partition_20210501
USING btree (item_date, item_id)
WHERE (code <> 'A'::enum_code)

The partial index is ~50MB large.
When I query the table such as
SELECT *
FROM foo_partition_20210501
WHERE item_date='20210521'
AND item_id=137415
AND code IN ('B', 'C')

The resulting plan (explain analyze+buffers) contains this:
->  Index Scan using ix_foo_partition_20210501_partial_index on foo_partition_20210501  (cost=0.43..18.00 rows=8 width=62) (actual time=460.775..20533.036 rows=63 loops=1)

Index Cond: ((item_date = '2021-05-21'::date) AND (item_id = 137415))
"Filter: (code = ANY ('{B,C}'::enum_code[]))"
Rows Removed by Filter: 1841
Buffers: shared hit=2 read=1124 dirtied=76
I/O Timings: read=20418.129

So the query plan is correct and does use my partial index, but is very slow (20s). It does read the disk instead of the cache, but I still don't understand why it would be so slow. The index is fairly small (only 50MB), and the number of overall rows fetched is less than 2000 with only 63 being kept.
Do you have any insight on why this is slow ? is it normal disk read performance for PostgreSQL ?
Details of the platform:

PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
SAN Disk

Running the same query multiple times:
When hitting the cache, it's very fast.
Index Scan using ix_foo_partition_20210501_partial_index on foo_partition_20210501  (cost=0.43..18.00 rows=8 width=62) (actual time=0.080..4.672 rows=63 loops=1)
Index Cond: ((foo_date = '2021-05-21'::date) AND (foo_id = 137415))
"Filter: (code = ANY ('{B,C}'::enum_code[]))"
Rows Removed by Filter: 1843
Buffers: shared hit=1128

Some times hitting the disk is still slow but only 8s here:
Index Scan using ix_foo_partition_20210501_partial_index on foo_partition_20210501  (cost=0.43..18.00 rows=8 width=62) (actual time=30.511..8135.828 rows=63 loops=1)
Index Cond: ((foo_date = '2021-05-21'::date) AND (foo_id = 137415))
"Filter: (code = ANY ('{B,C}'::enum_code[]))"
Rows Removed by Filter: 1843
Buffers: shared hit=4 read=1124
I/O Timings: read=8084.544


texts:
I have this scenario. Data files are transferred from multiple stores (transactions) to the head office as they happen. For each store the table in question is pretty big in size. At the head office, that same table contains the same info from all stores which makes it huge. The data is transferred through files as there isn't any direct connections between the HO and the stores.

I'm trying to find an efficient way to make sure that the data from each store has made it safe a sound to the head office table. To do this I have tried to do a checksum_AGG of a checksum for the fields that should match between the store and the HO for a specific Day for a specific store. But the performance was poor. I've also tried the sum of specific numeric columns and do the same at host for a specific Date and store to compare but the performance seems to be poor also.

I've tried to use a persisted computed column to calculate a row's hashbytes and maybe use that but unfortunately, I couldn't, since the table was created with ansi_nulls off, which is a restriction of the persisted computed columns. And it's unthinkable to fix that issue on a very large scale of stores and HOs.  I also thought of creating a trigger to fill any type of checksum per row, but the reality is that I will need to do this on multiple tables (maybe 50 tables) and to add triggers on every table will be very heavy, especially in OLTP system at the stores.

I have verified the fragmentation of the indexes and all is good.

Ideally it would be great if I transfer all the data from the store line by line and compare it against the HO but it's unthinkable since it's way too big.

I'm looking for ideas that I can explore to see if performance is acceptable. The idea is to get some type of checksum of a table per date (store/date) from a store and compare it with the same operation at the HO. This means that I would only need to transfer a checksum value per store and date to the HO.
The solution must be compatible from SQL Server 2008 up until the latest SQL Server version.

More info as requested
The sample table that I've been working with is one from a HO for my tests...
Size: 160Gb for 105 stores and 633 days (200 columns... I know... not ideal but it's what it is and practically impossible to change for all stores and HOs)

Example of queries tried to create a daily checksum per store:

select [trsDate],[store],CHECKSUM_AGG(checksum(amt,qty,total))
from BigTable
where store='111'
AND trsDate BETWEEN '2016-01-01' and '2016-01-30'
group by [trsDate],[store]
order by [trsDate],[store]

SELECT trsDate, store,'BigTable',
amt=SUM(ISNULL(amt,0)),
qty=SUM(ISNULL(qty,0)),
total=SUM(ISNULL(total,0))
FROM BigTable
WHERE trsDate BETWEEN '2016-01-01' and '2016-01-30'
AND store='111'
GROUP BY trsDate,store

select [trsDate],[store],CHECKSUM_AGG(checksum([trsDate],[store],terminal,trsNumb,linenumb))
from BigTable
where store='111'
AND trsDate BETWEEN '2016-01-01' and '2016-01-30'
group by [trsDate],[store]
order by [trsDate],[store]

SELECT [trsDate],[store], HASHBYTES('SHA2_512', concat(COUNT(*),CHAR(26), SUM(amt+qty+total))
from BigTable
where store='111'
AND trsDate BETWEEN '2016-01-01' and '2016-01-30'
group by [trsDate],[store]
order by [trsDate],[store]

Execution plan shows an index Seek 100%... Adding an index to Store\Date and include amt,qty and total has helped drastically but I have to investigate the impact of this index for the thousands of transaction processed and transfer.
Ideally I would love to have a checksum on all columns but the more I dig the more I realise that it will practically be impossible (otherwise I would need to double the size of each table by putting all columns in the indexe's include)


texts: 8k row overflow error when updating row of size 5k

texts:
I'm trying to update a target table which has one row of size 5k to a row of size 5k also.

Since it's one row it's easy to know the row actual size:

select *
from sys.dm_db_index_physical_stats(DB_ID('RODS_HSD_ES'),
OBJECT_ID(N'TBL_BM_HSD_SUBJECT_AN_148_REPRO'), NULL, NULL, 'DETAILED')

Reproduce

Table wasn't altered since creation. don't see any reason why it should fail. Ideas?


texts: Show specific data from a partitioned table in SQL Server. Data Archiving using Table Partitions

texts:
I have created partitions on a table i.e. tblUserDetails having the following record details based on the partitions:

ObjectName       PartitionNumber    RangeValue  RowCnt
tblUserDetails      1               01-01-2014  500
tblUserDetails      2               01-01-2015  347
tblUserDetails      3               01-01-2016  1948
tblUserDetails      4               01-01-2017  4077
tblUserDetails      5               01-01-2018  478

Now I want to hide the data of year 2014 i.e. display the data from year 2015 to 2018 and it should be available in the same table so that if the user wants to see the data it can be seen.

eg: Whenever I fire a query like like Select * from tblUserDetails. It should list all the records from year 2015 to 2018 and not the records of 2014.
But if user still wants to see the records of 2014 then the user will have to use some key like where or some thing explicitly to show all the records.

My requirement is to archive the data before a specific date. But still if user wants to see old records then on click of a button 'Show Old Records' every thing should be displayed.

I dont want to use the Swtich Command, because in that i will have to create another table.

I want to achieve this without making any changes in the existing code. And this is just a one table. I want to do this on many tables.

So, if there is any option to hide or disable a partition of a table then that will also be helpful.


texts: Is there any way to test which clustered index is better for specific queries?

texts:
We've been querying and processing a growing table of events in our system, which was fine without specific indexes for now. However we've noticed a decrease in performance and are wondering what we can do to improve that.

I've created a few test tables to test different indexes:

SELECT TOP 1000000 * INTO IndexTest1
FROM Events

SELECT TOP 1000000 * INTO IndexTest2
FROM Events

SELECT TOP 1000000 * INTO IndexTest3
FROM Events

Next I added different clustered indexes on columns that are often used to query the events or process them. Most of the time we use the Timestamp or the EventType.

CREATE CLUSTERED INDEX TimestampEventType
ON IndexTest1 (Timestamp, EventType)

CREATE CLUSTERED INDEX Timestamp
ON IndexTest2 (Timestamp)

CREATE CLUSTERED INDEX EventType
ON IndexTest3 (EventType)

Next I tested two different types of queries. However I currently have no way of noticing which performs best.

SELECT * FROM IndexTest1
WHERE EventType = 'String'

SELECT * FROM IndexTest2
WHERE EventType = 'String'

SELECT * FROM IndexTest3
WHERE EventType = 'String'

SELECT * FROM IndexTest1
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

SELECT * FROM IndexTest2
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

SELECT * FROM IndexTest3
WHERE Timestamp >= '2018-03-14' AND Timestamp <= '2018-03-20' AND EventType = 'String'

What I expected:
I expected IndexTest1 to perform best for both queries, seeing as it is the only one that includes both EventType and Timestamp in its clustered index.

Results second query set:
When turning on the live query statistics, I get varying times for execution for all queries, ranging between 8 - 20 seconds.

When looking at the execution plans I do notice that IndexTest1 has a much lower Estimated I/O Cost and Estimated Operator Cost for the second set of queries than IndexTest2 & IndexTest3. The difference is relatively large, IndexTest1 has an I/O Cost of about 0.003 whereas IndexTest2 & IndexTest3 are around 25 and 40 respectively.

Question: Can I conclude that for the second query set IndexTest1 is the best index?

Results first query set: When looking at the execution plans for the first set of queries IndexTest3 actually has the best performance (although barely). All of them are around an I/O cost of 40, with IndexTest2 being the worst and IndexTest 3 being the best.

Questiong: Does that mean that the indexes I added have no real effect on the first query set? And if so, why would that be if EventType is included in the clustered index of IndexTest1?

I have very little experience with testing indexes and creating them. Is there any other way I can test which index would perform best for the above queries? Or is there something else I should try out?

Or does anyone have any reading material they could link me to for more information about indexes and testing them?


texts: Dealing with Oracle ARCHIVELOG during huge updates

texts:
It's required to update columns with metadata globally in all rows of all tables in DB. Let's say every table has a column MY_META and the aim is roughly

update ANOTHER_TABLE set MY_META = 'HELLO'

for each table.

Estimated total rows count is 2e9.

Suppose ARCHIVELOG mode is on, so during those updates a lot of extra space is going to be consumed.

The process of updates is planned to be running in production DB simultaneously with business transactions, which should not be lost.

The simplest thing to start with is temporarily installing plenty of hardware for ARCHIVELOG files.

Is there an elegant way to achieve the same goal programmatically or via tuning "secret options"?


texts: Do MySQL limitations apply to MariaDB as well on AWS RDS

texts: I am thinking of migrating MariaDB 10.3.13 to RDS, I want to do an analysis of what limitation can be caused by migrating to RDS, I could find no documentation by AWS for MariaDB for issues and imitations, however there is a MySQL doc for issues and limitations, I am wondering do the same limitations apply to MariaDB?


texts: Window function not working on non selected rows

texts:
I'm creating auto switching partitions procedure.
I'm creating table that looks like this:

CREATE TABLE #MaxPartitionRows
    (MaxRows BIGINT NOT NULL,
    PNumber INT NOT NULL,
    PFName SYSNAME NOT NULL,
    Value VARCHAR(30) NOT NULL)

I'm using it to decide whether to merge partitions or not.
The columns are: max rows in tables per partition function, partition number, partition function and boundary value.
The logic is to merge partition only if the next partition is also empty.
So I tried this query:

 SELECT @vcPartitionFunction    = PFName,
    @vcBoundaryValue        = Value,
    @NextPartitionRowNum    = LEAD(MaxRows) OVER (ORDER BY PNumber)
FROM #MaxPartitionRows
WHERE PNumber = @iMinPartitionNumberToSwitchOut

The problem that I've faced is that @NextPartitionRowNum gets NULL as a value, even if there are rows in the table with bigger partition number.
My guess is that engine don't need to scan next row so it returns NULL.
Is that the correct assumption?
In this case the workaround is simple, I just use

SELECT @NextPartitionRowNum = MaxRows
    FROM #MaxPartitionRows
    WHERE PNumber = @iMinPartitionNumberToSwitchOut + 1

But what if I couldn't do that?


texts:
We have a need to send PostgreSQL DB backup to CRM developer.
We send them and get feedback that the backup can't be "uzipped", as archive is broken.
The backup script snippet looks like this:
pg_dump -c -O -U postgres $x -h localhost -p 5432 --disable-dollar-quoting -i | gzip > $BACKUPDIR/$DT-$x.gz

We chceck the backup process and in our opinion there is no any issue with backuping system.
btw. They used 7Zip to unzip.
Question: What would you suggest to me to say to CRM Development Team .... what could be the problem ?
btw.
In fact as so far we do not do any test for restoring, and I know must do this due to the security reason.
Question 2
We found that on Linux server all was properly backuped.
When we copy them with to SMB resource and get to Windows station then even 7zip is unpacking them correctly.
We found that the problem is with sending them to local FTP server.
We are sending this files by this BASH script:
#!/bin/sh
echo FTP file: $1 UPLOAD START
#pwd
ftp -n 192.168.1.205 <<END_SCRIPT
quote user userslogin
quote pass userspassw
cd /Backup
pwd
put $1
quit
quit
END_SCRIPT
exit 0

Could somebody say how to improve/fix our FTP bash script ?


texts: PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

texts:
Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows:

CREATE FUNCTION public.assert (
    in_assertion boolean,
    in_errormessage text
)
RETURNS boolean
IMMUTABLE
LANGUAGE plpgsql
SECURITY INVOKER
AS $function$
    BEGIN
        IF NOT in_assertion THEN
            RAISE EXCEPTION 'assertion failed: %', in_errormessage;
        END IF;

        RETURN in_assertion;
    END;
$function$
;

Upon testing I found that the exception is not thrown as I would expect. For example, with CREATE TABLE emptytable (somecolumn text); and

CREATE FUNCTION public.testassert_buggy (
    out somevalue text
)
LANGUAGE sql
SECURITY DEFINER
AS $function$
    WITH firstquery AS (
            SELECT * FROM emptytable
    ), nonemptycheck AS (
            SELECT assert(count(*) = 42, 'nonemptycheck failed') FROM firstquery
    ) SELECT * FROM firstquery;
$function$
;

I would expect a call like SELECT testassert_buggy(); to throw the exception, but instead the result is

 somevalue
-----------

(1 row)

(Note that firstquery actually returns 0 rows; the 1 row is due to this being a function with out parameters.)

With the following small change in the second-to-last line, the exception IS thrown.

CREATE FUNCTION public.testassert (
    out somevalue text
)
LANGUAGE sql
SECURITY DEFINER
AS $function$
    WITH firstquery AS (
            SELECT * FROM emptytable
    ), nonemptycheck AS (
            SELECT assert(count(*) = 42, 'nonemptycheck failed') FROM firstquery
    ) SELECT firstquery.* FROM nonemptycheck, firstquery;
$function$
;

If I rewrite the last query switching the table list (i.e. with FROM firstquery, nonemptycheck) there is again no exception. I'm puzzled. Is the query optimized in some way that ignores side-effects like exceptions? I tried to remove IMMUTABLE from the definition of assert, but that didn't make a difference.


texts: Accidentally executed reset master on the live server

texts:
Turns out I have called RESET MASTER; accidentally on the live server. The slaves stopped replication now because they can not find the expected bin log files on the master. Is there a way to continue replication without data corruption and without reimporting the whole dump on the slave?

Is it save to call CHANGE MASTER TO... with 'mysql-bin.000001' and position 1 on the slave now, because that is where they should continue, or do I risk data corruption in that case and it is generally preferred to reimport the whole dump and start over?


texts: After manually fail over resume button is disabled and query doesn't resumes

texts:
As there is no witess available, so when resumes the mirrored database by

ALTER DATABASE <database_name> SET PARTNER RESUME;

  does not resumes the mirroring. Resume button is also disabled. I don't get any error. What is the checkpoint?


texts: SQL Server Profiler shows a StartTime greater than EndTime

texts:
I'm using SQL Server Profiler to check some long run query. Somehow I see some entries in SQL Server profiler show StartTime is greater than the EndTime, and the Duration value is obviously overflow.

Why is this happening? Any advice? Thanks in advance.


texts: Upgrade a database from SQL Server 2000 to SQL Server 2014

texts:
I have database in SQL Server 2008 R2. I took a backup of this database and restored that on a SQL Server 2014 server.  However, some of the database queries are not looking compatible to the new version.

For example *= operator is throwing errors in SQL Server 2014.

I don't want to change each query where I get issue.  Is there any way to upgrade the older version of SQL Server backup to work with the newer version of database?


texts: Fast alter column NVARCHAR(4000) to NVARCHAR(260)

texts:
I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260).

Using

ALTER TABLE [table] ALTER COLUMN [col] NVARCHAR(260) NULL

results in SQL Server rewriting the entire table (and using 2x table size in log space), which is billions of rows, only to change nothing, isn't an option. Increasing the column width doesn't have this problem, but decreasing it does.

I have tried creating a constraint CHECK (DATALENGTH([col]) <= 520) or CHECK (LEN([col]) <= 260) and SQL Server still decides to re-write the entire table.

Is there any way to alter the column data type as a metadata-only operation? Without the expense of rewriting the entire table?  I'm using SQL Server 2017 (14.0.2027.2 and 14.0.3192.2).

Here is a sample DDL table to use to reproduce:

CREATE TABLE [table](
    id INT IDENTITY(1,1) NOT NULL,
    [col] NVARCHAR(4000) NULL,
    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

And then run the ALTER.


texts: Should I use the Infobright database or is there a better aggregation solution?

texts:
We are a MySQL front-end shop and have a system currently logging event data in a highly structured format. Think apache traffic log.

We need to be able to aggregate counts of this data (cube) vis a vi ad-hoc queries. We are currently sending the data to CouchDB. This is very fast, once a view has mapped completely, but our database size is only 31GB and CouchDB is taking FOREVER to map a new view ( 2+hours ). Retrieving JSON formatted data has worked well for us. But we dread creating new view documents. I'm thinking all Map-Reduce systems may have this problem.

We are evaluating Infobright as they claim to provide superior aggregation performance for ad-hoc queries and are a MySQL fork.

We have evaluated Mondrian and it's not going to work for us.

MySQL/InnoDB is too slow.

We will be around 500GB of log data by the end of the year.

Is Infobright the right solution or should we be also evaluating something else?


texts: Is there an automatic way for a secondary member to promote to primary member in a replica set?

texts:
Here is my setup:
Node 1 = 10.1.0.1 (Primary) - priority 1
Node 2 = 10.1.0.2 (Secondary) - priority 1
Node 3 = 10.1.0.3 (Secondary) - priority 0

In the event of Node 1 failure, Node 2 will be set to Primary of the mongo replica set.
Once Node 1 is already accessible, is there a way to automatically promote it to Primary and Node 2 will be set to Secondary again once Node 1 catches up on the replica set?


texts:
I'm really interested in knowing what the best way to structure relational database tables in the following scenario is.

Let's say the table is recording cryptocurrency prices. There are four important aspects:

1. An ID
2. The date and time
3. What cryptocurrency it is
4. The price

I'm thinking of two possible ways to structure the resulting table, either:

ID    Date                  Crypto        Price
1     2018-01-01 12:00:00   BTC           100000
2     2018-01-01 12:00:00   ETH           1234

Or

ID    Date                  BTC_Price    ETH_Price....
1     2018-01-01 12:00:00   1000000      1234

Is there a "best practice" to structure these kind of tables? One consideration that I have is I might want to add more types of currencies in this case. I can do this with the ALTER command, but in that context the first layout makes more logical sense to me.

What are your thoughts?


texts:
Good Morning,

I have installed MongoDB on an EC2 machine with 2CPU.

At the first time when I SSH Login I See this error message,

  System information disabled due to load higher than 2.0

However, My app is not receiving too many requests as before because of COVID it's receiving almost 30% of the normal traffic.

I tried to monitor the CPU usage and I got this htop :

and mongostat shows this

insert query update delete getmore command dirty  used flushes vsize   res qrw  arw net_in net_out conn                time
    *0     4     *0     *0       0     4|0  2.7% 93.3%       0 3.18G 2.20G 0|0 11|0  1.04k    121k   41 Mar 29 21:01:51.606
    *0    *0     *0     *0       0     4|0  2.7% 82.0%       0 3.18G 2.20G 0|0 11|0   285b   51.8k   41 Mar 29 21:01:52.737
    *0    *0     *0     *0       0     1|0  2.7% 86.0%       0 3.18G 2.20G 0|0 11|0   115b   42.3k   41 Mar 29 21:01:54.105
    *0    *0     *0     *0       0     6|0  2.7% 92.7%       0 3.18G 2.20G 0|0 11|0   462b    126k   41 Mar 29 21:01:54.566
    *0    *0     *0     *0       0     4|0  2.7% 86.5%       0 3.18G 2.20G 0|0 11|0   322b   58.5k   41 Mar 29 21:01:55.568
    *0     4     *0     *0       0     2|0  2.5% 88.3%       1 3.18G 2.20G 0|0 14|0   827b   54.6k   41 Mar 29 21:01:56.963
^C    *0

I see this data very very high and not realistic and I feel that there is something wrong with MongoDB, If you have any prior experiences with something like this please let me know


texts:
I have close to 2 TB Database PROD and the database backup size is too huge.

Everytime I take a backup it gets appended and when I check on it Microsoft sites says

  By appending another backup set to existing media, the previous
  contents of the media remain intact, and the new backup is written
  after the end of the last backup on the media.

What is the major use of this two options.

Append to Media Vs Overwrite an existing media


texts: MySQL : How do I partition based on Month of date

texts:
I have a table having id as primary key and date column.

I want to partition the data.. I felt month would be the best option so that I have 12 partitions.. How do I achieve that !

I am using mysql 5.6 on AWS RDS.

alter table report.track_pdp_visit partition by hash(start_date);

It gives me following error:

  Field 'start_date' is of a not allowed type for this type of
  partitioning


texts: SQL Server 2014 Log shipping - column error

texts:
I'm trying to set up log shipping between two instances of SQL Server 2014 Dev edition. Backup and copy jobs work flawless, but the restore one keep failing with the following error:

  An error occurred checking the log backup file '\server\file_20151113083854.trn' for secondary database
  'DB'.(Microsoft.SqlServer.Management.LogShipping)
  Column name or number of supplied values does not match table definition.

I'm not able to find out why this keep happening. The same issue occurs for any database I'm trying to set up shipping for. Manual restore of trn file works fine.

I'll be happy for any advice.


texts: Avoiding temporary tables while sorting by a column in a different table

texts:
So I have this MySQL InnoDB query here:

SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt
FROM stories s
LEFT JOIN votes v ON s.vid = v.vid
WHERE s.lv = 1
AND s.status = 1
GROUP BY s.vid
ORDER BY votecnt DESC

and profiling shows that over 93% of the required time to run this query is needed to copy to the results temporary table for further ordering by votecnt. What can be done to make it faster?

Explain output:

|  1 | SIMPLE      | s     | ref  | newest        | newest | 2       | const,const    | 19873 | Using where; Using temporary; Using filesort |

|  1 | SIMPLE      | v     | ref  | votes         | votes  | 4       | sikna_ci.s.vid |     1 | Using index                                  |


texts: Running Total of a count per day in SQLite

texts:
I usually write a couple of very simple queries on a redshift database which used in a marketing visualization tool.  And I repeat - VERY simple - I am a novice at this.

I am now faced with a new data source (SQLite) and I'm stuck.

I have a table with :

order_id
order_date

where there are several orders per day.  And I am trying to get a running total of the daily count of order ids.

For example the table would contain :

order_id      order_date
2541          2017-06-05
26777         2017-06-05
123           2017-06-06
8795          2017-06-07

And I'm trying to get to this result :

Day          RunningTotal
2017-06-05 : 2
2017-06-06 : 3  (the 2 of the previous day + 1 this day)
2017-06-07 : 4

In POSTGRESQL i would use

SELECT
order_date,
SUM (COUNT(order_id)) OVER (ORDER BY order_date rows between unbounded preceding and current row) as RunningTotal
FROM table
ORDER BY
order_date
GROUP BY
order_date

How do I do this in SQLite?

I've googled - and see many examples of either SUM or COUNT but seldom combined and never to achieve a running total of a count.


texts: Getting an aggregate (here: min) for a group of values

texts:
Example table:

ID   | value A | value B
----------------------
1    |    abc  |   3
1    |    def  |   5
1    |    ghi  |   1
2    |    cba  |   9
2    |    fed  |   4

I want the rows from within any 'ID-group' that has it's minimum within that group in value B.

Wanted result:

ID | value A | value B
----------------------
1  |    ghi  |   1
2  |    fed  |   4

Anything I tried with group by always fails because of that 'value B'-column and its varying values because I do not want to / cannot aggregate them.


texts: Confused about load balancing and horizontal scaling in postgresql

texts:
Please correct me if I am wrong but I guess handling more requests and load by adding more machines or balancing the load between multiple servers is horizontal scalling. So, if I add more servers, how do I distribute the database? Do I create one database to hold the user records with multiple servers? Or do I split the database too? What about database integrity? How to synchronize it? Or else what do I do? I am a newbie and really confused but eager to learn. I would like to use postgres for my project and would like to know some basic things before I start. I was thinking of using two small ec2 instances. But I got confused about the database. How do I go about creating the database. Do I need to go through sharding for this? What would be the best approach for horizontal scaling in accordance to postgres. I would really appreciate if you could explain it to me. Thank you!

Edit:

How do I load balance using multiple machines and manage the database?

I have an app where users can upload videos and it will be converted to mp4 using Elastic Transcoder. Users about 10k. So, how do I load balance using multiple machines and manage the database? What I want to do is load balancing for the performance. And I read in many post that adding more machine can leverage it. So I though of horizontal scaling. But since horizontal scaling is scary, how do I load balance and manage my database?


texts: Storing earth observation derived attributes for polygons

texts:
I want to generate a number of vegetation related indices (NDVI, NDTI, etc., min/max/avg/std (pixel values)) from earth observation data for a large number of polygons during the entire growing season, roughly between March and November each year. The figures are about as follows: I have about one million polygons. Every day I will get new EO data (Sentinel-1/Sentinel-2) for about 20% of them. For each of these polygons I generate 10-20 indices based on the EO data. This gives me appr. 2-4 million records, every day. That makes appr. 500 - 1000 million during just one growing season (I´ll need to store at least 5 seasons).

The infrastructure within which I have to operate is predetermined and will have to be something based on either Oracle(Locator) or PostGIS. Personally I´d prefer PostGIS since OpenSource allows for much more flexibility.

My initial idea is to create a PostGIS database, which is partitioned based on year value. I thought about creating one attribute table where I create a new row for each date and each interpreted property (polygon geometries+id are stored in a separate table). It would look something like this:

Since I have to do different interpretations depending on the geographical zone where the polygon lies, I also thought about creating a separate table for each zone. This will however make querying more difficult.

My questions are hence:

Does Oracle(Locator) or PostGIS as a base make sense or do I need to consider using an ESA DIAS/Google Earth Engine/AWS in order to be able to use more efficient storage solutions?
If Oracle/PostGIS indeed does make sense, what is your opinion on my planned table structure?

All comments are welcome.


texts: MYSQL: log the query executed on certain table

texts:
I am writing a trigger that suppose to fire on inserts for a table and log the exact query used to insert.

However, using below

SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID();

Always return in original_query variable this above query not the one firing the trigger and count in processlist is 1.

I am in MySQL 5.6.18.

This is the whole trigger deffinition

DROP TRIGGER IF EXISTS affiliate_revenue_i_log$$
CREATE TRIGGER affiliate_revenue_i_log BEFORE INSERT ON cds_affiliate_revenues
    FOR EACH ROW
    BEGIN
      DECLARE original_query VARCHAR(1024);
      SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=CONNECTION_ID();
      INSERT INTO cds_affiliate_revenues_log (action,id,added_date,original_query) VALUES('insert',NEW.id,NOW(),original_query);
    END$$


texts: Can I have one table be an "alias", or "symlink", to another?

texts:
I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables.

After a discussion with a colleague about the potential to save drive space, I'm wondering if mysql or postgresql has the ability to create on table as an "alias", or "symlink", of another.

I want the behaviour to be very similar to that of a soft file symlink; where reading from either the symlink itself or it's target will yield the same output, and writing to either will update the target.


texts: Is unique index combined with a "not null" constraint equivalent to a PK constraint?

texts:
I have these legacy table definitions (from here):

CREATE TABLE [dbo].[JobItems] (
   [ItemId]            UNIQUEIDENTIFIER NOT NULL,
   -- lots of other columns
   CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
ON [dbo].[JobItems]([ItemId] ASC);

which effectively yields two identical indexes. I want to get rid of one of them to reduce insertion time and free up disk space.

The table is stored in a production SQL Azure database, so I cannot rebuild the table and I cannot drop the clustered index (SQL Azure requires a clustered index for every table).

My requirement is I keep all the data integrity guarantees which were provided by the original definition and get rid of the non-clustered index. It looks like having a NOT NULL constraint and a unique index gets me the same guarantees as a PK constraint. So the PK constraint is redundant and I can drop it and the underlying index.

Can I just drop the PK constraint here without expecting anything to break?


texts: Should I use TINYINT for ID (PK) field in MySQL?

texts: Should I perform this type of optimisation if I know that a table can't have more records than 10?


texts: Connection reset to SQL Server when using SSH Tunnel

texts:
I am trying to connect to an MS SQL Server database through DBeaver via an SSH tunnel but am getting the following error message when I "Test Connection...":

  I/O Error: Connection reset
        java.net.SocketException : Connection reset

If I set the Keep-Alive interval to anything other than 0, the error becomes the following:

Can't initialize tunnel
  Cannot establish tunnel
    com.jcraft.jsch.JSchException: timeout in waiting for rekeying process.

Looking at the debug log, ~/.dbeaver4/.metadata/dbeaver-debug.log, I see that the connection was initially successful but later reset. I don't believe it is a problem in my connection settings configuration (my colleages with Mac's connected successfully using the same settings)

Here's an excerpt from the dbeaver-debug.log:

2017-06-26 15:01:35.354 - Instantiate SSH tunnel
2017-06-26 15:01:35.355 - Connect to tunnel host
2017-06-26 15:01:35.356 - SSH INFO: Connecting to jdms.dssg.io port 22
2017-06-26 15:01:35.480 - SSH INFO: Connection established
.
.
.
2017-06-26 15:01:35.953 - SSH INFO: Authentication succeeded (publickey).
2017-06-26 15:01:36.083 - Connection failed (mssql-hashhashhash-hashhashhash)
2017-06-26 15:01:36.085 - SSH INFO: Disconnecting from jdms.dssg.io port 22
2017-06-26 15:01:36.086 - SSH INFO: Caught an exception, leaving main loop due to Socket closed
2017-06-26 15:01:36.135 - org.jkiss.dbeaver.model.exec.DBCConnectException: I/O Error: Connection reset
org.jkiss.dbeaver.model.exec.DBCConnectException: I/O Error: Connection reset
.
.
.
Caused by: java.sql.SQLException: I/O Error: Connection reset
.
.
.
Caused by: java.net.SocketException: Connection reset

Could this be a Linux specific issue?

How else can I debug my problem?

I am using DBeaver 4.0.8 to connect to a MS SQL Server database using the jTDS driver, in Ubuntu 16.04.


texts:
I have a TVF that takes a userID parameter.

I have another table that contains a list of these userIDs.

I want to pass all these userID values into the TVF.

I have tried this code...

    FROM [dbo].[UserData](u.UserID) data
    JOIN dbo.Users u ON u.DetailID = data.DetailID

but get the error...

The multi-part identifier "u.UserID" could not be bound.

What am I missing?


texts: Oracle Application Server 10g Installation Process

texts: I downloaded Oracle Application Server 10g from this Oracle site. It has 4 .cpio files for the Linux installation. How do I unzip and install it on VMWare?


texts:
In production server there's a database per company, with each produced from a single template.

CREATE DATABASE company_x WITH TEMPLATE company_template;

Let's say I have updated the schema, is there a way to apply those changes to both existing databases and the template, or somehow synchronize between schema versions?


texts: I'd like to benchmark two db servers, one is running Ubuntu the other FreeBSD. Unfortunately we are unable to install SysBench on the FreeBSD OS. Could anyone recommend any alternatives? Or should I look into creating some intense queries based on the schema?


texts: Failed to start mongo.service on ubuntu 16.04 after update from 4.0 to 4.2

texts:
I'm trying to update my MongoDB installation from 3.6 to the latest version [4.4] on Ubuntu16.04 using apt-get. Going from 4.0 to 4.2 I faced a problem during the startup process after updating the binaries and mongo.log shows the exception below:

2020-08-07T14:58:26.205+0300 I  STORAGE  [initandlisten] exception in initAndListen: Location40415: BSON field 'MinValidDocument.oplogDeleteFromPoint' is an unknown field., terminating

I'm using WiredTiger and I checked the feature compatibility version and other prerequisites according to the installation and the compatibility docs and everything looks fine. I also tried mongod repair but with no luck.
I don't have any clue on what could be the source of this problem and I'd be grateful if someone can help.


texts: Disable Saving/Exporting Results of TSQL Query in SSMS?

texts:
I really don't want to go in to too much detail as to WHY I want to do this. Just know that it would be ideal for my use case.

Is there a way to disable a specific database role from using the Copy, Copy with Headers, Save Results As..., Print... functionality in the results window in SSMS?


texts: Mysql giving error while inserting 120 million rows "The total number of locks exceeds the lock table size"

texts:
I am inserting approximately 120 million rows in table. Its really slow and giving error "The total number of locks exceeds the lock table size". Can anyone please guide me the quickest process to insert 120 million rows or to remove that error. Insertion table is ISAM.

Here is the query

INSERT INTO brokergenius.market_data  (
  currentPrice,
  SN_ID,
  quantity,
  seatNumbers,
  ZN_ID,
  listingAttributeList,
  listingAttributeCategoryList,
  deliveryTypeList,
  ticketClass,
  dirtyTicketInd,
  splitOption,
  ticketSplit,
  splitVector,
  sellerOwnInd,
  faceValue,
  serviceFee,
  deliveryFee,
  totalCost,
  score,
  seller_price,
  Event_id,
  RN_ID,
  SSN_ID,
  LI_ID ,
  TS_ID
)
SELECT DISTINCT
  currentPrice,
  sectionId,
  quantity,
  seatNumbers,
  zoneId,
  listingAttributeList,
  listingAttributeCategoryList,
  deliveryTypeList,
  ticketClass,
  dirtyTicketInd,
  splitOption,
  ticketSplit,
  splitVector,
  sellerOwnInd,
  faceValue,
  serviceFee,
  deliveryFee,
  totalCost,
  score,
  seller_price,
  event_id,
  0,
  0,
  0,
  0
FROM
    market_snapshot.marketsnapshot;


texts: What's the name for a table that does not hold "mutable" data?

texts:
Let's say I have a users table, and each user has a different level of privilege. I have a privileges table with four rows that looks like this:

id | level
---+-----------
1  | viewer
2  | editor
3  | admin
4  | super-admin

In the business context, my users all have a privilege level, 1-4. The users table holds the corresponding user data, and it gets modified when users are modified, added, or dropped, but privileges never really changes, it is just a reference of the four established privilege levels.

Is there a name or term for this type of table?


texts: DIRTY_PAGE_POLL without activated Indirect Checkpoint

texts:
Since I encounter some performance issues on my SQL Server 2014 (12.0.2370.0 (X64)) I'm dealing with enormes amounts of different wait types.

One of the top 5 wait type is "DIRTY_PAGE_POLL".

I've read this wait type will only appear when I activate the "Indirect Checkpoint" feature, which is a part of the sql server since sqlserver 2012.

None of my databases has set the Target Recovery Time.

So, why is my SQL Server polling for dirty pages without the indirect checkpoint feature?


texts: sql server 2014 - Find gaps in sequence on 2 different fields group by third field

texts:
I need to detect the gaps between the sequences for each different seller considering that sequence is in 2 different rows considering only supplied range (avoiding numbers below the first record and numbers above the last record):

I have a table with orders where relevant fields are:

    SellerID | OrderID |   OID   |
    seller1  | 123456  |         |
    seller1  | 123457  |         |
    seller1  | 123458  | 123460  |
    seller1  | 123459  | 123460  |
    seller2  | 234567  |         | (first record)
    seller1  | 123455  |         |
    seller2  | 234568  | 234570  |
    seller2  | 234569  | 234570  |
    seller1  | 123463  |         |
    seller1  | 123466  |         |
    seller1  | 123453  |         | (first record)
    seller2  | 234572  |         | (last record)
    seller1  | 123470  |         | (last record)

I expect a result like this:

    seller 1 | 123454
    seller 1 | 123461
    seller 1 | 123462
    seller 1 | 123464
    seller 1 | 123465
    seller 1 | 123467
    seller 1 | 123468
    seller 1 | 123469
    seller 2 | 234571

it is quite easy also for me to find gaps in a single row and for single seller:
I used this query that I repeat for every seller:

Declare @SellerID nvarchar(50)='seller1'
SELECT s1.OrderID
FROM Orders s1
LEFT JOIN Orders s2
ON s1.OrderID = s2.OrderID -1
WHERE s2.OrderID IS NULL
and SellerID=@SellerID
order by s1.OrderID asc

but I do not know how to modify in way to check for the sequence in the 2 columns

moreover, is there a way to achieve this in a single query instead of repeating for each seller?


texts: Advice designing a stock management database

texts:
I am developing a webapp for my business, a mobile phone repair shop. The problem I have is I don't know how exactly to implement a way to manage the parts inventory.

Here what i have now:

{Parts(id, name)}
{Inventory(id, part_id, initial_qty, current_qty, initial_price, current_price)}
{Operations(id, name)}
{Inventory_Operation(id, inventory_id, operation_id, explanation, qty, price)}

I need to have a possibility to add inventory, sell inventory, add inventory to repairs, mark inventory as bad, return inventory.

I am developing using MySQL.

LE

As i mentioned before, the app will manage all of the shop repairs, i have the rest figured out, but this inventory management part of the database i cannot get a clear image.
When i buy parts from suppliers i need to add them to the inventory (they dont have serial numbers, i only need the quantity), then when i do a repair, and use a part, then the inventory for that part must be decreased, sometimes after a repair the client comes back because the part is not ok, and i need to offer warranty so i need to mark that part as defective in the inventory, other times i do the repair and the client declines to pay so i must return the part to the inventory. The other problem is, i can receive the same part with different price, or different supplier.

LE 2
I have made the whole app db diagram, the diagram is more or less accurate.


texts: How to enable the Federated/FederatedX storage engine on MariaDB 5.5?

texts:
I upgraded from MySQL 5.1 to MariaDB 5.3 and then to 5.5 on a Windows machine. Everything works well, except I can't enable the federated storage engine.
With MySQL 5.1 and MariaDB 5.3, I just added "federated" under [mysqld]. If I try to start MariaDB 5.5 with the same "federated" option, I get an error:

  C:\Program Files\MariaDB 5.5\bin\mysqld.exe: unknown option '--federated'

How can I enable the federated storage engine?


texts: JOIN and GROUP BY with additional columns

texts:
If I run the following query on my database I get the results pasted below:

SELECT t.id as ID,
j.text as Text,
l.location as Location,
FROM [text] t WITH (nolock)
INNER JOIN Text_Location tl WITH (nolock) on tl.textid = t.id
INNER JOIN Location l WITH (nolock) on tl.locationId = l.id

    ID       Text      Location
---------- ---------- ----------
     1         FC      London
     1         FC       Paris
     2         KG      Berlin
     3        TX1        Roma
     3        TX1      Vienna
     3        TX1      Dublin

How could I get the same results grouped by ID and having different columns for location? I would like to get something like this:

    ID       TEXT    LOCATION1   LOCATION2  LOCATION3
---------- ---------- ---------- ---------- ----------
     1         FC     London      Paris
     2         KG     Berlin
     3        TX1     Roma        Vienna     Dublin


texts: Why pad instead of trim when comparing strings?

texts:
The ANSI/SQL-92 standard states that strings have to be padded to the same length when compared:

     3) The comparison of two character strings is determined as fol-
        lows:

        a) If the length in characters of X is not equal to the length
          in characters of Y, then the shorter string is effectively
          replaced, for the purposes of comparison, with a copy of
          itself that has been extended to the length of the longer
          string by concatenation on the right of one or more pad char-
          acters, where the pad character is chosen based on CS. If
          CS has the NO PAD attribute, then the pad character is an
          implementation-dependent character different from any char-
          acter in the character set of X and Y that collates less
          than any string under CS. Otherwise, the pad character is a
          <space>.

        b) The result of the comparison of X and Y is given by the col-
          lating sequence CS.

        c) Depending on the collating sequence, two strings may com-
          pare as equal even if they are of different lengths or con-
          tain different sequences of characters. When the operations
          MAX, MIN, DISTINCT, references to a grouping column, and the
          UNION, EXCEPT, and INTERSECT operators refer to character
          strings, the specific value selected by these operations from
          a set of such equal values is implementation-dependent.

        Note: If the coercibility attribute of the comparison is
        Coercible, then the collating sequence used is the default de-
        fined for the character repertoire. See also other Syntax Rules
        in this Subclause, Subclause 10.4, "<character set specifi-
        cation>", and Subclause 11.28, "<character set definition>".

Other than the obvious "it isn't in the standard", what would be the reasons against trimming the ends of the strings instead of padding them to the same length?


texts: How does the Teradata REST API performance compare to other means of querying Teradata?

texts:
I'm planning on using the Teradata Python module, which can use either the Teradata REST API or ODBC to connect to Teradata. I'm wondering what the performance would be like for REST vs. ODBC connection methods for fairly large data pulls (> 1 million rows, > 1 GB of results).

My company doesn't currently have REST services enabled, so I can't directly test them to compare. Information on Teradata's site suggests that the use case for the REST API is more for direct access of Teradata by browsers or web applications, which implies to me that it may not be optimized for queries that return more data than a browser would be expected to handle. I also wonder if JSON overhead will make it less efficient than the ODBC data format for sending query results over the network.

Does anyone have experience with Teradata REST services performance or can point to any comparisons between REST and ODBC for Teradata?


texts: Setting a variable from a statement that starts with EXEC

texts:
I am trying to get the Server Name from a LinkedServer and assign it to a variable.

From this question (Get @@SERVERNAME from linked server), I found that either of these return the Server Name.

EXEC LinkedServer.[master].sys.sp_executesql N'SELECT @@VERSION;';

or

EXEC('SELECT @@VERSION;') AT LinkedServer;

Yet, when I try to assign that value to a parameter, SQL Server states it's incorrect:

SET @DbServerName = (EXEC TMR_DM_LS.[master].sys.sp_executesql N'SELECT @@SERVERNAME')

  Msg 156, Level 15, State 1, Line XXXX Incorrect syntax near the keyword 'EXEC'.
  Msg 102, Level 15, State 1, Line XXXX Incorrect syntax near ')'

What am I doing wrong?


texts: Optimising query: not sure why LOWER() index isn't used

texts:
I've got comments that have 1..N comment_notifications.
Each notification basically contains a username of the user notified.
Each notification can be resolved_at when the user is done with it.
This query is slower than I'd like – seeing times around 100–150 ms in production (hobby DB plan on Heroku) but would like to get it down to at least 50 ms or so.
EXPLAIN ANALYZE
  SELECT c0."id", c0."resolved_at", c0."username", c0."comment_id", c0."inserted_at", c0."updated_at",
  c1."id", c1."github_id", c1."commit_sha", c1."body", c1."commented_at", c1."commenter_username", c1."path", c1."position", c1."payload", c1."inserted_at", c1."updated_at"
  FROM "comment_notifications" AS c0
  INNER JOIN "comments" AS c1 ON c1."id" = c0."comment_id"
  WHERE
    (NOT (c0."resolved_at" IS NULL)) AND
    (LOWER(c1."commenter_username") = 'henrik')
  ORDER BY c0."resolved_at" DESC
  LIMIT 300

These are some of my indexes:
CREATE INDEX comment_notifications_resolved_at_index ON public.comment_notifications USING btree (resolved_at);

CREATE INDEX comment_notifications_lower_username_index ON public.comment_notifications USING btree (lower((username)::text));

This is the result of the EXPLAIN ANALYZE:
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=57.86..57.86 rows=5 width=298) (actual time=1.931..1.969 rows=300 loops=1)
   ->  Sort  (cost=57.86..57.86 rows=5 width=298) (actual time=1.930..1.949 rows=300 loops=1)
         Sort Key: c0.resolved_at DESC
         Sort Method: quicksort  Memory: 238kB
         ->  Hash Join  (cost=43.64..57.85 rows=5 width=298) (actual time=1.101..1.520 rows=459 loops=1)
               Hash Cond: (c0.comment_id = c1.id)
               ->  Seq Scan on comment_notifications c0  (cost=0.00..13.75 rows=866 width=48) (actual time=0.014..0.177 rows=871 loops=1)
                     Filter: (resolved_at IS NOT NULL)
                     Rows Removed by Filter: 46
               ->  Hash  (cost=43.62..43.62 rows=6 width=250) (actual time=1.051..1.051 rows=473 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 140kB
                     ->  Seq Scan on comments c1  (cost=0.00..43.62 rows=6 width=250) (actual time=0.058..0.805 rows=473 loops=1)
                           Filter: (lower((commenter_username)::text) = 'henrik'::text)
                           Rows Removed by Filter: 387
 Planning Time: 1.221 ms
 Execution Time: 2.056 ms
(16 rows)

My reading is that the username filter is the most expensive part by far. But I have an index on LOWER(username) as seen above. Any thoughts on why it's not being used?
Any suggestions of what I could improve, or further experiments I could make?


texts:
I have a table

  CREATE TABLE "AUTOMATION"."TESTRESULTS"
   (
    "BUILDNUMBER" VARCHAR2(30 BYTE),
    "TESTNAME" VARCHAR2(100 BYTE),
    "STATUS" VARCHAR2(100 BYTE)
   )

sample data would be

    buildnumber  testname   statusid
    1            testname1  pass
    1            testname2  fail
    1            testname3  warning
    2            testname1  fail
    2            testname2  fail
    2            testname3  fail
    3            testname1  fail
    3            testname2  pass
    3            testname3  fail

I would like to get a query to output:

               3         2           1
testname1      fail      fail        pass
testname2      pass      fail        fail
testname3      fail      fail        warning

What is the best approach to getting this output (buildnumbers as columns)?  I am using Oracle 11g and the test names are not known at the time of building the report.


texts: Is it safe to leave/exit the stored procedure without commit or rollback on a transaction?

texts:
Like the code below is it safe to do that?

create definer=`root`@`%` procedure `test`()
label: begin

declare exit handler for sqlexception rollback;
start transaction;

select condition from table where id = someid for update;

if condition = false then
  leave label;
end if;

// some query;
commit;
end$$


texts: SQL query to identify missing hinted indexes

texts:
We have a fairly large number of queries using index hints in our 11.2.0.4 database.  I recently uncovered some performance issues and found that some of the indexes referenced by the query hints were missing.  I'm concerned that there may be more cases of this, and I'm wondering if it's possible to construct a query to do the following:

Search the cursor cache for all queries with index hints
Extract the index name from each hint and search the data dictionary for the index.
Return back the name of each index that is missing.

The indexes all follow the same naming scheme: 6 letters, followed by 6 digits, followed by the string "$INDXn", where n is a digit 0-9.

Any suggestions how to approach this?


texts: Oracle Temporary table Space reasons for excessive use and sizing

texts: In Oracle how to find which operations/queries are causing excessive use of Temporary table Space and how to estimate its size?


texts: sql server 2012 Express bulk-insert fialed due to message Operating system error code 5(Access is denied.)

texts:
I used my personal laptop computer (Samsung RV520)
 OS: Windows 7 home premium (x64 bit)
 SQL Server 2012 Express version 11

I encountered an error when execut the code below.

error message:
  Msg 4861, Level 16, State 1, Line 47
Cannot bulk load because the file "bulk-campaign" could not be opened. Operating system error code 5(Access is denied.).
Msg 4861, Level 16, State 1, Line 47
Cannot bulk load because the file "bulk-campaign.Error.Txt" could not be opened. Operating system error code 5(Access is denied.).

 bulk insert project1.dbo.campaign
 from 'C:\Users\admin121\sql-server-2014-laotu\sql2012_class2_20141023\Project1_Bulk_Insert\textfiles\campaign.txt'
 with
 (
   firstrow = 2,
   maxerrors = 100,
   errorfile = 'bulk-campaign'
   );

this forum post offered suggestion, but I can't find the correct SQL Server name to give permission on the folder.

Please advise how can I find and add correct SQL Server Name to the the file folder.

I did some research online, it suggested to grant server role bulkadmin to the current login will resolve the issue

alter server role [bulkadmin] add member [boglenetwork\admin121];

After granting the role,I still receive the same error message.

my database name is project1, user associated with the database is dbo.
when i try to grant dbo additional user role e.g. acessadmin, system prompted me MS SQL server error: 15405

image1
image1
image1
image1
image1


texts: Decrease Data IO operation on query to lower the DTU cost on Azure

texts:
We have a FunctionApp in Azure that triggers on a timer and does some DB operations every 5 minutes. These operation are taking a very long time to complete. When I run the same query on my computer with the same data in the DB the query executes in about 2 seconds. On Azure it took almost almost 100 seconds.
We saw in the metrics on Azure that the Data IO limit hit the roof with 100% DTU. So we increased the DTU limit and the query now runs in about 48 seconds, but still hits the DTU limit for Data IO.
It's easy to say, just increase the DTU limit, but the customer isn't soo keen on paying more every month.
Soo, Is there something I can do with the below query to limit the amount of Data IO it does? Can I do it in another way? Split the query or something?
WITH CombinedTables AS
(SELECT SourceTable,a, b, c, d, e, f, g,
ROW_NUMBER() OVER (PARTITION BY a, b, c, d, e, f, g ORDER BY a, b, c, d, e, f, g) Row_Nbr
FROM (SELECT '2' AS SourceTable, a, b, c, d, e, f, g FROM [abc].[ExternalView]
             UNION ALL
             SELECT '1' AS SourceTable, a, b, c, d, e, f, g FROM [LocalTable] WITH (NOLOCK)
) AS CombinedTables GROUP BY a, b, c, d, e, f, g, SourceTable
) SELECT DISTINCT * FROM CombinedTables WHERE CombinedTables.Row_Nbr < 2 AND CombinedTables.SourceTable = '2'

Or is the solution to limit the number of rows we operate with, so that the amount of row that needs to be processed will be lower? there are about 84k rows in the table now, and it increases some every month.
edit: Added execution plan https://prnt.sc/uuys04


texts: Why does Management Studio add the second alter table "CHECK CONSTRAINT" for a foreign key constraint

texts:
ALTER TABLE [dbo].[REMINDER_EMAIL]  WITH CHECK ADD  CONSTRAINT
  [FK__REMINDER_EMAIL__FACILITY_ID] FOREIGN KEY ( [FACILITY_ID] )
  REFERENCES [dbo].[FACILITY] ( [FACILITY_ID] )

ALTER TABLE [dbo].[REMINDER_EMAIL] CHECK CONSTRAINT [FK__REMINDER_EMAIL__FACILITY_ID]


texts: Why doesn't SQL Server use my index in this SELECT ... WHERE?

texts:
I've created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I'm filtering with a WHERE clause ([target_user_id]):
CREATE TABLE [dbo].[MP_Notification_Audit] (
    [id]                    BIGINT             IDENTITY (1, 1) NOT NULL,
    [type]                  INT                NOT NULL,
    [source_user_id]        BIGINT             NOT NULL,
    [target_user_id]        BIGINT             NOT NULL,
    [discussion_id]         BIGINT             NULL,
    [discussion_comment_id] BIGINT             NULL,
    [discussion_media_id]   BIGINT             NULL,
    [patient_id]            BIGINT             NULL,
    [task_id]               BIGINT             NULL,
    [date_created]          DATETIMEOFFSET (7) CONSTRAINT [DF_MP_Notification_Audit_date_created] DEFAULT (sysdatetimeoffset()) NOT NULL,
    [clicked]               BIT                NULL,
    [date_clicked]          DATETIMEOFFSET (7) NULL,
    [title]                 NVARCHAR (MAX)     NULL,
    [body]                  NVARCHAR (MAX)     NULL,
    CONSTRAINT [PK_MP_Notification_Audit1] PRIMARY KEY NONCLUSTERED ([id] ASC)
);

[...]

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUser] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

This table has about 11,700 rows of data in, so it should be enough to trigger the use of indexes with WHERE clauses.  If I SELECT just the column I'm filtering on, only the index is used and 133 matching rows are read - an index-only scan:
SELECT [target_user_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

However, as soon as I add an extra column to the SELECT, the index is ignored and a table scan with a predicate is done to attain the result, reading over 11,700 rows:
SELECT [target_user_id], [patient_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

Why is it ignoring my index in this second query?  I'd have thought it would still be more efficient to use the index to get down to 133 RIDs, then query the extra row data required, than to go through every row of the table with a predicate?  I know I can add columns to the index with INCLUDE with the extra fields needed in the SELECT clause to make it use the index again, but I'm interested as to why it doesn't still use the index in this case.


texts: Truncating table without using Alter Table permission

texts:
So my DBA doesn't want developers to give Alter table permission. Now one of the procedures that we created requires to truncate tables (since apparently delete only removes records, but truncate frees up space too).

Now in order to use truncate minimum permission required is "Alter table". (Source)

But there was also a solution described to "create a stored procedure with execute as owner to only one table or a stored procedure to any table" (given by user3854427).

My question is in response to the code he provided. Embedding the code for easy reference:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Yimy Orley Asprilla
-- Create date: Julio 16 de 2014
-- Description: Función para hacer TRUNCATE a una tabla.
-- =============================================
ALTER PROCEDURE [dbo].[spTruncate]
    @nameTable varchar(60)

WITH EXECUTE AS OWNER
AS

SET NOCOUNT OFF;

DECLARE @QUERY NVARCHAR(200);

SET @QUERY = N'TRUNCATE TABLE ' + @nameTable + ';'

EXECUTE sp_executesql @QUERY;

My questions are:

Is the above code creating a stored procedure "on behalf of" OWNER?
Who has to create this procedure (DBA?) since I definitely can't create it.
Once the DBA create this procedure any member can use it to truncate the table even if they don't have the Alter table permission?

Please help.


texts:
I'm not necessary sure if the "wizard" is the correct term for it, but essentially what I have is a form that have a specific set of questions that a user must answer in order to determine which option is the best solution for them.

I have the following tables:

Question
Answer
QuestionAnswer
WizardOption

What I'm having an issue with is creating the relationship between QuestionAnswer and WizardOption. (I'm not sure if I'm just over thinking it; I'm new to designing a database structure).

QuestionAnswer Table

QuestionID INT
AnswerID INT

WizardOption Table

WizardOptionID INT
WizardOptionText VARCHAR(25)

To connect the two, is it ideal to create another table, like so:

WizardOptionByAnswers Table

WizardOptionID INT
Answers VARCHAR(100)

And then in the Answers column I would store the correct order of answers needed to match with the WizardOptionID.

+----------------------------------+
| WizardOptionID | Answers         |
+----------------------------------+
| Option1        | 1,2,1,2,3,1,1,1 |
| Option2        | 2,1,2,2,1,2,1,1 |
| Option3        | 2,2,2,1,3,1,1,2 |
+----------------------------------+

Not sure if that is the correct way to do this. Please let me know if more information is needed.


texts: restoring to new db putting old db into restore

texts:
The image below says it all.  I need to get some data out of a back-up that's two weeks old, so I restored to a new db called ETLFix.  However, when I started the restore my production DB, ETL went into restore as well.

I suspect this because I restored ETLFix from a two week old .bak and an notification went off saying that the back-up chain is broken for ETL.  Another way to say it is, ELT thinks it's in restore because it learned an old .bak was read.

How to I get my ETL production db out of restore, and keep this from happening in the future?


texts: How to handle divide by zero in GENERATED columns in MySQL

texts:
I am using GENERATED AS columns in MySQL 5.7 as follows:
ALTER TABLE report_user
MODIFY avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/(trans_out_count))) STORED;

but sometimes the trans_out_count could be 0, then the GENERATED AS column will give a divide by zero error. So, what I did was:
ALTER TABLE report_user
MODIFY avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/(trans_out_count + 0.00001))) STORED;

but can be a little problematic - for example for 100,000/1.00001, the right value is 100,000, but the actual value it not correct 9,999.90. What should I do to handle this problem? This is the table DML:
CREATE TABLE `report_user` (
  `trans_into_count` int(11) NOT NULL DEFAULT '0' COMMENT '转入次数',
  `trans_out_count` int(11) NOT NULL DEFAULT '0' COMMENT '转出次数',
  `trans_into_amount` bigint(11) NOT NULL DEFAULT '0' COMMENT '转入金额',
  `trans_out_amount` bigint(11) NOT NULL DEFAULT '0' COMMENT '转出金额',
  `avg_trans_into_amount` bigint(20) GENERATED ALWAYS AS (round((`trans_into_amount` / (`trans_into_count` + 0.00001)),0)) STORED,
  `avg_trans_out_amount` bigint(20) GENERATED ALWAYS AS (round((`trans_out_amount` / (`trans_out_count` + 0.00001)),0)) STORED,
  `user_name` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '用户名',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID'
) ENGINE=InnoDB AUTO_INCREMENT=36149 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='提转统计';


texts: install Galera but mariadb existing on system

texts:
I try to install galera on ubuntu 14.04 system. It has already got mariadb and it's working. How can I install it?

my command :

sudo apt-get install -y rsync galera-3 mariadb-galera-server

it gives error

Reading package lists... Done
Building dependency tree
Reading state information... Done
rsync is already the newest version.
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 mariadb-galera-server : Depends: mariadb-galera-server-5.5 (= 5.5.46+maria-1~trusty) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.


texts: Can I restore my SQL Server database to a point before the last full backup with my current backup strategy?

texts:
If I have this backup strategy(weekly full backup and 1 hour log backup), can I restore the database to the green highlighted period of time? Btw, does log backup 2 includes all log records that have their lsn great than the last_lsn of log backup 1?

Update
I did some more test and I think I found the answer. According to the image in the backup schedule, I did an initial full backup, some log backups, then log backup 1, a full backup, log backup 2. I have modifications in between. Use RESTORE HEADERONLY to check log backup 1, full backup and log backup 2, below is what I got. As you can see, log backup 2 catches all log records from the last one in log backup 1. If I want to restore to a point in the green highlighted period, I need to use log backup 2, not the full back.


texts: Extended Events Login audit - Reduce login noise

texts:
We are looking for the optimal sql login audit solution, with usable filtering.
Thus far we have looked into Server Audits and profiler but have concluded that extended events
look most promising, unless anyone can show us otherwise.
Question is, is there any good way to filter unnecessary login events? e.g. when I connect using SSMS, immediatly about 10 login events are added and continue to pile up if I leave SSMS connected to a instance. Why is that?
This is our current implementation:
USE master;
GO
-- Create the Event Session
IF EXISTS
    (
        SELECT
                  *
            FROM  sys.server_event_sessions
            WHERE name = 'ServerLoginAudit'
    )
    DROP EVENT SESSION ServerLoginAudit ON SERVER;
GO
EXECUTE xp_create_subdir 'D:\audits\Sessions';
GO
CREATE EVENT SESSION ServerLoginAudit
    ON SERVER
    ADD EVENT sqlserver.login
        (SET
             collect_database_name = (1)
           , collect_options_text = (1)
         ACTION
             (
                 sqlserver.sql_text
               , sqlserver.nt_username
               , sqlserver.server_principal_name
               , sqlserver.client_hostname
               , package0.collect_system_time
               , package0.event_sequence
               , sqlserver.database_id
               , sqlserver.database_name
               , sqlserver.username
               , sqlserver.session_nt_username
               , sqlserver.client_app_name
               , sqlserver.session_id
               , sqlserver.context_info
               , sqlserver.client_connection_id
             )
        )

    ADD TARGET package0.event_file
        (SET filename = N'D:\audits\Sessions\ServerLoginAudit.xel', max_file_size = (20), max_rollover_files = (2))
    WITH
        (
            STARTUP_STATE = OFF
          , TRACK_CAUSALITY = ON
        );
/* start the session */
ALTER EVENT SESSION ServerLoginAudit ON SERVER STATE = START;
GO


texts:
I have a view which is doing summation of several columns:

Select cola, colb, cola + colb + colc from table tbl_a

Can I know what are the indexes I should create for the view to improve its performances?


texts:
I have a JSONB object containing lots of elements and nested objects, and some of the elements are to be encrypted, e.g.:
jsonb_obj = '{"obj1" : {"obj2": {"firstname":"john", "surname":"smith": "bankNo":"Q|%^$ME%T^$"SS"}}}

So only the element bankNo is encrypted.
When traversing the entire object I can encrypt using
select jsonb_pretty(jsonb_object_agg(key, pgp_sym_encrypt( CAST(value as text), 'the key'))) as data"

to ensure that the value only gets encrypted in the object.  But when decrypting I keep getting the error "wrong key or corrupt data".  My question is can individual elements of a JSONB object be encrypted separately and if so how should I do it? Or must encryption take place at a column level?


texts: Creating a foreign server in Postgres only if not exists

texts:
As far as I know in order to create a foreign server (for a FDW) you need to do the following:

CREATE SERVER my_wrapper FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    dbname 'wrapperDB',
    host '127.0.0.1',
    port '5432'
);")

But because I create a migration script for an existing database, I want to be able to check whether this server exists with the same name and then create in a similar approach that is used upon tables:

CREATE TABLE IF NOT EXISTS mytable ...

Or in stored procedures:

CREATE OR REPLACE FUNCTION public.myfunction(text)....

Do you have any idea how to do that?


texts: Postgres Restore Not Restoring Bytea Data

texts:
This is a very urgent situation.

We have a postgres 9.4 database installed on a CentOs machine.

We are trying to take a backup from a Heroku Postgres database and restore it to the database on CentOs machine.

The commands we are using:
pg_dump -Fc -h ec2-99-99-99-99.compute-1.amazonaws.com -p 5762 -U xyz -d dbname > file.dump

The dump created by pg_dump seems to be missing data from columns that have the type of binary (these columns contain images in binary format).

When we restore this backup to the database on CentOS using following command, all the images from the image i.e. binary type columns are missing:

pg_restore -d onlinedb ~/file.dump

We have to go live in the morning and are completely bummed by this unexpected issue.

Output of \d attachments (attachments is the problem table) command:

             Table "public.attachments"
      Column      |          Type          | Modifiers
------------------+------------------------+-----------
 id               | integer                | not null
 configuration_id | integer                |
 style            | character varying(255) |
 file_contents    | bytea                  |

More information:
The heroku postgres database from which we are creating backup is PostgreSQL 9.2.6.
The one we are restoring to is PostgreSQL 9.4.


texts: Load csv into MySQL database programmatically

texts:
I know about LOAD DATA INFILE command -- however, this requires that we already have the table with appropriate columns in place. When I have csv with hundreds of columns, it is not feasible to type all the columns definitions in by hand.

There's also a website that allows uploading the .csv file and it will produce the appropriate MySQL CREATE TABLE and INSERT codes. However, 1) this is not easily reproducible, and 2) there are quirks such as BIT-type data has to be modified so that it's enter as b'1' with the b.

Is there any way to import csv into SQL programmatically, for example using R, Python, Java, etc.? I work in social sciences, and very frequently we need to download some csv files that need to be imported.


texts:
We are loading big files full of JSON, one JSON object per line, each LF-delmited line being loaded as a row.
If any row fails to match the JSON structure, the entire file is rejected. For example:
org.codehaus.jackson.JsonParseException: Current token (VALUE_STRING) not numeric, can not use numeric value accessors

That happens if we have something like {"questionID": "1240", instead of {"questionID": 1240,.
Is there any way to configure Hive or the serde to report and drop bad JSON rows, but keep the good ones? Or some syntax that I could use that reads the JSON into a staging table as a string, and then selects the good ones into one table and the bad ones into another?
Table is created like this:
ROW FORMAT SERDE
  'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

There's another serde (openx) that has a property ignore.malformed.json but I don't want to ignore them, I want to report on them.


texts: Is there a way to alias columns for reference in subqueries?

texts:
I have a SQL statement in PostgreSQL that takes the following form:

SELECT * FROM db WHERE (b + 1) IN (SELECT b FROM db WHERE a = 7)

This query returns each record whose b value, incremented by 1, is in the set of all b values of the records matching a = 7. However, I'm looking for a way to write this in the form of an EXISTS statement, since that may be more optimal. Something of the form

SELECT * FROM db WHERE EXISTS (SELECT 1 FROM db WHERE a = 7 and b = b* + 1)

where b* somehow references the b attribute not inside the subquery, but outside it. Is there a way to write the first query without IN and with EXISTS?


texts: If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am calculating reads by using the user_seeks + user_scans + user_lookups columns and the writes by using the  user_updates column of this DMV. Thanks


texts: PostgreSQL "upsert" tuning with unique constraint

texts:
I have the following table (PostgreSQL 9.6 but could upgrade):

# \d annotation_annotation
                                       Table "public.annotation_annotation"
      Column       |          Type           |                             Modifiers
-------------------+-------------------------+--------------------------------------------------------------------
 id                | bigint                  | not null default nextval('annotation_annotation_id_seq'::regclass)
 release           | character varying(32)   | not null
 chromosome        | character varying(32)   | not null
 position          | integer                 | not null
 reference         | character varying(512)  | not null
 alternative       | character varying(512)  | not null
 database          | character varying(8)    |
 effect            | character varying(64)[] | not null
 gene_id           | character varying(64)   |
 transcript_id     | character varying(64)   |
 transcript_coding | boolean                 |
 hgvs_c            | character varying(512)  |
 hgvs_p            | character varying(512)  |
Indexes:
    "annotation_annotation_pkey" PRIMARY KEY, btree (id)
    "annotation_annotation_release_chromosome_posit_d102b713_uniq" UNIQUE CONSTRAINT, btree (release, chromosome, "position", reference, alternative, transcript_id)
    "annotation__release_3f254e_idx" btree (release, chromosome, "position", reference, alternative, gene_id)

I'm running bulk insertions with ON CONFLICT IGNORE of tens of millions of files into the table. I'm expecting a high number of duplicates (>90%). The table is append-only. Using pg_activity, I'm seeing that PostgreSQL is spending more time reading than writing (as I would expect if the number of duplicates is high).

What would be a good way of tuning "upsert" performance here:

Would partitioning with PostgreSQL 11 help? I could provide an additional integer "chromosome_no" column that could be used for hashed partitioning.
Is it possible to use bloom indices to speedup "upsert" performance with unique constraints? (EDIT I realize that bloom filters will not help because they err into the wrong direction)


texts: join vs virtual table in query performance

texts:
Suppose I have two tables linked by a foreign-key

example DDL's

CREATE TABLE raw_collection (
    info_datetime DATETIME NOT NULL,
    asset_id INTEGER NOT NULL,
    price FLOAT,
    PRIMARY KEY (info_datetime, asset_id),
    FOREIGN KEY(asset_id) REFERENCES assets (id)
);

CREATE TABLE assets (
    id INTEGER NOT NULL,
    asset_symbol VARCHAR,
    PRIMARY KEY (id)
);

when filtering by asset_symbol on the raw_collection tables, what (and why) would be a faster solution. using a join or creating an in memory table from the asset_symbol table and filtering raw_collection according to it?

the queries

-- query 1
with needed_assets as (
  select id
  from assets
  where asset_symbol in ('a', 'b'))
select *
from raw_collection
where asset_id in (select id from needed_assets) ;
-- where asset_id in (table needed_assets) ;  -- Postgres only syntax
-- where asset_id in needed_assets ;          -- SQLite only syntax

-- query 2
select r.*
from raw_collection r join assets a on r.asset_id = a.id
where a.asset_symbol in  ('a', 'b') ;

I am either using PostgreSQL 11 or SQLite 3.28.0


texts: Is it possible to run a stored procedure automatically on a new database when it is attached?

texts:
Replication is causing issues on our daily snapshots.
What we would like to do is remove replication automatically on a database when it is attached to the SQL server.
Is this possible?


texts: Can't cast a stored procedure parameter?

texts:
I have several stored procedures that take a date/time input, one of which takes a date only in the form of a nvarchar because it utilizes dynamic SQL. When I try to cast the variable going into the stored procedure, I get an error. This doesn't make much sense to me.

I know I can work around it by creating a second set of nvarchar variables or casting within the stored procedure, but I'd like some insight on why I can't just cast the variable in-line.

Example.  *TOP is a Select statement telling me my NVARCHAR DATE kicks out just fine.

MID is where SSMS is throwing the error, BOTTOM is where SSMS thinks it's just fine.*


texts: How can I reset Aborted_connects in MySQL?

texts:
A recent issue in our network caused a large number of aborted connects, which is now being displayed on the health dashboard of our web application as a potential issue.

Aside from restarting MySQL, is there a way to reset Aborted_connects to 0? I have tried flush hosts and flush status, but they do not reset Aborted_connects

EDIT : MySQL 5.7.24


texts: Overwrote database with old mysql dump. How can I restore it with a more recent backup

texts: Have a server with multiple databases.  Imported wrong .sql from over the top of existing database.  I Have a rsync copy of the up to date files for that database.  What do I need to do to just restore that database without impacting the others?


texts: What are options for super volatile tables?

texts:
I've inherited a very volatile table which is a map of who holds what resource in the system.  At any given moment, there could be a dozen inserts/deletes/reads going against that table.  However, there are never any more than 30-40 rows in the system.

The system was written in the SQL 2000 era and the access to the table is serialized via sp_getapplock/sp_releaseapplock system sprocs, so that only 1 request is modifying the table. In addition, the INSERT & DELETE statements execute WITH (TABLOCK). Reading the notes from a decade ago, it states that without these restrictions, the system would experience non-stop deadlocks.

I've ported the database to SQL Server 2016 Enterprise Edition.  Now that the throughput of the system has increased 10 fold, this table is easily the biggest bottleneck.

What are my options for a table as volatile as this with SQL 2016?

I am looking for fast (hopefully concurrent) access and no deadlocks.


texts: Can indexes really decrease performances in a low volume writes DB?

texts:
I'm trying to do a bit of a clean up/review of the database I'm currently working on (which I didn't design, by the way). The DB is 'big' in term of objects (hundreds of tables, hundreds of SP, functions, a thousand triggers, looooots of columns, etc.) and it's designed, well...not that nicely. Not nicely at all. So, while I'm going through the long backlog of performances related issues that the customers have uselessly complained for in the last three years, I'd like to do some "easy fix" too.

Now, I've found an interesting number of things like 40% of the tables having no primary keys, 30% of tables having no indexes, and a lot of other really funny things (could go on for hours), but this question is focused on indexes.

I've been working on DBs for a while and I know that indexes can (and do) slow down the system during writes, no big surprise here. And yes, indexes still have to be stored somewhere so they increase the DB size, but...is there anything else aside from that?

The DB is ridiculously small, and the number of writes per seconds could probably be counted in writes per minutes without the need to go double digits, so I'm not concerned about those points.

I've written a bit of TSQL to find tables without indexes and 'lot' of records (like a table with four million records and not a single index or a primary key /facepalm), plus I've used sys.dm_db_missing_index_* to find what indexes the DBE think are needed.

So, bottom line: should I go on and blindly add indexes, or do I risk to kill system performances for some reason I'm not foreseeing?


texts: Parent/Child Relationship Table Design - What's The Best Practice?

texts:
I have a single table for storing 'Tasks'. A task can be a parent and/or a child.
I use the 'ParentID' as the FK referencing the PK on the same table. It is NULLABLE, so if it is NULL it does not have a parent task.

Example is the screenshot below...

It has been argued in my team, that it would be much better (for normalization/best practices) to create a separate table to store the ParentIDs and so avoid having NULLS in the table and lead to better normalization design.

Would this be a better option? Or will it be more difficult with querying and cause performance issues?

We just want to get the design right from the beginning rather than finding issues later.

SQL-DDL code for the existing table:

CREATE TABLE [Tasks].[TaskDetail]
(
    [TaskDetailID] [int] IDENTITY(1,1) NOT NULL,
    [TaskName] [varchar](50) NOT NULL,
    [TaskDescription] [varchar](250) NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_TaskDetail_IsActive] DEFAULT ((1)),
    [ParentID] [int] NULL,
    CONSTRAINT [PK_TaskDetail_TaskDetailID] PRIMARY KEY CLUSTERED ([TaskDetailID] ASC),
    CONSTRAINT [FK_TaskDetail_ParentID] FOREIGN KEY([ParentID]) REFERENCES [Tasks].[TaskDetail]([TaskDetailID])
);


texts: Difference between count(*) and count(1) in mysql?

texts:
In MySql we can count the total number of records by using count(1) or count(*).

Is there any technical difference between them?


texts:
We have an SQL Server Agent job that runs a maintenance plan to reindex all of the database on the server. Recently this has failed but the job history is not providing enough information to diagnose the problem.

In the job history it states The job failed. The job was invoked by user foo\bar. The last step to run was step 1 (Rebuild Index).

In the details window are multiple messages in the following form:

Executing query "ALTER INDEX [something] ON [a...".: 0% complete  End Progress  Progress: 2015-03-15 22:51:23.67     Source: Rebuild Index Task

The SQL statement that is running is truncated, and I would assume that the output of the statement is also truncated, preventing me being able to identify which particular statement has failed and why. Is there any way to extract the full text of these messages?


texts: MariaDB subqueries to same table and column resulting in several columns

texts:
I have a table and want to pick monthly minute data to compare column wize in 10.3.13-MariaDB
Tested and tested for hours and hours different approaches without success, one example is below. Some tests does not complain syntactically but takes forever, and some tests complains about column names not recognized. All subqueries if tested separately return the same number of records, each in one column.
SELECT RD, OT1, OT2, OT3 FROM
(SELECT rdate from  OO where month(rdate) = 7 and year(rdate) = 2006) AS RD,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2006) AS OT1,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2007) AS OT2,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2008) AS OT3;`

The result should be something like:
RD                   OT1    OT2    OT3
2006-07-01 00:00:00  1.2345 2.1234 1.543
...                  ...    ...    ...
2006-07-31 23:59:00  3.456  3.234  2.234

And, no I dont want to use UNION because then they will still follow one after the other...
Any thoughts?!


texts: What caused the database [.mdf file] to grow suddenly

texts:
I am trying to find the cause which have bloated the mdf of a database from 20 GB to 100 GB.

So far i tried checking the autogrow events to find time, but could not find none using standard reports and even default trace files.

We don't have 3rd party monitoring tools to confirm if any maintenance job like rebuild would have done or any other process.

How can i find what caused the growth on this mdf?


texts: Creating audit triggers in a generic way for mysql tables

texts:
We have many many tables in our operational database.
We would like to create an audit table for each one of them.
We would like to create a trigger for each of our tables that will simply insert an audit entry for the relevant audit table.

i.e. for table#67 the trigger will simply insert into auditTable#67 the data on each update\insert\delete. bear in mind the naming convention - each table X will have an auditX table.

As you can see our triggers are really simple and generic.

Our issue is how exactly to create them.

We can create lots of "create-trigger" statements but this is too time consuming.
we managed to create a simple java based code snippet that creates this triggers in less than 30 lines of code. problem is that we don't want to use java as part of our deployment process.

Do we have any viable option other then creating the straightforward trigger scripts?


texts: How to move all non-unique and non-clustered indexes to a different filegroup?

texts:
I want to move all indexes to a separate hard drive.  To that effect, I created a new filegroup, then added a new file to it (e.g. alter database foo add file(...) to FileGroup).

Now I want to move all the indexes to this new filegroup.  Turns out, I can't use ALTER INDEX  to move an index between filegroups.  And I can't move PK or Unique indexes either, lest I want to move the table itself (which I don't), so those are out.  So, at least, I'd like to move the rest of the indexes.

I am struggling to find a method to move all eligible indexes from one filegroup to another.  Is there a reasonable automated way to do this?


texts: UPDATE a table with just ~2000 rows is slow, would LOCK help?

texts:
RDS Performance Insights tell me UPDATE foobar SET expire=0 is slow , foobar has about 2000 rows and there's an index on expire, there needs to be. Now, would it be faster if I did a LOCK TABLES foobar WRITE before the UPDATE? I feel like UPDATE is locking piecemeal which causes the problem.
Alternatively, as a terrible hack, I could imagine doing a CREATE TABLE LIKE ; INSERT - SELECT ; RENAME TABLE series of commands which should be lightning quick...


texts: Unpacking JSON into 14 columns without crosstab() and tablefunc?


texts: Lost connection to MySQL server during query

texts:
Here is a part of my query:

SELECT *
INTO   OUTFILE 'Target_File_Name' fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ''
FROM   (
              SELECT 'Start time',
                     'Calling no',
                     'Called no',
                     'Calltype',
                     'Cust. name',
                     'Total Dur.',
                     'Call stat'
              UNION
                    (
                           SELECT cdr_startdatetime,
                                  cdr_a_number ,(
                                  CASE cdr_b_number
                                         WHEN 0 THEN ''
                                         ELSE (
                                                CASE cdr_calltypewhen 1 THEN concat('011',cdr_b_number)
                                                       ELSE concat('1',cdr_b_number)
                                                end)
                                  end ),
                                  cdr_calltype_name ,
                                  cdr_customername ,(
                                  CASE
                                         WHEN cdr_totalduration>0 THEN round(cdr_totalduration/60,1)
                                         ELSE 0.00
                                  end),
                                  cdr_callstat
                           FROM   tlb_cdrdetails_4 FORCE INDEX(index_callstatus)
                           WHERE  cdr_startdatetime >= cast('2012-04-01 00:00' AS    datetime)
                           AND    cdr_startdatetime <= cast('2012-04-30 23:59:59' AS datetime)
                           AND    cdr_callstat IN (42,
                                                   52)
                           AND    cdr_customerid IN (123,1000,1001)
                           AND    cdr_carrierid  IN (114,1039,115))) tbl

It fetches 8 million records. It's causing a "lost connection to MySQL during query" error. Is the network speed an issue while downloading huge amount of data?


texts: SQL storage sizing - How to get statistics of what data is being accessed

texts:
How can I monitor which data is being accessed and which frequency?

I'm in need to migrate several (very) small SQL Server instances, each which several small databases. Current configuration is based in a lot of also small servers with local storage. New configuration is based in a single server with a single NAS.

So far, the SQL Server memory and CPU sizing is OK. Also DB sizes and total IOPS. But there's no existing documentation of what data set is actually being accessed. So, basically, I don't have a clue about what are the real storage requirements since the total amount of IOPS may be for only a couple of tables (so it would work like a charm with just a couple of SSD) or if the whole set of databases are being scanned all the time and I'll need several dozens of disks.

So, back to the question: How can I "profile" and get statistics of what data is being accessed? Either at SQL or Windows level?


texts: Where can I check my permissions as SQL Server user?

texts:
Everytime I have to do a Restore from a backup, I have to log out from my personal user account from the SQL Server instanc and log back in using our main group user account, which I manage it too as currently no one else is using the sql server until the next DBA will be hired!
So  how can I compare our privilages and see the permissions allowed for this account so I can ask the IT department to grant me the same.

Thanks for any advice


texts: SQL Server stored procedure keeps restoring to previous one

texts:
There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure changes to the one before Alter! This has happened several times during past hours!

I am certain that nowhere inside the program or manually I change the Stored Procedure. I checked all Agent Jobs, There's only one Maintenance job for hourly backup, and another one for Replication, however Replication Schema Changes is set to false, and Stored Procedures are not defined in the list of Publication Articles

Is there any other possible reason for this behavior? Do I need to manually set a CHECKPOINT in the SQL Server database?


texts: MongoDB: Mongoimport server selection error , connection refused, too many files open

texts:
Trying to insert several JSON files to MongoDB collections using shell script as following,
#!/bin/bash

NUM=50000
for ((i=o;i<NUM;i++))
do
    mongoimport --host localhost --port 27018 -u 'admin' -p 'password' --authenticationDatabase 'admin' -d random_test -c tri_${i} /home/test/json_files/json_${i}.csv --jsonArray
done

after several successful adding, these errors were shown on terminal
Failed: connection(localhost:27017[-3]), incomplete read of message header: EOF
error connecting to host: could not connect to server:
server selection error: server selection timeout,
current topology: { Type: Single, Servers:
[{ Addr: localhost:27017, Type: Unknown,
State: Connected, Average RTT: 0, Last error: connection() :
dial tcp [::1]:27017: connect: connection refused }, ] }

And below the eoor messages from mongo.log, that said too many open files, can I somehow limit the thread number? or what should I do to fix it?? Thanks a lot!
2020-07-21T11:13:33.613+0200 E  STORAGE  [conn971] WiredTiger error (24) [1595322813:613873][53971:0x7f7c8d228700], WT_SESSION.create: __posix_directory_sync, 151: /home/mongodb/bin/data/db/index-969--7295385362343345274.wt: directory-sync: Too many open files Raw: [1595322813:613873][53971:0x7f7c8d228700], WT_SESSION.create: __posix_directory_sync, 151: /home/mongodb/bin/data/db/index-969--7295385362343345274.wt: directory-sync: Too many open files
2020-07-21T11:13:33.613+0200 E  STORAGE  [conn971] WiredTiger error (-31804) [1595322813:613892][53971:0x7f7c8d228700], WT_SESSION.create: __wt_panic, 490: the process must exit and restart: WT_PANIC: WiredTiger library panic Raw: [1595322813:613892][53971:0x7f7c8d228700], WT_SESSION.create: __wt_panic, 490: the process must exit and restart: WT_PANIC: WiredTiger library panic
2020-07-21T11:13:33.613+0200 F  -        [conn971] Fatal Assertion 50853 at src/mongo/db/storage/wiredtiger/wiredtiger_util.cpp 414
2020-07-21T11:13:33.613+0200 F  -        [conn971]

***aborting after fassert() failure


texts: How to Solve the Failed to initialize the CLR

texts:
I Have a Following Error ..

  Failed to initialize the Common Language Runtime (CLR) v4.0.30319 with
  HRESULT 0x80004005. You need to restart SQL Server to use CLR
  integration features.

How To Fix This Error ..


texts: Microsoft® SQL Server® 2012 Service Pack 2 (SP2) Express

texts:
which version SQL server can Install Windows 10?

Is  SQLManagementStudio_x86_ENU.exe  free ?


texts: What is the formal definition of a Primary Index and Primary Key?

texts:
The below is from Fundamentals of Database Systems (7th Edition)

17.1 Types of Single-Level Ordered Indexes
There are several types of ordered indexes. A primary index is specified on the ordering key field of an ordered file of records. Recall from Section 16.7 that an ordering key field is used to physically order the file records on disk, and every record has a unique value for that field. If the ordering field is not a key field—that is, if numerous records in the file can have the same value for the ordering field—another type of index, called a clustering index, can be used. The data file is called a clustered file in this latter case. Notice that a file can have at most one physical ordering field, so it can have at most one primary index or one clustering index, but not both. A third type of index, called a secondary index, can be specified on any nonordering field of a file. A data file can have several secondary indexes in addition to its primary access method. We discuss these types of single-level indexes in the next three subsections.
17.1.1 Primary Indexes
A primary index is an ordered file whose records are of fixed length with two fields, and it acts like an access structure to efficiently search for and access the data records in a data file. The first field is of the same data type as the ordering key field—called the primary key—of the data file, and the second field is a pointer to a disk block (a block address). There is one index entry (or index record) in the index file for each block in the data file. Each index entry has the value of the primary key field for the first record in a block and a pointer to that block as its two field values.

Specifically my question is about a "primary index" as it relates to a more formal academic parlance -- I never went to a university so I have no idea how they're using this term. Is the  "primary index is specified on the ordering key field of an ordered file of records" correct?  Was that the original distinction between a PRIMARY key (for a primary index), and a regular key into a non-primary non-clustered index?
I'm thinking that this book didn't go through 7 editions at $151.00 a pop with the false idea that a primary key necessitate "ordered file of records."