Is EXPDP the best backup approach when running Oracle on Windows with no RMAN? Well, I'm the SQL Server DBA here, and we have a Oracle Senior Dba that manages Oracle Databases and users. I'm curious about how he is handling these servers/users.

What is the best approach, when running oracle, to make backups?

If I'm not mistaken, dumps are not backups. right?

What he does here is:

1)A script create a dynamic query with EXPDP for ALL users and sent it to a TXT file:

SELECT  'EXPDP parfile=export_options.par dumpfile='
        || to_char(sysdate, 'YYYYMMDD_')
        || username
        || '_ServerName.dmp log='
        || to_char(sysdate, 'YYYYMMDD_')
        || username
        || '_ServerName.log schemas='
        || USERNAME
        || ' directory=dumpdir compression=ALL schemas=' || USERNAME
        FROM dba_users
        where created > '01/01/2005' and username not in (Some users)
        order by created desc

2)then it creates a lot of EXPDPs inside a txt file:

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

EXPDP parfile=export_options.par dumpfile=20190221_USER_SERVER.dmp log=20190221_USER_SERVER.log schemas=USERdirectory=dumpdir compression=ALL schemas=USER

3)a WINDOWS TASK SCHEDULER runs this TXT running EXPDP on all users and sending them do a folder.

Is this something that you guys normally do on Oracle? I would like to now if this is something I could use as a best pratice.

Thanks.

When you install an Oracle database RMAN is installed along with it. This has been the case for many years.
As I work at the Enterprise level backup has a fairly restrictive meaning and an RMAN backup is the only backup solution that Oracle supports.  Even third party solutions like Commvault still use RMAN commands to backup the database.
Dump files are very useful for refreshing development but you cannot do a complete restore of a database from a dump file.  You would need to install a new database first. See this excellent answer from Phil here
Ultimately the answer is what is required for backups?

Is there a mean time to recovery?
A disaster recovery plan?
SLA level agreements to clients?

If your shop has to answer these kind of questions then best practice is RMAN backups copied to another server with older backups sent off site and aged out after so many days.


date type implicit conversion When does oracle implicitly converts date type? on what parameters does it depend?

I am running the following:

SELECT SYSDATE - '01-01-01' FROM DUAL

and got :

SELECT SYSDATE - '01-01-01' FROM DUAL
                  *
ERROR at line 1:
ORA-01722: invalid number

why Oracle not converting varchar '01-01-01' to date ?

nls_date_format I have set to 'DD-MM-RR'

An ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number.
Nls_date_format parameter helps to implicitly convert the varchar to a date type.
Example:

SQL>INSERT INTO tbl values ('01-02-16');
SQL>SELECT ADD_MONTHS('01-02-16',2)

In the above example implicit type conversion happens since we have set NLS_DATE_FORMAT . In your case it was trying to extract number(Or you could tell explicitly) from SYSDATE but found string not date.
Example:

SQL>SELECT SYSDATE-'01' FROM dual;

Here this works perfectly and returns the date subtracting 1 day from the SYSDATE because it was able to convert the string to number.
The best way is to explicitly convert the varchar to a date type.
You can use the following query to subtract date from sysdate(assuming you have set NLS_DATE_FORMAT=DD-MM-RR):

SQL>SELECT SYSDATE-TO_DATE ('01-01-01');

Oracle recommends to use explicit type conversion.


What's the best way to pass a variable/valuable between steps in a SQL Server job For example, if I create a dtsx package that fills out an excel sheet, but only email it out if there is actual data?

The only way to pass state between SQL Server Agent T-SQL Job Steps is via a real, i.e. non-#temp, table, perhaps one that exists only in the tempdb database.  Even a global temp table (##temp) won't suffice since even though it would be accessible to multiple sessions, each T-SQL jobstep runs in it's own session that gets created at the start of the step and ends at the end of the step.  The first session to run would create the ##temp table, but when that step ends, the ##temp table would be destroyed prior to the 2nd step starting.
I'd suggest creating a table in tempdb, in the first step of the job, as in:
USE tempdb;
IF OBJECT_ID(N'dbo.JobStepState', N'U') IS NOT NULL
DROP TABLE dbo.JobStepState;
CREATE TABLE dbo.JobStepState
(
    VariableName varchar(25) NOT NULL
        CONSTRAINT JobStepState_pk
        PRIMARY KEY CLUSTERED
    , VariableValue varchar(25) NOT NULL
);

Then, in a subsequent step you can insert a variable into the table with:
INSERT INTO tempdb.dbo.JobStepState (VariableName, VariableValue)
VALUES ('SomeVariableName', 'SomeValue');

And in later job steps, you can refer to the value using:
DECLARE @val varchar(25);
SET @val = (
    SELECT jss.VariableValue
    FROM tempdb.dbo.JobStepState jss
    WHERE jss.VariableName = 'SomeVariableName'
    );
IF @val = 'SomeValue'
    PRINT N'Yes'
ELSE
    PRINT N'No';

DROP TABLE dbo.JobStepState;


What is an index KEY - emphasis on KEY? In SQL Server, What does the 'key' part of the phrase 'index key' mean? Is the key a number? what is it?

If I have the table of data containing a column called LastNames, and I have, say, a clustered index on it, is the 'key' for each row simply the last name? Would an example of a 'key value' be something like 'Cooper'?

I am asking because in non-clustered indexes on clustered tables, the leaf page of the non-clustered index contains the clustered index key. But what does it mean that it contains the index 'key'? Does this mean the actual column data stored on the leaf page of the clustered index (i.e. the data page) for the column that has a clustered index on it?

If we are talking about a currently supported version of Microsoft SQL Server, a non-clustered index does indeed contain a copy of the clustered index key.

This is one reason that there is emphasis on keeping keys as narrow as practical. Obviously a clustered key consisting of an integer would take much less space in the non-clustered index and therefore also require less I/O than if the key was varchar(128).

Note: If you have a clustered index that is "not unique", SQL Server will insert a hidden integer into the key so as to make it unique.  By definition, a clustered index must be unique.  This hidden number would also be included in the non-clustered indexes, of course.

If you are using some other implementation of SQL the details may be different.

EDIT: [Re question in comment} If you have: Clustered Index on column LastName and a non-clustered index on column FirstName, these are two separate indexes.

The clustered index controls the sort order of the table, which contains all the data of the table.  So, the clustered index has the clustered index key on LastName and the rest of the data in the table. The clustered index can provide the answer.
The non-clustered index will have the non-clustered value for FirstName and the Cluster Key (which is LastName) in it.  Depending on optimizer decisions which depend on more details of the query, this index may be used to satisfy the query.


What architecture is best to store a large 3 dimensional array in MySQL? I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy:

  { x, y, z } = { column, row, time }

The z dimension will be time, and I would like to store as much as possible. We are hoping for approximately 1TB of total stored data with modest performance. In other words, we are looking to take a snapshot of one MySQL Table, every 30 seconds, for years. It will be the same table every time, and will be about 10 columns {x} by 1000 rows {y} (approximately 50KB). So if you will, we want to take a snapshot of a 50KB table every 30 seconds.

So this creates the following problem: averting the need to store an endless amount of tables. I have read in various posts on Stack Exchange that it's bad architecture to have millions of tables in a database, and with such a design performance will suffer. So here are the two possible architectures I can think of:

Create a new table and name it using an epoch time, and create millions of new tables endlessly (not good).
Create one database with two columns: epoch_time and json. For every snapshot of the original table, every 30 seconds, convert that into a json string and store the entire table in the json column. So basically, a database with millions of rows containing json serialized tables.

Would number 2 be the best architecture? Is there a better way that I may be missing?

Deltas

The data you are "snapshotting" -- how often does it change?

I suggest looking into storing only the "deltas".  When some piece of the snapshot does not change at all, the delta is empty, and you can store nothing.

For reconstructing a snapshot at some point in the past, the processing is costly -- you need to walk through the versions, applying the deltas as you go.

There are two ways to run the deltas -- forward or backward.  Going 'forward', you would start with the original (complete) snapshot, then apply deltas until the desired time.  Going 'backward' has the advantage that the most recent snapshot is complete.  The going backward 'subtracts' off the changes.

Since you say "for years", it is probably wise to take complete snapshots every, say, day.  Then finding a particular 30-second second snapshot won't involve more than 2880 deltas.  This obviously leads to a speed/space tradeoff -- full snapshots are bulky, but infrequent snapshots leads to long 'reconstruction' times.

Trigger

Rather than "snapshotting", use a TRIGGER to build an "audit trail".  This is similar the "deltas" I mentioned, but it is better in that it is continuous, not "every 30 seconds".  The case I remember had over a billion rows in the audit trail; each row had (approximately) the timestamp, table name, PRIMARY KEY, and a compressed JSON blob of all the columns for that rows.  Your needs may be better served by some variant of that.

Schema

Until I see the actual queries, I will advise against PARTITIONing since it is usually of no performance benefit.

The link about table size limits is missing one number:  64TB is the limit for one non-partitioned InnoDB table.


Big jump in search time for Postgres Index query for results with high selectivity I am doing some performance comparison of databases and lucene for full-text searching.

So I use Postgres to create an Index for the data to search:

CREATE INDEX bodies_index
  ON bodies
  USING gin
  (to_tsvector('english'::regconfig, body));

and to query it I use

SELECT *
FROM bodies
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', '" + searchterm + "')"

The results are good for almost all database sizes but there is one exception.
It takes up to 10x longer for the biggest database of 250 000 records,
but only for results with a high selectivity.

Any ideas how this could happen?

EDIT:
My PG version is 9.2.

Here is the result for EXPLAIN ANALYZE for a fast result:
(17k results in under 100ms)

http://explain.depesz.com/s/i1F

And this one is much slower, despite only a small rise in the number of results
(28k results in over 50 seconds)

http://explain.depesz.com/s/WwpX

As I understand your question you are asking why highly selective index scans might be much slower after a certain number of records are returned or after the table reaches a certain size.  As it turns out your query plans provide most of the information needed.  Understanding of course is the first step in trying to figure out how to solve the problem.  It looks to me like your slow query is hitting a much more heavily used db than the fast query.

As I look at your query plans, I see two immediate pieces of bad news.

The first piece of bad news is in the buffer usage. Your rows returned are less than double, but you hit about six times the number of buffer pages.  This is bad news because it means that PostgreSQL, following the index scan, is scanning through at least six times more information to find the rows to retrieve.  Significantly worse, you shift from the rarely used db of mostly read buffers (fast, but few db-specific services) to shared buffers which require more overhead.

The second piece of bad news is that the recheck condition in the slow query weeds out about three times as many records as are returned.  This tells me you need to vacuum and/or reindex this table.

