Tweaking indexes for tables with "geographical" data We have quite a large table in our database which looks something like this:

CREATE TABLE `nests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pokemon_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `latitude` varchar(64) NOT NULL,
  `longitude` varchar(64) NOT NULL,
  `is_cluster` int(11) NOT NULL,
  `is_repeater` int(11) NOT NULL,
  `status` int(11) DEFAULT NULL,
  `spawn_type` int(11) DEFAULT NULL,
  `current_species` int(11) DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `current_species` (`current_species`),
  KEY `spawn_type` (`spawn_type`),
  KEY `status` (`status`),
  KEY `latitude` (`latitude`),
  KEY `pokemon_id` (`pokemon_id`)
) ENGINE=InnoDB AUTO_INCREMENT=76214 DEFAULT CHARSET=latin1;

This table basically stores user submitted data and is polled aggressively to find results within a bounding box. Here's an example of a few of the queries which run against this table.

SELECT `id`,
       `current_species`,
       `status`,
       `spawn_type`,
       `latitude`,
       `longitude`
FROM `nests`
WHERE latitude <= 50.953560763747
  AND latitude >= 50.898161073089
  AND longitude <= 7.0303058624268
  AND longitude >= 6.8912601470947
  AND `spawn_type` IN (1, 2, 3, 0)
  AND `status` IN (1, 2)
LIMIT 201;

The table, today, has 76,000 rows and when running an EXPLAIN for that query it seems to narrow by very little and keys off of status.

           id: 1
  select_type: SIMPLE
        table: nests
         type: range
possible_keys: spawn_type,status,latitude
          key: status
      key_len: 5
          ref: NULL
         rows: 54288
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

We have a few ideas, but since rolling out this query we've had to create A LOT of MySQL read slaves because of how long it takes to run these queries.

The two things I've looked into so far are to create a new index (and remove a bunch more) where we take latitude, remove the decimal and flatten to the first 5 ints. So, a latitude of 50.953560763747 becomes 50953 and use this column to key off of.

The second thing I've started looking into is GEOSPATIAL POINT type and key off of that. However, I understand that would require us changing from InnoDB to MyISAM and I'm not sure if it'll help as I have very little experience in GEOSPATIAL column/data.

The final thought I had on this was to break out the lat/long data into it's own MyISAM table with an id and a POINT, then create a foreign key relation to the nests table. Thereby querying the geospatial data and inner join that onto nests for final filters.

A couple of things to mention about this query:

There is no need to use MyISAM.  InnoDB in MySQL 5.7+ supports geospatial types, and rtree indexes.
Independent of the above, this is a key use case for composite indexes.  Possibly: ALTER TABLE nests ADD INDEX s_s_l (status, spawn_type, latitude).  You will not be able to effectively use latitude and longitude in the same index, as the remainder of the composite index can not be used after a ranged condition.

It looks like there is some doubt in the comments that this works.  Let me show the EXPLAIN output:

ALTER TABLE nests DROP INDEX status, DROP INDEX latitude, DROP INDEX spawn_type;
ALTER TABLE nests CHANGE latitude latitude DECIMAL(10, 8) NOT NULL;
ALTER TABLE nests CHANGE longitude longitude DECIMAL(10, 8) NOT NULL;
ALTER TABLE nests ADD INDEX s_s_l (status, spawn_type, latitude);

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "s_s_l"
      ],
      "key": "s_s_l",
      "used_key_parts": [
        "status",       # All parts of the key
        "spawn_type",   # are used, including
        "latitude"      # the last part.
      ],
      "key_length": "15",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "11.11",
      "index_condition": "((`test`.`nests`.`latitude` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 50.898161073089) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "19.03",
        "eval_cost": "0.18",
        "prefix_cost": "19.21",
        "data_read_per_join": "49"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ],
      "attached_condition": "((`test`.`nests`.`longitude` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 6.8912601470947))"
    }
  }
}

An index on (s,s,l,l) only uses the index for (s,s,l), but applies Index Condition Pushdown for the remaining (l):

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "19.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "s_s_l_l"
      ],
      "key": "s_s_l_l",
      "used_key_parts": [
        "status",
        "spawn_type",
        "latitude"  # last part of key not used
      ],
      "key_length": "20",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "11.11",
      "index_condition": "((`test`.`nests`.`latitude` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 50.898161073089) and (`test`.`nests`.`longitude` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 6.8912601470947) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "19.03",
        "eval_cost": "0.18",
        "prefix_cost": "19.21",
        "data_read_per_join": "49"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

And l_s_s is only effective for filtering (l), with ICP again used for (s,s):

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12.21"
    },
    "table": {
      "table_name": "nests",
      "access_type": "range",
      "possible_keys": [
        "l_s_s"
      ],
      "key": "l_s_s",
      "used_key_parts": [
        "latitude"  # Only first part of key used
      ],
      "key_length": "15",
      "rows_examined_per_scan": 8,
      "rows_produced_per_join": 0,
      "filtered": "0.89",
      "index_condition": "((`test`.`nests`.`latitude` <= 50.953560763747) and (`test`.`nests`.`latitude` >= 50.898161073089) and (`test`.`nests`.`spawn_type` in (1,2,3,0)) and (`test`.`nests`.`status` in (1,2)))",
      "cost_info": {
        "read_cost": "12.20",
        "eval_cost": "0.01",
        "prefix_cost": "12.21",
        "data_read_per_join": "3"
      },
      "used_columns": [
        "id",
        "latitude",
        "longitude",
        "status",
        "spawn_type",
        "current_species"
      ],
      "attached_condition": "((`test`.`nests`.`longitude` <= 7.0303058624268) and (`test`.`nests`.`longitude` >= 6.8912601470947))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)


invalid default value (mysql 5.7) for timestamp field EDIT:
When updating an existing database from mysql 5.6 and executing:

UPDATE phppos_register_log SET shift_end = '2015-01-01 00:00:00' WHERE shift_end = '0000-00-00 00:00:00';

This produces:

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'shift_end' at row 1
#1067 - Invalid default value for 'shift_start'

This worked in mysql <=5.7. I couldn't find any documentation about this...What is the issue with this?

CREATE TABLE `phppos_register_log` (
      `register_log_id` int(10) NOT NULL AUTO_INCREMENT,
      `employee_id_open` int(10) NOT NULL,
      `employee_id_close` int(11) DEFAULT NULL,
      `register_id` int(11) DEFAULT NULL,
      `shift_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `shift_end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `open_amount` decimal(23,10) NOT NULL,
      `close_amount` decimal(23,10) NOT NULL,
      `cash_sales_amount` decimal(23,10) NOT NULL,
      `total_cash_additions` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
      `total_cash_subtractions` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
      `notes` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `deleted` int(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`register_log_id`),
      KEY `phppos_register_log_ibfk_1` (`employee_id_open`),
      KEY `phppos_register_log_ibfk_2` (`register_id`),
      KEY `phppos_register_log_ibfk_3` (`employee_id_close`),
      CONSTRAINT `phppos_register_log_ibfk_1` FOREIGN KEY (`employee_id_open`) REFERENCES `phppos_employees` (`person_id`),
      CONSTRAINT `phppos_register_log_ibfk_2` FOREIGN KEY (`register_id`) REFERENCES `phppos_registers` (`register_id`),
      CONSTRAINT `phppos_register_log_ibfk_3` FOREIGN KEY (`employee_id_close`) REFERENCES `phppos_employees` (`person_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

since mysql 5.7, SQL_MODE default value is strict, please view this page:
https://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field


Relationship from Orders to Shop and Supplier, or to general User? I have a platform where

a User can have a Shop, which can place Orders
a User can be a Supplier, which can see Orders from Shops coming in.

I have created the following diagram depicting the structure I have modeled so far:

Is that a good schema? Or, would it make more sense to couple the relationships to the User straight away?

I'm guessing that will cause more overhead in my backend? (Laravel - PHP)

Thank you!

Following are my comments:

Users info should be isolated from Other Entities like shop, Supplier etc
Association between User and Shop should be moved to User_Shop table
Association between User and Supplier should be moved to User_Supplier table
There should be association between Shop and Supplier to view the Order from specific Shop by Supplier in the table Shop_Supplier.
There should be a table namely Order_Fulfil to map the orders fulfilled by Suppliers
Not sure about the mapping between the Product and User. Ideally there should be mapping between shop and product and order and Product.


Name of ForeignKey Back Relation? Table detail has a ForeignKey to table master.

How to call the back relation?

  master has ??? to table detail

I could not find a canonical word in wikipedia or other resources.

In the terminology used to to talk about conceptual design of database, this is called inverse relationship. So you could say:

  master has an inverse relationship with table detail.

See for instance: Fundamentals of database systems, by R. Elmasri, S. Navathe, Addison Wesley, ISBN: 978-0-13-608620-8, Chapter 11, Object and Object Relational Databases.


"Recheck Cond:" line in query plans with a bitmap index scan This is a spin-off from comments to the previous question:

Postgres 9.4.4 query takes forever

Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query plans output by EXPLAIN.

Like in the EXPLAIN output of the referenced question:

->  Bitmap Heap Scan on table_three  (cost=2446.92..19686.74 rows=8159 width=7)
      Recheck Cond: (("timestamp" > (now() - '30 days'::interval)) AND (client_id > 0))
      ->  BitmapAnd  (cost=2446.92..2446.92 rows=8159 width=0)
            ->  Bitmap Index Scan on table_one_timestamp_idx  (cost=0.00..1040.00 rows=79941 width=0)
                  Index Cond: ("timestamp" > (now() - '30 days'::interval))
            ->  Bitmap Index Scan on fki_table_three_client_id  (cost=0.00..1406.05 rows=107978 width=0)
                  Index Cond: (client_id > 0)

Or in the output of EXPLAIN ANALYZE for a simple, huge table (with very little work_mem):

EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000;

Bitmap Heap Scan on aa  (cost=107.68..4818.05 rows=5000 width=4) (actual time=27.629..213.606 rows=100001 loops=1)
  Recheck Cond: ((a >= 100000) AND (a <= 200000))
  Rows Removed by Index Recheck: 758222
  Heap Blocks: exact=693 lossy=3732
  ->  Bitmap Index Scan on aai  (cost=0.00..106.43 rows=5000 width=0) (actual time=27.265..27.265 rows=100001 loops=1)
        Index Cond: ((a >= 100000) AND (a <= 200000))

