Sql Server And Very Hot CPU % of Total Wait Time I have recently started monitoring a production database with transactional replication. The CPU % of Total Wait time = %93.00.

This is horrible. I confirmed all 8 processors are being utilized. This database has been in production for six years, so I am wondering is the 93 percent reporting 93% of all processors or is there something I am missing?

SOS_SCHEDULER_YEILD is not the cause, it's a side effect. It just means that something else is burning the cycles. In your graph, the second is LOGGING; this makes sense considering you are running replication. Based on my prior experience, you are probably experiencing log reader. This is typical; you can reduce your PollingInterval and increase CommitBatchSize

Use ProcessExplorer, sort by highest CPU usage process, must be sqlservr. Right click on sqlsrvr, go to properties, click on threads tab, sort by highest CPU column to see the highest cpu consuming thread. Note the thread id(s) down. Execute the below statement to see what is query is using the most CPU

select r.session_id, st.text, qp.query_plan from sys.dm_os_threads as ot join sys.dm_os_tasks as t on t.worker_address=ot.worker_address join sys.dm_exec_requests as r on t.session_id=r.session_id cross apply sys.dm_exec_sql_text(r.sql_handle) as st cross apply sys.dm_exec_query_plan(r.plan_handle) as qp where os_thread_id=<from process explorer high CPU thread>


Syncing Database for Server Transition I'm transitioning from an old web/sql box combined to a much more robust solution on a completely different host (800hosting to RackSpace).

Our application has very high uptime requirements. It runs 24x7x365 and impacts literally thousands of sites, and I want to ensure the minimum possible downtime during the transition. (Don't ask how we're pulling this off with a single box right now.)

My plan for the web server is a little bit of DNS work to point a new subdomain to the new server setup, and forward requests from the existing server. That'll take care of flipping everyone to the new web box fast enough. The problem is the database - how do I keep it in sync until I'm ready to flip the switch on the website (or is it worth the effort vs. just accepting the downtime to stop the app, backup, compress, transfer, and restore?).

A few details. We're running Sql2k5 on the old system, 2k8r2 on the new. The database itself is ~30 gigs for the primary, ~60 gigs for the warehouse. I can live with downtime on the warehouse, but really want to minimize the impact on the main database.

Any suggestions for the best way to migrate the database across from the old setup to the new?

S1 = old server, S2 = new server

