texts: Case statements for multiple fields when only certain cases exist for each field

texts:
We have an inventory feature where we generate Bills. There is an Edit Bill API call. We have implemented it as PATCH call.

A Bill with id = 1 has 2 LineItems :

|  Stock Id |   Qty        |  Rate       |
|    10     |      2       |    10       |
|    11     |      3       |    20       |

Now lets say I want to change the quantity for stock Id : 10 to 5 and I want to change the rate for stock Id : 11 to 40

We have represented it as PATCH Call :

bill : {
    id : 1

    lineItems : [
    {
        stockId : 10,
        qty : 5,
     },

     {
        stockId : 11,
        rate : 40
     }
    ]
    }

In the backend we run following query :

 UPDATE `billlineitem`
SET    `rate` = ( CASE
                    WHEN stockid = 11 THEN '40'
                    ELSE rate
                  END ),
       `qty` = ( CASE
                   WHEN stockid = 10 THEN 5
                   ELSE qty
                 END ),
       `updated_billitemquantity_at` = '2019-09-06 05:16:06.219'
WHERE  `bill_id` = '1'
       AND `stockid` IN ( 10, 11 )

Is it ok, in the above case when there is no change for an attribute then the else clause will take the value from the database for that attribute. The above update statement is run in a transaction.

Is this a correct approach? Will this do an update for every attribute for every stock Id. Is there a better approach?

We are using MySQL DB.

Apart from qty and rate we also have discountType, discountValue and there are other 4 more attributes.


texts: Postgres cascades truncates to unrelated tables

texts:
I've created a number of tables in my public schema.

Occasionally, I need to run a seeder to refresh the users table when I perform tests. To do this, I'm executing TRUNCATE TABLE users RESTART IDENTITY CASCADE.

However, I do not want any other tables but those related to the users table to be affected. The only foreign key referencing the users table is on the teams table which references users through owner_id. The teams table truncates as expected.

What isn't expected, is that the attributes table is also truncated by TRUNCATE TABLE users RESTART IDENTITY CASCADE.

The attributes table has one foreign key on the accounts table which references the users table. However, on the attributes table, ALL account_id values are NULL so there is no actual reference back on any of the records to a specific record in the accounts table. In fact, at the time of executing the truncate query, the entire accounts table is already empty.

How then is TRUNCATE TABLE users RESTART IDENTITY CASCADE causing my attributes table to be truncated based on NULL foreign keys?


texts: Why doesn't pg_dump include all database SETtings?

texts:
I'm trying to have this included in the script generated by pg_dump.

ALTER DATABASE foo SET plv8.start_proc = 'bar';

I'm using the following command

pg_dump -s -n public -d development > foo.sql

The dump file includes some SETtings, but it doesn't include the one above.

What am I doing wrong?


texts: Immediate e-mail alerting for error 229 (permission denied)

texts:
For a few reasons, I'd like to have all permission denied errors (number 229) emailed to me. Checking master.sys.messages shows that is_event_logged for this error is 0. Thus that rules out using SQL Server Agent error alerts, which rely on the SQL Server error log (I've tested this - I get no alert).

I figured I'd peek in mssqlsystemresource and look at the view definition for sys.messages, thinking maybe I could update is_event_logged for the message in question. But this view gets the system error message from OpenRowset(TABLE SYSERRORS), so that's a no-go.

Is there a reasonably simple way I can get all 229 errors emailed to me immediately (or within maybe 30 seconds) without harming server performance? A 60-second cool-down between emails would probably be a good idea too.


texts:
1/ DESCRIPTION:

Machine 1 (slave): Centos 6.6 , x64 , installed PostgreSQL 9.3  (on Local)
Machine 2 (master): Centos 6.6 , x64 , installed PostgreSQL 9.3  (on Cloud)

Machine 1 (slave) and machine 2 (master) are in a cluster (streaming replication). Sometime, I see "FATAL:  terminating walreceiver due to timeout" in slave log.

Here is full detailed logs:

Slave

2015-03-03 02:01:53 UTC 19693   LOG:  database system is ready to accept read only connections
2015-03-03 02:01:53 UTC 19699   LOG:  started streaming WAL from primary at 0/8000000 on timeline 1
2015-03-03 02:02:15 UTC 19695   LOG:  redo starts at 0/8F04530
2015-03-03 02:39:26 UTC 19699   FATAL:  terminating walreceiver due to timeout
2015-03-03 02:39:26 UTC 19695   LOG:  invalid record length at 0/8F080F8
2015-03-03 02:39:41 UTC 21065   LOG:  started streaming WAL from primary at 0/8000000 on timeline 1
2015-03-03 03:19:12 UTC 21065   FATAL:  terminating walreceiver due to timeout
2015-03-03 03:19:12 UTC 19695   LOG:  invalid record length at 0/9D488F8
2015-03-03 03:19:27 UTC 22489   LOG:  started streaming WAL from primary at 0/9000000 on timeline 1

Master

2015-03-03 02:02:40 UTC 1718   LOG:  database system is ready to accept connections
2015-03-03 02:02:40 UTC 1724   LOG:  autovacuum launcher started
2015-03-03 02:02:42 UTC 1726 [unknown] [unknown]LOG:  invalid length of startup packet
2015-03-03 02:02:42 UTC 1726 [unknown] [unknown]LOG:  connection failed during start up processing: user= database=
2015-03-03 02:35:45 UTC 1788 pgAdmin III - Query Tool enterprisedbERROR:  column "username" does not exist at character 18
2015-03-03 02:35:45 UTC 1788 pgAdmin III - Query Tool enterprisedbSTATEMENT:
        select datname, username, client_addr, client_port, query from pg_stat_activity;
2015-03-03 02:41:03 UTC 1748 walreceiver enterprisedbLOG:  terminating walsender process due to replication timeout
2015-03-03 02:51:42 UTC 3184 ::1 psql.bin enterprisedbERROR:  unrecognized configuration parameter "replication_timeout"
2015-03-03 02:51:42 UTC 3184 ::1 psql.bin enterprisedbSTATEMENT:  show replication_timeout;
2015-03-03 02:51:54 UTC 3184 ::1 psql.bin enterprisedbERROR:  relation "pg_setting" does not exist at character 15
2015-03-03 02:51:54 UTC 3184 ::1 psql.bin enterprisedbSTATEMENT:  select * from pg_setting;
2015-03-03 02:58:33 UTC 3388 [unknown] [unknown]LOG:  invalid length of startup packet
2015-03-03 02:58:33 UTC 3388 [unknown] [unknown]LOG:  connection failed during start up processing: user= database=
2015-03-03 02:58:57 UTC 3390 [unknown] [unknown]LOG:  incomplete startup packet
2015-03-03 02:58:57 UTC 3390 [unknown] [unknown]LOG:  connection failed during start up processing: user= database=
2015-03-03 03:15:04 UTC 3967 [unknown] enterprisedbFATAL:  database "enterprisedb" does not exist
2015-03-03 03:20:53 UTC 2884 walreceiver enterprisedbLOG:  terminating walsender process due to replication timeout

2/ QUESTION:

What is about "FATAL:  terminating walreceiver due to timeout" problem ? How can I fix it ?


texts: Scaling down DB where filesystem is almost full

texts:
Our DBA is on holiday, and since I know 'a bit' of MySQL, they've asked me to fix this. The data slice is at 99%, and I need to know what to get rid of.

  [root@servername]# find . -type f -size +1000000k -exec ls -lh {} \; |
  awk '{ print $9 ": " $5 }' ./mysql/stuffInDatabase.ibd: 6.8G

Thing is, I've never done this before, and so don't know where to start. Can someone point me in the right direction? Which database do I need to use, the production one or the information_schema? Which query do I use? Do I need to dump and then reimport?

Thanks in advance.


texts: How to select TOP 1 value from each column where value is not null

texts:
I am trying to select many columns from a table where there can be multiple rows with the same ID.  Each row contains data from columns that I want to return in one row.  Any time a column has a value other than NULL, I want to show that.  If all values in that column are NULL, it is OK to show NULL.

Example)

SELECT * FROM Table1 WHERE UnitID='73355'

This returns multiple rows.  Each row contains data in columns that I want to merge together.  I don't want to keep my WHERE clause.  In this example I am just using it to narrow down my results for troubleshooting purposes.  I want every row to return in the table but merge the data where the UnitID's match.  There could be 1, 2, 3, or more rows returned with matching UnitID's.  Each Row will have data in it I will want merged into 1 row.

I tried TOP 1 but it only returns the first row.  I want TOP 1 but each column I want the value that is NOT NULL.

I have this now...If i put in my WHERE clause a specific UnitID, it works.  However, I want to put other columns in my WHERE clause and remove UnitID.  I want to filter based off LRUnitStatus and Model.  As soon as I do that, it does not work.

    SELECT UnitID,
           MAX(LRUnitStatus) as 'Status',
           MAX([UnitNumber]) as 'Unit #',
           MAX(Make) as Make, MAX(Model) as Model,
           MAX([ProductionYear]) as 'Year',
           MAX(Length) as 'Length',
           MIN([Rear Door Type]) as 'Rear Door',
           MAX([Suspension Type]) as 'Suspension',
           MAX([LocationBranchCode]) as 'Location',
           MAX(HUBO) as 'HUBO',
           MIN([Wheel Type]) as 'Wheels',
           MAX([Tire Size]) as 'Tires',
           MAX([Floor Type]) as 'Floor',
           MAX([Tire Inflation]) as 'Tire Inflation',
           MAX([Side Skirts]) as 'Skirts',
           MAX([Side Panels]) as 'Panels',
           MAX([Roof Type]) as 'Roof',
           MAX([Logistic Posts]) as 'Log. Posts',
           MAX([E-Track]) as 'ETrack',
           MAX([Axle Spread]) as 'Axle'
    FROM   [ProfitMaster].[dbo].[vwUKTS_LR_jtg] LR
    WHERE  UnitID='73316'
    GROUP BY UnitID


texts: SSIS - Data Import & export wizard - existing tables

