texts: Is there any manual for the visual explain in mysql? The output plan is unreadable

texts: Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?


texts: Will deleting data in master automatically delete data in slave?

texts: I have data replication setup on MySQL.  If I delete data on the master, will replication know data was deleted and automatically delete the same data on slave?


texts: MySQL: Adding an IF condition to trigger?

texts:
We've got a trigger that is overwriting the uuid value before insert.

The vers.1 of our API depended on the DB to generate the UUID's, but I'm writing v2 which is using a library that generates the UUID's in the code before sending the INSERT query.
As such, I need to update our trigger so that the new uuid's created via my Model can get stored without being overwritten.

I'm working in PhpMyAdmin (for ease) and our original trigger looks like this:

DROP TRIGGER IF EXISTS `init_uuid_company`//
CREATE TRIGGER `init_uuid_m3_company` BEFORE INSERT ON `company`
 FOR EACH ROW SET NEW.uuid = UUID()
//

Obviously, the delimiter is set to //.

My latest attempt looks like:

DROP TRIGGER IF EXISTS `init_uuid_company`//
CREATE TRIGGER `init_uuid_company` BEFORE INSERT ON `company`
 FOR EACH ROW BEGIN
  IF(NEW.uuid IS NULL OR NEW.uuid = '') THEN
   SET NEW.uuid = UUID();
  END IF;
//

But it's throwing an error saying You have an error in your SQL syntax ... near '' at line 5

Unless I'm counting wrong, line 5 is the SET line. No quotes used there.
What am I missing?


texts: Will FILLFACTOR = 99 slow down my SQL Server?

texts:
I have read this today:

  Closely related to index rebuilding is the fillfactor. When you create
  a new index, or rebuild an existing index, you can specify a
  fillfactor, which is the amount the data pages in the index are filled
  when they are created. A fillfactor of 100 means that each index page
  is 100% full, a fillfactor of 50% means each index page is 50% full.

  If you create a clustered index (on a non-monotonically ascending
  column) that has a fillfactor of 100, that means that each time a
  record is inserted (or perhaps updated), page splits will occur
  because there is no room for the data in the existing pages. Numerous
  page splits can slow down SQL Server’s performance.

I have noticed that we have a database in our company with a table Department with departmentName as varchar(50) not null. We can have around 100 departments in this table.

We also have a non clustered index as follows:

CREATE NONCLUSTERED INDEX [DEPARTMENT_NAME]
    ON [dbo].[Department]([departmentName] ASC)
    WITH (FILLFACTOR = 99, ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON,
          PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
          IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
          ONLINE = OFF, MAXDOP = 0);

So, I am wandering if this FILLFACTOR = 99 would slow down our database. What does the author mean with "page splits occur when there is no room for data"?


texts: How to express a "some of which are" and "not all are" relationship with enhanced entity-relationship diagrams?

texts:
For example:

Gardens have attribute gate, Condos have attribute door. Some of the Gardens and Condos are Private_Properties. On the other hand, Private_Properties are not restricted to only Gardens or Condos.

How can these non-exclusive relationship be illustrated with an enhanced entity-relationship diagram (EERD)?


texts: SQL Server 2017: How does Interleaved Execution work?

texts:
Interleaved Execution is part of a family of features in the 2017 query processor that consists of:

Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback

So how does Interleaved Execution work?


texts: PosgtreSQL - Change multiple fields collation in one query

texts:
I have small database with few text fields with "default" collation. I don't want to recreate database. What is the query to alter all problematic fields at once?

To change the single one I can use

ALTER TABLE a_table_name ALTER a_column_name TYPE text COLLATE a_collate;


texts: "column ... does not exist" error when calling an SQL procedure (new in PostgreSQL 11)

texts:
Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema:

DROP TABLE IF EXISTS config.trading_holiday;
CREATE TABLE config.trading_holiday
(
  id smallint GENERATED ALWAYS AS IDENTITY NOT NULL,
  holiday_date DATE,
  name CHARACTER VARYING(80)
);
ALTER TABLE config.trading_holiday
  ADD CONSTRAINT trading_holiday_pk
  PRIMARY KEY (id);

Then a very simple stored procedure, config.sp_add_holiday is created to add holidays to the table:

CREATE OR REPLACE PROCEDURE config.sp_add_holiday(holiday_date DATE,
                                                  name CHARACTER VARYING(80))
AS $$
BEGIN
  INSERT INTO config.trading_holiday(holiday_date,name)
    VALUES(sp_add_holiday.holiday_date,sp_add_holiday.name);
END
$$
LANGUAGE PLPGSQL;

Then a simple call is made to add the first holiday:

CALL config.sp_add_holiday(holiday_date='2018-01-01',name='New Years Day');

And I get the following error message:

[2018-08-07 11:56:18] [42703] ERROR: column "holiday_date" does not exist
[2018-08-07 11:56:18] Position: 21

Doing a manual insert, e.g.:

INSERT INTO config.trading_holiday(holiday_date,name)
  VALUES('2018-01-01','New Years Day');

Works successfully:

[2018-08-07 12:04:01] 1 row affected in 2 ms

Despite being new to the PostgeSQL 11 SQL procedure functionality (who isn't?), this seems like such a simple proc. What in the world am I doing wrong?


texts: How to change job output log location, when using Ola Hallengren's scripts

texts:
When using Ola Hallengren's SQL Server Maintenance Solution, the job output log is going to a local directory on the SQL server. I was thinking of placing these logs on a fileserver to allow access to more people so they can review the job output.

My questions are:

How can I change this location?
Is there any downside to changing the location?

Thanks!


texts: MySQL DB as Master for some tables and Slave for others

texts:
I have 2 DBs - DB1 (back-office) and DB2 (web application). The data needs to be synced between the 2. I'm happy with the idea of full master-master replication and may have to implement it, but really I just want some tables replicated from DB1 (back-office) to DB2 (web application) and other tables replicated from DB2 to DB1. (Some data is too sensitive to be replicated out to DB2 hence I'd use the --replicate-wild-do-table method).
DB1 is updated by internal systems and then DB2 would receive the data to be displayed by a web application, the data considered "read-only". The web application can collect data via web forms and would put it in its local database DB, and I want that data replicated to DB1 back office server.
SO, can MySQL be a master for some tables and a slave for others? If not, could I do it with Postgres?
Many Thanks


texts: SQL getting IDs from one table with multiple entries in another table

texts:
In my database I have two tables: Persons and PersonAttributes.
One Person may have several Attributes associated with it.

What I would like to do is get a list of all Persons(/IDs) that have at least the specified attributes associated with them in the PersonAttributes table.

For one attribute this can easily be done with just one WHERE statement.
My problem is that I want to do this for multiple attributes.

The only solution I could come up with is doing a SELECT for each attribute and then JOINing them.
While I can build such a query programmatically it seems pretty complex and I was hoping that there may be an easier solution.

For better understanding here is a SQLFiddle including my solution for 3 attributes.

EDIT: Changed the SQLFiddle link. Query now looks like this:

SELECT Persons.ID
FROM Persons
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'b') t1
    ON Persons.ID = t1.ID
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'c') t2
    ON t1.ID = t2.ID
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'd') t3
    ON t2.ID = t3.ID;


texts: Changing database compatibility from legacy CE to 120

texts:
Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)

From DEV point of view all the testing has been done and they dont see much diff and want to move to prod based on their testing.

In our testing ,For certain process where we see slowness like in SP's we found the part of statement that was slow and added query traceon hint , something like below keeping compat to 120, which helps keeping performance stable

SELECT  [AddressID],
    [AddressLine1],
    [AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
    [City] = 'Burbank'
OPTION (QUERYTRACEON 9481);
GO

UPDATE- Editing question based on more findings-

Actually we found things getting worst for a table which calls scalar function within a computed column-

below is how that column looks

CATCH_WAY AS ([dbo].[fn_functionf1]([Col1])) PERSISTED NOT NULL

and part of query where it goes weird is somewhat looking like below

DELETE t2
   OUTPUT del.col1
          del.col2
          del.col3
   INTo #temp1
FROM #temp2 t2
INNER JOIN dbo.table1 tb1 on tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1])
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'')

I know function is being called and is slow but the problem is with current compat i.e. 100 runs OK'ish slow but when changed to 120 it gets X100 times slow and if kept at 100 its X100 faster.
What is happening ?


texts:
I have a bunch on time entries, they have entre_dates and hours associated with them like so.

drop table if exists data  cascade;
create table time_table (id int, entry_date date, hours numeric);
insert into time_table
values
(1, to_date('01-01-2019', 'dd-mm-yyyy'), 1),
(2, to_date('01-02-2019', 'dd-mm-yyyy'), 2),
(3, to_date('01-03-2019', 'dd-mm-yyyy'), 3),
(4, to_date('01-04-2019', 'dd-mm-yyyy'), 4),
(5, to_date('01-05-2019', 'dd-mm-yyyy'), 5),
(6, to_date('01-06-2019', 'dd-mm-yyyy'), 6);

For each time entry, I would like to sum all the hours that occur on and before that entry date to get a running total.

Here's my attempt but I have no idea what I'm doing:

SELECT t1.entry_date, t1.hours, htd.running_total
FROM time_table t1
INNER JOIN (
    SELECT SUM(t2.hours) AS running_total
    FROM time_table t2
    WHERE t2.entry_date <= t1.entry_date
) AS htd ON t1.id = t2.id;

I'm trying to get the following result:

Date       | Hours | Running Total
-----------------------------------
01-01-2019 | 1     | 1
01-02-2019 | 2     | 3
01-03-2019 | 3     | 6
01-04-2019 | 4     | 10
01-05-2019 | 5     | 15
01-06-2019 | 6     | 21

What's the correct way to do this query?


texts: Mathematically compare multiple 3 rows sets of table and record result in new column

texts:
I need to isolate 3 row groups in my overall query and perform the same CASE statement on each group and return a result in the final output

I believe i may have to use a CTE (which i have not done before)
but im not sure if this is the right thing to do

I will reuse a graphic from a previous question to illustrate.
The maths is simple, i just am unsure as to how to compare the 3 rows distinctly from the main table.

SQL Server 2012


texts:
I use this function INSERT (http://www.smallsql.de/doc/sql-functions/string/insert.html) to add a value in a data:

INSERT (log, POSITION(' edited' IN log), 1, 'NOW()')

I use also the NOW function to add the date in the data but it does not work. What is the problem?


texts: What is meant by Orphan Lock and Orphan Sessions?

texts:
I have came across this term Orphan locks and Orphan sessions and I really have no idea what that is. I have tried to search it on Google and I still haven't got any useful info/results regarding what it mean.

All I get till now is that orphan locks are some kind of session locks which results in deadlocks after executing query or queries. (please correct me if I get it wrong)

Can someone please explain this a bit ? What are Orphan Locks ? Are they the same as Orphan sessions ? How do they form and what they have to do with sessions ?

p.s: I am still newbie to this so I apologise if it sounds all too basic.


texts: Problem with PostgreSQL and the geqo optimizer

texts:
I'm trying to compare the two execution plans (exhaustive search and geqo) from the optimizer, but I always get the same plan from both types.
These are the variables I use for geqo:

SET geqo = true;
SET geqo_threshold = 2;

My queries have 8-12 FROM items involved, so the optimizer would definitely use the geqo algorithm.

I tried to add more variables to my queries to force the optimizer to choose an other plan:

SET geqo_effort = 2;
SET geqo_pool_size = 2;
SET geqo_generations = 1;

But like I said, I always get the same plan as the plan from exhaustive search.
Is there any possibility to show which algorithm (exhaustive search or geqo) the optimizer used to create the execution plan? Or are there other ways to force the optimizer to use geqo?


texts: What is the meaning of "select * from relational(owner.table)"?

texts:
We recently got some errors in our alert log of the form

301598: Tue Jul 16 03:58:29 2013
301599: ORA-01555 caused by SQL statement below
301600: SELECT * FROM RELATIONAL(<owner>.<table>)

I know how to handle ORA-01555 errors, but I never encountered something like SELECT * FROM RELATIONAL. I looked in DBA_OBJECTS, but there is no such function/operator/whatever.

Googling for 'SELECT * FROM RELATIONAL' only returned questions related to ORA-01555.

So, my question is: What is the meaning of the RELATIONAL keyword here?


texts: How do I find all roles that grant access to objects under a particular schema?

texts: I need to assign a user to any roles that grant access to tables under a particular schema. What would I query to find those roles?


texts: How does MySQL resolve schema information?

texts:
I’ve used MySQL on windows7.

Recently my machine was infected by a virus.

After that SHOW TABLES and SHOW DATABASES statements don’t show any tables and databases but I can see data from tables which I’ve created with SELECT statement.

In addition mysqldump doesn’t work. It makes dump file but there is no data.

mysqldump can’t resolve database and table names.

I think SHOW statements and mysqldump use information_schema table.

Then my question is how does MySQL resolve schema information when it create information_schema table?

There must be some resource that information_schema refer to.

I believe the resource was destroyed by the virus,and I want to fix it.


texts: calling a function with parameters in insert

texts:
Is it possible in SQL ellegantly achieve the following:

insert into my_table (the_geom) VALUES
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)),
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)),
    .....

which involves invoking an external function geomfromwkb with two parameters?

So far I have a un-elegant solution. The first solution is of this type:

insert into mytable (the_geom) select
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)) union
    geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)) union
    .....

The second one uses the prepare statement:

PREPARE my_insert AS INSERT INTO geom_example (the_geom)
    VALUES (geomfromwkb($1,4326));
EXECUTE my_insert ('\x010100000058b4b610a73a27408144b7147f194840');
EXECUTE my_insert ('\x010100000058b4b610a73a27408144b7147f194840');
statement: DEALLOCATE my_insert;

Question 1: Is there anything more ellegant than this?

Question 2: What does the DB do when it sees a bunch of unions?

Question 3: does the prepare really speed up things compared to one fat insert?

Question 4: the byte sequence is of the geometry type. It is possible to skip the consistency checks (to insure that this is indeed the geometry byte sequence) to speed up inserts?


texts: Does it make sense to upgrade to a newer PostgreSQL JDBC driver?

texts:
I would like to know if it could be possible to gain any performance benefits from upgrading the default PostgreSQL JDBC drivers from applications like Confluence or JIRA.

Assume that you cannot modify the application that is using them, the question if there are any visible benefits from switching from the current driver postgresql-8.4-702.jdbc3.jar to something newer?

the backend is using PostgreSQL 9.1 and soon will be upgraded to 9.3
JVM being used is the latest 1.7 from Oracle

Does it make sense or not? If so, which version should I pick?


texts:
How can i return a date only format (DD/MM/YYYY) from a DateTime format column? example below

Modified Date
2007-09-01 02:00:00.000
2005-09-01 05:00:00.000

Output:

Date
09-01-2007
09-01-2005


texts: Contention on tempdb due to high concurrency temp table creation from multiple databases?

texts:
I have an environment with ~100 databases, all with the same schema.  There is a stored procedure on each database that creates a #temp table and drops it, and is running very often (every 30 seconds or so per user, and there are 1000+ users). It's doing a lot more than just this: We are loading potentially thousands of rows into this temp table and just dumping the whole thing out, basically it aggregates a bunch of data.

Since all the databases create the same temp table, are they all competing with each other?  Or does each database get its own version of the temp table in tempdb?

It seems like they are having contention because I see a large amount of PAGE_LATCH waits, all on the same page in tempdb (2:3:1041580), which is not a GAM, SGAM, or PFS page, and 90% of the waits seem to be from this same stored procedure across all databases.  These waits are 90% of the overall waits on the server and are causing blocking.

I did a DBCC PAGE, and it appears to be sysobjvalues (from the object_ID 60 in the header).  I ran:

SELECT OBJECT_Name(object_ID), *
FROM sys.dm_db_database_page_allocations(2,60,NULL,NULL,'DETAILED')

I get 153 rows, not sure exactly what I'm looking at here.  Looks like it's IN_ROW_DATA and LOB_DATA in allocation_unit_type_desc.

I have 8 tempdb files on a separate volume from the other files.  I have 16 cores but my understanding is that since it's not an allocation page it might not help to increase the number of files.  Using SQL Server 2017 Enterprise.

I feel like the best bet is to just tell the developers to remove the temp table here but that will require reworking the logic and will take some time.  Is there anything else I can do to avoid these page latches while I wait for the dev fix?


texts: Error PRVG-11322 while atempting to install ODAC 12c release 3

texts:
I have been trying to install the Oracle Developper Tools with ODAC update 3 (because I need it to work with Entity framwork 6) but the Unified installer bring me a PRVG-11322 Error.

It seem to be linked to the fact that my computer have an underscore in his hostname (and I can't change it, work domain-joined workstation)

Altought the install instructions mention installation using xcopy, I can't find the instruction that are supposed to be in the zip package.

I am pretty much out of ideas. Do you know how to do it.


texts:
What would be the preferred method for speed and less database stress using a between or less than?
SELECT LogId
FROM [Master].[dbo].log mlog
WHERE cast(LogDate as Date) BETWEEN '2019-01-01' AND '2019-12-31'

Or, would it be better to do this?
SET @StartLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate >= '2019-01-01T00:00:00.000')

SET @EndLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate < '2019-12-31T00:00:00.000'
ORDER BY LogDate DESC);

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE mlog.LogId >= @StartLogId
    AND mlog.LogId <= @EndLogId;
```


texts:
I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message:

$ mysql -u root < mydata.sql
ERROR 1166 (42000) at line 65203: Incorrect column name ''

MySQL Version is 5.5.32-0ubuntu0.13.04.1 and this is a head from SQL file:

$ head -n 10 mydata.sql
-- MySQL dump 10.13  Distrib 5.5.22, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version   5.5.22-55-log

This is a snapshot of the line that making an issue:

$ sed -n '65200,65203p' < mydata.sql
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */

Line below 65203:

/*!50013 DEFINER=`mysql`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_payments` AS select concat(`paypal_payments`.`id`,1) AS `id`,
***SNIP****
,concat(`paypal_payments`.`payer_firstname`,' ',`paypal_payments`.`payer_lastname`,' ',`paypal_payments`.`payer_email`) AS `customer`
***SNIP***
 from `paypal_payments` union select concat(`payment_data`.`id`,2) AS `CONCAT(id,2)`,`payment_data`.`id` AS `id`,`payment_data`.`txndate` AS `txndate`,`payment_data`.`valuedate` AS `valuedate`,concat('DBS - ',`payment_data`.`category`) AS `CONCAT('T - ',category)`,(`payment_data`.`credit` - `payment_data`.`debit`) AS `credit-debit`
***SNIP***
,NULL AS `NULL`,NULL AS `NULL`,NULL AS `NULL`,`payment_data`.`note` AS `note`,`payment_data`.`remaining` AS `remaining` from `payment_data` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

How can I resolve this issue?


texts: Exclude lowest ranking team and repeat the process

texts:
The following are the first few rows of a SQLite table which is from a league-based championship:

+---------+-------+-------------+---------------+----------+---------------+
| EventID | Round |  HomeTeam   | HomeTeamScore | AwayTeam | AwayTeamScore |
+---------+-------+-------------+---------------+----------+---------------+
|    1210 |     1 | GERMANY     |            16 | AUSTRIA  |             4 |
|    1210 |     1 | ICELAND     |            10 | BELARUS  |            10 |
|    1210 |     1 | FINLAND     |            12 | FRANCE   |             8 |
|    1210 |     1 | NETHERLANDS |            11 | WALES    |             9 |
|    1210 |     1 | ROMANIA     |            10 | NULL     |             0 |
+---------+-------+-------------+---------------+----------+---------------+

I can query the final standings with:

SELECT
  team,
  SUM(vp) AS TotalVP
FROM
  (
    SELECT
      hometeam AS Team,
      hometeamvp AS VP
    FROM
      matchresults
    WHERE
      eventid = 1210
      AND team IS NOT NULL
    UNION ALL
    SELECT
      awayteam AS Team,
      awayteamvp AS VP
    FROM
      matchresults
    WHERE
      eventid = 1210
      AND team IS NOT NULL
  )
GROUP BY
  team
ORDER BY
  totalvp DESC;

which outputs:

+--------+---------+
|  Team  | TotalVP |
+--------+---------+
| FRANCE | 498     |
| MONACO | 487     |
| NORWAY | 486     |
| ...    | ...     |
+--------+---------+

(IS NOT Null is needed because odd number of teams entered the tournament) However, that is where my SQL skills end. How can I perform the following (which are, I believe, progressively more challenging):