Full Backup S1
Restore to S2 and with NOrecovery
Block ALL data changing connections to S1 (e.g. change web connection password)
Diff backup on S1
Restore diff to S2 with recovery - S2 will now be ready
Enable connections to S2 ( don't forget to reset any changed passwords back to the originals)


Should a DBA be able to write basic T-SQL? I am a SQL Server database developer (not a DBA) and I find myself frustrated working with my company's new DBA. I'm trying to figure out whether my expectations are unreasonable.

For the sake of clarity, I'll oversimplify: I'm defining a database developer as  one who writes stored procedures, views, functions, etc that are used in applications, such as a web application. I'm defining a DBA as one who creates maintenance plans for backups, installs the SQL Server on new servers, does patches and other mysterious things.

Sometimes, in order to do problem solving on production data I'll need to know something about it, like how many rows are in a table or how many rows there are where a column has a specific value. In the past, the DBA and I would have a phone conversation where I would ask my question, he would fire off the query and I would have my answer.

If you are a DBA (not a developer) and I asked if you could give me a count of the number of rows in MyTable, would you know how to write "select count(*) from MyTable" ?

No bashing, please, of me or my DBA. Just tell me if this query would be in your skillset if you were a mid-level DBA on a large project.

I have been lucky to have had the opportunity to work with many great people in my career and I used to think the same as you do now.

I've met DBA's who were better at writing T-SQL than me, and I've worked with DBA's that don't know a single line of T-SQL. While I personally find the latter frustrating, I try to keep an understanding of the task they are doing in mind when I judge their skill set.

To be more specific, some of the DBA's I have known who could not write T-SQL have been amazing at PowerShell, SMO or were a whiz with SSMS or another GUI and so never needed to write a query.

More importantly, the question comes down to can they do the job they were hired to do? If they can, and they are good at it, then so be it. You aren't asking them to do yours so don't stress about it.

If they need to run some T-SQL, walk them through it if they are interested, guide them to some help documentation. Some people may want to learn and have never had the opportunity to do so. Others are not interested and just want to clock in and out each day. Each to their own.

As long as you get what you need from the team, life is good.


Postgres normalize human name string Is there some function to normalize name string:
 select normalize('R.C. Bray')

 'R. C. Bray'

Or the opposite direction?
The simple replace('. ', '.') or replace('.', '. ') is wrong and may be effect another data.

regexp_replace should be enough:
regexp_replace('R.C. Bray.', '([A-Z])(\.)(\w)', '\1\2 \3', 'g')

This replaces a single upper case letter followed by a . directly followed by a "word character" by inserting a space between the dot and the character. It leaves dots that have a whitespace after them untouched.


Select rows changed in current transaction We have table name and CURRENT_TRANSACTION_ID().

How can we SELECT all rows INSERTed/UPDATEd in current transaction in MS SQL Server?

For example,

BEGIN TRANSACTION;
  EXEC some_big_procedure();
  --How to know what has been done inside some_big_procedure()?
ROLLBACK;

Note: original purpose is to test functions by comparing changes made by them with expected changes.

PostgreSQL has such mechanism (see this question):

BEGIN TRANSACTION;
  SELECT some_big_procedure();

  SELECT 'Changed row', t.*
  FROM my_table t
  WHERE t.xmin::text = (txid_current() % (2^32)::bigint)::text;
ROLLBACK;

Here txid_current() is analogue of CURRENT_TRANSACTION_ID(), t.xmin is built-in system column contains id of transaction which handles this row.

Bad solution is to write service function which will go through all the tables and add t.xmin's analogue. This column must be handled by triggers which are created dynamically by that service function too.

Any better idea for MS SQL?

You could return the rows from the proc via the OUTPUT clause.

insert into YourTable(Column1, Column2, Column3)
output inserted.Column1, inserted.Column2, inserted.Column3
--select... or values()... or what ever insert method

You would use the same methodology for DELETE and UPDATE. You can also store the OUTPUT into a table for further processing if need be.

Here is a DB Fiddle showing the below code

create table myTable (i int)
go

create proc myInsert
as
begin
   insert into myTable (i)
   output inserted.i
   values
   (1),(2),(3)
end
go

exec myInsert
go

create proc myDelete
as
begin

   create table #myTemp (i int)

   delete from myTable
   output deleted.* into #myTemp
   where i >= 2

   select * from #myTemp
end
go

exec myDelete
go


Are there any reasons to avoid using sp_rename for renaming objects? In the documentation it is clearly said:

  Changing any part of an object name can break scripts and stored
  procedures. We recommend you do not use this statement to rename
  stored procedures, triggers, user-defined functions, or views;
  instead, drop the object and re-create it with the new name.

What about renaming:

primary keys
unique keys
foreign keys
default constraints
indexes

I am going to rename all of the above objects in order to match our internal naming conventions but I want to write an automatic script for doing this (as there are thousands of constraints).

The only issue I can think off is that if index is used with a hint in statement it will be broken.

Could you tell any other reasons not to use the sp_rename for such purpose?

Update

sp_rename upates the name in sys.objects but it does not update the definition or objects referencing it.

Object definition

  Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name. (MSDN)

When you run this query:

Create Procedure Test
as
begin
    set nocount on
end
Go
Exec sp_rename 'Test', 'Tests'
GO
Select * From sys.sql_modules
sp_helptext 'Tests'

The definition column in sql_modules still contains Test:

object_id   definition ...
599673184   Create Procedure Test as begin set nocount on Select id From T1 end

Rename a Table (or an object)

When an object is rename, any object using it and referencing the old name will fail.

Here are a View and a Table beining renamed:

Create Table T1(id int)
Go
Create View V1 AS Select id From dbo.T1
Go
Exec sp_rename 'T1', 'T2'

Again if you look at the definition, both the procedure and the view reference T1 (which does not exist anymore):

object_id   definition
759673754   Create Procedure Test as begin set nocount on Select id From T1 end
791673868   Create View V1 AS Select id From dbo.T1

And both will fail with this error when you Select from it or Execute it:

Invalid object name 'dbo.T1'.
Could not use view or function 'V1' because of binding errors.

You get the same error if you refresh the view:

sp_refreshview 'v1'

All objects with references to the old name will break.

Dependencies

Before renaming an object (table, procedure, function, ...), you can look at its dependencies using sys.sql_expression_dependencies (MSDN) (deprecated: sys.sql_dependencies MSDN, sys.sysdepends (MSDN)


How to pass the input parameters to dynamic Pivot stored procedure This is my query:

DELIMITER $$

DROP PROCEDURE IF EXISTS `leavemanagement`.`SP_ViewAttendance` $$
CREATE PROCEDURE `leavemanagement`.`SP_ViewAttendance` (

    IN in_branch_id varchar(10)

)
BEGIN
       SET SESSION group_concat_max_len = 1000000000;

      SET @sql = NULL ;
  SELECT
    GROUP_CONCAT(
      DISTINCT CONCAT(
        'max(CASE WHEN attendance.date = ''',
        DATE_FORMAT(DATE, '%Y-%m-%d'),
        ''' THEN coalesce(p.present, '''') END) AS `',
        DATE_FORMAT(DATE, '%Y-%m-%d'),
        '`'
      )
    ) INTO @sql
  FROM
    calendar ,attendance
  WHERE DATE >= '2015-02-02'
    AND DATE <= '2015-02-31' ;

  SET @sql = CONCAT(
    ' SELECT  ',in_branch_id, @sql,'
        from
            (
               select c.date,  a.user_id ,a.user_name,a.attendance_id,a.attendance_date
                 from calendar c
                 cross join attendance a
            ) attendance
            left join attendance  p
              on attendance.user_id= p.user_id
              and attendance.date = p.attendance_date

           where attendance.date>= date
              and attendance.date <= date
              and attendance.branch_id = in_branch_id (---------HERE IM PASSING IN PARAM)

          group by attendance.user_id'
  ) ;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

But it is showing error:

  FUNCTION leavemanagement.1max does not exist

I don't know how to pass parameters in Pivot Query.

I'm passing input parameter in_branchId into pivot Mysql Query

DROP PROCEDURE IF EXISTS `leavemanagement`.`SP_ViewAttendance` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_ViewAttendance`(

              IN in_attendance_start_date DATETIME,
        IN in_attendance_end_date DATETIME,
        In in_branchId varchar(100)
    )
    BEGIN
          SET SESSION group_concat_max_len = 1000000000;

          SET @sql = NULL ;
      SELECT
        GROUP_CONCAT(
          DISTINCT CONCAT(
            'max(CASE WHEN attendance.date = ''',
            DATE_FORMAT(DATE, '%Y-%m-%d'),
            ''' THEN coalesce(p.present, '''') END) AS `',
            DATE_FORMAT(DATE, '%Y-%m-%d'),
            '`'
          )
        ) INTO @sql
      FROM
        calendar ,attendance
      WHERE DATE >= in_attendance_start_date
        AND DATE <= in_attendance_end_date
          AND attendance.branch_id = in_branchId;

      SET @sql = CONCAT(
        ' SELECT  ', @sql,'
            from
                (
                   select c.date,  a.user_id ,a.user_name,a.attendance_id,a.attendance_date
                     from calendar c
                     cross join attendance a
                ) attendance
                left join attendance  p
                  on attendance.user_id= p.user_id
                  and attendance.date = p.attendance_date

               where

                      attendance.date>= date
                  and attendance.date <= date
                  AND p.branch_id = ',in_branchId,' GROUP BY attendance.user_id') ;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


MySQL Group Replication multi-primary - add new member I am configuring MySQL Group Replication in multi-primary mode to replicate all databases between two members and allowing writes on either.

I have my two members IP addresses configured in the whitelist and seed list:

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y."
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061"

My understanding is that if in a month or two I want add a third member, I will need to update both member's my.cnf files to add the third member to the whitelist and seed list.
I will then need to restart the mysql services in order for the change to take effect.
What is the best practice here? I see two options:

Option 1: restart the members one at a time such that the group never becomes empty.

Restart mysql on member1
Re-join the group with START GROUP_REPLICATION;
Restart mysql on member2
Re-join the group with START GROUP_REPLICATION;
Start mysql on member3 with START GROUP_REPLICATION;

Option2: stop all members and rebootstrap

Stop mysql on both existing members
Bootstrap the group on member1
Join the group on member2 and member3

Option 1 should result in no downtime whereas Option 2 would have downtime. Are there any other options I'm missing or is Option 1 best practice?

If you want to add a new member to current online Group Replication, You don't need to stop or restart any of nodes.

For doing this just config new member(s3) like current members(s1,s2) but add new IP at the end of these two variables

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y,z.z.z.z"
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061,z.z.z.z:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_start_on_boot=off

After that Start new member and execute these

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';

Then Install group_replication and start group_replication

After the new member joined the group open my.cnf of s1,s2 server and update loose-group_replication_ip_whitelist and loose-group_replication_group_seeds to this:

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y,z.z.z.z"
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061,z.z.z.z:33061"

But you Don't need to restart these nodes(s1,2) just update my.cnf


How can I create a dblink from Postgresql 8.3 to SQL Server (2000 and 2008)? I have a Postgresql database that need to read data from two SQL Servers (one SQL Server 2000 and one SQL Server 2008).

I want to setup two dblinks to the SQL Servers.

After googling, foruming, reading documentation, I struggle to find something production ready.

What tool can I use ?

First off, you should be aware that PostgreSQL 8.3 will soon be going EOL.  You should probably use a more current version.

In fact, if you did use a more current version (9.1 or later), you could use Foreign Data Wrappers (FDW).  Someone has even posted source code for a TDS FDW, which should work for older SQL Server.

If you're somehow stuck with PostgreSQL 8.3, user DBI-Link as Craig Suggests.


PostgreSQL Give idetifier for transaction? Is there a way to give a identifier for Postgresql transaction.
Ex :

 begin transaction a;
 ...

If not, will this be supported in future.

I'm asking this if transaction has identifiers then true asynchronous database call will be possible with frameworks like Netty or Vert.x

No, you can't assign a transaction an arbitrary user-defined identifier.

Transactions already have transaction IDs, but only read/write transactions get true, persistent ones.

  I'm asking this if transaction has identifiers then true asynchronous database call will be possible with frameworks like Netty or Vert.x

Having a transaction identifier would do you no good there anyway.


Best cloud platform for PostgreSQL Currently, in our project, we use dedicated servers for our PostgreSQL database.

In theory, we can run anything on some cloud platforms. However, PostgreSQL configuration is strictly related to hardware configuration. What we are looking for is a cloud solution with native PostgreSQL support.

Here is the list of desired features:

automatic database configuration adjustment depending on selected resources
replication setup out of the box

So what are the options and the best pick for such a service?

Heroku has the features you require:

https://postgres.heroku.com/


Unknown database file format .DAT I am trying to find out the database name of this file.

I opened it via hex editor:

http://gyazo.com/5b579bd2ef9620aa833bf0743a480230

Any help is appreciated.

Client sent me this file. It is very old of his DOS program but not sure which database it is.

Any help is appreciated.

When I open it using Notepad I see this:

 €D                                                                      D                         147306154      340000          ‡      P„                                    Ã                     124126290      340001        O‰      †                                    Ä                     124126323      340002       ÀDŠ      ‡                                    Å                     124126289      340003        BŠ      ‡                                    Æ                     124126202      340004
     @Š      ‡                                    Ç                     124126253      340005
     €{Š      `‡                                    È                     124126244      340006       $‹      h‡                                    É                     124126245      340007

I was thinking of a flat file database. There was a Swedish producer that was creating them, but I cannot remember their name right now.

Something like https://filedb.codeplex.com/

(I meant this as a comment, not as an answer, but I do not have enough rights to comment these days.)


Limit of psql_history I can't find (using ctrl-r within psql and less ~/.psql_history) a particular query that I executed a couple months back. I also recently upgraded from 9.3 to 9.5 so I'm not sure if there is a limit on psql_history or if it was removed on the upgrade.

Is there a limit on the size of psql_history and, if so, how could I go about increasing it?

Thanks in advance!!

There's a psql variable that sets the number of commands to save in the history:

postgres=# \set HISTSIZE 100
postgres=# \echo :HISTSIZE
100
postgres=#

From the documentation:

  HISTSIZE - The number of commands to store in the command history. The
  default value is 500

You can set it in your ~/.psqlrc.


How to Sync a MySQL DB with a Oracle DB? I have access from 5 views of an Oracle DB located outside of my organization. I need to sync my db that is a mySQL DB with the oracle DB (Oracle views will be the master, my db will be the slave). I was reading about replication on MySQL DB but nothing about replication from a Oracle DB. There's anyway to I keep the tables equals? PS: I don't have access to the original tables, so I can't create a trigger there :(

Oracle GoldenGate might not be useful for you if Oracle source database is outside of your organization with no possibility for you to set up extract process.

The best for you is to have a look at this old post "Migrate from Oracle to MySQL" that despite its title points out good ways to do what you're looking for.

I found especially useful the below points from David Mann answer to the above mentioned topic:

There are 3rd party utilities available. I have used a few of these
but best for you to check them out yourself for your intended purpose.
A few 3rd party products are listed here: OraFaq . Unfortunately a lot
of them run on Windows which would slow down the data unload process
unless your DB server was on windows and you could run the load
utility directly on the server.
If you don't have any complex datatypes like LOBs then you can roll
your own with SQLPLUS. If you did a table at a time then you can
easily parallelize it. Topic has been visited on this site probably
more than once, here is an example: Linky


Oracle SQL Complications With Multiple Triggers Hey all so I am creating a DB that is used going to be used for managing utilities of a system. My DB has 3 tables: Users, Utilites, and Files (leaving Files table out because question shouldn't affect it).

TABLES

CREATE TABLE USERS (
User_id Number(38,0) NOT NULL PRIMARY KEY,
User_name char(18) NULL ,
User_pass varchar(18) NULL ,
Storage_Size varchar(18) NULL ,
Memory_Usage Number(38,0) NULL
);

CREATE TABLE UTILITIES (
Utility_id Number(38,0) NOT NULL PRIMARY KEY,
Utility_Name varchar(18) NULL ,
Cost Number(38,0) NULL ,
Running char(4) NULL ,
User_id Number(38,0) NULL ,
);

TRIGGERS

//Trigger to SUM the cost of all UTILITIES:

CREATE OR REPLACE TRIGGER memory_after_insert
AFTER INSERT OR UPDATE OR DELETE
    ON UTILITIES
BEGIN
   UPDATE USERS
   SET MEMORY_USAGE = (SELECT SUM(COST) FROM UTILITIES WHERE USERS.USER_ID = UTILITIES.USER_ID);
END;

//Trigger to SUM the cost of all FILES

CREATE OR REPLACE TRIGGER storage_after_insert
AFTER INSERT OR UPDATE OR DELETE
    ON FILES
BEGIN
  UPDATE USERS
  SET STORAGE_USAGE = (SELECT SUM(FILE_SIZE) FROM FILES WHERE USERS.USER_ID = FILES.USER_ID);
END;

Now I would like to create a Trigger that on INSERT into USERS (User creation) will INSERT into UTILITIES all of the systems Utilities (I have X default utilities I would like every user to have)

Example Trigger I have come up with:

CREATE OR REPLACE TRIGGER users_after_insert
  AFTER INSERT ON USERS
  FOR EACH ROW
BEGIN
  INSERT INTO UTILITIES (UTILITY_NAME, RUNNING, USER_ID, UTILITY_ID, COST)
  VALUES
  ('Javaw.exe', 'YES', :new.USER_ID, seq_utility_id.nextval, round(dbms_random.value(25000, 100000)));
  INSERT INTO UTILITIES (UTILITY_NAME, RUNNING, USER_ID, UTILITY_ID, COST)
  VALUES
  ('Firefox.exe', 'YES', :new.USER_ID, seq_utility_id.nextval, round(dbms_random.value(60000, 200000)));
END;

When I have this trigger in my DB it causes my other triggers to mess up. I get errors: ORA-04091: table STUDENT052.USERS is mutating, trigger/function may not see it
ORA-06512: at "STUDENT052.MEMORY_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'STUDENT052.MEMORY_AFTER_INSERT'
ORA-06512: at "STUDENT052.USERS_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'STUDENT052.USERS_AFTER_INSERT'

Before adding this third trigger, the other 2 triggers were working perfectly. I assume the problem lies within this trigger. If anyone has any knowledge on how to fix this it would be greatly appreciated! Thanks!

I am generally opposed to triggers.  They have their place but surprising behaviour, when you do one simple thing and the change ripples out across the world, will give you headaches later.

Instead of having storage_size and memory_usage on the users table, how about creating a view that displays the data you are looking for?  Then you don't need triggers at all.


How can I find out if a SQL Function can be inlined? In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will actually be slower.

That's interesting though because I didn't even know to_char() wasn't IMMUTABLE. Moreover, I didn't know that IMMUTABLE would ever make anything slower. My question is now, how do I find out if a function marked as IMMUTABLE is being inlined, or not?

Since it changes the actual execution plan, you can tell from the output of EXPLAIN (ANALYZE, VERBOSE) (among others): Example:

EXPLAIN (ANALYZE, VERBOSE) SELECT my_func(col) FROM tbl;

I simplified and adapted the fiddle from my answer you are referring to:

db<>fiddle here

The first function is inlined, hence you see the inlined expression in the output:

Output: (to_char((d)::timestamp with time zone, 'MMDD'::text))::integer

The second function is not inlined, hence you see the function call in the output:

Output: f_mmdd_tc_i(d)

To be precise: this shows whether the function has been inlined in the tested query. If it has not, that does not necessarily prove it can't be (under different circumstances).
And the test is not bound to just IMMUTABLE functions in any way.


How keep overview about database permissions? As DB-Administrator I need to keep overview about my servers and databases in general - and from a security context about logins, users, db-roles and permissions in particular.

It is possible to write queries that deliver all relevant information to reduce effort required to lookup each and every user / permission manually within SSMS for example to find out the settings or to create kind of a documentation about it (that get's outdated the next day).
Doing this I created a table having the following information torn out of my servers:

Servername | Datebasename | Username | Login | DatabaseRolename | Permission | PermissionState | SchemaName | ObjectName

This includes information about User-Permissions on object-level directly as well as through DB-Roles and also recursive DB-Role-in-DB-Role permissions inherited.

Instead of looking at this data or create a small application that allows more comfortable lookups / searches or filtering I would like to have it presented more graphical.
I looked at some sampe js libraries that allow visualization. Something that may
be appropriate is this samples:

http://vowl.visualdataweb.org/webvowl/

http://orgo.stolarsky.com/

My question is if anyone knows a way for visualization of this data? Maybe there is a solution for this everyone is using I did not yet stumble about.

I do not believe that this is truly possible, at least not in any truly accurate and meaningful way. The problem is, even taking into account the level of complexity that you have already identified (emphasis added):

  information about User-Permissions on object-level directly as well as through DB-Roles and also recursive DB-Role-in-DB-Role permissions inherited.

permissons are still quite a bit more complicated and involve several more layers.

The various layers of permissions that I am aware of are (the first 3 you already mentioned):

(A) Base permissions

Direct grants to Database User on the Object
Grants implied by membership in one or more Database Roles that has permissions on the Object
Grants implied by membership in one or more Database Roles that is in turn a member of one or more Database Roles, across any number of levels of Database Role membership where at least one Database Role has permissions on the Object
Grants implied by Schema-level permissions
Grants implied by Database-level permissions
Grants implied by membership in one or more Fixed-Server Roles by Server Login associated with the Database User
A DENY anywhere in the chain should supersede any GRANTs

Then you need to take into account:

(B) Windows Groups-based Logins

You can verify if a Login is a member of a particular Windows Group using the IS_MEMBER built-in function, but there is no way to determine what Windows Groups exist to be a member of (well, at least not without using SQLCLR ;-).
When using Logins based on Windows Groups, permissions are additive across all Group-based Logins and directly mapped Logins, but can be negated by a DENY.
Windows Logins can be associated with multiple Windows Groups, each Group having it's own Login, but no Login mapped to the Windows Login
Windows Logins can be associated with one or more Windows Groups and also having a Login mapped to the Windows Login

AND THEN you need to take into account the effects of:

(C) Permissions granted at run-time

A Login/User having IMPERSONATE permission on another Login/User having more permissions than the original Login/User
Any Stored Procedure, Function (excluding Inline TVFs), or Trigger created with the EXECUTE AS clause
Ownership Chaining
Module Signing (i.e. granting permissions to code -- Assembly, Stored Procedure, Function (excluding Inline TVFs), or Trigger -- using an Asymmetric Key or Certificate)

Using the sys.fn_my_permissions function you can loop through the various objects in a particular Database and get a list of effective permissions for the current User. And if you want the permissions of another User, then you have to use EXECUTE AS USER = 'user_name'; and then run that loop, and then execute REVERT;. You probably only need to check for a securable_class of OBJECT. So you could try something along the lines of the following:

SET NOCOUNT ON;

DECLARE @InnerSQL NVARCHAR(MAX) = N'';
SELECT @InnerSQL += N'SELECT SESSION_USER, * FROM fn_my_permissions(N''' + ss.[name]
                    + N'.' + so.[name] + ''', ''OBJECT'');' + NCHAR(0x0D) + NCHAR(0x0A)
FROM   sys.objects so
INNER JOIN sys.schemas ss
        ON ss.[schema_id] = so.[schema_id]
WHERE  so.[is_ms_shipped] = 0
AND    so.[type_desc] IN ('AGGREGATE_FUNCTION', 'CHECK_CONSTRAINT',
            'CLR_SCALAR_FUNCTION', 'CLR_STORED_PROCEDURE', 'CLR_TABLE_VALUED_FUNCTION',
                          'CLR_TRIGGER', 'DEFAULT_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT',
                          'PRIMARY_KEY_CONSTRAINT', 'SEQUENCE_OBJECT',
                          'SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION',
                          'SQL_STORED_PROCEDURE', 'SQL_TABLE_VALUED_FUNCTION',
                          'SQL_TRIGGER', 'SYNONYM', 'UNIQUE_CONSTRAINT', 'USER_TABLE',
                          'VIEW');

-- PRINT @InnerSQL;

DECLARE @OuterSQL NVARCHAR(MAX) = N'----------------------------------'
                                  + NCHAR(0x0D) + NCHAR(0x0A);
SELECT  @OuterSQL += N'EXECUTE AS USER = ''' + dp.[name] + ''';'
                     + NCHAR(0x0D) + NCHAR(0x0A) + NCHAR(0x0D) + NCHAR(0x0A)
                     + @InnerSQL + NCHAR(0x0D) + NCHAR(0x0A)
                     + N'REVERT;'+ NCHAR(0x0D) + NCHAR(0x0A)
                     + N'----------------------------------' + NCHAR(0x0D) + NCHAR(0x0A)
FROM    sys.database_principals dp
WHERE   dp.[type_desc] IN ('SQL_USER', 'WINDOWS_USER')
AND     dp.[principal_id] > 4
AND     LEFT(dp.[name], 5) <> N'##MS_'
AND     LEFT(dp.[name], 3) <> N'MS_';

--PRINT @OuterSQL;

IF (OBJECT_ID(N'tempdb..#Permissions') IS NULL)
BEGIN
  -- DROP TABLE #Permissions;
  CREATE TABLE #Permissions
  (
    UserName sysname,
    EntityName sysname,
    SubentityName sysname,
    PermissionName NVARCHAR(100)
  );
END;

INSERT INTO #Permissions (UserName, EntityName, SubentityName, PermissionName)
  EXEC (@OuterSQL);

SELECT * FROM #Permissions;

HOWEVER, you can only get info from section A above, and not even all of that given that you cannot get permissions for Windows Groups-based Users as you cannot use EXECUTE AS on those.

AND, you cannot take into account anything from section B without actually logging in as each individual Login.

AND, you cannot determine any net-effect from anything from section C without cross-referencing everything previously determined and figuring out some means of determining "effective permissions" on Users that cannot be impersonated (e.g. Certificate-based, Asymmetric Key-based, Windows Groups-based, etc), hence they were not discovered by the query shown above.

So, um: No :-(.


2 SSRS Instances, one in native mode and one in sharepoint integrated mode with PowerPivot and PowerView on same server Wanted to know if I can have two SSRS instances on the same server, one in native mode and one in SharePoint integrated mode.

I have one SSRS instance in native mode on a SQL server default instance (mssqlserver). I added the second SSRS PowerPivot instance to get PowerPivot/PowerView in SharePoint, but realized I have to have an SSRS instance in integrated mode as well to get this feature to work. When trying to add this SSRS instance in integrated mode to the PowerPivot SQL  instance, it only gives me the option for native mode and integrated mode is grayed out.

I am running SQL 2012 SP2 enterprise on the same server as SharePoint 2013 enterprise. I wanted to have both because our ERP utilizes the SSRS native mode in their reports and you can set security through the ERP and have the users view the reports within the ERP system. If this cannot be done, we will have to take all the reports and dashboards out of the ERP and manage them separately with the windows security in SharePoint.

So after messing with this off and on for a while I figured it out. It is possible. All this was done on one server with SharePoint 2013 enterprise and SQL Server 2012 Enterprise with SP2.

I have a default SQL server instance with all features selected, specifically the shared features "Reporting Services - Sharepoint" and "Reporting Services Add-in for Sharepoint Products". Then you have to run through the installer again and add a new instance. Then select "SQL Server PowerPivot for Sharepoint" (relational database check box at this point was not needed). After you do this you have to activate this feature in SharePoint. Here is a blog on how to do the PowerPivot setup.

Make sure this instance is at the same service pack level once you install it. After this I followed this walk-through, starting at step 2, to enable SSRS in SharePoint.

That is pretty much it. I now have a native SSRS site and SharePoint integrated SSRS site as well as PowerPivot/PowerView all on the same server. All databases are on the default instance. I only have SSAS installed for the PowerPivot instance and that is it.


Msrel_commands table has 20M rows I am desperately looking for some logic or script to check commands in msrepl_commands where number of commands which have already been moved to subscriber but not deleted yet.

I am running the procedure manually:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

but it is not deleting any rows even though I checked msrepl_commands and found there are 20M rows that all came in between 2016-08-30 00:01:00 AM and 2016-08-31 09:00:00 AM,  but if I run distribution cleanup then it should delete top 10K in a loop till the row count reaches 2000.

I am a bit confused how to check those delivered commands (msdistribution_status gives me dilevered and undilevered commands but that is cumulative, and I need current status).

I've encountered this issue more times than I can count. For some reason, maybe it's an execution plan difference, if you run it manually and also narrow it down it will complete faster than the job. Figure out the max date in there and then do the cleanup manually:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 78, @max_distretention = 80
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 76, @max_distretention = 78
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 74, @max_distretention = 76
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 72, @max_distretention = 74

I know this sounds ridiculous, but it is a solution that many of us have had to resort to (I had to google for a solution a few years ago and saw many people needing to do this. One such article). This is one of the reasons why I was so excited to move away from replication when Availability Groups came out.

Make sure you add some monitoring for this condition too that way it doesn't get too far behind when you have to resort to the manual running of sp_MSdistribution_cleanup.


Distinct 3 columns and 4th column with sum how can I select in a Oracle DB all columns but some of them DISTINCT.
I have columns A,B,C that I want distinct and column D that I want the sum of the rows.

A   B   C   D
1   2   3   10
1   2   3   20
1   2   4   1000

And I want the result

A   B   C   D
1   2   3   30
1   2   4   1000

Thanks!

It's easy-peasy:

select a,b,c,sum(d)
from yourtable
group by a,b,c;

Probably too simple for this site, to be honest.


Practicing AlwaysOn on SQL Server 2014 - Windows 10 Home TL;DR:
What possible solution(s) are there to practice AlwaysOn feature on SQL server 2014 installed on Windows 10 Home edition?

I am learning Database Administration on my own and have installed Trial version of SQL Server 2014 on my home laptop for 'playing around'. Going into Configuration Manager I found that I need to have Windows Server 2008 or later OS to work with AlwaysOn. Is there any way to bypass this requirement (with any patch or service pack) or what other solution can anyone kindly recommend.

Thanks in advance

SQL Server 2016 will allow for Availability Groups without a domain, but sorry, I don't think your operating system is going to cut it.

To support your scenario, you could put an eval of a proper server OS in a virtual machine or dual boot scenario, but to really test AGs, you'd need multiple copies of Windows in order to understand failover, availability modes, etc. And if you're trying to beef up your skills for a potential job, this edge case scenario is probably not the type of thing a company serious about HA/DR would have any interest in, because they're unlikely to be avoiding domains...


MySQL HDD activity with empty process list What does this mean and how can I understand what is mysql doing? I use InnoDB, my database is huge. This happens when I disconnect my app.

Thanx.

The InnoDB Storage Engine does lots of writing to disk behind-the-scenes. Please remember the subheading ASPECT #4 : Disk Issues from My Answer to Your Previous Question:

  ASPECT #4 : Disk Issues

  What is also noteworthy is this regard is the system tablespace
  ibdata1. Every time you access an InnoDB tables, the data dictionary
  is always consulted. Thus, having spread out tables onto other disk
  does not negate accessing ibdata1 back in datadir. Accessing two disks
  would always be in order.

  InnoDB has many moving parts in ibdata1:

  Data Dictionary
  Double Write Buffer (Safety Net to Prevent Data Corruption Helps Bypass OS for Caching)
  Insert Buffer (Streamlines Changes to Secondary Indexes)
  Rollback Segments
  Undo Logs
  Click Here to see a Pictorial Representation of ibdata1

  Expect the Highest I/O around ibdata1. In light of this, whatever disk tuning you do, give weight to the location of ibdata1.

From what source does InnoDB do this background writing to ibdata1?

The InnoDB Buffer Pool contains Dirty Pages. Those dirty pages must be systematically flushed to disk. Dirty pages come about when INSERTs, UPDATES, and DELETE are made. Those changes are not posted directly to tables. They are queued up in the InnoDB Buffer Pool's Dirty Pages.
There are also writes being posted in the Double Write Buffer within ibdatato bypass OS caching.
Secondary Index changes are queued up in the InnoDB Buffer Pool and  migrated to the Insert Buffer within ibdata1
Since up to 1023 simultaneous Transactions are possible for InnoDB, any data written in the Undo Logs and Rollback Segments are discarded in an orderly fashion.
When there is heavy writing to InnoDB, the Log Buffer catches the initial changes and are scheduled based on how Transaction Flushing is Done (set by innodb_flush_log_at_trx_commit,)

Setting innodb_flush_log_at_trx_commit=0 flushes the log buffer out to the log file once per second and the flush to disk operation is performed on the log file, but no writes are done at a transaction commit. A 1-second loss of data is possible on a server crash or a mysqld crash.
Setting innodb_flush_log_at_trx_commit=1 (Default) flushes the log buffer for each commit and flush the logs to disk
Setting `innodb_flush_log_at_trx_commit=2 flushes the log buffer out to the file at each commit, but the flush to disk operation is not performed on it. A 1-second loss of data is possible on a server crash.

NOTE : Even if no new InnoDB data have been written in a while, reads from the moving parts of ibdata1 must be done to determine availability of incoming data changes. Running a SELECT query against an InnoDB table requires checking the .frm file of the table and cross checking the data dictionary inside ibdata1. I noted this storage engine behavior in my past post : MySQL tables/databases into different disks


How to get the data from same table by different conditions in SQL? I have below data in my ColorAssign table;

| PKID | Name    | Type | HasAssigned |
| 11   | Red     | 1    | True        |
| 12   | Blue    | 2    | False       |
| 13   | Yellow  | 3    | False       |
| 14   | Red1    | 1    | False       |
| 15   | Yellow1 | 3    | True        |
| 16   | Blue1   | 2    | False       |

Note in this above table I have color types in type field and assign status in HasAssigned field also with different primary keys with different names (can be same name - but type is the identifier to find the actual color).

My goal is to get the data as the condition:-

Find the records with the color type with Hasassigned = true or if no records for the type has true. Then type with Hasassigned = false of color type. So my resultset would be;

| 11   | Red     | 1    | True        |
| 12   | Blue    | 2    | False       |
| 15   | Yellow1 | 3    | True        |
| 16   | Blue1   | 2    | False       |

What are all the ways we can do this, with a focus on optimization?

If I understand your question correctly, you want all rows with HasAssigned='True'

AND

all rows where HasAssigned='False' where there does not exist a row for that same type that is also HasAssigned='True'

If that is what you want, I think this query will give you that answer:

DECLARE @ColorAssignTable TABLE (
    PKID INT
    ,NAME VARCHAR(30)
    ,Type INT
    ,HasAssigned VARCHAR(10)
    )
insert into @ColorAssignTable(PKID,Name,Type,HasAssigned) values
(11,'Red',1,'True'),
(12,'Blue',2,'False'),
(13,'Yellow',3,'False'),
(14,'Red1',1,'False'),
(15,'Yellow1',3,'True'),
(16,'Blue1',2,'False')

SELECT *
FROM @ColorAssignTable
WHERE HasAssigned = 'True'

UNION ALL

SELECT *
FROM @ColorAssignTable a
WHERE HasAssigned = 'False'
    AND NOT EXISTS (
        SELECT *
        FROM @ColorAssignTable
        WHERE type = a.type
            AND HasAssigned = 'True'
        )
Order by PKID


Mysql server requires to explicitly state the database in the query I am running mysql-client (Ver 14.14 Distrib 5.7.19) and mysql-server versions 5.7.19 with user root from localhost on Ubuntu server 16.04.2 LTS.

I have noticed that mysql doesn't return the data I'm asking unless I explicitly specify the database in the query. I have replicated the behavior below over and over again.

Can someone please explain why this is happening ?

use db1;
select database();
    db1

select count(*) from users;
    145

select * from users;
    Empty set, 28416 warnings
select * from users;
    ERROR 2006 (HY000): MySQL server has gone away

The result is changing every time you run the query above.
One empty set, one Error2006, one empty set, one Error2006,...

show warnings;
    ERROR 2013 (HY000): Lost connection to MySQL server during query
show warnings;
    Warning 1287 'COM_FIELD_LIST' is deprecated and will be removed in a future release. Please use SHOW COLUMNS FROM statement instead

There's 64 rows of this warning. I get Error2013 only the first time that I run "show warnings". There is no pause or delay.
All subsequent runs return the same 64 rows.

select * from db1.users;

This returns the full table (145 rows) with no problems whatsoever.

It sounds like 5.7.19 jumped the gun.

----- 2016-02-05 5.7.11 General Availability -- Functionality Added or Changed -- -----

  The mysql_kill() ( /doc/refman/5.7/en/mysql-kill.html ), mysql_list_fields() ( /doc/refman/5.7/en/mysql-list-fields.html ), mysql_list_processes() ( /doc/refman/5.7/en/mysql-list-processes.html ), and mysql_refresh() ( /doc/refman/5.7/en/mysql-refresh.html ) C API functions are deprecated and will be removed in a future version of MySQL. The same is true of the corresponding COM_PROCESS_KILL, COM_FIELD_LIST, COM_PROCESS_INFO, and COM_REFRESH client/server protocol commands. Instead, use mysql_query() ( /doc/refman/5.7/en/mysql-query.html ) to execute a KILL ( /doc/refman/5.7/en/kill.html ), SHOW COLUMNS ( /doc/refman/5.7/en/show-columns.html ), SHOW PROCESSLIST ( /doc/refman/5.7/en/show-processlist.html ), or FLUSH ( /doc/refman/5.7/en/flush.html ) statement, respectively.

Recommend you post a bug with bugs.mysql.com .


PostgreSQL 9.1, how to restore a Hot Standby Slave w/o any Wal Files? I have a PostgreSQL 9.1 hot standby slave that had all of its wal files removed. How can I restart the standby server and have the slave resync from the master? Thanks

If the WAL files don't exist anymore anywhere, you need to rebuild the standby from scratch (new base backup).  If you still have the WAL files on the master or in an archive somewhere, just change the restore_command in recovery.conf to fetch them from wherever they are.


Why query with SQL_NO_CACHE runs slower on the first run? Query is made to be as fast as possible, using only SQL_NO_CACHE 1 in select and limit 1. The results are pretty curious; on the very first run it takes 0.125 - 0.203 sec. and all next runs take usually less than 0.040 sec. Is it some kind of warm-up, or is it related to building query process?

It is not an issue right now, but I want to know why it is so, and avoid it if possible.

Engine - InnoDB
MySQL version - tested as on 5.6 as 5.7

As another answer highlighted, Query Cache is not the only cache. Its use is not even advisable in most cases - in fact, it was removed in MySQL 8.0. The reason is that it has scalability problems (it's governed by a global lock) and it invalidates data far too frequently to be useful in a normal workload.

But InnoDB buffer pool contains indexes and data accessed frequently. After your first query, some indexes and data are cached, so next time they will be read from memory. Probably some data/index pages are accessed only once per query, in which case you should be able to see a slighter difference between second and third execution (first time these pages are not cached).

How to avoid the difference between query execution times? Well, there is no way to make the query faster the very first time it runs, as it needs to read from disk. But then, if your buffer pool is big enough, your query will always be fast. Keep in mind that a big buffer pool is very important for MySQL performance. The general recommendation is to keep it 75-80% of total memory. But in reality, things are more complex:

Other caches and buffers require space. In particular, if you have many users, or if your users run many JOINs or ORDER BYs, per-session buffers should be taken into account.
It is useless to have the buffer pool bigger than your database. And it is generally a waste to have it bigger than hot data (data you access often).

Another thing is, sometimes every server is restarted. When it happens, if the buffer pool is simply emptied, your queries will be slower after restart - until hot data are cached again. But you can avoid this by setting:

innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

In this way, your buffer pool will be (partially) written to disk on shutdown and reloaded at startup, so your queries should be fast even after a restart.


What order does Oracle's EXIST condition compare the sub-query results? I know EXIST works better than IN for larger sub-queries because it checks each sub-query record individually and stops once a comparison is found.

Let's say I can order the sub-query in a way to make it more likely for the match to be at the top. Would EXIST actually stick to the order in which the sub-query is returned?

I'm aware this approach is a bit of a double-edged sword, with specifying an order for a large sub-query having a negative impact on performance. I suppose I'm mostly just curious.

I would advise, don't try to out-think the optimizer like this. It doesn't always execute your query how you think it would be done based on how you wrote it. It may be able to take advantage of indexes or alternate join paths. And as mathguy said above, the difference between EXISTS and IN is much better than it used to be.

I would actually expect if you add an ORDER BY clause to your query, it might make it worse, because the entire result set might have to be retrieved, ordered, then checked against the EXISTS clause. But really, there's no way to know without an execution plan.

Just write your query so it retrieves the data you need, test it, then optimize it only if you need to. To quote Donald Knuth, "Premature optimization is the root of all evil."


Aggregated JSONB result in update query Introduction

I created a toy example to extract the problem into a smaller part.
Here is the schema definition with two tables users and events, users can have multiple events:

create table users (
    id int, name text, history jsonb
);
insert into users(id, name) values (1, 'Mike'), (2, 'Jake'), (3, 'Toots');

create table events (
    id serial, user_id int, external_id int, type text, timestamp timestamp
);

# Generate test data
insert into events (external_id, user_id, type, timestamp)
    select x % 5 + 1, x % 3 + 1, 'random', NOW() - '1 day'::interval * (random()::int * 100) from generate_series(1, 3000000) as x;

With the actual data, the users table would be lower volume table less than ~1M records and events table quite high ~25M records.

Problem

Update users table history column which would be an aggregated result of the events table as jsonb. The events should be grouped together by external_id, user_id based on that the object with min(timestamp) and max(timestamp) would be great.

History example:
history: [{start: '2018-12-12', end: '2018-12-20', external_id: 1}, {start: '2018-11-12', end: '2018-11-20', external_id: 2}]

My example, the grouping part seems to be not correct as an only single object is returned in the subquery. Also by performance wise, this is not the best solution.

 update users
    set history = e.history
    from (
        select
            user_id,
            json_build_array(
                json_build_object(
                  'start', MIN(timestamp),
                  'end', MAX(timestamp),
                  'external_id', external_id,
                  'is_fetched', true
                )::jsonb) history
        from events
        group by external_id, user_id
    ) e
    where users.id = e.user_id


I have multiple ibdata files : What should I do with them? I have 3 different ibdata files on disk. What is the difference between them? Can I remove some of them? The goal is to migrate the files from C: to E:...

I'm on MySQL 5.0.x...

Directory of C:\MySQL Datafiles

11/19/2012  12:05 PM       287,309,824 ibdata1
               1 File(s)    287,309,824 bytes

Directory of C:\Program Files\MySQL\Enterprise\Monitor\mysql\data

11/19/2012  12:05 PM     6,561,988,608 ibdata1
               1 File(s)  6,561,988,608 bytes

Directory of C:\Program Files\MySQL\MySQL Server 5.0\data

02/08/2008  09:00 AM        10,485,760 ibdata1
               1 File(s)     10,485,760 bytes

Disclaimer : Never Used MySQL Enterprise Monitor
In reality, you do not need the first one as it has not been written in years. However, the default datadir for a MSI-installed mysql instance is "C:\Program Files\MySQL\MySQL Server 5.0\data". You should leave it alone in case you want to create another local instance of MySQL or use it as a template datadir for another local instance.
As for the other two ibdata1 files, one may be needed by MySQL Enterprise Monitor and the other for your actual data.
To be sure, you need to check if you are running Multiple Instances of MySQL.
There are two methods
METHOD #1 Check via the Services List

Start
Control Panel
Administrative Tools
Services
Scroll through the List of Services
Look for any user-defined MySQL service names

If you see multiple MySQL services defined in Services, you have multiple MySQL instances running.
METHOD #2 : Check via the Task Manager

Right Click the Taskbar
Click Start Task Manager
Click Processes Tab
Scroll through the Process List
Count how many mysqld.exe entries you see

If you see multiple mysqld.exe entries in the Task Manager, you have multiple MySQL instances running.
NEXT STEP
Judging from the date and time of each ibdata1 file and their respective folders, my guess is that you have MySQL Enterprise running and it has its own MySQL instance. You need to check the MySQL Enterprise Documentation on moving Enterprise Monitoring Information to anther disk location.
As for C:\MySQL Datafiles, you need to locate the my.ini being used. You should be able to have MySQL Enterprise Monitor tell you. In the event MySQL Enterprise Monitor does not any mechanisms for editing the datadir or migrating the MySQL data folder, you could also get it from the Services entry:

Start
Control Panel
Administrative Tools
Services
Scroll through the List of Services
Right click on the MySQL Service
Choose Properties
Scroll left-to-right through the command for the how the Service is started. You should see the location of the my.ini

Once you find the my.ini

open it with Notepad or Wordpad
edit the datadir setting to new folder location on Drive E:\
save the file

Migrate the Data from C: to E: with something like this:
mkdir "E:\MySQL Datafiles"
xcopy "C:\MySQL Datafiles" "E:\MySQL Datafiles" /s

Then startup MySQL. If the MySQL Enterprise Monitor cannot be used to start MySQL then do net start mysql.
While you could move the whole folder C:\Program Files\MySQL\Enterprise\Monitor\mysql\data over to Drive E:\ with
mkdir "E:\MySQL_EMD"
xcopy "C:\Program Files\MySQL\Enterprise\Monitor\mysql\data" "E:\MySQLEMD" /s

you need to make sure MySQL Enterprise Monitor is full shut down. You also need to check for configuration settings for MySQL Enterprise Monitor to make sure it knows where its mysql data is located. Please don't move anything until you find this out. Consult either the Documentation or the Enterprise Forums on this aspect.
Sorry, I am not a user of MySQL Enterprise Monitor, but I hope I gave you a starting point.


How to cast an integer to a boolean in a MySQL SELECT clause? I'm new here so be kind to me. I have the following scenario:

I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value in this view representing if it is one kind of event or another (a simple boolean), which I tried to achieve with:

`gu`.`StoppingUnitEventME` = `ese`.`MonitoringElement` AS `IsStopingEvent`

The result is represented as int, and so is read by Entity Framework. The problem is that I really need a boolean return value, which I tried to achieve with:

CAST((`gu`.`StoppingUnitEventME` = `ese`.`MonitoringElement`) as boolean) AS `IsStopingEvent`

This resulted in an error, one that is not displayed to me in MySQL Workbench (I only receive that annoying "You have an error in ...").

Can you guys please help me out?

Tried to solve it in my application, but I really preffer this solved in the database, since it will be used by other software later.

Try using the IF function:

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, TRUE, FALSE)
FROM ...

or

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 1, 0)
FROM ...

Even without the IF function, running

mysql> select ('rolando' = 'rolando') str_compare;
+-------------+
| str_compare |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql>

yield 0 or 1 using the mysql client

The problem is this: CAST() and CONVERT() can only accept and return the following types:

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

Since BOOLEAN is not in this list, it can never be returned by CAST or CONVERT

You could use the IF function to generate strings

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 'TRUE', 'FALSE')
FROM ...


