Cumulative Game Score SQL I have developed a game recently and the database is running on MSSQL.

Here is my database structure

Table : Player

PlayerID uniqueIdentifier (PK)
PlayerName nvarchar

Table : GameResult

ID bigint (PK - Auto Increment)
PlayerID uniqueIdentifier (FK)
DateCreated Datetime
Score int
TimeTaken bigint
PuzzleID int

I have done an SQL listing Top 50 players that sort by highest score (DESC) and timetaken (ASC)
Sql below allowed me to get the result for each puzzle id. I'm not sure if it is 100% but I believe it is correct.

;with ResultSet (PlayerID, maxScore, minTime, playedDate)
AS
(
  SELECT TOP 50 PlayerID, MAX(score) as maxScore, MIN(timetaken) as minTime, MIN(datecreated) as playedDate
    FROM gameresult
    WHERE puzzleID = @PuzzleID
    GROUP BY PlayerID
    ORDER BY maxScore desc, minTime asc, playedDate asc
)
SELECT RSP.[PlayerID], RSP.[PlayerName], RSA.maxScore, RSA.minTime, RSA.PlayedDate
FROM ResultSet RSA
INNER JOIN Player RSP WITH(NOLOCK)
    ON RSA.PlayerID = RSP.PlayerID
ORDER BY
    maxScore DESC,
    minTime ASC,
    playedDate ASC

Question

1) I need to modify the SQL to do a cumulative rank of 3 puzzle ID. For example, Puzzle 1, 2, 3 and it should be sort by highest sum score (DESC), and sum timetaken (ASC)

2) I also need an overall score population for all the possible 1 to 7 puzzle.

3) Each player only allowed to appear on the list once. First played and first to get highest score will be rank 1st.

I tried using CTE with UNION but the SQL statement doesn't work.

I hope gurus here can help me out on this. Much appreciated.

Okay, so here is the query modified to work the way you want:

DECLARE @players table
(
    PlayerID uniqueidentifier NOT NULL PRIMARY KEY,
    PlayerName nvarchar(64) NOT NULL
);

DECLARE @playerScores table
(
    ID bigint NOT NULL IDENTITY PRIMARY KEY,
    PlayerID uniqueidentifier NOT NULL,
    DateCreated datetime NOT NULL,
    Score int NOT NULL,
    TimeTaken bigint NOT NULL,
    PuzzleID int NOT NULL
);

DECLARE @puzzleId int = 0;

SELECT TOP 50
    a.PlayerID,
    p.PlayerName,
    a.Score,
    a.TimeTaken,
    a.PlayedDate
    FROM
    (
        SELECT
            ps.PlayerID,
            ps.Score,
            ps.TimeTaken,
            ps.DateCreated AS PlayedDate,
            ROW_NUMBER()
                OVER
                (
                    PARTITION BY ps.PlayerID
                    ORDER BY ps.Score DESC, ps.TimeTaken, ps.DateCreated
                ) AS RN
            FROM @playerScores ps
            WHERE ps.PuzzleID = @puzzleId
    ) a
    INNER JOIN @players p ON p.PlayerID = a.PlayerID
    WHERE a.RN = 1
    ORDER BY
        a.Score DESC,
        a.TimeTaken,
        a.PlayedDate;

Having written this (note: indexes are not optimized), and looking at the other queries you're going to need to write, what I would actually recommend is to abandon this type of query entirely, and create a denormalized high-score table (rows are unique on the combination of PlayerID, PuzzleID), on which to run aggregates instead.

The reason why is because the GameResult table is going to grow huge in the database, and so it will be less and less efficient to run aggregates on it directly as time passes, and the requirements are incompatible with doing something like creating an indexed view to summarize the information.

Also, if you aren't doing this already, it's highly likely you'll want to use an asynchronous process to compute the "leaderboards" periodically and cache the results, instead of computing them just-in-time. (You could do something like merge the current player's score with the cached leaderboards so the player can see themself on the leaderboards immediately if they got a high score.) See my answer here for some ideas to consider when implementing a caching mechanism.


How do I get rid of the duplicate code in this query? I have this query, which works:
SELECT *, ("last done" + "auto time span"::interval) AS "new last done"
FROM personal."time tabs"
WHERE "reset automatically" = true
 AND ("last done" + "auto time span"::interval) < now();

However, I notice the duplicate code: ("last done" + "auto time span"::interval). Is it possible to make it only mentioned once?
These did sadly NOT work:
SELECT *, ("last done" + "auto time span"::interval) AS "new last done"
FROM personal."time tabs"
WHERE "reset automatically" = true
AND "new last done" < now();

Or:
SELECT *
FROM personal."time tabs"
HAVING ("last done" + "auto time span"::interval) AS "new last done"
WHERE "reset automatically" = true
 AND "new last done" < now();

What is the right way?

I would just duplicate the expression. It is possible to structure your query so that you only define the expression once, but it will end up being more verbose, as it requires that you define the field you're WHEREing as a column in a subquery and then do the WHERE on the outer field.
SELECT * FROM (
  SELECT *, ("last done" + "auto time span"::interval) AS "new last done"
  FROM personal."time tabs"
  WHERE "reset automatically" = true
) AS inner_query
WHERE "new last done" < now();

As long as you don't have ORDER BY or LIMIT on the inner query, the query planner should be able to tell that it's logically equivalent to having that WHERE in the inner query, so actual query execution shouldn't be affected.


What are the key points to avoid table locks? MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software -Java-)

First a quick recap on what a deadlock is. Let's say you have two sessions, 1 and 2, and two resources, A and B. The following sequence then occurs:

Session 1 gets a lock on resource A
Session 2 gets a lock on resource B
Session 1 requests a lock on resource B, but has to wait until session 2 has released it. We are fine up until this point
Session 2 requests a lock on resource A - but session 1 is blocked and can never go forwards to release it. Deadlock occurs.

There is only a very limited amount your DBA can do about this because this situation originates in the application, it merely manifests itself in the database. It is best to adopt a convention of always locking resource in the same order. If we had the convention of always locking in the order A, B then our scenario would be:

Session 1 gets a lock on resource A
Session 2 requests a lock on A, but has to wait for session 1
Session 1 gets a lock on resource B, completes its work, and releases both
Session 2 gets locks on both A and B, completes its work, and releases both. No deadlocks.


How to get mysql view definitions? Show create view viewName does not display correctly formatted text. How can I get the correctly-formatted SQL?

For example, I created a view like create view v1 as select * from mysql.uer, but it return like this:

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER
VIEW v1 AS
    select
        mysql.user.Host AS Host,
        mysql.user.User AS User,
        mysql.user.Password AS Password,
        mysql.user.Select_priv AS Select_priv,
        ... ...,
        mysql.user.Grant_priv AS Grant_priv,
        mysql.user.References_priv AS References_priv,
        mysql.user.Index_priv AS Index_priv,
        mysql.user.Alter_priv AS Alter_priv
    from mysql.user

How can I get my original definitions?

Show create view 'viewName'

returns the SQL definition with create view statement while

SELECT  VIEW_DEFINITION
    FROM    INFORMATION_SCHEMA.VIEWS
    WHERE   TABLE_SCHEMA    = 'DATABASENAME'
    AND     TABLE_NAME      = 'VIEWNAME';

returns only definition.

Both are correct for getting view definition.


Performance issue | MySQL Consumes more than 80%+ memory and 2000%+ CPU Everyday during the peak time my server getting slow or down. Our hosting provider insisting us to upgrade the server but I think some performance tuning issue is there.

Adding the  process information, server configuration and my.cnf parameters below.

Process Information

 PID  USER       PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 60848 mysql     20   0 34.8g  23g 6416 S 2196.2 82.1  16027:29 mysqld

Dedicated Server Configuration

Size:   'Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz', 2600MHz, 6 Core, Sockets: 2
Image:  CentOS 6 64-bit with cPanel Fully-managed
CPU:    Intel Dual Xeon E5-2620 v3
Speed:  2600MHz
RAM:    32067MB
CPUs:   2 Physical CPUs
Cores:  12
Total Cores RAID:   Level 10
Disks:  4
Size:   917GB
Type:   SSD

MySQL Configuration

[mysqld]
slow_query_log = 1
#long_query_time = 2
long_query_time = 2
slow_query_log_file = /var/lib/mysql/vps-slow.log
performance-schema=0
max_connections = 250
max_allowed_packet=128M
#skip-external-locking
key_buffer_size = 256M
open_files_limit = 50000
table_open_cache = 15000
sort_buffer_size = 6M
join_buffer_size = 6M
read_buffer_size = 1M
read_rnd_buffer_size = 15M
myisam_sort_buffer_size = 64M
query_cache_type=1
query_cache_limit=2M
query_cache_size=128M
max_heap_table_size = 96M
tmp_table_size = 96M
#low_priority_updates=1

thread_cache_size = 32
wait_timeout = 300
connect_timeout=15
interactive_timeout = 800

innodb_buffer_pool_size=4096M
innodb_buffer_pool_instances = 4

group_concat_max_len=1000000

default-storage-engine=MyISAM
innodb_file_per_table=1

[mysqldump]
quick

Please help me if there is anything wrong with the configuration ?

Or

Do I really need to upgrade the server ?

PS: Hosted  web application in the same server. Also WHM enabled for easy management.

It would be helpful to have SHOW CREATE TABLE for the first few tables in the slowlog.  It would also be helpful if you always tagged each column with the alias (or table) that it belongs to.  Meanwhile, I will make some guesses.

Indexes (or PRIMARY KEYs) needed:

user_registration:  (usr_username)
user_type:  (usr_type_id)
company_master:  (comp_id)
company_master:  (status, comp_id)

If my suggestions don't help enough, then provide EXPLAIN SELECT ... for the worst few queries.

Because of the implementation of information_schema (prior to version 8.0), SELECT  data_free... cannot be sped up.  But why are you doing the query?  There may be a workaround.

Including a test on status or del_status (etc) sometimes makes optimization more difficult.  There is not a one-size-fits-all remedy.

A tip about SELECT  U.*, ...: If * includes some TEXT or BLOB columns that you don't really need, there may be a performance improvement by spelling out only the columns you do need.

Those indexes should help the first few queries.  Since they comprise 99.9%+ of the activity, you should see the CPU go down significantly.

If you need another go at this, change to long_query_time = 1, clear the slowlog, and run for another day.


Can we use SQL Express to send messages to SQL Standard using the Service Broker? We want to use SQL Express at our local sites but to still be able to push data back to our head office using the Service Broker.  SQL Server at the head office is 2008 R2 Standard.

