Inventory database structure when inventory items have varying attributes I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using device serial numbers as the primary key. The problem I'm having is that the other attributes for these devices vary and I don't want to have fields in the inventory table that are unrelated to other devices. Below is a link to an ERD of part of the database (some FK relations are not shown). I'm trying to set it up, for example, so a device with a workstation device type can't be put into the phones table. This seems to require the use of a lot of triggers to validate the device type or class, and new tables anytime a different device with different attributes will be tracked; not to mention all of the one-to-one relationships which will make joins a nightmare (there are more one-to-one relationships not shown).

I looked into setting up attribute tables that can be mapped to serial numbers, but that would allow attributes that do not apply to a device type to be assigned to a device, e.g., someone could assign a phone number attribute to a workstation if they wanted. I found an explanation on this site that gave the following structure:

This structure would work great if the attributes were all applicable to the items I am storing. For example if the database was storing only mobile phones, the attributes could be things like touchscreen, trackpad, keyboard, 4G, 3G...whatever. In that case, they all apply to phones. My database would have attributes like hostname, circuitType, phoneNumber, which only apply to specific types of devices.

I want to set it up so only the attributes that apply to a given device type can be assigned to a device of that type. Any suggestions on how to setup this database? I'm not sure if this is a proper use of one-to-one relationships, or if there is a better way to do this. Thank you in advance for taking the time to look into this.

Here are some of the other threads I read. They gave me some good insight, but I don't think they really apply:

https://stackoverflow.com/questions/9335548/how-to-structure-database-for-inventory-of-unlike-items

https://stackoverflow.com/questions/1249632/database-structure-for-items-with-varying-attributes

https://stackoverflow.com/questions/5559587/product-inventory-with-multiple-attributes

https://stackoverflow.com/questions/6613802/question-about-setting-up-inventory-database

https://stackoverflow.com/questions/514111/how-to-best-represent-items-with-variable-of-attributes-in-a-database

Supertype/Subtype

How about looking into the supertype/subtype pattern? Common columns go in a parent table. Each distinct type has its own table with the ID of the parent as its own PK and it contains unique columns not common to all subtypes. You can include a type column in both parent and children tables to ensure each device can't be more than one subtype. Make an FK between the children and the parent on (ItemID, ItemTypeID). You can use FKs to either the supertype or subtype tables to maintain the desired integrity elsewhere. For example, if the ItemID of any type is allowed, create the FK to the parent table. If only SubItemType1 can be referenced, create the FK to that table. I would leave the TypeID out of referencing tables.

Naming

When it comes to naming, you have two choices as I see it (since the third choice of just "ID" is in my mind a strong anti-pattern). Either call the subtype key ItemID like it is in the parent table, or call it the subtype name such as DoohickeyID. After some thought and some experience with this, I advocate calling it DoohickeyID. The reason for this is that even though there could be confusion about the subtype table really in disguise containing Items (rather than Doohickeys), that is a small negative compared to when you create an FK to the Doohickey table and the column names don't match!

To EAV or not to EAV - My experience with an EAV database

If EAV is what you truly have to do, then it's what you have to do. But what if it weren't what you had to do?

I built an EAV database that is in use in a business. Thank God, the set of data is small (though there are dozens of item types) so the performance is not bad. But it would be bad if the database had more than a few thousand items in it! Additionally, the tables are so HARD to query. This experience has led me to really desire to avoid EAV databases in the future if at all possible.

Now, in my database I created a stored procedure that automatically builds PIVOTed views for each and every subtype that exists. I can just query from AutoDoohickey. My metadata about the subtypes has a "ShortName" column containing an object-safe name suitable for use in view names. I even made the views updateable! Unfortunately, you cannot update them on a join, but you CAN insert to them an already-existing row, which will be converted to an UPDATE. Unfortunately, you cannot update only a few columns, because there is no way to indicate to the VIEW which columns you want to update with the INSERT-to-UPDATE conversion process: a NULL value looks like "update this column to NULL" even if you wanted to indicate "Don't update this column at all."

Despite all this decoration to make the EAV database easier to use, I still don't use these views in most normal querying because it is SLOW. Query conditions are not predicate pushed all the way back to the Value table, so it has to build an intermediate result set of all the items of that view's type before filtering. Ouch. So I have many, many queries with many, many joins, each one going out to get a different value and so on. They perform relatively well, but ouch! Here's an example. The SP that creates this (and its update trigger) is one giant beast, and I'm proud of it, but it is not something you want to ever try to maintain.

CREATE VIEW [dbo].[AutoModule]
AS
--This view is automatically generated by the stored procedure AutoViewCreate
SELECT
   ElementID,
   ElementTypeID,
   Convert(nvarchar(160), [3]) [FullName],
   Convert(nvarchar(1024), [435]) [Descr],
   Convert(nvarchar(255), [439]) [Comment],
   Convert(bit, [438]) [MissionCritical],
   Convert(int, [464]) [SupportGroup],
   Convert(int, [461]) [SupportHours],
   Convert(nvarchar(40), [4]) [Ver],
   Convert(bit, [28744]) [UsesJava],
   Convert(nvarchar(256), [28745]) [JavaVersions],
   Convert(bit, [28746]) [UsesIE],
   Convert(nvarchar(256), [28747]) [IEVersions],
   Convert(bit, [28748]) [UsesAcrobat],
   Convert(nvarchar(256), [28749]) [AcrobatVersions],
   Convert(bit, [28794]) [UsesDotNet],
   Convert(nvarchar(256), [28795]) [DotNetVersions],
   Convert(bit, [512]) [WebApplication],
   Convert(nvarchar(10), [433]) [IFAbbrev],
   Convert(int, [437]) [DataID],
   Convert(nvarchar(1000), [463]) [Notes],
   Convert(nvarchar(512), [523]) [DataDescription],
   Convert(nvarchar(256), [27991]) [SpecialNote],
   Convert(bit, [28932]) [Inactive],
   Convert(int, [29992]) [PatchTestedBy]
FROM (
   SELECT
      E.ElementID + 0 ElementID,
      E.ElementTypeID,
      V.AttrID,
      V.Value
   FROM
      dbo.Element E
      LEFT JOIN dbo.Value V ON E.ElementID = V.ElementID
   WHERE
      EXISTS (
         SELECT *
         FROM dbo.LayoutUsage L
         WHERE
            E.ElementTypeID = L.ElementTypeID
            AND L.AttrLayoutID = 7
      )
) X
PIVOT (
   Max(Value)
   FOR AttrID IN ([3], [435], [439], [438], [464], [461], [4], [28744], [28745], [28746], [28747], [28748], [28749], [28794], [28795], [512], [433], [437], [463], [523], [27991], [28932], [29992])
) P;

Here's another type of automatically-generated view created by another stored procedure from special metadata to help find relationships between items that can have multiple paths between them (Specifically: Module->Server, Module->Cluster->Server, Module->DBMS->Server, Module->DBMS->Cluster->Server):

CREATE VIEW [dbo].[Link_Module_Server]
AS
-- This view is automatically generated by the stored procedure LinkViewCreate
SELECT
   ModuleID = A.ElementID,
   ServerID = B.ElementID
FROM
   Element A
   INNER JOIN Element B
      ON EXISTS (
         SELECT *
         FROM
            dbo.Element R1
         WHERE
            A.ElementID = R1.ElementID1
            AND B.ElementID = R1.ElementID2
            AND R1.ElementTypeID = 38
      ) OR EXISTS (
         SELECT *
         FROM
            dbo.Element R1
            INNER JOIN dbo.Element R2 ON R1.ElementID2 = R2.ElementID1
         WHERE
            A.ElementID = R1.ElementID1
            AND R1.ElementTypeID = 40
            AND B.ElementID = R2.ElementID2
            AND R2.ElementTypeID = 38
      ) OR EXISTS (
         SELECT *
         FROM
            dbo.Element R1
            INNER JOIN dbo.Element R2 ON R1.ElementID2 = R2.ElementID1
         WHERE
            A.ElementID = R1.ElementID1
            AND R1.ElementTypeID = 38
            AND B.ElementID = R2.ElementID2
            AND R2.ElementTypeID = 3122
      ) OR EXISTS (
         SELECT *
         FROM
            dbo.Element R1
            INNER JOIN dbo.Element R2 ON R1.ElementID2 = R2.ElementID1
            INNER JOIN dbo.Element C2 ON R2.ElementID2 = C2.ElementID
            INNER JOIN dbo.Element R3 ON R2.ElementID2 = R3.ElementID1
         WHERE
            A.ElementID = R1.ElementID1
            AND R1.ElementTypeID = 40
            AND C2.ElementTypeID = 3080
            AND R2.ElementTypeID = 38
            AND B.ElementID = R3.ElementID2
            AND R3.ElementTypeID = 3122
      )
WHERE
   A.ElementTypeID = 9
   AND B.ElementTypeID = 17

The Hybrid Approach

