Insert mysql query results into database column with corrensponding ids I'm trying to store some data from a temporary query into a table, but it doesn't work.
Suppose we have the following data
Table_A

Submission_id
Salesman
Customer name
Hobby
Carline
Fuel
Marketing_data

1
salesman1
customer1
hobby1
car1,car2
Fuel1
hobby1,car1,car2,Fuel1

2
salesman2
customer2
hobby1,hobby2
car1
Fuel2
hobby1,hobby2,car1,Fuel2

And the radio Button/checkbox value table:
Table_B

option_value
option_name

hobby1
Football

hobby2
Golf

car1
SUV

car2
Cabrio

Fuel1
Gas

Fuel2
Hybrid

I've written the following code trying to match the options stored in the previous table against the column "Marketing_data" to obtain a new column:
SELECT GROUP_CONCAT(DISTINCT c0.option_name)Marketing_data
FROM Table_A d
JOIN Table_B c0 ON FIND_IN_SET(c0.option_value, d.Marketing_data)
GROUP BY d.Marketing_data;

And the resulting column is composed as follow:

Marketing_data

Football, SUV, Cabrio, Gas

Football, Golf, SUV, Hybrid

Now, what I'm trying to do is to insert the last column into the first table (Table_A). I tried with INSERT INTO command and I've also tried to generate another table containing the Submission_id field, but nothing worked. Have you got any suggestions? Many Thanks to everyone who will try to help me.

It ais not a good ides to have a comma separated column in your database see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad
But you can make following

CREATE TABLE Table_A
    (`Submission_id` int, `Salesman` varchar(9), `Customer name` varchar(9), `Hobby` varchar(13), `Carline` varchar(9), `Fuel` varchar(5), `Marketing_data` varchar(24))
;

INSERT INTO Table_A
    (`Submission_id`, `Salesman`, `Customer name`, `Hobby`, `Carline`, `Fuel`, `Marketing_data`)
VALUES
    (1, 'salesman1', 'customer1', 'hobby1', 'car1,car2', 'Fuel1', 'hobby1,car1,car2,Fuel1'),
    (2, 'salesman2', 'customer2', 'hobby1,hobby2', 'car1', 'Fuel2', 'hobby1,hobby2,car1,Fuel2')
;

CREATE TABLE Table_B
    (`option_value` varchar(6), `option_name` varchar(8))
;

INSERT INTO Table_B
    (`option_value`, `option_name`)
VALUES
    ('hobby1', 'Football'),
    ('hobby2', 'Golf'),
    ('car1', 'SUV'),
    ('car2', 'Cabrio'),
    ('Fuel1', 'Gas'),
    ('Fuel2', 'Hybrid')
;

UPDATE
Table_A a
SET `Marketing_data` = (SELECT GROUP_CONCAT(DISTINCT c0.option_name)Marketing_data
FROM (SELECT * FROM Table_A) d
JOIN Table_B c0 ON FIND_IN_SET(c0.option_value, d.Marketing_data)
 WHERE a.`Submission_id` = d.`Submission_id`
 GROUP BY d.Marketing_data)

SELECT * FROM Table_A;

Submission_id | Salesman  | Customer name | Hobby         | Carline   | Fuel  | Marketing_data
------------: | :-------- | :------------ | :------------ | :-------- | :---- | :-----------------------
            1 | salesman1 | customer1     | hobby1        | car1,car2 | Fuel1 | Cabrio,Football,Gas,SUV
            2 | salesman2 | customer2     | hobby1,hobby2 | car1      | Fuel2 | Football,Golf,Hybrid,SUV

db<>fiddle here


How to set TIMESTAMP default value to 1970-01-01 00:00:01 UTC? How can I set the default value for a TIMESTAMP column to be exactly 1970-01-01 00:00:01 UTC without changing time_zone?

I tried the following:

CREATE TABLE `foo` (`ts` TIMESTAMP DEFAULT CONVERT_TZ(
    '1970-01-01 00:00:01', '+00.00', @@session.time_zone));

but got a syntax error:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near CONVERT_TZ('1970-01-01 00:00:01', '+00.00', @@session.time_zone)) at line 1

Is there any way to do this without creating a TRIGGER?  If not, how would I do it with a TRIGGER?

EDIT after clarification:

I'm afraid you can't deal with it the way you intend.
You have to find another way, like for example defaulting to NULL and selecting via

SELECT COALESCE(ts, CONVERT_TZ( '1970-01-01 00:00:01', '+00.00', @@session.time_zone)) AS ts, ...

Or just use datetime and store the time_zone in a separate column. It depends on your requirements. But there's no way you can make it work like you want to, not even with triggers.

Original answer:

You can't use any functions in a table definition.

What you're trying to do is unnecessary anyway. May I quote the manual:

  MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

If you are querying a database across different timezones, and need the date/time to reflect individual users settings, use timestamp. If you need consistency regardless of timezone, use datetime.


postgres: what is the archive_command and when to use it I have a master/slave postgresql configuration where the slave is a hot standby and is used as a readonly backup. Currently, the archive_command is set to "cd ." (a noop as I've read).  My understanding is that the archive_command can copy wal segments to a specific location; usually I see examples using the cp command to copy it somewhere else on disk.  I have wal_keep_segments set to 256, so if I understand correctly, the slave can fall behind by about 4GB (16MB * 256) worth of changes to the wal. Is this possibly why I've never needed to use the archive_command because I have enough wal segments to account for some lag?

So is the purpose of the optional archive_command to save more wal segments than the wal_keep_segments settings? So in my case, 256 wal segments would be in the database directory (pg_xlog), and postgres would automatically rotate out and clean up those wal segments as the limit was reached.

And, if I put an archive_command in place, would it just keep saving the wal segments to wherever the command copied the files to (and keep adding more and more archived wal segments unless I added my own cleanup process)? Is this thinking correct?

Then, assuming my understanding of the archive_command is correct, how does the slave take advantage of those wal files?  Does the slave first try to retrieve the 256 wal files from the master data directory, but then fall back to the recovery_command to retrieve wal files older than that?  Does the recovery_command run on the slave or somehow get proxied through the master; many of the examples of the recovery_command show it being used with cp, so I'm not sure how the files are being transferred over to a different server?

I've tried reading numerous guides on this, including the following.
When do I have to use archive_command and when not
https://www.postgresql.org/docs/current/static/continuous-archiving.html

I'm still a bit hazy on the concepts and am trying to reach some clarity. Can someone bring some clarity as to whether my thinking above is correct and why I might use the archive_command? Thanks for reading.

Here is my config:

Master: postgresql.conf

listen_addresses = '*'

wal_level = hot_standby
archive_mode = on
max_wal_senders = 2
archive_command = 'cd .'
wal_keep_segments = 256
hot_standby = on

Slave: postgresql.conf

listen_addresses = '*'

wal_level = hot_standby
archive_mode = on
max_wal_senders = 2
archive_command = 'cd .'
wal_keep_segments = 256
hot_standby = on

Slave: recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=IP-of-master port=5432 user=rep password=****'
trigger_file = '/tmp/postgresql.trigger.5432'

The archive_command is useful when:

You want to do point-in-time recovery from a base backup to some past state in case of disaster, like accidentally dropping a database.
You're using streaming replication without a replication slot. In this case if the replica gets behind by more than wal_keep_segments and hot_standby_feedback is off or the replica gets disconnected temporarily, the master will remove resources it needs and it'll break. If you have WAL archiving in use, the replica can fall back to using its restore_command to fetch WAL to catch up and recover. Otherwise you'd have to re-create it from a new pg_basebackup.


Transactional replication latency issue I inherited database systems like this. Currently I have publisher database in SQL Server 2005 compatibility mode on Windows Server 2008 R2 with SQL Server 2008R2 SP2 machine. Distributor is on the same machine. Subscriber is 2008R2 SP2 and database is in SQL Server 2008 compatibility mode. We are using Transactional Replication. Isolation level is Read Committed. Distributor resides on Publisher. Even though when I right click on publication and even though subscription shows as pull subscription, I think it won't matter since distributor resides on publisher itself. Please correct me if I am wrong. Storage system is IBM flex which is shared by five servers including publisher and subscriber.

Since couple of days, I see latency of few hours, it catches up in the morning and starts going up again in the afternoon. I followed https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/ to see exactly what was happening. I ran following query.

USE distribution
go
EXEC Sp_browsereplcmds
@xact_seqno_start = '<seq#>' -- seq# is same for start and end
,@xact_seqno_end = '<seq#>'
,@publisher_database_id = <publisher database id --this is different than database_id

I see that there are supposedly massive updates being done on few tables involved in replication and Log Reader is just scanning transaction log, not able to replicate anything till transaction completes. Interestingly, I can't see any blocking on either publisher and/or subscriber. Will changing isolation level to Read Committed Snapshot Isolation (RCSI) help here? Will it help to change polling interval to 1 and readbatchsize to 1000 or 5000. What's the command to change that setting?

I changed Log Reader Agent default profile as follows. Polling Interval from 5 to 1 and ReadBatchSize to 5000. This brought latency from 13 hours to zero almost instantly. But I see that it went back to 13 hours.

Replication is in sync and I don't have a single clue for actual root cause which caused the latency and now it went away.

I had to finally call Microsoft support and just a simple command called
    DBCC LOG INFO
on publisher revealed a possible root cause. I saw more than 8600 VLFs! and this was the cause of that latency. Also, our log file is preallocated to 538GB.

Replication was out of sync by almost 19 hours by the time I got follow up call from Microsoft help 4:00pm next day after I opened case with Microsoft. Steps to be taken were really simple. Backup publisher db log couple of time and try to shrink log file. Set increment factor for log file by 8GB or 12GB instead of percentage or 500MB. So, that next time log file grows, it will create 16 VLFs per 8GB or 12GB depending on your increment factor.

After I backed up log, I was able to shrink log file to 350GB and total VLFs to around 5300. Still higher. Latency didn't come down though. It went upto 22 hours. I started wondering whether number of VLFs was just one of the causes. However, around 11:30pm, latency reduced to around 7h30m and I freed up more space around that time, reducing VLFs to 2001. By 2am, replication was in sync. I hurried up and backed up log twice and shrink the log file to 10GB and grew it back to around 248GB. Total number VLFs as of now are 184 and replication is in sync since then. Whew! Log file is almost empty.

Let me know if you have any questions about this. I am glad to help. Hopefully someone else doesn't have to call Microsoft for this issue.


Inner join and group by So I've got two tables.

Accounts has three columns:

id, Account_ID, Account_Name

Tickets also has three columns:

id, Account_ID, Description

I can use the following query to get the most commonly occurring account IDs from within the ticket table - The accounts which are the most active in submitting tickets

select account_id, count(*) as 'occurences' from tickets group by account_id order by occurences desc

which is great, but i'd like to group that on the accounts table so the output reads

Accounts.Account_Name, Count of occurences from previous query

I tried this codeplex example to no avail, I get the error

The multi-part identifier "ticket.Account_ID" could not be bound.

using this query

Select accounts.Account_Name, Accounts.Account_ID, TotalTickets
from Accounts
inner join
(Select tickets.Account_ID,count(*) as TotalTickets
from  Tickets group by ticket.Account_ID) acctTickets
on acctTickets.Account_ID= accounts.Account_ID

Thanks!

Try:

select ac.Account_Name, ac.Account_ID, count(t.ID) as 'Total Tickets'
from accounts ac
join tickets t on
ac.Account_ID = t.Account_ID
group by ac.account_ID, ac.account_name

I think the only error in your query is that you aliased "tickets" as "ticket", but your query is also more complex than it needs to be.


MySQL auto increment column increases after insertion error occurs I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row inserted in this statement.

Thereby, this behavior causes the next valid insertion skip an available and possible value for the auto_increment.

Example:

1st Insert | Result: Success | id(auto_increment_column): 1       |
2nd Insert | Result: Failure | -------- No rows were recorded---- |
3nd Insert | Result: Success | id(auto_increment_column): 3       |

I want to know if someone had this problem before and if this is really a problem.

Cheers.

P.S.:

Errno: 1452 - fk constraint fails. This means that I'm trying to reference some column value in another table and this value doesn't exists there.

Database: MySQL Ver 14.14 Distrib 5.6.13, for Windows 64-bit
Storage Engine: InnoDB

Last Sentence of MySQL Documentation on Traditional InnoDB Auto-Increment Locking says

  You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

Therefore, if the second INSERT had failed and rolled back, it makes the gap.

You may have to set innodb_autoinc_lock_mode to 0 or 2. Please read the MySQL Documentation on Configurable InnoDB Auto-Increment Locking to understand what changing innodb_autoinc_lock_mode can do for you.


PostgreSQL return word after matching first character I would like to return the words beginning with ':' from 'select :barns, :fowl, :cows from farm' using regexp_matches.

So far I have:

select regexp_matches('select :barns, :fowl, :cows from farm', '([:])\w+', 'g')

But this only returns the set {:}, {:}, {:}. I would like it to return {:barns}, {:fowl}, {:cows}

Thanks!

Perhaps this will fit your needs?

SELECT regexp_matches('select :barns, :fowl, :cows from farm', '(:[a-zA-Z]*)', 'g');

Cheers!


Locking, deadlocking workaround while process data at the same table My database has a table: tableX.

Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate.

At the same time other Task2 SELECTs not processed records (inserted by Task1) and then inserts new records (as processed) to the same tableX in one transaction (it's too long calculation, up to 10 seconds).

Task3 recalculates UPDATED records (by Task1): just UPDATEs tableX.

Could someone recommend design to avoid long locking and deadlocks for this scenario.
It's very important to process all INSERTED records in order as its were inserted!

1) Should I separate (create a new tableX2) processed and non-processed records?

2) Should I separate transaction in Task1 and do SELECTs and INSERTs in two separated transactions?

3) Should I use rowlock, readpast...hints and where (can it help me)?

Simple this one.

Task1 should not insert into the main table, but a staging table, say tableXstaging. This table should either include an identity column or a row_processed (TINYINT or BIT) column.

Task1 keeps inserting all the time into tableXstaging. I would allow updates to update the main table directly as they are rare.

Another task which runs once every minute will either: set the MaxID = max(identity column) from tableXstaging, or if you instead used row_processed, set row_processed = 1. This gives you the batch to work with. This task will insert into tableX all records where identity<=MaxID, or if your using row_processed, WHERE row_processed = 1.
A single insert into your primary tableX is very fast. Then delete from tableXstaging where row_processed = 1, or WHERE identity<=MaxID. I would go with identity as you don't need to update row_processed = 1 where row_processed = 0, and so will be faster.

tableX is now only inserted into once a minute.

Actually re-reading your question, i'd go with row_processed, set row_processed = 1 for your first batch.

Task 2 works on the staging table WHERE row_processed = 1. Then sets row_processed = 2.

Task 3 works on the staging table WHERE row_processed = 2. Then sets row_processed = 3.

Finally, SET XACT_ABORT ON; BEGIN TRAN, you insert the records into tableX in one hit (row_processed=3). This is very efficient as the staging table is tiny, and tableX will be quite large and slow (by relative terms to the staging table). Then delete from staging where row_processed = 3. COMMIT;


Build array from boolean columns I have columns like role1, role2, role3, etc. They are all booleans.

I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the view would contain ["role1", "role3"].

Is there any good way to do this that doesn't explode into a ton of CASE WHEN´s? To clarify, I'd be fine with O(n) with CASE WHEN, but not O(2^n) which is what it currently seems to need. :)

You can convert the row to a JSON value and then use a sub-select:

select t.id, x.roles
from the_table t
  cross join lateral (
     select array_agg(col) as roles
     from jsonb_each_text(to_jsonb(t)) x(col,val)
     where x.val::boolean
       and x.col like 'role%'
  ) x


How to Filter values in two tables using inner join? How to filter values in two tables using inner join.

There are two tables Employee and SalaryIncrement:

Sample data:

CREATE TABLE Employee
(
EmployeeId INT IDENTITY(1,1) NOT NULL,
FullName VARCHAR(255) NOT NULL,
MonthlySalary MONEY NOT NULL
PRIMARY KEY(EmployeeId)
)

INSERT INTO Employee (FullName, MonthlySalary) VALUES ('John', 12000.00)
INSERT INTO Employee (FullName, MonthlySalary) VALUES ('Lee', 20000.00)

CREATE TABLE SalaryIncrement
(
SalaryIncrementId INT IDENTITY(1,1) NOT NULL,
SalaryIncreaseFrom DATE NOT NULL,
AmountOfSalaryIncrease MONEY NOT NULL,
EmployeeId INT NOT NULL
PRIMARY KEY(SalaryIncrementId)
)

INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2012-02-05', 5000.00, 2)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2012-02-05', 1000.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2013-05-05', 2000.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2013-04-05', 1000.00, 2)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2014-08-08', 1500.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2016-03-01', 500.00, 1)

From the above two tables I need to retrieve data like in this image below.

I also Need to display additional two columns Old Monthly Salary and New Monthly Salary calculated by Amount of Salary increased.

Result:

The first thing to do is a self JOIN on SalaryIncrement. This will be used to calculate the SUM of SalaryIncrease.

Query:

SELECT s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease, SUM(s2.AmountOfSalaryIncrease) as OldSalary
FROM @SalaryIncrement s1
INNER JOIN @SalaryIncrement s2
    ON s1.EmployeeId = s2.EmployeeId AND s1.SalaryIncreaseFrom <= s2.SalaryIncreaseFrom
GROUP BY s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease

Then this query only needs to be JOIN to Employee and it can do the math between the SUM and the current MonthlySalary (sample link).

Query:

SELECT sal.SalaryIncrementId, sal.SalaryIncreaseFrom, emp.MonthlySalary-sal.OldSalary, sal.AmountOfSalaryIncrease,  emp.MonthlySalary-sal.OldSalary + sal.AmountOfSalaryIncrease
FROM Employee emp
INNER JOIN (
    SELECT s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease, SUM(s2.AmountOfSalaryIncrease) as OldSalary
    FROM SalaryIncrement s1
    INNER JOIN SalaryIncrement s2
        ON s1.EmployeeId = s2.EmployeeId AND s1.SalaryIncreaseFrom <= s2.SalaryIncreaseFrom
    GROUP BY s1.SalaryIncrementId, s1.EmployeeId, s1.SalaryIncreaseFrom, s1.AmountOfSalaryIncrease
) as sal
    ON sal.EmployeeId = emp.EmployeeId
ORDER BY sal.EmployeeId, sal.SalaryIncreaseFrom

Output:

SalaryIncrementId   SalaryIncreaseFrom  OldMonthlySalary    AmountOfSalaryIncrease  NewMonthlySalary
2                   2012-02-05          7000,00             1000,00                 8000,00
3                   2013-05-05          8000,00             2000,00                 10000,00
5                   2014-08-08          10000,00            1500,00                 11500,00
6                   2016-03-01          11500,00            500,00                  12000,00
1                   2012-02-05          14000,00            5000,00                 19000,00
4                   2013-04-05          19000,00            1000,00                 20000,00


Mysql master-slave config in my.cnf I'm following this guide to setting up a master-slave config (https://support.rackspace.com/how-to/set-up-mysql-master-slave-replication/) and i'm confused about the end of the document where they are setting up the SLAVE

WHen setting up the slave, they suggest putting the following into the SLAVE my.cnf file

bind-address = 0.0.0.0
server-id = 2
master-host =  [private-IP-of-db01]
master-user = [replication-username]
master-password = [replication-password]
master-connect-retry = 60

Then later on they suggest logging into mysql and then telling the slave where to start replicating from

# mysql -u root -p
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[private-IP-of-db01]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed-on-master-status];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

My question is specifically with the MASTER LOG FILE and MASTER LOG POS.  Are these things not to be set in my.cnf on the slave?

I ask this because if I have to restart the slave mysql for whatever reason one day, how will the slave know where to pick up from?  Do I have to go through the process of re-impoorting the DB to know the position again?  Or do I have to save the position # before taking the slave DB down?

Thanks!

Are these things not to be set in my.cnf on the slave?

No, You only can set those values during execution time (using a mysql client tool)

  if I have to restart the slave mysql for whatever reason one day, how will the slave know where to pick up from? Do I have to go through the process of re-importing the DB to know the position again?

MySQL stores replication variables in files located by default in the data directory (Usually theses file names contain "relay" and "master" words) and it updates those file with every transaction, so no worries, if you need to restart your slave, the server already knows how to catch up the master.


Select multiple values in LIKE Operator I have a SQL query given below, I want to select multiple value using like operator.

Is my Query correct?

SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt
FROM       employee
INNER JOIN employee_mdata_history
ON         employee.ident=employee_mdata_history.employee_ident
WHERE      employee_id like 'emp1%' , 'emp3%'
ORDER BY   rx_dt desc