Get the final standings ignoring the matches played against a certain team. (For example: Team FINLAND is disqualified. Now we should a team's match score against FINLAND should be ignored/excluded. And do not display that team in the standings.
Get the final standings ignoring the matches played against the lowest scoring team. This time, display the ignored team (the teams's score should be 0).
(This one is way above me.) Suppose there are 10 teams competing in the tournament. The above requirement, (2), will display 10 teams where the matches played against the lowest ranking team are ignored and the lowest ranking team is also displayed with score 0. Now repeat the exact same procedure with the remaining 9 teams. Thus, this time there will be again 10 teams displayed but lowest ranking two teams have score 0. How do I automate the repetition of this process?


texts:
I was just modifying my script that I use to get size of database file and wanted to use max_size  to display if a max size was set for a database file.  As per Microsoft Docs, the column description is

  Maximum file size, in 8-KB pages:

  0 = No growth is allowed.

  -1 = File will grow until the disk is full.

  268435456 = Log file will grow to a maximum size of 2 TB.

I feel like I am missing something obvious, but under what condition does it show a value of 0?  I tried removing autogrowth and it comes up as -1.


texts: Suddenly SQL server restarted with pending task count increased

texts:
We are trying to understand the issue where SQL server was suddenly restarted on its own-
Its a SQL server 2012 SP4 -GDR applied with 40 CPu's HT enabled , thus total of 80 logical processors-

MAXDOP=8 , CTOP 5

From error logs we found

/**********************/
BEGIN stack Dump
Non- Yielding Scheduler /**********************/

Error messages just at the time of restart of SQL. Also there was no mini dump created as checked.
Yes we have quite some queries running at that time. Top 3 waits seen were:-
1.TranLogIO
2.CXPACKET
3.PAGELATCH SH

However we also noticed wait called SOS WORKER as collected from system health XE which i believe is nothing else but THREADPOOL. Therefore i went further and analyse the query processing details from that system health and found at the time of non yielding scheduler errors below was logged

At 19:46 maxworkers- 2944 workers created 789 oldest pending taskwait time 0 pending task 4
At 19:51 maxworkers- 2944 workers created 982 oldest pending taskwait time 256987 pending task 165
At 19:51 we started seeing restart happened.

Question is why there would be a threadpool wait if almost 2000 workers are still available from above calculation. Also why those pending task count 165 when queries have so many schedulers available to run and complete the request for bunch of queries waiting on CXPACKETS?
Edit: Updating my question for couple of waits also seen from XE
SOS_MEMORY_TOPLEVELBLOCKALLOCATOR

As i am reading info on above wait here https://www.sqlskills.com/help/waits/sos_memory_toplevelblockallocator/
Based on the blog there is a fix in SP3 CU but with TF T8075 : I am currently on SP4-GDR latest patch, do i still need to apply the TF, though i do not see any messages like Failed allocate pages: FAIL_PAGE_ALLOCATION 513 in error log
Not sure if it helps- i see total server memory dropping a gig now and then and then matching upto target server memory which is = max server memory (750GB)
Most of the times total=target=max server memory. The drops are just by a gig and not much
Memory info= total ram: 880 GB Max memory : 750GB  Min server memory: 130 GB
Its a 2 node windows cluster and no other SQL instance is shared. Resource governor is not enabled
Thanks


texts:
I have a very strange slow insert issue, here I am providing the table structure, and the insert statement used.

The database is clean, contains no other tables, no indices, no constraints, no triggers.

I tested this on two different machines, with high speed SSD drives, 8 cores 3.5 GHz processors. The server can read IO up to 700MB/s and can write up to 300MB/s

However, while executing the below insert statement, the processor is almost at 5% max and the IO operation less than 500KB/s which means that SQL Server is not using the server resources properly.

The maximum I can achieve is 250 row/s. The below insert statement is inserting 1000 rows and require 4 seconds to finish.

Any idea what is causing this slow in inserting?

SQL Server used is 2014 Developer Edition SP2

Thanks in advance.

CREATE TABLE [dbo].[MyTable](
    [ID] [int] IDENTITY(1666811137,1) NOT NULL,
    [col0] [smallint] NULL,
    [col1] [smallmoney] NOT NULL,
    [col2] [tinyint] NOT NULL,
    [col3] [tinyint] NULL,
    [col4] [tinyint] NOT NULL,
    [col5] [tinyint] NOT NULL,
    [col6] [datetime] NOT NULL,
    [col7] [varchar](15) NOT NULL,
    [col8] [varchar](20) NOT NULL,
    [col9] [tinyint] NOT NULL,
    [col10] [tinyint] NULL,
    [col11] [tinyint] NULL,
    [col12] [tinyint] NULL,
    [col13] [tinyint] NULL,
    [col14] [tinyint] NULL,
    [col15] [tinyint] NULL,
    [col16] [bit] NULL,
    [col17] [smallint] NULL,
    [col18] [varchar](17) NULL,
    [col19] [smallint] NULL,
    [col20] [tinyint] NULL,
    [col21] [varbinary](254) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/********* INSERT *********/
DECLARE @i int
SET @i = 0

WHILE @i < 1000
BEGIN
    SET @i = @i + 1

    INSERT INTO MyTable (col3, col1, col2, col4, col5, col6, col7, col8, col9, col21, col10, col11, col12, col13, col14, col18, col15, col16, col0, col19, col17, col20)
    VALUES (5, 0, 1, 1, 1, GETDATE(), 'ABC', '1234567890', 0, CAST('0x74657374' AS VARBINARY(MAX)), 1, 1, 1, 1, 0, 'VAL', 1, 0, 37, 37, 2, 0)
END


texts: Postgres 12.1 uses Index Only Scan Backward instead of index when LIMIT is present

texts:
I have a medium sized table (~4M rows) of "functionCalls" which consists of 2 columns, input and function (both ids for another table):

  Column  |  Type   | Collation | Nullable | Default
----------+---------+-----------+----------+---------
 input    | integer |           | not null |
 function | integer |           | not null |
Indexes:
    "functionCall_pkey" PRIMARY KEY, btree (input, function) CLUSTER
    "functionCallSearch" btree (function, input)
Foreign-key constraints:
    "fkey1" FOREIGN KEY (function) REFERENCES function(id) ON UPDATE CASCADE ON DELETE CASCADE
    "fkey2" FOREIGN KEY (input) REFERENCES input(id)

I want to find all rows that match a certain function, which is why I added the functionCallSearch index. Here is my query:

SELECT c.input FROM "functionCall" c
INNER JOIN "function" ON (function.id = c.function)
WHERE function.text LIKE 'getmyinode'
ORDER BY c.input DESC
LIMIT 25 OFFSET 0;

This takes forever (currently ~ 20s) because pg refuses to use the index, and decides to do a Index Only Scan Backward on the primary key instead:

 Limit  (cost=0.71..2178.97 rows=25 width=4) (actual time=12903.294..19142.568 rows=8 loops=1)
   Output: c.input
   Buffers: shared hit=59914 read=26193 written=54
   ->  Nested Loop  (cost=0.71..135662.48 rows=1557 width=4) (actual time=12903.292..19142.561 rows=8 loops=1)
         Output: c.input
         Inner Unique: true
         Join Filter: (c.function = function.id)
         Rows Removed by Join Filter: 3649900
         Buffers: shared hit=59914 read=26193 written=54
         ->  Index Only Scan Backward using "functionCall_pkey" on public."functionCall" c  (cost=0.43..80906.80 rows=3650225 width=8) (actual time=0.040..17083.489 rows=3649908 loops=1)
               Output: c.input, c.function
               Heap Fetches: 3649909
               Buffers: shared hit=59911 read=26193 written=54
         ->  Materialize  (cost=0.28..2.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=3649908)
               Output: function.id
               Buffers: shared hit=3
               ->  Index Scan using function_text on public.function  (cost=0.28..2.30 rows=1 width=4) (actual time=0.023..0.026 rows=1 loops=1)
                     Output: function.id
                     Index Cond: ((function.text)::text = 'getmyinode'::text)
                     Buffers: shared hit=3
 Planning Time: 0.392 ms
 Execution Time: 19143.967 ms

When I remove the LIMIT this query is blazingly fast:

 Sort  (cost=5247.53..5251.42 rows=1557 width=4) (actual time=3.762..3.763 rows=8 loops=1)
   Output: c.input
   Sort Key: c.input DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=6 read=4
   ->  Nested Loop  (cost=0.71..5164.97 rows=1557 width=4) (actual time=0.099..3.739 rows=8 loops=1)
         Output: c.input
         Buffers: shared hit=6 read=4
         ->  Index Scan using function_text on public.function  (cost=0.28..2.30 rows=1 width=4) (actual time=0.054..0.056 rows=1 loops=1)
               Output: function.id
               Index Cond: ((function.text)::text = 'getmyinode'::text)
               Buffers: shared hit=2 read=1
         ->  Index Only Scan using "functionCallSearch" on public."functionCall" c  (cost=0.43..5103.71 rows=5897 width=8) (actual time=0.039..3.670 rows=8 loops=1)
               Output: c.function, c.input
               Index Cond: (c.function = function.id)
               Heap Fetches: 8
               Buffers: shared hit=4 read=3
 Planning Time: 0.514 ms
 Execution Time: 3.819 ms

Things I've tried so far:

After reading Postgres sometimes uses inferior index for WHERE a IN (…) ORDER BY b LIMIT N I have checked n_distinct - but is not that far off, pg_stats says 623 while SELECT COUNT(*) FROM (SELECT DISTINCT function FROM "functionCall") returns 1065.
I've increased the table SET STATISTICS to 10k and ran ANALYZE. That cuts the time in half (9s) but still won't use the index I created.

Why is this? And how can I fix this?


texts: SQL Server connection failed for domain user from Mac OS machine

texts:
I have SQL Server 2008 R2 SP1. I am using connection string to connect to SQL Server.

In connection string I am passing the driver path, server name, TCP port number, user name with which I want to connect, password, DSN name, TDS driver version number, db name.

I am using Free TDS driver for connecting from Mac OS machine.
On SQL server side I am having Windows authentication mode.
When I tried to connect by entering the user name and password of the user who has installed the SQL server or admin of the SQL server, it works fine.

But if I try with normal domain user, I am getting the SQL Server connection failed error.

After searching about it I found that some authentication is failing. Can anyone help me in resolving this issue?

I believe that some settings need to be changed or there should be some modification on connection string.


texts: Random (rare): MySQL Stored Procedure Does Not Exist

texts:
I have a stored procedure that runs each day, for roughly 20 schemas (each have their own copy), for the last 3-4 months.

I got the error "Procedure [xxx] does not exist" for all databases in my report. They do exist, and they work correctly every time it's run. I just ran the cron job manually and it worked perfectly.

It's not a creation, implementation, network, or permissions issue, as it's been working for the past 3-4 months without problems. It's critical this stored procedure runs without fail.

Q: What can cause a Stored Procedure to "not exist" at random, or could some other factor be involved?


texts: Table design: how to store denormalized Excel data


texts: Run Transaction Log backups only during Work hours?

texts:
Currently we have a number of databases that are in full recovery model. This is a conscious decision.

We also run the transaction log backups every 15 minutes.

But we run the transaction log backups only during the hour when people work with the databases, that is between 7 in the morning until 8 in the evening.

My question is:

On a technical level, is this a good practice or not?


texts: I want to discover how much memory is being used by each table. Is this information available in DMVs or elsewhere?


texts: What happen if innodb_log_file become full?

texts: Once innodb_log_file ib_logfile0 & ib_logfile1 got full while running big transaction,so what is mysql next action in this situation ?


texts: How to find inputs that caused different query plans in Query Store

texts:
Query store shows that a completely parameterized query has multiple query plans:

How can I find the input that is causing such long response times?

Why would the same parameterized query end up with different query plans?  Where can I learn more about that?


texts: Rename the database name through terminal

texts: My database name is studenttRecord. I would like to correct the spelling to studentRecord. How can I change it if possible?


texts: Index on an input value without saving it to a column?

texts:
Short version:

I'm looking for a way to give Postgres a value just for an index (as opposed to saving that value to a column and then indexing it). Similar to an index on an expression, but the expression will be computed outside of Postgres. If this is possible, how can it be done?

Long version:

I've created an index on a plpgsql function that normalizes an email address, however using it is proving to be very clunky - either I have to remember to always call the function whenever I use the index, or I have to create a view and remember to use that for reads but not for writes... On top of that, the plpgsql implementation is 3x slower than the python implementation (and 30x uglier imo), and RDS doesn't support plpythonu. So I might as well go back to go back to doing it in the application, but I don't want to go back to saving a computed column and indexing on that; I still want to have the normalized email only in the index.

The way to do this I guess would be to somehow create a fake column name, and the input for this fake column would be used for the index. If this is possible, how can it be done?


texts: Making index sizes more efficient in Postgres10 when indexing most fields in a table

texts:
I've got a 3 field table with some time series data in it. The fields are a timestamp, what I'll call a device number and the measured value. The table has about 550 million records in it and is 27GB.

The table has a compound index incorporating the timestamp and the device number. The table will always be queried via these two values, virtually without exception. The index is 16GB.

Here are the definitions:

CREATE TABLE rollup_by_minute (
    minute timestamp with time zone NOT NULL,
    dev_id integer NOT NULL,
    value bigint DEFAULT 0 NOT NULL
);

CREATE UNIQUE INDEX idx_rollup_by_minute_minute_dev_id ON
    rollup_by_minute USING btree (minute, dev_id);

This seems to be an unhappy condition. I'm basically replicating 2/3 of the table in the index with no real way around it. In something like MS-SQL I would think I could use a clustered index for this.

Is there anything I can do in Postgres to make this a little more space efficient?

P.S. At this point I am almost tempted to just put the last field in the index so that I can get everything from there and skip accessing the heap altogether. I'd just have to suck up the fact that every table record is double sized.


texts: Can I create a SQL Job to run on specified dates?

texts:
I want to create a new SQL job that runs the beginning of every quarter:
01/01
04/01
07/01
10/01
Is this possible? I know i can set it to run every 90 days but that's not exact. I am using SQL Server 2008.  Thanks in advance!


texts: Multiple named Instances and Availability Groups

texts:
We have a two-node multi-subnet Windows Server 2012 R2 cluster in two regions. We have three named SQL Server 2012 standalone instances on each node and are in the process of setting up Availability Groups on each SQL instance. I have set one up on one instance successfully, but when I try to set up the second AG on the second named instance I get the following error message:

  A connection timeout has occurred while attempting to establish a connection to availability replica DPA01\BAPP01 with id [567C-F10B-43C3-A2DB-BEDDD307589B]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance."

It looks like it does not support multiple endpoints with different ports on a single node. Has anyone come across this scenario? First instance, I used 5023 as the end point port and for the second I used 5022. But I cannot telnet from node 2 to 1 on port 5023 whereas I can telnet from Node 1 to 2 on port 5023. Node 1 is not listening that port.

My simple question: Is the following setup supported?

  Having multiple SQL named instances on two node windows cluster and Always On Availability Groups on each Instance.

Example of my setup:

Node1
Named instances: SQLINS1, SQLINS2
Availability Groups - AO1 on SQLINS1 and AO2 on SQLINST2

Node2
Named instances: - SQLINS3, SQLINS4
Availability Groups - AO1 on SQLINS3 and AO2 on SQLINST4

Where:

Replicas of AO1: SQLINS1, SQLINS3
Replicas of AO2: SQLINS2, SQLINS4

If my question is not clear I am happy to give you more details about the setup which I am trying.


texts: Correct pg_hba.conf to allow script to run locally with a specific db-user

texts:
I've got a working postgreSQL database with following pg_hba.conf:

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
hostssl all             dbadmin         my.ip.at.home/32       md5

Which, to my understanding is correct, and only allows local connections and from my home-ip via SSL. Both via a md5 password.

I now want a local bash script to run some things on the databases (backups). For this I created a new user backup_user; as the script needs to run autonomous my thoughts were to create a line for this user to trust from the the lo:

host   all           backup_user     127.0.0.1/32    trust

But when I login via SSH and run this command I have to type the password....

psql -U backup_user -p 5454

What is the best way to let the script do its thing?
If it matters, I want this script to sun: Automated Backup


texts: Help with Connecting to SQL Server using Oracle SQL Developer

texts: I am trying to connect to SQL Server using Oracle SQL Developer. I was able to get the SQL Server JDBC installed on SQL Developer and am able to see the SQL server Tab on the Connections page. I have SQL server running on the machine with 4 Databases that I am wanting to connect. When I try to connect though I am getting a "Failure - Cannot Connect with Microsoft SQL server on Localhost". I have not changed any defaults when installing SQL Server . Any Help appreciated.


texts: Installation of Oracle 11gR2 in Oracle Linux 7

texts:
Installation of Oracle 11gR2 is failed with below error, on Oracle Linux 7 OS.

> INFO: Building Client Shared Libraries INFO: Building Client Shared
> Libraries INFO: The output of this make operation is also available
> at: '/u01/app/oracle/product/11.2.0.4/db_1/install/make.log' INFO:
>
> INFO: Start output from spawned process: INFO:
> ---------------------------------- INFO:
>
> INFO: /u01/app/oracle/product/11.2.0.4/db_1/bin/genclntsh
>
> INFO: /bin/ld: cannot find -lstdc++_nonshared
>
> INFO: collect2: error: ld returned 1 exit status
>
> INFO: genclntsh: Failed to link libclntsh.so.11.1
>
> INFO: make: *** [client_sharedlib] Error 1
>
> INFO: End output from spawned process. INFO:
> ---------------------------------- INFO: Exception thrown from action: make Exception Name: MakefileException Exception String: Error in
> invoking target 'client_sharedlib' of makefile
> '/u01/app/oracle/product/11.2.0.4/db_1/rdbms/lib/ins_rdbms.mk'. See
> '/u01/app/oraInventory/logs/installActions2017-11-09_12-52-50AM.log'
> for details. Exception Severity: 1

Let me know that i need to try reinstall it or any work around can be done.


texts: psql command output to print error via -L output.log

texts:
wanted to know how to print error output via psql command
using below :
output of Insert_log.log shows only query statement not the error :
psql -h ${hostname} -p 5432 -U postgres -d ${DB_NAME} -a  -f ${DIR}/log.sql -L Insert_log.log

[ec2-user@scripts]$ ./log.sh
\o
INSERT INTO mig.qb_tax_rates (id, xxx"}}', 18977);
psql:/home/ec2-user/scripts/log.sql:2:
ERROR:  column "q_id" of relation "qb_tax_rates" does not exist
LINE 1: INSERT INTO mig.qb_tax_rates (id, q_id, sync_token, qb_updat...

[ec2-user@ip-10-99-99-999 scripts]$ cat Insert_log.log
********* QUERY **********
INSERT INTO mig.qb_tax_rates (id, xx}}', 18977);
**************************


texts: Why does In-Memory feature not support foreign keys?

texts:
Why does Sql-Server 2014 In-Memory feature not support primary/foreign key relations? Can anyone explain what the problem with foreign key relationships and in-memory is?

I guess just and only this limitation alone may be a show stopper for 90 or more percent of all Sql-Server customers for usign the in-memory feature.
Does anyone know if this will be added in future versions?


texts: Does binlog_group_commit_sync_delay affect existing binary logging in MySQL

texts: We want to tune the setting binlog_group_commit_sync_delay to increase replication performance. Our slave is currently two days behind. Does changing this setting affect the current backlog or will we see the impact after two days?


texts:
I have query that returns a count of records with a matching date:

DECLARE @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
SELECT
DISTINCT(CAST(Response_Date AS DATE)) AS Response_Date
,COUNT(CAST(Response_Date AS DATE)) AS Response_Count
...
WHERE Response_Date BETWEEN @StartDate AND @EndDate

And I get a return as expected.

^Response_Date^Response_Count^
|2016-09-22   |2             |
|2016-09-24   |4             |
...

However, these values will be used in report builder in lists and charts.  The problem is that there are gaps between the dates.  Is there some way to run the query returning all dates from previous months, and a 0 when nothing is on that date?

Here is my current query:

DECLARE @JurisdictionName AS CHAR(30) = 'Something'
DECLARE @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

SELECT
    DISTINCT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Date
    ,COUNT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Count
FROM
    Response_Master_Incident
WHERE EXISTS
    (SELECT *
        FROM Activity_Log
        WHERE
        Activity_Log.Radio_Code IS NOT NULL
        AND (Radio_Name LIKE 'V[0-9][0-9]' OR Radio_Name LIKE 'U[0-9][0-9]')
        AND Response_Master_Incident.ID = Activity_Log.Master_Incident_ID
        )
    AND Response_Master_Incident.Time_First_Unit_Assigned IS NOT NULL
    AND Response_Master_Incident.Response_Date BETWEEN @StartDate AND @EndDate
GROUP BY CAST(Response_Master_Incident.Response_Date AS DATE)


texts:
Is there any way to identify when a specific index was last rebuilt; unless it is used as a batch maintenance activity?

In SQL Server 2008R2 or in advanced versions.


texts: Will TRUNCATE stop the increase of the DB2 database size?

texts:
we are periodically doing the TRUNCATE of some large tables in database.
After that in order to release disk space we are doing the command:

alter tablespace MAXDATA reduce max

and everything works smoothly.

I have a question if for example database size iz 100 GB, and one table size is 5GB and we execute TRUNCATE command on that table, I understand that it will not release database size back to 95 GB in order to decrease it for 5 GB we must execute ALTER
but will it stop the growth of database size for new data which will be populated?
So does it mean that database size will be 100GB and after new 5GB data in that same table is being generated it will not trigger additional 5GB of space (105 GB) but it will keep the 100GB size.

Please provide me explanation for this.

Thanks,


texts: Select rows from two joined table that not in a third table

texts:
I have 3 table A,B,C,D with the same columns and I'm trying to retrieve the
user_id that are in table a and b but not c.

For instance:
Table A
user_id     closed acct
10102345     Yes
12456786     Yes

Table B
user_id  closed acct
10102345     Yes
12456786     Yes

Table C
user_id  closed acct
45345696     No
45698788     Yes
12456786     Yes

Here is what the code that I try but it taking so long to execute:
   SELECT user_id  FROM TableA WHERE user_id  NOT IN (SELECT DISTINCT user_id
   FROM TableB) OR user_id  NOT IN (SELECT DISTINCT ID FROM Table C)


texts:
I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using.

How do you document your database? (SQL-Server)
What tool do you use?
Documentation Storage Format for database schema/meta-data?

Word documents
Excel spreadsheet
Plain Text

Documentation process or policies?

I am not talking about reverse engineering / document a existing database, but mainly on the documentation best practices while you develop your system/database.


texts:
MySQL permits you to store a zero value of '0000-00-00' as a “dummy date.”: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

I'm currently inserting into this field using STR_TO_DATE(?, "%M %e, %Y %l:%i:%s %p") where ? could be June 5, 2019 5:14:47 PM, for example.

What string can I pass to this STR_TO_DATE to get it to store zero?


texts:
I have a master and slave on Linux (centOS 7). The rs.initiate(cfg) was done, but its shows the below output when trying to add secondary via rs.add:

genienational:PRIMARY> rs.add("localhost.localdomain:27017")
{
     "ok" : 0,
     "errmsg" : "The hosts test.appraisergenie.com:27017 a
nd localhost.localdomain:27017 all map to this node new co
nfiguration version 2 for replica set genienational",
     "code": 103
}
genienational:PRIMARY> use local
switched to db local

How do I resolve?

Version: The master is on Enterprise. I think 3.2 and the slave is on 2.6.


texts: SQL Server Database Keeps Semi-Detaching Itself

texts:
Forgive me if I am not using all the proper terms but I have databases that will detach themselves from SQL Server. In SQL Server Management Studio the plus sign will be missing next to the database, or the database will have no tables.

Now the fix for this is easy, simply detach and attach the database and we're back up and running. BUT what is the cause for this?


texts: Is Scale out deployment for SSRS needed in an Availability group?

texts:
This is my first attempt at setting up SSRS, so I apologize if any question appears basic.  I've researched as much as I can and am confused about the below things.  I have my environment described below as well.

We have a 2 node AG in a WSFC.  lets call them sql1 and sql2, with listener of sql-listener.  SQL 2016 enterprise.  My intent is to have all application connections running reports sent to the secondary node to keep the load off the primary server.  Nodes are setup in synchronous commit, read/write primary mode, and readable secondary.  Questions:

is Scale out deployment required in a setup like this?  I can't seem to get Sql2 to show up on the scale-out screen on Sql1 for me to add.  Event viewer is giving errors on Sql2 like "report server windows service cannot connect to the report server database" and The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead."
Is the above error likely caused by using the built in virtual service account and not a domain account?  both nodes have the report server db's and are synced in the AG.  I have re-setup SSRS using the config manager on both nodes and they are identical in every way except the URL's reflect their local servernames   http://sql1:80/ReportServer and http://sql2:80/reportserver.  I had also added a new URL with the listenername on both servers, but the issue remains.
3.) the connection string must also be changed as I understand it.  I added the applicationintent = readonly and changed the data source to the sql listener.  Is that all that is required to change on the connection string to automatically point incoming connections to the secondary node given my AG setup above?


texts: Single DB shared across multiple servers

texts:
I've planned to host many servers (50+) each will have more than 20K users stay connected. There is lot of shared data amongst users (e.g. a user needs to refer data prepared by another user).

If I host single database server (preferably Microsoft SQL server), all the (50*20000) queries will be queued to that single server which could be major performance bottleneck. Is it possible to have multiple servers sharing single database. So that it will enable me to distribute my queries across multiple database servers. If this is not possible, what are the options available?

Update: This is completely new game application being developed and I want the design to be highly scalable. As each user's score changes (increases or decreases by certain number) it will be updated to the server. In turn Server will reply with "list of nearest 50 users" to that score. This is planned to happen in not less than 1 minute per user.

Thus if DB table is indexed on score, we can say single write and 50 reads will happen per minute by each user. Now if 20K users are connected to each "connection server" and all those "connection servers" in turn querying single DB Server, it is effectively queuing all the million requests to single DB Server. Which obviously cannot complete all those requests in a minute.

Hence, I was looking if we can distribute those queries across multiple SQL servers which share single database.


texts: Remove String from column based on character

texts:
i have table :

table : mytable

id  name
1   abcd ( dljdf
2   kjlkjkldjf ( ( ( ;dfld )

I want to remove string starting from character '('.

Result would look like

id  name
1   abcd
2   kjlkjkldjf

I tried using Trim Function but it did not give me required result

Any help plz.


texts: Full and differential backups are taking way longer only from one server

texts:
I see a perplexing issue on one quasi-production machine (SQL Server 2008 SP2 standard edition) only. Full and differential backup job all of a sudden started taking 48 hours since 2/4/2017. Full backup usually finishes within an hour and differential within minutes. There are four other servers backing up to same share and backup succeeded as usual. They are all virtual machines running on VMWARE and share same underlying SAN.

Sysadmin doesn't see any issues with SAN as well as backup share. I don't see any blocking. Replication from that machine is running fine. Transaction log backup job is running fine as most databases have simple recovery model. I can ping to backup share from machine which doesn't timeout.

I use Ola's maintenance solution. Cleanup jobs ran successfully as well. msdb database size is under half GB. Please let me know if I am missing an elephant in the room.

Here are waits on server. Full backup and differential backup jobs (different databases) are still in progress. I took full backup of a small database (60MB) locally and it finished in no time. Thank you.


texts:
I am struggeling with the following issue.

I have a table executives and supervisor, quite similar in the structure.
Both of the executives can have a Car one or more. Should I make a table executive_has_cars or executive_cars + supervisor_cars, or a table cars with fields ID,Person_ID,Type{1/2 - executive/supervisor}.

Or is there an other possible option. I need it to be scalable and fast, so would be good to have the best solution on the schema. And where should I put the indexes.

Thanks for tips


texts: Is it possible to have 1 slave server with multiple master?

texts:
Here is my set of master server:
DbServer1 - 10.1.0.1
DbServer2 - 10.1.0.2
DbServer3 - 10.1.0.3
DbServer4 - 10.1.0.4
and my replica server would be on 10.1.0.5
Is this doable if I create 4 instances of MySQL on a single server with different ports? But keeping in mind that all of my Master are using the default port which is 3306


texts: MySQL - How do you enter a record with only whitespace " "?

texts:
I want to enter the record with white-space as a record in MySQL. I had tried to enter it through shell and phpmyadmin, the record enters but when I try to retrieve the record it returns as null. I had also set the column null = no even then returns nothing.

code is below through shell and php:

insert into first_replace(to_replace, with_replace) values(".", " ");


texts: Are there any database engines which will intuit the join condition based on the existing foreign keys?

texts: It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the same clauses. Are there any databases, perhaps some kind of research project, that will inspect the existing foreign keys?


texts:
I need to aggregate all sales by days to build graph.

I have such a table

CREATE TABLE `transaction` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` varchar(255) NOT NULL,
  `purchase_date` int(10) unsigned NOT NULL DEFAULT '0',
  `expires_date` int(10) unsigned NOT NULL DEFAULT '0',
  `expires_date_calculated` int(10) unsigned NOT NULL DEFAULT '0',
  `payment_account_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_account_id` (`payment_account_id`),
  KEY `expires_date` (`expires_date`)
);

with the following data

INSERT INTO transaction
 (product_id, purchase_date, expires_date, expires_date_calculated, payment_account_id)
VALUES
 ('a', 1164357246, 0, 1264357246, 2),
 ('a', 1345117819, 0, 1435413246, 1),
 ('b', 1345117907, 0, 1440683646, 2),
 ('c', 1345119400, 1464357246, 0, 5),
 ('b', 1345119625, 0, 1464357246, 10),
 ('a', 1345120085, 0, 1464357246, 2),
 ('b', 1345121092, 0, 1464357246, 5),
 ('a', 1464357246, 0, 1564357246, 2) ;

And I have a function which triggers the following query 32 times with different date values

SELECT COUNT(DISTINCT payment_account_id) AS cnt
FROM transaction
WHERE (purchase_date < 1345120085
       AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085))
  AND (
    `product_id` IN
    (
      'a',
      'b'
    )
  );

And it works very slow. I want to replace it by one big query with 32 SUM(IF())

SELECT
  sum(IF((purchase_date < 1345120085
          AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085)),
        1, 0)) AS day1
#...
FROM (
       SELECT
         payment_account_id,
         (purchase_date)           AS purchase_date,
         (expires_date)            AS expires_date,
         (expires_date_calculated) AS expires_date_calculated
       FROM transaction
       WHERE
         `product_id` IN
         (
           'a',
           'b'
         )
         AND payment_account_id IS NOT NULL
        GROUP BY payment_account_id
     ) AS tmp;

SQL Fiddle with example

But I get wrong result. What I am doing wrong?


texts: How can I tell what recovery model my SQL database has?

texts: Is there a SQL command I can run to determine the recovery model of my database?  I want to know if its full recovery or not.


texts: Making a field greater than the limit unique

texts:
Firstly, just a quick note. I have searched for an answer but I can only people asking why rather than how to overcome.

I am currently designing a set of database tables for a project I am working on. A couple of the tables such as the following (simplified table):

CREATE TABLE members (
  id int(11) NOT NULL AUTO_INCREMENT
  email varchar(255) NOT NULL
  PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

I would like to ensure that the email remains unique however I am unable to use the unique constraint due to the length of the field and the fact I am using the utf8mb4 character set (trying to do so results in an error sch as "Specified key was too long; max key length is 767 bytes").

To overcome this I appear to have the following choices:

Modify the character set to 1 that will store the data in with a smaller footprint
Reduce the length of the field so it fits within the limit
Split the email into 2 sections (username, domain) and use these as unique values
Check for uniqueness in the code utilizing the database
Create a hash of the email field and store this in another field. Use this for unique constraint
Use a before insert and before update trigger to enforce the uniqueness

I am not overly keen on options 1 (or am I simply, in this case, using the wrong character set?), 2 (even this field is actually too short for a valid email address according to the RFC) and 3 (the domain would still be too long if following the rules of the RFC's) as I do not believe the structure of the database should be compromised to satisfy a simple constraint.

4 relies on the code to ensure the database is conforming. I am not overly keen on this as it can cause the data stored to lose integrity is someone forgets to check.

5 would require extraneous data to be stored in the database. This could cause collisions but dependent upon the hashing algorithm it may not be a problem.

6 would be implemented something like:

DELIMITER $$

CREATE
DEFINER = 'user'@'localhost'
TRIGGER MemberBeforeInsertTrigger
BEFORE INSERT
ON members
FOR EACH ROW
BEGIN
  DECLARE userCount int;

  SELECT
    COUNT(email) INTO userCount
  FROM members
  WHERE email = new.email;

  -- Ensure Email is not in use.
  IF userCount > 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email address already in use';
  END IF;

END
$$

DELIMITER ;

Would using a trigger such as this cause any negative affects in the performance of the database (compared with above options) or am I simply overthinking this?


texts:
I'm currently learning about object-relational databases and decided to whip up an example in PostgreSQL to test what I'm learning. Here is the example I wrote up for testing:

Create Type item_type As (name text, weight integer);

Create Type inventory_type As (item item_type, stock integer);

Create Table item Of item_type;

Create Table inventory Of inventory_type;

Insert Into item Values
('Block', 10),
('Ball', 2);

Insert Into inventory (item, stock)
Select item, 1 From item;

I then run Update item Set weight = 8 Where name = 'Block'; and the value correctly updates in the item table,  but I don't see the changes in the inventory table. I'm guessing that's because the insert into inventory is creating copies of the rows and inserting them into the inventory table.

Is there a way I can insert a reference to the object into the table? If so, is there any documentation on how to work with said reference? I've read about OIDS and that I can add them to the table using the WITH (OIDS) but it seems like I could achieve the same thing with a serial attribute, but that doesn't strike me as very object-relational.


texts: Pattern for copying hierarchical data from one table to another?

texts:
Imagine a table with three columns: an ID primary key, a nullable ParentID referencing the ID column on that same table, and a TreeID to indicating the records that make up a particular tree. A basic hierarchical structure might look like this. Note that the ordinal values don't necessarily have an effect on a record's position in the hierarchy.

      dbo.TreeNodes             Visual representation:
+----+----------+--------+    --------------------------
| ID | ParentID | TreeID |       Tree 7:     Tree 15:
+----+----------+--------+       1           10
|  1 | NULL     |      7 |       |–3         |–9
|  2 | 4        |      7 |       |–4
|  3 | 1        |      7 |       |–|–2
|  4 | 1        |      7 |       |-5
|  5 | 1        |      7 |
|  9 | 10       |     15 |
| 10 | NULL     |     15 |
+----+----------+--------+

Given another unrelated table with an identical definition (dbo.TreeNodes2, let's say), I've found that copying the data from the first table to the second while maintaining its self-referential hierarchy isn't so straightforward. I get stuck after inserting the new dbo.TreeNodes2 records when it comes time to populate the ParentID values. I would have to have a mapping table of some kind to know which records from the first table map to the new records in the second table. Here's what I've got so far:

DECLARE @TreeID INT = 7;
-- hard code arbitrary TreeID value for the 2nd table for the sake of demo
DECLARE @NewTreeID INT = 42;

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID INTO #InsertedRows
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

I could add a non-related OldID column to dbo.TreeNodes2 to copy the original IDs to, which I could then use to create a mapping to join on and populate the new ParentID records, then null out all the OldID values when I'm done, but this seems like bad design. If SQL Server allowed the OUTPUT clause of an INSERT to include columns from a join, then this would be easy, but all of the documentation, as well as my own testing, indicates that only Inserted.* columns, Deleted.* columns, and scalar values/literals can be used in the OUTPUT clause. Nothing from joins.

My pipe dream, non-valid T-SQL that does what I want would look something like this:

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY,
    OldID INT NOT NULL
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID, tn1.ID INTO #InsertedRows (ID, OldID)
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

From here, I would have a mapping table I can use to populate the ParentID values in the new table.

Is there a pattern for handing these kinds of cross-table hierarchical inserts without relying on an extra column whose only purpose is to allow the migration, after which it's an irrelevant column?


texts:
I'm interested in understanding how DBAs think about the balance between Oracle's strict licensing requirements and the growing compute capacity of today's machines.  Oracle's insistence that customers must license all physical cores on a box for Oracle EE if any of Oracle EE is installed on the box, seems to be, at first blush, at odds with the fact that with larger, more powerful machines, customers can do more (beyond just Oracle) on those boxes (e.g. with virtualization, etc.).

Ultimately, I'm interested in assessing how much license over provisioning typically occurs for most customers.  Here's an example: If I am a large customer running Oracle EE on a machine with two high frequency Intel Xeon E5-2670 (Sandy Bridge) Processors and 20 physical cores, would I typically be using 100% of those cores 100% of the time to run Oracle DBs (in which case there is no overprovisioning, since the whole box utilization is 100%) or would a more likely scenario be running 50% of those cores 50% of the time for Oracle DBs (in which case the whole box utilization is 25%, so I've effectively paid for 3x more licenses than i actually need.

Would appreciate any insights on the typical utilization rates that you see on the servers that run your Oracle DBs, and whether you think this notion of overprovisioning is a real issue that you would avoid if you could (say by buying DB capacity on-demand).


texts: Questions to Continuous Archiving and Point-in-Time Recovery with PostgreSQL

texts:
I'm planning my database backup strategy and came across the continuous archiving concept of PostgreSQL which is exactly what I'm looking for.

I have two questions on this.

I'm planning to mount the WAL file archive directory to an external NFS storage space (e.g. /mnt/nfs/archivedir/). I read that this log shipping is asynchronous by default. Does that mean that I don't have any performance issues on this?

As I understand it right, I must not loose any of the WAL segment files, right? But what if any file is corrupt? Can I recover the database ever again then? I guess that a lot of things can go wrong, because what happens when the server is suddenly turned off, or what if the NFS is suddenly not reachable, and so on.


texts: Redo log size results into performance degrade

texts:
We are continuously doing heavy insert and update query, but after 10 hours data base is returning answer late in minutes normally it takes around 1 ms. I observed that "free buffer wait" and "redo log check point complete" is taking most of the time.

Database have redolog size of 512 MB and 4 group defined and each have four files. So do we need to add more size or DBWR or LGWR thread has throughput issue (How I can identify), or hard limittation.

Thanks


texts: Error on Check Contraint that is on a Column with a Default Constraint

texts:
Inspired by this article, I wanted to test the idea before I considered implementing it.

However, I get this error

  Msg 547, Level 16, State 0, Line 14 The INSERT statement conflicted
  with the CHECK constraint "CK__DateCreated2". The conflict occurred in
  database "db_name", table "dbo.t2", column 'DateCreated'. The
  statement has been terminated.

OK, i know what the error means but how do I get around it.

Here's my sample data with DDL

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
     Id             INT IDENTITY
    ,Col1           NVARCHAR (15)
    ,DateCreated    DATETIME2 CONSTRAINT DEF__DateCreated2 DEFAULT (GETUTCDATE())
);
ALTER TABLE t2 WITH CHECK ADD CONSTRAINT CK__DateCreated2 CHECK ( DateCreated = GETUTCDATE()) ;

This INSERT results in the error

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1,DateCreated)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number), GETUTCDATE()
FROM Numbers M

so does this

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number) FROM Numbers M

and this

DECLARE @UTCNow DATETIME2 = GETUTCDATE() ;

;WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N1(N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N3 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N4 (N)
)
INSERT INTO dbo.t2
(Col1,DateCreated)
SELECT 'Col' + CONVERT(NVARCHAR(50), M.Number), @UTCNow
FROM Numbers M


texts: SQL Server - SQL script to restore database backup file into a new database

texts:
I am trying to create a SQL script to restore a database backup file into a new database in SQL Server.

I can do it manually without problems:

Right click on Databases  =>  Restore Database
Tick on Device
[General] Browse to the location where the BAK files is located and add it (click ok)
        Destination => Database => Type in name of new database
[Files] Change the names of the mdf and ldf files in 'Restore As'
[Options] Un-tick Tail-Log backup

In order to do this with a SQL script I am trying with the following:

RESTORE DATABASE new_db
FROM DISK = 'E:\test\test.BAK' WITH
MOVE 'E:\MSSQLSERVER\DATA\old_db.mdf' TO 'E:\test\new_db.mdf',
MOVE 'E:\MSSQLSERVER\LOG\old_db_log.ldf' TO 'E:\test\new_db.ldf'

I get the following error:

Logical file 'E:\MSSQLSERVER\DATA\old_db.mdf' is not part of database 'new_db'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

If I run this:

RESTORE FILELISTONLY
FROM DISK = 'E:\test\test.BAK'

...and then I run the RESTORE script above, I get the same error.


texts: Why the owner of a database can't alter the tables of this database that are owned by other owners?

texts:
For now I don't understand why this access rights segregation exist.
Is it implemented for some security reasons? Or can it be needed by some use cases I can't imagine right now?

Knowing almost nothing about the reasons behind concrete PostgreSQL design I wonder why prevent a database owner from altering those tables of the database owned that are owned by other owners.

Here's what the official documentation on altering tables says regarding who can alter tables of a database:

  You must own the table to use ALTER TABLE.

Of course I don't argue the design or the documentation. I'm just wondering why it can be needed to limit a database owner that way?


texts: Why/when does creating a mysql trigger require SUPER privileges?

texts:
I'm attempting to parse and understand what the MySQL doc says about when SUPER privileges are required for creating triggers. They say:

  If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege[1]

This sort of implies that there are circumstances where it does not require the SUPER privilege. Are they referring to the log_bin_trust_function_creators=1 setting, or is there something else?

Secondly, in [2] they say:

  ... trusted not to create stored functions that will cause unsafe events to be written to the binary log.

Just what exactly is the danger here? What kinds of events might be "unsafe" in the binary log, and to whom?

[1] https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

[2] https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_log_bin_trust_function_creators


texts: Why do PostgreSQL arrays have different starting indices for 1D and 2D arrays?

texts:
I'm working with point data in the form (p1, p2), which is represented in text as ((x, y), (x, y)). I would like to get 4 values out of this string (x1, x2, y1, y2), But I'm having trouble working with array indices.

For example:

(1) Get a point[] representing the 2 points

select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[];

-- This correctly gives me {(64.0,38.0),(-26.0,-40.0)}

(2) Get the first point from that array

select ( select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[] )[1]

-- this gives me (64.0,38.0) (i.e. index = 1 gives me the FIRST point)

(3) Get the x value of the first point

select ( select ( select concat('{', replace(replace('(64,38),(-26,-40)', '(', '"('), ')', ')"'), '}')::point[] )[1] )[0]

-- This gives me 64 (i.e. index = 0 gives me the FIRST point)

It seems that in a 2D array, indices start at 1. But in a 1D array, indices start at 0. How can this be????

I've tested this on Dbeaver and separately on PG Admin 4 and I get the same result.


texts: MariaDB Galera setup with drastically different amounts of memory used on cluster nodes - why?

texts:
We have a 3 node MariaDB Galera cluster.

All nodes are identical and the config on each is identical. They have 64GB of RAM each.

The output of the following query is 28:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Our database is growing quite quickly and the machines are dedicated so innodb_buffer_pool_size is now set to 56G. Originally it was set to 256M, we just increased it this morning (the cluster was only set up a couple of days ago).

My question is why is the amount of RAM used on each machine so drasticly different, see the graph below. When we changed innodb_buffer_pool_size to 56G (and did a rolling-restart) they all jumped up by around the same amount (in real terms, not proportionally), but as you can see are clearly still quite different. Can anybody shed some light on why?

Thanks!


texts: PostgreSQL sometimes fails to detect serialization failures

texts:
I have an http-server in application language that looks roughly like this:
item = SELECT item FROM table WHERE field = 'value'
if (item) {
  UPDATE another field on item
} else {
  INSERT item VALUE field = 'value' and so on
}

The field has unique constraint. About once every several minutes two requests with the same value come in at the same time. They both execute this same code so of course there is a race condition. I decided that if I wrap it in a transaction with SERIALIZABLE isolation level and retry on every serialization failure then the problem is solved. And on localhost it worked fine. I indeed see serialization failures thrown with error code 40001 as expected.
However on production in a database under load for some reason a Unique constraint violation is sporadically thrown with a code of 23505. It only happens sometimes. I put a lot of effort to  reproduce it on localhost but failed. Everytime I get a normal 40001. I tried to run server code in separate processes and to put delays at different points to enforce certain orders of execution. It only happens on production. So I added extensive logging to prod. The logs say that every time when a problem occurs the item is not found at first but when INSERT happens it triggers Unique constraint violation. So it looks just ad a race condition.
But why sometimes under some conditions Postgres SERIALIZABLE transactions don't detect it as they should? Or how to debug it? It happens like several times in a day out of several dozens of cases.
The (almost) exact SQL look like this:
(I omitted detailed fields lists and exact parameters)
START TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM products
WHERE product_code = $1
LIMIT 1 -- product_code is a parameter

SELECT *
FROM licenses
WHERE license_key = $1
LIMIT 1 -- license_key is another and it's unique across the table
-- At this point SELECT did not return anything but it was inserted before this INSERT by exactly the same transaction

INSERT INTO licenses ("license_key", "product_id", "other_data",...)
VALUES ($1, $2, $3,...) RETURNING "id"
query failed: error: duplicate key value violates unique constraint "licenses_license_key_key"


texts:
I'm trying to design database relations for archaeological places in a country. There are around 9000 places registered. An archaeological place can have multiple types. Types will be like Cave, Mound, Necropolis...There are around 100 different types. For example an archaeological place can be a necropolis and a mound at the same time. For different types, different attributes will be valid. For example, a mound type will have attributes such as diameter, direction etc...Another type, for example, a cave will have attributes such as depth etc...
Common attributes for archaeological places are id, name and location. I thought about creating a table with these common attributes and link to other tables. Each table linked to will hold information specific to a certain type.
The problem is there will be multiple one-to-one relations.
A particular id, name or location entry may point to tables type1, type2 etc.. but it cannot point to two or more different entries in the same table. Number of different tables an entry points to differs. It can point to one or 10 or more. So it is not balanced. If I create 100 one-to-one relations and most of them will be empty for most of the entries.
Can there be a better design for representing such data? Better in terms of compactness... I can simply create a huge table with all the attributes as columns but some of the attributes will simply mean nothing for some types. Are there different ways to structure multiple one-to-one relations?
Edit:
Here is an example:
Lets say that there are 3 types

A, B, C

Attributes for these types are as follows:

A: x, y, z
B: z, k
C: x, m

A sample table will look like as follows:
Entry   Location    Type    x   y   z   k   m
entry1  location1   A       o   o   o
entry2  location1   B               o   o
entry3  location2   B               o   o
entry4  location4   C       o               o

"o" represents data.
As you can see, entry1 and entry2 share the same location but their type is different.
I started with creating a table for locations(plus names which are unique to locations). Then I will have seperate tables for each type. Entries in location table will have one-to-one connections to different type tables. I couldn't find a better way to organize this.
At the end, I will create an app which shows archaeological places nearby. Users will be able to select and filter certain types.


texts:
Maybe I'll try my luck here. Server Fault site was quite hostile for non-professional server user.

I recently bought HP ProLiant ML310e Gen8 v2 server with 8G's RAM and 4 HP 1TB 6G 7.2k rpm SATA (3.5in) Non-Hot Plug MDL HDD disks.

It was surprise to me that HP didn't provide debian drivers for raid controller and I had to go with software raid. I installed Debian with it's own software raid and raid level 10.

After installation I am experiencing huge performance loss and it looks like I managed to track it to MySQL operations.

Server is used mainly as Apache2 server with MySQL database.

Here is simple script that I ran to measure SQL operation times with server and my desktop PC.

$sql = "SELECT id FROM c_event";
$events = $db->getall($sql);
foreach($events as $item)
    $db->query("INSERT table1 set id = {$item['id']}");
foreach($events as $item)
    $db->query("DELETE FROM table1 WHERE id = {$item['id']}");

I compared results with my desktop PC with Intel Q6600, 4GB of ram and 120G SSD disk.

desktop completed in 45 secs
server in 2 hours, 46 mins and 18 secs.

Difference is totally insane.

Server raid performance seems ok to me, as disks ain't top quality ones.

hdparm -t /dev/md1
/dev/md1:
 Timing buffered disk reads: 1024 MB in  3.00 seconds = 341.04 MB/sec

I was wondering if there was something misconfigured in my server or is it totally just lack of disk performance.

For comparsion, I had possibility to run same test on ML350p Gen8 E5-2609 2.4Ghz server with 4 HP 146GB 6G SAS 15K 2.5in SC ENT HDD disks in raid 10 mode. Test took about 10 seconds.


texts: mySQL trigger - error when declaring variable

texts:
I am using phpMyAdmin and I am trying to declare a variable but it throws an error.

Using the following code, it works just fine:

IF EXISTS(SELECT * FROM mytable WHERE new.val=old.val) THEN
    SET new.val = 0;
END IF

But when I try to declare a variable, it won't work. For example:

DECLARE d INT;
SET d=new.val;
IF EXISTS(SELECT * FROM mytable WHERE old.val=d) THEN
    SET new.val = 0;
END IF

I also tried @d instead of d.

Any ideas?


texts:
After running a fairly hefty query, the execution plan gave me a missing index suggestion which was of the form:

(Timestamp) INCLUDE (CustomerID, EventID, ID, EmployeeID)

Which seems to be a covering index (the INCLUDE column are all either primary keys (ID) or foreign keys). However, my querys WHERE clause is filtering by Timestamp, CustomerID, and EventID. I don't know why these weren't included in the main part of the index.

So my question is, is there any difference in using the suggested index above, or what I think is a better alternative;

(Timestamp, CustomerID, EventID) INCLUDE (ID, EmployeeID)

My understanding is that this will still allow Timestamp-only index seeking, but will also further assist my query by having the customer and event IDs (which are filtered) in the main part.

I think this was something to do with the width of the 'main' part - FYI, Timestamp is a datetime2(0), CustomerID is an int, and EventID is a byte.

I am testing this myself at the moment, but this is a HUGE table - over 1,000,000,000 rows - and it is taking time to compare the indexes. That, and I'd like to learn more about this.

Thanks.


texts: How to identify file for "The system cannot find the file specified." during a pg_ctl -D start?

texts:
I'm getting one message during pg_ctl -D start of the standby server. Perhaps more of a general troubleshooting question.

This is what I'm seeing when I do a pg_ctl -D start:

D:\>pg_ctl -D d:\dev_data_standby start
server starting
D:\>2016-03-21 08:41:12 EDT   1016 LOG:  loaded library "auto_explain"
        1 file(s) copied.
The system cannot find the file specified.

The log file shows:

2016-03-21 08:41:12 EDT   3564 LOG:  database system was interrupted; last known up at 2016-03-21 08:38:08 EDT
2016-03-21 08:41:16 EDT   3564 LOG:  entering standby mode
2016-03-21 08:41:16 EDT   3564 LOG:  restored log file "0000000100000002000000F2" from archive
2016-03-21 08:41:16 EDT   3564 LOG:  redo starts at 2/F2000020
2016-03-21 08:41:16 EDT   3564 LOG:  consistent recovery state reached at 2/F20000E0
2016-03-21 08:41:16 EDT   3816 LOG:  streaming replication successfully connected to primary

How can I troubleshoot that error:  The system cannot find the file specified.?


texts: Should Partitioned Tables Be Set to Escalation Lock of AUTO By Default

texts:
We're partitioning a handful of tables. We're hoping better concurrency will be part of the result from this change. However, even though setting the table level lock escalation to AUTO (partitions) for these tables should enhance that effect, is it something we should automatically try first?

From MSDN:
"HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better."

Since it indicates "in rare instances", can I take from that, that we should apply partition level lock escalation to these tables from the get-go until we see adverse effects suggesting that TABLE level locks would be better? I will say, that we already know that most of our existing queries and transactions should be coded to almost always use the partition keys.


texts: MySQL Workbench not showing stored procedures that I have created

texts:
I have a simple stored procedure that I have just created.

I have applied this script to my database

This script shows up in the my list of stored procedure

However, when I restart MySQL workbench, its not in the list anymore

Interestingly, the procedure still exists even though it is not shown.

I can call

CALL LoadBatch ('057753') ;

And get results

Whats going on, how can I see what stored procedure I have created?

I have tried clicking "Refresh All"

EDIT

As Vérace has suggested

I have tried calling the LoadBatch command from the commande line

This seems to be unresponsive to my stored procedure.

EDIT

After adding the ; character

I think this stored procedure call worked ok from the command line

Here is the warning

EDIT

I think this error message is important


texts: Reporting services existing in SQL Cluster instance but unable to see on SSMS?

texts: This is first time I am working on SQL Failover Cluster environment and I installed Reporting services on SQL server Failover Cluster Instance both Active Nodes(Node1 & Node2) and configured through Reporting services configuration manager, when I was opened SSMS I haven't find any Reportserver and ReportserverTempDB Databases. Which means did I miss something in installation? or we have to configure those separately? When I was trying to add Reporting services feature I got an error like below.


texts:
Would it be feasible to use UNIX timestamp as a primary key to store log records/analytics data of users?
Will there be inconsistencies if a RDBMS is used to store log data?


texts: SQL Server Report how to create multiple groups with hidden details

texts:
I have created a report that has multiple rows returned from one query.

I have a chart that is created for all the row and I have it grouped on the server location.  the group properties of the details group i have visibility set to hidden and set to display when toggled.

I want to do this for another column so there is another nested group that can be toggled.

When I try to add a child group under the details group it tells me cannot group inside detail.

Is it not possible to add another group that can be toggled to the report?


texts: Where should a default setting field be?

texts: I am creating a database to track occurrences of incidents on buses based on an existing system. Currently the drivers can set a default bus number, which is used to pre-populate the bus number on the incident form. Where they can change that then, if they are driving another bus. This is a selection made by the driver. Not a bus assigned to them, per say. I was going to put this field in the staff table, not the bus table. However, someone said I should not, that I should put it in the bus table. I guess it would technically work either way, but I don't feel like the field belongs to the bus - it belongs to the staff member. Am I overthinking this?


texts: Manual failover of AAG in SQL Server 2012

texts: Just a question regarding manual failovers.  Does it matter if we use SSMS to perform the failover vs going to the Failover Cluster Manager and move the role to the secondary node?  Is there a preferred method or either one is fine to do?


texts:
I have a small app which calls a stored procedure over ODBC using a sysadmin user with login and password.

The problem is that some (Windows Domain) users get an error saying execute permissions were denied on the stored procedure, whilst other users do not get any such error. However, both use the same app which uses the same login and password. The app does not use Trusted Connection or Integrated Security at all, only login and password from an ini file that stores encrypted credentials.

I'm at a loss. I have run SQL Server Profiler whilst the program is being used to confirm the user executing the procedure is as expected, I have tried setting permissions up for Windows Domain users in the Database... I have no idea why it would work for some and not for others when none of it was dependent on windows user permissions. Not only that, but it was working fine for everyone last week.

Is there anywhere I can look, Or tests I can do, that might help me diagnose the problem?

EDIT: Unfortunately I don't have access to the source code.


texts:
I've created sereral SSIS packages in Visual Studio 2012 and they can be executed without any problems.

For a project I'm working on I generate packages using BIML. In my BIML script I create a package for each databaseview in two defines databaseschemas. For example:

[crm].[contact]
[crm].[account]
[navision].[vendor]
[navision].[customer]

The name of the packages will be [schemaname] viewname, e.a. "[crm] contact".

When I start a package in Visual Studio I get the message "Failed to start project" see below.

"Exception deserializing the package. "Specified part does not exist in the package".

Why is this error occurring?


texts: Doing two counts on a single table in a single query

texts:
There are some similar questions/answers on the forums but I think my problem is simpler.

I have two quesries, eg

SELECT count(*) FROM agent;
SELECT count(*) FROM agent WHERE active = 't';

I would like the output to be on a single row.  For bonus points it would be nice to scan through the table only once and update both counters, to get a result like this

active_agents | total_agents
--------------+--------------
    10        |     20

So I guess I have two questions:
What is the Neatest/clearest way to do this, and
What is the fastest way to do it for very large tables.


texts: Why doesn't DELETE + REORG free diskspace (DB2)?

texts:
In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here?

The table resides in its own tablespace which I created as follows:

CREATE BUFFERPOOL "MY_BP" SIZE 250 AUTOMATIC PAGESIZE 4096;
CREATE LARGE TABLESPACE MY_TBS IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 BUFFERPOOL MY_BP OVERHEAD 10.500000 TRANSFERRATE 0.140000 FILE SYSTEM CACHING;

I deleted/reorged as follows:

DELETE FROM MY_TBL
RUNSTATS ON TABLE MY_TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL
REORG TABLE MY_TBL
RUNSTATS ON TABLE MY_TABLE WITH DISTRIBUTION AND DETAILED INDEXES ALL
ALTER TABLESPACE MY_TBS REDUCE

The table MY_TBL took up 2.5GB before all that and after deleting/reorging it uses only 3 MB less.

FWIW: I'm running DB2/NT v9.5.2.


texts:
I have table Like This

 ID | City     | Prize
_______________________
  1 | Surabaya | Notebook
  2 | Surabaya | Notebook
  3 | Surabaya |
  4 | Bandung  | NoteBook
  5 | Bandung  | PC
  6 | Bandung  | Handphone
  7 | Bandung  |

I want group my table by City, Prize, and Qty Like This :

 ID | City     | Prize    | QTy
______________________________
  1 | Surabaya | Notebook  | 2
  2 | Bandung  | Handphone | 1
  3 | Bandung  | NoteBook  | 1
  4 | Bandung  | PC        | 1

I try This Query but Not Perfect this My query

SELECT City, prize, count( prize ) AS qty
FROM `my_table`
GROUP BY hadiah

Can anyone Help me for fix this Problem?

Im very Appreciated your answer.

Thanks

I Have Try query From JoeTaras

SELECT Kota, hadiah, count( hadiah ) AS qty
FROM unik
WHERE hadiah IS NOT NULL
GROUP BY kota, hadiah

And the result is still not true, The Null Value is Count too


texts: Data Warehouse attribute that describes its timeframe?

texts:
I am writing a document about a Data Warehouse system, and would like to know if is there a single word (noun) that describes the DW attribute of how many time it contains?

I mean, if a DW contains 5 years, I want a noun for it, for instance: the XXX of DW is 5 years.

Likewise we call how fresh the data is as its Latency, and how often the data is updated as Periodicity.


texts:
I've got a cluster of 9.1 servers running streaming replication and using WAL-E based log shipping for when that falls behind.

Generally speaking it's a pretty solid setup and does not fall behind, however we appear to have one slave in the cluster that consistently falls behind when replicating in the wee hours of the morning. It always catches up again shortly after but I have not yet been able understand why it does this.

There does not appear to be consistency with regards to the timing apart from it happens when our application is not handling a lot of load. Is it possible that I'm seeing this occur because of lack of data to be replicated?

Here's what the relevant portions of the postgresql.conf and recovery.conf look like

postgresql.conf

archive_mode = on
archive_command = '. /mnt/data/postgresql/.profile && wal-e wal-push %p'

recovery.conf

recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=master_db_ip_address port=5432 user=postgres'
restore_command = '. /mnt/data/postgresql/.profile && wal-e wal-fetch "%f" "%p"'

To add a little more context, you can see the replication delay for this server is creeping up as time goes on.

This should not occur. The streaming replication delay should be < 1-2s most of the time (based on past experience with this cluster)
There are no scheduled jobs running on the db cluster at the time these spikes in the replication timing occur.

What are some good strategies for debugging this kind of issue?

EDIT

I had it suggested to me that my methodology for computing the replication delay may be incorrect, here's what I'm using:

SELECT EXTRACT(EPOCH FROM NOW() - pg_last_xact_replay_timestamp())


texts: How to make sqlplus prompt longer than 50 chars?

texts:
I've set a prompt in my sqlplus glogin.sql a little bit long, mainly because of some internal variables and control sequence. Starting sqlplus I get the following error:

String beginning "truncated_prompt..." is too long. Maximum size is 50 characters.
SQL>

...and yes, my prompt is really truncated to 50 characters.

What to do? Is it possible to somehow make this limit higher? (I already tried SET LONG and SET LONGC without any change.)

(Reacting comment)
I have this long prompt, because I have some control sequences in it, which make it so beautiful. And I am calling it through rlwrapper, which makes the normal keys (page up/down, history search, tab completion, etc) also working, just as in a good command line application.


texts: Nest SUM() Function Inside Case Statement?

texts:
I am trying to run this syntax, but I get an error of

  Msg 4145, Level 15, State 1, Line 9
  An expression of non-boolean type specified in a context where a condition is expected, near 'SUM'.

And this is my syntax, just trying to conditionally execute a SUM() based off the criteria.

SELECT
    CASE WHEN
         SUM(case when raaa.EN = 'Yes' AND raaa.HR >= raaa.WT then raaa.WT end)
         SUM(case when raaa.EN = 'Yes' AND raaa.HR <= raaa.WT then raaa.HR end)
         SUM(case when raaa.EN = 'No' AND raaa.HR <= raaa.WT then raaa.HR end)
         SUM(case when raaa.EN = 'No' AND raaa.HR >= raaa.WT then raaa.WT end)
    END AS HR
FROM
        ranaboveaboardabout raaa;

EDIT
I know that this will work, just can't seem to get syntax correct for the above

Select
CASE WHEN
        SUM(raaa.field1) / SUM(CASE WHEN raaa.field2 < raaa.field1 THEN raaa.field2 ELSE raaa.field1 END) >= 16.23 THEN 20
        ELSE SUM(raaa.field1) / SUM(CASE WHEN raaa.field2 < raaa.field1 THEN raaa.field2 ELSE raaa.field1 END)
END AS nestedSUM
FROM
    ranaboveaboardabout raaa;


texts: Enabling TDE without breaking existing mirror

texts:
I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended state.

There are a few good articles here and here as well as many others, but they all demonstrate enabling TDE on a mirrored configuration with importing the database in the mirror before enabling encryption. I've not yet found a way to simply turn on TDE in an existing mirrored system.

I've gone so far as to export the service master key and service keys from the Principal and import them in the mirror. I've tried transact-sql'ing mirroring in the hopes that what I was seeing in the GUI was erroneous due to some advanced mirroring things going on, with no success.

Is enabling TDE across an existing mirrored SQL Server setup possible?


texts: Find current running queries which takes more CPU

texts: How to get the details about the current running queries which are utilizing more CPU.