Postgres: How is SET NOT NULL "more efficient" than CHECK constraint In PostgreSQL docs for Constraints, it says

  A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient.

I'm wondering

What exactly does it mean by "more efficient"?
What are the detriments of using CHECK (column_name IS NOT NULL) instead of SET NOT NULL?

I want to be able add a NOT VALID CHECK constraint and validate it separately (so the AccessExclusiveLock is only held for a short period of time for the adding of the constraint and then a ShareUpdateExclusiveLock is held for the longer validation step):

ALTER TABLE table_name
  ADD CONSTRAINT column_constraint
  CHECK (column_name IS NOT NULL)
  NOT VALID;
ALTER TABLE table_name
  VALIDATE CONSTRAINT column_constraint;

Instead of:

ALTER TABLE table_name
  ALTER COLUMN column_name
  SET NOT NULL;

My wild guess: "more efficient" means "less time is required to perform the check" (time advantage). It may also mean "less memory is required to perform the check" (space advantage). It might also mean "has less side effects" (such as not locking something or locking it for shorter periods of time)... but I don't have a way to know or check that "extra advantage".

I cannot think of an easy way to check for a possible space advantage (which, I guess, is not that important when memory nowadays is cheap). On the other hand, it's not that difficult to check for the possible time advantage: just create two tables which are the same, with the only exception of the constraint. Insert a sufficiently large number of rows, repeat a few times, and check the timings.

This is the table setup:

CREATE TABLE t1
(
   id serial PRIMARY KEY,
   value integer NOT NULL
) ;

CREATE TABLE t2
(
  id serial PRIMARY KEY,
  value integer
) ;

ALTER TABLE t2
  ADD CONSTRAINT explicit_check_not_null
  CHECK (value IS NOT NULL);

This is an extra table, used for storing timings:

CREATE TABLE timings
(
   test_number integer,
   table_tested integer /* 1 or 2 */,
   start_time timestamp without time zone,
   end_time timestamp without time zone,
   PRIMARY KEY(test_number, table_tested)
) ;

And this is the test performed, using pgAdmin III, and the pgScript feature.

declare @trial_number;
set @trial_number = 0;

BEGIN TRANSACTION;
while @trial_number <= 100
begin
    -- TEST FOR TABLE t1
    -- Insert start time
    INSERT INTO timings(test_number, table_tested, start_time)
    VALUES (@trial_number, 1, clock_timestamp());

    -- Do the trial
    INSERT INTO t1(value)
    SELECT 1.0
      FROM generate_series(1, 200000) ;

    -- Insert end time
    UPDATE timings
       SET end_time=clock_timestamp()
     WHERE test_number=@trial_number and table_tested = 1;

    -- TEST FOR TABLE t2
    -- Insert start time
    INSERT INTO timings(test_number, table_tested, start_time)
    VALUES (@trial_number, 2, clock_timestamp());

        -- Do the trial
    INSERT INTO t2(value)
    SELECT 1.0
    FROM generate_series(1, 200000) ;

    -- Insert end time
    UPDATE timings
       SET end_time=clock_timestamp()
     WHERE test_number=@trial_number and table_tested = 2;

    -- Increase loop counter
    set @trial_number = @trial_number + 1;
end
COMMIT TRANSACTION;

The result is summarised in the following query:

SELECT
    table_tested,
    sum(delta_time),
    avg(delta_time),
    min(delta_time),
    max(delta_time),
    stddev_pop(delta_time)
FROM
    (
    SELECT
        table_tested, extract(epoch from (end_time - start_time)) AS delta_time
    FROM
        timings
    ) AS delta_times
GROUP BY
    table_tested
ORDER BY
    table_tested ;

With the following results:

table_tested | sum     | min   | max   | avg   | stddev_pop
-------------+---------+-------+-------+-------+-----------
           1 | 176.740 | 1.592 | 2.280 | 1.767 | 0.08913
           2 | 177.548 | 1.593 | 2.289 | 1.775 | 0.09159

A graph of the values shows an important variability:

So, in practice, the CHECK(column IS NOT NULL) is very slightly slower (by a 0.5%). However, this small difference can be due to any random reason, provided that the variability of the timings is far larger than that. So, it's not statistically significant.

From a practical point of view, I would very much ignore the "more efficient" NOT NULL, because I don't really see it's significant; whereas I think that the absence of an AccessExclusiveLock is an advantage.


Importing tablespace files - Length mismatch error for DATETIME field We are currently trying to import some old historic data tables. Unfortunately, the data is in exported tablespace files (.ibd and .cfg) - there are no dump files available and the server they came from no longer exists.

There are separate tables for each month of data, but they should all have exactly the same format. We have successfully imported the more recent files (with e.g. ALTER TABLE sqlt_data_1_2017_01 IMPORT TABLESPACE), but all the remaining idb files are failing with the following error:

SQL Error [1808] [HY000]: Schema mismatch (Column datevalue length mismatch.)

The datevalue column is a DATETIME.

It's possible that different versions of MySQL were used for creating the various tables and also for exporting the table spaces, but we are 99% sure that the starting version was 5.7.

Some of the tables have .frm files, so we compared a few with mysqlfrm --vvv -diagnostic and noticed that some of the datevalue columns have field_length 26 and some have field_length 19. Also, the stringvalue is VARCHAR(255) but the field_length is 762, although I suspect this is because it's in UTF8.

All the tables we can import have MYSQL_VERSION_ID 50722 and datevalue field_length 19
All the tables we can't import have MYSQL_VERSION_ID 50714 and datevalue field_length 26

Did something change with the DATETIME storage format at some point? What can we do to import these tables?

Thanks.

In all honesty, I know that DATETIME did change from 5.5 to 5.6. How do I know this ???

I ran into situation where I was setting up replication from MySQL 5.5 to 5.6 and replication would break because 5.6  wasexpecting microseconds in the relay log for DATETIME, tried to unpack the DATETIME and found out it was not the right length as 5.5 did not have microseconds (This was originally discovered by my manager then). The workaround was to switch to STATEMENT replication.

Here you are mentioning 5.7 to 5.7. I don't see why this should happen, but I have a theory.

If the source data on the running MySQL 5.7.14 instance came from an older version of MySQL as a result of doing inplace upgrades, then I can see this happening if MySQL 5.7.22 stores DATETIME different between minor 5.7 releases.

According to MySQL Documentation on Date and Time Data Type Representation:

    DATETIME

    Before MySQL 5.6.4 : 8 bytes, little endian
    After MySQL 5.6.4 :5 bytes + fractional-seconds storage, big endian

So, there was change in data storage format starting in MySQL 5.6.4 in terms of looking at 5 bytes + an unknown number of bytes for microseconds (WEIRD) !!!

I am just speculating at this point, but if the internals for DATETIME changed for 5.7 and it was not published, SHAME ON YOUR, ORACLE !!!. I am just kidding. What you should do from here is research if this bug was reported already. If not, you must report this one and let Oracle have some fun with it (They have missed a patch or overlooked microseconds somewhere).

You may have to get a mysqldump (Logical Dump) of the data and load it into target DB.

I wish you well in this adventure !!!


aggregate on join latest value This is a problem I often get stuck in and I hope there is a solution.

Let's build an example:

Table products: product_id, product_name
Table Prices: product_id, valid_since, Price

Lets say I would like to know the sum of all prices of all products (seems senseless, but it is just for illustrating the problem in an easy way)

Now you can imagine, prices will change with time, so the only price that matters is the price with the highest value of Prices.valid_since of course

SELECT SUM(Prices.price)
FROM products
INNER JOIN Prices
ON products.product_id=Prices.product_id

will result in something absolutely senseless, so how could it be possible?

As the way your schema current sits, you'll need two parts; the main select statement and (since you are using MySQL, which doesn't support common table expressions), a view in the where clause.

The view, priveView:

SELECT product_id
, MAX (valid_since) [validSince]
FROM prices
GROUP BY product_id

The select statement:

SELECT SUM (P.Prices)
FROM Prices AS P
INNER JOIN priceView as PV ON P.product_id = PV.Product_Id AND PV.valid_since = P.valid_since

If possible I would change the schema of your prices tables so that it also includes a column of when the price starts in additional when it ends/changes.  Whatever the current price happens to be has it's "price_end" value set to null.  You'd then be able to avoid the need for an intermediate view that gets the sub-section of rows that apply.


"Mysql has gone away" heavy multiple insert I am stuck because Mysql server will go away after executing this part of code this function will update the database with the new RSS feeds which are stored in ToUpdate but Mysql stops working. Every cell of the array contains a long string for description,for example at the size of 3000 words or more and may be that's the problem. the maximum size of $this->array_counter is less than 30 and is set before in another function. after running the function after 3 to 6 inserts mysql says " mysql has gone away". Is the problem related to connection limit or bandwidth limit or fast inserting?