Does that mean index conditions have to be checked a second time after a bitmap index scan?
What else can we learn from the EXPLAIN output?

As @Chris commented correctly on the referenced question:

a little investigation seems to indicate that the recheck condition is
always printed in the EXPLAIN, but is actually only performed when
work_mem is small enough that the bitmap becomes lossy. Thoughts?
http://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com

While this is all true and the core developer Heikki Linnakangas is a first class source, the post dates back to 2007 (Postgres 8.2). Here is a blog post by Michael Paquier with detailed explanation for Postgres 9.4, where the output of EXPLAIN ANALYZE has been improved with more information.
The Recheck Cond: line is always there for bitmap index scans. The output of basic EXPLAIN won't tell us more. We get additional information from EXPLAIN ANALYZE as can be seen in the second quote in the question:
Heap Blocks: exact=693 lossy=3732

From a total of 4425 data pages (blocks), 693 stored tuples exactly (including tuple pointers), while the other 3732 pages were lossy (just the data page) in the bitmap. That happens when work_mem is not big enough to store the whole bitmap built from the index scan exactly (lossless).
The index condition has to be rechecked for pages from the lossy share, since the bitmap only remembers which pages to fetch and not the exact tuples on the page. Not all tuples on the page will necessarily pass the index conditions, it's necessary to actually recheck the condition.
This is the thread on pgsql hackers where the new addition was discussed. The author Etsuro Fujita provides a formula for how to calculate the minimum work_mem to avoid lossy bitmap entries and ensuing condition rechecks. The calculation is not reliable for complex cases with multiple bitmap scans, so it was not used to output actual numbers from EXPLAIN. It can still serve as an estimate for simple cases.
Additional line BUFFERS:
In addition, when running with BUFFERS option: EXPLAIN (ANALYZE, BUFFERS) ... another line is added like:
Buffers: shared hit=279 read=79

This indicates how much of the underlying table (and index) was read from the cache (shared hit=279) and how much had to be fetched from disk (read=79). If you repeat the query, the "read" part typically disappears for not-too-huge queries, because everything is cached now after the first call. The first call tells you how much was cached already. Subsequent calls show how much your cache can handle (currently).
There are more options. The manual about the BUFFERS option:

Specifically, include the number of shared blocks hit, read,
dirtied, and written, the number of local blocks hit, read, dirtied,
and written, and the number of temp blocks read and written.

Read on, there's more.
Here is the list of output options in the source code.


PostgreSQL query slows due to ORDER BY even with an index I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes about 2 seconds on our data set.

SELECT off.provider_id, st_AsText(off.area), st_area(off.area)
FROM offices off
WHERE st_contains(off.area, :location)
ORDER BY st_area(off.area)
LIMIT 10 OFFSET :offset;

When this first happened I realized, "Duh! there's no index"; ST_area is computing the area covered by a geospatial region and since I'm ordering on it, the optimizer must be checking all containment-filtered results at query time.

CREATE INDEX offices_st_area_idx
ON public.offices
USING btree
(st_area(area));

This addition had no effect on query time. Is there a faulty assumption I'm making here? Anybody have an idea of how I can speed this query up? I'd like to render this view without having to resort to a 'loading' page.

If you don't have an ORDER BY clause the database takes any 10 records that meet your criteria and returns them. With ORDER BY it first finds all rows that meet criteria and then orders them.

You could try enabling btree-gist and then creating index on the spatial column and the area

CREATE INDEX offices_st_area_idx
ON public.offices
USING gist (area, st_area(area));

But otherwise then that it's a normal question regarding performance and depends heavily on your data and server settings.


PostgreSQL dump distinct column without memory consumption I'm trying to dump csv of distinct varchar(43) column to file, table has around billion rows, select distinct command either runs out of memory and command is aborted or server just closes all connections. Does PostgreSQL have built in methods for such task?

psql (10.9 (Ubuntu 10.9-0ubuntu0.18.10.1)) 16GB RAM

Solution suggested by jjanes worked, run set enable_hashagg=off in psql terminal


Why take full backups when you can do COPY_ONLY? With a SQL Server 2012 availability group, you can only take COPY_ONLY full backups on replica. You can make regular BACKUP LOG.

As it's possible to restore logs backups after a database restoration from a COPY_ONLY dump, do we really need to take full (non-COPY_ONLY) backups?

Yeah, it creates a new LSN sequence, but if we're not doing DIFFERENTIAL backups, is it something we have to think about?

By using only COPY_ONLY backups, I would hope to gain the ability to perform all backups on the read-only replica. My AG is asynchronous, so the backups might be behind the primary, but that is an acceptable risk.

I do take log backups. According to my tests, I'm able to restore any log backup on a database (restored from a COPY_ONLY or not) as soon as the log backup LSN is more recent than the full backup. The full backup changes the database_backup_lsn, the COPY_ONLY doesn't, but it seems it doesn't affect log restore. It seems I just can't restore DIF backups, but I don't need that.

There's a good explanation in the dba.se Q &A:

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

...but it does not answer my question. For now, my conclusions are: Except for the first backup (without which you will not be able to take log backups), we just can work with COPY_ONLY backups if we don't use differentials.

My (general) question is: Do we really need non-COPY_ONLY backups if we don't take differentials, and if yes, why?

As long as you don't care that:

in the async model, the REDO queue may fall way behind, so the backups on your secondary may not represent the point in time that you think they would if they were taken on the primary.
you will not be able to use DIFFERENTIAL backups in this model.
in the event of a restore, you will have to manually determine which log backups contain the LSN ranges you'll need to restore on top of the chosen COPY_ONLY backup and, given the above, the timestamps on the bak/trn files may not be accurate enough to determine the set of files and sequence you need.
backup history will not be recorded centrally.

Also, I still think there are going to be scenarios where this ultra-long log chain, or relying on a COPY_ONLY backup taken in between log backups, are going to make it difficult to restore. Personally, I would be keen to perform a full, proper backup on the primary on some regular interval, say once a week. YMMV.


Is it good practice to check for existence of records before deleting them I've came across the following SQL/PLSQL while investigating a stored procedure written by someone else:

1. V_COUNT NUMBER(5,0);
.........
2. SELECT COUNT(*) INTO V_COUNT FROM TABLE1 WHERE COL1='SOMEVALUE';
3. IF V_COUNT>0 THEN
4.   DELETE FROM TABLE1 WHERE COL1='SOMEVALUE';
5. END IF;

Isn't only the DELETE statement in the line 4 sufficient to cover all the logic that's meant with these 5 lines? I first thought maybe V_COUNT is used somewhere else, but it's not. I can't think of any good reason in making a simple delete statement so complex. Do you also think it's a bad design or is there a point that I can't see?

I would just delete.

Even if I had to only delete if there were say exactly 2 records, then I would add this condition to the where clause, like

DELETE FROM table1
WHERE col1='somevalue'
AND (SELECT COUNT(*) FROM table1 WHERE col1 = 'somevalue') = 2;

In all cases when writing a delete statement consider that in general the where clause is usually either

restricting to a single row using a key;
restricting to a set of rows using a flag/code/status column; or
restricting to a set of rows using a range of values -- often dates.


Invalid default value for DateTime when changing to utf8_general_ci I'm trying to run a script to modify a database, this query

ALTER TABLE cmContentVersion CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

gives this error

ERROR 1067 (42000) at line 68: Invalid default value for 'modifiedDateTime'
Warning (Code 1264): Out of range value for column 'modifiedDateTime' at row 1
Error (Code 1067): Invalid default value for 'modifiedDateTime'

the column modifiedDateTime is defined as

`modifiedDateTime` datetime NOT NULL default '0000-00-00 00:00:00',

Question: Is there a valid "invalid" default value that can replace 0000-00-00 00:00:00 when using utf8_general_ci. Or do I have to tell the developers to stop relying on an "invalid date" in their code?

This looks like more of a SQL mode issue than the char set. Strict mode (and more specifically  NO_ZERO_DATE which is part of strict mode) usually sets off that error if the table was created with an all zero default date before turning on strict.

What we use (for similar modified date columns), is the '1970-01-01 00:00:01'.

and on a slightly related note, we use timestamp for these columns (and for created time too). Takes half the storage space, and is faster access.


Is it possible to recover day's worth of queries from sql server log file? I lost all data entered and edited in my system yesterday, and my ISP has announced to me that last night's backup is corrupt and cannot be recovered.

Is there any way to access the database's log file to try and recover the database to the point immediately before the day's data was deleted?  I'm using SQL Server 2012 Standard Edition.

I've tried this (which I found in another question on this site):

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

but I'm only getting queries since this morning.

I'd be immensely grateful for any help.

Your only real chance is using a 3rd-party log analyzer tool, and even that may not work. SIMPLE recovery truncates the log when a CHECKPOINT process occurs, which is highly likely to have happened at this point.

Also, if losing the entire set of changes between backups is unacceptable, either back up more frequently in SIMPLE (if your database is small), or switch to FULL and take log backups as well to get point-in-time recovery ability. Corrupt backups notwithstanding.


JDBC Connection on Current Primary Database After Switchover: ORA-01033 encountered We are using Oracle Data Guard and currently testing Disaster Recovery scenarios, which is starting the application after switching over from the Primary Database to the Secondary Database.

I am using the following connection string as per Oracle Documentation:

(DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD_DB))
    )

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DR_DB))
    )

)

The scenario is that we are now switched over to the secondary database, so the application should now connect to DR_DB. However, when we try to connect, it still connects to the PROD_DB, because we're getting the following exception:

ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

This may be answered already in some threads, and I may not have searched hard enough, but I also do not know if I am expecting the correct result.

I do not expect the application's database connection to stay alive after a switch over; I only want to avoid reconfiguring the application whenever we do DR, and I expect that the above connection string will connect to whichever is the primary database. Is this correct?

Thank you guys for your help.

And that is why we use role-based services instead of the default service derived from instance_name and db_unique_name.

