texts:
We started to monitor sql server instance non-active server configurations and find something interesting.
Why is server configuration 'min server memory' configured at 0, but running at 16 (sometimes 8)?
The instances use the default value. 0 is the default and we don't change it, but apparently something changes it when SQL Server starts.
If this is the default behavior why isn't this value (16 or 8) configured as default instead of 0?

Does anyone have an (official/documented) explanation to this behavior?


texts: Why Does Oracle kill a session at the specific time?

texts:
Oracle kills sessions remotely open at the specific time. It means if I open a session at local machine, it is ok. In my case, at every 58 minute.

For example, at 13:58, 14:58, 15:58 and so on.

It means even if I open a session at 13:57, it will be closed 1 minute after.

My environment is:

I tested with sqlplus and my python application.
DB is placed in Windows Server 2012.
Firewall is turned off.
Network is connected over local router.

I also checked the Oracle alert.log, but there is nothing special.

Any suggestions?

--------------------Edit--------------------

I get a trace logs after setting TRACE_LEVEL_SERVER on sqlnet.ora.

There seems an error but I don't know what it means.

I run two program, one is my app and the other is seqlplus.

They missed connection at the same time 17:36.

Below are the trace log from each.

[07-4월 -2016 17:36:48:829] ntt2err: soc 4160 error - operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0
[07-4월 -2016 17:36:48:829] nserror: nsres: id=0, op=68, ns=12570, ns2=12560; nt[0]=530, nt[1]=53, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[07-4월 -2016 17:36:48:829] nioqrc: recv: packet reader error -> translated to IFCR_EOF
[07-4월 -2016 17:36:48:829] nstimarmed: no timer allocated
[07-4월 -2016 17:36:48:829] nsclose: closing transport
[07-4월 -2016 17:36:48:829] nsclose: global context check-out (from slot 0)
complete

.

[07-4월 -2016 17:36:48:829] ntt2err: soc 2216 error - operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0
[07-4월 -2016 17:36:48:829] nserror: nsres: id=0, op=68, ns=12570, ns2=12560; nt[0]=530, nt[1]=53, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[07-4월 -2016 17:36:48:829] nioqrc: recv: packet reader error -> translated to IFCR_EOF
[07-4월 -2016 17:36:48:829] nstimarmed: no timer allocated
[07-4월 -2016 17:36:48:829] nsclose: closing transport
[07-4월 -2016 17:36:48:829] nsclose: global context check-out (from slot 0) complete


texts:
We have an Oracle 12c (12.2.0.1) database server set up with AL32UTF8 (Unicode 5.0 UTF-8 Universal character set) character set as per Oracle recommendations.

However, the application that uses this database comes with a vendor recommendation to use WE8MSWIN1252 (MS Windows Code Page 1252 8-bit West European) character set instead.

I know, at least in general terms, how to convert from one character set to another, using either the Data Migration Assistant for Unicode or via a full Export/Import.

But there are two areas that I'm not clear on.

One is that Oracle says there is a potential for data truncation issues (and it explains how/why). Is there any way to find out if that's an issue within my database, short of converting the database in test and trying to do a row-by-row comparison between the converted and non-converted data?

More importantly, since the vendor wants us in a character set that's not the Oracle-recommended default, is there any way to measure what, if any, performance impacts the conversion may have? From what I can tell, the server is having to convert incoming data from the WE8MSWIN1252 character set to AL32UTF8 on inserts/updates and reversing that on selects, right? Are there any views that would let me see how much time the database is spending handling these conversions? I really can't imagine the performance hit is sufficient to justify the conversion, but I'd like to know for sure.


texts: Arabic characters not displaying in oracle database

texts:
We have a Oracle database 8.1.7 running for many years on Win-2000. We wanted to move the database on a newer machine running Win 2003. I did the following

I made a cold backup of old database.
Installed the oracle database same ver on the new machine and moved
the data files from the old to the new server.
Imported the data dump taken from export (taken from old server) on
the new server.
Without a full test we shifted live to the new server.
Everything was OK on the new oracle server except for the Arabic
characters. I am getting ?? (questions marks) characters in the
Arabic fields.

I checked the nls_database_parameters on both the servers.

Select * from nls_database_paramters;

The old database had: nls_char & nls_nchar = Ar8mswin1256
The new database had nls_char & nls_nchar = We8iso8859p1

I then did the following in the new server:

Changed the character set (nls_char)in the new database to
AR8mswin1256.
Changed the Nls Nchar set to Ar8mswin1256.

All the above done were by an alter database command as below:

SVRMGR> STARTUP MOUNT;
SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SVRMGR> ALTER DATABASE OPEN;
SVRMGR> AALTER DATABASE NATIONAL CHARACTER SET ar8mswin1256;
SVRMGR> SHUTDOWN IMMEDIATE;
SVRMGR> STARTUP;

I checked nls_database_parameters in the new server both nls_char, nls_nchar was changed to Ar8mswin1256.

I checked the database through our ERP applicatoin but still Arabic characters displaying in ??.

I then checked the registry of new Oracle server. The value of nls_lang is We8iso8859p1
whereas the registry of the old server had value of nls_lang as Ar8mswin1256. I changed
the value in new server to be American_america.Ar8mswin1256. Checked the database still
arabic characters displaying as ??.

The registry of Client has nls_lang as American_america.Ar8mswin1256.

It is a production database. How can I solve the problem of Arabic characters being displayed as ??? in the new server?

I still have the old server database but it is not live. It doesnt contain updated
data.


texts: AWS Webapp tutorial: how to choose source in database security group?

texts:
I am following the AWS tutorial to create a Webapp here: http://docs.aws.amazon.com/gettingstarted/latest/wah-linux/getting-started-create-rds.html

I am stuck at the part 'Create a Security Group for Your DB Instance'.

The tutorial instructs you to start writing 'WebserverSG' into the IP source, and to select an option. However, even after completely typing 'WebserverSG', no such option is presented and I am unable to create the security group.

Is there a way to know the source without selecting it from the options? Alternatively, have I done something wrong for the option to not be presented?

This is my first time dealing with AWS; I don't have a concrete understanding of the purpose of security groups - so please bear this in mind!

Thanks in advance for any answers.


texts:
So I have this side project of mine which relies on me agreggation User game data from an API.

What I want to store is the player encouters with other players and saving the result of those encounters aswell. The issue is that the only solution i have managed to come up is to create a Table for each User and have a 3 columns so far, IDPlayer (the player that was played against), GamesWon (the number of games won) and GamesLost (the number of games lost).

This wouldn't be an issue if my expected Player count was above 200k which would mean I would need to create at least 200k tables and each table could theorically contain 200k entries (if those players face each other which is unlikely but still possible). I have read that there could be performance problems once the number of Table have reached 10k.

My question is, is there a better way to handle this sort of information, am I on the wrong path ?


texts: How to pass an array to a plpgsql function with VARIADIC parameter

texts:
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.

I have already defined the function

CREATE OR REPLACE FUNCTION mix_table_fields(input_table_name VARCHAR, output_table_name VARCHAR, VARIADIC field_names VARCHAR[])
[...]

Now, when I try to call it:

SELECT mix_table_fields('articles'::VARCHAR, 'output_random_articles'::VARCHAR, array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]) ;

I get the following error : " the function mix_table_fields(character varying, character varying, character varying[]) does not exist".

I have had this error before. It cannot recognize the signature of the function (its input parameters).

When I use pgAdmin3 to look at the function definition, it is:

CREATE OR REPLACE FUNCTION public.mix_table_fields(
    IN input_table_name character varying,
    IN output_table_name character varying,
    VARIADIC field_names character varying[])

I cannot see what's wrong.

And if there is a way of getting rid of the ::VARCHAR at each parameter when calling the function, it would be easier.


texts:
I want to create a messaging system but I'm stuck in the relationships between the sender/receiver of the message and the message itself.

This is a list of requirements for this system:

Allow both registered customers and guests to send messages. Customers and guests can only send messages to the company departments. This means that customers can't send messages to other customers.
When creating the message, the creator needs to tell what type of message is creating by selecting an option from a list of categories.
Each category needs to be received by a "role" (So, billing related messages will be received by the financial department, support messages will be received by customer service, etc).
The messages can come from 1) a contact form available in the Contact page of the website where are asked fist name, last name, email, phone number, subject and message; and 2) emails sent by users (then the system will receive the emails via webhook and store them properly).
All departments can contact any user (registered or not) at any time. This means that they can reply to messages but also can "start" the conversation (example: A message asking to confirm/update details).

Here is what I currently have:

This covers almost everything, except for the users/guest management.

