texts:
CHECKCATALOG is failing on a database that originated in SQL Server 2000. I am getting errors of the type:

  Msg 3853, Level 16, State 1, Line 1
  Attribute (object_id=489156888) of row (object_id=489156888,column_id=1) in sys.columns does not have a matching row (object_id=489156888) in sys.objects.

There are 146 columns in sys.columns that do not exist in sys.objects. This represents 21 tables. If I could easily delete these rows from the sys.columns view I would (blasphemy I know). CHECKDB had not been ran before so these errors went undetected and all the backups will contain the same error.

My plan is to export the existing tables into a new database. But the db is 170GB so this is not trivial.

My question is this: can I just reuse my existing .mdf/.ldf files? Since the system tables are stored in the master database, I could create a [new_work] database and attach the .mdf/.ldf files from the [orig_work] database. Then when I drop the corrupt [orig_work] db the bad entries would be deleted from the system table.

Would this work to fix my error?


texts: Does a Postgres VACUUM free up space for any database object?

texts:
If I do a VACUUM on a Postgres table (without the FULL option) I know that it should make any free-space available to the database, but not the operating system.

Should it make the freespace available to any database object? or just the table that's been vacuumed?


texts: How to insert into table multiple geometry rows from function JSON param?

texts:
My table:

CREATE TABLE Points
(
    id integer NOT NULL DEFAULT nextval('point_id_seq'::regclass),
    info character varying(20) NOT NULL,
    my_point geometry(Point,4326),
    CONSTRAINT points_pkey PRIMARY KEY (id)
)

JSON text:

{
  "points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}
  ]
}

My function:

CREATE OR REPLACE FUNCTION add_multiple_geom ( multiple_geom json )
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$

DECLARE success numeric default 0;

BEGIN
    -- this insert only 1 row...
    INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point(lng,lat),4326), info;

  RETURN success;
END;

$BODY$;


texts: Columnstore Index in read_only filegroup prevents CheckDB

texts:
It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any filegroup in the database, including read-write secondaries and [PRIMARY]), the below error is returned...

Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts.
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Is there a supported method for having columnstore data in a read-only filegroup? Or am I precluded from integrity checks in this scenario?

Repro

create database check_fg_ro
go
use check_fg_ro
go
exec sp_changedbowner 'sa';
go
alter database check_fg_ro add filegroup check_fg_ro_2;
alter database check_fg_ro
    add file (
         name='check_fg_ro_2'
        ,filename='C:\check_fg_ro_2.ndf'
    ) to filegroup check_fg_ro_2;
go
create table foo (
    i int not null primary key
) on check_fg_ro_2;
go
create columnstore index ccix_foo on foo(i);
go
use master
go
alter database check_fg_ro modify filegroup check_fg_ro_2 read_only;
go
dbcc checkdb( check_fg_ro ) with no_infomsgs, all_errormsgs, extended_logical_checks;
/*
Msg 8921, Level 16, State 1, Line 24
Check terminated. A failure was detected while collecting facts.
Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
*/
go

Disclaimer: cross-posted to technet forums


texts: MySQL best way to define a couple of columns as both null or both not-null

texts:
On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns are not null. Is there any specific constraint, or could I do it just via BEFORE INSERT / BEFORE UPDATE triggers?
In other words, if a column of a record is null and it is updated setting it with a value, also the other column of that record must be updated with a not-null value. If the column is not null already, I can update it with another value (optionally, the other one too), but if I set it as null, the other column too must be set as null by the update query (no "default" settings).
Thank you.


texts: Azure blob storage container vs VM managed disk for SQL on Azure VM database files

texts:
Are there benefits specific to SQL Server on Azure VM when using the SQL Server Data Files in Windows Azure feature? Aka using Azure blob storage container for database files versus an Azure VM data disk which itself is backed by Azure storage page blob?

The MS docs SQL Server data files in Microsoft Azure encourage the use of this feature with SQL on Azure VM but don't make clear a compelling advantage compared to using an Azure managed data disk.

The listed benefits apply to both forms of Azure storage AFAIK.

Are there any benefits specific to SQL on Azure VM?

  Why use SQL Server data files in Microsoft Azure?

  Easy and fast migration benefits
  Cost and limitless storage benefits
  High availability and disaster recovery benefits
  Security benefits
  Snapshot backup

Per MS docs SQL Server data files in Microsoft Azure > Concepts and Requirements

  This new feature uses Page blobs, which are more efficient when ranges
  of bytes in a file are modified frequently.

Per MS docs, Introduction to Azure managed disks

  Azure managed disks are stored as page blobs, which are a random IO
  storage object in Azure. We call a managed disk ‘managed’ because it
  is an abstraction over page blobs, blob containers, and Azure storage
  accounts.


texts: How do I get Postgres DO $$ to work from the bash prompt?

texts:
Postgres 9.6.9 on Linux RHEL 6.10

If I surround my DO command in double-quotes and the roles in single quotes, then bash interprets the $$, and that obviously makes the command fail.  So I switched the double- and single-quotes, but now it treats the NOT IN string literals as column names, which also throws an error.

So, as mentioned in the title, what's the magic sauce for getting a DO $$ command to work from the command line?

$ export PGHOST=10.x.y.z
$ export PGUSER=postgres
$ psql -c 'do $$
>          declare rolename text;
>          begin
>            for rolename in select rolname
>                            from pg_roles
>                            where rolname not in ("postgres",
>                                                  "pg_signal_backend",
>                                                  "TAP")
>            loop
>            execute "DROP ROLE " || rolename;
>            end loop;
>          end $$
>          ;'
ERROR:  column "postgres" does not exist
LINE 3:                            where rolname not in ("postgres",...
                                                         ^
QUERY:  select rolname
                           from pg_roles
                           where rolname not in ("postgres", "pg_signal_backend", "TAP")
CONTEXT:  PL/pgSQL function inline_code_block line 4 at FOR over SELECT rows

Thanks


texts: Report Builder Data Sources Do Not Change in Catalog Table

texts: I am trying to extract the connection string for MS SSRS reports from the Report Server Catalog table. This is straightforward enough but if I change the Data Source in Report Builder, the change is not propagated to the Data Source definition on the Catalog table. Is the report definition stored elsewhere?


texts:
Is it better to index each column individually with it's own key or to index multiple columns under the primary key?

I've seen it done both ways in many different applications and I don't see a clear difference.


texts: MySQL Group Replication - Peer address is not valid

texts:
I have been trying to set up an MySQL Innodb Cluster, initially with two databases, both MySQL 8.0.19.

But I keep getting the same errors.

On the first one (username@1.2.3.4:3306), I have created the cluster, and that seems to be ok.

then I run:

cluster.addInstance('username@1.2.3.5:3306')

and get:

ERROR: Unable to start Group Replication for instance 'dbname1:3306'. Please check the MySQL server error log for more information.
Cluster.addInstance: Group Replication failed to start: MySQL Error 3096 (HY000): dbname1:3306: The START GROUP_REPLICATION command failed as there was an error when initializing the group communication layer. (RuntimeError)

From the errorlog on the second DB I get:

[Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Peer address "dbname:33061" is not valid.'
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] None of the provided peer address is valid.'
[ERROR] [MY-011674] [Repl] Plugin group_replication reported: 'Unable to initialize the group communication engine'
[ERROR] [MY-011637] [Repl] Plugin group_replication reported: 'Error on group communication engine initialization'
[Note] [MY-011649] [Repl] Plugin group_replication reported: 'Requesting to leave the group despite of not being a member'
[ERROR] [MY-011718] [Repl] Plugin group_replication reported: 'Error calling group communication interfaces while trying to leave the group'

My configuration thus far (from DB1) is:

group_replication_ip_whitelist="1.2.3.4,1.2.3.5"
 group_replication_local_address="1.2.3.4:3306"
 group_replication_group_seeds="1.2.3.4:3306,1.2.3.5:3306"

I thought maybe it was an issue with port 33061, so I tried using port 33061 for MySQL's port, and 3306 for the Cluster CommunicationPort with
cluster.addInstance('username@1.2.3.5:33061',{localAddress:"dbname:3306"})

But got the same errors.

Have I missed something obvious?


texts: Would it be worthwhile to use the PostGIS extension for custom planets?

texts:
I need to be able to store latitude and longitude in several different tables. My understanding is that PostGIS has a geography column type that does this.

However, I'm not storing coordinates on Earth, but those on fictional planets (for a game). I'm not a cartographer or any other sort of expert on this thing, knowing only what I'd learned in elementary/high school (90°S to 0° to 90°N for latitude, 180°E to 0° to 180°W for longitude).

Will the PostGIS extension be overkill for something I could manage as a composite type myself? Would I be able to use any of the additional functionality, for instance to be able to determine the distance between two points on the same planet, given that I assume PostGIS makes assumptions about those coordinates being located on our own planet?

Is PostGIS suitable for such uses as mine?


texts: Executing sql linked server procedures through python

texts:
I am getting an error when trying to execute SQL Server system procedures such as sp_addLinkedServer and sp_dropserver etc from python script
      USE [master]

      EXEC master.dbo.sp_dropserver @server=N'DS', @droplogins='bs'

Error :
          (15002, b"The procedure 'sys.sp_dropserver' cannot be executed within a transaction.DB-Lib
          error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL
          Server\n")


texts: Aws RDS cpu Spike to 100% because of running set isolation command and set

texts:
My application running with the AWS RDS as a backend.
currently i am doing a performance test.
While doing that i am seeing RDS cpu usage goes 100% after 20min.
While seeing the performance insights of the RDS i am seeing the below metrics,

and which shows setting the transaction isolation level are the culprit.
I have set the auto commit is false in my application.
Please let me know why it causing these much spike. Do i need to configure any other stuffs to reduce this.


texts: PostgreSQL: a Login Role to only access Views

texts:
I need to write a SQL file which must do the following:

create a role called 'selection'
grant connect/usage to the current database
grant select/execute to all views of all schemas (or at least on the current)

I tried a massive amount of GRANT...ON...TO combinations but I can't figure it out.

Additional info: These scripts are executed by ANT with the database and schema name (search_path) already set. So I can not burn in names like 'production_db' or 'qa_db'.

The goal is to create a single PostgreSQL Role, so that clients can go around Tomcat (Hibernate) and run selections/searches directly on the Postgres server. The role must be able to select from Views but nothing else.

UPDATE: the accepted answer led me to a solution where I extracted all the views to a separate schema and granted access to that schema only.


texts: Method\Function encryption in PostgreSQL

texts:
I'm primarily MS SQL Server user.

Designing a PostgreSQL database, I wanted to encrypt methods\functions.
Is there any way to do this?

Thanks!


texts: Could you please help to explain why one might need multiple instances of SQL Server on the same server? What is a good point to use separate instances? Why wouldn't one use two databases in one instance instead? At any point is it a good idea to have separate multiple instance in the same server?


texts: Is it possible to have mysql treat row based updates as "upserts"?

texts:
When processing row based replication events, mysql replication will break if it's unable to find a row matching the "before image".

Is there any setting to tell mysql, if you don't find that row, just create it?


texts: Most efficient way to return multiple aggregates in a single stored proc?

texts:
What is the best or most efficient way to get multiple aggregate result values?

Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, archived, flagged... ) as defined below.

Here is a sample of things I am trying to do in one stored proc call. (where [uid] is the userID)

SELECT * FROM Message
Inbox    WHERE [to]    = [uid]
Unread    WHERE [to]    = [uid] and isread = 0
Flagged    WHERE [to]    = [uid] and isFlagged = 1
Drafts    WHERE [to]    = [uid] and isDraft = 1
Sent Messages    WHERE [from] = [uid]
Archived Messages    WHERE [to]     = [uid] and isArchived = 1


texts: Need help in putting the single quote and constant timestamp in plsql

texts:
Please help me in appending the Single quotes and constant timestamp to my query output. Given below is the expected output from my param file.

Note: Query is executed through sqlplus

Expected output in spool file(params.txt):

'12-DEC-2014'
'15-DEC-2014 23:59:59'

Script:

spool C:\output\params.txt
SET SERVEROUTPUT ON;
variable from_date varchar2(30);
variable to_date varchar2(30);
set verify off
set feedback off
set trimspool on
set linesize 1000
set term off
set colsep ,
set pagesize 0
set heading on
set underline off

begin
  :From_date := '&1';
  :To_date := '&2';
end; /

select to_date(:From_date,'dd-mon-yyyy') from Scehma.table1;
select to_date(:To_date, 'dd-mon-yyyy') from Scehma.table1;
exit 0;
exit


texts: Relational Databases many records stored in one

texts:
I've built a few relational databases in Access, and will now start moving to SQL Server. I want to build a database for me to practice and learn, and thought about building a fake restaurant's database. I've been thinking about the design, and came across an interesting problem.

Each menu item would have a record in one table. Receipts would go on another table, customers too. But how would I join many menu items to a single receipt record?

I would prefer an answer about the design itself rather than the solution in SQL Server.

I know this question might sound simple to answer but I'm new to database design.

Thank you.


texts: Is it possible to create a dynamic join?

texts:
I'm using PostgreSQL if it matters.

Assuming these two tables:

 id | run
----+-------
  1 | run_1
  2 | run_2
  3 | run_3

and

sample | elapsed |  run
-------+---------+-------
 samp1 | 1:09    | run_1
 samp2 | 0:32    | run_1
 samp1 | 0:58    | run_2
 samp2 | 0:28    | run_2
 samp1 | 0:55    | run_3
 samp2 | 0:28    | run_3

I want the following output:

sample | run_1 | run_2 | run_3
-------+-------+-------+-------
 samp1 | 1:09  | 0:58  | 0:55
 samp2 | 0:32  | 0:28  | 0:28

This is easy enough to do if I have a fixed number of run values, but is it possible to create a dynamic query which will output the right thing regardless of the number of runs?


texts: mysql-community-server5.7.18 conflicts file /etc/my.cnf with Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64

texts:
When i tried to install percona-xtrabackup on Master server db
yum install percona-xtrabackup-24

I got this error:
 file /etc/my.cnf from install of Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 conflicts with file from package mysql-community-server-5.7.18-1.el6.x86_64

How to solve this problem?
-CentOS 6.5  2.6.32-431.el6.x86_64


texts: Migrating a cluster from SQL Server 2005 to SQL Server 2016

texts:
I am in the process of migrating a SQL Server Cluster. I would like to hear considerations for a successful migration.

The specs of production cluster are:

Windows Server 2003 SP2 32bit.
SQL Server 2005 Enterprise edition - 9.00.3152.00 (Build 3790: Service Pack 2)
Disk space is about 1.5TB including all databases. (IBM v7000)

The specs of destination cluster are:

Windows Server 2016 Standard 64 Bits
SQL Server 2016 Standard 64 Bits
Same disk as old cluster, but faster (IBM v9000 Flash system)

Special Considerations:

Cluster Name must be the same to avoid changes in applications, linked servers and other connections. I mean, we have to migrate with a different cluster name, then shut down old cluster and rename the new one using the old name (I hope this to be clear)

At this time what I have done and tested is the following:

Create and configure a Windows Failover Cluster using Windows Server 2016 (with a different name)
Configure Active Directory to allow cluster to create objects
Restoring a database from MSSQL 2005 to MSSQL 2016, run some queries and explore objects.

Also:

I have split the drives following the best practices.
We are cleaning "super users" and giving just the right permissions to users.
Setting and testing compatibility mode to 130
Backup strategy was defined about 1 week ago to ensure data availability

Things to do or consider:

Download all SSIS packages to new project and test them against the new cluster. Once the cluster name changes, change SSIS to original name
Migrate all Jobs and make sure they run successfully
Test application that uses the migrated database
Create ODBC connections for Linked Servers pointing to SQL Server 2000 Databases (yes, we still use MSSQL 2000)
Backup Active Directory to have a rollback point in case something fail regarding cluster names (we have to delete old object and create a new one)
Configure SQL Replication.


texts:
When creating a non-clustered index, the leaf level will contain a reference to rows in the original table:

if the table has clustered index, the leaf level will contain the clustered index key;
if the table has no clustered index (a heap), the leaf level will contain  a pointer to the physical address of the row in the table;

My question is why the non-clustered index wasn't designed to contain the physical address in both cases (in addition to clustered index key). It could save the reads of the B-Tree for key lookups when clustered index is defined.


texts: Does MySQL have problems with nested insert like with subqueries in where?

texts:
Query 1:

INSERT `personal`.`locations`
  SELECT DISTINCT `s`.*
    FROM `references` `t`
      JOIN `locations` `s` ON `first_id` = `s`.`id`
    WHERE
      `lat` >= 37.3
      AND `lat` <= 37.3
      AND `lng` >= -122.2
      AND `lng` <= -122.1
ON DUPLICATE KEY UPDATE
  `lat`  = `s`.`lat`,
  `lng`  = `s`.`lng`,
  `name` = `s`.`name`,
  `desr` = `s`.`desr`;

Query 2:

INSERT `personal`.`locations`
  SELECT DISTINCT `s`.*
    FROM `references` `t`
      JOIN `locations` `s` ON (`first_id` = `s`.`id` OR `second_id` = `s`.`id`)
    WHERE
      `lat` >= 37.3
      AND `lat` <= 37.3
      AND `lng` >= -122.2
      AND `lng` <= -122.1
ON DUPLICATE KEY UPDATE
  `lat`  = `s`.`lat`,
  `lng`  = `s`.`lng`,
  `name` = `s`.`name`,
  `desr` = `s`.`desr`;

The select in query 1 takes 0.008 seconds to select 4 million records.

The select in query 2 takes 1 second to select 300 thousand records.

Query 1 executes completely in 60 seconds.

Query 2 executes completely in 300 seconds.

The conclusion: MySQL seems to repeat the select for every insert just like with where clause subqueries.

Is there a solution?

Edit 1: Added new query

Query 2: faster alternative but still with the same issue

INSERT `personal`.`locations`
  SELECT DISTINCT `s`.*
    FROM `references` `t`
      JOIN `locations` `s` ON `first_id` = `s`.`id`
    WHERE
      `lat` >= 37.3
      AND `lat` <= 37.3
      AND `lng` >= -122.2
      AND `lng` <= -122.1
UNION ALL
  SELECT DISTINCT `s`.*
    FROM `references` `t`
      JOIN `locations` `s` ON `second_id` = `s`.`id`
    WHERE
      `lat` >= 37.3
      AND `lat` <= 37.3
      AND `lng` >= -122.2
      AND `lng` <= -122.1
ON DUPLICATE KEY UPDATE
  `lat`  = `s`.`lat`,
  `lng`  = `s`.`lng`,
  `name` = `s`.`name`,
  `desr` = `s`.`desr`;

Slightly faster despite the fact it actually executes more updates but since the two selects execute faster the the one they replace it gains a bit of time.

Edit 2: Added table structure

CREATE TABLE IF NOT EXISTS `references` (
  `id`        bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `first_id`  bigint(20) unsigned NOT NULL DEFAULT '0',
  `second_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name`      varchar(255) NOT NULL,
  `status`    enum('V','I','D') NOT NULL DEFAULT 'V',
  PRIMARY KEY (`id`),
  KEY `first_id`  (`first_id`),
  KEY `second_id` (`second_id`),
  KEY `status`    (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `locations` (
  `id`        bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `lat`       double NOT NULL DEFAULT '0',
  `lng`       double NOT NULL DEFAULT '0',
  `name`      varchar(40) NOT NULL DEFAULT '0',
  `desr`      varchar(254) NOT NULL DEFAULT '0',
  `status`    enum('V','I','D') NOT NULL DEFAULT 'V',
  PRIMARY KEY (`id`),
  KEY `lat`    (`lat`),
  KEY `lng`    (`lng`)
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Each reference has to have at least one location associated based on first_id. The second is optional but it does exist for 70%-80% of the records. Each location can be associated with multiple references.


texts: Handling backups of a large table (>1 TB) in Postgres?

texts:
I have a 1TB table (X) that is a pain to backup.

The table X contains historical log data that is not often updated after creation. We usually only access a single row at a time, so performance is still very good.

We currently make nightly full logical backups, and exclude X for the sake of backup time and space. We do not need historical backups of X, since the log files from which it is populated are backed up themselves. However, recovery of X by re-processing of the log files would take an unnecessary long time.

I'd like to include X in our backup strategy so that our recovery time can be much faster. It doesn't seem feasible to include X in the nightly logical backup.

Ideally, I'd like a single full backup for X that is updated incrementally (purely to save time).

I lack the experience to investigate solutions alone, and I'm wondering what my options are?

Barman for incremental updates? Partition X? Both?

Thanks!


texts: Want to clone a role, but am getting "can't login" message after doing so

texts:
I’m using Postgres 9.5 on Ubuntu 14.04.  I would like to create a role (user) that has all the same privileges and access to a database that an existing role in my system has.  I thought I could pull that off by creating that user and then granting the other role to him…

postgres=# create role myuser;
CREATE ROLE
postgres=# GRANT rails TO myuser;
GRANT ROLE

but then when I login to that user, it turns out I don’t even have login privileges.

myuser@myproject:~$ psql mydb_production
psql: FATAL:  role "myuser" is not permitted to log in

What is the proper way to clone a role to another user (e.g. copy the privileges)?


texts:
I have the following table:

CREATE TABLE post (
  id            bigint primary key,
  thread_id     bigint,
  is_notice     boolean,
  title         text,
  content       text
)

I display the list using the following query:

SELECT * FROM post ORDER BY is_notice desc, thread_id desc, id

Then, given the post selected by id(i.e. SELECT * FROM post where id=3), how do I retrieve the next and previous posts?


texts:
Recently, I had an issue with one of my database which got XID wraparound issue. So, I have started with single-user mode and ran vacuum. Everything was fine. But, when I checked datfrozenxid its ~1.5billon where as my txid_current is ~70billon. I feel like, its huge different and something is wrong.

The below query, I have used to see the datfrozenxid from pg_database

select datname, datfrozenxid, age(datfrozenxid) from pg_database order by datname;

I have read a XID wraparound scenario from http://www.rummandba.com/2014/02/understanding-xid-wrap-around.html

It make sense to me as per the Example given in above link. But, I have calculated xidWrapLimit, xidStopLimit, xidWarnLimit, xidVacLimit with my statistics.

xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1);
xidStopLimit = xidWrapLimit - 1000000;
xidWarnLimit = xidStopLimit - 10000000;
(I haven't given all the values here since I am looking only for xidWarnLimit)

So, the xidWarnLimit is 3.747 billon where my current txid_current>70 billion. And strange thing is txid_current is greater than xidWrapLimit which is 3.758 billon.

How to find out, when it will raise the Warning about wraparound warn limit.

(using Pg9.5)


texts: SQL Analyze - dbms_stats consuming much CPU on Oracle Server

texts:
In one bigger DB with many user schemas and big tables I have the devs starting regulary the analyze select dbms_stats queries, that consume a lot of cpu when started for many users at once. These analyze queries have sometimes over 2-3 minutes duration time and all of them have COUNT inside and the explain plan shows that the queries use every time table access FULL scan.
/* SQL Analyze(0) */ select /*+  full(t)    parallel(t,5) parallel_index(t,5) dbms_stats

A lot of those queries need to collect statistics, but during this period they consume all of the server CPUs, and I would like to see if they can be optimized.
The queries are similar like in the Doc ID 2552730.1
They have a lot of repetitive substrb(dump(max( and to_char(count( sub-functions for the table columns.
PX Coordinator is starting them parallel, which is ok, before SORT aggregate and after sort agreggate in the explain plan. Many tables have over 150 million rows.
The tables are not partitioned. At the end of the queries we have also TOPN,NIL,NDV methods repeating many times.
Have you maybe encountered same issues and which approach could be taken to optimize those queries if possible. SQL Advisor gave back no recommendations.

It is triggered by the application and not the developers. I have it also in production. The users from the app/batch servers are not in any particular group and they are in OTHER_GROUPS. So no special consumer groups are setup here. I have many CPUs and this can be done parallely but it is still using too much resources. How can I tell, how often the manual statistics gathering the statistics? I guess they are triggering it after tables get fragmented, but they are not always according to dba_segments.
I have the 19c database. And Cloud Control. How do I step back to let Oracle do their work. Which jobs or which components of Oracle need to be configured to do the statistics gathering automatic. I see that many tables are not even fragmented. We had some Shrink jobs on the big tables in the past. I try to compare dba_segments and dba_tables to see if they are fragmented. Not sure if this is the right approach for that.
I guess we have locally managed tablespaces since I have oracle 19c DB. I guess table fragmentation should not happen at all. We have a batch system where we run calculations during night and day. The analyze dbms_stats queries always do finish without errors, but as used in parallel they eat up all of the CPU resources and are very I/O intense.
I cannot just propose to the developers to turn these jobs only on weekends? Or make less frequent? Because batch jobs do subsequent table updates. At the moment I see no solution.
Maybe I could also use DBMS_STATS.LOCK_TABLE_STATS to lock the statistics for a week or more to avoid too much triggering of the jobs?


texts: How to reproduce "resource busy and acquire with NOWAIT specified or timeout expired " error on local machine in oracle

texts: I am running oracle database and I am getting error as "resource busy and acquire with NOWAIT specified or timeout expired " and because of this error i am getting some other exceptions. on my local machine I am not getting this "resource busy ..." error. basically I want to reproduce this error on my local machine . is there any way to reproduce this error manually on my local machine?


texts: How Ola Hallengrens Index maintenance scripts handles columnstore indexes?

texts:
I'd like to know how Ola's script handles columnstore indexes. Couln't find any information about that, only in the version history it was mentioned that script does have a support.

My experience is that script didn't do anything to fragmented columnstore index. Is that by design?


texts: PowerShell script to access Oracle system views

texts:
I am using PowerShell script to access oracle 11g's system views
as  SELECT COUNT (*) AS COUNT FROM v$session but I keep getting "ORA-00942: table or view does not exist"

I have created a user with sysdba privilege and all connections are a fine as I can even query other schemas without errors, but I only get error when I access any system views. can anyone tell me what I am doing wrong?

I also gave permission as "GRANT SELECT on v_$session TO mynewuser even tho I know its not needed if the user has sysdba.


texts:
What are the downsides to storing two integers as a decimal?

I am storing asset details in tables, each asset type has its own table (each asset is very different) and using another table to define the asset tables, so each asset table has an integer id and each asset also has an integer id.

I have two different scenarios where this could be handy:

have an "audit" table that stores information like: this user did this to that item
someone is assigned to work on this asset of this type.
I was thinking of storing it like assetType.assetID so asset type 5 and id 99 would be decimal 5.99

I would very rarely need to select based on 5.99, I would just query the record that stores the 5.99, then split it and use a function to go to table 5 record 99.

I can't tie the assetID to a specific table; assetType is the id of an entry in a table referencing the asset tables (defines things like table name, primary key column and the like) so it already seems like I wouldn't be able to use foreign key constraints either way.

There are a lot of asset tables, like asset_tmv and asset_backflow. An asset is assigned a type by what table it is in, as the data to be stored for each asset varies greatly.

I realise I could achieve this using 2 integer fields. What I am wondering is: What would the downsides be?


texts: Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date?

texts:
If I regularly backup my whole server where a SQL instance and database lives, and restore it to a different machine, can I use the database backups that have occurred (on the original server) after that full server snapshot was generated, to bring the newly restored server up to date?

Which kinds of database backups would I need available to be able to do so?
I'm guessing if I have a full backup of the database that is current, that would be sufficient, but if my last full backup was as old (or even older) than the server snapshot, could I use that full backup + any differential, transaction log, etc backups that came after that full backup to bring my server snapshot on the newly restored server back up to date?

Example (for simplicity let's assume each of these backups are completed instantly):

Server 1 has a full server point in time snapshot taken at 5:30 PM every day.

Database A on Server 1 undergoes nightly full backups at 11:30 PM every day.

Database A also has routine differential backups taken every hour throughout the entire day.

Database A also has transaction log backups taken every 15 minutes throughout the entire day.

Scenario 1:

Server 1 crashes at 11:50 PM.

Server 2 is restored from Server 1's full server point in time snapshot backup from 5:30 PM earlier that day.

Can we use the full backup (from Server 1) of Database A from 11:30 PM (20 minutes before the crash) to restore the database to as it was at 11:30 PM and then use the 11:45 PM transaction log backup to bring the database to as recent as possible (on Server 2)?

Scenario 2:

Server 1 crashes at 11:20 PM.

Server 2 is restored from Server 1's full server point in time snapshot backup from 5:30 PM earlier that day.

Can we use the full backup of Database A from the night before at 11:30 PM (since we didn't get to take one today yet before the crash) and then all the hourly differential backups between 11:30 PM the night before until 11 PM the night of and then the 11:15 transaction log backup to bring the database to as recent as possible (on Server 2)?

Is there a more efficient way of restoring in either scenario above and/or a better way processes on types of and frequency of database backups?


texts: What are the causes and solutions for mutating table errors?

texts:
I understand mutating table errors are caused by a design flaw or problematic query.

An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but we do not know how.

What exactly causes mutating table errors and how would our DBA have fixed the problem?


texts: SQL Multi Subnet Always on High Availability

texts:
I am configuring a SQL Always on High Availability solution using SQL Availability groups, this will be spanning two subnets.
Using Windows 2019 and SQL 2019 one node in AWS and one on Premise.
When I start the create cluster wizard I am able to add both nodes into the cluster but when I get to the next section 'Access Point for Administering the Cluster'  I only see a single network where I expected to see two, one for each subnet.


texts: How to query MySql using 256 AES decrypt with key and IV

texts:
How does one write a MySql query to do a search on (for example) a username, when the username has been AES encrypted with a key and an IV?

I can't find examples of how to write the query.

Of course one has to decrypt the entire dataset and then filter it. Doesn't this cause memory problems?


texts: How to access mongodb replica set after restart

texts:
Yesterday I created a replica set and everything seemed to work. I closed all the cmd's when i was done.

I am assuming this replica set has not been deleted or anything, since old dbs i've created still are available. How can i access the replica set?

My replica set consists of three nodes on ports 27017, 27018 and 27019.
I have started

one mongod (the default one, for 27017)
mongo --port 27017
rs.status() which returns

"ok" : 0,
"errmsg" : "not running with --replSet",
"code" : 76,
"codeName" : "NoReplicationEnabled"

The node is most likely a secondary node, but it shouldn't matter. Even if I were to start the other two mongod's, i am feeling i'm missing something here.

I know this is probably a very stupid question. I am a beginner to mongo so please bear with me.


texts: If a select statement returns no result, change the where condition

texts:
I have two queries. I want to run the first select statement and only if it does not return any record then I want to run the second statement. How can I achieve this. These are my two statements:

select * from drscan.oem_program p
WHERE upper(OEM_PROGRAMID) LIKE upper('%Subvented%')
  and oem_code = 'NCI'
  and rownum=1
  and p.ISACTIVE='Y'

select *
from drscan.oem_program p
where oem_code ='NCI'
  and p.PROGRAMCATEGORY='NEW'
  and p.ISACTIVE='Y'
  and trunc(p.STARTDATE)<=trunc(sysdate)
  and trunc(p.ENDDATE)>= trunc(sysdate)
  and rownum=1


texts:
I'm trying to run an update query but still taking to long I have a commit in it.
Is it better to set the commit to a higher value (5000) or lower (1)?
I have to update 15 million records in a table of 21 million

edit here's my query for the update but I need to know how low/high I should set the commit

DECLARE
CURSOR c_itemloc
IS
  SELECT item, loc
  FROM dc_item_loc;

 l_item item_loc.item%TYPE;
 l_loc item_loc.loc%TYPE;
 i        NUMBER;
 l_commit VARCHAR2(1) := 'Y';

 BEGIN
   i:=0;
   FOR r_itemloc IN c_itemloc
   LOOP
     i := i+1;
     UPDATE item_loc il
     SET il.status             = 'D',
        il.last_update_datetime = get_vdate,
        IL.LAST_UPDATE_ID       = 'CNVOBJ_RNG'
     WHERE item                = r_itemloc.item
          AND loc                   = r_itemloc.loc;

     IF l_commit = 'Y' AND mod(i, 5000) = 0  THEN
        COMMIT;
     END IF;

    END LOOP;

  EXCEPTION
  WHEN OTHERS THEN
   dbms_output.put_line('YOU FAIL');
  END;


texts: I am trying to add a replica in a different subnet to my availability group and keep running into an error. I have confirmed the default file locations are the same on both servers but can't figure out why I am getting this error.


texts:
I am building a fleet unit management software which handles data from GPS devices mounted on the clients' vehicles.

Now i have to implement the database design. I have two main tables.

table 1: Fleet_Unit(unit_id, col_2, col_3, col_4), unit_id is PK

table 2: Gps_Device(device_id, col_2, col_3, col_4), device_id is PK

In most scenarious only one gps device is mounted on one vehicle but there are cases when two or even more gps devices may be mounted on the same vehicle for various reasons. So i have to decide if i should have an extra table to map the vehicles to the gps devices or put an extra column inside the Gps_Device table where the ID of the Fleet_Unit table will be stored.

I decided to use the first approach so i made one more table:

Fleet_unit_gps_device_mapping(fleet_unit_id, gps_device_id).

So before i ask you for opinion let me introduce you few more tables that are used to describe the fleet_unit object.

Each gps device has some analog and digital I/O. There may be one or more fuel meters connected to the gps device and vehicle's engine, temp sensors, card readers and so on.

So here is ONE of the extra tables (the others are similar):

fleet_unit_digital_sensors_mapping(fleet_unit_id, gps_device_id, sensor_col_1, sensor_col_2)

As i mentioned there may be more than one gps device mounted on the same vehicle so digital-sensor-1 which is read from first gps device is different from digital-sensor-1 which is read from the second gps device.

Now i'd like to ask you how to implement the database.

Should i use fleet_unit_id and gps_device_id from table fleet_unit_digital_sensors_mapping as foreign keys for tables Fleet_Unit and Gps_Device

or

create a new column master_id which is "combination" of fleet_unit_id and gps_device_id and connect it to the mapping table - Fleet_unit_gps_device_mapping where i need to introduce another column used for the map.

The reason I'm asking is because in first scenario if i have 10+ tables that describe the vehicle i have to connect each one with the fleet_unit and gps_device table. In second case i simply connect all 10+ tables with the table that maps the vehicle and the gps devices.

Thanks in advance.


texts: Actual Row Size vs My Estimated Row Size

texts:
I have a table of 500 million rows on SQL Server 2016. I calculate that each row should take up 12 bytes, total size should be in the order of 6 GiB. However the actual size is over 10 GiB.

After running dbcc showcontig ('[Table]') with tableresults I see the the MinimumRecordSize and MaximumRecordSize is 19 bytes and the AverageFreeBytes is 11.023 bytes.

The table is defined as the following.

CREATE TABLE [dbo].[Table](
    [Key] [binary](7) NOT NULL,
    [Value] [binary](5) NOT NULL
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-Key] ON [dbo].[Table]
(
    [Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

My question is where is the extra 7 bytes per row coming from? And is there anything I can do about it?

Also if there are any suggestions about space optimizations would also be appreciated, the table will be read-only (no updates or inserts).


texts: "Last database backup" not updating as log shipping target has different name

texts:
I monitor standby synchronization with an scheduled job that sends me the outdated "last database backup" dates of multiple databases on many SQL Server instances.

I have this database named ANALYTIX that has a recent backup timestamp :

And here is it's standby called ANALYTIX_BKPSQL, which was re-created just now in an attempt to fix the undetected last database backup

I tend to believe this is due to the two databases having different names, is that right ?

I'd really like someone to show me how to get this to work other than renaming the log shipping target ANALYTIX_BKPSQL to ANALYTIX ?


texts: What would be an appropiate BatchSize for bulk insert 34 GB of data (JSON files) into SQL Azure?

texts:
I got like 25,000 JSON files whose size (in average) is 1.2 MB (also, the average of records per JSON is 5,000), and the total size is like 34 GB. I have been reviewed the BCP utility from SQL Server, especially the SqlBulkCopy class in C# (I'm going forward to use C# because these files are actually dumped in a Azure Blob Storage), and thinking the approach that I'd take for process these files: I'd iterate over files, and download 200 files in each iteration, then merge them into a collection, so according to the estimation above I'd have 1,000,000 records to insert in each iteration (1 file has in average 5,000 records, so 200 x 5000 = 1,000,000)... now, once I got the records to be inserted, I'd use the SqlBulkCopy class for insert these records... my doubt comes here: for improve the bulk insertion performance, I could hold a table lock during the insertion by passing some parameter when instantiating the SqlBulkCopy class (which is legal in my scenario, due to this program would be used for ETL purpose), and I could send the records in multiple batches if I set the BatchSize property... but what would be the best size for the batch? Send all the records in a single batch would cause any sort of overhead.

Note: the JSON schema is actually simple: an array of objects (it doesn't have nested objects), and each object has like 7 - 8 fields.


texts: Value of the Included Column is stored in Leaf Node?

texts:
There is SQL- script, which generated the Nonclustered Index with Included Column:

CREATE TABLE users
  (
     id        INT,
     firstname VARCHAR(50),
     surname   VARCHAR(50)
  );

CREATE CLUSTERED INDEX ix_users_id
  ON users (id);

CREATE NONCLUSTERED INDEX ix_users_firstname
  ON users (firstname)
  include (surname);

SELECT firstname,
       surname
FROM   users
WHERE  firstname = 'John';

If I correctly understood, most of the time, Engine of my SQL Server 2019 will seek Nonclustered Index for the above SELECT query, without touching the Clustered Index. Does that mean the value of surname column is stored in Leaf Node of the Nonclustered Index? Also, that means the value of surname is duplicated because it also stored in Clustered Index.

Am I right?


texts: How to alter an existing partitioned table to add sub-partitions to it?

texts:
This forum has helped me earlier on many occasions and now I need someone to help me with this.

I am looking to convert a pre-existing partitioned (range) table - to sub-partitioned (composite range-list).

1) I Range Partitioned an existing table using the query below:

alter table PART_TEST
modify PARTITION BY RANGE (CREATEDATE)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('15-MAY-2019', 'DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('16-MAY-2019', 'DD-MON-YYYY')),
PARTITION p3 VALUES LESS THAN (maxvalue)) online;

2) Then I tried to alter the table to add sub-partitions (list) using the query below:

ALTER TABLE PART_TEST
MODIFY PARTITION P1
ADD SUBPARTITION SP1
VALUES ('1') tablespace TEST_PART;

But I see this error:

  Error report - ORA-14253: table is not partitioned by composite
  partition method
  14253. 00000 -  "table is not partitioned by Composite Range method"
  *Cause:    The table in a subpartition maintenance operation (ALTER TABLE
             EXCHANGE/MODIFY/MOVE/TRUNCATE SUBPARTITION, or ALTER TABLE MODIFY
             PARTITION ADD/COALESCE SUBPARTITION command must be partitioned
             by Composite Range method
  *Action:   Ensure that the table is partitioned by Composite Range method

Below is what I am tasked with,

Scenario - 1:
==> I want to have my existing partitions(range) subpartitioned (list) so that my old data is usable. The data in each of my partitions have to be moved to a sub-partition,

Scenario - 2: Then revert back to partition level (reverse migrate),
==> I mean the data from all of the sub-partitions within each partition is moved to be at the partition level and no sub-partitions are left in the database.

Is it possible and is there a way to do so?
If so how to implement the above scenarios - convert existing partitions(range) to sub-partitioned (Range-list) - and how to move back the data from sub-partitions to each of its partition back and no-sub partitions are left.


texts: Monitoring for and dealing with sudden jump in TOAST bloat

texts:
I'm trying to diagnose a sudden jump in disk usage in one of my databases (running 11.4). Typically the database grows by ~1 GB per day, but recently in the span of 24h it grew by nearly 100 GB.
Looking at various statistics, I concluded the growth came from a particular TOAST table. The table is used for a BYTEA column which stores an encrypted blob of a couple of MBs. There are ~1000 rows, but the TOAST table takes up 400 GB.
These rows are updated frequently for a few days after they're first created and then aren't touched again.
The DB is a multi-AZ RDS deployment, so my best hypothesis is that replication fell behind, causing a bunch tuples to be kept alive and making the TOAST table suddenly grow.
Is that a sensible hypothesis? How can I confirm if it's the case? What are possible workarounds to avoid this hitting us again?

Additional data:

The pg_toast table has ~900k live tuples
Dead tuples were at 5.2M this morning but at 1.7M now
The pg_table_size hasn't budged in a few hours for the pg_toast table, despite continued activity on the underlying table


texts: The remote copy of database “” is not related to the local copy of the database

texts:
I'm trying to join a secondary DB to my SQL Server 2012 Availability Group following this article:

Join a Secondary Database to an Availability Group (SQL Server)

When I attempt this the local SQL server windows event log gives error 1407:

The remote copy of database "<DBNAME>" is not related to the local copy of the database

I have followed all the steps and created a recent backup and restore with NORECOVERY.

The error log of the destination SQL server gives these errors:

AlwaysOn: The local replica of availability group '<AGNAME>' is going offline because the corresponding resource in the Windows Server Failover Clustering (WSFC) cluster is no longer online. This is an informational message only. No user action is required.

The state of the local availability replica in availability group '<AGNAME>' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_NORMAL'. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. For more information, see the availability group dashboard, SQL Server error log, Windows Server Failover Cluster management console or Windows Server Failover Cluster log.

The state of the local availability replica in availability group '<AGNAME>' has changed from 'RESOLVING_NORMAL' to 'NOT_AVAILABLE'. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. For more information, see the availability group dashboard, SQL Server error log, Windows Server Failover Cluster management console or Windows Server Failover Cluster log.

The cluster and all its nodes are online. The AG dashboard does not give any additional information to help diagnose the problem.

Opening all firewalls did not solve the issue.

The Windows Event log gives this line:

The database 'DelphiOBUDB' is marked RESTORING and is in a state that does not allow recovery to be run.

However, the DB is left in Restoring mode when you restore it and specify the NORECOVERY flag as per the tutorial guidelines.

How can I join the DB to my availability group?


texts:
I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get notice like ERROR: column foo.[column] does not exist.

This is the simply what I've done. sqlfiddle.com

Maybe someone can help, thanks!

Table definitions and data:

CREATE TABLE public.tprog (
    progId integer NOT NULL,
    codeProg character varying(20) NOT NULL,
    nmProg character varying(100)
);
INSERT INTO tprog VALUES (1, '01PROG', 'Prog One');

CREATE TABLE public.tkeg (
    kegId integer NOT NULL,
    codeKeg character varying(20) NOT NULL,
    nmKeg character varying(100),
    progId integer NOT NULL
);
INSERT INTO tkeg VALUES (1, '01KEG', 'Keg One', 1);

CREATE TABLE public.mDonation (
    mDonationId integer NOT NULL,
    nmDonation character varying(100)
);
INSERT INTO mDonation VALUES
(1, 'Flood donation'),
(2, 'Tsunami donation');

CREATE TABLE public.tDonation (
    tDonationId integer NOT NULL,
    customerId integer,
    mDonatioanId integer,
    month character varying(10)
);
INSERT INTO tDonation VALUES
(1, 1, 1, '2019-01'), (1, 2, 1, '2019-02'), (1, 3, 2, '2019-03');

CREATE TABLE public.tmonth (
    tmonthId integer NOT NULL,
    progId integer NOT NULL,
    kegId integer NOT NULL,
    tDonationId integer
);
INSERT INTO tmonth VALUES (1, 1, 1, 1);

CREATE TABLE public.tCustomer (
    tCustomerId integer NOT NULL,
    nmCustomer character varying(30),
    loc3Id integer NOT NULL
);

INSERT INTO tCustomer VALUES
(1, 'Dani', 1),
(2, 'Sarah', 2),
(3, 'Jin', 3);

CREATE TABLE public.loc1 (
    loc1Id integer NOT NULL,
    nmloc1 character varying(50)
);
INSERT INTO loc1 VALUES (1, 'Country1');

CREATE TABLE public.loc2 (
    loc2Id integer NOT NULL,
    nmloc2 character varying(100) DEFAULT NULL::character varying,
    loc1Id integer
);
INSERT INTO loc2 VALUES
(1, 'SubCountry1 A', 1),
(2, 'SubCountry1 B', 1);

CREATE TABLE public.loc3 (
    loc3Id integer NOT NULL,
    nmloc3 character varying(100) DEFAULT NULL::character varying,
    loc2Id integer
);
INSERT INTO loc3 VALUES
(1, 'SubCountry1 A1', 1),
(2, 'SubCountry1 A2', 1),
(3, 'SubCountry1 B1', 2);

Query that results in error:

SELECT a.nmprog, b.nmkeg, f.nmDonation, foo.nmloc1, foo.nmloc2, foo.nmloc3, foo.nmCustomer
FROM tprog a
LEFT JOIN tkeg b ON a.progId=b.progId
LEFT JOIN tmonth d ON d.kegId=b.kegId
LEFT JOIN tDonation e ON e.tDonationId = d.tDonationId
LEFT JOIN mDonation f ON f.mDonationId = e.mDonatioanId
LEFT JOIN (
  SELECT z.nmloc1, y.nmloc2, x.nmloc3, w.nmCustomer FROM tCustomer w
  LEFT JOIN loc3 x ON x.loc3Id=w.loc3Id
  LEFT JOIN loc2 y ON y.loc2Id=x.loc2Id
  LEFT JOIN loc1 z ON z.loc1Id=y.loc2Id
) AS foo
ON foo.tCustomerId=e.customerId
WHERE e.month LIKE '%2019-01%'
;

  ERROR: column foo.tcustomerid does not exist Position: 508


texts: Finding The Actual Application User ID In The Transaction Logs

texts:
I am trying to make any row in the database auditable and transaction logs seem to capture what changed when. With regard to who made the change, this link explains how to extract the database user ID from trace logs: How to find out who deleted some data SQL Server.

This would be OK for SMSS users since they would all have different database user IDs. But because the web application has a single database user ID in its connection string, every transaction coming from the web application will refer to the same user. I want to be able to find out the application user who made the change, not the database user.

Is there a way that I can pass this actual user ID information as some kind of meta data so that it will be logged as well for each transaction?


texts:
What is the query to get the tokens in a string from SQL Server 2008 R2?

I need it to highlight results of FREETEXT


texts: Log Shipping with Clustered Instance- back up

texts:
Current Set up- Clustered Instance Active/Passive
Node  -1
Node  -2
Current Back ups goes to Network Drive
Cluster Drive

E drive

Virtual Name ABC
Shared Folder - e\Share

New instance- for Log Shipping

My question is if i set up a log shipping between clustered instance will it break my current back ups that goes to the network


texts: MySQL Dynamic columns duplicate value workaround

texts:
As the title suggests i have the following code which transposes certain row values into field names, however the problem im having is that the values "closed" & "reopened" may appear in several times in multiple rows. but the code is just picking up one occurrence

This is what the table looks like without running the query:

1

SELECT
CONCAT('SELECT `ost_ticket_event`.ticket_id',
        GROUP_CONCAT('
                 ,    `t_',
            REPLACE(state, '`', '``'),
            '`.timestamp
                     AS `',
            REPLACE(state, '`', '``'),
            '`'
            SEPARATOR ''),
        ' FROM `ost_ticket_event` ',
        GROUP_CONCAT('
                 LEFT JOIN `ost_ticket_event`   AS `t_',
            REPLACE(state, '`', '``'),
            '`
                        ON `ost_ticket_event`. ticket_id = `t_',
            REPLACE(state, '`', '``'),
            '`.ticket_id
                       AND `t_',
            REPLACE(state, '`', '``'),
            '`.state = ',
            QUOTE(state)
            SEPARATOR ''),
        ' GROUP BY `ost_ticket_event`.ticket_id')
INTO @qry FROM
(SELECT
    state
FROM
    `ost_ticket_event`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;

Produces:

As you can see the column closed and reopened is only appearing once, though i need it to be repeated for as many times as it has been closed and reopened (literally)


texts: MySQL settings useful to speed up a mysqldump import

texts:
Recently I had to import a 7 Gb MySQL dump file to a MySQL 5.6 server.  The import took around 7 hours on a mono-core CPU with 1 Gb of RAM.

Someone else tested the import on a MySQL server which has, amongst others, the following settings:

innodb_buffer_pool_size = 8G
query_cache_size = 300M

I'm a bit skeptical about the relevancy of these settings (and yes, I even think that setting such a large query cache is bad). Would that make a difference? Aren't these settings used only when querying the database, and hence irrelevant for an import?

If yes, which settings should be set to speed up the import of a large dump file?

According to the official documentation these values should be set temporarily:

unique_checks = 0
foreign_key_checks = 0

I've read here that it should be set also

innodb_flush_log_at_trx_commit = 2

but I don't think it would help, because autocommit mode (flushing logs to disk for every insert) is already disabled by default in the mysqldump command (--opt option).


texts: Is there a way to programmatically script all objects associated with a given table?

texts:
I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name?

If so, is there a way to find all objects (primary keys, foreign keys, triggers) associated with a given table and script all of them programmatically?


texts: LOCK=NONE when adding NOT NULL constraint fails with "cannot silently convert NULL values" error despite no NULL values being present

texts:
I'm trying to use MySQL's online DDL feature to add a NOT NULL constraint to an InnoDB table without blocking writes to the table. According to manual, this should be possible - the linked table summarising MySQL 5.6's online DDL capabilities has a 'YES' in the Allows concurrent DML column of the Make column NOT NULL row.

However, when I actually try this, it fails under absolutely all circumstances I've tried. Even if the column I'm trying to add the NOT NULL constraint to does not contain any NULL values, I get the following strange error:

  ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try LOCK=SHARED.

Here's an ultra-simple test case at the MySQL shell:

mysql> CREATE TABLE `Test` (
    ->   `id` int(10) auto_increment PRIMARY KEY,
    ->   `stuff` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)
mysql> ALTER TABLE Test MODIFY stuff varchar(100) NOT NULL, LOCK=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try LOCK=SHARED.

How (if at all) can I get around this and add a NOT NULL constraint while permitting concurrent DML?

In case it's relevant: my exact MySQL version is 5.6.16 and my SQL_MODE is NO_ENGINE_SUBSTITUTION.


texts:
I have table with following data

id   start_date end_date
1    2018-01-01 2018-01-15
2    2018-01-11 2018-01-16
3    2018-01-03 2018-02-10
4    2018-01-01 2018-03-15

I want to list events based on months. If I am on 2018-01 it gives results proper but when I am in 2018-02 it does not give me entry 4 which spread between 2018-01-01 to 2018-03-15

SELECT * FROM `table` `t`
WHERE  (start_date >='2018-01-01' AND start_date <='2018-01-31')
OR     (end_date >= '2018-01-01'  AND   end_date <='2018-01-31')

Any help


texts: MySQL Workbench - Migration Wizard - invalid option

texts:
I encountered following error during "Bulk Data Transfer" stage in MySQL Workbench Migration Wizard:

    Starting...
    Prepare information for data copy...
    Prepare information for data copy done
    Determine number of rows to copy....
    Counting number of rows in tables...
    /usr/lib/mysql-workbench/wbcopytables-bin --count-only --passwords-
    from-stdin --odbc-source="DRIVER=PostgreSQL ANSI;SERVER=XX;PORT=XX;DATABASE=XX;UID=XX;UseDeclareFetch=1" --source-timeout= --target-timeout=None --table "XX"

    /usr/lib/mysql-workbench/wbcopytables-bin: Invalid option --source-timeout=

    ERROR: Determine number of rows to copy: Error getting row count from source tables, wbcopytables exited with code 1
    Failed

Does anybody have an idea how to handle it?


texts: Unicode support of PostgreSQL and its performance

texts:
How is the Unicode support of PostgreSQL?

If I want to save Unicode data does it have to go through the conversion process while storing and retrieving? If yes then what is the performance of such conversion?

If there is such a conversion, will it come into play when storing simple ASCII data?


texts:
Starting at midnight for 30-40 minutes our application responds slowly (or times-out completely) What would cause this kind of cpu usage on a SQL 2012 server?

I cannot find any scheduled tasks or maintenance plans which run at this time.


texts:
I set up Data Collection for simple and basic monitoring of instances of SQL Server 2008 R2 (/2012) (is simple, accessible for and with very nice colors for my boss :-)

But one of instances showing negative numbers in Query Statistic History report, doesn't matter if is connected from local host or from another PC

Please to see image

My question is: what happened (or if is a bug in SSMS)?

How can I (and if is possible) to test correctness of the displayed values,


texts: MySQL equivalent of a spatial ST_Union aggregate function?

texts:
I'm working on a DB-spacial project in Mysql and I was trying to perform a union operation for a group of shapes. Im looking for something similar to the  SQL Server function UnionAggregate, or the PostGIS Spatial Aggregate function ST_Union

for example (In SQL SERVER):

SELECT
        geometry::STGeomFromWKB(Shape,27582),
        area_code
FROM area_table
WHERE area_code='xxxxxxx';

ST_GeomFromWKB do the same job in Mysql.

in the second part and with the UnionAggregate function + group by :

select
        dbo.UnionAggregate((geometry::STGeomFromWKB(Shape,27582)).MakeValid()),
        area_code
FROM area_table
WHERE area_code='xxxxxxx'
GROUP BY area_code;

Any Idea how to perform a similar operation (aggregate+group by) in Mysql.

Thanks.


texts: Creating a database with only MySQL command line?

texts:
The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable to "build" the correct install (he knows much more than me about linux command line and the server we're using than I do, and we are running on a relatively short schedule). Thus, the only tool I have with which to create a MySQL relational database is the built-in MySQL Command Line.

Is it possible (practically speaking) to create a functioning
relational database using only SQL code in the command line, or will
I eventually need a tool such as MySQL Workbench?

If it is possible, could you please direct me to a good
resource/reference on how to do so?

I have minimal experience with MySQL, but am familiar with databases and am generally computer-savvy. Thanks.


texts:
Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way to change the column to store just the YY-MM-DD?

If yes, would this corrupt any of the data?

If no, would the solution lie in creating a completely new column?


texts: Oracle Audit: Why Logons are less than 0.1% of logoffs?

texts:
I see the following quantities of logins versus logoffs vs logoffs by cleanup in a database:

SQL> select action_name, count(*) qty
from Dba_audit_session
group by action_name
order by 1;  2    3    4

ACTION_NAME                        QTY
--------------------------- ----------
LOGOFF                         1946180
LOGOFF BY CLEANUP               754683
LOGON                             1026

It doesn't make sense that logons are less than 0.1% of logoffs.

Any ideas why?


texts: Efficiently comparing prices in different currencies

texts:
I want to make it possible for user to search products within a price range. User should be able to use any currency (USD, EUR, GBP, JPY, ...), no matter what currency is set by the product. So, the product price is 200USD and, if the user searches the products that costs 100EUR - 200EUR, he still may find it. How to make it fast and effective?

Here is what I have done until now. I store the price, currency code and the calculated_price that is the price in Euros (EUR) that is the default currency.

CREATE TABLE "products" (
  "id" serial,
  "price" numeric NOT NULL,
  "currency" char(3),
  "calculated_price" numeric NOT NULL,
  CONSTRAINT "products_id_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "currencies" (
  "id" char(3) NOT NULL,
  "modified" timestamp NOT NULL,
  "is_default" boolean NOT NULL DEFAULT 'f',
  "value" numeric NOT NULL,       -- ratio additional to the default currency
  CONSTRAINT "currencies_id_pkey" PRIMARY KEY ("id")
);

INSERT INTO "currencies" (id, modified, is_default, value)
  VALUES
  ('EUR', '2012-05-17 11:38:45', 't', 1.0),
  ('USD', '2012-05-17 11:38:45', 'f', '1.2724'),
  ('GBP', '2012-05-17 11:38:45', 'f', '0.8005');

INSERT INTO "products" (price, currency, calculated_price)
  SELECT 200.0 AS price, 'USD' AS currency, (200.0 / value) AS calculated_price
    FROM "currencies" WHERE id = 'USD';

If user is searching with other currency, let's say USD, we calculate the price in EUR and search the calculated_price column.

SELECT * FROM "products" WHERE calculated_price > 100.0 AND calculated_price < 200.0;

In this way we can compare prices very fast, because we don't need to calculate the actual price for every row, because it's calculated once.

Bad thing is that at least every day we have to re-calculate the default_price for all rows, because the currency rates has been changed.

Is there a better way to do deal with this?

Isn't there any other clever solution? Maybe some mathematical formula? I have an idea that the calculated_price is a ratio against some variable X and, when the currency changes, we update only that variable X, not the calculated_price, so we even don't need to update anything (rows)... Maybe some mathematician can solve it like this?


texts: SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

texts:
Related Question: SQL Server script to delete accounts no longer in Active Directory

Whilst the above question points out the rather handy sp_validatelogins procedure, i'd like to extend this procedure to then drop the logins, search all databases & drop any related users.

Id also like to drop all SQL logins that have not been logged in for over X months - and again, drop any related users across all databases.

Before I go writing all this from scratch - I cant help but feel I'd be re-inventing the wheel - someone must surly have written a script to do this already but i'm not having much luck finding one!

If no one has a script up their sleeve to do all this, scripts to do parts of them would be just as good - I've no problem merging them into a single script.


texts: Getting the employee history of Sales department from HR database in Oracle

texts:
I am currently using the 'hr' database with Oracle 10g. You can view the schema diagram over here: http://docs.oracle.com/cd/B13789_01/server.101/b10771/diagrams001.htm#sthref63

What I would like is to retrieve the history of all employees who have worked in the 'Sales' department. I have selected only first name, last name, and job title for the working example but I'm getting the same job title where I should be getting two different ones.

This is the query I have so far:

SELECT a.first_name, a.last_name, b.job_title
FROM employees a, jobs b, job_history c, departments d
WHERE d.department_name = 'Sales'
AND c.employee_id = a.employee_id
AND a.job_id = b.job_id
AND c.department_id = d.department_id;

Any help would be appreciated.
JazakAllah khayr.


texts: Should there be a 1:1 ratio between table Partitions and filegroups

texts:
Table partitioning is quite new to me. I've been studying it for the past week. I was able to create a simple POC. The target table that we are going to apply partitioning to currently has around 100 million records and is partitioned by an foreign key/ client Id that also grows as time goes by.  We plan to apply partitioning on it and scatter the filegroups against our server's disk drives.

Now my questions are

Should there be a 1:1 correspondence between number of partitions and number of filegroups?
Will SQL Server automatically determine what filegroup a certain partition goes to? Lets say the table has 20 partitions, and we created one filegroup per drive (5 disks). When I enroll those filegroups against partition scheme/ function, will SQL Server do the allotment itself?

Any advice would be great.

Thanks in advance.


texts: Does Oracle support table-level & column-level permissions?

texts:
If there is a customer table, and some extra-private/secure information like SSN is there, should I create another table to store such information and lock that table so only certain people can view it even though it's strictly one-to-one? or can columns be locked too from unauthorized viewing based on rights?

Even if both types of security mechanism do exist, is one preferred over the another? I might have said something very vague but I've used SQL Server more often.


texts: SQL Server statement takes forever while running instantly in Oracle

texts:
Please help me interpreting this statement and plan:

https://www.brentozar.com/pastetheplan/?id=Bysy6YtEV

We migrated from Oracle to SQL Server and we have some quite strange behaviours. It might be related to a problem during migration.

I found it hard to interpret the execution plans. Both environments should have the same structure and indexes. Statistics should be up to date.
Settings in SQL Server:

Create Auto Statistics Enabled
optimize for Ad Hoc Queries = true
Snapshot Isolation enabled
Max Parallel = 4
Threshold 50

DB is 600 Gb in size, 16 cores, 160 Gb memory.

The query:

SELECT
    COUNT ( t_01.rsecondary_objectu ) AS selectExpr
FROM
    PIMANRELATION t_01 ,
    PITEM t_02 ,
    PITEMREVISION t_03
WHERE
    (   (   (   ( t_01.rprimary_objectu = t_02.puid )
            OR  ( t_01.rprimary_objectu = t_03.puid )
            )
       AND
            ( t_01.rrelation_typeu = 'w8INy241VJFL2B' )
        )
   AND  t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    )

Oracle

We found out that the issue is / was related and depend on the data. If I select a different item to copy in the GUI (it's actually a copy thing and any how the app does these statements) it works instant. The query then once working fine looks a little different: https://www.brentozar.com/pastetheplan/?id=SJo-h2c44

SELECT  COUNT ( t_01.rsecondary_objectu ) AS selectExpr FROM PIMANRELATION t_01 , PITEM t_02 , PITEMREVISION t_03 WHERE ( ( ( ( t_01.rprimary_objectu = t_02.puid ) OR ( t_01.rprimary_objectu = t_03.puid ) )  AND ( t_01.rrelation_typeu = 'w8INy241VJFL2B'  ) ) AND t_01.rsecondary_objectu IN  ('wBHZpD0uVJFL2B' , 'V2PlBGlAVJFL2B' , 'lnHlBGlAVJFL2B'  ) )

SQl Server seems to completely struggle with the one I mentioned before (running infinite) while providing the second one almost instantly. That's so crazy weird. Like a buggy product.

I don't have influence on the statements as they are produced by the app.

CE 110 / 70 results in the same plan. Even if disable all custom indexes and only keep the ones suggested by the app it behaves the same. All primary keys are clustered indexes. But maybe something messed during our migration. But it's quite strange. Most stuff runs fine, but the query at topic is extreme. I let it run für 45 minutes in SQL Server. It simply does not complete.

An other example. Our Prod Environment gets unuable once the highest rated index is created:

Index:
CREATE INDEX EN_PIPRELEASESTATUS_1 ON [TCEUP01].[dbo].[PRELEASESTATUS] ([pname], [pdate_released]) INCLUDE ([puid])

Results in this query:
https://pastebin.com/Ax3qTUjd
===>Took 137.142 seconds

our Test Environment, which should be the same, behaves differently:
https://pastebin.com/0PTZTJpr
===>Took 3.884 seconds

the problematic plan looks like this:
https://www.brentozar.com/pastetheplan/?id=Skvy0qRNE

and it seems it can run like infinite.


texts: How import mysqldump .sql file into MySQL 5.7 when exported from MySQL 8

texts:
I accidentally installed MySQL 8 and did a ton of work there, creating a lot of data. I then backed up all of the databases using mysqldump.

Later, I realized that I needed to downgrade back to MySQL 5.7 (after not even having realized that I'd upgraded).

I've uninstalled 8 and installed 5.7, but I've been having trouble importing my data from the .sql file from the dump.

First, I got the error ERROR 1273 (HY000) at line 1753: Unknown collation: 'utf8mb4_0900_ai_ci', which I solved by using Notepad++ to replace:

utf8mb4_0900_ai_ci with utf8_unicode_ci
utf8mb4 with utf8
utf8_unicode_520_ci with utf8_unicode_ci

Then I also deleted the section of the .sql file related to the 'mysql' database.

Now the command mysql < alldb.sql runs without error, but when I go look at my data, a lot of it is missing.

P.S. I feel optimistic that someone can help me, but it's a bit worrying that MySQL 8 docs say:

  Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you backup your data before starting the upgrade process.


texts: How can you output UPDATE / INSERT query results when using MySQL -e,--execute?

texts:
When using MySQL to run an UPDATE or INSERT, I get query result info:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

When using MySQL from the command line (eg: mysql -e "UPDATE ...") I get no output for successful queries.

How can I get the query result info to output when using the --execute argument?


texts: MySQL group replication between two servers behind NAT

texts:
does MySQL group replication should work if I have a server behind NAT?

I have a Hetzner CX server which has eth0 interface with addr 172.31.1.100.
Hetzner provides 1:1 NAT for this server and public IP (ex.: 123.123.123.101).

I tried to setup group replication with the following IP-related settings:

loose-group_replication_local_address = "172.31.1.100:33061"
loose-group_replication_ip_whitelist = "172.31.1.100,123.123.123.101,123.123.123.102"
loose-group_replication_group_seeds = "123.123.123.101:33061,123.123.123.102:33061"

and it doesn't work, due to error:

[ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
[ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group'

Replication user hostname defined as '%'.

When I tried to use the same configuration for servers without NAT - it works without any issues.

Please advice how to fix the problem or maybe MySQL doesn't support such situation.


texts: How to detect sudden bottleneck in mysql with 2000%CPU

texts:
Our nginx-mysql server has been running smoothly for months (even years) . All of the sudden mysql consume around 700-2000% CPU , very little RAM. even after mysql restart, the usage goes up right off. Nginx CPU/RAM usage is minimal (since the traffic is just at 20% of peak). Turning off nginx solved it proving some queries were having problem.  The question is how to pinpoint the troublemaker one. any command or query to do so (from bash, nginx, log etc..)?

FYI: I did optimize and repair all the tables but did not work. The problem was solved when I truncate 2 "middle size" table of 2M rows. Those 2 tables used to behave normally with 20M+ rows, and they have good indexes...  I did purge/truncate the large tables from time to time.

Edit 1: mysql-slow.log did help a little but not right to the point


texts: SQL Server schema intact but every record from every table is gone

texts:
We recently set up a new virtual machine running Windows Server 2008 and slapped a copy of SQL server 2005 on it. The server had running and in use without a hitch for about two weeks. The only real maintenance we've done on it is to move the partition the database files resided (done nearly a week ago) and we ran the Database Engine Tuning Advisor to look at some suggestions for new indices yesterday around 12PM.

Just after 5PM while the software was being used as usual someone notified me that the program no longer seemed to work properly. I checked the database and not only was data specific to his use of the program was gone, but every table of fifty tables or so seemed to be wiped of all records.

After talking to the application developers in extensive length, it became clear that this wasn't a remotely possible capability of their software.

In the scramble to get back from our feet we cloned a backup of the VM over the running copy between restarting and it's been running five for about a day ever since. Consequently, we've no logs of the events leading up to the problem itself.

Have you seen anything like this happen before? (All records in all tables of a database just... gone.) Does anyone have a reasonable theory as to how this could occur and what if anything would be a reasonable measure of prevention?


texts: Is it possible to increase Oracle execution plan output indentation?

texts: It seems Oracle's execution plan indent each row for just one character. See below image. Is there a setting to increase this value? To me, this is not very readable.


texts:
Disclaimer, I'm not a dba. I have been handed the job and have to do it. For day to day operations, I can handle the job, but now they want to upgrade the server and I'm stuck on this one issue.

I have 2 MS SQL servers, an older 2008 SQL server and a new 2016 SQL Server. I am replacing the old with the new. The servers are separated by network zones. The old server has been operating for a very long time. I have configured the new server with the same configurations as the old. I configured the servers using the same IP address.

I am able to connect to the new 2016 server via UDL file from another server in the same network zone. I am unable to connect from a different network zone via UDL.

I am able to connect to the old 2008 server from a server on the same network zone, AND a different network zone.

My question. Is there somewhere I should be allowing traffic from another network zone to the database server? or is there something else going on? The firewall rules are set up correctly by the network team (firewall is handled at the network level).


texts: Poor Query performance in postgresql 9.3

texts:
I have a simple query which is taking about 2 minutes to complete.

    select distinct
    a.statename,
    a.districtname,
    count(b.rel_globalid) as samplescollected
    from fulladmin a
    join gridpoint_attach_view b on a.globalid=b.rel_globalid
    where a.statename='Karnataka' group by statename, districtname order by districtname

Here fulladmin is a MATERIALIZED VIEW, which is having about 6.5 million rows.

    CREATE MATERIALIZED VIEW fulladmin AS
    SELECT DISTINCT a.districtname,
    a.talukname,
    a.hobliname,
    a.villagename,
    a.statename,
    b.globalid
    FROM village a
    JOIN gridpoint_1 b ON st_intersects(a.shape, b.shape::st_geometry)
    WHERE a.statename::text = 'Karnataka'::text
    GROUP BY a.statename,
    a.districtname,
    a.talukname,
    a.hobliname,
    a.villagename,
    b.globalid
    WITH DATA;

   ALTER TABLE fulladmin
   OWNER TO sde;

and gridpoint_attach_view is normal view

     CREATE OR REPLACE VIEW sde.gridpoint_attach_view AS
     SELECT gridpoint_1__attach.rel_globalid
     FROM gridpoint_1__attach;

     ALTER TABLE sde.gridpoint_attach_view
     OWNER TO sde;

I have created index on materialized view as

CREATE INDEX sample1 ON fulladmin (statename,districtname,globalid)

Still the query is taking too long. Any suggestion?

EDIT: When I run

explain (analyse,verbose) select * from fulladmin

I am getting output as

seq scan on fulladmin (cost=0.00..141541.52 rows=5765552 width=84) (actual time=0.042..1160.429 rows=5765552 loops=1)
  Output: districtname,talukname,hobliname,villagename,statename,globalid Total
runtime:1356.028 ms


texts:
I am having trouble understanding why a certain set of functional dependencies is not minimal.
We have a relation R(A,B,C,D,E,F,G) with the following dependencies F:

1. A->CDE
2. B->FG
3. AB->CDEFG

Minimal cover of F is just dependency 1 and 2. It is intuitive that attributes CDEFG are already determined by A and B separately. Hence, no new attribute is determined by union of AB. Is there an exact rule that determines that such dependency (union of AB->CDEFG) is redundant?

The Armstrong rules listed so far in the book are:

    IR1 (reflexive rule): If X ⊇ Y, then X →Y.
    IR2 (augmentation rule): {X → Y} |=XZ → YZ.
    IR3 (transitive rule): {X → Y, Y → Z} |=X → Z.

and

    IR4 (decomposition, or projective, rule): {X → YZ} |=X → Y.
    IR5 (union, or additive, rule): {X → Y, X → Z} |=X → YZ.
    IR6 (pseudotransitive rule): {X → Y, WY → Z} |=WX → Z.

I am not sure which rule is responsible for redundancy of AB->CDEFG. Union rule seem close, but the left-hand side attributes are listed as being the same (both X), which I can't say for FD1 U FD2 in my case.


texts: Specific changes to glibc 2.28 that affect PostgreSQL

texts:
I am preparing for upgrading a PostgreSQL server to Debian 10 (Buster) with the warning from Debian release notes about changes to gclibc affecting PostgreSQL in mind. The PostgreSQL wiki provides two specific strings that will be sorted differently under LC_COLLATE=en_US.UTF-8 with glibc versions prior to 2.28 (i.e. up to Debian 9) or from 2.28 (i.e. from Debian 10) respectively.

( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort # 11 1-1 under Debian 9
( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort # 1-1 11 under Debian 10

From this warning I have been expecting that similar problems occur with many string patterns. In order to better understand this I have created a table test with 1 million rows of random samples, i.e. each primary key is a string permutation of lower case letters, digits, hyphens and German umlauts. There are two additional rows that hold the two special strings 11 and 1-1.

When I dump this table with SELECT * FROM test ORDER BY key \g test.out from a database with encoding UTF8 and collation en_US.UTF-8 under Debian 9 and Debian 10 respectively what I observe is that the sort order only differs with respect to the two special strings. All the other (random) strings don't seem to be affected.

This makes me wonder what is the precise nature of changes to locale en_US (en_US.UTF-8) in glibc 2.28. Does it only concern the relative order between - and digits (and nothing else)? Where does the package source code reveal the exact difference (URL into GitHub, or similar)?


texts:
I am working with SQL Server Always On and I am querying the data as follows:
SELECT
name as AGname,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
ORDER BY name

This gives me the output I need, however, I also need the DB name so I am trying the following:
SELECT
name as AGname,
DB_NAME(AG_DB.database_id) AS DatabaseName,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
INNER JOIN master.sys.dm_hadr_database_replica_states AG_DB ON AG_DB.group_id = Groups.group_id
ORDER BY name

However, this is giving me issues as it is duplicating the information.
I also tried to fix this issue by doing the following:
SELECT
name as AGname,
(SELECT DISTINCT DB_NAME(AG_DB.database_id) FROM master.sys.dm_hadr_database_replica_states AG_DB WHERE AG_DB.group_id = Groups.group_id) AS DatabaseName,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
ORDER BY name

The latter is giving me "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression" error.
Any suggestions how I can get the required output without the duplicate entries?


texts:
I recently faced a delete syntax I was unaware of.

delete #fooTbl from #fooTbl where attr ='some'

The Official Microsoft documentation states:

DELETE FROM [database_name . [ schema ] . | schema. ] table_name
[ WHERE <search_condition> ]
[ OPTION ( <query_options> [ ,...n ]  ) ]
[; ]

i have some perplexity about the command I wrote:

What is and what is needed that parameter before the delete keyword (In my example coincides with table name)?
What is the best practice?


texts: How to deliberately lock out an SQL Server 2014 login without changing security policy

texts:
We came up with a series of SQL Server instance checks for the login used in our software product. One of the checks examines the login and determines if it's locked out. The code in the check needs to be tested so naturally we need a locked out login.

I've seen a lot of material on how to lock out a login after N failed attempts by changing the security policy. As we're subject to outsourced IT services, a request for such a change might be denied or placed at the bottom of the priorities list.

Is there a way to deliberately cause a lock out without making changes to the security policy?


texts: Query session parameter values of running session

texts:
I don't think there's a way to do this, but I'd like to be able to query what value a running session currently has for its COMMIT_WRITE session parameter.

I haven't seen anything in the v$ performance views... anyone know of a SYS x$ view from which such a value could be retrieved?

Clarification - the need is to pull the parameter setting for another session, running independent of current session.


texts: Why MySQL does not provide stddev(distinct expression)?

texts: I ask the question because some other numerical aggregates, like sum, avg or count support the distinct modifier. And other databases like Oracle or MSSQL support distinct modifier for stddev.


texts: What is the best way to store user profiles and logins?

texts: I need to store user profiles and logins. Is the best way to do that to have a table for logins (email, username, and password) and a separate table for their profile/info (e.g. their progress in something)? I am using MySQL if that makes a difference.


texts: Redshift: do with clauses /CMTs ALWAYS create temporary tables?

texts:
I like to use "with clauses" as filters: it makes my queries more "functional" in the linearish functional programming style x= a.b().c().filter(condition).sum() sense (obviously, SQL is one giant functional language, it's just a bit obtuse).

If I am careful about avoiding groupings, distincts or orderings, and only use a CMT in one following query/CMT, will Redshift dispense with the temporary table, and just treat it like a filter? Monitoring seems to suggest this to be the case.

For instance, will this query create two temp tables, or will the planmer say: "hey! no need to store any data…"?

with FilteredList as (
          select col1, col2 from Table
          where col1 is not Null
     ),
     MoreFiltering as (
           select col1, col2 from FilteredList
           where col2 is not Nill
      )
select col1+col2 as Summo
from MoreFiltering;


texts: Postgres functions: how to pass a type as an argument

texts:
I am using this query to list the values that are part of an enum:

SELECT unnest(enum_range(null::my_enum_name)) AS enum_values;

It works.

I am trying to make it more reusable by wrapping it in a function that takes the name of the enum (my_enum_name in the example above) as a parameter, but I can't find a lot of documentation on this.

Is it possible?


texts: How do I make a locale available in Postgresql?

texts:
I needed to add new locales to our postgresql server (PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit ).
As the locales were not yet installed on Ubuntu, I added them with

sudo locale-gen fr_FR.UTF-8

the locales do show now with

locale -a

I restarted the postgresql server, but the locales aren't visible in postgresql. I cant find themp in select * from pg_collation;

When I try to create the collation manually with create collation fr_FR (LOCALE=‘fr_FR.utf8’), I get this error

ERROR:  could not create locale "‘tr_tr.utf8’": Success

Am I missing a step ?


texts:
I am looking at some sample data and I noticed that the transaction datetime is 42,814,662,041,308. Can anyone help me convert it please?
I have already tried converting using Epoch Unix time


texts: Split a pattern string into columns in select query

texts:
I have a person table with below structure.

CREATE TABLE person(id integer, details text);
INSERT INTO person(id,details) VALUES(1, "name:Tahir,age:30,sex:male");

I want to split details column by comma and show results in difference columns. Like, in this example result would be id, name,age, sex

I believe that is possible with aggregate functions and I tried to produce a query like below

SELECT id,
if(person.details like '%name:%',cast(substring_index(substring_index(person.details,'name:',-1),',',1) as unsigned),null) as `name`,
if(person.details like '%age:%',cast(substring_index(substring_index(person.details,'age:',-1),',',1) as unsigned),null) as `age`,
if(person.details like '%sex:%',cast(substring_index(substring_index(person.details,'sex:',-1),',',1) as unsigned),null) as `sex`
FROM person

above query fetches data correctly for age but name and sex gets 0. Could you please figure out what I am missing here?

https://www.db-fiddle.com/f/cqyqb7Vhvs7kKYifFywmkV/6


texts: Will a nonclustered index with a unique column always address all queries filtering that column first?

texts:
I have found large heap tables that are over-indexed and for example have multiple different nonclustered indexes with different columns, but some of those indexes have the (only one present) primary key column set as their first index column.

I made some investigation, played around with indexes and queries and made my conclusions that I would like to see confirmed by professionals to be sure.

Question 1: Am I right to say it is nonsense to have multiple indexes based on the primary key instead of only ONE index based on the primary key column as the first and ONLY index column, because all queries using this pk for filtering or joining return only ONE row? (Assuming additional filtered, joined or selected columns are put into the index as included columns to entirely cover the queries and avoid RID lookups)

Question 2: Is it right to say that nonclustered indexes based on the primary key of a table should always be created as UNIQUE indexes? (a setting that is optional for whatever reason since sql server should already know that an index including the pk will be unique...)


texts: Is there a way to tell SQLCMD not to output rows to text or a file?

texts:
I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD.  My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows the ASYNC_NETWORK_IO waittype while an output file is being written to disk).  I would like to have SQLCMD run my SELECT statement but not actually save or output the result-set.  Just returning the number of rows affected would be fine.  Is this possible?

sqlcmd -E -t 0 -S server\instance -d databaseName -i hugeselect.sql -o none?


texts: Which PowerShell technique should I use to talk to SQL Server?

texts:
I'd ultimately like to use PowerShell to replace the old KornShell scripts we use for SQL instance monitors. I'm having a hard time, though, getting my brain around all the different ways that PowerShell can actually talk to SQL server. Not sure if this is all of them, but here are 5 entirely different ways I can query the version of a SQL server:

1. SQLConnection .NET Class

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=MyServer;Database=Master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "Select @@version as SQLServerVersion"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

2. WMI Provider

$sqlProperties = Get-WmiObject
    -computerName "MyServer"
    -namespace root\Microsoft\SqlServer\ComputerManagement10
    -class SqlServiceAdvancedProperty
    -filter "ServiceName = 'MSSQLSERVER'"
$sqlProperties.VERSION

3. SMO

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$smo-var = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\instancename'
$smo-var.VersionString

4. PSDrive

Set-Location SQLSERVER:\SQL\MyServerName\
$server = Get-Item Default
$server.get_VersionString()

5. Invoke-SQLCMD

Invoke-Sqlcmd -Query "SELECT @@version" -ServerInstance "MyServer"

How should I go about deciding which of these techniques to use for different scenarios? Are there pros/cons of each? Are some of these powershell 1.0 techniques that were superceeded in 2.0? Will some of them not work to communicate with SQL 2000 or 2005 servers?

On one level, I'm sure the answer is "use whatever works", but for someone new to Powershell, its very confusing to see so many examples written like #1 above, when that is the longest and (in my mind) least "powershell-like" example.

Slightly more info in case it is relevant: the SQL server that will actually be running the monitor scripts is SQL 2005, but it is used to connect to multiple instances from SQL 2000 up to 2008R2.


texts: Best Isolation Level to avoid deadlocks in Sql Server

texts:
I need execute un update statement over an sql server table, this table is used by another process at the same time. because that sometimes deadlocks ocurs.
wich Isolation Level do you recomend for L1 and L2 to avoid deadlocks?

p1:

set transaction level L1
    delete from t1 where id=2
    select * from t1 where id=3

p1:

set transaction level L2
    delete from t1 where id=3
    select * from t1 where id=2


texts: Would an INNER JOIN query in a sharded relational database hit the same shard when using a compound primary key?

texts:
In this question I have asked about modulus calculation in sharded tables, this design is used:

CREATE TABLE Orders (order_id INT PRIMARY KEY, ...);
CREATE TABLE LineItems (order_id INT, lineitem_id INT, PRIMARY KEY (order_id, lineitem_id), ...);

I would like to perform an INNER JOIN query so that the whole result set is gathered from a single shard in MySQL Cluster (or, for that matter, a sharded database), like so:

SELECT * from Orders INNER JOIN LineItems on Orders.order_id = LineItems.order_id where order_id <some_id>;

The idea would be that this query hit only a specific shard.  Is that how a sharded database works?  What needs to happen in order for that to be true?


texts:
I have a very strange case in mysql 5.5.46. the max_allowed_packet keeps resetting to 1024

show variables like 'max_allowed_packet';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_allowed_packet | 1024  |
+--------------------+-------+

although it is set in my.cnf to 32M

[mysqld]
max_allowed_packet=32M

and even if i try to run set global max_allowed_packet=33554432; it shows success but does nothing

mysql> set global max_allowed_packet=33554432;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_allowed_packet';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_allowed_packet | 1024  |
+--------------------+-------+
1 row in set (0.00 sec)

when i restart mysql it becomes 32M for a day or two then it back to 1024!! what could be the reason?


texts: SQL Relationship between Subtree Cost and Performance Time

texts:
What is the general relationship between "SQL Final Subtree cost" and "Query time performance"?

Example:
When tuning a query and it goes from
Subtree cost 0.2 to 0.1,
does that mean query time will be twice as fast? I do not see this occurring in my queries.

We have a Server and cannot truly measure query performance even with "Set statistics time on" and "DBCC DROPCLEANBUFFERS". There are different processes going on in the server, transactions, programs, background items.

Thanks,


texts: Insert blank row in the postgres query result

texts:
I have below table after run a postgres query select * from tbl

1 type_1 name-1
2 type_1 name_2
3 type_2 name_3
4 type_2 name_4
5 type_3 name_5

how can I add a blank line in between each type so I can read the output easily?

Like

1 type_1 name-1
2 type_1 name_2

3 type_2 name_3
4 type_2 name_4

5 type_3 name_5

OR

1 type_1 name-1
2 type_1 name_2
- ----   ----
3 type_2 name_3
4 type_2 name_4
- ----   ----
5 type_3 name_5


texts: Sync PostgreSQL with Cloud and multi Local Locations

texts:
I need help with a complex (for me) multi server setup with PostgreSQL.

I have to setup a Cloud Server and 3 Local Servers for our company.

Each Server will have:

Linux Ubuntu 16.04
Docker
PostgreSQL 9.6
Nginx
(maybe also Rancher 1.6 for container Management)

Users will normally connect to the Cloud instance for work.
But if the Cloud is down, users need to access local on each location.

How can i realize this scenario?
What should i use for PostgreSQL replication or sync?

Thanks in advance

Kevin


texts:
Look at the attached graphic.

I'm trying to keep this as simple as possible.

Basically I want to have multiple admins(or superusers) who own regular users who own sites.

Admins can give regular users permission or role to a website.
Admins can see and edit all sites of the users they own.
Users can only see & edit their own sites. (based on the role).
One site can be managed by multiple users.

I don't know if I should have permissions table with multiple rows for each role so one user can have many rows for each role or maybe use an array in one row only... Idk. I'm stuck there.

What's the best way to do this simply?

P.S: I know the graphic has User2 under site1 and site2 but I don't need it that complex. Same users for same site but different roles is sufficient. No need for 1 regular user to edit 2 sites.