texts:
Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML

I now have a very nice looking Execution Plan display for SQL Server (as a result of my previous question), and am hoping that lightning could strike twice here and that I might find a similarly high-quality solution for Oracle.

My site SQL Fiddle displays execution plans for each of the queries that you run on it, for each of the database types I support (at the moment - MS SQL, MySQL, PGSQL, and Oracle).  The Oracle execution plan is built in this manner:

explain plan set STATEMENT_ID = '#schema_short_code#/#query_id#' for
select id, type, details from supportContacts

/

select
dbms_xplan.build_plan_xml(statement_id => '#schema_short_code#/#query_id#').getclobval()
AS XPLAN
FROM dual

Where schema_short_code and query_id are obviously variables representing each distinct query.  You can see the result of the above example running here: http://sqlfiddle.com/#!4/ee7da/596

This produces the following XML:

<plan>
  <operation name="SELECT STATEMENT" id="0" depth="0" pos="2">
    <card>2</card>
    <bytes>94</bytes>
    <cost>2</cost>
    <io_cost>2</io_cost>
    <cpu_cost>7501</cpu_cost>
    <time>00:00:01 </time>
  </operation>
  <operation name="TABLE ACCESS" options="FULL" id="1" depth="1" pos="1">
    <object>SUPPORTCONTACTS</object>
    <card>2</card>
    <bytes>94</bytes>
    <cost>2</cost>
    <io_cost>2</io_cost>
    <cpu_cost>7501</cpu_cost>
    <time>00:00:01 </time>
    <project>&quot;ID&quot;[NUMBER,22], &quot;TYPE&quot;[VARCHAR2,20], &quot;DETAILS&quot;[VARCHAR2,40]</project>
    <qblock>SEL$1</qblock>
    <object_alias>SUPPORTCONTACTS@SEL$1</object_alias>
    <other_xml>
      <info type="db_version">11.2.0.2</info>
      <info type="parse_schema"><![CDATA["USER_EE7DA"]]></info>
      <info type="dynamic_sampling">2</info>
      <info type="plan_hash">2812004906</info>
      <info type="plan_hash_2">2012527029</info>
      <outline_data>
        <hint><![CDATA[FULL(@"SEL$1" "SUPPORTCONTACTS"@"SEL$1")]]></hint>
        <hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
        <hint><![CDATA[ALL_ROWS]]></hint>
        <hint><![CDATA[DB_VERSION('11.2.0.2')]]></hint>
        <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]]></hint>
        <hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
      </outline_data>
    </other_xml>
  </operation>
</plan>

Although reading the raw XML is not terrible, it certainly does not look anywhere nearly as nice as what I now am able to show for SQL Server (compare by clicking "View Execution Plan" on this page: http://sqlfiddle.com/#!3/1fa93/1)

Has anyone worked with this XML before?  Is it possible that someone might have built something close to what Justin built for SQL Server (see answerer for the SQL Server question)?  If not, does anyone know where I can find some good direction for building my own XSLT (schema documentation, etc....), and possibly where I can look to see some nice example query plan visualizers for Oracle that I could hope to emulate?

Update

Since there are no answers yet provided, and the bounty is about to expire, I'll happily accept an answer that simply can tell me anything useful about this XML schema, or points me in the direction of any graphical execution plan display for Oracle (even desktop based GUIs).


texts: Setting DataGuardSyncLatency for Oracle Data Guard

texts:
With Oracle 12.2 there is a new parameter DataGuardSyncLatency when setting up Data Guard. I keep getting the warning:

Warning: ORA-16714: the value of property DataGuardSyncLatency is inconsistent with the member setting

What value should this parameter be for protection mode: MaxPerformance with phsyical standby?


texts: Migrate on-premise SQL Server databases to Azure SQL Database

texts:
We're in the process of a server migration from an on-premise server (Win2008R2) to Azure PaaS.

To move the databases, we used the Microsoft Data Migration Assistant (DMA) tool, which worked great and we can connect to the migrated Azure DB via SQL Server Management Studio.

Considering:

Made quite a few changes to the migrated Azure database (tables, SPs, indexes) to work with the apps in Azure
Combined multiple DBs from the on-prem server into one DB in Azure via DMA to save costs
One on-premise database is continually being modified by insert and update operations (multiple tables) during the migration process
When fully switching hosting in Azure we need to make sure all data is up-to-date. This will be done very early in the AM during which on-prem IIS websites are stopped assuring no DB operations are made, giving us a short window to migrate missing data

Question: What is the best and fastest way to migrate data (all vs missing/updated) considering the above?

Can this be achieved by following the below steps?

Create a new empty Azure SQL database (to later hold all the tables of 5 on-prem databases)
Via Microsoft Data Migration Assistant (DMA), migrate only the schema of that one on-prem database which is continually being modified by insert/update operations
Via SQL Data Sync Agent, set up auto data sync (e.g. every 30 minutes) between the on-prem and Azure database
After data sync up and running, via Microsoft Data Migration Assistant (DMA)- migrate schema and data of the rest of the on-prem databases (no data changes during migration process) into the single Azure database previously created and used for syncing

EDIT:

After enabling Data Sync in the Azure portal, the sync was unsuccessful due to the below logged errors:

Member Database: MyAzureDb -> Tombstone cleanup failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:18456, Message: Login failed.
Member Database: MyAzureDb -> Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:18456, Message: Login failed.

To fix 1 and 2 above tried: ALTER ROLE DataSync_executor ADD MEMBER [myUser]; but got Cannot add the principal 'myUser', because it does not exist or you do not have permission.

Member Database:MyOnPremDb: -> Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:325, Message: Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. SqlError Number:156, Message: Incorrect syntax near the keyword 'AS'.

The current compatibility level of the on-premise database is 80. Not sure what impact setting it to 100 will have to the production databse/apps.


texts:
I have some JSON data stored in a filters column. An example from one row:

name     | filters
---------+----------------------------------------------------------
John Doe | [{"Year":2004, "SportID":3}, {"Year":2005, "SportID":27}]

Other rows may have more than 2 objects, this is just an example.

I am trying to select data using a where clause which would iterate the JSON objects and act upon the value. For example, find me records where Year > 2004 or SportID = 24.

It seems like this should work:

SELECT name, filters from my_table
where json_extract(`filters`, "$[*].Year") = 2004;

However I get an empty result set. ♂

Of course I could just return the entire JSON object and parse it in app code, but I'd rather not do that if I don't have to.

UPDATE:
As requested, my show create table:

CREATE TABLE `my_table` (
  `userId` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `filters` json DEFAULT NULL,
  PRIMARY KEY (`userId`),
  UNIQUE KEY `my_table_userId_uindex` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: Export pump all schemas except Sytem related schemas

texts: I have around 111 schemas in one database instance and have to export all the schemas except the system related schemas to a new instance. Is there any way as to exclude system related schemas while using expdp as it would not be easy to write all the schema name in schema paramter of expdp.


texts: One database with two schemas: cross-schema View permissions issue?

texts:
I have a database on SQL Server 2008 R2 that has two schemas, the default dbo and a new one called Test.

I have a view in the Test schema called View1 that selects from dbo.Table1. Both schemas are owned by dbo.

I have a user called User2 that has SELECT permission to the Test schema.

User2 can login and see View1 but when it tries to select from it this error comes up:

The SELECT permission was denied on the object 'Table1', database 'Database', schema 'dbo'.

I thought since everything was owned by the same owner, ownership chaining would check the permission at entry point and allow the query to execute.

For kicks, I setup a similar example under a SQL Server 2012 Express database and it works fine.

Any ideas why this doesn't work?


texts: PL/pgSQL - Indexes vs Collation vs Pattern Ops

texts:
I'm using PostgreSQL 9.3.

Having already read articles and answers on DBA stackexchange:

Why would you index text pattern ops on a text column

PostgreSQL documentation: Indexes - Opclass

Pattern matching with LIKE/SIMILAR TO/Regular expressions

and being aware of EXPLAIN command I've crossed paths with performance issues in my DB.

As mentioned in documentation and answers above, it seems that when my database has a collation C

db=# SHOW LC_COLLATE;
 lc_collate
------------
 C

I do not need to use special operator ie text_pattern_ops on my indexes.

I've also tested how things work in my database and it seems that I:

have collate C
have table (assume it's named as table) containing column named signature of type text
have index on that table idx_table_sign btree (upper(signature))
also have index on that table idx_table_sign_like btree (upper(signature) text_pattern_ops)

Now let's assume I have a row with signature value I Lm 837/32.

EXPLAIN SELECT * from table WHERE upper(signature) = 'I Lm 837/32'

Does search using index

Index Scan idx_table_sign on table ...
Index Cond: ...

Which is OK considering our index, but let's see this:

EXPLAIN SELECT * from table WHERE upper(signature) LIKE 'I Lm 837/3%';

    Index Scan idx_table_sign_like on table ...
    Index Cond: (... ~>=~ ...) AND (... ~<~ ...)
    Filter: ... ~~ ...

Which seems good as well, but as I've read when I do have collation C i don't need to set text_pattern_ops indexes, so imagine this:

DROP INDEX idx_table_sign_like;

EXPLAIN SELECT * from table WHERE upper(signature) LIKE 'I Lm 837/3%';

Which only differs in operators (~>=~ is now simply >= and ~<~ is <)

    Index Scan idx_table_sign_like on table ...
    Index Cond: (... >= ...) AND (... < ...)
    Filter: ... ~~ ...

Now, thank you for reading this and getting so far, here are questions:

Do I need two indexes on this column with one using text_pattern_ops? Doesn't collate C do the trick? Also, is it possible that I have a table or a column that overwrites the collation of entire database, and because of that it might have been added? If yes, please tell me how do I check that.
Are the operations INSERT, UPDATE, DELETE slowed by this index (imagine I have 4 columns like this in a table containing 1 milion rows - main point is that those indexes need to be rebuilt (as far as I know), which may be unnecessary - this question is about telling me whether or not queries also use this index with text_pattern_ops instead of just C collation and if that may result in lower performance - you can see the EXECUTION PLANS with that index
Do queries like select ... where upper(signature) like 'xxx%' - left-anchored patterns use index idx_table_sign_like which is totally unnecessary considering my collation which they could be using instead? - please notice the difference in operators that planner is using depending on index (having it or not)
Added after Craig Ringer answer. In my columns I'm having text and varchar which contain polish characters. What does it change in my specific case? I hope this is some kind of a hint for the answerer on topic of neediness of those indexes.

These questions are very important, as my database is pretty huge (> 1TB) and has more than a dozen tables containing such indexes on several columns, on which inserts and updates come on a daily basis.


texts:
I'm looking for a way to query the last backup time for each database in Teradata 14.10. I am piecing together something with info from DBQL tables, but am unable to get anything that seems to be coherent. Does anyone know of a query to show th most recent backup for each database? Preferrably pulled from DBQL if possible. This is what I am currently using:

SELECT A.UserName, A.QueryID, B.SqlTextInfo, A.StatementType, A.LogDate
FROM PDCRINFO.DBQLogTbl_Hst A
INNER JOIN DBQLSqlTbl_Hst B
ON A.QueryID = B.QueryID
WHERE A.UserName = '<ARCMAIN user>'
AND A.LogDate = '<date of backup>'
AND A.StatementType = 'Release Lock'
ORDER BY B.SqlTextInfo ASC;


texts: Latitude and Longtitude Distance Calculation

texts:
I'm using MySQL and I am trying to calculate the distance in meters between two different longitude, latitude coordinates. I wrote a stored function in MySQL but it calculates just 1 for each row in select query. There is my stored function in the following segment:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `CALCULATE_DISTANCEE`(Lati varchar(20),Longi varchar(20),MyLat decimal, MyLon decimal) RETURNS int(11)
BEGIN
    declare Lat decimal(12,12);
    declare Lon decimal(12,12);
    declare RadiusOfWorld decimal(12,12);
    declare DistanceLat decimal(12,12);
    declare DistanceLon decimal(12,12);
    declare A decimal(12,12);
    declare R decimal(12,12);
    declare D decimal(12,12);
    declare DistanceAsMeter decimal(12,12);
    select CAST(Lati as Decimal(12,4)) into Lat;
    select CAST(Longi as Decimal(12,4)) into Lon;

    select 6378.137 into RadiusOfWorld;
    set DistanceLat = (MyLat - Lat) * PI() / 180;
    set DistanceLon = (MyLon - Lon) * PI() / 180;

    select SIN(DistanceLat/2) * SIN(DistanceLat/2) + COS(Lat*PI()/180) * COS(MyLat*PI() / 180) * SIN(DistanceLat / 2) * SIN(DistanceLat / 2) into A;
    Select (2*ATAN2(SQRT(A),SQRT(1 - A))) into R;
    select (RadiusOfWorld * R) into D;
    select D*'1000' into DistanceAsMeter;
RETURN DistanceAsMeter;
END

And also I am calling the function like this:

select *,`brain_db`.`CALCULATE_DISTANCEE`(stop.Stop_lat,stop.Stop_lon,'-157.818079','21.260340') AS Distance from stop;


texts:
I have an Oracle database (11.2.0.4) on Oracle Linux 6.7.

I want to backup this database with rman to a folder on a NAS (network attach storage).

How can I do this?


texts: How to get all dates belonging to a timestamp range

texts:
I have a tsrange as input. For example:

tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp)

I want to generate all dates belonging to this range.

I started with the following, probably naive, approach:

select generate_series(lower(r), upper(r), '1 day')::date
from   tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp) r;

This seems to work great, it gives:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
| 2019-03-06      |
+-----------------+

However, there is are exceptions which are not handled well. Take the following range as an example:

tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp)

This is a range from 2019-03-04 00:00 inclusive to 2019-03-06 00:00 exclusive. In other words, the date 2019-03-06 does not belong to this range.

When repeating the solution above, I get the following results:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
| 2019-03-06      |
+-----------------+

As you can see, 2019-03-06 is in the output, while it shouldn't.

I perfectly understand why this is (we asked for it in the generate_series call; it is not aware that 2019-03-06 is exclusive in our range).

A solution could be:

select generate_series(
         date_trunc('day', lower(r)),
         case when upper(r)::time = '00:00' then date_trunc('day', upper(r)) - interval '1 day'
              else                               date_trunc('day', upper(r))
         end,
         '1 day'
       )::date
from   tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp) r;