I can add fist name, last name, email, phone number to the messages table, but that's data that we already have in the customers table. However, if the sender of the message is a guest, we don't have those details stored in other place. Moreover, if the conversation is started by one of the departments (let's say one of the agents from Customer Service department), makes no sense to store first name, last name, email and phone number of the agent.

Other option will be to create a Guest table where I store guests. That table will contain id, first name, last name, email and phone number and then, on the message table instead of having customer_id, I can have a "composed" key: user_type (which can be customer, guest) and user_id (which can be the customer_id or the guest_id).

Also in order to allow messages being started by any company department, probably I will need to add two fields: sender and receiver. So, if John Doe from financial department needs to send a message to the customer Charles Darwin, the messages table will store John Doe as sender and Charles Darwin as receiver. On the other hand, if Charles Darwin wants to initiate a conversation with the financial department, we just need to store the sender (because the message is directed to the financial department, so any member of it can reply).

However, it seems like simple queries do the database will be big and a little bit messy (for example, a query to search for all messages containing "example" sent by Charles Darwin).

Does anyone have a suggestion on how to do this efficiently? Are my thoughts a good practice?

Thanks in advance


texts: SSIS job -- how to create stored procedure that repeats SSIS jobs?

texts:
Hey I realize I can probably create a modular parent package that repeats SSIS jobs in Visual Studio itself .... but I know you can execute SSIS packages using a stored procedure in SSMS -- 'exec ssisdb.catalog.create_execution @package_name=N'myPackageName' ..... etc

For a quick and dirty way to execute an SSIS package like this say ... 'X' number of times ...

Is there way to do this? Like ... on package end, repeat ... for X times?

Or even simpler, if the job duration is known/ predictable ... "execute task, wait 5 minutes .... execute again ... for X times?"


texts:
We take full backups of the DB every day (in full recovery mode) and keep them for 10 days. Because of our requirements, we don't want to take incremental or differential backups of the DBs.

Now we are observing that the ldf file size is growing too large. The question, is there any way to handle this scenario?

We don't want the ldf file to increase in size but at the same time we don't want to lose full backups.


texts: How group with min(date) and select an another column in the same table

texts:
I have a query who return something like it :

Name   Gender Job         date of hire
John   M      mechanic    2012-05-08
John   M      electrician 2010-01-01
Vicky  F      scientific  2012-11-11
Bob    M      NULL        NULL

I need to have the name, gender and jobname of first job of each person. But i don't know how.
My query look like this :

select name,gender,jobname,hiredate
from person p
left join job j on p.personid = j.personid

I'm on Microsoft SQL Server 2000

I need this as result :

Name   Gender Job
John   M      electrician
Vicky  F      scientific
Bob    M      NULL


texts: Effective backup and archive strategy for database and linked files

texts:
I am using Postgres to store a variety of application data for a webapp.  Part of the application involves storing and retrieving user uploaded files.  I am storing the files in the filesystem with some associated metadata in the database.

I am trying to come up with a backup and archive strategy so that I can effectively backup and archive/restore the database and the linked files.  Here are the things I want to accomplish.

Perform routine backups that can be used for recovery from failures and which include all DB data and the linked files.  Ideally, this backup would be done while the app is running.  Live backup is certainly possible with a DB but I am not sure how to keep the linked files consistent with the database during the backup process
Archive chunks of data as they become "old".  These chunks must includes the database data plus any linked files.  It should be possible to put the archived data back into production again.  It would be ideal if it were easy to determine which ranges of objects were stored in each chunk.

Do you have any advice for how to accomplish these goals?  If the files were in the database as BLOBS these tasks would be much easier since normal database backup and restore functionality would handle this.  I am not sure how to accomplish the same thing when file data is linked to database rows.

Note: I asked this question on ServerFault but I did not get any response there.  The link to that question is: https://serverfault.com/q/284218/85719


texts:
I created a JOB to run a simple package that runs a query and saves the results to an Excel file. If I try to run the job In SQL Management Studio I get an error saying:

  SQLServerAgent is not currently running so it cannot be notified of
  this action. (Microsoft SQL Server, Error: 22022).

I made sure that SQLServerAgent was in fact running and tried running the job again (from SQL Managment Studio) but still got the same message.

I found this knowledge base article which is telling me that I have to edit the Registry in order to resolve the problem.

Before I go in and start messing with the registry which is something I really don't want to do I was wondering if anyone else has experienced this problem and if they had to edit their registry to fix it?


texts: clone collection in mongoDB in the same db

texts:
I want ti copy my collection including is indexes without the data.
how can I do it in the most efficient way?
I am using a replica set  version 4.0.3


texts: Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?

texts:
In this example:

DECLARE @KeyField INT, @ValueField INT

DECLARE CursorName CURSOR FAST_FORWARD
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
    ON T1.ValueField = T2.ValueField
WHERE T2.KeyField IS NULL -- Filter out any records that have the same value in T2 as T1

OPEN CursorName FETCH NEXT FROM CursorName
INTO @KeyField, @ValueField

WHILE (@@FETCH_STATUS = 0)
BEGIN

    INSERT INTO Table2 (KeyField, ValueField)
    SELECT @KeyField, @ValueField

    FETCH NEXT FROM CursorName
    INTO @KeyField, @ValueField

END

CLOSE CursorName
DEALLOCATE CursorName;

(I forgot how much I "loved" cursors.)

Would the update to Table2 inside the cursor loop body, cause that value to be filtered out from the dataset on the next iteration of the loop?...in other words does updating Table2 modify the dataset that uses Table2 for the original cursor definition.


texts: Transactional replication in sql server 2017 but job not running

texts:
I have created transactional replication and here is my script

Distribution script:

use master
exec sp_adddistributor @distributor = N'servername', @password = N''
GO

exec sp_adddistributiondb @database = N'distribution',
@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data',
@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data',
@log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48,
 @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
    create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
    EXEC sp_updateextendedproperty N'SnapshotFolder', N'E:\snapshot\ReplData', 'user', dbo, 'table', 'UIProperties'
else
    EXEC sp_addextendedproperty N'SnapshotFolder', N'E:\snapshot\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'servername',
@distribution_db = N'distribution',
@security_mode = 1

Publication script

use [databasename]
exec sp_replicationdboption @dbname = N'databasename', @optname = N'publish', @value = N'true'
GO
use [databasename]
exec [databasename].sys.sp_addlogreader_agent @job_login = N'pcusername',
 @job_password = null, -- pc does not have any password
 @publisher_security_mode = 1,
 @job_name = null
GO
-- Adding the transactional publication
use [databasename]
exec sp_addpublication @publication = N'Basketball17Pub',
 @description = N'Transactional publication of database ''databasename'' from Publisher ''servername''.',
 @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true',
 @allow_anonymous = N'true', @enabled_for_internet = N'false',
 @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false',
 @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false',
 @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active',
 @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false',
 @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false',
 @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
 @enabled_for_het_sub = N'false'
GO

I used one machine for distributor and publication and no network path used for snapshot
but job is not running what i'm missing any suggestion


texts: How can I query which actions have been assigned to individual error numbers on SRS?

texts:
The DBA can modify the default rowcount mismatch behavior by assigning a different action to the corresponding error number in the replication server error class (see the assign action command for more details).
  (from this answer regarding replication errors)

The question arises: how can I (not a DBA) query out what actions have been assigned to individual error numbers?

If possible I would prefer a solution that works from ASE side without having to have access to replication server.


texts: Login failed for user Error: 18456, Severity: 14, State: 11

texts:
I have an AD group XYZ that I have added to SQL Server security with data_reader permissions.

The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I recently added a new user to this group (at AD level), but this person is not able to access SQL Server (through Mgmt Studio) and he's getting the error below

  Login failed for user. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

  Error: 18456, Severity: 14, State: 11.

I have already verified AD permissions are setup properly, user has restarted his machine, he is not part of any group that has DENY access and  the SQL Server XYZ group has been removed and readded to the SQL Server instance in Mgmt Studio and server has been restarted.

Any ideas on how to proceed further?

Thanks!


texts: Setting and Changing the Server Collation after installation

texts:
I have the following scenario: 2 database servers SQL Server 2008 R2 on Windows server 2012 R2, Cluster 2 nodes (with no-shared storage), with different Server Collation settings, planning to implement an AG after upgrade SQL Server to 2014 SP2.

Will that different Server Collation settings have an impact on implementing the AG? Or will it affect the SQL Failover operation if required to be done?

Note: this structure is still in the testing phase.


texts: SQL Server: Programatically trigger export of db schema

texts:
SQL Server 2014, Std edition

We have seen and used, over the years, a variety of sprocs and non-MS tools to export the DB schema.

The way we really like to do this is the built in utility, in SSMS, as described here on SO: https://stackoverflow.com/a/12036513/147637

We strongly prefer the MS tool, because it is more complete than hand-rolled tools, and provides super clean, properly organized and structured output.

However, we want to automate this.

How can the MS engine be driven programatically?


texts:
Consider a client which asks MySQL server for 100000 rows, but retrieves rows later one-by-one.

Does MySQL server generate the entire resultset before sending to client, or does it generate rows during sending to the client?

When most memory is used by the server: when generating the resultset or when sending it to the client?


texts:
Short version

Copy table took up more space than I expected or tested
Why would that be? Could it be the replication / binlog? If so, what then is written to the binlog during this query:

INSERT INTO  newtable SELECT *, NULL FROM oldtable;

Details

Goal:

I'm trying to add a column to a big table. To avoid issues with locking we're doing this instead of an alter table:

create new table like the first and add column
copy contents from first to second
rename both
delete old table.

This works fairly well for our purposes. It does take some time, but there's no locking.

Now this uses an extra amount of disk space the size of the table (as after step two we temporarily have 2 copies of the data, so this  feels logical), which is reclaimed after renaming and removing.

So far the theory and the test.

Problem

When performing this on a live environment it used more disk than expected. Not sure how much more, as I had to kill the query to stop the server from running out of space.

The only difference I can think of between test and production, (therefore the possible culprit), is replication.
Can a copy take up extra space, e.g. in the binlog, in a replicated environment?

It probably boils down to: What is written to the binlog during a query like this?

INSERT INTO  newtable SELECT *, NULL FROM oldtable;

Context

System: MariaDB 5.5.43
binlog format: Mixed
storage engine: innodb


texts: Implicit conversion does not affect performance

texts:
I have read about implicit conversion on indexes affect performance so by that mean in the following query

select count(*)
from fpc
where SKey in (201701, 201702)

as SKey is of type int, if I change above query to

select count(*)
from fpc
where SKey in ('201701', '201702')

performance will degrade.

I tested this on a table (which has millions of rows). question is why I did not see any difference on execution plan and time.

I have non-clustered columnstore index on SKey.

There are about 20 million rows per SKey and I have about 100 different SKey


texts: Choosing shard key with compound index field

texts:
I have a compound key with field articleId_1_host_1_timeStamp_1_isIndia_1_location_1.

Cardinality of fields is

articleId has 20000 different value
host has 17 value
timestamp is increasing field with time

I want to use articleId_1_host_1_timeStamp_1 as shard key.

Could you please suggest if I can use this as shard key with timestamp field in it? Also do I need to create a different index using these three fields or can I use the above compound key with shard key of of first three fields?


texts:
Firstly, database novice here.  I'm trying to create a system for processing Sales Orders into Purchase Orders and then split the Purchase Order into Shipping Containers. The systems needs to be able to split a Sales Order Line Item into 2 or more Purchase Orders and a Purchase Order Line Item into 2 or more Shipping Containers.

There will be some(a lot of) adjustments back and forth where once a Sales Order Line Item gets split and put into multiple Purchase Orders it might get split differently later on.  And same when a Purchase Order Line Item is processed into Shipping Containers.  I can't for the life of me how to best handle this operation.

If it helps, here are the tables I have so far with some details excluded for brevity:

CREATE TABLE PurchaseOrder (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    PurchaseOrderNumber VARCHAR(15) UNIQUE,

    PRIMARY KEY(Id)
);

CREATE TABLE Container (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    ContainerName VARCHAR(20) UNIQUE,
    PurchaseOrderId INTEGER UNSIGNED,

    PRIMARY KEY(Id),
    FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),
);