If not, can anyone correct me?

My table has large amount of data starting with 'emp1' and 'emp3'.
Can I filter the result by top 3 "emp1" and top 2 "emp3" based on rx_dt?

Alternatively you can try the following method:

SELECT
  x.*
FROM
  (
    VALUES
      ('emp1%', 3),
      ('emp3%', 2)
  ) AS v (pattern, row_count)
  CROSS APPLY
  (  -- your query
    SELECT top (v.row_count)
               employee_id, employee_ident, utc_dt, rx_dt
    FROM       employee
    INNER JOIN employee_mdata_history
    ON         employee.ident=employee_mdata_history.employee_ident
    WHERE      employee_id like v.pattern
    ORDER BY   rx_dt desc
  ) AS x
;

The VALUES row constructor represents your pattern list as a table, additionally supplying each pattern with the number of rows to retrieve for that pattern. The CROSS APPLY operator applies your query to every row of the pattern list, i.e. to every pattern, limiting the number of rows for each pattern to the corresponding value from the pattern list.

As a side note, please let me take this opportunity to suggest that you always qualify your columns with the table alias in a query that is reading from two or more tables. That makes your query easier to read/understand. You can always use short aliases to avoid repeating potentially long table names. For instance:

SELECT TOP (1)
  e.employee_id,
  h.employee_ident,
  ...
FROM
  dbo.employee AS e
  INNER JOIN dbo.employee_mdata_history AS h
    ON e.ident = h.employee_ident
WHERE
  e.employee_id LIKE ...
ORDER BY
  ...


Does a filtered index improve searching on the opposite condition? Does having a filtered index on a column improve queries with a WHERE clause that is the opposite of the filter condition?

Since the filtered index specifies what not to return, does it reduce the number of records to search?

Example:

CREATE TABLE MyTable
(
  ColumnA INT PRIMARY KEY CLUSTERED,
  ColumnB INT NULL
)

CREATE NONCLUSTERED INDEX IX_MyTable_Filtered_ColumnB
  ON MyTable (ColumnB)
  WHERE ColumnB IS NOT NULL

The index should improve this query:

SELECT ColumnA
FROM MyTable
WHERE ColumnB IS NOT NULL

Will it make any difference to this one?

SELECT ColumnA
FROM MyTable
WHERE ColumnB IS NULL

nope. You'll probably even get an error if you try to force your 'IS NULL' query to use your filtered index with an index hint.


How can I enforce ensure uniqueness of an association with a time dimension? I have a database table AB which tracks the association of A and B over time:

create table AB columns (
   ARef ...,  -- FK of A
   BRef ...,  -- FK of B
   Since as Timestamp,
   Until as Timestamp,
   SomeAttrib1 ...,
   SomeAttrib2 ...
);

I would like to declare a unique key such that the same A and B can only be associated together once during the same interval.

What strategies might I use for expressing such a constraint?  Clearly, just doing a PK on ARef,BRef,Since,Until will not suffice - that would only prevent duplicates of the exact same interval.  I need to consider how a row's interval overlaps or does not overlaps with the intervals of other rows in the table.

Parameters to consider:

I am using SQL Server 2012, but I'm also interested to know what facilities are available in other RDBMS's.
Although I realise that keeping this 'history' or 'time dimension' could be considered an 'analytical' use of a database, I'm not wanting a solution that uses a 'dimension' or 'cube' ... this is an ordinary transactional database for an application which needs to assign different attributes to associations which occur at different times.
I'm not heavily committed to the timestamps ... happy to consider 'interval' datatypes if they would help.

I was thinking because this was a calculation it'd have to be a trigger but in doing some googling there are better options.

Overlapping intervals look like they can be blocked by a particular pattern of constraints (http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx)

This slideshare walks through 'Exclusion' constraints which are part of PostgreSQL 9.0 and above and also touches on the pitfalls of approaches like triggers (http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints)