I would recommend vacuum analyze and reindex first, followed if necessary by slightly lowering the shared buffer settings and see if this improves performance.


How can I create a gapless sequence? Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the transaction is rolled back...but the index incremented and was not rolled back. This results in unused sequence values, e.g. a table with indexes 1, 2, 4, 5, 7 etc. with no deletes.

Question: Is there any way to reclaim the lost sequence values, or prevent the failed function from incrementing the index?

I know that there are alternative ways to mitigate this potential problem, but I am still curious.

This results in unused sequence values

That's totally normal, and by design. It's also not the only way you can get such gaps. See the note on nextval in the manual.

  Is there any way to reclaim the lost sequence values

No.

  or prevent the failed function from incrementing the sequence?

Don't use a SEQUENCE. Instead, if you must have gapless values, use an ID-generating table via UPDATE ... RETURNING. See gapless sequences for more info.

Note that it's not possible to have concurrent inserts without possible gaps. You get one or the other. So think very hard about whether you really need gapless.

For most applications you should just use a SEQUENCE (SERIAL) and design for the fact that gaps are possible and normal.


Should I use additional id values as unique values that only applies in junction tables in MySQL? I have created three tables called person, metadata_image and person_image.

person table contains info about people.
person_id
person_name
person_bio

metadata_image table stores the images.
image_id
image_url
image_type
image_size
date_uploaded
importance (if importance equals to 1, I use that image as cover picture)

person_image has foreign keys from person and image tables.
person_id
metadata_image_id

Additional image :

Should I add a third column like person_image ID as a unique and auto increment value or is my structure okay as it is?

With just the two foreign keys in there I would leave it as it is.

If other tables have a foreign key pointing to person_image then the debate becomes a lot more nuanced and you will start considering table size and query (plan) complexity.


What is the fastest way to insert large numbers of rows? I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition per month). I have around 3.4 billion rows for three months of data.

What is the fastest way to get these rows from staging into the final table ? SSIS Data Flow Task (that uses a view as source and has fast load active) or an Insert INTO SELECT .... command ? I tried the Data Flow Task and can get around 1 billion rows in around 5 hours (8 cores / 192 GB RAM on the server) which feels very slow to me.

One common approach:

Disable / drop indexes / constraints on target table.
INSERT dbo.[Target] WITH (TABLOCKX) SELECT ...
With credit to JNK of course, you can do the above in batches of n rows, which can reduce the strain on the transaction log, and of course means that if some batch fails, you only have to-start from that batch. I blogged about this (while in reference to deletes, the same basic concepts apply) here: http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes
Re-enable / re-create indexes / constraints on target table (and perhaps you can defer some of those, if they are not necessary for all operations, and it is more important to get the base data online quickly).