CREATE TABLE SalesOrder (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    ClientId INTEGER UNSIGNED,
    SalesOrderNumber VARCHAR(10),

    PRIMARY KEY(Id),
    FOREIGN KEY(ClientId) REFERENCES Client(Id)
);

CREATE TABLE SalesOrderLineItem (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    SalesOrderId INTEGER UNSIGNED,
    ProductId INTEGER UNSIGNED,
    Qty INTEGER,
    Price DECIMAL(5,2),
    Cost DECIMAL(5,2),

    PRIMARY KEY(Id),
    FOREIGN KEY(SalesOrderId) REFERENCES SalesOrder(Id),
    FOREIGN KEY(ProductId) REFERENCES Product(Id)
);

CREATE TABLE PurchaseOrderLineItem (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    PurchaseOrderId INTEGER UNSIGNED,
    SalesOrderId INTEGER UNSIGNED,
    ProductId INTEGER UNSIGNED,
    ClientId INTEGER UNSIGNED,
    MfgId INTEGER UNSIGNED,

    PRIMARY KEY(Id),
    FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),
    FOREIGN KEY(SalesOrderId) REFERENCES SalesOrder(Id),
    FOREIGN KEY(ProductId) REFERENCES SalesOrder(Id),
    FOREIGN KEY(ClientId) REFERENCES Client(Id),
    FOREIGN KEY(MfgId) REFERENCES Mfg(Id)
);

I'm thinking to create additional association tables between Sales Order Line Item and Purchase Orders and for Purchase Order Line Item and Containers to keep track of this kind of back and froth splitting?

Help appreciated!


texts: Selecting records based of other table records

texts:
In an SQLite DB, I have teh following tables

*reports*
id: integer
name: string

*sections*
id: integer
warning_table: string
warning_kind: string
report_id: integer (foreign key)

*old_warnings*
id:integer
kind: string
creation_date: date

*new_warnings*
id:integer
kind: string
creation_date: date

sections#warning_table can be either old_warnings or new_warnings.
sections#warning_kind, old_warnings#kind, and new_warnings#kins can be one of the following values: small, medium, or large.

Now, given a date, I need to get the reports records for which there is at least a record in either old_warnings or new_warnings created that date of the kind as indicated in the sections.

Here an example to clarify

REPORTS
|id|name    |
|1 | first  |
|2 | second |

SECTIONS
|id|warning_table|warning_kind| report_id|
|1 |old_warnings |small       | 1        |
|2 |old_warnings |medium      | 2        |
|3 |new_warning  |small       | 2        |

OLD_WARNINGS
|id|kind  | creation_date|
|1 |small | 2020-02-01   |
|2 |medium| 2020-02-02   |

NEW_WARNINGS
|id|kind  | creation_date|
|1 |small | 2020-02-02   |
|2 |medium| 2020-02-04 |

So when the date is 2020-02-01 I need to get only the first report since there are neither medium old warnings or small new warnings created that day. And when the date is 2020-02-02 I should get back both reports since there is an old warning medium and a new warning small created that day.

Any ideas on how I can get what I need?
I have tried with join but I was lost. So I tried with count + union but also in that case I didn't go too far.


texts:
I need to be able to track API usage in order to generate reports or charts later. I've been struggling to decide whether to use regular MySQL or something like MongoDB or Elastic Search which I don't have any experience with at all.

The records I will insert will be

user_id (int)
request_time (datetime)
ip_address(varchar)

I will need to count total records per user_id per given time
I will need to grab all ips from user_id per given time
I will be inserting around 400k records per day
I need to be sure this will be able to handle at least 1 million records per day for inserts

Inserts are sent to a queue server (Redis) that will do single inserts to whatever system we decide to use.

What do you think will be the best solution for this scenario? It's worth mentioning that we are trying to find the least expensive way to do this.

Update: A million records using bigint for Ips will take approx 45mb making a total of 1350mb monthly


texts: should we combine both inbound and outbound shipping into a single table (then create an additional column to identify the direction) or to split them into separate table? What's the pros and cons?


texts: Trigger function error: Record "old" not yet assigned

texts:
I'm new to trigger functions and trying to work this out. I have looked for solutions in the documentation and applied those, but still something is wrong here. My aim is to show error an message when inserting into this specific column.

CREATE OR REPLACE FUNCTION test_f()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    BEGIN
    IF (TG_OP = 'INSERT') THEN
    IF (OLD.col1 != NEW.col1) THEN
        RAISE EXCEPTION 'Editing not allowed';
        END IF;
        END IF;
        RETURN NEW;
        END;
    $$;

    CREATE TRIGGER test_t
    Before insert on schema.table
    For each row execute procedure test_f();

    Insert into schema.table(col1)
    VALUES
    ('test');

(I'm aware there are questions like mine already however those haven't helped in my case.)


texts: pg_upgrade and tablespaces (postgres 9.2 to 9.3)

texts:
I have a 9.2 installed with tablespaces sitting at:

/var/lib/postgresql/9.2/main

and

/opt/postgres/9.2/second

And unfortunately they are on different filesystems.

This is a Debian package installation of 9.2. The 9.3 will be compiled from source. Since every example I have seen does not show how to deal with a second and a third table space, does anyone have the correct command line for this?  Does pg_upgrade take multiple -d and -D options?

The intent is for the data to stay on the same file systems and to use link-mode.


texts: MySql - changing innodb_file_per_table for a live db

texts: I have a large MySql DB (150GB) and only now I've noticed that the innodb_file_per_table is set to off which causes the entire DB to be hosted on one single file (ibdata1). I want to activate innodb_file_per_table and have it retroactively split the DB into several files, what's the best way to do this?


texts:
I am looking to create a job which would check a status of another job every 5 minutes.

select
    CASE jh.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Success'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
    END AS Run_Status
from
    msdb.dbo.sysjobs j
inner join
    msdb.dbo.sysjobhistory jh on j.job_id = jh.job_id
where
    j.name = 'DW Job'
group by
    j.name, jh.run_status

How would I go about creating a step to check the status of a job every 5 minutes and if it completes sucessfully, go to the next step, other wise keep checking if the job is running and if the previous job fails, then report a job failure.


texts:
I am trying to run a vacuum full analyze on a postgres 8.4 machine where it appears that autovacuum has not been able to keep up. The database is very large and under heavy load.

The problem started with postgres denying updates because it is out of transactions. My only option was to take our business offline and do a manual vacuum full.

The issue is that it is taking a really long time and I have been looking for a dependable way to speed it up. I don't want to arbitrarily make changes to the configuration files while the database is in an unknown state.

Here is the postgres.conf configuration for the server.

listen_addresses = '*'
port = 5476
max_connections = 1000

### logging
log_destination = 'syslog'

constraint_exclusion = partition

### work_mem for vacuuming
maintenance_work_mem = 128MB        # min 1MB
                                    # DEFAULT 16MB

# -----------------------------
# CHANGE THESE DEPENDING ON SYSTEM
# VALUES BASED ON 2G RAM
# -----------------------------

### RAM * 1/4
shared_buffers = 512MB              # min 128kB or max_connections*16kB
                                  # DEFAULT: 24MB
#### very import. all work happens here, needs to be as big as possible

temp_buffers = 64MB
#### session-local buffers used to access temporary tables on a per-session basis

### not more than RAM / connections
work_mem = 20MB                   # min 64kB
                                  # DEFAULT: 1MB
#### used for sorting in memory

### raise this if log complains or to reduce checkpoints
wal_buffers = 3MB                 # min 32kB
                                  # DEFAULT: 64kB
checkpoint_segments = 16          # in logfile segments, min 1, 16MB each
                                  # DEFAULT: 3
#### stores changes to shared buffer. higher value = fewer checkpoints/flushes

### RAM * 2/3
effective_cache_size = 1365MB
                                  # DEFAULT: 128MB
#### size of largest possible object

### multiplied defaults by 0.2
cpu_tuple_cost = 0.002                  # (same)
cpu_index_tuple_cost = 0.001            # (same)
cpu_operator_cost = 0.0005              # (same)
#### do this. based on older slower hardware.

### milliseconds vacuum will delay when its cost reaches threshold
vacuum_cost_delay = 20            # default = 0

# -----------------------------
# OPTIONAL DEBUG/LOGGING PARAMS
# -----------------------------

### uncomment the following 6 lines to enable query logging
#silent_mode = on
#log_min_duration_statement = 0
#log_duration = on
#log_statement = 'none'

Any ideas on what I can do to get this server back up asap would be greatly appreciated.

Edit

Here is the exact error message we are receiving, sorry about that.

ERROR:  database is not accepting commands to avoid wraparound data loss in database "live"


texts: Best approach instead of granting permissions all the time to logins

texts:
So, I'm trying to fix logins/users permissions here ( everybody was using DBO for everything, weak passwords and etc ).

For each customer we have, I will create only one login. It will access test databases, homologation, and etc.

I will give the login these permissions:

USE [master]
GO
CREATE LOGIN [User_Customer]
WITH PASSWORD=N'Strong_Pass',
DEFAULT_DATABASE=[Customer_DB],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [Customer_DB]--BULK
GO

USE Customer_DB
GO
CREATE USER [User_Customer]     FOR LOGIN [User_Customer] --User
GO
ALTER ROLE [db_datareader] ADD MEMBER [User_Customer] --DR
GO
ALTER ROLE [db_datawriter] ADD MEMBER [User_Customer] --DW
GO
ALTER ROLE [db_ddladmin]   ADD MEMBER [User_Customer] --DDL
GO
GRANT EXECUTE TO [User_Customer] --EXECUTE
GO
USE [master]
GO
DENY VIEW ANY database to [User_Customer] --DENY

