texts: Insert into multi table View in SQL SERVER

texts:
I'm asking this question in manner to try to understand a similar situation I have, and to try to understand how insert to view is working.

I have these table:

Create table A([id] int primary key not null, value nvarchar(50) NULL)
Create table B([id] int primary key not null, value nvarchar(50) NULL)

And I create view from the two tables like this:

create View V as (select * from A) UNION ALL (select * from B)

And I have this trigger on the V view:

Create trigger v_trig on V instead of insert AS
  insert into v (id,value) select id,value from inserted

When I try to insert to my view I'm getting the following error:

  Msg 4436, Level 16, State 12, Procedure v_trig, Line 2

  UNION ALL view 'db.dbo.V' is not updatable because a partitioning
  column was not found

I have a DB with a similar view (with union all) and for some reason I can insert into it without any problem and I'm trying to understand why.

What should I do in manner to allow insert into such as view? Is there's a way to decide (without changing the trigger) which is the default table of the view in manner to insert into it?


texts: Troubleshoot Microsoft SQL Server Reporting Services unexpected error

texts:
I have an error implementing reports on reporting services from Visual Studio 2005 but I can do it from the Reports Manager

Microsoft SQL Server Reporting Services Versión 10.0.5500.0.

  The underlying connection was closed: An unexpected error reception.

How would I troubleshoot what the problem could be?


texts:
I'm researching the best way to do the following but haven't been able to come up with anything.  I'm trying to build a small (probably 3 or so tables with at most 200 records each) relational database that is hosted online.  If it could be hosted offline, I'd use Access and call it a day, but I can't figure out an equivalent that is online.

Requirements are:

accessible online via anywhere
ability to restrict viewing of certain information to certain users
once setup, is very easy to use

Preferable:

not a lot of coding - I'm not fluent in any server side coding and don't want to spend the time learning for this small of a project - e.g. Access can do a lot without coding
free though cheap is an option (since this is a small project, it shouldn't cost more than 5-10$ a month)

If this isn't possible, I'd be obliged if someone could direct me to the closest thing to it. Thanks.


texts: Dealing with Violation of PRIMARY KEY constraint SSIS

texts:
How do i deal with this error in SSIS. I'm trying to load a data into an archive table. The package failed and came up with the below error message. I know that there are conflicting keys in the destination table that are being brought over from the source.

How do I deal with this? Any help will be appreciated. The data is about 30M records.

  [OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_xxxxxx'. Cannot insert duplicate key in object 'dbo.mytable'.".


texts: Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row"

texts:
I'm currently making SQL that'll update data from 1 table, based on data from another table for my company

update table1
set column1 = (SELECT color
                FROM table2
                Where table1.id = table2.id)

My goal is to make table1's column 1 to consist of the colors from table 2, however the id is not unique, instead it's cvr of the companies, therefore they appear multiple times giving me the error "single-row subquery returns more than one row". The duplicates can have different colors however, making me unsure on how to tackle this, any suggestions?

Also it would seem that while duplicates can be differnet colors, in 99% of all instances they don't, so if the first isn't possible, would there be a way for me to update, so all duplicates have same color?


texts:
I run the following code to purge old data. Exact code was running as is  as SQL Agent job step pointing to a user database.   Expecting to commit with a batch of 10,000 rows.  2 days ago it did not do that, after terminating the session (reason of termination is not related to this question) rolled back all the delete and not just the last batch.

Anybody can point me to a reason why?

SET NOCOUNT ON;

DECLARE @rowCount INT;

SET @rowCount= 1;

WHILE @rowCount > 0
BEGIN
  BEGIN TRANSACTION;

  DELETE TOP (10000)
    dbo.TableName
    WHERE RecordedAt<DATEADD(month, -3, GETDATE() )
   SET @rowCount = @@ROWCOUNT;

  COMMIT TRANSACTION;

END


texts: Best practices in securing Oracle Standard edition one

texts:
I have a database server with Oracle Standard Edition One. I'm interested what are the best practices to secure Oracle? I have Java web application installed in the same server. The Java application uses Oracle to store data.

Best Wishes
Peter


texts: OCI KBHS-00719: Error 'unknown'; Archived object is not restored

texts:
I am encountering below error message from RMAN backup to OCI.

  Either the bucket named 'DB_BACKUP' does not exist in the namespace
  'UNIcredits' or you are not authorized to access it. KBHS-01404: See
  trace file
  D:\APP\ORAADMIN\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACE\sbtio_11000_11672.log
  for details KBHS-00719: Error 'unknown'; Archived object is not
  restored. KBHS-00700: HTTP response error 'Conflict'

RMAN backups are successful and have been validated and existing in the said bucket, the error is encountered on the crosscheck from sbt_tape. I have two changes done before the error has been encountered. I changed the policy setting in the bucket to archive files from the standard 30 days to 7 days and delete policy from 90 days to 10 days, it deleted most of my backup in cloud older than 10 days. I also changed my account password in OCI.

Oracle support advise to ignore it since backups are successful but its bugging me as per my housekeeping script shows its completed with errors.

Below is the script for RMAN backup

run {
allocate channel CHAN_FUL device type sbt PARMS='SBT_LIBRARY=D:\app\Oraadmin\product\12.2.0\dbhome_1\opc\bdohi\lib\oraopc.dll, SBT_PARMS=(OPC_PFILE=D:\app\Oraadmin\product\12.2.0\dbhome_1\opc\bdohi\bdohi_config.ora)';
configure retention policy to recovery window of 7 days;
configure controlfile autobackup on;
configure default device type to sbt_tape;
backup incremental level 0 section size 2G database ;
sql 'alter system archive log current';
backup archivelog all delete input;
}
delete noprompt expired backup;
list backup summary;
crosscheck backup;
exit;


texts:
How do I go about getting the course number (CNO) of all the lowest enrolled by department?
For example if math had coursenum 123 and the 0 enrolled, and anoher coursenum 555 with 6 enrolled, it would return cno = 123

QUERY

--get the course number of the lowest course in the department
select select c.cno
from course c,
     -- number of students per course
(
     select c.dname as tempDname, c.cno as tempCno, count(e.sid) as tempCount
     from course c
     left outer join enroll e
     on e.dname = c.dname and e.cno = c.cno
     group by c.dname, c.cno
) temp
where  c.dname = tempDname and c.cno =

At the moment I am stuck. The sub query at the moment returns the number of students in each class.

Sub query returns:


texts: Can I separate user and system trace files in Oracle 11g?

texts:
In Oracle 10g, the destinations for system and user generated trace files were controlled by the background_dump_dest and user_dump_dest Oracle parameters.

Starting in 11g these parameters are deprecated, and all trace files are written to the Diag Trace value in v$diag_info.

Is there any way to separate system and user trace files like you could in 10g?


texts: One-to-many dictionary (lookup) table vs varchar vs enum?

texts:
Imagine we have orders table, and an order has a status. Which of these three options is the best?

Use varchar for status column
Use enum for status column
Use separate status table, which has status_id int and name varchar, and in orders table keep status_id as a foreign key.

Which is the best approach? I suspect that using dictionary table is also better because it'd be faster to search statuses by int, instead of varchar.


texts: Set private key password for mysql using ssl

texts:
2016-05-09T20:05:05.772747Z 0 [ERROR] SSL error: Unable to get private key from 'server-key.pem'
2016-05-09T20:05:05.772764Z 0 [Warning] Failed to set up SSL because of the following SSL library error: Unable to get private key

My installation correctly locates the certificate files however it does not start SSL as it is unable to get the pass phrase for the private key.

How can I configure a passphrase in the mysql configuration?

I have tried removing the passphrase from the key but openssl on ubuntu 16.04 requires a minimum passphrase length of 4 characters.


texts: Listing SQL Server instance startup parameters

texts:
I have a server I have just restarted and verified which trace flag are active using DBCC TRACESTATUS:

  Trace Flag : 3688 Function: Removes messages to errorlog about traces
  started and stopped

Here you can see what each trace flag does.

Flag 3688

The start parameters are as follows:

Question:

How can I find what the startup parameters of the SQL Server services are, through T-SQL?


texts: Using group by with left join and IN yields empty result

texts:
I have two tables: invitees and invitee_information. invitees belong to events (third table event_id is a foreign key in invitees table).

I want to get the total count of invitees per event for which there is no information available yet (no record in the invitee_information table).

When I write the query using only one event_id in the where clause and anti-join pattern (IS NULL for invitee_id column to filter for rows having no information saved) the count is returned as expected for that given event_id:

SELECT COUNT(i.id)
FROM invitees i
LEFT JOIN invitee_information ii
ON ii.invitee_id = i.id
WHERE ii.invitee_id IS NULL AND i.`event_id` = 18571 AND i.`invitation_sent` = 1;

Now in order to optimize the query to get this count data at DB level for multiple event_ids instead of getting this in a loop for each event_id, I used IN and passed multiple event_ids and used GROUP_BY event_id.

Expected result should be (if there is no data saved in invitee_information table for event_id 18569 :

event_id | count(i.id)
18569    |          0

But query result is always empty. My updated query is:

SELECT i.`event_id`, COUNT(i.id)
FROM invitees i
LEFT JOIN invitee_information ii
ON i.id = ii.invitee_id
WHERE ii.invitee_id IS NULL AND i.`event_id` IN(18569,18571) AND i.`invitation_sent` = 1
GROUP BY i.`event_id`;


texts:
I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question:

I have 2 tables one file level & another batch level. In a day, only 1 file_id is possible; but for each file_id there are multiple batches with different batch_id and then again for each batch_id there are multiple documents.

Now, I need something like the simple getters & setters. I want some data from the file level table.. on the basis of that get some data from the batch level table and then get some more data from the files table again.

This was very easy if I had an object & I could simply pass it around & setting stuff & filling up the object with things I want.

How can I do something similar with PL/SQL like passing the same object again & again ??


texts: Using function is much slower than running query

texts:
I am trying to write a plpgsql function that runs a query where the WHERE part is variable using EXECUTE, like this :
CREATE FUNCTION test1(p_filter TEXT) RETURNS SETOF Bin AS $$
    DECLARE
        q TEXT;
    BEGIN
        q = FORMAT('SELECT * FROM Bin WHERE %s;', p_filter);
        RETURN QUERY EXECUTE q;
    END
$$ LANGUAGE plpgsql;

The function was running much slower than using the query directly so I tried to create a function where I would juste return the query like this :
CREATE FUNCTION test2() RETURNS SETOF Bin AS $$
    BEGIN
        RETURN QUERY SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
    END
$$ LANGUAGE plpgsql;

In both cases, using auto_explain I got this plan :
2021-01-12 11:18:14.524 CET [19356] postgres@astar LOG:  duration: 26.203 ms  plan:
    Query Text: SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
    Seq Scan on bin  (cost=0.00..3779.06 rows=1 width=181) (actual time=0.008..22.763 rows=72687 loops=1)
      Filter: (((binfrac).linedivisionnb = 0) AND ((binfrac).columndivisionnb = 0) AND (_weight = 0) AND ((binfrac).height = 64))
      Rows Removed by Filter: 14316
2021-01-12 11:18:14.524 CET [19356] postgres@astar CONTEXT:  PL/pgSQL function test1(text) line 6 at RETURN QUERY
2021-01-12 11:18:14.532 CET [19356] postgres@astar LOG:  duration: 45.242 ms  plan:
    Query Text: EXPLAIN ANALYZE SELECT * FROM test1('((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0)');
    Function Scan on test1  (cost=0.25..10.25 rows=1000 width=511) (actual time=36.904..43.560 rows=72687 loops=1)

When running the query directly or in an SQL STABLE function I only get the first part :
2021-01-12 11:25:30.976 CET [19356] postgres@astar LOG:  duration: 27.241 ms  plan:
    Query Text: EXPLAIN ANALYZE SELECT * FROM Bin WHERE ((bin.binFrac).linedivisionnb = 0 AND (bin.binFrac).columndivisionnb = 0 AND (bin.binFrac).height = 64 AND bin._weight = 0);
    Seq Scan on bin  (cost=0.00..3779.06 rows=1 width=181) (actual time=0.009..25.511 rows=72687 loops=1)
      Filter: (((binfrac).linedivisionnb = 0) AND ((binfrac).columndivisionnb = 0) AND (_weight = 0) AND ((binfrac).height = 64))
      Rows Removed by Filter: 14316

Where does the extra 20ms come from ? Is there a way to remove this overhead or at least mitigate it ?


texts:
PostgreSQL supports CREATE TABLE AS and SELECT INTO when do I use both?

  CREATE TABLE AS -- define a new table from the results of a query

  CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types associated with the output columns of the SELECT (except that you can override the column names by giving an explicit list of new column names).

  CREATE TABLE AS bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the query just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the query. In contrast, a view re-evaluates its defining SELECT statement whenever it is queried.

And, then.

  SELECT INTO -- define a new table from the results of a query

  SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.


texts:
i need to make Sp to distribute students to their sections the procedure take 2 string parameters StuID and SecID

in case I've send '1,2,3,4,5' as StuID and 'a,b' as SecID i'm using spliting function which well return tables

Tb1 |   Tb2
1   |    a
2   |    b
3   |
4   |
5   |

how can i get the following result

1 a
2 b
3 a
4 b
5 a
....

I've tried to do it via cross join but it did not show the result i want

select US.vItem as UserID,SE.vItem as Section
from split(@pUserID,',') us
cross join split(@pSectionID,',') se


texts:
In which order does a mongodump --query executes, is from last insert to first insert or the other way around, if it is other way around is there any option that I can pass to mongodump --query, so that mongodump queries start from first insert to last insert,

any ideas?


texts: Creating a Primary Key on Existing table

texts:
We have a partition table with 2 billion records and we need to assign a primary key to one of the columns for enabling change tracking feature on it.

After searching we found out that primary key can be assigned by using below query :-

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
    PRIMARY KEY CLUSTERED (TransactionID);
GO

But wanted to know if it is really possible to assign a PK to such huge table without dropping it and if yes , how much time will it consume in doing so?

Also is there any other option to enable Change tracking on this table as it does not consist a Primary key. Help on this query will be much appreciated.

Note:- This column does not contain duplicate values and is also set to NOT NULL. Also the existing column is set as identity (1,1), how do we convert the identity column to a Primary key?


texts: Boolean check on fields returning any of them except if all have same value

texts:
Assuming we have 4 boolean fields a,b,c,d

How can we return any of the fields that are true, but not nothing when all 4 are true?

where a=true or b=true or c=true or d=true

will return a row where even all 4 fields are set tot true. How can I exclude this row from the query?

Same would apply if searched value is false


texts: What is the maximum number of local variables that can participate in SET operation?

texts:
I have a stored procedure that contains business logic. Inside it I have around 1609 variables (don't ask me why, this is how the engine works). I try to SET a variable to the concatenated value of all other variables. As a result during creation I get the error:

  Msg 8631, Level 17, State 1, Procedure XXX, Line YYY Internal error:
  Server stack limit has been reached. Please look for potentially deep
  nesting in your query, and try to simplify it.

I figured out that the error is due to the number of variables that I need to use in the SET operation. I can perform the assignment by splitting it in two.

My question is are there some restrictions in this area? I checked, but I did not find any.

We checked the error described in this KB, but this is not our case. We don't use any CASE expressions inside our code. We use that temporary variable to prepare a list of values that have to be replaced using a CLR function. We updated our SQL Server to SP3 CU6 (latest up to date), but we still experience the error.


texts: Database Design for Airline Reservation System

texts:
I'm just a beginner seeking some answers. I'm creating a very simple database system for an airline company, just for learning purposes. Here are my questions:

How to show relationships for entities that I created?
How can I show normalization in a relationship? (at least third normalization form)

Thanks in advance,
Chitru


texts: MySQL 5.7 - Where is the mysql_secret file on Windows 7?

texts:
I've just installed a copy of MySQL 5.7 for testing, and I love the fact that it now only creates a single root account, and puts the password into a file called .mysql_secret.

There's just one problem. Where can I find the above mentioned file. On Linux it is apparently found in $HOME, but my test DB is on Windows.

I tried various places including %USERPROFILE% but I just can't find it anywhere!!

Can anyone please tell me where to find this file? As otherwise my testing will be over before it has begun.


texts: Foreign key part of composite primary key of new table

texts:
I have tried searching loads but I have not received a concrete answer to my problem.
Suppose I have Table 'A' with primary key as Apk.

This Apk is a foreign key in Table 'B'. In Table 'B' I have also another column Bcol.

Now for 'B' can I have a composite primary key of 'Bcol' and 'Apk': PRIMARY KEY(Bcol, Apk)? Is it advisable?


texts:
I just started learning about Databases, and my first assignment has information about an Art Museum that we are supposed to use to define a DB for, demonstrated with an EER Diagram.

One specification is as follows:

"The museum has a collection of ART_OBJECTS. Each ART_OBJECT has a unique Id_no, an Artist (if known), a Year (when it was created, if known), a Title, and a Description. The art objects are categorized in several ways, as discussed below."

What role, if any, does "if known" play in terms of the diagram? I reviewed the instructors notes looking for something resembling such specification, but I couldn't find anything in the diagram components that would imply whether or not information is available. Thoughts?


texts: Extract all objects from json array using MariaDB 10.3

texts:
How can I extract every object from this json array using MariaDB 10.3? I think the JSON_TABLE function in MySQL does what I need, but I'm currently constrained to MariaDB.
SET @json_doc='[
    {
        "DatabaseName":  "master",
        "Schema Name":  "dbo",
        "Object Name":  "CommandLog",
        "column_index_id":  1,
        "Buffer size(MB)":  0.02,
        "BufferCount":  2,
        "Row Count":  3,
        "Compression Type":  "NONE"
    },
    {
        "DatabaseName":  "master",
        "Schema Name":  "dbo",
        "Object Name":  "xevents",
        "column_index_id":  0,
        "Buffer size(MB)":  0.16,
        "BufferCount":  21,
        "Row Count":  0,
        "Compression Type":  "NONE"
    }
]';

With this query I can get the one of the objects in the array, but not all. There won't always be the same number of objects in the array so I need it to be dynamic.
WITH RECURSIVE data AS (
  SELECT
    JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),'$[0]') AS json_column,
    JSON_VALUE(@json_doc,CONCAT('$[0].',JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),'$[0]'))) AS json_data,
    0 AS column_index

  UNION
  SELECT
    JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'), CONCAT('$[', d.column_index + 1, ']')) AS json_column,
    JSON_VALUE(@json_doc,CONCAT('$[0].',JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),CONCAT('$[', d.column_index + 1, ']')))),
    d.column_index + 1 AS column_index
  FROM data AS d
  WHERE d.column_index < JSON_LENGTH(JSON_KEYS(@json_doc,'$[0]')) - 1
)

