texts:
I have a table called Catalog. It has multiple columns, for example CreatedByID and ModifiedByID that contain user IDs. I would like to get count of how many documents a user has created and edited.

I've spent hours looking for alternatives for this but couldn't find anything that works. This works and I get correct results but is there a simpler way to do this?

SELECT l.UserName
     , US.Used
     , US.Created
     , US.MostRecent
     , US.Edited
    FROM [Users] US
    JOIN    (SELECT UserName
                  , COUNT(UserName) AS Used
                  , MAX([TimeStart]) AS MostRecent
             FROM [Log]
             WHERE ReportAction = 1
             GROUP BY UserName) AS l
        ON (Us.UserName = l.UserName)

    JOIN    (SELECT CreatedByID
                  , COUNT(CreatedByID) AS Created
             FROM [Catalog]
             GROUP BY CreatedByID) AS Cat
        ON (Us.UserID = Cat.CreatedByID)

    JOIN    (SELECT ModifiedByID
                  , COUNT(ModifiedByID) AS Edited
             FROM [Catalog]
             GROUP BY ModifiedByID) AS Cat2
        ON (Us.UserID = Cat2.ModifiedByID)

I would like to get both COUNT(CreatedByID) and COUNT(ModifiedByID) in a single select. For example I can get how many documents user has created with this:

SELECT CreatedByID, COUNT(CreatedByID) AS Created
FROM [Catalog]
GROUP BY CreatedByID

And how many documents user has edited with this:

SELECT ModifiedByID, COUNT(ModifiedByID) AS Modified
FROM [Catalog]
GROUP BY ModifiedByID

But how to do those on a single select? I've tried something like this, but the results are not correct any more for ModifiedByID.

 SELECT CreatedByID, COUNT(CreatedByID) AS Created, COUNT(ModifiedByID) AS Modified
FROM [Catalog]
GROUP BY CreatedByID


texts: MySQL compare values of two large tables

texts:
On MySQL 5.7 I want to get the records from table 1 that are not present in table 2.

I'm using this simple LEFT JOIN query that is very slow (more than 2 minutes), I don't know why.

SELECT * FROM t1 LEFT JOIN t2 ON t1.name = t2.name WHERE t2.name is null

When I do an explain of the query I get this result

id   select_type     table     partitions    type     possible_keys  key          key_len      ref     rows     filtered     Extra
1    SIMPLE          t1        NULL          index    NULL           NAME_UNIQUE  49           NULL    66387    100.00       Using index
1    SIMPLE          t2        NULL          index    NULL           NAEM_UNIQUE  37           NULL    2275410  10.00        Using where; Using index; Using join buffer (Block Nested Loop)

I have a unique index on both columns "name".
Here are the tables definitions:

CREATE TABLE `t1` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` char(12) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME_UNIQUE` (`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=50053 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `t2` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` char(12) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME_UNIQUE` (`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=2049985 DEFAULT CHARSET=utf8mb4;

I think it's something related to the use of "Using join buffer (Block nested loop)" but I tried to switch off with no effect:

SET SESSION optimizer_switch='block_nested_loop=off';

Any ideas to make this query perform much better?
Thanks!


texts: SSRS - Report using Stored Procedures, Cascading Parameters and Views

texts:
I am looking for a non-trivial Microsoft SQL Server Reporting Services example that shows the efficient use of the following for calculated columns such as:

Profit = Revenue - Expense
Margin = Profit / Revenue, etc.

Views
Stored Procedures
Cascading Parameters
User Selects values from dropdown list box to control the report output they want to see.

Example, User Selects Dept. Name from the Department Drop Down List Box and based on selected department the Employees working for that Department shows up in the Next Drop Down List box called Employee and based on the Employee Selection a user can run the report to see that Employee specific information in the Report.

Some of these information could be like "Total Compensation" and I want to see How and where the Total Compensation was calculated using Stored Procedures? How the Cascading Parameters pass those values down to the Report to see that Employee Specific data.

Any example based on AdventureWorks DB or any other would be useful since I want to recreate it to understand how SP and Cascading works together.


texts:
We recently setup a PostgreSQL Cluster with PGPool in front of it. This is our first go at such a thing. We setup our QA environment to use the new PGPool, and we cranked up the logging on the Postgresql servers so we could point pgBadger at it and see how things are going.

After minimal use, and not quite 24 hours of logging, pgBadger showed me this report this morning:

That seems absurd to me. The first three top queries don't even appear to have anything to do with our application nor application user, yet they all accuse the application of instigating them.

We assume that PGPool is doing this in the background when the application instigates it, but even then ... there is no way our QA application instigated 369K times. Admittedly, the time spent on these are negligible, all of 24 seconds over the course of a day.

However, we would like to grasp:

Why are we seeing this amount of traffic from pgPool?
Can we expect this to scale? Currently we have a single QA DB here with very light usage. What do we expect when we get 200 production databases running in this cluster?
Is there an easy way to prevent posgtresql from logging these types of queries from pgpool in order for us to more easily parse queries that are truly instigated by our application?
Is there another question we should ask that we haven't?


texts: Data integrity in one-to-many distributed architecture

texts:
We are a retail shop that has a central data repository which collects data continuously from geographically distributed 1500 odd servers (each store has one SQL Server instance). Like a one to many architecture.

Our auditing team wants to know how we are ensuring data integrity between any given store and the central repository.
Although row-count comparison seems like a plausible option; implementing it is a herculean task: 1500+ stores. 200+ replicated tables. Continuous incoming data, etc.

Even if we implement row-count comparison, just because two tables have the same number of rows, doesn’t mean that the data in them should match.

Appreciate any suggestions and help. Thank you so much.


texts: exec sp_getapplock - Different behaviour using go or without go

texts:
we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH command).

Assuming having two sessions this is the first that start

use SKY_RGT

begin transaction

    declare @ResCode int
    exec @ResCode = sp_getapplock
        @Resource = 'SEMAFORO',
        @LockOwner  = 'Session',
        @LockMode = 'Exclusive'

    alter table TABLE_1 switch partition 501
             to TABLE_2 partition 501

here is the situation that we have after the execution (result are coming from what we see querying sys.dm_tran_locks)

| resource | description | request_status | request_mode | session |
--------------------------------------------------------------------
|     -    |  SEMAFORO   |     GRANT      |       X      |     1   |
|  TABLE_1 |             |     GRANT      |     Sch-M    |     1   |
|  TABLE_2 |             |     GRANT      |     Sch-M    |     1   |

then Session 2 start using this command

use SKY_RGT

begin transaction

    declare @ResCode int
    exec @ResCode = sp_getapplock
        @Resource = 'SEMAFORO',
        @LockOwner  = 'Session',
        @LockMode = 'Exclusive'

    insert into TABLE_2
    select      TIT.*
    from        TABLE_3 TIT
    where       TIT.SCE_COD = 501

and here is the lock situation after this

| resource | description | request_status | request_mode | session |
--------------------------------------------------------------------
|     -    |  SEMAFORO   |     GRANT      |       X      |     1   |
|  TABLE_1 |             |     GRANT      |     Sch-M    |     1   |
|  TABLE_2 |             |     GRANT      |     Sch-M    |     1   |
|  TABLE_2 |             |     WAIT       |     Sch-S    |     2   |
|  TABLE_3 |             |     GRANT      |     Sch-S    |     2   |

as you can see it seems to ignore the application lock.

Different situation if we use the "GO" command after the lock.
Here is Session 1 using the GO (but in session 1 using or not the go is invariant because the application lock is acquired anyway)

use SKY_RGT

begin transaction

    declare @ResCode int
    exec @ResCode = sp_getapplock
        @Resource = 'SEMAFORO',
        @LockOwner  = 'Session',
        @LockMode = 'Exclusive'
    go

    alter table TABLE_1 switch partition 501
             to TABLE_2 partition 501

and here the lock situation after this

| resource | description | request_status | request_mode | session |
--------------------------------------------------------------------
|     -    |  SEMAFORO   |     GRANT      |       X      |     1   |
|  TABLE_1 |             |     GRANT      |     Sch-M    |     1   |
|  TABLE_2 |             |     GRANT      |     Sch-M    |     1   |

Then it comes session 2 using GO command

use SKY_RGT

begin transaction

    declare @ResCode int
    exec @ResCode = sp_getapplock
        @Resource = 'SEMAFORO',
        @LockOwner  = 'Session',
        @LockMode = 'Exclusive'
    go

    insert into TABLE_2
    select      TIT.*
    from        TABLE_3 TIT
    where       TIT.SCE_COD = 501

and here the lock situation after this

| resource | description | request_status | request_mode | session |
--------------------------------------------------------------------
|     -    |  SEMAFORO   |     GRANT      |       X      |     1   |
|     -    |  SEMAFORO   |     WAIT       |       X      |     2   |
|  TABLE_1 |             |     GRANT      |     Sch-M    |     1   |
|  TABLE_2 |             |     GRANT      |     Sch-M    |     1   |

the last one is the situation that we expected but why is working only using the go?
The problem is that we have to transfer this kind of pattern inside stored procedures and inside SP is not possible to use the GO.

Any advice on this?

Thanks in advance.
Leonardo


texts: UPDATE Blocking SELECT Of Unrelated Rows

texts:
I have TableA with Col1 as the primary key. I am running the following transaction without committing it (for test purposes).

BEGIN TRANSACTION
UPDATE TableA
SET Col3 = 0
WHERE Col2 = 'AAA'

In the meanwhile, I run the following query and see that it waits on the first transaction to complete.

SELECT *
FROM TableA
WHERE Col2 = 'BBB'

But the following query returns the results immediately:

SELECT *
FROM TableA
WHERE Col1 = '1'

So I thought that the second query might need to read rows that have exclusive locks put by the first transaction in order to select rows with Col2 = 'BBB'. That's why then I tried to index Col2 so that a table seek will not be necessary but that did not work either. Second query still waits on the first transaction.

What should be done to prevent SELECT from blocking (except the use of NOLOCK).

P.S: Transaction isolation level is "Read Committed".


texts: Running a query in Mongo Shell vs PyMongo (in Flask) results in extreme slowdown

texts:
When prototyping a query I've been using just the Mongo Shell to make sure my aggregation query is working fine:

# Default query
    query = [{
        # Supposedly forces MongoDB to initially use this index.
        '$sort': {
            '_id': 1
        }
    }, {
        '$unwind': '$things.titles',
    }, {
        '$group': {
            '_id': '$things.titles.name',
            'count': {'$sum': 1},
        }
    }, {
        '$sort': {'count': -1}
    }]

Being run on this collection which the top of .stats() look like this:

...
"count": 4065493,
"size": 31275790798,
"avgObjSize": 7692,
"storageSize": 11493429248,
...

When I run this query in Mongo Shell, it finishes within five minutes tops. But when I ran it as part of the request handler in a Flask route via PyMongo it took all night to complete.

How can I either:

Speed this up considerably
Determine what's causing the slowdown?

This is also running on a AWS instance with 4GB of RAM which MongoDB takes up a maximum of 42% I've noticed.

EDIT:

For some clarification, when I run this script:

import time
import subprocess

start_time = time.time()
subprocess.call(["mongo", "localhost:27017/ars-xbox", "/Users/simius/Downloads/test.js"])
print("--- %s seconds ---" % (time.time() - start_time))

I get a run of about 105 seconds which is an acceptable length.

However, when I run the exact same query as part of a Flask route, it runs for up to 20 minutes or more.


texts: Equivalent to json_strip_nulls for Postgres 9.4

texts:
The json_strip_nulls function was introduced in 9.5. What could give an equivalent result for 9.4?

I have a json column that is essentially just a text column, but since the contents are JSON, we made it a json column for semantic purposes. Now there are a lot of keys mapped to null, and it's wasting space and causing clutter on the screen.

Casting to jsonb and back doesn't remove the nulls, and 9.4 doesn't give a lot of tools to work with json(b).


texts: Notify when database is accessed in any manner

texts:
I'm using SQL Server 2012. Is there a way to receive a notification (email/log etc..) if a database or table in a database is touched in anyway (e.g. CRUD via Stored Procs or tables opened via Management Studio)?

I've come across C2 audio tracing while trying to find a solution but that seems server wide, whereas I need it for one particular database. Is this even possible?

Thanks


texts:
I have a Database that creates day tables and some of the tables don't have the same number of columns and in some cases the columns aren't the same.  What I'm needing to do is consolidate all the tables into a single table.  I'm not quite sure how to do this, but I'm assuming I need to make sure all the tables have the same columns and then add all the data from the multiple tables to the single table should be fairly easy.

so my first step, find out which tables are different and which ones are the same.

what I need is to find out if a table has the same columns as another.  not the data, but rather like the column names and possibly the data type for that column.

any help would be greatly appreciated.  Thank you.  I'm manually going through these and checking the content, but would definitely like something that would be much quicker.

UPDATE

the table's have a date related naming scheme.  here's one for an example: repo_2012_08_04


texts: Is it possible that records are changed while a read query is still executing?

texts:
Suppose a read (select) query R is reading some 100 records from a table, 50 records are read so far, could in the meanwhile a write query W change some of the unread records?

We didn't take any locks on the table (or to be read records).

I think it is obvious that reading single records (reading every record) is an atomic operation, but i am not sure about reading several (possibly millions) records. Is it an atomic operation? Do we need to take a read lock when reading multiple records (in one query) if it is possible that a write query be run in the meanwhile and we want a consistent snapshot of all of the records at the time the read query starts to be executed?

I am using MySQL.


texts: MySQL Workbench Visual Explain not working with MariaDB 10.3.9

texts: I recently installed MySQL Workbench 8.0.12 and MariaDB 10.3.9. I created my database and want to start fine tuning indexes and views. I can run a query with the EXPLAIN button and get the results in the tabular format (Tabular Explain). When I try to select "Visual Explain" from the dropdown menu, it switches back immediately to Tabular Explain. I tried almost everything and cannot switch to Visual Explain. Any ideas? Thanks!


texts: Finding the default isolation level in SQL Server 2017 and Azure SQL DB

texts:
I'm reading a book related to transaction and concurrency. In one paragraph it is mentioned that:

In on-premises SQL Server instance the default isolation level is Read-Committed based on locking

And the next sentence is:

The default in SQL Database is Read - committed snapshot based on row-versioning

My question is: what is the difference between "On-premises SQL Server instance" and "SQL Database" in these two sentences?

What is the default isolation level and how can I find it? Is there any special query to find out the default isolation level?


texts:
I have 2 databases on server A ("srvA") that I use to build a data cube on server B ("srvB").  It's taken me an embarrassingly long time to discover that populating my cubes fails because the batch file opens my script on the first server and it is not directed toward the server B for the second half of the scripts.  I have:

DECLARE @theDay dateTime
Set     @theDay = GetDate()

EXEC    [db1].[dbo].[sp_deltas] @theDay
EXEC    [db2].[dbo].[sp_deltas] @theDay

GO

--doesn't work
--Use [srvB].[cubeDB]
--Go

--works if I open as a new query in server B
DECLARE @theDay dateTime
Set     @theDay = GetDate()

EXEC    [dbo].[sp_CUBEpop]
        @DBname = 'db1',
        @startDate = @theDay,
        @endDate = @theDay

EXEC    [dbo].[sp_CUBEpop]
        @DBname = 'db2',
        @startDate = @theDay,
        @endDate = @theDay
GO

The sp_deltas stored procedure runs just fine, but the "Use [srvB].[cubeDB]" statement doesn't work, and I suspect that having a linked server names "srvB" may not be helping matters.  The login I am using can run the stored procedures just fine on srvB as long as I navigate there manually and open a new query. How can I run them all in the same script so I am sure that the actions happen sequentially?


texts:
I have an app to make appointments with doctors. Doctors have schedules (working hours).

For example, let's say doctor A usually works 10:00 - 14:00 on weekdays. I could save this info in each doctor's table; however, there are holidays, sick days and so on- maybe even short days 11:00 - 13:00. How to represent them in the PostgreSQL database?

My current approach is to create a schedules table and store the usual working hours. For doctor A on weekdays from 10:00 - 14:00, and for other days create separate tables in which we specify when the doctor cannot work.


texts: Data Warehouse Kimball Customer and Customer Address Dimensions

texts:
I have a Customer Table and Multiple Customer Addresses. A customer can have multiple addresses: home, work, vacation,etc.

In Kimball data warehouse, would I utilize a bridge table to go between Customer and Customer Address information?

(a)
So [DimCustomer] to [DimCustomerAddressBridge] to [DimAddress] Table?
Would this be the proper technique?

(b)
Or would I just have a Fact Table, like CustomerTransactionPayment Table, that connects the two Dimensions [DimCustomer] and [DimAddress]?

So one strategy is the connected Dims using bridge, and another is disconnected Dims connected by a Fact transaction table?


texts: How much free space do I need for mongoDB? and how to reclaim lost space?

texts:
I ran out of disk space on my mongo database. I saw it and dropped some collections (120 out of 1700 i had). And ran db.compact(...) thinking it would release free space. well, that was a stupid idea.

Anyway, the database is around 800GB, and I dont have any space (~5GB) in the current disk. I symlinked a folder from another disk that has around 35GB and tried db.repairDatabase() (and mongod --repair --repairpath ...), but that failed too saying not enough space. What else can I do? short of getting another 1TB HDD?

Why is even 35GB not enough? Since I dropped the collections, I know I should get another ~50GB free after the repair. Is that going to be enough?

The partition I use for data would have around ~800GB for mongo. How much free space should I ideally have?

Mongo says I need working set+2GB free space. But no queries are running and mongod stopped. So I'm not sure why 35GB is not enough.


texts:
Getting the error below while trying to create a catalog in sql server 2014 integration services.
Any idea what I missed in installation or anywhere else?

  The catalog backup file 'C:\Program Files\Microsoft SQL
  Server\120\DTS\Binn\SSISDBBackup.bak' could not be accessed. Make sure
  the database file exist, and the SQL Server Service account is able to
  access it(Microsoft.SqlServer.IntegrationServices.Common.ObjectModel)


texts: Installing SQL Server 2012 failed setup administrator

texts:
Can anyone tell me why this keeps failing?

I am logged in as "Administrator" on Windows Server 2008 r2
I added Administrator to the "Backup ... " Group
And I checked to make sure everything is as it should be via these instructions http://support.microsoft.com/kb/2000257/en-us

both windows and sql are 64bit..
I tried creating another administrator user and tried running the setup as administrator..
I searched online for hours trying to find an answer..
I reinstalled windows server at lest 3-4 times..
Right now I am downloading sql server 2012 32bit and 64bit again.. I will try from a new download..


texts: create more logs in Mysql, pros and cons?

texts:
I just install XAMP in my mac and I'm trying to learn by my own, with books, tutorials and you guys.  I'm working in the backup part and I saw I have only a LOG "mysql-bin.000001' :

What happen when this logs is full??.
Will Mysql create another one by it self??.
Is it possible to have 3 logs and they will delete by itself when the last one is full, like Oracle db does ?.
What are the cons to have more than one log (beside more space)???.
I guess that the Engine doesn't matter, right?


texts: DB2 ROLLFORWARD ERROR when doing "TO A POINT IN TIME" option

texts:
I do restore command of backup image which was made 03.03.2015 at 19:20:

db2 restore database maxdb71 from "/data/archiveBackup" taken at 20150303192002 without prompting;

Then I try to do the rollforward to a point in time (03.03.2015 at 21:05) so this is after the backup image:

db2 rollforward db maxdb71 to 2015-03-03-21.05.00.000000 using local time and complete;

But I receive error:

SQL4970N  Roll-forward recovery on database "MAXDB71" cannot reach the
specified stop point (end-of-log or point-in-time) on database partition(s)
"0". Roll-forward recovery processing has halted on log file "S0000189.LOG".

So like it cannot find log  S0000189.LOG.
But this log EXISTS in the archive log location (C0000004 currently subfolder is used for archive logs) /data/archive/ctginst1/MAXDB71/NODE0000/C0000004>:

these are the timestamps for some of archive log files:

ctginst1 db2iadm1 15765504 2015-03-03 19:22 S0000187.LOG
ctginst1 db2iadm1 16785408 2015-03-03 21:52 S0000188.LOG
ctginst1 db2iadm1 16785408 2015-03-04 00:15 S0000189.LOG
ctginst1 db2iadm1 16785408 2015-03-04 02:32 S0000190.LOG

Basically log file S0000188.LOG should be the one which contains transactions from 21.05, right?

So why I cannot do the rollforward of point in time when I have necessary logs?
I am really confused with this rollforward option.

When I do db2 rollforward database maxdb71 to end of logs and complete;
it is successful and I get information:

 Log files processed                    = S0000187.LOG - S0000188.LOG

So to end of logs works but why I cannot perform until some specific point in time?


texts: postgres backup / restore: restored database much smaller?

texts:
I am worried that my restored database is very different from the original:

#check size of postgres database
postgres@db1:/tmp$ psql -c "select pg_size_pretty(pg_database_size('test_db'));"
 pg_size_pretty
----------------
 2105 MB