Is there any way to trim data automatically Lets I have a table named myTable in mysql with Three field

1. id int autoincrement
2. product_id varchar(20) not null
3. product_name varchar(200) not null

Now I want to keep data in product_id and product_name without white-space.

Yes, one way to do this will be to create a insert trigger on the table...can trim the values before inserting into the table ..


Installing SQL Server on not the default path I am trying to install SQL Server 2012 on a machine which has

764 Mb Free Space on C:
832520 Mb Free Space on D:

So during the installation process I changed the default location from C:\Program Files\Microsoft SQL Server\ to D:\Microsoft SQL Server\

But during the Disk Space Requirements check I cannot proceed saying I dont have enough space left on C:

Is it possible to not use C: at all or is it not configurable?

From BOL:

During installation of SQL Server 2012, Windows Installer creates temporary files on the system drive. Before you run Setup to install or upgrade SQL Server, verify that you have at least 6.0 GB of available disk space on the system drive for these files. This requirement applies even if you install SQL Server components to a non-default drive.

http://msdn.microsoft.com/en-us/library/ms143506(v=sql.110).aspx

You still need 6GB free on the system drive, regardless of where you are installing it.  Although, depending on your options for install, that may be smaller, as in your case.


Query without having to specify table's schema I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename.

In the table properties it lists 'erpadmin' as the db schema. When I write a query I now have to include 'erpadmin.' in front of all the table names which is confusing.

