How to handle multiple hierarchies in a data model? In Sql Server, there is a datatype called HIERARCHYID which helps with hierarchies. In our environment, we have patient data that needs to be classified under multiple hierarchies.

Example 1: Hierarchy1, that categories patient's based on geography.

Country >> state >> etc... .

A Patient can only be identified in one node of the hierarchy.
When a patient moves, their association with node is also moved to appropriate new node.

Example 1: Hierarchy2, based on benefits they claim, they are put into one program or another.

Program >> CCMA >> etc

Again, same rules as before apply:

A Patient can only be identified in one node of the hierarchy.
When a patient changes plans (program), their association with node is also moved to appropriate new node.

We could have 1 or 2 or ... n hierarchies. How do we handle them?

This is different from Patient_Groups that are used for ad hoc grouping.

Thanks,

Jeff Moden has written two excellent articles on SQL Hierarchies here (Hierarchies on Steroids #1) and here (Hierarchies on Steroids #2) which present efficient SQL algorithms for converting hierarchies stored as an Adjacency List (ie children have a Parent pointer - easier to visualize and more efficient to create) to a temporary table organized as nested sets (more efficient for reporting).

Given Jeff's good work in describing how to efficiently convert to Nested Sets as needed, I would recommend storing and maintaining your hierarchies as a Adjacency Lists.

By making each Hierarchy an independent table you will gain the benefit of decoupling the hierarchies from the base patient data, facilitating the addition of additional hierarchies as required.

The Code (Thank you Jeff):

 CREATE PROCEDURE dbo.RebuildNestedSets AS
/****************************************************************************
 Purpose:
 Rebuilds a "Hierarchy" table that contains the original Adjacency List,
 the Nested Sets version of the same hierarchy, and several other useful
 columns of data some of which need not be included in the final table.

 Usage:
 EXEC dbo.RebuildNestedSets

 Progammer's Notes:
 1. As currently written, the code reads from a table called dbo.Employee.
 2. The Employee table must contain well indexed EmployeeID (child) and
    ManagerID (parent) columns.
 3. The Employee table must be a "well formed" Adjacency List. That is, the
    EmployeeID column must be unique and there must be a foreign key on the
    ManagerID column that points to the EmployeeID column. The table must not
    contain any "cycles" (an EmployeeID in its own upline). The Root Node
    must have a NULL for ManagerID.
 4. The final table, named dbo.Hierarchy, will be created in the same
    database as where this stored procedure is present.  IT DOES DROP THE
    TABLE CALLED DBO.HIERARCHY SO BE CAREFUL THAT IT DOESN'T DROP A TABLE
    NEAR AND DEAR TO YOUR HEART.
 5. This code currently has no ROLLBACK capabilities so make sure that you
    have met all of the requirements (and, perhaps, more) cited in #3 above.

 Dependencies:
 1. This stored procedure requires that the following special purpose HTally
    table be present in the same database from which it runs.

--===== Create the HTally table to be used for splitting SortPath
 SELECT TOP 1000 --(4 * 1000 = VARBINARY(4000) in length)
        N = ISNULL(CAST(
                (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)*4+1
            AS INT),0)
   INTO dbo.HTally
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
;
--===== Add the quintessential PK for performance.
  ALTER TABLE dbo.HTally
    ADD CONSTRAINT PK_HTally
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;

 Revision History:
 Rev 00 - Circa 2009  - Jeff Moden
        - Initial concept and creation.
 Rev 01 - PASS 2010   - Jeff Moden
        - Rewritten for presentation at PASS 2010.
 Rev 02 - 06 Oct 2012 - Jeff Moden
        - Code redacted to include a more efficient, higher performmance
          method of splitting the SortPath using a custom HTally Table.
****************************************************************************/
--===========================================================================
--      Presets
--===========================================================================
--===== Suppress the auto-display of rowcounts to prevent from returning
     -- false errors if called from a GUI or other application.
    SET NOCOUNT ON;

--===== Start a duration timer
DECLARE @StartTime DATETIME,
        @Duration  CHAR(12);
 SELECT @StartTime = GETDATE();

--===========================================================================
--      1.  Read ALL the nodes in a given level as indicated by the parent/
--          child relationship in the Adjacency List.
--      2.  As we read the nodes in a given level, mark each node with the
--          current level number.
--      3.  As we read the nodes in a given level, convert the EmployeeID to
--          a Binary(4) and concatenate it with the parents in the previous
--          level's binary string of EmployeeID's.  This will build the
--          SortPath.
--      4.  Number the rows according to the Sort Path.  This will number the
--          rows in the same order that the push-stack method would number
--          them.
--===========================================================================
--===== Conditionally drop the final table to make reruns easier in SSMS.
     IF OBJECT_ID('FK_Hierarchy_Hierarchy') IS NOT NULL
        ALTER TABLE dbo.Hierarchy
         DROP CONSTRAINT FK_Hierarchy_Hierarchy;

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

RAISERROR('Building the initial table and SortPath...',0,1) WITH NOWAIT;
--===== Build the new table on-the-fly including some place holders
   WITH cteBuildPath AS
( --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
 SELECT anchor.EmployeeID,
        anchor.ManagerID,
        HLevel   = 1,
        SortPath =  CAST(
                        CAST(anchor.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000)) --Up to 1000 levels deep.
   FROM dbo.Employee AS anchor
  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
  UNION ALL
 --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.EmployeeID,
        recur.ManagerID,
        HLevel   =  cte.HLevel + 1,
        SortPath =  CAST( --This does the concatenation to build SortPath
                        cte.SortPath + CAST(Recur.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000))
   FROM dbo.Employee      AS recur WITH (TABLOCK)
  INNER JOIN cteBuildPath AS cte
          ON cte.EmployeeID = recur.ManagerID
) --=== This final INSERT/SELECT creates the Node # in the same order as a
     -- push-stack would. It also creates the final table with some
     -- "reserved" columns on the fly. We'll leave the SortPath column in
     -- place because we're still going to need it later.
     -- The ISNULLs make NOT NULL columns
 SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),
        sorted.ManagerID,
        HLevel     = ISNULL(sorted.HLevel,0),
        LeftBower  = ISNULL(CAST(0 AS INT),0), --Place holder
        RightBower = ISNULL(CAST(0 AS INT),0), --Place holder
        NodeNumber = ROW_NUMBER() OVER (ORDER BY sorted.SortPath),
        NodeCount  = ISNULL(CAST(0 AS INT),0), --Place holder
        SortPath   = ISNULL(sorted.SortPath,sorted.SortPath)
   INTO dbo.Hierarchy
   FROM cteBuildPath AS sorted
 OPTION (MAXRECURSION 100) --Change this IF necessary
;
RAISERROR('There are %u rows in dbo.Hierarchy',0,1,@@ROWCOUNT) WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Using the information created in the table above, create the
--      NodeCount column and the LeftBower and RightBower columns to create
--      the Nested Sets hierarchical structure.
--===========================================================================
RAISERROR('Building the Nested Sets...',0,1) WITH NOWAIT;

--===== Declare a working variable to hold the result of the calculation
     -- of the LeftBower so that it may be easily used to create the
     -- RightBower in a single scan of the final table.
DECLARE @LeftBower INT
;
--===== Create the Nested Sets from the information available in the table
     -- and in the following CTE. This uses the proprietary form of UPDATE
     -- available in SQL Serrver for extra performance.
   WITH cteCountDownlines AS
( --=== Count each occurance of EmployeeID in the sort path
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
        NodeCount  = COUNT(*) --Includes current node
   FROM dbo.Hierarchy h,
        dbo.HTally t
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
  GROUP BY SUBSTRING(h.SortPath,t.N,4)
) --=== Update the NodeCount and calculate both Bowers
 UPDATE h
    SET @LeftBower   = LeftBower = 2 * NodeNumber - HLevel,
        h.NodeCount  = downline.NodeCount,
        h.RightBower = (downline.NodeCount - 1) * 2 + @LeftBower + 1
   FROM dbo.Hierarchy h
   JOIN cteCountDownlines downline
     ON h.EmployeeID = downline.EmployeeID
;
RAISERROR('%u rows have been updated to Nested Sets',0,1,@@ROWCOUNT)
WITH NOWAIT;

RAISERROR('If the rowcounts don''t match, there may be orphans.'
,0,1,@@ROWCOUNT)WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Prepare the table for high performance reads by adding indexes.
--===========================================================================
RAISERROR('Building the indexes...',0,1) WITH NOWAIT;

--===== Direct support for the Nested Sets
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT PK_Hierarchy
        PRIMARY KEY CLUSTERED (LeftBower, RightBower) WITH FILLFACTOR = 100
;
 CREATE UNIQUE INDEX AK_Hierarchy
     ON dbo.Hierarchy (EmployeeID) WITH FILLFACTOR = 100
;
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT FK_Hierarchy_Hierarchy FOREIGN KEY
        (ManagerID) REFERENCES dbo.Hierarchy (EmployeeID)
     ON UPDATE NO ACTION
     ON DELETE NO ACTION
;
--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Exit
--===========================================================================
RAISERROR('===============================================',0,1) WITH NOWAIT;
RAISERROR('RUN COMPLETE',0,1) WITH NOWAIT;
RAISERROR('===============================================',0,1) WITH NOWAIT;
GO


Possible to get equivalent MySQL bin log position on another server? Let's say we have three MySQL servers, mysql1, mysql2 and mysql3. Replication is set up such that mysql3 is the slave of mysql2, and mysql2 is the slave of mysql1.

At some point I take a dump of mysql2 and record the binlog file and position of it by running show master status;. Is there a way to find the equivalent binlog file and position on mysql1 - for example so that I could load up the dump and continue replicating from mysql1 instead of mysql2?

I managed to do this by examining the log files using mysqlbinlog.

Given the following master log position on mysql2:

Master log file = mysql-bin.003151
Master log pos  = 46678888

I opened the log file:

mysql2> mysqlbinlog mysql-bin.003151 | less

And searched for the position (46678888).

# at 46678861
#170301 12:15:33 server id 2  end_log_pos 46678888
  Xid = 12652204449 COMMIT/*!*/;
# at 46678888
#170301 12:15:36 server id 2  end_log_pos 46678967
  Query   thread_id=414919 69      exec_time=1     error_code=0
  SET TIMESTAMP=1488370536/*!*/; BEGIN /*!*/;