texts:
For a project I'm attempting to move data from a view in source to a table in destination. And this will need to be done periodically.
The quickest way to do this seems to be a SSIS package (as this also grants me additional control for possible data transformations in a following phase.

Now, the first time this works excellent, I go through the wizard and the destination table(s) are created and the data imjected.
However, when I run the same package a 2nd time SSIS complains that the tables already exist (which is right).

The only way I see how to rectify this is to change the source queries and add an ifexists clause. This though will become a lot of work as I will need to do this for 144 destination tables.

Hence my question, is the if exists clause the only way to capture this or is there an option within the wizard/SSIS to have the package check this for me and only create the table if it doesn't exist yet?


texts: Are there any risks in running 'flush hosts'?

texts:
Due to an error in my code I got my PC blocked from an active production DB (too many connection errors).
The error calls for me to use 'flush hosts' in order to reset the host cache.

Are there any risks involved in doing so? Will active connections be terminated? Does it do anything beyond just flushing the cache?


texts: Replicating a database that uses Fail Over

texts: We are trying to setup replication for data coming in from an outside vendor. The issue that we are having is they say that because our vendor is using SQL Fail Over Clustering so they claim that the DB cant be replicated? Is that true and is there any work arounds.


texts: Diagnosing Blocking issues using Blocked Process report

texts:
I have an application that is going down as a result of timeouts. During these times we see a massive spike in response times from the database that holds ASP session state.

In an effort to to diagnose the issue I have used SP_CONFIGURE'blocked process threshold',15 and ran a trace to catch any events that are fired. During the next incident the trace capture some blocking events in the Session State database.

I've identified the lead blocker and can see that it's suspended and waiting.

<blocked-process-report>
    <blocked-process>
        <process id="process53fb4c8"
                 taskpriority="0" logused="0"
                 waitresource="KEY: 10:72057594039238656 (594b17b7493c)" waittime="20631"
                 ownerId="9710782702" transactionname="UPDATE"
                 lasttranstarted="2013-06-27T17:29:38.130" XDES="0xc429df950" lockMode="U"
                 schedulerid="14" kpid="6912" status="suspended" spid="359" sbid="0" ecid="0"
                 priority="0" trancount="2" lastbatchstarted="2013-06-27T17:29:38.130"
                 lastbatchcompleted="2013-06-27T17:29:38.107"
                 clientapp=".Net SqlClient Data Provider" hostname="WEB03" hostpid="7520"
                 loginname="State" isolationlevel="read committed (2)" xactid="9710782702"
                 currentdb="10" lockTimeout="4294967295"
                 clientoption1="536870944" clientoption2="128056">
            <executionStack>
                <frame line="18" stmtstart="1114" stmtend="4494"
                       sqlhandle="0x03000a00210f020741a1be0058a000000100000000000000"/>
            </executionStack>
            <inputbuf>
                Proc [Database Id = 10 Object Id = 117575457]
            </inputbuf>
        </process>
    </blocked-process>
    <blocking-process>
        <process status="suspended" waittime="1046" spid="217" sbid="0" ecid="0"
                 priority="0" trancount="1" lastbatchstarted="2013-06-27T17:29:29.113"
                 lastbatchcompleted="2013-06-27T17:29:29.010"
                 clientapp=".Net SqlClient Data Provider" hostname="WEB02" hostpid="6236"
                 loginname="CState" isolationlevel="read committed (2)" xactid="9710776428"
                 currentdb="10" lockTimeout="4294967295" clientoption1="536870944"
                 clientoption2="128056">
            <executionStack>
                <frame line="18" stmtstart="1114" stmtend="4494"
                       sqlhandle="0x03000a00210f020741a1be0058a000000100000000000000"/>
            </executionStack>
            <inputbuf>
                Proc [Database Id = 10 Object Id = 117575457]
            </inputbuf>
        </process>
    </blocking-process>
</blocked-process-report>

My questions are

What the unit of time is for waittime="1046"?
What resource is the wait on?
Where to go from here?


texts: Why did a Redshift UNLOAD increase precision of a float4 field in the output?

texts:
I created a table in AWS Redshift such as

CREATE TABLE exampleTableName (
  id       int identity(1,1),
  accId    varchar(16) encode zstd,
  amount   float4,

  primary key(id)
)
distkey(accId)
interleaved sortkey(accId);

An example record in the table has the amount field's value as 120.12.

However, when I try to export the data by performing an UNLOAD, the resulting file (essentially a CSV) has additional precision for the field value.

The unload command:

UNLOAD ('SELECT * from exampleTableName')
TO 's3://bucket/prefixFile_'
IAM_ROLE 'XXX'
HEADER
ADDQUOTES
PARALLEL OFF
MAXFILESIZE AS 5gb
DELIMITER AS ','
GZIP;

The field value in the resulting output: 120.120003 (i.e. it has added 4 more decimal places, that aren't in the original dataset).

Why is this happening, and how can I prevent the additional precision (i.e. decimal places) from being output as part of the UNLOAD command?


texts:
I have a Table that stored GPS data from vehicle trackers.

There are 8 vehicles tracked via an API. Each time the information is returned from the web server each of the vehicles has its information stored in the table. The table is pinged every 10 seconds. So 8 new rows are added every 10 seconds.

I was doing a backup and noticed that the DB had grown from 100mb to approx ~3Gb.

I deleted all rows from the GPS table where the TimeStamp was older than 30 days which took the row count from 4 million down to 2.5 million.

The query to pull historical vehicle data was typically taking about 5 seconds before the purge; after it, it times out.

This is the query that displays dates for which vehicle data is available:

SELECT CAST(TIMESTAMP AS DATE) AS `Vehicle_Date`
FROM `GPSLog`
GROUP BY `Vehicle_Date`
ORDER BY TIMESTAMP DESC

This is the query that is now timing out:

SELECT JSONString, TimeStamp, DATE(TimeStamp) as JDate, ID
FROM BusinessManager.GPSLog
Where IMEI = @ID
  AND DATE(TimeStamp) = @D1

I have tried OPTIMIZE TABLE GPSLog and increasing the Buffer_Pool_Size but neither of these things have resolved the issue.

Any anybody suggests any alternative actions I can take?

The table structure is:

ID (INT 10) PK/AI
IMEI (Big INT 20) 0
JSONString (Json) NULL
TImeStamp (TimeStamp) CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Indexes:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
GPSLog  0   PRIMARY 1   ID  A   2055001             BTREE
GPSLog  1   Vehicle 1   TimeStamp   A   367903          YES BTREE
GPSLog  1   Vehicle 2   IMEI    A   2055252         YES BTREE

Table, Create Table

GPSLog

CREATE TABLE `GPSLog` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IMEI` bigint(20) DEFAULT '0',
  `JSONString` json DEFAULT NULL,
  `TimeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Vehicle` (`TimeStamp`,`IMEI`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5533794 DEFAULT CHARSET=utf8

EXPLAIN

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'GPSLog', NULL, 'ALL', NULL, NULL, NULL, NULL, '2055356', '100.00', NULL


texts:
I have a SQL optimization question.

Table Name: charts

Total Records: +250 million

Columns: timestamp(string), imported(boolean), ....

Indexes:

CREATE INDEX index_charts_on_timestamp ON public.charts
USING btree ("timestamp") WHERE (imported = false)

CREATE UNIQUE INDEX charts_pkey ON public.charts USING btree (id)

QUERY EXPLAIN

SELECT  "charts"."id" FROM
"charts" WHERE
"charts"."imported" = FALSE AND
"charts"."timestamp" = '2018-01-03' ORDER BY
"charts"."id" ASC LIMIT 1000;

OUTPUT

Limit  (cost=1000.59..237865.48 rows=1000 width=8)
 ->  Gather Merge  (cost=1000.59..33262989.10 rows=140426 width=8)
     Workers Planned: 2
 ->  Parallel Index Scan using charts_pkey on charts_pkey  (cost=0.57..33245780.43 rows=58511 width=8)
    Filter: ((NOT imported) AND (("timestamp")::text = '2018-01-03'::text))
(5 rows)

How can i make postgres to use index_charts_on_timestamp instead?

I cannot present EXPLAIN ANALYZE results, the command simply freezes without any results.


texts: MariaDB galera cluster with Mroonga in wrapper mode doesn't synch

texts:
MariaDB Galera Cluster states that the storage engine needs to be InnoDB.

I am using Mroonga on some tables in wrapper mode {which uses InnoDB under the hood as storage engine}.

The problem I am facing is broken synch.

All the standard InnoDB tables get replicated/synched perfectly fine but the tables that are using Mroonga do not get synched for some reason. Any clue what might be the reason?

[EDIT]

Here is the CREATE TABLE SQL

CREATE TABLE `employees` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `interview_memo` text NOT NULL,
  `caution_notes` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx_employees_interview_memo` (`interview_memo`) COMMENT 'tokenizer "TokenMecab"',
  FULLTEXT KEY `idx_employees_caution_notes` (`caution_notes`) COMMENT 'tokenizer "TokenMecab"'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='engine "InnoDB"'


texts: failed logon trigger blocking access into SQL Server instance

texts:
I wanted to create a trigger to record all the names of logins(who logon into system) in ServerLogonRecords table(all the columns are nullable) using LOGON trigger. I get information using eventdata() system function and convert the result into nvarchar type.

 create trigger tr_recorder
 on all server
 for logon
 as
 begin
 declare @var  nvarchar(70)
 set @var = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(70)')
 insert into ServerLogonRecords values(@var)
 end

Now I can't even login using my privileged account because all logins fail.
I want to know why do logins fail (is my trigger doing something wrong?). And any suggestions how to fix this in order to be able to login again into the instance will be very appreciated.

Thanks.


texts: Can SQL Server system tables be defragmented?

texts:
We have several databases in which a large number of tables are created and dropped. From what we can tell, SQL Server does not conduct any internal maintenance on the system base tables, meaning that they can become very fragmented over time and bloated in size. This puts unnecessary pressure on the buffer pool and also negatively impacts the performance of operations such as computing the size of all tables in a database.

Does anyone have suggestions for minimizing fragmentation on these core internal tables? One obvious solution could to avoid creating so many tables (or to create all transient tables in tempdb), but for the purpose of this question let's say that the application does not have that flexibility.

Edit: Further research shows this unanswered question, which looks closely related and indicates that some form of manual maintenance via ALTER INDEX...REORGANIZE may be an option.

Initial research

Metadata about these tables can be viewed in sys.dm_db_partition_stats:

-- The system base table that contains one row for every column in the system
SELECT row_count,
    (reserved_page_count * 8 * 1024.0) / row_count AS bytes_per_row,
    reserved_page_count/128. AS space_mb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.syscolpars')
    AND index_id = 1
-- row_count:       15,600,859
-- bytes_per_row:   278.08
-- space_mb:        4,136

However, sys.dm_db_index_physical_stats does not appear to support viewing the fragmentation of these tables:

-- No fragmentation data is returned by sys.dm_db_index_physical_stats
SELECT *
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('sys.syscolpars'),
    NULL,
    NULL,
    'DETAILED'
)

Ola Hallengren's scripts also contain a parameter to consider defragmentation for is_ms_shipped = 1 objects, but the procedure silently ignores system base tables even with this parameter enabled. Ola clarified that this is the expected behavior; only user tables (not system tables) that are ms_shipped (e.g. msdb.dbo.backupset) are considered.

-- Returns code 0 (successful), but does not do any work for system base tables.
-- Instead of the expected commands to update statistics and reorganize indexes,
-- no commands are generated. The script seems to assume the target tables will
-- appear in sys.tables, but this does not appear to be a valid assumption for
-- system tables like sys.sysrowsets or sys.syscolpars.
DECLARE @result int;
EXEC @result = IndexOptimize @Databases = 'Test',
        @FragmentationLow = 'INDEX_REORGANIZE',
        @FragmentationMedium = 'INDEX_REORGANIZE',
        @FragmentationHigh = 'INDEX_REORGANIZE',
        @PageCountLevel = 0,
        @UpdateStatistics = 'ALL',
        @Indexes = '%Test.sys.sysrowsets.%',
        -- Proc works properly if targeting a non-system table instead
        --@Indexes = '%Test.dbo.Numbers.%',
        @MSShippedObjects = 'Y',
        @Execute = 'N';
PRINT(@result);

Additional requested info

I used an adaptation of Aaron's query below the inspect system table buffer pool usage, and this found that there are tens of GB of system tables in the buffer pool for just one database, with ~80% of that space being free space in some cases.

-- Compute buffer pool usage by system table
SELECT OBJECT_NAME(p.object_id),
    COUNT(b.page_id) pages,
    SUM(b.free_space_in_bytes/8192.0) free_pages
FROM sys.dm_os_buffer_descriptors b
JOIN sys.allocation_units a
    ON a.allocation_unit_id = b.allocation_unit_id
JOIN sys.partitions p
    ON p.partition_id = a.container_id
    AND p.object_id < 1000 -- A loose proxy for system tables
WHERE b.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY pages DESC


texts: How to show distinct column one time and different seperately?

texts:
Here is the key value table

Key  value
one  rose
one  apple
one  Grapes

result showing like this

Key value
one rose
    apple
    Grapes


texts: Replicaiton and synchronizing to a central database

texts:
My client will have many outlets and each outlet will have a local db. But there is only one outlet which will have a central db. Ideally, my client wants to put everything on a cloud but due to the unreliable internet services where my clients operates, they decided to have a local db. Each local db will need push its data to the central db (one way only i.e. from local to the central db).

The database use will be MS SQL Server.

I am looking at Replication feature of the MS SQL and I think it will require Professional and above editions.

I am just wondering if there is other options? Maybe use other third party application to avoid costing or even other technologies?

Thanks in advance.


texts: SQL Server AlwaysOn High Availability Maintenance Plans

texts:
I have a SQL Server AlwaysOn High Availability with Primary & Secondary Database Servers

The Availability Group Backup Preferences is set to Primary.

Maintenance Plan is on Primary and does:

Check Database Integrity - Include Indexes, Physical Only
Backup Databases - Full
Backup Databases - Transaction Logs
Rebuild Indexes - Tables and views
Maintenance Cleanup - Delete files older than 2 weeks

No errors are reported in the plan history but I find that only databases that do not participate in the Availability Group are backed up. There seems to be scant resources on maintenance plans on High Availability environments.

Any advice how I can get maintenance plan to work on the databases in the High Availability group?


texts:
My question is whether it is safe to perform multiple dumps at the same time in mysql, i.e. running:

mysql -u root -p database1 < database1.sql

mysql -u root -p database2 < database2.sql

on the same host and database server of course;

The databases will be empty when the process starts; (just created from cmd)

These are huge dumps if it matters, one file is 10G and the other 100G.


texts: SQL Server recovery model cannot be changed

texts: I need your advice. Is there any way to prevent any user from changing the recovery model? It should throw an error when the user tries to change recovery model of SQL server.


texts: Reading decimal mark comma with an external table

texts:
SQL-server 2016, SQL server management studio (SSMS). Creating external tables with transact-SQL and Polybase. How can we read decimals with a decimal mark, comma instead of dot?

When using float and decimal it fails and throws an error. When using money data type it succeeds, but misread decimals and integers are returned instead.

Revised the file format standard clause, no option to configure such a thing. Collation already set for my region.

Sample data:

2017;4;2017;601PPP;183,63;0
2017;4;2017;601PPP;183,63;0
2017;4;2017;601PPP;183,63;1.000,55
2017;4;2017;601PPP;183,63;2,5
2017;4;2017;601PPP;183,63;7,5
2017;4;2017;601PPP;405,28;17,5

Example of a create table that would fail to read the sample data:

CREATE EXTERNAL TABLE [dbo].[STG_Table] (
    field1           smallint     NULL
   ,field2           tinyint      NULL
   ,field3           smallint     NULL
   ,fieldn_2         varchar(10)  NULL
   ,fieldn_1         money        NULL
   ,fieldn           float        NULL
)
WITH (LOCATION='/STG_Table/',
    DATA_SOURCE = AzureDataSource,
    FILE_FORMAT = FileFormat
);

Updated from comments

Query used to read data:

SET @dynamicSQL = N'SELECT * INTO ##' + @someTable + ' FROM STG_' + @someTable
EXEC sp_executesql @dynamicSQL

Specifying fields implies more cases to be added (working inside a procedure) and it can be avoided by configuring such a thing. I found more simple configuring this, than adding more code. No configuration found in database configuration


texts: Teradata : Disadvantage of Collect stats

texts:
I want to know any disadvantage can occur by doing collect stats on a column that is being widely used in join condition across so many procedures ? (Eg. the column is accessed 300+ times access in 60 days)

While searching in google, i came to know the only issue that can cause performance degradation is out of date Stats.
In this case, this is not going to happen as whenever data of table changes, we are going to run collect stats.

Any other disadvantage is there because of adding collect stats other than space consumption ?


texts: SQL Join Query to missing Some records from first table

texts:
I am creating a database application in SQL Server. I have 2 tables named tblClient (Client Table) and tblExpEntry (Expense Table). I want to select all Clients from tblClient table and show their expense from tblExpEntry. But I am getting records from tblClient only that are non-zero records in tblExpEntry table like I am missing 2 records that have 0 expense. I want to get them also in selected records. I am using the below query for selecting data

select tblClient.ProjectName, ISNULL(SUM(tblExpEntry.TotalPayment), 0)
     AS 'Total Expense'
FROM tblExpEntry
LEFT OUTER JOIN tblClient ON tblClient.Client_ID=tblExpEntry.Client_ID
WHERE tblCLient.StatusID=1
GROUP BY ProjectName


texts:
I would like to run an update as below on a 100 million row table in batches so as to not cause locking/without downtime and not affecting replication.

Update tbl
    set col_new = col
where (do 1 day at a time)
perform pg_sleep(120);

col is timestamp without time_zone, col_new is timestamptz

Can someone help me with the syntax?


texts: Users, transactions, points, payments tables design MYSQL

texts:
I'm designing a system where users may buy points for real money $. And I need a proper MYSQL DB design.
Users spend points on some internal services.
For example if they pay $100 they get 50 points, $200 = 140 points if they pay $1000 they get 1000 points.
After that they create an order (for example) which costs 10 points, the coins balance should be reduced.
My assumption that I need such tables:

Users table with points balance field
Users_payments table, date, with user_id who made a payment, user_id of the account who receives the payment, date, amount, maybe type
Users_orders table, with date, amount of points spent, type of the purchase

Every time user buys a service points balance will be reduced (need a mysql transaction here)
I'm doing something wrong? Users will be able to buy points via a payment processor or funds may be added manually.


texts: Shrink log file after configuring backups properly

texts:
I had a database that was working in FULL recovery mode but never had transaction log backups done.

The consequence of that was the fact that the log file kept growing and now its 10 GB in size.

I configured scheduled backups for this database (full + differential + log) and now log % space used is around 1%.

I want to shrink the 10GB file to a sensible size (maybe 200MB with autogrow 5 MB?).

How can I do it and what are the consequences of shrinking the file?


texts: AWS RDS (PostgreSQL) - Master user lost all privileges after upgrading from v9.6 to v10.6-R1

texts:
I just upgraded my AWS RDS DB instance from Postgresql v9.6 to v10.6-R1. I did this manually using the "Modify" tab in the Databases section under the Dashboard. Immediately afterwards, as the master user, I lost the ability to perform any operations in the public schema.

I can connect to the DB without issue, but am seeing this error when I try running a select statement on tables in the public schema: ERROR:  permission denied for schema public

It appears that my rds_superuser user can't log in anymore (freetiersuperuser is the name of my master user, although its name is misleading):

master_prod=> \du+
                                                                       List of roles
     Role name     |                         Attributes                         |                          Member of                          | Description
-------------------+------------------------------------------------------------+-------------------------------------------------------------+-------------
 freetiersuperuser | Create role, Create DB                                    +| {rds_superuser}                                             |
                   | Password valid until infinity                              |                                                             |
 rds_iam           | Cannot login                                               | {}                                                          |
 rds_password      | Cannot login                                               | {}                                                          |
 rds_replication   | Cannot login                                               | {}                                                          |
 rds_superuser     | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password} |
 rdsadmin          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}                                                          |
                   | Password valid until infinity                              |                                                             |
 rdsrepladmin      | No inheritance, Cannot login, Replication                  | {}                                                          |

