texts:
I'm running postgresql 9.3. I've currently got WAL archiving configured, but I'm doubting it's utility based on my configuration. On the master, I've got archive_command set to

  rsync -az --ignore-existing %p postgres@slave_replica:/wal_archive

and on the slave replica, I've got restore_command set to

  cp /wal_archive/%f %p || exit 0

If the slave server goes offline, it won't be able to receive the WAL files, and then when it comes back up, won't have what it needs.

Should I instead effectively reverse those two commands: archive the WALs locally on the master, and have slave replica rsync them from the master server? Or should I have the master archive the WALs both to a local directory and rsync them to the slave?


texts: How to generate and insert primary key when using SELECT DISTINCT in OracleDB?

texts:
How do I insert (or generate and insert) a primary key when I use the SELECT DISTINCT query?

If I have a table made like this:

CREATE TABLE testUser(
    username VARCHAR2(50 CHAR),
    email VARCHAR2(50 CHAR)
)

And I had some table customer_orders with this data I could quickly populate this using:

INSERT INTO testUser(username, email)
SELECT DISTINCT user_name, user_email
FROM customer_info;

However if instead I have this table:

CREATE TABLE users(
   id NUMBER,
   user_name VARCHAR2(50 CHAR),
   user_email VARCHAR2(50 CHAR)
)

and try to use the same method (the SELECT DISTINCT INSERT method) I'll get an error like ORA-01400: cannot insert NULL into ("MYDB"."USERS"."ID").

This makes sense since the primary key is null, how can I autogenerate a primary key 1-n where n is the COUNT(SELECT DISTINCT user_name)?


texts: is it safe to create mysql database by just creating its directory?

texts:
context: I want a separate encrypted filesystem per database (to have a separate key) on MySQL 5.7 but if I mount it before  I execute CREATE DATABASE foo it fails with error database exists, and mouting the filesystem after is a bit risky to me.
So I noticed I can "create" a database in MySQL but just creating a folder inside the mysql datadir and creating tables after causes no trouble. I know this is dirty but this way it simplifies the integration with the creation of the filesystem underneath (as it appeared as the folder when lie the tables).
Does creating database this way is safe?


texts:
I have query like this

SELECT user_ID, tanggal, kode_layanan, nomor_antrian, aksi_ID,jam
FROM `transaksi`
WHERE tanggal = '2018-10-11'
  AND kode_layanan = 'A'
  AND aksi_ID BETWEEN 4 AND 9

and the result is like the picture below

what I want to do is do calculation time difference "jam". I know there are similar question, but i think its difference condition. so how to solve query ? thanks


texts: How does an encrypted DynamoDB (the same question applies to RDS as well) query work?

texts:
I am trying to understand whether the primary key is encrypted when I choose to encrypt at rest for AWS DynamoDB

If the primary key is encrypted, how does a primary-key lookup get
performed under the hood?

If the primary key is not encrypted, then I know how DynamoDB is
implemented under the hood, but it seems that it's not entirely safe
for some use cases. Any comment on this?

Furthermore, if I choose to have a Global Secondary Index for my DynamoDB table, are the primary key of the Global Secondary Index table encrypted?

If not encrypted, I kinda understand how it works under the hood, but certainly some of my data get revealed in plain text;

If encrypted, I'd appreciate understanding how it works.


texts: PostgreSQL: How can I list the tables to which a sequence belongs?

texts:
I know I can list all sequences with this:

SELECT * FROM information_schema.sequences;

But I need to know by which table this sequence is used.

The reason I want to do this is to find out which sequences of PRIMARY KEYs are not in the default format of table_name_id_seq

Is this possible?

Note: My PostgreSQL version is 11.5.


texts: How do I completely disable MySQL replication

texts: I am running dual master MySQL replication and now want to move to a single database with no replication. How do I completely disable replication on both databases?


texts: Aggressive Under-Indexing and no data for missing index

texts:
I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every now and then, we get issue of blocking and this blocking gets so severe and their design is so poor that the whole portal goes down unless and until I kill few SPIDs which is holding exclusive lock(mostly).

I have been using sp_blitzindex from almost an year now and a big fan of First Responder Kit provided by Mr. Brent Ozar and Team. When I execute sp_blitzindex against this database where blocking takes place, it says - "Aggressive Under-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits" with the priority of 10. I checked the URL column and also checked other related pages however couldn't get much assistance.

I know that the tables listed here are under indexed and some more indexes need to be created however when I run the same procedure i.e. sp_blitzindex for these objects individually at table level by using below command:

EXEC dbo.sp_BlitzIndex @DatabaseName='db1', @SchemaName='sch', @TableName='tab1';

I don't get any missing index details at all. All the existing indexes are utilized and read count is lesser than write count, only issue which is highlighted here are in the "Lock Waits" column for Primary key.

I have no clue on which column index needs to be created. I also checked sp_blitzlock to see if I could gather some more info which would help however all I see there is lot of deadlocks and same objects are listed which is figured in aggressive under-indexing.

Also checked output of sp_blitzcache by passing sortorder as "reads" and "duration" in this particular database however no missing index request. There are warnings saying "Unparameterized Query"  and "Unparameterized Query, non-SARGables" and these plans involve different set of tables.

Any help is highly appreciated.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)


texts:
Can trcsess utility merge files pertaining to a user session scattered across different processes in a shared server configuration?

Is this tool have this function?


texts: Select ONE most recent post for each author

texts:
I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry.

I use recent versions of PostgreSQL and MySQL.
I have 2 tables:

CREATE TABLE authors (
    id INT,
    name VARCHAR
)

CREATE TABLE posts (
    id INT,
    author_id INT,
    text VARCHAR,
    date DATE
)

I need to select one most recent post for each author. Thanks!

UPDATE

Thanks, both links provide answers to my question with some exception.
All the following queries give the same result (which one is the most efficient btw?) The issue is when there is more than one post from the same author with the same date. Then the returned result set contains all such posts. How should I modify these queries to return exactly one post per author?

SELECT p1.*
FROM posts p1
LEFT JOIN posts p2 ON p1.author_id = p2.author_id AND p1.date < p2.date
WHERE p2.author_id IS NULL
ORDER BY p1.author_id;

SELECT p1.*
FROM posts p1
INNER JOIN (
  SELECT author_id, MAX(date) AS max_date
  FROM posts
  GROUP BY author_id) p2
  ON p1.author_id = p2.author_id AND p1.date = p2.max_date
ORDER BY p1.author_id;

SELECT *
FROM posts p1
WHERE date = (SELECT MAX(p2.date)
              FROM posts p2
              WHERE p1.author_id = p2.author_id)
ORDER BY author_id;

SELECT * FROM (
    SELECT author_id, MAX(date) date
    FROM posts GROUP BY author_id
) p1 INNER JOIN posts p2 USING (author_id, date)
ORDER BY author_id;


texts: InnoDB table size increased substantially after ALTER

texts:
I'm trying to understand why the data size of my table increased so much after performing this ALTER TABLE statement on an InnoDB table:

ALTER TABLE alert ADD COLUMN receiver_id INT(10) UNSIGNED

Note that I used gh-ost to perform the ALTER because the size of the table was rather large. I know that this will create a net new table, copy the rows and rebuild the indexes as data is copied. Based on what I know, I would expect the outcome to be very similar to how OPTIMIZE TABLE works.

Here's some stats from the old table (I got these from SQL Workbench, but they should be calculated from information_schema):

Table rows: 40037398
AVG row length: 8800
Data length: 328 GiB
Index length: 12.9 GiB
Table size (estimate): 341 GiB
Autoincrement: 65447094

And here's a screenshot of the column data from information_schema.COLUMNS, including the data types and encodings:

And here are the same stats for the new table which has the receiver_id column applied to it:

Table rows: 24763900
AVG row length: 18735
Data length: 432.1 GiB
Index length: 12.2 GiB
Table size (estimate): 444.3 GiB
Autoincrement: 65472610

And here's a screenshot of the column data from information_schema.COLUMNS, including the data types and encodings. You should see the additional INT column at the bottom:

The difference in autoincrement makes sense because after the tables had been swapped, new writes were being applied to the latter. The decrease in index length also makes some sense to me, as rebuilding indexes can optimize the storage underneath. Can anyone provide any insight into why Table rows, Data length would change so dramatically?

EDIT 10/4 adds COLUMN data screenshots.


texts: TNS-01190: The user is not authorized to execute the requested listener command

texts:
Oracle 11g Release 2
Oracle Linux Enterprise 5.10

[oracle@dub-ImrORA3 scripts]$ id oracle
uid=502(oracle) gid=502(oinstall) groups=502(oinstall),503(dba),504(oper)           context=user_u:system_r:unconfined_t

We recently changed the oracle id to 502 and the group oinstall to 502.

Everything under the $ORACLE_HOME/bin is owned by oracle:oinstall

Since this change we can only connect locally to the database using IPC. When we try to connect remotely using TCP we the error => ORA-12537: TNS:connection closed

When the oracle user tries to stop the LISTENER we get the TNS-01190 error.

[oracle@dub-ImrORA3 bin]$ ls -al lsnrctl
-rwxr-x--x 1 oracle oinstall 153535 Nov 26 09:45 lsnrctl

Is this an ownership/permission issue? How to resolve?


texts:
I am having a bit of a linked server problem.

I created a SQL Server linked server and it's been working fine for last 2 years and suddenly not working.

