texts:
I'm getting poor performance from DISTINCT.  The explain plan indicates that it is doing SORT (GROUP BY) which doesn't sound right.  I would expect some kind of HASH aggregation to produce much better result.  Is there a hint to tell oracle to use HASH for DISTINCT rather than sort?
I've used /*+ USE_HASH_AGGREGATION */ in similar situations, but it is not working for DISTINCT.

So this is my original query:

SELECT
count(distinct userid) n, col
FROM users
GROUP BY col;

users has 30M rows, each userid is there 12 times.  This query takes 70 seconds.

Now we rewrite it as

SELECT
count(userid) n, col
FROM
(SELECT distinct userid, col FROM users)
GROUP BY col

And it takes 40 seconds.
Now add the hint to do hash instead of sort:

SELECT
count(userid) n, col
FROM
(SELECT /*+ USE_HASH_AGGREGATION */ distinct userid, col FROM users)
GROUP BY col

and it takes 10 seconds.

If somebody can explain to me why this is happening or how I can beat the first simple query into working as good as the 3rd one, that would be fantastic.
The reason I care about query simplicity is because these queries are actually generated.

Plans:
1) Slow:

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation      | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |      1 |        |      5 |00:01:12.01 |     283K|    292K|       |       |      |     |
|   1 |  SORT GROUP BY     |               |      1 |      5 |      5 |00:01:12.01 |     283K|    292K|   194M|   448K|  172M (0)|   73728 |
|   2 |   TABLE ACCESS FULL| USERS |      1 |     29M|     29M|00:00:08.17 |     283K|    283K|       |       |      |     |

2) Fast

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |      1 |        |      5 |00:00:13.09 |     283K|    283K|   |   |      |
|   1 |  SORT GROUP BY       |               |      1 |      5 |      5 |00:00:13.09 |     283K|    283K|  3072 |  3072 | 2048  (0)|
|   2 |   VIEW               |               |      1 |   8647K|   2445K|00:00:13.16 |     283K|    283K|   |   |      |
|   3 |    HASH UNIQUE       |               |      1 |   8647K|   2445K|00:00:12.57 |     283K|    283K|   113M|    10M|  216M (0)|
|   4 |     TABLE ACCESS FULL| USERS         |      1 |     29M|     29M|00:00:07.68 |     283K|    283K|   |   |      |
--------------------------------------------------------------------------------------------------------------------------------------------


texts: How To Migrate From MSDE 2000 to SQL Server 2008R2 Express

texts:
We need to migrate a MSDE 2000 database running on a Windows Server 2003 Standard to a Windows Server 2008 R2 running SQL Server 2008 R2 Express (fresh install). The database is small, about 300MB.

Since Management Studio is not installed on the source server (Windows Server 2003), can we just stop the database instance via services and then copy the .MDF and .LDF files to the Windows 2008 R2 Server and attach the database using Management Studio?


texts: How can I insert if key not exist with PostgreSQL?

texts:
I have a table:

CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY)

Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the id. How can I do this with PostgreSQL?

I have seen a few scripts for this, but is there no single SQL-statement to do it?

E.g. I can INSERT and return id with:

INSERT INTO mytable (name) VALUES ('Jonas') RETURNING id

it works the first time and returns id. But it fails if Jonas already exist in the table, but I want to return the id even if the Insert fails. Is this possible to do with PostgreSQL?


texts:
We have a sever running a website Joomla 2.5. We are facing frequent mysql crashes that occur when website has medium visitor load like a few thousands. It appears that mysql consumes more memory and is killed by OOM killer. Currently if I look for memory consumption for mysql using top command, it says that mysql process is consuming 17% of memory. Normally the server shows upto 45% of total memory used. But when mysql crashes, system memory is recorded to be between 70% to 95% or sometimes 100%.

I have a feeling that its mysql memory settings (I think we have set huge values for different variables) which cause it to consume that much memory at peak traffic hours. Or it is the myisam tables in Joomla 2.5 that are causing it to crash due to locking of tables. Please suggest your thoughts.

Error from mysql.log is as follows,

140720 17:07:42 [Note] Plugin 'FEDERATED' is disabled.
140720 17:07:42  InnoDB: Initializing buffer pool, size = 512.0M
140720 17:07:42  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140720 17:07:42  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140720 17:07:42  InnoDB: Started; log sequence number 2 2605701970
140720 17:07:42 [Note] Event Scheduler: Loaded 0 events
140720 17:07:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-0ubuntu0.10.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Our server:

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000
RAM : 1Gb
Hdd : 50Gb
OS Ubuntu 10.04.4 LTS

my.cnf :

max_connections = 250
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
key_buffer = 16M
read_buffer_size = 128K
join_buffer_size = 128K
connect_timeout = 10
max_connect_errors = 10
max_allowed_packet = 16M
tmp_table_size = 16M
innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=40M
innodb_log_buffer_size=40M
innodb_thread_concurrency=2
innodb_flush_log_at_trx_commit=1

dmesg:

[276924.125161] Out of memory in UB 506518: OOM killed process 281 (mysqld) score 0 vm:983808kB, rss:119628kB, swap:57936kB

[276925.533666] Out of memory in UB 506518: OOM killed process 3473 (mysqld) score 0 vm:787876kB, rss:109864kB, swap:0kB

[305509.131154] Out of memory in UB 506518: OOM killed process 3553 (mysqld) score 0 vm:975140kB, rss:171960kB, swap:2068kB

[305619.694154] Out of memory in UB 506518: OOM killed process 10708 (mysqld) score 0 vm:973964kB, rss:161364kB, swap:0kB

[305621.875432] Out of memory in UB 506518: OOM killed process 11511 (mysqld) score 0 vm:841912kB, rss:152136kB, swap:0kB

[305622.938979] Out of memory in UB 506518: OOM killed process 11564 (mysqld) score 0 vm:841912kB, rss:120208kB, swap:0kB

[305623.061948] Out of memory in UB 506518: OOM killed process 11887 (mysqld) score 0 vm:789944kB, rss:115932kB, swap:0kB

[402005.199523] Out of memory in UB 506518: OOM killed process 12428 (mysqld) score 0 vm:982588kB, rss:162872kB, swap:11432kB


texts: What determines if a bytea has 1 or 4 extra bytes?

texts:
When inserting a BYTEA string in PostgreSQL the documentation says that such a string will occupy 1 or 4 bytes plus byte string length.

The question that I can't seem to find the answer to is what determines whether 1 or 4 bytes are added and is this effect consistent or will it vary depending on some factor or other?


texts: Return distinct rows based on matching data in a column in another table in PosgreSQL

texts:
I have a table (e_v2) with some time-series data. I want to fetch all rows where the data in the "POINT_KEY" column appears in the "KEY" column in another table (etraining_v1). The code below (I'm unsure whether it's correct) did return some rows, but there are many duplicates. I would like to exclude the duplicates.

SELECT * FROM e_v2
WHERE EXISTS (SELECT FROM etraining_v1 WHERE "POINT_KEY" = etraining_v1.key)
ORDER BY
"POINT_KEY",
"INTERVAL_READING"


texts:
I've been tasked with setting up our SQL server audits - despite having never done this before. So please forgive a really basic question.

I setup a specific user, sqlAudits, that I would like to own the audits.

How can I configure an audit to run under the sqlAudits account?

I've searched for hours but all I find it how to audit users, not how to change the account running the audit.

Can anyone point me in the right direction?


texts:
I want to use distinct to display my data.
But I don't know how to do it.This is my code.I want to display table gtest_version field max(poly_version)

select distinct on (rule_name)
rule_name,rule_version,polyname,state
from rule
order by rule_name,rule_version DESC


texts: MySQL schema for recurring events - Query by specific date

texts:
I've just asked this exact same question on stackoverflow so apologies for cross posting but I only just noticed dba so thought I'd ask across here as well.

I'm just about to start putting together a schema for a system that will have recurring events and keep going through different options. None of which seem ideal.

The requirements are that I need to store the events and the pattern by which they occur I also need to be able to query this data with a specific date to determine if the date/time specified is covered by any of these events.

An example that demonstrates the functionality I am after works along the lines of a train time lookup system.

I'd like to be able to insert the time at which a train departs for a specific journey. Say 10am every day Monday - friday.

Then I would like to be able to query this data set to see if any trains are leaving at 10am (with 15 minutes leeway either side so 9:45 - 10:15) on Wednesday 30th April 2014.

I keep going backwards and forwards on this without making any real progress so any ideas would be much appreciated.

Edit :

My initial thoughts on the table are as so:

The issue with this is that it is very static in it only allows for 24 hour granularity for the frequency of the trips. Ideally a system that allowed me to enter a start date and altering periods between trips would be the optimal solution. So for example, once a month, once a fortnight etc.


texts:
In SQL Server you could check specific index information using the DBCC PAGE command. Is there anything similar in postgres?

And if there isn't, how would you get the same info then? This is simply for research purposes.

This command returns this:

Page @0x00000004ED67A000

m_pageId = (1:299)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 121   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594045857792
Metadata: PartitionId = 72057594040614912                                Metadata: IndexId = 1
Metadata: ObjectId = 99             m_prevPage = (1:297)                m_nextPage = (1:300)
pminlen = 4008                      m_slotCnt = 2                       m_freeCnt = 70
m_freeData = 8118                   m_reservedCnt = 0                   m_lsn = (35:184:44)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 4011


texts: MySQL: How select the count of two queries while selecting another field too?

texts:
I'm trying to get how many orders happened during a month, if the order has a duration of 168, it's a weekly transaction so I have to divide it by 4, and get the ceiling. If an order has a duration of 720, it's a monthly transaction.

I want the result to contain the video_title and count, which is the transactions count which is all the monthly transactions + (weekly trasactions/4)

I tried the query below, it's correct but I get an error when trying to select the video title too which comes from videos table, how to select the video title as well?

SELECT video_title,
(
    SELECT  CEILING(COUNT(*)/4) FROM videos
        INNER JOIN wtb_order_archives
        ON wtb_order_archives.object_name = videos.video_name
    WHERE wtb_order_archives.is_test=0
        and wtb_order_archives.bypass=0
        and videos.is_movie=1
        and videos.owner = 'user'
        and wtb_order_archives.is_paid=1
        and wtb_order_archives.duration =168
        and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
        )
        +
        (
    SELECT  COUNT(*)
    FROM videos
        INNER JOIN wtb_order_archives
        ON wtb_order_archives.object_name = videos.video_name
    WHERE wtb_order_archives.is_test=0
        and wtb_order_archives.bypass=0
        and videos.is_movie=1
        and videos.owner = 'user'
        and wtb_order_archives.is_paid=1
        and wtb_order_archives.duration =720
        and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
        )
       as count
       GROUP BY video_title ORDER BY count ASC"