If you MUST have some of the dynamic aspects of an EAV database, you could consider creating the metadata as if you had such a database, but instead actually using the supertype/subtype design pattern. Yes, you would have to create new tables, and add and remove and modify columns. But with the proper pre-processing (like I did with my EAV database's Auto views) you could have real table-like objects to work with. Only, they wouldn't be as gnarly as mine and the query optimizer could predicate push down to base tables (read: perform well with them). There would just be a one join between the supertype table and the subtype table. Your application could be set to read the metadata to discover what it is supposed to do (or it can use the auto-generated views in some cases). This protects your application code from having to be touched extensively just to add or modify things.

Or, if you had a multi-level set of subtypes, just a few joins. By multi-level I mean when some subtypes share common columns, but not all, you could have a subtype table for those that is itself a supertype of a few other tables. For example, if you are storing information about Servers, Routers, and Printers, an intermediate subtype of "IP Device" could make sense.

I will give the caveat that I haven't yet made such a hybrid supertype/subtype EAV-metatable-decorated database like I'm suggesting here yet to try out in the real world. But the problems I've experienced with EAV are not small, and doing something is probably an absolute must if your database is going to be large and you want good performance without some crazy expensive gigantic hardware.

In my opinion, the time spent automating the use/creation/modification of real subtype tables would ultimately be best. Focusing on flexibility driven by data makes the EAV sound so attractive (and believe me I love how when someone asks me for a new attribute on an element type I can add it in about 18 seconds and they can immediately start entering data on the web site). But flexibility can be accomplished in more than one way! Pre-processing is another way to do it. It's such a powerful method that so few people use, giving the benefits of being totally data-driven but the performance of being hard-coded.

(Note: Yes those views really are formatted like that and the PIVOT ones really do have update triggers. :) If someone is really that interested in the awful painful details of the long and complicated UPDATE trigger, let me know and I'll post a sample for you.)

And One More Idea

Put all your data in one table. Give columns generic names and then reuse/abuse them for multiple purposes. Create views over these to give them sensible names. Add columns when a suitable-data-type unused column is not available, and update your views. Despite my length going on about subtype/supertype, this may be the best way.


Recommendations on how to organize Queues in Service Broker I have a service broker application that currently has 5 or 6 queues on two servers.

The general workflow is:

Server A

User populates some tables
User fires stored proc which puts a message into a header queue indicating there is work to be done
Broker reads from this header queue and creates a potentially large number of messages to send to Server B

Server B

Messages all go to 1 queue
In this queue, message is examined and unbundled, payload is run and a response is collected from another set of procs/tables that we interact with
Response message is created and sent back to Server A

Server A

Response messages go to a special queue and are used to update tables the user populated at the top
End dialog messages are read from the initiator queue and used to update another table that tracks the progress of a header record (i.e. 50 out of 100 messages processed)
When we detect that the header record has been entirely processed, a new message is sent to another queue that sends a notification email

I've had a recommendation to consolidate some of those queues on Server A and I'm not sure what the best practice is for this.  Two of the queues will have a relatively high volume of message processing, and the email queue will be be pretty slow.

We did (I think) a good job of managing message types so theoretically we could put it all into one queue and the application would still function.

Is there a best practice on when to maintain separate queues and when to consolidate in a scenario like this?

This is an open question with no clear choice. YMMV so you have to test. Here is my opinion:

Having one queue to handle everything is a good choice if you want to be able to control the number of activated tasks, as there is no global max_queue_readers. Other than that, I don't see many advantages. One could argue that one single activated proc is easier to maintain, but I think you should use some sort of template/code generation when creating SSB activated process, since typically much is cookie-cutter code and little is specific to service business logic.

However I can see several potential problems with a single queue, and they're all the kind of problems that manifest only when things are already going bad (ie. under stress/load):

latency and processing serialization. With only one queue is possible to plateau the max_queue_readers at max and have messages which require little processing sit in the queue waiting their turn to be picked up. Separate queues allow low latency services to drain their queues fast, while high load queues churn at their own (slower) pace.
a spike of messages for any service affects all services. Same as above, but for spikes. A dump of a high number of messages (eg. some manual 'oop we need to reprocess this 1MM items') will cause all the other services on the same queue to wait until the spike is drained before they get access to their messages (is a bit more complex due to conversation group locking, but ultimately processing is FIFO).
avoidable visits into the ugly land of large queues. If big-queue problems occur on your RAM/IO/CPU/activate code at N messages, one-queue-for-all-services will reach N faster/more often than one-queue-per-service.

In general my recommendation is to have one-queue-for-each-service.


Before the changes actually take action, you need to run

FLUSH PRIVILEGES

EDIT:
Just saw that you tried to grant Privileges on a database which doesn't exists. You need to give them global privileges to create databases.


Should I use DynamoDB or stick with MySQL via webservice for my mobile app syncs I asked this earlier at Stack Overflow but a user advised me to ask it here since this website is more relevant to database related questions.

I made an Android app, name of which I'll not mention at this stage. It syncs with a central MySQL database via a webservice on LAMP, which also I created. It all works just great, no problems whatsoever. As the users use the app and perform certain actions, data from their devices gets uploaded to this central server which is running in a data center. As other mobile devices syncs with the database, they get updated with the newer data. At the initial install, app gets a few hundred K of data downloaded via few hundred queries.

Now the problem is that the app has started to get so many downloads that I can foresee I'll not be able to keep up with the sync. For a while I have been optimizing the code both on the Android end and on Web end (with iPhone version still under development), and also using caching techniques, but still, the communication between thousands of mobiles devices and one single database is reaching its limits. Everyday the number of downloads are more than previous day. Now I am extremely worried how to handle this situation.

I have been looking into using Amazon DynamoDB, have also went over its SDK, tutorials, etc. and am ready to spend a few days and switch this whole sync thingy to Amazon. But this will eventually cost me heavily, for which I'll have to charge for the app or use a better advertisement scheme. But that's another issue.

Unfortunately Googling for weeks hasn't helped me on figuring out how to efficiently handle large syncs with thousands of mobile devices, which might get into tens of thousands or probably hundreds of thousands.

In short, what to do to, lets say, deal with 100K devices using my app and in need of keeping in sync with a central database?

Kindly answer if you have faced a similar situation and know how to deal with it in real life.

Thanks in advance.

I don't have experience with DynamoDB so the best I can do is offer some pointers on optimizations to look for if you stay in the hosted MySQL world.

Memory allocation.  If you're using innodb be sure you have adequate buffer pool size with two log files as the lesser of 25% buffer pool size or 1 Gig.  In a perfect world your buffer pool would be as large as all the .ibd files (aka sum(data_length+index_length) from information_schema.tables where engine='innodb').  Understandably this is not always possible, in which case try to allocate 60-70% of your system memory to buffer pool while leaving the rest available for disk cache.  (This of course assumes this is a dedicated MySQL server not hosting apache or running your php scripts).

If you're running MyISAM tables then at least make sure your key_buffer_size can accommodate the index_length (or size of your .MYI files).

Disks.  If you're needing to fall back to disk you want those to be as fast as possible.  In a perfect world you'd have PCI-E based SSD cards.  I imagine this is out of your budget if you're worried about charging after switching to amazon, so maybe see if your hosting provider has any options above what you currently have.  SSD based SAS drives > 15k hdd sas > nl-sas.
Service layer between mysql and clients.  There is memory overhead to each connection to the database that could be used toward buffer pool or disk cache.  Try to have a service layer that maintains a bounded connection pool and providing application layer caching where possible.
Distribute the work load.  This is tantamount to going to the "cloud" just that you're managing all machines yourself.  If your app is mostly read heavy and not always required to be up to the microsecond up to date you can setup replication between many physical machines and load balance the reads across them.

Look toward getting slaves setup at different physical datacenters across the country/world/what ever's applicable to your demographic.  Load balance connections to the nearest one.  (This assumes traffic is first routed to your front end in these locations otherwise the problem might just be worsened).

Partitioning.  This is especially true if you're using myisam and dealing with table level locks.  It's also particularly useful if you have a table that has patterns of pruning old rows periodically (you can just drop partitions instead of running individual delete queries which fragment the data).


Are indexes bloated if using nvarchar field sizes that are much bigger than needed? I've been provided with a database where it appears the designer has used much larger field sizes than are needed. Commonly nvarchar (128) fields have been used but the data being stored in them is nowhere near that size.

If I create an index on one of those fields, does the index reserve the whole field size or just use the number of bytes used by the actual value stored in that field?

Aaron Bertrand's comments copied as an answer:

SQL Server just uses the actual number of bytes for storage, but take a look at your data size estimates - they'll be (half the declared byte size x number of rows) rather than (the average size x number of rows), and this can have a big impact on memory grants. Right-sizing is a good thing. These values can be found in the estimated data size (and any memory grant-related information) in a query plan.


PostgreSQL HA using HAProxy and Patroni I am currently trying to deploy Load Balancing using HAProxy for my PostgreSQL cluster (used Patroni HA solution). Now, I have been able to successfully implement the setup and Load Balancing is working to an extent. My HAProxy configuration :

global
    maxconn 100

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /

listen read_write
    bind *:5004
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 IP:5432 maxconn 100 check port 8008
    server pg2 IP:5432 maxconn 100 check port 8009

listen read_only
    bind *:5005
    mode tcp
    balance roundrobin
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg1 IP:5432 maxconn 100 check port 8008
    server pg2 IP:5432 maxconn 100 check port 8009

However, my question is how will the application interact with HAProxy for read only requests. Does the application need to connect using port 5005 or is there any internal logic applied by HAProxy to do this.

This is my first time implementing a Load Balancing setup, hence would appreciate your patience. :)

your application must use port 5005 for each query known to be read-only (not only SELECT, because some SELECT can call custom functions)


Speed difference between Drop table and Truncate table in Postgres I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again.
I'm wondering what the speed difference is if instead of drop and create, if I just truncate.

DROP & CREATE is slightly more expensive, as it actually deletes rows from some system tables (pg_class, pg_attribute, ...) in addition to removing the physical table files - and later has to parse etc. the new CREATE TABLE command, while TRUNCATE only removes the physical files for the table and starts new ones, keeping the catalog entries. But the difference is negligible for simple tables, especially for temp tables. And it gets smaller, yet if you factor in an additional ANALYZE that might be needed after TRUNCATE. Then again, you might need that in any case. See:

Are regular VACUUM ANALYZE still recommended under 9.1?

By "filling the tables" you mean COPY, I suppose? A much more costly difference would be to CREATE or TRUNCATE a plain table in a separate transaction before writing to it, as in this case you accrue the additional (substantial) cost of writing WAL (Write Ahead Log) entries. The manual:

  COPY is fastest when used within the same transaction as an earlier
  CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be
  written, because in case of an error, the files containing the newly
  loaded data will be removed anyway. However, this consideration only
  applies when wal_level is minimal for non-partitioned tables as all
  commands must write WAL otherwise.

Bold emphasis mine. (minimal is the default for wal_level.) Does not affect temp tables, which do not write WAL at all.

You might be interested in CREATE TEMP TABLE ...ON COMMIT DELETE ROWS. The manual:

  All rows in the temporary table will be deleted at the end of each
  transaction block. Essentially, an automatic TRUNCATE is done at each
  commit. When used on a partitioned table, this is not cascaded to its partitions.

Should be fastest. But the difference still typically small.


strange "using where" in simple query I have a table with this structure:

mysql> describe user_groups;

+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | int(11) unsigned     | NO   | MUL | NULL    | auto_increment |
| user_id  | int(11) unsigned     | NO   | PRI | NULL    |                |
| group_id | smallint(5) unsigned | NO   | PRI | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

primary key of this table is : PRIMARY(user_id, group_id)
I have simple query:

mysql> EXPLAIN SELECT `UserGroup`.`user_id` , `UserGroup`.`group_id` FROM `user_groups` AS `UserGroup` WHERE `UserGroup`.`user_id` IN ( 1, 2 );
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | UserGroup | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+

it has using where; , according it has additional disk seek,I know using where; concept, and Also I know it is not terrible , but conceptually I want to know why this query has using where;, although the condition is on primary key directly and it doesn't have order or anything that causes using where;.
How can I fix this using where;?

See the documentation.

  Using index

  The column information is retrieved from the table using only
  information in the index tree without having to do an additional seek
  to read the actual row. This strategy can be used when the query uses
  only columns that are part of a single index.

  If the Extra column also says Using where, it means the index is being
  used to perform lookups of key values. Without Using where, the
  optimizer may be reading the index to avoid reading data rows but not
  using it for lookups. For example, if the index is a covering index
  for the query, the optimizer may scan it without using it for lookups.

In short, your query is working as intended and is not performing an additional disk seek.


Export data from InterBase / Firebird database without credentials I have an InterBase / Firebird database file of an old project for which I lost the credentials. Some information about the database, dumped with the gstat command, is shown below [1]. I'm accessing this file as an embedded database, i.e., without a running instance of Firebird. I'm on MacOS and I've Firebird 2.5 installed, since On-Disk Structure (ODS) 11.2 corresponds to this version.

I'm able to export the database structure using the isql command, but I'm unable to access any data using this tool without a valid username and password, resulting in the following error message:

Error while preparing SQL statement:
- SQLCODE: -551
- no permission for read/select access to TABLE FILES', -551, 335544352

Providing the default SYSDBA/masterkey as the username/password results in the error message:

Error while connecting to database:
- SQLCODE: -902
- cannot attach to password database', -902, 335544653

Since encryption was introduced in the newer Firebird 3 and the data is stored unencrypted (I can view the data with a hex editor) I'd assume there is a way to access this data without credentials and make an export. How can I do so?

[1]: Output of the gstat command

Database "./DATABASE.GDB"
Database header page information:
    Flags           0
    Checksum        12345
    Generation      26450714
    Page size       4096
    ODS version     11.2
    Oldest transaction  25058616
    Oldest active       25058617
    Oldest snapshot     25058617
    Next transaction    25058619
    Bumped transaction  1
    Sequence number     0
    Next attachment ID  1401189
    Implementation ID   26
    Shadow count        0
    Page buffers        0
    Next header page    0
    Database dialect    1
    Creation date       Jul 21, 2014 13:42:21
    Attributes      force write

    Variable header data:
    Sweep interval:     20000
    *END*

The second error cannot attach to password database, that is given when the username and password are provided, is due to the fact that the security database couldn't be opened.

On macOS this file is located under:

/Library/Frameworks/Firebird.framework/Resources/English.lproj/var/security2.fdb

and is owned by the firebird user by default. This causes problems when the administrative tools (isql, gsec, gfix, etc.) are ran as a different user.

One way to solve this issue is to run those command as the firebird user. Another is to change the permissions of the security database:

chmod o+r /Library/Frameworks/Firebird.framework/Resources/English.lproj/var/security2.fdb

This solved the issue and data could be accessed using the default sysdba/masterkey credentials. These are not stored in the GDB file, but in the mentioned security2.fdb file, which explains why the default credentials can be used.


Install Oracle 10.2.0.4 on Windows Server 2012 R2 I have problem with installing Oracle 10.2.0.4 on Windows Server 2012 R2.

We are running old 10.2.0.4 instance on Windows Server 2008 R2. Now I have to migrate it to the Windows Server 2012 R2. Because the application using the db is old and without support, it is very risky to migrate the db to 11g or 12c. I know that 10.2.0.4 is no longer supported and 2012 R2 is not certified for 10.2.0.4.

I have tried the following steps:

To run the installer I have edited the [Certified Versions] in oraparam.ini:
Windows=5.0,5.1,5.2,6.0,6.1,6.2 (I have also edited refhost.xml the same way)
Then I have run the installer with "run as administrator" (I am logged as domain admin on the server) and I have installed "software only"
Then I have generated the create scripts using dbca and created the instance:

a) mkdir adump, dpdump etc.

b) c:\oracle10\product\10.2.0\db_1\bin\oradim.exe -new -sid test10 -startmode manual

c) c:\oracle10\product\10.2.0\db_1\bin\oradim.exe -edit -sid test10 -startmode auto -srvcstart system

d) c:\oracle10\product\10.2.0\db_1\bin\orapwd.exe file=c:\oracle10\product\10.2.0\db_1\database\PWDtest10.ora password=test10 force=y

e)I have copied the pfile to the c:\oracle10\product\10.2.0\db_1\database
now I want to connect to the idle instance to run startup nomount:

  Edit:

I am trying to connect this way under windows command prompt:

set oracle_home

set oracle_sid

sqlplus / as sysdba

But I get following error:

  ORA-12546: TNS:permission denied

Then I have edited sqlnet.ora and changed sqlnet.authentication_services = (nts) to (none) and tried to logon using password file - no success.

The ORACLE_SID variable is set correctly.

With no success with installing Oracle 10.2.0.4, we've tried 10.2.0.5.  The installation worked without any problem.


SQL Server: query performance (search 2 million rows) Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much?

Simple holiday search app. 2 million holidays. Paging/Sorting around 600,000 rows.

This is the schema of the table

CREATE TABLE [dbo].[Holiday](
        [Id] [int] NOT NULL,
        [PropertyId] [int] NOT NULL,
        [Price] [int] NOT NULL,
        [Rating] [int] NOT NULL,
        [Country] [char](2) NOT NULL,
        [ResortId] [int] NOT NULL,
        [DepartureAirport] [char](3) NOT NULL,
        [DestinationAirport] [char](3) NOT NULL,
        [DepartureDate] [datetime] NOT NULL,
        [Basis] [char](2) NOT NULL,
        [Duration] [int] NOT NULL,

     CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED ([Id] ASC)
  )

As you can see, quite simple. We have a property, a price, duration, departure/destination airports, etc. Now, the more field that are provided the faster the search. If I have a Departure Airport, Property and Date then the search is very fast. However, if I just have a Country and nothing else, there is a lot of data to work through.

Using this CSV export of my table, there are 2 million rows in total, and around 666k just with a country code of FR, which is my example.

This, is the search query. Which returns two tables. The first is a summary, so total number of holidays that match your criteria and how many unique properties. The second table contains the actual results from the search.

--Build a temp table, and store everything we need in it
CREATE TABLE #Pricing (PropertyId int, Duration int, HolidayId int, Rating int, Price int, StartDate datetime, PropertyRow int);

INSERT INTO #Pricing
  SELECT
    PropertyId, Duration, [Id], [Rating], [Price], DepartureDate,
    ROW_NUMBER() OVER (PARTITION BY PropertyId ORDER BY Price ASC) as PropertyRow
  FROM
    dbo.Holiday
  WHERE
    DepartureDate > GETDATE() AND Country = 'FR'

--Get a total number of holidays, and total number of properties
SELECT
    COUNT(*) AS TotalHolidaysCount,
    COUNT(DISTINCT PropertyId) AS PropertyCount
FROM
    #Pricing

--Build the final table, which will contain all the holidays we actually want to return
DECLARE @FinalResults TABLE (HolidayId int, RowNumber int);

INSERT INTO
    @FinalResults
  SELECT
    HolidayId, RowNumber
  FROM
    (SELECT
         PropertyRow, HolidayId,
         ROW_NUMBER() OVER (order by (CASE WHEN StartDate <= '01/Apr/2013' THEN 1 ELSE 0 END) ASC, [Price] ASC) as RowNumber
     FROM
        #Pricing
     WHERE
        PropertyRow = 1) as SearchResults
WHERE
    (RowNumber > (10 * (1 - 1)) and RowNumber <= (1 * 10))
ORDER BY
    RowNumber;

SELECT
     *
FROM
    @FinalResults
    INNER JOIN dbo.Holiday ON HolidayId = Holiday.Id

