texts: Postgres or MapReduce to Match Reciprocal Needle Pairs from a Stack of Needles?

texts:
I have a Postgres (latest version) table with about a million lines (a_lat, a_lon, b_lat, b_lon) that each have about a dozen criteria.

These lines go together in reciprocal pairs (A - B, B - A), which I need to determine. There may be multiple lines with similar A and B lat/lon, with similar but different criteria.

The criteria matching isn't exact, so many passes are done with less and less strict criteria (exact match on early rounds, +/- 1 one on later rounds, then +/- 1.5, etc). Matches in each pass are removed from subsequent passes.

I'm currently doing the matching in Postgres by looping over each row in the table and doing a query to find matches:

SELECT
    id
FROM
    locations
WHERE
    a_lat BETWEEN SYMMETRIC (38.5565 - 0.00055533333334) AND (38.5565 + 0.00055533333334) AND
    a_lon BETWEEN SYMMETRIC (-77.2797222222222 - 0.00055533333334) AND (-77.2797222222222 + 0.00055533333334) AND
    criteria_1 BETWEEN SYMMETRIC (42.7 - 1.5) AND (42.7 + 1.5) AND
    criteria_2 = 23 AND
    criteria_3 = 'ABCD' AND
    (criteria_4 = '0014980726' OR criteria_4 IS NULL) AND
    criteria_5 = 'A' AND
    criteria_6 = 1

If there is exactly one match then it's recorded and that row removed from future passes.

If you do the math its a ridiculous amount of queries (billions) and takes days to run. All fields have been optimized (floats and integers where possible) and indexed.

Is this type of problem suitable for a MapReduce type solution?

Can MapReduce handle multiple criteria, and criteria ranges (+/- 1)?  Can a performance gain be expected on the same hardware (384 GB RAM, 64 core Xeon), or is the performance increase only due to the ability of MapReduce solutions to scale to multiple machines?

Thanks!


texts: PostgreSQL ANALYZE execution time over 24h (still running)

texts:
I upgraded a Postgres DB 9.3.2-->10.5 using pg_upgrade (in place). I did everything according to the documentation and the instructions given by pg_upgrade. Everything went fine but then I realized that the indexes were not being used in one of the tables (maybe others are affected too).

So I started an ANALYZE on that table yesterday which is still running (for over 22h)...!

The question: Is it normal for ANALYZE to have such a long execution time?

The table contains about 30M records. The structure is:

CREATE TABLE public.chs_contact_history_events (
    event_id bigint NOT NULL
           DEFAULT nextval('chs_contact_history_events_event_id_seq'::regclass),
    chs_id integer NOT NULL,
    event_timestamp bigint NOT NULL,
    party_id integer NOT NULL,
    event integer NOT NULL,
    cause integer NOT NULL,
    text text COLLATE pg_catalog."default",
    timestamp_offset integer,
    CONSTRAINT pk_contact_history_events PRIMARY KEY (event_id)
);

ALTER TABLE public.chs_contact_history_events OWNER to c_chs;

CREATE INDEX ix_chs_contact_history_events_chsid
    ON public.chs_contact_history_events USING hash (chs_id)
    TABLESPACE pg_default;

CREATE INDEX ix_chs_contact_history_events_id
    ON public.chs_contact_history_events USING btree (event_id)
    TABLESPACE pg_default;

CREATE INDEX ix_history_events_partyid
    ON public.chs_contact_history_events USING hash (party_id)
    TABLESPACE pg_default;

UPDATE:

I ran the query below in order to get the currently running processes and got a more than interesting results:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state = 'active';

It seems that the maintenance tasks and the concurrent recreation of the index table are frozen!

So the next question: is it safe to cancel those processes? And what to do next? IMO stopping them all and restarting index creation will be necessary but I'm unsure.

ANNEX 1

Possibly related errors corrected in v9:

9.3.7 and 9.4.2
Fix possible failure during hash index bucket split, if other processes are modifying the index concurrently

9.3.18 and 9.4.13 and  9.5.8 and 9.6.4
Fix low-probability corruption of shared predicate-lock hash table in Windows builds

9.5.4
Fix building of large (bigger than shared_buffers) hash indexes
The code path used for large indexes contained a bug causing incorrect hash values to be inserted into the index, so that subsequent index searches always failed, except for tuples inserted into the index after the initial build.

Possibly related errors corrected in v10:

10.2
Fix failure to mark a hash index's metapage dirty after adding a new overflow page, potentially leading to index corruption

Prevent out-of-memory failures due to excessive growth of simple hash tables

And last but not least that makes me concerns (since an upgrade seems to be not realistic on the productive environment):

10.6
Avoid overrun of a hash index's metapage when BLCKSZ is smaller than default

Fix missed page checksum updates in hash indexes

ANNEX 2

Upgrade instruction in v10:

Hash indexes must be rebuilt after pg_upgrade-ing from any previous major PostgreSQL version

Major hash index improvements necessitated this requirement. pg_upgrade will create a script to assist with this.

Note that I ran that script of course at the time of upgrade.


texts:
This is my relational schema;

Book(title, shelfmark, ISBN, author, year)
Genre(name)
GenreBook(name, ISBN)
User(firstName, lastName, cardID)
UserBooks(cardID, ISBN, position)

Why do I need the position attribute in the last relation? It is a N-M relation but (ISBN, cardID) should be enough to identify a tuple. Maybe my user can have two books with the same ISBN?


texts: My problem is finding MySQLL database using .ibd files, the problem is that I do not have access to the .frm file and due to problems with the hard drive I had to change the operating system and restart the entire MySQL I installed, how can I recover these databeses ??? !!!


texts: How to stop the SQL Server Agent Job Scheduler with T-SQL?

texts:
I have a job that I manually start in coordinated time periods that does a bunch of heavy lifting (mostly restoring backups and configuring for a different server)

There are several jobs scheduled to run with increments of minutes to days.  I want to "turn off" the scheduler so nothing will run on schedule until my manual job is done.  I realize that missed jobs will not run again until their next scheduled time, after the scheduler is restarted.

First Step: stop scheduler
Several Heavy lifting steps: Do the hard work
Last Step: start scheduler

I found An Oracle command but I am 100% Microsoft SQL Server

I know that if I Turn off the SQL agent, jobs will not run but that is not what I am looking for.

The jobs that exist for today, are not expected to be the same jobs next time.  Disabling jobs with EXEC dbo.sp_update_job is not a viable solution.

Additional information: There may also be jobs in place that are intentionally disabled for some reason, that do want to be re-enabled. Stopping the scheduler seems like the best choice.


texts:
Does a hidden member count toward and operate as part of a write_concern majority in MongoDB 2.6?

It states here that for the w option, prior to Mongo 3.0,

  "majority" refers to the majority of the replica set’s members.

It seems then that if I have four Mongo 2.6 nodes and one is hidden, then "majority" calculates as w=3, since the hidden node is still a replica-set member. Meanwhile, it's unclear to me whether writing to the hidden member counts toward fulfilling the write concern -- after all, only three nodes are visible for reads.

The concern I have is that with the above example, if one non-hidden node goes down, it becomes impossible to fulfill a write with w="majority".


texts: error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

texts:
I'm trying to load mysqldump and I keep getting following error:

  ERROR 2013 (HY000) at line X: Lost connection to MySQL server during
  query

/etc/my.cnf:

[mysqld]
max_allowed_packet = 16M
net_read_timeout = 30
net_write_timeout = 60
...
[mysqldump]
max_allowed_packet = 16M