If your primary database is called PROD_DB (db_unique_name), there will be  service with that name at the primary site, and that service will be available even after a failover or switchover. The listener receives your request, sees that you are requesting to connect using the service PROD_DB, and forwards your request to the instance(s) that provide that service. The instance is in MOUNT stage, and you receive ORA-01033.

Create the role based service at both sites (replace -db and -service values with your current ones):

srvctl add service -db ORCL -service myservice -role primary
srvctl start service -db ORCL -service myservice -role primary

The start step will fail at the standby site, and the service remains in INTERMEDIATE state, but that is normal.

With this, the service is automatically relocated (stopped at old primary and started at new primary) when you perform a role transition with Data Guard broker.

And use this service for connecting:

(DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice))
    )

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice))
    )

)

When you try to connect using the service myservice, and your client tries the first entry (old primary, currently standby), the listener refuses the connection as there will be no myservice service running at that site, and your client tries the next available address.


How To Restore A Replicated Database I am replicating our live database using transaction replication. This is basically to have a backup copy of our database in the case of server failure.

The replicated database is read only, so I backed it up, restored it, and tested it.

I noticed straight away that the identity columns didn't work.

For instance, the original schema was like this:

CREATE TABLE [dbo].[Inspection](
[InspectionID] [int] IDENTITY(1,1) NOT NULL,
[CarID] [int] NOT NULL,

Then, on the replicated copy, it looks like this:

CREATE TABLE [dbo].[Inspection](
[InspectionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CarID] [int] NOT NULL,

That means the identity columns are disabled, right?

Is there a way to get around this and make this database an independent copy in its own right?

Also, I noticed that the foreign keys are not propgated. I am aware that there are settings for this, but will propogating the foreign keys be a problem now that the identity columns are disabled (which are the primary keys)?

If you want a standby copy of your data for HA/DR then is much better to use log shipping or database mirroring. Since these technologies create a physical database copy, you avoid exactly the problem(s) you mention. Also, schema changes 'replicate' much easier.

If you insist on transactional replication, seed your replicas with a backup instead of a snapshot. See How to: Initialize a Transactional Subscription from a Backup for a step-by-step guide.


SQL Availability group IO prioritisation How does SQL Server handle IO in terms of availability group secondaries?
To give an example scenario:
A 2 node cluster with 2 Availability groups
Each AG is primary on a different node.
Therefore each node is a primary for one AG but a secondary for the other.
Will IO for the primary workload be prioritised over the replicated data, assuming all disks are shared?
Also would this differ if the secondary was in sync or async mode?

Will IO for the primary workload be prioritised over the replicated data, assuming all disks are shared?

No, I have not heard any such thing that SQL Server will prioritize certain IO over other. You have an option to set High Priority for SQL Server by using sp_configure but it is not recommended and mostly counter productive.


String that contains letters representing a negative number I would like for every string that has a letter on the end make it a negative value.

<field> = '00000000014545p';--Varchar(15)
<field> = '00000000012645x';--Varchar(15)
<field> = '00000000012345p';--Varchar(15)
<field> = '000000004512345';--Varchar(15)

SELECT
    SUM(CAST(<field> AS int))

FROM <table> WHERE <field> IN (38857,45678)

I want a return the sum of the fields.

Update: Found out that this was programmed using "over punch dibol ascii" This is why the letters are in this. p=0,q=1,r=2,s=3,t=4,u=5,v=6,w=7,x=8,y=9
Letter means *-1

You should really consider fixing this data before it gets into your tables, because working around bad data is cumbersome.

DECLARE @x TABLE(col VARCHAR(15));

INSERT @x(col) VALUES
('00000000014545p'),('00000000012645n'),
('00000000012345p'),('000000004512345');

;WITH x AS
(
  SELECT col, switch = CASE WHEN RIGHT(col,1) NOT LIKE '[0-9]' THEN 1 END
  FROM @x
)
SELECT SUM(CONVERT(INT, LEFT(col,
  CASE switch WHEN 1 THEN LEN(col)-1 ELSE LEN(col) END))
   * CASE switch WHEN 1 THEN -1 ELSE 1 END)
FROM x
-- WHERE ...;


SQL Server - WITH REPLACE What is the difference when using or not using "With Replace" when restoring database. I know that With Replace, I would get identical database as backup file even log file would be replaced/overwritten and current content / transactions would be broken.

But, what is actual difference; I read somewhere that some configuration for database like recovery model is stored in Master database; With Replace - it can be overwritten! What else? I cannot find much on what is the use of With Replace? Why it is used a lot, most of the developer restore databases using "With Replace".

I want to clear my concept of restore database.

Thanks

Using WITH REPLACE allows you to overwrite the DB without backing up the tail log, which means you can lose commited work.

Additionally, if you use WITH REPLACE you can, and will, overwrite whatever database you are restoring on top of. This means you could restore a database over another database that is used for something completely different, which can be quite dangerous if you aren't careful.

From the docs:

  REPLACE should be used rarely and only after careful consideration.
  Restore normally prevents accidentally overwriting a database with a
  different database. If the database specified in a RESTORE statement
  already exists on the current server and the specified database family
  GUID differs from the database family GUID recorded in the backup set,
  the database is not restored. This is an important safeguard.

For example, you backup Server1.DB1 and then restore it to Server2.DB2 but DB2 in this case is NOT an old restore from DB1. It's actually a completely different database used for something completely different. WITH REPLACE would replace DB2 with DB1 despite them not being the "same" database.


SQL Partioning on date, how does it work for future records? I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have some partitioned tables for data older than that. Have not chosen mysql or sql server express as not sure which one has the desired capabilities.

I have read some examples on how partitioning on ranges works as seen below:

CREATE TABLE SAMPLE_ORDERS
(
    ORDER_NUMBER NUMBER,
    ORDER_DATE DATE,
    CUST_NUM NUMBER,
    TOTAL_PRICE NUMBER,
    TOTAL_TAX NUMBER,
    TOTAL_SHIPPING NUMBER
)
PARTITION BY RANGE(ORDER_DATE)
(
    PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2010’, ‘DD-MON-YYYY’),
    PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2011’, ‘DD-MON-YYYY’),
    PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2012’, ‘DD-MON-YYYY’)
);

Read more at http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/1/#YCTAmfR0hkvo1at0.99

But this seems to be more for partitioning older data. What happens when it reaches newer data? Will it put the new data into a single partition?

I would rather have a small amount of data in the new table and dump all the old information.

Do I just need to create multiple partitions up to 2020, and keep manually adding them as the project runs?

Is there another way to do this?

I know I could achieve what I need by replicating all the data into another table, and cleaning up the old data every night, however I have had issues with this before and was wondering if partitioning would do a better job?

In SQL Server you have to split the partition at the head of the table:

ALTER PARTITION SCHEME partition_scheme_name NEXT USED [ filegroup_name ];
ALTER PARTITION FUNCTION partition_function_name() SPLIT RANGE ( '20120401');

This will add a new partition for new incoming data. You should always an empty partition at the head of the table to make the split a instant metadata-only operation. If you populated the last partition with rows then splitting has to move data.


PostgreSQL's Statistics Collector I am having trouble understanding the Postgresql's Statistics Collector.

Here is the official page of describing the pg_stat_database view.

I performed query on the pg_stat_database view,

 postgres=# select tup_inserted, tup_fetched from pg_stat_database;
 tup_inserted | tup_fetched
--------------+-------------
            0 |           0
            0 |           0
            0 |      425511
            0 |      407918
            0 |           0
         1011 |      413373
          324 |      360500
         1167 |      345411
         1139 |      346655
          787 |      342410
         1049 |      328089
            0 |      204959
         1646 |      231987
          892 |      205624
           41 |       66538
            0 |       41609
          838 |     2591785
(17 rows)

But these are ridiculous numbers. Even If I combine all my databases' rows, still they won't be equal to this.

What data does this actually tells us ?
And moreover I have not used these databases for weeks, means it is showing me old data.

I want to monitor the number of queries being performed, memory used, clients connected etc.

Is there any kind of tool to monitor my postgresql database ?

You actually have two different questions here. With respect to the numbers shown in pg_stat_database, most of them are cumulative since the last time they were reset, e.g. by pg_stat_reset() or friends. So, for example, if you frequently query a one-row table you will eventually accumulate high numbers of tup_fetched and blk_read_time.


Copying the value of ROWVERSION to another column in same row on insert While pondering this question Is there a function like min_active_rowversion for Identity?, I thought about another possible solution for my problem - using ROWVERSION as primary key (and monotonically increasing sequence number).

Of course nobody thinks that's a good idea as ROWVERSION changes all the time, on updates to the row.

I wonder though, if it's possible to copy the ROWVERSION value to another column on insert. This column would not be updated on update and could thus be used as  primary key and sequence number?

I do not think that there is a clean solution to add the rowversion value in the same insert statement. Here are some reasons as to why I would think that

You can create a Binary(8) column and store the MIN_ACTIVE_ROWVERSION() in there, but among other issues, multiple inserts will fail.

CREATE TABLE dbo.ID_Test_rowver
(
Rowver rowversion NOT NULL,
EmpName nvarchar(50),
Rowver2 binary(8) PRIMARY KEY NOT NULL
);

INSERT INTO ID_Test_rowver(EmpName,Rowver2)
VALUES('Emp3',CONVERT(BIGINT,MIN_ACTIVE_ROWVERSION()));

Validating the values

Rowver              EmpName Rowver2
0x00000000000007E3  Emp3    0x00000000000007E3

Run an update

UPDATE dbo.ID_Test_rowver
SET EmpName = 'Emp4'

Validating

Rowver              EmpName Rowver2
0x00000000000007E4  Emp4    0x00000000000007E3

Inserting multiple records is going to fail

INSERT INTO dbo.ID_Test_rowver(EmpName,Rowver2)
SELECT EmpName, MIN_ACTIVE_ROWVERSION()
FROM  dbo.ID_Test_rowver;

  Violation of PRIMARY KEY constraint 'PK__ID_Test___51A71CFE5E24148F'.
  Cannot insert duplicate key in object 'dbo.ID_Test_rowver'. The
  duplicate key value is (0x00000000000007e9).

Because it is calculated once per set, not per row.

An after insert trigger to update the column

