PostgreSQL vagrant VM cannot create extension I had some problems during installation of PostgreSQL and I eventually decided to go for a VM solution managed with vagrant, as explained in postgresql wiki.
(At the beginning my motivation was just to solve the installation issue but I find that a virtual machine solution has many advantages).
I started working with it and everything looked very good. But eventually I wanted to use crosstab(doc), however tablefunc extension was not installed in the virtual machine, and I can't find a way to install it.
The virtual machine creates a user for the application when you run it first time, which is fine, and it creates a database for the application as well. But if I try to install the extension with the application user
create extension tablefunc;

I get the following error (anyhow expected):

ERROR: permission denied to create extension "tablefunc"
SQL state: 42501
Hint: Must be superuser to create this extension.

The point is that I don't have the password for postgres user, so I cannot install the extension using pgAdmin. (This is already an issue that scares me a bit, if I have other problems and I need the administrator. But I've been browsing around and haven't found any comment about this.)
In postgres wiki, link above, it describes a way to connect to the VM as superuser, which I also tried,
vagrant ssh
sudo su - postgres

but again when I tried to run the command for the installation I was asked for the password of postgres user in the database. For example, one of the trials:
postgres@postgresql:~$ psql -h localhost -U postgres -d vic -c 'create extension tablefunc'
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"

My question is then, if there is a way to install the extension for the vagrant VM (or anyway to run any command with the superuser). Also, I'll be much happier having the superuser password, so that I could manage everything from pgAdmin.
Many thanks for reading and for your help.

postgres has no password after an automated install, it's expected to authenticate through the peer method.  sudo su - postgres is the first step to it. The second step is to connect through the Unix local domain socket, but your command doesn't do that, it connects through TCP, that's why you're stuck with the password problem.

The installed pg_hba.conf file probably starts with these two rules, apart from comments:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
[...]

When doing a TCP connection with -h localhost... the first rule doesn't match. The second rule matches and it triggers the demand for a password, but as none was set this can only fail (empty passwords are not allowed either).

The solution is to remove -h localhost from psql invocation, for the first rule to be taken instead. As it's on a Unix system, it will attempt a connection through the Unix domain socket.

Once logged to psql as the postgres user, you may set a password with the \password command or ALTER USER postgres PASSWORD 'foobar';

See also https://help.ubuntu.com/community/PostgreSQL
In the section Using pgAdmin III GUI , they suggest to change the pg_hba.conf first rule from auth peer to md5, after having set a password. Personally I don't quite get why. I'd rather leave that rule alone and run pgAdmin from my own Unix account, choosing localhost TCP connections.


Generate hierarchical path I have a classstructure table:

create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0));

insert into classstructure(classstructureid, classificationid, parent) values(1001, 'FLEET', null);
insert into classstructure(classstructureid, classificationid, parent) values(1002, 'LIGHTDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1004, 'MEDIUMDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1022, 'ACTIVETRANSPORTATION', null);
insert into classstructure(classstructureid, classificationid, parent) values(1023, 'FACILITYWALKWAY', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1024, 'TRAIL', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1085, 'SIDEWALK', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1091, 'SDWRAMP', 1085);
commit;

select * from classstructure;

CLASSSTRUCTUREID CLASSIFICATIONID              PARENT
---------------- ------------------------- ----------
            1001 FLEET
            1002 LIGHTDUTYVEHICLE                1001
            1004 MEDIUMDUTYVEHICLE               1001

            1022 ACTIVETRANSPORTATION
            1023 FACILITYWALKWAY                 1022
            1024 TRAIL                           1022
            1085 SIDEWALK                        1022
            1091 SDWRAMP                         1085

I would like to collapse the records down to hierarchy paths:

HIERARCHYPATH
---------------------------
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE

ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

How can I do this?

See if this helps.

SQL> select ltrim(sys_connect_by_path(classificationid, ' \ '), ' \ ') hierarchypath
  2  from classstructure
  3  where parent is not null
  4  connect by prior classstructureid = parent
  5  start with parent is null;

HIERARCHYPATH
--------------------------------------------------------------------------------
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE
ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

6 rows selected.

SQL>


Using a real certificate with rethinkdb cert chain bundle I know the rethinkdb guide uses a self signed cert as an example. If I wanted to use a real certificate that I purchased, how can I add the bundle to the server conf? I add the certificate I purchased and key to the config:

driver-tls-key=/etc/ssl/star.cert.key
driver-tls-cert=/etc/ssl/star.cert.crt

Openssl s_client gives me the following

Verify return code: 21 (unable to verify the first certificate)

With this as the certificate chain:

depth=0 OU = Domain Control Validated, OU = PositiveSSL Wildcard, CN = *.s0nr.co
verify error:num=20:unable to get local issuer certificate
verify return:1
depth=0 OU = Domain Control Validated, OU = PositiveSSL Wildcard, CN = *.s0nr.co
verify error:num=21:unable to verify the first certificate
verify return:1
---
Certificate chain
 0 s:/OU=Domain Control Validated/OU=PositiveSSL Wildcard/CN=*.s0nr.co
   i:/C=GB/ST=Greater Manchester/L=Salford/O=COMODO CA Limited/CN=COMODO RSA Domain Validation Secure Server CA

How can I use this cert properly?

Silly me. There was --driver-tls-ca option that I was missing. I found it in the rethinkdb manual (man rethinkdb).

TLS options:

  --http-tls-key key_filename                 private key to use for web
                                              administration console TLS
  --http-tls-cert cert_filename               certificate to use for web
                                              administration console TLS
  --driver-tls-key key_filename               private key to use for client driver
                                              connection TLS
  --driver-tls-cert cert_filename             certificate to use for client driver
                                              connection TLS
  --driver-tls-ca ca_filename                 CA certificate bundle used to verify
                                              client certificates; TLS client
                                              authentication disabled if omitted
  --cluster-tls-key key_filename              private key to use for intra-cluster
                                              connection TLS
  --cluster-tls-cert cert_filename            certificate to use for intra-cluster
                                              connection TLS
  --cluster-tls-ca ca_filename                CA certificate bundle used to verify
                                              cluster peer certificates

I set it in my rethinkdb instance conf file:

# TLS stuff
driver-tls-key=/etc/ssl/star.cert.key
driver-tls-cert=/etc/ssl/star.cert.crt
driver-tls-ca=/etc/ssl/star.cert.ca-bundle

And everything works as expected. openssl s_client returns the proper 0 (ok) code.

Edit note:
Although with the rethinkdb dump utility it looks like there is no ca option so I can't use a real cert anyway.


SQL Server does not failback to the primary node We are running SQL Server 2008 R2 on a Windows Server 2008 R2 two-node cluster.

My choice of machine is primary, and the secondary is just a low-end machine for contingency planing.

We had a failover the last day during night time and when I came to the office the next morning, I noticed that SQL Server service did not failback to the primary machine although it came back online.

All settings seems to be in place:

Attachment 1 shows the general tab of SQL Server service properties dialog box from Server Manager - > Failover Cluster Manager. Both machines are checked, primary being at the top.

Attachment 2 shows failover tab of the same dialog box. Immediate failback is activated.

How can I modify these settings so that whenever the primary comes back, system will failback onto it.

I set up a virtual lab environment simulating a clustered network where I unticked one of the nodes as the preferred owner. I can confirm that whenever the ticked node goes offline, the unticked one comes into play and the system failbacks onto the ticked node once it comes online again.


Necessary to include clustered index columns in non-clustered indexes? Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index?

In other words if the Products table contains a clustered index on ProductID, when creating a non-clustered index where it would be advisable to include the ProductID column, is it necessary to nonetheless add it as a column?

If not, are there scenarios where it would be good to add the column name to the non-clustered index?

In SQL Server the clustered index key column(s) are always added in to the non clustered index to act as a row locator (Ref: More About Nonclustered Index Keys).

For an NCI declared as unique they are added as an included column otherwise they are added to the end of the key.

You might want to add the columns in explicitly if the default placement is not optimum for your queries. For example if you want to control the ASC/DESC direction or you want to control the position of the key columns in the index.

CREATE TABLE T
(
A INT,
B INT,
C INT ,
PRIMARY KEY CLUSTERED (B DESC, C DESC)
)

/*Implicitly adds B DESC, C DESC to end of key*/
CREATE NONCLUSTERED INDEX ix1 ON T(A ASC)

/*No sort operation*/
SELECT  *
FROM T
ORDER BY A ASC,B DESC, C DESC

/*
But the avove index won't be able to seek into A,C
and will need a residual predicate after seeking into A.

For the following query
*/

SELECT  *
FROM T
WHERE A=1 AND C > 4
ORDER BY C ASC, B DESC

/*This index explicitly controlling the key column position
  and direction would be better*/
CREATE NONCLUSTERED INDEX ix2 ON T(A ASC, C ASC, B DESC)


Disallow superusers and single-user mode in POSTGRESQL I have a doubt about superusers and single-user mode.
I want to restrict the access to a certain database, in a way that only those users which I gave privileges can see the structure of my database. To achieve that, I had to erase the superuser, since it bypasses all permission checks. But then I found that if I have access to the computer, I can enter in "single-user mode" and grant superuser privileges or even creating new users.
My question
Can I disallow permanently the use of superusers and single-user mode?

You will find that you need superuser access for certain tasks, e.g. CREATE EXTENSION.

Vieving database metadata is not a security problem. Just because you know that there is a column with credit card data doesn't get you any closer to accessing it.

There is no way to keep somebody with shell access as PostgreSQL OS user from doing anything they want. Whoever can read the data files has access to all information.

Rather than spending effort on breaking PostgreSQL, invest in proper security:

Restrict shell access to the database machine.
Don't allow remote superuser access.
Encrypt connections.
Use an external, centralized authentication provider.
Grant object permissions as restrictive as possible.


MySQL tuning (my.cnf) for very large tables and ad hoc queries We are an email marketing company and recently switched our setup to MySQL. We need to configure mysql (my.cnf) for extreme performance.

