setting up multi-user, multi database server(sql)

As Max said, you can do this with the free SQL Server edition, SQL Server Express. You should be aware of some of the limitations (on top of the fact that I have no idea which JDBC client / library / driver / provider to recommend, and whether it will have any of its own problems):

Each database is limited to 10GB in data files;
each instance is limited to 1 GB of memory and 1 socket / 4 cores;
there is no SQL Server Agent or Database Mail; and,
many other features are simply not present (see the full feature list).

If those limitations don't affect you, then okay, SQL Server Express is a candidate. That said, just having SQL Server Express installed on your home machine is not the end of the story. You will need to consider the following things, and may need to speak to your ISP to resolve some of them:

They probably do not allow incoming connections on port 1433 (or virtually any other port that you could use for SQL Server). If you discover an open port that you're not already using, it may work technically, but you are likely violating your terms of service, since they typically do not support or condone you running your own services that are accessed remotely. If that port isn't part of the service they have guaranteed, they could shut that port down at any time and without warning.
Your IP address may change frequently and unpredictably, so you would need some kind of dynamic DNS service to keep that reliable (or constantly have to tell your students the new IP whenever it changes). I'm not sure which of these services are free, but you can do some of that research on your own to see if any provider makes sense for you.
Depending on what they are doing with the data, you could very easily exceed your bandwidth cap and - if you are trying to do this on the sly - alert them that you are hosting services (see #1). They may have clauses in your contract whereby they can terminate the agreement or upcharge if they deem the activity to constitute any type of denial of service attack.

So without any problems? Maybe. It depends on how your ISP operates, but I suspect that is a tall order.


adding cluster index other than primary key default cluster index

can we add cluster index on column (name) too?

No. Only one index is clustered.

For MySQL:

If primary key exists it is clustered.
If no primary index but unique indexes exists then first unique index is clustered.
If no unique indexes exists then the index by inner record number (hidden, unaccessable) is clustered one.


Collation issue after update (KB3072779) of SQL Server 2012

I would go and look at the setup.log files for the Service Pack 3 Upgrade. They should be found here:

Each execution of Setup creates log files are created with a new timestamped log folder at %programfiles%\MicrosoftSQL Server\110\Setup Bootstrap\Log\. The time-stamped log folder name format is YYYYMMDD_hhmmss. When Setup is run in an unattended mode, the logs are created at % temp%\sqlsetup*.log. All files in the logs folder are archived into the Log*.cab file in their respective log folder.

More information can be found here: View and Read SQL Server Setup Log Files (Microsoft)

This might provide you with information on what happened.

If you feel like rebuilding you SQL Server instance with the correct collation, then you could try the solution here:

How to change server level collation for a SQL Server Instance

There are slight variations of above post involving re-installing the system databases and or just modifying some objects:

Changing SQL Server Collation After Installation
Script and Alter SQL Server Database Objects with PowerShell to Correct Collation Issues

It's not highly recommended and is best tested in a non-productive environment first. Currently there are no documented cases of the collation being changed during the setup of a service pack on SQL Server. If you don't find any information in the setup logs, then I would try and contact Microsoft and open a case/ticket.


Efficiently DROP huge table with cascading replication in PostgreSQL

DROP TABLE sends very little information over the WAL stream, and the information it does send is independent of how many rows the table has in it.


How do I write a conditional statement (IF) with MySQL?

What you want is a CASE expression which is standard-SQL method of implementing a conditional and supported by every major database,

SELECT CASE WHEN 1 = 1 THEN 1 ELSE 12 END;

Or you can use the totally silly and proprietary IF statement

SELECT IF( 1=1, 1, 12 );


Connecting two different 'user' tables into one single table

Option 1: Single table

If you don't really have a lot of unique columns that belong only in tb_student or in tb_teacher, then it might make sense to combine everything in the tb_user table, and there would be no need for the tb_teacher and tb_student tables. They would be superfluous. But there could be reasons for keeping them separate, like maybe relationships to the other tables in your schema.

Option 2: Normalisation

So if you try to follow the principles of normalisation, and keep three separate tables, then all columns that are unique to the tb_student tables should be found only there, and likewise with the tb_teacher table. (You don't have anything unique to the tb_teacher table at the moment, but maybe you'll think of something later?) You will need to add a column e.g. id_tb_user in each of the tb_student and tb_teacher tables, and declare foreign key constraints for these columns referring to the tb_user table. E.g.:

CREATE TABLE tb_teacher (
...
  id_tb_user int unsigned,
...
  CONSTRAINT tb_teacher_fk1 FOREIGN KEY (id_tb_user)
    REFERENCES tb_user(id_user)
...
)

The tb_user table will contain all the shared columns: name, status and some of the other columns you already have, but not entry_year.

To retrieve information about students, including info about them in the tb_user table, you will then use a query with a simple INNER JOIN:

SELECT s.id_student,
  s.entry_year,
  u.username,
  u.name -- + any other columns you want
FROM tb_student s
  INNER JOIN tb_user u ON s.id_tb_user = u.id_user
WHERE
  u.username = 'spectrum@unida.gontor.ac.id';
  -- or any other condition you want

(To retrieve info about students and teachers in a single query, use instead two LEFT OUTER JOINs.)

By the way, you should also explicitly declare your primary keys as such. It's also a good idea to use int unsigned as the data type for these columns (or even bigint unsigned, although that is rarely needed), and it's also often a good idea to use auto_increment (see also the FAQ) so that MariaDB/MySQL does the job of generating a new id when you insert a new row:

CREATE TABLE tb_user (
    `id_user` int unsigned auto_increment PRIMARY KEY,


Ok to drop indexes on FK's if they have no stats in the DMVS

As long as you are sure the server was up for that entire time, and that nobody cleared out DMV stats inadvertently. This can happen if the database is detached + re-attached / restored / offline + online / auto-close + online, or if the index has been explicitly dropped / re-created (the DMV is not affected by disable / rebuild / reorganize, except in the case of SQL Server 2012, where rebuild currently clears the stats - I suspect that will be fixed - thanks @MartinSmith).

You should expect that if a DML activity used the index in a read capacity for some reason (e.g. to perform an update on some other table), this would register as a read activity, not a write. All of the writes you see are index maintenance.

As an aside, here is a slightly more efficient way to write this query. I removed the twice-referenced correlated subquery and dropped the unnecessary joins to sys.objects and sys.schemas. I also fixed a few minor syntax things, such as prefixing all columns with aliases where appropriate, placing reserved words in square brackets, and removing AS 'column alias' syntax. I found it confusing that half way through the SELECT list you changed from alias = expression syntax to expression AS alias syntax - you should pick one and be consistent IMHO. I changed these both for majority within the query and also according to my personal preference. :-)

SELECT name = OBJECT_NAME(s.[object_id])
    , indexname = i.name
    , i.index_id
    , reads = s.user_seeks + s.user_scans + s.user_lookups
    , writes = s.user_updates
    , g.[rows]
    , reads_per_write = CASE
        WHEN s.user_updates < 1 THEN 100.0
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END
    , [drop statement] = 'DROP INDEX ' + QUOTENAME(i.name)
      + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id]))
      + '.'    + QUOTENAME(OBJECT_NAME(s.[object_id]))
    FROM sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i
      ON i.index_id = s.index_id
      AND s.object_id = i.object_id
    INNER JOIN
    (
      SELECT [object_id], index_id, [rows] = SUM([rows])
        FROM sys.partitions GROUP BY [object_id], index_id
        HAVING SUM([rows]) > 2000
    ) AS g
      ON i.[object_id] = g.[object_id]
      AND i.index_id = g.index_id
    WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    ORDER BY name, reads;


How can I specify the position for a new column in PostgreSQL?

ALTER TABLE ADD COLUMN will only add the new column at the end, as the last one.
In order to create a new column in another position you need to recreate the table and copy the data from the old/current table in this new table.


Don't use offset - it has to read all the rows to skip them.

If you order rows for pagination by created_at, then thats the column you need to use.

First page:

SELECT ... FROM table
ORDER BY created_at, id
LIMIT N + 1;

The +1 gives you a peek to the next page - you can quickly decide if next page exists and where does it start. If created_at is not guaranteed to be unique, adding the ID to the ordering helps keep it stable.

Next page:

SELECT ... FROM table
WHERE created_at >= <(x+1)th created_at from previous page>
    AND id >= <(x+1)th id from previous page>
ORDER BY created_at, id
LIMIT N + 1;

You use the extra item from fetching the previous page to quickly find the start of current page using the ordering index to get new page.


Storing a large number of small size entries in MySQL

It depends on exactly what you're trying to do with the data -- if it's only being used for graphing, and you don't need high resolution the further out it goes (eg, you're not trying to plot a graph with full temporal resolution for a day from 1 year ago), you might actually want to look at RRDTool rather than a relational database.

If you're going to ever need to do analysis of the values (how often does a place get above 80°F?), you'll want to store discrete values, not an XML structure;  but you could also use flat file stuctures that are meant for dealing with this type of data (eg,  CDF, NetCDF ... maybe even HDF )

update :

I'd store each time as a separate record, as it makes it easier to adjust the granularity when graphing.  For instance, to extract the high/low/mean for each hour:

SELECT   min(date_obs),min(temp),max(temp),avg(temp)
FROM     observations
WHERE    date_obs between ...
GROUP BY floor(unix_timestamp(date_obs)/3600)

Also, it allows you to change the cadence for the measurements without needing to change the table structure.


How can I improve my query, using joins & improving performance?

You have an extra unnecessary subquery check, and your subquery seems to only return a rowset if there is a single value in it, so you may want to use = instead of in. You can achieve the same by doing this:

UPDATE product
  SET deleted_at = NOW()
WHERE id = (
    SELECT entity_id
    FROM product_media
    WHERE media_id = ?
    GROUP BY entity_id
    HAVING COUNT(media_id) = 1
)


DB2 online restore - “SQL1035N The database is currently in use.”

[This answer was also posted to the duplicate question on Stack Overflow]

When you perform an online restore, DB2 must lock the tablespace(s) you are trying to restore. The restore process essentially overwrites the file on disk containing the tablespaces' data. This is incompatible with applications using data in the same tablespace while the restore occurs.

If your database has all data in a single tablespace, then an online restore is not particularly useful. If you have multiple tablespaces in the database, applications may be able to continue functioning while the corrupted tablespace(s) are restored, but of course this requires some planning in your application and database design.


Looping through values stripping 1 character at a time

Please don't use loops for things like this (I'd also reserve recursive CTEs for scenarios where you have much less control over things, like hierarchies). Loops are bad in SQL; SQL is optimized to work in sets.

DECLARE @foo TABLE(ID INT);

INSERT @foo VALUES(34679),(13390),(89906);

