texts: Take a "slice" of a SQL Server database: for all tables, get only the rows that join (directly or indirectly) to a specific row

texts:
For audit purposes, I'm trying to obtain a copy/export of a DB but including only the rows from each table that have relevance to a small collection of rows in some table. So every row of every table that these rows point to; every row of every table that points to these rows; and so forth. (In algebra terms this is the transitive-symmetric-reflexive closure of those rows with respect to the foreign key relation.)
I'm wondering if there exists some kind of tool to do this, or if this is scriptable somehow. I have ~400 tables with a tight network of foreign keys so I'd rather not do this by hand.


texts: How do I estimate temporary storage needs for a Postgres query?

texts: Running queries on Amazon RDS Postgres instance.  I have a couple of tables that are just over 1TB in size, not including indexes.  I'm having trouble getting queries to run against these tables due to how RDS allocates temp storage. How do I estimate how much temporary storage is required to complete a query -- I'm interested both in determining how large an RDS machine I'll need, and also in how to minimize temporary storage needs for any given query.


texts: Why does altering a table and adding a foreign key create an extra constaint?

texts:
Edit: It was a default constraint as a result of setting a default value when adding the column. The name gave it away when starting with 'DF__'. Anyway, solved it by adding the column and manually naming the constraint.

ALTER TABLE MyTable ADD [FooId] [int] NOT NULL CONSTRAINT DF_MyTable_FooId DEFAULT 0

And now I can easily delete the constraint and delete the column.

For testing purposes I need to drop and recreate a few tables quite often. I am using MS-Sql server 2008 r2.

When I create the tables I also create a foreign key in an existing table (one that I cannot just drop) with:

ALTER TABLE MyTable
ADD CONSTRAINT FK_MyTable_NewTable
FOREIGN KEY (FooId) REFERENCES NewTable (Id)

this does create the foreign key correctly, but it also adds a constraint with a system generated name (something like DF__MyTable_FooId__53385258), that is not create if the foreign key is defined in the CREATE TABLE statement.

My problem is, that when I want to drop MyTable, I have to remove the foreign key constraint - this is easy enough since I named it, but the other system generated constraint is still there and I don't have a direct way to delete it.

Is it possible to add the foreign key to an existing table without the system generating a second (and redundant I assume) constraint?


texts: How to find dropped or ghosted columns in Azure SQL DB?

texts:
In SQL Server, dropping a column is a metadata only change; the data remains in the index until the index is rebuilt.

On prem, I use system_internals_partition_columns to determine what indexes have this ghosted column so I can rebuild and reclaim the space. This internal table is not accessible in Azure.

Does anyone know of a dmv or system table in Azure with this information?

Below is an example of what I am talking about.  These rows are approx. 2000 bytes so you get 4 rows per page.  Dropping a column should make each page about 50% free space but doesn't.  Worse, new records are still added at 4/page rather than 8/page.

DROP TABLE IF EXISTS dbo.TEST

GO
CREATE TABLE dbo.TEST (ID INT IDENTITY PRIMARY KEY,
                       a CHAR(995),
                       b CHAR(995))

 DECLARE @i INT = 1
   WHILE @i <= 4000
   BEGIN
      INSERT INTO dbo.Test
      VALUES(@i,@i)

      SET @i +=1
    END

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    1000    0.3 99.6540647393131

--*/
GO

ALTER TABLE dbo.TEST
DROP COLUMN a

GO

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result - No change from the dropped column

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    1000    0.3 99.6540647393131

--*/

 DECLARE @i INT = 1
   WHILE @i <= 4000
   BEGIN
      INSERT INTO dbo.Test
      VALUES(@i)

      SET @i +=1
    END

SELECT t.name, p.page_count, p.avg_fragmentation_in_percent, p.avg_page_space_used_in_percent
  FROM sys.tables t
  CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),t.object_id,1,NULL,'SAMPLED') p
  WHERE t.name = 'TEST'

/* Result - Same amount of space taken despite rows being half the size

name    page_count  avg_fragmentation_in_percent    avg_page_space_used_in_percent
TEST    2000    0.35    99.6540647393131

--*/

--This is how I find columns that are dropped but still being added to the page on prem
--DOES NOT WORK IN AZURE SQL DB
--SELECT tables.name
--  FROM sys.partitions
-- INNER JOIN sys.system_internals_partition_columns ON partitions.partition_id = system_internals_partition_columns.partition_id
-- INNER JOIN sys.tables ON tables.object_id = partitions.object_id
-- WHERE is_dropped = 1

I've continued looking into this off and on, but between not finding anything online and Randi's comment below, this may not be possible.


texts: MongoDB wiredTiger Error: collection-*.wt does not appear to be a WiredTiger file (Window10)

texts:
mongoDB version: 3.4.2 os: win10
Question: Cannot start mongoDB due to .wt file corruption
When reset mongoDB database path, cmd give the error as in picture
Any hint would be appreciated, thanks


texts: Get-ClusterParameter Not Returning Expected Results

texts:
I have been trying to get the parameter value for RegisterAllProvidersIP in my AG environment. However, the command is not fetching me all results. The resultset has just 4 parameters, and I'm wondering how to get all. I want to check this to investigate the ongoing connectivity issue. This is a multi-subnet AlwaysOn configuration.

I am using the below command:

Get-Clusterresource -Name AG_Name | Get-ClusterParameter

AG_Name being the listener name.

Please help in identifying what I am doing wrong or is it just some permission issue?


texts: PostgreSQL9.6 Beta1：new setting remote_apply for configuration parameter synchronous_commit

texts:
PostgreSQL9.6 add new setting remote_apply for  configuration parameter synchronous_commit, Reading the following document I still don't understand the difference between on the setting on and remote_apply. Can anyone help me? thanks a lot .

synchronous_commit (enum)

Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client. Valid values are on, remote_apply, remote_write, local, and off. Can anyone help me? thanks a lot.

When set to on, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and flushed it to disk. This ensures the transaction will not be lost unless both the primary and all synchronous standbys suffer corruption of their database storage. When set to remote_apply, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and applied it, so that it has become visible to queries on the standby(s)

synchronous_commit

http://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT


texts:
At the moment our production and development databases give sysadmin access to all of devops, third party developers, etc.
I'd like to propose new access levels based upon existing AD groups, but I'm wondering if anyone can give pointers on what levels of access should be defined?

At the moment I've come up with 3 roles:

Admin - giving sysadmin access
Developer - giving permission to create/drop tables, insert/update/delete.
Viewer - read only access.

We also have different applications running on each database/server, and it was proposed that we don't separate these by access level. So, if one person has sysadmin on the 'Sales' database, they would also have the same level on 'Human Resources', etc.

It was also suggest that that our new AD groups would be server agnostic, to ease in transitioning new application access.

I'm wondering if anyone can give their opinions how you've defined your access levels, etc.

Thank you.


texts: Split SSIS project in to multiple files to avoid merge hell

texts:
Since SSIS does not like being merged nicely I was wondering how to have a big SSIS package split up. With the idea of having multiple devs working on this simultaneously, and a minimal chance of having merge conflicts.

In SSIS 2016 I found these options:

package parts, but apparently they don't share connection managers. I don't want to have 100 different connection managers.
subpackages, still this doesn't look very clean and I also wonder if this is what it is intended for. Also, the debugger goes crazy too opening the subpackages while running. Any other drawbacks I should know of?

I can't be the only person with this problem. Is there an other way to achieve this?


texts: SQL Server linked to AccessDB Update statement doesn't work in some scenarios

texts:
I have a SQL Server 2014 Express server linked to an Access DB. I am able to SELECT and view data in the access db without issue. However, when I run update statements they don't always work. The linked server name is PINKSHEET.

The update query below fails.