SQL Server

  Microsoft SQL Server 2005 - 9.00.5324.00 (X64)   Aug 24 2012 18:28:47
  Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition
  (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Oracle client version

  Oracle - OraClient11g_home1

Error 7302:

  Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server.

We have enabled Allow in Process in SQL Server for the Oracle Provider.

Has anyone got any ideas on this and what can be done to avoid this happening again?


texts:
I have a grouping situation like:

select
coalesce(sector_ind, district_ind, area_ind) as postcode,
....a list of array_agg(..) here....
group by grouping sets ((sector_ind, import_date), (district_ind, import_date), (area_ind, import_date));

I have a query plan situation like:

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 GroupAggregate  (cost=6111398.62..157146473.87 rows=5143123 width=50)
   Group Key: sector_ind, import_date
   Sort Key: area_ind, import_date
     Group Key: area_ind, import_date
   Sort Key: district_ind, import_date
     Group Key: district_ind, import_date
   ->  Sort  (cost=6111398.62..6168661.84 rows=22905288 width=50)
         Sort Key: sector_ind, import_date
         ->  Seq Scan on sales_core  (cost=0.00..962628.88 rows=22905288 width=50)

As I am trying to speed up the whole query I have added indexes like:

create index sales_area_ind_import_date_idx on sales_core (area_ind asc, import_date asc);
create index sales_area_ind_import_date_idx on sales_core (area_ind, import_date);
create index sales_area_ind_idx on sales_core (area_ind);

But nothing seems to change the query plan.

Any idea how I can speed up this kind of query?


texts: barman + backup and restore + how to enable auto deletion from backup files

texts:
I am using the barman tool to backup the postgresql DB

barman is working fine but I notice that backup files that created under
/var/lib/barman/main-db-server/base folder are not automatically being deleted after so many days.

I found the variable last_backup_maximum_age - but this only for reporting

 last_backup_maximum_age
 This option identifies a time frame that must contain the latest backup. If
 the latest backup is older than the time frame, barman check command will
 report an error to the user. If empty (default), latest backup is always
 considered valid. Syntax for this option is: “i (DAYS | WEEKS | MONTHS)”
 where i is a integer greater than zero, representing the number of days |
 weeks | months of the time frame. Global/Server.

I can use

barman delete <server_name> oldest

in order to delete every time the oldest file; however, I want to understand if I've missed something that can automatically delete the files once they reach a certain age; an auto-rotate or auto-delete functionality.


texts: How to store YMD, HM but not S Seconds in mariaDB/MySQL?

texts:
I was trying to make a column that stores the showtime of movies.

Since it is more meaningful to have showtimes in the unit of minutes, can I have a date type that has only years to minutes (eg. 2017-12-26 15:30), but no seconds? Thanks!


texts:
I am trying to rank the table below based on score and class, I have been looking for a way to isolate each class so they can have their unique rank.

This is the main table

Id  Name   Class     Score
1   Stu1   Grade 1   100
2   Stu2   Grade 1   50
3   Stu3   Grade 2   10
4   Stu4   Grade 3   10
5   Stu5   Grade 3   88
6   Stu6   Grade 1   99

For instance, ranking students in Grade 1 should be something like

Name    Rank
Stu1    1
Stu6    2
Stu2    3

Then if i wanna rank Grade 3 Students, it should result to;

Name    Rank
Stu5    1
Stu4    2

I used this code

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( DISTINCT score
ORDER BY score DESC ) FROM scores)
) AS rank
FROM scores WHERE class = 'Grade 1'

but it keeps ranking the entire column which isn't what I want

Kindly help me...I'm really stuck on this.

Thanks


texts: How to access external DLL's static methods that incorporate output parameters in SQL Server

texts:
I know how to load a CLR DLL in SQL Server and access to its static methods which does not incorporate output parameters in their method signatures but what I cannot realize is how I can use methods which have output parameters.

For example it is strightforward to access Math.dll 's factorial method in SQL server like this:

CREATE ASSEMBLY MathAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\Math.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

CREATE FUNCTION Factorial(@x FLOAT)
    RETURNS FLOAT
    EXTERNAL NAME MathAsm.Math.Factorial;
Go

But what if method's signature would be like:

public static int GetInfo(int[] inputParams, out int[] outputParams)


texts: Error join database to SQL availability group

texts:
Can anyone help me with the following problem?  I get the following error when I try to join a database to an availability group

  "Restoring the database resulted in an error.
  (Microsoft.SqlServer.Management.HadrTasks)

  ---------------------------------------------- ADDITIONAL INFORMATION

  Restore failed for Server '[computername]\SQL01'.
  (Microsoft.SqlServer.SmoExtended)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)@EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

  System.Data.SqlClient.SqlError: The operating system returned the
  error '5(Access is denied.)' while attempting
  'RestoreContainer::ValidateTargetForCreation
  on'M:\UserLogs\OperationsManager.ldf'. (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)&LinkId=20476

Background

2 Instances of SQL Server on two servers (Server1\instance1, Server1\instance2, Server2\instance1, Server2\instance2)
SCCM databases on instance 1; SCOM databases on instance 2
Moved SCCM databases to another server
Changed collation on instance 1 on each server to the one used by the SCOM databases; restarted each server after doing this
Detached OperationsManager database from instance 2, copied to instance 1 drives and attached to instance 1
Created SQL Availability group on instance 1 for OperationsManager database
Getting error message listed above when trying to add OperationsManager database to availability group
Have checked permissions on instance 1 folders on both servers, also on share folder used to synch AG; have granted SQL Server instance, SQL Server service accounts (domain user accounts) full access to instance 1 folders and synch folder
Have also made sure that SPNs for instance 1 on both servers are present & correct


texts: SELECT records based on return of COUNT query

texts:
I would like to create a query that returns records if values in a column occur more than once.  I came across other Stack Exchange questions but these only dealt with returning the number of occurrences and not the actual records.
For example, if I have a table of "Names":

Id   Name        City
1    Mike        NYC
2    Bob         DC
3    Rachel      DC
4    Mike        NYC
5    Bob         DC
6    Melissa     LA
7    Melissa     LA

...and I want to get the count of each name, I understand that I use the SQL:

SELECT Name, COUNT(Name) AS "Count"
FROM Names
GROUP BY Name;

The following would be returned:

Name       Count
Bob        2
Mike       2
Rachel     1
Melissa    2

Now, If I want to get the count of each name where the count is more than 1, I do this:

SELECT Name, COUNT(Name) AS "Count"
FROM Names
GROUP BY Name
HAVING COUNT(Name) > 1;

The following would be returned:

Name       Count
Bob        2
Mike       2
Melissa    2

Now, what I want to do is return all the columns for each value if they occur more than once.  I thought about doing a join on the table after doing the counts, but that seems like an expensive operation when I tried it a few times.  The more columns I added, I noticed that it was comparing those too, which is not what I want.  What I'd like is the result below.

Name       City    Count
Bob        DC        2
Mike       NYC       2
Melissa    LA        2

I know there should be an easy way to do this, but I'm just not finding it.  Thanks!


texts: Append-only and snapshots with PostgreSQL?

texts:
I'm working on a small content management system database and I wanted to use an append-only architecture, having snapshots of content (revisions) that can be used at any time, kinda like Git. But unlike git, i don't think it's feasible to store deltas, but just an entire snapshot.

Does anyone know of a way to do this in PostgreSQL in a way that supports relationships? How can i store a snapshot of relations between tables?


texts: How can I only UPDATE if the user was created?

texts:
I got A cursor with some ifs like this one:

 IF @ROLE='RW'
            BEGIN
                SET @COMMAND ='
                USE '+QUOTENAME(@DATABASE)+'
                CREATE USER '+QUOTENAME(@LOGIN_ACESSO)+' FOR LOGIN '+QUOTENAME(@LOGIN_ACESSO)+' WITH DEFAULT_SCHEMA=[DBO]'+CHAR(13)+CHAR(10)+'
                USE '+QUOTENAME(@DATABASE)+'
                ALTER ROLE [DB_DATAREADER] ADD MEMBER '+QUOTENAME(@LOGIN_ACESSO)+'
                ALTER ROLE [DB_DATAWRITER] ADD MEMBER '+QUOTENAME(@LOGIN_ACESSO)
            END

PRINT @COMMAND
EXECUTE (@COMMAND)

UPDATE TBL_LOGINPERMISSAO SET CRIADO =1 WHERE [ID]=@ID

But obviously, even if the user was not created, I update TBL_LOGINPERMISSAO. How can I update only if the user was created? even copying this update on all ifs, it does not work, because even not creating the user will reach the UPDATE.

is this a case for try/catch?


texts: How should I build the architecture of my MongoDB database?

texts:
I have a project about e-Sports. In this project, I have VDS Game Servers for players. With this game servers, our players will play Ranked Games and players will earn Matchmaking Rank Points for their rankings. When 10 players match in web application/desktop application/mobile application; the system will choose a server which one is idle and game will start. After the start of game, the status of Server will need to be changed real-time.

For example, Team A has 9 score and Team B has 10 score. When Team A got one more point, the match status should be [Team A 10 - 10 Team B] and I can get this changed in database.

Server documents look like this:

{
    ip: address.split(':')[0],
    port: address.split(':')[1] || 27015,
    pass: pass,
    live: false,
    map: '',
    maps: [],
    knife: false,
    score: [],
    round: 0,
    knifewinner: false,
    paused: false,
    freeze: false,
    unpause: {
        'TERRORIST': false,
        'CT': false
    },
    ready: {
        'TERRORIST': false,
        'CT': false
    },
    steamid: [],
    admins: [],
    queue: [],
    players: {},
    banner: '',
    pool: [],
    banned: [],
    picked: [],
    stats: ''
};

The Question

Should I use MongoDB for real-time changes (updates) in this project?

Thanks!


texts: Relational Algebra Queries - How to Approach Problems?

texts:
I have the following schema:

employee(sin, emp_name, emp_address, salary, supervisor_sin, dept#)

works_on(emp_sin, project_number, hours)

The question is:

Assume that no employee works on more than 2 projects. For those employees
working on two projects, give the employee name and the two project numbers.
Each employee name should appear once only

My solution so far:

R1 = works_on [theta-join]emp_sin=d.emp_sin [Rename]d(works_on) - gets tuples in the form "emp_sin | project_number | d.project_number | hours | d.hours"?

Result = [Projection]emp_name,project_number,d.project_number(employee [theta-join]sin=emp_sin R1) - gets all names, and 2 project numbers in the form "emp_name | project_number | d.project_number"?

Is this correct thinking? Am I missing something?


texts: Change version number of maintenance plan package

texts:
We have a Maintenance Plan on SQL 2012 that has been running fine. I made a change to it yesterday using SSMS 2014. When the plan ran last night I got the following error:

  The version number in the package is not valid. The version number
  cannot be greater than current version number.

The error seems pretty straight forward - because I edited in SSMS 2014, it upgraded the version number and can no longer run on 2012.

I thought I would fix this by opening the maintenance plan in SSMS 2012 and saving it, however when I try to open the plan I now get an error box pop up:

Is there any way that I can edit this maintenance plan to make it work again, or do I need to either recreate it or recover it from a backup?


texts:
On my desktop I have an Oracle client and and Oracle ODAC installed in different homes. I need ODAC because there ADO components off the Client installation don't work correct with CLOB data.

I think I can't go without the client installation, because I need some odbc components.

Now I have to maintain to copies of the tnsnames.ora file. The nest thing I know to do is carefully keep the both tnsnames.ora in sync.

Are there better solutions for a scenario like this?


texts: Graphical user interface to change data capture on Oracle

texts:
I'm learning how to implement change data capture in Oracle. However, not being a DB specialist but rather a developer, I find the process tedious with respect to other things that I have to do. I end up doing it because my DBA/DevOp don't want to take care of it.

Hence I was wondering if there is any tool that can help set up Oracle change data capture. In other words a simple graphical user interface that would write the all code for me. Creation of change table, PL/SQL script, etc.


texts:
Every couple of days, my server's CPU and memory usage spikes up to 100%, making our vBulletin forum almost unusable for 5-10 minutes. CPU is typically at 1-2, Memory at 3GB. During the spikes, CPU goes up past 40 and Memory at 5GB (max).

There's no correlation with traffic load or cron jobs. The access logs show that hits are consistent during these spikes. No increase in hits. These spikes can happen in the middle of the night when there are less than 20 users online. Pages go from loading in 0.3 seconds to 10+ seconds. When I run top, I see MySQL eating up all of the CPU and memory, with httpd in second place.

I'm on an SSD VPS with 5GB RAM, Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz, usually lightning fast for our mid-sized forum.

MySQL stats from past week:

Uptime: 7 days 3 hours 50 min 12 sec

Max Used Connections:      230/500 (46.00%)
Key Buffer Used:           95.71M/256.00M (37.39%)
Current Key Buffer Usage:  77.97M (30.46%)
InnoDB Buffer Pool:        79.41M/128.00M (62.04%)
Query Cache:               62.59M/90.00M (69.55%)
Table Locks Waited:        25.16k Rate: 0.0/s (0.04%)
--------------------
Query Cache Limit (bytes): 524288
Query Cache Min Unit (bytes): 4096
Query Cache Size (bytes): 94371840
Query Cache Free Mem (bytes): 28736608
Query Cache Used Mem (bytes): 65635232
Queries in Query Cache: 1
Query Cache Avg Size (bytes): 65635232

TOP during normal, fast time (I don't have one from a recent spike)

top - 12:19:21 up 9 days, 22:08,  3 users,  load average: 0.92, 1.50, 2.10
Tasks: 112 total,   2 running, 109 sleeping,   0 stopped,   1 zombie
Cpu(s):  8.6%us,  0.4%sy,  0.0%ni, 90.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.1%st
Mem:   5242880k total,  2269752k used,  2973128k free,        0k buffers
Swap:        0k total,        0k used,        0k free,  1034424k cached

Queries from the past 7 days:

Total          92.95M     150.3/s
  QC Hits      39.96M      64.6/s  %Total:  42.98
  DMS          25.85M      41.8/s           27.81
  Com_         20.12M      32.5/s           21.65
  COM_QUIT      7.03M      11.4/s            7.56
  -Unknown      2.16k       0.0/s            0.00
Slow 10 s          90       0.0/s            0.00  %DMS:   0.00 Log: OFF
DMS            25.85M      41.8/s           27.81
  SELECT       22.59M      36.5/s           24.30         87.39
  INSERT        1.40M       2.3/s            1.50          5.40
  UPDATE        1.30M       2.1/s            1.40          5.04
  REPLACE     509.01k       0.8/s            0.55          1.97
  DELETE       51.48k       0.1/s            0.06          0.20
Com_           20.12M      32.5/s           21.65
  set_option   13.09M      21.2/s           14.09
  change_db     7.02M      11.3/s            7.55
  show_fields   5.16k       0.0/s            0.01

Rows            5.53G      8.9k/s
  Using idx     2.49G      4.0k/s  %Index:  44.95
Rows/question   59.47

Table Locks
  Waited         25.16k       0.0/s  %Total:   0.04
  Immediate      70.33M     113.7/s

Our my.cnf file:

[mysqld]
thread_cache_size=4
table_cache=512
local-infile=0
innodb_file_per_table=1
query_cache_limit=1M
max_connections=500
key_buffer_size=256M
join_buffer_size=1M
query_cache_limit = 512K
query_cache_size = 90M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
max_tmp_tables=1
default-storage-engine=MyISAM
open_files_limit=4974
max_allowed_packet=268435456
innodb_use_native_aio=0
innodb=on
innodb_buffer_pool_size=134217728

Do you see anything that looks like it could be causing these intermittent problems on an otherwise speedy website? We have around 1 million page views per month. 30-60 concurrent visitors at any given moment on a regular day. 60-130 on a busy day. But again, these spikes don't have any correlation with number of concurrent visitors.

Thanks!


texts: MySql Workbench Couldn't find section mysqld in the remote config file /etc/mysql/my.cnf

texts:
if you get this trying to connect to your remote server using MySql Workbench, then try to (on your remote server) navigate to /etc/mysql/my.cnf
and add

[mysqld]

then it should look like:

!includedir /etc/mysql/conf.d/
[mysqld]
!includedir /etc/mysql/mysql.conf.d/


texts: Transfer relational data from one DB to another production DB currently populated with data - MySQL

texts:
Details
So, unfortunately, at work the other day we put a site into production after some editing/changes that still had the development database configuration. As a result, we have several records spanning across 4 tables in the development DB that need to be appended onto the production DB.
Currently I have the necessary data extracted into temporary tables on the development DB.
Table structures
All of the id's are auto-inc
t1 - Contact information - contains contact_id
t2 - Submission information - contains submission_id
t3 - Swing table - contains contact_id & submission_id
t4 - Submission attributes - contains submission_id
Possible solutions
I have tried just to simply use a query like this for each table:
insert into Production.submission (select null, col1, col2, etc from Dev.tmp_submission)

However, I quickly found that this is tedious & very error prone. Records could be inserted while I am working and I may get id's mixed up, etc..
I have thought about adding a very high number to all of the id's in the development data set such as 50000 (the highest id in production out of all of the tables is around 28000). then Just insert all of the data into production. Would MySQL figure out the auto-inc values for all of the tables if I did this or would there be a clash eventually?
Conclusion
How could I get this relational data from the development database to the production database?
Clearly, I am not the most seasoned of DB admins. Would really appreciate some answers! Please, let me know if there is any other information you might find useful.


texts: How far should you go with normalization?

texts: I have a decent amount of data in a database. I have well formed tables and good relationships between them with some redundancy in my data. But how far should I go with normalization? Are there performance drawbacks to too much normalization?


texts:
Just wondering how does this postgres wal archive command works in replication:

rsync -z -a --contimeout=120 --partial-dir=.rsync-partial %p db-slave.sr1.net::primary-wal-archive-share/%f

what does this %p and %f mean here?


texts: Query a view in a procedure in which the view name is a variable

texts:
My problem: I have a set of views from which I want to query dynamically.
I want to load the view names by SELECT * from sys.all_views into a cursor (let's call it view_cursor) and then do something like this in a procedure:

-- loop starts
FETCH NEXT FROM view_cursor INTO @view_name;
SELECT * FROM @view_name;
-- fetch next
-- loop ends

I have already tried this out with one view only which I pass as a parameter to a stored procedure:

CREATE PROCEDURE [dbo].[query_Special_View](@view_name VARCHAR(100)) AS
DECLARE viewname VARCHAR(100);
DECLARE @counter INT = 0;
BEGIN

    PRINT @view_name
    EXECUTE('SELECT COUNT(Value) INTO' + @counter + 'FROM' + @view_name + '');

    PRINT @counter;
END

Then, I wanted to execute the procedure with an existing view name:

EXEC    [dbo].[query_Special_View]
        @view_name = N'A_very_special_LOC_view' -- the view name as parameter
GO

The result is:

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

However the column Value, on which I try to invoke the COUNT() function in the procedure, does exist.

So, how can I write such dynamic queries in stored procedures?
I hope I can do this without any ugly dynamic SQL magic, otherwise I will write my dynmic query logic with Java + JDBC in which I can do such things without any hassle.


texts: How to implement updates from mySQL operational DB to Azure SQL DB for reporting

texts:
We have an operational mySQL DB running on AWS for a transactional system and an Azure SQL DB for reporting with PowerBI. Now I'd like to regularly (e.g. every night) do an update of certain tables from the mySQL DB to the Azure SQL DB.

I found this description on how to do incremental copies using Azure Data Factory, however the alternatives don't seem feasible to me:

Delta data loading from database by using a watermark requires adding watermark columns to the source DB, but I don't want to make changes to the operational DB because it is managed and regularly updated by the transactional system.
Delta data loading from SQL DB by using the Change Tracking technology seems to require an SQL Server DB as the source if I understand this correctly.

The remaining two alternatives apply only to updates from files, not DBs, to my understanding.

Are there other feasible alternatives based on the described conditions? They don't necessarily need to involve Azure Data Factory, however the updates should run completely automated in the cloud.

Maybe a non-incremental update (i.e. full replacement of the target DB tables every time) would be an option too, but I'm afraid that this would lead to high costs on the Azure SQL Server side - please share any experience on that as well, if available.


texts: What does k=v do in the context of INSERT INTO tbl VALUES (expr)

texts:
Coming off this answer, I'm wondering why the lexer allowed it to get all the way down to the geometry grammar in this INSERT statement

CREATE TABLE foo ( x varchar(255) );
> INSERT INTO foo VALUES ( x='foo' );
Query OK, 1 row affected (0.01 sec)

I know that's not right, and as the answer above indicates that it should be INSERT INTO tbl SET, but what's happening? How is MySQL interpreting that? In PostgreSQL, such a syntax is rejected.

test=# CREATE TABLE foo ( x varchar(255) );
INSERT INTO foo VALUES ( x='foo' );

PostgreSQL interprets it as a column reference and errors out, further providing a hint

ERROR:  column "x" does not exist
LINE 1: INSERT INTO foo VALUES ( x='foo' );
HINT:  There is a column named "x" in table "foo", but it cannot be referenced
from this part of the query.

MySQL Seems to check to make sure the field name exists at run time but for what end I have no idea.

> INSERT INTO foo VALUES ( DOESNOTEXIST=5 );
ERROR 1054 (42S22): Unknown column 'DOESNOTEXIST' in 'field list'
> INSERT INTO foo VALUES ( x=5 );
Query OK, 1 row affected (0.01 sec)

If it does not exist you get an error. I'm not sure what happens if it does exist though. NULL is inserted, but why? Why that would be any less of an error. Can someone explain?

I know MySQL has a User-Defined Variable syntax, however I don't think that has anything to do with it here.


texts:
I am trying to create a replica set. I had create a primary node then I am trying to add a secondary node but it gives an error message

  Quorum check failed because not enough voting nodes responde                                            d; required 2 but only the following 1 voting nodes responded:...

Anyone know what the problems is?


texts: Clarification -- Disabling SQL Server 2014 Agent Job in AOAG Secondary replicas

texts:
We have AGs set up (1 primary and 2 secondaries (Includes DR)).

We have an archival job set up in each replica with same schedule and enabled in all three replicas.

This job runs in a frequency of about 15 minutes each day.

My question is:

Since the job in each replica is pointed to primary server while running :

Can I let the job run only in Primary Replica and disable the Job in secondary replicas ? Will it affect otherwise ?
In AlwaysOn AGs will the jobs also be failed over ?
If jobs not failover

My thinking is once the primary is failed over to other node (which is would be primary or current secondary synchronized) and since the secondary replica is now primary and the job will be in disabled state (If at all we can disable the jobs in secondary ?), I just need to enable it on the current replica.

Kindly correct my thinking ?

Regards


texts: How to pass query string containing variable names into mysql stored procedure

texts:
I have this procedure into which I need to pass variable parameters. Problem is this: once inside the procedure, the IN parameter needs to pick up already created variables in order to create the final query.
DELIMITER $$
DROP PROCEDURE IF EXISTS MY_PROCEDURE $$
CREATE PROCEDURE MY_PROCEDURE (
    IN sql_string TEXT
)
BEGIN
    SET @q1 := "SELECT * FROM table1";
    SET @q2 := "SELECT * FROM table2";

    -- capturing the string of the query from input
    SET @sql = sql_string;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

-- calling the procedure
call MY_PROCEDURE('CONCAT(@q1, " WHERE id1 < 10")');
call MY_PROCEDURE('CONCAT(@q1, " WHERE id1 < 10", " UNION ", @q2, " WHERE id2 < 10")');

No matter what I've tried, supplying the @ variable with the IN string results in a syntax error.
I have considered that having 2 groups of PREPARE might do the trick:
First PREPARE converts the IN string to actual query by expanding variables, only return the string (Is this even possible???);
Second PREPARE then runs the query on the expanded string.
Phew!!! Any help?


texts: Database failed to process. Divide by zero error encountered running sp_BlitzIndex

texts:
I am seeing a divide by zero message while running sp_BlitzIndex.  I am running Microsoft SQL Server 2008 (SP3) - 10.0.5775.0 (X64) Enterprise Edition (64-bit) on Windows NT 5.2  (Build 3790: Service Pack 2) (VM)

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1535
SIOP database failed to process. Divide by zero error encountered.


texts: How to store and query products the price of which depends on a complex set of conditions?

texts:
I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB.
Let the products be packages of cable tv providers:
{
    name: "Virgin TV",
    price: 20
},
{

    name: "Sky",
    price: 25
}

Every package comes with some conditions. The Sky package for example may be:

The first three months the price is XXX then it becomes YYY.
If you watch TV after 00:00 is XXX-10.
If you are the N'th customer you have 10% discount.
...

But the conditions for the Virgin package will be different.
1) How do I actually store the products along with all their conditions?
I could only think of creating something like stored procedure for every different condition?
It would be nice if I don't have to normalize all conditions to fit a database schema because new conditions can easily come and go.
EDIT :
I've realized 2 essentials

MongoDB supports anonymous functions as a value of a field.
Map/Reduce can transform a collection to another using a function

Now our the Sky package along with its condition can be easily stored:
db.packages.insert({
    name:      "Sky"
    price:     25,
    condition: function(object, user_input){
        time_discount = user_input.time > 0 and user_input.time < 6 ? 10 : 0;
        price = price - time_discount;
        return 6*price + 6*(price*1.2);
    }
})

Then if we use mapReduce we can query in like:
    db.packages.mapReduce(
        function(){
            emit(this.name, {...some fields..., annual_price: this.cond(this, user_input)});
        },
        function(key, values) {
            return values;
        },
        {out: "tempCollection"}
    ).find()

I'm not happy with the fact the mapReduce output is a an object which stores the transformed values in a single key named "value". Is there another way to transform a collection other than MapReduce?
------- Outdated ------
MongoDB has server-side functions which definition I presume can say something like:
db.system.js.save( { _id : "sky_annual_price" , value : function(price, user_input){
    time_discount = user_input.time > 0 and user_input.time < 6 ? 10 : 0;
    price = price - time_discount;
    return 6*price + 6*(price*1.2);
}});

for the Sky example above.
Yes, in theory looks nice but how can this be used in a query?
------- End of outdated ------

2) How do I query such information so that I can sort by the annual price ?
Any ideas on both 1) or 2) are welcome. I've spent considerable amount of time but I can't figure that out.