If your partitions are physical and not just logical, you may gain some time by having different processes populate different partitions simultaneously (of course this means you can't use TABLOCK/TABLOCKX). This assumes that the source is also suitable for multiple processes selecting without overlapping / locking etc., and making that side of the operation even slower (hint: create a clustered index on the source that suits the partitioning scheme on the destination).

You may also consider something a lot more primitive, like BCP OUT / BCP IN.

I don't know that I would jump to SSIS to help with this. There are probably some efficiencies there, but I don't know that the effort justifies the savings.


Iterating through results and performing sum on values I'm using MySQL and not really sure where to begin with this. I've got a query which returns N dates for a given company (N is likely to be 10 but could be more/less). These dates are returned as such (ddmmyyyy):
SELECT id, dates FROM myTable WHERE id='123'

123,01012016

123,13012016

etc...

I'd like to find information from another table that hs similar dates and get values within a boundary. For example, Table2 has

ID, DATE, AGE
123,01012016, 3
123,03012016, 7
123,15012016, 10

I'd like to SUM all Ages between each date brought back by the first command. In this example, the first command boundary would be 01/01/2016 - 13/01/2016 so I'd like to sum all values in the second table that fall between that bounday. This would loop for all date boundaries returned in the first query.

I don't really have a starting point for this type of query in MySQL so any help or pointers in what I should be reading docs for is appreciated

Thanks

I can give you some guidance, at a minimum. I don't use MySQL in detail, but in general:

Step 1: get the date ranges

I assume that for each row returned from mytable that you want the date in that row as a start date, and the date in the following row as the end date. Check MySQL documentation on "windowing functions" (related to aggregate functions). There may be a function that can do this for you (in SQL Server, the function is LEAD. If you can't find that, you can always do a subquery in each row, returning the smallest date (matching the same criteria) bigger than the date in the current row.  This would look something like:

SELECT id, dates as startdate
      ,(SELECT MIN(dates) FROM mytable WHERE id = a.id and dates > a.dates) as enddate
  FROM mytable a
 WHERE id = '123'

(Exact syntax may be different).

Step 2: Get the SUMs

Given that we have the start and end dates for each range, getting the SUM of the age ranges is fairly easy. We join the query we did above against Table2 and use GROUP BY and SUM. I assume that dates that fall on a boundary should not be included in both groups, so we'll toss them into the later group:

SELECT dtr.startdate, dtr.enddate, SUM(ag.age) as sum_Age
  FROM (SELECT id, dates as startdate
              ,(SELECT MIN(dates) FROM mytable WHERE id = a.id and dates > a.dates) as enddate
          FROM mytable a
         WHERE id = '123'
       ) dtr
       LEFT OUTER JOIN Table2 as ag ON (ag.date >= dtr.startdate AND ag.date < dtr.enddate)
 GROUP BY dtr.startdate, dtr.enddate;

(Again, syntax may need adjusting for MySQL). Note that this does not sum up ages for any dates either before the first date, or on or after the last date, as those are not part of valid ranges.

Hope this helps point you in the right direction.


ERROR 1226 (42000): User 'root' has exceeded the 'max_user_connections' resource (current value: -1) I'm using mariadb and have error

ERROR 1226 (42000): User 'root' has exceeded the 'max_user_connections' resource (current value: -1)

I'm set max_connection to 1000 and not set max_user_connections in Global and user table.

How can I fix that.

The quick and dirty fix is FLUSH USER_RESOURCES

  USER_RESOURCES

  Resets all per-hour user resources to zero. This enables clients that have reached their hourly connection, query, or update limits to resume activity immediately. FLUSH USER_RESOURCES does not apply to the limit on maximum simultaneous connections. See Section 6.3.4, “Setting Account Resource Limits”.

Since you did not set any user recsource limits, I would suspect a misalignment of columns in the mysql.user table, especially if you performed mysql_upgrade with fixing the grant tables.

To check this run this query

select ordinal_position from information_schema.columns
where table_schema='mysql' and table_name='user' and column_name='max_user_connections';

I get 40 in MySQL 5.6.

If I upgraded MySQL 5.6 and I don't get 40 from this query, I would just run

mysql_upgrade --upgrade-system-tables

There is a 6-month-old bug on a similar issue : MySQL Forums :: Newbie :: ERROR 1226 (42000) at line 1129: User 'root' has exceeded the 'max_updates' resource (current value: 100)


Postgres Specific table access We have a requirement in our Postgres Database, We want to give specific table permissions to a particular set of user, we also have airflow in our environment which syncs tables, but sometimes any new columns are added to a table so we have to drop the table, due to which the specific table access for the user is gone. Access to a specific table is given through GRANT. Can you guys suggest us a way in which specific table access can be given, and will remain if the table is dropped and recreated?

As soon as a table is dropped, all grants are gone. There is no way to avoid that.
You should put all your grants into a SQL script, put that into your version control system (e.g. Git) and then run it after you applied all your migration scripts.
Schema migration tools like Liquibase, Flyway or Sqitch help you organize this.


Queries per second avg I have Installed MySQL Server 5.6.23 on Centos server and the configuration of the server is

16 GB RAM
8 Core
CPU op-mode(s): 32-bit, 64-bit

When I check the status of the server it is showing me Queries per second avg: 22112.191

Still my server is running fine and there is no issue in read & write operation but I would like to know what is the maximum Queries per second my server can handle and what can I do to reduce it.?

Thanks in advance.

22K QPS is nice; I rarely see that high a number on a production system.  You must be doing rather trivial queries.  (In a quick survey, 7K is the 90th percentile.)

Do you use a lot of Stored Routines?  What is your Questions/Uptime?

It is unlikely that a high query rate will "crash" MySQL.  It can slow things down to the point where it feels "crashed".

How many active connections do you have?  When SHOW PROCESSLIST show more than a few dozen queries running (not "Sleep") at a time, you are threatening to get in this overburdened situation.

There have been benchmarks (carefully designed) that registered more than a million queries per second.  I could devise a situation that could saturate all the system resources, yet achieve only 1 query per minute.  Those extremes indicate that there are a lot of factors going on.


Repair GTID from Restore with sql_log_bin=0 and then drop table I have been experimenting with mysql innodb cluster. One of the errors i've come across I don't know how to remedy or why exactly it causes a permanent death to any slave that wishes to catch up to the master - let me explain.

Create 3 instances of mysql. Use the innodb cluster utility to combine them into a cluster. You can use the AdminAPI to make sure the cluster is good. (use Single Primary mode so only 1 writer)

In the master mysql use mysql.exe to restore a dump of any table that has
sql_log_bin = 0;

You'll notice that this causes that table not to replicate to the other database instances. Now if you drop the table the other slaves die and can never be caught up to the master.

You can even create mysql instance from scratch and point it to the master and it will never catch up. It will say recovering and then it will fail.

How do you repair in the case of this scenario? And, what exactly is going on that a slave even from scratch can't be synced after you do this?

The issue is that you have this blob of data that was no way of being replicated.
When you put it on the master and it set the gtid purged variable it basically says: I have this data, but I can't give it to you.

What you can do here is to use this dump to provision the slave/secondaries servers.
So you can execute this update in the master, but when you add a new member before it joins you must also execute the dump there.
Then they both can say: we have this lump of data we cannot replicate so we are in sync. Then the joiner can fetch whatever it is missing from the primary .


Mysqlimport very slow when importing two smaller files I am trying to import a large file, it was really big and it did not seem to be working so I broke it into pieces. The first piece takes 2 minutes, and the second one takes almost an hour!!! How can that be???

bash-3.2$ wc -l allelePiecesaa
2575063 allelePiecesaa
bash-3.2$ cp allelePiecesaa allele.txt
bash-3.2$ time /usr/bin/mysqlimport -h bluemoon-mgmt1a -u jbond --delete --local --fields_escaped_by=\\ MBSR -L allele.txt
MBSR.allele: Records: 2575064  Deleted: 0  Skipped: 0  Warnings: 6
real    1m50.285s
user    0m0.006s
sys     0m0.103s
bash-3.2$ cp allelePiecesab allele.txt
bash-3.2$ wc -l allele.txt
2545972 allele.txt
bash-3.2$ time /usr/bin/mysqlimport -h bluemoon-mgmt1a -u jbond --local --fields_escaped_by=\\ MBSR -L allele.txt
MBSR.allele: Records: 2545973  Deleted: 0  Skipped: 0  Warnings: 7
real    57m17.279s
user    0m0.030s
sys     0m0.097s

my configuration is:

[client]
user=X
host=Y
password=Z
port=3306

[mysqld]
init_connect='SET autocommit=0'
init_connect='SET foreign_key_checks = 0'
init_connect='SET unique_checks = 0'
log_slow_queries = 0

after I do SHOW CREATE TABLE allele;
I get:

mysql> SHOW CREATE TABLE allele

CREATE TABLE `allele` (
    `allele_id` int(11) NOT NULL auto_increment,
    `variation_id` int(11) unsigned default NULL,
    `subsnp_id` int(11) unsigned default NULL,
    `allele_code_id` int(11) unsigned default NULL,
    `population_id` int(11) unsigned default NULL,
    `frequency` float unsigned default NULL,
    `count` int(11) unsigned default NULL,
    `frequency_submitter_handle` int(10) default NULL,
    PRIMARY KEY  (`allele_id`),
    KEY `variation_idx` (`variation_id`),
    KEY `subsnp_idx` (`subsnp_id`),
    KEY `population_idx` (`population_id`)
) ENGINE=MyISAM
AUTO_INCREMENT=1082088650 DEFAULT CHARSET=latin1;

I am thinking of two things to try. If you get faster execution time, then the slowness would be related to the distribution of the data. If you still get slow execution time, it maybe then related to the server configs.

Try to load allelePiecesab file before. If this is slow, then the data in this file has higher cardinality of key fields.
Try to disable the indexes (as this is MyIsam), and load the files in whichever order, then re-enable the keys. If this is faster, then the slowness is related to the updating time of index files after certain growth:

ALTER TABLE allele DISABLE KEYS;
// Do the load
ALTER TABLE allele ENABLE KEYS;


Basic question about grouping and counts I'm having trouble coming up with a query in MySQL.

Suppose we have a table that logs an action by a user.

User1 is a Doctor and is logged several times.
User2 is a Doctor and is logged once.
User3 is an Accountant and is logged twice.
User4 is a Clerk and hasn't been logged.
User5 is an Accountant and hasn't been logged.

I would like to know what "roles" (i.e., doctor, accountant, clerk) have entries in the log file.

Here is the schema:

CREATE TABLE `test1_users` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `role` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `test1_log` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

And test data:

INSERT INTO `test1_users` VALUES
(1, 'Franklin', 'Doctor'),
(2, 'Sally', 'Doctor'),
(3, 'Linus', 'Accountant'),
(4, 'Patty', 'Clerk'),
(5, 'Marcie', 'Accountant');

INSERT INTO `test1_log` VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 1),
(5, 3),
(6, 3);

If I want to see how many times a user has been logged, it's pretty straight forward.

SELECT u.name, COUNT(*) AS total
FROM test1_log l
LEFT JOIN test1_users u ON u.id = l.user_id
GROUP BY user_id;

The query produces the following:

+----------+-------+
| name     | total |
+----------+-------+
| Franklin |     3 |
| Sally    |     1 |
| Linus    |     2 |
+----------+-------+

But how would I structure the query if I want to see how many different "roles" have been logged?
Not how many times each role is logged, just a list of all the roles that are logged.

Specifically, how many different Doctors are logged? I don't care how many times each has been logged.

Such a query I'm looking for would produce the following output:

+------------+-------+
| role       | total |
+------------+-------+
| Doctor     |     2 |
| Accountant |     1 |
| Clerk      |     0 |
+------------+-------+

Both Doctors were logged, but only one Accountant and no Clerks.

Here's the closest I've come:

SELECT role, COUNT(l.id) AS total FROM test1_users u
LEFT JOIN test1_log l ON u.id = l.user_id
GROUP BY role;

But this only gives me the number of log entries, and I need the number of roles that made it into the log.

Thoughts?

Thanks.

By my reading of your question, you want the numbers of distinct users who have been logged, broken down by each user's role.

Your query returns the number of log entries, broken down by role.

To get distinct users, we'd need to get a COUNT(DISTINCT x) on some column x, which represents users.

If we used a column from test1_users, we'd just get the count of users assigned to each role, without reference to whether or not the user had associated entries in the log.

If we use an INNER JOIN, we'll only count users who do have entries in the log - but we won't see any roles where no user exists in the log (as is true for "Clerk").

However, there's a columns in test1_log that's specific to a user - test1_log.user_id.

So, if we modify the query to be:

SELECT role, COUNT(DISTINCT l.user_id) AS total FROM test1_users u
LEFT JOIN test1_log l ON u.id = l.user_id
GROUP BY role;

we get the results you request (as you can see in this SQLFiddle.


what is the consequence of setting enableQueryTimeouts to FALSE? MySQL Connector-J has a property enableQueryTimeouts which is TRUE by default. However, in the documentation it is mentioned as :

  When enabled, query timeouts set via Statement.setQueryTimeout() use a shared java.util.Timer instance for scheduling. Even if the timeout doesn't expire before the query is processed, there will be memory used by the TimerTask for the given timeout which won't be reclaimed until the time the timeout would have expired if it hadn't been cancelled by the driver. High-load environments might want to consider disabling this functionality

So, I want to disable enableQueryTimeouts to gain high performance. However, if I disable enableQueryTimeouts, then

How a query will timeout?
Will there any consequences ?

Please help

Consequences: if there is a slow query(s) your system will be too slow, hang, or even go out of memory.

if QueryTimeout is disabled  from MySQL or the connector it will stay until its finished (how to enable it from MySQL SET GLOBAL MAX_STATEMENT_TIME=XXX;  -- where XXX=number


How to create properly tree-hierarchy I'm using web2py to create an application

I've got 2 different roles: client and referents. Every client can have multiple referents.
Both client and referent can insert row in a table, called object.

The user table, where client and referent are registered, has a column called "client_id".
Every referent in the column_id has the id of the client to whom he reference to.

The problem is: which value should a client have in client_id?
A possible answer could be NULL or the id of the client itself.

Which one is better? Or there's something better?

Main reason why I'd choose NULL is a bit simpler queries - you don't need to care about self-relationship when you are querying ,for instance, number of referred users by the given. You can also save some space in case you create an index on client_id (it usually makes sense) because some RDMS, for example Oracle, don't include NULLs into index, others, like SQL server, allow filtered indexes (WHERE client_id IS NOT NULL).


When to rebuild and when to reorganize indexes I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query :

SELECT
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count

The problem is that when i rebuilt indexes the avg_fragmentation_in_percent increased instead of decreasing. Any pointers?? If this is the normal behavior then what am i missing here??

Previously the avg_fragmentation_in_percent was 30 and after rebuild it has increased to 66.

As per followed practice you can rebuild index when fragmentation is >30 % and can reorganize when fragmentation is between 10 and 30 %. You should also include page_count value in your query. If page_count value( which is there in DMV sys.dm_db_index_physical_stats) is <1000 you don't need to rebuild or reorganize such indexes even though they show large fragmentation. I have seen sometimes for such indexes when rebuilt index fragmentation increases but it would hardly matter.

In your case page_count is 3 so absolutely no need to rebuild or reorganize index.

I would also like to see your full rebuild script it it quite possible you may have used less value of fill factor to rebuild index that would cause more number of pages after rebuilt and thus increasing fragmentation.

Below is a article written by Bob Dor from Microsoft where he actually pointed out that due to MAXDOP setting when used during index rebuild can cause increase in fragmentation I am  sure it is not applicable to your environment but have a look:
http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx.

Please use below query and then find out indexes which needs rebuild:

select
    ips.index_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0 and page_count >1000
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count


SQL - Design question for almost identical tables I am doing a project for my badminton club.

Intro

I want to create a database for players registration, such as the following:

CREATE TABLE Registration(
    PlayerName_fkey text,
    SchoolName_fkey text,
    Category text,
    TournamentNumber integer,
    TypeOfPlay text,
    Selected boolean
);

Note: It is not displayed here, but each column besides "Selected" is basically a foreign key to another table.

Question

For the registration section, I have entries for single play and doubles play.

The registration table would be almost identical, except the doubles have two players instead of one. I have the following choices:

Create two tables, one for single players, one for double players
Create one table and add an extra column double_partner which is always null for singles players.

Both options seem bad to me and I was wondering if there was another better option out there.

Thanks in advance!

Below is my first pass at a relatively simple system.

Don't know what RDBMS you're using, so your SQL
may vary a bit depending.

CREATE TABLE School
(
  School_id INTEGER PRIMARY KEY,
  School_name TEXT NOT NULL,
  Address_1 TEXT NOT NULL, -- must have at least 1 address field
  Address_fields..
  Tel TEXT NOT NULL -- I'm assuming that each school has a phone number,
                    -- put a UNIQUE CONSTRAINT on this. Different schools
                    -- can't have the same number! **But**, there might
                    -- be a boys and a girls school with the same office?
  Other_fields...
  ..
);

CREATE TABLE Player
(
  Player_id INTEGER PRIMARY KEY,
  Player_name TEXT NOT NULL,
  Player_gender BOOLEAN,  -- You forgot this one!
  Player_DOB DATE,        -- and this :-)
  ..
  Other_fields
  ..
);

CREATE TABLE Team
(
  Team_id INTEGER PRIMARY KEY,
  Team_name TEXT -- put in UNIQUE? Don't allow any teams with same name even
                 -- from different schools.
  Team_Player_id_1 INTEGER NOT NULL,  -- Put UNIQUE CONSTRAINT on both player 1
  Team_Player_id_2 INTEGER NOT NULL,  -- and player 2.
);

CREATE TABLE Tournament
(
  Tournament_id PRIMARY KEY,
  Tournament_name NOT NULL, -- UNIQUE?
  Tournament_type CHAR(1) CHECK Tournament_type IN ('S', 'D'),
  Tournament_start_date DATE,
  Tournament_max_entrants  INTEGER  -- is there a max no of entrants?
);

For the first pass, I've decided to split the registration
tables into singles and doubles - there may be better/more
sophisticated methods of dealing with this.

You could think of having an Entrant table with either a
singles or a doubles entity and maybe (probably as part
of code and not in the RDBMS) ensure that teams (i.e.,
an Entrant with two members) can only enter doubles tournaments?

CREATE TABLE Singles_Registration
(
    Reg_id INTEGER PRIMARY KEY,
    Reg_entrant_id  INTEGER FOREIGN KEY (Player) (Player_id),
    Reg_school_id,
    Reg_Category text,  -- don't know what this is for in your schema?
    Reg_tournament_id FOREIGN KEY (Tournament) (Tournament__id),
    Reg_selected Boolean  -- foreign key to table X
);

CREATE TABLE Doubles_Registration
(
    Dreg_registration_id INTEGER PRIMARY KEY,
    Dreg_entrant_id  INTEGER FOREIGN KEY (Team) (Team_id),
    Dreg_school_id,
    Dreg_category text,  -- don't know what this is for in your schema?
    Dreg_tournament_id FOREIGN KEY (Team) (Team__id),
    Dreg_selected Boolean  -- FK to table X
);

You might get more ideas from http://www.databaseanswers.org, here.

In particular, here and esp. here.

I think you might well need a bit more sophistication - you have
nothing about attainment levels/qualification_prerequisites,
heats or possibly even a tournament_location table - not every
school may be able to host a tournament and may use other
local courts. The list of potential fields is quite large. To
say nothing of results?

You don't mention your RDBMS, if you still have a choice and you're
going for an Open Source solution , I recommend PostgreSQL - it is
infinitely more capable than MySQL.

Neither do you mention your development environment - not that
it's critical, but it might be useful to know. The more information
you provide here, the better.

A Google of "sports tournament schemas" brought up several possibly
interesting sites (here inter alia).

A+


REPLACE INTO SELECT from table Is there a way in DB2 to replace the entire table with just selected rows from the same table ?

Something like REPLACE into tableName select * from tableName where col1='a';
(I can export the selected rows, delete the entire table and load/import again, but I want to avoid these steps and use a single query).

Original table

col1   col2
 a      0 <-- replace all rows and replace with just col1 = 'a'
 a      1 <-- col1='a'
 b      2
 c      3

Desired resultant table

col1  col2
 a      0
 a      1

If you are doing SQL, then you probably would need to do a TRUNCATE/DELETE on the table and then INSERT into.

If you are using LOAD and/or IMPORT, they both have options for clearing the table before getting data into the table.


First due you need to generate a series of dates, I'd suggest to use a calendar table.

CREATE TABLE if not exists calendar (
    mdate date PRIMARY KEY NOT NULL
);

INSERT INTO calendar values
('20170403'),('20170404'),('20170405'),('20170406'),('20170407'),('20170408');

How do they do it

Just to get overlapped activities I've used the query that you provided on your question.

create view overlaped_activities
as
SELECT name, min(start) AS start, end, TIMESTAMPDIFF(MINUTE, MIN(start), end) AS time
FROM (
    SELECT x.name, x.start, min(y.end) AS end
    FROM activities AS x
    JOIN activities AS y
        ON x.name = y.name
       AND x.start <= y.end
       AND NOT EXISTS (
           SELECT 1
           FROM activities AS z
           WHERE y.name = z.name
             AND y.end >= z.start
             AND y.end < z.end
       )
    WHERE NOT EXISTS (
        SELECT 1
        FROM activities AS u
        WHERE x.name = u.name
          AND x.start > u.start
          AND x.start <= u.start
    )
    GROUP BY x.name, x.start
) AS v GROUP BY name, end;

First I calculate minutes from start date till midnight:

if(date(start) = date(end),
  time_to_sec(timediff(end, start)) / 60,
  (1440 - time_to_sec(time(start)) / 60)) mstart

Then, if start <> end, I calculate minutes from midnight till end date:

if(date(start) = date(end), 0, time_to_sec(time(end)) / 60) mend

This returns a table like this:

| start               | end                 |     mdiff |  mstart |    mend |
|---------------------|---------------------|----------:|--------:|--------:|
| 03.04.2017 11:00:00 | 03.04.2017 11:30:00 |   30,0000 | 30,0000 |       0 |
| 03.04.2017 23:45:00 | 04.04.2017 00:15:00 |   30,0000 | 15,0000 | 15,0000 |
| 04.04.2017 10:00:00 | 04.04.2017 11:30:00 |   90,0000 | 90,0000 |       0 |
| 05.04.2017 23:00:00 | 07.04.2017 00:45:00 | 1545,0000 | 60,0000 | 45,0000 |

That's nice, but there is another problem here:

| 05.04.2017 23:00:00 | 07.04.2017 00:45:00 | 1545,0000 | 60,0000 | 45,0000 |

Of course: 1545 <> 60 + 45

We need to generate a series of dates between start and end date, and add 1440 minutes to each day.

We can get it using the calendar table:

   select   name,
            mdate date_activity,
            sum(1440) minutes
   from     calendar
   join     overlaped_activities
   on       calendar.mdate > date(start)
   and      calendar.mdate < date(end)
   where    datediff(end, start) > 1
   group by name, mdate

Ok, we got all ingredients, it's time to cook the recipe:

select name, date_activity, sum(minutes) min_activity
from (
       select name,
              date(start) date_activity,
              if(date(start) = date(end), time_to_sec(timediff(end, start)) / 60, (1440 - time_to_sec(time(start)) / 60)) minutes
       from overlaped_activities

       UNION ALL

       select name,
              date(end) date_activity,
              if(date(start) = date(end), 0, time_to_sec(time(end)) / 60) minutes
       from overlaped_activities

       UNION ALL

       select   name,
                mdate date_activity,
                sum(1440) minutes
       from     calendar
       join     overlaped_activities
       on       calendar.mdate > date(start)
       and      calendar.mdate < date(end)
       where    datediff(end, start) > 1
       group by name, mdate
    ) act
group by name, date_activity;

Final result:

| name |       date_activity | min_activity |
|------|--------------------:|-------------:|
| me   | 03.04.2017 00:00:00 |      45,0000 |
| me   | 04.04.2017 00:00:00 |     105,0000 |
| me   | 05.04.2017 00:00:00 |      60,0000 |
| me   | 06.04.2017 00:00:00 |    1440,0000 |
| me   | 07.04.2017 00:00:00 |      45,0000 |

Almost forget it, the recipe: http://rextester.com/EIJOI20983


Hint on some possible query improvement I am currently learning SQL on my own and I am looking for a review of one of my query.

I have the following problem.

"Given a table of players and a table of played match output the ranking of the tournament: (id, player_name, win, number_of_games)"

To that I have created to table

CREATE TABLE players (
id SERIAL PRIMARY KEY,
name char(50)
);
-- results => id of the player who won
CREATE TABLE game (
id SERIAL PRIMARY KEY,
player1 integer,
player2 integer,
results integer
);

I have come up with a query that do the job. But I find it dificult to understand.

SELECT S2.playersid as id, S2.name, COALESCE(S1.win, 0) as win, COALESCE(S2.games, 0) as games
FROM
  (
   SELECT results as id, COUNT(1) as win
   FROM game
   GROUP BY game.results
  ) AS S1
RIGHT JOIN
  (
   SELECT players.id as playersid, SUM(games) as games, players.name as name
   FROM
   (
       SELECT game.player1 as id, COUNT(game.player1) as games FROM game GROUP BY game.player1
       UNION ALL
       SELECT game.player2 as id, COUNT(game.player2) as games FROM game GROUP BY game.player2
   ) as computeGames
   RIGHT JOIN players
   ON computeGames.id=players.id
   GROUP BY players.id, players.name
  ) AS S2
ON S1.id=S2.playersid
ORDER BY win DESC, games DESC;

Is there any features that I might have missed that can help me simplify this query?

You can test it over there http://rextester.com/EYPW43210

Thanks for the test data, makes it much easier. First reflection is that almost all columns are nullable, I wonder whether that's deliberate because it makes querying more difficult. In the sketch below I'll assume that columns are mandatory. You may also want to add the following constraints to game

CHECK( player1 <> player2 )            -- can't play against self
CHECK( results in (player1, player2) ) -- winner must participate

select p.id, p.name
     , count(case when g1.results = p.id then 1 end) as win
     , count(g1.player1) as games -- any column from g1 will do
from players p
left join game g1
   on p.id = g1.player1
   or p.id = g1.player2
group by p.id, p.name
order by win DESC, games DESC;

If you make a left join between player and game (given the join condition), you will get all players and all games where player is either player1 or player2.

To count all games we count the rows where either player1 or player2 is not null. Note that we can not count the rows (say with count(*)) since we then would count rows where the player does not participate.

To count the wins we count the rows where the winner match the player.


Query performance while using sqlcmd Possible Duplicate:
  Running queries with SQLCMD vs. Running queries with SSMS

I have a number of scripts that query the data from different tables and consequently update them. I have defined a batch file and run them in sqlcmd command.
It seems that when I run these scripts directly in SQL management studio, they run quicker than when they are called from the batch file.

Is there any reason for this behavior?

Thanks in advance.

Below are what I did to speed up running the scripts with SQLCMD as the outcome of the reading the nice article AmmaR referred me to:

For each script, I added OPTION (RECOMPILE), so SQL would not need to have a query plan, it is compiling and running the script every time. It is especially important when we have used variables in the script. The variables are unknown to SQL until run time. For example in the code below, the randomId is calculated based on what RAND() function returns, so SQL Server has no idea what it would be beforehand. Adding OPTION (RECOMPILE) helped to run this script faster, even though I was running it using SQLCMD.

`

SET @i = 1

WHILE @i <= @Address_Row_Count
BEGIN
    SELECT @RandomId = ROUND(((@Upper_Town - @Lower -1) * RAND() + @Lower), 0)

    UPDATE a
    SET
        a.TownOrCity =
                    (
                        SELECT
                            Town
                        FROM
                            @TownTble aa
                        WHERE
                            aa.Id = @RandomId
                    )
    FROM
        Addresses a
    WHERE
        a.Id = @i
        AND a.PersonId IS NOT NULL OPTION (RECOMPILE)

    SET @i = @i + 1
END `

Thanks for your help!


INNER JOIN making COUNT(*) slow I have a very simple query:

SELECT COUNT(*)
FROM messages
INNER JOIN users ON messages.user_id = users.user_id

With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to me). Test carried out on the messages table containing 1,000,000+ rows.

I have I have a primary key set on both tables (message_id and user_id) and a foreign key setup connecting messages.user_id and users.user_id.

The reason for this query is to provide the total number of records for a paging system.

What else can I do to speed up the query?

If the foreign key is not nullable, then there is no reason for the join at all, is there? So why not let SQL Server scan the most efficient index available instead of computing a join that will result in the exact same count?

SELECT COUNT(*) FROM dbo.messages;

That said, you can do this much more efficiently:

SELECT SUM(rows) FROM sys.partitions
  WHERE [object_id] = OBJECT_ID('dbo.messages')
  AND index_id IN (0,1);

This will be slightly inaccurate due to in-flight transactions, but it also won't block (or be blocked by) any activity against the table, so pick your poison.


Why does MySQL increment the auto_increment value when there's a constraint violation? I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns.

I noticed that when I try to add a record that violates the constraint, the auto_increment value is still incremented. This seems weird to me, because it just means the value is getting higher while that isn't necessary.

I'm not asking for a way to work around this, but I'm just curious as to why this would be. Is this something that is useful in some cases?

I'm using Mysql 5.5.38-0ubuntu0.14.04.1.

Because the value is incremented outside the scope of the transaction. This is the way you want it to work, so that separate transactions can happily insert rows or roll back without waiting for each other.

If you care about gaps or want different behavior, stop using the built in and roll your own...


Query when index is ready To improve performance of a query I set up an in-memory table and fill it with data. Of course to increase speed I will use an index on the data stored in the in-memory table.

create table helper (
    id int, index using hash(id)
) engine = memory;

insert into helper (id)
select id from source;

[select that uses IDs stored in helper]

Now the problem I anticipate is that the actual query will be run prematurely - before the index is ready to use. How do I know how long I have to wait?

I think executing CREATE INDEX ... does the job because it will halt the sequence of SQL statements until it is done. But it seems odd to create the index again, when the propper place to set up an index is within the CREATE statement of the table.

(I am using MySQL)

Indexes in MySQL are maintained synchronously.  When an insert/update/delete query (or an alter table to add, remove, or enable indexes) finishes running, the indexes are consistent with the table's data.

There is no such thing in MySQL as an index being in a state that is not "ready to use."


Sql Server 2008 R2 - Failed to send an environment change notification to a log shipping partner node Login failed for user '*****'. Reason: Failed to send an environment
  change notification to a log shipping partner node while revalidating
  the login.

I'm seeing a bunch of these errors on my SQL Server 2008 R2 - SP3 database, on average about every 5 seconds. It seems to be affecting the connection pools as well.

This seems pretty straight forward except that log shipping is NOT enabled on this server for any databases. Log shipping was used for a database which was restored to this server, I'm pretty sure the log shipping wasn't properly broken before the backup/restore. However, the offending login is different from the login used for the restored database, so it doesn't seem that the problem is directly related the restored database.

I've run through all of these procedures: http://msdn.microsoft.com/en-us/library/ms189071.aspx and queried the server to find any traces of log shipping and have found nothing.

I've been trying to resolve this issue for the past week and have hit a dead-end.

Thanks

I guessed at several things in the comments above, but the one that hit home was the connection string having the failover partner attribute.

This could actually be what is telling SQL Server to go validate the login on the partner (and yes, I realize that is for mirroring, but this wouldn't be the first time SQL Server's error messages could be misleading).

It is possible that the login no longer has access to the database on the partner (e.g. their rights have been removed) or that the database is offline or no longer exists. Which database? Could be the login's default database (check both instances), the database specifically hard-coded into the connection string, or possibly a database mentioned in a logon trigger or classifier function.


DBCC CHECKDB Estimation Alternative I am in the process of rolling out DBCC CHECKDB across our instances.

Each database will eventually either have a DBCC CHECKDB or DBCC CHECKDB PHYSICAL_ONLY.

The way I have been gauging whether a FULL CHECKDB should be done, or a PHYSICAL_ONLY is by examining the results from DBCC CHECKDB WITH ESTIMATEONLY.

However I ran this yesterday on SQL Server 2008 (SP1) and the tempdb estimation for a certain database was 510MB.

However in practice it filled the entire 19GB drive so the estimation was way off!

I was under the impression the ESTIMATEONLY bug was in SQL 2008 R2, not the standard 2008 ?

Is there another way to gauge tempdb usage? Some sort of database size to tempdb usage ratio: (I.e if I have a 500GB database how much tempdb consumption should I be expecting?)

I also need to implement test restores because we don't do that here. Once I have that server in place I will switch to PHYSICAL_ONLY for everything then do a full CHECKDB on the restored database.

Thank you

What we do to estimate the usage of tempdb and its size is to track its growth via data tracked in tables for a period of time before reboot happens as a part of maintenance activity.

P.S note: It may not be an alternative, but helps in tracking how tempdb grows over a period of time

Step 1 Create table to store the data:

CREATE TABLE [dbo].[TempDBFileSize] (
[TFSID] [int] IDENTITY (1, 1) NOT NULL ,
[FileID] [int] NULL ,
[File_Logical_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Default_File_SizeMB] [int] NULL ,
[ActualKB] [int] NULL ,
[ActualMB] [int] NULL ,
[File_MaxSize] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File_Growth] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Growth_Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Physical_File_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCaptured] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TempDBFileSize] WITH NOCHECK ADD
CONSTRAINT [PK_TempDBFileSize] PRIMARY KEY CLUSTERED
(
[TFSID]
) ON [PRIMARY]
GO

