texts: SQL Server services could not be started as 2 instances sharing same 'model' file

texts:
Please advise me on this issue:

I have one default instance in SQL Server 2005 (sp 3 x64 bits) called instanceA. Then I installed another 2 instances called instanceB and C.

After that I restore master.bak from production server to this instanceB. The SQL Server services for this instance could not be started at all since then. If I turned off the default instance services, instanceB can be started. This is because both of the instances are pointing to the same model.mdf file in MSSQL.1 folder. Hence both instances could not be started simultaneously.

I believe that in production server, the model path is configured to the default folder MSSQL.1 .Is there anyway to change the path to MSSQL.8 that belongs to instanceB upon installation so that both instances A and B could be started together?

How to change the path of pointing to the correct model file?

Thank you.


texts: How the xlogs files travels form Master to Slave

texts:
I have some doubts about how the workflow proccess in PostgreSQL replication using archiving logs.

If I have this configuration in Master postgresql.conf:

archive_command = 'test ! -f /path/to/archive/%f && cp %p /path/to/archive/%f'

After the %p and %f parameters have been replaced, the actual command executed might look like this:

test ! -f /path/to/archive/00000001000000A900000065 && cp pg_xlog/00000001000000A900000065 /path/to/archive/00000001000000A900000065

Ok, if the file isn't into archive, then it will be copied.

If I have this configuration in Slave recovery.conf:

restore_command = 'cp /path/to/archive/%f %p'

Then if I don't configure anything like this :

scp /master/path/to/archive/* /slave/path/to/archive/

How is possible that the xlogs files in /path/to/archive/  travels from Master to Slave, and then the Slave copy them to its pg_xlog/ directory?


texts: Linked server not working after AWS RDS failover

texts:
Linked servers created on AWS RDS to other instances is not working after RDS instance failover. Error we get is "An error occurred during decryption".

This is similar issue reported, but no solution offered.
Linked Server: An error occurred during decryption

Any help if this is a bug or limitation with RDS?


texts: Does a record with NULL data does not eat any storage on the harddrive?

texts:
This is not an expert question about database, so I'm not sure if this question can be posted here or not. So if this question is out of place, feel free to close it.

It's a long development story, but long story short, the managers want to create a generic table to store various assets data. They want the table to be able to hold various type of data, eg. they want the table to be able to hold a car's number of tires, car's color, car's year model, etc, but they also want the table to be able to hold a house's address, floor size, etc.

So they want to create this generic table for record all the various assets' details with like 20 VARCHAR(45) columns, 20 DOUBLE columns, and 20 BOOLEAN columns. I have been against this decision because I'm afraid that the unused column on the row will used up space in harddrive. But they said that it would not used up space in harddrive if the record is NULL. So if for 60 columns, I only use 3 column in a row of data, then the 57 other columns will not take space because they are NULL. Is this true? Which one is true? Thanks.


texts: Are PostgreSQL 10.x identity columns inherently indexed? unique? Or do I need to add a (unique) index to the column as desired?


texts: How can I figure out what service is trying to connect as root?

texts:
In my postgresql log I see this from time to time:

2014-05-20 12:34:03 CEST FATAL:  role "root" does not exist

I would not expect any of the services that I have configured to connect as root. How can I figure out ip/service/port etc. of what service this is?


texts: Elevate transaction isolation in Maria DB

texts:
I am migrating code from Microsoft SQL to Maria DB. In the MSSQL stored procedure a transaction will be started with repeatable read and then later conditionally elevated to serializable if needed. In Maria DB this does not seem possible.

When trying to set the isolation level after the transaction has already been started, I get the error "Transaction characteristics can't be changed while a transaction is in progress."

Is there a way to do this in Maria DB without making the entire transaction serializable?

EDIT:

Here is an example of the Microsoft SQL code I am trying to migrate.

CREATE PROCEDURE -- ...
    -- ...
AS BEGIN

    -- check if there is a transaction already in progress
    -- if not, start a new one, otherwise elevate isolation to serializable
    DECLARE @TxNested INT = @@TRANCOUNT;
    IF @TxNested = 0 BEGIN TRANSACTION;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRY
        -- lots of code
        -- commit transaction if we are the one that started it
        IF @TxNested = 0 COMMIT TRANSACTION
    END TRY BEGIN CATCH
        -- ...
        -- rollback transaction if we are the one that started it
        IF @TxNested = 0 ROLLBACK TRANSACTION
        -- ..
    END  CATCH

END

EDIT 2:

It is not possible to elevate the isolation level on a transaction that is already started and LOCK TABLES does not work inside of stored procedure.
Is there a way to take range locks even if the current isolation is not serializable?


texts:
When I use java code to insert data to oracle,the following information appeared:

java.sql.SQLRecoverableException: No more data to read from socket
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1200)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3530)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)

The oracle release is 11g.


texts: Has using overwrite saved my from inserting duplicate data?

texts:
I set up a pipeline to insert data daily. The query is a simple grouped count:
CREATE TABLE IF NOT EXISTS schema1.import_table AS
SELECT
    p.yyyy_mm_dd,
    m.feature,
    p.xml_id,
    count(distinct(p.persona_id)) as persona_count
FROM
    schema.table1 p
INNER JOIN
    schema.table2 m
    ON m.metric = p.metric
    AND m.backapp = p.backapp
WHERE
    yyyy_mm_dd >= "${hivevar:yesterday}"
GROUP BY
    1,2,3;

INSERT OVERWRITE TABLE schema1.final_table
PARTITION (yyyy_mm_dd)
SELECT
    xml_id,
    feature,
    persona_count,
    yyyy_mm_dd
FROM
    schema1.import_table;
DROP TABLE IF EXISTS schema1.import_table

I deployed this to run starting on 2019-01-01, three weeks ago. So a job was scheduled every day from 2019-01-01 up until present day, and then continue to run once per day once caught up. In each scheduled job, ${hivevar:yesterday} is passed a date to use i.e. on the job scheduled for 2019-01-01, the script is passed the date string to use in place of ${hivevar:yesterday}.
I noticed an error in my query, in that I did >= instead of =. This means I've been getting counts for every day since the scheduled job date, instead of just the scheduled jobs date.
So when the first job ran on 2019-01-01, it would have actually inserted for every day up until present day. Whereas my intention was to inserts one days data at a time. Then on 2019-01-02, it would have dropped every day in the target table except for 2019-01-01 and then inserted the data, due to the OVERWRITE on the insert clause, is this correct?
All in all, has the OVERWRITE saved me from duplicating data despite my error of using >= instead of =? Is the biggest issue here that I would have wasted a lot of resources for the earlier scheduled jobs, as I would have been dropping and inserting the same data every day? I assume now that the job has caught up and >=${hivevar:yesterday} would now be >=2020-09-12 so we don't count for as many dates, using less resources so it's not so much a problem.


texts: Mysql query runs longer than long_query_time variable

texts: I have this query in picture attached that runs longer than my long_query_time. Please can anyone recommend what index or indexes to create to speed it up?


texts: What is the best process for merging packages to use SSIS package configurations?

texts:
I have several packages with hard coded connection managers which I would like to merge into one package.  Each individual package is currently loading a single data file to a single table is a single database and I would like to make the process more streamlined and also do some transformations on the data which would be more useful in a single package than in the many disparate ones.

I created a package configuration for one of the packages and changed the connection manager in the XML Package Configuration and am now looking to add the other packages to this one.

I have tried copying the tasks and then their data flows.  This results in errors like:

Error 1 Error loading Package1.dtsx: The connection "{450E96E4-3233-424A-BC4E-2F0A3EE1B2AA}" is not found. This error is thrown by Connections collection when the specific connection element is not found.    C:\PathToCofigFile\ConfigFileName.dtsx  1   1

and

Error 6 Validation error. Create Master Table(s) Execute SQL Task: Connection manager "{DE947E19-E854-47F6-9B84-DDE1343AEDDB}" does not exist.  Location.dtsx   0   0

Possibly, the best way is just to edit the XML of all the packages to have the same connection name first and then merge them.  Just wondering if anyone has any experience with this and could recommend a better way.

UPDATE:  I was able to resolve the errors by copying the connection managers from the source packages and then either editing the package XML or changing the connection in the GUI.  Still seems there must be a better way...


texts:
I wanted to speed up a large insert bulk by disabling keys. So I ran the following command on a MyISAM table:

alter table my_table disable keys;

When I execute show index from answer_2011, I see a comment of disabled in all the keys, except for the primary key.

Also, as I'm inserting new rows, I see that my_table.MYI is being modified.

Is there a way to disable or drop the primary key that does not involves having MySQL create temporary tables?


texts: SQL Server: Pass table name into table valued function as a parameter

texts:
All of my tables have those two columns:

[Id] [uniqueidentifier] NOT NULL
[Revision] [bigint] IDENTITY(1, 1) NOT NULL

Every other time I write a query, I want to eliminate some of the rows. The elimination conditions are the same for every table. I want an easy way to specify those conditions once and then reuse them on any table.

I defined a table type as a "base class" for my tables

CREATE TYPE EntityTable AS TABLE (
    [Id] [uniqueidentifier] NOT NULL
    ,[Revision] [bigint] IDENTITY(1, 1) NOT NULL
)

And here's a table valued function I create

CREATE FUNCTION MaxRevision (@Entities EntityTable READONLY)
RETURNS TABLE
AS
RETURN

SELECT *
FROM @Entities e
WHERE e.Revision = (
        SELECT max(Revision)
        FROM @Entities er
        WHERE er.id = e.id
        )

I thought I would be able to use it like that

SELECT * FROM MaxRevision(NameOfSomeTableWhichHasIdAndRevisionColumn) WHERE /* some other conditions */

But I'm getting the error

Msg 207, Level 16, State 1, Line 2
Invalid column name 'NameOfSomeTableWhichHasIdAndRevisionColumn'.

Why it treats the table name I pass as a column name? Are there any other ways to implement the behavior I want?


texts: Best way to store immutable/read-only copies of data for logging?

texts:
I'm storing data about tasks like so

TASK (id, name, employee_id, machine_id, ...)

