Copy related records from 3 tables to another 3 tables and maintain the relationship between them I have 3 tables that sore orders, order items and order item comments . I need to copy the data for one order to 3 new tables that have the same structure and maintain the relationship. the order will have a new id for the new table, order items should include this new created id for the order in its values and the comments should also have the id of the newly created order items.

Can this be done in one shot?

Probably the easiest way would be to maintain the old OrderID. You can do this even if the new table uses an IDENTITY column; you can say SET IDENTITY_INSERT dbo.NewOrders ON; and override. The next OrderID generated by the app will be roughly the highest "old" OrderID + 1 (keeping in mind that IDENTITY does not guarantee a contiguous sequence for a variety of reasons).

However if the goal is to renumber the orders (not sure why you would want to do that), the easiest way would be to temporarily add a column mapping to the old OrderID, using that to populate the related tables, then dropping it. Something like:

ALTER TABLE dbo.NewOrders ADD OldOrderID INT;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT dbo.NewOrders(OrderDate, CustomerID, etc., OldOrderID)
  SELECT OrderDate, CustomerID, etc., OrderID
  FROM dbo.OldOrders;

INSERT dbo.NewOrderItems(OrderID, ProductID, Quantity, etc.)
  SELECT n.OrderID, o.ProductID, o.Quantity, etc.
    FROM dbo.NewOrders AS n
    INNER JOIN dbo.OldOrderItems AS o
    ON n.OldOrderID = o.OrderID;

INSERT dbo.NewOrderComments(OrderID, CustomerID, etc.)
  SELECT n.OrderID, o.CustomerID, etc.
    FROM dbo.NewOrders AS n
    INNER JOIN dbo.OldOrderComments AS o
    ON n.OldOrderID = o.OrderID;

COMMIT TRANSACTION;

Once you're happy that the data is all mapped correctly:

ALTER TABLE dbo.NewOrders DROP COLUMN OldOrderID;

There isn't a way to do all of this in one statement. You may be able to do it in two with a really complicated and over-engineered MERGE statement and the OUTPUT clause, but I highly recommend against that for other reasons.


Need to move TempDB to another drive. But its files cannot be dropped or shrunk Today I had a task to move all TempDB files to a new, smaller drive (RAMdisk).
I analysed TempDB
files space usage - not all the space inside of its files was used so I decided that new drive is ok.
So I had to shrink all the 8 files to make them small enough. I tried to do it with:

USE [tempdb]
GO
DBCC SHRINKFILE (N'<logicalname>' , <size>)
GO

But it took a lot of time and eventually it didn't/almost didn't shrink files.

If I tried to empty a file :

USE [tempdb];
GO
DBCC SHRINKFILE (N'<logicalname>', EMPTYFILE);
GO

it still failed with a message:

DBCC SHRINKFILE: Page <pageNo> could not be moved because it is a work table page.

Then I found some posts about clearing the procedure cache, and the session cache:

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO

and I cleared everything, but no luck.

It turns out there are a couple of ways of quickly shrinking or emptying TempDB files and drop them.
The first way.

Change locations and sizes for all TempDB data and log files in system catalogs and restart SQL instance.

ALTER DATABASE tempdb MODIFY FILE(NAME = tempdev, FILENAME = '<file_location>', SIZE = <size>);
ALTER DATABASE tempdb MODIFY FILE(NAME = templog, FILENAME = '<file_location>', SIZE = <size>);

Restart SQL Server instance, drop old TempDB files

And one more way.

Start SQL Server with minimal configuration - using -f parameter and connect via sqlcmd
Delete unneeded files:

ALTER DATABASE tempdb REMOVE FILE [<LogicalFileName>]


MariaDB 10.2.7 doesn't accept trailing Z for DATETIME timezone when inserting, but earlier version does It works with ADDTIME, but not when inserting.

MariaDB [(none)]> SELECT ADDTIME('2016-10-12T23:00:00Z', '1');
+--------------------------------------+
| ADDTIME('2016-10-12T23:00:00Z', '1') |
+--------------------------------------+
| 2016-10-12 23:00:01                  |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [(none)]> INSERT INTO db.table (start) VALUES ('2016-10-12T23:00:00Z');
ERROR 1292 (22007): Incorrect datetime value: '2016-10-12T23:00:00Z' for column 'start' at row 1

MariaDB [(none)]> INSERT INTO db.table (start) VALUES ('2016-10-12T23:00:00');
Query OK, 1 row affected (0.00 sec)

Inserting with a trailing Z does work in another db that is 10.1.19.  What has changed?  It's valid ISO8601 as far as I know.

MariaDB does not recognize your date format. In previous versions it autocorrefted value for you, but starting from version 10.2.4 MariaDB changed SQL mode to stricter and raises error instead of warning for faulty SQL queries.
If it's production, then temporary solution would be changing sql_mode to be not so strict. Change file /etc/my.cnf (on CentOS)

[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

And restart mariadb service. I suggest you changing your query for MariaDB to fully understand it.
More info on sql_mode here: https://mariadb.com/kb/en/mariadb/sql-mode/


How do I validate that my purge hasn't deleted the wrong data? I've just inherited a 17 year old database which has 481 tables, terrible referential integrity and upwards of 800GB of data at some customer sites.

We have a process which goes through and identifies all parents older than x days, then identifies all the children, grandchildren, great-grandchildren etc. of those parents and deletes from the lowest level back up to the parent. We have a QA process for determining that we are deleting the right quantity of data (using table row counts), but when pressed by the CTO, I couldn't commit to saying that we are sure we are deleting the right data

The purge scripts comprise of over 15,000 lines of SQL spread across 50+ SPROCs, so this isn't something that can be solved by a code review or unit testing.

I'm looking for any advice on processes/practices to utilise as part of our QA to validate that we have not deleted the wrong data. The complexity of our application prevents the answer from being as part of application QA and given there is millions of dollars riding on this data, the margin for error is immeasurably small

Declaring foreign keys would be a great start, but I think you know that. While they will ensure what is in the database is consistent they will not ensure it is correct. In other words they can't stop you deleting something that should be kept. For that you need to program the "should be kept" rule somewhere. You've put the "should be removed" rule in stored procedures. I think putting the "should be kept" rule in triggers will give an additional level of assurance.

In past purges we have implemented extensive reconciliation scripts. Counting the rows is a good start. Summing values by multiple combinations of keys is better. SQL Server's GROUPING SET syntax makes this not much less efficient than a simple count. Perform these aggregates before the purge with only the retained rows in the WHERE clause. Preform them again afterwards with no WHERE clause. If too many or too few rows were removed the before and after numbers will be different. Because you summed by lots of different keys it will be easier to identify the offending source rows.

Given we accept an error can occur, it is possible that error will delete some number of rows it shouldn't and leave the same number of rows it should. Unlikely, but possible. By summing the error has to remove / leave exactly balancing rows. This is even less likely. By summing over multiple keys the chances are further reduced. This can be continued until an arbitrary level of assurance is achieved.

I would not suggest deploying this against production, at least not initially. A copy will suffice to perform the cross-checks, and to validate the validation code. If you can confirm here that the deleting procs are working as intended you have confidence to deploy them in production without the extra checks.


Sort operator positioning in an execution plan I am troubleshooting a performance issue and trying to get a stable execution plan for a reporting query. Links to the actual plans are below:

Good vs Bad

If you look at the Good plan, the sort is after the table joins. For the bad plan, the sort is just before the last join. In the case of bad plan since the sort is before the last join, and since the sort's estimated number of rows is 5000, SQL Server has decided to go with a nested loop join, which is slowing the query.

I do not want to use table join hint or change the variable on the TOP.

What are the strategies I can use in this scenario to get a consistent plan?

This query runs approximately every 5 minutes.

It looks like you have a classic parameter sniffing issue. As far as I can tell your queries are identical but both of them use different cached plans. The cached plan that you get depends on the input parameters for the first run. I assume that there's a plan that works well enough for all inputs and you want that plan. However, depending on parameter values you might get unlucky and end up with a plan that works better for some inputs but poorly for others.

If possible I would add a RECOMPILE hint to the query. This will prevent the query optimizer from using a cached plan for the query but it will see the values of the variables when making the plan. As a general rule of thumb you'll get better performance if you can give more information to the optimizer. The downside of this approach is that the query plan will be recompiled each time, but this is only a three table query. I wouldn't worry about it unless the query runs hundreds of times per second.

Another option is to use the OPTIMIZE FOR query hint. This will give you a cached plan that isn't based on input values, so the key is getting a query plan that works well enough for all possible input values. In your other question you implied that you weren't satisfied with the default guess of 100 rows for TOP so you can force the query optimizer to optimize for a specific TOP value like follows:

OPTION (OPTIMIZE FOR UNKNOWN, OPTIMIZE FOR (@top = 5000));

None of the input variables will be sniffed to form the plan. That syntax may look odd but it's allowed:

  If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.

If neither one of those options works and you're confident that you have a query plan which works well you could look into using plan guides.


SSRS 2008 - How to Total values from an expression I'm trying to figure out how to total values from a calculated field.

In the example below, I have the following expression:

=IIF(Fields!Role.Value = "Contractor", Fields!actualwork.Value * 100,
Fields!actualwork.Value * 77)

Now I want to provide a total, but it doesn't show up on the report. Is there something I'm missing?

You actually just put a SUM around the outside:

=Sum(IIF(Fields!Role.Value = "Contractor", Fields!actualwork.Value * 100,
Fields!actualwork.Value * 77))

It will evaluate that row by row and will provide the correct sum.


How to create script file with PL/SQL? I have to update several objects in many instances. In order to achieve that I am using DBMS_METADATA to create the DDL statements.

So far I have the DDL statements and I save them in a TABLE.
I would like to save said DDL statements in a .sql file for each object.

I know I can do this with C#, but so far I have been able to achieve everything I needed to do with PL/SQL. Is there a way to create the .sql files with PL/SQL?

That's relatively easy. Using the UTL_FILE package in Oracle you can create a file in the servers filesystem and write the output of any PL/SQL statement to it. You can actually also use it to read data from a file.
http://psoug.org/reference/utl_file.html

You might also want to read up a little on the Directory object type as well, since that's a prerequisite for using the UTL_FILE package; http://psoug.org/reference/directories.html

--
Here's an example of a procedure that writes ascii data to an existing Oracle directory object. The user running the code needs to have read/write access to the Oracle Directory object.

CREATE PROCEDURE put_local_ascii_data (p_data  IN  CLOB,
                                    p_dir   IN  VARCHAR2,
                                    p_file  IN  VARCHAR2) IS
    -- --------------------------------------------------------------------------
      l_out_file  UTL_FILE.file_type;
      l_buffer    VARCHAR2(32767);
      l_amount    BINARY_INTEGER := 32767;
      l_pos       INTEGER := 1;
      l_clob_len  INTEGER;
    BEGIN
      l_clob_len := DBMS_LOB.getlength(p_data);

      l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

      WHILE l_pos <= l_clob_len LOOP
        DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer);
        IF g_convert_crlf THEN
          l_buffer := REPLACE(l_buffer, CHR(13), NULL);
        END IF;

        UTL_FILE.put(l_out_file, l_buffer);
        UTL_FILE.fflush(l_out_file);
        l_pos := l_pos + l_amount;
      END LOOP;

      UTL_FILE.fclose(l_out_file);
    EXCEPTION
      WHEN OTHERS THEN
        IF UTL_FILE.is_open(l_out_file) THEN
          UTL_FILE.fclose(l_out_file);
        END IF;
        RAISE;
    END;