This post titled 'Non-Overlapping Dates Constraint' covers several approaches for Oracle (http://jeffkemponoracle.com/2012/08/30/non-overlapping-dates-constraint/)

My Google search query was 'unique time interval constraint database'


Is row based or column based raw input better? I have a vendor that is starting to give me some rather large reports on course completions and usage.

They have two formats for the reports:

If a student is enrolled in a course, they will have an row entry with basic student and course information for each course.  Fields would be student name, student id, student email, course name, course status, course completion date.
Each student would have one row.  Each course would be represented by a different column.  So we would have student name, student id, student email, course name A, course status A, course completion date A, course name B, course status B, course completion date B, course name C, course status C, course completion date C, course name D, course status D, course completion date D...

The good point of report #2 is that it is much smaller than the report #1.  However I am having a hard time getting a handle on executing views and jobs based on a variable column table (as courses get added I don't really know).

I need to make some advanced usage reports and wrap up student info with certain courses and completions.  Is there a reason why one of the raw formats will either make my job easier or make the database perform queries easier?

From a database design point of view, #1 is better.  But...  In either case (#1 or #2) you should not populate your database with only one table.

You should have these tables (at least):

Students - one row per student: Student id, name, email
Courses - one row per course: Course id (created by you), course name
StudentCourses - one row per student-course pair that exists: student id (but no other student info), course id, status, completion date.


Restore a point between two full backups I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:

Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past.
Restore the server with Oct 1 full backup with NORECOVERY option.
Try to restore to the point at Oct 5 12:00, with the transaction log.

But the restore fails and  SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?

Thanks

You can do a point in time recovery to Oct 5th if you have only full backups and a last transction log backup (called a tail log backup).
Your transaction log backup contains all transactions back to the last log backup. So that should be sufficient to do a point in time recovery.

That you are getting the message "transaction log does not contain the point" indicates one of the following:

There were other transaction log backups taken before your last transaction log backup.
The database was in Simple recovery mode somewhere between the 1st and the 23rd of October.

The reason your transaction log is so big is that it was not backed up regurarly. As transactions happen, the log grows. A transaction log backup truncates your transaction log, and then the empty space can be used for new transactions. The reason can also be that there was a big amount of data modified.

Recommendation: regurarly backup your database (for example every day) and your transaction logs (at least every hour or even more often, depending on the activity in the database). Keep those backups in a safe place. Then you will be able to do proper point in time recovery.


How to access to DB Instance from another DB Instance with Windows Authentication I have two database servers

DB_INSTANCE1
DB_INSTANCE2

Is it possible to access from DB_INSTANCE1\DB1 SQL script to DB_INSTANCE2\DB2 tables without using SQL Logins? (not every database servers are in MIXED mode)

Can I somehow set this in terms to use only AD security groups or DB roles?
What is the recommendation practice?

You will have to setup a linked server forwarding the user credentials between the instances. There are step by step instructions here:

It boils down to running this on  DB_INSTANCE1\DB1

EXEC sp_addlinkedserver
 @server=N' DB_INSTANCE2',
 @srvproduct=N'',
 @provider=N'SQLNCLI',
 @datasrc=N' DB_INSTANCE2\DB2';

sp_addlinkedsrvlogin @rmtsrvname = 'DB_INSTANCE2', @useself = 'TRUE'

Which creates a linked server named db_instance2 and tells the server to use the logged on user credentials to connect to the linked server. If you have configured Kerberos correctly for DB_INSTANCE1 users with permissions on both the servers will be able to select data from the other server: select * from db_instance2.master.sys.databases

As stated before, for this to work Kerberos has to work and therefore you will need to setup SPN's and do some active directory magic. The Kerberos configuration manager will help you and your AD administrator to set up everything needed

It also means that users that connect with database logins will not be able to connect to the server - For that you will have to create user mappings either in the GUI or using TSQL

sp_addlinkedsrvlogin
 @rmtsrvname = 'DB_INSTANCE2',
 @locallogin = 'Local_SQLLogin',
 @rmtuser = 'RemoteSQLLogin',
 @rmtpassword = 'rmtpassword'


Correct case for each reserved word in SQL Server? Is the most correct spelling for int upper case INT or lower case int?

There are many other reserved words in SQL Server.

How to find out the best case for each word?

For example the picture below is a random piece of code, with many different words in it, how can I verify that those words have the, so to say, 'right' case spelling?

Would these things matter in case I have a CS (case sensitive) database?
Would any interfere in the performance?

Let's say select 1 from table1 where 1=1 and SELECT 1 FROM table1 WHERE 1=1?

How to Convert Data to Proper Case In SQL Server

So,

Writing it as an answer for anyone else who might need this in the future-the answer to this question is described very nicely in the Why I use lower case for data type names (now) blog post from Aaron Bertrand.

As he explains in his blog post, this is important when using different database collations from the default one, for example (from the blog):

Considering this database:

CREATE DATABASE floobmort COLLATE Latin1_General_BIN2;
GO
USE floobmort;

The following query will provide an empty result set because "That comparison tries to compare N'INT' to what's stored in the table, N'int', and because the comparison is done using a binary collation, it returns false."

SELECT system_type_id
FROM sys.types
WHERE [name] IN (N'INT', N'VARCHAR');


How can I prevent gaps in a MySQL table AUTO_INCREMENT field? For example, I have a table for user registration:

CREATE TABLE IF NOT EXISTS test_user (
   user_id int(10) unsigned NOT NULL AUTO_INCREMENT,
   user_name varchar(50) NOT NULL,
   PRIMARY KEY (user_id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I want the user_id field ID numbers to be continuous, without gaps.

Failed transactions will occupy some ID numbers, is there any way around this?

An auto increment column will always provide the highest available id for a new user in your case.

However re-using missing numbers from failed transactions is not possible in a single step, since the primary key will probably have been used in other tables which will need to be updated.

There are no performance issues associated with having missing numbers, and there are a common occurrence. This can only be an issue if you have more failed transactions than those which complete, however there I would recommend you set a larger size for your primary key.


SQL Server Patch Level Is Inaccurate, Causing Upgrade Issues I have a box that lists SQL Server 2014 SP2 (12.2.5000) in both add/remove programs and the registry

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
  Server\120\SQLServer2014\CurrentVersion\PatchLevel

However, if I run SELECT @@VERSION it returns 12.0.2000. If I look at the sqlsrvr.exe it also lists 12.0.2000.

Doing some testing I can get a machine in this state (not sure if this is how it happened in production).

Windows 7 SP1 image with SQL Server 2014 RTM (12.0.2000)
Execute SQL Server 2014 SP2 installer as an upgrade (non-automated)
Once on the accept license screen, cancel the install
Reboot the machine

The big problem is that if I execute the SQL Server 2014 SP2 upgrade package against this machine nothing is upgraded. What are my options? Do I just need to tear out SQL Server and install clean? I would love to avoid that if possible as this will have to happen at 200+ locations

EDIT:
Okay, so this is where I say I'm not a DBA and I figured out what was happening. Apparently the "Upgrade" option does not upgrade the instance. Still doing some reading on that, but I can "add features to an existing instance" and get things upgraded. Still digging through, I will post an answer once I have everything squared away. Thank you for all the suggestions thus far.

EDIT 2:
At current this is the installer that was used.
https://www.microsoft.com/en-us/download/details.aspx?id=53167
When running through this upgrade the "Upgrade from SQL Server 2005, 2008, etc. option is chosen. I believe this is the issue at it appears to just updgrade Shared Features? I could use this installer and go through the add features to an existing installation and select the appropriate instances.

I believe this is the installer we should be using as it is a patch to SP2.
https://www.microsoft.com/en-us/download/details.aspx?id=53168
Select all instances and go. Seems easier.

Did I break this down about right?

Don't be a dummy and run the right stuff. Using this patch https://www.microsoft.com/en-us/download/details.aspx?id=53168 and this command everything works great.

  C:\FILELOCATION\SQLServer2014SP2-KB3171021-x64-ENU.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances


SSL connection to AWS RDS Postgresql instance from windows client I am trying to connect to a PG instance hosted on AWS RDS using a secure SSL connection. Where do I store the public key on windows so that it is automatically applied to the connection?

Please note that when I open PGAdmin it only lets me specify .crt and .key files. Is there a way to point it to .pem files?

I downloaded the public key referenced here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

On Linux it is a simple matter of putting this key into the default OpenSSL directory and postgres seems to detect it and take care of the rest itself.

On Windows I have not figured out how to do this or where to store the .pem file or how to point PGAdmin to a .pem file.

If you are using PGAdmin, when you create a new connection there is a tab called SSL[1]. There you can input your pem file.

If you are using psql, put your pemfile on %APPDATA%\postgresql\ directory. See more details at the documentation[2].

Hope it helps.

References:

https://www.pgadmin.org/docs/dev/connect.html?highlight=ssl
https://www.postgresql.org/docs/9.2/static/libpq-ssl.html


Postgres Greatest Query not returning correct timestamp I think I might be writing this query incorrectly. Essentially, I want to find the object with the latest timestamp across four possible values (in this example, user_id is 1).

SELECT *, GREATEST(books.updated_at, books.deleted_at, posts.updated_at, posts.deleted_at)
FROM books, posts WHERE books.user_id = 1
OR posts.user_id = 1

This will always return a time for updated_at, but even if the deleted_at time for one of the objects is the latest, it will not return a deleted_at time. I know, because running a query for deleted_at IS NOT NULL returned a later timestamp.

What am I doing wrong here?

So, I got it figured out and I'm leaving the answer for posterity.

Essentially, remove the * from the query and add a LIMIT 1 and it all appears to work. In addition, as @ypercubetm stated, you can use AND in place of OR, but it depends on your usecase.

SELECT GREATEST(books.updated_at, books.deleted_at, posts.updated_at, posts.deleted_at) AS greatest_time
FROM books, posts WHERE books.user_id = 1
OR posts.user_id = 1 ORDER BY greatest_time DESC LIMIT 1;

returns a single, latest time.


Why is my MySQL dump file and my phpMyadmin export file different sizes? I am looking to create a cron job to backup my MySQL database. I am using this command:

$ mysqldump --user="User" --password="Password" -A
    --single-transaction > /home/user/Export-`date +"%F"`.sql

This results in a 2.5 MB file size. However, when I use the phpMyAdmin export feature using the same user, I get a 3.7 MB file.

Can someone please explain the differences, and if I'm doing anything wrong?

DISCLAIMER : Not a User of PhpMyAdmin

I can explain what mysqldump does. When you run mysqldump, only data is dumped in SQL statements. Indexed are not copied. Indexes get rebuilt when the SQL is loaded into another server.

Here is an example

I have a query I run to show index and data usage.

Here is that query and its output

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
    -> CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
    -> (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
    -> SUM(data_length+index_length) TSize FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('mysql','information_schema','performance_schema') AND
    -> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
    -> (SELECT 2 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| BLACKHOLE      |             0.000 MB |             0.000 MB |             0.000 MB |
| MEMORY         |             0.000 MB |             0.000 MB |             0.000 MB |
| FEDERATED      |             0.142 MB |             0.000 MB |             0.142 MB |
| MyISAM         |             8.049 MB |             0.368 MB |             8.417 MB |
| InnoDB         |            10.828 MB |             6.766 MB |            17.594 MB |
| Total          |            19.019 MB |             7.134 MB |            26.153 MB |
+----------------+----------------------+----------------------+----------------------+
6 rows in set (0.62 sec)

mysql>

Notice that my PC has 19.019 MB of data and 7.134 MB. mysqldump will only dump the 19.019 MB of data. The indexes are not exported.

If phpmyadmin uses mysqldump, then I only have one possible explanation. mysqldump uses the option --opt by default. Here is is what --opt does

  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.

If phpmyadmin summons mysqldump using --skip-opt, then notice --extended-insert is not enabled. With --extended-insert enabled, a batch of rows is inserted using INSERT INTO. Using --skip-opt or --skip-extended-insert will cause each row in every table to have a separate INSERT INTO. Consequently, this would blow up the size of the SQL script. Subsequently, a reload would be longer than usual.


Tracking changes in stored procedures within a database Could you please help me out here.

I have multiple databases and each of the databases have the same tables and stored procedures.

I want to be able to compare the stored procedures and see if there were any changes made to one stored procedure in one database, that has not been replicated across the remaining databases.

I also want to be able to fine out if there were changes made to any tables (structure or new tables added in one and not in the rest). I do not need to know if the data within a table has been updated or if one table has more rows than the other.

Don't be tempted to re-invent the wheel. This problem has been solved many times before you came across it.

Take a look at schema comparison tools, such as those available in SQL Server Data Tools (SSDT), and commercial options like Red-Gate SQL Compare.

Going forward you can also use DDL Triggers to log certain DDL events, helping you catch outlying changes that only happened in one place. If you want to do more reading, I have a few tips about this handy feature here, here and here.


How to update Large table in MYSQL I want to update a large number of rows (more than 80k) from an external data source (from file) into an existing mysql table. This database is InnoDB.

Database structure:

CREATE TABLE IF NOT EXISTS `jobs` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `job_date` date DEFAULT NULL,
 `client_code` varchar(100) DEFAULT NULL,
 `department` varchar(100) DEFAULT NULL,
 `job_order` float(10,2) DEFAULT NULL,
 `completion_date` date DEFAULT NULL,
 `achivement_date` date DEFAULT NULL,
 `achivement_value` float(10,2) DEFAULT NULL,
 `current_status` varchar(100) DEFAULT NULL,
 `supervisor` varchar(100) DEFAULT NULL,
 `job_no` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

Update QUERY

UPDATE jobs SET job_date='$job_date',client_code='$value[1]',department='$value[2]',job_order='$job_order',completion_date='$completion_date',achivement_date='$achivement_date',achivement_value='$achivement_value',current_status='$value[7]',supervisor='$value[8]' WHERE job_no='$value[9]

How to optimize this query ?

If the job_no is unique it could be much faster (adding a unique index to the job_no). but even if its not unique this query could be optimized just adding an index to the job_no

ALTER TABLE jobs
ADD INDEX ind_job_no (job_no) USING BTREE;

adding this index will allow you to update quicker.


PostgreSQL to MySQL migration to use database for WordPress site - how to's? I have a complex issue to solve, related to database migration. I have a website full of news to migrate that is currently based on a PostgreSQL database and written in PHP, without a CMS. I have a dump of the database that is a huge text .sql file of 300.000+ lines (finished completely with success); the new version of the website should be based on WordPress.

Could you please kindly suggest some best practices of migrating this database dump properly to a MySQL database and importing it afterwards the right way into WordPress, e.g. through PHPMyAdmin? What procedures are to follow so that the database works with the WordPress install so that one can start with the front-end part.

Thank you very much in advance for your help!

Any specific reason to migrate to MySQL?

https://wordpress.org/plugins/postgresql-for-wordpress/

Else MySQL Workbench will work. You'll always have to take into consideration any functions/triggers, etc. that might contain advanced pgsql syntax, etc.

http://mysqlworkbench.org/2012/11/how-to-migrate-postgresql-databases-to-mysql-using-the-mysql-workbench-migration-wizard/

As @Max said, good luck!!


database_xml_deadlock_report or xml_deadlock_report Is [database_xml_deadlock_report] the same as [xml_deadlock_report] just with extra fields?

I'm creating a basic deadlock and blocking capture and see both.  Most examples online show [xml_deadlock_report], but [database_xml_deadlock_report] has extra fields that may be useful.

They are essentially the same thing. database_xml_deadlock_report was introduced to provide a method for retrieving deadlocks for Azure SQL Databases by scoping the deadlock event to the victim database and allowing users to create database scoped extended events sessions.

See this link for more information. Basically, the XML Report event field should return the same information and that is where the important data is. The other three fields (database_name, server_name and deadlock_cycle_id) are obtainable from within the XML report or from the global fields.

You can basically use either one.


Query of sys.login_token doesn't include particular windows group while connect within workgroup I had two SQL Server Logins which correspond to two windows groups:

MachineName\MyAppAmdin
MachineName\MyAppUser

And I use

select principal_id, name, [type], usage
from sys.login_token
where principal_id > 0

to check if the current user is a member of any of these windows groups.

When the machine is under domain it will list

+--------------+---------------------------------+---------------+---------------+
| principal_id |              name               |     type      |     usage     |
+--------------+---------------------------------+---------------+---------------+
|            2 | public                          | SERVER ROLE   | GRANT OR DENY |
|            3 | sysadmin                        | SERVER ROLE   | GRANT OR DENY |
|          260 | MachineName\SQL Server Services | WINDOWS GROUP | GRANT OR DENY |
|          309 | MachineName\MyAppUser           | WINDOWS GROUP | GRANT OR DENY |
|          325 | MachineName\MyAppAmdin          | WINDOWS GROUP | GRANT OR DENY |
+--------------+---------------------------------+---------------+---------------+

as expected.

However, when I try another the machine which is under workgroup it only lists

+--------------+---------------------------------+---------------+---------------+
| principal_id |              name               |     type      |     usage     |
+--------------+---------------------------------+---------------+---------------+
|            2 | public                          | SERVER ROLE   | GRANT OR DENY |
|            3 | sysadmin                        | SERVER ROLE   | GRANT OR DENY |
|          260 | MachineName\SQL Server Services | WINDOWS GROUP | GRANT OR DENY |
+--------------+---------------------------------+---------------+---------------+

Even the account is actually a member of MyAppUser or MyAppAdmin.

Well, perhaps there are other factors I didn't notice but so far workgroup vs domain is the only difference I knew.

Is it supposed to work this way or I got something wrong?

The problem solved after I remove logins and associated users in SQL Server then add them back again.
Have no clue what went wrong but a record for people who might run into the same issue.


What are the implications of changing the SQL Server Service Logon Account? What are the implications of changing the SQL Server Service Logon Account?
My default instance currently uses Network Service.
I want to change this to a domain account.
Are there any implications?

If you are talking about the immediate impact, then the service will need to be restarted in order for that change to take effect.  As per TechNet:

  When you change the service startup account for the Database Engine and SQL Server Agent, the SQL Server service (the Database Engine) must be restarted for the change to take effect. For more information, see Additional Considerations for Changing Service Accounts below. When the service is restarted, all databases associated with that instance of SQL Server will be unavailable until the service successfully restarts. If you have to change the service startup account of SQL Server or SQL Server Agent, make sure that you do so during regularly scheduled maintenance or when the databases can be taken offline without interrupting daily operations.

Using a domain account as the SQL Server service account is considered best practice, so you are making a step in the right direction.

For further reading, the TechNet reference on Change the Service Startup Account for SQL Server.


Getting an error in MySQL Workbench: Cannot Open Administrator I'm using local installation of MySQL CE on Windows 7 64-bit.  Tried to use Administrator part for the first time.

Here's the error I'm getting:

I also see the following in the log:

<object db.mgmt.Connection> key=connection could not be resolved

This information is probably not enough to diagnose the problem.  Please let me know what I need to post so I can get some help.

You are using an old MySQL Workbench version. Please update to the latest version: http://mysqlworkbench.org/2014/04/whats-new-in-mysql-workbench-6-1/.

In older versions of MySQL Workbench we had connections and management instances separated. So it could happen that you have a server instance without a connection assigned. You could edit that instance to re-assign a connection to make it work.

Since version 6.0 this separation is gone. You only have a connection with all the necessary settings for management too.


In MySQL, how to manually edit values of a BLOB column in few tuples? In MySQL, how to manually edit values of a BLOB column in few tuples?

For convenience of manual curation of database like this, should the column be created as BLOB or TEXT, or something else at the first place?

When deciding to store the field as BLOB or TEXT, this paragraph might be helpful:

BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set. [src]

The difference outlined here being in the way values are compared and sorted.
You can use the UPDATE statement to modify the values.
Now, from some of the comments, I garnered that this field is a primary key. If this table is InnoDB, and this table has secondary indexes (other indexes that are not primary key), and you expect the table to become very large, then be aware how InnoDB handles secondary indexes.
InnoDB will store the primary key along with the value of the secondary indexes, so your index will grow much larger than if you used an autoincremented int column as the primary key.


Oracle Express intermittently stops responding, requiring a OS reboot to fix I've been having an issue with Oracle XE the past 2 days and it's really been making me pull my hair out.

There seems to be no rhyme nor reason why this happens, or when it does, but it happened 6 times yesterday and so far twice this morning.  I'm developing a .NET app, and sometimes when I start the app, refresh the page, or do whatever, I get an exception that a connection could not be made to the database.  I then try to connect with sqlplus I just get:

C:\>sqlplus user/password@localhost

SQL*Plus: Release 11.2.0.2.0 Beta on Wed May 1 11:08:53 2013

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

It then just sits there forever.  No timeout, no error, no nothing.  I can't connect with any other database program either, the whole server is just unresponsive.

If I go into the Services manager, I see that the services are indeed running so nothing has crashed:

I've tried restarting OracleServiceXE and OracleXETNSListener.  Both will shut down and restart, however I still cannot make a connection to the server.

The only thing that fixes this problem is a complete Windows reboot.  However, this of course takes 5-10 minutes and is really starting to get old.

There does not appear to be anything related to Oracle around this time in the Windows Event Viewer.  I'm happy to add any other information that would help debug.  Any ideas?

Update:

I did notice a few strange errors in listener.log.  These errors start the exact same time the crashes started happening:

30-APR-2013 08:50:42 * 12531
TNS-12531: TNS:cannot allocate memory
Tue Apr 30 08:52:28 2013
30-APR-2013 08:52:28 * 12531
TNS-12531: TNS:cannot allocate memory
Tue Apr 30 08:52:58 2013
30-APR-2013 08:52:58 * 12531
TNS-12531: TNS:cannot allocate memory

Last 1,000 lines of alert_XE.log are here.

You are encountering these problems because you are running a 32-bit version of Oracle XE on 64-bit Windows. This is not a supported configuration, and is known to have many documented problems.

Due to there not being a 64-bit version of XE, you can instead install the Standard Edition (free for development) from here. If you're also using APEX, you can get it here.


Talend collation issue in mysql I am using Talend to backup MS SQL to MySQL Server. Everything goes well, I have a connection, data flows from one db to the other... however I cannot correctly copy diacritic signs.

On MS SQL I have Polish_CI_AS and on MySQL I have Latin2 general_ci.

When I tried to copy data from MS SQL to an Excel file, everything is in place.
I concluded that problem lies in MySQL.

Have you encountered this issue before?

In Talend MysqlOutput -> Component window -> Advanced settings. Add the following:

"useUnicode=true&characterEncoding=latin2"


Recursive relationship in oracle I have a table which stores the customer , related customer  and count of related customers.
The related customer can have again further related customers it's like a recursive relationships.

I wanted to find out all the related customers ( till the last related customer ) for a customer.

Table stats :-

Present volume is 2,00,000 records .

Create table
--------------
CREATE TABLE RELATED_TABLE
( CUS_ID            VARCHAR2(09) ,
  REL_CUS_ID        VARCHAR2(09) ,
  COUNT_OF_REL_CUST NUMBER(12)) ;

Sample data:-
---------------
 INSERT INTO RELATED_TABLE VALUES ('402758970','898196448',3);
 INSERT INTO RELATED_TABLE VALUES ('402758970','855115206',3);
 INSERT INTO RELATED_TABLE VALUES ('402758970','850353774',3);
 INSERT INTO RELATED_TABLE VALUES ('898196448','691094946',3);
 INSERT INTO RELATED_TABLE VALUES ('898196448','404636299',3);
 INSERT INTO RELATED_TABLE VALUES ('898196448','402758970',3);
 INSERT INTO RELATED_TABLE VALUES ('855115206','870397045',3);
 INSERT INTO RELATED_TABLE VALUES ('855115206','855115206',3);
 INSERT INTO RELATED_TABLE VALUES ('855115206','402758970',3);

CUS_ID     REL_CUS_ID    COUNT_OF_REL_CUST
402758970  898196448       3
402758970  855115206       3
402758970  850353774       3
898196448  691094946       3
898196448  404636299       3
898196448  402758970       3
855115206  870397045       3
855115206  855115206       3
855115206  402758970       3

OUTPUT:-
--------------------

402758970  898196448
402758970  855115206
402758970  850353774
402758970  691094946
402758970  404636299
402758970  870397045
402758970  855115206


Select random rows up to a limit SELECT *
FROM my_players
WHERE player_type = 'IF'
ORDER BY cardweight / rand() DESC limit 3

That is my current query, What I am trying to do is select random rows for which I have the * in my query, so I am trying to get the psbin (which is a column in my table) it is a int and I want the psbin of all these rows combined not to go over 250k.

Create table statement is:

CREATE TABLE `my_players` (
`myplayer_id` int(11) NOT NULL AUTO_INCREMENT,
`player_id` int(11) NOT NULL,
`First_Name` varchar(255) NOT NULL,
`Last_Name` varchar(255) NOT NULL,
`Rare` varchar(255) NOT NULL,
`Foot` varchar(255) NOT NULL,
`Height` varchar(255) NOT NULL,
`Date_Of_Birth` varchar(255) NOT NULL,
`player_name` varchar(255) NOT NULL,
`player_nation` int(11) NOT NULL,
`player_club` int(11) NOT NULL,
`player_league` int(11) NOT NULL,
`player_type` varchar(255) NOT NULL DEFAULT 'GOLD',
`player_pos` varchar(255) NOT NULL,
`totwImage` varchar(255) DEFAULT NULL,
`psbin` int(11) NOT NULL,
`xboxbin` int(11) NOT NULL,
`ps3bin` int(11) NOT NULL,
`360bin` int(11) NOT NULL,
`xmin` int(11) NOT NULL,
`xmax` int(11) NOT NULL,
`pmin` int(11) NOT NULL,
`pmax` int(11) NOT NULL,
`cardweight` int(11) NOT NULL DEFAULT '1',
`definitionId` int(21) NOT NULL,
`resourceId` int(21) NOT NULL,
`player_added` datetime NOT NULL,
`player_status` enum('0','1') NOT NULL,
`player_rating` int(11) NOT NULL,
`Attribute1` int(11) NOT NULL,
`Attribute2` int(11) NOT NULL,
`Attribute3` int(11) NOT NULL,
`Attribute4` int(11) NOT NULL,
`Attribute5` int(11) NOT NULL,
`Attribute6` int(11) NOT NULL,
`available` int(11) NOT NULL,
PRIMARY KEY (`myplayer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3207 DEFAULT CHARSET=latin1

If I understand you correctly you want to add up psbin values up to 250.000 from random rows

SELECT *
FROM my_players, (SELECT @psbin := 0) x
WHERE player_type = 'IF'
AND (@psbin := @psbin + psbin) <= 250000
ORDER BY RAND();


Mass update all cells in a column with a substring of it's original value I need to trim the last seven characters off of all the cells in a column.
This query returns the results that I need
SELECT test_date, SUBSTRING(test_date FROM 1 FOR CHAR_LENGTH(test_date) - 7) as test from results;

I now need to take the computed result from the substring and replace the original values.
Here's what the data looks like, if it helps.


MySql LOAD DATA with subquery performance issue I'm migrating data into a MySql (5.7.26) database (32GB ram), running as a managed service on AWS. While importing the data, I need to map one of the columns of the CSV being imported to another value using a MEMORY table lookup; so my LOAD DATA resembles the following:

LOAD DATA LOCAL INFILE 'file.csv'
INTO TABLE table_1(col_1, @var1)
SET col_2 = (select mapped_value from table_2 where id = @var1)

table_2 is a 2-column (id, mapped_value) MEMORY table with 3.4MM rows.

When I import the CSV without the subquery, I get several million inserts per minute. However, when I run the same import with the subquery the LOAD DATA performance degrades to near zero (~100 inserts per minute). Is this to be expected with a subquery, or is there something I'm doing wrong in the example above?

This is probably due to the fact that memory tables by default use a hash index instead of a btree index. Read more about it here.
Or maybe you don't use an index at all in your memory table?
A pretty similar performance problem is described in this blog post: Performance gotcha of MySQL memory tables
If you don't have an index on the id column, add one (btree!)

The MEMORY storage engine supports both HASH and BTREE indexes. You can specify one or the other for a given index by adding a USING clause as shown here:
   CREATE TABLE lookup
       (id INT, INDEX USING HASH (id))
       ENGINE = MEMORY;
   CREATE TABLE lookup
       (id INT, INDEX USING BTREE (id))
       ENGINE = MEMORY;

If you already have an index on that column, check with
show indexes from table_2;

of what index_type your index is. Convert to btree if necessary.


Does restarting Windows restart the SQL Server? Doesn't restarting Windows also restart the SQL Server?
Long story is that I just made the change so that both SQL Server and Windows authenticated users can log in. The server needs a restart for the change to work, can I just restart the machine instead of the server?
My server is having configuration issues, I wanted to change one of the settings so that SQL credentials can also log in (instead of just Windows authentication ). My admin said you can do any changes, but don't restart the SQL Server. I asked can I reboot the Windows server which the SQL Server is on, and he said yes.
Doesn't restarting the machine do the same as restarting the SQL Server?

Assuming you have a single MS SQL Server instance on a single Windows machine the answer is a very definite resounding yes, restarting Windows will mean restarting all the services running on it including SQL Server.
If you had some sort of clustering with automatic fail-over etc. configured and your apps behave with it properly (i.e. they don't somehow bypass the mechanism and connect to single host), then like other multi-node solutions (load balanced web servers and such) you should be able to drain-stop the node and restart without it affecting anything else (other than a performance drop if you have an active-active load balances setup instead of mirroring with fail-over), then do the same on other hosts in the cluster to make sure everything is updated. Even with a fail-over or load balanced solution, when the updated node comes back into service there may be some impact as there will be nothing in the buffer pool so initial queries will be slower than usual until the normal working set is loaded.


When does creating a .sql backup with mysqldump become too large? Is there a limit to how large a database can be when executing a .sql backup using mysqldump, or will you run into server problems before that - will the server not be able to take such a large backup file?

I'm thinking in the levels of:

1 - 10GB
10 - 100GB
100 - 500GB

I can only think of three(3) things that can cause a mysqldump to be too large
PROBLEM #1 : Disabling Extended Inserts
Extended Inserts (--extended-insert) is on by default via the --opt option. If you issue --skip-opt or --skip-extended-insert, every INSERT command will quickly become hundreds or every thousands. Such a mysqldump can still be loaded but takes longer because of the absence of batch inserts.
PROBLEM #2 : BLOB Data
When dumping a table with BLOB data, there is a small possibility that the dump may not be portable. To make such data portable, some resort to the --hex-blob option. This write the BLOB as text-represented hexadecimal. When using this, you should expect a more bloated mysqldump.
CAVEAT : Don't use --hex-blob and --skip-extended-insert together
PROBLEM #3 : MySQL Packet
Most people take the MySQL Packet for granted. Some mysqldump could time out if you not paying attention. mysqldump's default for max-allowed-packet in 24M. Extended inserts can benefit from a larger packet. It could possibly increase the number of rows per extended INSERT command. The savings in size might be nominal at best. Notwithstanding, the larger the dump, the slightly better the reduction of size might be.
UPDATE 2015-02-13 12:37 EST
If your main concern is just sheer size vs. hardware constraints, you dump like this:
mysqldump -uroot -ppasswword ... | gzip > mybackup.sql.gz

and restore like this
gzip -d < mybackup.sql.gz | mysql -uroot -ppasswword

You could use bzip2 instead of gzip (Pros and cons of bzip vs gzip?)


Why does MySQL only sometimes use my index for a range query? I have a table of IP address ranges and their country, here is the structure:

CREATE TABLE `geoIP` (
  `startBlock` int(11) unsigned NOT NULL DEFAULT '0',
  `endBlock` int(11) unsigned NOT NULL DEFAULT '0',
  `code` char(2) NOT NULL DEFAULT '',
  `country` varchar(60) NOT NULL DEFAULT '',
  KEY `startEnd` (`startBlock`,`endBlock`),
  KEY `country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The table is typically used to lookup the country of an IP address. I'm trying to understand why these queries do not always use the 'startEnd' index, as my slow query log is filled with entries like this:

# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 120967
SELECT code FROM geoIP WHERE INET_ATON('82.39.119.217') > startBlock AND INET_ATON('82.39.119.217') < endBlock;

120,967 is the number of rows in the table, so it's doing a full table scan. Here's the explain for that query:

id  select_type  table  type  possible_keys  key    key_len  ref   rows    Extra
1   SIMPLE       geoIP  ALL   startEnd       NULL   NULL     NULL  120967  Using where

Sometimes the index is used though, here's the explain for another - same query, the only difference is the IP address:

id  select_type  table  type   possible_keys  key       key_len  ref    rows    Extra
1   SIMPLE       geoIP  range  startEnd       startEnd  4        NULL   9579    Using where

both queries return 1 row.

Why does MySQL only sometimes use the index? Is there anything I can do to optimise further?

The general behavior of the MySQL Query Optimizer is as follows: If less than 5% of the rows of a table has to be traversed in an index, the index is used. Otherwise, the index is dismissed and a full table scan is done.

Now look at the index: (startBlock,endBlock).

Here are two questions

How many netblock up to the third octet do you have for startBlock ?
How many netblock up to the third octet do you have for endBlock ?

To get a hold of the distribution of netblocks, run this query

SELECT *,
    IF(ABS(endNetBlock - startNetBlock) >= 256,1,0) NotWithinSameRange,
    IF(startNetBlock - endNetBlock > 0,1,0) StartRangeAheadOfEndRange
FROM
(
    SELECT COUNT(1) rangeCount,startNetBlock,endNetBlock
    FROM
    (
        SELECT
            (startBlock - MOD(startBlock,256)) startNetBlock,
            (endBlock - MOD(endtBlock,256)) endNetBlock
        FROM geoIP
    ) AA GROUP BY startNetBlock,endNetBlock
) A

This will give you a general idea of

how many netblock combinations you have
presence of any invalid ranges

If NotWithinSameRange or StartRangeAheadOfEndRange are 1 in any instance, that range has to be clenaed up. You have to be sure that all netBlocks are properly represented.

If you have StartRangeAheadOfEndRange=1, that is a definite problem.

I hope these suggestions provide food for thought.


Deciding on a replication strategy We have several dozen small-medium (<1Gb) databases on our SQL Server 2008R2 (Standard Edition) server, with one or two more being created every month. The server itself is a fairly well spec'd box for the task; plenty of processor, memory, and disk space and throughput. Unfortunately, we don't have a similarly spec'd DBA to consult in-house :(

All the databases are currently in SIMPLE recovery mode, partly for convenience, mostly due to ignorance of how FULL mode works. The databases are being backed up every night (using BackupExec).

We're now looking at the best way to introduce some kind of simple redundancy in our SQL infrastructure; we have a companion server ready of similar spec to the primary server that can be quickly put to use if the primary server fails. Our requirements are fairly basic: we just need some mechanism that will take a copy of the database to another server in a regular basis. We don't need automatic failover, and we don't necessarily need the data to be replicated in real-time: once every 5-15 minutes would be fine, as long as it doesn't impact the performance of the primary server too heavily.

Ideally, the replicated database would be available for read-only queries for reporting et al.
I've looked and experimented with the following options:

Replication. I've had a horrible time with this: we've had one database replicated between servers for over a year, and although it works well most of the time, for no apparently reason the replication breaks every couple of months. I've spent hours (days?) trying to find the cause for this, but the logging is indecipherable and all advice seems to point to ultimate what I've had to do each time - blow away the replication and/or reinitialise. This is no fun on one database; having to do this semi-regularly for potentially dozens of databases is out of the question.
Mirroring. I've played around with this, and it seems to work well, but the mirrored copy database remains in recovery mode, and therefore isn't available for queries.
Log file shipping. I've not looked into this too deeply yet, but the principals seem easy enough. It seems if I set the restore operation to "Standby" I can have the secondary database available for read-only queries. However, I've seen notes that suggest that, in this mode, restores can be time-consuming, and connections may need to be closed during the restore, which may affect us if we start to rely on the read-only copy being available.

Not having much experience of any of the above - we've only really used Replication before, and as mentioned our experience hasn't been good - I'm hoping that someone can advise me on the best strategy to employ. Are there any other methods I should be considering, or any third-party software that might help? Am I being naive, and it's simply not possible to satisfy all our (hopefully modest) requirements?

You're right about each of your options.  Log Shipping is perhaps the easiest way to almost accomplish what you want, but there is the drawback that you won't be able to read from your secondaries while the logs are being restored.  If your goal is simply to become more highly available in case something bad happens, I advise you to at least get your backup strategy up to speed ASAP.  That means putting your databases in FULL recovery mode and performing transaction log backups at a high frequency, and putting those backups on a different disk than your database and transaction log.

One thing you didn't mention was creating a simple shared-disk cluster.  Again, it depends on what type of redundancy you want.  If you're just trying to ensure against a server hardware failure, this might be the way to go.

The technical solution to your problem that gives you the highest availability and flexibility is to upgrade to SQL Server 2014 Enterprise Edition, which has support for AlwaysOn Availability Groups.  AlwaysOn is a technology almost identical to mirroring, but allows for a readable secondary (to be sure, there are other benefits).

That upgrade can be costly, however, since it requires Enterprise Edition.  It's also pretty tricky to configure the first time, and you'll need to make sure you have a good relationship between your DBA-ish people and network administration team to pull it off.  It may even be worth hiring a consultant for this setup alone if you really don't have the DBA resources.  Just make sure the consultant has experience setting up this type of cluster.  It IS something you can pull of yourself if you know enough about networking, security, clustering, and SQL Server, though.  All the resources are out there, you'll just have to do some digging.  Brent Ozar has some good resources you can check out, but you'll probably want to look into a book purchase.

Another nice thing about AlwaysOn AG's is that you can set up an Azure instance as your secondary without making another hardware purchase.  This can be a really smart for DR scenarios where your main data center goes out -- you'll have Azure's completely separate infrastructure to fall back on.

Sorry, I think I didn't answer your question too well, I think I just gave you a lot more to think about.  That's how this goes, I guess...


How to convert different string value to respective 'YYYY' int format? I'm inserting values into table from xml as:

DECLARE @XMLRESULT XML

DECLARE @tbl_XMLResult TABLE
(
    RowID INT IDENTITY(1,1),
    Status VARCHAR(50),
    Address VARCHAR(250),
    ListPrice INT,
    SoldPrice INT,
    YearBuilt INT
)

EXEC usp_GetReportResult @query = @query, @ReportName = @ReportName, @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult
(
    Status,
    Address,
    ListPrice,
    SoldPrice,
    YearBuilt
)
SELECT
    L.value('(ListingStatus/text())[1]','VARCHAR(50)') AS Status,
    L.value('(PMCReportData/Listing_StreetAddress/text())[1]','VARCHAR(250)') AS Address,
    L.value('(ListPrice/text())[1]','INT') AS ListPrice,
    L.value('(ClosePrice/text())[1]','INT')AS SoldPrice,
    L.value('(YearBuilt/text())[1]','INT')AS YearBuilt
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing')AS Result(L)

But as shown above for YearBuilt column the datatype is int in which we are storing just year on which property is built. And accordingly subsequent calculations are done. But some of the time the values for YearBuilt comes from xml as in format

"New"
"0-5 Yrs"
"70+"

But when above type of result for YearBuilt comes in xml we are getting conversion error.
Need to get values from above result as if YearBuilt as:

"New"     - Here we want consider current year and store result as 2016 current year.
"0-5 Yrs" - 0-5Yrs. Here consider upper limit, i.e. 5. So store result as 2011 as(2016 - 5 = 2011)
"70+"     - consider 70 as => 2016 - 70 Year = 1946

How we can do this to store appropriate result into @tbl_xmlResult

Get the value from the XML as a string and do some string manipulation in a case statement.

Example with simplified XML:

declare @X xml = '
<YearBuilt>2017</YearBuilt>
<YearBuilt>New</YearBuilt>
<YearBuilt>0-5 Yrs</YearBuilt>
<YearBuilt>70+</YearBuilt>
';

select case
         when T.Value = 'New' then year(getdate())
         when T.Value like '%Yrs' then year(getdate()) - cast(replace(stuff(T.Value, 1, charindex('-', T.Value), ''), 'Yrs', '') as int)
         when T.Value like '%+' then cast(left(T.Value, charindex('+', T.Value) - 1) as int)
         else cast(T.Value as int)
       end
from @X.nodes('/YearBuilt') as Y(X)
  cross apply (
              select Y.X.value('text()[1]', 'varchar(10)')
              ) as T(Value);


Extracting DDL Events for a database on Azure I have been trying to extract DDL changes from SQL Server via Extended Events, we needed to know the type of change and the user who made the change. We wanted to avoid the use of triggers where possible and liked the idea of extended events as they are not part of the running transaction when a schema change is made.

Doing this on an on-premise server seems to work pretty well, populating a queue from a notification listening for DDL_DATABASE_LEVEL_EVENTS.

However, I need this solution to work on Azure so my first thought was to do exactly the same but the service broker does not work on Azure so I then looked at using EVENT SESSION sending the information to a ring buffer but this does not appear to be able to get the same events.

Is it possible to get this information on Azure via the ring buffer or do I have to revert to using triggers?

Assuming you are on v12, then Extended Events (with Ring Buffer target) are available but there is what is known as a reduced surface area, or less things you can do.  If I run the query below to list available actions, events and targets (sourced from here) I get 941 rows on my SQL Server 2014 and only 71 rows on my v12 Azure database, as at today:

SELECT
        o.object_type,
        p.name         AS [package_name],
        o.name         AS [db_object_name],
        o.description  AS [db_obj_description]
    FROM
                   sys.dm_xe_objects  AS o
        INNER JOIN sys.dm_xe_packages AS p  ON p.guid = o.package_guid
    WHERE
        o.object_type in
            (
            'action',  'event',  'target'
            )
    ORDER BY
        o.object_type,
        p.name,
        o.name;

As you know with Azure, things get added so it may well catch up.

That said, there are three events available which would allow you to track DDL changes on your v12 Azure database:

object_altered
object_created
object_deleted

Based on these three events, I scripted this Extended Events session to track DDL on my v12 Azure database:

IF EXISTS ( SELECT * FROM sys.dm_xe_database_sessions WHERE name = 'xe_track_DDL_changes'  )
DROP EVENT SESSION xe_track_DDL_changes ON DATABASE
GO

CREATE EVENT SESSION xe_track_DDL_changes ON DATABASE
ADD EVENT  sqlserver.object_altered(
    ACTION ( sqlserver.username ) ),        -- <-- NB this was session_nt_username in on-premise SQL Server
ADD EVENT  sqlserver.object_created(
    ACTION ( sqlserver.username ) ),
ADD EVENT  sqlserver.object_deleted(
    ACTION ( sqlserver.username ) )
ADD TARGET package0.ring_buffer ( SET MAX_MEMORY = 4096 )
WITH ( EVENT_RETENTION_MODE = NO_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS );
GO

ALTER EVENT SESSION xe_track_DDL_changes ON DATABASE STATE = START;
GO

-- Do some ddl (in order to track it)
CREATE TABLE test ( id INT IDENTITY PRIMARY KEY )
ALTER TABLE test ADD addedBy SYSNAME
DROP TABLE test
GO

-- Inspect the ring buffer
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO

SELECT IDENTITY( INT, 1, 1 ) rowId, CAST( target_data AS XML ) AS target_data, GETDATE() dateAdded
INTO #tmp
FROM sys.dm_xe_database_sessions s      -- NB this is dm_xe_session_targets in on-premise SQL Server
    INNER JOIN sys.dm_xe_database_session_targets st ON s.[address] = st.event_session_address      -- NB this is dm_xe_sessions in on-premise SQL Server
WHERE s.name = 'xe_track_DDL_changes'
GO

ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
GO
--CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( target_data );  -- !!NB OPTIONAL - may improve performance but needs up to 5x original table size
GO

-- type of change and the user who made the change
SELECT
    t.rowId,
    e.c.value( '@name', 'VARCHAR(100)' ) ddl_action,
    o.c.value( '.', 'VARCHAR(100)' ) objectName,
    a.c.value( '.', 'VARCHAR(100)' ) [user]

FROM #tmp t
    CROSS APPLY t.target_data.nodes('RingBufferTarget/event') e(c)
        CROSS APPLY e.c.nodes('data[@name = "object_name"]/value') o(c)
        CROSS APPLY e.c.nodes('action/value') a(c)
GO

My results:

Remember to drop your Azure Extended Event sessions when you're not using them.  You might also consider an Event File target which writes to an Azure Storage container because the ring buffer can fill up and cycle.

HTH


Why xlog checkpoint was started? Consider the following config

shared_buffers = 20GB
max_wal_size = 2GB
min_wal_size = 1GB

checkpoint_completion_target = 0.7
checkpoint_timeout = 5min

and checkpoints' log

2020-05-25 16:28:37.128 | LOG:  checkpoint starting: time
2020-05-25 16:32:07.098 | LOG:  checkpoint complete: wrote 21420 buffers (0.8%); 0 WAL file(s) added, 0 removed, 7 recycled; write=209.932 s, sync=0.009 s, total=209.969 s; sync files=295, longest=0.002 s, average=0.000 s; distance=45191 kB, estimate=503334 kB
2020-05-25 16:33:37.165 | LOG:  checkpoint starting: time
2020-05-25 16:37:08.001 | LOG:  checkpoint complete: wrote 25041 buffers (1.0%); 0 WAL file(s) added, 0 removed, 25 recycled; write=209.842 s, sync=0.865 s, total=210.835 s; sync files=421, longest=0.120 s, average=0.002 s; distance=82180 kB, estimate=461218 kB
2020-05-25 16:38:02.690 | LOG:  checkpoint starting: xlog
2020-05-25 16:41:32.181 | LOG:  checkpoint complete: wrote 191629 buffers (7.3%); 0 WAL file(s) added, 0 removed, 42 recycled; write=209.239 s, sync=0.049 s, total=209.491 s; sync files=666, longest=0.006 s, average=0.000 s; distance=758070 kB, estimate=758070 kB

Why at 16:41 the xlog checkpoint was started? The distance from prev checkpoint is just 758070 KB and it's greatly less then max_wal_size.

PS

It seems that max_wal_size is spread between several checkpoints, and xlog checkpoint is started if value max_wal_size / (2 + checkpoint_completion_target ) is exceeded. Am I right?

Dig into PG10 source code https://github.com/postgres/postgres/blob/REL_10_STABLE/src/backend/access/transam/xlog.c#L2224, thanks  @pifor for the hint

/*
 * Calculate CheckPointSegments based on max_wal_size_mb and
 * checkpoint_completion_target.
 */
static void
CalculateCheckpointSegments(void)
{
    double      target;

    /*-------
     * Calculate the distance at which to trigger a checkpoint, to avoid
     * exceeding max_wal_size_mb. This is based on two assumptions:
     *
     * a) we keep WAL for two checkpoint cycles, back to the "prev" checkpoint.
     * b) during checkpoint, we consume checkpoint_completion_target *
     *    number of segments consumed between checkpoints.
     *-------
     */
    target = (double) ConvertToXSegs(max_wal_size_mb) / (2.0 + CheckPointCompletionTarget);

    /* round down */
    CheckPointSegments = (int) target;

    if (CheckPointSegments < 1)
        CheckPointSegments = 1;
}

So the max_wal_size is divided across three checkpoints, two prev and the current with checkpoint_completion_target in mind.


Can't load local file into mysql database So I will preface this by saying that I'm not all that skilled in MySQL (or regular SQL for that matter), but I'm trying to load data from a .txt file into my table that I keep for ticket sales. When I do that using:

LOAD DATA LOCAL INFILE '/path/presales1.txt' INTO TABLE presales

I get the '> prompter. And I have no idea what to do. I tried looking up solutions and the one I thought would work was:
--local-infile[=1]; because that is straight out the MySQL handbook. It didn't work (nor did any variation on it...such as local-infile=1;, local-infile=1 etc....)

Did I miss a simple step? My version of MySQL is 5.7

Watch carefully, everything is up my sleeve.

mysql> SELECT 'abc
    '>
    '>
    '> def';
+-----------+
| abc

def |
+-----------+
| abc

def |
+-----------+
1 row in set (0.00 sec)

Notice that my first line has a quote, but not the matching quote.  When I hit return; it prompted me with '> -- That's the clue that I need to balance the quotes.  Eventually, I did so, and the query terminated.

Look around, I suspect there is a stray quote on the preceding or following line.  Or the quotes around the filename aren't really the same.


Why are so many MPP solutions based on PostgreSQL instead of MySQL? Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there other reasons? To me, it seems like the MyISAM, not being ACID complient and therefore not running into the same issues with MVCC (like seen here) as PostgreSQL is far better suited for building high-performance data warehouses. After all OLAP load does not require transactions as far as I can see.

It is mostly a license issue.  These developments end up patching the code quite heavily, so if you were to deal with MySQL, you'd either have to open-source your code or be at the mercy of MySQL's corporate owner for the life of your business.  Some offers for MySQL get around that by implementing their work as a storage engine, but that doesn't offer all the flexibility that they need, and they invariably end up patching the MySQL core as well.


Dev/Test Environment For Databases With Cross-Database Joins We have existing databases under source control in SQL Server Database Projects with a number of these databases referencing each other.

Getting these into projects and source control was challenging enough though I'm finding it difficult to setup Dev/Test environments without using a new SQL server instance.

At the moment we have a copy of the databases on a test SQL instance which is being used as part of continuous deployment. What I am looking to do is to develop in a branch and work against databases with a Dev prefix on the same instance, though realise that these would be referencing the databases used for continuous deployment.

Would this be possible by using Databases variables in Database References rather than just the name, if so how and is there a way to share SQLCMD variables across projects? Are there any alternative ways to consider?

I would tend to suggest using multiple publish.xml files as described here Jamie Thompson blog

The database names (or a prefix) could then be stored as SQLCMD variables.

Each publish file would then be built and published as a build artifact - you'd need to code a custom powershell or batch job to pick the correct file based on the server name. (You could use the server name as a switch here)

You would need to set up the publish file for each environment once, but once that job is done, it won't need doing again.


What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries? In docs, the join of non-recursive part to recursive is done via CROSS JOIN. Is there some benefit to using it instead of INNER JOIN? Is it just a subjective choice?

CREATE TEMPORARY TABLE folders (id INT, parent INT) ON COMMIT DROP;
INSERT INTO folders
    (id, parent)
    VALUES
    (1, null),
    (2, 1),
    (3, 2);

using CROSS JOIN

WITH RECURSIVE tree (id, parent) AS (
    SELECT id, parent
    FROM folders
    WHERE id = 3
    UNION ALL
    SELECT p.id, p.parent
    FROM folders p, tree
    WHERE tree.parent = p.id
)

using INNER JOIN

WITH RECURSIVE tree (id, parent) AS (
    SELECT id, parent
    FROM folders
    WHERE id = 3
    UNION ALL
    SELECT p.id, p.parent
    FROM folders p
        INNER JOIN tree
        ON tree.parent = p.id
)

Independent of the CTE context, comma-separated items in the FROM list are equivalent to the same with CROSS JOIN replacing the commas. And [INNER] JOIN is exactly the same as CROSS JOIN with a WHERE clause. The manual:

  FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). It is also equivalent to FROM T1, T2.

But there is a subtle difference between comma and explicit JOIN syntax. Comma separates more strictly. See:

What does [FROM x, y] mean in Postgres?


Whats the right table relationship for these entities? I want to store all the data for the following entities:

Users
Projects
Roles

A user can work in multiple projects and can have many roles, but only one role for a user should be allowed in each project, for example:
Jhon can work only as a developer in project #1 and can also work as a designer in project #2
I think the relationship should be:

Where the table User_Project has:

user_id
project_id
role_id

and the user_id,project_id combination is the primary key, and the role_id field is a foreign key to the roles table, where there is a row for each:
role_id  role
1        project manager
2        developer
3        designer
is that the right way to describe the relationship between the entities ??
At first i thought about a many to many relationship for each the users - projects, projects - roles and user - roles, but i dont think it describes the "only one role in each project for a user".

I would simply have:
USER - ROLE - PROJECT
I presume a user cannot be part of a project without having a role? The ROLE table facilitates your many-to-many mapping between users and projects: one user can map to many projects (with each mapping requiring a role record) and vice versa: one project can map to many users.
Simply add a UNIQUE constraint on the (user_id, project_id) columns on the ROLE table to ensure the user exists only once per project in the ROLE table.
If you need to standardise a list of options to describe roles, then your second diagram suits best - which is the same as mine, with an additional table to describe roles.


Does MSSQL Log shipping truncate the transaction log? I have setup log shipping on a database in Microsoft SQL Server 2012.

I was having issues with my secondary server and I found that this was probably due to a scheduled backup job messing up the log file consistency.

The solution was to set the job to do a copy_only backup however, now I am curious as to whether the log shipping will take care of the transaction log size, or will it just grow indefinitely?

Question: Will log shipping take care of truncating the log file?

The LSBackup job in a log shipping configuration performs a transaction log backup, so this will help maintain your log file size the same as a normal log backup job does.

Note that this will not truncate the file (reclaim space), it simply marks the portions of the log that have been backed up as available for re-use and this helps prevent log file growth by reusing existing space in the file. For performance reason, you don't want to be shrinking and regrowing your transaction log file constantly.

Typically if you're using log shipping there is no need to run another copy-only log backup job, you're better off simply copying the log backups that LSBackup creates into a suitable storage location. You will also run into problems if you're running LSBackup and a non-copy only log backup job as the restore chain will be broken on the secondary, as you've found.


Get the Largest Count of Consecutive Value I have this table that records win and lose. Let us say this is my table

create table test(
  col1 int not null
)Engine=InnoDB;

it has 10 rows :

0,1,1,1,0,1,1,0,0,1

1=Win and 0=Lose. I want to get max count of the consecutive win and lose. For this example, the max count for consecutive win is 3 and for lose is 2. How can I get my desired result? I'm not sure where to start with this one. Thanks in advance.

EDIT

create table t1 (
 colid int not null auto_increment,
 col1 int,

 primary key(colid)
);

insert into t1(col1) values (0);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (0);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (0);
insert into t1(col1) values (0);
insert into t1(col1) values (1);

select
a.col1,
max(a.rn)
from (

        select
        x.col1,
        if (@prev = col1 ,@current := @current + 1,@current := 1 ) rn, @prev := col1
      from t1 x , (select @prev := - 1 , @current := 1) v
      order by x.colid
      ) a
group by a.col1

this one works the way I see the results.. Is there anything I should consider? I only have knowledge on basic joins. I haven't fully understand the query as of now..

First of all, you can't trust that table to work.  A table, by definition, is unordered.  SQL is free to return the ones and zeros in any order.

So...  You need to have another column that can be used to sort on -- a date or a game-number, or something.

Then you can ask the question because you can do a SELECT ... ORDER BY ....

The rest is messy.  You should consider, instead, writing the algorithm in your favorite programming language other than SQL.

Add an AUTO_INCREMENT column and index it.
Do a self join on a.id = b.id-1 to find where the transitions are (0 to 1 or 1 to 0)
Extract only the transition ids.
Repeat the auto_in game, but instead of finding transitions, subtract the old ids.  This gives you the run lengths.
SELECT ... ORDER BY .. LIMIT 1 to find the longest run, together with some of the history of where it came from.


Impoving performance of query by using greater than on a primary key I have a Reporting table where i store description

tableA

sno   | Project |name     | description      | mins |
1     | prjA    |nameA    |ABC -10% task done|  30  |
...
3000  | prjA    |nameB    |ABC -70% task done|  70  |

i want to query the description field and save in another table

tableB

id | valueStr | total_mins  | last_sno
1  |  ABC     | 100         | 3000

if there is no entry in second table , i create a entry with default values

if there is and entry in second table , i update 2nd table , with the total_mins and increment the last_sno to that value say 3300 , so that the next time i query this table i get values from second table and based on the last_sno

Query

SELCT last_sno FROM tableB where valueStr ='ABC'

the first 3 characters in the description field

SELECT max(sno), sum(mins) FROM tableA
 where sno > last_sno and description like 'ABC%'

Since the first table has million of rows so, i search the first table with sno > last_sno , so that should help performance right ?

but the explain shows that it scans the same no of rows , when i query the first table from the first sno

Assuming that valueStr has a Unique constraint on the second table, so the following query will always yield at most one row:

SELECT last_sno
FROM tableB
WHERE valueStr ='ABC'

You can use it as a subquery (or a derived table) to run an INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO tableB
  (valueStr, total_mins, last_sno)
SELECT
  'ABC', SUM(a.mins), MAX(a.sno)
FROM tableA AS a
WHERE a.sno > COALESCE(
          ( SELECT last_sno
            FROM tableB
            WHERE valueStr ='ABC'
          ), 0)
  AND a.description LIKE 'ABC%'
ON DUPLICATE KEY UPDATE
  total_mins = total_mins + VALUES(total_mins),
  last_sno = VALUES(last_sno) ;

Regarding efficiency, the condition you have

WHERE a.sno > @last_sno
  AND a.description LIKE 'ABC%'

is basically two range conditions, one on sno and one on description (think of description LIKE 'ABC%' the same as description >= 'ABC' AND description < 'ABD') which is rather hard to optimize. The optimizer will have to choose one index to use, it can't use both indexes for this query.

If you could split the description column or add one more with the first (three or more) characters of description that uniquely identify the value_str, making tableA something like this:

tableA

sno   | Project |name     | description      | value_str | mins |
1     | prjA    |nameA    |ABC -10% task done| ABC       | 30   |
...
3000  | prjA    |nameB    |ABC -70% task done| ABC       | 70   |

then you could write the conditions as:

WHERE a.sno > @last_sno
  AND a.value_str = 'ABC'

and a composite index on (value_str, sno) would make this and similar queries much more efficient than what you have now.