Now I get:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
+-----------------+

This seems to work fine. But this solution feels hackish. It also makes the assumption that the upper bound of the range is exclusive (which is the default). If the upper bound is inclusive, the original solution was fine. We can add tests on that:

select generate_series(
         date_trunc('day', lower(r)),
         case when not(upper_inc(r)) and upper(r)::time = '00:00' then date_trunc('day', upper(r)) - interval '1 day'
              else                                                     date_trunc('day', upper(r))
         end,
         '1 day'
       )::date
from   tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp, '[]') r;

Is something better possible?


texts: Pass array of mixed type into stored FUNCTION

texts:
I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.

the problem here is, that one argument is list of n arrays (basically list of items that were bought), so I have to make my FUNCTION eat array of arrays.

The solution given by Erwin Brandstetter from question Use array of composite type as function parameter and access it is basically what I need, except one thing. My target table has to have also column: id SERIAL PRIMARY KEY

I have been trying to modify it slightly:

This is target table:

CREATE TABLE cashreg_journal (
  id SERIAL PRIMARY KEY,
  gid INTEGER NOT NULL,
  device_id TEXT NOT NULL,
  item TEXT NOT NULL,
  vat_percentage INTEGER NOT NULL,
  vat_sum INTEGER, price NUMERIC);

This is function:

CREATE OR REPLACE FUNCTION test_insert(daco TEXT, VARIADIC _journal_arr cashreg_journal[]) RETURNS TEXT
  LANGUAGE plpgsql
  AS $function$

DECLARE

  jr cashreg_journal;

BEGIN

  FOREACH jr IN ARRAY _journal_arr LOOP
    INSERT INTO cashreg_journal(gid, device_id, item, vat_percentage, vat_sum, price)
    SELECT jr.*;

  END LOOP;

  RAISE NOTICE 'Daco: %', daco;

  RETURN 'Saved';

END;
$function$;

but it is not working this way:

SELECT * FROM test_insert('Test daco', '(1,000-111,Test-P,20,20,100)', '(1,000-111,Test-F,10,10,100)');
ERROR:  invalid input syntax for integer: "000-111"
LINE 1: SELECT * FROM test_insert('Test daco', '(1,000-111,Test-P,20...
                                               ^

so it seems, that I do not fully understand how it works and my mind ways are not right.

Could anybody please give me some hint, to point me in the right direction? :)

Edit:
I am working on many machnes, however the postgres version is always 9.4, 9.5 or 9.6.
I have also forget to mention that what I like about given solution is, that I do not need to create new type. Which I try to avoid.


texts:
I have two fields in my table : A int, B int. I want to add two computed fields (CF1 & CF2) that uses these two fields A & B.

CF1 = (case when [A]>[B] then (CF1=3) else case when [A]<[B] then (CF1=0) else (CF1=1) end end)

CF2 = (case when [A]<[B] then (CF2=3) else case when [A]>[B] then (CF2=0) else (CF2=1) end end)

This works fine, but when the two fields (A & B) are NULL, CF1 and CF2 are = 1. How do I avoid this? I mean when either one of the fields A or B is NULL, both CF1 and CF2 should also be NULL.

In other words: there should only be a value of 0, 1, or 3 in CF1 and CF2 if there is a value NOT NULL in fields A & B.

+------+------+------+------+
|  A   |  B   | CF1  | CF2  |
+------+------+------+------+
| 2    | 3    | 0    | 3    |
| 5    | 2    | 3    | 0    |
| 2    | 2    | 1    | 1    |
| NULL | NULL | NULL | NULL |
| NULL | 1    | NULL | NULL |
| 1    | NULL | NULL | NULL |
+------+------+------+------+


texts: Master Key vs Password Certificate Encryption in SQL Server

texts:
We can encrypt certificates in SQL both by the database master key, and the other option is by password. I want to know the advantages and disadvantages of DMK encryption. Does password usage make the certificate more secure?

thanks


texts: Does a read apply locks when run outside of a transaction?

texts: If implicit_transactions = off and I execute a SELECT query outside of an explicit transaction, will any locks be applied or do locks only make sense as part of a transaction?


texts: SSMS 2012 - loaded saved query not executable ("invalid object name" error)

texts:
I have multiple saved queries (each one was working fine when I developed them).
But when I load them in SSMS they immediately show error in every table name that's in the script. Moreover, I can't select the relevant DB from "Available databases" box (or any DB on that server, for that matter) and USE [DB_NAME] command does not work, too.

However, if I'll navigate in "Object Explorer" to any table in any DB on the Server I want and do even a simple Select Top 1000, then paste in whole query that's not working - voila - it runs fine.

Is it important for this that I'm connected to multiple SQL servers on different machines at the same time? In case it's needed - all SQL Server 2012.

Does anyone have a thought as to what's happening? It's extremely annoying to go through all the above steps each time I need a query ran. Thanks!


texts: Log Shipping Copy Job Fails once in a while

texts:
I have configured log shipping at multiple database server with monitored server(separate server) and all of them are running fine without any issues.

One particular server has 7 database and the log shipping copy job fails once in a while for all databases, when I checked the error message, every time error remains same:

*** Error: The network path was not found.
(mscorlib) ***

This issue is not persistent and occurs at random time and at any given day(not every day). Below is details of log shipping schedule:

Log backup job is scheduled to run every 15 minute at primary
server(file is generated at monitoring server).
LS Copy job is scheduled to run every 15 minute at secondary
    server, at the gap of 5 minute from log backup job.
LS restore job is scheduled to run at 15 minutes at secondary
    server, at the gap of 5 minutes from LS copy job.

Above jobs are having delay of 5 minutes considering network speed and file size.

Can anyone please suggest what could be the underlying issue.

For time being, I have asked Infra admin to add server name along with its IP in the hosts file at secondary server, don't know if this will solve the issue.

Note: Version of Operating System and Database is same at primary, monitoring and secondary server.

Job failed again for all databases pertaining to the same server and file didn't get copied. No details on the error message and very next run which was scheduled 15 minutes later got succeeded. I have no clue on this.

I think there is something wrong with server setting. Any input will be highly appreciated.


texts:
I have 2 tables: records and access_rights

Access table has list of permission for certain and should be used to filter records based on rules:

function field is mandatory
object field is optional. if it`s empty do not apply filter else filter by value
All rules should work in one query. Please see example of expected output and SQL Fiddle data.

| ID |   FUNCTION |   OBJECT | SUM |
|----|------------|----------|-----|
|  1 | Function 1 | Object 1 | 900 |
|  4 | Function 1 | Object 2 | 750 |
|  5 | Function 2 | Object 2 | 200 |

Thanks in advance!

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE records (
  [id] int NOT NULL PRIMARY KEY,
  [function] varchar(100),
  [object] varchar(100),
  [sum] money
  );

INSERT INTO records VALUES
(1, 'Function 1', 'Object 1', 900),
(2, 'Function 2', 'Object 1', 600),
(3, 'Function 3', 'Object 1', 800),
(4, 'Function 1', 'Object 2', 750),
(5, 'Function 2', 'Object 2', 200),
(6, 'Function 3', 'Object 2', 500);

CREATE TABLE access_rights (
  [id] int NOT NULL PRIMARY KEY,
  [user] varchar(50),
  [function] varchar(100),
  [object] varchar(100)
  );

INSERT INTO access_rights VALUES
(1, 'User', 'Function 1', ''),
(2, 'User', 'Function 2', 'Object 2');

Query 1:

SELECT * FROM records WHERE
[records].[function] in
(SELECT "function" FROM access_rights where "user"='User'  )

Results:

| ID |   FUNCTION |   OBJECT | SUM |
|----|------------|----------|-----|
|  1 | Function 1 | Object 1 | 900 |
|  2 | Function 2 | Object 1 | 600 |
|  4 | Function 1 | Object 2 | 750 |
|  5 | Function 2 | Object 2 | 200 |


texts: Is my SQL Job causing the High Disk usage?

texts:
I have a concern regarding High Average disk queue length for one of my server with below configuration:

Version: SQL 2005 with SP3, standard edition.

windows: version 2003 r2 SP2

No Processors= 4 and RAM = 4GB

Databases = 19 with data size approx 7 GB

Drives: C with RAID 1+0 and Drive E only

I am continuously getting the alerts for High AVg disk queue length ( Most of the times value is above 8 and at peak goes 40 -100)

Analysis:: I did research and look for heavy queries but could not come across any per Idera and other monitoring methods

However i am unsure of one of my sql job which runs a SP.. This Job runs every 15 minutes. This SP contains queries with lots of inserts and updates which i believe to be the cause of high writes.

I believe this to be the cause, but i am really unsure how to proove on this or in case something else like i believe 4GB ram looks quite a less!

Kindly help on how to analyse and come to a solution.


texts:
I've discovered that my MySQL server is 10 minutes behind the current time because NTP was not running for some reason.

It is a single node and running MySQL version 5.6.15-56.

I know that changing time can affect replication in clusters but does it have any effect on single node servers?


texts:
I'm looking for an open source app to backup sql server / mariadb / postgres.
Do you know which app can do that ?
I want to schedule backups with the app.
yes it's possible with only one app . I used to schedule backups for my local databases servers ( SQL Server, mariaDB, Postgres) on SQLServerBackupAndFTP (it's an app) but unfortunately it's not free. that's why i'm looking for an open source tool.
Thank you guys.


texts:
I have a large SQL Azure database (P6 nearing 1TB in size). I want to do a cleanup/removal of any unused indexes. For the past 30 days, we've captured the 2 following sets of information.
See: https://gist.github.com/eoincampbell/3fe775d43e86ad342f9c6eba10f350f9

Index Stats gathered from sys.dm_db_index_physical_stats joined to sys.tables, sys.schemas and sys.indexes
Index Usage gathered from sys.dm_db_index_usage_stats

I have a small concern around the accuracy of sys.dm_db_index_usage_stats. It's not clear from the documentation when/if the following occurs in a SQL Azure environment (as compared to a single instance MSSQLServer.)

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=azuresqldb-current The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

Here's the query I'm using to subsequently identify the unused indexes. It

Gets the latest index information for all indexes in the database (812 records)
Gets the latest usage information for all indexes (558 records)
LEFT OUTER JOINs them together
Excludes any Clustered/PK Indexes
Returns anything with no usage stats OR anything with zero user-read stats.

The total returned row count is ~219 rows
Does this approach seem valid?
Query

WITH MostRecentStats (
    SchemaName, TableName, IndexName, IndexType, AllocUnitType, Pages, MostRecentAt
)
AS (
    SELECT      SchemaName, TableName, IndexName
                , IndexTypeDescription, AllocUnitTypeDescription
                , Max(PageCount) , Max(RecordDate)
    FROM        DBStats.IndexStats
    GROUP BY    SchemaName, TableName, IndexName
                , IndexTypeDescription, AllocUnitTypeDescription
    -- ****** Returns 812 Indexes across all tables ******
)
, AllCombinedUsage (
    SchemaName, TableName, IndexName
    ,user_seeks, user_scans, user_lookups, user_updates
    , system_seeks, system_scans, system_lookups, system_updates
)
AS (
    SELECT      SchemaName, TableName, IndexName
                , sum(user_seeks), sum(user_scans), sum(user_lookups), sum(user_updates)
                , sum(system_seeks), sum(system_scans), sum(system_lookups), sum(system_updates)
    FROM        DBStats.IndexUsage
    GROUP BY    SchemaName, TableName, IndexName
    -- Only Returns 558 Index with Usage Statistics...
)
SELECT      a.SchemaName, a.TableName, a.IndexName, a.Pages
            , b.*
FROM        MostRecentStats a
LEFT JOIN   AllCombinedUsage b
            ON a.SchemaName = b.SchemaName
            AND a.TableName = b.TableName
            AND a.IndexName = b.IndexName
WHERE       a.IndexName NOT LIKE 'PK_%' --Filter out all Primary Keys
AND         a.IndexType <> 'CLUSTERED INDEX' --And Clusted Indexes
AND (
            b.IndexName IS NULL --Include everything that has no index usage data
            OR
            (b.user_seeks + b.user_scans + b.user_lookups) = 0
            --Include everything with 0 User Reads on the data
)
ORDER BY    a.Pages DESC


texts: Is there any option to kill the particular process automatically if it is consuming a particular value of cpu?

texts: some sort of sql process makes my database server to consume 100% of the cpu, Is there any option to kill the particular process automatically if it is consuming a particular value of cpu ?


texts: sp_spaceused reveals 0 rows but 72 KB of reserved space

texts:
When I run sp_spaceused dummybizo I get the following results:

name        rows    reserved    data    index_size  unused
dummybizo   0       72 KB       8 KB    8 KB        56 KB

I am expecting the following results:

name        rows    reserved    data    index_size  unused
dummybizo   0       0 KB        0 KB    0 KB        0 KB

What is causing the reserved memory?

I have already tried to force a ghost record clean up with alter table dummybizo rebuild; but it doesn't change the results. I've also tried truncate table dummybizo but it doesn't do anything to these stats. I also note that select * from dummybizo with (nolock) doesn't reveal any hidden records.

What should I try next? I would like to set up the table such that the stats read 0   0 KB   0 KB   0 KB   0 KB


texts:
If a stored procedure has multiple statements, will the lock acquired for the first statement be released once its executed or will it hold on till entire stored procedure is complete?

I am running a stored procedure under transaction from Entity Framework. Invoking the same stored procedure multiple times simultaneously is causing deadlock. The stored procedure selects a bunch of records from a table and inserts back into same table and moves on to next table. For some tables, it makes join with previous table to get the list of records to be inserted.

The stored procedure doesn't have any transaction but the call from application routed via Entity Framework has a transaction


texts: I'm unable to track a stored procedure that is executing every morning 7 AM. Is there is any script to check that which job invokes that particular SP?


texts:
I Have normal InnoDB table with autoincremental primary key. There Is about 100 thousands records. Accidentally was saved record with ID 10 million. Other record continue in this row. Is any possible And safe solution how:

A) change some IDS (row started from 10 millions) to max ID from less row? Off course Is necessary to change foreign IDS too.
B) start to fill gap between 100 thousand to 10 million?

I would want it because of better readability.


texts: Create a MySQL Database using a MS SQL database dump (.CSV)

texts:
I have a dump of a MS SQL database in the form of CSV files for every table in the database. There are 679 .csv files each for every table. The highest size of the table extract is 9 GB and the lowest is 12 bytes.

I want to replicate the entire database on MySQL keeping all the table structure and relationships intact. The purpose is to replicate the database on MySQL so I can query the results when required.

How can I do that please?

Many Thanks,
Jitendra


texts: Permission error when restoring MsSQL database with full text on Linux

texts:
I'm trying to restore a .bak file created on Windows SQL Server to the Linux version of MSSQL, but I get an error like the following:
Msg 7610, Level 16, State 1, Line 112
Access is denied to "/var/opt/mssql/data/sysft_Catalog.ndf", or the path is invalid.

    Msg 3156, Level 16, State 50, Line 112
File 'sysft' cannot be restored to '/var/opt/mssql/data/sysft.ndf'. Use WITH MOVE to identify a valid location for the file.

    Msg 3119, Level 16, State 1, Line 112
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

    Msg 3013, Level 16, State 1, Line 112
RESTORE DATABASE is terminating abnormally.

The command I'm running is the following (or one of many variants I've tried):
RESTORE DATABASE Imported_Database FROM
DISK = '/var/opt/mssql/backup/database_to_import.bak'
WITH
  MOVE 'Dat' TO '/var/opt/mssql/data/Dat.mdf',
  MOVE 'ftrow' TO '/var/opt/mssql/data/Catalog.ndf',
  MOVE 'Log' TO '/var/opt/mssql/data/Log.ldf',
  MOVE 'sysft_Catalog' TO '/var/opt/mssql/data/sysft_Catalog.ndf',
  STATS = 5 -- REPLACE, PARTIAL, etc.
GO

The database is hosted on Ubuntu 20.04.1 LTS in a container created by docker-compose with:
version: "3.2"
services:
  sql-server-db:
    container_name: mssql
    image: mcr.microsoft.com/mssql/server:2019-latest
    ports:
      - "1433:1433"
    environment:
      SA_PASSWORD: "{{ PASSWORD }}"
      ACCEPT_EULA: "Y"
    volumes:
      - ./imports:/var/opt/mssql/backup:ro

What I've noted is that:

The error message is incorrect, as the path of the restore is writeable (i.e. touch /var/opt/mssql/data/sysft_Catalog.ndf as user mssql works fine)
Other databases restore fine, notably if they don't have full text search
The .bak file does restore to MSSQL running on Windows
Importing, then re-exporting from Windows to a new backup doesn't solve the issue
Creating an empty database and restoring to that empty database doesn't help
Creating the files in advance doesn't help
The database would not restore to an Azure SQL Server ("unknown error") or to Google Cloud SQL (same)
The exact same failure occurs when MSSQL is installed directly on Ubuntu (i.e. without Docker)
The MSSQL errorlog seems to have no additional information
Copy Database from Windows to Linux in SSMS doesn't work
Installing mssql-server-fts doesn't help

Some links to seemingly related issues:

https://github.com/docker/for-win/issues/7259
https://support.microsoft.com/en-my/help/4057087/kb4057087-can-t-restore-database-with-replace-if-filepath-has-path-sep
Restore database in docker container


texts: InfluxDB: selecting last() values from different tags

texts:
I have a bucket with the raw data of several sensors, things like temperature, air pressure and etc. The sensors don't send their data at the same time, which means that a given timestamp might have several data points from various sensors or just from just one.
Each reading is tagged with the ID of the sensor it comes from. I need to get the Last() reading from a given number of IDs. When I try this code:
select last(Temperature) from raw_measure where ID =~ /4372502|4399699|4406512|4407840/
instead of returning the last Temperature observation of each ID, it returns the most recent temperature timestamp of the whole group.
How can I get the last reading of each ID in a single query?


texts:
I have two mySQL queries. Individually, they give me what I'm looking for. However, I prefer to combine the results into a single set.

First query. Simple selection from table 'profile'.

SELECT id, first, last, TIMESTAMPDIFF(YEAR, dob, NOW()) AS age
FROM profile WHERE active = 'Y'

Results:

|id               |first    | last | age|
|160808213759VYO  |Peter    |Pan   | 31 |
|160808214828NGO  |Lion     |King  | 30 |
|160808215315TRA  |Buck     |Roger | 29 |

Second query. For a given id there are multiple entries (description). I
want to pick out the first entry for a given id only and it's working correctly.

SELECT r.id, (SELECT r1.description FROM reference r1
WHERE r1.id = r.id AND r1.active = 'Y' ORDER BY r1.description ASC LIMIT 1)
AS image
FROM reference r WHERE r.type = 'I' AND r.active = 'Y'
GROUP BY r.id
ORDER BY r.id, image;

Result:

|id             |image                         |
|160808213759VYO|   img/160808213759VYO_003.jpg|
|160808214828NGO|   img/160808214828NGO_002.jpg|
|160808215315TRA|   img/160808215315TRA_001.jpg|

How can I join these two queries to get this:

|id             |first   |  last |  age|   image                       |
|160808213759VYO|   Peter|  Pan  |  31 |   img/160808213759VYO_003.jpg |
|160808214828NGO|   Lion |  King |  30 |   img/160808214828NGO_002.jpg |
|160808215315TRA|   Buck |  Roger|  29 |   img/160808215315TRA_001.jpg |

Any help will be much appreciated.


texts: Improving slow queries. Slow query log attached

texts:
Below is the Slow Query Log when my .NET application was running. Please let me know how to improve the performance of the queries:

TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 110805 13:25:39
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.015625  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1373
use stockist;
SET timestamp=1312530939;
SELECT SUM(GROSSAMOUNT) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1373
SET timestamp=1312530939;
SELECT COUNT(BILLNO) FROM BILLDETAILS WHERE MONTH(BILLDATE) = 8 AND YEAR(BILLDATE) = 2011;
# Time: 110805 13:30:32
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.078125  Lock_time: 0.046875 Rows_sent: 1  Rows_examined: 1374
SET timestamp=1312531232;
SELECT A.BILLNO AS BILL_NO, DATE_FORMAT(A.BILLDATE,'%d/%m/%Y') AS BILL_DATE, B.PARTYNAME AS PARTY_NAME, A.NETAMOUNT AS NET_AMOUNT FROM BILLDETAILS A, PARTYMASTER B WHERE A.PARTYID = B.PARTYID AND MONTH(A.BILLDATE) = 8 AND YEAR(A.BILLDATE) = 2011 ORDER BY A.BILLNO, A.BILLDATE;
# Time: 110805 13:30:44
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.296875  Lock_time: 0.031250 Rows_sent: 407  Rows_examined: 19552
SET timestamp=1312531244;
select a.itemcode as Item_Code, a.itemname as Item_Name, a.stockinhand as Stock_In_Hand, b.mrp As MRP, round((a.stockinhand * b.mrp),2) As Value, date_format(b.invoicedate,'%d/%m/%Y') As Stock_Date from itemmaster a, stockentry b where a.itemid = b.itemid and invoicedate = (select max(invoicedate) from stockentry where itemid = b.itemid) group by a.itemname order by a.itemname;
# Time: 110805 13:30:55
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1312531255;
SELECT * FROM REMARKSETTINGS;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1312531255;
SELECT * FROM BILLINGSETTINGS;
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.015625  Lock_time: 0.015625 Rows_sent: 1  Rows_examined: 1373
SET timestamp=1312531255;
SELECT MAX(BILLNO) FROM BILLDETAILS WHERE YEAR(BILLDATE) = 2011;
# Time: 110805 13:30:58
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.031250  Lock_time: 0.000000 Rows_sent: 184  Rows_examined: 3266
SET timestamp=1312531258;
SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE FROM ITEMMASTER A, STOCKENTRY B WHERE A.ITEMID = B.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID) AND A.STOCKINHAND > 0 AND B.SALEPRICE > 0 AND B.INVOICEDATE IS NOT NULL ORDER BY A.ITEMNAME, B.INVOICEDATE;
# Time: 110805 13:31:21
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.031250  Lock_time: 0.000000 Rows_sent: 184  Rows_examined: 2898
SET timestamp=1312531281;
SELECT B.RECORDID, A.ITEMCODE, A.ITEMNAME, A.STOCKINHAND, B.SALEPRICE FROM ITEMMASTER A, STOCKENTRY B WHERE A.ITEMID = B.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID) AND A.STOCKINHAND > 0 AND B.SALEPRICE > 0 AND B.INVOICEDATE IS NOT NULL AND A.ITEMNAME LIKE '%';
# Time: 110805 13:36:31
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.250000  Lock_time: 0.000000 Rows_sent: 407  Rows_examined: 20367
SET timestamp=1312531591;
select c.categoryname, a.itemcode as Item_Code, a.itemname as Item_Name, a.stockinhand as Stock_In_Hand, b.mrp As MRP, round((a.stockinhand * b.mrp),2) As Value, date_format(b.invoicedate,'%d/%m/%Y') As Stock_Date, D.TradeName, D.OwnerAddress, D.OwnerTinNo from itemmaster a, stockentry b, categorymaster c, OwnerDetails D where a.itemid = b.itemid and a.categoryid = c.categoryid and invoicedate = (select max(invoicedate) from stockentry where itemid = b.itemid) group by a.categoryid, a.itemname order by a.itemname;


texts: How to list query which used an index while selecting result in Postgres?

texts:
I know that Postgres has some utility extensions that collect stats for statements and indexes.
I want to list queries which used particular index, whichselectsg the result. Is there a way to get that result set?


texts: Oracle Universal Installer: Install completes, but in (Not responding) state

texts:
I am running the universal installer for 64-bit ODAC 12c Release 1 (12.1.0.1.0) for Windows x64 on Windows Server 2012 R2 box. Screenshot of Oracle version below.

I have completed the installer properly many times, but this time I have run the installer with correct parameters (this has been a repetitive task) and the installer completes, except in (Not Responding) state.

I have checked the logs here C:\Program Files\Oracle\Inventory\logs and the last line looks as my other successful installs.

INFO: no. of sets of tools to be run: 0
INFO: ca page to be shown: false
INFO: exitonly tools to be excuted passed: 0
INFO:
*** End of Installation Page***
The installation of Oracle Data Access Components for Oracle Client was successful.

Did this in fact mean that Oracle has installed correctly?


texts: Inplace upgrade from SQL Server 2008 Standard to SQL Server 2014 evaluation

texts:
I am trying to evaluate SQL Server 2014 by using the 2014 evaluation edition to upgrade an in place SQL Server 2008 Standard sp4 instance.

At the end of the process I expect to have a SQL Server 2014 evaluation version running.

I am getting an error that says I cannot upgrade features.

Is there a migration path that I can use?


texts: error recovering pg_dump compressed file

texts:
I produced this backup:

pg_dump -v -Fc -Z5 -f thebackup.dmp.gz thedb

I can't open the dump with gzip, 7zip... I could recover the same files in the past without using -Fc parameter. Is it possible that using -Fc and -Z5 might have corrupted the compressed file?


texts:
Suppose I have a relation R=(A,B,C,D) and I decompose it into:

R1=(A,B,C)
R2=(A,D)

then if it is said that the decomposition is lossy,then what is the meaning of that statement?I have an understanding that the decomposition is lossy iff:

R1 ∪ R2=R

But can't see the meaning of this here because schemas are not same so we cannot take union.


texts: I need to create SYNONYM with name 'END'

texts:
Using EnterpriseDB I am not able to create/drop SYNONYM with name 'END'

DROP SYNONYM END;
CREATE SYNONYM END FOR TABLENAME;

I am getting similar issue while creating table with column name REFERENCES:

create tab1
( name character varying(250),
REFERENCES character varying(250) )

I am converting the table from Oracle to Postgres (EntrepriseDB). In Oracle (END, REFERENCES) are used. Hence I need to use the same words here, too. Is there any way to use the reserved key words as synonyms name and column name?


texts: What system information is stored in the primary file in SQL Server?

texts:
Suppose I have created a new database in SQL Server with a file group FG1 and marked this as the default. All my user tables etc are now created in FG1. However I also created a file that is part of the PRIMARY file group. Apparently the system objects are stored in this primary file. I was wondering

What kind of system objects are these? Can I inspect them? Do they appear in the 'System' Database?

Apparently there is some reliability gain to doing things this way. Suppose the file in my primary file group got corrupted, can I still recover my database?


texts: Update column with the sum of column in previous rows in MySQL

texts:
We have a table, essentially:

id, amount, end balance, account_id

I now want to re-calculate the end balance values (because of possible corruption)

End balance is basically "end balance of previous entry with the same account_id" + amount.

Assume that there are a lot of entries, but I want to do this for one account at a time (or for a couple of accounts).

(So, if we have the corrupt table:

Id  Amount Balance Account
1     100       0     1
2     100     100     2
3     -40     -40     1
4     100      60     1

I want the resulting db table to be:

Id  Amount Balance Account
1     100     100     1
2     100     100     2
3     -40      60     1
4     100     160     1

)


texts: Save Transaction: Is the name local to the stored procedure or not?

texts:
Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused.

There is a subset of stored procedures that preserve state on failure. That is, if the procedure raises an error, it anyway can be committed safely. This is done with save transaction.

Now, my initial understanding was that the savepoint name you set for the transaction is local to the stored procedure that uses it. And in a nested scenario, if the inner stored procedure saves under the same name, it is actually a different name, and all is fine.

To make sure this understanding of mine was correct, I set up a test case:

create table dbo.[footest] (
  v varchar(50) NULL
);
go

insert into dbo.footest(v) values ('Nothing'); go

create procedure dbo.[foo_test_tran_inner]
as
begin
  set nocount on;

  declare @foo int;

  begin tran;

  update dbo.footest set v = 'Inner, before savepoint';

  save tran the_constant_name;

  begin try
    update dbo.footest
    set v = 'Inner, after savepoint';

    set @foo = 1/0;
  end try
  begin catch
    rollback tran the_constant_name;
  end catch;

  commit tran;

  set @foo = 1/0;

  return 0;
end;
go

create procedure dbo.[foo_test_tran_outer]
as
begin
  set nocount on;

  begin tran;

  update dbo.footest set v = 'Outer, before savepoint';

  save tran the_constant_name;

  begin try
    update dbo.footest
    set v = 'Outer, after savepoint';

    exec dbo.foo_test_tran_inner;
  end try
  begin catch
    rollback tran the_constant_name;
  end catch;

  commit tran;

  return 0;
end;
go

begin tran;
exec dbo.foo_test_tran_outer;
commit tran;
select * from dbo.footest;

This yields "Outer, before savepoint". Which means, the savepoint names were local to the procedure and were properly rolled back in a nested scenario.

In production, those state-preserving stored procedures have exactly this schema. There is much more code in them, but if you remove it, only leaving saves, rollbacks and commits, it will be exactly what is shown above.

But. It didn't work in production. Instead, each nested savepoint with same name appeared to overwrite a previous savepoint, made from the calling procedure. And when the outermost code after receiving an exception executed a commit (believing the inner rollbacks are properly done), the database was left in a half-screwed state. If applied to the example above, that would mean the select returns Inner, before savepoint.

I stepped through every step in the production procedure with a debugger, and confirmed that execution properly went through all expected save tran a_name and rollback tran a_name.

To fix the problem in production, I replaced this:

save tran the_constant_name;
...
rollback tran the_constant_name;

with this:

declare @savepoint varchar(32) = replace(newid(), '-', '');
save tran @savepoint;
...
rollback tran @savepoint;

and that fixed it immediately.

So what gives? Is the savepoint name local to the stored procedure or not?
If it is, then why the production code did what it did, and was successfully fixed as shown?
If it is not, then why the test example above does what it does?


texts: Designing a database structure for Partners and Websites

texts:
I have two tables, Partners and Websites. I also have an "associative" table called Partners_Websites that retains the relationships "between" the rows of the tables Partners and Websites. In the Partners table, there are rows that I want to relate to all rows in the Websites table (even rows that can be added in the future).

Let's say I have P1, P2, P3 and W1, W2 and W3:

I want P1 related to W1 and W3
P2 related to W1 and W2
P3 related to all Websites

Then the Partners_Websites table will look like:

P1, W1
P1, W3
P2, W1
P2, W2
P3, W1
P3, W2
P3, W3

The question

If I add W4, I will need to add a new row in the Partners_Websites table to relate it to P3. How can I define "P3 is related to all Ws" without requiring the Partners_Websites table? I was thinking in adding a "flag" in Partners (that is, a boolean column named is_related_to_all) but I think it looks wrong (I may end up with hundreds of is_related_to_all = false).

Is there any way of doing this using relationships?


texts: Get the rank of a row based on a score I am generating from other field values

texts:
I am trying to figure out how I can get the rank of a row from a score I am generating from other field values.

The score query looks something like this

SELECT ((table2.field1*0.4) + (table2.field2 * 0.2) + (table1.field1*0.7)) 'score' FROM `table1` LEFT JOIN table2 ON table2.table1_id=table1.id GROUP BY table1.table1_id ORDER BY `score` DESC

This generates my scores, they look something like this

|4363.195
|3970.4210000000003
|2781.8
|2601.2980000000002
|2508.905
|2269.829
|2204.1
|1893.367
|1789.575

I am trying to get rank of each provider in a query like this

SELECT rank_here, table2.* FROM `table2` WHERE 1

This is just sample stuff I am just trying to figure out how to do this(this is why I have also included the score being generated from 2 tables, but it really doesn't matter it can be from a single table or even more than 2)

I researched :
Get the rank of a user in a score table

But all I found is how to get the rank if I have the score in a field, and in my case I need to make it based on other field values.


texts: Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assembly

texts:
The login running all these commands is a member of sysadmin server role on a SQL 2012 Developer instance. It is the owner of the DB where this is being deployed to. EXTERNAL ACCESS ASSEMBLY permission has been granted to the login in this DB. Also tried everything below as the sa login to no avail.

alter database test set trustworthy on
go
create assembly [icsharpcode.sharpziplib]
from 'C:\Workspace\111\icsharpcode-SharpZipLib-4f2d664\bin\Release\ICSharpCode.SharpZipLib.dll'
with permission_set = UNSAFE --< This works!
go
create assembly OutOfRowCompression
from 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'
with permission_set = UNSAFE --< This fails!
go

The last command fails with:

  Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly
  'OutOfRowCompression' failed because assembly 'OutOfRowCompression' is
  not authorized for PERMISSION_SET = UNSAFE.  The assembly is
  authorized when either of the following is true: the database owner
  (DBO) has UNSAFE ASSEMBLY permission and the database has the
  TRUSTWORTHY database property on; or the assembly is signed with a
  certificate or an asymmetric key that has a corresponding login with
  UNSAFE ASSEMBLY permission.

After getting the error I signed the assembly with a new asymmetric key key1.pfx and added it to the master DB:

CREATE ASYMMETRIC KEY key1
FROM executable
FILE = 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'

Verified that the key exists:

Created a login from this key and granted God mode:

create login clr_key1 from asymmetric key key1
go
GRANT EXTERNAL ACCESS ASSEMBLY TO clr_key1
go

Still the same error from the assembly creation command.

Tried building the assembly OutOfRowCompression with SAFE and UNSAFE access in Visual Studio 2012 - no difference.

I feel I have satisfied all requirement presented by the error message. It should work. What am I missing?


texts:
DB: Postgres 9.4

schema:

table a:
id, score

table ab:
a_id, b_id, b_type

table b:
id, name, type

I am trying to get top 10 result in the following format:

name, count(group by name), average(score)

count is the total occurrences of a group by name

Basically I can do a 3 table join:

select name, count(*), average(score)
from a
joins ab
on a.id = ab.a_id
joins b
on b.id = ab.b_id
where type = 'sometype'
group by name
order by count(*) desc limit 10

This works, but the performance is very slow since my middle table ab is like 200M record, and the index is pretty large as well.

Fortunately I can filter on the table ab and make the result to around 1000 rows.

select filtered_ab.b_id, count(1) as ct
from (
  select b_id
  from ab
  where b_type = 'sometype'
) as filtered_ab
group by filtered_ab.b_id
order by ct desc limit 1000

This will give me the top 1000 b_ids.

Now I only have to joins table b with this result to append name column to it.

select name, b_id, ct
from b
join (
select filtered_ab.b_id, count(1) as ct
from (
  select b_id
  from ab
  where b_type = 'sometype'
) as filtered_ab
group by filtered_ab.b_id
order by ct desc limit 1000
) as b_with_counts
on b_with_counts.b_id = b.id

I am returning top 1000 is because currently name and b.id is not 1 to 1, same name can have like 1 to 5 b.ids, so just return 1000, and then group by name later to approximately get all the counts for the same name.

But after this, I cannot figure out a way to append the average score from table a.

Currently the data in table ab, for each b_id, there can be 1 to 1000000 or more a_ids.


texts: Constraint to prevent overlapping ranges in an array field in PostgreSQL

texts:
If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, for example, I can use:

CREATE TABLE appointment (
    schedule tstzrange,
    EXCLUDE USING gist (schedule WITH &&)
);

Is there a way to get this same behaviour, but for the values in an array field of type tstzrange[] in a single row?

As an explicit example, I want to be able to define a table test with a constraint that lets these successive inserts both succeed, because in each single array the two ranges don't overlap:

INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-01, 2016-02-01)", "[2016-03-01, 2016-04-01)"}');
INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-15, 2016-02-07)", "[2016-02-29, 2016-03-15)"}');

but such that this insert fails regardless of what other rows exist, because in this array the second range overlaps the first:

INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-01, 2016-02-01)", "[2016-01-15, 2016-02-07)"}');

What I've done so far:

First I thought I'd just look up the function that returned whether or not all ranges in an array were pairwise non-overlapping, and then use a check constraint. Unfortunately, that function doesn't seem to be built-in. I also considered using unnest and the * intersection operator, which would probably mean defining a custom aggregate function, although that's not quite right either unless I can get tricky with the aggregator, because the intersection of three ranges might be empty even though two of the ranges overlap.

So now I'm looking at unnesting the array, sorting it by the start of each range, and returning the logical OR of range1 && range2, range2 && range3, and so on up to range(n-1) && range(n), and checking that that result is false. It's far from obvious to me though what the most efficient way to do this is, and I'm still hoping that I've missed some built-in functionality that will help me out at least partially.


texts: Get the value of the first item of JSON object

texts:
I know that the first element of an object is kind of a nonsense, but cannot find a different title. If you find a better name I'll be happy to change!

I use pg_memento to log a schema. Some tables in my schema have a stats column (it's business logic, nothing to do with SQL). So here is my request:

SELECT changes, stmt_date::date FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id=el.id)
  JOIN pgmemento.transaction_log t on (el.transaction_id=t.txid)
WHERE r.changes ?& (SELECT array_agg(key)
                    FROM pgmemento.row_log,
                    LATERAL jsonb_each_text(changes)
                    WHERE key like '%_stat'
                    GROUP BY key)
ORDER BY stmt_date::date

Ok great so this what I get:

changes, stmt_date
{"X_stat": "INCOMING"}, 2017-12-13
{"W_stat": "CREATED"}, 2017-12-13

What I would like is grouping "INCOMING", "CREATED", etc. I'm sure this is doable with a CTE, but cannot find a what to get the element of the first key.

What I would like is

type, date, count
INCOMING, 2017-12-13, 1
CREATED, 2017-12-13, 1

The tricks is to count *_stat by by date :-/

EDIT:

Almost there :

WITH status_per_table as (
SELECT changes, stmt_date::date FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id=el.id)
  JOIN pgmemento.transaction_log t on (el.transaction_id=t.txid)
WHERE r.changes ?& (SELECT array_agg(DISTINCT key) FROM pgmemento.row_log, LATERAL jsonb_each_text(changes) WHERE key like '%_stat' GROUP BY key)
ORDER BY stmt_date::date)
SELECT value from status_per_table, LATERAL jsonb_each_text(changes)

The problem is SELECT array_agg(DISTINCT key) FROM pgmemento.row_log, LATERAL jsonb_each_text(changes) WHERE key like '%_stat' GROUP BY key return multiple value


texts: postgresql v8.4 function runs but doesn't work

texts:
I have a postgresql v8.4 database. I need to update several hundred tables with about 3 million rows of data each.
The customer wants a new column added (which I added) and now I'm trying to update the new column with data.
 I created some SQL statements to do the update. This works if I run it manually in pgAdmin (just the sql statements); so I tried putting this into a function to automate the UPDATE, like so:

CREATE or REPLACE function update_ee() RETURN void as
$$
DECLARE
  row RECORD;
BEGIN
   FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tablename like '%_2015060%' order by tablename asc;
   LOOP
   RAISE LOG 'removing index for %', row.tablename;
   -- remove indices
   EXECUTE 'DROP INDEX ' || row.tablename || '_start_idx;';

   RAISE LOG 'starting update on %', row.tablename;
   -- update table
   EXECUTE 'UPDATE ' || row.tablename || ' set ee = array_to_string(regexp_matches(xml_data, ''<EE_ID>(.+?)</EE_ID>''), '';'') where elnot is NULL and xml_data is not null;';

   RAISE LOG 'restoring index on %', row.tablename;
   -- put indices back
   EXECUTE 'CREATE INDEX ' || row.tablename || '_start_idx on ' || row.tablename || ' USING btree(start);';
   END LOOP;
  RETURN;
 END;
$$
LANGUAGE plpgsql;

 So the function runs; the updates tend to slow down a bit with each successive table; the log statements all appear in the log file. It appears to be working; but upon finish not one table has been updated.
 What did I do wrong?
 Is it not possible to update tables from a function? Is the language wrong? Should I use SQL instead of plsql?
 Thanks in advance...


texts:
I tried to execute a simple .sql script from the command prompt and this is the output I got

C:\Users\Administrator>sqlcmd -S {machine name}\SQLEXPRESS -i C:/SqlScripts/UpdateStat.sql
Sqlcmd: Error: Error occurred while opening or operating on file C: (Reason: Access is denied).

I opened cmd under admin mode and got the same result. Checked C drive permission and Admin has full access.

Running on Windows Server 2012R2


texts:
My DB has two users, the superuser and a (non-superuser) worker. I've installed the tsm_system_rows extension using CREATE EXTENSION tsm_system_rows; on the superuser, and I can use the new functionality in queries from this user. However, the worker user does not appear to be able to do the same:

worker=> SELECT * FROM customer_services TABLESAMPLE SYSTEM_ROWS(2);
ERROR:  tablesample method system_rows does not exist
LINE 1: SELECT * FROM customer_services TABLESAMPLE SYSTEM_ROWS(2);

Is there some extra step I need to do to allow my worker user to be able to use this extension?


texts: On the query planners's reasons for choosing merge joins vs nested loops

texts:
I encountered a real-life version of this simplified sample on how the execution planner deals with a join. The schema and data is:

CREATE TABLE Fact (
    id INT not null,
    value1 INT not null,
    CONSTRAINT PK_Fact PRIMARY KEY CLUSTERED (id)
    );

CREATE TABLE Property (
    id INT not null,
    value2 INT not null,
    CONSTRAINT PK_Property PRIMARY KEY CLUSTERED (id)
);

DECLARE @batchsize INT = 10000;
DECLARE @i INT = 1;

WHILE @i < @batchsize
BEGIN
    INSERT INTO Fact VALUES (@i, @i % 1000);
    INSERT INTO Property VALUES (@i, @i);
    SET @i = @i + 1;
END

The we execute this query:

SELECT *
FROM Fact f
JOIN Property p ON f.id = p.id
WHERE f.value1 = 1

The execution plan makes this a nested loop:

If we reduce the the possible value count in Value1 to a hundred, by modifying a re-creating the schema and data like so:

WHILE @i < @batchsize
BEGIN
    INSERT INTO Fact VALUES (@i, @i % 100);
    INSERT INTO Property VALUES (@i, @i);
    SET @i = @i + 1;
END

... and re-run the query, it becomes a merge:

I have a real-life case where I believe the execution planner makes the wrong choice, as I can significantly boost performance by first selecting all the ids alone, without a join, and then fetch the actual rows with a second query.

I'm wondering:

Are there additional ways to ask Sql Server as to why exactly he chooses one plan or the other in cases like this? The general notion of using the number of possible of values is plausible, but can more exact information be retrieved? In particular, is the number of possible values the only information going in to the execution planner's reasoning, or is it more sophisticated than that? Does the average row size of the joined table matter?
Is there a way to hint Sql Server to do one or the other in cases where testing shows that Sql Server makes a bad guess?


texts:
I am writing a Package for one of my client according to his requirements. Package development is totally complete now time is to protect it with some security.
Is this possible I encrypt all the data with password so no one can open package file(.dtsx) ? and when some one want to execute package he require some other password instead of which I used to protect package file.


texts: Combining multiple left join for one single line

texts:
We have a query as below.

SELECT tblSData.header,
tblEAlert.eMessage
FROM tblSData
LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID
Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc

Structure for the tables are as below:

CREATE TABLE IF NOT EXISTS `tblSData` (
  `sDataID` int(11) NOT NULL AUTO_INCREMENT,
  `header` varchar(255) NOT NULL,
  `aID` int(5) NOT NULL,
  `gFInID` int(5) NOT NULL,
  `gFOutID` int(5) NOT NULL,
  `gFAInID` int(5) NOT NULL,
  `gFAOutID` int(5) NOT NULL,
  `eAlertID` int(5) NOT NULL,
  `dateTimer` datetime NOT NULL
   PRIMARY KEY (`sDataID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tblEAlert` (
  `eAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `sDataID` int(5) NOT NULL,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `eDateTime` datetime NOT NULL,
  `eInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `eMessage` varchar(255) NOT NULL,
  PRIMARY KEY (`eAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Sample data for tblSData

