Oracle EM 11g - query to find out CPU utilization in OEM 11g, if you see performance, there are graph about cpu utilization, memory utilization, and disk I/O utilization.

my question is:

what is the script to see CPU-Utilization history ?

Thank you

This query runs on 11.1.0.7 Enterprise and provides similar results to the the OEM Grid performance page. Both require the diagnostics package.

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,
       ROUND(OTHER / 60, 3) AS OTHER,
       ROUND(CLUST / 60, 3) AS CLUST,
       ROUND(QUEUEING / 60, 3) AS QUEUEING,
       ROUND(NETWORK / 60, 3) AS NETWORK,
       ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
       ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
       ROUND(COMMIT / 60, 3) AS COMMIT,
       ROUND(APPLICATION / 60, 3) AS APPLICATION,
       ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
       ROUND(SIO / 60, 3) AS SYSTEM_IO,
       ROUND(UIO / 60, 3) AS USER_IO,
       ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
       ROUND(CPU / 60, 3) AS CPU,
       ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
  FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,
               DECODE(SESSION_STATE,
                      'ON CPU',
                      DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),
                      WAIT_CLASS) AS WAIT_CLASS
          FROM V$ACTIVE_SESSION_HISTORY
         WHERE SAMPLE_TIME > SYSDATE - INTERVAL '1'
         HOUR
           AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*)
  FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER,
'User I/O' AS UIO,
'System I/O' AS SIO,
'Concurrency' AS CONCURRENCY,
'Application' AS  APPLICATION,
'Commit' AS  COMMIT,
'Configuration' AS CONFIGURATION,
'Administrative' AS   ADMINISTRATIVE,
'Network' AS  NETWORK,
'Queueing' AS   QUEUEING,
'Cluster' AS   CLUST,
'Other' AS  OTHER))
ORDER BY 1

This query is not as good as what Grid displays but it's close.


Issues with TempDB mdf file ever increasing I have a tempdb growth issue. Let me preface everything by giving my tempdb settings.

Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and then slowly without stopping. I've run many queries to figure out what is running, below is the result of the query below which actually gave me the results I could use.

As can be seen they are all internal spid's is there any way to find out why tempdb continues to grow out of control and how to mitigate it? Any help on this problem would be greatly appreciated.

--Query that returned the result set
SELECT session_id,
       SUM(internal_objects_alloc_page_count)   AS task_internal_objects_alloc_page_count,
       SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM   sys.dm_db_task_space_usage
GROUP  BY session_id
HAVING SUM(internal_objects_alloc_page_count) > 0

So first, why is your data file growth set to 1MB? If you need to accommodate 20MB worth of data in tempdb the file will have to grow 20 individual times! Imagine if you have a query that requires a 200MB or 2GB spill to disk? Yikes.

Growth events are expensive, especially on older SAS/SATA storage and especially if you don't have instant file initialization enabled. You should really try to pre-size your file so that they're big enough to accommodate your busiest workload, and avoid growth events altogether. This isn't always practical, but in lieu of that, the growth size should be much larger. You really want this to be a rare and isolated event, not one that is occurring constantly. What is the point of keeping tempdb small, when it's going to use more space eventually? Are you going to lease the space out temporarily to the highest bidder, then evict them?

Also, why is there only one tempdb file? This is a common source of contention. Typical wisdom suggests to start with 4 files, even if they're all on the same disk. This can drastically reduce contention especially when multiple concurrent processes are trying to create objects or otherwise use space in tempdb. You may also want to look into enabling trace flag 1117 (which ensures that all of your data files grow at the same time) and trace flag 1118 (which changes extent allocation). Links about these below.

None of this will solve your core issue, of course - lack of (or concern over) disk space. If you don't have enough disk space to support the current usage of your system (whether it's the system or your users), get more disk space, or move the system. You might be able to find some of the culprits (see this answer for some common ones), but you won't be able to squash them all.

You might also find these things useful:

this blog post by Jason Hall
this survey from Paul Randal
this TechNet article on optimizing tempdb
this squashed myth from Paul Randal
this discussion on TF1118
this blog post from Paul White, explaining temporary object caching
this question about determining version store usage
this answer (also linked above) on determining contributors to tempdb log usage, and common causes for tempdb usage in the first place
there are also three "Linked Questions" in the right sidebar of the above answer that may be useful
this documentation on contributors to tempdb size
this question, also about contributors to tempdb size
this tip about tempdb configuration best practices on mssqltips.com

And also, now that we know you are using Service Broker, you may want to read these two pages which might help explain why your conversations aren't clearing out:

http://itknowledgeexchange.techtarget.com/sql-server/writeup-of-a-strange-service-broker-and-tempdb-problem/
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c4414a6-213b-4a69-8dcd-f162bc979b3b/service-broker-filling-tempdb?forum=sqlservicebroker


SQL Server many-to-many relationship, row reuse? Below is your typical many-to-many relationship. Pretend there are 2 tasks that have the exact same Tags. because of foreign key/primary key relationship constraints, there is no way for the rows in the table TasksTag to be reused (both Tasks would have the same PK; impossible)

In my scenario, theoretically, a task will usually contain most or all tags, that means every Task will have roughly 300+ rows in the TasksTag table. This scales horribly because there will be a few hundred Tasks per month and a lot of which will have the same Tag list.

Can someone explain another way of doing this without removing the constraints? Am I worrying about space too much?

Thanks!

The below sample structure illustrates how you can do the TasksTags table most efficiently.  The Tasks table enforces unique task names.  The Tags table enforces unique tag names.  The TasksTags table joins these together allowing any combination of Tasks and Tags.

USE tempdb;
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TaskName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tasks_TaskName UNIQUE
);
CREATE TABLE dbo.Tags
(
    TagID INT NOT NULL CONSTRAINT PK_Tags PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tags_TagName UNIQUE
);
CREATE TABLE dbo.TasksTags
(
    TaskID INT NOT NULL
    , TagID INT NOT NULL
    , CONSTRAINT PK_TasksTags PRIMARY KEY CLUSTERED (TaskID, TagID)
);
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task1');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task2');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task3');

INSERT INTO dbo.Tags (TagName) VALUES ('MyTag1');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag2');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag3');

INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,3);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,1);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (2,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (2,3);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (3,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (3,1);

SELECT TK.TaskName, TG.TagName
FROM dbo.TasksTags TT
    INNER JOIN dbo.Tasks TK ON TT.TaskID = TK.TaskID
    INNER JOIN dbo.Tags TG ON TG.TagID = TT.TagID;

EDIT

The following allows a list of tags to be associated with more than one task.  You could blend this with the prior structure to create a totally customizable list of tags for each task.

USE tempdb;
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TaskName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tasks_TaskName UNIQUE
);
CREATE TABLE dbo.Tags
(
    TagID INT NOT NULL CONSTRAINT PK_Tags PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tags_TagName UNIQUE
);
CREATE TABLE dbo.TagList
(
    TagListID INT NOT NULL CONSTRAINT PK_TagList PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagListName VARCHAR(255) NOT NULL CONSTRAINT UQ_TagList_Name UNIQUE
);
CREATE TABLE dbo.TagListTags
(
    TagListID INT NOT NULL CONSTRAINT FK_TagListTags_TagListID FOREIGN KEY REFERENCES dbo.TagList(TagListID)
    , TagID INT NOT NULL CONSTRAINT FK_TagListTags_TagID FOREIGN KEY REFERENCES dbo.Tags(TagID)
    , CONSTRAINT PK_TasksTags PRIMARY KEY CLUSTERED (TagListID, TagID)
);
CREATE TABLE dbo.TasksTagList
(
    TaskID INT NOT NULL CONSTRAINT FK_TasksTagList_TaskID FOREIGN KEY REFERENCES dbo.Tasks(TaskID)
    , TagListID INT NOT NULL CONSTRAINT FK_TasksTagList_TagListID FOREIGN KEY REFERENCES dbo.TagList(TagListID)
    , CONSTRAINT PK_TasksTagList PRIMARY KEY CLUSTERED (TaskID, TagListID)
);

INSERT INTO dbo.Tasks (TaskName) VALUES ('Task1');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task2');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task3');

INSERT INTO dbo.Tags (TagName) VALUES ('MyTag1');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag2');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag3');

INSERT INTO dbo.TagList (TagListName) VALUES ('Tag List 1');
INSERT INTO dbo.TagList (TagListName) VALUES ('Tag List 2');

INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 1);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 2);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 3);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (2, 1);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (2, 3);

INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (1, 1);
INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (2, 1);
INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (2, 2);

SELECT Tasks.TaskName, Tags.TagName
FROM dbo.TasksTagList TTL
    INNER JOIN dbo.TagListTags TLT ON TTL.TagListID = TLT.TagListID
    INNER JOIN dbo.Tags ON TLT.TagID = Tags.TagID
    INNER JOIN dbo.Tasks ON TTL.TaskID = Tasks.TaskID;


Avoiding "?" symbols in string column In my contact table, I'm getting ? symbols in LastName. How can I avoid that?

FirstName   MiddleName     LastName
Bjorn       Erik           N?ss
Gunn                       Gjos?ther
Oystein     Larsen         Indrev?r
Louise      Tulle          Pr?strud

You need to ensure the LastName column is a Unicode type, for example nvarchar(30), and use the N prefix on any literal strings you use in insert/update/merge statements. (Alternatively, you could use a non-Unicode type with a code page that supports all the characters you need.)

SQL Server replaces characters it cannot convert with the ? symbol.

See nchar and nvarchar (Transact-SQL)

  Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

The code page is determined by the collation. Taking SQL_Latin1_General_CP1251_CI_AS as an example:

SELECT FH.[description]
FROM sys.fn_helpcollations() AS FH
WHERE FH.name = N'SQL_Latin1_General_CP1251_CI_AS';

Output:

Latin1-General, case-insensitive, accent-sensitive,
    kanatype-insensitive, width-insensitive for Unicode Data
SQL Server Sort Order 106 on Code Page 1251 for non-Unicode Data

Code page 1251 (used for non-Unicode data in that collation) does not have the æ character, so:

SELECT 'Gjøsæther' COLLATE SQL_Latin1_General_CP1251_CI_AS;

Produces:

Gjos?ther

Notice that as well as the ?, the ø character was translated to o.

The question does not state which collation the instance/database/column is using, but it seems very likely it is the wrong one for your data.

More general information:

Data Types (Transact-SQL)
Collation and Unicode Support


Unable to update or insert values table with DateTime column defaulted to GETDATE I have atlered table by adding Datetime column. This column is not null and also default value from GETDATE().

