texts: How to setup solution for multi-dimensional distance search with SQL Server spatial data types

texts:
I have an existing database solution that is not performing very well. I am searching on multi-dimensional data with a traditional table structure using floats, etc. The database contains ~ 1-2M rows.

After some searching I came across the SQL Server spatial data types where you can define a geometry based on Point or MultiPoint where you can search with a distance. I think this might be a solution but I need some help to get started in the right direction.

My current table structure is as follows where each row contains 2 (eventually more) XYZ values:

    | ID | X1  | Y1  | Z1  | X2  | Y2  | Z2  |
    | 1  | 1.1 | 2.2 | 5.1 | 1.2 | 2.1 | 4.1 |
    | 2  | 3.2 | 5.1 | 4.1 | 3.2 | 3.1 | 3.1 |
    | 3  | 4.1 | 2.3 | 6.3 | 4.2 | 4.1 | 2.1 |
    | 4  | 2.4 | 3.5 | 2.1 | 3.2 | 2.1 | 4.1 |

Im performing search with a tolerance for each X, Y and Z value.

So for example:

    (pseudocode)
    declare targetXYZ1 = (4.1, 2.2, 3.1);
    declare targetXYZ2 = (2.8, 2.2, 4.2);

    declare toleranceXYZ = (2,2,2)

    Select ID from MyXYZTable tb
    Where
           Math.Abs(tb.X1 - targetXYZ1.X1) < toleranceXYZ.X
       AND Math.Abs(tb.Y1 - targetXYZ1.Y1) < toleranceXYZ.Y
       AND Math.Abs(tb.Z1 - targetXYZ1.Z1) < toleranceXYZ.Z

       AND Math.Abs(tb.X2 - targetXYZ1.X2) < toleranceXYZ.X
       AND Math.Abs(tb.Y2 - targetXYZ1.Y2) < toleranceXYZ.Y
       AND Math.Abs(tb.Z2 - targetXYZ1.Z2) < toleranceXYZ.Z

After some analysis I would probably need to make a table with 1 or 2 geometry columns for xyz1 and xyz2 with geometry::Point(0, 0, 0)

My questions:

Is there a solution possible with SQL Server spatial types?
Do I need to use nearest neighbor search or can I use STDistance
Do I need to setup a spatial index for the table?

Any advice or tips and tricks are very welcome!


texts: For Postgres, how would I define a custom function to generate primary keys?

texts:
In postgres, primary keys are typically generated using a SEQUENCE. I am hoping to generate a unique random 12 character string for use as a primary key.
How would I do this in Postgres?


texts:
Here is my exercise:

  Given database of the website "eMovies" that manage data about cinema movies. build the given database in Access and create the relationships between the tables.

My attempt:

My questions:

I am not sure at all about the relationships that I made between the tables at all.
I am not sure about the keys

EDIT: After asking my doctor the table and the keys are correct


texts: Why does mysql allow me to enter password for non existing mysql user

texts:
My mysql server has 2 users -

root
robb

But when I try to login with another user it let's me enter password -

hussain@magic-laptop:~$ mysql -u jon -p
Enter password:

Shouldn't it give an error saying user: jon donesn't exist?


texts: Cannot Utilize Maximum CPU and Memory Usage for MySQL

texts:
Good day.

I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem.

I have a single stored procedure working with max 3 tables. when I run the procedure, only 30% of my CPU is used and about 25% of RAM.

I am sitting with a CPU with 4 cores and 16GB RAM.

my.ini looks as follows:

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 32M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 32M
thread_concurrency = 0

log-bin=mysql-bin

binlog_format=mixed

server-id   = 1

innodb_buffer_pool_size = 12G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_read_io_threads = 64
innodb_write_io_threads = 64

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Is it the nature of the procedure called which is causing mysql to under utiize the hardware or is it my configuration?

I was running XAMPP but then realised it was 32-bit so I switched to the 64-bit version of WAMP. I use a 32-bit MySQLWorkbench to run queries.

I am using the InnoDB engine.

Using MySQL Ver 14.14 Distrib 5.5.24 Win64 (x86).


texts: oracle failsafe - how to start instance in nomount

texts:
Using Oracle Failsafe, how can a instance be started up in nomount mode?

If the instance is shutdown via sqlplus, the windows cluster manager just starts it up again. If the instance is shutdown via windows cluster manager, I'm unable to connect to it via sqlplus.


texts: How to Get the First in and Last out in night shift?

texts:
I already have the Query for Getting the Day shift but I'm Having a problem in the night shift, Which is this query.

SELECT user_id,
Min(verify_date) as First_IN,
Max(verify_date) as Last_Out
from tbl_attendance group by user_id,verify_date

But this query doesn't give the night shift so i tried another Query Which is:

SELECT user_id,verify_date,
Lead(verify_date,-1) OVER(PARTITION BY user_id order by verify_date desc)
from tbl_attendance

This Query Give's The past attendance but i don't know how do i get rid the extra attendance in my Biometrics (Or the Double tap in Biometrics) the Biometric is used as the door so i need to get the first in and last out.

And also I'm having a problem with my SQL Fiddle The Date Time is maybe broke it alway's give me this output 2019-10-17T07:55:00Z.
See this DB Fiddle

Desired Output :

User_id     in_time             out_time
71      17/10/2019 7:55     null
13      16/10/2019 18:44    17/10/2019 7:05
8       16/10/2019 19:47    17/10/2019 7:05
70      16/10/2019 18:25    17/10/2019 7:01
31      16/10/2019 19:02    17/10/2019 6:34
66      17/10/2019 6:19     null
64      16/10/2019 11:49    16/10/2019 22:05
36      16/10/2019 7:42     16/10/2019 19:03
34      16/10/2019 6:26     16/10/2019 19:03
79      16/10/2019 9:25     16/10/2019 19:02
76      16/10/2019 7:02     16/10/2019 19:02
13      16/10/2019 7:05     16/10/2019 18:44
71      16/10/2019 7:02     null


texts: How to display rows containing special characters in a certain column

texts:
I have a table with the column f_Name. The column f_name sometimes contains special characters and numbers as in the examples below:

f_name
-----------
Pan23's
Ram'kumar
lawer"s()

I want to retrieve all the values of f_name which have a special character or number.


texts: Availability group listener is not listening

texts:
I've successfully created an availability group with 3 nodes. I've tried to create a listener, separately trying port 1433 and 1435, but the listener is not even trying to listen - there's no entry in sys.dm_tcp_listener_states, and nothing in netstat -abn. I don't know how to begin debugging this.

SELECT * FROM sys.dm_tcp_listener_states:

listener_id  ip_address  is_ipv4  port  type  type_desc           state  state_desc  start_time
5            ::          0        4607  2     DATABASE_MIRRORING  0      ONLINE      2018-06-18 14:35:16.220
6            0.0.0.0     1        4607  2     DATABASE_MIRRORING  0      ONLINE      2018-06-18 14:35:16.220
3            ::1         0        1434  0     TSQL                0      ONLINE      2018-06-18 14:35:15.533
4            127.0.0.1   1        1434  0     TSQL                0      ONLINE      2018-06-18 14:35:15.533
1            ::          0        1433  0     TSQL                0      ONLINE      2018-06-18 14:35:15.533
2            0.0.0.0     1        1433  0     TSQL                0      ONLINE      2018-06-18 14:35:15.533

netstat -abn | findstr "143":

TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
TCP    127.0.0.1:1434         0.0.0.0:0              LISTENING
TCP    [::]:1433              [::]:0                 LISTENING
TCP    [::1]:1434             [::]:0                 LISTENING


texts: select only one element from an priority ordered group with limits?

texts:
This question searches for an answer in pure sql (no db-server specific code)

-edit-
Due comments/answers:
"pure sql" - SQL92/99 because this works on all servers. I do not only use this code on mysql, this was just the first one I tried. I have to support Microsoft SQL Server (2000!), h2, PostGRE SQL and MySQL Server but the code should work on others too (db server independent). I'm sorry if "pure sql" was not detailed enough. Next time I'll specify requirement of the revision of SQL (3/4, 92/99)

I have to be backwards compatible.
And this is why I did not specify a db server. This was on purpose.
-end-of-edit-

I try to get all values from a table with given limits.
The data i have for the query is: possible locales and a max_priority

this is the table to work with

SELECT * FROM i18n

.

id|featureName|locale     |priority|text
--+-----------+-----------+--------+----
 1| type      |         en|       1|father
 2| type      |         de|       2|Vater
 3| type      |      de_AT|       3|Papa
 4| type      | de_AT_Wien|       4|Oida
 5| firstName |         en|       1|first name
 6| firstName |         de|       2|Vorname
 7| lastName  |         en|       1|last name
 8| lastName  |         de|       2|Nachname
 9| lastName  |         fr|       2|nom de famille
10| firstName |         fr|       2|prénom

so for example i have en, de, de_AT and a max_priority of 3 (which is the priority of de_AT)
i expect this as result: (the order of these results are not important!)

id|featureName|locale     |priority|text
--+-----------+-----------+--------+----
 3| type      |      de_AT|       3|Papa
 6| firstName |         de|       2|Vorname
 8| lastName  |         de|       2|Nachname

i tried to solve this with an left outer join
this is the best result i was able to get:

SELECT DISTINCT nls1.*
FROM i18n nls1
LEFT OUTER JOIN i18n nls2
ON (
    nls1.featureName = nls2.featureName
    and nls1.priority > nls2.priority
)
WHERE nls2.priority<=3
AND (nls1.locale='en' OR nls1.locale='de' OR nls1.locale='de_AT')
GROUP BY featureName
ORDER BY featureName, priority DESC

but this selects those rows:

id|featureName|locale     |priority|text
--+-----------+-----------+--------+----
 2| type      |         de|       2|Vater
 6| firstName |         de|       2|Vorname
 8| lastName  |         de|       2|Nachname

another try was

SELECT nls2.*
FROM   i18n nls2
       JOIN (SELECT Max(priority) max_priority,
                    featurename,
                    priority
             FROM   i18n nls
             WHERE  ( locale = 'en'
                       OR locale = 'de'
                       OR locale = 'de_AT' )
                    AND priority <= 3
             GROUP  BY featurename) nls3
         ON nls3.priority = nls2.priority
            AND nls3.featurename = nls2.featurename

but this one only returns the en version...

id|featureName|locale     |priority|text
--+-----------+-----------+--------+----
 1| type      |         en|       1|father
 5| firstName |         en|       1|first name
 7| lastName  |         en|       1|last name

NOTE: i'll update the question depending on the answers.

The main problem seems that i need a group by an column but the group also needs a order by?


texts: Help structuring a query with multiple many-to-many relationships

texts:
Assume that there are 4 tables, with association tables "between" each of them:

Machines
Products
Option Groups
Options

Machines may serve many products, and a product may "belong to" many machines. Products have many Option Groups and an option group can belong to multiple products. The same relationship exists between Option Groups and Options.