;WITH x AS
(
  SELECT TOP (2048) n = ROW_NUMBER() OVER (ORDER BY Number)
  FROM master.dbo.spt_values ORDER BY Number
)
SELECT RIGHT(f.ID, x.n) FROM x
INNER JOIN @foo AS f
ON x.n < LEN(f.ID);

Results:

9
79
679
4679
0
90
390
3390
6
06
906
9906

http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3


Update statement with "case syntax" is unable to update a specific column in temp table

Here is the solution: (The update statements had to be rearranged so they executed on there own.)

UPDATE  [#tempShip] SET  [ord_status] = CASE
                WHEN RTRIM([ord_status]) = 'N' THEN 'New'
                WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
                WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
                ELSE ''
                END

UPDATE                 [#tempShip] SET           [#tempShip].[DUP] = 1
FROM #tempShip
INNER JOIN
(
SELECT X.order_N_ext, COUNT(X.order_N_ext) AS dup
FROM #tempShip AS X
GROUP BY X.order_N_ext
) AS Y
ON #tempShip.order_N_ext = Y.order_N_ext
WHERE Y.dup > 1;
    ----- -- mbs 3/30/2017  #22844 - add parameter @ship_type
    SELECT * FROM #tempShip
    WHERE ship_type LIKE @ship_type
    -------<<<


How to replicate and sync MongoDB localhost to a remote server using replica set

Please use rs.add("host:port") command to add new node to replica set.

The main problem is that you can NOT mix localhost and IP addresses. If one member is on localhost, every member must be on localhost

You can of course use procedure:

var cfg=rs.conf()
cfg.members
<copy that to text editor and make changes>
cfg.members=<paste here edited members section>
rs.reconfig(cfg)


The command

ALTER TABLE ADD FULLTEXT INDEX index (column ASC);

sounds like it does not make sense.

What does FULLTEXT indexing a column in ascending order mean?

You should run it like this:

ALTER TABLE ADD FULLTEXT INDEX index (column);

Give it a Try !!!


Temporary Table error when executing entirety of script

if object_id('tempdb..#customers') is not null DROP TABLE #customers
GO
if object_id('tempdb..#temporary_storage') is not null DROP TABLE #temporary_storage
GO

SELECT [customer_number]
  INTO #customers
  FROM [source_placeholder]

SELECT DISTINCT([customer_number]) AS 'customer_number'
  INTO #temporary_storage
  FROM #customers

    IF object_id('tempdb..#customers') is not null DROP TABLE #customers

GO --- End Batch here

SELECT *
 INTO #customers
 FROM #temporary_storage

You can't put them in one batch, you have to put a GO between the drop statements.


Review database schema / thought process

Recommendations are as follows:

For access control use the groups instead of individual users. Create a group called public to which all users belong therefore any graphs assigned to public will be visible. Look to Role Based Access Control (RBAC) for more on this
Each graph can be accessed by one or more groups, which allows you to have graphs that are visible to different cross sections of users (through groups). This means your access table will be graphid, groupid, edit, view with a unique key constraint on graphid and groupid.
Answering the question can a user edit this graph will require a join from User_Group to Access table
There is no special meaning for nulls, as none exist
Handling users with no account is easier since no authentication is provided
For the graphs, you need a graph table, and also a graphid added to the node and relation tables to simplify queries.


Why are partial PostgreSQL HASH indices not smaller than full indices?

I would argue that this is a bug in the hash index code.  When you create an index on an already-populated table, it tries to pre-size the index to hold all the data so that it doesn't have to keep splitting buckets as the index is created.  But the code for doing this does not take the NULL fraction of the column nor (apparently) the selectivity of the partial index clause into account, so it arrives at a too-large number for the pre-sizing.

If you were to create the index first, and then populated the table, you will find that the hash index is small, whether you made it partial or not.  If the table is going to grow substantially after the index is created, the extra space consumed by the index upon original creation will be put to good use.


T-SQL - AOAG Health state Healthy or Not Healthy in a Single row

You have two states - everything is healthy or something is not healthy. This applies per availability group. So if anything's health is 'NOT_HEALTHY' that's the group's status, otherwise the status is 'HEALTHY'.

There are various ways to find if anything is unhealthy. One is EXISTS. Another is aggregate functions, some of which also work on character types. MAX() & MIN() are the useful ones. In a dictionary sort 'NOT_HEALTHY' > 'HEALTHY' so MAX('HEALTHY', 'NOT_HEALTHY') will return 'NOT_HEALTHY'. There is only one MAX in any GROUP BY so this removes the need for TOP clauses.

The query will look like

select
  case max (synchronization_health_desc)
    when 'NOT_HEALTHY' then 'NOT_HEALTHY'
    else 'HEALTHY'
  end
from <those tables, joined>
group by
  group_name

Once the GROUP BY is introduced other returned columns must also be in aggregate functions or mentioned in the GROUP BY clause.


First of all, check the SQL errorlog to see if it actually hit a max size for the log. If it did, then the query has no hope of completing, it is probably already in a rollback state.

Even if it is, I always prefer to kill the spid manually (use sp_who2 or sp_WhoIsActive to find the spid, then do a kill 59 or whatever). You also can't check the rollback status unless you do an explicit KILL, see this related thread.

Since this is a delete, and not an update or insert, you may be very lucky and find that it rolls back immediately. If not, it may take as long (or longer) to roll back as it did to get to this point.

To see the rollback status, use

kill 59 with statusonly

Unfortunately, I have found this frequently does not show anything useful, just a "0% complete". In that case, you'll have to use sp_who2 and watch the IO and CPU to see if it is still doing something.

Regarding the reboot, this is a grave risk. If the spid is actively rolling back (CPU and IO are changing), then restarting SQL will only take the database offline entirely until the rollback is completely finished (hours and hours). But, if the CPU and IO are not moving, then it may in fact clear it right away. Either way, it is a risk.

One final option, if things are especially dire: If you have a backup from just before the delete started (and there haven't been other updates to the db), then the fastest way to recover may be to simply drop the DB, restart SQL, and restore from backup.

If you can't drop the DB (or if you already restarted the instance and the sql errorlog is predicting a 24-hour recovery time), then shut down SQL services, delete the MDF and LDF files from disk, start up SQL, drop the (ghost) database, and restore from backup.

Obviously you'd only attempt that if this were a back-end processing database that users didn't interact with.


Why tmp tables created when connected to one database

Look at the message

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Table info is being read from the INFORMATION_SCHEMA. Why ?

According to the MySQL Documentation on auto rehashing for the mysql client:

  --auto-rehash

  Enable automatic rehashing. This option is on by default, which
  enables database, table, and column name completion. Use
  --disable-auto-rehash to disable rehashing. That causes mysql to start faster, but you must issue the rehash command or its # shortcut if
  you want to use name completion.

  To complete a name, enter the first part and press Tab. If the name is
  unambiguous, mysql completes it. Otherwise, you can press Tab again to
  see the possible names that begin with what you have typed so far.
  Completion does not occur if there is no default database.

For your session, internal temp tables with info from INFORMATION_SCHEMA is needed for hitting the tab character in the event multiple columns or multiple tables have the same prefix characters.

CAVEAT

Please note that the INFORMATION_SCHEMA database is comprised of temp tables to begin with : See my post How is INFORMATION_SCHEMA implemented in MySQL?.

Therefore, it should be no surprise that more temp tables are created to hold table and column info for a database you just switched to. This is particularly true if you are not a super user like root@localhost and your grants only go as high as the database level.


Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking

This is more a business process / UI race. It is often solved by deprecating / hiding values for a period before deleting and/or not accepting them as valid anymore.
One approach would be to add a DateDeprecated column to toys. Update it to the current date when you check and nobody has this toy as a favorite. Clear the field if/when the toy is selected as a favorite. In the UI don't show/list deprecated toys at all or don't show toys deprecated for a certain period of time like over 7 days. At some point delete long deprecated toys, say after they have been deprecated for 14 days.


python ibm_db driver and decimal values?

I'm not sure if it's supposed to work or not, in

https://github.com/ibmdb/python-ibmdb/issues/129

it says:

"This is the current limitation of ibm_db driver due to lack of C API of
python's Decimal object, but you will get decimal object through ibm_db_dbi
wrapper."

I used the following workaround:

import ibm_db
import ibm_db_dbi
[...]
ibm_db_conn = ibm_db.connect("DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s; PWD=%s" % (dbname, host, port, user, passwd),"","")
conn = ibm_db_dbi.Connection(ibm_db_conn)
c1 = conn.cursor()
c1.execute("values (cast(1 as int),cast(0.1 as decimal(2,1)))")
row = c1.fetchone()
if row == None:
    sys.exit(1)

# Manually create a dictionary
rowDict = dict(zip([c[0] for c in c1.description], row))
print rowDict
{'1': 1, '2': Decimal('0.1')}


ORACLE: Changing the grouping of a data to be based on month breakdowns

I figured it out,
had to group by trucn(invdate,'mm') and count the separate columns

SELECT TO_CHAR(TRUNC(inv.invdate,'MM'),'MON-RR') "Month",
         COUNT(DISTINCT(inv.order_no)) "Orders",
         COUNT(inv.order_no) "Order Lines",
         COUNT(DISTINCT(inv.invoice_no)) "Invoices",
         COUNT(inv.invoice_no) "Invoice Lines",
         COUNT(DISTINCT(inv.name)) "Companies"
FROM test.test1 inv
GROUP BY TRUNC(inv.invdate,'MM')


How to select a row having SubjectCode X if not then select SubjectCode as Y and so on as mentioned in code

as fas as I understand you would like to control the order of execution if x is not present search for y and so on. Therefore a simple IN clause or the case expressions probably won't work if you've got entries for x as well as for y and z....by result returning multiple rows.

The best way I can think of would be to introduce a priority factor within a cte and use this to return only the relevant row.

Here is an example. For simplicity I use a table with the following structure

  CREATE TABLE test
  ( StudentID int,
    SubjectCode char(1)
   );

The table has the following entries:

As you described we'd like to return X first, then possibly Y (if no X present) and at last Z and only one row per student if the applicable subject code.

XYZ is easy because it is alphabetical but let's introduce some additional complexity guessing that there could be also an A which we would like to return if there is no X.

Here is a SQL Fiddle with my example

I would use the following query to achieve this:

    WITH
   cteSortLogic AS
  (
      SELECT *
      FROM
       (
            VALUES ('X', 1), ('A', 2), ('Y', 3), ('Z', 4)
        ) AS tab(code,prio)
  )
 SELECT StudentId, SubjectCode
 FROM
 (
 SELECT
   StudentId,
   SubjectCode,
   ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn
 FROM test t
 INNER JOIN cteSortLogic  sl ON t.SubjectCode = sl.code
 WHERE SubjectCode IN ('X', 'Y', 'Z')
 ) res
 WHERE res.rn = 1
;

The CTE gives me a simple table with the codes and their priority of output:

Then I join it to my table and add a rownumber based on the priority:
So here's the output of my subselect (or derived query) named "res":

I have still got all the rows but assigned a rownumber based on my priority. Then the last thing I have to do is to filter only the most relevant rows which are the one having row number 1 .

Presto...here's the result of the whole query:

So given your example query your code would look like this:

    WITH
   cteSortLogic AS
  (
      SELECT *
      FROM
       (
            VALUES ('X', 1), ('Y', 2), ('Z', 3)
        ) AS tab(code,prio)
  )
SELECT *
FROM
(
select     dbo.GetStudentNumber('M', mcl.StudentID) As Student_Number,
           SubjectCode,   ROW_NUMBER() OVER (PARTITION BY StudentId ORDER BY prio) AS rn, *
FROM       dbo.Question mcl
inner join Answer mclo on         mclo.AnswerID = mcl.AnswereID
inner join cteSortLogic sl on mclo.SubjectCode = sl.code
where      StudentID = 6588
AND         SubjectCode IN ('X', 'Y', 'Z')
)
WHERE
    rn = 1


How to combine `delete` and `insert` operations in one statement?

You can do it in one go, sort of:

SQL> MERGE INTO sale s
  2  USING (SELECT ROWID rid, sale_at, product_id
  3           FROM sale
  4          WHERE product_id IN (SELECT parent_id FROM split)
  5         UNION ALL
  6         SELECT CAST (NULL AS ROWID) rid, sale_at, child_id FROM sale
  7           JOIN split ON (parent_id = product_id)) m
  8  ON (s.ROWID = m.rid)
  9  WHEN MATCHED THEN
 10     UPDATE SET s.sale_at = m.sale_at
 11     DELETE WHERE 1 = 1
 12  WHEN NOT MATCHED THEN
 13     INSERT VALUES (m.sale_at, m.product_id);

Done

SQL> select * from sale;

SALE_AT                                  PRODUCT_ID
----------- ---------------------------------------
11/06/2013                                        2
11/06/2013                                        2
11/06/2013                                        3
11/06/2013                                        3
11/06/2013                                        3
11/06/2013                                        2
11/06/2013                                        2
11/06/2013                                        2

This will delete the rows from sale that are present in split and replace them with their appropriate split products.

You could also write it:

SQL> MERGE INTO sale s
  2  USING (SELECT CASE WHEN row_number() over (PARTITION BY s.rowid
  3                                             ORDER BY sp.rowid) = 1
  4                     THEN  s.rowid
  5                END rid,
  6                s.sale_at, sp.parent_id, sp.child_id
  7           FROM split sp
  8           JOIN sale s
  9             ON sp.parent_id = s.product_id) m
 10  ON (s.rowid = m.rid)
 11  WHEN MATCHED THEN UPDATE SET s.product_id = m.child_id
 12  WHEN NOT MATCHED THEN INSERT VALUES (m.sale_at, m.child_id);

Here the row in sale will be replaced (updated) by its first split component and the additional components will be inserted.


Sql Server Transaction logs during large deletes

We don't know why your transaction log backups will fail but we can discuss solutions. He could be concerned about transaction log bloat, when the transaction log would grow so large that it filled the drive where the log file is stored.

First, and easiest solution: If you're moving all the data out of the table entirely, simply truncate the table. This is minimally logged.

Second: Split your DELETE statement into batches. Find a way to divide your data, let's say in batch sizes of 5,000.

DELETE FROM OldDBTable
WHERE ID IN
          (SELECT TOP 5000 ID
           FROM OldDBTable
           WHERE [Statement to define deleted data here])

You could probably increase that batch size if you want. Then just set a
WAITFOR DELAY '0:05:00' to wait 5 minutes for your next batch.


Oracle limit the number of files of audit

Connections as sysdba or as sysoper, also startup and shutdown events are always audited, whether AUDIT_TRAIL is set or not.


Is that possible to backup MongoDB sharded cluster without LVM or mongodump?

The LVM method works they way it does because snapshots are really fast, and the outage is really small and easily within the replication-log window. Using file-copy methods extends how long the replica-member is not replicating, so how long you can take to back up is constrained by the maximum replication-log allowed on the master node.

Secondly, snapshotting takes a point-in-time backup of all files at the same time which improves the consistency of the backup. For a file-copy style backup the files will be read sequentially, so the use of db.fsyncLock() is massively important; if you don't, the chance you're backing up inconsistent files is really high. It's the use of db.fsyncLock() that starts causing the replica to fall behind and the replica-log to increase on master.


ORA-01113: file 1 needs media recovery -- system01.dbf

Without a valid database backup: forget it.
If you do have a valid backup you could use that to restore the database.
If your database is also running in archivelog mode and you have all archives created since that backup, you can also roll forward the transactions in them and doing so recover all data.

It all starts with a valid backup, or a standby database. No magic involved, just common sense.

You could take a look on jdul
 a tool that has 'reads datafiles when db is down or corrupted' in it's description. I never used it. Kurt has made more nice tools.


MySQL cache is not getting hit by subqueries

MySQL current 5.1 and 5.5 versions do not cache subqueries. Only whole queries. Subqueries are not processed as a separate item and the execution planned created is for the whole query.

MariaDB (a MySQL fork), version 5.3 has an optimization feature that does exactly that: Subquery cache.

If I am not wrong a similar feature will be incorporated in MySQL 5.6.


Formally you may try to use something like

WITH cte1 AS (SELECT name, email1 email FROM table
              UNION ALL
              SELECT name, email2 FROM table
              UNION ALL
              SELECT name, email3 FROM table
              UNION ALL
              SELECT name, email4 FROM table),
     cte2 AS (SELECT email
              FROM cte1
              GROUP BY email
              HAVING COUNT(DISTINCT name) = 1)
SELECT *
FROM table
LEFT JOIN cte2 ON cte2.email IN (table.email1, table.email2, table.email3, table.email4)
WHERE cte2.email IS NULL
UNION ALL
SELECT table.name, MAX(table.email1) email1, MAX(table.email2) email2,
                   MAX(table.email3) email3, MAX(table.email4) email4
FROM table
JOIN cte2 ON cte2.email IN (table.email1, table.email2, table.email3, table.email4)
GROUP BY table.name

But I don't know what you must do with the records like

Name   email1           email2            email3           email4
John   john@gmail.com   johnX@gmail.com   -                -
John   -                Xjohn@gmail.com   john@gmail.com   -


How big should I make a column in an audit table for storing the output of COLUMNS_UPDATED()?

Let me start out with a question: Why do you store the value of COLUMNS_UPDATED() if you don't understand how to read it? What possible value can you get out of storing it?

But to answer your question, COLUMNS_UPDATED() returns one bit per column that is currently in the table or was previously in the table. COLUMNS_UPDATED() is based on the column_id that you can retrieve from sys.columns. Every new column get an increasing value assigned in there, kind of like an IDENTITY value, however restarted for each table. So if you remove and re-add columns old values will not get reused; instead you will have gaps. The following code snippet demonstrates that:

CREATE TABLE dbo.tx(c1 INT, c2 INT, c3 INT);
ALTER TABLE dbo.tx DROP COLUMN c2;
ALTER TABLE dbo.tx ADD c4 INT;

SELECT name, column_id FROM sys.columns WHERE object_id = OBJECT_ID('dbo.tx');

This is the output of this query:

|name | column_id|
+-----+----------+
|c1   | 1        |
|c3   | 3        |
|c4   | 4        |

That has the big advantage that you do not have to change trigger code that was looking for a specific column. However, it has the huge disadvantage that that everything gets obscured. Also you might not be able to script out your tables and triggers and recreate them in a testing database because there the column_id values might be different.

So to figure out how many bits you need, just look at the MAX(column_id) value for your table.

An alternative is to use the UPDATE() function that takes a single column name and returns a value indicating if that column was changed or not. Both functions have the additional disadvantage that they do not actually compare the values; they just report that the column was referenced, even if the value in the column stayed unchanged.

In your case it looks like you are trying to capture the actual data changes. Have a look at Change Data Capture - no need to reinvent the wheel.


How to create columns with Count and percentage using MS Access Query

Something like this?

SELECT Person, Count(Person) AS [Count], 100*(Count(Person)/DCount("*","T1")) AS Percentage
FROM T1
GROUP BY Person;


I think you will find the answer in MySQL documentation:
http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html

Note in particular how the 'innodb_autoinc_lock_mode' option/variable affects this. In MySQL 5.1+ this variable is "1" as default - in 5.0 and before it was "0". The reason to set it to "1" is that it improves performance of replication in some scenarios. If you don't use replication it makes no difference and setting it to "0" should avoid the 'gaps' in the autoincrement counter in most cases.

But there is not - and never was - any guarantee (in MySQL or any RDBMS) that auto_increment values will always be sequential without gaps. The purpose of auto_increment values is - and ONLY is - to ensure a unique identifcation of each row. The value itself has no importance.


Note that the following assumes that you are a SQL Server administrator on both of the instances...

Generally, you can follow these steps:

On the source computer, note the location of your database's files
On the source computer, detach the database from the source computer's SQL Server instance
Move the database files to the proper directories on the target computer
On the target computer's SQL Server instance, attach the database
Repeat for each of the databases you wish to migrate


How make convincing case to DBA to use Oracle external tables for 500 MB to 5 GB files?

IMO, an external table is much easier to manage and flexible than a SQLLoader script.  So if you're already doing a recurring SQLLoader load, I see no possibilities of evil in switching to external tables.

By doing the external table, even though the base of the table resides in a text file outside the database, the data can be accessed from within the database, no need for a separate SQLLoader tool/scripts.  So you can write procedures/packages to manipulate the data straight from the external table.  No need to load it anywhere...as long as it's in the directory you have set, it's already "loaded".

As a DBA, I'd much prefer managing a DML script using an external table than having to manage a SQLLoader script.  But SQLLoader has been around a while....if your DBA has too, it may be a tough sell. :)


No data inserted with this WHERE NOT EXISTS expression?

Your condition compares the column value to itself:

WHERE NOT EXISTS(SELECT code FROM subject WHERE code = code)

Boils down to:

WHERE NOT EXISTS(SELECT FROM subject WHERE code IS NOT NULL)

IOW, it's always true as soon as there is a single row in the table with code IS NOT NULL. I don't think that's intended.

Guess you want something like:

'''INSERT INTO subject (created, modified, code, name, units)
   SELECT current_timestamp, current_timestamp, %s, %s, %s
   WHERE NOT EXISTS(SELECT code FROM subject WHERE code = %s)'''
, (row['scode'], row['sname'], row['sunits'], row['scode']))

And that's not fit for concurrent write load. I suggest to create the missing UNIQUE constraint on (code) and use INSERT .. ON CONFLICT ... DO NOTHING if at all possible. If the table is big, you'll want an index on code for performance, anyway.


In Postgres, how do I cast character varying to an enum type?

I think I've found the answer: I need to drop the default first, and then re-add it.

ALTER TABLE logs ALTER COLUMN interface_type DROP DEFAULT;
ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types
  USING interface_type::text::interface_types;
ALTER TABLE logs ALTER COLUMN interface_type SET DEFAULT 'button';


A beautiful trigger issue. Flawed logic? Updating Orderheader when orderlines are complete

Your trigger checks all orderlines for completeness even when only one is updated. You should use the :NEW table prefix to filter the currently updated record.
There is no need to include the table orderHeader while counting
There is no need for a group-by

Try the following:

CREATE OR REPLACE TRIGGER orderComplete
   AFTER UPDATE ON orderline
 FOR EACH ROW -- Added after comment by a1ex07
 DECLARE ordersNotDone NUMBER;
 BEGIN
    SELECT COUNT(Orderline_fulfilled)
      INTO ordersNotDone
      FROM orderline
     WHERE orderline_order = :NEW.orderline_order
       AND orderline_fulfilled = 'N';

    IF ordersNotDone = 0
    THEN
        UPDATE orderHeader
        SET order_completed = sysdate
        WHERE order_id = NEW:orderline_order;
    END IF;
END;


Updating MySQL innodb table based on multiple joins

I think this will do:

UPDATE
    ( SELECT COUNT(DISTINCT o_id) AS total_oids
      FROM  oak_relation
    ) AS d
  CROSS JOIN
    oak_relation AS u
  JOIN
    ( SELECT o_id, COUNT(*) AS matching_o_ids
      FROM  oak_relation
      GROUP BY o_id
    ) AS o
      ON o.o_id = u.o_id
  JOIN
    ( SELECT k_id, COUNT(*) AS matching_k_ids
      FROM oak_relation
      GROUP BY k_id
    ) AS k
      ON k.k_id = u.k_id
SET
    u.xy = u.initial * d.total_oids / o.matching_o_ids / k.matching_k_ids ;

It would be good to check it out first by running the equivalent SELECT query:

SELECT
    u.o_id, u.k_id, u.initial,
    d.total_oids,
    o.matching_o_ids,
    k.matching_k_ids,
    u.initial * d.total_oids / o.matching_o_ids / k.matching_k_ids AS new_xy
FROM
    ( SELECT COUNT(DISTINCT o_id) AS total_oids
      FROM  oak_relation
    ) AS d
  CROSS JOIN
    oak_relation AS u
  JOIN
    ( SELECT o_id, COUNT(*) AS matching_o_ids
      FROM  oak_relation
      GROUP BY o_id
    ) AS o
      ON o.o_id = u.o_id
  JOIN
    ( SELECT k_id, COUNT(*) AS matching_k_ids
      FROM oak_relation
      GROUP BY k_id
    ) AS k
      ON k.k_id = u.k_id ;


How arrange columns in sp_whoisactive by Adam Machanic

Edit the line with @output_column_list declared on it and put [database_name] between [dd%] and [session_id].

When I scripted it out it was on line 110.


Find out where length semantics gets changed

I forgot the most obvious thing: session parameters can be changed!

I had this on the latest revision:

ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';

... but not the older one. I was running the scripts in a different session thus I couldn't spot the value change.


Separate differential backup from one restored baseline

The question is a little ambiguous, but either of the two ways I see to interpret it would work:
Starting point: A differential backup captures all changes from the last full backup (the 'differential base').
Both scenarios below start by creating the baseline database and taking a full (not copy only) backup of it.
Scenario 1 - Continuing differentials

Restore the baseline
Restore the latest differential for customer X (if any)
Make changes for customer X
Perform a new differential backup for customer X

You need to keep the full baseline and the latest differential for each customer in this scenario.
Scenario 2 - Separate full backups

Restore the latest full backup for customer X (or the baseline if a new customer)
Make changes for customer X
Perform a new full backup for customer X

You need to keep the baseline (for new customers) and the latest full backup for each customer in this scenario.

In scenario 1, the size of the differentials will grow larger over time, but never larger than the size of the current database for each customer.
Restoring for each customer requires two steps: baseline, then customer-specific single latest differential. Previous differentials can be discarded if necessary.
Scenario 2 will require more storage space than scenario 1, as each customer gets a full copy of their current database.
Restoring for each customer is a simple one-step process. Previous per-customer full backups may be discarded.

As usr mentioned in a comment, one drawback to either set up is it makes it more difficult to apply a change to all customers.
As always, a known good current database validated by DBCC CHECKDB with the appropriate options is needed, and the only valid backup is one you have tested to restore successfully (and pass the checkdb tests). It may be a good idea to keep the historical backups, at least for a time, in a case a customer ever wants to revert.


Streaming replication and failover on PostgreSQL

In our shop we selected repmgr and pgbouncer instead of pgpool. repmgr has some nice tooling to setup and maintain the cluster of replicated database servers. In our case 1 master and 2 slaves (one failover and one live read performance test that can become the failover of the new master). pgpool has issues with changes in the configuration, in most cases you have to restart the service and therefor you have some downtime. This is a problem when you need 24x7x365 availability.

repmgrd (the deamon) helps to select the new master after a failover, you really don't want a split brain situation. We have one virtual ip-address for the master database, the database that is master at that moment. When another server becomes master, this is the only server using this address. Every database server also has it's own ip-address for read only queries.

repmgr is maintained by the same guys that created streaming replication in the first place, so they know what they talk about. Version 2.0 is about to be released.

Prepare for the worst situation, do some serious testing by pulling some power and network plugs! When something goes wrong, many other things already went wrong and will bite you in the back when you can't afford it.

Replication is one thing, a working failover after some serious problems, is another thing.


Database design for dynamic Meta fields of an entity that shall be queried intensively

To my knowledge there are these 2 ways you can go:

If you want performance, simpler queries, easier programming, then you should make a second table with your ID as foreign key and make there a column for each of your properties that you want to store. For any new attribute you would have to change the database schema, what might be a major drawback.
The above won't work, if you need flexibility in your database schema (because your meta properties are not known in advance or are point to change), but it will add to your code and queries complexity (depending on your framework/application) and also lead to poor performance compared to the first alternative. Poor performance can be compensated to a certain extent with more hardware, but that's expensive.

It's a trade off between the two. You must judge your application scenario and decide what you need most and what is a absolute no-go. Then decide on the remaining criteria what you want to use.

I have seen and used both ways I mentioned above, and must say the second one worked better for me (didn't have high volume querying though), even if it is a antipattern from a SQL / database normalization point of view. However, we aren't payed for nice SQL database designs, but for working applications.


Split duration hourly depending on start and end time

The basic idea here is to add rows for each row in the status table depending on how many hours it covers. I'm doing this by joining to a numbers table. From there, you just need to account for all of the different cases to figure out how many minutes for the status are relevant for each hour bucket.

Data prep:

CREATE TABLE #t (
Shift_date DATETIME NOT NULL,
[Status] VARCHAR(20) NOT NULL,
Start_timestamp DATETIME NOT NULL,
End_Timestamp DATETIME NOT NULL
);

INSERT INTO #t VALUES ('5/20/2017', 'Operating', '5/20/2017 8:21', '5/20/2017 10:40');
INSERT INTO #t VALUES ('5/20/2017', 'Delay', '5/20/2017 10:40', '5/20/2017 11:10');
INSERT INTO #t VALUES ('5/20/2017', 'Operating', '5/20/2017 11:10', '5/20/2017 13:50');

One approach which should work on 2008:

-- may need more rows here depending on how long the events can be
WITH numbers (n) AS (
    SELECT 0
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
)
SELECT
   t2.Shift_date
 , DATEPART(HOUR, start_final) AS [hour]
 , t2.[Status]
 , DATEDIFF(MINUTE, start_final, end_final) Duration
FROM
(
    SELECT Shift_date
    , [Status]
    , Start_timestamp
    , End_Timestamp
    , CASE WHEN t.Start_timestamp > n.start_from_numbers THEN t.Start_timestamp ELSE n.start_from_numbers END start_final
    , CASE WHEN t.End_timestamp > n.end_from_numbers THEN n.end_from_numbers ELSE t.End_timestamp END end_final
    FROM #t t
    CROSS APPLY
    (
        SELECT dateadd(hour, n.n + datediff(hour, 0, t.Start_timestamp), 0) start_from_numbers
        , dateadd(hour, 1 + n.n + datediff(hour, 0, t.Start_timestamp), 0) end_from_numbers
        FROM numbers n WHERE DATEDIFF(HOUR, t.Start_timestamp, t.End_Timestamp) >= n.n
    ) n
) t2
ORDER BY start_final;

My results (it looks like you have a few issues with your sample data):

╔═════════════════════════╦══════╦═══════════╦══════════╗
║       Shift_date        ║ hour ║  Status   ║ Duration ║
╠═════════════════════════╬══════╬═══════════╬══════════╣
║ 2017-05-20 00:00:00.000 ║    8 ║ Operating ║       39 ║
║ 2017-05-20 00:00:00.000 ║    9 ║ Operating ║       60 ║
║ 2017-05-20 00:00:00.000 ║   10 ║ Operating ║       40 ║
║ 2017-05-20 00:00:00.000 ║   10 ║ Delay     ║       20 ║
║ 2017-05-20 00:00:00.000 ║   11 ║ Delay     ║       10 ║
║ 2017-05-20 00:00:00.000 ║   11 ║ Operating ║       50 ║
║ 2017-05-20 00:00:00.000 ║   12 ║ Operating ║       60 ║
║ 2017-05-20 00:00:00.000 ║   13 ║ Operating ║       50 ║
╚═════════════════════════╩══════╩═══════════╩══════════╝


unable to start mongod - Fatal assertion 28579 UnsupportedFormat

mongod -vvvvvv -dbpath /var/lib/mongodb

and remove corrupted datafile.

rm f index-1234-123456789123456789.wt

and then repair using following command

mongod --repair --dbpath /var/lib/mongodb/ --storageEngine wiredTiger

Note: Have a backup of /var/lib/mongodb


How to setup authentication for connections on MongoDB?

Here is a complete solution, including creation of a "super user", capable to access any resources and perform any operation.

Notice

First: the first access to MongoDB is made without authentication ($ mongo).
Second: the configuration file of MongoDB may differ, depending on which version are you using (3.x: mongod.conf / 2.x: mongodb.conf)

Creating User

> use admin
switched to db admin
> db.createUser( {user:"mongo", pwd:"mongo", roles:["root"]} )
Successfully added user: { "user" : "mongo", "roles" : [ "root" ] }

Verifying User Creation and Authentication

> db.getUser("mongo")
{
    "_id" : "admin.mongo",
    "user" : "mongo",
    "db" : "admin",
    "roles" : [
        {
            "role" : "root",
            "db" : "admin"
        }
    ]
}

> db.auth("mongo","mongo")
1

Enabling Authentication

check which config file is using MongoDB

$ ps aux | grep mongo
mongodb  12583  0.5  3.4 2381948 281140 ?  Sl  18:34  0:06 /usr/bin/mongod --config /etc/mongod.conf

MongoDB 3.x

$ grep -A 1 security /etc/mongod.conf
security:
   authorization: enabled

MongoDB 2.x

$ grep -B 2 auth=True /etc/mongodb.conf
# Turn on/off security.  Off is currently the default
#noauth = true
auth=True

Restart MongoDB (SysV script)

$ /etc/init.d/mongodb restart

Connecting via Mongo Shell

$ mongo -u mongo -p --authenticationDatabase admin
MongoDB shell version v3.4.3
Enter password: **********
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.3
> show dbs
admin     0.078GB
android   0.078GB
blog      0.078GB
games     0.078GB
school    0.078GB

Notice: you can just hit mongo and open the Mongo Shell, but it will not let you perform any action, due to the fact that you were not previously authenticated.

$ mongo
MongoDB shell version v3.4.3
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.3
> show dbs
2017-10-03T19:04:55.953+0000 E QUERY    [thread1] Error: listDatabases failed:{
"ok" : 0,
"errmsg" : "not authorized on admin to execute command { listDatabases: 1.0 }",
"code" : 13,
"codeName" : "Unauthorized"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
Mongo.prototype.getDBs@src/mongo/shell/mongo.js:62:1
shellHelper.show@src/mongo/shell/utils.js:761:19
shellHelper@src/mongo/shell/utils.js:651:15
@(shellhelp2):1:1
>

For dropping a user:

> use admin
> db.dropUser("myuser")

References:

https://docs.mongodb.com/manual/tutorial/enable-authentication/
https://docs.mongodb.com/manual/reference/configuration-options/
https://docs.mongodb.com/manual/core/security-built-in-roles/


Error when using general and specific GRANT

You already found the answer to this problem. It all has to do with the order the grants appear.
Let's go back to your first grants display
+----------------------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX'                              |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%';                                                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%'                                          |
+----------------------------------------------------------------------------------------------------------------+

Here is how the grants are stored:

Information from GRANT USAGE is stored in mysql.user
Information from the other two lines are stored in mysql.db

To see this order, please run this query:
SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G

This will display those grants in whatever order it was entered.
When you deleted the grants and reinserted them using
REVOKE SELECT ON `schema_%`.* FROM user;
GRANT SELECT ON `schema_%`.* TO user;

This reverses the order in the mysql.db table. To prove that, just rerun
SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G

after doing the REVOKE and GRANT.
UPDATE 2017-08-02 13:20 EDT
In your comment, you said

I didn't knew of the mysql.db existence... But yes, it looks like it's the order; I suspected that already; but is this the expected behaviour? I would expect MySQL to "merge" the permissions so I didn't have to worry about order, since the grants are made by a SP automatically in here.

The problem here is MySQL will not merge them. For the sake of continuity, you should avoid using wildcards at the database level if your want to manage the grants of a single user. In that instance, you will have to manage the grants of  every single user the same way.


Perfmon Resource Utilisation for PROD SQL Server

Perfmon is pretty light weight when it comes to data collection. Collect the data in blg files and set a sensible collection frequency. Jonathan has written about Essential PerfMon counters for DBA that will give you a good start.

For automation part you can use logman and load data into sql server or as shown here

You can even Setup Perfmon with PowerShell and Logman.

Alternatively, you can use PAL or Get-PerfMonSummaryStats.ps1 - Powershell script from MSFT

sys.dm_os_performance_counters exposes perfmon counters in raw form.


Count records with same id but different column value

The total number of rows per UserID is easy, you just need to use COUNT(*). As for the other column, then, assuming Name cannot be null, you need to count distinct Name values and compare the result to 1. To explain: if all names are identical, COUNT(DISTINCT Name)) will return 1, otherwise it will return a different number. Thus, by comparing the result to 1 you will determine whether all names are unique or not.

This is how I would implement it in SQL:

SELECT
  UserID,
  COUNT(*)                   AS TotalRows,
  (COUNT(DISTINCT Name) = 1) AS AllNamesUnique
FROM
  YourTable
GROUP BY
  UserID
;

Feel free to play with this solution at dbfiddle.uk.


How to do Oracle database cluster / Failover without shared storage

In this case, you'll be wanting to use a standby database / data guard, rather than RAC (which uses shared storage).

Read Data Guard Concepts and Administration in the Oracle Documentation.

There's a step-by-step installation guide on Oracle Base that walks through setting up a physical standby database here.


relation "(table name)" does not exist in non-public schema

Turns out, when querying anything other than the public schema, you need to change the syntax of the query from:

SELECT *
FROM testboundaryNW

to

SELECT * FROM "NW"."testbonudaryNW"

if I renamed the table to testboundarynw (no caps on NW) I don't need the "" around it.


Why db2data folder changes its size (increasing and decreasing )?

Temporary tablespaces grow and shrink as required. They can be used for temporary tables, sorts, or certain types of joins. I still think you should start reading manuals.


Why am I getting PLS-00103 when I try to create a table in a PL/SQL procedure?

You can not write DDL in PL/SQL block directly as you have shown in your question.

However you can execute these DDLs using EXECUTE IMMEDIATE command as demonstrated below.

  CREATE OR REPLACE PROCEDURE ddl_test AS
  BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tbl1';
  END;
  /


Custom location for postgis extension .control file

This is not 100% proven, but the following pass of the documentation suggests you just cannot have your control files in other locations:

  extension_name

  The name of the extension to be installed. PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control.

I can imagine one can work it around on systems supporting symbolic links - but in your case this does not help as well, as you cannot create the symlink at the right location either.


Relational tables with same content, custom solution

I would keep them separate. Although the details differ, your circumstance smells a lot like the One True Lookup Table antipattern. By combining you will have problems defining foreign keys (which parent to point at?), amongst other things. Indexing strategy will essentially be about spliting the combined table into its constituent parts. And so on...

The justification for combining would be if Foo and Bar are sub-types of some more abstract entity type and all comments could hang off this super-type.


Comparing previous/next record to calculate an average speed

Getting the previous row is very easy in Postgres:

select id,
       speed,
       "time",
       extract(epoch from lag("time") over (order by "time") - "time") as diff_seconds,
from the_table
order by "time";

The whole calculation would be:

select extract(epoch from max("time") - min("time")) as total_seconds,
       sum(diff_distance) / extract(epoch from max("time") - min("time")) as avg_speed
from (
  select id,
         speed,
         "time",
         extract(epoch from lag("time") over (order by "time") - "time") as diff_seconds,
         speed * extract(epoch from lag("time") over (order by "time") - "time") as diff_distance
  from the_table
  order by "time"
) t;

Subtracting a timestamp from a timestamp yields an interval. Using extract(epoch ...) converts the interval to the total number of seconds in that interval.

The column diff_distance isn't really necessary, I just added that for debugging purposes.

This however is currently not possible in MySQL.


mysql table replication on just some records?

I suggest using mysqldump instead of replication.

mysqldump <other-params> --where="date >= '2015-01-01'" db table > dump.sql

That way you have dump file which can be reused. Import will take some time, but setup is much simpler - you can import it anywhere and do what you need.


row_number over partition at Version < Firebird 3

Hard to tell what you want (a precise question gets a precise answer, a vague question at best gets a lucky guess). In Firebird 2.5 an answer may be something like:

EXECUTE BLOCK RETURNS (ID INTEGER, ID2 INTEGER, FROM DATE, TO DATE,
                        COSTS DECIMAL(9,2), MySUM(DECIMAL(9,2))
AS
DECLARE VARIABLE FROMDATE DATE;
DECLARE VARIABLE TODATE DATE;
BEGIN
  FROMDATE = NULL;
  TODATE = NULL;
  MySUM = 0;
  FOR SELECT ID, ID2, FROM, TO, COSTS
      FROM MyTable
      INTO :ID, :ID2, :FROM, :TO, :COSTS DO
  BEGIN
    IF (FROMDATE <= FROM and TODATE >= TO) THEN
    BEGIN
      MySUM = MySUM + COSTS;
      SUSPEND;
    END
    ELSE
    BEGIN
      MySUM = COSTS;
      FROMDATE = FROM;
      TODATE = TO;
    END
    SUSPEND;
  END
END

By the way, I don't think the above would quite compile since 'FROM' and 'TO' are not names I would recommend as names of columns.

Still, HTH,
Set


update cc_info dst, ( select a_msisdn
                      from cc_info
                      where DATE(incoming_timestamp) ='2019-08-04'
                        AND status = 3
                      group by a_msisdn
                      having count(*) > 100 ) src
set dst.status = 4
where dst.a_msisdn = src.a_msisdn


Use the concatenation operator

myarray || 9

Details in the manual: https://www.postgresql.org/docs/current/9.1/functions-array.html


Why isn't there a way to label a column as "keyword"

If your goal is to de-normalize while mitigating the storage space requirements, Hybrid Column Compression in Oracle 11gR2 can help with this.  It is only available on Enterprise Edition with a few specific types of storage.

  Storing column data together, with the same data type and similar
  characteristics, dramatically increases the storage savings achieved
  from compression. The database compresses data manipulated by any SQL
  operation, although compression levels are higher for direct path
  loads. Database operations work transparently against compressed
  objects, so no application changes are required.


Your query is a tricky one for the optimizer, for two reasons.

The optimizer doesn't currently have logic to transform a disjunction (OR) across tables into a union (related Q & A).
The optimizer doesn't reason about deferring lookups (dealing with keys only until the last moment). This relates to the SELECT * component of the given query.

Together, these limitations mean there is very little indexing can do to assist the query as written (ORM generated), leaving aside the possibility of a (nonclustered) column store index.
If performance is inadequate, you may have to bite the bullet and write a custom query. In that case, modifying the existing index on ValueB to include AId would be useful, coupled with a rewrite like the following:
SELECT TA.*, TB.*
FROM
(
    -- Disjunction as union
    SELECT A.Id, b.Id
    FROM TableA a
    INNER JOIN TableB b
        ON b.AId = a.Id
    WHERE
        a.ValueA LIKE 'SearchTerm%'

    UNION

    SELECT A.Id, b.Id
    FROM TableB b
    INNER JOIN TableA a
        ON a.Id = b.AId
    WHERE
        b.ValueB LIKE 'SearchTerm%'
) AS M (A_Id, B_Id)
-- Lookups
JOIN dbo.TableA AS TA ON TA.Id = M.A_Id
JOIN dbo.TableB AS TB ON TB.Id = M.B_Id;

The target plan shape is:

How much better this is in practice depends on several factors, including how selective the LIKE predicates are (and that they are always prefix searches).

Guessed schema:
DROP TABLE IF EXISTS dbo.TableB, dbo.TableA;

CREATE TABLE dbo.TableA
(
    Id integer NOT NULL PRIMARY KEY,
    ValueA varchar(256) NULL INDEX iValueA,
    Padding char(4000) NOT NULL DEFAULT '', -- other columns

    /*INDEX i (ValueA) INCLUDE (Id)*/
);

CREATE TABLE dbo.TableB
(
    Id integer NOT NULL PRIMARY KEY,
    AId integer NOT NULL REFERENCES dbo.TableA (Id) INDEX iAId,
    ValueB varchar(256) NULL /*INDEX iValueB*/,
    Padding char(2000) NOT NULL DEFAULT '', -- other columns

    INDEX i (ValueB) INCLUDE (AId)
);

UPDATE STATISTICS dbo.TableA WITH ROWCOUNT = 3000000, PAGECOUNT = 3000000;
UPDATE STATISTICS dbo.TableB WITH ROWCOUNT = 30000, PAGECOUNT = 30000;


Simplest MySql query uses filesort and ignores created_at index?

With InnoDB, any secondary index (such as INDEX(created_id)) implicitly includes a copy of the PRIMARY KEY (id).  Hence, the first query is "covering" as indicated by "Using index".
When you need to look at more than about 20% of a table, it is actually faster to ignore the index, and do a full table scan -- rather than bouncing between the index and the data.  (The index and the data are separate BTree structures, connected by that PK copy.)
ORDER BY causes a filesort unless it can be consumed by the index it is using.  (But it is not using it, as the above point points out.)
MySQL rarely uses more than one index in a SELECT.  (Because using multiple indexes would be more complex, and almost always slower.)

That is just a few of the possible things that can go on (other than the items I just mentioned).

See also my Index Cookbook .


Unable to connect to Oracle from SQL Developer

This seems to be a hostname resolution problem. Please try to connect to the IP address instead of the hostname.

If this is not the solution could you please post the output of the following command:

lsnrctl status

EDIT 1:

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = IPC)(KEY = EXTPROC1521)
    )
    (ADDRESS =
      (PROTOCOL = TCP)(HOST = 192.168.2.64)(PORT = 1521)
    )
  )
)

