sybase format datetime yyyymmdd This seems like something trivial would be easy, but I couldn't find a good way to do it.

This is what I came up with:

select convert(numeric,
                    (select convert(varchar,datepart(yy,getdate())) +
                         right('0' + convert(varchar,datepart(mm,getdate())), 2) +
                         right('0' + convert(varchar,datepart(dd,getdate())), 2)
                    ))

EDIT: I know it doesn't need to have a subquery like that, but ignore that =p

Much simpler: CONVERT(varchar(8),GETDATE(),112)

You can find a full list of CONVERT styles in Sybase BOL, although the explanation of each style isn't particularly clear (and is actually flat wrong for style 12 & 112). The Complete Sybase ASE Reference Guide includes a full list of styles with examples. (Please note that the complete guide is externally published, not from Sybase. Also I'm not affiliated with the book or the publisher, just mentioning it because everyone working with ASE should have a copy of it.)


How do I get the count for time periods? I have a table with the following columns.

[ComputerGroupid], [Computerid], [Userid], [loginTime], [logoffTime]

And my requirement is to get the table with the below information

[ComputerGroupid], [StartTime], [endTime], [numberOfcomputerBeingUsed]

But I am not sure how to merge the date ranges when they overlap and also when the number of computers being used are equal?

edit:
Some more details.
What I am looking for is to get a table of computer usage for a group of computers. so I am expecting to create a table where when ordered by time the [numberOfcomputerBeingUsed] column goes up as people log into computers and the [StartTime] and [endTime] columns will match up.

eg: if 4 people come into a room and login at about the same time, for an hour, and one person comes in and logs on for 5 mins and then leaves. I would expect:

 [ComputerGroupid] | [Computerid] | [Userid] | [loginTime] | [logoffTime]
 A                 | 1            | 1        | 09:00       | 10:00
 A                 | 2            | 2        | 09:01       | 10:01
 A                 | 3            | 3        | 09:02       | 10:04
 A                 | 4            | 4        | 09:03       | 10:03
 A                 | 5            | 5        | 09:10       | 09:15

  [StartTime]  |  [endTime]  |  [numberOfcomputerBeingUsed]
  09:00        |  09:01      |  1
  09:01        |  09:02      |  2
  09:02        |  09:03      |  3
  09:03        |  09:04      |  4
  09:10        |  09:15      |  5
  09:15        |  10:00      |  4
  10:00        |  10:01      |  3
  10:01        |  10:02      |  2
  10:02        |  10:03      |  1
  10:03        |  10:04      |  0

This is what worked for me in the end.

SELECT
     [StartTime].[PossibleTime] AS [StartTime]
    ,[EndTime].[PossibleTime] AS [EndTime]
    ,(SELECT COUNT(1)
       FROM [Log] AS [L]
       WHERE [StartTime].[PossibleTime] >= [L].[loginTime]
       AND [EndTime].[PossibleTime] <= [L].[logoutTime]
     ) AS [UserCount]
SELECT ROW_NUMBER() OVER (ORDER BY [PossibleTime]) AS [ROWNUM]
FROM (
    SELECT [loginTime] AS [PossibleTime]
    FROM [Log]
    UNION ALL
    SELECT [logoutTime] AS [PossibleTime]
    FROM [Log]
   ) AS [EndTime]
INNER JOIN
SELECT ROW_NUMBER() OVER (ORDER BY [PossibleTime]) AS [ROWNUM]
FROM (
    SELECT [loginTime] AS [PossibleTime]
    FROM [Log]
    UNION ALL
    SELECT [logoutTime] AS [PossibleTime]
    FROM [Log]
) AS [StartTime]
ON [StartTime].[ROWNUM] = [EndTime].[ROWNUM]
ORDER BY [StartTime].[PossibleTime], [EndTime].[PossibleTime]


How can I best combine 3 queries into 1? I need the following queries combined into one query that returns the following from all results from the first query you see below (Right now I load the first query into an array in php then run two queries against each result):

post_id from wp_postmeta
brandLogoName from brand

Here are the tables and their relevant columns:

merchant - brandIdentifier, locationIdentifier, locationName, postalCode
wp_postmeta - post_id, meta_key, meta_value
brand - brandIdentifier, brandLogoName

Currently I run the following and load it into an array:

SELECT `brandIdentifier`,`locationIdentifier`,`locationName` FROM merchant WHERE postalCode = '90210';
GROUP BY `locationName` LIMIT 0, 5

Which returns:

brandIdentifier  locationIdentifier locationName
11111111         666666             ABC
22222222         777777             DEF
33333333         888888             GHI
44444444         999999             JKL
55555555         000000             MNO

Then for each line in the array I run the following (using the first result as an example):

SELECT post_id FROM wp_postmeta WHERE meta_key = 'locationIdentifier' AND meta_value = '666666' LIMIT 1;

Which returns:

post_id
123456

Then for each line in the array I run the following (using the first result as an example):

SELECT brandLogoName FROM brand WHERE brandIdentifier = '11111111' LIMIT 1;

Which returns:

brandLogoName
file.png

I know joins are required. I just don't know how to join to a result set. Can anyone point me in the right direction on writing this query? Thanks.

SUBSET QUERY #1
SELECT
    merchant.locationName,
    wp_postmeta_location.meta_value,
    wp_postmeta_location.post_id,
    merchant.brandIdentifier,
    wp_postmeta_brand.meta_value
FROM
    (
        SELECT `brandIdentifier`,`locationIdentifier`,`locationName`
        FROM merchant WHERE postalCode='90210'
        GROUP BY `locationName`,`locationIdentifier`
    ) merchant
    INNER JOIN
    (
        SELECT post_id,meta_value FROM wp_postmeta
        WHERE meta_key = 'locationIdentifier'
    ) wp_postmeta_location
    ON merchant.locationIdentifier = wp_postmeta_location.meta_value
    INNER JOIN
    (
        SELECT post_id,meta_value FROM wp_postmeta
        WHERE meta_key = 'brandIdentifier'
    ) wp_postmeta_brand
    ON
    (merchant.brandIdentifier = wp_postmeta_brand.meta_value AND
    wp_postmeta_location.post_id = wp_postmeta_brand.post_id)
;


Oracle DB not generating AWR Sysaux tablespace is almost full . Could this be the reason AWR snapshot is not generated ? Although I do have the AutoExtend ON for SYSAUX tablespace.

If sysaux hasn't reached it's max physical growth limit, then it is not likely the reason AWR isn't working. AWR can be disabled a number of ways, for good reason. See this link for details:

http://www.dba-oracle.com/t_awr_remove_disable.htm

https://grepora.com/2019/06/05/oracle-disable-awr-and-prevent-to-violate-diagnostic-tuning/#:~:text=Disable%20AWR%20with%20Oracle%20provided%20script&text=To%20install%2C%20run%20the%20package,disable%20the%20AWR%20related%20advisors.

If someone disabled it, you should confirm the reason why before turning it back on. It is an extra cost feature that many systems are not licensed for.


SQL Server 08: Union over while I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out!
I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the fact that the timeslot count is the same for every date possible, they aren't duplicated for every date. Instead, it is recorded which timeslots are taken on what date. Those timeslots are then excluded from the query.
Due to the way the data is stored, I'm having to use a while loop to start on a given date, and query for a given number of days after. However, this is causing the return of multiple result sets. This would be OK, but the procedure will be called by Entity Framework, which does not support multiple result sets returning.
I've thought about storing it all in a temp table, but that option is out of the window due to a people problem: my dba is on holiday and I don't have an account with sufficient privileges to drop temp tables (or so it appears), and this needs doing before he'll get back.
Is there any way I can amalgamate all these result sets in to one? Every result set has the same structure.
EDIT TO ADD TABLE STRUCTURE (PKs in italics, becasue I can't find how to underline)
TimeSlotGroup
UKTNN, TimeSlotGroupID, TimeSlotGroupDesc
TimeSlotGroupTimeSlots
UKTNN, TimeSlotID, TimeSlotGroupID, TimeSlotDesc
BookedDates
UKTNN, TimeSlotGroupID, TimeSlotID, Date
I'm returning currently:
ResultSet
UKTNN, TimeSlotGroupID, TimeSlotID, TimeSlotGroupDesc, TimeSlotDesc, Date
It has duplicated data in it, but I'm OK with that right now
My existing query:
WHILE(@LoopDate <= DATEADD(DAY, @NumDays, @ArrivalDate))
    BEGIN
    SELECT TSG.TimeSlotGroupID,
           TSG.TimeSlotGroupDesc,
           TS.TimeSlotID,
           TS.TimeSlotDescription,
           TS.AdditionalCost,
           @LoopDate Date,
           @UKTNN UKTNN
    FROM FittingCentreBranchTimeSlotGroups TSG, FittingCentreTimeSlotGroupTimeSlots TS
    WHERE TSG.UKTNN = @UKTNN AND
          TS.UKTNN = @UKTNN AND
          TS.TimeSlotGroupID = TSG.TimeSlotGroupID AND
          NOT EXISTS(
                SELECT *
                FROM BookedFittingDates BFD
                WHERE BFD.TimeSlotGroupID = TS.TimeSlotGroupID AND
                      BFD.TimeSlotID = TS.TimeSlotID AND
                      BFD.UKTNN = @UKTNN AND
                      BFD.Date = @LoopDate)
    ORDER BY TimeSlotGroupID ASC, TS.TimeSlotID ASC

    SET @LoopDate = DATEADD(DAY, 1, @LoopDate)
END

A few points:

1 - This is indeed a bad idea.

2 - Your syntax should be updated.  Using implicit JOINs is generally bad since it can lead to a cartesian product if you aren't careful.

3 - Have you looked into using a recursive CTE?

4 - It also looks to me like the row-by-row could be unneeded anyways.  Your date isn't used anywhere in your actual query except to be passed as a field and checked against the subexpression.

5 - Does this return a single row per date?

6 - Finally, #temp tables are dropped when the connection is closed.  You don't need to drop them manually.  If you can create them, as long as you don't make another with the same name in the same connection you won't need to drop it manually.


UPDATE SET REPLACE() matches but does not change I've seen another post about this exact thing but it's 1-1/2 years old so I thought I'd make my own...besides, the other post did not help me.  What I am trying to do is pretty straight-forward.

To be on the safe side, I tried a simple UPDATE statement on a different table, which was successful.  So, here's my problem:

mysql> UPDATE bugs_fulltext SET short_desc =
REPLACE(short_desc,'I don\'t know why my previous comment means.',
'I don\'t know what my previous comment means.') WHERE bug_id=452;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

I'm not using wildcards nor am I using a LIKE statement.  I am telling it which record to update.  Why isn't my text changed?

Look carefully at the query's output

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The expression Rows matched is based on WHERE bug_id=452;. That matched.

When you executed it, the change evidently failed because the short_desc column didn't change because short_desc does not equal 'I don\'t know why my previous comment means.'

To verify that run this:

SELECT REPLACE(short_desc,'I don\'t know why my previous comment means.',
'I don\'t know what my previous comment means.') replace_result
FROM bugs_fulltext WHERE bug_id=452;

If you getback the old value, then the old value was not in the row to begin with.

You should just directly update it (overwrite it):

UPDATE bugs_fulltext
SET short_desc = 'I don\'t know what my previous comment means.'
WHERE bug_id=452;


Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result? I have a table with below structure and it's data :
create table test_table
(
Item_index   int,
Item_name    varchar(50)
)

insert into test_table (Item_index,Item_name) values (0,'A')
insert into test_table (Item_index,Item_name) values (1,'B')
insert into test_table (Item_index,Item_name) values (0,'C')
insert into test_table (Item_index,Item_name) values (1,'D')
insert into test_table (Item_index,Item_name) values (0,'E')

I want to know why changing the column in order by section of the query , changes the result? In QUERY-1 , I used item_index and in the QUERY-2 I used item_name column in the order by section. I thought that both queries must generate the same result because I used item_index in both queries for partitioning! I'm completely confused now ! why the order by column should affect the final result?
QUERY-1:
select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_index) new_column
from test_table t;