1,"A1",1122,100,102,1,2,1,2013-07-13 15:30:19
2,"A3",1122,104,103,3,4,3,2013-07-13 15:45:19
3,"A4",1122,105,108,5,6,7,2013-07-13 15:55:19

Sample data for tblEAlert

1,1,1,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 1"
2,1,2,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 2"
3,2,2,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 3"
4,2,3,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 4"
5,2,4,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 5"
6,2,5,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 6"

Sample output is.

"A1","Alert 1"
"A1","Alert 2"
"A2","Alert 3"
"A2","Alert 4"
"A2","Alert 5"
"A2","Alert 6"

The problem now is how to show say for e.g. A1 all the Alert Message in one line and same goes for A2 etc. Now each alert message is on a different line.


texts: Downsampling to (eg) "last result on Sunday night"

texts:
We have a largish PostgreSQL table that records user's disk usage.
id      | bigint                      |           | not null | (unique constraint)
time    | timestamp without time zone |           | not null |
quota   | bigint                      |           | not null | 0
usage   | bigint                      |           | not null | 0
zuser   | character varying(20)       |           | not null |

We present data usage to users when they login, and we report on data usage up the org.
In order for the data to be reliable for users, we poll our storage every five minutes. But for our reporting we only need a smaller sample size. We would like to keep some of the data for internal use as well.
Our plan is to keep the month of data, and the last reading for each Sunday night for everything older than that. Because it's data usage, we don't want an average, we just want their last reading before midnight Sunday turns into Monday.
I have discovered date_trunc and have even an idea for an algorithm that includes something along the lines of
SELECT date_trunc('week', time), zuser, quota, usage GROUP BY zuser ORDER BY date_trunc('week', time) DESC