Additionally, running ANALYZE VERBOSE; works, and I can statistic information for my public tables.

If it helps, here are my default access privileges

master_prod=> \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

Any ideas as to what went wrong or what I can do to fix this? I appreciate all suggestions.

Thank you


texts: Permissions for mysql 'SHOW CREATE PROCEDURE'?

texts:
I'm working on creating a 'read-only' mysql database. I want the user to be able to see everything, but not be able to modify anything. My user currently has the following grants:

GRANT USAGE ON . TO 'sqlprostudio-ro'@'%' IDENTIFIED BY PASSWORD '*x'
GRANT SELECT, EXECUTE, SHOW VIEW ON northwind.* TO 'sqlprostudio-ro'@'%'

I can see stored procedures, but if I run:

SHOW CREATE PROCEDURE x

The 'Create procedure' column returns null. Is there a way I can allow a read-only user to see the creation procedure without being able to modify it?


texts: Connecting to an 11g Standard Database (Local)

texts:
I've installed 11g Standard on a VM, it's going to be connected to from other Computers/Users. It installs successfully but I'm not really sure how to create my first database on it (using SQL Developer).

I'm trying to connect with SQL developer, but I'm not really sure of the parameters? Should I be able to connect at this stage? What do I enter for Username/Password etc...?

I've seen guides but they are for Express Edition. This is on a Windows 8 64 Bit VM Btw.


texts:
I'm trying to optimize a query on Oracle 10g (I know!), and I have a 4 column table which contains varchar2 values similar to this structure:

CREATE TABLE Settings (
    Client VARCAR2(50)
    , Class VARCHAR2(50)
    , Setting VARCHAR2(50)
    , Setting_Value VARCHAR(20)
)

The query is meant to use static values for Client and Class, these have been hardcoded, while Setting varies in two sections, and Setting_Value should join to different columns in another table, depending on the value of Setting.