For logging purposes I would like to store any information about that TASK once it was made.
Since the reference fields might change or be removed (let's say we remove an employee or a machine, or change its hard_drive...), I want to make sure I log everything as it was when the task was done.

My first approach was to just have a field for everything like so

TASK_DONE (id, name, employee_name, ..., machine_hard_drive, machine_ram, ...)

but it made a lot of redundant data and a quite huge table (there are already about 5 referenced tables in TASK each with a lot of logging relevant fields)

I felt like having some other logging tables like MACHINE_ON_TASK and referencing only to their ids could spare a lot of redundancy, but it also means creating new tables for everything log-worthy. On the other hand, it might make more sense that a TASK and TASK_DONE (and their associated data) look the same.

What do you think would be a better way to approach or solve this problem?

I'm using SQL-server but I don't believe this is relevant here.


texts:
My search for this question yielded some similar questions, but none that I could see exactly like this.
Question: In your experience, when doing a SQL Server upgrade, say from 2008 to 2017, or from 2012 SP1 to 2017, or whatever from/to (also depending on whether the direct upgrade path is available or not), is it better to start with a brand new installation, or with an in-place upgrade? What are the challenges you faced when doing one versus the other (SQL Server upgrade, in-place or new fresh install)? What poison do you pick? What is your own personal experience and advice?

Thank you in advance, Raphael.


texts: How to get the summary(sum) from Name wise and product wise

texts:
I have a 3 tables,

1.Payment Collector

Product

Payment

Payment collectors are collecting payments product wise and stored into payment table. products are assigned to payment collectors some products are not assign to collectors.. question is how to retrieve all the payment collectors product wise total collected amount result should be,

Note: data should be retrieve horizontal way and vertical way  (two queries)

Thanks.


texts: What happens if two mySQL queries both try to lock the same row at the same time?

texts:
So this isn't exactly my strong suit, but here's my basic understanding of the situation:

If two threads of the same application both try to execute the same query at the same time, mySQL will experience deadlock.  The way to prevent deadlock is by locking the table (or row) before writing to it.  But what if the locking requests happen simultaneously?

e.g. you have a table like:

#my_table
---------------
| key | value |
---------------
|  1  |  foo  |
---------------
|  2  |  bar  |
---------------

Then I write a query in my application that does something like

LOCK TABLES my_table WRITE;
UPDATE my_table SET value = RAND() WHERE key = 2;
UNLOCK TABLES;

and by some miracle two instances of this query fire in the exact same millisecond (nanosecond?) what would happen?  It's an InnoDB table if that matters.


texts: SQL Reporting Services (SSRS) - Blocked process on ReportServer, SP CheckSessionLock

texts:
We frequently see blocking on our SSRS box which houses both front-end Report Manager ReportingService and the SQL database engine with ReportServer & ReportServerTempDB Catalog databases

Blocking session:
    Database name: ReportServer
    Application name: Report Server
    Host name: SSRS01
    OS User name: SYSTEM
    Database User name: NT AUTHORITY\SYSTEM
    First 2KB of SQL: CREATE PROCEDURE [dbo].[CheckSessionLock] @SessionID as varchar(32), @LockVersion  int OUTPUT AS DECLARE @Selected nvarchar(32) SELECT @Selected=SessionID, @LockVersion = LockVersion FROM [ReportServerTempDB].dbo.SessionLock WITH (ROWLOCK) WHERE SessionID = @SessionID

Blocked session:
    Database name: ReportServer
    Application name: Report Server
    Host name: SSRS01
    OS User name: SYSTEM
    Database User name: NT AUTHORITY\SYSTEM
    First 2KB of SQL: CREATE PROCEDURE [dbo].[CheckSessionLock] @SessionID as varchar(32), @LockVersion  int OUTPUT AS DECLARE @Selected nvarchar(32) SELECT @Selected=SessionID, @LockVersion = LockVersion FROM [ReportServerTempDB].dbo.SessionLock WITH (ROWLOCK) WHERE SessionID = @SessionID

Anyone else experience this, and fix it ?


texts: Duration how long a door is open, but same status records can be present more than once

texts:
I want to calculate the difference between a status change, however the same state can have multiple records, see example below.

create table #DeviceMessages
(
    DoorName nvarchar(50),
    [StatusDoor] nvarchar(50),
    EventEndDate DatetIme
)

INSERT INTO #DeviceMessages
  (DoorName, [StatusDoor], EventEndDate)
VALUES
('Door 001', 'OPEN', '2017-03-23 16:50:42.143'),
('Door 001', 'CLOSED','2017-03-23 16:54:15.813'),
('Door 001', 'CLOSED','2017-03-23 17:53:12.757'),
('Door 001', 'OPEN', '2017-03-23 18:22:37.560'),
('Door 001', 'CLOSED','2017-03-23 18:28:01.197'),
('Door 001', 'CLOSED','2017-03-23 18:28:01.250'),
('Door 001', 'OPEN', '2017-03-23 18:29:40.593'),
('Door 001', 'OPEN', '2017-03-23 18:29:40.600'),
('Door 001', 'CLOSED','2017-03-23 18:34:53.827'),
('Door 001', 'CLOSED','2017-03-23 18:34:53.843'),
('Door 001', 'OPEN', '2017-03-23 18:42:56.463'),
('Door 001', 'OPEN', '2017-03-23 18:42:56.470'),
('Door 001', 'CLOSED','2017-03-23 18:46:32.550');
GO

It goes well when there are multiple CLOSED records, but it doesn't work when there are multiple OPEN records. At that moment I want the earliest OPEN record, compared to the nearest CLOSED record. I use the following statement:

    WITH DoorActivityWPairedTimes
AS
(SELECT [StatusDoor]
, EventEndDate
, DeviceDoorOpen = DoorName
, DeviceDoorClosed = LEAD(DoorName, 1) OVER(ORDER BY DoorName, EventEndDate)
, DoorOpen = EventEndDate
, DoorClosed = LEAD(EventEndDate, 1) OVER(ORDER BY DoorName, EventEndDate)
, DoorOpenActivityDate = CAST(EventEndDate AS DATE)
, DoorDichtActivityDate = CAST(LEAD(EventEndDate, 1) OVER(ORDER BY DoorName, EventEndDate) AS DATE)
FROM #DeviceMessages)

SELECT DeviceDoorOpen Device
, EventEndDate
, DoorOpen
, DoorClosed
, DATEDIFF(second, DoorOpen, DoorClosed) DurationOpenSec
FROM DoorActivityWPairedTimes
WHERE [StatusDoor] = 'OPEN'
  AND DoorOpen Is Not Null
  AND DeviceDoorOpen = DeviceDoorClosed
  AND DoorDichtActivityDate = DoorOpenActivityDate
  order by EventEndDate asc

The result of the query above:

+----------+-------------------------+-------------------------+-------------+
|  Device  |        DoorOpen         |        DoorClosed       | Dur.OpenSec |
+----------+-------------------------+-------------------------+-------------+
| Door 001 | 2017-03-23 16:50:42.143 | 2017-03-23 16:54:15.813 |         213 |
| Door 001 | 2017-03-23 18:22:37.560 | 2017-03-23 18:28:01.197 |         324 |
| Door 001 | 2017-03-23 18:29:40.593 | 2017-03-23 18:29:40.600 |           0 |
| Door 001 | 2017-03-23 18:29:40.600 | 2017-03-23 18:34:53.827 |         313 |
| Door 001 | 2017-03-23 18:42:56.463 | 2017-03-23 18:42:56.470 |           0 |
| Door 001 | 2017-03-23 18:42:56.470 | 2017-03-23 18:46:32.550 |         216 |
+----------+-------------------------+-------------------------+-------------+

WANTED RESULT

+----------+-------------------------+-------------------------+-------------+
|  Device  |        DoorOpen         |        DoorClosed       | Dur.OpenSec |
+----------+-------------------------+-------------------------+-------------+
| Door 001 | 2017-03-23 16:50:42.143 | 2017-03-23 16:54:15.813 |         213 |
| Door 001 | 2017-03-23 18:22:37.560 | 2017-03-23 18:28:01.197 |         324 |
| Door 001 | 2017-03-23 18:29:40.593 | 2017-03-23 18:34:53.827 |         313 |
| Door 001 | 2017-03-23 18:42:56.463 | 2017-03-23 18:46:32.550 |         216 |
+----------+-------------------------+-------------------------+-------------+

Can some helpme to make a query that makes the wanted result?


texts: SQL Query is sometimes slow, sometimes fast

texts:
I have two hosts, one with my rails app, one with my MySQL database. Going through my rails Logfile I can see that sometimes queries take much more time than the same query at other times. Think 200ms vs 2ms. The query itself is a select, explain tells me that it uses my index.

The engine is innodb, I already increased the innodb_buffer_pool_size, max_connections and the thread_cache_size. vmstat tells me that there is no paging.

What could be the reason for sometimes slow and sometimes fast queries?


texts: What to do when you need a cursor and you have a columnstore clustered index

texts:
I have a very large SQL Server table with a ColumnStore clustered index.  The table is very fast to query, so this is good.  However I need to do some updates to a large portion of records in the table.  When I tried a conventional update statement, my transaction log filled up.  Normally I would handle this by using a cursor to use an additional part of the composite key so I can update batches and run the update statements in a loop.  However I have just learned via the error message that this won't work on a ColumnStore table with a clustered index.

Can someone please suggest an alternative method for updating the data?

Should I just turn transaction logging off?  That worries me.

One thing I can't do is add more space to my transaction log.  It's already quite large.

declare @ig as cursor;
declare @pname varchar(200)

set @ig = cursor for
select distinct injection_group from mytable where
ProductGroup = 'Widget'
and target_month = '1/1/2018'
open @ig;

fetch next from @ig into @pname;

while @@FETCH_STATUS = 0
begin
    print @pname;
    update
    mytable
    set injection_group = replace(injection_group, '  ', ' ')
    where productgroup = 'Widget'
    and target_month = '1/1/2018'
    and injection_group = @pname
    fetch next from @ig into @pname;
end

close @ig;
deallocate @ig;

  Msg 35370, Level 16, State 1, Line 5
  Cursors are not supported on a table which has a clustered columnstore index.


texts: In which folder / directory does SQL 2008 store files?

texts: Where can I set the folder to store a database in when I create a new Database in the Microsoft SQL 2008 Server?


texts:
I have a table like below: I want to get time duration for each record which SPEED is greater than 100. I written  a function for that, it’s working fine , but my problem was i have millions of records these table so while i am calling these function from a stored procedure it is taking too much time.

Can you please suggest any other option for this..

Here is my table structure and data

---------------------------------------
ID            SPEED          TRACKTIME
---------------------------------------
1              50        2014-10-01 00:20:00
2              80        2014-10-01 00:21:00
3              110       2014-10-01 00:21:30
4              105       2014-10-01 00:22:10
5              97        2014-10-01 00:23:00
6              65        2014-10-01 00:25:00
7              107       2014-10-01 00:26:00
8              101       2014-10-01 00:27:00
9              41        2014-10-01 00:29:00
10             70        2014-10-01 00:30:00
-------------------------------------

My function:

CREATE FUNCTION [dbo].[udf_GetOverspeedMinute]
(
    @vehicleID      INT,
    @starttime      DATETIME,
    @endtime        DATETIME,
    @speedlimit     DECIMAL(18, 2)
)
RETURNS @tmp_overspeed TABLE(ID INT IDENTITY(1, 1), TimeInMinute INT)
AS
BEGIN
    DECLARE @tmp_tracking TABLE (
                ID INT IDENTITY(1, 1) PRIMARY KEY,
                TrackingID INT,
                Speed DECIMAL(18, 2),
                TrackTime DATETIME
            )

    INSERT INTO @tmp_tracking
      (
        TrackingID,
        Speed,
        TrackTime
      )
    SELECT Tracking.ID,
           Speed,
           TrackTime
    FROM   Tracking WITH(NOLOCK)
    WHERE  VehicleID = @vehicleID
           AND TrackTime BETWEEN @starttime AND @endtime

    ;WITH cte_speed AS (
        SELECT RN = ROW_NUMBER() OVER(ORDER BY TrackTime),
               *
        FROM   @tmp_tracking
    )

    INSERT INTO @tmp_overspeed
      (
        TimeInMinute
      )
    SELECT SUM(
               DATEDIFF(MINUTE, [Current Row].TrackTime, [Next Row].TrackTime)
           )
    FROM   cte_speed [Current Row]
           LEFT JOIN cte_speed [Next Row]
                ON  [Next Row].RN = [Current Row].RN + 1
    WHERE  [Current Row].Speed > @speedlimit

    RETURN
END


texts: Get not matching record as null while joining two queries

texts:
I have two queries having same columns, 1 returning 3 rows and other returning 2 rows. While I join this two query I am able to get only 2 rows. How can I get three rows with not matching record as null in sql server?

Query1:

Select UDA.production_date_T, Count (WO.order_state) AS 'cntplan'
From WORK_ORDER WO
INNER JOIN UDA_Order UDA ON UDA.object_key = WO.order_key
group by UDA.production_date_T) TBL1

Query2:

Select UDA.production_date_T, Count (WO.order_state) AS 'ACTUAL'
From WORK_ORDER WO
INNER JOIN UDA_Order UDA
ON UDA.object_key = WO.order_key
WHERE WO.order_state = 'BOOKED'
GROUP BY UDA.production_date_T

My desired Output is:

production_date_T           cntplan     ACTUAL
2019-04-26                       22         4
2019-05-03                        4        NULL
2019-09-05                        19        0


texts: Fixing wildcard expansion resulting in too many terms error (DRG-51030)

texts:
How can I resolve the wildcard_maxterm problem in Oracle 10g?

My index creation syntax is:

begin
    ctx_ddl.drop_preference('SUBSTRING_PREF');
end;
/

begin
    ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');
    ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
end;
/

begin
    ctx_ddl.drop_preference('bus_obj1_lexer');
end;
/

begin
    ctx_ddl.create_preference('bus_obj1_lexer','BASIC_LEXER');
    ctx_ddl.set_attribute('bus_obj1_lexer', 'base_letter', 'YES');
    ctx_ddl.set_attribute('bus_obj1_lexer', 'mixed_case', 'YES' );
    ctx_ddl.set_attribute('bus_obj1_lexer','printjoins', ';,@_');
    ctx_ddl.set_attribute('bus_obj1_lexer','skipjoins', '-');
end;
/

create index BUS_OBJ_FTS_Name on BUSINESS_OBJECTS1(name)
  indextype is ctxsys.context
  parameters ('wordlist SUBSTRING_PREF
               MEMORY 128M
               DATASTORE CTXSYS.DEFAULT_DATASTORE
               SECTION GROUP CTXSYS.AUTO_SECTION_GROUP
               STOPLIST CTXSYS.EMPTY_STOPLIST
               LEXER bus_obj1_lexer
               SYNC (ON COMMIT)');

And my query is:

select * from BUSINESS_OBJECTS1 where contains(name,'%WIN%')>0 and rownum<=100;

There are 15 million rows in that table, and more than 50000 rows match that query. I have set wildcard_maxterm=50000 and default=20000 but I'm still getting this error:

  DRG-51030: wildcard query expansion resulted in too many terms.

Can anybody hep me how to solve this error?


texts: Having md5(NULL) return a non-NULL value in postgresql

texts:
I have a table with the structure (content_md5 UUID, content TEXT), where I'd like to use content_md5 (which is the value of md5(content)) as the primary key, and use it as a foreign key in other tables.

This is a "static" table, where the content (some largish documents) would be referred to by their md5 value for simplicity, and to prevent duplication in the table (which wouldn't be a given with a simple SERIAL PKEY).

However, content can be NULL, which is different from an empty value to declare a non-existing content field in the referencing table.
Since md5(NULL) returns NULL, and NULL is not allowed in a primary key constraint, I'd like to have a way of having md5(NULL) return all zeros instead of NULL.

Example:

-- setup
CREATE TABLE example (content_md5 UUID PRIMARY KEY, content TEXT);
CREATE TABLE test (id SERIAL PRIMARY KEY, tags TEXT, content_md5 UUID REFERENCES example(content_md5) ON DELETE RESTRICT);
INSERT INTO  example VALUES ('00000000000000000000000000000000'::uuid, NULL);

-- usage
INSERT INTO example VALUES (md5('some text')::uuid, 'some text');
INSERT INTO test (tags, content_md5) VALUES ('some content defining tags', md5('some text')::uuid);

SELECT tags, content FROM test LEFT JOIN example USING (content_md5);

-- QUESTION: Having an md5-like function to return zero-filled "md5"/uuid?
INSERT INTO example VALUES (md5(NULL)::uuid, NULL); -- ignored, because already existing record
INSERT INTO test (tags, content_md5) VALUES ('non-existing-document', md5(NULL)::uuid);

Is it possible to somehow cast the returned value to a zero-filled string, create a custom function based on md5() which replaces NULL with 00000000000000000000000000000000, or some other way to achieve this result?

/edit: Or perhaps I don't need any NULL values in this table, and can just set the referencing foreign key column to NULL to achieve the same result?


texts: How to set the first row of CSV data as column name in SQL Server?

texts:
I am using BULK INSERT to read a csv file in SQL Server. Is there a way to keep the first row as column name while I am reading the file?

If not, after reading the data from the csv (from second row), how can I add column names to it?

Any help/comments/suggestions are much appreciated.


texts: For each value in field A, select the row with some special value in field B, while respecting further criteria

texts:
Disclaimer: While the basis of my question—the greatest-n-per-group question—has been asked and also answered so many times before, I am facing a seemingly hard issue due to extra conditions that I have to apply.

In case I really missed the answer to my exact question that was given somewhere, I am sorry. I searched, read, tried, a lot, and I spent already half my day on this.

I tried to describe the abstract problem in the title: for any other field A value, get the row with some special field B value, and at the same time, respect further conditions.

Now, my concrete problem is as follows...
I have the following table:

CREATE TABLE IF NOT EXISTS `mdl_datasets` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `cmid` bigint(10) NOT NULL DEFAULT '0',
  `userid` bigint(10) NOT NULL DEFAULT '0',
  `timecreated` bigint(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

For each user (i.e., userid) in one specific module (i.e., cmid), I want to get the most recent row (i.e., maximum timecreated), before a given timestamp (i.e., again timecreated). The latter part makes it hard.

I don't see how I can do this without using at least three (sub)queries, and even then I'm unable to make it work properly in any case.

So far, I only managed to get it partially working, for example, only for users with at least two rows. This is wrong, of course. If there is a user with only a single row, which has a correct timecreated value, then I want to get this row as a result. For any other user, I want to get the one row with the greatest timecreated value that is below the given maximum date (as Unix epoch timestamp).

Is there anything else that I should mention? Tell me.


texts: How to generate TPC-DS query for sql-server from templates

texts: I know that dsqgen is used to transform the query templates into executable SQL. But when I type in the command line: "dsqgen /directory query_templates /template query1.tpl /dialect sqlserver /scale 5", there's an error that "Substitution 'STATE' is used before being initialized". I also copied the ansi.tpl and tpcds.idx files in the same directory as dsqgen.exe because dsqgen always read these two files. Anyone has any idea how can I solve this? Thanks in advance.


texts: Can I MERGE INTO ... INSERT with a column list in Hive?

texts:
Here's a good example of a MERGE statement:

MERGE INTO target AS T
USING source AS S
ON T.ID = S.ID and T.tran_time = S.tran_time
WHEN MATCHED UPDATE SET quantity = S.quantity
WHEN MATCHED AND S.quantity IS NULL THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.quantity, S.tran_time);

When doing the INSERT, does it have to exactly match the structure of the target table, or can a column list be somehow be specified like in a simple INSERT statement? I don't like hard coding value lists that have to match the order of the columns in the table, I like my statements to be column order independent.


texts: Email Column Constraint to Contain '@' character

texts: How do you create an inline constraint for a Email Address Field that requires there be an "@" character? Pretty much I want there to be a restriction to the field so that the data entered must have the "@" within it. I'm using Oracle SQL Developer 12c.


texts:
I couldn't find any topic with my question, so I created my own question.

I have a web application with big database which has not lot's of Update and Insert but has so many search (Select).

This application uses MySQL but as there are some tables which be better to be implemented by NoSQL database, so I want to change the database and convert the data to NoSQL database, but I know that it's cause problem in term of speed, when the users search into the database.

My Question: Is there any way to use SQL database not NoSQL database for almost all the data, but for some columns NoSQL?

Simply saying, about 85% of the database columns are fix but there are some columns who are not fix and they are related to the the record, ex: if the current record is bird, we need a column can_fly but if it's a quadruped, we need the column is_mammal.

Thanks


texts: SSIS Packages with SQL Server Express 2008 R2

texts:
I understand from various questions [1] [2] that SQL Server Express 2008 R2 doesn't support SSIS packages and that there's only primitive support for ad-hoc data imports / exports.

I have already got SQL Server 2008 Client tools (not express version) installed for use with an instance of SQL Server Standard edition.

Will I be able to use BIDS and SSIS to create packages for use between the Standard edition of SQL Server and the Express instance of SQL Server?


texts: Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility?

texts:
We're migrating from postgres 8.1 to 9.3, since we need streaming replication badly. All the application developers have deserted the company a long time ago, way before my time here, so there is really no one who knows what's going on inside the applications. We've been treating them as black boxes.

As a strategy to migrate and test the applications compatibility with postgres 9.3, I'm considering:

Do a pg_dump to have a starting point.
Log all SQL statements.
Replay all the logged SQL statements to the new server from the point were I did the
pg_dump. This will take of synchronization, and...
Check the response of the new server to the statements fed on (3), to see if It can
digest all the SQL statements that will be sent to it. This will take care of "input"
compatibility. Once we're satisfied with the "input" compatibility, and the new
server is up to date, then...
Pick a good sample of the SELECT commands from the logs, replay them to both servers,
capture the output on files, compare the files. This would take care of "output"
compatibility.

This would run over 6 months, to make sure there will be no surprises. If you see any holes in my plan, feel free to point them out :)

The only doubtI have is about the order in which the statements appear in the log file:

Do the statements in the log file appear in the same order as they do in the WAL file?

Thanks


texts:
update line_items set product_id = dups.latest_product_id
    from  line_items as li
    join products p on li.product_id = p.id
join vendors v on p.vendor_id = v.id
    join vendorgroups vg on v.vendorgroup_id = vg.id
    join duplicate_product_sets dups
      on dups.host_id = vg.host_id and dups.vendor_id = v.id
     and dups.invtid = p.invtid and dups.latest_product_id <> p.id

I have dropped indexes on line_items (except the primary key) because I figured since it is the table being updated, indexes would slow down the update.

Other than that, I have indexes on these columns (in addition to primary keys):

products.invtid
products.vendor_id
vendors.vendorroup_id
vendorgroups.host_id
duplicate_product_sets.host_id
duplicate_product_sets.vendor_id
duplicate_product_sets.invtid
duplicate_product_sets.latest_product_id

The result of EXPLAIN:

-------------------------------------------------------------------------------------------------------------------------------
 Update on line_items  (cost=5444.25..5004105.00 rows=397508809 width=753)
   ->  Nested Loop  (cost=5444.25..5004105.00 rows=397508809 width=753)
         ->  Seq Scan on line_items  (cost=0.00..12944.60 rows=434160 width=719)
         ->  Materialize  (cost=5444.25..20030.69 rows=916 width=34)
               ->  Hash Join  (cost=5444.25..20026.11 rows=916 width=34)
                     Hash Cond: (li.product_id = p.id)
                     ->  Seq Scan on line_items li  (cost=0.00..12944.60 rows=434160 width=10)
                     ->  Hash  (cost=5433.62..5433.62 rows=851 width=32)
                           ->  Nested Loop  (cost=147.90..5433.62 rows=851 width=32)
                                 Join Filter: (v.id = p.vendor_id)
                                 ->  Hash Join  (cost=147.47..1253.41 rows=700 width=40)
                                       Hash Cond: ((dups.vendor_id = v.id) AND (dups.host_id = vg.host_id))
                                       ->  Seq Scan on duplicate_product_sets dups  (cost=0.00..706.97 rows=39197 width=28)
                                       ->  Hash  (cost=117.37..117.37 rows=2007 width=20)
                                             ->  Hash Join  (cost=36.70..117.37 rows=2007 width=20)
                                                   Hash Cond: (v.vendorgroup_id = vg.id)
                                                   ->  Seq Scan on vendors v  (cost=0.00..53.07 rows=2007 width=14)
                                                   ->  Hash  (cost=24.09..24.09 rows=1009 width=14)
                                                         ->  Seq Scan on vendorgroups vg  (cost=0.00..24.09 rows=1009 width=14)
                                 ->  Index Scan using products_by_invtid on products p  (cost=0.42..5.96 rows=1 width=24)
                                       Index Cond: ((invtid)::text = (dups.invtid)::text)
                                       Filter: ((dups.latest_product_id <> id) AND (dups.vendor_id = vendor_id))
(22 rows)

I am using Postgres 9.4.


texts:
I have a query to retrieve the Top 10 queries based on total logical reads from the dm_exec_query_stats DMV.

SELECT TOP 10
    SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(qt.TEXT)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1),
db_name(qt.dbid) as db_name,
qs.execution_count,
qs.total_logical_reads,
qs.total_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
SUBSTRING(CONVERT(varchar(19),qs.last_execution_time),1,19)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC

The query returns all of the information requested, except for the name of the database that the query came from or was directed at.  Whether I use dm_exec_sql_text or dm_exec_query_plan the result is the same.

  db_name(qt.dbid) as db_name  dm_exec_sql_text

or

  db_name(qp.dbid) as db_name  dm_exec_query_plan

Both return NULL or tempdb as the database name.

The same problem occurs when selecting Reports -> Performance Top Queries by Average IO.

The database name is empty.

If I add the query plan to the query however, and then open up the query plan in SSMS, I can see the name of the originating database by hovering over the various Index Seeks, Scans or RID lookups.

I have noticed that there are several databases referenced in the query plan, such as mssqlsystemresource along with the trackit database

If the query plan was able to display the name of the database or databases affected by the query in my top 10 list, then it stands to reason that I should be able to obtain the names of those databases using a DMV.

How can I modify the top 10 query to retrieve the name of the database for each query?

Or is there a better way to go about getting the top 10 queries by CPU/IO/Memory usage and get the database name or names for each of the top 10?


texts: Can text fields be not loaded for join memory purposes in MySQL?

texts:
I want to use many text fields for a table but they can be very large so doing complex operations with rows of this table, like joins with big tables can become intractable or very slow
I understand that text field are not stored in memory so they forbid their rows from being cached, so I would like to switch off text field loading from disc making the rows of this table remain in fast memory and allowing load text fiels "manual loading" when necessary
Is this possible?


texts: MSDB - Backup information is not updating

texts:
I'am having a problem in my database

My backup job is working correctly, but the tables of msdb is not updating the information.

Here is my case, if you see, My last transaction log backup was in 2020/05/23 05:00:00 in my job history:

But in the msdb tables like msdb.dbo.backupset and msdb.dbo.backupediafamily is not updating.. the last backup was in 2020/05/22 01:00:00:

How can I resolve it?


texts:
Below is the log Sequence number of the log back ups and full back ups

Why are LSN numbers same from 11.30 to 11.45 pm...Was it due to the fact that there was no changes made ? When can i find out that the log sequence has been broken ?

11.30 pm Log first LSN:  325:31307:1,   last LSN: 325:31307:1
11.45 pm Log first LSN:  325:31307:1,   last LSN: 325:31307:1
12.00 am Full first LSN: 325:31409:239, last LSN: 325:31507:1
01.00 AM lOG first LSN:  325:31307:1,   last LSN: 325:42530:1


texts:
I have the following query:

SELECT
    [ID] AS [NewID]
    , (
       CASE WHEN LEN([MiddleName]) > 0 THEN
          [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.'
       ELSE
          [LastName] + ', ' + [FirstName]
       END
    ) AS [Name]
    , [Title] AS [ProfTitle]
    , CONVERT(CHAR(10),[DepartureDate],111) AS [BorDepart]
    , [LocationOfficeNumber] AS [OfficePhone]
    , [Location]
    , [LocationSection] AS [Sec]
    , [PracticeGroup] AS [PracticeTL]
    , [Email]
FROM [dbo].[Borrowers]

With that, I'd like to find out how I can write a FOR XML query that translate to this output:

<sydney>
    <import>
        <template id="Borrower" name="Borrowers">
            <record searchfield="NewID" searchvalue="NewID">
                <field id="NewID">
                    <![CDATA[NEWID]]>
                </field>
                <field id="Name">
                    <![CDATA[NAME]]>
                </field>
                <field id="ProfTitle">
                    <![CDATA[Professional Title]]>
                </field>
                <field id="BorDepart">
                    <![CDATA[YYYY/MM/DD]]>
                </field>
                <field id="OfficePhone">
                    <![CDATA[office phone]]>
                </field>
                <link id="Location" linkfield="Code">
                    <![CDATA[011]]>
                </link>
                <link id="Sec" linkfield="Term">
                    <![CDATA[SECTION]]>
                </link>
                <link id="PracticeTL" linkfield="Term">
                    <![CDATA[SUBSECTION]]>
                </link>
                <field id="Email">
                    <![CDATA[email]]>
                </field>
            </record>
        </template>
    </import>
</sydney>

I've tried all kinds of combinations with FOR XML PATH and FOR XML EXPLICIT and cannot find any answer to it. The closest I came is with this:

SELECT (
    SELECT
       'Borrower' AS '@id'
       , 'Borrowers' AS '@name'
       , (
          SELECT
             'NewID' AS '@searchfield'
             , 'NewID' AS '@searchvalue'
             , [ID] AS 'NewID' --AS [Field!1!CDATA]

             , (
                CASE WHEN LEN([MiddleName]) > 0 THEN
                    [LastName] + ', ' + [FirstName] + ' ' + LEFT([MiddleName],1) + '.'
                ELSE
                    [LastName] + ', ' + [FirstName]
                END
             ) AS [Name]
             , [Title] AS [ProfTitle]
             , CONVERT(CHAR(10),[DepartureDate],111) AS [BorDepart]
             , [LocationOfficeNumber] AS [OfficePhone]
             , [Location]
             , [LocationSection] AS [Sec]
             , [PracticeGroup] AS [PracticeTL]
             , [Email]
          FROM [dbo].[Borrowers]
          FOR XML PATH('record'), TYPE
       )
    FOR XML PATH('template'), TYPE
) AS 'import'
FOR XML PATH('sydney'), TYPE

Please any help is welcome since I've been stuck with this for a week and cannot find anything.

P.S.: Here's the schema for the Borrowers table:

Cheers,

Alvaro Costa


texts: What are best practices for safely permanently deleting a database?

texts:
We have an "organic" environment, meaning people piled code on code for ten years with minimal oversight or documentation.  The server I use has several databases which I believe are no longer being used; I'd love to delete them and leave just the three I actually use.

At the reckless extreme, I could disable these databases and wait for someone to scream; at the other I could leave them running forever "just in case".  What steps have you found valuable in identifying whether a server is being used, and how?

Also, what steps would you recommend to ensure that, as one moves forward in disabling systems, that they remain conveniently reversible for a period of time (e.g., rename objects rather than deleting them outright)?

Thanks!


texts: Optimizing slow SELECT query (postgresql 9.6.5)

texts:
In an app called Links, users post interesting content in a forum like interface, and others post replies or comments under this publicly posted content.

These posted replies are saved in a postgresql table named links_publicreply (postgresql 9.6.5 DB).

One query run on the table keep on cropping up in the slow_log (greater than 500ms). This relates to showing the most-recent 25 public replies accumulated under a given piece of shared content.

Here's a sample from the slow log:

LOG:  duration: 1614.030 ms  statement:

 SELECT "links_publicreply"."id",
       "links_publicreply"."submitted_by_id",
       "links_publicreply"."answer_to_id",
       "links_publicreply"."submitted_on",
       "links_publicreply"."description",
       "links_publicreply"."abuse",
       "auth_user"."id",
       "auth_user"."username",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar",
       "links_link"."id",
       "links_link"."description",
       "links_link"."submitter_id",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."latest_reply_id"
FROM   "links_publicreply"
       INNER JOIN "links_link"
               ON ( "links_publicreply"."answer_to_id" = "links_link"."id" )
       INNER JOIN "auth_user"
               ON ( "links_publicreply"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_publicreply"."answer_to_id" = 8936203
ORDER  BY "links_publicreply"."id" DESC
LIMIT  25

Here are the explain analyze results of the said query: https://explain.depesz.com/s/pVZ5

According to that, ~70% of the query time seems to be taken up by index scan. But to an accidental DBA like myself, it isn't immediately obvious what optimization I can do to make this more performant. Perhaps a composite index on links_publicreply.answer_to_id, links_publicreply.id?

It would greatly help me learn if a domain expert can furnish guidance + intuition on solving this class of problem.

P.s. \d links_publicreply is:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE


texts: Full text index does not honour deleted words - They are still indexed

texts: I have a full text index defined as 'Automatic' radio button checked on in its Catalog. When I modify a word in the full text indexed column, the new word appears in the sys.dm_fts_index_keywords but the old,updated value does not disappear and still shows in the list. It is also true when a word is deleted from the column as well. When I rebuild the catalog though the old (or deleted) word drops off the list. It looks like full text indexes does not honour deleted words. Why would that be?


texts: Why do information_schema queries scan all databases?

texts:
I'm building a PHP application that looks up the last modified date of all databases using information_schema.  A simplified version is like this:

$sql = "SELECT update_time FROM information_schema.tables;

I have about 300 databases and this loop takes about 3.1 seconds, so I want to speed this up.  I noticed with Explain that any query on information_schema scans all databases, so I'm guessing this is why it's slow.  Is there any way to fix this?  I'm using the latest MariaDB 10.3.14.

MariaDB [(none)]> EXPLAIN SELECT update_time FROM information_schema.tables\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tables
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Open_full_table; Scanned all databases
1 row in set (0.000 sec)

EDIT:  I tried MySQL 8.0 and now the loop takes 0.03 seconds.  Apparently this is a bug, but is there any fix for MariaDB? https://bugs.mysql.com/bug.php?id=81347


texts: How to compare between two values in one field depending on another field in SQL* / Oracle Reports?

texts:
I have three fields in a table named table_family.

One is "relation" it has 3 kinds of values 1 for person, 2 for parents, 3 for son.

Other field is "age" and it contains the ages of all the relations in the first table.

The 3rd field is for the family number.

Now the relation could be repeated like four sons and two parents for the same family
This is an example:

Family   Relation   Age

1         1          25

1         2          40

1         2          35

1         3          30

1         3          28

1         3          29

2         1          30

2         2          40

2         3          12

Now I want to build a report that displays an error which is:
Whenever the difference between parent (relation=2) and son (relation=3) is less than 12, then this is an error.

I tried this code :

select family from table_family
group by family
having
SELECT  family

FROM table_FAMILY

GROUP BY

     family
HAVING
((
 MAX(DECODE ( relation,2,age ))
-  MAX(DECODE ( relation,3,age ))
 )
 <12 )
 OR
 (
 (SELECT MIN(age) FROM table_FAMILY WHERE relation=2) -(SELECT MIN(age) FROM       table_FAMILY WHERE relation=3) < 12

 )

If I use this code then only the maximum and minimum values will be compared. The values in between will be ignored but I need them to be compared in the same way.

The output report will be something like:

family  difference between parent and son

1               10

1               12

1               11

1                5

1                7

1                6

This what I need in the final report

For family = 1 I subtracted parent age - son age =

40-30=10

40-28=12

40-29=11

35-30=5

35-28=7

35-29=6

But for the 2nd family

40-12= 28 >12 then should not be listed (not an error).

Any help please?


texts: import geonames allCountries.txt into MySQL 5.7 using LOAD INFILE - ERROR 1300 (HY000)

texts:
I'm trying to import the allCountries.txt file from http://download.geonames.org into MySQL 5.7 using LOAD INFILE
but getting the following error:

  ERROR 1300 (HY000): Invalid utf8 character string:
  ''Afikanisitani,'Apekanikana,A Phu Han (Afghanistan),A Phú Hãn '

procedure:
- download & unzip the allCountries.zip from   http://download.geonames.org/export/dump/
- create a table in mysql / phpmyadmin / MySQLWorkbench for the import:

CREATE TABLE `geoname` (
 `geonameid` int,
 `name` varchar(200),
 `asciiname` varchar(200),
 `alternatenames` varchar(4000),
 `latitude` float,
 `longitude` float,
 `feature_class` char(1),
 `feature_code` varchar(10),
 `country_code` varchar(2),
 `cc2` varchar(60),
 `admin1_code` varchar(20),
 `admin2_code` varchar(80),
 `admin3_code` varchar(20),
 `admin4_code` varchar(20),
 `population` int,
 `elevation` int,
 `dem` int,
 `timezone` varchar(40),
 `modification_date` date,
 `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY
) CHARACTER SET utf8;

- run the following command to import the .txt file:
LOAD DATA LOCAL INFILE '/location/of/my/allCountries.txt' INTO TABLE geoname;

This procedure worked fine in MySQL 5.6 - but fails with the aforementioned error in MySQL 5.7.13


texts: 19c Impdp hangs after SCHEMA_EXPORT/VIEW/VIEW with cpu 100%

texts:
I've a impdp job on Oracle 19c always hanging with 100% CPU (ora_dw00_orclcd process) after the message below:

Elaborazione dell'object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Elaborazione dell'object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Elaborazione dell'object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Elaborazione dell'object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Elaborazione dell'object type SCHEMA_EXPORT/VIEW/VIEW

Very strange for me is the output of select NAME,SQL_TEXT from dba_resumable;:

SYSTEM.SYS_IMPORT_SCHEMA_01.1   (null)
SYSTEM.SYS_IMPORT_SCHEMA_01     BEGIN :1 := sys.kupc$que_int.receive(:2); END;

It's a datapump exported from a 11.2.0.1 oracle database with COMPRESSION=ALL and not Full (SCHEMAS=MYSCHEMA1 MYSCHEMA2 MYSCHEMA3)

Any hints?

EDIT2:

top output is:

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
  6313 oracle    20   0 2669888   1,3g 744812 R  91,7 23,9  81:32.14 ora_dw00_ORCLCDB

And this query to find running statement returns null

select
s.username su,
substr(sa.sql_text,1,540) txt,
s.event,
s.SID
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=6313;

"SU"    "TXT"   "EVENT" "SID"
"SYSTEM"    ""  "db file sequential read"   276

Impdp status after 18 hours:

Import> status

Job: SYS_IMPORT_SCHEMA_01
  Operation: IMPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 8,481,046,744
  Percent Done: 14
  Current Parallelism: 2
  Job Error Count: 0
  Job heartbeat: 904
  Dump File: /u01/DMP/my.dmp

Worker 1 Status:
  Instance ID: 1
  Instance name: ORCLCDB
  Host name: lnx-db
  Object start time: Wednesday, 29 April, 2020 14:48:35
  Object status at: Wednesday, 29 April, 2020 14:48:35
  Process Name: DW00
  State: EXECUTING
  Object Schema: MYSCHEMA
  Object Name: V_MYVIEW_xxx
  Object Type: SCHEMA_EXPORT/VIEW/VIEW
  Completed Objects: 80
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: ORCLCDB
  Host name: lnx-db
  Object start time: Wednesday, 29 April, 2020 14:48:13
  Object status at: Wednesday, 29 April, 2020 14:48:31
  Process Name: DW01
  State: WORK WAITING


texts: Teradata 15 : Failure 5619 Role nesting exceeded single-level

texts:
I am using Teradata 15.  In Active Directory, We have a policy to assign a universal group, which has people, to a local group, where permissions are assigned. So I create two Teradata roles to mimic this structure.

I granted one role to another role:

grant role_1 to role_2;

but I got the following error.

*** Failure 5619 Role nesting exceeded single-level.

I am wondering if I need to create special type of roles to identify a parent and a child relationship first?

Your help is greatly appreciated.


texts: Need both a Readable Secondary and High Availability

texts:
I am doing research into how to have both a readable secondary database that we can use for offloading heavy queries (such a reports) and also (ideally) use as highly available backup.  We are a VERY small shop, and we do not have the option of hiring a dedicated DBA.  We need a solution that is low maintenance.

We went down the road of Transactional Replication. We have it configured on our staging servers, but we've hit some issues.  (Short version of the issues: the subscriber database is sllloowwww. And, making schema changes in the publisher is a serious PIA.) I am second guessing my decision to use Transactional Replication.  When I began to research the solution to these problems, it became clear that managing Replication is a much larger task than originally anticipated. It has us a bit spooked.

We are already doing nightly off-sight backups in another region of the country.  We're good for pure DR.  It's HA and a readable secondary that we lack.

Here are my questions:

For a small shop with no DBA, what solution(s) should we consider?  Is there one single solution that will provide both a readable secondary and HA? Or should we be looking at two different solutions? Will Always On provide both?  Should we push forward with Replication?

What would you do?


texts: ERROR 1290 MYSQL secure-file-priv option

texts:
I have a database which I have been LOADING text files, these were stored in /tmp.

for reasons of permits however this morning when I turn the computer the files were no longer in that folder and attempt the LOAD gives me the next

  ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it can not execute esta statement

I had any problems to use the LOAD so far what could be?


texts: How to read multiple record from xml to insert in table in SQL?

texts:
DECLARE @tbl_XMLResult table
(
    Status varchar(50),
    Address varchar(100),
    ListPrice varchar(25),
    SoldPrice varchar(25),
    YearBuilt varchar(25),
    SF varchar(25),
    Acres varchar(25),
    OnMarkDate varchar(25),
    PendDate varchar(25),
    SaleDate varchar(25),
    DOM varchar(25)
)

DECLARE @XMLRESULT xml

exec usp_ReportResults @query = "759,905,1048,170,725,80129", @ReportName = "GenReport", @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult(Status, Address, ListPrice, SoldPrice,
        YearBuilt, SF, Acres, OnMarkDate, PendDate, SaleDate, DOM)
    SELECT
        Listing.value('(/ListingStatus/text())[1]', 'varchar(50)') AS Status,
        ReportData.value('(/Listing_StreetAddress/text())[1]', 'varchar(50)') AS Address,
        Listing.value('(/ListPrice/text())[1]', 'varchar(50)') AS ListPrice,
        Listing.value('(/ClosePrice/text())[1]', 'varchar(50)')AS SoldPrice,
        Listing.value('(/YearBuilt/text())[1]', 'varchar(50)') AS YearBuilt,
        Listing.value('(/TotalLvSpace/text())[1]', 'varchar(50)') AS SF,
        Listing.value('(/AcresApx/text())[1]', 'varchar(50)') AS Acres,
        Listing.value('(/ListDate/text())[1]', 'varchar(50)') OnMarkDate,
        Listing.value('(/PendingDate/text())[1]', 'varchar(50)') AS PendDate,
        Listing.value('(/CloseDate/text())[1]', 'varchar(50)') AS SaleDate,
        Listing.value('(/DaysOnMarket/text())[1]', 'varchar(50)') DOM
    FROM
        @XMLRESULT.nodes('/Results/Report/Listings/Listing') AS ListingTable(Listing),
        @XMLRESULT.nodes('/Results/Report/Listings/Listing/PMCReportData') AS TempTable(ReportData)

SELECT *
FROM @tbl_XMLResult

I am trying to get result from stored procedure as in xml format and by reading that result store it into the temporary table created above. But I'm getting result as null and current stored procedure returning only six records in xml but it showing 36 rows in temporary table with null values in it. How to fix that?

This is the xml format:


texts: Last occurance of time relative to now()

texts:
What's the most elegant way to ask Postgres for the last 4:05pm relative to now()?

I have been writing a fairly elaborate CASE with date_trunc's and my guess is it can be accomplished in a much cleaner manner.

I speak with reference to no table. i.e. There is no FROM in this query. Just a simple SELECT.

The result of this is either a TIMESTAMP at 4:05pm today, or yesterday.


texts: How to update the parent/child of all rows in the tree (ltree)?

texts:
Here are my configurations:
DB type: postgresql (9.3 if that's necessary)
Table name: product_sections
Column names: section_id (integer) & section_path (ltree)

Detail:
I have one reference value: section_id: 15 & section_path: Automation_Solutionz. So, when I want to rename Automation_Solutionz to, say Chrome, I want all references of Automation_Solutionz to be updated.

FROM:

Automation_Solutionz
Automation_Solutionz.Test_Case_Creation
Automation_Solutionz.Test_Case_Creation.Automation
Automation_Solutionz.Test_Case_Creation.Manual

TO:

Chrome
Chrome.Test_Case_Creation
Chrome.Test_Case_Creation.Automation
Chrome.Test_Case_Creation.Manual

Similarly, when I update a value which is in the middle, it should rename those too. In the case above, Test_Case_Creation should be renamed to something like TestCase or anything else. The same goes for any item which is either in the middle, end or at the start.

Hope, I explained things correctly. Thanks in advance :)


texts: oracle null value in quotes - yet another newbie question

texts:
I have a where clause like

"and uc.completion_status in ('[P....]') "

here ('[P....]') is the user input from taken a dropdown list in the user interface. and dropdown list consists three options Y, N or NotStarted

not started is defined with nvl(uc.completion_status, 'NotStarted') therefore NotStarted is null actually. and null in quotes is not working. how can this problem can be handled?

thanks in advance.


texts: EXCEPT operator query to differentiate between new and changed rows

texts:
I am using the EXCEPT Operator to compare data between two tables (as part of a final import I want to compare "today's data" with "yesterday's data" and only finally import new and changed rows.)

It (EXCEPT) seems to be exactly what I need, but does anyone know a way I could extend it so that in the resulting data subset I was able to differentiate between rows that are new and rows that are changed?


texts: How to Shard a Mongodb Collection based on String and Date Time Shard Key using Range Based Sharding

texts:
I am new to Mongodb,I have to Shard a Collection Based on Integer, String and Date time shard key, I am able to shard a collection using integer shard key. below is the command,I am using for Range Based Sharding.

mongos> use admin
switched to db admin
mongos> db.runCommand({split:"poc_db.test_sharding",middle:{docId:500}})
{ "ok" : 1 }
mongos> db.runCommand({movechunk:"poc_db.test_sharding",find:{docId:1},to:"shard0000"})
{ "millis" : 7288, "ok" : 1 }
mongos> db.runCommand({movechunk:"poc_barclays.db",find:{docId:500},to:"shard0001"})
{ "millis" : 1709, "ok" : 1 }

So what if docId is String or DateTime value, How to set Range?

Ex: ^^#aaaaaah or 3000-01-01 00:00:00.000

Any solution?


texts:
First Master Table

CREATE TABLE `edipostingmaster` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `applied` int(11) DEFAULT '0',
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `checkDate` date DEFAULT NULL,
  `checkCount` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unqiue` (`checkNo`,`fileName`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8

First Detail Table

CREATE TABLE `edipostingclaims` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `applied` int(11) DEFAULT '0',
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `claimSno` int(11) NOT NULL,
  `claimNo` varchar(100) DEFAULT NULL,
  `claimExists` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UNIQUE` (`fileName`,`checkNo`,`claimSno`)
) ENGINE=InnoDB AUTO_INCREMENT=385 DEFAULT CHARSET=utf8

Second detail table

CREATE TABLE `edipostingcodes` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `fileName` varchar(255) DEFAULT NULL,
  `checkNo` varchar(100) DEFAULT NULL,
  `claimSno` int(11) DEFAULT NULL,
  `claimNo` varchar(100) DEFAULT NULL,
  `lineItemNo` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `unique` (`checkNo`,`claimSno`,`fileName`,`lineItemNo`)
) ENGINE=InnoDB AUTO_INCREMENT=1289 DEFAULT CHARSET=utf8

Here is the query

EXPLAIN
SELECT * FROM edipostingmaster a  , edipostingclaims b, edipostingclaims c
WHERE a.fileName = b.fileName AND a.checkNo = b.checkNo
AND b.fileName = c.fileName AND b.checkNo = c.checkNo AND b.claimSno = c.claimSno
AND a.checkNo ='893881996'
ORDER BY b.claimSno

Now explain showing Using where; Using temporary; Using filesort for table a.
Without order by, it is fine.


texts:
I have a production enviroment in a Oracle 10g database which I would like to authomatically duplicate in a test enviroment. My production enviroment is just a number of tables in which I have usually indices, constrains and triggers.
Is there a way to authomatically make a copy of the prod-enviroment including all tables, indices, triggers and constrains without the data in the tables?
Thanks in advance for your help.


texts: MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB

texts:
I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB.  I converted all row formats to dynamic first for all the tables to be on Barracuda then running alter table <tablename> engine = InnoDB for 16 tables.  12 of the 16 tables changed file formats as well without an alter table command.  I am at a loss to understand this.  I think this may be related to the .frm files, but I'm not sure how.  I've checked environment variables:

innodb_file_format is showing Barracuda

innodb_file_format_check is ON

A couple of the tables: Articletranslations is showing as compressed, pubmedabstractauthors and pubmedtranslated are showing as compact.
The create table statements from tables that I had changed to dynamic file format before changing the storage engine to InnoDB.

Table: articletranslations

Create Table: CREATE TABLE `articletranslations` (
  `TranslationID` int(11) NOT NULL AUTO_INCREMENT,
  `ArticleID` int(11) NOT NULL,
  `language` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `TextContent` longtext COLLATE utf8_unicode_ci,
  `Name` text COLLATE utf8_unicode_ci,
  `Tags` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Detail_Abstract` longtext COLLATE utf8_unicode_ci,
  `Disclosures` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Discussion` longtext COLLATE utf8_unicode_ci,
  `Acknowledgements` longtext COLLATE utf8_unicode_ci,
  `D` longtext COLLATE utf8_unicode_ci,
  `Materials` text COLLATE utf8_unicode_ci,
  `HTMLTopContent` text COLLATE utf8_unicode_ci,
  `Rep_Results` longtext COLLATE utf8_unicode_ci,
  `Introduction` text COLLATE utf8_unicode_ci,
  `IsMachine` tinyint(1) NOT NULL DEFAULT '1',
  `DateTranslated` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TranslationID`),
  KEY `ArticleTranslations_Language_ArticleID` (`language`,`ArticleID`),
  KEY `ArticleTranslations_ArticleID` (`ArticleID`)
) ENGINE=InnoDB AUTO_INCREMENT=177437 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

Table: pubmedabstractauthors

Create Table: CREATE TABLE `pubmedabstractauthors` (
  `AuthorID` int(11) NOT NULL AUTO_INCREMENT,
  `ForeName` varchar(255) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `Initials` varchar(255) NOT NULL,
  PRIMARY KEY (`AuthorID`),
  KEY `names` (`ForeName`,`LastName`,`Initials`)
) ENGINE=InnoDB AUTO_INCREMENT=712515 DEFAULT CHARSET=latin1

Table: pubmedtranslated

Create Table: CREATE TABLE `pubmedtranslated` (
  `PMID` int(11) NOT NULL,
  `ArticleTitle` text COLLATE utf8_unicode_ci NOT NULL,
  `ArticleAbstract` text COLLATE utf8_unicode_ci NOT NULL,
  `LanguageID` smallint(6) NOT NULL,
  PRIMARY KEY (`PMID`,`LanguageID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


texts: MS SQL Server Error: Replication components are not installed on this server on Ubuntu

texts:
We have a Windows 10 SQL Server 2014 Standard License I'm using on our local computer. Trying to replicate a database to an Ubuntu Server 18.x with SQL Server 2018 (installed using this easy guide).

I've completed the replication steps on my local PC (using this guide), but when I try to setup Subscription using SSMS 2018 I get the error below

Replication components are not installed on this server on Ubuntu

I've tried:

Reinstalling the SQL Server on Ubuntu as recommended by most Microsoft forums  like here and here to no avail. The instructions point to a command with .exe:

You need to run setup again and install the replication components. You can do this from the command line like this:

setup.exe /q /Features=Replication /InstanceName=MSSQLSERVER /ACTION=INSTALL /IAcceptSQLServerLicenseTerms

Edit 2: This says Replication isnt supported on Linux SQL Server 2017


texts: Storing NULL Values in large VARCHAR fields

texts:
I have a table that represents a "note". This note has things like a title and various other meta data. However, I'm trying to figure out the best practice for storing the contents of the note.

Currently, my strategy is to have the contents of the note be a VARCHAR(10000) and just store a NULL value if there are no contents. But, I'm not sure if that's the right thing to do, or if this will cause performance or storage issues.

I am very new to constructing databases - am I going about this the right way?


texts:
I am having MySQL 5.1 installed on windows box. Now I would like to upgrade to latest MySQL 5.6 version.

I am planning to install MySQL 5.6 in the same server with same port 3306. After installation, I will map the new installation data directory to the older data directory which is used by MySQL 5.1.

After this, I will upgrade using mysql_upgrade.exe.

Will this plan work out or not ? Also let me know if any precautions to be taken care.

Edit: This is production server and the DB is of huge size 100 GB, so we are not able to go with backup and restore mechanism which is time consuming process.


texts: SQLite - Remove duplicates across multiple columns

texts:
I have the following table:

C1 | C2 | C3
-------------
A | X | 1
A | Y | 2
B | X | 3
B | Y | 4

I want to deduplicate across columns 1 and 2, and select the max from column 3. In case, since rows 1 and 2 both have "A" in column 1, those are a duplicate. Since rows 3 and 4 both have "B" in column 1, those are a duplicate. Since rows 1 and 3 both have "X" in column 2, those are a duplicate. Finally, since rows 2 and 4 both have "Y" in column 2, those are a duplicate. Hence the first four rows would be considered a duplicate, and the result should return row 4, since that contains the maximum in column 3. I cannot figure out how to deduplicate across multiple columns. Any advice would be appreciated.


texts: Oracle Global Statistics preference NO_INVALIDATE

texts:
From the documentation, NO_INVALIDATE is a Global statistics preference Parameter.

  ..controls the invalidation of dependent cursors of the tables for which statistics are being gathered. It does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have the system decide when to invalidate dependent cursors. This is the default.

My question is what is the meaning of dependent cursors of the table?


texts: MongoDB shard keeps growing, I don't know why?

texts:
I've had a project dumped on me and I'm trying to get up to speed withMongo.   But I have an immediate firefight, in that one shard in my Mongo cluster just keeps growing and growing, adding more files daily, and I can't find the cause.   Our cluster is simple, 12 sharded replica sets.   "rs1" is the primary shard, and it's the one that keeps gobbling up more and more disk space while the other 11 shards haven't grown at all.    Our data is simple too, we just create 3 collections per day of our 3 document types.  And we keep those for 90 days.  So roughly at any time we have about 270 or so active collections, and nightly we drop the oldest collections as new collections are created for upcoming days.

The balancer seems to be running fine, I can see the chunk counts getting balanced in the collections normally.

Is there some way I can STOP mongo from sending any more data to my problem shard while I get a handle on what's going on?   I've already tried setting a maxSize, but that hasn't prevented it from still growing.   Right now rs1 is taking up about 885 2gb disk files.   (note that this is Mongo 2.6)

  shards:
      {  "_id" : "rs1",  "host" : "rs1/psc1c250:39080,psc2c250:39080",  "maxSize" : 1500000 }
    {  "_id" : "rs10",  "host" : "rs10/psc1c259:39080,psc2c259:39080" }
    {  "_id" : "rs11",  "host" : "rs11/psc1c260:39080,psc2c260:39080" }
    {  "_id" : "rs12",  "host" : "rs12/psc1c261:39080,psc2c261:39080" }
    {  "_id" : "rs2",  "host" : "rs2/psc1c251:39080,psc2c251:39080" }
    {  "_id" : "rs3",  "host" : "rs3/psc1c252:39080,psc2c252:39080" }
    {  "_id" : "rs4",  "host" : "rs4/psc1c253:39080,psc2c253:39080" }
    {  "_id" : "rs5",  "host" : "rs5/psc1c254:39080,psc2c254:39080" }
    {  "_id" : "rs6",  "host" : "rs6/psc1c255:39080,psc2c255:39080" }
    {  "_id" : "rs7",  "host" : "rs7/psc1c256:39080,psc2c256:39080" }
    {  "_id" : "rs8",  "host" : "rs8/psc1c257:39080,psc2c257:39080" }
    {  "_id" : "rs9",  "host" : "rs9/psc1c258:39080,psc2c258:39080" }
  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "prodLN",  "partitioned" : true,  "primary" : "rs1" }

rs1:PRIMARY> db.stats(1024*1024*1024)
{
    "db" : "prodLN",
    "collections" : 319,
    "objects" : 301960471,
    "avgObjSize" : 3532.3332748146363,
    "dataSize" : 993,
    "storageSize" : 1626,
    "numExtents" : 4436,
    "indexes" : 634,
    "indexSize" : 25,
    "fileSize" : 1765,
    "nsSizeMB" : 16,
    "dataFileVersion" : {
            "major" : 4,
            "minor" : 5
    },
    "extentFreeList" : {
            "num" : 1820,
            "totalSize" : 81
    },
    "ok" : 1
}


texts: SQL Server Agent log is showing error [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (LogToTableWrite)

texts:
While checking SQL Server Agent Log i found lot of error like follows

[298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (LogToTableWrite)
[298] SQLServer Error: 10004, Communication link failure [SQLSTATE 08S01] (LogToTableWrite)

Anyone having idea about this error ? Is there any impact ? How we can resolve the same


texts:
Suppose the following table t1:

=================
|  tag  |  val  |       --+ for the sake of simplicity, val is non NULL
=================
|   a1  |  v1   |
|   a1  |  v2   |
|   a1  |  v3   |
|   a1  |  v4   |
|   a1  |  v5   |
|   a2  |  v6   |
|   a2  |  v7   |
|   a2  |  v8   |
|   a2  |  v9   |
|   ... | ...   |
=================

If you execute the script below in MySQL:
SELECT `tag`, AVG(`val`) FROM `t1` GROUP BY `tag`

You would get the average values grouped by the column tag:

=================
|  tag  | AVG() |
=================
|   a1  | avg1  |
|   a2  | avg2  |
|   a3  | avg3  |
|   a4  | avg4  |
|   ... |  ...  |
=================

Besides AVG(), MySQL has several other built in functions to calculate aggregate values (e.g. SUM(), MAX(), COUNT(), and STD()) that could be used in the same way as in the script aforementioned. However, there is no built in function for median.
This issue has already come up several other times at SE; however, most of them related to tables without GROUP BY. The only one with GROUP BY seems to be MySql: Count median grouped by day; however, the script seems to be overcomplicated.
Question
What would be an easy and simple way (if possible) to calculate this median?
Follow-up
Excellent article that complement the accepted answer:
http://danielsetzermann.com/howto/how-to-calculate-the-median-per-group-with-mysql/


texts: Does index rebuild time depend on the fragmentation level?

texts:
Is the required time for index rebuild dependent on the level of fragmentation?

Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented 40% takes 1 minute?

I am asking for the RUNTIME (for example in seconds) that may be required to perform the required action, not about which action is required in what particular situation. I am aware of basic best practices when index reorg or rebuild / statistic updates should be done.

This question does NOT ask about REORG and the difference between REORG and REBUILD.

The background: Due to setup of different index maintenance jobs (each night, heavier job at the weekends...) I wondered if a daily "light intense" OFFLINE index maintenance job should be better performed on low-middle fragmented indexes to keep the off-times small - or does it not even matter and the rebuild on a 80% fragmented index might take the same off-time as the same operation on the same index 40% fragmented.

I followed the suggestions and tried to find out myself what is going on. My experimental setup: On a test server doing NOTHING else and not being used by anyone or anything else I created a table with a Clustered Index on a uniqueidentifier primary key column with some additional columns and different data types [2 numerics, 9 datetime, and 2 varchar(1000)] and simply added rows. For the test presented I added about 305,000 rows.

Then I used an update command and randomly updated a range of rows filtering on an integer value and changed one of the VarChar Columns with a changing string value to create fragmentation. After that I checked the current avg_fragmentation_in_percent level in sys.dm_db_index_physical_stats. Whenever I created a "new" fragmentation for my benchmark, I added this value including the physical_page_count value to my recordings the following diagram is made of.

Then I Ran: Alter index ... Rebuild with (online=on);
and grabbed the CPU time by using STATISTICS TIME ON into my recordings.

My expectations: I expected to see at least the indication of a kind of linear curve that show a dependency between fragmentation level and cpu time.

This is not the case.
I am not sure if this procedure is really appropriate for a good result. Maybe the number of rows / pages are too low?

However the results indicate that the answer to my original question definitely would be NO.
It looks like the required cpu time SQL Server needs to rebuild the index is neither depending on the fragmentation level nor depending on the Page Count of the underlying index.

The first chart shows the cpu time required to REBUILD the index in comparison to the previous fragmentation level. As you can see the average line is relative constant and there is not at all a relation between fragmentation and required cpu time observable.

To respect the possible influence of the changing number of pages in the index after my updates that could require more or less time to rebuild, I calculated FRAGMENTATION LEVEL * PAGES COUNT and used this value in the second chart that shows the relation of required cpu time vs. fragmentation and page count.

As you can see, this also does not indicate that the required time to rebuild is influenced by the fragmentation even if the number of pages varies.

After making those statements I guess my procedure must be wrong because the cpu time required to rebuild a huge and highly fragmented index then might only be influenced by the number of rows - and I do not really believe in this theory.

So, because I really and definitely want to find this out now, any further comments and recommendations are very welcome.


texts: can I create an SSRS group without adding the group to the box itself and without using Active Directory?

texts:
I want to know if it is possible to create a group "internal" to SSRS without having to add the group to the ReportServer box itself.

I do not have permissions to the box to create groups and/or add users to existing groups.  I know that this is a way (the only way?) to have groups that can be assigned permissions in the SSRS interface.

I do have all the permissions I need within the SSRS interface.  I have the System Administrator System Role on the ReportServer instance, as well as full sysadmin rights to the ReportServer database.

I am trying to simplify our security structure and it would be peachy if I could create a group, assign users to that group, and then permissions to the group.  But all I seem to be able to do is assign permissions to the groups set up on the box.

Any ideas?

EDIT: Active Directory groups are not an option for me.  :(


texts: How to find what SQL Jobs are using a specific account as Proxy?

texts:
We have identified a few servers that are using Proxy accounts inappropriately. Some of these servers have multiple Credentials and Lots of Jobs.  Manually checking the Job Property GUI for "Run As" on each step is not desired.

How can I quickly identify which if any, jobs have steps that are using Proxy Credentials we have identified as inappropriate?

I want to see, the account related to the Proxy. As well as the Job Name and Step the Proxy is used on.

SQL 2008+


texts: Enabling Kerberos for Microsoft SQL Server Reporting Services

texts:
I am trying to enable Kerberos for MS SQL Server Reporting Services. I am fairly familiar with Windows security and how it works. However, I am new to Scale Out Deployment and Clustering of Windows Servers. I know I need to add SPNs that direct towards both SSRS web service (using a domain account) and to the database engine. I am a little confused on exactly how the SPNs should be structured. I have looked in Stack Overflow and other resources and most reference a standard deployment without scale-out/clustering.

My questions are:

Do I structure the SPNs to point to the node, the cluster, both?
How do I structure the SPNs for each?
Do I need to include ports?

I ran the Microsoft Kerberos Configuration Manager and the Reporting Services SPN shows "Unauthorized". I don't know what this means and I can't find documentation on the tool.

Here are the details of my setup:

Domain: clearwd (not my actual domain)

Server OS: Windows Server 2016

Cluster: DEV-CLUSTER

Node 1: DEV-SQL1

Node 2: DEV-SQL2

Role: DEV-CLUSTER1 (SQL Server / MSSQLSERVER)

SQL Server Version: 2016 Enterprise

SQL Server Name: DEV-CLUSTER1

SQL Server Port: 1433

SSRS Service Account: ssrsuser.clearwd.com OR clearwd\ssrsuser

SSRS Mode: Native

SSRS Report Server Web Service URL: http://DEV-SQL01:80/ReportServer

SSRS Web Portal URL: http://DEV-SQL01:80/Reports

Let me know if any other information is needed.

References:

https://www.itprotoday.com/sql-server/implement-kerberos-delegation-ssrs-0

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/register-a-service-principal-name-spn-for-a-report-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/configure-report-server-urls-ssrs-configuration-manager?view=sql-server-2017


texts:
I use a UNION ALL query and it outputs 2 rows for the same date. I want to output only 1 row like the second picture.

Do I have to use IIF, CASE or else command?

The date, time_in, time_out fields are output from other table (main_table) and then I use a left join with the following UNION ALL query.

   (select date, time_in, time_out,leave_start,leave_end,leave_type_id, leave_type_descr
   from   table1

   union all

   select date, time_in, time_out,null,null,leave_type_id, leave_type_descr
   from  table2)


texts: Ports necessary for Oracle 11g Replication across a firewall?

texts:
We have two Oracle 11g Enterprise Edition servers that need to replicate data via Oracle streams.  However, the two servers are on untrusted domains and connect via a VPN.  On end of the connection is a Juniper firewall, with the other side a TMG firewall.

I know the listener runs on port 1521, but what other ports or port ranges should be opened for Oracle replication to work correctly?


texts: Why is the built-in distance function so much slower than my custom one?

texts:
I'd like to find the distance between 2 sets of coordinates.  First I use the built in function:
declare @lat1 real = 33.1, @lon1 real =-117.1, @lat1 real = 39.6, @lon1 real =-98.7
declare @source geography = geography::Point(@lat1, @long1, 4326);
declare @target geography = geography::Point(@lat2, @long2, 4326);

select  @source.STDistance(@target)

Now I use manual calculation to do the same thing:
 -- Kilometers to Miles: 0.621371, Earth Radius: 6378.137
 select 0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))

In my testing on SQL Server 2014, the manual function is around 4x faster than the built-in one.  Is this normal or am I missing something fundamental?


texts:
I have two SQL Server 2008 R2 systems that, due to network restrictions, cannot use SQL Server replication. However, data needs to be shared between these systems. It may be possible to send a file from one system to another, however.

What I am thinking is possible setting up Log Shipping. I know that to use Log Shipping,the secondary database must be in a NORECOVERY state, so it cannot be used for live OLTP.

What I am thinking is maybe on the secondary server, I could have the secondary database, then have that database replicate to a database on the same local server, and that database would then be the live OLTP database.

I assume replication can't happen while in NORECOVERY mode. Would it be possible to run Log Shipping restores say, every 5 minutes, first changing to NORECOVERY, then doing the restore, then going back to recovery, allowing replication to continue? Is there a TSQL command to change the status to NORECOVERY, and then back?

I'm no SSIS guru by any means, but maybe that's a better way to go (flat file)? Would that be possible to set up in a database with 150+ tables, complete with FK dependencies, where all changes need to be reflected on the target database on a pretty frequent basis


texts: I have a CSV file and I want to convert it into a SQL INSERT statements.But I could only find online converters but I would like to know can I create it manually ?


texts: Weird error when bringing up docker image for postgres

texts:
I have a docker stack using multiple images. One of them being docker image of postgres. Now, I have been working on this for couple of months but since yesterday my postgres image just started failing and when I debugged the service, I found following errors in the log:

LOG:  invalid primary checkpoint record
iaap_manager-db.1.5ajswuqhk7se@linuxkit-025000000001    | 2018-06-12 18:16:12.513 UTC [18] LOG:  invalid primary checkpoint record
iaap_manager-db.1.6g83gkc55on3@linuxkit-025000000001    | 2018-06-12 18:16:21.877 UTC [19] LOG:  invalid record length at 0/1670AE0: wanted 24, got 0
iaap_manager-db.1.94tmkm7nnaxr@linuxkit-025000000001    | 2018-06-12 18:16:30.009 UTC [19] LOG:  invalid record length at 0/1670AE0: wanted 24, got 0
iaap_manager-db.1.wwpvypju8wdr@linuxkit-025000000001    | 2018-06-12 18:16:04.113 UTC [19] LOG:  invalid resource manager ID in secondary checkpoint record
iaap_manager-db.1.6g83gkc55on3@linuxkit-025000000001    | 2018-06-12 18:16:21.877 UTC [19] LOG:  invalid primary checkpoint record
iaap_manager-db.1.5ajswuqhk7se@linuxkit-025000000001    | 2018-06-12 18:16:12.513 UTC [18] LOG:  invalid resource manager ID in secondary checkpoint record
iaap_manager-db.1.5ajswuqhk7se@linuxkit-025000000001    | 2018-06-12 18:16:12.513 UTC [18] PANIC:  could not locate a valid checkpoint record
iaap_manager-db.1.5ajswuqhk7se@linuxkit-025000000001    | 2018-06-12 18:16:12.514 UTC [1] LOG:  startup process (PID 18) was terminated by signal 6

I have no idea what does it even mean. Any ideas?


texts: Is there any way to graceful restart mysqld?

texts:
I think my question is impossible but I sure I do not know everything.

In my opinion, when mysqld restart, some transaction is not still completed will be rollback on startup. Mysqld use undo log to do it. But is there any way to keep long transactions on restarting mysqld ?

I am using MariaDB Galera with three nodes.


texts:
CREATE TABLE plan
  (
     id                VARCHAR(30) NOT NULL,
     name              VARCHAR(20),
     amount            INT,
     interval          INT,
     currency          CHAR(3),
     object            VARCHAR(20),
     livemode          BOOLEAN,
     interval_count    INT,
     trial_period_days INT,
     created           TIMESTAMP,
     updated           TIMESTAMP,
     deleted           TIMESTAMP,
     PRIMARY KEY (id)
  );

Produces

'ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval          INT,
     currency          CHAR(3),
     object            ' at line 6'

Doesn't work. However if the 'l' at the end of 'interval' is removed. It works.

CREATE TABLE plan
  (
     id                VARCHAR(30) NOT NULL,
     name              VARCHAR(20),
     amount            INT,
     interva           INT,
     currency          CHAR(3),
     object            VARCHAR(20),
     livemode          BOOLEAN,
     interval_count    INT,
     trial_period_days INT,
     created           TIMESTAMP,
     updated           TIMESTAMP,
     deleted           TIMESTAMP,
     PRIMARY KEY (id)
  );

I don't understand if this is a problem with attempting to use 'interval' and 'interval_count'. What's going on here.


texts: Why does COALESCE function not work for this query?

texts:
I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can do this by using the COALESCE function, but for example this query still gives me an empty cell:

CREATE VIEW third_table AS
SELECT *
FROM  (second_table
NATURAL FULL JOIN (
   SELECT assignment_id, COALESCE( count(*), 0 ) AS num_60_79
   FROM (
      SELECT assignment_id, mark_as_percent
      FROM avg_required_table
      WHERE mark_as_percent >= 60
      AND mark_as_percent < 80
      ) a
   GROUP BY assignment_id
   ) b);

Is this how COALESCE is supposed to be used?


texts: Repair database on Replica in sharded set up

texts:
I am using mongo sharding in my DB. The scenario I am facing is that I have deleted some of my collections in my DB. But as to reclaim the space I need to run repair option which i can not run using Mongos in production.
So my plan is to run Repair on each replica server one by one by switching primary to replica and vice versa so that production systems is working.
Here I have two confusions:

Is it a right approach to repair DB
I have a big Data base like 1 TB of Data so repair option might take a day or two in this case my issue is every daya my application creates sharded DB bucket for that day so while running repair on one replica server metadata in config will get updated will it be a error in this case.
While deleting some old databases from my cluster I stopped the balancer and restart it after delete but still DB is not completely deleted it is showing me 2 or 3 GB memory space used. I check the remaining DB and it shows no collection in it.


texts: Is there a way to give GRANT OPTION recursively for subsets of permissions?

texts:
In SQL Server, I would like to create a role that has the ability to manipulate database objects as well as create other roles and grant those roles subsets of its permissions.

CREATE ROLE deploymentRole;
CREATE ROLE subRole;
GRANT SELECT TO deploymentRole WITH GRANT OPTION;

CREATE TABLE dbo.testTable (id INT NULL);
CREATE USER deployUser WITHOUT LOGIN;
ALTER ROLE deploymentRole ADD MEMBER deployUser;

EXECUTE AS USER = 'deployUser';

--This works
SELECT * FROM dbo.testTable

--This also works
GRANT SELECT TO subRole AS deploymentRole;

--This does not work
GRANT SELECT ON dbo.testTable TO subRole AS deploymentRole;
-- Cannot find the object 'testTable', because it does not exist
-- or you do not have permission.

While having SELECT (or another permission) on the entire database implies having SELECT on an object within the database, having the GRANT OPTION for SELECT on the entire database does not imply you have the GRANT OPTION for SELECT on an object in the database. Said another way, the GRANT OPTION does not seem to be recursive down the hierarchy.

In this case, I would like members of the deploymentRole to be able to grant subsets of their permissions to other principals without having to give explicit GRANT OPTIONs on all permissions for all objects now and in the future. Is there a way to apply the GRANT OPTION recursively or another paradigm I should be considering?

I do not want to grant permissions on individual objects as our schema is constantly changing. I want my role to be able to have database level permissions and also grant smaller subsets of its permissions without having to enumerate objects.


texts: How to get exact year difference between two dates in SQL Server?

texts:
I have a date of birth column to which I need to compare the current date and return the difference as age. Currently I am using the following query :

DECLARE @DateOfBirth date = '2000-07-27'
DECLARE @CurrentDate date = '2015-07-25'

SELECT
    @Age = DATEDIFF(YEAR, @DateOfBirth, @CurrentDate)

I am getting a difference as 15, but the desired result should be 14. This is because it compares the year and gets the difference between 2015 and 2000. So I used the following query :

select
    @Age = CAST(datediff(day, @DOB, @CurrentDate) / 365.2425 AS INT)
RESULT : 14

But when I change the Date Of Birth value to '2000-07-25', I still get the value of 14, but it should be 15 since the date difference is exactly 15 years.

How can I get the exact year difference from this?


texts: How does R-Tree outperform B-Tree for simple check if a point is within a rectangle

texts:
For things like find nearest neighbors, I can undertand that R=Tree can outperform B-Tree. R-Tree can kick out more obviously false points.

However, for simple check of a point in a rectangle, it's effectively

min

If x and y is indexed by b tree, they too can kick out a lot of false points.

So I did an experiment with spatial index

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
   GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672  106.81978308133)'),
   TA.Latlong
   )
   AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

This is pretty fast. .24 seconds

And then I do

SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
   join tableauxiliary as TA
WHERE
  -6.2317830813328 < TB.Latitude
AND
  TB.Latitude < -6.1382169186672
AND
  106.72621691867 < TB.Longitude
AND
  TB.Longitude <106.81978308133
AND
   MATCH (TA.FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
  Distance
LIMIT
  0, 20

This is very slow.

I wonder why. It doesn't make sense.


texts: Does Postgresql apply the same type of locks on UPDATE with range condition and on UPDATE with in-set condition?

texts:
Does Postgresql apply the same type of locks on UPDATE with a range condition and on UPDATE with in-set condition?

E.g. will the queries:

UPDATE table WHERE id IN (1,2,3,4,5,6,7,8,9,10);
UPDATE table WHERE id >= 1 AND id < 11;

Cause the same type of locks on table?