But I'm struggling to work out how to just get a single entry, and how to loop over all data back in time. I could do it in Python, but it seems a little clumsy and then it's something that needs to be run regularly.
In my head I had some sort of database trigger - a PostgreSQL event hitting a function every night at midnight that cleans up (NOW()-1 month)? - that would do it all.
But at the moment I'm looking for help on how to down sample each week into the single reading that happens at the end of that week.
----- edit -----
I ended up coming to
SELECT *
FROM storage.home
WHERE time < date_trunc('month', CURRENT_DATE - interval '1' month)
EXCEPT
SELECT *
FROM storage.home
WHERE TIME '24:00:00' - CAST(time AS time) < INTERVAL '6 minutes'
AND extract (dow FROM time) = 0;

But when I change that to a DELETE, it fails on the EXCEPT clause because (I presume) it's no longer a SELECT...EXCEPT SELECT but rather a DELETE...EXCEPT SELECT which is non grammatical. Searching for DELETE EXCEPT is not simple due to language.
Is what I should be doing something more like this:
DELETE FROM storage.home
WHERE id IN
  (SELECT id
  FROM storage.home
  WHERE time < date_trunc('month', CURRENT_DATE - interval '1' month)
  EXCEPT SELECT id
  FROM storage.home
  WHERE TIME '24:00:00' - CAST(time AS time) < INTERVAL '6 minutes'
  AND extract (dow FROM time) = 0);