Looking at the feature lists of SQL Express 2008 R2 for Express it says "Client Only", what does this mean in relation to our needs?

The 'client only' means that two Express instances cannot communicate directly. They have to have a Standard instance in between acting as a forwarder. Express instances can both generate and consume messages.

Messages originating on Express instances can be received on Standard or higher instances.
Messages originating on Standard or higher instance can be received on Express
Messages originating on Expres can be received on another Express instance only if it was routed to an intermediate Standard or higher instance using a forwarding endpoint, see Service Broker Message Forwarding. A single Standard instance can act as a forwarder to any numer of Express instances, limited only by processing capacity and network bandwidth.


Move data from mysql 4.0 to 5.6 without corrupting encoding I have a old mysql database that I would like to move to a recent version of mysql and optimize for SELECT ... WHERE ... statements. It is going to be a SELECT only database.

Original database uses latin 1 encoding but the actually data uses Turkish chracters. It is used by a desktop application. Probably application interprets data as latin 5 to display correctly.

I may ditch the original program and implement my own UI for queries.

My problem is that when I mysqldump -> mysql import, some chracters get corrupted. For example, YAŞAR becomes YAÞAR. I didnt add any encoding information to mysql dump and import. When I use phpmyadmin, it seems like my rows use latin1_swedish_ci collation with latin1 encoding.

How can I move my data and don't mess up with encoding.

Here is how I did it. First, I launched old mysqld and issued this command:

mysqldump -u root -p --tab=tmp --default-character-set=latin1 database tablename

Then, I started new mysqld and created table structure using phpmyadmin. I selected latin5_turkish_ci as collation for database and table rows.

I also copied resulting tablename.txt to new mysql data directory. And I imported the file like this:

mysqlimport -u root -p --default-chracter-set=latin5 database tablename.txt

This fixed issues I was having.


Can I identify duplicates in a large MySQL insert? I currently have a PHP script that inserts thousands to millions of rows into an InnoDB table.  To prevent duplicates, the table has a UNIQUE index set up on the combination of four columns, and I use INSERT IGNORE to allow the insert to continue regardless of duplicates.

For performance reasons, rather than inserting one row at a time, I batch them up into 2000 rows per query.  I want to know which individual rows are ignored due to a key violation, and the only way I can think to do it is to insert one row at a time and then check the value of mysqli_affected_rows after each insert, but that feels inefficient and I will lose the advantage of batching my inserts.  Alternatively I could remove the UNIQUE index and retrospectively check for duplicates using some SQL at the end.

Any other suggestions?

Many thanks.

This can probably be solved with a more complex set of steps.  You still get the benefit of the bulk INSERT.

Build a temp table (probably permanent, but TRUNCATE between uses).

INSERT the 2K rows into it.

Analyze the rows via a JOIN between the temp table and the real table.

INSERT INTO real ... SELECT ... JOIN ... to put the "new" rows.

More details in http://mysql.rjweb.org/doc.php/staging_table , especially the section on 'Normalization', which uses two queries in a slightly different way -- one to insert 'new' rows, another to pull back the id for every row.

Your app may also benefit from the "flip-flop" technique described there.


Fiscal Year to Date filter This is my data:

I need a Fiscal Year to Date logic that will gather all previous months starting October.
However, for October 2018 I want to start form 15th, for remaining years it must start from October 1st.

Expected result would be all dates from October till last day of previous month.
For testing purpose I replaced sysdate, 'MM' with to_date('31-DEC-18'), 'MM'

Below is working but wondering if I can improve it...

PER_PERIOD_START_DATE between

(CASE
WHEN (a.PER_MONTH_NUM = 10 and extract( year from a.PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(to_date('31-DEC-19'),6),'YYYY'),-3) +14
ELSE Add_Months(Trunc(Add_Months(to_date('31-DEC-19'),6),'YYYY'),-3)
END
)
and trunc(to_date('31-DEC-19'), 'MM')-1

Final code:

where PER_PERIOD_START_DATE between

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(sysdate,6),'YYYY'),-3) + 14
ELSE Add_Months(Trunc(Add_Months(sysdate,6),'YYYY'),-3)
END
)
and trunc(sysdate, 'MM')-1

Below code fully meets the requirement:

PER_PERIOD_START_DATE between

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'),-3) + 14
ELSE Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'),-3)
END
)
and trunc(sysdate, 'MM')-1

Alternative:

PER_PERIOD_START_DATE between

(CASE
WHEN (PER_MONTH_NUM = 10 and extract( year from PER_PERIOD_START_DATE) = 2018)
THEN '15-OCT-'||(EXTRACT (YEAR FROM ADD_MONTHS(sysdate, -9)))
ELSE '01-OCT-'||(EXTRACT (YEAR FROM ADD_MONTHS(sysdate, -9)))
END
)
and trunc(sysdate, 'MM')-1


Single row to multiple rows with column combination I have a table like this:

╔═════╦═══════╦════╦════╦═════╦═════╦═════╦═════╦════╦════╗
║ ZIP ║ STATE ║ F  ║ C  ║ F1  ║ c1  ║ f2  ║ c2  ║ f3 ║ c3 ║
╠═════╬═══════╬════╬════╬═════╬═════╬═════╬═════╬════╬════╣
║ A   ║ AS    ║ AF ║ AC ║ AF1 ║ AC1 ║ AF2 ║ AC2 ║    ║    ║
║ B   ║ BS    ║ BF ║ BC ║ BF1 ║ BC1 ║     ║     ║    ║    ║
╚═════╩═══════╩════╩════╩═════╩═════╩═════╩═════╩════╩════╝

The output I am looking for is:

╔═════╦═══════╦═════╦═════╗
║ ZIP ║ STATE ║  F  ║  C  ║
╠═════╬═══════╬═════╬═════╣
║ A   ║ AS    ║ AF  ║ AC  ║
║ A   ║ AS    ║ AF1 ║ AC1 ║
║ A   ║ AS    ║ AF2 ║ AC2 ║
║ B   ║ BS    ║ BF  ║ BC  ║
║ B   ║ BS    ║ BF1 ║ BC1 ║
╚═════╩═══════╩═════╩═════╝

How can I achieve this?

Given this test data:

DECLARE @T AS TABLE
(
    ZIP sql_variant NOT NULL,
    STATE sql_variant NOT NULL,
    F sql_variant NULL,
    C sql_variant NULL,
    F1 sql_variant NULL,
    C1 sql_variant NULL,
    F2 sql_variant NULL,
    C2 sql_variant NULL,
    F3 sql_variant NULL,
    C3 sql_variant NULL
);

INSERT @T
    (ZIP, STATE, F, C, F1, C1, F2, C2)
VALUES
    ('A', 'AS', 'AF', 'AC', 'AF1', 'AC1', 'AF2', 'AC2');

INSERT @T
    (ZIP, STATE, F, C, F1, C1)
VALUES
    ('B', 'BS', 'BF', 'BC', 'BF1', 'BC1');

One way to produce the desired output is:

SELECT
    T.ZIP,
    T.STATE,
    CA.F,
    CA.C
FROM @T AS T
CROSS APPLY
(
    VALUES
        (F, C),
        (F1, C1),
        (F2, C2),
        (F3, C3)
) AS CA (F, C)
WHERE
    CA.F IS NOT NULL
    OR CA.C IS NOT NULL;


Header is added when exporting column of type image with BCP to files I export a column of type image to files using this command:

EXEC master..xp_cmdshell
'BCP "SELECT file_name FROM file_table WHERE id = 11 "
queryout "C:\bcpdir\test.xml" -T -N'

In SQL Management Studio, I can see the hex value of the field starts with 0x3C3F786D6C which is correct for an XML file (<?xml).

However, the command above seem to add an 4 byte header to the file. How can I use BCP without this header?

The solution seem to be to first execute BCP manually, set prefix to 0 in the user dialog. And then use the generated .fmt-file as in this command:

EXEC master..xp_cmdshell 'BCP "SELECT my_column FROM my_table WHERE my_id = 11 "
queryout "C:\bcpdir\bcpout.xml" -T -f "C:\bcpdir\bcp.fmt'


How to write a query that will retrieve the items with sold amount Problem : I have a table which has three columns such as
item id ,transaction_date,Amount_sold.

I want to have a list of item with Amount sold which are sold today but if the same item was sold yesterday then it should not come in my list.