EDIT 2:

It turned out to be an firewall problem. See comments below.


Postgres multidisk master replicating to single disk slave

Community Wiki answer generated from a question comment by a_horse_with_no_name

Create symbolic links on the slave that match the mount points of the tablespaces on the master.


Index strategy for TEXT / NTEXT column to filter out NULL / NOT NULL rows

If you're not doing any specific search predicates in your WHERE clauses, rather you're literally trying to SELECT rows WHERE TextColumn IS NULL in one query, and WHERE TextColumn IS NOT NULL in another query, then you should look into Filtered Indexes.

A filtered index is an optimized nonclustered index especially suited
to cover queries that select from a well-defined subset of data. It
uses a filter predicate to index a portion of rows in the table. A
well-designed filtered index can improve query performance as well as
reduce index maintenance and storage costs compared with full-table
indexes.

You can create a Filtered Index for each case I mentioned above, so that subset of rows is indexed separately from each other, which may help with your performance.


Perhaps this from the Oracle Database Reference will be useful:

    Note: For Enterprise Edition users, this parameter has been deprecated
    in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise
    Edition is not installed or it is installed, but you have not
    specified any LOG_ARCHIVE_DEST_n parameters, this parameter is valid.

  LOG_ARCHIVE_DEST is applicable only if you are running the database in
  ARCHIVELOG mode or are recovering a database from archived redo logs.
  LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n
  parameters, and must be defined as the null string ("") or (' ') when
  any LOG_ARCHIVE_DEST_n parameter has a value other than a null string.
  Use a text string to specify the default location and root of the disk
  file or tape device when archiving redo log files. (Archiving to tape
  is not supported on all operating systems.) The value cannot be a raw
  partition.

  If LOG_ARCHIVE_DEST is not explicitly defined and all the
  LOG_ARCHIVE_DEST_n parameters have null string values,
  LOG_ARCHIVE_DEST is set to an operating system-specific default value
  on instance startup.

  To override the destination that this parameter specifies, either
  specify a different destination for manual archiving or use the
  SQL*Plus statement ARCHIVE LOG START filespec for automatic archiving,
  where filespec is the new archive destination. To permanently change
  the destination, use the statement ALTER SYSTEM SET LOG_ARCHIVE_DEST =
  filespec, where filespec is the new archive destination.