Yes. I took a backup and ran the above. Worked exactly as expected.
I considered the below for the left side of that EXCEPT because it's more true to needs, but I thought I'd start with the less accurate.
WHERE date_trunc('day', time) = CURRENT_DATE - interval '35 days')


texts: Is there a way to let MySQL join some external data without importing those data into DB?

texts:
I have some static data which stored in a JSON file:

outinfo=
[{out_id:123,out_name:'hello'}]

and I have a table:

----tableA----
| id | label | out_id |
| 1  |   a   |   123  |
| 2  |   b   |   123  |

I wanna select all info out like

{id:1,label:"a",out_name:"hello"}

It is easy to achieve that by importing the outside JSON data into DB table, and select them out through a simple left join query.
But I wanna know how to achieve that if I don't create a table for those static data.


texts: How to find out which host / IP address / program performed a delete?

texts: We are using SQL Server 2005. In our database some rows are deleted; how can I find the system (host name / IP address), program and date & time of deletion?


texts: Best way to design table structure for Operating Hours along with break time and optimized Query

texts:
Problem #1:

How can the table be designed in MySQL in efficient way to structure

1) Store Opening Time,
2) Break Time and
3) Closing Time in a day

For an instance, On Sunday: Opening Time: 7:00AM, Closing Time: 5:00PM, Break Time Start: 1:00PM, Break Time End: 2:30PM.

Problem #2:

How to define the query to search that table for all Stores that are being Opened/Closed between the given range.


texts: Find which permissions are being used for a given user on a object

texts:
Is there any way in which I can see what permissions are being used to determine a users access to a given object? I'm able to see which permissions have been granted to various roles and users within a database, but am having problems with these reports not lining up with what is actually happening.

For example, the SQL below will return my effective permissions on a given object:

SELECT * FROM fn_my_permissions('dbo.SomeTable', 'OBJECT')
ORDER BY subentity_name, permission_name ;

Which returns:

entity_name         subentity_name permission_name
------------------- -------------- ---------------
dbo.SomeTable                      INSERT
dbo.SomeTable                      SELECT
dbo.SomeTable                      UPDATE

What would be very useful is something along the lines of below, which explains where each line item is coming from:

entity_name         subentity_name permission_name reason
------------------- -------------- --------------- ----------------
dbo.SomeTable                      INSERT          dbowner
dbo.SomeTable                      SELECT          role: datareader
dbo.SomeTable                      UPDATE          role: sysadmin

Note, there is a similar question on the site, however the accepted answer is a query that returns the permissions that are set, not where said permissions are coming from.

Edit: The reason for asking was that there were 'shadows' of former permissions as seen and explained here. However if I had access to something like the above, it would have shown the SQL Server thought I was a member of a given role and thus pointed me in the right direction...


texts: Migrating data from Firebird to CSV on OSX

texts:
I have a .gdb Firebird database file that I would like to partially transform to .csv. From the CLI tool, I'm able to do the following:

CONNECT "/Users/jpmonette/Desktop/file.gdb";
OUTPUT /Users/jpmonette/Desktop/output.csv;
SELECT EMAIL,FIRSTNAME FROM ACCOUNT;

This seems to be working as expected - outputting stdout to a file. However, I would like to know if there is a way to output the data in a easy to consume format (JSON? CSV? SQL dump?). I had a look at fbexport and fbcopy, but they both seem to be not available for OSX.

Any clue on how I could get around that?


texts:
I have a publication of 4 tables and many subscribers (transactional push replication), most of them being interested by one of those 4 tables.

Everything is working fine as long as I create the subscription for all articles:

EXEC sp_addsubscription
    @publication = @publicationName,
    @subscriber = @subscriber,
    @destination_db = @DatabaseName,
    @subscription_type = N'push',
    @article = 'all';

But I get an error if I change

@article = 'TableName'

The @article parameter value must be 'all' for immediate_sync publications.

Why this happens? How can I create a subscription only for one of those 4 tables (=4 articles)?


texts: How to correct a primary key field in MS SQL 2014

texts:
I have a table that uses serial numbers as the primary key in a database. We had a bad import of some serial numbers, where a single apostrophe was included at the beginning of the field.

I attempted to remove it using the statement

