texts: How can I represent the entity types depicted in an ERD as relations with attribute names?

texts:
I have created the entity-relationship diagram (ERD) shown below (for brevity, each individual bubble-shaped box portrays multiple attributes):

Considering such an ERD, I have to convert each entity type or associative entity type depicted there as a relation with its corresponding attribute names, but I run into some problems.

I usually represent a relation as follows:

X = (a, b, c)*

* Where X is the name of the relation and a, b and c are the names of its attributes.

Then, in the business domain that I am working on, a Course depends on zero-to-many (0:M) other Courses (as portrayed in the ERD). This means that a Course should be connected somehow to the Courses it is dependent on.

So, how can I represent this situation as a (mathematical) relation?

Option 1

I have tried the following:

Course = (course_number, course_name, course_dependencies?)

Option 2

I think it is possible to depict the desired aspect in another way, e.g.:

Depends = (course_name)

and then

Course = (course_number, course_name, depends)

Option 3

But I thought about, maybe, arranging the elements as follows:

Course = (course_number, course_name)
Depends = (course_name_depender, course_name_dependee)

In this way, each pair of attribute names in the relation called Depends represents the association (or relationship) between two occurrences of the relation entitled Course.

Questions

Which of these options is the right way to do it? Perhaps there is a better approach?


texts: File Extensions are missing from new SSMS 2012

texts:
One of my colleagues SSMS recently got updated to 2012 version from SSMS 2008 R2. After that they are view only a few editors in SSMS for adding different File Extensions
Tools ==> Options ==>Text Editor => File Extension, whereas I am able to add / view more extensions. Felt something was missing in the settings. Anybody can help to find out the issue with this?

Adding the screen shot from both machines (Both are using SSMS 2012). Any help will me much appreciated.


texts: SQL Server Encryption with "localhost" certificate

texts:
Using SQL Server 2014/2016:

I'm trying to encrypt my connections from my application to my local SQL Server instance.  As I read the documentation, SQL Server seems to very much prefer certificates issued to a fully qualified domain name (not just localhost). SQL Server Configuration Manager only seems to see certs issued to the machine's FQDN.

I found this article which seems to indicate that you can use wildcard certificates as well.

But I am connecting from a local app to a local SQL Server instance that is not exposed to the network.  Is there any way to associate a certificate issued to localhost with a SQL Server instance?  This is doable with IIS Certificates (Encrypting HTTP traffic to localhost).

Internal security policies require that any inter-process communication with the database, even on a local machine, be encrypted.  From what I can tell there are no encryption options for Named Pipes. We are disabling all other protocols and only allowing TCP/IP, because it is the only option that supports encryption, as mandated by our security policies.

The problem is these are 300+ instances of SQL Express on individual workstations. The certificate management if we can't use just one localhost cert will be a nightmare.


texts: Anything like a hash for Postgresql databases?

texts:
I have an export process I want to check. I want to verify that source and destination databases are equal. There are about 25000 rows, and schema is the same.

Is there a way to somehow "hash" the database so that two databases containing the same data are confirmed equal?

I guess pg_dump output may differ even if data are same.


texts:
I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (all Number values are unique).

I tried this but I'm getting invalid column name

INSERT INTO dbo.B ( [ID] )
SELECT orig_id
FROM dbo.A a, dbo.B b
WHERE b.[NUMBER] = a.number

but a whole bunch of new rows are created instead of updating the existing ones. How can I update the ID column of table B with the id's from table A matching on the Number values?

Thanks


texts: Connecting from SQL Server 2000 client to SQL Server 2008 Express

texts:
How to connect from SQL Server 2000 client in my pc to our SQL Server 2008 express server?

I'm using to connect to our SQL Server 2008 with 'servername' using sql server authentication.

error: Unable to connect to server 'servername'
       Server: Msg17, Level, State1
       [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.


texts: moving large number of tables to different filegroups

texts:
An existing database has a large number of tables stored in the PRIMARY filegroup. I want to move theses tables and their indexes on different filegroups automatically, depending on the "prefix" of the table name.

for example, with 5 tables named like this :

ABC_XXXX
ABC_YYYY
DEF_ZZZZ
DEF_TTTT
GHI_UUUU

All tables beginning by ABC should be moved to filegroup FG1, DEF to filegroup FG2 and other tables to filegroup DEFAULT.

This can be done using the command CREATE INDEX :

CREATE (UNIQUE|CLUSTERED|) INDEX <Index Name> ON <Table Name>(<Index Columns>)
       WITH (DROP_EXISTING = ON) ON <New Filegroup>

The biggest problem with this command is to retrieve the columns of each index in their proper order.


texts: OLAP linked server fails connection test

texts:
I am trying to create a new linked server to an Analysis Services database and I would like to use this linked server to import data into a SQL Server table.
example:
SELECT *
into raw.example
FROM openquery( Test1, 'MDX CODE/etc etc'

Below is a screenshot of what I have done (just putting in examples/test names instead of the names I need to use). The data source is pointing to a valid server name, which I know I have access to.

After I click "ok" to create this linked server this message comes up:

The linked server has been created but failed a connection test. Do you want to keep the linked server?
OLE DB provider 'MSOLAP' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

I am not sure why it isn't connecting. Is it a server issue where I have to ask for permission to access it, or do I somehow need to revert away from the provider being configured to run in a single-threaded apartment mode?
I am doing this on my local SQL Server Express Edition machine, whereas before my script and OPENQUERY/MDX query worked on a remote desktop server etc.
I have looked at other posts but those solutions do not solve my problem.
If it matters, the MDX I'm trying to execute was generated by XLCubed.


texts: Is there a setting to force an error when asigning a varchar longer than a variable?

texts:
I would like to get an error when assigning a longer varchar to a variable and no silent truncation. Are there any settings?

This truncates silently:

declare @code varchar(10)
set @code = 'This is too long'
select @code

Inserting a varchar into a smaller Table column

create table test (col1 varchar(10));
insert into test values ('This is too long');

yields an error:

Meldung 8152, Ebene 16, Status 14, Zeile 2
String or binary data would be truncated.
The statement has been terminated.


texts: MongoDB replica set - no election after primary node failed

texts:
For testing I killed the primary node of my two-node replica set while the cluster was under heavy write load.

I was expecting the secondary node would take over after a few seconds, but this isn't happening.

shard0:SECONDARY> rs.status()
{
    "set" : "shard0",
    "date" : ISODate("2014-10-22T15:11:52Z"),
    "myState" : 2,
    "members" : [
        {
            "_id" : 1,
            "name" : "10.128.42.177:27020",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 10270,
            "optime" : Timestamp(1413990107, 52),
            "optimeDate" : ISODate("2014-10-22T15:01:47Z"),
            "self" : true
        },
        {
            "_id" : 2,
            "name" : "10.128.42.188:27020",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : Timestamp(1413990103, 49),
            "optimeDate" : ISODate("2014-10-22T15:01:43Z"),
            "lastHeartbeat" : ISODate("2014-10-22T15:11:50Z"),
            "lastHeartbeatRecv" : ISODate("2014-10-22T15:01:43Z"),
            "pingMs" : 0,
            "syncingTo" : "10.128.42.177:27020"
        }
    ],
    "ok" : 1
}

As you can see at the time of printing rs status the last heartbeat received was already 10 minutes old.

What am I doing wrong?


texts: mysql client gives no output when trying to connect to a remote DB

texts:
I am trying to connect from a Raspbian shell to a remote MySQL database (Windows).

I freshly installed mysql-client and mysql-server (version 5.5.55) and now try following command:

mysql -v -h hostname -uroot -pmypass db_name

I get no response at all. Program does not exit or print anything just keeps hanging.

Connecting to a local DB works fine with mysql -uroot -pmypass.

I am quite confused why the client does not print anything even with the verbose option being set on.

Thanks!


texts: How to delete specific range of data from Service Broker > System Queues data table?

texts:
Due to sudden increase of my database .mdf file we found there are 4 internal queue message table are increase unexpectedly. so we decided to delete some range of data from these table.
we try this code:
declare @c uniqueidentifier
while(1=1)
begin
    select top 1 @c = conversation_handle from dbo.queuename
    if (@@ROWCOUNT = 0)
    break
    end conversation @c with cleanup
end

but its try to  delete  all data and due to space limitation this query cannot execute.
my internal message queue table :


texts: How to grant execution rights on DBMS packages to a PDB user?

texts:
I'd like to grant execution right on dbms_lock package to a local PDB user.

I have created new PDB with a local administrative user. I'm assuming I can use that local user for all management tasks in PDB. But when I try to grant execution right on dbms_lock to another local user I get ORA-01031: insufficient privileges.

I'm using Oracle 12c database app development VM.

The steps to re-produce the problem:

Create new PDB as CDB SYS user

Creating a PDB Using the Seed

create pluggable database test1
admin user test1admin identified by test1admin
roles = (DBA)
file_name_convert = ('/pdbseed/', '/test1/')
;

alter pluggable database test1 open;

Log in to PDB as test1admin and create new local non-administrative user

create user test1 identified by test1;
grant connect to test1;
grant execute on dbms_lock to test1;

Error starting at line : 3 in command -
grant execute on dbms_lock to test1
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

I have used test1admin succesfully to grant several other privileges to th elocal users. I have also verified test1admin user has access to dbms_lock package. But it seems that dbms_lock-package (and btw dbms_crypto too) is different.


texts:
I have a login table, which registers datahora_ini (system entry time) and datahora_fim (system exit time). Sample data:

CREATE TABLE login(cod_user,datahora_ini,datahora_fim)
AS
  VALUES
    ( 101::int, '2017-06-02 08:02:14'::timestamp, '2017-06-02 13:59:23'::timestamp ),
    ( 102,      '2017-06-02 08:03:38',            '2017-06-02 16:13:53' ),
    ( 103,      '2017-06-02 08:05:50',            '2017-06-02 11:44:39' ),
    ( 104,      '2017-06-02 08:06:03',            '2017-06-02 14:01:33' ),
    ( 105,      '2017-06-02 08:06:35',            '2017-06-02 14:00:23' ),
    ( 106,      '2017-06-02 08:10:05',            '2017-06-02 08:31:26' )
  ;

I need to set up a query where I can add the time of all the users that were logged into the system at that time! Fake example of the desired format:

time    time_logged ( Seconds )
07:00   1005
08:00   4980
09:00   8193
10:00   9259
11:00   25692
12:00   18823

My current query :

SELECT
 TO_CHAR(al.datahora_fim,'HH24:00') as Data_ini,
 ROUND(SUM(CASE WHEN al.datahora_fim IS NULL THEN EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - al.datahora_ini)) ELSE EXTRACT(EPOCH FROM (al.datahora_fim - al.datahora_ini)) END))as tempo_logado

FROM callcenter.agente_login al
LEFT JOIN callcenter.agente a ON ( a.id_agente = al.id_agente )
LEFT JOIN crm.usuarios u ON ( a.crm_cod_usuario = u.cod_usuario )
WHERE ( al.datahora_fim BETWEEN '2017-06-02 00:00:00' AND '2017-06-02 23:59:59' )