Current result:

select *
from erpadmin.tablename

Desired result:

select *
from  tablename

If you want to go back to using the dbo schema like you were in SQL Server 2000, you can move the table back into the dbo schema:

ALTER SCHEMA dbo TRANSFER erpadmin.tablename;

An alternative if you like having the non-dbo schema is to set your user's default schema to erpadmin then if you do not specify a schema, it will use that as default. (Members of the sysadmin fixed server role ignore the DEFAULT_SCHEMA and use dbo by default.)

ALTER USER erpadmin WITH DEFAULT_SCHEMA = erpadmin;

The two part name you have (schema.table) is a good habit to get into though, so you can be explicit with which table you are referring to.  Some features require you to use a two-part name, Indexed Views are one example.


Assign dbo role to login without creating a new user in db I would like to create assign dbo rights on an existing database to an existing login without creating a new user in the database. I would like to have the login use dbo user, like a member of sa server role does.

All I have found involves creating a new db user.

Thanks!

If at all possible, this should be fixed from the deployment side of things and not from a questionable modification of the target database side of things (referring to the conversation in the comments on the Question related to using the deprecated sp_addalias).

Depending on what the exact problem is, there are various configuration options of DacPac deployment that can be used to get around such conflicts. These options can either be specified on the command line if running SqlPackage, or can be placed into a DAC Publish Profile (i.e. an XML config file).