I tried to increase these values, but I keep getting that error no matter what( What else can I do to overcome this error?


texts:
I have a table with the following structure.

CREATE TABLE `score` (
    `sid` INT NULL,
    `name` INT NULL,
    `rollno` INT NULL,
    `examid` INT NULL,
    `phone` INT NULL,
    `dob` INT NULL,
    `district` INT NULL,
    `score` INT NULL,
    `active` INT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I need a query that will assign rank to a student based on their score and age. If two students have same mark, student with highest date of birth is given priority.

This is the sample data I have:

--------------------------------------------------------------------------------
sid | name | examid | phone | dob       | district | score | date       | active
--------------------------------------------------------------------------------
1     User1    1      12345  10/11/1983   209         10     10/11/2017     1

2     User2    1      34567  11/10/1983   209         10     10/11/2017     1

3     User3    1      34567  11/10/1985   209         20     10/11/2017     1
------------------------------------------------------------------------------

The result I am expecting is like this:

+----+------------------+----------------+
| id | name             |           rank |
+----+------------------+----------------+
|  2 |           User2  |              1 |
|  3 |           User3  |              2 |
|  2 |           User1  |              3 |

My question is similar to this question (Get the rank of a user in a score table)  but is there any other solution.

This is the current query I have which is working but how to display rank with the same query.

SELECT name, rollno, score
FROM score
WHERE examid = '1'
ORDER BY score DESC, dob ASC


texts: how to tell which statistics SQL server references in query

texts:
Is there a way to check in SQL Server 2016 which statistics the optimizer referenced when compiling a particular query?

My question is similar to Check which statistics were used in query optimization,
except that I am using SQL Server 2016, which doesn't appear to support trace flags 9204 and 9292 as listed in How to Find the Statistics Used to Compile an Execution Plan by Paul White. I tried them and nothing showed in my SSMS.

I googled to check why - and it sounded as if 2016 doesn't support them?


texts: How to implement composite partition (Range list / Hash range) partition in SQL server?

texts: Our production database table already have a 18 partitions based on date range for a single state wise, records of around 200 million rows. Now we are planning to include multiple states with state ID and the record count will hit around 3 billions. Is it possible to implement List partition based on state id and create a sub partition based on date range in SQL server similar to Oracle. I am aware that SQL server will support only Range partition, is there any work around to implement like that? Do not want to create any tables based on state wise. ours is a single staging table and multiple objects depends on the table so any additional objects will be strictly avoided because it requires lot of dependency objects code change. Kindly help me to create composite partition similar to Oracle in SQL server 2012 enterprise edition.


texts: Start job from another SQL server instance

texts:
I have a job on an SQL 2008 server (Server A) (I know this is not ideal... That issue is being addressed). The agent runs under NT AUTHORITY\NETWORK SERVICE.

I want to add a step that runs a job that sits on another SQL 2014 server (Server B).

I would use:

EXEC [Server B].msdb..sp_start_job N'JobName'

Ran from Server A manually it executes the job on Server B fine as expected.

If I add the task to a job on Server A it fails with message:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. The EXECUTE permission
  was denied on the object 'sp_start_job', database 'msdb', schema
  'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.

I have assigned NT AUTHORITY\NETWORK SERVICE on Server B to the 'TargetServerRole' in msdb and then granted Execute permission to concern user to SP_Start_Job and SP_Stop_Job.

Server A has Server B as a linked server and I tried setting "Local server login to remote server login mappings" Local login NT AUTHORITY\NETWORK SERVICE to impersonate.

The job still fails with the same error.

What do I need to do?

Thanks


texts: For each record, count records dated earlier in PostgreSQL

texts:
I have a table that contains a column year. I need, for each row, calculate the number of entries where the year is less than that in the row.

So that the output would look like:

  col1 | col2 | ... | year | number of entries prior to this year

I'm using PostgreSQL.


texts: PgPool is pushing Postgres to 100% cpu usage

texts:
I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, and two postgres machine, one being a master and the other a slave, both reaching 100% cpu usage on a pgpool balancing, but more than 70% of the pgpool connections are just idle. Both postgres machines oscillates on the cpu usage but constantly reaches 100%, the cpu usage drops dramatically when pgpool close some connections in his own maintenance configuration, but once he reopens then, we reach a high cpu overload.

Both, master and slave, postgres machines have the follow configuration:

max_connections = 300
superuser_reserved_connections = 3
shared_buffers = 480MB
work_mem = 6656kB
maintenance_work_mem = 120MB
effective_cache_size = 1408MB

I've get the above configuration using pgtune:

$ pgtune -i /etc/postgresql/9.2/main/postgresql.conf \
-o postgresql.conf -T Web -c 300

Master CPU hardware and usage

Intel(R) Xeon(R) CPU X5680  @ 3.33GHz
Tasks: 214 total,   9 running, 205 sleeping,   0 stopped,   0 zombie
Cpu(s): 99.3%us,  0.3%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   2055808k total,  2038980k used,    16828k free,     3700k buffers
Swap:  1340408k total,   144824k used,  1195584k free,   508272k cached

Slave CPU hardware and usage

Intel(R) Xeon(R) CPU X5680  @ 3.33GHz
Tasks: 216 total,   4 running, 212 sleeping,   0 stopped,   0 zombie
Cpu(s): 94.7%us,  3.6%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  1.7%si,  0.0%st
Mem:   1537664k total,  1525140k used,    12524k free,      996k buffers
Swap:  1340408k total,   516120k used,   824288k free,   381964k cached

My pgpool balance two postgres machines thorough a master/slave streaming replication, my pgpool pertinent configuration was setup as follow:

num_init_children = 128
max_pool = 2
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
connection_cache = on

I also have tried these other variations of 256 connections:

1)

num_init_children = 64
max_pool = 4

2)

num_init_children = 32
max_pool = 8

But nothing seems to calm down the overload.


texts: Dispaly Results from left join takes lot of time - MSSQL

texts:
I have the following query (just for example):

SELECT
     a.id
    , a.name
FROM db.schema.tablea a
    LEFT JOIN db.schema.tableb b
        ON a.id = b.id    -- number
        AND a.name = b.name    --string

This query takes usually 1 second to run.

But, the following query takes 20 seconds to run:

SELECT
    a.id
    , a.name
    , b.value
FROM db.schema.tablea a
    LEFT JOIN db.schema.tableb b
        ON a.id = b.id    -- number
        AND a.name = b.name    --string

In other words, display the results (b.value) makes the query 20 times slower.
In my specific case, I make a left join on two columns: string (lenght max 10) and number. The result (b.value in my example) is a float.

The joined table has 3 columns and ca 2000 rows.


texts: What is the best index implementation for a really large databse

texts:
I have a really large SQL SERVER table with only non clustered indices for several columns , while querying the table the execution plan shows RID Lookups , i expected to see this kind of lookup because the columns in the select list are not part of a covering index or included in another index .

The non-clustered indices were created on several columns because each one of these columns is a part of a different query with different select lists and search conditions.

Due to size and storage issues creating a covering index or a new index with included columns while keeping the current non-clustered indices is not feasible.

Now the question is :

Can i replace the non-clustered indices with one covering index ?
If not, is there any way to get rid of RID Lookup rather than a covering index ?
Does each query require a different covering index depending on the columns in the select list and the search conditions ?
I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?
Any notes or comments to solve such problems are welcome ..


texts: Is there a way to create temporary tables using CTE's and WITH/VALUES in Vertica?

texts:
I'm trying to create a temporary table using CTE's/With.

Is there a Vertica equivalent for this approach?

WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100))
SELECT * FROM vals;

I want to be able to use the data without creating a temp table

How to create a temporary table using VALUES in PostgreSQL


texts: Create deadlock scenario without foreign key

texts:
I need to test my C# code in a SQL Server deadlock scenario.  Is there a way to create a deadlock "on demand"?

I have tried this:

Begin Transaction
Select * from TABLEA with (tablockx, holdlock)
Where 0 = 1
WaitFor Delay '00:02:00'
--Rollback Transaction

But the second connection just waits and until after the Delay, and then does its job.

In the second session I do

Update TABLEA
Set LastUpdatedBy = 'IT_TESTING'
Where OrderRefProviderId = 397685
WaitFor Delay '00:00:30'


texts:
I am having an issue with Editioning in an Oracle 19c database. I have never heard of this feature before today, but I have found that somehow a number of objects in the database have had their Editionable property set to 'Y'. We have never made use of this feature before, non of our users have Editions enabled, and the only edition in the database is the default 'ora$base'. Even still, when I query all_objects and look at the Edition_Name field, I see that even those objects which have Editionable set to 'Y' have an Edition_Name of (null)
Is there a way to modify these objects to reset their Editionable value to 'N'?
This is preventing me from running some migration scripts as since the user does not have Editions enabled the CREATE OR REPLACE statements are trying to change the Editionable property of the objects back to 'N' and thus we encounter the following error:
ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object

So my question is, what command may change the EDITIONABLE property of an existing object?


texts: Best way to put commas into large numbers

texts:
I've started a new job and it involves looking at a bunch of big numbers. Is there an easy way to add commas to an int or decimal field to make it readable?

For example, SQL Server outputs the column on the left, but for my own sanity, I need it to look like the one on the right:

2036150 -> 2,036,150

...or would I have to write some heinous

left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3)

function?

The perfect thing would be commas in the display grid, then plain integers in the output.


texts:
I'm using DB2 9.7.0.3 for AIX.

I want to select data from a remote database, also running  DB2 9.7.0.3 for AIX, and insert it into the local database I'm connected to.

I believe the query for this is

INSERT INTO SCHEMA.TABLE
    SELECT * FROM REMOTEDB.SCHEMA.TABLE

How do I define REMOTEDB as an alias to a remote database connection?


texts:
If you want to upgrade or install a patch on two separate instances that house both the principal and mirrored database, how can you go about that?  If the database that is being mirrored needs to be available 24/7 and you don't have a window to go offline, what is the best means to do this?

EDIT: this is with SQL Server.


texts: How to distinguish free-standing composite types from table composite types in pg_type?

texts: When querying pg_type, I'm interested in identifying free-standing composite types (user-defined) and not those which are created along with tables. Perhaps there is a way to lookup pg_class (with typrelid) which might help but it's not apparent how I might do that.


texts: How can the System databases in SQL Server 2016 be moved using Powershell?

texts:
I'm trying to move the system databases using Powershell ie. without using any T-SQL.

Using SMO :
Install-Module SQL-SMO
$smo = New-SMO -ServerName localhost
$smo.databases["TempDB"].PrimaryFilePath= "F:\Tempdb\"
$smo.databases["TempDB"].Alter()    gives an error:

  'PrimaryFilePath' is a ReadOnly property.

The logfile moves fine though.
$smo.databases["TempDB"].LogFiles[0].Filename = "F:\Tempdb\tempdb.ldf"

Couldn't locate any specific cmdlets in the SQLServer or DBATools modules either. Copy-DbaDatabase comes closest; but not exactly.


texts: Protocol for Converting External Unnormalized Data to Normalized Tables

texts:
I'm working through the fundamentals of bringing data from Excel (or any single unnormalized form table model) to SQL (MySQL or SQLite).

These are the steps I've extrapolated from reading:

Import data to a single table
Assign Headers
Match Columns Into Tables
Duplicate Table-Seperated Columns to New Table
Remove Duplicate Rows from New Tables
Reset Auto-Increment
Attach Foreign Keys to Original Table by matching column data on New Tables
Remove Columns on Old Table represented by Foreign Keys/New Table
Repeat 4-8 for each new table until DB is normalized

Is this a rational flow?


texts: Database file ..._log has a max file size set to x MB. If it runs out of space, the database will stop working

texts:
I am using BrentOzar's SQL Server First Responder Kit since I fall under the category "developer who manages Microsoft SQL Servers. It's your fault if they're down or slow."

One of the warning of running sp_blitz says the following:

  The [DB] database file DB_log has a
  max file size set to 10240MB. If it runs out of space, the database
  will stop working even though there may be drive space available.

Log files are on a separate drive from OS and I indeed limited their growth. All databases have simple recovery model (most of the data is automatically obtained through replication and ETL and recovering from last backup is enough for me).

The database has about 20GB of data.

Question: how can the database stop working if the recovery model does not force me to backup the log?


texts:
In all the following scenarios on my laptop, all the tables in the SSMS Tables folder will be deleted from tempdb:

Instance Restart;
Stop & Start SQL Server Service;
End Task SQL Server process in task manager;
OS (WINDOWS) Restart.

I have no problems with the above.

But …

The following scenario is very interesting:

First, I create a table in tempdb:

USE tempdb;
GO
CREATE TABLE Test_Table
(ID INT,
Title VARCHAR(100));
GO
INSERT INTO Test_Table
VALUES
(1,'Test01'), (2,'Test02'), (3,'Test03');
GO

Then I exit SSMS and shut down Windows.

A few days later …