Step 2 Create SP to catch the tempdb data and log file usage within above created table:

CREATE PROCEDURE dbo.spTempdbFileSize
AS
/*
------------------------------------------------------
-- Object Name: dbo.spTempdbFileSize
-- Project: SQL Server TempDB Sizing
-- Business Process: SQL Server Capacity Planning
-- Purpose: Capture the TempDB growth
-- Detailed Description: Capture the TempDB size, name, file, etc for the TempDB files
-- Database: TempDB
-- Dependent Objects: dbo.TempDBFileSize
-- Called By: Admin - TempDB Sizing
-- Upstream Systems: None
-- Downstream Systems: None
--
------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
------------------------------------------------------
-- 001 | N\A | 12.02.2007 | Edgewood | Original code
-- 002 |     | 03.30.2009 | Penberth | Modified the code to get the actual size
--                                     of the tempdb alongside the the default sizes.
--                                     Added the ActualKB and ActualMB columns and
--                                     renamed the [File_SizeMB] to [Default_File_SizeMB]
--
*/
SET NOCOUNT ON
INSERT INTO dbo.TempDBFileSize (FileID, File_Logical_Name, State_Desc, Type_Desc,
[Default_File_SizeMB], ActualKB, ActualMB, File_MaxSize, File_Growth, Growth_Type,
Physical_File_Name, DateCaptured)
SELECT File_ID,
MasterTbl.[Name],
MasterTbl.State_Desc,
MasterTbl.Type_Desc,
(MasterTbl.[Size] * 8)/1024 AS 'File_SizeMB',
(TempTbl.[size]*8) AS ActualKB,
(TempTbl.[size]*8)/1024 as ActualMB,
File_MaxSize = CASE
WHEN MasterTbl.[Max_Size] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Max_Size] = -1 THEN 'UnlimitedGrowth'
WHEN MasterTbl.[Max_Size] = 268435456 THEN 'TLogMax'
ELSE CAST((MasterTbl.[Max_Size] * 8)/1024 AS varchar(10)) END,
File_Growth = CASE
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 0
THEN CAST((MasterTbl.[Growth]* 8)/1024 AS varchar(10))
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 1
THEN CAST(MasterTbl.[Growth] AS varchar(10))
ELSE 'Unknown' END,
Growth_Type = CASE
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[is_percent_growth] = 0 THEN 'MegaBytes'
WHEN MasterTbl.[is_percent_growth] = 1 THEN 'Percentage'
ELSE 'Unknown' END,
MasterTbl.[Physical_Name],
GETDATE() AS 'DateCaptured'
FROM Master.sys.master_files MasterTbl (NOLOCK)
LEFT OUTER JOIN TEMPDB.SYS.SYSFILES TempTbl (NOLOCK)
 ON MasterTbl.[Physical_Name] = TempTbl.[filename]