The options of interest here are listed in the Publish Parameters, Properties, and SQLCMD Variables section of the SqlPackage page.

To start with, do you have any of the following set to True:

DropObjectsNotInSource (default: False)
DropRoleMembersNotInSource (default: False)
IgnoreLoginSids (default: True)

If you have the DropObjectsNotInSource property set to True, then try adding:

DoNotDropObjectType=Users
ExcludeObjectType=Users
IgnoreRoleMembership=True
IgnoreUserSettingsObjects=True ??

If needing to specify the ExcludeObjectType property, that would look as follows, depending on where it is specified:

On the command-line: /p:ExcludeObjectType=Users
In a DAC Publish Profile:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0"
         xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <PropertyGroup>
        <ExcludeObjectType>Users</ExcludeObjectType>
    </PropertyGroup>
</Project>


Alerts from Maintenance Plans I would like to setup alerts from within the maintenance plans.
When you setup a notification from the Agent Job it sends out the Job name, duration, status, message.

If I add a Notify Operator Task it want's a subject and body. Will it still include the above information?

If not how can I have this information included?

Since the Maintenance Plan uses EXECUTE msdb.dbo.sp_notify_operator @name=N'some operator',@subject=N'some subject',@body=N'some body' there is no way to automatically include details about the Job Name, duration, status, etc.  You could add a "on failure" precedence constraint that only executes the Notify Operator Task when a step in the maintenance plan fails.  You could hard-code the Notify Operator Task with a specific message indicating the piece of the maintenance plan that failed.  This will get pretty odious very fast, and still doesn't allow automating the error message you receive via email.  This looks like:

Having said that, instead of using the built-in Maintenance Plans feature, you may want to look into Ola Hallengren's scripts that are built for this purpose, and offer a far wider range of options.  You could then use standard Agent notification for the maintenance plan the way you do for other jobs.

http://ola.hallengren.com/


Find all occurrence of value from column b in column a So basically I have 2 tables which I merge by

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNumber

I need to display all occurrence of value from t1.columnB in t2.columnA

The perfect situation for this dataset is when there is no duplicated value in column A and value of column B matches value from column A, but i know there is a lot of errors.

This is example of situation i want to display. The value of ColumnB which is in red box appears in ColumnA multiple times.

UPDATE: Sample data for first query

t2.columnA

5900920132278
5900920132278
5900920132278
5900920132278
5900920132278
5900920132278
038548088099
038548088099
038548088099
038548088099
038548088099
5900442707411
5909426907526
5900855046879
4014855166091
5909426909667

t1.columnB

5900920061554
5900920132278
2010000001165
5900920061479
5900920132261
5900920132278
038548088099
5706915071948
38548088099
038548088099
38548088099
2100000038879
2100000035502
2100000030163
4014855026562
2100000031627

In this dataset need to display all records from t2.columnA contains 5900920132278 and 038548088099

UPDATE 2. Data from separate queries:

COLUMN A:

5900920132278
5900920132278
038548088099
038548088099
038548088099

GIDNumber:

21801
37273
97191
163053
165153

ColumnB:

5900920132278
5900920132278
5900920132278
038548088099
038548088099
038548088099
038548088099

TwrNumber:

21801
37273
91424
21801
37273
91424

Solution :

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNumber
WHERE EXISTS
( SELECT 1 FROM Table2 as t3 WHERE t1.colmnB = t3.columnA )
AND t2.columnA <> t1.columnB

Test Data:

CREATE TABLE dbo.Table1( columnA nvarchar(255), columnB nvarchar(255),GIDNumber int, Description nvarchar(255));
CREATE TABLE dbo.Table2( columnA nvarchar(255), columnB nvarchar(255),TwrNum int,Description nvarchar(255));

INSERT INTO dbo.Table1(columnA,ColumnB,GIDNumber,Description)
VALUES(5900920132278,5900021321,1,'F-A1120010185')

,(5900920132278,5900021322,1,'F-A1120010165')
,(5900920132278,5900021323,1,'F-A1120010165')
,(5900920132278,5900021324,1,'F-A1120010165');

INSERT INTO dbo.Table2(columnA,ColumnB,TwrNum,Description)
VALUES(2000061232,5900920132278,1,'F-A1120010185');

Original query

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNum;

Result:

columnA columnB description
2000061232  5900021321  F-A1120010185
2000061232  5900021322  F-A1120010165
2000061232  5900021323  F-A1120010165
2000061232  5900021324  F-A1120010165

Adding COUNT() and GROUP BY

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB
GROUP BY t2.columnB,t2.columnA;

Result:

columnB columnA amount_of_matches
5900920132278   2000061232  4

If you also need to check if GIDNumber = TwrNum

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB and t1.GIDNumber=t2.TwrNum
GROUP BY t2.columnB,t2.columnA;

As a result, if column B matches column A but the t1.GIDNumber=t2.TwrNum  does not match, it will show as no match:

columnB columnA amount_of_matches
5900920132278   2000061232  4

Extra tests:

INSERT INTO dbo.Table2(columnA,ColumnB,TwrNum,Description)
VALUES(2000061232,5900920132279,2,'F-A1120010185');

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB
GROUP BY t2.columnB,t2.columnA;

columnB columnA amount_of_matches
5900920132278   2000061232  4
5900920132279   2000061232  0

INSERT INTO dbo.Table1(columnA,ColumnB,GIDNumber,Description)
VALUES(5900920132278,5900021321,1,'F-A1120010185');

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB
GROUP BY t2.columnB,t2.columnA;

columnB columnA amount_of_matches
5900920132278   2000061232  4
5900920132279   2000061232  1

Adding the description column:

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches, t2.Description
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB   and t1.GIDNumber=t2.TwrNum
GROUP BY t2.columnB,t2.columnA, t2.Description;

Result:

columnB columnA amount_of_matches   Description
5900920132278   2000061232  4   F-A1120010185
5900920132279   2000061232  0   F-A1120010185


Cannot restore MySQL database from .frm and .ibd files I have a bigger database with around 1000 tables. Unfortunately after a OS X update everything vanished, but I was able to recover all the file that now consists of only .frm and libd files. Following answers from here restore table from .frm and .ibd file? I did the following, test only on one table

1.Installed MAMP

2.Created a new database that has the same name as the old (sunflower)

3.Using mysqlfrm I was able to get a query that created me the table structure( First at once I am trying to recover only one table):

    CREATE TABLE `sunflower`.`USDT@002dNXT_hour` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `open` text,
  `close` text,
  `low` text,
  `high` text,
  `volume` text,
  `btc_volume` text,
PRIMARY KEY `PRIMARY` (`datetime`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB;

Here I encountered with one problem. Running this query it created me a file called : usdt@0040002dnxt_hour.ibd why my old file is usdt@002dnxt_hour.ibd. (Renaming 1 file will be fine, but renaming 1000 is quite complicated)

Then I run :  ALTER TABLE USDT@002dNXT_hour DISCARD TABLESPACE;
Copied the old usdt@002dnxt_hour.ibd file to my database directory.
Now I tried to run  ALTER TABLE USDT@002dNXT_hour IMPORT TABLESPACE;
which complained that such a table does not exits, so I renamed my file to usdt@0040002dnxt_hour.ibd.
Running now the same script trows me this error : #1808 - Schema mismatch (Table has ROW_TYPE_COMPACT row format, .ibd file has ROW_TYPE_DYNAMIC row format.) So I changed the row type be running : ALTER TABLE USDT@002dNXT_hour ROW_FORMAT=DYNAMIC;.
Now running the import query again throws me this error : Table sunflower.usdt@002dnxt_hour doesn't exist, however the table exits.

Is my approach is good ? If yes, where I make the mistake ?

Overall your method/steps look good.

Step3: Change you CREATE TABLE statement

CREATE TABLE sunflower.#mysql50#USDT@002dNXT_hour

This should create the table the way you expect it to be i.e. no 0040 is added to table name.

Step 6: This shouldn't be required after change to Step3 as mentioned above

Give this a try.


INSERT into three tables with many-to-many from one table With Postgres 9.5 using a trivial example.
Given I have this data:

Table everything:

| fruit  | country |
|--------|---------|
| Banana | USA     |
| Banana | Panama  |

How would I create these tables?:

Table fruit:

| fruit_id | name   |
|----------|--------|
| 1        | Banana |

Table country:

| country_id | name   |
|------------|--------|
| 1          | USA    |
| 2          | Panama |

Table fruit_country:

| fruit_id | country_id |
|----------|------------|
| 1        | 1          |
| 1        | 2          |

As an initial pass I hoped I could do something like this:

WITH fruit_ids AS (
  INSERT INTO fruit(name) (
    SELECT (fruit) FROM everything
  )
  ON CONFLICT DO NOTHING
  RETURNING fruit_id
),
country_ids AS (
  INSERT INTO country(name) (
    SELECT(country) FROM everything
  )
    RETURNING country_id
)
INSERT INTO country_fruit(fruit_id, country_id) (
  SELECT
  fruit_id,
  (SELECT country_id FROM country_ids)
  FROM fruit_ids
);

But no dice:

ERROR:  more than one row returned by a subquery used as an expression

The immediate cause for the error message is your abuse of parentheses. But there is more:

Data-modifying CTEs are the wrong tool for the use case. The UPSERT (ON CONFLICT DO NOTHING) indicates that some of the fruits might already exist in table fruit (or its use would be nonsense). Hence, some of the fruits might not be included in the set returned with RETURNING.

Use simple, separate INSERT commands instead. If you need the whole operation to be atomic (all or nothing), wrap it into a transaction.

Also, name is not a good name for a column. I use fruit for fruits and country for countries instead. Avoids naming conflicts, and happens to simplify the query a bit by allowing the (totally optional) USING clause for the join condition.

Your tables can look like this: (If that was even part of the question?)

CREATE TABLE fruit (
   fruit_id serial PRIMARY KEY
 , fruit    text UNIQUE
);

CREATE TABLE country (
   country_id serial PRIMARY KEY
 , country    text UNIQUE
);

CREATE TABLE fruit_country (
   fruit_id   int REFERENCES fruit
 , country_id int REFERENCES country
 , PRIMARY KEY (fruit_id, country_id)
);

Detailed instructions:

How to implement a many-to-many relationship in PostgreSQL?

To INSERT data from everything:

BEGIN;

INSERT INTO fruit (fruit)
SELECT DISTINCT fruit              -- must be distinct!
FROM   everything
ON     CONFLICT DO NOTHING;

INSERT INTO country (country)
SELECT DISTINCT country            -- must be distinct!
FROM   everything
ON     CONFLICT DO NOTHING;        -- UPSERT here as well

INSERT INTO fruit_country(fruit_id, country_id)
SELECT f.fruit_id, c.country_id
FROM   everything
JOIN   fruit   f USING (fruit)     -- matching row guaranteed now
JOIN   country c USING (country);  -- matching row guaranteed now

COMMIT;


Create a temp table, loop, add data, select from it? "Query has no destination for result data" I am creating a temp table, and am fetching data from multiple tables and inserting data into it. When I try to get the data back from the table I get an error:

[42601] ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.

do
$$
    DECLARE
        sampleproductId   varchar;
        productIds        text[] := array [
            'abc1',
            'abc2'
            ];
        tId              varchar;
        DECLARE result  jsonb;
        DECLARE resultS jsonb[];
    begin
       CREATE TEMP TABLE IF NOT EXISTS product_temp_mapping
        (
            accountid      int,
            productUID      varchar,
            sampleproductId text
        );
        FOREACH sampleproductId IN ARRAY productIds
            LOOP
                tId := (select id
                        from product.product
                        where uid = sampleproductId);
                INSERT into product_temp_mapping (accountid, productUID, sampleproductId)
                select accountid, tId, sampleproductId
                from product.accountproductmap
                where productId = cast(tId as int);
            END LOOP;
        select * from product_temp_mapping;
    end ;
$$;

Is this the right way to do it? This is the first time I am doing something with a temp table.

When I try to get the data back from the table I get an error.

That's because you cannot return anything from a DO statement. You would need a function where you define the return type in the RETURNS clause. (But you still wouldn't need a temporary table.)
Plus, PL/pgSQL does not allow SELECT without a target.
But you don't need any of this for the example. A plain query does it:
SELECT a.accountid::int, p.id::varchar AS product_uid, a.sampleproductId::text
FROM   product.product p
JOIN   product.accountproductmap a ON a.productId = p.id::int
WHERE  p.uid = ANY ('{abc1, abc2}'::text[]);