I've been setting up the join using this structure:

Edit: It appears there's been confusion. The Setting column effectively points to the column name of the other_table. As such, Settings records act as filters for a given Client/Class operation. As such, adding Setting to the other_table is counterproductive.

WHERE Settings.SettingValue =
    CASE Settings.Setting
        WHEN 'Column1' THEN other_table.Column1
        WHEN 'Column2' THEN other_table.Column2
        WHEN 'Column3' THEN other_table.Column3
        ...

The problem is that I can't get Oracle 10g to hit any indexes on other_table for the values in the CASE statement. Is there a strategy I'm missing or a structure that would work for Oracle 10g to use an index as opposed to a full table scan?


texts: Oracle 10g - How to prevent one user from hogging all resources?

texts: I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run non optimized queries.


texts: InfluxDB - Get series containing given field

texts:
Is it possible to ask Influxdb (1.8 version, using InfluxQL) for all series which contains given fields?
I have database which stores results of some research experiments. Each experiment is stored as separate series, with individual name (stored as measurement), set of tags describing some parameters used during an experiment and several fields with data obtained from an experiment. The thing is, that data from each experiment may be different - each time we decide individually which data will be measured, so each series may contain different set of fields. By querying SHOW TAG KEYS and SHOW FIELD KEYS and removing repeating entries I can quite easily obtain full list of all available tags and fields in my database. Also, by using SELECT my-key FROM some-series I can extract chosen data from chosen experiment. But is there any way to obtain list of all series which contain given field?
To illustrate, lets say my database is as follows
> show measurements
name: measurements
name
----
seriesA
seriesB

> select * from seriesA
name: seriesA
time                tagA  tagB  valueA
----                ----  ----  ------
1611768077318789078 tagA1 tagB1 0
1611768077332632509 tagA1 tagB1 1
1611768077336126260 tagA1 tagB1 2
1611768077340873470 tagA1 tagB1 3

> select * from seriesB
name: seriesB
time                tagA  tagB  valueA valueB
----                ----  ----  ------ ------
1611768077324713234 tagA1 tagB2 0      0
1611768077333928603 tagA1 tagB2 -1     -1
1611768077337841723 tagA1 tagB2 -2     -2

Is there any way to ask for all series which contain valueB and receive seriesB as an answer?


texts:
DECLARE float_one, float_two, my_result NUMERIC(7,2)
my_result = CONVERT(float_one/float_two, DECIMAL(7,2));

In this mysql query, I do this type of operation in a stored procedure, but the Linux environment phpmyadmin throws the following warning:

  Note: #1265 Data truncated for column 'float_one' at row 5

Does anyone have an idea how can I solve this problem?

