Converting MSSQL Stored Procedure to MySQL - Syntax Error I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out:

CREATE PROCEDURE usp_AddNewUser
(IN UserName VARCHAR(50),
IN UserID VARCHAR(50),
IN Password VARCHAR(30),
IN PageAccess VARCHAR(10),
IN AdminUser VARCHAR(50))

BEGIN
Declare RowID, CountAll INT DEFAULT 0

SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID);

IF @CountAll = 0 THEN
    INSERT INTO WebUsers (UserName, UserID, UserPassword, AddedBy, UpdatedBy) Values (UserName, UserID, Password, AdminUser, AdminUser);
    SELECT @RowID := @@Identity;
ELSE
    SELECT @RowID := RowID FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID);
    UPDATE WebUsers SET UpdatedBy = @AdminUser, UpdatedOn = NOW() WHERE RowID = @RowID;
END IF;

If  @PageAccess Like ('%4%') THEN
    INSERT INTO WebUserTypeMapping (WebUserRowID, WebUserTypeRowID) Values (@RowID, 4);
ELSE

    If  @PageAccess Like ('%1%') THEN
        INSERT INTO WebUserTypeMapping (WebUserRowID, WebUserTypeRowID) Values (@RowID, 1);
    END IF;
END IF;
END;

The syntax error is at the SELECT@CountAll line:

SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID)

I've tried rewriting it several times, but no dice. There are likely additional errors in this code too that I don't know about yet since I can't get by that error.

Can anyone help me out? Not that it matters, but it would totally make my Friday... :)

Thanks!

Edit: The full error message is the following:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID)' at line 12

MySQL Server version: 5.5.30

Edit: When setting a different delimiter, I get an error message that literally just says "Error" -- with no other information.

Comment from @ypercube is correct -- in this case, the syntax error you're encountering is because the previous line did not end in a semicolon:

Declare RowID, CountAll INT DEFAULT 0

Should be:

Declare RowID, CountAll INT DEFAULT 0;

In MySQL's stored procedure language, you have access to two different types of variables.

The first you declare with DECLARE and these are locally scoped to the procedure. Do not prefix these with @.
The second type of variable are called user variables and they are prefixed with @. You don't have to declare these. But they are kind of like "global" variables in that they are scoped to the session, and you can assign and read them outside the procedure. That is, you could assign a value to one of these variables in the procedure, and then after the procedure is done, you could read that user variable in any SQL expression.

In your procedure, you also have some input arguments. These are treated like the first type of variable. They are locally scoped, and they don't use the @ prefix. For example, you have an input argument UserID but this is not the same variable as @UserID that you use in your query.

This brings up the possibility of ambiguity when your variables have the same names as columns, and you try to use the variables to compare to the columns. MySQL does its best to have rules for precedence, but it is far easier to debug if you make sure your local variable names don't use the same names as columns.

Other issues:

This line:

SELECT @RowID := @@Identity;

Should be as follows in MySQL:

SET @RowID = LAST_INSERT_ID();

Comments starting with -- must have a space following them, which is non-ANSI but is meant to resolve syntax ambiguity. E.g., this:

--User

Must be changed to:

-- User


Query to change 2 statements into a batching job I have a SQL server agent job setup to delete records from a parent table where the date is greater than 2 weeks and also delete all records from a child table where the corresponding ID no longer exists in the parent table (basically a FK relationship without the actual relationship).

The issue is that even though this job takes under a minute to run it is being deadlocked every other week.  The code you see below is my attempt to setup batching to break it up.  My issue right now is that the entire statement completes successfully, but for whatever reason no records are being deleted from the parent table despite it having a very simple WHERE class (greater than 2 weeks).  It is almost like it is completing the first loop, going to the second, but doing nothing, and then saying it completed.  I am sure I am missing something very simple that is causing me to overlook this issue.

SET NOCOUNT ON

USE database
GO

DECLARE
    @sql nvarchar(1000)
    ,@i int
    ,@ii int
    ,@batchSize int
    ,@totalDeleteLimit int
    ,@totalDeleteCurrentCount int
    ,@totalDeleteCurrentCount2 int
    ,@interval nvarchar(100)

SELECT
    @i = 1
    , @ii = 1
    , @batchSize = 10000
    , @totalDeleteLimit = 2000000
    , @totalDeleteCurrentCount = 0
    ,@totalDeleteCurrentCount2 = 0
    , @interval = '00:00:00.01'

--PRINT CAST(CAST(GETDATE() as Datetime2(7)) as nvarchar(100)) + N' - Start Logging Purge'

BEGIN TRY
WHILE @i <> 0
BEGIN
    SELECT @sql = N'DELETE TOP(' + CAST(@batchSize as nvarchar(10)) + N')
    FROM ChildTable WHERE LogID IN (SELECT LogID
                FROM ParentTable
                WHERE [Timestamp] <= dateadd(week,-2,getdate()))'

    --PRINT @sql
    EXEC sp_executesql @sql

    SET @i = @@ROWCOUNT

    --print @i
    SELECT @totalDeleteCurrentCount = @totalDeleteCurrentCount + @i;

    WAITFOR DELAY @interval;

    IF(@totalDeleteCurrentCount >= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached on 1st loop';
        BREAK;
    END
    ELSE
        CONTINUE;
WHILE @ii <> 0
BEGIN
    SELECT @sql = N'DELETE TOP(' + CAST(@batchSize as nvarchar(10)) + N')
    FROM ParentTable
    WHERE [Timestamp] <= dateadd(week,-2,getdate())'

    --PRINT @sql
    EXEC sp_executesql @sql

    SET @ii = @@ROWCOUNT

    --print @ii
    SELECT @totalDeleteCurrentCount2 = @totalDeleteCurrentCount2 + @ii;

    WAITFOR DELAY @interval;

    IF(@totalDeleteCurrentCount2 >= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached';
        BREAK;
    END
    ELSE
        CONTINUE;
END
END
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return
    -- error information about the original error that
    -- caused execution to jump to the CATCH block.
    RAISERROR (
        @ErrorMessage, -- Message text.
        @ErrorSeverity, -- Severity.
        @ErrorState -- State.
    );
END CATCH

--PRINT CAST(CAST(GETDATE() as Datetime2(7)) as nvarchar(100))  + N' -- Total Purged: ' + CAST(@totalDeleteCurrentCount as nvarchar(20))

Your first while loop

WHILE @i <> 0
BEGIN

Encompasses the second while loop as well.

The docs on break  show that:

  BREAK exits the current WHILE loop

.

Meaning that you are closing the entire while loop including the nested while loop (WHILE @ii <> 0). You have to close the first while loop before opening the second one.

Add an END before opening the next while loop, for example:

    IF(@totalDeleteCurrentCount >= @totalDeleteLimit)
    BEGIN
        PRINT N'-- Limit Reached on 1st loop';
        BREAK;
    END
    ELSE
        CONTINUE;
END
WHILE @ii <> 0


How do I remove duplicate entries in a given timeframe? My table looks like this:

+-----------+
|     id    | <- primary key
|    time   | <- DateTime
|    row2   | <- varchar
|    row3   |
|    ...    |
+-----------+

I want to remove all entries, except one, which are within a timeframe of one full hour and have the same value of row2 (distinct row2). Is it possible to do this without a stored procedure and/or non-sql code?

The usual solution for this problem is first looking for the rows you want to keep, and then delete all others.  The first part can be accomplished with an aggregate and grouping:

SELECT min(id)
FROM my_table
GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')

Then you can write your DELETE:

DELETE FROM my_table
WHERE id NOT IN (
    SELECT min(id)
    FROM my_table
    GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')
);

There are variations to this theme: you can use a NOT EXISTS construct instead of NOT IN (with appropriate changes), or a join using the USING keyword.


Fastest way to move a database from one server to another I'm moving a single MySQL database (~10GB uncompressed) from one server to another on the same network. The current MySQL version is 5.1.41 and the new version is 5.5.24. The database contains both MyISAM and InnoDB tables. Is it possible to use this method:

Shut down MySQL on both servers
Copy the /data directory from old server to new server
Start new server

I realize this basic question has probably been asked 1000 times before but most of the ones I've seen don't mention changing versions & supporting both MyISAM and InnoDB.

If you are changing versions, DO NOT MOVE THE mysql SCHEMA.

Why should you not move the mysql folder? It has to do with the authentication privileges.

The number of columns in mysql.user is different from version to version

If you run desc mysql.user

You will see 31 rows for MySQL 4.1
You will see 37 rows for MySQL 5.0
You will see 39 rows for MySQL 5.1
You will see 42 rows for MySQL 5.5

I wrote about this before

Cannot GRANT privileges as root
Mysql users deleted

It is OK to move everything else. On the new machine that has MySQL 5.5.24, do this:

mv /var/lib/mysql /var/lib/mysql/mysql55
mkdir /var/lib/mysql
<scp or rsync /var/lib/mysql of MySQL 5.1.41 over to /var/lib.mysql of MySQL 5.5.24>
rm -f /var/lib/mysql/mysql/*
cp /var/lib/mysql/mysql55/* /var/lib/mysql/mysql/*
chown -R mysql:mysql /var/lib/mysql
service mysql start

So, the question remains:

How do you move the User Privileges in the old MySQL 5.1.41 to MySQL 5.5.24 ???

There are two ways to do this starting on the MySQL 5.1.41 machine:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Either way, move MySQLUserGrants.sql over to the MySQL 5.5.24 machine and execute the script

I wrote about this before : importing myisam 5.0 database into a 5.5 innodb server


Using WITH in sql query I am trying to replace # with numbers, but the order by is being ignored, and I was told to move the subquery usingWITH or adding a limit to the order by, I tried the latter but it didn't work, and I am not sure how to use WITH in the following query.

update items
join (select id, @num := @num + 1 as num
    from items, (select @num := 0) items
     where parent_id =  159133    order by name) items2
on items2.id = items.id
set items.description = replace(items.description, '#' COLLATE utf8_unicode_ci, cast(num as char(10)))
WHERE parent_id = 159133 and type='photo';

Your query can be written without subqueries/joins like that:

UPDATE items
   SET description = replace(description, '#', cast(@num:=@num+1 as char(10)))
 WHERE parent_id = 159133
   AND type='photo'
   AND @num := 1       -- @num initialization by Akina
 ORDER BY name ASC
;


SSMS Oracle import using OpenQuery over LinkedServer (Oracle Provider for OLEDB) - XML Parsing: unable to switch encoding I am trying to migrate the data from an OracleDB to an SQL Server Express database. I'm doing this by openquerying a linked server (data provider based on Oracle Provider for OLEDB). Everything works fine for the tables that contain simple data types. But when I get to the table that contains an XML column it returns an error:

Msg 9402, Level 16, State 1, Line 24
XML parsing: line 1, character 60, unable to switch the encoding

For that I do...

INSERT INTO [datab].[dbo].[tr] (ID, CREATED, UPDATED, COMPANY, INTERNAL_MESSAGE, EXCHANGE_MESSAGE)
SELECT * FROM OPENQUERY(DATAB_ORACLE,
'SELECT id, CAST(created AS DATE) AS created, CAST(updated AS DATE) AS updated, company, XMLSerialize(DOCUMENT internal_message AS CLOB) AS internal_message, exchange_message FROM datab.tra')

I have no clue as to what encoding the Oracle column has, neither for the MSSQL column (probably default).

Any suggestions?

LE: Please note that a solution relying on Oracle CONVERT() function (with the corresponding encoding type) doesn't seem to work.

I found a solution, for everyone to know:

Assuming that we put the xml field in a variable of type varchar(max) and then casting it to a variable of type XML (but not by using the CONVERT function, rather just by simple assignment), in the end the value of the newly created XML variable will be included in an insert query, by casting it back to varchar(max).

DECLARE @id numeric(19, 0)
DECLARE @xmlmsg varchar(max)
SELECT @id = id, @xmlmsg = internal_message FROM
    OPENQUERY(DATAB_ORACLE, 'SELECT id, CAST(created AS DATE) AS created, CAST(updated AS DATE) AS updated, company,
        XMLSerialize(DOCUMENT internal_message AS CLOB) AS internal_message, exchange_message FROM datab.tra')

DECLARE @xml xml
SET @xml = @xmlmsg

DECLARE @tsql varchar(8000)
SELECT @tsql = 'INSERT INTO [datab].[dbo].[tr] (ID, CREATED, UPDATED, COMPANY, INTERNAL_MESSAGE, EXCHANGE_MESSAGE)
    SELECT id, created, updated, company, ''' + CONVERT(varchar(max), @xml) + ''', exchange_message FROM OPENQUERY(DATAB_ORACLE,
    ''SELECT id, CAST(created AS DATE) AS created, CAST(updated AS DATE) AS updated, company, XMLSerialize(DOCUMENT internal_message AS CLOB) AS internal_message,
    exchange_message FROM datab.tra where id = ' + CONVERT(VARCHAR(max), @id) + ''')'

EXEC (@tsql)

You should note that in such cases, seeing that the XML Document Declaration (e.g. "") is gone missing after inserting the row is normal, due to the fact that SQL Server strips that part (by design). Find out more here: http://msdn.microsoft.com/en-us/library/ms187107(v=sql.90).aspx


Constraint violation in a trusted constraint I have a table with a constraint on it that has an existing record that is in violation of that constraint.  While this is possible if someone applied the constraint without a check and after violation data was inserted, how would it be possible that the is_not_trusted flag is zero in the sys.foreign_keys table?

This is the situation I currently have, a data violation on a foreign key where the is_not_trusted flag is zero.  I thought that was essentially the flag that confirms whether the constraint has been enforced 'religiously'.

Thanks

From comments:
This might happen due to the the value physically written on the page being incorrect (data corruption). Or a bug in the product (probably less likely but possible). Run DBCC CHECKDB to see if that finds any issues. – Martin Smith Jun 18 '16 at 0:07
3
Along Martin's comment - did the data get into the table via MERGE? Here is one bug (closed as "Won't Fix") that can do exactly this, and another bug (also "Won't Fix"). If you need more reasons to avoid MERGE, see this article. – Aaron Bertrand♦ Jun 18 '16 at 3:21


How do I check if PostGIS is installed in my PostgreSQL? I'm trying to check whether or not the PostGIS extension is installed, as a PG query. I'm unable to find this info out.

In psql CLI use \dx  to display all installed extensions in the current database or with SQL:

select * from pg_extension


How to know my workload type Is there any tsql-query/DMV through which I can tell my workload type on database, whether mostly Reads or mostly writes happening?

Thanks in Advance.

You can use the information available in sys.dm_db_index_usage_stats for that purpose. The following query returns the number of reads and the number of writes against each database since the last server restart. That information is stored in memory only and therefore not preserved past a server restart.

SELECT D.name AS database_name, D.state_desc,D.is_read_only,X.read_cnt, X.write_cnt
  FROM sys.databases AS D
  LEFT JOIN(
       SELECT database_id,
              SUM(DDIUS.user_seeks+DDIUS.user_scans) AS read_cnt,
              SUM(DDIUS.user_updates) AS write_cnt
         FROM sys.dm_db_index_usage_stats AS DDIUS
        GROUP BY DDIUS.database_id
      )X
    ON D.database_id = X.database_id
 ORDER BY database_name;

The numbers represent the count of "actions" against the tables in a database. You cannot judge the amount of data read or written based on these counts. The DMV also includes a user_lookups column. I did not include those, as a lookup usually implies another read action. For example a single index scan can cause thousands of lookups. But it still is only a single read action.


alter compatibility level I plan to change the compatibility level on the databases in an sql server 2016 sp1 instance from 100 to 130. When upgraded from sql 2008 to sql 2016 the compatibility level was changed from 80 to 100.
I also am in the planning stages of installing the SP2 and the latest cumulative update.
What is best practice for changing the compatibility level - before or after the SP2 and cumulative updates are installed?

What is best practice for changing the compatibility level - before or after the SP2 and cumulative updates are installed?

SQL Server 2016 SP1 is out-of-support so you should upgrade to supported service pack first.
Then the best practices for altering the database compatibility level are documented here: Change the Database Compatibility Level and use the Query Store


Can I move a system database from one version of SQL server to another? We are moving our databases from one computer running SQL Server 2005 to a different machine running SQL Server 2008.

I am following the steps found here, in an attempt to copy over the master database, with all the user logins and server-level permissions, however it is telling me the backup cannot be restored because it was created by a different version of the server.

Is there a way to copy the master database from one version of SQL server to another version of SQL server?

No you can not move the master database (or any system database) from one version of SQL Server to another version of SQL Server.  You would need to install SQL 2005 on the server, then restore master.  Then upgrade to SQL 2008.


How to get last identity row Inserted when using instead of trigger When I insert into tables using instead of triggers, @@Identity, IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get the last identity of inserted row?

In your instead of trigger, you definitely can get the inserted value... but not until after you've performed the insert.

USE tempdb;
GO

CREATE TABLE dbo.SmellThis
(
  id INT IDENTITY(1,1),
  name VARCHAR(32)
);
GO

CREATE TRIGGER dbo.SmellThis_First
ON dbo.SmellThis
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ids TABLE(id INT);

    IF NOT EXISTS
    (
      SELECT 1 FROM sys.objects AS o
        INNER JOIN inserted AS i
        ON o.name = i.name
    )
    INSERT dbo.SmellThis(name)
      OUTPUT inserted.id INTO @ids
      SELECT name
      FROM inserted;

    SELECT id FROM @ids;
END
GO

INSERT dbo.SmellThis(name) SELECT 'Remus';
GO

Results:

id
----
1

Now clean up:

DROP TABLE dbo.SmellThis;

As an aside, you should never, ever, ever be using @@IDENTITY or IDENT_CURRENT() anyway. And SCOPE_IDENTITY should be reserved for situations where you know only one row can ever be inserted. A common misconception with triggers is that they fire per row, like in other platforms, but in SQL Server they fire per operation - so a multi-row insert using VALUES(),(),() or INSERT...SELECT - which SCOPE_IDENTITY would you be setting to your variable?


Setup logical Database model for measurements I'm struggling to set up a logical model for the following spreadsheet:

(See a close-up of the table here.)

My problem is that it is not clear to me how to deal with the "gap" values where each gap (nominal, min, max) contains values (forces) from three different sources.

On the right-hand side you can see other forces coming from those three sources but they are not related to the gap - only to the profile which I believe won't let me create a Forces entity.
So I was thinking about one gap entity with (length, min,max,nominal,SourceType,MinForce,MaxForce,NominalForce) but it doesn't look right since I will have repeating data in three rows for each profile.
Or should I manage the different forces within each source?

Also have a look at the FL thickness values. This thickness is an attribute from an FL entity which can have more than one thickness value for a given profile which then changes the Build/Rebuild-Force values for LAB and CalcUnit

I've also left out some columns to not confuse you and myself even more. If I can find a solution for the gap problem I might be able to solve the rest on my own.
Maybe it's the abstract data structure that confuses me.

Hope you can help me with some tips and ideas.

From what I understand of your spreadsheet and your description, this is the normalized logical ERD for your data:

Now, based on how many GAP_CATEGORY records you have as a rule, and how many FORCE_SOURCE records you have, you might decide to do some denormalization.  You have to decide what is easier to build, maintain and use and makes more sense in your application.


Can we WITH x AS ( ... ) INSERT INTO y(k) SELECT * FROM x in MySQL? We can do INSERT INTO SELECT, but what if I have a bunch of WITH AS statements (about 27ish) for the SELECT part? My current solution is do the query then scan using programming language (Golang for my case) then insert it using normal INSERT INTO statement one by one. Was there one step solution to do this?

Something like
insert into item
  with x as (
    select id + 10 as id1, concat(brand,' D') as brand1 from item),
       y as (
    select id + 20 as id1, concat(brand,' E') as brand1 from item)
  select * from x
  union all
  select * from y

Yes, we can!


SQL Server performance for tables having large number of columns I have a requirement where I need to create a table with about 600 columns. Out of 600 columns about 200 columns will be XML columns. Other column will be of other datatypes such as int, varchar, nvarchar etc.

Also about 50% XML column will have about 40 to 60 rows and other will have less number of rows. The entire table will have about 2000 to 3000 rows. Theoretically SQL allows maximum of 1024 columns. But what will be the performance in my scenario? Does this design will effect performance?

Also does SQL Server have limitation for row size?

If a table has 600 columns it is a very good idea to review the database design. You could move several columns into separate tables, especially the ones with 98% and more NULL values.

Also you will run into trouble if your row does not fit into a single page any more. (see here.)


Streaming Replication Failover - how to point second slave at new master? I have a Postgres database HA cluster I've set up in a lab. I'm using PgPool-II to manage fail-over.

For the cluster itself, I have three identical Postgres 9.2 servers set up like so:

Server A - Master DB
Server B - Slave DB with synchronous Streaming Replication
Server C - Slave DB with asynchronous Streaming Replication (possible synchronous server)

When Server A fails, server B takes over (no problems here). However, I need to make sure that Server C is 're-pointed' to stream from Server B.

Currently, I'm achieving this by doing a full base backup from B to C - it works, but seems like a sledgehammer approach given that the two databases should be almost identical at the point when server A fails...

Is there a sensible way to get server C to start streaming from server B without doing a full base backup?

I'm not looking for a hack to get this to work - the lab system will eventually move into a production environment, so I need a solid solution...

Any advice or pointers would be most welcome!

Cheers.

In PostgreSQL 9.2, you should be able to just repoint server C to B by changing it's recovery.conf file, if you are also using log archiving (and have a restore_command defined in your recovery.conf). It should work automatically - but it does require the log archive to work (machine B will send critical information about timeline switching to the log archive, which machine C will then replay).

PostgreSQL 9.3 will be able to deal with this repointing of the slave over pure streaming replication.


PostgreSQL suitabilty of Hash Index on PK and FK I read this not so recent article about Hash Index performance on PostgreSQL, where the author writes:

  ...the hash index performs better than the btree index and the performance difference is in the range of 10% to 22%. In some other workloads we have seen a better performance like with hash index on varchar columns and even in the community, it has been reported that there is performance improvement in the range of 40% to 60% when hash indexes are used for unique index columns.

Assuming that your PostgreSQL is at leat version 10 (to have the index WAL-logged) and (as usually happens) you only have equality comparisons on PK and FK columns, my question is:

Shouldn’t be Hash index preferred to B-Tree index on PK and FK column, due to the (slightly) better performance and for the smaller memory footprint?

While I’m writing, I’m asking if the IN operator can use that kind of index, since it might be the only one other operator (other than =) usually involved in lookup queries on those kind of columns, and if not, this may respond to my answer (negetively).

Thank you!

Hash indexes do not currently support unique indexes, and so not PK or FK either.  Maybe in a future version...


Postgres "session variables" kludge on AWS RDS I am trying to migrate a PostgreSQL database to AWS RDS.

The problem I ran into was that the database in question makes extensive use of the SET kludge.variable TO 'foo' hack, that is, it uses the customized options syntax for storing runtime session data. (In this case, the DB has a slew of triggers, which seem to be basically reinventing the concept of roles. Rewriting the software isn't really an option at this point, so please bear with me here.)

The main problem with migrating that DB to the AWS cloud is that I couldn't find a way to set a sensible default for the custom param. An RDS database doesn't give you Superuser access required for ALTER SYSTEM SET kludge.param TO 'foo', nor do you get to edit postgresql.conf. (It's possible to edit existing DB parameters via RDS's DB parameter group mechanism, but adding new ones won't work. I tried.)

In addition, trying to get the current_setting(kludge.param) doesn't return anything nice if the option isn't set; instead it causes the statement to fail with an SQL error. Here's what I get when I try to update a table without setting the param in the session:

ERROR:  unrecognized configuration parameter "kludge.param"
CONTEXT:  PL/pgSQL function update_modifier() line 1 at assignment

Furthermore, I couldn't find a way to check for the existence of the parameter without producing the same error.

My current idea is to edit all the trigger functions (although there are many triggers, there aren't ridiculously many trigger functions) to trap error 42P02  undefined_parameter with a WHEN clause, and set a default value for the custom parameter in the error handler.

Given that my only goal seems very simple (inserts and updates work sensibly, with or without setting any custom parameters) and my solution seems to fall in the suspicious category of "clever hacks for enabling other clever hacks", I wanted to check with you guys first:

Does anything I'm saying make any sense at all?
Is there an easier way?
If there isn't, is this approach likely to solve my problem?

Thanks for your time in advance.

(We're running PostgreSQL server version 9.5.10.)

AWS Support confirmed that it is, indeed, impossible to set defaults for custom session variables on RDS PostgreSQL.

The next best solution was to trap SQLSTATE 42704 (undefined_object) errors in all the trigger functions with

  EXCEPTION WHEN undefined_object THEN SET kludge.param TO 'default_value';

followed by replicated trigger code, so that the trigger gets properly executed even in the error case.


Avoiding a many-to-many relationship cycle between 3 tables I'm designing a database for a IDLE CPU time donation system. and I struggled to design the database.

The problem is the following.
I have 3 entities (tables):

donors: hold donors' information
devices: a big array of various machine types (Phones, PCs, Tablets...)
events: hold information about the events, that will receive the CPU donations.

The rules the database is based on are.

Each donor owns many personal devices.
A donor can register to events.
By registering to an event, the donor choose which device(s) to donate CPU time from.
A donor can register to as many events he chose to.
A donor can register a device to multiple events.

At first, I created 2 additional tables besides the ones listed above(donors, devices, events):

donor-device : this table contains the various devices (Phone, PC, TV ...) owned by clients.
donor-event: this table contains the donors registered in events and the status of their participation (pending, rejected, accepted).

Then I discovered that this data model is not relevant in my use case. in this case I cannot know the devices that the donor chooses to donate for a specific event.

Here are some solutions I thought of, though I'm not convinced of their optimality.

Adding a column in the client-event table containing a string with the id of the devices donated + the amount of CPU time donated.
Using a JSON field instead if the string.
Adding a third many-to-many relationship device-event-client.

PS: I'm not tied to a relational solution, I can try other models as well.

A way to solve your problem is to think of registrations as entities: a registration has a donor, an event, and a set of devices.

The relationship between donors and registrations has cardinality 1:N (a donor can have more registrations, a registration concerns a unique donor), the relationship between events and registrations has cardinality 1:N as well, while the relationship between registration and devices is M:N (in a registration, a donor can offer various devices, while a device can be registered multiple times for different events).

So, this situation can be represented with 5 tables:

Donors(DonorID, other attributes)
Devices(DeviceID, other attributes, OwnerID)
Events(EventID, other attributes)
Registrations(RegID, DonorID, EventID, maybe other attributes, like Status, Date, etc.)
RegistrationsDevices(RegID, DeviceID, maybe other attributes as TimeDonated, etc.)

with DonorID primary key for Donors, DeviceID primary key for Devices, OwnerIDforeign key referring to a Donor, EventID primary key for Events, RegID primary key for Registrations (that has DonorID and EventID as foreign keys), and (RegID, DeviceID) primary key for RegistrationsDevices (and also foreign keys).


Help needed with several engines use case We are developing an app, aprox 50k RPM read, 1k RPM write, it ask via key and get a JSON.
The search is always made by key.
I'm inclined to use one MySQL 8 table with a Id field and JSON field with innodb. Seems simple, cheap and fast accessing by index. Each index can have n rows (30 max), total size of table less than 100gb.
Response time is important, I think 2-10ms are achievable on MySQL.
The other, more expensive options that I have are DynamoDB and ElasticSearh (can't use another tool).
Can't find a comparison for this use case to help me know if I'm in the correct path. Do you see any cons of using MySql or I'm missing something?
Thanks!!

So any of those options EXCEPT ElasticSearch will probably work ok for you (equal hardware under the hood considered).
ElasticSearch is quite possibly the opposite use case from what you described. It's primarily best for tokenizing full text (like parsing email messages for example) and searching it efficiently, you mentioned your use case will always read by key.
A NoSQL database system or DynamoDB are actually rather fitting for your use case because of two things you mentioned: always reading by key, and storing the actual data in JSON format which allows for semi-structured, unstructured, and varying structured data. So this would be my first choice (might want to look into read limitations of DynamoDB vs other NoSQL database systems, as I thought I recall it was a little more limited based on how it was designed).
Otherwise MySQL should be able to handle your use case ok as well, being a modern RDBMS, but it wasn't really designed for your use case (serving the data only by the key and storing it all in a JSON format). It's a relational database system and therefore was designed for normalized schemas with Tables and relationships between those Tables.


How to do split each quantity and grouping by child customer order in one table I have some data like in picture below on and need to perform some query.

And then I try to produce result like the picture below, but I have trouble and don't know how to split qty by child customer order.

How to get results like picture 2?

This is the query I have tried in order to obtain the result I want:

SELECT item
    ,no_po
    ,sum(qty) as total
    ,parent_customer
    ,child_customer
    ,qty
FROM `detail_order`
WHERE received_date='2018-06-27'
group by item
ORDER BY item asc

select item, no_po, sum(qty) qty_total,
    sum(case when parent_customer='Customer B' and child_customer='ha' then qty else 0 end) as customer_b_ha,
    sum(case when parent_customer='Customer B' and child_customer='he' then qty else 0 end as customer_b_he,
    sum(case when parent_customer='Customer B' and child_customer='hi' then qty else 0 end as customer_b_hi,
    sum(case when parent_customer='Customer B' and child_customer='ho' then qty else 0 end as customer_b_ho,
    sum(case when parent_customer='Customer B' and child_customer='hu' then qty else 0 end as customer_b_hu,
    sum(case when parent_customer='Customer C' and child_customer='ha' then qty else 0 end as customer_c_ha,
    sum(case when parent_customer='Customer C' and child_customer='he' then qty else 0 end as customer_c_he,
    sum(case when parent_customer='Customer C' and child_customer='hi' then qty else 0 end as customer_c_hi,
    sum(case when parent_customer='Customer C' and child_customer='ho' then qty else 0 end as customer_c_ho,
    sum(case when parent_customer='Customer C' and child_customer='hu' then qty else 0 end as customer_c_hu
from detail_order
where received_date='2018-06-27'
group by item, no_po
order by item asc, no_po


Is it possible for unique column to contain multiple null values SQL Server 2008 I want to migrate data contained in an Oracle Table A to SQL Server. The table A contains a column CIN with unique constraint but mutliple null values. The problem is that SQL Server is rejecting the data. Is there any workaround for this ?

You can use a Filtered Index to remove the NULL values from consideration regarding uniqueness. You would first drop the existing UNIQUE CONSTRAINT and then replace it with a UNIQUE INDEX. These two objects are essentially the same given that a UNIQUE CONSTRAINT actually exists via a UNIQUE INDEX (the documentation for creating a column constraint states that UNIQUE is a "constraint that provides entity integrity for a specified column or columns by using a unique index.").

CREATE UNIQUE NONCLUSTERED INDEX index_name
ON SchemaName.TableA (CIN ASC)
WHERE [CIN] IS NOT NULL;


SQL Server services could not be started as 2 instances sharing same 'model' file Please advise me on this issue:

I have one default instance in SQL Server 2005 (sp 3 x64 bits) called instanceA. Then I installed another 2 instances called instanceB and C.

After that I restore master.bak from production server to this instanceB. The SQL Server services for this instance could not be started at all since then. If I turned off the default instance services, instanceB can be started. This is because both of the instances are pointing to the same model.mdf file in MSSQL.1 folder. Hence both instances could not be started simultaneously.

I believe that in production server, the model path is configured to the default folder MSSQL.1 .Is there anyway to change the path to MSSQL.8 that belongs to instanceB upon installation so that both instances A and B could be started together?

How to change the path of pointing to the correct model file?

Thank you.

The paths of the other system databases (model, msdb, and tempdb) can be changed using ALTER DATABASE ... MODIFY FILE. The changes take effect on instance restart.

I blogged about how to move all the system databases here, including a video demo of the whole process. (The demos shows 2008 R2, but it's the same for 2005.) In your case, the files are already in the spot you want, so all you need to do is change the paths.


SQL: SOS: MySQL indexes overloads disk My server is down now, 'couse indexes of 84GB DB are overloads disc.

Here is output of df-h:

/dev/ploop58097p1     193G  187G   16M 100% /
tmpfs                 2,0G     0  2,0G   0% /lib/init/rw
tmpfs                 2,0G  4,0K  2,0G   1% /dev/shm

DB retuns #1030 - Got error 28 from storage engine every time.
I can login in phpmyadmin, but don't know how to delete indexes of tables.

I am glad you mentioned the table was MyISAM.

You can now run this

OPTIMIZE TABLE mytable;

on the 14.8G table.

So why did you get #1030 - Got error 28 from storage engine ?

It wasn't so much the indexes in themselves. Sometimes, a temp table may need to be written to disk. If there is no space for the temp table on disk, mysqld will not crash. It will freeze until space becomes available. I wrote about this before:

Apr 05, 2013 : "Site Offline" MySQL server failing to start and stop
Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?

If you want to compress every table regardless of storage engine, run this code in a script

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /tmp/CompressTables.sql
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN')"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /tmp/CompressTables.sql
less /tmp/CompressTables.sql

If the file looks good to you, run this

mysql ${MYSQL_CONN} -A < /tmp/CompressTables.sql

If all your tables are huge, run this over the weekend


Cardinality rule for bitmap indexes The Oracle documentation includes the following advice:

  A bitmap index should be built on each of the foreign key columns of
  the fact table or tables

In that reference, there is even a bitmap index on the date column. Whatever happened to cardinality rule for using bitmap indexes? Date columns defy that rule the most, but other columns like customer_key are also a little too huge to be considered candidates for bitmap indexes. I can sort of understand putting one on item_key if you don't have thousands of items.

If not a bitmap index, then what - especially for a date column that has a foreign key to a time dimension - typical stuff - month, year, day, etc? Obviously, it's queried often.

I asked this question on Stack Overflow a couple days ago, but I'm going to delete it, since it received no replies.

There was never a rule that bitmap indexes were only useful on columns that had relatively few distinct values.  That was a myth that derived from the fact that bitmap indexes aren't appropriate for columns that are unique or mostly unique and that a lot of the columns that you would want to put bitmap indexes on happen to have relatively few distinct values.

Richard Foote (who probably knows more about indexes in Oracle than any other person on the planet) has a nice article on bitmap indexes with many distinct values that walks through why this is perfectly reasonable and appropriate in much more detail.  A followup article comparing bitmap and b-tree indexes on columns with many distinct values is also well worth reading.


MariaDB RAM usage does not reflect configuration I've got 256GB RAM. InnoDB pool buffer size is 128GB, and InnoDB data + indexes in all databases are about 105GB. Most of that data is frequently queried so, correct me if I am wrong, it should reside in buffer_pool?
I thought that innodb_buffer_pool resides in RAM, but top command shows:
MiB Mem : 257873.0 total,    747.0 free,  74239.4 used, 182886.6 buff/cache

Most of users complain about high RAM usage, and in my case it seems that MariaDB is not using all the RAM it should?
ulimit -v says 'unlimited'.
Is that ok? Can I force putting entire innodb_buffer_pool in RAM, and if so, then how?

MariaDB's memory usage grows over time. It stops growing when certain configurable settings are hit. In practice, it mostly stops growing long before the 'theoretical' limit is hit.
The buffer_pool is allocated only in RAM.  It is the main memory user that grows over time, stopping when it hits innodb_buffer_pool_size.
Since the buffer_pool is a "cache", the data can be much bigger than the buffer_pool; the downside is the need to do more I/O to pull "blocks" of data (or indexes) into the cache, bumping out other blocks.
If all of the activity is concentrated on a subset of the data (eg, "recent" items), then the buffer_pool grows until "big enough" and may not actually expand to innodb_buffer_pool_size.  I/O activity will be low.
If the buffer_pool is bigger than all the InnoDB data and indexes you have, it will grow to about that size and then simply stay at that size.
The innodb_buffer_pool_size should not be so large that you run out of RAM.  When that happens, swapping occurs.  If there is no swap space, then the process is killed.


how to sucessfully revoke view any definition (or any other server permissions)? I had to grant a server permissions to an active directory group, in order to solve a LIVE Bug:

GRANT VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
GO

after the permission was no longer needed I revoked it:

revoke VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
go

this didn't seem to revoke the permission on all databases
so I have tried it in a different way:

use master
go
revoke VIEW DEFINITION ON SCHEMA::[dbo] to [mycompany\webDevSeniors]
go

still, when I use one of my procedures to check which permissions are active in a specific database, it shows the "VIEW DEFINITION"

sp_getlogindbpermissionsX @db='ATPayment',@login='MYCOMPANY\webDevSeniors'

as you can see on the picture below, the permission is not there at server level:

Problem Solved:

At the end, this is how I solved this problem:

I downloaded the sp_foreachdb stored procedure by Aaron Bertrand

And then I run the following command on my server:

sp_foreachdb @command='use ?; REVOKE VIEW DEFINITION ON SCHEMA::[dbo] to [mycompany\webDevSeniors]'
   --         ,@print_dbname = 1
            --,@print_command_only = 1

I believe this is because when you ran:

GRANT VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
GO

Grant was given to that particular user. But when you tried to revoke, that was at the schema level.(2nd run). However when initially you gave the GRANT access , it was not specified at object level.Since REVOKE has no affect if the specified permission does not already exist.

I believe starting with creation of user and then assigning the roles accordingly can help here.

Please read Understanding GRANT, DENY, and REVOKE in SQL Server which will help you in this scenario.


Short answer:  No.

Long answer:

A "covering" index is usually better than a non-covering one.
A covering index for the query you mention would contain all the columns in the table.  This is usually a 'bad' idea if there are more than, say, 5 column.

But...  I suspect that is not the real problem, nor the real question.

What is the DATATYPE of phonenumber?  I suspect it is VARCHAR(...), correct?  Yet you are asking for a numeric comparison by saying where phone_number = 98371381.

Add quotes (and make sure it is indexed):  where phone_number = "98371381".  Based on the table name, I suspect that the that is the PRIMARY KEY?  If so, simply PRIMARY KEY(phone_number) is better than any "covering" index.

Always provide SHOW CREATE TABLE when asking performance questions.  Else, we have to guess at too many things (datatype, PK, number of columns, engine, etc).


Are there any issues with sparsely populated partitioned tables The partitioning strategy used by a third party product results in a fairly sparse distribution of data.
i.e. of our ~900 partitions only ~300 have data. The way this works it will only become more sparse over time.
Is there any value in merging consecutive empty partitions (I can guarantee there will be no inserts into them) or is this a waste of time?

Each partition is essentially a separate table, so there is some cost involved in query optimization, statistics, and metadata management.  So if the third-party product supports you removing partition boundary points they created, I would probably do it.

See

  The new, higher limit of 15,000 partitions affects memory, partitioned
  index operations, DBCC commands, and queries. This section describes
  the performance implications of increasing the number of partitions
  above 1,000 and provides workarounds as needed.

Partitioned Tables and Indexes - Performance Guidelines


SQL Error [1242] [21000]: Subquery returns more than 1 row (AMATEUR) Basically, everything works in the brackets by itself. The statement in the brackets bring back 4 different values all on different rows.
How can I get around the fact that I need to apply the sql statement outside the brackets to the 4 values returned from the statements in the brackets when subqueries can only return 1 row?
SELECT
    to2.Name, to2.PhoneNum
FROM
    tbl_operatordesc to2
WHERE
    to2.operatorID = (SELECT
            to3.operatorID
        FROM
            tbl_operatorrouterelation to3
        WHERE
            to3.routeID = (SELECT
                    tr.routeID
                FROM
                    tbl_route tr
                WHERE
                    tr.`Start` = (SELECT
                            tb.busstopID
                        FROM
                            tbl_busstop tb
                        WHERE
                            tb.Description = 'Durham Estate')
                        OR tr.Destination = (SELECT
                            tb.busstopID
                        FROM
                            tbl_busstop tb
                        WHERE
                            tb.Description = 'Durham Estate')))

Thanks!

You should be joining between each table on the fields you're trying to compare in the WHERE clause. Then your  statement can be on top of that.


Why do I have 2 connections when im using 1 client? The command:

select * from pg_stat_activity

shows all currently active connections.
The database is hosted by AWS RDS and I am accessing the database with pgAdmin 4, thus I expect one other connection than mine and that is the rdsadmin, but it seems like I have 2 connections.

Output:

Why do I have two connections when using one Client?

The second connection gets started when you start the query tool.


Can the account name of 'distributor_admin' be changed? 'distributor_admin' is a SQL-auth account that is used for replication. Out of the box it is granted sysadmin and currently it looks like that can not be dialed back to db_owner (see related Does distributor_admin need sysadmin? ).

According to some sources (pg4 of PDF) distributor_admin is one of the top eight SQL usernames used in brute force attacks. It is considered a best practice to rename the 'sa' account can the 'distributor_admin'be renamed also?

I looked around and don't find anyone suggesting this alternative. I found a few posts that implied the possibility of the 'distributor_admin' being hard coded into the replication process.

There is not a lot of recent documentation about the distributor_admin account with replication.

No it can't be renamed.  The good news is that the account only exists on the distributor, which doesn't really contain any user data.

You can safely disable the account when you aren't setting up new replication publishers. If you need to setup a new publisher, enable the account setup the publisher then disable the account again.

It does need to have sysadmin rights. It needs to be able to create linked servers.


Best way to design a database and table to keep records of changes? I need to setup a history feature on a project to keep track of prior changes.

Let's say I have two tables right now:

NOTES TABLE (id, userid, submissionid, message)

SUBMISSIONS TABLE (id, name, userid, filepath)

Example: I have a row in notes and the user wants to change the message. I want to keep track of it's state before the change and after the change.

What would be the best approach to setting up a column in each of these tables which will say if an item is "old." 0 if active OR 1 if deleted/invisible.

I also want to create a history (AUDIT TRAIL) table which holds the id of the prior state, the id of the new state, which table these id's relate to?

When designing versioning capabilities in your data, there are several minimal (I would think) requirements:

Each version of the data should be self-contained and independent of other versions. This means no flag or other indicator showing which is the current version and which are "history." It also means updating the entity means inserting a new version only -- no updating of previous versions needed.
Avoid what I call Row Spanning Dependency. That is where one field (End_Date) of a row must remain in synch with another field (Start_Date) of a different row. This makes working with the data more difficult and is an excellent source of anomalies.
The current version and all past versions should be in the same table. This makes it possible to use the same query to view past data "as of" a particular date and to view the current data.
Foreign keys to data that has been versioned should work the same as normal (unversioned) data.
The design should be so simple or universally understood that the learning curve for new developers is minimized.

Here are the slides of a presentation I have made a few times at tech fairs. It covers how all the above can be done. And here is a document that goes into more detail. I must make apologies for the document -- it is a work in progress and not all sections are completed. But it should give you all the information needed to implement anything from simple versioning to full-on bi-temporal access.


Migrating from 32 bit using pg_basebackup Has anyone migrated a 32 bit postgres 9.2 database to a 64 bit postgres 9.2?

I'm trying to use pg_basebackup but I'm not sure whether this is the right approach.

pg_basebackup is not the right approach. It won't work, because the 32-bit and 64-bit data directories are not compatible.

You will have to dump and reload using pg_dump and pg_restore, plus a pg_dumpall --globals-only restored via psql for user accounts etc.


Pointing DB to new Oracle home in primary and standby oracle database I needed to apply a security patch to production database. To minimize the outage time, I cloned the oracle home and applied the patch to this newly created oracle home. With production DB there is a physical standby database. I cloned the oracle home in standby database too and applied the same patch.
Now , I need to point both the databases to respective new Oracle home. Guide me on how to do this when there is a physical stand by involved.

I went through multiple oracle blogs etc. the steps provided are to apply the patch simultaneously on both primary and stand by database. However , in my case i have cloned the Oracle homes in both the environments. I could not find any useful info on doing this way. Any help/hints are appreciated.

With Grid Infrastructure, you need to modify the configuration with srvctl, for example after upgrading to a new release:

srvctl stop database -d db_unique_name
export ORACLE_HOME=<..new home...>
export PATH=$ORACLE_HOME/bin:$PATH
srvctl upgrade database -d db_unique_name -o oracle_home

Alternatively you can just re-register the database (add back with srvctl from the new home):

srvctl remove database -d db_unique_name
export ORACLE_HOME=<..new home...>
export PATH=$ORACLE_HOME/bin:$PATH
srvctl add database -d dbname -o oracle_home ...

Without Grid Infrastructure, you can start your databases manually, or with a custom script. You need to modify these scripts and update ORACLE_HOME in them. Also, you need to update /etc/oratab if you are on a UNIX/Linux platform. This is also needed if you use the scripts provided by Oracle (dbstart and dbshut).

If you do not have any scripts for starting and stopping the databases, all you need to do is:

shutdown immediate
export ORACLE_HOME=<..new home...>
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
startup

Finally, do not forget to copy other configuration files from the old home (pfile/spfile, listener.ora, tnsnames.ora, sqlnet.ora, etc.).


SQL Replication components install in 2016 standard edition i would like to install the components of Replication in sql server where the initial installation version is 2016 standard with SP1 (KB 3182545) - 13.1.4001.0 but now the SQL version is 2016 standard (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4.

Question: Will this be good to go ahead and install components with initial setup file ?

Your currently installed instance is at version 13.0.4466.4 (2016 SP1-CU7-GDR), and your installation media is at a lower patch level for the same major version (in your case, 2016 SP1, without the CU).

When you install an additional component (Replication, Full-text search, etc) with install media at a lower version, the installation will succeed, but those components will be unpatched. Essentially, the database engine & previously installed bits will be at one version (SP1-CU7) and the newly installed bits will be at a lower version.

After installing the additional components, you will want to re-patch the instance to install the CU. This will ensure that all components on the instance are at a consistent patch level, and ensure you have a supported configuration.

Essentially, whether you're installing additional components or a brand new instance, you'll want to run through the same combination of installers to ensure you get the desired major version + SP + CU.


Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x) Given this data:

gid | val
1   | a
1   | a
1   | a
2   | b
3   | x
3   | y
3   | z

the following queries return groups (gid) that contain exactly one distinct value (val):

SELECT gid FROM t GROUP BY gid HAVING MIN(val) = MAX(val)
SELECT gid FROM t GROUP BY gid HAVING COUNT(DISTINCT val) = 1

People seem to suggest that the first variant would be faster (if assuming appropriate indexes exist then looking up MIN and MAX would be faster than counting all values). Is that a fact or a myth.

The short version is that you should expect MIN(val) = MAX(val) to be better for rowstore queries in all cases and COUNT(DISTINCT val) = 1 to be better for columnstore queries when val is a string column.

I put 6.4 million rows into a table to test with. The data has a roughly similar data distribution to your sample data:

DROP TABLE IF EXISTS dbo.t224998_2;

CREATE TABLE dbo.t224998_2 (
gid INT,
val VARCHAR(20)
);

INSERT INTO dbo.t224998_2 WITH (TABLOCK)
SELECT
CASE WHEN q.RN % 7 <= 2 THEN q.RN - q.RN % 7
WHEN q.RN % 7 >= 4 THEN q.RN - q.RN % 7 + 4
ELSE q.RN END
, CHAR(65 + q.RN % 7)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

Here are the first 14 rows in the table:

Here are the query plans when running your pair of queries without any indexes:

The query with MIN and MAX only has a single hash aggregate operator. The COUNT(DISTINCT) query has two. For the second query, the rightmost operator only keeps the distinct rows and the leftmost operator performs the count. Not surprisingly, the DISTINCT query is about twice as slow.

Creating the following index makes the two queries more competitive:

CREATE INDEX IX1 ON dbo.t224998_2 (gid, val);

Now the plans look like this:

Now the distinct query is about 25% slower. It should be stressed here that for none of these plans are the MIN and MAX values "looked up". You're querying the table without a filter. SQL Server is going to scan all of the rows of the index or table. The index is useful because it can be scanned in key order and that allows the aggregate to be calculated more efficiently. For the MIN(val) = MAX(val) query, the stream aggregate reads the ordered rows and keeps track of the min and max value seen for each unique value of gid. It passes the row along to the next operator when it finds a new value for gid. At no point is an index seek performed to get the minimum or maximum value. You can write a query to do that but it's somewhat convoluted.

The COUNT(DISTINCT) query again splits the work up into two aggregates. Both aggregates take advantage of the ordering of the index. The rightmost one removes duplicate rows and the leftmost one performs the count.

If I change the table to a columnstore one without any nonclustered indexes the second query becomes the winner. Here are the plans:

The MIN(val) = MAX(val) performs all of the aggregate work in row mode. Aggregates that returns string columns are not supported by batch mode. This restriction is documented by Microsoft. COUNT(DISTINCT val) is supported by batch mode and as a result all of the aggregate work is performed in batch mode. That query is over twice as fast as the MIN(val) = MAX(val) option.


How to prevent memory optimized tables from blowing out OPT_MEMORY_OPTIMIZED\$HKv2 Microsoft SQL Server 2017 database, Developer edition. Just updated to latest version (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64).

It uses memory optimized tables. When I run a very large stored procedure which deletes many records in many tables, I find that the database creates more and more big files in the directory C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OPT_MEMORY_OPTIMIZED\$HKv2, this until the disk is full.

I use CHECKPOINT in strategic places in the stored procedure. But this doesn't stop the growth of that directory.

I tried to solve this problem by setting a limit to size of the OPT_MEMORY_OPTIMIZED database file. However, after the limit was reached, SQL Server then started reporting

  "Cannot continue the execution because the session is in the kill state."

The only way I found to get out of this situation was to delete the database and restore a backup.

How can I stop the growth of the OPT_MEMORY_OPTIMIZED\$HKv2 directory?

It turned out the prolem was caused by the fact that:

My database used recovery mode Full; and
I wasn't backing up my logs.

Once I set my database to use recovery mode Simple the problem went away.


Does "Connect" statement for every query indicate broken connection pooling? there's only one client application running queries against a MySQL instance.

After enabling MySQL's general query log (configured to log every query) I noticed that every query starts with a "Connect".

Does this imply connection pooling isn't working?

Thx,
Christian.

Edit1:

DB is accessed by PHP application (SugarCRM). It's configured to use persistent connections.

> egrep connections /var/www/html/sugarcrm/config.php
'disable_persistent_connections' => 'false',

> egrep -A7 dbconfigoption /var/www/html/sugarcrm/config.php
'dbconfigoption' =>
array (
'persistent' => true,
'autofree' => false,
'debug' => 0,
'ssl' => false,
'collation' => 'utf8_general_ci',
),

Main Question (not trying to solve it at the moment, just want to understand): If in the log every query starts with a "Connect" - does this then imply that no persistent connections are used?

Three aspects must be considered
ASPECT #1 : PHP
PHP has a setting for Persistent Connections
[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent=On

Make sure this is on in php.ini if you want to use Persistent Connections
ASPECT #2 : Your Code
Since mysql_pconnect is deprecated, make sure you are using one of the following

mysqli-connect with p: before the host name
PDO::__construct() with PDO::ATTR_PERSISTENT as a driver option

ASPECT #3 : MySQL
When mysqld sees a connection attempt, it records it statistically as one of the following

Connections : Connections Attempts (Successful or Not)
Aborted_connects : Failed Connection Attempts

If the general log is echoing just the word Connect as an event, chances are that's just a Connection attempt. Any subsequent events on that connection would be operating on a established thread (DB Connection). The general log will not tell you whether the DB Connection is newly created or already established.
To actually know if the connections are coming and going, you should monitor

Threads_connected : The number of currently open connections
Threads_created : The number of threads created to handle connections

To actually see them in a single query, please run this
SELECT * FROM information_schema.global_status WHERE variable_name IN
('Connections','Aborted_connects','Threads_connected','Threads_created');


Why does this query work with a CTE, but not with a derived table? I was looking to identify the 'Email' that was duplicated.

The two simplest ways I thought of are shown below, however for some reason the query using the derived table produces the error: Incorrect syntax near ')'.

CREATE TABLE #TEMP (
    ID INT PRIMARY KEY,
    Email VARCHAR(255)
    )

INSERT INTO #TEMP VALUES
    (1, 'a@b.com'),
    (2, 'c@d.com'),
    (3, 'a@b.com')

-- Fails to run
SELECT Email
FROM (
    SELECT Email, COUNT(*) AS Count_
    FROM #TEMP
    GROUP BY Email
    HAVING COUNT(*) > 1)

-- Runs successfully
;WITH cte AS (
    SELECT Email, COUNT(*) Count_
    FROM #TEMP
    GROUP BY Email
    HAVING COUNT(*) > 1)
SELECT Email FROM cte

I am wanting to understand why this may be. What is the issue with the query using the derived table?

You are missing an alias for the derived table.

Books online - Emphasis mine

  When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.


Pivot data type error I have a table which needs to do pivot as column to row, but getting data type error,
I have below table data

Need to pivot and output should be like

I tried with query:

select prod As product_id , para As parameter_id
from
(
  select product_family_id,product_id,parameter_id
  from product_family_exclusion
) d
pivot
(
  max(product_id)
  for parameter_id in (prod,para)
) piv;

But getting error:

  Msg 8114, Level 16, State 1, Line 15 Error converting data type
  nvarchar to int. Msg 473, Level 16, State 1, Line 15 The incorrect
  value "prod" is supplied in the PIVOT operator.

What I am missing about PIVOT table?

I don't think your sample output is correct for product_id 10211.  You seem to be missing parameter_id 416.

Here is an example of using dynamic SQL (in SQL Server) to solve the problem.  You could probably adapt it to other RDBMS's.  I took the liberty of generating unique column names for parameter_id because using the same column name might be problematic for the PIVOT.

--Demo setup
set nocount on
DECLARE @cols AS NVARCHAR(MAX),@colsCoalesce AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
drop table if exists Table1
create table Table1 (product_family_id int, product_id int, parameter_id int)

insert into table1(product_family_id,product_id,parameter_id) values
(111,3227,334),
(111,3227,335),
(1112,10211,414),
(1112,10211,415),
(1112,10211,416),
(1112,10211,417),
(1112,11784,514),
(1112,11784,515),
(1112,11784,516),
(1112,11784,519)

--The solution
drop table if exists PivotTemp
select * into PivotTemp from
(
select product_family_id, product_id, parameter_id, 'parameter_id' + convert(varchar(5),ROW_NUMBER() over(partition by product_family_id, product_id order by parameter_id)) as GeneratedParameterHeading from table1
)a
--select * from PivotTemp
SElect @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.GeneratedParameterHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
SElect @colsCoalesce = STUFF((SELECT distinct ',' + 'coalesce(convert(varchar(100),' + QUOTENAME(c.GeneratedParameterHeading) + '),'' '') as ' + QUOTENAME(c.GeneratedParameterHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

print @cols
print @colsCoalesce
set @query = 'SELECT product_family_id, product_id, ' + @colsCoalesce + ' from
            (
                select product_family_id
                    , product_id
                    , parameter_id
                    , GeneratedParameterHeading
                from PivotTemp
           ) x
            pivot
            (
                 max(parameter_id)
                for GeneratedParameterHeading in (' + @cols + ')
            ) p '

print @query
execute(@query)

| product_family_id | product_id | parameter_id1 | parameter_id2 | parameter_id3 | parameter_id4 |
|-------------------|------------|---------------|---------------|---------------|---------------|
| 111               | 3227       | 334           | 335           |               |               |
| 1112              | 10211      | 414           | 415           | 416           | 417           |
| 1112              | 11784      | 514           | 515           | 516           | 519           |


Limiting the occurence of a value in PostgreSQL Using PostgreSQL 11.7 and I have to model some real-world constraints. Effectively, I'm modeling a graph and an edge, of course, has two nodes. Each node can have zero, one, or two edges. Thus, I need a constraint similar to:

node UUID NOT NULL CHECK (SELECT true FROM this_table WHERE node = NEW.node HAVING COUNT(*) <= 2)

Except that this syntax is illegal. I also have the following problem since this applies to four of our tables:

I don't know the table name up front
I don't know the column name up front
I don't know the allowed number of occurrences up front

I almost have it working, but I can't figure out how to extract the value of the NEW.node. I have written a trigger that's close, but no cigar:

CREATE DOMAIN uint2 AS int4 CHECK(VALUE >= 0 AND VALUE < 65536);

CREATE OR REPLACE FUNCTION limit_count() RETURNS TRIGGER AS $$
DECLARE
    total       integer;
    this_table  text := TG_TABLE_NAME::regclass::text;
    this_column text := TG_ARGV[0]; -- first argument (triggers cannot define arguments directly)
    this_limit uint2 := TG_ARGV[1]; -- second argument
BEGIN
    IF cardinality(TG_ARGV) <> 2 THEN
        RAISE EXCEPTION 'limit_count() trigger expects a column name and a limit';
    END IF;

    EXECUTE FORMAT('SELECT COUNT(%I) FROM %I WHERE %1$I = $1', this_column, this_table)
       USING NEW.quote_literal(this_column)
       INTO total;

    IF total + 1 > this_limit THEN
        RAISE EXCEPTION '%.% has % occurences, but must not have more than %', this_table, this_column, total, this_limit;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fibre_route_edge_start_node_limit_trigger BEFORE INSERT OR UPDATE ON fibre_route_edge
    FOR EACH ROW EXECUTE PROCEDURE limit_count('start_node', 2);
CREATE TRIGGER fibre_route_edge_end_node_limit_trigger BEFORE INSERT OR UPDATE ON fibre_route_edge
    FOR EACH ROW EXECUTE PROCEDURE limit_count('end_node', 2);

The above fails with:

ERROR:  schema "new" does not exist
LINE 1: SELECT NEW.quote_literal(this_column)
               ^
QUERY:  SELECT NEW.quote_literal(this_column)

Happy to solve this problem without a trigger, but I'm kinda stumped right now. And examples I've seen using hstore and other tools have been incomprehensible to me.

You can't access the fields of the new record dynamically using names stored in a variable.

The only way I can think of, is to convert the NEW record into a JSON value, then you can access the value "dynamically" through the name:

this_column_value := (to_jsonb(new) ->> this_column)::int;
EXECUTE FORMAT('SELECT COUNT(*) FROM %I WHERE %I = $1', this_table, this_column)
  USING this_column_value
  INTO total;


Photo caption and keyword associations I am creating a database that references images. Keywords and captions will be part of the metadata. These captions and keywords are not necessarily added to the photos with the intent of facilitating quality searches.

I would like to effectively convert the information in the captions into keywords. Obviously some of the words in the captions will not be useful (e.g. "the","he","she","of",etc.) and I would exclude them, but other words will be very useful, especially if they appear in combination with other words.

I'm trying to figure out the best way to associate these keywords and words from captions with the images. Would it be best to create a table of images (filenames, path to file, size, etc.) and a table of words, where each word has an ID, and then an associative table that references the words to the images? Or perhaps two associative tables, one to link caption words and the other to link keywords?

Is this a good approach?

The best solution depends on your RDBMS, but in general you shouldn't need to manually sanitize the captions because the database will have a "stopword list" or "stoplist" which is used to exclude words from a full-text index (not the same as a regular clustered/non-clustered index). You can modify that list to specify which words are excluded from the full-text index. In most databases, the list would already include prepositions, pronouns and other noise words, meaning a search for birds AND flight would return a caption of "birds in flight" (assuming it exists). Additionally, most systems can stem the indexed terms and give you the ability to search on inflections of a term, e.g. searching for inflections of "fly" will return flying, flew, flown, etc.

If the captions are the keywords, you don't need to parse keywords from them; just add a full-text index to the caption column. If you have or want to add more keywords in addition to the captions, you can index them separately and use them to augment or modify the caption search using the RDBMS' built-in numeric ranking/scoring of results - a hit on a keyword could increase the rank while a miss could do nothing or even decrease it.

If the captions have a 1:1 relationship to images (which they usually do), you don't even need a separate table for those; just store the caption in another column of the images table. Additional keywords could be normalized and stored in a separate table, which would require an intermediate join table with foreign keys from images and keywords. However, this adds complexity and you may find it better to just store a space-delimited list of words in another column in the images table.

The solution you select will depend on several factors including business needs, performance requirements and use cases, i.e. what types of searches you need to support: exact phrase, Boolean AND/OR, fuzzy matches like SOUNDEX, etc. Sometimes full-text indexing isn't even necessary. For instance, if stemming and noise word exclusion aren't important, a search using LIKE with wildcards may suffice.


Mysql: binary log vs transaction log In mysql with innodb, there are two types of logs: binary log and transaction log. I know transaction log is specific to innodb. But when comes to replication, backup and crash recover, what roles each of them play and what is the different?

Thank you!

The binlog is a history of the writes to the database.  It is vital for replication.  It can also be used in certain "incremental" backup techniques.

The transaction log (usually in files iblog1 and iblog2) is vital to InnoDB for multiple reasons:  ROLLBACK, crash recovery, and possibly more.

Manual page about binlog
non-InnoDB logs

Redo log

There are lots of web pages discussing these files from various viewpoints.

There are many ways to do "backup".


Simple model database transaction log full 'CHECKPOINT' I have a SQL Server 2012 SP1 database which has a problem that the transaction log is full. It consists of a single data file and a single log file, both about 800MB.

I had 5GB free disk space, and I've enlarged it to 20GB+, just in case there was a disk space problem.

The database is in SIMPLE recovery model.

I cannot do a full database backup - it fails with

  transaction log is full, with wait_desc of 'CHECKPOINT'

MTA: Note - the wait_desc is CHECKPOINT (someone asked below about the wait desc strings)

I cannot add a log file - it fails with the same error.

MTA: I cannot change the growth model of the one existing log file - it fails with the same error.

MTA: The existing log file has +10% growth.

MTA: I also cannot increase the size of the existing log file - it fails with the same error.

I cannot change the recovery model to full or bulk-logged - it fails with the same error.

I cannot execute a manual CHECKPOINT - it fails with the same error.

DBCC LOGINFO (dbid) shows that there are a couple hundred VLFs, all in status 2.

I'm not using change tracking, mirroring, or transaction log shipping.

What can I do to resolve this?

(As recommended, moved from stackoverflow to dba.stackexchange)

MTA: More tech details here (but no answers at all): http://www.sqlservercentral.com/Forums/Topic1451836-2799-1.aspx

eg, "DBCC OPENTRAN
gives me no open transactions"

It sounds like you've got a huge transaction that has remained open which has done a lot of work.  Run DBCC OPENTRAN on the database and see how long the oldest transaction has been open for.  You'll probably need to kill that transaction (or have the user commit if it's someone in Management Studio).  Then the log will clear automatically.


SQL Server connection failed for domain user from Mac OS machine I have SQL Server 2008 R2 SP1. I am using connection string to connect to SQL Server.

In connection string I am passing the driver path, server name, TCP port number, user name with which I want to connect, password, DSN name, TDS driver version number, db name.

I am using Free TDS driver for connecting from Mac OS machine.
On SQL server side I am having Windows authentication mode.
When I tried to connect by entering the user name and password of the user who has installed the SQL server or admin of the SQL server, it works fine.

But if I try with normal domain user, I am getting the SQL Server connection failed error.

After searching about it I found that some authentication is failing. Can anyone help me in resolving this issue?

I believe that some settings need to be changed or there should be some modification on connection string.

As Jason Cumberland mentioned in his comment connecting from a Mac is problematic since the Mac does not support Active Directory so is not likely to work with Integrated Security = yes or Trusted Connection = True.  You will need to use standard SQL Server Authentication where you specify the username and password, and set Trusted Connection = False.


PostgreSQL requires "GROUP BY" when aliasing a table? Here's something strange: there is a large table named "products" which is partitioned with inheritance such that there are two children: products_active and products_inactive, and the constraint is that in products_active, status=1, and products_inactive gets all the other statuses.

There is a large query which joins a bunch of tables, of which this is the first part:

SELECT
  products.id, products.status, products.brand_id, products.name, products.description, products.data, products.website,
  products.packaging, products.container, products.country_of_origin, products.category_id, products.product_type_id, products.tsv_keywords,
  COUNT(prices.id) as prices_count,
  ROUND(AVG(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as avg_price,
  ROUND(MAX(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as high_price,
  ROUND(MIN(currency_convert(prices.amount,currencies.currency_code,'USD')),2) as low_price,
  ts_rank(tsv_keywords, plainto_tsquery('merlot')) as rank,
  ROUND(AVG(ST_Distance(ST_GeographyFromText('SRID=4326;POINT(0.001 0.001)'),ST_GeographyFromText('SRID=4326;POINT(' || stores.longitude || ' ' || stores.latitude || ')')))) AS distance
FROM
  products
  JOIN product_types ON products.product_type_id = product_types.id
  JOIN categories ON products.category_id = categories.id
  JOIN prices ON prices.product_id = products.id
  JOIN currencies ON prices.currency_id = currencies.id
  JOIN stores ON prices.store_id = stores.id
  JOIN brands ON products.brand_id = brands.id
  JOIN merchants ON stores.merchant_id = merchants.id
  JOIN manufacturers ON brands.manufacturer_id = manufacturers.id
, delivery_zones
WHERE ...

(full query here: http://pastebin.com/VjJPTQWj)

The problem is: note the FROM products... part, if I replace this with FROM products_active AS products, the query errors out with:

ERROR:  column "products.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:   products.id, products.status, products.brand_id, products....

The two tables (products and products_active) have identical structures! They are inherited from one another!

Am I missing a subtlety in aliasing table names?

The problem is that the 2 tables are not identical.

The first has (id) as the PRIMARY KEY while the second has (id) a UNIQUE constraint (or index, doesn't matter). This might seem like a minor detail but it isn't.

Postgres has added in version 9.1, a feature were functionally dependent columns need not be mentioned in a GROUP BY clause (if the column they depend on is mentioned in the GROUP BY) and still used in the HAVING, SELECT and ORDER BY clauses. However the implementation is not 100% complete. It doesn't identify all possible functional dependencies but only those coming from PRIMARY KEY constraints. While a UNIQUE constraint with all columns NOT NULL does not differ in any meaningful way from a PRIMARY KEY constraint, the implementation does not consider UNIQUE constraints.

So, for example, with these two very similar tables (test in SQLfiddle):

CREATE TABLE products
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    PRIMARY KEY (id)
  ) ;

CREATE TABLE products_active
  ( id INT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    UNIQUE (id)
  ) ;

The query will succeed for the first table:

SELECT p.id, p.status, p.name
FROM products AS p
GROUP BY p.id ;

while it will fail for the second:

SELECT p.id, p.status, p.name
FROM products_active AS p
GROUP BY p.id ;

with:

  ERROR: column "p.status" must appear in the GROUP BY clause or be used in an aggregate function


Shall I use enum when are too many "categories" with PostgreSQL? Problem

I plan to load a CSV with more 10 million records into PostgreSQL v12.1, one of its columns has "categorical" values, so creating an enumerated type for it seems to be a good choice, but it holds 208 categories.

The shortest field is 2 and longest is 11 character long. The Average of all fields is 2.4. The character encoding is UTF8, but all characters are ASCII.

Questions:

Which type should I use enumerated or varchar?

Additional info

I discard char because the official PostgreSQL documentation states the followig about char, varchar and text:

  Tip: There is no performance difference among these three types, apart
  from increased storage space when using the blank-padded type, and a
  few extra CPU cycles to check the length when storing into a
  length-constrained column. While character(n) has performance
  advantages in some other database systems, there is no such advantage
  in PostgreSQL; in fact character(n) is usually the slowest of the
  three because of its additional storage costs. In most situations text
  or character varying should be used instead.

An enum value in PostgreSQL occupies 4 bytes on the disk (see 8.7.4. Implementation Details). Considering this and 2.4 average string length using the enum type would lead a slightly higher disk usage (Short stings in PostgreSQL needs one extra byte disk space). Still I have the intiution that using enum is a better choice, because its implementation makes many operations faster against it.

With an average of 2.4 characters (more relevant: avg bytes - but that's the same for all ASCII characters) I would not bother to use enums. Those occupy 4 bytes on disk plus, possibly, alignment padding. (text does not require alignment padding.) You are not even saving storage and get more overhead for it.

With most values below 7 characters (= 8 bytes on disk), an index on a text category column will also be only slightly bigger than one on an enum. (Space for data is (typically) allocated in multiples of 8 bytes.)

For a fixed number of 208 categories, a "char" encoding (not to be confused with char!) might be an option to save storage. See:

How to store one-byte integer in PostgreSQL?

But, again, not worth the trouble for such small strings. Just use text. Maybe enforce correctness with a FK constraint to a category table like:

CREATE TABLE category (category text PRIMARY KEY);

Also a good place to store additional information per category. And you can easily modify the set of categories. Make the FK constraint ON UPDATE CASCADE and you can change category names in one central place. Make it ON DELETE SET NULL , and you can easily remove a category. Etc.

Related:

What is the overhead for varchar(n)?
Calculating and saving space in PostgreSQL


What is the best storing device for DB transaction log? I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 writes per second 6Kb each).
Setting innodb_flush_log_at_trx_commit to 2 solved problem and now load in average 5%. However, MySQL documentation says:

When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2.
With a value of 2, only an operating system crash or a power outage can erase the last second of transactions.

Therefore, it is possible to lose transactional log for last second.
I'm not experienced in configuring hard for DB servers and therefore want to know what modern solutions exists and are most suitable for DB's transactional log giving reliability and high IO capable.
By the best I mean different variants, but personally would prefer optimal combination of performance/price. Any way I think it will be great to collect here different variants.
I know that usual answer will be "use SSD", but as I'm aware of its storing principles it's not reliable solution for transactional log and it should relatively fast fail. From first look, it may seem that rewrite count of modern SSD is enough to operate for years. However, as I know SSD rewrite data by blocks and for example if you want to write 1b of data SSD will rewrite entire block, which may be 4Kb. So for 1 transaction log rewrite circuit SSD might rewrite its blocks many times.
From my perspective, ideal storing device will be a combination of Battary+RAM+HDD with relatively small size (around 5Gb or less). Which in normal operation uses only RAM and on electricity black out flushes all data to HDD. It should be all in one device, not a software solution. However, I do not know if they exist.

You really need a fast HDD, but you need a properly sized innodb_log_file_size.

Why not SSD for MySQL ? I learned something from this layout from a FaceBook Engineer's blog

I wrote old posts about this

Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?
Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Aug 12, 2014 : Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

I even mentioned doing this for PostgreSQL : Postgres Write Performance on Intel S3700 SSD

BTW See my post MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined? for determining the right size for innodb_log_file_size. You don't want a small HDD for ib_logfile0 and ib_log_file1.

Give it a Try !!!


What is the difference between right/left placement of column name in a WHERE clause? Both of the following queries return the same output but I'm curious whether there is underlying difference between these two syntaxes.

    SELECT * FROM table
    WHERE first_name = 'John';

and

    SELECT * FROM table
    WHERE 'John' = first_name;

There is no semantic difference. It is purely a matter of style.

The first form is much more common, in my experience.

The order of WHERE clauses with respect to AND does not matter either.


SQL Server performance issue (CXPACKET wait type) I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I reduce MAXDOP or "cost Threshold for Parallelism" to avoid more parallelism and reduce CXPACKET wait type?

Any help would be appreciated.

General consensus seems to tend torwards setting Cost Threshold for Parallelism to 50 and go from there.
Why Cost Threshold For Parallelism Shouldn’t Be Set To 5

This even includes official reccomendations from VMWare regarding SQL Server Setup with vSphere. Section 4.5 of the Guide "Architecting Microsoft SQL Server on VMware vSphere®" states:

  The default value is 5 which is usually considered too low. [...] The recommendation instead is to increase the CTFP value from 5 seconds to approximately 50 seconds to make sure only large queries run in parallel. Set the MAXDOP according to Microsoft’s recommendation for the number of cores in the VM’s NUMA node (no more than 8).

The usage of of the unit "seconds" should be taken with a grain of salt because on modern systems and versions of SQL Server it will not reflect the actual duration in seconds.

Regarding MAXDOP there is a comprehensive guide found here: Configure the max degree of parallelism

So in your case I would start with the following settings and test them out:

Cost Threshold for Parallelism: 50

Maximum Degree of Parallelism: 8


Is it possible with SQL Server 2008 to have a Partioned Tables with an Identity Column? I'm not thinking of using it for the partition function.
I just want to know, if it is supported or not.

Don't tell me why it is not a good idea to use such columns on partitioned tables.

I would never tell you it is a not a good idea to use an IDENTITY column on a partitioned table.

Including an IDENTITY column in a partitioned table is supported.


Access query with "last" and display the rest too I have an Access DB about measuring tools.

I have 2 tables: tblTools, which countains the details of the device, the tblCalibration contains when each device was calibrated.

tblTools: tool_ID; tool_Name
tblCalibration: calibration_ID; tool_ID ;calibration_Date

I wrote a query to get the last calibration date, but it only shows the devices which have a calibration date.  I need the results to show all devices, not just those that have been calibrated.

Is there any solution for a query that will list all of the devices plus the last calibration date if one exists?

This query selects the LAST calibration date for each tool.

SELECT t.tool_Name
    ,(
        SELECT TOP 1 c.calibration_Date
        FROM tblCalibration c
        WHERE t.tool_ID = c.tool_ID
        ORDER BY c.calibration_Date DESC
        )
FROM tblTools t

If you want to see all the tools and calibration events you have to use LEFT JOIN:

SELECT t.tool_Name
    ,c.calibration_Date
FROM tblTools t
LEFT JOIN tblCalibration c ON t.tool_ID = c.tool_ID
ORDER BY t.tool_Name, c.calibration_Date DESC --ORDER BY is optional

The first query returns all the tools and the date of last calibration of this tool. If you have 100 tools, you get 100 records, no matter how many times you calibrated each one. The second query will return all the tools and all the calibration events. It returns all calibrations for the tool instead of the last one.


SQL Server replicated databases swap I have a SQL Server 2012 database.

I would like to be able to make changes to this database or to it's replication, then make the replica, the real one.

The point here is to have 0 downtime on system updates.

What will happen if on the replica I'll change the type of a column?

Am I on the right way or there is a better way to have 0 downtime when updating my system?

Thanks.

If you make changes to the schema of the subscriber then it should keep running, but it really depends on the schema change that you make.  If you change an nvarchar(50) to varchar(10) then there's going to be data truncation problems that you have to deal with.

Also there's no way to simply switch replication from one direction to another.  Doing so would require deleting all the replication from the first server then setting up the replication from the second server back to the first server.

Depending on the changes that you need to make there should be basically zero downtime unless you are doing massive data changes or adding columns with default values or other large offline operations.


How to let particular logins to work on the secondary replica only? We are configuring an availability group with a readable secondary replica.
A login can connect to the secondary replica using "ApplicationIntent=ReadOnly" in its connection string. But we came across a problem that could make our headache in future.

If the secondary replica is unavailable due to any causes when the login establishes connection, this login will be directed to the primary replica whatever it's using "ApplicationIntent=ReadOnly" or not.

So, my question - is there any way to ban particular logins from connecting to the primary replica if they configured for working with the secondary one?

I mean if the primary replica isn't able to direct the connection to the secondary one the connection should be closed instead of going on working on the primary replica.

  Microsoft SQL Server 2016 (SP2-CU1) (KB4135048) - 13.0.5149.0 (X64)
    May 19 2018 09:41:57    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server
  2016 Standard 10.0  (Build 14393: )

You can configure this by setting the "Connections in primary role" setting when configuring read-only routing. You have 2 options:

Allow all connections (default)
Allow read/write connections - which really means, "Connections where the Application Intent connection property is set to ReadOnly are not allowed. This can help prevent customers from connecting a read-intent work load to the primary replica by mistake."

This setting isn't configured at the login level though - this relies on what you said in the first part of your question, that your logins are using ApplicationIntent=ReadOnly in their connection string.

If they don't ask for that specifically - meaning, if they don't declare that they're only going to write - then SQL Server has no fast way of knowing that they have no writeable permissions in any database, on any object. After all, they could even execute a stored procedure signed with a cert, and that cert could allow them to do writes.

So if you really want to stop them from logging in even if they don't use ApplicationIntent=ReadOnly, then I'd do it by simply disabling their logins on the primary. Then, run an Agent job every minute on every replica, checking to see if this particular replica is a primary. If a failover has occurred, and this replica is now a primary, disable the reporting-only logins. If the replica is a secondary, and the reporting-only logins are disabled, then enable them.

This will of course run into problems if you really do want to disable someone's login - it'll instantly be enabled again in a minute.


What column type can store txid_current value? AFAIK txid_current returns uint 64 value (source).
However, the biggest int column type is bigint which is signed (docs).
From what I understand, NUMERIC type is slow and should be used "for storing monetary amounts and other quantities where exactness is required" (docs).
Should I store the txid_current value as text?
I need to perform mainly greater than queries on this field:
select * from t where fxid > 438298432

What would be the most efficient (from search speed POV) for full-transaction-id (unsigned integer 64)?

I'm suspicious about your motivations, but I'd say you should either use bigint (which is the data type xid_current returns) or use xid (which is the internal data type for xid's). By way of example, see the following:
pagila=# create table xidvals as select * from txid_current();
SELECT 1
pagila=# \d xidvals
                 Table "public.xidvals"
    Column    |  Type  | Collation | Nullable | Default
--------------+--------+-----------+----------+---------
 txid_current | bigint |           |          |

pagila=# select xmin,xmax,txid_current from xidvals;
  xmin  | xmax | txid_current
--------+------+--------------
 358165 |    0 |       358165
(1 row)

pagila=# select attname, atttypid::regtype from pg_attribute where attrelid = 'xidvals'::regclass and attname in ('xmax','xmin','txid_current');
   attname    | atttypid
--------------+----------
 xmax         | xid
 xmin         | xid
 txid_current | bigint
(3 rows)


I have an error before I connect to the user Oracle 12c After that, I log in normally.

I tried it sqlplus as sysdba error is the same

Why this error occurs

I'm a beginner

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 21 13:08:51 2018

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

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: system
Enter password:
Last Successful login time: Wed Nov 21 2018 13:07:42 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Possible reasons:

You have SQLNET.AUTHENTICATION_SERVICES=(NONE) set in sqlnet.ora which disables operating system authentication based on group membership ($ORACLE_HOME/network/admin/sqlnet.ora, or $TNS_ADMIN/sqlnet.ora, it $TNS_ADMIN is set).
your user (oracle) is not the member of the group configured to be used for operating system authentication. Groups can be found in $ORACLE_HOME/rdbms/lib/config.c.
You have the environmental variable TWO_TASK set to something which is automatically added to sqlplus connection attemts as a TNS alias. For example if TWO_TASK is set to ORCL, sqlplus / as sysdba will become sqlplus /@ORCL as sysdba in the background, where operating system authentication does not work.


OPENQUERY and server permissions when I run my stored procedure it is a complete success, even if I run it through exec.

Running these:

    sp_GetServerLogins
    GO
    exec ( 'sp_GetServerLogins ')
    GO

I get this:

Needless to say this first:

    select @@servername

    EXEC sp_serveroption @server = @@servername
                        ,@optname =  'data access'
                        ,@optvalue =  'TRUE'

Now when I run the same stored procedure, on the same server, using OPENQUERY, this is what I get:

        declare @sql nvarchar(max)
        select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) +
',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

    exec ( @sql)

If you haven't found the difference is in the Login_From.

Basically what I am trying to achieve here is to find windows_logins that belong to a windows_group login that are also present in the same server.

Inside my procedure the critical line that get the windows group for an individual login is the following:

INSERT INTO @UserList EXEC xp_logininfo @LOGIN, 'members'

The funny thing is that I have servers where these two (running the procedure through exec AND running the procedure through OPENQUERY) return the exactly same thing. That is great.

Some other servers, however, give me the results above.

What is missing?
any ideas?

cheers
Marcello

P.S.

I need to use OPENQUERY because I want to achieve something like this, on the code below:

    drop table #radhe

    CREATE TABLE #Radhe(
        [n] [bigint] NULL,
        [Login_Name] [nvarchar](128) NULL,
        [Login_From] [nvarchar](128) NULL,
        [Account_Type] [nvarchar](128) NULL
    )

        declare @sql nvarchar(max)
        select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) + ',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

        INSERT INTO #Radhe
        exec ( @sql)

        SELECT * FROM #Radhe

Basically I have my data inside a temp table, it could be in fact a table variable.

If I do without the OPEQUERY:

    drop table #radhe

    CREATE TABLE #Radhe(
        [n] [bigint] NULL,
        [Login_Name] [nvarchar](128) NULL,
        [Login_From] [nvarchar](128) NULL,
        [Account_Type] [nvarchar](128) NULL
    )

        INSERT INTO #Radhe
        exec sp_GetServerLogins

I get the error below:

Msg 3930, Level 16, State 1, Line 11
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

when I use the function fn_my_permissions, I get the exact permissions inside and outside the OPENQUERY.

                declare @sql nvarchar(max)
                select @sql = 'SELECT * FROM OPENQUERY(' +

quotename(@@servername) + ',' + '''' + 'SET FMTONLY OFF; SELECT * FROM

fn_my_permissions(NULL, ''''SERVER''''); ' + '''' + ')'
                exec ( @sql)

You asked for ideas--here's mine.

We had similar issues. On SQL Servers where the issue exists try changing the SQL Server service account to a Windows account instead of a built-in account, restart SQL Server, and retry the query.

If you're running these queries with SQL Agent, you may need to change the SQL Agent account to an actual Windows account.

My thought is that build-in accounts can't access Active Directory properly.