DROP TABLE #Pricing

Now, I could look into Indexing which would obviously improve performance. But what worries me, is the incredible use of Temporary tables. Surely this shouldn't be how it's done? Taking 5 seconds to search through what is ultimately a tiny amount of data. They only reason they are used is because of the need to reference the data later.

Would it maybe be worth running the query twice, instead of storing all of the data in memory? It seems a waste to select over 25% of the table into memory again and again.

Any helpful feedback would be appreciated. Not looking for the 'answer', just some assistance.

Many thanks,
Dean

Without indexes, any time  the search query is run, all 2 million records have to be searched to find the 650K 'FR' instances.  With indexes, the database can essentially go straight to them.  Even if you left the search query as-is, I think proper indexes would give you speed improvements.

As far as the temp table, I really don't see why that couldn't be done as a sub in the final results query.  Aside from that, I think temp tables would be more useful for persistent connections to the DB, or pools of users.  If you're just creating the temp table and immediately destroying it... then it's basically just being used as a subquery.

Update: M_M makes a good point in his comment on this answer.  However, I still feel an index would be better if the majority of activity is not just on sets where country is the only criteria.  For me (just my opinion) it would come down to how often the 'FR' subset would be needed by itself with no other criteria.  Otherwise, indexes could be used in the marjority of the searches.


When does PostgreSQL write logs? I turned on the configuration parameter log_planner_stats and would like to examine the details in log. But the log file is empty. It seems all the log files are changed at 12:00am of a day. Does PostgreSQL only writes log at that time? Is there any way to see the real-time log? BTW, I checked the log files in the directory pg_log which I guess is the right directory to find logs.

No, if PostgreSQL writes logs, then it writes basically real-time.  For example, if you open a log file with less and press ShiftF, you will get the newly written rows flowing in.

That you don't see anything in the logs can be caused by a few things, but first be sure you have read through the relevant documentation and set up everything accordingly.  There and in postgresql.conf you can also check where the DB writes the logs and so on.  Be aware that some configuration options need server restart or DB reload to take effect.


Database Hangs while impdp "DATABASE_EXPORT/STATISTICS/MARKER" at this stage I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

i am facing issue while impdp on other server. it hangs at the last line message below. it takes min. over 2-3 hours to continue. while importing i can not  find any error message on  alert log file or no any lock . then why it is taking too much time for importing ?

 -***************-
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER

here is my par file for impdp syntax :-

DIRECTORY=dir1
LOGFILE=abc_imp.LOG
DUMPFILE=abc.dmp
INCLUDE=SCHEMA:"IN('u1','u2','u3')"
include=role
content=METADATA_ONLY
TRANSFORM=SEGMENT_ATTRIBUTES:N

please let me know if any solution for this issue ?

Known bug, still under investigation.

Workaround is to use exclude=statistics.


How to encode a known date but unknown year? I am using a Postgres database, and have a table with a date-time field. For many records, we know the year but not the date. Sometimes we know the date but not the year. And other times we know the time but not the date.

How can I store partial date information while using the date-time data type?

The only thing that I can think of is to split the date into day/month and year - having one or the other as NULL as necessary. This is one of the only times I consider a split year/month/day structure, with separate fields, to be OK.