ORDER BY count ASC

And also what indexes are the best for such a query?


texts: How to set isolation to SERIALIZABLE DEFERRABLE for a whole Postgresql database?

texts:
I think read somewhere that it was possible to configure a Postgresql database to force all transactions to be SERIALIZABLE, but I can't find that document anymore.

In fact, I would like to force the isolation level of my database to SERIALIZABLE DEFERRABLE. Is this possible at all? If yes, how?


texts: Does SQL manage foreign key constraints in terms of insertion?

texts:
Say I have a table tbl1 with data A1 and A2, where A1 is the primary key.  Then I have another table tbl2 with data A3 and A1, both together being the primary key, referencing A1 as a foreign key from tbl1.

Am I able to insert a tuple into tbl2 that has an A1 that is not in tbl1?  Does SQL manage this for us as an error?  Or what happens in this situation?


texts: Is it possible to use a column value as the argument to a hypothetical-set aggregate function?

texts:
Say I have a table like the one described below.

                                     Table "public.foo"
  Column   |            Type             |                    Modifiers
-----------+-----------------------------+-------------------------------------------------
 id        | integer                     | not null default nextval('foo_id_seq'::regclass)
 data      | text                        | not null
 score     | integer                     |

To calculate the percentile rank for a particular score, I can do something like this:

test=# select percent_rank(5) within group (order by score) as rank from foo;
        rank
--------------------
 0.0762917366163029
(1 row)

What I'd really like to do is have a select statement that shows the data, score, and rank for each row present. I tried the below query.

test=# select data, score, percent_rank(score) within group (order by score) as rank from foo group by data, score;

This produces a rank of 0 for every row. I'm assuming this is because the within group clause ends up being limited by group by clause, but I'm not enough of a SQL ninja to know for sure.

At any rate, is it possible to include the percentile rank of a row's score as part of a multi-row select?


texts: Working SSIS package fails as a job, probably due to data flow connection

texts:
I'm working in SQL Server 2012. I've built out an SSIS package that is made up of a few scripts and 2 data flow tasks. The data flow tasks move data from the local SQL server out to an Amazon SQL server.

The whole package runs fine in Visual Studio. I imported it into the Stored Packages in Integration Services, and I can successfully run it from there.

However, when I set up a job and run that SSIS package as the one and only step, it fails. The only error message I get is that Step 1 failed. I can't trouble shoot why it fails because it otherwise runs successfully in Visual Studio and even in the Integration Services.

I've done a similar project before successfully, and what's different here and where I suspect the issue is in the Data Flow tasks. I suspect that the connection to the Amazon SQL Server is failing, that the password isn't getting properly passed. Again, I've got that information correct in the package, but somehow it's not running with that.

Can anyone point me to the way to properly pass the authentication info when the SSIS package is run as a job?


texts:
Im trying to find rows that contain certain unprintable characters and encounter some inconsistent behaviour of various string functions. The field is of type VARCHAR. Here are the queries:

select ascii(substring(AddressTail, 17, 1)) from Address where ID = 1934484 returns 0

select charindex(char(0), substring(AddressTail, 17, 1)) from Address where ID = 1934484 returns 1 which is expected

but

select charindex(char(0), AddressTail) from Address where ID = 1934484 returns 0 which is quite strange.

So, how to find the rows that contain byte zero? SQL Server version is Microsoft SQL Server 2016 (SP2-CU9) (KB4515435) - 13.0.5479.0 (X64)


texts: Cannot log in to the MySQL server they say mysqli::real_connect(): (HY000/1045): Access denied for user 'Stephen Ankoh'@'localhost' (using password: YES)


texts: How do you repair a 'Suspect' SQL Server 2008 database

texts:
My database has gone offline, it is highlighted as 'Suspect'.

Foolishly, I haven't got a recent backup.

Is there a way of quickly restoring the database?


texts: Difficulty with Postgis extension on Ubuntu 14.04