CREATE TRIGGER RowverTrigger
ON dbo.ID_Test_rowver
AFTER INSERT
AS
UPDATE RT
SET RT.Rowver2 = I.RowVer
FROM dbo.ID_Test_rowver RT
INNER JOIN INSERTED I
ON I.RowVer = RT.Rowver;

But you would not be able to do that with a primary key constraint unless you have a unique placeholder value.

CREATE TABLE dbo.ID_Test_rowver
(
Rowver rowversion NOT NULL,
EmpName nvarchar(50),
Rowver2 binary(8)
);

And, as a side effect of this update, the rowversion column is updated again.

Rowver              EmpName Rowver2
0x00000000000007EF  Emp3    0x00000000000007ED

Changing the query to do an insert + Update in would get the same end result as the trigger.

BEGIN TRANSACTION
INSERT INTO dbo.ID_Test_rowver(EmpName)
VALUES('Emp5'),('Emp6')

UPDATE RT
SET RT.Rowver2 = RT.RowVer
FROM dbo.ID_Test_rowver RT
Where Rowver2 IS NULL
COMMIT TRANSACTION

Another option would be Row by row inserts with an instead of trigger.

CREATE TRIGGER RowverTrigger
ON dbo.ID_Test_rowver
INSTEAD OF INSERT
AS
BEGIN
        DECLARE @count INT ;
        DECLARE @counter INT ;

        SELECT  @count = COUNT(*) ,
                @counter = 0
        FROM    inserted ;

        SELECT  * ,

                ROW_NUMBER() OVER ( ORDER BY ( SELECT   1
                                             ) ) AS TriggerRowNumber
        INTO    #inserted
        FROM    inserted ;
        WHILE @counter < @count
            BEGIN
                INSERT  INTO dbo.ID_Test_rowver
                        ( EmpName ,
                          Rowver2
                        )
                        SELECT  EmpName ,
                                @@DBTS + 1
                        FROM    #inserted
                        WHERE   TriggerRowNumber = @counter + 1 ;
                SET @counter = @counter + 1 ;
            END
 END

Based on Listing 9 from this source

Note that I had to use @@DBTS + 1 here, because MIN_ACTIVE_ROWVERSION() was not deemed active inside the loop.

Among other considerations, performance of this will be very slow depending on the amount of inserted records.

When considering these examples, you would have to choose between

Not being able to do multi value inserts.
Row by row inserts.
Not having a primary key.

Those choises are really not the choices I would like to make.

Not to say that somebody more knowledgable than me might give you a better solution


Slow SQL Server authentication, but fast SAN, fast querying? I am asking you after days of research but I think I don't have required skills to understand what is happening.

I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the linked application. Sometimes up to 20s for a simple SQL auth (not windows or kerberos or AD)
6GB of RAM, W 2008 R2, virtualized on VMWare.

There is a separate drive for TempDB and for the Main DB, both on a fast SAN behind fiber channel.

Queries (once authentication is ok) are quite fast (few micro seconds up to 10 s, average around 15ms).

I have the following data :

MSDB on C:
86% of 32GB
io_stall_read_ms : 2340428
num_of_reads : 45062
avg_read_stall_ms : 51.9
io_stall_write_ms  : 192217
num_of_writes : 34392
avg_write_stall_ms : 5.6
io_stalls : 2532645
total_io  : 79454
avg_io_stall_ms : 31.9

Main DB : on E:
94 % of 250GB

io_stall_read_ms : 556848206
num_of_reads : 46555392
avg_read_stall_ms : 12.0
io_stall_write_ms : 2213187501
num_of_writes : 62730741
avg_write_stall_ms : 35.3
io_stalls : 2770035707
total_io : 109286133
avg_io_stall_ms : 25.3

Master on C:,
io_stall_read_ms : 11966
num_of_reads : 2229
avg_read_stall_ms : 5.4
io_stall_write_ms : 240
num_of_writes : 104
avg_write_stall_ms : 2.3
io_stalls : 12206
total_io : 2333
avg_io_stall_ms : 5.2

Well, I don't know which column to look at, but C looks like a slow drive isn't it ?

WAITS are the following :
Oledb   28 %
Pageiolatch Ex  18 %
Writelog    13 %
Pageiolatch Sh  10 %
Cxpacket    8 %
Sos Scheduler Yield 5 %
Sleep Bpool Flush   4 %
Backupbuffer    1 %
Io Completion   1 %
Preemptive Os Authenticationops 1 %
Preemptive Os Waitforsingleobject   0 %
Backupio    0 %
Async Io Completion 0 %

My understanding is that Pageiolatch and Writelog indicates a slowness around IO completion , is that right ?

IO over C drive is quite calm : 1% of data Read / Write over 24hours, no peaks.
1 or 2 Read / Write per second, of a few KB.

Only Main Db (E:) is heavily used : 100 Reads / sec, 130 writes / sec.

Memory usage : 100 % of 6GB, swap file of 6GB full . (from what I've read, it is perfectly normal).

Well, What do you think slowness over authentication can come from then ?

Thanks a lot for your help,

S.

Here is your answer:

MSDB on C:
86% of 32GB
io_stall_write_ms : 192217

If MSDB is on c: master is on c: as well and I'm almost willing to bet that tempdb is still in the default location as the average I/O stall on c is 19 seconds!

First, check if tempdb is on c:\ or has any file on the c:\ partition and if so move it away

Check if the master database has some free space - Usually you dont need to tune it as very little is written to the master database, unless if someone has created objects in it

Then make space in the MSDB database is way to big which can cause a lot of troubles, please remove as much backup and job history from it database as you can.

You can do all these checks very simply by downloading and running sp_blitz


Referencing system views in SSDT? I've imported a database into SSDT containing a reference to a system view (specifically, sys.columns). The problem is, that I then get warnings about unresolved references when I build the project

From what I've seen on MSDN forums, it looks like it may be a known issue: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/5a7026bd-0602-42e6-a639-d73bed903c26

Now, I know I can switch the warning off or ignore it, but does anyone know of an actual solution?

Thanks

This should be handled by adding a reference to the master database.

Previously, we've had issues related to the relative path that's used to reference the master.dacpac (as described here). If I remember correctly, we pulled a copy of the master.dacpac file into the local project and referenced it using the last option shown in the dialog above. This is not ideal however as you'll need to remember to update your copy of the reference periodically.

Will update if I can find the details of what was causing a problem with the referencing method.


Oracle database for learning purposes I would like to know if I can download Oracle Database on my Mac OS X and use it for learning purposes?

I know that Oracle Database Express Edition 11g is free, but is it free for OS X?

You can get Oracle 11 to run natively on OS X, but there is a bit of work required, and not as quick to set up as a pre-installed environment. However, this is a tried and tested method, so it will work, and it will run quicker and more responsively than if you opt for a virtual machine solution.

Taken verbatim from Olivier Refalo's answer to Installing Oracle 11g on OSX (I've not added blockquote formatting as it would mess up the original formatting):

Yes! The fact that Oracle doesn't provide a OSX installer doesn't mean these product shouldn't work natively. Most of them are actually 100% Java.

Please follow the instructions below to install you development environment.

Instructions are for 11g 11.1.1.4.0 but were also tested with 11.1.1.5.0. The trick is to run the installer via a Linux VM and migrate the files to OSX.

Linux VM Installation

It doesn't really matter if you get a 32 or 64 bits Linux distribution. However, the installation folder should be the same on Linux and MacOS.

Download VirtualBox and create a Virtual Machine for Linux. Since we are dealing with Oracle products, rather user their linux distribution: look for this file on google OracleLinux-R5-U7-Server-i386-dvd.iso
With the VM installed, up and running, boot into Linux.
Install JDK 1.6 for Linux 32 bits

Download the following software, oracle offers native installers for windows and Linux. However you should stick to generic downloads as much as possible. Some of these packages come in several zips, extract files according to oracle directions.

Weblogic & coherence: wls1034_generic.jar
OEPE: oepe-helios-all-in-one-11.1.1.6.1.201010012100-win32-x86_64.zip
jDeveloper: jdevstudio11114install.jar
OSB: ofm_osb_generic_11.1.1.4.0
SOA: soa_generic_11.1.1.4.0

Install the software in the following order

Weblogic & Coherence: run with java -Xmx1024m -jar -Dos.name=unix wls1034_generic.jar
OEPE extract act in a folder called /oepe
OSB Disk1/runInstaller - Make sure OSB IDE extensions are being installed (in OEPE)
SOA Disk1/runInstaller
jDeveloper

OSX Installation

Middleware

Here comes the trick, copy the oOracle Middleware folder from your Linux VM to OSX. Make sure the location is the same. For instance if you installed under /Oracle/Middleware on Linux, you should copy to /Oracle/Middleware on OSX.

Fix JVM

For some obscure reasons, Oracle installers don't recognize the Apple JVM. While not mandatory, it is good practices to fix the issue with the following script.

 $ sudo mkdir -p /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home/jre/lib
 $ cd /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home/jre/lib
 $ sudo ln -s /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/classes/classes.jar rt.jar

OEPE

Download the following software (64bits please!)
oepe-helios-all-in-one-11.1.1.6.2.201111102323-macosx-cocoa-x86_64.zip
Extract the file into /oepemac
Right click on Eclipse.app
On the opened menu, choose “Show Package Contents”
Edit file “/Contents/MacOS/eclipse.ini”, append the following lines

-Dweblogic.home=/Oracle/Middleware/wlserver_10.3
-Dharvester.home=/Oracle/Middleware/Oracle_OSB1/harvester
-Dosb.home=/Oracle/Middleware/Oracle_OSB1
-Dosgi.bundlefile.limit=750
-Dosgi.nl=en_US
Now copy the file oracle.osb.ide.link from the folder “oepe/dropins” to “oepemac/dropins” (both under your middleware home).

Oracle XE

Oracle_XE is unfortunately not supported on OSX. we will need to run it via a VirtualBox appliance. The good news is that Oracle provides a easy to install RPM.

Download oracle-xe-10.2.0.1-1.0.i386.rpm
Under Linux, as root, run 'rpm -i oracle-xe-10.2.0.1-1.0.i386.rpm'
Next step is to download and run Oracle RCU to prepare the data model, please refer to the next section for directions.