GROUP BY after ORDER BY/LIMIT/OFFSEt? I have two tables events and eventdate, where eventdate has a row event that corresponds to the id column in events. (So an event can have multiple dates). `

Now I am trying to get the unique events of the first five eventdates after a certain date. I tried this query:

SELECT ed."event"
    FROM public."eventdate" as ed
    GROUP BY ed."event"
    ORDER BY ed."start_date" ASC
    LIMIT 5
    OFFSET 0

but the following error is thrown:

  ERROR:  column "ed.start_date" must appear in the GROUP BY clause or
  be used in an aggregate function
  LINE 4:         ORDER BY ed."start_date" DESC

To be clear, I expect a list of ids, the list should have at maximum 5 entries, and no duplicates (a duplicate would just be "removed").

Given the following set:

id | event | start_date
1  | 1     | 0
2  | 2     | 0
3  | 1     | 2
4  | 4     | 3
5  | 3     | 4
6  | 1     | 5
7  | 5     | 6
7  | 6     | 6

The result would be:

event
1 (if I'd count the ids here it would be "2")
2
4
3

so basically the invariant would be: summing the count of each group = 5

If I get it right, you can use window functions:

select event
from (
    select ed.event, ed.start_date
         , row_number() over (partition by ed.event order by ed.start_date) as rn
    from public.eventdate
) as x
where rn = 1
order by start_date
limit 5

In the inner select, the events are given an ordering number per event. In the outer select, the oldest one per event is chosen and the result is ordered by this date.

If this is not what you meant, you can change your query so that the order by becomes deterministic, by adding an aggregate function (I used MIN here):

SELECT ed."event"
FROM public."eventdate" as ed
GROUP BY ed."event"
ORDER BY MIN(ed."start_date") ASC
LIMIT 5
OFFSET 0


Why pg_wal dir does not clean up while logical replication my main question - why pg_wal dir is extremely growing up?

I have two postgresql instances.

The first was set up as master:

wal_level: "logical"
max_wal_senders: 5
max_replication_slots: 10

Also, I was enable archiving for pg_wal:

archive_mode: on
archive_command: 'gzip < %p > /.../data/pg_wal_archive/%f'

After that, PUBLICATION was created for some of my tables (not ALL TABLES)

For replication psql instance - I using default psql config, only created SUBSCRIPTION to master.

And I have two questions:

I run some script on master, which pulling ~ 10 Gb data to one table, which was not included in the PUBLICATION. And, I see, that my pg_wal dir size is increasing anyway. Why?
Why pg_wal dir does not cleaning? Is it normal? Looks like I have some data for restore table which is not using in replication, how to delete this data?

archive_mode is used for for archiving, regardless of standby configuration. It can be used for point in time recovery. So when you perform DML on any table, it will generate WAL.

For cleaning up you can use archive_cleanup_command from recovery.conf


return field name and number of rows which meet condition in one sentence I have a table in postgres with 10 fields, and need to check for certain values in 3 of them, then return the field name which met the condition and the number of rows in each field.
So it's something like:
select count(*)
from table
where field1 ilike '%E+%'
OR field2 ilike '%E+%'
OR field3 ilike '%E+%'

and then return the field which met the condition AND the number of rows (which I'm not sure how to do it)

you can use conditional aggregation for that:
select count(*),
       count(*) filter (where field1 ilike '%E+%') as field1_matches,
       count(*) filter (where field2 ilike '%E+%') as field2_matches,
       count(*) filter (where field3 ilike '%E+%') as field3_matches
from the_table
where field1 ilike '%E+%'
   OR field2 ilike '%E+%'
   OR field3 ilike '%E+%'


Start and Stop entires in MYSQL Logs I am a newbie to MYSQL and I am trying to check where does MYSQL log start and stop entry with timings. I have issue in my application where I get communication link failure at random intervals and trying to check whether MYSQL was down at that time and it restarted after few minutes or not.

MYSQL installation details:

innodb_version 5.6.15
protocol_version 10
slave_type_conversions
version 5.6.15-enterprise-commercial-advanced
version_comment MySQL Enterprise Server - Advanced Edition (Commercial)
version_compile_machine x86_64
version_compile_os Linux

any help would be appreciated.

"where does MYSQL log start and stop entry with timings."

In the error log, it's formatted with a timestamp.

1st, locate your error log:

mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| log_error     | /usr/local/mysql/data/mysql-error.log |
+---------------+---------------------------------------+
1 row in set (0.00 sec)

Then just tail out the contents:

Fri May 02 $ tail -n 100 /usr/local/mysql/data/mysql-error.log
2014-05-02 08:36:35 1024 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
... irrelevant lines ommitted ...
2014-05-02 08:36:45 6473 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Furthermore, you can query the server status variable uptime to display the number of seconds the server have been up.

mysql> SHOW STATUS LIKE 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 13833 |
+---------------+-------+
1 row in set (0.00 sec)


How do I track the usage of XML indexes? My XML indexes are not listed in sys.dm_db_index_usage_stats

Does this mean they aren't being used? Or does this particular DMV not track XML index usage? (Books Online doesn't appear to answer the question)

If they aren't being tracked by sys.dm_db_index_operational_stats, is there another way to know how much they are being used, or what cached plans currently use them?

I tried to reproduce what you're experiencing and couldn't make it happen, unfortunately (see the second part of this answer). If this was a bugfix between versions, it was pretty well undocumented: running this through Google produced absolutely nothing.

I suspect the problem is that the analysis query you're running is joining sys.dm_db_index_usage_stats to sys.tables. Sounds reasonable, except XML indexes don't have the same relationship to their base table as regular clustered and nonclustered indexes do.

The way XML indexes work, the indexes aren't created against the base table at all. Creating a primary XML index materializes (i.e., creates a clustered index of) the node table that gets generated from the XML data; secondary indexes are nonclustered indexes on the node table. Okay, fine, so what? The catch is that the materialized node table is an internal table, and does not appear in sys.tables (have a look in sys.internal_tables instead). So you can see why you won't get rows back for those indexes at all unless all the rows from the DMV are getting returned with an OUTER JOIN in the query (and even then, you won't directly get a name for the table in that row).

To be honest, I only recently learned how XML indexes work, so I actually have to go back and revisit my own index analysis scripts as well (I suspect they have this exact flaw). Here's a quick base script I whipped together that should correct the problem:

SELECT
    OBJECT_NAME(COALESCE(t.object_id, t2.object_id)) AS TableName,
    i.name AS IndexName,
    us.*
    FROM sys.dm_db_index_usage_stats us
    INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id
    LEFT OUTER JOIN sys.tables t ON t.object_id = us.object_id
    LEFT OUTER JOIN sys.internal_tables it ON it.object_id = us.object_id
    LEFT OUTER JOIN sys.tables t2 ON t2.object_id = it.parent_object_id

This was run against my local instance (64-bit Developer edition, 10.50.1617):

CREATE TABLE XmlIndexTest
(
    Id int IDENTITY(1, 1) NOT NULL,
    Data xml NOT NULL,

    CONSTRAINT PK_XmlIndexTest
        PRIMARY KEY CLUSTERED(Id)
)
GO

INSERT INTO XmlIndexTest(Data)
    VALUES('<Invoice><LineItem Id="1" /></Invoice>')

CREATE PRIMARY XML INDEX IX_PrimaryXml ON XmlIndexTest(Data)

SELECT *
    FROM XmlIndexTest
    WHERE Data.exist('/Invoice/LineItem[@Id = 1]') = 1

SELECT i.name, us.*
    FROM sys.dm_db_index_usage_stats us
    INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id

CREATE XML INDEX IX_SecondaryXml ON XmlIndexTest(Data)
    USING XML INDEX IX_PrimaryXml FOR PATH

SELECT *
    FROM XmlIndexTest
    WHERE Data.exist('/Invoice/LineItem[@Id = 1]') = 1

SELECT i.name, us.*
    FROM sys.dm_db_index_usage_stats us
    INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id

and got these results:

Note how the object_id of the primary and secondary XML indexes is different than the object_id of the table's clustered index.


About GRANT statement for user creation I am new to databases, learning MySQL through a textbook.

My doubts are regarding grant statement.

I am learning on a localhost environment (both server and clieat are my own system).

FIRST DOUBT

If I create a new user account by

 GRANT ALL on mydb.* TO 'violetkiwi' IDENTIFIED BY '12345';

Command executed successfully.  When I tried to log into this account by

mysql -u violetkiwi -p

Access denied for user 'violetkiwi'@'localhost' (using password: YES)

If I specify @'localhost' in GRANT ALL issue is solved:

 GRANT ALL on mydb.* TO 'violetkiwi'@'localhost' IDENTIFIED BY '12345';

Is @'localhost' or @'IP ADDRESS' compulsory in GRANT ALL user account creation?

SECOND DOUBT is IPADDRESS specification in GRANT ALL for user account

Consider I have to connect to MySQL server over the internet.

How can I achieve connection to MySQL server through ADSL modem which doesnt have static IP to specify during user account creation with GRANT ALL?

THIRD DOUBT

Sometimes I can access MySQL without specifying -p:

mysql -u root;

mysql -u violetkiwi;

but I can't perform operations on DB. Why this access is provided without specifying password? Is it a bug in my system? Does it have a purpose?

Let's look at the way you created the user.

EXAMPLE : I have a database called zipcodes. I will create a user called rolando

mysql> grant all on zipcodes.* to rolando identified by 'blahblah';
Query OK, 0 rows affected (0.04 sec)

mysql> show grants for rolando;
+--------------------------------------------------------------------------------------------------------+
| Grants for rolando@%                                                                                |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rolando'@'%' IDENTIFIED BY PASSWORD '*446525BB82B5E22BD9E525261D37C494F623C52B' |
| GRANT ALL PRIVILEGES ON `zipcodes`.* TO 'rolando'@'%'                                                  |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

I specified rolando without an IP or netblock. By defualt, '%' is used. That should allow remote IP address to login.

You said you tried to login to your account with

 mysql -u violetkiwi -p

Why did this not work? Simply put, you were not connecting remotely. You created the user violetkiwi@'%'. You should create the user violetkiwi@localhost. Then, it can cleanly connect.

You need to make sure your mysql authentication scheme has been cleaned up

MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:

  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).

A remotely accessible account is an account whose host is % which allows anyone and his grandmother. To find those accounts, run this query:

SELECT user,host FROM mysql.user WHERE host='%';

You should also be cognizant of how MySQL user authentication occurs.

According to MySQL 5.0 Certification Study Guide pages 486,487, the following describes mysql's authentication algorithm:

  There are two stages of client access control:

  In the first stage, a client attempts to connect and the server either
  accepts or rejects the connection. For the attempt to succeed, some
  entry in the user table must match the host from which the client
  connects, the username, and the password.

  In the second stage (which occurs only if a client has already
  connected sucessfully), the server checks every query it receives from
  the client to see whether the client has sufficient privileges to
  execute it.

  The server matches a client against entries in the grant tables based
  on the host from which the client connects and the user the client
  provides. However, it's possible for more than one record to match:

  Host values in grant tables amy be specified as patterns contains
  wildcard values. If a grant table contains entris from
  myhost.example.com, %.example.com, %.com, and %, all of them
  match a client who connects from myhost.example.com.

  Patterns are not allowed for the User values in grant table entries,
  but a username may be given as an empty string to specify an anonymous
  user. The empty string matches any username and thus effectively acts
  as a wildcard.

  When the Host and the User values in more than one user table record
  match a client, the server must decide which one to use. It does this
  by sorting records with the most specific Host and User column values
  first, and choosing the matching record that occurs first in the
  sorted list, Sorting take place as follows:

  In the Host Column, literal values such as localhost, 127.0.0.1,
  and myhost.example.com sort ahead of values such as %.example.com
  that have pattern characters in them. Pattern values are sorted
  according to how specific they are. For example, %.example.com is
  more specific than %.com, which is more specific than %.

  In the User column, non-blank usernames sort ahead of blank usernames.
  That is, non-anonymous users sort ahead of anonymous users.

  The server performs this sorting when it starts. It reads the grant
  tables into memory, sorts them, and uses the  in-memory copies for
  access control.

SUMMARY

If you have any users that must have remote access, change the host of each user that has % into the public IP you will be connecting from.

For example, if the user myuser must connect from 208.21.42.184, replace the host like this

UPDATE mysql.user SET host='208.21.42.184' WHERE user='myuser' AND host='%';
FLUSH PRIVILEGES;

You should also be checking for the presence of any test databases. The presence of test databases. Any database named test or whose first five(5) characters are test_ need to be renamed immediately !!! I wrote an earlier post on how and why.


How to restore SQL Server 2008 R2 database to newly installed SQL Server 2008 R2 instance? Our SQL database server recently suffered a serious OS failure and had to be reinstalled from scratch. We are now attempting to associate the restored database and transaction log files into the new instance of SQL Server.

Our backups are up to date and contain the following two files:

Database.mdf (Primary data file: 170 MB)
Database.ldf (Transaction log file: 837 MB)

I know this is quite a basic question but I believe it is not a duplicate, is relevant and could be of great value to anyone browsing this site looking for similar answers.

The server is running SBS 2003 SP2 (x86).

Please let me know if I need to update this question with more information.

You can use SQL Server Management Studio and attach the database using GUI as described here.

Or using T-SQL, change the path to your drive & folder accordingly.

CREATE DATABASE MyDatabase
    ON (FILENAME = 'D:\MySQLServer\Database.mdf'),
    (FILENAME = 'D:\MySQLServer\Database.ldf')
    FOR ATTACH;

More at http://msdn.microsoft.com/en-IN/library/ms176061(v=sql.105).aspx.

The SQL Server Service Account needs read/write permission to those folders used to store the database files.

More reading on security and permission required at msdn.


Is there a way to construct SQL statement to return desired value only if there is a single row found, and return 0 otherwise? Consider below:

    SELECT
        customer.contact.id
    FROM customer.contact
    WHERE
        customer_office_id = [INTEGER]

If only a single row is returned, I want the result set to return the customer.contact.id, but if 0 or more than 1 rows are returned, I want the SQL statement to return 0 (that is, despite that there are multiple rows, or no rows, I want SQL to return a single row with a value that is 0)

Note - if there is a special SQL case for when only 0 or 1 rows returned are possible, I'd be interested in that as well, i.e if there are no more than 1 rows possible given the data.

One way is using COUNT and MIN/MAX:

SELECT
    CASE WHEN COUNT(*) > 1 THEN 0 ELSE MIN(contact_id) END
FROM
    contacts
WHERE
    customer_office_id = [int]
GROUP BY
    customer_office_id;

db<>fiddle here

To cover the case when there are no rows returned you can use:

SELECT
    COALESCE((SELECT
                  CASE WHEN COUNT(*) > 1 THEN 0 ELSE MIN(contact_id) END contact_id
              FROM
                  contacts
              WHERE
                  customer_office_id = [INT]
              GROUP BY
                  customer_office_id), 0) contact_id;

db<>fiddle here


Slave stoping with Last_SQL_Errno: 1032 I have added an extra Slave server to an existing MySQL Replication. The Master server and the old Slave server are working fine without any issue, but the newly added server is stoping with the following error:

  Last_SQL_Errno: 1032
                 Last_SQL_Error: Could not execute Update_rows event on table xxx.email_events; Can't find record in 'email_events', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000410, end_log_pos 368808733

It will be fine for some hours after repairing.

Questions

I'm a newbie,

how can I resolve this issue?
Can we permanently skip Last_SQL_Errno: 1032?
Is there any issue with skipping this error?

The way I prefer to make another Slave (S2) is to clone an existing Slave (S1)

Install the mysql on S2, but turn off mysql.
STOP SLAVE on S1.
Stop mysql on S1.
Copy the entire mysql tree from S1 to S2.
Copy my.cnf (or its include files) from S1 to S2, but be sure to change server_id.
Start mysql on S1; START SLAVE.
Start mysql on S2; START SLAVE.

Both S1 and S2 will then catch up on replication from the same point.

Apparently your creation of S2 failed in some way; I am recommending you start over rather than "fix" the inconsistent data.

If you would prefer to fix, then see Percona toolkit.


Storing shipping details in orders table Have orders table and among columns the following: shipping type, shipping service provider, delivery type and shipping address. Let me explain what these are.

User can choose between two shipping types: pick it yourself and use postal service.

If user goes for pick it yourself all other shipping fields are NULL. If he wants to use provider, then he has to specify which one (shipping service provider), does he want goods to be delivered at local postal office or at his home (delivery type) and shipping address.

This table looks good to me as it is, don't think it violates any of the first three normal forms, but want to check am I missing something? That is, should I introduce new table, shipping details for example, and why?

I would only introduce a shipping details table if you have a high number of orders going to the same place.  This would save on space as the address wouldn't be repeated multiple times.

But you might want to do something like that if you have users with multiple different shipping destinations that they use.


Indexing VARCHAR column? Should I add INDEX to my VARCHAR column? I need to select rows with a string, not with ID, will it speed my queries?

Example:

SELECT * FROM `cities` WHERE `slug` = 'new-york';

You plan to have queries of the type:

SELECT * FROM cities WHERE slug = 'new-york';

So, yes, you should definitely have an index on (slug).

Should it be UNIQUE index or a UNIQUE constraint?

That depends on whether you allow to have two rows in the table with same "slug". I think this is unlikely to be a requirement, as "slugs" are usually short terms to uniquely identify a thing (user, city, country, web page, etc).

So, yes, the index should be UNIQUE. MySQL makes no difference between UNIQUE INDEX and UNIQUE CONSTRAINT. I prefer to use the UNIQUE constraint syntax (which adds a UNIQUE index):

ALTER TABLE cities
    ADD CONSTRAINT cities_slug_uq     -- constraint name
        UNIQUE cities_sluq_uqx        -- index name
        (slug) ;


How can I join two tables while filtering the results with a" WHERE IN" clause? What I am trying to do is set up a selection filter for a cascading list on my site.  When I say selection filter, I mean I am trying to limit, and sort the results based on what the user selects.

I have set up the ability for my users to click a button and mark an item as a favorite.  That information is stored in "billdata.favoritebills".

Also, for efficiency I have saved vote totals for each bill in billdata.billvotesmeta.  billdata.billvotesmeta contains the column names: billid,totalvotes,yesvotes,presentvotes, and novotes. The primary key is billid.

My database is set up as follows:

billdata.bills

billdata.favoritebills

billdata.billvotesmeta

In this particular filter I am trying to set up, I want to limit the results to only being ones that the user has marked as a favorite and at the same time I want to order them from least to greatest total number of votes.

billdata.favoritebills contains the column names: userid and billid and a combination either exists in marked as a favorite or is deleted if it is unmarked as a favorite.

$testquery = "SELECT * FROM bills INNER JOIN bills ON billdata.billvotesmeta.billid = billdata.bills.id WHERE id IN ( SELECT billid FROM billdata.favoritebills WHERE userid='$_SESSION[userid]' ) ORDER BY DESC billdata.favoritebills.totalvotes";

$result = mysqli_query(dbcxn('bill'),$testquery)
     or mysqli_debug();

$fetched = mysqli_fetch_assoc($result);

The resulting output from PHP is:

Warning: mysqli_debug() expects exactly 1 parameter, 0 given in /opt/lampp/htdocs/unipartisan/includes/billlistcompiler.inc on line 38

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /opt/lampp/htdocs/unipartisan/includes/billlistcompiler.inc on line 39
1NULL

What should happen is that it limits the billdata.bills.* selection to only being favorites and then orders them by the joined table's total vote count.  The data from billdata.bills is the information used to build the page.

I think the problem is in the " WHERE id IN ( SELECT billid FROM billdata.favoritebills WHERE userid='$_SESSION[userid]' ) maybe this is not compatible with a query that has a join as I have not found anywhere else detailing a WHERE IN clause along with a join.
"

not familiar with mySQL syntax, but I believe there are multiple errors with your query. you've got an extra ")" or you tried to use "order by" in a subquery, the join clause looks weird, it's returning everything and there is no group by with sum(votes), etc.

For problems like these, it is best to start with a working query and expand it one step at a time. For example, first join 2 tables, then change the * to the list of fields you want, then restrict it with the third, then sum up votes, then sort them, ...

try:

SELECT b.id,sum(bm.totalvotes)
FROM
bills b
inner join favoritebills fb on b.id = fb.billid
inner join billvotesmeta bm on b.id = bm.billid
WHERE
userid='$_SESSION[userid]'
group by b.id
order by sum(bm.totalvotes) desc;

You may have to rename some fields. the names aren't clear to me.
if you want to add fields to your resultset, be sure to add them to the group by clause at the bottom too.


'text search dictionary "unaccent" does not exist' entries in postgres log, supposedly during automatic analyze I have many such entries in the postgresql main log, ever since upgrading to Postgres 10:

2018-03-28 08:51:00.281 CEST [97547] ERROR:  text search dictionary "unaccent" does not exist
2018-03-28 08:51:00.281 CEST [97547] CONTEXT:  automatic analyze of table "dbname.public.periodical"

I do use unaccent in many indexes in all tables (by making unaccent immutable (I know it is not the recommended way, but it is so historically)).

If I manually run vacuumdb dbname --table periodical or VACUUM periodical or VACUUM ANALYZE periodical, it does not generate such an error (neither on the command line, nor in the log).

Does anyone have a tip how I could stop these messages and what their real cause may be?

PostgreSQL version: 10.3 (Debian 10.3-1.pgdg80+1)

You are obviously using a fake-IMMUTABLE function wrapper for unaccent() to use it in indexes. It's based on the search dictionary "unaccent" implicitly or explicitly. The dictionary to be used can be provided as optional first function parameter to unaccent(). The name of the dictionary can be schema-qualified. Else it needs to reside in one of the schemas of the current search_path.

I do not advise to manipulate the function unaccent() provided by the extension. Instead, create and use a wrapper function like instructed here:

Does PostgreSQL support “accent insensitive” collations?

In your case, the additional module unaccent might be installed in a schema that's missing from the search_path of the session reporting these errors. When you try manually, you do that in a session where the search_path includes that same schema, so no error there.

You can either set your search_path to include the schema of the extension in all sessions, or schema-qualify the dictionary in the function calls of unaccent().

Security for client programs has been tightened with Postgres 10.3 / 9.6.8 etc. Now you need to schema-qualify function and dictionary when used in any indexes - as demonstrated in the linked answer above.

The release notes for Postgres 10.3:

  Also, the changes described in the second changelog entry below may
  cause functions used in index expressions or materialized views to
  fail during auto-analyze, or when reloading from a dump. After
  upgrading, monitor the server logs for such problems, and fix affected
  functions.

  ...

  Avoid use of insecure search_path settings in pg_dump and other client programs (Noah Misch, Tom Lane)

  pg_dump, pg_upgrade, vacuumdb and other PostgreSQL-provided
  applications were themselves vulnerable to the type of hijacking
  described in the previous changelog entry; since these applications
  are commonly run by superusers, they present particularly attractive
  targets. To make them secure whether or not the installation as a
  whole has been secured, modify them to include only the pg_catalog
  schema in their search_path settings. Autovacuum worker processes now
  do the same, as well.

  In cases where user-provided functions are indirectly executed by
  these programs — for example, user-provided functions in index
  expressions — the tighter search_path may result in errors, which will
  need to be corrected by adjusting those user-provided functions to not
  assume anything about what search path they are invoked under. That
  has always been good practice, but now it will be necessary for
  correct behavior.

Bold emphasis mine.

See the special mention of auto-analyze and autovacuum? To fix, schema-qualify function name and dictionary in the unaccent() call of the wrapper function used in your index(es) as demonstrated in the linked answer. Like:

CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE;

You can change an existing function definition with CREATE OR REPLACE FUNCTION ... without rebuilding existing indexes based on it. Make sure the function keeps using the same function & dictionary, then indexes keep working properly without REINDEX. (You can't DROP the function, though, while it's used in an index.)

Moving the unaccent module to the system catalog pg_catalog also works. Then everything is in the search_path automatically. You ended up doing so, but I do not advocate that. Messing with system catalogs is generally discouraged and can easily damage your installation if you don't know exactly what you are doing.

Related:

Error when creating unaccent extension on PostgreSQL

The search dictionary "unaccent" is also regularly used by text search configurations in full text search. Related:

Get partial match from GIN indexed TSVECTOR column
Finding the most commonly used non-stop words in a column


Select all columns from table but apply function to 1 column What I want to do: Select all columns but apply a function to one of the selected columns in a shorthand manner such that I don't have to type out all of the column names

Current approach: I am currently using select table.* to select all columns in a table.

Example table

Name data1 data2 data3 ....
a    1     1     1
b    1     1     1
c    1     1     1

Code:

SELECT * from table

Desired outcome: I want to be able to select all columns then apply a function to a specific column, is it possible to do this with select * or does it require selecting all column names and applying a function to my specified column?

Select all columns ... such that I don't have to type out all of the column names

But that is exactly what you should be doing!

Never use select * in production code.

Why?  Two reasons.

The database has to work harder, first to work out which columns exist, construct the query and then actually retrieve the data for you.  OK, the amount of extra work is small but involves core, data dictionary tables which can result in performance bottlenecks.
Databases are inherently shared environments.  Today, your query might be perfectly happy, pulling back all three of the columns in a given table.  Life is Good.  Tomorrow, someone else might add a hundred BLOB fields into that table and suddenly your query's performance goes through the floor, pulling back all that data in which your application has absolutely no interest.

Always specify the columns that your query requires explicitly.

Also, are those really your column names?  If so, then your table design worries me greatly.

Database != Spreadsheet.

Basic Data Normalisation rules should extract those repeated attributes out into a another [sub-]table, rather than running them "along" a row.


Aborted restoring from back up Sql Server 2008 I started restoring from backup using (WITH REPLACE, RESTORE WITH RECOVERY)
and realized that it was a mistake, so I stopped restoring.

Now this db is unavailable and has a 'Restoring' status.

Can I expect full rollback?
What should I do?

No there is no rollback.  You will need to restore from the most recent backup that you have.


Is the "comments" field in pgAdmin 4 a pgAdmin thing, or is it a PostgreSQL feature? If I right-click a table (or any object) in pgAdmin 4 and select "Properties", it brings up a GUI window which allows me to change the name of the table and many other things.
One of the fields is called "Comment". The manual for pgAdmin 4 just states this useless description:

Store notes about the table in the Comment field.

Where exactly is this stored? In the actual PostgreSQL database? Or some separate, special pgAdmin storage?
It seems to have nothing to do with the SQL comments such as:
-- This is an SQL comment.

Also, if I add such SQL comments inside my query, are they discarded by both PostgreSQL and pgAdmin, or added somewhere? For example, this query:
CREATE TABLE blablabla ...; -- This table is so cool, dude.

Would "This table is so cool, dude." just be stripped away by both pgAdmin and PG?
I looked in the PG manual but found no special "COMMENTS" feature, where the comment is actually stored in PG. For example, something like this:
CREATE TABLE blablabla ... WITH COMMENT 'This table is so cool, dude.';

Please clarify.

It's a Postgres thing
create table blabla (...);
comment on table blabla is 'This tables stores important information';


Why is FORCE JOIN option slowing down query and forcing execution plan to sort prior to joining tables? I'm linking Orders data with the Results data for those orders. Here's the SQL query for joining and selecting the
; with orders as (
    select o.*, otr.*, oo.*
    from Order o
    inner join OrderTransitionRecord otr
        on otr.Order_oid = o.ObjectID
    inner join OccurrenceOrder oo
        on oo.Order_oid = o.ObjectID
), results as (
    select ir.*, irsi.*
    from InvestigationResult ir
    left outer join InvestigationResultSuppInfo irsi
        on irsi.ObjectID = ir.ResultSuppInfo_oid
), anchor as (
    select orders.*, results.*
    from orders
    inner join results
        on results.Occurrence_oid = orders.OO_oid
        and results.IR_Patient_oid = orders.O_Patient_oid
        and results.IR_PatientVisit_oid = orders.O_PatientVisit_oid
)
select top 100000 *
from anchor

This takes ~35 seconds and follows this execution plan. If you look at the execution plan, you'll see that it first joins (OccurrenceOrder, OrderTransitionRecord) with InvestigationResult...and then joins that result with Order. My question is why doesn't it first join all three tables (Order, OccurrenceOrder, OrderTransitionRecord) that are inside the orders CTE prior to joining with the tables (InvestigationResult, InvestigationResultSuppInfo) that are in the results CTE?
I tried forcing the order of the joins by adding the OPTION(FORCE JOIN) option at the end of the select query. However, it slowed the query down  by a factor of 2 (~75 seconds). Here's the execution plan for when I force the join order. If you take a look at it, you'll see that the join order is forced correctly, but it has to sort the results of clustered index scans on all 3 Order tables before joining them. Why does it need to do that?
What can I do to speed up my query? I've already made sure that the tables have indices for all the columns that are needed in all the joins. Any and all help would be much appreciated. Thanks
Original

Force Order

Okay, after heeding advice from the comments, I removed the top 10000 * from the select statement. After doing so, forcing the join order actually sped up the query as intended. It took 48 seconds (vs. 60 sec).

Here's the original execution plan.

And here's the forced join execution plan.


PostgreSQL - Simple column binding I have many tables of the same length (number of rows) that were previously ordered by some columns as they were being written on disk.

My SELECT is of the form:

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 LEFT JOIN t2 ON t1.id=t2.id
        LEFT JOIN t3 ON t1.id=t3.id
        ...

I know by how these tables were populated that this result corresponds exactly to simple column binding, so that ON t1.id=t2.id and ON t1.id=t3.id can be omitted. The problem is, as far as I know, RDBMSs can operate only projections and row binding. Column binding isn't a typical operation.

So the question is the following: is it possible to bind columns "as they are" without specifying any join criteria? For example, the following query, which is syntactically wrong, should explain what I mean:

SELECT t1.id, t1.value, t2.value, t3.value, ...
FROM t1 COLUMN_BIND t2 COLUMN_BIND t3 ...

I'm using PostgreSQL 9.4.5.

Data Example

Input tables:

t1.id | t1.value
----------------
    1 |       a
    2 |       a
    3 |       b
    4 |       a
    5 |       c
    6 |       a

t2.id | t2.value
----------------
    1 |       g
    2 |       g
    3 |       h
    4 |       g
    5 |       o
    6 |       l

t3.id | t3.value
----------------
    1 |       e
    2 |       e
    3 |       e
    4 |       e
    5 |       q
    6 |       e

Expected result:

t1.id | t1.value | t2.value | t3.value
--------------------------------------
    1 |       a  |        g |       e
    2 |       a  |        g |       e
    3 |       b  |        h |       e
    4 |       a  |        g |       e
    5 |       c  |        o |       q
    6 |       a  |        l |       e

To me this looks like a quite typical join:

select t1.id,t1.value,t2.value,t3.value
from t1,t2,t3
where t1.id=t2.id and t1.id=t3.id


MySQL 5.5 not starting after master-master configuration The following master-master configuration worked on MySQL Server 5.1.x(Ubuntu 10.04).

Master1(192.168.2.80)

##################################
#MySQL Replication
##################################
skip-host-cache
skip-name-resolve
event_scheduler = ON
max_connections = 500
max_connect_errors = 1000

server-id = 10
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

master-host = 192.168.2.81
master-user = repli
master-password = secret
master-connect-retry = 60

binlog-format = MIXED
#binlog-ignore-db = information_schema
#binlog-ignore-db = lb1

#max_binlog_size = 500M
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index

log-slave-updates
report-host = 192.168.2.80
replicate-ignore-db = information_schema
replicate-ignore-db = lb2
replicate-ignore-db = performance_schema

relay_log_space_limit = 3G
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index
##################################

Master2(192.168.2.81)

##################################
#MySQL Replication
##################################
skip-host-cache
skip-name-resolve
event_scheduler = ON
max_connections = 500
max_connect_errors = 1000

server-id = 20
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2

master-host = 192.168.2.80
master-user = repli
master-password = secret
master-connect-retry = 60

binlog-format = MIXED
#binlog-ignore-db = information_schema
#binlog-ignore-db = lb2

#max_binlog_size = 500M
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index

log-slave-updates
report-host = 192.168.2.81
replicate-ignore-db = information_schema
replicate-ignore-db = lb1
replicate-ignore-db = performance_schema

relay_log_space_limit = 3G
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index
##################################

Now it's NOT working on MySQL Server 5.5(ubuntu 12.04). I mean to say when I restart the mysql service after above configuration, mysql server takes long time and dont' start at all. Nothing in mysql logs and are empty. I tried killing mysql and starting it again but still it's not starting and if I remove the above master-master replication configuration then it starts usually. What's wrong I am doing with mysql 5.5?

Thank you!

What's the exact version of 5.5 you're running.  If you're running 5.5.24 you might be running into this bug https://bugs.launchpad.net/percona-server/+bug/1008278.

If you are running 5.5.24 ensure userstat=OFF.  Really though this is annoying so you might look at just upgrading beyond that.


Is there a cool way of performing CU updates for SQL Server on hundreds of machines? My company has grown from 50 SQL Servers to over 200 overnight in a merger acquisition. HELP!

My questions are:

How can I perform CU updates on this many servers and still have a life and keep my sanity? SCCM doesn't seem to be able to perform CU updates.
Has anyone else been able to perform CU updates on 100s of servers?
What are some other alternatives?

The cool way is to use an unattended installation script that simply is called from each server, where the media for the installation is on a central directory on your network, that is accessible from each server. You will have to run the script in an elevated mode, which this is fairly easy in PowerShell.

You will need to use the CLI to extract the hotfix, I found that here. Then the CLI options for the SQL Server patch can be found here.

The following commands are what I used to apply the CU6 patch to the local SQL Server 2012 SP2 instance on my laptop. I changed to the directory I downloaded the hotfix to:

.\SQLServer2012-KB3052468-x64.exe /X:E:\Software\SQL_Server\2012\SP2_CU6\Extracted
cd Extracted
.\setup.exe /action=patch /instancename=SQL12 /quiet /IAcceptSQLServerLicenseTerms

You pretty much just work out writing this as a script for each server, then pull in that bit of the summary log (or just the whole log) to a central spot so you can go back and determine the status. Option 2 would be to just include a few lines of code to connect to each instance and verify the build number.


Duplicate rows with a few changes in a table with a composite primary key, but only if target does not exist Consider the following table:

CREATE TABLE [xxx].[MyTable](
    [period] [date] NOT NULL,
    [genusId] [int] NOT NULL,
    [subjectId] [int] NOT NULL,
    [waitingStageId] [tinyint] NOT NULL,
    [value] [tinyint] NULL,
    [savedOn] [datetime2](2) NULL,
    [savedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_ExpectedActivity] PRIMARY KEY CLUSTERED
(
    [period] ASC,
    [genusId] ASC,
    [subjectId] ASC,
    [waitingStageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 92, 1, 1, CAST(N'2017-05-29T13:45:20.6300000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 96, 1, 1, CAST(N'2017-05-29T13:45:21.2400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 128, 0, 2, CAST(N'2017-05-29T13:45:21.6500000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 128, 1, 3, CAST(N'2017-05-29T13:45:21.4400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 35, 0, 1, CAST(N'2017-05-26T20:05:40.2800000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 94, 0, 3, CAST(N'2017-05-26T20:05:40.6700000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 94, 1, 1, CAST(N'2017-05-26T20:05:40.4800000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 23, 0, 6, CAST(N'2017-05-26T20:03:26.5400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 47, 0, 6, CAST(N'2017-05-26T20:03:26.7700000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 67, 0, 3, CAST(N'2017-05-26T20:03:27.1800000' AS DateTime2), NULL)
GO

I am trying to create a stored procedure that will allow us to duplicate values from the previous month into the current month -- BUT ONLY if there isn't a value for the current month already.

DROP TABLE IF EXISTS #PreviousMonth
DECLARE @schemaName AS varchar(5) = 'xxx' -- To be used later as a parameter in stored procedure
DECLARE @lastMonth AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AS DATE) --First day of previous month
DECLARE @currentMonth AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) --First day of current month

-- Add rows for existing month into a temporary table
SELECT @currentMonth AS period, genusId, subjectId, waitingStageId, value, GETDATE() AS savedOn, '<Automated by Stored Procedure>' AS savedBy INTO #PreviousMonth FROM [xxx].[ExpectedActivity]
  WHERE period in(@lastMonth)

SELECT * INTO xxx.MyTable FROM #PreviousMonth
-- Here is where I got stuck. I tried WHERE caluses and NOT EXISTS but my skills are limited.

This is as far as I managed to get unfortunately. My skill is limited where it gets to do an INSERT with a condition with the NOT EXIST condition.

How can I make sure that I only insert rows in the xxx.MyTable where rows with the same primary key (period AND genusId AND subjectId AND waitingStageId) does not exist already?

Taking what your query says literally, you don't have to worry about what exists in xxx.myTable - because your statement will create it, therefore it won't have anything in it (or, if it already exists, your statement will fail). Presumably, that's not really what you were going for.

If we assume that xxx.myTable does exist, and may already have some rows whose values match your defaults, then this should work.

INSERT INTO xxx.myTable (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
  FROM #PreviousMonth pm
 WHERE NOT EXISTS (SELECT 1
                     FROM xxx.myTable
                    WHERE period = pm.period
                      AND genusID = pm.genusID
                      AND subjectID = pm.subjectID
                      AND waitingStageID = pm.waitingStageID
                  )
;

In the NOT EXISTS clause, you're checking if the current row from the temp table matches the primary key values for any rows already in xxx.myTable. If you find a match for the current row, then you won't SELECT it (and thus, won't INSERT it).

You can also write this as what's called an "anti-join":

INSERT INTO xxx.myTable (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
  FROM #PreviousMonth pm
         LEFT OUTER JOIN xxx.myTable mt ON (    pm.period = mt.period
                                            AND pm.genusID = mt.genusID
                                            AND pm.subjectID = mt.subjectID
                                            AND pm.waitingStageID = mt.waitingStageID
                                           )
 WHERE mt.period IS NULL
;

Here, we're matching all the rows in the temp table with any matching records in xxx.myTable. via a LEFT JOIN. Because of the left join, if there's no matching row in xxx.myTable, we'll still return columns from that table, but all the values will be NULL. So, if we find a row where period is NULL in xxx.myTable, that should mean that there isn't a matching xxx.myTable row for our temp table row. So, only those rows (temp table rows with no matching xxx.myTable row) will be SELECTed, and thus will be INSERTed.

In most cases, I wouldn't expect dramatically different performance between the two versions; test in your specific case, to see if one seems better than the other.

UPDATE: If you want to use the previous month's data anywhere the key fields don't exists, or where they do exist but the value = 0, you've got a couple of options available.

You could delete all rows in xxx.myTable where the value is 0, then do the insert as above.

I would be inclined to UPDATE the existing rows, and then perform the INSERT as above. The UPDATE statement would be:

UPDATE mt
   SET value = pm.value
  FROM xxx.myTable mt
         INNER JOIN #PreviousMonth pm ON (    mt.period = pm.period
                                          AND mt.genusID = pm.genusID
                                          AND mt.subjectID = pm.subjectID
                                          AND mt.waitingStageID = pm.waitingStageID
                                         )
 WHERE mt.value = 0
   AND pm.value <> 0
;


How to improve performance to 12million rows table? I have performance issues on a query in a big table (12 million records) based on Geonames, that's a read-only database so NO DELETE, UPDATE or INSERT only SELECT.
There are queries I make every now and then filtering by different columns that are not keys (latitude and longitude, fcode and country, only name, etc..), the thing is that with my server resources it takes more than 30 seconds to complete them.
I have made views and small tables (clone of the big table but with only data from one country) to check how to improve it.
With the views, I get similar results than in the big table and using explain I have seen that views check the same amount of rows as the big table (12million rows)
In one of the small tables I get less than 200 milliseconds, more or less depending on the table size.
I'm not a database expert but duplicating data in small tables feels awkward. I'm not sure if that's the best approach that can be done there.
All queries are being sent from my backend, so no stored procedures.
The queries done filtering by primary keys works blazing fast though!.
Thanks in advance for any advice!

UPDATE FOR COMMENTS

Table definition

CREATE TABLE `geoname` (
  `geonameid` INT,
  `name` VARCHAR(200),
  `asciiname` VARCHAR(200),
  `alternatenames` VARCHAR(4000),
  `latitude` DECIMAL(10,7),
  `longitude` DECIMAL(10,7),
  `fclass` VARCHAR(1),
  `fcode` VARCHAR(10),
  `country` VARCHAR(2),
  `cc2` VARCHAR(60),
  `admin1` VARCHAR(20),
  `admin2` VARCHAR(80),
  `admin3` VARCHAR(20),
  `admin4` VARCHAR(20),
  `population` INT,
  `elevation` INT,
  `gtopo30` INT,
  `timezone` VARCHAR(40),
  `moddate` DATE,
  PRIMARY KEY (geonameid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What kind of indexes could I add there?

I think I have been using that table.  It is clunky when you want to see states/provinces and other things like that.
Sure break out a country if that is all you need.  But don't plan on breaking out all ~250 countries into separate tables (plus continents, etc).
VIEWs are not performance enhancers.  They can hide the clumsy nature a table like that one.  (Especially due to the fcode checks.)
This may help:
INDEX(fcode, country_code)

WHERE feature_code LIKE 'PCL%' AND ...
WHERE feature_code = 'ADM1' AND country_code = 'ES'

If you would care to provide the desired queries (not the views) and the desired table(s), I may be able to provide more suggestions.
Lat/lng searches
Lat/lng needs more work than simply a composite index.  Suggest you start with a "bounding box" and these two composite indexes:
INDEX(lat, lng),
INDEX(lng, lat)

If such searches are not fast enough, then look at more complex methods in http://mysql.rjweb.org/doc.php/find_nearest_in_mysql


Insert into table with identity insert taking almost a second We have an oltp application and I am trying to troubleshoot why insert into one of our table is taking close to a second.  Not all inserts are taking this much time but there are some which does take close to a second and since this insert is part of business transaction which can only take around 200ms, a one second insert is a  problem.

This table has a primary key which is identity and is nonclustered.  Clustered index is another column.

The insert stored procedure is very simple.  Something like

Create proc usptable_insert
( a bunch of parameters)
as
insert into table (col1, col2 ........)
values (parameters1, parameter2...... etc)
DECLARE @Id INT = (SELECT SCOPE_IDENTITY());
SET IDENTITY_INSERT [table2] ON
insert into table2 (col1, col2 ........)
values (@Id, parameter2...... etc
SET IDENTITY_INSERT [table2] OFF

We are in the process of migrating from table1 to table2 hence inserting into two tables and using identity insert to make sure table2 has same keys.

First two rows are the the insert statements

Initially I thought it was because of latch contention because of hotspot issue on the last page.  But I tried to reproduce this in pre-prod by running this stored procedure through sqlstress with 100 threads and couldn't.

If I am not wrong this behaviors cannot be blocking as its an insert and unless something is blocking the whole table which is not the case.

What can be other reasons for this behavious?

If I am not wrong this behaviors cannot be blocking as its an insert
  and unless something is blocking the whole table which is not the
  case.

If there are bigger queries reading data from table or table2, they could take a table-level shared lock, resulting in blocking.

You could test with read commited snapshot isolation level and use row versioning if blocking is the case.

You need to know the implications (version store will grow, long running open transactions can be problematic for your tempdb, row versioning is on query level, not transaction level.)

To enable RSSI:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

Look into what your app supports, an example of when not to use RCSI is when application code is counting on pessimistic locking. More on that here

Autogrowth

Another reason could be an autogrowth event triggering as a result of the insert.
The time this takes depends on the autogrow settings, hardware, instant file initilization, .... this could take longer than expected, and be a blocking factor for your inserts as they have to wait for these new pages to be available.

Trace them with an extended event. This event should also provide you the query that made the autogrowth occur.

Pregrowing your files on idle moments could work to solve this.