Ideally, I'd like to return all of the data for a "menu" (there is not a menus table - that's just how I'm referring to a group of products that belong to a machine) in a single call, but I'm struggling to get things working right.

Any help, including a simple nudge in the right direction, would be very much appreciated!


texts:
I have a CiviCRM database on a Drupal CMS using a MySQL database. I'm having problems because users keep getting logged out. First thoughts/suggestions were related to being switched between http/https, or perhaps related to Drupal modules. I've discounted all of these.

However, when I tried to deliver training to the new users (we're due to go live on 3rd April!), it only seemed to log us out when we were trying to write to the database, e.g. creating new contacts or events. Also, for a handful of contacts I hit an error when I try to view their events, but this is fine for the majority. The events were imported directly to the database using phpMyAdmin, rather than the UI in CiviCRM.

Is it possible that there's an issue with the database? I'm using MySQL v5.7.

I'm totally out of ideas and I really need to fix this!


texts: Interpreting sp_BlitzCache: # Executions but since when?

texts:
I'm getting started with sp_BlitzCache  (which is amazing!) and trying to understand the results better. When does the # Executions count from? e.g. if it says 343 executions is that today, since server start, or what? I'm running it like this:
sp_blitzCache @MinutesBack = 1440, @sortOrder = 'query hash'

But the # Executions appears not to change based on the @MinutesBack parameter so I'm guessing it's since some fixed time period?
I'm using SQL 2019.


texts: How to store very small values (E-314) in SQL Server database?

texts:
I had created table which hold small floating values using below query

create table temp_data (val float)

But when it gives warning message for very small values (e-314)

insert into temp_pce_rank_data values(2.466987305926227e-314)

Data gets inserted with value '0' warning message is: The floating point value '2.466987305926227e-314' is out of the range of computer representation (8 bytes).

How I can store & retirve very small values (E-314) in SQL Server database?
Thanks


texts: Permissions to execute a procedure on database a that reads a table on database b

texts:
A user in database a has execute permission on a stored procedurte in database a, that stored procedure reads table 1 in database b however when the user executes that procedure they get the following message.

  The SELECT permission was denied on the object '1', database 'b', schema 'dbo'.

If I give the user db_datareader permission in database b then the procedure works.

I didn't think I needed to do that though. Please can somebody explain how wrong I am!


texts: How to open a file in MySQL event scheduler?

texts:
I am currently creating my first event in MySQL:

CREATE EVENT check_for_uploads
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
   // I would like to open a .php file here

However I cannot find information on if its possible to open a file from there. I think I have seen it in some tutorial.

Thank you very much!


texts: How to create new table with same constraints and indexes?

texts:
I am Creating a new table with Primary Key Constraints and a Non Clustered Index in that table.

I know, I would like to create another table with same structure and values as well keys and indexes also.

create table Dummy (id integer ,name varchar(20),salary integer
Constraint PK_Con_id primary key(id))

insert into Dummy values(11,'AAA',1000);
insert into Dummy values(12,'BBB',2000);
insert into Dummy values(13,'CCC',3000);
insert into Dummy values(14,'DDD',4000);

select * from Dummy;

create nonclustered index IX_Name
on Dummy(Name)

Now I am creating Dmy table but Keys and Constraints not reflect in Dmy table in SQL Server 2008 R2.

SELECT *
INTO Dmy
FROM Dummy


texts:
I have two postgres databases.

Master = master_database
Slave = database

All initial changes are made to master.  Upon admin approval, the version is updated, and I want all changes to be copied to slave.  I looked into using octupos, rubyrep, etc. but I don't want an automatic replication.

I'd like to add a rake task that is executed only when the version is updated.

I've tried using pg_dump and pg_restore, but I get error messages saying that:

ERROR: relation "whatever" already exists
~ OR ~
ERROR:  multiple primary keys for table "whatevers" are not allowed


texts:
I am trying to learn sharding in MongoDb and here is my plan:

I'm trying to shard the database S001 into 2 different machines.

  192.xx.xx.60>mongod -configsvr --dbpath S001
  ..Now running in port 27019

  192.xx.xx.60>mongo

    show dbs
    S001 0.74151GB

Only one database S001 is there

  192.xx.xx.60>mongos -configdb 192.xx.xx.60:27019,192.xx.xx.61:27019,192.xx.xx.62:27019
  ..Some messages...

  192.xx.xx.60>mongos

    show dbs
    config 0.54125GB
    admin empty

Whereas now it is showing 2 different databases called config and admin.

I don't understand the following points:
1. how were these 2 new database created?
2. Is my database now sharded?
3. How to continue further?

FYI: Still now I haven't set up replication so I'm trying to shard without replication.

Totally stuck and unable to continue further.  Please share your suggestions.


texts: Using AMMM or Huge Pages for Oracle 11g R2 Database

texts:
I'm running an Oracle production 11gR2 database which has around 65Gb of data, running with a 4Gb SGA & 40Mb PGA using ASMM.

Now, I have a memory increase up to 32Gb. I'm planning to increase the SGA and PGA for the database but I don't know which one I should use - AMMM or HugePages? I'm using Linux 6 (RHEL?).


texts: "Can't find any matching row in the user table" when attempting "SET PASSWORD"

texts:
When I do SELECT User, Host FROM mysql.user I see that I have a user named myuser on %. I tried setting that users password thusly:

SET PASSWORD FOR 'myuser'@'%' = 'mypassword';

Doing so, however, resulted in this error:

Error Code: 1133. Can't find any matching row in the user table

Superficially this sounds like Error Code: 1133. Can't find any matching row in the user table but it's not the same because the username and password do appear to exist...

Any ideas?


texts: FCI with Alwayson Availability Group (Asymmetric Storage)

texts:
I am trying to setup a demo environment where I have a windows cluster with 3 nodes.
Node 1 will be standalone sql server and will be the primary replica.
Node 2 and 3 will be in a Failover cluster instance(sqlclu1) and that will be the async secondary replica.

I have installed sql server as a clustered instance (Node 2 and 3) and
has a shared storage(E: drive) which is presented only to node 2 and 3 and which contains sql data files(by which I mean, the possible owner for this disk is only node 2 and 3 and also I have not connected the disk to Node 1).

I am stuck at the next step where I am trying to install a standalone sql server on node 1 for being the primary replica.  The issue is when I try to add a disk with the same name (E:) it is not letting me do that.  E:drive is just not available in the list when I create a new volume.

I am guessing I have stuffed up something and am wondering how Asymmetric Storage needs to be configured so that I can create disk with the same drive letter.

As per https://msdn.microsoft.com/en-us/library/jj215886.aspx

  Asymmetric Storage Two FCIs, one at each site on a single multi-site
  WSFC, introduce considerations around how shared storage is handled.
  Each FCI has its own shared storage. The nodes at the primary site
  share storage among themselves to form a shared-storage FCI, and the
  nodes at the DR site share storage among themselves to form another
  shared-storage FCI. The storage on the primary site is not visible to
  the nodes on the disaster recovery site and vice versa. This
  arrangement of storage, where a cluster disk is shared between a
  subset of nodes within a WSFC, is referred to as asymmetric storage.
  Before the asymmetric storage capability, shared storage needed to be
  visible to all the nodes in the WSFC (symmetric storage).  This
  Windows Server enhancement is the key piece of functionality that
  enables the FCI + AG solution architecture discussed in this white
  paper. By enabling this functionality, you can combine the shared
  storage solution (FCI) with the non-shared storage solution
  (availability groups), in a single HA + DR solution. Consequently,
  this enhancement also enables you to use identical drive letters for
  shared disk resources across data centers.

Using sql server 2014 and windows 2012R2


texts: Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3)

texts:
I am new to postgres,

I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing this table (very frequently).

I have a way to delete in about 100-400K row chunks at a time. Between each delete, I want to make the query sleep - so that other operations can get a chance to access this table.

I have the code, but I believe in this version, it locks the table the entire time the query runs (with all the sleeps). How can I actually release the table while the process sleeps? Thank you!!

My code so far:

CREATE SEQUENCE tmp_sq increment by 1 minvalue 1 maxvalue 53 start with 1;

DO $$

DECLARE
w_counter integer;

BEGIN
w_counter := (SELECT nextval('tmp_sq'));

  while w_counter < 53 loop
    raise notice 'Value: %', w_counter ;

    w_counter := (SELECT nextval('tmp_sq'));

-- this way of breaking up the delete into chunks works for my table because of dates.
    delete from table_a where date_part('week',my_date) = w_counter;

    raise notice '     Rows Were Deleted ';

    --sleep
    raise notice 'Sleeping Now.....';
    perform pg_sleep(60);
  end loop;

END $$;


texts:
I have a setup of merge replication.

Sometimes, subscribers will not be able to sync within the retention period set in the publication.

How can I get a list of expired subscribers to a merge replication?


texts: How to refer to Field Name with a ? in it in MS Access VBA code

texts:
Simple question, but I am new to databases. I have a field called 'ActorRetired?', and in some VBA code I need to set the value of it to -1. But When I try:
Me.ActorRetired?.Value = -1

I get the error 'Compile Error: Expected: Expression' with the question mark highlighted.
How do I refer to the field name without this error? So far I have tried
Me.[ActorRetired?].Value = -1
Me."ActorRetired?".Value = -1

But neither has worked. Please help!


texts: MySQL: Can not add foreign key into create table statement

texts:
The queries below (assigned to php variables) seem to have an issue on the 2nd query, where I set the foreign key to the "couponId" column on the first table. My IDE lints "couponId" (inside the foreign key declaration) as well to show there's a issue.

Does anyone see what the issue is? Below is the error and the create function/query

// codeception is a test runner so I think it injects "temporary" into create statements

WordPress database error Cannot add foreign key constraint for query CREATE TEMPORARY TABLE IF NOT EXISTS wp_frequentVisitorCoupons_targets (
      targetId mediumint not null auto_increment unique,
      primary key (targetId),
      isSitewide tinyint(1) not null,
      targetUrl varchar(500),
      displayThreshold tinyint(5) not null default 20,
      offerCutoff tinyint(5),
      fk_coupons_targets mediumint not null unique,
      foreign key (fk_coupons_targets) references wp_frequentVisitorCoupons_coupons(couponId) on delete cascade
    ) made by Codeception\Application->run,  <continues on ...>

// the create table function
public static function createTablesIfNotExists() {
    global $wpdb;

  $createCouponTableQuery = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fvc_coupons (
  couponId mediumint not null auto_increment unique,
  primary key (couponId),
  totalHits mediumint not null,
  isText boolean not null,
  fileName varchar(200),
  folderDateString varchar(7)
  )";

  $createTargetTableQuery = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}fvc_targets (
    targetId mediumint not null auto_increment unique,
    primary key (targetId),
    isSitewide tinyint(1) not null,
    targetUrl varchar(500),
    displayThreshold tinyint(5) not null default 20,
    offerCutoff tinyint(5),
    fk_coupons_targets mediumint not null unique,
    foreign key (fk_coupons_targets) references {$wpdb->prefix}fvc_coupons(couponId) on delete cascade
  )";

  require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
  dbDelta($createCouponTableQuery);
  dbDelta($createTargetTableQuery);

}