For reference, we allocated 1cpu, 600MB of ram to our Linux/OracleXE VM.

Next Steps

Configure your web logic development domain. Please refer to this document for instructions.
Quick Start Guide for Oracle® SOA Suite 11gR1 (11.1.1.5.0).pdf

Fix startup scripts

Finally, you will need to fix domain startup script as follows

------------- user_projects/domains/DEVdomain/bin/setDomainEnv.sh -------------

index f74490c..8d75c6c 100755
@@ -108,7 +108,7 @@ else
    else
        JAVA_VENDOR="Unknown"
        export JAVA_VENDOR
-       JAVA_HOME="/usr/java/jdk1.6.0_21"
+       JAVA_HOME=`/usr/libexec/java_home`
        export JAVA_HOME
    fi
 fi

------------ user_projects/domains/DEVdomain/bin/setSOADomainEnv.sh ------------

index 8c6743b..b92cfa4 100755
    @@ -144,6 +144,15 @@ case ${PLATFORM_TYPE} in
         fi
         export USER_MEM_ARGS
         ;;
    +#-----------------------------------------------------
    +# OSX
    +#-----------------------------------------------------
    +Darwin)
    +
    +  USER_MEM_ARGS="${PORT_MEM_ARGS}"
    +  export USER_MEM_ARGS
    +
    +  ;;

       #-----------------------------------------------------
       # Sun OS


Foreign key not able to get one to many working I want to have a one to many relation with player points but I need it in such a way that entity framework sees it as a collection so i can add to it. I mean that one player can have multiple points requests.

Player Table

Player Points Table