LIMIT 12342,3 -- to get the row and the adjacent rows.  No need to worry about the PRIMARY KEY, etc. the query will probably be in the right order.


moving to SQL Enterprise 2014 from Standard 2012

It sounds like you are referring to Always On availability groups with a read only replica.  2012 EE as well as 2014 EE have this as an option and you can route reports to the replica without impacting the primary OLTP database.

Product Documentation


MySQL slave stuck at 'System lock' most of the time, high CPU usage

first we need to understand , there is no specific solution for this .
So as per provided information in Question, what good is :

You have mentioned that relay log position is keep on changing which means "SQL thread" is working

You have mentioned that Slave_SQL_Running_State is also getting change which means I/O thread is also working.
What bad is :"relay log space is increasing" which means data is coming but taking time to execute.

Lets dwell more here :

Are you observing slowness in Master as well ? Any slow Query in master ? If answer is No , proceed towards 2

Is configuration of both server same ? In this you must check mysql buffers pool configuration and isolation method. I have personally experience that Isolation level can also be culprit ( Repeatable read which is default in MySQL ) If same , proceed towards 3

Have you checked what query is executing at time when you observed slowness.
Just do , pager grep Query followed by show processlist;

This will give you an idea what is stucking.
Lets go more inside , check relay log position and check in relay log what is the query and try to optimize it. But before that be ensure if it is really performing slow .
You can use performance schema database and in this database , just enable instrument for SQL,IO,Replication and take help of this link : https://dev.mysql.com/doc/refman/5.7/en/performance-schema-replication-tables.html
Apart from this , you can also use SYS schema to diagnose whats going wrong.
What Rolando told is one of the cause and he is correct . Load file can generally cause this issue.
Let me know if i make sense :)