texts: SQL Server 2008 Analysis Management objects work with SQL Server 2005?

texts: Can SQL Server 2008 Analysis Management objects API work with SQL Server 2005 install?


texts: Does msmdpump.dll pass the authentication info to SSAS?

texts: I'm using msmdpump.dll with basic authentication to access a SSAS OLAP cube from the internet. Does this method pass the auth info to SSAS so if I put permissions on the cube they get applied depending of the user I input in my connection string? If not, is there a way to do it?


texts:
I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT.

I'm trying to move data between tables, both of which have an identity column (similar schema).

SET IDENTITY_INSERT DestinationTable ON;

returns that IDENTITY_INSERT is already ON for that table. (Error Msg 8107) Fine, it's already ON, I can work with that, right?

Then:

INSERT INTO DestinationTable (Id, ColA) SELECT Id, ColA FROM SourceTable;

returns that I can't insert an explicit value .. when IDENTITY_INSERT is OFF (Error Msg 544). But it just said it was ON.

So, what's going on?

EDIT:
Actual script (save for table names and structure):

SET IDENTITY_INSERT User2.DestinationTable ON;
INSERT INTO User2.DestinationTable (Id, ColA) SELECT Id, ColA FROM User1.SourceTable;
SET IDENTITY_INSERT User2.DestinationTable OFF;


texts: Getting Identity values to use as FK in an INSTEAD OF trigger

texts:
I have a series of updateable views we are exposing to end users as the interface for a back end process.

One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and INSERTs.

The structure of the tables is (greatly simplified):

Claim
(DataRowID bigint IDENTITY PRIMARY KEY
,<bunch of claim data>)

ClaimExtended
(ClaimDataRowID bigint FOREIGN KEY references dbo.Claim(DataRowID) NOT NULL
,<bunch of other claim data>)

My original plan was to do this in the trigger like so:

CREATE TRIGGER [dbo].[MyTrigger] ON [dbo].[MyView]
INSTEAD OF INSERT
AS

DECLARE @IDLink TABLE
    (RowID int
    ,ClaimDataRowID bigint)

DECLARE @Inserted TABLE
    (RowID int identity (1,1) NOT NULL
    ,<all the columns from the view>)

INSERT INTO
    @Inserted
    (<View columns>)
SELECT
    (<View columns>)
FROM
    Inserted

INSERT INTO
    Claim
    (<Columns>)
OUTPUT
    I.RowID
    ,inserted.ClaimDataRowID
    INTO
        @IDLink (RowID, ClaimDataRowID)
SELECT
    (<Columns>)
FROM
    @Inserted I

INSERT INTO
    ClaimExtended
    (ClaimDataRowID,
    <Columns>)
SELECT
    C.ClaimDataRowID,
    <Columns>
FROM
    @Inserted I
INNER JOIN
    @IDLink C
        ON C.RowID = I.RowID

The OUTPUT clause here is not working, however (Multi-part identifier I.RowID could not be bound) I'm assuming because I can't reference the source table in an INSERT OUTPUT clause.

What other method could I use here besides making the view a table?  For other reasons this needs to be a VIEW and the underlying tables are pretty much set in stone.


texts: SQL Server renaming table from another database

texts:
Is there a way in SQL Server to rename a table in another database? When you are in current database and migrating data over to another database in the TEMP table and then renaming TEMP table in that another database. I do not want to use "USE [database]" since the database name could change on different servers.

Thanks for the help in advance!


texts:
I've set up replication between an EC2 instance as master and a MySQL 5.5 RDS instance as slave. Everything is (mostly) fine except for one odd thing: the dates in a transaction table are either correct or have a +4 offset and the only correlation I can find is in the application code.

Details:

EC2 Master is running MySQL 5.5 with time_zone = SYSTEM. The 'date' command shows the master is in EDT (US/Eastern).

RDS Slave is running MySQL 5.5 with time_zone = US/Eastern.

binlog_format is MIXED.

The transaction records are written from two different points in the code because the data being transacted comes from two different vendors, A and B. They are written to the same transactions table. The datatype of field, create_time, is TIME.

When processing Vendor A's data, the application uses

$create_time = Carbon::now()->toTimeString()

to generate the time. The time gets saved to the database and properly replicated to the slave.

When processing Vendor B's data, the application uses NOW() in the SQL statement for create_time. The time gets saved to the master database properly, but is replicated with a +4 hour offset.

I don't understand how correct data in the master is replicated differently based on the application code; once the correct data is written to disk, it's written to disk and that's what is replicated, right?

Musings:

I wonder if this bug is applicable to my situation.

BTW, I have some other tables where ALL of the dates are either correct or have a +4 offset. No correlations in the application code found there. Yet.

My suspicion is it's the bin_log format. I'm about to dig through the binlogs to see what I can find. If it is, do I need to tear down my replication and start from scratch?

Edit:

After replication synced up, I ran pt-table-checksum on all databases. No errors were found.


texts: Inserting many documents in collection, but only unique elements, based on specific field

texts:
I cannot seem to find an answer on this anywhere. I need the following:

Given an array of objects with the structure:

    {
       link: 'some-link',
       rating: 25,
       otherFields: '..',
       ...
    }

I want to insert them into my collection. So I would just do insertMany... But I only want to insert those elements of the array that are unique, meaning that I do not want to insert objects with the field "link" being something that is already in my collection.

Meaning if my collection has the following documents:

    {
      _id: 'aldnsajsndasd',
      link: 'bob',
      rating: 34,
    }
    {
       _id: 'annn',
       link: 'ann',
       rating: 45
    }

And I do the "update/insert" with the following array:

    [{
      link: 'joe',
      rating: 10
    },{
      link: 'ann',
      rating: 45
    }, {
      link: 'bob',
      rating: 34
    }, {
      link: 'frank',
      rating: 100
    }]

Only documents:

    {
      link: 'frank',
      rating: 100
    }
    {
      link: 'joe',
      rating: 10
    }

Would be inserted in my collection


texts: Create Enumerated Function Parameter Type

texts:
I want to create a function similar to DATEDIFF in MS SQL Server 2008 R2.  The first parameter in DATEDIFF is a datepart.  Can I use datepart as a parameter in my function?  If not, how do I create a parameter similar to datepart?  I don't want to make the parameter text.

For example, I would like to have a function like this: MYFUNC(datepart, timestart, timeend)

So when I call the function, it would look like this: MYFUNC(Hour, N'08:00:00', N'12:00:00')

I'm currently using a nvarchar for the datepart param so my function looks like this: MYFUNC(N'Hour', N'08:00:00', N'12:00:00')


texts: MariaDB Galera Cluster wsrep_cluster_address not working

texts:
I have configured MariaDB galera cluster with three nodes on CentOS-6.5 64bits:

db1 - 192.168.1.111
db2 - 192.168.1.112
db3 - 192.168.1.113

My /etc/my.cnf.d/server.cnf file is configured as follows:

[galera]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.111,192.168.1.112,192.168.1.113"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.1.112'
wsrep_node_name='db2'
wsrep_sst_method=xtrabackup
wsrep_sst_auth=username:password

All three nodes have the same config (with changes for wsrep_node_address & wsrep_node_name on each appropriately). When i start with the first node and start MariaDB with either of the following command, it works fine and it shows that the cluster is running with one node up:

#/etc/init.d/mysql bootsrap
#/etc/init.d/mysql start --wsrep-new-cluster

But when i try to start MariaDB on the second node with /etc/init.d/mysql start, it fails to start. When i comment out wsrep_cluster_address=... from the config above, then MariaDB on the second node starts but the node is not not part of the cluster. I have done this config before and worked just fine. But when i shutdown the cluster for maintenance and tried to start it again, only the first node will start. Any suggestions to why this is happening?


texts: Drop auto increment hack w/o alter table?

texts:
I was looking around for a quick way to remove an auto increment from the definition of a primary key.  As best I can tell the only way to do it is w/ an alter table or dumping all the data into a new schema sans auto_increment.

Just for fun I tried the following to see if it would work.