We have tried to configure my.cnf but heavy queries can get stuck for many many hours without any result and same queries take one or two hours to complete on sqlserver (8GB Ram).

It's a dedicated MySQL server.

OS = ubuntu
Installed Ram = 512GB
Table type = Myisam

We have very large tables, (few tables having more then 70 million rows), almost every time new tables to match so indexes wont help that much, although there exist necessary indexes.

We do not have any application to connect our database, just 2 users who writes ad hoc queries directly.

Below are some variables we have changed from the default values.

key_buffer_size= 250G
read_buffer_size=2G
read_rnd_buffer_size=50M
join_buffer_size=25M
tmp_table_size = 5G
sort_buffer_size = 2G
innodb_buffer_pool_size = 2G
query_cache_limit       = 10M
query_cache_size        = 100M
max_connections        = 20

Below is the mysqltuner recommendation

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1909G (Tables: 940)
[--] Data in InnoDB tables: 2G (Tables: 3)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6m 59s (9 q [0.021 qps], 6 conn, TX: 10K, RX: 625)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 252.1G global + 4.1G per thread (20 max threads)
[OK] Maximum possible memory usage: 333.6G (66% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 5% (1/20)
[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 83% (1 created / 6 connections)
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 1% (18/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 2.2G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 10M, or use smaller result sets)
    innodb_buffer_pool_size (>= 2G)

Any help would be greatly appreciated.

very large tables more then 70Million to query

If you have more than 70 million tables then you need to get a big stick and apply it repeatedly to the person responsible for this.

There's lots of things you do to improve the performance a database (and ignoring this line, some small changes could massively increase the performance of a sensible schema) - but until you sort the number of tables you are wasting your time.


Query for highest version I have the following table (stripped to the bare essentials):
(script is in postgresql)

CREATE TABLE t_version (
  name VARCHAR(64) NOT NULL,
  major INTEGER NOT NULL,
  minor INTEGER NOT NULL,
  attr VARCHAR(64)
);

ALTER TABLE t_version
  ADD CONSTRAINT PK_VERSION
  PRIMARY KEY (name, major, minor);

And some test data:

INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 1, 0, 'a1');
INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 1, 1, 'a2');
INSERT INTO t_version (name, major, minor, attr) VALUES ('n1', 2, 0, 'a3');

I am trying to figure out, how to select the highest version from this, i.e. a select which would give me

'n1', 2, 0, 'a3'

as a result.

I know how to do that, when only the highest minor version is required:

SELECT A.name, A.major, A.minor, A.attr
  FROM t_version AS A
    JOIN (
           SELECT name, major,
             max(minor) AS minor
           FROM t_version
           GROUP BY name, major
         ) LATEST
      ON A.name = LATEST.name
         AND A.major = LATEST.major
         AND A.minor = LATEST.minor;

But I am at a loss for the more general problem.

BTW, the solution should not only work for postgresql by for sql server too.

greatest-n-per-group problems are usually solved using window functions.

The following is standard SQL and will work on SQL Server as well:

select name, major, minor, attr
from (
  select name, major, minor, attr,
         row_number() over (partition by name order by major desc, minor desc) as rn
  from t_version
) t
where rn = 1
order by name;

For Postgres it's typically faster to use distinct on () instead:

select distinct on (name) name, major, minor, attr
from t_version
order by name, major desc, minor desc;


Index that is not used, yet influences query I've got a PostgreSQL 9.3 table with some numbers and some additional data:

CREATE TABLE mytable (
    myid BIGINT,
    somedata BYTEA
)

This table currently has about 10M records and takes 1GB of disk space. myid are not consecutive.

I want to compute how many rows are in every block of 100000 consecutive numbers:

SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;

This returns about 3500 rows.

I noticed that existence of a certain index significantly speeds up this query even though the query plan does not mention it at all. The query plan without the index:

db=> EXPLAIN (ANALYZE TRUE, VERBOSE TRUE) SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1636639.92..1709958.65 rows=496942 width=8) (actual time=6783.763..8888.841 rows=3460 loops=1)
   Output: ((myid / 100000)), count(*)
   ->  Sort  (cost=1636639.92..1659008.91 rows=8947594 width=8) (actual time=6783.752..8005.831 rows=8947557 loops=1)
         Output: ((myid / 100000))
         Sort Key: ((mytable.myid / 100000))
         Sort Method: external merge  Disk: 157440kB
         ->  Seq Scan on public.mytable  (cost=0.00..236506.92 rows=8947594 width=8) (actual time=0.020..1674.838 rows=8947557 loops=1)
               Output: (myid / 100000)
 Total runtime: 8914.780 ms
(9 rows)

The index:

db=> CREATE INDEX myindex ON mytable ((myid/100000));
db=> VACUUM ANALYZE;

The new query plan:

db=> EXPLAIN (ANALYZE TRUE, VERBOSE TRUE) SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=281242.99..281285.97 rows=3439 width=8) (actual time=3190.189..3190.800 rows=3460 loops=1)
   Output: ((myid / 100000)), count(*)
   ->  Seq Scan on public.mytable  (cost=0.00..236505.56 rows=8947485 width=8) (actual time=0.026..1659.571 rows=8947557 loops=1)
         Output: (myid / 100000)
 Total runtime: 3190.975 ms
(5 rows)

So, the query plans and the runtimes differ significantly (almost three times) but neither mention the index. This behavior is perfectly reproducible on my dev machine: I went through several cycles of dropping the index, testing the query several times, recreating the index, again testing the query several times. What's happening here?

When you create an expression index, it causes PostgreSQL to gather statistics on the that expression.  With those statistics on hand, it now has an accurate estimate for the number of aggregated rows that the query will return, which leads it to make a better plan choice.

Specifically in this case, without those extra statistics it thought the hash table would be too large to fit in work_mem, so it didn't choose that method.


Oracle Hot Backup Theory After a hot backup, where all tablespaces are placed in backup mode seperately; i.e.

alter tablespace user_data start backup;

each datafile will have a different Checkpoint SCN in its header; with the equivalent redo log files which will bring it up to date.

Why doesn't this non-sync of Checkpoint SCN cause a problem when restoring and recovering these datafiles?  The "checkpoint" SCN of each datafile is different from other datafiles and probably different from the control file's recorded SCN as well; doesn't this cause a problem?

Addition

Doesn't the inconsistency of the SCN in the datafiles cause a problem when SMON checks them once we decide to restore and recover the database using the hot backup?

When in doubt ask Tom...

backup up and recovery generating extra redo
Which is good reason to use RMAN for backups rather than
scripted hot backups using begin backup/end backup.


Change level(s) of totals with GROUP BY I am interested in the totals and the details of every line, in other words, the results of the second query.

Is there an easier way to do a rollup, and just have grandtotals?
(my 'live' query has more group levels that his example...)

CREATE TABLE #TEMP (a int, b int, c int)

INSERT INTO #TEMP VALUES( 1,1,1)
INSERT INTO #TEMP VALUES( 1,1,2)
INSERT INTO #TEMP VALUES( 1,1,3)
INSERT INTO #TEMP VALUES( 1,2,1)
INSERT INTO #TEMP VALUES( 1,2,2)
INSERT INTO #TEMP VALUES( 1,2,3)
INSERT INTO #TEMP VALUES( 1,3,1)
INSERT INTO #TEMP VALUES( 1,3,2)
INSERT INTO #TEMP VALUES( 1,3,3)

SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY ROLLUP(a,b,c)

-- 2nd query
SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY ROLLUP(a,b, c)
HAVING (c is not null and b is not null) or a is null