texts:
(Repost from StackOverflow)

I have a MySQL 5.7 running on a Windows Server 2016 VM with two disks from a NAS: C:\ for system and D:\ for data (empty at start). I try to create a partitioned table:

DROP DATABASE IF EXISTS `PROFILES`;
CREATE DATABASE `PROFILES` DEFAULT CHARACTER SET latin1;
use `PROFILES`;

CREATE TABLE PROFILES.ACQUISITION (
    Id int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (Id)
)
PARTITION BY RANGE COLUMNS(id) (
       PARTITION p_MAXVALUE VALUES LESS THAN(MAXVALUE)
       DATA DIRECTORY = 'D:\\MyDataBase'
);

Here's the issue:

if D:\ has a capacity of 16TB (value found using dichotomy) or bellow, it works perfectly fine: the D:\MyDataBase\profiles directory is created, and the profiles#acquisition#p_maxvalue.ibd file is in there
if D:\ has a capacity over 16TB, the D:\MyDataBase\profiles directory is created (OK), but the profiles#acquisition#p_maxvalue.ibd file is stored in the default MySQL Data directory\profiles, - on C:). No error message is written in the MySQL logs.

When looking at MySQL internal tables,

select * from information_schema.INNODB_SYS_TABLESPACES where name like '%profile%';

returns one row if D:\ is <=16TB, and nothing if >16TB.

Has anyone has an explaintation and/or a fix? Thanks in advance!

(Bye The way: this is not a innodb_page_size issue, as it is set to default 16K value, and is only related to the maximum size of the files, not to the disk capacity itself)


texts:
I have a an inner query that needs outer query column values as input. It looks something like this

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)
INSERT INTO  TISSET VALUES (1,1,1);
INSERT INTO  TISSET VALUES (1,2,1);
    SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTOuter.COL2 + TTOuter.COL1 ) > 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM TISSET TTInner)
    FROM TISSET TTOuter
DROP TABLE TISSET;

But this raises the following error.

"Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression."

In order to overcome this, after referring to this Stack overflow discussion, I have to modified the code as following.

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)
INSERT INTO  TISSET VALUES (1,1,1);
INSERT INTO  TISSET VALUES (1,2,1);
    SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTInner.COL2 + TTInner.COL1 ) > 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM (
            SELECT TTInner.Col3, TTOuter.COL2, TTOuter.COL1
            FROM TISSET TTInner
        ) TTInner)
    FROM TISSET TTOuter
DROP TABLE TISSET;

I understand that using functions can be very costly, so dont want to create functions.

Please suggest performance factors to consider if there are any to make this better.
I have not applied this yet on the actual table, as that table would contain upto several million rows.


texts: Oracle High-water Mark: how relevant it is when using ASSM tablespace for tables?

texts:
I have several questions:

1) If Oracle manages both High-water Mark and Low High-water Mark in ASSM tablespace then do we have to think about that when thinking about performance? I mean, does Oracle manages deleted blocks itself and does not scan those blocks during table full scan, or do we still have to shrink those tables (alter table mytable shrink space;) in order to clean those deleted blocks and increase performance?

2) If ASSM gives automatisation for this and other related stuff then no doubtfully there's a performance trade-offs when choosing ASSM instead of MSSM. What should be major criteria on choosing ASSM or MSSM?

UPDATE. I did a test on my laptop with mytable (ASSM). First, inserted 1.000.000 records. Query "select count(*) from mytable" was executing at average 0.05 s. Then did full DELETE. The same query was executing at average 0.03 s, so maybe Oracle was still reading deleted blocks. Only after TRUNCATE the query executed at average 0.001 s. Maybe ASSM takes time to shrink table space, or still I have manually to shrink it?


texts: Percona XtraDB Cluster With Async slaves

texts:
I have set up a 3 node percona cluster in amazon ec2. As our client required, i also had to set up an asynchronous slaves in another datacenter as well. Each async slave pointed to single percona cluster node with log-slave-updates and bin-log enabled.

However the async slaves failed to maintain the replication process for long. At least once a week i have to build the slaves from scratch. These async slaves are used only for backup purpose.

Is there any alternative method(best way) of doing this?

Percona Cluster ---> async slaves ?


texts: Equivalence of correlated DATEADD intervals

texts:
Is there any difference between the following statements?

SELECT DATEADD(MONTH, @SomeInteger * 12, @SomeDate)

SELECT DATEADD(YEAR, @SomeInteger, @SomeDate)

In other words, are there any cases in which they would yield different results for the same value of @SomeInteger and @SomeDate?

I ran a quick test via the following:

SELECT
    *
FROM dbo.DimDate dd
WHERE DATEADD(MONTH, 12, dd.Date) <> DATEADD(YEAR, 1, dd.Date)

which returned zero records, but that's far from proving the two statements are equivalent.

The computer scalar execution plan component has the exact same cost in both queries, but the ScalarOperator definitions are different - it would appear that the Query Optimizer is not converting one statement into another.

Thanks!


texts: Left outer join to itself to find outliers?

texts:
I have a table of item types and the coordinates of the items, like this:

    ItemType | X
    --------------
    1        | 1
    1        | 5
    2        | 5
    1        | 6
    2        | 6
    1        | 7
    2        | 7
    2        | 10

I need to find the count of outliers - the items that are not in the vicinity of the items of another type (there may be more than two types, I want to count the outliers for each pair of item types). The vicinity is defined, say, as abs(X1-X2)<=2. That way, the first and the last rows of the table are the outliers.

I tried to run the following query but did not get the expected answer (I expected to get 1 for the pair of 1 and 2 and another one for the pair of 2 and 1).

select t1.ItemType, t2.ItemType, count(t1.ItemType)
from Tab as t1
left outer join Tab as t2 on abs(t1.X-t2.X)<=2
where t1.ItemType!=t2.ItemType
and t2.ItemType is null
group by t1.ItemType, t2.ItemType;

What am I doing wrong? I am using MySQL.


texts: sp_estimate_data_compression_savings for column store compressions

texts:
Is there a way to get compressions saving for column store and column store column store archival compression?

In order to check these compressions saving I have to drop all clustered and non clustered indexes and then to create a clustered column stored index - most of the time this a time consuming operation and I am wondering is there an other way to do this.


texts: Specified key was too long creating primary key

texts:
Simple question: what is wrong here?
Using utf8 (CHARSET=utf8mb4) makes the internal fields larger.
What is the maximum field size for UTF8 keys?
We need to guess?
create table repos(repo varchar (200), PRIMARY KEY (repo));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

How hard is for mariadb to give a better error message?
btw: The answer, by trial and error, is 191 * 4 + 4 = 767.
mariadb Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2


texts: SQL Server 2012: query to see queries that are consuming more resources

texts:
I'm trying to find/build a query to show me those queries that are consuming more resources in SQL Server so that I can optimize them or at least have a statistical idea of what is really happening.

For now I'm using these queries:

select
    er.percent_complete
    ,SUBSTRING(text,statement_start_offset/2+1,(CASE
            WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
            ELSE statement_end_offset
        end - statement_start_offset)/2) AS query_text
    ,se.session_id
    ,se.login_name
    ,se.login_time
    ,er.session_id as 'Vitima'
    ,blocking_session_id as 'Bloqueador'
    ,wait_resource
    ,start_time
    ,er.status,command
    ,db_name(er.database_id) 'Database'
    ,SUSER_NAME(er.user_id) 'Usuario de contexto'
    ,wait_type,wait_time
    ,text
    ,query_plan
FROM
    sys.dm_exec_requests as er
    INNER JOIN sys.dm_exec_sessions as se
        ON er.session_id = se.session_id
    CROSS APPLY sys.dm_exec_sql_text (sql_handle)
    CROSS APPLY sys.dm_exec_query_plan (plan_handle)
WHERE
   se.session_id>50 and se.session_id <> @@SPID

and

SELECT --TOP 10
    SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.TEXT)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) as vQuery,
    qs.execution_count,
    qs.total_logical_reads, qs.last_logical_reads,
    qs.total_logical_writes, qs.last_logical_writes,
    qs.total_worker_time,
    qs.last_worker_time,
    qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
    qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
    qs.last_execution_time,
    qp.query_plan
FROM
    sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
--ORDER BY qs.total_logical_writes DESC -- logical writes
--ORDER BY qs.total_worker_time DESC -- CPU time
--ORDER BY last_execution_time DESC
ORDER BY vQuery

They show similar info, but from different places and with different attributes.

The problem with both queries is that they seem to show only late queries. I've added comments to my queries that are being listed, and these comments aren't appearing, which means that latest queries (for about 15 minutes) aren't still being listed by them.

I'm ok with seeing historical data, as long as I'm able to control how historical it is. It's good to have a long run view.

But still, sometimes, I need to see the current situation. In example, all of a sudden the server starts to get slow, then I wanna see what exactly is doing it right now.

I'm still googling more queries, but I still didn't find anything better than these 2, to show more current data.

Anybody can give me a tip?

Another question. Regarding SQL Server optimization and possible caching, is it troubling to add a timestamp to each sent query? Since I can't see how old a query is, I'm thinking in adding in each query the date and time it was sent to SQL Server. But I'm afraid that SQL Server will lose the ability to see that the same query is being requested over and over, and think each query is brand new, and thus stop doing any optimization it might be doing.


texts: SSIS: Passing variable from child package to parent package

texts:
I have an SSIS project consisting of many packages, including a Main_Flow package which executes the other packages in the desired order.

The first package to be called from Main_Flow is Extract_Archive with a ForEach container that looks for zipped files. If one or more zip file exists, it extracts them in a location and marks a variable ArchiveFileFound to TRUE.

Now back in the Main-Flow package, I would like the next step to be executed ONLY if the ArchiveFileFound from the Extract_Archive is set to TRUE. However that variable only exists in Extract_Archive. I've been looking for information on how to pass variables from one SSIS package to another but I cannot find a good practical and recommended example. Any thoughts?


texts: Get availability group and listener of the current DB (dynamic SQL)

texts:
I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate this a bit:

DECLARE @name VARCHAR(50);
DECLARE @sql nvarchar(4000);
DECLARE db_cursor CURSOR FOR select name from sys.sysdatabases where name not in ('testing_db');

Open db_cursor
Fetch Next From db_cursor Into @name
WHILE @@FETCH_STATUS = 0
BEGIN
     select @sql =
     'ALTER AVAILABILITY GROUP [' + @name + '] REMOVE DATABASE [' + @name + '];' + char(10) +
     'ALTER AVAILABILITY GROUP [' + @name + '] REMOVE LISTENER ' + char(10) +
     'DROP AVAILABILITY GROUP [' + @name + '];' + char(10) + 'GO' + CHAR(10) + CHAR(10) + CHAR(10)

     print @sql;

     fetch Next From db_cursor Into @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

This will give me something as the below for each DB in my instance:

ALTER AVAILABILITY GROUP [**DB1**] REMOVE DATABASE [DB1];
ALTER AVAILABILITY GROUP [**DB1**] REMOVE LISTENER N**<>**
DROP AVAILABILITY GROUP [**DB1**];
GO

I cannot find a way to get the availability group and its listener for each DB in each run (The stuff between ** ** needs to be replaced by the AG name/AG listener accordingly). I have searched how to get an AG name/listener for a DB but all of the solutions require a lot of joins which will complicate this query. What's the neatest way to do this?


texts: Help with designing a multi relational schema / domain model

texts:
I am in the process of developing a web application with asp.net MVC. I am using Entity Framework to make a connection to my database.

I am having trouble with a specific concept in my program. I will explain below.

I have a Network class which resembles a network in real life which has a frequency.

I then have a Block class. A block is a certain section of devices on a networks.

I also have a Customers Class.

Here are my current Models of the above classes:

Network:

  public class Network
{
    public int networkID { get; set; }
    public String name { get; set; }
    public int startCode { get; set; }
    public decimal frequency { get; set; }
    public virtual List<Block> blocks { get; set; }
}

Customer:

 public class Customer
{
    public int customerID { get; set; }
    public String name { get; set; }
    public String shortName { get; set; }
}

Block:

 public class Block
{
    public int blockID { get; set; }
    public decimal start { get; set; }
    public int end { get; set; }
    public int blockSize { get; set; }
    public Customer customer { get; set; }
}

Ok now my problem comes with this.

A network has many blocks.
Each block belongs to a single client.
A client may have many blocks in one network, and can also have many blocks in many networks.

Is my above configuration correct / sufficient to store my certain scenario. But I also then need to be able To browse a client and query all his blocks which he has.


texts: Performance tuning for multi-tenant database

texts:
The database, MS SQL 2014 Standard Edition, is becoming a bottleneck in our multi-tenant web application. Aware that most performance issues can be solved by making code more efficient, however looking beyond that now.

When load is high, the database seems to grind to a halt. At seemingly random times, it takes a massive slowdown (thinking table locking when one tenant inserts a load of data). Other times, we see deadlocked transactions. In the latest slowdown, a big chunk was deleting single rows from this table with queries like DELETE FROM tableX WHERE ID = x, resulting in a lot of Transaction (Process ID 183) was deadlocked on lock resources with another process and has been chosen as the deadlock victim errors.

Which of the below strategies would you recommend, if any?

Changing clustered indexes from the auto-incrementing int PK to
being tenantID + id. (note all tables have tenantID column and
already a non-clustered index on tenantID):

This would be an attempt to stop page/table locks due to activities from one tenant from affecting all other tenants. This seems recommended practice for multi-tenant DBs. Otherwise if a tenant inserts 4,000 rows or similar this seems to lock the table.
Changing from default
isolation level to Read Committed Snapshot so writes don't block
reads and vice versa
Moving to faster physical disks - currently
on a RAID 10 array of SSDs, looking at the Intel NVMe PCI-e SSDs

We use SQL Mirroring with High Safety Mode as we cannot lose a single booking/row due to a hardware error. Was under the impression all writes are written to disk, and the transaction won't complete till written to disk on the mirror. Networking between the two servers is sub 1ms and no contention. There's plenty RAM in the machine (64GB and doubling to 128GB in a few days), but does host a second multi-tenant DB (~40GB) for a different product.


texts: DB model strategy to override field value

texts:
We have to store some dynamic configuration in JSON format in DB. There would be some default configuration, and it can be overriden per account. Can someone help me with the strategy to store such kind for data.

I have come up with following. If the accountConfig is null then look at defaultConfig.

DefaultConfig table

id, name,   config
1   name1   {x:12, y: 13}
2   name2   {y:10, z:100}

AccountConfig table

id, accountId, defaultConfigId, accountConfig, active
1   1000       1                null           false
2   1000       2                {y:11, z:111}   true

Should I have inheritance, or embedded entities? or anything else?


texts: Can changes to cardinality optimization be made without taking users offline?

texts:
I have an old SQL Server database that has been migrated a few times since < 2005. Some queries are running very slowly. I have been advised that the following changes could help performance:

Increase the database compatibility level from 90 (2005) to highest possible (the software itself does support the highest).
Set Legacy Cardinality Estimation to ON.
Set Query Optimizer Fixes to ON.

Can these changes be made without taking users offline?


texts: Trying to return empty rows even if data does not exsist in SQL

texts:
I am trying to return the last changed time for a list of users, but i would like to return a row even if the a last changed time does not exist

select  user_id,
        last_changed_time = MAX(ISNULL(change_date_time,0))
from audit_log_tx
where user_id in (users)
group by user_id
order by user_id asc


texts:
I need to store substantial amounts of data per node in Neo4j. The data is Unicode chunks of text. Actually not every node will have big chunks, but many of them will.

I waded through the documentation but didn't find any mention on the Node size – the amount of data a single node can contain.

Does anyone have any idea?


texts: what are the conditions under which mysql replication might break?

texts:
I am running mysql server in master/slave configuration mode. There are multiple slaves replicating from a master. They are working fine and their replication status is monitored.

One of the slaves serves for a special purpose. The devs have given me a set of update queries to be run on replicated slave db on slave A.

They are updating old records in a table in a db (modifying a column data for lot of records).

Questions

Is there any way I can figure out if the given query will break the replication ?
For the future, how do I determine if the query will break the replication or not ?


texts: Combining a tree structured Group model with its HABTM User model in one VIEW

texts:
I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a Has-and-Belongs-to-Many (HABTM) relationship using the join-table groups_users

I want to create a view that creates a new tree which includes all the groups and all the users as children of the groups they belong to. The view should have the columns id, parent_id, model, foreign_key, lft and rght.

The id column should just be the Nth row and parent_id, lft and rght can't be just copied from the Group model, they will have to calculated for the tree of the view.

An example of what the view could look like:

| id | parent_id | model | foreign_key | lft | rght
----------------------------------------------------
|  1 |    NULL   | Group |      1      |  1  |  14
|  2 |     1     | User  |      1      |  2  |   3
|  3 |     2     | Group |      2      |  4  |   7
|  4 |     3     | User  |      3      |  5  |   6
|  5 |     4     | Group |      3      |  8  |  13
|  6 |     5     | User  |      2      |  9  |  10
|  7 |     5     | User  |      4      | 11  |  12
|  8 |    NULL   | Group |      4      | 15  |  20
|  9 |     8     | User  |      4      | 16  |  17
| 10 |     8     | User  |      5      | 18  |  19

How do I do this?

Can it be done with some super complex SELECT statement or do I need to use a procedure?

I'm not super skilled at MySQL; I can do simple SELECTs and JOINs, but this is way beyond my skill level.


texts: Will (ROWLOCK,UPDLOCK,READPAST) with ORDER BY non-clustered columns work?