Not exists will work here

 select item_id, amount_sold
       from table t
   where transaction_date = trunc(sysdate)
     and not exists (select 1
                       from table t1
                      where t1.item_id = t.item_id
                          and t1.transaction_date = trunc(sysdate)-1;

EDIT based on OP's latest observations :

select item_id, SUM(amount_sold) as amount
       from table t
   where trunc(transaction_date) = trunc(sysdate)
     and not exists (select 1
                       from table t1
                      where t1.item_id = t.item_id
                          and trunc(t1.transaction_date) = trunc(sysdate)-1
 group by item_id;


SQL Server query result transfer time over VPN (WAN) I have a mysterious phenomenon on one remote SQL Server 2005 which I manage and query using SQL Server Management Studio. I connect to the server through a VPN tunnel (provided by an ISP, about 8 MBit/s up and down).

When I open a new query window (first connection) the result is quickly transferred to the client. Let's say < 1 sec. When I repeat the query a few times, the results transfers are getting slower, up to 5 sec and more. It has nothing to do with the query execution time, it's just the transfer time to the client (SSMS).

Now, even stranger: I opened a second query window to the same server/database, the query result transfer is always fast even if I run them at the same time. Even after two hours the results are always transfered very quickly in that other window, while it takes much longer in the first query tab.

I'm using this testing query in order to avoid some potential caching issues:

select top 2000 newid() from <SOMETABLE>

I tried to change the server registration's connection packet size, but it didn't change much.

What could cause the problem?

Update: as suggested in the comments I cross posted on Serverfault.com.

In your VPN properties, click options, PPP Settings.
Do you have "enable software compression" ticked?
SQL data compresses quite nicely, and if the remote site is heavily used bandwidth wise, it may simply be struggling to send you the data.


SQL Server credentials - Maximum Password size What's the maximum password size I can get on SQL Server 2012/2014? I was unable to find this information online.

I mean SQL Server credentials itself, not passwords inside tables.

Thanks a lot

Passwords are case-sensitive. Passwords should always be at least 8
  characters long, and cannot exceed 128 characters. Passwords can
  include a-z, A-Z, 0-9, and most non-alphanumeric characters. Passwords
  cannot contain single quotes, or the login_name.

https://msdn.microsoft.com/en-us/library/ms189751.aspx


Query to identify people exceeding a particular amount within an interval of 6 months I have tried searching in Google but am just not finding the right post.
I have this table with fields trans_date, CID (which is customerid), and amount.

How do I find the CID exceeding an amount of say $1000 within any time period of 6 months or 180 days (eg. Jan-June or Feb-July, or November 2014-April 2015 (periods overlapping years too)) starting from the beginning of time?

trans_date             CID        amount
22-Jun-2015            123        550
24-Aug-2015            999        200
15-Aug-2015            123        500
24-nov-2014            321        200
08-Jan-2015            321        900
24-oct-2015            999        200

In the result CID 123 and 321 should be the output both exceeding the total amount 1000 in a span of 6 months.

Any response would be highly appreciated.

The part "within any time period of 6 months" makes things quite tricky. It is very easy to check this for a specific period:

select cid,
       sum(amount)
from transfer
where trans_date >= date '2014-11-01'
  and trans_date < date '2015-06-01'
group by cid
having sum(amount) >= 1000;

But this is not what you want.

You want a "rolling running total" that covers the last 6 month relative to the the trans_date value of each row. This can easily be achieved using a window function (aka "analytical function")

select trans_date,
       cid,
       amount,
       sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
from transfer
order by trans_date;

The "trick" is the "range between ..." part: it will only evaluate the running total for the "last 6 month" relative to the month of the "current row"

The above returns the following result for your sample data:

TRANS_DATE          | CID | AMOUNT | RUNNING_TOTAL
--------------------+-----+--------+--------------
2014-11-24 00:00:00 | 321 |    200 |           200
2015-01-08 00:00:00 | 321 |    900 |          1100
2015-06-22 00:00:00 | 123 |    550 |           550
2015-08-15 00:00:00 | 123 |    500 |          1050
2015-08-24 00:00:00 | 999 |    200 |           200
2015-10-24 00:00:00 | 999 |    200 |           400

Now to get the customers you are interested in, you can wrap this query into a derived table and then get only those CIDs that have at least one running total that exceeds 1000:

select distinct cid
from (
  select trans_date,
         cid,
         amount,
         sum(amount) over (partition by cid order by trans_date range between interval '6' month preceding and current row) as running_total
  from transfer
) t
where running_total > 1000;

The distinct is necessary because a customer can have more than one "6 month period" where the total amount exceeds 1000.

This is probably not the most efficient query though - but currently I can't think of anything better.

Here is a SQLFiddle example: http://sqlfiddle.com/#!4/1e26f/2


Using a PostgreSQL tablespace from two different servers I have a PostgreSQL database living on an external hard disk (RAID0 via Thunderbolt). So far I accessed it from a PostgreSQL server running on my MacBook Pro. As my work on this database is getting more and more intensive the queries are getting more and more complex, too. Therefore I'd like to use my brand spanking new iMac from now on.

My question is: Can I somehow tell the new PostgreSQL server (running on the iMac) to use the data that is already living in the tablespace on the external disk? Or will I have to export & import all the data manually (pg_dump, pg_restore)?

I strongly recommend doing a dump and reload.

Don't put tablespaces on removable media, it doubles your chances of data loss. If either the main database on the main disk or the removable media fails / is lost, your data's gone. A tablespace cannot be read without the database it's from.


How to downgrade my mdf file from version 661 to 655 or earlier? I have a problem when attaching the file AdverntureWorksLT2008R2_Data.mdf to Microsoft SQL Server Management Studio (SSMS).

  The database 'AdventureWorksLT2008R2' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported. Could not open new database 'AdventureWorksLT2008R2'. CREATE DATABASE is aborted. )Microsoft SQL Server, Error: 948)

So, I was wondering if it is possible to do something on the .mdf file so that the .mdf file will be downgrade to version 655 or earlier.

There is a similar question(s) such as this - Attaching database to the SQL server

The correct answer mention about getting to know more about the version and one suggest that latest Service Pack must be installed.

I have checked the version and notice that the latest Service Pack have been installed.

So, I was thinking of downgrade the mdf file but how do I downgrade it?

Simple answer: you cannot do that.

There's no tool, no undocumented way, no hack, no workaround to achieve this. SQL Server database files just aren't "backwards" compatible - you can easily go from 2005 to 2008, or from 2008 to 2012 - but you cannot go backwards.

You'll need to find another way to do this, e.g. by scripting out database structure and/or data into .sql files and running those on the old system.


MSSQL - what is the best isolation level for a website My website is mostly informative,
Users cannot Update or Delete, they can perform Insert but that almost doesn't happen.

Most of the site uses SELECT queries and the information doesn't change often so I don't mind of if user reads information that was published even a day ago.

Now the isolation level is the default which is Read Committed which seems a bit overkill for my website I think.

After a bit research I think about changing it to Snapshot or read_committed_snapshot.

What do you suggest?

To switch a database to RCSI:

USE mydb;
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE mydb SET MULTI_USER;


execution plan question I have this query running for long time

    (@P0 nvarchar(4000))

select emaildmo0_.Id as Id7_
    ,emaildmo0_.CreatedDate as CreatedD2_7_
    ,emaildmo0_.EmailAddress as EmailAdd3_7_
    ,emaildmo0_.EmailType_Id as EmailType4_7_
    ,emaildmo0_.isPreferedEmail as isPrefer5_7_
    ,emaildmo0_.ProfileId as ProfileId7_
    ,emaildmo0_.UpdatedDate as UpdatedD6_7_
from dbo.Email emaildmo0_
where emaildmo0_.EmailAddress = @P0

This is the execution plan

My first question why it is index scan not index seek

These 2 lines from the ExecutionPlan.xml

        <Warnings>
          <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(255),[emaildmo0_].[EmailAddress],0)" />
          <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(255),[emaildmo0_].[EmailAddress],0)=[@P0]" />
        </Warnings>

May you explain for me what is this warning mean ? and if there is any suggestion to tune this query

It means that the parameter should be defined to match the data type of the underlying column - currently the parameter is nvarchar(4000) but it should be varchar (looks like 255 but you should check the table).

Whether this actually makes your query slow or leads to a bad plan is tougher to determine. But making those two definitions match certainly isn't going to make it worse.

If you can't change the parameter data type, either get a better ORM or change the column as a last resort.


Should I add a column or 1-1 table if property is usualy null There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%).
We thought it's better to add a new table which will be 1-1, than to add a new column which will have Nulls 99% of the time.

Is a new table the preffered way?

If one of the following conditions is true then yes, go ahead and create a separate table.

You are working in SQL 2005 or lower
Your new column is one of the following datatypes: Geography, Geometry, Image, NText, Text, Timestamp, or a user defined data type
No default value
No rules
Column isn't a computed column

If none of those are true then you could use a SPARSE column.

ALTER TABLE tablename ADD mysparsecolumn INT SPARSE NULL

Here is BOL on SPARSE columns.  Also you may want to look at COLUMN SETS.  A sparse column is a column that is optimized for NULL values and according to BOL takes up no space when the value is NULL.  There are tables in BOL that will give you an idea of the actual savings generated by using SPARSE columns.

All that being said if you want to use a separate table you certainly can.  The down sides are that you will lose some space over all depending on how large your primary key is (the minimum you will need to include in the new table along with the extra column) and it's libel to be a bit slower because of the extra join.


SQL-Server: sp_spaceused gives zero rows but big dataSize for cleaned table After cleaning my table with DELETE FROM MyTable (and executing DBCC shrinkdatabase('MyDB') should that matter) I run the statement EXEC sp_spaceused MyTable. The results confuse me:

tableName   numberOfRows   reservedSize   dataSize   indexSize   unusedSize
---------   ------------   ------------   --------   ---------   ----------
MyTable     0              21664 KB       20672 KB   736 KB      256 KB

As you can see there are zero rows, yet there's almost 21 MB of data. My question is: what are possible causes for this situation and/or how can I further investigate this?

DELETE does not reclaim space, it deletes rows.

Space can remain allocated for several reasons, 3 of which are:

ghost clean up is still running
the table has no clustered index
the system tables are not updated yet

Try

EXEC sp_spaceused 'MyTable', 'true' to force a space used update
TRUNCATE  TABLE which deallocates space, rather then deleting rows
DELETE  myTABLE WITH (TABLOCKX) if no clustered index and can't use TRUNCATE

Don't run DBCC shrinkdatabase because it adds no value and it will simply grow again


Prevent columns from having same value in record I'm working with PostgreSQL 10. I'd like to have a table that allows for the same value in a column, but not in the row. The table I created:

CREATE TABLE teams
(
  team_id SERIAL PRIMARY KEY
  team_lead character varying(250) NOT NULL,
  member1 character varying(250) NOT NULL,
  member2 character varying(250),
  member3 character varying(250)
  )

This would be allowed:

| team_lead     |  member1     | member2       |
------------------------------------------------
| Jane Doe      | Bill Smith   | Shirley Green |
| Jane Doe      | Carol Lewis  | John  Doe     |

This would NOT:

| team_lead  | member1     | member2       |
--------------------------------------------
| Jane Doe   | Jane Doe    | Shirley Green |
| Bill Smith | Carol Lewis | Bill Smith    |

Does anyone know how I would accomplish this? Thank you in advance.

This is called a "many-to-many" relationship. One person can be a member of multiple teams and a team has multiple members. One way to model this, could be:

create table person
(
   person_id integer primary key,
   name text not null
);

create table team
(
  team_id integer primary key,
  name text not null
);

create table team_member
(
  team_id integer not null references team,
  person_id integer not null references person,
  person_role text not null check (team_role in ('lead', 'member')),

   -- each person can only be assigned to a team once
  primary key (team_id, person_id)
);


Document Store & Relational DB Hybrid. Exists? Possible? Does anyone know of any product or open source project that attempts to strap together Mongo DB and MySQL for instance?

I hear a lot about how relational DBs being ACID compliant makes it nearly impossible to do sharding as easily as in Mongo and this is the achilles heel. The NoSQL fans are surely right. A technology must eventually parallelize or die.

No to NoSchema

However, schemaless DBs are not a sustainable solution to many problems. Schema changes are only hard because they force you to see what you're breaking. Not seeing doesn't mean you're not breaking.

Best of both?

What I need help understanding is why it seems accepted that you can't in theory have the advantages of both. Can't a relational DB be engineered to compromise with a wise strategy of eventual consistency? I understand why it's more difficult than with documents - a single fetch is composed of records stored physically all over the disk. But how about a document store which acts as a front for a relational DB?

Imagine

The DB has a built in ORM to map from relational to document collection and you interface with the document in your app. Essentially, the document collections are like materialised views with write-back capability. By default the relational DB part is ACID compliant and ensures that non-acidic documents are eventually consistent. Cross-record schema constraints may not be performant enough but most other schema controls could be.

