texts: Forbid user to query internal SQL server information

texts:
I'm trying to forbid database user to get SQL server internal information.

Script that creates DB user:

CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: sys TO [testuser]
DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: INFORMATION_SCHEMA TO [testuser]
GRANT SELECT on SCHEMA::dbo to [testuser]
DENY INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA :: dbo TO [testuser]

The problem is that user is still able to run queries like these:

EXECUTE AS USER   = 'testuser';
GO

SELECT DB_NAME() , @@VERSION
SELECT ConnectionProperty('net_transport')
SELECT * FROM sys.login_token

Do you have any ideas?


texts: Giving all permissions to all users for a database in SQL Server

texts: Since the database is created under one user's log in, other users using this computer cannot access this particular database. How can I grant the same privileges to all users who log in to this computer for this database? Step by step instructions through SSMS or by a script will be appreciated greatly.


texts: Better ways to update columns of a table than using Merge Statement

texts:
I have a table with this structure

Table1(Cust_No    Varchar2(50),
       First_Name Varchar2(50),
       Last_Name  Varchar2(50),
       Error_Code Number(1)   ,
       Error_Desc Varchar2(50))

there is another table with this structure

Table2(Cust_No            Varchar2(50),
       Customer_FirstName Varchar2(50),
       Customer_LastName  Varchar2(50)
       )

Imagine that Column Cust_No from Table1 has data.By this I mean there are some data for Cust_No in this field. I need to Update other columns of the table based on this column and column Cust_No of Table2 and find First_Name and Last_Name for each Customer Number. What I have written so far is this :

-- FIND CUSTOMER'S FIRST AND LAST NAME

   MERGE INTO (SELECT * FROM REQUEST R WHERE R.IS_CHECKED IS NULL) R
   USING VMI_DIMCUSTOMER V
   ON (V.CUSTOMER_NUM = R.CUST_NO)
    WHEN MATCHED THEN
    UPDATE
      SET R.FIRST_NAME = V.CUST_FIRST_NAME,
          R.LAST_NAME  = V.CUST_LAST_NAME,
          R.ERROR_CODE = 0,
          R.IS_CHECKED = 1;

            COMMIT;

   -- CUSTOMERS NOT FOUND

  UPDATE /*+ PARALLEL(4) */ REQUEST T
     SET T.FIRST_NAME = '--',
         T.LAST_NAME  = '--',
         T.ERROR_CODE = 2,
         T.ERROR_DESC = 'Customer Not Found',
         T.IS_CHECKED = 0
      WHERE T.FIRST_NAME IS NULL;

        COMMIT;

What I'm doing in this query is that First of all, I'm using a Merge statement to find customer's first and last name and update column Is_Checked to 1 when Customers have first and last name. In the second part I'm updating columns that does not match to any records in Table2.

I want to know wether there are better ways to write this query in terms of performance.


texts: Perl vs Ksh for Unix Database Administration

texts:
I realize this is a somewhat more subjective question, but I am looking to the community for guidance.

Our company is fairly new to having DBAs. We used to use DB2 for i on the IBM's iSeries servers, so there really wasn't a need for a DBA (at least operationally) as the OS pretty much managed everything for us. Within the last few years we started to move to DB2 LUW on AIX. Thus a need for DBAs was born. Three of us were interested in making the career change from developers to DBAs (of which I am one of them).

Because we are on AIX, we defaulted to using ksh as our scripting language for database management functions (creation, deployment, operations, etc.). I have found ksh to be quite powerful, yet some of the functions I need to perform get rather complex. An example of this is my question over on Unix and Linux StackExchange.

Based on that question, as well as reading blogs by other DB2 administrators, I see a general suggestion that Perl is better suited for administration scripting than ksh is.

I realize that the Unix/Linux forum would have their own thoughts on such a thing, but I wish to approach this from a database administration viewpoint.

Regardless of DBMS, which scripting language seems better suited for administration on the Unix/Linux platform? Perl? Ksh? (or perhaps other?). What strengths and weaknesses in your use of scripting languages did you encounter as a DBA, and why did you pick the language you did?

What I don't want is a highly subjective flame war on "My language is better than your language!" kind of stuff, like I used to see in the development community regarding say Java vs Ruby, etc. I am looking for honest, as objective-as-possible, information regarding what best suits the needs of database administrators.


texts: Missing rows in Visual DBA, exist when queried specifically

texts:
I have an interesting issue on which Google has failed me.
Background
I am working with medical records managed with Ingres SQL. The other day I wrote a simple query in Excel to pull all patient rows that have died in a set date range and output them as a report. This query works. I happened to notice (because of my date range) that I was missing a recent row. Upon further inspection I realized I am missing more rows, not just in this report.
Details of issue
When I query specifically for these missing rows (by selecting their specific patient ID from its column) they exist. When looking at Visual DBA at the table in which they reside and were just queried from successfully, they do not appear.
Similarly, these missing rows do not appear in the raw data pulled by my Excel query, whereas other rows meeting the same criteria do appear. I have looked for inconsistencies between rows that are showing up in Visual DBA and rows that are not. They are all consistent (null values, format, etc) and have differences only in value, not type. ie, last name is 'Jones' instead of 'Smith'.
My question
Why are rows missing from Ingres Visual DBA (table 'x' let's say) but when queried specifically in Ingres Visual SQL from table 'x' they appear?
This is a serious issue for us as rows are missing from automatically generated medical reports and vital information could be missed.
Additional Details
When in the Object Manager in Visual DBA I navigate to my table in question (say d1_17, which is sorted by ascending values of d1_17.xpid) xpid = '27245' is not there. When I query specifically in Visual SQL for d1_17.xpid = '27245' it outputs the row correctly.
They are connected to same database. Excel and Visual DBA are also in sync (both missing the same rows). The discrepancy arises when I query specifically d1_17.xpid = '27245' in Visual SQL and the row prints, and yet, when browsing in object manager, in d1_17 in Visual DBA the row with xpid = '27245' isn't there.
Every value in d1_17.xpid is non-null. Other columns may have null values, but every row is a patient and every patient has an xpid (patient ID).
While we are admins on the network for our database we unfortunately did not create it, our software team for our EHR GUI did that part as well.


texts:
I have the two tables courses AND something_about_courses:

courses

course_code
course_session
course_type

something_about_courses

course_code
course_session
some_description

To ensure that I fetch some_description only for courses that actually exist, I use:

SELECT some_description
  FROM something_about_courses A
  JOIN courses B
    ON (A.course_code = B.course_code
   AND A.course_session = B.course_session)

Okay, everything is good and the system is put in to use. Now, two year after the system is in use, the client wants to compare not just with {course_code, course_session} but also with course_type.

So I change the structure of something_about_courses to:

course_code
course_session
course_type
some_description

and I change to query to:

SELECT some_description
  FROM something_about_courses A
  JOIN courses B
    ON (A.course_code = B.course_code
   AND A.course_session = B.course_session
   AND A.course_type = B.course_type)  <<<<<<< AND clause added to JOIN

The problem I now face is that all old data in something_about_courses doesn't JOIN successfully since they have a NULL for course_type as it was a newly added column.

In other words, I have working queries but a disconnect in the data sets.

Solutions I have thought of:

Date based segmentation: use the old query for before the feature change and the new query for after. However, this isn't elegant and has a huge amount of maintenance overhead.
Manual adjustment of the old data: there is way too much of this to adjust it to be compatible with the new query to make this feasible.

Neither of the above solutions are ideal. I was wondering if there is anything I can do at the query level to JOIN on 2 or 3 columns based on whether something_about_courses.course_type IS NULL.


texts: From a set of values, how do I find the values not stored in a table's column?

texts:
I have a table which will potentially store hundreds of thousands of integers

desc id_key_table;

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id_key         | int(16)      | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

From a program, I have a large set of integers.  I'd like to see which of these integers are NOT in the above id_key column.

So far I've come up with the following approaches:

1) Iterate through each integer and perform a:

select count(*) count from id_key_table where id_key = :id_key

When count is 0 the id_key is missing from the table.

This seems like a horrible, horrible way to do it.

2) Create a temporary table, insert each of the values into the temporary table, and perform a JOIN on the two tables.

create temporary table id_key_table_temp (id_key int(16) primary key );

insert into id_key_table_temp values (1),(2),(3),...,(500),(501);

select temp.id_key
from id_key_table_temp temp left join id_key_table as main
         on temp.id_key = main.id_key
where main.killID is null;

drop table id_key_table_temp;

This seems like the best approach, however, I'm sure there is a far better approach I haven't thought of yet. I'd prefer to not have to create a temporary table and use one query to determine which integers are missing.

Is there a proper query for this type of search?

(MySQL)


texts: How to update MySQL with CentOS 6 in most unintrusive way - in regard to perl and PHP packages?

texts:
For my few hobby projects (web games + forums) I have been using CentOS 5 (then 6) with Drupal and PostgreSQL plus few custom PHP and Perl scripts written by mysef.

Since PostgreSQL version delivered with CentOS package has been a bit dated, I always used the PGDG packages:

# rpm -qa | grep -i pgdg

pgdg-centos93-9.3-1.noarch
postgresql93-9.3.5-1PGDG.rhel6.i686
postgresql93-libs-9.3.5-1PGDG.rhel6.i686
postgresql93-server-9.3.5-1PGDG.rhel6.i686

As a web developer this has been a very pleasant experience, since (by some great magic) all the other CentOS packages (like php-pgsql and perl-DBD-Pg) just worked with the PGDG packages.

Now I have decided to switch to WordPress for my new projects and am forced to switch the database too:

I have to use MySQL or MariaDB with CentOS 6.5.

So my question is: if anybody can recommend a similarly comfortable package repository for MySQL/MariaDB - which wouldn't mess up any other CentOS packages and which would update itself (with yum update)?

And please do not suggest something like Fedora or EPEL repositories, because other than for MySQL/MariaDB I would like not to add any additional packages to have my server as "stable" as possible.


texts:
What are the recommended ZFS tunings for PostgreSQL 11 (with TimescaleDB 1.6 extension) running on striped mirrored vdevs of SATA3 SSDs?

I am considering:

ashift=12
atime=off
relatime=on (or off?)

But am unsure about the following:

xattr=sa
logbias=throughput
redundant_metadata=most

Data compression is pretty important, so I am thinking of keeping the default setting of recordsize=128K instead of changing it to 8K which will likely cause data compression to be much less effective.

Any advice greatly appreciated!


texts:
I'm trying to access an Oracle 12c EM and when I try to login it shows me a load error box with the errors that I expose below, which could be happening? Is there a possibility to reconfigure EM Express? If possible, I would like to know where the logs are, thanks.

ORA-00979: not a GROUP BY expression
ORA-06512: at "SYS.PRVT_AWR_VIEWER", line 9171
ORA-06512: at "SYS.PRVT_AWR_VIEWER", line 13196
ORA-06512: at "SYS.PRVTEMX_DBHOME", line 876
ORA-06512: at "SYS.PRVTEMX_DBHOME", line 144
ORA-06512: at "SYS.WRI$_REPT_DBHOME", line 91
ORA-06512: at "SYS.DBMS_REPORT", line 898
ORA-06512: at line 1


texts: Check if master is up from slave server(replication) postgresql

texts:
I want to check whether my master server is up by querying from slave server ?
Is there any view or command ?


texts: 'bsondump --pretty --type=debug' use case and output explanation

texts:
sample output with type debug

bsondump --pretty --type=debug audit.bson.2017-03-10T10-38-23
--- new object ---
        size : 236
                atype
                        type:    2 size: 28
                ts
                        type:    9 size: 12
                local
                        type:    3 size: 37
                        --- new object ---
                                size : 30
                                        ip
                                                type:    2 size: 15
                                        port
                                                type:   16 size: 10
                remote
                        type:    3 size: 38
                        --- new object ---
                                size : 30
                                        ip
                                                type:    2 size: 15
                                        port
                                                type:   16 size: 10
                users
                        type:    4 size: 53
                        --- new object ---
                                size : 46
                                        0
                                                type:    3 size: 41
                                                --- new object ---
                                                        size : 38
                                                                user
                                                                        type:    2 size: 19
                                                                db
                                                                        type:    2 size: 14
                roles
                        type:    4 size: 12
                        --- new object ---
                                size : 5
                param
                        type:    3 size: 39
                        --- new object ---
                                size : 32
                                        ns
                                                type:    2 size: 27
                result
                        type:   16 size: 12
--- new object ---

What's --- new object ---? It seems not to be a JSON document.
What's type and the number? Is size characters or other measurement?

Why this output is useful? What's the usecase for DBA or App developer?


texts: UDF in MS SQL Server returning a query starting with "with" cause

texts:
This is a perfectly valid SQL Query and works nicely (for e.g.) in Microsoft SQL Server 2014:
with t as (select 'a' as attr) select * from t

it returns a, surely one can simplify it and drop the with part, but that's not the point, the point is to find out a smallest example that illustrates the need/problem. I can pack part of it as a UDF:
CREATE PROCEDURE x(@__employee_id uniqueidentifier)
AS
RETURN (select 'a' as attr);

How can I pack a query that has with block? My aim is to achieve something like:
CREATE PROCEDURE x (@__employee_id uniqueidentifier)
AS
RETURN (
    with t as (select 'a' as attr) select * from t
);

however sql server does not like it, firing an error:
Msg 156, Level 15, State 1, Procedure x, Line 4 [Batch Start Line 0]
Incorrect syntax near the keyword 'with'.


texts: SMS Suggesting covering all columns in an index?

texts:
I'm still a bit newer to indexes and database works, so I may very well be missing something obvious.

SMS suggested adding a covered index to one of my tables that Indexes on the column I believe makes perfect sense. However, it also recommended including (covering) every other column in the table. To me this seems like it is moving the entire table into the index. Which I believe is probably a bad idea.Is SMS potentially misleading me in this case?

More generally is there any reason to cover every single column in a table?

SELECT  *
    FROM    SlideshowImages SI
    JOIN    Files F ON F.PK_FileID = SI.FK_FileID
    WHERE   F.FK_ModuleID   = 81
    AND     F.ParentType    = 2
    AND     F.FK_ItemID     = @SlideshowID
    ORDER BY F.[Order] ASC

Suggested Index by SMS - All 10 columns of the SlideshowImages table:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SlideshowImages] ([FK_FileID])
INCLUDE ([PK_SlideshowImageID],[Title],[Caption],[IsLink],[LinkAddress],[StartDate],[EndDate],[AltText],[OpenInNewWindow])


texts: What sort of database setup is used for an App Store or Google Play Store?

texts:
I'm trying to build a desktop App Store, and it's just a small project for demonstration purposes, I'll be using java to create the application. In essence it's just a mock App Store that stores information about customer & applications. Note: We will only be working with sample data, so no new data will be added.

Now there is the option to use the JDBC but we're a team of four people, and there is a question of how other team members will have access to the data.

The second option I have in my mind is to connect to an online MySQL database, which would require installing the MySQL connector.

There is the option of using an embedded database, so the database will move with the application, in a sense, at least that's what I think.

I'm completely new to the database scenario, and I would really much appreciate any rational guidance for what database setup I should consider.

Many Thanks


texts: How to select all Mondays (or any day combo like MTW, WTTh) between two given dates in MySQL?

texts:
What I see over the internet is just counting how many days occur whenever you input the day of the week you need. Like between May 1 to May 31, how many mondays are there in it. What I need is to display the dates where it is monday between two given dates. I don't have any code for this now since I don't really know where to start as I've scanned already the MySQL docu but I haven't seen anything useful besides

WEEKDAY('1998-02-03 22:23:00')

or

DAYOFWEEK('1998-02-03')

which shows the day index of a certain date. (but this time, of course, I don't know which dates fall on Mondays., i need the reverse thing)

I need is when I need mondays and tuesdays in february, it will return the following:

2015-02-02
2015-02-03
2015-02-09
2015-02-10
2015-02-16
2015-02-17
2015-02-23
2015-02-24

I dont have any code to display since my progresses in mysql on our proj isn't directly related to this one I'm needing. This is another independent mysql query of our proj I'm trying to get done..


texts: Optimizing a "categorized" search table using trigrams

texts:
I've been reading through this excellent answer to understand how pg_trgm works a bit, but I'm still unclear on the most efficient way of solving this query (efficient in terms of speed of search):

I have a table search that I run trigram searches on that looks like this:

Column      |  Type   | Modifiers
------------+---------+-----------
id          | bpchar  | collate C
user_id     | integer |
type        | text    |
search_on   | text    | collate C
data        | json    |
Indexes:
 "index_search_id" UNIQUE, btree (id)
 "index_search_search_on" gist (search_on gist_trgm_ops)
 "index_search_type" btree (type)
 "index_search_user_id" btree (user_id)

In this scenario, user_id is NULLable and type is also NULLable. The queries I'd run amount to these possibilities:

Search for rows (WHERE user_id = 123 OR user_id IS NULL) AND search_on % 'mystring'
Search for rows (WHERE user_id = 123 OR user_id IS NULL) AND type='my-type' AND search_on % 'mystring'

In plain words, I want all rows that have my user_id or NULL user_id, optionally are categorized by type, and match the term being passed in.

Right now I just have individual indexes on the 3 columns (as shown above) that can change based on the query. I understand however that a single index is generally more efficient.

Is it possible to use a single index that does trigram searches, but also exact match on user_id and type where they can optionally be NULL.


texts: What happens when SQL Server is in Single User Mode?

texts:
If I put a database in single user mode, would I have to worry about locks?  Or will SQL Server basically perform (with exception to my open transactions) like the isolation level is read uncommitted?

We have a heavy ETL process that was written and sped up by utilizing query hints such as TABLOCK and NOLOCK depending on the data.  I was wondering if putting the database into single user mode during these processes would be beneficial.


texts: Simple insert in stored procedure runs twice

texts:
I have one stored procedure to insert into a table. I have two questions:

Is it a ok?
Sometimes the insert operation runs twice. Why?

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation   Enterprise Edition (64-bit) on Windows 8.1 Enterprise 6.3  (Build 9600: )

CREATE PROCEDURE [dbo].[insert_newNEWS]
-- Add the parameters for the stored procedure here
@news_owner_id   INT,
@news_owner_type BIT,
@news_cat_id     INT,
@news_producer   NVARCHAR(1000),
@news_title      NVARCHAR(1000),
@news_sutitr     NVARCHAR(1000),
@news_text       NVARCHAR(1000),
@news_date       NVARCHAR(128),
@news_time       NVARCHAR(16),
@news_link       NVARCHAR(512),
@img_uri_type    TINYINT,
@img_uri         XML,
@newsContentID   INT OUTPUT
AS
     BEGIN

         -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
     BEGIN TRANSACTION;
     SET @newsContentID = dbo.Biggest_ID(13);
     INSERT INTO [Contents].[news]
     ([id_news],
      [news_owner_id],
      [news_owner_type],
      [news_cat_id],
      [news_producer],
      [news_title],
      [news_sutitr],
      [news_text],
      [news_date],
      [news_time],
      [news_link],
      [img_uri_type],
      [img_uri]
     )
     VALUES
     (@newsContentID,
      @news_owner_id,
      @news_owner_type,
      @news_cat_id,
      @news_producer,
      @news_title,
      @news_sutitr,
      @news_text,
      @news_date,
      @news_time,
      @news_link,
      @img_uri_type,
      @img_uri
     );
     IF(@@ERROR <> 0)
         BEGIN
             ROLLBACK TRAN;
             RETURN 1;
         END;
     COMMIT;
 END;

AND this is Mr Biggest_ID function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Biggest_ID](@table_id INT)
RETURNS INT
AS
     BEGIN
         DECLARE @BiggestID AS INT;
         --another table ID
         IF @table_id = 13
             BEGIN
                 SELECT @BiggestID = ISNULL(MAX(nws.id_news), 0) + 1
                 FROM [Contents].[news] AS nws;
             END;

         RETURN @BiggestID;
     END;

