MySQL - Deleting old data; Select with index taking time

(This does not address your question directly; rather it addresses your goal of deleting old records.)

Plan A

You are talking about DELETEing "old" records.  Assuming you are removing "all" the old records, then PARTITIONing is an excellent way to do that.  PARTITION BY RANGE(TO_DAYS) by weeks.  Use DROP PARTITION (instantaneous and non-invasive) to get rid of the oldest week and REORGANIZE PARTITION to add a new partition.  Details.  It will take some downtime to initially partition your 50-million-row table.

Plan B

Alternatively, you can continually walk through the table, using the PRIMARY KEY for keeping track of where you are.  Find the 1000th row hence & delete any 'old' records.  Repeat.  Details.  And it does not need an index on lastseen; removing it could be an efficiency bonus.

Since the table is clustered on the PK(id), walking through the table is very efficient -- very little I/O for each step.  (For unknown reasons, your original solution was more I/O-bound than it should have been.)


Is it wise to drop fixed-length columns to free up space?

Dropping them will reclaim space if you rebuild indexes or run DBCC CLEANTABLE
New rows will not have these columns
Fixed length columns always have space reserved whether NULL or not (note: variable length columns with NULL don't consume space usually)
Don't shrink the database, it will only grow again and has other issues (out of scope here)
40GB is not big

Note: an index rebuild to reclaim space assumes you have a clustered index


Start job from another SQL server instance

You probably don't want to grant direct access to sp_start_job or sp_stop_job, or even grant access to a range of SQL Agent functionality via SQLAgentOperatorRole, to either NT AUTHORITY\NETWORK SERVICE or MyDomain\ServerA$. Doing any of those combinations would, at the very least, allow any process running as NT AUTHORITY\NETWORK SERVICE on ServerA the ability to start and/or stop any job on ServerB.

Using Module Signing, you can set up very granular permissions such that MyDomain\ServerA$ (or any login or role, or any combination of those) can only start and stop that one particular job.

USE [msdb];
GO
CREATE PROCEDURE dbo.StartStopJobX
(
  @Operation VARCHAR(10) = 'start'
)

AS
  IF (@Operation = 'start')
  BEGIN
    EXEC dbo.sp_start_job N'{job_name}';
  END
  ELSE
  BEGIN
    EXEC dbo.sp_stop_job N'{job_name}';
  END;
GO

CREATE CERTIFICATE [SqlAgentPermissions]
 ENCRYPTION BY PASSWORD = 'change_me'
 WITH SUBJECT = 'Allow low-priviledged accounts to start/stop certain jobs',
 EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
  TO dbo.StartStopJobX
  BY CERTIFICATE [SqlAgentPermissions]
  WITH PASSWORD = 'change_me';

CREATE USER [SqlAgentPermissions] FROM CERTIFICATE [SqlAgentPermissions];

ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [SqlAgentPermissions];

Now all you need to do is:

GRANT EXECUTE ON dbo.[StartStopJobX] TO [MyDomain\ServerA$];


Get previous and next row from result set help

Instead of 1424, perhaps you should use t4.t2id. Instead of 1046, if the parent exists in table4 or any of the others, use that link instead of the constant.

You could also use a single IN, and a UNION of both queries.

Finally, if you use a filter condition in WHERE on a LEFT JOINed table, that defeats the purpose of a LEFT JOIN and might result in bugs. Either use JOIN or move the condition to the ON part of that LEFT JOIN. In this case, the condition in the LEFT JOIN was different than the one in WHERE for the same field (t2.id), so it could never work.

SELECT
CONCAT_WS('/', LOWER(t1.name), LOWER(t2.slug), LOWER(t3.slug)) as slug,
t1.id, t2.id, t3.id
FROM table4 t4
left join table1 t1 on t1.id = t4.t1id
left join table2 t2 on t2.id IN (
    SELECT COALESCE((SELECT MAX(id) FROM table2 WHERE parent = 1046 AND id < t4.t2id),
                    (SELECT MAX(id) FROM table2 WHERE parent = 1046))
    UNION
    SELECT COALESCE((SELECT MIN(id) FROM table2 WHERE parent = 1046 AND id > t4.t2id),
                    (SELECT MIN(id) FROM table2 WHERE parent = 1046)))
left join table3 t3 on t3.id = t4.t3id
where t4.t2id = 1424


When you run /usr/local/mysql2/bin/mysql in your last code snippet, that is obviously your MySQL 5.7 mysql command-line client, but the output shows it's connecting to your MariaDB server:

Server version: 5.5.5-10.3.18-MariaDB-log MariaDB Server

If you want it to connect to your MySQL 5.7 server, then you need to specify the non-default port or socket and maybe other credentials like the database user and password (if you have created that), e.g. one of these commands:

/usr/local/mysql2/bin/mysql -P3307
/usr/local/mysql2/bin/mysql -uroot -p -P3307
/usr/local/mysql2/bin/mysql -S/var/lib/mysql2/mysql2.sock

For reference, see also my answer to "how to install a second server of mariadb".


Mongo: how to covert a string to decimal?

Consider a collection with documents:

{ _id: 1, bal: "123.45" }
{ _id: 2, bal: "67890"}
{ _id: 3, bal: 999 }

The following command can be run from the mongo shell to update the two documents with the field bal canverted from string data type to NumberDecimal:

db.collection.find( { bal: { $type: "string" } }
).forEach( doc => { doc.bal = NumberDecimal(doc.bal); db.collection.updateOne( { _id: doc._id }, { $set: { bal: doc.bal } } ); } )

After the update, the documents:

{ "_id" : 1, "bal" : NumberDecimal("123.45") }
{ "_id" : 2, "bal" : NumberDecimal("67890") }
{ "_id" : 3, "bal" : 999 }


Insert Row With Foreign Key Equal to ID - Circular Reference

Solution 1:

Make the event_id nullable.

ALTER TABLE events
  MODIFY COLUMN event_id int unsigned NULL ;

This will allow you to insert rows which reference nothing (NULL):

INSERT INTO events
  (user_id, event_id, title)
VALUES
  (a_valid_user_id, NULL, 'test event 1') ;

And to see events without a "parent" event:

SELECT *
FROM events
WHERE event_id IS NULL ;

Test at SQL-Fiddle-1

(Parenthesis)

Unless you have some braindead ORM that requires all tables to have a column named id, it's better to have names that describe what they represent. I'd rename id to event_id and event_id to parent_event_id:

CREATE TABLE events (
  event_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(10) unsigned NOT NULL,
  parent_event_id int(10) unsigned NULL,
  title varchar(200) NOT NULL,
  PRIMARY KEY (event_id),
  --- ...
) ;


Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL

When I see that expression Copying to temp table on disk, I see a cascading problem.
First of all, what would precede Copying to temp table on disk? It would be Copying to temp table.
Why is this the case?
There are two variables that dictate how big a temp table in RAM can be

tmp_table_size
max_heap_table_size

Imagine processing a query where mysqld has tmp_table_size configured at 32M. If the query being processed needs more that 32M to populate a temp table, look at what mysqld must do to the query and the temp table:

Suspend the query's execution
Open a temp table on disk
Migrate the 32M data already in the temp table in RAM over to the new temp table on disk
Continue the query's execution from where it left off, populating the temp table on disk

That's when the message Copying to temp table on disk appears (during step 3). Based on this and the step given, my guess is that you have tmp_table_size set way too high. The bigger the temp table is that is not enough to finish any phase of the query, the longer step 3 must take in the interim.
SUGGESTION #1
I would set tmp_table_size and max_heap_table_size as low as possible. That would force a temp table to go to disk faster.
SUGGESTION #2
If you have the ability to setup a RAM disk in Windows, try mapping tmpdir to that RAM disk.
I once suggested these things in StackOverflow.


If your Label has fixed pattern, i.e. IP_BLOCK_X.info#NNN, where NNN is an integer.
You can easily do this way

use tempdb
drop table #t;
create table #t (Block varchar(100), Label varchar(100));
go
insert into #t (Block, Label) values ('Block_A','IP_BLOCK_X.info#9'), ('Block_A','IP_BLOCK_X.info#8'), ('Block_A','IP_BLOCK_X.info#11')
go

select * from #t
order by cast(substring(Label, len('IP_BLOCK_X_info#')+1, 100) as int)

The result is

Block   Label
Block_A IP_BLOCK_X.info#8
Block_A IP_BLOCK_X.info#9
Block_A IP_BLOCK_X.info#11

Edit:

After the question is updated, as promised, here is my best guess for the answer.

use tempdb
--drop table #t;
create table #t (Regression_name varchar(100), [TimeStamp] datetime, Block varchar(100), Label varchar(100));
go
insert into #t (Regression_name, [TimeStamp], [Block], Label)
values ('Regression_1', '2016-11-01','Block_A','IP_BLOCK_X.info#8')
, ('Regression_1', '2016-11-02','Block_A','IP_BLOCK_X.info#9')
, ('Regression_1', '2016-11-03','Block_A','IP_BLOCK_X.info#11')
, ('Regression_1', '2016-11-04','Block_A','IP_BLOCK_X.info#21')
, ('Regression_2', '2016-11-04','Block_B','IP_BLOCK_X.info#8')
, ('Regression_2', '2016-11-05','Block_B','IP_BLOCK_X.info#9')
, ('Regression_2', '2016-11-06','Block_B','IP_BLOCK_X.info#11')
, ('Regression_2', '2016-11-07','Block_B','IP_BLOCK_X.info#21')
go

-- find the latest (by [TimeStamp] top 3 Regression_name for each [Block] and order by [Label]'s last numeric number
; with c as (
select distinct Regression_name, [Block], [TimeStamp]
, rnk = ROW_NUMBER() over (partition by [Block], Regression_name order by [TimeStamp] desc)
from #t) --select * from c
select t.*
from #t t
inner join c
on t.Regression_name = c.Regression_name
and t.Block = c.Block
and t.TimeStamp = c.TimeStamp
where c.rnk <=3
order by t.Regression_name, t.Block, t.[TimeStamp],  cast(substring(t.Label, len('IP_BLOCK_X_info#')+1, 100) as int)


We have copied a drive that had a db on it and I need to open the db on a new server

Assuming your database had its MDF and LDF files hosted locally, you should look for these files.
Once you've found your MDF and LDF files you can attach them to the new SQL server using:

USE master;
GO
CREATE DATABASE MyDatabase
    ON (FILENAME = 'C:\MySQLServer\MyDatabase.mdf'),
    (FILENAME = 'C:\MySQLServer\Database.ldf')
    FOR ATTACH;
GO

MSDN article about attaching MDF and LDF files


You really should not hide the data you want to do arithmetic inside any kind of structure.

Use a table with 4 columns:  class, student, subject, score.

Then things like each student's avg score:

SELECT student,
       AVG(score)
    FROM tbl
    GROUP BY student;


Database Design Cars-Models / Car Classifieds

Perhaps something like this:

CREATE TABLE make (
    make_id INT NOT NULL,
    make_name VARCHAR(25),
    PRIMARY KEY(make_id);
);

CREATE TABLE model (
    model_id NOT NULL,
    make_id NOT NULL,
    model_name VARCHAR(25),
    PRIMARY KEY(model_id)
);

CREATE TABLE model_year (
    model_id INT NOT NULL,
    year_num INT NOT NULL,
    PRIMARY KEY (model_id, year_num)
);

CREATE TABLE transmission (
    transmission_id INT NOT NULL,
    transmission_descrip VARCHAR(15),
    PRIMARY KEY (transmission_id)
);

CREATE TABLE drive (
    drive_id INT NOT NULL,
    drive_descrip VARCHAR(10),
    PRIMARY KEY (drive_id)
);

INSERT INTO transmission (transmission_id, transmission_descrip)
VALUES (1, 'Auto'), (2, '4-speed manual'), (3, '5-speed manual');

INSERT INTO drive (drive_id, drive_descrip)
VALUES (1, '2WD Front'), (2, '2WD Rear'), (3, '4WD'), (4, 'AWD');

To allow someone to add a 2005 Toyota Camry, first you need to make sure Toyota is in the make table, Camry is in the model table, and 2005 is available for the Camry:

INSERT INTO make (make_id, make_name)
VALUES (1, 'Toyota');

INSERT INTO model (model_id, model_name)
VALUES (1, 1, 'Camry');

INSERT INTO model_year (model_id, year_num)
VALUES (1, 2005);

We keep certain data on car buyers at my job, and in selecting the car type (either currently owned or looking to buy), we have them select the year, then make, then model and options from drop-downs.  Each drop-down is populated on the fly based on previous answers.  First the year:

SELECT DISTINCT year_num FROM model_year;

Then the make (you can't pick Pontiac for 2013):

SELECT DISTINCT m.make_id
FROM make m
INNER JOIN model mo ON mo.make_id = m.make_id
INNER JOIN model_year my ON my.model_id = mo.model_id
WHERE my.year_num = @some_year_variable;

Then the model drop-down gets populated based on:

SELECT DISTINCT model_id
FROM make m
INNER JOIN model mo ON mo.make_id = m.make_id
INNER JOIN model_year my ON my.model_id = mo.model_id
WHERE my.year_num = @some_year_variable
    AND ma.make_id = @some_make_variable;

You get the idea. As far as constraining other options for a model, there are a few options, based on how strict you want to be, how much time you want to spend managing it, and how much you know about the auto industry.

Since all cars have a drive type and transmission, you might want to make those separate tables, like:

CREATE TABLE model_transmission (
    model_id INT NOT NULL,
    year_num INT NOT NULL, -- include this to restrict tranny based on some years
                           -- certain models had different trannies offered
    transmission_id INT NOT NULL,
    PRIMARY KEY (model_id, transmission_id) -- include year_num in PK if it is in the
                                            -- table
);

CREATE TABLE model_drive (
    model_id INT NOT NULL,
    year_num INT NOT NULL, -- again, if you are up to this level of control
    drive_id INT NOT NULL,
    PRIMARY KEY (model_id, drive_id) --include year_num in PK if it is in the table
);

Or you could use an option table and a model_option table, like this:

CREATE TABLE option (
    option_id INT NOT NULL,
    option_descrip VARCHAR(25),
    PRIMARY KEY (option_id)
);

INSERT INTO option (option_id, option_descrip)
VALUES (1, 'Transmission'), (2, 'Drive'), (3, 'Doors');

CREATE TABLE model_option (
    model_id INT NOT NULL,
    option_id INT NOT NULL,
    option_value VARCHAR(15),
    PRIMARY KEY (model_id, option_id) -- include year_num as above if desired
);

INSERT INTO model_option (model_id, option_id, option_value)
VALUES (1, 1, '1'), (1, 2, '1'), (1, 3, '4'); -- All Camrys now have automatic,
                                              -- front-wheel drive, and 4-door options
                                              -- available

I prefer the first approach as you can see the problem with referential integrity inherent in the second, plus it makes it more awkward to restrict available options.  The table for listed cars might be like this:

CREATE TABLE car (
    listing_id INT NOT NULL,
    year_num INT NOT NULL,
    model_id INT NOT NULL,
    transmission_id INT NOT NULL,
    drive_id INT NOT NULL,
    doors INT NOT NULL, -- might want varchar to include 'hatchback' or other options
    mileage INT,
    customer_id INT NOT NULL,
    PRIMARY KEY (listing_id)
);

If this doesn't address a particular feature you believe you need just add a comment.


Random selection with a "complex" subset structure

The steps to perform are the following:

  Pick 1 task randomly (e.g. Eat)
  Retrieve all combinations that contain that task (e.g. all    combination about Eat)
  Pick 1 mood randomly from the list of combination retrieved in step 2
  Retrieve all combinations from step 2 list that contain step 3 mood
  Pick 1 place randomly from remained combination
  Find the randomly selected combination

WITH
cte1 AS (SELECT *
         FROM task
         ORDER BY RAND() LIMIT 1),
cte2 AS (SELECT mood.*
         FROM link_table
         JOIN mood USING (mood_id)
         JOIN cte1 USING (task_id)
         ORDER BY RAND() LIMIT 1)
SELECT cte1.*, cte2.*, place.*
FROM link_table
JOIN cte1 USING (task_id)
JOIN cte2 USING (mood_id)
JOIN place USING (place_id)
ORDER BY RAND() LIMIT 1

fiddle

PS. It is possible that the query will return no record.

PPS. There is more simple alternative:

WITH cte AS (SELECT *
             FROM link_table
             ORDER BY RAND() LIMIT 1)
SELECT task.*, mood.*, place.*
FROM cte
JOIN task USING (task_id)
JOIN mood USING (mood_id)
JOIN place USING (place_id)

fiddle

This variant will never return zero records if link_table is not empty.

But! the chance for each separate record to be returned differs for 1st and 2nd queries...


Oracle sqldeveloper Database Diff - not seeing indexes/sequences

This may not be an answer but worth to check up. Some indexes may have been set to invisible as an alternative to being dropped. These are still maintained by the database but ignored by the optimizer. You'll be able to see them by querying the dictionary, but the compare tool may ignore them.

SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY
FROM ALL_INDEXES
WHERE INDEX_NAME LIKE 'EMP%';


Likely what's happening here is that your second set of data (after the UNION ALL does not return any data, and so only the first part is returning anything. Because you are passing a 0 for the date, this is 1900-01-01. All of the results are going to get grouped for that date.

Here's a similar mockup, but this one returns data after the UNION ALL

DECLARE @t TABLE
    (
      C1 INT ,
      C2 DATETIME ,
      C3 INT
    );

INSERT  INTO @t
VALUES  ( 1, '2016-01-01', 100 )
,       ( 1, '2016-01-01', 250 )
,       ( 1, '2016-03-05', 200 )
,       ( 2, '2016-02-01', 200 )
,       ( 2, '2016-02-01', 500 )
,       ( 2, '2016-06-01', 200 );

SELECT  YEAR(C2) [Year] ,
        MONTH(C2) [Month] ,
        SUM(C3)
FROM    ( SELECT    0 AS C2 ,
                    SUM(C3) AS C3
          FROM      @t
          WHERE     C1 = 1
          GROUP BY  C2
          UNION ALL
          SELECT    C2 ,
                    SUM(C3) AS C3
          FROM      @t
          WHERE     C1 = 2
          GROUP BY  C2
        ) AS A
GROUP BY C2;


If you are using FOR XML PATH you can specify nested elements using forward slash (/), like this:

DECLARE @yourTable TABLE (
    ItemCode    INT,
    ItemName    VARCHAR(10),
    Price       MONEY
)

INSERT INTO @yourTable VALUES
    ( 12345, 'test', 20 )

SELECT
    ItemCode,
    ItemName,
    Price AS "Prices/Price"
FROM @yourTable
FOR XML PATH('article'), ROOT('DocumentElement'), TYPE


How to design this kind of relations in a database

You should add more tables. Instead of Skills and projects being fields in Person, you should have PersonSkills (PersonID, SkillID) and PersonProjects (PersonID, ProjectID)

Also, Salary/Skills_Pay shouldn't be a float, and sex, probably not an int.


This is how I might approach this design... by adding an ID for the original manufacturer at the Product level, then storing the details in a history table.

The history table would allow for Manufacturer IDs (or other details) to change over time to be stored without a need for version numbers.

Product table

Product_ID
Product_Name
Orig_Mfgr_ID

Product History table

Product_ID
ProductHistory_ID
Orig_Mfgr_ID
Mfgr_StartDate
Mfgr_EndDate
CreateDate

I would then create a view or function to pull the most recent/current manufacturer based on the purchase date of the product, rather than storing this information in the Product table itself.


How come spatial objects can't be used in a VALUES statement?

What result are you expecting? It seems to work OK for me:

SELECT *
FROM (VALUES
  ('foo')  -- or (1), etc.
) AS t(x);
GO

| x   |
| :-- |
| foo |

SELECT *
FROM ( VALUES
  ( geography::Point(0,0,4326) )
) AS t(geog);
GO

| geog        |
| :---------- |
| POINT (0 0) |

dbfiddle here


How many cluster must i created on MongoDB Atlas?

MongoDB Atlas won't let you create a standalone server, so any Atlas cluster should be able to use transactions.


SQL Server mixed mode authentication not being set

To start, even though the gui shows mixed mode authentication, running

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

gives

'Windows Authentication'

Two related small things resulted in this issue. (1) My windows domain account is not an administrator, so I can't restart the SQL Server instance from the Configuration Manager. (2) Restarting windows does not reload the SQL Server configuration options, or at least, it won't change the authentication setting.

A windows domain account with administrative privileges on my local machine was able to stop and restart SQL Server from the Configuration Manager. I can now login with the SQL account I created.

After that, the above query gives

'Windows and SQL Server Authentication'

So, even though I can change the authentication options, and SQL Server displays in the GUI that the options have been changed, the options are not actually changed until the SQL Server instance is manually restarted by an admin (rebooting doesn't do it).


This query should accomplish your goal.

Select YEAR(fromDate) [Year], MONTH(fromDate) [Month], SUM(amount) [Amount]
FROM '__example__'
GROUP BY YEAR(fromDate), MONTH(fromDate)
ORDER BY Year, Month


MySQL ON DUPLICATE KEY UPDATE performance issue

Try to start a transaction or set autocommit=false prior the updates.

start transaction;
.... a lot of updates here ...
commit;

Also, the swapping may yield to the lack of physical RAM which occurs in some situations (big resources created by MySQL).

You may also try to
- increase the "key_buffer_size" to the maximum permitted by your SO and MySQL version (check manual). This will improve index updates.
- reduce "max_heap_table_size" (128MB or less) and increase "tmp_table_size" (the bigger, the better).
The idea is to avoid swappiness when some resources needs too much RAM, by sending the work to on-disk temporary tables by default. Some operations will suffer on speed (the bigger ones), but in whole the server will run smoother.

Of course, you may have a lot of memory so a perfect answer cannot be supplied without all informations, at least RAM quantity and server variables, because your problems may start there.


MySql - is possible that performance issues are caused by dynamic SQL?

Good news - it seems dynamic SQL is NOT a bottleneck.

The solution was to change temp tables to MEMORY engine.
It did the trick and made AMAZING difference. Solution was suggested in

Can MySql nested SP be a bottleneck?

create temporary table areas (
    id int not null,
    code varchar(30),
    name varchar(100),
    shortName varchar(100),
    levelid int not null,
    sortOrder int not null,
    key (id)
) ENGINE=MEMORY;


As the discussion in the comments revealed, an fcontainer can be related to many products, due to the 1-to-many relationship between FContainer and Stämplingar.

Try this query, which will choose only one product per fcontainer.

The missing fcontainers are due to the INNER join. Changing it to LEFT join will show all fcontainers, even those not related to any product:

;WITH CTE1 AS
(
    SELECT
        FContainerHylla.Name, FContainerPlats.HyllId,
        FContainerPlats.x, FContainerPlats.y,
        FContainerPlats.enable, FContainerHylla.Type,
        ISNULL( (SELECT MIN(fcontainer.id) AS id
                 FROM FContainer
                 WHERE (PlatsId = FContainerPlats.id)
                ), 0) AS FcId
    FROM FContainerPlats
      INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id
    WHERE (FContainerHylla.Type IN (1, 6, 10))
),

    CTE2 AS
(
    SELECT
        FContainer.id AS SubID, MIN(produkter.Produktnamn) AS Produktnamn
    FROM FContainer
      INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID
      INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr
      INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr
    GROUP BY
         FContainer.id
)

SELECT CTE1.*, CTE2.Produktnamn
FROM CTE1
  LEFT JOIN CTE2 ON CTE2.SubID = CTE1.FcId
ORDER BY CTE1.HyllId, CTE1.x, CTE1.y ;


Sending email from SQL Server without using DBMail

Configure the Smart Host:

Click Start, point to Programs, point to Administrative Tools, and then
click Internet Services Manager to open the IIS Manager.
Open the tree for your server. Right-click Default SMTP Virtual Server, and then click Properties.
Locate the Delivery tab, and then click Advanced.
Type the name of your SMTP mail server in the Smart Host text box. If you do not know the name of your SMTP mail server contact your mail administrator.
Make sure that the Simple Mail Transfer Protocol (SMTP) service is running. The SMTP service is part of the IIS Admin Service. Therefore, the IIS Admin Service must also be running.

Create a Stored Procedure to Send CDONTS E-Mail:
You can use code that is similar to the following to create a stored procedure in your database that sends e-mail by using the SQL Server OLE Automation stored procedures to invoke the CDONTS object model.
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]  @From varchar(100),  @To varchar(100),  @Subject varchar(100),  @Body varchar(4000),  @CC varchar(100) = null,  @BCC varchar(100) = null  AS  Declare @MailID int Declare @hr int  EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT EXEC @hr = sp_OASetProperty @MailID, 'From',@From  EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body  EXEC @hr = sp_OASetPropert, @MailID, 'BCC',@BCC  EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject EXEC @hr = sp_OASetProperty @MailID, 'To', @To  EXEC @hr = sp_OAMethod @MailID,'Send', NULL  EXEC @hr = sp_OADestroy @MailID

Next, use the stored procedure that you created and provide the correct parameters:
exec sp_send_cdontsmail 'someone@example.com','someone2@example.com','Test of CDONTS','It works'
Note Only members of the sysadmin fixed server role can run the OLE Automation stored procedures. If the SQL Server user is not a member of the sysadmin fixed server role, you cannot use the stored procedure that is mentioned in the example to send e-mail. In such a situation, you may have to develop a client application to send e-mail with CDONTS. For example, you can use a Microsoft Visual Basic application.
CDONTS [CDO for Microsoft Windows NT Server - It's a Simple Mail Transfer Protocol (SMTP) specific OLE server that is specifically designed to provide messaging functionality to Web-based applications], CDONTS sends the e-mail to the local SMTP virtual server. The server then routes the e-mail to the SMTP mail server that is specified in the Smart Host text box. The SMTP mail server sends the mail to the e-mail address that is specified in the To: argument ("someone2@example.com" in the example). The name that is specified in the From: argument appears as the sender of the e-mail ("someone@example.com" in this example) with the subject "Test of CDONTS" and the message "It works" as the body of the e-mail. No one is copied on the e-mail because you did not supply an argument for the CC or for the BCC field. You can modify the example so that it will send HTML-based e-mail or attachments.
For documentation about CDONTS, visit the following Microsoft Web site:

http://msdn.microsoft.com/library
https://support.microsoft.com/en-us/help/312839/how-to-send-e-mail-without-using-sql-mail-in-sql-server


No, the cluster will not go down until a node cannot form quorum.  Assuming all four nodes have a quorum vote, there are five possible votes.  Accordingly a node will have to get three votes to maintain or gain quorum.  When the file share witness goes offline, the node that currently owns quorum will maintain ownership because it can still get four votes.

See Understanding Quorum in a Failover Cluster for the long answer.


You can use this command to disable parsing for substitution variables for the session.

SET DEFINE OFF;

Enter this at the start of the query or operation.  This cannot be used with PL/SQL code blocks but the standard SELECT, INSERT, UPDATE, DELETE allow it.

This Stack Overflow question from 2010 shows this is a popular question!


Datetime to formatted Date Implicit Conversion

The query you posted does indeed produce PlanAffectingConvert warnings in SQL Server 2016 SP1 (didn't try on earlier versions).

That's a bug - those convert statements shouldn't be throwing that warning since it doesn't affect cardinality in any way when they're just in the SELECT portion.

File a bug report for it at http://connect.microsoft.com and include your repro code, and edit your question to include a link to the Connect item so folks can upvote it.


Dig into PG10 source code https://github.com/postgres/postgres/blob/REL_10_STABLE/src/backend/access/transam/xlog.c#L2224, thanks  @pifor for the hint

/*
 * Calculate CheckPointSegments based on max_wal_size_mb and
 * checkpoint_completion_target.
 */
static void
CalculateCheckpointSegments(void)
{
    double      target;

    /*-------
     * Calculate the distance at which to trigger a checkpoint, to avoid
     * exceeding max_wal_size_mb. This is based on two assumptions:
     *
     * a) we keep WAL for two checkpoint cycles, back to the "prev" checkpoint.
     * b) during checkpoint, we consume checkpoint_completion_target *
     *    number of segments consumed between checkpoints.
     *-------
     */
    target = (double) ConvertToXSegs(max_wal_size_mb) / (2.0 + CheckPointCompletionTarget);

    /* round down */
    CheckPointSegments = (int) target;

    if (CheckPointSegments < 1)
        CheckPointSegments = 1;
}

So the max_wal_size is divided across three checkpoints, two prev and the current with checkpoint_completion_target in mind.


PostgreSQL has few functions reporting information on the client environment, listed at
https://www.postgresql.org/docs/current/static/functions-info.html

Concerning the remote IP address, it provides inet_client_addr().

Concerning the client username, I don't think it has this information at all, because it's not passed at connection time, unless it happens to be the same as the database user name, but the backend can't know that in general.

If the SQL functions need that information,it could be passed up front by your applications in the form of a SET statement. Current versions of PostgreSQL allow to instantiate any namespace for custom variables.

Example with version 9.3:

test=> set session.osuser to 'daniel';
SET

test=> show session.osuser;
 session.osuser
----------------
 daniel
(1 row)

test=> select current_setting('session.osuser');
 current_setting
-----------------
 daniel
(1 row)


How to put result of SQL into bind variable

exec is a PL/SQL block:

SQL> var teste number
SQL> exec select trunc(avg(user_id)) into :teste from dba_users;

PL/SQL procedure successfully completed.

SQL> print :teste

     TESTE
----------
 447392453

It is the same as:

begin
  select trunc(avg(user_id)) into :teste from dba_users;
end;
/

SQL*Plus substitution variable:

SQL> column my_value new_value teste
SQL> select trunc(avg(user_id)) my_value from dba_users;

  MY_VALUE
----------
 447392453

SQL> define teste
DEFINE TESTE           =  447392453 (NUMBER)
SQL>


You must be doing something odd with your DDL or you have a very, very old version of MySQL.

MyISAM Index creation can be very lethargic. I wrote about this almost ten years ago.

Oct 10, 2006 : Why does mysql drop index very very slow in a large table? (from the MySQL General Discussion Forum)
May 12, 2011 : Does MySQL still handle indexes in this way? (where I mention this again)

Without any additional details from you, I can only guess. Here it goes:

I suspect your must be just doing the following:

ALTER TABLE mytable ADD INDEX (column01);
ALTER TABLE mytable ADD INDEX (column02);
ALTER TABLE mytable ADD INDEX (column03);
...
ALTER TABLE mytable ADD INDEX (column10);
ALTER TABLE mytable ADD INDEX (column11);

I would like to suggest the following

SUGGESTION #1 : Build all 11 indexes at the same time

You should create all 11 indexes in a single DDL statement

ALTER TABLE myisamtable
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;

SUGGESTION #2 : Delay indexes building

You can disable the building of non-unique indexes so that sorting and be done.

Doing so can make a smaller BTREE for each index

ALTER TABLE mytable DISABLE KEYS;
...
ALTER TABLE mytable ENABLE KEYS;

SUGGESTION #3 : Use a temp table

If you wish to test this manually, you can make a temp table, add all the indexes to the empty temp table, disable indexes, insert the data, enable indexes, and switch table names.

Here is a sample of what I just described to you

CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

GIVE IT A TRY !!!


Added skip-character-set-client-handshake to the [mysqld] group of the my.cnf file.

# The MySQL server
[mysqld]
skip-character-set-client-handshake
collation_server=utf8_unicode_ci
character_set_server=utf8

My-small.cnf as supplied in "support files" does not contain this, subsequently my connection was overriding the values, causing me to think the file was not being read... newbie mistake.

Solution mentioned on the MySQL manual here.


Login failed for user when trying to connect to Azure Postgre database from MSSMS

Instead of SSMS use pgAdmin to connect to Azure Postgresql. You can download the software from here.

1.Open the pgAdmin application on your client computer.

2.From the toolbar go to Object, hover over Create, and select Server.

3.In the Create - Server dialog box, on the General tab, enter a unique friendly name for the server, such as mydemoserver.

4.In the Create - Server dialog box, on the Connection tab, fill in the settings table.

5.Select Save.

6.In the Browser pane on the left, expand the Servers node. Select your server, for example, mydemoserver, to connect to it.

7.Expand the server node, and then expand Databases under it. The list should include your existing postgres database and any other databases you've created. You can create multiple databases per server with Azure Database for PostgreSQL.

8.Right-click Databases, select the Create menu, and then select Database.

9.Enter a database name of your choice in the Database field, such as mypgsqldb2.

10.Select the Owner for the database from the list box. Select your server admin login name, such as the example, my admin.

11.Select Save to create a new blank database.

12.In the Browser pane, you can see the database that you created in the list of databases under your server name.

For more details please read this documentation.


What free database software support sequences

If with "free" you mean "open source", then Firebird comes to mind (but I personally would prefer PostgreSQL over it - the much better documentation alone is a reason).

Ingres also supports sequences, but I don't know if there is a .Net driver for it.

There are other open source ones like Apache Derby, HSQLDB and H2 Database but as they are Java only, I'm pretty sure they do not have a .Net driver

If with "free" you mean "free of charge", then Oracle Express, DB2 Express-C and SQL Server 2012 Express are an option as well (as all three of them support sequences)


Overwrite event - data from SQL Server to MySQL

Write PHP/Perl/Java/VB/whatever code to SELECT from one db server and INSERT/UPDATE/whatever into the other.

For "timed", use cron (if on *nix).


How to predict new node allocation in a Cassandra cluster?

Be  careful about decreasing the amount of free disk space. The recommendation for cassandra 2.1 is to maintain 50-80% free space. You can read about it on the Datastax site. The free space is needed during compaction as the SStables will be streamed to disk. Depending on your compaction strategy, you can determine your disk capacity.


Best approach for a one-to-many list - how to store/acces some related count info?

Without specifics such as which database system, version, and implementation (when applicable), your table schema, how much data there is, anticipated will be, and general growth pattern, it's hard to recommend one option over the other.
It looks like you're trying to do standard types of OLAP querying against your data in which case both options are valid (to a degree). Most modern RDBMS can handle OLAP type queries for Tables with rows into the multi-millions without a blink of an eye. So option 1 can be sufficient of a solution up to a certain point, as long as proper architecting and indexing is adhered to. For example (and this is where it's important to know specifics like which database system you're on), Microsoft SQL Server offers columnstore indexing which can immensely improve the performance of an OLAP type query by efficiently compressing the data in a columnar format which helps improve aggregation queries. YMMV on these kinds of features depending on which database system you're using.
Alternatively, when you get to a point where the data is just too much for your regular OLTP architected database to handle, then pre-storing data rollups and the answers to your other OLAP type queries in data cubes (loosely using the term here) makes sense and is accomplished by either a real-time process (such as via triggers) or periodically calculated at a rate acceptable to the business needs, and is considered the one source of truth for whatever state it's in at any point of time. But I would still follow some level of normalization and not store this information directly on the person table, rather stored on a table that references the person table (by storing the person_id for example).


Don't get me wrong. I suggest you to get rid of user information to make you query more readable and because user information is out of scope of your answer. IMHO is better to have more small queries with a single purpose that to have single big query that retrieves all information your application need, if there isn't a good reason to do so (typically performace). Small queries are more maintainable, more understandable (seeing then after years) ... you can add here your prefered boring virtue :-)

I attempt to outline an answer because I did not understand exactly what you want to achieve.

I suppose there is only a bonus game for week and poll so the following query always returns a number:

select (game.hteam_score - game.vteam_score)
from xf_nflj_pickem_game as game
where game.bonus = 1 and game.week_id = 1 and game.pool_id = 1;

So I suppose you want to sum the difference from previous number and pick.hscoreary - pick.ascoreary. I have rewritten your query:

SELECT pick.user_id,
       count(game.game_id) as correct_picks,
       sum(game.points)    as points,
       -- game.bonus,      -- cannot aggregate on this field
       SUM(
         CASE
         WHEN pick.hscoreary is not NULL
         THEN ABS( (select (game.hteam_score - game.vteam_score)
                    from xf_nflj_pickem_game as game
                    where game.bonus = 1 and game.week_id = 1 and game.pool_id = 1)
                    - (pick.hscoreary - pick.ascoreary)
                 )
         ELSE 0
         END)
       as margin

FROM      xf_nflj_pickem_game as game
LEFT JOIN xf_nflj_pickem_pick as pick ON game.game_id = pick.game_id
WHERE       game.winner = pick.team_id
       AND game.week_id = 1
       AND game.pool_id = 1
       AND pick.team_id <> 0
GROUP BY pick.user_id;

I don't know if the previous query is correct. But you can try :-)

You can retrieve information about user from the result set of previous query:

SELECT user.username FROM xf_user where user_id IN(...)


There are tens of different ways to do this in SQL. Lets start with the simple correlated subquery (mind the fancy name, once you see and write a few of them, they are very easy to understand):

select                                -- show
    g.name, g.stage, g.score          -- all data
from                                  -- from
    game as g                         -- the table
where                                 -- where
    not exists                        -- there isn't
        ( select *                    -- any other
          from game as g2             -- from the same table
          where g2.stage = g.stage    -- and the same stage
            and g2.score > g.score    -- with bigger score
        ) ;

Another simple way would be to first find the biggest score for each stage using GROUP BY (in a subquery, either a derived table or a CTE) and then JOIN back to the original table:

-- using derived table
select
    g.name, g.stage, g.score
from
    game as g
  join
    ( select stage, max(score) as score
      from game
      group by stage
    ) as m
  on  m.stage = g.stage
  and m.score = g.score ;

-- using CTE
with stage_max as
    ( select stage, max(score) as score
      from game
      group by stage
    )
select
    g.name, g.stage, g.score
from
    game as g
  join
    stage_max as m
  on  m.stage = g.stage
  and m.score = g.score ;

A more modern way would be to use window functions (available in your SQL Server versions), i.e. the RANK() function, so first you get the "rank" of everyone per stage and then select only the ones with rank=1. This can also be done with either a derived table or a CTE:

-- window functions, using derived table
select
    w.name, w.stage, w.score
from
    ( select name, stage, score,
             rnk = rank() over (partition by stage
                                order by score desc)
      from game
    ) as w
where
    w.rnk = 1 ;

-- window functions, using CTE
with ranking as
    ( select name, stage, score,
             rnk = rank() over (partition by stage
                                order by score desc)
      from game
    )
select
    w.name, w.stage, w.score
from
    ranking as w
where
    w.rnk = 1 ;


PostgreSQL: DBLink weird permission/connection error

Here is what I think is happening.  I don't think it is a bug.

You have a view.  That view is somewhat complex but it is a dynamically rewritten query.
The planner is pretty smart.  It will ignore UNION clauses where the search criteria will not produce an output result.
You are not executing what you think you are.  (EXPLAIN ANALYSE should show you this)

My recommendations:

Wrap the whole result set in a plpgsql function.  Wrap that function in the view.  OR
Use a WITH clause.  I am less sure about this one.  I think the with clause might result in running the whole thing consistently but not 100% sure, and it is not guaranteed in future versions.  Therefore wrapping in a function is probably better.

I think what the planner is seeing is:

"Oh we don't need to execute the first and third portions of the view because they are not in the output!  Let's skip and only run the second portion!"  This is a feature, not a bug (and it is what allows table partitioning to be useful).


You are missing an END. See updated as below:

Note: Use Aaron's version of sp_msforeachdb

declare @sql nvarchar(max)

set @sql = '
IF NOT EXISTS (SELECT * FROM sys.triggers T WHERE T.name =
''Database_DDL_Audit'') AND ''?'' <> ''TempDB''
BEGIN
EXEC
(
''
CREATE TRIGGER Database_DDL_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @EventDataXml   XML
DECLARE @SchemaName     SYSNAME
DECLARE @ObjectName     SYSNAME
DECLARE @EventType      SYSNAME

SET @EventDataXml = EVENTDATA()

SELECT
@EventType  = @EventDataXml.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''SYSNAME'''')
, @SchemaName = @EventDataXml.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', ''''SYSNAME'''')
, @ObjectName = @EventDataXml.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''SYSNAME'''')

INSERT [ServerAdmin].[dbo].[ServerChangeLog]
(
[CreateDate]
, [LoginName]
, [ComputerName]
, [ProgramName]
, [DBName]
, [SQLEvent]
, [SchemaName]
, [ObjectName]
, [SQLCmd]
, [XmlEvent]
)
SELECT  GETDATE()
        , SUSER_NAME()
        , HOST_NAME()
        , PROGRAM_NAME()
        , @EventDataXml.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''SYSNAME'''')
        , @EventType
        , @SchemaName
        , @ObjectName
        , @EventDataXml.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''NVARCHAR(MAX)'''')
        , @EventDataXml
END
''
)
END
'
print @sql


Anyone Know of Some Desktop Software that Searches an SQL server for a String

Step 1: Download SSMS LINK

Step 2: Download Red Gate SQL Search LINK

Step 3: Install both

Step 4: Connect to your database server

Step 5: Search!


Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use:

CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
    SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
    FROM generate_series(1, $1);
$body$
LANGUAGE 'sql'
VOLATILE
SET search_path = 'pg_catalog';

It's a simple SQL function that's cheaper to call than PL/PgSQL.

The difference in performance due to the changed aggregation method is immense for larger bytea values. Though the original function is actually up to 3x faster for sizes < 50 bytes, this one scales much better for larger values.

Or use a C extension function:

I've implemented a random bytea generator as a simple C extension function. It's in my scrapcode repository on GitHub. See the README there.

It nukes the performance of the above SQL version:

regress=# \a
regress=# \o /dev/null
regress=# \timing on
regress=# select random_bytea(2000000);
Time: 895.972 ms
regress=# drop function random_bytea(integer);
regress=# create extension random_bytea;
regress=# select random_bytea(2000000);
Time: 24.126 ms


Getting a "Request to run job refused because the job is already running" When no job is running?

Alright so the problem I was having was that the write time to the log file for job completion occasionally takes longer than it takes to run the stored procedure that restarts this loop.

The solution to this is my original workaround.

In case anyone wants to use it here is the code.

BEGIN
WAITFOR DELAY '00:01';
    EXECUTE dbo.LoopStageStore
END; GO

Just make it wait a little bit.


Comparing 1 Database with Many Tables to Multiple Databases with Fewer Tables in Each

In general, a good RDBMS will handle both solutions the same way and you should not see performance differences. However, you do need to test with your data load on your (R)DBMS. MySQL in particular does not always follow the relational rule/mind set and your results might therefor vary.

There are two main differences that I can think of:

You cannot declare referential integrity constraints across databases.
If you split the data in several databases, restore from a backup might require a smaller downtime if you can restore the most important database first and bring up the application partially functioning. (This requires the App to be written in a way that it can gracefully handle partial database availability.) You could for example keep one month worth of data in a small active database and move older data to a larger archive database that also could live on cheaper (read: slower) storage. However, depending on your RDBMS, you might be able to achive the same without spreading across multiple databases. With SQL Server for example you can implement a similar solution within a single database by separating tables in different file groups. SQL Server allows a restore to happen one file group at a time. Once a file group is restored you can access its data. Accessing a table living on a not-yet-restored file group will fail with an error comparable to the one you get when accessing a normal database that has not yet finished restoring.


I understand what you are saying, but the reason it works in SQL Server is because it's in a single statement, not because the transaction isn't committed.  In SQL Server you still don't have deferred integrity - EACH statement has to satisfy the constraints, regardless of whether there is an open transaction.  If there was to be a BEGIN TRANSACTION and two UPDATEs it would fail on SQL Server.

Having said all that, in your case, the UPDATE is done in a single statement, and I think you're running into a MySQL defect.

Also: https://stackoverflow.com/questions/6968247/mysql-giving-duplicate-entry-error-when-trying-to-increment-date-field


copy table comments from one MySQL database to another

You could use the INFORMATION_SCHEMA to sculpt the SQL commands for you.
SELECT CONCAT('ALTER TABLE ',db,'.',tb,' COMMENT ''',com,''';')
FROM (SELECT table_schema db,table_name tb,table_comment com
FROM information_schema.tables WHERE table_schema NOT IN
('inforamtion_schema','performance_schema','mysql')
AND table_comment <> '') A;

Output this query to a text file and view the text file
SQL="SELECT CONCAT('ALTER TABLE ',db,'.',tb,' COMMENT ''',com,''';')"
SQL="${SQL} FROM (SELECT table_schema db,table_name tb,table_comment com"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('inforamtion_schema','performance_schema','mysql')"
SQL="${SQL} AND table_comment <> '') A"
mysql -uroot -ppass -ANe"${SQL}" > AddCommentsToTables.sql
less AddCommentsToTables.sql

If the output looks like what you need, then copy the script to the production server, login to mysql and run this to execute the script:
mysql> source AddCommentsToTables.sql

Give it a Try !!!
UPDATE 2014-04-04 16:34 EDT
It is a little harder to change each column definition's COMMENT. Why ???
According to the MySQL Documentation

When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows:
ALTER TABLE t1 MODIFY col1 BIGINT;
The resulting column will be defined as BIGINT, but will not include the attributes UNSIGNED DEFAULT 1 COMMENT 'my column'. To retain them, the statement should be:
ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
Warning This conversion may result in alteration of data. For example,
if you shorten a string column, values may be truncated. To prevent
the operation from succeeding if conversions to the new data type
would result in loss of data, enable strict SQL mode before using
ALTER TABLE (see Section 5.1.7, “Server SQL Modes”).

You would have to express the entire column definition from scratch each time. Doing that is not only tedious but dangerous because it would just copy the data into a fresh temp table and rename the temp table afterwards.
ADDITIONAL WARNING : StackOverflow has this : https://stackoverflow.com/questions/2162420/alter-mysql-table-to-add-comments-on-columns
One of the comments on the accepted answer says : This solution can break the auto increments. –  workdreamer Jul 31 '13 at 12:10
That's makes sense because of what I just said: The table get's copied, so there will be some auto_increment movement.


run these commands for ubuntu

sudo rm -rf /tmp/mongodb-27017.sock
sudo service mongod start
sudo ps aux | grep mongo
sudo kill


Update primary SQL Server in mirrored environment using transaction logs from old environment

When we are ready to go live with the new environment I plan to take another transaction log from the old environment and restore that to the new primary server. Is that possible? Will this be successfully synchronized to the mirror?

You can set up mirroring as normal. But when you want to failover, then use SYNCHRONOUS - High safety without automatic failover.

using T-SQL :

ALTER DATABASE <<DB NAME>> SET PARTNER FAILOVER

Doing so, only the role reversal happens and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN.

Now once you verify everything is OK, then you can break mirroring with below steps :

Turn mirroring off (break mirroring) on the mirror DB.

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Set the DB to RECOVERY

RESTORE DATABASE <<DB NAME>> WITH RECOVERY

Migrate Logins as well as any sql agent jobs to secondary. You might have to fix orphaned users.

EXEC sp_change_users_login ‘Auto_Fix’ , ‘<<username>>’


Different syntax to add/substract interval

My guess(!) is:

The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval

The - operator on the other hand supports two different combinations:

timestamp - timestamp
timestamp - interval.

Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.


Disk space requirement for REINDEX in Postgresql

Up to three times than the size of new index:

index structure itself
WAL records during create index
temporary files while sorting index values

I have deleted old records in this table

It is possible to reclaim space using the pgcompacttable tool. pgcompacttable was designed specifically for compressing tables when there is not enough space for a copy of the table.
During an update or insert, postgresql will write the new version of the tuple to a free space at the beginning of the table. Therefore, if we somehow update all tuples at the end of the table, the subsequent vacuum will find only empty pages at the end of the table without actual (live) data and will return those pages to the file system. So the size of the table on disk will be reduced. pgcompacttable performs exacly such updates + vacuum.


select insert query optimization on large volume MYSQL 8.x

The first thing about your query is the indexing on bidPrice needs to map to your join criteria:
Hence the index on bidPrice should be:
idRequest, idExchangePair, idExchangePlatform

The first two can be in either order as both are referenced with an = and idExchangePlatform is a range < based search so leave this last.
Extend one of the existing indexes that begins with the same element(s).
The subquery SELECT statements in the query are ugly.
SELECT ...,
   GREATEST(bp1.lastPrice, bp2.lastPrice),
   (ABS(bp1.lastPrice - bp2.lastPrice))*100)/GREATEST(bp1.lastPrice, bp2.lastPrice)

will make it simpler (but probably won't help with speed significantly) - ref: GREATEST.
To help with such a large scan, batch this up based on the first element of the index above.
So:
INSERT ... SELECT ... JOIN ... WHERE idRequest < 10000;
INSERT ... SELECT ... JOIN ... WHERE idRequest BETWEEN 10001 AND 20000;
....


Is it possible to execute an "update" trigger if all columns except one have changed?

I'm not sure about performance impact, but you can convert row to jasonb, remove field, and compare json objects.
So changing WHEN (OLD.* IS DISTINCT FROM NEW.*) to
when (row_to_json(old)::jsonb - 'last_seen' is distinct from
 row_to_json(new)::jsonb - 'last_seen') will do the job.

Also, if you want to modify NEW.updated , it should be BEFORE UPDATE  trigger, not AFTER UPDATE.


How to Revoke and Re-grant Permissions for an in-use MySQL User

I hope It should work, Please have a look at following

GRANT ALL ON *.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'
1 row in set (0.00 sec)

Above shows that all privileges are granted to myuser on all databases.Now grant privileges for single db.

GRANT ALL ON mydb.* to 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'
*************************** 2. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'%'
2 rows in set (0.00 sec)

Now revoke unnecessary privleges

REVOKE ALL PRIVILEGES ON *.* FROM 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

Now check which privileges still exits

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT USAGE ON *.* TO 'myuser'@'%'
*************************** 2. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'%'
2 rows in set (0.00 sec)

That's what you need !!


Bulk insert from a complex text file into a table

SQL Server BULK INSERT is very limited in the types of files it can import and your type of file is way outside of what it can do.

The best approach is to have the file format changed.

Second best is to have an external application do the parsing of the file and upload data to table.

If none of the above is possible then you should use SQL Server solutions below:

Assuming the "header" section appears only once in a file, load this file in two parts.
Load the first part into a separate one column staging table (use LASTROW parameter). The second part would be loaded using your current approach. You will then join the data using tSQL.
Create a staging table that would have an ID (will act as row number) and a single VARCHAR( MAX) column. Load the file into this table as is, without column splitting.
Then, using SQL queries, manipulate the data into the format you need. The code will be rather ugly.

Some useful info: Splitting delimited strings

Update

SQL Server implementation option 1 would follow the steps below. Note: this is pseudo-code to avoid overly long code, that I have no way to test.

create table ##ImportHeader (RowID INT IDENTITY, RowData VARCHAR( 3000 ))
-- Load the first 3 rows into a separate table.
bulk insert ##ImportHeader from [YourFile] with( LASTROW = 3, FIELDTERMINATOR='\t' ROWTERMINATOR = '\n' )

-- Load the rest of the data
-- Your import table needs to have the same number of columns as the data you are loading
create table ##Import (RowID INT IDENTITY, Name VACRHAR( 200 ), Department VARCHAR( 200 ), Status VARCHAR( 10 ), Age INT, Gender VARCHAR( 10 ))
bulk insert ##Import from [YourFile] with( FIRSTROW = 5, FIELDTERMINATOR='\t' ROWTERMINATOR = '\n' )

-- Join data
SELECT CompanyID.RowData, CompanyName.RowData, CAST( SUBSTRING( CompanyDate.RowDate, 5, 50 ) AS DATETIME ),
   D.*
FROM ##Import AS D
    INNER JOIN ##ImportHeader AS CompanyID ON CompanyID.RowID = 1
    INNER JOIN ##ImportHeader AS CompanyName ON CompanyName.RowID = 2
    INNER JOIN ##ImportHeader AS CompanyDate ON CompanyDate.RowID = 3

What you are doing is loading the same file twice. First time you are loading first 3 rows and second time you are loading the rest of the structured data.


Different Maximum server memory across Availability Replicas

That’s due to fact it (SECONDARY) has to serve same level of work load as PRIMARY. I.e. when PRIMARY is memory intensive for 32GB, and SECONDARY limits to 24GB in the incident when it’s become PRIMARY it could perform same as primary the impact (8GB Memory) could be in various factors

Additional connections might not established
Existing requests might be slower
Log redo could be backlogged
Etc..


How to split a table in SQL Server into two different related tables inserting foreign key?

I do not believe you will save any storage space by splitting the attributes into two tables. INT uses 4 bytes of storage space.  A date uses 3 bytes. Say you compress down a million rows to just one value. ( Ex There are 1 Million rows that have the combination of Event ID 5 and date 2019-10-07). You will save a total of 3 million bytes. That's only 3 megabytes.

You could technically do the same thing you want on the other side of the table; split off every combination of MyValue, create a look up table of them, and then join each on a third table. But don't!

Readability and maintenance are things to consider along side just storage cost. So is your time developing this solution. The current 10/7/2019 cost for Azure SQL database storage (past your 32gb of free data) is $0.138/GB/Month. After 3 years of every week gaining a full GB of data(Total storage is now 156gb), you will have only spent a TOTAL of $1,679.18.


Can we enable alwayson feature of sql 2012 in windows 8.1?

Short answer is, No.

To enable Sql Server AlwaysOn, the server

  The server instance must reside on a Windows Server Failover Clustering (WSFC) node. Enable and Disable AlwaysOn Availability Groups, MSDN

Server Clustering is only available on Windows Server OS (i.e. Not 8.1)
Windows Server Clustering

You might want to see Overview of AlwaysOn Availability Groups (SQL Server)

Anticipating further questions, you might want to have a look at other disaster recovery options that you might be able to use: Description of disaster recovery options for Microsoft SQL Server
Something like log shipping might be the most feasible and easy to set up (assuming you have limited resources, and that's why you have SQL Server installed on Windows 8.1 and not a server)

Edit
Without lingering on this too much, for the sake of completeness I'll point out there is a difference between AlwaysOn Availability groups and SQL Server Failover Cluster Instances, discussed here, the former only available on Enterprise edition. Still both dependant on WSFC though so it doesn't make a difference for you.


The easiest way would be to create a role, assign that role select access to the table in question, then assign the users and logins to that role.
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15


How do I tell if a long running update won't lock the database?

Any UPDATE transaction will always acquire locks on the table being updated in some capacity. This is the normal default behavior in all modern relational database management systems. It's just a matter of the type of lock and how long a particular row stays locked during an UPDATE. Depending on the isolation level your database is configured for, even though a row may be locked, doesn't necessarily mean things like reads of that row will be blocked. You can read up more here on locks in MariaDB.
To your follow up question in the comments, depending on the isolation level, if an UPDATE is locking a set of rows that causes blocking on other subsequent queries running against those same rows, then those other queries will wait until those rows are no longer locked by the UPDATE query, to execute, in general.


How about using a prepared statement to do it? Something like the below:

SELECT @orderBy := orderby, @ascDesc := ascdesc FROM user WHERE UserId = @id;
PREPARE cmd
FROM 'SELECT * FROM user WHERE user.UserID = ? ORDER BY ? ?';
EXECUTE cmd USING @id, @orderBy, @ascDesc;
DEALLOCATE PREPARE cmd;

EDIT

Apparently it is not possible to use parameters in the way that I have above (see comment below) in which case you should do the following:

SELECT @orderBy := 'Name', @ascDesc := 'ASC' FROM user WHERE Id = @id;
SET @s :=
CONCAT('SELECT * FROM user WHERE user.UserID = ? ORDER BY ',@orderBy,' ',@ascDesc);
PREPARE cmd FROM @s
EXECUTE cmd USING @id;
DEALLOCATE PREPARE cmd;

I hope this helps you.


LocalDB is installed by Visual Studio and is intended to be minimal configuration for development work.  Check out here.

If you want to keep just one, I'd go for SQL Express as you can easily connect to there for your development work.


Since it was not in most_common_vals, PostgreSQL then looks in the histogram.  If there is no histogram, it would conclude the most common values are the only values present in the table, and therefore the estimate for 123123 is zero.  But it doesn't allow zero estimates in most places, to prevent div by zero error, and instead clamps it at 1.

0.5% is for cases where there is no information, like for a generic query plan or a join where the value won't be known at planning time.

But having a list of MCV and the one you want not being present in that list and there being no histogram to fallback on does constitute information.


how to dump a single stored procedure from a database

BAD NEWS

You cannot use mysqldump to do this.

GOOD NEWS

You can use the mysql client. First, find out if the Stored Procedure exists. If it does, then fetch the type of Procedure it is (PROCEDURE or FUNCTION). Then, issue the SHOW CREATE for the Stored Procedure. You will have to strip the top 3 and bottom 3 lines:

DBNAME=${1}
SPNAME=${2}
SPFILE=${DBNAME}_${SPNAME}.sql
SPTEMP=${DBNAME}_${SPNAME}.tmp
MYSQL_CONN="-u... -p..."
SQLSTMT="SELECT COUNT(1) FROM mysql.proc WHERE db='${DBNAME}' AND name='${SPNAME}'"
PROC_EXISTS=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
if [ ${PROC_EXISTS} -eq 1 ]
then
    SQLSTMT="SELECT type FROM mysql.proc WHERE db='${DBNAME}' AND name='${SPNAME}'"
    PROC_TYPE=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
    SQLSTMT="SHOW CREATE ${PROC_TYPE} ${DBNAME}.${SPNAME}\G"
    mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}

    #
    # Remove Top 3 Lines
    #
    LINECOUNT=`wc -l < ${SPFILE}`
    (( LINECOUNT -= 3 ))
    tail -${LINECOUNT} < ${SPFILE} > ${SPTEMP}

    #
    # Remove Bottom 3 Lines
    #
    LINECOUNT=`wc -l < ${SPTEMP}`
    (( LINECOUNT -= 3 ))
    head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
else
    echo "Stored Procedure ${DBNAME}.${SPNAME} Does Not Exist"
fi
rm -f ${SPTEMP}

Here is a sample run:

[root@***]# ./GetMyProc.sh common DMSPushers
[root@***]# cat common_DMSPushers.sql
CREATE DEFINER=`pma`@`10.%` PROCEDURE `DMSPushers`()
BEGIN

  DECLARE cntr INT DEFAULT 0;
  DECLARE dealerName VARCHAR(50);
  DECLARE dealerID INT;

  DECLARE done boolean DEFAULT false;
  DECLARE dealers CURSOR FOR SELECT bdd.dealer_id, ta.name FROM `b_dealer__dmsaccount` bdd left join t_dmsaccount ta on bdd.dmsaccount_id = ta.id where ta.dms_type = 1 order by bdd.dealer_id desc;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := true;

  OPEN dealers;

  read_loop: LOOP
        FETCH dealers INTO dealerID, dealerName;
        SET cntr = cntr + 1;

    IF done OR cntr > 200 THEN
      LEAVE read_loop;
    END IF;

        IF dealerID > 0 AND dealerID < 664 THEN
                IF dealerName IS NULL THEN
                        SET dealerName = '';
                END IF;
                SET dealerName = REPLACE(dealerName,'''','''''');

                -- Does the Database even exist???
                SET @query = CONCAT('SELECT COUNT(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ''D',dealerID,''' INTO @schemaExists');
                PREPARE stmt1 FROM @query;
                EXECUTE stmt1;

                IF @schemaExists > 0 THEN
                        SET @query = CONCAT('INSERT dealers_that_push (dealerID, dealerName, pushDate) SELECT ', dealerID, ',''', dealerName ,''',date FROM D',dealerID,'.contactLog where message like ''%pushed to the DMS%''');
                        PREPARE stmt1 FROM @query;
                        EXECUTE stmt1;
                END IF;

        END IF;

  END LOOP;

  CLOSE dealers;
END
[root@***]#

GREAT NEWS

Here is a script that will dump every Stored Procedure into separate SQL files:

MYSQL_CONN="-u... -p..."
SQLSTMT="SELECT COUNT(1) FROM mysql.proc"
PROCCOUNT=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
if [ ${PROCCOUNT} -eq 0 ] ; then exit ; fi
SPLIST=""
for DBSP in `mysql ${MYSQL_CONN} -ANe"SELECT CONCAT(type,'@',db,'.',name) FROM mysql.proc"`
do
    SPLIST="${SPLIST} ${DBSP}"
done
for TYPEDBSP in `echo "${SPLIST}"`
do
    DB=`echo "${TYPEDBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $2}'`
    SP=`echo "${TYPEDBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $3}'`
    SQLSTMT=`echo "SHOW CREATE ${TYPEDBSP}\G" | sed 's/@/ /'`
    SPFILE=${DB}_${SP}.sql
    SPTEMP=${DB}_${SP}.tmp
    echo Echoing ${SQLSTMT} into ${SPFILE}
    mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}
    #
    # Remove Top 3 Lines
    #
    LINECOUNT=`wc -l < ${SPFILE}`
    (( LINECOUNT -= 3 ))
    tail -${LINECOUNT} < ${SPFILE} > ${SPTEMP}
    #
    # Remove Bottom 3 Lines
    #
    LINECOUNT=`wc -l < ${SPTEMP}`
    (( LINECOUNT -= 3 ))
    head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
    rm -f ${SPTEMP}
done
ls -l

Give it a Try !!!


Loading Oracle ctl export file into postgresql

The solution is to load the data into oracle and then use one of the migration toolkits to move it to Postgres. Oracle produce a number of virtual box VMs that limit the effort needed but its still an extra step.


ms/write on tempdb data files are dead slow

ASYNC_IO_COMPLETION waits occur when a task is waiting for I/Os to finish.

PREEMPTIVE_OS_PIPEOPS occurs when SQL Server runs code in Windows.  For example, if you run xp_cmdshell, SQL Server spawns a command that is controlled by the Operating System.  While the command is running SQL Server shows PREEMPTIVE_OS_PIPEOPS as the wait since it doesn't know what the OS is actually doing.

The combination of these two waits seems like the operating system is busy.  You mentioned that the server is a virtual machine - if SQL Server itself is not super busy, it's quite likely the physical host server may be very busy servicing other VMs.  Ensure your SQL Server VM has a memory and CPU reservation appropriate for its needs.  Also, if running on VMware, ensure the VM uses the PVSCSI disk controller for the drives where SQL Server data and logs reside.


TLDR; firstly no it doesn't make sense to do that, secondly you can do that and the application wont care / notice either way

The main thing that you are fighting with confusing is human interaction, So as far as any application is concerned you should have the user_id, employee_id and a leave_request_id, any application when coded correctly will be able to identify the differences,

Looking at it, would it not be easier to have a person table (I'm presuming here that users are external to your employee list.

From here you could have a person and all their details in that table, with the identity field on it, and then if they are a user that entry is added into that table, if they are an employee they are entered into that table, (if both then both tables) and finally if an employee leaves they can be added into that table.

Now you have one id across all of your tables which makes joining them together a lot easier if you need to look at any users who are employees who have left you have a single ID across all tables, it also means that from a human perspective the ID you know what it all is in relation to because its all the same.


Error 10053 connecting with Management Studio

There are several causes for this. The ones I know of (at least)

max server memory set to 0
user connections set to 1 (limiting you to one only)
and possibly server side SSL enabled

Have you ever connected? Changed any settings? Have a backup of master? What other errors are there in the event log?

Anyhow, restart SQL Server with the -f option and ensure the the 2 settings above are sensible.


How to determine if users have the default password in Oracle 12c?

SYS and SYSTEM accounts are incorrectly listed in DBA_USERS_WITH_DEFPWD in 12c (Doc ID 2173962.1)

  SYS and SYSTEM have that flag set a database create time, and the flag
  doesn't clear until the first password change AFTER the database is
  created. So it may or may not be a default password, but it if it
  isn't default, it's one that hasn't been changed since the database
  was created and therefore should be changed.

If you do not have access to this, then:

https://mandysandhu.com/2017/10/20/oracle-12c-syssystem-accounts-are-incorrectly-listed-in-dba_users_with_defpwd/


MySQL innodb: if all the tuples are stored in the clustered Index then why do we call it index?

Let's approach it from two other perspectives...

The data needs to be stored somehow.  Perhaps unordered, perhaps based on a ROWNUM, or InnoDB's approach of ordering it according to the PRIMARY KEY.
In InnoDB the data structure and an INDEX structure are virtually identical -- both are BTrees of 16KB blocks with the 'items' ordered according to some KEY.  (I suggest that the documentation has confused you because it is hinting at the symmetry without enough background.)

Ordering the data according to one of the indexes (the PK) is actually a performance advantage for some queries.  In particular, if you want to look up a row by the PK, you can't get any faster.  If you want to scan a subset of the rows and do it in PK, order, you can do it any faster.

I assume you understand a BTree?  (See Wikipedia.)  InnoDB actually uses a B+Tree, very similar to a BTree, but adds links to make range scans more efficient.

Aside from nodes arranged in a tree, what is in the "leaf" nodes?

For the data / PRIMARY KEY, the leaf nodes contain all the columns of each row.  This is what is meant by "clustered".

For a secondary key (any index other than the PK), the leaf nodes contain a copy of the columns of the PK.  So, to look up a row via a secondary key, first you dig into its BTree to find the PK of the actual row, then you dig into the data to find the row.

One optimization is to use a "covering" index.  This is when all the columns needed anywhere in a SELECT are in a single index.  Guess what?  Now a Secondary index acts like a "table".  You don't need to bounce over to the data/PK BTree.

Each BTree for one table has its nodes stored in the .ibd file (assuming innodb_file_per_table=ON).  Each BTree is separate, aside from the PK linking.  (Think of ibdata1 and "tablespaces" are collections of tables thrown into a single disk file.)

You mentioned "pointer".  Mostly InnoDB gets away from pointers.  In the PK case, you arrive at the data; no need for a "pointer".  In the case of a secondary key, the copy of the PK serves as the "pointer".

As a Rule of Thumb, the nodes of either type of BTree have about 100 subnodes.  This implies that the BTree for a million-row table or index is about 3 levels deep.  (Think "log-base-100".)

Since fetching blocks is far more costly than rummaging through a block, counting disk hits is the main performance metric in an I/O-bound InnoDB table.

(MyISAM is radically different.)

(From comments:)

An InnoDB with just a PRIMARY KEY contains a single BTree.  The data is sorted according to the PK.  Lookups by PK are optimal.  That is, 'entire table and its data is in the cluster index structure'.

Inserting into such a table has multiple situations:

If you are 'randomly' inserting into the table, then the operation is technically Order(log n), such as 3 for a million-row table.  For all practical purposes, you can pretty much ignore drilling down the BTree.

A typical case is having id INT AUTO_INCREMENT PRIMARY KEY.  In this case, inserts are (normally) at the 'end' of the table.  (I do not happen to know if there is any optimization to avoid the repeated drill-down, but since this is a very common situation, I expect the developers have done a good job.)

Is an insert-heavy table inefficient?  Not really.  INSERTs have to go somewhere.  Granted, the typical table is read more than it is written, but still, "don't worry".

If you are doing thousands of INSERTs per second, there are various techniques to speed things up.  However, they focus on transactions, parsing, scattering, etc, not on the structure of the PRIMARY KEY.  One notable exception:  if the PK is a UUID, and the table is huge, you will be I/O-bound and probably have big problems.


How to get the 8.00.194 (!!!!!!) server to 8.00.2040:

You can download Service Pack 4 (8.00.2039) from here, which you will need to install before you can install the 8.00.2040 hotfix.

How to restore a 2000 .bak to a 2008 server:

You may need to clarify your actual requirements and clean up the question, but if you are trying to ultimately restore a SQL Server 2000 .bak file from server 2 to a SQL Server 2008 instance somewhere else, there is no reason to restore to server 1 first - just restore the .bak directly on the SQL Server 2008 instance (regardless of the source, 8.00.194 or 8.00.2040).

How to resolve the invalid tape format message:

You may have an invalid RESTORE statement, or you may have a corrupt backup. If the former, add the RESTORE statement to the question. If the latter, take a new backup (there are some tedious workarounds if you are restoring to 2005 or higher, but they include potentially allowing data loss). I don't believe this error message will be resolved simply by upgrading server 1. If @@VERSION were the issue (and it can be in some cases), you'd get a different error message than one about tape format.


Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

timestamp vs. date and time
If you are interested in the date part of your timestamp, it is cheaper to just cast it. The result is an actual date, while date_trunc() returns a timestamp! (You could cast that to date in turn, but don't bother.) Your index should be:
CREATE INDEX start_date_index ON reservations (cast(start AS date));

Use the more verbose standard notation cast(column AS type) in the index definition. The Postgres shorthand would require an additional set of parentheses in this context: (column::type). You can still use either notation in queries to match:
SELECT id, start
FROM   reservations
WHERE  start::date = '2014-03-14';

See:

How to create an index on an integer json property in postgres

You can do similar things with start::time if needed.
Either way, I would certainly not split the date and time part. A timestamp is almost always the superior design.
Index tuning
Depending on your actual use cases, you could make that a multi-column index including the id:
CREATE INDEX start_id_date_index ON reservations (cast(start AS date), id);

That would serve the above query perfectly, possibly even with an index-only scan. And since you are now using an actual date in the index (4 bytes), an additional integer column (also 4 bytes) is just perfect for performance. See:

Is a composite index also good for queries on the first field?

What's more, you can also just use a plain index on the timestamp column:
CREATE INDEX start_start_index ON reservations (start);

Just make sure your WHERE conditions are "sargable". Like:
SELECT id, start
FROM   reservations
WHERE  start >= '2014-03-14 0:0'::timestamp
WHERE  start <  '2014-03-15 0:0'::timestamp;

This will use the index.
The best solution depends on the sum of all your requirements. Also, Postgres can use your original, less optimal index nonetheless. How many rows are there in your table? With only few, a sequential scan is always faster and you won't see an index scan.


MySQL output file location - accessing server files

If this is a one-time thing, you can do the following to export the result into csv file using MySQL workbench:

SELECT * FROM mytable

then the result will appear in the result table (area) all you need to do use export button in that area:

Otherwise, if you want to create output file you can do it by using a shared folder:

if its over a network //my_pc/folder/test.csv that both machines have read/write access
and if its under the same server use for example d:/test.csv

and save the file into any location you want.


Load New Foreign Key in Facts Table, When Update Dimension with SCD Type 2

No, you wouldn't update the fact table.

The fact record is tied to the version of the customer which existed between 2018-04-02 11:52:47.040 and 2018-04-02 22:29:05.243 in which they had a 1.99 tppessoa

If they generated a 3.1 tppessoa  at 2018-04-03 00:00:00.000 then your fact table would look something like

idFact  idcipessoa  tppessoa
------- ----------- --------
1       88393       1.99
2       169852      3.1

Then you can see the activity for this customer as they have changed over time.

If you wanted to have the fact table's idcipessoa updated as the dimension changes, that's not a type dimension.


Here is your basic problem: You most likely have many users in mysql.user other than root@localhost with the SUPER privilege. Please note the MySQL Documentation in your question:

  mysqld actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.5.30, “SHOW PROCESSLIST Syntax”.

You need to run this

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) FROM mysql.user
WHERE super_priv='Y' and user<>'root';

You need to take away the SUPER privilege from those users.

The fastest way to revoke the SUPER privilege from those users is to login to mysql as root@localhost and execute the following:

UPDATE mysql.user SET super_priv='N' WHERE
WHERE super_priv='Y' and user<>'root';
FLUSH PRIVILEGES;

GIVE IT A TRY !!!

NOTE #1 : I have discussed this before on Jul 06, 2012 in How to resolve too many connections and fatal error in mysql running on vps

NOTE #2 : If you have other mysql users doing database backups and cronjobs, you need to make sure those users have all needs privileges for their respective operations after removing the SUPER privilege.

NOTE #3 : If you do not use other users to connect to mysql, you need to create extra users that do no have all privileges, but still have enough privileges for needed operations.


PostgreSQL stored procedure to return rows or empty set

You can do something along the following lines:

test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer)
RETURNS SETOF integer AS
$body$
BEGIN
    CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS
    SELECT a
    FROM generate_series(1, cnt) t(a);

    IF (SELECT count(1) FROM tmp_container) > 5
    THEN
        RETURN QUERY SELECT a FROM tmp_container;
    END IF;
END;
$body$
LANGUAGE plpgsql;

test=> SELECT * FROM temptabl(4);
 temptabl
----------
(0 rows)

test=> SELECT * FROM temptabl(6);
 temptabl
----------
        1
        2
        3
        4
        5
        6
(6 rows)

This way you have to perform your original query only once.  All other statements work on the temporary table.


How can I import latitude and longitude data into a database using Mysql Workbench?

Well, I couldn't figure out how to do it in Mysql Workbench, but I have a workaround.

Use:

mysqldump -u mysqlUser -p DatabaseName TableName > table_data

To get data out of the table you want to use, then use:

mysql -u mysqlUser -p DatabaseName TableName < table_data

To get the data into the desired table.  This will retain the correct values.


Regarding configuration of kernel parameter

The parameters are all prefixed according to which part of the system  they affect. fs = filesystem, net = networking etc.

kernel.shmmni = 4096 This sets the system wide maximum number of shared memory segments. At a high level, a share memory segment is a "piece" of memory that multiple processes can all attatch to at the same time. You can view all current shared memory segments on a Linux box using the command ipcs -m.

kernel.sem = 250 32000 100 128 configures semaphores. A semaphore is best described as:

  A value in a designated place in operating system (or kernel) storage
  that each process can check and then change. Depending on the value
  that is found, the process can use the resource or will find that it
  is already in use and must wait for some period before trying again.

This link
 explains how semaphores are used & what the 4 different values actually set.

fs.file-max = 6815744 sets the maximum number of files that can be open systemwide at any given point in time. Once the limit has been reached, trying to open another file will fail.

fs.aio-max-nr = 1048576 is (simplified for the sake of this answer) the maximum number of concurrent file read/write operations that can be in progress at any point in time.

net.ipv4.ip_local_port_range = 9000 65500 is the IP port range used for outgoing connections. So when a process opens a connection to the outside world, it'll use a port between 9000 and 65500. This explains what ports are.

The following are networking parameters, and basically control how much network data can be in flight at any point in time.

net.core.rmem_default = 262144 is the default OS receive buffer size for all types of connections.

net.core.rmem_max = 4194304 is the maximum OS receive buffer size for all types of connections.

net.core.wmem_default = 262144 is the default OS send buffer size for all types of connections.

net.core.wmem_max = 1048576 is the maximum OS send buffer size for all types of connections.


Stored procedures vs inline SQL in a Job step

does it make a difference (performance or otherwise) in SQL Server 2014, to run a job step that calls a stored procedure or one that executes inline sql?

In your case, since you are just doing reporting, it won't make much difference either using inline SQL or a stored procedure.

Aaron Bertrand provides a really good answer to a related question:

Stored procedures vs. inline SQL


MySQL Error: Authentication plugin 'caching_sha2_password' cannot be loaded

So, I found the running process of mysqld by sudo lsof -i tcp:3306 then I killed it using sudo kill -9 <PID>.
After this I tried $ mysql_secure_installation again but ran into a new error Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)
After trying a few fixes for getting mysql.sock to work, I started the MySQL server with
$ sudo mysql.server start
then went ahead with $ mysql_secure_installation to set password for the root user.

This worked for me finally.

Note: Homebrew asks you to first do mysql_secure_installation before starting the MySQL server for the first time but that just made me run into endless loop of errors.


SQL - Find Results with MAXimum for given colum


Number of locks exceeded when adding foreign key constraint

According to the MySQL Performance Blog

  Lets start with a bit of background – in Innodb row level locks are
  implemented by having special lock table, located in the buffer pool
  where small record allocated for each hash and for each row locked on
  that page bit can be set. This in theory can give overhead as low as
  few bits per row, so lets see how it looks in practice

The same reference brings this out...

  So we locked over 100K rows using about 44KB. This is still quite
  efficient using less than 4 bits per locked row.

  In practice this means memory consumption by row level locks should not be
  the problem even for rather large databases – even billion of locked rows should
  take half GB of memory, which is small fraction of memory used on
  serious systems. Furtermore you would unlikely need or want to lock
  every row in your table/database which makes it even smaller problem.

Evidently, all 17 million rows need to be locked. Based on the link from mysqlperformanceblog.com:

100,000 rows takes 44 KB
17,000,000 rows should take 7480 KB, just over 7 MB

I can easily see an unconfigured innodb_buffer_pool_size (Default is 128M in MySQL 5.5, and 8M before MySQL 5.5) causing a problem.

OK Let's get back to your questions:

  Why exactly are there so many locks, i.e. what is being locked in this
  query?

Every row in an InnoDB table must be locked. This makes the data available via MVCC, ACID Compliance, and Transaction Isolation.

  Why didn't locking the table solve the problem? Do I need to do the
  lock in a different way or lock something else as well?

Same as Answer 1

  If the only solution is to increase the innodb_buffer_pool_size, how
  large does it need to be for this query?

There must be enough RAM to not only hold your working set of InnoDB data and index pages, but also small amount of overhead (44KB per 100,000 rows) for table locks.


Is there a good reason to put the company name at the beginning of the table names?

As you say in the comments

  I work for a different company, yes, that is building the system for the bank

I suspect this is a requirement of the company you work for and/or the expectations of the purchasing bank.

When the build is complete mycorp is going to be all over in the code.  If you wanted to sell the same product to DifferentCorp it would be a lot of work to remove reference to mycorp.  I assume that by adding mycorp they bank paying you to custom build a product for them, will have some feeling that you will not be going and selling to their competition, as soon as you get it working.

See related

Is adding the ‘tbl’ prefix to table names really a problem?
What are some best practices for using schemas in SQL Server?


Reference:

Diagnosing and Resolving Latch Contention on SQL Server by Microsoft Corporation
Buffer Latch Class by SQLSkills.com

Some latch contention is to be expected as a normal part of the operation of the SQL Server engine. It is inevitable that multiple concurrent latch requests of varying compatibility will occur on a high concurrency system. SQL Server enforces latch compatibility by requiring the incompatible latch requests to wait in a queue until outstanding latch requests are completed.

Latches are lightweight synchronization primitives that are used by the SQL Server engine to guarantee consistency of in-memory structures including; index, data pages and internal structures such as non-leaf pages in a B-Tree. SQL Server uses buffer latches to protect pages in the buffer pool and I/O latches to protect pages not yet loaded into the buffer pool.

Whenever data is written to or read from a page in the SQL Server buffer pool a worker thread must first acquire a buffer latch for the page. There are various buffer latch types available for accessing pages in the buffer pool including exclusive latch (PAGELATCH_EX) and shared latch (PAGELATCH_SH). When SQL Server attempts to access a page which is not already present in the buffer pool, an asynchronous I/O is posted to load the page into the buffer pool. If SQL Server
needs to wait for the I/O subsystem to respond it will wait on an exclusive (PAGEIOLATCH_EX) or shared (PAGEIOLATCH_SH) I/O latch depending on the type of request; this is done to prevent another worker thread from loading the same page into the buffer pool with an incompatible latch.

Latches are also used to protect access to internal memory structures other
than buffer pool pages; these are known as Non-Buffer latches.

My recommendation to you will be instead of focusing on the number of buffer class latch, start from wait types.  Especially Pagelatch_XX and Pageiolatch_xx type in this case if those are showing up as the top waits in your case. You will find details about these wait types here by sqlskills.com

Quote from Paul Randal:

  I have not seen this latch be a noticeable contention point.

  You should filter this wait type out of results from
  sys.dm_os_latch_stats otherwise it will overwhelm the other latch
  information.


There's an error in your command. If you remove the [%] at the end, you'll get the correct output.
Current:
EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'
Should be:
EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks]'
As a side note: if you want to get the query plan, and associated locks (since you've got them in your column list), you'll need to use the @get_plans and @get_locks parameters in combination with the output list.


Database Relational Design for Multiple Categories

I second @FrustratedWithFormsDesigner's suggestions for a unique constraint.  See http://office.microsoft.com/en-us/access-help/create-a-constraint-adp-HP003088257.aspx for how to do this in MS Access.

What a Unique Constraint does is state that no two rows can have duplicate data on some portion of the row.  So presumably you want each patient to be in a study only once, so you'd want a unique constraint on PatientsInStudy(participantID, study).

This way each individual can only be given one study id.  Similarly in that design your PatientsInStudy table would have a primary key spanning all three fields, and a second unique constraint on (StudyID, study_code).  This way even if two studies end up with similar semantics or overlapping spaces, the key cannot be reused in the same study.

Otherwise, good.


Model a family tree database in Access 2007

Start using a ERD tool, or use paper to draw the relationships, those you have already written.

For example, "A tribe has many clans"  and "one clan, which belongs to one tribe" means there's a 1:N relationship between Tribe and Clan, so there should be a tribe_id in Clan table (FK to Tribe).

Another issue would be the "a family has many Sims" and "a Sim belongs to at least 2 families". You'll have to use a Sim_Family joint table for the many-to-many association between the Sim and Family but the "at least 2" is hard (or impossible?) to enforce with DRI alone.

After you have drawn all relationships between tables (entities), check if there are circular paths in the Foreign Keys. It's hard to deal with that and there's usually a way to avoid it by minor alterations in the design.


Delete query does not appear to open space on the SQL Server

Deleting rows from a table does not automatically free up space, unless you happen to delete a large chunk of rows that are stored on the same pages, and every row on each such page was marked for deletion. You would need to rebuild the indexes in order to reclaim the space.
Even if you rebuild the indexes, SQL Server doesn't reduce the size of the data file(s) just because you've removed some data inside the file. The assumption is typically that if the database got that big once, it will get that big again, so why shrink the file only to grow it again later? Both shrink and growth operations are extremely disruptive to normal activity. Please read this thread in full to understand why you want to be very careful about freeing up space on your drive (even on a cloud-hosted server):

Why Does the Transaction Log Keep Growing or Run Out of Space?

Also see:

Why you should not shrink your data files


Trouble with triggers and insert .. on duplicate key update

This question came back on my radar after a recent comment was posted.

This issue at the time turned out to be this reporting table that was meant to be maintained by triggers was on a slave only.

The insert on duplicate key statements were getting flagged as not safe for statement based replication and getting pushed through in RBR (the stream was in mixed mode).

RBR events do not fire triggers.


is it problematic to cluster across two datacenters w/aag?

PASQLDBA it depends really on how much data is being written to the database and the link between these two DC's and also how far out of sync you can tolerate the two servers keeping your RTO/RPO in mind.

This is by no means any indication of what your milage may be but we have the following setup running quite well.

2 Node Cluster, 1 node in each DC
DC's are a good 20 - 30kms apart
Fiber link between the DC's
Local SAN disks
Node and File Share Majority
Availability Groups setup, auto failover with synchronous commit

This houses multiple AG's with ~50 databases with no noticeable performance hit over the single instance it was on before.

Hope this helps.


If the maximum current value of an ID generated by a sequence is N, does that guarantee that all future rows will have index > N?

If the database is running on a RAC cluster, each node in the cluster would have a separate sequence cache.  That would cause the values returned to be out of order unless the sequence was created with ORDER specified (i.e. CREATE SEQUENCE seq_foo START WITH 1 ORDER).
If the database is running on a standalone instance, sequence numbers will always be generated in order.  If you're depending on this behavior for functional reasons, I'd be pedantic and specify ORDER when creating the sequence even on a standalone database.  But it isn't necessary to do so.
From the documentation

ORDER is necessary only to guarantee ordered generation if you are
using Oracle Real Application Clusters. If you are using exclusive
mode, then sequence numbers are always generated in order.

The other potential issue would be if the sequence reached the MAXVALUE and was set to CYCLE by starting over at the minimum value.  NOMAXVALUE and NOCYCLE are the defaults, though, so it would be pretty unusual that someone using a sequence like this would create the sequence in that way.


How to create DBA user from oracle APEX 11gR2 XE

Here is an article from dba-oracle which talk about this same problem. This might solve the issue you are having.


Of course, SQL*Plus does not autoformat your output, but you can do it:

SQL> col tname format a30
SQL> col tabtype format a7
SQL> set numwidh 10

SQL> select * From tab where rownum = 1;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ACCESS$                        TABLE


Probably a clustered index over the #xMobiles temp table can help you in the last Join

CREATE CLUSTERED INDEX IDX_xMobiles_VehicleKey ON #xMobiles
(
    VehicleKey ASC,
)

The best option is to create the index after populating the table.