Is there a way to create a single role, where I can only crate the role, and assign the users mapped to this login, to the role?

I don't want to sometime forget a permission and mess everything.

I appreciate any tips about how to manage logins and users better than what I'm currently doing.

(The above script is for sql 2014. It's just for you guys to get the Idea. with sql server 2008, it's a different way ( sp_addrolemember and etc ).


texts: Migrating from windows-1252 to UTF-8 character set

texts:
I'm thinking of migrating my database (oracle) from windows-1252 to UTF-8 by doing the following:

export all data (backup)
truncate all tables
run alter database character set
import data back

Is there a way to detect if any characters will be lost or need manual adjustments before the migration?

If so, how?
If not, then how to prevent losing characters during the migration?


texts: Three update query vs single update query performance

texts:
Am trying to optimize a procedure. There are 3 different update queries present in the procedure.

update #ResultSet
set MajorSector = case
        when charindex('  ', Sector) > 2 then rtrim(ltrim(substring(Sector, 0, charindex('  ', Sector))))
            else ltrim(rtrim(sector))
        end

update #ResultSet
set MajorSector = substring(MajorSector, 5, len(MajorSector)-4)
where left(MajorSector,4) in ('(00)','(01)','(02)','(03)','(04)','(05)','(06)','(07)','(08)','(09)')

update #ResultSet
set MajorSector = substring(MajorSector, 4, len(MajorSector)-3)
where left(MajorSector,3) in ('(A)','(B)','(C)','(D)','(E)','(F)','(G)','(H)','(I)','(J)','(K)','(L)','(M)','(N)','(O)','(P)','(Q)','(R)','(S)','(T)','(U)','(V)','(W)','(X)','(Y)','(Z)')

To complete all three update queries it takes less than 10 seconds.

Execution plan for all three update queries.

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

What I planned is to change the three different update queries into one single update query, so that the I/O can be reduced.

;WITH ResultSet
     AS (SELECT CASE
                  WHEN LEFT(temp_MajorSector, 4) IN ( '(00)', '(01)', '(02)', '(03)', '(04)', '(05)', '(06)', '(07)', '(08)', '(09)' )
                      THEN Substring(temp_MajorSector, 5, Len(temp_MajorSector) - 4)
                  WHEN LEFT(temp_MajorSector, 3) IN ( '(A)', '(B)', '(C)', '(D)','(E)', '(F)', '(G)', '(H)','(I)', '(J)', '(K)', '(L)','(M)', '(N)', '(O)', '(P)','(Q)', '(R)', '(S)', '(T)','(U)', '(V)', '(W)', '(X)','(Y)', '(Z)' )
                      THEN Substring(temp_MajorSector, 4, Len(temp_MajorSector) - 3)
                  ELSE temp_MajorSector
                END AS temp_MajorSector,
                MajorSector
         FROM   (SELECT temp_MajorSector = CASE
                                             WHEN Charindex('  ', Sector) > 2 THEN Rtrim(Ltrim(Substring(Sector, 0, Charindex('  ', Sector))))
                                             ELSE Ltrim(Rtrim(sector))
                                           END,
                        MajorSector
                 FROM   #ResultSet)a)
UPDATE ResultSet
SET    MajorSector = temp_MajorSector

But this takes around 1 minute to complete. I checked the execution plan, it is identical as first update query.

Execution plan for above query:

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

Can somebody explain why it is slow?

Dummy data for testing:

If object_id('tempdb..#ResultSet') is not null
drop table #ResultSet

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT CONVERT(varchar(255), NEWID()) as Sector,cast('' as varchar(1000)) as MajorSector
into #ResultSet
FROM Tally
where  n <= 242906 -- my original table record count
ORDER BY n;

Note : Since this is not my original data the timings I have mentioned above could be little different. Still the single update query is much slower than the first three.

I tried executing the queries more than 10 times to make sure external factors should not affect the performance. All 10 times first three updates ran much faster than the last single update.


texts:
I suspect the following transaction is blocking a bunch of others in my mysql-5.6.19b.R1 database:

---TRANSACTION 10769842051, ACTIVE (PREPARED) 51 sec flushing log
3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 2
MySQL thread id 244298, OS thread handle 0x2ab552959700, query id 1736175859 ip-10-8-153-46.loco 10.8.153.46 creditcards init
COMMIT

Is it possible to identify the components of the commit?  I'd like to know individual queries/updates/deletes that are part of it.

Update:
I've obtained the server's binary log but to my suprise, despite having lots of COMMITs and other queries, it lacks the transaction or even query id so besides a vague timestamp, I don't have a way to match these up.  Does mysqlbinlog command have a flag to expose those?


texts:
I was wondering what the best name for a linked table in this situation (or any) would be. I've searched for this, but could not find any concrete examples.

For example, I have the following tables (where 'table in the name refers to tables in a restaurant):

restaurant_table
restaurant_table_feature

A table can have all kind of features and also multiple. Think of rounded tables, tables near the window, ground-floor tables, tables near entrance. It really depends on the type of restaurant, so it needs to be customizable. Arrangements are not permanent, since tables can be moved around for various reasons. Usually, the smallest possible combination of a table has a table number, if tables are combined for a group, multiple table numbers are used.

I want to link multiple features to a table row, with a linked table. Should I call the linking table restaurant_table_feature_table or restaurant_table_to_feature.

I've seen both possibilities in multiple systems, but which one would be the preferred one?

I also could simply rename restaurant_table_feature to restaurant_feature, and create a restaurant_table_feature table as link table. However if I also have a restaurant_location table for which I would have features (which differ from the table features) I would run into problems.

To be clear, my question is what the best name would be (for the many-to-many tables) in any situation with multiple many-to-many tables.


texts: PostgreSQL, how to query the database to retrieve the percentile elements of a dataset?

texts:
I need a PostgreSQL query that do the following job: given a percentage (e.g. 10%), the query should return each 1st element of each percentile, among all the elements selected.

So suppose in this simple case my table contains elements which are integer values between 1 and 100:

id    | value
--    | -----
id1   | 1
id2   | 2
...
id100 | 100

And suppose the input percentile is 10%.
The query should divide all the dataset into spans whose range is 10%, and return each first element.
So the output should be:

id1 | 1
id11 | 11
id21 | 21
id31 | 31
id41 | 41
id51 | 51
id61 | 61
id71 | 71
id81 | 81
id91 | 91

Does anyone knows how I could write this Sql query? Thanks!


texts: SQL Server sys.dm_tran_active_transactions long running transaction worktable

texts:
today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no clue with the sp_whoIsActive oputput alone.This issue is solved but now I see that there are quite old transactions in the table with type "worktable". Here's the output for the following query

SELECT  *,
 case transaction_type
      when 1 then 'Read/Write'
      when 2 then 'Read-Only'
      when 3 then 'System'
      when 4 then 'Distributed'
      else 'Unknown - ' + convert(varchar(20), transaction_type)
 end as tranType,
 case transaction_state
      when 0 then 'Uninitialized'
      when 1 then 'Not Yet Started'
      when 2 then 'Active'
      when 3 then 'Ended (Read-Only)'
      when 4 then 'Committing'
      when 5 then 'Prepared'
      when 6 then 'Committed'
      when 7 then 'Rolling Back'
      when 8 then 'Rolled Back'
      else 'Unknown - ' + convert(varchar(20), transaction_state)
 end as tranState,
 case dtc_state
      when 0 then NULL
      when 1 then 'Active'
      when 2 then 'Prepared'
      when 3 then 'Committed'
      when 4 then 'Aborted'
      when 5 then 'Recovered'
      else 'Unknown - ' + convert(varchar(20), dtc_state)
 end as dtcState
FROM    sys.dm_tran_active_transactions
ORDER BY transaction_begin_time

The transaction_begin_time happens to correlate with the time the SQL Server Service was last restarted.

Should I be concerned about this in any way? I did have a look at https://www.sqlservergeeks.com/sys-dm_tran_active_transactions/ to learn more about the dmv. However it just mentions that worktables are used for storing temporary query results in tempdb. However my understanding is that if I kick off a query whose execution plan decides to spool to tempdb and use a worktable a new worktable is created each time it is needed. Therefore it seems odd that therer are 6 worktables so old.  I could hypothesize that these are tables reused since service restart for monitoring queries which run constantly or internal SQL server stuff. Anyway the transaction type read only seems to be harmless and I noticed no sign of blocking or version store growth.

Thanks in advance for your assistance

Martin


texts: SQL Server 2005 Tools makes SQL Server 2008 Express install fail

texts: When I tried installing SQL Server 2008 Express, the install failed due to this line.  How can I circumvent this, or fix the issue?


texts:
I have Question on log shipping:

I am migrating a VLDB (12TB) SQL database from a physical environment (server A) to virtual environment (server B), and from version SQL 2008 R2 to SQL 2014.

The high-level plan for migration is to log ship from server A to server B during a transition\test period. Once we’re happy with testing we simply make server B the new production environment. During this period server B needs to receive periodic log restores (once or twice per day) and be in read-only\standby mode for testing.

I have the added complexity of A and B existing in related but different domains, and I also want to use the existing .trn files that are currently being created via the existing Ola Hallengren regime. So basically, I just want to utilize the COPY & RESTORE aspects of log shipping on server B by picking up trn’s from a network share.

Before I attempt this with the VLDB I am testing the concept on a much smaller DB.

The issues I’m getting are :

When I try to get server B into standby mode I get this error:

  This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY

Stack overflow Questions 3010115 and 40760521 are similar but unfortunately provide no solution.

Any help or other suggestions would be much appreciated.


texts: Deleting records from tables containing FK pointing to each other

texts:
Background: Bit of an accidental DBA here. I have this reddit-like website where users submit links pointing to various internet content, and can then leave comments under each posting. This app - let's call it Links - has two corresponding tables to store data: link and publicreply (i.e. comments assoc. to each link).

Problem: I can't seem to delete records (for maintenance) from these two tables because of interdependent FK constraints. Need guidance to resolve the situation.

Details: Each Publicreply object stores a FK to the Link object it's associated to. Moreover, each Link object also saves a reference to the latest publicreply associated to it. This creates a situation whereby all Publicreply objects have a Link FK, and vice versa. As in:

Table "public.links_link"
        Column        |           Type           |                        Modifiers
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 url                  | character varying(250)   | not null
 image_file           | character varying(100)   |
 reply_count          | integer                  | default 0
 latest_reply_id      | integer                  |
 is_visible           | boolean                  | default true
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED

 Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

How do I delete records from the table Link under this scenario? And from Publicreply? Im using postgresql 9.3.10.


texts: Not able to regulate/auto grow tempdb size

texts:
We have a SQL Server 2017 (RTM-CU15) AG group (3 servers, 2 HA synchronous) that has had performance issues for the past two months. During the resolution of an issue yesterday we found that the instance was struggling with the tempdb, as per the logs:

  A time-out occurred while waiting for buffer latch type 2, bp 000001C9A41D4A40, page 4:40616, stat 0x810b, database id: 2, allocation unit Id... Not continuing  to wait

There were no mention of storage issues in Event Viewer system logs.

Upon inspection, we found that tempdb had 8 data files each of 250MB (2GB in total), even though they were set to autogrow unlimited. This is small in my opinion.

Based on the size of the other DBs on the instance (about 1.5TB) and the time it's taken for some of my own BI queries (30 minutes) I would have expected the tempdb to be way over 2GB.

No one was monitoring the page life expectancy, but I suspect the instance was not able to manage/auto grow its tempdb files. Once we increased the tempdb size, we no longer had performance issues.

I was surprised to find both HA nodes had the same tempdb sizes of 2GB (both have run the production services for some time).

I haven't found this issue online. Is this an issue with this CU or with 2017 in general? What could have prevented the tempdb from auto growing? Should we have looked elsewhere for the performance issue?

Instant file initialization is not enabled:

The AG logs don't have anything useful. The AG did break with the tempdb error that occurred though.

The tempdb database had two months to grow, but it didn't. It stayed at the initial size of 2GB. It should have grown by itself based on the autogrow settings on each file, which were 100MB, unlimited?


texts: For each row, find the next closest value in another table (Teradata)

texts:
I've seen a few close examples here but I'm not quite getting something close enough for me to noodle out a fix.

I have events, where trucks come in and out of a gate and I have two tables.  The first is all ingate events the second is all outgate events.  The problem is conceptually easy: For each ingate, find the nearest outgate.  The dilemma I have is that obviously trucks can come back.  So I might have 300 ingates for a unit on the ingate table with a corresponding 300 outgates.

Both tables look like this, except the other is session.Outgate

session.Ingate:

CHAS_INIT|  CHAS_NR   |     Ingate_TIME
---------+------------+---------------------
  MAEC   |   66862    | 2018-01-29-01.34.00
  MAEC   |   67218    | 2018-03-24-01.29.00
  MAEC   |   67557    | 2018-03-30-01.26.00
  MAEC   |   67557    | 2018-04-06-01.49.00
  MAEC   |   68773    | 2018-02-22-01.47.00

While this is a breeze to do in a spreadsheeting tool, I'm stumped on if it can be done during a join.  Any helps is greatly appreciated.


texts: Is it possible to recover day's worth of queries from sql server log file?

texts:
I lost all data entered and edited in my system yesterday, and my ISP has announced to me that last night's backup is corrupt and cannot be recovered.

Is there any way to access the database's log file to try and recover the database to the point immediately before the day's data was deleted?  I'm using SQL Server 2012 Standard Edition.

I've tried this (which I found in another question on this site):

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

but I'm only getting queries since this morning.

I'd be immensely grateful for any help.


texts: Postgres pgpool increase num_init_children

texts:
I am trying to tune postgre to allow many concurrent queries from clients as i am checking how many parallel connections with there queries it can handle. I read A good PgPool II configurataion
  , but what really can help to handle many concurrent connections with queries is to set up (num_init_children = 1000 , for example 1000 parallel queries )

If i set up num_init_children = 2000 server fails to start.
My pgpool config is:

num_init_children = 1000
max_pool = 4
child_life_time = 120
child_max_connections = 100
connection_life_time = 120
client_idle_limit = 10

How can i increase num_init_children or tune some parameters to allow 2, 5 or 10k parallel queries at the same time, cause it's really frustrating that postgre stucks with more that 1000 queries?


texts:
I had the following quiz question and could not answer it myself because of a little confusion.

Please answer this and tell me why?

  A database trigger is an alternative for?

  A. Stored procedure
  B. Primary key for implementing referential integrity
  C. Foreign key for implementing referential integrity
  D. All of the above


texts: Migrating development and production databases to AWS

texts:
After careful thinking and asking on meta.stackoverflow, I'm posting this question on the DBA site. If anyone thinks this question should be on serverfault or any other site, feel free to close and migrate it.

We have a couple of self managed servers, production and development, with some not too big databases on a MS SQL Server 2008R2 express instance. Also we have some other databases hosted on the Amazon RDS service and a couple of web sites too. I've been asked to investigate the option of migrating all the production & development databases to Amazon AWS. Also check the viability of using EC2. Sorry if I'm miss using some terms, I'm just learning about all these services.

Where should I start doing some tests, maybe creating a dummy database to test the different configurations, compatibility issues, latencies, etc? Can I have some recommendation on tutorial/blog/faq's where to find some usefull information? I already opened an account on the "free tier" that Amazon offers for testing purposes and I'm reading everything I can find on the subject, other than the AWS Documentation site.

Any recommendation will be welcomed as well as any previous experience doing this kind of migration to the AWS. I'm willing to add to my question any information that could help to clarify it.


texts: Select all overlapping ranges from starting range

texts:
My question is similar to this one with (I think) significant enough difference. I have a base range and I want to find all other ranges in a table that conflict with it and each other. or rather the items that form the ranges, but it doesn't really make a difference.

The line with a asterisk is the starting range. The ranges 1,2 and 3 are the ones that are supposed to expand it. The result range should be the X.

1 |
3 |    ===1====            ====
5 | ==2==    ====*====           ====
6 |             ====3====     =====
--+-------------------------------------
  | |<--------X-------->|

I have written this:

WITH   cte
AS (
    SELECT DATA1.ID, DATA1.STARTDATE, DATA1.ENDDATE
    FROM DATA1
    WHERE
    DATA1.ID = @PARID AND
    DATA1.STARTDATE > @STARTDATE AND
    DATA1.ENDDATE < @ENDDATE

    UNION ALL

    SELECT DATA1.ID, DATA1.STARTDATE, DATA1.ENDDATE
    FROM cte
    INNER JOIN DATA1 ON (DATA1.ID = cte.ID)
    WHERE
    DATA1.ID = @PARID AND
    (cte.STARTDATE < DATA1.ENDDATE AND cte.ENDDATE > DATA1.ENDDATE)
)
SELECT *
FROM cte

But after some fiddling realized this can't really work, since the 2nd SELECT doesn't know what STARTDATE and ENDDATE it should use from the cte. And I can't use subqueries in recursive SQL.

We have previously calculated this in client in .NET via set of recursive functions, but at O(N^2) it was stupidly slow. The intent here is to move the calculations to the server as well as optimize the calculation.

Is what I'm trying to do here at all viable?

SQL Fiddle. I'm having some trouble getting the query to run as it did on my machine (where I used less simple data structure), but at least I added example data. I will keep trying to get it to produce the same result I got on my machine.

With input range from 2018-08-24 12:00:00 to 2018-08-31 12:00:00, the correct output should be IDs 2, 3, 4, 6.


texts: Altering column from INT to BIG INT and fill factor trick

texts:
I have inherited a script that rebuilds the fill factor sitting at 90 fill factor on the INT primary key. It rebuilds it at a fill factor of 50. Then the script drops the index, alters the column to a BIGINT and rebuilds the fill factor back to its original 90.

Does that make sense to anyone?  Are they doing this to grow space?  Seems like as soon as they drop the clustered index, the space is a moot point. Thoughts on this?


texts: MySql Database solution for specific web app problem

texts:
Well, I need to create a comment system for posts that people add to my web app. This web app is just like facebook or myspace posts. Users add posts and people can comment on them. But the problem is I don't know how to store each comment in mysql table. How can I map each comment to the specific author.

I am new to mysql and how can I learn to solve things like that. Is there any books on the subject. I know some basic sql.

Thank you!


texts: Compare order of columns between two databases

texts:
I  have two postgres databases which should have an equal db-schema, but have not.

I want to see the difference in the column order.

Columns of table1 in Dev: id, foo, bar

Columns of table1 in Prod: id, bar, foo

Strategy 1: list of columns

This is my current strategy to solve this.

Dump all columns of the database like this:

table1.id
table1.foo
table1.bar
table2.id
table2....

If I have a simple ascii list of both sides, I can use a diff tool to compare both db schemas.

I found ways to output all columns like above but the solutions don't support ordering by the schema of the table.

Question

How to show the difference of the column order in two databases?

Other strategies are welcome.

Implementation: I use postgres 9.3


texts: Find how many SQL Server instances are installed

texts:
On a SQL Server 2012 instance, how can I get the names of SQL Server instances using a SQL query?

I want to show instance names currently installed in SQL Server.


texts: Query to select specific columns based on current date

texts:
I have a table named 'mrtg' that has four columns:

mysql> select * from mrtg;
+----------+------+------+------+
| city     | day1 | day2 | day3 |
+----------+------+------+------+
| Delhi    |    2 |    6 |    9 |
| Mumbai   |    1 |    3 |    8 |
| Banglore |    4 |    1 |    6 |
+----------+------+------+------+
3 rows in set (0.00 sec)

If current date = '2019-09-01' then I want to select as

SELECT CITY, DAY1 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2, DAY3 FROM MRTG;

and so on.

How can I write a query for this?

The values represent day-wise data for a whole month. I do wonder about this type of table structure, but it's the client requirement. I need to find a solution to fetch only the columns from day 1 to the current date.


texts: How to filter for consecuting timeline events in pure SQL?

texts:
I run a LPR system on a parking lot, and I record every plate along with the camera that recorded it.

I have 2 cameras, camera 1 is on the entry and camera 2 on the exit. So for every reading I record the following on a table

Id|Camera|Plate|Timestamp

I need to run a query which returns every car who has not exited the parking lot. So the condition would be: I need to return every record which has an entry (camera = 1) not followed by an exit (camera = 2).

Which would be the best approach? I don't want to use cursors if possible.

Should I create 2 temp tables:

SELECT plate, MAX(Timestamp)
FROM records WHERE camera = 1 GROUP BY plate, MAX(timestamp)

one for each camera, then

SELECT temp1_entries except temp2_exits

and the result of the above query would be the cars who hasn't exited along with their entry timestamp.

DBMS is MariaDB, version 10.3.7.

Is this approach correct? Is there a better approach?


texts: Unknown database corruption: Data error in cyclic redundancy check

texts:
I have a server that is throwing an error message when I try to backup a database. The error is:

10 percent processed.
20 percent processed.
Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file "R:\data\MyDB.mdf:"
         23(Data error (cyclic redundancy check).).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I thought it was a database corruption, so I executed:

DBCC CHECKDB([MyDB]) WITH NO_INFOMSGS;

and no errors were found.

What could be happening?
What are your suggestions regarding this datafile?


texts: How to get desired type of records for desired number of times in oracle

texts: I want to get get 6 "double_scoring" records and 4 "singe_scoring" records from my Profiles table based on SCORING_RULE column values. Can you please let me know how can I do this with SELECT query where I am just selecting 10 records with ROWNUM <= 10 limit.


texts:
Is it possible to setup always on QL Server 2016/2017 without windows clustering and domain?

My intention of implementing always-on and secondary replica is offloading the select query from the primary database server. I'm not expert at windows clustering, so I'm looking for an easy solution to implement always-on as I don't need automatic failover.


texts: percona xtrabackup to backup mysql database

texts:
I'm trying to use percona xtrabackup to backup a mysql database.  in the restoring the database according to the documentation:

rsync -avrP /data/backup/ /var/lib/mysql/

this will copy the ibdata1 as well.
what if I have want to restore the backup into an existing mysql instance with some some existing databases? would this corrupt my other databases? clearly this will overwrite existing ibdata1.


texts: Which constraints can the PostgreSQL planner make use of?

texts: I'm interested in how the PostgreSQL 12 planner use constraints. I assume that it can use NOT NULL and UNIQUE, but what about foreign keys? Or CHECK?


texts:
Let's say I have this query :

SELECT X FROM Y

If I run it for the first time then it would do a hard parse.

After that, I run this query :

select x from y

With just a difference in alphabetical upper and lower case, does it mean they have to do the hard parse again?

Thank You.


texts:
I need help with the design of my schema. I'm almost new at this and I'm not sure whether my relationships between the tables ROOM, GUEST and RESERVATION below are correct.

Also, I have problems figuring out what to do regarding the possibility of ROOM CHANGE: An example:

Michael Keaton has one reservation in which he took two rooms. The first time, he didn't like the room so the hotel moved him to another room, and the second time there was a plumbing problem in the same room so they moved him again. My database needs to keep record about every room change.

Here's the entity relationship diagram:


texts: Should I put common fields in each specific tables or one common table then join when data selecting?

texts:
Assuming there are a few categories: blog, video, gallery. Each of them contains post data like id, title, content, author_id, created_at, but different category have few more specific field: video will have links, gallery will have imgURL.

Besides, user can leave comment. Comment have identical fields with Content too: id, title, content, author_id, created_at, and original_post_id.

My question is: blog, video, gallery, comment have common field, should I create a table called CommonContent: id, title, content, author_id, created_at?

CommonContent and Blog/ Video/ Comment are in 1:1 relationship. So should I still create a CommonContent table? My concern is if all common fields put in one table, it creates bottleneck when selecting data.


texts: Ola scripts = log shipping and transaction logs

texts: We use the Ola Hallengren scripts for full/diff/tran log backups.  Ola's site indicates that log shipping is compatible unless we are using transaction logs.  Why is that?


texts: Installing extension as non superuser on Amazon AWS RDS

texts:
I would like to allow non super users to install a specific postgres extension. Accordingly to the documentation at https://www.postgresql.org/docs/current/extend-extensions.html that is possible if I set superuser to false  in the extension .control file.

However, in AWS RDS that is not possible, because I don't have access to the filesystem. Would there be a way to set those .control parameters via SQL query? Or did I reach the usability limit that RDS offers me?


texts: What is the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives

texts:
A Microsoft Technet article recommends creating a secondary file group as the default file group (see reference below). The secondary file group should have a number of files, say four, that are each placed on separate disks. As an added rule-of-thumb from a colleague, the number of files should be equal to the number of CPU cores.

My understanding is that this setup is ideal for mechanical spinning disk drives where there is a performance boost by streaming data from multiple heads because a spinning disk drive is much slower than a solid-state drive. Is this understanding correct?

If yes, then my question is whether the cost-based optimizer takes into account the newer solid state drives? It seems like the performance bottlenecks from the spinning hard drives goes away when switching to the new solid-state drive. Our IT Operations groups has informed me that while I am currently allocated one virtual drive, the data is really stored on an ISCSI SAN, which has multiple solid-state drives.

This question is geared for trying to answer what is the most optimal setup for a large database of this magnitude:

Should I have a default secondary file group with just one file?
Should I have a default secondary file group with a number of files that equals the number of cores on the CPU?
Is there a performance boost by partitioning a database table when using solid state drives?

The current project I am working on requires a scaled database that will be a few terabytes in size that stores massive amounts of log data. A one week sample has approximately 150 million records and we need to store a rolling 3 years of logs. So, I am now looking at long running queries to find data. I have tuned the indexes to the point where nearly all of the work is attributed to non-clustered index seeks; the optimizer does not recommend adding a missing index.

Note

The licensing on Microsoft SQL Server is currently by CPU core. So, there is sensitivity to throwing more cores at the problem, especially if that will not boost performance.

Additionally, I am currently developing on SQL Server 2014, but will be migrating to SQL Server 2017 for both development and production.

Update 1

The project will be loading logs nightly where I expect few (perhaps none) updates or deletes because the logs are not expected to change at all - so they will not be re-loaded. Everything else will be reads for analytic purposes.

The PRIMARY file group for the system tables where the SECONDARY default file group is for everything else. The reason for doing this is explained by the link that is referenced at the bottom of this question.

Separate file groups will be created for the table partition. There are other tables within the database that are small enough where they will reside within the SECONDARY file group - I am only partitioning two tables where one exceeds 100 million records (partitioned by IDENTITY row number) and the other will go into the billions of records (partitioned by time [monthly]).

I plan on partitioning by month over 3 years. So, there will be 36 partitions. I will create file groups for each year and then place 12 files into the corresponding yearly file groups. The partition strategy is to reduce read times as there will be a lot of data scanning for analytic purposes. The annual file group strategy is strictly for ease of maintenance for the DBAs where they can remove a year's worth of data by removing a single file group.

Reference:

SQL Server Best Practices Setting a Default File Group


texts: Backup File Size Almost 50% of Database File Size with TDE Enabled

texts:
I receive a full backup of a very large database once per week from a vendor. It contains transactional data which we then load into our data warehouse.

Fully restored, the database is around 707GB. It is comprised of 3 files, 1 data file which is about 385GB, a second data file which is about 132GB, and 1 log file which is about 190GB.

This database has TDE (transparent data encryption) enabled. Everything that I've read says that with TDE, backup compression should be minimal, if any. However, the full backup file which I receive is 386GB in size. This is about 54% of the total file size before the backup.

I have been told that the vendor is performing native SQL backups and the file is not decrypted before they back it up.

How are the backup files so much smaller than the database files? I'm really hoping someone can help shed some light on this for me.


texts: Slow Query With Multiple Left Outer Joins

texts:
I am using the following query which has multiple left joins. it takes around 3.2s for execution. I have added indexes on every column which is taking part in JOIN. is it something regarding DB performance?
What all columns I need to consider for index. is a composite index needed in this case?

SELECT
            COUNT(*) FROM
        (SELECT DISTINCT P.PROFILE_ID
            FROM
            user.PROFILE P
            LEFT OUTER JOIN user.PROFILE_ACCOUNT P_AC ON P_AC.PROFILE_ID = P.PROFILE_ID
            LEFT OUTER JOIN user.ACCOUNT AC ON AC.ACCOUNT_ID = P_AC.ACCOUNT_ID
            LEFT OUTER JOIN user.PROFILE_ADDRESS SA ON SA.ADDRESS_ID = P.SHIPPING_ADDRESS_ID
            LEFT OUTER JOIN user.PROFILE_ADDRESS BA ON BA.ADDRESS_ID = P.BILLING_ADDRESS_ID
            LEFT OUTER JOIN user.PROFILE_ADDRESS AC_BA ON AC_BA.ADDRESS_ID = AC.BILLING_ADDRESS_ID
            LEFT OUTER JOIN user.PROFILE_ADDRESS AC_SA ON AC_SA.ADDRESS_ID = AC.SHIPPING_ADDRESS_ID
            LEFT OUTER JOIN user.PROFILE_CUSTOM_PROPS PCP ON PCP.PROFILE_ID = P.PROFILE_ID

         WHERE P.ORGANIZATION_ID = 1
            AND P.ISANONYMOUS = false
     )  AS T_PROF_ID;


texts: Sometimes getting quotes in Oracle spool sometimes not

texts:
Using Oracle SQL Developer, I do:

spool c:\temp\file1.csv
select /*csv*/ id, version, key, pattern, name from mydatabase.edition;

spool c:\temp\file2.csv
select /* csv */ id, version, amount, item FROM mydatabase.mtci;

In the first statment the export is like this

"string1", 98, "key1", "pattern", "name"

In the next statement it is like thhis

string2,       104,         988.99,         yuikk

i.e. it has no quotes and has tabs after the commas.

Anyone know why this is the case?

Note: I am using Oracle 11.


texts: Moving Local PostgreSQL 8.4.20 Database to Amazon EC2 or RDS

texts:
I'm running PostgresSQL 8.4.20 on a local server and plan to migrate the data to either Amazon EC2 or RDS. I have working knowledge of databases and need advice from experts. I've been researching this topic and I've come across some helpful and similar answers like this and this already.

I'm leaning towards Amazon RDS because of automatic backups and less maintenance. But will I be able to install v8.4 on RDS? Or do I need to upgrade to v9.3+? Will moving data from v8.4 to v9.3 cause any issues? If so, maybe it is best to install v8.4 on EC2?


texts: Grant Privileges on Production Database Throws "Error: tuple concurrently updated"

texts:
I would like to grant read access to all tables in my schema

GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX;

While it works on our alpha environment, it fails with our production database:

  [XX000] ERROR: tuple concurrently updated

There are some examples how to solve regular queries, but when it comes to GRANT commands it is getting more difficult. According to this so question I understood that another connection is granting/revoking permissions at the same time, something I hardly believe.

Is there a way to identify these other connections? Or in general to circumvent concurrent access to pg_class (i guess)?

Thanks!


texts: How to do automatic failover for PostgreSQL with application automatic switch?

texts:
There are two ways to try.

Method 1

Use JDBC connect the PostgreSQL cluster (primary, standby 2 servers only):

jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/jiradb

But failed:

  The configuration of your dbconfig.xml file is incorrect (user, password, or database URL etc.)

How to connect multiple servers from jdbc?

Method 2

Use pgbouncer and repmgr

Three servers:

pgbouncer
PostgreSQL Primary (192.168.0.1) Installed repmgr, running repmgrd
PostgreSQL Standby (192.168.0.2) Installed repmgr, running repmgrd

Set config in pgbouncer server(pgbouncer.ini):

[databases]
postgres = host=192.168.0.1 port=5432 dbname=postgres
postgres = host=192.168.0.2 port=5432 dbname=postgres

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres
unix_socket_dir = /tmp

When primary down, repmgr can switch to standby server as primary. But pgbouncer didn't automatic connect the second db server. Why? The config setting for databases does not work. Is it the wrong usage? Or pgbouncer doesn't has this automatic switch feature?


texts: Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?

texts:
In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT,

CREATE TABLE foo ( a int );
INSERT INTO foo VALUES (1),(2),(3);

Followed by,

BEGIN;
  ALTER TABLE foo ADD COLUMN b varchar;
  UPDATE foo SET b = CAST(a AS varchar);
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

However, that same thing in Microsoft's SQL Server seems to generate an error. Compare this working db fiddle, where the ADD (column) command is outside of the transaction,

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
COMMIT;

-- txn2
BEGIN TRANSACTION;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

to this db fiddle which doesn't work,

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

But instead errors

Msg 207 Level 16 State 1 Line 2
Invalid column name 'b'.

Is there anyway to make this transaction visible, with regard to DDL, behave like PostgreSQL?


texts: Mysql Server always throwing unknown error

texts:
Sorry for the bad formatting since its the first time

When i try to delete a row which has a foreign constraint, i'm expecting the Mysql server to give me the constraint key and the table to which it's related to but all i get is only this message "Unknown error 1451".

mysql server version : 5.7.26 (linux)

CREATE TABLE `detailmenu` (
  `detailmenu_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `menu_id` int(10) unsigned NOT NULL DEFAULT '0',
  `detailsubprg_id` varchar(20) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `link` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`detailsubprg_int_id`),
  KEY `FK_detailsubprgmtb_1` (`subprg_int_id`),
  CONSTRAINT `FK_detailmenu_1` FOREIGN KEY (`menu_id`) REFERENCES `menu` (`menu_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=latin1

When i try to delete a row from this table which has a constraint i expect the Mysql server to atleast give me the constraint key which is 'FK_detailmenu_1' or the child table reference but all i get is "Unknown error 1451" without any additional information. Is there any configuration issue? This issue does not come in windows machine.


texts: Postgres database insert become slow after 10 days, and then it needs a full vacuum

texts:
We have a postgres database which is almost 15GB's in size after vacuum. With a dedicated hardware i,e 32GB of ram with 12 Cores. The data automatically gets inserted around 300k inserts per day, and we also process the data. The data is deleted after 3 months, in order to keep the db size down.
NORMAL INSERT SPEED USED TO BE: 1200 INSERTS / MINUTE
One of the important table has quite a large trigger on it (We cant do anything about it unfortunately), the DB can insert roughly around 1200 records per minute, which is quite acceptable for us. These records are not inserted in bulk, but rather individually after processing.
AFTER THE PROBLEM STARTED: 200 INSERTS / MINUTE
About 10 days ago, our database suddenly got extremely slow i.e 200 inserts per minute. So after a few sleepless nights we vacuumed top 10 largest tables, but only increased the performance to 300 inserts per minute.
AFTER VACUUM 10 LARGE TABLE: 300 INSERTS / MINUTE
So we vacuum analyzed the whole database, and also REINDEXED the 10 largest tables. This worked extremely well and we were quite satisfied with the results
AFTER FULL VACUUM ANALYSE AND REINDEX: 1400 INSERTS / MINUTE
But just after 10 days we began to have the same problem again, now the inserts are back to 200 inserts/min. We didn't change any configurations of postgres.
AFTER 10 DAYS, BACK TO: 200 INSERTS / MINUTE
Can you please help me identify the problem? i know this is not much information to work with, but if u guys did have this problem before you might instantly identify it. Can this be problem with Disk or too many deadlocks?
Hard disk might not be a problem, as we did gain good speeds after the vacuum, but please let me know if you think that.
Thank you for helping me out.


texts: I have one table for users, one for a collection of games. My users can own one or many games. How do I structure that in my database?

texts:
Since I can't store lists, should I add a column to my table called owned games and then just keep a long string like "game1, game2..."?

Or should I add another table called something like ownership and every row represents the fact that a user is linked to a game...

  ownership

  - gameID
  - owner
  - dateAdded


texts: Does subqueries "short-circuit" based on other conditions?

texts:
I have a slightly costly case when exists (...) then 1 else 0 end sub-query in the select clause. The sub-query is only really needed if another column in the main query has a specific value (can think of it as a has_child flag).

As the main query returns tens of thousands of rows and runs frequently, it's a prime target for optimisation.

I have tried adding has_child = 1 into the case when clause, but that did not change the explain plan at all (understandably). But real query test feels quicker. Unfortunately, I cannot gather statistics on the production database, so I have no idea if the improvement is real or not.

So is Oracle actually able to skip/stop the join/sub-query if the condition guarding it is already false?

The explain plan looks like:


texts: error no 55, while creating a linked server

texts:
m stuck in creating a link server.. please help if you know the solution. find the screenshot below.
TIA


texts: Performance Issue after Truncate and Load of a table in Oracle

texts:
One of our clients has migrated from old to new (larger size/resources) servers. A process (X) is taking 4 hours extra whenever we are doing a truncate and full load of a specific table. If we do not truncate-load this table X doesn't take long to run. In process X we are inserting, updating and reading data from this table into numerous temp tables and then using these temp tables further.

I would like to know:

Best practices whenever a truncate and full load is performed.
What steps can we take from our side on this specific table?
Does the database migration to another server bring such issues?
What debugging should I do? I am gathering stats with cascade after the full load on this table, then running the process X.
Is it recommended to update any Oracle parameters on moving from Oracle 12c from Oracle 11g or 10?

Sorry for being very generic. The exact usage of the table in process X is complex and difficult to mention here. Please advise.

Other details:
Table Structure is as follows: - Around 55 columns -PCT_FREE    10 PCT_USED INI_TRANS   1 MAX_TRANS 255 INITIAL_EXTENT  65536 NEXT_EXTENT   1048576 MIN_EXTENTS 1 MAX_EXTENTS   2147483645 PCT_INCREASE FREELISTS   FREELIST_GROUPS LOGGING YES BACKED_UP   N NUM_ROWS 4064125

Typical weekly full load inserts around 2-3Million records

I am doing gather stats to the table and other related tables as well, with following options: dbms_stats.gather_table_stats(ownname=>'schema_x', tabname => 'Table', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', cascade=>TRUE, DEGREE=>7)
Table has two Normal Unique index and one Normal Non-Unique index defined


texts:
I have the following code:

$sql=
    'select *,'.
    'case when fisicas_cpf is not null then null else razao_social end as razao_social,'.
    'case when fisicas_cpf is not null then null else nome_fant end as nome_fant '.
    'from pessoas p join fisicas f join juridicas j '.
    'on p.fisicas_cpf=f.cpf or p.juridicas_cnpj=j.cnpj '.
    'group by p.id'
;

I want to unify the case statements because they verify the same thing.

I want something like this:

'case when fisicas_cpf is not null then null else razao_social,nome_fant end as razao_social,nome_fant '.

Here, @AaronB says that the case just return one value. This applicate to my situation, or there is a way of doing it?


texts: Please review the My.INI settings of my PC. File Attached

texts:
Below is the my.ini file of one of my PC running MySQL 5.5 with 1 GB RAM. It is not using concurrent connections and we are using a stand-alone PC for a single application and single user.

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------

# CLIENT SECTION

[client]

port=3306

[mysql]

default-character-set=latin1

# SERVER SECTION
[mysqld]
log-bin=E:/CRITICAL_MYSQL_LOGGING/AUTOMATED_BINARY_LOGS/MySqlBinLog

# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow.
max_connections=02

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again.
query_cache_size=15M

# The number of open tables for all threads.
table_cache=256

# Maximum size for internal (in-memory) temporary tables.
tmp_table_size=9M

# How many threads we should keep in a cache for reuse. When a client
# disconnects
thread_cache_size=8

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=20M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=500M

innodb_log_file_size=10M

innodb_thread_concurrency=8

log-queries-not-using-indexes

log-warnings

log=E:/CRITICAL_MYSQL_LOGGING/QUERY_LOGS/QueryLog

log-error=E:/CRITICAL_MYSQL_LOGGING/ERROR_LOGS/ErrorLog

log-slow-queries=E:/CRITICAL_MYSQL_LOGGING/SLOW_QUERY_LOGS/SlowQueryLog

log-bin-index=E:/CRITICAL_MYSQL_LOGGING/BinLogIndexFile

long_query_time=2

I have updated the original my.ini file. Please suggest better values for a 1 GB single user PC.

I also want to know how to flush logs and when.


texts:
What standard should I follow when naming tables and views? For instance, is it a good idea to put something like tbl_ at the beginning of table names? Should I designate code/lookup tables in some way like ct_, lut_, or codes_? Are there any other do's/don'ts?

I'm using MS SQL Server and have many databases with many tables so it would be nice to have something we can use as a standard with some supporting rational.


texts:
If relevant, I'm using IBM i V7R1M0.

I have some code in C#:

var generateSqlSql = $@"
CALL QSYS2.GENERATE_SQL(
    '{obj.Name}',
    '{obj.Library}',
    'VIEW',
    CREATE_OR_REPLACE_OPTION => '1',
    HEADER_OPTION => '0',
    COMMENT_OPTION => '1',
    PRIVILEGES_OPTION => '0',
    LABEL_OPTION => '0'
)";
...
//get results
//split on semicolon
//change library
//write back to database

The sql that it's then trying to write is:

CREATE OR REPLACE VIEW AMMLIBC.BBI_USER_MY_VIEW FOR SYSTEM NAME         BBIU_00001 (
/* view columns */ )
AS
/*view definition */
RCDFMT BBIU_00001

When I try to run it, it gives the following error:

iDB2SQLErrorException: SQL7029 New name BBIU_00001 is not valid.

...Why? What's causing this, and how do I make it work?


texts:
I have a particular issue with PostgreSQL 10:

Postgres creates backend processes for each connection on different ports. The problem is that some of our apps also use many ports and sometimes they collapse with Postgres resulting in a port binding error.

One workaround is to first start apps and then the Postgres service, but I'm looking for a cleaner solution.

Do you know if there's a way to specify a port range used by Postgres for backend processes? I have not found something built-in so far.


texts: subquery for history average is slower then expected

texts:
I have a MySQL table which contains historic product values. A new record per product is updates every minute. The timestamp field is an INTEGER field with a unix timestamp. There is an index on the timestamp column.
If we visualize the values based for each hour we use
WHERE MOD(history.timestamp, 3600) = 0

Now we want to calculate an historic 7 day average on the hourly results.
My first attempt is the following:
SELECT
    history_1m.timestamp,
    history_1m.value,
    (
        SELECT
            AVG(avg1_internal.close)
        FROM history_1m as avg1_internal
        WHERE
            avg1_internal.productid = 1 AND
            avg1_internal.timestamp <= history_1m.timestamp AND
            avg1_internal.timestamp > (history_1m.timestamp - (84600 * 7)) AND
            MOD(avg1_internal.timestamp, 3600) = 0
    ) AS 7d_avg
FROM history_1m
WHERE
    MOD(history_1m.timestamp, 3600) = 0 AND
    history_1m.productid = 1 AND
    timestamp >= 1616590800 AND
    timestamp <= (1616590800 + (180 * 3600))

This query takes approx. 8 seconds to run. However if we run the subquery seperatly it completes a single query in +- 0,0064 seconds.
My tought was that it tried to calculate the historic 7d average for all rows and later filtering them for the specified time range. (180 hours)
So i tried the following:
SELECT
    prefetch.timestamp,
    prefetch.value,
    (
        SELECT
            AVG(avg1_internal.close)
        FROM history_1m as avg1_internal
        WHERE
            avg1_internal.productid = 1 AND
            avg1_internal.timestamp <= prefetch.timestamp AND
            avg1_internal.timestamp > (prefetch.timestamp - (84600 * 7)) AND
            MOD(avg1_internal.timestamp, 3600) = 0
    ) AS 7d_avg
FROM (
        SELECT
            prefetch_data.timestamp,
            prefetch_data.value,
        FROM history_1m AS prefetch_data
        WHERE
            MOD(prefetch_data.timestamp, 3600) = 0 AND
            prefetch_data.productid = 1 AND
            timestamp >= 1616590800 AND
            timestamp <= (1616590800 + (180 * 3600))
    ) AS prefetch

However it still takes +- 8 seconds to complete the query.
If we manually select all the desired datapoints (180 hours). And execute the subquery one by one for the datapoints the result is calculated much faster.
Is there a way to get this result in a single query statement?


texts:
I am trying to compute the number of distinct pairs sender_id, send_to_id and assign it to a variable @UniqueRequests. When I try the statmement below, I get an error saying the syntax near ) is incorrect.

DECLARE @UniqueRequests int;
SET @UniqueRequests = (SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request));

So my questions would be

What is the correct statement?
It seems that we must use parentheses around a SELECT when assigninig a variable. That is to say, we need to write SET @MyVariable = (SELECT some_expression); Why is that so?
Is there any documentation page on the use of parentheses in SQL Server in general?

Thank you for your help!


texts:
If we have code in an SP Database1 then queries Database2 (on the same server) we want the same code to work on the databases Database1Dev and Database2Dev. But this currently means editing the full SP each time we push to Live.

We want a single of line of code such as

select col1, col2 from databaseName..ourTable

Where databaseName is somehow set at a server level.

I understand synonyms are the wrong solution for this. Is there something similar we can use that doesn't include writing a synonym for every single object?


texts: MariaDB User for both remote access and local access

texts:
I want to enable my app to access the database locally, and in the same time access it remotely from a tool like MySQLyog. I'm wondering whether I should be creating two users, one for the local connection, and one for the remote connect.
CREATE USER  'charla-user-db'@'localhost' IDENTIFIED BY 'pass@word';
CREATE USER  'charla-user-db'@'%' IDENTIFIED BY 'pass@word';

I thought this would be enough CREATE USER  'charla-user-db'@'%' IDENTIFIED BY 'pass@word';, since it should allow connections from any hosts, but I failed to connect locally using it.


texts: POSTGRES 9.4: Function parameter as empty array

texts:
I'm trying to create an empty array as a parameter so it's optional. I want to be able to call it without having the need to give a 2nd and 3rd parameter. Can somebody help?

CREATE OR REPLACE FUNCTION myschema.fnc_calc(
    p_par1 text,
    p_par2 text DEFAULT NULL::text,
    p_par3 par_type DEFAULT[] '{}'::par_type[]
)
RETURNS void AS

When I call it like this:

select * from myschema.fnc_calc('what_the_hell');

I get this message:

ERROR:  function myschema.fnc_calc(unknown, unknown, par_type) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

BTW, this is has nothing to do with the public schema or calling it from a different schema. All the references are right. I checked this. I'm thinking that the declaration of the array parameter (p_par3) in the function should be something different (maybe not subscribe to an array with the '[]', but leaving it out doesn't work as it requires the parameter to be populated). Any ideas?


texts: Insert to SQL Azure through Linked Server very slow

texts:
I have a table on SQL Asure server with no indexes except clustered.
And I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes. Although, when I do it with SSIS it takes just a second.
This is the query:

INSERT INTO DatabaseName.dbo.tmpOurClients
(KodClient,Name ,ShortName,INN,Boss,contact,Email,Adres,Tel,City_ID,DateLop)
SELECT KodClient,Name ,ShortName,INN,Boss,contact,Email,Adres,Tel,City_ID,DateLop
FROM dbo.OurClients

That is how I created the Linked Server:

EXEC sp_addlinkedserver
@server='Azure',
@srvproduct='',
@provider='sqlncli',
@datasrc='ServerName.DATABASE.WINDOWS.NET',
@location='',
@provstr='',
@catalog='db_name'

EXEC sp_addlinkedsrvlogin
@rmtsrvname='Azure',
@useself='false',
@rmtuser='login@server_name.database.windows.net',
@rmtpassword='password'

EXEC sp_serveroption 'Azure', 'Collation Compatible', true;

I've already checked this topic Linked server to SQL Azure very slow but none of suggestions helped me.

Version of local SQL-Server: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

So, what can be the problem?


texts: Conflict handling, SQL Server 2008 R2 Peer to Peer

texts:
I have a Peer to Peer replication topology set up between two SQL Server 2008 R2 servers. I was doing some testing on fault tolerance and conflict handling. I have conflict detection enabled, and continue relpication after conflict detected enabled. I took one of the servers off the network, and updated the same row on both servers.

For simplicity, let's say i updated columns A and B on the first node, and A, B, and C on the second node. On the first node, column C was null before and after the updates. On the second node, column C was null before the update, but not null after.

I reconnected the network, and replication picked back up. The changes from the first node (higher peer originator id) were made at the second node. However, the changes to column C were still present at the second node, but NOT at the first node.

Is this the expected behavior? I am thinking the transaction at the first node to update A and B overrode the transaction from the second node, and therefore column C was touched at node 2, and still null at node 1. Is there a setting that I a missing that will allow column C to be set to null at node 2?


texts: SQL Server Merge Replication Change Partition Definition

texts:
I am using Merge Replication on a few SQL Server 2008 R2 servers.  There is one server, and the rest are clients.

I have each of the clients on a different partition using row filters with the HOST_NAME() function.

This works as expected, but I can't seem to figure out how to change this value after creating the subscription.

I need to change this value for all of the subscriptions and I would like to avoid dropping and recreating them.

How can I change the HOST_NAME() value for a subscriber without dropping/recreating?


texts: Access the database by different users simultaneously

texts:
My two databases(.mdb) are placed at a network shared folder.

Now at the same time few users are connected to database from the network place and doing their job.

In the middle get the error like :

error 1 : "The database has been placed in a state by a user "Admin" on machine "machine no." that prevents it from being opened or locked."

error 2: “Cannot Open Database”. It may not be a database that your application recognizes, or that file may be corrupt.

can anyone please suggest me how i can open this database in programatically, so that different users can run their own application simultaneously without any error.


texts: What is the behavior of null/empty value in compound shard key?

texts:
I have a shard key based on {Field1:1,Field2:1} where Field2 can
  be null. So if two documents have same value for Field1 and
  null/empty value of Field2 then are they considered to belong to
  the same shard key/non split-able??