SQL Server service account Windows privileges and rights My question is, if you create a new domain user account for each of the SQL Server processes, what permissions should be set for each account? Or does the SQL configuration manager actually take care of this, and I just had an unforeseen issue?

I quite often have to setup Microsoft SQL Server and wondered if anyone can provide advice on configuring the accounts the services should run as. IMO this has been vaguely documented by Microsoft, while they point you in the right direction I have never been able to find any concrete examples.

To summarize what I've seen so far:

For simple deployments\development environments it is OK to use the virtual account defaults the installer uses:
e.g. NT SERVICE\MSSQLSERVER

Avoid using the SYSTEM account, this is not secure.

For production and in domain environments it's recommended to use either a Managed Service Account, or create a domain user account (not an admin) for each service. Allegedly if you use a domain account at installation time the installer will set any required permissions for you.

If changing the service account on an existing install from a virtual account to a domain account the recommendation is to use the SQL Server configuration manager to set the new service accounts. Allegedly this will set any required permissions for you.

I just tried changing the service account in an existing install to a domain account and it would give me a logon failure until I granted the account log on as service permission, which contradicts the part where the SQL Server configuration manager will set any required permissions. (Although I'm not sure if a GPO may have interfered with setting this local security policy)

Microsoft does provide a list of the permissions that the SQL Server Setup grants on this page.

But it's not clear to me if that is something I should be doing manually for the user I create to run the service as, or whether using the SQL config manager should automatically set these permissions.

SQL Server 2014, Domain Controller is on Windows Server 2008 R2.

I quite often have to setup MS SQL Server and wondered if anyone can
  provide advice on configuring the accounts the services should run as.
  IMO this has been vaguely documented by Microsoft, while they point
  you in the right direction I have never been able to find any concrete
  examples.

It's actually documented quite thoroughly: http://msdn.microsoft.com/en-us/library/ms143504.aspx

Is there a part of that you're not sure about?

  For simple deployments\development environments it is OK to use the
  virtual account defaults the installer uses: e.g. NT
  SERVICE\MSSQLSERVER

This is going to depend on the environment. I, personally, hate finding a server someone setup using a local account and asking to get access to network resources some time in the future, among other issues.

  For production and in domain environments it's recommended to use
  either a Managed Service Account, or create a domain user account(not
  an admin) for each service.

Again, depends, but generally I would agree (a counter example would be availability groups where it makes sense to use a single domain account across all instances).

  Allegedly if you use a domain account at installation time the
  installer will set any required permissions for you.

Unless there is a failure, etc, it will do so. I'm not sure why the "Allegedly" part.

  If changing the service account on an existing install from a virtual
  account to a domain account the recommendation is to use the SQL
  Server configuration manager to set the new service accounts.
  Allegedly this will set any required permissions for you.

When changing any of the services for SQL Server, always use SSCM. Always. Period. It will set the permissions for the new account to the basics. If before the local system account was used and unrestricted permission to everything on the system was had, I would expect something to fail permissions after the change due to tighter controlled security. That's not a SQL Server SSCM fault, that's an admin fault of not granting proper EXTRA permissions (such as accessing a network share, restricted folders, items outside of the SQL Server install purview, etc.)

  I just tried changing the service account in an existing install to a
  domain account and it would give me a logon failure until I granted
  the account 'log on as service' permission, which contradicts the part
  where the SQL Server configuration manager will set any required
  permissions. (Although im not sure if a GPO may have interfered with
  setting this local security policy)

Sounds like a GPO is causing an issue (IMHO). Wouldn't be the first time :)

  So my question is, if you create a new domain user account for each of
  the SQL Server processes what permissions should be set for each
  account?

I would explicitly set any permissions outside those stated in the msdn link I have above (also given by @joeqwerty and in your OP). For example, on a "backup" folder on a network share, on a new drive added to hold new databases (where setup was already run but the drive didn't exist), etc.

  But it's not clear to me if that is something I should be doing
  manually for the user I create to run the service as, or whether using
  the SQL config manager should automatically set these permissions.

Unless something is extremely broken with the server, these shouldn't have to be manually given.


Sort by match of LIKE I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the results ordered by the best match of asked query.

I'll try to describe that by pseudo code

select
*
from
articles
where item_nale like '%'+@user_input +'%'
order by "BEST MATCH"

Since all your matches have to match the LIKE pattern in order to be included, the simple thing to do is assume that shorter values for the column you're matching are "better" matches, as they're closer to the exact value of the pattern.

ORDER BY LEN(item_nale) ASC

Alternatively, you could assume that values in which the match pattern appear earlier are "better" matches.

ORDER BY PATINDEX('%' + @user_input + '%', item_nale) ASC

Or you could combine the two; look for earliest matches first, and within those, prefer shorter values.

ORDER BY PATINDEX('%' + @user_input + '%', item_nale) ASC, LEN(item_nale) ASC

It's not as sophisticated as what full-text indexing does, and it only accounts for matching on a single column, but it gives decent enough results.


incorrect Estimated Number of Rows vs Actual number of rows I'm seeing a huge difference in the estimated number of rows vs actual number of rows.  I'm not sure if i'm reading this right, so does this mean that sql server estimated the number of rows returned from this index is 16, but the actual number of rows being returned is 565824?  if so, then is this a concern?  This is for SQL server 2016

If you include cd_key in your index, it won’t have to do those lookups and the estimates will have less of an impact. Otherwise, correcting that estimate is going to cause it to do a Clustered Index Scan, which could also be slow. cd_camp is already in there as it’s part of the clustered index key.

But really, the index you want here is:

CREATE CLUSTERED INDEX ixYearCamp ON campdiv (cd_campyr, cd_camp) include (cd_key);


Remove sub-string from WHERE clause I have a stored procedure that is using the SUBSTRING function in the WHERE clause.

SELECT DISTINCT
   ColumnBB,
   ColumnCC
FROM TableAA WITH (NOLOCK)
WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;

How can I prevent an index scan for this query and make it execute quickly?

If you can't modify the schema per db2's answer, then try:

WHERE
       ColumnAA LIKE @VariableA + '%'
   AND LEN(@VariableA) >= 17          -- safety check

Note that if @VariableA is shorter than 17 characters, then you may match unwanted strings using just a LIKE.  For example, 'The quick brown fox jumped over the lazy dog.' is LIKE 'The%', but that doesn't satisfy the requirements of your original SUBSTRING query.

Aaron also brings up a good point that the DISTINCT may be the ultimate culprit.  Why do you have it in there?  Is it something that should be replaced with an appropriate unique index?


Optimizing sorted table for join In our asset tracking system, I have the following 2 tables:

item
item_location_date

Where item_location_date has the following fields:

ild_item (FK to Item)
ild_location (FK to location)
ild_pallet (Nullable FK to pallet id)
ild_created_when (creation timestamp)

To get an item's current location, it's something like this:

select *
from item
cross apply (
    select top 1 *
    from item_location_date
    where ild_item = itm_id
    order by ild_date_created desc
) ild

This, however is relatively slow. Looking at the execution plan, the issue is with the sorting of the item_location_date table, it is using the correct clustered index, which I've defined as:

ALTER TABLE [dbo].[item_location_date] ADD  CONSTRAINT [PK_item_location_date] PRIMARY KEY CLUSTERED
(
    [ild_date_created] DESC,
    [ild_item] DESC,
    [ild_location] DESC
)

However, performance is still poor. Changing the query to:

select *
from
item inner join
(select *
 from (
        select *,
                ROW_NUMBER() over (PARTITION by ild_item order by ild_date_created desc) rn
        from item_location_date
    ) ild
    where rn = 1
) itm_location
on itm_location.ild_item = itm_id

Yields better results, changing the sort from a top-N-Sort to a normal sort.

This database is still very small and easilty fits in RAM with plenty to spare, but I'm concerned about the performance as the quantity of data increases.

Is there anything I'm missing? Do I have the index set up to return to return the results as efficiently as possible for this scenario?

In this query:

select *
from item
cross apply (
    select top 1 *
    from item_location_date
    where ild_item = itm_id
    order by ild_date_created desc
) ild

The correlated subquery is first filtered by ild_item and then ordered by ild_date_created to get the TOP 1. With this in mind, the index you need has to have ild_item first and ild_date_created second:

CREATE NONCLUSTERED INDEX NCI_item_location_date_item_date_created
    ON [dbo].[item_location_date] (ild_item, ild_date_created)

Do you really need to select all columns with *? If it's just a few, you could also INCLUDE these on the index so it becomes a covering index and reduces additional pages retrievals.

Your current clustered index is ([ild_date_created], [ild_item], [ild_location]), if you don't mind changing it to ([ild_item], [ild_date_created], [ild_location]) then it will be as fast as you could get, since it's ordered in the manner you need and being it clustered you won't require additional lookups to get the data. Make sure there aren't other queries that depend on the current order if you consider making this change.


Merge table from master slave replication I have a dual master-slave replication (server1 is master AND slave of server2).

After a problem with server1, the replication broke. After some skipped logs the replication came back  to life.
Then I ran a checksum and there was one table with DIFFs:

+----------------+---+-----+--------+------+-------+--------+-----------+
| TS             |ERR|DIFFS|ROWS    |CHUNKS|SKKIPED|TIME    |TABLE
+----------------+---+-----+--------+------+-------+--------+-----------+
| 09-25T21:09:39 | 0 | 2   |5447202 | 181  |0      | 967.269|db1.myTable
+----------------+---+-----+--------+------+-------+--------+-----------+

I don't want to do a simple table sync from server2 to 1 because I'm afraid there might be data in server1 that it isn't in server2.

So, is there a way to make a merge between tables in a master-slave replication?

You can use pt-table-sync with --verbose to generate the differential sql and verify the data from there. Whichever version you feel is correct you may choose  to keep.

I'm not aware of any tool which will merge, as such what will be the merge condition upon conflicts?

Writing on both nodes in master-master can cause bad bad data! I'd try to avoid that.


Transactional Replication from SQL Server to Azure SQL I'm attempting to setup transactional replication between an on-premises SQL Server and an Azure SQL database. Unfortunately, it looks like Azure SQL doesn't support SQL Server Replication, and Azure Data Sync seems like it's been abandoned (and even if not, it chokes on databases with over 200 tables).

What are some alternatives to setup continuous transactional replication between an on-premises SQL Server and an Azure SQL database? My goal is to only replicate a handful of tables, and I'd like it to happen in near-real-time (as opposed to on a schedule).

This is supported (if I understand your question correctly).

See Transactional Replication to Azure SQL DB now in public preview

Below is the article  written by Jean-Yves (JY) Devant, a Senior Program Manager for SQL Server.

Copying Entire Article ,so to avoid link rot

This new capacity of Transactional Replication is available starting with the following versions\updates of SQL Server:

Community Technology Preview (CTP) 3.0 of SQL Server 2016
SQL Server 2014 Service Pack 1 Cumulative Update 3
SQL Server 2014 RTM Cumulative Update 10
SQL Server 2012 Service Pack 2 Cumulative Update 8

This enables two main scenarios:

Migrate your data to Azure SQL DB with no downtime.
Bridge SQL Server on-premises/on VMs to Azure SQL DB.

The way we enable Azure SQL DB as a subscriber is by extending existing logic of Transactional Replication. From the perspective of your experience with it, the only difference you’ll notice is when you create a subscriber: you give the URL to your Azure SQL DB instead of giving the name of a server or an instance. Because we designed the feature so you have a seamless experience, and you already know about Transactional Replication, the learning curve to use this capacity is 0. It will be very easy and quick for you to start replicating your data to Azure SQL DB.

How does it work? There is "no replication" service per say in Azure SQL DB. The actual replication of data is performed through the Distribution agents.

The chart below shows the architecture from a high level.


Maintenance plan: Confirmed server as primary but switched to secondary during backups? While investigating a failed job that occurred over night I stumbled on to a novel situation that I thought was pretty interesting.

A server that was part of an AOA group was performing nightly backups. The first step is to determine whether the server was the primary or secondary. At the time the job began the server was the primary so it moved ahead to begin backing up the databases. While this was happening the server was bumped down to secondary and the backup jobs began failing as SQL won't allow full backups (without COPYONLY). This then lead to an alert from our monitoring solution.

Is it possible to continually check if the server is primary while performing backups?

We use ola hallengren backup scripts/jobs and that does check the cluster settings for each database.

For each database it will check, can I backup this database?, using the cluster settings and current cluster status. So if your AOAG is set to only allow backups from the Primary it wont backup a database that is currently a secondary.

Works great for us, the only issue we run into is the currently running DB backup will usually fail during a failover event because all connections are briefly severed.


Does it matter if id (in TABLE ) starts from 0 or from 10000 Sorry for my incorrect terminology (as I don't know what to call that)..
does it matter (with regard to performance) if unique (index) key starts from 0 and from i.e. 100000 ?

For example, if I have

ID     |  Brand
----------------
1      | BMW
2      | FERARRI

or

ID     |  Brand
----------------
100000 | BMW
100001 | FERARRI

will be there any difference for performance/queries? I ask that because I have a PHP function that deletes everything and inserts new values. Thus, delete/insert/delete/insert causes to increase the ID more and more, and I am afraid, if I will eventually meet a problem? Or that's not a problem?

Your database shouldn't care, and to it a number is a number is a number in this context. Performance will be the same either way here.

Just make sure that you select an appropriate data type so that you have enough room to "grow" and don't hit the upper bounds of that data type in your expected usage (e.g. don't use a smallint if you're going to need your IDs to go out to 2 billion).


Can someone help me with this PL/SQL block? Write a PL/SQL block to display the details of five employees who are getting the highest salary (table name is Employee)
Is this how you do it??

DECLARE
Cursor C is select * from Employee ORDER BY salary DESC;
Edet C%ROWTYPE;

BEGIN
 Open C;
 Loop
 fetch C into Edet;
 EXIT WHEN C%ROWCOUNT == 5;
 DBMS_OUTPUT.PUT_LINE(Edet);
 end loop;
 Close c;
 END;

Your code has a few syntax issues, it should be like this:

DECLARE
  Cursor C is
  select * from Employee ORDER BY salary DESC;
  Edet C%ROWTYPE;

BEGIN
 Open C;
 Loop
    fetch C into Edet;
    EXIT WHEN C%ROWCOUNT = 5;
    DBMS_OUTPUT.PUT_LINE(c.salary);
 end loop;
 Close c;
END;

However, there are some design issues.

You will get an error if table Employee has less than 5 records because C%ROWCOUNT = 5 does never become true.

If you have Oracle 12 running then you can simple do

select *
from Employee
ORDER BY salary DESC
fetch first 5 rows only;

For earlier releases you can do

select *
from (select e.*, ROWNUM as RN from Employee e ORDER BY salary DESC)
where rn <= 5;


Use of IN clause within a DELETE command - MySQL I am browsing through the official MySQL documentation to learn about the DELETE clause.
This page states

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

I can see from this WHERE, ORDER BY and LIMIT is allowed but there is no mention of IN

Therefore the following valid command

DELETE FROM WHERE column = z OR column = y OR column = x OR column = w

However I wrote the following command which execute's fine and does exactly the same as the above but looks better.(possibly executes faster too)

DELETE FROM table WHERE column IN (z,y,x,w)

Am I allowed to do this? I am wondering if maybe it works in this case but other cases would fail?
Also if this is fine should'nt the docs state it?

Yes, you can use IN() in the WHERE clause of a DELETE statement.

There is no "IN clause" -- IN() is just a comparison operator, perhaps slightly disguised as something more sophisticated.

http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#function_in

To illustrate what IN() is really doing, consider these examples in isolation.

mysql> SELECT 1 IN (2,3,4); -- should be false
+--------------+
| 1 IN (2,3,4) |
+--------------+
|            0 | -- is indeed false
+--------------+
1 row in set (0.00 sec)

mysql> SELECT 1 IN (2,1,4); -- should be true
+--------------+
| 1 IN (2,1,4) |
+--------------+
|            1 | -- is indeed true
+--------------+
1 row in set (0.00 sec)

...and, as @ypercube has pointed out, the "where_condition is an expression that evaluates to true for each row to be deleted".


Using PL/R for clustering geometries I have a Postgres/PostGIS database of geography points which I'm trying to cluster; meaning I want to group and count them by proximity. I have had first success with the kmeans Postgresql extension, but the algorithm is not optimal for what I require. I want to experiment with more algorithms, and R seems to be the best choice for this. I have set up PL/R and can call R functions from Postgres...

But what now? A simple R function which returns a scalar is no problem, but I'm not sure how to approach even the same kmeans solution in PL/R, yet alone implementing a whole clustering algorithm solution. The aforelinked kmeans extension is very approachable to me, with a query like:

    SELECT kmeans, count(*)
      FROM (
               SELECT kmeans(ARRAY[ST_X(geom), ST_Y(geom)], 5) OVER (), geom
                 FROM points
           ) AS ksub
  GROUP BY kmeans;

Since kmeans() only returns a single int this is very comprehensible to me. But I'm not sure where to start with some of the more complex R clustering solutions. Would it be better to post-process the entire result set in R, like SELECT r_cluster((SELECT ...))? Can I use a WINDOW function, in which case what would I return from R and how?

I'd like to see one actual code sample of a PL/R function declaration and invocation to get started on this.

-- please fill in the blanks

CREATE OR REPLACE FUNCTION r_cluster()
RETURNS ? AS $$ ? $$ LANGUAGE plr;

SELECT r_cluster(?) OVER (?) FROM points;

After some experimentation, I got this:

-- x and y are not actually used, they just give the function its signature
CREATE FUNCTION R_cluster_dbscan(x float8, y float8, eps float8)
RETURNS int
WINDOW
STRICT
VOLATILE
LANGUAGE plr
AS $$
    if (pg.state.firstpass == TRUE) {
        pg.state.firstpass <<- FALSE
        c <- fpc::dbscan(cbind(farg1, farg2), eps, MinPts = 2, method = "hybrid", seeds = FALSE)
        assign("cluster", c$cluster, env = .GlobalEnv)
    }

    return(cluster[prownum])
$$

This window function is called once for each row and returns the cluster id of the record, but it calculates the cluster only once using the DBSCAN algorithm and stores it in a global variable. It is used like:

  SELECT c                             AS cluster_id,
         COUNT(*)                      AS place_count,
         ST_Centroid(ST_Collect(geom)) AS center
    FROM (
             SELECT R_cluster_dbscan(ST_X(geom), ST_Y(geom), 1) OVER () AS c,
                    geom
               FROM points
         ) AS sub
GROUP BY c

Et voilà, a DBSCAN clustering implementation using PL/R. Not sure if this is the optimal implementation, but it's an implementation and hopefully some useful sample code.


How to sum the buy order and sell order in one row per each ticker I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker.

I have one table containing the following: iOrder 1 = Buy, -1 = Sell.

Ticker | iOrder | Price | Amount
1,1,90,180
1,1,89.5,179
1,-1,91,91
2,1,5,20
2,-1, 4,16
2,-1,3,9

My Expected resulting Table:

Ticker | BuyAmount |  SellAmount
1, 359, 91
2,20, 25

Ticker 1 Buy amount 359  =two Buy order with amount equals 180 and 179 , therefore sum of the buy order for ticker 1 = 180+179 = 359

I just know how to do one table containing Ticker and BuyAmount.

 select Ticker, sum(Amount) as BuyAmount
    from AAMMNews where iOrder = 1 group by Ticker

Using union or join? Please help, experts. Thanks.

below should help you :

CREATE TABLE dbo.Trading
        ([Ticker] int, [iOrder] int, [Price] int, [Amount] int)
    ;

INSERT INTO dbo.Trading
    ([Ticker], [iOrder], [Price], [Amount])
VALUES
    (1, 1, 90, 180),
    (1, 1, 89.5, 179),
    (1, -1, 91, 91),
    (2, 1, 5, 20),
    (2, -1, 4, 16),
    (2, -1, 3, 9)
;

SELECT Ticker
    ,sum(CASE
            WHEN iorder = 1
                THEN Amount
            ELSE 0
            END) AS BuyAmount
    ,sum(CASE
            WHEN iorder = - 1
                THEN amount
            ELSE 0
            END) AS SellAmount
FROM dbo.Trading
GROUP BY Ticker


Unable to access mysql database remotely I ran this command-

[root@dev-img mysql]# mysql -u root -h 192.168.1.181 -e "use mysql;"

and this is the output I got-

ERROR 1045 (28000): Access denied for user 'root'@'192.168.1.65' (using password: NO)

Is there anything wrong with my command? I've searched everywhere and the syntax seems to be as above.

Two possible problems

You didn't specify the -p option. Without -p the client will not prompt for a password and will attempt to connect without one
The database may not be configured to allow remote login for root.  You may have to login on the server and enable remote access (i.e. GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '...';)


Update multiple Ids when SET = (Sql Query) I want to update multiple records however the value being set is based on the primary key of the given record. This is not an issue with a single record as I can use:

UPDATE T1
     SET A1 = (SELECT TOP 1 X FROM T2 WHERE Y = ID1)
     WHERE A2 = ID1

The IN operator works fine for the WHERE clause as it will update the correct list but not in the sub query. In the SET it will get the top 1 from all values in the ID list and I just want it for that corresponding record/ID.

Using: Microsoft T-SQL

Thank You

Instead of specifying the same IN list for both Y and A2, specify it only for A2 and make the subquery correlate with T1 on T2.Y = T1.A2, like this:

UPDATE
  dbo.T1
SET
  A1 = (SELECT TOP 1 T2.X FROM dbo.T2 WHERE T2.Y = T1.A2)
WHERE
  A2 IN (ID1, ID2, ...)
;

This way the subquery will pick the topmost row from T2 that matches the key of the T1 row being updated.

Please note also that using TOP n without ORDER BY will result in an arbitrary row being selected. If there can be two or more distinct values of X per Y in T2, it might be a good idea to make the results deterministic by introducing an ORDER BY clause with specific enough set of sorting criteria.

On the other hand, if X stays the same per Y, you are probably storing redundant information in T2. Perhaps, in that case you should move the X column to T1, based on the facts that a) T2.Y appears to be a reference to T1.A2 and b) A2 is the primary key of T1.


Get a user's rank from an equation, and then store it in the table I'm working on a project for my database class, and I have a table called "accounts". It looks like
What I want to do is have a stored procedure where it calculates the new ranks for each account. The ranks will be based off a value (higher the better) that will be computed with an equation I am working on. How would I be able to get the rank, and store the rank in my table?
Ex: Account_id||Games_won||Games_lost||(calculated value) not in table||Account_rank
         1        200           157              1856                    1

EDIT: Here is my CREATE TABLE statement:
CREATE TABLE IF NOT EXISTS league_of_legends.accounts (
account_id INT NOT NULL AUTO_INCREMENT,
account_name VARCHAR(45) NOT NULL,
account_password VARCHAR(45) NOT NULL,
subscriber_first_name VARCHAR(45) NOT NULL,
subscriber_last_name VARCHAR(45) NOT NULL,
subscriber_email_address VARCHAR(45) NOT NULL,
subscriber_dob DATE NOT NULL,
server VARCHAR(45) NOT NULL,
account_balance INT NULL,
games_won INT NULL,
games_lost INT NULL,
account_rank INT NULL,
PRIMARY KEY (account_id, account_name, subscriber_email_address));
My INSERT sample statements:
INSERT INTO accounts (account_id, account_name, account_password, subscriber_first_name, subscriber_last_name, subscriber_email_address, subscriber_dob, server, account_balance, games_won, games_lost, account_rank)
VALUES (DEFAULT, 'Bwomp', 'asdfqwer', 'Nile', 'Williams', 'getrekt@gmail.com', '2003-10-09', 'NA', 1250, 100, 100, NULL),
(DEFAULT, 'Dirty Dan', '1234567890', 'Daniel', 'Brown', 'awovuca-1084@yopmail.com', '1990-07-16', 'EU', 0, 3, 20, NULL),
(DEFAULT, 'Alfy', 'doodcool', 'Alfonso', 'Riggleman', 'AlfonsoRRiggleman@armyspy.com', '1992-12-27', 'KR', 50, 400, 410, NULL),
(DEFAULT, 'Justaguy1', 'retrodonkey', 'Nick', 'Chetta', 'NicholasSMaine@jourrapide.com', '1996-06-28', 'NA', 3, 0, 5, NULL);
Sample equation:
(Games_won / total_games) * games_won = value

OK - this should be easy for the first part, if you're willing to be a bit adventurous :-).

MariaDB has had computed columns (otherwise known as virtual or generated columns) for quite a while now. Otherwise, the latest milestone release of MySQL (5.7.7) also has them.

If you want to do this with a current GA version, you'll have to use TRIGGERs.

However, if you want to update the RANK (see a lovely post here) on each update of games_won/games_lost, then you'll have to call a PROCEDURE which will update RANK. MySQL's Stored Procedure programming language isn't sophisticated enough for this on its own, but see here for some ideas. It says that there can be rollback issues, but since you're updating only one table, it should be easier than most - good luck :-). BTW, I'd use float/double for games_won/_lost - otherwise, you might have problems with integer division.


Optimizing InnoDB default settings I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no constraint of disk space. Which default settings should I change to optimize for better performance, reliability, and possible point-in-time backups [high availability].

Edited

At present, whenever I run "Optimize Tables" via Maintenance on MySQL Administrator, it shows:

Table does not support optimize, doing recreate + analyze instead

on all tables. All of my tables are InnoDB, but why they don't support Optimize?

The way to tune InnoDB is centered around

InnoDB Buffer Pool : It caches data pages and index pages. The amount of data and index you can cache is not a function of disk space constraints but a function of available memory and diskspace currently used by InnoDB.
InnoDB MetaData : By default, the file ibdata1 normally houses anything and everything InnoDB. That would include data pages, index pages, table metadata, MVCC data.

Here is a formula I have used for the past 5 years to compute the InnoDB Buffer Pool based on diskspace used by InnoDB data and index pages:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(Power1024<0,0,
IF(Power1024>3,0,Power1024)))+0.49999),SUBSTR(' KMG',IF(Power1024<0,0,
IF(Power1024>3,0,Power1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 Power1024) B;

Use (SELECT 0 Power1024) for Bytes
Use (SELECT 1 Power1024) for KB
Use (SELECT 2 Power1024) for MB
Use (SELECT 3 Power1024) for GB
Use (SELECT 4 Power1024) for TB (Email Me if you have TerraBytes of RAM)

Of course, I said a function of available memory and diskspace currently used by InnoDB. From here, just use common sense. The recommended number from the above query SHOULD NOT EXCEED 75% OF INSTALLED RAM !!! That's the simplest rule-of-thumb for sizing the InnoDB Buffer Pool.

You should also set innodb_flush_method to O_DIRECT since it will provided stable synchronous writes of InnoDB. I have also written a post on how to optimize Disk Storage for InnoDB.

With regard to the message Table does not support optimize, doing recreate + analyze instead, the reason why you get that error message is the fact that the storage engine is InnoDB. Mechanically, OPTIMIZE TABLE just copies the table to a temp table and performs ANALYZE TABLE.

In reality, ANALYZE TABLE against InnoDB is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that same conclusion as well.

Here are other posts I have made over the year about InnoDB Tuning

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261
https://stackoverflow.com/questions/5248420/at-what-point-does-mysql-innodb-fine-tuning-become-a-requirement/5250306#5250306
https://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space/246111#246111
https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400
https://serverfault.com/questions/253059/mysql-innodb-optimisation/253233#253233
How do you tune MySQL for a heavy InnoDB workload?


SQL Server 2014 Central Management Server Can anyone tell list of all tasks I can do using CMS apart from Executing T-SQL code in multiple SQL Instances at same time.Thanks

Apart from the T-SQL

Executing policy's across your environment, using different groups to evaluate different policy's. http://pietervanhove.azurewebsites.net/?p=1151
Working together with other dba's, this will enable you to list all the servers, and let your other dba's know that there is a new server. You will get an easy overview of everything installed in your environment.
Quickly open object explorer or logs from several servers and perform actions like restarting sql server service

In any environment, I do not see a reason to not use CMS. It just makes your life easier.


Total number of DML and DDL operations performed in a table in oracle I would like to know the total number of operations (DDL and DDL) performed on a table in oracle dabatase since it was created.

Is there a query for it?

If your database has been running in ARCHIVELOG mode and supplemental logging is enabled, you can historically mine the archive logs using Log Miner.

There's a nice demo on this page.

May I ask why you want the total number of operations? The numbers by themselves are meaningless.


CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard? Just came across a weird issue, whereby depending on how I create an index, an index name is required.

http://dev.mysql.com/doc/refman/5.5/en/create-index.html

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

CREATE INDEX `random_name` ON `my_table` (`my_column`); # Requires an index name

ALTER TABLE `my_table` ADD INDEX (`my_column`); # Does not require an index name

It seems to me that the CREATE INDEX call, should not make index name required. I'm wondering if this is a MySQLism, or a SQL standard?

I don't think that the SQL standard defines how to create indexes at all.

A quote from this Wikipedia page:

  Standardization

  There is no standard about creating indexes because the ISO SQL
  Standard does not cover physical aspects. Indexes are one of the
  physical parts of database conception among others like storage
  (tablespace or filegroups). RDBMS vendors all give a CREATE INDEX
  syntax with some specific options which depends on functionalities
  they provide to customers.

The Postgres manual seems to support this here:

  There are no provisions for indexes in the SQL standard.

More evidence under this related question on SO.


Do not want to allow Queries to take more than x minutes? Do not want to allow Queries to take more than x minutes ! What System variable can I set to automatically terminate it.

In mysql 5.7 ; you can use max_execution_time
From the documentation:

Statement Execution Time Optimizer Hints
The MAX_EXECUTION_TIME hint is permitted only for SELECT statements. It places a limit N (a timeout value in milliseconds) on how long a statement is permitted to execute before the server terminates it:
MAX_EXECUTION_TIME(N)

Example with a timeout of 1 second (1000 milliseconds):
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

The MAX_EXECUTION_TIME(N) hint sets a statement execution timeout of N milliseconds. If this option is absent or N is 0, the statement timeout established by the max_execution_time system variable applies.
The MAX_EXECUTION_TIME hint is applicable as follows:

For statements with multiple SELECT keywords, such as unions or statements with subqueries, MAX_EXECUTION_TIME applies to the entire statement and must appear after the first SELECT.

It applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

It does not apply to SELECT statements in stored programs and is ignored.


SSMS Template Browser Add-In development Okay, I’m trying to do something that I just can’t seem to get accomplished and I haven’t found anything online to address this request.
In the “Template Browser” I want to add another category of templates to keep the various templates separate. Looking at the template browser, there are two icons in the “header”. One icon is for the “SQL Server Templates” and the other is for the “Analysis Services Templates”. (See icons in red circle in the image below.)
I want to add a third icon (or more) to the header along with the “SQL Server Templates” icon and “Analysis Services Templates” icon.
Does anybody know if this is possible, and if so, how do you do it?  I'm a full-stack developer / DBA / BI and would like to build / modify this on my own as opposed to just installing a third party solution.
I have SQL Server 2012 with an instance of SSMS 2012 and an instance of SSMS 2018.

That would likely fall into the realm of SSMS Add-In Development.  Having said that, I'm not certain you can add a button to the Template Browser, even then.
Microsoft has a GitHub repo with a sample SSMS Add-In here.


Sql Server replication: Initial snapshot for publication is not yet available? I had setup transactional replication and my distributor, publisher are on same windows server and 2 subscriber one on same server and another on other windows server both using push snapshot.

I am getting the message in the synchronization status, that snapshot is not yet available, just a few hours back it was working fine every transaction was synchronized. The only thing i did in between was, added more articles(stored procedures, etc.) to the publication using its properties.

Is it causing the issue here, there is no error message displayed. Just that I am not able to get the data replicated

Once you add articles to a transaction replication that is synced from snapshot(@sync_type = 'automatic'), you need to run the snapshot agent to generate the snapshot for the added article.  Note, depending on the immediate_sync parameter, it will create snapshot of either the entire publication(1) or just the article that you have added(0).


SET_AUTHENTICATION_FROM_WALLET call returns ACL error Running Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.  Have a wallet setup containing a basic authentication credential.
PL/sql package is executing a request over https to an external server.  The acl seems to be setup correctly, as when I issue a request as below, and do some gets, I get results back.

D_Request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
                                 wallet_path     => D_Path,
                                 wallet_password => NULL,
                                 enable_cookies  => FALSE
                              );

    D_Req := UTL_HTTP.BEGIN_REQUEST(
                  url             => D_Url,
                  method          => C_METHOD,
                  http_version    => C_VERSION,
                  request_context => D_Request_context      )

If I call the same code, but include this call

   UTL_HTTP.SET_AUTHENTICATION_FROM_WALLET(
            r         => D_Req,
            alias     => 'myalias',
            scheme    => C_SCHEME,
            for_proxy => false
         );

I get this error:

ORA-29273: HTTP request failed
ORA-06512: at line 52
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 450
ORA-06512: at "SYS.UTL_HTTP"

So why does the call to BEGIN_REQUEST get through the ACL, but the SET_AUTHENTICATION_FROM_WALLET, gets an error?

Using a wallet requires an ACE (new name of ACL) as well.

SET_AUTHENTICATION_FROM_WALLET Procedure

  Usage Notes

  To use the password credentials in a wallet, the UTL_HTTP user must
  have the use-passwords privilege on the wallet.

Configuring Access Control to an Oracle Wallet

Example:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
  wallet_path => 'file:/home/oracle/wallet',
  ace         => xs$ace_type(privilege_list => xs$name_list('use_passwords'),
                             principal_name => 'YOUR_USER',
                             principal_type => xs_acl.ptype_db));
