texts: Problem in PostgreSQL replication - ALTER DATABASE

texts: Recently I put to work a Master/Slave replication in my PostgreSQL (9.3). Everything is ok... insert, update, delete, in data and changes in tables... but when I changed the data base name the replication not works. The name of the Slave database has not changed. How can I configure the master/slave replication to do this (replication of alter database rename)?


texts: SQL 2016 SP1 Mirroring - slow database list refresh and repetitive automatic failovers

texts:
I recently installed SQL Server 2016 SP1 Standard on two servers and 2016 SP1 Express as a Witness on a third server.  Firewall is off and the servers can ping each other.  I created an empty database on afserver1 (full recovery model), performed a full backup, and restored with norecovery on afserver2.

The first issue I'm seeing is that on afserver2 when I refresh the databases for the first time after restore, two identical database entries show up in the list.  Refreshing again corrects it.

It also takes about 10 seconds to refresh the database list on the secondary even though there is only two db's on the server.  The primary always refreshes the db list instantly.  In the log file on the secondary after the refresh shows a number of these errors:

  Login failed for user 'DOMAIN\USER'.
  Reason: Failed to open the explicitly specified database 'MirrorTest'.
  Error: 18456, Severity: 14, State: 38.

It seems odd that simply refreshing the database list would result in 6 access errors in the logs for a database in a restoring state, but maybe that's normal?

When I go through the wizard to set up the mirror, it succeeds immediately.  However, when I start mirroring it takes a very long time to start (and this is an empty db with no transactions).  It will finally start but after a short time will fail over to the secondary.  In the logs after about a minute, I start seeing entries for both the secondary and witness server:

  Database mirroring connection error 4
  'An error occurred while receiving data:
  '10054(An existing connection was forcibly closed by the remote host.)'.'
  for 'TCP://AFSERVER3.domain.local:5022'.
  Error: 1474, Severity: 16, State: 1.

It appears the database is failing back and forth between afserver1 and 2, with a number of messages in the logs about database mirroring being inactive and then active again.  It feels like a communications problem but the servers are on the same subnet with the firewalls turned off.

Any ideas for what could be causing not only the slow refresh of the database list and the back and forth failover of the database?

I've verified that the account has CONNECT permission to the endpoint on each server.  I'm using domain accounts for everything on these servers.


texts:
I have 2 Tables namely Area_ Table and PingData_Table

Area Table has the list of the areas along with the Area ID and Area Name

 AreaId | AreaName |  isActive
   1    |Jumbo House| 1
   2    | DCC       | 1
   3    |Dubai Mall | 1

Ping Table has the ping made by devices installed on different area location with a different ID. I ping each device every 5 mins and update the status in the table.

  Please note that no row is inserted in the table if the ping is
  unsuccessful which means the device is inactive

pkey  | AreaId  |   UpdatedOn
411   |   1     | 2018-08-08 21:54:24.810
547   |   1     | 2018-08-08 10:39:21.987
1046  |   1     | 2018-08-08 00:09:57.843

Now I want to show the above data in the following format.

Status of devices for 8 August 2018

Areaid | Name           |   Status
1      |Jumbo House     | Active
2      |DCC             | Not Active between 00:00 hrs to 23:00 hrs
3      |Dubai Mall      | Not Active between XX:00 hrs to XX:00 hrs

for all the Areas.

What I have tried till now is:

CREATE TABLE #TimeTable (hrs DATETIME)
CREATE TABLE #pingData (pid INT , areaId INT , pingTime DATETIME)
DECLARE
  @start DATETIME = '2018-08-08 0:00',
  @end   DATETIME =  '2018-08-08 23:00';
WITH x(n) AS
(
  SELECT TOP (DATEDIFF(HOUR, @start, @end) + 1)
  rn = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_columns ORDER BY [object_id]
)
INSERT INTO #TimeTable(hrs) SELECT t = DATEADD(HOUR, n-1, @start) FROM x ORDER BY t;
INSERT INTO #pingData ( areaId  , pingTime )
SELECT DISTINCT [AreaId],CAST(FORMAT([UpdatedOn],'yyyy-MM-dd HH')+':00:00.000' as datetime) as pingtime
FROM [dbo].[Pingtable]
where UpdatedOn > @start and UpdatedOn <= @end
select  a.areaId , p.pingTime,t.hrs , a.AreaName , CASE WHEN  p.pingTime IS NULL THEN 'INACTIVE' ELSE 'ACTIVE' END as DeviceStatus
from Area_Table a
left  join #pingData p on a.areaId = p.AreaId
left   join #TimeTable t  on p.pingTime = t.hrs
ORDER BY a.areaId,t.hrs
drop table #TimeTable ;
drop table #pingData ;

Please guide me as to how to get the desired result.


texts: Extended Event Reported Error Login Timeout

texts:
While poking around for a specific event I found myself discovering an error happening every second.
At the moment I cannot find any more information about the error as it only gets displayed in the extended events and no information is recorded either in the error log or event viewer. I can't also seem to find any application side effects that could potentially help me troubleshooting.

Has anyone seen such thing ?
Any ideas how to start troubleshooting this ?

@@Version / OS 2012 R2
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
    Aug 15 2017 10:23:29
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Error reported

message Network error code 0x2746 occurred while establishing a connection; the connection has been closed. This may have been caused by client or server login timeout expiration. Time spent during login: total 2 ms, enqueued 0 ms, network writes 0 ms, network reads 1 ms, establishing SSL 0 ms, network reads during SSL 0 ms, network writes during SSL 0 ms, secure calls during SSL 0 ms, enqueued during SSL 0 ms, negotiating SSPI 0 ms, network reads during SSPI 0 ms, network writes during SSPI 0 ms, secure calls during SSPI 0 ms, enqueued during SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms. [CLIENT: ::1]


texts: configure remote connection for multiple instances sql server 2008

texts:
I've installed 2 instances of SQL Server 2008 R2 onto our local windows server.  For the purposes of testing I've turned off the firewall.  I'll get the firewall back up after I manage a successfull remote connection. The server isnt exposed anyway.

TCP\IP is turned on for both instances.  The default port is assigned to the Default instance and that one is working fine.

I've assigned 4143 to the named instance but I'm still unable to connect remotely.

I can connect remotely to the default instance just not the named instance.  What step or steps am I missing?


texts: Is there a way to have MySQL GROUP BY utilize trailing whitespace?

texts:
I recently had an issue come up where I noticed that MySQL would ignore trailing whitespace when issuing a GROUP BY. Here's the table:

CREATE TABLE `myTable` (
 `id` int(10) unsigned NOT NULL,
 `foo` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And here's an example query I ran (not a production use, but shows what I'm trying to fix):

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY foo

Which would return a result set:

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 4 |

All well and good, except 'Bar' is length three, not four. So I went to examing the individual entries (No group by):

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 4 |
| Bar  |                 3 |

Now, there are five entries for Bar, four of which correctly report a length 3. But there's one that reports a length of four.

It turns out the one with length four has a space on the end: "Bar ". I would have expected GROUP BY to treat this as a completely different value and not lump it with the other "Bar"s.

Is there a way to tell MySQL to consider values different if they have trailing (or leading) whitespace?

(running mysql 5.1.55 64-bit on mac osx)


texts:
How can I use count(*)>0 in a where clause of a delete statement?

My scenario is  if count(*)>0 then delete the table1.  I've tried this code:

delete table1 where c='n' and having count(*)>0;

But it's getting an error.


texts: Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?

texts:
Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my question.

Basically I'm working on a new feature for an existing system and am powerless to change the existing schema. So let's say that there currently is a Users table which has a primary key UserID that is of type uniqueidentifier. I want to create a new table called Alerts - related to the Users table in a one-to-many relationship (one User can have many Alerts). Alerts will be regularly queried by this foreign key. I will expect to index this field for performance.

My question is - from what I understand about Indexing strategies, indexing a uniqueidentifier column is (delicately put) not a good idea - so would it be a better idea to have an intermediary table to map these uniqueidentifier keys into a better indexing candidate like an integer?

Users

UserID uniqueidentifier {PK}
Username varchar(50)

Alerts

AlertID int {PK}
UserID uniqueidentifier {FK}
Message varchar(250)
Timestamp datetime

OR

Users

UserID uniqueidentifier {PK}
Username varchar(50)

UserMap

UserKey int identity(1,1) {PK}
UserID uniqueidentifier {FK}

Alerts

AlertID int identity(1,1) {PK}
UserKey int {FK}
Message varchar(250)
Timestamp datetime


texts: Is a relation with two attributes always in Boyce-Codd Normal Form (BCNF)?

texts:
I went through some tutorials on BCNF and got that a relation with two attributes is always in BCNF.
My question
What if a relation with two attributes has no prime or non-prime attributes? That is, the attributes A and B do not determine each other and are simply in 1NF. From 2NF onward, the question of prime and non-prime attributes appear.
Anybody there to clarify the matter? Thanks in advance.


texts:
How can I put an sql query result in a string array with Postgres?
I have some attributes in the column 'zone' of the table 'mytable' and I must put them all in a String[] areas;
This is my first request, sorry if I made some format mistake.


texts:
I've been reading about the table_open_cache and I'm slightly confused about how big it should be, I've seen conflicting advice. My DB is running on MyISAM and has 199 tables.

I ran into an issue months ago whereby the server was running out of file descriptors, so I've increased the number of file descriptors to 2500 (from default), and the table_open_cache to 1000, and max connections to 500 (probably too high).

I've read this article on negative scalability (https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/) and now I'm wondering if by increasing these values, performance has been impacted.

I've also ran MySQLTuner and my table cache hit rate is 0%, it shows 1K tables open vs 1M opened. I'm also only using 47% of available the available file limit.

Could anyone point me in the right direction?


texts: How to specify not null contraints for the fields of composite types in postgres

texts:
Let's say I would like to have a composite type for address, like:
create type address as (
  city text,
  address_line text,
  zip_code int
);

And to make data integrity better, I don't want to allow NULLs to be members of city, address_line, or zip_code. So I would like to have a not null constraint for those fields.
Creating domain checks isn't working for me. So this code produces error:
create domain address_domain as address
check (
  value.city is not null and
  value.address_line is not null and
  value.zip_code is not null
);

You might say: "Well, why won't you store address as three columns, and add contraints to the fields?". And I will answer with that I would like to have ability to make address itself nullable, but if address is present, all of it's fields should be present as well. Something like this:
create table companies (
  id serial primary key,
  name text not null,
  headquaters address -- this one can be null tho
)