RESULT:
Item_index  Item_name     new_column
----------- --------------------------
0           A                E
0           C                E
0           E                E
1           D                D
1           B                D

QUERY-2:
select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_name) new_column
from test_table t;

RESULT:
Item_index  Item_name  new_column
----------- -----------------------
0           A             A
0           C             C
0           E             E
1           B             B
1           D             D

Can anybody explain how exactly these two queries are being executed and why each of them generates different result?
Thanks in advance

The explanation for the different results is given in SQL Server's documentation about window functions, the ORDER BY section:

ORDER BY
Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
If it is specified, and a ROWS/RANGE is not specified, then default
RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for
window frame by the functions that can accept optional ROWS/RANGE
specification (for example min or max).

Note that MIN() and MAX() window aggregates accept an optional ROWS or RANGE specification.
When there is no such specification, they calculate the MIN and MAX over the whole partition. When there is, they calculate the MIN or MAX over the specified range. Since your two queries specify different orders/ranges, they yield different results.
If you wanted the MAX over the whole partition, then remove the ORDER BY range:

If it is not specified, the default order is ASC and window function will use all rows in partition.


Default instance name different than server name I just started a new gig, and have discovered that two instances of SQL 2012 (SP2), installed on Core Server 2012 R2, have a different name than the server it's running on. They are both virtual servers. For example, the server name is Server2 and Server3 (can RDP to it and can connect to it multiple ways using this name) and can even connect with SSMS from other machines. However, when I run @@Servername, it returns "CO-SQLTEMPLATE"--on BOTH servers. The reason this is troublesome is because these two instances participate in AlwaysOn replication, and there are several problems...the most glaring one is automated backups will not work. Using the HADR systables and functions, it either shows that none of the replicas are "primary" or all of them are (!!!). Also, it is not clear that the replication is actually working. In a couple of the AGs, it shows that the databases are synchronized with the Primary (Server1), but I don't trust that since there are essentially 2 instances with identical names.

I've been hunting around on Google, and cannot find a source that indicates where SQL Server gets its instance name from during installation. I've never seen this issue before, and it is truly bizarre.