How to map another server through SQL Server Management Studio

This is more of a NET USE question than a SSMS/SQL Server question.

NET USE has the syntax:

net use [{DeviceName | *}]
   [\\\\ComputerName\ShareName[\volume]] [{Password | *}]]
   [/user:[DomainName\]UserName] [/user:[DottedDomainName\]UserName]
   [/user: [UserName@DottedDomainName] [/savecred] [/smartcard]
   [{/delete | /persistent:{yes | no}}]

So, I would expect your command to look like this:

EXEC xp_cmdshell
    'NET USE H: \\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes'


What is the difference between concurrency control in operating systems and in trasactional databases?

Databases have two concurrency requirements. One is the very short term physical management of memory blocks as they are referenced. These are known as latches in the DB world and can be implemented using mutexes and the like. The concern here is for the stability of a block of memory while a worker thread is accessing it.
The second controls the validity and isolation of the client data held within the DB. They control access to logical things (tuples, tables etc.) rather than physical hardware-related things (memory blocks).  These are refered to as locks. They last as long as the client transaction lasts, which can be an arbitrarily long time. The locks are typically held in a separate structure to the one which holds the object that is locked. Consequently the thing locked need not be in memory for the whole duration that the lock is held. For example, a page may be read in, a row inserted and a lock taken on it to ensure isolation. If the page containing the new row is evicted the lock would remain in effect. Indeed, the things locked may not exist when the lock is taken! A range lock protects key values from a lower bound to an upper bound. Neither of these values need be present in the data when the lock is acquired, nor must There be values that fall within the range. Nevertheless the lock will come into effect and allow isolation of clients' work.
The introductory sections of Goetz Graefe's "Survey of B-Tree Locking Techniques" covers these ideas nicely.
An OS deals with the first of these only (in my limited expertise). Any extension to disk is hidden behind virtual memory and the page file. In contrast a DBMS explicitly handles RAM and disk residence to optimise for its specific use-case. Different requirements lead to different treatments between OS and DBMS.


DROP USER takes too long when there are many users

The resolution to this issue was enabling service broker on the database as so.

ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

After enabling service broker for the database, drop users were practically instantaneous.

Kin asked if the service broker was enabled in a previous comment which sent me searching in the right direction.


Query across multiple schemas, unknown until execution time

If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between:

SET search_path = u111, public;
SELECT * FROM foo;
SET search_path = u222, public;
SELECT * FROM foo;
...

The schema search path search_path in Postgres works much like the search path a file system. Related:

How does the search_path influence identifier resolution and the “current schema”
PostgreSQL and default Schemas

If you need to combine results from multiple schemas (probably your use-case), you can either build the statement in your client or use a plpgsql function with dynamic SQL and EXECUTE. That's what I would do. Plain SQL does not allow parametrized identifiers (schema, table, column, ...).

CREATE OR REPLACE FUNCTION foo(_schemas text[])
  RETURNS TABLE (bar int, baz text) AS  -- matching return type
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT string_agg(format('SELECT bar, baz FROM %I.foo', sch)  -- your query here
                          , E'\nUNION ALL\n')
   FROM   unnest(_schemas) sch
   );