GROUP BY Data_ini
ORDER BY Data_ini ASC;


texts:
I have an legacy application that has a number of very complex queries that take significant time and resource to execute. Rather than rewrite the application I am looking at the possibility of doing some form of load balancing.

I firstly looked at writing a script with pg_isready to determine if the database is able to respond, if not to potentially switch to another replica. However, this approach does not give an indication of the current load on the database.

I've read up on using haproxy to do some load balancing, but it seems as if it would suffer the same problem. My question is, has anyone faced a similar problem and found a neat solution. I would like to switch to a different replica is the database load is above a certain threshold. Is this at all possible ?


texts: DDL trigger AFTER ALTER ON SCHEMA behavior

texts:
Can anyone please tell me what's going on here:

CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA
 cols ora_name_list_t;
BEGIN
 IF (ora_dict_obj_type = 'TABLE') THEN
  select column_name bulk collect into cols from user_tab_cols where table_name =   ora_dict_obj_name;
  FOR i IN cols.first .. cols.last LOOP
   dbms_output.put_line(cols(i));
  END LOOP;
 END IF;
END;

The above trigger is expected to display the list of all the columns in a table after the ALTER TABLE command is executed.

The following works just fine when I ALTER a table with column(s) without any constraints:

Alter Table tab_xxx ADD(nucol char);

However, if I add a new column(s) with a constraint like

ALTER TALE tab_xxx ADD(n number NOT NULL);

then this new column(s) is not immediately reflected in the USER_TAB_COLS DD view and the FOR loop above to display the names of all the columns DOESN'T show this NEW column added.

Thanks already.


texts: Why keeps Oracle writing into WARNING_SETTINGS$?

texts:
For tests we have Oracle 11 instance running on a RAM disc. Since the RAM disc is limited in size the database must not do any auditing or anything that keeps increasing the size of anything.

We managed to fix many such leaks but have one left which we don't know how to handle:

The database tries to write to the table

WARNING_SETTINGS$

resulting (after a couple of weeks) in a

ORA-01653: unable to extend table SYS.WARNING_SETTINGS$ by 8 in tablespace SYSTEM

while trying to create a database object (a trigger right now)

We tried to disable all writes to that table with this statement

begin
  dbms_warning.add_warning_setting_cat('ALL', 'DISABLE', 'SYSTEM');
end;
/
commit;

The questions are:

What is the purpose of that table?

Why does Oracle keep writing into it?

How do we make Oracle stop doing that?


texts: How do I make Postgres use an index when using IN or ANY?

texts:
I have a query such as:

select col1 from mytable where col1 in('a','b','c')

But running such a query yields a Seq Scan per EXPLAIN

If I have an index on col1, why does Postgres not do an index scan?


texts: What is the behavior of SQL Server Maintenance Plan - Clean Up task for deleting old backups

texts:
This seems pretty obvious, but it's remarkably hard to find an actual document that addresses this.

The scenario:

Full Backup - Weekly (Set to expire after 7 days)
Differential Backup - Daily (Set to expire after 7 days)
Transaction Log Backup - Hourly (set to expire after 3 days)

In this scenario there is a full backup and then 6 days of differential backups tied to the full backup, with several transaction logs in between.

Now, I want to create a cleanup task to remove old backups. If I set the cleanup task to remove backup files (bak) older than 7 days does it delete the full backup and essentially orphan the 6 differential backups?

Does the expiration policy on the backup task "lock" those files from being deleted before they expire? In essence, could I set the cleanup task to delete files older than 1 day, and yet not delete backup files that haven't yet expired?

The goal here is to do a couple of things:

If a FULL backup is deleted, then all the differentials are useless, so they should be deleted as well.
Keep at least one full week of intact backups. i.e. If I tell the cleanup task (on the 8th day) to delete files after 7 days and it deletes a FULL, then the remaining 6 days of differentials become invalid and now I only have 1 day of backups.

I would like to treat the transaction logs the same way, but I only want point in time recovery for 3 days. After that it will be expected to fall back to the most recent, suitable differential backup w/o transaction logs. So, I would delete transaction logs older than 3 days as well.


texts: MySQL stalls and strange threads/connections behavior is observed

texts:
We have a MySQL instance which most of the time works just fine. From time to time, however, we observe strange behavior. What happens is that suddenly MySQL connections reach the limit (currently set to 1000) and MySQL pretty much stalls. The following is observed when we check the graphs for this server:

The image is from a Grafana instance running a MySQL exporter. What troubles me is why threads from the cache are not reused. The other question is why the threads in cache suddenly drops to zero but at the same time no new threads are created (according to the second graph - blue line stays at 0).

Ultimately, my goal is to understand why 1000 connections are used all of a sudden and if this is a problem with MySQL or the app. This is what the MySQL docs say in regards to threads:

  How many threads the server should cache for reuse. When a client
  disconnects, the client's threads are put in the cache if there are
  fewer than thread_cache_size threads there. Requests for threads are
  satisfied by reusing threads taken from the cache if possible, and
  only when the cache is empty is a new thread created.

Any assistance or guidance will are highly appreciated!


texts: Restructuring table - start with PK or not?

texts:
I am restructuring a bunch of tables in my database (in this case using a smaller data type for some columns - in most cases cutting those by ~50%, and they tend to be large columns).

I've gone through the process of creating a new table with the wanted structure, copying all the data over, dropping/renaming old table and then applying all constraints, indexes, reseeding etc...

My question is - should I create the new table with the primary key (clustered index) or should I be createing it without?

My understanding is that without means faster copying at the cost of restructuring later on, but with means no restructuring later on.