# at 46678967

The nearby line of SET TIMESTAMP=1488370536 looked like a good candidate to search for in the corresponding log on mysql1. So I opened up the master log file on mysql1 (there were multiple to choose from, but I went with the one with the closest timestamp to that on the slave, you might have to try a few files):

mysql1> mysqlbinlog mysql-bin.002966 | less

And searched for SET TIMESTAMP=1488370536. This is what I found:

# at 46236234
#170301 12:15:33 server id 2  end_log_pos 46236261
  Xid = 88972150 COMMIT/*!*/;
# at 46236261
#170301 12:15:36 server id 2  end_log_pos 46236325
  Query   thread_id=414919    69      exec_time=1     error_code=0
  SET TIMESTAMP=1488370536/*!*/; BEGIN /*!*/;
# at 46236325

I checked surrounding lines to ensure I'd got the right place and then used the corresponding end_log_pos (in this case 46236261) as the new log position. Therefore my master log position on mysql1 is:

Master log file = mysql-bin.002966
Master log pos  = 46236261

It's probably possible to automate this process somewhat, but to be honest it was very quick and easy to do it manually (and I don't expect to have to do again much if at all) - it's taken longer to write up the process than actually do it.


SELECT to only see Offsetting Transactions in table? I have a temp table like this

I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.
So, I only want to see

CCC 333333333  Ambition Trust  Purchase,
CCC 333333333  Ambition Trust  Sell

This is the SQL giving me the same Portcode, Cusip combo from a table.
SELECT PortfolioName, CUSIP, COUNT(*) AS CNT
INTO #tmpCusip
FROM #tmpTrades
GROUP BY PortfolioName, MCUSIP
HAVING COUNT(*) > 1

Does anyone have any ideas?

I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.

There is no Buy in your table. You only have Sell and Purchase.

Below is a good starting point for you to get (Purchase,Sell) combo :

;with temptrades
as
(select *, row_number() over (partition  by PortfolioName, [transaction]
order by PortfolioName, [transaction]) as rowNumber
    from tmpTrades
)
select  portcode,cusip, PortfolioName,  [transaction]
 from temptrades
where  rowNumber = 1
order by portcode

SQLFiddle here.


Mysql Performance tuning for myisam tables. Folks

I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records files)

I have a table(billing_datas) with about 200 million records.( myisam table). ( basically we store all the cdr files data with the ids of carriers etc) which keeps on growing at a rate of 90 million rows per month

We need to generate reports based on selected "date from" and "date to" criteria. I m confused with the approach

The current scenario that we have is we are firing the query on the main table i.e billing_datas and creating the temporary table with "date from" and "date to" parameters and performing the aggregate calculations on this temp table. But the real performance hit is creating the temporary table is taking about 18 seconds for 1 month records.

should we create 3 summary tables based on
summary_monthly,summary_weekly,summary_daily and run the reports
query on these summary tables??
will it improve the performance as described in point 1?

Increase size of tmp_table_size

The maximum size of internal in-memory temporary tables. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.

Based on tmp_table_size value adjust max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables.

After setting proper tmp_table_size and max_heap_table_size restart your server and try those reporting queries and observe performance.

Please refer How to optimize Group By queries from here:

http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html


Raise Multiple Exceptions Postgres I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function.

I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted, return the user friendly error to the user, and log the sql server error message somewhere. Is that possible to do inside a stored procedure?

Here is what I have so far, but it does not work:

CREATE FUNCTION CREATE_USER_FUNC (
role VARCHAR,
firstname VARCHAR,
lastname VARCHAR,
email VARCHAR,
username VARCHAR,
is_enabled BOOLEAN,
city VARCHAR,
state CHAR,
zipcode INTEGER,
password VARCHAR,
team VARCHAR,
OUT new_user_id INTEGER
)
RETURNS integer AS $$
BEGIN
INSERT INTO users (firstname, lastname, email, username, is_user_enabled, city, us_state, zipcode, user_password) VALUES($2, $3 ,$4, $5, $6, $7, $8, $9, $10) RETURNING user_id INTO new_user_id;
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values can not be null';
    RAISE EXCEPTION SQLSTATE '23514' USING MESSAGE = 'Password not the proper length';

INSERT INTO role_members (user_id, role_name) VALUES(new_user_id, $1);
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values not present in the table';

INSERT INTO team_members (user_id, dealership) VALUES(new_user_id, $11);
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values not present in the table';
EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION
END;
$$ LANGUAGE plpgsql

If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted

That happens automatically if any exception is raised inside a function (by you or by Postgres). A function is always atomic and can only be run within a transaction context - which is the main distinction from real stored procedures (currently implemented in Postgres). There are a few exceptions: things that cannot be rolled back, like incremented serials or dblink calls.

  ... return the user friendly error to the user

You might just go with what Postgres returns by default. If you insist on custom error messages, be careful not to catch the wrong exception. You want to return two different messages for the same error code 23502. You would need separate blocks to achieve this in plpgsql.

  and log the sql server error message somewhere

If you are happy with the standard log output in the database log, that's plain and simple (and highly configurable). The manual:

  Whether messages of a particular priority are reported to the client,
  written to the server log, or both is controlled by the
  log_min_messages and client_min_messages configuration
  variables. See Chapter 18 for more information.

If you think of writing to a table, that would be the tricky part since everything is rolled back in case of an exception. That would include everything written to a log table. You'd have to fake an autonomous transaction to persist the log entries even though the transaction is rolled back. Detailed instructions:

Persistent inserts in a UDF even if the function aborts

To ensure passwords with proper length, simply create a CHECK constraint with a descriptive name. Like password_needs_8_char_or_more. Then you don't have to do anything extra. Postgres will raise an exception including the name of the constraint in case of a check_violation (error code 23514).

I would make it a simple SQL function with data-modifying CTEs. Fastest, safest, simplest - and go with the built-in error messages and built-in error logging:

CREATE OR REPLACE FUNCTION create_user_func(
   role text, firstname text, lastname text, email text, username text
 , is_enabled boolean, city text, state text  -- char is a dubious data tpye
 , zipcode int, password text, team text
 , OUT new_user_id int
   ) RETURNS integer AS
$func$
   WITH i1 AS (
      INSERT INTO users (firstname, lastname, email, username, is_user_enabled
                       , city, us_state, zipcode, user_password)
      VALUES($2, $3 ,$4, $5, $6, $7, $8, $9, $10)
      RETURNING user_id
      )
   , i2 AS (
      INSERT INTO role_members (user_id, role_name)
      SELECT i1.user_id, $1
      )
   INSERT INTO team_members (user_id, dealership)
   SELECT i1.user_id, $11;
$func$ LANGUAGE sql;

And you probably do not want to use the data type char. Just use text. Or varchar(1) if you need the length restriction. Or possibly the special type "char" (with double quotes) if it's just a single ASCII character:

Any downsides of using data type “text” for storing strings?


Get the rank of a user in a score table I have a very simple MySQL table where I save highscores. It looks like that:

Id     Name     Score

So far so good. The question is: How do I get what's a users rank?
For example, I have a users Name or Id and want to get his rank, where all rows are ordinal ordered descending for the Score.

An Example

Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

In this very case, Assem's rank would be 3, because he got the 3rd highest score.

The query should return one row, which contains (only) the required Rank.

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC )
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3

You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the score column would help performance on large tables.


SQL Server Index Scan Actual Executions I have a fairly complicated query and running it on two databases with same schemas, but with different data produces different result. On database D1 the elapsed time is around 1 second, and the number of returned rows is 4936. On database D2 the elapsed time is around 10 seconds, adn the number of returned rows is 135. The returned data is correct and expected in both cases, but the execution time is what confuses me.

Both databases have the same indexes. All statistics have been updated and indexes reorganized or rebuilt where neccessary. However, the actual execution plans on both databases differ slightly in their layout and costs.

On database D2 29% of time is wasted on Index Scan of a non-clustered index that executes 409 times (expected executes = 1) and returns 2195 rows, even though it expected only 146. With a Clustered Index Seek (only 2% cost) on another table that executes 2195 times (estimated executions = 146) and that returns 2195 rows (estimated rows = 146, both based on upper Index Scan) it enters into a Nested Loops Inner Join (9% cost).

The same Index Scan on D1 takes only 4.1% of time and executes only once and returns 4451 rows, even though it expected 4491. After it goes Compute Scalar, and then the arrow joins with output from Compute Scalar from Clustered Index Scan (instead of Clustered Index Seek in D2) into Hash Match Inner Join.

Can anyone tell me why this might be happening?

Also, in the plan on D2 I have another Index Scan of the same problematic index that also has 29% cost, but returns 0 rows, even though it expects 27 rows. In D1 this returns 7 rows, expects 8, and has 4% cost.

I'm completely lost. Any help will be appreciated.

Schema and indexes are only one aspect of query plan and performance.  Your statement "but with different data" is likely the source of the difference. The number of rows and the distribution of data is essential to the query optimizer.  If you have significantly more rows in D2, or if the data is of entirely different characteristics (wider or narrower range of values), then you should expect to see different performance and execution plans.

For each set of statistics, SQL Server keeps a maximum of 200 samples. As the rows in the tables grow and the more irregular the distribution of values the more likely it is that SQL Server will not have enough information to generate optimal execution plans.  That's where the use of filtered indexes and statistics comes into play.

If this is a parameterized query you may also be running into a parameter sniffing problem.  Note that if you're using local variables the calculation changes also.


Too many TEXT fields leads to record too big I have a table with multiple 9 TEXT columns, each containing JSON data.  I usually UPDATE all the columns at once.  I can't seem to add a 10th TEXT column.

The table is InnoDB.

What can I do?

You are near or at a limit of row size.  There is a limit of about 8000 bytes per record.  TEXT fields, by default, store only 767 bytes toward that 8000, and store the rest elsewhere.  You have enough TEXT fields to threaten the 8000.

Plan A:  Have one JSON TEXT field that is a structure containing the 9 structures you now have.  This will easily avoid the 8000.  And, since you seem to update all of them all the time, it is probably more efficient.  (If it could exceed 65KB, make it MEDIUMTEXT instead.)