When you query a large batch of documents, if a few are out of sync - because another document sharing the same denormalised data has just been written to - then they are silently refreshed live from the underlying DB by default. If blocking is an issue then you can choose to handle those dirty docs with a number of other looser consistency options.

Pros and pros

..and small cons

You don't get the document store advantage of fast writes but

Gets NoSQL advantage: massive and fast reads, easy sharding and no O-R impedance mismatch for front-end developers.

Gets SQL advantage: Organisations can stay nimble, able to work with their data from new angles not thought of initially. Schema helps keep production data from turning into a turgid mess of exceptions before its too late.

Gets hybrid advantage: Rows can be physically re-organised in the RDB to follow the patterns suggested by the most frequently accessed document collections.
When the load gets too high for the relational DB on one box this will make sharding more of a possibility (with a possible tradeoff against whole-system analytical queries but that's what you have an OLAP for).  The relational DB will also have much fewer reads than if it were directly user facing.

I've misunderstood something. I must have.

I don't imagine this is an easy bit of engineering but it seems within the powers of the those who have brought us the products we already have.

I'm not seeking general discussion here but I wouldn't be surprised if there's a big, concrete gotcha I'm missing in this account and I'd like to know what it is.

SQL Server has JSON support though it seems to be a variation on it's XML support.

PostGres has had JSON support since 9.2.
Teradata has had it since 2014.

This makes them hybrids.  Obviously PostGres is the open-source one.

It really depends on what you need from JSON support.  If it is to return the document on a key then that has always been possible without explicit JSON support.

If you want to index certain parts of the JSON document then that does require specific JSON support.  There is the question of how sparse the attribute is that you want to index.  If it is present in the vast majority of cases (or even a mandatory attribute) then I would break it out of the JSON and have it as an explicit attribute in a hybrid store.

If by parallelize or die you mean support a distributed dataset or die then I'm not sure that I agree.  For OLTP work you would have to be in a Times top 100 company to approach the limits of the traditional RDBMS.  By definition most companies just don't generate as much useful data as they would like to think.

Companies like Facebook, Twitter and NetFlix are dealing with data on an order of magnitude far beyond what most people will see.

For high end web analytics work then yes, you might want a NOSQL product as a collector.  Cassandra is useful in that respect, plus it has tunable consistency.

The gotchas in distributed systems is Brewers CAP theorem.  You can have any two of Consistency, Availability or Partition tolerance.  It's a bit more blurred than that but the 2 of 3 rule is generally true.

There's the challenge of how you handle referential integrity and in some cases how do you honour primary key constraints.  Your data might be splashed across many servers so honouring a foreign key concept might require a lookup between one server and another.  Even if this were a feature it would seriously impact performance.

If people want to interact with an API that only talks JSON then I see no problem with that providing there is an underlying DAL that obfuscates whether a data attribute is present in a JSON document or explicit attribute in a hybrid design.


SQL Server 2008 Triggers and Merge Replication I am trying to have a trigger fire when the Merge agent inserts or deletes data into / from a table. It is supposed to insert data into a report table for use with SSRS.

I DO NOT have the "NOT FOR REPLICATION" set in the trigger. Here is the trigger code:

ALTER TRIGGER [dbo].[MyTable_INSERT]
ON  [dbo].[MyTable]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO...
END

I have tried FOR INSERT and AFTER INSERT, neither seem to work.

The table structure is usual parent / child, I have the trigger set on the child to make sure the parent is already created. The relationships are secured with FK's.

I'm sure am overlooking something simple. Any ideas ?

The problem was an error in my trigger insert code. It was using INNER JOIN's and one was not correct so was not returning any data.


Create view using tables from differing databases on a server I need to create a view that pulls data from multiple databases on a server. I've done this by hard coding the database names (db1.schema.table), but I need it to be more dynamic. This view needs to be available to run on multiple servers that have 1 or more databases. The database and table structures are all the same.

I know I can get the data using a cursor and dynamic SQL, but that does not result in the view I need for reporting in another software program.

If it makes more sense, I want to Union data from the same table in multiple databases into one view, however, the number and name of different databases will be different on different servers. Can this be done without hard coding the views on each server?

You'll need to hard-code the view on each server.  But you can write a stored procedure to recreate the view on-demand.


How can I get partition name from value in MySQL/MariaDB I have my table partitioned on day of month basis.

Now I need to archive data of the last 16th day date_add(now(), interval -16 day) to another table. For that I need to find out the partition name that contains data of the date, so that I can exchange the partition and export to another table.

But my problem is,I don't know how to find the partition name from value, "I need to exchange and archive.

Your help will be highly appreciated.

Thanks

information_schema.PARTITIONS.PARTITION_DESCRIPTION

Consider naming the partitions in an obvious way, such as

p20200419

to contain data for the 19th (and defined LESS THAN ('2020-04-20'))

More tips:  http://mysql.rjweb.org/doc.php/partitionmaint and its companion:  http://mysql.rjweb.org/demo_part_maint.pl.txt

Example

Given:

SHOW CREATE TABLE pmdemo.pmdemo\G
*************************** 1. row ***************************
       Table: pmdemo
Create Table: CREATE TABLE `pmdemo` (
  `dt` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION `start` VALUES LESS THAN (0) ENGINE = MyISAM,
 PARTITION from20121008 VALUES LESS THAN (735150) ENGINE = MyISAM,
 PARTITION from20121009 VALUES LESS THAN (735151) ENGINE = MyISAM,
 PARTITION from20121010 VALUES LESS THAN (735152) ENGINE = MyISAM,
 PARTITION from20121011 VALUES LESS THAN (735153) ENGINE = MyISAM,
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.01 sec)

This select:

mysql> SELECT * FROM information_schema.`PARTITIONS`
      WHERE table_name = 'pmdemo'
        AND partition_description = TO_DAYS('2012-10-10')\G

*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: pmdemo
                   TABLE_NAME: pmdemo
               PARTITION_NAME: from20121009  -- the partition name
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: TO_DAYS(ft) -- a convenient formula for time-series
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 735151  -- the TO_DAYS() in the query
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 1970324836974591
                 INDEX_LENGTH: 1024
                    DATA_FREE: 0
                  CREATE_TIME: 2017-01-09 13:49:01
                  UPDATE_TIME: 2017-01-09 13:49:01
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
1 row in set (0.00 sec)


MySQL - applying same binary log Quick question - if I apply same binary log multiple times via:

mysqlbinlog --start-position=xxx mysql-bin.xxxx | mysql

will the server understand and omit previously applied changes or further corrupt the database?

Also, in my backup I have the log position stated like this:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=155;

However, when I do

SHOW MASTER STATUS

this is what I get:

+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
| File      | Position      | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set      |
+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
| mysql-bin.000002 | 497824459     |                   |                       |                        |
+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
1 rows

I understand that the number from the backup should be more accurate because the backup was made in transaction, but my confusion is how comes the numbers are so different? (in a matter of 30 minutes) Or these are different numbers and I should use the 155 number for the
--start-position option?

It it evident from your question, that 30 min worth of transactions came into the database while the mysqldump was in progress.
In order for the binlog position to move and the mysqldump to work, the mysqldump parameter --single-transaction had to have been issued. What does it do ???
According to MySQL Documentation on mysqldump --single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.
When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.
To dump large tables, combine the --single-transaction option with the --quick option.

The log file and position recorded in the mysqldump (usually between lines 23-25) simply give you the place to start when setting up a MySQL Replication Slave.
EXAMPLE
Suppose you ran mysqldump --single-transaction from IP 10.20.30.40 (call it Server M1) and loaded that mysqldump into another server (the one to become the MySQL Replication Slave, call it Server S1).
You run the following commands on S1
CHANGE MASTER TO
    master_host='10.20.30.40',
    master_post=3306,
    master_user='replicationusername',
    master_password='replicationpassword',
    master_log_file='mysql-bin.000002',
    master_log_pos=155;
START SLAVE;

What this will do is activate two DB threads

I/O Thread : This will copy all binlog events from the initial position you gave it or the last position it successfully executed from the master (M1). When it gets to the end of the binlog it was reading, it will move to the next binlog (mysql-bin.000003) and keep going from there.
SQL Thread : This will execute all binlog collected from the I/O until there are no more. Then, the SQL thread will wait for new events from the I/O Thread to the collected.

What are binlog events ?? The recorded SQL commands that a Master Server records when the Master has executed them.
Would you like to know what the binlog events look like ???
You would go to M1 and run the following
mysqlbinlog --start-position=155 --stop-position=497824459  mysql-bin.000002 > binlog_events.sql
less binlog_events.sql

The file binlog_events.sql will contain all the binlog events that executed on the Master while the mysqldump was in progress.
If you want those events on another server without setting up MySQL Replication, you could load the mysqldump and then load binlog_events.sql into a new server and have the dump just as up-to-date as possible.
YOUR ACTUAL QUESTION
Yes, you will further corrupt the DB.


How does MariaDB handle ENUM types used in FOREIGN KEY constraints? Off the heels of this question,

How does MySQL handle joins on ENUMs?

How does MySQL work if the referencing table has more and less values in the ENUM than the table being referenced? Is it validating integrity based solely on the global ENUM textual key (symbol)? Or the underlying integer value?

As demonstrated in the related question, JOINS on enums are "by-value". Constraints, on the other hand, is validated "by-position". This behaviour strikes me as rather counter-intuitive. Using a similar setup as Evan:

CREATE TABLE foo ( a ENUM ('A', 'B') not null primary key );
CREATE TABLE bar ( a ENUM ('X', 'Y', 'Z') not null primary key );
ALTER TABLE bar ADD CONSTRAINT fk_foo_bar
    FOREIGN KEY (a) REFERENCES foo (a);

insert into foo (a) values ('A'),('B');
insert into bar (a) values ('X'); -- OK
insert into bar (a) values ('Z');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test3"."bar", CONSTRAINT "fk_foo_bar" FOREIGN KEY ("a") REFERENCES "foo" ("a"))

So 'X' is valid according to the foreign key constraint. As Evan demonstrated, JOINs behave differently:

SELECT * FROM foo JOIN bar USING(a);
Empty set (0.00 sec)

So despite that, the row in bar is validated by the foreign key, the join between foo and bar is empty.

It may be tempting at first to use ENUM as a substitute for the lack of CHECK CONSTRAINTS in the MySQL family. However, the way FOREIGN KEYS work together with ENUMs can shoot you in the foot. A typical pattern for subtypes look like:

CREATE TABLE P
( p int not null primary key
, sub_type ENUM ('A','B')
,     constraint AK_P unique (p, sub_type));

CREATE TABLE A
( p int not null primary key
, sub_type ENUM ('A')
,     constraint aaa foreign key (p, sub_type)
                     references P (p, sub_type));

CREATE TABLE B
( p int not null primary key
, sub_type ENUM ('B')
,     constraint bbb foreign key (p, sub_type)
                     references P (p, sub_type));

insert into P (p,sub_type) values (1,'A'),(2,'B');
insert into A (p,sub_type) values (1,'A'); -- OK
insert into B (p,sub_type) values (1,'B'); -- OK, but should not be allowed since 1 is sub_type 'A' in parent
insert into B (p,sub_type) values (2,'B'); -- Fails, but should be OK

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test3"."B", CONSTRAINT "bbb" FOREIGN KEY ("p", "sub_type") REFERENCES "P" ("p", "sub_type"))

So the foreign key validates the position in the enum, not the value itself.

PostgreSQL does not allow foreign keys between different enums, so the situation is not possible to reproduce there:

CREATE type e1 as ENUM ('A', 'B');
CREATE type e2 as ENUM ('X', 'Y', 'Z');

CREATE TABLE foo ( a e1 not null primary key );
CREATE TABLE bar ( a e2 not null primary key );

ALTER TABLE bar ADD CONSTRAINT fk_foo_bar
    FOREIGN KEY (a) REFERENCES foo (a);
ERROR:  foreign key constraint "fk_foo_bar" cannot be implemented
DETAIL:  Key columns "a" and "a" are of incompatible types: e2 and e1.


The operating system returned error 665(The requested operation could not be completed due to a file system limitation) on SQL Server 2014 Have a SQL Server 2014 instance running on Windows 2012 R2 that was running Ola Hallengren's DB integrity script.  This is the error that resulted:

  The operating system returned error 665(The requested operation could
  not be completed due to a file system limitation) to SQL Server during
  a write at offset 0x0001392f6ee000 in file
  'H:\MSSQL12\Data\EDW_Data_6.ndf_MSSQL_DBCC15'. Additional messages in
  the SQL Server error log and system event log may provide more detail.
  This is a severe system-level error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online.

I checked both the event log and the SQL Server error log but not getting a lot of additional info that could help. I haven't rerun the DBCC check for this db yet.  This database is pretty active as it is a data warehouse database.  It's currently 10.5 TB.  I've heard that if I rerun the dbcc it may clear out the MSSQL_DBCC15 however I'm hesitant to do that on this server due to the level of activity.  My preference would be to copy the backup to another server and run the dbcc there.  If I do that, is there any way for me to clean up the DBCC15 files left on disk on the primary server?

Any other places I could find more info other than event log and SQL Server error log?

Appreciate any thoughts on what could cause this error or the best option for running DBCC checkdb based on the situation.

DBCC creates a database snapshot, and database snapshots are implemented using NTFS sparse files. Error 665 reported for DBCC has been reported before, and blogged about by the CSS team, read:

SQL Server reports operating system error 1450 or 1452 or 665 (retries)
Sparse File Errors: 1450 or 665 due to file fragmentation: Fixes and Workarounds

The articles linked show the problem details, and the applicable fix (depends on your OS version and configuration).


INSERT INTO using the RETURNING values from another INSERT I'd like to do something like this:

INSERT INTO table2(name)
  INSERT INTO table1(name, address) VALUES ('me', 'home')
  RETURNING name;

And then I would expect a new record in table2 with 'me' in name. This would work fine with a SELECT returning the values, but I'm having trouble with the nested insert.

Use a data modifying CTE:

with t1 (name) as (
  INSERT INTO table1 (name, address) VALUES ('me', 'home')
  returning name
)
insert into table2 (name)
select name
from t1;


regarding transaction commit in mysql? My server configuration has auto_commit = off which helps in large transaction while inserting and updating but what if innodb_autoinc_lock_mode = 1 as my tables does not have primary key with auto_increment? will it help in faster results or do we need to make to 2? is there any way to help it?

Beyond some point, waiting too long to COMMIT slows things down.  Think of it as having to save more and more "undo" stuff.

innodb_autoinc_lock_mode only applies to AUTO_INCREMENT.

It seems that your real question is about high speed ingestion.  Are you INSERTing one row at a time?  You can get 10x speedup if you insert 100 at a time.  That is, a single INSERT with 100 rows.  Are you inserting from multiple connections?  Etc.  Here's one discussion on high speed ingestion.


Does the MongoDB background flush (MMAP) update the entire document even if only a small portion has changed? i.e. set on an array position We are currently trying to get to the bottom of some high background flush times on our MongoDB installation. As part of this investigation we have been looking at the $set operation we perform on an array embedded in one of our document types. The array contains 348 empty documents to begin with, and over the course of a week these array element will have sub-documents inserted (if empty) and then subsequently updated (if they already exist). The sub-documents are approximately 100 bytes in size and are not indexed.

So my question is, when the document is flushed to disk, what actually gets written, the entire document or just the sub-document that has been updated?

The array contains 348 empty documents to begin with, and over the course of a week these array element will have sub-documents inserted (if empty) and then subsequently updated (if they already exist). The sub-documents are approximately 100 bytes in size and are not indexed.

One consideration with this use case is that your documents are consistently growing. MongoDB used a record allocation or padding strategy to allow documents to grow in-place. For example, if your document starts off as 1000 bytes MongoDB 2.6 or newer will round this up to a 1024 byte record allocation for MMAP (as per the Power of 2 Size default strategy). Updates that don't grow the size of the document beyond the current record allocation are more efficient for the server to execute.

However, if you added 100 bytes to a document which was initially 1000 bytes, the document would have to be moved to a new record allocation in storage (and associated index entries would also have to be updated). So in this example, the next allocation for a 1100 byte document would be 2048 bytes (allowing for ~9 more 100 byte fields to be added before a new record allocation was needed for this document). Indexes in MongoDB include the storage location of the document, so a document move will result in an update for every index entry referencing that document.

You can check the frequency of document moves by looking at the nmoved value for slow updates (or by enabling increased levels of logging / system profiling). Frequent document moves can definitely have a performance impact. Common strategies include either reconsidering the data model (eg. moving the growing portion of the document to a separate collection if appropriate) or adding manual padding to the initial document allocation. The default power of 2 allocation strategy is designed to avoid the need for manual padding in most cases, but if your documents start small and grow quickly you might be able to avoid some initial document moves.

  So my question is, when the document is flushed to disk, what actually gets written, the entire document or just the sub-document that has been updated?

The answer will depend on the size of your document and the nature of updates since the last background flush. I'll assume you are using a default configuration with MMAP storage engine and journal enabled.

By default data changes are written twice: once to fast append-only journal files (committed to disk every 100ms) and again to a private view in memory (flushed to data files every 60s). The background flush process is a periodic asynchronous write of all pages that have been "dirtied" in memory since the last flush. Journal commit and background flush intervals can be influenced by both server configuration and write concerns. For a good overview of the process see How MongoDB’s Journaling Works.

The MMAP storage engine will fetch the full document into memory before applying updates. The standard x86 page size is 4KiB so a single document may be represented by one or more pages -- or multiple documents may be part of a single page in memory.

So, if you are updating a single document the writes will include:

all changes written to the journal
all changes written to the oplog (if that node is part of replica set)
any pages dirtied for that document since the last background flush

An important caveat is "since the last background flush". Multiple updates affecting the same pages within a given sync interval will effectively be batched.

If you're trying to get to the bottom of performance issues then consistently high background flush times (particularly as a large or increasing percentage of the default 60s flush interval) are definitely of concern, but should be reviewed in the context of other metrics such as page faults, I/O stats, and lock percentage. I would also review the MongoDB Production Notes for general tips and upgrade to the latest MongoDB production release for your major version (i.e. latest 2.6.x or 3.0.x if there's a newer x than your current version).