texts:
I have a updated Ubuntu 14.04 running postgresql 9.3 (installed using the tutorial from https://wiki.postgresql.org/wiki/Apt).

When I access my database through pgadmin and go to the console and type:

-"create extension postgis;"

I get an error that the extension is missing in the directory:

 /usr/share/postgresql/9.1/extension/postgis.control

But that is 9.1. - Is pgadmin connected to the wrong postgresql? In my postgresql directory I also have a folder "9.1", "9.2" and "9.3". And the postgis extension files seem to only be in the 9.3 subfolder.

Do I need to change something in the pgadmin settings? I kind of ran out of options myself.

Packages:

postgis 2.1.2+dfsg-2
postgresql-9.3-postgis2.1
postgresql 9.3+154.pgdg14.04+1
pgadmin3 1.18.1-2
postgresql-contrib-9.3

Edit:

 dpkg -L postgresql-9.3-postgis2.1

returns:

 dpkg-query: Package »postgresql-9.3-postgis2.1« is not installed.

but this worked

 sudo dpkg -L postgresql-9.3-postgis-2.1 2.1.2+dfsg-2

    /.
    /usr
    /usr/lib
    /usr/lib/postgresql
    /usr/lib/postgresql/9.3
    /usr/lib/postgresql/9.3/lib
    /usr/lib/postgresql/9.3/lib/postgis-2.1.so
    /usr/lib/postgresql/9.3/lib/rtpostgis-2.1.so
    /usr/share
    /usr/share/doc
    /usr/share/doc/postgresql-9.3-postgis-2.1
    /usr/share/doc/postgresql-9.3-postgis-2.1/copyright
    /usr/share/doc/postgresql-9.3-postgis-2.1/changelog.Debian.gz

Edit 2:

The server status is also complaining about "The server lacks instrumentation functions" and the missing conbrib-package. Here too the installed package "postgresql-contrib-9.3" is ignored.

Edit 3:

Output of "pg_lsclusters"

    Ver Cluster Port Status Owner    Data directory               Log file
    9.1 main    5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
    9.3 main    5433 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log


texts: What's causing this ERROR 2000 (HY000): Unknown MySQL error

texts:
I have a MySQL server with version 5.6.12-log (it's running on OpenSUSE). I can connect to it from another server (CentOS 6) using:

mysql -h **** -u **** --port **** -p

It prompts me for my password and connects just fine. The client version is "Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using  EditLine wrapper."

However, from another server (running CentOS 7), using the same command, I get the following error after entering my password:

ERROR 2000 (HY000): Unknown MySQL error

This error is useless. There is nothing in the client machine logs that indicate the cause of the failure. However, I do know that the MySQL server is at least receiving the connect request because it logs a warning about the Reverse DNS lookup not matching. It's just a warning—not the cause of this problem—but it proves that firewalls and/or network configurations aren't blocking the connection. There is nothing else in the MySQL server log (or error log) that indicates the cause of the failure. This client version is "Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using  EditLine wrapper."

Help??? I'm lost here. In 10+ years of MySQL admining, I've never seen this error before. I don't even know where to begin.


texts:
I have 4 cubes running on the SSAS server. The CPU seems to peg and we had memory issues with the 4th cube during loads.

We have 4 CPUs with MAXDOP set at 2, cost threshold for parallelism = 70. We added server memory 68GB to 96GB, with max server memory from 33GB to 44GB.

I had max SQL Server memory higher, but looks like the OS and other processes were causing memory issues, so I lowered it to 44GB. Is there a great article to help with SSAS and optimal settings? I would like to adjust them so we don't throw un-needed CPUs and memory at it if possible.


texts: Oracle: Very slow to purge table from recycle bin

texts:
I ran a "PURGE TABLE" command on a table in the Recycle bin. It has now been running for 25 minutes and still going on. Is it normal for a purge to take that long?
Is it safe to cancel (I'm using SQL developer which has a cancel button)?


texts: Update Duplicate record in select query of access

texts:
I have a select query (test) in access with below mentioned column

  ID
 2561
 2562
 2563
 2343
 2561
 2563
 2563

Now in ID, i have duplicate records , for example here 2561 , 2562 , 2563 are duplicated

So i just want to update these records like 2561 to 2571 , 2562 to 2572 , 2563 to 2573

I know how to update these records in sql server :

;with allrows as (
 select ID,ROW_NUMBER() over ( partition by ID order by ID)as rn from test
)
update allrows set ID = substring(ID,1,len(ID) -2) + right(ID,2) +10 where rn > 1

i understand in MS-ACCESS , we can use MID instead of substring.

but i m unable to figure out how to update in Select-Query of MS-ACCESS.

I have reffered this  link as well :

https://stackoverflow.com/questions/871905/use-select-inside-an-update-query

but still not figure out

Well My select query is :

select (rno.+right(no.,1)) as ID from test1 ;


texts: Indexed columns in SQL Server do not appear to work for basic queries according to execution plan

texts:
Disclaimer: I'm not a DBA. I have picked up a few things from this board in the past that I'm building from.
I have a table of google analytics session start times. I have an index on each column. I want to filter for all sessions that were started between two dates. Screenshot below shows the query, and the index.

The query runs quickly but I do not believe it's using the index based on the Execution plan which both says that there's a missing index and shows a table scan rather than an index scan:

Why?
Is it because of something about the way I'm searching through the datetime? If instead of looking between dates, I set it equal to a date, the execution plan shows it using the index:

But it's not just this table or datetime. Here's a different table with an index on a varchar column:

And a simple query on this one also tells me I'm missing the index:

I'm stumped.


texts: How to change datetime system timezone to other zones in SQL Query using CONVERT_TZ() SQL Function

texts:
I have two times timeA & timeB stored in database, I am using SQL Query to extract data from DB as:

SELECT ID, timeA, timeB, CREATED_AT, IS_ACTIVE
FROM datatable
WHERE IS_ACTIVE = TRUE AND NOW() >= timeA AND NOW() <= timeB;

which is working fine!

Now due to timezone changes, I need to have data compared in US/EASTERN timezone, I have found CONVERT_TZ() method to change the system time in any other timezone by using it as

CONVERT_TZ(NOW(), 'SYSTEM', 'US/EASTERN')

By applying CONVERT_TZ() function, SQL Query becomes:

SELECT ID, timeA, timeB, CREATED_AT, IS_ACTIVE
FROM datatable
WHERE IS_ACTIVE = TRUE AND CONVERT_TZ(NOW(), 'SYSTEM', 'US/EASTERN') >= timeA AND CONVERT_TZ(NOW(), 'SYSTEM', 'US/EASTERN') <= timeB;

and it is not working (giving null as result).

kindly suggest if I am doing anything wrong or other ways if possible to do this.


texts: Clear performance schema on read-only MySQL instance

texts:
I am using AWS Aurora MySQL with Writer and reader instances in replication. Both instance are used by two separate applications.
I want to clear the performance schema on the read replica server but getting below error.

An error occurred truncating performance_schema event tables. ('The
MySQL server is running with the --read-only option so it cannot
execute this statement', 1290)

Is there any option that can be done specifically for this AWS deployment?
Here is the version info:

Output of Select @@global.read_only, @@global.super_read_only;


texts: Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?

texts:
SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in.

I'm looking for the equivalent in MySQL - the only view-related information_schema item i can see is VIEWS which has no column info.

Is the only way to find the tables that the columns in a view belongs to to parse the view definition?  This seems rather barbaric, and maybe not possible if one doesn't control the SQL being used to define views.

Thanks for your time!


texts:
Let's imagine : I have several instances of an application, spread into several servers, each instance has a database and data of each database is regularly extracted and stored in a central data warehouse. Sounds like a classic stuff.

This DW size is ~1 To, all the tables are partitioned (monthly) on the creation date of the records.
The DB server has 32 Go of RAM allocated to SQLServer (2014, enterprise).

The thing is that the PLE of this DB server is always low. Low meaning less than 120 seconds.
I know that evaluating PLE "good" value is a complex topic (here or there) and should be correlated with other indicators, but I have always been told that the higher the PLE is, the better it is (for performance, stability, etc...).

The process that extracts data from production instances to the DW runs every 5 minutes.
This process can affect thousands of records, not necessarily the most recent ones (for instance a customer A is created, customer B that was created 6 months ago is updated, etc...). So each time SQLServer will have to access different pages. And therefore PLE will drop.
So, as the process is designed currently, the low PLE seems normal to me.
And actually maybe it's not a so big deal because all other processes using this DW (reporting, aggregation and all) are stable and performant.

So, questions:

is my understanding correct and low PLE normal in this context ?
should I stop worrying and tell the supervision guy not to shout every time Nagios says the PLE is too low?
or should I stop asking dumb questions and ask for more CPU / RAM ?


texts: Why is restoration from binary logs so slow?

texts:
We have a MySQL 5.5 server with binary logging enabled and a backup script that collects the binary logs and, once a day, generates a complete database dump with mysqldump.  In testing database restoration from the dump & binlogs, we found that the latter are an order of magnitude slower.

Specifically:

Restoring from a 92.8 MB database dump with mysql dbname < dbdump.sql took 3m 26.526s.
Restoring all of the binary logs from after the dump with mysqlbinlog -d dbname --start-position=107 mysql-bin.000{727..755} | mysql dbname took 38m 10.090s.  The total size of the binlog files was 56.8 MB, and the size of the output from mysqlbinlog was 66.4 MB.
Combining the above two steps by just restoring from a (93.2 MB) dump taken after the events the binlogs cover took 2m 15.406s.

Is this time disparity normal?  Can anything be done to reduce it?


texts: Update using PK from a lookup table, should I use the PK directly or select it using the associated lookup value? - best practice?

texts:
This is not asking for an opinion, simply what DBA's who are more professional than me tend to do in this situation.
Assume I have a simple lookup table status_type  such as this, where status_id is an autoinc integer PK and status_value is a varchar.
status_id  status_value
---------  ------------
        1  Active
        2  Lapsed
        3  Resigned
        4  Deceased
        5  Demurred
        6  Terminated
        7  Pending
        8  Redacted

and another table person has a field the_status INT as a FK linked to status_type from which I can deduce the status string pertaining to that person. This is easy to maintain and I can alter the text of the status_value if necessary without altering the person record and can add new status values as needed.
If, inside a stored procedure, I want to update table person and change someone's status, say to 'Resigned', is it best practice / more common to say
(a) UPDATE person
    SET the_status  = 3
    WHERE personID = 1234;

or
(a) UPDATE person
    SET the_status  = (SELECT status_id
                       FROM status_type
                       WHERE status_value = 'Resigned'
                      )
    WHERE personID = 1234;

Option (a) assumes that the ID of 3 will always mean the same as 'Resigned', so it won't matter even if I change the string contained in status_value to 'Left the company' but it needs a magic number hard coded in the stored proc, which I don't really like.
Option (b) does not require me to hard code 'magic numbers' into the stored proc but requires me to know what status_value string is currently held in table status_type to indicate someone voluntarily no longer works for us. Consequently the stored proc will need changing if the status_value in table status_type is ever edited.
Is either way more widely used than the other?
I did look at this and this


texts: Cannot reclaim Index Unused Memory in In-Memory OLTP

texts:
Steps to reproduce the problem
Create a database with memory-optimized filegroup and container
Create schema only in-memory table with nonclustered pk
Simulate insert and delete activity.
My result is that I have high index unused memory that won't go down.
 USE master
 go
 DROP DATABASE IF EXISTS MemoryOptimizedTest
 CREATE DATABASE MemoryOptimizedTest
 GO
 USE MemoryOptimizedTest
 GO
 ALTER DATABASE MemoryOptimizedTest
 ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
 GO

 ALTER DATABASE MemoryOptimizedTest ADD FILE (name='imoltp_mod1', filename='c:\imoltp_mod1') TO FILEGROUP imoltp_mod
 GO

 DROP TABLE IF EXISTS dbo.MyCache
 CREATE TABLE dbo.MyCache
 (
    PK int NOT NULL,
    SecondInt int NOT NULL,
    ThirdInt int NOT NULL,
     CONSTRAINT PK_MyCache PRIMARY KEY NONCLUSTERED (PK)
 ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

 go

/* Generate activity and monitor table size */
USE MemoryOptimizedTest
go

SELECT
    object_id,
    OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) AS Table_Name,
    memory_allocated_for_table_kb,
    memory_used_by_table_kb,
    memory_allocated_for_indexes_kb,
    memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_ID = OBJECT_ID('dbo.MyCache')

;WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
, tally AS (SELECT TOP (10000) n FROM Nums ORDER BY n)
INSERT INTO dbo.MyCache (PK, SecondInt, ThirdInt)
SELECT
    n
    , n+1
    , n+2
FROM tally

WAITFOR DELAY '00:00:02'
DELETE FROM dbo.MyCache

GO 50

When I run it on my localmachine Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Developer Edition
with 16 GB Max Memory and 1.5 GB Available memory, the memory_allocated_for_indexes_kb fluctuates normally.
When I run it on our DEV environment Microsoft SQL Server 2019 (RTM-CU7) (KB4570012) - 15.0.4063.15 (X64) Enterprise edition
2 TB Max Memory, 220 GB Available memory
The memory_allocated_for_indexes_kb only grows. I've simulated activity for a table for few hours and have index used memory = 0.24 MB, Index Unused Memory = 385 MB and it won't go down.
The garbage collector ran according to PerfMon Sweep expired rows removed/sec in XTP Garbage collection.
I read somewhere that the garbage collector doesn't free up space until it faces memory pressure but it seems weird that it would hold so much unused memory.
EDIT: I used Resource pool for the database holding in-memory tables. One percent is the lowest I could go. I filled the memory with an other table taking 99% of the resource pool and the memory_allocated_for_indexes still won't go down. Whether there is a transactional activity or not. No active trasnactions are blocking GC and I've waited for more than 30 minutes.
I can't accept any of the current answers as my problem is still not answered.
Note that the table rows are cleaned up, but not for the index.


texts: How can I stop script execution if insert value is null and set value if not null?

texts:
I have tried to use Case and SET NOEXEC ON but get the following errors:

  Syntax Error: unexpected 'SET' (set)
  Syntax Error: missing 'closing parenthesis'

INSERT INTO tag (table, repr, tag, value)
    SELECT 'product' AS table,
           @Id AS repr,
           'product_code' AS tag,
           CASE @Code
             WHEN NOT null THEN @Code
             ELSE SET NOEXEC ON
           END AS value`

@Id and @Code are declared parameters in the script


texts:
I’m designing a FAN web page for one artist, more specifically, the discography section. The discography includes this entire artist CD’s, Vinyls, 8-tracks, cassette, etc. So far, I’ve build something pretty good, but I got stuck when it came time to handle all of the artwork of a release, not just the front cover. Let me explain:

The model I had was handling only the front cover. The logic was as following:

One release contains 1 front cover, and 1 front cover could be in many releases (this happens a lot for editions of a release where the front cover of an edition is pretty much the same as the primary release.)

For that, I’ve build 2 tables: release and cover

release (release_id, title, cover_id,...)
cover (cover_id, filename, width, height,...)

The model worked perfectly until I’ve decided to add more artwork for releases. Since then, I’m stuck and I can't get my mind to think clearly.

Here’s what I’m trying to do:

I’m trying to add more artwork to a release, like the back cover, inlay, side a, side b, booklet pages, etc. The artwork needs to be in a specific order (front cover first, then back cover, etc).

I’m struggling on how to achieve that. Should I keep the one-to-many model and add one more table for the rest of the artwork? Should I mix all of the images in one single table (let’s call it Artwork)? If yes, how do I handle front covers so there’s only 1 front cover for each release. Should the front cover remain a 1:m relation since the rest of the artwork would probably end up being a 1:1 relation? If all of the images should go in one table, then how to position images and how to make the front cover unique and the primary cover of the release? So many questions that led me to build so many models, so many that at this point I’m totally lost!

Here’s the basics :

One artist discography (cd, cassettes, 8-track, singles,vinyl,etc...)
Some releases have up to 12 editions, and a lot of times the front cover of the main release is the same as the front cover of the edition.
I need to make one artwork the main artwork of the release (most of the time that would end up being the front cover)
I need to display all of the artwork in a specific order... (front covers are always the first displayed)

How would you approach this?


texts: Sql job fails when connection string is updated with port number,where as it succeeds without port number

texts:
We have a sql job which creates package dynamically based on metadata config values.We have specified connection string of servers in below format.

Source : sat\sql2012,5000
destination:tak3\war2012,55750

our job tries to first validate schema of metadata after acquiring source connection and then creates package and execute it.

We are seeing strange issues with the job,job is executing fine ,if we don't specify port number
Source : sat\sql2012,5000

Even with named pipes it is working,
np:sat\sql2012

But when we specify port number along connection string,job is failing in below order.

1.Package creation started
2.Unable to connect to given source destination Exception from HRESULT: 0xC020801C
3.Package creation failed

Steps we have tried:
1.Make sure port is enabled in firewall ..it is
2.port is set to static
3.Spn is set and there are no duplicates
4.we are able to connect through SSMS locally and remotely
5.checked which node server is listening thru portqry,it is 5000 only

SSIS account and sql agent account are both the same,but it doesnt matter,job working fine without port number,so no issues with permissions

We tried creating test package in server and tested connection,connection succeded too.

No info in error logs or eventvwr,except framework error which get in logs (as pasted above with exe name).
We tried debugging using windbg,when it failed ,it showed same info as eventvwr  logs

Our devs came up with theory,that job requires strong connection while validating schema,so it is failing and it is an infra issue.

We are out of options,even when we ran profiler and procmon,we are not seeing any connection drops from source and destinationCan some one help me on this please on how to debug further.


texts: Managing Mapping between different entities

texts:
A Company is National Entity, say for eg Apple-India (so there can be multiple Company like Apple-UK, Apple-Germany etc.)

A Company is furthur divided into Region (similar to Province/State)
    so Apple-India will be divided into Apple-NewDelhi, Apple-Maharastra etc.
    so Apple-USA will be like Apple-California, Apple-Texas

A region is furthur divided into Districts (county)
   so Apple-Maharashtra will be divided like Apple-Mumbai, Apple-Pune etc.
   so Apple-Texas will be divided like Apple-Dallas , Apple - Houston etc.

There is special status to store like say tax entity, some districts are privelaged to be taxed on rate x(TE1), some on y(Te2).

so lets say Apple-Mumbai will tax set to 10%, Apple-Dallas and Apple-pune to 8% and Apple - Houston to 5%;

An employee when joining the system is assigned mappings.

possible scenarios

He may be assigned to Apple-India   And to Apple-California
so it makes him responsible(some more bussiness logic,out of context) for all operations in  India and California state of US.
He may be assigned to Apple-India   And to Apple - Houston
so it makes him responsible for all operations in  India and city of Houston.
He may be Assigned to All store with 8% tax and Apple-USA.

so it makes him responsible for all operations in  USA and stores having 8% tax.(which in our case is Apple-pune [alog with Dallas, which is alrready in Apple-USA])

Currently my mapping table looks like

ID (Pk)
UserId(fk)
EntityTypeId (ref Company, State, district, Tax entity)
EntityTypePKId (so pk of table which is referenced by EntityTypeId)

since I am storing the mapping of all type of relations in same column so conditional joins on different entites, what will be better way to acheive the same?


texts:
What are the Unicode symbols that most closely relate to the symbols used in entity-relationship diagrams, such as the ones in the screenshot of the OP here: https://stackoverflow.com/questions/1755591/many-to-many-relationships-with-additional-data-on-the-relationship?
When displaying data, I want to prefix a Unicode representation of entity-relationship that its table represents.
The "many-to-one" entity relationship uses symbols that look almost like ">o-to-⧺" , respectively, but...

the actual many symbol, like in the screenshot and in some database books, has an extra glyph that looks like a strikethrough of the ">" symbol, almost like ->, but with the - attached to the > like a 3rd prong, not separated like it is here ->.
The actual one symbol appears to be the ⧺ (U+29fa =  Double Plus) character, which I found while writing this post, but I do not know if this is the actual correct symbol, but it looks very close to it.  Close enough, I guess.

I've gleaned through several Unicode charts, like this one for the many symbol, but have not found it.


texts: IF condition on temporary table takes long time for query execution

texts:
SELECT actualTable.column1, actualTable.column2, actualTable.column3
FROM table1 actualTable LEFT JOIN (
    SELECT column1, column2
    FROM table2
    GROUP BY column1
    ORDERBY ASC
) temp
WHERE IF(temp.column1 IS NOT NULL, temp.column1, actualTable.column1)
AND IF(temp.column2 IS NOT NULL, temp.column2, actualTable.column2)
GROUP BY actualTable.column1;

Above query is for reference. I have used 6 tables and added necessary joins.
The problem is that IF condition on Temporary table in Where cluase takes time. If i remove it then it takes less time to execute.


texts: What is the impact of changing the index creation memory setting in SQL Server?

texts:
I am trying to create some indexes a SQL Server 2008 database but am getting the following error message.

  Msg 8606, Level 17, State 1, Line 1
  This index operation requires 1024 KB
  of memory per DOP. The total
  requirement of 2048 KB for DOP of 2 is
  greater than the sp_configure value of
  704 KB set for the advanced server
  configuration option "index create
  memory (KB)".  Increase this setting
  or reduce DOP and rerun the query.

Currently the index creation memory setting is set to 794KB and the minimum memory per query is set to 1024KB. What would be the impact of changing these values? Especially with regards to performance. Should I change them, create the index and change them back or should I set it to 0?


texts: Computed Columns Or Views - Database Table Design

texts:
I want to store values in certain units along with their corresponding conversions. So for example, there would be following columns

  DateTime
  Millimetres
  Centimetres
  Metres

For a given datetime, the user will provide me a value in any one of the units. So I will need to calculate the rest by myself. Is this better to implement using computed columns or views? I suppose if I am using views or computed columns, I will need to always provide one specific unit as input which I can do in code before inserting. Is their any other way and what is the best way to implement such requirement?


texts:
I have inherited a system where the previous DBA added 7 data files to the PRIMARY filegroup (8MB initial size) and left the AUTOGROW option at 8MB. What I have now is a set of eight files each about 3 - 4GB in size that have been slowly growing over a two-year period. I'd like to remove the file fragmentation in the fastest way possible.

Here are the options that I came up with:

Expand the 1st file in the PRIMARY filegroup by ~28GB (7 files x 4GB)
Move the data off each of the successive files and mark them for deletion
Delete the other 7 files
Detach the database
Copy the detached database file to a different drive on the server
Copy the detached database file back to the original drive
Reattach the database

or

Create a new database 32GB in size (8 x 4GB)
Transfer all of the objects, tables, users and permissions to the new database using SSIS
Drop the old database
Rename the new database

Is an OS level "Disk Defrag" still not an option?

I do not know which option is the best or if it will even work.

Also, this database is being mirrored and replicated, so the least amount of work in regard to having to rebuild that it optimal.

Thanks for your help.


texts: Are mysql global variables values in bytes?

texts:
When I run the following in my mysql terminal

mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.01 sec)

This is probably very oblivious but is the value 16777216 in bytes?  I just want to be sure and can't seem to find a definitive answer on the web.

Is this the same for all values? Such as query_cache_size & query_cache_limit

I need to know this to set up a Amazon RDS Param group.


texts:
This is a very basic foreign Key issue, I presume. Unfortunately I am not a dba so it is a bit baffling to me.

I have two tables, Report and Request.

The Report table has a primary key of [RP_ID], and Request has a foreign key for field [RQ_RP_ID]. I can insert a record into the Report Table and it's primary key will have a value of 1. When I then try to enter a record into Request Table and enter the number 1 into field [RQ_RP_ID] I get an error message:

  The INSERT statement conflicted with the FOREIGN KEY constraint
  "FK_REQUEST_REPORT". The conflict occurred in database "THEDATABASE", table "dbo.REPORT", column 'RP_ID'. The statement has been terminated.

From my naive knowledge of what I know about Foreign Keys and what I am using it for is simply to keep my data clean by not allowing a report request to come through my app without a valid report id. if this is not what I thought it was please explain otherwise please explain why it is that this is not working as I expected.

Request Table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REQUEST](
    [RQ_ID] [int] IDENTITY(1,1) NOT NULL,
    [RQ_NOTE] [nvarchar](max) NULL,
    [RQ_DATE] [datetime] NOT NULL,
    [RQ_BY] [nvarchar](50) NOT NULL,
    [RQ_TYPE] [nvarchar](50) NOT NULL,
    [RQ_RECORD_ID] [nvarchar](50) NULL,
    [RQ_RP_ID] [int] NOT NULL,
    [RQ_MANAGER] [nvarchar](50) NOT NULL,
    [RQ_RESPONSE_DATE] [datetime] NULL,
    [RQ_RESPONSE_ACTION] [nvarchar](50) NULL,
    [RQ_REPORTING_NAME] [nvarchar](50) NULL,
    [RQ_REPORTING_ACTION] [nvarchar](50) NULL,
    [RQ_IS_CLOSED] [bit] NULL,
    [RQ_CLOSED_DATE] [datetime] NULL,
 CONSTRAINT [PK_REQUEST] PRIMARY KEY CLUSTERED
(
    [RQ_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[REQUEST]  WITH CHECK ADD  CONSTRAINT [FK_REQUEST_REPORT] FOREIGN KEY([RQ_ID])
REFERENCES [dbo].[REPORT] ([RP_ID])
GO

ALTER TABLE [dbo].[REQUEST] CHECK CONSTRAINT [FK_REQUEST_REPORT]
GO

ALTER TABLE [dbo].[REQUEST] ADD  CONSTRAINT [DF_REQUEST_RQ_DATE]  DEFAULT (getdate()) FOR [RQ_DATE]
GO

ALTER TABLE [dbo].[REQUEST] ADD  CONSTRAINT [DF_REQUEST_RQ_TYPE]  DEFAULT (N'OMISSION') FOR [RQ_TYPE]
GO

ALTER TABLE [dbo].[REQUEST] ADD  CONSTRAINT [DF_REQUEST_RQ_MANAGER]  DEFAULT (N'MANAGER_NOT_FOUND') FOR [RQ_MANAGER]
GO

ALTER TABLE [dbo].[REQUEST] ADD  CONSTRAINT [DF_REQUEST_RQ_IS_CLOSED]  DEFAULT ((0)) FOR [RQ_IS_CLOSED]
GO

Report Table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT](
    [RP_ID] [int] IDENTITY(1,1) NOT NULL,
    [RP_NAME] [nvarchar](50) NOT NULL,
    [RP_SHORT_DESCRIPTION] [nvarchar](200) NOT NULL,
    [RP_FULL_DESCRIPTION] [nvarchar](max) NOT NULL,
    [RP_IS_ACTIVE] [bit] NOT NULL,
    [RP_DATE_CREATED] [datetime] NOT NULL,
    [RP_DATE_DISCONTINUED] [datetime] NULL,
    [RP_REQUESTED_BY] [nvarchar](50) NOT NULL,
    [RP_IS_PDP_SPECIFIC] [bit] NOT NULL,
 CONSTRAINT [PK_REPORT] PRIMARY KEY CLUSTERED
(
    [RP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[REPORT] ADD  CONSTRAINT [DF_REPORT_RP_IS_ACTIVE]  DEFAULT ((1)) FOR [RP_IS_ACTIVE]
GO

ALTER TABLE [dbo].[REPORT] ADD  CONSTRAINT [DF_REPORT_RP_DATE_CREATED]  DEFAULT (getdate()) FOR [RP_DATE_CREATED]
GO

ALTER TABLE [dbo].[REPORT] ADD  CONSTRAINT [DF_REPORT_RP_REQUESTED_BY]  DEFAULT (N'SOMENAME') FOR [RP_REQUESTED_BY]
GO

ALTER TABLE [dbo].[REPORT] ADD  CONSTRAINT [DF_Table_3_RP_PDP_SPECIFIC]  DEFAULT ((0)) FOR [RP_IS_PDP_SPECIFIC]
GO


texts:
I have a database DAT1 running on DB2 version 10.5.
I want to enable logging for all CLOB and BLOB columns of my table TAB1.
The table was initially created with those columns as NOT LOGGED but now I need to change it to LOGGED.

I know there is the procedure ALTOBJ

that should be able to change this attribute. However I learned form the documentation that this procedure do the following:

  The procedure backs up the data of the table being altered, then drops the original table and creates a new version using the DDL statement; the final step loads the stored data back into the new table.

I need to modify many tables and the size of each table normally reaches about 10GB or even more. This means that this operation may require a very large amount of free disk space plus the time needed to create the new table and copy back the data.

I'm wondering if this operation can be achieved also with a simple ALTER TABLE or another command which doesn't involve creating or copying new data.


texts: Postgres Point-In-Time Recovery with a limited point in time

texts:
I'm configuring PostgreSQL WAL-based backup for point-in-time recovery. Actually, I don't really need any point in time beyond 1 month ago and don't want to waste time for the logs older than that.

Is that possible to combine the old logs with the current baseline snapshot, to get a new snapshot as if it was made 1 month ago while keeping all the newer logs for the PITR recovery?

To explain that schematically:

current_database == base1.tgz + older_logs + newer_logs
                 == base2.tgz + newer_logs

How to get base2.tgz?


texts: System Database Migration and Security - Log Shipping or DB Mirroring

texts:
I've setup log shipping between two servers (they used to be mirrored).

What is the simplest or most efficient way to mirror the system databases between the primary and secondary?

Is it just a matter of backing these databases on the Primary and then restoring them on the Secondary?

Further, does anyone know of a TSQL script or a (free) program that'll script all logins from the primary server?


texts: Assignment of dbms_metadata.get_ddl to pl/sql variable fails

texts:
I have the followig script

declare
    v_tmp  CLOB;
begin
   v_tmp := dbms_metadata.get_ddl('TABLE', 'FOO', 'BAR');
end;

Where FOO is the table's name and BAR is the name of the current user.
When I run the script I get:

06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints

.
The value returned by dbms_metadata.get_ddl appears to be of type CLOB so why isn't the assignement statement working?


texts:
I am not able to connect to my mysql (mariadb to be specific) via phpmyadmin, it seems there is some issue with my socket or port it tries to connect to.

At first, mysqld is running and I am able to connect to it on the command line and fiddle around with it.

I get two error messages on phpmyadmin login:
#2002 - No such file or directory — The server is not responding (or the local server's socket is not correctly configured).

mysqli_real_connect(): (HY000/2002): No such file or directory

I ran a script I came across while browsing stackexchange for solutions, these is the output:

Checking for mysqld process: root 1126 1 0 07:53 ? 00:00:00 /bin/sh

/usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysql/mysql.pid --skip-networking mysql 1219 1126 0 07:53 ? 00:00:16 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-networking --log-error=/var/lib/mysql/grooves.err --pid-file=/var/run/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock --port=3306 matthias 1589 1586 0 07:53 ? 00:00:17 /usr/libexec/mysqld --defaults-file=/home/matthias/.local/share/akonadi/mysql.conf --datadir=/home/matthias/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-matthias.77jGRS/mysql.socket
Failed with localhost, root, XXX
Failed with localhost:3306, root, XXX
Failed with 127.0.0.1, root, XXX
Failed with 127.0.0.1:3306, root, XXX
Failed with localhost, root, XXX
Failed with localhost:3306, root, XXX
Failed with ::1, root, XXX
Failed with ::1:3306, root, XXX

my hostname.err file tells me that mysql is listening on port 0 instead of 3306 what I would expect.

MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.0.27-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          2
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.0.27-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 5 hours 14 min 29 sec

Threads: 1  Questions: 4  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 63  Queries per second avg: 0.000
--------------

MariaDB [(none)]>

Can anyone point me where I should investigate further on the issue? thanks in advance, this is driving me nuts for days :)

--1st edit!
The script that lead to above output:

<?php

check_running();

$user='root'; // fill in your details
$password='XXX'; // fill in your details

$hosts=array(
  'localhost', '127.0.0.1', $_SERVER['HTTP_HOST'], $_SERVER['SERVER_ADDR']
);
foreach ($hosts as $addr) {
   try_con($addr, $user, $password);
   try_con($addr . ':3306', $user, $password);
}

function try_con($host, $user, $password)
{
 $dbh=mysql_connect($host, $user, $password);
 if ($dbh) {
     print "Connected OK with $host, $user, $password
\n";
 } else {
     print "Failed with $host, $user, $password
\n";
 }
}

function check_running()
{
   // this assumes that you are using Apache on a Unix/Linux box
   $chk=`ps -ef | grep httpd | grep -v grep`;
   if ($chk) {
      print "Checking for mysqld process: " . `ps -ef | grep mysqld | grep -v grep` . "
\n";
   } else {
       print "Cannot check mysqld process
\n";
   }
 }

?>


texts: Does row versioning increase the size of indexes?

texts:
Row versioning maintains 14 bytes of internal versioning information for each row. But is this really a cost per row or is this a cost that also applies to each index on the table?

It seems the 14 bytes must be added to all index records as well so that index-only scans (and other index-only accesses) can see the versioning information and perceive a point in time snapshot of the data.

All information that I could find on the web only talks about a per row overhead of 14 bytes, though.


texts: Why postgresql can't been accessed after added Datadog agent?

texts:
After added Datadog agent for postgresql, restarted Datadog. Reference:

https://docs.datadoghq.com/integrations/postgres/

Then check postgresql status, it's fine. But can't find in the process:

postgres@ip-1-2-3-4:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2018-06-18 06:21:05 UTC; 56s ago
  Process: 15863 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 15863 (code=exited, status=0/SUCCESS)

Jun 18 06:21:05 ip-1-2-3-4 systemd[1]: Starting PostgreSQL RDBMS...
Jun 18 06:21:05 ip-1-2-3-4 systemd[1]: Started PostgreSQL RDBMS.
postgres@ip-1-2-3-4:~$ psql
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Also tried to restart postgresql:

$ sudo service postgresql restart

The same result.


texts: Why are constraints applied in the database rather than the code?

texts:
Why are constraint applied in Database? Will it not be more flexible to put it in the code?

I'm reading a beginners book on implementing databases, so I'm asking this as a beginner. Let's say I have designed a database, including this entity model:

 entity type    |   sub-types
----------------+--------------------------------------------
   Person       |   Employee, Student,       ...
   Student      |   Graduate, Undergraduate, ...
   Employee     |   Teacher,  Administrator, ...

Current constraints:

A registered person on the system can only be a Student or an Employee.
Person entity requires uniqueness of social number, which we presume every person holds only a single unique one (aka, a good enough primary key). (see #1)

Later we decide to remove the number 1: If one day the college decides that the Teacher (the Employee sub-type) can also be Student, taking courses in their free time, it's much harder to change database design which could have thousands, millions, billions, zillions of entries rather than just changing the logic in code: just the part which didn't allow a person be registered both as a student and an employee.

(It's very improbable but I can't think of anything else right now. Apparently it is possible).

Why do we care about business rules in database design rather than in code?

#1: A note 7 years later, a real life example:
I have seen a government where because of a mistake, issued SSNs were duplicated: multiple people, same SSN. Those designing the original DB definitely made that mistake of not applying this uniqueness constraint in the database. (and later a bug in the original application? multiple applications using the shared database and not agreeing where to put, check and enforce the constraint? ...).
This bug will go on to live in the system and all the system developed after which rely on that original system's database, for many many years to come. Reading the answers here I learned to apply all the constraint, as many of them as possible, wisely (not blindly) in the database to represent the real physical world out there as good as I can.


texts: Join on clustered tables shows greater cost than non-clustered tables?

texts:
I have created a cluster and have two tables emp and sep on the cluster.

When I do the simple query...

explain plan select * from emp_cluster join dep_cluster using (dno)

the cost is 26.

I created two other tables on heap (emp_heap and dep_heap). When I do:

explain plan select * from emp_heap join dep_heap using (dno)

the cost is only 15, Which is less than that of the cluster.

I have inserted 33,000 records in the emp tables and 99 records in the department tables. I know that in join cluster behaves well but in my case it the opposite is true?


texts: Run query against all databases on server containing specific string in name

texts:
If I have a query such as:

SELECT convert(varchar, DateTime, 100) AS "Date and Time"
,Text2 AS Area
,Substring(PText7, 1,Charindex(':', PText7)-1) AS Username
FROM the_database.dbo.AlgViewENU AS data
WHERE PText6 LIKE '%BypIn%' AND DateTime = (SELECT MAX(DateTime) FROM the_database.dbo.AlgViewENU WHERE Text1 = data.Text1 AND PText6 LIKE 'Set%')

How would I run the same query against all the databases in the server containing "the" (for example) in the name? I haven't had any luck with sp_MSforeachdb


texts: "Slow Storage Writes on Drive T" warning from sp_Blitz

texts:
We migrated our platform to new SQL Server 2016 instances a couple of nights ago. Immediately following the migration, we ran sp_Blitz again, but noticed the following entry, which relates to the dedicated volume holding tempdb (all eight data files and the single log file reside on the same dedicated volume). All of the database volumes are using SSDs so this is somewhat surprising:

  Slow Storage Writes on Drive T Writes are averaging longer than 100ms
  for at least one database on this drive.  For specific database file
  speeds, run the query from the information link.

No link was provided, but delving into the code, we found that sp_Blitz uses dm_io_virtual_file_stats to assess the following logic:

o_stall_write_ms / ( 1.0 + num_of_writes ) ) > 100 AND num_of_writes > 100000

Using this logic, the function points to all eight of our tempdb data files, with write speeds of between 164-198ms, which suggests we have a serious issue.

To prove the issue to management, we have since run Performance Monitor traces to review the "Avg. Disk sec/Write" counter for all volumes attached to the server, but the T volume did not appear to be any slower than the other volumes during the working-hours time frame we sampled.

We have since run CrystalDiskMark against the OS, Data, Log and tempdb volumes on the secondary (log shipped) server, which uses the same SAN storage as the server which raised the sp_Blitz warning. All storage volumes show very similar figures to the below.

QUESTIONS:

Are the CrystalDiskMark speeds expected figures for SSDs, and are they consistent with the warnings in sp_Blitz?
Are we using the wrong PerfMon counter to prove this? If so, what counter should we use?
Is the above function showing us live speeds, or just the worst speed it ever encountered (i.e. should we ignore this warning, unless it is still there after the next server restart)?
Could anyone please provide further insight/advice?


texts: Using column alias in a WHERE clause doesn't work

texts:
Given a table users with two fields: id and email.

select id, email as electronic_mail
from (
        select id, email
        from users
) t
where electronic_mail = ''

Postgres complains that:

ERROR:  column "electronic_mail" does not exist

The example is just to demonstrate the arising problem. My actual case is more complex, I iterate though an array of elements in a json column, taking a single scalar value from each. (I can share some code if that helps.)

I really don't get what would be the complication, probably I'm unaware of something. I was under the impression that aliased columns can be employed in a WHERE clause without problem?


texts: IBM DB2 v9.7 HADR TSA failure on the standby server

texts:
I have an environment here where I have 2 servers running DB2.
One is the primary and the other one is standby. They replicate with HADR and failover is made automatically by TSA.

What's the default behaviour when the standby host fails ? Because for now failover is perfect when the primary peer fails. And then I can reintegrate the old primary peer as standby.
But when the standby peer fails, here is my problem, I have to start HADR manually on this peer. Is it normal ?


texts: do NOT log the actual queries ever (or maybe if they are above a certain size)

texts:
How can I completely disable logging of queries?
Nothing I do seems to suppress logging of the query when there is an error. Some of our cache values are quite large, and we occasionally hit a memory error: printing the queries into the Postgres log file results in huge logs (20+ GB).
Is there a way to never ever actually print the query statement?
we have tried setting log_statement="none" and log_error_verbosity="TERSE" and about a billion other settings... in the
/etc/postgres/9.5/main/postgresql.conf

May be there is some way to call a hook that truncates queries in log messages, or specify a magic format (probably not the prefix_format) that tells it to not print the actual statement.
It's probably worth noting that we primarily run into this issue on our non-production test server, where we have significantly larger values for some items.


texts: MariaDB 10.1.22 to use more RAM rather than CPU

texts:
Im using:

Server version: 10.1.22-MariaDB - Source distribution

server spec:

128GB RAM
CPU: E5-2600

we have around 80 tables with moderate data below 10k record per table, all of them are InnoDB. I would like to utilize it to use more RAM rather than CPU of the server is it possible?

my.cnf :

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user    = mysql
datadir = /www/server/data
basedir = /www/server/mysql
log_error = /www/server/data/mariadb.err
#pid-file = /www/server/data/mariadb.pid
default_storage_engine = InnoDB
#skip-external-locking
#loose-skip-innodb
skip-name-resolve

performance_schema = OFF

key_buffer_size = 1024M
max_allowed_packet = 100G
table_open_cache = 4096
sort_buffer_size = 16M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 256M
thread_cache_size = 512
query_cache_size = 512M
tmp_table_size = 512M
max_heap_table_size = 512M
table_definition_cache = 8192
table_cache = 4096
join_buffer_size = 10M
sort_buffer_size = 10M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#skip-networking
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

default_storage_engine = InnoDB
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 30
innodb_log_files_in_group = 2
innodb_thread_concurrency = 20
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_file_format = Barracuda
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_log_compressed_pages = 0
innodb_flush_neighbors = 0
innodb_adaptive_hash_index_partitions = 16
innodb_buffer_pool_instances = 4

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 16M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

UPDATE: we have alot of this kind of SQL hitting on the site (pagination), but its already indexed

SELECT VID, title, duration, addtime, thumb, thumbs, viewnumber, rate, likes, dislikes, type, hd FROM video WHERE active = '1' AND type = 'public' ORDER BY com_num DESC LIMIT 2952, 36

its show something like this on EXPLAIN:

/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 2 queries: 0.562 sec. */
Using index condition; Using where; Using filesort

do you think that the filesort caused it to do more CPU usage?

the cpu usage on photo above (the red box) will spike to 160% on peak hour, how can we reduce it?


texts: Is there a way to query SQL Server 2005/2008 for location of its log files?

texts:
I there a way to query SQL Server 2005+ for the file system location of its various log files. By that I mean the text log files, not database transaction log files?

For example my SQL Agent, Error Logs and maintenance plans all write to a folder called:

D:\MSSQLData\MSSQL.1\MSSQL\LOG

It's this folder I need to find the name of.

Updated:

As well as hunting SQL Books I had a poke around the registry under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

and

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER`

but nothing jumps out at me.

I'd also like an "official" way to achieve this and would rather not rely on undocumented features.


texts: Concat a string to a variable to use as a column name in a query

texts:
I have a procedure where I query table a and create variables like this
SELECT TRCODE , COLA , COLB , COLC , COLD
INTO TRANSACTION_TYPE , HOURS , RATE , FACTOR , APPROPRIATION
FROM PR . PREXEC
WHERE USRID = USER_NUMBER ;

The appropriation variable contains one character and it is a number from 1-4.
I have another table called pr.earnings. In that table I have a columns named APPROP1 through APPROP4. In this same procedure I need to query pr.earnings for by taking the string 'APPROP' and concatenating the APPROPRIATION variable from the query.
I tried
select concat('APPROP',APPROPRIATION)

FROM PR.EARNIGS;

The result I want is select APPROP1 FROM PR.EARNINGS; What happens is I get a system created column with APPROP1 inside it. I can't figure out how to concat a string to a variable to use in a query.
I tried a using dynamic sql but kept getting errors because I don't know what I'm doing.
How do I concat a string and a variable to use it as a column name in an sql query?
Based on what Lennart suggested I have this now
SET STMT = 'SELECT APPROP' || appropriation || ' pr.earnings into ' || active_appropriation ||
               ' where empnumber = ' || user_number;
    EXECUTE IMMEDIATE STMT

And am trying to insert active_appropriation like this
insert into pr.worksheet (W_EMPLOYEE_NUMBER, W_TRANSACTION_TYPE, W_HOURS, W_RATE, W_APPROPRIATION)
    values (user_number, transaction_type, hours, rate, active_appropriation);

I get the error 'Variable cannot be null' I am assuming it is the active_appropriation value.
I got it to work. here is my solution.
    SET STMT2 = 'VALUES (SELECT approp' || appropriation ||
    'FROM pr.earnings where empnumber = ' || user_number || ') INTO ?';
PREPARE S2 from stmt2;
EXECUTE S2 using active_appropriation;


texts:
How to concatenate multiple columns in to a single row? For example:

id   name    car
1    sam     dodge
1    ram     maserati
1    john    benz
1    NULL    mazda
2    kirk    lexus
2    Jim     rolls
1            GMC

The expected result set is:

ID  name               car
1   sam,ram,john       dodge,maserati,benz,mazda,GMC
2   kirk,jim           lexus,rolls

Using a solution I found on Stack Overflow:

SELECT * FROM (
SELECT t.id,stuff([m].query('/name').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined1],
stuff([m].query('/car').value('/', 'varchar(max)'),1,1,'') AS [SomeField_Combined2]
FROM dbo.test t
OUTER apply(SELECT (

SELECT id, ','+name AS name
,','+car AS car
FROM test WHERE test.id=t.id
FOR XML PATH('') ,type)
             AS  M) A)S
GROUP BY id,somefield_combined1,somefield_combined2

Are there any better solutions? The inner select comes from an expensive multi-table join (not the single table 'test' shown above). The query is in an in-line TVF, so I cannot use a temporary table.

Also, if there is a blank column the results will yield extra commas
like

ID  name                car
1   sam,ram,john,,      dodge,maserati,benz,mazda,GMC
2   kirk,jim           lexus,rolls

Is there any way to prevent this?


texts: 10.2.0.5 RAC instance running on Linux-64 recieved KEWRRFC: Remote Flush slave Failed, return code: 2

texts:
Last Friday one of two instances in our 10.2.0.5 RAC cluster was non-responsive after logging the following messages. We ended up rebooting the node and it came up successfully. While I will likely end up creating an SR for this, I would like to know if anyone else has had a similar issue. The mmon process was taking up 100% of the CPU, and I could not connect to the database even as sysdba. The metalink notes that I saw point to AWR as the culprit, but they were getting return code 5, not return code 2.

alert log
Fri May 30 03:55:52 EDT 2014
MMNL absent for 5619 secs; Foregrounds taking over
Fri May 30 03:55:55 EDT 2014

trace file
*** 2014-05-30 03:55:55.790
*** SERVICE NAME:(SYS$BACKGROUND) 2014-05-30 03:55:55.790
*** SESSION ID:(536.1) 2014-05-30 03:55:55.790
*** KEWRRFC: Remote Flush slave failed, return Code: 2.

Thanks,

Brian


texts: Why isn't to_char IMMUTABLE, and how can I work around it?

texts:
How can I index a to_char() of a column?

I have tried:

adam_db=> CREATE INDEX updates_hourly_idx
          ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00'));

But got the error:

  ERROR:  functions in index expression must be marked IMMUTABLE

Which seems strange, since the to_char() of a timestamp is reasonably immutable.

Any ideas how to generate that index?


texts: Ola Hallengren solution, backups not cleaning up

texts:
I have about 30 SQL Server 2005-2014 instances, all of which use the excellent Ola Hallengran maintenance script to produce my full/diff/log backups. This has worked for me for years with no issues, until recently.

One of the servers has two instances. In the case of the default instance on that server, the cleanup of the .bak files does not occur.

I wouldn't assume this has anything to do with the script, but rather something else like perhaps the .bak files are locked when the cleanup occurs. I have changed the scheduling of the backups in case the backup window is clashing with another process, to no avail. None of the other instances have this issue, including the named instance on the same box.

I know for a fact this is not a permissions issue, as the instance's Agent runs under a domain account with full control of the backup target volume, which is a dedicated share on a physical server, RAID 5 (unfortunately). It has group policy permissions as follows:

bypass traverse checking
log on as batch job

I have checked all the event logs on the backup server and see nothing that would help me troubleshoot it.

Can anyone offer any advice as to how I might be able to troubleshoot this issue?

Thanks

Update:

Date/Time on Server is consistent with the DC and my workstation. The backup server also has perfect time sync so definitely not a time slippage issue.
The @cleanupTime is just as I'd left it (72 hours).
I had initially assumed tape backups of that volume might be the culprit, but have since checked and triple checked that the files are not being locked - our Veeam backup of that volume only happens once a week.

I can delete the files manually with no issues.

On your advice I have added -o to the end of the command to get visibility on how the commands look. The only thing I think might be potentially the cause is:

  Date and time: 2015-10-20 11:29:58
  Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\backupServer\Production\SQLInstance01\Reporting_Database\FULL', 'bak', '2015-10-17T03:29:58' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
  Outcome: Succeeded
  Duration: 00:00:00

So the command appears to be using a UNC path which is not terminated with a backslash. I have read elsewhere that the xp_delete_file SP requires a backslash:

https://stackoverflow.com/questions/24582996/sql-server-xp-delete-file-parameters

So this is probably a red herring but based on the -o output, the xp_delete_file SP wouldn't work correctly. I'm going to check the output on another instance which doesn't exhibit this problem to compare. Thanks for your suggestions.

Update#2:
I have added the -o to my command on another instance which does correctly cleanup files and found that the output for this also does not have a trailing \ in the output file, so I'm still at a loss.

Update#3:
I tried deleting the filed by dropping the command from the output file into SSMS. What I have found is that the command works correctly on any other instance, just not this one. **It says "Command Completed Successfully" but no files are deleted. Is there an instance-level setting that would enable/disable the ability to use xp_delete_file?


texts:
We have internal audit team, which works with E&Y and SOX on Monthly, Quarterly and Yearly basis. They would come up with different kind of questions each time when its time for monthly/quarterly/yearly audit needs to be done. This would involve my DBA's to spend almost a day and half (depending upon requirements) to provide them the information (i.e. Screenshot, Query, Result-set) they are looking for.

Here are few examples of what they ask to see:

Who has DBO/SA access?
Who can modify particular object?
Who got access to modify MonthEnd process since last time we did security check?

We have asked our internal audit group to provide us list of things (i.e. most commonly asked question from E&Y and SOX), so this way, we can build them a Security Dashboard, which internal audit group can utilize to see, who has access to what and so on without requiring DBA help. But, each time we asked for requirements from them, we get little run around or just won't respond with requirements. I know there are 3rd Party solution out there like Idera, Apexsql...but we don't have luxury to spend money on 3rd party tools at the moment.

My questions are:

Does anybody have build their Own Solution to provide Security information to internal audit via Dashboard/Report/Some Automated ways so they can export out result-set or take a screenshot from the dashboard or something like that?
Is there a way to get requirements which are approved by E&Y and SOX compliance for SQL database?

We are all Microsoft shop and have various SQL Server i.e. 2000 (please don't ask why still SQL 2000) to 2016.

**Note: If you think this is not the correct place to post this question or is missing anything or wanted me to correct anything, please let me know.

Thank you,

HP


texts: Subtract values in same column from 2 different dates in same Mysql table

texts:
I have a MySql table like:

Item        Date          Likes

Book        2012-05-20    402
Book        2012-05-21    432
Book        2012-05-22    450
Pen         2012-05-20    20
Pen         2012-05-21    20
Pen         2012-05-22    25

1) What is the simplest query for calculating and returning the difference in Likes column between 2 set dates for all of the Item values, ordered by the Difference?

For example:

Likes difference between 2012-05-20 and 2012-05-21 returns next results:

Item        Difference
Book        30
Pen         0

Likes difference between 2012-05-20 and 2012-05-22 returns next results:

Item        Difference
Book        48
Pen         5

2) Also there will be some cases when there will be no records for some of the dates for an Item in the table, in those cases no results for that Item should be returned.

3) What should be the correct indices to optimize the speed of the query?

I did come across this query that might work but it is a huge query and I am sure that there can be a more compact and elegant solution :)


texts: Get all items from list table which don't have a successful log entry for 'sent' in log table

texts:
How might one retrieve items that when joined either don't have a specific field (sent), or if they do have that field (sent) then they have an additional field (failure_reason) as well for every sent row?

Consider a database with two tables:

list
  id INT
  name VARCHAR

log
  id INT
  list_id INT REFERENCES (list.id)
  action VARCHAR
  failure_reason VARCHAR

Typical list contents:

ID | Name

1  | John
2  | Paul
3  | George
3  | Atwood

Typical log contents:

ID | List_id | Action  | Failure Reason
---+---------+---------+---------------
1  | 1       | entered | NULL
2  | 1       | sent    | NULL

3  | 2       | entered | NULL
4  | 2       | sent    | Connection Error
5  | 2       | sent    | NULL

6  | 3       | entered | NULL
7  | 3       | sent    | Cosmic Ray

8  | 4       | entered | NULL

It can be seen that List_id 1 has a log entry for both entered and sent, with no failure_reason. Thus, this item has left my responsibility.

Likewise, List_id 2 has a log entry for entered, and two for sent. This is because the first sent failed. We know that the first sent failed as the failure_reason is not NULL. Since there is a successful sent row, this item has also left my responsibility.

However, List_id 3 has a log entry for entered, but the only sent row is a failure. Thus, this item is still my responsibility and should be retrieved in the query.

Additionally, List_id 4 has a log entry for entered, but no sent row. Thus, this item is also still my responsibility and should be retrieved in the query.

I have tried using some subquery magic to get / exclude rows based on the sent values, however as this table is growing huge (expected thousands of new records per day) I need to avoid queries that require returning all results in the entire database.

This is in MySQL 5.1 on CentOS 6.x.


texts:
What do indexes look like in an Entity Relationship Diagram?

I've googled it and I'm unsure what the standard look of an index is in an ERD.  And I'm talking about the crowsfoot diagram that includes all of the fields.


texts: Performance of Query where time captured from SP statement completed differs by large from RPC completed

texts:
I am unable to understand the performance of below query when collected data from XE events:

Query

DECLARE @Importdate DATETIME = ISNULL ((

SELECT ImportDatetime
FROM
Customertable
where CustomerID = 1
and OrderID =1
) , '01/01/2000')

This part of statement returns just 1 row

  SP statement completed shows 40 secs

  RPC Completed for above statement shows 64 secs

Need help to understand why RPC will show such a big difference and where to look for this problem ?
I do not see any NW issue reported as such but what does this indicate here and query as simple as above seems to high for that run time

Please advice


texts: Potential schema for product failures and repairs

texts:
I'm creating a schema for product failures and repairs and would appreciate feedback on the schema.

The database is intended to capture all product failures and corresponding repairs. A product can fail multiple times and can be repaired multiple times(many to many relationship). Not all products fail, and no data will be captured for products that don't fail.

All products have a job which dictates the product_pn(product part number), the due date, and the qty to ship. A product is assigned a unique DMR number if it fails. DMR means the product is discrepant material and should be quarantined until a repair is made to determine if the product can be shipped. A given product will never have more than one DMR number. The DMR number is used to uniquely identify a given product to know what failures and corresponding repairs have occurred(a person can enter the dmr number to see the full history of failures and repairs for a given product). If a product is successfully repaired the product is assigned a unique sn(serial number). The DMR number is linked to the sn and removed from the product, so a history of all failures and repairs can be maintained for any product. Some products will be scrapped and will never receive a SN.

A product failure is reported by a technician(any technician can report a failure), and requires a description of the failure and what operation the product failed at(inspection, programming, testing) as well as a timestamp of when the failure was reported.

A repair requires a disposition(repaired, use-as-is, or scrap), a repair code(process not followed, bad test equipment...), a description of the repair, and who the qualified technician is that repaired the board. We also need to capture any components that were replaced on the product(many components could be replaced on a product), and a timestamp the repair information was submitted.

Here is what I have for the schema so far:

I'm a bit unsure when to use an enum vs creating another table(e.g. disposition can be scrap, repaired, or use-as-is). Am I on the right track?

Also, I'm planning on using MySQL innodb tables, any problems / suggestions with this? This is for a small ajax based web app... Thanks in advance.

Edit: Thanks for all your help so far. Sorry I'm new here so had to try to clarify the original post based on feedback.


texts: Query to determine percentage composition on a specific grouping of records

texts:
I want to analyse the Market capital of the Industry per Counterparty of the following table:

--------------------------------------------------
| CPTY | Industry      | Value   |
--------------------------------------------------
| a    | food          | 10      |
| a    | water         | 5       |
| c    | wood          | 20      |
| c    | food          | 20      |
| d    | wood          | 10      |

I would like a breakdown of percentages for each of the CPTY fields in relation to their child Industries.

A sample of the output query I would expect should look like:

| CPTY | Industry      | Value   | %IndustryPerCpty
--------------------------------------------------
| a    | food          | 10      | 66.66
| a    | water         | 5       | 33.33
| c    | wood          | 20      | 50.0
| c    | food          | 20      | 50.0
| d    | wood          | 10      | 100.00

E.g. for CPTY a, the corresponding sectors are food and water. The total value for CPTY a is 10+5=15. Thus, food has 10/15*100% = 66.66% of the total market value for that CPTY. Likewise, water has 5/15 *100% = 33.33% of the total market value for that CPTY.

My original attempts at the query follow the structure adopted in accepted answer here. However, this gives me percentage composition for each row, not the Industry per CPTY.

Note: I don't expect the sum( %IndustryPerCpty ) column to equal 100%, for reasons I hope are clear.

Can anyone suggest a query structure in Ms Access to generate my desired table with %IndustryPerCpty fields?


texts:
I thought the value in msdb.dbo.backupset.compressed_backup_size represented the size, in bytes, of the full, differential, or log backup file (that is, the BAK, DIFF, or TRN file).  However, although it is either exact, or very close, for full backup (BAK) files, for log backup (TRN) files, it's so far off that the results seem random.

Steps to reproduce (SQL Server 2008 R2 SP3):

(1).  Create a database named 'MyDatabase'.  Set it to Full Recovery Model.

(2).  Take a compressed full backup of 'MyDatabase'.

(3).  Take a log backup of 'MyDatabase'.

(4).  Run the following query:

SELECT
      bs.[database_name]
    , bs.[backup_start_date]
    , bs.[backup_finish_date]
    , bs.[type]
    , bs.[compressed_backup_size]
    , bmf.[physical_device_name]
FROM
    [msdb].[dbo].[backupmediafamily] AS bmf
    INNER JOIN [msdb].[dbo].[backupset] AS bs ON bmf.[media_set_id] = bs.[media_set_id]
WHERE
    bs.[database_name] = N'MyDatabase'
ORDER BY
    bs.[backup_start_date] DESC

(5).  Compare the value in the compressed_backup_size column to the actual size of the files on disk.  An example of my results for a log backup are that the 'compressed_backup_size' value displayed is 9505 bytes; the size of the TRN file on disk is 28.0 KB (28,672 bytes).

I've tried this on several servers (with existing databases, as well as with newly created ones) and in all cases the TRN file sizes are reported incorrectly. Is anyone able to reproduce this?  Am I misunderstanding the meaning of the compressed_backup_size column?


texts: Granting Select access to all tables within a specific Database in MS SQL

texts:
I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012.
I have a special role set up with 3 users that are apart of that role. I would like to grant Select access to the role for all tables that are within 1 specific database.
The database in question is an archive database that has archive tables for each month for the past 12 years. When originally creating the role, I granted access by just running the following:
GRANT SELECT ON [dbo].[myarchivetable] TO myspecialrole

go
This worked great because I would just have to change the year and month and run it all as 1 script.
I am now finding out that they are needing Select access to every table in the archive database. I could do the above method, however Having to write all of that out 132 times, it would be very easy to make a mistake.
How can I easily add the myspecialrole to every table within that database?


texts:
I have this statement

SELECT *
FROM memberships
WHERE EXTRACT(HOUR FROM expired_at) = 0
  AND EXTRACT(DAY FROM expired_at) = 15;

I need to UPDATE all those records to be 08:00:00

So for example, if a record is 2016-06-15 00:00:00, I want to change it to 2016-06-15 08:00:00


texts: CREATE TABLE not issued on other masters

texts:
Database is created on all multi masters.

Why CREATE TABLE doesn't being replicated to other masters.

some explanations?


texts: MySQL - identifying bottlenecks in complex queries

texts:
I apologize if this question is too general, I can post and example code, but it may not be reproducible because there is no access to the actual database.

Suppose I have a big MySQL query with a lot of joins and unions, as well as functions like concat, date, various time and date conversion functions. And it uses a lot of tables, nested select queries etc. Lets suppose it is a select query.

My questions is, where would one start, if they need to optimize this script to run faster? Currently its taking hours to complete. Furthermore, If I run stand-alone chunks of code from it (including some nested queries, etc.) they run much faster. Therefore there are one or a few bottlenecks.

I am aware of profiling and bench-marking as well as explain functionality in MySQL they all help us to understand what MySQL does behind the scenes, but all provide the summary for the entire script overall. What would be the best way to identify these bottlenecks without profiling each each portion of the script separately? Are there best practices when faced with such problem?

Again, I apologize for asking a question that may be too broad. I can post and example code, but it may not be reproducible because there is no access to the actual database.


texts: Very large btree index with few rows (openstreetmap gis data)

texts:
I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then recreating them) and they end up taking massive amount of space.