Plan B:  ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED would drop that 767 to 20, thereby no longer threatening to hit 8000.

Plan C:  "Vertical partitioning" -- That is, put some columns in a 'parallel' table.

I prefer Plan A, but it that does not fit with your access patterns, then consider the other options.

Actually, I would go with a variant of Plan A:  I would compress the JSON in the client, then store into a BLOB (TEXT won't work with compressed data).  That shrinks the disk footprint, cuts back on the network traffic, etc.

Yes, a single UPDATE (setting many fields of one row) is better than multiple UPDATEs (each setting one field of the same row).

Reference:  http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html

Another note:  If all 9 text fields (as you currently have it) are all longer than 767 bytes, then they are stored in 9 different places outside the record.  Plan A would lead to only 1 'overflow' place for the text.


Extended Events session file truncated on server restart I have created an extended events session to capture login and logout information. The session is setup to store events to a file.
I understood that one of the advantages of using an event file (rather than a ring buffer) was that events history would be persistent across a restart.
However it appears that a new event file is created when SQL Server is restarted and the history is lost.
Is this expected? If so, what is the advantage of using a file rather than a ring buffer?

Based on your question (and additional comments regarding your setting of max_rollover_files to 1:

  However it appears that a new event file is created when SQL Server is restarted and the history is lost. Is this expected?

Yes - that is expected.  When you restarted SQL Server all currently running extended events were terminated.  When you restarted SQL Server, a new extended event session started and your existing target file was replaced because you indicated you only wanted 1 file.  If you want to preserve extended event data stored in target files across server restarts, you will have to increase the max_rollover_files to something greater than 1.

sys.fn_xe_file_target_read_file (path, mdpath, initial_file_name, initial_offset) has the capability of reading all extended event files in a the path referenced by mdpath if you specify null for the initial_file_name, so maintaining multple files should not be a problem.

Regarding the use of the Ring Buffer, take a look at why Jonathan Kehayias (world renowned expert in SQL Server Extended Events) Hates the ring buffer.


How to set the timeoffset in a Datetimeoffset column without changing the date part Is there any way to set the timeoffset of a datetimeoffset column directly without having to use SWITCHOFFSET which changes the date as well?

In the post below, we can see the SWITCHOFFSET will change the date accordingly along with setting the timeoffset (which is not what we want):
http://blog.sqlauthority.com/2010/07/15/sql-server-datetime-function-switchoffset-example/

For example I have the date value as '2013-02-10' and want to record it in different timezones, without losing the '2013-02-10'. Just set the timeoffset to what needed , e.g: +11, -4, -6, etc.

Is it possible?

Thanks.

To avoid the extra DateAdd (may be difficult if time zone is a variable), use:

TODATETIMEOFFSET(@datetimeoffset, @timeZone)


How to fetch file path from a folder through PL/SQL We upload files on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible.

If possible, please suggest what should I do.

Mr. Kyte wrote about accessing os-file-information utilizing the preprocessor directive a while ago. Perhaps this is what you are after.

query the operating system


Indexing strategy for VARCHAR2 LIKE search Scenario

Database

Oracle Database 11g

Table

Users

ID Number, PK
CODE Number, Indexed, Unique, Not null
FIRST_NAME VARCHAR(100), Not null
LAST_NAME VARCHAR(100), Not null
EMAIL VARCHAR(50), Not null

Search

Except for ID and CODE, all other columns has mixed case.
I need to search a user by an string. The search should be case insensitive.
The string is a partial value. (ex: 'foo' to search for 'foobar')
The pattern can be present on any part of a value on the colum. (ex: 'ob' for foobar)
The pattern can be present on any of the table fields.

SQL

SELECT *
FROM users u
WHERE
u.code = COALESCE( to_number( REGEXP_REPLACE(:pattern, '[^0-9]+', '') ), -1)
OR upper(u.email) LIKE upper(:pattern)
OR upper(u.last_name) LIKE upper(:pattern)
OR upper(u.first_name) LIKE upper(:pattern)

Query Plan

Results show a full table scan with an OR Filter Predicate

Question

What is the best strategy to optimize this query? Can we reduce the cost somehow?
Is beneficial to use an index on the VARCHAR2 column(s)? Even with the search using the LIKE operator?
Can we use a different SQL(s) to take advantage of the indexes and join the results at the end?

Text Search Engines

I'm aware that we could use an text search engine to handle that. But let's discuss what can be done using the database.

Related Links

Virtual Columns (suggested by: @Michael Kutz) : ToadWorld - Oracle Virtual Columns and Indexes
Oracle Text (suggested by: @Balazs Papp) : Oracle - Oracle Text Development Guide
Oracle Text - Check if it is installed : Oracle Forum Thread
Oracle Text - Avoid Fragmentation : Oracle - Text Index Maintenance Regime

No, forget LIKE operator for this, conditions as first_name LIKE '%name%' will never be effective. A regular index can not be used for range scans for conditions as first_name LIKE '%name%'. If first_name is indexed, index full scan is the best the database can use. And you still need to handle case-sensitity.

(index range scan can be used for conditions as first_name like 'name%', where the beginning of the value is fixed)

This is a task for Oracle Text.

No, forget virtual columns, you can not create text indexes on virtual columns, this is what you get if you try it:

DRG-11304: function-based indexes are not supported by this indextype

I have answered a similar question: Oracle “matches” multiple columns

But here it is again, with an example:

create table t1
(
  id number primary key,
  code number not null,
  first_name varchar2(100 char) not null,
  last_name varchar2(100 char) not null,
  email varchar2(50 char) not null
);

insert into t1 values (1, 111, 'Balazs', 'Papp', 'dba@stackexchange.com');
insert into t1 values (2, 222, 'linux', 'unil', 'index@stackexchange.com');
commit;

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'multi_column_datastore');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'code, first_name, last_name, email');
ctx_ddl.create_preference('t1_lexer', 'basic_lexer');
ctx_ddl.set_attribute('t1_lexer', 'mixed_case','no');
end;
/

create index i1 on t1 (first_name) indextype is ctxsys.context
  parameters ('lexer t1_lexer datastore t1_multi_column_datastore');

Then the query:

SQL> select * from t1 where contains (first_name, '%STACK%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         1        111 Balazs     Papp       dba@stackexchange.com
         2        222 linux      unil       index@stackexchange.com

SQL> select * from t1 where contains (first_name, '%11%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         1        111 Balazs     Papp       dba@stackexchange.com

SQL> select * from t1 where contains (first_name, '%Uni%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         2        222 linux      unil       index@stackexchange.com

Notice how I did not specify all the columns, still the query searched in all of them (enumerated in the multi_column_datastore). I specified first_name in the index and the query, yet the query returned records that matched on the email, code or last_name column.

Notice how I did not specify anything related to case-sensitivity in the query. It was specified with the mixed_case=no property (Oracle Text word searches are not case-insensitive by default: Case-Sensitive Searching).

And the query uses the index:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  a4vw67psmh4x3, child number 0
-------------------------------------
select * from t1 where contains (first_name, '%Uni%') > 0

Plan hash value: 1218792127

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   544 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | I1   |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("FIRST_NAME",'%Uni%')>0)


mysqli large amount of connections I run a php site with mysqli. I got 3551496 Connections, but there were only 30 processes

How can It be
What should I do about it?
The server is up for 6 month, The problem is that I keep getting too many connections error

1) How can it be?

If you check the documentation for server status variables, this number (3551496) is:

  The number of connection attempts (successful or not) to the MySQL
  server.

i.e. the number of connections (plus attempts to connect) since the server was booted!

The number of active connections can be verified by looking at the Threads_connected variable, which gives you:

  The number of currently open connections.

(which is at the bottom of your image and is 29!)

The show processlist command gives you more information about your active connections!

You need to make sure that you're using a privileged user:

  Like the output from the corresponding SHOW statement, the PROCESSLIST
  table will only show information about your own threads, unless you
  have the PROCESS privilege, in which case you will see information
  about other threads, too. As an anonymous user, you cannot see any
  rows at all.

You will find more relevant information here - the processlist table!

2) What should I do about it?

Congratulate yourself on a site which appears to be running nicely! :-)


Conversion of simple ERD to Table: is the solution below any good? Consider the following ERD:

Suppose that all entities only have two attributes: a PK plus a name/number.

I want to convert that ERD to tables.

Is it needed 4 tables (1 for each relationship plus the phone table)? Is there a better solution?

Here is my solution (4 tables, one for each relationship plus the phone table):

Relationship 1 (R1)
-----------------------------------------
ID_PhoneNumber | ID_Company | CompanyName
-----------------------------------------

Relationship 2 (R2)
-------------------------------------------
ID_PhoneNumber | ID_Employee | EmployeeName
-------------------------------------------

Relationship 3 (R3)
-------------------------------------------
ID_PhoneNumber | ID_Supplier | SupplierName
-------------------------------------------

Telephone
----------------------------
ID_PhoneNumber | PhoneNumber
----------------------------

I assumed that is not needed to add three more tables, one for each entity (Company, Employee, Supplier) since those entities are already represented by the tables of each relationship (R1, R2, R3).