MySQL Slave replication: Need to determine where replication left off to start slave in correct location Idiotically I rebooted a machine that is used as a MySQL slave without running STOP SLAVE, FLUSH TABLES first.

I had thought MySQL automatically handles all of this during a machine reboot, but apparently it doesn't, at least not in the configuration I was using, because the SLAVE doesn't start back up.

mysqld does start but there is an error message in the logs indicating the slave portion stops due to duplicate primary key issues.  Meaning it is trying to insert data that has already been added.

Here are the errors generated in the mysql log:

120104 11:07:54 [Warning] Slave: Duplicate entry '94459' for key 'PRIMARY' Error_code: 1062
120104 11:07:54 [ERROR] Error running query, slave SQL thread aborted.
    Fix the problem, and restart the slave SQL thread with "SLAVE START".
    We stopped at log 'mysql1-bin.000362' position 3384732

How do I determine where in the master binary log the process needs to start from to run a CHANGE MASTER statement?  I know I could potentially, skip log entries using the sql_slave_skip_counter but without knowing how many to skip I would need to go one by one, and that could take all day.

The Primary thing you need to do was left in a comment:

  Follow the suggestion in Duplicate entry in MySQL slave:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

However, here is how you can start off at the better position with the CHANGE MASTER TO command.

Let's take a look at a sample SHOW SLAVE STATUS\G

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.4.16.245
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001527
          Read_Master_Log_Pos: 554619670
               Relay_Log_File: relay-bin.004561
                Relay_Log_Pos: 554619815
        Relay_Master_Log_File: mysql-bin.001527
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: phpmyadmin
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 554619670
              Relay_Log_Space: 554620007
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

Please take note of Relay_Master_Log_File and Exec_Master_Log_Pos. These represent the log file and the position of the last SQL statement that was completed on the Master that is next in line to be executed on the Slave.

So, the CHANGE MASTER TO in this case would be:

CHANGE MASTER TO master_log_file='mysql-bin.001527',master_log_pos=554619670;

For your particular case, here is what you need to do on the Slave Server:

Step 01) Start mysql up with replication disabled at startup

$ service mysql restart --skip-slave-start

Step 02) Login to mysql and show the slave status:

mysql> SHOW SLAVE STATUS\G

Step 03) Get Relay_Master_Log_File and Exec_Master_Log_Pos from SHOW SLAVE STATUS\G

Step 04) Run CHANGE MASTER TO command using Relay_Master_Log_File and Exec_Master_Log_Pos

This will erase any relay logs collected and start collecting with a fresh, empty relay log.

Step 05) mysql> START SLAVE;

Step 06) mysql> SHOW SLAVE STATUS\G repeatedly to watch Seconds_Behind_Master go to 0