(1 row)

#backup database
postgres@db1:/tmp$ pg_dump -Fc test_db > test_db_Fc.dump

#rename postgres database (i.e. park it nearby)
postgres@db1:/tmp$ psql -c "alter database test_db rename to test_db_20130322;"
ALTER DATABASE
-------
(1 row)

#restore test_db
postgres@db1:/tmp$ pg_restore -Fc -C -d postgres test_db_Fc.dump

#check size of restored postgres database
postgres@db1:/tmp$ psql -c "select pg_size_pretty(pg_database_size('test_db'));"
 pg_size_pretty
----------------
 257 MB
(1 row)

The original db is many times larger than the restored one. What is going on here?
As far as I can tell, the website that test_db serves is still working perfectly well, after a restore, but I need to know what's up before i use a backup in live context.

FYI if I run vacuumdb on each database there seems to be no change in database size.

[Addendum, added later]
In the tradition of RTFM I have gone hunting in the manual for PostgreSQL.
Here are some more pieces to the puzzle

#is the autovacuum switched on?
postgres@db1:/tmp$ psql -c "SHOW autovacuum;"
 autovacuum
------------
 on
(1 row)

#The "track counts" parameter is needed by autovacuum which uses statistics from database activity to know where to do its job
postgres@db1:/tmp$ psql -c "SHOW track_counts;"
 track_counts
--------------
 on
(1 row)

#is there an autovacuum daemon resident in memory?
postgres@db1:/tmp$ ps  -ef | grep 'autovacuum'
postgres  1261  1021  0 Jan23 ?        00:08:27 postgres: autovacuum launcher process
postgres 18347 18149  0 00:33 pts/0    00:00:00 grep autovacuum

#what's been happening on the live server?
postgres@LIVEdb1:/tmp$ psql -c "SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables;"
#result is list of 65 tables (out about 300), all empty (no dates at all)

Gratefully following up on @craig-ringer's advice to look into VACUUM FULL I turned to PostgreSQL documentation, (paraphrased) "... In the worst case where your disk is nearly full, VACUUM FULL may be the only workable alternative. ... (but) the usual goal of routine vacuuming is to avoid needing VACUUM FULL. The autovacuum daemon attempts to do standard VACUUMs often enough to maintain steady-state usage of disk space ..."

I will follow @chris-travers advice and map out the number of rows in tables from each version of the database. I think in my case it'd be fair to say that VACUUM FULL will relieve the pressure on disk space, and it'll make original_vs_restored look better but there's still this red flag of excessive bloat. I think autovacuum isn't doing anything and that's a worry! Thanks for the guidance so far, it's fascinating.


texts: Which is better: Selecting across linked servers vs migrating the database

texts:
I have a customer that needs to query a linked server to produce a report. They will be joining a linked server table with a local table.

Is it better to just let them do the select across the linked server OR migrate (backup/restore) the database every day and have there query run against 2 local databases?

For discussion sake let's say the query returns 20K records.


texts:
I saw this interesting article : http://mikaelronstrom.blogspot.com/2012/02/105bn-qpm-using-mysql-cluster-72.html and it made me get interested in MySQL Cluster.

After little chat with hosting provider and i was told i may not be able to achieve to such performance on the Cloud Architecture which brings me to my question :

MySQL Cluster On Cloud  vs MySQL Cluster On Dedicated Cluster

Are there really any performance difference
Which is better and why ( using different factors eg.  Scalability ,  Cost Implication  etc)

Thanks


texts: Should I put the error handler inside the loop, or the loop inside the error handler?

texts:
When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run as quickly as possible.

I'm developing a maintenance job that deletes expired data from a table in chunks. It's designed to run continuously. It's implemented by two stored procedures: the first deletes from the table, and the second calls the other in a loop until either there is no expired data left to delete, or an error occurs.

The delete procedure looks like this:

CREATE PROCEDURE DeleteSomeExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT,
  @ActualDeleteCount INT OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON;

  DELETE TOP (@MaxDeleteCount)
  FROM BigTable
  WHERE DataCollectionDate < DATEADD(DAY, -@ExpiryAgeInDays, GETDATE());

  SET @ActualDeleteCount = @@ROWCOUNT;
END;

I want the looping procedure to reraise errors from the delete procedure, so I'm combining a TRY...CATCH statement and a WHILE BEGIN...END statement.

I can either put the WHILE BEGIN...END loop inside the TRY...CATCH statement:

CREATE PROCEDURE CycleDeleteExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ActualDeleteCount INT = 1;

  BEGIN TRY
    WHILE @ActualDeleteCount > 0
    BEGIN
      EXEC DeleteSomeExpiredData
        @ExpiryAgeInDays = @ExpiryAgeInDays,
        @MaxDeleteCount = @MaxDeleteCount,
        @ActualDeleteCount = @ActualDeleteCount OUTPUT;
    END;
  END TRY
  BEGIN CATCH
    EXEC RethrowError;
  END CATCH;
END;

Or I can put the TRY...CATCH statement inside the WHILE BEGIN...END statement:

CREATE PROCEDURE CycleDeleteExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ActualDeleteCount INT = 1;

  WHILE @ActualDeleteCount > 0
  BEGIN
    BEGIN TRY
      EXEC DeleteSomeExpiredData
        @ExpiryAgeInDays = @ExpiryAgeInDays,
        @MaxDeleteCount = @MaxDeleteCount,
        @ActualDeleteCount = @ActualDeleteCount OUTPUT;
    END TRY
    BEGIN CATCH
      EXEC RethrowError;
    END CATCH;
  END;
END;

These look functionally equivalent, and I find both easy to read. But there may be a semantic difference I'm not aware of. Can you tell me any?


texts: Eager spool for update and delete on partitioned table

texts:
When I update or delete on partition table it shows eager spool in execution plan.

Image for Update query execution plan showing eager spool:-

I can’t understand why it is doing eager spool only when I am updating or deleting on partition table, same query when I run on non partition table it does not do eager spool.


texts: How to update records of one table with the values of another one (PostgreSQL)

texts:
I have a table t1 with columns account_id and user_id, and another table users with columns id and account_id. I want to populate t1.account_id with the values of users.account_id.

For instance having

t1
user_id account_id
1       NULL
2       NULL

and

users
id account_id
1  10
2  11

I would like to populate t1.account_id with these values:

t1
user_id account_id
1       10
2       11

I've seen this answer for MySQL but can't have it working with PostgreSQL.


texts: Why doesn’t IMPLICIT_TRANSACTIONS increment @@TRANCOUNT for my IF statement?

texts:
I was playing around with SET IMPLICIT_TRANSACTIONS ON. I found a situation where I am convinced the database is automatically creating and committing a transaction without leaving it open. If I do IF EXISTS (SELECT * FROM dbo.t1) or any variation, I am certain that a transaction is happening in the background which I’d expect to stay open if I’m using implicit transactions.

However, after reading the docs, it says something like:

  This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction.

and the list it shows does not include IF. However, then I’d expect all statements not found in the documentation’s list to not leave @@TRANCOUNT incremented. So, I looked for another statement not included in that list. I found DECLARE. However, I found that if I perform a subquery with DECLARE, I get the behavior I’d expect from SET IMPLICIT_TRANSATIONS ON. That is, if my DECLARE has a subquery, it increments @@TRANCOUNT if it is zero. So it appears that the documentation is inconsistent with itself

SET IMPLICIT_TRANSACTIONS ON
GO
IF OBJECT_ID('dbo.t1') IS NOT NULL
BEGIN
    DROP TABLE dbo.t1
    COMMIT
END
GO
CREATE TABLE dbo.t1 (x INT);
COMMIT;
GO

IF CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END = 1 PRINT 'unreachable';
PRINT 'after1 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after1 0

DECLARE @x INT = 2;
PRINT 'after2 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after2 0

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;
PRINT 'after3 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after3 1
GO

WHILE @@TRANCOUNT > 0 ROLLBACK
GO

Why does DECLARE increment @@TRANCOUNT for an embedded SELECT but not IF? Is the SELECT embedded in the IF somehow not actually using a transaction?

I’m using SQL Server 13.0.4411.


texts: Converting and/or enforcing an Ascii Character Set in Informix database from 819 or ISO Latin 1

texts:
Overview: In Informix is it possible to enforce ASCII for a database or instance.

Details: I am trying to enforce ASCII in an instance of informix for a while. This is to help have a smooth transition between char sets: ISO Latin-1 (819) and UTF8. I would like this to be done by cleansing my current data of non ASCII characters and then exporting the data from the databases. These steps have already been done.

Then setting the character set to enforce ASCII. However I cannot find a character set that I can use that my instance allows me to change the db_locale too. My current db_locale is default aka ISO Latin 1 or 819. After I figure out the character set. I believe that I can figure out the rest. It should be just importing the data to a new database with the db_locale set to an ASCII char set.

Is this possible? I am also open to other ways of setting or enforcing ASCII at the database level.


texts: Are certain environment variables required for mysql login-path?

texts:
I created a bash script that backs up my database, db_backup.sh

It basically just runs these commands:

DB=/home/linuxuser/backups/backupfilename.sql.gz
mysqldump --login-path=local db_name | gzip > "$DB"

When I run it from the command line as linuxuser or as root, it works.

But when I add it to cron.d, I get a mysql error and the sql.gz file is empty.

mysqldump: Got error: 1045: Access denied for user 'linuxuser'@'localhost' (using password: NO) when trying to connect

(I get the same type of error when running the cron as root, too)

How can I fix this? Could it be a problem with environment variables?

I tried changing:

HOME=/

to

HOME=/linuxuser/

... but that did not help.


texts:
Forgive me if I misuse any words. I'm a c++ programmer with some basic DB knowledge.

I'm developing an analytics engine for simulation data for vehicle engines. OLAP seems like a good fit but I haven't done an exhaustive search for different methodologies.

What I'm stuck on is how to structure my tables/cubes. Most examples I've seen have 1 or 2 measures and my simulations can generate 100s or even 1,000s of different measures for each test. The measures have some hierarchy to them, usually encoded in the name of the measure. For example, there may be TEMP.sensor1, TEMP.sensor2, TEMP.sensor3, TORQUE.sensor1, TORQUE.sensor2, etc. This dimension is used to slice.

Another dimension is different tests being run. There may be several different highway tests which act as different inputs to the simulation. The tests are either analyzed independently or aggregated into a single set of measures.

Does each test belong in a row of a fact table that has 1 measure for each of my statistics? Are there other approaches? Is this even a suitable problem for a database and OLAP?


texts: Disk Separation DB installation and data files

texts:
I am wondering whether it is a good idea to separate the database installation media from the actual data files.