texts:
I need to write a MS SQL Server query statement that will generate the result set table name SUMMARY from the DETAIL table

Server name: Development
Database name: Test

                  DETAIL
STATE     Total BadEggs  GoodEggs   Unknown
Kansas      5      2        2         1
Texas       10     5        4         1
Texas       2      0        2         0
Georgia    20      5        5        10
Maine      25     15        5         5
Florida     5      1        2         2
Texas       8      4        2         2
Georgia     9      3        4         2
Maine       5      2        1         2
Florida    11      1        5         5
Texas       2      2        0         0
Florida     5      2        1         2
New York   20      5        5         10
New York    6      2        2         2
Alabama    10      3        4         3
Alabama    30     12       15         3
Alabama     4      1        1         2
Alabama     8      3        2         3
       185

                  SUMMARY
STATE     Total  BadEggs   GoodEggs Unknown
Kansas      5        2          2   1
Texas      22       11          8   3
Georgia    29        8          9   12
Maine      30       17          6   7
Florida    21        4          8   9
New York   26        7          7   12
Alabama    52       19          22  11
Total     185       68          62  55


texts: Improve performance for order by with columns from many tables

texts:
Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two columns takes 5 minutes). E.g.:

select r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
from requests r
inner join samples s on (s.code = r.sample_code)
order by s.barcode  asc , r.code asc
limit 21;

Table Info:

CREATE TABLE public.samples (
  code BIGINT NOT NULL,
  barcode VARCHAR(40) NOT NULL,
  registry_date TIMESTAMP WITH TIME ZONE NOT NULL,
  CONSTRAINT samples_pkey PRIMARY KEY(code)
);

CREATE INDEX idx_samp_barcode ON public.samples (barcode);
CREATE INDEX idx_samp_barcode_code ON public.samples (barcode, code);
CREATE INDEX idx_samp_barcode_code_desc ON public.samples (barcode DESC, code DESC);
CREATE INDEX idx_test_identifier_desc ON public.samples (barcode DESC);

CREATE TABLE public.requests (
  code BIGINT NOT NULL,
  test_code INTEGER NOT NULL,
  sample_code BIGINT NOT NULL,
  CONSTRAINT requests_pkey PRIMARY KEY(code),
  CONSTRAINT "Requests_S_fk" FOREIGN KEY (sample_code)
    REFERENCES public.samples(code)
    ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE,
  CONSTRAINT "Requests_T_fk" FOREIGN KEY (test_code)
    REFERENCES public.tests(code)
    ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE
);

CREATE INDEX idx_req_test_code ON public.requests (test_code);
CREATE INDEX idx_req_test_code_desc ON public.requests (test_code DESC);
CREATE INDEX request_sample_code_index ON public.requests (sample_code);
CREATE INDEX requests_sample_code_desc_idx ON public.requests (sample_code DESC, code DESC);
CREATE INDEX requests_sample_code_idx ON public.requests (sample_code, code);

Each table has 1 million rows.
All rows in both tables are distinct.
If I order by either column alone, the execution time is ~ 30 ms.
There is no sample without request.
A sample can have many s.code for the smaes.barcode.

How can I improve the performance?


texts: Selecting Consective rows with same Value & calcute the difference between timestamps of same value rows.Please Check What will b error in this query?

texts:
I have a table in which there are two columns
A_MSISDNs and Incoming_timestamp which is the time of call.

I want to calculate the time duration between two calls of same MSISDN
e.g:-

A_MSISDNs        Incoming_timestamp
420000128321     2018-06-26 06:26:02
420000128321     2018-06-26 07:04:44

The Time Duration between Calls by above Same MSISDN is 28 minutes.

For this purpose I made query which is giving error  1064

Kindly correct my query if there is any syntax error and logic error

SELECT MSISDN,total_count,TIMESTAMPDIFF(MINUTE, MIN(times), MAX(times)) AS duration

FROM (

SELECT t1.*
, @groupNumber := IF(@prev_MSISDN != t1.MSISDN, @groupNumber + 1, @groupNumber) AS gn
, @prev_msisdn := t1.MSISDN
FROM (

(#t1
#[ itwill returns the msisdn,incoming_timestamp of all msisdn and total count and order them according to their msisdn ,timestamp. We do this beacuse we want to retrive the different timestamp of calls of the same MSISDN
SELECT tb1.a_msisdn AS 'MSISDN',tb1.incoming_timestamp AS 'Times',tb2.total_count AS 'Total_counts'
FROM cc_info  AS tb1
JOIN
#{it will count the msisdn whose total_count>=10
(SELECT COUNT(1) AS 'Total_count',a_msisdn
FROM cc_info
GROUP BY a_msisdn
HAVING Total_count>=10
ORDER BY incoming_timestamp) AS tb2
#}
ON tb1.`a_msisdn`=tb2.a_msisdn
WHERE tb1.a_msisdn=420000128321 #it is for testing
ORDER BY tb1.a_msisdn ,tb1.incoming_timestamp
#]
) AS t1
,(SELECT @groupNumber := 0, @prev_MSISDN := NULL) var_init_subquerysa ORDER BY MSISDN,times

) AS t2
)
GROUP BY gn,MSISDN


texts: Can SQL Profiler filter data columns only for one event?

texts:
I am using SQL Profiler in MS SQL Server. I can filter data columns by clicking 'Column Filters'. But this filter applies to all events. I want to filter login only for 2 events. Like, audit login and logout only for specified login, but all other events for all logins. Is it possible to do that, and how if yes.
Thanks for your time!


texts: Postgresql - Do Foreign Key Constraints Automatically Create Indexes?

texts:
Per the PGSQL 9.3 docs:

  Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

Yet after I create such a constraint, when I run \d <table> I see that an index does in fact get created:

Indexes:
    "table_primary_key" PRIMARY KEY, btree (id)
    "fki_table_product_foreign_key" btree (product)

What could explain why this gets created? Do I still need to create another index on my product column to improve performance of queries on it?


texts: Is disabling triggers to disable foreign key constraints a good idea?

texts:
Background

We're moving a legacy application from a non-relational database to PostgreSQL.

Problem

We'd like to enable foreign keys on a lot of fields for future data integrity and so that reporting tools can easily determine which tables are linked. Some of the current data won't pass the FK check and because of the legacy app connecting to the database we can't turn on the FKs yet.

Temporary Solution

Some of the programmers are suggesting that we disable triggers on the database as shown here so that the FKs aren't enforced but will still be there for reporting tools.

Question

I'm afraid that this will cause a bunch of other un-intended consequences but I'm not sure what they are. Can anyone put my fears to rest or explain why this is a really bad idea? I'm assuming that one reason it is bad is that it would disable all triggers not just triggers that fire the FK constraints.


texts: Delete User View named the same as system view

texts:
I am exporting a database to Azure and have found a user view created in a previous version of MSSQL (Currently running 2008) that is name INFORMATION_SCHEMA.TABLE_CONSTRAINTS which obviously should be impossible.  I can find the objectid in sysobjects but can find no way to drop the view by anything other than it's name.

Trying to drop the view this error is generated which makes sense:

  Msg 3701, Level 11, State 5, Line 1 Cannot drop the view
  'INFORMATION_SCHEMA.TABLE_CONSTRAINTS', because it does not exist or
  you do not have permission.

But the object in sysobjects and it's corresponding definition are definitely user based, not what one should expect.

Any thoughts?

Pics:


texts: Alternative to MS Acess for in-house user database

texts:
I am a working student at a bank and my current task is to create an in-house database for customer-specific lookups which often rely on complex underlying data aggregation from different sources. I was expecting to get access to something like a SQL Server or similar in order to implement this but was left with the challenge to do it without a dedicated DB or server to work with. My goal is to implement the most sustainable solution and not do something like a quick'n dirty fix.

I am always up for a challenge but certain things like data security/integrity and multi-user access popped up when I thought of implementing it all with MS Access.
On top of that I worry that it might become messy and also cause some drama should I eventually leave the company.

There are of course dedicated things like an Oracle Data Warehouse but using this will be problematic as other departments are denied access to it and therefore will not be able to use it.

Next thing that came to mind was using something like a portable DB. I downloaded PostgreSQL Portable and it works great, but since I am pretty much the only one fluent enough in SQL to use it, it would require some kind of frontend like Access yet again.

To outline my situation: I have access to my own computer and Network Storage with daily backups. I, as for now, do not have the ability to leave my laptop on all the time and have no access to a dedicated server.

On top of being a DB, I would like it to update and load data from other sources at regular intervals, but I could also automate that via PowerShell scripts if needed.

My questions are as follows:

Is using something like MS Access a smart or rather stupid idea? Are there any points I was missing in my assessment?
Are there any free alternatives I could use for my needs?
Is the plan in its current state even doable or should I rather sit down and negotiate about access to more resources?

I know that the questions are more subjective than I would like but I don't want to upset our DBA's and I think most of you guys have faced similar situations and know what would have been a far better solution.
I appreciate any input.


texts: how to filter the resulted table in dbeaver?

texts:
I have big sqlite table that I filtered one column with IN statement from the GUI. It worked will but I want to filter another second column also based on the first selected. eg full table looks like.

A    v
A    c
A    n
B    o
B    i
B    k
C    l
C    a
C    z

after first query

A    v
A    c
A    n
C    l
C    a
C    z

I want to filter this second column only (without the o, i, k available in selection menu).
I found the  best solution is the CREATE TABLE, name it, then use it.

However, I don't know where this table is saved. I can't find it in the explorer however it is there when I SELECT it.

What would you do if you have to do that?


texts: Update end date by adding duration to start date in derby database

texts:
I created table using Derby database.
CREATE TABLE PROJECT
(PID  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
PSDATE DATE,
PDURATION INTEGER,
PEDATE DATE,
PRIMARY KEY (PID)
);

I attempted to update end date in this project table, adding duration to start date.
UPDATE PROJECT
SET PEDATE = ADD_MONTHS (PROJECT.PSDATE, PROJECT.PDURATION)
WHERE PID = PROJECT.PID;

But I am getting below error
"[Exception, Error code 30,000, SQLState 42Y03] 'ADD_MONTHS' is not recognized as a function or
 procedure.
 Line 1, column 1"

Later I tried with below code
select {fn TIMESTAMPADD(SQL_TSI_DAY, +pduration, psdate)}
from name.PROJECT
where pid = project.PID

Can I use TIMESTAMPADD function to update project table in Derby database?
Thanks in advance to help me to solve this problem.