The table size is 9GB, with an int id, 70 text columns for attributes (for example column access_type has 250 different text values, and is null in 90% of cases), and a possibly large geometry column. If I try:

 CREATE INDEX planet_osm_polygon_accesstype_index
   ON planet_osm_polygon
   USING BTREE (access_type)

I end up with an index that's 1GB in size for 140k rows, which is massive considering how little data there is. This doesn't seem to happen on other systems, and I tried to ANALYZE, then VACUUM.

I don't know much about Postgres, but any hints about how to reduce the index size would be very appreciated.

(OS is ubuntu 12-04, PostgreSQL is version 9.1)


texts: How to real time replicate a SQL Server DB without primary keys defined

texts:
Is there a way to replicate a SQL Server database real time without primary keys defined?

I understand we can't use the SQL transactional replication because of lacking primary keys. Is there any 3rd party tool that can do that?


texts: I have worked on MySQL databases for a long time, but one question I have never been able to get an answer to that seemed formal is: In the query SELECT *, SUM(col1) FROM tbl, what is the proper name for the field SUM(col1). The closest I have been able to determine is it is a computed column, is this correct or does it have another name?


texts: Can i pair multivalued attribute with an entity?

texts:
If i have the following scenario.

  Every doctor can diagnose a disease for different patients. A diagnose
  holds a date and disease(multi valued attribute). So the patient has
  testing that need to be done with investigation, the investigation
  holds type, description and title and testing holds date. For each
  investigation we know the disease that need to be investigated, hence
  one disease can have multiple investigations.

