Default Postgres privileges not showing on \ddp As far as I understand there are set of default Postgres public privileges. I have created user and granted him privilege using:

CREATE USER foo WITH PASSWORD 'foo';
\connect bar;
GRANT SELECT ON ALL TABLES IN SCHEMA public to foo;

and I can connect to DB with no issues. Then I taught I must have connection privilege in the default privilege set, since I didn't grant connect on database explicitly.

When I do \ddp I get 0 rows, and as far as I understood it is a way of checking default privileges per this page.

Can someone help me understand how connecting to the DB is permitted when I haven't explicitly allowed it?

According to the documentation, PostgreSQL does the following for PUBLIC:

  PostgreSQL grants privileges on some types of objects to PUBLIC by
  default when the objects are created. No privileges are granted to
  PUBLIC by default on tables, table columns, sequences, foreign data
  wrappers, foreign servers, large objects, schemas, or tablespaces. For
  other types of objects, the default privileges granted to PUBLIC are
  as follows: CONNECT and TEMPORARY (create temporary tables) privileges
  for databases; EXECUTE privilege for functions and procedures; and
  USAGE privilege for languages and data types (including domains).

This implies that to prevent a new user to be able to connect to any database following must be run for each database:

revoke connect on database <db_name> from public;


What are SQL Server Securables? I migrated two applications to the new server. One of the them works without any issues but the other ran into back end sql permission problems like

  The EXECUTE permission was denied on the object
  ‘aspnet_CheckSchemaVersion’

I could find figure why one application would work and the other would not. They both run under the same application pool but uses different database. I didnont have to do anything for the one that to work, for the other I had to grant permission on [Network Service] user for each table, stored procedure which the application access.

Well I have resolved the problem now by making the two databases securables for Network Service. I dont know why I had to do this for one application while the other does not need it. My question is

What is SQL Server Securable in SQL Server Management Studio? You basically access them when you click on user -> Properties then go to second page on the left.

What is it used for? What is its proper use?

From Microsoft

  Securables

Lists the securables on which specific permissions have been granted or denied to  this principal.

A securable is anything that can have permissions granted, denied, or revoked on in SQL Server.  It's basically "something you can secure".

Think about it like this:  Your car is a securable.  You would have to have been given keys (permissions) to access your car (securable).

A securable is anything that can be accessible or inaccessible.

Take a look at this BOL reference on Securables.  Under the three scopes of securables (server, database, schema) you can see a listing of the contained ones.  Here is the list of securables (taken directly from BOL, but for reference):

  Server Scope Securables

  Endpoint
  Login
  Database

  Database Scope Securables

  User
  Role
  Application role
  Assembly
  Message Type
  Route
  Service
  Remote Service Binding
  Fulltext Catalog
  Certificate
  Asymmetric Key
  Symmetric Key
  Contract
  Schema

  Schema Scope Securables

  Type
  XML Schema Collection
  Object

  Aggregate
  Function
  Procedure
  Queue
  Synonym
  Table
  View


How to Exclude Foreign Keys during Publish action in sqlpackage I am trying to publish a .dacpac extract to a target database, I want to exclude just the foreign key constraints in all the tables.  After going through the ExcludeObjectTypes options I do not find a clear way to exclude just the foreign keys.

I need to migrate the schema structure of the source except for the foreign keys as I will be loading the data in-parallel by other means. So the requirement is to just exclude the foreign keys when I perform Publish

You need to disable deployment option "Script validation for new constraints".
Foreign keys will be still created, but in not trusted state and existing data will not be validated.
Publish script will contains something like:

ALTER TABLE [Tab] WITH NOCHECK
    ADD CONSTRAINT [FK_Tab] FOREIGN KEY ([Id]) REFERENCES [Tab2] ([Id]);

This will allow you to deploy all constraints without errors, but you still need to disable them in post deployment script.


In Postgres, how can I calculate cume_dist (relative rank) over the entire table, but only for a single row? I have a table with the following schema:

CREATE TABLE foo (
  data  TEXT   NOT NULL,
  count BIGINT NOT NULL DEFAULT 1,
  PRIMARY KEY (data)
);

I'd like to get the relative rank of a single entry, based on count, as it relates to the entire table, but I'm not sure how to structure the OVER clause in order to do this. Running the following gives me a rank of 1, so I'm assuming the OVER clause is getting limited to a single row, rather than all the rows in the table, as dictated by the WHERE clause.

SELECT cume_dist() OVER(ORDER BY f.count ASC) AS rank
FROM foo AS f
  WHERE f.data = 'bar';

rank
------
    1
(1 row)

Thoughts?

You need to first apply the function in the whole table and then restrict the results with WHERE:

SELECT r.rank
FROM
  ( SELECT f.data, cume_dist() OVER (ORDER BY f.count ASC) AS rank
    FROM foo AS f
  ) AS r
WHERE r.data = 'bar';

Alternatively, you could calculate the function with 2 subqueries:

SELECT
    ( SELECT COUNT(*)
      FROM foo
      WHERE count <= (SELECT count FROM foo WHERE data = 'bar')
    )
  * 1.0 /
    ( SELECT COUNT(*) FROM foo )
  AS rank ;

or with the new FILTER syntax:

SELECT COUNT(*) FILTER
                (WHERE f.count <= (SELECT count FROM foo WHERE data = 'bar'))
       * 1.0 / COUNT(*) AS rank
FROM foo AS f ;


Converting EER diagram to ER diagram? I'm studying for a database class and came across this EERD:

Very simple EERD however, I'm wondering- how can I go about and convert this to a simple ERD instead? From my understanding, the only thing different about an EER is that it shows the specialization, generalization, partitioning, and aggregation - for this above diagram: specialization of course.

So if I were to remove the specialization entities for VEHICLE (Motorcycle, Car, SUV, Truck), would this diagram then be considered an ERD? That wouldn't make sense though, wouldn't it be important to show those relationships on an ER diagram?

If someone can tell me how I would go about converting this to an ER diagram, that would really help.

The only way I know of is one I can't recommend.

If you convert your EERD into a relational design (with tables, etc.) using some technique like "class table inheritance", you have now disguised the fact that the original EERD contained a generalization/specialization pattern.

If you now turn around and reverse engineer that back to an ERD, you have something that satisfies the requirements of your question.  But that's grotesque.  It conceals the original intent rather than revealing it.  Why would anyone ever want to do that?


password expire duration and maximum invalid login attempts how to set password expire duration and maximum invalid login attempts for user under Cassandra DB ?

for example user test tried to login with incorrect password 5 times , that should should get blocked or write this info to logs.

password expire duration  how we can configure ?

Cassandra doesn't have such functionality (yet). In DSE you can enable audit logs, and log all authentication attempts & their result - but I don't remember such functionality for Cassandra