CREATE TABLE IF NOT EXISTS `float_sample` (
      `id` int(11) NOT NULL auto_increment,
      `first_number` float(10,3) default NULL,
      `second_number` float(10,3) default NULL,
      PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `float_sample` (`id`, `first_number`, `second_number`)
VALUES    (1, 2.900, 1.900),    (2, 3.100, 22.100);

and the procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS float_test$$
CREATE PROCEDURE float_test(IN my_ID INT)

BEGIN
DECLARE first_float, second_float DECIMAL(10,3);

DECLARE done INT DEFAULT 0;

DECLARE myCursor CURSOR FOR
        SELECT `first_number`, `second_number`
        FROM `float_sample`
        WHERE `id` = my_ID;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN myCursor;
my_loop:LOOP
    FETCH myCursor INTO first_float, second_float;

    IF done = 1 THEN
        LEAVE my_loop;
    END IF;

END LOOP my_loop;
CLOSE myCursor;

-- SELECT first_float, second_float;
END;$$
DELIMITER ;

and the result

1 70 10:41:36    CALL mytests.float_test(1) 0 row(s) affected, 2 warning(s):
1265 Data truncated for column 'first_float' at row 2
1265 Data truncated for column 'second_float' at row 2  0.032 sec


texts:
Please advise which Postgres version is better for setting up a replication between a master which resides in one continent and one of its standby server is located in a different continent, and network latency is high.

Thanks.


texts: Is MySQL syntax GROUP BY ... ASC/DESC removed or not?

texts:
I would like to ask if MySQL syntax GROUP BY ... ASC/DESC was removed or not? By this worklog task it should be removed but it seems that it is not truth. There are no details in which version it was applied first time.

By this db-fiddle demo it still works well on any MySQL version (but on 5.7 and 8.0 sql_mode ONLY_FULL_GROUP_BY must be disabled or ANY_VALUE() must be used to avoid error ER_WRONG_FIELD_WITH_GROUP). I know that aim in this demo can be re-written using inner join and subquery but in my use case it is 5 times slower on table with 4.500.000 rows. Demo is simplified. My real use case is much similar to this fiddle.

MySQL doc says that results from GROUP BY, used that way I used, are non-deterministic and I should not rely on it. But in my case result are always identical on any MySQL version (can be tested be switching version in db-fiddle). There ore only two requirements: engine must be InnoDB and I have to create index for columns used in GROUP BY (both requirements are not problem - InnoDB and index is performance advantage). I'm using this solution because it is much faster then any other solution using inner join, subqueries etc.

However syntax is removed or not there is alternative which produces identical result. So GROUP BY col1 ASC/DESC can be rewriten as GROUP BY col1 ORDER BY col1 ASC/DESC. Which one is better to use in my case? Both are identical what about results and performance too.

Thanks for any advice.


texts: Constrain a postgres timestamp to "End of day"?

texts:
Given a table named bananas
and a timestamp without time zone column named end_time
and some psql clients have set timezone to 'UTC'
and other psql clients have set timezone to 'US/Eastern'
and the server config has timezone = 'UTC' in postgresql.conf

How would one write a check constraint on bananas.end_time to ensure that end_time is always the end of the day, defined as the 23rd hour and 59th minute and 59th second of a "US/Eastern" day?

I tried:

alter table bananas
add constraint ck_end_time_is_end_of_day
check (
  23 = date_part('hour', end_time at time zone 'UTC' at time zone 'US/Eastern')
  and 59 = date_part('minute', end_time at time zone 'UTC' at time zone 'US/Eastern')
  and 59 = floor(date_part('second', end_time at time zone 'UTC' at time zone 'US/Eastern'))
)
;

This seems to correctly constrain the column, but it seems horrendously inefficient, and quite unreadable.  Is there a more efficient and/or more readable implementation?


texts: How to put more than 1000 values into an Oracle

texts:
I have a comma delimited list of many IDs(Database size is very huge here). Sometimes this list can exceed 1000 items, at which point Oracle throws an error. The query is similar to this...

select * from table where ID in (1,2,3,4,5,6...,1001,1002,1003...)

The above query throws an error saying that expression limit in Oracle is only 1000. So i tried the below query

select * from table where ID in (1,2,3,4,5,6...,1000) union all select * from table1 where ID in (1001,1002,1003...)

The above query works Good. My problem is i have 1 million ID's which i need to select using the above query. Whether it is possible to frame such as 1000's & 1000's using union all for 1 million. Also for 1 million what will be the performance using oracle 11g.

I am almost checked using INs using OR: But results up in failure. Also i don't have permissions to create a temporary table in my Database. Kindly give your valuable suggestions.

Thanks in Advance.


texts: Restoring a backup to an older version of SQL Server

texts:
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:

Restore failed for Server '...\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The database was backed up on a server running version
10.50.1600.
That version is incompatible with this server, which is running version 10.00.2531.
Either restore the database on a server that supports the backup, or use a backup
that is compatible with this server. (Microsoft.SqlServer.Smo)

Is there a way to get a backup which is compatible with the older (in my case 10.00.2531) version from the newer (in my case 10.50.1600) version of SQL Server Express?


texts: Is it Ok to have one table with two relations?

texts: I have an event table that has two kinds of participants. Each event will have many participants of each type. Is it good form to have a separate table for each participant type, each related to the event table?


texts: Single Row Table for Storing an Additional Unique Identifier

texts:
I just came across a somewhat old database (and frontend) and it has funny way of dealing with the aspect of unique ids.

I've got one table with a single column and row storing an Integer (currently 31448). This number is used on an Invoices table as 'InvoiceNo', the Invoices table also has a unique auto-inc id (currently 2847)

It looks a bit like this:

CREATE TABLE InvoiceNumber(
    LatestId [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE Invoices(
    InvoiceId [int] IDENTITY(1,1) NOT NULL,
    InvoiceNo [int] NOT NULL,
    Amount [decimal](18, 2) NULL
) ON [PRIMARY]
GO

No actual relationship between the two tables other than the InvoiceNo which is not actually an enforced relationship.

The frontend then, when the user adds a new invoice performs the following:

public static void CreateInvoice(Invoice newInvoice)
{
    try
    {
        using (var ctx = new DataContext)
        {
            var lastInvoice = (from lastNumber in ctx.SingleRowTable
                                    select lastNumber).Single;

            var newInvoiceNo = lastInvoice.InvoiceNumber +1; // Int

            lastInvoice.InvoiceNumber = newInvoiceNo;

            newInvoice.InvoiceNo = newInvoiceNo;

            ctx.Invoices.InsertOnSubmit(newInvoice);

            ctx.SubmitChanges;
        }
    }
    catch  (Exception x)
    {
        // Sleep now in the fire
    }
}

So basically when a new invoice is created the frontend requests the number on the InvoiceNo (single row) table and adds one (+1) that number then becomes the new invoice number and also the latest used invoice number.

Other tables in the database (InvoiceItems) reference the InvoiceId not the InvoiceNo.

I supposed the question is: Why would someone use this approach? is it an anti-pattern or design error or are there any real implementation towards it?

My thought was that for some reason the "Real World" invoice number needed to be preserved so they decided that running an additional 'Unique Id' would be the best way to deal with it. I thought maybe the developers couldn't afford a one thousand jump on the invoice number. (?) Maybe.


texts: Find records with same string with extra character

texts:
OK, so I have a Microsoft SQL Server 2014 database table called owner with around 90,000 records with owner information, another called vehicle with vehicle information

Owner_Name                   owner_id       V_name     owner_id    exempt
-------------------------------------       ------------------------------
JACOB JAMISON & JESSICA           35        Civic            35        H3
JACOB JAMISON M & JESSICA B       39        Accord           39        H3
BLACKSON BARRINGTON               56        Bugatti          56        H6
BLACKSON BARRINGTON H             98        SSC              98        H7
BRUSTER MICHAEL                   107       Corvette         107       H9

I'm trying to find all records that have more than one exemption on a vehicle
   ( H0 means no exemption). This code below has worked well, as long as the name is exactly the same. However, if there's a variation, such as an extra letter or if it's entered backwards, then it won't return those records. I've looked at things like SOUNDEX, but this wouldn't work in my scenario.

SELECT Owner_name
     , COUNT(Owner_name) AS 'xNameAppears'
     , COUNT(v.exempt) AS 'ExemptionCount'
FROM owner o
INNER JOIN vehicle V ON V.owner_id = o.owner_id
WHERE v.exempt <> 'H0'
GROUP BY O.owner_name
HAVING COUNT(v.exempt) > 1

Is there a solution that would allow me to return records like so, not knowing which owner_name's may be similar? Basically trying to get the server to search through the owner_name column and if there's a similarity such as
JACOB JAMISON & JESSICA and JACOB JAMISON M & JESSICA B then it'll return those records like so:

Owner_Name                      xNameAppears      ExemptCount
-------------------------------------------------------------
JACOB JAMISON & JESSICA           2                         2
JACOB JAMISON M & JESSICA B       2                         2
BLACKSON BARRINGTON               2                         2
BLACKSON BARRINGTON H             2                         2

Thank you in advance!


texts: Why the sudden increase of my database .mdf file size?

texts:
Suddenly my database .mdf file size is increased to more than 250GB but my total table data size count is 4GB only. I did a data shrink, but there are no results. Then I did the query below:
    SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
       COUNT(*) AS [ReservedPages],
       (COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
        ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;

and it showed this result:

I have a service broker on.
Any suggestion to remove or how to minimize the queue_message table size?

SELECT COUNT(*) FROM sys.conversation_endpoints; from here count is 7 and the query in Dan's answer returns 4 activation_procedure and 4 return NULL.

dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Receiver has 361840332 rows, is_retention_enabled 0, Activation procedure- NULL
dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Sender has 0 row  is_retention_enabled 0 and Activation procedure [dbo].[dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_QueueActivationSender]


texts: Optimization of variables in my.cnf (mysql) for Drupal

texts:
I just get a Dedicated Server running UBUNTU and I need to optimize the variables in my.cnf for Drupal 7.

I understand that this is tailed job. Then Is there any tool to help me to do this? I am a newby at this.

I found this article (which seems a little outdated). It shows some values for a server with 500mb of RAM.

My server has 16Gb of RAM. I guess is not as ease as to multiply those values by 32...?

I use InnoDB tables.

For a setup with 500mb of RAM your my.cnf file may look like this:

[mysqld]
max_connections = 150
max_user_connections = 150
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
innodb-flush-log-at-trx-commit=2


texts: MySQL: Adding innodb_log_file_size in my.cnf causes not starting MySQL service

texts:
I checked and found that InnoDB pluginis active.
By default, Innodb_log_file_size=5MB. As per the MySQL Documentation,
its ideal values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group.

InnoDB_buffer_pool_size=1024MB and innodb_log_files_in_group=2

So, Value of Innodb_log_file_size should be 512MB.
To achieve this, I have written below line in /etc/my.cnf and tried to restart it.

innodb_log_file_size=512M

I am getting below error:
Starting MySQL.Manager of pid-file quit without updating fi[FAILED]

Please note that I have only two log files -one is for slow query log file and another is error log.

Can anyone help me to find why there is a strange behavior and I am
not able to edit Innodb_log_file_size?


texts:
It is stated in the MongoDB documentation FAQ (https://docs.mongodb.com/manual/faq/storage/#wiredtiger-storage-engine) :

  Avoid increasing the WiredTiger internal cache size above its default value.

which should be 50 % ou 60 % of RAM by default

and

  The storage.wiredTiger.engineConfig.cacheSizeGB limits the size of
  the WiredTiger internal cache. The operating system will use the
  available free memory for filesystem cache, which allows the
  compressed MongoDB data files to stay in memory.

I don't understand this statement. Does the WT cache contains the working set in terms of compressed data and index ? If so, do we need to keep extra memory for some kind of memory mapped files for MongoDB data files ?

My db.serverStatus().mem shows this :

{
    "bits" : 64,
    "resident" : 10518,
    "virtual" : 12131,
    "supported" : true,
    "mapped" : 0,
    "mappedWithJournal" : 0
}

So I would guess I've no memory mapped bytes, then I can increase de WT cache ?

Thanks !


texts: Duplicate Customer Records for SQL Query from ODBC with Joins and Group Bys

texts:
Possible Duplicate:
  SQL Query with ODBC, Joins and Group Bys

I am getting duplicate customer records with the query below.  The main purpose of the query is to pull a list of customers into an Access db from ODBC (our customers are stored in DB2 on IBM i). The query is intended to look for a customer (OM01U1.OM01015) that has not had a delivery (RS2101F.DELDATE) or service (EC01_LASMTCDAT) in over 13 months but is not already suppressed (OM01U1.OM01068). I have narrowed it down to the EC01 records (Equipment) that are causing the duplicates. The query is giving results with the last maintenance record for every piece of equipment (EC01_EQUNUM, EC01_EQUACY, and EC01_EQUSPP)  because some customers have several pieces of equipment that could have been maintained at different dates. I just need the most recent maintenance date (EC01_LASMTCDAT) for the outlet (OM01U1.OM01015). Is there a statement I can use to only pull the outlet's last maintenance date (EC01_LASMTCDAT)?

SELECT
OM01U1.OM01041 as Loc,
OM01U1.OM01015 AS Outlet,
OM01U1.OM01945 AS OLTyp,
OM01U1.OM01052 AS Outlet_Name,
OM01U1.OM01054A AS Street_Number,
OM01U1.OM01054C AS Street_Name,
OM01U1.OM01055A AS City,
OM01U1.OM01055B AS State,
OM01U1.OM01106 AS Zip,
OM01U1.OM01058A AS Area_Code,
OM01U1.OM01058C AS Phone,
OM01U1.OM01037 AS Channel,
OM01U1.OM01926 AS USA_Type,
OM01U1.OM01078 AS Key_Acct,
OM01U1.OM01110 AS TRDGRP,
OM01U1.OM01034 AS Trade_Name,
OM01U1.OM01248 AS DTC,
OM01U1.OM01073 AS Sales_Route,
OM01U1.OM01068 AS Sup_CDE,
OM01U1.OM01982 AS Sup_Reason,
OM01U1.OM01067 AS Sup_Date,
OM01U1.OM01065 AS Creation_DT,
OM01U1.OM01066 AS Update_DT,
OM01U1.OM01141 AS CAN_NUM,
OC02_RNTCTCSTA AS OC_CONT_STATUS,
CF30_CNSTYP AS CF_CONTYP,
EC01_SYSSTA AS EC_STATUS,
RS2101F.DELDATE AS RS_LAST_DEL_DT,
EC01_LASMTCDAT AS EC_LAST_MNT_DT,
COUNT (EC01_EQUNUM) AS EC_EQUIP,
COUNT (EC01_EQUACY) AS EC_ACCESSORY,
COUNT (EC01_EQUSPP) AS EC_COMPONENT
FROM OM01U1
JOIN RS2101F ON OM01U1.OM01015 = RS2101F.OUTNUM
LEFT JOIN CF30 ON OM01U1.OM01015 = CF30_OUTNUM
LEFT JOIN EC01 ON OM01U1.OM01015 = EC01_EQUOUTNUM
LEFT JOIN OC02 ON OM01U1.OM01015 = OC02_OUTNUM
GROUP BY EC01_LASMTCDAT,EC01_SYSSTA,EC01_EQUNUM,EC01_EQUSPP,EC01_EQUACY,
CF30_CNSTYP,OM01U1.OM01141,OM01U1.OM01066,OM01U1.OM01065,
OM01U1.OM01067,OM01U1.OM01982,OM01U1.OM01068,OM01U1.OM01073
,OM01U1.OM01248,OM01U1.OM01034,OM01U1.OM01110,OM01U1.OM01078,OM01U1.OM01926,
OM01U1.OM01037,OM01U1.OM01058C,OM01U1.OM01058A,OM01U1.OM01106,OM01U1.OM01055B,
OM01U1.OM01055A,OM01U1.OM01054C,OM01U1.OM01054A,OM01U1.OM01052,OM01U1.OM01945,
OM01U1.OM01015,OM01U1.OM01041,RS2101F.DELDATE,OC02_RNTCTCSTA

HAVING
RS2101F.DELDATE < 1110815
AND EC01_LASMTCDAT < 1110815
AND OM01U1.OM01068<>'S'


texts: Best way to pg_dump PostgreSQL on RDS to S3

texts:
I need to dump my PostgreSQL on RDS to a file on S3 periodically (to use it elsewhere than AWS).

Is there a better way than running a script that runs pg_dump and uploads results to S3 using e.g. boto3?


texts: Schema design for products with multiple variants/attributes?

texts:
I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes.

From all the research I've done this seems the most likely solution for me and I'm just wondering if there's anything wrong with the following schema and what are the upsides/downsides?

Thank you

Table: products
------------------------------
| ID | ProductName           |
|----------------------------|
| 1  | Leather Wallet Case   |
| 2  | Jeans                 |
| 3  | Power Bank            |

Table: products_variants
-------------------------------
| ID | ProductId | ParentId | Variant  | VariantName | SKU  | StockTotal | WholeSalePrice | BuyPrice | OnSale | OnSalePrice |
|---------------------------------------------------------------------------------------------------------------------------|
| 1  | 1         | null     | model    | iPhone5     | SKU  | 10         | 3              | 10       | null   | null        |
|---------------------------------------------------------------------------------------------------------------------------|
| 2  | 1         | null     | model    | iPhone4     | null | null       | null           | null     | null   | null        |
| 3  | 1         | 2        | color    | Red         | SKU  | 10         | 3              | 10       | null   | null        |
| 4  | 1         | 2        | color    | Blue        | SKU  | 10         | 3              | 10       | null   | null        |
|---------------------------------------------------------------------------------------------------------------------------|
| 5  | 2         | null     | size     | M           | null | null       | null           | null     | null   | null        |
| 8  | 2         | 5        | color    | Black       | SKU  | 10         | 3              | 10       | null   | null        |
| 9  | 2         | null     | size     | XXL         | SKU  | 10         | 3              | 10       | null   | null        |
| 10 | 2         | 9        | material | Cotton      | null | null       | null           | null     | null   | null        |
| 11 | 2         | 10       | color    | Red         | SKU  | 10         | 3              | 10       | null   | null        |
| 12 | 2         | 10       | color    | Blue        | SKU  | 10         | 3              | 10       | null   | null        |
| 13 | 2         | 9        | material | Casmir      | null | null       | null           | null     | null   | null        |
| 14 | 2         | 13       | color    | Green       | SKU  | 10         | 3              | 10       | null   | null        |
| 15 | 2         | 13       | color    | Brown       | SKU  | 10         | 3              | 10       | null   | null        |
|---------------------------------------------------------------------------------------------------------------------------|
| 13 | 3         | null     | null     | null        | SKU  | 10         | 3              | 10       | null   | null        |


texts: Insanely slow replication after MHA promotion

texts:
I have a replication setup with 1 master and 3 slaves.  I promoted the current master to one of the slaves using MHA.

That went seemingly flawless, not errors reported, everything marked ok.

Spot checking after the promotion the old master is now replicating and catching up from the new master.

I check back later and replication is falling more and more behind, seemingly not making much progress.

Thins like single row inserts are starting to fill up the slow query log taking as long as 8 - 10 seconds.

Being this was the old master I figured if anything the new master would be slow to service activity not having the buffer pool primed.

Checking the runtime server config differences using pt-config-diff between the two servers are things you would expect:  log file names, server ids, host names, read only mode.

The other two prexisting slaves switched over fine and are not showing such problems.

Disk I/O on the problem slave is not pegged.   The mysqld is showing a lot of activity CPU wise (200-300% in top).

I'm at a loss for what could be causing this.  Anything else to check?  Anything to help it catch up?

UPDATE

Out of frustration, with out making any config changes I simply tried restarting the problematic slave.  That seemed to solve the problem, it started making progress instead of continuing to fall behind.

I guess my question still remains, what would cause that?


texts: Why is the processor % different in Activity Monitor vs Resource Monitor?

texts:
I'm tracking a significant difference between the processor % reported in the Activity Monitor for SQL Server vs the total processor % used in the Resource Monitor.  I'm seeing about a 50% difference between the two with the Activity Monitor always being higher.

System Specs:

SQL Server EE 2008 R2
Windows 2008 R2
Bare metal
96 cores w/no processor affinity set (4 Physical Sockets with 12 cores each w/HyperThreading enabled)
768 GB of memory
Active/Passive cluster
Direct attach storage of SSDs

What would cause such a difference?

Which one should I be looking at?

Update:

I see that resource monitor has it split between different categories however wouldn't the "CPU - Total" graph show all CPU usage on the machine?

The Task Manager Performance tab is showing the same difference between it and Activity Monitor.

Activity Monitor is on a 1 sec refresh.

Could there be an issue with Activity Monitor not seeing that each core is HyperThreaded?  Not sure how as SQL Server sees all 96 cores.

Update 6/28/2018

Even after migrating to AWS and updating the OS to Server 2012 R2 the SQL Server 2008 r2 Activity Monitor is still dramatically off from what Task Manager shows.  The below screen shot is from a load test that was just performed.  There is a visible 20% difference between the two metrics.  Even when Task Manager is reporting the overall CPU just over 55% the SQL Activity Monitor is pegged at 100%.  Metrics from the AWS Cloud watch show the same results as Task Manager so I have two data metrics that correlate with a third that isn't even in the ballpark.

Updated System Specs:
System Specs:

SQL Server EE 2008 R2
Windows 2012 Datacenter R2
i3.16xl - Dedicated Host
64 cores w/no processor affinity set (2 Sockets with 32 cores each)
488 GB of memory
Synchronous Mirroring
ENA Enabled w/EBS Optimization enabled


texts:
I have a query I'm using as the source for an OLE DB source component. When I load the package or try to execute the package, the validation on that component takes a very long time. In fact, I left the package overnight and it hadn't finished validating the next morning.

I've found so many (too many) examples of how to delay validation, work offline, etc., but I don't want to workaround the validation. I want to figure out how to make the validation process faster. Currently, when I run the query in SSMS it returns one row (total) in 20 seconds, so why does validation currently run for many hours without completion?

When I run sp_who2 I see that package is executing a select statement against the db with very high CPUTime and low DiskIO (e.g. 390000/300). Is this the issue? If so, how do I correct this or where do need to go to investigate how to fix this? My package is sitting on the same server that the SQL Serve instance is running on, so I can't imagine connection issues are the cause of the validation slowness.

UPDATE

I'm on a tight deadline, so I will have to stop investigating the root cause of this for now. To workaround this, I replaced the Data Flow task where the OLE DB Source component was using this query with an Execute SQL task. I then created a stored procedure that simply does INSERT INTO TableName SELECT Col1,Col2,ect. FROM ViewName. When I run the package now it all runs in a reasonable amount of time like I would expect it to. I'm not going to make this the answer to the question because this still really bothers me, and I'm losing some built in logging functionality in SSIS to do it this way. If anyone knows the asnwer, please let me know!


texts: How to find SSL root cert that made connection to the database in PostgreSQL?

texts:
When we connect to postgreSQL via ssl-mode=verify-full how will I make sure if the certificate I passed is used while making the connection?

With ssl_is_used(); shows only true or false. Is there any other extension or pg_catalog views that shows the root cert used in making connection to the DB ?


texts: Explanation requested for slow DELETE with SQL Server

texts:
I would like to get some extra insights/reasoning for SQL Server delete behavior. We have a fairly large database of over 1800 GB.

In there are some very shallow tables (only a few integer columns) with many millions of rows. When we delete 10,000s of rows from these shallow tables the delete queries are generally quite fast (at most a handful of seconds).

We also have a table with a field of type image storing images averaging 100 KB. When we delete only a few thousand rows from this table, it takes well over a minute.

Although the difference is clear (much more data size-wise is deleted), I'm am eager to learn more about what happens inside SQL Server. So that I can better understand the latter deletes to be so much slower.

Can anyone please shed some light?


texts: What inefficiency does sharding & partitioning attempt to address?

texts:
I have never implemented sharding or partitioning, but from what I understand, multiple servers are used to distribute large amounts of data.

Is this because of a limited hardware budget or to address end-user latency?

What I mean is that provided an unlimited budget providing for ideal processing, storage, memory, network, etc, would sharding or partitioning be able to still increase performance?


texts:
Has anyone ever successfully created SQL Server AlwaysOn Availability Group in a single subnet in AWS?
We have created a Windows Failover Cluster with two nodes but they are both in the same subnets.  So far everything with the AAG has worked with the exception of a failover.  We have added two different secondary IPs to each node that is assigned to the Availability Group Listener.

When do a failover, the secondary IP keeps trying to come online but it can't because of IP conflict.  After a few minutes then the resource would be up and running.  Is there anyone to speed this up so that it doesn't take so long for the cluster resource to get back online?

We are using Windows 2016 and SQL Server 2016 Enterprise Edition with SP1


texts:
My disk space fills up daily with postgresql logs. One optimzation solution I'm pursuing is that instead of turning off logs, I should overwrite the original log file (i.e. rotate?) once it hits a certain size (say 200MB).

How do I enable such a setting in postgresql.conf? I'm aware that a better solution would be to backup all logs, but for now this is the one I want to pursue.


texts:
Is it possible to enter a date with a zero year?
What would the insert clause look like?

I ask, as an application is doing this, see the dump of the date:

dt_data: 05/01/00
dump(dt_data): Typ=12 Len=7: 100,100,5,1,1,1,1
to_char(dt_data, 'dd/MM/yyyy'): 00/00/0000

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0


texts:
I ask your help on a subject that I do not really master.

I have two tables:
tb_user (NIC_Handle, Pseudo)
tb_user_messages_recap (Auteur, Intervenant)

I have to retrieve the NIC_Handle of each 'Pseudo' that corresponds to either 'Auteur' or 'Intervenant'

Can you help me please ?

<?php try{
$MyPDO=$pdo->query("
SELECT
tb_user_messages_recap.Auteur,
tb_user_messages_recap.Intervenant,
tb_user_messages_recap.Numero,
tb_user_messages_recap.Sujet,
tb_user_messages_recap.Creation,
tb_user_messages_recap.Edition,
tb_user_messages_recap.Etat,
tb_user.NIC_Handle,
tb_user.Pseudo

FROM tb_user_messages_recap

LEFT JOIN tb_user

ON tb_user_messages_recap.Auteur = tb_user.Pseudo

WHERE tb_user_messages_recap.Auteur='".$_SESSION_PSEUDO."' OR
      tb_user_messages_recap.Intervenant='".$_SESSION_PSEUDO."'

ORDER BY Edition DESC
");
    while($data=$MyPDO->fetch(PDO::FETCH_ASSOC))
    {
?>

<?php echo $data["NIC_Handle"]." - ".$data["Auteur"]; ?>
<?php echo $data["NIC_Handle"]." - ".$data["Intervenant"]; ?>
<?php echo $data["NIC_Handle"]." - ".$data["Intervenant"]; ?>

<?php }}catch(PDOException $e){echo "
".$e->getMessage()."
";} ?>

Thank you for your help,

Cordialy


texts: Keep empty string instead of null for /copy command

texts:
When using the \copy command with psql I have a file with empty values. These values are not to be null but instead always an empty string.

My command:

\copy agltransact FROM 'dbo_agltransact.csv' with null as '' CSV HEADER;

The error I get:

ERROR:  null value in column "apar_id" violates not-null constraint

I have this data:

account,amount,apar_id,apar_type
14015,3000000.000,,1
14015,3000000.000,,2

I want the next to last columns to be an empty string.


texts: How do I list available casts in SQL Server?

texts:
In PostgreSQL, there is a command, \dC+ I can use in psql \dC+,

\dC[+]  [PATTERN]      list casts

To lists the possible CASTS. This is useful, because I can easily find out what I can use to cast to timestamp,

\dC+ timestamp
                                             List of casts
         Source type         |         Target type         |  Function   |   Implicit?   | Description
-----------------------------+-----------------------------+-------------+---------------+-------------
 abstime                     | timestamp without time zone | timestamp   | yes           |
 date                        | timestamp without time zone | timestamp   | yes           |
 timestamp without time zone | abstime                     | abstime     | in assignment |
 timestamp without time zone | date                        | date        | in assignment |
 timestamp without time zone | timestamp without time zone | timestamp   | yes           |
 timestamp without time zone | timestamp with time zone    | timestamptz | yes           |
 timestamp without time zone | time without time zone      | time        | in assignment |
 timestamp with time zone    | timestamp without time zone | timestamp   | in assignment |

Is there any command like that in SQL Server? How can I find what I can CAST to DateTime2? Is this documented anywhere?


texts: Rolled back transactions during SQL Server Mirroring Failover

texts:
I have two SQL Server 2014 instances setup with High Safety Mirroring for a production environment. I need to apply security patches to these servers on a regular basis which requires a reboot.

My typical process is applying patches to mirror and rebooting. Then triggering a failover to mirror and applying patches to the other server. Rebooting, then failing back.

My issue is, every failover has some uncommitted transactions rolled back and this is really not good for a production environment. I have no idea which transactions were rolled back and what kind of data loss I'm having. But I need to reboot these servers and apply patches. If I have zero data loss tolerance, what are my options?


texts: How to make SQL Server 2008 compatible to SQL Server 2000

texts:
I have to run the backup of SQL Server 2000 in SQL Server 2008.

While restoring the database from the .bak file, I got the error

  specified cast is invalid

After doing google I feel there is compatibility issue. Therefore I want to make the database compatibile to SQL Server 2000.

And run the below query

ALTER DATABASE  DBNAME
SET COMPATIBILITY_LEVEL = 80

but nothing help. Any help will be appreciated.
The complete error message is :

Specified cast is not valid. (SqlManagerUI)

------------------------------
Program Location:

  at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()

   at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()

   at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)

   at System.Windows.Forms.Control.OnTextChanged(EventArgs e)

   at System.Windows.Forms.TextBoxBase.OnTextChanged(EventArgs e)

   at System.Windows.Forms.Control.set_Text(String value)

   at System.Windows.Forms.TextBoxBase.set_Text(String value)

   at System.Windows.Forms.TextBox.set_Text(String value)

   at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.bu
ttonSelectDevice_Click(Object sender, EventArgs e)

   at System.Windows.Forms.Control.OnClick(EventArgs e)

   at System.Windows.Forms.Button.OnClick(EventArgs e)

   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button,Int32 clicks)

   at System.Windows.Forms.Control.WndProc(Message& m)

   at System.Windows.Forms.ButtonBase.WndProc(Message& m)

   at System.Windows.Forms.Button.WndProc(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
<IntPtr wparam, IntPtr lparam)


texts: How can I pass a list of databases to sp_MSforeachdb?

texts:
I have the following script that when run gives me a comma delimited list of database names.

        SET NOCOUNT ON
        declare @db_list NVARCHAR(MAX)
        DECLARE @command varchar(1000)

        SELECT @COMMAND = 'SELECT DB_NAME()'

         SELECT @db_list  = STUFF((
                                    SELECT ', ' + name FROM sys.databases
                                    WHERE name like '__Order'
                                    FOR XML PATH(''),
                                    TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

         -- in case there are no DBs to show, then show no DB '***'
         select @db_list = COALESCE(@db_list,'***')

         SELECT @db_list = RTRIM(LTRIM(REPLACE(@DB_LIST,',', ',''')))

         select @db_list

this gives me the following result:

UKOrder,' ATOrder,' AUOrder

1) this is giving the wrong result set, I would like it to be

'UKOrder','ATOrder','AUOrder'

2) how can I pass @db_list to sp_MSforeachdb?

suppose I would like to run

  select db_name()

inside each database in @db_list

UKOrder

ATOrder

AUOrder


texts: How to grant SYSDBA access to users on Oracle Cloud v19c

texts: It seems that you can't grant SYSDBA access to users on oracle cloud. I am using v19c, when trying to alter roles through the web "Database Actions" interface, the only roles I see are; CDB_DBA DBA LBAC_DBA PDB_DBA. How can I make a user SYSDBA?


texts: On a MySQL 5.6 server, the mysql.user table contains an user with an empty hostname ('jdoe'@''). What does that mean?


texts: MYSQL Multiple table optimization for advanced search

texts:
I have a users table where there is the basic information of each user:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `account_type` varchar(10) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(500) NOT NULL,
  `registration_date` timestamp NULL DEFAULT NULL,
  `account_status` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

On the other hand, I have a `user_data' table in which we put every other data:

CREATE TABLE `user_data` (
  `data_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `value` text,
  PRIMARY KEY (`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Basically, the data is stored as a name => value

I fetch the data in the following fashion:

SELECT email, ud_desc.value as description, ud_lang.value as language
    FROM users
        JOIN user_data as ud_desc
        ON ud_desc.user_id = users.user_id
            AND ud_desc.name = 'description'
        JOIN user_data as ud_lang
        ON ud_lang.user_id = users.user_id
            AND ud_lang.name = 'language'

I made that second table this way because there is a ton of different data for each user, certain depending on their users.account_type.
Also my client keeps adding, removing and changing different kinds of data all the time.
So I needed something quite flexible and this is the best idea I came up with so far, from a developer point of view. Because I fear that this so called "best idea" is just a faint dream and will be too slow on the long term.

Knowing that I might have to perform LIKE and MATCH AGAINST queries on the large volume of data that user_data will hold.

Also, you might take note that I am not very familiar with indexes. But at this point, I have no idea what is the most efficient, given that the data is fetch based on two columns user_data.user_id and user_data.name.

Should I make both of those columns (user_id and name) indexes? Should user_id be a foreign key (for the users table JOIN)?

Is there a way to improve either my database model or my queries?

I thank you all in advance. I am a bit lost at the moment and need some fresh points of view on this matter. Don't hesitate to tell me if you need more details.


texts: calculate percent change between two entire rows

texts:
i have a table of currency data, it's have multiple records in each rows.

| Date           | ETH  | XRP | LTC | USDT | BTC  | etc... |
|----------------|------|-----|-----|------|------|--------|
| Today          | 3111 | 0.3 | 100 | 1    | 3500 |        |
| Tomorrow       | 3211 | 0.6 | 90  | 1.14 | 3540 |        |
| After Tomorrow | 3215 | 0.8 | 122 |   1  |      |        |

Date column is timestamp.

the output sample data is:

| Date             | ETH  | XRP | LTC | USDT | BTC  | etc... |
|------------------|------|-----|-----|------|------|--------|
| Past Day ->Today | 10%  | 1%  |-10% | 0    | 2%   |        |

I want to know how to find the percent change from day to day for all column that have price(ETH,XRP,LTC,USD,...) without execute multi query request like this :

SELECT t.*,
 BTC - (SELECT BTC FROM PRICE prev WHERE prev.date < t.date ORDER BY date DESC LIMIT 1) AS percent


texts: Postgres reported table / db size varies wildly from dump size

texts:
I have a very strange misreporting of the size of one specific database.

According to \l+ in psql the DB size is 292 MB.

Doing a sum over the sizes reported by the following statement also reports very close to 292 MB.

SELECT
    table_name,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

Yet a pg_dump of this database produces a sql file of 2.02 GB

I suspect that both counting methods skip LOBs since there are two tables in this database that contain TEXT fields with largish content (up to ~4MB per row).

Is there a way to include LOBs in the size calculation?

EDIT:
It gets even stranger:

This query:

SELECT SUM(LENGTH(text_column))
FROM some_table

Gives a result of 2,091,245,318 (i.e. 2.02 GiB) which is about the size of the dump.


texts: SequelizeDatabaseError: cannot execute UPDATE in a read-only transaction

texts:
I'm currently using Sequelize, NodeJS and PostgreSQL. I have users who update their accounts password on first login. Well, today I had this error appear after 3-4 successful logins.

What does this even mean? I did nothing out of the ordinary. A google search does not help me at all as they dont seem to have my problem.

This error appeared on this query:

UPDATE "Users"
SET "password"='-hash-',
"verificationCode"=0,
"startedOn"='2018-04-03 20:45:31.801 +00:00',
"emailVerified"=true,
"updatedAt"='2018-04-03 20:45:31.802 +00:00'
WHERE "userId" = 84

Any help?


texts: SQL Server 2016 Temporal tables metadata

texts:
Is there any way to get metadata (information scheme) information about temporal table feature for the table, like:

Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF
What name of history table
Is SYSTEM_VERSIONING turned for the table


texts:
I've just been asked whether our company should consider Data Virtualization for our test environments.  The benefits are given as:

Screening of sensitive data
Fast data refreshes in our test environments
Potential benefits for DR and BI scenarios

However I've only found marketing info; nothing technical.  From what I can figure out there are 2 approaches:

A service layer over a production database which abstracts you from the data model (presumably resulting in a different data model presented by that new layer).
A tool to automate the restore and subsequent manipulation of data which can be used by non-technical users and is faster than using database backups and SQL scripts.

Without seeing any technical information this smells of snake oil to me; but I want to understand it rather than dismissing out of hand.

Keywords:
[data-as-a-service]
[data-virtualisation]
[data-virtualization]
[delphix]
[denodo]


texts:
I have a mysql multi select query that performs inner left joins. These queries UNION with two other tables. The resultset is huge (250k+ rows). My client wishes to expose this resultset to a webpage. The infinite scroll methodology used on the webpage calls the above query with an offset limit - 50 rows per call. The time taken to retrieve the above dataset is 7+secs.
Can anyone offer any advice as to how this delay could be reduced?

SELECT 'address_messages_unique' as db,`block_height`,`block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `address_messages_unique`.`id`, `tags`, CONVERT(UNHEX(`address_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`address_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `address_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
UNION
SELECT 'coinbase_messages_unique' as db,`block_height`,`block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `coinbase_messages_unique`.`id`, `tags`,CONVERT(UNHEX(`coinbase_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`coinbase_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `coinbase_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
UNION
SELECT 'op_return_address_messages_unique' as db, `block_height`, `block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `op_return_address_messages_unique`.`id`, `tags`,CONVERT(UNHEX(`op_return_address_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`op_return_address_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `op_return_address_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
order by `block_height`
limit 0, 50;

The resultset (which essentailly is the output of the entire db needs to viewed page by page


texts: Using pg_dump and psql to transfer table with timestamp into another Db

texts:
I am using the following pg_dump and psql command lines through the terminal in order to transfer my table from one database to another.

pg_dump -U postgres -t public.table1 database1 | psql -U postgres -d database2

But the problem is that the table1 from the database1 is getting pasted into database2 with the same name, i.e. table1. I want to add the timestamp also into the name, something like table1_18_11_2014_20_45_30, in order to keep a track on when the transfer operation was taken place (in this case, at 20:45:30 hours on 18th Nov 2014). Any idea how to do this? Thx.

Cheers!


texts: Cannot add standby database to the data broker configuration in oracle 11g

texts:
I have duplicated the primary database to standby database and then I added the primary database in data broker configuration. But when I'm trying to add standby database I'm getting following error:

DGMGRL> add database 'orcl_standby' as connect identifier is orcl_standby
maintained as physical;
Error: ORA-16606: unable to find property ""
Error: ORA-16554: translation not valid

Failed.

Is this a problem with log_archive_dest_state_2? I have enable it.

Or is there any problem in DR2ORCL.DAT files?

Any help will be appreciated.


texts: 3 way junction table and redundant value


texts: are there significant differences with 500+ million row tables in Oracle?

texts:
I am in a database designer in a data warehouse environment.  I am used to dealing with tables with a maximum of 1 millions rows and am now faced with tables with more than half a billion rows.  Are there any significant differences with the tools in the "efficiency toolbox"?  Can I trust my previous knowledge of indexes, partitions, and the like or are some of these specific tools more of a hindrance than a help with such large data?  Any other tips for dealing with the tables?

(Already found a great post on Updating 700 million rows to same value)


texts:
Does pg_stat_statements use the same stats_temp_directory directory? Our stats_temp_directory is under /var/run/postgresql which resides in tmps. We have about 500 databases in an instance and set pg_stat_statements.max to 10000000 (20000 statements / db).

We don't want pg_stat_statements to be a major factor of i/o (we execute tons of short running queries). Can't seems to find this info in the pg_stat_statements.

postgres=# select version();
                                                                     version
-------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Ubuntu 9.6.10-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
(1 row)


texts: DB and operating system packages updates

texts: is it ok to apply security updates to the packages of the operating system that hosts an Oracle Database? Or is it better to not touch them, since the system is perfectly working? I have an Oracle Database installed on a Red Hat Enterprise Linux 6, on which some packages have security updates available, and I am not sure about what to do.


texts: Best idea to deal with historic data ( Partitioning? )

texts:
We have a database with almost 1 billion rows.

90% of this table is historical data. We don't use it, BUT if we need to use it, it needs to be right here, right now (we work with banks, so, they need things pretty fast).

I'm reading about partitioning in Brent Ozar blogs etc. but I don't know if this is a good idea.

I would like to partition this table but I'm concerned about backups. If doing backups of the database, without backing up this partition, am I going to have a corrupt database in case of a restore, right?

Am I able to restore only the database, without the partitioned file?

I'm thinking about partitioning the table, throw this file created in a different disk, and just do a backup of the database without this file.

I really don't think this is a good idea, so I would like to hear about some experiences with this.


texts:
I've created a function in PostgreSQL 11.10 to handle DDL change in pglogical to apply it into subscriber. Here is the function:
CREATE OR REPLACE FUNCTION public.intercept_ddl()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
  declare _qry text;
BEGIN
  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
            SELECT current_query() into _qry;
            PERFORM pglogical.replicate_ddl_command('_qry', '{default}'), _qry;

 end if;
END;
$function$
;

When I tried it, it threw an error:

ERROR:  syntax error at or near "_qry"
LINE 1: SELECT pglogical.replicate_ddl_command('_qry', '{default}')
        ^
QUERY:  SELECT pglogical.replicate_ddl_command('_qry', '{default}')
CONTEXT:  during execution of queued SQL statement: _qry
PL/pgSQL function intercept_ddl() line 6 at PERFORM

How to pass _qry variable so we can use it replicate_ddl_command()?

After fixing single quotes as instructed by Laurenz, I tried again with the command:
alter table test alter COLUMN description type text;

And got another error message:

ERROR: cannot drop active portal "pglogical"
CONTEXT: during execution of queued SQL statement: alter table test alter COLUMN description type text; SQL statement "SELECT pglogical.replicate_ddl_command( _qry, '{default}')" PL/pgSQL function intercept_ddl() line 7 at PERFORM


texts: How to troubleshoot poor trigram (gin) index performance?

texts:
I have a compound LIKE in a query that would have seemed to have benefited from a gin index however on two practically identical servers I see vastly different performance, with the worst case being almost as slow as a Seq Scan:

Bad:

EXPLAIN (ANALYZE, BUFFERS) SELECT nodes.node_path FROM nodes WHERE nodes.node_path LIKE 'company%/path%';
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nodes  (cost=2114.03..17149.31 rows=3875 width=42) (actual time=11000.379..11036.533 rows=52 loops=1)
   Recheck Cond: ((node_path)::text ~~ 'company%/path%'::text)
   Rows Removed by Index Recheck: 46597
   Heap Blocks: exact=27913
   Buffers: shared hit=73481
   ->  Bitmap Index Scan on nodes_node_path_gin_idx  (cost=0.00..2113.06 rows=3875 width=0) (actual time=10992.576..10992.576 rows=46649 loops=1)
         Index Cond: ((node_path)::text ~~ 'company%/path%'::text)
         Buffers: shared hit=45568
 Planning time: 0.245 ms
 Execution time: 11036.769 ms
(10 rows)

Good:

EXPLAIN (ANALYZE, BUFFERS) SELECT nodes.node_path FROM nodes WHERE nodes.node_path LIKE 'company%/path%';
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nodes  (cost=2528.05..20571.91 rows=4651 width=43) (actual time=1329.024..1354.603 rows=56 loops=1)
   Recheck Cond: ((node_path)::text ~~ 'company%/path%'::text)
   Rows Removed by Index Recheck: 48704
   Heap Blocks: exact=16106
   Buffers: shared hit=126080
   ->  Bitmap Index Scan on nodes_node_path_gin_idx  (cost=0.00..2526.89 rows=4651 width=0) (actual time=1324.801..1324.802 rows=48760 loops=1)
         Index Cond: ((node_path)::text ~~ 'company%/path%'::text)
         Buffers: shared hit=109974
 Planning time: 0.237 ms
 Execution time: 1354.748 ms
(10 rows)

For index:

CREATE INDEX nodes_node_path_gin_idx ON public.nodes USING gin (node_path gin_trgm_ops)

Where node_path stores slash delimited file paths.

These hosts otherwise exhibit very similar performance, and there's seemingly no disk I/O or other factors obviously different between these executions.

I enabled track_io_timing, I/O Timings is not emitted, and BUFFERS indicates Buffers: shared hit=43783. I dumped this column to a local 9.6.16 instance and it runs in milliseconds, but on the RDS Postgres 9.6.11 instance it's always 10 seconds:

EXPLAIN (ANALYZE, BUFFERS) SELECT nodes.node_path FROM nodes WHERE nodes.node_path LIKE 'company%/path%';
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on nodes  (cost=403.14..15246.51 rows=4018 width=42) (actual time=76.402..76.421 rows=52 loops=1)
   Recheck Cond: ((node_path)::text ~~ 'company%/path%'::text)
   Rows Removed by Index Recheck: 48576
   Heap Blocks: exact=506
   Buffers: shared hit=8530
   ->  Bitmap Index Scan on nodes_node_path_gin_idx  (cost=0.00..402.13 rows=4018 width=0) (actual time=71.494..71.494 rows=48628 loops=1)
         Index Cond: ((node_path)::text ~~ 'company%/path%'::text)
         Buffers: shared hit=8024
 Planning time: 0.188 ms
 Execution time: 76.452 ms
(10 rows)

How do I get information on where the cost is for the Bitmap Index Scan itself - I assume it's something specific to the implementation of the ~~ operator for the gin index type, but is there additional instrumentation I can enable to understand the unexplained time?


texts: Long Running Transactions with Active Status

texts:
I could see long running transactions in my Innodb Status, I'm using RDS Aurora Mysql 5.6 Version.

---TRANSACTION 977075315, ACTIVE 70113 sec
2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1630
MySQL thread id 72467, OS thread handle 0x2ba042a41700, query id 8257176 10.105.86.37 administrator cleaned up
Trx read view will not see trx with id >= 977075316, sees < 977054983
---TRANSACTION 977073376, ACTIVE 70210 sec
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1724
MySQL thread id 72463, OS thread handle 0x2ba041e8a700, query id 8263196 10.105.86.37 administrator delayed send ok done
Trx read view will not see trx with id >= 977073377, sees < 977054983
---TRANSACTION 977064809, ACTIVE 70654 sec
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1360
MySQL thread id 72464, OS thread handle 0x2ba042a41700, query id 8263033 10.105.86.37 administrator delayed send ok done
Trx read view will not see trx with id >= 977064810, sees < 977054983
---TRANSACTION 977061227, ACTIVE 70848 sec
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1705
MySQL thread id 72695, OS thread handle 0x2ba041e49700, query id 8262830 10.105.86.103 administrator cleaned up
Trx read view will not see trx with id >= 977061228, sees < 977054983
---TRANSACTION 977054983, ACTIVE 71140 sec
4 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1599
MySQL thread id 72693, OS thread handle 0x2ba03f7c1700, query id 8262150 10.105.86.103 administrator cleaned up
Trx read view will not see trx with id >= 977054984, sees < 977054977

I'm not using any Explicit transactions.(like begin,commit..)

And I could see from performance_schema.events_statements_history_long that currently executing queries are using the same transactions that are active for long time.

Why is Mysql not closing these transactions and using same transactions for Other Queries ??

Here one of the above transaction even has some row locks, which leads to lock wait time out exception.

I don't want to kill those threads, I want to fix the rootcause of this problem

Kindly help me out with this Issue.


texts: Output a table as well-formed, indented JSON

texts:
I have a table dictionary and its structure is:

create table dictionary
(
  id integer,
  first_level_key character varying(50),
  key_name character varying(50),
  key_value_en character varying(1000),
  key_value_fr character varying(1000)
);

I want to output this table as well-formed indented JSON.

I have tried the following query:

COPY (select
   (select row_to_json(_) from (select c.first_level_key) as _) as first_last,
   c.key_name,c.key_name_value_en
from
   dictionary as c)
 TO '/tmp/label_en.json';

I got JSON output, but not in the correct format, as shown below.

{"first_level_key":"topheader"} screen  Screen Reader Access
{"first_level_key":"header"}    title
{"first_level_key":"header"}    subtitle
{"first_level_key":"nav"}   about   About

The output that I want is well-formed, indented JSON as shown below!

{
"topheader":{

        "screen":"Screen Reader Access"
        },
        "header":{
        "title":"title",
        "subtitle":"subtitletest"
        },
    "nav":{
        "about":"About"
}}


texts: How to find the erroneous code failing the SSDT publish?

texts:
Every time something fails the publish, assuming it passes the preview generation, the error line and executed script sample are completely useless at helping me trace the source of the error, since the line number is relative to the last GO statement, and the executed script doesn't always appear in the publish script.

In this instance, it fails with this error:

The executed script doesn't exist in the publish script:

And this is where it navigates in the publish script upon double clicking the error message:

Usually I am able to trace it down based on the error information, but this time I'm at a loss because the only relevant keywords I can think of are so common that it seems hopeless.

It looks like logging is hackish and the errors sometime come before the postdeployment scripting (e.g. table triggers executing on incomplete data since publish settings only include an option to disable DDL triggers).

I've tried running SQL Server Profiler during the publish but that tool omits even more crucial debugging information (and gets overwhelmed by my large database project). Perhaps another reason to upgrade to get the Extended Events Profiler?

(I verified this statement was not the culprit)


texts: I run SQL Server 2000. Database is in standby mode. I have multiple files with MyDb.tuf name on a different drives. How may I know which one is in use?


texts:
Our environment has a full daily DB backup system, including the control file at 11 pm.

We had a case to restore the database in different environments but with maximum data to recover. Archivelogs are available on disk until the next day 5pm.

But, if we restore using the previous backup control file, it will restore until the last SCN, which is the backup time. So that, it won't apply the created archivelogs on disk after the backup untila the next day 5 pm.

How can we confirm that the DB has applied those archivelogs? Please advise me the way even if I have to apply something manually.

I believe that building DR have such circumstances by applying the differences in archivelogs after mounting, which is fine if we had to follow similar scenario. Thanks.