WHERE Database_ID = 2
SET NOCOUNT OFF
GO

Step 3: Execute the above SP via SQL agent job: we schedule it to run every 4 hours. Therefore by the end of the week we have enough data to predict how much tempdb is growing and thus estimating accordingly.

Just execute

    SELECT *
FROM [dbo].[TempDBFileSize]
ORDER BY ActualMB

to view the stored results


Unable to create restore plan due to break in LSN chain My backup plan is:

Full backup - once a day
Differential backup - every four hours
Transaction Log backup - every 30 minutes.

All database backup are stored on my computer.

I add some wrong changes to my database and now I need to restore it to point-in-time (SQL Server 2012) with the help of SSMS.

Firstly I restored the last full backup WITH NORECOVERY, it restored successfully, then I tried to restore the last differential backup WITH NORECOVERY and received the following error message:

  Unable to create restore plan due to break in LSN chain.

Please advise. What am I doing wrong?

Recently I have faced with the same issue. I have googled for a while and found that this is a problem in the Microsoft products. I wrote the article according to this error message, so you can find more information there.

Disclaimer: I am the Marketing Manager for Pranas.NET, maker of the Sql Backup and FTP tool promoted in that article.

So, to solve this issue and restore your database to point-in-time use T-SQL commands:

Restore your last full backup

RESTORE DATABASE your_database FROM DISK = 'd:/full' WITH NORECOVERY, REPLACE

Restore your last differential backup

RESTORE DATABASE your_database FROM DISK = 'd:/diff' WITH NORECOVERY

And restore your transaction log backups, when you will restore the last transaction log backup point the time you need to restore your database

RESTORE LOG your_database FROM DISK = 'd:/log1' WITH NORECOVERY

RESTORE LOG your_database FROM DISK = 'd:/log2' WITH STOPAT = '2016-01-05 13:29:59.000', RECOVERY


SQL-Server 2008 R2, update Microsoft.SqlServer.Types.dll Is it possible to update the hosted assembly with a new one? Guessing not. Would like new features found in SqlGeography, like STContains() and not have to write ugly SQL workaround.

//Daniel

I suspect such a change would require a whole lot more than just swapping out a DLL to get the new definition. How would the engine know how to parse never mind process the function? You can't just add FORMAT() or DATETIMEFROMPARTS() to earlier versions by checking in a DLL either.

I think the only way you'll be able to get SqlGeography.STContains() is to upgrade to a version that has it (SQL Server 2012).


How to get row_number() within a group? I have a table event_seating_lookup with 3 columns: (id int, event_id int, lookup_is_successful boolean).

For every event_seating_lookup record, I want to get a number of previous (lag) records with the same lookup_is_successful value, i.e.

Assuming the following setup:

CREATE TABLE event_seating_lookup  (id int, event_id int, lookup_is_successful boolean);

INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (1, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (2, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (3, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (4, 1, FALSE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (5, 1, FALSE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (6, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (7, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (8, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (9, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (10, 1, TRUE);

Using a simple row_number() function gives absolute number within the group:

SELECT
  esl1.id,
  esl1.event_id,
  esl1.lookup_is_successful,
  row_number() OVER (PARTITION BY event_id, lookup_is_successful ORDER BY id ASC) consecutive_lookup_is_successful_without_change
FROM event_seating_lookup esl1
WHERE esl1.event_id = 1
ORDER BY esl1.id DESC;

id  event_id  lookup_is_successful  consecutive_lookup_is_successful_without_change
--  --------  --------------------  -----------------------------------------------
10  1         true                  8
9   1         true                  7
8   1         true                  6
7   1         true                  5
6   1         true                  4
5   1         false                 2
4   1         false                 1
3   1         true                  3
2   1         true                  2
1   1         true                  1

http://www.sqlfiddle.com/#!15/7f550/2

Meanwhile, I want to get a table:

id  event_id  lookup_is_successful  consecutive_lookup_is_successful_without_change
--  --------  --------------------  -----------------------------------------------
10  1         true                  5
9   1         true                  4
8   1         true                  3
7   1         true                  2
6   1         true                  1
5   1         false                 2
4   1         false                 1
3   1         true                  3
2   1         true                  2
1   1         true                  1

The ultimate objective is to get a table that tells me for every event (event_id) in event_seating_lookup, how many failed lookups (lookup_is_successful=FALSE) there are, i.e.

event_id  consecutive_lookup_is_successful_without_change
--------  -----------------------------------------------
5         5

Thanks to @Colin's recommendation to read an article about using lag/sum to create virtual groups, I come up with the following solution:

WITH
  lookup_is_successful_change AS (
    SELECT
      esl1.id,
      esl1.event_id,
      esl1.lookup_is_successful,
      CASE
        WHEN (
          lag(esl1.lookup_is_successful) OVER (PARTITION BY esl1.event_id ORDER BY id ASC) != esl1.lookup_is_successful OR
          lag(esl1.lookup_is_successful) OVER (PARTITION BY esl1.event_id ORDER BY id ASC) IS NULL
        )
        THEN 1
        ELSE 0
      END lookup_is_successful_change
    FROM event_seating_lookup esl1
    ORDER BY esl1.id DESC
  ),
  look_is_successful_change_group AS (
    SELECT
      lisc1.id,
      lisc1.event_id,
      lisc1.lookup_is_successful,
      sum(lisc1.lookup_is_successful_change) OVER (PARTITION BY lisc1.event_id ORDER BY id ASC) group_vid
    FROM lookup_is_successful_change lisc1
    ORDER BY lisc1.id DESC
  )
SELECT DISTINCT ON (liscg1.event_id)
  liscg1.event_id,
  liscg1.lookup_is_successful,
  row_number() OVER (PARTITION BY liscg1.event_id, liscg1.group_vid ORDER BY id ASC) consecutive_lookup_is_successful_without_change
FROM look_is_successful_change_group liscg1
ORDER BY liscg1.event_id, liscg1.id DESC;

http://www.sqlfiddle.com/#!15/7f550/5

This query is not very performant, though.


Conditionally insert a value in an array when creating a new column I have some data in a jsonb col that I need to clean and insert into another text array column.
What I have:
[
  {
    "v": "birthday"
  },
  {
    "v": "reference"
  }
]

What I want (_text):
{birthday, reference}

My query:
select array[col -> 0 ->> 'v', col-> 1 ->> 'v'] from src

gives the expected result as above.
However, when the value is null, I want the value not to be inserted i.e. no NULL value nor empty:
[
  {
    "v": "birthday"
  }
]

should result in
{birthday}

and not
{birthday, [NULL]} -- NULL value
{birthday,} -- Empty string with coalesce

How do you conditionally insert an element into an array when creating one, based on the value of the element?

array_remove() seems to answer my use case (by removing in a 2nd step the null values):
select array_remove(array[col -> 0 ->> 'v', col-> 1 ->> 'v'], null) from src


Command for setting Inline Length has no effect I dropped a CLOB(2G) NOT LOGGED column in a table and recreated it with the same size and the LOGGED attribute.
If I look at the syscat.columns I see that the INLINE_LENGTH is 0. The old column had this value set to 312 which is the minimum value accepted.

However if I try to set the inline length of the column with the following command the value doesn't change

ALTER TABLE MYSCHEMA.MYTABLE ALTER MYCOLUMN SET INLINE LENGTH 312

The command complete successfully but apparently there is no change in the inline length.

The following query returns 1 record with a value of 0:

SELECT INLINE_LENGTH FROM syscat.columns WHERE TABNAME='MYTABLE' AND COLNAME='MYCOLUMN'

I would expect a record with a value of 312. Furthermore if I look at the DDL of the table there is no INLINE LENGTH specification.

I also tried to specify the inline length in the ADD COLUMN statement but the result is the same.

How can it be possible ?

The reason for this behavior is that DB2 must have enough space for the largest possible LOB locator in the column for the row in the table.

INLINE LENGTH is not like a VARCHAR where space is used only according to the amount of data.  It's like CHAR – the size you specify is reserved in every single row of the table.

From the documentation (scroll down to description of INLINE LENGTH integer):

  For a column of data type LOB, the default inline length is set to be the maximum size of the LOB descriptor if the clause is not specified. Any explicit INLINE LENGTH must be at least the maximum LOB descriptor size.

There's a table that shows the minimum explicit inline length for various LOB lengths, and for CLOB(2G), the maximum LOB Locator size is 312 bytes.

Therefore, for your table, the inline length must be at least 312 bytes to ensure that the largest possible LOB Locator can be stored.


Why all users excep sys and system can connect to oracle db after database deletion on multi database environment? I have 2 database on one server.
I can connect to the database with normal users but cannot connect with sys and system users and invalid username/password occurs. before that I dropped one of the databases and during dropping that database, this error occurred:

  DIM-00014: cannot open the windows NT service control
  manager.O/S-Error:(OS 5) Access is denied

I check if I can connect sqlplus /nolog and conn / as sysdba and it did not worked and below error occurred:

  12560:  tns protocol adapter error

anybody help!

I finally found a solution. I changed sys password using orapwd. There is just one issue. As documentation says, the password for users which do not have sysdba or sysoper privileges will be expired after some time. So, to avoid this login as sysdba and grant sysdba and sysoper to the sys.


How to skip to next iteration if no results are returned in SELECT statement in Stored Procedure I have a case where I SELECT rows from one table and INSERT them to another table. However, I get Error: 1329 SQLSTATE: 02000 if the SELECT statement doesn't return any row. Can I do something like:

CREATE PROCEDURE SP()
   DECLARE CONTINUE HANDLER FOR NOT FOUND;
   BEGIN
      ITERATE
   END
   BEGIN
      DECLARE cur1 CURSOR FOR SELECT * FROM table1;
      OPEN cur1;
      read_loop: LOOP
         FETCH cur1 INTO var1;
         INSERT INTO table2 (SELECT c1 FROM table3)
      END LOOP;
      CLOSE cur1;
   END;

Is this correct? Thanks.

EDIT:

Based on answers below, what worked for me was:

BEGIN
    DECLARE done BIT DEFAULT FALSE;
    DECLARE name VARCHAR(255);
    DECLARE lat FLOAT;
    DECLARE lon FLOAT;
    DECLARE radius FLOAT;
    DECLARE cur1 CURSOR FOR SELECT * FROM anchorage_test;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;
    read_loop: LOOP
       SET done = FALSE;
       FETCH cur1 INTO name, lat, lon, radius;
       IF done THEN
          LEAVE read_loop;
       END IF;
       INSERT INTO sp_result
       SELECT imo, draught, ST_Distance_Sphere(point(lon, lat),point(longitude,latitude))*0.000539957 AS distance, dt_pos_utc, name FROM historical_temp HAVING distance < radius;
    END LOOP;
    CLOSE cur1;
END

You shoud use: LEAVE loop_name

IF NOT EXIST (SELECT 1 FROM table3 WHERE <some condition>)
    LEAVE read_loop;


Database design scenario for budget (whole and daily) Scenario: Each user add funds to his account, for example 100 USD, and he defines that the system (our website) could spend up-to 10 USD per day for showing his ads in the website. each time his ad is shown in or website, it's gonna cost him 1 USD.

P.S: we're not inserting a record into the database every time his ad is shown, we just deduct the amount for showing his ad (1 USD) from his whole budget.

because we're showing too many ads, it's not possible to insert a record for each ad show into the database.

I have 2 columns in users table, whole_budget and daily_budget, so for the above user whole_budget is 100 and daily_budget is 10.

in the above scenario, how can we manage daily budget? so when an ad is shown, how we're gonna deduct 1 USD from his account?

how can we know if he has left daily budget? so we can decide to show his ad for the rest of the day or not.

I would appreciate your help

EDIT ---------------

This could be simply achieved by having a table called shown_ads, and every time we show an ad, we insert a record in this table with the respected DATE() so we can later on calculate the amount of his budget already spent today. but imagine we're showing 1M ads each day, so what I believe is that it's not good to insert 1M records every day! It's just a case-study, I want to know how would you implement this.

The easiest solution (probably) would be:

Have one table for the whole budget for each campaign (let users have more than one ad at a time :-):

CREATE TABLE budget
(
    campaign_id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    /* Assumption: your currency doesn't have decimals; or you're using cents
       as currency unit */
    original_budget    INTEGER NOT NULL,
    available_budget   INTEGER NOT NULL,
    daily_budget_limit INTEGER NOT NULL,

    -- Security checks
    CHECK(original_budget > 0),
    CHECK(original_budget >= available_budget),
    CHECK(daily_budget_limit > 0),
    CHECK(daily_budget_limit <= original_budget)
) ;

Have a second table for the budget of just today

CREATE TABLE todays_budget
(
    campaign_id INTEGER NOT NULL REFERENCES budget(campaign_id) PRIMARY KEY,
    original_daily_budget INTEGER NOT NULL,
    available_daily_budget INTEGER NOT NULL,

    CHECK(available_daily_budget >= 0)
) ;

Every day, at 00:00, perform the following operations:

Subtract the spent_budget (original_daily_budget - available_daily_budget) from the budget
DELETE everything from todays_budget
INSERT new rows to todays_budget, with both original_daily and available_daily budget equal to the min of (daily_budget_limit, available_budget)

This can be done with the following queries:

Subtract remaining from yesterday

UPDATE
    budget
SET
    available_budget = available_budget -
        (SELECT
            original_daily_budget - available_daily_budget
        FROM
            todays_budget tod
        WHERE
            tod.campaign_id = budget.campaign_id)
 WHERE
      campaign_id IN
          (SELECT
              campaign_id
          FROM
              todays_budget
          WHERE
               available_daily_budget > 0
          ) ;

Clear out everything from todays_budget

DELETE
FROM todays_budget ;

And fill it up again

INSERT INTO todays_budget
   (campaign_id, original_daily_budget, available_daily_budget)
SELECT
    campaign_id,
    CASE WHEN available_budget > daily_budget_limit then
        daily_budget_limit
    ELSE
        available_budget
    END AS original_daily_budget,
    CASE WHEN available_budget > daily_budget_limit then
        daily_budget_limit
    ELSE
        available_budget
    END AS available_daily_budget
FROM
    budget
WHERE
    available_budget > 0 ;

And very time you show an ad: Subtract one from available_daily_budget

UPDATE
    todays_budget
SET
    available_daily_budget = available_daily_budget - 1
WHERE
    campaign_id = ((the corresponding campaign_id)) ;

Notes

I'm using SQL to describe your scenario. It is possible to do the same with noSQL databses. I wouldn't. This works well under a relational model.
If using SQL, steps 1 through 3 should be wrapped in a transaction, under SET ISOLATION LEVEL SERIALIZABLE; to avoid concurrency issues. If any of the check constraints fails, the whole transaction will be aborted and you'll have to retry it as a whole.
I've tried to use SQL Standard as much as possible, specific databases could allow for (slightly) simpler queries.
I am ignoring very many details that should be taking into consideration in a realistic situation.


Improving `GROUP BY` query performance in sqlite3 I have a little web-application that is using sqlite3 as it's DB (the db is fairly small).

Right now, I am generating some content to display using the following query:

SELECT dbId,
        dlState,
        retreivalTime,
        seriesName,
        <snip irrelevant columns>
        FROM DataItems
        GROUP BY seriesName
        ORDER BY retreivalTime DESC
        LIMIT ?
        OFFSET ?;

Where limit is typically ~200, and offset is 0 (they drive a pagination mechanism).

Anyways, right now, this one query is completely killing my performance. It takes approximately 800 milliseconds to execute on a table with ~67K rows.

I have indexes on both seriesName and retreivalTime.

sqlite> SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;
<snip irrelevant indexes>
DataItems_seriesName_index
DataItems_time_index           // This is the index on retreivalTime. Yeah, it's poorly named

However, EXPLAIN QUERY PLAN seems to indicate they're not being used:

sqlite> EXPLAIN QUERY PLAN SELECT dbId,
                                  dlState,
                                  retreivalTime,
                                  seriesName
                                  FROM
                                      DataItems
                                  GROUP BY
                                      seriesName
                                  ORDER BY
                                      retreivalTime
                                  DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|USE TEMP B-TREE FOR ORDER BY

The index on seriesName is COLLATE NOCASE, if that's relevant.

If I drop the GROUP BY, it behaves as expected:

sqlite> EXPLAIN QUERY PLAN SELECT dbId, dlState, retreivalTime, seriesName FROM DataItems ORDER BY retreivalTime DESC LIMIT 200 OFFSET 0;
0|0|0|SCAN TABLE DataItems USING INDEX DataItems_time_index

Basically, my naive assumption would be that the best way to perform this query would be to walk backwards from latest value in retreivalTime, and every time a new value for seriesName is seen, append it to a temporary list, and finally return that value.  That would have somewhat poor performance for cases where OFFSET is large, but that happens very rarely in this application.

How can I optimize this query? I can provide the raw query operations if needed.

Insert performance is not critical here, so if I need to create an additional index or two, that's fine.

My current thoughts are a commit-hook that updates a separate table that is used to track only unique items, but that seems like overkill.

An index can be used to optimize the GROUP BY, but if the ORDER BY uses different columns, the sorting cannot use an index (because an index would help only if the database would be able to read the rows from the table in the sort order).

A COLLATE NOCASE index does not help if you use a different collation in the query.
Add a 'normal' index, or use GROUP BY seriesName COLLATE NOCASE, if that is allowed.

Using the OFFSET clause for pagination is not very efficient, because the database still has to group and sort all rows before it can begin stepping over them.
Better use a scrolling cursor.

Note: there is no guarantee that the dbId and dlState values come from any specific row; SQLite allows non-aggregated columns in an aggregated query only for bug compatibility with MySQL.


Why can't object names start with a number? For example, if I'm creating view with a name '4aii', why does SQL Server care that it starts with a 4? I could call the table Fouraii or IVaii.

Additionally, what does [] do behind the scenes to allow for any string to be used as a name?

A string's a string, amirite?

Firstly, you need to distinguish between numbers (numeric literals), strings (string literals) and identifiers. '4aii' is a string literal, which can be a value of some "thing", but it does not identify (name) a thing. 4aii or [4aii] would be identifiers (if it were allowed).

The query parser needs to understand the meaning of a token that it is looking at. By allowing names to begin with digits, you by extension allow them to consist of digits exclusively. Then, given select 12345 from mytable, how would you (and the parser) know if 12345 is an integer literal or a name of a column?

However, if you allow identifiers to begin with letters only (or underscore characters), you can unambiguously say if you're looking at an identifier (abc123) or a string literal ('abc123') -- the latter is enclosed in quotation marks.

Square brackets in SQL Server, backticks (`) in MySQL, and double quotes in ANSI SQL-compliant engines, signify identifiers, and you use them for when your identifiers cannot be readily distinguished from other tokens: start with a digit, have spaces or other special characters in them etc. Hence, [4aii] or "4aii" clearly tell the parser that it's dealing with an identifier.

A little dbfiddle demo.


Change MAMP to use a lower Mysql 5.1 version I need to have the same development environment for my PHP program. My clients test server is running on Mysql 5.1 while my local is using MAMP running Mysql 5.5.

How do downgrade my mysql database to used  mysql 5.1

Thanks.

Use a binary distribution (or compile from source) and then install using your system specific my.cnf (see below for mine).

pol@Pol-laptop:~/Downloads/mysql/src/inst5.6$ ./scripts/mysql_install_db --defaults-file=/home/pol/Downloads/mysql/src/my.cnf

Then, when you kick off your 5.1 instance, specify the my.cnf in the --defaults-file option to the mysqld daemon. I've never done this with 5.1 (why are you using 5.1?) but if you have problems, it could be due to the fact that older versions sometimes  appear to require that the --socket, --pid-file and --port options be specified separately. Experiment and post a new question if you're stuck.

pol@Pol-laptop:~/Downloads/mysql/src/inst5.6$ more my.cnf
[mysqld]
#
# * Basic Settings
#
user        = pol
pid-file    = /home/pol/Downloads/mysql/src/inst5.6/mysqld.pid
socket      = /home/pol/Downloads/mysql/src/inst5.6/mysql.sock
port        = 3306
basedir     = /home/pol/Downloads/mysql/src/inst5.6
datadir     = /home/pol/Downloads/mysql/src/inst5.6/data
tmpdir      = /tmp
pid-file    = /home/pol/Downloads/mysql/src/inst5.6/mysql.pid
lc-messages-dir = /home/pol/Downloads/mysql/src/inst5.6/share
lc-messages = en_US

general_log     = on
general_log_file= /home/pol/Downloads/mysql/src/inst5.6/logfile.txt

log_error   = /home/pol/Downloads/mysql/src/inst5.6/error.log
log-error   = /home/pol/Downloads/mysql/src/inst5.6/error.log

slow_query_log_file = /home/pol/Downloads/mysql/src/inst5.6/slow_query.log
slow_query_log  = 0

explicit-defaults-for-timestamp = TRUE

#language   = /home/pol/Downloads/mysql/src/inst5.6/share/english

# bind-address  = 0.0.0.0 - may not need c.f. host...

[client]
port        = 3306
socket      = /home/pol/Downloads/mysql/src/inst5.6/mysqld.sock
host            = 127.0.0.1
# or maybe localhost... cf. bind-address above...

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
#loose-local-infile =   1

local-infile    = 1

[mysqld_safe]
#user       = linehanp
#socket     = /home/pol/Downloads/mysql/src/inst5.6/mysql.sock
#err-log        = /home/pol/Downloads/mysql/src/inst5.6/error.log
#pid-file   = /home/pol/Downloads/mysql/src/inst5.6/mysql.pid
#log_error  = /home/pol/Downloads/mysql/src/inst5.6/error.log
#log-error  = /home/pol/Downloads/mysql/src/inst5.6/error.log

pol@Pol-laptop:~/Downloads/mysql/src/inst5.6$


Transforming start and end times to simple graph I've been struggeling with this problem for a while now.

I have a table sessions which stores the start and end times of sessions.

What I am trying to archive is a simple graph that shows the number of active sessions for the last 24 hours in a reasonable interval.

My current solution is to just query the number of active sessions every 15 minutes and store them in a seperate table with just a timestamp and a number column. This is basically duplicate data and not very exact.

The following query produces the needed result, but is very inefficient:

CREATE TEMPORARY TABLE timings (tstamp timestamp);
INSERT INTO timings (tstamp) VALUES (CURRENT_TIMESTAMP - 0 MINUTE), (CURRENT_TIMESTAMP - 15 MINUTE), [...];
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE tstamp BETWEEN starttime AND endtime) AS 'numSessions' FROM timings;

I am using MariaDB 10.1. Version 10.2 adds CTEs and updating is a possibility.

I've upgraded and worked a bit with CTEs. The following query runs in about half of the time.

WITH RECURSIVE tstamp_cte AS (
    SELECT 0 AS n, CURRENT_TIMESTAMP AS tstamp FROM DUAL
    UNION ALL
    SELECT n + 1, tstamp - INTERVAL 15 MINUTE FROM tstamp_cte WHERE n < 96
)
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE starttime < tstamp AND endtime >= tstamp OR endtime IS NULL) AS 'sessionCount'
FROM tstamp_cte


Concatenate column values I have a query that uses a couple joins to get a name from another table.

It works okay except that I would like to combine "Examiners Name" instead of having multiple duplicates:

ID    |  Name       | Examiners Name
1290  | Jun         | Aron
1290  | Jun         | Mark
1292  | Takahiro    | Didier
1292  | Takahiro    | Eiro
1292  | Takahiro    | Kurt

What I am looking for:

ID    |  Name       | Examiners Name
1290  | Jun         | Aron, Mark
1292  | Takahiro    | Didier, Eiro, Kurt

Here is my query:

SELECT DISTINCT
usr.CandidateID,
usr.FirstName,
Examiner.FirstName AS [Examiners Name]
FROM
dbo.tbl_User AS usr
LEFT JOIN dbo.Tbl_ExaminerCandidates AS ExamCa ON usr.UserID = ExamCa.CandidateID
LEFT JOIN dbo.tbl_User AS Examiner ON ExamCa.ExaminerID = Examiner.UserID
WHERE
usr.CandidateID IS NOT NULL AND
usr.IsActive = 1 AND
usr.UserStatus = 1

I have been looking around but I cannot find a way to do this. I am using SQL Server 2008.

You can use FOR XML PATH() clause to generate the desired output.

I'm starting from your result set and generate the output.

DECLARE @Table1 TABLE
    ([ID] int, [Name] varchar(8), [Examiners Name] varchar(6))
;

INSERT INTO @Table1
    ([ID], [Name], [Examiners Name])
VALUES
    (1290, 'Jun', 'Aron'),
    (1290, 'Jun', 'Mark'),
    (1292, 'Takahiro', 'Didier'),
    (1292, 'Takahiro', 'Eiro'),
    (1292, 'Takahiro', 'Kurt')
;

SELECT
    C.[ID],
    C.[Name],
    STUFF(CA.[Ex_Name],1,1,'') AS [Examiners Name]
FROM
    (SELECT DISTINCT
        [ID],
        [Name]
     FROM
        @Table1) AS C
     CROSS APPLY
     (
        SELECT
            ','+E.[Examiners Name]
        FROM
            @Table1 AS E
        WHERE
            C.[ID] = E.[ID]
        ORDER BY E.[Examiners Name]
        FOR XML PATH('')
    )CA(Ex_Name)

The output for this :

ID          Name     Examiners Name
1290        Jun      Aron,Mark
1292        Takahiro Didier,Eiro,Kurt

If you provide the DDL for your table , we can post a  query base on these tables.

And base on your raw tables:(something that is not tested)

SELECT
usr.CandidateID,
usr.FirstName,
STUFF(Examiner.FirstName,1,1,'') AS [Examiners Name]
FROM
(
    SELECT DISTINCT
        usr.CandidateID,
        usr.FirstName,
        ExamCa.ExaminerID
    FROM
        dbo.tbl_User AS usr
        LEFT JOIN dbo.Tbl_ExaminerCandidates AS ExamCa
            ON usr.UserID = ExamCa.CandidateID
    WHERE
        usr.CandidateID IS NOT NULL AND
        usr.IsActive = 1 AND
        usr.UserStatus = 1
)AS USR
OUTER APPLY
(
    SELECT
        ','+ E.FirstName
    FROM
        dbo.tbl_User AS E
    WHERE
        E.UserID = USR.ExaminerID
    FOR XML PATH('')
)Examiner(FirstName)


Time Boxing SSIS Task I've an SSIS task which download a couple of files,update various Database tables and archive the files(i have it split in various packages).
The average time to completion is about 2.30 min.

However, i've recently noticed  on few occassions that the SSIS Task does remain in the  runnning state and unable to complete.

What i would like is to receive an email when the average time to completion is passed  and after fail the package .

My off the cuff thought would be that you need to turn on logging in your SSIS package and I'm assuming you have it pointed at SQL Server. I prefer to capture OnPre/PostExecute, TaskFailed and OnError events at a minimum.

Write a query that looks for packages that have begun execution but have not terminated. This will go against dbo.sysdtslog90 for 2005, dbo.sysssislog for 2008+ instances.

Depending on how you like to solve your problems, you can use that query in pure TSQL to detect and send mail via sp_send_dbmail (assuming you have it configured) or create an SSIS package that contains the logic and uses a Send Mail Task.

Now you have a tool that when it runs detects the overlong situation and sends notification.

The final step is how do you make it go. The cheap and lazy route would be to schedule a job every N intervals that fires off the solution above and checks. If you wanted more elegance, you could add a step in your existing SQL Agent job that schedules a one off execution of your job check logic, perhaps even a self-deleting job scheduled for a single execution, that runs 2:30 from now. If the package completes, then it will do nothing and delete. Otherwise, you get your page and have to jiggle the database. Harder to do it this way and uglier to test but there's a certain elegance to it.


How to connect from local computer (mac) to remote Oracle DB and execute script and save results to CSV? I can connect to remote Oracle DB using this signature:
 sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'

Now want to execute a script and save results to local CSV file - how to do that?
I already have a script that is doing a work but need a terminal (bash) command to connect (remotely) to a DB and then call a script (all in 1 command).
Guessing something like this (but did not work):
 sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))' @sql_script

Thanks.

SQL*Plus starting with version 12.2 supports printing the output in CSV format.

You can add the below in your script:

set markup csv on

Then execute your script called myscript.sql as:

sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))' @myscript.sql

For example:

[oracle@o71 ~]$ cat myscript.sql
set markup csv on
set feedback off echo off termout off
spool my.csv
select username, account_status, created from dba_users where username like 'SYS%';
spool off
exit

[oracle@o71 ~]$ sqlplus -S 'bp/bp@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o71.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SIMPLE)))' @myscript.sql
[oracle@o71 ~]$ cat my.csv

"USERNAME","ACCOUNT_STATUS","CREATED"
"SYS","OPEN","10-JAN-18"
"SYSTEM","OPEN","10-JAN-18"
"SYS$UMF","EXPIRED & LOCKED","10-JAN-18"
"SYSBACKUP","EXPIRED & LOCKED","10-JAN-18"
"SYSRAC","EXPIRED & LOCKED","10-JAN-18"
"SYSKM","EXPIRED & LOCKED","10-JAN-18"
"SYSDG","EXPIRED & LOCKED","10-JAN-18"


Do I really need Re-Build Index Task in SQL Server all the Time? I have created Re-build Index task which will run every Sunday night @11:00 PM.

I do not have Re-Organize Maintenance Task created

Do I really need Re-build Index Task or I need it only when the Fragmentation Level is above 30% ?
Do I need Re-Organize Index Task when I have Re-build Index Task ?

My Major concern is, we have the Databases in Full Backup Recovery Model and the T-Log Auto-Growth setting is in '%' .

This is eating up all my T-log drive space and creating mess for SQL Server Functionality .

Please advice ?

Do I really need Re-build Index Task

The answer to that is very much "it depends". If the data in your tables doesn't change much over time the definitely no, at least not often.

  or I need it only when the Fragmentation Level is above 30% ?

How much fragmentation you allow before a rebuild is an art rather than a science and may depend on your data patterns and performance metrics+targets, but there is definitely no need to rebuild a large index if there is only a very small amount of fragmentation so definitely check each index before rebuilding to see if it is worth the IO load.

  Do I need Re-Organize Index Task when I have Re-build Index Task ?

There is no point reorganising around the same time (either before or after) rebuilding. Reorganising is a less intensive operation then a full rebuild while having some but not all of the benefits. If you rebuild after reorganising then the reorg was wasted as the whole structure is thrown away, if you reorganise after then there is nothing for the process to really do so anything it tries to do is wasted effort.

  My Major concern is, we have the Databases in Full Backup Recovery Model and the T-Log Auto-Growth setting is in '%' .

I would generally recommend against % increments as they can produce large unneeded growth at times. Set the growth to a fixed size, reasonably large so it doesn't need to happen too often but not overly large. The values of "reasonably" and "overly" will change depending on your database.

  This is eating up all my T-log drive space

If altering your plans to rebuilt/reorg/nothing less aggressively (i.e. not rebuilding everything every time irrespective of current fragmentation) then perhaps you could spread the load over the week by working on a subset of your tables each day. This will make little difference if you DB has one huge structure and the rest of the data is in very small tables (an analytics setup with one huge results table and a series of small dimension tables perhaps) but could be beneficial otherwise. Try altering the current plan to be less aggressive first though, this will be simpler to implement and maintain, and there are many example scripts out there that you can take and tweak for your database's needs.


JOIN + Subselect over M:N table I have four tables Product, Packaging, LabelsForPackaging and Label

I need to query all Products with their Packaging and Labels (should result in multiple rows)

Product has Packaging1Id and LabelsForPackaging has the PackagingId and the LabelId.

I tried this

select * from Product
left join Packaging on Product.Packaging1Id = Packaging.Id
left join
(
    select * from LabelsForPackaging
    inner join Label on LabelsForPackaging.LabelId = Label.Id
    where LabelsForPackaging.PackagingId = -- what to use here?
) as P
on Product.Packaging1Id = P.Id

If I try to use where LabelsForPackaging.PackagingId = Packaging.Id in the subselect, I get this error:

The multi-part identifier "Packaging.Id" could not be bound.

Why don't you just omit the WHERE... clause altogether? And then just JOIN all relevant tables as in:

SELECT * FROM Product
    LEFT JOIN Packaging
        ON Product.PackagingID = Packaging.ID
    LEFT JOIN LabelsForPackaging
        ON LabelsForPackaging.PackagingID = Packaging.ID
    INNER JOIN Label
        ON LabelsForPackaging.LabelID = Label.ID


Azure: Many Failed Login Attempts Logged I run a SQL Server on an Azure Virtual Machine. I have noticed in the Event Logs of the server lots of failed SQL Login attempts using a ton of different usernames (none of which exist):

  Login failed for user 'kisadminnew1'. Reason: Could not find a login
  matching the name provided. [CLIENT: 124.117.233.94]

  Login failed for user 'djapple'. Reason: Could not find a login
  matching the name provided. [CLIENT: 124.117.233.94]

  Login failed for user 'China'. Reason: Could not find a login matching
  the name provided. [CLIENT: 124.117.233.94]

  Login failed for user 'vice'. Reason: Could not find a login matching
  the name provided. [CLIENT: 124.117.233.94]

In all there's probably a few hundred random attempts all at the same time overnight.

Should I be concerned about this? Does it look like somebody is trying to get access to my SQL Server through brute force and is this common?

Is the TCP port SQL Server is listening on open globally?

If so, yes I'd be concerned. If there is a password that can be brute-forced or guessed, or an exploit that allows someone to bypass authentication, eventually your database could be compromised. You'll also be vulnerable to attacks that don't require access, such as someone filling up the drive that that has SQL Server error logs on it by creating many failed logins every second.

If the TCP port is open to the internet, the unwanted login attempts are normal. Based on my experience working at ISPs and hosting companies, a machine that's exposed to the internet will get probed on well-known ports all day (and port scanned periodically) if traffic isn't being dropped by a firewall.

On the other hand, if you have restrictive firewall rules that explicitly allow 124.117.233.94 through, it's likely that one of your customers or colleagues at that address has had a computer compromised.

You can currently read about controlling traffic to Azure Virtual Machines here. A good practice is to drop everything by default and explicitly allow only the traffic you want. Perhaps someone else will edit this to add further information.