Step 07) If replication breaks due to Error 1062 (Duplicate Key), now you can implement @DTest's suggestion. Then, goto Step 06. Repeat this until Seconds_Behind_Master go to 0.

  We stopped at log 'mysql1-bin.000362' position 3384732.

That's what you use:

CHANGE MASTER TO master_log_file='mysql1-bin.000362'. master_log_pos=3384732;

If you ran RESET SLAVE on the Slave, do not despair. Just run the full syntax version of the CHANGE MASTER TO command:

CHANGE MASTER TO
MASTER_HOST='IP Address of Master',
MASTER_PORT=3306,
MASTER_USER='Replication Username',
MASTER_PASSWORD='Replication Password',
MASTER_LOG_FILE='mysql1-bin.000362',
MASTER_LOG_POS=3384732;


SQL Server database encoding My database in SQL Server 2008 went into suspect mode, and I fixed it by changing to emergency mode then I used this query

DBCC CHECKDB (Test, REPAIR_ALLOW_DATA_LOSS);

And now my data letters in my tables changed from Arabic to question marks, I want to recover the encoding without dropping the tables or losing the data

You have already damaged the database by running REPAIR_ALLOW_DATA_LOSS. You need a professional to asses the loss in your database and see what is recoverable, and how. Contact product support.

Next time know that the most appropriate action is to restore now from a recent non-damaged backup.


MySQL Daemon failed to start - Can't find file: './mysql/plugin.frm' Fresh install of CentOS 6 on a test box, ran following as root:

yum install mysql-server    #success
service mysqld start        #success
service mysqld stop         #success
cd /var/lib/
cp -rp mysql mysql.bak
rm -rf mysql
cp -rp mysql.bak mysql
service mysqld start

MySQL Daemon failed to start.

cat mysqld.log

/usr/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)

ls -l
drwxr-xr-x. 4 mysql   mysql   4096 May  1 13:26 mysql
drwxr-xr-x. 4 mysql   mysql   4096 May  1 13:23 mysql.bak

Directories are the same

diff -r mysql mysql.bak

No differences

ls -lR mysql > out1
ls -lR mysql.bak > out2
diff out1 out2

No differences

I also looked manually and every file in mysql vs mysql.bak has the same ownership mysql:mysql & same permissions.

Ive seen the previous SO issue like this, but that didn't help.

Any ideas?

The answer maybe in the question. "Can't find file: './mysql/plugin.frm'"

Make sure that file exists and permissions are correct, which you did.
Next, SELinux may interfering (see this post)

You may test the SELinux theory by running the following:

setenforce 0
service mysql start

To permanently turn off SELinux, follow the steps here.


When to use mysqldump with '--no-autocommit'? My benchmark shows slower restore I tested quick and dirty --no-autocommit vs. without this option on MacBook (installed with Homebrew latest MariaDB). I restored to an empty MariaDB a 4.6 GB *.sql file. The dump made without --no-autocommit took 5m42.072s to restore and with --no-autocommit it took 1 minute longer.

Other options used for dump are --max_allowed_packet=1G --flush-logs --single-transaction --all-databases

set autocommit=0;
INSERT INTO ...
UNLOCK TABLES;
commit;

When to use --no-autocommit? In what use case this option makes sense?

With autocommit=1, each INSERT will be committed before moving on to the next.  There is less overhead.  But, if the server crashes, the table will be half built because some INSERTs will be committed; some won't.

With

SET autocommit=0;
INSERT...
INSERT...
...
commit;

it will put all the info into a redo log for the entire table.  For large tables, this is more overhead.  Even for a tiny tables (only one INSERT), it might be a little slower due to the extra two statements.


TINYINT vs Nullable BIT performance in MS SQL Server We need three modes to display the status of users in the database!
We can do it in two ways

Nullable BIT:

Null: Not checked

0: Not accepted

1: accepted

TINYINT

0: Not checked

1: accepted

2: Not accepted

Which data type is better when we need to represent three values?

It's highly unlikely that there is a material performance difference here.

So the choice is between

create table AppUser
(
  Id int identity primary key,
  Name nvarchar(200) not null,
  UserAccepted bit null
)

And

create table UserStatus
(
  Id tinyint primary key,
  Description varchar(200)
)

create table AppUser
(
  Id int identity primary key,
  Name nvarchar(200) not null,
  UserStatus tinyint not null references UserStatus default 0
)

And while one might be preferable in your particular application, they are both reasonable designs.


Get the matching condition in the SELECT list Let's say my full text search query is below:

USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO

I need one more column that shows which condition was matched in the CONTAINS clause.

SELECT Name ,Matched_condition
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');

+---------------------+-------------------+
| name                | matched_condition |
+---------------------+-------------------+
| chain smokers       | chain             |
+---------------------+-------------------+
| full throttle       | full              |
+---------------------+-------------------+
| chain full of smoke | chain,full        |
+---------------------+-------------------+

What should I use to get the new column (matched_condition)?

I have a list of conditions that includes 200 words.

Possible realization. Fulltext search replaced with common LIKE substring search.

Source table:

CREATE TABLE test (val VARCHAR(16));

INSERT INTO test VALUES
('1 chaining'),
('2 fullfill'),
('3 full chain'),
('4 no pattern');

Patterns table (can be temporary or table-type variable):

CREATE TABLE patterns (pattern VARCHAR(8));

INSERT INTO patterns VALUES
('%chain%'),
('%full%');

The query:

SELECT test.val, STUFF( ( SELECT ',' + pattern
                          FROM ( SELECT test.val, patterns.pattern
                                 FROM patterns
                                 WHERE test.val LIKE patterns.pattern
                               ) tmp
                          FOR XML PATH('')
                        ), 1, 1,''
                      ) matched_patterns
FROM test;

Query result:

    val      | matched_patterns
------------ | ----------------
1 chaining   | %chain%
2 fullfill   | %full%
3 full chain | %chain%,%full%
4 no pattern |

fiddle


In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index? Update 2014-12-18

With the overwhelming response to the main question being "No", the more interesting responses have focused on part 2, how to solve the performance puzzle with an explicit ORDER BY.  Although I've marked an answer already, I wouldn't be surprised if there were an even better performing solution.

Original

This question arose because the only extremely fast solution I could find to a particular problem only works without an ORDER BY clause. Below is the full T-SQL needed to produce the problem, along with my proposed solution (I am using SQL Server 2008 R2, if that matters.)

--Create Orders table
IF OBJECT_ID('tempdb..#Orders') IS NOT NULL DROP TABLE #Orders
CREATE TABLE #Orders
(
       OrderID    INT NOT NULL IDENTITY(1,1)
     , CustID     INT NOT NULL
     , StoreID    INT NOT NULL
     , Amount     FLOAT NOT NULL
)
CREATE CLUSTERED INDEX IX ON #Orders (StoreID, Amount DESC, CustID)

--Add 1 million rows w/ 100K Customers each of whom had 10 orders
;WITH
    Cte0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    Cte1 AS (SELECT 1 AS C FROM Cte0 AS A, Cte0 AS B),--4 rows
    Cte2 AS (SELECT 1 AS C FROM Cte1 AS A ,Cte1 AS B),--16 rows
    Cte3 AS (SELECT 1 AS C FROM Cte2 AS A ,Cte2 AS B),--256 rows
    Cte4 AS (SELECT 1 AS C FROM Cte3 AS A ,Cte3 AS B),--65536 rows
    Cte5 AS (SELECT 1 AS C FROM Cte4 AS A ,Cte2 AS B),--1048576 rows
    FinalCte AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Cte5)
INSERT INTO #Orders (CustID, StoreID, Amount)
SELECT CustID = Number / 10
     , StoreID    = Number % 4
     , Amount     = 1000 * RAND(Number)
FROM  FinalCte
WHERE Number <= 1000000

SET STATISTICS IO ON
SET STATISTICS TIME ON

--For StoreID = 1, find the top 500 customers ordered by their most expensive purchase (Amount)

--Solution A: Without ORDER BY
DECLARE @Top INT = 500
SELECT DISTINCT TOP (@Top) CustID
FROM #Orders WITH(FORCESEEK)
WHERE StoreID = 1
OPTION(OPTIMIZE FOR (@Top = 1), FAST 1);
--9 logical reads, CPU Time = 0 ms, elapsed time = 1 ms
GO
--Solution B: With ORDER BY
DECLARE @Top INT = 500
SELECT TOP (@Top) CustID
FROM #Orders
WHERE StoreID = 1
GROUP BY CustID
ORDER BY MAX(Amount) DESC
OPTION(MAXDOP 1)
--745 logical reads, CPU Time = 141 ms, elapsed time = 145 ms
--Uses Sort operator

GO

Here are the execution plans for Solution A and B, respectively:

Solution A gives the performance I need, but I couldn't get it to work with the same performance when adding any kind ORDER BY clause (e.g., see Solution B).  And it certainly seems like Solution A would have to deliver its results in order, since 1) the table has only one index on it, 2) a seek is forced, thus eliminating the possibility of its using an allocation order scan based on IAM pages.

So my questions are:

Am I right that it will guarantee the order in this case without an order by clause?
If not, is there another method to force a plan that is as fast as Solution A, preferably one that avoids sorts? Note that it would have to solve the exact same problem (for StoreID = 1, find the top 500 customers ordered by their most expensive purchase amount). It would also have to still use the #Orders table, but different indexing schemes would be OK.

Am I right that it will guarantee the order in this case without an order by clause?

No. A Flow Distinct that preserves order (allowing ORDER BY without a sort) is not implemented in SQL Server today. It is possible to do in principle, but then many things are possible if we are allowed to change the SQL Server source code. If you can make a good case for this development work, you could suggest it to Microsoft.

  If not, is there another method to force a plan that is as fast as Solution A, preferably one that avoids sorts?

Yes. (Table & query hints only required when using the pre-2014 cardinality estimator):

-- Additional index
CREATE UNIQUE NONCLUSTERED INDEX i
ON #Orders (StoreID, CustID, Amount, OrderID);

-- Query
SELECT TOP (500)
    O.CustID,
    O.Amount
FROM #Orders AS O
    WITH (FORCESEEK(IX (StoreID)))
WHERE O.StoreID = 1
AND NOT EXISTS
(
    SELECT NULL
    FROM #Orders AS O2
        WITH (FORCESEEK(i (StoreID, CustID, Amount)))
    WHERE
        O2.StoreID = O.StoreID
        AND O2.CustID = O.CustID
        AND O2.Amount >= O.Amount
        AND
        (
            O2.Amount > O.Amount
            OR
            (
                O2.Amount = O.Amount
                AND O2.OrderID > O.OrderID
            )
        )
)
ORDER BY
    O.Amount DESC