update [xxx].[dbo].[xxx] set [xxx].[dbo].[xxx].[keynumber] = replace([xxx].[dbo].[xxx].[keyNumber], '''','')

When I did, I got the following error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'xxx$PrimaryKey'. Cannot insert duplicate key in object 'dbo.xxx'. The duplicate key value is (123456789).

Is there a way that I can update this field and remove the single apostrophe without violating the constraint?


texts: AWS RDS SQL Server extremely slow compared to EC2 with SQL Server

texts:
Background: I built a tool which grabs a lot of data from multiple third party APIs and stores it in a SQL Server database. Then, I built a visualization to display this data in tabular and chart format.

Problem: I just recently migrated from SQL Server on EC2 (m1.medium) to RDS (db.m3.medium) and used this tool to get all my data into my RDS instance.

My application layer is PHP on EC2. Everything is working perfectly. After migration I was able to enter ~300,000 rows INTO the DB via PHP and ODBC driver. Today I went to run a transform stored procedure which will take the ~300,000 rows of data from landing and move it into dbo. On EC2+SQL Server  this script took at most 15 minutes. Now using RDS this script has taken up to 5 hours.

My transform script simply creates temp tables, moves the data from landing into the temp tables, creates a join between temp table and dbo, and where there is no data in that join inserts the data from temp table into dbo. In short, if the data is not in dbo it inserts it.

Example logic is included below:

SELECT DISTINCT *
INTO ##temptable
FROM lnd.landingtable

INSERT INTO dbo.realtable(
    [DataTable].id
    ,[DataTable].name
    ,[DataTable].createdBy
    ,[DataTable].createdDate
    ,[DataTable].updatedBy
    ,[DataTable].updatedDate)
SELECT
    [Landing].id
    ,[Landing].name
    ,SYSTEM_USER
    ,GETDATE()
    ,SYSTEM_USER
    ,GETDATE()
FROM
    ##temptable [Landing]
    LEFT JOIN dbo.realtable [DataTable] ON [Landing].id = [DataTable].id
WHERE
    [DataTable].id IS NULL

I cannot figure out what I am doing wrong. I did not provision IOPS due to cost however from what I read online this should not be necessary. The difference in performance is so dramatic that I cannot understand why anyone would use RDS. Obviously I am doing something majorly wrong. Any guidelines, links, or comments would be greatly appreciated!!


texts: What is 'Log Memory' in Query Store 2017

texts:
In SQL 2017 there is a new execution metric, 'Log memory' other than that it was added in 2017 I am not finding anything about it.

Execution metric: (SQL 2017)

  CPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, Physical Reads, CLR time, Degree of Parallelism (DOP), Row count, Log memory, TempDB memory, and Wait times

I believe I understand what all the other metrics are and why I might care.

I ran all the metrics for the top 5 resource consuming queries, during several specific periods.  I recorded and now I am examining the results.  I know the (very large) values for 'Log memory' are in KB's.

What exactly is the metric 'Log memory'?

Edit, having received two answers I checked

The answer by LowlyDBA suggests it is a combination of 5 related fields from sys.query_store_runtime_stats

Using the code to validate provided by jadarnel27 in their answer

I created the database '231682' and ran the test query for the 5 fields, I got results very similar

I summed (used =SUM() in Excel) my values and got 1,383,040 (bytes)

I looked at Query Store, for Log memory used (KB), it shows a value of 354,058,240 (KB), this number is orders of magnitude larger, it is also KB as compared to bytes, in bytes it would be 354,058,240,000 (bytes)

I summed the totals of all the fields and only got 1,655,236 (byte)

SELECT *
FROM sys.query_store_runtime_stats qsrs
WHERE qsrs.avg_log_bytes_used > 0;

I suspect the answer to my question, is that the 'Log memory' metric in SQL 2017, is not any real value. The value presented in this small experiment would be 354GB, unrealistically high.


texts: How to design a database for production volume and loses control?

texts:
I must develop a database for inputting information of the produced volume (hourly), quality and technical issues during this production. As the input of the production volume is made manually, the user must indicate for each time interval how many parts were produced (for instance from "6 a.m. to 7 a.m."). This time intervals are configured by an ADM user, who also indicates the speed of the production line (time/part) and the pauses programmed during the day. The points I just can't figure out how to do are:

Should I save this time interval at the database, such as "start time:6 a.m. | end time: 7 a.m." ? Or use just like a string "Time interval: 6 a.m. - 7 a.m." ?
When the line has broken down for example at 6:45 a.m. and the problem was solved only at 7:30 a.m., when inputting the production volume from 6-7 a.m. the user must indicate 15 minutes of lost due to line break down, and from 7-8 a.m. 30 minutes of production losses. The problem here is that I must be able to know that both losses were only one event, and not two separated ones.

If anyone have any tip for me, I would really appreciate. :)

Best regards!


texts: Query to get logs between Start and End Night hour

texts:
I am working on Procedure where we pass in the Night Start Hour and the Night End Hour, I need to get the Logs between these hours.

So:

If the StartHour = 18 and EndHour = 5, the Stored Procedure should return all logs between 6:00 PM and 5:00 AM.
If the StartHour = 1 and EndHour = 5, the Stored Procedure should return all logs between 1:00 AM and 5:00 AM.

I tried creating a SQL Fiddle but there seems to be a problem with creating the fiddle in either SQL Server 2014 or SQL Server 2008 Today.

I AM USING SQL SERVER 2014 - But Fiddle is in MySQL

(I have created one in MySQL to display the data) [Wrong Fiddle: http://sqlfiddle.com/#!9/6f0a6/1]

Correct: http://sqlfiddle.com/#!9/6467d8/3

This is what my Script looks like:

Select
    *
FROM
(

    Select
        iVehicleMonitoringId,
        dtUtcDateTime,
        HourPart = DATEPART(HOUR, dtUtcDateTime)
        From VehicleMonitoringLog vm

        Where vm.dtUTCDateTime > @utcStartDate AND vm.dtUTCDateTime < @utcEndDate
        AND  DATEPART(HOUR, vm.dtUtcDateTime) >  @iStartHour

UNION
    Select
        vm.iVehicleMonitoringId,
        dtUtcDateTime,
        HourPart = DATEPART(HOUR, dtUtcDateTime)
        From VehicleMonitoringLog vm

        Where vm.dtUTCDateTime > @utcStartDate AND vm.dtUTCDateTime < @utcEndDate
        AND  DATEPART(HOUR, vm.dtUtcDateTime) <  @iEndHour
) As G
Order by dtUtcDateTime

But this query will be wrong for the second condition. Because 18,19,20.... are all greater than 1, so I am confused on how to write this query.


texts: MySql: how to remove special characters from column in query

texts:
What i get value in php

    $subject = preg_replace('/[^A-Za-z0-9]/', '', 'abc - 123');

// abc123

Is it possible to use preg_replace in my mysql query?

I want to something like this. want to compare like following

SELECT `id`,`user_id`,`subject` FROM `mail` where preg_replace('/[^A-Za-z0-9]/', '',`subject`)=$subject

I know what i write above is not available. Still i want help. I didn't get proper answer.

I tried with function

CREATE DEFINER=`root`@`localhost` FUNCTION `removeExtraChar`(`str` VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
    NO SQL
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);
  SET len = CHAR_LENGTH( str );
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c REGEXP '[[:alnum:]]' THEN
        SET ret=CONCAT(ret,c);
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  RETURN ret;
END

and call it like

My table data

id   user_id   subject
----------------------
1      1        abc-123
2      1        xyz-234
3      2        new-post

SELECT `id`,`user_id`,`subject` FROM `mail` where  removeExtraChar(`subject`)=$subject

but this goes in infinite loop. Nothing executes
Note: I don't want to use extra mysql plugin.


texts:
I have a table for user registration with a column for account_status which I filled with the content from SYS.dba_users. I also have a APEX application for this table and some other stuff and want to add a button that will update the account_status column from SYS.dba_users using the MERGE statement.

MERGE
  INTO USER.usr_t_user_reg t
  USING (
    SELECT username, account_status
    FROM SYS.dba_users) s
  ON(UPPER(t.username) = UPPER(s.username))
  WHEN MATCHED THEN
    UPDATE SET t.account_status = s.account_status
    WHERE t.account_status != s.account_status;

I set Conditions to SQL Expression, but every time I want to apply my changes I get a Failed to parse SQL query! ORA-00920: invalid relational operator and I don't know why. I even tried to change the Action When Button Clicked options, but it's the same. -.- When I execute my statement with SQL Developer it's all fine and no error message or so.

I also tried to join my user registration table with the SYS.dba_users table to get the account_status column, but I registered multiple database instances in my table and therefore I can't join it.

Can somebody give me a hint or a solution to my problem? (I hope you all understand my problem ^^)


texts: Maximum table capacity in SQL Server 2008

texts:
I have an application which inserts more than 1 billion rows annually into a table. This table contains some varchar and bigint columns and one blob column as well.

The 1 billion rows consist of history data which are kept for tracking purpose. So I was wondering whether there will be a table capacity limitation if I continue in this structure according to this MSDN article about maximum table size.

Does the data file size mentioned in that link refer to the table data file group?


texts: Move an Oracle database by moving the raw data files

texts:
I have a large Oracle database (~300 GB) and I need to move a copy of the database to a new environment in a short period of time. I was wondering if there is a way to just pick up the raw files Oracle manages and move them to the new box and bypass doing a dump and then another import?

I was looking at http://docs.oracle.com/cd/B28359_01/server.111/b31107/asmcon.htm but not sure if this is what I need. Where are the raw data files stored?


texts: How to get list of databases that are mapped to a login?

texts: I was trying to list all databases that are mapped to a login in sql server using T-SQL without creating a temp or any type of table. Any help will be appreciated.


texts:
I have a table where all actions affect single rows (insert and update, no delete's but if there were, they would be individual as well).  I'm considering using a UDF in a check constraint so I can make sure that a field value is only used in combination with another value (two FK's, the first time A=1 is used, B=x becomes fixed, so that if x = 4 then A=1 can never be matched with B=3 or anything except 4).

What are the downsides of doing this, other than the neligible additional time spent when inserting/updating the row?

UPDATE: The application doesn't provide a mechanism for changing either column A or B after the row has been created.


texts: sql expiry date use Trigger OR Schedule a Job

texts:
I have this table :

Now, i like the way when the date expires. EndTime < GETDATE()

expire field is true.

What method should I use? use a trigger to true or a schedule job that run before run any  query  in this table?


texts:
I'm trying to create a trigger where I set a colunm to 0 if another column is set to 0 and 1 if it's set higher than 0. This is what I wrote

CREATE TRIGGER livre_disponible BEFORE INSERT ON livres
FOR EACH ROW
BEGIN
    IF NEW.nb_exemplaires=0 THEN
            SET NEW.disponible=0;
    ELSEIF NEW.nb_exemplaires>=1 THEN
            SET NEW.disponible=1;
    END IF
END

The error is said to be on line 5 which is the first "SET".

I this the correct syntax to do it?


texts: Re integrate SQL Server 2008 to new Active Directory

texts:
I have a server which was joined to an AD Domain, then installed with SQL Server 2008 R2 and System Center Operations Manager 2012's Reporting Server on it. The same server had the Active Directory DS, CS, DNS Server, IIS Server installed.

Now someone just removed all the role from this server which includes AD DS, AD CS, IIS, DNS, all gone, what only remain is the SQL Server only. To solve the issue, I installed the IIS Server on it, however instead of installing ADDS here, I have installed it to another machine with Windows 2012 R2, the new machine has AD DS, AD CS and DNS Server.

During the System Center(SCOM) install, I created some users on the old AD for the SCOM, three users and all users on a group named OMAdmins. (where the DB is also installed). They had different permissions to SQL Server and to SCOM (SCOM Management server and console are installed on another machine, which too unfortunately losses its integration to old AD).

Now I re-created the same users, same groups, with same old password on new separate AD machine I have, and authenticated all the services with the right users on the SQL Server machine and on SCOM machine.

My issues are: I cannot take any backup of DB, I was able to login to SQL Server Management Studio using NT Authority, but when I try to do backup, it says permission denied, when I check the Security -> Logins on the Studio, I see many users are missing (those SCOM Users, the groups those user was on, some NT Authority and the SCOM computer which was also on the AD). I only see two users now BUILTIN/Administrator and sa. I tried to add new users to add those missing users from AD, but SQL server fails with error message on permission denied.

Any help to bring my server back> I was able to start the services with new AD user's authentication but it looks like it is just a forced authentication. The original authentication SQL server had is missing. I need some to help to get it back to work and so for the SCOM which is also down too. I even tried SCOM Management Server recovery, but it failed and the log says SQL connection was established but existing database is already there as error, so cannot proceed with the recovery of SCOM either. It is the SQL Server which first needs an urgent fixes or recovery.


texts: SQL server Read Only permission automatically for new databases

texts: When I setup my database I create a sql user that my application runs as.  The sql user gets read only access to all the databases on the server. The problem is the user won’t have read only access to any new created databases in the future. There is another app that creates these new databases for users and I don’t have a way to add my user script into the templates database created.  Is there a way to automatically update the sql user for read only for the future ones?  SQL server 2014.


texts: Is there a performance hit by breaking out tables into different schemas in SQL Server?

texts:
Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server.

For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and Table2 into the history schema, and then I tried to link the tables in a query, is this implicity slower than having the 2 tables in the same schema?


texts: How to create column in DB with default value random string

texts:
Can I create column in DB table (PostgreSQL) which have default value random string, and how ?

If is not possible, please let me know that.


texts:
I need to get count of 2 columns with group by

This is my sample data

I need to get count of persons from particular state and count of persons from particular country

My output should be like

I need to manipulate data like this; how to create query for this


texts: Will SSRS be extinct soon, and PowerBI the new model?

texts: I read SQL Server 2017 will now include PowerBI Server. They also moved SSRS to a different installer, so it will not come packaged in the original SQL Server installation. Does this mean Microsoft will eventually try to deprecate SSRS? Should our team try to build new reports in PowerBI and transition the previous SSRS reports?


texts: Which items are table entities and which are fields?

texts:
I'm a mystery novelist with a series of three books that will end up at around 20 novels. I am having trouble getting the tables and fields sorted out.

I want to be able to:

create a list of all the people in the series
create a list of all the books in the series
search and find all the characters who have appeared in more than one book
which book a character first appeared
list a complete company or platoon by year
identify all characters with a serial number
Search for a  character in different companies, platoon, sections within the Battalion
Find the book where a character first appears

During the course of the series, people can be promoted or demoted (change their rank). They can be cross-posted within the Battalion from one Company to another (from a rifleman in Alpha Company to a Mortar man in Mortar platoon, Support Company). I have no idea how to represent this – or even if it can be done with one database.

Based on what I understand of normalization, I have reduced the data chunks into the following entities or fields:

Books by title
Years – 1964 -70
Months of the year (more than one book can appear in a year, no book appears in two years)
Battalion organization – the Companys
The platoons, sections
Character – last name, first name, nickname, where he came from in Canada, notes
Rank – (Which can be different depending on the year/book)

More information is available if required, please ask for any information regarding the Canadian Army of the time.


texts: MySQL (on Windows) general log too large to rotate properly?

texts:
First off, I'm not sure if this should go to Server Fault or Database Administrators.  It could be a Windows Server 2008R2 problem.  But I am asking here first in the chance that someone else running MySQL on Windows might have had the same problem.

We are using MySQL Community Version 5.5.34 with the general log turned on.  On Windows, there was no built-in log rotation script, so I wrote one, following the documentation on the MySQL website.  The information there says to rename the log file, then flush.  This is supposedly because it's quicker (almost instant) to rename the file than it is to make a copy of it in Windows.  Then presumably the log can be flushed right afterward (instead of waiting some arbitrary length of time for the log file to be copied).  The script renames the log to (date-time-stamp).log, then flushes the log using mysqladmin.  It then compresses the log as a zip file and moves it to an archive folder.

Anyway, I created a Windows Scheduled Task to run the script.  Everything works when I run the script either directly or through the Scheduled Task manager.  But when I leave it to run on its schedule, the log is not rotated properly.  It looks like the log is flushed, because I see the new log.  But the renamed file is not there.  Our log does get fairly large, the first day it got to about 400 MB.  When I ran tests on the script, the log was smaller (10 MB, etc).  I'm wondering if having a large quickly updating log might be too much for Windows/MySQL to handle.  Is there any other recommended way to rotate logs in Windows?

I will try running the rotation script without the zip compression and moving the file to the archive folder to see if that affects anything.  But like I said, everything works when I test the script both from the command line or through the Scheduled Task Manager (so presumably permissions and working directory and stuff like that should be ok).  The only difference I can think of is the size of the log file when the script is run.

Any ideas?


texts: Will MariaDB treat ut8/utf8mb3 and utf8mb4 collations as compatible?

texts:
Background on relevant character sets

MySQL and MariaDB support the deprecated utf8mb3 character set and collations. (utf8 is currently an alias for utf8mb3.) This supports all Unicode code points on the Basic Multilingual Plane (BMP) and stores on-disk using up to 3 bytes.

The more inclusive utf8mb4 character set supports all Unicode code points (including BMP) using up to 4 bytes on-disk. (utf8 will alias to utf8mb4 in the future.) Everything storable with utf8mb3 is stored the same way (bit-for-bit) in utf8mb4. Some things storable with utf8mb4 are not storable with utf8mb3.

Our tables

We have an index definition using the new character set:

user_email VARCHAR(50) CHARACTER SET utfmb4 UNIQUE

and somewhere else have one using the deprecated version

email_in_another_table VARCHAR(50) CHARACTER SET utf8 UNIQUE

The question

Will a query using the two indices avoid reading row data (which is slower than indices)?

SELECT user_email
  FROM a
 UNION -- this is an intersection
SELECT email_in_another_table
  FROM b


texts: Unable to shrink log file due to transactional replication

texts:
I have a transactional replication configured as per below:

ServerA.DBA (pub) --> ServerB.DBB (Sub)
ServerB.DBB (pub1) --> ServerC.DBC (Sub)
ServerB.DBB (pub2) --> ServerD.DBD (Sub)
ServerB.DBB (pub3) --> ServerE.DBE (Sub)
ServerB.DBB (pub4) --> ServerF.DBF (Sub)
ServerB.DBB (pub5) --> ServerG.DBG (Sub)

Today, I got an alert saying that my replication ServerA.DBA (pub) --> ServerB.DBB (Sub) is failing with below error:

  Replication-Replication Distribution Subsystem: agent failed. The
  transaction log for database is full due to 'REPLICATION'.

Upon investigation, I found that my log file was capped at 100GB on ServerB.DBB which was causing the issue. To fix this, I added a new log file as I was unable to extend the existing log file due to same error. This fixed my issue and replication caught up in some time. However, now I am unable to shrink the log file and it always shows my log file utilization as 99% and increasing. This raised some doubts in my mind whether my replication from Server B to C,D,E,F and G is actually running or not and why I am unable to shrink the log file.

Log_reuse_wait_desc on my ServerB.DBB always shows 'REPLICATION'. I ran DBCC OPENTRAN on my ServerB.DBB and it shows below:

Replicated Transaction Information:
Oldest distributed LSN     : (0:0:0)
Oldest non-distributed LSN : (10417370:9406:1)

This value is not changing since many hours now. Does it mean my replication from Server B to C,D,E,F and G is not working? However when I see in replication monitor it shows everything as fine. i.e. logreader, all distributor agents are running and moving transactions for all publications. Why I am unable to shrink the log file and what should I do to shrink the log file?

Also, want to add that CDC is not enabled on any of my DBs involved in replication. DBCC LOGINFO shows all my VLFs in use.


texts: Why has deadlock behaviour changed between MariaDB 10.1.22 and 10.2.14?

texts:
Since upgrading MariaDB 10.1.22 to 10.2.14 our MariaDB slaves are encountering deadlocks that are not handled in less than 600 seconds thus the classic semaphore decision to crash the server.  The server has crashed 3 times. The extremely high volumes have not changed; only the MDB performance has improved with the upgrade.

Note we have Insert on Duplicate Updates that process super high volumes on our master. The deadlocks on same queries occur on the slaves so it has to be related to the slave parallel replication locking.  Reducing slave_parallel_workers has mitigated some of this.

In summary looking to understand what has changed with mdb 10.2.x regarding threads, timeouts, etc. to zoom in on this issue.  Why MDB is unable to determine the deadlock and rollback one of the offending transactions.

I ACKNOWLEDGE all deadlocks should be addressed but as stated above they are not occuring on the master, only on the slave for same statements.

We had the deadlocks prior to the upgrade but MDB always managed same with NO problems.

  2018-06-11 10:32:02 139519224362752 [Note] InnoDB: A semaphore wait:
  --Thread 139518736328448 has waited at read0read.cc line 579 for 910.00 seconds the semaphore: Mutex at 0x7f2b63dc13a0, Mutex TRX_SYS created trx0sys.cc:554, lock var 2

  2018-06-11 10:32:02 139519224362752 [Note] InnoDB: A semaphore wait:
  --Thread 139518749968128 has waited at dict0dict.cc line 1160 for 910.00 seconds the semaphore: Mutex at 0x7f2b63dcb500, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2

  2018-06-11 10:32:02 139519224362752 [Note] InnoDB: A semaphore wait:
  --Thread 139518750574336 has waited at dict0dict.cc line 1160 for 890.00 seconds the semaphore: Mutex at 0x7f2b63dcb500, Mutex DICT_SYS created dict0dict.cc:1096, lock var 2

  InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic
  info: InnoDB: Pending reads 2, writes 0 InnoDB: ###### Diagnostic info
  printed to the standard error stream 2018-06-11 10:32:32
  139519224362752 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted >
  600 seconds. We intentionally crash the server because it appears to
  be hung. 180611 10:32:32 [ERROR] mysqld got signal 6 ;