and this table :

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Contents].[news](
     [id_news] [int] NOT NULL,
     [news_owner_id] [int] NOT NULL,
     [news_owner_type] [bit] NOT NULL,
[news_cat_id] [int] NOT NULL,
[news_producer] [dbo].[JBText] NOT NULL,
[news_title] [dbo].[JBText] NOT NULL,
[news_sutitr] [dbo].[JBText] NOT NULL,
[news_text] [dbo].[JBText] NOT NULL,
[news_date] [nvarchar](128) NULL,
[news_time] [nvarchar](16) NULL,
[news_link] [dbo].[JBLinks] NULL,
[img_uri_type] [tinyint] NULL,
[img_uri] [xml] NULL,
 CONSTRAINT [PK_news] PRIMARY KEY CLUSTERED
(
    [id_news] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


texts: Get interactive psql shell after executing queries

texts:
I want to execute a set of commands before executing other queries using psql

The following is what I want to execute

psql << EOF
\setenv PAGER 'pspg -s 0'
\pset pager always
EOF

After executing this, I want to have an interactive shell with these parameters set.

I am looking for how to get interactive shell after this.


texts: How to index a many to many table most effectively

texts:
Should a many to many table be indexed? What kind of index would be best?

Here's an example table:

CREATE TABLE user_role (
  userId INT,
  roleId INT
)

--edit: drachenstern - I added the table def based on the original comments, and assumed ints.


texts: How to skip to next iteration if no results are returned in SELECT statement in Stored Procedure

texts:
I have a case where I SELECT rows from one table and INSERT them to another table. However, I get Error: 1329 SQLSTATE: 02000 if the SELECT statement doesn't return any row. Can I do something like:

CREATE PROCEDURE SP()
   DECLARE CONTINUE HANDLER FOR NOT FOUND;
   BEGIN
      ITERATE
   END
   BEGIN
      DECLARE cur1 CURSOR FOR SELECT * FROM table1;
      OPEN cur1;
      read_loop: LOOP
         FETCH cur1 INTO var1;
         INSERT INTO table2 (SELECT c1 FROM table3)
      END LOOP;
      CLOSE cur1;
   END;

Is this correct? Thanks.

EDIT:

Based on answers below, what worked for me was:

BEGIN
    DECLARE done BIT DEFAULT FALSE;
    DECLARE name VARCHAR(255);
    DECLARE lat FLOAT;
    DECLARE lon FLOAT;
    DECLARE radius FLOAT;
    DECLARE cur1 CURSOR FOR SELECT * FROM anchorage_test;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;
    read_loop: LOOP
       SET done = FALSE;
       FETCH cur1 INTO name, lat, lon, radius;
       IF done THEN
          LEAVE read_loop;
       END IF;
       INSERT INTO sp_result
       SELECT imo, draught, ST_Distance_Sphere(point(lon, lat),point(longitude,latitude))*0.000539957 AS distance, dt_pos_utc, name FROM historical_temp HAVING distance < radius;
    END LOOP;
    CLOSE cur1;
END


texts:
In the customers' table, many customers do not have state data in the state column therefore when we select customers, the state column displays NULL values, which is not meaningful for the reporting purpose.

╔════════════════╦════════════════════╦═══════╦═════════╗
║ customerNumber ║    customerName    ║ state ║ country ║
╠════════════════╬════════════════════╬═══════╬═════════╣
║            103 ║ Atelier graphique  ║ NULL  ║ France  ║
║            112 ║ Signal Gift Stores ║ NV    ║ USA     ║
╚════════════════╩════════════════════╩═══════╩═════════╝

I am trying to improve the output by using the case, but getting an error:

SELECT
    customerNumber,
    customerName,
    CASE
      WHEN state IS NULL, THEN 'N/A'
      ELSE state
    END) as state,
    country
FROM
    customers;

Error:

  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 ' THEN 'N/A' ELSE state END) as state, country FROM customers' at line 5

Desired Result:

╔════════════════╦════════════════════╦═══════╦═════════╗
║ customerNumber ║    customerName    ║ state ║ country ║
╠════════════════╬════════════════════╬═══════╬═════════╣
║            103 ║ Atelier graphique  ║ N/A   ║ France  ║
║            112 ║ Signal Gift Stores ║ NV    ║ USA     ║
╚════════════════╩════════════════════╩═══════╩═════════╝


texts: What is the purpose of a Row_GUID column?

texts:
I've been digging around in the AdventureWorks2012 database and see Row_GUID used in several tables.

There are 2 parts to my question:

When should I include a Row_GUID column?

What are the uses and benefits of a Row_GUID column?


texts:
I know when you delete data, that data is only "marked" as "deleted' for faster rollback.  The only way to really delete this data is to run something that reads the datapage, such as a table scan or to wait for the "ghost cleanup task".

I've deleted a LOT of data and I can see my database has a lot of free space, which is great. I'm not going to shrink the database since I don't need space right now; this question is just about how the delete process works.

Let's say, after this delete, I could run a script that shows me 20 indexes that need to be rebuilt, and some more to reorganize.  Well, inside them I'm sure there's a lot of ghost data.  If I run a ALTER INDEX ... REBUILD ( organize or rebuild ) will the table consume less space in the database?   I mean, can I gain space by running a reindex operation after a delete?  Or when data is marked as ghost data, is that data already not consuming space in the table?


texts:
I have an SSIS package that runs fine locally. After deploying it to SQL Server, I get an unexpected error. Under data flow task, "validation has started" is the last message I see.

To fix the problem I set "delay validation" to True everywhere. Then, I get:

  Data flow task 1;Error : SSIS Error code DTS_E_OLEDBERROR . An OLE DB
  Error has occurred .Error code 0x80004005.An Ole db record is
  available ,Source "Microsoft Access database Engine" Hresult 0x0004005
  Description "unexpected error from external database driver (1)"


texts: Updating a column after expiration of specific time in MySQL

texts:
I have a table where I have a column named state(int(2)) and modify_time (time_stamp on update current timestamp).
I have an update trigger which changes the value of state column to 0 based on some condition.
I want to set the value of state column to 1 after 24 hours of modify_time, if it is still 0.
I tried below method to test :

CREATE EVENT myevent1
 ON SCHEDULE AT current_timestamp + interval 1 minute
 DO UPDATE test.mytabletable SET state = 0;

This did not doing anything. Is there any alternative method?


texts: SELECT column for a row only if its value is not empty

texts:
I want to display a certain column only if its value is not empty.

Pseudo code:

SELECT column1, IF_NOT_EMPTY(column2), ... FROM table;

Will display all columns except column2 if column2 is empty.


texts: select Data between two date range mysql return wrong values


texts:
Not sure what am I doing wrong in this query, there seem to be a syntax error in the subquery  AND typeId = (SELECT @id = typeId FROM EmpType, near @id =.  I'm looking at getting the typeid in a variable and use it in the next subquery following this one, not sure if that is not allowed.  Its SQL Server 2012.

Employee:

  empId empName
  1     Jim
  2     Sam

EmpType:

  typeId  type
  1         Full Time
  2         Contractor
  3         Temporary
  4         Work Permit

FamilyTree:

  familyId  familyName
  1         APAC
  2         Technology-APAC
  3         Domain-EU
  4         Platform-US

EmployeeMap:

  empId   typeId    familyId
  1         1       1
  1         2       2
  2         2       3
  2         3       3

Query:

  DECLARE @id INT;
  DECLARE @empId INT = 100;
  DECLARE @type VARCHAR (50) = 'Contractor';
  DECLARE @family VARCHAR (50) = 'Domain-EU';

  SELECT * FROM  EmployeeMap
  WHERE empId = @empId
  AND typeId = (SELECT @id = typeId
                FROM EmpType
                WHERE type like @type
                AND familyId = (SELECT familyId
                                FROM FamilyTree
                                WHERE familyName like @family))


texts: SQL command mode - unable to run command

texts:
I just answered this question but never tried to run it. So I tried it.. https://stackoverflow.com/questions/53521410/sql-command-mode-sql-server-unable-to-print/53521635#53521635

and I thought if Print works then the command will run for sure but to my surprise it didn't

USE [DBName]
        :setvar ScriptPath 'C:\Work\'
        :setvar SQLFile 'Test.sql'

         ----- commented  PRINT $(ScriptPath) + $(SQLFile)    ---- Works
        GO
         :r $(ScriptPath) + $(SQLFile)   ---  Doesn't work
-- throws:A fatal scripting error occurred. Incorrect syntax was encountered while parsing :r

             :r $(ScriptPath)+$(SQLFile)   ---  Doesn't work
    -- throws:A fatal scripting error occurred. Unable to process :r command
             GO

I searched but couldn't find correct answer.

What is needed to be fixed here ?


texts: Is inserting multiple queries takes same time as that of single query?

texts:
I have a requirement where I have to insert a lots of rows.For inserting we use like this

insert into emp(name,age) values('abc',12);

This will insert only one row.For inserting multiple rows we can either write insert query multiple times or write a single query with multiple values.For example

condtion1

insert into emp(name,age) values('abc1',121);
insert into emp(name,age) values('abc2',122);
insert into emp(name,age) values('abc3',12);

condition2

insert into emp(name,age) values('abc',12),('abc2',122),('abc3',12);

My question is are both (condition1 and condition2) above takes time?I guess condtion2 takes less time than condition2. If my guess is true then what is the reason for it?


texts: Getting catagory title total number of questions and answers order by number of questions

texts:
I have three tables. Which is in at this fiddle,
http://sqlfiddle.com/#!9/4cadb2
Which is like this?.
CREATE TABLE `user_answers` (
  `id` int(250) NOT NULL,
  `question_id` varchar(250) NOT NULL,
  `user_id` int(250) NOT NULL,
  `answer` text NOT NULL,
  `date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `user_questions` (
  `id` varchar(250) NOT NULL,
  `user_id` int(250) NOT NULL,
  `note_id` int(250) NOT NULL,
  `title` text NOT NULL,
  `question` text NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `material_univarcity_list` (
  `id` int(100) NOT NULL,
  `name` varchar(50) NOT NULL,
  `about` mediumtext NOT NULL,
  `date` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I want to get a resultant table which has count - Name(Name is from material_univarcity_list) Total number of questions to that particular name. Here id of material_univarcity_list table is the note_id of user_questions and I want total number of answers to that particular catagory here question_id is the id of user_questions. How can I do this?
My expected result is like
no|catagory        |number_of_questions |number_of_answers
 1|DBMS 5th sem ISE|            2       | 3
 2|DBMS 6th sem ISE|            1       | 1

How can i do this?


texts:
i want to ask about my script this is doesn't work. i need to help !

SELECT A.*,B.mat_nama,C.mats_nama
FROM pengeluaran AS A
INNER JOIN(material AS B) ON(A.mat_id = B.mat_id)
INNER JOIN(mat_satuan AS C) ON(B.mat_satuan_id = C.mats_id)
WHERE A.mat_id = '7'
   AND A.pengeluaran_tgl BETWEEN '01/01/2014'  AND '02/28/2014'

UNION

SELECT A.*,B.mat_nama,C.mats_nama
FROM penerimaan_sub AS A
INNER JOIN(material AS B) ON(A.mat_id = B.mat_id)
INNER JOIN(mat_satuan AS C) ON(B.mat_satuan_id = C.mats_id)
WHERE A.mat_id = '7'
  AND A.penerimaan_tgl BETWEEN '01/01/2014' AND '02/28/2014'

Error Notification in PhpMyadmin:

1064 - 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 'ON(A.mat_id = B.mat_id) INNER JOIN(mat_satuan AS C) ON(B.mat_satuan_id = C.mats_' at line 1


texts:
Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server?

In this particular case I am wondering about async_network_io.


texts: Update all rows from a table with random foreign key from another table

texts:
I have four tables: (show on image below)

Topic whit columns:

ID

primary key
int

CategoryID

references to table Category
int

UserID

references to table User
nvarchar(128)

User with column:

ID

primary key
nvarchar(128)

Category whit column

ID

primary key
int

UserCategoryLink with columns

UserID

primary key
references to table User
nvarchar(128)

CategoryID

primary key
references to table Category
int

I will to update table Topic so that the CategoryID is one of the CategoryID's of the table UserCategoryLink.

P.S.: I use MS SQL Server.

3th update: Like you can see on this SQL Fiddle, the category from the topics form every user is not included into the table UserCategoryLink but it must! So can Marc only have topic whit category cycling and snowboarding and not from moto and swimming. Now I will to update all the wrong (correct can also be updated) rows to an ID from the column CategoryID from the table UserCategoryLink.

1st update: I have try the following code

;WITH rs AS
(
    SELECT UserID as Sid, CategoryID as Cid
    FROM UserCategorieLink
)
update Topic
    set CategoryID = (select top 1 Cid from rs where UserID = Sid)
    where UserID = (select top 1 Sid from rs where UserID = Sid);

But the result is not random chosen by the favorite categories for every user. I know it comes from top 1 but it is because I don't know how to generate random numbers.

I will take a random row from a select statement (see code below / like I can do in a programming language like C#) but it gives me an error because RAND generates a random number between 0 and 1.

RAND(select top 1 Cid from rs where UserID = Sid)

The errors are:

  Incorrect syntax near the keyword 'select'

and

  Incorrect syntax near ')'.

on the 7th line.

2nd update: The code give me also the same random number for each topic that comes from the same user. Can that also be a random number? I know that every user has always two favorite categories. The query must pick one of them.

I know that it comes from the where clause of the update statement. But how can I fix it?


texts: Query optimization: how to efficiently occlude overridden records?

texts:
Imagine a table with roughly a couple of billion rows.  A simplified schema for the table would be

started_on: datetime
recorded_on: datetime
image_id: integer

It's indexed by started_on.

One quirk of the data is that there can be many records with identical started_on values but they may have different recorded_on and image_id values.  Further, for a given started_on, we only want the most recently recorded record.

Currently we get this by

1) pulling all the records within the started_on range, and then

2) using code to grab out the most recently recorded_on record for each started_on.

This works fine, but seems like a waste because we're serializing and sending all those extra records to our app.

Is there an efficient way to accomplish the job of occluding the overridden records in our initial query?


texts:
I'm new to PostgreSQL so sorry if I haven't explained myself clearly!

I've used the below query to find out the max character length of each column in my table. I now want to be able to shorten each column to the largest character length. Can anyone help?

SELECT
        MAX(char_length(gid)) as column1,
        MAX(char_length(column2)) as column2
FROM
        schema.table

The reason I want to do this I want to export to shapefiles and the file size is huge as the majority of the columns are set to 254.


texts: Most used tables in MySQL database server

texts:
Can somebody please help me with a query that lists the most used tables in a MySQL database? Or how can i find out the most used tables in the database?

Thanks


texts:
I would like to see what queries are being sent to a DB and what is returned. Is this possible with MsSQL Server Management Studio or a third party application?
 Is there anything i can use that is free?


texts: How the single SQL statement is isolated?

texts:
All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved.

But what's about single statements? What level of isolation do they have? Is it specified somewhere in a standard? Or does it depend on RDBMS and isolation level?

Let me show a couple of examples.

UPDATE table SET value = value + 1 WHERE id = 1;

This is a composite read-update-write operation. Can a parallel transaction change the value between read and write operations? Some book states that this operation is atomic (in multithreaded programming meaning) in the most of the RDBMS.
SELECT * FROM table t1 JOIN table t2 USING (id);

If the table is quite large (or query would have some complicated filtering clause), is it possible at some isolation level that t1.* and t2.* columns would differ due to parallel update?
SELECT * FROM table1 WHERE id IN (SELECT t_id FROM table2);

Is it possible that some records from table1 are removed after sub-select has been executed? (Here I assume that table2.t_id references table1.id with cascaded removal.)
CTE...

Links to the useful manuals that fully explain all the details of transactions are also appreciated.


texts: SQL Server space in MDF file with multiple files and one Primary file

texts:
We have space issues in our F: and our H: drive. We have added one more drive - I: drive with 500 GB capacity. Can we add the new database file in the I: drive and set the autogrowth option in the F: drive and the H: drive so that the new data will use the I: drive?
The current database files are as follows:
F:\MSSQL11.MSSQLSERVER\MSSQL\DATA\test.mdf
G:\MSSQL11.MSSQLSERVER\MSSQL\Log\test1_log.ldf
H:\MSSQL11.MSSQLSERVER\MSSQL\Data\test123_1.ndf
H:\MSSQL11.MSSQLSERVER\MSSQL\Data\test_2.ndf


texts: What's the quickest way of porting thousands of PostrgreSQL queries to HSQLDB?

texts:
Lately we have been tasked with porting some existing DB infrastructure from PostgreSQL to HSQLDB. Yes, I know what you're thinking (why on Earth would anybody do such a thing?). But unfortunately for reasons I can't explain here this is our task and there's no way around it.
We don't need to port any data, just the queries: we need to plug an HSQLDB layer into our software to replace the PostgreSQL layer.
There are thousands of hardcoded PostgreSQL queries -- and while I thought initially that HSQLDB syntax is similar (somewhat compatible) with PostgreSQL syntax, I was quite surprised. For instance, statements including

SERIAL,
RETURNING,
and other more complex queries involving foreign keys,

are not supported in HSQLDB. Note that we are using the latest HSQLDB driver (2.5.1), but if any other version is more compatible that's fine as well.
I can of course go through each and every query and ''translate'' them into HSQLDB syntax, but that is not very efficient.
Any ideas?


texts: How can I pass parameter to stored procedure through a query?

texts:
I have this stored procedure:

CREATE PROCEDURE `test`(OUT `vote_value` INT, OUT `score` INT, IN `user_id` INT, IN `time_range` INT)
    NO SQL
BEGIN
    SELECT coalesce(sum(vote_value), 0), coalesce(sum(score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > CASE time_range
          WHEN 'WEEK'
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))
          WHEN 'MONTH'
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 MONTH))
          WHEN 'YEAR'
          THEN  unix_timestamp(DATE_SUB(now(), INTERVAL 1 YEAR))
          ELSE 1 END;
END

As you see my procedure has 4 parameters. Both vote_value and score have OUT direction (as result), And both user_id and time_range have IN Direction. So I have to pass those two which have IN direction when I call the procedure.

Ok, I can call procedure about like this:

CALL test(@vote_value, @score, 10, 'WEEK');
SELECT @vote_value, @score;

It would work as well. But please focus on the third parameter which is 10. I don't want to pass it manually by hand. I want it be a value from a query. Something like this:

CALL test(@vote_value, @score, u.id, 'WEEK');
SELECT id, @vote_value, @score FROM Users u;

But it throws syntax error. Does anybody know how can I do such a thing? The expected result is this.


texts: Why do I have to set MySQL user password with mysql_native_password to connect with Workbench?

texts:
I've noticed if I set the mysql user's password like below, I cannot connect with MySQL Workbench.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'myPassword'
If I use this below I can connect to it:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'myPassword';
I remember at MySQL 8 there was a default password policy change.
But still after years of that change, do the drivers (c/c++, node.js etc) not support the new password authentication?
Does MySQL's own Workbench don't support the new way of authentication?
Or am I missing something?


texts:
I am running in to an issue where when I run a query against a view it takes 4+ minutes.  However, when I run the guts of the query it finishes in like 1 second.

The only thing I am not sure about is the tables being joined are both temporal tables.

Ad hoc query plan:
https://www.brentozar.com/pastetheplan/?id=BykohB2p4

View query plan:
https://www.brentozar.com/pastetheplan/?id=SkIfTHh6E

Any suggestions on where to try and figure this out?

View code:

ALTER VIEW [dbo].[vwDealHistoryPITA]
AS
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.LastUpdateDate) AS Deal_HistoryID,
       cm.CodeMasterID,
       cm.ProjectName,
       cm.[Status],
       d.CompanyID,
       d.DealTypeMasterID,
       cm.[Description],
       d.PassiveInd,
       d.ApproxTPGOwnership,
       d.NumberBoardSeats,
       d.FollowonInvestmentInd,
       d.SocialImpactInd,
       d.EquityInd,
       d.DebtInd,
       d.RealEstateInd,
       d.TargetPctgReturn,
       d.ApproxTotalDealSize,
       cm.CurrencyCode,
       d.ConflictCheck,
       cm.CreatedDate,
       cm.CreatedBy,
       cm.LastUpdateDate,
       cm.LastUpdateBy,
       d.ExpensesExceedThresholdDate,
       d.CurrentTPGCheckSize,
       d.PreferredEquityInd,
       d.ConvertibleDebtInd,
       d.OtherRealAssetsInd,
       d.InitialTPGCheckSize,
       d.DirectLendingInd,
       cm.NameApproved,
       cm.FolderID,
       cm.CodaProcessedDateTime,
       cm.DeadDate,
       d.SectorMasterID,
       d.DTODataCompleteDate,
       cm.ValidFrom AS CodeMasterValidFrom,
       cm.ValidTo   AS CodeMasterValidTo,
       d.validFrom  AS DealValidFrom,
       d.validTo    AS DealValidTo
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID;
GO

Added the Partition by and get similar results to the ad hoc query.


texts:
I have several SSAS databases.  I only want to script out the XMLA for them on a nightly basis.  This will be a second tier backup in addition to the regular backups we have.

How do I auto-generate XMLA scripts for all the databases?


texts: Slow MYSQL performance due to sub queries

texts:
Here is my query:

SELECT BAL,NAME,
   (SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR ,
   (SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS CR
FROM main
GROUP BY MARK,NAME WITH ROLLUP

This query takes at least 20 seconds and sometimes even up to 45 seconds when  ‘DATE’ increases(example:2012-05-01).

There are about 500,000 records in the transaction table. After hours of testing I think the problem might be with the 2 sub queries:

(SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR ,
(SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS CR

I tried adding an index to DATE and TNAME, it speeded things up a little bit, but still not enough.Would be great if I could atleast shave off atleast 10 more seconds
Is there anything I could do to make it faster?

Table details:

 CREATE TABLE ` transaction` (
  `DATE` DATE NULL DEFAULT NULL,
  `DOCNO` VARCHAR(10) NULL DEFAULT NULL,
  `TCODE` VARCHAR(6) NULL DEFAULT NULL,
  `TNAME` VARCHAR(50) NULL DEFAULT NULL,
  `CR` DECIMAL(12,2) NULL DEFAULT NULL,
  `DR` DECIMAL(12,2) NULL DEFAULT NULL,
   INDEX `TNAME` (`TNAME`),
   INDEX `DATE` (`DATE`),
   INDEX `CR` (`CR`),
   INDEX `DR` (`DR`)
  )
  COLLATE='latin1_swedish_ci'
  ENGINE=InnoDB;

  CREATE TABLE `main` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `CODE` VARCHAR(50) NOT NULL,
    `NAME` VARCHAR(70) NULL DEFAULT NULL,
    `BAL` DECIMAL(12,2) NULL DEFAULT '0.00',
    `MARK` VARCHAR(35) NULL DEFAULT NULL,
    PRIMARY KEY (`CODE`),
    UNIQUE INDEX `NAME` (`NAME`),
    PRIMARY KEY (`id`),
    INDEX `MARK` (`MARK`)
   )
   COLLATE='utf8_general_ci'
   ENGINE=InnoDB
   ROW_FORMAT=COMPACT
   AUTO_INCREMENT=545451;


texts: MySQL Using a trigger to update quantity on another table if part is used

texts:
I am new to triggers but want to know how I can use one in my database.
I have created a table for a maintenance log, a parts inventory, and a parts_used junction table. When maintenance is carried out the parts and log id's a inserted into parts_used. I want to be able to take subtract 1 from the quantity in the inventory for that part.

Maintenance log

CREATE TABLE maintenance_log (
    idlog int  (4) AUTO_INCREMENT,
    idVehicle int (2),
    description VARCHAR (80),
    entry_date DATE(),
    release_date DATE,
    idMechanic int (2),
    FOREIGN KEY (idVehicle) REFERENCES vehicles(idVehicle),
    FOREIGN KEY (idMechanic) REFERENCES mechanics(idMechanic),
PRIMARY KEY (idlog)
);

Parts

    CREATE TABLE parts (
    idParts int(3) AUTO_INCREMENT,
    part_description VARCHAR (60),
    quantity int (2),
PRIMARY KEY (idParts)
);

parts_used

CREATE TABLE parts_used(
    idParts int (3),
    idlog int  (4),
CONSTRAINT PK_partUsed PRIMARY KEY
    (
        idParts,
        idlog
    ),
    FOREIGN KEY (idParts) REFERENCES parts(idParts),
    FOREIGN KEY (idlog) REFERENCES maintenance_log (idlog)
);

My current trigger just returns ERROR with no other message. This is what I am using.

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
BEGIN
           UPDATE parts
           SET parts.quantity = parts.quantity - 1
           WHERE parts.idParts = NEW.idParts
END

DELIMITER ;

Can anybody help or offer any advice?


texts: Drive Configuration In Virtual Environment

texts:
My company is getting some new VM (VMWare) servers provisioned for purposes of installing SQL 2014 or 2016 (version to be determined) with AlwaysOn Availability Groups.  The server and storage provisioning is being done by managed services within the data center.

As a DBA with the company, I recommended our standard drive layout (OS, App, SqlData, SqlLog, TempDB, and SQLBackup), so six drives per server.

The Virtualization Lead with the data center is recommending just two drives per server.  One for OS/Apps, and the other for SQL.  His exact response was "With an aag [AlwaysOn Availability Groups] and modern storage, we can typically flatten it out to an OS and a SQL disk.  The era of separating disk groups to alter performance  is behind us with object based arrays on the back end".

To me, this configuration seems counter-intuitive to everything I've been taught and experienced.

My question is this:  Are modern storage arrays (I have no insight into what the actual infrastructure is, other than it's supposed to be "state of the art") getting that good that we don't need to manage for splitting up IO for data, logs, and TempDB?

I won't be managing the servers, nor even doing the SQL configuration on them, so I don't have much skin in the game.  I'm more interested in hearing if this is others experience on server drive configurations going forward as we move to better virtualized environments.  I should note that the data going on these servers will not likely have a high amount of IO, so I don't think performance will be a real factor.  The two main priorities are that the system has very high up-time and is very secure.


texts:
Not sure if this can be done without UNION. I think it is the UNION which makes it slow. The two parts are quite fast on their own, but combined with the UNION this code takes > 5 seconds to complete. Can it be optimized?

SELECT rl.Updated, c.CultureName,
"CollectionName"
as CollectionName, k.KeyName , rl.Value , s.StatusName ,
rl.Comment , rl.Comment as ActionInfo, a.ActionName, u.UserName
FROM ResourceLog rl
INNER JOIN `Status` s ON rl.StatusId = s.StatusId
INNER JOIN Culture c ON rl.CultureId = c.CultureId
INNER JOIN `Key` k ON rl.KeyId = k.KeyId
LEFT JOIN `Action` a ON rl.ActionId = a.ActionId
LEFT JOIN `User` u ON rl.UserId = u.UserId
UNION SELECT imp.Created as Updated, "Imported" as CultureName,
"None" as CollectionName, "Imported" as KeyName,
"Imported" as Value, "Imported" as StatusName,
"Imported" as Comment, "ActionInfo" as ActionInfo,
"Imported" as ActionName, UserId
FROM Import imp
ORDER BY Updated DESC, KeyName, CultureName limit 50


texts: Truncate tables with dependent foreign key constraints

texts:
Hi I'm trying to truncate some tables from my db, but they are related with foreign key constraints so every time I try it SQL Server throws an error like this:

  Cannot truncate table 'Table' because it is being referenced by a FOREIGN KEY constraint

I don't want to drop the tables or delete them.

A friend told about a Truncate Cascade for this case, but I haven't found any info related, also other user told me to try this; I did try it, but I'm still not getting my tables truncated.

Also I read about SCRIPT for Foreign Keys DROP and RE-CREATE for use with TRUNCATE.

And the script is supposed to affect my db or at least I thought so. But after running it, I was unable to truncate my tables and it threw the same error. I'm using SQL Server 2008 R2, and running my queries with that version of SSMS.


texts: SQL statement to combine main table and child table as columns?

texts:
Having a main table and a child table in a Microsoft SQL Server database (2005+), I want to show the user a single table in a grid view which includes rows from the main table and rows from the child table, with the latter one being shown as additional columns.

Please see the following example.

Main table:

ID | Name
---+--------
20 | Thomas
21 | Martin
22 | John
23 | Mike

Child table:

ID | MainID | Key | Value
---+--------+-----+-----------
1  | 20     | X   | Apple
2  | 20     | Y   | Microsoft
3  | 20     | Z   | Goole
4  | 21     | X   | Dell
5  | 22     | X   | Xing
6  | 22     | Y   | LinkedIn
7  | 22     | Z   | Cisco
8  | 22     | A   | Redhat
9  | 23     | Y   | Logitech
10 | 23     | Z   | Adobe

Now what I want to generate is an SQL statement that results in the following result set:

ID | Name   | X      | Y         | Z
---+--------+--------+-----------+--------
20 | Thomas | Apple  | Microsoft | Google
21 | Martin | Dell   | <null>    | <null>
22 | John   | Xing   | LinkedIn  | Cisco
23 | Mike   | <null> | Logitech  | Adobe

I.e. the child rows are transformed as additional columns.

My question is:

Is there a way to achieve this with standard SQL, i.e. without using the PIVOT statement or some XML statements? (because I want to use the query against a VistaDB database, too).

Update 1

Then number of detail keys is variable and not limited to 3 or to "X", "Y" and "Z" as names.


texts: Why won't SQL Server optimize the UNIONs?

texts:
Consider these queries (SQL Fiddle):

Query 1:

SELECT * INTO #TMP1 FROM Foo
UNION
SELECT * FROM Boo
UNION
SELECT * FROM Koo;

Query 2:

SELECT * INTO #TMP2 FROM Foo
UNION
SELECT * FROM Boo
UNION ALL
SELECT * FROM Koo;

Note that Koo does not overlap with Boo/Foo, so the end result is the same.  The question is why the first UNION / UNION combination is not merged into a single SORT operation?


texts:
I have created a SharePoint application which has a specific time zone.
The Windows server where SQL Server and SharePoint are installed are using same time zone.

When I create a new item in list in SharePoint it shows some different time zone time.

I don't know why this is happening but I suspect this is because of SQL Server. If I query current date and time in SQL Server then it shows correct time zone but in SharePoint it is showing different times.

What could be the reason?


texts: How assigning a value to Variable is working in SSRS

texts:
Recently started learning Reporting Services using Microsoft SQL Server 2016 Reporting Services, Fifth Edition, Brian Larson.
I am creating this one report which uses UNION to create a dataset, FuelType, that will be used for assigning values to a dropdown list.

SELECT 'All' AS Fueltype, '_All' AS SortField
UNION
SELECT Description, Description FROM Propulsion ORDER BY SortField

Now there exists another dataset that will help generate the chart for me and there.

SELECT Propulsion.Description AS Fueltype, FuelPrice.PriceStartDate, FuelPrice.Price
FROM  FuelPrice
INNER JOIN Propulsion ON FuelPrice.PropulsionID = Propulsion.PropulsionID
WHERE        (YEAR(FuelPrice.PriceStartDate) = @year) AND  (Propulsion.Description = @PropulsionType)
OR  (YEAR(FuelPrice.PriceStartDate) = @year) AND (@PropulsionType = 'All')
ORDER BY Fueltype, FuelPrice.PriceStartDate

I do not understand, how by using "@PropulsionType = 'All'" I am able to pass in all available values to the parameter.

For someone who has the book, this is under Chapter 6 :Fuel Price Chart, Version 2


texts: Galera cluster consume many CPU percent and CPU high load

texts:
I have implemented a cluster include three nodes: two stored nodes and one garbd (also haproxy - load balancer).

Some information about servers:
SSD 480G Raid1
Memory 32G
Swap 16G
2xCPU, number of cores: 24
Maria Galera Cluster 10.0.14

mysqld process consume CPU percent so much 229.3% - 300%

The highes CPU load 20-30.

When CPU load grow up, there is some log like that.

*** Victim TRANSACTION:
TRANSACTION 17362851, ACTIVE 5 sec
mysql tables in use 1, locked 1
809 lock struct(s), heap size 95784, 149905 row lock(s), undo log entries 19493
MySQL thread id 182155, OS thread handle 0x7fc587451700, query id 1566816, query end
    *** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 180 page no 764 n bits 288 index `PRIMARY` of table `xxx`.`yyy` trx id 17362851 lock_mode X
141128 10:36:45 [Note] WSREP: cluster conflict due to high priority abort for threads:
141128 10:36:45 [Note] WSREP: Winning thread:
   THD: 22, mode: applier, state: executing, conflict: no conflict, seqno: 1447458
   SQL: (null)
141128 10:36:45 [Note] WSREP: Victim thread:
   THD: 182155, mode: local, state: committing, conflict: no conflict, seqno: -1

I don't know why galera get cert fail because I am using master-slave. There is only node receiving writeset from clients.

Some solution I am applying:

set wsrep_auto_increment_control=OFF on two nodes
set query_cache_size=1G
Decrease wsrep_slave_thread from 48 to 24

Now, my CPU load is in range 2.00-3.00 I am waiting and observing.
What can I do for better performance. I am mangaing use thread pool for decrease CPU percent usage of mysqld.


texts:
I have two tables:

I1 (A, B, C)
I2 (A, C)

I2 has some tuple of data that I1 doesn't have.

Now I want to create a table I3 that only has the data (A, C) that both exists in I1 and I2.

Which keyword I should use to create this table?
Use intersect?


texts:
I am a Report Developer who wants to make my queries as efficient as possible.  I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to use NOLOCK in every single query.

Now, I work with a DBA who has banned NOLOCK under any circumstance - even when a report of mine (due to a considerable lack of indexes on a couple of tables) is stopping replication and system updates.  In my opinion, in this case, a NOLOCK would be a good thing.

Since most of my SQL training has come various DBAs with very different opinions, I wanted to ask this to a wide variety of DBAs.


texts: Using Multi-table Insert for Parent and Child Table

texts:
Is it safe to use Oracle's multi-table insert statement to insert into a (foreign key constrained) parent and child table?

With minimal examples, I've found that it works as long as the parent table comes before the child table in the into list. Can I rely on this or should I make the constraint deferrable?


texts: XMLTABLE solution without getting right parenthesis missing error

texts:
I am trying to select from an XML type and produce multiple rows. Have searched for my specific scenario/issue with not much success. Environment: Oracle 12c. I have commented out the offending code.

WITH XML_DATA AS
    (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
    <response>
        <phone>
            <number>859111</number>
            <contactModes>
                <mode>AUTO_DIAL</mode>
                <mode>AUTO_TEXT</mode>
                <mode>MANUAL_DIAL</mode>
            </contactModes>
            <contactableTimeRange>
                <day1>
                    <startTime>08:00:00</startTime>
                  <endTime>20:00:00</endTime>
                </day1>
                <day2>
                    <startTime>08:00:00</startTime>
                    <endTime>20:00:00</endTime>
                </day2>
            </contactableTimeRange>
        </phone>
    </response>') doc
    FROM DUAL)
SELECT X.phone
    ,x3.d1starttime
    ,x3.d1endtime
    /*,x4.* */
    FROM XML_DATA XD,
    XMLTABLE('/response/phone' PASSING XD.DOC
         COLUMNS phone  VARCHAR2(36) PATH 'number',
                CONTACT_MODES XMLTYPE PATH 'contactModes',
                CONTACT_TIMES XMLTYPE PATH 'contactableTimeRange')  X
      /*  , XMLTABLE('/contactModes'
       passing x.contact_modes
       COLUMNS  modes VARCHAR2(10) 'node' ) AS  x2*/
       ,XMLTABLE('/contactableTimeRange/day1' PASSING X.CONTACT_TIMES
                COLUMNS D1STARTTIME VARCHAR2(10) PATH 'startTime',
                        D1ENDTIME VARCHAR2(10) PATH 'endTime') AS X3
       ,XMLTABLE('/contactableTimeRange/day2' PASSING X.CONTACT_TIMES
                COLUMNS D2STARTTIME VARCHAR2(10) PATH 'startTime',
                D2ENDTIME VARCHAR2(10) PATH 'endTime') X4;

I would like the query the return the following records. The mode values are not static, could be more or less. And same thing for the days. Could be day1 ..dayn

Phone      MODE      DAY    STARTTIME     ENDTIME
859111    AUTO_DIAL   1    08:00:00  20:00:00
859111    AUTO_DIAL   2    08:00:00  20:00:00
859111    AUTO_TEXT   1    08:00:00  20:00:00
859111    AUTO_TEXT   2    08:00:00  20:00:00
859111    MANUAL_DIAL 1    08:00:00  20:00:00
859111    MANUAL_DIAL 2    08:00:00  20:00:00


texts: MySQL Master - Slave replication stops after 20min with no errors

texts:
I'm using MariaDB 10.4 in a standard master slave setup.
Everything works fine for about 20min, I can see changes replicating across but then after about 20min replication just stops.
However, if I restart the slave MySQL instance, replication begins again and catches up.
Restarting the master has no effect on the replication it stays broken.
When in the broken non-replicating state this is the output from the slave and master.

So it looks like it thinks its caught up, but it hasn't, no matter how long I leave it.
I've used netcat to test the connection and I know it can see the master still, plus when I restart MySQL it works right away.
The my.cnf config has not been changed apart from adding the server ID and this is how I configured the slave initally CHANGE MASTER TO MASTER_HOST='dynamicip.xxxxxxx.org', MASTER_PORT = 4302, MASTER_USER='remote_replication', MASTER_PASSWORD='xxxxxxxxxx', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=328;
The error logs contain no errors also.
Where would I begin to troubleshoot this?
EDIT / UPDATE
So it appears the data does replicate over eventually but it takes a least 50 minutes, and its tiny bits of data I'm testing it with.
I've switched the hostname dymanic.****.org to an IP address in case it was Cloudflare doing something weird but that hasn't fixed it. I also tried skip-host-cache before that.
I've also switched innodb_flush_log_at_trx_commit from 0 to 1.
Traceroute looks fine.
I can see the statements in the binlog file on the master.
Interestingly using STOP SLAVE and START SALVE kicks it back into action for a while before it begins having the issue again.
SHOW PROCESSLIST on the Master shows that the I/O thread is sometimes present and sometimes not, not sure what that means..


texts: DELETE IGNORE on Tables with Foreign Keys Can Break Replication

texts: I am using replication in a MYSQL database. I have read some where DELETE IGNORE command on Tables with Foreign Keys can break Replication. Is it true? If it is true then how can workaround this?


texts: Must data be evenly distributed among Mongo shards?

texts:
According to this article: https://www.mongodb.com/blog/post/on-selecting-a-shard-key-for-mongodb. Assuming I have my schema as:

{
    time_posted: ...,
    userid: ...,
    content: ...
}

The author mentions that if I frequently query the latest 10 articles from a certain user, I should set a compound key of time_posted and user_id.

But in my case, I only need to query the latest 10 articles from all users. Therefore I think I only need to set a single key time_posted. I observed that by doing so the data are mostly inserted into 1 shard, and so Read Locality is improved, which is the desired effect.

However, according to https://docs.mongodb.org/v3.0/core/sharding-balancing/, Mongo will try to balance the data evenly among all the shards.

  Balancing is the process MongoDB uses to distribute data of a sharded
  collection evenly across a sharded cluster. When a shard has too many
  of a sharded collection’s chunks compared to other shards, MongoDB
  automatically balances the chunks across the shards.

So my question are: must the data be evenly distributed among Mongo shards? and can I turn off the rebalancer since I don't need my data to be evenly distributed?


texts: Is there a possibility to see cascade path in MS SQL

texts:
I'm having trouble with creating a foreign key in a database, because of multiple cascade paths.
I am aware of some of my FKs cascading the delete, but I cannot traverse back to find the multiple cascade path.

So I'm trying to find a way to caclulate the actual cascade paths of my database.
Is there anything besides manually querying the FKs to show the paths?

Inspired by @David Spillett I forged a small script, which will at least Show all references:

SELECT
    FK.name,
    PSCH.name + '.' + PTBL.name + '.' + PCol.name AS PARENT,
    RSCH.name + '.' + RTBL.name + '.' + RCOL.name AS REFERENCED,
    PSCH.name + '.' + PTBL.name + '.' + PCol.name + ' -> ' + RSCH.name + '.' + RTBL.name + '.' + RCOL.name
FROM
    sys.foreign_keys FK
JOIN
    sys.foreign_key_columns FKC
    ON FK.object_id = FKC.constraint_object_id
JOIN sys.tables AS PTBL
    ON FKC.parent_object_id = PTBL.object_id
JOIN sys.schemas as PSCH
    ON PTBL.schema_id = PSCH.schema_id
JOIN sys.columns AS PCOL
    ON FKC.parent_column_id = PCol.column_id
    AND FKC.parent_object_id = PCol.object_id

JOIN sys.tables AS RTBL
    ON FKC.referenced_object_id = RTBL.object_id
JOIN sys.schemas as RSCH
    ON RTBL.schema_id = RSCH.schema_id
JOIN sys.columns AS RCOL
    ON FKC.referenced_column_id = RCol.column_id
    AND FKC.referenced_object_id = RCol.object_id

WHERE
    FK.delete_referential_action = 1

With a Little CTE-Magic, this could probably be turned into something which Shows all cascade paths.


texts:
We are seeing the below query as regularly having very high wait stats.

What does this query relate to? System tables? Is it possible to reduce it's wait time?

Thanks!

select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"

When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id


texts: SQL Server 2008 R2 Restore Database From File - "failed: 38(failed to retrieve text for this error. Reason: 15105)"

texts:
I'm running a database restore on a backup file that was created up on a clients server, the backup file has been moved to our server (same SQL version 2008 R2) and I'm getting the following error after the T-SQL has been running for nearly 7 minutes:

TSQL:

RESTORE DATABASE [Charms_OrangeGrove]
FROM    DISK = N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Charms_OrangeGrove_290114.bak'
    WITH  MOVE N'CharmsData_dat'
          TO N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Charms_OrangeGrove.mdf',
          MOVE N'CharmsData_log'
          TO N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Charms_OrangeGrove_Log.ldf'

Error:

Msg 3203, Level 16, State 1, Line 1
Read on "S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Charms_OrangeGrove_290114.bak"
    failed: 38(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


texts:
How can I check if a SQL Login exists for an arbitrary domain user if the account I'm running doesn't have SA rights to the instance (but will be dbo on at least one DB)

Ideally I'd like the solution to be automate-able for inclusion into a utility at some point in the future.

If I create a user in my DB and attempt to map it to a SQL Login that doesn't exists will it report that it doesn't exist or just give me a permissions error?


texts:
I'm an ruby hobbist with no experience at all related to databases. I'm writing an application that is going to gather and backup tweets and blog posts (via rss feed) in an SQLite3 database.

The first version had support just for backing up a specfic user's tweets as I wanted to create a backup for my twitter account. So the SQLite3 schema was easy: 1 database, 1 table per user.

Now I want to add backup support for: Location based tweets (i.e. Tweets from Germany), username based tweets, and hashtag based tweets plus support rss feeds.

Since SQLite3 does not support any sort of sub-tables and I'd like to create a schema that will not need change as soon as I begin testing, I'd like to have opinions on how to approach this.

I've read a lot or articles online about SQL db design and found a couple of book also. Didn't purchase any yet, I'm just considering reading some basics about db design since I don't need an extensive book just an intro I'm evaluating. I'm not sure if these articles confused me more or not.

What I came up with a db schema is an INDEX table which user's info will be held and then a table per option per user, like:

➜  lib git:(dev) ✗ cat sqlite_scheme.txt
SQLite3 Table Scheme

MRParent
--------
db_key
table_id
twitter_id
twitter_option {what and how}
feed_url
short_description

MRChild-id-tweet-usr
---------
db_key
tweet_id {from MRParent}
table_id
tweet_date
tweet_location
tweet_text

MRChild-id-tweet-location
-------------------------
db_key
table_id {from MRParent}
tweet_id
tweet_date
tweet_location
tweet_text

MRChild-id-tweet-hashtag
------------------------
db_key
table_id {from MRParent}
tweet_hashtag
tweet_id
tweet_date
tweet_location
tweet_text

MRChild-id-rss
--------------
db_key
table_id {from MRParent}
post_author
post_date
post_title
post_text

So all tables will link to the INDEX table via table_id (which I've not deciced if it's gonna be a sort of hash or a combination of user's data, ideas here also welcomed).

Does this schema look 'rational' for an SQLite3 db or not? Any comments and/or questions are welcome.

Thanks for your time!

PA


texts: Does not writing a checkpoint record damage anything?

texts:
I got an alert

  Event ID: 3619. Could not write a checkpoint record in database 'ID ####' because the log is out of space

I know why it happened, I have addressed the issue, backed up the t-logs, shrunk the log that overgrew and other wise done my best to ensure it does not occur again. The issue that caused the logs to fill was an index rebuild.

Other than checkpoint not getting written and the specific index rebuild not finishing, I don't see anything else going wrong.

Does the failed checkpoint write (by its self) cause any damage that I might need to address?

Reference CHECKPOINT (Transact-SQL)


texts: Suppress bogus warning for query using triggers

texts:
As far as I understand based on my research, a very simple update query like:

UPDATE someTable SET status = 10 WHERE id=123;

will fill the error log with bogus warnings if the query invokes triggers, and statement based binary logging is turned on.

At the end of this bug report https://bugs.mysql.com/bug.php?id=45677 it says:

  For 5.1, We mark all the statements that invoke a trigger or call a
  function that updated autoinc fields as unsafe and will switch to
  row-format in mixed mode. But in statement mode, we didn't emitted the
  warning. (sic)

  For 5.5, we emitted the warning in statement mode, because  a suppress
  warning feature is introduced.

In my understanding this means that whenever I update a row with an auto-increment column, and triggers are invoked, the queries the trigger runs will be marked as unsafe for replication in statement mode without further consideration. Is this correct? Why is it like that?

If the above is correct, how can I turn the warnings off for these specific queries only?

Please note, that switching to row based replication is not an option for me, because in my three tier replication model I have a blackhole engine in the middle. This kind of setup, I believe, cannot function with row based replication, and is a matter of another question of mine here: Row-row based logging in a blackhole replication filter setup?

I'm using Mysql 5.7


texts: Question about new behavior of current connection combobox in SQL Server 2012

texts:
I have a question about the new behavior of the database selection combobox in SQL Server 2012 (Press Ctrl + U to move your focus to it). It seems that, unlike in SQL Server 2008 R2, clicking in the text of the database selected (as opposed to the dropdown arrow) selects the text in the combobox rather than activating the dropdown function.

I find this sort of a nuisance, since the new intellisense isn't active in the combobox. I don't actually want to type out the name of the database since most of the databases have the same prefix in their name; that would be a lot of unnecessary typing.

As such, I would prefer if clicking the text of the combobox would activate the dropdown rather than have it be available for text input. Does anyone know how I can do this?

Also, if I use the Control + U keyboard shortcut to put the focus on the combobox, is there any key I can press to activate the dropdown so I can navigate to the available databases that way? I know pressing up and down will switch from one to another, but is there anyway to actually bring up the list?

Can anyone recommend any options to alter the behavior of the combo box to make it more useful? I was considering using SSMSBoost; does anyone have any experience with it?

I would appreciate any advice anyone can provide.


texts: Copy from one MySQL table to another MySQL table of same database

texts: I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?


texts:
I feel like a may be standing too close to this one.
I've inherited an SSIS package that has two data connection objects. On OLE DB connection (to perform work) and an ADO.NET connection (to a different server) for logging.

The server on the far end of the ADO.NET connection can be flaky and I need to make the logging fault tolerant. The logging task is already set not to fail the package, nor the parent, on failure. However the process isn't getting that far.
The package fails because the connection fails, not because the task fails.

The logic path of this package is Log Start > Do Stuff > Log Completion. In this particular example Log Start succeeded, as did Do Stuff. Log Completion failed because the server was suddenly unavailable.

The error message at SSIS report level is

  Log Status Completion Error: Failed to acquire connection 'Logging
  ADO NET'. Connection may not be configured correctly or you may not
  have the right permissions pm this connection.

All suggestions are welcome. (Thanks and Merry Christmas)


texts: SQL Server 2012 Crashes and service doesn't restart

texts:
We've been having an issue with our SQL server lately. It seems that the server will run out of memory and then crash. For some reason, the sql service wouldn't restart. I would have to go and manually restart it and then it's fine.

I am still trying to figure out the memory issue. We are using SQL Server 2012 SP1 Enterprise Edition. This is on a Windows 2012 Server. I saw alot of 701 out of memory error on the server right before it crashed and I'm still trying to figure out what is causing the memory error.

Why wouldn't the SQL Service be restarted automatically? Is it because of the memory issue?

Also, server has 20gb of memory and the max server memory is set to 16.5gb. I have noticed that the MEMORYCLERK_SQLOPTIMIZER is really high, around 11.5 gb right before the server crashes.

Anyone know why that would be so high? Is that what's causing the memory errors?

Any help will be appreciated.
Thanks.


texts: Single user mode while restoring database

texts: Is it necessary to put the database into single user mode before restoring a database? If so which one is more preferable putting the sql server into single user mode or putting the database only into single user mode before restoring ! Thank you.


texts: Is there a difference between making a composite foreign key for all columns or a foreign key for each column?

texts:
In general, is there a difference between making a composite foreign key for all columns or a foreign key for each column?

First approach

FOREIGN KEY(OrderId, CompanyId) REFERENCES ParentTable(OrderId, CompanyId)

Second approach

FOREIGN KEY(OrderId) REFERENCES ParentTable(OrderId)
FOREIGN KEY(CompanyId) REFERENCES ParentTable(CompanyId)


texts: What's the advantage of thread pool in MySQL/MariaDB?

texts:
MariaDB thread pool
MySQL thread pool
I've read official document of MySQL/MariaDB thread pool,but I'm still confused about it; As I understand, thread pool has two advantages:
    1. limit too many connections, too many connection could lead to too many context switches, but it seems option "--max-connections" also could limit connections~
    2.reuse connected old thread:when a new connection is coming, unused old thread could be reused,but it seems option "--thread_cache_size"  also could reuse old thread

so,what's the advantage of thread pool?


texts: Significance of has_unchecked_assembly_data due to a persisted computed column after changing compatibility level?

texts:
This database has a persisted computed column, which is computed by calling a .NET function. When changing compatibility from MSSQL 2005 to 2012, I was presented with error 10334, which looks more like a warning: it's telling me that it had to mark one of the tables as has_unchecked_assembly_data.

What it doesn't tell me is what to do about this. Should I leave it as is? Should I force the column to be recomputed somehow? How?

The documentation for alter assembly unchecked data lists several causes for this, and I think #1 is the only one that applies to this database. It also doesn't explain how to unmark such columns.


texts: Limit max CPU usage SQL SERVER with WSRM

texts:
I have a physical server running one instance of SQL Server.

I notice that quite often this server is running at 100% CPU usage.

My IT team is not happy about this, and suggested we reserve 2 of the 32 cores for the OS.

This works great, now max usage peak just under 90%.  Additionally, slow data retrieval from various users is no longer reported.

Is there any reason NOT to use WSRM (Windows System Resource Manager) in this way - instead of SQL Resource Governor?


texts: SQL 2005 query all databases and output to one table

texts:
Need a little help with the below:

I've created a query which basically will give me all the stored procedures in all the table for every database and I want to try and amend it so that the output queries are output to one table.

Any suggestions on how to improve this?

DECLARE @command varchar(1000)
SELECT @command =

'USE [?] select *,  DB_NAME() as DatabaseName
from sys.all_objects where type=''p'' and is_ms_shipped=0 '

EXEC sp_MSforeachdb @command


texts: SQL Server, ServicerBroker for asynchronized execution of stored procedures

texts:
I followed this tutorial:
https://gallery.technet.microsoft.com/scriptcenter/Using-Service-Broker-for-360c961a

and it is working for me,
However,
I don't understand some thing:
At PROCEDURE proc_BrokerTargetActivProc we have infinite loop: WHILE (1=1). Why ? After all, during creating queue we bind messages with this procedure:PROCEDURE_NAME = proc_BrokerTargetActivProc.

In addition, I am not sure If I correctly understand way of working it:
ExecuteProcedureAsync push to queue message with name of procedure to execute.
What now ? How does it work that BrokerTargetActivProc will be called with exactly one message ?

What about parameter MAX_QUEUE_READERS = 5 ?

Thank in advance,
Regards


texts: mariadb takes too much load though having sufficient RAM

texts:
I am facing more CPU usage for my app server. Server configuration is as below.

1> CPU: Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz 8 core
2> RAM: 32 GB
3> 600 GB SSD NVRAM
4> Mariadb version 10.3.17.
5> OS: Debian 9

I have run mysql tuning script to optimize server performance and add some parameter in my.cnf. But yet it's not creating big impact.

I can see that RAM used very less compare to CPU. How can I balance in both and improve server performance?

Here is my tuner script output tunner.
Here is current my.cnf output on my.cnf


texts: Search table data based on attribute and storing the output in nested table collection which is traversed through ref cursor

texts:
CREATE OR REPLACE PACKAGE BODY search_pkg IS
    /* PROCEDURE TO SEARCH PURCHASE DATA INPUT IS PURCHASE ID
         INPUT : PURCHASE_ID
         OUTPUT :  DATA IS STORED IN THE COLLECTION DECLARED IN PACKAGE
     */

        PROCEDURE purchase_search_by_id_prc (
            purchase_id IN purchases.pur_id%TYPE
        ) IS

            l_index1 NUMBER := 1;
    -- REFRENCE CURSOR TO TRAVERSE PURCHASE RECORD
            rc_purchase_info   SYS_REFCURSOR;
    -- RECORD VARIABLE TO STORE RECORD FETCH BY  REF CURSOR
            purchase_var       purchase_info;

        BEGIN

            OPEN rc_purchase_info FOR SELECT
                                         pur_id,
                                         pid,
                                         cid,
                                         qty,
                                         total_price
                                     FROM
                                         purchases
                                     WHERE
                                         pur_id = purchase_id;

            LOOP
    -- FETCHING PURCHASE INFORMATION IN RECORD rc_purchase_info
                FETCH rc_purchase_info INTO purchase_var;
                EXIT WHEN rc_purchase_info%notfound;
                purchase_coll.extend;
                purchase_coll(l_index1) := purchase_var;
                l_index1 := l_index1 + 1;

            END LOOP;
    -- CLOSING THE CURSOR

            CLOSE rc_purchase_info;
        END purchase_search_by_id_prc;
end search_pkg;

When calling this procedure from client, output is fetched correctly followed by same record again with empty values !! Can anybody suggest where is the problem?
Here's the client code to call the procedure declared and defined in the search package:
DECLARE
    l_purchase_id     purchases.pur_id%TYPE;
    r_purchase_info   search_pkg.purchase_info;
BEGIN
    dbms_output.put_line('SEARCH  PUCHASE INFORMATION BY PURCHASE ID:  ');
    dbms_output.put_line('ENTER PURCHASE ID :   ');
    l_purchase_id := '&purchase_id';
    search_pkg.purchase_search_by_id_prc(l_purchase_id);
    dbms_output.put_line('PURCHASE DETAILS FOR PURCHASE ID  :   ' || l_purchase_id);
    FOR index1 IN 1..search_pkg.purchase_coll.count LOOP
        r_purchase_info := search_pkg.purchase_coll(index1);
        dbms_output.put_line('Purchase Id : '
                             || r_purchase_info.purchase_id
                             || chr(9)
                             || 'Product Id : '
                             || r_purchase_info.product_id
                             || chr(9)
                             || 'Customer Id : '
                             || r_purchase_info.customer_id
                             || chr(9)
                             || 'QUANTITY : '
                             || r_purchase_info.quantity
                             || chr(9)
                             || 'TOTAL PRICE : '
                             || r_purchase_info.total_price);
    END LOOP;
   search_pkg.purchase_coll.DELETE;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('ERROR:' || sqlerrm);
END;


texts: How to select multiple rows using one query

texts:
Is it possible to select multiple rows using one query in SQL Server? For example, I have to run two queries to get my results as follows. How can I achieve the results using one query?
SELECT col1, col2
WHERE order_number = 'unique_value1'

SELECT col1, col2
WHERE order_number = 'unique_value2'


texts: Is there any safe way to use ephemeral storage (deleted on halt) for postgresql cluster?


texts: Is it possible to attach Oracle database files after a clean install

texts:
I had an Oracle 10g instance on my Win2008R2 server. Unfortunately, the server crashed and we had to re-install the operating system on the C: drive. The oracle files are on drives other than C.
The other drives (except C) are restored from disk backups. I don't have a dump file, I only have the Oracle data files such as USERS01.DBF and the Oracle control files.
Now I am trying to find a way to install Oracle again on the server but I am just wondering about how I can attach the current .DBF files to the new Oracle instance? Is this possible? If so, how can I attach the current .DBF files to Oracle?
Currently there is no "OracleService" on services.msc and no listener as well. But previos the previous Oracle instance was installed on the F drive so I still have the Oracle binaries and data files on the F drive.
I would really appreciate if you could provide any information about this.


texts: How to Update same table on deletion in MYSQL?

texts:
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.

Following is query I used to create table:

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I could successfully create my table:

mysql> DESCRIBE Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| name        | varchar(64)  | YES  |     | NULL    |       |
| designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | YES  | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Then I inserted some tuple in Employee table using following query.

mysql> INSERT INTO Employee VALUES
    -> ("1", "A", "OWNER",  NULL),
    -> ("2", "B", "BOSS",   "1"),
    -> ("3", "C", "WORKER", "2"),
    -> ("4", "D", "BOSS",   "2"),
    -> ("5", "E", "WORKER", "4"),
    -> ("6", "F", "WORKER", "1"),
    -> ("7", "G", "WORKER", "4")
    -> ;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

Now I have following hierarchical relation among the rows in table:

        A
       / \
      B   F
     / \
    C   D
       / \
      G   E

Following is Select statement for table:

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
+-----+------+-------------+------+
7 rows in set (0.00 sec)

Now, I want to impose a constraint like : If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS). e.g. If I delete D then B Become BOSS of G and E.

For that I also written a Trigger as follows:

mysql>  DELIMITER $$
mysql>        CREATE
    ->        TRIGGER `Employee_before_delete` BEFORE DELETE
    ->          ON `Employee`
    ->          FOR EACH ROW BEGIN
    ->          UPDATE Employee
    ->          SET MSSN=old.MSSN
    ->          WHERE MSSN=old.MSSN;
    ->        END$$
Query OK, 0 rows affected (0.07 sec)

mysql>        DELIMITER ;

But When I perform some deletion:

mysql> DELETE FROM Employee WHERE SSN='4';
ERROR 1442 (HY000): Can't update table 'Employee' in stored function/trigger
because it is already used by statement which invoked this stored
function/trigger.

I learn here that this trigger is not possible because In MySQL triggers can't manipulate the table they are assigned to.

Is there some other possible way to do this? Can some one suggest me other method ? A suggestion would be enough but should be efficient.

EDIT: Is it possible using Nested Query?
I also asked it here.


texts:
We need to reindex a Clustered Primary Key on quite a large table. Here is the definition of the table:

Data Size: 22GB
Index Size: 34GB
Total Non-Clustered Indexes: 3
Row Count: 54 Million
Data File Free Space: Minimal
Drive Free Space: 3GB

The indexes are all over 90% fragmented.

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is? Since there is insufficient free space in the data file or disk drive to rebuild the index, I wrote a script which would:

Set the DB Recovery mode to Simple (to avoid growing the Log file too large)
Drop the non-clustered indexes
Rebuild the PK
Recreate the non-clustered indexes
Set the DB Recovery mode back to Full

I did a dummy run on a backup of the database on another server, which took 32 minutes in total, which is acceptable for a maintenance window for us.

The benefits I can see of dropping the non-clustered indexes are:

The DB file does not need to autogrow to rebuild the PK as space would have been created when the indexes are dropped
We will not need to shrink the DB file again (and cause additional fragmentation) following the maintenance
The non-clustered indexes will also be defragmented when they are rebuilt.

This script suits our needs for now, but what is considered best practice usually for a table of this size assuming we had infinite available space?


texts: How do I write IPv4 slash notation in IPV6

texts:
I have the following IPv4 address and mash in my Postgres hba.conf file:

192.168.0.0/24

Which works great when connecting my other Mac perfectly as long as I connect to the PostgreSQL database server directly using it's IPv4 address. But how do I convert it to IPv6?


texts: Three tables with some matching data and some not - want to data from all three

texts:
I have three tables

Costs (47 rows)
Budgets (60 rows)
Committed Costs (12 rows)

All three have the same 2 fields and than a number
JobNumber, CostCode, (either costs, budget, commitedcosts)

They all have the same JobNumber and they all CostCode in common.  However they also each have unique CostCodes.  I need to run a query that would get me results from all three tables like this:

JobNumber, CostCode, Costs, Budgets, CommitedCosts

Currently I am using outer joins on the three tables and only getting 47 results since the Costs table is the first table selected from.


texts: Database recovery pending on external drive

texts:
I have database with mdf and ldf file residing on external drive. There was a long powercut and the database restarted. Now the database has gone for recovery pending. The database is around 600 gb.

I have tried the following commands

ALTER DATABASE ‘DATBASE NAME’ SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE ‘DATBASE NAME’ SET ONLINE WITH ROLLBACK IMMEDIATE

but i am getting the following error

  Msg 5120, Level 16, State 101, Line 4 Unable to open the physical file
  "G:\SQL_DATA\TEST_NEW.mdf". Operating system error 5: "5(Access is
  denied.)". File activation failure. The physical file name
  "G:\SQL_DATA\TEST_NEW_log.ldf" may be incorrect. Msg 5181, Level 16,
  State 5, Line 4 Could not restart database "TEST_NEW". Reverting to
  the previous status. Msg 5069, Level 16, State 1, Line 4 ALTER
  DATABASE statement failed.

I have tried all options.

Do any body has a working solution. Please share it will save my day.


texts: SQL Server falling below 5000 free pages

texts:
I have set alerts on two SQL Server 2005 instances I have in production.
One of those alerts is sent whenever the Free Pages performance counter falls below the 5000 pages mark.

Both servers normally operate with more than 300000 free pages, but sometimes one of these servers momentarily goes well below 2000 or even 1000 free pages and then goes up again. Again, this only happens with one of the servers.

As far as I know, this behavior is not degrading the performance of this server, because it looks to me as if whenever the server is going to starve on free pages, it frees up older data pages from the buffers and populates the Free Buffer List. Theoretically speaking, at least.

What I don't understand is why the lazywriter isn't maintaining a larger free buffer list and is 'waiting' for SQL Server to get so short on free pages before freeing up more buffers.

So my question is actually twofold:
1. How can I determine what/who is causing SQL Server to become short on free pages?
2. How can I inspect what is actually going on here?


texts:
I have an existing SQL Server (2014) setup using Always On Availability Groups. All the documentation says I should not try to install replication (aka distributor role) on any of the servers participating in the Always On Availability Groups.

Why does the documentation warn against doing that, will it break Always On? Does Always On prevent replication from working? Or is there some other reason?


texts:
I need to calculate reach, grouped by different criterions, and would prefer to keep everything in my database. I'm looking for either a pre-built bitset implementation for PostgreSQL, or pointers on how I may achieve my goal.

What I'm currently doing is storing serialized Java instances of EWAHCompressedBitSet in bytea columns, but this means I have to round-trip to a Java process to continue my calculations.

Ideally, I'd like to be able to do the following:

-- Unique people reached during a time window
SELECT   service_name, last_updated_at, bitset_or(followers_bs)
FROM     followers
GROUP BY service_name, last_updated_at

I have used a commercial solution (Truviso) that had such a feature, and it was based on PostgreSQL as well.

Note that I do have a table that has the data in an uncompressed format, but because the data is so huge (10M rows and counting), JOINing on it simply takes too much time than I want to devote to it.


texts: How to resolve "logical replication target relation schema.table is missing some replicated columns" error in postgresql logical replication?

texts:
I'm getting

"logical replication target relation schema.table is missing some replicated columns"

error in logical replication. I'm using all table replication. Due to some changes I wanted to delete a column from both subscriber and publisher. I dropped column from subscriber end first and then from publisher(it should've been other way around). But deletion from publisher took time and logical replication broke.
I tried adding columns at both end in every way. I also tried refreshing the publication at subscriber end. I also enabled and disabled the subscription. Still the postgres gives same error.
I'm using PostgreSQL 12.3.
How do I start the logical replication in such case without any data loss?
-------------UPDATE---------------
I resolved it by dropping the subscription and recreated it with copy_data = false. Later I copied the data missing on the subscriber side.
This was quite a headache and a long way to do this. Still looking for optimal solutions for this problem. Optimal solutions for this are welcomed.


texts: How to pull rows only from the weekends?


texts: MySQL Query One Table/Column With Value While Another Table/Column Empty

texts:
I set up a test database named ‘test’ that contains 2 tables, tbl1 and tbl2.  In plain language, I’m trying to return the name of all entries in the package2 column of tbl2 that do not exist in the package1 column of tbl1.

I’ve tried all different sorts of queries with no success as shown below.  How can I accomplish this?

Returns nothing:

SELECT *
FROM tbl1,tbl2
WHERE NOT tbl1.package1 != tbl2.package2
OR (tbl1.package1 IS NULL AND tbl1.package1 IS NOT NULL)

Returns nothing:

SELECT *
FROM tbl1,tbl2
WHERE (tbl1.package1 IS NULL AND tbl2.package2 IS NOT NULL)

Database setup

CREATE TABLE IF NOT EXISTS `tbl1` (
  `host` varchar(10) NOT NULL,
  `package1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(4, 'd');

-- Indexes for table `tbl1`
ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

-- Indexes for table `tbl2`
ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;