END;
/


How to SELECT a column based on a condition on the column's name? I have the following table named MyTable:

+------+--------+--------+--------+
| Code | U_Col1 | U_Col2 | U_Col3 |
+------+--------+--------+--------+
|  123 |      1 |      2 |      3 |
|  456 |      4 |      5 |      6 |
+------+--------+--------+--------+

I want to write a query to give me the output based on the condition on the column's name. The names of the column are not fixed. They can be anything and come from another table.

CREATE FUNCTION [dbo].[udf_Value] (@Code VARCHAR(3), @Name VARCHAR(10))
RETURNS TABLE AS RETURN (

    SELECT *
    FROM MyTable T0
    WHERE T0.Code = @Code AND <columnname> = CONCAT('U_',@Name)
)

The expected output of

SELECT * FROM [dbo].[udf_Value]('123','Col3')

is 3.

How do I get this kind of a WHERE condition to work? I realize I would normally do it with dynamic SQL, but I want to do this in an inline table-valued function, which won't allow me doing it dynamically.

I tried an UNPIVOT:

SELECT X.Value
FROM (SELECT * FROM MyTable WHERE Code = @Code) T
UNPIVOT (
Value FOR Name IN (@Name)
) AS X

But there is something wrong with the syntax.

You can do this without dynamic SQL with some XML trickery.

Create XML of all values for each row and and use local-name() function to extract the value of the column you want.

Here is an example where you perhaps need to change the data types. I assumed integers.

create table dbo.T(Code int, U_Col1 int, U_Col2 int, U_Col3 int)

go

create function dbo.GetT
(
  @Name nvarchar(128),
  @Code int
)
returns table
as
return (
       select T.Code,
              C.X.value('(*[local-name() = sql:variable("@Name")]/text())[1]', 'int') as U_Col
       from dbo.T
         cross apply(select T.* for xml path(''), type) as C(X)
       where T.Code = @Code
       );
go

insert into dbo.T (Code, U_Col1, U_Col2, U_Col3) values
(123, 1, 2, 3),
(456, 4, 5, 6)

go

select * from dbo.GetT('U_Col1', 123);
select * from dbo.GetT('U_Col2', 456);

Note that the names of all columns in the table must be valid as element names in XML.

dbfiddle.uk


Best Practices for Separating Content from Presentation in SQL A common and useful paradigm of programming is to separate content and presentation. One system deals with building the result, another system deals with outputting it.
You see this with HTML vs CSS. HTML should ideally be clean and without any internal styling (color, position, etc), which should be handled by CSS.
I'm now wondering how this applies to SQL results. I often find myself formatting results in a query:
SELECT
    CONCAT(first_name, ' ', last_name) AS name,
    DATE_FORMAT(last_edit, '%d/%m/%Y') AS last_edit,
    CONCAT(
        record_type,
        IF (private = 1, ' (Private)', ' (Public)')
    ) AS record_type
FROM people
ORDER BY updated DESC, LastEdit DESC

Three different scenarios are covered here:

The first column is a simple concatenation.
The second transforms the output into a different format.
The third actually adds content to the result.

I understand that there are many useful functions for transforming data in a query. But from a team development point of view, should I be formatting these results in the presentation layer rather than the logic layer (i.e., in my PHP code instead of the SQL query)?
If so, then how far should I take it? Are columns 1 and 2 acceptable but 3 isn't?
Is there a widely-accepted best practice regarding this?

It depends.  There is no one-size-fits-all answer.  Yes, a database is a persistent store for data -- focus on that when designing the schema.
Meanwhile, let me point out that
DATE_FORMAT(last_edit, '%d/%m/%Y') AS last_edit,

implies something that needs to be strongly stated:  Store the data in a generic format:

Store Dates and times in the database in a format suitable to the database.  This may require conversion both during input and output.  It facilitates filtering and sorting (as in your example, the typos notwithstanding).
Store text as UTF-8, not html-entites, latin1, etc.  This implies adding entities (or whatever) after SELECTing the data back out.
Monetary values stored as DECIMAL; scientific as FLOAT/DOUBLE. etc.
JSON can be handy for storing random attributes (think EAV) that don't need to be sorted/searched too much.

But, let's study a common example:  Should an "address" be split into address1, address2, city, province, country, postal_code.  Versus a directly printable address.
One rule for deciding is whether you will ever need to search/sort on individual pieces of the data.  Keep in mind that it is very hard and very inefficient to split apart fields for such purposes.
At the other extreme, would you even think about splitting up an image into pixels?  (I hope not.)
Now, back to your question.  Your example seems to have reasonable datatypes under the covers.  But your question is who does the formatting as it comes out.  You have given simple examples where it is quite simple to use SQL to do the formatting.  I would probably write it the way you did for a "simple" system.
If there is any chance of changing the formatting (eg, flipping and adding a comma: "last_name, first_name") you should probably leave that for later code.
For something more complex, it is better to postpone formatting.
Some things are messy in SQL, so it is better to leave for later code:

Pivoting -- You have 2-dimensional data stored in 1D.  (Follow tag [pivot-table])
Want to build tabular data but not duplicate the value (vertically) until it changes.  This is too cryptic to do in SQL; it is better to do in formatting code.

As for IF (private = 1, ' (Private)', ' (Public)'), consider switching from numeric to ENUM('public', 'private').


Bulk Delete for Large Table in MySQL I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month.

If I do DELETE FROM NOTIFICATION WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);, all the IOPS will be taken, the process will take hours, and a lot of new entries cannot be inserted due to "Lock wait timeout exceeded; try restarting transaction".

I was trying to do the way describe in here:http://mysql.rjweb.org/doc.php/deletebig
However, I am using UUID instead of increment ID.

What is the correct and efficient way to delete those rows while not affect new data being insert/update?

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

NOTIFICATION is emptied fast by means switching in an empty table.
NOTIFICATION is immediately available for new INSERTs
The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
Dropping the old version of NOTIFICATION does not interfere with new INSERTs
NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!


What triggers the SSRS to generate a log file or modify a current one? When running a trace on my new SSRS Reporting Services 2016, after troubleshooting some problems related to migrating from SSRS 2008, I find the following pattern running continuously:

Here is the continuously run script:

--========================================================
--the script that is run over and over
-- by the SSRS service
--========================================================

    declare @BatchID uniqueidentifier

    set @BatchID = NEWID()

    UPDATE [Event] WITH (TABLOCKX)
        SET [BatchID] = @BatchID,
        [ProcessStart] = GETUTCDATE(),
        [ProcessHeartbeat] = GETUTCDATE()
    FROM (

        SELECT TOP 4 [EventID]
          FROM [Event] WITH (TABLOCKX)
         WHERE [ProcessStart] is NULL
      ORDER BY [TimeEntered]

        ) AS t1
    WHERE [Event].[EventID] = t1.[EventID]

    select top 4
        E.[EventID],
        E.[EventType],
        E.[EventData]
    from
        [Event] E WITH (TABLOCKX)
    where
        [BatchID] = @BatchID
    ORDER BY [TimeEntered]

set @BatchID = newid()

UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (

    SELECT TOP 4  [NotificationID]
        FROM [Notifications] WITH (TABLOCKX)
        WHERE ProcessStart is NULL and
    (   ProcessAfter is NULL
        or ProcessAfter < GETUTCDATE())

    ORDER BY [NotificationEntered]

) AS t1
WHERE [Notifications].[NotificationID]
        = t1.[NotificationID]

    select top 4
    -- Notification data
    N.[NotificationID],
    N.[SubscriptionID],
    N.[ActivationID],
    N.[ReportID],
    N.[SnapShotDate],
    N.[DeliveryExtension],
    N.[ExtensionSettings],
    N.[Locale],
    N.[Parameters],
    N.[SubscriptionLastRunTime],
    N.[ProcessStart],
    N.[NotificationEntered],
    N.[Attempt],
    N.[IsDataDriven],
    SUSER_SNAME(Owner.[Sid]),
    Owner.[UserName],
    -- Report Data
    O.[Path],
    N.[ReportZone],
    O.[Type],
    SD.NtSecDescPrimary,
    N.[Version],
    Owner.[AuthType],
    SR.[SubscriptionResult]
from
    [Notifications] N with (TABLOCKX)

    inner join [Catalog] O
            on O.[ItemID] = N.[ReportID]

    inner join [Users] Owner
            on N.SubscriptionOwnerID = Owner.UserID

    left outer join [SecData] SD
                    on O.[PolicyID] = SD.[PolicyID]
                AND SD.AuthType = Owner.AuthType

    left outer join [SubscriptionResults] SR
                    on N.[SubscriptionID] = SR.[SubscriptionID]
                AND CHECKSUM(convert(nvarchar(max),N.[ExtensionSettings]))
                    = SR.[ExtensionSettingsHash]

where
    N.[BatchID] = @BatchID
ORDER BY [NotificationEntered]

In the meantime SSRS generated another log. There is nothing significative in the log, it is all information only, as you can see on the partial view picture below:

All seems normal. So the question is:

What triggers the SSRS to generate a report - other than an exception or error somewhere?

Where in SSRS can I go to have a look\modify this behaviour if possible?

I have checked the config files:
You should backup these alongside the report server databases:

Rsreportserver.config
Rssvrpolicy.config
Rsmgrpolicy.config
Reportingservicesservice.exe.config
Web.config for the Report Server ASP.NET application
Machine.config for ASP.NET

This is the log in question:

The modified date of the file has just changed and I noticed SSRS added information to this same file.

The log in question I believe is

Report Server Service Trace Log and it turned ON by default

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/report-server-http-log?view=sql-server-ver15

I figured this by checking where the log is stored in the question.

  Trace log behavior is managed in the configuration file
  ReportingServicesService.exe.config. The configuration file is found
  in the following folder path:

  \Program Files\Microsoft SQL Server\MSRS13.\Reporting
  Services\ReportServer\bin.

if you want to disable this logging you can do the below.

  DefaultTraceSwitch    Specifies the level of information that is reported
  to the ReportServerService trace log. Each level includes the
  information reported by all lower-numbered levels. Disabling tracing
  is not recommended.   Valid values are:

  0= Disables tracing. The ReportServerService log file is enabled by
  default. To turn it off, set trace level to 0.

You can tweak this value based on your requirements.

0= Disables tracing. The ReportServerService log file is enabled by default.
   To turn it off, set trace level to 0.

1= Exceptions and restarts

2= Exceptions, restarts, warnings

3= Exceptions, restarts, warnings, status messages (default)

4= Verbose mode

More info here


MySQL master binlog corruption We recently made some changes to our infrastructure and now I can't keep MySQL replication running.  The slave complains of a corrupt binlog and resetting it doesn't help.  I keep seeing entries like this in my master log:

BINLOG '
WFxKTRNJAAAAPwAAAKY/YwAAABsAAAAAAAEACHdlYmVkaTMwAA1QYXJ0bmVyQ29uZmlnAAQICA8P
BC0AYwAA
WFxKTRhJAAAAXAAAAAJAYwAAABsAAAAAAAEABP//8BoSAAAAAAAAggMAAAAAAAAJUEFDS0NPVU5U
Azc3MfAaEgAAAAAAAIIDAAAAAAAACVBBQ0tDT1VOVAM3NzI=
'/*!*/;

In the slave log, this just comes across as "Unknown Event" and the replication fails at that position.

Here is my my.cnf for the master:

[mysqld]
datadir=/data/mysql-data
socket=/tmp/mysql.sock
user=mysql
log-error=/var/log/mysqld.log

binlog-do-db    = db1
binlog-do-db    = db2
binlog-do-db    = db3
binlog-do-db    = db4
binlog_format   = 'MIXED'

log-bin = /data/mysql-binlogs/mysql-bin.log
server-id=73
report-host=thisserver.mydomain.com

thread_cache_size = 30
key_buffer_size = 700M
myisam_sort_buffer_size = 300M
table_cache = 256
sort_buffer_size = 4M
read_buffer_size = 1M
innodb_data_home_dir            = /data/mysql-data/
innodb_data_file_path           = InnoDB:100M:autoextend
set-variable                    = innodb_buffer_pool_size=500M
set-variable                    = innodb_additional_mem_pool_size=10M
set-variable                    = max_connections=500
innodb_log_group_home_dir       = /data/mysql-data
set-variable                    = innodb_log_file_size=20M
set-variable                    = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit  = 1

Does anybody know what might be causing the gibberish in my master binlogs?

Surprisingly, that's not gibberish.

That indeed appears at the top of binlogs whenever you do mysqlbinlog to a binary log generated using MySQL 5.1 and MySQL 5.5. You will not see that gibberish in binary logs for MySQL 5.0 and back.

This is why the start point for replication from an empty binary log is

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

This is good to remember if you do MySQL Replication where the Master if MySQL 5.1 and the slave is MySQL 5.0. This could present a really big headache.

Replication from Master using 5.0 and Slave using 5.1 works fine, not the other way around.(According to MySQL Documentation, it is generally not supported for 3 reasons: 1) Binary Log Format, 2) Row-based Replication, 3) SQL Incompatibility).

Anyway, do a mysqlbinlog on the offending binary log on the master. If the resulting dump produces gibberish in the middle of the dump (which I have seen a couple of times in my DBA career) you may have to skip to position 98 (MySQL 5.0) or 106 (MySQL 5.1) or 107 (MySQL 5.5) of the master's next binary log and start replicating from there (SOB :( you may need to use MAATKIT tools mk-table-checksum and mk-table-sync to reload master changes not on the slave [if you want to be a hero]; even worse, mysqldump the master and reload the slave and start replication totally over [if you don't want to be a hero])

If the mysqlbinlog of the master is completely readable after the top gibberish you saw, it is possible the master's binary log is fine but the relay log on the slave is corrupt (due to transmission/CRC errors). If that's the case, just reload the relay logs by issuing the CHANGE MASTER TO command as follows:

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='< master-host ip or DNS >',
MASTER_PORT=3306,
MASTER_USER='< usernmae >',
MASTER_PASSWORD='< password >',
MASTER_LOG_FILE='< MMMM >',
MASTER_LOG_POS=< PPPP >;
START SLAVE;

Where

MMMM is the last file used from the Master that was last processed on the Slave
PPPP is the last position used from the Master that was last processed on the Slave

You can get MMMM and PPPP by doing SHOW SLAVE STATUS\G and using

Relay_Master_Log_File for MMMM
Exec_Master_Log_Pos for PPPP

Try it out and let me know !!!

BTW running CHANGE MASTER TO command erases the slave's current relay logs and starts fresh.


How to fix filestream? I have SQL Server 2008 R2, when I try to access any value stored in filestream I get the following error:

  Msg 233, Level 20, State 0, Line 0
  A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Also the following error thrown in the SQL ERRORLOG file :

  SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

I read about that exception and Microsoft has KB for it (KB972936), but it also didn't work after I installed its hotfix.

If I SELECT the count of the records in that table using :

SELECT COUNT(1) FROM [Table_Name]

I get the correct result.

Here are some details for the database files and filegroups. The database has 2 files the "Row Data" file and the "Log" file where it should also contains the "Filestream Data" item.

The database has the following filegroups :

Rows : PRIMARY with 1 File
Filestream : [MyFileName] with 0 Files !

Here is a snapshots for the DB properties page

And here is the full SQL ERRORLOG file.

The error seems to point at the SQL Server Service account not being able to access the filestream data. Try to change the SQL Server Service account using the SQL Server Configuration Manager (NOT the Windows Services manager!) to one of the build in accounts. Then change it right back to the original account (you will need to know the password here!).

That should fix any access right issues. However it will cause two service restarts.


Upgrading from MySQL 5.1.37 to MySQL 5.1.73 We have a fairly advanced website that runs on a cloud system. We have one master DB server and 2 Slave DBs that read from the Master.

We're currently using 5.1.37 (I know...its old and from 2009), and we're getting some common slave/replication errors.

I've done my research and MYSQL have fixed the errors I'm getting in later releases. So I need to upgrade.

My concern is losing any configuration I have already set within the server. If I upgrade to 5.1.73 will I lose my current setup of everything? Master users/Slaves/data etc.?

Also, what would be the ssh command to perform this upgrade?

Thanks in advance for your help!

I upgraded an old server from 5.1.34sp1 to 5.1.67 some time back... that was about the same amount of "jump" in minor versions as you are contemplating.  In this case, the system worked without incident.
Within the same major release of MySQL (e.g. 5.1.x to 5.1.y), assuming the versions involved are after the GA milestone, upgrading should be relatively straightforward.

For upgrades between versions of a MySQL release series that has reached General Availability status, you can move the MySQL format files and data files between different versions on systems with the same architecture.
— http://dev.mysql.com/doc/refman/5.1/en/upgrading.html

Nevertheless, a complete backup is essential.
Importantly, if you haven't tried restoring your backup on a different server, then for all practical purposes, you don't actually have a backup.
Giving you the "ssh commands" (actually, shell commands) to do the upgrade isn't possible, since there are variations in installation that make it impossible to provide a list of "type these things and it will work" commands.  Assuming your current installation is using an Oracle tarball binary distribution, you'll need to download and extract the binaries into a new directory, stop the server, move or relink the data files where the new copy of MySQL can see them, and rename a couple of directories or change a couple of symlinks.  There's not really a concept of "uninstalling" the old version, since the old version can live harmlessly on your server in a different directory.  You just move it out of the way, and if the upgraded version doesn't start up properly, will often still be able to start the old version back up, if needed.  Eventually, you can remove the old directory, with caution, to avoid removing the wrong things.
You'll need to read the documentation.
http://dev.mysql.com/doc/refman/5.1/en/upgrading.html
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html
After you do the upgrade, don't forget to run the mysql_upgrade utility.  This utility does not upgrade the version of your server; instead, it upgrades your data files to be fully compatible with the new version you've just installed.  You run this after you do the upgrade, and after you restart the MySQL server daemon.
http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html
Depending on how well the server was maintained in the past, if it was upgraded in the past, then it's possible that this was not done when it was upgraded previously.  If so, it would actually be a good idea to run it before doing the upgrade, so that all of your structures are all consistent.  Two important points, here:  The version of the mysql_upgrade utility that you run needs to be the version bundled with the currently running version of the server, and you need to make your backup before you run this.  If you have some serious latent structural corruption that can't be automatically fixed, this can be theoretically be uncovered by mysql_upgrade and the server may shut down to prevent the detected corruption from causing even more serious problems.  This is because mysql_upgrade uses CHECK TABLE as part of its work, and:

If CHECK TABLE finds a problem for an InnoDB table, the server may shut down to prevent error propagation. Details of the error will be written to the error log.
— http://dev.mysql.com/doc/refman/5.1/en/check-table.html