In our current environment we have:

C: - Windows Server
D: - MS SQL Installation & DB Data Files
E: - Backups
L: - Log files

Would it be a good idea to separate out our D:

Drive 1: MS SQL installation
Drive 2: DB Data Files

There are similar questions already posted, but nothing specifically relating to the separation of DB software and DB data files. (similar questions ask whether it is a good idea to separate OS and DB).


texts: Optimise Large Record Deletion Procedure

texts:
I'm creating a database that mirrors our production database, but is lighter and anonymised - for local development purposes.

Ensuring we've enough data to operate on as an engineering team, I'm removing all customers that have their updated_at date set to over a year ago. Simple process is to keep new users, but bin old or inactive users.

For this, I've created a stored procedure.

DELIMITER //
CREATE PROCEDURE delete_old_customers()
BEGIN
    SET @increment = 0;
    customer_loop: LOOP

        DELETE FROM customers
        WHERE id BETWEEN @increment AND @increment+999
        AND updated_at < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR);

        IF @increment > (SELECT MAX(id) FROM customers) THEN
            LEAVE customer_loop;
        END IF;

        SET @increment = @increment + 1000;

    END LOOP customer_loop;
END //
DELIMITER ;

CALL delete_old_customers();

DROP PROCEDURE delete_old_customers;

So this procedure batches the removal into groups of 1000, and runs until there are no more customers to process.

I run the procedure like this:

mysql "$MYSQLOPTS" devdb < ./queries/customer.sql

Where the $MYSQLOPTS refers to a my.cnf file with the following options:

[mysqld]
innodb_buffer_pool_size = 6G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
query_cache_size = 0

The problem is that due to the FKs and references this table has, this process can take up to 3 hours to remove ~800k users; and of course, as time goes on, this is only going to grow.

This is running on a Quad-core, 8GB RAM, Digital Ocean Droplet; so I've only limited means to work within.

So given this, I'd love the opportunity to begin optimising this procedure to improve its speed, but I'm unsure of where to start. I'm also open to alternative methods to achieve the same aim.


texts: Using wildcards in NEAR in a Full Text Search

texts:
Let's say I have the following text somewhere in a document stored in an FT indexed table in SQL Server

minsitry of education and science

I want the query to ensure the words are NEAR, but the query won't contain the exact words (instead I would like to use wildcars) - something like

CONTAINS(FileContent, 'NEAR(minist*, educ*, 10, TRUE)')

If I try this query, no results will be returned as NEAR seems to work only with exact words.

My question - is there a way to complete a search like this using SQL Server queries?


texts: Creating UUID extension as the non super privileged user

texts:
I need to load the uuid extension on my database, which is being created in runtime. The user that created the db is not superuser, she only has the "CREATEDB" permissions.

Is there a way to do this?
Thanks.


texts: Autodetect ‘random_page_cost’ vs ‘seq_page_cost’

texts:
I read this article about PostgreSQL performance on SSD:

https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0

These two configurations seem to be important random_page_cost vs seq_page_cost

Since both parameters need to match the particular hardware I wonder if it is possible to automatically detect the matching values?

Update

I have these steps on my mind:

Script creates some dummy tables
Scripts inserts data to the tables
Script does some queries
Script shows matching values for random_page_cost and seq_page_cost
Human or an automated system takes these values and updates the config. This step is not part of the question.


texts: "Cannot continue the execution because the session is in the kill state" when querying system tables