UPDATE PINKSHEET...[I Warehouse]
SET [Warehouse Name] = 'Test'
WHERE ([Warehouse ID #] = 3)

with the message

  The Microsoft Jet database engine cannot find the input table or query 'I'.

followed by the message

  Cannot execute the query "UPDATE I Warehouse set `Warehouse Name` = 'Test'  WHERE `Warehouse ID #`= ? " against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PINKSHEET".

If I rewrite the above query as noted below it works without issue.

UPDATE PINKSHEET...[I Warehouse]
SET [Warehouse Name] = 'Test'
WHERE [Warehouse ID #] IN (SELECT [Warehouse ID #] FROM PINKSHEET...[I
Warehouse] WHERE [Warehouse ID #] = 3)

I cannot figure out why the simpler structure throws the error.


texts: Easily show rows that are different among multiple tables

texts:
Imagine you have several tables with same structure (each table, one year of data):

table states_2017

stateID propertyID  owner         area  timestampGot
1       1           David Smith   15    123456789
2       2           Amanda Green  74    123456799
3       1           David Smith   15    123456999

table states_2018

stateID propertyID  owner          area timestampGot
1       3           Amanda Green   12   123459999
2       1           Peter Black    11   123499999
3       4           Jack Stone     62   123999999
4       2           Amanda Green   70   129999999

Then you have table properties with basic information about properties:

table properties

propertyID  defaultStateID  defaultStateYear
1           1               2017
2           2               2017
3           1               2018
4           3               2018

In the example above, state of propertyID 1 changed from its default state (stateID 1 in states_2017) to the most recent state (stateID 2 in states_2018). Similarly, state of propertyID 2 changed. How to show these changes (using MySQL)?

Desired output:

propertyID  owner         area  timestampGot
1           David Smith   15    123456789
1           Peter Black   11    123499999
2           Amanda Green  74    123456799
2           Amanda Green  70    129999999


texts: How efficiently does iSeries DB2 use index with WHERE ?

texts:
MS SQL dev here, this is my first attempt to optimize a DB2 query on iSeries.
I was asked to help improve a huge query which consists entirely of dozens of joined subselects. Some of those subselects include unions and where exists/not exists clauses were used extensively.
Among other things there are many where clauses that specify column <> 0 etc.
Does iSeries DB2 efficiently use index with where column <> 0, <> '' etc?


texts:
Given a many-to-many relationship (product/component), I need to find a product (or all products) that are composed from the given set of components.  I can't wrap my head around the SQL query for this. A general solution, but if there is anything particular to SQLServer, that will work too.

Update: assume the standard many-to-many ternary mapping.


texts: oracle crash with ORA-00942: table or view does not exist

texts:
we use oracle9i database. and since last week my oracle crash many times, after running for 30 hours more or less.
   we see errors repeat in my trc file and alert log :

qmhGetHTTPError: Got unknown oracle error. Error stack is:
ORA-00942: table or view does not exist

we do not find the exact table or view does not exist.
   if we upgrade our database to oracle 10g or 11g,will solve the problems?
   I found some error another log,

error 1242 detected in background process
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 16 (block # 156925)
ORA-01110: data file 16: 'D:\ORACLE\ORADATA\SDH\SDH_DATA4.ORA'
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile()
O/S-Error: (OS 1450)

 will above implies hard disk error? anyone gets an idea? thanks for all.


texts:
Can somebody explain me the below query?

select SUBSTR(SUBSTR(PART_SEQ_NBR,
                     POSITION('_' in PART_SEQ_NBR )+1,
                     char_length(PART_SEQ_NBR)
                    ),
              1,
              POSITION('_' in SUBSTR(PART_SEQ_NBR,
                                     POSITION('_' in PART_SEQ_NBR )+1,
                                     char_length(PART_SEQ_NBR)
                                    )
                      ) -1
             ) AS PRD_NAM
from PRD_MWE_GPRSDATPART_PERM_S

Thank you in advance


texts:
I want to install the pgcrypto extension on my postgresql installation on Windows. I found that I just have to call CREATE EXTENSION pgcrypto; but I've got an error :

Syntax error on « CREATE »
LINE 1: SELECT COUNT(*) AS total FROM (CREATE EXTENSION pgcrypto) AS...

(I'm on PostgreSQL 9.2.4 and I executed the sql line from phpPgAdmin with postgres user)


texts: what is the quickest way to check whether a XML column is empty ( @xml = '') in the where clause?

texts:
following the conclusion of the question
how to test if XML = '' in sql server?

to cut a long story short there is a procedure which has one of its parameters a XML variable @ListNoteTypeCode.

It is a XML because it can contain more than one value and I am using sql server 2005.

there are 2 ways to check whether or not the @ListNoteTypeCode parameters is empty or contain value(s).

1) @ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('*') = 0

2) @ListNoteTypeCode is NULL OR DATALENGTH(@ListNoteTypeCode) = 5

which one performs better?


texts: Log Shipping "out of sync" but none of the jobs are failing

texts:
I've configured a log shipping from a server, to the same server, only with a different instance.

Primary server is configured this way:

LSBACKUP_MyDatabase - every 25min

Secondary server:

LSCOPY_MyDatabase - every 1 minute

LSRESTORE_MyDatabase - every 10 minutes

Whats happening is, the primary backup job is running fine ( there is a lot more history, im just showing the last 2 ).

in the folder, I can see the TRN files.

In the secondary instance, LSCOPY and LSRESTORE is ok too. It's copying files, but the problem is here. The restore job is reporting this "message" ( the job runs succesfully, so I don't think it's a error):

  Message

  2015-12-29 09:10:02.41     Skipped log backup file. Secondary DB:
  'MyDatabase', File: '\ServerIP\instancia
  g\BACKUP\Log_Sp_Secundario\MyDatabase_20151229104500.trn'

  2015-12-29 09:10:02.41     Could not find a log backup file that could be
  applied to secondary database 'MyDatabase'.

  2015-12-29 09:10:02.42 The restore operation was successful. Secondary
  Database: 'MyDatabase', Number of log backup files restored: 0

  2015-12-29 09:10:02.42     Deleting old log backup files. Primary
  Database: 'MyDatabase'

  2015-12-29 09:10:02.42 The restore operation was successful. Secondary
  ID: '5a0a361c-039c-40a3-9c39-af5e338c7f72'

And then, when I click to see the history of the LSALERT_ Job, its reporting errors with this message:

  Message Executed as user: CMDO\gdladmin. The log shipping secondary
  database VMWGDLPRD04\GDLIC2014.GDL_IC has restore threshold of 45
  minutes and is out of sync. No restore was performed for 8323 minutes.
  Restored latency is 0 minutes. Check agent log and logshipping monitor
  information. [SQLSTATE 42000] (Error 14421).  The step failed.

According to one of the Microsoft's support pages, there is this query to show if there are gaps between logs. there are none:

SELECT
    s.database_name,s.backup_finish_date,y.physical_device_name
FROM
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
    (s.database_name = 'MyDatabase')
ORDER BY
    s.backup_finish_date DESC;

I've been searching all over the internet, but I could only find those dba blogs with lack of information, and some posts saying that the primary database was dropped ( obviously was not ).

The primary instance is 2012. The secondary is 2014. the secondary database is in recovering mode.

To fix this, should I recreate all log shipping?


texts:
I'm trying to get my report to display different color cells, depending on the condition. This is my current code:

=Switch(Fields!DaysToExpiration.Value > 31, "Green",
        Fields!User_Certification_Status.Value = 8, "Firebrick”,
        Fields!User_Certification_Status.Value = 13, “Orange”,
        Fields!User_Certification_Status.Value = 11, “Orange”,
        Fields!User_Certification_Status.Value = 10, “Orange”,
        Fields!User_Certification_Status.Value = 12, “Orange”,
        Fields!DaysToExpiration.Value <= 30, "Yellow",
        1=1, "Gray")

So, my problem lies with the last part of the code. I'm using a matrix view, which means there are some cells where there is no data. SSRS is treating these like they have a value though (I'm assuming 0). This means that all the blank ones automatically turn to yellow, rather than gray. How can I get around this?
Thank You.


texts: How to check if all elements of an array exists in a table?

texts:
Lets take an array of ids of size n in the application level. for instance [132,3425,13,13,... 392] where n is bigger than 100k entries. Some of this entries are repeated.

I need to check if all of these registers are contained in a MySQL table containing more than 5M entries.

Now I'm checking one after another, but this process takes a very long time.

How can I make this verification in one single check, or maybe make this more efficient.

I would like to know if all of them exists on the table, and, if possible, know which don't already exists on that table.


texts: Replacing full contents of SQL tables without user disruptions

texts:
I have a SQL Express 2012 DB in which there are tables that need frequent full "refreshs" (on-hours) from an external source without any disruption of the users.  Let's say the tables are Item, Inventory & Barcode.

I need to keep these tables in-sync with a MySQL database (the system of record) and I am looking for a fast & reliable way to fully replace the contents of the SQL tables with data from the MySQL database.  I will be using a C# app for this (reading the data from MySQL into memory, then writing it to the SQL server [likely using Bulk Copy]).

The approach that I am considering is to have an _A and a _B version of each of the tables above, with a synonym pointing to the current one.  The update would work something like this:

1) Read control record which tells me that A is the current version (i.e. the synonyms are currently pointing to the _A versions).

2) Drop the constraints of and truncate the _B tables (Item_B, Inventory_B & Barcode_B)

3) Do the Bulk Copy to populate the _B tables

4) Add the constraints back to the _B tables.

5) Drop and re-create the synonyms so that they point to _B (i.e. Item -> Item_B, Inventory -> Inventory_B, Barcode -> Barcode_B)

6) Note: the _A and _B tables would never be dropped, just truncated, so that the permissions do not get lost.

The above approach seems fairly good to me, except for the fact that I am using code-first Entity Framework on the SQL side and it gets a bit messy when there is a schema change; I assume that EF will apply migrations to the _A or _B tables depending on the current synonyms, so if it updates the schema of the _A tables, I will have to manually do the same to the _B tables.  Not a huge deal.

I am looking for feedback on the above as well as suggestions on how to do it in a cleaner / better way.


texts:
I have a question I can't solve.

My query would be:

SELECT
    f.Title, p.StartOfShowing
FROM
    PlayDates p
INNER JOIN
    PlayDateSections ps ON p.PlayDateId = ps.PlayDateId
INNER JOIN
    Films f ON p.FilmId = f.FilmId
WHERE
    DATEDIFF(d, p.StartOfShowing, '2018-04-30') = 0

The result of the table would be this:

A Quiet Place                      2018-04-30 12:40
A Quiet Place                      2018-04-30 15:05
A Quiet Place                      2018-04-30 17:30
A Quiet Place                      2018-04-30 19:55
A Quiet Place                      2018-04-30 22:20
Avengers : Infinity War            2018-04-30 11:10
Avengers : Infinity War            2018-04-30 11:30
Avengers : Infinity War            2018-04-30 12:00
Avengers : Infinity War            2018-04-30 13:00
Avengers : Infinity War            2018-04-30 13:20
Avengers : Infinity War            2018-04-30 14:00
Avengers : Infinity War            2018-04-30 14:20
Avengers : Infinity War            2018-04-30 14:40
Avengers : Infinity War            2018-04-30 15:10
Avengers : Infinity War            2018-04-30 16:10
Avengers : Infinity War            2018-04-30 16:30
Avengers : Infinity War            2018-04-30 17:10
Avengers : Infinity War            2018-04-30 17:30
Avengers : Infinity War            2018-04-30 17:50
Avengers : Infinity War            2018-04-30 18:20
Avengers : Infinity War            2018-04-30 19:20
Avengers : Infinity War            2018-04-30 19:40
Avengers : Infinity War            2018-04-30 20:20
Avengers : Infinity War            2018-04-30 20:40
Avengers : Infinity War            2018-04-30 21:00
Avengers : Infinity War            2018-04-30 21:30
Avengers : Infinity War            2018-04-30 22:50
Avengers : Infinity War  ATMOS     2018-04-30 12:40
Avengers : Infinity War  ATMOS     2018-04-30 15:50
Avengers : Infinity War  ATMOS     2018-04-30 19:00
Avengers : Infinity War  ATMOS     2018-04-30 22:10
Avengers : Infinity War  PLATINUM  2018-04-30 14:40
Avengers : Infinity War  PLATINUM  2018-04-30 17:50
Avengers : Infinity War  PLATINUM  2018-04-30 21:00
Avengers : Infinity War  PLATINUM  2018-04-30 23:30
Love Simon                         2018-04-30 14:25
Love Simon                         2018-04-30 19:15
Rampage                            2018-04-30 16:55
Rampage                            2018-04-30 21:45

However I wanted to have a pivot query with result like the ones below :

A Quiet Place                     4/30/2018  12:40  15:05  17:30  19:55  22:20
Avengers : Infinity War           4/30/2018  11:10  11:30  12:00  13:00  13:20
Avengers : Infinity War           4/30/2018  14:00  14:20  14:40  15:10  16:10
Avengers : Infinity War           4/30/2018  16:30  17:10  17:30  17:50  18:20
Avengers : Infinity War           4/30/2018  19:20  19:40  20:20  20:40  21:00
Avengers : Infinity War ATMOS     4/30/2018  12:40  15:50  19:00  22:10  NULL
Avengers : Infinity War PLATINUM  4/30/2018  23:30  14:40  17:50  21:00  NULL
Love Simon                        4/30/2018  19:15  14:25  NULL   NULL   NULL
Rampage                           4/30/2018  16:55  21:45  NULL   NULL   NULL


texts: Is it possible to separate the application of statements inside database transactions?

texts:
This is a simple scenario that I encountered recently.

I will fabricate a minimal scenario to illustrate the problem.