The p_data parameter is a CLOB that would ( in this case ) contain all the ascii data being written to the p_dir directory and the p_file name ( which gets created at runtime ).
You might need to do some clob conversions on your data, depending on the data types in your query results that you'd use to populate the clob object.


Replication on Always On I have two SQL Servers 2014 Enterprise Edition (SERVER A, and SERVER B) designed for Always On Availability Group. There are 5 databases in the AG.

I have been told that we need to replicate one of the databases (Database A) from the AOAG. I set up a transactional replication for database A, pointing to listeners which shouldn't cause any issue if a failover happened. Tested, worked just fine.

I'm thinking to create two distribution databases, each on a different server.  The reason is, I'm trying to be redundant. If Distribution Server A is gone for any reason, I should be able to replicate database A from Distribution Server B. Is it possible, any alternative to this?

Here is what my design is for Replication on AOAG:

Distribution Server A (contains Distribution database) - SQL Server 2014
Two subscribers - SV1 and SV2  - SQL Server 2008R2 Standard
Publishers  (SERVER A and B - from AOAG) - SQL Server 2014

What I'm trying to do: Add one more distribution server (B).

Distribution Server A (contains Distribution database)
Distribution Server B (contains Distribution database1) - SQL Server 2014
Two subscribers - SV1 and sV2  - SQL Server 2008R2 Standard
Publishers (SERVER A and B - from AOAG)

What I see as a challenge:

Can't configure remote Distribution server for possible publishers. Help/ideas are welcome!

You can't have two Distribution databases. Put the distribution database in a Failover Cluster Instance that way it is highly available.

Maybe one day MS will allow the distribution database to be in an AG. I hear you can have an FCI on a cluster that also has AG (having both on the same cluster but completely distinct instances - so two standalone instances with an AG and one FCI), though I've never done it, and probably wouldn't recommend it.

Clustering is how you make the distribution database highly available. So if High Availability is important for your organization and you need replication, then you need to learn Failover Clustering and setup an FCI.


Recreating Database from IBD files (no FRM) I am running OS X with mySQL 8.0.22 running natively. A few weeks ago had to reinstall mySQL as I could not get it to start. I made a backup of all the folders under '/usr/local/mysql/data'.
When mySQL was installed again it had wiped all the databases.
The data inside these DB is in no way critical, but I remembered today I was working in a WordPress site with a page builder, hence all info is stored in the DB tables.
I would like to find a way to restore this database, but every article I have read mentions "ibd and frm" files.
There is not a single frm file from the backup I made.
Is there any way of recreating this DB at all or is it a lost cause?
Thanks!

As suggested by @AMtwo, .frm files contain the schema definition. Since these are WordPress tables, getting the schema was very easy as I had lots of installs to get this info from.
I then followed the guide at https://www.chriscalender.com/recovering-an-innodb-table-from-only-an-ibd-file/ and it worked just fine:

Create table based on the schema of original table.
Running ALTER TABLE product DISCARD TABLESPACE;
Copying the original IBD file
Running ALTER TABLE product IMPORT TABLESPACE;

The tutorial mentions further instructions but at this stage it simply worked for me. I did the same for all other tables, exported as SQL dump and imported successfully.
I would recommend using Docker + mySQL if you are going to be trying this out.
Thanks again @AMtwo, you pointed me in the right direction!


Consume Rest Api with SSIS I try to consume a Rest API (JSON export) with SSIS. Since it seems that a REST source is not supported by SSIS I was looking for a solution but could only find the SSIS JSON Source.

Is there a non proprietary solution or is this feature even supported by the new SQL Server 2016?
I checked that as well could not find anything specific though.

You could use a script component which is supported in 2016 and previous versions.

Make sure that when you add the component you select "source" as the type and not destination or transformation.

Then you add a reference to System.Web.Extensions.dll to the task and write some code to consume the webservice.

This blog post looks like it documents the process nicely, but the key is that you consume the json and override the CreateNewOutputRows method to send whatever you are receiving to the task's output. The output can be connected to any SSIS component accepting a data flow input.

If you insist on using a source component there is JSONSource on codeplex but they don't support SQL 2016 yet, but I don't think it's necessary to add a JSON component if you are only consuming a single service and it's not really complicated to implement yourself.


Convert SQL Server results to a text file that PowerShell can access and use The query below is used to create a text file with a list of server names.

The problem I have is that the text file gets created like

Server, Server, Server etc.

but I need it as follows

Server
Server
Server

The query:

BEGIN
    CREATE TABLE [TempPSWmiObjectQuery]
                (QueryText nvarchar(max))
END

INSERT INTO [TempPSWmiObjectQuery]

SELECT STUFF((SELECT char(13)+char(10),
             SUBSTRING(ServerName, 1,
             CASE WHEN CHARINDEX(',', ServerName) > 0
                  THEN CHARINDEX(',', ServerName)-1
                  ELSE LEN(ServerName)
             END)
FROM MasterList
FOR XML PATH('')), 1, 1, '')

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE

EXEC xp_cmdshell 'bcp SQLEnv.dbo.TempPSWmiObjectQuery out "\\ServerName\Data\temp\testlist.txt" -c -T -S "ServerName\Instance"'

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE

DROP TABLE TempPSWmiObjectQuery

The PowerShell Script:

        $StagingServer = "ServerName\InstanceName"
        $StagingServerDatabase = "SQLEnv"

    foreach($line in
    [System.IO.File]::ReadLines("\\ServerName\Data\temp\testlist.txt"))
    {
        $line
        Get-WmiObject win32_Service -Computer $line  | where {$_.DisplayName
    -match "SQL Server"} | select SystemName, DisplayName, Name, State,
    Status, StartMode, StartName | ConvertTo-DbaDataTable | Write-
    DbaDataTable -SqlInstance $StagingServer -Database
    $StagingServerDatabase -Table _SQLServicesInfo -AutoCreateTable -Confirm

    }

This answer assumes multiple comma's are in a single row of the ServerName column, and these need to be removed & Line feed + Carriage returns added instead of each comma.

There could be better ways to get your endresult, but to write to a text file without the comma's, you could use the REPLACE() function.

CHARINDEX() will only give you the location of the first value found.

CREATE TABLE [TempPSWmiObjectQuery](QueryText nvarchar(max));
INSERT INTO [TempPSWmiObjectQuery]
SELECT  REPLACE(ServerName,',',+ ' '+char(13)+char(10) )  -- Replace comma with space, line feed and carriage return.
FROM MasterList;

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

EXEC xp_cmdshell 'bcp SQLEnv.dbo.TempPSWmiObjectQuery out "\\ServerName\Data\temp\testlist.txt" -c -T -S "ServerName\Instance"';

EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

DROP TABLE TempPSWmiObjectQuery;

Test

INSERT INTO MasterList(ServerName)
VALUES('Server1,Server2,Server3')

Result

Change SELECT REPLACE(ServerName,',',+ ' '+char(13)+char(10) ) to
SELECT REPLACE(ServerName,',',+ ' ' ) if you don't want new lines.


Schema for lists of relationship differences Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item <- InheritedItem <-> *AddedProperties, *RemovedProperties, *ChangedProperties. How would such a model be best represented in a relational database?

Having thought about possible schemas, I've come up for two general options for storing the list of changes:

Two or three different tables for added, removed, changed properties.
A single table that has a change type flag and all the necessary columns for all three change types.

Regarding change tracking, there are again two options I've come up with so far:

Storing changed properties as two foreign keys, one to the old property, one to the new property.
Giving the Property table a nullable foreign key to the original property and recording the change as as an added and a removed property.

Clarification: This question relates to additions & deletions in the sense of version control (i.e. an object of this kind was replaced with a new object that has these changes relative to the parent object), not in the sense of object inheritance.

If I understand your question correctly you are looking to store, let's say, a Person entity with Name and DateOfBirth attributes.  You will also have an Employee entity which is defined as everything a Person has plus EmploymentNumber plus DeskNumber but doesn't have DateOfBirth.  I'm guessing you want to be able to add a column to a base type and automatically see it showing up in the sub-type(s), and sub-sub-type(s).  In short you are looking for an object-oriented data store.  RDBMSs in general are not will suited to deliver this featrure.

If you know what these differences are and they are stable, at least between schema relases, you can define a type/ sub-type model.  The base table has the columns which are common to all types.  It has a surrogate primary key.  From there you define further tables, strictly adding columns as you go.  The same ID is carried through this hierarchy.  The case where you need to remove columns from one type to a descendant is handled by defining an abstract common ancestor and placing the removed column in one branch but not the other.

For my example above we would end up with these tables

Human
    ID     PrimaryKey
    Name

Person
    ID     Primary key and also foreign key to Human.ID
    DateOfBirth

Employee
    ID     Primary key and also foreign key to Human.ID
    EmploymentNumber
    DeskNumber

You could add an column to the base table (Human) to indicate which of the sub-types any particular occurance writes to.  I don't think that is necessary because the process which writes to this database has to know which type it is dealing with in order to capture the correct values.  Even if that process dynamically builds its list of attributes by examining the DB, it has to know which ultimate sub-type it is looking for to bootstrap the process.

It is tempting to have, say, EmployeeID and a separate foreign key HumanID.  This is unnecessary.

When retrieving values you will either be interested in a known occurrance of a sub-type or will be looking for all available values for an instance i.e. "Human 99 as Employee" or "All about occurrance 99".  The former can be had by INNER JOINing Employee up to its ultimate ancestor using the defined keys.  The latter by OUTER JOINing all tables in the model.

If your attributes can vary at run time you will be forced into using some variant of an entity-attribute-value (EAV) model.  This has been well documented in many, many blogs, papers, forums and SO questions.  While being fractious, conceptually challenging and non-performant these models can be made to work (for an appropriate definiton of "work").  Your tables will be a lot like this:

ItemType
    ItemTypeID    primary key
    Name
    ParentTypeID  fk to ItemTypeID

Attribute
    AttributeID   primarykey
    Name
    AddedOrDeletedInd
    ItemTypeID    fk to ItemTypeID

Item
    ItemID
    ItemTypeID    fk to ItemTypeID
    ParentItemID  fk to ItemID

ItemValue
    AttributeID   fk to Attribute
    ItemID        fk to Item
    Value

ItemValue.Value could be a catch-all varchar() column or you could have one for each datatype you want i.e. ValueInt, ValueDate, ValueChar etc. and and indicator in Attribute to say which is populated.  As you can see reading any one item's full definition will require recursion through a tree.  Good luck.


For multiple mysql instance , fresh download required when one exist? i need multiple instances of Mysql on same server.
I had been through many links , but i confused here :

What i have : MySQL 5.6 is already installed on my windows server A.
Installed at : "C:\Program Files (x86)\MySQL\MySQL_Server_5.6\"

Problem :   For multiple instances , do i need to download and install new mysql(either same or higher version) on server A ?

OR
  should i just copy existing my.conf file in new folder and then change its port number and then done.

My own analysis goes on fresh installation , but please confirm .

What i did is :

       i used existing my.cnf file and copy it in another location and edit
       some part of it :

         1. Commented #port=3306
         2. Added these lines at the end.
           [mysqld1]
           basedir ="C:\Program Files (x86)\MySQL\MySQL Server 5.6"
           port = 3307
           enable-named-pipe
           socket = mypipe1

           [mysqld2]
           basedir ="C:\Program Files (x86)\MySQL\MySQL Server 5.6"
           port = 3308
           enable-named-pipe
           socket = mypipe2


PROCEDURE SELECT IN ORACLE I'm new to Oracle--can somebody help me with this procedure?

CREATE OR REPLACE PROCEDURE JAKARTA
as
BEGIN
SELECT *
FROM
karyawan
WHERE
alamat = 'Jakarta';
COMMIT;
END jakarta;
/

When I run this I get the following

  Warning : Procedure created with complication errors.

Please help...

If you are going to select some data inside a procedure then it needs to go into something.  You have not shown us the complete error message but I suspect it is

PLS-00428: an INTO clause is expected in this SELECT statement

Revise your code to provide a target of the same type as the data selected. I removed the commit as is not necessary unless you UPDATE/INSERT/DELETE something after changing it. Something like

    CREATE OR REPLACE PROCEDURE JAKARTA
    as
    --declare a type with all the same columns as the karyawan table
        TYPE karyawanType IS RECORD (
            id karyawan.id%type,
            alamat karyawan.alamat%type,
        );
    --declare a record of the type you created
    v_record karyawanType;

    BEGIN
    SELECT *
    INTO v_record
    FROM
    karyawan
    WHERE
    alamat = 'Jakarta';

    END jakarta;
    /


What is the best way to manage interdependent indexes So I have a table of items as so

|firstIndex|subIndex|relatedIndexes|
-----------------------------------
|     1    |   1    |  2 ,6 , 8    |
-----------------------------------
|     1    |   2    |  2 ,6 , 8    |
-----------------------------------
|     1    |   3    |  2 ,6 , 8    |
-----------------------------------
|     2    |   1    |  1, 10 , 12  |
-----------------------------------
|     3    |   1    |  20, 50, 77  |
-----------------------------------
|     4    |   1    |  ...         |
-----------------------------------

So both firstIndex an subIndex are the primary keys (they cannot be two times the same combination, hope I'm clear :) ), subIndex is the index representing the number of the variation of firstIndex and finally relatedIndexes is the array of  firstIndex related to other firstIndex. As you can see in the sample firstIndex 1 is related to firstIndex 2 and 2 to 1

My question is how can I represent, store and manage this model the easiest and quickiest way in MYSQL?

It can looks like

CREATE TABLE indices (firstIndex INT NOT NULL,
                      subIndex INT NOT NULL,
                      PRIMARY KEY (firstIndex, subIndex)
                     );

CREATE TABLE refs (baseIndex INT NOT NULL,
                   relatedIndex INT NOT NULL,
                   PRIMARY KEY (baseIndex, relatedIndex),
                   FOREIGN KEY fk_bi (baseIndex) REFERENCES indices (firstIndex),
                   FOREIGN KEY fk_ri (relatedIndex) REFERENCES indices (firstIndex)
                  );

Update

  why there's a baseIndex + relatedIndex

It is a "link" table (instead of non-relational CSV-field relatedIndexes). The (2,6,8) value for firstIndex=1 in a source scheme is transferred to 3 separate records in refs with (1,2),(1,6),(1,8) value pairs.

  what is Foreign Key

It is constraint references which do not allow to insert into refs an index value which not exists in indices table. And prevents deletion a record from indices while at least one reference exists in refs.


How to get next/previous partition count using window function? I'm trying to get the current_count, next_count, and previous_count for date.month partitions with window functions:

WITH tmp1 AS (
SELECT location.city, date.month, date.year
     , COUNT(*) OVER W AS current_count, COUNT(*) OVER W2 AS
count_next
FROM fact, crime, date, location
WHERE fact.location_key = location.location_key and crime.crime_key =
fact.crime_key and fact.date_key = date.date_key

WINDOW W AS (
        PARTITION BY location.city, date.year, date.month
        ORDER BY date.month
    ),

    W2 AS (
        PARTITION BY location.city, date.year, date.month
        ORDER BY date.month
        RANGE BETWEEN CURRENT ROW AND FOLLOWING
    )
)

SELECT *
FROM tmp1
GROUP BY city, year, month,current_count, count_next

However, the code above works for current_count only, the count_next shows the same result as the current_count.

How to show the count for next month and previous month? With window functions?

Do the aggregation first. Then use the lead() and lag() window functions. Looks like you were aiming for something like this:

SELECT *
     , lead(curr_count) OVER w AS next_count
     , lag(curr_count)  OVER w AS prev_count
FROM (
   SELECT l.city, d.year, d.month, count(*) AS curr_count
   FROM   fact     f
   JOIN   crime    c ON c.crime_key = f.crime_key    -- see below
   JOIN   date     d ON d.date_key = f.date_key
   JOIN   location l ON l.location_key = f.location_key
   GROUP  BY l.city, d.year, d.month
   ) sub
WINDOW w AS (PARTITION BY city ORDER BY year, month)
ORDER  BY city, year, month;

You can use a CTE or a subquery (like I did) to apply the logic in two steps. May be easier to understand at first. But you can add window functions over aggregate functions in the same query level. See:

Best way to get result count before LIMIT was applied

So this is equivalent and shorter (probably same query plan / performance):

SELECT l.city, d.year, d.month
     , count(*)              AS curr_count
     , lead(count(*)) OVER w AS next_count
     , lag(count(*))  OVER w AS prev_count
FROM   fact     f
JOIN   crime    c USING (crime_key)    -- see below
JOIN   date     d USING (date_key)
JOIN   location l USING (location_key)
GROUP  BY l.city, d.year, d.month
WINDOW w AS (PARTITION BY l.city ORDER BY d.year, d.month);

Use equivalent explicit join syntax. Much easier to read.

The USING shorthand in the join clause of my second query is only legit if column names are unambiguous. Else stick to the explicit ON syntax in the first query.

Why join to table crime? Does the join eliminate or multiply rows? Else you can remove it completely.

Use table aliases to keep the noise low.

You had PARTITION BY location.city, date.year, date.month ORDER BY date.month. But I suggest you really want PARTITION BY city ORDER BY year, month to make the query work across year boundaries?

It would probably be more efficient to use a single column of type date (4 bytes) instead of year and month. (But don't name it just "date".)

Note how I skipped ORDER BY city, year, month in the second query. You normally get rows sorted that way anyway due to the window definition, but there are no guarantees without explicit ORDER BY. And you did not specify that order is required. If so, you might want to be explicit about this and add ORDER BY.


JSON or many to many I'm building a database for a local charity. The database will be filled with entries coming from different sources and updated daily.
One of the element (e.g. a car) has a relationship with many elements (e.g. car colors).
One of my friends who is helping with the project suggested to use a JSON field. I'm not a DBA and maybe I don't fully understand the benefit of using JSON over a many to many table.
I could build the cars table as follow:

Where an entry would be:
Cars
+----+--------+-------------------------------+
| id |  name  |             colors            |
+----+--------+-------------------------------+
|  1 | Fiesta | { "15": "red", "22": "blue" } |
+----+--------+-------------------------------+

Or I could build a many to many table:

Where an entry would be:
Cars
+----+--------+
| id |  name  |
+----+--------+
|  1 | Fiesta |
+----+--------+

Colors
+----+------+
| id | name |
+----+------+
| 15 |  red |
+----+------+
| 22 | blue |
+----+------+

CarsColors
+----+-------+---------+
| id | carID | colorID |
+----+-------+---------+
|  1 |   1   |    15   |
+----+-------+---------+
|  2 |   1   |    22   |
+----+-------+---------+

Personally I find the second approach would be cleaner, especially since in future we'll need to perform operations like "find all cars with color red". I know that the answer probably is "it depends" but, please, could you point me in the right direction?

"find all cars with color red" is a relational question, and therefore a normalized relational database with the table structure you designed in your second example makes more sense than JSON. JSON makes things easier for highly variable and structureless scenarios, but when you have relationships between objects (entities) like Cars and Colors, then it's the "lazy developer solution" to use JSON instead of a relational database. And it's more work in the long run when you have to ask relational questions like your example, when the data is stored in JSON as opposed to a relational structure with normalized tables.


Recovering Mysql - how to get a dumpfile from wampserver and none running db I had the following:
- Wampserver/mysql/drupal + batch file with auto backup by use of mysqldump.
- Backup was distributed off and online.

then : my laptop was stolen :-( and I need to recover but... the size of the backup is zero bites aahhhhh. I have a good mysqldump until 20/09/2014

In need to have a mysqldump until 19/12/2014 and this is still available in database in the wampfolder. I needed to re-install wamp so I have two folders now:

d:\wamp with the new wampserver
d:\wamp_old with the old wampserver with the database until 19/12/2104.

How to get a dumpfile from the none running db?

Y

I'm a bit lost in your explanation, but there may not be much of a problem.

You have a dump from December?
But it is in the 'wrong' directory?
The dump was taken using mysqldump?

Reloading is nothing more than
mysql -uroot -p


Optimizing MariaDB for 5-10K small databases Running a SaaS based on MariaDB LTS (1:10.1.44-0ubuntu0.18.04.1) with nearly 5000 databases (1 per tenant). Average per tenant: 8MB of data (on disk) with ~50 tables. CPU/Mem load is negligible. It's been running fine for a couple years, with some 5-20 tenants added per day.

However. When the nr of dbs grows to about 5150, the MariaDB server process crashes. So far I haven't been able to distill a useful log message, unfortunately. Furthermore, it takes about 10 minutes for MariaDB to start up, which is quite long when I want to experiment with new settings.

What settings would you recommend to reduce the risk of crashing, and speed up starting time?

Some more characteristics:

SHOW GLOBAL STATUS
SHOW VARIABLES
Server has 2CPU and 8GB ram, of which 25% is available during peak times.
Load per CPU core never exceeds 0.20 during peak times
Connections are short lived, I expect that there are never more than 20 concurrent db connections.
All tables are InnoDB
Schema may differ per database (but are probably identical for 98% of tenants)
I use stock configuration, except for:

innodb_file_per_table
open_files_limit        = 65536
log_warnings            = 3
innodb_buffer_pool_size = 1G

FWIW, I'm willing to upgrade to a newer version of MariaDB, but given the risk of new uncertainties, I'd rather make an informed decision than take a leap of faith.

Thanks for any advice!

Your output for GLOBAL_STATUS is showing:

opened_tables 2098690

The definition for this status is defined as:

  Number of tables the server has opened...

Reference: Opened_tables (MariaDB documentation)

Your configuration for innodb_file_per_table is set to:

innodb_file_per_table   ON

And MariaDB defines this setting to be:

  If set to ON, then new InnoDB tables are created with their own InnoDB file-per-table tablespaces. If set to OFF, then new tables are created in the InnoDB system tablespace instead. Page compression is only available with file-per-table tablespaces. Note that this value is also used when a table is re-created with an ALTER TABLE which requires a table copy.

Reference: innodb_file_per_table (MariaDB documentation)

So basically, you have 2'098'690 open files on your Ubuntu Server, but your settings for innodb_open_files is set to 2000:

innodb_open_files   2000

Now there could be an issue that Ubunutu is hitting a limit or your MariaDB is in some race condition opening and closing the database/table files.

The 2 million + open files is a counter and there is a recommendation that if your counter keeps on increasing that:

  If the number of opened tables is increasing rapidly, you should look at increasing the table_open_cache value. Try to find a value that sees a slow, or possibly even no, increase in the number of opened tables.

Reference: Optimizing table_open_cache (MariaDB documentation)

Additionally you might want to have a look at your Ubunutu instance and see what limits are set for system-wide resources:

 bash#> ulimit -a

This will return a list of currently set limits. You might have to increase these limits to achieve a stable MariaDB environment. The one setting being:

-n   The maximum number of open file descriptors.

Reference: ulimit (SS64.com)

You can set this setting according to this Q&A:

How to increase max open files limit on Ubuntu 18.04? (Ask Ubuntu)


Microsoft SQL Server creating an Inline function I am completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart datetime, and ClassStartDateEnd datetime. Where the output should be the student's GPA for all classes that were taken between ClassStartDateStart and ClassStartDateEnd. Also, supply the script to call this new function, passing it parameter values of your choice. I tried creating a code but don't know where to start. Here is what I have so far:
USE [Master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION GPAofStudents
  (StudentID int,
   ClassStartDateStart datetime,
   ClassStartDateEnd datetime)
  RETURNS decimal(3,2)
  AS
BEGIN
DECLARE AvgGPA decimal(5,2);
SET AvgGPA = (SELECT AVG(Class_GPA)
FROM Students_Classes
  WHERE Student_ID = StudentID
    AND Start_Date > ClassStartDateStart
    AND Start_Date <= ClassStartDateEnd
    AND Class_GPA > 0;
    RETURN AvgGPA;
END;

My table for the students is this:
CREATE TABLE [dbo].[Students_Classes](
    [Student_Class_ID] [int] IDENTITY(1,1) NOT NULL,
    [Student_ID] [int] NOT NULL,
    [Class_ID] [int] NOT NULL,
    [Start_Date] [date] NOT NULL,
    [Assignment1] [int] NULL,
    [Assignment2] [int] NULL,
    [Assignment3] [int] NULL,
    [Assignment4] [int] NULL,
    [Class_GPA] [int] NULL,
     CONSTRAINT [Student_Class_ID] PRIMARY KEY CLUSTERED
(
USE [A_University_Database]
INSERT INTO Students_Classes
([Student_ID],[Class_ID],[Start_Date],[Assignment1],[Assignment2],[Assignment3],
 [Assignment4],[Class_GPA])
    VALUES
('1', '5', '2010-05-30', '86', '92', '69', '99', NULL),
('1', '6', '2010-05-30', '86', '92', '69', '99', NULL),
('2', '2', '2010-05-30', '99', '85', '91', '79', NULL),
('2', '7', '2010-05-30', '99', '85', '91', '79', NULL),
('3', '3', '2010-10-01', '67', '91', '71', '100', NULL),
('3', '4', '2010-10-01', '67', '91', '71', '100', NULL),
('4', '5', '2009-02-21', '56', '93', '72', '86', NULL),
('4', '6', '2009-02-21', '56', '93', '72', '86', NULL)

What I have to do is create an aggregate inline function that displays the students class GPA. Do I need to create a stored procedure first that way the inputs are recognized?

Do I need to create a stored procedure first that way the inputs are
  recognized?

You are missing @ symbols at the beginning of each parameter.

Find out more about writing functions here

Once you know the basics this article by Jeremiah Peschka explains the benefits of inline functions and much more. Basically if your table valued function is not inline then it will execute once for every row returned by the calling query. This can be very inefficient. An inline table valued function can be referred to as a parameterised view.

Your function is a scalar function which will always execute once for each row but it could be converted to an inline table valued function with the code below.

CREATE FUNCTION dbo.GPAofStudents
  (@StudentID int,
   @ClassStartDateStart date,
   @ClassStartDateEnd date)

RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
    SELECT  @StudentID AS Student_ID,
            AVG(Class_GPA) AS GPA
    FROM    dbo.Students_Classes
    WHERE   Student_ID = @StudentID
            AND Start_Date > @ClassStartDateStart
            AND Start_Date <= @ClassStartDateEnd
            AND Class_GPA > 0;

Using the WITH SCHEMABINDING option will stop anyone from editing the schema used by your function and breaking it.


mysql 8 ndbcluster distributed privileges I've setup a mysql 8 ndbcluster but cannot get the distributed privileges working.

following how to setup distributed privileges using NDB_STORED_USER (https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-privilege-distribution.html), i've verified the following:

SHOW ENGINES lists the value of "DEFAULT" for the Support column on the ndbcluster (Engine) row.
Both SQL nodes have "default-storage-engine=ndbcluster" in their my.cnf
I created a user on SQL node 1 and then added them to NDB_STORED_USER.

mysql> GRANT NDB_STORED_USER ON *.* TO 'admin'@'%';
Query OK, 0 rows affected (0.26 sec)

I verified I could see the respective GRANTS in ndb_sql_metadata from BOTH SQL Nodes

type    name    seq note    sql_ddl_text
11  "'admin'@'%'"   0   2   "CREATE USER 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$MGBmokOj
                                m!<=P9q7BhmH0ANKg1Fs3JGEv.ITwLdXa7KC0n2GOogn6Xc/' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT"
12  "'admin'@'%'"   1   [NULL]  "GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,NDB_STORED_USER,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`%`"
12  "'admin'@'%'"   0   [NULL]  "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%`"
3 rows returned```

I've restarted SQL Node 2, but I still do not see the new user (admin)

    mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)```

Any other suggestions? this is my first time setting up a mysql cluster.

I had same issue on 8.0.21 NDB Cluster. I was looking at the error log and trying to figure out what is wrong, and I saw this
NDB: Query 'CREATE USER IF NOT EXISTS 'admin'@'localhost'' failed, error: 1819: Your password does not satisfy the current policy requirements
NDB: Query 'REVOKE ALL ON *.* FROM 'admin'@'localhost'' failed, error: 1269: Can't revoke all privileges for one or more of the requested users
NDB: Query 'ALTER USER 'admin'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0  MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0' failed, error: 1819: Your password does not satisfy the current policy requirements
NDB: Query 'ALTER USER 'admin'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$Dzd+M c5Cnthnjcc^hEvdg6M.d3j9TWVcO6ze/6DBrNnW3SB/Qh7wn19XGI7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT' failed, error: 1396: Operation ALTER USER failed for 'admin'@'localhost'

When I first create user on SQL Node 1 and grant NDB_STORED_USER to new user the second node has this log. So basically when new user created on one of the node the other nodes are trying to create it with different query. As you can see the log the second node tries to create admin@localhost user and then it tries to set password using ALTER USER. And due to validate_password policy plugin the second node cannot create user without password.
So I have disabled the validate_password plugin and created user again. After disabling the validate_password plugin the user created without any error. And the password privileges have been set on second node.


How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id? I'm working on PostgreSQL and I have a table like this called words_table:

I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent id_word.
In other words, I want all the words softwaredesign, software_design, software.design... and so on having the same number on its map column (in this specific case, a number from 1 to 10). The same applies to civil_engineering and IndustrialDesign.

I know this involves some regular expressions and case insensitive comparisons but i'm stuck at the SQL logic.
I know these expressions could be useful:

regexp_replace(word, '(\.|:|,|&|-)','','g')

To handle the separators

lower(something)

To handle the uppercase lowercase matching or

UPDATE words_table SET a.map = b.id_word WHERE word ILIKE something

... WHERE word ~* something

for case insensitive matching.

Should I create new columns with regexp_replaced words and do the mapping after that with some joins?
Or maybe something with CamelCase and underscore matching? Maybe functions?
Which is the optimal solution?

Database design

Either you already have the look-up table and forgot to mention it, or you should create one.

CREATE TABLE map (
  map_id int PRIMARY KEY
 ,map    text UNIQUE NOT NULL
);

-- And that's how I would shape your original table:
CREATE TABLE word (
  word_id serial PRIMARY KEY
 ,map_id  int REFERENCES map(map_id)
 ,word    text
);

Here and here is why I normally prefer text over varchar(n).
Here is why I put the two integer columns first in table word.

INSERT INTO map(map_id, map) VALUES
  (1, softwaredesign)
 ,(2, civilengineering)
 ,(3, industrialdesign)
 , ... ;

Map data

Define a function like @Daniel already suggested.

Depending on your exact requirements, I would use pre-defined character classes where possible in a regular expression instead of rolling my own. Postgres uses locale information from your OS to identify characters, digits etc.

[:alnum:] includes all numbers and digits, depending on your locale. Unlike [a-zA-Z0-9] this also identifies ä or é as characters - depending on your locale.

[^[:alnum:]] is the negation, i.e. all other characters.
There is also the similar class shorthand \W, but that would include the underscore _.

CREATE FUNCTION map_word(text) RETURNS text AS
$$SELECT lower(regexp_replace($1, '[^[:alnum:]]', '', 'g'))$$
LANGUAGE sql IMMUTABLE;

This update would be most efficient then:

UPDATE word w
SET    map_id = m.map_id
FROM   map m
WHERE  m.map = map_word(w.word)
AND    w.map_id IS DISTINCT FROM m.map_id -- avoid empty updates


Visually create a join using PHPMyAdmin I recently started using SQL Server 2008 express and it has a really nice joins interface, whereby you can visually create a join.  Is this possible on PHPMyAdmin?

as far as I could find no option in phpmyadmin, but I found http://www.mysql.com/products/workbench/  which seems very useful for visual database design.


List of open (uncommitted transactions) in DB2 LUW How can I get list of open (uncommitted transactions) in DB2 LUW?

Or just the oldest transaction id?

Basically, I need to get the first change of the oldest open transaction. I found APPL_ID_OLDEST_XACT in SYSIBMADM.SNAPDB view, but it tells me only the application id and I could figure out how to get to the actual LSN/LRI.

I was suggested to use db2pd -transactions, but I can only run it from server side.
Is there a solution that I can run from client side?

Assuming you have a newer version of DB2 (10.1 or higher, although I want to say a few of these are in 9.7). You have the Monitor views and functions.

The MON_CURRENT_SQL view and the MON_GET_ACTIVITY table function look like they could possibly be what you are after.


Oracle 11g migration to AWS I have an on premise oracle 11g database that i have to migrate to an EC2 instance hosting Oracle 11g in AWS. I have three options to achieve this

Use Oracle Data guard to replicate the on premise database to cloud database
Using RMAN to backup the existing database and restore it on Cloud
Using expdp and impdp commands to export and import the database

My database size is approximately 2 TB.
I have following doubts
Q1: What is the best way among the three options?
Q2: Using data guard means that the AWS and the on premise database should be connected the entire time until the data is replicated on cloud, what if there is a network disconnect in between the process? Do i have to restart the replication process or will it continue from where it stopped?
Edit:
We have a 2 day downtime window in which the data has to be migrated and restored on cloud. The on premise database is hosted on Solaris Operating System and Operating System on the AWS cloud will be RedHat Linux since Solaris in not available in AWS.
Will Oracle Data guard or RMAN support the change in the Operating system on the servers?

1: Data Guard between x86 Linux and SPARC Solaris will not work (unless it is x86 Solaris), see:
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration (Doc ID 413484.1)
2: Same for RMAN, due to the difference in endianness of these hardware platforms. Even if the on-premise system is x86 Solaris, restoring a backup on different platform with same endianness is not supported (restore actually works, but recovery does not). Oracle allows building the standby databases with RMAN active duplication instead of backups in such configuration.
3: That will work, you should be able to manage less than2 TB in 2 days with this.
Q1: Most likely Data Pump, unless your on-premise is x86 Solaris.
Q2: It can reconnect and continue where it was interrupted.


Database agnostic auditing Background

Various databases have custom implementations for auditing:

SQL Server Audit
Oracle Audit
PostgreSQL Audit Trigger
MySQL Enterprise Audit

Problem

A number of problems exist:

There is no consistency (different commands, different log formats, etc.)
There does not appear to be an ANSI SQL standard
Some of the audits log to files, rather than a table or tables

Question

What ANSI SQL table auditing mechanism is:

database agnostic; and
tracks changes that can be queried using SQL (i.e., an audit table, not a log file)?

If no ANSI SQL standard for audit tables exists, then what alternatives exist for database-agnostic auditing (e.g., create a custom audit table) of CRUD operations against all tables in a schema?

Triggers make an auditing mechanism, that satisfies your criteria.

they are present in the ISO/ANSI standard (ISO 9075)
they can be used to track row-level changes
they can save data to tables, hence can be queried using SQL

Only thing I do not understand in your question is "database agnostic" - what does it mean, precisely?


Mysql replication slave source ports? Will the slave use source port 3006 and then connect to master on port 3306 to start replication? Or is random source port is being used to connect to master on 3306?

Unless otherwise specified, MySQL uses Port 3306.  That can be changed in various config files, CHANGE MASTER, startup command, etc.


In Oracle DB 11.2g, is it possible for the audit trail to explicitly mention account locking events? As it is, the audit trail will show login failures, and an attempt to log into a locked account (ORA-01017 and ORA-28000). What I would like to know is if it's possible for it to make a mention when the locking itself happens, not just in response to a user's actions.

So ideally it will say something to the effect of:

user login attempt failed
user login attempt failed
user login attempt failed
user account has been locked for multiple login failures

Is something like this possible?

I do not think it is possible. The AUDIT_ACTIONS table contains all possible actions in the audit trail. There is a LOCK action, but that refers to LOCK TABLE.

The LOCK_DATE column in DBA_USERS contains this information though.


Postgres database migration "zero" downtime I have a setup with a JBoss EAP server, postgres 9.1 database.
I will soon update so I have two JBoss servers in a cluster. This is mostly to avoid downtime when doing application deployments.

With the database as a single point of failure, how can I get close to zero downtime when having database migrations.

how can I get close to zero downtime when having database migrations.

Set up a streaming replica pair, with a master and a replica. Use WAL archiving in addition to streaming replication to deal with connectivity drops. Make sure your application is easily reconfigured to switch to a different server for if you need to fail over; if it's not easy to reconfigure, run a local PgBouncer on each node so you can switch it to connect to a different PostgreSQL without needing to mess with the rest of the server config.

A tool like repmgr makes this considerably simpler.

To learn more, read the manual.


Suggestions - Enabling EFS on FileStream Data for SQL Server If anyone has already tried enabling EFS on FileStream data for multiple databases around 500 GB+, please share your thoughts on feasibility or issues / considerations we should keep in mind.

We are in process of enabling EFS for PROD. We are testing in lower environments too.

Specifically, which one is better - enabling parallel EFS, or enabling EFS on one FileStream Folder at a time?

I can't speak to whether or not EFS should be applied per folder.  However, there is a security consideration that seems to be worth an answer here.

Check out this post from David Browne on the MSDN forums question Can FILESTREAM directory containers be encrypted with EFS?:

  Note that during a SQL Server backup of FILESTREAM data, the data is stored decrypted. A restore operation to a normal volume therefore will result in decrypted values.

So if you are using native SQL Server backups, you will need to keep this caveat in mind from a security / compliance standpoint.


Get a count of shippable orders I have two tables: orders and order_lines. I need a fast, inexpensive way to display a count for orders that are going to be shipping today.
The order_lines table is associated with the orders table view a foreign key based on order id. A line is shippable if the shipped field > 0.
An order is "shippable" if any of its lines have a qty of shipped.

Right now I basically have a query that reads through the orders, then through the lines (via sqlalchemy in python), and then if any of the lines can be shipped a counter is incremented. This works but it is very slow.

Is there some way on the database side or can create something to speed this up?

From your question I'd translate that you have the following table structure:

Orders:
    OrderId,
    OrderDetails

OrderLines:
    OrderLineId,
    OrderId,
    Shipped

I'd use the following statements for reading data:

-- shippable order lines
Select ol.*
from OrderLines ol
where ol.Shipped > 0

-- shippable orders
select *
from Order o
where Exists
(
    select *
    from OrderLines ol
    where ol.Shipped > 0
    and ol.OrderId = o.OrderId
)

In case the last script is again slow, than I'd guess that you need an index on the column OrderId in tabe OrderLines. In case this also doesn't work, then come back with the query that you used, the real tables definition and, most important, the EXPLAIN ANALYZE of your query.

PS: please keep in mind that I used the MS SQL flavor of the language, you might need small adjustments to make it work properly in PGSQL :-).


Database Design - Custom Attributes / Columns So I'm trying to figure out the best way to go about handling this database design wise. This is a rough idea of how I THINK it should go, but not really sure.

If I have orders that have their own necessary attributes that a user can create for all of their orders to have, and if the order can be comprised of items, and those items also can have their own custom attributes, and not necessarily the same attributes as the order, what makes the most sense?

Orders
======
id
client_id

Items
=====
id
user_id
name

Attributes
==========
id
user_id
is_order_attr
is_item_attr
name
type

Orders_Attributes
=================
id
order_id
attribute_id
value

Items_Attributes
================
id
item_id
attribute_id
value

A) Have an Attributes table for Orders and an Attributes table for Items, subsequently storing the values in another table, say Orders_Attributes_Values and Items_Attributes_Values?

B) Storing everything in a JSON Data Type column in some type of Orders_Configs table and then storing the values in additional tables with the JSON Data Type?

or

C) Something completely different?

A small JSON blob on Items to cover the wide range of descriptors of a item might be prudent. The rest of Orders / Clients tables should be fairly structured and not need any attributes.

Considering attribute tables as a primary design will get you into a mess when queries 3. EAV Tables — Entity-Attribute-Value Tables. A sharded Attribute table between a variety of other tables will make this even worse.


Finding matching records in a chain to form a parent user I need to find commonality across 3 columns of data. For instance, user 1 and user 2 have the same email. user 2 and user 3 have the same device id.  So I want to assign a unique parentuserid of 1 to user 1, 2, and 3. See below for sample data.
DROP TABLE IF EXISTS #usr
SELECT 'u1' AS userid, 'email1' AS email, 'device1' AS device INTO #usr UNION ALL
SELECT 'u2' AS userid, 'email1' AS email, 'device2' AS device UNION ALL
SELECT 'u3' AS userid, 'email99' AS email, 'device2' AS device UNION ALL
SELECT 'u4_nomatch' AS userid, 'email77' AS email, 'device77' AS device

-- expected results
-- new parentuserid    userid
--  1                u1
--  1                u2
--  1                u3
--  2                u4_nomatch

This parentuserid = 1 is a set of 3 users, but this could actually be n (50+). I thought maybe cross join with window functions would be needed here or maybe recursive CTE but I haven't had any luck. It's hard to Google this sort of problem.
SQL Server 2017

These users form a graph. They are linked by edges to common attributes (email, device etc.). The question boils down to finding disjoint subgraphs within the data, and assigning each a label.
There's an answer on SO which addresses this. The web will have many more if you care to search.
SQL Server 2019 introduced SHORTEST_PATH() which would simplify the query.


Using table values as column I have 3 tables:

Products, containing - id, name and description
Attributes, containing - id, name
prod_attri_rel, containing - id, prod_id, attri_id and value

Products looks like:

 1 | test | just a description for test
 2 | test2| just another description

Attributes looks like:

 1 | height
 3 | length
 2 | width
 4 | power
 5 | id

prod_attri_rel looks like:

1 | 1 | 1 | 2
2 | 1 | 2 | 25
3 | 1 | 3 | 20
4 | 2 | 1 | 2
5 | 2 | 2 | 25
6 | 2 | 3 | 20
7 | 2 | 4 | UBEC
9 | 2 | 5 | BC2212-850

After looking at https://dba.stackexchange.com/a/33307/48727 and https://stackoverflow.com/a/695860/1820180

Now I would like to get this result:

id | name | height | width | length | maybe other attr | so on and so forth
  1| test | 2      | 25    | 20     | ...

When I use the following query:

SELECT p.id, p.name , par.value, a.name FROM products p
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id

I get:

id | name | value| name
1  | test | 2    | height
1  | test | 25   | width
1  | test | 20   | length

Then when I tried:

SELECT p.id, p.name , group_concat( par.value ), group_concat( a.name ) FROM products p
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id GROUP BY p.name

I get a better result but not really it:

id | name | value   | name
1  | test | 25,2,20 | width,height,length

Last I found this https://stackoverflow.com/a/10926106/1820180 but I did not understand it enough to adapt it to my problem.

Any help in this matter would be appreciated, even if it is finding another method, or getting it to work.

Purpose: This is a database for different parts to RC toys, such as motors, ESC's wheels, propellers and such, all have different attributes, but all motors have same attributes, all wheels have same attributes.

The output of your query is fine but you must then pivot the data using GROUP BY. (SQL Fiddle)

Query:

SELECT p.id, p.name
  , MAX(IF(a.name='height',par.val,0)) As 'height'
  , MAX(IF(a.name='width',par.val,0)) As 'width'
  , MAX(IF(a.name='length',par.val,0)) As 'length'
FROM products p
JOIN prod_attri_rel par on p.id = par.prod_id
JOIN attributes a on par.attri_id = a.id
GROUP BY p.id, p.name
;

Output:

id  name    height  length  width
1   test    2       20      25
2   test2   3       35      30

Data:

CREATE TABLE Products(id int, name varchar(50), info varchar(50));

INSERT INTO Products(id, name, info) VALUES
(1, 'test', 'just a description for test')
, (2, 'test2', 'just another description');

CREATE TABLE Attributes(id int, name varchar(50));
INSERT INTO Attributes(id,name) values
    (1, 'height')
    , (3, 'length')
    , (2, 'width')
    , (4, 'power')
    , (5, 'id');

CREATE TABLE prod_attri_rel(id int, prod_id int, attri_id int, val varchar(20));
INSERT INTO prod_attri_rel(id, prod_id, attri_id, val) values
    (1, 1, 1, '2')
    , (2, 1, 2, '25')
    , (3, 1, 3, '20')
    , (4, 2, 1, '3')
    , (5, 2, 2, '35')
    , (6, 2, 3, '30')
    , (7, 2, 4, 'UBEC')
    , (9, 2, 5, 'BC2212-850');


How does the operator "&" work in sql server? I was running a trace on one of our test servers when someone did this:

and one of the queries I could catch in the trace was:

declare @UserOption int
select @UserOption=convert(int, c.value) from sys.configurations c where c.name='user options'

SELECT
CAST(@UserOption & 1 AS bit) AS [DisableDefaultConstraintCheck],
CAST(@UserOption & 2 AS bit) AS [ImplicitTransactions],
CAST(@UserOption & 4 AS bit) AS [CursorCloseOnCommit],
CAST(@UserOption & 8 AS bit) AS [AnsiWarnings],
CAST(@UserOption & 16 AS bit) AS [AnsiPadding],
CAST(@UserOption & 32 AS bit) AS [AnsiNulls],
CAST(@UserOption & 64 AS bit) AS [AbortOnArithmeticErrors],
CAST(@UserOption & 128 AS bit) AS [IgnoreArithmeticErrors],
CAST(@UserOption & 256 AS bit) AS [QuotedIdentifier],
CAST(@UserOption & 512 AS bit) AS [NoCount],
CAST(@UserOption & 1024 AS bit) AS [AnsiNullDefaultOn],
CAST(@UserOption & 2048 AS bit) AS [AnsiNullDefaultOff],
CAST(@UserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull],
CAST(@UserOption & 8192 AS bit) AS [NumericRoundAbort],
CAST(@UserOption & 16384 AS bit) AS [AbortTransactionOnError]

there is lots of information that can fit in a int if you know -

How does the operator & work?

A single ampersand is the Bitwise AND operator. The numbers on either side represent the value of one or more bit positions:

Actual binary (not BINARY datatype), evaluated from right to left -> 00100010

BIT    Position    Value (Position ^ 2)         "Selected"
0       0                            1                 0
1       1                            2                 2
0       2                            4                 0
0       3                            8                 0
0       4                           16                 0
1       5                           32                32
0       6                           64                 0
0       7                          128                 0
...   ...                          ...               ...
0      62          4611686018427387904                 0

Hence, 34 is made up of bitwise values of 2 and 32.

A very simple example showing that the & operator will return whatever value(s) is/are present on both sides is:

SELECT 1 & 1 -- 1
SELECT 1 & 2 -- 0 (because 2 is made up of only 2)
SELECT 1 & 3 -- 1 (because 3 is made up of 1 and 2)

SELECT 2 & 2 -- 2
SELECT 2 & 3 -- 2 (because 3 is made up of 1 and 2)
SELECT 2 & 4 -- 0 (because 4 is made up of only 4)

SELECT 3 &  4 -- 0 (because 3 is made up of 1 and 2, while 4 is made up of only 4)
SELECT 3 &  5 -- 1 (because 3 is made up of 1 and 2, while 5 is made up of 1 and 4)
SELECT 3 & 11 -- 3 (because 3 is made up of 1 and 2, while 11 is made up of 1, 2, and 8)

Looking back at the code you posted in the question, you should be able to see how it can be used to determine if one or more bits / flags are "set" within a given value. For example, let's say you want to require that AnsiPadding (value = 16), AnsiNulls (value = 32) and QuotedIdentifier (value = 256) are set to ON. You would add up those individual values -- 16 + 32 + 256 -- and then compare the total -- 304 -- with some "current" value, let's say 7000 (values 8 + 16 + 64 + 256 + 512 + 2048 + 4096):

SELECT 304 & 7000; -- 272

The result, 272, is the combination of 16 and 256. So if you want to require those 3 options (additional options will not adversely affect the result), you would use:

IF (@options & 304 = 304)
BEGIN
  ...
END;

As you can see, bitwise operations allow you to extract one or more "options" from one or more "options". But, in the code that you posted, they are just looking for individual options since using & with an individual bitwise value will return 0 if not present or that particular bitwise value if present. And the CAST to the BIT datatype will reduce all of those variations to either 0 (if the & yielded a 0) or 1 (if the & yielded anything else).

Please note that for the bitwise operators, either side (i.e. expression) can be:

  any of the data types of the integer data type category, or the bit, or the binary or varbinary data types.

However, counter-intuitively:

  In a bitwise operation, only one expression can be of either binary or varbinary data type.

That's right: "binary" operations do not work on purely "binary" data ;-). Attempting to do what seems entirely reasonable:

SELECT 0x03 & 0x0B;

will result in the following error:

  Msg 402, Level 16, State 1, Line 28
  The data types varbinary and varbinary are incompatible in the '&' operator.

In practical terms, this means that bitwise operations are constrained by the upper-limit of the BIGINT datatype (which is 8 bytes / 64 bits). Hence:

Total bits (i.e. flags / options): 63 (the 64th bit is used for negatives as BIGINT is signed)
Largest individual bit value: 4611686018427387904  (i.e. 2 ^ 62 ; 62 being the 63rd bit position))
Largest usable value: 9223372036854775807 (i.e. (2 ^ 63) - 1 ; all 63 bits present / set to "on")

Meaning: if you need to handle more than 63 flags / options, then you won't be using the & operator ;-).


How to interpret correlated subqueries? EDIT:
Both answers are great but I think I must clarify my question.

NOTE: I must indicate that in the following, by executed, I mean logically. I understand that the actual execution made by the particular DBMS may use a completely different approach. So what I will ask is not about the actual execution but rather about the evaluation logic of a correlated subquery.

What I particularly want to know is:

"Why a correlated subquery is executed in this way?" For example, for the example query I have given, why isn't it executed in the following way:

  "Return the rows of the cities_stores relation whose store_type is
  equal to some row's store_type in stores relation. If the
  resulting relation has at least 1 element, the value of EXISTS
  clause is TRUE. Otherwise, its value is FALSE."

but rather in this way:

  "For each row processed in the outer query (let's call this row as
  "current row"), process the inner (correlated) subquery, using the
  current row's values for the attributes not present in the subquery's
  FROM clause."

and from where can I learn to think about correlated subqueries in the latter method, instead of the former one?

I hope this is not an unwise question to ask but exactly this is what I don't understand.

Correlated subqueries are highly confusing me.

Example query from here:

SELECT DISTINCT store_type FROM stores
  WHERE EXISTS (SELECT * FROM cities_stores
                WHERE cities_stores.store_type = stores.store_type);

Initially, I thought that EXISTS clause would have a value that doesn't change for each row of the outer query and that value would be calculated in the following way:

"Return the rows of the cities_stores relation whose store_type is equal to some row's store_type in stores relation. If the resulting relation has at least 1 element, the value of EXISTS clause is TRUE. Otherwise, its value is FALSE."

Had the EXISTS statement evaluated in this way, it would have a value that does not change among various rows for the outer relation and hence, the query would be equivalent to the following:

SELECT DISTINCT store_type FROM stores
  WHERE TRUE; -- Or FALSE, if the subquery did not return any rows.

And of course this made me think how is an EXISTS statement is useful at all?

But apparently, that isn't the way a correlated subquery is executed. Apparently, a correlated subquery is executed in the following way:

"For each row processed in the outer query (let's call this row as "current row"), process the inner (correlated) subquery, using the current row's values for the attributes not present in the subquery's FROM clause."

So finally, what I don't understand:

Why is this the way it happens? I mean, why not it doesn't happen in the way I initially thought? (That is: Why it doesn't return the rows of the cities_stores relation whose store_type is equal to some row's store_type in stores relation?) From exactly where am I supposed to interpret that it is how SQL works?
In various sources around the internet, I constantly see the following about the correlated subqueries:

  In a correlated subquery, the subquery is evaluated once for each row
  processed by the outer query.

But most curiously, a statement similar to this does not exist in MySQL's page about correlated subqueries. So:

Is that an incompleteness in MySQL manual, or is such a statement simply not required? If it is not required, from where am I supposed to understand that it the way how a correlated subquery is evaluated? Or, as another possibility, is the quote I have given simply a vague statement that is not completely correct? If so, how am I supposed to interpret a correlated subquery?

This specific case (the exists predicate) is a bit of an outlier as far as correlated subqueries go.  ish...

Some correlated subqueries are certainly evaluated once for every row in the outer query, but I don't think you can apply that across the board, and I specifically think that's a statement that cannot be applied with authority to the exists predicate.

The SQL example below this paragraph is highly contrived, but nevertheless illustrates an "evaluated once for each row in the outer query" correlated subquery. It's "highly contrived" because a straight relational join would be a much better solution, and in this simple case the join is super easy to write.

select
    team_name
    , (
       select league_name
       from leagues
       where leagues.league_id = teams.league_id
    ) as league_name
from teams

In this instance, each row in the result set is going to contain the name of the league that the team belongs to, because of the correlated subquery. And the correlated subquery is probably going to be run once for each row in the outer query. Maybe.

The thing is, the database's query optimizer might rewrite this as a more efficient join, depending on the database. So even this simple example might not be executed "once per row in the outer query."

Either way, the subquery is dependent on the values returned from the table in the outer query, and that makes it a correlated subquery--because of the correlation with the outer table.

Part of the point of SQL in general is that the specific implementation is not important to you.

You should think in terms of sets of data (think set arithmetic), not in terms of iterations and comparisons per iteration.

The exists predicate doesn't make you any promises about the particular implementation. In general, exists is supposed to take advantage of table statistics and indexes in such a way that it produces a more efficient query plan than many other approaches will (and more efficient than you're likely to come up with on your own).

The actual query that ends up being run is going to be some kind of join, or multiple joins. But you don't need to get worked up about that. That's why your exists clause contains select * instead of identifying specific fields.

The query plan produced by the database engine will figure out which fields it wants to use, which key or keys and which index or indexes matter, etc.

So your exists example is still a correlated subquery, despite the underlying implementation, because the inner query refers to and is logically dependent upon the table in the outer query.

SELECT DISTINCT store_type FROM stores
WHERE EXISTS
    (
    SELECT * FROM cities_stores
    WHERE cities_stores.store_type = stores.store_type
    );


CREATE Windows User in database for not existing domain Is it possible to create a Windows User in the database when the domain is not accessible?
I am preparing database at my dev environment outside of the targeted domain.
Created user is not expected to work at my dev of course.
I am trying:
CREATE USER [NOTEXISTINGDOMAIN\User1] WITHOUT LOGIN;

But getting an error:
Windows NT user or group 'NOTEXISTINGDOMAIN\User1' not found. Check the name again.

I have searched for help and it seems there is no way...which I don't believe much cause this seems to me like relevant scenario.
ADDITION:
Let me explain my scenario. My task is to downgrade database to older MS SQL Server (SQL 2017 to SQL 2016 to be precise). I cannot do this in customers environment (and domain, DC) cause obviosly there is no both MS SQL versions available. So I amd doing the downgrade in my dev. And I want to prepare the database for my customer with all objects in it, including users of all types, including Windows Users - so when my customer restores the database everything is ready and the same as in original database.
Obviosly I'll have to create users later after restoring the database in the customers environment.

No, it isn't possible. SQL needs to contact the DC to collect the user/group SID and needs to save it inside the sys.server_principals table in master database.


What will happen when a forum uses 24 char length as an URL ID? I found a forum using self made/custom engine still in beta version, previously using vBulletin.

Now, their new engine uses very long URL id, 24 char length.

http://example.com/thread/000000000000000013186559/seo-friendly-title

What will happen to database performace when someone create URL id like this?
What the reason to use fixed and very long ID? This is very bad, isn't it?

FYI: this forum has more than 500,000,000 posts

But notice: it's not 24 bytes (256-bit characters), it appears to be a number only. And a 24-digit decimal number can be stored in just 10 bytes (12 digits, 999999999999, uses 5 bytes, E8D4A50FFF, so squaring that will use 10). A bigint is 8 bytes, and it can go up to 9.2 * 10^18. For all you know, they actually do use bigints and ignore the first 5 digits of the number. Even an int can store 500 million, but just 9 times that size will exceed the positive range. Perhaps they simply thought big when they started!

Even if they do use 10 byte keys, sure, it's a little unpleasant, but it's really not that big a deal. It all depends on the structure of the tables being used and their indexes, plus access patterns to those tables.

Anyone who has a database with five hundred million posts has some serious hardware and some developers/DBAs who are paid to keep it in good order.

Perhaps the post ID is sparse and can be converted by a lookup into something much shorter, and the database is built around the shorter key. By sparse, I mean that each next post ID is not sequential with the one before it, similar to credit card numbers, where some internal relationship between the digits proves it's valid. It's all pure speculation until we hear facts from someone who knows the architecture.


Oracle - Connecting as a proxy user via connection string I have been granted access to a specific schema in an Orale database via a "proxy user" / "connect through" .

In SQL Developer / TOAD, I give my username as "MyUser[SomeSchema]", and after login I am now able to query all tables etc. in SomeSchema. I can also login using just "MyUser", but then I am not able to query any tables in SomeSchema (not even if using the schema.table syntax.)

So my question is: How do I "replicate" the MyUser[SomeSchema] approach when connection from code using a connection string (using ODP.NET on .NET)?

UPDATE 1
I found that if I run the following SQL in Toad, I actually get the result I want.

connect MyUser[SomeSchema]/{password for MyUser};

My current schema is changed to SomeSchema and I can query it's tables.

Question is, whether this can be achieved via ODP.NET directly?

UPDATE 2
I finally figured out how to piece together the connection string. See my own answer below.

Research

I've googled quite a bit, and tried the following with no luck.

Supplying MyUser[SomeSchema] as "User Id".
Supplying the "Proxy User Id" and "Proxy Password" parameters in the connection string. However, I do not have a proxy user password.
Changing the schema after login using the "ALTER SESSION" syntax: "ALTER SESSION SET CURRENT_SCHEMA = SomeSchema". This does not fail, but I am still not able to query any tables in the schema.
Using SQL Tracker on Toad itself, to see if it ran any SQL to connect to the proxy, but that seems to happen "before" SQL Tracker can pick anything up. Triggers?

I finally found out how to represent the proxy user login via my connection string.

Proxy User ID = MyUser
Proxy Password = MyUser password
User Id = SchemaName
User Password not set.

The dime finally dropped  after visiting https://www.devart.com/dotconnect/oracle/docs/ProxyUserConnection.html and running the SQL query below, after having logged in via Toad. Here it was clear to me, that I had misunderstood, that MyUser was in fact the proxy user...

SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual


show variables - Unknown column 'value' in 'where clause' In mysql I ran the below:

mysql> SHOW VARIABLES where variable_name = 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.32 sec)

mysql> SHOW VARIABLES where variable_name = 'character_set_database' and value = 'utf8';
ERROR 1054 (42S22): Unknown column 'value' in 'where clause'

Why does the above happen?

SHOW does not have that syntax.  Simply do

SHOW VARIABLES LIKE 'character_set_database';

and don't include the 'value'.


Load balancing after clustering is failed in SQL Server cluster This is what I have been asked today on my interview for a SQL Server DBA position:

  It is an active - active cluster configuration. The primary node is in NY and the other node is in Rio. The NY server is failed and the failover cluster took over the activities. But the Rio server is becoming overloaded because of the additional activities. What will you do to solve the overload problem?

Please help as I am a newbee for the clustering concept.

GTT,

You stated 'SQL Server' so I assume you mean MS SQL Server.  The reasons it can be overloaded is because that single node is now handling the additional instances and that puts load on the shared resources (Memory, Cpu, Cpu threads, not disks though, etc.).  There are several ways of resolving this:

1-If you're going active-active, you should follow the N+1 rule so you would want 3 servers in the cluster.  This way if 1 fails over, if you setup the preferred nodes properly, it'll go to the empty node and you can finish your maintenance on the original node and the nodes aren't overloaded.

2-Setup resource groups per cluster, you should do this on an active-active implementation anyways.  You can limit the amount of RAM and set the CPU affinity (how many cpu cores the instance will use).  By doing this you can adjust the usage of the limited resources in advance, so the other instance which might not be as critical won't hog all the resoruces.

3-How do they feel about the other server being so far?  Distance increases latency which will have a direct impact on the customer receiving their queries, especially if best practices aren't being used and extra data is being generated (NO COUNT off, SELECT * instead of just what they need, etc.).  You should also check the wait times to see if the network is now a issue too.  For all we know the bottleneck is just the network and everything is fine.

This answer would satisfy me if I was interviewing you.   Good luck.  Have you been studying hard on SQL Server?


User can't right-click SQL Azure database in SSMS I've done the following on my SQL Azure instance:

1) Add a new login
2) Create a new user on my database from that login
3) Granted db_datareader to that user on that database

I can use that user to connect through SQL Server Management Studio, and I can expand the "Databases" node in the Object Explorer, but once I try to right-click the database to select "New query", the whole UI freezes for about 30 seconds.

I can connect and do the same thing with the db_owner, and it works without a problem.

Is there some other role/right I have to give to the user?

Please run the following statement on the Master database and that should do the trick

CREATE USER LoginName FROM LOGIN LoginName

Hope this helps.

Regards,

Alberto Morillo


Why shouldn't I have one table for multiple relationships? Assuming I have multiple relations in my database, for example Store, Employee and Sale, and I want to connect pairs with a simple binary relationship. Personally I would create tables named Employee_Store and Employee_Sale with a natural key composed of the foreign keys.

Now, my colleague insists on creating one table for multiple relationships. For the above example there could be a table called EmployeeLinks:

EmployeeLinks(
    IdLink int PK,
    IdEmployee int FK null,
    IdStore int FK null,
    IdSale int FK null,
    LinkType int not null
)

Please help me with good reasons why this is not a good idea. I have arguments of my own but I would like to keep them private and hear your unbiased opinions.

EDIT:

Initially the table above would have no primary key (!). Because the foreign keys allow null a surrogate key is the only option.

What does your colleague propose as the primary key for this link table?
Primary key columns can not be NULL of course: the table above has nullable.

There isn't any natural row identifier (which is what a PK is) in the example above (a IDENTITY column is not a primary key), therefore it fails in any modelling process. Don't even think about creating tables without some model (ERD, ORM, IDEF1X, whatever)

You'd also need CHECK constraints to ensure you don't have 3 way links.

Finally, you're straying into 4th and 5th normal form territory but for the wrong reasons.

I can't find any examples on the internet: that shows how stupid this is


Help me with sql backups please I'am facing some difficulties regarding my Sql Backup method, i am not sure if my method is correct, i am using a backup method described below;

Created a Full backup named Xbackup in Maintenance Plans, which backups in X External Drive with the extension of *.bak.
Once in a day Job created Automatically in Sql Server Agent .
Once in a day backup software transfers the bak files to a NAS device.
If *.bak files are older than 2 days a job deletes them on server
If *.bak files are older than 5 days a job deletes them on Nas Device

The difficulties are;

There are 7 database to backup and 7 *.bak files to copy, it is huge
330 GB to back up and transfer to NAS everyday and it is increasing up to 5
gb everyday.
Some times backups does not transfered correctly
it takes a lot of time to backup and transfer it to Nas device

Not: Nas device was shutting down when it finishes the transfer and Turn on when transfer time comes, but nowadays i canceled this task because of increasing file size i couldn't figure it out how long it takes to backup .bak files and how long Nas device has stay on in order to finish transfer.

What would be your suggestions?

There are 7 database to backup and 7 *.bak files to copy, it is huge 330 GB to back up and transfer to NAS everyday and it is increasing up to 5 gb everyday.

That is the size of the data you have. Either you need to back it up, or you don't. Are all of these databases in simple recovery mode? Should they be? One thing you could consider is taking full + diff/log backups and rotate the schedule. Database 1 gets a full backup on Sunday, and diff backups once a day for the rest of the week, and log backups throughout the day on each day. Database 2 gets a full backup on Monday, and diff backups once a day for the rest of the week, and log backups throughout the day on each day. And so on. This way you spread out the pain of the bigger backups throughout the week.

But as your data grows, you need to grow your capacity along with it, whether that's a better network pipe, a more reliable/bigger NAS, or both.

One thing to note is that if your backup file is getting bigger and bigger all the time, a very common issue is writing backups to the same file without using WITH INIT. We just talked about this here.

  Some times backups does not transfered correctly

This does not seem like a database issue, sounds like infrastructure. Maybe you need a better NAS or a more reliable network or better "backup software" or just simply a better, more resilient copy method (e.g. RoboCopy).

  it takes a lot of time to backup and transfer it to Nas device

Let's split those out.

If the backup takes a long time, again, consider whether you need to be taking full backups every day. This seems atypical unless you really could not care less about point in time recovery. If you do, and use full recovery model, this should help because (a) you won't have to perform a full, standalone backup every night, and (b) suspect the size of the log will be much better managed as well.
If the network transfer takes a long time, that is likely a symptom of your network and not anything you're going to be able to fix with code or changes to your backup routine. But this should be a background process anyway. Does it matter if it takes 10 minutes or 12 minutes? Is someone actively waiting on this copy to complete?


Max connection user in mysql I want to increase Max-connection upto 1000 .How much RAM is required current 4GB RAM with Max-connection 500 ...

If any formula please share to me

This is an interesting read for How MySQL uses memory
We have,
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_buffer_size, query_cache_size
and
Per Thread Buffers: read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, thread_stack, max_allowed_packet, net_buffer_length
To calculate present status you can download procedure here and just run it from MySQL.
I guess we can formulate number of Threads (nT) = ( R - GB  ) / PTB

Where,
R - Ram
GB - Global Buffers
PTB - Per Thread Buffers


Clean Code - On which side to place the key I am currently modelling a database schema. In it, addresses are used several times, which follow a fixed structure. In our previous systems, the address relation was modelled in such a way that the address contained foreign keys, e.g. those of a user.
Reasoning: In this way, an address can only be attached to one parent. From my point of view, however, this is an absolute chaos because sometimes up to eight foreign keys exist in the address relation, whereby care must be taken that only one is filled.
Wouldn't it make much more sense to place the key on the parent, so that by means of a not-zero constraint it can be ensured that each parent has an address and the address relation is clean? The parent is also rarely/never navigated to from the address side.
Or am I violating any conventions by doing this? In such a case, doesn't cleanliness weigh more heavily than a "convention"? If desired, one could also model the previous requirements additionally using triggers?
Current approach: Constraint to ensure only one FK is set on address.

My approach: address_id as FK on parents.

Yes so your approach / the second approach is generally more favorable and normalized because it leads to less redundant addresses. But you lose the flexibility of a user having multiple addresses this way, because of this one-to-one approach. If that's not a requirement of the logical schema, then your approach makes more sense.
Alternatively to support the normalization and flexibility best of both worlds, you can add one more table called userAddress that is a bridge / linking table between the two, supporting a many-to-many relationship by storing the user_id and address_id fields as pairs of all addresses for each user.
So in short:

Approach 1: More flexibility, but less normalization / more data redundancy is possible
Approach 2: More normalization / less data redundancy, less flexibility
Approach 3 (Bridge Table): Flexibility and normalization so reduced data redundancy

Also to your second question regarding Triggers, they aren't all implemented exactly the same way across all modern RDBMS (because the ANSI-compliant spec for them is very minimal relative to the other features of SQL). So for a generalized answer (since we're not talking about a specific database system in the context of this question), it depends on the database system if you'd be able to replicate the desired behavior via a Trigger. (I see you personally use PostgreSQL, so I believe in that context, then it would be possible with a Trigger.)


Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes? I have a VARCHAR(80) utf8mb4 column, and I'm testing adding some ASCII & Emoji characters, and use LENGTH() and CHAR_LENGTH() to understand the differences.
From reading in different places, my understanding is that each character on a utf8mb4 column would take 4 bytes. However, it appears that it may not be the case, if I understand LENGTH() as giving me the actual size that the specific contents are taking on that field.
Is it correct that a row with "aaaa" contents would take less storage than a row with ""?

Actually, my answer is in the Documentation:
https://mariadb.com/kb/en/unicode/

UTF-8 encoding using one to three bytes per character. Basic Latin
letters, numbers and punctuation use one byte. European and Middle
East letters mostly fit into 2 bytes. Korean, Chinese, and Japanese
ideographs use 3-bytes. No supplementary characters are stored.
utf8mb4   -- Same as utf8, but stores supplementary characters in four
bytes.

Also, as per https://mariadb.com/kb/en/data-type-storage-requirements/

String Data Types
In the descriptions below, M is the declared column length (in characters or in bytes), while len is the actual length in bytes of the value.
VARCHAR(M) -- len + 1 bytes if column is 0 – 255 bytes, len + 2 bytes if column may require more than 255 bytes

So, for VARCHAR(80) utf8mb4,
80x4 > 255, so a field with "aaaa" will take 6 bytes (len + 2)


Vacuum on TOAST table Is it possible to get the number of dead/live tuples for TOAST table as we do for normal table?

CREATE TABLE messages1 (message text);

INSERT INTO messages1
SELECT (SELECT
        string_agg(chr(floor(random() * 26)::int + 65), '')
        FROM generate_series(1,10000))
FROM generate_series(1,10);

SELECT reltoastrelid::regclass
 FROM pg_class
 WHERE relname = 'messages1';

 DELETE FROM messages1
WHERE ctid IN (
    SELECT ctid
    FROM messages1
    ORDER BY 1
    LIMIT 6
);

postgres=# SELECT reltoastrelid::regclass
postgres-#  FROM pg_class
postgres-#  WHERE relname = 'messages1';
      reltoastrelid
--------------------------
 pg_toast.pg_toast_147777
(1 row)

postgres=# select relname, vacuum_count, n_tup_del, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='pg_toast.pg_toast_147777';
 relname | vacuum_count | n_tup_del | n_live_tup | n_dead_tup
---------+--------------+-----------+------------+------------
(0 rows)

TOAST table is treated as system table , so you need to query pg_stat_sys_tables, not pg_stat_user_tables:

select relname, vacuum_count, n_tup_del, n_live_tup, n_dead_tup
from pg_stat_sys_tables
where relname='pg_toast_147777'

Or you can put it all together,

select  cl.relname ,vacuum_count, n_tup_del, n_live_tup, n_dead_tup
 FROM pg_class cl
 inner join pg_stat_sys_tables st on st.relid = cl.reltoastrelid
 WHERE cl.relname = 'messages1';

Quoting the manual:

  The pg_stat_all_tables view will contain one row for each table in the
  current database (including TOAST tables), showing statistics about
  accesses to that specific table. The pg_stat_user_tables and
  pg_stat_sys_tables views contain the same information, but filtered to
  only show user and system tables respectively.


How can I reset a mysql table auto-increment to 1 in phpMyAdmin? I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this:

ALTER TABLE tablename AUTO_INCREMENT = 1

I am curious if there is a way to do this from within phpMyAdmin.  Something like a check box to reset the auto-increment or something else along those lines?

Not that there is anything wrong with the command line approach.  More one of those curiosity things I keep thinking on...  Thanks in advance!

phpmyadmin
Perhaps you could just select the phpMyAdmin Operations tab:

In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value
Click on the Operations Tab
In the Table Options box find the auto_increment field.
Enter the new auto_increment starting value
Click on the Go button for the Table Options box.

Since this one of the most frequently asked questions for phpmyadmin, you can learn more about this in this blog : http://trebleclick.blogspot.com/2009/01/mysql-set-auto-increment-in-phpmyadmin.html
Supplemental Info
For an empty table, another way to reset the auto_increment attribute is to run
TRUNCATE TABLE mydb.tablename;

Don't run this if you have data in it. If you want to hose the data, then be my guest.
In phpmyadmin, just click the SQL tab, enter the command, and run it.
For a nonempty table, you may want to adjust the auto_increment attribute to the highest existing id in use in case higher entries were deleted.
First, optimize the table
OPTIMIZE TABLE mydb.mytable;

Next, locate the highest value for the auto_increment column (say it is id)
SELECT MAX(id) maxid FROM mydb.mytable;

Suppose the answer returns 27. Goto the Operations tab and enter 28.


Apply profiler template to saved log I need to check for deadlocks in my customer's system. He provided me with unfiltered logs.

Can I filter them after running the trace somehow? (I don't have direct access to his server)

It seems like the template can be applied only before running the trace.

I don't believe you can apply a template after a trace file is saved, however you can work around this by exporting the results to a table.

If you open the trace file in SQL Profiler and the navigate to File > Save As > Trace Table, you can output the result set to a table in an existing or new database and then you can query/filter the result set to your hearts content.