I'm not sure if that decision is correct or if I'm making good use of the fundamental concepts (maybe I'm lacking some).

Looking at Relationship 1 as an example.  The primary key of this table is ID_PhoneNumber and ID_Company.  The CompanyName, however depends only on ID_Company alone.  The table is not in second normal form.  For the design to be normalised it would have to be:

Company (ID_Company, CompanyName)
Relationship1 (ID_Company, ID_PhoneNumber)

The same argument holds for Relationship 2 and 3.

Telephone has only two columns - the ID and the number.  I'd suggest quite strongly that it would be an error to have two rows in this table with the same number.  So a unique contraint on the number would be appropriate.  The ID column, which is the primary key, will have a unique constraint, too.  So we have a table where each row consists of a unique number (ID) paired with a unique number (the phone number).  It seems to me one of these is redundant.  You may choose to retain the ID as a surrogate key since an integer (the most likely data type) will be shorter than a full international telephone number, especially with formatting characters embedded.  Adopting this would give the table

Relationship1(ID_Company, PhoneNumber)

I would be inclined to use call it CompanyPhone at this point.

I know I'm mixing logical and physical arguments.  I'm assuming your intention is to implement a system and not simply derive an abstract model.

One thing I haven't covered, and isn't mentioned in your question, is whether a phone number can relate to a company or an employee or a supplier i.e. are the relationships mutually exclusive.


SQL Server Management Studio - Why is Dark Theme Disabled by Default? Why is the Dark theme disabled by default?  Are there some bugs with this theme or something along those lines?  It seems odd to me that it is forcible disabled on purpose.

I found plenty of links that I made use of to turn on the 'Dark' Theme for SSMS however I'm curious to know why this setting is turned off by default.  Why is this one single line commented out in the ssms.pkgundef file?

Here's one of many links that show how to make available the disabled Dark Theme.
How to Enable Dark Them in SSMS

Basically I'm curious if there is some sort of consequence I'm unaware of by turning this feature back on.  I can't find any links that state why it's turned off in the first place.

  Screenshot of Dark theme enabled after making changes to ssms.pkgundef

Because it is a beta feature according to this blog, and this suggestion as well as this one for 18.1.
As a workaround, you can get some custom themes from the web. A favorite of mine is Aaron Bertrand's. He explains how to do it manually (so you can roll your own) and also provides a file you can just upload. Other blogs mention this as well.

Basically I'm curious if there is some sort of consequence I'm unaware
of by turning this feature back on.

Like all BETA features, it isn't supported and thus if it breaks something else that's on you.


How to know when/if I have too many indexes? Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").

From my understanding every added index can make an SQL SELECT query faster but also an UPDATE or INSERT query slower since the indexes have to be adjusted.

What I wonder is, when do I have "too many" indexes/statistics?

Maybe there is no clear answer on this but some rule-of-thumb.

Your load profile is the most important factor in answering this question.

If your load is read-intensive, you want indexes to satisfy your heaviest or most frequent queries.
If your load is write-intensive, index carefully. Index to satisfy a seek an UPDATE needs, for example, as well as your one or two most expensive SELECTs.
If your load is an OLAP one, index sparingly since you will be scanning the target tables anyway.

How do you know you have too many indexes?

When you can see that some of them are not used by any queries.
A frequent DELETE, UPDATE or INSERT shows a query plan that involves several expensive index changes (i.e. a nonclustered index insert, update, or delete). Use your judgement to determine if the penalty on those DML statements is worth the gain you are getting from the indexes that have to be updated.


How to find out what is causing my Identity column to keep going up I have a table with an int identity as the PK. Something is causing it to increment, and I'd like to know what that is.

To give a quick example, yesterday I inserted a record and got ID 41,773. Today I inserted a new record and I got ID 44,898. There are no records with IDs between these.

There are lots of apps with access to the database, so I'm in the process of checking their logs for errors; however, I'm wondering if there is a way I can find out what queries are causing the IDENTITY increment without a record being inserted.

There are no explicit deletes on this table either, so I know its not an insert + delete combo, causing this, it has to be something failing.

The most likely scenario based on the test JNK did, is transactions that are rolling back, but still causing the seed to increment.

The best route is to trace (profile) the activity so you can catch the offending transaction(s). Here's a starting point for using Profiler:

http://msdn.microsoft.com/en-us/library/ms181091.aspx

When you're starting to learn tracing, err on the side of tracing less than you think you need. tracing is "invasive", and you can imperil a DB instance if you go after too much info.

In this case you're looking for transaction based info. Feel free to post back if you have specific questions while setting up your trace. Note that you'll probably want to log to files unless you can leave the GUI up overnight. Logging to files is preferred overall unless you're trying to catch something in real time.


File permissions for SQL Server 2014 virtual account I have two SQL Server 2014 instances installed on the same server - one for production, and one for development.  I have set up a SQL Agent job that identifies the most recent backup from the production instance and restores it on the development instance.  When I run the job, it gives me an error like this (names altered to protect the innocent):

  Executed as user: NT SERVICE\SQLAgent$MyAppDEV.
  Cannot open backup device 'E:...MyApp_backup_2016_02_24_092912_0136837.bak'.
  Operating system error 5(Access is denied.).
  [SQLSTATE 42000] (Error 3201)
  RESTORE DATABASE is terminating abnormally.
  [SQLSTATE 42000] (Error 3013).
  The step failed.

I read a little about the virtual accounts that SQL Agent runs under.  If I open the folder in question and give read permissions to NT Service\SQLAgent$MyAppDEV, I still get the same error.  But if I give read access to the Authenticated Users built-in account, everything works.

What gives?  The error message purports to tell me what security context the job is using, but that doesn't seem to match up with reality.

Dan,

I'm assuming the account running the agent job is a sysadmin. In this case, the
DEV instance will need to have its' service account or service sid for the DATABASE ENGINE given access to the folder.

SQL Server agent doesn't do the restore, it connects to SQL Server and impersonates the account if non sysadmin and then continues. In this case you're connecting to the database engine and issuing a RESTORE DATABASE command which will be processed by the engine. SQL Agent isn't touching the backups, the engine is.

Edit: Explaining the posted agent output

Executed as user: NT SERVICE\SQLAgent$MyAppDEV.

This is letting you know that the Agent job was executed as the SQLAgent$MyAppDEV local account. This is most likely due to the job owner being sysadmin.

Cannot open backup device 'E:...MyApp_backup_2016_02_24_092912_0136837.bak'.

Here is the actual error, we can't open the file. We don't know why yet.

Operating system error 5(Access is denied.).

This is explaining why we can't open the file, the OS returned error 5. This can be validated by checking the OS return code by executing net helpmsg 5 on a command prompt.

[SQLSTATE 42000] (Error 3201)
RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013).
The step failed.

Some extra notes about what is happening, but this isn't relevant to the actual error or issue.


Import Database from Oracle .DBF files Recently my Oracle Installation has been crashed and I reinstalled the Oracle again. I have my old Oracle Data files (.DBF files) which have been recovered from old installation.

Now, I need to restore or reinstate these .DBF files into my new installation, so that I can get all my data and procedures.

Both Old and New Oracle Installation version: Oracle 12c

According to your description, the specific answer depends upon what and how the original backup was done. If it was a cold backup of OS file copy after the database was cleanly shutdown then you can restore the database BUT make sure you also have control and redo log files copied together with the DBF files. First copy all the complete and correct files to the target system in the exacly same location as these were in the original database before. Make sure that the version of the database is same. Then you can simply open the database normally.

Here is a similar post regarding the issue that you have mentioned here.
Mounting a database from database files copied from a previous installation

Cold Clonning Using controlfile backup in oracle 11g


ms sql query multiple table colums as identical tag (sequel) Extending this original question

I am using SQL Server Express 2017

Pretending this is my source table:

+-------------+---------------------------+
| ID | Name   | Age  | HairColor | HasCar |
+-------------+---------------------------+
| 1  | Bert   | 25   | blonde    | 1      |
| 2  | Agnes  | 61   | white     | 0      |
| 3  | Diane  | 43   | brunette  | 1      |
+-------------+---------------------------+

How can get a result looking like this?

<MyRootElement>
    <MyGroupingTag Group="1">
      <MyTag Value="1"      />
      <MyTag Value="Bert"   />
      <MyTag Value="25"     />
      <MyTag Value="blonde" />
      <MyTag Value="1"      />
    </MyGroupingTag>
    <MyGroupingTag Group="2">
      <MyTag Value="2"        />
      <MyTag Value="Agnes"    />
      <MyTag Value="61"       />
      <MyTag Value="white"    />
      <MyTag Value="0"        />
    </MyGroupingTag>
    <MyGroupingTag Group="3">
      <MyTag Value="3"         />
      <MyTag Value="Diane"     />
      <MyTag Value="43"        />
      <MyTag Value="brunette"  />
      <MyTag Value="1"         />
    </MyGroupingTag>
</MyRootElement>

Because of the Group attribute in MyGroupingTag I can't use something like

SELECT... FOR XML PATH('MyTag'), ROOT('MyGroupingTag')

The Group itself is a simple row count.

That is a bit of odd looking XML you want to produce. You are relying on element position to distinguish what value is what.

declare @T table
(
  ID  int,
  Name varchar(10),
  Age int,
  HairColor varchar(10),
  HasCar bit
);

insert into @T(ID, Name, Age, HairColor, HasCar) values
(1, 'Bert' , 25, 'blonde'  , 1),
(2, 'Agnes', 61, 'white'   , 0),
(3, 'Diane', 43, 'brunette', 1);

select row_number() over(order by T.ID) as '@Group',
       T.ID as 'MyTag/@Value',
       null,
       T.Name as 'MyTag/@Value',
       null,
       T.Age as 'MyTag/@Value',
       null,
       T.HairColor as 'MyTag/@Value',
       null,
       T.HasCar as 'MyTag/@Value'
from @T as T
for xml path('MyGroupingTag'), root('MyRootElement');

This might generate a better version of your XML.

select row_number() over(order by T.ID) as '@Group',
       'ID' as 'MyTag/@Name',
       T.ID as 'MyTag/@Value',
       null,
       'Name' as 'MyTag/@Name',
       T.Name as 'MyTag/@Value',
       null,
       'Age' as 'MyTag/@Name',
       T.Age as 'MyTag/@Value',
       null,
       'HairColor' as 'MyTag/@Name',
       T.HairColor as 'MyTag/@Value',
       null,
       'HasCar' as 'MyTag/@Name',
       T.HasCar as 'MyTag/@Value'
from @T as T
for xml path('MyGroupingTag'), root('MyRootElement');

Result:

<MyRootElement>
  <MyGroupingTag Group="1">
    <MyTag Name="ID" Value="1" />
    <MyTag Name="Name" Value="Bert" />
    <MyTag Name="Age" Value="25" />
    <MyTag Name="HairColor" Value="blonde" />
    <MyTag Name="HasCar" Value="1" />
  </MyGroupingTag>
  <MyGroupingTag Group="2">
    <MyTag Name="ID" Value="2" />
    <MyTag Name="Name" Value="Agnes" />
    <MyTag Name="Age" Value="61" />
    <MyTag Name="HairColor" Value="white" />
    <MyTag Name="HasCar" Value="0" />
  </MyGroupingTag>
  <MyGroupingTag Group="3">
    <MyTag Name="ID" Value="3" />
    <MyTag Name="Name" Value="Diane" />
    <MyTag Name="Age" Value="43" />
    <MyTag Name="HairColor" Value="brunette" />
    <MyTag Name="HasCar" Value="1" />
  </MyGroupingTag>