Turn on my laptop.

When I get into SSMS, I'm surprised to see that there is a Test_Table with its records in the tempdb.

Why? I did not create such a table in the model database.

I thought so much about this.

Can the following explanations prove this hypothesis?

You should be aware that Windows shut down is much faster than restarting.

It is likely that in Windows restarting, the SQL Server service will also be restarted, and to end the service, it should release the resources it has, as well as empty the contents of tempdb.

But in the Windows shut down process, the Windows shut down service is likely to be responsible for releasing resources, and in fact kill all processes, and obviously there is no accident in the tables in the Tables folder of tempdb, Because the SQL Server service does not restart.

I tested this in versions SQL Server 2019, 2017 and 2008.

Do you think your guess is correct?

I'd be happy to express your opinion about this.


texts: Using postgres to select a filtered array

texts:
I'm looking to select data from a table that contains an ID, and an array of values. But in the query results, i want to filter the array and remove values that overlap with a white list.

For illustrative purposes, the following query shows how the data would be formatted with a whitelist of values in a single column, and then a table i want to select from... that contains a unique ID, and the array of keywords.

with whitelist as (
    select distinct unnest(array['a','b','c','f','h']) as keywords
), raw_with_keyword_array as (
    select * from  (
        values
            ('1234', array['a','c','e','h','q']),
            ('1235', array['a','d','e','f'])
    ) as t (id, keyword_array)
)
select...

The expected results i'm looking for is two rows from the raw_with_keyword_array table, but filtered arrays not including the values from the whitelist.

I figure i probably need to select the data from the main table, and then do some kind of recursive sub query to filter out the whitelisted values, and put the results back into a new array?

Any suggestions would be appreciated!

E.g.

1234    {e, q}

1235    {d, e}


texts: Changing compatibility level of database causes performance improvement of stored procedure

texts:
We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has a Stored Procedure that obtains 3 parameters UserID, StartDate and EndDate. This Stored Procedure runs very well but at the end of the month (when all the +40.000 clients uses it) starts to decrease its performance gradually.

The weird thing is that when we Changed the compatibility level to Sql Server 2008 It automatically "refreshed" and stared to work very fast, and then again gradually decreased its performance until we (again) took the compatibility lever back to 2005 and stared to work properly.

Why is it that changing the Compatibility Level the Main Stored Procedure improves a lot after decreasing its performance gradually?


texts:
I can't figure how can I update an element in a PostgreSQL 9.3 datatype.

My example:

CREATE TABLE "user"
(
  id uuid NOT NULL,
  password character varying(255),
  profiles json,
  gender integer NOT NULL DEFAULT 0,
  created timestamp with time zone,
  connected timestamp with time zone,
  modified timestamp with time zone,
  active integer NOT NULL DEFAULT 1,
  settings json,
  seo character varying(255) NOT NULL,
  CONSTRAINT id_1 PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE "user"
  OWNER TO postgres;

The json part in "profiles"

{
    "Facebook": {
        "identifier": "xxxxxxxxxxx",
        "profileURL": "none",
        "webSiteURL": "none",
        "photoURL": "none",
        "displayName": "test2 test2",
        "description": "none",
        "firstName": "test2",
        "lastName": "test2",
        "gender": 2,
        "language": "none",
        "age": "none",
        "birthDay": "none",
        "birthMonth": "none",
        "birthYear": "none",
        "email": "test@test.com",
        "emailVerified": "none",
        "Added": null,
        "phone": "none",
        "address": "none",
        "country": "none",
        "region": "none",
        "city": "none",
        "zip": "none"
    },
    "Google": {
        "identifier": "xxxxxxxxxxxxxxxxxxxxxx",
        "profileURL": "none",
        "webSiteURL": "none",
        "photoURL": "none",
        "displayName": "test2 test2",
        "description": "none",
        "firstName": "test2",
        "lastName": "test2",
        "gender": 2,
        "language": "none",
        "age": "none",
        "birthDay": "none",
        "birthMonth": "none",
        "birthYear": "none",
        "email": "test@test.com",
        "emailVerified": "none",
        "Added": null,
        "phone": "none",
        "address": "none",
        "country": "none",
        "region": "none",
        "city": "none",
        "zip": "none"
    }
}

I'm using x-edit for the frontend, and I was hoping that something like this would work, but it doesn't:

UPDATE public.user
SET "profiles"->'Facebook'->'social'->'facebook' = 'test' WHERE` id='id'

I can't seem to find any information on how to update a json datatype.


texts: Debit Credit Balance calculating from previous records with Pagination MYSQL

texts:
I have table with the following schema and demo data-
id  date        particulars          debit  credit
104 29-12-2020  Crediting            NULL   1000
105 29-12-2020  Purchased Item:A     2000   NULL
106 29-12-2020  Purchased Item:B     200    NULL
107 29-12-2020  Purchased Item:C     1000   NULL
108 29-12-2020  Credited             NULL   1200
109 29-12-2020  Credited             NULL   500
110 29-12-2020  Credited             NULL   2100
111 29-12-2020  Credited             NULL   1300
112 01-01-2021  Purchased Item:Q     200    NULL
113 02-01-2021  Purchased Item:A     2300   NULL

I want to calcualte running balance along with filters like from this date to this and pagination included.
So far by referring this Answer
I found the satisfied results, only the problem lies with WHERE clause.
Query i have used =
SET @variable = 0;
SELECT id, date, particulars, debit, credit, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance`
FROM billing_ledger  ORDER BY id asc

when i use this query im not getting expected balance as it should be
SET @variable = 0;
SELECT id, date, particulars, debit, credit, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance`
FROM billing_ledger where date >= '2021-01-01' ORDER BY id asc

i want this second query to also consider the above rows but to give me results for the following dates with where clause and also with LIMIT
Here is the fiddle: FiddleLink
In fiddle, we can see that the second query is not considering the rows above, and thus the balance is not correct as it is in the 1st query.


texts: check the progress of table upload-mysql

texts:
I have a dataset of around 200GB.

The sample is shown below:

Posted a new song: "Ensaio Don`t back anger &amp; imagine- Stone Rocks " http://t.co/i9TqakT38t #music  460523675293196289  1308622848  0   0   Sun Apr 27 20:59:34 +0000 2014  nc  f   nr
Posted a new song: "Led Zeppelin- Black Dog Feat. RockSauro" http://t.co/Scdo6ncIc1 #music  460454112694263809  13086228298 0   0   Sun Apr 27 16:23:09 +0000 2014  nc  f   nr

This data is in a text file. I have created a table and importing this data into mysql by using the command:

mysqlimport --local -u root -pxxx twitter_data '/home/abc/alltweet.txt'

How can I check how much data has been loaded in mysql table. Actually this command is running since 10 hours and count(*) is giving me zero count.


texts: SQL Server 2016 - Availability Groups - How To Copy Permissions To Secondary Node

texts:
SQL Server 2016
SSMS v18.4

I want to create the same logins and give the same privileges on both node.
In primary node I have no problem about this operation.
But when I create the same login on secondary node and want to user mapping, it gives below error:

Create failed for login 'loginname'.
Additional information:
An exception occured while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Failed to update database "dbname" because the database is read-only.
(Microsoft SQL Server, Error:3906)

But my database is not read-only.
I'm trying the give permission like this:
Node 2 (Secondary) -> Security -> Logins -> Right click on MyLogin-> Properties -> User Mapping -> Select database and choose dbo for default schema -> Select public and db_owner -> OK.

It gives this error all of the databases for secondary node.
How can I solve this problem without doing failover (failover can solve this because it does primary node to Node-2 but I don't want to failover)?
If it possible with using PowerShell, how can I do it on this operation on PowerShell for Node-2?
Thank you.
Best regards,


texts: PostgreSQL: Auto update updated_at with custom column

texts:
Before, I would join multiple tables into one and return result, but in many tables I had same column names. That's why I decided to to prefix column names with table name. But that broke the trigger I had to auto-update my updated_at column.

This is the function I had:

CREATE OR REPLACE FUNCTION update_updated_at()
  RETURNS TRIGGER AS $$
  BEGIN
      NEW.updated_at = now();
      RETURN NEW;
  END;
  $$ language 'plpgsql';

And I would add a trigger like:

CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE PROCEDURE update_updated_at();

But now my column is named users_updated_at and this doesn't work. My question is is there any way that I can pass the column name to the trigger and update the passed column, or is there is any other way that I am not aware of?


texts: macOS Catalina - postgreSQL - sysctl.conf still relevant?

texts:
I had the following settings in /etc/sysctl.conf before Catalina:

# PostgreSQL Config for the MAC
kern.sysv.shmmax=2147483648
kern.sysv.shmall=524288
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.maxprocperuid=1024
kern.maxproc=2048

The Catalina install moved this file to the /Users/Shared folder as /etc is now considered private to macOS.

Are these settings still relevant for Catalina, and if so, where do they need to go?

Thx.


texts: Access operator added by extension inside a schema

texts:
I am writing an application that uses postgres. Each microservice uses its own schema inside the database.

So connect to the database schema I use this jdbc string:

jdbc:postgresql://${db.host}/${db.name}?currentSchema=${schema}

This kind of works, so each sql statement can use the tables inside the schema without the danger of accidentally accessing the other services schema.

But in one schema I'd like to use the earthdistance extension. I only managed to install it without setting a schema and when I connect to the DB without the currentSchema parameter I can access the <@> operator to calculate the distance.
But with the parameter postgres complains that it's not able to find the operator.

Is there a way to access the operator that is in the public namespace, when I connected with the currentSchema parameter?

I'm kinda lost here :-(


texts: How to speed up query containing concat() in WHERE clause?

texts:
In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form:

SELECT table1.attrib1
  FROM table2, table1
 WHERE table2.status = 'Active'
   AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 )

The output of EXAMINE tells me that the index on table2.table1 is used, but the index on table1.attrib1 isn't used to execute the query. Instead all existing rows of table1 are accessed (full table scan). The expected result contains only a few rows.

It's a commercial software, so I can't change the query. But I could add indexes or change database parameters.

Question: What can I do to speed up this query?


texts: SQL Server Configuration Manager not display services

texts:
Config: Window Server 2008 R2 Enterprise SP1 / SQL Server 2008 R2 Enterprise (RTM)

I have searched through Google using many different words and phrases. I know this was a common issue to occur with SQL 2005 and most mention doing a command line to re-register the mof file and a few other things.

I'm getting the same issue similar to what everyone else receives: I can only see SSIS service within SSCM.

I have checked Wmimgmt.msc permissions and I am an in local Administrator group and that group has Remote Enable. Reference
I have enabled WMI trace log (feature in Server 2008 Event Viewer) and see the calls being made to the WMI provider, but no errors showing. Just information messages.
I have tried calling the services information through PowerShell WMI command (here) and it only returns SSIS service as well. No error is returned when I execute the $mc.Services | Select Name, DisplayName it just returns SSIS service info and that's it.
I've gone through the GP policies and don't see any settings that would restrict me having access to WMI providers.
I have permission to "..\Microsoft SQL Server\100\Shared\sqlmgmprovider.dll" file.

I'm officially stumped now. I also checked Microsoft Connect and did not see any thing for this issue. SP1 for SQL Server 2008 R2 has not been applied but I don't see anything in that documentation that would have any effect on this issue.


texts: Is there any difference between putting a column alias at the start or the end of the column definition?

texts:
I've always seen and written my column aliases as

SELECT 1 as ColumnName

but today came across a query that used

SELECT ColumnName = 1

Is there any difference in how these two queries get executed? Or is there a standard among DBAs about which one to use?

Personally I think the 2nd would be easier to read/maintain for longer column definitions (good example here from this article), however I've never seen the 2nd syntax used before today so am wondering if there is some reason I shouldn't be using it.


texts: Postgres knowing which where column returned positive result

texts:
Assuming we have a query like:

select * from table where a=1 or b=2 or c=3

and the where conditions met are a=1 and c=3

Would it be possible to know in the query (in the output) that a and c are the columns that matched the where clause?


texts: Why doesn't row data show up in SQL Developer?

texts:
I'm using Oracle database and I've created a test table "Table1" with a varchar column "column1". I ran the following loop to add some rows:

BEGIN
    FOR i in 1..10 LOOP
        INSERT INTO TABLE1 (COLUMN1)
        VALUES (i);
    END LOOP;
END;

If I run 'select count(*) from table1' I get '10' as my result. However, in SQL Developer, nothing shows up. I can add rows manually and commit them to the database(these appear in SQL Developer), but I can't seem to view the ones created in sqlplus command line. Why is that?


texts: SQL Server - Prevent Clustered Index Scan on a specific table

texts:
My database contains a specific table that is quite large (250+M rows, 100+GB data space).

Generating a clustered index scan on this table is always a bad idea.

This table has multiple indexes and we never run a query on this table without specifying predicates on indexed columns.

Since we updated the server, we have experienced random query plan degradations. Queries that would run in a few seconds randomly start to timeout after multiple dozens of minutes.

Here are the server changes:

The compatibility level was updated from 2008 to 2016, thus the cardinality estimator changed.
SQL Server edition was updated from Standard to Enterprise.
SQL Server version (2016) was not changed.
The storage (SSD) was not changed.

Of course, I can easily solve this issue for one specific query by adding hints, but I have too many queries in my applications to broadly apply this solution.

Is there a way to globally prevent SQL Server from generating Clustered Index Scan on this table?

I was not able to reproduce the same bad query plans from my previous production issues (the bad query plans happen randomly), but I was able to generate similar query plans using a new simple query. I uploaded plans for the simple query without hint and with hint.

OPTION (RECOMPILE) could solve the issue. But it does not ensure that the new query plan will be better or that SQL Server will not switch to another query plan in a few days. I am not looking for a way to improve one particular query or to improve one particular query plan. I want to globally prevent Clustered Index Scan on my table.

I have many queries that query many different columns and I cannot include all columns in all indexes.

One global option that would probably solve my issues is to use the legacy cardinality estimator, but I hope to find something better.


texts: View permissions on specific Oracle object

texts:
I want to be able to see what permissions have been granted on a specific object in my Oracle geodatabase. What would my syntax look like in SQL Plus?

Thank you.


texts:
I have this password hash function that hashes a password to sha256. I've already got it running in pgadmin3 in a local database. I've done it by importing the extension, a feature that has been added a few versions ago.

To my schoolwork I need to run the trigger in a phppgadmin server but when I execute

CREATE EXTENSION pgcrypto;

I get

ERROR:  could not open extension control file "/usr/share/postgresql/9.1/extension/pgcrypto.control": No such file or directory

How can I resolve this without having to change my hash to a weaker one (md5 or sha1)?


texts:
I have a two node RAC (Oracle 12c) on linux. I have 5 SCAN listeners configured.

Currently, if I do a srvctl config scan I notice that out of the 5 scan listeners, one scan is running in one node and the remaining 4 in the another node.

Is it necessary for me to relocate one or two listeners running on node 2 to node 1?

Will my connections still be load balanced even if I have no scan listeners running in node 1 and all the 5 are running in node 2? If yes, how?

When does one actually need to relocate the scan to another node?


texts: How to search for compound words, and get the word parts returned

texts:
I need to be able to search for compound words like "menneskerettighedsforkæmperens" (the human rights activist's) and find the words "menneske" (human), "rettighed" (right) and "forkæmper" (activist).

I have all the words (and hundreds of thousands of other words) listed in MySQL, all in separate rows with additional information about each word (like hyphenation, which is what I need the DB to return), but I need to search a lot of words at the same time, which can be painfully slow.

I'm currently using a MySQL database, but willing to switch it out for something better suiting my needs, maybe some kind of NoSQL or elasticsearch. But I haven't been able to find any examples of how to accomplish what I'm looking for in any other type of databases. So if anyone can help me out, I would really appreciate it.


texts: Managing code and deployments to Amazon Redshift

texts:
I am finding it difficult to manage and deploy schema changes to my Amazon Redshift database.  Ideally, I would like is to have the code for my database under source control and use a schema comparison tool (like Redgate's Schema Compare or Microsoft's Sql Server Data Tools) to generate a delta script between the repository and the target db that can be used for deployment.  However, I can't find anything out there that does this.

Would anyone out there like to share how they are managing and maintaining their redshift codebase?


texts:
To fix password security issues we need to set SQLNET.ALLOWED_LOGON_VERSION=12.  According to the Oracle Database Net Services Reference:

  Note the following implications of setting the value to 12:
  ...
  Releases of OCI clients before Oracle Database 10g and all versions of JDBC thin clients cannot authenticate to the Oracle database using password-based authentication.

That statement looks really bad to me.  I don't want to choose between decent password security and the recommended JDBC driver.

The good news is that the manual might be wrong.  I can connect withSQLNET.ALLOWED_LOGON_VERSION=12 and a JDBC thin client.  I did have to upgrade ojdbc.jar from 11.2.0.1.0 to Oracle 11.2.0.3.0, but that is expected and also proves that the setting is truly taking affect on my server.

What's going on here:

Is the manual wrong?
Am I reading the manual wrong?
Is it only working in my simple test case as some undocumented feature, but will fail with other configurations?

We have thousands of users with many different configurations, I cannot rely on just my simple test.  Does anyone else have experience with both SQLNET.ALLOWED_LOGON_VERSION=12 and JDBC thin clients?


texts: Reset Variable when row value is changed

texts:
Below is an example of a SELECT statement. I have a variable @total that is set to 0, which works fine if I am doing a WHERE clause but I need the final total for each agent. Currently how this works is that each agent's records display and their total at the end. My question is how can I reset the variable when the agentID changes to the next agent and only display the final count.

select agentID, points,
@total:= @total + points AS Total, comment
from attendance, (select @total:= 0) v group by agentID, incurredDate

This is what I am trying to achieve.

=======================
agentID    |    total
=======================
brwilson   |      2
vimunson   |      5

http://sqlfiddle.com/#!2/d53222/8


texts: Change SQL Server settings without restarting services

texts:
I can change SQL Server settings using Management Studio or programmatically as described e.g. here.

I would like to make this changes WITHOUT the need to restart SQL Server services. Is there any trick to do so??


texts: I'm running SQL Server 2014. I was checking logs to ensure the automated nightly Backup Database job that was scheduled was completing okay. I noticed another BACKUP DATABASE job had been running nightly. I would like to see if I can disable this job, but I have no idea where it is scheduled. It is not listed in the Jobs folder under SQL Server Agent. The message in the log states "BACKUP DATABASE successfully processed 0 pages in 15.770 seconds". When I look at the "physical_device_name" for the jobs I'm aware of it shows the filepath of the backups. The mystery backups just show a long alpha-numeric string in curly brackets. Anybody know what this is? Is it safe to disable? How do I disable?


texts: Handling user logins while using a multiple schema design to support multi-tenancy

texts:
I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I want all data related to organisation A to be stored in a schema named A and all data for organisation B to be stored in schema named B etc..

What's confusing me is how to handle user logins. Let's say Amy is a member of Organisation A and Ben is a member of Organisation B. When Amy goes to example.com to access my web application she's presented with a login form, which asks for her username and password. Now, Amy's username and password is stored in the users table of schema A but how does the web application know where to look to authenticate her?

I've never used multiple schemas in a web application before so in the past I would create a fixed username, i.e. webapp for the application, which would log in and check the credentials supplied by Amy against the data stored in the public.users table (where public is the default schema created by PostgreSQL) but with the design I'm thinking of using, there is no way to tell which schema Amy belongs to and as a result, it wouldn't be easy to find the correct users table to authenticate her.

One approach would be to stop using a users table for authentication and create a role with the LOGIN privilege for the database for every user, then only give that user permission to interact with the appropriate schema. That's a solution I've seen but not one I really want to use. I assume there's a better way but I just haven't been able to find it.

So, to summarise, all I want is for users to be able to log in to my app via the web interface and be restricted to the schema of the organisation they belong to. What's the best way to accomplish this, bearing in mind I intend to have several organisations and users in the long run.

All help is appreciated. Thanks in advance.


texts: Oracle Function Accepting Precision 2 Number

texts:
I'm trying to define an Oracle function that only accepts a NUMBER with a precision of, at most, 2 digits:

create or replace FUNCTION ftest(x NUMBER(2)) RETURN NUMBER IS
  BEGIN
    RETURN 42; -- body of function is not relevant for this question
  END;

However, it does not compile:

Error(1,24): PLS-00103: Encountered the symbol "(" when expecting one of
       the following:     := . ) , @ % default character The symbol ":="
         was substituted for "(" to continue.

How can I fix ftest's definition to only allow an input NUMBER of precision 2?


texts: Why is intarray's push `+` so much faster than array-to-element concatenation `||`?

texts:
When it comes to using arrays of ints, I always default to using intarray. But, now I'm wondering why is the default concatenation so much slower.

This answer probably requires some understanding of internals. Internally, intarray's calls intarray_push_elem, and || calls array_append

Sample Data

CREATE EXTENSION intarray;
CREATE TABLE foo AS
  SELECT ARRAY[x::int] AS bar
  FROM generate_series(1,1e7)
    AS gs(x);

Same result

SELECT ARRAY[1] + 42 = ARRAY[1] || 42;
 ?column?
----------
 t
(1 row)

With ||

EXPLAIN ANALYZE
  SELECT bar || 42 FROM foo;

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..198528.55 rows=9999884 width=25) (actual time=0.029..5193.752 rows=10000000 loops=1)
 Planning time: 0.051 ms
 Execution time: 5679.489 ms