END
$func$  LANGUAGE plpgsql;

Builds and executes a query of the following form dynamically:

SELECT bar, baz FROM u111.foo
UNION ALL
SELECT bar, baz FROM u222.foo
UNION ALL
SELECT bar, baz FROM u333.foo;

Schema names are escaped as identifiers properly to defend against SQL injection.

db<>fiddle here (returning query string as error msg instead of executing it)
Old sqlfiddle


Delete and Insert performance SQL Server

First step: Make sure there's something to tune and that your query isn't just being blocked. You can do this with free tools like sp_WhoIsActive or sp_BlitzFirst (disclaimer, I'm a contributor to the First Responder Kit).

Second step: Don't use local variables.

Third step: Maybe indexing the temp table will help.

Fourth step: Check your wait stats using sp_BlitzFirst (same disclaimer about me contributing to it, blah blah blah). It could be that the query as written is fine, but you're running into some other issue, like tempdb contention.


Background images in SSRS report resizing on export to PowerPoint

While this is not an answer but a work around that works for me.

You need to have both a small image , and a larger version of the image imported into your report.  Then use the following code to display the large image when exporting to PowerPoint.

The following code should be used in the rectangle's BackgroundImage.Value property

=iif(Globals!RenderFormat.Name="PPTX","LargeImage","SmallPage")


If you are using InnoDB with the option innodb_file_per_table option off (by default on MySQL 5.1 and before), you need to stop the server after the dump (make sure you are not using innodb_fast_shutdown = 2), then delete at least the ibdata1, ib_logfile0, ib_logfile1 and your database directory and finally restart MySQL and import only the final truncated data. Otherwise, your ibdata1 file won't shrink in size.
If you have active the option innodb_file_per_table (by default on MySQL 5.5 and later), you have separate .ibd files per table, and your ibdata1 file is not too big, you do not need to import/export the data and delete the InnoDB files. Just delete the appropriate rows and defragment the .ibd files by doing:

ALTER TABLE <your table> ENGINE=InnoDB; -- for 5.5 and before

ALTER TABLE <your table> ENGINE=InnoDB, ALGORITHM=COPY; -- for 5.6 and later

Replication will probably break. On ROW based replication, it will certainly break as it won't find the right rows to affect. On STATEMENT mode some queries may continue working, affecting 0 rows, but as time passes, it is highly probable to find some incompatible query, like an UPDATE that can be done on the master but not on the slave due to a unique key constraint. I do not recommend running a replication with different data on master and slave unless you know for sure that those parts are not modified or they are done in a very restricted way. I can tell you that most of the replication problems I fix are due to replication filters.


Database solution for a large matrix/very wide table

"but this table would have 100M+ rows and be expensive to update, which I do daily."

In order to refute this, I did the following;

CREATE TABLE test_article (
    the_series integer,
    user_id integer,
    article_id integer,
    rating numeric
);

Put on the timing, so we have proper metrics.

\timing

Then, I inserted 10 million records into test_article:

INSERT INTO test_article
SELECT generate_series(1, 10000000), CAST(RANDOM() * 10 + 1 AS INTEGER), CAST(RANDOM() * 100 + 1 AS INTEGER), ROUND(CAST(RANDOM() AS NUMERIC), 2);

TIME:

INSERT 0 10000000
Time: 33520.809 ms (00:33.521)

Table content (sample):

test=# SELECT * FROM test_article;

 the_series | user_id | article_id | rating
------------+---------+------------+--------
          1 |       5 |         85 |   0.95
          2 |       6 |         41 |   0.14
          3 |       5 |         90 |   0.34
          4 |       3 |         18 |   0.32
          5 |       7 |          6 |   0.30
          6 |      10 |         32 |   0.31
          7 |       8 |         70 |   0.84

I realise that this is not a perfect benchmark. In order for it to be so, there would have to be a UNIQUE index on (user_id, article_id) - however in order to make it as realistic as possible, I'm going to put it on those fields. I believe that it's not a huge distortion. EDIT - see below - this problem has been resolved!

So, I created the index:

CREATE INDEX user_article_ix ON test_article (user_id, article_id);

TIME:

CREATE INDEX
Time: 20556.118 ms (00:20.556)

Then, I inserted 100K records:

INSERT INTO test_article
SELECT generate_series(1, 100000), CAST(RANDOM() * 10 + 1 AS INTEGER), CAST(RANDOM() * 100 + 1 AS INTEGER), ROUND(CAST(RANDOM() AS NUMERIC), 2);

TIME;

INSERT 0 100000
Time: 996.115 ms

Less than 1 second!

So, it would appear that there is no problem with inserting a large amount of records into your linking table (also called an Associative Entity - aka joining table, association table...)

So, I very much suggest that you should go with this as a solution!

Unique combination of user_id and article_id.

After much wailing and gnashing of teeth, I finally  figured out how to make the combination of user_id and article_id be unique (because any given user can only have one current rating of an article) using generate_series.

I won't show every step, just the ones which helped with uniqueness - based on what's above:

The "secret sauce" was this bit:

INSERT INTO test_article (user_id, article_id)
SELECT * FROM
(
  WITH x AS
  (
    SELECT generate_series(1, 500) AS bill
  ),
  y AS
  (
    SELECT generate_series(1, 20000) AS fred
  )
  SELECT * FROM x
  CROSS JOIN y
) AS z
ORDER BY bill, fred;

It involves CROSS JOINing a table of 500 (i.e. users) with a table of 20,000 (i.e. articles) - the astute among you will realise that the product of these is 10,000,000 (seen above).

Now, the combination of user_id and article_id are guaranteed to be unique, because with (sample), bill = 2 and fred = 3, you get

bill | fred
------+------
    1 |    1
    1 |    2
    1 |    3
    2 |    1
    2 |    2
    2 |    3

Every record is unique - et voilà!

In any case, I used this construct to test for dupes:

SELECT (user_id, article_id)::text, count(*)
FROM test_article
WHERE 1 = (SELECT 1)
GROUP BY user_id, article_id
HAVING count(*) > 1

TIME: 4s.

You can then make (user_id, article_id) the PRIMARY KEY (not shown - only took around 30s).

Then, to add 100,000 records, you leave the users alone (still 1 - 500), but you modify the generate_series() for the articles to 20,001 to 20200 (i.e. 200 x 50 = 100,000) and do the same INSERT as above. Blisteringly fast - even with the PRIMARY KEY (< 1s).

To obtain all the articles of a particular user is v. fast (~ 25 ms)

test=# EXPLAIN(ANALYZE, BUFFERS) SELECT * FROM test_article WHERE user_id = 77;
                                                                  QUERY PLAN
 Index Scan using test_article_pkey on test_article  (cost=0.44..65174.74 rows=44503 width=44) (actual time=0.074..21.837 rows=20200 lo
ops=1)
   Index Cond: (user_id = 77)
   Buffers: shared hit=40371 read=361 dirtied=271
 Planning Time: 0.131 ms
 Execution Time: 23.475 ms
(5 rows)

Time: 24.187 ms

And the pièce de résistance, a point search on the PK (< 1 ms):

test=# EXPLAIN(ANALYZE, BUFFERS) SELECT * FROM test_article WHERE user_id = 77 AND article_id = 4567;
                                                            QUERY PLAN

 Index Scan using test_article_pkey on test_article  (cost=0.44..10.22 rows=2 width=44) (actual time=0.038..0.040 rows=1 loops=1)
   Index Cond: ((user_id = 77) AND (article_id = 4567))
   Buffers: shared hit=4
 Planning Time: 0.219 ms
 Execution Time: 0.078 ms
(5 rows)

Time: 0.947 ms


As far as it's a CHAR/VARCHAR/NVARCHAR field you should use single quotes:

Maybe there is some extra or leading space.

Try it on this way:

SELECT *  FROM table1 WHERE fieldid =  '900399530';
SELECT *  FROM table2 WHERE idfield =  '900399530';

or remove additional spaces by using TRIM() function.

SELECT * FROM table1 JOIN table2  ON TRIM(table1.fieldid) = TRIM(table2.idfield);

For versions below 2017:

SELECT *
FROM   table1
JOIN   table2
ON     LTRIM(RTRIM(table1.fieldid)) = LTRIM(RTRIM(table2.idfield));


Reinitialize Table Values in SQL SSDT Unit Testing

I have created the usp_Generate_Merge_For_Table procedure a few years ago exactly for this purpose.
It can generate a MERGE command with a Values Constructor in order to "re-initialize" the contents of a specific table.

You can find it here:

https://gist.github.com/EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb#file-generate_merge_for_table-sql

Direct link to the raw script:

https://gist.githubusercontent.com/EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb/raw/608177e77da0a42d686098ab10703bbba8c54d64/Generate_Merge_For_Table.sql

Simply create the procedure in your database, run it while sending it your table name as a parameter, like so:

EXEC usp_Generate_Merge_For_Table 'cities', 'dbo'

And then you can copy and paste its output in your Unit Testing project.

You can find more info and detailed instructions on how to use it here:

https://eitanblumin.com/2018/10/28/generate-merge-statement-as-a-snapshot-of-your-tables-content/


I think you want to go with your first option.  Here's why:

You should design your database with a primary goal of maintaining data integrity.  Subtle data corruptions can be harder to debug than source code logic errors.  This is because corrupted data may only cause a detectable problem when a series of conditions occur at runtime.  Often you can't repeat these reliably.

The best way to focus on data integrity is to create a properly structured database.  If your data is going to be edited at any time, then you're best to start with a design that's in Third Normal Form (3NF) at least.  In general, you should have a table for each distinct type of thing that you have data about.

Sometimes, you may have two or more different types of things with similar attributes (data items).  This puts you in the position of deciding where you make your trade off.  Combining the facts about your things into one table means fewer tables.  Fewer tables will mean less code as long as the different types of things are and continue to be similar.  If you need to start moving in the direction of each type of thing having its own distinct set of attributes, then your code will become more complex and hard to maintain, with lots of branching logic.

It's important to remember that two different tables having the same set of attributes might be coincidental to your system or it might be consequential to your system.  Database structure design is not optimized in the same way as code reuse.  Cramming logically distinct but structurally similar data into the same table is not a good practice.  If you have two or more semantically distinct tables with very similar or even identical structure and your programmer's spidey-senses are tingling too much, then satisfy your urge for code reuse by subclassing in your code, but leave your apples and oranges in their own tables, rather than cramming them into a "fruit" table.

In a case where your two (or more) types of things are different from one another, then having one table for each type of thing is probably an excellent place to start.  This will keep your logic simple and your data clean, which makes for a high quality system.


Separate Schemas by Industry or by Application functionality?

I understand that you want to go with single database (as it is good from management & maintenance point of view), but maybe it's too much integration.

I am assuming that:

you will have separate application for each industry,
they have not very much in common (they really cover different business aspects)

I think that one of acceptable solutions in this case would be:

a separate database for each industry
in every database, single shared public schema for shared data, including common dictionaries, all non-customer-dependent data.
one separate database user account + private schema for each customer. You might use postgres feature of table inheritance to conform to some public table.

Issues that Simon puts in his comment are really important. So you will have to enforce a very strict policy regarding GRANTs in your database.

In PostgreSQL, it is possible to achieve (as well in many other RDBMS). The key to achieving multi-tenant solution would be intelligent usage of schemas, roles, search_path setting. See http://www.postgresql.org/docs/current/static/ddl-schemas.html. Actually what I propose will in a way emulate what Oracle does. If you need help on details please ask.


'packet too large' error with packet smaller than max_allowed_packet

The problem was not in fact MySQL. We use the DRb Ruby library, which gives a very similar error under similar circumstances: too large packet 26814732. Due to familiarity with the MySQL issue referenced here, I didn't read the error message well enough and didn't notice it is slightly different from Packet too Large.


MySQL read-write Master with many read-only Slaves over shared storage?

NO.  Do not attempt to have a single copy of the data with multiple copies of mysqld accessing it.  I don't know if that ever worked; if it did, I suspect it has atrophied over the years as changes have been made.

Think about it...  Each copy of the server has lots of stuff cached in RAM.  To coordinate that with other servers would require writing things to disk; this defeats the performance goal.  When a write occurs; all traces of it would need to be immediately flushed to disk, and all the other servers would need to know to read it and replace whatever is cached in their RAMs.

With FusionIO, check to see that you can safely turn off innodb_doublewrite.

How many queries/second?  Do you have any 'slow' queries?  (That is, there are other ways to beef up performance, than just hardware.)


Given tables of the following general form:

CREATE TABLE Device
(
    ID integer PRIMARY KEY
);

CREATE TABLE EventType
(
    ID integer PRIMARY KEY,
    Name nvarchar(50) NOT NULL
);

CREATE TABLE [Event]
(
    ID integer PRIMARY KEY,
    [TimeStamp] datetime NOT NULL,
    EventTypeID integer NOT NULL REFERENCES EventType,
    DeviceID integer NOT NULL REFERENCES Device
);

The following index is useful:

CREATE INDEX f1
ON [Event] ([TimeStamp], EventTypeID)
INCLUDE (DeviceID)
WHERE EventTypeID IN (2, 5, 7, 8, 9, 14);

For the query:

SELECT
  [Event].ID,
  [Event].[TimeStamp],
  EventType.Name,
  Device.ID
FROM
  [Event]
INNER JOIN EventType ON EventType.ID = [Event].EventTypeID
INNER JOIN Device ON Device.ID = [Event].DeviceID
WHERE
  [Event].[TimeStamp] BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.EventTypeID IN (2, 5, 7, 8, 9, 14);

The filter meets the AND clause requirement, the first key of the index allows a seek on [TimeStamp] for the filtered EventTypeIDs and including the DeviceID column makes the index covering (because DeviceID is required for the join to the Device table).

The second key of the index - EventTypeID is not strictly required (it could also be an INCLUDEd column); I have included it in the key for the reasons stated here. In general, I advise people to at least INCLUDE columns from a filtered index WHERE clause.

Based on the updated query and execution plan in the question, I agree that the more general index suggested by SSMS is likely the better choice here, unless the list of filtered EventTypeIDs is static as Aaron also mentions in his answer:

CREATE TABLE Device
(
    ID integer PRIMARY KEY,
    Name nvarchar(50) NOT NULL UNIQUE
);

CREATE TABLE EventType
(
    ID integer PRIMARY KEY,
    Name nvarchar(20) NOT NULL UNIQUE,
    [Description] nvarchar(100) NOT NULL
);

CREATE TABLE [Event]
(
    ID integer PRIMARY KEY,
    PLCTimeStamp datetime NOT NULL,
    EventTypeID integer NOT NULL REFERENCES EventType,
    DeviceID integer NOT NULL REFERENCES Device,
    IATA varchar(50) NOT NULL,
    Data1 integer NULL,
    Data2 integer NULL,
);

Suggested index (declare it unique if that is appropriate):

CREATE UNIQUE INDEX uq1
ON [Event]
    (EventTypeID, PLCTimeStamp)
INCLUDE
    (DeviceID, IATA, Data1, Data2, ID);

Cardinality information from the execution plan (undocumented syntax, do not use in production systems):

UPDATE STATISTICS dbo.Event WITH ROWCOUNT = 4042700, PAGECOUNT = 400000;
UPDATE STATISTICS dbo.EventType WITH ROWCOUNT = 22, PAGECOUNT = 1;
UPDATE STATISTICS dbo.Device WITH ROWCOUNT = 2806, PAGECOUNT = 28;

Updated query (repeating the IN list for the EventType table helps the optimizer in this specific case):

SELECT
  Event.ID,
  Event.IATA,
  Device.Name,
  EventType.Description,
  Event.Data1,
  Event.Data2,
  Event.PLCTimeStamp,
  Event.EventTypeID
FROM
  Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
  Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND EventType.ID IN (3, 30, 40, 41, 42, 46, 49, 50)
  AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
  AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;

Estimated execution plan:

The plan you get will likely be different because I am using guessed statistics. The general point is to give the optimizer as much information as you can, and provide an efficient access method (index) on the 4-million row [Event] table.


This should largely be a Google question and might get marked, but I'll add an answer just in case it helps others.  Please see a blog post by the Brent Ozar crew:
http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

First off: ‘Reorganize’ and ‘Rebuild’ are two different operations that each reduce fragmentation in an index. They work differently toward the same end. You don’t need to run both against the same index. (I sometimes find that people are doing both against every index in a maintenance plan. That’s just double the work and NOT double the fun.)

Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).

Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Do you need to reorganize your indexes every day?  Most likely not and you're going to increase disk pressure with higher wear and tear.  A solution like Ola Hallagren's scripts help out often but make sure you don't reindex during business hours and take database size into account.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