OPTION (MAXDOP 1);

(500 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

SQL CLR solution

The following script shows using a SQL CLR table-valued function to meet the stated requirements. I am not a C# expert, so the code may bear improvement:

USE Sandpit;
GO
-- Ensure SQLCLR is enabled
EXECUTE sys.sp_configure
    @configname = 'clr enabled',
    @configvalue = 1;
RECONFIGURE;
GO
-- Lazy, but effective to allow EXTERNAL_ACCESS
ALTER DATABASE Sandpit
SET TRUSTWORTHY ON;
GO
-- The CLR assembly
CREATE ASSEMBLY FlowDistinctOrder
AUTHORIZATION dbo
FROM [encoded data, 12290 characters]
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
-- The CLR TVF with order guarantee
CREATE FUNCTION dbo.FlowDistinctOrder
(
    @ServerName nvarchar(128),
    @DatabaseName nvarchar(128),
    @MaxRows bigint
)
RETURNS TABLE
(
    CustID integer NULL,
    Amount float NULL
)
ORDER (Amount DESC)
AS EXTERNAL NAME FlowDistinctOrder.UserDefinedFunctions.FlowDistinctOrder;

Test table and sample data from the question:

-- Test table
CREATE TABLE dbo.Orders
(
    OrderID    integer  NOT NULL IDENTITY(1,1),
    CustID     integer  NOT NULL,
    StoreID    integer  NOT NULL,
    Amount     float    NOT NULL
);
GO
-- Sample data
WITH
    Cte0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    Cte1 AS (SELECT 1 AS C FROM Cte0 AS A, Cte0 AS B),--4 rows
    Cte2 AS (SELECT 1 AS C FROM Cte1 AS A ,Cte1 AS B),--16 rows
    Cte3 AS (SELECT 1 AS C FROM Cte2 AS A ,Cte2 AS B),--256 rows
    Cte4 AS (SELECT 1 AS C FROM Cte3 AS A ,Cte3 AS B),--65536 rows
    Cte5 AS (SELECT 1 AS C FROM Cte4 AS A ,Cte2 AS B),--1048576 rows
    FinalCte AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Cte5)
INSERT dbo.Orders
    (CustID, StoreID, Amount)
SELECT
    CustID  = Number / 10,
    StoreID = Number % 4,
    Amount  = 1000 * RAND(Number)
FROM FinalCte
WHERE
    Number <= 1000000;
GO
-- Index
CREATE CLUSTERED INDEX IX
ON dbo.Orders
    (StoreID ASC, Amount DESC, CustID ASC);

Function test:

-- Test the function
-- Run several times to ensure connection is cached
-- and CLR code fully compiled
DECLARE @Start datetime2 = SYSUTCDATETIME();

SELECT TOP (500)
    FDO.CustID
FROM dbo.FlowDistinctOrder
(
    @@SERVERNAME,   -- For external connection
    DB_NAME(),      -- For external connection
    500             -- Number of rows to return
) AS FDO
ORDER BY
    FDO.Amount DESC;

SELECT DATEDIFF(MILLISECOND, @Start, SYSUTCDATETIME());

Execution plan (note the validation of the ORDER guarantee):

On my laptop, this typically executes in 80-100ms. This is nowhere near as fast as the T-SQL rewrite above, but it should show good performance stability in the face of different data distributions.

Source code:

using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

public partial class UserDefinedFunctions
{
    private sealed class ReverseComparer<T> : IComparer<T>
    {
        private readonly IComparer<T> original;

        public ReverseComparer(IComparer<T> original)
        {
            this.original = original;
        }

        public int Compare(T left, T right)
        {
            return original.Compare(right, left);
        }
    }

    [SqlFunction
        (
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.None,
        FillRowMethodName = "FillRow",
        TableDefinition = "CustID integer NULL, Amount float NULL"
        )
    ]
    public static IEnumerable FlowDistinctOrder
        (
        [SqlFacet (MaxSize=128)]string ServerName,
        [SqlFacet (MaxSize=128)]string DatabaseName,
        long MaxRows
        )
    {
        var list = new SortedDictionary<double, int>
            (new ReverseComparer<double>(Comparer<double>.Default));

        var csb = new SqlConnectionStringBuilder();
        csb.ConnectTimeout = 10;
        csb.DataSource = ServerName;
        csb.Enlist = false;
        csb.InitialCatalog = DatabaseName;
        csb.IntegratedSecurity = true;

        using (var conn = new SqlConnection(csb.ConnectionString))
        {
            conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText =
                    @"
                    SELECT
                        O.CustID,
                        O.Amount
                    FROM dbo.Orders AS O
                    WHERE
                        O.StoreID = 1
                    ORDER BY
                        O.Amount DESC";

                int custid;
                double amount;

                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        custid = rdr.GetInt32(0);
                        amount = rdr.GetDouble(1);

                        if (!list.ContainsKey(amount))
                        {
                            list.Add(amount, custid);
                            if (list.Count == MaxRows)
                            {
                                break;
                            }
                        }
                    }
                }
            }
        }
        return list;
    }

    public static void FillRow(object obj, out int CustID, out double Amount)
    {
        var v = (KeyValuePair<double, int>)obj;
        CustID = v.Value;
        Amount = v.Key;
    }
}


Failure to log in using just the server name I have more than one PC connected by LAN that I access from Server Management Studio.

When I try to connect with the DB server, if I place the server IP instead of the server name in the serverName field, it works fine, and I can log in.

If I use the server name, why does logging in fail? How can I resolve this issue?

Check if server name you use resolved to valid IP address: ping SERVERNAME gives you the same IP address as you assumed it has to be


RMAN rename datafile created in an archivelog I'm trying to restore an Oracle database (11.2.0.4.0) to a test system with a different file layout. The production database is being backed up using RMAN, no catalog. We do a full backup of all the datafiles on Sunday (last one being Mar 9th) and then only backup the archivelogs every night during the week.

On 3/9 we had 11 datafiles. On 3/12 I created a new tablespace/datafile. When doing the restore today my general process has been:

Install Oracle software only, no initial database
Restore the SPFILE to a PFILE from the 3/9 backup and edit it. This is where I change the DB_DOMAIN and the new locations of the control files on the test server.
Startup nomount with the new PFILE.
Restore the control file from the 3/9 backup.
Mount the database. Using sqlplus I rename the redo logs to their new location.
Since the restored control files aren't aware of the backup pieces for 3/10 - 3/14, I run this for each missing day: CATALOG BACKUPPIECE '/path/to/backuppiece.bkp'
Finally I run this RMAN command to restore/recover the database (getting the SCN from LIST BACKUPSET):

RUN
{
SET UNTIL SCN 426265698;
SET NEWNAME FOR DATAFILE 1 TO '/var/oracle/data/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/var/oracle/data/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/var/oracle/data/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/var/oracle/data/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/var/oracle/data/orcl/foo01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/var/oracle/data/orcl/foo02.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/var/oracle/data/orcl/foo03.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/var/oracle/data/orcl/foo04.dbf';
SET NEWNAME FOR DATAFILE 9 TO '/var/oracle/data/orcl/foo05.dbf';
SET NEWNAME FOR DATAFILE 10 TO '/var/oracle/data/orcl/foo06.dbf';
SET NEWNAME FOR DATAFILE 11 TO '/var/oracle/data/orcl/foo07.dbf';
SET NEWNAME FOR DATAFILE 12 TO '/var/oracle/data/orcl/foo08.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

So this process generally has been working fine; datafiles present in the 3/9 backup restore to their new location. The problem is the tablespace I created on 3/12. When the recovery process gets to that point, RMAN outputs:

creating datafile file number=12 name=/opt/oracle/oradata/orcl/foo08.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/14/2014 13:24:41
RMAN-20505: create datafile during recovery
ORA-01119: error in creating database file '/opt/oracle/oradata/orcl/foo08.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

Now, I know that I could work around this by just creating the directory at the old location, letting RMAN restore into it, and moving the datafile later. But I'd like to understand why my last rename command isn't working, or what I need to change to get it to rename when restoring the archivelog.

SET NEWNAME works for RESTORE and SWITCH, but not for RECOVER. Datafiles had been renamed and switched before the recovery started.

There is a flaw in your process though, and the above issue is its side effect:

  Restore the control file from the 3/9 backup.

Why? You do a nearly complete recovery to get as close as possible to the current state, not the state on 3/9. Restore the controlfile from the latest backup taken on 3/14.

  Since the restored control files aren't aware of the backup pieces for 3/10 - 3/14, I run this for each

This step is unnecessary if you use the latest controlfile.


How Can I Access the postgres database in a Script Executing pg_dumpall? I want to run the following script using my role which includes the postgres database.

pg_dumpall -c -f ~/Desktop/pgdumpall_proddb_`date +%Y%m%d_%H%M%S`.sql

Here are the settings in pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local    all            all                                     md5
# IPv4 local connections:
host     all            all            127.0.0.1/32             md5
# IPv6 local connections:
host     all            all            ::1/128                  md5

Initially when I wrote this script I had the methods in pg_hba.conf set to trust. The script ran fine at that time. However when I changed the methods to md5 the script fails with an authentication error when it tries to access the postgres database. The password on my role works when access the databases that I created.

I need to either connect as user postgres to access the postgres database in my script or give access to the postgres database to my role. I would prefer the latter since my script is executed by my role.

I found the following example in the PostgreSQL documentation for pg_hba to allow access to the postgres database to all users with a valid password which is what I want to do.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.12.10/32        md5

I am using a Mac Mini Server where my Rails applications using PostgreSQL are hosted. I have the external IP address (IPv4) that I use to remotely access my server at the colocation site. Is this the IP address I should use in pg_hba.conf or should I use the router IP address? If I add this statement to pg_hba.conf will I need to modify the existing IPv4  statement to accommodate this new rule?

I decided to modify my pg_hba.conf file to the following:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    postgres        all             127.0.0.1/32            md5
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

I'm now able to execute my script using my role and successfully access the postgres database.


Referencing label from Select clause in where, simple sql query Consider I'm having a query such as:

SELECT student.name, MAX(grades.final_grade) as max_grade
From grades , students
where student.id == grade.student_id and max_grade == 90

Is this query wrong and won't work, because it's illegal to acess label mentioned in select in a where clause?

"Aggregates" cannot be referenced in WHERE.  But they can be moved to HAVING:

SELECT  student.name, MAX(grades.final_grade) as max_grade
    From  grades , students
    where  student.id = grade.student_id
    HAVING  max_grade = 90

Also it is =, not ==.


Simple logging of table access In SQL Server 2008, is there some simple logging of how often a particular table is selected (in an hour, a day, a week, etc.)? I have a large table that is accessed by dozens of applications and scores of web pages. The data provider is threatening to make major changes to what is available and I need to start working on a demonstration of how much the data is accessed.

Assuming that there is an index on the table in question, you could try querying sys.dm_db_index_usage_stats.  It's a view so you will need to specify the database and object_ID of the table you are interested in.  You can query at intervals and store the results to get delta over time.  Look at the MSDN page https://msdn.microsoft.com/en-us/library/ms188755(v=sql.100).aspx for how to interpret the information.


Facing error while trying to create a function using 'ACCESSIBLE BY' clause in PL/SQL Hi everyone hope you're doing well
I'm new to oracle PL/SQL and I'm trying to write a function using 'ACCESSIBLE BY' clause but I receive errors .
this is my function

create or replace function Show_Description
(i_Course_Number course.course_no%Type) Return Varchar2
  ACCESSIBLE BY(function show_binary)

   AS

     V_Description Varchar2(50);
  Begin
      Select description
      into V_Description
      from course
      Where course_no = i_Course_Number;
   Return V_Description;

  Exception

   When No_Data_Found Then
     Return('Course Is not in the database');
   When Others Then
     Return('Error in running Show_Description Function');

END;

I want to know if it's even possible to use 'ACCESSIBLE BY' clause with a function.this the error I receive : ORA-00922 :missing or invalid option

ACCESSIBLE BY is a 12c feature. Based on your previous questions, you have a 11g database.


What might be the reason for licence compliance violation? Much to my surprise we got a licence compliance violation for our DB2 Workgroup Server Edition installation regarding:

~]$ db2licm -g tmp.txt
~]$ cat tmp.txt

 License Compliance Report
 DB2 Workgroup Server Edition   Violation
    Materialized Query Tables
    Multidimensional Clustering Tables

Since we are not using any of these I was kind of puzzled, how can this happen?

One possible cause might be that someone created a sample database using db2sampl. The sample database contains both of these artefacts:

]$ db2 -x "select tabschema, tabname, type, clustered
           from syscat.tables
           where type = 'S' or clustered = 'Y'"

DB2INST1    ADEFUSR    S    -
DB2INST1    EMPMDC     T    Y

It appears as if something in the instance is flipped during create of sample database, dropping the sample database does not help, the violation persists.

As @Chris Aldrich points out in the comment, db2licm -x does a reset of the license report:

DB2 Workgroup Server Edition   In compliance

Out of curiosity I created 1 MQT to see if the violation re-appears. So far it has not. It's now more than 12 hours since I created the MQT and I'm still in compliance.

I'm still puzzled by how the licence compliance mechanism works, but I'll add another question about that.


How can I select records which are between 2 dates based on a date in a field in PostgresSQL? I have two tables:

1 with an Unique ID with the first time they visited (in date format)
and a historical addresses table with the same Unique ID, a start field (date field), an end field (date field) and the addresses fields.

What I like to do is create a table which get the addresses of the persons (Unique ID) based on the date where they lived at the time of the visit.

I'm able to select records with my own made up dates, but I would like to get them from the 'first time visit' field.

The SQL query with random dates:

SELECT * FROM historical_adresses
WHERE (startdate, enddate) OVERLAPS ('2013-05-15'::DATE, '2013-05-15'::DATE);

Who can help me do this in PostgresSQL?

If I understand your problem correctly, you have to join the other table to historical_adresses based on the unique ID and that the visit time falls between startdate and enddate.

If this is so, the query could look like:

SELECT [address_elements]
FROM historical_adresses AS ha
JOIN visits AS v
    ON ha.user_id = v.user_id
    AND v.visit_time BETWEEN ha.startdate AND ha.enddate
WHERE [find the visit somehow that you are interested in];

Note: you usually don't need all columns of a table (and definitely not from a join), so use the actual column names rather than *.


Permissions issue in Docker SQL Server 2017 while restoring certificate Docker SQL Server 2017 container @latest. Using master database.

The error I am facing is the following:

[S00019][15208] The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

The closest thing I have found to this exact question is this issue on Stackoverflow. However the answer doesn't work for me. This question has a similar answer.

I have also tried the instructions here, and here.

So going through the parts of the error:

I have recreated the files twice, so I don't think it's the "invalid" part. And it's obviously not the "does not exist" part (if I put in the wrong password, it tells me it's the wrong password).
I have backed up and restored the SMK and Master Key without issue, so I don't think it's the permissions issue. The files have the exact same permissions.

I can't get the certificate to restore no matter what I try. I have searched the GitHub issues to no avail so I don't think it's a bug. I must be doing something wrong.

Relevant code:

--on Prod
BACKUP CERTIFICATE sqlserver_backup_cert
TO FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.cer'
WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.key',
    ENCRYPTION BY PASSWORD = 'foobar'
    )

--on Test
CREATE CERTIFICATE sqlserver_backup_cert
FROM FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.crt'
WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/certs/sqlserver_backup_cert.key',
    DECRYPTION BY PASSWORD = 'foobar'
    )

It's noteworthy that /var/opt/mssql/certs is a Docker volume. However I have also tried creating my own directory inside the container and using docker cp. No change.

Is the code shown under "Relevant code:" (in the question) the exact code being executed, as in copied and pasted into here and not re-typed? I ask because the file extension on the sqlserver_backup_cert file is different between the BACKUP and CREATE (i.e. restore) statements. It is backed-up as .cer but then you are looking for .crt in the CREATE.

Also, have you tried skipping files altogether and using hex bytes (i.e. a VARBINARY literal)? You would do the following:

On Prod

SELECT CERTENCODED(CERT_ID(N'sqlserver_backup_cert')) AS [CertificateAndPublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PrivateKeyTest'),
                      'new_password_for_extract(A)',
                      'current_password(B)') AS [PrivateKey];

On Test

CREATE CERTIFICATE [sqlserver_backup_cert]
    FROM BINARY = {output of CERTENCODED}
    WITH PRIVATE KEY (
        BINARY = {output of CERTPRIVATEKEY},
                 DECRYPTION BY PASSWORD = 'new_password_for_extract(A)',
                 ENCRYPTION BY PASSWORD = 'current_password(B)'
                     );

UPDATE

O.P. has confirmed that the issue was indeed the filename extension mismatch.


Access to database denied :ELEPHANTSQL on Bluemix - “No pg_hba.conf entry for host localhost, user ADMIN, database SYSTEM” While trying to connect to my ElephantSQL database on Bluemix using pgadmin, it throws the following error.

Access to database denied
The server doesn't grant access to the database: the server reports
FATAL: no pg_hba.conf entry for host "XX.XXX.XX.XX", user "abcd", database "postgres", SSL on FATAL: no pg_hba.conf entry for host "XX.XXX.XX.XX", user "abcd", database "postgres", SSL off

To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.

How can I access the pg_hba.conf file on bluemix?

The error message means you are already accessing pg_hba.conf. There is just no line granting access for your connection attempt. At least one of your provided details (host, user, database) does not match for any line in the file.

I am not familiar with ElephantSQL, but it seems to be a database hosting service providing standard PostgreSQL. Advice in the manual should apply. Here is a related answer with more explanation and links:

Something wrong in pg_hba.conf


Convert varchar column to datetime In SQL Server, I have a varchar column with value such as:

 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22

How do I convert this column to a datetime column with real datetimes?
If this is not possible, how do I just create a new column and copy this values into it but as datetime values?

Assuming dates in other months are truncated to 3-char months:

 ALTER TABLE dbo.tablename ADD newcol DATETIME;

 UPDATE dbo.tablename
   SET newcol = CONVERT(DATETIME,
    REPLACE(LEFT(varchar_col, 11), '/', ' ')
        + ' ' + RIGHT(varchar_col, 8), 113);

Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.


How can I solve java lang unsatisfiedlink error when runing jdeveloper? I cannot run Jdeveloper. My java is in /usr/java
How can I fix this? I run rpm --query --whatprovides 'libX....' but it says no package provides this

Take a look here - the answer appears to correspond to your needs. It appears to be more of a progamming question than a DBA one, but we're here to help! :-).

Furthermore, a quick Google of sudo yum install libXext got me to this link. Please take the statckexchange site tour and in particular, check out the "help us to help you" blog - both available at the bottom of the page on the left.


What is the best practice when creating boolean columns in Oracle? I faced again the serious problem, that Oracle simply doesn't have a boolean column type. I need to use char(1), or smallint, or some other.

What is the best practice, how to emulate boolean values in Oracle?

(Space consumption isn't important now - but a good cooperation with java/hibernate line were important).

I usually use a number(1) type combined with a check constraint:

some_flag number(1) not null check (some_flag in (1,0))

To make things crystal clear I also add a comment to that table:

comment on column some_table.some_flag is '0 is false, 1 is true';

so that the explanation on what "true" means can be seen when looking at the definition of the table.

I would avoid a character representation due to localization problems. But if you do, make sure you create the approriate check constraint. Because it's not clear if a char(1) would use T, Y, y, t (or even W or J which I have seen in German).


How to replace the string which has passed in procedure while using EXECUTE IMMEDIATE in Oracle? Can some one help me how to use the string inside a string? I need to use something without bound variables because I am passing all the input parameters from a procedure which I am using in my real requirement, so the values are fixed.

create or replace procedure Sample( rowsize number )
is
    v_result varchar2(100);
    v_str varchar2(200);
begin
    v_str := 'Select rtrim(legl_ent_name) || '' | '' ||rtrim(legl_ent_prd_grp) as heirarchy from legl_ent_regn where rownum < rowsize';
    EXECUTE IMMEDIATE v_str ;
    DBMS_OUTPUT.PUT_LINE(rowsize);

end;
/

I'm getting the error below when I run the procedure.

BEGIN
dbms_output.enable();
Sample(2);
END;

ERROR at line 1:
ORA-00904: "ROWSIZE": invalid identifier
ORA-06512: at "RATABASE.SAMPLE", line 7
ORA-06512: at line 3

create or replace procedure Sample(rowsize number)
is
    v_result varchar2(100);
    v_str varchar2(200);
begin
    v_str := 'Select rtrim(legl_ent_name) || '' | '' ||rtrim(legl_ent_prd_grp) as heirarchy from legl_ent_regn where rownum < :1';
    EXECUTE IMMEDIATE v_str USING rowsize;
    DBMS_OUTPUT.PUT_LINE(rowsize);
end;