Considering that the data source should already be sorted by the same PK (the PK doesn't usually change type on these tables), what's the best option?

Data sizes vary between the different tables - some have a few million rows, most have far less.


texts: postgresql explain analyze from sql file?

texts:
I had wrote a long query for postgresql, and I want to use postgresql explain ANALYZE to find out runnig time / parallel workers information about that query.
I can run that sql command by using

psql -d database -f myquery.sql

but I don't know how to explan/analyze that sql file in command.
and because it is a really long query , it's hard to paste in psql console.
so , is there any other way to run explain/analyze from the sql file ?


texts: Cannot set innodb_buffer_pool_size above 1023M in my.ini

texts:
Windows server 2012 R2 with 64GB RAM, MySQL 8. The Database is about 5GB.

I am using a buffer pool of 1023M, as shown below:

innodb_buffer_pool_size=1023M

If I enter anything above 1023M e.g. 1024M or 2G then MySQL will not start and the following appears in the errorlog:

  "Configuring persisted options failed: "Variable
  'innodb_buffer_pool_size' can't be set to the value of '8388608'".

If I use the value 8388608, then it starts (which is ironic) but that's not enough.

When running using:

SET GLOBAL innodb_buffer_pool_size=10737418240;

It starts fine and sets the pool to 10GB - but that reverts back when rebooted.

Any pointers would be great.


texts: Understanding the differences in the storage space used by two tables

texts:
I have two tables in which, more or less, one contains a subset of data of the other one.  There is some data I delete from the first table, which is inserted in the second one as historical data.

These are the defintions:

CREATE TABLE [dbo].[CIC_DESLUNGHE](
    [CD_CIC_DESLUNGHE] [bigint] IDENTITY(1,1) NOT NULL,
    [CD_CIC_PRODUZIONE] [bigint] NOT NULL,
    [CD_CIC_OPERAZIONI] [bigint] NOT NULL,
    [AFNMPROG] [int] NOT NULL,
    [AFDSLINE] [nvarchar](max) NULL,
 CONSTRAINT [PK_CIC_DESLUNGHE] PRIMARY KEY CLUSTERED
(
    [CD_CIC_DESLUNGHE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[CIC_DESLUNGHE_STORICI](
    [CD_CIC_DESLUNGHE_STORICI] [bigint] IDENTITY(1,1) NOT NULL,
    [CD_CIC_PRODUZIONE_STORICI] [bigint] NOT NULL,
    [CD_CIC_OPERAZIONI_STORICI] [bigint] NOT NULL,
    [AFNMPROG] [int] NOT NULL,
    [AFDSLINE] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_CIC_DESLUNGHE_STORICI_1] PRIMARY KEY CLUSTERED
(
    [CD_CIC_DESLUNGHE_STORICI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The second table contains a subset of data that I need it as historicization of my data.

Table                   Rows        TotalSpace MB
---------------------   ----------  -------------
CIC_DESLUNGHE_STORICI   12.709.497  48214,75
CIC_DESLUNGHE           24.827.337  5675,49

What leaves me astonished is that the total storage space utilized. The subset table use 10 times the space of the other.

Any idea why this is happening?

I tried rebuilding the clustered index of CIC_DESLUNGHE_STORICI, but the storage only dropped from 48 GB to 47 GB.

-- EDIT --

This is the result I got with the query of Josh Darnell


texts: Bidirectional Replication Push and Pull with SQL Server

texts:
Please bear with me, I'm a developer working with a client without access to a strong DBA. I have a question about bi-directional replication with the following setup and requirements:

MSSQL Server Database A: the back-end of a web application in a remote environment
MSSQL Server Database B: a mirror of Database A that resides in a DMZ

Databases A and B can be updated independently from each other but need to stay in sync. Normally this would be a good candidate for bi-directional replication because the business rules are such that conflicts will not occur in the replicated tables.

However, the client has a security requirement that no transactions can be initiated from the remote environment into the DMZ.

Can I set up bi-directional replication so that the DMZ Database B pushes its changes to Database A and pulls Database A's changes in? Or do you suggest another strategy - replication or otherwise?

Thanks!


texts: Counting Null rows between non Null values

texts:
I have a table that counts logins to a website with the first column, USER_ID, being the user's unique identifier. The next column is a list of months in the date format %m/%d/%Y, and the next column is a date which indicates whether or not that person logged into the website during that month.
What I want to generate is a 4th column that counts NULL values between non-NULL values for that 3rd column.

My question is: given the first 3 columns below, how would I write a query that generates the 4th column?


texts: LOAD DATA INFILE blocks on concurrent use against MyISAM tables

texts:
I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks – a system lock, in MySQL profiling information – until the previous is finished. This happens independent of loading into the same/different table, or even a different database. InnoDB does not have the same problems, however, MyISAM seems better suited for a pure logging-table.

Can concurrent LOAD DATA INFILE operations be done with MyISAM engine on MySQL? Am I missing some crucial settings (DISABLE KEYS is automatic against an empty database)? Tested on MySQL 5.1.63 and 5.5.14.

I expected loading using LOAD DATA INFILE to different tables concurrently to be performant, however, performance is instead completely destroyed.

To replicate my scenario:

SQL

CREATE DATABASE test;
CREATE TABLE `log_isam` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `num` int(11) DEFAULT NULL,
  `surname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE log_isam ADD INDEX num(num);

CREATE TABLE `log_isam2` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `num` int(11) DEFAULT NULL,
  `surname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE log_isam2 ADD INDEX num(num);

LOAD DATA log.txt file contents

https://gist.github.com/4245602

Shell script

MYSQL_USER=root
MYSQL_PASS=PASS
time mysql -u$MYSQL_USER -p$MYSQL_PASS -e \
    "LOAD DATA INFILE '/tmp/log.txt' \
     INTO TABLE log_isam FIELDS TERMINATED BY ','" test & \
time mysql -u$MYSQL_USER -p$MYSQL_PASS -e \
    "LOAD DATA INFILE '/tmp/log.txt' \
     INTO TABLE log_isam2 FIELDS TERMINATED BY ','" test


texts: Mongo HDD disk grows to fast when insert / deleting data

texts:
My Mongo version is 2.6.9

I have a application what is inserting, reading and removing data from Mongo. Thats all working fine, however the disk space increate exponentially. My HDD is around 120 GB, and mongo consumes about: 100GB.

I checked the collection what was consuming so much data, i couldnt do a repair database because of disk space. So i remove my database completely.

mongodump -d db
echo 'db.dropDatabase()' | mongo db
mongorestore /root/bashscript/backups/dump/db

I reimported all the old documents, except from the collection what was consuming so much data. I recreated this collection with compact and power of 2 sizes (because that should do the trick)

Now when i add new data in this collection (around 200.00 docs), the result is the same, the collection is using 80GB of data.

What am i doing wrong? Am i using Mongo DB wrong? Is it not a good idea to use mongo db in my situation?
I don't understand why mongo is consuming so much disk space.


texts: Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

texts:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7600: ) (VM)
Microsoft Server 2008 R2 Standard

ProductVersion 10.0.2531.0
ProductLevel   SP1
Edition        Enterprise Edition (64-bit)
EngineEdition  3

In trying to execute the following powershell-script...

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost\myServer; Initial Catalog=SysAdmin; Integrated Security=SSPI")

$conn.Open()

$cmd = $conn.CreateCommand()

gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {
   $Name      = $_.Name.substring(0,1)
   $FreeSpace = $_.FreeSpace
   $Size      = $_.Size

   $cmd.CommandText = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('"+ $Name + "', " + $FreeSpace + ", " + $Size + ")"
   $cmd.ExecuteNonQuery()

   $cmd.CommandText = "EXEC dbo.sp_diskspace @performAggregation=1"
   $cmd.ExecuteNonQuery()
}

$conn.Close()

...I get the following error

  Message
  Executed as user: Domain\SqlSrvAgentSer.

  A job step received an error at line 7 in a PowerShell script. The corresponding line is 'gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near ','."  Incorrect syntax near ','.  '.  Process Exit Code -1.  The step failed.

Additional Information

CREATE PROCEDURE dbo.sp_diskspace
   @performAggregation BIT = 0
AS

SET NOCOUNT ON;

DECLARE @aggregrateDate DATETIME

BEGIN
  SET @aggregrateDate = DATEADD(month, -1, GETDATE())
  SET @aggregrateDate = DATEADD(dd, DATEDIFF(dd, 0, @aggregrateDate), 0)

  INSERT INTO dbo.diskspace (
     drive,
     MeasurementDate,
     [free(bytes)],
     [total(bytes)],
     isAggregated)
  SELECT
      drive,
      DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0),
      AVG([free(bytes)]),
      AVG([total(bytes)]),
      1
  FROM
      dbo.diskspace
  WHERE
      MeasurementDate < @aggregrateDate
      AND isAggregated <> 1
  GROUP BY
      drive,
      DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0)

  IF @@ERROR = 0 and @@ROWCOUNT > 0
     BEGIN
        DELETE FROM dbo.diskspace
        WHERE MeasurementDate < @aggregrateDate
        AND isAggregated <> 1

        IF @@ERROR = 0
           BEGIN
              RAISERROR('sp_diskspace : aggregation complete', 0, 1)
           END
     END
END

Any help on this would be greatly appreciated!


texts: So I have been trying to design a inventory management system for a Car Showroom. The problem with this design is that I can get a purchase but on selling the product I can't link it to my purchase. The idea I've came with up is add a Batch_ID to my Inventory_Details table but I can only link that Batch_ID in my details table not in my inventory table. Any suggestions will be helpful.


texts: How to upgrade PostgreSQL from version 8.4 to 9.4?

texts:
I want to upgrade my PostgreSQL from version 8.4 to 9.4.

The documentation is not very clear to me.

Will I lose my old databases if I do the upgrade?
How can I backup my old databases if I am to lose them after the upgrade?
How can I upgrade my psql?

My PostgreSQL is running on a CentOS 6.6 server.


texts: Stored procedure slows down after upgrade from SQL Server 2008 to SQL Server 2016

texts:
Post migration from SQL Server 2008 to SQL Server 2016, one of our stored procedure is showing performance degradation. It works fine few days, and then gets slow taking up to 5 minutes. Interestingly, at times SP gets back to working normal again.

On clearing the query cache of stored procedure (DBCC FREEPROCCACHE), it starts to work fine (but only for few days).

We have tried to look into execution plan, and it looks like when the performance is fine – the smaller table join happens first to filter the records, whereas while the stored procedure is slow, the smaller table join happens in the end resulting into slow performance. The clustered index seek shows up "Expected Number of Rows"= 4 and "Actual Number of Rows" ~ 60000000.

Appreciate any inputs.


texts: How to configure the client certificate in Power BI desktop to authenticate with mutual TLS

texts:
I am trying to connect to a private data source from Power BI desktop. Data source has its own trusted root authority. I created my personal certificate and private key signed by this root CA. I configured the PFX file of my certificate in "personal" folder in windows' certificate management UI. I also added the root CA as trusted CA.
Power BI desktop does not seem to pick up this certificate and fails the mutual TLS authentication. I added my cert to firefox and opened the server url, it is able to authenticate me fine using mutual TLS there.
What am I doing missing?
FHIR data source connector. JPA over Postgres is the actual server connection.
Error message:

the request was aborted: could not create ssl/TLS secure channel.

When I connect from firefox, it is using TLS 1.3 for mutual authentication. Does Power BI not support TLS 1.3? The server supports both TLS 1.2 and 1.3


texts: SQL Server Cardinality Estimation Warning

texts:
How can I fix the following cardinality estimation warning?
while exists(select 1 from @images)
begin
   declare @imageid     int
   declare @filename    varchar(max)
   select TOP (1)
      @imageid = [imageid],
      @filename = concat([imageid], '.', [extension])
   from
      @images
   order by
      [imageid]
   ...
   delete @images where [imageid] = @imageid
end

SQL Server Compatibility Level: SQL Server 2019 (150)


texts:
I've got this query I'm testing with:
select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average
from record_sales_data
where customer_country_id=2
group by week,customer_country_id

Which returns what I want for country 2 of course. Now I'd like to return the average for ALL countries, but I don't seem to find a way to return by all countries, grouped by country id..
In the example above, here's what country 2 looks like in the raw data: (there are many more countries of course)
year|month|week|customer_country_id|units|
----|-----|----|-------------------|-----|
2020|    5|  21|                  2|    1|
2020|    5|  21|                  2|   36|
2020|    5|  22|                  2|   -1|
2020|    5|  22|                  2|   -1|

I apologize if this is a simple to resolve question but I'm still learning SQL and can't figure out an easy way to come up with a solution to this.
Thanks!


texts: Enforce a hard limit on write execution time during Amazon RDS write stalls

texts:
I am trying to recover from the situation where Amazon RDS write operations stall (some discussion on why this is here). If I show full processlist, I will see something like this:

318263  myapp1  domU-XX-XX-XX-0F-90-C1.compute-1.internal:55660 mydb    Query   88  Updating    UPDATE `profiles` SET `updated_at` = '2012-05-23 14:24:46', `latitude` = 38.896, `longitude` = -77.0452 WHERE (`profiles`.`id` = 100767)
318264  myapp1  domU-XX-XX-XX-01-60-B1.compute-1.internal:46609 mydb    Query   91  updating    DELETE FROM `unread_message_indices` WHERE (`unread_message_indices`.`sender_id` = 100601) AND (`unread_message_indices`.`recipient_id` = 101515)
318265  myapp1  domU-XX-XX-XX-14-41-C1.compute-1.internal:59277 mydb    Query   88  Updating    UPDATE `inbox_profile_indices` SET `updated_at` = '2012-05-23 14:24:22' WHERE (`inbox_profile_indices`.`id` = 127613)

As you can see from the number after query, these have been in the state of "updating" for 80-90 seconds! I am running the largest DB instance, so clearly something bad is happening on the EBS node on which the DB is running.

In these situations, I would prefer the query fail after 1 or 2 seconds, not wait for over 1 min stuck in the "Updating" state. I am using Ruby's ActiveRecord FYI. What is the best way to force a failure in this case after 2 seconds? Should I use innodb_lock_wait_timeout (Don't think so since these tables aren't locked and besides these times are clearly > 50, which is what it is set at). I believe the optimal approach is to set the per-session read timeout and write timeout. In Ruby on Rails this is done by editing the databases.yml file and entering something like:

production:
  ...
  write_timeout: 2
  read_timeout: 10

Will this approach work? Are there any other approaches I can use to more quickly fail these queries instead of having them hang my application threads indefinitely?

Thanks!


texts: Count two different columns from two different tables and throw into a single variable

texts:
I have two tables in my project: "cats" and "items". Every single item in "items" table has the value "item_cat" (column), which represents the ID of actual category, where that item is assigned to. And every single category in "cats" table has its unique ID.

When I add a new item, I can choose the category ID which can only be the one of categories from "cats" table: "music" (id=1), "books" (id=2) or "movies" (id=3). Items stored: "Pulp fiction" (id=1), "Mein kampf" (id=2), "Gone with the wind" (id=3), "Les miserables" (id=4), "The Holy Bible" (id=5) and "The best of Tina Turner" (id=6). Items 1, 3 and 4 belong to category 3, items 2 and 5 belong to category 2, and item 6 belongs to category 1 (I hope I'm still understandable ;) ).

Now I want to count how many items are assigned to every category. Is it possible to use "SELECT COUNT" in some combination, to get such a result as an output: cat1 = 1 (item), cat2 = 2 (items), cat3 = 3 (items)? How to do this?

Thank you in advance for your help!


texts: Check data is the same after restore SQL

texts:
I've backed up, transferred to another location and restored a database. Is there anyway to check that the data is the exact same?

Would I be best using the CHECKSUM and then the VERIFYONLY method?

Thanks.


texts:
I'm developing an app that allows to track a mobile device instantly (live)  ...
I need an of advice. The application must send the location to a webservice that in it's turn records the received data in a database.

What would be, in your opinion, the best way to store the location values?

I'm new in using bigdata and I'm afraid that simple sql requests wont be able to do the work properly ... I imagine if there is lot of users and each user send a request each 1sec I'll have issue with the database ...

An advice ? Thank you very much


texts: MySQL auto increment problem with deleting rows / archive table

texts:
A hosted server is running "maintenance" each weekend. I am not privy to the details.

In a database on this server there is a MyISAM table. This table never holds more than 1000 rows and usually much less. It is MyISAM so that the auto increment does not reset (and with so few rows it really doesn't matter). Rows are regluarly deleted from this table and moved to an archive table (1M rows).

The problem is lately the auto increment has "rolled back" slightly after each maintenance.

Is there any easy way to verify the auto increment of the insert table by reading the max id from both the insert and the archive table?

I'd rather not verify before each insert unless that is the only solution.

Here are the basic table layouts:

CREATE TABLE x
(
    xid int(10) unsigned NOT NULL AUTO_INCREMENT, //snip
    PRIMARY KEY (xid)
) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE xhistory
(
    xid int(10) unsigned NOT NULL DEFAULT '0', //snip
    PRIMARY KEY (xid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Far from perfect workaround: (this was somewhat urgent, I had to manually update over 100 rows)

select xid from xhistory where x=?

Check if just inserted row in x exists in history. If it does:

select greatest(max(x.xid),max(xhistory.xid)) as newval from x,xhistory

Find a new id.

INSERT INTO x SELECT * FROM x AS iv WHERE iv.xid=? ON DUPLICATE KEY UPDATE xid=?

And update our row with this id.


texts: Query an intermediate table and join to child table

texts:
Here's my database structure:

Product
Market
ProductMarket (linked to Product and Market with "product_id" and "market_id"
Price (linked to ProductMarket with "productmarket_id")

I want to query the ProductMarket table and filter by a particular product. Then, I want to associate the latest price (from the Price table) to the product (for each market), based on a date field in the Price table.

Right now, I have that:

SELECT
    mp.id AS id,
    p.name AS productName,
    m.name AS marketName,
    mp.identifier
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
WHERE
    productName = 'SomeProduct'

I'm having difficulty integrating the Price table in. How can I achieve this?


texts: db2 range partitioning attach and detach issue

texts:
We have a table which is used in many select Queries, as it is be accessed by customers to fetch their information. Also they can change their information too whenever they want.

We had to retrieve records only less than 2 years of age. So whatever is older than 24 months gets deleted.

Also, we have a script which actually runs every month and adds (not ATTACH) a partition to the table.

The problem is, we were testing the add and DETACHING by loading the table with some dummy data and running 'Select * from tab'. The ADD and DETACH scripts seems to fail.
Can you please elaborate the reason for this and also how can I achieve the above, without having to force connections and bringing the table down as its very critical and must be available at all times.

db2 "alter table tabname add partition <partname> starting 'date' ending 'date' exclusive"

now when table is not being used it goes on well. But when I load the table and just run db2 "select * from tabname" and then run the same (as above) command to add partitions following error comes. Here is the error:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001


texts:
I am curious about something, I was trying to find any long running queries currently running on my Oracle database.

The Query:

SELECT username,sid, serial#,
     TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI:SS') AS curr,
     TO_CHAR(start_time,'HH24:MI:SS') AS logon,
     (sysdate - start_time)*24*60 AS mins
FROM V$SESSION_LONGOPS
WHERE    username is not NULL
AND (SYSDATE - start_time)*24*60 > 1 ;

The output:

USERNAME SID   SERIAL#  CURR         LOGON       MINS
user     1999   23457   15:53:59    13:58:10    4435.81666666667
user     581    7549    15:53:59    15:57:10    4316.81666666667
user    1999    31343   15:53:59    16:58:10    4255.81666666667

Based on the info above is that telling me I have several queries running for days?

I am new to Oracle, mostly MySQL but i know in MySQL this is not a good thing.

If someone could tell me what I am seeing that would be great.


texts: Same session ID(with same SQL text) but 3 different connection ids

texts:
I was listing out all sessions open in my sql server 2016 production server.
I have issues with slow performance and i have been investigating it.
I see that i have several same sessions(session_id) with multiple connection_ids.
All are in suspended state(must be waiting for some resource..investigating this also)  and the sql text also is same.

I wonder why i have 3 different connection_ids for the same session_id.

When i looked at the sys.dm_exec_connections for those connection ids i see the below info.

Can someone help me to understand this.?

Additional Info:

That query runs in parallel.So if this indicates the parallel execution then i have the below scenarios where:

The scalar function is executing with three diff connection_ids and with same session_id.That function is not touching any tables as it takes a value are format(does substring) it to display in a different way.There is no way that the particular function can run in parallel as  per my understanding.
As shown in the screeshot ,the return statement from the same function also have 3 different connection_ids.
I have a MAXDOP of 8 set,so if this indicated parallel execution,then the number can be greater than 3 as per my understanding.


texts: Foreign Data Wrappers & Windows Authentication

texts:
I been trying to get data from a SQL Server to use it on a PostgreSQL database. After a previous question I got into use FOREIGN DATA WRAPPERS, first I try with tds_fdw but after a successfull install I couldn't make it work, possibly due to the same issue I detail below.

After this i went to Multicorn and things where a bit better since there actual documentation for the usage of it.

After attempting to add a table from SQL Server I got the message:

 ERROR:  Error in python: OperationalError DETAIL:  (OperationalError)
 SQL Server message 18456, severity 14, state 1, line 1: **Login failed
 for user 'postgres'**. DB-Lib error message 18456, severity 14: General
 SQL Server error: Check messages from the SQL Server DB-Lib error
 message 20002, severity 9: Adaptive Server connection failed

So I got it that its an authentication error, the SQL Server works with Windows Authentication so my connection string (mssql+pymssql://postgres:postgres@10.0.0.10/ForeignDatabase) its rendered useless. After some more search i got to this answer:

  The check_mssql_server.py plugin will NOT work with Windows
  Authentication and requires SQL authentication.

  As of this time I do not believe anyone has created a MSSQL plugin
  that can use Windows Authentication.

Clearly not the same scenario but its also a Data wrapper operation giving a extremely similar error message.

So my question is: Is it true that there's no way of using a foreign data wrapper from PostgreSQL to SQL Server using Windows Authentication?. I did add a new user (postgres:postgres) to the SQL Server.

I will also like to add that the PostgreSQL 9.3 Server lives on an Ubuntu Server 14.04 and the SQL Server 2005 on a Windows Server 2003.


texts: PostgreSQL 9.6 performance problem with Sum() aggregate query

texts:
I have the following table:

     id                | bigint                      | not null default nextval('shares_id_seq'::regclass)
     poolid            | text                        | not null
     blockheight       | bigint                      | not null
     networkdifficulty | double precision            | not null
     miner             | text                        | not null
     worker            | text                        |
     ipaddress         | text                        | not null
     created           | timestamp without time zone | not null
     useragent         | text                        |
     payoutinfo        | text                        |
     difficulty        | double precision            | not null default 0

Indexes:
        "shares_pkey" PRIMARY KEY, btree (id)
        "idx_shares_pool_block" btree (poolid, blockheight)
        "idx_shares_pool_created" btree (poolid, created)
        "idx_shares_pool_miner" btree (poolid, miner)
        "idx_shares_pool_miner_diff" btree (poolid, miner, difficulty)

I cannot wrap my head around why the following query takes so long:

explain analyze SELECT SUM(difficulty) FROM shares WHERE poolid = 'xmr1' AND miner = '4BCeEPhodgPMbPWFN1dPwhWXdRX8q4mhhdZdA1dtSMLTLCEYvAj9QXjXAfF7CugEbmfBhgkqHbdgK9b2wKA6nqRZQCgvCDm';
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4150712.54..4150712.55 rows=1 width=8) (actual time=25490.101..25490.101 rows=1 loops=1)
   ->  Bitmap Heap Scan on shares  (cost=389414.64..4143195.97 rows=3006629 width=8) (actual time=2499.409..24815.011 rows=7445802 loops=1)
         Recheck Cond: ((poolid = 'xmr1'::text) AND (miner = '4BCeEPhodgPMbPWFN1dPwhWXdRX8q4mhhdZdA1dtSMLTLCEYvAj9QXjXAfF7CugEbmfBhgkqHbdgK9b2wKA6nqRZQCgvCDm'::text))
         Rows Removed by Index Recheck: 55232916
         Heap Blocks: exact=149273 lossy=2641988
         ->  Bitmap Index Scan on idx_shares_pool_miner  (cost=0.00..388662.98 rows=3006629 width=0) (actual time=2449.977..2449.977 rows=7445802 loops=1)
               Index Cond: ((poolid = 'xmr1'::text) AND (miner = '4BCeEPhodgPMbPWFN1dPwhWXdRX8q4mhhdZdA1dtSMLTLCEYvAj9QXjXAfF7CugEbmfBhgkqHbdgK9b2wKA6nqRZQCgvCDm'::text))
 Planning time: 0.256 ms
 Execution time: 25490.137 ms
(9 rows)

I specifically created index idx_shares_pool_miner_diff for this scenario, yet it isn't even used. Can't PostgreSQL use the index due to MVCC?

Update:

Running vacuum analyze shares as suggested made PostgreSQL use the aforementioned index:

 Aggregate  (cost=546165.94..546165.95 rows=1 width=8) (actual time=2489.446..2489.447 rows=1 loops=1)
   ->  Index Only Scan using idx_shares_pool_miner_diff on shares  (cost=0.69..537874.79 rows=3316458 width=8) (actual time=0.041..1924.396 rows=7461785 loops=1)
         Index Cond: ((poolid = 'xmr1'::text) AND (miner = '4BCeEPhodgPMbPWFN1dPwhWXdRX8q4mhhdZdA1dtSMLTLCEYvAj9QXjXAfF7CugEbmfBhgkqHbdgK9b2wKA6nqRZQCgvCDm'::text))
         Heap Fetches: 16575
 Planning time: 0.122 ms
 Execution time: 2489.477 ms
(6 rows)

I assume that's as fast as it gets without using parallel workers.

After reading the docs about index-only-scans, it looks like I'm taking a performance hit due to the constant heavy write activity on the shares table (> 200 inserts/sec), causing the many Heap-Fetches. Interestingly the number of Heap-Fetches is apparently only going up, never down.


texts: How can I determine the performance (rows/second) for a certain query on one of my tables?

texts:
I want to find the performance (rows/second) for an UPDATE query on one of my tables so I can adjust my WHERE clause to select an appropriate number of records.

The table has around 10 million rows. The WHERE clause uses an index to select rows, and the updates will not change the index. I'm using MySQL 5.5, InnoDB engine.

Bonus question: How can I perform this test in a read replica version of the table? If possible.


texts:
I want to write a report that gets all the permissions for every Login in our SQL server farm. For now I can get the logins but get stuck there trying to connect the logins to roles. Haven't even found a way to get the roles yet. Are there PowerShell functions available for this or do I need to write sql statements and parse the results with PowerShell? Alternatively, Am i making it too complicated and should get to the information from a different angle?

#Get all the logins for every server
#Filters out all "NT Service" and "NT Authority" accounts as well as MSPolicy Accounts
$SQLLogins = Get-SqlLogin -ServerInstance $DefaultServerList | Where {($_.name -notmatch "^NT Service\\.*") -and ($_.name -notmatch "^NT AUTHORITY\\.*") -and ($_.name -notmatch "##.*##")}

#For every one, determine the permissions
ForEach ($Login in $SQLLogins) {
    Write-Host $Login.name "    " $Login.Parent.Name
}


texts: mysqldump - does it have non-repeatable DMLs?

texts:
We are migrating databases using mysqldump, and since this is the first time I'm doing it, I have a couple of questions.

If in the middle of mysqldump the utility crashes(for some reason- latest was global max_allowed_packet error that I had to increase), can I just rerun the dumpfile completely or try to find how much it had progressed and start from there? Is the dumpfile from mysql

2nd question - I use
    mysql -u <> -p<> < dump.sql
method to restore. Any chance we can see how much it has progressed? Is there any utility that would allow me to see the progress since I'm restoring 2 TB databases ?


texts:
Data in table A:

X   Y
------
m1  r1
m1  r2
m1  r3
m2  r1
m2  r4
m2  r5

Data in Table B:

Z
--
r1
r2
r3
r4
r5

Using a query I want a result like

m1  r4
m1  r5
m2  r2
m2  r3

The data required from join of two sample tables so that data corresponding to column Y which is not in table B. I have already developed a solution using cursors and inserting data in the table but that's is causing performance issues like 10 hours for 125122 distinct rows of column X in table A. I want a query where insertion operations are not involved so that we can save some disk I/O operations and optimize the performance.


texts:
I'm studying a DB course and I found this table:

In this exercise, the objective is to determine whether the table has redundancy or not.

According to the definition of redundancy

  Data redundancy occurs in database systems which have a field that is repeated in two or more tables

Redundancy DOES exists in the table,FECHA_INICIO(START_DATE) and FECHA_FIN (END _DATE) attributes, but should this attributes be considered as redundant?

Dates are quite volatile and I'm not quite convinced that those columns should be treated as redundant?

Any ideas on whether this table contains redundancy or not?


texts: Table not getting created under dbo schema

texts:
When creating tables in SSMS, I've realized that if you execute the following statement:

CREATE TABLE [tableName];

the table will be created under your own schema (and not dbo).  So in order to create it under the dbo schema, you'll need to explicitly say so, like this:

CREATE TABLE [dbo].[tableName];

Does anyone know of a way (eg. server-wide setting) so that when a table is created, the [dbo] part doesn't need to be specified?


texts:
I currently have 1 mysql server running and want to install 2 seperate mysql VPS'es. Maybe one master for inserts/updates and one slave for selects. What is the best setup for this if I have the following requirements:

Run backups on machine
When my mysql backups run on the slave machine it generates a high load. Can the master decide to take over some selects to reduce the loads?

Is there a way that the master decides where to execute the query (load balancing)? Is it possible to send all queries to one master and the master decides if he sends it to the slave, so I don't have to make 2 connections in my php code (1 for select, 1 for updates)?

Thanks,
Ward


texts: How to insert a Unicode character verbose into a varchar DB?

texts:
I need to insert this character '●' into a VARCHAR column of a MSSQL database with collation set as SQL_Latin1_General_CP1_CI_AS (or at least mock what my Python + Windows MSSQL Driver might have done). I tried to insert this from my client, but it gets inserted as ?.

The hex value as checked in Python is \xe2\x97\x8f which is binary 226, 151, 143.

The collation spec says that 226 is defined but 143 and 151 is undefined. So my best would be to insert 226?

The reason I am doing this exercise is that our app was inserting this character into DB earlier. At this point, I don't know how it went in because but it went through a Python App with Windows MSSQL Driver and our DB Team wrote a script to correct it and apparently it got replaced with "?".

Now I am just trying to reproduce this scenario to ask them why it is getting replaced with "?" because we are replacing it as empty in the App. Since my App already has a fix for this issue (we remove anything greater than 127), I am trying to mock this directly from back-end to prove that it is getting replaced with '?' which is actually my claim and I think it is done because it says "COLLATE" in the update query.

UPDATE pr_response
SET nur_respon =
REPLACE (nur_respon,
     SUBSTRING(nur_respon, PATINDEX('%[^ !-~]%' COLLATE Latin1_General_BIN, nur_respon), 1),
         '')
WHERE PATINDEX('%[^ !-~]%' COLLATE Latin1_General_BIN, nur_respon) > 0

Is this a fine script to remove Non-ASCII-Data > 127? Can someone explain this query in plain English?


texts: Foreign key part of a composite primary key

texts:
I have a table Room with primkey on column room_id and another table Items with primkey item_id. Each Item has a foreign integer key to room_id. When my program runs, it only looks at items in a single room so it would help if my primary key was on (room_id, item_id) in this order so that data is sorted on disk in this order.
Is this possible? Or do I need to duplicate room_id in Item and not have it as foreign key?


texts:
I have a lookup table

PerformanceID (incremental integer - Primary Key - Identity Column)
PerformanceRating (Integers from 1 to 5)
Description (100 char description of what the rating means)
PerformanceFactor (numeric, not an int, field that ranges from 0 to 5)

The PerforamnceID is equivalent to PerformanceRating, just because they both start at 1. Though this can easily change if an entry is deleted and re-added, thus skipping an integer value in PerforamnceID.

The only downside I really see to this is that now if a DBA wants to update a table that references the lookup table the PerformanceID is completely arbitary. Is this a bad thing?

Would there be a downside to making the PerformanceRating the Primary Key?


texts: Postgres randomly fails silently on huge CSV import

texts:
With Postgres 10.12 on Windows 10, I am importing at 67 GB CSV file into a database using a simple COPY query:

COPY table_name FROM 'c:/temp/data.csv' WITH (FORMAT csv, HEADER);

The import fails silently at random intervals, with the intervals ranging from 1.x minutes to over 10 minutes. This is the only process on the device with major resource use, so the time-difference cannot be explained by other processes competing for resources.

(Why 10.12? Postgres 11+ has a bug that blocks straightforward imports of large files. Yes, I know I could use the cat command with Postgres 12 (see third answer), and I actually did, and ran into the same problem.)

While the query runs, the Postgres process has sustained, high disk activity, generally 16 - 46 MB/s (running this on a Surface Pro 4 i7).

The response from the query always indicates success. The latest time I ran it, the response was Query returned successfully in 13 min 28 secs.

The resulting table is always empty but behaves strangely. If I count rows (SELECT COUNT(*) FROM table_name), I have to wait through about 45 seconds of very high disk activity before it returns 0. Viewing the table through Pgadmin 4 has a similar outcome: many seconds of high disk activity, then 0 rows returned.

There is data in there somewhere, however. If I go to C:\Program Files\PostgreSQL\10\data\base and check the size, there's over 11 GB of data in there, so some data is coming in. I inspected further and found several 1 GB files in C:\Program Files\PostgreSQL\10\data\base\16393.

I have segmented the first 45 lines of the CSV into a separate file. If I import that file instead, it runs fine, and I get 44 rows in the table (row 1 is a header and is not imported).


texts: How to identify whether a log backup or job executing a SP is causing High Page Reads/sec

texts:
For one of my servers( SQL server 2008R2) we can continuously witness High Page Reads/sec during business hours say: from 7 AM to 9 PM EST.

I kept the counter settings to monitor the same and found Page reads /sec to be constantly high with avg value of 4225 and max value of 60489. In addition during this High Pages read values PLE also remains very low, avg 90 and max of 270.

Therefore I went and checked for the process running during that duration :

Log backups occurring hourly
SQL Server agent job kicks a SP which checks the conditions and do Updates and Inserts
accordingly.
There are other jobs which executes few other SP's as well, but they are scheduled run once a week on same date:

I believe the 2nd point to be a culprit, but is there a way I can be sure if that is the case and if yes, what should be the first thing to look with?

Ram: 16 GB, CPU 16 core

Databases: 2

Kindly suggest! Thanks..


texts: DNS Alias for SQL Instance - LOTS of Instances - All going to default instance

texts:
We run an SQL server (2012 SP4) that has 40+ named instances (one for each client) and the default instance. We are thinking about replace the single server with multiple servers over the next few years. I'd like to setup a DNS alias for each of the instances so that we can make switch for the users more seamless (plus the instance names are long and gross and we get users complaining about them all the time).

We also have software the uses the full instance name (Server\Inst1) to connect, and i'd like to keep that functionality.

I was trying to implement DNS Aliases, the alias connects, but only connects to the default instance. Below is what i've setup so far, I'm not sure what is causing all of the aliases to connect only to the default instance but the normal connection (server1\inst1) works fine. It seems like it should work. What am i missing?

Server: Server1

Network Cards: 2
1. normal traffic and default instance (10.10.0.236)
2. SQL Alias connections w/ 40+ IP's all pingable; separate NIC so that "Register this connection address in DNS" can be unchecked and manually setup.

DNS: Host A records for all Aliases created. Verified that pinging the Alias resolves to the correct IP.

Instance: Server1\Inst1; DNS Alias: Inst1; IP for Inst1: 10.10.0.237;

SQL Server Config Manager:
Network Config for Default Instance:
SM/NP/TCP Enabled;
TCP: ListenAll=No;
entry for 10.10.0.236 TCP port 1433 - enabled;
Removed Port from IPAll;

Network Config for Inst1:
SM/NP/TCP Enabled;
TCP: ListenAll=yes; (to support normal Server\Inst1 connection)
Changed entry to 10.10.0.237 TCP port 1433 - enabled;
Left Port in IPAll;

Breakdown of the below query results for different types of connections:

    SELECT @@Servername, @@ServiceName
    SELECT * FROM sys.dm_exec_connections WHERE session_id = @@SPID

Alias INST1 - SQL Authentication

    @@Servername=Server1, @@ServiceName=MSSQLSERVER
    SQL Auth: Net_Transport=named pipe; Auth_Scheme=SQL

Alias INST1 - Windows Authentication

    @@Servername=Server1, @@ServiceName=MSSQLSERVER
    Net_Transport=named pipe; Auth_Scheme=NTLM

Server1\Inst1 - SQL Authentication

    @@Servername=Server1\Inst1, @@ServiceName=Inst1
    Net_Transport=TCP; Auth_Scheme=SQL

Server1\Inst1 - Windows Authentication

    @@Servername=Server1\Inst1, @@ServiceName=Inst1
    Net_Transport=TCP; Auth_Scheme=NTLM

.
.
.
EDIT (Solution):

There were two changes that i had to make to get this to work:

Disable IPAll and manually configure both 10.10.0.236 connection
Full Inst1 Network config now:
Protocol tab:
ListenAll=no;
IP Address tab:
CUT port from IPAll to IP(n) that had 10.10.0.236 IP; Enabled .236 IP; (to support normal Server\Inst1 connection)
Changed entry to 10.10.0.237 TCP port 1433 - enabled; (in my case i had to manually overwrite one of the unused IP(n) connections because .237 wasn't showing up)
After restarting the SQL Inst1 instance i had to restart the SQL Browser before it would actually update the port listeners and link .237:1433 with Inst1

Thank you David Browne - Microsoft & BradC for responding.


texts:
Windows PowerShell Desired State Configuration (DSC) newbie here. The MOF file generation fails with:

PSDesiredStateConfiguration\Node : The argument is null or empty. Provide an argument that is not null or empty, and then try the command again. At line:13 char:5
+     Node localhost
+     ~~~~
    + CategoryInfo          : MetadataError: (:) [PSDesiredStateConfiguration\node], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ArgumentIsNull,PSDesiredStateConfiguration\node   Compilation errors occurred while processing configuration 'SQLConfig'. Please review the errors reported in error stream and modify your configuration code appropriately. At C:\windows\system32\WindowsPowerShell\v1.0\Modules\PSDesiredStateConfiguration\PSDesiredStateConfiguration.psm1:3917 char:5
+     throw $ErrorRecord
+     ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (SQLConfig:String) [], InvalidOperationException
    + FullyQualifiedErrorId : FailToProcessConfiguration

Script

Configuration SQLConfig
{
    param(
        [Parameter(Mandatory =$true)][string[]]$serviceConfig,
        [Parameter(Mandatory =$true)][string]$DataDrive,
        [Parameter(Mandatory =$true)][string]$LogDrive

    )
    Import-DscResource -ModuleName SqlServerDsc
    Import-DscResource -ModuleName PSDesiredStateConfiguration

    Node localhost
    {
        WindowsFeature Net35
        {
            Name = 'NET-Framework-Core'
            Ensure = 'Present'
        }
        WindowsFeature Net45
        {
            Name = 'NET-Framework-45-Core'
            Ensure = 'Present'
        }
        WindowsFeature Cluster
        {
            Name = 'RSAT-Clustering'
            Ensure = 'Present'
        }

        File datadrive
        {
           Type = 'Directory'
           DestinationPath = $DataDrive
           Ensure ='Present'

        }
        File logdrive
        {
           Type = 'Directory'
           DestinationPath = $LogDrive
           Ensure ='Present'
        }
        SqlDatabaseDefaultLocation dataPath
        {
            InstanceName = 'MSSQLSERVER'
            Path = $DataDrive
            ServerName = 'localhost'
            Type = 'Data'
            DependsOn = '[File]datadrive'
        }
        SqlDatabaseDefaultLocation logPath
        {
            InstanceName = 'MSSQLSERVER'
            Path = $LogDrive
            ServerName = 'localhost'
            Type = 'Log'
            DependsOn = '[File]logdrive'
        }
        foreach ($service in $serviceConfig) {
            ServiceSet $service
            {
                Name = $service.ServiceName
                State = $service.State
                StartupType = $service.StartupType
                Ensure = $service.Ensure
            }

        }

    }
}

#region create configuration data

$serviceConfig=(
    @{ServiceName='MSSQLSERVER';State='Running';StartupType='Automatic';Ensure='Present'},
    @{ServiceName='SQLSERVERAGENT';State='Running';StartupType='Automatic';Ensure='Present'},
    @{ServiceName='SQLBrowser';State='Ignore';StartupType='Disabled';Ensure='Present'}
)
SQLConfig -serviceConfig $serviceConfig -DataDrive "F:\Data" -LogDrive "H:\Log" -OutputPath "C:\dump"


texts: PostgreSQL index array of int4range using GIN / GIST - custom operator class

texts:
Here is my table:

CREATE TABLE
        mytable
        (
                id      INT NOT NULL PRIMARY KEY,
                val     int4range[]
        );

I want to index the val column:

CREATE INDEX
    ix_mytable_val
ON      mytable
USING   GIN (INT4RANGE(val, '[]'));   // error, as is GIN(val)

I came up with the following:

CREATE OPERATOR CLASS gin_int4range_ops
DEFAULT FOR TYPE int4range[] USING gin AS
OPERATOR        1       <(anyrange,anyrange),
OPERATOR        2       <=(anyrange,anyrange),
OPERATOR        3       =(anyrange,anyrange),
OPERATOR        4       >=(anyrange,anyrange),
OPERATOR        5       >(anyrange,anyrange),
FUNCTION        1       lower(anyrange),
FUNCTION        2       upper(anyrange),
FUNCTION        3       isempty(anyrange),
FUNCTION        4       lower_inc(anyrange),
FUNCTION        5       upper_inc(anyrange);

But when I try to create the index, it fails (error below). However, if I call the create from within a DO $$ block, it executes.

If the create index executed, I get the error on INSERT INTO instead.

  "ERROR:  cache lookup failed for type 1"

I also tried this:

OPERATOR        1       &&(anyrange,anyrange),
OPERATOR        2       <@(anyrange,anyrange),
OPERATOR        3       @>(anyrange,anyrange),
OPERATOR        4       =(anyrange,anyrange),

In order to try and solve this, I have rebooted PG, the machine, and vacuumed the DB. I believe there is an error in the CREATE OPERATOR code.

If I can index an array of custom type of (int, int4range), that would be even better.

I've spent quite some time (a full day) wading through documentation, forums, etc., but can find nothing that really helps me to understand how to solve this (i.e. create a working custom operator class).


texts: SELECT count vs DELETE JOIN. Why/how are they different?

texts:
Can someone explain in what way these queries could be different and the results are not the same?

> begin;
Query OK, 0 rows affected (0.01 sec)

>select count(*) from tableA a JOIN tableB b ON (a.internal_id = b.internal_id) and a.customer_id=1001;
+----------+
| count(*) |
+----------+
|    23666 |
+----------+
1 row in set (0.20 sec)

>delete b.* from tableA a JOIN tableB b ON (a.internal_id = b.internal_id) and a.customer_id=1001;
Query OK, 23649 rows affected (0.74 sec)

Can someone please explain why the delete shows a different number of rows than the count? I am using the same join. Are they not equivalent?


texts:
I want to select and count all cats and dogs at one location;

location | cats | dogs |
new york | 150  | 66   |
paris    | 34   | 12   |

What I've got so far:

SELECT
    cats,dogs
FROM
    (select k.location, count(*) as cats from pet k, location lo where k.location= lo.id and k.pet= 'Cat' group by k.location)
    union
    (select k.location, count(*) as dogs from pet k, location lo where k.location= lo.id and k.pet= 'Dog' group by k.location)

But I only get one column for location and cats...


texts: Lookup data while in mysql cli edit mode (external editor) (\e)

texts:
I'm using the mysql cli in edit mode (\e).

> Select * from \e

Goes to vim and I finish typing my command.

Select * from test_table;

Only to realize I've forgotten to use the database I need.  Is there a way I can preserve this script in edit mode, switch back to the regular mysql mode, run my command, and then come back to edit?

Another example would be: I forget how a column name is spelled and I need to look it up after I've started typing a command in edit mode.

Thanks!


texts: How to loop through AS400 table with cursor

texts:
I have a AS400 table that contains multiple rows per person. Each row contains data concerning a transaction by the person including points assigned to each transaction. I read through all rows for one person ordered by the points. The first row with the highest points, I leave alone. On subsequent rows the point values are cut in half and I do an update. I am currently using a cursor to open the table and doing a Loop to read through the rows. I am getting an -508 error stating :

  An UPDATE or DELETE statement with a WHERE CURRENT OF CL was attempted, but the cursor is not positioned on a row or is positioned on a row, but the row is not locked because a COMMIT HOLD or ROLLBACK HOLD statement released the lock on the row. A FETCH statement must be issued to position the cursor on a row and lock the row. Recovery  . . . :   Issue a FETCH statement to position the cursor on a row and lock the row; then, try the request again.

Part of my code is below:

DECLARE V_LNAME CHAR ( 30 ) ;
DECLARE V_LNAMEHOLD CHAR ( 30 ) ;
DECLARE V_FNAME CHAR ( 15 ) ;
DECLARE V_FNAMEHOLD CHAR ( 15 ) ;
DECLARE V_DOB DATE ;
DECLARE V_DOBHOLD DATE ;
DECLARE V_TRANSNMBR CHAR ( 9 ) ;
DECLARE V_TRANSNMBRHOLD CHAR ( 9 ) ;
DECLARE V_POINTS NUMERIC ( 5 ) ;
DECLARE V_POINTSHOLD NUMERIC ( 5 ) ;
DECLARE V_POINTSEQ NUMERIC ( 5 ) ;
DECLARE FIRSTRECORD CHAR ( 1 ) ;
DECLARE CL CURSOR FOR
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS
FROM DB_TRANSDATA
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ;
DECLARE CLHLD CURSOR FOR
SELECT LNAME , FNAME , DOB , TRANSNCNMBR , TOPOINTS
FROM DB_TRANSDATA
ORDER BY LNAME ASC , FNAME ASC , DOB ASC , TOPOINTS DESC ;

OPEN CLHLD ;
FETCH CLHLD INTO V_LNAMEHOLD , V_FNAMEHOLD , V_DOBHOLD , V_TRANSNMBRHOLD ;
close clhld;

OPEN CL ;
SET FIRSTRECORD = 'Y' ;
LOOP
FETCH CL INTO V_LNAME , V_FNAME , V_DOB , V_TRANSNMBR , V_POINTS , V_POINTSEQ ;
IF TRIM ( CHAR ( V_LNAME ) ) = TRIM ( CHAR ( V_LNAMEHOLD ) ) AND TRIM ( CHAR ( V_FNAME ) ) = TRIM ( CHAR ( V_FNAMEHOLD ) ) AND V_DOB = V_DOBHOLD AND V_TRANSNMBR = V_TRANSNMBRHOLD AND FIRSTRECORD = 'N' THEN
SET V_POINTSEQ = V_POINTS * .5 ;

UPDATE DB_TRANSDATA
SET POINTSEQ = V_POINTSEQ
WHERE CURRENT OF CL ;

SET V_LNAMEHOLD = V_LNAME ;
SET V_FNAMEHOLD = V_FNAME ;
SET V_DOBHOLD = V_DOB ;
SET V_TRANSNMBRHOLD = V_TRANSNMBR ;
ELSE

UPDATE DB_TRANSDATA
SET POINTSEQ = V_POINTS
WHERE CURRENT OF CL ;

SET V_LNAMEHOLD = V_LNAME ;
SET V_FNAMEHOLD = V_FNAME ;
SET V_DOBHOLD = V_DOB ;
SET V_TRANSNMBRHOLD = V_TRANSNMBR ;
SET FIRSTRECORD = 'N' ;
END IF ;

END LOOP ;

CLOSE CL;

END  ;


texts: Best approach to maintain a clone of a db in Postgres RDS

texts:
We'd like to maintain a separate database for our dev environment that closely resembles the current state of our production database. What is the best way to do this in RDS? Our current process is to restore the nightly snapshot of our production db and make this our dev db. The problem with this approach is that it takes ~18hrs for all the changes (e.g. going from a multi AZ instance to a single AZ instance) to take effect, and we get charged on-demand rates while transitioning. So we only get to take advantage of the reservation for the dev db for 25% of the day.

Is there a better approach to maintaining a copy of a production db in RDS? I know they have read-replicas available now, but that is not sufficient for us.

Thanks in advance!


texts: SQL Server 2019 - Polybase -> Cosmos DB (MongoDB Mode) Error with dates

texts:
I'm new to polybase. I have linked my SQL Serer 2019 to a third parties Azure cosmos and i am able to query data out of my collection. I am getting an error out when i try to query date fields though. In the documents the dates are defined as:
"created" : {
    "$date" : 1579540834768
},

In my external table i have the column defined as
[created] DATE,

I have tried to create the column as int and nvarchar(128) but the schema detection rejects it each time. (I have tried to create a field created_date but the schema detection also disagrees that this is correct.
When I try a query that returns any of the date fields I get this error:
Msg 105082, Level 16, State 1, Line 8
105082;Generic ODBC error: [Microsoft][Support] (40460) Fractional data truncated while performing conversion. .
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Unspecified error".
Msg 7421, Level 16, State 2, Line 8
Cannot fetch the rowset from OLE DB provider "MSOLEDBSQL" for linked server "(null)". .

This happens if i try and exclude null values in my query - even when filtering to specific records where the date is populated (validated using the Azure portal interface)
Is there something i should be doing to handle the integer date from the json records; or another type i can use to get my external table to work?


texts: How to rollback when 3 stored procedures are started from one stored procedure

texts:
I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful.

If the first stored procedure works fine in the master stored procedure, but the 2nd stored procedure fails, then will it automatically roll back all the SP's in the master SP or do I have to make some command?

Here is my procedure:

CREATE PROCEDURE [dbo].[spSavesomename]
    -- Add the parameters for the stored procedure here

    @successful bit = null output
AS
BEGIN
begin transaction createSavebillinginvoice
    begin Try
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   BEGIN

   EXEC [dbo].[spNewBilling1]

   END

   BEGIN

   EXEC [dbo].[spNewBilling2]

   END

   BEGIN

   EXEC [dbo].[spNewBilling3]

   END

   set @successful  = 1

   end Try

    begin Catch
        rollback transaction createSavesomename
        insert into dbo.tblErrorMessage(spName, errorMessage, systemDate)
             values ('spSavesomename', ERROR_MESSAGE(), getdate())

        return
    end Catch
commit transaction createSavesomename
return
END

GO


texts: Query to Linked Server Ignores Configured Timeout Connection

texts:
I need to know where to set the timeout for a login or a query to a linked server. If the server is down I don't want to waste too much time waiting for a timeout. (Our servers are on the same network with a gigabit connection. All are running SQL Server 2017.)
For example,
SELECT * FROM SERVER_A.master.sys.databases
runs instantaneously if the linked server is up. If the linked server is down, e.g. if I stop the SQL Server service on SERVER_A, it takes 21 seconds to timeout with the following message:

OLE DB provider "SQLNCLI11" for linked server "SERVER_A" returned
message "Login timeout expired".
OLE DB provider "SQLNCLI11" for
linked server "SERVER_A" returned message "A network-related or
instance-specific error has occurred while establishing a connection
to SQL Server. Server is not found or not accessible. Check if
instance name is correct and if SQL Server is configured to allow
remote connections. For more information see SQL Server Books
Online.".
Msg 2, Level 16, State 1, Line 2
Named Pipes Provider: Could not open a connection to SQL Server [2].

EXEC sp_testlinkedserver SERVER_A times out in the same 21 seconds with the same message.
I can't figure out where the timeout is set. Here is what I've tried:

Linked Server Properties for SERVER_A\Advanced, I set the Connection Timeout to 1.
Linked Server Properties for SERVER_A\Advanced, I set the Query Timeout to 1.
Local server Properites\Advanced, I set Remote Login Timeout to 1. (Same as EXEC sp_configure 'remote login timeout', 1)
Local server Properites\Connections, I set Remote query timeout to 10.(Same as EXEC sp_configure 'remote query timeout', 10)
I looked through all of the settings in sys.configurations and couldn't find any that are anywhere near 21.

Regardless of what settings I change, the timeout still takes 21 seconds and returns the above error message.
What am I missing?

UPDATE:
I tried setting the linked server connection timeout to 43. After that, it took 63 seconds to timeout. We're getting somewhere! Setting it to 30 or 33 times out in 42 seconds, etc. Something is adding either 12 or 20 seconds to the login timeout.
After more testing, it appears that the timeouts I'm experiencing are in multiples of 21 seconds. It's taking 21, 42 or 63 seconds to timeout. It takes longer as I increase the Connection Timeout past a multiple of 21. e.g. if I set the Connection Timeout to 21, it takes 42. If I set it to 50, it takes 63.


texts: MADlib apply `cosine_similarity` on term frequency

texts:
I am working on a small topic modelling project and I have stumbled upon the idea of developing it in MADlib and PostgreSQL.

MADlib provides this term frequency function that converts my documents into, well term frequencies.

Are there any other utility functions that can help me to perform madlib.cosine_similarity or madlib.dot_product
 on the generated term frequencies which is essentially stored as sparse matrices (which is not a supported format for the cosine_similarity function that I needed)?


texts: Prevent users from disabling FK checks in InnoDB

texts:
In MySQL, it is possible to disable FK checks using something like the following:

SET FOREIGN_KEY_CHECKS=0;

Is there a way to prevent this, i. e. preventing users from disabling foreign keys?  I don't ever want to allow that or do it myself.


texts: SQL Server 2019: Rasing errors between 1 and 13000

texts:
I've been struggling with a task which requires me to raise a number of errors for testing purposes. Of course the time consuming errors are between 1 and 13000. I've managed to do them, but I need to do it several times on multiple test instances and I wanted to ask if there's a way to raise SQL Engine errors which normally only could be raised by the engine itself. By 'a way', I mean any possible routes to generate those error messages.
The ultimate goal is that the messages appear in the current error log.
Any help would be appreciated!


texts: How much does a query of deletion costs in terms of operations when the attribute is a key or an attribute?

texts:
How much does the following query costs in terms of operations when the attribute is a key or an attribute ?

DELETE
  FROM R
    WHERE ATTRIBUTE = 50

We assume we have R(K,A,B,other) a relation with

Nrec(R)=100000,
Lr=100 byte I think the size of these records...
K, a key in    [1;100000]

A with integer values uniformly distributed in the range (1,1000)

R is stored with heap organization with data unsorted with both respect to K and A, in pages of size Dpag=1024 bytes each.

I have an array giving me the following costs for heap organizations (but actually for deletion it is Cs+1).

According to my teacher the number of deleted records for an attribute operation is Nrec(R)/Lr = 100000/100 = 100.
But I don't understand why.

Therfore the cost of the operation would be :
Npage+100.


texts: MySQL consistent nonlocking reads vs. INSERT ... SELECT

texts:
So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case:

With InnoDB tables, a simple SELECT statement (one that does not use FOR UPDATE or LOCK IN SHARE MODE) will not grab any row locks on the tables that the SELECT statement reads.  This is true at all transaction isolation levels.
However, if your transaction isolation level is REPEATABLE READ or higher, an INSERT ... SELECT or CREATE TABLE AS SELECT statement will place row locks on tables that it reads from.

Sources:

http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html

If I understand this correctly (and correct me if I don't), then I'm puzzled by this difference.  Why does reading a table require lock rows in the one case but not the other, when the transaction isolation level is the same?  I'd like to understand the reason for this.


texts: What problem prevents well defined behavior of a foreign key on a non-unique column?

texts:
In my noob opinion, it strikes me that in a serialized environment, this shouldn't be a problem.

Is that correct?  If not, please explain to me why a foreign key's behavior on a non-unique column is not well defined.

Does behavior not well defined include that if a foreign key's referenced column is not unique then it will delete the keyed row even though there are valid entries?


texts: Imports & exports under storage constraints and preallocations

texts:
I have a 5GB oracle database that I want to export and then import on my own local database. Easy right?

The point is, I use oracle XE 10g which allows me to use up to 4GB. I then decided to make the export with the option "rows=N" and I would then fill my empty DB with what I need.

My export without any row is 1MB.

I created my user, granted privileges, created a 1GB tablespace and imported my 1MB file.

The problem is that the tables seem to have some pre-allocated space which means that my tablespace is still too small... 1GB space is too small for a 1MB dump, what a joke :/

I can't truncate the database, and I haven't found anything about turning off preallocation during either export or import.

How can I get this done?


texts:
I need to create 90 databases. Something like this. Do you have any idea?

My script:

declare @db_name nvarchar(100)
set @db_name = 'name';

CREATE DATABASE @db_name
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'@db_name', FILENAME = N'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\@db_name.mdf' , SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'@db_name_log', FILENAME = N'L:\MSSQL11.MSSQLSERVER\MSSQL\Log\@db_name_log.ldf' , SIZE = 512KB , FILEGROWTH = 256KB )
GO
ALTER DATABASE @db_name SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE @db_name SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE @db_name SET ANSI_NULLS OFF
GO
ALTER DATABASE @db_name SET ANSI_PADDING OFF
GO
ALTER DATABASE @db_name SET ANSI_WARNINGS OFF
GO
ALTER DATABASE @db_name SET ARITHABORT OFF
GO
ALTER DATABASE @db_name SET AUTO_CLOSE OFF
GO
ALTER DATABASE @db_name SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE @db_name SET AUTO_SHRINK OFF
GO
ALTER DATABASE @db_name SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE @db_name SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE @db_name SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE @db_name SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE @db_name SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE @db_name SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE @db_name SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE @db_name SET  DISABLE_BROKER
GO
ALTER DATABASE @db_name SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE @db_name SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE @db_name SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE @db_name SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE @db_name SET  READ_WRITE
GO
ALTER DATABASE @db_name SET RECOVERY FULL
GO
ALTER DATABASE @db_name SET  MULTI_USER
GO
ALTER DATABASE @db_name SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE @db_name SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE @db_name
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE @db_name MODIFY FILEGROUP [PRIMARY] DEFAULT
GO


texts: Does creating / updating statistics have an impact on the transaction log?

texts:
Does creating or updating statistics, assuming on a large table, create significant (or at all) transaction log churn? I would think not as it does not change the underlying data or structure.

If it does, what would be a good method to verify?


texts: How to move database from Db2/Linux/System-z to Db2/Linux/Intel

texts:
our company has decided to migrate database:

from source Db2 Enterprise Server Edition v11.1 fixpack 5 on Linux/System-z (this is still Linux/Unix/Windows type of database despite running on System-z mainfraime)
to Db2 Enterprise Edition v11.1 fixpack 5 on x86_64 (Intel/AMD).

Linux/System-z is big endian system and Linux/x86_64 is little endian system. There is no backup/restore option between bin/little endian Linux system.
The other option that I have read a lot about is combination of: db2look/db2move and a plenty of manual tasks. This seems pretty dangerous operation, because one little mistake and something may go terrible wrong when applications start using database on target system.
On PostgreSQL system there is build-in pg_dump tool. With this tool one single command dumps whole database (all objects: create database, create table, triggers, foreign keys, primary keys, etc including all of the data) into one single huge file, that can be easily transferred   to target system and then one single command is executed on target system to get the database up and running. There are no manual interventions, tricks, procedures etc. Just two commands, one on source and one on target system. Is there something similar on Db2?
Is there any other way to move database between two Linux/endian systems except db2look/db2move? Some sophisticated tool or something?
Regards


texts: High cost on my query on partitioned table

texts:
I have a partitioned table as follows:

create table daily_summary
(
    id                 uuid           default uuid_generate_v4() not null
        constraint daily_summary_partition_pkey
            primary key,
    person_licence     varchar(25),
    fuel_quantity      numeric(10, 3),
    start_date         timestamp                                 not null,
    end_date           timestamp,
    person_enabled     boolean                                   not null,
    start_location     text,
    end_location       text,
    organisation_licence       uuid,
    active             boolean        default false
);

create table daily_summary_201912
(
    organisation_licence uuid,
    active       boolean default false,
    constraint daily_summary_201912_pkey
        primary key (id),
    constraint fk_daily_summary_machine_licence_201912
        foreign key (machine_licence) references machine_has_licence,
    constraint daily_summary_201912_start_date_check
        check ((start_date >= '2019-12-01'::date) AND (start_date < '2020-01-01'::date))
)
    inherits (daily_summary);

create index idx_daily_summary_organisation_licence_start_date_201912
    on daily_summary_201912 (organisation_licence, start_date) WHERE active = true;

There are other partitions as well, from 2013-01-01 to future date.

On the above table, I've a simple query:

select *
from daily_summary
where daily_summary.organisation_licence = '03312415-585d-42fd-b185-b0ab91dabf35'
  and daily_summary.active
  and daily_summary.start_date >= timestamp '2019-12-01 '
  and daily_summary.start_date < timestamp '2020-01-01';

The data in each partition is big, in the order of GBs. When I do explain analyze on my query:

Append  (cost=0.00..80822.64 rows=112091 width=322) (actual time=17.877..145.944 rows=113896 loops=1)
  ->  Seq Scan on daily_summary  (cost=0.00..0.00 rows=1 width=684) (actual time=0.002..0.002 rows=0 loops=1)
        Filter: (active AND (start_date >= '2019-12-01 00:00:00'::timestamp without time zone) AND (start_date < '2020-01-01 00:00:00'::timestamp without time zone) AND (organisation_licence = '03312415-585d-42fd-b185-b0ab91dabf35'::uuid))
  ->  Bitmap Heap Scan on daily_summary_201912  (cost=2601.29..80822.64 rows=112090 width=322) (actual time=17.874..104.199 rows=113896 loops=1)
        Recheck Cond: ((organisation_licence = '03312415-585d-42fd-b185-b0ab91dabf35'::uuid) AND active)
        Filter: ((start_date >= '2019-12-01 00:00:00'::timestamp without time zone) AND (start_date < '2020-01-01 00:00:00'::timestamp without time zone))
        Heap Blocks: exact=36057
        ->  Bitmap Index Scan on idx_daily_summary_organisation_licence_start_date_201912  (cost=0.00..2573.27 rows=112112 width=0) (actual time=11.619..11.619 rows=113896 loops=1)
              Index Cond: (organisation_licence = '03312415-585d-42fd-b185-b0ab91dabf35'::uuid)
Planning time: 5.927 ms
Execution time: 167.103 ms

I'm unable to understand the reason why there's a Bitmap Heap scan on the entire table, which is costing a lot, especially when I query across an entire year, the cost is going really high (80822.64x12)! Can anyone help me understand this and also help me optimize my query?


texts: PostgreSQL: duplicates despite having a unique constraint

texts:
We have a table in the production DB that looks like this:

create table domains
(
 domain_id serial not null
  constraint domains_pkey
   primary key,
 domain_name text not null
  constraint domains_domain_name_key
   unique
)

For some reason, we've got duplicates in that table, even though, in theory, this is impossible.

Here's a query that I use to find these duplicates:

SELECT md5(t1.domain_name), md5(t2.domain_name)
  FROM domains t1
  JOIN domains t2
    ON t1.domain_name = t2.domain_name
 WHERE t1.domain_id != t2.domain_id;

It returns 50 records, and there are over 7M total records in the table.

I understand how to get rid of them. However, I would really like to know how to prevent new duplicates and why this happened?

PostgreSQL version: PostgreSQL 11.7 (Debian 11.7-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit


texts: Chunking data into a PostgreSQL bytea column?

texts:
I have column of type bytea in PostgreSQL database.

If I update a row, is it possible to chunk the data into the bytea column in several small steps?

The size of one bytea entry will be mostly below 200MByte.

The chunks will be about 1MByte.

I use psycopg2 and Python to access the db.

This is a follow-up question of Store HTTP response in PostgreSQL


texts:
When a full-backup is restored by the DBA, does the DBA need to restore permissions, users, and roles separately? Or does it all come with the full restore?

I am from the dev team. We are migrating from SQL Server 2008 to 2014. We asked the DBA to restore a full backup.

They have restored the full backup to the new server, but I can see discrepancies in users, permissions, and roles between the old and new servers.

Why it is happening?


texts: How to capture Query Status(Sleeping, Background, Running) in SQL Server Profile trace

texts:
Is there any EventClass/Data Column which can log the Query Status to trace in SQL Server Profiler?

Query Status which we find through sys.dm_exec_requests


texts:
I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type:

SELECT t1.id, t2.stuff
FROM
              someTable    t1
   INNER JOIN otherTable   t2
      ON t1.id=t2.id
;

But... Why is this acceptable in stored procedures and such? It seems like all it does is harm the readability of the statement while saving an extremely minor amount of time. Is there any functional or logical reason to do this? It seems to add ambiguity rather than remove it; the only acceptable reason I can see for using this format is if you were adding a semantically meaningful alias -- for example, FROM someTable idsTable -- when the table name isn't descriptive enough.

Is table aliasing a bad practice or is this just a misuse of a helpful system?


texts: Microsoft Key Storage Provider for Always Encrypted Master Key

texts:
Hoping someone can help-
I've been experimenting with Always Encrypted columns and have generated a certificate in the "Microsoft Software Key Storage Provider" -

How can I remove the "Always-Encrypted-Auto1" entry?
using SQL Express 2017 with SQL Management Studio 17.
Have not found an answer to this question via conventional search engine means or via dba.stackexchange.com.
Cheers!


texts: Avoid using complicated strings in Joins in SQL Server?

texts:
I will create a couple of tables in SQL Server for a client. Of special interest for this question is the FactStoreSale table to be created and DimProduct in which the first table will include all store's sales and include for instance StoreKey, DateKey, TimeKey, ProductKey and sales data.

The DimProduct table will use complementary data in regards to Products, i.e. ProductName, ProductGroup, ProductCategory etc. This table will be a slowly changing dimension, and store Product history in terms of price etc. for each store. The table will include the columns FromDate and ToDate which describes when the data was entered, and when it was replaced (with default value year 2999 in ToDate until a new update of the Product is inserted) respectively.

I Believe this is very common. However, the ProductKeys from the underlying database are very complex strings, such as 140-xx4449CH-4.44,9.

My alternatives as I see it, is to use Checksum in SQL Server to convert the complex strings to integers, and store these in a separate column ProductKeyInt
and use these columns when doing JOINS. But checksum does not guarantee unique integer values, which would likely cause problems. When I tried Checksum on a Virtual PC and my own PC, doing Checksum  on the same values returned different results which is a concern as well. I Believe these two together rules out Checksum, unless it can be manipulated somehow to make it more unique?

Another  alternative is to use more complex functions to ensure that the string values are converted into integers, such as the one provided on a  question of mine here. A solution such as this however also has issues, the values 100-xx3 and 1003 will for instance get the same result. It is also complicated in the sense that if someone who isn't that savvy in SQL need to try to find any issues with the Query that uses the function, it may be very hard to understand what is happening.

My third option seem to be to use the DimProduct table and update this table first from the SQL Agent, and using an Indexing Key on this table and use this index as the  ProductKeyInt in the FactStoreSale table (where ProductKeyInt will be some kind of subquery in for instance FactStoreSale to fetch the value with the biggest ToDate in DimProduct corresponding to the ProductKey.

Does anyone have any input? Is there a simpler way? I don't want to have strings as JOIN keys because of for example increased CPU time


texts: Effective way to migrate from SQL Server to MySQL

texts:
I have a 20GB Microsoft SQL Server database that needs to be migrated to MySQL.

Right now I only have the schema for the source db. I tried the migration wizard on the schema in MySQL Workbench and it worked out fine (with some minor varchar issues that was easily solved)

As I am waiting for the data I am wondering if MySQL Workbench is the way to go here as the source is 20GB.

Can the Workbench handle it? Is it effective?
Should I investigate other tools than the Workbench?

Any advice and pointers is appreciated.


texts: path on ASM for standby database after switchover

texts:
The question is quite complicated for me to describe so please be patient and ask for  more details of needed.
I have a primary database (ABC) and a standby one (STBABC). Both are located on ASM. For both primary and standby there are several parameter in spfile (control_files, db_create_file_dest, db_recovery_file_dest  etc. ) to point where files has to be located. For ASM databases value is always something like +DATA or +ARCH  without full path.  Instance creates automaticaly full path which is more or less something like : +DATA/<DB_UNIQUE_NAME>/ARCHIVELOG  or +ARCH/<DB_UNIQUE_NAME>/ARCHIVELOG  and so on.
Every thing is ok until .... I switchover databases .  Then name of ex standby (which is now primary)is ABC but all files are still located in STBABC directory.  Is there any easy way to replace old directory name ( STBABC)  to ABC ?
regards
K.


texts: How to import export.sql in sql developer?

texts:
In SQL Developer, from the top menu choose Tools > Data Export. This launches the Data Export wizard.

So that is export. It creatres export.sql file. Now how to do import ? I am usgin SqlDeveloper 3.20.
Trygin to follow this tutorial , and have created new connection but I cannot find import.
Tools->Preferences->Database->Utilities->Import Method and than what ??
I have tried to  run script @C:\locationOfExportFile\export.sql
And I am getting eroors

SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:

BTW, I have no idea what am I doing i just want to get that database to localhost. Other computer and my computer both have Oracle 11g Express database. Schema name is the same!


texts: How to change the create and use statement in a large sql file without opening it

texts:
I need a way to change the schema name which appears in create statement and use statement of mysql dump file. The goal is having the database restored with a different database name on another server. The file is a few gigs in size so opening an editor doesn't work.

How do the experts deal with that?

I also need a way to see the changes before I upload the Mysql to a new server to validate it.

Thanks !