How to move the ownership/copy a table from one user to another? I've been tasked with doing some tidying of our database. I'm not a DBA expert so here I am. (We don't have a DBA expert either).

We have a user Old_User and another user New_User.

Old_User owns a set of tables, Table_A and Table_B.
New_User has synonyms t_a and t_b respectively.

Can anyone give me some advice on how I should go about moving the ownership of Table_A and Table_B to New_User.

There are tables owned by New_User which have foreign keys to the tables owned by Old_User. I'm not sure if that complicates anything? Ideally, all referential integrity and constraints should be the same.

The goal will be to delete the Old_User eventually.

You can't change the owner of a table.

You can create a new table that is owned by NEW_USER, copy the data from the old table to the new table, drop the foreign key constraints that reference the old table, and create new foreign key constraints that reference the new table.  But that's realistically going to require a downtime window since the tables need to be static while this copy is going on.

There are various ways to create a new table that is a copy of the old table.  Personally, I'd use your favorite PL/SQL IDE (i.e. SQL Developer, TOAD, etc.) to generate the DDL for Old_User.Table_A and then manually edit the DDL to create New_User.Table_A.  You could also do an export & import with the FROMUSER and TOUSER parameters.  Or you can use the DBMS_METADATA package to get the DDL

SELECT dbms_metadata.get_ddl( 'TABLE', 'TABLE_A', 'OLD_USER' )
  FROM dual

and edit that.

Once you've created the new table, you can copy the data over

INSERT INTO new_user.table_a( <<list of columns>> )
  SELECT <<list of columns>>
    FROM old_user.table_a

Then, you'll need to go through each child table dropping the old foreign key constraint and creating the new constraint, i.e.

ALTER TABLE child_of_a
 DROP CONSTRAINT fk_constraint_name;

ALTER TABLE child_of_a
  ADD CONSTRAINT fk_constraint_name FOREIGN KEY (name_of_column_in_child)
      REFERENCES new_user.table_a( name_of_column_in_parent )

If you have enough child tables, you could potentially write a script that dynamically generates that DDL.

Once that's done, you can drop the old table and let the applications start modifying the data again.


Can I safely kill OPTIMIZE TABLE on an InnoDB table? MySQL's documentation for kill warns:

  Warning

  Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to such a table fail until you optimize or repair it again (without interruption).

That's for MyISAM.

Is it also unsafe to kill an OPTIMIZE TABLE process running against an InnoDB table?

According to MySQL Certification Guide :

  The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting
  it. This involves reclaiming unused space resulting from deletes and
  updates, and coalescing records that have become split and stored
  non-contiguously. OPTIMIZE TABLE also sorts the index pages if they
  are out of order and updates the index statistics

  Also works for InnoDB tables, but maps to ALTER TABLE, which rebuilds
  the table. This updates index statistics and frees space in the
  clustered index.

So In Case of InnoDB :

Innodb is ACID compliant, the optimize table simply copies all records into a new table

If the index pages are not sorted, sort them,
If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
If the table has deleted or split rows, repair the table.

And if you try to kill it when it's running you will not loose any records.

Additionally, case of InnoDB with innodb_defragment=1, there will be no ROLLBACK required, as in this case OPTIMIZE TABLE is incremental.


What is the cardinality of entity relationship between Employee and Department? Recently, a friend of mine asked me this question about the cardinality between  a Department and Employee entities, I replied many to many, then he, my friend, showed me drawings from the internet which says otherwise, one to one. I objected the idea and told him don’t trust everything on the internet. However, after I examined books on Database Concepts I found the one to one relationship that my friend showed to me. As an example I took the following snapshot from “The Morgan Kaufmann Series in Database Management System: Database Modeling and Design 4th Edition”

Fortunately, these guys mapped the entities depicting the foreign key relationship. Suppose after a year another employee is assigned to manage a department previously being managed by another employee. At this point are we going to update the mgr_id column and loose the history of the department and employee relationship?

What is the cardinality of (this) entity relationship between Employee and Department?

Yes, this relationship ("managed-by") is an example of a one-to-one relationship. This doesn't forbid the two entities form having other relationships, (ie. "works-in") that are of different cardinality, one-to-many or many-to-many.

  Suppose after a year another employee is assigned to manage a department previously being managed by another employee. At this point are we going to update the mgr_id column and loose the history of the department and employee relationship?

Yes, the department . mgr_id will be updated and the historical info will be lost. If you want to preserve historical data, the tables should be designed with this in mind. Some DBMS provide built-in support for such temporal data. For some more details, see Temporal databases.


Amazon: How to change database name in Amazon RDS MySQL? I want to change MYSQL database name of the DB instance of Amazon RDS.

How it can be possible?

you can rename the database using the following command but make sure to take backup first

CREATE database new_db_name;
RENAME TABLE db_name.table1 TO new_db_name, db_name.table2 TO new_db_name;
DROP database db_name;

references:
http://www.rndblog.com/how-to-rename-a-database-in-mysql/
https://stackoverflow.com/questions/12190000/rename-mysql-database


DELETE statement conflicted with the REFERENCE constraint My situation looks like this:

Table STOCK_ARTICLES:

ID *[PK]*
OTHER_DB_ID
ITEM_NAME

Table LOCATION:

ID *[PK]*
LOCATION_NAME

Table WORK_PLACE:

ID *[PK]*
WORKPLACE_NAME

Table INVENTORY_ITEMS:

ID *[PK]*
ITEM_NAME
STOCK_ARTICLE *[FK]*
LOCATION *[FK]*
WORK_PLACE *[FK]*

The 3 FKs in INVENTORY_ITEMS reference the "ID" columns in the respective other tables, obviously.

The relevant tables here are STOCK_ARTICLE and INVENTORY_ITEMS.

Now there is a SQL job consisting of several steps (SQL scripts) that "synchronizes" the database mentioned above with another database (OTHER_DB). One of the steps inside this job is for "cleanup". It deletes all records from STOCK_ITEMS where there's no corresponding record in the other database with the same ID.
It looks like this:

DELETE FROM STOCK_ARTICLES
 WHERE
    NOT EXISTS
     (SELECT OTHER_DB_ID FROM
     [OTHER_DB].[dbo].[OtherTable] AS other
               WHERE other.ObjectID = STOCK_ARTICLES.OTHER_DB_ID)

But this step always fails with:

  The DELETE statement conflicted with the REFERENCE constraint
  "FK_INVENTORY_ITEMS_STOCK_ARTICLES". The conflict occurred in database
  "FIRST_DB", table "dbo.INVENTORY_ITEMS", column 'STOCK_ARTICLES'.
  [SQLSTATE 23000] (Error 547)  The statement has been terminated.
  [SQLSTATE 01000] (Error 3621).  The step failed.

So the problem is that it can't delete records from STOCK_ARTICLES when they are referenced by INVENTORY_ITEMS.
But this cleanup needs to work.
Which means that I probably have to extend the cleanup script so that it first identifies the records that should be deleted from STOCK_ITEMS, but can't because the corresponding ID is referenced from inside INVENTORY_ITEMS.
Then it should first delete those records inside INVENTORY_ITEMS, and after that delete the records inside STOCK_ARTICLES.
Am I right?
How would the SQL code look like then?

Thank you.

That is the whole point of foreign key constraints: they stop you deleting data that is referred to elsewhere in order to maintain referential integrity.

There are two options:

Delete the rows from INVENTORY_ITEMS first, then the rows from STOCK_ARTICLES.
Use ON DELETE CASCADE for the in the key definition.

1: Deleting In Correct Order

The most efficient way to do this varies depending on the complexity of the query that decides which rows to delete. A general pattern might be:

BEGIN TRANSACTION
SET XACT_ABORT ON
DELETE INVENTORY_ITEMS WHERE STOCK_ARTICLE IN (<select statement that returns stock_article.id for the rows you are about to delete>)
DELETE STOCK_ARTICLES WHERE <the rest of your current delete statement>
COMMIT TRANSACTION

This is fine for simple queries or for deleting a single stock item, but given your delete statement contains a WHERE NOT EXISTS clause nesting that within WHERE IN might produce a very inefficient plan so test with a realistic data set size and rearrange the query if needed.

Also note the transaction statements: you want to make sure both the deletes complete or neither of them do. If the operation is already happening within a transaction you will obviously need to alter this to match your current transaction and error handling process.

2: Use ON DELETE CASCADE

If you add the cascade option to your foreign key then SQL Server will automatically do this for you, removing rows from INVENTORY_ITEMS to satisfy the constraint that nothing should refer to the rows you are deleting. Just add ON DELETE CASCADE to the FK definition like so:

ALTER TABLE <child_table> WITH CHECK
ADD CONSTRAINT <fk_name> FOREIGN KEY(<column(s)>)
REFERENCES <parent_table> (<column(s)>)
ON DELETE CASCADE

An advantage here is that the delete is one atomic statement reducing (though, as usual, not 100% removing) the need to worry about transaction and lock settings. The cascade can even operate over multiple parent/child/grand-child/... levels if there is only one path between parent and all the descendants (search for "multiple cascade paths" for examples of where this might not work).

NOTE: I, and many others, consider cascaded deletes to be dangerous so if you use this option be very careful to properly document it in your database design so you and other developers don't trip over the danger later. I avoid cascading deletes wherever possible for this reason.

A common problem caused with cascaded deletes is when someone updates data by dropping and recreating rows instead of using UPDATE or MERGE. This is often seen where "update the rows that already exist, insert those that don't" (sometimes called an UPSERT operation) is needed and people unaware of the MERGE statement find it easier to do:

DELETE <all rows that match IDs in the new data>
INSERT <all rows from the new data>

than

-- updates
UPDATE target
SET    <col1> = source.<col1>
  ,    <col2> = source.<col2>
       ...
  ,    <colN> = source.<colN>
FROM   <target_table> AS target JOIN <source_table_or_view_or_statement> AS source ON source.ID = target.ID
-- inserts
INSERT  <target_table>
SELECT  *
FROM    <source_table_or_other> AS source
LEFT OUTER JOIN
        <target_table> AS target
        ON target.ID = source.ID
WHERE   target.ID IS NULL

The problem here is that the delete statement will cascade to child rows, and the insert statement won't recreate them, so while updating the parent table you accidentally lose data from the child table(s).

Summary

Yes, you have to delete the child rows first.

There is another option: ON DELETE CASCADE.

But ON DELETE CASCADE can be dangerous, so use with care.

Side note: use MERGE (or UPDATE-and-INSERT where MERGE is not available) when you need an UPSERT operation, not DELETE-then-replace-with-INSERT to avoid falling into traps laid by other people using ON DELETE CASCADE.


SQL Server 2017 and TLS - client requirements I am testing something around SSL\TLS and SQL Server 2017. I have enabled TLS on a test SQL Server instance by creating a self-signed cert and doing the required steps to add it to the Windows certificate store and then adding it to SQL Server in Configuration Manager. I have set also 'Force Encryption' to 'On', and restarted the service. So as far as I can ascertain that is enough to enable TLS on the SQL Server side.
For client applications on other computers connecting to the SQL Server instance I am confused as to what the connection string requirements are for connections using SqlClient. My understanding from reading up on it was that 'TrustServerCertificate=true' would have to be added to the connection string, but it seems possible to connect without that.
Is this because the certificate on the server is seen as being from a trusted authority?

no,
TrustServerCertificate=true

means more or less
ignore certification source verification
It's used in env like your when a self-signed certificate is used for encryption.
It's better to use the certificate produced by a CA and distributed by your AD GPO.
In that case you don't need to use 'TrustServerCertificate=true'; but be sure to use in the connection string the FQDN of the server that is inside the certificate.


T-SQL Syntax error copying views dynamically I'm creating a stored procedure to copy all views from one database to another using the following code.

ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB NVARCHAR(200))
AS
BEGIN
    DECLARE @SQL2 NVARCHAR(MAX)
    SET @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']','')
    SET @DestinationDB = REPLACE(REPLACE(@DestinationDB, '[', ''), ']','')

    SET @SQL2 =
        'USE [' + @SourceDB + ']
        BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE CUR_V CURSOR FOR
        SELECT sc.text
        FROM sys.views av
            JOIN sys.syscomments sc ON sc.id = av.object_id
        OPEN CUR_V
        FETCH NEXT FROM CUR_V INTO @SQL
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC sp_executesql N''USE [' + @DestinationDB + '] EXEC sp_executesql '' + @SQL +  ''''
            FETCH NEXT FROM CUR_V INTO @SQL
        END
        CLOSE CUR_V
        DEALLOCATE CUR_V
        END'

    EXEC sp_executesql @SQL2
END

The stored procedure is returning the error INCORRECT SYNTAX NEAR '+'

I'm receiving the error at the first + before @SQL

As far as I can tell this syntax should be correct.

Here is the output for @SQL2 to help get rid of some of the quote nesting in case that is helpful.

USE [SOURCEDBNAME]
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE CUR_V CURSOR FOR
    SELECT sc.text
    FROM sys.views av
        JOIN sys.syscomments sc ON sc.id = av.object_id
    OPEN CUR_V
    FETCH NEXT FROM CUR_V INTO @SQL
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_executesql N'USE [DESTINATIONDBNAME] EXEC sp_executesql ' + @SQL +  ''
        FETCH NEXT FROM CUR_V INTO @SQL
    END
    CLOSE CUR_V
    DEALLOCATE CUR_V
END

You cannot do EXEC sp_executesql on combined literals.  You must use a variable as in:

ALTER PROCEDURE [dbo].[spCreateViews] (@SourceDB NVARCHAR(200), @DestinationDB NVARCHAR(200))
AS
BEGIN
    DECLARE @SQL2 NVARCHAR(MAX)
    SET @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']','')
    SET @DestinationDB = REPLACE(REPLACE(@DestinationDB, '[', ''), ']','')

    SET @SQL2 =
        '
        BEGIN
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE @cmd NVARCHAR(MAX);
        DECLARE CUR_V CURSOR FOR
        SELECT sm.definition
        FROM ' + QUOTENAME(@SourceDB) + '.sys.views av
            JOIN ' + QUOTENAME(@SourceDB) + '.sys.sql_modules sm ON sm.object_id = av.object_id
        OPEN CUR_V
        FETCH NEXT FROM CUR_V INTO @SQL
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC ' + QUOTENAME(@DestinationDB) + '.sys.sp_executesql @SQL;
            FETCH NEXT FROM CUR_V INTO @SQL
        END
        CLOSE CUR_V
        DEALLOCATE CUR_V
        END'

    EXEC sp_executesql @SQL2
END

This code uses @SourceDB and @DestinationDB in the dynamic SQL to correctly identify the source and destination databases.  This negates the need for the USE [Database] statement on line after SET @SQL2 =.


MongoDb max timeout for all queries I am using MongoDb/PHP on a Linux server.
The problem is that, if a query takes too long to execute, the mongo server stop (apache keeps running).
So is there any way ,so that I can limit the time for a query to execute, and kill the process automatically like in php-  max execution time  ?

Till now I have found:

socketTimeoutMS, but I found some where , it will not kill the process.
cursor.maxTimeMS() , but I have to define it for each query.

So can anyone please suggest which approach I should follow so that

the query is automatically killed, if it takes more time,
I don't need to change every query.

As at MongoDB 3.4, the $maxTimeMS value is a cursor option that needs to be set by the client/driver making the request.

There is currently no equivalent server configuration directive, but there is a relevant feature request to watch/upvote in the MongoDB issue tracker: SERVER-13775:
maxTimeMS on an instance/database level.

There are some potential caveats to consider for a server-level default $maxTimeMS:

This would be a breaking behaviour change for use cases that have intentional long-running query execution times. For example, backup tools such as mongodump would have to be updated to explicitly set a no-MaxTimeMS option or handle unexpected early termination.
$maxTimeMS applies to cumulative time for query execution (including multiple batches of results for the same cursor) so this option may have an unexpected outcome for queries returning a larger number/size of results.

Generally your application will have more context on which queries can be terminated, so I suspect a closer fit would be adding the ability to set a default $maxTimeMS per authenticated user/role. The closest related feature suggestion is currently SERVER-15072:
Limit resource usage for certain users.


How can I drop an index on all partitions? If I create an index on a partitioned table, each partition gets its own index. These are not dropped if I drop the main index.

Is there an easy way to do this?

I have written a function that looks for all the indexes that match the top level index name with a partition suffix, and that works fine for indexes that were created on partitions.

However, when Greenplum adds a new partition through a default partition split, it generates a new index for that partition with a totally different naming convention.

Existing partitions get a name like indexname_1_prt_partitionname

New partitions get a name like tablename_1_prt_partitionname_indexcolumn

Any idea how I can identify that the new partition index is part of the parent index, when the name doesn't match? Or do I just call my index deleter twice with two different patterns to match?

I will be using either Bell's query below, or this one which is adapted to take a partition index (in case we have already deleted the head index):

SELECT child_index.indexrelid::regclass
  FROM pg_index AS partition_index
    -- Find the partition that the partition index is on
    INNER JOIN pg_partition_rule parindex_rule ON parindex_rule.parchildrelid = partition_index.indrelid
    -- Follup up to the partitioning scheme
    INNER JOIN pg_partition ON pg_partition.oid = parindex_rule.paroid
    -- Follow the links through to the individual partitions
    INNER JOIN pg_partition_rule ON pg_partition_rule.paroid = pg_partition.oid
    -- Find the indexes on each partition
    INNER JOIN pg_index AS child_index ON child_index.indrelid = pg_partition_rule.parchildrelid
      -- Which are on the same field as the named index
      AND child_index.indkey = partition_index.indkey
      -- Using the same comparison operator
      AND child_index.indclass = partition_index.indclass
  -- Filtered for the index we're trying to drop
  WHERE partition_index.indexrelid = 'schema.partitionindexname'::regclass

Greenplum doesn't (as at version 4.3.8) maintain a record in the catalogue linking indexes on partitions to indexes on base tables. The best option is to follow the partitions and find indexes on the partitions matching the definition of the base index.

CREATE OR REPLACE FUNCTION drop_child_indexes (index_name varchar)
RETURNS VOID
AS
$functionBody$
DECLARE
  child_index_name varchar;
BEGIN

  FOR child_index_name IN
    SELECT child_index.indexrelid::regclass
      FROM pg_index AS parent_index
        -- Find the partitioning scheme for the table the index is on
        INNER JOIN pg_partition ON pg_partition.parrelid = parent_index.indrelid
        -- Follow the links through to the individual partitions
        INNER JOIN pg_partition_rule ON pg_partition_rule.paroid = pg_partition.oid
        -- Find the indexes on each partition
        INNER JOIN pg_index AS child_index ON child_index.indrelid = pg_partition_rule.parchildrelid
          -- Which are on the same field as the named index
          AND child_index.indkey = parent_index.indkey
          -- Using the same comparison operator
          AND child_index.indclass = parent_index.indclass
      -- Filtered for the index we're trying to drop
      WHERE parent_index.indexrelid = $1::regclass::oid
      -- Drop leaves first, even if it doesn't really matter in this case
      ORDER BY pg_partition.parlevel DESC

LOOP
  RAISE NOTICE '%', child_index_name||' ';
  EXECUTE 'DROP INDEX '||child_index_name||';';
END LOOP;

END
$functionBody$
LANGUAGE plpgsql;

If there another index with the same definition (fields, comparison operators) it's partition indexes will also be dropped. Not a perfect answer but better than substring matching.


Traffic simulator tool for Mariadb? I made a MariaDB Tuning course, and it's very important to test the system with real traffic and real data, simulate a Production server basically.

The data is easy to replicate, just a dump, or even replication for have the database updated. But how do you test your traffic for make statistics? You can't replicate your users and their consumption of the server's resources, can you?

Do you know any tool that I could use for simulating this traffic?

There are no 100% real workload replication tools for MySQL (at least 100% free)

All you could do is only simulate:

using HammerDB
a very interesting tool from Percona, Percona playback and related article
you can also look for Apache JMeter
you can sometimes enable the general log on MySQL server, and use it to prepare tests with JMeter (after manual preparation)

With Playback - You can not replay collisions as it was on real server
With JMeter (and similar). You would need to define queries by yourself.

Depending from what you want to demonstrate it could be easy or hard to prepare a proper simulation.

Sometimes for proper problem simulation all you need is mysqlslap. Run 3-4 instances with different queries and you can simulate locks, deadlocks etc.


SQL80001: Incorrect Syntax:" Create Procedure" must be the only statement in the batch When I try to create a procedure, I get an error message:

  SQL80001: Incorrect Syntax:" Create Procedure" must be the only statement in the batch

This is my procedure:

CREATE PROCEDURE [dbo].[USP_UserRoles_Select]
(
    @Rolename VARCHAR(30) = ''
)
AS
BEGIN
    Select ID,Name
    FROM
        AspNetRoles
    WHERE
        Name like  @Rolename +'%'
END

What does the error mean and how to fix it?

You just need to put a GO batch separator around your CREATE PROCEDURE. You have something else above it, and it needs to be in its own batch. Hence the GO that's needed.

GO
CREATE PROCEDURE [dbo].[USP_UserRoles_Select]
(
    @Rolename VARCHAR(30) = ''
)
AS
BEGIN
    Select ID,Name
    FROM
        AspNetRoles
    WHERE
        Name like  @Rolename +'%'
END
GO

A GO is also needed after the script if there is code following the END. The BEGIN and END statements are superfluous so all code in the batch will be part of the stored procedure.


PostgreSQL directory very full In the /var/lib/pgsql/data/base Postgres that in version 8.4 and it was identified that the error single bank is with 37G which is absurd, is not already realized vacuum full declined nothing and also I put the autovacuum as off in postgresql.conf. the only thing I can do is back up and after that uninstall and reinstall the bank to resolve palliative-mind.

Can someone help me?

I am using Centos 6.7 and I know the Bank that's full ...

Vacuum (even FULL) isn't the most efficient when it comes to reclaiming space from bloated indexes. And in PostgreSQL 8.x it was even more the issue.
I do remember reading about tests where dump, drop and restore might be indeed faster and gets better results than vacuum full.
And this is what I would suggest if possible.

There is another option you might try first though, quoting PostgreSQL 8.4 documentation:

  The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would. The FULL option does not shrink indexes; a periodic REINDEX is still recommended. In fact, it is often faster to drop all indexes, VACUUM FULL, and recreate the indexes.


'Cleanse' a SQL Server database file created with Instant File Initialization enabled? I have Instant File Initialization enabled on our SQL Server, so the 'empty' part of the database file is not zeroed before being allocated.  My understanding is that this means that the file could contain 'deleted' data.

So now I want to send a copy of a database (probably a backup file) outside of the company.  But there's all that potentially sensitive 'deleted' data sitting around inside the file.  Now I would like to zero the unused portion of the file.

Is that possible?  I imagine I could create a new database and copy everything over, or perhaps restore a copy of the database to another server without Instant File Initialization enabled and then be aggressive with a ShrinkFile command to remove most or all of the unused portion of the database file, but is there a less manual and time consuming method?  Ideally a command to tell SQL to zero the file as it would have done if Instant File Initialization was not enabled.

A SQL Server BACKUP only backs up extents that are being used to hold data.  The unused extents are left behind by the backup.  When a page is used for data it will be formatted for use as needed, so that page would be free of old data.

Therefore, all you should need to do is backup the database and restore it elsewhere.  The restored files will be of the same size as the original database, but the unused extents will be created using the capabilities of the target server.  This may be initialized fully or instantly initialized using the blocks of disk on the target server.

However, because extents are the level at which backups happen the unused pages in the extent could still have potential to expose some data when restored on another server.  Not as much as could be exposed on the source server, since the unused extents are not restored.


Creating a read only replica of SQL Server database for reports I've faced a problem when my .Net web application is running slow, even when I've optimized all queries. As it turned out another developer, who is making reports runs a bunch of non optimized queries all the time. So I've decided to create a separate server for him. Currently I created Jobs making backup, copying it to another server and restoring it. But it's temporary solution, so in order for second server have up to date data or at least with minimal latency I'm investigating an opportunity to create a read only replica.

As I'm not a DBA I've read a bunch or articles regarding SQL Server replication mechanism. The best option for me is when my PROD web application isn't affected by replication at all, I mean lots of lock on syncronizing tables. I don't need a real time sync as mirroring does it, also I don't need any king of cluster solution, just read only syncing copy of my PROD database. So I've choosed Transactional replication mechanism(not updatable) with asynchronous distributing policy(on schedule).

So I've some question:

Does Transactional replication suit my problem the most among SQL Server replication mechanisms?
If I have an opportunit to migrate from SQL Server 2008 to SQL Server 2012 would transactional replication have some breaking changes? I've read an article on technet that there might occur errors?
Isn't SQL Server 2012 Always On mechanism better to solve my problem(I'm considering this option as last one, as I'm still using 2008 R2, and yet migration to 2012 is planned by not soon)?

Does Transactional replication suit my problem the most among SQL
  Server replication mechanisms?

Out of all of the replication options - Transactional sounds like it is what helps you the most here. It gives the minimal latency and it doesn't need to be (but can be) bidirectional. You aren't merging changes and don't need Peer to Peer.

That said - Replication isn't "free" -  you need to administer and monitor it and a poor setup could make performance problems worse. You should learn about the best options for performance and the best setup for performance and keep that in mind and keep your replication environment monitored.

Transactional replication is "easier" than Merge replication. But that doesn't mean you will always have a smooth sail and can always get away without someone with replication experience helping out.

  If I have an opportunity to migrate from SQL Server 2008 to SQL Server
  2012 would transactional replication have some breaking changes? I've
  read an article on technet that there might occur errors?

Unfortunately this is an "it depends" type of question. We could theoretically give you answers from our own experiences, but you will need to test. If you do bump into issues they will not necessarily be impossible issues that you cannot fix, they will require testing and tweaking.

  Isn't SQL Server 2012 Always On mechanism better to solve my
  problem(I'm considering this option as last one, as I'm still using
  2008 R2, and yet migration to 2012 is planned by not soon)?

In SQL Server 2012 AlwaysOn has something for you here that works potentially. Availability Groups are an answer here with the concept of an Active Secondary for reading purposes.  But there are licensing concerns - you need to be in Enterprise on both the primary and the read only/active secondary. With the replication approach you still need to be licensed on both ends, but this is a good option for sure. It, too, has overhead associated and some administrative concerns that you should learn about - but it makes a great option.


dummyuser creating users I'm writing a script which weekly updates the users who have access to a database (all users from a specific group in our AD).

In my script I have to authenticate me as an user with the privileges to create users and to grant them privileges.

I dislike using the root user (and his plaintext password!) in a script, so I hope to create a dummyuser with as little as possible privileges, I want to reduce the security risk.

Is there a possible way and will it be secure? Which privileges will this dummyuser need?

create a user with grant option privilege.

GRANT [whatever privilege to that normal user] TO 'normal_user'@'ipaddress' WITH GRANT OPTION IDENTIFIED BY 'password';
FLUSH PRIVILEGES;


Internal error code, kksgaItLvl_Setup A simple query fails with the error. Has anyone faced this issue or has any solutions?

   SELECT MAX(some_value)
      FROM some_table
    WHERE some_id = 62328188199
       AND some_name = 'someParameter'
       AND someother_name = 'name';

  ORA-00600: internal error code, arguments: [kksgaItLvl_Setup: desc],
  [3], [3], [], [], [], [], [], [], [], [], []

(Link accessible only by registration to Oracle Support):

Bug 21266085 - ORA-600 [kksgaItLvl_Setup: asc] from a query against an interval partitioned table (Doc ID 21266085.8)

The fix for 21266085 is first included in

12.2.0.1 (Base Release)
12.1.0.2.161018 (Oct 2016) Database Patch Set Update (DB PSU)
12.1.0.2.160719 (Jul 2016) Database Proactive Bundle Patch
11.2.0.4.160719 Exadata Database Bundle Patch (Jul 2016)
12.1.0.2.160419 (Apr 2016) Bundle Patch for Windows Platforms


Is there a name for this database schema of key values? We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one row per entity per attribute):

Before: one column per attribute

ID   Ht_cm   wt_kg   Age_yr  ...
1      190      82     43    ...
2      170      60     22    ...
3      205      90     51    ...

After: one column for all attributes

ID    Metric   Value
 1     Ht_cm     190
 1     Wt_kg     82
 1     Age_yr    43
 1      ...
 2     Ht_cm     170
 2     Wt_kg     60
 2     Age_yr    22
 2     ...
 3     Ht_cm     205
 3     Wt_kg     90
 3     Age_yr    51
 3     ...

Is there a name for this database structure?  What are the relative advantages?  The old way seems easier to place validity constraints on specific attributes (non-null, non-negative, etc.) and easier to calculate averages.  But I can see how it might be easier to add new attributes without refactoring the database.  Is this a standard/preferred way of structuring data?

It's called Entity-Attribute-Value (also sometimes 'name-value pairs') and it's a classic case of "a round peg in a square hole" when people use the EAV pattern in a relational database.

Here's a list of why you shouldn't use EAV:

You can't use data types. It doesn't matter if the value is a date, a number or money (decimal). It's always going to be coerced to varchar. This can be anything from a minor performance problem to a massive gut-ache (ever had to chase down a one-cent variation in a monthly roll-up report?).
You can't (easily) enforce constraints. It requires a ridiculous amount of code to enforce "Everyone needs to have a height between 0 and 3 metres" or "Age must be not null and >= 0", as opposed to the 1-2 lines that each of those constraints would be in a properly-modelled system.
Related to above, you can't easily guarantee that you get the information you need for each client (age might be missing from one, then the next might be missing their height etc.). You can do it, but it's a hell of a lot more difficult than SELECT height, weight, age FROM Client where height is null or weight is null.
Related again, duplicate data is a lot harder to detect (what happens if they give you two ages for one client? De-EAVing the data, as below, will give you two rows of results if you have one attribute doubled. If one client has two separate entries for two attributes, you'll get four rows from the query below).
You can't even guarantee that the attribute names are consistent. "Age_yr" might become "AGE_IN_YEARS" or "age". (Admittedly this is less of a problem when you're receiving an extract versus when people are inserting data, but still.)
Any sort of nontrivial query is a complete disaster. To relationalise a three-attribute EAV system so you can query it in a rational fashion requires three joins of the EAV table.

Compare:

SELECT cID.ID AS [ID], cH.Value AS [Height], cW.Value AS [Weight], cA.Value AS [Age]
FROM (SELECT DISTINCT ID FROM Client) cID
      LEFT OUTER JOIN
    Client cW ON cID.ID = cW.ID AND cW.Metric = "Wt_kg"
      LEFT OUTER JOIN
    Client cH ON cID.ID = cH.ID AND cW.Metric = "Ht_cm"
      LEFT OUTER JOIN
    Client cA ON cID.ID = cA.ID AND cW.Metric = "Age_yr"

To:

SELECT c.ID, c.Ht_cm, c.Wt_kg, c.Age_yr
FROM Client c

Here's a (very short) list of when you should use EAV:

When there's absolutely no way around it and you have to support schema-less data in your database.
When you just need to store "stuff" and don't expect to have to need it in a more structured form. Beware, though, the monster called "changing requirements".

I know I just spent this entire post detailing why EAV is a terrible idea in most cases - but there are a few cases where it's needed/unavoidable. however, most of the time (including the example above), it's going to be far more hassle than it's worth. If you have a requirement for wide support of EAV-type data input, you should look at storing them in a key-value system, e.g. Hadoop/HBase, CouchDB, MongoDB, Cassandra, BerkeleyDB.


Keeping version history of functions in PostgreSQL For many of my tables, I've added auditing triggers (based on this wiki page).  It works very well and has helped me several times figure out who changed what in the system.  We have a Python/Django application that sits on top of the data and that code is tracked in Git.  However, there seems to be one area of our system where changes are not tracked very well.  And that's the function/triggers, etc in PostgreSQL.  I wish there was a way I could add a similar audit capability to the schema as I have with the data itself.

How do DBA track these changes?  Note: I'm in a position where there is more than one person with sufficient privileges to "CREATE OR REPLACE" a function, so I can't necessarily count on a person to write a script and check it into Git and I certainly can't force them.  If possible, it needs to be automatic.

I've thought about writing a python script to "create" a script file and write it to a file and then programmatically commit changes to a git repo.

Another option would be to add some tables to my audit schema and just query each night and look for changes and right them to a table.  Certainly could work, but not quite as nice as being able to do a diff/blame via git.

This may not be the greatest question because I'm not exactly sure what I'm looking for and there may not be an exact "right" answer, but I would like to know what people do.  I've done some "googling" on the topic, but I'm not finding a lot (maybe I'm just using the wrong terms).

Finally, I'm currently using PostgreSQL 9.1.

Right now there isn't really a single wonderful option for this.

You need to make sure people use revision control properly and consistently.

Some after-the-fact checks can be used to query for unexpected procedure changes and alert if the pg_proc.prosrc column doesn't match what you have in revision control.

If necessary you can force all changes through a single person's approval. Alternately you can deny access to create and update procedures to all accounts except one - and have that account automatically apply changes that're committed to git via a hook. I don't recommend this though - it'll make operations very restrictive and make it hard to fix unexpected issues.


After delete audit table doesn't have the rows that were deleted CREATE TRIGGER trgEmp ON Emp
AFTER DELETE

AS
BEGIN

IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        INSERT INTO EmpAudit
        (
            Change,
            Date,
            empID,
            fName,
            lName,
            ssn,
            address,
            state,
            zip,
            serialNoJob,
            dob,
            classification,
            pay
        )
 SELECT 'DELETED', GETDATE(), d.empID, d.fName, d.lName, d.ssn, d.address, d.state, d.zip, d.serialNoJob,
        d.dob, d.classification, d.pay
        FROM Emp e inner join deleted d on e.empID = d.empID
    END

END
GO

This trigger also had AFTER UPDATE, INSERT. The updates and inserts work as expected. So I decided to try the trigger with delete by itself. I tested again by deleting from Emp table, I still get
0 rows affected
1 rows affected
and nothing is actually inserted into the EmpAudit Table

After delete trigger is executed after the record has been removed from the table.

Joining a table Emp will yield no result because record with that ID does not exist in that table.

Also note that inserted table will always be empty in after delete trigger.


Upgrading OS and then upgrading to SQL Server 2016 One of my clients would like to have the OS upgraded to Windows 2012 enterprise and then the plan is to upgrade the SQL Server to 2016. I would like to know what would be the steps in doing so and how to revert the same if something bad happens in between and the upgrade fails. I have not really done this in the past so I do not have knowledge on the same. Below are the details of my client system

SQL Server 2008 R2 standard
OS: Windows 2008 R2 standard
RAM: 48 GB (32 GB usable)
Server: Vmware (Virtual platform)

OS changes, in my opinion, should always be done side-by-side, never in-place.  In this scenario, SQL 2016 requires Windows Server 2012R2 or better, so you'll need to view it as 2 separate, yet equally important migrations.  My recommendation would be to do this in 2 phases:

1- Stand up a new server with Windows 2012R2.  Odds are there will be hardware upgrades on the new server as the old server was probably built years ago. install the current version of SQL that you are using then migrate a copy of production over to it.  Bang away at it and make sure the OS upgrade isn't causing any undesirable behavior.  After testing is complete, migrate production to the new server (SQL 2008R2, Windows 2012R2) and let it soak in for a few weeks.  If everything is good and you've addressed any issues, proceed to step 2.

2- Stand up a new server with Windows Server 2012R2 and SQL 2016, copy production DBs and test again.  Be careful with compatibility mode of the DBs as anything greater than 2014 (120) will have the new cardinality estimator which can effect query performance.  Once testing on 2016 is complete, then migrate production again to complete the upgrade.

This is by no means a comprehensive list of things that you need to accomplish but an abridged version that just hits the overall flow of doing an upgrade like this.

Can you do it all in one step? Sure, but then if you hit an issue, the line between the SQL upgrade (which is a pretty major version jump) and the OS upgrade may become blurred and you won't know which upgrade is giving you a headache.


Is there to get space used in files for SQL Server database without negatively affecting IO? I need to get the space used within each database file across large numbers (1000+) of databases on a regular basis without hitting disk IO too hard.

I thought I could simple call the FILEPROPERTY(@file, 'SpaceUsed') function or sp_spaceused - however, the disk IO spikes to near 100% for quite a few seconds when I loop over a large number of databases calling either of these functions.

I suppose that SQL Server must interally have some idea of how much space is used (or left) in its files so that it can auto-grow. I wonder if there is some way to get hold of these values (even if they are less accurate than FILEPROPERTY) without causing such as massive hit on IO?

Thanks :)

Update

I have also tried summing the total_pages from sys.allocation_units which seems to be another approach that SSMS uses - see here, but it's equally slow sadly.

Update 2

I realise I can also use DBCC showfilestats (and multiply the UsedExtents by 64), to get the used size of the database file and DBCC SQLPERF (LOGSPACE) to get the used size of the log - initial testing shows these to be better in terms of IO hit.

SELECT
 DB.name,
 MF.physical_name,
 SUM(MF.size * 8 / 1024.0 /1024.0) AS FileSizeGB
FROM
 sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id is null -- exclude snapshots
GROUP BY DB.name, MF.file_id, MF.physical_name
ORDER BY FileSizeGB DESC;

or

SELECT
 DB_Name(vfs.DbId) DBname,
 MF.physical_name,
 vfs.DBid,vfs.FileID,
 vfs.BytesOnDisk
FROM ::fn_virtualfilestats(null,null) vfs
JOIN sys.master_files MF ON vfs.DBid = MF.database_id
 AND vfs.FileId = MF.file_id


Oracle delete technique for table's segment size (data compactness) I would like to ask if there is any effect on the following deletion technique of a database to the tablespace size. The existing code for clearing the data has the following style of code before:

TRUNCATE TABLE WORK_T_TABLE;

INSERT /*APPEND*/ INTO WORK_T_TABLE
SELECT * FROM T_TABLE WHERE UPDATE_DATE > SYSDATE - 14;

TRUNCATE TABLE T_TABLE;

INSERT /*APPEND*/ INTO T_TABLE
SELECT * FROM WORK_T_TABLE;

I changed the table into a simple delete statement since I thought this was doing unnecessary truncate and insert.

DELETE FROM T_TABLE WHERE UPDATE_DATE < SYSDATE - 14;

Does the code deletion has any significant meaning in oracle? Does this reduce the unnecessary increase in the table's segment size? Currently, since the sql code change, the table's segment size is increasing rapidly without change to the data record count.

Note that the T_TABLE is always being deleted and inserted data every day. It runs the above delete command to keep 14 days of data only. Then the new data is inserted for the current day. The table's segment size increased by 400% within a month but the record count just increased to 150%.

What I mean by table's segement size is from the value of the code below by the following value:

SELECT BYTES FROM USER_SEGMENT WHERE SEGMENT_NAME = 'T_TABLE';

Also, data is being inserted via the insert select. The values are first loaded via sqlloader to the WORK_T_TABLE then appended to the table via some condition.

INSERT /*APPEND*/ INTO T_TABLE
SELECT * FROM WORK_T_TABLE;

Note: i changed the wording of question from tablesize to table's segment size to address the Justin's clarification.

How do you define the "tablespace size"?  Are you interested in the total size of the data files on disk that comprise the tablespace?  Or are you interested in the total size of all the segments that are part of the tablespace?

Issuing a DELETE will not affect the size of the table's segment so it will have no impact on the size of the tablespace under either definition.  Both the size of the table's segment and the size of the tablespace's data files will remain constant.  Of course, there will now be additional free space in many of the table's blocks that can be used by subsequent INSERT and UPDATE operations.

Issuing a TRUNCATE, on the other hand, will decrease the size of the table's segment.  That won't affect the size of the tablespace's data files.  But it will affect the total size of all the segments that are part of the tablespace.  So there may be a difference depending on your definition of the size of a tablespace.  A TRUNCATE, being DDL, will not be transactional so it cannot be rolled back.  Assuming that you are deleting a large fraction of the rows in the table, it will also tend to be much more efficient than issuing a DELETE because it generates much less UNDO and REDO.

If you are stating in your last paragraph that the size of the segment is increasing much faster than the rate at which new data is being added, assuming that the new rows are roughly the same size as the old rows and that the old rows are not growing over time due to updates, is it possible that the new rows are being added via direct-path inserts which will always go above the current high-water mark for the segment and will thus never reuse the space in blocks that is freed up by a DELETE?  If so, is that intentional?  If the table is small, you might see similar differences because of the granularity of extent allocation-- you might insert 100 rows without requiring a new extent, the 101st insert requires Oracle to allocate a new extent, and that new extent might be sufficient for thousands of new rows to be added, but you'll only see the size of the segment change after the 101st insert.  But that is less likely if this is a reasonably large table unless you've chosen a particularly large extent size.


Large table load slow mysql php high resources usage in this situation i want to tell you guy's about one weird think.
Maybe its already answered for this but seriously i can't find answer.
I have table who have 130k records. 3 columns (id, product_id, picture_url).
When im creating SELECT picture_url FROM table WHERE id='1', server cpu,ram and ssd is going to 100% but when im selecting all SELECT * FROM table; Its loads fast and without any resourses usage. Where could be a problem for this weird situation?

check indexes and table structure

for 99% id - is INT
so

SELECT picture_url FROM table WHERE id='1'

born scan of all column and convert it to archer, same if no indexes - it start scan all table and sort

at the same time

SELECT * FROM table

just start send data without transformation

if I right - just change query for

SELECT picture_url FROM table WHERE id=1

and add index if it not already there


Query Performance | Update Statistics | Ola Hallengren SPs I recently read or listened to something Tara Kizer said about developers coming to her on timeouts or slow running queries, and the resolution was usually updating stats.  I think she went on to say something about eventually she worked with developers to update their code, but I can't find the article or podcast (it was something to the extent of her talking about early in her career, IIRC).

Anyway, I am experiencing the same issue after recently switching to using Ola Hallengren's scripts for maintenance. I started with a new company and I am working on moving everything over from maintenance plans (weekly index rebuilds) to using Ola Hallengren's stored procs with jobs (still doing weekly index/stats maintenance unless there is a reason to do it more frequently).  The specific code I am using for this is:

EXECUTE dbo.IndexOptimize
 @Databases = 'ALL_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y',
 @LogToTable = 'Y'

Oddly enough, after switching to this I am getting developers approaching me, exactly as Tara mentioned, and the fix for every single case has been updating stats (it has been 4 or 5 situations, and repeating periodically).  Without discussing specifics of each case, any idea why this would surface all of sudden?  Its too coincidental that I never saw any issues before, and it's only happened after switching to Ola's scripts.  I am sure I could dig into each case and find bad code behind all of them, but holistically, what would be different about the two situations (MPs vs. Ola's scripts)?  Maintenance plans are ugly, and I don't want to go back, but I am having a hard time expressing a business value for going to jobs with Ola's SPs, when we're having production issues since switching.

I appreciate any thoughts anyone has on this.

Depending on your version of SQL Server, maintenance plans reorganize or rebuild all of the indexes (I believe that starting w/ 2016 you can set thresholds to only process indexes with a certain level of fragmentation).

So, with Ola's scripts, you've got indexes that aren't badly fragmented enough to be rebuilt. While Ola's scripts allow you to update all statistics (depending the parameters you provide), the job may be finishing up faster than it did in the past. Therefore, some other job that makes many changes may not be done at the point when the statistics are being updated, causing enough changes to throw off the validity of the statistics.

(Aside - you noted that you were using @OnlyModifiedStatistics = 'Y'; you might see what happens if you change that to 'N', just as an experiment.)

Alternately, if (for example) you've limited the set of indexes to be considered, Ola's script will put the same limits on the statistics to be updated.

You can set up a job to update statistics using either T-SQL or maintenance plans. That could be run more often than the rebuild job.

UPDATE: I had originally presumed that Ola's script would only update statistics for indexes it had rebuilt/reorganized; it does not look like that's the case. Pulled in info I'd added to the comments.

The suggestion remains the same: have a separate job to update statistics, and run that during the week (daily, if you want to and the job doesn't take too long), and complaints should go down.


Deleting MySQL table with pending transactions Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)?

What happened:

I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a huge data set (300M rows) into an InnoDB table. I did not use set autocommit = 0; before. Unfortunately, mysqld was stopped right in the middle of the import.

When I restart mysql, it tries to roll back the transaction filling the system log with messages like this:

  mysqld_safe[4433]: 121212 16:58:52  InnoDB: Waiting for 1 active transactions to finish

The problem is that the roll back is running for more that 25 hours now during which  mysqld is not accepting any socket connections.

I cannot just delete /var/lib/mysql/* and start from scratch because there are some other InnoDB databases/tables on this machine as well. However, the problematic table is the only table in a separate database. Deleting the entire table or the entire database is not a problem since I can re-import all data afterwards.

There is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1, which should have grown immensely.
If you kill the mysqld process and restart mysql, it will just pickup where it left off as part of the crash recovery cycle.
DISCLAIMER : Not Responsible for Data Loss
What you could do may result in data loss for other tables, but there is something you can do to circumvent InnoDB's normal crash recovery cycle.
There is a startup option called innodb_force_recovery, which allows you to bypass various stages of InnoDB crash recovery.
According to MySQL Documentation on Forcing InnoDB Recovery, here are the settings and its effects:

1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
2 (SRV_FORCE_NO_BACKGROUND)
Prevent the master thread from running. If a crash would occur during the purge operation, this recovery value prevents it.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollbacks after crash recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.
6 (SRV_FORCE_NO_LOG_REDO)
Do not do the redo log roll-forward in connection with recovery.

With transactional changes buried in the UNDO and REDO logs, you run the risk of

losing data meant to be written
keeping data meant to be deleted

In case you expect bad side effects, backup the entire /var/lib/mysql and put it somewhere in case you want to copy ibdata1, ib_logfile0, and ib_logfile1 and retry normal recovery.
If mysql is fully up in one of the modes

mysqldump all data except the offending table
shutdown mysql
remove everything in /var/lib/mysql except /var/lib/mysql/mysql
start mysql
reload the mysqldump

CAVEAT : Make sure you backup everything !!!
I hope this helps !!!


Mysql output to csv putting column names on the bottom I'm trying to export the results of the query listed below to csv and have the column names in the first row. However, when I run the code and open up the csv the header is listed as the last row.  I have no idea why.  What am I missing?

SELECT 'id','state','First_Name', 'Last_Name', 'Phone','County'
UNION ALL
SELECT id, state, First_Name, Last_Name, Phone, County
FROM tx.students
GROUP BY First_Name, Last_Name, Phone,
HAVING COUNT(*)>1
ORDER BY id
INTO OUTFILE '/var/lib/mysql-files/_students_dupes_test3.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The records order is defined by your ORDER BY clause expression. All id field values are converted to string type due to the type of the base (first) subquery field type. Of course the digits are first and letters last.

Replace it with

ORDER BY id='id' DESC, id


I want to delete connections in tnsnames.ora. I need to figure out which ones are currently in use, so that I don't delete them mistakenly I'm using Oracle 12c in Debian 8 (on a vm)
This question is for educational purposes only. I'm not using any production servers, so anything that you can tell me won't have consecuences

To start with, I found this link to delete registers in tnsnames.ora. I didn't test it yet because first, I needed to know if there were active connections in the database using the information of the tnsnames.ora.
I'm asking here because I found no way of doing this, but it may be possible to do it.
For you to know, I'm using this command for connecting to the database, so that I clearly specify a tnsname
rlwrap sqlplus sys as sysdba@tnstest

The contents of my tnsnames.ora are the following
tnstest =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

But, if I want to be 100% sure that this is working, I use this command
tnsping tnstest

Which results are...
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-JAN-2020 23:51:38

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

After all of this I know that my configuration is working, I'm using a register in tnsnames.ora for the connection, but I don't know a way of checking...
"Hey, now that I'm connected, I want to know which register I used from the tnsnames file for connecting, if I used that file of course"
Is there any possibility that I could get this information?
Well, my following attempts of trying to figure this out, were checking the v$session view, if there was any field that I could use to get this information, but I couldn't find one.
After all of this, ultimately, I came here for asking. I don't know what else to try nor I have more ideas of what should I do next.

P.S: there's a chance that this problem is impossible to solve, because this is task I'm doing due to I'm a student of sysadmin, and our teacher intentionally added unsolvable questions.
Even so, I still think there might be a solution for this particular case

I don't think you can get that from the database server side. The tnsnames.ora file is local to the client, so you could have a net service name (connection name) of 'ORANGE' that connects to a database service called 'BANANA'.

I had a look at some of our listener log files, and they don't have this info. The SYS_CONTEXT function exposes a lot of info but not the client's local net service name.  https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A5BD086

If you want to differentiate connections, you could have multiple service names for the database, e.g. 'DB1_OLTP', 'DB1_REPORTS', 'DB1_NORMAL'. The service name can be used to assign the session to a resource consumer group to limit CPU etc.


AWS RDS Constant cpu (20%) utilisation I have a RDS instance that has workload at specific time window, however, cpu utilisation is a constant 20% even outside this window.

There is no replication configured.

Can't find a solution for this case.

Spec:

The database has more or less 2GB with a 300GB of storage (900 IOPS)

The instance type is a db.t2.medium

Quoting AWS support which I think was really disappointing:

"Thanks so much for your patience. I'm one of the RDS Support Engineers looking at your instance issue.

I've escalated this CPU utilisation issue of your instance to our RDS team and they are aware of an issue that affects a very small number of db.t2.medium instances where CPU spikes to 50% despite little to no actual database activity happening on your instance. This issue is outside of the database engine and in the underlying operating system. What this does is eat through the available CPU credits available to you, and when you get to zero CPU credits, your CPU starts being throttled, impacting the ability for your database to do any actual work. There's no remediation we can apply and we encourage you to scale your instance to either a db.t2.small or db.m3.medium or large which are not impacted by this issue. Our RDS team will be rolling out a proactive operating system fix to all impacted db.t2.medium instances over the coming months, although we don't have a timeline for this roll out.

Please let us know if we can help you further on this support case.

Best regards,

MJ A. | Sydney Support Centre
Amazon Web Services
We value your feedback. Please rate my response using the link below."


Locating row in index page In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below.

How SQL check whether some index key belong to specific range, that is, which operation it uses for that?

Is it cursor, which traverse through all rows from one page, or something else?

The default algorithm to locate a particular record in an index is a binary search using the row offset array:

Enterprise Edition may also use interpolation based on linear regression information:

I wrote about the details and the performance implications in SQL Server, Seeks, and Binary Search.


Does this computed CHECKSUM() index design make sense? I have come across what looks to me like a slightly odd pattern in a SQL Server 2005 database I'm taking care of, and was wondering whether it's just me, or whether it really is odd.

There are a number of tables with uniqueidentifier primary keys, which also have a computed column which is the CHECKSUM of that key, e.g.

[CustomerGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
[CustomerHash]  AS (CHECKSUM([CustomerGuid])) PERSISTED,

Then, there are indexes which contain both of these fields, e.g.

CREATE NONCLUSTERED INDEX [IX_Customer_CustomerHashAndGuid] ON [dbo].[Customer]
(
    [CustomerHash] ASC,
    [CustomerGuid] ASC
)

This pattern also pops up with Guids that are not primary keys - e.g., an Order table with CustomerGuid and CustomerHash for each order, and an index on those two columns for looking up orders by customer.

Surely, the whole point of a checksum is that you create an index just on the checksum, so a SELECT will retrieve the records that match the checksum, and then compare the underlying value as a safety check? Doesn't putting the underlying value in the index waste a bunch of space for no real gain?

You are right, this is pointless.

Two (of many) reasons that I see it's wrong

it isn't guaranteed unique (CHECKSUM gives int) whereas the GUID is (over the range of GUID). It's a small chance of duplicate but quite possible: like the "birthday problem" somewhat
it's still random order. The main reason IDENTITY is better then GUID for a clustered index is that IDENTITY is monotonically increasing. CHECKSUM(someGUID) is random order too

I'd add a new IDENTITY column, and then start changing dependencies to use this only.


How to design two tables: A hasMany B, and one B per A has state 'active'? Figure two tables: environments and branches.

Their relationship is: one environment has many branches. This is easy with an environment foreign key in the branches table.

The problem is adding this constraint: zero or one branch of the environment's branches can have the "active" status.

The system will be supporting these two requests:

Give me the branches of environment 'E'
Give me the active branch of the environment 'E'

A solution would be to add "activeBranchFK" in the environment table, but this produces a cyclic dependence between the tables and it looks not a good solution to play with that.

Another solution would be a boolean "active" in the branches table, but it seems to me that we can have undesired states if many branches of the same environment are active at the same time.

Did you ever have to model this pattern?

You can use a partial unique index on the branches table:

create table branches
(
  id integer primary key,
  env_id integer not null references environments,
  active boolean not null,
  ... other columns ...
);

create unique index on branches (env_id)
where active;

Now for each env_id, there can be many different rows with active = false, but only one with active = true.


Oracle 12c RAC Cluster / CRS fails to start after reboot Could someone shed some light to help fix the cluster/CRS startup failure

1)
/oragrid/app/grid_home/bin/crsctl start crs

CRS-4640: Oracle High Availability Services is already active

CRS-4000: Command Start failed, or completed with errors.

2)
/oragrid/app/grid_home/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services
daemon

CRS-4534: Cannot communicate with Event Manager

3)
/oragrid/app/grid_home/bin/crsctl start res ora.crsd -init

CRS-2672: Attempting to start 'ora.gpnpd' on 'xxxxxxxxxx'

CRS-2674: Start of 'ora.gpnpd' on 'xxxxxxxxxx' failed

4)
/oragrid/app/grid_home/bin/crsctl start cluster

CRS-2672: Attempting to start 'ora.gpnpd' on 'xxxxxxxx'

CRS-2674: Start of 'ora.gpnpd' on 'xxxxxxxx' failed

ASM manual startup:

[oracle@rac1 ~]$ cd /u01/app/11.2.0/grid/bin
[oracle@rac1 bin]$ ./crsctl check crs
CRS-4639: Could not contact Oracle High Availability Services
[oracle@rac1 bin]$
[oracle@rac1 bin]$ su - root
Password:
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin
[root@rac1 bin]# ./crsctl start crs
CRS-4124: Oracle High Availability Services startup failed
CRS-4000: Command Start failed, or completed with errors
[root@rac1 bin]#

[root@rac1 bin]# ./crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@rac1 bin]#

[root@rac1 bin]# nohup /etc/init.d/init.ohasd run &
press Enter:
[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager

[root@rac1 bin]# cd /dev/oracleasm/disks
[root@rac1 disks]# ls -lrt
total 0
brw------- 1 root root 8, 17 May  6 10:16 DISK1
brw------- 1 root root 8, 33 May  6 10:16 DISK2
brw------- 1 root root 8, 49 May  6 10:16 DISK3
brw------- 1 root root 8, 65 May  6 10:16 DISK4
brw------- 1 root root 8, 81 May  6 10:16 DISK5

[root@rac1 bin]# ps -ef  | grep css
root      3784     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdmonitor
root      3801     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdagent
root      4189  4107  0 10:26 pts/1    00:00:00 grep css

[root@rac1 disks]# chown -R oracle:dba /dev/oracleasm/disks
[root@rac1 disks]# chmod -R 777 /dev/oracleasm/disks
[root@rac1 disks]# ls -lrt
total 0
brwxrwxrwx 1 oracle dba 8, 17 May  6 10:16 DISK1
brwxrwxrwx 1 oracle dba 8, 33 May  6 10:16 DISK2
brwxrwxrwx 1 oracle dba 8, 49 May  6 10:16 DISK3
brwxrwxrwx 1 oracle dba 8, 65 May  6 10:16 DISK4
brwxrwxrwx 1 oracle dba 8, 81 May  6 10:16 DISK5

[root@rac1 bin]# ./crsctl start cluster
CRS-2672: Attempting to start 'ora.cssd' on 'rac1'
CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'rac1'
CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded
CRS-2679: Attempting to clean 'ora.asm' on 'rac1'
CRS-2681: Clean of 'ora.asm' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rac1'
CRS-2676: Start of 'ora.asm' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rac1'
CRS-2676: Start of 'ora.crsd' on 'rac1' succeeded

[root@rac1 bin]# ps -ef | grep css
root      3784     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdmonitor
root      4372     1  0 10:30 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdagent
oracle    4387     1  1 10:30 ?        00:00:02 /u01/app/grid/11.2.0/bin/ocssd.bin
root      5347  4107  0 10:33 pts/1    00:00:00 grep css

[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@rac1 bin]#

Su – oracle

Srvctl start database –d dbname

Create diskgroup FRA external redundancy ‘ORCL:VOL5’;(in ASM (sqlplus / as sysasm))

Asmcmd lsdg
Alter diskgroup FRA mount; (if not in mount).

http://mbc-dba.blogspot.com/2014/08/crs-4639-crs-4124-oracle-high.html
https://shivanandarao-oracle.com/2012/06/03/crs-4535-cannot-communicate-with-cluster-ready-services/


Username length restriction in different MySQL environments I am setting up a project to my local environment and at its initial script are some SQL commands for creating the database:

create database 'db-name' default character set utf8;

and to grant privileges for the user account:

grant select, insert, update, delete, create, drop, index, alter on 'db-name'.* to 'user-name-too-long-for-user-name'@'localhost' identified by 'user-name-pass'

When I am trying to run the grant command I get this:

ERROR 1470 (HY000): String 'user-name-to-long-for-user-name' is too long for user name (should be no longer than 16)

and I can't understand why is this happening whereas this same script has successfully run to another environment before it comes to mine.

The server version I am using is 5.6.20 MySQL Community Server (GPL).

Is this situation related to a certain MySQL version or something else?

The maximum length for a user name in MySQL does depend on the version, as stated in the manual:

  MySQL user names can be up to 32 characters long (16 characters before MySQL 5.7.8).


How to configure MySQL master master replication but use only one server for writing? How to configure MySQL master master replication but use only one server for writing? Is it done at LB end and how? If two users work on same db and same table and they both enter a data into it which users data will replicate first and will there be any loss of data or data will overwrite?

To use only one server for writing you point your application at only one server. You can do this explicitly in your application's database connection configuration. Or you can have a load balancer only forward connections to one node.

If you want read-write splitting either you have to code that into your application or you need an advanced load balancer like MaxScale that is fully transaction aware.


What is going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced? I am using PostgreSQL 9.1. Trying to enforce UTF8 encoding as default.

This is what I am doing.

service postgresql initdb -E 'UTF-8' \
               --lc-collate='en_US.UTF-8' \
               --lc-ctype=locale='en_US.UTF-8';

Although the initilization process goes on without any problem,

a \l at the psql prompt gives there details.

                             List of databases
   Name    | Owner    |Encoding  | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | LATIN1   | en_US   | en_US |

Why is the UTF-8 encoding not getting enforced?

If initdb is run through a run-level script of the OS. This script might not pass on the parameters.

I couldn't run initdb directly as a superuser. Gave an

initdb: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

error. After logging in as an unprivileged user I was able to initialize the db with the proper encoding.

To initilize the db, run initdb directly. The command

/usr/bin/initdb --pgdata=/var/lib/pgsql9/data/ -E 'UTF-8' \
--lc-collate='en_US.UTF-8' \
--lc-ctype='en_US.UTF-8';

was run under the non-superuser postgres.


MySQL SELECT slow, but only 2 x 300K rows and indexes Have the MySQL SELECT query below that is awfully slow.
It takes ~1.0 seconds to execute despite having only 300K rows and indexes, so I would love to find a way to get it to execute faster since it's a query that needs to be run again and again.
The query:
SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice)
FROM products p
JOIN shops s ON p.shopid = s.id
JOIN products_category pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
WHERE p.brand_id > 0
AND pc.category_id = 46
AND pc.active = 1
AND p.price > 0
AND p.saleprice > 0
AND p.saleprice < p.price
AND (p.last_seen > DATE_SUB(NOW(), INTERVAL 2 DAY))
GROUP BY p.image

The query returns 960 rows.
The table products has 300.000 rows and these columns + one index:
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(512) DEFAULT NULL,
  `shopid` int(11) DEFAULT NULL,
  `image` varchar(512) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `saleprice` int(11) DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `brand_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_id etc` (`brand_id`,`shopid`,`last_seen`,`price`,`saleprice`)
) ENGINE=InnoDB AUTO_INCREMENT=391671 DEFAULT CHARSET=utf8

The table products_categories also has 300.000 rows and these columns + two indexes:
CREATE TABLE `products_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id etc` (`category_id`,`active`),
  KEY `product_id etc` (`product_id`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=373364 DEFAULT CHARSET=utf8

The shops table. It has 15 rows and these columns:
CREATE TABLE `shops` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `active` int(11) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8

Based on similar questions here, I have tried nesting things with an inner select:
SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice)
FROM
(SELECT * FROM products WHERE p.brand_id > 0 AND price > 0 AND saleprice > 0 AND saleprice < price AND (p.last_seen > DATE_SUB(NOW(), INTERVAL 3 DAY))) p
JOIN shops s ON p.shopid = s.id
JOIN products_category pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
WHERE pc.category_id = 46
AND pc.active = 1
GROUP BY p.image

It didn't help. The version with the inner select takes ~1,3 seconds to execute.
The problem seems to be the join between products and products_category, i.e. the two big tables with 300K rows each.
The GROUP BY p.image is necessary (some products are just different sizes, so we use p.image to filter these out).
Maybe there's a trick I can do with my indexes? Or can any of you spot something else I should optimize?
EXPLAIN of the query:
id  select_type table   partitions  type    possible_keys                   key             key_len ref             rows    filtered    Extra
1   SIMPLE      c       \N          const   PRIMARY                         PRIMARY         4       const           1       100.00      Using temporary; Using filesort
1   SIMPLE      pc      \N          ref     category_id etc,product_id etc  category_id etc 10      const,const     43104   100.00      Using where
1   SIMPLE      p       \N          eq_ref  PRIMARY,brand_id etc            PRIMARY         4       pc.product_id   1       5.00        Using where
1   SIMPLE      s       \N          eq_ref  PRIMARY                         PRIMARY         4       p.shopid        1       100.00      \N

KEY brand_id etc (brand_id,shopid,last_seen,price,saleprice)

This index does not support this query well.
The first field in the index is brand_id, but you are only filtering by "> 0".
Since I doubt you have many brands with negative id's, this probably isn't going to help very much.
The next field in the index is shopid, which doesn't appear in your where clause at all.
Based on this cursory analysis (and MySQL will do a lot more than that), this index will probably be discounted and the table scanned serially instead.
A better discriminator for choosing the rows you want returned would be last_seen.  Consider adding an index on that (or with that as its first field) and you should see some improvement.


Finding related functional dependencies from Amstrong's axioms Let be

F = { AB → C,
       B → D,
      CD → E,
      CE → GH,
       G → A }

some functional dependencies.

Do we have, thanks to Amstrong's axioms or its corollaries AB → G?

My attempt

I don't think so because

AB → C
CE → GH

Then from the pseudo-transitivity rule:

  {X → Y, WY → Z}⊨XW → Z

Then

ABE → GH

And tht's all. We can't get rid of H.

This is rather easy.

AB → C
B → D

therefore:

AB → C
AB → D

and:

AB → CD

Next:

AB → CD
CD → E

therefore:

AB → E

with: AB → C
we get:

AB → CE

And finally:

CE → GH

thus:

CE → G

and with the (previous): AB → CE

AB → G


Fastest way to split/store a long string for charindex function I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function).
I have tested this with a 1GB string and a 9-digit-substring using SQL Server, storing the string as a varchar(max). Charindex takes 10 secs. Breaking up the 1GB string in 900 byte overlapping chunks and creating a table (StartPositionOfChunk, Chunkofstring) with chunkofstring in binary collation, indexed takes under 1 sec. Latter method for 10GB,10 digit-substring rises charindex to 1,5 min. I would like to find a faster storage method.
Example
string of digits: 0123456789 - substring to search 345
charindex('345','0123456789') gives 4
Method 1: I can now store this in a SQL Server table strtable consisting of one column colstr and perform:
select charindex('345',colstr) from strtable

Method 2: or I can make up a table strtable2 (pos,colstr1) by splitting up the original string: 1;012 | 2;123 | 3;234 a.s.o and then we can have the query
select pos from strtable2 where colstr1='345'

Method 3: I can make up a table strtable2 (pos2,colstr2) by splitting up the original string into larger chunks 1;01234 | 4;34567 | 7;6789 and then
select pos2+charindex('345',colstr2) from strtable2 where colstr2 like '%345%'

First method is the slowest.
Second method blows up the database storage size!
Method 3: Setting colstr2 length to 900 bytes in binary collation, creating an index on this column takes 1 sec for 1GB string and 9 digit substring search. For 10GB string and 10 digit substring ist takes 90 secs.
Any other idea how to make this faster (maybe by utilizing the string consists of Digits, with Long integers,....)?
Search is always for a 12 digit substring in a 1TB string of digits  SQL Server 2017 Developer Edition, 16 cores, 16GB RAM. Primary goal is search speed! 10 digits in a 10GB string (for performance testing).

Storing and processing 1TB of data with only 16GB RAM available may prove to be a challenge. 1GB per core is rather unbalanced, especially for this kind of workload. 8GB per core would be a much better starting point, with more desirable.

That said, I would still be tempted to try a variation of method 2:

Store all possible 12-character substrings as bigint in a clustered columnstore table (with archive compression if that turns out to be useful):

CREATE TABLE dbo.Search
(
    pos bigint NOT NULL,
    fragment bigint NOT NULL,

    INDEX CCS CLUSTERED COLUMNSTORE
        WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) -- optional
);

You will probably have to implement some way of incrementally loading the source data into this table. Make sure you end up with maximally-sized row groups (1,048,576 rows) in the finished columnstore structure. See the data-loading guidance.

You might stage rows in multiples of 1048576 in an unindexed rowstore table before creating a clustered columnstore index on that, then switching the result directly into a partitioned main table. The exact approach depends on how you intend to load the data, whether it will be added to, and how familiar you are with SQL Server in general.

Very good performance is possible with this method, but as so often with columnstore, you would need to achieve effective partition and segment elimination. Partitioning on the fragment column and building the columnstore index serially while replacing a rowstore clustered index keyed on fragment is the way to achieve this, as noted in the documentation linked above. This will also minimize the storage needs, since fragment values in the same range will be stored in the same segment. This allows effective value rebasing and bit packing.

While loading, try to limit the strings you are working with within SQL Server to non-LOB (max) types. If you do find working with LOBs best for throughput, there is often a sweet spot of data length to be found, above which performance drops off significantly.

Depending on the final size of the structure and the speed of your I/O subsystem, you may find that this approach offers consistently good enough performance. Increasing the memory available would improve things markedly.


Automobile database logic I am building a web based quoting system for a local automobile dealership.

I think I have figured out the logic for the databases but I want to get a second opinion before I start just to make sure I am doing it right.

I am working with the service plans for various vehicles and the client wants a drill down type search to find the required service plan e.g. Model of vehicle, transmission, etc...

The thing is the drill down is dynamic so some cars only have one type of transmission manual or automatic but with others you have to pick, the same applies to airbags and a few other things.

I was thinking I would have one table with the models and their specifications and then another table for each service plan at X number of KM's/Miles.

The table with models/specifications would populate the drop downs and I would then use this data to formulate a query to find the correct service plan table.

The reason for keeping the service plans in individual tables is because the data is so inconsistent.

Does this sound like the right approach?

Start by thinking through a user interface.  Will the user start with the make+model+year of the vehicle?  Or will he start with "airbag"?  More maybe you want to provide both ways to start the drilldown.

Don't plan to do all the work in the database.  Plan to either have some application code to sort through the "so inconsistent" data, or throw text at the user, and let him sift through the final information.

How many different sets of info do you have?  One per make+model+year?  One per component with a service plan?

The design might be one table, with lots of columns.

Or it may need 3 tables (to start with).  One for each distinctly different vehicle; perhaps PRIMARY KEY (make, model, year).  One for service plans; this would contain an id, a TEXT field, and some meta information ('airbag', mfg's id, etc).  The third would be the "relationship" that ties the two other tables in a many-to-many relationship; it would have two ids (for JOINing to the other tables).


How to determine the data type of all fields in a result set in SSDT? I'm writing a table valued function in SQL Server Data Tools (SSDT).  Table valued functions require you to explicitly state the data type of the returned table.  In my particular case, I've written a query which returns the values that I want.  I can execute it using SSDT, but can't figure out how to quickly determine the data type of the columns returned.

Is there a way to get the list of columns that it returns and the data types of said columns so I can quickly and accurately populate the list of columns in the ReturnTable?

The closest I've gotten is to hover over the field name in the SELECT clause, but that doesn't provide the length or precision limitations.

Edit: I struck through my comment regarding table valued functions require you to explicitly state the data types of the columns in the returned table based on @srutkzy's answer.  Thanks!

You can use the SELECT..INTO syntax to create a table on-the-fly and examine the resultant column definitions in sys.columns.

select
  *
into #SomeName
from <your query goes here>
where 1=0 -- because we only want the metadata

I think sys.columns has definitons for functions, too, but I don't have an instance to hand just now to check.  Alternately you could create a view with the same query you are about to put into your function and check the catalogue for that.


Clarification on SQL Server 2016 Always Encrypted We are planning out a move from SQL Server 2008 to SQL Server 2016.  One of the key requirements for us is to have encryption at rest at a minimum.  We have been considering SQL Server Enterprise as it includes TDE though now with SQL Server 2016 Standard including the "Always Encrypted" feature set we are looking into this as an alternative to Enterprise (saves us some money).  Though we are having a very difficult time locating a source which clarifies what exactly AE encompasses in SQL Server. Thus far in our research it appears that AE is specific to column level encryption from the client to the DB utilizing certs though we've found nothing that mentions AE can be used in a similar fashion to TDE and encrypt the entire DB.

Can someone please shed some light on this for us?

AE is intended to protect sensitive data end-to-end. That's why keys are held by the client, not server. In SQL Server 2016 granularity is at the column level.

If you want to AE the entire database, you'd have to do it one column at a time. That doesn't really make sense and doesn't fit the AE scenario unless all data in your database is sensitive. Theoretically possible but pretty rare in reality. You've also made your database extremely limited in query capabilities and perf is pretty much out the window.


SQL CLR very slow on Amazon EC2 Instance (SQL Server 2014 CU6) I am running a SQL Server 2014 Standard Edition (Cumulative Updates 6) instance on an r3.large Amazon EC2 box.
I noticed that the CPU usage of SQL Server skyrockets to 100% while executing pretty much no queries at all.

Looking at the SQL Performance Tool, I can see that most of the time is lost in SQL CLR functions.

Here's a screenshot of one of such operations:
https://dl.dropboxusercontent.com/u/3098924/Schermata%202015-03-04%20alle%2016.46.17.png

Most of those CLR functions are doing basic things. For example the IsNullOrEmpty function is returning the result of String.IsNullOrEmpty() but even those are taking up between 1500 to 7000 ms to execute.

If I take the same job and run it on a physical box that we use for development, those calls can't almost be seen as they're way too quick.

Is there anyone who faced similar issues? I suppose it's due to EC2 or VMs but I can't really tell why. Thanks!

It turns out that the CLR was not enabled and for some reason the exception wasn't being trapped and SQL Server took about 15 seconds just to say that the CLR was not enabled but during that time the instance is being kept at 100% CPU and hogged down.

In case anyone happens to have the same problem, you can turn the CLR on by running this:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO


Config file location in MySQL I have MySQL 5.5 running in RHEL. I have checked for my.cnf file in my filesystem and its available in two destinations. One in the user's home directory and the other is in /data/etc_ORIG directory. It was not seen in /etc or /etc/mysql folder. Is there any issue with the config file present in a different path? And how do I check which config is being used by MySQL?

As your my.cnf is not on standard location, check
ps -ef | grep mysql for --defaults-file option.

Below command will give you default file options:

mysqld --verbose --help | grep -A 1 "Default options"

(There are many similar questions previously answered.)


Oracle. How to copy BLOB, LONG RAW and BFILE column between tables I have a table name mytable in Oracle 9i.

columns of mytable are

id number(9,0)
C1 LONG RAW,
C2 VARCHAR2(256),
C3 BLOB,
C4 BFILE

when I try to do this:

create this_other_table as
select * from mytable;

I get this:

ORA-00997: illegal use of LONG datatype

I guess the rest of columns that are of some kind of binary data (BLOB, AND BFILE, besides LONG RAW) will yield errors also.

Would someone please shed some light on how to do this?

What Oracle says about your error?

  You try to select distinct values from a long column, create a table
  using as select with a long column, insert into a table by selecting
  the long column from another table and an ORA-00997: illegal use of
  LONG datatype ... occurs

  It is a restriction of the long columns that they cannot be used with
  a DISTINCT operator and cannot be referenced when creating a table
  with query  (CREATE TABLE...AS SELECT...) or when inserting into a
  table(or view) with a query (INSERT INTO ... SELECT...)

And what Oracle recommends?

  Oracle strongly recommends that you convert LONG RAW columns to binary
  LOB(BLOB) columns.

Here is how simulated your problem and prepared the solution

 SQL> create table mytable(id number (9,0), c1 long raw, c2 varchar2(256), c3 blob, c4 bfile);

Table created.

SQL> desc mytable
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                         NUMBER(9)
 C1                         LONG RAW
 C2                         VARCHAR2(256)
 C3                         BLOB
 C4                         BINARY FILE LOB

SQL> create table this_other_table as select * from mytable;
create table this_other_table as select * from mytable
                                        *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL> create table this_other_table as select to_lob(c1) c1, c2,c3,c4 from mytable;

Table created.

SQL>


How to stop ROLLBACK in progress? How do I stop a rollback in progress. I killed a very long running INSERT INTO table1 SELECT * FROM table2 statement. I want to commit what's already there but I can't since it's rolling back.

This is a mySQL database.

There is no way to do what you are asking as this would fundamentally break the Atomicity part of ACID