Poor performance of JOIN, WHERE, ORDER BY combination (MySQL) Goal: select 10 most recent rows from posts which are related to a specific user.

There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows).

posts columns:

--------------------------------------------------------------------------------------------
|  id (int)  |  source_id (int)  |  title (varchar)  |  content (longtext)  |  date (int)  |
--------------------------------------------------------------------------------------------

relations columns:

----------------------------------------------------
|  id (int)  |  source_id (int)  |  user_id (int)  |
----------------------------------------------------

I tried using JOIN for retrieving 10 most recent rows from posts which are related to a specific user:

SELECT      p.id, p.title, p.content, r.id AS rid
FROM        posts AS p
JOIN        relations AS r
ON          r.source_id = p.source_id
WHERE       r.user_id = 1
ORDER BY    p.date DESC
LIMIT       10

However, it takes ~30 seconds to execute it (SDD hosting!). I tried lots of index combinations for both tables, including indexes for single and multiple columns – none of that interfered the time of execution anyhow. Is there any way to speed up the selection?

For user_id=1 there are ~1,000 source_id's and ~450,000 rows in posts.

SHOW CREATE TABLE... results:

CREATE TABLE `relations` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `source_id` bigint(20) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `source_id` (`source_id`),
 KEY `source_id_2` (`source_id`,`user_id`),
) ENGINE=InnoDB AUTO_INCREMENT=7692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `posts` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `source_id` bigint(20) unsigned NOT NULL,
 `title` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
 `date` int(10) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `source_id` (`source_id`),
 KEY `date` (`date`),
 KEY `date_2` (`date`,`source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4867283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN results:

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise.

You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then returning the 10 most recent ones. You'll need an index on posts by (date, source_id). It would return quickly for the most recently active users, but take much longer for users without recent posts. Something like the following:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name(u_id int)
BEGIN
  DECLARE fd DATE;
  DECLARE d DATE;

  SELECT MIN(date), MAX(date) INTO fd, d FROM posts;

  CREATE TEMPORARY TABLE last_posts (id int);

  WHILE d > fd AND (SELECT COUNT(*) FROM last_posts) < 10 DO
    INSERT INTO last_posts (id)
    SELECT p.id
    FROM relations AS r
    JOIN posts AS p ON (p.source_id = r.source_id AND
                        p.date > date_sub(d, interval 7 day) AND p.date <= d)
    WHERE r.user_id = u_id
    ORDER BY p.date DESC
    LIMIT 10;

    SET d = date_sub(d, interval 7 day);
  END WHILE;

  SELECT p.id, p.title, p.content, r.id AS rid
  FROM posts p
  JOIN relations AS r ON (r.source_id = p.source_id)
  WHERE p.id IN (SELECT * FROM last_posts)
  ORDER BY p.date DESC
  LIMIT 10;

  DROP TABLE last_posts;
END;;
DELIMITER ;

You could play with the intervals, reducing them to 1 day (more cycles with faster queries). Ensure you have an index on posts (date).


How to design a database The file which contains the day to day activities of each employee. And it shows the detailed description of employee what they done at work time. So these files are prepared by employee.

Now, my requirement is how to design a database for this model. I know only the basics of databases and yet I didn't design any databases. So any one help me to solve how to create a database design for the application.

The design should also work for the query to get details about individual employee like

For Ex:

Total time spent by the shiva 'shiva' on CCTNS project
To find the team leader for the specified project
To find the members in a particular project

I would suggest two fundamental tables for this purpose, if you're creating this all from scratch. This assumes that this is being created in PostgreSQL.

An employee table containing their name and whatever other details are important for your application, with a numeric primary key. The primary key is important because you may have more than one employee with the same name, and you may have employees that change names in the future. Using a key allows the information about their time to remain consistent.

An activity log table containing:

a primary key column (type SERIAL is likely fine for this)
an employee ID column (this will reference the column in the other table)
a project column (you can make this free-text, or create another table with a primary key and reference it here), this column should be nullable to reflect activity log entries that aren't related to a project (eg tea break)
a tstzrange column to represent the period for the log entry

Now this schema doesn't contain enough information to identify a team leader. You could add this information to another table, referencing your employee (and possibly project) table.

Here's a quick example schema you can play with:

 CREATE TABLE employees (
     employee_id SERIAL NOT NULL,
     employee_name text NOT NULL,
     PRIMARY KEY (employee_id)
 );

 CREATE TABLE projects (
     project_id SERIAL NOT NULL,
     project_name text NOT NULL,
     PRIMARY KEY (project_id)
 );

 CREATE TABLE project_memberships (
     employee_id integer NOT NULL REFERENCES employees,
     project_id integer NOT NULL REFERENCES projects,
     supervisor_employee_id integer REFERENCES employees,
     PRIMARY KEY (employee_id, project_id)
 );

 CREATE TABLE activity_logs (
     activity_log_id SERIAL NOT NULL,
     employee_id integer NOT NULL REFERENCES employees,
     project_id integer,
     activity_time tstzrange NOT NULL,
     activity_notes text,
     PRIMARY KEY (activity_log_id)
 );
 ALTER TABLE ONLY activity_logs
     ADD CONSTRAINT activity_logs_project_member_fkey FOREIGN KEY (employee_id, project_id) REFERENCES project_memberships(employee_id, project_id);


pg_basebackup giving "could not receive data from client: Connection reset by peer" I'm trying to get data to a new slave box from my new master (previously the slave).

I've tried the steps already and it worked previously.  Now when I run the following command:

pg_basebackup -h masterhost -D /var/lib/postgresql/9.3/main -U rep -v -P -X stream

Sometimes when I run this command, nothing is logged.  Other times I receive: "could not receive data from client: Connection reset by peer".  I can see this process running on the master:

postgres: wal sender process rep slavehost(60899) sending backup "pg_basebackup base backup"

I'm able to scp from one box to another at 100MB/s
I've set up the snakeoil certs on both boxes
It rejects invalid passwords
When I kill the process on the slave, I get "pg_basebackup: could not initiate base backup: FATAL:  terminating connection due to administrator command"
There is no additional load on either box when I run this command
The /var/lib/postgresql/9.3/main dir on the slave is owned by postgres and is empty
The DB is ~60GB
Eventually the "wal sender process" on the master ends but nothing seems to happen
I can psql to the master box from the slave using a non-replication user
Both boxes are Ubuntu 14.04 running PG 9.3.6

Slave config:

listen_addresses = '*'
hot_standby = on

default_statistics_target = 50
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 22GB
work_mem = 192MB
checkpoint_segments = 16
shared_buffers = 7680MB
max_connections = 160
log_min_duration_statement = 80
log_temp_files = 0

Master has the same but also:

wal_buffers = 8MB
checkpoint_segments = 16
wal_keep_segments = 128
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 2
vacuum_defer_cleanup_age = 100000
ssl_renegotiation_limit = 0

If you have errors in your log along the lines of SSL Renegotiation Failure or SSL Error, then you likely ran into an issue with a version of OpenSSL and SSL renegotiation during the streaming basebackup.

Setting ssl_renegotiation_limit = 0 should help address your problem.

There are and have been many discussions now on disabling ssl renegotiation by default on the PostgreSQL lists. Example here: http://www.postgresql.org/message-id/E1ZKB9u-00050W-7V@gemulon.postgresql.org

Hope that helps. =)


Views missing on MySQL database We have a MySQL database that was restarted a few hours ago

When it restarted, the views were missing. The tables all seem intact, and a check on the mysql tables seems all good.

Any ideas what could have happened?

A backup was restored to the server sans views.  Simple really.


opened_tables count increasing rapidly number of opened tables is increasing rapidly after sometime.

MariaDB [(none)]> show global status like 'open%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Open_files               | 6      |
| Open_streams             | 0      |
| Open_table_definitions   | 1200   |
| Open_tables              | 1200   |
| Opened_files             | 791203 |
| Opened_plugin_libraries  | 0      |
| Opened_table_definitions | 788523 |
| Opened_tables            | 807692 |
| Opened_views             | 0      |
+--------------------------+--------+
9 rows in set (0.00 sec)

MariaDB [(none)]> show global status like 'up%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Update_scan               | 200   |
| Uptime                    | 2028  |
| Uptime_since_flush_status | 2028  |

how global status like '%conn%';
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 2     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 151   |
| Max_used_connections                          | 13    |
| Performance_schema_session_connect_attrs_lost | 0     |
| Slave_connections                             | 0     |
| Slaves_connected                              | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 12    |
| wsrep_connected                               | OFF   |
+-----------------------------------------------+-------+

 select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|     1986 |
+----------+

Can anyone suggest how to investigate why the opened tables count is increasing so rapidly and how to resolve this issue?

would you confirm what load you are running during this 30 mins of database uptime?  this is actually accumulative values of the total opened tables and a smaller table open cache or creating temporary tables can increase this value.

paste values of CREATED_TMP_TABLES, TABLE_OPEN_CACHE.  Also paste total ram available for this DB on your machine.

Thanks


Ranking unique rows in a table - MySQL I am trying to rank the table below based on score and class, I have been looking for a way to isolate each class so they can have their unique rank.

This is the main table

Id  Name   Class     Score
1   Stu1   Grade 1   100
2   Stu2   Grade 1   50
3   Stu3   Grade 2   10
4   Stu4   Grade 3   10
5   Stu5   Grade 3   88
6   Stu6   Grade 1   99

For instance, ranking students in Grade 1 should be something like

Name    Rank
Stu1    1
Stu6    2
Stu2    3

Then if i wanna rank Grade 3 Students, it should result to;

Name    Rank
Stu5    1
Stu4    2

I used this code

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( DISTINCT score
ORDER BY score DESC ) FROM scores)
) AS rank
FROM scores WHERE class = 'Grade 1'

but it keeps ranking the entire column which isn't what I want

Kindly help me...I'm really stuck on this.

Thanks

While keeping your approach to the solution, the query will be

SELECT s.id,
       s.class,
       s.name,
       s.score,
       FIND_IN_SET( s.score, ( SELECT GROUP_CONCAT( DISTINCT ss.score
                                                    ORDER BY ss.score DESC )
                               FROM scores ss
                               WHERE s.class = ss.class ) ) AS `rank`
FROM scores s
-- WHERE class = 'Grade 1'
ORDER BY class, `rank`


Does redundancy exists in this table? I'm studying a DB course and I found this table:

In this exercise, the objective is to determine whether the table has redundancy or not.

According to the definition of redundancy

  Data redundancy occurs in database systems which have a field that is repeated in two or more tables

Redundancy DOES exists in the table,FECHA_INICIO(START_DATE) and FECHA_FIN (END _DATE) attributes, but should this attributes be considered as redundant?

Dates are quite volatile and I'm not quite convinced that those columns should be treated as redundant?

Any ideas on whether this table contains redundancy or not?

Redundancy does not mean:

Columns that have the same data type
Columns (or rows) that have the same data value (if this is coincidental)
Columns that are foreign keys that link a child table to its parent (including the same data value as FK and PK in the respective tables)

Redundancy is not about columns or rows for that matter.  Redundancy is about functional dependencies.  If you have predicates (i.e. columns) that are transitively dependent, then you have redundancy.

Hunting for redundancy is a bad approach when optimizing a logical data model.  Trying to decide what is and isn't redundant is tricky and frankly a little bit beside the point.  Focus on normalization.  This will eliminate your redundancy along with other potential problems along the way.

When you move from logical model to physical database, you can then determine whether managing some redundancy is worthwhile for some reason.


How dangerous is it to have a user's password in plaintext in a cgi script? I am setting up a MySQL database and am providing a web interface for it. Now, the DB is extremely simple and the interface just runs certain SELECT statements.
I have created a user for my script to use when connecting with these commands:

CREATE USER 'foo' IDENTIFIED BY PASSWORD 'bar';
GRANT SELECT ON dbname.* TO foo

I then have these lines in my Perl script:

my $user = "foo";
my $pw = "bar";

NOTES:

The data on the database are completely public and users can use/redistribute them at will. I have no interest in blocking anyone from accessing it, it is to provide a dataset to the scientific community.
The data are static, there is no need for anyone to be able to modify them in any way and the only user who has the right to is root (the only other user on the system).

So, my question is, how dangerous is this? Given that the user only has SELECT rights, what bad things can happen with this, clearly insecure, setup? Is there a better way of allowing access to my script bearing in mind the two notes above?

You should always put application passwords in O/S Environment Variables (envvars).

Can only be read by the assigned O/S user (or root)
You won't accidentally screw up file permissions (if putting passwords in files)
You won't accidentally check passwords in to source control (this is particularly important in open source)
Survives reboots
envvars are easy to read with most languages
You should follow the principle of least privilege and run say your web server as its own user.

http://perldoc.perl.org/Env.html


Installing scott schema for oracle 11g express I want to install scott schema, a sample database in oracle.

But in express edition, I can't find out it.

Please help me install it on Oracle 11g express.

I'm a newbie, pls help me

when you are installing oracle database you can install all oracle example schemas like scott, HR, oe ...
you can install these in step "Specify Database Config Details".click the last tab page "Sample Schemas" then check the check box "create database with sample schemas" as displayed bellow in screenshot.

if you already installed database you can recreate it for training "without uninstalling it" by write the command:
start -> run -> dbca
to run oracle database configuration assistant.
follow steps to drop your running database and recreate it with sample schemas.

update

and here in Oracle 12C:


SQL SERVER Verifying mirror status from 2 weeks ago How we can verify if the mirror was working fine two weeks ago ??

I was checking the sqlerror log, I'm not sure whether the mirroring sync status will be logged, if so, I don't have that log. because the log was reinitialized. we do take backups. So, my other question is what to restore from tap backup to retrieve error log ( is it master db, or msdb )?

Your help is appreciated!!

Depending on how long your SQL Server logs are retained, you can filter the SQL Server Logs with "mirror" to get an overview (not every detail) of the mirroring status on your Mirror server.

You will see messages like:

Database mirroring is active with database 'Name' as the mirror copy.
Database mirroring is inactive with database 'Name' as the mirror copy.

And some other messages as well that may reveal other issues on the mirror server such as:

The mirroring connection to "serverconnection" has timed out ...


Transaction log in the full backup I have a little misunderstanding when I make SQL Server full database backup. How does exactly full backup work? What data does it backup?

Here is a case: I scheduled full database backup supposing at 12.00 it takes about 15 minutes, so it finishes at 12.15, But at 11.58 one transaction has begun and finished at 12.05.

So my question is: Will my full backup contain this transaction or now? And why?

The simple answer is yes, it will contain the transaction.  Also simply the backup contains anything that happens before the end of the backup, not as of the start.  The full backup actually contains the transaction log information as of the completion of the backup.  When  you do your restore any transactions that were not committed at that time get rolled back.

If you want to learn more about it you can research backup internals.

Here is a technet article on it and the Kin's answer to this question has a number of additional good links.


How to select specific value from a table that has multiple values for a record in primary key table in PostgreSQL? I have three tables as follows (with sample data):

CREATE TABLE users (
  id int PRIMARY KEY,
  login varchar(20),
  organization varchar(20)
);
CREATE TABLE rights (
  id int PRIMARY KEY,
  name varchar(20)
);
CREATE TABLE user_rights_rel (
  uid int REFERENCES users,
  rid int REFERENCES rights,
  PRIMARY KEY (uid, rid)
);

-- 1-5  'A'
-- 6-10 'B'
INSERT INTO users VALUES
  (1,  'Smith',   'A'),
  (2,  'Jane',    'A'),
  (3,  'Katrin',  'A'),
  (4,  'Todd',    'A'),
  (5,  'Ervine',  'A'),
  (6,  'David',   'B'),
  (7,  'White',   'B'),
  (8,  'Anderson','B'),
  (9,  'Randi',   'B'),
  (10, 'Polak',   'B');

INSERT INTO rights values
  (9,'Contact'),
  (62,'Download'),
  (59,'Reviewer'),
  (32,'High Reviewer'),
  (64,'Read Only'),
  (66,'Data Entry');

INSERT INTO user_rights_rel VALUES
  (1,9),(1,64),(1,59),(1,62),
  (2,64),(2,59),(2,62),(2,66),
  (3,66),(3,62),(3,9),
  (4,66),(4,64),(4,9),
  (5,62),(5,64),(5,9),
  (6,9),(6,64),(6,62),
  (7,64),(7,62),
  (8,66),(8,9),
  (9,66),(9,64),(9,9),(9,32),
  (10,62),(10,64),(10,9);

Now I want to write a query based on the below criteria:

If a user has Reviewer or High Reviewer right along with other rights, it should be counted as Reviewer.
If a user has Data Entry right along with the other rights(except Reviewer and High Reviewer), it should be counted as Data Entry User.
If a user has Read Only right along with the other rights(except Reviewer, High Reviewer and Data Entry), it should be counted as Read Only User.

As per the above criteria, my desired output is:

Organization        Reviewer     Data Entry        Read Only
   A                    2            2                 1
   B                    1            1                 3

Organization A has 2 Reviewers (Users 1 and 2). If a user has Reviewer right then we count it as Reviewer user and we ignore other rights.

Organization A has 2 Data Entry users (Users 3 and 4). If a user does not have Reviewer and High Reviewer rights then we look for Data Entry right, if it has Data Entry right then we count it as Data Entry user and ignore other rights.

Organization A has 1 Read Only user (User 5). If a user does not have Reviewer,High Reviewer and Data Entry rights, then we look for Read Only right, if it has Read Only right then we count it as Read Only user and ignore other rights.

The priority should be as follows:
1. Reviewer (if a user has it, count him/her as Reviewer and ignore other rights).
2. Data Entry (if a user has it, count him/her as Data Entry and ignore other rights).
3. Read Only (If a user has it, count him/her as Read Only and ignore other rights.)

Any help is appreciated in advance.

Single pass using DISTINCT ON

We can use DISTINCT ON in subselect to get the users highest priority rights. Then we need only count the rights by those groups.

It's important to note, we're joining to rights here. We needn't do that. You could adopt the method of simplicity used in @JackDouglas's answer to compare on ints. It saves a lot of time by not joining but because we only do one table scan we're still twice as fast. In production, you may want to skip the join on rights.

SELECT
  organization,
  count(rightgrp) FILTER (WHERE rightgrp = 1) AS "Reviewer",
  count(rightgrp) FILTER (WHERE rightgrp = 2) AS "Data Entry User",
  count(rightgrp) FILTER (WHERE rightgrp = 3) AS "Read Only User"
FROM (
  SELECT DISTINCT ON (organization, uid)
    organization,
    uid,
    CASE
      WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
      WHEN rights.name IN ('Data Entry') THEN 2
      ELSE 3
    END AS rightgrp
  FROM rights
  JOIN user_rights_rel
    ON rid = rights.id
  JOIN users
    ON uid = users.id
  ORDER BY 1, 2, 3
) AS t
GROUP BY organization
ORDER BY organization;

 organization | Reviewer | Data Entry User | Read Only User
--------------+----------+-----------------+----------------
 A            |        2 |               2 |              1
 B            |        1 |               1 |              3
(2 rows)

Using the tablefunc extension and crosstab

I wouldn't use this method, it's too sledge-hammery for what you need.

Here is a method that users crosstab for display, though it doesn't have to,

SELECT *
FROM crosstab($$
  SELECT organization, rightgrp, count(*)
  FROM (
    SELECT DISTINCT ON (organization, uid)
      organization,
      uid,
      CASE
        WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
        WHEN rights.name IN ('Data Entry') THEN 2
        ELSE 3
      END AS rightgrp
    FROM rights
    JOIN user_rights_rel
      ON rid = rights.id
    JOIN users
      ON uid = users.id
    ORDER BY 1, 2, 3
  ) AS t
  GROUP BY organization, rightgrp
  ORDER BY 1,2
$$, $$
  SELECT * FROM (VALUES (1),(2),(3)) AS t(cat)
$$
) AS ct(organization text, "Reviewer" int, "Data Entry User" int, "Read Only User" int);

First, we optimize the table read to a single pass which we select only the highest-privilege rights for the (organization,user).

SELECT DISTINCT ON (organization, uid)
    organization,
    uid,
    CASE
      WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
      WHEN rights.name IN ('Data Entry') THEN 2
      ELSE 3
    END AS rightgrp
  FROM rights
  JOIN user_rights_rel
    ON rid = rights.id
  JOIN users
    ON uid = users.id
  ORDER BY 1, 2, 3;

 organization | uid | rightgrp
--------------+-----+----------
 A            |   1 |        1
 A            |   2 |        1
 A            |   3 |        2
 A            |   4 |        2
 A            |   5 |        3
 B            |   6 |        3
 B            |   7 |        3
 B            |   8 |        2
 B            |   9 |        1
 B            |  10 |        3
(10 rows)

Then we simply GROUP BY organization, rightgrp and select the count, like this

SELECT DISTINCT ON (organization, uid)
  organization,
  uid,
  CASE
    WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
    WHEN rights.name IN ('Data Entry') THEN 2
    ELSE 3
  END AS rightgrp
FROM rights
JOIN user_rights_rel
  ON rid = rights.id
JOIN users
  ON uid = users.id
ORDER BY 1, 2, 3

 organization | rightgrp | count
--------------+----------+-------
 A            |        1 |     2
 A            |        2 |     2
 A            |        3 |     1
 B            |        1 |     1
 B            |        2 |     1
 B            |        3 |     3
(6 rows)

From there we can feed this to pivot using PostgreSQL's tablefunc extension which provides crosstab. We can generate the pivot manually though using crafty SELECT/FILTER. Keep in mind right #1 is the highest priority

 organization | Reviewer | Data Entry User | Read Only User
--------------+----------+-----------------+----------------
 A            |        2 |               2 |              1
 B            |        1 |               1 |              3

If you want to generate it without crosstab..


SQL Server database Mirroring on different servers I tried to perform SQL Server database Mirroring(with SQL Server 2008R2). And I got success to perform mirroring with "High Safety" mode.

And for that I have used three instances of SQL Server database.One for Principal second for Mirror and third for Witness.

As I mentioned I used three instances but all of those instances was on the same SQL Server.

But when I tried to perform mirroring with different SQL Server instances then it was not successful.

So I want to know can we perform mirroring with different SQL Server instances? Or we can only do it with instances on same server?

Also in mirroring how many instance we can configure as a Mirroring server ?

And is it a right way to achieve database availability? Or is there any other/advance way to achieve database availability and security?

So I want to know can we perform mirroring with different SQL Server instances? Or we can only do it with instances on same server?

Yes of course you can do it. Perhaps it is best idea or a suggested good practice to keep Principal mirror and witness on different servers. Of course Principal and mirror would be different servers.Have a look at article Prerequisites, Restrictions, and Recommendations for Database Mirroring and Things to consider before database setting database mirroring

  Also in mirroring how many instance we can configure as a Mirroring server ?

Question is little incorrect or not properly formed. For one principal you can have only one mirror server. Its is not like Logshipping where you can have multiple standby servers for one primary server.

  And is it a right way to achieve database availability? Or is there any other/advance way to achieve database availability and security?

To answer this question to absolute correctness one would require to know your environment RPO and RTO. A simple SQL server cluster with mirroring can work good. You can even go storage replication, SRDF various technologies for Hardware redundancy. Database mirroring almost provides a high availability for SQL Server database. If you want OS redundancy as well create SQL Server cluster and then move databases to cluster and then create database mirroring for the desired database.

PS: In whole question you did not provided what error you got while configuring mirroring. Nonetheless if you read the articles I gave you would be able to configure it correctly


How to get more physical details when executing an SQL query? I want to get some physical features about a SQL query execution with PostgreSQL, like

Read/write page accesses
I/O operations
Response time
Sequential I/O throughput rate
Deadlocks
Pages scanned Queries ratio
Hit ratio, Pages read, Logging, Ratio of using indexes Rows selected , Average sort time, Number of locks held, Number of sorts Throughput..

and more, depends on what's available

You should use pg_stat_statements.

Modify postgresql.conf as follows:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
track_io_timing = on

and restart PostgreSQL.

Then

CREATE EXTENSION pg_stat_statements;

and find all the data you want in the view pg_stat_statements.


AlwaysOn Availability Group error after Windows Update in environment without a failover cluster Our SQL Server service was down this morning which brought down some of our websites.  When I went to check the Windows Event Viewer I saw the following errors:

  Script level upgrade for database 'master' failed because upgrade step
  'SSIS_hotfix_install.sql' encountered error 942, state 4, severity 25

  Cannot recover the master database. SQL Server is unable to run.
  Restore master from a full backup, repair it, or rebuild it. For more
  information about how to rebuild the master database, see SQL Server
  Books Online.

The first thing that I did was to Google the errors.  I eventually found a forum entry with the exact problem and a fix for it (also on a blog entry where I am seeking a solution).  The problem has something to do with AlwaysOn Availability groups and the fix requires you to:

Start SQL Server service with Trace Flag 902:

  Net Start MSSQL$InstanceName /T902

Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL11.MSSQL$InstanceName\MSSQL
Stop SQL Server services:

  Net Stop MSSQL$InstanceName

Start SQL server service from SQL Server configuration manager
Add SSISDB back to Availability Group

However, I could not get past step #2 because I got the following error when I tried to expand the "AlwaysOn High Availability" folder:

  The "AlwaysOn feature must be enabled for the server instance
  'InstanceName' before you can create an availability group on this
  instance.

Then I followed the instructions to go to "SQL Server Configuration Manager" and the "AlwaysOn High Availability" tab to turn on the feature.  This time the feature was grayed out and there was a message saying that the computer node is not in a failover cluster.

My question is:

How can I fix this issue if we don't even have a failover cluster setup that would use this feature?

I ran dbcc checkdb on master; the results were:

  CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.

AlwaysOn Availability Group is NOT on because I do not even have a failover cluster.

Community Wiki answer to document the solution

KenWilson:

Skip all the steps pertaining to AlwaysOn and run the SSIS_hotfix_install.sql script.


Is there a limit to how many backups can be appended to a single file? A recent question got me looking at MS Documents and wondering.

Is there a limit to how many backups can be appended to a single file?

By default SQL Server uses NOINIT to append new backups to the old backup file.

  { NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT). Source

The documents clearly say that lack of free disk space will cause an appended backup to fail.

  If a disk file fills while a backup operation is appending a backup to the media set, the backup operation fails. The maximum size of a backup file is determined by the free disk space available on the disk device; therefore, the appropriate size for a backup disk device depends on the size of your backups. Source

The answer at SQL Recover from .bak file with NOINIT indicates that the Position from RESTORE HEADERONLY indicates the individual backup in the file. Which is a "smallint" field, which should max out at 32,767

For the most part when googling around, you find people who are accidently appending their backups, and can't understand why the backups are so big.

I don't find any clear references on how many backups can be appended, assuming sufficient disk space.  Is the limit 32,767 or something else entirely?

TL:DR; It is possibly to put 32,000+ backups on a single file.  If this is good thing or if you can recover from a backup on this file is not addressed here.

I started taking tlog backups last night, on an existing database (231682) with no activity.  I used a while loop and a counter so I could get a running total.

DECLARE @counter int
SET @counter = 1
While 1=1
Begin
BACKUP LOG [231682] TO
DISK = N'G:\SQLBackups\Test_Tlog.trn' WITH NOFORMAT, NOINIT,
NAME = N'231682-Log Database Backup', SKIP, NOREWIND, NOUNLOAD
SET @counter = @counter + 1
print @counter
End

16 hours later the count is at 5,967 with a file size of 356MB. This might take a while. At the start was completing 24 log backups per second.
1 day 16 hours: count is 8,401 with a file size of 700MB, sp_whoisactive shows wait info (2029ms)BACKUPTHREAD
4 days 18 hours: count is 12,834 with a file size of 1.6GB sp_whoisactive shows wait info (52113ms)BACKUPTHREAD time between backups has slowed to about 70 seconds per log backup.
5 days and some hours; an external event (server reboot) caused the tlog backups to halt. count of completed backups is 13,717 file size is 1.8GB, time between backups is about 80 seconds. Validated with
restore headeronly from disk='G:\SQLBackups\Test_Tlog.trn' Did not attempt a database restore, as it would be beyond painful. Set counter to start SET @counter = 13717 and restart adding backups to the same file with the same code.  Backups resume and are taking about 80 seconds
1 week; second restart due to external issues.  Count is 15,186 file size 2.3GB each backup is taking about 90 seconds.
Week and half; Count is 17,919 file size is 3.2GB each backups is taking about 2 minutes 25 seconds.
2 Weeks; Count is 19,645 file size is 3.8GB each backup is taking about 2 minutes 45 seconds.
3 Weeks; count is 22,919 file size is 5.2GB each backup is taking about 3 minutes 30 seconds (17 per hour) as I am running the endless t-log backups in a job now, I have added this to the code RAISERROR(N'Count equals :%d', 16, 1, @counter ) WITH LOG; so the running count displays in the SQL error log Thank you @Erik Darling
4 weeks; count is 25,587 file size is 6.6GB each backup is taking about 3 minutes 45 seconds (16 per hour)
5 Weeks; count is 28,242 file size is 8GB each backup is taking about 4 minutes 35 seconds, (13.5 per hour)
6 weeks; count is 30,037 file size is 9.1GB each backup is taking about 5 minutes (12 per hour)
~7 weeks: Running out of space on the dedicated backup disk. Backups start failing when insufficient space, but the job keeps running and trying. Stop everything else, made a bit more space by deleting some files. The count in SQL logs is wrong, as it counts both failed and successful attempts.

Stop job
File size is 10.4GB each backup is taking about 5 minutes (12 per hour), time is the same on success or failure.
Check backup with restore headeronly from disk='G:\SQLBackups\Test_Tlog.trn' Count is 32,021
Header implies all the successful backups are fine.
Most backups show a value of 75,776 for BackupSize and a value of ~4,000 for CompressedBackupSixe, Compressed size varies on each backup.
I did not attempt a Restore with the t-log file. I suspect there will be issues, and I would want to try it before I made everything as ugly as it is now.
Delete the t-log file, restart normal backups.

I am compressing backups by default on this instance.

Week 3 Note: File size and backup time is growing out of proportion to number of backups. Looking at tlog headers, we see the backup in position 2 has a size of 75766 bytes and a start to finish time of one second or less.  The backup in position 22919 also has a size of 75766 bytes and a start to finish time of one second or less.  The overhead of appending the backups to the same file seems be causing the slow down. The abnormal growth is probably related to weekly maintenance tasks I have running on the instance.

Off site backups, It looks like my offsite backup solution (IBM Spectrum) is not backing up the trn file.  I suspect this is because the file is constantly being edited.

Edit, some time later.  I was considering doing another experiment to test recovery at around 30,000 backups. To avoid the issues of trying to restore multiple t-logs I looked at using differential backups.  I created an empty database, took a full backup and then took 10 differential backups.  Then I took 10 t-logs backups and using RESTORE HEADERONLY FROM DISK I compared the size, the differential backups are significantly larger then the t-logs, I don't have a enough space to perform a good test.

Differential backups 2-10 (first is always a bit bigger)

BackupSize = 1126400
CompressedBackupSize = 62271

T-Logs backups 2-10 (first is always a bit bigger)

BackupSize = 75776
CompressedBackupSize = 3800 (average, it varies)

Differential backups are about 16 times larger, best case I could only get about 2,000 of them, I am not doing further testing at this time.


Are there tools for SQL-Server helping to add the semicolons? Earlier versions of SQL-Server didn't recommend the use of semicolons to terminate sql-statements.

Beginning with sql-server-2008 examples show, that now Microsoft recommends terminating with semicolons.

There are some formating tools which format sql queries and add the semicolons. I think the basic function is somewhere in SMO.

The only problem is that these tools remove all the comments.

Any ideas how to preserve all comments and how to add all recommend semicolons?

Start typing and don't trust code generators.

As you bring up procedures and code to edit it for other reasons, just add the semi-colon in then.  You wouldn't want to deploy all your stored procedures in one batch just to add in the semi-colons only.


Cleanup of Oracle killed session I killed a session

ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

But it remains in v$session for 10 days, although an attached process is absent in v$processes.

SELECT
    s.username,
    s.osuser,
    s.sid,
    s.serial#,
    p.spid
  FROM v$session s, v$process p
 WHERE s.sid = '123'
   AND p.addr (+)= s.paddr;

I've also checked all Linux processes related to Oracle and found no zombies: all present processes are consistent to alive sessions.

This happens not for all the killed sessions. It seems that PMON is active, because the most of the killed sessions are cleaned from the list in a reasonable time (often it takes a second).

What can be done to eliminate long-playing killed session from the list?

I think this expected behavior for an inactive session that has been killed.

I have a session connected as a user 'JAY' which is in 'INACTIVE' state.

 [oracle@myserver datafile]$ sqlplus jay@hrpdb
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Let's check session details using sys user.

SQL> col username format a20

SQL> select sid,serial#,username,status from v$session where username ='JAY';

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        15      36102 JAY                  INACTIVE

Let's kill it.

SQL> alter system kill session '15,36102';

System altered.

Check session details once again.

SQL> select sid,serial#,username,status from v$session where username ='JAY';

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        15      36102 JAY                  KILLED

It is still there in 'KILLED' state.

Now let's try to do some operation in Jay's session which was inactive and killed.

SQL> select * from tabs;
select * from tabs
*
ERROR at line 1:
ORA-00028: your session has been killed

Now let's check session details using SYS user.

SQL> select sid,serial#,username,status from v$session where username ='JAY';

no rows selected

The session is no longer visible in v$session.


SQL Server 2008 R2 Management Studio : "Evaluation period has expired" I've spent several hours trying to fix this issue so far with no success. I had installed SQL Server 2008 Evaluation Edition. Out of fear it might expire & create problems for me I installed SQL Server 2008 R2 Express Edition & migrated all my databases to Express. It worked fine for several months but now I have problems when I try to launch Management Studio:

I have tried the fixes in http://support.microsoft.com/kb/971268 but they didn't help.

I am not able to understand even after uninstalling the SQL Server 2008 Evaluation Edition why I am getting error with the Express Edition.

I even installed new instance of SQL Server 2008 R2 Express Edition, uninstalled SSMS 2008 Express & re-installed it still nothing seems to work for me.

Upgrade screen of installation:

Present status of my system

I have two instances of sql server 2008 r2 express edition
SSMS 2008 express edition

I am not sure what else to do.

You've already I assume, applied the registry fix suggested by Microsoft.

Since they are saying that this has been resolved since SP1, have you considered installing a newer version of SQL Server 2008 R2 Express?

http://www.microsoft.com/en-us/download/details.aspx?id=26729

Update 12:27

Based on what you're saying and without having your system in front of me, I'd try to find out if the evaluation edition you think you removed might actually still be there.

You can run the SQL Server features discovery report to find out what components are actually installed on your laptop.

Start -> All Programs ->  SQL Server 2008 -> Configuration Tools -> SQL Server Installation Center.  Then on the left you select Tools, and then click on the Installed SQL Server features discovery report.

Installation Center - Tools

Features Report

I would also go Control Panel, Uninstall Programs, and make sure there wasn't anything left over from the evaluation edition.  Once you're absolutely sure that evaluation is gone, reboot the system just to make sure that the registry is reloaded correctly.


Unable to Force a WSFC Cluster to Start Without a Quorum I was testing a three node AlwaysOn availability group cluster.  SQL Server  is on Node 1 and I powered off Node 1 and Node 3 simultaneously.  The cluster lost its quorum and appear to be down.  I tried following "Force a WSFC Cluster to Start Without a Quorum", but I am unable to bring the cluster online.  It is stuck is "joining" state.

What can we do now?

cluster log

Initially the cluster service is down.  After executing the Start-ClusterNode command it goes into running state. I have tried restarting the cluster service and also restarting the node, but can't force quorum.

In the screenshot it shows the attempt to use -FixQuorum as part of the Start-ClusterNode cluster powershell commands, however the cluster log shows that quorum was not forced.

Your log shows the following:

Creating cluster node <vector len='1'>
<item>ClusSvc</item>
</vector>

Which is just starting up the cluster service. The log is also missing the fact stating that on this node quorum was forced.

Here is an example of how a cluster log (WSFC 2016, like the one you posted) would look if you forced quorum on the node:

Creating cluster node <vector len='2'>
<item>ClusSvc</item>
<item>/FixQuorum</item>
</vector>

Additionally, you'd see another entry further down:

[CS] FixQuorum mode is on

My suggestion is to log locally onto the server and try running the powershell command (after shutting down the cluster service if it's still running):

Start-ClusterNode -Node SQL2 -ForceQuorum

Then check the status about 15 seconds later with:

Get-ClusterNode -Node SQL2

Check to see what is the status and also check the cluster log to see if the ForceQuorum switch was properly passed.


Copy result of a query to a new PostgreSQL database I have a Postgres database A with 2 tables,  and I need to copy part of these tables (recent records in 1 month) to the new database with the same structures.

How to achieve this, as I know cross database references are not implemented. So I can't select records from the old database to copy to the new one.

I can code, so any ideas are welcome.

Some ideas:

You can use COPY (SELECT ...) TO ... to write the relevant parts of the tables to a file on the database server and use COPY ... FROM on the destination database to load the data.

If the databases are not on the same machine, you could use NFS or similar for a shared directory, or you can use COPY ... TO PROGRAM ... to pipe the result into scp.
You can use a postgres_fdw foreign table and just use

INSERT INTO foreign_tab
   SELECT ... FROM localtab;

to transfer the data.


T-SQL or PowerShell to move bak files older than 2 days to another location I'm using SQL Server 2005. One of my backup drives is filling up, so I want to move backups older than 2 days.

I was thinking of creating a job to run everyday, but got stuck on the scripts moving the .bak files.

Do you know a script to move files from drive A and move it to a shared location? My .bak files doesn't have a timestamp.

This Powershell example will find all .bak files older than two days in the c:\backup folder and move them to \shared\archive shared drive.

Get-ChildItem c:\backup\*.bak |where {$_.LastWritetime -lt (get-date).adddays(-2)} |move-item -Destination \\shared\archive

Note you cannot run PowerShell commands directly in SSMS. Try it in a SQL Server Agent job step, or just in a PowerShell command window. Should work fine in either.


Linked server to SQL Azure very slow I can use the Management studio to connect directly to my Azure SQL database and execute this query:

select * from DB.dbo.DSeries where instrument='name1'

and it works really fast. But if I create a linked server (called "WF") in my local server that connects to Azure and try to execute the same query,

select * from WF.DB.dbo.DSeries where instrument='name1'

The query is really slow. Why could that be happening?

Try setting the "Collation Compatible" to True on the Linked Server definition.

If you set it to False, it will pull the entire table down and do the compare on the receiving end. Consider the scenario where the local server is case insensitive, and the remote server is case sensitive, the results will differ based on where the compare is done.


Dynamic form builder database optimization Postgresql Hi I have a project that create forms dynamically I use a model like the following:

forms
-----
  id (PK)
  name
  (other fields)

form_fields
-------------
  id (PK)
  form_id (FK to forms.id)
  label (nome to the field)

form_submits (form inserted by someone user)
-------------
  id (PK)
  form_id (FK to forms.id)
  user_id (FK to users.id)

form_submits_fiels
-------------
  id (PK)
  form_fields_id (FK to form_fields.id)
  form_submit_id (FK to form_submit.id)
  data

Now I have seriously problem whit the performance in the production server because the form_element table have more than 1.500.000 rows; the number continue growing and the function that return all records of a form take more that 20 seconds, the question is how optimizer the model or the query to take less time

I have some ideas but I thing that not resolve the problem:

Paginate: if i could paginate the queries the performance will better so much but I thing that is not possible because the data was not in order.
MongoDB: If use a no relational model the performance maybe will better but I not sure so mach can ber better with Mongo and is a so expensive solution

If I had more reputation, I would add a comment asking you to describe your indexes. As it is, I'll assume you don't have indexes since you didn't mention them.

First, consider using EXPLAIN on your queries to analyze the decisions the planner makes. 1.5m rows shouldn't take 20 seconds with any sort of reasonable combination of hardware and indexing. See the documentation of the EXPLAIN modifier to get started with that. You should include (ANALYZE on) to see the cost breakdown and timing info from actually running the query, and more importantly the individual processes that go into each stage.

Second, a schema like this would benefit not only from indexes, but from some limited use of multi-column indexes. (See CREATE INDEX documentation.) Consider creating indexes on:

form_fields.formid, form_fields.id
form_submits.formid, form_submits.id

and others. It depends highly on what your code is doing. Duplicate the database to another server and create different configurations of indexes and run your code against it. (EXPLAIN (ANALYZE on) again here!) Multi-column indexes are great because they help the planner check multiple filtering/sorting criteria in one pass of the index.

Third, to address your last points, I'm not sure that pagination or moving to a non-relational model will help. The fact that you're using foreign keys actually points to a strongly relational set of data. You can, of course, try MongoDB for free and see what the performance looks like.


Tools to visualize adhoc multi dimentional data With SSMS I cannot run a MDX query such as

Select [Measures].[Internet Average Sales Amount] on 0,
[Product].[Category].children on 1,
Product.[Model Name].children on 2
From [Adventure Works];

since it supports only 2 axes and hence gives the error message

Results cannot be displayed for cellsets with more than two axes.

However SSAS itself supports 128 axes I believe.

What can I use to run ad hoc queries against SSAS with more than 2 axes?

What are you trying to achieve with more than two axes? If at the end you just want those fields/values to be dumped into a tabular result, you can rewrite your query to use crossjoins.

Select [Measures].[Internet Average Sales Amount] on 0,
{[Product].[Category].children * Product.[Model Name].children} on 1
from [Adventure Works]


Why does Postgresql tell me the database does not exist? I created a Ruby on Rails application just for demonstration to a colleague and then deleted the application, but I did not delete the database which was also created using Postgresql.

I go into posgresql like so:

psql postgres

postgres=# \l
                                            List of databases
                 Name                 | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
--------------------------------------+--------+----------+-------------+-------------+-------------------
 GenerateApp_development              | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 GenerateApp_test                     | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 GeneratorApp_development             | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 GeneratorApp_test                    | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Overtime_development                 | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Overtime_test                        | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 RoutingApp_development               | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 RoutingApp_test                      | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 dancortesPortfolio_development       | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 dancortesPortfolio_test              | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 freelance_camp_documents_development | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 freelance_camp_documents_test        | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 freelance_camp_proposal_development  | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 freelance_camp_proposal_test         | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres                             | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0                            | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/danale        +
                                      |        |          |             |             | danale=CTc/danale
 template1                            | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/danale        +
                                      |        |          |             |             | danale=CTc/danale
(17 rows)

I want to delete the ones that say RoutingApp_development and test.

postgres=# DROP DATABASE RoutingApp_development;
ERROR:  database "routingapp_development" does not exist

I get that error above. I have tried to search for why this is happening online and I have been unsuccessful in finding the answer, please help.

You need to double quote the name. In PostgreSQL we never double quote names. If you follow the convention, PostgreSQL will convert the unquoted identifier to lower case. In your case, that which created the db used double quotes.

Now you have a capital letter in the identifier.

From now on you have to double quote the identifier.

Not,

postgres=# DROP DATABASE RoutingApp_development;

But,

postgres=# DROP DATABASE "RoutingApp_development";


Archive table does not work I have a 19GB archive table that is working as expected on one server. I copied the following 2 files to another server. The table is not working on the new server.

r_mi_raw_20120712.ARZ
r_mi_raw_20120712.frm

The first server version is 5.5.22-log MySQL Community Server (GPL) by Remi
The second server version is: 5.5.20 MySQL Community Server (GPL)

What can be the reason?
When I used mysqlshow db_name it would list the table, but when I use --count I do not get count. Instead I get "Opening tables" state for command Field list.

update:

mysql seems to be writing to the ARN file...

r_mi_raw_20120712.ARN

Is this one time optimization? How do I improve the speed of ARN file creation?

There are a couple of nice links you should check out on the ARCHIVE storage engine

http://www.mysqlperformanceblog.com/2006/11/12/trying-archive-storage-engine/
http://datacharmer.blogspot.com/2006/03/mysql-51-improving-archive-performance.html

You may want to think about running OPTIMIZE TABLE on the table before copying it over. I cannot make any promises on an ARCHIVE table be copied to another server. Everybody trusts MyISAM tables to be that portable.

IMHO you are probably better off, definitely safer, doing a mysqldump of the ARCHIVE table and reloading. That way, you could trust mysqld to get the table into a readable, trustworthy format on disk in the new server.


Import from Access .accdb file to SQL Server How to import data from Access .accdb file to SQL Server? What should I choose in SQL Server Import and Export Wizard?

Suppose we choose OLE DB Provider for SQL Server Integration Services, then if that is a correct direction, what should be choose next from the following window? What to type in fields Data Source and Location, which mean more or less the same?

I followed instructions described here:
Easiest way to import access db (.accdb) into SQL Server 2016?
I have installed Microsoft Access Database Engine 2010 Redistributable

However I was hopeless in seeing Microsoft Office 12.0 Access Database Engine OLEDB

Update

If I choose Data Source as Microsoft Access (Microsoft Access Database Engine) I end up getting error message the one shown on picture below. Moreover I think that this data source is tailored for files mdb, not accdb.

I've had to import Access files into SQL Server before and haven't found the Import and Export wizard to be a very good tool for the job. What I ended up doing was opening up the Access file, finding the table, right clicking and going to Export -> ODBC Database. This does require you to have a User or System DSN setup for the target SQL Server database, and it is a bit slow, but it was the only thing that I found that worked all that well. (It will create the target table for you.)


Schemas and user permissions When our custom, home-grown CMS creates data tables using the GUI, it creates them against the site-specific user schema (i.e. sitename) instead of the dbo schema.

The issue is that our site-specific users currently do not "see" these other schemas even though they are in the db_owner role. So somewhere there is a permissions setting missing which would grant them the ability to read and write against those tables.

This issue has occurred when migrating to a new SQL server. Going from 2005 Workgroup Edition to 2008 Web Edition.

It could be as simple as:

GRANT SELECT ON SCHEMA::[sitename] TO [user];

If there are procedures and functions you will also need:

GRANT EXECUTE ON SCHEMA::[sitename] TO [user];

The full list of permissions applicable at the schema level is listed in the documentation:

GRANT Schema Permissions (Transact-SQL)

You can also set the default schema for any user, which may be helpful in some scenarios (for example, if they create tables without explicitly specifying the sitename schema):

ALTER USER [user] WITH DEFAULT_SCHEMA = [sitename];

Note that DENY overrides GRANT (and also overrides role membership rights, including db_owner), so check to be sure someone hasn't inadvertently blocked access to the schema(s). You can start your investigation if you find any rows here:

SELECT * FROM sys.database_permissions
  WHERE class_desc = N'SCHEMA' AND [state] = 'D';

Note that permissions can apply to a user indirectly (e.g. through AD group membership or database roles), so make sure you follow the bread crumbs for any database principal with a relevant DENY...


ORA-19815, ORA-01031 and ORA-12514 error I have Linux RedHat Enteprise with Oracle 11g database and I've stumbled upon a circle of errors and I can not find a way out.

First error in alert_XE.log:

ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/fast_recovery_area/XE/archivelog/2018_12_01/o1_mf_1_580_%u_.arc'
Errors in file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_2940.trc:
ORA-16038: log 2 sequence# 580 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_dh40n8hk_.log'
System state dump requested by (instance=1, osid=2940), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_diag_2859.trc
USER (ospid: 2940): terminating the instance due to error 16038

Which would be easily solveable with rman, but here comes error number 2 when "connect target /" on rman:

ORA-01031: insufficient privileges

Which is of course again solveable with orapwd file. I've created a new file with the right SYS password. Trying again to login via rman, but of course, no luck.

I've tried also with sqlplus with various commands like "sqlplus sys/@XE as sysdba" and I get error number 3:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I've tried "tnsping XE" and it looks ok:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

Any ideas how to proceed?

The last line in alert.log says that your instance should have been killed (Shutdown abort). This is why you can't connect: listener is up and running and responds to tnsping, BUT the instance is not available (listener does not currently know of service requested in connect descriptor").

You can verify with:

ps -ef | grep pmon

and check if the process ora_pmon_INSTANCE is present or not.

First you have to free space in archive destination, moving elsewhere archived logs, change parameter db_recovery_file_dest_size to a bigger size and/or add disk space to filesystem where path /u01/app/oracle/fast_recovery_area/XE/archivelog/2018_12_01 resides.

Then restart the instance. Sometimes I saw redolog corruption after this scenario, but this is a different issue, you can face of after resolving the first one (startup the instance).


DISTINCT column combination with permutations My postgresql column structure looks like this:

id  | from | to
---------------
1   |  A   |  B
2   |  A   |  B
3   |  C   |  D

Now I want an result which looks like this:

 res
-----
'A:B'
'B:A'
'C:D'
'D:C'

where the first and the rows are permuted from A:B to B:A and 'C:D' to 'D:C' and the second column is omitted due to distinct operation.

This looks more like a stack overflow question yet Union will give a distinct combination.

SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table


Improving row estimates for query with jsonb_object_keys() I have a query that looks something like this:
SELECT jsonb_object_keys(json) AS key,
       json
FROM (
       SELECT json_object -> 'some_key' AS json
       FROM my_json_table
     ) j;

The planner currently estimates 100 rows to be returned by the jsonb_object_keys call for each from the inner query. In my case, it's 2. Since this underlies some larger queries in my database, it's critical that I get a better row estimate.
I would like to avoid creating a materialized view. Is there another approach to improve the query plan?

A super-user can do:
alter function jsonb_object_keys ( jsonb) rows 2;

But this will change the estimate for all uses of jsonb_object_keys in that database, not just that one use of it.
If you don't like that, you could duplicate the function and change the duplicate:
CREATE OR REPLACE FUNCTION jsonb_object_keys2(jsonb)
 RETURNS SETOF text
 LANGUAGE internal
 IMMUTABLE PARALLEL SAFE STRICT ROWS 2
AS $function$jsonb_object_keys$function$;


How does Couchdb calculates the document revision number? Whats the exact algorithm to calculate the CouchDB revision number.
CouchDB uses a deterministic algorithm to calculate the document revision number.
Here is one relevant doc I found:
https://stackoverflow.com/questions/5954864/how-does-couchdb-calculate-the-revision-number

Buts its all in elrang, I am trying to implement it in dart.
I use sample document : {"_id":"123-this-is-an-id","hello":"world","testing":123} for testing.

When I do the MD5 hash of it , its gives me : f0853833850bce08dce0896e010a375e, where as the couchdb revision number is :

"_rev": "1-43dce8fe7e9a26b49390e3727a015cc0"

I google it everywhere, but if I can find the algorithm some where in text , UI can convert it into dart.

So after some time, I got to know that couchdb calculates revision number as such:

seq_num-md5HashOfDocJsonContentWithoutRevField


Collect disk space metrics for whole SQL Server instance I am quite new to SQL Server. I would like to collect database disk space metrics for the SQL Server instance as a whole.

I have found a code sample that can collect disk space information per database.

IF OBJECT_ID('DISK.dbo.disk_activity') IS NOT NULL
    DROP TABLE disk_activity

CREATE TABLE disk_activity (
       servername NVARCHAR(100)
    ,  database_id INT PRIMARY KEY
    , name NVARCHAR(MAX)
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO DISK.dbo.disk_activity (servername, database_id, name, data_used_size, log_used_size)
    SELECT
          (SELECT @@SERVERNAME AS "Server Name") AS SERVER_NAME
        , DB_ID()
        , DB_NAME()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

However, I would like to collect the metrics across the entire server, i.e. total disk space allocated for all databases, total disk space used, total disk space free, and percentage of disk space used.

How should I modify the above script or are there other options available?

To monitor disk space usage and database file size I use this script that you can find on my blog:

-- create a temporary table to hold data from sys.master_files
IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL
    DROP TABLE #masterfiles;

CREATE TABLE #masterfiles (
    database_id int,
    type_desc varchar(10),
    name sysname,
    physical_name varchar(255),
    size_mb int,
    max_size_mb int,
    growth int,
    is_percent_growth bit,
    data_space_id int,
    data_space_name nvarchar(128) NULL,
    drive nvarchar(512),
    mbfree int
);

-- extract file information from sys.master_files
-- and correlate each file to its logical volume
INSERT INTO #masterfiles
SELECT
     mf.database_id
    ,type_desc
    ,name
    ,physical_name
    ,size_mb = size / 128
    ,max_size_mb =
        CASE
            WHEN max_size = 268435456 AND type_desc = 'LOG' THEN -1
            ELSE
                CASE
                    WHEN max_size = -1 THEN -1
                    ELSE max_size / 128
                END
        END
    ,mf.growth
    ,mf.is_percent_growth
    ,mf.data_space_id
    ,NULL
    ,d.volume_mount_point
    ,d.available_bytes / 1024 / 1024
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) AS d;

-- add an "emptyspace" column to hold empty space for each file
ALTER TABLE #masterfiles ADD emptyspace_mb int NULL;

-- iterate through all databases to calculate empty space for its files
DECLARE @name sysname;

DECLARE c CURSOR FORWARD_ONLY READ_ONLY STATIC LOCAL
FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'

OPEN c
FETCH NEXT FROM c INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @sql nvarchar(max)
    DECLARE @statement nvarchar(max)
    SET @sql = '
        UPDATE mf
        SET emptyspace_mb = size_mb - FILEPROPERTY(name,''SpaceUsed'') / 128,
            data_space_name =
                ISNULL(
                    (SELECT name FROM sys.data_spaces WHERE data_space_id = mf.data_space_id),
                    ''LOG''
                )
        FROM #masterfiles AS mf
        WHERE database_id = DB_ID();
    '
    SET @statement = 'EXEC ' + QUOTENAME(@name) + '.sys.sp_executesql @sql'
    EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql

    FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

-- create a scalar function to simulate the growth of the database in the drive's available space
IF OBJECT_ID('tempdb..calculateAvailableSpace') IS NOT NULL
    EXEC tempdb.sys.sp_executesql N'DROP FUNCTION calculateAvailableSpace'

EXEC tempdb.sys.sp_executesql N'
CREATE FUNCTION calculateAvailableSpace(
    @diskFreeSpaceMB float,
    @currentSizeMB float,
    @growth float,
    @is_percent_growth bit
)
RETURNS int
AS
BEGIN
    IF @currentSizeMB = 0
        SET @currentSizeMB = 1
    DECLARE @returnValue int = 0
    IF @is_percent_growth = 0
    BEGIN
        SET @returnValue = (@growth /128) * CAST((@diskFreeSpaceMB / (@growth / 128)) AS int)
    END
    ELSE
    BEGIN
        DECLARE @prevsize AS float = 0
        DECLARE @calcsize AS float = @currentSizeMB
        WHILE @calcsize < @diskFreeSpaceMB
        BEGIN
            SET @prevsize = @calcsize
            SET @calcsize = @calcsize + @calcsize * @growth / 100.0
        END
        SET @returnValue = @prevsize - @currentSizeMB
        IF @returnValue < 0
            SET @returnValue = 0
    END

    RETURN @returnValue
END
'

-- report database filegroups with less than 20% available space
;WITH masterfiles AS (
    SELECT *
        ,available_space =
            CASE mf.max_size_mb
                WHEN -1 THEN tempdb.dbo.calculateAvailableSpace(mbfree, size_mb, growth, is_percent_growth)
                ELSE max_size_mb - size_mb
            END
            + emptyspace_mb
    FROM #masterfiles AS mf
),
spaces AS (
    SELECT
         DB_NAME(database_id) AS database_name
        ,data_space_name
        ,type_desc
        ,SUM(size_mb) AS size_mb
        ,SUM(available_space) AS available_space_mb
        ,SUM(available_space) * 100 /
            CASE SUM(size_mb)
                WHEN 0 THEN 1
                ELSE SUM(size_mb)
            END AS available_space_percent
    FROM masterfiles
    GROUP BY DB_NAME(database_id)
        ,data_space_name
        ,type_desc
)
SELECT *
FROM spaces
WHERE available_space_percent < 20
ORDER BY available_space_percent ASC

IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL
    DROP TABLE #masterfiles;

IF OBJECT_ID('tempdb..calculateAvailableSpace') IS NOT NULL
    EXEC tempdb.sys.sp_executesql N'DROP FUNCTION calculateAvailableSpace'

Basically, it uses sys.master_files to query the size of all the database files and sys.dm_os_volume_stats to query the drive info. FILEPROPERTY returns information on the available space inside the file.

The script there was meant for alerting when available free space falls under a threshold (say 20%), but it can be modified to perform regular monitoring and record the information in a centralized table.

Don't overlook what the Data Collector feature can record in the Management Data Warehouse database. The feature is available in all editions of SQL Server (express excluded for the lack of an agent to schedule the collection). If the reports you have there are not enough, you could query the tables directly (you just need to query [snapshots].[disk_usage] in the MDW database).

In particular, if you're on Enterprise Edition, the Utility Control Point feature focuses on resource utilization with a "holistic" approach. Again, it provides some reports and a dashboard to see resource utilization across the whole server infrastructure or the single server.

In my experience, Data Collector and Utility Control Point are the most underused features in SQL Server.


Backing entire Postgres database server up with pg_basebackup I back Postgres DB 9.1 up using following command:

pg_basebackup -h 127.0.0.1 -U replicationuser -D /storage/test_backup -Ft -z -P

In postgresql.conf I have this set:

wal_level = hot_standby

However, at the end of pg_basebackup run I get this warning:

  NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

I care about the backup of data from time of starting pg_basebackup only (I can afford a bit of downtime in the night, so I shut down the app server that is using this PG instance as backend DB and then start the backup). I do not need nor aim to have "continuous backup".

Question: do I really have to copy the WAL to have complete backup from the time of starting pg_basebackup command? Or is that warning designed strictly for purpose of achieving "continuous archive" functionality?

Question: do I really have to copy the WAL to have complete backup from the time of starting pg_basebackup command?

Yes, you do.

The DB needs current WAL to start up from the base backup.

You can use pg_basebackup -X stream on 9.3 and above to copy all the required WAL along with the initial basebackup.


MYSQL Password Settings Is there a command that I could use to check the password settings for MySQL without looking into each account? I'm trying to get password max length, min length, lockout threshold, password history, expiration date and etc.

I'm using MySQL 5.1.50.

The password is encrypted via a one-way algorithm.  You cannot discover the length or contents (numbers, punctuation, etc) of passwords once they are stored.

Password rules need to be enforced when they are first created, even before touching MySQL.

If you want expiration rules that are easily configured and maintained, upgrade to 5.7.  (Anyway 5.1 is antique by now!)


MySQL : Is there a command similar to FLUSH STATUS to reset GLOBAL STATUS? I'm trying to run some load tests on my MySQL server and check a bunch of relevant GLOBAL STATUS variables after each run.  Is there an easy way to flush GLOBAL STATUS variables like the regular FLUSH STATUS command?

Restarting the server seems to be one way of accomplishing the same thing but I'd prefer something faster.

Try from command-line (not mysql prompt):

$ mysqladmin flush-status


PostgeSQL 9.3: Is it safe to stop VACUUM FULL? I'm using PostgreSQL 9.3 on RDS. Once in a while, I run a VACUUM FULL operation on the database. However, such operation can take quite a while and it blocks other tables, so the need to stop the operation might arise.

Is it safe to stop a VACUUM FULL operation that takes too long?

Yes, it is safe. Vacuum full will recreate the tables as new objects and only when they're done they will be put to use. If you cancel it, the new files not yet put to use will be removed and old ones will be kept.

Of course if you would cancel it by killing the process handling the vacuum there would be files left, but still the database would be intact.


Trigger to restrict users from creating a new table in the database How do I create a SQL Server trigger that will restrict any users from creating a new table in the database?

Please help me with this.

Thanks & Regards,
Ritesh.

If you really must use a trigger you can use a database trigger.

CREATE TRIGGER NoCreateTable ON DATABASE
    FOR CREATE_TABLE
AS
PRINT 'You may not create a table'
ROLLBACK TRANSACTION


How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on? In the Books Online for Reorganize and Rebuild Indexes Microsoft mentions:

  Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation.

What magnitude of number of rows or number of data pages is considered around the cutoff of where it's worth considering index reorganize or index rebuild operations?

Also "reducing fragmentation" is not per se a performance goal.  On many (most?) modern storage platforms there is little difference between sequential and random IO, which is a major historical reason for defragmenting.

I've worked on systems where the difference in throughput between sequential and random IO was 10x or more.  As SQL Server attempts to scan a fragmented index, the physical file locations of the linked-list of pages jumps around every few extents, reducing the IO size, randomizing the IO, preventing read-ahead IO, and eliminating the benefit of speculative reads into the SAN controller cache.

On modern systems, the storage either has lots of spindles, or is flash-based.  Both of these reduce the performance differential between small, random IOs and large, sequential IOs.

Also if tables are heavily cached, the benefit of defragmenting their storage on disk diminishes.  And small rowstore tables will typically be cached.


Can a database's files be copied while a database is online? I'm working on setting up a development copy of a production database on SQL Server 2008 R2 SP1. The live database is getting lightly used by two developers for read only queries currently, but the new database will have updates done on it as well.

Since the database is 2.1TB and took a total of 3 days to restore and update to the latest build we need for testing, my original plan was to create a new set of backup files and then restore from those files. This would allow me to create the development copy of the database on the same SQL instance and machine, without having to take the current database offline.

However, in order to save a couple of those days, I was thinking that it might be a good idea to just copy over the physical database files and attach the new copy of the database. Unfortunately when I try to copy I get an error referring to the lock that SQL Server puts on those files.

Since I can't take the database offline for anything but transferring the log files (I can finish this before people get in in the morning), is there any way I can copy the live database files without putting the database into an offline state? Or should I wait until people go home to do that?

Why does it take 3 days to backup / restore a 2.1 TB database? If it is all about moving the backup, I'd suspect copying the MDF/LDF files would actually be slower since at least the backup could be compressed. And if it's just about restoring, then writing the copies of the file there shouldn't be any faster than the restore process itself - make sure you have instant file initialization enabled. Or get faster I/O on the secondary system.

Anyway, no, you can't copy the MDF/LDF files while the database is online, you'd have to take it offline to do so. And this is the absolute LEAST safe way to copy a database. If anything happens to the files while they're detached/offline, you now have zero copies of your database.

I would suggest looking into ways to make the backup/restore faster - whether it be ensuring you're compressing your backups and transferring the files in the best way possible, using the best I/O subsystem available, getting better I/O, making sure that IFI is enabled, etc.

Another option to consider is round robin log shipping - assuming production is in full recovery and you are taking regular log backups, you can perform log shipping to one database while developers work on another, and when you get to a stable point, restore with recovery and have the developers switch, taking their recent changes with them. Now re-initialize log shipping on the copy they just stopped working on, and it will be ready for them at the next "stable" cut-over, with no waiting.


Manual sql failover machine with replication I hope this is the correct place to ask this question. We recently experienced one of our virtual machines being offline for 3 days due to issues in the data center hosting it. This quickly brought us to realize that we where not well prepared for failures in our system. Since then we have moved data centers and as a precaution I have applied for a separate smaller virtual machine hosted in a different location for a backup machine.

I have drawn a very rough image of what I want to try and achieve.

So to explain what is happening here.

He have a primary sql server which websites and services access. via a dyndns name, for ex.  sql1.dyndns.org

certain tables and columns are replicated off to a group of subscribers (they are read only) and I do live transaction replication. Other services run on the subscriber machines which access the local db on that subscriber.

Now my issue comes in, say my primary sql server goes offline. All of my services depending on it stop working. hence why I want to add the backup machine. Say primary goes down. I simply go and change the IP address associated with sql1.dyndns.org and then all services should be able to continue running. However I then still need my replication to the subscribers from my secondary machine. As data which is inserted into that database needs to be sent off to the subscribers immediately.

So my questions:

Can I do what I have described using the standard sql server methods and technologies which I have mentioned.
Is there maybe a better way to achieve what I am looking for? Am I over-complicating it?
What technology should I use for the purple link (replication / live mirror etc)
After my primary is back online, how would I swap back over to it without loosing the changes in data?

Yes, if you are using Peer2Peer replication between the primary and secondary servers which is an enterprise only feature
I would personally use Mirroring/AlwaysOn for redundancy between primary and secondary sites as this includes ways to failover without changing the DNS records. Either by adding ';Failover Partner=PartnerServerName;' to the connection string or by using Availability groups with multisite failover
Mirroring or AlwaysOn, depending on your SQL Server version - or Peer2Peer replication.
Using Mirroring or Availability groups this will in most cases simply by achieved by bringing the server online and the service will automatically send all the changes over and then you can manually failover.


Select from two tables with not direct relationship OK i found myself in a dead end, and i know must be a way but my brain is just about to explode. This is the case: I have two tables with tons of rows, one for works done (lets call it works table), something like this:

ID | Home_Work_ID | task_id | Person_id
1  | 23           | 1       | 30
2  | 23           | 2       | 31
3  | 23           | 3       | 30
4  | 876          | 1       | 31
5  | 123          | 3       | 32

and another table to report the fixes to do on the works mentioned before, lets call it fixes table

ID | Home_Work_ID | Person_reporting_id | Task_id | Details
1  | 23           | 93                  | 1       | Fix this
2  | 23           | 85                  | 3       | Fix that
3  | 123          | 86                  | 3       | Fix something

As we can see, in the fixes table there are home works with fixes reported, and those home works was done by the person_id 30 and 32 (from the work table). The results that im trying to achieve would be:

Person                               | Fixes
John (lets say this is person id 30) | 2
Kate (lets say this is person id 32) | 1

The problem is, i need to create a report that show me who was the person responsable of the work done in the works table that have reported fixes in the second table and how many fixes was reported for that person. The only link is the home work id and probably the task id, so i was testing something like this

SELECT P.person_name AS Person, COUNT(F.id) AS fixes
FROM fixes F
INNER JOIN homeworks H ON F.home_work_id = H.id
INNER JOIN works as W
INNER JOIN people AS P ON W.person_id = P.id
INNER JOIN tasks AS T ON T.task_id = F.task_id
WHERE F.task_id = W.task_id
AND F.home_work_id = W.home_work_id
AND W.home_work_id IN (SELECT home_work_id FROM fixes GROUP BY home_work_id)
GROUP BY P.person_name ORDER BY fixes DESC

Ok there are three more inner/left joinable tables with the id and name of the person, home work and task. This show me the person responsable but the number of fixes for that person and that home_work/task dont match the ones in the fixes table, i guess im doing wrong inner joining the work table that way but i dont see the light yet. Any help will be really appreciated.

Regards

The COUNT is too high, correct?  That is because the JOINs get more records before the GROUP BY comes along.  I suggest you remove the ORDER BY and SELECT more fields so you can see which rows are coming redundantly.  That may lead to splitting it into

SELECT ...
FROM ( SELECT person, COUNT(...) FROM ... JOIN ... ) X
JOIN some other tables

AND W.home_work_id IN (SELECT home_work_id FROM fixes GROUP BY home_work_id)

optimizes very poorly; change it to yet another JOIN.  Why do you have the GROUP BY there?  Perhaps you meant DISTINCT?  Perhaps in this case, do

JOIN ( SELECT DISTINCT home_work_id FROM fixes ) Q ON Q.home_work_id = W.home_work_id


regex character class :print: in postgres I am trying to replicate an Oracle check constraint that prevents non-printing characters in postgres, and I am stumped. I think postgres may be treating :print: and :ascii: as synonymys?!?

select REGEXP_REPLACE('bla', '[^[:print:]]', '[X]','g') ;
 regexp_replace
----------------
 bla
(1 row)

select REGEXP_REPLACE('bla'||chr(10)||'bla', '[^[:print:]]', '[X]','g') ;
 regexp_replace
----------------
 bla[X]bla
(1 row)

select REGEXP_REPLACE('Ҕ', '[^[:print:]]', '[X]','g') ;
 regexp_replace
----------------
 [X]
(1 row)

select REGEXP_REPLACE('ñino', '[^[:print:]]', '[X]','g') ;
 regexp_replace
----------------
 [X]ino
(1 row)

Why would Ҕ and ñ be caught in that?

Any guidance is appreciated.

It is the LC_CTYPE setting that defines character classification, case conversion, and other character attributes. So this is what determines if a character is printable or not.

ñ is not printable in the C locale:

SHOW lc_ctype;

 lc_ctype
----------
 C
(1 row)

select regexp_replace('niño', '[^[:print:]]', '[X]','g');

 regexp_replace
----------------
 ni[X]o
(1 row)

But it is printable in de_DE.utf8:

SHOW lc_ctype;

  lc_ctype
------------
 de_DE.utf8
(1 row)

select regexp_replace('niño', '[^[:print:]]', '[X]','g');

 regexp_replace
----------------
 niño
(1 row)

If you don't want to change the lc_ctype of the database (which can only be done with dump/restore), you can also use the COLLATE clause to give a string expression a different collation (and character type):

select regexp_replace('niño' COLLATE "en_US.utf8", '[^[:print:]]', '[X]','g');

 regexp_replace
----------------
 niño
(1 row)

You see that ñ is also recognized as a printable character in the English UNICODE collation, but you can also use the language independent ICU collation if you have built PostgreSQL with ICU support:

select regexp_replace('niño' COLLATE "und-x-icu", '[^[:print:]]', '[X]','g');

 regexp_replace
----------------
 niño
(1 row)


Recommendation for free event monitoring software I'm seeking recommendations for free / open event monitoring tools. HPOV costs continue to multiply like so many wet gremlins, and we've had enough.

There are some interesting suggestions in this article and the subsequent comments, but before I set about trying many of these I'm sure you all have found a few that you like that may or may not be in this list. There is also this thread, but we're really shooting for a no-cost solution.

The primary requirements are:

Perform a basic ping test
Check the up-state of the SQL Server service.
Send an email alert if 1 or 2 fail.
Works as far back as Sql Server 2005 - present. (This is where PBM comes up a bit short)

Additional desired-not-required functionality would be:

Ability to pass a synthetic transaction, which if failed triggers an alert.
Ability to measure said transaction for duration, not just completion, and alert if a threshold is exceeded.
Alert on other measurable (logged) events.

Thanks much for any recommendations!

Edit: Currently investigating SpiceWorks

Polymon from codeplex is ideal for my uses.  I'm monitoring not just server availability, but sql agent jobs success, a host of perfmon data, SQL locking/blocking, db and filesystem freespace, and a whole lot more.

It has a very nice Powershell interface, so any Powershell script you can imagine, it will run on the schedule you specify and then alert/notify/take action as you direct.

It requires a SQL2005 or greater instance to hold its db, but it can monitor any version of SQL.  (Using Powershell, I even have it monitoring CACHE on AIX.)

No cost besides having an available SQL 2005 server to host the db.


SQL Server Licensing for training, small scale development I am currently undertaking SQL SERVER 2012 Training for the exam 70-461. I currently have SQL SERVER 2008 running on my laptop but I'm looking to upgrade to 2012 and I'm having trouble trying to get together the licencing information needed to purchase what I need. I'm running Windows 7 (64 bit) and want to run the database on the laptop itself.
They have asked me:
·        SQL Server Edition. Standard or Enterprise? Standard
·        Licencing Model. Core or processor?
·        If ‘Processor’ do you need CAL licenses quoted? (CAL =Client Access License)
·        If ‘Core’ how many cores?
Thanks

What I would do in your case is the following:

Download HyperV and install on your machine, details here: https://blogs.msdn.microsoft.com/virtual_pc_guy/2010/07/07/installing-windows-virtual-pc-on-windows-7-home-editions/
Download Windows Server 2016 evaluation edition here:
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016
Set up a Virtual Machine using HyperV (this is a good skill to have anyway and is really easy to learn. Search online for a tutorial).
Install the Windows Server 2016 on the VM.
Download SQL Server 2016 developer edition.
Install Developer Edition on your new VM with Server 2016 and SQL Server 2016 and enjoy the benefits of the latest release of both products.

Remember afterwards that there is a bit of firewall configuration if you want to install SSMS on your local PC and connect to your SQL Server 2016 VM (also look this up on the web). SSMS is compatible with Windows 7 still, so you are OK to install locally and connect it to your SQL Server 2016 VM.

You may also require quite a bit of RAM etc for this approach. I had a 2011 laptop with 16GB of RAM and a SSD drive running 5 VMs on HyperV a while ago and it ran OK. Your mileage will be different depending on your PC specs.


Total hours worked per employee per day I have a table like this:
SQLfiddle: http://www.sqlfiddle.com/#!9/340c8

CREATE TABLE Table1
(ID int, empid int, time datetime, state int);

+--------------+---------------------+-----------------+
|    empid     |       time          |         state   |
+--------------+---------------------+-----------------+
|            1 | 2011-08-16 14:59:08 | 0               |
|            1 | 2011-08-16 15:00:06 | 1               |
|            1 | 2011-08-16 15:14:51 | 0               |
|            2 | 2011-08-16 15:15:00 | 0               |
|            1 | 2011-08-16 15:59:01 | 1               |
|            2 | 2011-08-16 15:59:09 | 1               |
+--------------+---------------------+-----------------+

Where 0 means login and 1 logout.

I am trying to get the total hours worked per employee per day:

1      2011-08-16     total hours worked    08:32:00

With my query:

SELECT CONCAT(
    MOD(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
    MIN(CASE WHEN state =    '0' THEN time END)), 24 ), ' hours ',
    MINUTE(TIMEDIFF(MAX(CASE WHEN state = '1' THEN time END),
    MIN(CASE WHEN state    = '0' THEN time END))), ' minutes') as HoursWorked,
    empid,
    Date(time)
FROM emplog T
GROUP BY empid, Date(time)

I get the time worked per day, but only for the min max in out times so I am missing all employees that come in and out several times per day. Any ideas?

If we assume that login and logout always comes in pairs, and that login and logot are always on the same date, it's just a matter of finding the smallest time for a logout after each login. If no logout is found it means that the employee is still working so now() (if the report is run at a later date, use  23:59:59 for the date of login) is used instead:

select empid, date(time) as work_dt, time as login
    , coalesce(
          (select min(time)
           from emplog as b
           where a.empid = b.empid
             and date(a.time) = date(b.time)
             and b.time >= a.time
             and b.state = 1
          ), now()) as logout
from emplog as a
where a.state = 0

;

Now all that remains is to sum the difference between logout and login for each employee and work_dt:

select empid, work_dt,
        SEC_TO_TIME(sum(TIMESTAMPDIFF(SECOND,login,logout))) as time_worked
from (
    select empid, date(time) as work_dt, time as login
        , coalesce(
              (select min(time)
               from emplog as b
               where a.empid = b.empid
                 and date(a.time) = date(b.time)
                 and b.time >= a.time
                 and b.state = 1
              ), now()) as logout
    from emplog as a
    where a.state = 0
) as t
group by empid, work_dt
;


mysqlcheck --repair a specific table? I logged in my server via SSH and committed a "repair table xxx" query on a MyISAM table with 34 million rows and 69GB of data. Unfortunately the SSH connection was lost. When I logged back in I found the mysql -u xxx -p session was gone by 'ps a'. The disk mount is now fully occupied and is not released. So I suppose a repair again would correct all this?

I want to use cron job to run this remotely rather than from SSH which can possibly be disconnected again. After finding this page:

http://dev.mysql.com/doc/refman/5.0/en/rebuilding-tables.html

I see it's possible to run repair table via command line like this:

mysqlcheck --repair --databases db_name ...

But how can I just --repair a specific table in the database db_name? Any other tips regarding how I can correct this mess would be much appreciated!

Using screen or tmux to handle this task will mean that if the SSH connection is lost the session will continue. When you log back in you will find the mysql -u xxx -p session is still active.

nohup works too but "screen and tmux are a sysadmin's best friends" as jynus commented.


Schannell: Certificate received from the remote server was issued by an untrusted certificate authority i'm not sure whether to ask this on ServerFault or here.

Periodically following error is logged in Windows Server 2008 Administrative events:

  The certificate received from the remote server was issued by an
  untrusted certificate authority. Because of this, none of the data
  contained in the certificate can be validated. The SSL connection
  request has failed. The attached data contains the server certificate.

Log Name: System
Source: Schannel

Followed by:

  The following fatal alert was generated: 48. The internal error state
  is 552.

Log Name: System
Source: Schannel

If i change the Force Encryption property in SQL-Server Configuration from "No" to "Yes"(see image below) the error would not be logged anymore.

But i don't really need SSL-Encryption since all connections are trusted(from intranet) and ports 1333 and 1334 are firewalled from internet.

Would it be a performance impact if i would force encryption and generate a server certificate, is it recommended at all in my situation? I don't want to enforce encryption only to prevent from some event logs if it not even causes serious connection problems(where can i see which connection has caused it?).

Q: Can somebody please explain why these errors are raised and wherefrom?

You're getting those errors because something is trying to connect to SQL Server via SSL, and you don't have a real (signed) certificate trusted by all parties involved.

By setting "Force Encryption" to Yes, you won't make the errors go away - actually, the opposite. You'll make the errors happen everywhere, because you're not fixing the root problem. Right now, only a few things are trying to connect via SSL, but you're about to make EVERYTHING connect via SSL, which means everything will throw those errors. Bad idea.

Instead, track down what's trying to connect via SSL. If it's happening at the same date/time every time, you can set up a trace, or figure out what servers are running batch jobs. Then, THAT server is the one that needs a real cert (as well as the SQL Server) - or just change that application's settings so that it's not trying to connect with SSL.


How to handle concurrency during an INSERT-SELECT cycle? The application should limit each user's access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table:

CREATE TABLE accesses (
    user_id         INTEGER NOT NULL,
    dt              TIMESTAMPZ
);

Each time the resource is accessed, we record it in the table. However, we also need to check if the access limit was reached. To that end, we need to execute this query:

SELECT count(*)
FROM accesses
WHERE dt >= timezone('utc', now()) - interval '24 hours' AND user_id = $1

and we need to check the result against the user limit.

Now, if two accesses are requested at the same time we may have problems:

if we first read, compare, and the insert we may grant more accesses than the limit;
if we first insert, then read and compare, we may disallow legitimate accesses.

The two ways that I know are to either use a SERIALIZABLE transaction or SELECT ... FOR UPDATE. I am not sure if the latter actually works in this case, because we will not update the rows that are read, rather we will insert new ones. So I think that it'll be a useless lock and the result will probably be still incorrect.

However, with a SERIALIZABLE transaction I have retry it in case it fails.

What should be done in this case, to ensure proper access limits?

Not sure if that's the best approach, but it seems to be a quick and reliable as long as application[s] consistently uses it.
Create a new table, access_attempt (user_id integer not null primary key).
Modify you workflow :

begin ;
insert into attempt_access (user_id) values (:user_id);
-- do you verification, raise an error if needed
insert into access (user_id, dt) values(:user_id, current_timestamp) ;
delete from access_attempt where user_id = :user_id;
commit;

A brief description.  First of all, a new transaction is started and we insert a user_id
 into the new table attempt_access which has just one column. Until transaction completes , other transactions will not be able to insert row with the same user_id and will wait until the first one complete.  Then verification is performed, record insert into access table, and finally the record inserted in the very beginning is removed. Now transaction can be committed . If transaction is rolled back the record in attempt_access will be removed automatically .