</MyRootElement>

The null between columns in the column list is there to separate MyTag into different elements.


Why write ahead logs in PostgreSQL are generated every second PostgreSQL version 9.2 generates write ahead log (WAL) every second i.e. 60 WALs are generated in one minute.

Is it possible to make them occur less frequently?

Is it possible to increase the WAL file size, so that fewer WALs will be generated in one minute?

Start with the manual page on Write Ahead Log

  wal_writer_delay (integer)
  Specifies the delay between activity rounds for the WAL writer. In each round the writer will flush WAL to disk. It then sleeps for wal_writer_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting wal_writer_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in the postgresql.conf file or on the server command line.

Other options on that page can also have an effect. Also consider that many small writes can easily be maintained but one big write can cause delays and increase the data lost if the power goes out.


how to increase column values by 20 percent using mysql How do i increase the values in a column with percentage increment using MySQL,  for example:

ID      price
1       500
2       800
3       450

How do i increase the values in price column by 20%  using one sql query (MySQL)?

Simply calculate 20% of actual price:

update your_table
set price = price + (price * 20.0 / 100.0)
where id = (some condition)

or a shorter way:

update your_table
set price = price * 1.2
where id = (some condition)


Can I avoid a join for my select procedures? Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory):

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT `first_name`, `last_name`, `hashID` FROM MYDB.person;

  SELECT`id`, `id_status`, `criminal_status`, `hashID` FROM MYDB.id_crimes;

  SELECT `stateID`, `stateID_status`, `dob`, `hashID` FROM MYDB.dob_state;

  SELECT `create_time`, `update_time`, `hashID` FROM MYDB.timestamps;

  COMMIT;
end//
delimiter ;

What I'm looking for is the absolutely most concise way to select a record in its entirety, keeping it as a procedure. I'd really like to avoid joins on the tables, but I don't really see any way around doing a join on the hashID, given the abovementioned restrictions. Is there another way? Pointers? What would that look like in a procedure? I'd like to see someone's concise correction of what I want to do. :)
Thanks!

You can use variables:

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT @fn:=`first_name`, @ln:=`last_name`, @hid:=`hashID` FROM MYDB.person WHERE <condition>;

  SELECT @id:=`id`, @s:=`id_status`, @cs:=`criminal_status` FROM MYDB.id_crimes WHERE `hashID` = @hid;

  SELECT @sid:=`stateID`, @sids:=`stateID_status`, @dob:=`dob` FROM MYDB.dob_state WHERE `hashID` = @hid;

  SELECT @ct:=`create_time`, @ut:=`update_time` FROM MYDB.timestamps WHERE `hashID` = @hid;

  COMMIT;

  SELECT @fn AS `first_name`, @ln AS `last_name`, @hid AS `hashID`,
         @id AS `id`, @s AS `id_status`, @cs AS `criminal_status`,
         @sid AS `stateID`, @sids AS `stateID_status`, @dob AS `dob`,
         @ct AS `create_time`, @ut AS `update_time`;
end//
delimiter ;

Edit:

You can also use SELECT <columns,...> INTO <variables,...>:

You can use variables:

delimiter //
CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime,  _hashid int,
  _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1))
begin
  START TRANSACTION;

  SELECT `first_name`, `last_name`, `hashID` INTO @fn, @ln, @hid FROM MYDB.person WHERE <condition>;

  SELECT `id`, `id_status`, `criminal_status` INTO @id, @s, @cs FROM MYDB.id_crimes WHERE `hashID` = @hid;

  SELECT `stateID`, `stateID_status`, `dob` INTO @sid, @sids, @dob FROM MYDB.dob_state WHERE `hashID` = @hid;

  SELECT `create_time`, `update_time` INTO @ct, @ut FROM MYDB.timestamps WHERE `hashID` = @hid;

  COMMIT;

  SELECT @fn AS `first_name`, @ln AS `last_name`, @hid AS `hashID`,
         @id AS `id`, @s AS `id_status`, @cs AS `criminal_status`,
         @sid AS `stateID`, @sids AS `stateID_status`, @dob AS `dob`,
         @ct AS `create_time`, @ut AS `update_time`;
end//
delimiter ;


Select children from hierarchy (/a/b/c/d/) I have to select from database which I did not create and I have this problem:

example schema:

Unit
----
id
HierarchyPath (example: '/42/48/63/84/')
UnitName (example1: "John" example2: "Team1")
typeId

UnitType
--------
id
Name (example1: "Name", example2: "TeamName")

I need to have result like

Team1, John
Team1, Joe
Team1, Johana
Team2, Bob
Team2, Bobek
Team2, BobaFett

I tried join tables like

UnitName_Teams.HierarchyPath = left(4,UnitName_Players.HierarchyPath)

but it crash on error

'Conversion failed when converting the varchar value '/42/48/63/' to data type int.'

How can I get children in this hierarchyPath 'data type' ?
I can select players from one team with for example

Like '/42/%'

But how to do it for all Teams ?!

You need to 'walk the tree'.  This can be done using a recursive CTE, something like this:

USE tempdb
GO

CREATE TABLE dbo.Unit (
    id INT IDENTITY PRIMARY KEY,
    unitName        VARCHAR(20),
    hierarchyPath   hierarchyId UNIQUE,
    typeId          INT
    )
GO

CREATE TABLE dbo.UnitType (
    id      INT PRIMARY KEY,
    name    VARCHAR(20)
    )
GO

INSERT INTO dbo.Unit ( hierarchyPath, unitName, typeId )
VALUES
    ( '/42/', 'Team1', 1 ),
    ( '/42/1/', 'John', 2 ),
    ( '/42/2/', 'Joe', 2 ),
    ( '/42/3/', 'Johana', 2 ),

    ( '/43/', 'Team2', 1 ),
    ( '/43/1/', 'Bob', 2 ),
    ( '/43/2/', 'Bobek', 2 ),
    ( '/43/3/', 'wBobaFett', 2 )

INSERT INTO dbo.UnitType ( Id, name )
VALUES ( 1, 'Team' ), ( 2, 'Name' )
GO

--SELECT *, hierarchyPath.ToString() hierarchyPath_string
--FROM dbo.Unit

;WITH cte AS
(
SELECT 1 xlevel, Id, hierarchyPath, unitName AS teamName, CAST( '' AS VARCHAR(20) ) unitName
FROM dbo.Unit
WHERE typeId = ( SELECT Id FROM dbo.UnitType WHERE name = 'Team' )

UNION ALL

SELECT xlevel + 1, u.Id, u.hierarchyPath, c.teamName, u.unitName
FROM cte c
    INNER JOIN dbo.Unit u ON u.hierarchyPath.GetAncestor (1) = c.hierarchyPath

)
SELECT teamName, unitName
FROM cte
WHERE xlevel > 1
ORDER BY teamName, unitName
GO

DROP TABLE dbo.Unit
DROP TABLE dbo.UnitType
GO

HTH


How to create index on huge oracle table? I tried it with below SQL.

CREATE INDEX idx ON table1 (column1, column2) Local parallel 10;

but its giving me below error

SQL Error: ORA-12801: error signaled in parallel query server ..,
instance .. ORA-27090: Unable to reserve kernel resources for
asynchronous disk I/O Additional information: 3 Additional
information: 128 Additional information: -1374056344
12801. 00000 -  "error signaled in parallel query server %s"
*Cause:    A parallel query server reached an exception condition.
*Action:   Check the following error message for the cause, and consult
           your error manual for the appropriate action.
*Comment:  This error can be turned off with event 10397, in which
           case the server's actual error is signaled instead.
Elapsed: 00:16:10.821

where table1 is a partitioned table.

You can try to build the index individually for each partition:

BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 MODIFY PARTITION '||aPart.PARTITION_NAME||' INDEXING OFF';
    END LOOP;
END;
/

CREATE INDEX TABLE1 ON idx (column1, column2) LOCAL INDEXING PARTIAL;