Create table test1 (innodb) w/ an auto increment PK
Insert a couple rows w/o specifying PK values, letting auto inc do its job
Create table test2 like test1;
Alter table test2 modify pk_col int(10) unsigned not null; -- no auto_inc
Shutdown mysql
backup test1.frm; cp test2.frm test1.frm
Restart mysql
select * shows all rows as expected, w/ previously auto_inc created PKs 1,2 and 3
Insert a row w/o a PK specified, gets created w/ PK value as 0 (the default value).
Insert a row specifying PK of 5
Shutdown mysql
restore original auto_increment test1.frm
Restart; show create table lists auto_inc
Test insert a row w/o specifying PK, generated auto_inc value of 6 (+1 the highest value even though the last one created by virtue of auto_inc was 3

Seems like that works!

For curiosity I ran both .frms through od and diffed that output.  All I got

9c9
< 0010020 000001 015000 002000 000400 003400 001400 000000 001400
---
> 0010020 000001 015000 002100 000400 003400 001400 000000 001400
45c45
< 0020760 002000 005003 000012 000001 015000 007400 000000 020403
---
> 0020760 002000 005003 000012 000001 015000 000100 000000 020403

Thoughts, comments?


texts: SQL Server 2008R2 Ent + Win2003 Ent R2 32bit will not use all available memory

texts:
Server:

HP ProLiant DL380 G5
2 proc -2x QuadCore Intel Xeon E5450, 3000 MHz
Memory- 12288 Мб (DDR2-667 Fully Buffered ECC DDR2 SDRAM)

Settings:

/3GB in the boot.ini.
/PAE in the boot.ini.
Enabled AWE in SQL Server.
Enabled Lock Pages in Memory Option for users SYSTEM and Local Service.
Set Min/Max Memory to : 6 Gb/8 Gb

SQL Server: Memory Manager_Total Server Memory (KB) = 100%

I don't know how to make SQL capture free memory allocated him.


texts: Search for null in id column over all tables programmatically

texts: I want to automatically check for null ID's over all id columns in my database, I have to check all tables of which there is about 50,000 so manually doing this is infeasible


texts: Is it a good approach to duplicate data in a database?

texts:
I have a program that needs to store traceroutes (among other data).

Here is a diagram that depicts the business scenario:

My use case right now is to store the traceroutes using the following tables (with their corresponding columns):

packet

packet_id
source_ip
destination_ip
packet_length

traceroute

traceroute_id
packet_id
timestamp
path_id

path

path_id
traceroute_id
timestamp

ip

ip_id
ip_address

path_ip

path_id
ip_id
order_index

This creates the need to make several JOINs to get back a traceroute, but it makes the pieces of the traceroutes available for query for other needs.

I am considering having a second traceroute table that stores the path as a json string of ip_addresses. This allows me to recover full traceroutes with minimal JOINs, but still retain the individual hops (ip_addresses) used for other queries.

My questions are:

Does having this duplicated data make sense?
Is there a better approach to this?

Responses to comments:

I would add the traceroute data from one method, and would populate the tables at the same time.

The duplicated table (perhaps called traceroute2?) would just be used for retrievals, and none of the data would ever be edited or updated.

I am considering this because I am most interested in speed of data retrieval. I did some benchmarking (sort of crude) and I can get a 2-6X speed improvement on retrieval.

I don't want to prevent JOINs for a particular reason, just want to avoid this many. For this operation, I never need the individual hops of a traceroute, so I don't see what I would gain by splitting them out when I add it, and then piecing them back together again.


texts: Operand data type nvarchar is invalid for multiply operator error

texts:
I am getting this error when I run (this part) of the following query in SQL Management Studio. Can someone help? Do I need to change the query to see this as a different value?

sum(a.UNITCST * a.PRICEQTY) AWP,
sum(a.CLTINGRCST) INGRD_CST,
sum(a.CLTDISPFEE) DF


texts: Is there any trick that allows to use Management Studio's (ver. 2008) IntelliSense feature with earlier versions of SQL Server?

texts:
Latest version of Management Studio (i.e. the one that ships with SQL Server 2008) finally has a Transact-SQL IntelliSense feature. However, out-of-the-box it only works with SQL Server 2008 instances.

Is there some workaround for this?

[I asked this question a while ago at StackOverflow and unfortunately nobody knew such a trick. Maybe I will have more luck with this problem here.]


texts: improve performance of insertions using MySQL for 10 million records in 5 tables

texts:
*env: windows 10 version mysql 5.7 Ram 32GB ide : toad mysql*

Count of records is 11308147.

I have 5 like tables

I have sufficient hardware requirement but issue is the performance of insert into simple table that does not have any relation ships. I need to have index on the table.

table structure

 CREATE TABLE `2017` (
      `MOB_NO` bigint(20) DEFAULT NULL,
      `CAF_SLNO` varchar(50) DEFAULT NULL,
      `CNAME` varchar(58) DEFAULT NULL,
      `ACT_DATE` varchar(200) DEFAULT NULL,
      KEY `2017_index` (`MOB_NO`,`ACT_DATE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I am using below for inserting the records into table. with out index it took around 45 min with out indexing and with out set statements where as with indexing it took 22 hrs still going on.

   SET autocommit=0;
    SET unique_checks=0;
    SET foreign_key_checks=0;
    LOAD DATA LOCAL INFILE 'D:/base/test/2017/2017.txt'
    INTO TABLE `2017` COLUMNS TERMINATED BY '|';
    commit;

I have seen suggestion to change cnf file, Could not find any in my machine. I would request some one to help me in this concern. I have been working on this for past 7 days but there is no improvement in the performance.


texts:
I'm running

mysql  Ver 15.1 Distrib 10.4.10-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

How can I set the system variable sql_safe_updates via my.cnf? I want it to always be on.


texts: Postgesql non-deterministic order of result with LIMIT and ORDER BY

texts:
I understand that Postgresql may return results in non-deterministic order when the query parameters are non-unique. This is especially a problem when we use LIMIT since this may return entirely different results. So the suggestion for safety is to use ORDER BY with LIMIT.

However, what if we use ORDER BY on an non-unique column name? Will this produce a deterministic result or no? Let me demonstrate with an example:

We have the following data:

id | name | index
1    foo     5
1    bar     2
1    test    5

If I query SELECT * FROM <table> WHERE id = 1 ORDER BY index LIMIT 1;. Will this produce the same result every time?


texts:
At my company we have a need to migrate a portion of our provisional data between versions of our database, housed on different servers (upserts for the most part).  For release management purposes, we have several environments, each containing a slightly different version of our database.  The differences may include additional or missing: columns, constraints, tables, views, etc.  Most importantly, we always have different data (identities representing the same entities may differ across environments as well).

The purpose of our migration requirement is that we'd like to be able to copy data back and forth between environments to stage production changes in our UAT environment, or to copy a particular data scenario/configuration back to our development environment for debugging or testing purposes.

I've read that SSIS is an option, but I'm not sure this applies to my scenario since we would like to leave existing similar (but unrelated) data in-tact.  So far we've achieved our goal via custom migration scripts which use dynamically generated merge statements and by tracking identity values to maintain relationships.  This approach is limited in capability and, without regular maintenance, it quickly falls out of date.

It seems our only viable alternative is spinning up a new SQL instance per configuration, which is an option not well-received by management (due to licensing and maintenance costs primarily).

Is there an easier/lower-maintenance way to transfer only a portion of our data dynamically between environments, while leaving similar but unrelated data in-tact?  Or are we approaching the problem incorrectly?


texts:
Okay, this seems funny for a simple script: but I really need this small piece to execute well.

DECLARE @dbName NVARCHAR(50)
SET @dbName = 'rx_lab'
DECLARE @SQL02 NVARCHAR(MAX)
SET @SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +']'
exec sp_executesql @SQL02

SELECT DB_NAME()

This still returns the 'master' database (instead of 'Rx_lab')
I have even tried using

exec ('use '+@dbName)

No change. It didn't work even without the conversion to nvarchar.
However, when I use 'USE [Rx_lab]' directly it returns well. This is part of stored procedure where the @dbName is main parameter.


texts: Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios

texts:
From what I can tell, there are three possible ways of backing up your SQL Server database

Full backup
Differential backup
Log Shipping

What are the pros and cons of each strategy and in what situations should they be employed?


texts: Could not find prepared statement with handle -1 SQL Server Profiler

texts:
So I have this issue where I'm running Sql Server Profiler and I'm getting hundreds(at least) of Could not find prepared statement with handle -1.(error 8179) per second.

I can't seem to really correlate the errors with anything specific except some cached prepared statements I think.

I'm using Linq to Sql.

If you need more info just let me know.


texts: How many pool connection size should be set for a 1000 users Jira system?

texts:
For a Jira system, if use Jira's default connection configuration as below:

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>postgres72</database-type>
  <schema-name>public</schema-name>
  <jdbc-datasource>
    <url>jdbc:postgresql://[IP]:5432/jiradb</url>
    <driver-class>org.postgresql.Driver</driver-class>
    <username>jiradbuser</username>
    <password>{PASSWORD}</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

In postgresql's config file:

postgresql.conf

max_connections = 300
shared_buffers = 80MB

In /etc/sysctl.conf file:

kernel.shmmax=100663296

At test stage, it can work. But in the production environment, for 1000 users use this system, if one app server serve 500 users, how many pool connection size is good to set in the postgresql.conf file?
And, if use pgbouncer, how many connections it can be reduced?


texts: What is fragmenting my index on a table with stable traffic?

texts:
I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's being used hasn't changed either.

For the last three weeks every time it runs it reports that the clustered index on the database's biggest table is getting between 5-12% fragmented. Strangely enough this is not occurring during weekdays, so it must be there sometime before the maintenance job runs.

My table has a timestamp on every row so I know that the traffic has stayed on the same level for months. Usually it's under 1% fragmentation per month.

I have two questions:

What else could be fragmenting my index?
Is there an automated solution I can set up to track changes? Ideally something not disruptive since this is a production box.

The table has 134 columns contains 14gb worth of data, and the primary key is not an identity (sigh)

The index in question looks like this:

Average row size: 1413
Depth: 4
Leaf-level rows: 9884500
Maximum row size: 2303
Minimum row size: 746
Pages: 1904907
Partition ID: 1

CREATE UNIQUE CLUSTERED INDEX [FOOINDEX] ON [dbo].[FOOTABLE]
(
   [FOONO] ASC,

   [ID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,  IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


texts:
I have an HR system and I need to detect if a new leave (time off) request conflicts with an existing one. I have written code to do this based on the entire day; I now need to do the comparison based on half days.

Here is my data structure (and the data format). I am using SQL Server 2008 R2.

StartDate     (datetime)
EndDate       (datetime)

The following 2 variables show if the user is taking a half or a full day off.
The options for each are listed and explained.

StartDateAMPM (int) 0=All Day (StartDate may or may not be the same as the EndDate),
                    1=AM, (User is out of the office only in the morning)
                    2=PM  (User is out of the office only in the afternoon)

EndDateAMPM   (int) 0="All Day" / "half day" / "StartDate = EndDate",
                    1=AM (The user is off for the morning only)

**NOTE This cannot be PM (as you cannot end a leave with a half day in the afternoon)

**IF this is a "0", there are 3 possibilities --
A) a full day leave where the StartDate <> EndDate
B) a full day where the StartDate = EndDate
C) a partial day where StartDate = EndDate
   and the user is off in the morning or the afternoon

I pass this info to the database and am trying to see if the new leave conflicts with existing leave. Here is the code to do it for a single day:

Legend:  @Start DateTime -- StartDate from new leave
         @end   DateTime -- EndDate from new leave
         @StartDateAMPM (int) 0,1,2 (see above for details)
         EndDateAMPM    (int) 0,1   (see above for details)

select count(ID)
from v_VacReqWHalfDays
where
(
        @Start BETWEEN StartDate and EndDate
    OR  @End   BETWEEN StartDate and EndDate
    OR
    (
        @Start <= StartDate
        AND @End >=EndDate
    )
)
AND kUserID = @UserID;

If Count(ID) > 0, there is a conflict.

My question is how to incorporate the 1/2 day leave. People can take full days or half days off (the morning or the afternoon).

I want to use TSQL to determine if the current leave request conflicts (overlaps) with an existing and can't figure out how to do it.


texts: Database table data transfer between different servers using bcp.exe and mysqldump

texts:
I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY.

Using MSSQL on two different servers I am using the following command lines to transfer table data between servers...

Export table data from DATAENTRY:

bcp.exe "SELECT * from database.dbo.table WHERE Modified > '{MM/DD/YYYY} 00:00:00.000'" queryout C:\export\dbtabledump.bcp -n -U{username} -P{password}

##FTP upload file: dbtabledump.bcp to PRODUCTION##

Import table data into PRODUCTION:

bcp.exe database.dbo.table in "C:\import\dbtabledump.bcp" -n -U{username} -P{password}

Now I would like to trim down the DATAENTRY server to the bare essentials and use LAMP instead of the current WIN/IIS/MSSQL combo. Database schema will be identical on both platforms.

Here are my following quesitons:

What is the equivalent of MSSQL BCP.EXE in MYSQL?

Currently I have found mysqldump command

Is the exported mysqldump file compatible with MSSQL BCP.EXE?
If not, how do I prepare the MYSQL table data dump file for MSSQL BCP.EXE to import?
I'm open to any other ideas, is there a more elegant solution to my workflow?


texts: What are the limits on the amount of slaves per master?

texts:
I currently have a Master with 2 slaves, all running MySql 5.5.

What are the limitations on the amount of slaves I can connect to a single master? what parameters should be taken into affect?


texts:
Anyone have an idea on how to solve this issue.

Im modeling my membership and user profile DB and run into one issue I have to solve.
I have this layout of tables.

Membership (where all basic info is stored, like password etc.)
UserProfile (where all extra info is stored, like name etc.)

In my application I will have two user groups (Parent/Kid), and both of them have the need of registering different types of profile data. Some are the same, but some is different.

How should I solve this? Should I just add all fields to the UserProfile table, and settle with the fact that some fields will be redundant.

Or should I create a new table for each user group and link them to the UserProfile?


texts: Pull data from one DB Server to another DB server

texts:
I want to pull Few tables (33 Tables) from one DB server A to another DB server B.
I have Schema ready with few datas in DB Server B but when I try to pull data from DB Server A to DB Server B,
I am getting following  error:

The INSERT statement conflicted with the FOREIGN KEY constraint
"Table 1".
The conflict occurred in database"DB Server A", table "dbo.Parent Table",
column 'xyz'.

The code which I used for this Operation:
INSERT INTO server1table
(X,Y,Z)
from server2.database.dbo.server2table

So what are the possible ways to do this operation taking care of key constraints?


texts: Can't get past password lock on fresh install of postgreSQL

texts:
I feel really dumb writing this, I wanted to have a mess around with databases so I just installed postgreSQL (9.1)

During the installation I am asked to provide a superuser password. I provide a password that it can use and everything gets installed.

I'm following the getting started documentation. getting started documentation and attempt to use the command createdb in cmd.exe. It asks for a password and responds with:

createuser: could not connect to database postgres: FATAL:  password authentication failed for user "VanGOss"

I can access a database inside pgAdminIII with the password I originally provided at installation. So it's the command line tools not accepting the password.

I also run postgres and got this back:

Execution of PostgreSQL by a user with administrative permissions is not
permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises.  See the documentation for
more information on how to properly start the server.

I tried using createuser to make a user, again run from cmd.exe and it said its not accepting my password.

what i'm thinking is I must run my environment under a limited user, which I don't want to do. This is my home computer and I just want one account.

windows 7 64-bit
1 user account, me, admin
can't past page 2 of a tutorial on running a database, kinda shameful :(

Any one know how I can make my own database? Please.


texts: How to connect Informix to SQL Server as a linked server?

texts:
We got to the point that we can see the tables, but we can't see the columns.  For the record, all of the tables are called "root.".  I don't know what that means in Informix speak.  Based on the table names I think they are the tables that we want to see, but I'm not sure that they aren't all system tables.

FWIW, the error I see from SQL Server is:  "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "CMS".  (Microsoft SQL Server, Error 7303)"

It doesn't seem very helpful.


texts: Trigger function using current row and current table name as variables (final part)

texts:
Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables (from a to z). Each table has a trigger that calls a trigger function named trfn_tbl_log_%letter% (from a to z) which does the exact same thing:

CREATE OR REPLACE FUNCTION trfn_tbl_log_a_timetypespan()
  RETURNS trigger AS
$BODY$
DECLARE
v_timetype character varying;
v_timestmp_timetype timestamp without time zone;
v_timetypespan_resume interval;
v_stmtserial real;
v_sumtimetypespan_fnname interval;

BEGIN

IF NEW.timetype = 'lap' THEN
  SELECT timetype, timestmp, timetypespan FROM tbl_log_a WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'resume') ORDER BY stmtserial DESC LIMIT 1 INTO v_timetype, v_timestmp_timetype, v_timetypespan_resume;
    IF v_timetype = 'start' THEN
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype);
    ELSIF v_timetype = 'resume' THEN
      SELECT timestmp FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timestmp_timetype;
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype) - v_timetypespan_resume;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

ELSIF NEW.timetype = 'resume' THEN
  SELECT timestmp FROM tbl_log_a WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'lap') ORDER BY stmtserial DESC LIMIT 1 INTO v_timestmp_timetype;
    IF FOUND THEN
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype);
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

ELSIF NEW.timetype = 'total' THEN
  SELECT stmtserial FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'total' ORDER BY stmtserial DESC LIMIT 1 INTO v_stmtserial;
  SELECT SUM(timetypespan) FROM (SELECT DISTINCT ON (floor(timeidx)::int) floor(timeidx)::int timeidx, timetypespan
  FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'lap' AND stmtserial > coalesce(v_stmtserial, 0) ORDER BY 1, 2 DESC) a INTO v_sumtimetypespan_fnname;
    IF v_sumtimetypespan_fnname NOTNULL THEN
      NEW.timetypespan := v_sumtimetypespan_fnname;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE;

Trigger definition:

CREATE TRIGGER trfn_tbl_log_a_timetypespan
  BEFORE INSERT ON tbl_log_a
  FOR EACH ROW EXECUTE PROCEDURE trfn_tbl_log_a_timetypespan();

So I have to create 26 trigger functions, one for each tbl_log_%letter%. I am trying to replace all of them with a single, generic trigger function.

We worked out a solution with dynamic SQL under my previous question with a simplified function. Can the same technique be extended to this more complex scenario?

EXECUTE format($$...


texts: What is disadvantage of disabling lock escalation in SQL Server 2016

texts:
Using SQL Sever 2016 at AWS on I3 32 cpu 240GB 2012R2 server.

We have import process that inserts as select 100,000s or rows into table A.  Users when they log in need to update table A.

Import process is is causing lock escalation since it uses more than 5000 locks.  This blocks users from logging in.

We can alter the table to disable lock escalation.

However, what is the cost of doing so.

Is it just that we consume more memory for locks ( we have enough at 200GB) and that is it?  Or are the any other problems.


texts: Database restore time increased after we changed from SQL Server 2014 to SQL Server 2016

texts:
I have a 2 TB database (200 GB backup) that I am restoring every night on a secondary server of a AlwaysOn group, to erase confidential information and then backup the clean database and send it to test environment. During the restore there is no other statement running on the SQL Server, except the redo from the primary (which mostly concern reindex).
Before the SQL Server were in 2014 edition, the rate was about 180 MB/s to 200 MB/s for the restore. I have upgraded in-place to a 2016 edition (CU12) a few weeks ago, and now the rate to restore the database is below 90 MB/s.
I have limited time to do the full process of restoring and cleaning the database before teams get to work on it on the morning, and since the migration the processing time no longer makes it possible to respect the schedules.
I have checked disk graph and configuration with system teams, and we didn't found anything conclusive. Database instant file initialization is already enable on the server, and disk block are in 64K. I am restoring from one disk E:\ (where backup are stored) to another disk F:\ (where I work on the cleanup).
Has this problem ever happened to someone after a migration (rate when restoring slower than before)? I haven't found anything like that in official documentation or forums.
EDIT: Resource Governor is disabled. Max memory is 480 GB. Nothing in the error log. No significant queue on the disk during the restore.


texts: Moved MySQL datadir Now Server Will Not Run!

texts:
My problem yesterday (MySQL - Alter Table Fails As Workbench Tries To Create Existing Table)  was caused by /dev/sda2 (/root) running out of space.

The cause of this was that, by default, MySQL had installed the DataDirectory (datadir) on /dev/sda2(/root).
I therefore recursively copied its contents to /dev/sda3 (/home) after stopping the MySQL server.
I also editted /etc/init.d/mysql, /etc/mysql/my.cnf and /etc/apparmor.d/usr.bin.mysqld to reflect this change.

After both trying to restart the MySQL server and a reboot the server is still not running.  The error.log in /var/log/mysql is also not being appended to. Obviously I have missed something but what? I am running Linux Mint 13.


texts: Clustered Index taking huge amount of space

texts:
I have been working on indexing a database dealing with historical data and recently encountered an issue with a clustered index that surprised me.

The database is SQL Server 2018. The table itself deals with account information and is essentially a fact table, it is populated with around 200k records a day and has 200 columns. Currently it is sitting at 68mil records, and before the index it took up around 120GB of space.

Because it is a historical table on account information, and we do not have UUID's set up (which would be a massive effort my boss told me not to spend my time on with this database), I created the primary key + clustered index on the Date field and Account Number with a statement equivalent to:

ALTER TABLE AcctHistory
ADD CONSTRAINT PK_AcctHistory PRIMARY KEY CLUSTERED (Date, Account_Number)

The Date field is a Datetime field (This is a DEV environment that I didn't set up but am now building out. Just know I plan on updating it to a date soon, but I am providing the type in case that is relevant.), the Account_Number field is a bigint.

After running this statement, according to the SSMS disk space by table report, the amount of space the table consumed shot up to 480GB, 4x what it took before the index.

I know indexes take up space, but I have never heard of anything like this. Is this normal or is this indicative of a problem I can solve to save space?


texts: Connect to WSL2 Postgres database from local network

texts:
I run Postgres 12 service in Windows Subsystem for Linux 2 (WSL2).
In my /etc/postgresql/12/main/postgresql.conf, I have:
listen_addresses = '*'

while my /etc/postgresql/12/main/pg_hba.conf has this content:
local   all    postgres                 peer

local   all    all                      peer
host    all    all         0.0.0.0/0    md5
host    all    all         ::0/0        md5

host    all    all         0.0.0.0/0    md5
host    all    all         ::0/0        md5
host    all    all         all          md5

If I try to connect to this server from any other device then the local machine, it times out.
Does anyone have any idea why should this be?


texts: could not open log file "/var/log/postgresql-2015-05-24_181212.log": Permission denied

texts:
I installed postgresql from source on Ubuntu 14.04 (I also installed from repositories too before, I just want to get a feel for both of them right now, as I will heavily rely on this database). So when I installed from source, in my postgresql.conf, I specified I want all standard error from the postmaster executable to go to /var/log:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

I know that only root can write to /var/log (and that is the defaults on Ubuntu), so how are people doing this? How can you make that log file writable before it even exists? Or are people manually creating the log file first and then changing its permissions?

Unfortunately, my temporary solution is to hard-code a filename e.g. postgresql-9.4.2-main.log for log_filename and then as root create that file in /etc/log and then change its ownership to postgres, and then when I start the server it works fine. But I want the log file to be dynamic. After it reaches a specified size, I want the old one to be zipped up, and a new one created with the current date and time.


texts:
I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, not realizing that a lot of that compounded in the ibdata1 file. Now I am in a situation where the ibdata1 file has grown so much that it has used up all of my disk space on my dedicated SQL SSD drive. I have read up on the matter and I know that I have to reload the database into the sql server for it to shrink the ibdata1 file. However, the mysqldump I took of my database is suspiciously small (it is about 2/3rds of my database size and is uncompressed), so what I want to do is load the dump file in a test database to check if there are any issues with it. The issue is, as I do not have any disk space left on my drive (this is a vicious cycle). So, what I would like to do is to move the ibdata1 file to my main HDD, which has plenty of space, and then add a line in the mysql configuration file telling the new location of the ibdata1 file, so I can free up enough space to load up a dummy database. Can I do this? And if so, how do I do this?

Mysql's location; /mysql,
ibdata1 location; /tmpsql/ibdata1 (I can move this to let's say /var/lib/mysql or so if that would be better).

I already set innodb_file_per_table in the configuration file before it used up all my disk space. I have moved the ib_logfile* files as well to the same directory as ibdata1. The ibdata1 file was still used and extended regardless of this setting.

Please let me know if you need any additional information. Thanks in advance!


texts: Is it allowed to for two tables to have two types of relationships between them?

texts:
I have been going back and forth working with relational and no-sql databases for some time now and my questions may be the result of mixing the way of thinking about how to approach a problem.
So consider a simple multiple-choice quiz scenario where a question has an undefined number of answers (meaning it is not a fixed number of answers that each question has. One question could have two alternatives and another could have five). This question will have one correct answer. As such, if we want to have an initial translation between what I just wrote to SQL terms, it will be something like this:
Question                Answer
===============|       |==========
Id             |       |Id
---------------|------<|----------
Title          |       |Body
---------------|-------|----------
CorrectAnswerId|       |QuestionId

So in my mind, a question can have many answers, and an answer can only be linked to one question -> One-to-Many relationship between Question and Asnwer.
Also each question has one and only one correct answer -> One-to-One relationship between Question and Asnwer.
What I am trying to ask is, Is this design acceptable? If not what approach can I take?
One thing that I could do is to add a IsCorrectAnswer flag to the Answer entity. However it makes updating the correct answer to a question more difficult and (in my opinion) error-prone.
Clarification of what I mean by "allowed"
I would like the data to be normalized as much as possible (which I think won't be with my first solution) and I would like not to have any update, insert and delete anomalies either (which I think the second solution "might" have)


texts: Algorithm used to perform offset/limit over a union all result set

texts:
I have two result sets:

rs1:

     id       name
   serial     text
________________
     1        Nick
....................
  1233112     Pete

rs2:

     id       name
   serial     text
________________
  123121      Mike
....................
 221233112   Junior

If we write a query:

SELECT *
FROM
(

    SELECT *
    FROM rs1

    UNION ALL

    SELECT *
    FROM rs2

) as rs
OFFSET 100000 LIMIT 10;

How the result of the query computed? What algorithm is going to be used?

I believe that the server performs lazy evaluation in the sense of not loading the entire union, iterating over it and then returning the required result.

I would be glad if you described algorithms using in other SQL servers as an addition.

UPD: I'm quite new in database internals and don't know if it's possible to ask the sql-server itself about the exectuin plan of the query.

Here is a query plan:

"Limit  (cost=77.11..77.88 rows=10 width=522)"
"  ->  Append  (cost=0.00..140.18 rows=1818 width=522)"
"        ->  Seq Scan on tbl  (cost=0.00..70.09 rows=909 width=522)"
"        ->  Seq Scan on tbl tbl_1  (cost=0.00..70.09 rows=909 width=522)"

The saqme query with order by id:

"Limit  (cost=33.16..36.42 rows=10 width=522)"
"  ->  Merge Append  (cost=0.56..593.16 rows=1818 width=522)"
"        Sort Key: tbl.id"
"        ->  Index Scan Backward using pk_tabl on tbl (cost=0.28..285.21 rows=909 width=522)"
"        ->  Index Scan Backward using pk_tbl on tbl tbl_1  (cost=0.28..285.21 rows=909 width=522)"


texts: Which is the better schema design for a voting system?

texts:
Imagine a q/a site or forum similar to stackoverflow. I'm trying to determine which of these two methods would be the better design for a voting system:
(note: I would also like to keep record of all a users vote decisions; and you can assume the site experiences a relatively high amount of traffic)

create a junction table named VOTE referencing USERS, and POSTS. The problem I see with this is that when a view displays a topic listing all the posts, this seems very inefficient when we want to display each post's "score", since we'd have to count N votes for M number of posts; if the forum is very large this will be a major hindrance.
create the above junction table, but also keep a score on the posts table and simply use this to alleviate the above performance issue. The problem with this is that it describes something that is already described in another table which isn't a good idea either, if im correct?
Use some nosql solution for this part of the application; however this introduces another moving part into the equation and complicates things by having two db technologies working with eachother.

(my solution doesn't have to be one of these three, if you have a fourth or even fifth it is more than welcome, thank you)

(oh, and if it matters in any way, ill be using postgres)


texts:
I have few mysql servers in replication master-salves in cloud service.

The problem is even thought all configuration, mysql versions, server's resources are the same, still I have inconsistency in slave's behavior.

For example, I have a set of stable mysql slaves which work perfectly, and a set of unstable slaves which still have seconds behind master even in idle mode, without traffic, and the set of unstable is always the same servers.

I am looking for something that is different in stable and unstable slaves. So far I didn't find something like this. Please, if you know how to find the thing that different share this with us.

Thanks.


texts:
I have 2 databases - one with customers' data and the second one with discount codes. I would like to assign code to each customer. The code can be assigned only once, so I would like to mark the code as 'used' once it is assigned. The codes should be assigned only to those customers that don't have a code assigned. Unused code can be assigned to any customer.
CUSTOMER_DATABASE:

CUSTOMER ID
CUSTOMER CODE

1
6f73js

2

...
...

DATA_DISCOUNT_CODES:

ID
CODE
STATUS

1
6f73js
USED

2
kdw45s

...
...
...

Could you please help me write SQL query for this, using MySQL 8.0 syntax?


texts: How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?

texts:
This query:

select distinct profile from dba_profiles;

Returns 3 rows:

default
default1
default2.

I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query or without manually passing the profile names in query.

Because I do not know how many profiles are there in the system, the number of profiles will vary. I have to execute this query in multiple systems.


texts: Ola Hallengrens code for Index Optimization - Clarifications

texts:
I am trying to deploy Ola Hallengrens code for Index Optimization in our PROD environment.

What is the difference between using USER_DATABASES and AVAILABILITY_GROUP_DATABASES in the place of @Databases in below code .

Also I was going through the blogs and read :

Ola Hallengrens code for Index Optimization is meant to defragment only with page count more than 1000.

When I execute below command it updates only statistics not rebuild indexes -

Does this mean I am good with fragmentation , not to worry much about it as my end users does not complaint any thing about slow response or bad performance from Database end .

EXECUTE dbo.IndexOptimize
 @Databases = '',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30


texts: Redundant fields in ORDER BY for efficient sorting?

texts:
Suppose I have a table SortTest with fields Data1, Data2, Sort1, and Sort2, Key1 and Key2 need to perform the following query:

SELECT TOP 1
    Data1,
    Data2
FROM SortTest
WHERE Key1 = @key1 AND
      Key2 = @key2
ORDER BY Sort1, Sort2

In order to opimise it, I created an index for the following sequence

Key1, Key2, Sort1, Sort2

But the execution plan still shows an index scan instead of a seek, for it cannot sort effectively on a field sequence that does not start an index. In order, therefore, to optimise the query, I had to add they keys to the ORDER BY clause, which are, of course, redundant:

SELECT TOP 1
    Data1,
    Data2
FROM SortTest
WHERE Key1 = @key1 AND
      Key2 = @key2
ORDER BY Key1, Key2, Sort1, Sort2

The query now works as expected, but I should like to know whether it can be optimised in a more elegant way.

Correction:

I realized that when I simplified the queries above, there was a grave mistake. One of the condition had an IN, not =, so the real queries (that produce the different plans) have this instead:

WHERE Key1 = @key1 AND
      Key2 = IN (@key2a, @key2b, ...)

which explains (that the key2 values in the result are not fixed and thus the different ORDER BY can result in different output) and the plan difference.

Let thank every body for their help and apologies for the confusion.


texts: sp_Blitz does not warn "No Alerts for Corruption" if one of 3 is set

texts:
I was just setting up the Alert for Corruption.

Error 823
Error 824
Error 825

Before setup:

I just by chance added alert for Error 825 and ran sp_Blitz again. And the message for Finding "No Alert for Corruption" was not displayed for Error 823 and 824 !!

After Alert setup:

And I verified by only setting up 823 and ignoring 824 and 825, still the sp_Blitz does not report for other 2 missing (824 and 825) !!


texts: Oracle: terrible execution times from virtual application server

texts:
I've been provided with an Oracle VM linux box playing host to an Oracle 11g installation. I've also been provided with an Oracle VM Windows Server 2008 64-bit box acting as an application server. Tasked with deploying my application to this environment, I've created / populated the DB, installed instantclient drivers and SQL*Plus on the application server and ran a few simple queries...

...and performance is terrible. A selection on 9000 rows takes 30 seconds.

N.B. the same SELECT * FROM ... ; query is being used as a baseline, executed from SQL*Plus with timing on, autotrace traceonly statistics and arraysize 5000.

Things I've tried:

HRPING the DB server from the application server - response time is around 0.5ms (slow, but not this slow)
Execute the query using SQL*Plus directly on the DB server - execution time is 0.2 seconds
Execute the query using the exact same instantclient drivers / SQL*Plus client from a different (physical) machine on the network - execution time is 0.2 seconds
Checked that tablespaces are not full
Checked that tmpfs (/dev/shm) space is not full
Check that no AV / firewalls are running

I'm convinced this is an infrastructure / networking issue, but the infrastructure admin insists it is a configuration / software issue.

Other factors:

The application is using manual IPv4 settings, and the DNS server was not specified... The local machine I used for testing had Automatic Settings. I've now requested for the correct details to be applied. Could this cause the issue?
There's an undiagnosed (unsolved) issue whereby if either the application server or the DB is restarted, it drops off the network and requires a shutdown/startup command to be issued via the Oracle VM client to bring it back
The application server came with Oracle PV drivers installed... I uninstalled to see if this would have any impact, but no difference has been observed

I'm all out of ideas. Any suggestions? Thanks in advance.

EDIT:

I'm currently looking into DNS settings. They were missing on the application server, and I'm not sure that they're correct on the DB server in /etc/resolv.conf either. I found a couple of resources suggesting that incorrect DNS settings could cause very poor performance.


texts: SQL Agent and DTSX SSIS package: How to get it to email or ftp the output file? (SQL Server 2016)

texts:
I have a dtsx, and can get SQL Agent to run it as a package on the file system.

So that drops the resulting xlsx to disk.

How can I get that xlsx emailed or sftp'd somewhere?


texts:
When using the PHYSICAL_ONLY option and NOINDEX with CHECKDB, my run times are longer!  But, on the weekend, it is 1/4 the time without those options.  Yes, there are more data changes during the week (like most systems) but from everything I have read using either option (especially both) is supposed to significantly decrease the run time.

Can someone help explain why this seems to be having the opposite affect?  We are running SQL Server 2012 SP2 and Windows 2012.  And I don't see anything obvious (yet) with the other service packs/cu's for SQL Server 2012.

Any suggestions would be much appreciated, I am at my wits end here.


texts: Restore database excluding FILESTREAM data

texts:
Context
We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB.

Out of these, approximately 8.5 GB is in a single table BinaryContent. As the name suggests, this is a table where we store simple files, of any kind, directly in the table as a BLOB. Recently we've been testing the possibility to move all these files out of the database to the file system using FILESTREAM.

We did the necessary modifications to our database without any problems, and our system is still working fine after the migration. The BinaryContent table looks roughly like this:

CREATE TABLE [dbo].[BinaryContent](
    [BinaryContentID] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](50) NOT NULL,
    [BinaryContentRowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL
) ON [PRIMARY] FILESTREAM_ON [FileStreamContentFG]
ALTER TABLE [dbo].[BinaryContent] ADD [FileContentBinary] [varbinary](max) FILESTREAM  NULL
ALTER TABLE [dbo].[BinaryContent] ADD  CONSTRAINT [DFBinaryContentRowGUID]  DEFAULT (newsequentialid()) FOR [BinaryContentRowGUID]

With everything residing in the PRIMARY file group, except the field FileBinaryContent which is in a separate file group FileStreamContentFG.

Scenario
From a developer's point of view, we would often like a fresh copy of the database from our production environment, to be able to work the the latest data. In those cases, we are rarely interested in the files stored in BinaryContent (now using FILESTREAM).

We have this almost working as we'd like. We back up the database, without the file stream like this:

BACKUP DATABASE FileStreamDB
FILEGROUP = 'PRIMARY'
TO DISK = 'c:\backup\FileStreamDB_WithoutFS.bak' WITH INIT

And restore it like this:

RESTORE DATABASE FileStreamDB
FROM DISK = 'c:\backup\FileStreamDB_WithoutFS.bak'

This seems to be working OK, and our system works as long as we avoid the parts that use the FileBinaryContent field. We can for instance run the following query without a problem:

SELECT TOP 10 [BinaryContentID],[FileName],[BinaryContentRowGUID]
--,[FileContentBinary]
FROM [dbo].[BinaryContent]

Naturally, if I un-comment the line above, including FileContentBinary in the query, I get an error:

  Large object (LOB) data for table "dbo.BinaryContent" resides on an
  offline filegroup ("FileStreamContentFG") that cannot be accessed.

Our system handles files where the content is set to null, so what I would like to do is something like this:

UPDATE [dbo].[BinaryContent]
SET [FileContentBinary] = null

But this of course gives me the same error as above. At this point I'm stuck.

Question
Is there any way I can restore the database without having to also restore everything from the FileStreamContentFG file group? Either by updating the values to null as I'm trying above, or default to to null when the file is missing or something?

Or am I perhaps approaching the problem in the wrong way?

I'm a developer by nature and does not have much knowledge as a DBA, so do excuse me if I'm overlooking some trivial thing here.


texts: Why VolumeQueueLength goes high for SQL Server in AWS

texts:
We have one SQL Server hosted on AWS EC2 machine. In the monitoring tab of one of the volume, we are seeing that VolumeQueueLength is periodically reaching to around 32 and then coming back.

So before troubleshooting that why it is reaching to 32, I want answer to few of my own questions:

On what factors VolumeQueueLength depends? Does it depends on volume's max IOPS capacity, IOPS happening at that moment, Volume throughput capacity, current throughput happening or anything else as well?
How do we correlate all these things and get some meaning info/conclusion out of it?  Like are we bottleneck in terms of IOPS or in terms of Throughput? For instance I have below info:

At 2:40 UTC
VolQueueLength = 31.6
VolumeReadOps = 912k
VolumeWriteOps = 8.78k
VolumeReadBytes = 41.2k
VolumeWriteBytes = 15.5k
Vol Type: gp2
Vol IOPS: 6144

Any help / inputs would be greatly appreciated. Need to have strong foundation in storage as a DBA.


texts:
I'm modeling a system where with vendors, each of which sells some overlapping subset of products available in the universe. Products (T-shirt) come in various variants (Small, Medium, Large) with generic (size) and vendor-specific (Item code) attributes. There is also vendor-specific information about the Products themselves which doesn't vary by size (e.g., their manufacturer).

I've got four tables involved:

VendorProduct (vendor_id, product_id, manufacturer)
Product (product_id, name)
Variant (variant_id, product_id, size)
VendorVariant (variant_id, vendor_id, item_code)

I'd like my database to enforce a constraint that every VendorVariant has an associated VendorProduct, which could be found with this query:

select * from vendor_variants
  inner join variants on variants.variant_id = vendor_variants.variant_id
  inner join products on products.product_id = variants.product_id
  inner join vendor_products on vendor_products.product_id = products.product_id AND
               vendor_products.vendor_id = vendor_variants.vendor_id

Can I do this in Postgres without changing the table structure? If no, are there tricks to model the data differently to allow this type of constraint?


texts:
I'm pulling values from RESTORE FILELISTONLY FROM DISK for multiple backup files into a temp table.
I am trying to insert a set value (@dbSet) to a column (@dbSetC) but when running my insert it's not allowing me.

I'm not sure how to run the INSERT command and have both the result of the EXEC in the first columns then the @dbSet value into the @dbSetC column.

create table #tmpA
(
    idx int identity(1,1),
    lpath nvarchar(260)
)

------------ ADD ALL DB Backup Files below ------------
insert into #tmpA (lpath)
    Select
    'C:\temp\db.bak' union select
    'C:\temp\db1'
------------ END EDIT ------------

create table #tmp
(
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0)
    ,FileId tinyint
    ,CreateLSN numeric(25,0)
    ,DropLSN numeric(25, 0)
    ,UniqueID uniqueidentifier
    ,ReadOnlyLSN numeric(25,0)
    ,ReadWriteLSN numeric(25,0)
    ,BackupSizeInBytes bigint
    ,SourceBlockSize int
    ,FileGroupId int
    ,LogGroupGUID uniqueidentifier
    ,DifferentialBaseLSN numeric(25,0)
    ,DifferentialBaseGUID uniqueidentifier
    ,IsReadOnly bit
    ,IsPresent bit
    ,TDEThumbprint varbinary(32)
    ,SnapshotUrl nvarchar(128)
--  ,dbSetC int
)

declare @countr as INT
declare @size as INT
declare @dbSet as INT
declare @tmpPath as nvarchar(260)

SET @countr=1
SET @size=(SELECT count(*) FROM #tmpA)

WHILE @countr <= @size
BEGIN
    SET @tmpPath = (SELECT lpath FROM #tmpA WHERE idx = @countr)
    SET @dbSet = (SELECT idx FROM #tmpA WHERE idx = @countr)

    INSERT INTO #tmp
        EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @tmpPath + '''') --need to add @dbSet to dbSetC column
        SET @countr = (@countr + 1)
END


texts:
I'm doing pretty serious operations on my DB and I would like to keep my data safe.

Unfortunately something is going wrong, look:

SAVEPOINT before_foo_update;

CREATE TABLE AUX_FOO as
   SELECT * FROM FOO;

TRUNCATE TABLE FOO;

ALTER TABLE FOO
  MODIFY(BAR NUMBER(11) NOT NULL);

INSERT INTO FOO
  SELECT * FROM AUX_FOO;

DROP TABLE AUX_FOO;

ROLLBACK TO SAVEPOINT before_foo_update;

Unfortunately I'm getting an error:

ROLLBACK TO SAVEPOINT before_foo_update
Error report:
SQL Error: ORA-01086: savepoint 'BEFORE_FOO_UPDATE' never established
01086. 00000 -  "savepoint '%s' never established"
*Cause:    Trying to roll back to a save point that was never established

Now FOO table is empty...
And AUX_FOO wasn't dropped.
Do you know what I'm doing wrong?


texts:
I have three tables which I have to join. I'm not a good SQL writer. Can please sombody help me to prepare this using joins:

branches
-----------------------------------------
branch_id | branch_name | branch_address
-----------------------------------------

branch_services
-------------------------------
bs_id | branch_id | service_id
-------------------------------

services
--------------------------
service_id | service_name
--------------------------

I need to select * from the branches table and service_names related to each branch.


texts: When are procedural queries absolutely necessary?

texts:
I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not give you the results?

I understand the difference between the two, I just have never come to a situation where I need to use a cursor.  I'm wondering if there are such situations.


texts: right join leaving one row from right table

texts:
I am using MariaDB,in my sample database I am having two tables department and emp.
I want to display all the department having less than 50 employees.
In this case it should display all the department names.

select  count(a.deptId),b.deptname
    from  emp as a
    right join  department as b on a.deptId = b.deptId
    group by  a.deptId
    having  count(a.deptId) <= 50 ;

I tried above mentioned query, it is returning correct count for the department but it is leaving the last deptname operations mysteriously.
Can anybody explain me why it is happening?
My understanding : As per right join the right side table's all row will be fetched and matched with the other table. If matches found result will be displayed otherwise NULL will be printed.

+--------+-------------+
| deptId | deptname    |
+--------+-------------+
|    501 | HR          |
|    502 | Accounts    |
|    503 | Development |
|    504 | Operations  |
+--------+-------------+

+--------+--------+------+-------+--------+
| emp_id | salary | name | bonus | deptId |
+--------+--------+------+-------+--------+
|      1 |    850 | NULL |    10 |    501 |
|      2 |    920 | NULL |  NULL |    502 |
|      3 |    550 | NULL |  NULL |    501 |
|      4 |    450 | NULL |     5 |    502 |
|      5 |    800 | NULL |  NULL |    501 |
|      6 |    920 | NULL |  NULL |    502 |
|      7 |    155 | NULL |  NULL |    501 |
|      8 |     50 | NULL |    20 |    501 |
+--------+--------+------+-------+--------+


texts: When is the SQL Server database ready to accept queries?

texts:
In the SQL Server error log file I found the following lines:

2018-02-22 14:10:58.95 spid17s     Starting up database 'msdb'.
2018-02-22 14:10:58.95 spid16s     Starting up database 'ReportServer'.
2018-02-22 14:10:58.95 spid18s     Starting up database 'ReportServerTempDB'.
2018-02-22 14:10:58.95 spid19s     Starting up database 'XYZ'.

If I check for the status of the database XYZ before this time, it is ONLINE using the following statement:

SELECT state_desc FROM sys.databases WHERE name='XYZ'

...but when I try to connect to this database using a C# application, it can't connect to the database.

The error is:

  Login failed for user 'asd'.
  Reason: Failed to open the explicitly specified database.

I tried three different users (Windows user, sa, SQL Server user defined for the application). The problem happens when I run the application in the start up of the OS, but if I start it manually after the start up, no errors happen, so I think all SQL Server settings and firewall settings are correct.

I also checked before this that the service status is running.

What else should I check to make sure the database is actually online and ready for queries?

I'm looking for a key that tells me it's ok to query the database, instead of delaying for a time (even not based on a clear reason).

I thought of scanning the error log for the text "Starting up database 'XYZ'", but this means I have to add a setting for the application for the path of the SQL Server error log. It also means reading the file many times until I find this phrase.


texts: Complex queries in CouchDB across multiple documents

texts:
I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something.

Assume the following (simplified) document examples:

{ "docType": "school", "_id": "school1", "state": "CA" }
{ "docType": "teacher", "_id": "teacher1", "age": "40", "school": "school1" }

I want to find all the teachers aged 40 in California.

Maybe my mind is still stuck on SQL world, but I'm having a hard time understanding how to run a query that uses data from different documents...

PS. let me know if this is more suited to StackOverflow...


texts:
The follow is a paragraph from Microsoft Docs:

  New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.

I can't figure out why this is the case. If I have a heap with a specified compression setting, why wouldn't it be applied to a page that belongs to the table?

Thanks


texts: MySQL Replication: MASTER_LOG_POS never changes

texts:
I have a Master / Slave Setup in production and from time to time I add or remove a slave and recently I also removed the master and promoted a former slave to new master.

This procedure usually involves dumping my master DB like so:

mysqldump -u root -pPASSWORD --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  > /data/dump.sql

and then reading the MySQL master log position from the dump like so:

head dump.sql -n80 | grep "MASTER_LOG_POS"

The output is something like

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=107;

(Source: http://plusbryan.com/mysql-replication-without-downtime)

Now as I have done this a dozen times I noticed that while the MASTER_LOG_FILE changes, the value for MASTER_LOG_POS is always 107 and never changes even though I have created new dumps from the master every time I add a new slave, and the dumps are weeks apart. This seems odd. I have not noticed any sorts of data corruption but should the value of MASTER_LOG_POS not change with every dump from the master?


texts:
I need to improve my database performance, in order to achieve that, I enabled the log in my postgresql.conf:

log_statement = 'all'
log_connections = on
log_disconnections = on

Why do I have 3 duration output in this SELECT ?

2016-02-29 15:35:07 CET [3492-349] postgres@uat LOG:  execute <unnamed>: SELECT ID FROM public.document WHERE documentID is NULL AND StatusID = (SELECT ID FROM public.status WHERE status = 'New') ORDER BY PriorityID DESC, ModTime ASC FOR UPDATE LIMIT 1
2016-02-29 15:35:07 CET [3492-350] postgres@uat LOG:  duration: 31.096 ms
2016-02-29 15:35:08 CET [3494-223] postgres@uat LOG:  duration: 0.090 ms
2016-02-29 15:35:08 CET [3494-224] postgres@uat LOG:  duration: 0.061 ms

EDIT

More information:

log_min_duration_statement = 2
log_line_prefix = '%t [%p-%l] %q%u@%d '

Another example:

2016-02-29 16:56:14 CET [2145-1225] postgres@uat LOG:  execute <unnamed>: SELECT ID FROM public.document WHERE documentID is NULL AND StatusID = 1 ORDER BY PriorityID DESC, ModTime ASC FOR UPDATE LIMIT 1
2016-02-29 16:56:14 CET [2139-9001] postgres@uat LOG:  duration: 48.190 ms
2016-02-29 16:56:14 CET [2139-9002] postgres@uat LOG:  duration: 0.022 ms
2016-02-29 16:56:14 CET [2139-9003] postgres@uat LOG:  execute S_3: COMMIT
2016-02-29 16:56:14 CET [2139-9004] postgres@uat LOG:  duration: 0.207 ms
2016-02-29 16:56:14 CET [2145-1226] postgres@uat LOG:  duration: 51.172 ms
2016-02-29 16:56:15 CET [2143-1691] postgres@uat LOG:  duration: 0.079 ms
2016-02-29 16:56:15 CET [2143-1692] postgres@uat LOG:  duration: 0.055 ms

With csvlog as output:

2016-02-29 17:06:28.475 CET,"postgres","uat",2317,"eniac:50690",56d46bee.90d,1765,"SELECT",2016-02-29 17:03:58 CET,15/442,0,LOG,00000,"execute <unnamed>: SELECT ID FROM public.document WHERE documentID is NULL AND StatusID = 1 ORDER BY PriorityID DESC, ModTime ASC FOR UPDATE LIMIT 1",,,,,,,,,""
2016-02-29 17:06:28.507 CET,"postgres","uat",2317,"eniac:50690",56d46bee.90d,1766,"SELECT",2016-02-29 17:03:58 CET,15/442,0,LOG,00000,"duration: 31.351 ms",,,,,,,,,""
2016-02-29 17:06:28.694 CET,"postgres","uat",2308,"eniac:50677",56d46bc2.904,3043,"PARSE",2016-02-29 17:03:14 CET,8/762,0,LOG,00000,"duration: 0.079 ms",,,,,,,,,""
2016-02-29 17:06:28.694 CET,"postgres","uat",2308,"eniac:50677",56d46bc2.904,3044,"BIND",2016-02-29 17:03:14 CET,8/762,0,LOG,00000,"duration: 0.053 ms",,,,,,,,,""


texts:
In our production system, we have shard cluster with -

2 shards (each one is replica set of primary + secondary + arbiter)

3 Config servers

4 mongoS instances

A couple of days back, we faced a network outrage in which a secondary failed to connect to its corresponding primary. This issue was resolved in some time but mongo balancer is stuck from that point of time.

All of the data is being moved to a single shard and nothing is transferred. In mongoS logs, we monitored that lock was acquired by a mongoS instance but not released till date -

    configsvr> db.locks.find({state:2}).pretty()
    {
    "_id" : "balancer",
    "process" : "mongosHost3:27017:45654643:-54656",
    "state" : 2,
    "ts" : ObjectId("57c29902a443254e2d8e1b27"),
    "when" : ISODate("2016-08-28T07:55:46.350Z"),
    "who" : "mongosHost3:27017:45654643:-54656:Balancer:37654778",
    "why" : "doing balance round"
    }

We tried restarting this MongoS instance but nothing changes. We tried stopping the balancer but that failed with error -

     Error: Error: assert.soon failed, msg:Waited too long for lock balancer to unlock

How can we resolve this balancer issue?

Is there any way we can manually relinquish this lock and re-enable balancer?


texts: Why the simple insert into query allocated a lot of tempdb?

texts:
I'm using sp_whoisactive to monitor the tempdb and found the following query allocated 30,000,000 tempdb_allocations (BTW, what's the unit of the allocations?)

select a.*, b.A, b.C, b.D
into c
from db1.dbo.a -- a, which has 2M rows, is a heap and don't have a index on column bid
left join db2.dbo.b on a.bid = b.id -- b has only several hundred rows. clustered PK (id)

Why it allocated so many tempdb space? What's the unit of tempdb_allocations?


texts:
Other than the obvious, what is the difference between:

drop user user_01@host01;

and

drop user 'user_01'@'host01';

In a sample test, they both achieved the same result. From Account Names docs (5th bullet point), looks like they are not the same ... yet they appear to do the same thing ... ?

MySQL 5.5 on Centos 6.x.


texts: How to use "regular expression" to separate specific strings in Oracle

texts:
I have a string '(1:30,2:4,52:0,8:1)', and I need to use a regular expression to have this output:

field1   field2  level
  1        30      1
  2         4      2
  52        0      3
  8         1      4

The query I've wrote so far is:

select distinct trim(regexp_substr('1:30,2:4,52:0,8:1','[^:,]+',1,level)) repfield,level lvl
from dual
connect by regexp_substr('1:30,2:4,52:0,8:1', '[^:,]+', 1, level) is not null
 order by lvl


texts:
I have a votes table that has many subitems. I have a subitems action table that records what action was performed by which user on which group, on which subitem, on which vote

Users can either cast/uncast/delete/update create on actions.

To find out if a user 'belongs' to a subitem, I used groupwise max.The logic is that among all the cast or uncast actions that has has performed on a subitem, if the latest timestamp is cast, then he has indeed voted on it. If it is uncast then he has not.

This is what the query looks like :

select  act.user_id,act.anonymous
    from
      ( SELECT  sub_item_id,user_id ,max(created_at) as newestAction
            from  vote_sub_item_action
            where  sub_item_id =10
              AND  user_id=910
              AND  action_type IN('ACTION_CAST_VOTE','action_uncast_vote')
            GROUP BY  user_id
      ) as x
    inner join  vote_sub_item_action as act on act.user_id = x.user_id
      and  act.created_at = newestAction
      and  act.action_type='action_cast_vote'
      and  act.sub_item_id =x.sub_item_id;

Now I would like to get all the subitems that 'belong' to him from a particular vote. The logic of belongs is the same as above.

How may I do that?


texts: Query is slow in application but fast in SSMS

texts:
stored procedure is running slow in application but fast in SSMS...
i have enabled SET ARITHABORT ON in stored procedure and it ran very fast from application in QA but still slowness in production ...we are running the stored proc from application in Read only database ( always on AG group )...

Things we have done so far:

Generated the both plans from plan cache (plan from SSMS and Application)
Cleared the plan for application procedure
Ran the procedure from application again but still it is slow and generating the plan with ARITHABORT as False.
Cleared both plans ( SSMS and Application ) and ran the procedure from application, still slow.
Altered the procedure , recompiled and created/dropped the procedure.
Stats were up to date.

Can anyone please help me on this ?


texts:
I am wanting to show these results vertical as opposed to the default horizontal.  My desired output is like this

  Passed w/ notes	   26
  Passed w/o notes	    28
  Passed w/ notes 	    52
  Passed w/o notes	    14

Here is my query - how should I write this so that the results are displayed vertically?

Select
COUNT(case when storenum = '1463' AND CAST(notes As VARCHAR(MAX)) IS NOT NULL THEN id else NULL end) As [Passed w/ notes]
,COUNT(case when storenum = '1463' AND CAST(notes As VARCHAR(MAX)) IS NULL THEN id else NULL end) As [Passed w/o notes]
,COUNT(case when storenum = '1638' AND CAST(notes As VARCHAR(MAX)) IS NOT NULL   THEN id else NULL end) As [Passed w/ Notes]
,COUNT(case when storenum = '1638' AND CAST(notes As VARCHAR(MAX)) IS NULL THEN id else NULL end) As [Passed w/o notes]
FROM monster.dbo.storesales

I looked at using a PIVOT and from my readings syntax would be something PIVOT(SUM(ID) FOR fieldname IN ()

But I do not have a specific fieldname to use for the "FOR" clause of my pivot.  How would I work around that?