This is my last ditch effort to try to find a solution before I just wipe the 2 servers clean and start from scratch (which, obviously, I'd rather not do).

Sounds like someone thought it would be easy to create a "go-to" SQL Server VM that you can clone. Nobody is bothering to change the standard CO-SQLTEMPLATE SQL Server name. If replication is actually working, I would question whether it is working properly because the SQL Server name should match the VM/Physical server name--minus the instance. What's worse, people are probably connecting to SQL Server using the VM/Physical name and don't know about CO-SQLTEMPLATE.

If you're not going to trash and rebuild, you will probably want to rename SQL Server to match the Windows Server name where it's installed:

exec sp_dropserver 'CO-SQLTEMPLATE'
exec sp_addserver 'MyWIndowsServerName'.

I wouldn't trust that SQL Server setup. If you can, I'd check the Availability Groups folder to see which if the replicated database says myDB (Primary) or myDB (Secondary). Then on the server with myDB (Primary) break and rebuild the AlwaysOn replication and rebuild the replication with the AlwaysOn Wizard. (However, I don't add the listener with the wizard as that part seems buggy to me. I add the listener separate).

But before that, if the servers are as messed up as you say, I'd validate that the Windows cluster is even working properly via Failover Manager. And, validate that DNS actually contains the names for your Windows Servers and your Listener (and possibly a witness)--if you have one.

Caution:
Your servers might even be worse than you imagined with users connecting to both instances of CO-SQLTEMPLATE on two different servers as they both "could" represent two completely different sets of data and are completely unrelated. Because, after all, they used the handy-dandy VM template to make their SQL Server life easier.


update cc_info dst, ( select a_msisdn
                      from cc_info
                      where DATE(incoming_timestamp) ='2019-08-04'
                        AND status = 3
                      group by a_msisdn
                      having count(*) > 100 ) src
set dst.status = 4
where dst.a_msisdn = src.a_msisdn


how to add data compression to a primary key of a table? We work with tables with over 300GB of size, a small percentage of them tables use sparse columns
although that doesn't always agree with CPU utilization as this question shows.

For the tables that don't use sparse columns I want to implement data compression - either page or row
depending on the findings of procedure sp_estimate_data_compression_savings
or this script:

T-SQL Script for Estimating Compression Savings

I have a routine that generate the create index scripts that works fine.

when I create this table as an example:

IF OBJECT_ID('[DBO].[t1]') IS NOT NULL
DROP TABLE [DBO].[t1]
GO
CREATE TABLE [DBO].[t1] (
[a]  INT                              NOT NULL,
CONSTRAINT   [PK__t1__3BD0198E286DEFE4]
PRIMARY KEY CLUSTERED    ([a] asc))

My create index scripts gives me:

USE [ontime_new_test];
ALTER TABLE [dbo].[t1]
ADD  CONSTRAINT [PK__t1__3BD0198E286DEFE4]
PRIMARY KEY CLUSTERED (  [a] ASC  )
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  ,
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
DATA_COMPRESSION=NONE, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

Question:

Is there a way to apply a data compression to this table without dropping the primary key?

Basically I want to get this done (note the data_compression=PAGE) without having to drop any constraint first:

USE [ontime_new_test];
ALTER TABLE [dbo].[t1]
ADD  CONSTRAINT [PK__t1__3BD0198E286DEFE4]
PRIMARY KEY CLUSTERED (  [a] ASC  )
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  ,
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

Is there a way to apply a data compression to this table without dropping the primary key?

Yes you just need to run below command to rebuild the clustered index (in your case it is also PK):

ALTER INDEX [PK__t1__3BD0198E286DEFE4] on TABLE [t1]
REBUILD with (ONLINE = ON, DATA_COMPRESSION = PAGE)

"ONLINE = ON" clause can help greatly reduce locking of table that is being rebuilt


Update with default values in case clause I have a single table like

CREATE TABLE Mytable (ID int identity, Name nvarchar(10));
GO
INSERT INTO MyTable (Name) VALUES ('test1');
INSERT INTO MyTable (Name) VALUES ('test2');
GO
ALTER TABLE MyTable ADD CONSTRAINT DF_Name DEFAULT('test') FOR Name;

Now I want to update my Name column like this:

Update MyTable
set name = case ID when 1 then DEFAULT END;

But I am getting this error:

  Incorrect syntax near the keyword 'DEFAULT'

How can I use UPDATE with DEFAULT statement in the CASE clause?

You can't.

The relevant bit of the grammar for update is

SET column_name = { expression | default | null }

You can only use the default keyword in place of an expression, not inside an expression. So you would either need to just use the value of the default directly (could be looked up from the system views to be dynamic) or spilt it into two update statements with appropriate mutually exclusive where clauses - one using default and the second using an expression - if you really want to use that keyword.


How arrange columns in sp_whoisactive by Adam Machanic I really like using sp_whoisactive v11.11 by Adam Machanic. But when then info is displayed, the DB name is at the very end. I would like it to in the very first columns so I don't have to scroll everytime... Does anyone have suggestions to arrange columns

Edit the line with @output_column_list declared on it and put [database_name] between [dd%] and [session_id].

When I scripted it out it was on line 110.


Trigger with multipe IF conditions doesn't trigger I am trying to update the table latest_activities with the latest activity a employee had.

An activity has multiple conditions to be considered as the latest activity.

The reason why I don't do a JOIN to get the latest activity from the table activity is because of performance and a sub-optimal database design I cannot change.

This is the trigger I have made. It doesn't add any records to latest_activities. I suspect the problem is that there are a difference on IF() and IF. Also I am not sure that my REPLACE INTO is correct

DELIMITER $$
CREATE TRIGGER update_latest_activity
    AFTER INSERT ON activity
    FOR EACH ROW
BEGIN
    IF NEW.log = 0
        AND (NEW.type = 'hired'
        OR (NEW.type = 'note'
        AND JSON_VALID(NEW.data) = 1
        AND NEW.data->>'$.status' = 1))
    THEN
        REPLACE INTO
            latest_activities (employee_id, activity_id)
        SELECT
            pivot.employee_id AS employee_id,
            pivot.activity_id AS activity_id
        FROM
            pivot
        WHERE
            pivot.activity_id = NEW.id
            AND pivot.type LIKE '%Employee';
    END IF;
END$$
DELIMITER ;

UPDATE

After debugging (thanks to @akina) I found that NEW.id for some reason isn't working when used in the REPLACE INTO statement pivot.activity_id = NEW.id. Hard-coding the value will work, so the query is correct.

PROBLEM SOLVED

The problem was obvious when I took a step back. Trigger should be on the pivot table because the relationship is first created there AFTER the data has been inserted into the activity table facepalm.

Debug your trigger.

Create service table.

CREATE TABLE service (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                      step INT,
                      data TEXT,
                      dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Add tracing info save (example code).

CREATE TRIGGER update_latest_activity
    AFTER INSERT ON activity
    FOR EACH ROW
BEGIN
    INSERT INTO service (step, data) VALUES (1, NEW.log);
    INSERT INTO service (step, data) VALUES (2, NEW.log = 0);
    INSERT INTO service (step, data) VALUES (3, NEW.type);
    INSERT INTO service (step, data) VALUES (4, NEW.data);
    INSERT INTO service (step, data) VALUES (5, JSON_VALID(NEW.data));
    INSERT INTO service (step, data) VALUES (6, NEW.data->>'$.status');
    INSERT INTO service (step, data) VALUES (7, NEW.log = 0
                                                AND (NEW.type = 'hired'
                                                OR (NEW.type = 'note'
                                                    AND JSON_VALID(NEW.data) = 1
                                                    AND NEW.data->>'$.status' = 1)));
    IF NEW.log = 0
        AND (NEW.type = 'hired'
        OR (NEW.type = 'note'
        AND JSON_VALID(NEW.data) = 1
        AND NEW.data->>'$.status' = 1))
    THEN
        INSERT INTO service (step, data) VALUES (8, 'Execute THEN branch');
        REPLACE INTO
            latest_activities (employee_id, activity_id)
        SELECT
            pivot.employee_id AS employee_id,
            pivot.activity_id AS activity_id
        FROM
            pivot
        WHERE
            pivot.activity_id = NEW.id
            AND pivot.type LIKE '%Employee';
    END IF;
END

Insert record which must (as you think) to perform the action.

Analyse the info in service table.


Does my entity-relationship diagram reflect the relevant business rules? I'm new to database design and I need some critiques, feedback, suggestions, advice, etc. regarding a) some business rules, b) the entity-relationship diagram I have developed to represent them, and c) whether or not they "match".

Business rules

The relevant business rules are as follow:

Users can rate multiple businesses
A business can fall under more than one category
Users can comment on multiple businesses
Businesses can have zero to many ratings
Businesses can have zero to many comments
A business can only be located in one and only one city
A city can have multiple businesses
A category can have multiple businesses
Users can have one or more checkins for businesses
Businesses can have zero to many checkins

My entity-relationship diagram

And I have represented them in the diagram below:

First - I agree with S4V1N completely on the Category-Business link - you need a table in between, as each business can have many categories, and each category could be tied to many businesses.

However, as the rules are currently stated, I don't think you need a table between City and Business. I think you've got City laid out wrong.

Currently, each business must be in one and only one city, and each city can be home to multiple businesses. For that, the City table should not have a B_ID column, as a city is tied to no one business. (I also see no reason for it to have a U_ID column, as no relationship between User and City has been established. I suspect a cut-and-paste issue.) Business, however, should have a CI_ID for the city it's in.

Also - the way I read the diagram, it looks like each user must have at least one Comment, Rating, and Checkin. The rules explicitly indicate "one or more" checkins per user, so that appears correct. However, it doesn't look to me like the user is required to have any comments or ratings - so shouldn't those two be linked with the "0 or more" connector?


Error when trying to access linked server from another machine I have created a linked server from a Excel source and when I query or test the connection from the locally from the server everything is good but if I try the same from another machine with DBMS I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

The test connection to the linked server failed.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Source" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Source". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Server: Windows 2008
SQL: Server 2008 R2

The SQL service is running fine and the user attached has full administrator access.

Any help is appreciated.

You put no details about the involved paths in your post so I'm just making wild guesses.

You can run the query from any machine in your network but the path you save the excel file into must be accessible by the user executing the RDBMS service (usually local network or local service but may be customized at will).

The path you put into the linked server configuration is always local to the server.  Even when running the query from a client machine the excel file must be on the server. (Well, you may specify a UNC network path when creating the linked server but that  would make the whole thing more 'funny' to debug and handle because of permissions.)


How can I install pgtap on rds postgresql? I am using postgresql 10.7 under AWS RDS.

I downloaded pgtap-1.0.0.zip from https://pgxn.org/dist/pgtap/ because I saw an article in AWS forums saying you can just run pgtap.sql yourself.

However, in the zip file there is no pgtap.sql but a pgtap.sql.in...

I am using a Windows client. Tried to run make in wsl but complains about "pg_config: not found" while there is a pg_config.EXE in my PATH.

It did produce a pgtap.sql but running it fails on view "tap_funky" because it tries to use a non existing column pg_catalog.pg_proc.prokind

I replaced the missing prokind column by a hard coded "f" for (normal function) and got something apparently working but it creates everything in the public schema.

I tried to run that script in a specific schema "pgtap" but it failed straight way. Is there a proper way to do this?

As of May 2019 pgTAP is an extension in RDS postgres version 11.2, (not version 10,7 however) according to
https://aws.amazon.com/about-aws/whats-new/2019/05/amazon-rds-postgresql-supports-minor-version-112/


SQL Server Always-On Availability Groups LeaseTimeout One of my AG environments encountered the error

  The lease between availability group XXXXXXX and the Windows Server
  Failover Cluster has expired. A connectivity issue occurred between
  the instance of SQL Server and the Windows Server Failover Cluster. To
  determine whether the availability group is failing over correctly,
  check the corresponding availability group resource in the Windows
  Server Failover Cluster.

Now I know why this issue occurred however I want to understand why the availability group crashed instead of failing over to another node i.e. primary role changed to resolving was the next error. It took around 1 minute for the availability group to come back online automatically on the same node. Surely it should failover to the next available node? This is a 5 node cluster all having votes within quorum.

Is  this just the way SQL Availability groups work? i.e. if the AG loses connection to the windows failover cluster dll then it cannot failover and crashes to avoid a split brain scenario?

Where do I configure this default behavior of it trying to reconnect first before failing over to another node?

If you'd like to know why, the answer will be in the cluster log. If you need help looking over it, please upload the primary node's (at the time) cluster log.

Clustering makes the decisions when it comes to FCIs and AGs with SQL Server. The cluster is responsible for metadata updates and changes, health checking, resource placements, etc., and most people don't change the by default cluster behavior which is to restart the resource after a period of time.

Since in your case it connected back up one minute later, the best guess without the cluster log is that this is what occurred, especially since you've stated that the default clustering settings have not been changed.

More Info 1: https://docs.microsoft.com/en-us/previous-versions/orphan-topics/ws.11/dn281898(v=ws.11)?redirectedfrom=MSDN

More Info 2: https://support.microsoft.com/en-us/help/228923/cluster-resources-can-be-configured-to-restart-automatically

  so does restarting the resource mean restarting the SQL instance?

No, it restarts the AG Resource which is not a SQL Server instance.

  What I dont understand is, if its set to restart the SQL service before trying a failover (when it comes to a leasetimeout) - wouldn't a restart of the service/resource cause a failover anyway?

No, because it's not the instance. The availability group tied to that resource will stop synchronizing and will go into a resolving state but the instance itself will not have any issues.


Type with nested table in oracle I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a table called corrections. All my basic objects are compiling fine, just trying to build the assignment_tbl is a problem as im not sure how to use the nested table statement.

CREATE TYPE correction_info_objtyp AS OBJECT
(
   correction_date   DATE,
   raised_by         VARCHAR(50),
   problem           VARCHAR(255)
);

CREATE TYPE correction_info_ntabtyp AS TABLE OF correction_info_objtyp;

CREATE TYPE assignment_status_info_objtyp UNDER status_info_obj
(
   corrections     correction_info_ntabtyp
);

CREATE table assignement_tbl
(
  assignment_id       NUMBER(6),
  assignment_title    VARCHAR(40),
  elements            elements_v,
  assignment_status   assignment_status_info_objtyp
)NESTED TYPE assignment_status STORE AS assign;

-- I know this nested type isn't going to work because it's not a table,
-- But inside the assignment_status_info_objtyp is correction_info_ntabtyp which is
-- a table and needs the nested type statement

I've tried to follow the example here with the planets and satelites, where they have a nested table in a nested table, but for my situation i have a nested table in a type because I only ever want one instance of assignment_status in the assignment.

Any help, hints or even a link to an example or a page in a book would be of fantastic use.

Exhausted first 20 results of Google already.

I managed to create an array of size one instead of using a object type. Through thorough reading I found no mention of whether a nested table within a type was possible, although  varrays of nested tables are; Nested tables of varrays are also allowed.

CREATE TYPE correction_info_objtyp AS OBJECT
(
   correction_date   DATE,
   raised_by         VARCHAR(50),
   problem           VARCHAR(255)
);

CREATE TYPE correction_info_ntabtyp AS TABLE OF correction_info_objtyp;

CREATE TYPE assignment_status_info_objtyp UNDER status_info_obj
(
   corrections     correction_info_ntabtyp
);

CREATE TYPE assignement_status_info_v AS VARRAY(1) OF assignment_status_info_objtyp

CREATE table assignement_tbl
(
    assignment_id       NUMBER(6),
    assignment_title    VARCHAR(40),
    elements            elements_v,
    assignment_status   assignement_status_info_v
)VARRAY assignment_status STORE AS LOB assignement_status_store; -- contains the nested table


How can I determine which Innodb table is being written? I am running Mysql 5.5.

I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The disk IO is from mysqld, and in monitoring mysqld, I am seeing what seem to me to be a lot of Innodb writes and specifically, I am seeing a lot of activity in the Innodb_rows_updated status variable that I can't easily explain.

Is there a way that I can determine which of the ~100 Innodb tables is having those rows updated?

SINGLE DATABASE

DATABASE_TO_VIEW=mydb
cd /var/lib/mysql/$(DATABASE_TO_VIEW)
watch "ls -lt | grep ibd$ | head -20"

If you want just the table names

DATABASE_TO_VIEW=mydb
cd /var/lib/mysql/$(DATABASE_TO_VIEW)
watch "ls -lt | grep ibd$ | head -20 | awk '{print $9}' | sed 's/\.ibd//'"

MULTIPLE DATABASES

Create a script called /root/InnoDBChanges.sh with these lines

DATADIR=/var/lib/mysql
IBD1=/tmp/InnoDBFiles.txt
IBD2=/tmp/InnoDBFiles.srt
echo -n > ${IBD1}
cd ${DATADIR}
for DB in `ls -l | grep ^drw | awk '{print $9}' | grep -v mysql | grep -v performance_schema`
do
    FLDR=${DATADIR}/${DB}
    ls -l --time-style="+%s %Y-%m-%d %H:%M:%S" ${FLDR}/*.ibd|awk '{print $6,$7,$8,$9}' >> ${IBD1}
done
sort -nr < ${IBD1} > ${IBD2}
head -32 ${IBD2} | awk '{print $2,$3,$4}'

Then, you can monitor all the databases for changes at the same time

chmod +x /root/InnoDBChanges.sh
watch -n 5 /root/InnoDBChanges.sh

GIVE IT A TRY !!!

CAVEAT

If innodb_file_per_table is disabled, this will not work for you.

You will have to extract the tables from ibdata1. Then, you can try my solution.

STEP #1

Set innodb_file_per_table to 1 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 1

STEP #2

service mysql restart

STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql


When running a script with several commands, how to find out where we're at? I have some processes that require different steps to be taken before they start. For example:

Disable check constraint on foreign key:

select 'alter table '+fk.table_schema+'.'+fk.table_name
+' NOCHECK CONSTRAINT '+fk.CONSTRAINT_NAME
from SAProduct.information_schema.table_constraints fk
join SAProduct.information_schema.tables t
on t.table_schema = fk.table_schema
and t.table_name = fk.table_name
WHERE constraint_type='FOREIGN KEY'

This will produce a big list of statements, I am copying only 3:

use saproduct
go
alter table dbo.tblProdClassificationCode NOCHECK CONSTRAINT FK_tblProdClassificationCode_tblProdClassification
alter table dbo.tblProdClassificationCodeDescr NOCHECK CONSTRAINT fk_ProdClassificationCodeDescr_ProdClassificationCode
alter table dbo.tblProdClassificationCodeGenerate NOCHECK CONSTRAINT fk_ProdClassificationCodeGenerate_ProdClassification

If I was to run that on one go, how would I find out where I am at?
which table I am dealing with, and which one is the next?

The same for the delete:

select 'DELETE FROM '+table_schema+'.'+table_name
from saproduct.information_schema.tables
where table_name like 'tbl%'

the script above would produce the set of statements below:

use saproduct
go
DELETE FROM dbo.tblBLanguage
DELETE FROM dbo.tblBLgCategoryXRef
DELETE FROM dbo.tblBLgSegmentXRef
DELETE FROM dbo.tblProdClassification
DELETE FROM dbo.tblProdClassificationCode
DELETE FROM dbo.tblProdClassificationCodeDescr
DELETE FROM dbo.tblProdClassificationCodeGenerate
DELETE FROM dbo.tblProdClassificationDescr
DELETE FROM dbo.tblProdClassificationMarket
DELETE FROM dbo.tblProdClassificationTier
DELETE FROM dbo.tblProdClassificationValue
DELETE FROM dbo.tblProdData
DELETE FROM dbo.tblProdDataDescr
DELETE FROM dbo.tblProdDataMarket
DELETE FROM dbo.tblProdDataTempTier1Descr
DELETE FROM dbo.tblProdDataTempTier1Tier2Descr
DELETE FROM dbo.tblProdDataTier
DELETE FROM dbo.tblProdDataValue
DELETE FROM dbo.tblProdDataValueDate
DELETE FROM dbo.tblProdDataValueNumber
DELETE FROM dbo.tblProdDataValueString
DELETE FROM dbo.tblProdName
DELETE FROM dbo.tblProdNameSizeAlias
DELETE FROM dbo.tblProdNameSizeRuleAlias
DELETE FROM dbo.tblProdNameStructure
DELETE FROM dbo.tblProdNameStructureDescr
DELETE FROM dbo.tblProduct

Which table is the next one for me to delete?

I am trying to access tables in a certain order to minimise deadlocks, specially when having to put any type of exclusive locks. it is not here, but I try to avoid lock escalation too, so it can get complex. I like to "see" what exactly is going on. I am using SQL Server 2014:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

I agree with Aaron that RAISERROR...WITH NOWAIT can be very useful and is probably the way to go if you have full control over the script that is being generated.

However, if a long script is currently executing and you don't have the ability to change the script in order to add RAISERROR calls, there are also less direct ways to get this information.

Test script

Here is a test script you can run to help demonstrate the two approaches below:

SELECT 1
WAITFOR DELAY '00:00:15'
SELECT 2
WAITFOR DELAY '00:00:15'
SELECT 3

sp_whosiasctive

While running this script, you can use sp_whoisactive to view the current server activity.  You can often view the query plan for the specific statement that is currently executing.  In my case, I see the following because the WAITFOR statement is most likely to be running at any given moment in time:

Using sys.dm_exec_requests.statement_start_offset

Alternatively, Conor Cunningham also has a post on extracting the statement from sys.dm_exec_query_stats AND sys.dm_exec_sql_text.  I don't believe this has been incorporated into sp_whoisactive yet, but you can use a query like the following to see both the current executing statement and the overall batch.

SELECT er.session_Id AS spid
    --Use the full batch text and the start/end offset of the currect statement to figure
    --out the SQL that is currently executing. This logic is based on the blog post above
    --but has been updated in light of strange cases in SQL Server that caused the original
    --blog post logic to crash with out of bounds errors on the SUBSTRING operation.
    , SUBSTRING (qt.text
                , (CASE WHEN er.statement_start_offset > DATALENGTH(qt.text)
                    THEN 0 ELSE er.statement_start_offset/2 END)+1
                , (CASE WHEN er.statement_end_offset <= 0 THEN DATALENGTH(qt.text)
                    ELSE er.statement_end_offset
                    END - CASE WHEN er.statement_start_offset > DATALENGTH(qt.text)
                        THEN 0 ELSE er.statement_start_offset/2 END)
                    + 1
                ) AS query
    , qt.text AS parent_query
FROM sys.dm_exec_requests er
JOIN sys.dm_exec_sessions s
    ON s.session_id = er.session_id
    AND s.session_id <> @@SPID      -- Ignore this current statement.
    AND s.is_user_process = 1       -- Ignore system spids.
    AND s.program_name NOT LIKE '%SQL Server Profiler%' -- Ignore profiler traces
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
ORDER BY spid


Problem installing Oracle 19c to symlinked directory from RPM I've created destination directory and linked it to default install path:
$mkdir /u01/oracle
$ln -s /u01/oracle /opt

Then I've changed the owner:
$sudo chown -R oracle:oinstall /opt/oracle

But when I tried to install RPM I got an error:
$sudo rpm -i ./oracle-database-ee-19c-1.0-1.x86_64.rpm
warning: ./oracle-database-ee-19c-1.0-1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

[SEVERE] The install cannot proceed because ORACLE_BASE directory (/opt/oracle)
is not owned by "oracle" user. You must change the ownership of ORACLE_BASE
directory to "oracle" user and retry the installation.

error: %prein(oracle-database-ee-19c-1.0-1.x86_64) scriptlet failed, exit status 1
error: oracle-database-ee-19c-1.0-1.x86_64: install failed

How to properly fix this problem?

you cant do that unless oracle user has a permission on the original directory here is an example explain to you what is exactly happening (in the example I am going to create normal text file)
[aabuhasna@aabuhasna ~]$ sudo mkdir /test
[aabuhasna@aabuhasna ~]$ sudo ln -s /test/ ./test
[aabuhasna@aabuhasna ~]$ cd ./test/
[aabuhasna@aabuhasna test]$ touch 1
touch: cannot touch '1': Permission denied
[aabuhasna@aabuhasna test]$ ls -alrt
total 8
dr-xr-xr-x. 19 root root 4096 Oct 21 18:15 ..
drwxr-xr-x.  2 root root 4096 Oct 21 18:15 .
[aabuhasna@aabuhasna test]$
[aabuhasna@aabuhasna test]$ sudo chmod 0777 /test
[aabuhasna@aabuhasna test]$ touch 1.txt
[aabuhasna@aabuhasna test]$ ls -altr
total 8
dr-xr-xr-x. 19 root      root      4096 Oct 21 18:15 ..u
-rw-rw-r--.  1 aabuhasna aabuhasna    0 Oct 21 18:18 1.txt
drwxrwxrwx.  2 root      root      4096 Oct 21 18:18 .
[aabuhasna@aabuhasna test]$

but in your case you need to use sudo chown -R oracle:oinstall /u01/oracle this is a prerequisite from Oracle, there is no workaround for that
Note: softlink will not provide full access and\or ownership on the original directory


What does MySQL actually use to decide which index should be used? I know there is already similar question here like How does the database decide which Index to use. But it seems that those answers are not cool and useless.

In my development and use of MySQL, I found there are multiple factors that affect how MySQL decides which index should be used to execute the SQL statements. Those factors include but are not limited to:

The estimated scan count of rows for certain index.
The order by condition and whether certain index would cause filesort.
The presence of limit 0,N condition and even the value of N would impact the result.

Any response from people who have read the source code or have developed MySQL source codes will be highly appreciated! :)

If you know someone who maybe knows the answers, please share this page with them.

What is in the WHERE clause.
An estimation of how many rows will be fetched -- to decide whether to use an index, or simply scan the table.
Size of two tables in a JOIN -- if there is no other good way to decidw which table to start with.
The value of N in a LIMIT rarely matters.
Whether the columns's datatypes match -- mismatch of COLLATION may inhibit use of an index.
Not used: whether the needed blocks is already cached in RAM.
Not used: Spinning disk vs SSD.  (There is a tunable in 8.0 to help.)
In certain situations, an index is dynamically created for a subquery.
Size of the index.
"Cost" of the various options.
ORDER BY is used only if the entire WHERE and GROUP BY are handled first.  Or the Optimizer may skip the WHERE and only use an index for the ORDER BY, thereby avoiding 'filesort'.
GROUP BY and ORDER BY, of not compatible enough may lead to two filesorts.

See EXPLAIN FORMAT=JSON SELECT ... for some extra clues.  See also the "Optimizer trace".

My cookbook approaches your question in a different way...  "Given this SELECT, what is the best INDEX for it?"


Is Oracle not suited as the database behind websites like Stackoverflow? I see from the thread Which tools and technologies build the Stack Exchange Network? that the database specifications are:

  Database SQL Server 2008 R2 running Microsoft Windows Server 2008 Enterprise Edition x64

While this does not necessarily mean that Oracle wont do this job, it does indicate that it is not as good for this part.

How far is it true? What are the reasons?

It just means that the developers were more familiar with the Microsoft stack.


What are the practical reasons behind learning relational algebra? Learning how to formulate query-like expressions in relational algebra is a traditional part of many, perhaps most, "Introduction to Databases" courses.

This is usually justified by the assertion that relational algebra is the mathematical foundation of relational databases in general and SQL in particular with the implication that it is important to know it.

However it seems to me that formulating expressions in relational algebra is basically the same as formulating queries in SQL and that much the same thought processes underly both tasks. In particular, I can't really see that knowing relational algebra makes it easier to write SQL queries or vice-versa. This makes me wonder if the teaching of relational algebra is just some sort of historical hangover, or if there actually specific benefits to knowing it.

So my question is "are there specific practical benefits to knowing relational algebra, of sufficient importance to make it worthwhile teaching?"

As Database Administrators, do you feel that relational algebra is or was important to your career trajectory?

A sort of sub-question is whether the time spent learning relational algebra could more effectively be used by learning more SQL?

When Codd defined the relational model he defined a set of operators which could be applied to relations.  In specifying a relational algebra, much like specification of an integer algebra, we are able to use symbols in place of relations to solve queries.  These operators are subject to the same algebraic properties that integer algebra operators (+, -, *, /) are.  As a result, we can assume certain laws that always apply to a relation, any relation, undergoing that operation.  For example, in integer algebra we know that addition and multiplication are associative in that we can change the grouping of operands and not change the result:

  a + ( b + c ) = ( a + b ) + c

Similarly, in relational algebra we know that natural join is associative and thus know that A join B join C can be executed in any order.  These properties and laws create the power to re-write query formulations and be guaranteed to get the same results.  The book Applied Mathematics for Database Professionals provides significant detail on the various re-write rules you can use to precisely formulate the same query in different ways.  In a perfect world any formulation producing the same result would have the same performance.  A modern optimizer, while an amazing piece of software, isn't perfect however.  Thus if you have formulated a query one way and are getting poor performance, you have the skills to formulate it a different way and know it has the same semantics.  Another practical advantage to this is in the specification of database constraints.  First, understanding the relational algebra enables you to determine the simplest way to formulate the constraint.  Second, by formulating the constraint in formal logic, you can immediately clarify any ambiguity in intent from the business subject matter experts who formulated the business rule in loose English and avoid bugs.

It was Leonardo da Vinci who said:

  He who loves practice without theory is like the sailor who boards ship without a rudder and compass and never knows where he may cast.

In this same way, a data practitioner who doesn't understand the fundamentals of relational theory cannot be in as complete command of the technology as they can be with that understanding.  Some great references on relational algebra are SIRA_PRISE's Introduction to the Relational Algebra page, and CJ Date's SQL and Relational Theory.  Date's book shows the practicality in understanding relational algebra so that you can write much more accurate SQL queries.  SQL has many quirks and pitfalls and having a sound grasp of how it works vs. the original relational algebra operators really helps realizing where the pitfalls are and avoiding them.


select and count based on a condition I have two tables, proj: id name and proj_reports: id, position, created_at, key_id, proj_id and
I want to select from key_reports for each proj_id:

- total number of `key_id` which can be done with `count(.key_id)`
- total number of DISTINCT `key_id` `count(DISTINCT .key_id)`
- total number of `key_id` where `position` > 1
- total number of `key_id` where `position` > 2 AND < 5

The problem that I have is that I don't know how to add those conditions for checking if the position is > or < a number or in a range.

You can use a CASE expression inside the COUNT aggregate funciton.

SELECT
    COUNT( CASE WHEN position > 1 THEN key_id ELSE NULL END ) AS GT1,
    COUNT( CASE WHEN position > 2 AND position < 5 THEN key_id ELSE NULL END ) AS GT2LT5,
FROM
     proj_reports

The COUNT aggregate does not count NULL values, so that's why it works.

Here is a SQL Fiddle that demonstrates the concept.


Unable to start the SQL Server Agent I am unable to start then SQL Server Agent. I am getting the below error message when I tried to start the agent:

  The SQL Server Agent service (DB_NN) on local machine was started and then  stopped. Some services automatically stop if they are not used by any service or program

SQL version: SQL Server 2008R2 Standard Edition(RTM)
Windows:Windows server 2008 R2 enterprise

I verified below things, currently SQL agent is running through service account. I changed to local and tried to start. But agent didn't started.

How to fix this issue?

After reading your posting I understand that when you establish USBLAN_LDR service on windows server 2008, system prompt an error message.

According the information what you provided right now, it’s hard to determine what cause this issue, you may like to post more information about your environment (Event log ,Other  System prompting etc… ).

Please check if it worked with assign a  Local System account instead of Local Service account to run this service.

Open service MMC snap-in. ·
Right UBSLAN_LDR service , and click “properties”.
Click “Log on” tab , and assign a local account for running this
services.

Seems this issue related to a third party software, I suggest to consult software vendor for future troubleshooting.

Please understand that I don't mean to bounce you between different solution provider as I am fully aware how time consuming this can be. However, they really are in a better position to be able to assist you with this issue as they may have experienced similar issues.


Where does SQL Server store symmetric keys? I am wondering if symmetric keys created in particular database are being stored in the primary file group?

Or they are stored in some special file group which the user is not able to interact with?

For example, if I am performing a partial backup to a file group (primary or secondary) can I be sure that the backup does not contain the symmetric keys?

The underlying system tables used by the sys.symmetric_keys catalog view are stored on the PRIMARY filegroup along with other database meta-data.


Insert Speeds for large batches In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table.

Lets take a sample table

CREATE TABLE bill (
id BIGINT(20) PRIMARY KEY,
amount INT(11) DEFAULT 0,
bill_date DATETIME DEFAULT NOW(),
INDEX (bill_date)
) ENGINE=InnoDB

Taking 3 rows as my batch size, the following are the approaches i could think of for inserting

Approach 1 - construct and fire 3 raw inserts

INSERT INTO bill (amount, bill_date) VALUES (10, '2012-01-01 00:00:00');
INSERT INTO bill (amount, bill_date) VALUES (20, '2012-01-02 00:00:00');
INSERT INTO bill (amount, bill_date) VALUES (40, '2013-02-05 00:00:00');

Approach 2 - clubbing the values into 1 query

INSERT INTO bill (amount, bill_date) VALUES
(10, '2012-01-01 00:00:00'),
(20, '2012-01-02 00:00:00'),
(40, '2013-02-05 00:00:00');

Approach 3 - fire this query 1 time passing 6 parameters

INSERT INTO bill (amount, bill_date) VALUES
(?, ?), (?, ?), (?, ?);

Approach 4 - Fire this prepared query 3 times changing the 2 parameters each time

INSERT INTO bill (amount, bill_date) VALUES (?, ?);

Any other approaches are welcome.

My question is

What is the quickest way to make multiple inserts in a table?

I have read this link on mysql insert speed and this guide to JDBC programming, but i'm not able to come to a conclusion.

My case -

Currently my table has ~ 20 columns, most of which are numbers, with a couple of varchar(60) and 1 text column. Mysql version 5.5.
Running on INNODB and has 1 index on Integer primary keys.
All the queries run in transaction.

I construct my queries from Java and use Spring JDBC to run the queries.

I am currently following Approach 3, It is taking around 10 seconds for 20,000 inserts to an empty table, not including the time it takes to construct the query.

To keep things in perspective, its taking 100-200 millis to fetch the data from the table.

Is there something i am missing?
How do i make the inserts faster?

Consider batching your commits.
A batch size of 1024 is a good starting size.
Change batch sizes until you reach your optimum throughput.


Assign maximum memory limit for a particular database in sql server In my SQL-Server instance, there are many databases. I want to restrict the memory size of each database (for example 8 GB). When I tried using Resource Governor, there are options like max_memory_percent and min_memory_percent and it is given in percentage value (values from 0 to 100). But I want to give the database size explicitly in GB and it should not go beyond that maximum limit.

Is there any way to restrict the database size or to get alerts if the database size exceeds the limit?

Can I implement this using Resource Governor?

ALTER RESOURCE POOL

There is no way to limit the amount of memory a specific database takes.  SQL Server uses a least used / first evicted from memory policy (basically).


MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded I have database session enable, when website got thousands of visitor. It gives me error

[30-Apr-2014 04:27:12 America/Denver] PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;
 try restarting transaction' in public_html/lib/Cake/Model/Datasource/DboSource.php:458
Stack trace:
#0 public_html/lib/Cake/Model/Datasource/DboSource.php(458): PDOStatement->execute(Array)
#1 public_html/lib/Cake/Model/Datasource/DboSource.php(424): DboSource->_execute('UPDATE `yuldi_d...', Array)
#2 public_html/lib/Cake/Model/Datasource/Database/Mysql.php(400): DboSource->execute('UPDATE `yuldi_d...')
#3 public_html/lib/Cake/Model/Model.php(1821): Mysql->update(Object(cake_sessions), Array, Array)
#4 public_html/lib/Cake/Model/Datasource/Session/DatabaseSession.php(117): Model->save(Array)
#5 [internal function]: DatabaseSession->write('66336365f5fc91c...', 'Config|a:3:{s:9...')
#6 [internal function]: session_write_close()
#7 {main}
  thrown in public_html/lib/Cake/Model/Datasource/DboSource.php on line 458

I want to know, how can i check MySQL insert queries per second, is there any commands to check that using ssh ?
is there changes in my.cnf required ?

If you wan't to check the queries/s in live you can use the mysqladmin tool with some options:

[root@db-prod-01 ~]# mysqladmin extended-status -uroot -p -i 1 -r | grep 'Com_insert '
Enter password:
| Com_insert                            | 59863746      |
| Com_insert                            | 1             |
| Com_insert                            | 2             |
| Com_insert                            | 9             |
| Com_insert                            | 2             |
| Com_insert                            | 2             |
| Com_insert                            | 3             |
| Com_insert                            | 2             |
| Com_insert                            | 3             |
| Com_insert                            | 6             |

extended-status: return the result of SHOW GLOBAL STATUS;
-i 1 -r:  Execute the SHOW GLOBAL STATUS; every second (one line by second)
| grep 'Com_insert': Shows only INSERTS statements

Note that Com_insert is a cumulativ counter the first line shows the nominal counter value and at each iteration of mysqladmin, a substraction is made to show you how there were insertions during the time you setted (here -i 1 is each 1 second).

By the way, if you want some infos for your error (1205) it's the InnoDB Engine that should check because it's the only way to see which of your query (or queries) is/are stucking your DB.

You can find infos on current and past transactions that lock your DB with:

SHOW ENGINE INNODB STATUS; -- In a MySQL prompt

Max.


MySQL InnoDB issue not using indexes correctly [Percona 5.6] I just installed Percona 5.6 on my new CentOS 6.4 server. It's a fast machine 32 core xenon, 72GB ram, 8x SAS RAID 10 setup. So far so good

My old server is a bit less powerful, and was running MySQL 5.1 still. So this was quite an upgrade. But I'm having some issues with InnoDB, it is not using the indexes correctly on some tables it seems. Where on my old machine the same queries were running fine.

Both servers have the same database. I did a mysqldump on the old machine and imported it onto the new Percona 5.6 server. Indexes stayed the same. Both servers use the same my.cnf config settings.

Table items has indexes on: item_id, item_format, item_private and contains about 40 million rows. Table formats has index on: format_id and contains about 250 rows.

SELECT
i.item_name, i.item_key, i.item_date, f.format_long
FROM
items i, formats f
WHERE
i.item_format = f.format_id
AND
i.item_private = 0
ORDER BY
i.item_id DESC LIMIT 8

On my old server this query takes about 0.0003 seconds. On the new server it takes over 100 seconds.

Query with EXPLAIN on OLD server.

+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type | table |  type  | possible_keys |   key   | key_len |         ref          | rows |    Extra    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
|  1 | SIMPLE      | i     | index  | item_format   | PRIMARY |       4 | NULL                 |    8 | Using where |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY |       4 | dbname.i.item_format |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+

Query with EXPLAIN on NEW [problem] server.

+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys |     key     | key_len |        ref         | rows |              Extra              |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
|  1 | SIMPLE      | f     | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  219 | Using temporary; Using filesort |
|  1 | SIMPLE      | i     | ref  | item_format   | item_format | 4       | dbname.f.format_id | 3026 | Using where                     |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+

You can see that it's using temporary and filesort. This seems to be the reason for the slowness.

Any idea how I could resolve this issue?

First of all: This is not because of Percona, the differences you are getting are because of the upgrade to 5.6. Percona Server rarely modifies the SQL optimiser from upstream. The changes come from the new optimiser in MySQL 5.6, which Percona Server 5.6 uses and that was heavily optimised between the two version (usually, for the best).

The reason why the execution is slower is because with an index on (item_format, item_private), it thinks that the query will be faster by getting the results using that index, but in reality, that would cause the join to be done in the "wrong" order. The best index here is (item_private) -assuming it is selective enough, if not it would be the primary key-, as it can use item_private for filtering and the hidden PRIMARY KEY inside the secondary key for ordering, while it uses the PRIMARY KEY of format for the join. Please note that (item_filtering) or (item_filtering, item_private) are not good indexes in this case.

By looking at the optimiser trace and the handler status, the problem seems to come by the predicted number of rows: the old method, in 5.6, seems to predict a full table scan, while the actual number of rows read is -more or less- the number of rows in the LIMIT clause. This seems to be a regression in the query optimiser, and it should be reported if you confirm that it is not due to any special personal configuration. It is specially bad, as it prefers the creation of a temporary table for the join (potentially on disk, so it may be very slow in some cases) over a very light scan.

You have been already told several ways of avoiding this problem for the time being: not creating an index containing item_filtering, using STRAIGHT_JOIN or forcing the usage of item_filtering (or PRIMARY).


String or separate Table I'm creating the DB for a system and in it is an alert system, where, when a certain action happens, or when certain information is present, an alert (via email) is triggered. I'm allowing users to customize their own alerts so that they can receive alerts when they want, not when I, as the product engineer, thinks they should get them. I'm also allowing admins on the account to create alerts on behalf of other users, i.e. Boss can create alerts to be sent out to employees who have the responsibility to handle the alert.

So, there are three different ways that a user can be signed up for an alert: 1) all employees 2) all admins 3) manually decide which users get it.

I've decided that having a column is_all_users and is_admin as booleans is the right approach to handling those scenarios, but the question is, when dealing with "manually deciding users" is it ok to be a string of user ID's that then get parsed (I don't anticipate there ever being more than 5 recipients in this scenario), or should I make it a separate table?

The two schemas are as follows:

alerts

client_id (int)
alert_id (int)
etc. etc.
recipients (string)
recipients_is_admin    (boolean)
recipients_is_all (boolean)

OR

alerts

client_id (int)
alert_id (int)
etc etc.

alerts_recipients

alert_id (int)
user_id (int)
recipients_is_admin (boolean)
recipients_is_all (boolean)

In scenario 1, there could be a speed and/or parsing concern but scenario 2 just seems unwieldy and unnecessary. Of course, in the second scenario, I could also put recipients_is_admin and recipients_is_all in the first table, but then I'd be dealing with two different tables for a single purpose which just seems wrong.

It may seem unwieldy, but I would go with the approach of having a separate table rather than using a comma-separated string.

In addition to avoiding the parsing complexities, this allows you to do nice things like apply foreign key constraints and / or indexes to that user_id column.  It also simplifies mass updates if they are ever needed ("remove user 23 from all alerts").

I would differ from your proposed schema in that I would leave the two boolean fields on the main alerts table, as they apply whether or not there are individual sets of recipients.


Map ASM files to physical Linux Devices We are having problems with log file sync waits periodically.  We have a 2 node RAC cluster using ASM. I'd like to monitor the physical device(s) containing the REDO log files using iostat, but I'm not sure which physical device contains the redo log groups.  We have something like 150 physical devices so I'd like to narrow this down as much as possible.  How would I map the ASM files containing the redo logs to the physical linux devices?

First of all, people often fall for this, but high log file sync waits do not necessarily mean I/O problem.

Second, this could be really troublesome, if you have many physical devices in the ASM diskgroup where your redo logs are, because the extents of your files will be evenly distributed on several disks.

Anyway, you need to find the ASM diskgroup number and file number for your redo logs. For example my redo logs:

select member from v$logfile

MEMBER
--------------------------------------------------
+O6CA_DATA/BRIX/ONLINELOG/group_1.258.915373939
+O6CA_FRA/BRIX/ONLINELOG/group_1.257.915373939
+O6CA_DATA/BRIX/ONLINELOG/group_2.259.915373939
+O6CA_FRA/BRIX/ONLINELOG/group_2.258.915373939
+O6CA_DATA/BRIX/ONLINELOG/group_3.266.915374563
+O6CA_FRA/BRIX/ONLINELOG/group_3.259.915374563
+O6CA_DATA/BRIX/ONLINELOG/group_4.267.915374563
+O6CA_FRA/BRIX/ONLINELOG/group_4.260.915374563

Since I use OMF, I can easily recognize the file number, because it is part of the name (258, 257, ...), but you can get this as:

select
  af.group_number,
  af.file_number
from
  v$asm_file af
  join v$asm_alias aa on (af.group_number = aa.group_number
                          and af.file_number = aa.file_number)
  join v$logfile lf on (substr(lf.member, instr(lf.member, '/', -1) + 1) = aa.name)
;

GROUP_NUMBER FILE_NUMBER
------------ -----------
           1         258
           2         257
           1         259
           2         258
           1         266
           2         259
           1         267
           2         260

Now I am curious about the first redo logfile (group 1, file 258). In the ASM instance (so not the database instance), I can query as:

select
  group_kffxp,
  disk_kffxp,
  size_kffxp,
  count(*)
from
  x$kffxp
where
  group_kffxp = 1
  and number_kffxp = 258
group by
  group_kffxp,
  disk_kffxp,
  size_kffxp
;

GROUP_KFFXP DISK_KFFXP SIZE_KFFXP   COUNT(*)
----------- ---------- ---------- ----------
          1          0          1         51

So this single redo log file has 51 extents in diskgroup number 1, disk number 0, each extent the size of 1 allocation unit (and the allocation unit I used is 1 MB, and I created a redo log with the size of 50 MB, but that is not relevant here). And that single disk, in my case, is:

select name, path from v$asm_disk where group_number = 1 and disk_number = 0

NAME            PATH
--------------- ------------------------------
O6CA_DATA_0000  /dev/oracleasm/asm-o6ca_data1


Data directory of mysql moving from one drive to different drive? I'm moving data directory from D drive which has raid controller hard disk to C drive where OS installed on windows environment.

Here is the issues:

Stop Mysql server
open my.ini --> data directory -> "C drive path"
copy data directory folder to c drive folder
start mysql server

Will it work as this is innodb engines, I have heard we need to restore back as .sql files , copying raw file from one place to another doesn't work.
Is it true or do you have any other workaround as there is 600 GB data size?

You can try with below steps.

Stop the existing MySQL instance using services.msc, shutdown must be clean so that the instance flushes any pending changes to disk.
After successful instance shutdown, copy the data directory from existing location to the new location.
Existing location: C:\ProgramData\MySQL\MySQL Server 5.7
New Location: E:\MySQL
Change datadir parameter in current my.ini file. I have commented old entry and added new one:
#datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data
datadir=E:\MySQL\Data
Start MySQL instance using services.msc
After starting MySQL successfully, you can verify above changes by creating temporary database and verify data directory for the same database.


Unable to disable SQL statement logging in PostgreSQL 9.1 I am running PostgreSQL 9.1 on Windows Server 2008 R2.  I enabled SQL statement logging to the stderr log by modifying my postgresql.conf file, but now I can't seem to disable the logging without turning off all logging.  Server was sent SIGHUP signals via pg_ctl reload, and the service has been restarted after each attempt to fix this.  Rebooting the entire machine did not seem to help either.

Specific lines from my current postgresql.conf that should have accomplished this, but aren't:

log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default
log_hostname = on
log_line_prefix = '%t %a %u %r '
#log_lock_waits = off
log_statement = 'none'
#track_activities = on
#track_counts = on
#track_functions = none
#track_activity_query_size = 1024
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'

I've checked the manual; it seems all I need to do is ensure that the log_statement line in the config file is set appropriately.

However, nothing I do seems to have any effect on what is actually being logged.  If I turn off the logging_collector, the text log files are no longer generated.  This would indicate that PostgreSQL is at least pointed to the correct postgres.conf file, and that PostgreSQL is reading it to determine its configuration.

What do I do to turn of SQL statement logging?

There are a number of relevant settings missing in your list. Check the chapter "When to log" in the manual, in particular these settings:

log_min_messages (enum)
log_min_error_statement (enum)
log_min_duration_statement (integer) -- as @Jack already hinted in his comment.


SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012 Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server.

Problem now is that the database on SQL Server 2012 is much slower than it was on the Windows 2003 server with SQL Server 2005. What's even more worrisome is that the exact same maintenance plan (that took 37 minutes to run on SQL Server 2005) is now taking more than 2 hours and uses 16GB of memory and the tempdb is filling up the empty space on the C: drive. What's going on?

The server specification are the same for both:

X5650 @ 2.67GHz (4procs)
16 GB RAM

The maintenance plan consists of the following steps:

Step 1: Check DB integrity
Step 2: Reorganize Index
Step 3: Rebuild Index
Step 4: Shrink Database

There can be many things to address. Below is an outline of things to check before jumping on any conclusions :

First, your sequence of POST restore steps will mess up all the work you did. Never shrink your database and that too especially after doing Index maintenance.

Read up - Why you should not shrink your data files by Paul Randal.

Below are my common post restore steps for any lower version to higher version migration. In your case SQL Server 2005 to 2012.

change compatibility level on the restored databases to 110 (with the caveat that it will require a full regression test)
run DBCC UPDATEUSAGE on the restored databases DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
Update statistics on all tables with full scan : Update Statistics table_name with FULLSCAN
Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's Index and Stats maintenance scripts.
Recompile all SP's using sp_recompile 'procedureName'
Refresh your views SP_REFRESHVIEW view_name

----------

As a side note:

turn on Instant File Initialization on the new server.
Have multiple tempdb data files with equal size.
Enable Trace Flag 1118
Configure max and min memory correctly. Especially Max memory away from default.
Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
Best is to install sp_Blitz from Brent Ozar.  Run it and address the critical and high priority issues reported by it.
You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.
Enable Optimize for ad-hoc workloads and Default backup compression options.

Refer to UPGRADE and POST UPGRADE steps for more details.


pg_restore restore FK constraints with explicit --table argument I started using the -t argument of pg_restore to only restore some tables, which has worked well for read-only workflows.

The problem is that the -t seems to not restore the FK constraints nor sequences applied to the table/column.

Regarding the former (sequence), it seems that the tables restored with -t which had an id column with a modifier of not null default nextval('report_id_seq'::regclass) was restored with just not null, so the sequence isn't applied, which is an obvious.

Is there a way to use pg_restore with explicit tables using -t that also restore foreign-key constraints and sequences?

What you probably want is to restore all things that are not data. So try -s, from man pg_restore

-s
--schema-only
    Restore only the schema (data definitions), not data, to the extent
    that schema entries are present in the archive.

    This option is the inverse of --data-only. It is similar to, but
    for historical reasons not identical to, specifying
    --section=pre-data --section=post-data.

    (Do not confuse this with the --schema option, which uses the word
    “schema” in a different meaning.)

Note that schema is everything. Foreign key constraints mean you may not be able to load just the data for a table. From pg_dump

  when -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that the results of a specific-table dump can be successfully restored by themselves into a clean database.

So if you've got a clean database, and you try to restore with -t you're potentially attempting to insert data the conflicts keys and constraints specified in the schema -s.


Should I use a Column naming convention or rely on column descriptions? Frankly I am stunned that after some basic (very basic) internet searches I have not been able to find a comprehensive naming convention or guideline or SQL object and column naming.

The parallel I'm used to is in the .NET development world where there are many offered conventions and Microsoft has gone so far as to document a comprehensive set of recommendations which is widely adopted:

General Naming Conventions
Design Guidelines for Class Library Development
Guidelines for Names

1. Is there a well-adopted standard for database object, column, etc naming?

2. Are there any pitfalls with relying on the Description property for describing columns of a table? Obviously one wants the name to reveal intent but that's sometimes difficult in a database and descriptions can be invaluable for someone learning about the DB (although they can also lie).

Help much appreciated!

Pick a standard, make sure it makes sense, and document it. There have been plenty of debates about this (e.g. whether the identity column in the contacts table should be called ID or ContactID, or whether the contacts table should be called dbo.Contacts or the unnecessarily verbose dbo.tblContacts), and you're never going to find agreement among your peers. Which standard you choose is not important; what is important is that you use it consistently, make it well-known, and get agreement from your colleagues that this is the way.
Your columns should be self-documenting (and not cryptic, abbreviated or obfuscated) where practical. I would NOT rely on the extended properties, but that's just me (google/bing for problems with extended properties and you will see plenty of complaints I would guess). External documentation / data dictionary is much more valuable to me - if your database goes south and so does your backup, it makes it that much easier to rebuild. It also makes it easier to describe your schema to junior developers etc. without giving them direct access to everything.


Should I care about physical columns in SQL Server? So far, I only ever looked at the logical columns of my table but never at the physical columns. My motto was : "let SQL Server deal with it".

After reading that article, I wonder if I should not pay some attention to it.

  By now, our 5 column table has actually 15 columns in the physical
  storage format, 10 dropped columns and 5 usable columns. The table
  uses 412 bytes of fixed space for the 3 fixed length columns that have
  a total length of only 112 bytes. The variable length columns that now
  store the first_name and last_name are stored in the record after
  these 412 reserved bytes for fixed columns that are now dropped. Since
  the records always consume all the reserved fixed size, this is quite
  wasteful. How do we reclaim it? Rebuild the table:

Physical columns seems to take useless space but can it also affect performance?

Actually, this is a great article. Life would be a way easier if every developer would ask similar questions. Nowadays almost nobody cares about that but to tell the truth the correct answer is: It depends on.

If you have a relatively small software with several million of records, you will be fine with that. Maybe I could recommend you to create a weekly job that does an online rebuild on each table every Sunday to take care of your tables and indexes (of course, please use to Online=On hint or you will have some surprise :D)

If you have a big software with several million of users and plenty of rows, you must ask yourself similar questions.

If you store a row in 400 bytes instead of 100, it does not affect your performance directly. But indirectly, let imagine: you have larger rows, the pages are filled sooner, you will have more page split that takes time and IO. And, of course, your server has to read more pages to get the same amount of rows because each row is larger. So indirectly, it affects your performance.

I think if you keep some rules in your mind, you will avoid the major problems:

every fixed length column takes a place. If you have a nullable int column, it will take 4 bytes. If you have a char(10) column, it will take 10 bytes and so on.
variable length columns take a same amount of bytes as their length multiple by the storage size of one unit. A varchar(10) that stores 'apple' takes 5 bytes (plus some additional info). An nvarchar(10) that stores 'apple' takes 10 bytes.
Very big rows are stored out of the row data (check the Inside SQL Server books).

So my advice is simple:

plan your columns well
avoid to delete or alter a column
maintain your tables


Necessity of having two instances (Reader and Writer) in Amazon Aurora I'm managing an Amazon Aurora account. Somebody set it up months ago.
The service has TWO instances (same AZ):
- project
- project-eu-central-1b

Same class (db.t2.small), but different "Replication Role". First is Reader and the second is Writer. The service has only one cluster:project-cluster-1

I wouldn't like paying twice if I need only one instance but I'm not sure if Aurora actually needs two instances (W and R) to work or it was a bad initial setup.

Can I use one instance for reading/writing and "move permissions" on MySQL-side?
I'm also interested in "Reserved instances". In actual scenario I think I need to buy two reserved instances, right?

Thanks.

Yes, you are paying twice for the instances, but only once for the storage, because they share a single Aurora cluster storage volume (there is only one volume per cluster), which is part of why replication is nearly instantaneous.

You can remove the reader instance, as long as you understand that this will slow down recovery if the lone remaining instance fails -- Aurora will have to spin up a new one, instead of automatically promoting the reader to become a writer, which is what it would otherwise do if the current writer fails.


mongodb :: failures in bring up mongo server I am unable to add a replica to the cluster.

Steps tried:

done hot backup for existing member.
Copied it to new replica.
Tried to start the server, it's failing with these errors:

2019-12-31T13:03:38.277+0000 E STORAGE  [initandlisten] WiredTiger error (0) [1577797418:277479][8130:0x7ff6a95f9b80], connection: __wt_cache_destroy, 358: cache server: exiting with 169 bytes in memory Raw: [1577797418:277479][8130:0x7ff6a95f9b80], connection: __wt_cache_destroy, 358: cache server: exiting with 169 bytes in memory
2019-12-31T13:03:38.279+0000 W STORAGE  [initandlisten] Failed to start up WiredTiger under any compatibility version.
2019-12-31T13:03:38.279+0000 F -        [initandlisten] Fatal Assertion 28559 at src/mongo/db/storage/wiredtiger/wiredtiger_util.cpp 65
2019-12-31T13:03:38.279+0000 F -        [initandlisten] aborting after fassert() failure

We are using percona mongodb - 4.0.13-7

What could be the reason?

If you add a new replica node, Mongodb will itself copy the data across to the new node. Unless if your data size is huge and do not want your network to be flooded in transferring the data you can follow the backup method.


MySql: Change from latin1 to utf8 Today my database character set and collation is set to latin1. All of the tables in the database are however already set to DEFAULT CHARSET=utf8 and all data is utf8.

Is if it is safe to change character set and collation of the database to utf8?

Is it safe to also set the default settings in the my.cnf file with:

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

A typical table in the database looks like this:

CREATE TABLE `tblpayment` (
    `paymentid` int(11) NOT NULL AUTO_INCREMENT,
    `userid` mediumint(9) DEFAULT NULL,
    `payed` enum('False','True') CHARACTER SET latin1 DEFAULT 'False',
    PRIMARY KEY (`paymentid`)
) ENGINE=InnoDB AUTO_INCREMENT=72803 DEFAULT CHARSET=utf8;

As you can see the enum "payed" is still using latin1 for some reason, however the rest of the table is utf8. Is it safe to change the CHARACTER SET of the enum to utf8 instead?

Setting the default character set and collation is completely safe. This will ensure that future DDL changes will use utf8, but will not affect existing columns that use latin1. Those will have to be converted to utf8. Make a backup of the data, because there are risks of data corruption (one example).

And for completeness, I will point out that adding the changes in the my.cnf will require a server restart.


How to force drop database in SQL Server 2008 I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error

  cannot create file C:\Program Files.....[databasename].mdf because it already exists

Here's my query to force drop the database

Use master;
ALTER database [databasename] set offline with ROLLBACK IMMEDIATE;
DROP database [databasename];

I understood that, the above query is dropping the database, but it's not deleting the .ldf and .mdf files. How to drop the database thoroughly?

A normal query

Drop database [databasename] ; //deletes the database completely, including the ldf and mdf's.

How to force drop a database, which also deletes the .mdf and .ldf files?

That is expected and documented behavior:

  Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

So why are you taking your database offline first? Just set it into SINGLE_USER mode and then drop it as documented on SQL Server Books Online.-

USE master;
ALTER DATABASE [databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [databasename] ;

Note, database backups will not be deleted as part of the process documented above.


Lots of Indexes - MySQL vs MongoDB - Migrating So, I've never used MongoDB, I just read a lot about it and I think it's going to be good for my project. Also, I do not have lots of experience with MySQL and to be even more honest I have no clue of what I'm about to ask.

Scenario:

MySQL table profile:

id = [pk, auto_increment, smallint]
user_id = [pk, fk, varchar]
category_id = [pk, fk, smallint]
role_id = [pk, fk, tinyint]
country_id = [pk, fk, smallint]
state_id = [pk, fk, smallint]
legal_document = [pk, varchar, ?unique]
name = [pk, varchar, ?unique]
type = [pk, boolean]
last_activity = [pk, date]

All the fk you see there are MySQL tables of course. Then I'm thinking to use MongoDB to store the profile info, profile_info collection should contain documents like:

{
  '_id' : 1 (profile_id),
  'address': 'Some street in some state of some country :P',
  'phone': [5555555, 5555555],
  'email': 'example@example.com',
   etc...
}

I'm planning to use mongodb because my project needs to be public ASAP and we may add several new profile info and other things and I do not want to do alter table and migrate from tools with lots of rows.

With that said, we may have to add a few more pks to the MySQL table, so I was thinking of migrating all the project to MongoDB and I do not know if that is a good move.

Questions:

It's better to keep the pks in MySQL and trivial info in MongoDB, or would it be fine if I move all to MongoDB?
May MongoDB be faster if I just migrate the whole project but I keep the structure like that? I mean like having profile and profile_info collections instead of just profile.
I'm worried about how many resources MongoDB could use for a 'table/collection' with that many indexes, I want to keep disk space and ram at minimum use. Is there a critical difference between MySQL and MongoDB?

PS: SSDs are going to be used in the system.

PS II: All tables are just planned, nothing is written yet. I'm a bit of plan very ahead person so please be patient with me.

Given your description I strongly suggest against using MongoDB. Not because it would necessarily a bad choice (although I believe it is in your case for reasons other than pure technical ones).

Here are the points that caught my eye.

Data modeling

Trying to use MongoDB with relational data model with no adaptations almost always leads to tears and misery except for the most trivial use cases. And that is the better end of the story. The worse end is loosing money, potentially big time.

The reason for this is that with SQL, you identify your entities and their attributes and relations and then bang your head against the wall for a few hours to get your upper left above and beyond JOINS right to get your questions derived from your use cases answered. All while avoiding data redundancy like the devil holy water.

Data modeling with MongoDB works different. You identify the use cases and the questions derived from them and model your data in a way so that those questions can be answered in the most efficient way.

Since this is a bit abstract, let me give you an

Example

Let us assume you have a web application called "chirper", with users doing chirps. With SQL you would now model your data, coming up with something like a user table and a chirp table.

The first use case you encounter is that you want the latest 10 chirps to be displayed on the applications home page, together with the username of the chirper. With SQL it is easy enough, you do a join on chirps and users, sort the result descending by data and limit to 10 records.

With MongoDB, you'd have a careful look at what you need. For various reasons you do not want to embed the chirps into the user collection. But since you do need to relate the chirps to a user anyway, you decide to do so by username and come up with a "schema" for your chirps collection like this:

{
  _id: ObjectId("570b87a56931b8f21a8bf25c"),
  user: "jdoe",
  date: ISODate("2016-04-11T11:17:08.415Z"),
  text: "Chirp!!!"
}

and, miraculously enough, all you have to do is to do a

db.chirps.find({}).sort({date:-1}).limit(10)

which gives you the same result without a JOINS at the expense of relatively cheap disk space.

Resource limitation

  I want to keep disk space and ram at minimum use

MongoDB is a lot, but surely not resource friendly. It was never meant to be a replacement for MySQL – which by the way was specifically designed to be a relatively lightweight general purpose RDBMS. I strongly advice against running MongoDB and anything else on the same server for production purposes. The reasons are manifold, but here are the most important ones:

MongoDB will take up to 85% - 90% of your available, physical RAM. This is because the working set (the indices and a subset of the data) are kept in RAM.
Depending on the storage engine and its configuration you use for MongoDB, even CPU utilization can be considerable.
A heavily loaded MongoDB needs a lot of disk IO. I have seen systems with SSDs in a RAID0 in which the IO rate was at its limit.

Leaving out the petty details for the moment, this would mean that during situations where you do not want it the most (your application is taking off and has a lot of users), MongoDB would battle with the other parts of the application over resources.

Do not get me wrong: MongoDB is not a resource eating monster per se. But if it does what it is intended to do, namely dealing with huge amounts of data and data changes, you do not want to have the parts of your application battle for resources.

That being said: There are ways to limit MongoDBs resource allocation and make sure those limits are obeyed. Probably the most well known as of today is Docker. You have to decide wether it is worth it to run MongoDB in a Docker container.

As for the data file size: There is transparent compression available for the default wiredTiger storage engine. You can choose from either snappy(default) or the better known zlib compression. Both come at the expense of a higher CPU utilization, as mentioned above.

Time to market

Forgive me, but you demonstrate a lack of basic knowledge about MongoDB. Since you are not sure what to do, the best you can do is to research every step carefully, make a decision, rinse and repeat. And I am not even taking into account that you would need to remodel your data and most likely make massive changes to your applications persistence layer. If I were you and wanted/needed a fast time to market, I'd go with what I have as of now. In case problems occur during production, I'd get myself a consultant specializing on NoSQL to find out wether one of the various NoSQL databases suits your needs, identify said DBMS (which may or may not be MongoDB), get myself a specialist for that and do the migration only then. With a specialist on your side.

Administration

A mistake commonly made by people is that they think it is as easy to manage a MongoDB deployment as it is to getting it to work. It is not. Any DBA, Sysadmin or even DevOps (even when used in the wrongest sense of the word) should be able to get a sharded cluster running. Choose the proper dimensions, identify problems, decide when to scale, deal with non-trivial problems and errors? Not so much. And note that the latter is highly subjective and prone to the skills of the DBA in question. Do you really want to store your production data on a system you barely understand?

Conclusion

Imho, MongoDB does not fit the requirements you stated. Changing now will most likely increase your time to market, and very much so since you do not understand the intricacies and pitfalls of MongoDB. In the worst case you built up technical debts while simultaneously diminishing the performance of your application.

MongoDB is less well suited for your resource limitation requirements, and finding a sweet spot for resource limitations that give you an acceptable performance requires an experienced DBA and quite some time, during which your application might run with sub-par performance.

Let me put it in a bit of context: MongoDB might very well be suitable for your use cases, and even excel. But I do not think it is feasible to change your persistence technology as of now, given your level of knowledge and the stated requirements.

hth