Some of the casts are probably unnecessary.
You might wrap it into an SQL function (PL/pgSQL not needed for this). See:

SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?
Difference between language sql and language plpgsql in PostgreSQL functions

Aside: use legal, lower-case identifiers! See:

Are PostgreSQL column names case-sensitive?


How do I grant a SELECT permission to an individual user who's part of a large group that is explicitly denied? I have a table SuperSecretData which is currently setup to deny SELECT to everyone in the company by the standard Active Directory account "Domain Users". But a couple of specific individual users do need to be able to SELECT from it.
What's the proper way to achieve this?

DENY overrides GRANT, and an inherited DENY even overrides a direct GRANT.  And a DENY at the schema or database level overrides a GRANT at the object level.

So you won't be able to directly override the DENY.

You can create a view, synonym, or stored procedure owned by the same user as SuperSecretData and grant them access to that.

  is there a better way to GRANT SELECT to almost all entities on my SQL server to "Domain Users" except for a couple of tables that I'd only want some specific users to have SELECT permissions on?

Yes.  Never use the built-in roles, and always use schema-level grants.  Then you can create separate schemas for additional security.  Eg

grant select on schema::dbo to ...

instead of a built-in role

alter role db_datareader add . . .

or a database-level grant

grant select to ...


Migrating timezone timestamps I am trying to migrate this node library https://github.com/LocalSEOGuide/lighthouse-reporter from Postgres to MySQL and getting an error  error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-03-22T20:38:48.347Z' for column 'fetch_time' at row 1

I've isolated the query where the error happens and built a little test:

     await db.query('drop table test');
     await db.query(`CREATE TABLE IF NOT EXISTS
                  test(
                    id SERIAL PRIMARY KEY,
                    fetch_time datetime
                  );
    `);

    const query_text = "INSERT INTO test SET ?"
    const query_params = { "fetch_time":"2020-03-22T20:38:48.347Z" }
    await db.query(query_text, query_params);

It seems that the reporter is returning a timestamp with a Z at the end, but i cant figure out how to get it properly stored in to mysql.  Any suggestions would be very helpful!

I've also tried with fetch_time as a timestamp

You can do only one thing

var mystring = "2020-03-22T20:38:48.347Z";
mystring = mystring.replace(/Z/, '');
mystring = mystring.replace(/T/, ' ');
console.log(mystring);

2020-03-22 20:38:48.347

see jsfiddle

Replace the T and the Z the resulting string is no problem for Mysql


How do i subtract a number of days from a prepared statement parameter in db2? I have a C# application using OLEDB to connect to a DB2 database.  Basically, I want to do this:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE
FROM (
        SELECT
            CASE
                WHEN O.DATE < ? - 21 DAYS THEN 1
                WHEN O.DATE >= ? - 21 DAYS AND O.DATE < ? - 14 DAYS THEN 2
                WHEN O.DATE >= ? - 14 DAYS AND O.DATE < ? - 7 DAYS THEN 3
                WHEN O.DATE >= ? - 7 DAYS THEN 4
            END AS WEEK,
            O.ID, O.SUBTOTAL
        FROM COMPANY.ORDERS AS O INNER JOIN
            COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN
            COMPANY.STORES AS S ON O.STORE_ID = S.ID
        WHERE O.DATE >= ? - 28 DAYS
            AND O.DATE <= ?
            AND S.NUMBER = ?
   ) AS CALC
GROUP BY CALC.WEEK;

However, when I run that, I get this:

An expression with a datetime value or a labeled duration is not valid.. SQLCODE=-182, SQLSTATE=42816, DRIVER=3.63.108

And if i change it to wrap the ? parameter with a TIMESTAMP function thusly:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE
FROM (
        SELECT
            CASE
                WHEN O.DATE < TIMESTAMP(?) - 21 DAYS THEN 1
                WHEN O.DATE >= TIMESTAMP(?) - 21 DAYS AND O.DATE < TIMESTAMP(?) - 14 DAYS THEN 2
                WHEN O.DATE >= TIMESTAMP(?) - 14 DAYS AND O.DATE < TIMESTAMP(?) - 7 DAYS THEN 3
                WHEN O.DATE >= TIMESTAMP(?) - 7 DAYS THEN 4
            END AS WEEK,
            O.ID, O.SUBTOTAL
        FROM COMPANY.ORDERS AS O INNER JOIN
            COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN
            COMPANY.STORES AS S ON O.STORE_ID = S.ID
        WHERE O.DATE >= TIMESTAMP(?) - 28 DAYS
            AND O.DATE <= TIMESTAMP(?)
            AND S.NUMBER = ?
    ) AS CALC
GROUP BY CALC.WEEK

I get this error:

The invocation of routine "TIMESTAMP" is ambiguous. The argument in position "1" does not have a best fit.. SQLCODE=-245, SQLSTATE=428F5, DRIVER=3.63.108

As a bonus question, short of creating a PL/SQL function, is there a way to use the same parameter for all the timestamp locations instead of supplying the same value 8 times?

Just the cross join bit:

SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS ,
    SUM(CALC.SUBTOTAL) AS REVENUE
FROM (
        SELECT
            CASE
                WHEN O.DATE < the_param - 21 DAYS THEN 1
                WHEN O.DATE >= the_param - 21 DAYS AND O.DATE < the_param - 14 DAYS THEN 2
                WHEN O.DATE >= the_param - 14 DAYS AND O.DATE < the_param - 7 DAYS THEN 3
                WHEN O.DATE >= the_param - 7 DAYS THEN 4
            END AS WEEK,
            O.ID, O.SUBTOTAL
        FROM COMPANY.ORDERS AS O INNER JOIN
            COMPANY.LOCATIONS AS L ON O.LOCATION_ID = L.ID INNER JOIN
            COMPANY.STORES AS S ON O.STORE_ID = S.ID CROSS JOIN
            (SELECT ? as the_param FROM SYSIBM.SYSDUMMY1)
        WHERE O.DATE >= the_param - 28 DAYS
            AND O.DATE <= the_param
            AND S.NUMBER = ?
    ) AS CALC
GROUP BY CALC.WEEK

you might need to give the subquery an alias—not sure if that is mandatory on DB2


How to provide a access to the data base if the user is not having the privilleges In MS SQL Server
2008 R2 is installed by a user "A" and he has a data base named "DB1" . He left the company. Now the user "B" is trying to access the data base. He is able to see the data abse and but he is not having the privillege to read the data.

We don know the password of sql authentication. Both users are tin the admin group in that machine where the sql server is installed .

now is there any way by which the user is able to read the data

With SQL Server 2008 onward local Windows Administrators are not added as SQL Server Administrators by default.

If you do not have access to any Administrative account to SQL Server then you need to start SQL Server in single user mode, in this mode local Windows Administrators can login to SQL Server. Once logged in you can create a new Administrative Account.

Refer link for step by step tutorial: SQL Server – How to connect to SQL Server when ‘sa’ account is disabled


About Global Temporary Table and Stored Procedure in Oracle I am confusing with the global temporary table in Oracle:

For example, I want to write a SP MySP which can be used by 5 users at the same time.

CREATE GLOBAL TEMPORARY TABLE temp
     (ID NUMBER, COL1 VARCHAR2(4000), COL2 VARCHAR2(4000));

CREATE OR REPLACE PROCEDURE MySP
(
  id NUMBER
, input1 varchar2(4000)
, input2 varchar2(4000)
)
AUTHID CURRENT_USER IS

sql_stmt VARCHAR2(4000);
BEGIN
   sql_stmt := 'INSERT INTO temp VALUES ('
             + TOCHAR(ID,99) + ','
             + input1 + ','
             + input2
             + ')';
   execute sql_stmt;

END MySP;

My question is:
  When the five user run the SP at same time, will they  not interrupt with each other because they use the same global temp table?

GLOBAL TEMPORARY

  Specify GLOBAL TEMPORARY to indicate that the table is temporary and
  that its definition is visible to all sessions with appropriate
  privileges. The data in a temporary table is visible only to the
  session that inserts the data into the table.

http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402


Prepare MySQL for big UPDATE query on production server Surprisingly I have not found anything relevant on this subject.

What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details for a typical setup:

MySQL 5.6
80/20 read/write workflow ratio
Only InnoDB
Replication Master
Query Cache enabled
Data fits entirely into memory buffer pool with some spare room left
Healthy HDD drives on hardware RAID-1
Millions of rows in the update query
The query only covers single table (1 primary key - INT)

And the questions:

What to look for?
What mysql configuration parameters will be most relevant for the process to go quickly and smoothly?
Is it worth splitting the update into multiple batches?

The Query cache is a hindrance.  (In general, it should be OFF for production systems.)  All entries in the QC for that table will be purged by the Update.

Millions of rows -- all of the table?  A small fraction of a billion row table, but no good index?  What?

Let's dissect the query...

Scan through the entire table.  This takes time for the following reasons:

Read the blocks
Write the "undo" records to disk (in case of ROLLBACK or crash)
Write the blocks back to disk
Toss the undo blocks

The size of "undo" may necessitate a less efficient method, adding to the overall time.

The affected rows will be locked; well you may as well plan on the 'entire' table being blocked from other usage.

Batching...

Avoids blocking as long
Probably takes longer overall
Complexity in the code
Not atomic (if that matters)

Why do you need to do such a big Update?  Perhaps the column you are changing should be in a separate, parallel, table to avoid this much impact?  Or 'computed' by subsequent SELECTs?

Config changes?  Assuming the innodb_buffer_pool_is big enough, nothing useful to do.

For chunking, see advice on chunking -- that talks about deleting, but can be adapted to updating.


Oracle aggregate function performance Can Oracle be smart about aggregate functions, such as MIN(), MAX(), and (AVG)?  My testing shows that it seems to be surprisingly stupid.

I have the following query:

SELECT COUNT(userId), AVG(age),
 STDDEV(age), MIN(age), MAX(age),
 date_range_start, date_range_end
FROM users
WHERE
(date_range_start >= TO_DATE('01-Dec-2010')) AND (date_range_end <= TO_DATE('30-Nov-2011'))
GROUP BY date_range_start, date_range_end;

It takes 27 seconds.
Now I remove the STDDEV, MIN and MAX aggregations the same query takes only 12 seconds.
OK, I can see STDDEV slowing things down as it requires 2 passes.
So I try AVG + MIN and MAX -- I get 21s.

How is this even possible?  How can adding calculation of min and max to the calculation of AVG slow things down by the factor of 2 almost?  Considering that out of the 12 seconds that it takes with AVG only 10 are spent on the full table scan?  So adding min/max calculation changes the group by step from 2 seconds to 10?

The explain plan:

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     12 |00:00:24.61 |     369K|    369K|   |   |      |
|   1 |  HASH GROUP BY     |              |      1 |     12 |     12 |00:00:24.61 |     369K|    369K|   762K|   762K|   11M (0)|
|*  2 |   TABLE ACCESS FULL| USER         |      1 |     29M|     29M|00:00:09.34 |     369K|    369K|   |   |      |
-------------------------------------------------------------------------------------------------------------------------------------

Use Oracle analytics:

SELECT distinct
  COUNT(userId) over (partition by date_range_start, date_range_end)
, AVG(age)      over (partition by date_range_start, date_range_end)
, STDDEV(age)   over (partition by date_range_start, date_range_end)
, MIN(age)      over (partition by date_range_start, date_range_end)
, MAX(age)      over (partition by date_range_start, date_range_end)
, date_range_start, date_range_end
FROM users
WHERE
(date_range_start >= TO_DATE('01-Dec-2010')) AND (date_range_end <= TO_DATE('30-Nov-2011'))
/

It does the same but most of the times it is surprizingly faster.


how to create invalid utf-8 characters in MySQL I'm trying to test some application level ruby methods and how they handle invalid utf-8. I'm trying something like to get invalid utf-8:

mysql> update items set header=UNHEX('4D2AFF') where id=10938;
ERROR 1366 (HY000): Incorrect string value: '\xFF' for column 'header' at row 1

Is there a way to update a varchar(255) to invalid utf-8?

Why not changing the text strings to a binary encoding or non-utf8 encoding, or to use binary strings (varbinary, blobs)? That will allow you to store all invalid values that you want.

Edit: To be more specific: change the type to varchar(255) charset binary, that may cause you all kind of charset problems .

@RLF, funny that you mention Wikipedia, text on the wikipedia is stored in binary format!


Return a default value in group by if selected column is null I am trying to run a group by statement which works fine however, one of the "group" is null.  How do I return "Unknown" for that row?

For example:

    10000
A   9999
B   9990
C   4

etc.

First line I'd like to return as "Unknown" in the query.

You should be able to use COALESCE(your_column, 'Unknown'):

  The COALESCE function returns the first of its arguments that is not null.

This is not specific to GROUP BY, although you'll certainly need to use it both in your SELECT column expression and in the GROUP BY expression (as usual when using GROUP BY).


Does adding non-clustered indexes to Transactional Replication increase latency I would like to add non-clustered indexes to my Publication for Transactional Replication. I am aware that replication works by reading the transaction log for the published database and sends the updates to the subscribers. My question is: does transaction log include updates to indexes and therefore would this double the updates sent to the subscriber? Will it send updates to subscriber when I run Index maintenance ie: Rebuilding/Reorganizing indexes?

If so, then perhaps creating non-clustered indexes as part of post snapshot script application is a better way. If not, then I prefer to keep it as part of Publication.

@Dina I would still prefer that you write an answer. This was too long to put in comments.

From my experience, I agree with your statement no transaction were sent to subscribers but log reader still have to read every entry in the transaction log. Does not matter if that log entry is being replicated or not. If you are rebuilding large indexes with high number of maxdop option your replication latency can increase very quickly. I use to pause in between index rebuilds for the logreader to catch up so I do not exceed latency SLA.

Below comments are copy pasted from 2 articles I mentioned, which are relevant to your question.

Ref:

Frequently Asked Questions for Replication Administrators

Performance Tuning SQL Server Transactional Replication: A Checklist by Kendra Little

How do I rebuild indexes or tables in replicated databases?

There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.

How do I add or change indexes on publication and subscription databases?

Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

Are You replicating non-clustered indexes blindly?

It’s very easy to set up transactional replication and send all the indexes over to the subscriber: you just set “Copy nonclustered indexes” in the articles property to “true”. But you’re only required to replicate the Primary Key and unique indexes. There’s two big problems with replicating all the nonclustered indexes:

It can make re-initialization slower. By default the subscriber will
have objects created, bulk load the articles, create “extra”
nonclustered indexes, then “catch up” on any changes that came in
after the snapshot was pushed. You definitely want to make sure that
all “extra” nonclustered indexes are disabled or don’t exist while
that bulk load is happening. But if a lot may have changed since the
snapshot ran, you may not want the indexes to be created until the
very end, anyway. Handling the nonclustered index creation outside of
replication gives you that flexibility.
It’s very rare for the exact same queries to run on the publisher and
subscriber. You usually want nonclustered indexes that are specific
to the workload on the subscriber, anyway.

Identify the “extra” nonclustered indexes specific to the queries that run on the subscriber. Script them out, check them into your source control, and have a process to deploy them whenever replication needs to be re-initialized.


How to select number of affected rows to variable in function? create or replace function test()
returns void as $$
begin
  update tbl set col1 = true where col2 = false;
  -- now I want to raise exception if update query affected more than 2 rows to rollback the update
end;
$$ language plpgsql;

How I can select number of affected rows to a variable in function?

create or replace function test()
returns void as $$
declare
    v_cnt numeric;
begin
    v_cnt := 0;

        update tbl set col1 = true where col2 = false;
        GET DIAGNOSTICS v_cnt = ROW_COUNT;

        if v_cnt > 1 then
           RAISE EXCEPTION 'more than one row affected --> %', v_cnt;
        end if;
end;
$$ language plpgsql;


PL/SQL recursive lookup with cursor I basically have a need for recursing through data in my Oracle table so I can find related rows and send them out. It is a single table, a single ANSI SQL query seems not possible due to turin completion I need in this logic - perhaps a PL/SQL function.

I have a table that contains DNS data looks like below

RN TIMESTAMP  REQUEST                              TYPE  RESPONSE
1  1431606892 www.microsoft.com                    CNAME toggle.www.ms.akadns.net.
2  1431606892 www.ms.akadns.net                    CNAME www.microsoft.com-c.edgekey.net.globalredir.akadns.net.
3  1431606892 c.edgekey.net.globalredir.akadns.net A     23.6.72.154
4  1431606892 www.google.com                       A     173.194.121.48
5  1431606892 c.edgekey.net.globalredir.akadns.net A     23.6.72.194

I want to write a PL/SQL function that when a person searches for REQUEST "www.microsoft.com" it returns rows 1,2,3,5 to the user.  Basically it follows the CNAME trail (up to 255 times)

CREATE OR REPLACE PACKAGE DNSUTLS AS
TYPE col_table is table of tfinal%ROWTYPE;
FUNCTION CNAME_TRAIL(nsearch VARCHAR2) RETURN col_table pipelined;
END DNSUTLS;
/
CREATE OR REPLACE PACKAGE BODY DNSUTLS AS
DUMMY NUMBER;
FUNCTION CNAME_TRAIL(nsearch VARCHAR2) RETURN
col_table pipelined as
cursor temp_cur is
       SELECT * FROM tfinal where request=lower(search);
BEGIN
   for cur_rec in temp_cur loop
--if cur_rec.type = 'CNAME' THEN
--keep recursing to add additional rows
--endif;
      pipe row(cur_rec)
   end loop;
END;
END;

/

The answer is really use Hierarchical query model from Oracle. http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

So in my case:

select *
from tfinal
start with request = &search
       and type = 'CNAME'
connect by prior response = request;

The solution is simpler than what I envisioned.  Here is the real data and the query itself

select a.request,a.response,level from dnstest a  left join dnstest b on b.request=a.response||'.'  start with a.request='www.microsoft.com' connect by prior a.response=a.request||'.';

The data looks like

1,1431606892,www.microsoft.com,CNAME,toggle.www.ms.akadns.net.
2,1431606892,toggle.www.ms.akadns.net,CNAME,www.ms.akadns.net.
3,1431606892,www.ms.akadns.net,CNAME,www.microsoft.com-c.edgekey.net.globalredir
.akadns.net.
4,1431606892,www.microsoft.com-c.edgekey.net.globalredir.akadns.net,CNAME,c.edge
key.net.globalredir.akadns.net.
5,1431606892,c.edgekey.net.globalredir.akadns.net,A,23.6.72.154
6,1431606892,www.google.com,A,173.194.121.48
7,1431606892,c.edgekey.net.globalredir.akadns.net,A,23.6.72.194


Shrinking a massive database file I have a client with a 2 TB database for a pension company. The database contains a lot of big documents.

They want to archive everything that's not from this year. So far no problem - I'll just do a while loop for batches insert and delete and then backup the log after every batch.

After I perform the archiving can I shrink the database? How long can it take if we're talking about maybe 400GB that was moved?

It sounds like a very heavy process for a server like that. I can do it on the weekend but I'm not sure if it'll stop running in time.

Firstly this needs to pre-mentioned that only shrink files if its actually needed, as you'll cause a lot of index fragmentation which you'll need to clear up afterwards.

With that said, if you're worried about the time it takes to run the entire shrinkfile then you can do it in stages (takes longer overall but technically can be stopped and resumed with a lot more ease), personally I use this to drop it in 2GB stages (also useful on high transactional dbs to reduce contention for the operation to shrink in smaller chunks I've found)

DECLARE @value int = 2048
DECLARE @target int = 1536
SET @value = (@value *1024)-2048

WHILE @value >= (@target*1024)
BEGIN
    DBCC SHRINKFILE(filename ,@value)
    SET @value = @value - 2048
END

Replace

@value with the current size of your file in MB and the

@target with what size you want to shrink down to and

filename with the name of the file you wish to be shrinking


Role to grant a DBA who should not access confidential Database I am a junior DBA and I am in a bit of a pickle. I need to grant elevated privileges to our new DBA but he hasn't signed consent forms yet so I cannot grant him access to a specific database in our server.
How can I grant someone enough access to fulfill a DBA role but not access to one specific database?
As in, I don't want to add the person to sysadmin role which will give him access to this confidential database, but I also want him to be able to work (do his dba job that he was hired for).
Thank you

I suggest that you are careful with securityadmin.
As a security admin, you can create a new login and give this new login CONTROL permissions on the server. Now you can login as this new login and do almost anything. CONTROL on server is very close to sysadmin. Among other things, you can use any database and access all information, unless there are explicit DENY
Checking DENY is one of the big differences between sysadmin and CONTROL server. SQL Server doesn't even check for DENY for a sysadmin. But it does that for CONTROL server.
So, initially I thought that you could grant CONTROL server to this person, create a user in the confidential database, and then DENY CONNECT to this user. However, since this now can create a a login this can create a new login and grant CONTROL server to this new login. So, you would have to DENY ALTER ANY LOGIN to avoid this part.
In short: create a login, grant CONTROL SERVER (or securityadmin, or something else except sysadmin), deny ALTER ANY LOGIN, create a user in your confidential database, and deny connect for this login to the user in the confidential database.
This is just my own reasoning, so I can't say that there aren't any holes in above!