monitoring mongodb and mysql replications using nagios

For MongoDB, this would be a good place to start:

https://github.com/mzupan/nagios-plugin-mongodb

It's python rather than PHP, but it's basically what you are looking for to get the data into Nagios.  Then for what to monitor and alert on, I've previously written up a quick summary of this plugin previously on the mongodb-user group:

https://groups.google.com/forum/#!topic/mongodb-user/bix36VvywXE/discussion


Slow proc Used to take a few seconds now takes well over a minute

Easy answer for this. Updating statistics takes care of this slowness.


postgresql different configuration for different tablespace

See the docs for ALTER TABLESPACE.

Since version 9.6, you can set "effective_io_concurrency" on a per-tablespace basis. You can do so for "random_page_cost" long before 9.6.

These changes have to be made inside the database itself. For discoverability purposes, I would also add a comment inside postgresql.conf near the global settings, saying that they have been tweaked per tablespace.  Otherwise, it is not very obvious that these changes have been made.


Unpredicatable single insert performance on SQL Server table

I suspect this isn't a case of "the insert took 7 minutes" but rather "the insert was blocked for over 6 minutes." When an insert normally runs fine and then suddenly takes a long time, it is likely there is another cause, and highly unlikely that SQL Server suddenly got overwhelmed by your large table with too many indexes.