BEGIN
    FOR aPart IN (SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE1') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX idx REBUILD PARTITION '||aPart .PARTITION_NAME||' PARALLEL 10';
    END LOOP;
END;
/

-- Enable indexing, otherwise new partitions will not get indexed.
ALTER TABLE TABLE1 MODIFY DEFAULT ATTRIBUTES INDEXING ON;


How to estimate needed undo retention for a operation? Recently I needed to create a new table with CTAS. But after 4 hours of work I've received the erro snapshot too old
How can I estimate the needed undo retention for a operation?

There are a number of things you can do to ensure successful completion:

Optimize your query. Use DBMS_XPLAN to display your query execution plan, then work to eliminate things that could slow it down, like full table scans, by having proper indexes on join columns. See this article, this article and this article for details on getting started.

Make sure you have accurate statistics gathered on your source tables. This whitepaper from Oracle explains optimizer stats in detail.

If your CTAS query is failing because there have been too many changes to the underlying data, then consider running the command only when there are no competing workloads on the database.

If everything else fails, look at changing Undo settings using the retention guarantee and the Undo Advisor as described here.


Joining a function and a view I have a function get_sa001 and a view Axis_RefCustomer.

When I execute my function get_sa001 on it's own for a certain period let's say 2016 - 2017, the execution time is about ~6 seconds.

SELECT d."Selling_date",  d."Value_in_EUR", d."Value_in_currency", d."Site"
FROM report.get_sa001('2016-01-01'::date, '2017-03-31'::date, 32) AS d

When I execute a select on the view Axis_RefCustomer, it runs for around ~1 second.

Select a."Selling_currency" FROM report."Axis_RefCustomer" AS a

When I join them together, the execution time is around ~39 seconds !

SELECT d."Selling_date",
a."Selling_currency",
d."Value_in_EUR",
d."Value_in_currency",
d."Site"
FROM report.get_sa001('2016-01-01'::date, '2017-03-31'::date, 32) AS d
LEFT JOIN report."Axis_RefCustomer"
AS a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code"

Is there anyway to reduce the amount of time my query takes to execute ?

What my function does : My function collects data from the dates I put in as parameters and associates them with a view and it sends me back a table of which I use on a 3rd party app.

Here's my explain analyze for 2016 - 2017 of the function on it's own :

Function Scan on get_sa001 d  (cost=0.25..10.25 rows=1000 width=104) (actual time=2522.959..2534.987 rows=53446 loops=1)
Total runtime: 2537.926 ms

Here's my explain analyze for the view on it's own :

Hash Right Join  (cost=3527.82..5840.32 rows=74513 width=4) (actual time=47.363..71.317 rows=77965 loops=1)
    Hash Cond: ((("T12_RefCustomer"."Site")::text = ("T01_References"."Site")::text) AND (("T12_RefCustomer"."Internal_reference")::text = ("T01_References"."Internal_reference")::text))
    ->  Seq Scan on "T12_RefCustomer"  (cost=0.00..348.81 rows=13181 width=29) (actual time=0.002..2.350 rows=13182 loops=1)
    ->  Hash  (cost=1973.13..1973.13 rows=74513 width=22) (actual time=46.591..46.591 rows=74513 loops=1)
            Buckets: 2048  Batches: 4  Memory Usage: 1019kB
            ->  Seq Scan on "T01_References"  (cost=0.00..1973.13 rows=74513 width=22) (actual time=0.014..18.580 rows=74513 loops=1)
    Total runtime: 72.540 ms

Here's my explain analyze for the query using a left join :

Nested Loop Left Join  (cost=1.23..2375.17 rows=1000 width=108) (actual time=2406.131..42314.297 rows=53446 loops=1)
  ->  Function Scan on get_sa001 d  (cost=0.25..10.25 rows=1000 width=168) (actual time=2405.980..2429.250 rows=53446 loops=1)
  ->  Nested Loop  (cost=0.98..2.35 rows=1 width=28) (actual time=0.547..0.746 rows=1 loops=53446)
        Join Filter: (((d."Site")::text = ("T01_References"."Site")::text) AND ((d."Internal_reference")::text = ("T01_References"."Internal_reference")::text) AND (("T02_Customers"."Site")::text = ("T01_References"."Site")::text))
        Rows Removed by Join Filter: 126"
        ->  Nested Loop  (cost=0.57..1.42 rows=1 width=33) (actual time=0.011..0.057 rows=127 loops=53446)
              ->  Index Scan using "T02_Customers_pkey" on "T02_Customers"  (cost=0.28..0.77 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=53446)
                    Index Cond: ((d."Customer_code")::text = ("Customer_code")::text)
              ->  Index Scan using "T12_RefCustomer_pkey" on "T12_RefCustomer"  (cost=0.29..0.64 rows=1 width=29) (actual time=0.007..0.027 rows=99 loops=68927)
                    Index Cond: ((("Customer_code")::text = ("T02_Customers"."Customer_code")::text) AND (("Site")::text = ("T02_Customers"."Site")::text))
        ->  Index Scan using "T01_References_pkey" on "T01_References"  (cost=0.42..0.92 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=6795220)
              Index Cond: ((("Internal_reference")::text = ("T12_RefCustomer"."Internal_reference")::text) AND (("Site")::text = ("T12_RefCustomer"."Site")::text))
Total runtime: 42318.196 ms

What I've done :
I've Vacuumed my tables and reindexed them but the results have not changed.

Have you tried using CTEs? I've found several times that reducing the size of my data sets before attempting the joins can lead to a big improvement in query times.

with
    __d as(
        select
            "Selling_date",
            "Value_in_EUR",
            "Value_in_currency",
            "Site",
            "Internal_reference",
            "Customer_code"
        from
            report.get_sa001('2016-01-01'::date, '2017-03-31'::date, 32)
    ),
    __a as(
        select
            "Selling_currency",
            "Site",
            "Reference_internal" as "Internal_reference",
            "Customer_code"
        from
            report."Axis_RefCustomer"
        where
            ("Site", "Reference_internal", "Customer_code") in(
                select
                    "Site",
                    "Internal_reference",
                    "Customer_code"
                from
                    __d
            )
    )
select
    __d."Selling_date",
    __a."Selling_currency",
    __d."Value_in_EUR",
    __d."Value_in_currency",
    __d."Site"
from
    __d
    left join __a using("Site", "Internal_reference", "Customer_code")


Split Multiple Columns Into Multiple Columns by Delimiter I would like to split multiple columns into multiple columns by a delimiter. I am using SQLite, but I am open to importing into another DBMS, if necessary.

An example table, showing how the table currently is, where “;” is the delimiter:

http://sqlfiddle.com/#!5/f1845

An example table, showing how I’d like the updated table to look:

http://sqlfiddle.com/#!5/d6283f

In SQLite, you have to do it the hard way with the instr() and  substr() functions:

SELECT before_semicolon,
       substr(rest, 1, pos - 1) AS after_semicolon1,
       substr(rest, pos + 1)    AS after_semicolon2
FROM (SELECT before_semicolon,
             rest,
             instr(rest || ';', ';') AS pos
      FROM (SELECT substr(data1, 1, pos - 1) AS before_semicolon,
                   substr(data1, pos + 1)    AS rest
            FROM (SELECT data1, instr(data1 || ';', ';') AS pos
                  FROM example)));

It might be a better idea to create a user-defined function like Postgres's split_part().


Update statistics for all databases all tables in a server Hi I have been working on a script to update statistics of all tables on all my DBs. The idea is to parametrise it later on, but just as a quick fix, and not wanting to implement Ola's scripts today, follow the script below.

I have tested it on a few servers, but before I schedule it to run on a live server Sunday morning, would like to get some ideas and share it with you.

SET NOCOUNT ON

DECLARE @DBS TABLE (I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                    DBNAME SYSNAME NOT NULL )

DECLARE @I INT
        ,@Z INT
        ,@SQL VARCHAR(1008)
        ,@j INT
        ,@Y INT
        ,@MYDB SYSNAME

INSERT INTO @DBS
SELECT s.name
FROM sys.databases s
INNER JOIN SYS.master_files F ON S.DATABASE_ID = F.database_id  AND F.data_space_id = 1
WHERE S.STATE = 0 -- online
  AND S.database_id > 3 -- exclude master, tempdb and model -- I left msdb
  AND S.is_read_only = 0 -- read/write
  AND S.user_access = 0 -- multi_user
  AND S.NAME NOT IN ('TableBackups',
                     'Troubleshooting')
ORDER BY F.SIZE DESC

SELECT @Z = @@ROWCOUNT
SELECT @I = 1

WHILE @I <= @z BEGIN

    BEGIN TRY DROP TABLE #T END TRY BEGIN CATCH END CATCH
    CREATE TABLE #T( I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, MYSQL VARCHAR(1008) NOT NULL )

    SELECT @MYDB = QUOTENAME (S.DBNAME )
           ,@SQL = 'USE ' + QUOTENAME (S.DBNAME ) + ';' + CHAR(13) + '
                    ' + CHAR(13) +
                   'INSERT INTO #T(MYSQL)  SELECT ''UPDATE STATISTICS '' + QUOTENAME(NAME) FROM SYS.TABLES WHERE TYPE = ''U'' ' + CHAR(13)

      FROM @DBS s
        WHERE s.I = @I

    PRINT CAST (@SQL AS NTEXT)
       BEGIN TRY

            EXEC (@SQL)
            SELECT @Y = @@ROWCOUNT

       END TRY
       BEGIN CATCH
           SELECT @Y = 0
       END CATCH

    SELECT @J = 1

    WHILE @J <= @y BEGIN

       SELECT @SQL = 'USE ' + @MYDB + ';' + CHAR(13) + MYSQL
       FROM #T
       WHERE I= @J

       BEGIN TRY

            EXEC (@SQL)

       END TRY
       BEGIN CATCH
       END CATCH

       PRINT CAST (@SQL AS NTEXT)

       SELECT @J = @J + 1

    END

    SELECT @I = @I +1

END /*WHILE*/

Right off the bat, your script completely ignores schema, so it will fail unless all of your production servers only use dbo explicitly as the namespace for all tables (and nobody has a default schema other than dbo). You're also using a deprecated data type (NTEXT) for seemingly no reason. Also I don't think the WHERE clause against sys.tables is necessary (I don't know how anything but a U could ever be in there). Not sure why you bother ordering by file size; the job isn't going to finish faster if you handle the biggest databases first (also the join could return multiple rows for a database if it has more than one file in data_space_id = 1, which means you would potentially process that database multiple times).

Finally, I have to wonder if it is really wise to blindly update stats on every table in every database across your entire instance, regardless of size... and why you don't bother checking for indexed views, which also require maintenance. You could save quite a bit of work if you add some logic to exclude any tables that don't need it.

My partial solution

This code will be slightly harder to maintain, doesn't have the TRY/CATCH wrappers yours does, and doesn't add any additional logic about which tables to actually pursue, but since I get a kick out of making automation tasks out of dynamic SQL (and really enjoy solutions that cause no horizontal or vertical scrollbars on this site), here you go. When you're happy with the output, comment the print and uncomment the exec.

DECLARE @sql NVARCHAR(MAX) = N'', @stats NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @stats;'
  FROM sys.databases
  WHERE [state] = 0 AND user_access = 0; -- and your other filters

SET @stats = N'DECLARE @inner NVARCHAR(MAX) = N'''';
  SELECT @inner += CHAR(10) + N''UPDATE STATISTICS ''
    + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + '';''
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id];
  PRINT CHAR(10) + DB_NAME() + CHAR(10) + @inner;
  --EXEC sys.sp_executesql @inner;'

EXEC [master].sys.sp_executesql @sql, N'@stats NVARCHAR(MAX)', @stats;


Postgres Can't Add Foreign Key Constraint I have a table with about 220 million records :( and I need to add a foreign key constraint.

My command looks something like this:

ALTER TABLE events
ADD CONSTRAINT events_visitor_id_fkey
FOREIGN KEY (visitor_id)
REFERENCES visitors(id)
ON DELETE CASCADE;

It's been running for probably an hour now.

I ran this before hand:

set maintenance_work_mem='1GB';

What's the fastest way to do this, and about how long should it take. The table it references is only 25 million.

I'm running it on an RDS instance of db.r3.large (15 GB of RAM).

EDIT:

Just cancelled the command and got this:

ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "SELECT fk."visitor_id" FROM ONLY "public"."events" fk LEFT OUTER JOIN ONLY "public"."visitors" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."visitor_id") WHERE pk."id" IS NULL AND (fk."visitor_id" IS NOT NULL)"

about how long should it take

It depends on the amount of data, your indexes, and the speed of your IO sub-system (the latter particularly if supporting indexes aren't in place).

As you already have 220 million rows in the child table, it must check each of them against the parent table to make sure they are all valid values. Without a useful index on events.visitor this will mean scanning the whole table.

Note that creating a foreign key does not automatically create a supporting index, because depending on your access patterns such an index may not be needed so would be a waste of space.

  What's the fastest way to do this,

You can tell the DB not to validate the foreign key constraint for existing rows by adding NOT VALID (see the documentation at https://www.postgresql.org/docs/current/sql-altertable.html for the details and caveats associated with this). That is the fastest way but can lead to you having rows that are invalid that you do not know about until they cause a problem.
(NOT VALID is postgres specific syntax, other DBs that offer the option may name it differently, for instance with MS SQL Server the equivalent is WITH NOCHECK)

The second fastest method is to make sure there is an index on that column before defining the foreign key, though creating that index will itself take a notable amount of time on a table of that many rows.

  I tried again and it ended up completing in around half an hour. Not sure what was going on before.

It may be that at the point of the later attempt much of the table was in memory after being read during the earlier aborted create, so scanning to validate existing rows was faster second time around due to fewer disk reads being needed.


The number of records that is the table shows in .dbf file is not match with the real number of records I have some .dbf files made in VFP. I have to work with them and occasionally edit it. Recently I noticed that one of .dbf`s is running in a wrong way. The number of records that is the table shows is not match with the real number of records. I have tried to modify the header of this table to reflect an estimated number of records but with no result. How can I get my origin files back?

Use a hexidecimal editor and open a .dbf file in it. This tool is located at tools\hexedit\hexedit.app.

The header bytes that contain the record count are located in columns 4, 5, 6, and 7 of address 00000000 (topmost row).
Go to the address and then change the data in this column to double zero.
Next, open this file in VFP as a table and note that the record count is zero.

I have found this thread for you https://getsatisfaction.com/impathic/topics/corrupt-dbf-files
where a similar issue is discussed.


Partitioning vs Indexes I'm very new to databases, so I apologize if my question is very basic...

Anyways, I'm creating a table with what seems like alot of data (500 million rows right now, and potentially twice as much in the future).  Now, I need to have a way to access the data in this table quickly, so I'm researching partitions and indexes.  However, I'm getting confused on when I'd want to create a partition vs. an index.  I have three columns that seem like reasonable candidates for partitioning or indexing:

Time (day or week, data spans a 4 month period)
Customer "Token": The last two digits of a user's ID
Client ID (typically will have 5-20 clients in the dataset)

When running future selects against this table, it's likely that I'll be filtering on client id as well as wanting to do some sampling (which I would like to do by the "Token" variable).  I may occasionally be filtering by the time variable as well.

So, my question is this: how should I organize my table?  Should I partition by Client and Token, and then create an index on time?  Or just partition on client and create indices on time and token?  And, more importantly, what is the logic behind the strategy you recommend?

Also, after I've created the table, will the indexes break if I add more data into it (particularly new clients for the same date/token range)?  Is recreating an index relatively simple?

Thanks so much for your help, and please let me know if you need any more information from me.

To put it simply, indexes allow fast access to small proportions of a table. This is because they access data that is scattered throughout many block in the data segment, so unless the rows you are looking for are clustered into a small number of blocks the total cost of accessing all of those single blocks will soon become greater than just scanning a table.

At absolute best you might get benefits from an index if you are accessing 20% of a table's rows, but it's more likely that 1-5% is an effective limit.

If you want to access a greater proportion effectively, say 10% of the rows, then if you can use a partitioning scheme to isolate "queryable" groups into partitions then they can be queried very quickly. Even if you are accessing 1% of a table's rows then if the query can retrieve them from a partition that holds just those rows with a full partition scan then that will be quicker than accessing them through an index -- around 1/100th of the time to perform a full table scan (ignoring parallel query).

So if your queries very often include a predicate on the client id then partition on that -- list partitioning, I would suggest. If you also query on ranges of dates, then consider range partitioning on the time column.

So you could composite partition by range-list, or by list-range. If you want to drop old data easily then range-list could be better, but I'm not sure that there's a lot to choose between them.

The token sounds like it could be a good candidate for an index. Indexes are self-maintaining, and modifying data will not invalidate them. Obviously there's an overhead to maintaining them, but data is generally queried many time more often than it is modified so on balance I tend to "index first, ask questions later".


Does a read-only MySQL slave need log-bin in addition to relay-bin logs? I'm running out of disk space on a newly initiated MySQL slave server. The bulk of disk space is in /var/log/mysql where I see both mysql-bin.000XXX and mysql-relay-bin.000XXX logs, each 1.1G.

Does a traditional read-only MySQL slave need both of these? mysql-bin.000XXX goes all the way back to 000001 which leads me to believe these aren't used.

Here's the relevant information from my my.cnf:

## Slave my.cnf
binlog_format                   = mixed
log_bin                         = /var/log/mysql/mysql-bin.log
sync_binlog                     = 1
pid_file                        = /var/run/mysqld/mysqld.pid
log_error                       = /var/log/mysql/error.log
general_log                     = 0
general_log_file                = /var/log/mysql/general.log
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 3

server_id                       = 2

relay-log                       = /var/log/mysql/mysql-relay-bin.log
replicate-wild-do-table         = my_table_prefix_%.%
log_slave_updates               = 1
read_only                       = 1

Here's a ls -lah of my log directory:

-rw-r-----  1 mysql mysql 1.1G Mar 29 19:48 mysql-bin.000001
-rw-r-----  1 mysql mysql 1.1G Mar 29 19:55 mysql-bin.000002
-rw-r-----  1 mysql mysql 1.1G Mar 29 20:03 mysql-bin.000003
...
-rw-r-----  1 mysql mysql 1.1G Mar 29 20:11 mysql-bin.000286
...
-rw-r-----  1 mysql mysql  254 Mar 31 12:27 mysql-relay-bin.000015
-rw-r-----  1 mysql mysql 1.1G Mar 31 12:27 mysql-relay-bin.000016
-rw-r-----  1 mysql mysql  326 Mar 31 12:27 mysql-relay-bin.000017
...
-rw-r-----  1 mysql mysql 1.1G Mar 31 12:27 mysql-relay-bin.000769

Slave is slowly catching up to master and purging mysql-relay-bin files appropriately. But it looks like mysql-bin files will stack up forever.

And slave status:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000291
          Read_Master_Log_Pos: 15703910
               Relay_Log_File: mysql-relay-bin.000016
                Relay_Log_Pos: 662979789
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: my_table_prefix_%.%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 662979592
              Relay_Log_Space: 271579588573
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 729594
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 100
                  Master_UUID: 5e12-f712611e5-b2f1-f206553c91-3b124
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

You are right. Traditionally, replication slaves need binary logs enabled unless you plan to failover to it someday.

The only reasons for using binary logs on a slave would be

Setting you circular replication (Makes a Slave a Master at the same time)
You want to promote the Slave to a Master
Using it as a Distribution Master (All Binary Logs,No Data, See my old post Is it Possible to Recover Data from a BLACKHOLE table? that describes why one would use such a setup)

As for your actual situation, here are two suggestions (pick one)

SUGGESTION #1

First, login to mysql as root@localhost on that slave and run

mysql> STOP SLAVE;
mysql> RESET MASTER;

Next, comment out this line from my.cnf

#log_bin                         = /var/log/mysql/mysql-bin.log
#log_slave_updates               = 1

Next, restart mysql

service mysql restart

Finally, manually delete those logs

cd /var/log/mysql
rm -f mysql-bin.*

SUGGESTION #2

If you do not want to restart mysql, do this

First, login to mysql as `root@localhost on that slave and run

mysql> SET GLOBAL expire_logs_days = 1;
mysql> RESET MASTER;

Then, add this line to my.cnf

expire_logs_days = 1

That way, the slave would still pile up binary logs, but would auto delete binary logs older than 24 hours.

GIVE IT A TRY !!!


How to insert data from a local MSSQL db table to a remote MSSQL db table using WCF application coded in .NET I made a WPF application using VS2013, coded in .NET. It is working fine in my local SQL Server. But I want to use it to upload data from a local database to a remote database. How can I do this?

I have three buttons (UPLOAD, DOWNLOAD, SYNC), and three stored procedures in SQL Server.

Upload will run insert into Database123.table, select * from DATABASE456.table
Download will run insert into database456.table2, select * from database123.table2
sync will run delete from databse456.table1 where id in database456.table2

But i want to use a remote database in place of database123. How can I do this?

I can see four ways you could accomplish this:

1. Linked servers

You can establish a linked server on both the local and remote SQL Server, which will allow you to reference tables not only on the same machine, but also on the remote machine, like so:

INSERT INTO some_server.some_database.some_schema.some_table
SELECT * FROM local_database.local_schema.local_table;

You might run into trouble with your database administrators - some are perfectly fine with linked servers, others not. Also, there's a bit of work to making authentication over linked servers work without introducing security risks.

2. Replace the stored procedures with application logic

Create two connections in your application, one to the source database, the other one to the target database. Then, for each row you read on the source connection, you insert/delete/perform some action on the target connection.

The downside with this approach is that you can run into performance issues if you work with large amounts of data, or if you're not careful with your database design and indexing.

3. ETL tools

There are a range of data tools out there, including SSIS and Pentaho, that allow you to create data transfer/synchronization processes in a graphical interface in Visual Studio. SSIS is included with SQL Server.

Obviously, there's a considerable learning curve to mastering these tools.

4. SQL Server replication

For bonus points, see if setting up replication between the two SQL Servers could solve your problem, or ask a database administrator for help with this.


Marking a row as removed or deleted: Is it a good design practice? This is a theoretical* design question about marking rows as removed or deleted. I assume rows should be marked as removed whenever there is a need to remove the pertinent data, but the row should stay in the relevant database table to maintain referential integrity.
* Theoretical, within the scope of this question, means that it should not be related to concrete example and also not related to concrete relational database.
For the sake of the question I have chosen a classical example: A Shop's database. And a situation is: shop's user wants to quit using the shop and wants his personal data purged.
We have an Orders table which references a Users table. If we try to DELETE a user row, we will lose all referenced orders (assuming we have strong referencing). Therefore instead of a DELETion of a user row we may mark such row as 'removed' (i.e., have a BIT (or boolean) column SET to '1', e.g., IsRemoved = 1) and clear his login, password, first name, last name columns to purge personal data. This is a first approach, which I believe is bad. While we get rid of personal data, the row still occupies space in database - just for the sake of referential integrity: no other useful meaning it has.
But there is an alternative, instead of having an Orders->Users relationship, we can have a different structure: Orders->Customers->Users, where the Customers table has no personal data. When a user wants to quit, we simply DELETE the pertinent User row, but Customer along with its related Orders stay stored in the database. No need to mark row as removed, no need for additional business rules to handle the removed rows behavior. I believe this approach is better and may be used for any situation where the IsRemoved row approach is used.
Question
Do you think second approach lacks real-life application in some situations? Or can it be used for any DB design?
P.S. Of course, it is possible to referential integrity with NULLs in FOREIGN KEYs, however this approach is out of scope. While it may be useful in the Shop's example, the question is hypothetical, and shop is used only as example, not as a real problem (which may be solved with NULLs).

The 2nd approach is interesting, but it seems like you could be optimizing the model for a scenario that is not that frequent (introducing a third table to be joined whenever you need to access the user data).

Also,  your description of the first approach seem to imply that the user table has no other info,  except the one that you need to purge, and it's not necessarily the case (e.g. you may want to track the insertion and purge date to have statistics for the leaving users).

Finally,  as everyone else has already said, there is not a single "good" answer.  For example,  you might have some audit regulations that forbids you to delete the personal data for a period of time,  so the "isActive" flag would be a better match.


Is it a good choice to choose NoSQL for a finance management app? I am working on a personal finance management application, which will also analyse the user's financial statement and give a good overview about their expenses and savings.

What I am having trouble is choosing between SQL and NoSQL. As per the data it looks very straight forward when using relational database managing the transactions and the user data. When using non relational approach de-normalizing the data might be a problem because there are a lot of referencing to transactions, category, modes and more.

What would be a good approach to this?

*Note: We are planning to use MEAN Stack for the app

The first thing you should do is to read this (from 2010). If that doesn't convince you, the read this (from 2014) and this (from 2017). Developers are discovering that SQL, transactions and consistency weren't such old-hat after all!

NoSQL is a tasty soufflé composed of more air than substance. There are some cases for which NoSQL is a good fit, and those are NOT finance management apps!

You abdsolutely want strong consistency for anything to do with monetary activities! NoSQL is one of those industry bandwagons which come along from time to time, like a star which is about to go supernova, briefly shines brightly, only to explode, then withers and dies a lingering and painful death much like a white dwarf!

NoSQL paradigms are good for social media type sites (i.e. the user typically isn't paying anything, or for only a small subset of services) where absolute consistency (through proper transactions) isn't a priority.

Systems such as marketing and/or analysis and/or reporting apps could be NoSQL. However, SQL is also great for slicing and dicing data for marketng, analysis and reporting. See Brian Aker's (former MySQL chief architect) hilarious take on NoSQL here.

From a similar thread about banking, there is a suggestion that NoSQL could be suitable for banking if the system has strong consistency and transactions - which brings us back to the RDBMS anyway. This is sort of the point of the 2014 link above - i.e. that NoSQL systems are all trying to put a strongly consistent SQL layer above their systems - we're back to the classic database: It's like déjà vu all over again :-).

Take a look at what Michael Stonebraker - a man who knows a thing or two about databases calls NoSQL a giant step backwards. He proposes "NewSQL" which "all support the relational data model and use SQL as their primary interface".

Re. MEAN stack, there's nothing wrong with using JavaScript frameworks for your front-end, just change the backend to an RDBMS. You should let the functionality decide the stack, not the other way round! If you want to go with Open Source, then my recommendation is that you use PostgreSQL (the best one in the field but a country mile!).


Powershell - SQL Server - connectionstring in loop for multiple Instances Thisis part of a bigger script which finds DatabaseFiles on a SQL Server machine (multiple instances).

Following should just return all files for 3 instances.

The server is called V3000801 and there is one default instance + 2 named instances on there. It's ok with me if either default or named doesn't work I'll work around this alone (most likely create a flag and do default with another connection string).

$SqlCmd.ExecuteNonQuery() just returns -1 which does not make any sense for me.

Thanks for the help

for($i=0;$i -lt $instances.Length;$i++){
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
  $Server= "V3000801\"+$instances[$i];
  $SqlConnection.ConnectionString = "Server = $server ; Database = master; Integrated Security = sspi;trusted_connection=true";
  $sqlQuery="SELECT physical_name FROM sys.master_files;";

  Write-Host $SqlConnection.ConnectionString;

  $SqlConnection.Open();
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
  $SqlCmd.CommandText = $sqlQuery;
  $SqlCmd.Connection = $SqlConnection;
  $SqlCmd.ExecuteNonQuery();
  $SqlConnection.Close();
}

The return value of -1 makes sense when you consider what ExecuteNonQuery() does. It returns the number of rows affected or -1 in case of set nocount on or non-updating queries.

As per documentation,

  For UPDATE, INSERT, and DELETE statements, the return value is the
  number of rows affected by the command. ... For
  all other types of statements, the return value is -1. If a rollback
  occurs, the return value is also -1.

Try .ExecuteReader() instead.


How to select multiple rows using one query Is it possible to select multiple rows using one query in SQL Server? For example, I have to run two queries to get my results as follows. How can I achieve the results using one query?
SELECT col1, col2
WHERE order_number = 'unique_value1'

SELECT col1, col2
WHERE order_number = 'unique_value2'

The first query is the fastest, with an iNDEX on order_number

CREATE TABLE mytable (col1 int, col2 int, order_number varCHAR(50))
GO

SELECT col1, col2
frOM mytable
WHERE order_number = 'unique_value1'
UNION
SELECT col1, col2
frOM mytable
WHERE order_number = 'unique_value2'

GO

col1 | col2
---: | ---:

SELECT col1, col2
frOM mytable
WHERE order_number = 'unique_value1' OR order_number = 'unique_value2'
GO

col1 | col2
---: | ---:

SELECT col1, col2
frOM mytable
WHERE order_number IN ( 'unique_value1' ,'unique_value2')
GO

col1 | col2
---: | ---:

db<>fiddle here


Setting up default values from another table Here is a problem I am trying to solve. Suppose I have a table called States (Name, Population). Let us assume it has one row - (Montana, 2million). Now suppose I have another table called People (Name, State, Profession). In this table I would like to set a default for state as the column value Name of table States, i.e., Montana. Now, doing so, for a tuple in People, (John, NULL, Programmer), I should get (John, Montana, Programmer) when I select this row. Suppose at a later date, I update Montana to Alabama. I should get (John, Alabama, Programmer). Any idea how using default, constraints, trigger or anything like that.

A bit more clarification:

Suppose I did

CREATE TABLE state
  ( state_name VARCHAR(20),
    population INTEGER
  );

INSERT INTO state VALUES ('Montana', 2000000);

Then I did

CREATE TABLE person
  ( person_name VARCHAR(50),
    person_state VARCHAR(20) DEFAULT sate_name(state),
    profession VARCHAR(50)
  );

INSERT INTO person VALUES (NULL, 12000);
INSERT INTO person VALUES ('New York', 145000);
INSERT INTO person VALUES ('Mississippi', 5000);
INSERT INTO person VALUES (NULL, 1000);

I know, I cannot do the DEFAULT as I wrote. But assuming I can do something that will let me achieve the effect is what I am looking for.

After this, if I wrote

SELECT *
FROM person

Should give me

Montana, 12000
New York', 145000
Mississippi', 5000
Montana, 1000

Now if I deleted the tuple ('Montana', 2000000) and then inserted ('Alabama', 50000), the query

SELECT *
FROM person

It should give me

Alabama, 12000
New York', 145000
Mississippi', 5000
Alabama, 1000

Hope it explains. I know about default, and join that you all suggested. Thanks for those comments. But that is not what I am hoping to achieve.

The MOST important thing to observe here is that the default value should come from another table, and cannot be a constant. For example, it cannot be the following statement:

CREATE TABLE person (person_name VARCHAR(50), person_state VARCHAR(20) DEFAULT 'Alabama', profession VARCHAR(50));

Where 'Alabama' is a constant. It must be a column name from another table.

I think you are making this much more complicated than needed. If I understand the requirements, you want a single state, stored in the state table to cat as the default state value for all persons. And if that default state is changed, the change should be reflected in all - existing and future - rows of person.

If the above interpretation is correct, then you don't need to store a default in persons (and then update it every time the default changes). Store NULL instead! This is how it will work:

CREATE TABLE state
      ( state_name VARCHAR(20),
        population INTEGER
      );

INSERT INTO state VALUES ('Montana', 2000000);

CREATE TABLE person
      ( person_name VARCHAR(50),
        person_state VARCHAR(20) DEFAULT NULL,        -- yes!
        salary INTEGER
      );

INSERT INTO person
VALUES
  ('Alex', NULL, 12000),
  ('Bill', 'New York', 145000),
  ('Cath', 'Mississippi', 5000),
  ('Dean', NULL, 1000) ;

Then we use COALESCE() to get the default value from state if person_state is null:

    SELECT p.person_name,
           COALESCE(p.person_state, s.state_name) AS person_state,
           p.salary
    FROM person AS p
      CROSS JOIN state AS s ;

person_name | person_state | salary
:---------- | :----------- | -----:
Alex        | Montana      |  12000
Bill        | New York     | 145000
Cath        | Mississippi  |   5000
Dean        | Montana      |   1000

Change the default state:

DELETE FROM state;

INSERT INTO state VALUES ('Alabama', 50000);

And see the results:

    SELECT p.person_name,
           COALESCE(p.person_state, s.state_name) AS person_state,
           p.salary
    FROM person AS p
      CROSS JOIN state AS s ;

person_name | person_state | salary
:---------- | :----------- | -----:
Alex        | Alabama      |  12000
Bill        | New York     | 145000
Cath        | Mississippi  |   5000
Dean        | Alabama      |   1000

dbfiddle here