This is the sentence where i have difficulties

  For each investigation we know the disease that need to be investigated, hence one disease can have multiple investigations.

I have the following diagram so far. My question is can i connect multivalued disease with Investigation entity? I am new to EER diagrams


texts: Reliability of Scimore embedded database

texts:
I currently use SQLite for some applications that need a light embedded database. These apps use multiple processes that access the same database. For concurrent write access this is cumbersome. We have done some work-arounds to overcome this to some degree, but clearly SQLite has it limits.

So, we looked around and found an interesting, but very unknown, database: Scimore. It's quite capable; it's embedded version uses an out-of-process technique that allows multi-concurrency read and write access. Besides this, it's free. It's almost too good to be true.

From our first test results it seems to be very good and fast, but then why is it so unknown?

Therefore my question: Do you know this database; if so what are your experiences regarding reliability?

Scimore website

NB: I'm only asking for real usage experiences; not opinions.


texts: Unable to change thread_stack size MySQL Windows

texts:
I got the following error on MySQL 8.0 Windows on large recursive procedure:
Error Code: 1436. Thread stack overrun:  127944 bytes used of a 286720 byte stack, and 160000 bytes needed.
Use 'mysqld --thread_stack=#' to specify a bigger stack.

After doing hours of research on how to increase stack size.
Here are my findings:

there is no .cnf file anywhere;
there is a single my.ini file in the hidden C:\ProgramData\MySQL\MySQL Server 8.0\ but not in the Program Files\MySQL\MySQL Server 8.0\;
there is no thread_stack value in that .ini file;
there is no data folder in C:\Program Files\MySQL\MySQL Server 8.0\ but there is a data folder in C:\ProgramData\MySQL\MySQL Server 8.0\.
To execute mysqld commands in command prompt I had to create a blank data folder in the Program Files subfolder:

On executing the above command, there are two findings:

So, here's is what I did to increase it 4x:

I added a thread_stack=1120K line under [mysqld] in the my.ini file using notepad as administrator. (286720*4/1024 = 1120).

(on suggestion of a user but did nothing) added semicolons(initially not present) around the url after datadir in the same my.ini.

Now after I again ran the verbose --help command it still showed me the same stack size
So, I copied the 'my.ini' file from ProgramData into the C:\Windows\ folder as the mysqld statement searches that directory for config files. (as mentioned in the image)
Now when I ran the command again:

And here the size has increased. But, the main thing is after I use mysql -u root -p, connect with the server and use SELECT VARIABLE LIKE '%thread_stack%';

Here there is no change in the size.
Here I am stuck, how to do this?


texts: MongoDB document size for collection - impact on RAM and query performance