This is not a likely scenario, but it's a documented possibility of which you need to be aware.
As a general rule, the slave server should be upgraded before the master, not after.  This is somewhat less critical when remaining within 5.1 (if you were upgrading to 5.5, you absolutely need to upgrade the slaves first) but upgrading the master last is most definitely the most correct approach.
Upgrading the slave first will also give you a better idea of what to expect, and if the slave's data is consistent with the master, you can always promote it to master if your upgrade of the actual master fails.


Can SQL Server 2008 R2 get email name resolution from Office 365? We just moved from an on premise Exchange server to Office 365's Exchange Online.  My question is, is there any way to still get email name resolution from Office 365?  I have many stored procedures and SQL Agent jobs where the name is specified instead of the email address, and I'd like to know if I can do something to avoid needing to find and change all of them.

So, can "John Doe" be translated to "jdoe@domain.com" using Sql Server 2008 R2 db_mail and Office 365 Exchange Online, like I could do with the old on premise Exchange?

The answer seems to be "no, you can't".  Thanksfully our email addresses were consistant, so I was able to write a function that transforms the [firstname lastname] or [groupname] into the valid email  address.  For anyone outside our company, their raw email address can be supplied.


Database in postgresql with missing rows, how to fill the missing rows with a backup copy? I have a big problem and I'm not that good with postgresql since I use Django to make queries and I only have the basics of sql for querys.

My problem is that there was a problem where a super user deleted a field that was related with my table "clients" (I don't know how she did it,  I didn't design the system, I just manage it), so there are like 120 clients that dissapeared from the table, I made a backup from yesterday (I know how important is to make backups) and I want to "copy" all the data from my backup to database with the missing rows.

I tried to restore only the table from pgadmin (the easy way) but it says that "duplicates key values violates unique constraint "user_client_pkey", detail: Key(id) = 461 already exist. So I'm guessing that I cannot restore because there are values that match the exact id's, is there any easy way (or program) that can copy the missing row only giving the id's and fill with the data that I have in my backup?

Something like compare rows and if there are no row  with that id, insert the data there? I know is not an easy query, but any help would be great.    The most easy way I'm sure that is to rollback, delete the database and restore, but there are so many tables that have more information and clients is the only one that really needs to be restore.

I tried to create a new table and restore the data from backup but it doesn't fill (probably because it doesn't match the name with the new tables), I also changed the name of my table with the missing rows and I create a new table and restore from the backup, but the pk got missing and I receive this error:

I changed the original of the table cliente, then I created a new table and name it like the original one, restore the database, it fills, but gives me this error:

Since the table public."admin_clienteB" doesn't have a primary key or OIDs, you can view the data only. Inserting new rows and changing existing rows isn't possible for the Edit Data tool without primary key.

Any helps would be great.

You can restore all affected tables into a different schema, there are a variety of ways in which you can do that (several good ideas in https://stackoverflow.com/questions/4191653/i-want-to-restore-the-database-with-a-different-schema).

Once the table is in a separate schema, you can compare them by primary key, and insert the missing ones, like so for each affected table:

INSERT INTO production.table
SELECT b.*
FROM backup.table b
LEFT JOIN production.table p ON (p.pkey = b.pkey)
WHERE p.pkey IS NULL;

You will just need to find out the list of affected tables, restore their backup in a separate schema, determine what are their primary keys, create the script with the inserts, and run it.


Stored Procedure - MySQL I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to implement the following logic,

For example:
Business Type Table

Stored Procedure

As per my knowledge I thought, this procedure will work fine. While I call the procedure name as follows,

Call businesstype('insert', 1, 'Online Marketing', datetime(),'raj', datetime(), 'raj', 1);
Every time needs to call this procedure with all its parameters, but developers need to call the procedure with required fields, for example they need to call only the ID (PK) to perform delete function, but it required some values (NULL) for remaining parameters.
How to perform this action? I am just a starter, I am expecting some best practices to implement this, also I need to know whether the given table design and procedure will meet the industry expectations? is it good to implement in this way?

Thanks in advance.

In my opinion, a procedure should do one thing and one thing only. The logic should be performed in the application code. Secondly, a procedure should not return a result set. It should do work and then possibly return a status e.g. success, failure or something similar. Also, the name of your procedure is meaningless. Choose a descriptive name.

So, break it up into three procedures (insert, update and delete). Move the select statement into a view rather than a procedure. Let your programmers add the logic in their code to determine which one to call. Make sure you put index on the table.

Never do a select *. Only select the columns you need. It may be that you add columns later on but you will still SELECT *. Your SELECT * will then store all (incl unnecessary columns) columns in memory and, in worst case, run out of memory and be swapping to disk.


Logging of incorrect row in SQL Server Integration Services I newbie to SQL Server Integration Services and I have a problem:

Data loaded from file looks like this:

1 'a' 'b'
2 'a' 'b'
1 'c' 'd'

And this rows would be loaded in table like this:

CREATE TABLE T (
   ID NOT NULL PRIMARY KEY,
    A nvarchar NOT NULL,
    B nvarchar NOT NULL
);

Cause of ID is primary key third row in input data could not be loaded, and I want to load correct rows (first and second or third and second) and send to log incorrect row.

Check this:
https://stackoverflow.com/questions/11628352/etl-ssis-redirecting-error-rows-to-a-seperate-table

It has all the steps you need to have the error records stored somewhere else.


SQL Union with results in Order of Union I'm looking to write a Union between 2 tables where the 1st Union set of results appears before the second. (MSSQL 2014)

http://www.sqlfiddle.com/#!6/17c9e/74

I've noticed this question asked a few times but for whatever reason the accepted answers aren't working for me:

https://stackoverflow.com/questions/4076052/union-query-display-results-in-order-queries-are-written

https://stackoverflow.com/questions/13885261/order-result-sets-in-a-union

The 1st set of results returns:

userID   FirstName   LastName
-----------------------------
4        John        Snow
5        Joffrey     Baratheon
1        Tyrion      Lanister

And the 2nd results return:

userID   FirstName   LastName
-----------------------------
5        Joffrey     Baratheon
3        Daenerys    Targaryen
1        Tyrion      Lanister

Union gets rid of the duplicates which leaves userID 3 which should be appearing very last in my results. Final result:

userID   FirstName   LastName
-----------------------------
4        John        Snow
5        Joffrey     Baratheon
1        Tyrion      Lanister
3        Daenerys    Targaryen

The result I'm getting on SqlFiddle has userID -3 appearing second.

Any help appreciated.

Based on your sqlfiddle link, here's a brute force way to get what you're looking for. I inject an ordering value for each result set being unioned, group on matching values to get the minimum order, and then order by the minimum order for each grouped value.

SELECT userID, FirstName, LastName, UserName
FROM (
  SELECT userID, FirstName, LastName, UserName, MIN(UnionSet) UnionSetOrder
  FROM (
    SELECT u.userID, u.FirstName, u.LastName, u.Username, 1 as UnionSet
    FROM follows f INNER JOIN Users u ON f.Following = u.UserID
    WHERE follower=2
    UNION ALL
    SELECT u.UserId, u.FirstName, u.LastName, U.Username, 2 as UnionSet
    FROM users u
    WHERE u.username LIKE '%a%' AND u.UserID <> 2
  ) x
GROUP BY userID, FirstName, LastName, UserName
) y
ORDER BY UnionSetOrder, UserName

Note that this doesn't exactly match the results you're showing in your question, as they differ from what's in the code sample.


Automated SQL backup on a timely fashion, & cleaup the database for the backed up data I need to back up SQL database (historian), on a timely fashion, and then clean up the database by removing the backed up data.

I am using MS SQL 2008 (R2), on a Windows XP machine. The biggest issue is the very limited hard disk space. The database is limited to a maximum of 3GB! In terms of overall performance, the PC is really slow, and unfortunately I do not have the choice to change that. So, I could consider backing up overnight when the data flow is expected to be less.

The intention is to back up the data every two weeks, have it stored in a special directory (e.g. c:\ ). Then an operator can move the backup to another machine. Given the limited space, I could consider some 'house clean up', by removing the backed up data. What is more important is the ability to merge the regular backups to an external database. So perhaps a typical SQL backup routine and restore, could be an option.

I would appreciate your kind advice regarding this matter. Thank you.

Instead of rewriting your own solution, my suggestion is to use SQL Server Maintenance Solution - SQL Server Backup

Edit: Below script will help you once you deploy Ola's script and create required objects:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', -- put the databases you want to backup
@Directory = 'C:\Backup',      -- change the backup directory to suit your needs
@BackupType = 'FULL',          -- This will take FULL database backups
@Verify = 'Y',                 -- This will verify the backups
@Compress = 'Y',               -- Since you are using SQLServer2008R2, standard and enterprise both have backup compression available.
@CheckSum = 'Y',
@CleanupTime = 24              -- This will delete the backup older than 24 hrs !!


remote database connection issue is it possible to connect ASP website which is on web server to connect to SQL server 2008 database which is on another database machine? In my case the database machine has windows XP SP1 installed on it, so it is just like desktop machine on the network. Both machines are on the same domain.
If possible how can we setup the connection? What security settings are required on database side? what network settings are required? What code can we use on ASP file?
Is there any test that we can perform with the simple connection string code?

I had used this code

Provider=SQLOLEDB.1;Data Source=myDbServer;Initial Catalog=myDatabase;Uid=myUsername; Pwd=myPassword;
I got this code from the .udl file where test connection works but it does not work when I put it on dataconn.inc file. Why?
I have tried various connection strings but it does not work. I am confuse.

How can I debug the datconn.inc file? it is just the text file. Can I use any other asp file rather than using dataconn.inc? I am conecting to database to check my login credentials and then log into this website and I am getting http 500 error.

The simplest way to do this is give IUSR_[computer name] (where that is your web server machine) read/write access to the relevant database (use the datereader and datawriter builtin roles, DON'T use dbo).

If you are using stored procedures, you will need to grant execute permissions to  IUSR_[computer name] as well.

In addition, eet you connection string to use a Trusted connection:

"Data Source=myServer;Initial Catalog=myDataBase;Integrated Security=SSPI;"


Find all users who rated all the movies rated by a specific user So we have the following relational schema:

users(user_id, ..)
movies(movie_id, ..)
ratings(user_id, movie_id, rate)

So, we want to find all the users who have rated all the movies which are rated by a specific user '1234'

In other words, if user '1234' has rated movies 1, 2, 3, and 4, we want the user ID of all others who have also rated movies 1, 2, 3, 4.

This will not suffer from duplicate votes

SELECT user_id
FROM ratings
WHERE movie_id IN (SELECT movie_id FROM ratings WHERE user_id = '1234') AND user_id <> '1234'
GROUP BY user_id
HAVING count(DISTINCT movie_id) = (SELECT count(DISTINCT movie_id) FROM ratings WHERE user_id = '1234')


How to know who dropped a database I have in the SQL server a lot of databases, but one of them has been dropped, so I need to create a trigger to send me an email when someone try to drop another database. The e-mail should contain the user name and the the name of the database.

@Kin is right about getting this info from the default trace, if it is in fact still there (depends on how busy your server is, and how long ago this happened).

To get an e-mail immediately, though, instead of having to remember to check the default trace all the time just in case something happened, you can do this from a DDL trigger. Database Mail config is out of scope for the question, I think, so I'll leave that part to you (there is a Q & A here).

ALTER TRIGGER DroppinDatabasesAwYeah
ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
  DECLARE
    @name    NVARCHAR(255),
    @login   NVARCHAR(255),
    @msg     NVARCHAR(MAX);

  SELECT
    @name  = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(255)'),
    @login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(255)');

  SELECT @msg = N'It appears that '
    + QUOTENAME(COALESCE(@login, N'<unknown>'))
    + N' has dropped the database '
    + QUOTENAME(COALESCE(@name, N'<unknown>')) + N'.';

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'default',
    @recipients = N'you@yourdomain.com',
    @subject = N'Somebody done dropped a database',
    @body = @msg;
END
GO


Error trying a manual's example in DB2 11.1.4.4 When trying this example from the DB2 11.1 manual:

VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER));