SELECT json_column, json_data, column_index
FROM data;

This is the result I currently get.
------------------------------------------
|json_column     |json_data |column_index |
|DatabaseName    |master    |0            |
|Schema Name     |dbo       |1            |
|Object Name     |CommandLog|2            |
|column_index_id |1         |3            |
|Buffer size(MB) |0.02      |4            |
|BufferCount     |2         |5            |
|Row Count       |3         |6            |
|Compression Type|NONE      |7            |
-------------------------------------------

This is the result I desire.
-------------------------------------------------------
|json_column     |json_data |column_index |array_index|
|DatabaseName    |master    |0            |0          |
|Schema Name     |dbo       |1            |0          |
|Object Name     |CommandLog|2            |0          |
|column_index_id |1         |3            |0          |
|Buffer size(MB) |0.02      |4            |0          |
|BufferCount     |2         |5            |0          |
|Row Count       |3         |6            |0          |
|Compression Type|NONE      |7            |0          |
|DatabaseName    |master    |0            |1          |
|Schema Name     |dbo       |1            |1          |
|Object Name     |xevents   |2            |1          |
|column_index_id |0         |3            |1          |
|Buffer size(MB) |0.16      |4            |1          |
|BufferCount     |21        |5            |1          |
|Row Count       |0         |6            |1          |
|Compression Type|NONE      |7            |1          |
|...             |...       |...          |n          |
-------------------------------------------------------


texts: I don't know how to fix ORA-04091 mutating trigger error

texts:
I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table.

The error is:

  error ORA-04091: table USUARIO.LINEAS is mutating, trigger/function may not see it ORA-06512: at "USUARIO.TRG_ACTUALIZARPEDIDO", line 5 ORA-04088: error during execution of trigger 'USUARIO.TRG_ACTUALIZARPEDIDO'

I've learning about it, but can't find a solution or understand by myself what's the problem.

CREATE OR REPLACE TRIGGER trg_actualizarpedido
AFTER INSERT OR UPDATE OR DELETE ON LINEAS
FOR EACH ROW

DECLARE
    sumaImporteLineas NUMBER := 0;

BEGIN
    SELECT SUM(IMPORTE) INTO sumaImporteLineas FROM LINEAS WHERE NUMPEDIDO = :NEW.NUMPEDIDO;
    UPDATE PEDIDOS ped SET ped.TOTAL = sumaImporteLineas WHERE ped.NUM = :NEW.NUMPEDIDO;