texts:
My question is based on the answer given here

 Attendance:
id | student_id | date       |
------------------------------
1  | 2          | 2013-10-24 |

Students:
student_id | student_name   |
-----------------------------
1          | Johnny Johnson |
2          | Bobby Tables   |
3          | Suzie Smith    |
To get a report of all the kids at your school and their attendance status for the day, you could run a query like:

SELECT s.student_id, s.student_name
,  CASE WHEN a.id IS NOT NULL THEN 'Absent' ELSE 'Present' END as attendance_status
FROM Students s
LEFT JOIN Attendance a on s.student_id=a.student_id
WHERE a.date = '2013-10-24'
Which would return:

Result :
student_id | student_name   | attendance_status |
-------------------------------------------------
1          | Johnny Johnson | Present           |
2          | Bobby Tables   | Absent            |
3          | Suzie Smith    | Present           |

I have a similar query but it is not returning null values in this case present but its only retunring absent

Result that I am getting :
student_id | student_name   | attendance_status |
-------------------------------------------------      |
2          | Bobby Tables   | Absent            |     |

My query looks like SELECT
student.StudentID,
student.Name,
CASE WHEN attendance.StudentID IS NOT NULL THEN 'Present' ELSE 'Absent' END as Attendance_Status
FROM
student
Left JOIN attendance ON student.StudentID = attendance.StudentID
where
attendance.Date = '2015-03-30'


texts:
We have a staging server and a production server, both running their own MySQL server. Data on the production MySQL server changes all the time.

Occassionally we want to update the data on the staging MySQL database using data ob the production MySQL server. This allows us to play around on the staging server with an updated copy of the production data.

What is the recommended way of performing this clone/sync from a live server to another?


texts: Lost connection to mysql while restoring dump. Is it safe to resume?

texts:
I was in the process of restoring an sql dump to a newly created RDS mysql instance on AWS.

The dump was created with the following command:

mysqldump
--all-databases \
--single-transaction \
--compress \
--order-by-primary > dump.sql

During the restore process I lost connection to the RDS instance.

Can I simply restart the restore process of the dump by re-initiating the restore process?

Or, asking more generally:

What excactly happens when restoring an sql dump?
Will already created tables simply be overwritten? Will the database delete old entries and copy over the new database state?


texts: Can I avoid a join for my select procedures?

texts:
Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory):

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT `first_name`, `last_name`, `hashID` FROM MYDB.person;

  SELECT`id`, `id_status`, `criminal_status`, `hashID` FROM MYDB.id_crimes;

  SELECT `stateID`, `stateID_status`, `dob`, `hashID` FROM MYDB.dob_state;

  SELECT `create_time`, `update_time`, `hashID` FROM MYDB.timestamps;

  COMMIT;
end//
delimiter ;

What I'm looking for is the absolutely most concise way to select a record in its entirety, keeping it as a procedure. I'd really like to avoid joins on the tables, but I don't really see any way around doing a join on the hashID, given the abovementioned restrictions. Is there another way? Pointers? What would that look like in a procedure? I'd like to see someone's concise correction of what I want to do. :)
Thanks!


texts: MariaDB: How can I grant read-only privileges for Stored Procedures?

texts:
I've granted SELECT privileges for a user to select on all tables of a database. However that user is unsable to view store procedures.
I'd want to grant only the permission for SHOW CREATE PROCEDURE (or something alike).
Is that possible on MariaDB 10.x?


texts: SQL Server - SQL Agent - Allow a Group to own a job

texts:
Scenario
There are three separate groups of users in our organisations (development, support, implementation). As it stands everyone is using a single login for everything. We are starting to move away from this. Everyone is accessing the database though their own login where each person has access to their groups systems. So Support has access to support functions and jobs etc.
The allowed permission for the procedures and tables are really easy. Create a login from the windows group. Then create an user based on that login and allow it to read and execute on the new schema that we're creating for them.
We are running SQL Server 2012 and 2008 R2.
Problem
The SQL Agent Jobs sits above the user level and requires a login to be assigned to the owner. But it doesn't take a group login as an accepted parameter:

The specified @owner_login_name is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).

The hope is to allow the groups to have their own set of jobs that they can manipulate directly (on a reporting server not the live database). Without effecting others departments work or the other jobs (DB maintenance tasks, replication etc). But allowing other members of a team to manipulate a job if the original writer is out of the office or has left.
I've tried searching around but so far the only thing I've found on sqlservercentral.com from back in 2010 saying it can't be done.
Things I'm looking at
Having the SQL Agent job assigned to a support / devopment  generic account that nobody has access to. But everyone somehow has access to manipulate their jobs.
This would allow people to alter the group's jobs while allowing jobs to continue being used after an individual leaves the company.
I've not found a way for people to be able to access / run jobs by that have other owners (even when they are using the same windows group login to sql). Without granting them SQLAgentOperateorRole (which would defeat the point in locking them out) of each other's (and the admin's) jobs.
Create a user per group that has the added access of the msdb rights along with the standard support abilities that they can log into to manipulate jobs.
The disadvantage of this is now the users have two logins one of which won't change. So an user leaving the company would still have the login credentials for that account potentially, it would also lead to users being lazy and only ever logging in under that account. (They shouldn't but let's be honest, they will).
Having a SQL job that runs every 5 minutes, each group has a table with commands for run this command at frequency x (essentially one SQL agent job runs a custom written SQL Agent list of jobs).
I don't think I need to go through how horrible an idea this is. But it's something that has semi been considered.
Overview
Obviously the best solution that we can see for us, would be to allow a group to own a SQL Agent job but it doesn't seem like this is possible.
Anyone have any ideas? For now guidance on how to implement something like this would be appreciated.


texts: How to create a new database in PostgreSQL 10 in Windows from batch file?

texts:
I am creating a batch file to create a new database in PostgreSQL 10. I used the following code,

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
psql "dbname=myDB host=localhost user=postgres password=postgres port=5432"
pause

But I get this error,

  psql: FATAL:  database "myDB" does not exist

I understand this is not the command to create a new database. I also tried the following code,

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
psql "createdb myDB passfile %APPDATA%\postgresql\pgpass.conf"
pause

This is based on the information in this link. The "pgpass.conf" file has the follwoing text,

  localhost:5432:myDB:postgres:postgres

But it asks to enter password. I entered the default password "postgres". But this was the output,

  psql: FATAL:  password authentication failed for user "User1"

The objective here is to create new database from the batch file without asking for password. How to achieve this?


texts:
Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB).

Next, I have a second table, but this time it's restricted to entries in table A that have ThingB = 3. The primary key is ThingA, because ThingB is a constant of 3.

Initially, I had thought I could simply:

FOREIGN KEY (ThingA, 3) REFERENCES A(ThingA, ThingB)

But I've learned that's not the case, and I have to create a column for the ThingB:

ThingB INT NOT NULL DEFAULT(3) CHECK(ThingB = 3)

Then,

FOREIGN KEY (ThingA, ThingB) REFERENCES A (ThingA, ThingB)

Is there an alternative to this that doesn't require an extra column, or the DEFAULT + CHECK? One alternative is a persisted, computed column, but I hate that idea too as it's basically a cheat and still adds a new column with physical storage. While on it's own, the INT won't be big, there are several million rows that need it across several tables, and I'd rather not maintain the extra columns.

Here's sample DDL to illustrate the situation:

CREATE TABLE Test1
(
    ThingA INT NOT NULL,
    ThingB INT NOT NULL,
    PRIMARY KEY (ThingA, ThingB)
);

CREATE TABLE Test2
(
    ThingAVal INT NOT NULL,
    ThingBVal INT NOT NULL DEFAULT(3) CHECK(ThingBVal = 3),
    Val INT NOT NULL,
    FOREIGN KEY (ThingAVal, ThingBVal) REFERENCES Test1 (ThingA, ThingB)
);

And I've created a db<>fiddle that demonstrates my (current) solution:

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=edba40478f104ac017c3444138a07762

If the answer is "No", I'll accept it, but I'm curious if there are any other alternatives.


texts: Report Server ( [162] Internal request to deactivate schedule ) error ( SQL Server 2008 R2 )

texts:
This is the error:

  [162] Internal request (from SetJobNextRunDate [reason: schedule will
  not run again]) to deactivate schedule N

The Image:

Has anyone seen this? I have no idea what is this error.

When we Schedule a task to send an e-mail with report server, it shows me this error in error log.

There are a lot of websites saying it is a SQL 2005 BUG. But I can't find nothing to help me with SQL Server 2008 R2.

EDIT 1

Digging a little more...I could find this:


texts:
I am facing an issue as follows:
I login to em using SYS as SYSDBA. Enterprise Manager shows information screen saying my password is going to expire. SYS Password will expire on 7th Feb 2017 as confirmed with Sql Plus:

Now on EM side, when I go to preferences -> General -> Password and try to change my password, it says Current Password - Current password is invalid.

I am providing same password I used to login to EM as SYSDBA but no luck.

Can someone advice what the problem is? How can I access database from EM in this situation. Also, please briefly advice about how to extend password expiry date.

DB Version: Oracle 11g


texts: ORDER BY too slow in Foreign Table using postgres_fdw

texts:
PostgreSQL v9.6, postgres_fdw

Foreign table

CREATE FOREIGN TABLE user_info (
  id bigint ,
  info jsonb
)
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'user_info_raw' );
-- user_info_raw is a large table (100 million records, 200 GB)

Sample data of info  column

{"key1": 1, "key2": 0.678}
{"key1": 1, "key2": 1.0}
{"key1": 1, "key2": 0.986}
{"key1": 2, "key2": 0.75}
{"key1": 2, "key2": 0.639}

Query on foreign table  (updated)

SELECT id, info
FROM user_info
WHERE info ->> 'key1'= '1' -- OR using jsonb_extract_path_text(info, 'key1')  = '1'
ORDER BY id
LIMIT 10;

Limit  (cost=10750829.63..10750829.65 rows=10 width=40) (actual time=550059.320..550059.326 rows=10 loops=1)
   ->  Sort  (cost=10750829.63..10751772.77 rows=377257 width=40) (actual time=550059.318..550059.321 rows=10 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Foreign Scan on user_info (cost=100.00..10742677.24 rows=377257 width=40) (actual time=1.413..536718.366 rows=68281020 loops=1)
               Filter: ((info ->> 'key1'::text) = '1'::text)
               Rows Removed by Filter: 7170443
 Planning time: 4.097 ms
 Execution time: 550059.597 ms

Query on user_info_raw

EXPLAIN ANALYSE
SELECT id, info
FROM user_info_raw
WHERE info ->> 'key1'= '1'
ORDER BY id
LIMIT 10;

 Limit  (cost=0.57..1296.95 rows=10 width=59) (actual time=0.043..0.073 rows=10 loops=1)
   ->  Index Scan using idx_user_info_raw_info on user_info_raw  (cost=0.57..68882850.88 rows=531346 width=59) (actual time=0.042..0.070 rows=10 loops=1)
         Filter: ((info ->> 'key1'::text) = '1'::text)
 Planning time: 0.192 ms
 Execution time: 0.102 ms

select pg_size_pretty(pg_table_size('user_info_raw'));
 pg_size_pretty
----------------
 223 GB

It takes 10 milliseconds to execute the query on the user_info_raw (remote server).

But, It takes a lot of time when using theuser_infoforeign table. When I remove ORDER BY id, the query executes very fast.

I think that my query on foreign table should send to the remote server for executing, but it's not, I don't know why, may be due to of this excerpt from postgres_fdw document

  By default, only WHERE clauses using built-in operators and functions
  will be considered for execution on the remote server. Clauses
  involving non-built-in functions are checked locally after rows are
  fetched. If such functions are available on the remote server and can
  be relied on to produce the same results as they do locally,
  performance can be improved by sending such WHERE clauses for remote
  execution

How can I fix the issue related to ORDER BY in foreign table  ?

UPDATED

Adding use_remote_estimate on server and foreign table doesn't help.


texts:
I'm trying to delete all users but getting the error:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_M02ArticlePersons_M06Persons". The conflict occurred in database "workdemo.no", table "dbo.M02ArticlePersons", column 'M06PersonId'.
The statement has been terminated.

The query:

DELETE FROM [workdemo.no].[dbo].[M06Persons]
WHERE ID > '13'
GO

Seems I need to use on delete cascade; but I'm stuck.


texts:
I've just installed the last version of free Oracle (11 XE) and I would like to run some queries. I've also installed the DreamCoder IDE to connect to the Oracle but I cannot run any query. Already trying for 2 hours with no success.
The oracle service is already running.

In the new connection menu in DreamCoder asks for:

username: admin
password: ***

Now I have 2 options:

First: TNS
Database: XE (it's already set by default)
Connect as: [normal, SysDba, SysOper, SysASM] I select the SysOper option.
Instaled clientes - Connect using: XE (the only option available)

Second Direct
Host name: localhost
Port: 1521
Database name: XE (from the TNS)

When I test the connection with the TNS I get connection successful; Then when I really connect I get:

  "NET: Invalid Host";

When I test the connection with the Direct:

  "ORA-01017: Invalid username/password; logon denied"

I really don't know what to do more.


texts: Does MySQL or MariaDB support double literals (double precision)?

texts:
MySQL seems to support double precision

  The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

Is there a method to declare that a literal is a double precision and not a float, decimal or anything else? I don't see this mentioned in the docs on Numeric Literals. In PostgreSQL, I could coerce a literal to a double precision with

SELECT 1::double precision,
  double precision '1',
  CAST(1 AS double precision);


texts: Removing postgres connection installed with EnterpriseDB on MacOS

texts:
I had earlier installed Postgres using Enterprise DB which is installed by default at location: /Library/PostgreSQL/10

However, because of some database issues I had to uninstall it. I followed all the steps given here.

Today i tried to reinstall the postgres using homebrew. Installation was successful but when I run the below command to start the service it give the following message.

MacBook-Pro ~ % pg_ctl -D /usr/local/var/postgres start

waiting for server to start....2020-05-21 14:15:45.793 CEST [6798] LOG:  starting PostgreSQL
12.3 on x86_64-apple-darwin19.4.0, compiled by Apple clang version 11.0.3 (clang-
1103.0.32.59), 64-bit
2020-05-21 14:15:45.794 CEST [6798] LOG:  listening on IPv6 address "::1", port 5432
2020-05-21 14:15:45.794 CEST [6798] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-05-21 14:15:45.795 CEST [6798] LOG:  could not bind Unix address
"/tmp/.s.PGSQL.5432":Address already in use
2020-05-21 14:15:45.795 CEST [6798] HINT:  Is another postmaster already running on port
5432? If not, remove socket file "/tmp/.s.PGSQL.5432" and retry.
2020-05-21 14:15:45.795 CEST [6798] WARNING:  could not create Unix-domain socket in
directory "/tmp"
2020-05-21 14:15:45.795 CEST [6798] FATAL:  could not create any Unix-domain sockets
2020-05-21 14:15:45.796 CEST [6798] LOG:  database system is shut down
stopped waiting
pg_ctl: could not start server
Examine the log output.

I guess it is suggesting that the port 5432 is in use by another instance of postgres so i am trying to find and stop the process but it does not list any PID:

MacBook-Pro ~ % lsof -i :5432
MacBook-Pro ~ %

Not sure what I should do next to completely stop the postgres installed via Enterprise DB and start the one installed with homebrew. Kindly guide me with this.


texts:
I have the following table:

value    tag    main_tag
------------------------
    1    tag_A  ABC
   -1    tag_C  ABC
   -1    tag_A  BCD
    1    tag_C  BCD
   -1    tag_A  ABC
    1    tag_C  ABC
   -1    tag_A  BCD
    1    tag_C  BCD
   -1    tag_C  BCD

As demonstrated, there are three columns value, tag, main_tag. Under each main_tag there are several other tag s. I want to get the rows where sum of value s of each tag is equal to zero under each main_tag.

This is the query I tried,

SELECT *
FROM foo
WHERE ( WHERE sum(value) = 0 GROUP BY tag)
GROUP BY main_tag ;

First I am trying to find the sum of values GROUP BY tag and then I am using GROUP BY main_tag. But it is not working.

Output Should be :

value    tag    main_tag
------------------------
    1    tag_A  ABC
   -1    tag_C  ABC
   -1    tag_A  ABC
    1    tag_C  ABC


texts: Oracle: how to set only some roles as not default for a user (no GUI)

texts:
Scenario:

I have instanceA
I have instanceB
I have USER1 in instanceA
I have USER1 in instanceB
USER1 in instancA has four roles grante to it:

ROLE1 default yes admin_option no
ROLE2 default false admin_option yes
ROLE3 default false admin_option no
ROLE3 default yes admin_option yes

USER1 in instanceB has no roles granted
All mentioned roles exist also in instanceB although not granted to USER1
I have already writen a script that generates the DDL in order to duplicate the role grants for USER1 in instanceB.

Problem:

I haven't found a way to programatically do the following because the DEFAULT=NO part doesn't exist in the GRANT ROLE clause.

grant ROLE1 to USER1; -- admin option no
grant ROLE2 to USER1 DEFAULT NO with admin option;
grant ROLE2 to USER1 DEFAULT NO;
grant ROLE3 to USER1 with admin option;

Notice than I want ro replicate in instanceB the same role set USER1 has in instanceA, meaning two of the roles are default and two aren't.
I've studied setting the default role false for ROLE2 and ROLE3 after granting them, using ALTER USER DEFAULT ROLE but that only works for setting ALL roles to default or setting ALL roles to non-default with NONE.
I cannot find a way for setting only ROLE2 and ROLE3 as not-default and ROLE1 and ROLE2 as default.
I know that I can set that using the visual console but I need to automate this so I need syntax way to do it.
I don't want to make the roles ask for password. All roles in the databases are not password roles.


texts: Is 1.6 seconds a typical time to fetch nearly 3.7 million rows from a table?

texts:
I am trying to work out if we need to upgrade our server hardware or need to configure the MySQL settings to make things faster.

A simple SELECT * From table took 1.6 seconds from the command line. However mysql-slow.log shows that it takes 7 seconds when one of our processes from another server executes it. This seems very slow to me. The table contains three INT columns only.

It's an Ubuntu Rackspace cloud server. We have 4GB RAM and only 1.2GB is used up.

So do the numbers look about right or is there something wrong?

Additional information

I have added the EXPLAIN EXTENDED SELECT * From table here:


texts: Cannot find either column "dba" or the user-defined function or aggregate "schema.functionName", or the name is ambiguous

texts:
I created a function and granted select on [dba].[ufn_track_dailies] to public.  The function shows up under table valued functions, but when I try to use it I get an error:

select [dba].[ufn_track_dailies]('Real Name','', '08-01-2015', '08-31-2015')


texts: Creating very quickly an composite partial index on 2 new columns with null values

texts:
I'm adding 2 new columns on big table. Those 2 new columns have no default value and no constraint. I also want to add a composite partial index on those 2 new columns.

CREATE UNIQUE INDEX CONCURRENTLY index_users_on_col1_and_col2 ON public.users USING btree (col1, col2) WHERE ((col1 IS NOT NULL) AND (col2 IS NOT NULL));

I need that to be done concurrently to avoid writes lock. The 2 new columns will start having value only a few days after I add them and I add the index.

So basically the index will be empty when creating.

Is there a way to create the index by "saying" to PostgreSQL it should create the index directly empty and not scan the table?


texts: Accent marks/symbols not displaying correctly in MySQL database

texts:
Anytime there's an accent mark (é or ') or symbol (&) in my database it displays as HTML code. The table CHARACTER SET is UTF-8 Unicode and the COLLATION is utf8mb4_general_ci, which should display those characters correctly. Any ideas why this is happening?

(MySQL version 5.7)

Current data

`company`| `firstname`| `lastname`
----------------------------------
A&amp;T  | Patrick    | O&#039;Brien

Expected

`company`| `firstname`| `lastname`
----------------------------------
A&T      | Patrick    | O'Brien

Thank you in advance!


texts: Creating Sort Index takes 96% of query time even without any ORDER BY

texts:
I have this query:

SELECT itr.recipe_id,
   SUM(itr.weight),
   SUM(aprice_weight),
   SUM(itr.weight+aprice_weight) AS score
FROM
  (SELECT itfin.id AS flyer_item_id,
          itfin.flyer_id,
          MAX(itfin.max_weight) AS aprice_weight,
          itfin.ingredient_id
   FROM
     (SELECT MAX(price_weight) AS max_weight,
             flyer_items.id,
             flyer_items.flyer_id,
             ingredient_to_flyer_item.ingredient_id
      FROM flyer_items
      JOIN ingredient_to_flyer_item ON flyer_items.id = ingredient_to_flyer_item.flyer_item_id
      WHERE flyer_items.flyer_id IN (2)
      GROUP BY ingredient_to_flyer_item.flyer_item_id) AS itfin
   JOIN ingredient_to_flyer_item ON itfin.id = ingredient_to_flyer_item.flyer_item_id
   GROUP BY itfin.ingredient_id) AS itf
INNER JOIN `ingredient_to_recipe` AS `itr` ON `itf`.`ingredient_id` = `itr`.`ingredient_id`
GROUP BY `itr`.`recipe_id`
ORDER BY `score` DESC
LIMIT 12

But even without the ORDER BY clause, execution time is still the same, around 350ms. I tried looking at the indexes to see what I can do, but nothing worked so far. Not even putting a bigger value for SORT_BUFFER_SIZE in MySQL settings.

The profiler tells me that a single Create Sort Index (out of 3) takes 96% of the query time.

Here is the EXPLAIN result:

| id | select_type | table                    | partitions | type   | possible_keys           | key           | key_len | ref                      | rows | filtered | extra                                        |
|----|-------------|--------------------------|------------|--------|-------------------------|---------------|---------|--------------------------|------|----------|----------------------------------------------|
| 1  | PRIMARY     | <derived2>               | NULL       | ALL    | NULL                    | NULL          | NULL    | NULL                     | 583  | 100.00   | Using temporary; Using filesort              |
| 1  | PRIMARY     | itr                      | NULL       | ref    | recipe_id,ingredient_id | ingredient_id | 4       | itf.ingredient_id        | 35   | 100.00   | NULL                                         |
| 1  | PRIMARY     | r                        | NULL       | eq_ref | id                      | id            | 4       | metadata2.itr.recipe_id  | 1    | 10.00    | Using where                                  |
| 2  | DERIVED     | <derived3>               | NULL       | ALL    | NULL                    | NULL          | NULL    | NULL                     | 246  | 100.00   | Using temporary; Using filesort              |
| 2  | DERIVED     | ingredient_to_flyer_item | NULL       | ref    | flyer_item_id           | flyer_item_id | 4       | itfin.id                 | 2    | 100.00   | Using index                                  |
| 3  | DERIVED     | flyer_items              | NULL       | ALL    | id_2,id,flyer_id        | NULL          | NULL    | NULL                     | 104  | 100.00   | Using where; Using temporary; Using filesort |
| 3  | DERIVED     | ingredient_to_flyer_item | NULL       | ref    | flyer_item_id           | flyer_item_id | 4       | metadata2.flyer_items.id | 2    | 100.00   | NULL                                         |

Any help to speed this query up would be so appreciated. Thank you!

UPDATE

Link to .sql demo tables: https://nofile.io/f/F4YSEu8DWmT/meta.zip


texts: How can I get the percentage of the table the groups represent?

texts:
Given a query like this,

CREATE TABLE foo(grp)
AS VALUES ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('c'),('c'),('e');

I can use GROUP BY to aggregate into a,b,c,e, but how can I return the different groups, and the percentage of the total table they represent?

a | 20%
b | 30%
c | 40%
e | 10%


texts: Constrain referenced rows to two different tables to belong to the same row in another table

texts:
Apologies for the potentially confusing question title. I struggled to make it generic!

I am designing a wildlife recording database. An observer goes to a site and records the occurences of species on a date as part of a sample. You can have lots of samples over time at the same site. So a sample has an id, a siteid and a date etc. Each sample has a number of occurrences, which are the individual wildlife records - the species and (say) the number counted. A site is divided up in to several locations within the site (locations have a siteid) and the occurrences reference these locations - not the site directly. So occurrences have a sampleid, speciesid and locationid.

So occurrences reference both a sample and a location that need to belong to the same site for the data to be consistent. The only way I could ensure that all occurrences reference locations from the same site as the sample was to have both sample and location primary keys include the siteid and then occurrences to include siteid in its foreign key to them. Here's a screenshot below.

I have never needed to use compound primary keys in tables before and it jars a bit that occurrence has a siteID in it as part of the foreign key to both location and sample. Is this the correct way to constrain the locations and samples that occurrence can reference? Or is there an alternative table design that doesn't need compound keys? I don't have anything against compound keys if they're necessary. Are these ones necessary?


texts: How can I limit the number of emails sent by SQL Server agent?

texts:
I use SQL Server agent notifications a lot for notifying of failed jobs. Usually, jobs run every hour so I don't mind getting a few emails if the job fails.

However, I want to run a job every minute now and leave it running. I really don't want to get 100s of emails if it fails for a few hours.

Can I set a limit on the number of emails SQL Server agent will send for a job?


texts: SSMS closes after installing visual studio

texts:
Good Morning.
I have a problem, first I made the installation of SQL Server Express, followed by SSMS and finally Visual Studio Community.

Before installing visual studio, SMMS worked perfectly, when you install visual studio, SSMS is sorry, the logo appears and opens the window to connect to the server but it immediately closes. Also if I try to uninstall SSMS the same thing happens with the installer, it closes when it appears.

SSMS V. 17.9.1

Thank you


texts: Job to remove logins on secondary replica that don't exist in primary replica

texts:
How to remove logins on a secondary replica that don't exist in primary replica?
I want to create automated jobs that should check if the login still exists on a secondary after the login on the primary dropped. I want to drop this login on the secondary replica.


texts: Should my application utilize database credentials for user login?

texts:
I'm working on an application that has an ASP front end and a SQL Server database.  The way credential management is currently performed is there is a custom built User Table in the database that stores their email and encrypted password.  When the user logs-in, the password is retrieved from the database, decrypted by ASP engine, and is compared to the password they submitted.  Then, their userID is stored in the ASP session.

I think the user should log into the database instead of the application, since this is a database-centric app.  This way, history can be maintained by triggers on update and can capture the user's database credentials, instead of relying on ASP to pass the user name in.

What are best practices for where user management should be performed?  Is this idea even possible?  I've tried to do research on this topic, but can't find any good information.

I apologize if this is the wrong venue to ask this question.


texts: Merge-Replication: New column doesn't get replicated down by update command

texts:
I do this frequently but all clients have this fault:

I added a new column to a table using a SQL statement, which causes the column to be created and added to every publication that uses this table.

Now I create values by using an update statement.

The column and the values are properly replicated from server to server, but all clients based on Express only get the field but no value.

The log shows successfully updates of the right number of table rows, but it needs little time as if nothing is transferred.

Changes that were made on the client are successfully replicated up to the server.

Also I deleted some unnecessary columns and then recreated the new column, same here.

I use SQL Server 2008 R2 with merge replication, no filters. The column limit is 246 columns, but I use 112 at the moment. Reproducing the error fails on a copy of the database, recreating the abonnements (subscriptions) is not an option for me.

Edit: Checked the last created columns and found this additional info. I added 4 columns earlier that are not in use yet. 2 of them have the same issue.


texts: Data too long for column for longtext datatype - MySqlException

texts:
I am trying to insert string in LONGTEXT column. Length of string is 75402 and byte count is 150804 (about 147 KB)

Throws an exception Data too long for column On inserting record.

MySQL document state that LONGTEXT can contains maximum 4GB of data

Any suggestion will be welcome

UPDATE:

Exception occurs when characters length is greater than 65535

But 65536 is a max length of TEXT datatype and I am using LONGTEXT here.


texts:
I have a MySQL server that serves a couple of databases including a Magento e-commerce store.
We have been suffering with corruption in the ibdata1 file for a while, but it has now reached a critical point.
The only database that uses innodb tables is the Magento webstore back-end.
I have an up to date SQL extract of the entire magento database. I thought that I could shut down MySQL, rename the ib* files, restart MySQL and re-create the database from the SQL file - however I am having a major problem restarting MySQL after renaming the ib* files.
I keep getting 'cannot connect to mysql ..... mysql.sock'.
It appears as though the ib* files are all re-created as I would expect, but I can't restart and then connect to the sever...
Any ideas??
EDIT
Have already tried all values of innodb_force_recovery. Error log shows various issues including
difficult to find free blocks from the buffer pool

and now also
InnoDB: Error: page 131073 log sequence number 17 3671003941
InnoDB: is in the future! Current system log sequence number 17 3670939210.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files


texts:
I am trying to optimize an sql query by using in memory tables. After I had ran the query some times I ran the Transaction Performance Analysis Overview report of SQL Server 2016 which suggested that I should optimize some tables. I chose to migrate to in memory the table with the biggest performance gain combined with the minimal migration work. However when I run the query using the in memory table it takes much more time than when using the disk based table.

Trying to troubleshoot it I ran the Top Resource Consuming Queries in query store where all the metrics except for duration are better for the query using the in memory table.

I would appreciate if anyone could advise me about this case and where should I search for the reason of this performance issue.

Edit: I added the query plans of a part of the complete query.


texts: Prevent mysqldump from writing column-level collate and charset?

texts:
Using mariadb 10.x, it looks like the mysqldump utility adds column-level collate even if the table was created without them. Is there a way to suppress this behavior?

Table created:

CREATE TABLE `ACL` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(512) DEFAULT NULL,
  `property` varchar(512) DEFAULT NULL,
  `accessType` varchar(512) DEFAULT NULL,
  `permission` varchar(512) DEFAULT NULL,
  `principalType` varchar(512) DEFAULT NULL,
  `principalId` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC

SHOW CREATE TABLE ACL:

CREATE TABLE `ACL` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `property` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `accessType` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permission` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `principalType` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `principalId` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC

Is there any way to get exactly what was used to create the table in the first place?


texts: Using Oracle hierarchical queries for transposing field value into rows

texts:
One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm afraid it's not very efficient :

select distinct test_id , regexp_substr(str_data,'[^,]+', 1, level)
from
(
select 1 as test_id, '1,2,3' as str_data from dual
union all
select 2 as test_id, '4,5,6' as str_data from dual
)test_data
connect
by
regexp_substr(str_data, '[^,]+', 1, level) is not null

I don't like that I have to use distinct . Is there a better approach ?

Thank you.


texts: How to calculate required ressources from a baseline and what should be measured at all?

texts:
I have to deal with the following situatiuon:

Currently we have

1 cluster with 5 nodes running 10 instances (SQL Server 2008 Enterprise edition), and
1 cluster with 2 nodes running 5 instances (SQL Server 2014 Standard edition)

in use. All servers are virtual servers running in VMWare.

We want to setup a new cluster (SQL Server 2014 Enterprise). In a first step, 1 instance from the 2008 Enterprise cluster and 1 instance from the 2104 Standard cluster are to be migrated.

Therefore, my boss asked 2 questions:

How many cores do we need (aim: minimize license costs)?
How much RAM do we need?

My answer was: "It depends ..." Now I have to deliver hard facts by monitoring the next few weeks. Great! (beware of irony)

My approach for question number 1:

Using perfmon.exe I plan to monitor

Processor\% Processor Time (_total and single cores),
Processor\% User Time (_total and single cores),
Processor\% Interrupt Time (_total and single cores) - is this really necessary? -, and
System\Processor Queue Length.

The question is, where to get these data from? From the node? From the SQL Server?

In the first case it should be easy: the first instance in question - vsql2008ent-1\instanceX for the sake of simplicity - is currently running on a node, let's name it node sql2008NodeA. No other instances, nor server should run under normal conditions on this node. So it should not matter where I get the data from, should it? In case of a disaster other instances will be running on this node, too. But we want to have a baseline for normal operation.

The second instance - vsql2014stan-1\instanceY - shares it's node - sql2014NodeA - with 2 other instances. In this case I can never be sure, how much cores the instance will truely need for smooth operation, right? So I can monitor the instance. But what does the result mean? It shows the CPU ressources actually used by this instance, only. But would more cores have been used if they were available? So what would be the answer to the question mentioned above?

RAM is the other question. Due to several disasters in the past when all instances landed on the same node I have set an upper limit for the maximum server memory for each instance. This limit depends on the available memory of the node (currently 100GB or 120GB respectively). So how to monitor this? If all memory is used up, everything seems clear: insufficient memory. If all goes slow: insufficient memory. But how much memory do I really need?

I try to summarize my questions:

Where should I get the measures from (node vs. server)?
Do I need to monitor the interrupt time, if I want to know the number of cores required?
What should I monitor under the given circumstances to answer the question, how much RAM I need (I know: "The more the better.")?

Thank you very much for your help!

Best regards!


texts: Removing unwanted NULL results when using Right join

texts:
Currently I've got two different tables, one named "Student" and another named "Class". I'd like to pick out one SPECIFIC Classcode from the Student table, while also picking out all the available Classcodes in the Class table. So far the best result I got was by using RIGHT JOIN which kinda gave me what I wanted. However instead of just displaying the specific Classcode from Student which I asked for, it displays the rest of them as "NULL". Code I used is posted below:

SELECT Student.Classcode, Class.Classcode
FROM Student
RIGHT JOIN Class
        ON Student.Classcode=Class.Classcode
       AND Student.Username='test'
ORDER BY Student.Classcode;

This provides me with the following result:

As you can see, it gives me the Classcode linked to the username on the left side (INF2), while showing all available classcodes on the right side. However for those that are NOT linked with the specified username, it shows up as NULL. Is there any way for it to show my desired Classcode without including the results that DONT match with the set username?


texts: Postgresql assign mutliple row query result to variable and continue execution

texts:
I'm trying to create a script that calls several stored procedures in PostgreSQL and print the result of some of them:

CREATE OR REPLACE FUNCTION script(
vtoken character varying)
  RETURNS void AS
$BODY$
DECLARE
  aux character varying;
BEGIN

PERFORM (SELECT acquire_table(vtoken));

-- This query gets the dynamically generated token for the client in that codebar
aux := (SELECT token
                FROM clients c JOIN tables t ON c.tablekey = t.tablekey
                WHERE codebar = vtoken);

PERFORM (SELECT order_drinks(aux, '{1}', '{1}'));

--EXECUTE (SELECT issue_ticket(aux));

PERFORM (SELECT order_drinks(aux, '{1}', '{1}'));

END;$BODY$
  LANGUAGE plpgsql VOLATILE

The issue_ticket() function returns a multiple row result, I'd like to print it directly and continue execution of next function (order_drinks()). If I do PERFORM it does not show the results, and if I just try to execute the SELECT query as it is, it asks to assign return value to variable.


texts:
According to Microsoft computation here:

  The maximum compute capacity usage by one instance is limited to the
  lesser of 4 sockets or 24 cores.

To me a socket is the recepticle where a CPU is seated. In which case a socket and a core count aren't always equivalent. I know some motherboards can have multiple sockets but nowadays with the high core count on server CPUs this isn't the case normally.

Can anyone explain what this means?

(doing my Microsoft 70-762 developing SQL databases exam)


texts:
I have been all over looking for the solution to logging sp_blitzfirst results to table on a linked server. I see github posts where a contributor was adding this to sp_blitz, but that's been closed for a while. I see vague rumblings that this might already be in sp_blitzfirst, but am ready to be punished for my inability to find the solution on my own.

Can someone point me to a definitive answer?


texts: PostgreSQL UPSERT issue with NOT NULL columns

texts:
I want o know if it's possible to do an upsert when one of the columns (column1 or column2) is null, I just need a partial update, and of course, I'm trying to avoid a SELECT to verify if the record already exists.
This is an example of what I'm trying to achieve.
CREATE TABLE test(
    column1 timestamp NOT NULL,
    column2 uuid,
    status integer NOT NULL,
    id uuid NOT NULL,
    CONSTRAINT upsert_conflict UNIQUE (id, status)
);

And I have something like this:
INSERT INTO test(column1, status, id)
VALUES(now(), 0, <any uuid>)
ON CONFLICT ON CONSTRAINT upsert_conflict
DO UPDATE SET column1= now()

This one works as expected, but I also have something like this:
INSERT INTO test(column2, status, id)
VALUES(gen_random_uuid (), 0, <existing uuid>)
ON CONFLICT ON CONSTRAINT upsert_conflict
DO UPDATE SET column2= gen_random_uuid ()

I know that the INSERT is wrong because we cannot set a NULL value to column1, but assuming that I already have a record with that id and status, I would like to do the UPDATE, instead of that, I'm getting an error: ...violates not-null constraint. because of the missing column.
I'm creating these queries dynamically according to what users pass me. Sometimes I will have something like the first example, and sometimes, something like the second example, and also I don't know which column must not be null.
I hope you can help me.


texts:
We've inherited some SQL server 2014 databases, they are running on Azure IaaS.  We're getting complaints about performance and trying to get a grip on it.  We're starting to look at CPU performance, and run the following test (all tests are running on database machine itself, ie not via network):

SET NOCOUNT ON
DECLARE @Loops INT SET @Loops = 1
DECLARE @RESULT INT

WHILE @Loops <= 2000000
BEGIN
    IF COALESCE('123', '456') = '456'
            SET @RESULT = 1
    SET @Loops = @Loops + 1
END

GO

On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:

Completes in 3 minutes

On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:

Completes in 1 second

I've tried running a few benchmarks on the production machine, but they don't show any problems, and always show the production machine CPU at least twice as powerful as test machine.

Is there some SQL Server setting that might be configured wrongly?  Any idea how to troubleshoot this issue?

Other Infrastructure Information:

Both databases have

"max degree of parallelism"set to 1
"cost threshold for parallelism" set to 5
Azure VM storage is Premium SSD (P30)

Production (slow) is Standard_D14_v2
Test (fast) is Standard_D12_v2
Data, temp and logs are separated
Data is striped across 3 striped P20 or P30
temp on 4 striped P20 or P30
log on single P20 or P30

Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.


texts: Postgresql v10.1 - How to fix ERROR: duplicate key value violates unique constraint

texts:
I'm not sure how this happened to this table but  have an issue with ERROR: duplicate key value violates unique constraint trying to use AWS's DMS service to move a postgres v10.1 DB to RDS v10
The table looks like
# \d+ public.brokered_carriers
                                                                  Table "public.brokered_carriers"
         Column         |            Type             | Collation | Nullable |                    Default                    | Storage  | Stats target | Description
------------------------+-----------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
 id                     | integer                     |           | not null | nextval('brokered_carriers_id_seq'::regclass) | plain    |              |
 name                   | character varying           |           |          |                                               | extended |              |
 code                   | character varying           |           |          |                                               | extended |              |
 external_id            | character varying           |           |          |                                               | extended |              |
 mc_number              | character varying           |           |          |                                               | extended |              |
 dot_number             | character varying           |           |          |                                               | extended |              |
 phone_1                | character varying           |           |          |                                               | extended |              |
 phone_2                | character varying           |           |          |                                               | extended |              |
 tms_payment_address_id | integer                     |           |          |                                               | plain    |              |
 account_id             | integer                     |           | not null |                                               | plain    |              |
 data                   | jsonb                       |           |          | '{}'::jsonb                                   | extended |              |
 created_at             | timestamp without time zone |           |          |                                               | plain    |              |
 updated_at             | timestamp without time zone |           |          |                                               | plain    |              |
Indexes:
    "brokered_carriers_pkey" PRIMARY KEY, btree (id)
    "index_brokered_carriers_on_account_id_and_external_id" UNIQUE, btree (account_id, external_id)
Foreign-key constraints:
    "fk_rails_39675fc9f0" FOREIGN KEY (account_id) REFERENCES accounts(id)

The DMS error is

dms-tasks-company dms-task-2JVAOJI7TQAZSPCVDXDTYBA5YE 2019-10-18T17:59:37 [TARGET_LOAD     ]I:  Table 'public'.'brokered_carriers' has Non-Optimized Full LOB Support  (odbc_endpoint_imp.c:4708)
dms-tasks-company dms-task-2JVAOJI7TQAZSPCVDXDTYBA5YE 2019-10-18T17:59:37 [TARGET_LOAD     ]E:  RetCode: SQL_ERROR  SqlState: 23505 NativeError: 1 Message: ERROR: duplicate key value violates unique constraint "index_brokered_carriers_on_account_id_and_external_id"; Error while executing the query [1022510]  (ar_odbc_stmt.c:2639)
dms-tasks-company dms-task-2JVAOJI7TQAZSPCVDXDTYBA5YE 2019-10-18T17:59:37 [SOURCE_UNLOAD   ]I:  Unload finished for table 'public'.'brokered_carriers' (Id = 18). 19163 rows sent.  (streamcomponent.c:3473)

Any idea how to fix this and fix the constraint?


texts: Table join using outer and inner in 1 statement

texts:
I want to get all years for product and country pairs, which has at least 1 overlapping year. Is there any way to do it more efficiently than the query below?

CREATE TABLE#t1(product varchar(5), country varchar(5),time int)
CREATE TABLE#t2(product varchar(5), country varchar(5),time int)

INSERT INTO#t1 values ('water','uk',1), ('water','uk',2),       ('ice','uk',1)
INSERT INTO#t2 values ('water','uk',2),                         ('ice','uk',2)

SELECT *
FROM #t1 t1
         FULL OUTER JOIN #t2 t2 ON t2.product = t1.product
                                   AND t2.time = t1.time
                                   AND t2.country = t1.country

SELECT *
FROM
(
    SELECT ISNULL(t1.product, t2.product) AS product,
           ISNULL(t1.country, t2.country) AS country,
           ISNULL(t1.time, t2.time) AS time
    FROM #t1 t1
         FULL OUTER JOIN #t2 t2 ON t2.product = t1.product
                                   AND t2.time = t1.time
                                   AND t2.country = t1.country
) allMarkets
INNER JOIN
(
    SELECT DISTINCT
           t1.product,
           t2.country
    FROM #t1 t1
         INNER JOIN #t2 t2 ON t2.product = t1.product
                              AND t2.time = t1.time
                              AND t2.country = t1.country
) overlaps ON overlaps.product = allMarkets.product
              AND overlaps.country = allMarkets.country;

DROP TABLE #t1
DROP TABLE #t2


texts: How to create the multiple sum query with joins

texts:
Please consider following database:

Table `invoices`:
-- invoice_id

Table `amounts`:
-- amount_id invoice_id type_id amount

I am trying to get the sum for each type from table like this:

SELECT
    invoice_id,
    SUM(amounts1.amount),
    SUM(amounts2.amount),
    SUM(amounts3.amount)
FROM
    invoices
LEFT JOIN
    amounts amounts1 ON (amounts1.invoice_id = invoices.invoice_id AND amounts1.type_id IN (1,2,3))
LEFT JOIN
    amounts amounts2 ON (amounts2.invoice_id = invoices.invoice_id AND amounts2.type_id IN (4,5,6))
LEFT JOIN
    amounts amounts3 ON (amounts3.invoice_id = invoices.invoice_id AND amounts3.type_id IN (7,8,9))
GROUP BY
    invoices.invoice_id

This query returns unexpected results. Result is different when I remove one of the joins:

SELECT
    invoice_id,
    SUM(amounts1.amount) // returns different amount
FROM
    invoices
LEFT JOIN
    amounts amounts1 ON (amounts1.invoice_id = invoices.invoice_id AND amounts1.type_id IN (1,2,3))
GROUP BY
    invoices.invoice_id

Any ideas what I am doing wrong?

Same thing with COUNT(amounts1.amount_id)

Edit: I realized problem occurs when there is one type_id=1 amount and more than one type_id=4 amount - amount type_id=1 is added twice. Like this:

amount_id amount type_id invoice_id
    51     2.99     1        2          // SUM(amounts.amount1) returns 8.97
    52     2.99     4        2
    53     2.99     4        2
    54     2.99     4        2

How to avoid it?


texts: Entries in suspect_pages but checkdb shows no error

texts:
I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)   Jun 15 2019 23:15:58   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )

Yesterday, I got two entries in "suspect_pages" for the same database. One of event type 1 and one of type 2

  1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID).

  2 = Bad checksum.

database_id file_id page_id eventtype   error_count last_update_date
8           1       1482057 1           1           2019-11-14 14:40
8           1       1482057 2           1           2019-11-14 14:40

I found the object related and they both point to the same table on the database.

DBCC TRACEON (3604);
DBCC PAGE (8, 1, 14823057, 0);
DBCC TRACEOFF (3604);

I had a valid backup of before the corruption and couldn't afford a down time so I took a backup of the corrupted database, restored my backup on a new name. I dropped the corrupted table and then recreated it from the valid backup.

Today, I restored the corrupted database backup that I took yesterday on a test server and when I run a full checkdb, it detects no corruption.

  DBCC CheckDB() WITH No_INFOMSGS, ALL_ERRORMSGS

How is it possible that the backup I took from a corrupted database (according to suspect_pages) doesn't have any problems? Can those entries in suspect_pages be a false positive?

Database Compatibility Level is 130 (SQL 2016) Our SQL Server is running on Windows Server 2012.


texts: Create a job that sets Auto Update Statistics on

texts:
I am using SQL Server 2012. The vendor wants Auto Update Statistics set to false, but my boss wants it set to true.

If I set it to true, after a while it goes back to false by itself. I think the vendor has something to switch it to false in their application.

I want to create a job which checks the whether Auto Update is set false or true. If it is to false, it will turn it to true. If it is true it should leave it true.

This is the script I am going to use in the job:

USE master
GO
ALTER DATABASE AdventureWorks2008
SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;
GO

But I don’t want the job to do anything when it is already true.

Do I need to change the script or run the way it is?


texts:
I am syncing from PostgreSql to MSSQL using SymmetricDS. Some tables of the source database have timestamp field set too '0001-01-01 00:00:00.0' and it could not be able to insert to destination(SQL Server) database.

Is there anyway to work around this ?

AcknowledgeService - The outgoing batch 001-987 failed: Parameter arg '0001-01-01 00:00:00.0' type: TIMESTAMP caused exception: Only dates between January 1, 1753 and December 31, 9999 are accepted.


texts: Delete cascade produce scan intead of index seek

texts:
I have two tables in my database.

TableA : 1427877 rows
TableB : 339939000 rows

TableB has a foreignkey column in TableB (TableB.foreignId). It has Delete On Cascade ON.

The problem is when I delete a row in TableA.

DELETE TableA WHERE id = @id

The cascade reference, produce an index scan on TableB (300Million rows). While exits a non-clustered index on foreignId column.

I test with a procedure

CREATE OR ALTER PROCEDURE #tempDelete(@id INT) AS DELETE TableA WHERE id = @id;

After cleaning cache plan. The plan is the same, index scan.

If I did

DELETE TableB WHERE ForeignId = @id

It use an index seek on 'indexB'

Plan for both query

I checked this post about missing index. I think the index are rigth.

Also this post about correct data type. foreignId is Int, the same that use on my .Net client and also in the store procedure.

How I could fix the Delete operation? The plan is same for production and development.

Thanks,

Every index on TableB have ForeignId as first column.
Indexc is the only that has ForeignId as second column.


texts: What is the correct term to describe a "persistent transaction"?

texts:
I'm kind of a the administrator of a zero-administration OODBMS, but mostly I do programming.

I remember reading years ago about some SQL database that had a concept of something like a permanent transaction. It might have been Sybase. Anyway, what that "thing" was, was like a transaction that you start, and that persist between connections, and withing which you can run "normal" transactions. It basically gave you a "view" of that database that could diverge from the main database, but running on the same server, and therefore sharing most of the resource instead of having to have it's "own" server. I hope you understand what I mean now.

Anyway, what is that kind of thing called, and is it still available in modern databases. In particular, is there any of the fancy distributed NoSQL databases that can do that?

The reason I am asking, is because I could use something like that to run the staging of the next version of my software on the main database, saving me the need to have a staging system that is as powerful as the main one, and the trouble/bandwidth of synchronizing the changes from the main to the test. I would just start the "permanent transaction", test the new software for a few days, possibly letting some users get a preview, and throw away the changes when I'm done. Then I make the real deployment, and start a new "permanent transaction" for the next version...

NB: I couldn't put meaningful tags because it's my first post and I haven't got the right to invent tags, and there was nothing like "persistent", or "permanent" or even "long running" ...


texts: Cross-cutting I18N native support in databases

texts:
Does a database engine with native cross-cutting internationalization support exist, able to store data like this?

SET LOCALE='en'
SELECT * FROM BOOKS

+------+-----------------------+----------------+
|  ID  | TITLE                 | AUTHOR         |
+------+-----------------------+----------------+
|   1  | The Lord of The Rings | J.R.R. Tolkien |
+------+-----------------------+----------------+

SET LOCALE='it'
SELECT * FROM BOOKS

+------+-------------------------+----------------+
|  ID  | TITLE                   | AUTHOR         |
+------+-------------------------+----------------+
|   1  | Il Signore degli Anelli | J.R.R. Tolkien |
+------+-------------------------+----------------+

i.e. able to store values in a third dimension, such as a locale in this case?

Clarification

In the example above, just TITLE should be affected by SET LOCALE by a special definition in this kind of database (I imagine something like LVARCHAR); so, adding a discriminator column would not be feasable since all the non-localized columns will be duplicated.


texts:
I have a very large database (millions of rows) which has been indexed.
I receive a diff of rows from another source that I need to update in my database.

The problem I am facing is that is it necessary to re-index the entire database/table again since it is a very expensive process and I don't want our site to be down for the period it is being indexed.
Is there a better way or a way to add the partial index to the original one so that I do not need to reindex everything ?

I am using postgresql 9.1 as a database.


texts: With SQL transactional replication, does changing user permissions to a table trigger a new snapshot?

texts:
I was hoping to get some help with a SQL Server 2012 transactional replication scenario - or maybe just my understanding of where permissions are stored in SQL server. If I change a users permissions to a particular table (like granting SELECT or INSERT) on the publisher in a transactional replication configuration, would that change the schema such that subscribers would need to pull from a new snapshot?

My concern is potential down-time with such a change where the subscriber would effectively be offline while the new snapshot is created. Ideally I could just grant new permissions to a particular user to a replicated article either on the publisher or subscriber without making things reset. I'm not concerned about the updated permissions being replicated from the publisher to the subscriber (in fact, ideally they wouldn't). Thanks in advance!


texts: Matching Custom Exported CSV File with Main MySQL Database

texts:
I recently selected certain columns from a table from a huge MySQL db >10GB into a csv file and then filtered through the rows.

I would now like to retrieve the rest of the data matching the filtered rows. How could I go about doing that.

E.g.

MyTable (All columns are example names only)

+----+---------------+---------+-----------+
| ID | User          | Country | Etc..     |
+----+---------------+---------+-----------+
| 1  | JohnAppleSeed | Canada  | More Info |
+----+---------------+---------+-----------+
| 2  | ExampleUser   | Britain | More Info |
+----+---------------+---------+-----------+
| 3  | Admin         | Canada  | More Info |
+----+---------------+---------+-----------+

Exported.csv (unfiltered)

"JohnAppleSeed","Canada","More info"
"ExampleUser","Britain","More info"
"Admin","Canada","More info"

Exported.csv (filtered)

"JohnAppleSeed","Canada","More info"
"Admin","Canada","More info"

How do I get a file with the following information:

"1","JohnAppleSeed","Canada","More info"
"3","Admin","Canada","More info"

I know I could've selected columns where the value matched the country, but during the filtering process there were changes made to the 'more information' columns.


texts: Joining two ltree based tree tables recursively

texts:
I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join them to each other – and bring in all the relations – in a reasonably efficient way?

In this system, the ancestry determines the capabilities of a role or permission. A role below building_access will inherit the rights and permissions of its parent. I can quite easily query roles or permissions and determine the entire line of ancestry for a given record.

What I am trying to do is query a role and determine the complete tree of permissions for that role. I am also trying to query all roles and determine the same list of permissions, for each and every role, aggregating it into a string.

SELECT r.name AS "role", CONCAT('["', string_agg(p.name, '", "' ORDER BY p.id), '"]') AS "permissions"
FROM roles r
JOIN roles_permissions rp ON rp.role = r.id
JOIN permissions p ON p.id = rp.permission
WHERE r.path @> 'company_employee.warehouse_employee'
GROUP BY r.id;

Actual Results:
---------------------+-------------------------------------------------
        role         |                   permissions
---------------------+-------------------------------------------------
 company employee    | ["building access", "break room access"]
 warehouse employee  | ["warehouse access", "warehouse stock access"]

Intended Results:
---------------------+-------------------------------------------------
        role         |                   permissions
---------------------+-------------------------------------------------
 company employee    | ["building access", "break room access"]
 warehouse employee  | ["building access", "break room access", "warehouse access", "warehouse stock access"]

This query functions to give me the direct permissions for every role, but it doesn't get the ancestors of those permissions to establish the actual total permissions for the role.

My sample database is as displayed below:

CREATE EXTENSION ltree;
CREATE EXTENSION tablefunc;

CREATE TABLE roles (
  id   int PRIMARY KEY,
  name text NOT NULL,
  path ltree
);
CREATE INDEX roles_path_idx ON roles USING gist (path);

INSERT INTO roles (id, name, path) VALUES
  (1, 'company employee', 'company_employee'),
  (2, 'warehouse employee', 'company_employee.warehouse_employee'),
  (3, 'warehouse manager', 'company_employee.warehouse_employee.warehouse_manager');

CREATE TABLE permissions (
  id int PRIMARY KEY,
  name text NOT NULL,
  path ltree
);
CREATE INDEX permissions_path_idx ON permissions USING gist (path);

INSERT INTO permissions (id, name, path) VALUES
  (1, 'building access', 'building_access'),
  (2, 'break room access', 'building_access.break_room_access'),
  (3, 'warehouse access', 'building_access.warehouse_access'),
  (4, 'warehouse stock access', 'building_access.warehouse_access.warehouse_stock_access'),
  (5, 'warehouse security access', 'building_access.warehouse_access.warehouse_security_access');

CREATE TABLE roles_permissions (
  role       int REFERENCES roles,
  permission int REFERENCES permissions,
  PRIMARY KEY (role, permission)
);

INSERT INTO roles_permissions (role, permission)
VALUES (1, 1), (1, 2), (2, 3), (2, 4), (3, 5);


texts: Slow query performance on large table with no unique keys

texts:
I have a table that has approximately 20 million records and I'm having a very hard time improving the performance of the following query. It's taking well over a minute to run.

SELECT  a.batch_date, a.batch_number, sum(a.gross_amt) AS gross_amount,
        (sum(a.trans_fee)*-1) as trans_fee, sum(a.net_amt) AS net_amount
    FROM  cc_detail a
    WHERE  a.company_id='1'
      AND  a.account_number='999999'
      AND  (a.batch_date BETWEEN '2015-10-01' AND '2016-01-31')
    GROUP BY  a.batch_date, a.batch_number
    ORDER BY  a.batch_date DESC, a.batch_number
    LIMIT  0, 10

Here is the table structure:

CREATE TABLE `cc_detail` (
    `company_id` int(11) NOT NULL,
    `account_number` int(12) NOT NULL,
    `location_number` int(12) NOT NULL,
    `batch_date` date NOT NULL,
    `batch_number` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
    `gross_amt` decimal(15,6) NOT NULL,
    `trans_fee` decimal(15,6) NOT NULL,
    `net_amt` decimal(15,6) NOT NULL,
    KEY `company_id` (`company_id`),
    KEY `account_number` (`account_number`),
    KEY `location_number` (`location_number`),
    KEY `batch_date` (`batch_date`),
    KEY `batch_number` (`batch_number`),
    KEY `ca` (`company_id`,`account_number`),
    KEY `cab` (`company_id`,`account_number`,`batch_date`),
    KEY `cb` (`company_id`,`batch_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The server has 3.5 GB RAM and is running MariaDB 10.1. One issue with this table is that it does not have a primary or unique key. Due to the source of this data not having a unique key, I can not create one (unless someone has suggestions on ho to do this safely). This table is updated multiple times a day by deleting records within a date range and inserting data within that same date range.

Here is the result of the Explain statement using the above query:

id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: company_id,account_number,batch_date,ca,cab,cb
key: ca
key_len: 8
ref: const, const
rows: 1
Extra: Using where; Using temporary; Using filesort


texts: Poor performance on inner join using dates and between

texts:
I am having trouble optimizing a query that does an inner join using a date range. The purposes of the query is take daily data and summarize by week.

Select  pcw.EndDate WeekEndDate, h.Store, SUM(h.DeliveryChargesTotal) DeliveryChargesTotal
from Daily_GC_Headers h
inner join PeriodCalendar_Weeks pcw
on h.SalesDate between pcw.StartDate and pcw.EndDate
where SalesDate between @StartDate and @EndDate and isCanceled = 0
group by pcw.EndDate, h.Store

Simplified schema of Daily_GC_Headers table (13.8 million rows; about 5.4 million match criteria in WHERE clause):

Store - Varchar(10) (PK)
SalesDate - Date (PK)
TicketNumber - SmallInt (PK; starts over 1 each day at each store.)
IsCanceled - Bit
DeliveryChargesTotal - Decimal(9,2)

Simplified schema of PeriodCalendar_Weeks Table (570 rows; 53 match the criteria):

Year - smallint (PK)
Period - tinyint (PK)
Week - tinyint (PK)
StartDate - Date
EndDate - Date

This query takes about 15 seconds in SSMS. Querying Daily_GC_Headers by itself (and just grouping by Store) takes 2 seconds. A query against PeriodCalendar_Weeks is "instant".

DBCC SHOW_STATISTICS indicates that the stats are both tables are current (we run a weekly job to update them). I've tried clearing the plan caches.

The execution plan is strange. For example, it is doing an Eager Spool on PeriodCalendar_Weeks. The estimated rows is 156.6 but the actual rows is 153,971. It then filters the results of that first spool and does a Lazy Spool. The estimated/actual rows of that 2nd spool is 5.4 million, even though the underlying table has less than 600 rows in it.

What should I be looking for or doing to optimize this?

Additional Information

For sake of clarity, I initially described an oversimplified PK on the Weeks table. I have update the schema above to show the full key. The PK described for the Headers is (and was) the full key.

Screen shot of some rows from the Weeks table:

Stats from the Weeks table:

Some stats from Headers table. There seems to be an histogram record for about every 5-10 days for the entire history in the table (3 years).


texts: Is there any best number for rows count in a table to start using indexing?

texts: I'm a newbie in DBA. I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index. I know there should be some dependency between table's rows count and using index, but I don't know if there is an standard table's rows count or not.


texts: MariaDB Replication - Replicate only specific tables and views

texts:
Note: A backend developer here with little to no experience in setting up database servers and replication.

What needs to be done

Setup DB replication of an existing database with the following constraints:

Replicate only a specific list of tables/views with different names in the replicated database.
Change the name of the tables/views in the replicated database (during the replication process)
Setup a user on the replicated DB with further restrictions with which only a set of table/view can be viewed/updated/deleted

Progress so far

I have already read the document here, however, I did not find anything concrete to help me move forward with all the use-cases I wish to support!

Use Case

Show only essential data to the external vendor.

PS: If there are any other approaches other than replication, would be happy to consider and implement that as well.


texts: SQL Server 2008R2 Linked Server to SQL Server 2000 showing incorrect server

texts:
I have 2 servers and 4 instances

Server 1 (SQL Server 2008R2) has instance a,b,c
Server 2 (SQL Server 2000) has instance d

I created a linked server from server 1 instance c to server 2 instance d. When I expand the catalog for linked server I get server 1 instance b. Tried IP and DNS name no reason I can find anywhere for this to happen.

Any thoughts or ideas would be helpful.


texts: Oracle db_name, db_unique_name, and ORACLE_HOME

texts:
I'm studying Oracle and I would like to understand the uniqueness or constrains when assigning these parameters.

Suppose a machine with a single OS (host) and 2 different databases (physical). Are they forced to use different db_name in following cases?

They share ORACLE_HOME
They don't share ORACLE_HOME

How is this related when db_unique_name enters in the game? What's the point?

So far reading in Forums and offical Docs I got the following assumption :

EDITED

This is what created me confusion: http://www.mail-archive.com/oracle-l@fatcity.com/msg10241.html

Then, you could have following situations:

Repeated DB_NAME is not possible within the same ORACLE_HOME (no matter OS and SID) for the DB_NAME is stenciled onto the files for that DB. There are 2 exceptions (Parallel Server/RAC though they are indeed different instances of the same database, and Primary-Standby systems, where need a common DB_NAME and Oracle provides us with the DB_UNIQUE_NAME mechanism)
For different ORACLE_HOMEs, it is possible as each installation does not see the other and they don't conflict when sharing DB_NAME, but is strongly undesirable. Besides, on Windows, every instance is mapped to a service OracleService%SID%, so that makes impossible to have two different SIDs on the same OS, even if an instance ID is defined as ORACLE_HOME + ORACLE_SID. But as by default (and it's an ORACLE recommendation for conventional setups) DB_NAME = SID, even in different ORACLE_HOME, 2 databases in Windows are likely to fail due to a SID default name that is common (so the second service cannot be created).

... but more authoritative answers are still welcome!


texts:
I'm working on MySQL database, well i have a master instance and slaves, and i need to add a new slave to Master. I adopt a simple strategy to do so: Stop slave, import data to new slave, and finally start new slave.

Well i get a dump using mysqldump, and import data even i get lot of lock. Some queries have the state update, so i did not know if it is a innodb lock or input/ouput error.

Finally, i imported the hole of data, but i suspect later that the binary log was missing on master, because the rotation log was for 1 day.

My question is how to do a good replication master->slave?