CREATE TABLE [dbo].[player](
    [id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_player_id]  DEFAULT (newid()),
    [Name] [varchar](150) NULL,
    [player_id] [varchar](50) NULL,
    [description] [varchar](max) NULL,
    [address] [varchar](150) NULL,
    [gender] [varchar](30) NULL,
    [town] [varchar](150) NULL,
    [zipcode] [varchar](50) NULL,
    [telephone] [varchar](255) NULL,
    [email] [varchar](255) NULL,
    [active] [bit] NULL,
    [createdDate] [date] NULL,
    [author] [varchar](255) NULL,
    [siteid] [uniqueidentifier] NULL,
    [regEmailSent] [bit] NULL,
    [regEmailActivated] [bit] NULL,
    [teamId] [uniqueidentifier] NULL,
    [isDeleted] [bit] NULL,
    [deletedDate] [date] NULL,
    [modifiedDate] [date] NULL,
    [modifiedBy] [nvarchar](250) NULL,
    [isArchived] [bit] NULL,
    [districtId] [uniqueidentifier] NULL,
    [dob] [date] NULL,
 CONSTRAINT [PK_player] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[player]  WITH CHECK ADD  CONSTRAINT [FK_player_team] FOREIGN KEY([teamId])
REFERENCES [dbo].[team] ([id])
GO

ALTER TABLE [dbo].[player] CHECK CONSTRAINT [FK_player_team]
GO

How do i add a foreign so that A player can have many point rows the field that im using to link them with is playerId.
And My Player Points Table

CREATE TABLE [dbo].[player_points](
    [id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_player_points_id]  DEFAULT (newid()),
    [playerId] [uniqueidentifier] NOT NULL,
    [playerName] [nvarchar](255) NULL,
    [teamId] [uniqueidentifier] NULL,
    [points] [int] NULL,
    [pointsToRedeem] [int] NULL,
    [dateToBeAwarded] [date] NULL,
    [approvedBy] [varchar](50) NULL,
    [approved] [bit] NULL,
    [approvedDate] [date] NULL,
    [siteId] [uniqueidentifier] NULL,
    [sentApproveEmail] [bit] NULL,
    [isActivated] [bit] NULL,
    [authUserName] [nvarchar](50) NULL,
    [authCreatedDate] [date] NULL,
    [modifiedDate] [date] NULL,
    [modifiedBy] [nvarchar](255) NULL,
    [playerNumber] [nvarchar](50) NULL,
    [isProcessed] [bit] NULL,
    [sentEmailToAdmin] [bit] NULL,
    [sentEmailToUser] [bit] NULL,
 CONSTRAINT [PK_player_points] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

For anyone else stuck this is how I had to add my foreign key on the actual points table and not the player table I no school boy error but thanks to all for helping.

CREATE TABLE [dbo].[player_points](
    [id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_player_points_id]  DEFAULT (newid()),
    [playerId] [uniqueidentifier] NOT NULL,
    [playerName] [nvarchar](255) NULL,
    [teamId] [uniqueidentifier] NULL,
    [points] [int] NULL,
    [pointsToRedeem] [int] NULL,
    [dateToBeAwarded] [date] NULL,
    [approvedBy] [varchar](50) NULL,
    [approved] [bit] NULL,
    [approvedDate] [date] NULL,
    [siteId] [uniqueidentifier] NULL,
    [sentApproveEmail] [bit] NULL,
    [isActivated] [bit] NULL,
    [authUserName] [nvarchar](50) NULL,
    [authCreatedDate] [date] NULL,
    [modifiedDate] [date] NULL,
    [modifiedBy] [nvarchar](255) NULL,
    [playerNumber] [nvarchar](50) NULL,
    [isProcessed] [bit] NULL,
    [sentEmailToAdmin] [bit] NULL,
    [sentEmailToUser] [bit] NULL,
 CONSTRAINT [PK_player_points] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[player_points]  WITH CHECK ADD  CONSTRAINT [FK_PlayerPoints] FOREIGN KEY([playerId])
REFERENCES [dbo].[player] ([id])
GO

ALTER TABLE [dbo].[player_points] CHECK CONSTRAINT [FK_PlayerPoints]
GO


Quality of Microsft's SQL Server Training/Certification I'm working as a SQL Developer in the healthcare industry and I have about 2 years experience.  I'm primarily using SQL Server 2012.  My company will pay for me to get additional training and/or certifications.  My first thought was to look at Microsoft, so I was looking here for possible certs or courses.  I'm wondering what others have done and/or would recommend?

If your company is willing to pay, I would suggest you to have a subscription for Pluralsight. Its worth for your company or as an individual as well its worth taking it.

(I am speaking from my experience, since I have the subscription and its worth quality investment).

It has courses from SQLSkills team and other people which teaches you a lot about SQLServer - internals, XEvents, Query plans, troubleshooting methodology, powershell, etc.

As a side note, certification will give you a level of confidence to you and your employer, what matters is hands on experience.


Trying to set up new data_directory on Postgres 9.3 Ubuntu 14.04, recently installed Postgres 9.3.

Trying to set up a new data_directory to an attached, larger hard drive (/data), and I changed the postgresql.conf file to reflect that.

However, I get this error when trying to restart the server:

user@server:/etc/postgresql/9.3/main$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server
 * Error: pid file is invalid, please manually kill the stale server process.

I don't have too much experience setting up Postgres, so I'm not sure what to do from here, and Googling around provides no quick answer.

Ubuntu and Debian use pg_wrapper to manage PostgreSQL. It's in the postgresql-common package.

(As a result, this answer doesn't apply to RHEL/CentOS/Fedora).

If there's no data of value, you can just use pg_dropcluster (part of pg_wrapper) to drop (delete) your old datadir. This will destroy all data in it. Then use pg_createcluster to make a new one in /data. That's simplest, but of course it's destructive.

If you want to retain the data, you must:

Ensure that the file system on the new storage is compatible with PostgreSQL. Do not attempt to put PostgreSQL on FAT32 / vfat. I wouldn't attempt to use NTFS on Linux either.
Create every directory up to the location you want for the new datadir.
Ensure that the postgres user has at least x rights on all directories from the data dir down to the / directory, especially including the mount point. You may want to chmod a+x each directory along the path, or chgrp postgres and chmod g+x it.
stop PostgreSQL
mv the data directory, as shown by pg_lsclusters, to the desired new location. If you copy it instead, use cp -aRv to make sure permissions are preserved correctly. Don't just move all contents, move the directory its self.
Edit /etc/postgresql/mypgversion/postgresql.conf and change data_directory to point to the new data directory location.
Start PostgreSQL

I think the mistake you made was, in bold above, that you failed to stop PostgreSQL before moving the datadir. It won't stop the server because it isn't sure it'd be killing the right process, so it refuses to act. You must:

sudo pkill -u postgres

then make sure that all PostgreSQL processes have exited (very important) by making sure that this returns no results:

ps -u postgres

and only then attempt to start the server again.

Never delete postmaster.pid.


Postgres - How to prevent locking when parent table is updated whenever an insert/delete occurs in child table I have a simple use case wherein I update the parent table when a row is inserted/deleted in the child table.
Suppose we have a Products table and Items table and ProductId is the foreign key in the Items table.
Whenever an item is inserted or deleted in the Items table, an update is made in the Products table corresponding to the item. An update is simply a query like:
UPDATE "Products" SET "PropertyX" = $1, "UpdatedAt" = $2 WHERE "Id" = $3

Problem:
If the items inserted/deleted in the items table correspond to the same product in the products table then the database load increases considerably causing timeouts even for 3K reqs/min due to Lock:transactionid waits.
I tested this on Aurora Postgres (with 4 CPUs, 16G Ram) and this happens even if the number of rows in the Items table is in the thousands and the number of rows in the Products table is in the hundreds. I used .net core with entity framework.

My advice is to use a “deferred constraint trigger” to update the "Products" table whenever "Items" is modified.
Such triggers are AFTER triggers that are not run immediately after the statement, but at the very end of the transaction.
The advantage here is that the rows in "Products" won't be locked any longer than necessary, and your concurrency will be much better.


What is the difference between select and fetch time? In this article MySql vs MongoDB performance benchmark, they defined two measurements:

select time
run time (select + fetch)

I do not understand what the difference is between these two. Can someone explain what the select time and fetch time are?

The benchmark you cited is using the term "select time" to mean the amount of time that elapses between when the time when the query is sent to the server for execution and the time when the first row is available for retrieval.  The "fetch time" is the difference between when the first row is available to the client and the last row has been retrieved by the client.

"Select time" then means the amount of time the server spends identifying the rows that will be returned, and "Fetch time" means the amount of time required for the server to actually deliver all of the data to the client.

In the simplest query, SELECT * FROM t1, where the server has to do almost no work in order to identify which rows should be returned (all rows would be returned, with no WHERE clause), we could expect that the select time would be almost instantaneous, while the fetch time would depend on how fast the server can read the rows from its backing store (or cache) as well as the available network bandwidth and latency between the server and the client.  In a more complicated query, where the server has to do more work to identify the rows, the select time could be much higher and the fetch time could be lower by comparison.

In summary, "Select time" is an estimate, based on observation from the client's perspective, of how fast the server can identify the rows to be returned, while "Fetch time" is how fast the server can actually deliver them once they have been identified.

The two times added together are the actual time required for your query to be fully answered.  In light of that, the only thing that really matters is the total, since that's how long you're going to have to wait until you can use the entire result set.


mysqld constant CPU usage when absolutly nothing is connected and no queries I'm using mysql on a windows development machine, and all was good for a long time.

But today my PC was running slowly, and I noticed mysqld.exe is using between 25% and 40% CPU.

I've tried rebooting and giving it some time, but the CPU usage is fairly constant even when idleing on the desktop.
I've noticed the hard drive is working quite hard too, probably because of mysqld

I'm certain nothing is using the server as the only thing that would connect to it is the application on my local machine that I am developing, and that is not running.

But, just to be sure I ran the following statements

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqladmin -u root -p status
Enter password: **********
Uptime: 995  Threads: 1  Questions: 2  Slow queries: 0  Opens: 67  Flush tables: 1
Open tables: 60  Queries per second avg: 0.002

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqladmin -u root -p processlist
Enter password: **********
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host           | db | Command | Time | State | Info             |
+----+------+----------------+----+---------+------+-------+------------------+
| 2  | root | localhost:1775 |    | Query   | 0    | init  | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+

which also seem to show the sever isn't doing anything.

So, what gives?  What is mysqld doing?

Edit: I did recently kill an update statement that was taking a long time.  Could this have anything to do with it?

Yes, your kill statement is the likely cause.

The command SHOW PROCESSLIST will only show you what is happening in foreground threads.  InnoDB is likely rolling back the transaction in the background.  Some diagnostics you might find useful:

SHOW ENGINE INNODB STATUS - see what is happening inside of InnoDB.  Under 'TRANSACTIONS' you should see your killed staetment being rolled back.
SELECT * FROM performance_schema.threads - show all threads in MySQL.
MySQL Workbench 6.1 includes Performance Schema Reports, exposing some of this data in a visual form.


"Upsert" trigger writing mass duplicates from SSIS I have a problem with an "upsert" trigger, being triggered by a SSIS package with fastload. (FIRE_TRIGGERS is enabled)

CREATE TRIGGER [dbo].[MP_VOL_UPSERT]
   ON  [dbo].[vwMP_VOL]
   INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

UPDATE [dbo].[vwMP_VOL]
   SET
    [vwMP_VOL].[id_batch] = inserted.id_batch
      ,[vwMP_VOL].[id_invoice] = inserted.id_invoice
      ,[vwMP_VOL].[text_vchr] = inserted.text_vchr
      ,[vwMP_VOL].[amt_frt_tcur] = inserted.amt_frt_tcur
      ,[vwMP_VOL].[amt_line_hcur] = inserted.amt_line_hcur
      ,[vwMP_VOL].[line_total_hcur] = inserted.line_total_hcur
      ,[vwMP_VOL].[id_tax_1] = inserted.id_tax_1
      ,[vwMP_VOL].[id_tax_2] = inserted.id_tax_2
      ,[vwMP_VOL].[id_tax_3] = inserted.id_tax_3
      ,[vwMP_VOL].[id_tax_4] = inserted.id_tax_4
      ,[vwMP_VOL].[line_total_rem_hcu] = inserted.line_total_rem_hcu
      ,[vwMP_VOL].[id_po] = inserted.id_po
      ,[vwMP_VOL].[cnt_rlse] = inserted.cnt_rlse
      ,[vwMP_VOL].[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted
INNER JOIN (SELECT [Org]
      ,[id_vend]
      ,[id_org_orig]
      ,[id_vchr_nbr]
      ,[id_vchr_line]
  FROM [vwMP_VOL]) AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line;

INSERT INTO [dbo].[vwMP_VOL]
SELECT inserted.* FROM inserted
LEFT JOIN (SELECT [Org]
      ,[id_vend]
      ,[id_org_orig]
      ,[id_vchr_nbr]
      ,[id_vchr_line]
  FROM [vwMP_VOL]) AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line
WHERE old.Org IS NULL;

END

Inserting new rows works fine, but all existing rows get updated with one set of values.

SELECT
text_vchr, count(*) as dups
FROM MP_VOL
group by text_vchr
order by count(*) desc

Results:
text_vchr                   dups
-------------------------   -----
MEDIA MONITORING SERVICES   20897
                                1

Do I have to disable fastload in SSIS, or is there some other way to fix it?

(It's SQL Server 2005, so MERGE is not available)

I suspect the issue appears because you are referencing the view twice in the UPDATE statement, once in UPDATE [dbo].[vwMP_VOL] clause and then in FROM clause and the second time you are giving it an alias. But you are not using the alias in the SET clause, only the first reference.

The result is that the the two references are unrelated and the rows are updated with unrelated data (and possibly many times as well!)

See the  the official MSDN documentation about UPDATE and the caution:

  Best Practices

  ...

  Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

and a more detailed analysis of this "feature" and other issues it can cause, in Hugo Kornelis's blog post: Let's deprecate UPDATE FROM! and especially this paragraph:

  Correctness? Bah, who cares?

  Well, most do. That’s why we test.

  If I mess up the join criteria in a SELECT query so that too many rows from the second table match, I’ll see it as soon as I test, because I get more rows back then expected. If I mess up the subquery criteria in an ANSI standard UPDATE query in a similar way, I see it even sooner, because SQL Server will return an error if the subquery returns more than a single value. But with the proprietary UPDATE FROM syntax, I can mess up the join and never notice – SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. And there is no way of knowing which row that will be, since that depends in the query execution plan that happens to be chosen. A worst case scenario would be one where the execution plan just happens to result in the expected outcome during all tests on the single-processor development server – and then, after deployment to the four-way dual-core production server, our precious data suddenly hits the fan…

I think the best way to solve this is to use the alias in the UPDATE clause and change accordingly the SET clause:

UPDATE old
   SET
       [id_batch] = inserted.id_batch
      ,[id_invoice] = inserted.id_invoice
      ,[text_vchr] = inserted.text_vchr
      ,[amt_frt_tcur] = inserted.amt_frt_tcur
      ,[amt_line_hcur] = inserted.amt_line_hcur
      ,[line_total_hcur] = inserted.line_total_hcur
      ,[id_tax_1] = inserted.id_tax_1
      ,[id_tax_2] = inserted.id_tax_2
      ,[id_tax_3] = inserted.id_tax_3
      ,[id_tax_4] = inserted.id_tax_4
      ,[line_total_rem_hcu] = inserted.line_total_rem_hcu
      ,[id_po] = inserted.id_po
      ,[cnt_rlse] = inserted.cnt_rlse
      ,[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted
          --   INNER JOIN (SELECT * FROM [dbo].[vwMP_VOL]) AS old
-- or the simpler
INNER JOIN [dbo].[vwMP_VOL] AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line;

This would work, too, but I don't like it as much as the previous because we can't alias the table:

UPDATE [dbo].[vwMP_VOL]
   SET
       [id_batch] = inserted.id_batch
      ---
      ,[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted
WHERE [dbo].[vwMP_VOL].[Org] = inserted.Org
      AND [dbo].[vwMP_VOL].[id_vend] = inserted.id_vend
      AND [dbo].[vwMP_VOL].[id_org_orig] = inserted.id_org_orig
      AND [dbo].[vwMP_VOL].[id_vchr_nbr] = inserted.id_vchr_nbr
      AND [dbo].[vwMP_VOL].[id_vchr_line] = inserted.id_vchr_line;


How to disable triggers from nesting/recursing when accessing the same table So I have this data

+----+----------+-------+---------------+----------------+------------+--------+
| id | date     | accid | accid_currbal | Action         | Desc       | Amount |
| 1  | 05/06/17 | 1     | 7777          | Create Account | some desc… | 7777   |
| 2  | 05/06/17 | 1     | 32091         | Add Funds      | some desc… | 24324  |
| 3  | 05/06/17 | 1     | 32100         | Add Funds      | some desc… | 9      |
| 4  | 05/06/17 | 1     | 32600         | Add Funds      | some desc… | 500    |
+----+----------+-------+---------------+----------------+------------+--------+

And an BEFORE_UPDATE trigger that accesses this table when it fires

CREATE ..... TRIGGER
`db_moneytracker`.`transactions_BEFORE_UPDATE` BEFORE UPDATE ON
`transactions` FOR EACH ROW
BEGIN
DECLARE adj INT;

if (old.amount <> new.amount && old.accid = new.accid) then
set adj = new.amount - old.amount;

update transactions set accid_curr_accbalance = accid_curr_accbalance + adj
where accid = new.accid and `date` >= new.date;

elseif (old.accid <> new.accid) then
SIGNAL SQLSTATE '45000'
                SET MESSAGE_TEXT = 'IDs unchangeable';
end if;
END

But produces an error when updated

1442: Can't update table 'transactions' in stored function/trigger because
it is already used by statement which invoked this stored function/trigger.

I think I understood what it meant so... I want the trigger not to fire the UPDATE is fired by THIS trigger.

You don't need an update statement:

In a "before update" trigger, you can directly change any of the values in the new row.

if ( old.amount <> new.amount && old.accid = new.accid ) then
   set adj = new.amount - old.amount ;
   set new.accid_curr_accbalance = new.accid_curr_accbalance + adj ;
else
   . . .


Secure Linked Server - Non privledged user possible? Registry corruption? Is it possible to use a non privledged Windows domain account to impersonate itself in a linked server?

And why would it be unable to read the registry for available network protocols?

Overview: Only way I am able to have a scheduled job utilize a linked server is when the local account is mapped to a remote SQL account.  Unable to use 'Impersonate.'

Details:

Two SQL 2008 R2 Std instances on Win Server 2008 R2 x64
One default  + one named
I'll use Server_A_Default + Server_A_Named to refer to the instances
Each instance has it's own AD service account for MSSQL + Agent (4 unique AD accounts in use on server)
Port hard coded for Named instance Server_A_Named
SPNs created for the 2 MSSQL accounts.
SPNs match the default and hardcoded named instance port respectively

Within the named instance (Server_A_Named):

Created a linked server on Server_A_Named to Server_B.  We'll call the linked server SAN-B.

In SAN-B, I've used SQL Nativue Client 10.0 + OLE DB Provider for SQL

Under the Security for SAN-B, I have 3 accounts:

NonPrivADuser
ADuserSysAdmin
LocalSQLuser

For logins not defined, connections will not be made.

As ADuserSysAdmin, I can click on test connection and it works.

Only way to get linked server to work for NonPrivADuser is to have it map to a local SQL account on Server_B
NonPrivADuser has access on Server_B's database as well.

This is the error that NonPrivADuser receives while trying to access the linked server using 'impersonate':

Executed as user: DOMAIN\NonPrivADuser. SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].
[SQLSTATE 42000] (Error 65535)  OLE DB provider "SQLNCLI10" for linked server "SAN-B" returned message "Login timeout expired".
[SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "SAN-B" returned message "A network-related or instance-specific
error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct
and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
[SQLSTATE 01000] (Error 7412).  The step failed.

I fired up procmon on Server_A while trying to use the linked server, SAN_B.

SQLAGENT.EXE can read HKLM\SOFTWARE\Microsoft\MSSQLSERVER\Client\SNI10.0
SQLSERVR.EXE receives a BAD IMPERSONATION on the same key.

I fired up regedit and 'users' has read permissions on that key.

Have you granted the SQL Server accounts the right to impersonate other users?  It's a Windows setting on the domain account.


Does Postgres ever require a naked field without single quotes? I have this query:

UPDATE user_map_table SET  user_id = '1', value = '{"bob":"diane"}', key = 'couple', value_type = 'string', added = 'false' WHERE user_id = 1

it worked fine. The thing is the user_id column is an int column, and the added column is a boolean column. Postgres doesn't seem to care that I put single-quotes around the '1' and 'false'.  Is there ever a time where single quotes will make Postgres barf?

One obvious answer might be if you have extra whitespace:  ' 1' or 'false ', but other than that?

With overloaded functions (same name but different arguments), there can be cases where they mean different things. But you should probably avoid creating such cases.

create function j(int) returns int language SQL as $$ select 1-$1 $$;
create function j(text) returns int language SQL as $$ select 7-$1::int $$;
select j('5');
 2
select j(5);
 -4


How to know spid which is filling tempdb from temp table name in sybase I received alert that tempdb is going to be full and temp table name aslo provided.
Now I want to know how to check which spid is filling tempdb from temp tablename.

The best is to use the montables. Make sure you have mon tables activated. Once verified you can issue the following select statement:

select SPID, DBName, ObjectName, PartitionSize
from master..monProcessObject
where DBID = tempdb_id(SPID)
order by SPID

for more information please see the official Sybase docs: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00967.1550/html/MigrationGuide/MigrationGuide14.htm


Install Developer Edition ASE in Vagrant/Virtual Box It is possible to deploy a development version of Sybase in a Vagrant/Virtualbox instance?  I was planning on using a CentOS vagrant box to create a development environment but am not sure if it's even feasible to run ASE in a virtual environment.

If possible, are there any caveats to be aware of?

Installing Sybase within a virtual environment purely for development purposes is possible and I have used it that way for years with Windows, Linux and Solaris x86. I'm not sure about Vagrant, but with Virtual Box it works.

You must ensure your virtualised host has sufficient memory to run the database and have made the relevant additional settings.

You'll need to set the maximum amount of shared memory to a suitable value and there is probably a dependency on libaio1.

For example, add the following to /etc/sysctl.conf. If 1GB is too large you can use a smaller value. You'll need to consult the Sybase documentation for the version you're installing for a minimum value.

# Set the kernel shared memory to 1GB
kernel.shmmax=1073741824


Performance settings for MyISAM tables - keep everything in memory I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage of my server's resources?

What settings should I attempt configure in my.cnf?
Can I force data + indexes to stay in memory?

MySQL only caches the indexes in MySQL's memory, not the data.  The data is only cached by the underlying operating system's filesystem caches.  The most important value to tune in my.cnf is key_buffer_size which will allocate memory for MyISAM to cache indexes.

However, unless you have some specific reason for using MyISAM tables you should  convert those tables to InnoDB.  In this day and age, there are less than a handful of reasons to use use MyISAM and MyISAM is not good for concurrency, data integrity, or crash recovery.

If you switch to InnoDB, the most important setting is innodb_buffer_pool_size which is the internal memory cache that will cache data pages & indexes.  Other things to consider are the innodb_log_file_size and make sure to set innodb_file_per_table=1.  With 4GB of data+indexes, setting  innodb_buffer_pool_size=4G is reasonable.  If you are running in a highly concurrent environment, you could experiment with values for innodb_thread_concurrency.  My recommendation is to start with innodb_thread_concurrency=0, but you might find it necessary to explicitly set it to be somewhere between 4-8 depending on your workload and version of MySQL.


How to prompt sql if there is no exception I'm new to SQL advance methods. I'm trying to do some error handling after checking for exceptions.

DECLARE
value_invalid exception;
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_max_size=12g';
EXCEPTION
when value_invalid then
null;
END

I'm trying to have a PROMPT for this SQL because I'm calling this script from a shell script. It returns out no prompt at all which is different from when I run the SQL command in sqlplus manually. See, for example,

SQL> ALTER SYSTEM SET filesystemio_options=setall scope=spfile;
System altered.

In my case, once I ran the shell file it gave me this:

SQL> Database Tuning for Static_Parameters
Database Tuning for sga_max_size
13   14  Disconnected from Oracle Database 12c Enterprise Edition Release
12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options

My questions is, was it altered successfully? How can I add a prompt to notify user that the system is altered? I tried ELSE to prompt the user if there's no exception, but still nothing appeared at the prompt.

Using

EXCEPTION
when value_invalid then
null;

is definitively a bad idea, you should not silently suppress the error message.

Try this:

DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_max_size=12g';
END;
/

Note, inside a PL/SQL Block you have only direct privileges. Privileges granted by role (e.g. DBA role) do not apply inside the block.


DB schema for storing the process tree of linux I search a db schema to store the process tree of linux every half hour.

Platform: PostgreSQL on Linux.

I am unsure where to split all the data into columns or to use a json column.

Which one is better, and why?

The design should be according to your requirements. If your requirement includes to store only some columnar data that are mostly required (mostly not null) then use tables with relations (foreign keys). If you just need some logging with situational tree structure data then use JSONB.
For example, suppose that you store io_counters when counters are higher than some point, then use JSON.
I presuppose you use minimum postgresql 9-4.


Migrate legacy application to new db/schema I apologize if I made any mistake/missing info, fist post here!

I am in the process of rewriting a website from scratch to a newer tech stack with a completely redesigned architecture. The website in question is a real estate website to rent houses for short period of times. It was designed/created in 1998-1999 and has been working ever since. A few cosmetic redesigns have been made to the website, but no changes have been made to the database or the architecture except for hosting-mandated updates to the infrastrcture (they run on one of those shared spaces). The current stack is as follows:

DB: MySQL (10.1.41-MariaDB-cll-lve)
Server: PHP 7.0
Client (Web): Simple Bootstrap website, nothing fancy

The system was originally designed by a student, which had no idea/experience with these kind of systems. The structure of the DB is quite flat, and there are just a few tables (< 5). There are almost no joins in the code, and as an example their booking periods are all put together in the same field, with the dates split by delimiters and concatenated in the format

DD:MM:YYYY-DD:MM:YYYY/DD:MM:YYYY-DD:MM:YYYY/...

So whenever they need to check for availability, they parse this string. Also, data types are scarcely used and most of the fields are strings.

I have rewritten the entire system with a new architecture and schema using relations whenever possible as well as adding quite a few tables. This is the stack:

DB: PostgreSQL
Server: Spring Boot (latest) with Spring Data
Client (Web): Vue.js

I now need to migrate the old data to the new system, and I am not sure how to proceed. I have thought of two different options:

Write a script that takes all the data from the DB, creates the relations and adapts to the new schema, and stores them in the new DB (EDIT: The script could also parse the dates, etc). Of course this needs to be properly tested but I am not in a time crunch. The script could be written in either Python (to allow me to manipulate the fields without having to deal with types) or using Java (being as I use Spring) and using the new models directly.
Extract the data from MySQL and dump it into PostgreSQL. Then, write some kind of an adapter that allows me to read the data and manipulate it little by little whenever needed. I am afraid of this option as Spring Data is quite strict at times and I would need to duplicate a lot of the models I have created.

Keep in mind the following things:

There are about 5000 entries between all the tables, so not a huge migration. Still, they have a lot of information that needs to be accurate and cannot be lost.
Spring Data complains quite easily about foreign key restrictions if you touch the schema in any way (i.e. manually) and therefore I am afraid of doing all the work and then having to deactivate safety features.
The migration will be done only once. Once verified, we will keep a copy of the old database just to be sure but everything else will be moved to the new system.

EDIT:
As pointed out in the comments, I forgot to ask the actual question :). Are any of the options I listed a good way to approach this problem? Are there any tools out there that may help me in this migration? I think that option 1 is the best for my case, am I missing something?

Any help is greatly appreciated!

As mentioned in the comments, this rather misses a question regarding a database technology. However here is my suggestion:

Since there are likely not any MySQL or Postgres specific features involved, just dump to CSV roughly in the data format that you will likely want in your target system and insert it there into any kind of table. If you afterwards need to massage data and add postgres features on top of it, create a new target table with the functionality you require (e.g. indexes, constraints, relations, views) and insert into it from the previous table.

I would not suggest anything more complicated, since you seem to migrate only small data.

PG has all the manipulation functions you need. Once you are done make it work in your application software.


To Foreign Key or not to Foreign Key..... (inet -> cidr) Below is a very abridged version of a project I am working on.

CREATE TABLE cidr_block (
    id serial not null unique,
    block_def cidr primary key,
    dhcp_server_id int not null references computer_system (id),
    ...
);
CREATE TABLE ip_addr (
    computer_id int not null references computer_system (id),
    mac_addr macaddr not null unique,
    ip_addr inet not null unique,
);

This is a network configuration database.  Now, it is expected that most, but not all, ip_addr.ip_addr records will be in cidr_block.block_def blocks.  Others, which might be externally linked vpn interfaces to customer locations might not be.  There is no simple, declarative way to ensure a fkey for the applicable rows between the two tables.  So I am wondering:

Is it preferable to use triggers to create fkey links where appropriate?
Is it sufficient just to join where expected?  If a computer is given a bad IP address, it just won't be given one from the dhcp server, and we will know this pretty quickly.  (writes trigger notifications which trigger updates on relevant dhcp servers)
Is there another, better way?

To answer my own question here, here is what I eventually did:

Function ip_block(inet) to look up the ip block record of an inet address.
Check constraint to make sure that returns not null for appropriate classes of addresses.  That would be triggered on insert or update to the assignments table.
Trigger function to check assignments table for addresses in a block and raise an RI exception if such are found.
Trigger which runs the above function when the block_def of the ip block is updated, or the ip block is deleted.

I will leave this answer unaccepted for a while to see if anyone else has a better approach.  However the amount of code to actually do this turned out to be a lot less than expected so I am pretty happy with it so far.


Fill populate arrays from other table Things go inside other things.  More than one can go inside and each one can be inside many different containers.  This is for a on-line shopping site where assorted things that we get from our suppliers are bundled together, given a web-page and then sold as that collective.  It all used to be tracked with a bunch of horrible CSV files but I'm setting up a proper database now.

create table collections(
    sku character varying(50),
    components text[]);

create table products(
    ordering_id character varying(50),
    inside text[] );

inside's elements are skus from collections and components elements are ordering_ids from products.

The contents of these easily fill in both columns in the products table and sku in collections.  Now I want to fill in components: preferably with some tricky SQL statement.  The best I could come up with is:

with new_values as ( select ordering_id, inside from products )
    update collections set components = components ||
        nv.ordering_id::text  from new_values nv
    where collections.sku::text = any( nv.inside );

but that only fills in the first product that's found inside each collections.  Why is that?  Do I need to do some sort of JOIN to get this to work?

Sample data:

collections

sku    components
10A    {}
20A    {}

products

ordering_id    inside
1234            {10A}
5678            {10A, 20A}

should leave products unchanged but collections should now be

collections

sku    components
10A    {1234,5678}
20A    {5678}

Unfortunately what I get from the SQL that I came up with above is

sku    components
10A    {1234}
20A    {5678}

The following aggregates the products as you want it:

select c.sku, array_agg(p.ordering_id) as ids
from collections c
  join products p on c.sku = any(p.inside)
group by c.sku;

The above returns:

sku | ids
----+------------
10A | {1234,5678}
20A | {5678}

Now this can be used as the source of an update statement:

update collections
   set components = t.ids
from (
    select c.sku, array_agg(p.ordering_id) as ids
    from collections c
      join products p on c.sku = any(p.inside)
    group by c.sku
) t
where collections.sku = t.sku;


Login system: how is it possible to model multiple user types identified by only one account? I am trying to model a database consisting of different entities that need to be authenticated through a login system. Let's assume to have two separate entities (Seller and Manager) just like this image:

as you can see both of them have (only) one account identified by the primary key username. I would like to be able to identify one role by its username, so that my system recognises what to do. If we translate this conceptual model in the physical one the resulting model is the following:

Is this actually correct?

Update:
I translated in english all my entities and decided to upload my physical model here in order to explain why i made this user-centric:

My users are:

SystemAdministrator
Owner
Seller

basically these three roles have logical connection with other entities, how can i keep these logical connections and still have a valid account table (for username, password... info)?

Thanks in advance and best regards.

I don't think you should make tables to represent Roles (User, Manager, Seller, etc) I think you could implement something more scalable like this:

Completely forget about the "user-centric" model and think to a lower level, Individuals. Individuals can have any number of Accounts and Accounts can have any number of Roles. Individuals can be represented on any number of abstractions around the database or even across databases.

What do you think?


Lock a row if it exists, create new record otherwise I have a table that holds metadata for "documents", let's call it DOCS.  Other tables point to the metadata table's ID, to build the parts of the document.

I want to make sure that, in my application, I can have one person "check out" a document so that they are the only person who can edit it. (With an override mechanism built into the app, of course).

So my idea is to have a column in the DOCS table, LockedBy, which holds a user ID if the document is locked.

I want the application to only have to use one stored procedure for inserts and updates, so I'm currently taking the given @DOCID and doing an "upsert":

UPDATE DOCS
SET column=@param
WHERE ID=@DOCID;
IF @@ROWCOUNT=0 --@@ROWCOUNT will be 0 if the update fails
BEGIN
    INSERT INTO DOCS(columnnames)
    VALUES(@newvalues);
    SET @DOCID=SCOPE_IDENTITY();
END

But now that I want to add the locking mechanism, I need some way to check if the doc exists, and it is not locked by someone else, taking into account the possibility of concurrency.

If I do:

BEGIN TRANSACTION TR1
IF EXISTS(
    SELECT *
    FROM DOCS WITH(HOLDLOCK,ROWLOCK)
    WHERE ID=@DOCID)
BEGIN
    IF NOT EXISTS(SELECT * FROM DOCS WHERE ID=@DOCID AND LockedBy=@Editor)
    RETURN -1 --return "error" code to app
END
ELSE
--create row here

--finally do all the updates to secondary tables
COMMIT TRANSACTION TR1

Will the row be locked from the SELECT to the end of the transaction?  And if I create the row in the last ELSE clause, do I need to do something like INSERT INTO DOCS WITH(HOLDLOCK,ROWLOCK)?
Is there a better way to do this?

If LockedBy is null when not locked then...

Using select @CurrentLockedBy = isnull(LockedBy,0) lets us find out which of the three states the record is in. If it is null, then the record doesn't exist. If it is 0 then it exists and is unlocked. Otherwise it exists and is locked by someone (possibly the caller).

I would use something like this:

create procedure dbo.docs_upsert (
    @docid int output
  , @cols varchar(max)
  , @LockedBy int
  , @CallerUserId int
  ) as
begin
  set nocount on;
  set xact_abort on;
  declare @CurrentLockedBy int;
  begin try;
    begin tran;
      select @CurrentLockedBy = isnull(LockedBy,0)
        from docs d with (updlock, holdlock) -- lock for update/insert
        where d.id = @docid;
    /*
     If @CurrentLockedBy >0 then Locked by someone
     If @CurrentLockedBy = 0, exists and not locked.
     If @CurrentLockedBy is null, row doesn't exist.
     */
    /* Locked by another user*/
    if isnull(@CurrentLockedBy,@CallerUserId)!=@CallerUserId
    begin;
      return -1;
    end;
    /* Exists, not Locked */
    if @CurrentLockedBy = 0
    begin;
      update docs set
          cols = @cols
        , LockedBy = @LockedBy
        where id = @docid;
    end;
    else
    /* Doesn't exist, insert */
    begin;
      insert into docs(cols)
        values(@cols);
        set @docid=scope_identity();
    end;
    commit tran;
  end try
  begin catch;
    if @@trancount > 0
      begin;
        rollback transaction;
        throw; -- or other error handling
      end;
  end catch;
end;


How is a composite database different from an ESB in that both provide a single point of access for multiple data sources After reading this article on composite db software here

it seems to me that the composite db software is not exactly a databse store like Oracle which physically stores the data. It is more like a adapter that connects to different sources in the back end and provides a single point of data access to the client. is this correct?

If the above is correct, then how is it different from an ESB which can act as a single point for the clients to access data? ESB can fetch data from a variety of cources and present ot the client and client dont have to know here or how the data is stored.

Consider data virtualization solutions like Composite to be one level below the ESB in an enterprise architecture.  A virtual view in Composite can bridge n data sources transparently to the code in any given service.  So while you COULD implement all that multi-source joining at the ESB level, it would be much more complex, probably slower, and ultimately tightly couple your services to your data stores.  DV provides an abstraction layer so that you properly separate concerns in your architecture and allow your ESB to encapsulate business logic without getting into the dirty details of data storage locations.


Help needed to design an ER diagram I want to design an ER diagram in order to create a database for a small local business. My problem is that the bussiness sells merchandise to persons and businesses both. How can i differentiate businesses from persons in my ER diagram? Should i create different entities for those?
EDIT: Here's my ER diagram so far. I implemented two subclasses to differentiate between business and person. Not sure if this is the best way to go though and i'm also not sure about cardinalities.

It depends on the similarities between the two entities in the context of the business logic. If they share all the same attributes (columns) except for the fact one is a business and one is a person you theoretically can store them as the same entity in a table like Customer with just a column to distinguish between the two such as a boolean based field IsBusiness.
But in most cases, developers will find that there are unique attributes to one or the other that they need to store as well, and will normalize into separate tables.
Having more context on exactly what you expect to store, even such an example diagram of what you've thought about so far, may help in providing a more specific and concrete answer.


PostgreSQL, PostGIS: Copying a schema with a geometry column According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn():

  AddGeometryColumn — Adds a geometry column to an existing table of
  attributes.

I am copying a schema from one PostgreSQL instance to another. I wonder if the SQL created by pg_dump is good enough - will it create the geometry columns the same way AddGeometryColumn() does?

Adam,

First that is old document -- new one is here: http://postgis.net/docs/AddGeometryColumn.html

Though what they say in both is the same.

The using that function is only need for 1.5 and below.  2.0 we encourage you to just create the table normally.

something like

CREATE TABLE test(gid serial primary key, geom geometry(POLYGON, 4326) );

pg_dump works fine either way since in old version it would restore the constraints and new version the create table would be built with typmodifiers.