END;

Thanks!


texts:
SELECT top 50 column1, column2,column3,column4
FROM (Select Distinct column1,column2, column3, column4 from Table
Where Column2>= '2016-07-01' )
Order By Column2;

The result is not what I am expecting. I want to make columns Column1, Column2, Column3, Column4 all distinct, meaning each column should have no two identical values. Is there a way to do this?


texts: why does it shows truncated incorrect integer values when using char(salary,'$99,999)

texts:
I tried the following query:

select
    concat(e.first_name,"  ",e.last_name) as 'employee name',
    char(s.salary*15/100,'$99,999') as 'No.Of Dollors'
from
    employees e
join
    salaries s
    on e.emp_no=s.emp_no
group by
    e.emp_no,s.emp_no;

It raises:

  truncated incorrect integer vale:'$99,999'


texts: ORACLE DB literal does not match format string

texts:
I' am having this error:

 ORA-01861: literal does not match format string
    01861. 00000 -  "literal does not match format string"
    *Cause:    Literals in the input must be the same length as literals in
               the format string (with the exception of leading whitespace).  If the
               "FX" modifier has been toggled on, the literal must match exactly,
               with no extra whitespace.
    *Action:   Correct the format string to match the literal.

It happened overnight without any changes in the database configuration. It looks like there is a problem with sysdate format but I'm not 100% sure. We have many places in our procedures where we use to_date(to_char(sysdate, 'YYYY-MM-DD')) from dual which actually breaks. Is there any way to format sysdate to match the following expression?

to_date(to_char(sysdate, 'YYYY-MM-DD')) from dual


texts: Obtaining a MIN() value in a column in a subquery

texts:
I have the two following tables:

CREATE TABLE PC (
    model ...,
    speed ...,
    ram...,
    ...
);

CREATE TABLE Laptop (
    model ...,
    speed ...,
    ram ...,
    screen...,
    ...
);

And am trying to create a query that gives the model numbers of all laptops with a speed slower than that of any PC.

I came up with:

SELECT model
FROM Laptop, (SELECT MIN(speed) AS speed FROM PC)slowpcs
WHERE Laptop.speed < slowpcs.speed;

However even after double checking that I had added a laptop that meets this criterion and running the code, it's giving me an empty results list.

INSERT INTO Laptop
(model, speed, ram)
VALUES
(2006, 3.00, 2048),
(2007, 1.73, 1024),
(2009, 2.80, 512);

INSERT INTO PC
(model, speed, ram)
VALUES
(1070, 2.00, 1024,
(1071, 2.10, 512),
(1050, 1.90, 512);


texts:
I am trying to install MySQL Server on my Windows-7 64-bit machine. I have downloaded MySQL Server Community Edition and MySQL Workbench. I am trying to start a new Server Instance from Workbench. I clicked on New Server Instance -> localhost, but I am getting the following error

I have been trying this for the last 6 hours. Any pointer will be great help.


texts: Powershell SQL Script Execution in Selected Schemas Only

texts:
I have many schemas in one database. In that database I have some SQL scripts to be executed. I want to execute those SQL scripts in only selected schemas.

Suppose I have 10 schemas in total (A to J). I want to execute a SQL script in all schemas except B,G,H and prompt the user for the excepted schemas. How can I do this?


texts: Does the SQL Server optimizer inline function calls in stored procedures?

texts: I'd like to factor out some of the functionality in our SPs into separate functions/stored procedures, but I'm worried about the potential performance penalty. In particular, how efficient will be a join with a table-valued function, as opposed to writing out the query from that function in full?


texts: DB2: database hangs while trying to execute simple select query on specific table

texts:
No errors or warnings, database just hangs while trying to execute simple select query on specific table. Other tables are accessible until I trying to query specific (faulty) table.

This should not be related to large volume of data, table contains only few thousands of rows.
I've tried db2 restart db2 dbName, but it didn't help.

Something wrong with data? Any suggestions how to recover?

DB2 9.5.3 on Solaris


texts: SQL Server 2008r2 keeps hogging CPU not responding after a while online

texts:
Please provide some advice to an accidental DBA.

We are currently hosting in an Azure VM an instance of a SQL Server 2008r2 DB. It is being used to hold data relative to a few websites and it has been online and working 24/7 for 15 months except for one hiccup related to the log file size (which we solved switching to SIMPLE recovery mode).

Well, the problem is that recently every few weeks or so, the MSSQL process starts hogging ~50% of CPU usage in the host machine and stops serving some basic queries (it stucks forever processing the query blocking every other pending or new query). There aren't any processes running (spid > 50) and the daily backups seem to work as usual. The Error log file shows no strange events.

Restarting the DB takes almost 8 hours (which I ignore if it's normal) and only delays the problem a few weeks. I'm not sure at all what may cause the problem, as there is no shortage of disk space and, well, I'm quite unexperienced in these scenarios.

Any ideas? I'm fearing is log-related or somehow involves disk caching but I'm completely lost here. I'd honestly appreciate advice for a newcomer on how-to diagnose and isolate the problem.

Additional info: Some of the queries that the DB dislikes involve TOP and ORDER BY clauses which when removed work flawlessly. Particularly this case amuses me:

SELECT TOP 101 * FROM [table] ORDER BY id DESC

...hangs the DB completely and...

SELECT TOP 100 * FROM [table] ORDER BY id DESC

...works flawlessly in 00:00:00.


texts: Galera cluster into Google cloud platform

texts:
We have a galera cluster with 3 nodes, on 3 different physical machines but all located in the same datacenter.

From what I understood, the reason they deployed this in the past was to increase availability and reliability, DC failures are not a concern. Each node is installed on a VM using 12 cores and 4Gb RAM. (from the monitoring I did we can decrease the amount of cores used to 4)

We are asked to migrate this to Google Cloup Platform in order to get rid of the ops tasks. I could create 3 compute engine instances and deploy the galera cluster, GCP even have a Percona XtraDB Cluster one click deployment service, but I have difficulties to see the added value compared to Cloud SQL instances with replication and backup. I am not very familiar in scaling heavy load systems.

The db hosted in these nodes is kind of critical and should ensure maximum availability and reliability.

What strategy should I adopt in order to migrate this architecture to GCP ?


texts: Cluster command locking read only queries on replica

texts:
I noticed that clustering can take a long time and lock the table entirely, even for reads.

Index Organized Tables and incremental clustering would be perfect, but sadly they aren't supported by PostgreSQL.

Now I realize that it also lock the replicas' tables, so apparently there isn't a way to cluster without having a lot of downtime?

Setting rds.logical_replication to 1 on both master and replica instances didn't help.

I would like to cluster four big tables every week for better performance.


texts: How to troubleshoot SQL "The timeout period elapsed prior to completion of the operation or the server is not responding."

texts:
I'm trying to troubleshoot a timeout issue that occurs on one of our SQL 2005 servers.

The database responds normally for about a day until we  start to see sql timeout errors.  After that all queries take 30+ seconds to return and it does not return to normal operation until we reset the SQL services. The sql queries are very simple, updating a single row in a database.

It seems like the problem is specific to one database because we can run the same queries on another database on the same server and they return normally. I've looked into possible locks using the technique outlined here and I don't see anything that sticks out.  This is on one of our beta servers so there are very few requests to it.  Any ideas?  Thanks


texts:
i'm an accidental DBA for a company and having trouble figuring out this permissions situation.

We have a new 2017 SQL server that I would like to get our report writer / analysts access to a few of the databases. She is in an "analyst" AD group with another analyst.  That group is setup at the server level login's with a few of the databases checked off (not MSDB) in the user mapping.

While trying to query the databases she should have access to, she gets the same exact errors described in this post (including the MSDB datababase part), but the other analyst does not: https://www.codeproject.com/Articles/548424/The-Server-Principal-xxxx-is-Not-Able-to-Access-th

We can't find the 2 users setup individually anywhere on the server where one would have different access than the other. We also checked other groups to make sure they were not in them. Is it possible it's a way her computer/SSMS is configured? We changed her default SSMS database to a database she should have access to.

The resolution in the article I linked suggests to grant connect access to the public role or individual users/groups in MSDB but when testing that, she could view data in that DB and had more access than we would like. I thought I read connect should only grant her enough access to connect to the DB but not see anything, however that didn't seem to be the case.

Any suggestions on what we're missing? Let me know what what additional details you may need.

Thank you,
Dave


texts: How to remove references to performance_schema.user_variables_by_thread in mysql 5.6?

texts:
I have a Jira 6.4.7 instance running on MySQL 5.6.44. I'm in the progress of updating the Jira instance to 7.0.5, which still supports MySQL 5.6.
I accidentally hooked up a newer Jira 7.0.5 to the same database, wich inserted performance_schema tables. Jira 7.0.5 probably thought it was running with >= mysql 5.7.
I immediately stopped the Jira 7.0.5 instance, but the references to performance_schema are still there.
When I look at the database with MySQL Workbench, I get this error:
Error Code: 1146 Table 'performance_schema.user_variables_by_thread' doesn't exist

Everything still seems to work with jira 6.4.7, but I can't dump the database anymore to move it somewhere else.
I've tried using the xml export and import from jira, but this copies the error from the database.
I've tried
[mysqld]
performance_schema=OFF

but the error persists.
I couldn't find the performance_schema table in other MySQL databases, because then I could have copied the table.
So how can I fix this? Can anyone provide an SQL statement of how this table should look like? Or how can I find and delete the references to performance_schema in my current MySQL 5.6 database?.


texts: Do database engines update when the updated value is the same?

texts:
Suppose I am running multiple column update on one row. Does the database engine physically write new data to the database if some of the columns contain the same values as existed previously in the database? If so, how can it be avoided?

I'm about to implement a job which will run an update on a large table and most of the values will be the same but still, it has to recalculate them all. I am wondering if the update will also rewrite each column even if there is no need to, because the storage media will degrade faster.


texts: how to add shard into current cluster without sharing any chunks

texts:
my current cluster sh.status() is as below:

  shards:
    {  "_id" : "rsDev",  "host" : "rsDev/10.60.90.224:27003,10.60.90.224:27004" }
    {  "_id" : "rsRaw",  "host" : "rsRaw/10.60.90.224:27001,10.60.90.224:27002" }
  active mongoses:
    "3.0.14" : 1
  balancer:
    Currently enabled:  yes
    Currently running:  no
  databases:
    {  "_id" : "mike",  "partitioned" : true,  "primary" : "rsDev" }
        mike.PDiff
            shard key: { "_id" : 1 }
            unique: false
            balancing: true
            chunks:
                rsDev   23
                rsRaw   22
            too many chunks to print, use verbose if you want to force print

Now I want to add a new shard into this cluster by

db.runCommand({addShard: "rsPro/10.60.90.224:27005", maxSize:1})

After google about maxSize, it is a soft limit for balancer.
If I add the new shard rsPro using above command, it start to balance all chunks.
If I don't stop the balancer, is there any way to prevent any chunk moving to the new shard rsPro ?


texts: SYS_CONTEXT INSTANCE changing on failover?

texts:
When using Oracle RAC with either Fast Connection Failover or TAF (Transparent Application Failover), for a given database connection, is SYS_CONTEXT('USERENV', 'INSTANCE') going to change when there's an automatic failover?

(I can't test right now, not having a RAC instance available on hand.)


texts: MYSQL matching one column to another in a different table via a FULLTEXT index

texts:
I current have two different tables.

The first table has a list of titles and IDs associated to these titles; the second table is a list of random heading.

What I would like to know is if there is a way to match up all the titles in table 2 to the closest matching title in table 1. Is this possible?

I've tried :

SELECT    title_table .*,
          random_titles.*,
          MATCH (title_table.title) AGAINST (random_titles.title) AS relevance
FROM      title_table
ORDER BY  `relevance` DESC

But that did not work.

I know I could use this query as each title is being put in table 2 with PHP but I already have a lot of titles in the database.


texts: Should I pause Mirroring and stop SQL Server services before running windows updates?

texts:
I am running Windows update on my SQL Servers (which are in a Principal-Mirror partnership, in High Safety mode).

I am starting with updating the mirror and I was wondering, to increase safety, should I pause mirroring and stop SQL Server Services and then run Windows Update?  Or is this totally unnecessary?


texts: Odd execution plan behaviour from variations in WHERE...IN(List)

texts:
(reposted from stack-overflow by request)

I've been looking at trying to optimise (or at least change) some EF code in C# to use stored procedures, and found what seems to be an anomaly (or something new to me) when finding rows matching a constant list.
The typical manually-generated short query I'd use would be something like...

SELECT Something FROM Table WHERE ID IN (one, two, others);

We had an EF query that we were replacing with a stored procedure call, so I looked at the output, saw that it was complex and thought my simpler query (similar to the above) would be better. It wasn't. Here is a quick demo that reproduces this, trying a couple of intermediate variations to see where the change-over in plan behaviour occurs.

Can anyone explain why the execution plans for the final version - with the

...WHERE EXISTS(... (SELECT 1 AS X) AS Alias UNION ALL...) AS Alias...)

construct is better - seemingly because it omits the costly SORT operation, even though the plan includes TWO index scans (or more with more list members) rather than the one of the simpler query.

Here's a self-contained example script (I hope)...

USE SandBox;  -- a dummy database, not a live one!
-- create our dummy table, dropping first if it exists
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Test')
    DROP TABLE Test;
CREATE TABLE Test (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, FormId INT NOT NULL, DateRead DATE NULL);
-- populate with some data
INSERT INTO Test VALUES (1, NULL), (1, GETDATE()), (1, NULL), (4, NULL), (5, NULL), (6, GETDATE());

-- Simple query that I might typically use
-- how many un-read entries are there for a set of 'forms' of interest, 1, 5 and 6
-- (we're happy to omit forms with none)
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test AS T
 WHERE  T.FormId IN (1, 5, 6)
   AND  T.DateRead IS NULL
 GROUP BY T.FormId;

-- This is the first step towards the EF-generated code
-- using an EXISTS gives basically the same plan but with constants
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (VALUES (1), (5), (6)
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
   AND  T.DateRead IS NULL
 GROUP BY T.FormId;

-- A step closer, using UNION ALL instead of VALUES to generate the 'table'
-- still the same plan
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (    SELECT 1
                                UNION ALL
                                SELECT 5
                                UNION ALL
                                SELECT 6
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
   AND  T.DateRead IS NULL
 GROUP BY T.FormId;

-- Now what the EF actually generated (cleaned up a bit)
-- Adding in the "FROM (SELECT 1 as X) AS alias" changes the execution plan considerably and apparently costs less to run
SELECT  T.FormId, COUNT(*) AS TheCount
  FROM  Test T
 WHERE  EXISTS (    SELECT NULL
                      FROM (    SELECT 1 FROM (SELECT 1 AS X) AS X1
                                UNION ALL
                                SELECT 5 FROM (SELECT 1 AS X) AS X2
                                UNION ALL
                                SELECT 6 FROM (SELECT 1 AS X) AS X3
                            ) AS X(FormId)
                     WHERE X.FormId = T.FormId
               )
   AND  T.DateRead IS NULL
 GROUP BY T.FormId;

Can anyone help me to understand why and (importantly for my learning) if there is a benefit in wider use for using this kind of query format?

I looked around for anything special in (SELECT 1 AS X) stuff and though many show it as being common in EF output, I couldn't see anything about this particular apparent benefit.


texts: FATAL: database does not exist in postgresql for jdbc

texts:
I create a database by installing Postgres on my ubuntu 20.04 machine and used the following commands to create it:
sudo su - postgres
psql
CREATE USER preopus WITH PASSWORD 'admin222';
CREATE DATABASE preopusDB WITH OWNER=preopus;

and received no errors. When I tried using slick-codegen by calling the database with this URL:
"jdbc:postgresql://localhost/preopusDB?user=preopus&password=admin222", it gave me the error:

[error] (run-main-0) org.postgresql.util.PSQLException: FATAL: database "preopusDB" does not exist

If anybody can help it would be greatly appreciated.


texts: Are master/slave replication relationship between different versions of SQL Server possible?

texts:
I have a database server, SQL Server 2005, that's inside a private network. Now i need to make a database there available for a webserver, so i want to replicate this db to an external server, which is running SQL Server 2008. I want the internal server to be the Master and the external one the Slave. New data will be added a few times a day maybe, infrequently, and should be replicated smoothly without manually intervention.

Is this a possible scenario, or do i need to upgrade the SQL Server on the inside to the same version as the external one?

If it's possible, are there drawbacks in doing it in this way that makes the upgrade scenario more recommended anyway?


texts: File creation dates of archived WAL files not in line with archive_timeout in PostgreSQL 9.6

texts:
I have configured archive_timeout = 5 (i.e. 5 seconds) for a PostgreSQL 9.6 database in order to conduct tests around point-in-time recovery. My archive_command invokes cp into a local archive directory.

If I check the file modification dates in that local archive directory I see that WAL files are typically created in there only approx. every 20 seconds apart, i.e. far more than the configured 5 seconds. This is for PostgreSQL 9.6 running inside a Docker container on a lightly loaded system.

What could explain this large discrepancy?


texts: Do temporal tables log changes when there are none?

texts:
Unfortunately I don't have ready access to a SQL 2016 instance to test this. If I had a temporal table with the following schema:

create table blah (
    foo int identity(1,1),
    baa int
)

and I were to run the following statement:

update blah set baa = baa

would it log changes as if I'd actually updated data?

I've had a look around the MSDN article for temporary tables and the one for Modifying Data but I can't find anything there specifying this.


texts: SQL Server Replication: How do you change the distributor?

texts:
I've been given the task to change the distributor to a new server for pre-existing replication (both snapshot and transaction).

Server A:  Existing Replication (Publisher)
Server B:  Existing Distributor
Server C:  New Distributor

I haven't done this before and all I've done is setup the Distributor Properties on Server C to accept Server A as a Publisher.
What other steps need to happen to change the distributor please?
Thank you for your help.


texts: How can I set myself as owner of a database ? just using sql

texts:
As I want to view dependencies of just a couple of tables in my clients database I want to set myself as the owner of the database.

If the database is called MyDatabase and my userName is MyDomain\MyUserName
How do I use the ALTER AUTHORIZATION statement to set myself as Owner ?


texts: Shirking of Log file on Primary Server of Transaction Log shipping

texts:
I have a database with size 200 GB Total(mdf+ldf) and Log file(ldf) with 50 GB and 99% unallocated space in ldf file i.e. not being used at all.

We had setup log backups for every 5 minutes, so there no problem with ldf file increasing.

We had setup Log shipping on Prod server(Primary) and DR Server(Secondary)

Now i have a question,

Can i shrink the log file of the database on primary to 1 GB?
Can i shrink the log file i.e. ldf without changing the recovery model of database to 1 GB?
Can i shrink the log file without using NOTRUCATE option, if i use ssms wizard to shrink.
Will shrinking cause for log file to grow more after shrinking?

Expedite answers would be appreciated

Thank you


texts: Changes in Oracle Database implicit conversion rules for select statement

texts:
Due to hardware failure after a power outage we had to recreate the database for one of our 3rd party applications from an dumpfile (created via exp). The old hardware had Oracle 10G R2 running while the new hardware is running Oracle 11G R2. Everything went smoothly and the application ran normally again.

The last few days our users reported problems with the software. After some research the application runs a sql statement which tries to filter a varchar column with a number thereby forcing an implicit conversion of the column.

In the previous version of Oracle everything went fine but now an "ORA-01722: invalid number" is thrown.

Might there be some setting we have missed for the new database?

We already contacted the developing company but it will take some time.

Sample table and data:

create table conversion (
  col1 varchar2(10)
  );

insert into conversion values ('test2');
insert into conversion values ('42');
insert into conversion values ('test3');

Simplified statement throwing the error:

select count(*) from conversion where col1 = 42;

Thanks in advance and best regards
Jan


texts: SQL (Redshift) SELECT COUNT from CSV column

texts:
Hi exchange community,

Problem statement and details:
I am trying to query a column that contains CSV data to produce a count of instances of each value in the table.  There are a total of 14 possible values in the CSV data. These are static and will never change. Each record contains at least one value in the CSV column but may contain up to all 14 values. Each value may only appear at most once in the CSV column per record.

Sample Data from Pets column:
cat, dog fish, rat
dog
dog, cat horse, ferret, zebra
dog, cat, alligator, bear, fish, bird

what have i tried?
I've gotten as far as being able to return a view that contains a single record with columns containing a count for the number of instances. It's close to what I need, I just need to have this transposed (rows <-> columns).  any ideas?

SELECT
    count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS dog
    ,  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) as cat
    ,  count(case when vet.pets like '%rat%' THEN 1 ELSE NULL END) as rat
    ,  count(case when vet.pets like '%bird%' THEN 1 ELSE NULL END) as bird
    ,  count(case when vet.pets like '%fish%' THEN 1 ELSE NULL END) as fish
    ,  count(case when vet.pets like '%hamster%' THEN 1 ELSE NULL END) as hamster
    ,  count(case when vet.pets like '%ferret%' THEN 1 ELSE NULL END) as ferret
    ,  count(case when vet.pets like '%horse%' THEN 1 ELSE NULL END) as horse
    ,  count(case when vet.pets like '%zebra%' THEN 1 ELSE NULL END) as zebra
    ,  count(case when vet.pets like '%bear%' THEN 1 ELSE NULL END) as bear
    ,  count(case when vet.pets like '%alligator%' THEN 1 ELSE NULL END) as alligator
    ,  count(case when vet.pets like '%sugar_glider%' THEN 1 ELSE NULL END) as sugar_glider
    ,  count(case when vet.pets like '%tiger%' THEN 1 ELSE NULL END)  as tiger
    ,  count(case when vet.pets like '%rock%' THEN 1 ELSE NULL END) as rock
    FROM vet

This produces:

  dog | cat | rat | bird | fish | hamster | ferret | horse | zebra |
  bear | alligator | sugar_glider | tiger | rock
      3 | 5 | 2 | 6 | 8 | 1 | 8 | 5 | 1 | 3 | 2 | 2 | 4 | 2

But I need it in the format:

dog | 3
cat | 5
rat | 2
bird | 6
fish | 8
hamster | 1
ferret | 8
horse | 5
zebra | 1
bear | 3
alligator | 2
sugar_glider | 2
tiger | 4
rock | 2


texts: Linked Server Query is not working in SQL Agent job

texts:
I have a problem with linked server query from SQL Agent job. Linked server connection working fine. But while executing query from sql agent job it fails.

  Microsoft OLE DB Provider for SQL Server: Login failed for user

I used current context credential. All fine from linked server settings. But, I can not run the query from SQL Agent job.


texts: Inspect size of Postgres database and storage space usage on a DigitalOcean managed database instance

texts:
On an instance of a managed Postgres database system on DigitalOcean.com, I have a limit of 10 gigs of storage space.
How can I tell how much space my Postgres instance is using? How can I tell what storage space remains available?


texts: oracle 12c grid standalone Installation failed with crsconfig_params file not found error in windows

texts:
Oracle 12c grid installation failed (39% progressed) with "crsconfig_params file not found" error in Windows operating system:

Can we abort the session or need to fix and retry the session?

Please share your advice.


texts:
Consider the following command, which I've wrapped for readability:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'DIFF',
@Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y'" -b

It returns the following error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'E'.
Msg 103, Level 15, State 4, Line 3
The identifier that starts with 'EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup',
@BackupType = 'DIFF', @Verify = 'Y', @C' is too long.
Maximum length is 128.

When I remove the sqlcmd it works.  I know I'm doing something wrong, however I don't understand what the problem is.


texts: Error "#1118 - row size too large" on the first row of the table only

texts:
I ran into a problem using a mySQL database. I have some columns as type text but when I try to enter data into the first row I get the error code "#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs" for anything with more than 15 characters. This only occurs for the first row, all the other rows in the column and table work just fine. Help!

Field Type    Collation              Null   Default  Extra
pid   int(11)                        No     None     AUTO_INCREMENT
3     text    utf8_general_ci        Yes    NULL
6     text    utf8_general_ci        Yes    NULL
7     text    utf8_general_ci        Yes    NULL
8     text    utf8_general_ci        Yes    NULL
9     text    utf8_general_ci        Yes    NULL
10    text    utf8_general_ci        Yes    NULL
21    text    utf8_general_ci        Yes    NULL
22    text    utf8_general_ci        Yes    NULL
23    text    utf8_general_ci        Yes    NULL
24    text    utf8_general_ci        Yes    NULL
25    text    utf8_general_ci        Yes    NULL
26    text    utf8_general_ci        Yes    NULL
27    text    utf8_general_ci        Yes    NULL
28    text    utf8_general_ci        Yes    NULL
29    text    utf8_general_ci        Yes    NULL
30    text    utf8_general_ci        Yes    NULL
31    text    utf8_general_ci        Yes    NULL
32    text    utf8_general_ci        Yes    NULL
33    text    utf8_general_ci        Yes    NULL
34    text    utf8_general_ci        Yes    NULL
35    text    utf8_general_ci        Yes    NULL
36    text    utf8_general_ci        Yes    NULL
37    text    utf8_general_ci        Yes    NULL
38    text    utf8_general_ci        Yes    NULL
39    text    utf8_general_ci        Yes    NULL
40    text    utf8_general_ci        Yes    NULL
... 41-59 follow

Indexes:

Keyname Type    Unique  Packed  Field   Cardinality Collation   Null
PRIMARY BTREE   Yes     No      pid     61          A

Server Version: 5.5.9


texts: DB Master Key Encrypted By *Correct* Service Master Key?

texts:
My client has frequently run process where we move databases from a prod environment to lower environments (refresh QA with prod database, etc).  These DBs have database master keys on them.  We have no issues re-encrypting these into the service master key on the destination server, but here's the annoying part:

sys.databases says that the DMK is already encrypted (albeit with
the source service's SMK)
There is no way we can find to tell if the DMK is encrypted by the current SMK (checked all the encryption DMVs)
The SMK signature in sys.symmetric_keys is always 0x01 (not unique across servers).

This isn't a problem, per se.  The fix is always re-encrypt the DMK to the SMK.  We just would like to be able to tell if it is already done or not.  Anyone have any tricks up their sleeve?

Thanks,
Eric


texts: How does unnest() treat NULL values and empty arrays?

texts:
I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted to pass fake array with single null element if the source array is empty, but it does not work as expected:

Query 1

select element
from (
  select array['a']::text[] as arr --< single non-null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
    else sub.arr
    end
  )
) element
-- returns 1 row with element = "a"

Query 2

select element
from (
  select array[]::text[] as arr --< empty array
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
    else sub.arr
    end
  )
) element
-- returns 0 rows (should return 1 row with element = NULL?)

Query 3

select element
from (
  select array[null]::text[] as arr --< single null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
    else sub.arr
    end
  )
) element
-- return single row with element = NULL

Just figured out that select array_length(array[]::text[], 1) returns NULL - my question is why?


texts: mysql dump a list of query to file and restore to another database

texts:
I have 2 databases. The first one is a production have some data deleted. The 2nd one is a backup so it has everything. I traced the log so I got every records are deleted in production database by the list like this

SELECT * FROM `journal_details` WHERE `journal_details`.`journal_id` = 28679;
SELECT * FROM `journals` WHERE `journals`.`id` = 28679;
SELECT * FROM `journal_details` WHERE `journal_details`.`journal_id` = 29262;
SELECT * FROM `journals` WHERE `journals`.`id` = 29262;

I want to migrate the result of queries above to the production database. How to do it the easy way? I can't restore the whole database due to it will overwrite some other records :(

PS: I tried mysqldump like this but got errors :(

mysqldump -u dk2303  -h 127.0.0.1 -p  rm_production --default-character-set=utf8 --result-file=/home/dk/dump.sql --where="SELECT * FROM journal_details WHERE journal_details.journal_id = 28679; "
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `attachments` WHERE SELECT * FROM journal_details WHERE journal_details.journal_id = 28679; ': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM journal_details WHERE journal_details.journal_id = 28679' at line 1 (1064)


texts: How to persist selection of foreign key in related table

texts:
I have a database design that I need to get some help with

Suppose I have three tables, namely preferences, books, genres. The tables are related as follows:

The following tables are accessible in a view, in which the user is allowed to add a book to his personal page.

Currently, the view dictates that the user first choose a genre, before he gets the opportunity to select a book (A basic filter).

However, imagine that the user has selected a genre and decides to come back to select a book at a later point - Then this architecture doesn't support that the selected genre is saved as a part of the preferences. Meaning that, even though the user saved his preferences with a genre, he will have to, once he gets back to add his book, select the genre again in order to then select the book.

Therefore, how would I optimally "refactor" this architecture in order to support what I want to achieve.

My first intuition would be to add one more column to the preferences table named "genres_Id" and have a foreign key pointing to the genres table. I guess this is not good practice though..


texts: Log shipping 2008 r2 SP2 server to a 2008 r2 SP3 CLUSTER

texts:
We have recently configured one database to be replicated with log shipping in production, using SQL Server 2008 R2.

We have up and running more than 150 database replicas without any problems. But now, when we try to configure the new LS replica in a new server environment we have experiencing one error:

The log shipping is configured from a 2008 r2 SP2 server to a 2008 r2 SP3 CLUSTER (the only difference compared with our other replicas)

the LS backup works Ok in the primary
the LS Copy works OK and successfully copy the files from the primary to the secondary
the LS Restore restores one, two or three trn files,  and than it fails:

  Restored log backup file.

  Secondary DB: 'xxxxyyyy_mirror' File: 'P:\ xxxyyyy _TLogs_Mirror_LS\xxxyyyy\ xxxyyyy _20151120163351.trn'

  The restore operation was successful. Secondary Database: ' xxxyyyy _mirror', Number of log backup files restored: 1

The next job execution:

  Error: An error occurred while processing the log for database ' xxxyyyy _mirror'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

  RESTORE LOG is terminating abnormally.
  Processed 0 pages for database xxxyyyy _mirror', file 'Vitacare2k6_Dev' on file 1.

  Processed 1 pages for database ' xxxyyyy _mirror'
  file 'Vitacare2k6_Dev_log'
  on file 1.

  (Dot Net SqlClient Data Provider)

And then the database gets into Suspect Mode
the files (MDFs, LDFs and TRNs) are in an Clustered Disk shared by the cluster nodes.

I tried to restore the TRNs in the same full backup from the cluster my PC and I get no errors.

Can you guys provide me any guidance on where I could troubleshoot this further, I would greatly appreciate it.
Thanks for your time and input.
Rafael

Notes: I tried to restore the files in other instance (my Laptop) and i get no error, so i do not believe in corruption
I also tested in restore the Sp2 to an SP2 instance manually and sp2 to sp3 instance with no errors.


texts: Designing a database where records can reference different foreign keys

texts:
I can use PostgreSQL or Microsoft SQL, whichever provides the needed capabilities.

I need help designing a database that has records that can reference FKs in different tables. Given the following fictitious structure:

CREATE TABLE Lists (
  ListID int NOT NULL AUTO_INCREMENT,
  ListName varchar(50) NOT NULL,
  Description varchar(255),
  PRIMARY KEY (ListID)
)

CREATE TABLE PeopleNames (
  PersonNameID int NOT NULL AUTO_INCREMENT,
  GivenName varchar(50) NOT NULL,
  Surname varchar(50) NOT NULL,
  PRIMARY KEY (PersonNameID )
)

CREATE TABLE PetNames (
  PetNameID int NOT NULL AUTO_INCREMENT,
  PetName varchar(50) NOT NULL,
  PRIMARY KEY (PetName)
)

CREATE TABLE ListEntries (
  ListEntryID int NOT NULL AUTO_INCREMENT,
  EntryID int NOT NULL, <-- this could be a PetNameID, PersonNameID, or even ListID, it's not a strictly enforced FK except by application code
  PRIMARY KEY (NameID)
)

Ok, so in this example, the ListEntries table has a column called EntryID. The EntryID could reference the PersonNameID, PetNameID, or even another ListID field. CRUD functionality will be wrapped in an API which will enforce relations.

What's the best way to model this? Should I just create another column in the ListEntries table called Table varchar(50) NOT NULL which references the table that the EntryID relates to?

I know I can use something like Redis or MongoDB, but fundamentally, the data is related, so shouldn't a Relational DB be used?

The database will NOT be updated frequently, but it will be read frequently. Also, could I generate a JSON file as a Materialized View and stuff that into another table for fast reads?


texts: 54001 stack depth limit exceeded in recursive stable function

texts:
I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I declared the function as STABLE and executed it again. Now it always aborts with the above error, telling me to increase the max_stack_depth which sounds like endless recursion to me.

What is happening here?


texts: How do I render GeoHashes with PostgreSQL?

texts: Are there any geohash implementation for PostgreSQL? I couldn't find any.


texts: We know that we can normalize 1NF to 2NF by removing the partial dependencies. But I saw a lot of examples only show partial dependencies with 1 one of the PKs. If a table (in 1NF) has 3 PKs, and there is an attribute that is partially dependent on 2 of the 3PKs (different than the usual example which only depends on 1 of the PKs), how should I normalize the table to 2NF?


texts: Is there a major RDBMS that DOESN'T allow column name quoting/escaping?

texts:
The title basically says it: Are there any major DBs that don't handle column name quoting/escaping?

For instance, suppose I want to have a column called FROM (and let's assume I have a really good reason for that). In every SQL-based RDBMS, naturally that's a keyword. In MS SQL Server I can escape it using [ and ]:

CREATE TABLE FOO (
    [FROM] VARCHAR(255)
)

MySQL uses backticks

CREATE TABLE FOO (
    `FROM` VARCHAR(255)
)

Similarly, Oracle and Postegres use double quotes.

Is there any major RDBMS that doesn't support quoting/escaping column names, one way or another?


texts:
In SSMS, if you follow the path [Database] > Security > Schemas and view any schema properties, you have a tab "permissions" that list all the permissions that every user have on that specific schema.

I would like to make a query that gives me the same output than that tab. I tried using sys.database_principals, database_permissions and sys_objects but I can't retrieve the permissions linked to schemas.


texts: Instead of insert trigger VS For insert Triggers in SQL server

texts:
Edited: My question is since there is an insert statement in the instead off insert trigger, then it should call insert trigger from inside the instead off insert trigger and which should avoid inserting row in the testtable. What my observation is that for insert trigger is firing but after instead off trigger which already has inserted the new row in the testable. For insert trigger is raising error but instead off trigger has already inserted a row in testtable. Why for insert trigger is not called from inside the instead off insert trigger?

I have two triggers on the table called TestTable. One is Instead of insert trigger and second is After insert trigger. First instead of insert trigger will fire and then for insert trigger.What it does that it inserts new row in TestTable and CopyTable, but not in AuditTable.

My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?

My table script

CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Age] [int] NULL,
    [DOB] [date] NULL,
    [Address] [nvarchar](50) NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

My SQL Query:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');

My instead of insert trigger is:

ALTER TRIGGER [dbo].[utInsteadoff]
   ON  [dbo].[TestTable]
   Instead of Insert
AS
    Declare @Name as nvarchar(50)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Insert into dbo.TestTable (Name,Age,DOB,Address) Select Name,Age,DOB,Address From inserted;
    Insert into dbo.CopyTable (Id,Name,Age,DOB,Address) Select Id,Name,Age,DOB,Address From inserted;

END

After insert Trigger is:

ALTER TRIGGER [dbo].[insertTrigger]
   ON  [dbo].[TestTable]
   AFTER Insert
AS
 Declare @Name as nvarchar(50)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
     Set @Name = (Select Name from inserted)

 If (@Name = 'Waleed')
    Raiserror('You Cannot Enter Waleed Name',10,1)
  Else
 Begin

    Insert into dbo.AuditTable (ID,Name,Age,DOB,Location)
    Select ID,Name,Age,DOB,Address from inserted
    -- Insert statements for trigger here
end
END


texts: Is it safe to change the Recovery Model of SSRS database ReportServer to SIMPLE?

texts:
I'm changing the recovery model of ReportServer database from FULL to SIMPLE.

Is there any reason why i would not want to do this? Besides the obvious answer of losing point-in-time recovery? (i am not doing transaction log backups on this database anyways.)


texts:
How to get the free temporary memory available in a server

For example:- I am running one SSIS package to upload data to tables. First it will move all the data to temporary table and then it will upload to actual Tables. So before executing this package is there any way to get the available remaining temporary memory size? including SQL Server ( Because there are some parallel jobs are running by SQL Server 2005 and using the temporary space).  So if I can get the remaining memory size I can decide whether I can run the package or not. Please help me.


texts: PSQL: create random data sets with the same statistic distrbution as another table

texts:
For test purposes, I want to create data sets (tables) where the statistic distribution of the values in the columns is the same as my actual data, but randomized.

Example : consider the following table ('ARTICLES' for a clothes shop):

| id | type    | colour | size | price |
----------------------------------------
| 1 | shirt    | yellow | M    | 14.99 |
| 2 | trousers | brown  | L    | 20.00 |
| 3 | shirt    | red    | L    | 14.99 |
| 4 | trousers | red    | L    | 20.00 |
| 5 | cap      | yellow | M    | 5.00  |
| 6 | cap      | brown  | S    | 5.00  |
| 7 | shirt    | red    | M    | 14.99 |
| 8 | trousers | red    | L    | 20.00 |

So, out of these 8 rows, I have the following distributions for each column:

type : 3 shirts, 3 trousers and 2 cap
colour : 4 red, 2 yellow, 2 brown
size : 1 S, 3 M, 4 L
price : 2 x 5.00 , 3 x 14.99 , 3 x 20.00

What I want is to create another table, with the same columns and the same number of rows, in which I have for each separate column the same statistic distribution of values, but assigned randomly and independently from each other.

Doing the stats for each column is pretty easy :

SELECT column_name, COUNT(1) AS number_of_rows
FROM ARTICLES GROUP BY column_name ;

I can also easily record these stats in a dedicated table :

CREATE TABLE articles_stats ( column_name varchar(255), value varchar(255), number_of_rows integer );

INSERT INTO articles_stats (column_name, value, number_of_rows)
SELECT 'type', type, COUNT(1) AS number_of_rows
    FROM ARTICLES GROUP BY type
UNION
SELECT 'colour', colour, COUNT(1) AS number_of_rows
    FROM ARTICLES GROUP BY colour
UNION
SELECT 'size', size, COUNT(1) AS number_of_rows
    FROM ARTICLES GROUP BY size
UNION
SELECT 'price', price::varchar(255), COUNT(1) AS number_of_rows
    FROM ARTICLES GROUP BY price ;

In this example, it would create the following articles_stats table :

| column_name | value    | number_of_rows |
-------------------------------------------
| type        | shirt    |        3       |
| type        | trousers |        3       |
| type        | cap      |        2       |
| colour      | red      |        4       |
| colour      | yellow   |        2       |
| colour      | brown    |        2       |
| size        |    S     |        1       |
| size        |    M     |        3       |
| size        |    L     |        4       |
| price       |    5.00  |        2       |
| price       |   14.99  |        3       |
| price       |   20.00  |        3       |

But how do I then create inserts into the target table (lets' call it 'RANDOM_ARTICLES') ?

PS: I have to do this many times so I hope to create a PSQL function for this.


texts:
I have a table where one column will have a few values many times (maybe 20 different values). Now I would like to use something like the set to improve performance and memory consumption. The problem is that I don't know which values I'm going to have so I can't  use the normal set as I have to define the values in the create statement.

What could I do to achieve this?

More specific i will have a table where i list schoolbooks, and each of these books is associated to a subject. Now the same subject will appear in multiple datarows. But as I don't know which subjects will exist I can't use the set.

Thanks in advance


texts:
I hope this is the correct forum but will ask anyway:

I have lots of Mesh data I want to store. Mesh is actually triangles. I hope it's not too of beginner question but wouldn't Geometry type of SQL Server be the best way to store that?

Specifically I'm interested in storing FBX file that are created in other programs sometimes in different formats such Autodesk Revit then converted to FBX. My other option is use file system which is risk and not managed.
Or using binary field but storing large binary data in a database rarely ends well I understand.

This question is not about how to actually load the data I know this could be a problem but what SQL datatype should I use.

I was planning to make the FBX files very small so that for example I can have lookup tables of types. Example: a door will always be a single FBX and then the table will have: FBXType FBXData 1 (door) 2 (furniture)

At the moment is just querying so I can build AssetBundles for Unity. Then I was thinking if it is geometry maybe I can use the smart indexes and functions like intersect that is available for geometry type.

The data will be changing that is the reason I want it in a database however it will probably be in deliveries (this one day of the month everything is updated) and probably new rows (will have another column IsDeleted) can even be a new table but not a must. If splitting by type my colleague says only few FBX files will be large for buildings skeleton but even this is not sure because we can split by floor. Most FBX files will be 5MB or less


texts: What data store should I use for storing and querying a billion small (~2kb) JSON documents?

texts:
I'm trying to figure out which database to use for storing and querying on ~1 billion JSON documents about the size of 2KB each. About 2 TB of data.

Nature of JSON documents:

3 types of documents (structure is loosely defined)
Nested, multi level, sparse

Things that I'm looking for are:

High throughput
Secondary indexes support
Queries over JSON

Nice to haves:

SQL like query interface

Things I'm looking at:

Cassandra
Storing JSON in Postgres
Riak
HBase

If more details I missed regarding usage or the data I missed that would help make the decision, please let me know


texts:
we are experiencing unresponsive Sql Server 2008 R2 Web Edition. The only way how to connect to the server is via DAC.

The dm_os_tasks return 539 tasks almost all SUSPENDED or PENDING. There is one task with blocking_session_id same as session_id. It's waiting for LCK_M_SCH_M, the others tasks are waiting for TEMPOBJ.

Both LCK_M_SCH_M and TEMPOBJ are weird. There is no scripts for changing/creating/droping schema. We are not using any temporary tables, only table variables and table-UDT as input parameters for several functions and stored procedures.

The task with LCK_M_SCH_M is inline-table-function with table-UDT (TblGuid).
The UDT TblGuid is table with single column:

CREATE TYPE [dbo].[TblGuid] AS TABLE(
    [Guid] [uniqueidentifier] NULL
)

The table-valued-function is unchanged for months and is called a lot. This behaviour became "out of nowhere".

If we restart the sql server service, all became ok. This state occures +- 10x in day.

I suspect the same session_id and blocking_session_id is invalid state and could not appears. Am I right?

EDIT:
The problematic function is:

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel]
(
    @IDsBattleTargets [dbo].[TblGuid] READONLY,
    @IDCurrentPlayer uniqueidentifier
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        DISTINCT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM @IDsBattleTargets AS IDS
    INNER JOIN [dbo].[tblBattleTarget] AS BT
        ON BT.BattleTargetID = IDS.Guid
    INNER JOIN [dbo].[tblUnit] AS U
        ON U.IDBattleTarget = IDS.Guid
    LEFT JOIN [cache].[tblHeroSharingWithPlayerClan] AS HSWPC
        ON HSWPC.IDHero = IDS.Guid
            AND HSWPC.IDPlayer = @IDCurrentPlayer
    LEFT JOIN [cache].[tblHeroSharingWithAlliance] AS HSWPA
        ON HSWPA.IDHero = IDS.Guid
            AND HSWPA.IDPlayer = @IDCurrentPlayer
    WHERE U.Amount > 0
        AND (BT.IDBattleTargetOwner = @IDCurrentPlayer
            OR HSWPA.IDPlayer = @IDCurrentPlayer
            OR HSWPC.IDPlayer = @IDCurrentPlayer)
)

The function is called from another function or from C# (.NET 4.0) via SqlConnection,SqlCommand and SqlParameter.

SOLUTION:
After advices I decided for change TVP to something another. I choosed the varbinary(max) where one Guid/uniqueidentifier value has 16 bytes. I created sql-clr function with varbinary(max) input and table output Guid uniqueidentifier. I won't use spliting text because it's not much efficient for my purpose (this function is called very often). After the 30 hours in production (the problem occured only on one server in production, and we was not able reproduce them in testing environment) there was no problem. Usualy the problem occures several times a day. This will be propably solution.

In the final the function looks:

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel]
(
    @IDsBattleTargets varbinay(max),
    @IDCurrentPlayer uniqueidentifier
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        DISTINCT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM [dbo].[GetGuidsFromBinary](@IDsBattleTargets) AS IDS
    INNER JOIN [dbo].[tblBattleTarget] AS BT
        ON BT.BattleTargetID = IDS.Guid
    INNER JOIN [dbo].[tblUnit] AS U
        ON U.IDBattleTarget = IDS.Guid
    LEFT JOIN [cache].[tblHeroSharingWithPlayerClan] AS HSWPC
        ON HSWPC.IDHero = IDS.Guid
            AND HSWPC.IDPlayer = @IDCurrentPlayer
    LEFT JOIN [cache].[tblHeroSharingWithAlliance] AS HSWPA
        ON HSWPA.IDHero = IDS.Guid
            AND HSWPA.IDPlayer = @IDCurrentPlayer
    WHERE U.Amount > 0
        AND (BT.IDBattleTargetOwner = @IDCurrentPlayer
            OR HSWPA.IDPlayer = @IDCurrentPlayer
            OR HSWPC.IDPlayer = @IDCurrentPlayer)
)

If someone will have similar issue, please contact me. I would like to more investigate this behaviour.


texts:
What is the equivalent of SQL Server's varchar(max) data type in ASE?
By ASE I mean what used to be known as Sybase ASE, which is now owned by SAP.
I'm attempting to execute a piece of dynamically constructed T-SQL that is longer than 8,000 chars.
If I declare a variable with varchar(max), ASE chokes with:
Incorrect syntax near the keyword 'max'.
My next thought was to try the text datatype.  But alas:
You specified an incorrect datatype for the variable containing the 'execute immediate' command string.
A minimal, complete, and verifiable example:
DECLARE @cmd varchar(max) --change this to varchar(4000) and it works
SET @cmd = 'SELECT 1;'
EXEC (@cmd);
GO

Changing the variable declaration to text or whatever will suit your purposes.  I need to execute a dynamic command that is probably 50,000 characters long.
Alternately, I could use a cursor, but I am unfamiliar with how cursors work on ASE.
Note that in SQL Anywhere, you can define an equivalent to the varchar(max) datatype as declare @cmd long varchar;, however that won't work in ASE.


texts:
I've posted this message on Toad World looking for help but no one has answered.  So I'm trying here.

I repeatedly get the same error while using any of the Compare options when connecting to a remote host.  In this specific case it is a HostGator server.  I did create a support ticket with them, they refereed me to my unanswered Toad World forum post.

The error is:  Keyword not supported: 'btkkw;pooling'.

I am using Toad for MySQL 7.5.0.966.  This has been happening for a the past month or more.  I noticed it while running 7.3.x, updating hasn't helped.  Deleted the remote profile but that didn't help either.

Otherwise I can connect to the remote server with no problems, view the db, make changes and export without problems.

Can somebody please help me with this problem?  Maybe make some suggestions?


texts:
I am a Java developer and currently I use PostgreSQL as the RDBMS. However, I would also like to learn Oracle. So can I download a latest version of Oracle (11g I guess) for free?

I found this link: Oracle Database 11g Release 2 Downloads, and it seems that it is downloadable. However, I know that Oracle Server is proprietary. So how does that work?

Also, will there be PL/SQL along with the download of Oracle 11g?


texts:
We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN.  IOPS increased twenty-fold during the period when the problem was occuring.  The LUN in question was the one that tempdb uses.  This is all according to our network/server admins (I have no network/SAN/Server expertise).

Everyone on the team was investigating the problem, until one team member found an expensive query in activity monitor that look suspicious.  He inspected the table and found and index that was a duplicate.  Actually, it was technically an overlap, since it had one additional included column over and above from the original index.

The overlapping index had been in production for about three weeks with no problems.

The index was dropped in production, and the performance problems stopped almost immediately after  We were not all in sync monitoring and trying fixes (never mind that this isn't a good practice).  So it's possible it was a coincidence, but no other changes were made in the same timeframe, and we're 99% sure this index was somehow the cause.

I realize a superfluous index can cause performance problems, but I have never heard of a case where the problem would be that pronounced.  Adding the index back also caused no problems (I know--also not a good practice in production).

Any idea of what the root cause of the problem could have been?  The duplication?  A corrupted index (if such a think exists)?

Any online sources as references would be deeply appreciated as well.


texts: Assign a value to a field of a composite type (a row in an array of rows)?

texts:
I am currently writing a function that works with an array of rows. I need to write a value to a particular column of a particular row in the array. The following code should encapsulate the problem I'm having.

drop type if exists test_type;

create type test_type as (
  some_int int
);

create or replace function test()
returns void as
$$
declare
x test_type[];
y test_type;
begin
  x[1] = row(1);
  y = row(1);

  /* DOESN'T WORK */
  /* ERROR: syntax error at or near "." */
  /* x[1].some_int = 2; */

  /* HOWEVER, THIS WORKS */
  y = x[1];
  y.some_int = 2;
  x[1] = y;

  raise notice '%', x[1].some_int;
end;
$$
language plpgsql;

As you can see, referencing x[1].some_int is perfectly legal in most contexts but not for assignment. The workaround accomplishes the goal; however, it involves two additional copies via the assignment operator.  Is there any explanation for why this is an error or how to work around it and accomplish this task without creating extra row copies?


texts: How do I get the row as column and one of the column as rows under the first column data

texts:
I have table like below:

Tname         Scode
-----         -----
SVC           code1
SVC           code2
SVC           code3
AKV           code1
AKV           code12
AKV           code3
AKS           code3
AKS           code2
AKS           code1

I would want a result like below:

SVC      AKV         AKS
----     -----       ----
code1    code1       code3
code2    code12      code2
code3    code3       code1


texts: How to grant access to SQL server agent to read network location file?

texts:
Description: Cannot open the datafile "Z:\Folder\File.txt".  End Error
Error Code: 0xC004701A


texts: Creating unique constraint to be validated from input

texts:
I have this table name tbl with this table definition:

create table tbl (
    id serial primary key not null,
    counter integer not null default 1,
    ref_id integer not null,
    date_booked date not null
);

With these insert statement(s):

insert into tbl(counter, ref_id, date_booked) values (1, 1, '2014-07-05');
insert into tbl(counter, ref_id, date_booked) values (2, 2, '2014-07-10');
insert into tbl(counter, ref_id, date_booked) values (3, 3, '2014-07-15');
-- this should fail
insert into tbl(counter, ref_id, date_booked) values (3, 4, '2014-07-20');

I am having a problem of creating a unique constraint/index in such way tbl.counter should be unique within the month of the inputted tbl.date_booked. From the insert statements above, the 4th insert statement should fail since if violates the unique validation. The problem is I don't know how to create such validation. Though I have a bit of an idea about constraints, index, etc. it's just am confused which to choose to make this work.


texts: SqlPackager.exe what is SqlPermissionStatement?

texts:
I am using SqlPackager to determine the changes between two databases as  described here.

What I cannot figure out is what the SqlPermissionStatement is referring to? Is it a byproduct of the missing user?

In other words I don't know how to make the change to my target database to make this disappear from the deployment report.


texts: Can I GRANT SELECT on all schemas in a database?

texts:
I need to create role users of which can only read (select) data.
I can do it for a particular schema.
But I need it to work with all existing and future schemas (existing at least). How can I do it?
I've tried
GRANT select ON DATABASE my_db TO my_role;

But it says that I can't:

0LP01 invalid_grant_operation

p.s: I need to grant rights on select to all schemas in db, I'm just trying to do it with db in query as a option, but if you know how to do give role permission on select for the hole db, I'll be glad to see that too


texts:
I'm trying to setup a query, so that it will compare two dates from two different tables, and if they are equal, then the query exits. If they are not equal, then the query will continue and insert some stuff. I can't figure out how to get it to do what I want however.

SELECT TOP(1) @dateA=a.someDate
FROM a
ORDER BY DESC;
SELECT TOP(1) @dateB=b.someDate
FROM b
ORDER BY DESC;

CASE WHEN @dateA=@dateB THEN raiseerror('dates equal',20,-1) with log;

Insert statements;

Any help would be super appreciated.


texts: What exactly does "No Join Predicate" mean in SQL Server?

texts:
MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".

But unfortunately it does not seem to be as easy as that.

For example, very simple situation:

create table #temp1(i int);
create table #temp2(i int);
Select * from #temp1, #temp2 option (recompile);

There is no data in the tables, there is no warning either, although it obviously has no join predicate.

If I take a look at documentation for SQL Server 2005 (the same link, just other server version), there is an extra sentence: "This event is produced only if both sides of the join return more than one row." This would make perfect sense in the previous situation. There is no data, so both sides returns 0 rows and no warning. Insert rows, get warning. Ok, cool.

But for the next confusing situation, I insert the same values in both tables:

Insert into #temp1 (i) values (1)
Insert into #temp1 (i) values (1)
Insert into #temp2 (i) values (1)
Insert into #temp2 (i) values (1)

And I get:

-- no warning:
Select * from #temp1 t1
    inner join #temp2 t2 on t1.i = t2.i
option (recompile)
-- has warning:
Select * from #temp1 t1
    inner join (select 1 i union all select 1) t2 on t1.i = t2.i
option (recompile)

Why is this so?

Note: some scripts I used to detect these bad queries on my server.

of course, execution plan of procedures
used default server trace to find warnings

Declare @trace nvarchar(500);
Select @trace = cast(value as nvarchar(500))
From sys.fn_trace_getinfo(Null)
Where traceid = 1 and property = 2;

Select t.StartTime, te.name, *
From sys.fn_trace_gettable(@trace, 1) t
    Inner join sys.trace_events te on t.EventClass = te.trace_event_id
    where EventClass = 80
order by t.StartTime desc

execution plan cache, to find those plans with warnings (like this one)

WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    Cast('<?SQL ' + st.text + ' ?>' as xml) sql_text,
    pl.query_plan,
    ps.execution_count,
    ps.last_execution_time,
    ps.last_elapsed_time,
    ps.last_logical_reads,
    ps.last_logical_writes
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
WHERE pl.query_plan.value('(//Warnings/@NoJoinPredicate)[1]', 'bit') = 1
Order By last_execution_time desc
OPTION (RECOMPILE);


texts: How to get latest 2 records of each group

texts:
So, I have table similar to:

sn color value
1  red   4
2  red   8
3  green 5
4  red   2
5  green 4
6  green 3

Now I need the latest 2 rows for each color, eg:

2  red   8
4  red   2
5  green 4
6  green 3

How to do it, other than using separate query for each color?

Thanks


texts: Logminer archived redo logs in other database

texts: Can I move my archived redo logs to other database and apply logminer there to analyze them?


texts: Oracle MV requires object type to be defined as FINAL?

texts:
I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY:

The MV would do a COMPLETE refresh on a schedule — on a local table.

Some info about ST_GEOMETRY:

The ST_Geometry data type implements the SQL 3 specification of
user-defined data types (UDTs), allowing you to create columns capable
of storing spatial data such as the location of a landmark, a street,
or a parcel of land. It provides International Organization for
Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant
structured query language (SQL) access to the geodatabase and
database. This storage extends the capabilities of the database by
providing storage for objects (points, lines, and polygons) that
represent geographic features. It was designed to make efficient use
of database resources; to be compatible with database features such as
replication and partitioning; and to provide rapid access to spatial
data.
ST_Geometry itself is an abstract, noninstantiated superclass.
However, its subclasses can be instantiated. An instantiated data type
is one that can be defined as a table column and have values of its
type inserted into it.

The SQL definition of the user-defined datatype can be found here: db<>fiddle.
CREATE OR REPLACE TYPE SDE."ST_GEOMETRY" (
...
) NOT final

(Although, I'm guessing most of the logic is stored in the EXTPROC.)

MV object types must be FINAL:
There is a known issue when trying to create an MV on the ST_GEOMETRY user-defined datatype (ORA-30373):

Creating an Oracle materialized view for a table containing an
ST_Geometry attribute returns the following error:
"ORA-30373: object data types are not supported in this context".

Code:
SQL> CREATE MATERIALIZED VIEW parcel_view
  2  AS SELECT * FROM parcel@remote_server;

CREATE MATERIALIZED VIEW parcel_view
*
ERROR at line 1:
ORA-30373: object data types are not supported in this context

Cause:
Oracle's Advanced Replication functionality requires that all
object types be defined as FINAL to participate within a materialized
view.

The reason that the ST_Geometry cannot be defined as FINAL is because
the type contains subtypes used for type inheritance. Oracle does not
allow a type with subtypes to be defined as FINAL.
Esri is currently working with Oracle to address this issue and
limitation. The following Oracle TAR file is available for reference:
"6482996.992 - ORA-30373 MATERIALIZE VIEW UNABLE TO REPLICATE A TYPE
WHICH CONTAINS SUBTYPES. Enhancement Request (ER) 6370112".

Note: I've contacted ESRI support and they say there hasn't been any progress with Oracle regarding the enhancement request.

Question:
Is there a way to workaround this issue?

I ask because ESRI has a reputation of not being great with Oracle. So there is a
chance that they have misunderstood the issue or haven't gotten creative enough.

For example, what about the ONLY keyword?
9.9 Materialized View Support for Objects

For both object-relational and object materialized views that are
based on an object table, if the type of the master object table
is not FINAL, the FROM clause in the materialized view definition
query must include the ONLY keyword.
For example:
CREATE MATERIALIZED
VIEW customer OF cust_objtyp AS
SELECT CustNo FROM ONLY HR.Customer_objtab@dbs1;

Otherwise, the FROM clause must omit the ONLY keyword.

Notes:

Unfortunately, that blurb is about object tables, not about object columns (I believe the ST_GEOMETRY user-defined datatype would be considered
an "object column").

But I wonder if the concept of the ONLY keyword could be used to help with
the FINAL problem with the ST_GEOMETRY object column. Any ideas?


texts:
I have an architecture of cascading replication with 3 postgresql db

Because the master is becoming too big (~ 170Gig), I wanted to run a "cleaning" job over the week-end that would do multiple DELETE operations on Millions of rows by batch and VACUUM table right after.
Unfortunately my cleaning script could not complete because disk of DB2 got filled (pg_xlogs?)

2019-06-21 17:41:08.770 UTC [1136] FATAL:  could not extend file "base/34163166/44033600.20": No space left on device
2019-06-21 17:41:08.770 UTC [1136] HINT:  Check free disk space.                                                                                2019-06-21 17:41:08.770 UTC [1136] CONTEXT:  xlog redo at 662/6A087C30 for Heap/INSERT+INIT: off 1                                                         2019-06-21 17:41:09.188 UTC [13036] FATAL:  could not write to file "pg_xlog/xlogtemp.13036": No space left on device

My disks size are all the same (db1 is bigger because the server has other files on it):

DB1: /dev/sda1       496G  266G  210G  56% /
DB2: /dev/sda1       496G  177G  299G  38% /
DB3 /dev/sda1       496G  177G  299G  38% /

size of the main table is the same on master and replicas

Size              | 173 GB

Databases are all running on different VMs. The commands I ran are simple, SELECT count(*) FROM TABLE1 WHERE X, then DELETE FROM TABLE1 WHERE X, then VACUUM TABLE1. And this script is iterating by batch of 5 days on a timestamp column.
Hence my VACUUM command is split per table. I've been doing backups before starting the script (with a pg_backup that is then sending the back up files to another backup server)
I am actually not sure why the disk of db2 got filled :/ , would you have any idea ? I need to run my script so I think I might need to stop the replication to run it.
Thank you for your help, cheers


texts: Check if a primary key value is referred in any other table

texts:
I am using oracle database.

Is there a way I can check if the primary key value in one table is being used as a foreign key value (have any associations) in any other table?

Example: if I have the Student_id as primary key in STUDENT table. And there are other tables in the schema like CLASS, MARKS, SPORTS etc.

I have student_id=100 with me. I need to find out all the references of this student_id value '100' in the entire schema wherever it is linked as a foreign key from the parent table STUDENT.


texts: Mysql Procedure error while executing it

texts:
I am creating procedure which selects member_id in courser.member_id having more that 100000 records. i had declared one variable i which stores one member id at time update it with next query.

can anyone resolve this..? procedure is as follows:-

    DELIMITER //
DROP PROCEDURE IF EXISTS Mock_test_1;
CREATE PROCEDURE Mock_test_1()
   BEGIN
DECLARE i int unsigned;
DECLARE curs1 CURSOR FOR select member_id from client_master;
open curs1;
read_loop: LOOP
      FETCH curs1 INTO i;
update client_master set
         `mobileno`='0000001231',
         `email`=concat('MOCK',member_id,'@mock.com'), `password`=md5('MOCK'),
         `personaladdress` = concat('MOCK',member_id),
         businessaddress = concat('MOCK',member_id),
         imeino = '00000000000000', panno= 'AAAAA66666' ,aadharno = '11111111111111';
insert into client_master_test select * from client_master;
   END //
 DELIMITER ;

can anyone suggest me blocker for this..?


texts:
I have a multiple databases i.e (database1,database2) and i need to export a table from database1 to database2 . Here are the commands i tried

expdp hr/oracle@oracle tables=emp directory = ora dumpfile=emp.dmp

This command will export the the emp.dmp file to "ora" directory but i need to import this dump file to another database but the "ora" directory is not accessible to the database2 . Do we have any other method to export the database dump .


texts: MySQL: sysbench test - InnoDB vs Memory tables

texts:
I've done some tests in order to investigate performance issue on the new HP Gen8 server (Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz)

I've created two tables, first one is using InnoDB storage engine and the second one is in Memory - heap table.

System details:

sysbench-0.4.12-5.el6.x86_64
CentOS release 6.4 (Final)

Prepare stage:

InnoDB

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-db=sbtest1 prepare

Memory (heap)

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=heap --oltp-table-size=1000000 --mysql-db=sbtest2 prepare

Testing stage:

Sysbench – read only test – single table with 1 mln rows - data size 559MB (527MB data + 31MB indexes)

InnoDB

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --num-threads=128 --max-requests=100000 --oltp-read-only run

Total time: 16.3648s, TPS (transactions per second): 6111.40

Memory (heap)

# sysbench --db-driver=mysql --test=oltp --mysql-table-engine=heap --mysql-engine-trx=no --num-threads=128 --max-requests=100000 --oltp-read-only run

This test is running much longer and I had to stop it as the load on the server was very high - even if this is in memory table!?.


texts: How can I connect the mysql enterprise monitor to its repository database with a socket?

texts:
By default the Oracle-MySQL-Monitor MEM (used to monitor MySQL Enterprise Instances) connects to its own repository database through localhost.

Since we want to reduce the bindings of that database to just one specific IP, localhost is not available as connecting point.

The idea was to use the local linux-socket instead.

So far we found no way to configure/install MEM using the socket. The usual "DB@localhost" tries to connect to 127.0.0.1 instead.

MEM-Version: 8.0.1 - if that matters.