We have a database table with a column of IDs used as a foreign key to something (but which is not specified as a foreign key; it's being used to hold an ephemeral mapping index temporarily, in a really rather dangerous way that relies on external state). Each row has a value populated here. This column of data subsequently must be updated with a new mapping.

An example of a valid mapping:

0 -> 1
1 -> 4
2 -> 0

An example dataset:

2
1
0
2

Obvious desired result:

0
4
1
0

Implementation (pseudocode):

for (mapping in map) {
    sql("update table set value = " + mapping.to + " where value = " + mapping.from + ";")
}

Pretty straightforward but almost as obvious is that this is completely wrong because the result we see is

0
4
4
0

The third row got the mapping applied twice.

I was thinking that maybe if the N (where N is the number of mappings) statements are executed in a transaction that it may solve the problem. But I'm not so sure, and I don't really see why it would. The transaction would just guarantee that either row 3 got zero or both mappings applied, but what I need is for only the one mapping. It basically seems like I need a whole new column to even accomplish such a simple operation in a straightforward way.

How is this sort of situation dealt with usually? I feel like I'm missing something trivial.


texts: How to use a variable date when running a db2 command in a shell script?

texts:
I need to use a date variable in a select, like bellow, but I'm getting this error: Expected tokens may include:  "".  SQLSTATE=42601

How can use these variables in a shell script?

day0_T1=`TZ=CST+12 date +"%Y-%m-%d %T"`
day0_T0=`TZ=CST+24 date +"%Y-%m-%d %T"`
dayn_Tn=`TZ=CST+n date +"%Y-%m-%d %T"`

db2 connect to BD
db2 "/path/result.tmp  OF DEL MODIFIED BY COLDEL; decplusblank striplzeros
select count(*),user from table1
and close_datetime between cast(date('$day0_T0') as timestamp) AND cast(date('$day0_T1') as timestamp)
and task = 'task1'
group by user
with ur"
db2 terminate


texts: Availability Groups Service Account has over 25000 sessions open

texts:
I am running Availability Groups on SQL Server 2014 with one database in the group. Today, the server was responding very slowly and when I got on the server I found that the Availability Groups service account had over 25000 sessions open. Every session was to the MSDB database with the same query,  The query was:

(@P1 nvarchar(max),
 @P2 uniqueidentifier,
 @P3 int,
 @P4 int,
 @P5 int)
DECLARE @logTextWithPreAndPost nvarchar(max) set @logTextWithPreAndPost = N'' + @P1 + N'';
EXECUTE msdb.dbo.sp_agent_write_sysjobstep_log
 @job_id = @P2,
 @is_system = @P3,
 @step_id = @P4,
 @log_text = @logTextWithPreAndPost,
 @append_to_last=@P5

Does anyone know why it would hold so many sessions open or what I can do to stop it.


texts: Prevent duplicates in MySql without using unique constraint

texts:
I have a rules table consist of a column called priority, that must be gapless, incremental and unique.
A user must be able to re-order priority of the rules. For example, if there are 10 rules for a user and she moves the last rule to the 5th place, all of the other records between must get updated.
To prevent race condition during updates, I can use a select ... for update lock. But I can't find a way to guarantee the uniqueness of priority during inserts.
Firstly I wanted to use a deferred unique constraint, but figured out that MySql does not support this feature as Postgres do.
Then I thought about using a trigger on before insert, to calculate the priority, but I found that duplications still can show up.
What must I do? Is there any way to create a unique check that only works during inserts not updates?


texts: Nothing to do, no schemas or tables selected on export

texts:
I'm using MySql Workbench 6.3.6, on Windows 10, which has all updates.

I have previously been able to get a data dump from this database. Now I can't get any from any of the schemas I have open in workbench.

I've been able to run queries and make changes to the databases.

Then when I try to use the export function I get this message:

  Nothing to do, no schemas or tables selected.

I have found some bug reports, but am not convinced they apply to version 6.3.6:

mysqlbugs

ubuntu mysql workbench

File bug report http://bugs.mysql.com/bug.php?id=79866&thanks=4


texts: Remote connection to DB instance from fresh formatted system?

texts: I have 1 win7prof  fresh system. Now I want to connect to a DB instance remotely via sqlcmd/any other way. My req is  to connect and execute select query? is there any method/software needed to be installed in fresh win7prof system to access the instance.? kindly let me know what to do for access and use manipulation operations ?


texts: Performance at finding all foreigns with 3 one-to-many relation

texts:
Basically, I have 2 tables : user and object. One user can be associated to one object by three ways (roles). I want to list all users associated at least to one business object by any role.
This query works, but it takes 5 seconds !
SELECT u.id
FROM user u
LEFT JOIN translation_revision t1
    ON u.id = t1.translator_id
LEFT JOIN translation_revision t2
    ON u.id = t2.proofreader_id
LEFT JOIN translation_revision t3
    ON u.id = t3.reviewer_id
wHERE t1.id IS NOT NULL
    OR t2.id IS NOT NULL
    OR t3.id IS NOT NULL
GROUP BY u.id

Complete fiddle with creation and data here : https://www.db-fiddle.com/f/nojYaUsWicKJDuRQk1Nqd2/7
Any idea on how to improve perf here ?


texts: Simple date calculation to show old records

texts:
How to select from v$session records where logon_time is less than 2 days before current date?

SELECT * FROM v$session WHERE logon_time + 2 <= currentDate (???)


texts: good practices of creating tables in mysql

texts:
Hi I was thinking of developing an web apllication using php and mysql.
Application will give a professor the required data about the student.

i want to design application considering Engineering college.

So professor can view topper in perticular branch,overall topper in college,search student by roll no,display the list of students i roll no order or on the basis of percentage

Overalll data will consist of

Student name,roll no,year,percentage,department name,marks in each subject,subject name,branch

So please guide me to create table for the same in mysql..

thanks in anticipation


texts: How should I design my table(s) to represent a list of objects, some of which are components of other objects?

texts:
My initial concept of this manufacturing DB would have a table where all parts and materials used to make finished products are part of a table:
Raw Material A
Raw Material B...Z
Widget A
Widget B...Z

Widgets are components of Finished Products. Raw Materials can be used to make Widgets but can also just be a component of a Finished Product.
I need to keep track of inventory for everything in this table, which means I need to be able to represent what Raw Materials are consumed when making a Widget.
Considerations
What direction should I go with this? My first thought would be a nullable column like component_id_needed and component_qt and multiple table rows for the same Widget if multiple Raw Materials were needed.


texts: I have MySQL version 5.7 with a master-slave configuration. but I'm confused to make a backup cron. Is there a way to do a backup while remaining safe to master-slave? I heard that using mysqldump is too risky


texts:
I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL.

Why does = NULL not work here?


texts:
Thanks for reading my post...I'm new at setting up databases and am working on a simple practice app.

I have a table called PhoneNumbers, which hold a list of phone numbers (duh).  I have two other tables that relate to PhoneNumbers, Employee and Company.

Each Employee can have multiple phone numbers, each Company can have multiple phone numbers.

Since my app needs to be able to separate out the difference between Company phone numbers and employee phone numbers, and be able to list each number per employee and per company, I was wondering the best way to set up the PhoneNumbers table, keeping in mind that perhaps there will be more than two Number Types in the future.

One ideas I had was:

Create two fields in the Phone number table called NumberTypeID and OwnerID and create another table called NumberType.  NumberTypeID would hold the ID numbers of the Number Types created in the NumberType table.  OwnerID would hold ID of the Company or Employee it belongs to.

PhoneNumbers
 - ID
 - PhoneNumber
 - NumberTypeID
 - OwnerID

NumberType
 - ID
 - Name

I'm thinking the NumberTypeID column will help querying all numbers for all companies/employee easier. I also am thinking this makes it more flexible down the road for new number types, but perhaps is overly complex?  Maybe I don't need the NumberTypeID column at all and querying all numbers for all companies/employees is best with a more complex statement saved as a view? Or perhaps I'm missing something else all together?

Again very new at this, so any help would be greatly appreciated.

Thanks


texts: Oracle 10g slow performance on first query

texts:
We have an application which we recently upgraded and is having bad performance issues. Any ideas would be much appreciated.

The main issue is windows taking ages (minutes) to open because the initial query to fill their list boxes is taking so long. The second time a user opens the window its fine.

We would expect this to be application cacheing but we don't think so. We see similar problems using Crystal Reports and using command line SQL queries.

We have increased the RAM available to the database.

Another part of our business has a slightly different version of the same application and database and doesn't get this problem.

The time to complete a query can be 30 minutes for the first time in a session and 30 seconds for subsequent executions.


texts: Tabular mode disabled during SSAS installation

texts: I am trying to install SSAS in Tabular Mode. However, i find that the radio button is disabled. I am installing it on a separate instance as well. The regular Multidimensional SSAS  had been installed first.I am installing Standard SQL server . .


texts:
I’m designing a database with a table that automatically gets loaded with a new record every 15 seconds (each record will have a 'timestamp' field and around 50 ‘float’ fields).  Each record in this table needs to be copied to another table.  I have done some initial testing with two different approaches to do this:

Use a trigger to insert each record into the other table (will fire every time a record is added to the source table - every 15 seconds)
Use a job (SQL Server Agent) to run every ten minutes or so and insert a batch of records into the other table (and use a flag field in the source table to indicate which records have been processed).

I don’t have very much experience with triggers or SQL Server jobs so I don’t know the impact regarding overhead and system resources (over time, both tables will have a lot of records).

In general, what approach would be better?  Is there another approach I should consider?

Thanks


texts: Sql server AOAG license on readable secondary

texts:
Scenario:
Microsoft sql server AlwasOn Availabiliry Group:
For SQL server License on readable secondary if :
Example
Primary server  cores : 32
Secondary Replica cores : 32, but at the time it's secondary only 12 cores assigned to sql server.
The question:
Will the license be calculated for the 32, or the 12 cores on active readable secondary


texts: Schedule Job to Start at Different Steps

texts:
I am working on a system that generates a number of recurring emails as parts of various jobs scheduled in SQL Server Agent. The emails contain text-based reports that are loosely associated to the subject matter of their jobs, but not dependent on the job's outcome. When a customer requests a change to the contents of the report or the distribution list, it can be difficult to find the specific job/step that is responsible for generating the email/report.
To make maintenance of these tasks easier, I want to create a new central job and move each email/report step into that job. I would configure each step of the job to "Quit the job reporting success." I would configure a unique schedule for each step in the job so that the email/reports continue to be generated on their normal schedules. For instance, step 1 would run daily at 7am. Step 2 would run on Saturdays and Sundays at noon. And so on.

When I went to create a schedule, I was unable to find a way to specify which step to start on. I know that msdb.dbo.sp_start_job accepts a @step_name parameter. Likewise, SQL Server Agent allows me to pick a step to start from when manually starting a job through the UI. Is there a way to specify the start step from a schedule, or am I limited to starting with the configured (default) start step as listed in the job?


texts: Identify ssis packages that call ssrs reports

texts:
We are migrating our SSRS reports server database from SQL SERVER 2008 R2 to SQL SERVER 2014 and as I understand it this means all SSRS reports will receive a new id.  Hence any SSIS packages that reference a report would break.

Is there a way to identify all SSIS packages that make reference to SSRS reports?


texts:
What is the best way to count NULL values inside case?

Using a joined table - I am trying to count the values of the column P.ID (from the joined table), and to categorize the counted values into a new column  via case.

For example:

when count 0 - display 'none'
when count 1-2 - display 'few'.
when count 3-5 - display 'medium
when count 5+ - display 'many'

However, since some of the values received from the join are null - I cannot count them.

What changes should I do to my query in order to refer every NULL as 0?

SELECT
    U.Mail,
    COUNT(P.ID) AS PostCount,
    CASE
        WHEN COUNT(P.ID) = 0 THEN'none' --doesn't work
        WHEN COUNT(P.ID) <= 2 THEN 'few'
        WHEN COUNT(P.ID) <= 5 THEN 'medium'
        ELSE 'many'
    END AS PostCountCategory
FROM
    Users U
LEFT JOIN
    Tag T ON U.Mail = T.Mail
LEFT JOIN
    Post P on T.IDPost = P.ID
GROUP BY U.Mail;


texts: MYSQL 5.7 Partition a table based on partition key of another related table

texts:
Please understand a little background before getting to know the problem.
TABLE transaction (

t_id: AUTO INCREMENT BIGINT

t_execute_on_date: DATE,

timestamp: DATETIME,

... other columns
)

The table transaction has 300M (three hundred million) rows and growing (1M rows daily) and growing and we need to drop old data based on the column  t_execute_on_date. I plan to drop old data (older than 2 years. approx 40M rows) on the basis of creation monthly range partitions based on KEY t_execute_on_date. I have tested this on a test server while using the procedure suggested in @RickJames blog post PARTITION Maintenance in MySQL:  create a new partitioned table, with optimised indexes according to queries and then reinserting records, and drop old partitions, and this approach seems fine to me. Any suggestions in this approach are also welcome.
I have done this in master-master replication with one master offline, reorganising data, and recovering replication lag, and then drop the old master, bring the traffic to new one, if everything is fine.
TABLE transaction_history (

th_id: AUTO INCREMENT BIGINT,

t_id: FOREIGN KEY (t_id of TABLE transaction)

timestamp: DATETIME,

... other columns
)

This table has 1.5B+ (One billion five hundred million) rows growing 15M+ daily.
This table has related history of transaction and contains multiple rows corresponding to one t_id.
Once old transaction data from table transaction is dropped, I would like to drop the same from this table transaction_history as well.
This table does not have any key like t_execute_on_date based on which I can partition.
This table has 1.5B+ rows I think that adding an additional column t_execute_on_date is just not possible.
Like the approach discussed above would I have to manually pull data from table transaction_history based on t_id of TABLE transaction (reorganised ,old data dropped) and reinserting only the selected records into the new table transaction_history_partitioned?
The PROBLEM I face is how to purge old data from table transaction_history?
Using MYSQL 5.7
Running on
[2.8T HDD, Amazon Linux AMI 2017.03, 16 core, 63GB RAM]


texts:
Following is the schema of my Tables:

Marks_Table
(SubjectCode,TypeID,MarksObtd)
Subjects_Table
(SubjectCode, Subject)
Types_Table
(TypeID, Type)

I want to Select the Marks_Table table with SubjectCode and TypeID replaced with appropriate Subject and Type.

I wrote the following query:

SELECT [Subject] , [Type] , MarksObtd
FROM [Marks_Table]
INNER JOIN [Subjects_Table]
   ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode
INNER JOIN [Types_Table]
  ON *XXXXXXXXXXXXXXX*.TypeID = [Types_Table].TypeID

What should I write in place of XXXXXXXXXXXXXXX ? What should be the name of the table that has not been created yet?

It can be done in two queries, but is it possible to do this like above? and how?


texts: Table income and expense is better to have it together or apart

texts:
I am developing a small system where I have to record incomes and expenses of the company, and even I can not determine if the income and expense table should be in one table or in separate tables.

The design of the database must allow the system to generate a report of income and expenses by date (in column incomes and other expenses).

I would like to explain me what would be the advantage or disadvantage of having them together or separately


texts: Relating a variable number of rows in one table to one in another?


texts: What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application?

texts:
I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it.

the system will have a asp.net UI, web services middle tier and SQL2005 back end. The UI and Services will communicate using JSON calls.

I was wondering on how to ensure transactions are committed and if not for any error to bubble up and be logged. ideally the action to be retried a couple times after 5 minute intervals, like an email app does.

I was planing to use try catch blocks in SQL and was wondering what the interface (or contract if you will) would look like between the SQL stored procs and the Services that call them would look/ function. this interface will play 2 roles one is to pass params for the proc to function and return expected results. the next will be for the proc to return error information. maybe somethign like error number and error message.

my quagmire is how to i structure this intelligently so that the services expect and react accordingly to both data and error info returned from procs and handle each accordingly?

is there a framework for this because it seems very boiler plate?


texts: Oracle 10g Database Locking Session Issue

texts:
I have a problem with locking and not timing out in one of my Oracle databases. This session locks occur on certain transactions that come from Oracle Forms users. For instance, when some user requests to make a change on a locked object the transaction waits for the locked object to be released, but it waits indefinetely until killed by alter session.

I've tried the same in my testing database and it does kill the locked session after 60s, so I thought it has to be a configuration issue.
I've checked the v$parameters DISTRIBUTED_LOCK_TIMEOUT and it is set to 60s in both databases.

Does anyone know where is the "locking session/transaction timeout" set?

Any help is greatly appreciated.


texts: Subquery check for every occurence - Gantt style


texts: How to avoid MySQL slave to have errors after crash

texts:
I have a MySQL master/slave configuration (MariaDB 10.0.17). Sometimes, the slave or the master server crashes. After the crash and restart, I get some errors of duplicated keys on slave. The server get stuck into these errors and I need to take action by deleting the duplicated key entries so it goes on.

How to avoid this or, if not possible, do this "slave recover" automatically?


texts:
Given some event data that only consists of an id and a type of event (entry/exit) I'd like to compute a third column which could keep track of the current state of things (count), basically stating how many animals are present at any given time.

Example:

id   event
--------------
1    entry
2    entry
3    entry
4    entry
5    exit
6    exit
7    entry

I'd need an output similar to this (considering it starts empty)

    id    event        count
-----------------------------
    1     entry        1
    2     entry        2
    3     entry        3
    4     entry        4
    5     exit         3
    6     exit         2
    7     entry        3

Where count holds keeps track of the actual number of animals after that event has been processed.

I've been trying to take some windowed approach, but after a couple of hours I just can't seem to nail it (I'm quite new to this yet).

Any help will be greatly appreciated.


texts: SSAS and Visual Studio Optimization Wizard does not work

texts:
We have setup and logged data on our Production Server, but when we try to run the Optimizations from within Visual Studio it crashes immediately with

TITLE: Microsoft Visual Studio
------------------------------

The system cannot locate the object specified.

------------------------------
BUTTONS:

OK
------------------------------

The tables all exist, right where they should, with DBO as the owner.  The logged in user has the rights to read the data.

The extra information on the error says:

Program Location:

   at EnvDTE.IVsExtensibility.RunWizardFile(String bstrWizFilename, Int32 hwndOwner, Object[]& vContextParams)
   at Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.RunAddNewItemWizard(String pszItemName, Int32 cFilesToOpen, String[] rgpszFilesToOpen, IntPtr hwndDlg)
   at Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.RunWizard(String wizardName, String newItemName)
   at Microsoft.DataWarehouse.DataWarehouseUtilities.RunWizard(String wizardName, IServiceProvider serviceProvider, Object[] stackParameters)
   at Microsoft.AnalysisServices.Design.AnalysisServicesUtilities.InvokeUsageBasedOptimizationWizard(MeasureGroup measureGroup, Partition[] selectedPartitions)
   at Microsoft.AnalysisServices.Design.Aggregations.AggregationsEditor.OnUBO(MenuCommand menuCommand, CommandHandlingArgs args)

The OLAP tables have the default names of dbo.OlapQueryLog and there is recent data in it.  Im not sure what else I need to do to make this activate, but I never get as far as the wizard window.

Any ideas?


texts: Extracting data from extremely large csv files

texts:
I have a 40gb csv file with over 60 million rows for data analysis. Each row has a unique identifier (some numbers). For example, the first row's unique identifier will repeat approximately 150,000 rows later.

I would like to have a method to run through the entire file, and extract rows with the same identifier and write them into new csv files. Is there a good, automated way to do that? Please note that the file is very large and excel has problems opening it.


texts:
My current query looks like this

SELECT `task`, COUNT(*) as sum
FROM tasks
where `datetask` between ? and ?
  AND `taskstatus` = ?
GROUP BY DATE(`datetask`)
ORDER BY `datetask` ASC

Parameter status is currently 1. What If I wanted to make multiple status count up? Like 3-4 per date

"query": [
      {
        "datetask": "2015-11-04 00:00:00",
        "status1": "1"
      },
      {
        "datetask": "2015-11-05 00:00:00",
        "status1": "1"
      }
    ]

What I want to accomplish

DATE         | Status 1  | Status 2 | Status 3
---------------------------------------------
2015-11-04   | 3         | 3        | 3
2015-11-05   | 2         | 3        |
2015-11-06   | 5         | 3        | 3


texts:
I'm using PostgreSQL And I want to print a message to the console.

If I use plpythonu I use plpy.notice
If I use plpgsql I use raise notice

but how do I print when the function is pure SQL?
using SELECT 'string' isn't helping me as it print the string in a column and if the message is located in the middle of the code it doesn't show it.
I want something like raise notice / plpy.notice for SQL.

CREATE OR REPLACE FUNCTION A()
  RETURNS VOID AS
$BODY$
                how do i print 'hello world' here?
 $BODY$
  LANGUAGE sql VOLATILE

if it was a plpgsql I would do:

CREATE OR REPLACE FUNCTION A()
  RETURNS VOID AS
$BODY$
        Raise Notice 'hello world'
 $BODY$
  LANGUAGE plpgsql VOLATILE

I'm looking for the equivalent in  LANGUAGE SQL


texts: DB locks being created on temp tables. Can percona ignore temp tables for replication

texts:
I have a stored procedure that I am using that creates a temp table.  Recently I've noticed the logs are filling up with locks. I think the temp table is trying to be replicated but I am unsure.

*** Victim TRANSACTION:
TRANSACTION 0, ACTIVE 1 sec fetching rows
mysql tables in use 3, locked 3
MySQL thread id 1875027, OS thread handle 140436968662784, query id      41228278 dbserver 192.168.5.5 web_east Sending data
INSERT IGNORE into temp_table select ... from db.table as f inner join   data_temp temp on temp.id =f.id where f.date1 >= "2018-07-01 00:00:00" and f.date2 < "2019-08-01 00:00:00"

*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2272 page no 5986916 n bits 288 index   db_name_date_idx of table `db`.`table` trx id 421941145506800 lock mode S  locks gap before rec
2019-12-02T10:25:38.389502Z 10 [Note] WSREP: --------- CONFLICT DETECTED --------
2019-12-02T10:25:38.389506Z 10 [Note] WSREP: cluster conflict due to high  priority abort for threads:
[Note] WSREP: Winning thread:
THD: 10, mode: applier, state: executing, conflict: no conflict, seqno: 7653754
SQL: (null)


texts: How to measure bandwidth requirements for MySQL, Postgres, …?

texts:
TLTR: how to configure MySQL/Postgres to get a fair measurement of the I/O requirments for analytical queries?

Hello,

for certain database and especially database hardware experiments for want to measure how much bandwidth is required to answer analytical queries (i.e., CHBenCHmark queries on a TPCC data set with a scale of 1000). Current we use iotop to measure the read data from disk for each of the 22 queries. The database is ~80 GBs in size and the VM has 32 GB of main memory (e.g., Postgres reads 14 GB in average for the 22 queries).

Now we want to make fair measurements to get a better idea, what hardware is needed to answer such queries, e.g. answer the average query in less than 10 seconds (i.e., we need 1.4 GB/s bandwidth).

To measure these, we are asking ourselves how to tune the databases best. We don't care about the performance but only the I/O requirements. Right now, we have measured MySQL and Postgres and kept both databases at their default configurations. This includes rather smaller buffers and a huge chunk of OS-based page buffering. We intending to test the same queries with large buffers to see whether there is a substantial difference.

So our main questions are: - is our approach fair to evaluate I/O requirements? - should we tune each database (even though we care about I/O and not performance) or is it acceptable to use defaults? - if we should configure each database, do you have any hints on what to tune towards better I/O performance?


texts: Attempting to backup to remote NAS but access is denied

texts:
I was attempting to use Ola Hallengren's scripts to backup to a remote NAS, but it was failing and not giving any direct errors, so I tried doing it manually using Server Management Studio and got the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request.
(Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The operating system returned the error '0x80070005(Access is
denied.)' while attempting 'SvlPathDoesPathExist' on
'\Dl-nas-01\LOCAL FILE BACKUP\OLASQL'. The statement has been
terminated. (Microsoft SQL Server, Error: 3634)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2002&EvtSrc=MSSQLServer&EvtID=3634&LinkId=20476

Now, the account I am signed into has FULL permission and access to that share, my connection is my domain\myaccount, and from the VM I can even put \\Dl-nas-01\LOCAL FILE BACKUP\OLASQL into a folder browser and access it.
Does SQL Server run these from a different account I don't know about? Backing up to a local location works fine, what's the solution? I'm pretty lost.


texts:
When I tried to start the Informix instance in Linux using oninit, it says

WARNING: server initialization failed, or possibly timed out (if -w was used).
Check the message log, online.log, for errors.

Log says:

13:10:30 IBM Informix Dynamic Server Started.
13:10:30 Insufficient free huge pages in /proc/meminfo for shared memory segment.
Requested: 2610302976 bytes. Available: 0 bytes.
The default memory page size will be used.
13:10:30 Segment locked: addr=0x44000000, size=2610302976
13:10:30 Insufficient free huge pages in /proc/meminfo for shared memory segment.
Requested: 1048576000 bytes. Available: 0 bytes.
The default memory page size will be used.
13:10:30 Segment locked: addr=0xdf961000, size=1048576000

13:10:33 Event alarms enabled. ALARMPROG = '/opt/informix/etc/alarmprogram.sh'
13:10:33 Booting Language  from module
13:10:33 Loading Module
13:10:33 Booting Language  from module
13:10:33 Loading Module
13:10:39 DR: DRAUTO is 0 (Off)
13:10:39 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
13:10:39 shmget: [EEXIST][17]: key 52564803: shared memory already exists
13:10:39 (2) shm creation of shmem segment failed
13:10:39 Trusted host cache successfully built:/etc/hosts.equiv.

onclean shows:

 - Looking for semaphore ID: 950274
 - Looking for the shmem key: 52564801
 - Looking for the shmem key: 52564802

oninit -v shows:

 6136
Reading configuration file '/opt/informix/etc/onconf.cars'...succeeded
Creating /INFORMIXTMP/.infxdirs...succeeded
Checking config parameters...succeeded
Allocating and attaching to shared memory...succeeded
Creating resident pool 1494788 kbytes...succeeded
Allocating 1024016 kbytes for buffer pool of 2K page size...succeeded
Creating infos file "/opt/informix/etc/.infos.jensuse02"...succeeded
Linking conf file "/opt/informix/etc/.conf.jensuse02"...succeeded
Initializing rhead structure...succeeded
Writing to infos file...succeeded
Initialization of Encryption...succeeded
Initializing ASF...succeeded
Initializing Dictionary Cache and SPL Routine Cache...succeeded
Bringing up ADM VP...succeeded
Creating VP classes...succeeded
Forking main_loop thread...succeeded
Initializing DR structures...succeeded

WARNING: server initialization failed, or possibly timed out (if -w was used).
Check the message log, online.log, for errors.


texts: SQL Server 2014 Always On .. can I have replication without a listener?

texts:
I am still a novice with SQL Server Always On, having recently converted from a standalone server to an always-on configuration. I currently have two nodes on the same subnet configured in my availability group which is working out great.

The problem comes when I try to configure a third node into the availability group. This third node is across the WAN in a completely different subnet.  When I try to add the third node to the group I get an error in the step for "Joining secondary replicas to availability group "agname". clicking on the details of the error

  None of the IP addresses configured for the availability group
  listener can be hosted by the server <new server name>. Either
  configure a public cluster network on which one of the specified IP
  addresses can be hosted, or add another listener IP address which can
  be hosted on a public cluster network for this server

So, I tried to add another listener address, but viewing properties of the already configured listener does not give the option to add another address. Trying to add a second listener throws an error that a listener is already configured.

Backing up a moment, I do not need this third node to be a failover candidate. I want it to be a read-only copy. Is it possible to add it into the replication without having a listener in its subnet?

Is there another way to add a second listener with a DHCP address in that subnet so I can complete the wizard? Searches online so far have only shown workarounds by creating additional client access points in the failover cluster configuration itself which is a route I am not sure I wanted to take.


texts: Second opinion about elimination of possible redundant database column

texts:
We currently have a Widgets database table were we track the widgets the company produces. When a widget is produced the widget information is added to this table. This Widgets table contains a Status column used to keep track of whether a widget is in inventory or if it has been shipped. So basically, if the widget is in inventory the value of this column is Inventory and if the widget has been shipped the value of the column is Shipped.

We also have a Shipped Widgets table were we track the widgets being shipped. When a widget gets shipped we add its id (and some other information) to this table.

What I would like to do now is to remove the Status column from the Widgets table because I consider the column to be redundant since we could just as easily use the Shipped Widgets table to determine if a widget is in inventory or if it has been shipped. In other words, if the widget is not in the Shipped Widgets table it means that is in Inventory and if the widget is in the Shipped Widgets table it means it has been Shipped.

One obvious issue with this change is that right now, if all I want is to find out what Widgets are in inventory all I need to do is query the Widgets table for a Status value equals to Inventory and I am done. However, after I remove the Status column I will need to use a more elaborated query that retrieves the Widgets that are on the Widgets table that don’t exist on the Shipped Widgets table. This may slow things down a little but I hope the benefits outweigh the cons.

This all looks pretty obvious and maybe like a good idea but before I proceed and make the changes I was wondering if anyone sees something wrong with this approach. I just don’t want to make the change a latter regret it because I failed to notice a critical drawback.

Thank you.


texts: postgresql updating integer column taking more space than expected

texts:
I have two tables, A(a1 serial, a2 varchar(8)) and B(b2 varchar(8));

My query looks like,

alter table B add column b1 integer;
update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = t2.b2;

Here's my problem:

B was originally around 46 GB and had 6*10^7 entries. According to the documentation integers take up 8 bytes. Thus, 4 * 6 * 10^7 = 24 * 10^7 < 1 GB. The query is still executing. When I log in from another console and do

select pg_size_pretty(pg_relation_size('B'));

I see 60 GB. I'm expecting the size to be more than this when the query ends.

I'm confused by this hike in size. Is it because of the temporary data structures that it creates? If so could I expect the size to go down if i use VACUUM?

I'm using postgres 9.1.


texts: Calculating total "on row" bytes for each row ... the easy way

texts:
We want to calculate the total "on row" storage bytes for each row in the table.  As we understand it, we must add up the DATALENGTH() of each column while also accounting for NULLs and things like VARCHAR(MAX) which only have a 24-byte pointer "on row".  We are aware there is also some overhead for each row which is not accounted for in the query below.

SELECT   ROW_ID,

         CASE
              WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL1','PRECISION') = -1 THEN 24
              ELSE ISNULL(DATALENGTH(COL1), 1)
         END
          +
         CASE
              WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL2','PRECISION') = -1 THEN 24
              ELSE ISNULL(DATALENGTH(COL2), 1)
         END
          +
         CASE
              WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL3','PRECISION') = -1 THEN 24
              ELSE ISNULL(DATALENGTH(COL3), 1)
         END
          +
         ...
         ...
         AS ROW_SIZE

FROM     EXAMPLE_TABLE

ORDER BY ROW_SIZE DESC
;

What a beast!  And it's only an approximation.

Then we discovered

DBCC SHOWCONTIG ('EXAMPLE_TABLE') WITH TABLERESULTS

which returns MaximumRecordSize.  This reveals that there is already an algorithm buried somewhere within SQL Server which is capable of calculating the exact size of a row.

How can we access that algorithm directly?


texts:
Is it possible somehow to run the postgres batch tool pg_bulkload on windows? I could not find any documentation on this, only for linux.

Though it should be possible as the docs state:

  If you use pg_bulkload in Windows operating system, postgresql script
  is not included in a pg_bulkload package. So you have to invoke
  "pg_bulkload -r" manually.

Finally it should be triggered from inside a java tool, which itself also has to run on the same machine.


texts:
Does creating a unique constraint on a Postgres column remove the need to index it?

I expect that an index is automatically needed to maintain the constraint efficiently.


texts: How to deploy case-insensitive embedded firebird database?

texts: I'm trying to find out how to create CI firebird embedded database. I've found (here) that there are some character sets each of them has one or many collations. So, UTF8 has UCS_BASIC, UNICODE and UNICODE_CI collations. From documentation I can set any collation for any column during table creation. My question is: Is it possible to set collation on whole database? Or I have to impose collation on all columns?


texts: Duplicate results from query trying to find cross-indexed multiple tables

texts:
I working with a database of several hundred schemas with 250 tables and thousands of records. PGAdmin III 1.22 as my only tool. A GUI interface to the database is not robustly written and occasionally cross-indexes some entries. I am trying to write a query that shows a list of the cross-indexed items and display their LABEL to the users rather than the ID. This allows the user to use the GUI program to fix the issue with out requiring postgresql knowledge.

Here's the query at this point:

SET search_path="SCHEMA_NAME";
SELECT
  e1.label,
  c1.label,
  h1.contentsid,
  h1.group
FROM
  hostdata as h1
INNER JOIN hostdata as h2
  on h1.group = h2.group
  and h1.contentsid = h2.contentsid
  and h1.id != h2.id
INNER JOIN class as c1
  on t1.classid = c1.id
INNER JOIN element as e1
  on t1.elementid = e1.id
ORDER BY
  t1.group ASC,
  t1.contentsid ASC,
  c1.label ASC,
  e1.label ASC;

The cross-indexing occurs when the GROUP and CONTENTSID fields are both duplicated in two or more records in the HOSTDATA table. It's OK to have one duplicated as long as both fields are not the same. If it matters, the GROUP field is text.

What I am trying to do is compare the records in HOSTDATA to each other, list those that match another record in the GROUP and CONTENTSID fields, and then list them along with the LABELs from the CLASS and ELEMENT tables.

The above query works perfectly as long as there are only two matches, but as soon as the number of matches exceeds that the results multiply - 8 matches results in 7 returns per record - one for each match - 8 times 7 instead of just a list of 8. This is obviously because the first INNER JOIN returns multiple true conditions.

Performance is not an issue. I haven't yet come up with a way to re-arrange the sort and query to return the desired truncated output.


texts: Which column to trust about the event order in a profiler result?

texts:
Hi I recorded some events using the SQL profiler and I want to work out the exact order of what happened first. But I am having trouble in this basic task, because what ORDER BY EventSequence showed was different to ORDER BY EndTime and also different to ORDER BY RowNumber

Which column should I trust?


texts: Does a varchar column in be eliminated in a 3NF database?

texts:
There is a debate on if a VARCHAR column breaks 3rd Normal Form. Specifically the column represents a NAME that can be derived from other metadata specific to the entity. Occasionally the column contains customized input from users.

Should the column be eliminated and the application concatenate the metadata together to display the value, OR does a VARCHAR column that occasionally gets modified not break 3NF and should stay in the table.


texts: SQL Clustering options with 2012 standard Ed

texts:
i need help to find the best solution (if one exists..) to build a sql cluster using two servers and two sql Standard Edition licenses (2012 or 2014 or 2016).
No shared storage is available
No need for an active/active configuration

I did try searching and reading but still cant understand how to do that, i'm clearly not a dba

thanks for your help!
RL


texts:
I have the following tables: users, highscores

I would like to get the top ten best results in highscore. The scores should be unique, meaning one score per user. I tried using INNER JOIN and GROUP but this is not returning the correct data. I get the correct uid, score, alias and email but the time is not correct. It seems that it is taken from another highscore post. How do I get the correct data? As soon as we find the "best score" I want everything else (time) from the exact same post.

SELECT UID, ALIAS, EMAIL, SCORE, TIME
FROM users t
INNER JOIN (SELECT user_uid, MAX(score) as SCORE, value1 as TIME
            FROM highscores
            GROUP BY user_uid) x
  ON x.user_uid = t.uid
ORDER BY SCORE DESC
LIMIT 0, 10


texts:
I got one mongo v2.4.10 server with 1.7TB data, I am trying to migrate and upgrade the mongo to mongo v.3.0.15 server

I've setup a new mongo v.3.0.15 and configured replication for v3.0.15 to be secondary to sync with v.2.4.10 primary mongo.

The secondary was in STARTUP2 and the sync was almost finish as I can check with the growth of my storage device for the new machine which running mongo v.3.0.15

However there were some socket exceptions which caused both of my machine to resyn again from the start, just to ask anything I can configure or setup to prevent the error to happen again because I don't want to waste another 7 days to fail to sync up 1.7TB again.

Below are some logs from my mongo:

Primary mongo (v2.4.10):

Wed Jul 3 10:03:59.196 [conn21] SocketException handling request, closing client connection: 9001 socket exception [SEND_ERROR] server [x.x.x.x:32829]

Secondary mongo (v.3.0.15)

...
2019-07-03T09:54:29.169+0800 I NETWORK [ReplExecNetThread-0] Socket recv() timeout 192.168.168.122:27017
2019-07-03T09:54:29.169+0800 I NETWORK [ReplExecNetThread-0] SocketException: remote: 192.168.168.122:27017 error: 9001 socket exception [RECV_TIMEOUT] server [192.168.168.122:27017]
2019-07-03T09:54:29.169+0800 I NETWORK [ReplExecNetThread-0] DBClientCursor::init call() failed
2019-07-03T09:54:29.169+0800 I REPL [ReplicationExecutor] Error in heartbeat request to 192.168.168.122:27017; Location10276 DBClientBase::findN: transport error: 192.168.168.122:27017 ns: admin.$cmd query: { replSetHeartbeat: "ArchiverReplica", pv: 1, v: 1, from: "x.x.x.x:27017", fromId: 1, checkEmpty: false }

2019-07-03T09:54:29.170+0800 W NETWORK [ReplExecNetThread-0] Failed to connect to 192.168.168.122:27017 after 1 milliseconds, giving up.
2019-07-03T09:54:29.170+0800 I REPL [ReplicationExecutor] Error in heartbeat request to 192.168.168.122:27017; Location18915 Failed attempt to connect to 192.168.168.122:27017; couldn't connect to server 192.168.168.122:27017 (192.168.168.122), connection attempt failed
...
2019-07-03T10:07:41.452+0800 W NETWORK [ReplExecNetThread-0] Failed to connect to 192.168.168.122:27017 after 4995 milliseconds, giving up.
2019-07-03T10:07:41.452+0800 I REPL [ReplicationExecutor] Error in heartbeat request to 192.168.168.122:27017; Location18915 Failed attempt to connect to 192.168.168.122:27017; couldn't connect to server 192.168.168.122:27017 (192.168.168.122), connection attempt failed
2019-07-03T10:07:43.602+0800 I REPL [ReplicationExecutor] Member 192.168.168.122:27017 is now in state PRIMARY
...
2019-07-03T10:08:03.845+0800 I NETWORK [rsSync] Socket recv() errno:104 Connection reset by peer 192.168.168.122:27017
2019-07-03T10:08:03.845+0800 I NETWORK [rsSync] SocketException: remote: 192.168.168.122:27017 error: 9001 socket exception [RECV_ERROR] server [192.168.168.122:27017]
2019-07-03T10:08:03.853+0800 I NETWORK [rsSync] trying reconnect to 192.168.168.122:27017 (192.168.168.122) failed
2019-07-03T10:08:03.928+0800 I NETWORK [rsSync] reconnect 192.168.168.122:27017 (192.168.168.122) ok
2019-07-03T10:08:03.939+0800 E REPL [rsSync] 16465 recv failed while exhausting cursor
2019-07-03T10:08:03.939+0800 E REPL [rsSync] initial sync attempt failed, 9 attempts remaining
2019-07-03T10:08:08.939+0800 I REPL [rsSync] initial sync pending
2019-07-03T10:08:08.958+0800 I REPL [ReplicationExecutor] syncing from: 192.168.168.122:27017
2019-07-03T10:08:09.204+0800 I REPL [rsSync] initial sync drop all databases
2019-07-03T10:08:09.205+0800 I STORAGE [rsSync] dropAllDatabasesExceptLocal 3
2019-07-03T10:08:09.221+0800 I JOURNAL [rsSync] journalCleanup...
2019-07-03T10:08:09.221+0800 I JOURNAL [rsSync] removeJournalFiles
2019-07-03T10:08:09.895+0800 I JOURNAL [rsSync] journalCleanup...
2019-07-03T10:08:09.895+0800 I JOURNAL [rsSync] removeJournalFiles
...
resyn from the begining .......


texts: Efficiently store large list structure in RocksDB so that the data can be retrieved in pages

texts:
Description:

RocksDB is a key-value storage so we can simply serialise the list of objects & store the value corresponding to a key. This would be ok if the data in the list is small enough.

But if the list is large and ever increasing in size then we would need the data paginated. So in this case storing the entire serialised list data corresponding to a single key would not be a good idea; as there would be a performance issue since every time a new data is inserted into the list this very large value would need to be read & updated also during read time when showing list to user entire value would be retrieved while only a part of it was needed by the user.

Ex: Let’s say we want to store orders placed by user in rocksDB. Then we could store this order data in following way in RockDB   “u:1:li:o” : Serialised([O1{}, O2{},….On{}]). But if there are thousands of orders placed by user and we would like to retrieve the orders in form of pages (10 or 20 records at a time). So storing thousands of order in same key and retrieving entire data from that key & then giving required 10-20 records won’t be a good idea. Also adding new order by user to same key will affect the performance as described above.

So I am working to design schema for efficiently storing and retrieving such large lists in RocksDB.

If you can give your suggestions on schema design that would be great & very much helpful.


texts: Mysql Workbench 8 doesn't detect the my.ini file for my MySql 5.7 server. How can I point it to the correct file?

texts:
I upgraded MySql workbench to version 8.
I'm running MySql Server 5.7.
When I open the options file in workbench it says it cannot locate a file for MySql 8 and that it will create one in the path C:\ProgramData\MySql\MySql Server 8\my.ini
I do not have MySql Server 8 installed.

Why is workbench not finding the file?
How can I point it towards the correct file?
Do I need to downgrade workbench?


texts: Mysql Alter table with Instant algorithm

texts:
I just read this MySQL 8 official docs for new features of online DDL operation, where new algorithms added INSTANT. but I am a little bit confused about its working. Can someone please help me to understand the below statement

The benefit of the INSTANT algorithm is that  only metadata changes
are made in the data dictionary.  There is no need to acquire metadata
lock during SE changes and we  don’t touch the data of the table.


texts: SQL Server Automatic Tuning (for query plans) vs. Availability Groups

texts:
In SQL Server 2017 (and up), for a database configured within an Availability Group, with query stored enabled, will automatic tuning work on the read-only replicas?
I don’t think so, because query store data is stored in the read/write database, gets copied to all AG read-only replicas, would thus not represent/support any query activity being performed on a given read-only replica, and so could not support automatic tuning for any read-only replicas.
Is this correct? I’m hoping someone can disprove this, or provide some kind of work around. We’re getting serious query sniffing problems on some important read-only replica queries, and if automatic tuning will make this go away, management might buy into an upgrade.
(I was not able to find anything on this on the web, and hardly anything on automatic tuning.)


texts: Database slowdown at random times using mirroing

texts:
Our web application suffers DB query timeouts a few times a day at random times. Can you help to work out the cause, e.g. deadlocks, mirroring (disk or network), etc?

We have two identical database servers (and a third witness server) with MSSQL 2008 R2 mirroring in high safety mode with automatic failover. Two DBs are mirrored and each server is principal in one so in the event of a failure, both DBs will run from one server. As far as I can see, the problem only occurs on one of these two databases.

Using performance monitor, I can see the "Log Harden Time (ms)" on the principal server spikes to 212,301.206ms at the exact time a timeout occurred (07:30 today). The average from a 24hr period is 22.712ms.

What else should I be looking at to determine the cause of this slowdown?


texts: if exists, update else insert, with cursors in stored procedures only returns 1 row

texts:
I have 2 tables api(columns - api , api_context, api_id -->primary key) and api_request_summary(columns - api_context). i need to insert the number of times the api_context relevant to a particular api_id is repeated in the table curhittest1. both tables have multiple rows so i have used cursors to loop through the tables. i can correctly insert the values to the table but i need to check wether that api_id already exists in the curhittest1 table and if yes update and if no insert.

DELIMITER //
CREATE PROCEDURE curhit12()

BEGIN

 DECLARE done INT DEFAULT FALSE;
 DECLARE apiId, noOfHits int(11);
 Declare apiContext varchar(255);

 DECLARE cur1 CURSOR FOR Select api_id from api;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO apiId;

    IF done THEN
      LEAVE read_loop;
    END IF;

    select api_context into apiContext from api where api_id =apiId;

    SELECT COUNT(*) FROM api_request_summary WHERE api_context=apiContext into noOfHits;

 IF exists (SELECT * FROM curhittest1 WHERE apiid = apiId) THEN

   UPDATE curhittest1
                      SET noofhits=noOfHits
                      WHERE apiid = apiId;
 ELSE

  insert into curhittest1(apiid,noofhits) values (apiId,noOfHits);
 END IF;

  END LOOP;

  CLOSE cur1;

END//
DELIMITER

When i use the following code only 1 row is added to the curhittest1 table. When the exact same code is used in sql procedures without cursors it displays so i'm assuming i need to do something differently when using with cursors. How can i add all the values to the table accurately?


texts: Is it possible to migrate data from a shard MongoDB to a non-shard MongoDB with dump

texts:
Question:

I have a shard mongo cluster and I want to migrate data from this cluster to a one node mongo instance. So I want to know is it possible to use dump/restore?

  I use pymongo and Django and python3 and mongo3.4

If the answer is No, So what is the best way to migrate data from a mongo cluster to a single node mongo instance?

I tried to write a script to migrate the data:

# fetch data from old cluster
data = MongoCollection._get_collection().find(query)

# insert data to new single mongo instance
NewMongoCollection.insert(data)

What I did had a lot of issues such as:

Fetch whole data and store data in RAM
If something goes wrong, I have to start it again
...


texts: Insert into a specific partition, create if not exists

texts:
So I have my master table:

p_reports: id(pk), web_id, other(data)

At the moment I create my partitions which inherits from p_reports by using:

CREATE TABLE IF NOT EXISTS reports_#{web_id}(CHECK(web_id = #{web_id})) INHERITS (p_reports);

Then I do a bulk insert into that table, this works fine, but for this I need to group to create blocks of data for the specific partition, and do multiple inserts.

Is it posible to have a function, so I can send all my data at once, and the function will create partitions if they don't exist and then insert the data in the corresponding partition?


texts: What's the difference between fetch and fetch next?

texts:
I've got a cursor sending out pager messages, and occasionally it sends out duplicates. The syntax looks like this:

    declare @col1 varchar(255),
    @col2 varchar(255)

    declare c cursor local for

    select column1, column2
    from table
    where flippity = 'floppity'

    open c
    fetch  from c into @col1, @col2
    While @@FETCH_STATUS = 0
    begin

    sp_do_your_thing @col1, @col2

    fetch next from c into @col1, @col2
    end
    close c
    deallocate c

Question: if I'm getting duplicates, could it be the fetches? do they both need to be fetch next, or neither?


texts: Maintenance plan in AlwaysOn environment

texts:
I need to create below three jobs in AOAGs SQL Server.

CheckDB integrity,
Update stats,
Re-organize index

My question is ,Do I need to create in only Primary Replica or all the secondaries also ?


texts: Finding same values and updating rankings

texts:
thank you for taking the time to look at this.  I have a simple table that I am trying to update based on the entries.

table name is TOPUSER and there us a field called tu_total and tu_rank, I would like to look through this data and see if there are tu_total that are the same and if so those records get the same tu_rank and the tu_rank.  the ranking starts out at 1 and goes up from there.  Also if a users have a 0 value in tu_total then they all would get the next number above the last user's tu_rank who actually has data in their tu_total.

Here is a sample of what it currently looks like:

So as you can see Mark and Bill have the same values of tu_total = 3

So it should look like this:


texts: How to effectively limit a selection size in a generic manner in Oracle?

texts:
Let's say, we have the a query to an Oracle database:

SELECT *
FROM FOO f
WHERE f.bar > baz
ORDER BY f.qux;

And we are looking for a way to limit the selection size in a generic manner.

The approach of wrapping a query in the following way may be applied:

SELECT *
FROM (
  SELECT *
  FROM FOO f
  WHERE f.bar > baz
  ORDER BY f.qux
)
WHERE ROWNUM <= 1;

Though, it is easy to employ, it may be shortened to the following one with a small price of the input query analysis (to place AND ROWNUM <= 1 at the right position):

SELECT *
FROM FOO f
WHERE f.bar > baz
    AND ROWNUM <= 1
ORDER BY f.qux;

Would the later query practically perform better? Would it affect caching & optimizing in any significant way?


texts:
I have a PostgreSQL table:

CREATE TABLE essays (
    id serial primary key,
    content text
);

I inserted a few rows, then ran:

ALTER TABLE essays DROP COLUMN content;

How can I recover the data? I'm willing to use any means.


texts: "SQL command not properly ended" with trigger

texts:
Problem
No matter what I do, I can not get my trigger to work. It seems to always complain about SQL command not properly ended, ignoring SQL statement and so on.
Research
I have spent hours looking into why this issue is occurring and I still have come up empty handed. This is my first time creating a TRIGGER so I have a feeling it has something to do with the placing of things.
What I have tried
I have tried many many things. I have tried adding a comma to the last variable in the brackets, I have tried adding ; to random statements and so on. This is my trigger:
CREATE OR REPLACE TRIGGER create_base_invoice
AFTER INSERT ON reservation
BEGIN
    INSERT INTO
        invoice
    VALUES
        (i.resnum, i.bnum, i.rnum, c.cfname, c.clname, i.bstartdate, null, null)
    SELECT
        i.resnum, i.bnum, i.rnum, c.cfname, c.clname, i.bstartdate
    FROM
        INSERTED i, CUSTOMER c
    WHERE(
       (i.cfname = c.cfname)
       AND
       (i.clname = c.clname)
        );
END create_base_invoice;

I do not understand the point of the SELECT command in my case and I have tried removing it and I do not get a different error so I am not sure if I need that there or not.
The reason I added it was due to stumbling across a post with a very similar scenario of mine and seeing the answer contain that SELECT statement which at the time I was missing.
The reason I need the where claus is because the Reservation table does not actually hold the customer name and surname (which we need in this case). It just holds the customer number. Here is the reservation table:
RESNUM
CNUM
BNUM
RNUM
BSTARTDATE
BDURATION
BOCCUPANTS
BSTATUS
BPAYMENT

I removed the VALUES clause, and now the error is

3/78 PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification

I removed ,null ,null too. It then complains

missing VALUES keyword

So, what I am trying to do is create a invoice based off of a reservation. When someone books a reservation, the trigger then  saves certain variables from the reservation (resnum, bnum, rnum, bstartdate, cnum) and then it finds the customers full name based off their customer number (cnum).
All of this is saved into invoice to make a base invoice. Then I have another procedure that adds the checkout date and calculates total price of stay. We do not save cnum into invoice.
Just to clarify, cnum is just used to essentially search for the customer and find their first and last name which is then saved onto the invoice.


texts: What happens when independent writes are made to a MySQL DB and its writeable read-replica

texts:
The problem:
I have a production RDS MySQL DB (PROD-DB). Data from it is read and written to by my production API. I would like to deploy migrations to a replica of PROD-DB and have the UAT instance of my API read/write to this replica. The reason being that I can verify that all migrations/API updates work as expected (with essentially production data), while allowing the production API to read/write to PROD-DB during this verification period.
Consideration:
I am considering creating a Writeable Read-Replica (WRR) of PROD-DB and have my UAT API read/write to this during the verification period. Once verification is complete, the WRR can be promoted to be the new PROD-DB.
I suspect one of the problems with the considered approach, is new records may be created by the UAT API within WRR with primary keys that conflict with new records added to PROD-DB via the production API. Primary keys in all tables are set to auto increment.
Question 1: Is my above suspicion correct?
Question 2: Is there another solution/pattern that can be used to address the problem?


texts: How to determine the optimal sort_buffer_size?

texts:
I read from a sample configuration file which says the following:

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.

I have a couple of queries which use filesort. How do I determine what is the size of the buffer I need for queries to run smoothly without hitting the disk?


texts: Recommended settings for a small table with high update rate

texts:
I have a small table which can be explained as follows:

create table mytab (
    primary_key_column number,
    a number,
    b number,
    c number,
    d number,
    e number,
    f number
);

It will have very few rows (not more than 256 records) but they will be updated at a high rate (for example one row could have up to 1000 updates per second).

What recommended settings should I use for the table to be able to handle this load and avoid update contention? Or other suggestions for doing this?

I will be using Oracle 12c or newer.

Edit

Concerning the high nnumber of updates, as was asked in the comments.
With pctfree and initrans I can perhaps get each row in a separate block, but there will still be a lot of updates for a given key. So any suggestions on how to handle that?
I see only three ways forward on this.

1) don't update but write the increments to a separate table and then have a job summing them and do the update regularly. This will get me out of the update contention, but I will be doing a lot of inserts instead

2) change the primary key in some way, like