(3 rows)

With + from intarray extension

EXPLAIN ANALYZE
  SELECT bar + 42 FROM foo;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..198528.55 rows=9999884 width=25) (actual time=0.026..2066.786 rows=10000000 loops=1)
 Planning time: 0.052 ms
 Execution time: 2388.462 ms
(3 rows)

Routinely intarray's + is faster than ||. Why is || so slow?


texts: Result occurs often with fast random select query

texts:
I have this query to display 4 random photos on a website (I have it from here (when I asked for something faster than ORDER BY RAND())):

(SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
    `users`.`username`,
    `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)
UNION ALL
(
SELECT   `p1`.`id`, `p1`.`url`, `p1`.`hits`, `p1`.`created_at`,
        `users`.`username`,
        `users`.`displayname`
FROM     pics p1
JOIN
        (SELECT ceil(rand() *
                (SELECT max(id)
                FROM   pics)) AS id
        ) AS p2
JOIN     users
ON       users.id = p1.user_id
WHERE    p1.id >= p2.id
ORDER BY p1.id ASC limit 1
)

It is fast, but it seems, that one particular photo is displayed too often, about every 10th refresh of the page, or so. It has not id 1 (it has 1131), but is the 1st photo of a certain user.

Could it be, that somehow gaps in the pics table could be the reason? I have no idea what else could be the reason.

edit:

SELECT id FROM pics WHERE id < 1131 ORDER BY id DESC LIMIT 1; outputs this:

+-----+
| id  |
+-----+
| 923 |
+-----+


texts: Lets say I have a table to hold Tasks and then I have a table to hold Sub Tasks, is it better to break Tasks and Sub Tasks into separate tables or keep them both in the same table with a possible ParentId column.  What if a Parent Task could also be a Sub Task, does this change the design?


texts: Log file shrinking automatically due to UserShrinkSize property?

texts:
We have a SQL Server 2014 system that is apparently shrinking one or more log files automatically.  We are getting a low free disk space alert (triggered at <5% free) at 1:00 AM, but when we check the system in the morning there is about 50% free.  We manage about 160 SQL Servers between the various environments and they're all set up with a good degree of standardization.  Auto shrink is NOT enabled.  I have found what was causing all of the database writes to cause the log files to grow, and that has been addressed, so the cause of the log file growth is not the subject of this question.

I found this SQL Server 7 era post, Transaction log shrinking mysteriously, which states that transaction logs will be shrunk automatically if UserShrinkFile is set to anything other than -1.  This seems so explain what we've noticed, but with a bit of a wrinkle.  There is no UserShrinkFile property in the header, but there is a UserShrinkSize property, and on three of the databases it is set to a positive integer.

Oddly, I cannot find anything salient about UserShrinkSize.  Anywhere.  The only place I've found reference to it is posts that just list it along with everything else in the header.  I assume it is the newer version of UserShrinkFile, and that it works that same way as stated in the linked article above.  However, getting some confirmation  of these assumptions would be fantastic, and this would be a lovely addition to utilities that check for configuration items that might cause unwanted behavior.

It would be good to know how this property affects the system, as it obviously doesn't just shrink the log files immediately all the time.  Right now all three of the databases that have a positive UserShrinkSize value have log files ranging from 40 - 200 GB with 99% free space, so it's leaving me wondering whether it only shrinks them when it can't grow the log file.


texts:
I have a table with (suppose) just 2 columns:

- fecha (a date)
- ignicion (0/1 representing boolean state)

The goal is to retrieve which periods of time was ignicion held at '1', for such task I figured out an iterative approach like the one in the following queries:

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 00:00:00-03' and fecha < '2016-05-12 00:00:00-03';

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=0  and fecha > '2016-05-11 03:01:38+00' and fecha < '2016-05-12 00:00:00-03';

select min(fecha) from reports.avl_historico_11557000631206 where ignicion=1  and fecha > '2016-05-11 03:42:19+00' and fecha < '2016-05-12 00:00:00-03';

Programming that is no problem, but executing it might be an issue, as the report may involve a month's wide of data, and not from just a single table being "scanned", but around 500 with the same data structure (just different table name).

Any ideas on how this could be achieved in a cleaner/better/faster way?

I've been advised on "window" functions, but I don't really know if any of those actually fit this task.


texts: AFTER DELETE trigger will not fire during a cascade delete

texts:
I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong.  In addition, each Aisle has a Node at which it starts and ends.

Aisle Table:

Aisle char(4)
Start_Node int not null
End_Node int not null
...

This table would have foreign keys referencing the Node table (deleting on cascade), but this is apparently not possible (multiple cascade paths?) so I have to enforce this rule myself in a trigger on the Node Table...which looks like so:

Node_ID int
x float
y float
Aisle char(4) [MAY BE NULL]

Now, the trigger on the node table does some data integrity stuff, and I'm pretty sure a trigger is the right place to do it since I absolutely cannot think of a situation where I wouldn't want the trigger to fire -- but it doesn't always fire...

If I manually delete an entry from the Aisle table to which rows in Node reference, the deletion cascades to the Node table and the trigger definitely fires, as expected.

But if I delete a record from the Node table to which Aisle rows reference, the trigger on the Node table enforces the pseudo-foreign key constraint in Aisle (deleting rows in Aisle by design), which should then cascade the deletions back to Node and call the trigger again (by design), but I have verified multiple times that the trigger never gets called after the cascade occurs.

What's going on here?  I'm clearly missing some detail on how triggers and FKs interact...for the record, nested triggers are active, I'm using an After Delete trigger, and I'm definitely no DBA.


texts: How to find the AD user that logged in to SQL Server using SQL Login

texts:
Some users are logging into database servers locally, opening SSMS and logging in to SQL Server using SQL logins to run queries.

How can I find the AD user that logged in to SQL Server using a SQL login on the local database server using SSMS?


texts: Database Compatibility Requirement for Transactional Replication

texts:
Does the subscriber "within two version requirement" for transactional replication from this MSDN article apply to the SQL Server Instance, Database Compatibility Level, or Both?

For example, would replication work with this environment setup?

Publisher and Subscriber SQL Instance: SQL Server 2014 Enterprise Edition 64-bit (12.0)
Distributor Database Compatibility Level: 110
Publisher Database Compatibility Level: 110
Subscriber SQL Instance: SQL Server 2017 Enterprise Edition 64-bit (14.0)
Subscriber Database Compatibility Level: 140


texts: Internals of Windows-to-Windows SQL Server data transfer

texts:
This is a sort of generic question and I don't require a very specific answer, but let me ask it by describing a scenario.

You connect to a remote db in SQL Server Studio 08, and run a select * insert into a table in a local db. Assume the schemas are identical, this is not a question about SQL syntax or table design.

If, on a linux box, I write an incredibly light weight application that utilizes the Sybase driver (such as Perl and DBI) to copy only that same table. You do this with a select *, and construct one nice multi row insert into a local mysql db.

The times aren't that different until you start scaling up the rowcount many times over. Assume the hardware is identical, and also the mysql config is ultimatly optimized. The windows-to-windows transfer will always be faster. Now, perl's compile time, the little initialization time is negligible as you scale up. They are both socket based data streams.

My question is, plainly, why the SQL Server scenario so much faster?  Is it the way they format data?  Is it Windows' domination of the hardware for maximum optimization?  How does  SQL Server  make so much more use of the bandwidth when it's Windows-to-Windows?

Edit: @gdn I realize its row by row, i guess what im really wondering is how these types of bulk operations are optimized as such. Coming experience with these client query based apps first, im finding hard to imagine how its done so much more efficiently.


texts: Load clob from "insert into" output file

texts:
In the process of moving data from live to the dev system (completely isolated), I have been given a series of "insert into" files for the affected tables.

One of these tables contains a clob that houses stupidly massive sql queries, so the file contains stupidly massive amounts of text that cannot be inserted via the insert statement...

So, the question is, how can I import the data for this table?


texts: Selecting/Matching cells that hold the same values as opposed to ID in MySQL/phpMyAdmin

texts:
Can someone point me in the right direction?

Given tables:

actor_uri_address

----------------------
| ID | URI | Address |
----------------------
|    |     |         |
----------------------

actor_full_name_opc

-------------------------------------------------
| ID | Given Name | Surname | Outward Postcode  |
-------------------------------------------------
|    |            |         |                   |
-------------------------------------------------

I am trying produce a table that contains actor URI, first names, surnames and addresses of actors living at the same addresses on the list (I do not need to supply the address at query time, rather the duplicate values in the address column) on phpMyAdmin.

Below is my query construct so far. Despite all efforts, I have only been able to get it to run and return the entire URIs, first names, surnames and addresses. I am totally stuck on how to have it narrow down to only the actors living at the same address.

SELECT   `actor_uri_address`.`URI`, `actor_full_name_opc`.`Given Name`,
         `actor_full_name_opc`.`Surname`, `actor_uri_address`.`Address`
FROM     `actor_uri_address`
    LEFT JOIN `actor_full_name_opc` ON `actor_full_name_opc`.`ID` = `actor_uri_address`.`ID`
GROUP BY `actor_uri_address`.`URI`, `actor_full_name_opc`.`Given Name`,
         `actor_full_name_opc`.`Surname`, `actor_uri_address`.`Address`


texts:
We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField).

If myField is of type NVARCHAR, we can do a comparison of said field against a string like so: myField = 'foo'.

However, this does not work for fields of type NTEXT.  Thus, we have to do the comparison with a cast:  CAST(myField as NVARCHAR(MAX)) = 'foo'.  This will in fact work if myField is of type NVARCHAR or NTEXT.

What is the performance hit of doing the aforementioned cast on a field that is already of type NVARCHAR?  My hope is that SQL Server is smart enough to dynamically recognize that myField is already of type NVARCHAR (effectively turning the CAST into a no-op).


texts: Most Efficient / Easiest Way to Capture Multiple Entries in Same Field

texts:
I have been tasked with replicating a database that has been for some time used along with Cold Fusion and cleaning it up for use with a more modern web portal.

In an effort to fork out various lookup tables, I've ran into a particular column whose field may have up to eight different e-mail addresses for personnel (delimited by semicolons).

The reason for the multiple e-mail addresses is those individuals map to the same case / issue #.

What is the best way for me to parse through the current table and "tokenize" each e-mail address in order to copy it and other fields over to a new lookup table whereby each e-mail address gets its own row in the new lookup table?

In the meantime, the "legacy" database used with Cold Fusion will be in use until I've migrated things over satisfactorily.

Also, I'm a total newb to SQL so any links to similar problems to enhance your answer would be greatly appreciated.


texts:
I need help with creating a trigger that will generate a protocol number. The following sequence must be followed:

323926YYYYMMDDXXXXXX

323926 = fixed number
YYYY   = year
MM     = month
DD     = day
XXXXXX = Number we must automatically generate from 1 that will go to 999999

My difficulty is that this incremental number should return to 000001 after the course of the day, summarizing: Every day the first protocol generated must start with the number 000001

My Code :

CREATE OR REPLACE FUNCTION crm.novo_ticket()
RETURNS trigger AS
$BODY$
BEGIN

NEW.protocolo =  '323926' || TO_CHAR(NEW.data_cadastro,'YYYYMMDD') || '000001';

RETURN NEW;

END $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Sorted out

SELECT
    lpad(CAST((SELECT COUNT(1) + 1 INTO quant
FROM
    crm.ticket a
WHERE
    a.data_cadastro::DATE = CURRENT_TIMESTAMP(0)::DATE;)as VARCHAR),6,'0');


texts: Formatting out a column value - mm:ss.hh

texts:
I have a time in seconds and I want to convert it to the following formats possibilities:

121.17 -> 2:01.17 |
34.28 -> 34.28 |
133.56 -> 2:13.56

I am using the following:

IF(Time > 60, CONCAT(FLOOR(Time/60), ":", Time - (FLOOR(Time/60)*60)),
   Time) as TTime,

Results:
2:1.17 (incorrect) |
34.28 (correct) |
2:13.56 (correct)

I tried a number of things (LPAD, CONVERT, etc)

Please advise. MySQL 5.6.17


texts: Workaround to implement Minimum Degree of Parallelism

texts:
Please correct me if I am mistaken but as I understand it, when a query is being scheduled for execution, the engine takes into account the number of available free threads and will adjust the query plan accordingly.

For example, if a machine is highly loaded such that only a single thread is free, a query that normally runs using multiple threads on an unloaded machine could be executed as single threaded.

Is there a way to dynamically query the number of free threads available so that a particular critical query (that runs unacceptably as MaxDop < X) could be delayed (while/waitfor) for execution until a minimum free thread count is available?

Update: A starting point based on Kin's answer:

This seems to follow a simple load test but never returns all idle for some reason.  I know nothing about this table.

select count(*) as Cpus,
       sum(IsIdle) + 1 as IdleCpus  -- +1 since current query should be excluded
  from
     (
        select Cpu_id,
               min(convert(int, is_idle)) IsIdle
          from sys.dm_os_schedulers
          group by Cpu_id
     ) q
   ;


texts: postgres: archive_command can't write to an nfs share

texts:
I've enabled the archive_mode with wal_level = hot_standby on a master server. I intended to send the wal logs to a nfs share with:

archive_command = 'test ! -f /mnt/psql_wal/%f && cp -i %p /mnt/psql_wal/%f'

But it keeps telling me this:

sh: /var/lib/pgsql/archive_wal.sh: Permission denied
LOG:  archive command failed with exit code 126
DETAIL:  The failed archive command was: test ! -f /mnt/psql_wal/000000010000000000000001 && cp -i pg_xlog/000000010000000000000001 /mnt/psql_wal/000000010000000000000001
cp: failed to access ‘/mnt/psql_wal/000000010000000000000001’: Permission denied

Basic nfs permissions issue, you may think, but no. The following command works:

sudo -H -u postgres cp -i /var/lib/pgsql/data/pg_xlog/000000010000000000000001 /mnt/psql_wal/000000010000000000000001

Is the archive command running under a different user?


texts: Why does SQL Server do lots of unnecessary clustered key lookups in this SELECT - OFFSET - FETCH?

texts:
I have the following table:
CREATE TABLE [dbo].[MP_Notification_Audit](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [type] [int] NOT NULL,
    [source_user_id] [bigint] NOT NULL,
    [target_user_id] [bigint] NOT NULL,
    [discussion_id] [bigint] NULL,
    [discussion_comment_id] [bigint] NULL,
    [discussion_media_id] [bigint] NULL,
    [patient_id] [bigint] NULL,
    [task_id] [bigint] NULL,
    [date_created] [datetimeoffset](7) NOT NULL,
    [clicked] [bit] NULL,
    [date_clicked] [datetimeoffset](7) NULL,
    [title] [nvarchar](max) NULL,
    [body] [nvarchar](max) NULL,
 CONSTRAINT [PK_MP_Notification_Audit] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MP_Notification_Audit] ADD  CONSTRAINT [DF_MP_Notification_Audit_date_created]  DEFAULT (sysdatetimeoffset()) FOR [date_created]
GO

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

There are over 10000 rows in the table with a [target_user_id] of 100017.
When I execute the following query:
SELECT
    [target_user_id], [patient_id]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 9200 ROWS
FETCH NEXT 10 ROWS ONLY

... I get the following actual execution plan:

Why did SQL Server need to do 9210 instead of 10 clustered key lookups?  The index [IX_MP_Notification_Audit_TargetUserDateCreated] should've allowed it to figure out the 10 RIDs it needed to retrieve to get [patient_id], and only do 10 clustered key lookups, right?
I've also discovered some more odd behaviour - it looks like SQL Server 'punishes' you for not selecting unindexable columns.  If I instead OFFSET 10000 rows, I get the following execution plan:
SELECT
    [target_user_id], [patient_id]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 10000 ROWS
FETCH NEXT 10 ROWS ONLY

... with the recommendation of creating an index that includes [patient_id], and an inefficient clustered index scan for the whole table.  The time taken was 0.126s, however this could apparently have been a lot better because when I add the unindexable column [title] to the query, I get this:
SELECT
    [target_user_id], [patient_id], [title]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 10000 ROWS
FETCH NEXT 10 ROWS ONLY

... and the nonclustered index is still used, the time taken only being 0.032s.  Does SQL Server basically say "you could've created an index to do this more efficiently, so we're not even going to use the index you do have and we're going to do the lookup inefficiently to punish you", or am I missing something?


texts: Inserting BLOBs through PHP are always 0 bytes

texts:
I'm making a site that allows users to upload small thumbnails for books they post. I want to store them as a BLOB.

The first row there was inserted manually through phpMyAdmin. The second row was uploaded through a PHP script. The two string values are evidently going through, but the blob I'm uploading isn't.

error_reporting(E_ALL);
ini_set("display_errors", 1);

include './getConnection.php';
include './getTokenData.php';

$conn = getConnection();

$image = addslashes(file_get_contents($_FILES['pic']['tmp_name']));
$name = $_POST['name'];
$desc = $_POST['desc'];

$SQL = "INSERT INTO  `db_bookclub`.`t_book` (
`c_name` ,
`c_description`,
`c_image`
)
VALUES (
?,?,?
);
";

echo $image;

$stmt = $conn->prepare($SQL);
$stmt->bind_param('ssb', $name, $desc, $image);
$stmt->execute();

When I echo $image, the image data (a whole bunch of characters) are sent to to the client, showing that the image is making it to the PHP script unmolested.

I have a feeling this is because of a settings file I haven't heard of. I've also tried mysql_real_escape_string in place of addslashes.


texts: Please, look at this screenshot of iotop tool output (Linux). There is long-running batch update process on table with 20+ million rows. Does it mean that postgres is limited by I/O disk performance?


texts: Why is this PostgreSQL not null constraint not catching this insert?

texts:
I have a table for users that includes two columns for contact information. A user can provide a phone number, an email, or both, but not neither.

I have the table set up with the following constraints

create table application_user(
    -- other columns, primary key, etc.
    phone text,
    email text,
    constraint valid_email check (((email IS NULL) AND (phone IS NOT NULL)) OR (email ~ '(?:[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])'::text)),
    constraint valid_phone check (((phone IS NULL) AND (email IS NOT NULL)) OR (phone ~ '\d{10}'::text))
);

The idea is to make sure the email is valid if present and the phone is valid if present. However, when I run the following insert statement:

INSERT INTO application_user (email, phone) VALUES (null, null);

The insert still completes. What am I doing wrong here? I'm using PostgreSQL 12 if that matters.


texts: PostgreSQL to HDFS to Spark, or PostgreSQL directly to Spark SQL?

texts:
If the end goal is to perform computations with Spark, what are the reasons one would first transfer Postgres data to an HDFS (with Sqoop) vs. just using Spark SQL with Postgres directly (with the JDBC?)

The answer to this question (which is referencing MongoDB, not PostgreSQL...but still applicable) mentions that these are the two options, but I'm wondering what would motivate choosing one over the other.


texts: Merge Join SSIS Throwing Error Despite Sort and Matching Meta

texts:
I'm attempting to Merge Join two data sources. I ensured the metadata matched and that the sort key was present in the metadata, but it is still giving me the error:

  Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata."

All of the support articles and matching forum questions all mention metadata and sorting. I even type cast the right data set to match the left... Not sure what to do here. Flow and metadata are pictured. First is the left join, second is the right join. The last entry is the column I wish to join on in both metadata tables.


texts:
I came across this answer on Stack Overflow. The question itself isn't really relevant. The answer outlined a database design that I will paraphrase here:

Consider the relations Organization, Account, and Invoice. The Organization key is OrgName. The Account key is OrgName, AccountID. The Invoice key is OrgName, AccountID, and InvoiceNumber. As suggested by the keys, an organization has many accounts and an account has many invoices. (Note that AccountIDs can be reused across organizations and InvoiceNumbers can be reused across Organizations and Accounts - hence the need to have two and three attribute keys. Clarified further, AccountID and InvoiceNumber are not individually unique within their respective tables.)

It strikes me that if you need to move an account to another organization, that would necessitate an update not only in the Account relation, but also in the Invoice relation. This "redundancy" leads me to believe that a normal form is violated in this design, though I've been unable to identify which one.

Thus my questions is: Is a normal form violated in this design? If so, which one and why?


texts: Does Lazy Writer spend resources on flushing out pages from the buffer pool?

texts: In MS SQL Server, does Lazy Writer spend resources to make pages out of the buffer pool after periodically checking free buffer list and marking some clean pages as free? Or free marked clean pages continue to stay in the buffer pool until another page is needed to retrieve from the disk and the latter overwrites the free marked clean page?


texts: How to order rows randomly but keep groups together?

texts:
My table looks like this:

GroupID INT NOT NULL
SomeValue INT NOT NULL

I'd like to keep rows with the same GroupID together in the result set. The groups themselves should be ordered randomly, though. SomeValue is supposed to be the secondary sort criterion. Like this:

GroupID, SomeValue
13, 1
13, 2
7, 1
7, 2
11, 1
11, 2
...

ORDER BY GroupID, SomeValue does not order groups randomly. ORDERBY NEWID() is totally random. ORDER BY SomeHash(GroupID), SomeValue comes to mind but I need a new random order each time.

How can this be done?


texts: How to handle process blocking in SQL Server Profiler?

texts:
I have run a SQL Profiler Trace which detected a process blocking. Now what should i do, if it always brings the same blocking event, each time duration is getting bigger. I have only one transaction which created a block, and I want it to be fixed in a trace only once, why it keeps repeating?

Thanks


texts:
I'm building a database on Microsoft SQL Server to teach myself SQL. I am a big UFC fan so I'd like to build a database on UFC fighters.

I'm building two tables fight and fighter. The fighter table will have stats like weight, height, birthday, etc. as well as a column for wins, losses, draws, and NC (no contest). The fight table will have a winner_id column and a loser_id column as well as is_draw and is_nc columns.

How can I best create a link between the winner_id/loser_id columns from the fight table to the wins and losses columns for the fighter table?

There is one caveat. Usually MMA fighters will come to the UFC with experience in other organizations. So they will already have a record of wins and losses prior to the UFC. My database will only record UFC fights. So I can't have those previous wins and losses in the [fight] table. For that reason, I've decided to use triggers for now.

Question: is there a better way to do this other than triggers? I'm thinking setting the wins column = a base number + the number of times their fighter_id is listed in the winner_id column. But how would I do that? Would I need to use a view?

Here is the trigger I have created.

CREATE TRIGGER tr_fight_ResultUpdateFighter
ON fight
FOR INSERT
AS
BEGIN
    -- Win
    DECLARE @winner_id int
    DECLARE @loser_id int
    DECLARE @is_draw bit
    DECLARE @is_nc bit
    SELECT
        @winner_id = winner_id,
        @loser_id = loser_id,
        @is_draw = is_draw,
        @is_nc = is_nc
        FROM inserted

    --Wins
    UPDATE fighter
    SET wins = wins+1
    WHERE fighter_id = @winner_id

    --Loss
    UPDATE fighter
    SET losses = losses+1
    WHERE fighter_id = @loser_id

    --Draw
    UPDATE fighter
    SET draws = draws +
        CASE
            WHEN @is_draw = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

    --NC
    UPDATE fighter
    SET nc = nc +
        CASE
            WHEN @is_nc = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

END


texts:
We are in planning to upgrade from SQL Server 2008 R2 to SQL Server 2014, with the intention of using Availability Groups.

One of the secondaries will be used to do backups.

I have tested and found that I can do point in time restores as long as I have the copy-only full, and the chain of log backups that span the copy-only up to the time we want to restore to.

We do NOT use differentials, and have no plans to.

I cannot see any downside to this approach other than perhaps not being able to shrink the data file without a "normal" backup. We do not normally shrink our databases unless we do something to blow them up, and by then we are in intervention mode anyway.

So my question is, will I regret going this route?


texts: dm_db_missing_index_details - does the order of equality_columns matter

texts:
I've been investigating some databases using the dm_db_missing_index_group_stats DMV, and have a question about the equality_columns field in the dm_db_missing_index_details view.

MSDN says that this holds "a comma-separated list of columns that contribute to equality predicates of the form: table.column = constant value"

I have a table which is represented in three of the highest cost missing indexes, with the following equality columns:

client_product_id
client_id, client_product_id
client_id, client_product_id, status

Does the order of the columns in the second and third index groups matter? e.g. if I created an index on client_product_id, client_id, status, it seems that it should satisfy all three missing index cases, on the grounds that a query like client_id = 123 AND client_product_id = 456 should be satisfied equally regardless of whether client_product_id or client_id comes first in the index definition.

(obviously if it was an inequality predicate, this would not be the case)


texts: Force collation when querying Visual FoxPro through OPENROWSET

texts:
How can I force collation in the following DBF select statement:

SELECT * FROM OPENROWSET
  ('VFPOLEDB','FilePath\FileName.DBF';'';'','SELECT *  FROM FileName')

I've tried with COLLATE to each column individually but it doesn't work.

(For background, see this thread.)


texts: Is it possible to execute an "update" trigger if all columns except one have changed?

texts:
Assume I have the following table:

CREATE TABLE data (
    key TEXT primary key,
    some_interesting_value TEXT,
    inserted TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    last_seen TIMESTAMP WITH TIME ZONE
);

Then I can create a trigger to set the "updated" column like this:

CREATE TRIGGER set_updated_col
    AFTER UPDATE ON data
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE set_updated_timestamp();

The last_seen column in this case contains a timestamp which is updated every time a process is run (with some conditions). However, this value is not interesting to track table updates. In my case I have many such tables. They are all contain information about physical network devices, and the "last_seen" value is important for each entity.

The trigger above will set the "updated" timestamp even if only the "last_seen" value is modified.

In my case I want to avoid this.

I could manually name all columns in the WHEN clause, but then, if I add a column to the table, there is a risk that this gets forgotten in he trigger.

Is there a way to "remove" the last_seen column from OLD.*?

As code example:

-- This should NOT trigger a change to `updated`
UPDATE data SET last_seen=NOW() WHERE key='foobar';

-- this SHOULD trigger a change to `updated`
UPDATE data SET some_interesting_value='foo', last_seen=NOW() WHERE key='foobar'


texts: Replicated MySQL write performance query

texts:
Whilst I've worked with MySQL for years, I've never had to created a replicated database, and after researching the topic I have a query about performance.

I'm thinking of an architecture based on 5 servers, one of them designated as the master and will handle ALL write transactions, whilst the other 4 will handle read requests.

My question is, if the master server is at 90% CPU usage handling writes, are the other servers in the cluster also under the same strain due to each write being sent to all other servers? or is something going on where the bulk of the processing is handles on the master so the slaves do less work processing the write transaction?


texts:
Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ?

Thank you for help.


texts: Modelling a database for products, categories and features

texts:
Good day everyone. I am building a database for an application and I have thought of two different approaches for achieving the same result, and I would like your opinion.

Is basically the classical product database, so at the moment I have products, each of theme belonging to a category. Each product has different features but the products that fall in the same category all share some common features.

At the moment I thought of this two structures:

A products table, just to store the name.
A product_categories table, to store the categories, and an intermediate table to link both.
A features table, to store the different features.
A product_features to link the product with it's features and store their value.

Since the products that fall on the same category have some shared features, I also thought of extracting the common features and creating a table for each product, so the result would be something like this:

A table for each product, to store their name and common features.
A features table, to store the different features that are not common.
A product_features to link the product with it's features and store their value.

I believe the first structure will be easier to maintain if I need to add newer products down the line as I would not need to create any new tables, but I kind of feel that the second approach would be more correct.

Let me know your thoughts!


texts: Update large number of row in MySQL table

texts:
I'm using relational database(MySQL 5.7). On this database i have a table called customer_transaction. On this table i have 4 columns: id, customer_id, type, amount

|id|customer_id |type     |amount|
|--|------------|---------|------|
|1 |44          |Credit   |50    |
|2 |44          |Credit   |20    |
|3 |44          |Debit    |30    |
|4 |10          |Credit   |30    |

now i am introduce a new balance column(current balance) on this table like below.

|id|customer_id |type     |amount|balance|
|--|------------|---------|------|-------|
|1 |44          |Credit   |50    |50     |
|2 |44          |Credit   |20    |70     |
|3 |44          |Debit    |30    |40     |
|4 |10          |Debit    |30    |-30    |

The problem is, on the customer transaction table, their was nearly millions of row and all balance column was 0.00.

So i want to re-sync all balance data. But i'm confused how to recalculate and update all those row. Can i update this by MySQL query or calculate and update from my application (Laravel-PHP).

Solution 1: suggested by @GMB

In MySQL 5.x, where window functions are not available, an option uses a correlated subquery to compute the balance:

update customer_transaction ct
inner join (
    select
        id,
        (
            select sum(case type when 'Credit' then amount when 'Debit' then -amount end)
            from customer_transaction ct2
            where ct2.customer_id = ct1.customer_id and ct2.id <= ct1.id
        ) balance
    from customer_transaction ct1
) ctx on ctx.id = ct.id
set ct.balance = ctx.balance

But their is a problem. We are doing those process on a live server and it take's some times to execute. i think, during that execution time, my transaction table remains locked. Is their any way to preventing locking.


texts: What are the naming rules for named instances on a cluster?

texts:
I am planning to install two SQL Server 2008 instances on an active/passive cluster. Each instance is supporting the same application, but the DBs will be supporting specific geographic locations. I'd like to name the instances to reflect the name of the application and the site, factoring in that at least one instance will be a named instance.

My understanding is that the SQL Server Virtual Name has to be registered in AD and can only be used once. If this is true, I can not have instances on this cluster named \EAST and \WEST as the  can't be registered in AD more than one, or can I?

Can I have two named instances on a cluster with the same base instance name or will the base instance name have to be unique for AD registration purposes?


texts: Why query with SQL_NO_CACHE runs slower on the first run?

texts:
Query is made to be as fast as possible, using only SQL_NO_CACHE 1 in select and limit 1. The results are pretty curious; on the very first run it takes 0.125 - 0.203 sec. and all next runs take usually less than 0.040 sec. Is it some kind of warm-up, or is it related to building query process?

It is not an issue right now, but I want to know why it is so, and avoid it if possible.

Engine - InnoDB
MySQL version - tested as on 5.6 as 5.7


texts:
I have a table called Products with ID(PK, Clustered), Name, Price, Brand, ...., RetailerID, SKU, ModelNumber. My query have

SELECT ....
FROM Products
WHERE RetailerID = @RetailerID AND
      ( (@SKU != '' AND SKU = @SKU)  OR (@ModelNumber != '' AND ModelNumber = @ModelNumber))

I am going to add index for this query. Should I add 3 indices on RetailerID , SKU and ModelNumber or 2 compound indices on (RetailerID, SKU) and (RetailerID, ModelNumber) or 1 compund index on (RetailerID, ModelNumber, SKU)


texts:
As I mentioned in this question I'm trying to understand the working of clock hands. Simply put clock hands move when there is memory pressure so that cache entries can be removed or the cost can be reduced.

With what I've learned I now try to interpret dm_os_memory_cache_clock_hands.
When I check dm_os_memory_cache_clock_hands on one of my servers (SQL Server 2016 SP1, 32 RAM) I get a little confused about what I see.

The round_start_time for CACHESTORE_SQLCP  is approx 31 hours, so I would think this cache isn't suffering a lot of memory pressure because it takes a while to complete one round.
On the other hand I see that last_tick_time is changing with each refresh, so the hand is moving.
Another strang fact is that removed_all_rounds_count is also changing. So entries are removed from the cache. The clock_status is always suspended.
The removed_last_round_count is about 68000.

So my conclusion is that there is pressure on CACHESTORE_SQLCP because the hand is moving and removed_all_rounds_count is changing and removed_last_round_count is high.
Is this a correct interpretation of the dmv?

What I don't understand is why round_start_time is so high on a 32GB server and the hand is moving all the time.

I used following script (that I found in the tigertoolbox) to convert last_tick_time to datetime:

    declare @ticks_per_ms bigint,@now DATETIME
    select @ticks_per_ms=ms_ticks from sys.dm_os_sys_info
    set @now=getdate()

    CASE WHEN last_tick_time BETWEEN -2147483648 AND 2147483647 AND
         @ticks_per_ms BETWEEN -2147483648 AND 2147483647
    THEN DATEADD(ms, last_tick_time - @ticks_per_ms, @now)
    WHEN last_tick_time/1000 BETWEEN -2147483648 AND 2147483647 AND
         @ticks_per_ms/1000 BETWEEN -2147483648 AND 2147483647
    THEN DATEADD(s, (last_tick_time/1000) - (@ticks_per_ms/1000), @now)
    ELSE NULL
    END AS last_clock_hand_move

I restarted the SQL Server Service so that the DMV is cleared. Then I monitored the local pressure limit and the DMV to see what actually happens.

I don't know if my findings are correct but this is what I discovered:

According to BOL the cache pressure limit of CACHESTORE_SQLCP is 5GB (the visible target memory of the server is 24GB). So the internal clock hand should be triggered at 62.5% of 5GB (3200MB).
I noticed the internal hand got the status running around approx 3142MB (a little earlier then calculated). This doesn't match with the info on BOL or I did a miscalculation.
removed_all_rounds_count increased when last_tick_time changed
The hand didn't move anymore untill the threshold was reached and again entries where removed. This was repeated untill a round was completed and then it started all over.


texts: Is it faster to split a large table into 12 rolling monthly tables & use UNION them for reports or keep large table & delete rows older than 1 year?

texts:
My co-worker wants to split a large 158M row stats table into stats_jan, stats_feb, ... and use UNION to select from them for reports. Is that standard practice and is it faster than to just use the large table in place and delete rows older than one year? The table is many small rows.

mysql> describe stats;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| badge_id       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| hit_date       | datetime            | YES  | MUL | NULL    |                |
| hit_type       | tinyint(4)          | YES  |     | NULL    |                |
| source_id      | bigint(20) unsigned | YES  | MUL | NULL    |                |
| fingerprint_id | bigint(20) unsigned | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

I did manually split the table up and copy the rows into the appropriate month tables and created a giant UNION query. The large UNION query took 14s versus 4.5m for the single table query. Why would many smaller tables take a significantly shorter time than one large table, when it's the same number of rows total?

create table stats_jan (...);
create table stats_feb (...);
...
create index stats_jan_hit_date_idx on stats_jan (hit_date);
...
insert into stats_jan select * from stats where hit_date >= '2019-01-01' and hit_date < '2019-02-01';
...
delete from stats where hit_date < '2018-09-01';
...

The monthly tables have from 1.7M rows to 35M rows.

select host as `key`, count(*) as value from stats join sources on source_id = sources.id where hit_date >= '2019-08-21 19:43:19' and sources.host != 'NONE' group by source_id order by value desc limit 10;
4 min 30.39 sec

flush tables;
reset query cache;

select host as `key`, count(*) as value from stats_jan join sources on source_id = sources.id where hit_date >= '2019-08-21 19:43:19' and sources.host != 'NONE' group by source_id
UNION
...
order by value desc limit 10;
14.16 sec


texts: How to allow a certain domain user to create databases on a remote SQL instance?

texts:
I want to allow for a certain domain user to be able to create databases on a remote SQL instance.
What steps are necessary and is it possible to perform those steps programmatically in a C# application ?
Should I first create a login if doesn't exist and then give it the dbcreator server role?
Related question: Safe and secure implementation of logon trigger in SQL Server 2014 Express edition?


texts: When does Oracle automatically flushes in-memory monitoring information?

texts:
We can use FLUSH_DATABASE_MONITORING_INFO procedure from dbms_stats package. But when Oracle automatically flush this monitoring info without using this procedure? Can we configure it?
I've found this information.

FLUSH_DATABASE_MONITORING_INFO Procedure This procedure flushes
in-memory monitoring information for all tables in the dictionary.
Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and
IND_STATISTICS views are updated immediately, without waiting for the Oracle database to flush them periodically. This procedure is
useful when you need up-to-date information in those views. Because
the GATHER_STATS procedures internally flush monitoring information,
it is not necessary to run this procedure before gathering the
statistics.

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68568
Without waiting for the Oracle database to flush them periodically. How periodically is it? Can we change it?


texts: Listing IDs that match values in two columns of a table

texts:
I'm using SQLite 3.27.2-3 on Debian Buster/stable. I could use some other DB, but for my use case (LuaTeX), something with low overhead seems to make sense.

My use case is that I have a letter (in the sense of correspondence) which refers to other letters in the text. I have a table that is automatically built from references in the text.

Let's say the table looks like this

RowID           From     To
=====           ====     ==
2019.06.05       PC      SW
2019.06.11       PC      SB
2019.06.12
2019.06.15       SB      PC
2019.06.22       DO      SW

and that it corresponds to some rows from a larger table, which I'll call attachments.

And let's suppose there is an abbreviations table which looks like

Abbreviation     Name
============     ====
B                Bashful
D                Dopey
DO               Doc
H                Happy
G                Grumpy
PC               Prince Charming
SB               Sleeping Beauty
S                Sleepy
SN               Sneezy

Then I want a table (abbreviations) that has a list of the abbreviations that occur in my short list of correspondence. This would look like

Abbreviation     Name
============     ====
DO               Doc
PC               Prince Charming
SB               Sleeping Beauty
SW               Snow White

Something like the query below works, approximately. But I also get blank output for when it doesn't match. I'm getting errors from SQLite (or LuaSQL, to be precise) so I'm wondering if it is because of the
blank output.

Better (and simpler) approaches appreciated.

SELECT abbreviations.Abbreviation,
       abbreviations.Name
FROM attachments
LEFT OUTER JOIN abbreviations ON attachments.`From`=abbreviations.Abbreviation
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22')
UNION
SELECT abbreviations.Abbreviation,
       abbreviations.Name
FROM attachments
LEFT OUTER JOIN abbreviations ON attachments.`To`=abbreviations.Abbreviation
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22');


texts: DLL Error While Creating an In-Memory Table in SQL Server 2014

texts:
While trying to create an in-memory table on a pre production box we get the following error.

CREATE TABLE [stage].[MyData]
(
   [ID] [VARCHAR](50) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX imxMyID NONCLUSTERED,
   [Col1] [VARCHAR](15) NULL,
   [Col2] [VARCHAR](5) NULL,
   [Col3] [NUMERIC](12,2) NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

  Msg 41309, Level 16, State 1, Line 0 Unable to load the compiled DLL
  for database ID 9.

I understand the architecture of in-memory tables is such that SQL Server generates a DLL out of the in memory object which it then loads into memory.  My guess is that operation is the source of this error - however, either my bing-fu skills are failing me, or there is really nothing in the wild that might lead me to resolution for this problem.

My gut is that it's some type of group policy or security issue as this is a pre-production stage environment with anti-virus, parity, the works - just like production has - but need to solve as this is meant for production some day.  The only GPO mentioned in documentation is 'Instant File Initialization' for performance reasons.

Anyone deal with this error before or understand the internals of in-memory objects enough to point me in the right direction?  Exact same setup, database, statement works on my own local machine.