why update a table take so long on oracle

Looks like my short answer was not to everyone's taste, so let me show you the long answer

First of all, let's create our lab.

@WestFarmer mentioned a couple of tables

SQL>create table A (
  2  some_column    number,
  3  another_column number
  4  );

Table created.

SQL>create table B (
  2  id number
  3  );

Table created.

Also a mentoned that he create an index over table A (another_column) and that B(id) was the primary key.

SQL>create index idx1 on A (another_column);

Index created.

SQL>alter table B add constraint b_pk primary key (id);

Table altered.

Now let's populate both tables, 3800+ for A and 300+ for B. I fix it to 4000 and 400.

SQL>declare
  2    i number;
  3    j number;
  4  begin
  5    for i in 1..400 loop
  6      for j in 1..10 loop
  7        insert into A values (j, i+100);
  8      end loop;
  9      insert into B values (i);
  10   end loop;
  11 end;
  12 /

PL/SQL procedure successfully completed.

Also I am going to add this extra record, just to show you why I suggest NOT EXIST instead NOT IN

SQL>insert into A values (1,NULL);

1 row created.

SQL>commit;

Commit complete.

As you will see below, ANOTHER_COLUMN include values from 101 to 500 for table A

SQL>select max(another_column), min(another_column) from A

MAX(ANOTHER_COLUMN) MIN(ANOTHER_COLUMN)
------------------- -------------------
                500                 101

And id store values from 1 to 400

SQL>select max(id), min(id) from B;

   MAX(ID)    MIN(ID)
---------- ----------
       400          1

Now the logic said that NOT IN should be the same as NOT EXIST, but if we count the record using not in you will see that it does not include NULL values:

SQL>select count(*) from A where another_column not in (select id from B);

  COUNT(*)
----------
      1000

However if we use not exists it include the null record I inserted.

SQL>select count(*) from A where not exists (select null from B where another_column = id);

  COUNT(*)
----------
      1001

Now the question does not mention nothing about NULL values but performance. So, let's remove the null record

SQL>delete A where another_column is null;

1 row deleted.

SQL>commit;

Commit complete.

SQL>select count(*) from A where another_column not in (select id from B);

  COUNT(*)
----------
      1000

SQL>select count(*) from A where not exists (select null from B where another_column = id);

  COUNT(*)
----------
      1000

Now both options looks similar, right? No matter if we use NOT IN or NOT EXIST.

I will show you why is better option NOT EXISTS from the performance perspective

I have activated autotrace option in order to see the execution plan.

First, let's run NOT IN option

SQL>update A set some_column = '2' where another_column not in (select id from B);

1000 rows updated.

Elapsed: 00:00:00.39

Execution Plan
----------------------------------------------------------
Plan hash value: 3794573283

------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | UPDATE STATEMENT          |      |  4000 |   152K|     4 |
|   1 |  UPDATE                   | A    |       |       |       |
|*  2 |   HASH JOIN RIGHT ANTI SNA|      |  4000 |   152K|     4 |
|   3 |    INDEX FULL SCAN        | B_PK |   400 |  5200 |     1 |
|   4 |    TABLE ACCESS FULL      | A    |  4000 |   101K|     2 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ANOTHER_COLUMN"="ID")

Note
-----
   - cpu costing is off (consider enabling it)
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          7  recursive calls
       1022  db block gets
         44  consistent gets
          0  physical reads
     248832  redo size
        397  bytes sent via SQL*Net to client
        584  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Now, let's run NOT EXIST option

SQL>update A set some_column = '2' where not exists (select null from B where another_column = id);

1000 rows updated.

Elapsed: 00:00:00.33

Execution Plan
----------------------------------------------------------
Plan hash value: 2312933728

------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |  4000 |   152K|     3 |
|   1 |  UPDATE             | A    |       |       |       |
|   2 |   NESTED LOOPS ANTI |      |  4000 |   152K|     3 |
|   3 |    TABLE ACCESS FULL| A    |  4000 |   101K|     2 |
|*  4 |    INDEX UNIQUE SCAN| B_PK |     1 |    13 |     1 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ANOTHER_COLUMN"="ID")

Note
-----
   - cpu costing is off (consider enabling it)
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          7  recursive calls
         15  db block gets
         47  consistent gets
          0  physical reads
      94068  redo size
        394  bytes sent via SQL*Net to client
        601  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Looks pretty similar if we check the Elapsed time, however if you check the execution plan you will see one little difference:

NOT IN use HASH JOIN meanwhile NOT EXISTS use NESTED LOOPS which in this case is a better way to run this query, in fact the cost is different 4 vs 3. probably in this case does not look to much, but imagine millions of records. Big difference!

NOTE: The index over A (another_column) is not usefull no matter what option you use.


select item,
       sum(duration),
       moved_to
from (
  select item,
         lead(move_date)  over (partition by item order by move_date) - move_date as duration,
         moved_to
  from movement
) t
group by item, moved_to
order by item, moved_to;

SQLFiddle example: http://www.sqlfiddle.com/#!4/dff2c/1


Just update one column, trigger updates another

book_id is int

Then your code should say:

WHERE book_id = 15

And not:

WHERE book_id='15'

This shouldn't affect performance in this case, but it is a best practice to delimit (or not) correctly. Non-Unicode strings and dates with single quotes, Unicode strings prefixed with N, binary and numerics without any quotes.

  I am afraid that, since the trigger is on UPDATE, and it actually updates the column, it may trigger multiple times. Is it possible?

No, SQL Server is smart enough to not throw itself off a cliff. It recognizes that the update inside the trigger shouldn't recursively call the trigger in an infinite loop. You can prove this by running a server-side trace or an extended events session and you will see that the update inside the trigger only runs once.

You can add code to prevent an update of that column from triggering a second update (for example if someone had manually updated the last_update column, but that doesn't seem relevant here).

  Do you see any optimization possible?

Generally, if you find that triggers are slowing down your code (I haven't seen any proof of that in this case), look to get rid of the triggers. I'm not sure what application code is updating this table directly, but if you can use a stored procedure to process all updates to the table, then you can add the update to this column there, instead of having to rely on the trigger. But I suspect if you are waiting 5 seconds for this update to happen, the trigger is NOT the problem.


Oracle: Truncate large table takes forever

I assume you are using dictionary managed tablespaces. You have to wait until all extents are deallocated. The following statement should report the number of extents that are to deallocate:

select extents
  from dba_segments
  where owner='TABLEOWNER'
  and segment_name='TABLENAME'

Index segments and materialized view logs have to be deallocated too, if they exist.

If you drop and recreate the table you will have to wait, too. Dropping the table will take as long as truncation of the table because the number of extents to release is almost the same.

Maybe the following will help:
instead of drop and recreate you

1)  rename the table TABLENAME to OLDTABLE
  2)  create a new table with the original name TABLENAME. Now you have an empty table TABLENAME
  3)  you drop the table OLDTABLE

The advantage of this approach is you can continue your work with table TABLENAME after step 2. But the space occupied by the former table TABLENAME will be release during the execution of step 3.
In Linux you can start the job that does step 3 in the background with nohup so that you must not leave your terminal window open. Use
nohup sqlplus user/password@tnsname @dropscript.sql &.

Even if you want to restart the database during execution of step 3 this is not a problem because Oracle can handle such situations.
As far as I remember it does this in the following way:
After the restart of the instance (or if the sessions  that does the drop of the table dies for some other reasons) the segment of the table is converted to a temporary segments. Its extents are deallocated by a background process (I think it is smon). The table is removed from the dictionary after the death of session so the tablename OLDTABLE is not visible in the dictionary anymore.


APPROACH #1

The easiest thing to do would be to revoke ALTER, DROP, and CREATE from all users except root

UPDATE mysql.user SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user <> 'root';
UPDATE mysql.db   SET alter_priv='N',drop_priv='N',create_priv='N' WHERE user <> 'root';
FLUSH PRIVILEGES;

APPROACH #2

You said that you do not want to issue

SET sql_log_bin = 0;

on the client side. A very adventurous approach would be to use a Stored Procedure to do your ALTER TABLE commands. You could then apply SET sql_log_bin = 0; in the Stored Procedure. In effect, that happens on the server side.

DELIMITER $$