texts:
I have a SQL code as below

#table creation
CREATE TABLE TAB
([i_task_id] [int] IDENTITY(1,1) NOT NULL,
[i_priority] [tinyint] NULL,
[i_status] [smallint] NULL
CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED
(
[i_task_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

#non-clustered index on i_status
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name = 'IX_status' AND
OBJECT_ID = OBJECT_ID('TAB'))
BEGIN
CREATE NONCLUSTERED INDEX IX_status
ON [dbo].[TAB] (i_status)
END

#values in the table#
INSERT INTO TAB(i_priority,i_status)
VALUES (31,1),(30,1),(31,1),(20,1),(10,1)

#select query#
SELECT TOP 1 i_task_id
FROM TAB with (ROWLOCK,UPDLOCK,READPAST)
WHERE i_status = 1
ORDER BY i_priority desc,i_task_id asc

Locking does not work while executing the above query in 4 different sessions. I get result in one session where as nothing is returned in other 3 sessions.

When i tried to change the ORDER BY condition as below

 ORDER BY i_task_id asc,i_priority desc

then the query worked and returned 4 different results in 4 sessions.

My requirement is if several users try to access the same select query at the same time, there should not be any deadlock situation(i.e each user should be provided the unlocked row).Also,each user should be given different task id in different sessions.

Will the locking with order by non-clustered columns work?

Thanks for your time!


texts: The multi-part identifier > "a.surveyid" could not be bound

texts:
I am getting the error :

  Msg 4104, Level 16, State 1, Line 1 The multi-part identifier
  "a.surveyid" could not be bound.

Below is the query ,what could be the issue?

 SELECT a.surveyid,*
    FROM [360HRSurvey] where sempid IN (
     SELECT  a.EmpCode
     FROM surveyusers a
     where a.surveyid = 80 and a.EmpCode NOT IN (
      SELECT p.EmpID
      FROM empsurveyselection p
      WHERE p.surveyid =80)
    and empid =   (
      SELECT empid
      from [360HRSurveyEmployee]
      where surveyid = 80)
    )


texts: Create a log file for check which time import has been done and to split the import files

texts:
How to create a log table for when does the last import has been done
and also for
Query for split the records in to 1000 while importing. to avoid the bulk insert
For example :
If the data contains 10000 records means i want to import 1000,.... records one by one
and finally 10000 should be updated after the import fully completed


texts:
I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest.

Consider a table posts

posts
{
    id  int(11),
    user_id  int(11),
    url varchar(256),
    image varchar(256),
    source int(11),  // Social Network Source
    created bigint(20), // Publish time of the post
    visible int(1) // Public or Private Posts
}

Using InnoDB

Indexes :

primary key on id
user_id,source
user_id,created

Space usage

Type    Usage
Data    45,876.0    MiB
Index   4,959.0 MiB
Total   50,835.0    MiB

Using Amazon RDS with 7.5 GB RAM.

The queries I am executing are as follows

select id
from posts
where user_id={user_id}
  and visible=1
order by created desc
  LIMIT 20 ;

select id
from posts
where user_id={user_id}
  and source={your_network}
  and visible=1
order by created desc
  LIMIT 20 ;

For users with > 500k posts sometimes my queries take a really long time.

I have run explain and you can see the results below

EXPLAIN SELECT id
FROM  `posts`
WHERE user_id =123529745 and source=1 and visible=1
ORDER BY created DESC
LIMIT 20

id    select_type       table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE          posts   ref     user_id_2,user_id_4,user_id_3,created   created 4       const   954174  Using where; Using index

id    select_type       table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE          posts   ref     user_id_2,user_id_4,user_id_3,created   created 4       const   742308  Using where;

Can you please suggest me any optimizations?


texts: Best way to define related resources db model

texts:
We have different type of resources (docs, events, media,...) each one has each own table to store related information for each resource, having their respective PK-id.

We have the need to manage the relation between different resources, it is, a document could have, for example, two events and one media collection related to it.

Our first idea is have the following schema:

resource_id | resource_type | related_id | related_type

We need to know the type of the resource because different type of resources could have the same id.

A tuple example of the approach:

resource_id | resource_type | related_id | related_type
-------------------------------------------------------
1           | document      | 1          | event
1           | document      | 5          | event
1           | document      | 33         | media

The relation could be bidirectional or directional. Then, for example this record could also be valid

33          | media         | 1          | document

Then,
 - when displaying the collection with id 33, we will find the document related (doc_id:1)
 - when displaying the document with id 1, we will find the related collection and other two events (media_id:33,event_id:1,event_id:5)

To know the details of the related resources (title of the media collection, for example, we will query the table of media collections)

I think that schema could cover our need, but since I am not a DBA guru, I would appreciate to know if this approach is good enough or there are other ideas which cover this need better in terms of performance, flexibility, ...

EDIT: Adding the schema of the mentioned approach

CREATE TABLE `related_resources` (
  `fk_resource_id` int(11) NOT NULL,
  `resource_type` varchar(64) NOT NULL,
  `fk_related_id` int(11) NOT NULL,
  `related_type` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `related_resources`
--
ALTER TABLE `related_resources`
  ADD PRIMARY KEY (`fk_resource_id`,`resource_type`,`fk_related_id`,`related_type`);


texts: How to configure mongodb replica set with redundant networks

texts:
I need to set up a mongodb cluster with redundant private networks connecting all the nodes.  Mongo's documentation states:

  ... you should place each mongod instance on a separate host server serviced by redundant power circuits and redundant network paths.

https://docs.mongodb.com/manual/tutorial/deploy-replica-set/

I am able to make mongod listen on multiple nics using the --bind_ip parameter, which I set to:

--bind_ip localhost,10.1.1.101,10.1.2.101

But then when doing rs.initiate()/rs.reconfig()/rs.add() I see no way to specify multiple IP addresses for the members[n].host fields.  I tried multiple values separated with a comma:

> rs.initiate({ _id : 'rs', version : 1, members : [{ _id : 0, host : '10.1.1.101:27107,10.1.2.101:27017' } ] })
{
    "ok" : 0,
    "errmsg" : "FailedToParse: More than one ':' detected. If this is an ipv6 address, it needs to be surrounded by '[' and ']'; 10.1.1.101:27107,10.1.2.101:27017 for member:{ _id: 0.0, host: \"10.1.1.101:27107,10.1.2.101:27017\" }",
    "code" : 93,
    "codeName" : "InvalidReplicaSetConfig"
}

I tried making host an array:

> rs.initiate({ _id : 'rs', version : 1, members : [{ _id : 0, host : [ '10.1.1.101:27107', '10.1.2.101:27017'] } ] })
{
    "ok" : 0,
    "errmsg" : "TypeMismatch: \"host\" had the wrong type. Expected string, found array for member:{ _id: 0.0, host: [ \"10.1.1.101:27107\", \"10.1.2.101:27017\" ] }",
    "code" : 93,
    "codeName" : "InvalidReplicaSetConfig"
}

I tried specifying host multiple times, which mongo accepted, but it only used the last host specified.

> rs.initiate({ _id : 'rs0', version : 1, members : [{ _id : 0, host : '10.1.1.101:27107', host: '10.1.2.101:27017' } ] })
{ "ok" : 1 }

All this is just getting the first node set up.  I had similar issues when adding nodes 2 and 3 with rs.add() and rs.reconfig().

Any help would be much appreciated.


texts: Does Page verification require downtime?

texts: So I used sp_blitz and came across  a suggestion to change verification to checksum. What I want to know is, would this step require any downtime?


texts: SQL Server 2008 - How to check FK integrity?

texts:
I'd like to pull a partial dataset from production for test environments - e.g. Copy the production database with only the first 100 rows of each table or 5% of the data - which ever comes first. Simple enough. For each table, just... SELECT TOP 100 * INTO DESTINATION FROM SOURCE"

The problem is maintaining data integrity for FK. Is there a native SQL Server SP/FX to verify the integrity of each row's FK after the data has been copied over?

If not, once the production schema is copied to sandbox environments, I was planning to implement the following logic:

PSUEDO:

For each source table{
 While (Destination Tbl =<100 rows/5% of Source Tbl COUNT(*)) {
   If FK exists on source tbl{
     For each FK, query referenced FK tbl and insert results into FK's corresponding destination tbl
   }
  Insert row from source tbl to destination tbl
 }
}

I don't want to recreate the wheel or make it harder than it needs to be. If you see any holes in my logic, please let me know. Thanks in advance.


texts:
related question: https://stackoverflow.com/questions/65165530/aws-dynamodb-table-design
I am trying to design a schema that will support the use cases (with 1 query per use case):

"get all items(including all attributes) with assigned_user = user_id"
"get all items(including all attributes) with owned_user = user_id"
"get all items(including all attributes) by item_ids"
"get all users(including all attributes) by user_ids"

note that:

an item can be assigned for multiple users.
I do not want to use a filter, I am trying to create a "Global secondary index" that will solve it.
another (less preferable) possibility is to find a query that will get all the items ids from the user and then query the ids - but I want to do it in 1 query
relationship between Users and Items is many-to-many

table:

user: p_key: "user_id" , s_key: "type", attributes: {owned_items: set<user_id> , assigned_items: set<user_id>, ...}
item: p_key: "item_id" , s_key: "type", attributes: {metadata: string,  item_assignees: set<user_id>,  item_owners: set<user_id> ,
...}

any suggestions?


texts:
Admin creates an Oracle directory:

CREATE OR REPLACE DIRECTORY EXAMPLE AS '\\server\share\test';
GRANT READ, WRITE ON DIRECTORY EXAMPLE TO JOHN;

Admin can test whether the directory exists:

SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='EXAMPLE'

How can John verify whether directory EXAMPLE exists? How can he find out what permissions he's been granted?


texts: List primary keys for all tables - Postgresql

texts:
Is there a query that will do that?

I found some queries that can do this for one table, but I wasn't able to modify it so I can see:

tablename | column | type


texts: Which version of Microsoft Tape Format is supported by which MS SQL Server

texts:
When you create a backup with Microsoft Sql Server, the resulting file is in Microsoft Tape Format.
Different versions of SQL Server apparently use different version of this format.

Which version of SQL server is required to restore a given file with a specific format version?

Can I somehow influence the version SQL server uses when creating a backup?

Background:
I am currently trying to restore a database backup created with an unknown version of SQL server, probably 2012. I have SQL Server Express 2008 R2. When executing the command

RESTORE VERIFYONLY FROM DISK='C:\myfile.bak'

I get the error

The media family on device 'C:\myfile.bak' was created
using Microsoft Tape Format version 1.8.
SQL Server supports version 1.0.

I guess this is simply a case of being unable to restore a database with an older version of SQL server, and there is nothing to be done about that besides upgrading. However I found no information about which MS tape format version corresponds to which sql server, and the error message is not very enlightening.

Therefore, in order to understand such error messages, I am interested in some general information about this format, in particular the exact correspondences between tape format version numbers to SQL Server version numbers.

Update:

It seems this is not simply a problem with the wrong version of SQL Server.

Restoring on SQL Server 2012 Express failed with the same error.

When executing a RESTORE HEADERONLY the resulting headers where

BackupName: *** INCOMPLETE ***
Position: 1
Every other column: NULL

I am currently in the process of acquiring another copy of the file.


texts: How can i return the max of a sum by each team?

texts:
I have a lot of teams and i want to know the maximum of sum total of each team.

This's my query :

SELECT campaign_id,
       campaign_identifier,
       team,
       campaign_name,
       Month(time) AS month,
       Sum (total) AS Total
FROM   campaign
WHERE  Year(time) = Year(Now())
       AND Month(time) = 12
GROUP  BY campaign_identi,
          team

Thanks.


texts:
I added lines to ~/.my.cnf which worked fine for weeks. Now I receive complaints when I run myqsl CLI.

System specs:

MAC OS X - El Capitan 10.11.13

Mysql Version:

mysql  Ver 14.14 Distrib 5.6.29, for osx10.11 (x86_64) using  EditLine wrapper

Contents of ~/.my.cnf:

[client]
user = root
password = ***REDACTED***
host = 127.0.0.1

table_definition_cache = 400
table_open_cache = 400

These are the error messages I get when I type 'mysql' in bash:

  mysql: unknown variable 'table_definition_cache=400'

  mysql: unknown variable 'table_open_cache=400'

I only get the second error message if I comment out the first setting.

I needed these settings because otherwise I get these errors intermittently:

Lost connection to MySQL server at 'sending authentication information', system error: 32

UPDATE:

This link suggested the solution: https://stackoverflow.com/questions/17813630/mysql-5-6-headaches-on-mac-osx

I needed to put the settings in the [mysqld] section of the config file.


texts:
I want to Replicate my PostgreSQL, in the website there far too many options, I want to know which one to choose.

I have a main server which the database is stored at. Inserting, editing and deleting or records occurs on this server. The second server contains a replica of the database which accessed only for search.

Which type of replication is this, and which option to choose ?


texts: Overwriting MySQL database to only store 1 month of data

texts:
We are logging data on hardware with only small memory (short on disk storage) only 4GB.

We only require the data to be stored for 1 month and then be over written in a way that it overrides older data first. The memory on hardware is very small so cannot continue to record indefinitely.

We are using a MySQL data base, the hardware it is running on is not always powered on as it is in a vehicle. The data will be viewed in a graph to show historical data over time.

A few options I have thought of but not sure how to execute it:

Let’s assume I will record 1 million rows of data in a month

When the table (table1) gets to 1 million rows, move this table to another and start new table (table2). When table2 reaches 1 million rows. Delete table1, move table2 to new table and create table3 etc...

This way there will be minimum 1 month of entries.

Second option (not sure if possible):

When the table gets to 1 million rows it starts to override from row 1 again.


texts: MySQL: What happens to the row when skip-errors=1062 is set

texts: I found out that I can set skip-errors=1062 in the my.cnf but I need to know what happens to the row when the error happens. Does it get silently dropped? Or is that config option the equivalent to STOP SLAVE;SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;


texts:
I'm teaching a class in which I want to demonstrate to my students that queries are not guaranteed to return results in a specific order without an explicit ORDER BY. I've observed that SQL Server seems to return ordered results even when ORDER BY is not present in the query. This is a problem because I'm concerned that my students will become confused on this point.

Examples of observed behaviour:

By default, data appears to be ordered in primary key order, even if the primary key is nonclustered.
If I use GROUP BY, the result set appears to be in order of the last column.
For example GROUP BY this, that ends up with the data in order of that, which is weird, especially if there is no index on it.

I haven't observed this behavior in other databases such as MySQL, PostgreSQL and SQLite. Is there something fundamentally different about SQL Server which causes it to order results for some queries even without an ORDER BY?

Now it seems to me that ordering the data when not asked is an extra unnecessary step which wastes processing time. Is that true?

And is there some kind of global setting that disables this behavior? Is there a way to get SQL Server to stop ordering data?

It also makes it harder to demonstrate to students that data is not automatically ordered.

Can anyone give me a simple demo script that shows the order of the results changing from execution to execution?


texts: Creating a schema for various sizes of varchar

texts:
I am redesigning a database schema for an application I am working on, and one of the major changes I am adding is the ability to dynamicly add new "fields" into a form based administration area.

Basically, a user can add a "large" field (varchar 4000), or a "small" field (varchar 255), and assign the field a name. The idea here is so I do not need to edit the code/database everytime we want new data to be featured

What would be the best way to incorperate this into a database schema? Doing something like adding a single varchar 4000 column would work, but that seems wasteful and I am looking for better ways.

Thanks


texts: Return all rows containing specific value in json array

texts:
I have the following table:

CREATE TABLE [dbo].[Journals](
    [Id] [nvarchar](450) NOT NULL,
    [CorrelationId] [nvarchar](450) NOT NULL,
    [Version] [int] NOT NULL,
    [Type] [nvarchar](max) NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
    [Created] [datetime2](7) NOT NULL,
    [Updated] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Journals] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Journals] ADD  DEFAULT (getutcdate()) FOR [Created]
GO

ALTER TABLE [dbo].[Journals] ADD  DEFAULT (getutcdate()) FOR [Updated]
GO

One row could look like this:

Id: 1833eac9-3ea7-4613-be26-af3f7589d6f0
CorrelationId: 0HLNKM700ILBN:00000001
Version: 1
Type: Notification
Data: [{"type": "Event_Received","timeStamp": "2019-06-24T12:47:54.3960942Z","data": {"version": 1,"notificationName": "My Notification","receiverName": "John Doe","receiverUserIdentifier": "123456789","receiverEmail": "john.doe@example.org","senderName": "Some Sender","senderEmail": "someone@example.org","contentSubject": "This is my subject","contentBody": "And this is my body","attachmentNames": []},"sensitive": true}, {"type": "Event_ValidationSucceded","timeStamp": "2019-06-24T12:47:54.4167034Z","data": "{}","sensitive": false}, {"type": "Event_PublishStart","timeStamp": "2019-06-24T12:47:54.4691455Z","data": "{}","sensitive": false}, {"type": "Event_PublishSucceeded","timeStamp": "2019-06-24T12:47:54.4740542Z","data": "{}","sensitive": true}]
Created: 2019-06-19 12:29:20.8100000
Updated: 2019-06-19 12:30:51.1123311

I want to fetch all the Ids from the rows that contains array entries where Sensitive = true

So I have tried something like this:

SELECT Id
FROM Journals journals
CROSS APPLY OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive') as jsonValues
WHERE Sensitive = 1

This works but I get duplicate ids back (since the array contains more than one item where Sensitive = true).

How can I limit the query so that I don't receive duplicate Ids when the array contains more than one item where Sensitive = true?

Wanted output:

1833eac9-3ea7-4613-be26-af3f7589d6f0

Actual output:

1833eac9-3ea7-4613-be26-af3f7589d6f0
1833eac9-3ea7-4613-be26-af3f7589d6f0

I guess that I could add a DISTINCT to SELECT DISTINCT Id but that feels like cheating, or? :D


texts:
I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table.

I split 150 mln row into 3 BCP Export files each of 50 million rows and 3 BULK INSERTS. It worked (15 min BCP out/ 40 min Bulk Insert), but I would rather export just one file and import as well one file.

Is it technically possible or I can have issues for the big amount of rows? I never Imported more than 50 mln rows from a BCP file and I am not sure how this can work out. Is there a limit for BCP export or Import?

Thanks,


texts: Make a non-ordinal numeric ID in PostgreSQL

texts:
If I declare a column to be serial in PostgreSql, it will auto-increment in a ordinal sequence.  The end result may not be contigious due to rollbacks and whatnot, but it's more or less ordinal.

I have a user table where the user id's are set as serial, and are thus incremented starting from 1... but I'd rather they weren't, as it seems like asking for a sequence number attack of some kind.

Is there a relatively easy, relatively performant way to create an automatic artificial ID in PostgreSQL such that the ID's are randomly spaced apart, and you shouldn't expect that ID 987654321 follows ID 987654320?


texts: Transaction log is full (due to NOTHING)... but this database is in simple recovery mode

texts:
I'm supporting an antedeluvian webapp (soon to be retired) that still uses "aspnetdb" for its auth system.  I was doing some work in prep for its retirement on my test environment, when I found my test server complaining with the following error:

  The transaction log for database 'aspnetdb' is full due to 'NOTHING'.

Now, normally I'd assume the problem came from the database transaction log... but this database was recently switched into simple recovery mode (our admin got sick of us complaining that the test-SQL-server was out of space and so she switched it to simple recovery).

I've tried a few experiments with no luck, and done a fair bit of googling.  Other answers talk about growing the size of the transaction log... but all options related to the transaction log files and autogrowth are greyed out in SSMS - does the transaction log file even exist?  I've tried setting the transaction log to ulimited size through

alter database aspnetdb modify file (NAME = 'aspnetdb_log', maxsize = UNLIMITED, FILEGROWTH = 100MB)

but that just fails with the same error.

Anybody seen this error before?  Full transaction log on a database in simple recovery mode?

It's on SQL Server 2016, running in 2008 compatibility mode because aspnetdb is that old.


texts: MongoDB replicate data item across multiple shards

texts:
Is it possible to replicate a data item across multiple shards?

My end goal is to determine how up-to-date the data in a replica slave is. I'm only concerned with subsets of the data, so I don't think examining oplogs will work. Instead, I plan on having a counter increment in the master, every time a modification to the appropriate data occurs. Then, I can fetch the slave's replicated counter to see how current it is.

The problem occurs when I try to shard the data. I need the counter replicated across every replica, not just the replica set that owns the counter data item. So, I need some way to replicate the counter to every master shard, where it will then be replicated to each slave. Is this possible?

Modifications to the counter would still go through the master shard that "owns" the counter. Data reads would need to aggregate the counter value from each shard/replica, taking the minimum value. I read through the MongoDB documentation, and I really don't think it's possible. But perhaps one of you MongoDB experts/developers can shed some hope.

Someone on SO mentioned replSetGetStatus, which may be an option. However, I would like separate counters for different subsets of the data, where the replica status corresponds to all the data.


texts: How to find out what caused a schema change?

texts:
I am investigating some performance issues on my SQL Server 2008 R2 server. Some queries are being recompiled due to a "schema change". I am pretty sure no one is changing the schema. All databases on this server are full text enabled.

The question is: how can I find what is causing the schemas to change? Is there any query oy profiler column that can tell me what is going on?


texts:
We have a 300 GB database that I am test restoring to a new SQL Server 2016 instance, on a virtual Windows 2016 server, used for testing purposes.

The VM has 32 GB RAM, running on a 1 socket CPU with 10 logical processors. (E5 - 2670 , 2,6GHz). Not sure about the power setting, will check that with the guys who set up the VM.

The first time I restored, I did it from a .bak- file from a 2014 instance.
The second time (still running) I did it from a backup made on the SQL Server 2016 instance.

Both times it took over an hour before the "5 percent processed" message was shown. Before that the percentage was on 0% (using a script that shows progress of restore). The restore was finished after about 2 hours the first time.
I especially wonder why it takes so long for the process indicator starts going. What is going on in when that stays on 0%?

Is this is normal on a SQL Server 2016 instance, or could something be wrong? On the production server, a restore will go much faster, but it is difficult to compare since the prod is a dedicated server with multiple spindles, while the test-server is a virtual server.


texts: please help starting mariadb galera cluster node

texts:
I am getting thsi error:
Starting MariaDB 10.4.17 database server...
WSREP: Failed to start mysqld for wsrep recovery: '2020-11-23 21:48:20 0 [Note] /usr/sbin/mysqld (mysqld 10.4.17-MariaDB-1:10.4.17+ma
2020-11-23 21:48:20 0 [Note] InnoDB: Using Linux native AIO
2020-11-23 21:48:20 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-11-23 21:48:20 0 [Note] InnoDB: Uses event mutexes
2020-11-23 21:48:20 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-11-23 21:48:20 0 [Note] InnoDB: Number of pools: 1
2020-11-23 21:48:20 0 [Note] InnoDB: Using generic crc32 instructions
2020-11-23 21:48:20 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
2020-11-23 21:48:20 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
2020-11-23 21:48:20 0 [Note] InnoDB: Completed initialization of buffer pool
2020-11-23 21:48:20 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page
2020-11-23 21:48:20 0 [ERROR] InnoDB: Upgrade after a crash is not supported. This redo log was created before MariaDB 10.2.2.
2020-11-23 21:48:20 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-11-23 21:48:20 0 [Note] InnoDB: Starting shutdown...
2020-11-23 21:48:21 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-11-23 21:48:21 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-11-23 21:48:21 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-11-23 21:48:21 0 [ERROR] Unknown/unsupported storage engine: innodb
2020-11-23 21:48:21 0 [ERROR] Aborting'

this is my galera config:
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.0.5,10.8.0.1,192.168.0.1"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="192.168.0.1"
wsrep_node_name="node3-cluster"

the first and second node are working fine this 3rd node didnt pick up
if I delte the mysql data dir and restart fresh will this third node pick up from the other 2 nodes ?
mariadb Ver 15.1 Distrib 10.4.17-MariaDB, for debian-linux-gnu (aarch64) using readline 5.2
node3-cluster:~$ exit node1-cluster:~$ mariadb --version
mariadb Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnu (aarch64) using readline 5.2
node2-cluster:~ $ mariadb --version
mariadb Ver 15.1 Distrib 10.3.25-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2


texts: Indexing only for most recent entries in PostgreSQL

texts:
Consider the following table:

|-----------------------------------------------------|
| raffle                                              |
|----|---------|----------|-----|---------------------|
| id | shuffle |  user_id | ... |           notify_at |
|----|---------|----------|-----|---------------------|
| 1  | 4D6G8Z1 |      542 | ... | 2019-12-01 14:00:00 |
| 2  | 64G264D |        6 | ... | 2019-12-28 14:00:00 |
| 3  | 4IPF93D |       58 | ... | 2020-01-01 14:00:00 |
| 4  | D25LF03 |       58 | ... | 2020-01-14 14:00:00 |
| 5  | G04LDWE |      684 | ... | 2020-03-02 13:00:00 |

In this table, most requests are not done to the id column, but to the user_id and notify_at, which is a 64-bit timestamp (no 2038 Bug):

SELECT *
  FROM [raffle]
 WHERE [user_id] = ?
   AND [notify_at] = ?

The table grows by the minute, but that is not the problem, but rather, the records for the notify_at in the current month are most accessed than the rest. In 10.000.000 records, an index of the user_id and notify_at sums 160MB, which only 1% of these are heavily accessed.

Is there a way to optimize an index (or any other strategy) to make retrieval of the records for the current month snappier (as in, "trying to use the index instead of sweeping the whole table for records)?

Update 1: I'm asking this way because the table holds many notifications. This would grow large over time, and the SQL query would only take those in the current month:

SELECT *
  FROM raffle
 WHERE user_id = 542
   AND notify_at > '2020-01-01 00:00:00'
   AND notify_at < '2020-01-31 23:59:59'

As you can see, the index would also grow larger.


texts:
I have a database that has entered "Recovery Mode". It was working fine, but ended up in this status a few days ago.

I have been told that a cause can be the partition holding the datafiles running out of space. However in this case the drive has plenty of space. The log files for the database are also of a reasonable size (300-450mb)

I found this script on google that is meant to return the about of time a database has left before recovery is complete. It dosn't give me any results. We do not have a backup of the database, and we really need its data back

DECLARE @DBName VARCHAR(64) = 'databasename'

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT]  VARCHAR(MAX))

INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName

SELECT TOP 5
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT])  - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]