texts:
This is a snippet from some code I run on a 2012 (SP3-CU9) (KB4016762) - 11.0.6598.0 (X64) system that I just inherited and have not yet had a chance to fully patch.  AlwaysOn AG are not enabled, and there's plenty of space in the LDF.
declare @sqlstr nvarchar(2048);
declare @dbname     sysname = 'GSMPRD'
declare @qdbname     sysname;
set @qdbname = QUOTENAME(@dbname);
set @sqlstr =
        'use ' + @qdbname + '; ' +
        'select ''' + @dbname + ''', '+
                'schema_name(tbl_schid) as sch_name,
                tbl_name,
                ndx_name,
                cast(ips.avg_fragmentation_in_percent as decimal(5,2)) as frag_pct
        FROM (select i.object_id,
                i.index_id,
                i.name as ndx_name,
                t.name as tbl_name,
                t.schema_id as tbl_schid
                from ' + @qdbname + '.sys.tables as t, ' +
                    @qdbname + '.sys.indexes as i
                where i.object_id = t.object_id
                ) as ndx,
                sys.dm_db_index_physical_stats(' + cast(@dbid as char(4)) + ', NULL, NULL, NULL, NULL) as ips
        WHERE ndx.object_id = ips.object_id
            AND ndx.index_id = ips.index_id
            and ips.index_type_desc <> ''HEAP''
            and ips.avg_fragmentation_in_percent >= 5.0
            and ips.fragment_count > 25
        ORDER BY tbl_name, ndx_name;'
exec(@sqlstr)

Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Sometimes it runs, and sometimes it doesn't.  Also, I frequently see A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) when rerunning SQL that had previously run.
sqlsvr.exe is using less than it's max allowed RAM, and the Task Manager says that it's barely using half of available RAM.
What can be causing this?


texts: Is there a common approach to encrypting data at the database level?  The application I am working on will be storing sensitive data that only users of the system themselves will be able to view.


texts: Does restoring a database overwrite transaction logs?

texts:
I accidentally restored an sql server database from a backup file and lost all my previous data. I did not have any backup but I read that some third party software (like apexSQL) can retrieve some data by reading the transaction logs. However, if restoring the database has also overwritten the transaction log file, this operation is pointless.

So my question is: does restoring a database from a backup file also overwrite the transaction logs or just append to it.


texts:
My database has 50+ schemas. Each schema has around 100 tables and some views.

I want a query that tells me the schema name, table name, and total number of rows in each table. The below query:

select table_schema, table_name, table_type, count(*)
from information_schema.tables
group by table_schema, table_name, table_type

...gives the total number of tables with each name, rather than giving the row count. I am using pgAdmin 3 version 1.10


texts: Installing PostGIS in a different schema causes topology extension to not find geometry type

texts:
I have a sql dump of the schema of an existing database that is working correctly. When I try to load this into a new, empty database psql new_db < dump.sql, I get the following error:

ERROR:  42704: type "geometry" does not exist
LOCATION:  LookupTypeNameOid, parse_type.c:228
Time: 51.680 ms

The dump.sql file is quoted below down to the failing line:

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.1
-- Dumped by pg_dump version 10.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: bridj; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA bridj;

--
-- Name: postgis; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA postgis;

--
-- Name: topology; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA topology;

--
-- Name: SCHEMA topology; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA topology IS 'PostGIS Topology schema';

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--
-- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA postgis;

--
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';

--
-- Name: postgis_topology; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;

It appears that the postgis_topology extension cannot find the geometry type because it's in the postgis schema rather than the public schema. I have tried running

set show_path to "$user", public, postgis;

but that has no effect; creating the extension fails with the same error.

More background:

The reason for the schema move is that we are using database schemas to isolate data for multiple tenants. Thus, public will not always be in the search path. By moving PostGIS to its own schema, we can keep that in the search path at all times

  Postgres 10.1

  POSTGIS="2.4.2 r16113" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.7" LIBJSON="0.12.1" LIBPROTOBUF="1.3.0" TOPOLOGY RASTER


texts: From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly?

texts:
I have an Always On Availability Group set up, and I want to make sure my users are using ApplicationIntent=ReadOnly in their connection strings.

From the SQL Server via DMVs (or Extended Events or whatever), can I tell if a user connected with ApplicationIntent=ReadOnly in their connection string?

Please don't answer with how to PREVENT connections - that's not what this question is about. I can't simply go stopping connections, because we have existing applications that are connecting without the right string, and I need to know which ones they are so I can work with the developers and users to get it fixed gradually over time.

Assume that users have multiple applications. For example, Bob connects with SQL Server Management Studio, and with Excel. He connects with SSMS when he needs to do updates, and Excel when he needs to do reads. I need to make sure he's using ApplicationIntent=ReadOnly when he connects with Excel. (That's not the exact scenario, but it's close enough to illustrate.)


texts: Query syntax is checked at java level or Database Level?

texts:
If i used normal JDBC Connection for accessing database of Mysql, The syntax of query is checked at java level or Mysql level.

Example

String Query="Select * fromEMP"

above wrong query syntax throw a SQLException.

so this exception is actually thrown by MySQL or java.


texts: Oracle Database Client 11g R2 Installation Types, which components?

texts:
I need to know which component is under each installation type!

Instant Client
Administrator
Runtime
Custom (No need as it shows all the components)

ALSO?
does it includes ODP.NET? is it the component

  Oracle Data Provider for NET

if not then I need to have to install (ODAC 112040 Xcopy) after!


texts:
I use user IDs a lot in tables, and I would like to find out when naming these columns, what's the best way to go about naming them. Should you be specific to what the user ID is, or should you keep using the same column name(s) to keep it consistent?

So for example:

Column names for subscriptions

user_subscriber_id = the user ID that made the subscription.

user_subscribed_to_id = the user ID that was subscribed to.

Column names for comments

sender_id = the user ID that made the comment.

reply_to_id = the user ID of the comment replied to.

Or should I always use user_id, and in the case where there needs to be 2 user IDs in a table (like above), use user_id and secondary_user_id, where the user_id should always be the user that does an action, and secondary_user_id should always be the user ID of the receiver, so:

Column names for subscriptions

user_id = the user ID that made the subscription.

secondary_user_id = the user ID that was subscribed to.

Column names for comments

user_id = the user ID that made the comment.

secondary_user_id = the user ID of the comment replied to.


texts: SQL Server Management Studio 17.1 Mirroring bug

texts:
I noticed there is a bug that when people asked about it, they were sent in the wrong direction (see final remarks below).

When trying to use SQL Server 2016 with SSMS 17.1 to create a mirrored database, the following errors might be received:

  Database is not configured for database mirroring.
  Database x cannot be opened. It is in the middle of a restore.

This error was a real PITA that cost me 2 weeks of searching as I am new to this DBA stuff, otherwise I would have gone the scripting path.

I think this is a bug in SSMS 17.1 because it works in SSMS 16.5.3. It took me quite some time to figure the real issue was the Management Studio itself, unfortunately. The interface of version 17.1 is much better compared to version 16.5.3!

Wrong suggestions and misleading solutions

Mostly it was the database mirroring/hadr_endpoint TCP://servername:port setup, restoring with NORECOVERY being forgotten in many cases, but was not the real issue when backup and restore were done correctly.


texts:
I need the table to go from this:

 CalanderDateTime   PtID      EventDateTime    GapinMinutes
 2017-09-23 08:34   NULL          NULL              NULL
 2017-09-23 08:35   NULL          NULL              NULL
 2017-09-23 08:36   NULL          NULL              NULL
 2017-09-23 08:37    1      2017-09-23 08:37        1
 2017-09-23 08:38    1      2017-09-23 08:38        4
 2017-09-23 08:39  NULL          NULL             NULL
 2017-09-23 08:40  NULL          NULL             NULL
 2017-09-23 08:41  NULL          NULL             NULL
 2017-09-23 08:42  NULL          NULL             NULL
 2017-09-23 08:43    1      2017-09-23 08:43       3
 2017-09-23 08:44  NULL          NULL             NULL
 2017-09-23 08:45  NULL          NULL             NULL
 2017-09-23 08:46  NULL          NULL             NULL

to this:

CalanderDateTime   PtID      EventDateTime    GapinMinutes
 2017-09-23 08:34   NULL          NULL              NULL
 2017-09-23 08:35   NULL          NULL              NULL
 2017-09-23 08:36   NULL          NULL              NULL
 2017-09-23 08:37    1      2017-09-23 08:37        1
 2017-09-23 08:38    1      2017-09-23 08:38        4
 2017-09-23 08:43    1      2017-09-23 08:43        3
 2017-09-23 08:44  NULL          NULL             NULL
 2017-09-23 08:45  NULL          NULL             NULL
 2017-09-23 08:46  NULL          NULL             NULL

In this example i would want the next 4 rows removed from the row where 4 is shown in the 'GapinMinutes' column (but this can be any number > 3 and therefore whatever number of rows removed is based on the number shown in the 'GapinMinutes' column, so if the column has a 5, remove the next five rows, if its a 7 remove the next 7 rows, if its a 10, remove the next 10 rows but if its a a number <= 3, keep the next 1,2 or 3 rows...

Here's the script i have so far:

With VentHours (ptID, t, ET, VGM)  AS
(
    SELECT sig.PatientID, sig.Time,

        Case When sig.PatientID = LEAD(sig.PatientID) OVER (ORDER BY sig.PatientID, Sig.Time) AND DateDiff(MINUTE,sig.Time,LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) between 0 and 720 THEN DateDiff(MINUTE,sig.Time,LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) ELSE 0 END Vent_Gap_Mins

        FROM GCUH_Reporting.dbo.VIEW_GCUH_Vent_Signals sig
        inner join GCUH_Archive.dbo.Patients p ON sig.PatientID = p.PatientID
),

JoinTime as
(
    Select *
    from GCUH_Reporting.dbo.AUX_DateDimension ts
    left outer join VentHours vh ON convert(DateTime,ts.DateTime) = convert(DateTime,vh.t)
)

select jt.DateTime, jt.ptid, jt.et, jt.vgm

from JoinTime jt

--where  jt.ptID   is not null

order by jt.DateTime


texts: Why repmgr didn't failover automatically?

texts:
Installation

- name: Install repmgr repository
  get_url:
    url: https://dl.2ndquadrant.com/default/release/get/10/rpm
    dest: /var/lib/pgsql/repmgr

- name: Install repmgr rpm
  shell: bash /var/lib/pgsql/repmgr

- name: Install repmgr
  yum:
    name: repmgr10
    state: present

repmgr setting

node1 - primary

$ cat /etc/repmgr/10/repmgr.conf
node_id=1
node_name=node1
conninfo='host=node1 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/pgsql/10/data'
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
log_file='/var/log/repmgr/repmgr.log'
log_level=NOTICE
reconnect_attempts=4
reconnect_interval=5
monitoring_history=yes

node2 - standby

$ cat /etc/repmgr/10/repmgr.conf
node_id=2
node_name=node2
conninfo='host=node2 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/pgsql/10/data'
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
log_file='/var/log/repmgr/repmgr.log'
log_level=NOTICE
reconnect_attempts=4
reconnect_interval=5
monitoring_history=yes

Start their services

$ sudo systemctl start repmgr10

Status

$ repmgr cluster show
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+-------------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 3        | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | standby |   running | node1    | default  | 100      | 3        | host=node2 user=repmgr dbname=repmgr connect_timeout=2

test

Stop PostgreSQL's service on node1

$ sudo systemctl stop postgresql-10

It's status can be disconnected. But node2 can't be primary automatically.

After restart PostgreSQL's service on node1 again, the repmpr cluster status can be the origin again.


texts: disable warning from `drop table if exists`

texts:
When I DROP a table with the IF EXISTS clause (in PostgreSQL 11.x), it issues a warning as follows:

=> DROP TABLE IF EXISTS tt;

NOTICE:  table "tt" does not exist, skipping

Sometimes, I do expect that certain tables to not exist, and don't want to see many warnings like these in the output of a batch processing function/script.

Is there a way to disable this type of warning altogether?


texts: SQL Server Linux Disable Remote Connections

texts:
What is the best way to disable remote connections on a SQL Server Install on Linux (CentOS 7)? I.E. force SSH Tunneling to connect over localhost.

I typically block ports explicitly with IPTables/Firewalld rules, and have 1443 closed off to the outside.

However with MySQL I also tend to close off users to local access only, i.e. 'testUser'@'localhost' and not 'testUser'@'%'. Also, the default if you run mysql-secure-installation is to turn of remote connections as root (SA in SQL Server). This ensures that if my network is breached, they still have to SSH Tunnel into the server.

How do I achieve this?

I was reading this doc and was not able to turn off remote connections when running:

EXEC sp_configure 'remote access', 0 ;
GO
RECONFIGURE ;
GO


texts: Delete and archive old data from several related tables

texts:
Our schema looks like this:

table tblTrip: stores start and end trip date and time
table tblTripData: keeps all the trip data
table tblTripEvent: stores events related to the particular trip.

Then we have two more tables, tblTripEmail and tblTripSms, which store the email and SMS that have been sent according to the events.

We want to keep just 3 months old trip data, we would like, to from time to time, to delete and archive the rest to be stored in offline database.

What is the best mechanism to store in an offline database? I am not sure how partitioning would be helpful because the tables are linked to each other here.

Below is the table desc for tblTripData

CREATE TABLE IF NOT EXISTS `tblTripData` (
  `tripDataID` int(11) NOT NULL AUTO_INCREMENT,
  `tripID` int(5) NOT NULL,
  `deviceSerialNumber` varchar(20) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `speed` float NOT NULL,
  `course` int(3) NOT NULL,
  `gpsdateTimer` datetime NOT NULL,
  `insertDateTime` datetime NOT NULL,
  `odoMeter` float NOT NULL DEFAULT '0',
  `driverID` int(5) NOT NULL,
  `eventAlertID` int(11) NOT NULL DEFAULT '0',
  `tripDataInsertDateTime` datetime NOT NULL
  PRIMARY KEY (`tripDataID`),
  KEY `dateTime` (`gpsdateTimer`),
  KEY `tripID` (`tripID`),
  KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


texts:
I saw another post where they showed you how to do this with a case statement and had you define your buckets in advance. how do I make it dynamic?

I know about the width_bucket and ntile functions. I am having trouble getting them to do what I want.

I created the following table to get counts of duplicate names

create table name_dupe as
select name,count(*) mycount
from mynametable
group by name
having count(*) > 1

I now want to find out how many duplicate names fit into certain counts. For example:
How many names have between 101 and 200 duplicates, 201 and 300. 301 and 400 and so on. I may want to change the bucket ranges.

How do I do this? I think its with width_bucket and not ntile, but I can't get it to work. This is what I tried.

select distinct mycount,dupes
from (
select mycount,width_bucket(mycount,200,300,30) dupes
from name_dupe
)
where dupes > 0
order by mycount

Sample output:

mycount dupe
------- ----------
    887         31
    909         31
    993         31


texts: Inline expansion of inline composite expression

texts:
I want something like this:

SELECT 1, (CASE WHEN TRUE THEN (2, 3) ELSE (4, 5) END).*;

...hoping to get 1 | 2 | 3. But in PostgreSQL 11 this query throws:

  ERROR:  record type has not been registered

and without * it obviously returns 1 | (2,3).

Is this possible?


texts: SSRS minimal permission to download reports

texts:
I received this request from a developer:

  When I try to download *.rdl report files from myssrs_server/Reports
  I’m getting this error:

  Would you be able to extend my permissions so I can download reports
  from all branches?

What is the minimal permission I should grant in order for him to get this done?

Any alternatives? (I was thinking about granting read only access to the reportServer database or something on those lines - but I don't want to make their lives too difficult )

I found this article very good regarding ssrs permissions:

SQL Server Reporting Services 2012 Permissions

Webpage were all the ssrs Predefined Roles are explained.


texts: Pagination, check number of selected records

texts:
I am working on pagination for a complex join query and unfortunately I am not sure what's the best way to go... Right now I am thinking about the following solution.

Imagine that SELECT id... is the complex join query. I copy all id's to the temporary cache.

CREATE TEMPORARY TABLE tmp SELECT id FROM comments WHERE user_id = 123;

Now I will check number of selected records.

SELECT COUNT(*) FROM tmp;

If a number of records is let's say greater than 1000 all records will be moved to a cache table and then selected by e.g 100's (completely all records from a cache are going to be selected ,so it is not as a classic pagination...).

INSERT INTO cache_table SELECT * FROM tmp;
DROP TEMPORARY TABLE tmp; #Temp is not needed anymore

however if number of records is smaller than 1000 then they don't have to be cached and can be immediately selected:

SELECT column_names FROM comments c JOIN tmp t ON c.id = t.id;
DROP TEMPORARY TABLE tmp; #Temp is not needed anymore

OK, now if there are some records in cache, they will be selected by 100 in this case. Records don't have to be selected all at once (10*100), a connection may close and client will ask data later. So we have got 1000 records in cache. To select a data I execute:

SELECT column_names FROM comments c JOIN cache_table ct ON c.id = ct.id LIMIT 100;
DELETE FROM cache_table LIMIT 100; #Records are selected so they can be deleted

This process is repeated until it return 0 or less than 100 records (If there is e.g 1001 rec).


texts: One address table for many types of addresses

texts:
I have an application where I have to store adresses of multiple types of location.

For example, my app requires addresses of people, hotels and event venues.

Should I create separate tables for different type of addresses having same fields? or Should I add an additional field telling what is the type of address?


texts: Remove first all null records from first part of time series until one value is not null and return the rest, in PostgreSQL

texts:
I have a table that has something like this:

dt              col1 col2
2000-01-01      null null
2000-01-02      null null
2000-01-05      null null
2000-01-10      null 100
2000-01-11      1    2
2000-01-12      null 3
2000-01-14      null null
2000-01-20      1    1
2000-01-21      null null

Note it has an unpredictable number of records and in some cases I want to query the table and trim off the records that are earliest in the time series that are all null that occur prior to the first record that has at least one value. In this example I want to remove records 2000-01-01 through 2000-01-05. Note that I want to leave all records whether all values are null or not after the first one is found that has a non-null value starting from oldest date forward.

So my desired result would be:

dt              col1 col2
2000-01-10      null 100
2000-01-11      1    2
2000-01-12      null 3
2000-01-14      null null
2000-01-20      1    1
2000-01-21      null null

This doesn't work because it removes all records that have all null values:

select
    *
from
    tbl
where
    col1 is not null
    or col2 is not null;

Is there a way to solve this without changing the table at all (doing it all in a query)?

My alternative is to iterate through the records in a program that this query is needed. I'm thinking SQL might be better.

I'm using PostgreSQL 9.6.


texts:
my Data
    TradeID Commodity   GeneratedFrom
    32839   CPO             NULL
    32840   Hard Stearin    NULL
    32841   PFAD            32839
    32842   RBDPO           32839
    32843   CPO             NULL
    32844   Olein           32842
    32845   Stearin         32842
    32846   RBDPO           NULL
    32847   Hard Stearin    32845
    32848   PMF             32845

when give the trade id then return patents and childs like when i give Tradeid 32845
in above data Genrated from is patrent Id and which tradeid has Grerated from they are childs.
i want below result
     TradeID
      32845
      32847
      32848
      32842
      32839


texts: is "create schema" the same way of saying "create database"?

texts:
I'm a beginner in DBMS, sorry if my question sounds dumb. I often see some textbooks says: create a schema . does it that mean "create a database"?

But isn't that a schema is also a skeleton of a table? don't we often describe a table's structure such as column name/column type using schema? so does schema a table concept or database concept?


texts: Is there a possible reason for having index on (a) and on (a,b) at the same time

texts:
Suppose I have a table with several column, among them - columns a and b.

Is there any use case for which having both multi-column index on (a,b) and a single-column index on (a) is beneficial?

AFAIU for simple equality check using first column of multi-column index is equally good as using the single-column index.


texts: PostgreSQL CPU 100% after configure Wal log shipping hot standby

texts:
I have configured Hight Avaliabily WAL Log Shipping Hot Standby (SSL) with one node. But after I configure, both hosts (primary and secondary) take 100% CPU usage.
The most strange is because I have only one database called "TESTE" with one table called "EXAMPLE" to check if replication was OK. The replication worked perfectly but I don't understand why my CPU takes 100% usage because I don't have any application connect on my cluster.
I ran HTOP command and get this results:
Image 1 - Primary Server HTOP Info

Image 2 - Secondary Server HTOP info

S.O. version:
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 10 (buster)
Release:        10
Codename:       buster

PostgreSQL version:
postgres (PostgreSQL) 12.3 (Debian 12.3-1.pgdg100+1)

Primary server postgres.conf file:
max_connections = 100
SSL = on
shared_buffers = 2GB
work_mem = 10485kb
maintenance_work_mem = 512MB
dynamic_shared_memory_type = posix
effective_io_concurrency = 200
max_worker_processes = 4
max_parallel_maintenance_workers = 2
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
wal_level = replica
wal_buffers = 16MB
max_wal_size = 200MB
min_wal_size = 80MB
checkpoint_completion_target = 0.7
archive_mode = on
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on
hot_standby_feedback = on
random_page_cost = 1.1
effective_cache_size = 6GB
default_statistics_target = 100

pg_stat_activity info:

pg_stat_archiver info:

pg_stat_replication info:

If I stop postgres service in both servers the CPU still has 100% usage.
Anyone can help me?


texts: How to create a cascading query finding parent/child rows from table

texts:
I hoping someone can help with what seems like a simple query, but seems to be harder than I anticipated.
I'm looking to find the parent rows for the child row based on the corresponding ID.

TABLE1
------------------------------
ID  LEVEL   VALUE   PARENTID
------------------------------
1   COUNTRY UK      NULL
2   COUNTRY FRA     NULL
3   CITY    LONDON  1
4   CITY    PARIS   2
5   RIVER   SEINE   4
6   RIVER   THAMES  3

.

SELECT VALUE FROM TABLE1 WHERE VALUE = 'THAMES'
...ID = PARENTID
...ETC
;

So for example with the above table I can pass in 'THAMES' and it will find the parent and parent's parent and so on, until it gets to a NULL value. for example:

THAMES
LONDON
UK

Thanks in advance.


texts:
I need to fill in transactional log file in possibly shortest amount of time.
I have tried a few variants of a loop of inserts and deletes on the same record. I fired it on separate tables in several sessions at the same time. The result was not bad but it is still too slow - about 1 GB per minute. And my log file is 500 GB (Despite regular backups. It is a very busy system, and put in Availability Group).
Could you suggest a clever and effective way to fill in the file?
And in case you are wondering why I am doing such a strange thing. I need to temporarily create a secondary log file and switch the log writer header to it. Then I will be able to truncate and recreate the primary log file. Because of too small autogrowth, it  consists now of some 15,000 VLFs which I want to fix.


texts: SQL Server 2014 Express database size limitation exceeded, suddenly cannot be exceeded anymore?

texts:
I have a database in SQL Server 2012 Express.  I inserted 14 million rows to test index performance and everything was working fine.  The database's PRIMARY file group was at over 21 GB.  I had applied this index manually in the Management Studio designer, and even dropped and recreated it a few different times, trying different clustered indexes on different keys to see which one performed best for the queries I was using.  Once I settled on one, I had to drop the index and restore the original clustered index on the primary key, so I could create a migration with entity framework that would build the clustered key I wanted.  I manually inserted lines into the migration to drop and recreate the primary key as non-clustered so that the new clustered index could be created.

Suddenly, when trying to create the clustered index, which should be less than 4 GB, I get the error:

Msg 1101, Level 17, State 12, Line 6
Could not allocate a new page for database 'MyDatabaseName' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I'm not out of disk space with 702GB free on the drive, and the database file was already clearly over the 10 GB limit that I've been reading about.  I tried shrinking the database.  That got it down to about 7 GB, so I have no idea how or why it was up to 21 GB in the first place.  I cannot set the size greater than 10 GB now, as I get a licensing error.

What I'm trying to understand is how I was able to add all that data to the database and drop and recreate this very index and ones just like it many times before without any issue and how the database clearly exceeded the size limit, yet now it's suddenly enforcing the limit.


texts:
I have defined a table named Inventory that consists of one varchar(45) field named ItemNameCode.  Here is the table and contains the following alphanumeric values.

Which we want to sort alphanumerically as:

MES40
PT14
PT14-403
PT14-646-649
PT14-646-649
PT15-3-11
PT15-12-42
PT15-12-572-575
PT16-164
PT16-227
PT16-62-71
PT16-136
PT16-137

I tried the bellow query but not getting exact order:

SELECT ItemNameCode
FROM Inventory
ORDER BY LEFT(ItemNameCode,PATINDEX('%[0-9]%',ItemNameCode)-1)


texts:
In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a specific column name derived from the row's unique id. (e.g. 'Lat1' and 'Lat2 for a 'Latitude' source column).

All is fine when I use INT or FLOAT data types but when I try to use a DateTime all  VALUES become DateTime.

Example Shipping Lat/Long/DateTime

We have two points in a ships journey reported on two different days:

CREATE TABLE #Shipping
(
     [RouteID]     [INT]  NOT NULL,
     [Latitude]    FLOAT NOT NULL,
     [Longitude]   FLOAT NOT NULL,
     [Time]        DATETIME NOT NULL

);

INSERT #Shipping(RouteID, [Latitude], [Longitude], [Time])
VALUES          (1,         18.0221,    -63.1206,  '24-Jan-2016'),
                (2,         17.8353,    -62.99667, '25-Jan-2016');

Successful CrossApply
 If we use CrossApply against the data for the first three columns

  SELECT  col+cast([RouteID] as varchar(1)) new_col
         , X.value
       FROM #Shipping
  CROSS APPLY
  (
      VALUES
         (RouteID,   'Id')
      ,  (Latitude,  'Lat')
      ,  (Longitude, 'Lon')
  ) X (value, col)

Our results are as expected:

Great!

Failure CrossApply With DateTime

But once we add DateTime to the mix:

      SELECT  col+cast([RouteID] as varchar(1)) new_col
             , X.value
           FROM #Shipping
      CROSS APPLY
      (
          VALUES
             (RouteID,   'Id')
          ,  (Latitude,  'Lat')
          ,  (Longitude, 'Lon')
          , ([Time],    'Time')
      ) X (value, col)

All become DateTimes

How should one work around this if my end goal is to pivot all the values from the original table into one row?


texts: How to UPDATE a column by COUNT of FK from another table?

texts:
How to extend this query for the entire table, to update all ids?

UPDATE table1 SET number=(
SELECT COUNT(*) FROM table2 where id=1
) WHERE id=1

id is the PRIMARY KEY in table1 and FK in table2.

How to remove the WHERE clause to update each row of table1 by counting the number of rows in table2 with the same FK id?


texts:
I have more of a conceptual question if anyone has examples or some ideas of how others have accomplished this.

On a sales reporting site, a user should be able to pull information based on the following hierarchical structure:

Account Level
Department Level
Division Level

The categories are encompassing as you move up the chain (ie. Dept includes all accounts within that group). A user could have access to several groups within each tier. But a user would not have cross-category filters (ie. both a Department & Division level filter).

Would all this be accomplished in a single table with a column indicating the filter level and several rows per user with each criteria? What other suggestions are there?


texts: MYSQL Huge Data migration (5TB) from old server to new server

texts:
We have an old MYSQL server  contains huge data ~5TB and wanted to migrate to the new server in order to minimize the costs and get rid of very old hardware.
My one and only idea in my mind is using mysqldump for the migration but I’m pretty sure that it’s a poor and risk option with that huge data.
Then someone in my team came up with the idea for using ETL tools but we haven’t go into the deeper details and not really sure if this ETL way can literally help us.
Any idea are always welcomed
thanks


texts: Update an array element with inc mongo update

texts:
HI All I have this Data in mongo,

{"articleId" : [
            {
                    "articleId" : "9514666",
                    "articleCount" : 1
            }
    ],

    "count" : NumberLong(1),
    "timeStamp" : NumberLong("1416634200000"),
    "interval" : 1,
    "tags" : "famous"
 }

I want to update it using this new data

 {"articleId" : [
            {
                    "articleId" : "9514666",
                    "articleCount" : 3
            }
            {
                    "articleId" : "9514667",
                    "articleCount" : 3
            }
    ],

    "count" : NumberLong(6),
    "timeStamp" : NumberLong("1416634200000"),
    "interval" : 1,
    "tags" : "famous"
 }

What i need in the output is

{"articleId" : [
            {
                    "articleId" : "9514666",
                    "articleCount" : 4
            }
            {
                    "articleId" : "9514667",
                    "articleCount" : 3
            }
    ],

    "count" : NumberLong(7),
    "timeStamp" : NumberLong("1416634200000"),
    "interval" : 1,
    "tags" : "famous"
 }

Could you please suggest me how can i achieve this this using update operation
My update query will have tags field as query parameter.


texts: Are most of the NoSQL databases key/value stores technically?

texts:
This video distinguishes between three (main) types of NoSQL databases:

Key/Value store
Tabular
Document oriented

Do these types differ in how they technically store their data or can they be considered all to be key/value stores since even the tabular ones (like Cassandra) may have variable column count and most likely serialize the column values to a string? And the documents (MongoDB) are also only strings in the end... I suppose all types generate an unique id for their "values/data". So are all the 3 types key/value on low level?


texts: Where I can find the tnsnames.ora file for my Oracle Windows client?

texts:
I installed Oracle client for my Windows 7 machine. I can connect to a remote Oracle database using Toad for Oracle using the direct method (giving host, port and service name for the connection).

Now I tried to find the tnsnames.ora file from the Oracle client oracle home, but could not find it. I could not find a NETWORK\ADMIN folder in ORACLE_HOME (C:\app\myaccount\product\11.2.0\client_1).

Where is this file located?


texts: Is there an efficient way to see the cause for "String or binary data would be truncated"?

texts:
This is a follow-up on this question. It is also related to this feature request from Microsoft.

However, many years have passed and several major releases reached the market since it was reported.

Question: does SQL Server 2017 provide any mechanism to ease finding out the root cause of this error? Or it is as hard to investigate as it was about 9 years ago when the issue was reported?


texts:
I have a large text corpus that I am storing in Postgres. A table articles has all of the metadata for each article, about 19 million rows. It's primary key is articles.doc_id. Another table (texts) stores the texts themselves, with a text column for the document text, and a column texts.doc_id which is a primary key that is also a foreign key to articles.doc_id.

Is this the best way to store a large amount of text, or would I better off just having a another column on the articles table with the text?


texts: Query with two LEFT JOIN's is not working as expected

texts:
I am using PostgreSQL as my database and I need to write the query that returns:

a user_id from 1st table
sum of every row's amount from 2nd table (where user_id is the same as in the 1st table)
sum of every row's amount from 3nd table (where user_id is the same as in the 1st table and the type is defined).

So I wrote this query with two LEFT JOINs, but it doesn't work as expected:

SELECT aff.user_id, COALESCE(SUM(t.amount), 0) as total_bet, COALESCE(SUM(d.amount), 0) as total_deposit
FROM affiliate_guests aff
LEFT JOIN transfers t ON aff.user_id = t.user_id
LEFT JOIN deposits d ON aff.user_id = d.user_id AND d.type = 'deposit'
WHERE aff.code = 'code'
GROUP BY aff.user_id;

This query returns the wrong numbers for total_bet and total_deposit columns.

If I omit the second LEFT JOIN, like this:

SELECT aff.user_id, COALESCE(SUM(t.amount), 0) as total_bet
FROM affiliate_guests aff
LEFT JOIN transfers t ON aff.user_id = t.user_id
WHERE aff.code = 'code'
GROUP BY aff.user_id;

everything works fine.

What can be the problem and how can I solve it?


texts: Reclaiming mysql empty space... nothing works

texts:
I've deleted tons of records from my database but I can't reclaim the space....
Use % is at 96%
Your MariaDB connection id is 242212
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

I have innodb_file_per_table -
MariaDB [(none)]>  show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.16 sec)

I have the table with the space I need to reclaim
MariaDB [(none)]> SELECT TABLE_NAME,
    -> ROUND(DATA_LENGTH/1024/1024) AS USED_SPACE_MB,
    -> ROUND(DATA_FREE/1024/1024) AS UNUSED_SPACE_MB
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE ROUND(DATA_FREE/1024/1024) > 1000
    -> ORDER BY UNUSED_SPACE_MB;
+--------------+---------------+-----------------+
| TABLE_NAME   | USED_SPACE_MB | UNUSED_SPACE_MB |
+--------------+---------------+-----------------+
| correlations |         65720 |           54103 |
+--------------+---------------+-----------------+
1 row in set (0.01 sec)

But when I run
'optimize table correlations'
.... it does nothing.
I even ran mysqlcheck -o
....it does nothing.
Use % stays at 96%.
Is there something I'm missing?   I'd appreciate any suggestions.  Thanks!
Adding config info...
[mysqld]
innodb-defragment=1
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
innodb_buffer_pool_size = 4G
skip-name-resolve
max_heap_table_size= 128M
tmp_table_size= 128M

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M


texts: Access Web App query 3 Tables with aggregatios - Need a workaround

texts:
I'm designing an Access 2016 Web App for inventoring our computers.

The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC.

The computer-value in RAM and CPU are lookups on Computer.DNS
So in SQL it would be something like

SELECT Computer.DNS,SUM(CPU.Cores),SUM(RAM.Size) FROM Computer,CPU,RAM GROUP BY Computer.DNS

The result from the GUI-Query aboth is

[

Both SUM coloumns are (for most PCs) wrong.

The formula it's using (which are wrong!):

Cores: COUNT(CPUs)       * realAmount

RAM  : COUNT(RAM-sticks) * realAmount

Can anybody show me why it's doing that and what I did wrong?

I tried using

Cores: Sum([CPU].[Cores]-Count([RAM].[Size]))

in the "Field"-field but I get the error "Cannot have aggregate function in expression"


texts: Adding standard set of constraints (rules) to PostGIS `raster` type column

texts:
Following on from my related prior question, let me elaborate this topic a bit.

raster2pgsql is a raster loader executable that loads GDAL supported raster formats in PostGIS. It has a -C flag defined as follows:

gislinux@gislinux-Precision-M4600:~$ raster2pgsql

Output:

-C  Set the standard set of constraints on the raster
column after the rasters are loaded. Some constraints may fail
if one or more rasters violate the constraint.

When I am importing my raster file like this:

gislinux@gislinux-Precision-M4600:~$ raster2pgsql -d -I -C -M -F -t 100x100 -s 4326
us_tmin_2012.01.asc chp05.us_tmin_new | psql -h localhost -p 5432 -U postgres -d pgrouting

Output:

ANALYZE
NOTICE:  Adding SRID constraint
CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
NOTICE:  Adding scale-X constraint

Few constraints have been applied to this new table by the -C flag.

pgrouting=# \d+ chp05.us_tmin_new

Output:

Indexes:
"us_tmin_new_pkey" PRIMARY KEY, btree (rid)
"us_tmin_new_rast_gist" gist (st_convexhull(rast))
Check constraints:
"enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[100, 21]))
"enforce_max_extent_rast" CHECK (st_coveredby(st_convexhull(rast),

The standard constraints comprise the following rules (although these constraints are acting independently on all the incoming raster tiles):

Width and height: This rule states that all the rasters must have the same width  and height.
Scale X and Y: This rule states that all the rasters must have the same scale X and Y.
SRID: This rule states that all rasters must have the same SRID.
Same alignment: This rule states that all rasters must be aligned to one another.
Maximum extent: This rule states that all rasters must be within the table's
maximum extent.
Number of bands: This rule states that all rasters must have the same number
of bands (means this ensures that all the coming rasters must have a specific number of bands, it's not comparing existing raster tiles).
NODATA values: This rule states that all raster bands at a specific index must have the same NODATA value.
Out-db: This rule states that all raster bands at a specific index must be in-db or out-db, not both.
Pixel type: This rule states that all raster bands at a specific index must be of the same pixel type.

NOW, in order to run ST_MapAlgebra function, I had to drop these std constraints individually, which I did using:

ALTER TABLE chp05.us_tmin_new DROP CONSTRAINT enforce_scalex_rast

in pgAdmin SQL Editor for each of those std constraints. But now I don't know how to bring these standard constraints back? The following command is not working:

ALTER TABLE chp05.us_tmin_new ADD CONSTRAINT enforce_scalex_rast unique (rast);

and giving the following error:

ERROR:  data type raster has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.


texts:
My Windows 10 users need the ODBC driver for Db2 installed.
I can locate the full driver package:
ibm_data_server_driver_package_win64_v11.1.1.3fp3b.exe
but it is a 100MB+ package.
Would there be a smaller package to distribute?


texts: Getting "BadImageFormatException" when attempting to connect through Visual Studio to Oracle

texts:
I get the following message: “Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.” when I attempt to create a connection from both Visual Studio 2008 and 2012 to Oracle from my Windows 8 64bit workstation.

I’m not running a web app when this message happens. I’m only trying to establish an Oracle connection with Visual Studio.
Any solutions posted on the internet that I have seen that resolve this issue with others, doesn’t seem to apply to my circumstances.

I did not have this issue when I was using Widows 7 64bit.
I have set the Active Solution Platform to both Any PC and x86 when I have a solution open, but I still get the same message with either setting.
I have the latest Oracle 32 bit Client since 64 bit Oracle client won’t work with 32 bit Visual Studio.
At this point, I am only trying to connect to Oracle from Visual Studio and not run a web application. So IIS settings have nothing to do with this issue.

My environment is

Windows 8 64bit
Visual Studio 2012 and 2008
Oracle 32 bit Client. But also tried 64 Bit Client.
Oracle 11.2.0.3 database.
Active Solution Platform was tried with both Any PC and x86.

Any suggestions would be appreciated.


texts: What would cause an UPDATE statment to hang when executing from an .mdb file to SQL Server 2008 R2 over ODBC?

texts:
Is there anything that would cause an UPDATE statement INNER JOINed between a separate MS-Access linked table and an SQL Server 2008 R2 table to hang, when the values are being set on the SQL Server side of the join?

I already checked the MS-Access linked table to see if there was an issue with corrupted records (sorting the table by various columns to find one), but I didn't find any.

I also tried compacting the .mdb that makes the calls.

I'm thinking about re-indexing the table in question in SQL Server 2008 R2, I'm not sure if that will help.  Is there anything else I can try?


texts:
I have two tables Places and Redemptions (details in gist)

https://gist.github.com/kunashir/2648e6e4d8a97aa2d03859b8ee08a394

And the query takes a lot of time:

SQL (26910.2ms)  SELECT "redemptions"."id" AS t0_r0,
"redemptions"."uuid" AS t0_r1, "redemptions"."code_value_id" AS t0_r2,
"redemptions"."created_at" AS t0_r3, "redemptions"."updated_at" AS t0_r4,
"redemptions"."place_id" AS t0_r5, "redemptions"."user_id" AS t0_r6,
"places"."id" AS t1_r0, "places"."name" AS t1_r1, "places"."geo_fence" AS t1_r2,
"places"."external_id" AS t1_r3, "places"."city" AS t1_r4,
"places"."country" AS t1_r5, "places"."bounding_box_north" AS t1_r6,
"places"."bounding_box_south" AS t1_r7, "places"."bounding_box_east" AS t1_r8,
"places"."bounding_box_west" AS t1_r9, "places"."image" AS t1_r10,
"places"."created_at" AS t1_r11, "places"."updated_at" AS t1_r12,
"places"."latitude" AS t1_r13, "places"."longitude" AS t1_r14,
"places"."image_uid" AS t1_r15, "places"."image_name" AS t1_r16,
"places"."featured" AS t1_r17, "places"."active_benefits_count" AS t1_r18,
"places"."radius" AS t1_r19, "places"."terminal" AS t1_r20,
"places"."deleted_at" AS t1_r21, "places"."category" AS t1_r22,
"places"."street" AS t1_r23, "places"."zip" AS t1_r24,
"places"."twitter" AS t1_r25, "places"."push_notification_message" AS t1_r26,
"places"."timezone" AS t1_r27, "places"."twitter_boost" AS t1_r28,
"places"."push_notification_message_enabled" AS t1_r29,
"places"."geojson" AS t1_r30, "places"."push_notification_image_uid" AS t1_r31,
"places"."searchable" AS t1_r32, "places"."boost" AS t1_r33,
"places"."geojson_active" AS t1_r34
FROM "redemptions" LEFT OUTER JOIN "places" ON "places"."id" = "redemptions"."place_id" AND "places"."deleted_at" IS NULL

The result of explain: https://explain.depesz.com/s/PYPC

I can't understand when is bottleneck or all is right?


texts:
I have a datafile in a recover state:
SQL> select status from v$datafile where file#=201;

STATUS
---------------------
RECOVER

I'm not really sure how it was damaged, looks like something went wrong right after its creation time.
SQL> select creation_change#,creation_time,checkpoint_change#,checkpoint_time,last_change#,last_time
from v$datafile where file#=201;

CREATION_CHANGE# CREATION_TIME       CHECKPOINT_CHANGE# CHECKPOINT_TIME      LAST_CHANGE# LAST_TIME
---------------- ------------------- ------------------ ------------------- ------------- -------------------
    141311481693 29/08/2019 16:54:57       141311481694 29/08/2019 16:54:57  141311481738 29/08/2019 16:55:15

I've checked every table in a tablespace this file belong to and everything seemd OK. First I tried
SELECT /*+ FULL(t) */ count(*) from table_name; for every table, then I tried ANALYZE TABLE table_name VALIDATE STRUCTURE;. No errors.
I'm pretty sure there is no option to drop an offline/recover datafile. Or is there?
I have one thought in my mind: create a new tablespace, move all objects there and then recreate the TS with a failed datafile in it. Then move everything back. Is this OK thing to do? Or am I missing something?
Please advice.


texts: MySQL Timezone Resets after exiting server

texts:
I am running this code to set my database's timezone:

set time_zone = "US/Eastern"

This successfully changes the time to the correct time. However, as soon as I "exit" the database and re-enter, it reverts back to what I had originally changed it from. I have also tried this code:

set time_zone = "-04:00";

The same thing happens, it reverts to the servers default time. Any suggestions?


texts: Handling performance problems with jit in postgres 12

texts:
tl;dr: What is the best way to handle performance decrease caused by jit?

Background:

Lately I have migrated from postgres 11 to 12 and noticed, that some queries / procedures run considerably slower. I did some research and tested different configurations - it turned out, that the jit overhead exceeds its gains. I see two possible solutions:

Disable jit permanently for all queries by running set jit = off; once. This helps, but does not feel natural. (Maybe because of changing the default configuration, which I did not much as there was no such need. The defaults are sane and there is low probability they need to be changed.) This would also mean throwing away possible performance gains when running long queries.
Disabling jit at the beginning of the procedure and enabling it at the end. I have objections about the impact of changing the configuration on queries running in parallel. Of course this can harm performance of log queries, that would run alongside, but maybe this can cause other, possibly hard to detect bugs. Also this seems even less right than the first solution - changing the configuration as a part of the procedure.

The best way to handle this would be by fine-tuning the configuration, so that jit is not discarded in its entirety, but also configuration is not changed at runtime. Unfortunately I found little to no resource on how that would be possible (only parameters like jit_above_cost in the documentation that seem to be of little help).

Questions: What is the best way to solve this issue? Are there any criteria about when to disable jit except for running and measuring? Can the behavior be fine-tuned (for example jit disabled for specific queries)?

Edit: I'm attaching a plan with and without jit. The plans are too big to post here.


texts: TSQL DateTime DataType - fractional seconds vs milliseconds

texts:
Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000 and 2013-03-05 23:59:59.999. We're getting overlap at the backend with things that happened at 2013-03-06 00:00:00.000.

So, in testing I run in SSMS:

select cast('2013-03-05 23:59:59.999' as datetime)
select cast('2013-03-05 23:59:59.996' as datetime)
select cast('2013-03-05 23:59:59.994' as datetime)

And get:

2013-03-06 00:00:00.000
2013-03-05 23:59:59.997
2013-03-05 23:59:59.993

The millisecond part comes back off by a little. In the case of the first line it really matters. Because I don't want 2013-03-06 00:00:00.000 - I want a millisecond before midnight.

TSQL states in documentation that it uses fractional seconds as opposed to .Net datetime which uses milliseconds. This would seem to be a syntax difference only but if you really care about millisecond precision in TSQL are you forced to use DateTime2 across the board.  Casting from datetime2 to datetime still mucks things up by a couple milliseconds.

In c# DateTime.Parse("03/5/2013 23:59:59.999").Millisecond still returns 999.

Am I interpreting this all right? If I care about millisecond precision am I stuck with datetime2 only.

Normally that would be an easy fix but in this case we're using a Microsoft system function that receives datetime as parameter.


texts: Galera Cluster with MariaDB 10 and asynchronous slave

texts:
As it's easier to understand what I'm trying to accomplish with a picture, here's a picture:

I'm really not an expert in database administration, so please be patient.

I am working on a setup where I run three servers with MariaDB 10.0.12 and Galera cluster. I also have a separate installation of MariaDB that I want to run a master/slave setup where the databases gets replicated from each Galera node through keepalived and haproxy for high-availability.

After setting everything up according to a jumble of guides online, I have set up the Galera cluster and servers just fine. However, the replication to my async server doesn't seem to be working very well. Mind you I am also trying to use GTID for all of this using Using_Gtid: Slave_pos. The slave server uses the virtual IP of keepalived to connect to remotely.

Right now I'm getting the following error under the show slave status part of the slave:

  Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.'

This seems to be related to some kind of problem with the slave not being able to sync with the master servers due to the master logs being too high in value (e.g.: mariadb-bin.000080 on the master servers). Could that be the case? And if so, how do I manage to update the slave to understand that it needs to get everything up to the latest?

I really hope someone could help me out with this, or at least show me some way to better solve my current problem.

I was expecting the slave data to be synchronized from the Galera cluster at first connection and the GTID be assigned automatically, but is this is not the case? If so, how do I get the slave up to date?


texts:
I need to setup logical replication from PostgreSQL 10.5 EDB (Publisher) to PostgreSQL Community 10.5 (Subscriber).
The database size is approximately 10TB in size, so we're concerned that setting up replication may induce some performance issues, or create other issues we're not thinking about.
Our individual tables are up to 5TB in size.
I fear that replication could cause some side effect in production or increase response times, etc.
What are the best practices that we can follow to complete this smoothly?


texts:
How to store "other" text?

In summary, I have too many of these kinds of fields (like hundreds?):

http://i.stack.imgur.com/0H81O.png

I don't know an appropriate way to handle this.

If I store only text of these options, then I will have data redundancy and no foreign key. Maybe foreign key does not matter since I am allowing users to store "other" anyway.


texts: Create cyclic pointers in postgresql 9.5

texts:
The problem is to write the "ADD-VALUE" request defined below.
For each "cat" value from the table below, keep only 3 records. Figure this table:

id
cat
value
updatedAt

1
cat1
v1
06/01/2021 00:00:01

2
cat1
v2
06/01/2021 00:00:02

3
cat1
v3
06/01/2021 00:00:03 (pointer cat1 is here)

4
cat2
v1
06/01/2021 00:01:01

5
cat2
v2
06/01/2021 00:01:02  (pointer cat2 is here)

INSERT case: Calling "ADD-VALUE(cat=cat2, value=v3)" will produce the result in bold:

id
cat
value
updatedAt

1
cat1
v1
06/01/2021 00:00:01

2
cat1
v2
06/01/2021 00:00:02

3
cat1
v3
06/01/2021 00:00:03 (pointer cat1 is here)

4
cat2
v1
06/01/2021 00:01:01

5
cat2
v2
06/01/2021 00:01:02

6
cat2
v3
06/01/2021 00:01:02  (pointer cat2 is now here)

UPDATE case: Calling "ADD-VALUE(cat=cat1, value=v4)" will produce the result in bold:

id
cat
value
updatedAt

1
cat1
v4
07/01/2021 00:00:04  (pointer cat1 is now here)

2
cat1
v2
06/01/2021 00:00:02

3
cat1
v3
06/01/2021 00:00:03

4
cat2
v1
06/01/2021 00:01:01

5
cat2
v2
06/01/2021 00:01:02  (pointer cat2 is here)

Any advice is welcome. UPDATE or INSERT in one request is maybe impossible? I think about using row-num to have the count per category.


texts: How to store LOB-image or PDF in Postgresql and what is the impact of having in Postgresql?

texts:
Please guide me to store LOB-image or PDF in Postgresql and let me know what is the impact of storing these types of objects on Postgresql database.

Any help will be appreciated.


texts: Query to compare the structure of two tables in MySQL

texts:
To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version).

Can you think of a query that can compare two tables?

Here are some example tables that you can compare.

CREATE TABLE product_today
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_yesterday
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_2days_back
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

The first two tables have identical structures. The last one is different. I just need to know whether two tables have different structures or not. I'm not interested in the how they differ.