(You don't say which DBMS, but inferring SQL Server from your temporary table name..)

If you're on a recent enough version (>=2008 ?) you can achieve the fine level of rollup control you're after with GROUPING SETS. For your 2nd query, the equivalent would be:

SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY GROUPING SETS ((a,b,c),())


SQL Server Backup Database not .bak file format I've been using SQL Server 2012 for as long as I can remember. When I do a backup it's always saved as .bak file format. However, there's this other server that has SQL Server 2012 and when I do a backup, it's not saved as .bak but just a generic 'file' file format. When I try to restore this file it works just fine. I'd just like to know why it didn't save as .bak file. In other servers that I've worked on it's always .bak. Thank you so much.

Take a look at the command that is generating the backups (it could be a job, a maintenance plan, 3rd party tool, etc.).

You can give the file any extension you like, including no extension. Windows Explorer might not know what on earth it is, but SQL Server will still be able to restore it just fine.


Proper Postgresql vacuuming practices Some tables are very dynamic in terms of additions and subtraction of records.  The application (rails 4.2) deletes a large number of records daily.  However, the size of the database to the OS - or dumped via pg_dump does not seem to go on a diet.

As a test, all records from three related tables were deleted by the application. However, when  running \d+ one table has 42MB size.

So then a manual exploration.

durc_production=# VACUUM FULL commonpassaggios;
durc_production=# VACUUM FULL posiziones;
durc_production=# VACUUM FULL passaggios;

run.  The relative times they take are coherent with the table and index sizes. But then \d+ returns the same data sizes.

I need to get the vacuum demonstrably running before configuring postgresql.conf...

Update  This question ends up as a red herring.  Craig's reply allowed to pick up on the problem.  it was not related to postgresql at all.  see subsequent comments for culprit identification.

Database size as measured by

select pg_size_pretty(pg_database_size(current_database()))

or using du at the file system level
won't usually go down when you delete records. Even after VACUUM has had time to run. Instead, when you next add new records it won't go up because the free space inside the database gets re-used.

VACUUM can sometimes shrink a table and free space to the operating system, but only if there aren't any still-needed rows near the end of the table. Just one still-needed row can prevent the compaction of a huge table. There is no automatic background defragmentation performed to stop that happening.

You can VACUUM FULL to force space to be released back to the OS. This can speed up SELECTs considerably, too. However, it will make subsequent INSERTs and UPDATEs considerably slower since they need to claim new space back from the OS again. You can mitigate that by ALTERing the FILLFACTOR on your tables and indexes to be less than 100 (percent), so there's free space within the table to re-use immediately. Also, VACUUM FULL locks the table completely while it's running, which you really don't want on a running production DB.

Now, the size of a dump is different. That does go down when you delete data. If it doesn't go down, then you didn't delete anything, you added as much data as you deleted, or the data you removed is so small that it doesn't make a significant difference to the dump size.

If you delete records then do a new dump and it doesn't change size, I'd say that:

You didn't COMMIT the delete, so it hasn't taken effect;
You added about as much data as you deleted;
You deleted it from a different copy of the database to the one you dumped

You can check all of these with things like select count(*) from table_of_interest and select pg_size_pretty(pg_total_relation_size('table_of_interest')).


pfile - client parameter file - starting an instance remotely I do not understand how we can start an Oracle instance remotely using a pfile.

If we ssh into the Oracle server and issue:

startup pfile='/path/to/pfile';`

the pfile is on the server side.

How can we actually startup a database remotely using a pfile?

The procedure is described in the Remote Startup of a Database Instance part of admin guide for BS2000/OSD (I can't find this part in the Linux docs...).

Essentially what you do is:

Create an Oracle password file for your database if you haven't already done so
Add a static registration for your instance in its listener
Set the instance parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE
Restart the listener and the database.

Once you've done that and granted sysdba to a user, you can connect to the instance using sqlplus directly from another machine

c:\foobar> sqlplus /nolog
...
SQL> connect mat/mat@SOMETHING_REMOTE as sysdba
...
SQL> startup pfile='c:\foobar\init.ora'

That last command reads the pfile from the machine where sqlplus is running, not from a directory on the server.

But I'd generally say you probably shouldn't be doing this. Have your database all use spfiles on the server. Otherwise you never know how they were started.


Adding a password authenticated user to a peer authenticated postgres setup not working I would like to add a read-only user to a postgres setup on a remote server with default peer authentication, that uses a password as an authentication method.
I create my user like this:
CREATE USER my_user WITH PASSWORD 'some_pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO my_user;

and I have added a rule to the pg_hba.conf file, stating that this particular user should be able to access the database using a password.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   my_db           my_user                                 password <- my new line
local   all             all                                     peer

And then I start psql from the command line like this:
psql -d my_db -U my_user -W

and provide 'some_pass' when prompted. This works fine locally, but on the server I get this error:
psql: FATAL:  Peer authentication failed for user "my_user"

Do I need to restart postgresql so that the changes in pg_hba.conf will take effect, or is there something else missing?  I am reluctant to restart postgresql since it is being used on a production environment.
EDIT: I mention that the server is remote, but I log into it first using ssh before accessing the database, meaning that the connection to the database is in fact local.

Your user connects from a remote client host, so the authentication type, must be host, not local which is used for local connections using named pipes.
The "password" authenticaion method should not be used either, as it sends the password in cleartext. Use md5 instead (or if your Postgres server is configured for it, scram-sha-256)
To allow remote connections you also need to put in an IP address. If you know the IP address you could add that. Using 0.0.0.0/0 would allow a connection from any IP address.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host   my_db           my_user          0.0.0.0/0               md5

If the client computer is located in the same network as the Postgres server, samenet would be a safer choice than 0.0.0.0/0
To apply changes to pg_hba.conf you need to reload the configuration. A restart is not needed. Reloading can e.g. be done through select pg_reload_conf(); while being connected as the superuser.


Eventually I realized that it was some king of caching and probably on Linux level.

After applying # sync && echo 3 | tee /proc/sys/vm/drop_caches followed by mysql> RESET QUERY CACHE; the query time became linear.

Note though, that before this the effect was stable on different queries.


The ALTER TABLE statement conflicted with the CHECK constraint I need to check datecom is less than datelivr

create table Commande
(
Numcom int identity primary key,
Datecom date,
Datelivr date,
Codecli int foreign key references clients (Codecli)
)

alter table Commande
    add constraint date_check check(datediff(day,Datecom,Datelivr) > 0)

but I get the error message:

  The ALTER TABLE statement conflicted with the CHECK constraint
  "date_check". The conflict occurred in database "tp4", table
  "dbo.Commande".

How can this happen?

The error message is self-explanatory: Your check constraint is trying to enforce that all values in Datecom are at least one day earlier than Datelivr, but you must have at least one row where this is not true - either Datecom is the same day, later, or one of the values is NULL. Find those rows using:

SELECT Numcom, Datecom, Datelivr
FROM dbo.Commade
WHERE datediff(day,Datecom,Datelivr) <= 0
  OR Datecom IS NULL
  OR Datelivr IS NULL;


How to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only I recently implemented Ola Hallengren Maintenance solution on all of my production servers(SQL server 2005,2008,2008R2 and 2012).  It works great.
My problem is how to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only.
To be specific I have 20 databases on the server.  It is required 15 databases have nightly full backup plan.  The remaining five databases should have weekly database full backup.

I scheduled the backup job for 15 databases nightly full backup. It is working great. But I don’t know how to take care the remaining five databases weekly full backup. (SQL Server 2008R2)
On native maintenance plan I used to create two plans
1.  Nightly full backup for the 15 databases and
2.  Weekly full backups for the remaining  five databases
I don’t know how to do that on ola.
Do I need to install ola Backup solution again for the five databases?  Or is there another way to do it?

I copy and past the script from ola User database job substitute my databases on the place of user databases and create backup job as follows

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MASTER -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'DB1,DB2,DB3,DB3,DB4,Db5', @Directory = N'C:\Backup2', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y'" -b

I run a test backup . It worked but I am not sure that is the right way doing it or not? could you please somebody tell me whether it is ok or not.
Thank you your help in advance.

@KASQLDBA I have attached the error screen shot

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's


How can I tell if a SQL Server database is still being used? We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.

How can I tell if they are still being used by users or a web application?

I found a forum thread which had a T-SQL query you could run to retrieve the last query date. It seems to work but I want to know if this information is valid enough to drop databases. Is it?

If you have alternative methods that would help as well.

You would have to be concerned with items that have been purged from the cache and that you have missed, or for databases that have infrequent usage.

Rather than dropping the databases out of hand put them either OFFLINE to prevent access without dropping them or in RESTRICTED_USER mode to limit access. Doing this you can leave them in that state for a month or two to check and see if there is occasional usage.

You could also look to use a server side profiler trace filtering on that database.


How can we automatically replace a table with a view for a specific user? We currently have some reports that query a specific table, but one of our users wants the ordering to be different. I don't want to go through and fix each of the reports, because that's rather a mammoth task, and only one user has a problem here. Is there a way to have SELECT queries on a base table automagically SELECT from a view table instead of the base table based on the user making the query? It would be a bonus if I can also base it on the program making the connection. We're using MSSQL 2008.

ie. SELECT * FROM myTable would instead be SELECT * FROM myView if the user executing the query was [user] and the program making the connection was [reporting software name].

EDIT:
Note: The outer reporting software is an old version of Crystal Reports running some massive ugly queries built by the query builder.
Some sample output would be:

Regular Guy:    Other Guy:

 ItemCode        ItemCode
 AAAAAA745       AAAAAA745
 AAABBB672       AAACCC657
 AAACCC657       BBBAAA766
 BBBAAA766       BBBCCC383
 BBBBBB838       AAABBB672
 BBBCCC383       BBBBBB838
 CCCBBB883       CCCBBB883

As we want to order all items with SUBSTRING(ItemCode, 4,3) = 'BBB' at the bottom.

You can check these things in sys.dm_exec_sessions, but you have to do it in your (let's hope) stored procedures (you can't redirect a query that specifies a specific table name to a different table based on these variables):

ALTER PROCEDURE dbo.report
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS
  (
    SELECT 1 FROM sys.dm_exec_sessions
      WHERE program_name = 'program'
      AND login_name = 'user'
      AND session_id = @@SPID
  )
  BEGIN
    -- do it their way
    RETURN;
  END

-- do it the normal way
END
GO

This is a bad solution though. If one user wants custom ordering, what happens when another user decides they, too, want different ordering? It will happen. Now all your reports are going to have three forks? Why not make ORDER BY a part of the interface?

I'm wondering if you could do something here with schemas, for example if this guy has a different default schema you could write the query such that it doesn't reference a schema. Everyone else will get dbo.viewname and he would get newschema.viewname. But this is messy, the opposite of self-documenting, and if you're going to write code that handles his case, you may as well fix the code the right way.

Plus, as I suggested in my comment, simply referencing a different view or table name doesn't guarantee you'll get a different ordering by doing so. SQL Server still only has to obey a specific order if you've actually stated one in the outer ORDER BY.


Postgresql: very slow results with "group by" I am trying fix some severe performance issues on codechecker. Issue reported here:
https://github.com/Ericsson/codechecker/issues/1399#issuecomment-368862492

The table is the following:

                                       Table "public.reports"
      Column      |            Type             |                      Modifiers
------------------+-----------------------------+------------------------------------------------------
 id               | integer                     | not null default nextval('reports_id_seq'::regclass)
 file_id          | integer                     |
 run_id           | integer                     |
 bug_id           | character varying           |
 checker_id       | character varying           |
 checker_cat      | character varying           |
 bug_type         | character varying           |
 severity         | integer                     |
 line             | integer                     |
 column           | integer                     |
 checker_message  | character varying           |
 detection_status | detection_status            |
 detected_at      | timestamp without time zone | not null
 fixed_at         | timestamp without time zone |
Indexes:
    "pk_reports" PRIMARY KEY, btree (id)
    "ix_reports_bug_id" btree (bug_id)
    "ix_reports_file_id" btree (file_id)
    "ix_reports_run_id" btree (run_id)
Foreign-key constraints:
    "fk_reports_file_id_files" FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    "fk_reports_run_id_runs" FOREIGN KEY (run_id) REFERENCES runs(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "bug_path_events" CONSTRAINT "fk_bug_path_events_report_id_reports" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
    TABLE "bug_report_points" CONSTRAINT "fk_bug_report_points_report_id_reports" FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

You will notice the lack of indexes.

The following query takes a while to run (26 seconds) with 2 469 648 elements in the table.

SELECT reports.run_id AS reports_run_id,
  reports.detection_status AS reports_detection_status,
  count(DISTINCT reports.bug_id) AS count_1
FROM reports
GROUP BY reports.run_id, reports.detection_status

with

 GroupAggregate  (cost=566298.15..590994.88 rows=25 width=41) (actual time=6782.100..26183.127 rows=28 loops=1)
   Group Key: run_id, detection_status
   ->  Sort  (cost=566298.15..572472.27 rows=2469648 width=41) (actual time=6768.582..9561.902 rows=2469648 loops=1)
         Sort Key: run_id, detection_status
         Sort Method: external merge  Disk: 123104kB
         ->  Seq Scan on reports  (cost=0.00..152130.48 rows=2469648 width=41) (actual time=0.010..3248.899 rows=2469648 loops=1)
 Planning time: 0.066 ms
 Execution time: 26224.844 ms
(8 rows)

Any idea how to improve the query or a missing index? Thanks!

In my hands this query, on a simulated data set, is ten times faster than you observed, even on pretty crummy hardware. It has the same EXPLAIN plan and similar estimated costs, just much faster execution.  So either something very strange is going on, or your server is seriously overloaded.  Your best bet for investigating that might be outside of PostgreSQL, for example using top, perf, vmstat, etc.

As far as an index, the one best suited for this query would be:

create index on reports (run_id , detection_status, bug_id);

This allows it to use an index only scan to complete the query, and in my hands is 4 to 5 times faster.  But I don't know how that would translate to your server, as it seems to have an undiagnosed performance pathology.  Also, you need to keep reports well vacuumed for this to be effective, otherwise the index-only nature of the scan loses its benefit.  You might need to modify the tables autovac parameters to get it to be vacuumed enough, or set up your own manual vacuum scripts.


For every inserted row, generate series of rows in another table with AFTER INSERT trigger in PostgreSQL Let's say that a customer places an order with certain quantity and I want to insert this information into table1 initialy. At the same time, I want to generate a series of  rows in the table2 with a combination of the newly generated ID in table1. Has to be executed for every new order. Like:
Table 1
columns - id ,qty
          A1   25
          A2   32

Generates:
Table 2
column - sub_id
          A1_1
          A1_2
          .
          .
          A1_25
          A2_1
          A2_2
          .
          .
          A2_32

This is likely best handled in application code since it's a procedural goal you're trying to accomplish, not really a relational one, since the approach likely requires iteration.
That being said, you possibly can accomplish it in a relational way via Dynamic SQL if you had a numbers table you could select the TOP N number of rows from prepended with your id. The Dynamic SQL comes into play as a way to build the query with N = your qty field.
E.g. the query that your Dynamic SQL could generate would look like this (with id and qty filled in):
SELECT CONCAT('A1', '_', Number) AS sub_id
FROM NumbersTable
ORDER BY Number
LIMIT 25


What are the penalties of using many (thousands) of column families or keyspaces in Cassandra? I am in the process of evaluating the best design for our Cassandra installation.

There is not so much information out there on the Internet about using the first two levels of access that Cassandra provides—keyspaces and column families.

I am wondering if and what the penalties will be if you choose to create an extensive amount of keyspaces or column families (>10.000).

An old blog post somewhere suggested that Cassandra reserves memory for each column family. The article was about the 0.6 version and the current version is 1.0. Is this still the case and a real problem?

What are the penalties of using many thousands of column families or keyspaces in Cassandra?

Cassandra 1.0 uses a minimum of 1MB of heap per CF.  So, 1000 or 2000 CFs will be okay for typical heap sizes, but 10000 will probably not be.  JVM GC does poorly with very large heaps; I recommend staying under 8GB.


Azure Backup SQL VM - Not shrinking log file after transaction log backup I am using Azure Backup to take a transaction log backup from my SQL 2016 Enterprise VM every 15 mins. It also takes one full backup daily. The db is using Full recovery model.

Problem is that the Azure Backup solution is not shrinking the log file after the transaction log backups. Is there any way to automate this process or ensure that the log file doesn't grow out of control?

I'm using this Azure Backup solution: https://docs.microsoft.com/en-us/azure/backup/backup-sql-server-database-azure-vms#create-a-backup-policy

I think there may be some confusion here on the word "Shrinking" - The log file is not ever "Shrunk" by a backup. It can be "truncated" by the log backup, but not shrunk.

When a log is truncated, the segments in the log that are no longer needed for recovery (either because you are in SIMPLE recovery model and a checkpoint has ensured that the data is in the data file securely, or you are in FULL recovery model and the log records have been backed up with a transaction log backup) are effectively marked for overwrite. The log is allowed to use the space those "old/unneeded" log records consumed because they aren't required for recovery.

Note I didn't say "Shrink". The truncation means you can possibly continue writing to the space already allocated to the log file - if there is enough. But truncation will NEVER shrink a log file. It just means it may not have to grow more.

Truncation is more or less an internal log file maintenance type operation. I love what they used to call SIMPLE recovery model back in SQL Server 2000 - "Truncate on checkpoint" mode.

Shrinking is a physical operation on the file itself. I have a few opinions on shrinking data and log files in a rather long answer here. But, in general, don't fear a bigger file. Fear an out of control file that is growing unconstrained. If you are truly taking log file backups, and SQL is showing that you have - you likely just have a file with mostly empty space - meaning the file can be rewritten, and will. It's rather healthy to have a file that is "right-sized" to not have to shrink/grow and shrink/grow between log backups.


Changing SQL Server service account passwords enterprise-wide In the event of a DBA leaving an organization, what options can the surviving team members pursue to change passwords for service accounts portfolio-wide? While using Configuration Manager appears to be the de-facto method, can PowerShell or another scripting or batch language be used to reset them in bulk? I figure if you're changing the login name for the service accounts, Configuration Manager is essential as registry permissions and the like need to be propagated. For just a password change to the existing account however, is a scriptable method OK as long as it's performed during approved maintenance windows for the service restarts?

Option 1

I would probably take advantage of using a Central Management Server to run the password change script across all the different servers at once. This should provide an easy method for verifying the output from each server while keeping it easy to manage by executing once for all target servers.

Option 2

If I was doing this in a tighter change management system, then I might consider scripting it as a batch file and having a sqlcmd execution output a separate log file for each server. This would verify the changes and keep record of them more reliably than doing it in SQL Management Studio.

I don't think there is necessarily a "right" method here, just depends on what meets your requirements best.


Average CPU utilization oracle database I'm interested in understanding how DBAs think about the balance between Oracle's strict licensing requirements and the growing compute capacity of today's machines.  Oracle's insistence that customers must license all physical cores on a box for Oracle EE if any of Oracle EE is installed on the box, seems to be, at first blush, at odds with the fact that with larger, more powerful machines, customers can do more (beyond just Oracle) on those boxes (e.g. with virtualization, etc.).

Ultimately, I'm interested in assessing how much license over provisioning typically occurs for most customers.  Here's an example: If I am a large customer running Oracle EE on a machine with two high frequency Intel Xeon E5-2670 (Sandy Bridge) Processors and 20 physical cores, would I typically be using 100% of those cores 100% of the time to run Oracle DBs (in which case there is no overprovisioning, since the whole box utilization is 100%) or would a more likely scenario be running 50% of those cores 50% of the time for Oracle DBs (in which case the whole box utilization is 25%, so I've effectively paid for 3x more licenses than i actually need.

Would appreciate any insights on the typical utilization rates that you see on the servers that run your Oracle DBs, and whether you think this notion of overprovisioning is a real issue that you would avoid if you could (say by buying DB capacity on-demand).

Start using your licenses in a smart way. Many customers buy EE licenses, only using SE features. Don't underestimate the power of SE. You can use SE with a max of 4 sockets and with modern CPU's that's a lot of power. SE1 has a max of 2 sockets. See Database Licensing - Oracle

Next to that, consider consolidating your smaller applications into 1 serious database, often leading to much better CPU and license utilization. For one customer I saved 800.000,-/year doing so.

The question about how to size for over provisioning is really a business question. How does the business get hurt when it can't process every request immediately? Some will lose a lot of money for lost sales, others can handle it.


why do SSRS reports run longer for a particular user We have been receiving complaints from a particular SSRS user that her reports run slow.  I investigated in the ExecutionLog table in the ReportServer database and I observed something strange.

Fairly consistently, the TimeDataRetrieval is much longer than other users.  Both TimeProcessing and TimeRendering are near the averages of other users.

I am puzzled.  The reports all use the same shared data source that runs as a service account.  I would think if it was a crappy user computer issue, I would see differences in the rendering time.  Same if there was a network issue.

I don't know where else to look - any ideas?

Converted to answer, per OP.

Check the SET settings for her session (sys.dm_exec_sessions). I wonder if she has different SET ARITHABORT or other settings compared to the other users, in which case she might be getting a different plan for the same underlying query.


Perl - MySQL/MariaDB - slow with no identifiable bottleneck I am running a Perl script (using DBI) which reads from raw files from a hard disk, and updates MySQL database (which is on a separate SSD).  My performance is rather slow (1000 files processed in 30-60 seconds), but I can not find the bottleneck.

CPU. Network, Disk and Memory are all rather unused. I am running Windows 7 64bit on an i7 machine (8 cores). MariaDB is version 10.0.10. My database is around 78G in size with 5M entries, all tables properly indexed.

Perfmon confirms this showing total CPU 6%, Network 0%, HD Disk 140 I/O/s, Memory 10%. None of the CPU cores are used more than 3-4%.

I have experimented with changing all these mysql variables with no success:
    innodb_use_global_flush_log_at_trx_commit
    innodb_buffer_pool_instances
    global.max_connections
    innodb_thread_sleep_delay
    global.innodb_io_capacity
    global.innodb_sync_spin_loops
    innodb_flush_log_at_trx_commit
    wait_timeout

Mysqltuner did not report anything of interest, except for:
Data in InnoDB tables: 78G (Tables: 12)
Total fragmented tables: 10
Query cache is disabled
Thread cache is disabled
InnoDB data size/buffer pool: 78.1G/2.0G

Perl profiling showed that majority of time is taken up by DBI::st::execute (invoking sql).

I have also tried disabling firewall and virus scanner too - no difference.

Conclusion and a workaround

After exhausting all options on Windows, I decided to switch to Linux, mostly because I was frustrated with inability to profile and debug in detail.

I have moved the whole setup to Ubuntu 14.04. I first tried XAMPP but gave up because of conflicts between XAMPP and MySQL and MySQL Workbench.  Then I moved to vanilla MySQL (5.5, I think) and vanilla Apache 2.

However, I was still left with the same problem – no visible bottleneck and resources still underutilized.   I suspected throttling in TCP sockets (used between Perl code and MySQL), but further profiling proved this not to be the case.

Then, I turned my attantion to Perl DBI module DBD::SQL, thinking that it may be doing some throttlinig.  I did some tests where I replaced DBI calls in Perl with system calls (system("mysql -e'INSERT INTO blah blah …').  I have determined that the performance did not change, therefore absolving DBI as a culprit.

I need to add one important detail now: I was in fact always running a number of my Perl scripts in parallel.  Given that the CPU has 8 cores, this is necessary to utilize all of them, of course.
Further debugging showed that almost all my perl processes which were supposed to work furiously
were sleeping most of the time. Ubunty System Monitor shawed them as waiting on Waiting Channels  wait_answer_interruptible or  unix_stream_recvmsg.  CPU History graph in System Monitor showed all perl processes jumping to 100% CPU utilization and then dropping to ~0% in unison.  I suspected that MySQL server is not configured for multi threading, but htop showed 17 mysqld threads activated, confirming that all should be ok.
I suspected that all MySQL threads were waiting on the same semaphore and were locked out for most of the time.  I dreaded delving into the dark bowels of MySQL trying to figure out what goes on inside. Instead, I decided to replace MySQL with MariaDB, even though MariaDB seems to have had the same issue originally when I was running it on Windows.

Lo and behold – this finally worked.  My perl scripts were screaming.

One last problem remained: I had a very rudimentary method of parallelising the perl scripts: I would just run 10 or 20 with their respective loads and hope that they would utilize all the resources.

This has obvious drawbacks: if too many processes are spawned, the OS may spend too much time swapping them (although not a serious issue with only 20 processes, it becomes an issue with e.g. 1000).   If not enough processes are spawned (e.g. less than 8, for each core) the CPU will not be utilized fully for sure.   If too many processes exhaust RAM, Linux will turn to disk and will start swapping.  As soon as this starts happening, everything grinds to a halt.

I searched but could not find a perl library/script/code which would spawn new processes only when CPU, memory and disk are under utilized.  Hence I created my own: raspawn.pl (resource aware spawn) which I placed on github.  Raspawn.pl spawns a number of processes while trying to keep resources utilization just below the maximum.  It constantly checks the CPU, memory and disk utilization and only if all are less than ~90% utilized, starts a new process.

Finally, this worked.  I can now process my whole load in around 7 days, instead of many months...


How can a primary key be inserted as foreign key? Hi I am building an order intake form that will have information submitted belonging to 3 tables.  The order table will have the primary key used for the property and customer table.

How can I simultaneously create the primary key via the identity on the order table and insert that into the property and customer table?

SQL please.

Order
OrderKey PK

Property
OrderKey FK

If you insert multiple Order records (pick a new name that's not a keyword, by the way) then you need to use the OUTPUT clause.  Otherwise SCOPE_IDENTITY() should work.

Quick example using SCOPE_IDENTITY:

DECLARE @OrderID int

INSERT INTO
  Orders
   (<columns>
VALUES
 (....)

SET @OrderID = SCOPE_IDENTITY()

INSERT INTO
  Properties
   (OrderID, ...)
VALUES
  (@OrderID, ...)


Getting count(column) as well as a specific row in each group I am working with SQL Server 2012.

I want to get the count of column as well as the rows with specific condition in each group.

The query I have in mind looks something like this:

select count(column1), (column2 where column2 contains 'page1')
group by column1

I know that the above query isn't correct but I want to show the idea.

Sample data

column1   column2
-------  --------
x1       'temp/page1_l'
x1       'temp/page2_f'
x2       'temp/page2_d'
x2       'temp/page1_k'
x2       'temp/page2_e'

Expected output

count(column1)  column2
--------------  --------------
2               'temp/page1_l'
3               'temp/page1_k'

How can I achieve that output?

For the (column2 where column2 contains 'page1') expression, try something like:

MAX(CASE WHEN Column2 LIKE '%page1%' THEN Column2 END)

...but I'm not sure what you expect if you have multiple rows with "page1" per column1.


Why can I not DENY permissions through module signing? In the documentation for ADD SIGNATURE, they have the following caveat:

  Module signing should only be used to grant permissions, never to deny or revoke permissions.

My question is: why? Conceptually, I think of signature possibly adding to the user token. If the user linked to the certificate or key that signed the module has a DENY permission associated with it, I'd think that it would come along for the ride. My testing shows that it doesn't, but I'd like to understand why it doesn't.

Actually, DENY permissions do get applied when used via module signing. If your test indicated that nothing happened, then most likely you were experiencing the wonder of ownership chaining ;-). If a User has an explicit DENY for INSERT on a Table, but the Stored Procedure does an INSERT and has the same authorization as the Table, then the User's permission isn't even checked for what the Stored Procedure does, only that the User can EXECUTE the Stored Procedure. In order to test you need to prevent ownership-chaining from interfering, and you do that by making the statement(s) within the Stored Procedure Dynamic SQL (i.e. wrap the statement(s) in EXEC (N'...'); ). Then you can (and will, as my testing shows) see the effect of DENY permissions assigned to the Certificate- / Key- based User.
As to why the recommendation to not use Module Signing to restrict access, I would say that:

The default state is that permission is already implicitly denied for something, else there is no need for the module signing. There is no purpose to adding restrictions in this manner. If restrictions are needed, they can be added to the User / Login / Role.
Meaning: module signing exists because there are restrictions that need to be overcome (and impersonation is not a great way of overcoming them), not because the system is wide-open and restrictions are needed.

The permissions apparatus is, in itself, conceptually different: regular permissions (where DENY does work) deals with who can do certain operations (i.e. Users / Logins / Roles), but module signing deals with what can do certain operations (i.e. code such as Stored Procedures, Triggers, etc). Generally speaking, if you don't want a module to be able to do a particular operation, just don't code it to do that operation in the first place.
You can't prevent someone from attempting to execute queries that do operations that they shouldn't do, hence we have implicit and explicit DENYs. But you do have control over the code ;-)

While probably not a reasoning for Microsoft to have made that statement, I would say that a minor reason to avoid it is simply the confusion caused by situations such as your testing (where it appeared to not even work due to ownership-chaining doing its job).

The only time that I can think of, at the moment, to have a DENY in module signing is if module signing is used to allow Dynamic SQL to work, and the Dynamic SQL is structured to allow for something along the lines of the User passing in a Table name to do an operation on and you want to ensure that some tables are considered "off limits", though I am not sure that the DENY is even needed in this scenario (could perhaps be a case of it simply being easier to GRANT access to a Schema but DENY one or two Tables within it).


Range Optimizer in Mysql: What is an Index dive during Query Optimization? On following Jorgen Loland post on range optimization, I have come to the concept of "index dive" which is made during MySQL query planning (aka in Mysql Optimizer).

Quoting his point,

  For as long as there have been a range access method in MySQL, the number of rows in a range has been estimated by diving down the index to find the start and end of the range and use these to count the number of rows between them. This technique is accurate and is, therefore, a good basis to make the best possible execution plan (QEP) for the query.

I have tested the same and they were accurate in my tests too.
I have a few questions,

What does it exactly mean, "to find the start and end of the range"? Does this mean
getting the start page and end page for range in the B-tree.
If my first question is Yes, then how this technique will be accurate,i.e.,
 what about the pages.
in the middle of the range.

To "dive" is to drill down the BTree from the 'root' down to the leaf node for a particular key.

Let's say you have a non-unique INDEX(x).  Further, let's say that there are 100 rows with x=123.  WHERE x = 123, the two dives would be to "find the first index entry for x=123" and to find the "last" such row.

His first use of "accurate" (in the article) was to say that the dives are more accurate than using statistics that may be stale.

I suspect his use of "accurate" is an exaggeration in the other uses.  I say this because I cannot believe that counting the rows between the first and last dive is no more than an estimation.  To get the exact number would require fetching each block -- this is too costly.

Note the 100 rows might be in the same block, or might (if the rows are wide) be spread across multiple blocks.  For the purposes of EXPLAIN and query planning, an estimate is considered "good enough".  The exact ("accurate") number can be quickly counted if the start and end are in the same block, but, if spread across multiple blocks, I think then it would only estimate the row counts for the intermediate blocks.

The "dives", even if they involve a disk-read, are usually not wasted.  This is because you will (presumably) then proceed to perform the query and need those block(s) anyway.

Side note:  Since Jørgen Løland wrote that article, the Optimizer has employed a "cost basis" algorithm.  However, the "dives" are still important in it.


Getting a proxy account to backup to a share using Ola Hallengrens backup script I have a SQL Server 2012 Standard install where the Engine and Agent are running under the default NT Service accounts

Engine - NT Service\MSSQLSERVER

Agent  - NT Service\SQLSERVERAGENT

I am trying to get Ola Hallengrens backup scripts to backup to a Network Share but this is proving a bit problematic.
Normally I would have the Engine and Agent running under domain accounts and grant those access to the network share and it would just work however this was setup before my time and am unable to change these.

As the Agent and Engine are not running under domain accounts I am attempting to use a Proxy to get this working.

I have a share \\ComputerName\Folder and a service account Domain\account.svc. I am able to browse and create files and folders using the Domain\account.svc account so the access seems to be working ok. However when running this through the Agent it is not working.

What I have done.

Created a Credential using Domain\account.svc
Created a Operating System (CmdExec) Proxy and linked it to the Credential
In the Backup System Databases job step set the "Run as" to the Proxy account

The error I am getting in the log file is

  Msg 50000, Level 16, State 1, Server [SERVER NAME], Procedure DatabaseBackup, Line 457
  The directory \\ComputerName\Folder does not exist.

  Msg 50000, Level 16, State 1, Server [SERVER NAME], Procedure DatabaseBackup, Line 740
  The documentation is available at https://ola.hallengren.com/sql-server-backup.html.

This being the same error you normally get when the Agent Domain account doesnt have access to a share.

Question - Can someone shed some light on why this would not be working.

Thanks.

Edit 1:
The Domain\account.svc has Full Control on the share, is a local admin on the machine as well as a sysadmin within SQL.

Edit 2:
Installed a test instance of SQL on my desktop and am able to replicate the issue. When I then change the Engine and Agent service accounts to run under Domain\account.svc the backup completes successfully to the share.

Edit 3:
Not an ideal solution but for now I am backing up locally and have a robocopy job set to mirror the directory to the share.

To make this happen, you need to give the computer domain account on which you have SQL Server installed rights to write to the share [Folder] on [ComputerName].

Say you have a computer SRV-SQLSERVER, and SQL services you run with NT Service*, but in some situation you can't create a domain account for them.
But you need to store backups on other domain server \SRV-BACKUP.

To have this working, create an share on \\SRV-BACKUP\Folder and give write permission for SQL box domain account DOMAIN\SRV-SQLSERVER$.

Some related info can be found here:

How to grant network access to LocalSystem account?


How to express functional dependency in relational calculus and SQL? I want to express in relational calculus and SQL the following functional dependency associated with Vote. This is the first time I try to implement it in actual SQL and relational calculus.
Vote: #PollID, #ElectorID → VoteID

From the following relational scheme:

Candidate(CandidateID, Name, PoliticalGroup)

Poll(PollID, Date, Round)

Elector(ElectorID, Name, #PollingStationID)

Vote(VoteID,#PollID, #CandidateID, #ElectorID)

My attempt
1. In relational calculus

2. In SQL
CREATE ASSERTION Vote
    BEFORE COMMIT t.voteID FROM Vote t
        CHECK u.CandidateID FROM Candidate u
             WHERE EXISTS v.PollID FROM Poll v
                 WHERE u.ElectorID=t.ElectorID AND t.PollID=v.PollID

Not knowing your full situation, I recommend something like this. The foreign keys should create the dependencies you need. If your SQL structure is already in a tabular format, you might consider putting this information into temporary tables--like the ones below. But it looks like your tables are there according to your assertion comment.

From what you mentioned you'll need to connect the Candidate, Poll, and Elector tables to the Vote table with a foreign key (with the specific requirements you need--you can easily do this in the SQL Server Studio GUI as well. You'll need to do the same thing with the Elector table and the Poll table via a foreign key within Elector that points to the Poll table. Use the following below as a template/base--add/subtract as needed...

create table Candidate
(
CandidateID int primary key,
Name varchar(255) null,
PoliticalGroup varchar(255) null
)
create table Poll
(
PollID int primary key,
Date datetime null,
Round float null
)
create table Elector
(
ElectorID int primary key,
Name varchar(255) null,
FK_PollingStationID int null
)

create table Vote
(
VoteID int primary key,
FK_PollID int null,
FK_CandidateID int null,
FK_ElectorID int null
)

ALTER TABLE dbo.Elector ADD CONSTRAINT
    FK_Elector_Polling FOREIGN KEY
    (
    FK_PollingStationID
    ) REFERENCES dbo.Poll
    (
    PollID
    )

GO

ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Candidate FOREIGN KEY
    (
    FK_CandidateID
    ) REFERENCES dbo.Candidate
    (
    CandidateID
    )

GO
ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Poll FOREIGN KEY
    (
    FK_PollID
    ) REFERENCES dbo.Poll
    (
    PollID
    )

GO
ALTER TABLE dbo.Vote ADD CONSTRAINT
    FK_Vote_Elector FOREIGN KEY
    (
    FK_ElectorID
    ) REFERENCES dbo.Elector
    (
    ElectorID
    )

GO


How to install and configure Postgres-XC in windows? Can anybody suggest me any resources of the complete installation procedure of postgres-XC in windows. I've installed postgresql-9.2 in my windows and can  use it. Now I need to know the installation procedure of postgre-XC and the way to do clustering and the whole distribution and replication process using postgres-XC. I've googled and found few but all of them are for linux. But I need this for windows.

FYI:

http://manojadinesh.blogspot.com/2012/08/postgres-xc-setup.html
http://alexalexander.blogspot.com/2013/01/postgres-xc-explained.html

Thanks

Postgres-XC does not run on Windows:

  Postgres-XC currently runs on the Linux Operating Systems running on
  64-bit Intel(R) processors.

  The development team tested this with Cent-OS 5.3 and 5.4.

You might be able to build the data nodes and possibly the coordinators on Windows, but the GTM will take major work to port to Windows.


Does database systems use basic system calls of local file system? Suppose database system inserts a row in table, will it use write() system call of host file system to write that particular row to table?

What mysql_user wrote is probably true for all systems using files to store data as those just have to communicate with the filesystem used and it does not make sense to rewrite the "drivers" for all known filesystems just to manage them directly (and would collide with the OS drivers anyway).

But some DBMS allow the use of disks/partitions directly. It allows to skip one layer of indirection and buffering, to manage caches directly.., but loses the benefits of using files (simpler backup etc.).

It is often called raw partition

MySQL InnoDB
SQL Server
Oracle used to support it but seems to be desupported - probably getting replaces by Oracle ASM which looks more like a dedicated filesystem, but in the end has similar benefits - being optimized for databases instead of universal files.
Firebird
raw devices deprecated in DB2, but it can use Linux block devices instead which is still quite low-level


Why is the speed of executing statements dependent on the network connection? It looks like the speed of executing T-SQL is dependent on the latency of the network connection against the server. I assumed that if SQL Server has nothing to report back to the client about, it will just execute away until it is done but testing shows another story.

create procedure UselessLoop
  @I int
as
declare @D datetime = getdate()
while @I > 0 set @I -= 1
print datediff(millisecond, @D, getdate())

exec UselessLoop 100000

Server    Milliseconds
local     53
nearby    63
faraway   660

exec UselessLoop 1000000

Server    Milliseconds
local     546
nearby    640
faraway   6183

The tests are executed against the same server from different computers using SSMS. Local is executed from the Server, nearby is on the same local network and faraway is executed from another office 500 km away connected with 1 gigabit fiber.

There is obviously some communication going on between SQL Server and the client that directly is dependent on the number of statements executed.

I used Wireshark to look at what is transported and I can't say that I understand that much but it was a tcp.stream exchanging a total of 26 MB in 22740 packets.

How about a useless function instead?

create function dbo.UDFUselessLoop(@I int)
returns int
as
begin
  declare @D datetime = getdate()
  while @I > 0 set @I -= 1
  return datediff(millisecond, @D, getdate())
end

print dbo.UDFUselessLoop(1000000)

It executes in 406 milliseconds regardless of where it is executed from. Looks like there is no communication with the client in the loop.

There is obviously some communication going on between SQL Server and the client that directly is dependent on the number of statements executed.

Yes, there is. By default, SQL Server sends a TDS DONE_IN_PROC message after every statement in a stored procedure. The message communicates status and row count information for the completed statement to the client.

You can suppress the sending of these messages using the T-SQL command:

SET NOCOUNT ON;

The following is an extract (my emphasis) from the Books Online entry for this command:

  For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Related Q & A: Why does a simple loop result in ASYNC_NETWORK_IO waits?


SQL Server - Defining and populating a new temp table column fails only once How can I add a new column to a temp table dynamically in a stored procedure and populate it with some values, without resorting to dynamic SQL? I am getting an error only on the first time I run the code. It seems to be side-effecting in tempdb, and being inconsistent with Deferred Name Resolution.

Consider this sample stored procedure that has been enhanced to populate a newly-added Column2 that is only used by some callers:

CREATE PROCEDURE TestAddTempTableColumn AS BEGIN
    IF NOT EXISTS
    (--don't require all callers to have their table declarations updated
        SELECT 1
        FROM tempdb.sys.columns
        WHERE
            object_id = OBJECT_ID('tempdb..#TestTempTable')
            AND name = 'Column2'
    ) ALTER TABLE #TestTempTable ADD Column2 VARCHAR(MAX) NULL

    INSERT INTO #TestTempTable(Column1, Column2)
    VALUES ('column1 value', 'column2 value')
END

Then running this code in a new query window throws an error only in the first batch:

PRINT 'Test1'

IF OBJECT_ID('tempdb..#TestTempTable') IS NOT NULL DROP TABLE #TestTempTable
CREATE TABLE #TestTempTable
(
     Column1 VARCHAR(MAX) NULL
)

SELECT [Case1_1]='', * FROM #TestTempTable
EXEC TestAddTempTableColumn
SELECT [Case1_2]='', * FROM #TestTempTable

GO

PRINT 'Test2'

IF OBJECT_ID('tempdb..#TestTempTable') IS NOT NULL DROP TABLE #TestTempTable
CREATE TABLE #TestTempTable
(
     Column1 VARCHAR(MAX) NULL
    ,Column2 VARCHAR(MAX) NULL
)

GO

SELECT [Case2_1]='', * FROM #TestTempTable
EXEC TestAddTempTableColumn
SELECT [Case2_2]='', * FROM #TestTempTable

PRINT 'Test3'

IF OBJECT_ID('tempdb..#TestTempTable') IS NOT NULL DROP TABLE #TestTempTable
CREATE TABLE #TestTempTable
(
     Column1 VARCHAR(MAX) NULL
)

SELECT [Case3_1]='', * FROM #TestTempTable
EXEC TestAddTempTableColumn
SELECT [Case3_2]='', * FROM #TestTempTable

Redefining the stored procedure or changing query window are the only ways I have found to make the error recur after ever declaring #TestTempTable with Column2, even if #TestTempTable gets dropped! But then why doesn't it choke on the ALTER TABLE ... ADD COLUMN statement when I call it with the column already existing?

When adding a new optional column to this output temp table, I'd much rather make one code change in the stored procedure populating it, rather than every single reference throughout the codebase (and there are indeed numerous). And of course, while testing I defined it with the new column before testing other code that doesn't, so it passed my testing and is now breaking on the live server.

When a stored procedure is executed for the first time, the query
  processor reads the text of the stored procedure from the
  sys.sql_modules catalog view and checks that the names of the objects
  used by the procedure are present.

Deferred Name Resolution and Compilation

That explains the observed behavior.  On the first call, the stored procedure body cannot be parsed and compiled because it references an object that doesn't exist: Column2.

When you create Column2 before invoking the procedure it will parse, compile and run.  Then in the third invocation the stored procedure has a cached plan, and doesn't need to be parsed again.  If you call dbcc freeproccache before Case_3 it will fail too.

And so the answer to:

  How can I add a new column to a temp table dynamically in a stored procedure and populate it with some values, without resorting to dynamic SQL?

Is that you can't and shouldn't.  It's a very strange thing to even attempt.

You can normalize the the temp table and then use it in a subsequent stored procedure.  Like this:

CREATE or alter PROCEDURE TestAddTempTableColumn AS
BEGIN
    IF NOT EXISTS
    (--don't require all callers to have their table declarations updated
        SELECT 1
        FROM tempdb.sys.columns
        WHERE
            object_id = OBJECT_ID('tempdb..#TestTempTable')
            AND name = 'Column2'
    ) ALTER TABLE #TestTempTable ADD Column2 VARCHAR(MAX) NULL

    exec TestAddTempTableColumn_impl
end

go
CREATE or alter PROCEDURE TestAddTempTableColumn_impl AS
BEGIN
    INSERT INTO #TestTempTable(Column1, Column2)
    VALUES ('column1 value', 'column2 value')
END

But it strikes me as somewhat surprising to have code that creates a temp table, calls a stored procedure to load that temp table, and have the procedure alter your temp table.


SSMS 2014 Gives an exception when opening the new query window I have an installation of SQL Server 2014 on a server on the LAN and on my PC I have SQL Server Management Studio 2014 for few months already. Out of no where when I open SSMS I get

The type initializer for 'system.windows.media.fonts' threw an exception

I have no clue of what might have caused this exception. I tried re-installing SSMS 2014, removing all SQL components on my PC and re-installing SSMS again, I even removed .NET 4.7 (Because I have VS 2017 as well). What is the cause for this exception and how to resolve it?

This issue has been posted as a Microsoft Connect item.

2012 SSMS System.Windows.Media.Fonts Exception On New Query

A possible workaround was posted by the author as follows:

  I have found that if change the font for the "Text Editor" from the default of "Consolas" to something else (e.g. Lucida Console), this problem goes away.


SQL Server CUs vs SPs + CUs? I recently applied the latest SQL Server CUs (Cumulative updates) for their current SPs (Service Packs) on our SQL Servers.  I'm getting the sense that I actually really need to update all of them to the latest SP + CU.  For example, we have a 2014 SP2 server, which now has the latest CU for SP2 (SP2 CU18).  Should I update it to the latest SP + CU (SP3 CU4)?
Thanks,
-Peter

Should I update it to the latest SP + CU (SP3 CU4)?

When a new SQL Server service pack is released, Microsoft provides only 12 months of full support for the previous service pack.

Support is limited for older SPs:

  For customers on supported products with service pack versions that
  have left full support, Microsoft offers commercially reasonable
  support as follows:  Commercially reasonable support incidents will be
  provided through Microsoft Customer Service and Support and Microsoft
  managed support offerings (such as Premier Support). If the support
  incident requires escalation to development for further guidance,
  requires a nonsecurity update, or requires a security update,
  customers will be asked to upgrade to a fully supported service pack.

. . .

  Microsoft recommends that you apply the latest Cumulative Update (or
  one released in the last one year) for the corresponding release. The
  support team might require you to apply a specific Cumulative Update
  that addresses a specific problem when troubleshooting an issue.

https://support.microsoft.com/en-us/help/17138/service-pack-lifecycle-policy

So cross-referencing the release history here, yes you should apply SQL Server 2014 SP3 CU4.


Convert rows to columns in SSIS. Unpivot? If I have data like this

"","",WAREHOUSE ID,1,2,3,4,5...
"","",WAREHOUSE CODE,BOS,HOU,LAX,NYC,CHI...
"","",WAREHOUSE NAME,BOSTON,HOUSTON,LOS ANGELES,NEW YORK CITY,CHICAGO...

But want to transform it like this

 WAREHOUSE ID, WAREHOUSE CODE, WAREHOUSE NAME
 1,BOS,BOSTON
 2,HOU,HOUSTON
 3,LAX,LOS ANGELES
 ...

If, as above, [Column 2] is the column that contains what should be the header row, and Columns 3 through 40 contain what should be row values, which columns get selected and set to pass through for the UNPIVOT to perform the desired transposition?

Or is UNPIVOT not even what I'm looking for?

Yes the Unpivot Transformation should work, as long as the number of input columns does not vary.

Looking at your example, it looks like [Column 2] (e.g. WAREHOUSE ID) should be set as the Pivot Key Value.  You can ignore any output columns that you dont want to output e.g. the Pivot Key Value Column Name.


Can you rollback a query in state 'committing alter table to storage engine' We've got an InnoDB table with 70 million rows, and we have been trying to run an alter table statement to modify and add a couple of columns. The query seems to have altered the table, and is now in the state of 'committing alter table to storage engine'.

START TRANSACTION;
ALTER TABLE table
  MODIFY COLUMN column1 int(11) NOT NULL DEFAULT 0,
  MODIFY COLUMN column2 tinyint(1) NOT NULL DEFAULT 1,
  ADD COLUMN column3 int(11),
  ADD COLUMN column4 int(11) NOT NULL DEFAULT 1,
  ADD COLUMN column5 varchar(255);
COMMIT;

This has been running overnight, and is at 19 hours at the current time. We do not have the performance schema enabled so cannot look at an estimated time of completion. My concern lies as to what the query is doing and whether the query will rollback if killed. I have seen other questions relate to queries that are stuck in copying to tmp tables, or awaiting a table lock. However I cannot find anything about being stuck while the alter table is committing.

Is it safe to kill a query in this state, and if the query is killed, will it rollback successfully?

The server is running MariaDB 10.2

I am sorry to have to inform you, but ALTER TABLE cannot be rolled back. In fact, ALTER TABLE triggers an implicit commit.

According to Page 418 Paragraph 3 of the Book

the following commands can and will trigger an implicit commit

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
START TRANSACTION

I have mentioned this before

Aug 21, 2015 : Transactional DDL workflow for MySQL
Feb 12, 2014 : row locking within ACID transaction innodb
Mar 15, 2013 : MySQL backup InnoDB

In your case, the ALTER TABLE will either finish or you will have a temp table left hanging around. In either case, it will not be a rollback in a transactional sense.


How to get the information of the optimizer of PostgreSQL? I am curious about the information like

total optimization time
the time spent on each stage of optimization
the number of alternative plans and
the cost of each alternative plan.

You can get some of that information by turning on the configuration parameter log_planner_stats.  Most of that information, however, doesn't really exist, because the planner does not fully compute all alternative plans and their costs.  It only explores an alternative plan until it can determine that it is slower than the current best plan.  So alternative plans are not fully materialized.


mongo DB The disk space completelly filled for replicate instance I am currently facing a scenario that one of the replicate server disk got completely filled up, while our primary server still has additional space left.

My confusion is that the cmd like db.compact and db.repairDatabase() has to be run on primary server or I need to run it on replicate server instance?

Along as I have no disk space left, would it be appropriate to  remove it, as a replicate using db.config , delete the data files and add it again.

Need your help?

Immediate action to regain redundancy

Make absolutely positively sure your data fits on the secondary. DO NOT PROCEED IF IT DOESN'T OR YOU ARE IN DOUBT. YOU HAVE BEEN WARNED!
Shut down the secondary
Delete the content of the secondaries dbpath (for example /var/lib/MongoDB)
Restart the secondary
The secondary will now resync from the primary, creating unfragmented datafiles which will make the smallest space possible.

Change deployment (!)

If one of the members has less space available than the others, there will be a point in which it can't replicate any more and drop out of the replica set, reducing failover capabilities. This usually happens 3am on a Saturday morning, when you just got home from a party ;)

You have to make absolutely sure that your replica set members have the same amount of disk space available. If you absolutely can't do this, you have to artificially limit the space available to each member, either by creating dedicated partitions of the exact same size (preferred way) or by using MongoDB's quotaFiles option.


How can I improve this design? I'm creating an event planning PHP/MySQL web application as a learning project. The whole app revolves around the idea of the event and its various components (participants, groups, lists, etc.)

I understand how to use PHP and MySQL but my theoretical knowledge is at a basic level. I have setup and used very simple MySQL databases before, with little or no relationships between tables. This project is quite different.

Let me explain my idea of lists in the context of an event. The initiator of an event can create any number of lists for that event, each list can have items added to it by any participant of the event. With my current knowledge I would create the following tables: events, users, eventuser, lists, eventlist, items, listitem, etc.

This doesn't feel right… If the app was to be used by a couple thousand users, each making a few events, each having a couple of lists, each having a few list items… well, you get the idea. The listitem table would get gigantic pretty quick. Am I totally wrong? Would that be OK? Would it be better to create a set of tables for each event? …that doesn't feel right either.

What is the right way to do this? Or at least a better way?

Something like this:

Party
id
type: {individual, group}
name

PartyRelationship
fromPartyId
toPartyId
type: {groupMembership}

Event
id
name
scheduledStart
scheduledEnd
actualStart
actualEnd

EventRole
eventId
roleType {organizer, scheduler, participant}
partyId

List
id
eventId
name

ListItem
id
listId
description
createdBy /* should point to a party */

An Event hasMany Roles, which could be organizers, participants, etc.

A participant can be either an individual or group, both of which are subtypes of Party

A Group party has many Individual parties, as described in the PartyRelationship table. An Individual can belong to zero+ groups.

An Event hasMany Lists, and a List hasMany Items. A list belongsTo one Event, and a ListItem belongsTo one List.

Example usage:

insert into party values (1, 'individual', 'neil');
insert into party values (2, 'individual', 'bernk');
insert into party values (3, 'individual', 'simon');
insert into party values (4, 'individual', 'ypercube');

insert into party values (5, 'group', 'canadians');

insert into partyRelationships values (5, 1);
insert into partyRelationships values (5, 2);
/* now neil and bernk are related to the 'canadians' party */

insert into event values (1, 'an event with canadians and ypercube, organized by simon', '2012-07-01', '2012-07-02');

insert into eventRoles values (1, 'organizer', 3); /* simon is the organizer*/
insert into eventRoles values (1, 'participant', 5); /* the 'canadians' group is a participant */
insert into eventRoles values (1, 'participant', 4) /* ypercube is a participant too */


mysql.sock not generated when starting MySQL server using XAMPP I have XAMPP installed, and I want to use mysql.sock file for my rails app.

Now, when I start the MySQL server (from xampp control panel), it starts, I see it's PID, I can access the mysql command line etc, but I can't find the mysql.sock file anywhere.

I'm running under Windows 7.

Any idea what might could have gone wrong?

EDIT:
Here's the error log, from mysql_error.log - this is what I see when i start MySQL from xampp control panel:

2015-07-16 13:51:27 4088 [Note] Plugin 'FEDERATED' is disabled.
2015-07-16 13:51:27 16f0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2015-07-16 13:51:27 4088 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-07-16 13:51:27 4088 [Note] InnoDB: The InnoDB memory heap is disabled
2015-07-16 13:51:27 4088 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2015-07-16 13:51:27 4088 [Note] InnoDB: Memory barrier is not used
2015-07-16 13:51:27 4088 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-07-16 13:51:27 4088 [Note] InnoDB: Not using CPU crc32 instructions
2015-07-16 13:51:27 4088 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2015-07-16 13:51:27 4088 [Note] InnoDB: Completed initialization of buffer pool
2015-07-16 13:51:27 4088 [Note] InnoDB: Highest supported file format is Barracuda.
2015-07-16 13:51:27 4088 [Note] InnoDB: The log sequence numbers 1665558 and 1665558 in ibdata files do not match the log sequence number 1666100 in the ib_logfiles!
2015-07-16 13:51:27 4088 [Note] InnoDB: Database was not shutdown normally!
2015-07-16 13:51:27 4088 [Note] InnoDB: Starting crash recovery.
2015-07-16 13:51:27 4088 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-07-16 13:51:27 4088 [Note] InnoDB: Restoring possible half-written data pages
2015-07-16 13:51:27 4088 [Note] InnoDB: from the doublewrite buffer...
2015-07-16 13:51:28 4088 [Note] InnoDB: 128 rollback segment(s) are active.
2015-07-16 13:51:28 4088 [Note] InnoDB: Waiting for purge to start
2015-07-16 13:51:28 4088 [Note] InnoDB: 5.6.24 started; log sequence number 1666100
2015-07-16 13:51:28 4088 [Note] Server hostname (bind-address): '*'; port: 3306
2015-07-16 13:51:28 4088 [Note] IPv6 is available.
2015-07-16 13:51:28 4088 [Note]   - '::' resolves to '::';
2015-07-16 13:51:28 4088 [Note] Server socket created on IP: '::'.
2015-07-16 13:51:28 4088 [Note] Event Scheduler: Loaded 0 events
2015-07-16 13:51:28 4088 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.6.24'  socket: ''  port: 3306  MySQL Community Server (GPL)

I have BAD NEWS and VERY BAD NEWS

BAD NEWS

Connecting to MySQL via the socket file is a Unix-only protocol

  This option explicitly specifies a protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For example, connections on Unix to localhost are made using a Unix socket file by default:

shell> mysql --host=localhost

  To force a TCP/IP connection to be used instead, specify a --protocol option:

shell> mysql --host=localhost --protocol=TCP

  The following table shows the permissible --protocol option values and indicates the platforms on which each value may be used. The values are not case sensitive.

--protocol Value   Connection Protocol                              Operating Systems
TCP                TCP/IP connection to local or remote server      All
SOCKET             Unix socket file connection to local server      Unix only
PIPE               Named-pipe connection to local or remote server  Windows only
MEMORY             Shared-memory connection to local server         Windows only

Therefore, MySQL for Windows does not create a socket file and you cannot connect with one.

The closest protocol you want to use like a socket file is the named pipe

  On Windows, you can force a MySQL client to use a named-pipe connection by specifying the --pipe or --protocol=PIPE option, or by specifying . (period) as the host name. If named-pipe connections are not enabled, an error occurs. Use the --socket option to specify the name of the pipe if you do not want to use the default pipe name.

VERY BAD NEWS

I have an old posts where I mentioned that certain builds of mysqld.exe supported named pipes but are no longer being distributed :

Oct 15, 2014 : How to check/change MySQL named pipe settings?
Sep 27, 2011 : MySQL-NT is crashing frequently

As I said in How to check/change MySQL named pipe settings?

  Do yourself a favor and do not enable it since there is so little documentation on using this protocol. It was originally meant for Windows NT (Not There).

SUGGESTION

Connect with TCP/IP instead of trying to work with named pipes


How to determine if open_table_cache is set properly? Does anyone know what this really means? (from MySQL docs)

  You can determine whether your table cache is too small by checking
  the mysqld status variable Opened_tables, which indicates the number
  of table-opening operations since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+ | Variable_name | Value |
+-| Opened_tables | 2741  |

  If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table cache size.

What does 'very large' mean?  What does 'too rapidly' mean?

In my case I have open_table_cache set to 10,240 and table_open_cache_instances set to 16.  Anytime I query opened_tables it is higher than the last time.  Just now it read 122,261.

My DB has 2600 tables and maybe 300 connections regularly.  Any thoughts on how I might know what open_table_cache should be?

You have not mentioned but looks like you are using MySQL 5.6 version.
Opened_tables is a counter variable which stores the value of all tables opened by queries since start of the MySQL service.

What does 'very large' mean?

This Value can be grown up to few millions.

What does 'too rapidly' mean?

To check whether this value is growing rapidly or not, just run the SHOW GLOBAL STATUS LIKE 'Opened_tables'; query at the interval of each second. You will be able to see how many tables are being opened per second?

There is not a single variable or factor which is responsible for open_table_cache or table_open_cache.
(1) open_files_limit: MySQL has clearly mentioned in its documentation about it - Increasing this value increases the number of file descriptors that mysqld requires.
As per the storage engine, MySQL maintains file descriptors for each table. So, you must need to check system variables open_files_limit. This link provide you importance about this variable. Luckily, this variable can be increased or decreased in MysQL5.6 on Windows server. At the start of the service, MySQL automatically adjust this value <= specified value of open_files_limit.

(2) Query Execution Plan: What is your query and how many tables are getting used? How efficient indexes are getting utilized during query execution.

(3) Table Size: Number of records in a table. How big or small it is?

(4) RAM: RAM is the physical memory and cache size depends upon the RAM size. In performance of and application as well as speed up the query execution, it plays a vital role.

This is not the exact answer but a helpful suggestion.


Keep a history of transactions for easy rollback Is there a database (either SQL or NoSQL) which allows you to keep a history or log of all past transactions so that you can easily rollback to any given point in time (similar to how git manages a source repository)? Taking a checkpoint after every transaction probably will do the same job, but keeping a history looks more efficient.

SQL Server with database in FULL recovery model permits you to do a point-in-time restore.

There is no need in "Taking a checkpoint after every transaction" because of write ahead logging.

To know more about it you can read the followin article: SQL Server point in time restore

  Overview

  The RESTORE ... WITH STOPAT option allows you to restore your
  database to a point in time.  This gives you the ability to restore a
  database prior to an event that occurred that was detrimental to your
  database.  In order for this option to work, the database needs to be
  either in the FULL or Bulk-Logged recovery model and you need to be
  doing transaction log backups.

And here is the official documentation:
Restore a SQL Server Database to a Point in Time (Full Recovery Model)


Indexing the difference between two columns in MySQL Is there a way to optimize/index a query that sorts the difference between two columns.  I have a modest sized table of 5 million rows that I'm trying to do the following:

SELECT pk_column, column_x, column_y, column_x-column_y
FROM table_a
ORDER BY column_x-column_y DESC

This particular query takes about 2 seconds, however this ORDER BY seems to create a fairly large bottleneck in more complex queries.

You could try MariaDB which supports virtual columns.

  Indexes are partially supported. Virtual columns do not support
  primary keys and indexes can only be based on PERSISTENT virtual
  columns. PERSISTENT columns can be part of a foreign key and can be
  referenced by foreign keys, but ON UPDATE CASCADE, ON UPDATE SET NULL,
  ON DELETE SET NULL are not allowed.

You could simulate this through your INSERTs or via TRIGGERs (better IMHO) and index the simulated virtual column. Then test to see does that resolve your issue (or at least improve it).

[EDIT in response to the OP's comment]

Yes, indeed, I thought that MariaDB would be a "bridge too far". I really included it for completeness. MariaDB is a drop-in replacement up and including 5.5 (in MariaDB terms). AFAICS, the 10 series is still compatible (it's like MySQL with bits added, but the divergence will only widen in the future AIUI.

The advantage of using a persisten column is that the persistent column can be indexed whereas the virtual one can't according to the docco that I quoted.

Has the idea of "simulated" virtual columns worked for you?