FROM @ErrorLog ORDER BY [LogDate] DESC


texts: Finding list of Duplicate records from 2 tables in DB2

texts:
I have a Person table and Address Table. Their relationship is Person.AddrId = Address.Id

Person table has First_name, Last_name, and AddrId.

The Address table has Address1, Zipcode, and Id.

I want to get all the list of duplicate records from this columns combination.

I tried below query:

SELECT  A.FIRST_NAME, A.LAST_NAME, B.ADDR, B.ZIPCODE, count(1)
FROM  SCHEMA.PERSON A, SCHEMA.ADDRESS B
WHERE
A.ADDR_ID = B.ID
group by A.FIRST_NAME, A.LASTT_NAME, B.ADDR, B.ZIPCODE
having count(1) > 1

Unfortunately this gives only the duplicate records. I want both original as well as duplicate records.


texts: fetching externally hosted db's table from within the local mysql server

texts:
Let's say we have a mysql database on localhost with table A and table B.

Is it possible to fetch details from a 3rd party mysql server to which we have login info and which allows external access?

Let's say there are 3 tables in the database we can connect to - table A2, B2 and C2.

How do you fetch the rows from table C2 and make it VIRTUALY accessible (as table C) from within the original mysql server (just like A and B), just like if it was hosted on it?