create table mytab(
  primary_key_column number,
  sub_key number,
  a number,
  ...

and then let updates choose a random "sub_key" for the given primary_key. Sub_keys could be like the numbers 0-99 to give each row only 1% of the updates.

Given that all values a-f are zero at start, the total values can then be gotten by summing over the primary key.
It's ugly, but it could work.

Might the In-memory feature solve the problem?

Any comments on this?


texts:
We know WAL file locate in the $PGDATA/pg_xlog directory,  but I don't know the meaning of WAL file ,such as a WAL file which named "0000000A00005283000000E0",

I see some's blog say WAL file name  respect a format name subdivided into 3 sequences of 8 hexa digits defining:

Timeline ID

Block ID

Segment ID

But I still don't understand this. anyone can explain this?

--query

francs=> select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 5283/D9C2A320
(1 row)

francs=> select pg_xlogfile_name(pg_current_xlog_insert_location());
     pg_xlogfile_name
--------------------------
 0000000A00005283000000E0


texts: PostgreSQL 10 Optimizing slow query performance with aggregate function

texts:
I have two tables person and tag.
person has an bigint[] column containing ids of tags.
I want to write a query which fetches all tags including the count of the tagged persons.
So far I have the following query

SELECT t.id AS id, t.name AS name,
(select count(id) from person where person.tags @> array[t.id]) as instances_count
 FROM tag AS t

I know the sub-query is bad but I couldn't think of a way of doing it better.

Any suggestions are welcome.
Thank you

Edit:
PostgreSQL 10.8

Here is the 'explain analyze':

Seq Scan on tag t  (cost=0.00..4309761.27 rows=79 width=41) (actual time=131.109..11329.945 rows=79 loops=1)
  Buffers: shared hit=560506 read=3505230
  SubPlan 1
    ->  Aggregate  (cost=54553.91..54553.92 rows=1 width=8) (actual time=143.411..143.412 rows=1 loops=79)
          Buffers: shared hit=560505 read=3505230
          ->  Seq Scan on person i  (cost=0.00..54550.82 rows=1233 width=8) (actual time=41.367..143.393 rows=148 loops=79)
                Filter: ((NOT is_deleted) AND (tags @> ARRAY[t.id]))
                Rows Removed by Filter: 245452
                Buffers: shared hit=560505 read=3505230
Planning time: 0.098 ms
Execution time: 11330.014 ms


texts: Give third-party access to AWS hosted MySQL

texts: I have a production MySQL database hosted on AWS for an eCommerce store I work with. Employees in the firm are requesting access to our live SQL database to use with BI tools like Tableau/Qlikview. How can I grant these users read-only permissions to analyze data from the live database and not the ability to write data?


texts: How to quote database names with spaces when using queryout with bcp?

texts:
I'm attempting to use bcp on Microsoft SQL Server 2000 to export a couple of columns from a large database table.

The database names contain spaces. Here's an example that works that contains an export of the whole table:

bcp "database name with spaces.dbo.table name with spaces" out
    C:\database_name_with_underscores.csv -c -q -t ","
    -S "server_address" -U username -P password -k

I'd like something along the lines of:

bcp "SELECT TOP 10
        [column name with spaces],
        [another column name with spaces]
    FROM [database name with spaces].dbo.[table name with spaces]"
    queryout C:\database_name_with_underscores.csv -c -t "," -S "server_address" -k

...but I've not been able to get it to work using combinations of the -q flag and brackets [] or quotes ". I receive errors similar to:

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Invalid object name 'database name with spaces.dbo.table name with spaces'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Statement(s) could not be prepared.

Any help is appreciated.


texts: PostgreSQL: DBLink weird permission/connection error

texts:
The goal is to (1) create a connection, (2) return a view of another database, and (3) close the same connection all to be stored in a view (no stored procedure or embedded password).  I came up with the following code, which uses a trust account and seems to work, until filtering on a boolean field.  Searching for false (field=false) works, but searching for true (field=true) or IS NOT NULL prompts for the password.

It's really odd that the data is being returned w/o needing the password, but when filtering, that is when the password is required.

PostgreSQL: v8.4:

There are two databases: db_remote and db_local
There are two accounts:

trusted_user (set up as trust in pg_hba.conf to both databases)
md5_user (set up as md5 in pg_hba.conf to both databases)

db_remote has a view (v_sessions), which contains two fields: a text field and a boolean field, which is created with a CASE statement.

From within db_local (as md5_user):

-- Creates okay:
CREATE VIEW v_sessions AS
SELECT * FROM (
     select  '1' query_type,'' as username, false as is_logged_in
     from    dblink_connect_u('connection', 'host=development dbname=db_remote user=trusted_user')
     union
     select  '2' query_type, username, is_logged_in
     from    dblink('connection', 'select username, is_logged_in from v_sessions') as v_session(username text, is_logged_in boolean)
     union
     select  '3' query_type,'',false
     from    dblink_disconnect('connection')
) v_sessions
WHERE query_type=2;

-- Calling the view with filter:
SELECT * FROM v_sessions WHERE is_logged_in;

What works:

removing the AND is_logged_in
filtering on a text field AND username = 'some value' !!
filtering on the boolean field for a false (AND is_logged_in = false) !!

What doesn't work:

filtering on the boolean field for a true value: AND is_logged_in IS NOT NULL, AND is_logged_in, or AND is_logged_in = true, gives the following error message:

  ERROR:  password is required
  DETAIL:  Non-superusers must provide a password in the connection string.

Filtering inside the second query works, but it doesn't help when wanting to store this in a view.

I suppose I'm looking for suggestions, but really trying to understand what is going on eg. why searching for false works and true does not - I first thought the connection was preemptively closing, but it's not.


texts: Primary Index VS Unique Index Performance

texts:
I have the following table:

CREATE TABLE `actions` (
  `user_id_from` int(11) unsigned NOT NULL,
  `user_id_to` int(11) unsigned NOT NULL,
  `action_type` int(11) NOT NULL,
  `counter` smallint(11) unsigned NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`user_id_from`,`user_id_to`,`action_type`),
  KEY `user_id_to` (`user_id_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Currently it has around 1.3 Billion Rows, Data size is 109GB and Index Size is 44.5GB.

The INSERT queries on this table come in chunks of ~1k rows all with the same user_id_to. Most of the SELECTS have user_id_to in the WHERE condition, user_id_from is also used but less frequently, and results are mostly sorted by counter DESC and grouped by user_id_from.

Currently both INSERTs and SELECTs are running much slower than I'd expect. I have a theory a possible improvement will be to replace between the two indexes and making the new indexes:

  PRIMARY KEY (,`user_id_to`, `user_id_from`,`action_type`),
  KEY `user_id_from` (`user_id_from`)

The hope is that all writes will happen in the same area on the disk, and index changes therefore will also be minimal, and same thing for fetching the data when having one specific user_id_to as a WHERE condition, all data will be in the same place on disk, so it will run much faster.

Does my theory hold water? Any other suggestion?

UPDATE: My Queries:

SELECT COUNT( DISTINCT user_id_from) FROM actions WHERE user_id_to=:user_id

SELECT locations_users.*, location_country, countryCode, location_state, location_city
                    FROM actions INNER JOIN locations_users ON actions.user_id_from = locations_users.user_id
                        INNER JOIN countries ON locations_users.countryId = countries.countryId
                        LEFT JOIN states ON locations_users.stateId = states.stateId
                        LEFT JOIN cities ON locations_users.cityId = cities.cityId
                    WHERE actions.user_id_to = :user_id

SELECT actions.counter, actions.user_id_from, actions.action_type
            FROM actions
            LEFT JOIN users ON actions.user_id_from=users.user_id
            WHERE actions.user_id_to=:user_id AND users.something>50
            ORDER BY actions.counter DESC LIMIT 40


texts: Get next row based on current row and conditional ordering

texts:
I have this table:
Id   Name
1    M
2    P
3    A1
4    C
5    A2
6    T
1009 A2
1011 A1
1010 A3

I can get it sorted by Id if Id < 1000 and by Name if Id > 1000 (thanks to the help of people here) with this query:
SELECT *
FROM table1
ORDER BY
  CASE WHEN Id < 1000 THEN Id ELSE 1000 END,
  Name;

Which gives
Id   Name
1    M
2    P
3    A1
4    C
5    A2
6    T
1009 A2
1010 A3
1011 A1

Now, given an Id, I want the next row in the order I just ordered by. So far, I've managed to get the result with two SQL queries, based on the givenId. If givenId < 1000 I do:
SELECT *
FROM table1
WHERE
  Id > @givenId
ORDER BY
    CASE WHEN Id < 1000 THEN Id ELSE 1000 END,
    Name
LIMIT 1

If givenId > 1000 I do:
SELECT *
FROM table1
WHERE
  Id > 1000 AND Name > (SELECT Name FROM table1 WHERE Id = @givenId)
ORDER BY
    CASE WHEN Id < 1000 THEN Id ELSE 1000 END,
    Name
LIMIT 1

The Id with value 1000 will never exist so I don't have to worry about that case. This works, but I'm wondering if there's a better way to do this? Perhaps with one query instead of two? I've tried to come up with something better but can't seem to.


texts: SQL Server: multiple failover cluster instances with disjoint storage

texts:
This is for SQL Server 2012. I currently have two datacenters that have separate SANs. I'm using failover cluster instances (FCIs) for server redundancy, and then AlwaysOn Availability Groups (AGs) for datacenter redundancy. This is what it looks like:

The problem is that I'd like FCIs at both datacenters, like so:

When we try to add the storage for Nodes3&4 we can't because the storage is not shared with Nodes1&2. At Add Storage to a Failover Cluster it says:

The disk must be available to all nodes in the cluster before you can add it to the set of storage for the cluster.

It is my understanding that Nodes1,2,3,4 must all be in the same Windows Failover Cluster in order to use AGs. However, this requirement seems to prevent us from being able to setup FCIs in both datacenters.

Is there any way to have my cake and eat it to?


texts: How do I sync instance A to instance B without losing any of the data already on instance B?

texts:
Like I said in a comment below, I'm no DBA and I'm still in a learning process.

Here's my scenario :

I have an Oracle server running a couple schemas (let' call it Source). What I would like to do is send a 1:1 copy of every objects to another Oracle server (let's call it Target) where there's already some objects that I need to preserve.

Ideally, I don't want to shutdown the Source server.

Also, I need to keep the already existing schemas of the Target server. So the new objects from Source need to co-exist on the Target server with the already existing objects. Maybe by using 2 instances that could be accomplish? I don't know, I'm just suggesting.

What could be the solutions to achieve that?


texts: What is the equivalent of the SQL Server CHAR function?

texts:
SQL Server has CHAR ( integer_expression ), so I can do

SELECT CHAR(65)

What is the equivalent on Postgresql? I have tried SELECT E'\0x65'; but I get

  ERROR:  invalid byte sequence for encoding "UTF8": 0x00


texts: Drill Down Functionality for Data Entry in Database

texts:
I am building a database that houses financial statement data. Within the database and to maintain the relational model and data integrity the fields are standardized. However, I do not want to lose the supporting data that the standardized data represents. For example, an income statement from one entity may be more detailed than another entity.

Company A
Depreciation = 100
Taxes = 50
Other Expenses = 300
Total Expenses = 450 * This is what is stored in the relational model

Company B
Total Expenses = 400

If I wanted Total Expenses to be the standardized field how could I capture within that field the detailed data that supports that total expense figure?

Many Thanks!


texts: The full contents of the bind-variable are not displayed in SQL Plus

texts: I wrote a big query in bind-variable. I want to print it in SQL Plus, but the text doesn't show, only small part of it.