I get this error:

  SQL0171N  The statement was not processed because the data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.JSON_VALUE" is incorrect. Parameter name: ""

I get the same error for various json functions. Using the Command Line Processor Plus from Windows 10.

The problem is that the database must be encoded in UTF-8.

https://developer.ibm.com/articles/sql-json-publishing-functions/

  JSON data consists of a sequence of Unicode characters that are arranged in a hierarchical form based on the JSON value syntax and grammar


How can I eliminate false "connection string format is not valid" messages from SSIS log? I have a project that uses the SSIS catalog with project-level connection managers. The connection managers' connection strings are overridden at the server using a project parameters file, to enable easily repointing the whole project to different data sources by setting different connection strings.

Here's the issue:

It works. No real connection problems or failures.
It bloats the log
    tables horribly in the catalog because upon each package execution I
    get:

  120    30  Error: The connection string format is not valid. It must
  consist of one or more components of the form X=Y, separated by semicolons.
  This error occurs when a connection string with zero components is set on
  database connection manager.

For every connection manager, in every package. I think this has to be a false error that gets triggered when the connection string is overridden with the (correct) values in the param file.

Is there something wrong here? Some way to suppress those messages?

If you use the Excel connection manager for example, you should set the ExcelFilePath property as an Expression instead of the ConnectionString !


PostgreSQL - summing arrays by index I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array columns by index and produces a new 48 array index as explained below

A = double[48] = {3,2,0,3....1}
B = double[48] = {1,0,3,2....5}

RESULT = double[48] = {A[0] + B[0], A[1] + B[1],...,A[47] + B[47]}

Thank you!

I would use unnest together with array_agg, like this:
SQL Fiddle

PostgreSQL 9.3.1 Schema Setup:

create table t (
A double precision[5],
B double precision[5]);

insert into t values
('{3,2,0,3,1}', '{1,0,3,2,5}');

Query 1:

with c as(
select unnest(a) a, unnest(b) b from t)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c

Results:

|         A |         B |         C |
|-----------|-----------|-----------|
| 3,2,0,3,1 | 1,0,3,2,5 | 4,2,3,5,6 |

As commented below, the query above will work well for the arrays of the same size. Otherwise it may produce an unexpected result.

If you need to support the arrays of different size, use this query:

with a as(
  select unnest(a) a from t),
b as(
  select unnest(b) b from t),
ar as(
  select a, row_number() over() r from a),
br as(
  select b, row_number() over() r from b),
c as(
  select ar.a, br.b from ar inner join br on ar.r = br.r)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c;


I don't think a cursor is good solution to this as it would have to be running 24 / 7 and you would need to check it was running. A better solution would be to have a SQL Agent job that runs at midnight each night. The Agent job would call a stored procedure that checks the IsActive flag, LastUpdate field and NextUpdate field for each employee and then increments the mentioned values for each employee.

The stored procedure could send an email after each run to show you how many records were updated. It could also incorporate error handling to alert you if there was a failure for some reason. This way you can be sure the process is running correctly.


Reset MySQL root password in LAMPP server on Ubuntu I forgot the password I changed for the root user of MySQL. I'm logged in as root user and when I do
# /opt/lampp/lampp security
it gives result as

XAMPP: Quick security check...
XAMPP: Your XAMPP pages are secured by a password.
XAMPP: Do you want to change the password anyway? [no] n
XAMPP: MySQL has a root passwort set. Fine! :)
XAMPP: The FTP password for user 'nobody' is still set to 'lampp'.
XAMPP: Do you want to change the password? [yes] n
XAMPP: Done.

How do I reset the password. I checked to see, but couldn't find the password in any file (including my.cnf).

The skip-grant-tables solution is not a recommended one, for a couple of reasons:

It makes the database vulnerable (even with skip-networking)
It requires taking your database down twice.

A solution which requires taking the database down just once is as follows:

Create a temporary SQL text file, say /tmp/init.sql
Within this file, write:

SET PASSWORD FOR root@localhost = PASSWORD('the_new_password');
Add the following to your MySQL config file (on Ubuntu this is on /etc/mysql/my.cnf), under the [mysqld] section:

init-file=/tmp/init.sql
Restart MySQL once. The init file is read and executed upon startup. The password is reset.
Proceed to remove the init-file=/tmp/init.sql entry from my.cnf (do not forget this). Even as the server is up and running.
Remove the /tmp/init.sql file.

There are even more solutions! Please refer to a past blog post of mine. Make sure to check out comment #4 by strcmp


Is there a way to tell your hostname within a stored procedure? Is there a way to determine the hostname of the server you're running in w/ in a stored procedure?

You can run system hostname from a client but you can't make system calls from SP.  I don't see it any of the global variables or statuses.

I can see my hostname on my PC

mysql> show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)

mysql>

This works in MySQL 5.x

SELECT variable_value INTO @local_hostname
FROM information_schema.global_variables
WHERE variable_name = 'hostname';

The query works directly in the mysql client:

mysql> SELECT variable_value INTO @local_hostname
    -> FROM information_schema.global_variables
    -> WHERE variable_name = 'hostname';
Query OK, 1 row affected (0.00 sec)

mysql> select @local_hostname;
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

mysql>

Here is quick SP I wrote in MySQL 5.5.12 on my PC

DELIMITER $$

DROP PROCEDURE IF EXISTS `junk`.`ShowHostName` $$
CREATE PROCEDURE `junk`.`ShowHostName` ()
BEGIN

    SELECT variable_value INTO @local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';
    SELECT @local_hostname;

END $$

DELIMITER ;

Then run CALL junk.ShowHostName();

mysql> CALL junk.ShowHostName();
+-----------------+
| @local_hostname |
+-----------------+
| LW-REDWARDS2    |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Here it is as a function

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetHostName` $$
CREATE FUNCTION `junk`.`GetHostName` () RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN

    DECLARE local_hostname VARCHAR(64);

    SELECT variable_value INTO local_hostname
    FROM information_schema.global_variables
    WHERE variable_name = 'hostname';

    RETURN local_hostname;

END $$

DELIMITER ;

Now, just SELECT it:

mysql> SELECT junk.GetHostName();
+--------------------+
| junk.GetHostName() |
+--------------------+
| LW-REDWARDS2       |
+--------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!


SSIS import data or insert data if no match I have a situation in which I need to import data from one table to another. The challenge here is in tables I have some columns which are references to other table values (kind of foreign key). I need to match these referencing columns and if fields match I need to map the respective id otherwise I need to insert that value in the referencing table and than map id in the source table data source and import all the data.

Elaborating my problem:

So this is how my ETL look like. Currently I am "On Lookup Match" moving forward with execution but I also need to create new records in the respective table if "Look up don't match". (And this I need to do for all the 8 lookups I am doing). I do not understand how to do all this in sequence and import all the data at final stage.

Here is what i did:

Created a new data flow to look up and insert non matching fields.
Inserted the actual data flow.


Updating SQL Server 2016SP2 to latest CU in Cluster...what happens? Updating to latest CU in a cluster with a bunch of databases.
Can I do this on the passive node without SQL Server kicking on?  Do I need to worry about that?
I think I can run it on the passive, reboot the passive, then failover and ... at this point I'm unsure what happens with the databases in the instance.
Does it go through and update each individual database?
Run update exe on passive node
Reboot passive node
Failover...?

Install the CU on a passive node, failover, install on another passive node, repeat until all nodes are patched.
When you failover to the passive node, the databases will be upgraded. If you failback, the databases will be downgraded back to the old CU.  (This is true for CU's, not for SP's or Major Version upgrades).


Best practice for exposing record IDs to API users I am extending an API to include the ability to retrieve and update a specific record after the record is created by the user.

Currently, these records are created with an auto generated integer key, for example 49300.

Users need a way to access a specific record but I don't like the idea of exposing this ID to end users, because it will reveal information about my schema, for example I can assume there is also a record of ID 493299.

I'm considering a GUID alongside the auto generated integer, but I have concerns about performance as the table size is ~5 million records, growing by 1 million/year.

Is there reason for concern adding a table this size, or the subsequent SELECT query on the GUID when retrieving/updating the record?

I'm using AWS RDS MySQL.

Thanks!

Rows are created with a Surrogate, numeric key that keeps the database nice and tidy but, for each record, is there a Natural key that would be meaningful to the Users?

For example, a User might log into your Application using an email address.

Each User record would have both a Surrogate, numeric, UserId that the User never sees plus their email address (and other stuff).  When joining tables within the database, you use the internal, Surrogate key.  For anything coming from the Outside World, you use the Natural keys that make sense to Them Out There:

create table Users
( id            int            auto_increment not null
, email_address varchar( 100 ) not null
, primary key ( id )
, key ( email_address )
);

select *
from Users ;
+----+----------------------------+
| id | email_address              |
+----+----------------------------+
|  1 | fred.flintstone@rubble.com |
+----+----------------------------+

It might be that one field is not enough to do this.  The Natural, Primary Key of a record might be a handful of fields - if that what it takes for Users to get to that record, then that's what they have to enter.

As you quite rightly suspect, you should never leak those internal identifiers outside of the database.  Always take Real World value[s] and map them back into their internal equivalents.  If nothing else, it gives you an degree of input data cleansing en route.


Consolidating a row of data, based on previous rows I'm trying to build a history table out of an audit log (ultimately to build out a type 2 dimension table).  Unfortunately, the audit log only records the specific fields being changes.  Here's a rough example of what I'm talking about;

CREATE TABLE Staff(
  [ID] int,
  [Surname] varchar(5),
  [FirstName] varchar(4),
  [Office] varchar(9),
  [Date] varchar(10)
);

INSERT INTO Staff ([ID], [Surname], [FirstName], [Office], [Date])
VALUES
  (001, 'Smith', 'Bill', 'Melbourne', '2015-01-01'),
  (001, NULL, NULL, 'Sydney', '2015-03-01'),
  (002, 'Brown', 'Mary', 'Melbourne', '2014-04-01'),
  (002, 'Jones', NULL, 'Adelaide', '2014-05-01'),
  (002, NULL, NULL, 'Sydney', '2015-01-01'),
  (002, NULL, NULL, 'Perth', '2015-03-01');

The first entry for a particular staff member is for when their record is created, and each subsequent record is an update... but only shows the update to the field that was updated*.  I want to "fill out" the update row with the rest of the employee record as it currently stands.  ie, a result like this;

001, Smith, Bill, Melbourne, 2015-01-01
001, Smith, Bill, Sydney, 2015-03-01
002, Brown, Mary, Melbourne, 2014-04-01
002, Jones, Mary, Adelaide, 2014-05-01
002, Jones, Mary, Sydney, 2015-01-01
002, Jones, Mary, Perth, 2015-03-01

I know I can do this using a while loop or a cursor but I suspect there is probably a more performant option.

*A NULL always means "value didn't change" rather than "value changed to NULL".

Sample data with the Date column typed as date:

CREATE TABLE dbo.Staff
(
  [ID] integer NOT NULL,
  [Surname] varchar(5) NULL,
  [FirstName] varchar(4) NULL,
  [Office] varchar(9) NULL,
  [Date] date NOT NULL,

  PRIMARY KEY (ID, [Date])
);

INSERT INTO Staff ([ID], [Surname], [FirstName], [Office], [Date])
VALUES
  (001, 'Smith', 'Bill', 'Melbourne', '2015-01-01'),
  (001, NULL, NULL, 'Sydney', '2015-03-01'),
  (002, 'Brown', 'Mary', 'Melbourne', '2014-04-01'),
  (002, 'Jones', NULL, 'Adelaide', '2014-05-01'),
  (002, NULL, NULL, 'Sydney', '2015-01-01'),
  (002, NULL, NULL, 'Perth', '2015-03-01');

The idea of the following solution is to lag back as many rows from the current row as there are preceding nulls:

SELECT
    G.ID,
    Surname = LAG(G.Surname, G.SurnameLag) OVER (
        PARTITION BY G.ID
        ORDER BY G.[Date]),
    FirstName = LAG(G.FirstName, G.FirstNameLag) OVER (
        PARTITION BY G.ID
        ORDER BY G.[Date]),
    Office = LAG(G.Office, G.OfficeLag) OVER (
        PARTITION BY G.ID
        ORDER BY G.[Date]),
    G.[Date]
FROM
(
    -- Find the LAG offset per column
    SELECT
        S.ID,
        S.Surname,
        SurnameLag = SUM(IIF(S.Surname IS NULL, 1, 0)) OVER (
            PARTITION BY S.ID
            ORDER BY S.[Date]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        S.FirstName,
        FirstNameLag = SUM(IIF(S.FirstName IS NULL, 1, 0)) OVER (
            PARTITION BY S.ID
            ORDER BY S.[Date]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        S.Office,
        OfficeLag = SUM(IIF(S.Office IS NULL, 1, 0)) OVER (
            PARTITION BY S.ID
            ORDER BY S.[Date]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        S.[Date]
    FROM dbo.Staff AS S
) AS G
ORDER BY
    G.ID, G.[Date];

For versions of SQL Server prior to 2012, the IIF expressions can be written as CASE WHEN <column> IS NULL THEN 1 ELSE 0 END.

Output:

This will all become much easier when Microsoft implements LAG with the IGNORE NULLS option.

For more options, see The Last non NULL Puzzle by Itzik Ben-Gan.


Archive a single giant table from a SQL 2008 database - filegroup backup? I have an extremely large table (400GB data size, inside a 2.5TB database) that is no longer needed. Before we drop it, however, our client contract says we need to archive it to tape in case in needs to be recovered at some point in the future.

The most obvious idea is to simply archive the full database backup, but recovering this one table from that very large backup would be a challenge, due to the total database size.

I suppose I could also export the table to an otherwise-empty database, and back that up. This may be my fallback option.

I wanted to ask about filegroup backups, however, since this table is part of its own filegroup. Is this a valid option for me?

If I understand this article on filegroup restores correctly, the answer is no, but I'm not sure that my case applies here, since my database is in SIMPLE recovery mode (not FULL), and because I'm dropping the filegroup entirely after the backup.

Is filegroup backup an option? Or should I go with one of the other two?

I'd favour a BCP extract.

BCP export to file.
Import somewhere (different database or server) to confirm the file "is good".
Archive multiple copies, to multiple tapes.

@Marian answered a question of mine with a great list of BCP references that you might find useful. The BCP Basics by SQLFool is also a good starter for ten.


Oracle 10g Storage Migration We need to migrate the storage of our production database. What is the appropriate method of doing so and what specific/generic precautions do we need to take??

Database Configuration:
Volume Manager - ASM using Raw Disks. ASM External Redundancy as storage is published from storage subsystemDatabase version - Oracle 10g R2Database Size - 5TB approx.

Existing Storage (source): HP MSA 2312sa Dual Controller. Directly connected to our database server (no fc or ethernet switch).
The Oracle binaries are also on this storage

New Storage (Target): HP EVA6300 FC storage. This storage will be connected to the hosts via FC switches.

Can we use host based storage migration like VxVM Plex attach/dettach to copy data from source LUNs to the target LUNs?
Do we have to use Oracle RMAN backup and recovery method for storage migration?

There is no need to copy the files - there is an ASM "trick" that does exactly what you want to do with no interruption in service. When ASM rebalances a disk, it maintains a list of viable LUNS for each block independently of the LUN on which it presently lives. It also won't drop a disk on which there are active blocks without rebalancing elsewhere - but it will mark it as not a viable home for rebalancing!

Once you have used oracleasm to make the new LUNs visible to ASM, you just issue the single command (forgive syntax, off the top of my head)

alter diskgroup my_dg add disk 'new1', 'new2', 'new3' drop disk old1, old2, old3;

Then sit back and wait. ASM will hot-relocate each block from the old storage to the new, laying them out nicely on the way, and drop the old disks when the operation completes. I/O will be a bit higher, but your users probably won't notice a thing.

As for your ORACLE_HOME, I'm afraid switching that will require a (brief) outage.


Why does mysql make delete offset? I am implementing database(College project).So I come across innodb diagrams in github https://github.com/jeremycole/innodb_diagrams/blob/master/images/InnoDB_Structures/B%2BTree%20Record%20Delete%202.png
    I see that we maintain a delete chain why we wanna do this because if we write the whole block(because it if more efficient write whole block ) back why don't just simply delete that record .And what is the use of maintaining delete chain.As we can see they use linked list so deleting the whole record requires no extra cost. Please explain or tell me where i am wrong?

The list is kept for MVCC (https://en.wikipedia.org/wiki/Multiversion_concurrency_control) as @jkavalik mentions in his comment. This is required for providing isolation for transactions  (https://en.wikipedia.org/wiki/ACID). A record cannot be deleted under you as long as you're in a transaction.

When you start a transaction (depending on your isolation level) you get a "snapshot" of the current state of the database. This is implemented in MVCC by TRX_ID which is being checked against your actual transaction id. If it's higher you cannot see that because it means it was updated after you started the transaction.

Deleted (and also old versions of updated) records are picked up by a purge thread in the background as of MySQL 5.6. It used to be in the master thread and now the number of threads is configurable also (https://dev.mysql.com/doc/refman/5.5/en/innodb-improved-purge-scheduling.html)


I need a help to get data from a table which has id,serialnumber,process(prcs) and result The query able to get the recent failed process(prcs) of that serialnumber,if the recent process(prcs) is Pass, it will not show. But my problem is if the Last process is PASS,do not show the serial number.Now im able to get the recent Failed process but i want to ignore it if the last process is pass.Thank you

The Query i tried-:

SELECT tt.id,tt.serialnumber,tt.prcs,tt.result
FROM tblTest tt
INNER JOIN
    (SELECT serialnumber,prcs MAX(id) AS MaxID
    FROM tblTest
    GROUP BY  serialnumber,prcs) groupedtt
ON tt.prcs = groupedtt.prcs AND tt.id = groupedtt.MaxID
WHERE result='FAIL' AND tt.sn='A01'
ORDER by serialnumber

Try:

WITH cte AS ( SELECT id,
                     serialnumber,
                     prcs,
                     result,
                     ROW_NUMBER() OVER (PARTITION BY serialnumber
                                        ORDER BY id DESC) rn
              FROM tblTest )
SELECT id, serialnumber, prcs, result
FROM cte
WHERE rn = 1
  AND result = 'FAIL'

  if lastprocess and 5th process fail, should show both

Try:

SELECT t1.*
FROM tblTest t1
WHERE t1.result = 'FAIL'
  AND NOT EXISTS ( SELECT 1
                   FROM tblTest t2
                   WHERE t1.serialnumber = t2.serialnumber
                     AND t1.id < t2.id
                     AND t2.result != 'FAIL' )


Get result from select exists, execute insert query based on the response of it I have to insert unique value for a row but has to put check on 3 columns collectively ((unique value(col1 && col2 && col3) == true)

I found a solution here it is

query = select exists(select 1 from titles where "col1"='${col1}' AND "col2"='${col2}' AND "col3"='${col3}');

This query would return true or false based on the entries of the table

if(query == true)

execute insert into table values ('col1','col2','col3','col4','col5');

I am inserting multiple records from a json file using promises.

Is there any way to execute this in single sequelize.query().

Create a UNIQUE index or constraint on (col1, col2, col3) - if you don't have one already.

Then use INSERT ... ON CONFLICT ... DO NOTHING. That's simpler, faster and more reliable, and unlike the route you had in mind, it's also safe against race conditions.

Related:

What does a CONSTRAINT have to do with my unique index?
What happens with duplicates when inserting multiple rows?

Be aware of NULL handling:

PostgreSQL multi-column unique constraint and NULL values