texts:
I have been advised on stackoverflow to ask this question on dba.stackexchange instead -

We are using MongoDB version 3.0 with WiredTiger storage.

As newbies to MongoDB, we may have designed our schema naively based on limited knowledge from various books and articles and want to improve the design for better performance.

One or 2 collections have an average object size of 52.3 KB and these collections may likely have millions of records eventually which we may shard. What I want to know is - what would be the impact on RAM if we query on the collection. Please note that the sizes of the documents will not grow much in time.

Eg - 1 document (with avg object size of 52KB) has 91 fields/attributes which include arrays and sub-documents. Say I am interested in around 5 fields in a particular query and I specify these fields in the projection argument- I have verified that appropriate indexes are being used on my query. Will mongoDB load only those 5-6 fields into RAM - the ones I am interested in - or the entire document with 91 fields and size 52KB. My question is for both -

Normal queries
Aggregation based queries

This will help in estimating my working set size.
And another thing, there are lots of other kinds of queries requiring different set of attributes on the same collection and documents - so covered query indexes may not be feasible for all of them.

Should I explore the possibility of splitting to different collections depending on usage patterns even though they may all be really 1:1 relationships? On the flip-side this will not guarantee atomic writes if lots of attributes are getting updated together.

The reason I ask is that I recently observed that geoNear aggregation queries are definitely faster if I trim down the collection to only a few essential attributes. I have a hunch that probably MongoDB may bring entire documents into RAM since it memory maps data files.


texts: InnoDB MySQL table with a trigger that inserts into a MyISAM table

texts:
Background

I have a Java web application that is used by 300 - 500 users at any given time.  On a separate Linux server we have a 5.7.19 MySQL Community Database Server that handles all the transactions for this application. We have a very active InnoDB table called "Requests".  This table is involved in many queries.  70% are select statements, 20% are updates, 10% are inserts.

On any CRUD operation against Requests, there is a trigger that does an insert into a different table called Audit.  That audit table is MyISAM.  We have having these very weird performance issues once or twice a day that are caused by Lock Wait Timeouts (at least that is what I see in the log file for Apache Tomcat).  So I wrote a shell script that dumps all the processes in MySQL whenever I see that entry in the Tomcat log file.  What I see is that there is a full table lock Requests.

Question

Can a InnoDB table with a trigger that does a insert into a MyISAM table cause a table level lock on the InnoDB table?