ALTER TABLE myTable ADD myDateColun DATETIME NOT NULL DEFAULT (GETDATE())

I'm trying to update, I'm unable to update or insert values table

update  myTable set myDateColun = GETDATE() where id = someNumber

Its throwing me SQL error

  Column name or number of supplied values does not match table
  definition.

Note: This table has trigger defined on insert and delete scenario for data, which updates synonym table linked to this table in other database.

Any ideas what can be reason for SQL errors in above scenario?

My guess is that your trigger has a "insert into ... select * from myTable" kind of structure, and now it's broken because there's one more column in the table. Change your trigger so that it lists the columns instead of using *.


Find Employees With Multiple Managers Listed We outsourced a project and are paying for it!  In our table which holds roughly 1500 employees a manager is assigned, but some employees have duplicate managers assigned.  How can I using SQL Server 2008 R2 return only each employee who has more than 1 manager assigned (so query window should return the employee and both managers assigned)?

Sample DDL to show table structure and how bad it is messed up atm

Create Table #DuplicationIdentifier
(
  empName varchar(500)
  ,empID varchar(25)
  ,managerID varchar(25)
)

Insert Into #DuplicationIdentifier VALUES
('Blue Bell', 'bb12', 'fj12')
,('Joe Jones', 'jj30','bb41')
,('Mark Jones', 'mj91','ll98')
,('Surge Drink', 'sd22','ll98')
,('Paul Pills', 'pp23', 'ss10')
,('Red Color', 'rc84','ss10')
,('Pink Flamingo', 'pf04','bb41')
,('Blue Bell', 'bb12','bb41')
,('Joe Jones', 'jj30','ss10')
,('Red Color', 'rc84', 'rd22')
,('Pink Flamingo', 'pf04','fj12')

Here is one approach

WITH DuplicateManagers
AS (
    SELECT empid
        ,count(*) AS rcount
    FROM #DuplicationIdentifier
    GROUP BY empid
    HAVING count(*) > 1
    )
SELECT a.empid
    ,a.managerid
FROM #DuplicationIdentifier a
JOIN DuplicateManagers b ON b.empid = a.empID
ORDER BY a.empid


Quickly restore test/QA SQL Server database My company is releasing new mission critical software.
For thorough QA, we are doing database comparisons.

Our process is:

Restore full backup.
Test code.
Compare.
Repeat.

This database is relatively large, and step 1 can take up to 2 hours.

Is there any method someone could suggest that would speed this up?  We are making minimal changes when the new code is being run and compared.  I'd like to quickly revert back to before the test were run.

Thanks in advance!

I believe you could use database snapshots. Though snapshot can not replace full database backup, since it contains only amended pages from the moment the snapshot was created.

Essentially, you restore the production database onto the development server, take a snapshot, perform whatever test work you need to.  When you need to return to the original state of the database, revert the snapshot.

Here is MSDN articles that describes process of recovery to the state before a snapshot was taken.

http://msdn.microsoft.com/en-us/library/ms189281.aspx


Select all results from two independent tables I have two tables:

Comments
--------
id
comment_content
time_stamp

Activities
-----------
id
activity_content
time_stamp

And I would like to combine the results from both, ordered by their timestamp fields.

Combine the two tables with UNION and ORDER BY timestamp. You can also add an addition (type) column so the source of a row can be identified:

SELECT
    'comment' AS type, id, comment_content AS content, time_stamp
FROM
    comments

UNION ALL

SELECT
    'activity', id, activity_content, time_stamp
FROM
    activities

ORDER BY
    timestamp ;


Join on primary keys to primary key vs primary key in range Assume we have an #RelevantOrders table and an OrderRow table.

create table #RelevantOrders(Order_Id int primary key)

create talbe OrderRow (Order_Id int, OrderRow_Id, [lots of data columns], primary key (Order_Id, OrderRow_Id)

Now what I by instinct did initialy do was a simple join

select OrderRow.* from #RelevantOrders as ro join OrderRow on OrderRow.Order_Id = ro.Order_Id

Now, due to the data, the Order_Id in #RelevantOrders are several ranges of Ids, ie. 1,2,3,4,5,100,101...120, 240..250 ... hope i am being clear that.

So what happens, if there are 1000 Relevant orders, execution plan does 1000 index seeks on OrderRow table, 1 per each Order_Id. This leads quite 'big' scan count and logical reads (~5000).

SO it got me thinking, i would expect that instead of doing 1000 index seeks, it would do ~80 (number of continuous ranges) index seeks.

So i tried an alternative:

create table #RelevantOrderRanges(f int, t int)

select OrderRow.* from #RelevantOrderRanges
join OrderRow on OrderRow.Order_Id between f and t

The good news is ammount of index seek and logical reads drops to 80 index seeks (equal to amount of ranges) and 609 logical reads. Much better. The only issue i have here and thats due to my experience with it causing to gobble up memory is the estimated number of rows and estimated data size, which goes up to about 3.5GB in this case instad of 49KBs.

Now i know that in case number of ranges approaches number of orders any gain is lost.

So this got me thinking and i have two questions:
1) am i overthinking the estimates too much? I know more testing is required but i am a bit afraid the estimates would bite me later due to memory hog

2) is there any way to 'better explain' SQL what to expect? Indices, hints, statistics.... ?

Thanks for discussion

PERFORMANCE UPDATE:
TC 1:
Order_Id count: 9045
Range count: 5499
Total rows produced:

Range based approach:
~800ms CPU, ~800ms ellapsed time
Scan count: 5,500
Logical reads: 37,896

ID join approach:
~900ms CPU, ~900ms ellapsed time
Scan count: 9045
Logical reads: 56774

TC 2:
Order_Id count: 19684
Range count: 12849
Total rows produced: 124561

Range based approach:
~718ms CPU, ~770ms ellapsed time
Scan count: 12849
Logical reads: 74127

ID join approach:
~950ms CPU, ~950ms ellapsed time
Scan count: 19684
Logical reads: 110352

Edit. I misunderstood the question. I thought this was about app design rather than query performance.

For an App: Personally i would avoid using an intermediate table. Its just extra steps and complication.

The #table makes sense for queries.

Provided you have useful indexes and your stats are up to date, you are typically better off leaving SQL Server to decide the best execution plan.

Depending on the growth and distribution of data the best execution plan may change over time. So i would be reluctant to invest a lot of time and effort coding to squeeze the last 1% of extra performance.


Can't execute dynamic DDL, argument is NULL I'm running PostgreSQL 9.4.4.
I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly.

I'm trying to dynamically create partitions for a table. I've got the individual parts of the code working however I can't get the function to execute the dynamic DDL statements. If I replace the execute with an insert into a log table, the statements that are generated are valid and I can run them myself but if the function attempts to execute the following error is produced:

select part_test();
ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function phil_test() line 18 at EXECUTE statement

A reproducible function is as follows:

create table harry(logged_time timestamp, id integer);

create or replace function part_test () returns void as $$
declare
rec record;
begin
for REC in SELECT 'create table if not exists '||'harry'||'_'
                ||to_char(date_trunc('day', my_date), 'YYYY_MON_DD')
                ||' (check (logged_time>=DATE'''
                ||to_char(date_trunc('day', my_date), 'YYYY_MON_DD')
                ||''' and logged_time < DATE'''
                ||lead(to_char(date_trunc('day', my_date), 'YYYY_MON_DD'),1)
                  over (order by date_trunc('day', my_date))
                ||''')) inherits ('||'harry'||');' STMT
                FROM generate_series
                        ( '2015-SEP-01'::timestamp
                        , '2015-SEP-10'::timestamp
                        , '1 day'::interval) my_date
LOOP
    execute REC.STMT;
END LOOP;
end;
$$ LANGUAGE plpgsql;

@dezso already explained the direct cause of your error. But the whole function is needlessly convoluted. Use instead:

CREATE OR REPLACE FUNCTION part_test()
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN
   FOR _sql IN
      SELECT format ('
         CREATE TABLE IF NOT EXISTS harry_%s (
           CHECK (logged_time >= date %L AND logged_time < date %L)
         ) INHERITS (harry)'
       , to_char(d, 'YYYY_MON_DD')  -- safe against SQL injection!
       , d::date, d::date + 1)
      FROM  generate_series (timestamp '2015-09-01'
                           , timestamp '2015-09-09'  -- 1 day before last
                           , interval  '1 day') d
   LOOP
   -- RAISE NOTICE '%', _sql;  -- to debug
      EXECUTE _sql;
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Major points

Use format() to make concatenating statements simpler and safer.
No need for date_trunc(), the result of to_char() is the same.
You don't need a record variable, it's just text.
You don't need a window function, just add 1 day to the start.
Always use ISO 8601 format for date and timestamp literals. Those work irregardless of your current locale settings.
I am using default format in the CHECK constraint now. (date parameters to format() are cast to text with default format automatically.) You might consider the pattern 'YYYY_MM_DD' instead of 'YYYY_MON_DD' for the table name as well.
Resulting table names are lower case (like they should be), because I did not double-quote.

Are PostgreSQL column names case-sensitive?

Not sure why you compare logged_time to a date, and not a timestamp or timestamptz. There may be a problem with time zones / volatility lurking there. Consider timestamptz. Or if the column is actually a date type, don't call it "time" ...

The formatted date happens to be safe against SQL injection. Else you have to do more. Consider:

SQL injection in Postgres functions vs prepared queries


How to avoid intermittent "duplicate entry" error on slave when replicating mysql.time_zone_transition? Rarely, but intermittently, MariaDB 5.5 fails to replicate the timezone tables onto a completely new replica (instance creation is all automated, so there's no room for missing/changed steps). We never change these internal tables, so how can this happen?

Example result (formatted for readability):

$ mysql -e 'SHOW SLAVE STATUS\G'
[…]
           Master_Log_File: bin-log.000011
       Read_Master_Log_Pos: 5124
            Relay_Log_File: relay-log.000002
             Relay_Log_Pos: 527
     Relay_Master_Log_File: bin-log.000010
          Slave_IO_Running: Yes
         Slave_SQL_Running: No
                Last_Errno: 1062
                Last_Error: Error 'Duplicate entry '339--1956609120' for key
'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO
time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) […]

I am not aware of any automatic method inserting data to those tables? Maybe you are trying to load data to a table that already contains them? Which OS are you? Maybe the operating system is doing it without telling you. Maybe someone setup a cron doing on the master:

shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

https://mariadb.com/kb/en/mariadb/mysql_tzinfo_to_sql/

You can put a replication filter for now to avoid the issues, but then research the master's binaty log to understand who and where that is being inserted, and why. If those are legitimate, or it is automatized, make sure it is executed without writing to the binary log (SET SESSION sql_Log_bin=0)


For SSL renewal procedure in Db2 LUW , DO i need to create CSR request for each instance residing in same server? I am trying to renew my SSL certs . Stuck here if we need to have  create a Certificate Signing Request for each instance residing on the same server and send it to CA who will provide us the certificates .

There is no reason to create a different CSR for each instance on a host.  A non-wildcard SSL Certificate is tied to a particular server, so you would typically use the same certificate for all instances.

With multiple instances, you can create a CSR for the first instance to send to the CA, then receive the certificate into that instance’s keystore.  As long as you don’t have different contents for each instance’s SSL keystore, you can simply copy the files that make up one instance’s keystore to each other instance’s keystore.

Some might suggest having a single keystore that all Db2 instances use, but this creates other problems for maintaining security (as the keystore should only be accessible by the instance owner).  It might be possible to do this with ACLs, but creating that complexity may outweigh the benefit you’d get from having a separate keystores for each instance.


Update all values in a column with individual values I ran into a problem where Excel and SQL Server have different "zero day" date conventions, so that all the dates currently in one of my tables are two days ahead of where they should be. Is there an easy way to update all the dates?

I know I can do something like

SELECT DatabaseID, P60_Date, DATEADD(day, -2, P60_Date) AS NEW_P60_Date FROM US_P60

But I don't know how to UPDATE all the values in P60_Date with the new dates.

It should be this simple:

UPDATE u
SET u.P60_Date = DATEADD(DAY, -2, u.P60_Date)
FROM dbo.US_P60 AS u

If you want to be sure, you can do:

SELECT *
INTO dbo.US_P60_bk
FROM dbo.US_P60

Then validate the update is what you want. If it is, drop the _bk table.


Help on "At least one" query writing Given a matrix t1 as below, I want to verify the field id_ has at least one 'b' on column value1. Could you help me build the query?
CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

CREATE TABLE IF NOT EXISTS result (
   id_ INTEGER  NOT NULL,
   is_b INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 'a'),
(2, 'a'),
(2, 'b'),
(3, 'a'),
(4, 'b'),
(5, 'a')

INSERT INTO result (id_, is_b) VALUES
(1, 0),
(2, 1),
(3, 0),
(4, 1),
(5, 0)

Thank you very much!

SELECT `id_`,
       MAX(CASE WHEN `value1` = 'b' THEN 1 ELSE 0 END) as `is_b`
  FROM `t1`
 GROUP BY `id_`
 ORDER BY `id_`;

If you want that to go into result, slap an INSERT on top of it.


How does mysql decide if concurrent insert is possible for a MyISAM table? For "concurrent inserts", MySQL reference manual has the following explanation:

  The MyISAM storage engine supports concurrent inserts to reduce
  contention between readers and writers for a given table: If a MyISAM
  table has no holes in the data file (deleted rows in the middle), an
  INSERT statement can be executed to add rows to the end of the table
  at the same time that SELECT statements are reading rows from the
  table.

http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html

Let's say our database "concurrent insert" parameter is set to "Auto" (1).
And we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Or do we need to run "OPTIMIZE" before the table knows there are no gaps?

While you can do what Rolando suggests and set concurrent_insert=2 to always enable concurrent inserts, to answer your question about filling holes:

  we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Yes (emphasis mine):

  If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. [src]

Disclaimer: I haven't actually tested it. It seems unless you inserted the exact same data-length in the holes, you will still have holes somewhere.

You can see if there are holes from a query such as this (data_free=0 would mean no holes):

SELECT table_name, data_free FROM information_schema.tables WHERE table_schema='FOO' AND engine='myisam'


PostgreSQL 8.4: How to tell if a procedural language is installed or not? I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the language and re-add it, as that could screw some other things up.

Is there a way to install the language "gently"?
CREATE LANGUAGE IF NOT EXISTS does not appear to be valid.

In PostgreSQL 9.0 and later, PL/pgSQL is pre-installed by default.
Version 9.0 also introduced CREATE OR REPLACE LANGUAGE:

  CREATE OR REPLACE LANGUAGE will either create a new language, or
  replace an existing definition. If the language already exists, its
  parameters are updated according to the values specified or taken from
  pg_pltemplate ...

To avoid raising an exception on older versions you can check the catalog table pg_language. I quote the manual once more:

  The system catalog pg_language [...] records information
  about the currently installed languages.

SELECT EXISTS (
  SELECT 1
  FROM   pg_language
  WHERE  lanname = 'plpgsql');

Or use the client application createlang, which has an option to check for existing languages:

createlang -l [connection parameters]


ORA-29339: tablespace block size 16384 does not match configured block sizes My box is RHEL 6.2, and file system BLOCK SIZE is 1024.

# tune2fs -l /dev/sda1
...
Block size:               1024
...

My Oracle is 11g, and database block size is 8192.

SQL> show parameters db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

I create a tablespace, set block size 16K.

CREATE BIGFILE TABLESPACE "C_IDX" DATAFILE '/u01/oradata/weixin/c_idx1.dbf'
SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO blocksize 16K;

then  i got error ora-29339.

ORA-29339: tablespace block size 16384 does not match configured block sizes

It seems the reason is  DEFAULT 16K buffer cache is 0.

SQL> select component, current_size from v$sga_dynamic_components;

    COMPONENT       CURRENT_SIZE
    shared pool 402653184
    large pool  16777216
    java pool   16777216
    streams pool    16777216
    DEFAULT buffer cache    1308622848
    KEEP buffer cache   0
    RECYCLE buffer cache    0
    DEFAULT 2K buffer cache 0
    DEFAULT 4K buffer cache 0
    DEFAULT 8K buffer cache 0
    DEFAULT 16K buffer cache    0
    DEFAULT 32K buffer cache    0
    Shared IO Pool  0
    ASM Buffer Cache    0

Now the problem is how large  should i set it, or according to what i set the value?

SQL> alter system set db_16k_cache_size= ?

Thanks.

your db_block_size is 8k and your db_16k_cache_size = 0.

Normally we don't build databases with mulitple block sizes because the optimizer does not take the different block sizes into account. If you still want to do this, start with something like

alter system set db_16k_cache_size = 16m;

and try again.
It does not have much to do with the filesystem blocksize but aligning your database storage blocks sizes with the storage blocksizes is performance wise a smart thing to do.


best approach to keys, if data from separate DBs to be presented in same UI Suppose we are creating databases containing information about monkeys, and a user interface to present monkey information to users.

We have two separate databases. The schemas are identical (and are at least intended to remain so) but they are on different machines and have information on different monkeys. This is inconvenient but unavoidable.

The user interface we're building queries both databases and presents all the monkeys in the same filterable HTML table to the user.

What's the best approach to generating IDs for monkeys? Is it acceptable to prefix the ID according to which database the monkeys are in?

I'd consider composite PK instead of prefixing values in one column. It seems a bit more clear to me, and it should help avoiding potential problems if you switch to single server (or cluster) in the future (then this column can be used as partition key).


SQL Server Failed Login - SQL Server login (not windows) - Random times We have the following situation and am wondering if anyone can suggest a way of determining the cause.

One of our SQL Developers can log into, and use, the instances/databases fine and has a SQL logon with sysadmin privilages. He works from approx 8am to 5pm and has no issues.

However we get random "Insufficient Permissions for Action Attempted" for his SQL logon, at random times, with the message:

  Login failed for user '(user)'. Reason: Password did not match that
  for the login provided. [CLIENT: xxx.xxx.xxx.xxx]

The (user) is this developer's Logon, and the CLIENT IP address is the PC he uses on a daily basis.

The only real difference between the developers is this user is using SSMS 18.0 and the others are still on older versions (17.x)

The messages occur all times of the day. The developer doesn't work remotely and would definitely not be trying to log in at 3am.
At one point, we asked the developer to shut down his machine at the end of the day. The alerts stopped happening for a few days but eventually returned.
I've looked at running processes and there's nothing obvious as to what might be causing it.
We've looked into a variety of other potential things, such as incorrectly configured SSIS connection managers.

Does anyone have any suggestions on actions that may lead to finding out the offending "thing" - would wireshark traces help or is there a better way of figuring it out? Thanks for reading.

I would look for some kind of automated process... either a SQL Agent job, a batch file connecting to SQL, etc.
There is some good chance that the login use for this process does not have the right password.

You can create an extended event (or a trace) and capture the "Error Reported". Make sure you include the "application name" in the collected field and you should see something like :

This should give you some good info to find the cause of your issue.


Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script.

INSERT INTO params (extension, type, subtype, paramvalue)
VALUES ('12345', 'FRNR1', 'FORWARD', '24268')
ON DUPLICATE KEY UPDATE paramvalue = VALUES(paramvalue);

The purpose is that params_exceptions contains records which prevents certain records in params to be updated with the new data (or using the data in params_exceptions afterwards for that record).

I've been messing around a bit and I was thinking of combining WHERE NOT EXISTS within the ON DUPLICATE KEY UPDATE? Of course all of the above is possible by using sequential statements, but it's possible that the script would run at the same time and I can't use transactions for now.

I was thinking of the following statement, but I can't seem to figure it out:

INSERT INTO params (extension, type, subtype, paramvalue)
SELECT *
FROM (SELECT '12345', 'TEST', 'TEST2', '24268') AS tmp
WHERE NOT EXISTS (
    SELECT 1, extension
    FROM params_exceptions t
    WHERE extension = '12345' AND
          type = 'FRNR1' AND
          subtype = 'FORWARD'
) LIMIT 1
ON DUPLICATE KEY UPDATE subtype=subtype, paramvalue=t.extension;

Can someone give me some advice here?

The problem in your query is this part:

ON DUPLICATE KEY UPDATE
    --
    paramvalue = t.extension ;

The alias t is not visible in that part of the query.

However, assuming that the two values, the one to be inserted in the params.extension column (12345) and the one checked against the params_extensions column (WHERE t.extension = '12345')  are the same, then you you can use the VALUES (extension) in the UPDATE part:

Several other improvements can be made:

no need for a derived table (tmp), you can use the internal dual table.
the UPDATE subtype = subtype is redundant
the inetger values (eg. 12345) need not to be passed quoted. They are not string literals, they are integer values.

The statement becomes:

INSERT INTO params
  (extension, type, subtype, paramvalue)
SELECT 1234567, 'TEST', 'TEST2', '24268'
FROM dual
WHERE NOT EXISTS
  ( SELECT 1
    FROM params_exceptions t
    WHERE extension = 1234567
      AND type = 'FRNR1'
      AND subtype = 'FORWARD'
  )
ON DUPLICATE KEY UPDATE
  paramvalue = VALUES (extension) ;


How do Savepoints work in Oracle? I'm doing pretty serious operations on my DB and I would like to keep my data safe.

Unfortunately something is going wrong, look:

SAVEPOINT before_foo_update;

CREATE TABLE AUX_FOO as
   SELECT * FROM FOO;

TRUNCATE TABLE FOO;

ALTER TABLE FOO
  MODIFY(BAR NUMBER(11) NOT NULL);

INSERT INTO FOO
  SELECT * FROM AUX_FOO;

DROP TABLE AUX_FOO;

ROLLBACK TO SAVEPOINT before_foo_update;

Unfortunately I'm getting an error:

ROLLBACK TO SAVEPOINT before_foo_update
Error report:
SQL Error: ORA-01086: savepoint 'BEFORE_FOO_UPDATE' never established
01086. 00000 -  "savepoint '%s' never established"
*Cause:    Trying to roll back to a save point that was never established

Now FOO table is empty...
And AUX_FOO wasn't dropped.
Do you know what I'm doing wrong?

A savepoint is a point within the current transaction.  A DDL statement like CREATE TABLE, TRUNCATE, ALTER TABLE or DROP TABLE will issue an implicit commit before and after the statement runs that will end the current transaction.  Once the current transaction ends, you can no longer rollback to a savepoint defined within that transaction.

Potentially, you want to look at using a restore point instead.  A restore point is an attribute of a database, not a transaction, so you can generate many transactions and still restore the entire database back to the restore point.  Restoring to a restore point, however, will affect every change that had been made to the database between the time that you defined the restore point and the time that you do restore-- you would lose all changes made not just by your session or your user but all sessions and users in the database.


Does SQL Server use pointers instead of storing duplicate rows? I'm using the built in sp_spaceused stored procedure before and after performing a operation in our software to see which tables have row inserts and how the size of each table changes.

What I'm seeing is that out of all the tables that get rows written to them, only a handful show that the table has increased in side. The others that show rows were added show no change in size from this stored procedure.

The only case this is not true is on the first transaction after performing a truncate on all the tables. So to me it appears that instead on storing duplicate data SQL Server is showing rows are inserted but must be just storing pointers to previous identical rows.

Can anyone confirm this please?

No, SQL Server does not detect duplicate rows

SQL Server is filling up empty or partially empty pages within the allocated pages.

So if I have a very narrow row (2 columns say), I can add a few hundred more rows on the same page without increasing space used.

Quick and dirty demo (without duplicate rows, but you can play with this if you want)

IF OBJECT_ID('dbo.Demo') IS NOT NULL
    DROP TABLE dbo.Demo;
GO
CREATE TABLE dbo.Demo (DemoID int NOT NULL IDENTITY(1,1), Demo char(1) NOT NULL)
GO
SELECT 'zero rows, zero space', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('a');
GO
SELECT 'one row. Peanuts', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('b');
GO 100
SELECT '101 rows. All on one page', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('b');
GO 1899
SELECT '2000 rows. More than one page', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

TRUNCATE TABLE dbo.Demo
GO
SELECT 'zero rows, zero space. TRUNCATE deallocates pages', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

INSERT dbo.Demo VALUES ('c');
GO 500
SELECT '500 rows. Some space used', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

DELETE dbo.Demo
GO
SELECT 'zero rows after delete. Space still allocated', SUM(ps.reserved_page_count)/128.0 AS ReservedMB, SUM(ps.used_page_count)/128.0 AS UsedMB
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id = OBJECT_ID('dbo.Demo')
GO

IF OBJECT_ID('dbo.Demo') IS NOT NULL
    DROP TABLE dbo.Demo;
GO


SQL Server Transaction Logs in the Cloud It is a common practice to place the transaction logs (*.ldf) on a separate physical disk system than database files (*.mdf, *.ndf).

In a virtual machine cloud environment, is it okay to put the transaction logs on the same drive letter as the other data files? How is this usually handled in popular cloud virtual machine environments of today.

NOTE: Not using SQL Azure. Using a Windows virtual machine with full access.

In my extensive testing of cloud SQL on cloud servers (rackspace cloud, to be specific), I found that splitting the ldf and mdf to distinct block storage volumes made a significant improvement in performance. I achieved really hot performance by putting the ldf on an SSD based block storage volume and the mdf on a standard block storage volume.

Ultimately, of course, a block storage volume is a shared SAN somewhere. And local "instance" storage is on the hypervisor itself. So the hardware topology of your cloud environment will dictate what config will work best.


How can I safely rename a sequence in PostgreSQL, ideally without downtime? We want to rename some sequences in our production database for certain reasons.

Is there a way to do this safely in production without having to first close all connections to the database?

It's fine if we get gaps in the ID sequence (e.g. it jumps from ID 123 to ID 200 or whatever) but we obviously don't want long locks causing delays/errors, don't want any risk of duplicate IDs and similar.

We're on PostgreSQL 9.5.21 on Heroku.

We've looked at the docs but are still uncertain about the consequences of running

ALTER SEQUENCE old_id_seq RENAME TO new_id_seq;
ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq');

in production. I guess one risk would be if stuff happens in mytable between those two commands. But what if we did something like this:

-- Starting at a much higher value than the currently highest ID.
CREATE SEQUENCE new_id_seq START 200;

ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq');

DROP SEQUENCE old_id_seq;

What are the risks of doing it that way?

If the sequence is used in the DEFAULT clause of the table column, it is enough to rename the sequence.

That is because the DEFAULT clause is not stored as string, but as parsed expression tree (column adbin in catalog pg_attrdef). That expression tree does not contain the name of the sequence, but its object ID, which is unchanged by renaming the sequence. Tools like psql's \d re-construct a string from the parsed expression, so the DEFAULT clause will appear to reflect the renaming.
If the sequence name is used elsewhere, like in your client code or in a PostgreSQL function, you would have to change the name in that code. PostgreSQL functions are stored as strings (column prosrc in catalog pg_proc), so renaming a sequence can make a function that uses the sequence fail.

In this case, you would have to suspend activity until you have changed the code and renamed the sequence if you want to avoid errors.


Any reason to have both an index with included columns and one without? I am looking through a few slow running queries on a database that has under gone significant development (under a number of different developers) over the last couple years.
One of the most commonly used tables has a couple indexes with the same indexed columns, but one will have included columns and the other will not.

For Example:
One Index:

 CREATE NONCLUSTERED INDEX [IDX_tblTable_IndexedColumnId_Inc] ON [dbo].[tblTable]
 (
[IndexedColumnId] ASC
 )

And then the other:

CREATE NONCLUSTERED INDEX [IDX_tblTable_IndexedColumnId_Inc] ON [dbo].[tblTable]
(
[IndexedColumnId] ASC
)
INCLUDE (   [Field1Id],
[Field2],
[Field3],
[Field4],
[Field5],
[Field6])

I would assume this is just an oversight and is just using up disk space and additional overhead to have the index without the included columns (the included columns are known to be needed).

Is there any benefit of having two indexes on the same column, one with included columns and one without?

To expand a little on what everyone's favorite n-dimensional analogue commented, there may be some benefit to having both.

Here's a small (but not inadequate) demo.

The necessary:

USE StackOverflow;
SET NOCOUNT ON;

CREATE TABLE dbo.IndexCrap
(
    Id INT IDENTITY PRIMARY KEY CLUSTERED,
    Whatever DATETIME,
    Nonsense VARCHAR(50),
    Etc UNIQUEIDENTIFIER,
    SoForth BIT
);

INSERT dbo.IndexCrap WITH (TABLOCK)
        (Whatever, Nonsense, Etc, SoForth )
SELECT TOP 1000 DATEADD(DAY, x.n, GETDATE()),
                REPLICATE('A', x.n % 50),
                NEWID(),
                CASE WHEN x.n % 15 = 0 THEN 1 ELSE 0 END
FROM (SELECT ROW_NUMBER() OVER (ORDER BY @@ROWCOUNT) AS n
        FROM sys.messages AS m ) AS x;

The indexes:

CREATE INDEX ix_tinydancer ON dbo.IndexCrap (Whatever);

CREATE INDEX ix_largemarge ON dbo.IndexCrap (Whatever) INCLUDE (Nonsense, Etc, SoForth);

So like your question, on index on one column, one index on the same column with some includes.

The queries:

SELECT COUNT(*)
FROM dbo.IndexCrap AS ic;

SELECT ic.Nonsense, ic.Etc, ic.SoForth
FROM dbo.IndexCrap AS ic
WHERE ic.Whatever >= DATEADD(DAY, 500, GETDATE());

The execution plans:

For the COUNT(*) query, the optimizer chooses our smallest single column index over the wider nonclustered index and the slightly wider clustered index, since it's not filtered, and all we need is a row count.

For the wider query, the optimzer chooses the covering index so it doesn't have to a) scan the clustered index or b) access the narrower index and do Key Lookups back to the clustered index.

Of course, you may not know every single query that runs on your system. That's why the company I work for writes sp_BlitzIndex, a free tool to analyze your indexes.

If we run EXEC master.dbo.sp_BlitzIndex @DatabaseName = 'StackOverflow', @Mode = 4;, here are some results we're interested in:

The two indexes I added to IndexCrap (not counting the PK/CX) get flagged as having duplicate key columns. It also gives their definitions, usage, size, and a whole lot more not shown in the screencap.

The usage counters aren't perfect. They'll reset if you add or drop indexes, in some versions of SQL Server they'll reset if you rebuild indexes, and of course they reset if you restart SQL Server.

Hope this helps!


Backing up large MySQL DB for DR purposes Fundamentally I am just trying to come up with the best way to back up my MySQL DB nightly.  Complicated by the fact that:

its a 400 gb DB and growing
its running on windows
I am not permitted any real downtime

I have two replication servers going which I used mysqldump with a read lock to get going and I plan to implement any back-up strategy from a slave.

I have thought about setting up an additional replication server and running percona xtrabackup from it but I am not sure what my recovery strategy might look like taking back-ups from a linux box to restore on windows.

mysqldump is no longer a good strategy as the full process dump/zip/move/restore is 2 days at this point.

it's innoDB with a few myisam log tables mixed in.

looking for any insight or direction from those that have gone down this road already.  Any options outside of oracles enterprise back-up?

thanks

xtrabackup from a slave will work fine.

If you apply redo log on a linux machine(innobackupex --apply-log) you can use the restored datadir on windows.


Data Migration and altering tables in one SSDT deployment script I'm using SSDT and MS SQL Server 2012.
And I need to handle next cases in one deployment script:
--1
CREATE TABLE Table1 (Col1 INT)
--2
INSERT INTO Table1(Col1)
SELECT SomeCol FROM SomeTable
--3
ALTER TABLE dbo.SomeTable DROP COLUMN SomeCol
--4
INSERT INTO AnotherTable(AnotherCol)
VALUES(1)

What can I see now in my deployment script:

--Pre-deployment part
--some insert. Will fail because it select/insert from/to not created objects

--Deployment part
CREATE TABLE Table1 (Col1 INT)
GO
ALTER TABLE dbo.SomeTable DROP COLUMN SomeCol
GO

--Post-deployment part
--some insert. Will fail because it select/insert from/to not created objects

How do I need to configure post/pre-deployment scripts?
Is it possible to do it in SSDT automatically during deployment?

Appreciate for any proposals.

I'm unsure if this is in the version of SSDT/Visual Studio you're using but as far as I know, in the Database Project type - you can add Pre/Post Deployment scripts by simply adding right clicking your project and adding a Script > Pre/Post-Deployment script.

When you publish this, and generate the script, you'll see the pre and post deployment scripts in the SQLCMD syntax.

In terms of doing it automatically, I don't think what you're asking is possible. Personally, I found generating DACPACs to be quite useful for deployment. Develop your database and publish to a DACPAC and you can almost automate your deployment. However, your specific example requires some knowledge of your implementation, so I can't think of a way it could programmatically know this. Apologies if I'm not reading you correctly.


sliding window partitioning on date field I need to apply Sliding Window Partitioning on a table that contains 1B of records.

CREATE TABLE table_1(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [log_date] datetime NULL,
    [field1] [bigint] NULL,
    [field2] [int] NULL,
    [field3] varchar(20) NULL,

 CONSTRAINT [PK_table_1] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I want to do the partitioning on log_date field (1 partition per day) , knowing that my queries will not use log_date field , and only Id will be used.

I'm confused here if I have to add log_date to my clustered index , or Id field alone will be alone.
Also if I added log_date to the clustered index , can I still query on the table while filtering only on the Id ?

Edit:

sample of query I'm using :

declare @i int = 1000000
select * from table_1 where id>=@i and id<@i + 200000 order by id

Starting from the fact that I wouldn't partition on a column which is not used in a where clause, yes, your clustered index will have to change to include the log_date into its key columns, and yes, you can still query it filtering on ID only. I'd still suggest to include the partitioning column into your where clause to leverage partition elimination.

It's explained to much detail here: https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-2017


Replace & Update all rows sqlite I have a table called Players and columns Name, PlayerID. I am using sqlite under DB Browser for SQLite.

Unfortunately, all my player's names have a something like a "\n" (turned out to be a /r) at the end of the name.

Ex:

"Mark
"

I tried to Update & Replace all the names with the following query (I have like 450 rows in the table):

UPDATE Players
SET Name = REPLACE(Name,CHAR(10),'')
WHERE PlayerID <= 500

When I execute something like:

SELECT * FROM Players
WHERE Players.Name LIKE 'Mark'

it'll return no rows because of the end line.

I want to change all my rows from this format

"Mark
"

to this

"Mark"

and save all the changes.

How can I solve my problem? What's wrong?

If you want to remove all line feed characters (\n), then what you have is ok. You could also remove the WHERE, to update all rows:

UPDATE Players
SET Name = REPLACE(Name, CHAR(10), '')
-- WHERE PlayerID <= 500
;

Similarly, if you want to remove all spaces, do the same:

UPDATE Players
SET Name = REPLACE(Name, ' ', '')
-- WHERE PlayerID <= 500
;

Beware though that the above will change 'Mark Jenkins' to 'MarkJenkins'. Are you sure you want to do that?

You could also do both actions in a single statement, with:

SET Name = REPLACE(REPLACE(Name, CHAR(10), ''), ' ', '')

Before running the actual update, you could examine the changes with:

SELECT Name,
       REPLACE(Name, CHAR(10), '') AS NewName_NoLinefeed,
       REPLACE(Name, ' ', '') AS NewName_NoSpace,
       REPLACE(REPLACE(Name, CHAR(10), ''), ' ', '')
           AS NewName_NoLinefeed_NoSpace
FROM Players
-- WHERE PlayerID <= 500
;


MySql - selecting into file and re-loading it with special characters I have a MySql table called twitter_statuses which as the title suggests holds twitter statuses.  here is the table structure.

this table contains all sort of characters in the text column, and some of the column holds Ruby object which are multi lined.

Im trying to select some of the records into a file and then load them to a new table but because of all the special characters some of the rows get "messed up".

here is the query I use to dump the statuses

here is the query I use to re-load the dumped statuses

As you can see I tried replacing the commas in the text column with **** to prevent them from splitting the fields, and replaced the new line in the multi line columns (urls, hashtags, user_mentions) to prevent "cutting the line" when dumping the file to CSV.

currently the multi line fields load great (the replacement of the new line character works) my problem id with other characters that appear in other fields (all sort of character) with cause to split columns.

any ideas on what could I change there?

I would suggest remove all those REPLACE(text,',','****') stuff, and use FIELDS ENCLOSED BY...:

SELECT
  id,
  status_twitter_id,
  twitter_id,
  creator_twitter_identity_id,
  text,
  in_reply_to_status_id,
  in_reply_to_user_id,
  urls,
  hashtags,
  created_at,
  updated_at,
  sent_at,
  user_mentions
FROM
  twitter_statuses
into outfile '/tmp/twitter_statuses.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;

You then LOAD DATA INFILE like this:

LOAD DATA LOCAL INFILE '/srv/$SPLIT_FOLDER/$f'
INTO TABLE my_db.twitter_statuses_new
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
...

This should do the work.


Is it possible to Clone an Existing MYSQL Slave Database into Amazon RDS with the RDS clone being a slave to an external Master? We are interested in migrating our data from one of our MYSQL slave databases to an Amazon RDS instance such that the new RDS database is a slave database to an external master DB from our colocation/facility.

I was reading through the documentation for RDS and was not keen on bringing our master mysql server down/stop writes while the import into RDS is taking place.  I would prefer doing that import processing on an existing slave database.

We use some of the existing Percona tools to create slaves from existing slaves but I'm sure there are limitations on RDS so any helpful advise is appreciated.

Master MYSQL Version: 5.6.11-log MySQL Community Server (GPL)
Slave MYSQL Version:  5.6.11-log MySQL Community Server (GPL)

This is completely possible. The only type of import you can use with RDS is a logical restore, so mysqldump. You will want to use the --dump-slave=2 option :

mysqldump --dump-slave=2 (other options) > dump.sql

This will add the CHANGE MASTER statement as a SQL comment to the dump.sql file. Take the binary log file and log position from that statement and set the RDS up accordingly:

CALL mysql.rds_set_external_master (
  host_name
  , host_port
  , replication_user_name
  , replication_user_password
  , <binary log file>
  , <binary log position>
);

[src]

I would caution you that your replication events are going to be sent as plain text, since ssl_encryption is not implemented for RDS MySQL. If your existing master is not in AWS, to be secure you would need to have your RDS instance in a VPC and set up to communicate with a VPN in your existing infrastructure. Read more about this in the docs: https://aws.amazon.com/vpc/


Extracting text from a column I want to import data from Excel into an existing table in a SQL Server database.

I have a column like Gender and the values are Male and Female in Excel. I want to insert only the first character into database, like M and F.

How should I achieve this thing in SSIS for SQL Server 2008?

You can use a Derived Column transformation. Add an expression that converts the gender column into the initial. You can use something like SUBSTRING(GENDER,1,1) for this.


Linked Server Between SQL Server and iSeries is rounding decimals We just set up a new SQL Server 2008 database, and I got the link set up between our server and an iSeries As400 server that we get some of our data from.

The problem I'm having is that it is rounding down any floating numbers, which is a pain because we're working with dollar amounts a lot of the time.

select * FROM openquery(NADS, 'select CMD, 1.23 Type from CREDD')

On our older server this returns the CMD column, and a column called Type with 1.23 all the way down. On the new server type is just 1.00

So far as I can tell, the link part is set up the same. The IBM i Access for Windows ODBC Setup is mostly the same, but there are some differences between versions that I'm not sure about.

Edit:
In case it matters, For the provider I'm using Microsoft OLE DB Provider for ODBC Drivers

Edit2:

provider options are the same

Linked Server properties:

iSeries Access for windows ODBC setup appears to be the same

Figured it out!!

Apparently we didn't have the latest service pack installed. We had to install the 64bit version of the service pack (32bit version failed to install), and when it finished the decimal numbers were coming across.

EDIT: In case anyone was wondering:
We're using "IBM iAccess for Windows 7.1 (07.01.0800) (64bit)" and using "Microsoft OLE DB Provider for ODBC Drivers" as the linked server sql driver.

EDIT 2017: Seeing as I just ran into this problem again and had to hunt down the service pack again, here is the link to the service pack download page, so that in a few years when I run into this problem again I will find it easier...

http://www-03.ibm.com/systems/power/software/i/access/windows_sp.html


Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4.

After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad at one point that the decision was made to revert back to the old cardinality estimator by enabling the 9481 traceflag and UPDATEing statistics again. When I say "got so bad" I'm referring to the execution times for queries increasing by a magnitude of 10 in some cases.

Using traceflag 9481 has resolved the problem but this can't be the solution can it?

Searching Google has shown some taking the old cardinality estimator route and others using a combination of 2312 and 4199 to use the new estimator.

So after upgrading from 2008R2 to 2014, what combination of traceflags (if any) and other steps should we be taking?

Thanks,
Craig

Update April 26th 9am

The 4199 traceflag does not turn on the new Cardinality Estimator. I had to use the 2312 traceflag instead.

With the 4199 traceflag, the version was still on 70. The answer from Chris Wood reminded me of an article from Brent Ozar I'd also read at some point. Still waiting to see if the execution times improve.

Based on my experience with such issue and as mentioned in This Blogs.msdn article

  You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix.SQL Server 2014 Service Pack 1 made various fixes on new Cardinality Estimator (new CE).  The release notes also documents the fixes.

So I suggest to suppress regression you enable it.

Trace flag 9481 solved your problem because if forced SQL Server to use old optimizer. So basically you are using SQL Server 2014 but not new CE which comes with it.

Trace flag 4199 makes sure the optimizer is using all changes/fixes made to SQL Server optimizer since SQL Server 2005.

From this support article

  Trace flag 4199 was used to collect hotfixes that were intended to become on-by-default in a future release, whereas other trace flags were used for situations in which a fix was not intended to become on-by-default in current form. Starting with SQL Server 2016 RTM, the database COMPATIBILITY_LEVEL setting will be used enable trace flag 4199-related hotfixes on-by-default. This article describes the mechanics and policy of how plan-affecting hotfixes will be delivered for SQL Server 2016 and later versions.

There are three ways to do it depending on need.

By enabling the trace flag in a batch (by using DBCC TRACEON command) right before the target query, and then disabling the trace flag (by using DBCC TRACEOFF command) right after the query.
Starting with Microsoft SQL Server 2005 Service Pack 2 (SP2) and Microsoft SQL Server 2008, the query-level option "QUERYTRACEON" is available. This option lets you to enable a plan-affecting trace flag only during single-query compilation.
You enable it in starup parameters.

The first and second point will only make sure query/batch runs with 4199 trace flag while third point makes sure any query which runs sees this trace flag enabled.

NOTE: Enabling trace flag requires sysadmin permission so if you ask your developer to use it in query for instance he might not be able to assuming developers have limited access.

I would suggest you to use querytraceon or dbcc traceon to see if queries are performing up to mark.


Always On Architecture Question I have three servers Server1, Server2 and Server3. Server1 contains DB1, DB2 and similarly Server2 Contains DB3 and DB4. Can I use Server3 to host the AG groups of Server1 and Server2.

I have drawn a very bad picture below my apologies :).
AG1 and AG2 represents availability groups. Is the below possible?

Can I use Server3 to host the AG groups of Server1 and Server2.

Your scenario is possible. AG1 and AG2 should be different availability groups. Also, all the 3 servers should be part of same Windows cluster.

The only problem that I can see is if server3 goes down (for whatever reason, blue screen, disk failure, or any disaster) then you will loose DR for server1 and server2 both at the same time. Also, server3 when a failover occurs from both server1 and server2 should be powerful to support the load interms of CPU, DiskIO and Memory.


Creating a column in dataset for later filter use (mysql) I have a date column (format: 14.05.2018) and a count of sales. I would like to create an extra column that from the current date perspective flags fields that are +/-6 days from today. The following query does not work quite well, the column is empty
select `Date`, `Sales`,
(CASE
    when DAY(CURRENT_DATE())-6 <= DAY(`Date`)
            AND DAY(CURRENT_DATE())-6 >=  DAY(`Date`) then '1'
    when DAY(CURRENT_DATE())-6 > DAY(`Date`)
            AND DAY(CURRENT_DATE())-6 < DAY(`Date`) then '0'
 end as Flag)
from sales;


How to reuse an update trigger for multiple tables in postgresql? In the database for a new project, I've started the convention of having each table have a last modified timestamp column.  To implement this, I wrote a trigger for each table:

CREATE TRIGGER touch_users
  BEFORE UPDATE
  ON users
  FOR EACH ROW
  WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
  EXECUTE PROCEDURE touch_modification_time();

CREATE TRIGGER touch_company
  BEFORE UPDATE
  ON company
  FOR EACH ROW
  WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
  EXECUTE PROCEDURE touch_modification_time();

-- etc for each table

This gets tedious fairly quickly; especially since they're exactly the same, except for the table name.

Is there a way I can reduce the boilerplate required for each table?  Or possibly even eliminate it altogether with a single trigger that acts on every table by default?

Single trigger function for multiple triggers: possible - that's what you do.
Single trigger for multiple tables: not possible.

But you can shorten the code for creating lots of triggers:

DO
$$
BEGIN

EXECUTE (
SELECT string_agg('CREATE TRIGGER touch_users
   BEFORE UPDATE ON ' || quote_ident(t) || '
   FOR EACH ROW
   WHEN (OLD.modification_time IS NOT DISTINCT FROM NEW.modification_time)
   EXECUTE PROCEDURE touch_modification_time();'
 , E'\n')
FROM unnest('{users, company, foo, bar}'::text[]) t -- list your tables here
);

END
$$;

Or gather table names from system catalogs:
Tables accessed during last period


Is there a way to find out what queries are run the most against a particular table? We have been experiencing issues with slowness regarding our Master Data Services application. So we have looked to see which tables have the highest value of range scans, singleton lookups, page lock waits, etc. Can you tell me if there is a way to see the most queries run against a particular table in our MDS database.

Is there a way to find out what queries are run the most against a particular table?

  T-SQL – Get a Historical Record of SQL Statements Hitting a Table

  This query checks the sys.dm_exec_query_stats system view and the
  sys.dm_exec_sql_text table valued function.

  Also because these are system tables, you will need to be logged into
  SQL Server Management Studio (SSMS) with an account that has
  sufficient access permissions.

  Here is the query:

 SELECT
 qryStats.last_execution_time AS [Time]
 ,qryText.TEXT AS [Query]
 ,DB_NAME(qryText.[dbid]) AS [Database]
 ,OBJECT_NAME(qryText.[objectid]) AS [TableName]
 FROM sys.dm_exec_query_stats AS qryStats
 CROSS APPLY sys.dm_exec_sql_text(qryStats.sql_handle) AS qryText
 WHERE qryText.TEXT LIKE '%TableName%'
 ORDER BY qryStats.last_execution_time DESC

  Running this query will show you in reverse chronological order
  what SQL was run against a particular table on your database server.

  If you are using stored procedures then you’ll see that rather than
  showing the EXEC statement, SQL Server will have logged the CREATE
  statement for the stored procedure itself. For our purposes this is
  useful since it allows us to also find stored procedures that are
  having an effect on the table that we are interested in.

  The downside of this query is that it will not show you what user
  account is running each query.

  source


Implementing older version of mySQL on RH9 I'm building a virtual machine running Red Hat Linux 9 (Shrike) and MySQL. This is to virtualize a current production system that needs new hardware.  Questions:

Can I copy the existing mysql daemon executables to the new machine from the old one and if so, where are they located, and what is involved?

Are their archived packages of the early mySQL packages? I was hoping to find the exact version running on my old server, to do an install, and the copy the database file.

Update:
Hi..... many thanks for the ideas.  I'm pretty sure the copy is the way to go.... (thanks Tometzky), but it is somewhat fraught, as the old hardware has a software RAID array, so I'm not sure I can do a bitcopy and have it work. And, the original mySQL is 3.23 (just about the first production version....). Earliest source I've found was at SkyServer which started at version 4.x.
A dd from one /dev/sda to /dev/sdb seems to work fine (simulated in VirtualBox).  So far an /dev/md0 (from a RAID 1 drive) doesn't work....but I'm going to try copying partitions individually, and then see if that will work.
Our original mySQL is 3.23 (just about the first production version....). Earliest packaged binaries at SkyServer are 4.1
Obviously, this is a temporary move to buy us some time while we re-write the applications that query the mySQL.

Personally, I would not risk moving the installed binaries laterally to a newer OS because you may have to drag along needed libraries WHICH MAY NOT WORK IN THE NEWER OS !!!

You should thoroughly look through MySQL's Archives, which goes back to MySQL 5.0.15.

SkySQL also tries to fully accommodate users of older versions of MySQL, MariaDB, and more. Please go to their repository and find the version you need. They go back to MySQL 4.1.

You should make sure SkySQL has RHEL6 builds, at least.

You could also try compiling MySQL from source within RH9. DISCLAIMER : Never Built MySQL From Source


Creating Synonym For a Table in PostgreSQL over dblink? I have two PostgreSQL databases connected over dblink extension. I want to create synonym for a table in first database so the queries that are coming through application I can route them to the table in the second database. It will be good to mention that both databases has the same structure.

Is it possible to create a synonym or is there any alternative way to do so?

That's exactly what foreign data wrappers and foreign tables were created for:

You create a foreign server pointing to the source server, and then a foreign table that enables access to that remote server.

Some examples for that are in the description of the Postgres foreign data wrapper:

https://www.postgresql.org/docs/current/static/postgres-fdw.html


Restore a specific file group to a test environment without restoring the Primary file group (Error 3154) Scenario.

I have a filegroup that we need to restore to get data back that has
been removed from the database after the last backup I cannot touch
The production systems because they are running
The primary filegroup is just over 1TB in size (don't blame me I didn't do that and it makes me want to cry)
I don't have anywhere large enough to restore that Primary filegroup
The filegroup I wish to restore is only 60 GB in size
I simply need the data from one table out of the filegroup
The system is all in a remote location so the backup can not be removed from there and potentially loaded elsewhere
I have a test 'copy' of the database, but that is generated through scripts and is 1/1000 of the size but contains all the same tables etc as the main database, just next to none of the data content
I only need to restore from the full backup

Code Sample

RESTORE DATABASE testEnvironment
filegroup = 'requiredFileGroup'
from disk =
N'\\remotestorage\backupfile.bak'
with REPLACE,
move
N'requiredFileGroup_file1' to N'F:\requiredFileGroup_file1.ndf',
move
N'requiredFileGroup_file2' to N'F:\requiredFileGroup_file2.ndf',
stats = 1

Issues

When trying to restore I get the error 3154

The backup set holds a backup of a database other than the existing 'testEnvironment' database.

This occurs when trying to restore to a new file location, or overwriting the files that currently exist in the test environment
As mentioned the Primary Filegroup is too large to be restored anywhere

Any ideas on a work around for this would be greatly appreciated

Ste

Well, sad to say you can't do a piecemeal restore and bring a secondary filegroup online without the primary being online first. If there is a hack to get around this I am not aware of it.

If your primary filegroup is at 1TB and you are not using it, why not just do a one time shrink to a more manageable size? This is not uncommon to do in production environments where a large amount of data may have been purged and you want to reclaim the space. As well, if you have no ability to test your backups your are in a far worse situation.


Why would splitting up a query make it faster, and can I/should I fix this? The following query is for a web-based game.  It sets the isTiedForHighScore column to true for all solutions that tied the score for a given "map."

UPDATE solutions
INNER JOIN
(
    SELECT mapID, score
    FROM solutions AS s2
    WHERE isHighScore = 1
) AS maxScore ON maxScore.mapID = solutions.mapID AND solutions.score = maxMoves.score
INNER JOIN maps ON maps.ID = solutions.mapID
SET solutions.isTiedForHighScore = 1
WHERE maps.mapExpireTime >= @mapExpireTime
AND maps.mapExpireTime <= NOW()

(solutions.isHighestScore = 1 will only be true for exactly one solution for each map)

If I set @mapExpireTime to be 1, 2, or 3 months ago, this query runs in around 0.1s.  However, if I set it to 4 months ago or longer, it takes about 50s!

Why would this happen (there's no large difference in the size of the dataset over the months)?  I imagine it has something to do with all the data not being able to fit in memory - is this usual?

More importantly, is it standard practice to sometimes split a query into chunks (I could, say, send multiple queries, each one working on only one month's worth of maps at a time)?  Or is there some way to tell MySQL itself that it's allowed to work on this query in chunks?  I really have no idea how to speed up this query any more.

Here is the output of the EXPLAIN (I have to change the UPDATE to a SELECT to get this to output):

id    select_type     table       type    possible_keys       key           key_len  ref                            rows     Extra
1     PRIMARY         <derived2>  ALL     NULL                NULL          NULL     NULL                           1462
1     PRIMARY         solutions   ref     mapIDuserID,mapID   mapID         8        maxScore.mapID,maxScore.moves  3
1     PRIMARY         maps        ALL     mapExpireTime       NULL          NULL     NULL                           2953     Using where; Using join buffer
2     DERIVED         s2          ref     isHighScore         isHighScore   1                                       1585     Using where; Using temporary; Using filesort

There is no direct way to tell MySQL to work on a single query "in chunks," since SQL is (primarily, at least) a declarative language ("platform, I need you to accomplish this end result") not a procedural one ("platform, I need you to process this data using these steps: for each...").

Memory might be your issue -- which storage engine do these tables use?  ...but really, it seems like there are some more likely possibilities:

According to the EXPLAIN SELECT you posted, no index is being used to join the maps table (key is NULL), even though the mapExpireTime index is available.   When the optimizer determines that the index doesn't have sufficient selectivity, it won't be used -- and the more records in the maps table your @mapExpireTime would not eliminate, the less likely the optimizer will be to select that index, and instead opt to a full table scan, which may explain why performance falls off.  On shorter date ranges, does the index appear in key for the maps table in EXPLAIN?  If so, that's the short answer to "why does it slow down?"

It's possible that ANALYZE TABLE maps; might build some better index stats and improve the query performance.   Conventional wisdom is that the constant "shiftiness" of InnoDB index stats makes this assertion untrue, but I've seen too many times where the stats on an InnoDB table are in a state that biases the optimizer towards a phenomenally bad query plan and ANALYZE TABLE cleans it right up.  (I think the reason the conventional wisdom on this is so widespread is the fact that SHOW TABLE STATUS actually triggers a random index stats dive, so the act of looking makes what you intended to look at different than what it was before you looked, but I digress).

Another possibility would be that you could add an index (ID,mapExpireTime) on the maps table, which seems at first glance quite redundant, but might be used as a covering index... which would be far better than the full table scan and join buffer that's getting used now.


MySql cluster data node replication What I'd like to do is to replicate data nodes within a cluster not only for backup/failover purposes but also for scale-out reasons. Is this possible? If not: why? If yes: how do I do it?
Actually, it would be nice if someone could briefly explain how the data node replication technically works  for my better understanding :)

MySQL Cluster automatically replicates data nodes, based on the number of replicas and the number of data nodes you specify.  Your tables are automatically sharded across these nodes, and updates are replicated between them for HA.

Worth watching the short demo to see more:
http://www.oracle.com/pls/ebn/swf_viewer.load?p_shows_id=11464419

For more detail - take a look at the 'Cluster overview' section of the docs:
http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-basics.html


When upgrading to mongodb 3.0, should I upgrade authentication schema after upgrading the storage engine to WiredTiger? I know that, I need to upgrade the storage engine after upgrading the binaries.
But I am not very sure whether I should upgrade the authentication after upgrading the storage engine. Or it does not matter?

As part of the MongoDB 3.0 upgrade it is strongly recommended that you upgrade to the SCRAM-SHA-1 authentication scheme. New users will have their credentials created in this format by default; mixed authentication schemas will likely lead to confusion.

You will need to upgrade your client driver & admin tools (if you're using a GUI) in order to use the WiredTiger storage engine. Drivers/tools that have been updated for MongoDB 3.0 will also support the new authentication method.

As per the note on the SCRAM-SHA-1 documentation (referring to a future major release which would like be 3.2.x):

  The next major version of MongoDB is likely to remove all support for MONGODB-CR.


Postgresql pg_basebackup from slave didnt save WAL's Is it possible to shoot a backup with the pg_basebackup utility from the slave server (where of streaming replication with the help of this utility pg_basebackup)?

I have a hot_standby server (streaming replication with pg_basebackup), and periodically run pg_basebackup from slave, but when I open my backup (base.tar.gz) I can't find any WAL files.

Do I have to set up archive_command on master (or slave, idk)?

You might want to try to set up max_wal_senders on your replica, and use --xlog-method=stream instead of --xlog-method=fetch, which would let the basebackup grab the individual WAL records as they are shipped, rather than trying to grab the WAL segments at the end of the basebackup. If you have to use --xlog-method=fetch, then you should set wal_keep_segments high enough to let you take a reasonable basebackup. checkpoint_segments x2 should be reasonable as a starting point.

As a reference, the documentation for the 9.3 version of pg_basebackup is here.

I highly recommend that you test out the backup by restoring it on another server, and running a few tests against it to make sure it comes up in a consistent state, and has a reasonable snapshot of the data that you wanted backed up.

One additional note, if you set up an archive_command under 9.3, and a restore_command in your recovery.conf, your streaming replica will be able to recover itself and continue if network conditions cause streaming replication to lag. An example and discussion of this problem and a solution is talked about here: http://evol-monkey.blogspot.com/2014/09/offsite-replication-problems-and-how-to.html

I hope this helps. =)


Does a MySQL dump that contains triggers double data on import? I have a quick and simple question to clarify. I have medium sized database (400 Gigs) running in MariaDB 10.2 with lots of tables and lots of triggers as well. I am planning to take  a mysqldump and import it to another server. So when we take mysqldump, it will be packed with triggers by default as well.

So if I import that dump with triggers to another server, do those triggers fire during the import and create duplicate data?

I know we can take dump without triggers, but I am asking if I import dump with triggers does this create data duplication?

If you take time to run mysqldump, even with all default arguments, you will see that triggers in the dump file are created after all insert statements that populate your tables. So no, there will be no duplication, your data after restoring the dump will be the same as in the source database.


Default phpMyAdmin Login Credentials I downloaded and installed phpMyAdmin 4.8.5 on my windows. Upon opening it in the browser, it immediately asks for a Username and Password. I tried to submit root for username while leaving the password black. I even try password as password. Unluckily, both didn't work by default.

So I checked the web and I saw this from stackoverlow. Unfortunately, all of the given answers did not work for me and hello no, the thread is already closed.

Here's what I did

Open phpmyadmin directory
You will see config.sample.inc php file
Clone it, but remove the sample from the title
After cloning, open the file config.inc and look for $cfg['Servers'][$i]['AllowNoPassword'] = false;
Set it to true

By doing this, we are allowing the phpMyAdmin to submit an empty password.

Now, try root as a username and leave the password blank.

Hope this will work for you as well.


Select record based on multi-row criteria So here's the deal. I have a table with the following sample data.

Edit: This table has a many-to-one relationship with the "PEOPLE" table.

Table_A

ID   |CODE   |TYPE   | BAL
-----|-------|-------|------
1    |AR     |FO     |0.00
-----|-------|-------|------
1    |RT     |CM     |335.00
-----|-------|-------|------
2    |AR     |CU     |0.00
-----|-------|-------|------
3    |RT     |CM     |167.00
-----|-------|-------|------
4    |AR     |CU     |500.00
-----|-------|-------|------
4    |RT     |CM     |0.00
-----|-------|-------|------
5    |RT     |CM     |0.00
-----|-------|-------|------
6    |AR     |FO     |200.00
-----|-------|-------|------
7    |AR     |CU     |0.00
-----|-------|-------|------
7    |RT     |CM     |0.00

I need to select all records in which if they only have one row then CODE IN ('AR', 'RT') AND BAL <= 0, but if they have two rows then I only need the ones that both BAL columns are <= 0.

Example:

The query should return:

Edit: Later I will add the DISTINCT to the query, but for now I'm just curious on how to get the records.

ID   |CODE   |TYPE   | BAL
-----|-------|-------|------
2    |AR     |CU     |0.00
-----|-------|-------|------
5    |RT     |CM     |0.00
-----|-------|-------|------
7    |AR     |CU     |0.00
-----|-------|-------|------
7    |RT     |CM     |0.00

So pretty much I need to evaluate whether or not the BAL <= 0 and if they have two rows than both rows should have BAL <= 0.

I've tried different WHERE clauses with no luck. Let me know if you all need more information and I can add it here.

SELECT *
FROM [Table_A]
WHERE [ID] IN
(
    SELECT [ID]
    FROM [Table_A]
    GROUP BY [ID]
    HAVING (
            COUNT(*)=1 AND
            MAX(CASE WHEN CODE IN ('AR', 'RT')
                AND BAL <= 0 THEN 1 ELSE 0 END)=1
            )
            OR
            (
            COUNT(*)>1 AND MAX(BAL)<=0
            )
)


Set of values of a field of a related entity must be unique: How do I express this constraint in SQL? Given the following three tables:

Type( id int primary key,
      key varchar )

GroupOfInstances( id int primary key,
                  name varchar )

InstanceOfType( id int primary key,
                type int foreign key references(Type.id),
                group int foreign key references(GroupOfInstances.id) )

As seen from GroupOfInstances, I want the set of key values to be unique. I can't make key unique on Type.

To put it another way, I want all keys assigned to a GroupOfInstances through InstanceOfType to be unique for each GroupOfInstances.

How do I do that? I'm using MariaDB 10.1. I can't upgrade to >=10.2 where check constraints are enforced for stupid large enterprise reasons.

Edit: To better illustrate the question I will add examples.

This should be allowed:

INSERT INTO Type (id, key) VALUES (1, "key"), (2, "key"), (3, "otherkey")
INSERT INTO GroupOfInstances (id, name) (1, "group")
INSERT INTO InstanceOfType (id, type, group) VALUES (1, 1, 1), (2, 3, 1)

Because for GroupOfInstances with id 1, they keys are unique: "key" from Type 1 and "otherkey" from Type 3.

This should not be allowed:

INSERT INTO Type (id, key) VALUES (1, "key"), (2, "key"), (3, "otherkey")
INSERT INTO GroupOfInstances (id, name) (1, "group")
INSERT INTO InstanceOfType (id, type, group) VALUES (1, 1, 1), (2, 2, 1)

Because now, for GroupOfInstances 1, the keys are not unique: "key" from Type 1, and "key" again from Type 2. This INSERT should be rejected by the database.

LAST UPDATE:

  I want to allow duplicate key in the table Type, but I want unique key for each GroupOfInstances – Patrick 21 mins ago

The answer do not match the question. Sorry...

Imagine that you have created the constraint which you need.

Imagine that you have a group with keys id=1 and id=2, and you want to create another group with keys id=1, id=2 and id=3.

Formally it is correct - in the final state the groups will have different keysets. But in dynamic...

You try to execute

INSERT INTO groups_to_keys (group, key) VALUES (2,1), (2,2), (2,3)

You hope that all records will be inserted... but the constraint is checked for each separate inserted record! So 1st value pair will be inserted sucessfully, whereas inserting 2nd pair will be rejected - at this moment the group 2 will fully duplicate the keyset for group 1!

But if you'll try to execute

INSERT INTO groups_to_keys (group, key) VALUES (2,1), (2,3), (2,2)

then all records will be inserted successfully.

The only simple solution I see is de-normalization. I.e. the keys are stored not in separate joining table but as an attribute of group entity in some arrayed form (for example, JSON). Such method allows to create UNIQUE constraint over this field (common UNIQUE index).

Of course this method do not allow to use the FK integrity check.

One more solution - separate table which stores unique keysets, and group is referenced to it. Looks like previous, but this allows to create predefined keysets, including those which are not assigned to any group yet. This table may be service one and available to users for reading only. Or even the user may have privileges to read and to insert, but not alter or delete.


Which database technologies to consider for a data search application? I'm designing an application that only does read queries to some database tables (it's a kind of search application); the queried data is updated at night by the system (so speed is less critical, while however very important here too), while application users only do fulltext searches within the stored data.
The retrieval performance is then a key point of the application.

Can you suggest links, Google terms or arguments and other material that I can read to properly design those parts of the database that will be involved in the users searches?
I already know the basics about indexes, and checking the queries with EXPLAIN, I'm reading up about table partitioning to see if there's anything that can help me.

The application will run on MySQL 5.6
So far its builtin FULLTEXT search is enough for our requirements, I'm looking more at hints on how to properly structure the database so that I don't add unnecessary slow downs (by leveraging the fact that no user query will update the data).

What other database technologies should I look for?

Fulltext Search is a specialized technology that complements a traditional SQL database. There is a simple full text indexing feature built into MySQL, but you will probably find other alternatives that are more featureful and higher performance.

I did a presentation comparing them here: Full Text Search Throwdown.

The short answer: Sphinx Search.

Re your comment:

It seems your question is not about a specific solution but just asking for categories of performance optimizations.

Indexing. How to Design Indexes, Really is my presentation, which I've given several times. Here's a recording of me presenting a version of this talk: Tools and Techniques for Index Design.
Partitioning. Partitions Performance with MySQL 5.1 and 5.5 by Giuseppe Maxia. This was the one that made partitioning click for me. Partitioning is very powerful when it works, but it works for a small number of cases.
Caching. What's the fastest SQL query? The one you don't have to run at all, because you can fetch a copy of the data from a high-speed cache. This requires more case-by-case application coding to decide on the right data to cache, what format to store it in, and how to refresh it. Lots of work, but can speed up certain data access by orders of magnitude.
Denormalization. Although denormalization creates opportunities for data anomalies, sometimes it's the best choice to optimize for a specific query. Beware though, because denormalization degrades performance for all other queries against the same data.
Sharding. Useful when your data or query traffic grows so large that a single database instance can no longer process enough queries per second. Split your data horizontally, storing a subset on each shard instance. Add code to your application to decide how to send your queries to the appropriate shard. Sharding is used by MySQL Cluster, MySQL Fabric (which is still pretty alpha as of this writing), and Shard-Query.

Finally, the advice I often give about optimizing is that every method listed above optimizes for one query at the expense of others. For example, sharding is great if you read and write individual rows, but sharding makes things worse if you query for ranges of rows that span multiple shards.


UPDATE with JOIN on 100mm records, how to do this better? (in T-SQL) I need to update 100 million records in a single table, in effect, normalizing the table by replacing the varchar value of a column with simply an ID. (I say "replacing" but really I'm writing the ID into another column.)

What I'm trying to achieve is to normalize the dataset. The not yet normalized data has no indexing. My thought was that I would not build indexes on the raw values, waiting, instead to index the foreign keys that will be replacing the varchar values with tinyint values after the update completes.

UPDATE A
SET A.AutoClassID = B.AutoClassID
FROM AutoDataImportStaging.dbo.Automobile as A
JOIN AutoData.dbo.AutoClass as B on (A.AutoClassName = B.AutoClassName)

Background

using MSSQL 2008 R2 on Server 2008 R2
server has 8 GB RAM
server has one RAID10, 7200 RPM SATA (not great, I know, in production this will only read data and not write data; plus recent HD shortage made this necessary for cost)
server has dual quad-core Xeon CPU
the machine is not doing anything else (currently dedicated to dev, only this process)
simple logging turned on (? - but does it still log so that it can rollback?)
note that the query references two different DBs, for what that's worth
"width" of a record in table getting updated is 455 bytes

Resources During Execution

physical RAM is maxed out
disk I/O is maxed out
CPU is hardly doing anything (choke point is I/O)
run time has been 14 hours and counting!

I suspect a few things like I need an index on the raw data, even though I will be dropping the column (AutoClassName) after the normalization updates. I also wonder if I should just loop down the table one record at a time instead of the JOIN, which seemed ridiculous at the time I started this, but now it seems that would have been faster.

How should I change my methodology for my remaining normalization updates (similar to this one) more quickly?

I would take a different approach.

Instead of updating existing tables, just build a new  table that has what you need in it.

This will almost certainly be faster:

SELECT DISTINCT
    AutoClassID,
    <Other fields>
INTO
    AutoDataImportStaging.dbo.Automobile
FROM
    AutoData.dbo.AutoClass

As currently written, there are a lot of logical operations happening:

Read all values of A.AutoClassName
Read all values of B.AutoClassName
Compare A and B values
Of the matching set, read all values of B.AutoClassID
Update existing values of A.AutoClassId to be the B.AutoClassId value through whatever indexes exist


MySQL LOAD DATA INFILE Error Code 1366. Incorrect integer value: ' ' I've been trying to upload a CSV table into my remote server database but I find an error. I use MySQL in a RedHat Linux distrib. My mycsv.csv file looks like this:

aa,ProductDescription,country,potato,L,11/18/2013,N,05

bb,ProductDescription,country,tomato,L,12/31/9999,N,05

cc,ProductDescription,country,curry,M,01/01/2014,Y,05

dd,ProductDescription,country,spicy,V,05/01/2015,N,

As you may see, last line ends with comma "," without having any field (it is empty and should be like this or even better if I can change it to NULL). However when I type the following query:

LOAD DATA INFILE "/home/mycsv.txt"
INTO TABLE inbound.master_data
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

I get the following error in the command line

  Error Code: 1366. Incorrect integer value: '' for column
  'product_life_cycle' at row 4

Does anybody know how to set a null value after the comma in the last line?
(I think this is the problem why I can't upload my table). Thank you very much in advance.

The table definition:

CREATE TABLE master_data
  ( aa varchar(6) NOT NULL,
    bb varchar(50) NOT NULL,
    cc varchar(3) NOT NULL,
    dd varchar(2) NOT NULL,
    ee varchar(1) DEFAULT NULL,
    ff varchar(20) NOT NULL,
    gg varchar(1) NOT NULL,
    hh int(11) DEFAULT NULL,
    PRIMARY KEY (aa),
    UNIQUE KEY aa_UNIQUE (aa)
) ENGINE=InnoDB
  DEFAULT CHARSET=latin1 ;

To set NULL you just need your table to have allow nulls.

If you want to change it to some default value in case of nulls/missing values, you need to specify individual column names as below:

LOAD DATA INFILE "/home/mycsv.txt"
INTO TABLE inbound.master_data
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
    (name,proddescr,country,thing,charA,datecol,charB,@VAL)
    set numberCol=if(@VAL is null, 0, @VAL);

You can change that 0 to any other default value.

This blog post has many other interesting load data scenarios you might want to learn. (go through the comments as well).


WITH cte AS (
SELECT *,
       SUM(keyPerformanceIndicator) OVER (ORDER BY `date`, `time`
                                          ROWS BETWEEN 10 PRECEDING
                                                   AND CURRENT ROW) is_true_within_10
FROM datatable
)
SELECT * FROM cte WHERE is_true_within_10

If BigQuery does not support implicit convertions then use SUM(CASE keyPerformanceIndicator WHEN TRUE THEN 1 ELSE 0 END) and WHERE is_true_within_10 > 0.


Grant Permissions to all databases using server role I know this question has been asked many times but they are all 3+ years old so I'm going to try my luck by asking again.

I need to grant Read permissions to all databases on MSSQL server. I would like to achieve this using server roles.

I've created server role with following permissions

CREATE SERVER ROLE ReadOnlyAccess
GO
GRANT VIEW ANY DEFINITION TO ReadOnlyAccess
Go
GRANT VIEW ANY DATABASE TO ReadOnlyAccess
GO
GRANT VIEW SERVER STATE TO ReadOnlyAccess
GO
GRANT CONNECT SQL TO ReadOnlyAccess;
GO

Assigning user to this role allows me to connect to the server but not expand any of the databases. I would of thought that 'GRANT VIEW ANY DATABASE' would be the one that gives those rights but boy was I wrong...

Is there a way to achieve this without granting db_datareader database role on each DB for the user? New databases are constantly being created so doing this manually would be quite a nightmare.
I'm guessing that creating a job that runs every few hours would do the trick but I would still rather have it done via Server Role.

Any ideas? I'm using SQL 2016 and above.

Besides reading my answer look at this Q&A by Aaron Bertrand

How to create a read-only server role on SQL Server 2012?

This solution will work for SQL Server 2014 and above.

CREATE LOGIN [AccountNeedPermission] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
GO
GRANT CONNECT ANY DATABASE to [AccountNeedPermission];
GO
GRANT SELECT ALL USER SECURABLES to [AccountNeedPermission];
GO
GRANT VIEW ANY DEFINITION to [AccountNeedPermission];
GO

Reason your solution will not work Server roles cannot be granted permission on database-level securables.