function update_db()
{

    for($i = 0;$i < $this->array_counter ; $i++ ){

        $url=$this->toUpdate[$i]['url'];

         $t=addslashes( $this->toUpdate[$i]['title'] );
         $t=$this->convert_line_breaks($t,"
");

         $d=addslashes( $this->toUpdate[$i]['desc'] );
         $d= $this->convert_line_breaks($d,"
");

         $date= $this->toUpdate[$i]['date'];

    //echo "<fieldset>".$url."
".$t."
".$d."</fieldset>";

        $r=mysql_query("INSERT INTO feed_items(items_id,items_title,items_url,items_description,items_date,feed_id,like_hits) VALUES('','$t','$u','$d','$date','$this->Feed_id','0') ",$this->dblink)or die(mysql_error());

    }//end of foreach
}

I usually blame the size of your MySQL Packet

I have written past posts on this

Sep 01, 2011 : MySQL server has gone away obstructing import of large dumps
Sep 25, 2011 : Fixing MySQL errors recorded in error logs
Jun 12, 2012 : MySQL Error Reading Communication Packets

Just raise your MySQL Packet size (max_allowed_packet) in /etc/my.cnf

[mysqld]
max_allowed_packet=256M

and restart mysql


Database mirroring is limited to an original database Database mirroring is limited to an original database and how many mirror copies?

Yes, database mirroring works from one principle server to one mirror server. Only one mirror copy can be used. For multiple readable replicas, consider AlwaysOn Availability Groups, introduced in SQL Server 2012 Enterprise Edition.


Common entities with differing attributes across multiple databases We have two systems each with their own database. Each system has Parties (Customers and Vendors) and each of these can have one or more Sites (addresses) for different functions (delivery, holding, ...).  Some of the Customers and Vendors appear in both systems. Both systems need to keep track of customer and vendor Licences. A Licence belongs to a Party and applies to a specific Site. So it would make sense to hold the common data (Parties, Sites, Licences) in a shared database to simplify maintenance etc.
However, each system has different attributes for Customer, Vendors and Sites that make sense only in that system.
For example system A holds routing information against each customer that is not relevant to system B. Further, the routing codes are foreign keys to a Routes table that would only exist in A. So it would not make sense to store a Customer's routing info in the common database. (There are many other examples.) So it looks like I will need to maintain entities in all three databases (A, B and Common), and keep the entities in sync - which will kind of cancel out the gains I hoped to make.
So my question is: has anyone come up with a satisfactory way to deal with this kind of thing? Or should I just combine the two systems?

Yes, you absolutely should not store the data across more than one system if it can be avoided.  But welcome to the nightmare many organizations have created for themselves.
Without knowing the exact circumstances of how the applications are set up, it'll be hard to give you a definitive answer, but hopefully we can get you in the right direction.
Recommendations:

This will be incredibly stressful experience, so make sure you have an exercise regimen or therapist lined up.
Now is a great time to make sure your new system for storing customer data complies with GDPR and other data privacy standards!
Store the customer data in a common database, but force the other systems to use the primary keys of that system.  You will either need to update each system's data with the new key (hard at first) or create a mapping from the source system's key to the new key (easy at first, harder as time goes on). It isn't possible to maintain strict PK-FK relations between different databases, so you'll need to ensure procedures exist to keep things in sync if records are merged, deleted, etc.
For attributes not stored by both systems (but not required to be stored in either system), you can create a table in 1:0-1 relation with the master table and store the optional column(s) there.  Depending on the exact circumstances you may need to create subtypes and handle the optional columns to ensure the proper columns are stored for each subtype.

Trying to maintain anything other than the proper keys across each system will lead to far more headaches than you have today.
Other things:

Know your industry's regulatory/reporting requirements and ensure any changes you make won't interfere.
If you don't already standardize information like addresses, you'll want to do so now to make matching easier.
People are really bad at typing consistently, so get familiar with string comparison functions (Levenshtein Distance and Regex).
If you are in a strict environment, or don't have too many records that are close matches, may make sense to have a human review any possible matches before merging records.


Storing Live Timeseries Data in SQL Server Azure Database I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors.
Data Source:- 5-minute data is obtained via an API.
Current table structure: -
 Timestamp | ComponentId | Parameter1 | Parameter2 | Parameter3

Each sensor has a unique ComponentId. I have a non-clustered index on Timestamp and ComponentId to eliminate duplicates and also a clustered column store index on the whole table (It compresses data and saves space. Also gives a performance boost for aggregate queries). A python script is used to fetch the data via the API and pyodbc library is used to push this data to the table. The script runs every 10 minutes and inserts data into the table.
Some queries like fetching data for a particular component for just one day seem to take 5 seconds. Is this normal?
QUERY:-
SELECT Timestamp,ComponentId,ParameterId FROM TABLE WHERE ComponentId=1 AND Timestamp BETWEEN '2021-05-01' AND '2021-05-02'

IO Stats: Table 'RawData'. Scan count 2, logical reads 10164, physical reads 3, page server reads 0, read-ahead reads 10146, page server read-ahead reads 0, lob logical reads 2766, lob physical reads 12, lob page server reads 0, lob read-ahead reads 4877, lob page server read-ahead reads 0.
Table 'RawData'. Segment reads 2, segment skipped 10.
Is this way of fetching/pushing data fine? Please let me know if it can be improved and if there are any better methods to do the same.

With that design it is expected that the query requires a full scan of the columnstore.  That should be very fast at that scale, but the IO stats show that you're reading from disk.  If you look at the wait stats for the actual execution plan you should see that it's more IO waits than CPU utilization.
You could scale up the database to get more cache memory, or perhaps partition the columnstore either by componentId or timestamp.  Whenever you partition a columnstore you want to ensure that partitions have at least 1M rows.


Using Echo statement writes Echo is ON in the text file Following code is used for write a number in the text file but instead it is writing "Echo is ON" into the text file.

SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck
  Print @CMD

EXEC xp_cmdshell @CMD
  ,NO_OUTPUT

Print @CMD  gives  "ECHO 1>C:\Pharmsuite\Vercheck.txt"

Here instead of 1, the query is writing "Echo is ON".

This command: ECHO 1>C:\Pharmsuite\Vercheck.txt

Does not do what you think it should do because 1> has a special meaning.

This problem can be fixed by adding a space before >:

ECHO 1 >C:\Pharmsuite\Vercheck.txt

Or in your case:

SET @CMD = 'ECHO '+@Ver+' >'+@Vercheck

It also works with this command (making sure there is a space before ECHO [thanks to Andriy M]):

SET @CMD = '>'+@Vercheck+' ECHO '+@Ver

You should look at:

Technet Using command redirection operators
SS64 Redirection:

  Numeric handles:

  STDIN  = 0  Keyboard input
  STDOUT = 1  Text output
  STDERR = 2  Error text output
  UNDEFINED = 3-9

  command 2> filename       Redirect any error message into a file
     command 2>> filename      Append any error message into a file
    (command)2> filename       Redirect any CMD.exe error into a file

Anyhow, you should avoid using xp_cmdshell. If you are trying to output something, Powershell or SQL Server command line commands are better options.

Using Powershell and Invoke-Sqlcmd:

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSQLCmd.sql" | Out-File -filePath "C:\MyFolder\TestSQLCmd.rpt"

You obviously have to adapt it to your own requierements and you must also look at Daniel Hutmacher's answer using osql.


Is it possible to enter a missing sequence number into another table, on exception in a Trigger Below is the code which I have written in the exception part of my before insert trigger.

CREATE OR REPLACE TRIGGER ORGANIZATIONS_TAB_BI_TRG
    BEFORE INSERT ON ORGANIZATIONS_TAB
    FOR EACH ROW
DECLARE
    -- variables
    lv_CurrentUserID VARCHAR2(30):= NULL;
BEGIN
   -- Getting the current session user id
    SELECT USER
      INTO lv_CurrentUserID
      FROM DUAL;
    IF :NEW.ORG_ID IS NULL THEN
      SELECT TO_NUMBER(TO_CHAR(SYSDATE,'RRRR')||LPAD(ORGANIZATIONS_SEQ.NEXTVAL,6,0) ORG_ID
        INTO :NEW.ORG_ID
        FROM DUAL;
    END IF;
EXCEPTION
  WHEN OTHERS THEN
        SEQ_AUDIT_PKG.InsertMissingSequence('ORGANIZATIONS_SEQ',
                      ORGANIZATIONS_SEQ.CURRVAL,
                          SYSDATE,
                          SYSDATE,
                          lv_CurrentUserID,
                          'Sequence Missed due to :'||SQLERRM);
END;

Can we do this for storing the missing sequence numbers to keep a track?
The SEQ_AUDIT_PKG.InsertMissingSequence is as follows:

PROCEDURE InsertMissingSequence(P_SEQ_NAME IN MISSED_SEQ_LOG.SEQUENCE_NAME%TYPE,
                                             P_SEQ_NUMBER IN MISSED_SEQ_LOG.SEQUENCE_NUM%TYPE,
                                             P_SEQ_DT IN MISSED_SEQ_LOG.SEQUENCE_DT%TYPE,
                                             P_CREATION_DT IN MISSED_SEQ_LOG.CREATION_DT%TYPE,
                                             P_CREATED_UID IN MISSED_SEQ_LOG.CREATED_UID%TYPE,
                                             P_REMARKS IN MISSED_SEQ_LOG.REMARKS%TYPE) IS
  BEGIN
    -- Inserting records into the Missing Sequence Log
    INSERT INTO MISSED_SEQ_LOG (SEQUENCE_NAME,SEQUENCE_NUM,SEQUENCE_DT,CREATION_DT,CREATED_UID,REMARKS)
           VALUES(P_SEQ_NAME,P_SEQ_NUMBER,P_SEQ_DT,P_CREATION_DT,P_CREATED_UID,P_REMARKS);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END InsertMissingSequence;

EXCEPTION
  WHEN OTHERS THEN
        SEQ_AUDIT_PKG.Inse...

is a bad idea, because 'when others' could be anything. eg, you might have a datafile corruption, or an out of memory error.  You dont want to be doing a sequence audit in that respect.  Similarly, when that primary trigger fails, you want to be sending that back to the calling program, not telling them that "Hey, all is well" (which is when WHEN-OTHERS typically will do).

You would need your SEQ_AUDIT_PKG.InsertMissingSequence to be an autonomous transaction so that you could log the information outside the transaction scope of the trigger.

But having said all that ..... just dont do it :-)

Sequences have gaps...period.  You can

nocache them
pin them in the shared pool
carefully police shutdown abort commands
capture unused ones with an autonomous transaction

and you'll still eventually get some missing and any promises you've made about no gaps become moot.  And the complexity cost toward meeting this goal is just not worth it.


Why some triggers run exactly at the same time? I wrote a simple audit with triggers. when a trigger called, the data inside the trigger (inserted and deleted rows) are sent through broker to a procedure to write them in the Master Audit Table. here is the trigger:

CREATE TRIGGER [dbo].[trg_personAudit]
ON [dbo].[person]
 AFTER INSERT, UPDATE, DELETE
AS
    DECLARE @auditBody XML = '<AuditMsg/>'
    DECLARE @oldAuditBody XML = '<AuditMsg/>'
    DECLARE @newAuditBody XML = '<AuditMsg/>'
    DECLARE @DMLType CHAR(1)
    DECLARE @tableName sysname
    DECLARE @guid varchar(100) = NULL;

    BEGIN
        DECLARE @change_date datetime2(7) = sysdatetime();
        SET @DMLType = 'I'
        IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
            SET @DMLType = 'U';
        ELSE IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
            SET @DMLType = 'I';
        ELSE
            SET @DMLType = 'D';

        declare @userid nvarchar(20)

        select @userid=splitdata from dbo.fnSplitString(APP_NAME(),'@') where splitdata like 'userid=%'
        select top 1 @userid=splitdata from dbo.fnSplitString(@userid,'=') ORDER BY splitdata asc
        select @guid=splitdata from dbo.fnSplitString(APP_NAME(),'@') where splitdata like 'gid=%'
        select top 1 @guid=splitdata from dbo.fnSplitString(@guid,'=') ORDER BY splitdata asc

        IF (@userid IS NULL OR @userid = '')
            SELECT @userid = '-1'

        SELECT  @oldAuditBody = (SELECT * FROM deleted  FOR XML AUTO, BINARY BASE64, ELEMENTS)
        SELECT  @newAuditBody = (SELECT * FROM inserted  FOR XML AUTO, BINARY BASE64, ELEMENTS)

        SET @tableName = 'person'

        print 'generated guid is: ' + @guid;
        SELECT @auditBody = (select
                                DB_NAME() as SourceDb,
                                @tableName as SourceTable,
                                @userid as UserId,
                                @DMLType as DMLType,
                                @oldAuditBody as OldData,
                                @newAuditBody as NewData,
                                @guid as gid,
                                @change_date as ChangeDate
                            for xml path('AuditMsg'))

        EXEC dbo.usp_SendAuditData @auditBody
    END

GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'DELETE'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'INSERT'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'UPDATE'

as you can see, I get the current time stamp in this line:

DECLARE @change_date datetime2(7) = sysdatetime();

Suppose there is an insert and after that we have an update: sometimes this two triggers run exactly at the same time. even we have datetime2(7) but the times of the both rows are exactly the same. I can't find any solution to overcome this situation. Why the times are exactly equal?

when you use the MERGE statement it triggers all three types of triggers (INSERT / UPDATE / DELETE), but I'm not sure, if it calls a combined trigger three times too, but this should be easy to find out...


Mysql 5.6.17 drop large table locks the server for minutes We run Mysql 5.6.17.

Dropping a large InnoDB table 10GB caused the whole server to lock for about 40 seconds. We have 55GB innodb buffer pool size. I read bunch of old bugs that could be the source of this problem, but as far as I can see they should be fixed: (http://bugs.mysql.com/bug.php?id=64284). Also the same lock happened when we tried alter table with LOCK=NONE. The alter was going well and at the end when it was switching the table the whole server locked for 10 minutes This was very large table. Also it was reproducable on our slave the same alter also locked the whole database for few minutes even though we have no reads on the slave. We use one file per table. Any ideas?

RDBMS in general aren't best at dropping tables at runtime, tends to be done more at deployment time.  That aside, there are times when whatever process (thinking ETL) in facts need to create/drop tables.  How about, try to TRUNCATE the table first, and then DROP it.  See if that reproduces the locking behavior you are seeing.


Setting up users and admins with schema privileges I currently have a database that is split into two schemas: data and ref.  I have two database roles named Users and Admins.  Users need to be able to INSERT, SELECT, UPDATE, and DELETE from the data schema, and need to be able to SELECT from the ref schema.  Admins need to be able to INSERT, SELECT, UPDATE, and DELETE from both schemas.

All users and admins interact with the data via stored procedures.  Each table has a generated save, select, and delete stored procedure that the application uses (the stored procedure is in the same schema as the object it's hitting).

I previously had the database roles set up to execute against the schemas, but that's not really what I want, because then a user can insert, update, and delete against the ref schema via a stored procedure.

How can I set up security for the Admins and Users database roles to allow users to INSERT, SELECT, UPDATE, and DELETE against the data schema and to only SELECT against the ref schema while allowing the admins to INSERT, SELECT, UPDATE, and DELETE against both schemas?

The part that should interest you is the grants. But I figured a "code/picture" is worth a thousand words.

use master
go
create database test1
go
use test1
go
create schema data
go
create schema ref
go
create table data.tbl1(a int);insert data.tbl1 values(1);
go
create table ref.tbl2(a int);insert ref.tbl2 values(2);
go

create proc data.tbl1_select as select * from tbl1
go
create proc data.tbl1_insert(@a int) as insert tbl1 values(@a)
go
create proc data.tbl1_update(@a int) as update tbl1 set a = @a
go
create proc data.tbl1_delete as delete from tbl1
go

create proc ref.tbl2_select as select * from tbl2
go
create proc ref.tbl2_insert(@a int) as insert tbl2 values(@a)
go
create proc ref.tbl2_update(@a int) as update tbl2 set a = @a
go
create proc ref.tbl2_delete as delete from tbl2
go

create role users
go
create role admins
go
grant execute on schema::data to admins
grant execute on schema::ref to admins
grant execute on schema::data to users
grant execute on ref.tbl2_select to users
go

use master
go
create login user1 with password='', default_database=test1, check_expiration=off, check_policy=off
create login user2 with password='', default_database=test1, check_expiration=off, check_policy=off
go
use test1
go
create user user1 for login user1;
create user user2 for login user2;
alter role admins add member user1;
alter role users add member user2;
go

setuser 'user1'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
go
setuser -- To go back to your user
go
setuser 'user2'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
select * from ref.tbl2

Here's the result:

The EXECUTE permission was denied on the object 'tbl2_update', database 'test1', schema 'ref'.
The SELECT permission was denied on the object 'tbl2', database 'test1', schema 'ref'.


Is it safe to stop mysql maintenance check in progress? I am using HeidiSQL and running maintenance checks on all tables in the database (mysql 5.5.46-0+deb7u1-log) using:

CHECK TABLE `database`.`table` QUICK FAST MEDIUM

It just goes through and runs that command on each table, one at a time. However, I need to restart the server. Is it safe to kill this check without corrupting anything?

Please note what the MySQL Documentation on CHECK TABLE says about running on InnoDB tables, bulletpoint 7 under the heading CHECK TABLE Usage Notes for InnoDB Tables

  When running CHECK TABLE on large InnoDB tables, other threads may be blocked during CHECK TABLE execution. To avoid timeouts, the semaphore wait threshold (600 seconds) is extended by 2 hours (7200 seconds) for CHECK TABLE operations. If InnoDB detects semaphore waits of 240 seconds or more it starts printing InnoDB monitor output to the error log. If a lock request extends beyond the semaphore wait threshold, InnoDB aborts the process. To avoid the possibility of a semaphore wait timeout entirely, you can run CHECK TABLE QUICK instead of CHECK TABLE.

CHECK TABLE never implies doing writes, just blocking other connections in the worst. CHECK TABLE should abort itself after 4 minutes as a courtesy to other connections. In your case, if there are other processes, wait 4-5 minutes. Then kill the process. If there is nothing else running, kill the process now. Doing a shutdown with it still running may block the shutdown for 4 minutes or more, but will eventually shutdown.

Please run SET GLOBAL innodb_fast_shutdown = 0 beforehand.


mysqlcheck to optimize innodb tables -- performance/scheduling I'm attempting to optimize tables on a very large, production database server.  I'm looking for statistics or benchmarks on how long it will take for certain sized databases/tables.

Is there a multiplier of some sort using database/table size to figure out how long it will take?

My total database is about 90GB.

Running SHOW VARIABLES LIKE 'innodb_file_per_table'; indicates that innodb_file_per_table is on.

Don't OPTIMIZE.

Seriously, there is almost never a need to OPTIMIZE an InnoDB table.  90GB would take hours, maybe days, depending on tunables, disk speed, RAID, etc, etc.

OPTIMIZE may free up some of the free space caused by DELETEs and UPDATEs.  But, normally, InnoDB takes care of itself, rarely leaving more than 50% of the disk footprint "free".  Normal random inserts (in data or secondary indexes) lead to an average of 31% "free" -- that's a mathematical fact for BTrees.

If your table is PARTITIONed (and you must OPTIMIZE), do it one partition at a time, and do not use OPTIMIZE.  There is a bug wherein it will do the whole table in spite of what you say.  REORGANIZE PARTITION can do a single partition.

Note that non-tiny InnoDB tables normally always say 4MB-7MB of "Data_free" in SHOW TABLE STATUS.  You can't get rid if that 'wasted' space.

OPTIMIZE will not rearrange the data or the secondary indexes -- the rows will continue to be in exactly the same order.  (This is unlike MyISAM, which has multiple reasons for doing OPTIMIZE.)

Caution:  Most ALTERs, OPTIMIZEs, etc, require enough spare disk space for a complete new copy of that table -- 90GB more.

If you would like to present more details on why you want to OPTIMIZE, I will see if there is a good reason for it.  Please provide SHOW CREATE TABLE and SHOW TABLE STATUS.  And maybe even a technique to avoid it without downtime.


Indexing a GROUP BY Select with Multiple Inequalities I have a pair of linked issues I need help with:

Issue #1: How to Index a Table to Optimize for a "Group By" SELECT

I have a table of stock price information with the following Create statement:

CREATE TABLE `price_daily` (
  `Symbol` varchar(20) NOT NULL,
  `Date` date NOT NULL,
  `Price` float DEFAULT NULL,
  `MarketCap` double DEFAULT NULL,
  `PriceToEarnings` double DEFAULT NULL,
  `PriceToSales` double DEFAULT NULL,
  PRIMARY KEY (`Symbol`,`Date`),
  KEY `Date_MCap` (`Date`,`MarketCap`,`PriceToSales`,`Symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table is fairly expansive; it spans roughly 100 million rows. Thus, imprecise SELECT queries are likely to take a long time to resolve, which I would like to avoid.

I want to be able to search the database for stocks within a specified date range that fall within selected PriceToSales and/or PriceToEarnings ranges. The query below is typical of one I would perform:

SELECT Symbol, Date, MarketCap, Price
FROM price_daily
WHERE Date BETWEEN '2002-07-31'AND '2017-07-31'
AND MarketCap >= 1000
AND PriceToSales BETWEEN 2 AND 5
GROUP BY Symbol
;

I use GROUP BY because I only need to see one result per Symbol.
The problem is, when I add the 'GROUP BY' clause at the end, the query suddenly takes many times longer to complete than it does without that clause. What am I doing wrong? I assume it has something to do with my indexing, but I have not yet 100% figured out the 'rules' for ordering columns in indexes.
(A couple non-specific specifics: without GROUP BY the query has roughly 0.5s completion time but takes >200s to fetch, as there are potentially millions of results to display.)

EDIT 4/28/20 3pm: I have been told, in multiple locations, that the syntax of the above GROUP BY query is bad because the columns Date, MarketCap and Price are not aggregated. This is presumably a trivial issue to fix; for now, I leave Issue #1 alone to focus solely on the second issue.

Issue #2: SELECTing the Smallest Value From Within a GROUPed Query

A related question to the above:
Suppose, rather than allow the 'GROUP BY Symbol' clause from the above to choose whichever date it wants, I want to guarantee that my query will always SELECT the earliest Date chronologically for each Symbol. How would I do that? I assume the correct query would look something like this:

SELECT Symbol, MIN(Date)
FROM price_daily
WHERE Date BETWEEN '2002-07-31'AND '2017-07-31'
AND MarketCap >= 1000
AND PriceToSales BETWEEN 2 AND 5
GROUP BY Symbol
;

...but, due to the issue I am having with GROUP BY above, I cannot currently test this.

Any help given on either or both of these problems will be much appreciated.
If you need me to provide more information, I'll edit this post tomorrow morning when I get back to the office.

EDIT 4/28/20 3:30pm: The EXPLAIN for the query given under Issue #2 is as follows:

+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
| id   | select_type | table       | type  | possible_keys      | key       | key_len | ref  | rows     | Extra                                                     |
+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
|    1 | SIMPLE      | price-daily | range | PRIMARY, date_mcap | date_mcap | 21      | NULL | 50622081 | Using where; using index; using temporary; using filesort |
+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)

The query took 331.1 secs to resolve, generating 2735 rows. An excerpt from the table:

Symbol  Date
------  ----------
A       2003-12-19
AABA    2007-07-26
AACC    2005-10-03
AAN     2010-02-11
AAOI    2017-03-07
AAON    2013-10-01
AAPL    2004-10-05
AAT     2012-07-27
...     ...

It seems there are a lot of rows which fit the conditions and database server needs to sort them for grouping. It probably even needs to create a temp table on disk if your data doesn't fit into memory. In-memory temporary table size is limited by max_heap_table_size and tmp_table_size variables, but what is more important - MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length. It means every varchar(20) value will consume 20 bytes of RAM. Add 3 bytes for dates and 4 for doubles and your row size will be 31 bytes. If 5% of 100 million rows fit the conditions than you need 150 MB temporary table to group your data. You can start with increasing the max temporary table size and check if it helps.

But also you can try to use another approach:

SELECT Symbol
    ,(SELECT Date FROM price_daily
        WHERE Symbol = s.Symbol
            AND Date BETWEEN '2002-07-31'AND '2017-07-31'
            AND MarketCap >= 1000
            AND PriceToSales BETWEEN 2 AND 5
        ORDER BY Date LIMIT 1) AS min_date
FROM symbols AS s;

I assumed you have a table with list of symbols (Symbol data type should be the same).


Is it bad to have a cyclical relationship? I have 5 tables that have ended up with a cyclical relationship and I don't think that is ok.
I School is in one County.  A Distict has many Counties. A Teacher can teach in many Districts.

STUDENTRECORD - RecordId, TeacherId, CountyCode
COUNTY - CountyCode, CountyName, DistrictId
DISTRICT - DistrictId, DistrictName
TEACHER - TeacherId
TEACHERDISTRICT - DistrictId, TeacherId

So the relationship goes from the StudentRecord, teacherid, to the district, to the county and back to the studentrecord.
I have built the database, populated the teacher, district and county tables, but I have not tried to insert a StudentRecord yet.  I just know I have not seen a db design with a circle relationship before and I think there is a reason for that.  I assume I have created a bad design.
Is a cyclical relationship a bad thing in database design?  If so, why?Thoughts on fixing?
EDIT:  I forgot the DistrictId in the COUNTY table, which completes the circle. Sorry for the incomplete question.  Here is a picture:

Here is the original model -- at least as worded in the question.

Though there is nothing cyclical here it does have few problems. For example, the model has no relationship between County and District, although you do state that "District has many Counties". It is also possible to have StudentRecord {RecordId, TeacherId, CountyId} such that that teacher does not teach in the district the county is located in.

One possible fix may look like this, though I do not think this would be complete solution, but it may be a good starting point. I will use predicates -- marked by | -- and constraints (italics) to describe the model. Predicates map to tables and constraints to PK,AK, FK.

| District (DistrictId) exists.

District is identified by DistrictId.

| County (CountyCode) is located in district (DistrictId).

County is identified by CountyCode.

Each county is located in exactly one district; for each district that district may have more than one county.

If a county is locaeted in a district, then that district must exist.

| Teacher (TeacherId) exists.

Teacher is identified by TeacherId.

| Teacher (TeacherId) is licensed to teach in district (DistrictId).

For each teacher, that teacher may be licensed to teach in more than one district.

For each district, more than one teacher may be licensed to teach in that district.

If a teacher is licensed to teach in a district, then that teacher must exist.

If a teacher is licensed to teach in a district, then that district must exist.

| School (SchoolId) is located in county (CountyCode), district (DistrictId).

School is identified by SchoolId.

For each school, that school is located in exactly one county, district.

For each county, district; more than one school may be located in that county, district.

If a school is located in a county, district; then that county must be located in that district.

| Teacher (TeacherId) teaches in school (SchoolId), in district (DistrictId).

Each teacher may teach in more than one school in a district.

For each school in a district it is possible that more than one teacher teaches in that school.

If a teacher teaches in school in a district, then that school must be located in that district.

If a teacher teaches in school in a district, then that teacher must be licensed to teach in that district.

| Student (StudentId) exists.

Student is identified by StudentId.

| Student (StudentId) attends school (SchoolId).

Each student may attend more than one school, for each school is is possible that more than one student attends that school.

If a student attends school, then that student must exist.

If a student attends school, then that school must exist.

| Student record (RecordId) was generated for student (StudentId) by teacher (TeacherID) in school (SchoolID).

Student record is identified by RecordId.

If a student record was generated for a student by a teacher in a school, then that student must attend that school.

If a student record was generated for a student by a teacher in a school, then that teacher must teach in that school.


Does SQL Server cache the result of a multi-statement table-valued function? A multi-statement table-valued function returns its result in a table variable.

Are these results ever reused, or is the function always fully evaluated every time it is called?

The results of a multi-statement table-valued function (msTVF) are never cached or reused across statements (or connections), but there are a couple of ways that an msTVF result may be reused within the same statement. To that extent, an msTVF is not necessarily repopulated each time it is called.

Example msTVF

This (deliberately inefficient) msTVF returns a specified range of integers, with a timestamp on each row:

IF OBJECT_ID(N'dbo.IntegerRange', 'TF') IS NOT NULL
    DROP FUNCTION dbo.IntegerRange;
GO
CREATE FUNCTION dbo.IntegerRange (@From integer, @To integer)
RETURNS @T table
(
    n integer PRIMARY KEY,
    ts datetime DEFAULT CURRENT_TIMESTAMP
)
WITH SCHEMABINDING
AS
BEGIN
    WHILE @From <= @To
    BEGIN
        INSERT @T (n)
        VALUES (@From);

        SET @From = @From + 1;
    END;
    RETURN;
END;

Static table variable

If all the parameters to the function call are constants (or runtime constants), the execution plan will populate the table variable result once. The remainder of the plan may access the table variable many times. The static nature of the table variable can be recognised from the execution plan. For example:

SELECT
    IR.n,
    IR.ts
FROM dbo.IntegerRange(1, 5) AS IR
ORDER BY
    IR.n;

Returns a result similar to:

The execution plan is:

The Sequence operator first calls the Table Valued Function operator, which populates the table variable (note this operator returns no rows). Next, the Sequence calls its second input, which returns the contents of the table variable (using a Clustered Index Scan in this case).

The giveaway that the plan is using a 'static' table variable result is the Table Valued Function operator below a Sequence - the table variable needs to be fully populated once before the remainder of the plan can get going.

Multiple accesses

To show the table variable result being accessed more than once, we will use a second table with rows numbered from 1 to 5:

IF OBJECT_ID(N'dbo.T', 'U') IS NOT NULL
    DROP TABLE dbo.T;

CREATE TABLE dbo.T (i integer NOT NULL);

INSERT dbo.T (i)
VALUES (1), (2), (3), (4), (5);

And a new query that joins this table to our function (this could equally be written as an APPLY):

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
JOIN dbo.IntegerRange(1, 5) AS IR
    ON IR.n = T.i;

The result is:

The execution plan:

As before, the Sequence populates the table variable msTVF result first. Next, nested loops is used to join each row from table T to a row from the msTVF result. Since the function definition included a helpful index on the table variable, an index seek can be used.

The key point is that when the parameters to the msTVF are constants (including variables & parameters) or treated as runtime constants for the statement by the execution engine, the plan will feature two separate operators for the msTVF table variable result: one to populate the table; another to access the results, possibly accessing the table multiple times, and possibly making use of indexes declared in the function definition.

Correlated parameters and non-constant parameters

To highlight the differences when correlated parameters (outer references) or non-constant function parameters are used, we will change the contents of table T so the function has much more work to do:

TRUNCATE TABLE dbo.T;

INSERT dbo.T (i)
VALUES (50001), (50002), (50003), (50004), (50005);

The following modified query now uses an outer reference to table T in one of the function parameters:

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

This query takes around 8 seconds to return results like:

Notice the time difference between rows in column ts. The WHERE clause limits the final result for a sensibly-sized output, but the inefficient function still takes a while to populate the table variable with 50,000-odd rows (depending on the correlated value of i from table T).

The execution plan is:

Notice the lack of a Sequence operator. Now, there is a single Table Valued Function operator that populates the table variable and returns its rows on each iteration of the nested loops join.

To be clear: with just 5 rows in table T, the Table Valued Function operator runs 5 times. It generates 50,001 rows on the first iteration, 50,002 on the second...and so on. The table variable is 'thrown away' (truncated) between iterations, so each of the five calls is a full population. This is why it is so slow, and each row takes about the same time to appear in the result.

Side notes:

Naturally, the scenario above is deliberately contrived to show how poor performance can be when the msTVF populates many rows on each iteration.

A sensible implementation of the above code would set both msTVF parameters to i, and remove the redundant WHERE clause. The table variable would still be truncated and repopulated on each iteration, but only with one row each time.

We could also fetch the minimum and maximum i values from T and store them in variables in a prior step. Calling the function with variables instead of correlated parameters would allow the 'static' table variable pattern to be used as noted earlier.

Caching for unchanged correlated parameters

Returning to address the original question once more, where the Sequence static pattern cannot be used, SQL Server can avoid truncating and repopulating the msTVF table variable if none of the correlated parameters have changed since the prior iteration of a nested loop join.

To demonstrate this, we will replace the contents of T with five identical i values:

TRUNCATE TABLE dbo.T;

INSERT dbo.T (i)
VALUES (50005), (50005), (50005), (50005), (50005);

The query with a correlated parameter again:

SELECT T.i,
       IR.n,
       IR.ts
FROM dbo.T AS T
CROSS APPLY dbo.IntegerRange(1, T.i) AS IR
WHERE IR.n = T.i;

This time the results appear in around 1.5 seconds:

Note the identical timestamps on each row. The cached result in the table variable is reused for subsequent iterations where the correlated value i is unchanged. Reusing the result is much faster than inserting 50,005 rows each time.

The execution plan looks very similar to before:

The key difference is in the Actual Rebinds and Actual Rewinds properties of the Table Valued Function operator:

When the correlated parameters do not change, SQL Server can replay (rewind) the current results in the table variable. When the correlation changes, SQL Server must truncate and repopulate the table variable (rebind). The one rebind happens on the first iteration; the four subsequent iterations are all rewinds since the value of T.i is unchanged.


DNS Alias in SSRS Subscriptions We have an SSRS instance that is widely used, and we use a DNS alias to access the reports.  However, when you create a subscription to a report that sends a link, the server name is given and not the DNS alias.  I never would have noticed this, but in an effort to keep branding consistent, someone recommended that we use the DNS alias here as well.

Anyone know of a way to change that, so that subscriptions use the alias?

Thanks in advance.

In case it helps anyone else.  If you open Reporting Services Configuration Manager you can specific a URL for the Web Portal. If you use the DNS alias here this automatically sets the subscription emails to have the DNS alias.  The Web Service and Web Portal address have to match and make sure you have your Kerberos/NTLM settings configured properly or you'll get an error when you try to manage subscriptions from the server itself.


Export, based on query, into INSERT text file I need to export some data into a file. The data are selected with a query that will often be made of multiple joins. I want the import operation to be as easy as possible. I was thinking of building an INSERT file with such a method:
Oracle PL/SQL: Dump query result into file

But I am wondering if there is some tool already existing to export data based on a query. If this is the case, I might as well be happy with some binary file, as long as importing this file afterward is straight forward.
I see there is exp, but it doesn't seem to really be able to handle queries, only basic WHERE clauses.

Have you looked into using the UTL_FILE package ?
Using that, you can write to ( and read from ) files really easily and directly from PL/SQL, with great robustness.
You need to configure a Directory object in your database, and then you can write to it with ease.

Here's some material on the subject : http://psoug.org/reference/utl_file.html , http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm


How to generate list of dates --- ie every day for the past 2 years? We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value.

I'm wondering what's the simplest way to virtually generate a table that simply lists dates in one column, literally every date from '2014-01-01' to the current date (the other columns I can use a formula from there). Actually even appending future dates on for a year would probably be useful as well.

Now yes I can grab distinct dates from another random fact table that has thousands of entries, but that seems sloppy and is creating a dependency where there really shouldn't be one.

Using pure T-SQL, you can do the following:

;WITH cte AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
  FROM   [master].[sys].[columns] sc1
  CROSS JOIN [master].[sys].[columns] sc2
)
SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
FROM   cte
WHERE  DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();

Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:

SELECT [DatetimeVal]
FROM   [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');

If you want the ending date to be in the future, just replace the GETDATE() in either query.


How to add Subpartition to all partitions I believe adding sub-partition in Oracle 11g is easy

ALTER TABLE PART_TEST
modify partition OCT19
add subpartition OCT19AXCS
values ('AXCS');

I have close to 250 partitions..namely

jan07
feb07
...
up till
...
dec26

Does it mean I need to include that many alter table statements to alter all my partitions and add sub-partitions ?

You can just easily loop through the partitions in a PL/SQL loop, and add the subpartitions:

begin
  for p in (select partition_name from user_tab_partitions where table_name = 'PART_TEST')
  loop
    execute immediate 'alter table part_test modify partition ' || p.partition_name || ' add subpartition ' || p.partition_name || 'AXCS values (''AXCS'')';
  end loop;
end;
/


Clarifying delayed durability I'm considering applying delayed durability in a system I work on and want to make sure I have the correct mental model in order to avoid data loss.  My understanding is it basically means COMMIT turns into a sort-of "Schrodinger's cat" between COMMIT and ROLLBACK, right?  In other words, some transactions that are committed might actually end up rolled back ("the commit is a lie").
Hence, we should not actually expect to see any data loss in scenarios like the following:

A service broker activation procedure that RECEIVEs messages, processes them inside a transaction, and COMMITs.  If "the commit is a lie" and the transaction doesn't actually commit, it will be a rollback instead and service broker will automatically rerun it.
A periodic ETL-type job which bulk imports files into a staging table (this part would be fully durable).  Afterwards, inside a transaction, it processes the staged data and marks the staged data as processed.  If "the commit is a lie" and the transaction doesn't actually commit, it will be a rollback instead and the staged data will not be marked as processed, so we'll do it again next time.

In these scenarios we would not expect actual data loss because the transactions get rerun automatically.  In this case it's no more risky than anything else that could interrupt a transaction (e.g. a deadlock or an unplanned shutdown that interrupts the process).
In contrast, a scenario like the following would not be a good candidate for delayed durability and would risk data loss:

An external process (e.g. SSIS), for each file in a directory, begins a transaction, bulk imports the data from the file, and commits the transaction.  Once the transaction is committed, it deletes the file.  Now, if "the commit is a lie" and the transaction doesn't actually commit, we will have lost the file data for good because we deleted the file but the import was rolled back.

So in sum, delayed durability can lose data only if some external party is relying on the COMMIT.  For example, an online checkout enters a sale and reasonably assumes that when the data has been committed, the sale is finished, etc., and informs the user as such.  But if it's some processing that can already automatically tolerate and resume from interruptions/rollbacks, there should be no increased risk.
Does that sound right?

I'm afraid you misunderstand how transactions with delayed durability work. There won't be a magic rollback after a reportedly successful commit -- the client application will not know if the changes it thinks have been committed are never actually persisted. As described in the documentation, in the delayed durability mode the server reports a successful commit immediately, but delays writing the log buffer to the disk until some time later. If the server crashes or shuts down before then, these changes will be lost and the client will not know about that.


How to find the number of tuples that are vacuum-eligible? I am trying to find the number of vacuum-eligible tuples in one of our postgres tables. This is the output of vacuum verbose on that table

INFO:  "act_msgs": found 0 removable, 2217083 nonremovable row     versions in 314492 out of 314492 pages
DETAIL:  2185476 dead row versions cannot be removed yet.
There were 62133 unused item pointers.

Before running the vacuum the live and dead tuple counts were n_live_tup => 2204051 and n_dead_tup => 12370. After running the vacuum they are n_live_tup => 2217220 and n_dead_tup => 9. So it looks like if vacuum cannot remove the actually deleted tuples it adds those tuples to live_tup.

Now, we know that we have a long running transaction opened by the application and that is causing the auto vacuum not able to clean up the dead tuples. The application would do frequent inserts and deletes. How do i find the number of tuples that were actually deleted and would be vacuumed when that transaction is closed?

It clearly tells you right there..

  DETAIL:  2185476 dead row versions cannot be removed yet.

That's how many versions are vacuum-eligible. They're not being vacuumed because you have a long running transaction holding a ACCESS SHARE lock on the table?

See this answer.


SSAS Cube - Create measure based on the a dimension attribute We have an analysis service cube contains:

1 Measure = All_Calls_Count

2 dimensions = Month  &  Call Status

The All_Calls_Count reflects the count of rows on the fact table as shown :

Is it possible to create another measure that count all rows with only Call_Status = Rejected ? How ?

The below picture shows the desired output when browsing the cube :

You can try:

create a Calculated Member (clic on Calculation tab and then on
New Calculated Member
define it with the MDX expression you want

In your case, it could look like that:

A more detailed example here.


How can I permanently remove replication by script if I only have access to the subscriber? We've been building a replication network of DBs to give us some degree of Always On without paying the full price tag. Currently we have a publisher DB which replicates to a handful of subscribers, so that if the publisher goes down, we can promote a subscriber to be the new publisher. As is, this is a tedious manual process, but we want to start scripting elements of it so that the process is quicker and closer to being automated.
What we're currently wanting to do is make a script for subscribers to disable/remove the subscription to the old publisher if the old publisher goes down. This is due to the chance that DBs sometimes come back online after being offline for some time - in this scenario, we don't want it to have any further replication impact on the subscribers as this risks data inconsistency. We'd likely clean-slate the old publisher, so it's not an issue if its replication setup is throwing errors.
I've tried sp_subscription_cleanup which briefly shows the subscription as gone, but it comes back and data continues to be distributed to it from the publisher. I've found an MS doc on this, but that assumes access to the publisher.
So is there a script approach to permanently kill the subscription just from the subscriber?
Additional information
I note that you can right click on the subscription and delete it there, but I can't see from that menu the script that it's running. The subscription has also returned.

It sounds like what you're asking for is sp_removedbreplication, which you would run like:

EXEC sp_removedbreplication @dbname = 'MySubscriberDb';

From docs.microsoft.com:

  This stored procedure removes all replication objects ...on the
  subscription database on the Subscriber instance of SQL Server.
  Execute in the appropriate database, or if the execution is in the
  context of another database on the same instance, specify the database
  where the replication objects should be removed. This procedure does
  not remove objects from other databases, such as the distribution
  database.

That said, doing just this may not resolve your issue, depending on your configuration.

Option 1) Change your replication topology

I'd also recommend configuring your Distribution Agent to run as a pull subscription. This will enable you to disable the SQL Agent Jobs associated with loading any snapshots or transactions. Because the job is on your subscriber, you will be able to disable it, even when the publisher/distributor are offline. If/when the publisher/distributor comes online, you don't need to worry about transactions/snapshots being loaded to the subscriber, until you explicitly re-enable the Distribution Agent job. Note that if you do this, you would not need to use sp_removedbreplication.

Option 2) Change your replication topology another way

Is your distribution database on the same server as your primary? If you move your distribution database & jobs to a dedicated distributor, then this provides similar benefit to Option 1. Additionally, you would have access to the distribution database in order to add/remove subscription information from the replication metadata.

Option 3) Revoke permissions

You could also evaluate the permissions in use by your Distribution Agents. By revoking permissions for a remote Distribution Agent, you can prevent the distribution agent from logging in to the database to re-establish replication.

Option 4) Is replication really the right choice?

While you're asking about replication, I'd challenge you to ask if replication is the right choice for your need. It sounds like you're using replication for High Availability / Disaster Recovery (HA/DR).  Replication isn't a great choice for HA/DR. You may find that Availability Groups make for a better HA/DR solution. They come with different challenges, but are specifically built to solve HA/DR needs, and ensure only a single writable primary in order to avoid this particular challenge.


Returning all rows from multiple partitions of a partitioned table I have a table that is manually partitioned into multiple filegroups and spread out over multiple tables by month. My partition scheme is using a date column. What I would like to do is something like the following:

SELECT a
, b
, c
FROM<table>
WHERE date between '2015-08-01' AND SYSDATETIME()

I am wondering if there is a way to do it without the need to use a UNION ALL on all the tables?

As long as you have the appropriate check constraints on the date column on each of the underlying tables, you can create a partitioned view:

CREATE VIEW dbo.2015Sales
AS
SELECT date, sales FROM dbo.Jan2015Sales
UNION ALL
SELECT date, sales FROM dbo.Feb2015Sales
...

This would allow the UNION ALL to be hidden behind the view, and SQL Server can eliminate irrelevant tables (based on your check constraints) from the query plan if the query explicitly specifies a date range.

However, partitioned tables, in which all of the data would be in one table and no UNION ALL would be needed, is the more common approach.

That said, there are a lot of potential performance implications for either approach, so it's best to test on your specific data and workload. For example, partitioned views frequently yield query plans with extraneous Concatenation operators, a situation that may reduce the accuracy of the Cardinality Estimator (especially in SQL 2012 and earlier). And partitioned tables, while very useful and probably what you want, do come with a couple potential downsides as well.


Disaster recovery solution for MySQL I have set a Master-master replication with the second Master running as read-only. This master-master setup is on the same location. Now I will have to setup an DR for this production at a different location. What are the solutions I have? Is it possible to set another master-master setup at DR site where in the Master in DR acts as the slave of the active master of DC? Will that work? Any other inputs for DR solution would be appreciated.

Thanks!!

Active-Passive master-master is a good setup but I have seen SUPER (humans)users writing on slave without setting sql_log_bin.
(Though super_read_only in 5.7 will change things around this.)

Anyways, following is possible and works.

ProdActiveMaster<---->ProdPassiveMaster
|
|
|
\/
DRmaster----->DRSlave

So you know now that another master-master setup is not possible as DRMaster cannot replicate from two masters.
I doubt multi-source replication is a solution here as it will create more trouble than help.

You need to make sure to keep your DRSlave, "master-ready"... setting auto_increment% and binlog + logslaveupdates settings.


Is there a SQL Server 2008 R2 specific target for SSDT projects? I am making a SQLCLR stored procedure in Visual Studio 2012. I have these options for target platform:

Is there not a SQL Server 2008 R2 specific option? Is this because SQL 2008 R2 shares the same compatibility level of 100 with SQL Server 2008?

A 2008 and 2008R2 user database is schema compatible, hence one version for both in SSDT. In other words, there aren't any schema objects that you could add to a 2008R2 SSDT model that couldn't be created in 2008.

This isn't the same as either database version or database compatibility level.

Database version:

  The database version is a number stamped in the boot page of a
  database that indicates the SQL Server version of the most recent SQL
  Server instance the database was attached to. The database version
  number does not equal the SQL Server version.

Database compatibility level:

  The database compatibility level determines how certain database
  behaviors work. For instance, in 90 compatibility, you need to use the
  OUTER JOIN syntax to do an outer join, whereas in earlier
  compatibility levels, you can use *= and =*.


Check existence on other tables: performance advice This is my first post.
I have this data model:

I want fetch some profile, with checking if they have any page or user assigned or not. I have tried multiple queries and finally came to these tows:

Query 1:

DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

SELECT TOP ( 1 )
        p.[ProfileId] AS [ProfileId] ,
        p.[Username] AS [Username] ,
        p.[UsernameCleaned] AS [UsernameCleaned] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Users] u WITH ( NOLOCK )
                             WHERE  u.UserId = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsUser] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Pages] pg WITH ( NOLOCK )
                             WHERE  pg.[PageId] = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsPage]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token

Query 2:

DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

DECLARE @ProfileId BIGINT= NULL ,
    @Username NVARCHAR(256)= NULL ,
    @UsernameCleaned NVARCHAR(256)= NULL ,
    @IsPage BIT= 0 ,
    @IsUser BIT= 0;

SELECT TOP ( 1 )
        @ProfileId = p.[ProfileId] ,
        @Username = p.[Username] ,
        @UsernameCleaned = p.[UsernameCleaned]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token;

IF @ProfileId IS NULL
    BEGIN
        RETURN
    END

SELECT  @IsUser = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Users] u WITH ( NOLOCK )
                                       WHERE    u.UserId = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT  @IsPage = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Pages] pg WITH ( NOLOCK )
                                       WHERE    pg.[PageId] = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT TOP ( 1 )
        @ProfileId AS [ProfileId] ,
        @Username AS [Username] ,
        @UsernameCleaned AS [UsernameCleaned] ,
        @IsUser AS [IsUser] ,
        @IsPage AS [IsPage];

Which one would be better and would have a higher performance?

p.s.
I'm not an exact SQL-guy. So, it's hard for me to determine which query would have a better performance and executes faster -I mean as fast as possible. Any help/advise would be absolutely helpful. Thanks in advance.

I'd probably write something like the following:

SELECT TOP 1 -- Top command optional and may not actually help speed things up
  p.ProfileId -- No need to alias to the same thing
  , p.Username
  , p.UsernameCleaned
  , IsUser = CAST(CASE WHEN u.UserId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
  , IsPage = CAST(CASE WHEN pg.PageId IS NOT NULL THEN 1 ELSE 0 END AS BIT)
FROM dbo.Profiles p
  LEFT JOIN dbo.Users u ON u.UserId = p.ProfileId -- Left join here instead of exists above
  LEFT JOIN dbo.Pages pg ON pg.PageId = p.ProfileId
WHERE
  p.UsernameCleaned = @Token;

If you notice I basically just traded EXISTS for LEFT JOIN. This should give you reasonable performance and be fairly clear for people who review your code. Of course the standard disclaimer applies for any performance question.

  You should profile this on a representative workload to determine what is fastest, and indexes are your friend.

In general though you want to avoid subqueries in the SELECT portion of your query because they will probably be run once for each row in the result set. Here that isn't a big deal but if you have more results then the difference can be dramatic.

Try at all costs to avoid the NOLOCK query hint. It looks nice on paper and can give you an apparent boost, but nothing is free. For more detailed information on why this probably isn't a good idea I'd advise you read this blog post by Aaron Bertrand.

Also of note rewriting your query made it clear that a profile can be both a user and a page. I don't know your use cases but that feels wrong to me especially since this query seems to be designed to tell the difference. If that is true then you might need to rework your data model.

Finally as a personal preference I like to have my aliases on the left if the column is computed/derived like IsUser above. My reasoning goes the alias can get lost in the confusion of the calculations so it is best to put it first. Of course you should go with what you and your coworkers find is most legible.


Catching Errors in procedure I have created procedure for orders table. The procedure checks if the order has shipped, then the customer can returns all/part of the shipped items. Once verified by the procedure and the order has shipped, order details will be replicated in deleted orders table. However, I get an error on the insert whenever I run the program. This is due to duplication - An UPDATE or INSERT statement attempted to insert a duplicate key.
Is there anyway I can insert records for the returned item without showing an error?
My procedure is:

CREATE PROCEDURE RET_ORD(PARAM_ONO ORDERS.ONO%TYPE)
  IS
    CURSOR CUR_RET
  IS
  SELECT * FROM ORDERS
  WHERE ONO = PARAM_ONO;
  V_CUR CUR_RET%ROWTYPE;
BEGIN
  OPEN CUR_RET;
 LOOP
  FETCH CUR_RET INTO V_CUR;
  EXIT WHEN CUR_RET%NOTFOUND;
 IF V_CUR.SHIPPED IS NOT NULL THEN
  INSERT INTO
  DELETED_ORDERS(ONO,CNO,ENO,ENAME,ORD_DE_D,STATUS)
  VALUES(V_CUR.ONO,V_CUR.CNO,V_CUR.ENO,USER,,SYSDATE,'RETURNED');
  DELETE FROM ODETAILS
  WHERE ONO = P_ONO;
 ELSIF V_CUR.SHIPPED IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('CANNOT PROCESS ORDER '|| V_CUR.ONO);
  END IF;
 END LOOP;
 CLOSE CUR_RET;
END;

I'm guessing you're getting the unique constraint violation on the deleted_orders table because you are running this multiple times for the same ONO.  To prevent subsequent calls using the same ONO from getting a unique constraint violation, you can either modify the select query to not find them like this:

SELECT * FROM ORDERS
  WHERE ONO = PARAM_ONO
  AND ONO NOT IN (SELECT ONO FROM DELETED_ORDERS);

If instead you want feedback when this occurs rather than just ignoring the duplicate, you can nest the INSERT into a BEGIN...EXCEPTION...END block that catches the constraint violation and handles it by doing a DBMS_OUTPUT.


Oracle Fine-Grained Access (FGA) auditing excluding one source I want to audit DML statements coming from other places than application user "tomcat" (OS_USER attribute). I want to focus only on developers or support team changing data in any way.
DML Trigger doesn't log exact query which was executed, just query type (INSERT, UPDATE, DELETE).
With straightforward usage of FGA I cannot filter out application user "tomcat". I am familiar with those questions:
Oracle auditing on specific user activity
Can this be accomplished with Oracles Fine Grained Auditing?
but information is logged in trace files, viewing which requires more effort, and I would like auditing to be accessed by dictionary view. Is it possible?

I helped my self with this:
BEGIN
    dbms_fga.add_policy(
      ... ,
      audit_condition => 'SYS_CONTEXT(''USERENV'',''OS_USER'') <> ''tomcat''',
      ...);
END;
/


Query performance degraded after upgrading Postgres I have a table with several million records in a PostgreSQL 12 database, and after an upgrade from 11 to 12, a few queries started performing horribly. They went from taking ~1 second to ~5 minutes. I tried rebuilding all indexes, vacuuming, and all usual Postgres low-hanging fruit, but performance is still terrible.
This is the query:
SELECT id, activity_count
FROM user
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND active = true
ORDER BY activity_count DESC LIMIT 101

In other words, find all active users matching the given account number, and sort from most active to least.
This query takes about 5 minutes to return just 2 records. Something's not right.
The column search_index is a tsvector storing all the keywords from the table's various text fields (just things like account_number, name, etc).
I have a GIN index created for this column with:
CREATE INDEX user_search_index_gin
    ON public.user USING gin
    (search_index)
    TABLESPACE pg_default;

I also have an index for the active column with:
CREATE INDEX user_active
    ON public.user USING btree
    (active ASC NULLS LAST)
    TABLESPACE pg_default;

And I have an orderd index for the activity_count with:
CREATE INDEX user_activity_count
    ON public.user USING btree
    (activity_count ASC NULLS LAST)
    TABLESPACE pg_default;

Yet when I run EXPLAIN, I get:
"Limit  (cost=0.56..11443.66 rows=101 width=1552)"
"  ->  Index Scan Backward using user_activity_count on user  (cost=0.56..36010185.91 rows=317836 width=1552)"
"        Filter: (active AND (search_index @@ '''1234567890'':*'::tsquery))"

Why is it only using the user_activity_count index and not the more efficient GIN index? How do I fix this?

Which index to use?
While your WHERE conditions are not very selective, the current query plan makes a lot of sense with ORDER BY activity_count DESC LIMIT 101. See:

How to search a table with 80 million records faster?

However, your predicates strike me as pretty selective:
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND active = true

That's unless your example '1234567890' is misleading. (Are you sure you want the single quotes in the search term?) It's obviously not the actual query you used. Unmatched parentheses. In any case, you probably want the  "simple" text search configuration (plus some improvements):
WHERE  search_index @@ to_tsquery('pg_catalog.simple', '''1234567890'':*')
AND    active

See:

Get partial match from GIN indexed TSVECTOR column

Statistics
So it seems your statistics are not up to date.

after an upgrade from 11 to 12, a few queries starting performing
horribly. They went from taking ~1 second to ~5 minutes. I tried
rebuilding all indexes, vacuuming, and all usual Postgres low-hanging
fruit, but performance is still terrible.

Why is it only using the user_activity_count index and not the more efficient GIN index?

Did you also add ANALYZE to your basket of all usual Postgres low-hanging fruit? You certainly didn't mention it. Consider instructions in the manual:

Statistics
Because optimizer statistics are not transferred by pg_upgrade, you
will be instructed to run a command to regenerate that information at
the end of the upgrade. You might need to set connection parameters to
match your new cluster.

If your DB is big, you may be interested in vacuumdb with the option --analyze-in-stages. (That ship may have sailed for the case at hand.) The manual:

This option is useful to analyze a database that was newly populated
from a restored dump or by pg_upgrade. This option will try to create
some statistics as fast as possible, to make the database usable, and
then produce full statistics in the subsequent stages.

Related:

Optimizing slow performance of simple SELECT query


How to monitor history of database usage if login auditing is not enabled? I have a number of databases on multiple servers that don't seem to be used any longer.  I'd like to drop these databases, but I'm uncertain if they are actually in use.

I can't find their owners, there are no processes linked to them, and nothing has been written to the tables for a few months now.

Login auditing was not enabled prior to today.

Servers versions range from 2005 to 2014, but most of these are on 2008 R2.

Is there a way to check the history of logins for these databases, going back 6 months, just to double check with these users, before taking the databases offline?

I got a suggestion to look at index usage history; is someone able to share a script for that?

I blogged about it exactly one month ago. Since link-only answers are discouraged and pasting here the whole blog post would be ridiculous, here is a summary of what you will find there.

Basically, you have to record index usage in a user table, in order to make sure that server restarts and index maintenance don't delete entries for the databases you're interested in.

A second method involves auditing index usage by means of streaming an Extended Events session that captures lock acquired events. This method is particularly useful for capturing additional information about who is accessing the tables without having to capture detail information (audits and XE file targets would be too verbose for this task).

I hope you find it useful.


Unexplainable DBCC CHECKDB Command Output Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1
  (X64) Mar 22 2019 22:33:11 Copyright (C) 2017 Microsoft Corporation
  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0
   (Build 14393: ) (Hypervisor)

My Production server executes these command inside a SQL Agent job to output results to a table in my Administration database:

DECLARE @SQL VARCHAR(MAX) = 'master..xp_cmdshell ''SQLCMD -E -Q"DBCC CHECKDB('+@Database+') WITH NO_INFOMSGS, ALL_ERRORMSGS"'''; -- If all is well, there will be no output.
INSERT INTO DBCC_CHECKDB_Results EXEC(@SQL);

Last night, one line of information was output and the job stopped executing, with no errors shown in the job history. The one line of information was:

  "Sqlcmd: Warning: The last operation was terminated because the user
  pressed CTRL+C."

How is this possible? At 10:00 p.m. Sunday night, no one is logged onto the server. CTRL+C is not in the command line.

As a test, I executed this command, and while it was executing, typed: CTRL+C:

USE ARC
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS:
Nothing happened.

Any ideas on what would cause that line of output?

Thanks, Lee

The error message you're seeing:

  Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.

Occurs when sqlcmd is asked to quit while it is waiting for a response from SQL Server.  Most likely someone (or something) killed the spawned cmd.exe process that was running SQLCMD.exe, which resulting in that error message being directed to the SQL Server errorlog.

Instead of using SQLCMD to execute DBCC CHECKDB, you should simply setup a standard T-SQL job step to run the DBCC CHECKDB command directly.

Alternately, run Ola Hallengren's excellent free and open-source Maintenance Solution, or one of the other myriad solutions available.


Mysql slave use more memory than master server I have used top command to check memory usage on both mysql master and slave server. Master is using only 12% of allotted memory but slave is using 82%. configuration wise both are same. can anyone tell me how to resolve this and why its taking more than slave?

query_cache_size = 200M

Too big.  Don't go past 50M, else the maintenance will hurt performance.

  innodb_buffer_pool_size = 1G

If you are using InnoDB, set this to 70% of available RAM.

table_open_cache (formerly called table_cache) relates to the number of tables you might need to open, but not the sizes of the tables.  A few hundred is typically enough.  4096 is needed by only a few systems; don't go past that without a clear indication that you need it.

  All the activities are happening at master

If so, what is the Slave for?

Please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; (on each server) so I can help you dig deeper into your puzzle.


SQL Replication, transactions Replication I have configured a SQL transaction replication to create two subscribers and one publisher, the question is that after any deployment and changes that made on the SQL "alter procedures" on the publisher server, the replication log several errors and i solved the issue by re establish the snapshot.

is there any think that i should do during the deployment "run the updated script on the publisher" ? should i stop the application to not log any transaction during the deployment, or take the DB offline. any ideas please .

An alter procedure would not normally be replicated and so would not cause any problems. More than likely you're doing DDL operations and changing the tables and this is breaking the replication.

Michael Keleher has suggested your replication may not be set up to replicate schema changes:

  Open the properties of the publication, go to the Subscription Options section and change the Replicate schema changes property to true. Then all DDL changes applied to articles in the publication will be applied to the subscriber without the need to re-initialize.

If that works for you then great.

In my experience replication can cause a lot of normally benign deployments to fail, and so I prefer that replication be removed as part of a deployment and reapplied afterwards.

For a "lighter" touch, in your specific example the end of your deployment you can mark the subscribers as needing a snapshot (using sp_reinitpullsubscription) then trigger the snapshot agent (with sp_start_job).

It's roughly documented in BOL https://msdn.microsoft.com/en-AU/library/ms152466.aspx?f=255&MSPPError=-2147217396 though the instructions differ slightly depending on whether you're doing push or pull subscriptions.

However you probably need to dig a little more into what's actually going on to cause the problem because doing this kind of thing will make every single deployment go slowly for little reason.


I cannot write into SQL Server 2008 R2 express edition from C# There is no error, the dataset is getting updated, but the database is not updated.

I am using Windows authentication in connection string to the database. I have given accept changes on update.

Are you using the User Instances and AttachDbFileName options in your connection string? If so I will bet that you are actually connected to two different copies of your database. You need to make sure you are connecting to the same copy - I would suggest manually attaching the database on your Express instance, and always connecting to that without the use of User Instances and AttachDbFileName.

If this is not the case, please show the connection string you are using in C# and how you are validating that the data is not updated (e.g. you are probably using a different application and/or connection string for that).

The User Instances feature is actually deprecated for future development work. In SQL Server 2012 this is replaced with SqlLocalDb.


Large files in wamp\bin\mysql\mysql5.5.24\data directory I have discovered these files located in the wamp\bin\mysql\mysql5.5.24\data folder on my Windows 2008 server.

They are consuming about 22 GB on a 40 GB drive.

I am using a LMS called moodle, that may be kicking off the creation of them, I am not sure.

I am trying to determine:

what is creating these files?
can I delete them to reclaim drive space?

1 - what is creating these files?

Those files are called binary logs. They contain a list of all completed SQL statements you have executed. Your mysqld process created them and autorotates at 1GB. Any restart of mysqld or issuing FLUSH LOGS; will close one binary log and open another

They got created because there is a line in my.ini that looks like this:

log-bin=mysql-bin

Note the timestamps on each file

mysql-bin.000068 has 10/31/2012 2:13 PM
mysql-bin.000069 has 11/1/2012 11:13 AM
mysql-bin.000070 has 11/5/2012 11:51 AM
mysql-bin.000071 has 11/9/2012 11:50 AM
mysql-bin.000072 has 11/13/2012 11:13 AM
mysql-bin.000073 has 11/16/2012 12:05 AM

Here is what has been going

On 10/31/2012 at 2:13 PM

mysql-bin.000068 surpassed 1GB (1048576 KB)
mysql-bin.000068 closed
mysql-bin.000069 opened

On 11/1/2012 at 11:13 AM

mysql-bin.000069 closed due to mysql restart or FLUSH LOGS;
mysql-bin.000070 opened

On 11/5/2012 at 11:51 AM

mysql-bin.000070 closed due to mysql restart or FLUSH LOGS;
mysql-bin.000071 opened

On 11/9/2012 at 11:50 AM

mysql-bin.000071 surpassed 1GB (1048576 KB)
mysql-bin.000071 closed
mysql-bin.000072 opened

On 11/13/2012 at 11:13 AM

mysql-bin.000072 closed due to mysql restart or FLUSH LOGS;
mysql-bin.000073 opened

On 11/16/2012 at 12:05 AM

mysql-bin.000073 surpassed 1GB (1048576 KB)
mysql-bin.000073 closed
mysql-bin.000074 opened

By the way, the reason it autorotates at 1GB? The default setting max-binlog-size is 1GB.

  2 - can I delete them to reclaim drive space?

As long as you do not have mysql replication installed, then yes.

First login to mysql and run

mysql> RESET MASTER;

This will erase all binary logs and leave you with mysql-bin.000001 with 107 bytes.

Next, if you comment it out like this

#log-bin=mysql-bin

and restart the mysqld process

C:\> net stop mysql
C:\> net start mysql

The logs will no longer be written.

As a final step, you can then delete mysql-bin.000001 from Windows Explorer.

Give it a Try !!!


How can I connect to SQL Server from All IPs? I have problem with access to SQL server with TCP/IP.

I would like to SQL instance accept client from all network adapter that machine has.

I go to configuration manager console and enable TCP/IP, set TCP port to 1433 (or any other port) , set “TCP Dynamic Port” to Blank and set “Listen All” to true. After these changes, I restart SQL server service and starting SQL service will fail. For starting SQL service I have to change “Listen All” to false and then service will start successfully.

But after disabling “Listen ALL” you have to set Adapter IP in “IP Addresses” tab and SQL will accept request only from that IP and if you don’t specify any IP, SQL doesn’t accept any TCP/IP request.

Now I need to know why enabling “Listen ALL” will lead to service failure. And how can I resolve this problem?

I ran “netstate –a” and I saw no application has port 1433 on TCP.

When “Listen ALL” was disable and I don’t added any specific IP Yet, I check SQL browser service(UDP 1434) with “portquery.exe” and SQL browser response doesn’t include any TCP port.

After I added 1 specific IP SQL browser service include TCP 1433.

And as I mentioned if I enable “Listen ALL” , SQL service will failure and after restart it can’t start.

After service restart failure I checked windows event viwer and I saw 4 Error as Following :

1.TDSSNIClient initialization failed with error 0xd, status code 0x10. Reason: Unable to retrieve registry settings from TCP/IP protocol's 'IPAll' configuration key. The data is invalid.

2.TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.

3.Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

4.SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Some experts tell me may there is a tiny whitespace instead blank in IPALL/TCP Dynamic Ports but When I Checked out that I saw just Blank is there and it is OK.
this screen shot of my TCP/IP properties for more detail :
https://drive.google.com/open?id=0B3zq7J4BJZgMNEdCZEJIeml1RHc

also as you see from above image, All Ips that exist in IP Addresses tab exist and network adapters IPs doesn't changed.I mean I know if SQL listen to non exist Ip it will failed.
Finally , How can  I Listen to all IPs?

Thanks

Finally

I found the reason of problem.

I don't know for what SQL or other services had changed the value type of TcpPort fields in registry
(HKEY_LOCAL_Machine\Software\Microsoft\Microsoft SQL Server\Instance ID\MSSQLServer\SuperSocketNetLib\TCP\IPALL\TCPPort ).

It's converted to  Binary or DWORD type.

but the solution :
I removed TcpPort value and then create a new string value and named it TcpPort and set it to 1433 and then service started successfully.

there is no need to say that I checked that field over and over and it doesn't has any whitespaces.

Thanks to all experts whom participate in this discussion and helped me.


Parsing the value and copying it to another column in postgresql column_1 (varchar) has values with the format like 1024 MB, 2048 MB etc. There also exits column_2 (int). Now I would like to parse the value in column_1 and update the column_2 with values like 1024, 2048. I am using PostgreSQL.
How do I do this ? I am fairly new to databases. Any help/suggestion is appreciated.

If your data is consistent, so that after a number it always contains a space and then something else, then the following expression will get you the number part:

split_part(original_size, ' ', 1)

This will split up your string along the spaces with the remaining chunks are indexed from 1 up to the last one, then the first one is picked.  There are, of course, other possibilities around, for example using a regex, which probably other will add later :)

Now you can construct your UPDATE statement:

UPDATE size
   SET processed_size = split_part(original_size, ' ', 1);

Notice that there is no WHERE clause as you want to set the second column for every row in the table.

You find an example on SQLFiddle.


How to get accurate query performance? I'm attempting to improve performance of a stored procedure.  When I run the SP, it finishes almost instantly, as if something were cached.  I was told to use the following two lines of SQL before executing the SP in SSMS:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

When I run the SP with the two lines of code above the SP finishes in around 8 seconds. However, is this really giving me the real execution time (as in if I run this from an application)?  How do I know?

Those commands do two things:

Clear the page cache, which stores data pages that have already been retrieved from disk (normally the biggest factor in time in a query is disk access)
Clear the query plan cache, which means the server needs to create a new query plan.  This is normally not significant except for very high transaction volumes.

You are basically getting a time that is equivalent to the "worst case" scenario - you just rebooted the server and nothing is in memory.  Subsequent runs don't need to pay the cost to pull the data from disk since those pages are already loaded into memory.

This is similar to a real world situation - your first user to run a particular query will likely have to wait longer than on subsequent runs, assuming you are checking the same data.

A good method that I like to use is running multiple times and taking an average.  This is especially helpful in a shared environment since you don't have full control over shared resources like tempdb.

You can also use these commands to get more information on what is actually happening behind the scenes:

SET STATISTICS IO ON
SET STATISTICS TIME ON

These will give you detailed information about page reads from disk (per object), logical page reads, time spent compiling a plan, and time spent executing a query.


Where does this index come from? Looking for some index in one schema I get to create, I found one index with prefix sys_ such as below:

SQL> select index_name from dba_indexes where owner = 'DANILO';

INDEX_NAME
------------------------------
INDEX_SALES_ID
SYS_C0010892

Indeed, I didn't create this index, Why do I have it in my schema?

That is an index that was automatically generated for a constraint. Example:

SQL> select index_name from user_indexes;

no rows selected

SQL> create table t1(c1 number primary key);

Table created.

SQL> select index_name from user_indexes;

INDEX_NAME
--------------------------------------------------------------------------------
SYS_C006380


can't remove "GRANT USAGE" I was testing some stuff, and added a:

grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password';

so now when I do

show grants for cptnotsoawesome@localhost;

I can see that one of them is:

Grants for cptnotsoawesome@localhost
----------------------------------
GRANT USAGE ON *.* TO 'cptnotsoawesome'@'localhost' IDENTIFIED BY PASSWORD 'somePEW-PEWstring'

Now I want to remove it as I think it's a security hazard, so i do the:

REVOKE USAGE ON *.* FROM 'cptnotsoawesome'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

But it still shows that USAGE grant in the grant list.

Grants for cptnotsoawesome@localhost
----------------------------------
GRANT USAGE ON *.* TO 'cptnotsoawesome'@'localhost' IDENTIFIED BY PASSWORD 'somePEW-PEWstring'

Any ideas why? What am I doing wrong?

Under the hood, when you see a user with USAGE only, that the user is written in the mysql.user table with all global privileges turned off.

You originally stated the the user had this:

grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password';

You should see a row in mysql.user with the MD5 password and all globals privs set to N. You should also see a row in mysql.db with

user='cptnotsoawesome'
host='localhost'
db='statistics'
all DB levels privs set to 'Y'

You should be able to see them with this query

SELECT * FROM mysql.db
WHERE user='cptnotsoawesome'
AND host='localhost'
AND db='statistics'\G

When you ran the REVOKE command, you simply removed the row from mysql.db. This did not touch the row in mysql.user. Thus, you could still login to mysql and only have the privs to run

SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;

If there is a test database called test or a database whose first 5 characters are test_, (look for it using SELECT * FROM mysql.db WHERE db LIKE 'test%';) then the user with just USAGE can have full rights to the test databases. I wrote about this in ServerFault Sep 2011.

If you want to remove the row from mysql.user you could either run

DROP USER cptnotsoawesome@localhost;

or

DELETE FROM mysql.user WHERE
WHERE user='cptnotsoawesome'
AND host='localhost';
FLUSH PRIVILEGES;


Linkserver update not working in Transaction I am trying to do operation on my linked server. I have created one linked server and everything is ok. I just observed one thing which I am not able to understand.

if I run:

update SERVER.testdb.[AAA].MyTable
set MyName = 'abc@pqr.com'
where ID = 999

This works fine. But when I execute the same code in following way:

 BEGIN TRY
    BEGIN TRANSACTION TRF

    update SERVER.testdb.[AAA].MyTable
    set MyName = 'abc@pqr.com'
    where ID = 999

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TRF
END CATCH

It says that the parameter is incorrect.

Can anyone tell me why this is happening and what is solution ?

I have tried this on SQL Server 2012. If I run your query as is it completes successfully but does not update anything. But if I do SET xact_abort ON it completes successfully. You can read more about xact_abort on  https://msdn.microsoft.com/en-us/library/ms188792.aspx.

SET xact_abort ON

BEGIN try
    BEGIN TRANSACTION trf

    update [SERVER].[testdb].[AAA].[MyTable]
    set MyName= 'abc@pqr.com'
    where ID=999

    --COMMIT TRAN trf
END try

BEGIN catch
    ROLLBACK TRANSACTION trf
END catch

What is the SQL Server version you are trying this on? Also, tell us on which line you are getting incorrect parameter error?