DROP PROCEDURE IF EXISTS `Local_Alter_Table` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Local_Alter_Table`
(
    SQLStatement VARCHAR(2048)
)
BEGIN

    SET sql_log_bin = 0;

    SET @sql = SQLStatement;
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    SET sql_log_bin = 1;

END $$

DELIMITER ;

Then, require users to run

CALL Local_Alter_Table('ALTER TABLE ...');

GIVE IT A TRY !!!


There are a few backup options for you.

A standard dump (pg_dump) will be restorable on another machine but you may want to create the tablespaces first before restoring.  Where the tablespaces are is not really a concern for the recovery.  Note you can also do pg_dump -Fc and this will allow you to select tables to restore at restore time.

Another option is to use pg_basebackup if you are on 9.1 or higher and this will rebuild your data directories over replication.  See http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html for more on this option.


Does the Int type primary key insert order affect query performance in MySQL?

I already answered a question like this

Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
Jun 28, 2012 : Benefits of BTREE in MySQL

Of the three methods to insert data you mentioned, I would easily choose method #3 : Random insert id: 1-10000. Why ?

Ascending or Descending Ordered Insertion into an index structure will generate root and node splits 45% of the time as I mentioned in my two earlier posts.

In contrast, the ordering does not matter when it comes to reloading of a table from a mysqldump or upon the execution of a LOAD DATA INFILE command. That's because of the use of sorting mechanisms after the completion of the data load of the table and the index creation can be performed.

For example, look at how a MyISAM table is reloaded.

Data Pages for .MYD are populated
Index Pages for .MYI are populated with unique keys in parallel with Data Page Reload
Secondary Indexes are built by the internal execution of Repair by sorting

In that instance, order does not matter.

Switching gears to SELECT ORDER BY, performance could be nominally affected if needed keys from indexes are so split apart that multiple index pages must be accessed for ranges of data, this again leads me to choose method #3. The lower the frequency of index page splits, the fewer pages need to be read by an ORDER BY request.


Application/MS SQL Server Express Installer

Create the DB, make the DB files part of the installer, copy them into the right SQL Server directory, hope it recognizes it (will it?)

It will not, you will have to attach the database to your server afterwards. You could add a sql script that will attach the files to your server though.

  Let the application test for the existence of the DB, and, if it does not yet exist, run a SQL script (generated by SSMS, modified) to create the database (currently implemented).

This could be a good option. Perhaps even better if you could run the .sql file in your installation, that way your application doesn't have any delay when the users first run it.

I also think it depends on the size of the database files. If it's big you could go for the first option, if not i'd use the second option.


MariaDB InnoDB does not rollback on timeout

What is the client?  Does it have "auto reconnect" turned on?  Turn it off.
What I think is happening:

The UPDATE croaks (in any way) and gets rolled back.
The connection is reestablished, perhaps with autocommit.
The INSERT runs successfully and autocommits.
The COMMIT is silently ignored.

Put error checks after every SQL statement.


Reducing the duration of this COUNT() query

There are 2 possibilities for a covering index of the query:

a composite index on (con_codigo_fk, mcon_data). This would cover all similar queries.
If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data) can be used by different queries, so I wouldn't remove it.

Code to add the index:

CREATE INDEX ix__con_codigo_fk__mcon_data       -- choose a name
  ON dbo.tbMovimentoConta
  (con_codigo_fk, mcon_data) ;

a filtered index on (mcon_data) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).
The use cases for such indexes are much more rare so it's probably not useful to you.

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*)
from tbMovimentoConta
where con_codigo_fk = 1
  and mcon_data >= '2015-01-05'
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

Another issue - that the index isn't trying to address - is that while the time needed for the query with the WHERE condition might be explained by a bad plan and lack of appropriate index, the time for the 1st query, the whole table count (9 seconds) sounds a bit too much. (and the 1 second for the query after the index added is high, too).

I would be speculating without further details as the explanation might be from numerous different reasons (general/memory SQL Server settings, high load on the server, wide clustered index, etc) so I suggest you either add more details on the question or post a new question (with the CREATE TABLE script if the issue is only for queries involving this table).


Parse a column with a "range" in a WHERE in SQL Server

Alright, please don't hate me for this query, because I'm sure there are far better ways to do this (starting by fixing this design). With the sample data that you posted, this query should give you every key in the original table as a single column (KEY_Unified):

WITH CTE AS
(
    SELECT *
    FROM dbo.YourTable t
    CROSS APPLY
    (
        VALUES
            (t.KEY1),
            (t.KEY2),
            (t.KEY3)
    ) x (KEY_Unified)
    WHERE x.KEY_Unified IS NOT NULL
), CTE2 AS
(
    SELECT  KEY_Unified,
            CAST(RIGHT(LEFT(KEY_Unified,CHARINDEX('-',KEY_Unified,0)-1),3) AS INT) Start,
            CAST(RIGHT(SUBSTRING(KEY_Unified,CHARINDEX('-',KEY_Unified,0)+1,4),3) AS INT) Finish
    FROM CTE
    WHERE KEY_Unified LIKE '%-%'
), CTE3 AS
(
    SELECT 's' + RIGHT('000' + CAST(B.number AS VARCHAR(3)),3) KEY_Range, CAST(B.number AS VARCHAR(3)) n
    FROM CTE2 A
    CROSS JOIN (SELECT *
                FROM master.dbo.spt_values
                WHERE type = 'P') B
    WHERE B.number BETWEEN A.Start AND A.Finish
), CTE4 AS
(
    SELECT KEY_Unified
    FROM CTE
    WHERE KEY_Unified NOT LIKE '%-%'
    UNION
    SELECT KEY_Range
    FROM CTE3
)
SELECT *
FROM CTE4 t1
LEFT JOIN SomeOtherTable t2
    ON t1.KEY_Unified = t2.SomeKey
;

There are a couple of assumptions though.

The only kind of range you can have is this kind: s001-s100, you can't have s001-s100,s005-s006  for instance
Every key has a 4 char length, starting with s001 till s999


When do I have to use archive_command and when not

The first one (cd .) does nothing. It's a placeholder. It would be better to write true in my view, so it's more obviously intended as a no-op.

Turning archive_mode on but setting a no-op archive_command is a hacky workaround for the fact that changing archive_command only requires a server reload, but changing archive_mode requires a full server restart that can be disruptive in production.

The purpose of the archive_command, and all the rest, is covered in the documentation so I won't repeat all that material there.


SMO : The login already has an account under a different user name, when calling TransferData()

First, if you do both:
GRANT VIEW ANY DATABASE TO [username];

and
DENY VIEW ANY DATABASE TO [username];

the DENY still wins, so there was no sense to grant it, this is also the default permission that every new login has so there was no need to grant it again.
Second, you don't see the difference between database principal dbo and any member of the fixed database role db_owner. When you make some user a member of db_owner this user has all possible permissions in this database. But this has nothing to do with the fact "to see some database in Object Explorer".
To "see" any database in OE, login should have view any database permission.
Login and not user. So your login can has no view any database permission but still be a member of db_owner role, in this case user has all the permissions in that database but it cannot "see" any database in OE.
Dbo database principal is a special principal where any sysadmin (i.e. a member of sysadmin server role) is mapped, and the OWNER of database is also mapped to dbo.
The owner of database is one and only one login that created this database or was assigned to be database's owner by executing alter authorization statement:
ALTER AUTHORIZATION ON DATABASE::Dinesh TO [username]

This way you assigned the LOGIN username to be the owner of this database. The owner of database is of course a member of db_owner role but not vice versa. DBO will always "see" the database it owns in OE as any sysadmin will.
To your question:
the error you receive tells you that you cannot execute
CREATE USER [username] FOR LOGIN [username]

Because the LOGIN username is already mapped to this database, as dbo.
So you cannot re-map it to another username.
If you want your script to run without errors change the database owner to any other login, for example to sa login, this way the login username can be created in this database.
Then if you want your username to be database owner just drop your user username from this database and alter authorization to username, this way it will be mapped to dbo.
Alternatively, if you already know that you want this user to be database owner, drop the user username from your database prior to transfer and it still be there in the database as it is mapped to dbo.

So if then I create another login and I execute:
USE master; GO ALTER AUTHORIZATION ON DATABASE::dbName TO
[anotherUser]; GO Now username can not access the database.

This is because as I explained above, database OWNER can be only one login at a time. If you want your ex-owner to access this database you should map it:
USE dbName;
CREATE USER [username] FOR LOGIN [username] ;
GO

You don't need to map a login to database only if it is sysadmin or database owner (which, I repeat it, can be only one). Any other login should be mapped to database (create user for login) in order to access it.


Using pg_prewarm to load X number of latest rows into cache

Addressing Question #3

  the live_row_size I get from above query is not the same
  as the result mentioned in this answer (by Erwin). Am I doing
  something wrong?

You have:

SELECT pg_size_pretty(tuple_len / tuple_count) AS live_row_size
FROM pgstattuple('public.mytable');

The manual defines:

  tuple_len ... Total length of live tuples in bytes
  tuple_count ... Number of live tuples

You compute the medium length of live tuples. It seems like you expect Postgres to extract only live tuples when fetching data pages into RAM, but that's not how it works. Postgres just reads full pages into RAM, including any dead tuples that may be contained.

Hence, your next expression does not compute "live rows per page":

8192.00 / (tuple_len / tuple_count) AS live_rows_per_page

It computes "hypothetical maximum of live rows per data page with only live tuples", which is nice to know, but otherwise useless for your task.

Addressing Update #1

Why different sizes? In the referenced answer, I am leading with this:

  This is going to demonstrate that the various methods to measure the
  "size of a row" can lead to very different results. It all depends
  what you want to measure exactly.

Your findings seem to confirm that - though I am not sure what you compare exactly.

The referenced answer is a bit outdated. I improved some details and added numbers from pgstattuple for comparison. You need to install the module once per database (well you obviously have it, but maybe other readers):

CREATE EXTENSION pgstattuple;

Then:

SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr
      / CASE part WHEN 1 THEN NULLIF(x.ct, 0)
                  WHEN 2 THEN NULLIF(st.tuple_count, 0)
                  WHEN 3 THEN NULLIF(st.dead_tuple_count, 0)
                  WHEN 4 THEN NULLIF(st.tuple_count + st.dead_tuple_count, 0) END
            END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.big t                     -- provide table name *once*
   ) x
 , LATERAL pgstattuple(tbl) st             -- also get numbers from pgstattuple
 , LATERAL (
   VALUES
      (1, false, 'row_count'                           , ct)
    , (1, false, ' ------ DB_obj_size_func -------'    , NULL)
    , (1, true , 'core_relation_size'                  , pg_relation_size(tbl))
    , (1, true , 'visibility_map'                      , pg_relation_size(tbl, 'vm'))
    , (1, true , 'free_space_map'                      , pg_relation_size(tbl, 'fsm'))
    , (1, true , 'table_size_incl_toast'               , pg_table_size(tbl))
    , (1, true , 'indexes_size'                        , pg_indexes_size(tbl))
    , (1, true , 'total_size_incl_toast_and_indexes'   , pg_total_relation_size(tbl))
    , (1, true , 'live_rows_in_text_representation'    , txt_len)
    , (2, false, ' ------ pgstattuple ------------'    , NULL)
    , (2, false, 'live_tuples'                         , st.tuple_count)
    , (2, false, 'dead_tuples'                         , st.dead_tuple_count)
    , (2, true , 'total table / live tuples'           , st.table_len)
    , (2, true , 'live table / live tuples'            , st.tuple_len)
    , (3, true , 'dead table / dead tuples'            , st.dead_tuple_len)
    , (4, true , 'total table / all tuples'            , st.table_len)
   ) l(part, is_size, what, nr);

Simple solution?

If your query does not have side effects, the most efficient method should be to just execute it to fetch relevant data pages into RAM.


How to fix a MySQL server with a full hard drive?

You did not need to erase the large tables. Why ???
Here is the strange thing about mysql. mysqld uses MyISAM temp tables for it internal operation. When mysqld runs out of space, it does not crash. IT FREEZES !!!
The following is  my answer to 2 year old post "Site Offline" MySQL server failing to start and stop
EXCERPT
According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no
error occurs. The server suspends the operation until space becomes
available, and then completes the operation.

In reality, mysqld is not dead. It is waiting for free diskspace to continue whatever it normally does. Even during startup, if mysqld makes a temp table, it is a MyISAM table. No space left on the disk? No problem. MyISAM just patiently waits for you to make free space.
END OF EXCERPT
All you had to do was manually delete the binary logs. Then, mysqld would start to function normally. Even deleting just 10 logs would have freed 1G and would make mysqld get going again.
You need to do the following:
Login to mysql and run
mysql> RESET MASTER;
mysql> SET GLOBAL expire_logs_days = 7;

Then, add this to my.cnf
[mysqld]
expire-logs-days = 7

Now, mysqld will automatically delete any binary log older than 7 days.


I took the liberty of editing your SQL so the clauses are on separate lines. I violated CodeReview group discipline by doing that. Sorry!  My suggestion to you is to format your queries so the various clauses are clearly visible, as I did. This kind of formatting, in my experience, makes it easier to understand the logic of queries and to spot errors.

You've done a good job declaring your columns NOT NULL where possible. That helps performance a lot.

Your first query seems to be illogical, as it combines an aggregate function with a DISTINCT qualifier. MySQL allows a lot of sloppiness in aggregate queries unfortunately.

It looks like you're looking for a resultset with a row for each location that shows the the largest replication_number at that location. Do you want this query instead?

select  location_code,
        Max(replication_number) as replication
   from hybrid_exp_trait_dtl  t
  where t.experiment_id='NSKOK12K1102' and t.trial_status='MLT'
  group by location_code

Your Index_4 will allow this query to be satisfied using a so-called loose index scan, which is really very fast.

Your second query will most likely be a lot faster if you change your fourth compound key (the one you've named hbrid) to have the following order.

(EXPERIMENT_ID,TRAIT_NAME,HYBRID_NUMBER)

That is because your query looks for exact values of EXPERIMENT_ID and TRAIT_NAME. When those two columns come first in the index, the query can jump to the exact position in the index and then sequentially reel off your results.

Now, you may have some other query that needs to have HYBRID_NUMBER first, but you didn't mention it in your question.

You asked what effect these indexes will have on load performance. It's hard to answer that question exactly without knowing a lot about your load process.

You don't have any indexes that call for uniqueness. That is very good for loading performance.

But, there are a few things you can do to speed up loading. They are detailed here. The most important speed trick is probably to avoid autocommit.  Start your import process with this SQL command.

 SET autocommit=0;

Then do a few hundred INSERT or UPDATE statements in a row, then do

 COMMIT;

Don't try to do too many INSERT or UPDATE statements before COMMIT or you'll use up a lot of RAM with transaction buffers.

You can also turn of foreign key checks while loading. See the web page mentioned above. But be careful. If your loaded data violates foreign key constraints, when you try to turn checking back on you'll have a mess.

When you're done with a load operation that changes a large fraction of the rows, do

 OPTIMIZE LOCAL TABLE hybrid_exp_trait_dtl;

Your table will be unavailable while this OPTIMIZE operation runs: it rebuilds the table and indexes.


Do mongodb's 2dsphere index works on multidimensional coordinantes array?

The GeoJSON spec doesn't support multidimensional coordinate positions in the format you are suggesting:

  A position is represented by an array of numbers. There must be at least two elements, and may be more. The order of elements must follow x, y, z order (easting, northing, altitude for coordinates in a projected coordinate reference system, or longitude, latitude, altitude for coordinates in a geographic coordinate reference system). Any number of additional elements are allowed -- interpretation and meaning of additional elements is beyond the scope of this specification.

A 2dsphere index in MongoDB 3.0+ will allow additional values in the coordinate array but only the first two values will be indexed and used in a 2dsphere query. This allows GeoJSON objects with additional elements (eg. altitude) to be saved in MongoDB, but interpretation or use of those extra values is up to your application code.

The MongoDB manual has information on supported GeoJSON objects.

There's also a relevant feature suggestion you can upvote/watch in the MongoDB issue tracker: SERVER-691: n-dimensional geospatial search.


I managed to figure it out.  I started digging more into the user table and found the root user entry, and then found the authentication_string column.  I had already followed the guides that someone commented and it didn't work, not sure why, so I tried the following.
mysql> USE mysql
mysql> UPDATE user
    -> SET authentication_string = 'newpassword'
    -> WHERE User = 'root';

RESULT: Query OK, 1 row affected (0.01 sec)
Rows matched:1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;

I then checked on phpmyadmin and it shows that it has a password on it now.  I think it should be good now...unless there is another check i need to run to be sure?


MySQL : Till when MySQL retains binary logs?

The number in the mysql-bin.nnnnnn filename is simply a sequence number.  This gets updated every time a FLUSH LOGS is initiated, either manually or through predetermined means and is sequential (e.g. normally starts at 00001 and goes up).

The logs will be kept forever unless you specify an expiry.  This should be done in your MySQL configuration:

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_expire_logs_days

You can also delete the binary logs manually within MySQL by using the command:

PURGE BINARY LOGS .... [TO FILENAME | BEFORE 'DATE' ]

See details here http://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html

It is possible to delete the files manually (outside of MySQL) but NOT recommended.

See http://dev.mysql.com/doc/refman/5.6/en/binary-log.html for details on the binary logging system.


Different way to do a LIKE WHERE condition?

Two things you can do:

Use SendTime >= '20140101' AND SendTime < '20150101' instead of the year(SendTime)= 2014. This makes the condition sargable and indexes can be used.
Add a composite index on (SendTime, MessageType) so the query does not have to do a full table scan but only a (partial) index scan.

The query rewritten:

SELECT
    month_date = month(SendTime),
    year_date = year(SendTime),
    MessageType,
    message_count = COUNT(MessageType)
FROM Message
WHERE SendTime >= '20140101' AND SendTime < '20150101'
  AND MessageType NOT LIKE '%test%'
  AND MessageType NOT LIKE '%ANOTHER THING%'
  AND messagetype NOT LIKE '%STUFF%'
GROUP BY year(SendTime), month(SendTime), MessageType ;

Another option would be to add computed columns on year(SendTime) and month(SendTime) and an index on year(SendTime), month(SendTime), MessageType - and keep the original query!


Debuging import of a large dump file to MySQL

There are two possibilities for this issue.

Wait timeout
Low resource on the server where you are trying to restore the DB.

Increase the lock_wait_timeout and wait_timeout to 300 seconds.
Try to restore the Database where you have enough resource to export the file(generally if you are using a t2.nano or t2.micro instances(where you are executing the restore command and keep the Backup file), then it'll kill the restore query due to low resource)


Try this script, It has helped me in the past:

-- Unused Index Script
-- Original Author: Pinal Dave
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/


What does MySQL "USE INDEX()" without any arguments do?


Understanding of Oracle Statement-Level Read Consistency between Oracle 10g and 10g+

The behaviour did not change. It has been the same since Oracle 7 at least (probably before, but that was before my time) and still is today with Oracle 19. The documentation was just expanded to include the behaviour at isolation levels other than the default READ COMMITTED, or when using the Flashback Query mechanism.

(You should really be looking at the latest documentation: https://docs.oracle.com/en/database/oracle/oracle-database/index.html - 11g is very old)

The important point is that the default isolation used by Oracle (statement-level consistency) implies that repeating the same statement may not deliver the same result since other transactions may have updated the data during the first execution of the statement. Oracle uses multi-version consistency to provide concurrent access such that readers don't block writers and writers don't block readers.


cross database trigger: server principal is not able to access the database under the current security context

This should be fairly simple to accomplish via module signing. Look at my other answers here on DBA.SE:

Permissions not allowing Update or Select on a trigger executed function
Permissions in triggers when using cross database certificates

The basic concept is as follows:

Create a Certificate in the Source DB (i.e. where the Trigger exists).
Sign the Trigger using ADD SIGNATURE.
Create that same Certificate in the Target DB.
Create a User in the Target DB from that Certificate.
Grant that Certificate-based User INSERT, UPDATE permission on the table in the Target DB.


PostgreSQL function to get levels (hierarchy)

One simple way would be correlated subqueries in a CASE expression of a single query:

CREATE OR REPLACE FUNCTION f_get_level(_level text, _id_levels int)
  RETURNS text AS
$func$
SELECT CASE _level
          WHEN 'Level 1' THEN (SELECT description_1 FROM level_1 WHERE id_1 = l.id_1)
          WHEN 'Level 2' THEN (SELECT description_2 FROM level_2 WHERE id_2 = l.id_2)
          WHEN 'Level 3' THEN (SELECT description_3 FROM level_3 WHERE id_3 = l.id_3)
          ELSE 'Unexpected input for _level!'
       END
FROM   levels  l
WHERE  l.id_levels = _id_levels
$func$  LANGUAGE sql STABLE;

Call (exactly as requested):

SELECT f_get_level('Level 1', 10);

Another option would be a PL/pgSQL function with separate CASE (not SQL CASE!) branches and an OUT parameter ...

CREATE OR REPLACE FUNCTION f_get_level2(_level text, _id_levels int, OUT _descr text) AS
$func$
BEGIN
   CASE _level
   WHEN 'Level 1' THEN
      SELECT description_1
      FROM   levels  l
      JOIN   level_1 l1 USING (id_1)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   WHEN 'Level 2' THEN
      SELECT description_2
      FROM   levels  l
      JOIN   level_2 l2 USING (id_2)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   WHEN 'Level 3' THEN
      SELECT description_3
      FROM   levels  l
      JOIN   level_3 l3 USING (id_3)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   ELSE
      RAISE EXCEPTION 'Unexpected input for _level: %!', _level;
   END CASE;
END
$func$  LANGUAGE plpgsql STABLE;


Transactional replication failure handling

Yes, your understanding is correct. Usually, the Publisher and Distributor are the same servers.
Yes, there are jobs that are automatically created that notify you when the replication is down. After creating the replication, check your SQL Server Agent and see the new jobs. You can alter the schedule and the notification properties. But you can create your own job that checks some latest record in two instances if they have linked server to one another.
That one I think you will have to set it up by the application.
Subscribers can get queries and read operations, that will let you the publisher with less I/O and tend to be faster. Updates will work though, so you will have to treat it so that the user knows that it may not persist.


SQL Server Standard 2016 Service Pack and Cumulative Update

Query 1: You have to look this up in the release notes. Normally you can do that, but you always need to check the release notes regardless. There is a possibility that it is not possible. Please refer to this link,  which will guide you to all the SPs and latestes CUs available, including the release notes.

https://sqlserverbuilds.blogspot.com

Query 2: The same applies as the answer to your query 1.

Query 3: Since you are 'only' (and I say this between quotes, because you always need to plan things very carefully) upgrading to a new service pack/CU combination, not a new version (like from SQL Server 2016 -> SQL Server 2017), it is fairly straight forward. Install the (chain) of service pack(s)and required CU. However:

Make sure you test and document the upgrade path AND the rollback path before you start the upgrade for your production environment.
Since you are upgrading a cluster, make sure the cluster is healthy and you can are sure you can failover without any issues.
It would be advisable to create a baseline prior to the upgrade in order to compare things afterwards. The most easy way to do this is use performance monitor to capture different SQL Server and Windows Server counters over a month or so. This way you can get an insight of your normal workload and compare it with the workload after the upgrade. This can help in determining if the upgrade caused some issues or not. Even when you think there are none, you could be surprised. :-)
Preferably perform the rollout in phases over your development, test, quality and production environment. Do not upgrade all the environments within a short time span, but let every environment run for a week or more, depending on your requirements, to filter out any possible issues. If you do not have a dev, test, quality, production environment, then at least perform the upgrade on a test environment. Maybe you could use Distributed Replay to simulate a production workload to see what the effects are.
Determine if you want to upgrade to the latest service pack. You are asked not give advice based on personal opinion here, but evaluate for yourself if you want to be on a Service Pack that was brought out yesterday and not all issues/ bugs are known. ;-) There were updates that brought SQL Server to it's knees or even... cause corruption... Check how old it is and also check if other people have had issues with it after they deployed it. Just do some plain 'ol Google searching. :-)
Make sure you thoroughly read all the release notes to check if there are any changes that could have a high impact on your environment. For example, discontinued features, new features, etc.
Just take the extra full copy-only backup before you start and put it somewhere safe. Just in case the 3rd party backup software fails the restore... You don't want to explain that to your boss.
Perform a health check prior to upgrading, so you don't upgrade a broken server. Check the logs, check for performance issues, check the backups, Windows Event Viewer, make sure you check if your DBCC CHECKDB ran recently. Although, you should do this by default. You could also use sp_Blitz from Brent Ozar.
Personally I always perform a DBCC CHECKDB directly after the upgrade AND an extra copy-only backup, just to be sure the upgrade didn't cause corruption and you always have the starting point from directly after the upgrade for possible referencing later on.
Don't fool around with VM snapshots, especially on a cluster. They are not backups and could potentially lead to problems with the snapshot/storage/ io performance. It is more an extra risk than a benefit IMHO.
Check disk space prior to the upgrade
Disable backups and SQL Server Agent jobs during the upgrade
Since you are upgrading a cluster, you can perform a rolling upgrade; https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-ver15#perform-a-rolling-upgrade-or-update. My strategy is to failover to the second node, to make sure it works, then upgrade the first node, fail back to the first node and upgrade the second node. Be aware that this could mean downtime, since the SQL Server service is stopped on one node and started on the one you fail over to. Some applications can handle this, some not.
Verify the version afterwards to make sure you are indeed on the expected version.

I think there are a lot of checklists out there on the internet and you can actually easily find them. Have you done some more research but posting this question?

Query 4: The rollback plan is uninstall the SP/ CU, but that is only the SQL Server part of it. Make sure that you test the whole scenario.

Is this info usefull?


What sequence when changing the master MySQL server, but not IP?

I'm interpreting your scenario as follows:

You start with one master database and two replicas; both replicas are replicating from the master.
During the promotion, you want to set the original master to replicate from the promoted replica.
The second replica you leave alone to continue replicating from the original master.

The command RESET SLAVE on a host that is currently a master would have no affect since this command works on replicas.

The Change master statement on the master needs to be run using the log coordinates of the promoted replica at the point in time of the promotion. The best way to ensure this, is to set read_only on the original master, validate replication has caught up, check the log coordinates (show master status on the host you are promoting), then promote.

In order for the second replica to continue replicating from the original master you need to have log-slave-updates enabled on the original master, otherwise it won't receive anything written by the new master. You can quickly test this by creating a new dummy db or table on the new master and validate if it replicates down to the extra replica.

If I were setting up for this

Prerequisites

All hosts should be running with log-bin, log-bin-index, log-slave-updates, relay-log, and relay-log-index. Replicas should be running with read_only set to true. Application users and standard database users should not have the SUPER privilege (e.g. ALL PRIVILEGES on *.*).

Promotion

Apply a maintenance page and stop background processing if applicable.
On the existing master mysql -e 'set global read_only=1'; select sleep(1); show master status;
On the existing master, set read_only = 1 in your mysql configuration file in case the host is restarted.
On the replica to be promoted mysql -e 'show slave status\G'| egrep 'Exec|Relay_Master_Log_File'
Compare the coordinates of the master to the replica coordinates to ensure they match. Until they do, your replica is not in sync, wait or investigate as needed.
On the replica being promoted mysql -e 'show master status; reset slave all; set global read_only=0'
Update the replica being promoted configuration file to read_only = 0.
On the original master run the start slave command with the coordinates from step 6, and the start slave command.
Redirect your application and background processing to write against the new master's IP address, and restart your services.

Hope this helps.


T-SQL How to Pivot on the Values of a Table?

Try this Query;

Select s.ItineraryID, R.ShortName as RoadTripName,  sum(EstimatedMiles) as TotalMiles, count(itineraryid) as TotalDays,
SUM(case DayType.DayTypeCode when 'XX' then 1 else 0 END ) as 'Planning/Prep',
SUM(case DayType.DayTypeCode when 'SE' then 1 else 0 END ) as 'Short Event',
SUM(case DayType.DayTypeCode when 'LE' then 1 else 0 END ) as 'Long Event',
SUM(case DayType.DayTypeCode when 'WD' then 1 else 0 END ) as 'Writing Day',
SUM(case DayType.DayTypeCode when 'TD' then 1 else 0 END ) as 'Tech Day',
SUM(case DayType.DayTypeCode when 'HD' then 1 else 0 END ) as 'Hiking Day',
SUM(case DayType.DayTypeCode when 'PD' then 1 else 0 END ) as 'Photography Day',
SUM(case DayType.DayTypeCode when 'LD' then 1 else 0 END ) as 'Layover Day'
from  RoadTrip r inner join Itinerary s on r.roadtripid = s.roadtripid
  inner join DayType on s.DayTypeId = DayType.DayTypeId
group by s.ItineraryID,  R.ShortName

Or another way using Pivot

Select ItineraryID, RoadTripName, EstimatedMiles,[XX] as 'Planning/Prep',[SE] as 'Short Event',[LE]  as 'Long Event',[WD] as 'Writing Day',[TD] as 'Tech Day',[HD] as 'Hiking Day',[PD] as 'Photography Day',[LD] as 'Layover Day'
FROM
(SELECT i.ItineraryID, r.RoadTripName,  dt.DayTypeCode, EstimatedMiles, 1 as daycount
    FROM RoadTrip r
    inner join Itinerary i on r.roadtripid = i.roadtripid
     --Itinerary i
    inner join DayType dt on dt.DayTypeId = i.DayTypeId
) ps
PIVOT
(
COUNT(dayCount)
FOR DayTypeCode IN ( [XX],[SE],[LE],[WD],[TD],[HD],[PD],[LD])
) as Pvt


It's possible to skip duplicate keys (i.e. MERGE) in a BULK INSERT in MySQL?

There is no MERGE statement in MySQL. You can do this quite easily as follows (all DDL and DML shown at bottom of post and also in the fiddle here):

I created and populated two tables foo_1 and foo_2 with identical data and identical PRIMARY KEYs, the fields (foo_x, foo_y);

I then added a record to table foo_1.

Then I performed the query:

INSERT INTO foo_2
SELECT * FROM foo_1 WHERE (foo_x, foo_y) -- foo_x, foo_y are the PK!
NOT IN (SELECT foo_x, foo_y FROM foo_2);

And then SELECT * FROM foo_2 and the new record from table foo_1 had been copied over to table foo_2. This is a simple and effective method of doing what you want as far as I can see. Doing this with a single field PRIMARY KEY is even easier!

=============== DDL and DML ============

table foo_1:

CREATE TABLE foo_1
(
  foo_x INTEGER NOT NULL,
  foo_y INTEGER NOT NULL,
  foo_2 INTEGER NOT NULL,
  foo_3 VARCHAR (25) NOT NULL,
  PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_1
VALUES
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');

and table foo_2:

CREATE TABLE foo_2
(
  foo_x INTEGER NOT NULL,
  foo_y INTEGER NOT NULL,
  foo_2 INTEGER NOT NULL,
  foo_3 VARCHAR (25) NOT NULL,
  PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_2
VALUES
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');

Insert new record into table foo_1:

INSERT INTO foo_1 VALUES (1, 5, 24, 'foo_3_val_5');

And then:

INSERT INTO foo_2
SELECT * FROM foo_1 WHERE (foo_x, foo_y)
NOT IN (SELECT foo_x, foo_y FROM foo_2);

And then check that table foo_2 now contains the single new record from table foo_1:

SELECT * FROM foo_2;

Result:

foo_x   foo_y   foo_2   foo_3
1       1           2   foo_3_val_1
1       2           7   foo_3_val_2
1       3           8   foo_3_val_3
1       4           9   foo_3_val_4
1       5          24   foo_3_val_5


I use this query to monitor all running SPID's and the column Blocked shows you the blocking session ID. Try it

SELECT s.STATUS
    ,db_name(r.database_id) AS DB
    ,s.session_id AS SPID
    ,r.blocking_session_id AS blocked
    ,s.login_name AS userN
    ,s.host_name AS pc
    ,s.program_name AS app
    ,t.TEXT
    ,spu.internal_objects_alloc_page_count AS Pages_Allocated
    ,s.cpu_time AS CPU_T
    ,s.memory_usage AS Memory
    ,s.total_scheduled_time AS Tot_Sch_T
    ,s.total_elapsed_time AS Tot_Ela_T
    ,s.reads
    ,s.writes
    ,s.logical_reads AS Log_Read
    ,s.row_count
    ,s.last_request_start_time AS Last_Strt_T
    ,s.last_request_end_time AS End_T
    ,r.open_transaction_count AS OPENTRAN
    ,CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, s.last_request_start_time, s.last_request_end_time), 0), 108) AS TOTAL_TIME
    ,r.percent_complete
    ,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
LEFT JOIN sys.dm_db_session_space_usage spu ON s.session_id = spu.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id > 50
    AND s.STATUS LIKE 'RUNNING'
ORDER BY s.total_elapsed_time DESC


I would  recommend a Configuration File instead a bunch of command line options.

There have a closer look at Windows Service Options

Once you created the configuration file you can install the service like this:

mongod.exe --config c:\MongoDB\config\mongo.cfg --install

Note, the default service name is MongoDB. Unless you specify processManagement.windowsService.serviceName you have to use os.system('net start MongoDB') in order to start the service.

However, if you like to run more than one MongoDB service, the service names have to be distinct.

Running start mongod ... starts the service in foreground and the terminal remains open.


2,147,352,576 bytes is 2Gb which implies you have a 32-bit SSIS running on 32-bit Windows, so the 16Gb total memory of which 10.9 is free is fairly meaningless but is where the 31% comes from: (17,179,328,512 - 11,714,314,240) / 17,179,328,512 = 0.318.

A 32-bit process can only use 2GB (or under some configurations 3Gb) unless it is compiled LARGEADDRESSAWARE and running on a 64-bit edition of Windows. On 64-bit Windows a large-address aware process should be able to use 4Gb (see this which refers to here).

In this case the process seems to have ~386Mb available in the 2GB address space and is failing to allocate 100Mb which is less than that - one educated guess is that this could be due to fragmentation: if SSIS is wanting a single coherent block and the remaining free address space is in blocks smaller than that spread around the address space.

No new SSIS packages have been added.

The change is likely due to increases in incoming data size in that case.

Possible Solutions

If you are running a 32-bit Windows edition you could try using the /3GB switch to ask Windows to use the 3+1 address space arrangement for 32 bit processes instead of 2+2 (see https://technet.microsoft.com/en-us/library/bb124810(v=exchg.65).aspx or similar reference for your edition of Windows) - if SSIS is capable of using the larger address space this will give it a bit more room.

If you are using a 64-bit Windows install and 64-bit SQL then try to make SSIS use its 64-bit run-time. There are issues with some data access drivers here though so that may not work. Also the design-time environment is always 32-bit. See https://technet.microsoft.com/en-us/library/ms141766(v=sql.105).aspx and other documentation.

In either case you may be able to break down your ETL process into smaller packages and chain them together with Execute Package tasks. With the "ExecuteOutOfProcess" option turned on each package called should be able to use its own 2Gb address space.

You might also try tweaking the way buffers are allocated in data flow tasks using the DefaultBufferMaxRows and DefaultBufferMaxSize properties; see here, here and here.


How could DBAs be more 'programmer friendly'?

I've been a MySQL DBA for the past 6.5 years. I've also spent some 16 years as a developer and have interacted with many DBAs. Many of them pragmatic. Some of them obnoxious. A few have no idea what it means to be a DBA.

I have come to this conclusion:

Technically speaking, DBAs who have one or more of the following qualities are the best to work with:

Spent years as developers themselves
Have a grasp of database theory
Have a good understanding of how the RDBMS works internally
Have superior knowledge of the operating system

Very disciplined, knowledgeable DBAs have a lot to share and offer. They may see database performance from a perspective not really considered by Developers.  Developers know what they want from the database. DBAs know how to be "polite" to the database.

As far as personalities go, there will always be conflicts, pettiness, and maybe even envy. One thing is for certain: In no particular order, DBAs and Developers are like husbands and wives (I've been happily married for 16 years with on-going projects [4 children]).

Regardless who is viewed as the husband and who is viewed as the wife, these principles apply:

one must consult the other
one must value the perspective of the other
one must make decisions for the good of both parties
one must support, and not sabatoge, the decision made
one must not denigrade the other if decisions result in bad consequences
one must rejoice in the contribution of both parties to the success of decisions
one must consult a higher authority (HA) if a decision cannot be mutually agreed upon

These seven(7) principles apply just as much in the workplace, especially in the IT realm.

By communicating every step of way, all should :

layout their expectations
engender respect for the other party's ability to do their part based on past performance
have trust and confidence that the other party can complete their assignment
live up to our own expectations
acquiese under the guidance of the HA (see principle #7)

There is no room for micromanagement in this. DBAs SHOULD NOT TELL Developers how to think like DBAs. Developers SHOULD NOT TELL DBAs how to be Developers. Final decisions on database performance and usage must rest with DBAs. Final decisions on application needs must rest with Developers. This symbiosis must be maintained always.

FINAL THOUGHTS

Principle #7 requires active participation and oversight by the HIGHER AUTHORITY (the HA), i.e., project manager, team leader, lead developer. Your HA better know how both parties work individually and how both parties should work together. If the HA does not establish ground rules for both parties, or if the HA fails to guide the parties individually and together, projects will always come to a halt at some point and endanger the very existence (employment) of the Developer, the DBA, or even the HA.


Equivalence of correlated DATEADD intervals

In that case they are equivalent behaviors even though the ScalarOperator definitions are different. This is because in other cases they can't be swapped. Think about:

datediff in days, which depends on which month and also must account for leap year.
datediff in seconds which will be non-deterministic when there is a leap second.
etc.

SQL Server doesn't gain any advantage changing one scalar operator's definition to look more like another one's. What would be the benefit? If you said WHERE column = 4 * 5 wouldn't it be confusing if the scalar operator said WHERE column = 2 * 10? Even if you have another query somewhere that uses 2 * 10?

In other words, if you said DATEADD(MONTH, n*12, ... I see no reason at all for SQL Server to change that expression to DATEADD(YEAR, n, ... just because we all happen to know that there are 12 months in a year. You asked for a delta by months, not years, so that's what the expression should show.


Select statement on tables with the same name and schema but in different databases

SELECT  GROUP_CONCAT(CONCAT(table_schema, '.', tableA))
    FROM information_schema.tables
    WHERE table_name = 'tableA';

will generate the list you need.

Figure out what the query needs to look like (and I do not think your example is correct), and modify this SELECT to generate the desired query.

Then copy and paste it into mysql commandline tool to run it.


SQL Server Primary key / clustered index design decision

You are correct to separate "clustered index" from "primary key":

A clustered index is the organisation of data on disk is better if

narrow
numeric
increasing (strictly monotonic)

The primary key identifies a row

Note: GUIDs make poor clustering keys

In this case, with the surrogate column, the table has 2 candidate keys:

ProductHistoryID
ProductNo + CreatedDateTime

Assumed convention states that the ProductHistoryID becomes the PK, but you can leave the PK on (ProductNo, CreatedDateTime): it will just be non-clustered. Which leads to indexes:

clustered index should be on ProductHistoryID
unique non-clustered index on (ProductNo, CreatedDateTime)

Example

CREATE TABLE Product (
    ProductHistoryID int NOT NULL IDENTITY (1,1) NOT NULL,
    ProductNo ...
    CreatedDateTime ...

then you a choice of

    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductHistoryID)
    CONSTRAINT UQ_Product UNIQUE NONCLUSTERED (ProductHistoryID)

or

    CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductNo, CreatedDateTime)
    CONSTRAINT PK_Product UNIQUE CLUSTERED (ProductHistoryID)

Also, the pattern you have is a "type 2 Slowly Changing Dimension"


MySQL NDBcluster 7.3 limitation on Table Joining in a single SQL statement

This says 61.  However, it could be an oversight that NDB is different.


UPDATE n:m relation in view as array (operations)

I'll assume a single given art.id at a time.

Since there is no provision to explicitly lock not-yet-existing rows ("value locking") in Postgres, it's hard to defend against concurrent INSERT commands in the default isolation level READ COMMITTED. The more expensive isolation level SERIALIZABLE should solve this, I didn't take a closer look, I think I have a better idea.

A "brute force" approach in READ COMMITTED would be to lock the whole table art_tag in SHARE ROW EXCLUSIVE mode. The manual:

  This mode protects a table against concurrent data changes, and is
  self-exclusive so that only one session can hold it at a time.

If you have lots of concurrent transactions writing to the table, performance will deteriorate quickly either way.

To only defend against concurrent UPDATE / DELETE commands in the default isolation level READ COMMITTED, lock all rows of the given article_ref in art_tag and wrap the whole operation into a single transaction.

Solution

There is a smarter alternative: take a FOR NO KEY UPDATE lock on the one row in the master table art. (FOR NO KEY UPDATE is a bit less restrictive than the common FOR UPDATE and good enough for our purpose.)

This is assuming that you have no other, possibly racing, concurrent write access to art_tag that ignores locks on art. It's your responsibility to make sure of it.

BEGIN;

SELECT * FROM art WHERE id = 1      -- we do not actually retrieve rows
FOR NO KEY UPDATE;                  -- only to lock the master row exclusively

DELETE FROM art_tag
WHERE  tag_ref <> ALL ('{1,2,3}'::bigint[]);

INSERT INTO art_tag (article_ref, tag_ref)
SELECT 1, unnest('{1,2,3}'::bigint[])
ON CONFLICT DO NOTHING;             -- requires Postgres 9.5 or later

COMMIT;                             -- locks released at end of transaction

To fine-tune behavior in case of conflicts consider the NOWAIT or SKIP LOCKED modifiers.

Related:

Postgres UPDATE ... LIMIT 1
How do I insert a row which contains a foreign key?

Function

You can wrap this into a function to simplify parameter passing and have an automatic transaction wrapper:

CREATE OR REPLACE FUNCTION f_set_tags(_article bigint, VARIADIC _tags bigint[])
  RETURNS void AS
$func$
BEGIN
   PERFORM * FROM art WHERE id = _article
   FOR NO KEY UPDATE;

   DELETE FROM art_tag
   WHERE  tag_ref <> ALL (_tags);

   INSERT INTO art_tag (article_ref, tag_ref)
   SELECT _article, unnest(_tags)
   ON CONFLICT DO NOTHING;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_set_tags(1,1,2,3);  -- 1st param is article_ref, rest are tag_ref

Or, if you have an actual array to pass:

SELECT f_set_tags(1, VARIADIC '{1,2,3}');
SELECT f_set_tags(1, VARIADIC your_array_variable);

For small sets of tags (max. 100), a VARIADIC parameter should be particularly convenient, to pass a list of values for the array. You can still pass an actual ARRAY (with any number of elements). Details:

Return rows matching elements of input array in plpgsql function

Could be a plain SQL function as well (switch back SELECT for PERFORM). I expect PL/pgSQL to be a bit faster for repeated calls in the same session (prepared statements).

Caution! Locks are only released at the end of a transaction, so it might be unwise to call this function in the context of a bigger outer transaction!


In SQL, it's not possible to have a function that simultaneously figures out what columns it should return and return them.

It's a consequence of the fundamental rule that the output structure of a SQL query must be known when parsing a query, before executing it.

If the client-side cooperates, that's not a problem. Just implement a two-step process:

step #1: call inverse_columns() that returns the SELECT statement as an string (or just the list of columns and your client code builds the rest of the statement).

step #2: run the SELECT statement.

There's no manual process involved if you can do client-side automation.

For instance newer versions of psql (9.6) have a \gexec function that executes a query and reinject the result as an SQL query.

Reworking a bit your except_select function to return a query instead of a function:

CREATE OR REPLACE FUNCTION public.except_select(_table_name text DEFAULT NULL::text, _col_list text[] DEFAULT NULL::text[])
 RETURNS text  LANGUAGE plpgsql IMMUTABLE
AS $function$
DECLARE
ret_cols text[] := inverse_columns(_table_name, _col_list);
ret_vals record;
BEGIN
RETURN 'SELECT '|| array_to_string(ret_cols, chr(44)||chr(32)) ||
    ' FROM ' || _table_name;
END;
$function$

Say we want to query all of pg_stat_activity except the query field, this could be written in psql as:

select except_select('pg_stat_activity', '{"query"}') \gexec

Result with expanded display (\x):

-[ RECORD 1 ]----+------------------------------
datid            | 12303
datname          | postgres
pid              | 15175
usesysid         | 10
usename          | daniel
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2016-12-17 16:10:07.200232+01
xact_start       | 2016-12-17 16:21:10.069285+01
query_start      | 2016-12-17 16:21:10.069285+01
state_change     | 2016-12-17 16:21:10.069288+01
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 622


Don't use / (or certain other things that could confuse the file manager) in a table name.

Perhaps you can rename it?

Update

OK, S is the database name, AD is the table name.

No, you cannot move .ibd files around.  There is critical information in ibdata1 that will get confused.

It is possible to do

RENAME TABLE X.AD TO Y.AD

(At least that was possible before 8.0.  The internals of 8.0 are radically different; if such a cross-db RENAME TABLE fails, file a bug report with bugs.mysql.com )


PostgreSQL: how to create both sequence and table simultaneously?

You can create the sequence implicitely through the serial (pseudo) type and then change its current value:

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER);
select setval('mytable_gid_seq', 100);

or alternatively using

CREATE TABLE mytable (gid serial NOT NULL, size INTEGER);
alter sequence mytable_gid_seq restart with 100;

This is slightly different than defining a minvalue - but that would only matter if the sequence was set to wrap. This version also creates a link between the sequence and the table, so that the sequence is automatically dropped when the table is dropped (in your script you would need to alter sequence mytable_gid_seq owner to mytable.gid);


SSIS installation on the same server as SQL Server

I concur with @dan Guzman's comment. You're likely overallocated on memory for SQL Server (and probably for the box itself). Unless you just have continuous package execution and/or suboptimal design patterns in play, that smells like the box will be happy with 1/4 of the resources - just make sure it has the best network speed (assuming data comes from server 1, ssis packages run on server 2 and push data to server 3, you'll likely be network bound). Profile and test, of course.
SSIS is an in-memory run-time engine. When you launch jobs from SQL Agent, they are going to run in their own address space so your 64GB of free memory is going to allocated to the Operating System, dtexec (the process that actually runs packages), etc. Getting into the weeds of an SSIS package, you're only really burning memory when you have data flow tasks and even then, the typical culprits are blocking transformations (aggregations, merge joints, etc) and fat data types n/varchar(max) or varbinary data in the pipeline. Better design can reduce memory contention. And of course, depending on your data source/destinations, you might have to deal with 32 bit drivers so the maximum memory per package execution would be capped under 4GB.
SQL Server is going to need memory to keep the system databases running + the SSISDB for project deployment model. That's likely a trivial amount of resources (disk + memory) but yes, good job on setting a maximum capacity on SQL Server.


I cant figure out the error probably with the date field in this mysql stored procedure

Syntax issue in the CREATE OR REPLACE. I've not written any procs with this notation so for me it rang alarm bells. I checked both with and without and dropping the 'OR REPLACE' allowed the proc to be created. You can 'DROP PROCEDURE' in advance of creating is for the same effect.

Try with;

DELIMITER //
drop procedure if exists getEmpInfo1;
create procedure getEmpInfo1(in n INT(3),out nam varchar(10),out addres varchar(20),out db date)
begin
select name,address,dob into nam,addres,db from empDetails where id=n;
end//


Is it safe to pg_dump and pg_restore a new postgres database that has malware?

Probably.  But if you just recreate the same vulnerable setup, you will just get infected again from scratch.  They probably got in by guessing the weak password on a super-user account.  So set up all new passwords on the new system and make then strong ones. (pg_dump will not carry over your old passwords anyway, but pg_dumpall will.). It would also be good to lock down your new pg_hba.conf so that superusers can only connect locally, or at least only from white-listed IP addresses.
They probably created a new function which gives them shell access, and pg_dump will carry over that function definition.  But if they can't log in in the first place they can't use it anymore; and it probably relies on a dropped binary .so file, which pg_dump will not carry over.


Cannot connect from computer to server database

Have you turned on TCP/IP access in the SQL configuration manager?  It's off by default for security reasons and is the first thing you have to turn on for remote DB access.

You will also need to open port 1433 in the firewall and in the case of a non-default instance, port 1434 as well.  For non-default instances, the SQL Server Browser service must also be running.


How to create a login and user that can only access one schema in one database

Off guess you are missing the connect permission.  I'd always thought that was created with the user but there may be conditions where it isn't added.

Try running

USE [MyDatabase];
GO
GRANT CONNECT TO [MyDomain\MyAccount];

You could also try changing the default schema of [MyDomain\MyAccount]

ALTER USER [MyDomain\MyAccount] WITH DEFAULT_SCHEMA = MySchema;

Lastly you can use my sp_dbpermissions and sp_srvpermissions to review the permissions for the user.

EXEC sp_srvpermissions 'MyDomain\MyAccount'; -- Return instance level permissions
EXEC sp_dbpermissions 'All','MyDomain\MyAccount'; -- Return DB level permissions for all databases


Ola Hallengren Index Script not Reindexing

Ah!  It's the image column.

[column11] [image] NULL,

Online only works on tables without blobs.

Guidelines for Performing Online Index Operations


Implementing versioning system with MySQL

Analyzing the scenario —which presents characteristics associated with the subject known as temporal databases— from a conceptual perspective, one can determine that: (a) a “present” Blog Story Version and (b) a “past” Blog Story Version, although very resembling, are entities of different types.

In addition to that, when working at the logical level of abstraction, facts (represented by rows) of distinct kinds must be retained in distinct tables. In the case under consideration, even when quite similar, (i) facts about “present” Versions are different from (ii) facts about “past” Versions.

Therefore I recommend managing the situation by means of two tables:

one dedicated exclusively for the “current” or “present” Versions of the Blog Stories, and
one that is separate, but also linked with the other, for all the “previous” or “past” Versions;

each with (1) a slightly distinct number of columns and (2) a different group of constraints.

Back to the conceptual layer, I consider that —in your business environment— Author and Editor are notions that can be delineated as Roles that can be played by a User, and these important aspects depend on data derivation (via logical-level manipulation operations) and interpretation (carried out by the Blog Stories readers and writers, at the external level of the computerized information system, with the assistance of one or more application programs).

I will detail all these factors and other relevant points as follows.

Business rules

According to my understanding of your requirements, the following business rules formulations (put together in terms of the relevant entity types and their kinds of interrelationships) are specially helpful in establishing the corresponding conceptual schema:

A User writes zero-one-or-many BlogStories
A BlogStory holds zero-one-or-many BlogStoryVersions
A User wrote zero-one-or-many BlogStoryVersions

Expository IDEF1X diagram

Consequently, in order to expound my suggestion by virtue of a graphical device, I have created a sample IDEF1Xa diagram that is derived from the business rules formulated above and other features that seem pertinent. It is shown in Figure 1:

Why are BlogStory and BlogStoryVersion conceptualized as two different entity types?

Because:

A BlogStoryVersion instance (i.e., a “past” one) always holds a value for an UpdatedDateTime property, while a BlogStory occurrence (i.e., a “present” one) never holds it.
Besides, the entities of those types are uniquely identified by the values of two distinct sets of properties: BlogStoryNumber (in the case of the BlogStory occurrences), and BlogStoryNumber plus CreatedDateTime (in the case of the BlogStoryVersion instances).

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is based on the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on the Entity-Relationship view of data, developed by Dr. P. P. Chen; and also on the Logical Database Design Technique, created by Robert G. Brown.

Illustrative logical SQL-DDL layout

Then, based on the conceptual analysis previously presented, I declared the logical-level design below:

-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also you should make accurate tests to define the most
-- convenient index strategies at the physical level.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATETIME NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    UserName        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        BirthDate,
        GenderCode
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (UserName) -- ALTERNATE KEY.
);

CREATE TABLE BlogStory (
    BlogStoryNumber INT      NOT NULL,
    Title           CHAR(60) NOT NULL,
    Content         TEXT     NOT NULL,
    CoverImageName  CHAR(30) NOT NULL,
    IsActive        BIT(1)   NOT NULL,
    AuthorId        INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT BlogStory_PK              PRIMARY KEY (BlogStoryNumber),
    CONSTRAINT BlogStory_AK              UNIQUE      (Title), -- ALTERNATE KEY.
    CONSTRAINT BlogStoryToUserProfile_FK FOREIGN KEY (AuthorId)
        REFERENCES UserProfile (UserId)
);

CREATE TABLE BlogStoryVersion  (
    BlogStoryNumber INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    Title           CHAR(60) NOT NULL,
    Content         TEXT     NOT NULL,
    CoverImageName  CHAR(30) NOT NULL,
    IsActive        BIT(1)   NOT NULL,
    AuthorId        INT      NOT NULL,
    UpdatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT BlogStoryVersion_PK              PRIMARY KEY (BlogStoryNumber, CreatedDateTime), -- Composite PK.
    CONSTRAINT BlogStoryVersionToBlogStory_FK   FOREIGN KEY (BlogStoryNumber)
        REFERENCES BlogStory (BlogStoryNumber),
    CONSTRAINT BlogStoryVersionToUserProfile_FK FOREIGN KEY (AuthorId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT DatesSuccession_CK               CHECK       (UpdatedDateTime > CreatedDateTime) --Let us hope that MySQL will finally enforce CHECK constraints in a near future version.
);

Tested in this SQL Fiddle that runs on MySQL 5.6.

The BlogStory table

As you can see in the demo design, I have defined the BlogStory PRIMARY KEY (PK for brevity) column with the INT datatype. In this regard, you may like to fix a built-in automatic process that generates and assigns a numeric value for such a column in every row insertion. If you do not mind leaving gaps occasionally in this set of values, then you can employ the AUTO_INCREMENT attribute, commonly used in MySQL environments.

When entering all your individual BlogStory.CreatedDateTime data points, you can utilize the NOW() function, which returns the Date and Time values that are current in the database server at the exact INSERT operation instant. To me, this practice is decidedly more suitable and less prone to errors than the use of external routines.

Provided that, as discussed in (now-removed) comments, you want to avoid the possibility of maintaining BlogStory.Title duplicate values, you have to set up a UNIQUE constraint for this column. Due to the fact that a given Title may be shared by several (or even all of the) “past” BlogStoryVersions, then a UNIQUE constraint should not be established for the BlogStoryVersion.Title column.

I included the BlogStory.IsActive column of type BIT(1) (though a TINYINT may as well be used) in case you need to provide “soft” or “logical” DELETE functionality.

Details about the BlogStoryVersion table

On the other hand, the PK of the BlogStoryVersion table is composed of (a) BlogStoryNumber and (b) a column named CreatedDateTime that, of course, marks the precise instant in which a BlogStory row underwent an INSERT.

BlogStoryVersion.BlogStoryNumber, besides being part of the PK, is also constrained as a FOREIGN KEY (FK) that references BlogStory.BlogStoryNumber, a configuration that enforces referential integrity between the rows of these two tables. In this respect, implementing an automatic generation of a BlogStoryVersion.BlogStoryNumber is not necessary because, being set as a FK, the values INSERTed into this column must be “drawn from” the ones already enclosed in the related BlogStory.BlogStoryNumber counterpart.

The BlogStoryVersion.UpdatedDateTime column should retain, as expected, the point in time when a BlogStory row was modified and, as a consequence, added to the BlogStoryVersion table. Hence, you can use the NOW() function in this situation too.

The Interval comprehended between BlogStoryVersion.CreatedDateTime and BlogStoryVersion.UpdatedDateTime expresses the entire Period during which a BlogStory row was “present” or “current”.

Considerations for a Version column

It can be useful to think of BlogStoryVersion.CreatedDateTime as the column that holds the value that represents a particular “past” Version of a BlogStory. I deem this much more beneficial than a VersionId or VersionCode, since it is user-friendlier in the sense that people tend to be more familiar with time concepts. For instance, the blog authors or readers could refer to a BlogStoryVersion in a fashion similar to the following:

“I want to see the specific Version of the BlogStory identified by Number 1750 that was Created on 26 August 2015 at 9:30”.

The Author and Editor Roles: Data derivation and interpretation

With this approach, you can easily distinguish who holds the “original” AuthorId of a concrete BlogStory SELECTing the “earliest” Version of a certain BlogStoryId FROM the BlogStoryVersion table by virtue of applying the MIN() function to BlogStoryVersion.CreatedDateTime.

In this way, each BlogStoryVersion.AuthorId value contained in all the “later” or “succeeding” Versions rows indicate, naturally, the Author identifier of the respective Version at hand, but one can also say that such a value is, at the same time, denoting the Role played by the involved User as Editor of the “original” Version of  a BlogStory.

Yes, a given AuthorId value may be shared by multiple BlogStoryVersion rows, but this is actually a piece of information that tells something very significant about each Version, so the repetition of said datum is not a problem.

The format of DATETIME columns

As for the DATETIME data type, yes, you are right, “MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format”, but you can confidently enter the pertinent data in this manner, and when you have to perform a query you just have to make use of the built-in DATE and TIME functions in order to, among other things, show the concerning values in the appropriate format for your users. Or you could certainly carry out this kind of data formatting via your application programms(s) code.

Implications of BlogStory UPDATE operations

Every time that a BlogStory row suffers an UPDATE, you must ensure that the corresponding values that were “present” until the modification took place are then INSERTed into the BlogStoryVersion table. Thus, I highly suggest fulfilling these operations within a single ACID TRANSACTION to guarantee that they are treated as an indivisible Unit of Work. You may as well employ TRIGGERS, but they tend to make things untidy, so to speak.

Introducing a VersionId or VersionCode column

If you opt (because of business circumstances or personal preference) to incorporate a BlogStory.VersionId or BlogStory.VersionCode column to distinguish the BlogStoryVersions, you should ponder the following possibilities:

A VersionCode could be required to be UNIQUE in (i) the whole BlogStory table and also in (ii) BlogStoryVersion.

Therefore, you have to implement a carefully tested and totally reliable method in order to generate and assign each Code value.
Maybe, the VersionCode values could be repeated in different BlogStory rows, but never duplicated along with the same BlogStoryNumber. E.g., you could have:

a BlogStoryNumber 3 - Version 83o7c5c and, simultaneously,
a BlogStoryNumber 86 - Version 83o7c5c and
a BlogStoryNumber 958 - Version 83o7c5c.

The later possibility opens another alternative:

Keeping a VersionNumber for the BlogStories, so there could be:

BlogStoryNumber 23 - Versions 1, 2, 3…;
BlogStoryNumber 650 - Versions 1, 2, 3…;
BlogStoryNumber 2254 - Versions 1, 2, 3…;
etc.

Holding “original” and “subsequent” versions in a single table

Although maintaining all the BlogStoryVersions in the same individual base table is possible, I suggest not to do it because you would be mixing two distinct (conceptual) types of facts, which thus has undesirable side-effects on

data constraints and manipulation (at the logical level), along with
the related processing and storage (at the physical tier).

But, on condition that you choose to follow that course of action, you can still take advantage of many of the ideas detailed above, e.g.:

a composite PK consisting of an INT column (BlogStoryNumber) and a DATETIME column (CreatedDateTime);
the usage of server functions in order to optimize the pertinent processes, and
the Author and Editor derivable Roles.

Seeing that, by proceeding with such an approach, a BlogStoryNumber value will be duplicated as soon as “newer” Versions are added, an option that and that you could evaluate (which is very alike to those mentioned in the previous section) is establishing a BlogStory PK composed of the columns BlogStoryNumber and VersionCode, in this manner you would be able to uniquely identify each Version of a BlogStory. And you can try with a combination of BlogStoryNumber and VersionNumber too.

Similar scenario

You may find my answer to this question of help, since I as well propose enabling temporal capabilities in the concerning database to deal with a comparable scenario.


Defining multiple fields in a select string with hard set length values (SQL 2000)

First - note that performing detailed formatting in SQL is often frowned upon, and "should be left to the calling application". Such recommendations, of course, ignore situations like this where there is no calling application.

I don't see an issue with the fundamental methodology seen in the query you provided. In a situation like this, it's wise to use application-specific knowledge. For variable-length values that you're willing to truncate, you need to figure out what length covers most of the data, and provides enough of that column to make out what it is. For values that must be provided as is (your numeric data), you need a length will cover the maximum possible value for the field. And, that may be different for different columns: a quantity column in a table to hold data on cars at a dealership can probably be smaller than a "total value" column in the same table.

If the hard-coded values you're using need to be used on a number of lines, you can put them into variables.

I would recommend making sure that you have "gutters" between your columns - 1-3 spaces between each column helps keep them distinct and easily readable. I'm pretty sure the "LD-MOLDINLD-" lines in your sample output are run together due to the lack of a gutter.

Finally, you might want to flip the padding on the numeric columns; if you change:

LEFT(CONVERT(varchar(7), ordered) + '       ', 7)

to:

RIGHT('       ' + CONVERT(varchar(7), ordered), 7)

then the numbers will be right-aligned, which is far more typical for numbers.

All that said, none of this works well if your carefully spaces output isn't displayed using a mono-space font.

Looking at "MACHINE", "SHIPPING", and "MOLDINLD", I suspect the output you've provided a screen shot of is not using a mono-spaced font. Note that "MACHINE" (7 letters) and "SHIPPING" (8 letters) seem like they line up on their right side (or are off by 1 pixel).

Not knowing your email program, I can't be sure of the best way to convince it to display this data that way. For HTML output, try enclosing the entire output block in
 tags (with appropriate closing
 tags at the end. As, in fact, is done on this very page at this time (right-click, select "View source", and scroll a ways to see).

You can confirm this is correct by copying the output you took a screenshot of, and pasting it into a text editor set to use a mono-space font.


How can I only UPDATE if the user was created?

Taking into account that you need to check the user for a specific database,
you could do something like this?

SET @COMMAND =
'USE '+@DATABASE+'
 SELECT name FROM sys.sysusers WHERE name = '''+@LOGIN_ACESSO+''''
DECLARE @t TABLE (LOGIN_ACESSO NVARCHAR(255))
INSERT INTO @t
EXECUTE  (@COMMAND)
IF exists (select LOGIN_ACESSO from @t)
BEGIN
    UPDATE TBL_LOGINPERMISSAO SET CRIADO =1 WHERE [ID]=@ID
END


ON DELETE CASCADE not deleting entry for foreign key

Aftee searching the Ideone FAQ, it seems that it uses Sqlite3 for the SQL language.
Sqlite needs PRAGMA foreign_keys = ON; to enable foreign keys checks. As they say in their docs: SQLite Foreign Key Support :

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:
sqlite> PRAGMA foreign_keys = ON;

Tested at: Ideone


Postgres Aurora RDS password auth fails except with master user

Even tho PG is case sensitive in other cases, it requires lowercase usernames. Using a lower case name during login fixed the issue.


Fragmentation is often called bloat in PostgreSQL. It relates to its implementation of MVCC where rows are not updated in place or directly deleted, but are copied with a different ID. Those rows are then made visible or invisible depending on the transaction looking at the data. You can start looking at the wiki Show database bloat for more information on fragmentation issues. Bearing in mind that this query will not give you exact numbers, it's a useful tool to have:

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
      (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+COUNT(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::NUMERIC) AS bs,
          CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;

Once you know where bloat is happening, you can take measures to clean the table. The operation is called VACUUMing and can be launched on demand. It is also usually done in the background with the autovacuum worker, so please do not disable it.

As noted in https://www.postgresql.org/support/versioning/, PostgreSQL 9.1 has been unsupported for one year, consider upgrading.


MySQL - How can I speed up a 50+GB fixed width text file import for a 1000+ column table? (Can't remove columns)

MyISAM is probably as fast as InnoDB for this action.

I suggest you minimize the work during the LOAD and plan on doing TRIM, etc, after the data is loaded.  That is, load the data into a staging table; then perform various conversions as you copy the data to the real table; then drop the staging table.

This 'staging' can also give you an easy way of splitting up the columns into tables.  Or consolidating the columns into a single JSON string, or something else.

If you provide more details on the data and the transformations and the 1000+, I can be more specific.

More

Oh! Sounds like Punched cards.  Or mag tape..  I would

Read the text into a single column declared as TEXT.
Probably I would also have another column: id INT UNSIGNED AUTO_INCREMENT NOT NULL.
Meanwhile, I would generate SUBSTR(t, 12, 23) clauses based on what the split up needs to be.
And decide how to build the schema.  Are these medical records?  Animal characteristics?  Quiz results?  What?  Census data?
Are some of the "columns" repeated a lot?  For example for census data, I might expect the first two columns to be "state" and have about 51 distinct 2-character values, then the next ~20 columns be the county, with about 3000 distinct values, etc.  If so, then that definitely suggests a structure (states contain counties contain cities ...)
After deciding on the several tables to spread the data across, build SELECT queries to pull the data out and put them into the other tables.

Continuing with the example:

CREATE TABLE Cities (
    id MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
    state CHAR(2) NOT NULL,
    county VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    PRIMARY KEY(id),
    INDEX(state, city)
) ENGINE=InnoDB  CHARACTER SET ascii;

INSERT INTO Cities (state, county, city)
    SELECT DISTINCT
            SUBSTR(t, 1, 2) AS state,
            SUBSTR(t, 3, 20) AS county,
            SUBSTR(t, 23, 20) AS city
        FROM Raw;

After that, it gets trickier, because you want to use cities.id in whatever table comes next, rather than repeating the state+county+city again and again.

Get the idea?  Want to share the type of data you have?

Sure it will take hours, probably even days.  But the structured result will be better than a nearly useless regurgitation of the raw data.

Also, think about how you want to eventually query (SELECT) the data.


How to give a client backups of their data without compromising our database design?

Dumping the data into CSV would be the best way. This way, the data is "most usable" to the client - as opposed to an XML file or even an SQL dump. They could conceivably take that data and use it in another system, for example.

If the data is going to be backed up by the client with the intention of having you (or indeed another party) "restore" or otherwise import that data at a later date, your data dump will need to include Primary and Foreign Keys so you can match the data up again.

For example, you might export a "Category" CSV file which contains CategoryID and CategoryName, a "Customer" CSV file which contains CustomerID, Name, Email etc and a CustomerCategory CSV file which simply contains a list of CustomerID and CategoryID values. You would then be able to use the three CSV files to rebuild the client's Customer and Category tables if required.

You can export the data using outfile as in the following example:

select col1,col2 into outfile '/path/file1.(txt/sql/csv)' fields
terminated by ',' lines terminated by '\n' from tab ;


Simple Database Design - Many to many? - First database (newbie)

Why have product_inventory at all?

inventory

prod_code (foreign key of products.code) | warehouse_code (FK of warehouse.code) | >inventory_count


Altering table on SQL Server 2012 AON High Availability Databases

It turns out that changes in table structure (schema) is automatically handled, so changes in the primary database should be also reflected in the secondary ones.

  Schema changes and data changes are essentially the same. It works like traditional mirroring today : what happened in the log on the primary happens on the secondary. Not everything that happens in Vegas has to stay in Vegas. :-)

Source: Do schema changes "break" SQL Server 2012 AlwaysOn or are they handled transparently?


SELECT *
FROM account_logs
WHERE ID IN (
  SELECT MAX(ID)
  FROM account_logs
  WHERE customer_id = 1061
  GROUP BY DATE )
ORDER BY DATE ASC ;

The sub-query should return all the ID's that you are interested in.


Check the wait events in pg_stat_activity to see if your query is running or hanging.

In the latter case, examine pg_locks or use the pg_blocking_pids function to see which session blocks you.

If no locks are involved, see if the backend process consumes CPU or not. Optionally use strace to see what the backend is doing.


Where can I find the connection string for a DB2 database?

Not sure where you got those connection strings. According to the documentation, the provider you use needs three keywords: DSN, UID, and PWD. DSN refers to the Windows data source, which you define as usual after installing the DB2 Data Server Client software.


What is the use of restore headeronly when performing a backup?

I notice that before the backup, SQL Server perform a restore headeronly then the backup.

This might be part of restore steps that are performed internally by sql server.

You can look into the restore phases using DBCC TRACEON(3604, 3605, 3004);. Use it only for educational purpose on a NON PROD server.

As you can see above that there are essentially 3 main phases of restore - data copy, undo and redo phase along with other sub-phases where in it opens and loads the backup set as well.

  It appears to take longer than the actual backup.

To improve restore time, you should

Enable Instant file initialization.
Use T-SQL and restore with REPLACE for an existing database instead of dropping and restoring.
Make sure you do proper maintenance of msdb.
Check for VLFs. The more VLFs, the longer restore time.

Refer to Babysitting a slow backup or restore by @AaronBertrand.


Where to put login.sql in Windows environment to configure SQL Plus?

You need to set the environment variable SQLPATH (through the Control Panel) to tell SQL*Plus where to find the file:

SQLPATH=C:\users\myname

Setting environment variables is explained here: http://support.microsoft.com/kb/310519/en-us


It looks like there was a bug in a previous patch that messed up the version number.  Replace the 3 with 00 and you will find the correct info.  It should be Cumulative update package 16 for SQL Server 2008 Service Pack 3


Update record column with id found in the table of an associated model

Use the FROM clause in the UPDATE. Like:

UPDATE purposes p
SET    user_id = j.user_id
FROM   journeys j
WHERE  j.purpose_id = p.id
AND    p.owner_id IS NULL
AND    j.user_id IS NOT NULL;  --  may be redundant

Ideally, you have some kind of unique index on journeys(purpose_id) to make this unambiguous. Else multiple rows might qualify.

The last predicate AND j.user_id IS NOT NULL is redundant if the column is defined NOT NULL.


Why can't rows inserted in a CTE be updated in the same statement?

All sub-statements of a query with CTEs happen virtually at the same time. I.e., they are based on the same snapshot of the database.
The UPDATE sees the same state of the underlying table as the INSERT, which means the row with val = 1 is not there, yet. The manual clarifies here:

All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.

Each statement can see what's returned by another CTE in the RETURNING clause. But the underlying tables look all the same to them.
You would need two statements (in a single transaction) for what you are trying to do. The given example should really just be a single INSERT to begin with, but that may be due to the simplified example.


Make a database inaccessible through "sa" user but should be accessible through another user

There is no way to prevent the "sa" login from accessing every database on the server.  If the "sa" login isn't needed (which it shouldn't ever be needed) then disable it.  If it is needed, then there's nothing you can do as anything that you put in place to block it, the "sa" login can remove.