I treat this in more detail here but, essentially, you need to examine the system while this query is running long. Identify the session_id (SPID) and check sys.dm_exec_requests for blocking_session_id, which I think is most likely the issue; after that, check wait_type and last_wait_type. Most of the time, for duration outliers like this, one of those columns is going to help you track down the reason.

Sometimes, they might not have an active query in sys.dm_exec_requests - as I explain in the comments below, this is possible if someone started a transaction, made some statements against a table, then walked away without committing. You can see what object(s) that session has locked even if they aren't actively running a query.

DECLARE @SPID INT = <blocking_session_id from your "hung" query>;

-- see the command they ran last (might be related, might not)
DBCC INPUTBUFFER(@SPID);

-- see what that session has locked
SELECT s.name, o.name
  FROM sys.dm_tran_session_transactions AS tst
  INNER JOIN sys.dm_tran_locks AS tl
  ON tst.transaction_id = tl.request_owner_id
  INNER JOIN sys.objects AS o
  ON tl.resource_associated_entity_id = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE tst.session_id = @SPID;


You need to use multiple self joins to get that information:

select c.personelid,
       a.username as accounting,
       f.username as finance,
       h.username as hr
from confirmations c
  left join users a on c.accounting = a.userid
  left join users f on c.finance = f.userid
  left join users h on c.hr = f.userid;


Can't "Script Database As -> CREATE TO" with SSMS 2008R2 and 2016

I opened a connect feedback here: https://connect.microsoft.com/SQLServer/feedback/details/2869241
as follow:

"A SQL Login, mapped to a database user member of the db_owner role cannot script out the database with "Script Database As" or "Generate Script" task when using SSMS 2008, 2008R2, 2016.
It gets the following error message "Could not read metadata possibly due to insufficient access rights.
Granting the login "View any definition" solve it but this cannot be a workaround for obvious security reason.

(Tested with SQL2008, SQL2014 and SQL2016 instances installed on VMware virtual machines.)"


It's unlikely it's related to the query plans.  I've only every seen very complicated queries take more than 1 second for the plan to get created, much less 4 seconds as your describe.

It sounds like it's page caching - basically SQL Server keeps recently used data in memory.  The first run with each set of parameters you are paying the (normally expensive) cost of physical disk access.

Subsequent executions can access the data pages which are already stored in memory without waiting for the disk subsystem.


Reason for using hexadecimal in NCHAR()?

Yes, there is the obvious readability reason. For programmers, decimal numbers are meaningless. What the heck is 3221225477? But 0xc0000005 you recognize it immediately. 2147500037? Rings no bell, but 0x80004005 uberwellknown. These are value that carry meaning, you recognize them at a glance after seeing them every day in the debugger... It becomes second nature.

And no, I'm not kidding nor being sarcastic. I'm very serious. I would had also use 0x0D and 0x0A. If I see 13 I would have to think what value that is.


An easy way to select one db dump from multiple db MySQL dump

Take the difference between two 'CREATE DATABASE' using sed operation

eg

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

https://stackoverflow.com/questions/2342356/import-single-database-from-all-databases-dump

https://stackoverflow.com/questions/3596444/how-do-i-restore-one-database-from-a-mysqldump-containing-multiple-databases

Restoring a single database from mysql enterprise full backup


Operator "~<~" uses varchar_pattern_ops index while normal ORDER BY clause doesn't?

This revolves around COLLATION rules (which are part of your locale). Per documentation:

  The C and POSIX collations both specify "traditional C" behavior,
  in which only the ASCII letters "A" through "Z" are treated as
  letters, and sorting is done strictly by character code byte values.

Bold emphasis mine. These internal operators:

~<~, ~<=~, ~>=~, ~>~

are members of the operator classes text_pattern_ops, varchar_pattern_ops and bpchar_pattern_ops which ignore current collation rules and default to "traditional C" behavior. Per documentation:

  The difference from the default operator classes is that the values
  are compared strictly character by character rather than according to
  the locale-specific collation rules.

That's why they can use your first index, while their locale-aware equivalents

<, <=, >=, >

would need the second (using the default operator class).

ORDER BY defaults to the current collation rules. Unless you happen to work with the "C" collation the second set of operators is used for sorting, which can only use indexes with a matching collation (default btree indexes).

Notably, pattern-matching generally relies on "traditional C" behavior, that's why a left anchored LIKE match can use a _patern_ops index with a non-C locale, but not a default index. Compare (last chapter):

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

In Postgres 9.1+ you can force a selected sort order with COLLATE "locale_name" (the locale has to be available in your system!). So if you run your query with COLLATE "C" (which is a much better option than the evil hack using ~<~) , it can use the _pattern_ops index:

SELECT * FROM individual
ORDER  BY upper(surname) COLLATE "C"
        , upper("name") COLLATE "C"
        , upper(patronymic) COLLATE "C"
        , birth_dt
LIMIT  100;

Results are different. Try with non-ASCII letters like ö,  é, etc. Those typically sort depending on your current collation rules, while letters a-z typically sort the same in all locales.