texts: GSE1015N A spatial reference system named "NAD83_SRS_1" does not exist

texts:
I am following this tutorial

https://www.ibm.com/developerworks/data/tutorials/dm-1202db2spatialdata1/index.html

And, running the sql "Listing 3. Contents of import_counties.sql" in IBM DB2 (on Windows), the CLP returns this error:

Is there anyone experienced this error before, could you please tell me how I get it solved? Because I already googled, and tried out many solutions including reinstall DB2 but the problem is still there. Is there any place that I can download "NAD83_SRS_1" and install it separately.


texts: How to find statement executed by sp_executesql in [sys].[dm_exec_query_stats]?

texts:
I have a function which is generating dynamic T-SQL statement. The generated code is executed then in a stored procedure.

I want to log some statistics about how long the execution of the generated code is and some IO statistics (logical/physical reads and stuff).

In order to do that, in the function which is building the dynamic T-SQL statement I append the following line to the final statement:

SET @A = @A + '--' + REPLACE('F2B287FD-1419-48DB-9881-4AA77B99450E', '-', '');

Then I am searching for statistics using the following query:

SELECT *
FROM [sys].[dm_exec_query_stats] QS
CROSS APPLY [sys].[dm_exec_sql_text] (QS.[sql_handle]) ST
WHERE ST.[dbid] = DB_ID()
    AND ST.[text] LIKE '%F2B287FD141948DB98814AA77B99450E%';

but it finds nothing.

The code is executed via sp_executesql procedure. Could anyone tell what's wrong with my query or alternative way of detecting IO for specific query.


texts:
Under what conditions would a materialized view log not update?

I must be missing something. I've got a 10gR2 database. I can create the following MV logs:

CREATE MATERIALIZED VIEW LOG ON linked_t1
 WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON t1
 WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES;

Then if I do an insert:

INSERT INTO linked_t1 (id, link_id, link_analysis_id) VALUES ('11111111','22222222','0000000001');

I see my insert in the MV Log:

SQL> select * from MLOG$_LINKED_T1 t;

LINK_ID  M_ROW$$            SEQUENCE$$ SNAPTIME$$  DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
-------- ------------------ ---------- ----------- --------- --------- ---------------
11111111 AAHvaaAAHAABvDsACR         11 1/1/4000    I         N         FE

Not really sure what to make of the 1/1/4000 snaptime.

As soon as I create a materialized view:

CREATE MATERIALIZED VIEW mv$sub2 (c1, c2, m1, m2, m3, m4, m5)
USING INDEX TABLESPACE idx
 REFRESH FAST WITH ROWID ON COMMIT DISABLE QUERY REWRITE AS
SELECT la.rowid c1, ar.rowid c2, ar.analysis_id m1, ar.id m2, ar.valid m3, la.analysis_id m4, la.id m5
  FROM linked_t1 la, t1 ar
 WHERE ar.analysis_id = la.analysis_id;

The materialized view log on LINKED_T1 is emptied, and will no longer track any DML! Why is that? I have not made any changes to T1 for this test.

I should mention that this MV was suggested by DBMS_ADVISOR.TUNE_MVIEW.


texts: Do start and end points change conflict serialisability?

texts:
If we have two transactions, and the following schedule:

S1 R1(X) S2 R2(X) R2(Y) E1 W2(X) W2(Y) E2

where Si and Ei indicate the start and end of the ith transaction respectively. Does this change the way you'd find whether or not this schedule is conflict serialisable? In other words, can I assume that the schedule is just:

R1(X) R2(X) R2(Y) W2(X) W2(Y)

when finding out whether it is serialisable or not?


texts: How to avoid full table scan when using OR operator on an Index

texts:
Essentially, I want to get intersection between 2 tables based on matching IDs with the option of wildcard '*' to return all IDs:

  Select * from A a inner join B b on b.id = a.id or b.id = '*'

Table A is fairly large (10 M rows), and I have setup the id to be index already.

Because of OR, index id is not used, and it does a full scan (Takes 20 secs).

If I don't allow the wildcard:

  Select * from A a inner join B b on b.id = a.id

It uses id index and takes only 300 ms

Edit:

By separating into 2 select with Union helped. Detail here: https://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea