Will SQL Server "int" datatype reliably truncate (and not round) decimal values when they are input?

The Microsoft Docs page for the float and real data types, says this:

Values of float are truncated when they are converted to any integer type.

In addition the CAST and CONVERT (Transact-SQL) page says:

When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.

I'd prefer to get the source software modified to not return floating point data, but short of that, if the data is not used by some process that would be life-threatening, then it looks like an implicit conversion to int should be safe.
I qualified it with "non-life-threatening" because it's possible there is some situation where the implicit conversion does not operate the way it is documented.  Take for instance, the details presented in the "Caution" section of the int Docs page.


Is there a free software that can import excel into mysql on mac?

Full disclosure.. I am the author of the following program.
Give it a try and let me know what you think.
I have tried many applications including the excel add-in from MySQL, but was not satisfied with them, so I wrote my own.

The current version of my program is slow (just like the MySQL add-in), doesn't support international characters, and doesn't support command line parameters, but it is free and works well.

http://panofish.net/excel2mysql/

I am working on a much better version which will remedy the aforementioned shortcomings and should be available soon.


Find the greatest difference between each unique record with different timestamps

WITH
cte1 AS ( SELECT DISTINCT created_at
          FROM t
          ORDER BY 1 DESC LIMIT 2 ),
cte2 AS ( SELECT src.*,
                 LEAD(src.followers) OVER (PARTITION BY src.id
                                           ORDER BY src.created_at DESC) prev_followers,
                 src.followers - LEAD(src.followers) OVER (PARTITION BY src.id
                                                           ORDER BY src.created_at DESC) gain
          FROM t src
          JOIN cte1 ON src.created_at = cte1.created_at )
SELECT *
FROM cte2
WHERE gain IS NOT NULL
ORDER BY gain DESC LIMIT 1;

fiddle

whenever there are two records with the same difference margin..It always shows one instead of two.

WITH
cte1 AS ( SELECT DISTINCT created_at
          FROM t
          ORDER BY 1 DESC LIMIT 2 ),
cte2 AS ( SELECT src.*,
                 LEAD(src.followers) OVER (PARTITION BY src.id
                                           ORDER BY src.created_at DESC) prev_followers,
                 src.followers - LEAD(src.followers) OVER (PARTITION BY src.id
                                                           ORDER BY src.created_at DESC) gain
          FROM t src
          JOIN cte1 ON src.created_at = cte1.created_at ),
cte3 AS ( SELECT *, RANK() OVER (ORDER BY gain DESC) rnk
          FROM cte2
          WHERE gain IS NOT NULL )
SELECT *
FROM cte3
WHERE rnk = 1;

fiddle


The media family on device incorrectly formed for read only file group restore

I think you have encountered a problem with disk sector size, the old (very old) standard was 512 bytes per sector and the new one is 4kb. When you restore a database between these 2 standards the restore re-writes the data to 4kb sectors but with a read-only filegroup my hypothesis is that this would not be allowed. There is a basic discussion of the change here :-
https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-new-drives-use-4k-sector-size/ba-p/316277


Can i use a custom function with UpdateOne or UpdateMany in MongoDB Shell?

The only way i could find was this, and i ask it in many places, no one answered...:

db.humanResource.user.find({
    ProfilePictures: {
        $exists: true
    },
    ProfilePictures: {
        $ne: null
    }
}).forEach(function (e, i) {
    for(var i=0; i< e.ProfilePictures.length; i++){
         e.ProfilePictures[i].CloudinarySecureUrl = updateHttpToHttps(e.ProfilePictures[i].CloudinaryUrl);
         e.ProfilePictures[i].CloudinaryUrl = updateHttpsToHttp(e.ProfilePictures[i].CloudinaryUrl);
    }
    db.humanResource.user.save(e);
})

Which is based on the answer of this thread:
We can use forEach to decrease load on database and Ram Usage, while updating each record, without loading them all, and then we save that document of collection.

And i could use Print ans JSON stringy as a helping reference before destroying my database:

JSON.stringify is usable in MongoShell with BSON

Print Can be used to print into Mongo Shell

also there was a printjson in the second link, which i didn't notice, at first, i just noticed now.


How do I move or redistribute FILESTREAM files between containers?

A good option for you, I would imagine, is to split table logically, so every section would be in its own folder and have under 300k files(=records).

In SQL Server logical table split is usually done with Partitioning (this is an Enterprise feature). Partitioning essentially maps sections of table (partitions) to physical storage (filegroups). Various partitions can be assigned to different filegroups. FILESTREAM is essentially a property of a filegroup.

Design

This makes up the spine of your solution:

Partitions reside on different filegroups
Each filegroup is a separate FILESTREEAM folder/container
Size of each partition under 300k records

Here is how to partition a table with FILESTREAM column.

Main advantage:  you will be able to manage your FILESTREAM data per Partition at a time, not whole table.

You'll need:

ID that would allow to track size of a partition or at least not allow random writes across partitions

[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newsequentialid())

Maintenance job, that checks how full last partition is, creating new filegroups and partitions in advance, rebuilding indexes per partition etc.
To cater for Insert operations make you partitions smaller than 300k, so index is rebuild one partition at a time.

This design is for mostly read-only table with inserts happening at the end of it. Do consider writes and updates and impact of partitioning on them, if you have not done so already.

Migration

In order to make a table partitioned, you need to build an index. There 2 main ways:

Rebuild index for the whole table. It will requires x2-x3 times more space than the table. It is not suitable for big tables for a number of reasons.
Migrate data into new table, which is already partitioned.

My preferred method for this:

Create a table of the same structure as target partitioned table.
Insert data using DELETE... OUTPUT DELETED ... INTO <intermediary table> for data that fits 1 partition.
If number of rows is too big for one transaction, then wrap this into a loop
Create index on Partitioned scheme
Switch partition to Target table
Drop index on Intermediary table
Wrap in a loop and repeat for all partitions/data

Inserts

Newly inserted rows ideally should go into last partition. However, there is a catch about NEWSEQUENTIOALID() function:

  Creates a GUID that is greater than any GUID previously generated by
  this function on a specified computer since Windows was started. After
  restarting Windows, the GUID can start again from a lower range, but
  is still globally unique.

It means that new writes will go into last partition only until first reboot of the server. And this will eventually happen. New rows will be inserted in the middle of the table. But remember we have partitions now? Only one partition at a time will be affected. Still it will exceed 300k rows at some point in time.

Main option here is to split partition. Existing partition will split in 2, where right part will form a new partition. New partition should go onto new filegroup. It will not be a metadata only operation: files will be physically copied into another partition.

A work around would be moving (delete and insert rows into Intermediary table) rows on same PS; splitting partition and moving rows back. But it involves moving data once anyway. If intermediary table in on same PS, then split will involve moving data. If not, then data needs to be moved second time back into main table.

Multiple FILESTREAM containers

Multiple FILESTRAM containers are a feature of Enterprise version. A container is often referred to as a "folder" in FILESTREAM container. Actually it is a database file in a filegroup of FILESTREAM type.

It does help to stay under 300k files per NTFS folder, but it is very difficult to manage stored files across these folders explicitly.

Article Rebalancing data across files in a filegroup from 2011 by Paul Randall gives a hint on use of files in a filegroup:

  [SQL Server] also uses an algorithm called proportional fill that
  aims to allocate data from the files according to how much free space
  they have relative to the other files in the filegroup.

  ...

  This means that if you add a new data file to a filegroup that has mostly full
  data files, the proportional fill weightings will be such that the new
  file will be the one where allocations come from until it fills up to
  the same level as the older files. The new file in essence becomes an
  allocation hot spot.

SQL Server will be filling newly added files first until they fill up to the same %age level as others.

It may look like an easier alternative to Partitioning, however it is not:

You cannot do anything about files already stored in existing containers.
You still have one big table that you need to manage within single transaction

At the same time you can blend multiple FILESTREAM containers into Partitioning solution to make folders smaller and faster.

UPDATE for Standard Edition (SE)

SE does not have Partitioning nor Multiple FILESTREAM containers.
It means we can have only one partition and one container per table.

One option will be to have a number of tables with a UNION ALL view on top.

Number of tables will fixed for most of the time. You already have quite a number of documents, so you can look at distribution of GUIDs and creates tables for smaller intervals for higher density at the lower end.
Create a view that will UNION ALL all tables. Hint: in order to avoid scans of all tables add a WHERE clause for IDs belonging to that particular table. it will help Optimizer to exclude tables there a particular does not exist:

    Select * from Table1 where ID between 1 and 100
    UNION ALL
    Select * from Table2 where ID between 101 and 10000
    UNION ALL
    Select * from Table3 where ID between 10001 and 100000000

Make sure that you cover all possible values of GUID from 0000-...-0000 to FFFF-...-FFFF
 - This view will updatable, so you will be able to insert data into relevant table by using this view. There are imitations to this: Modifying Data Through a View.
 - You need to copy all data physically from existing table to new tables. it can be achieved via the master view.

P.S.
SQL Server is a RDMS first of all. Features to manage huge volumes of unstructured data like VARBINARY(MAX) are add-ons and relatively new.
Therefore there are other limitations that need to be taken into account:

Max number of partitions per table:       15 000
Max files groups OR files per database:   32 767
Max files per container:                 300 000
(=filegroup in my example, =database file in case of multiple containers per filegroup)

Size of transaction log is a limitation for one transaction as well as time that a transaction takes to complete. You need to keep individual operations manageable and possible to roll back in reasonable time.


Should PgBouncer be installed on web application server or database server?

I would recommend first installing it on the database server.  That way you can run top (or whatever) on that machine and have a one-stop-shop to see what is going on.  Also, the dependencies are more likely to already be installed on the database server than they are on the application server.

If you then notice the database server is more heavily loaded than the app server, you can move it to the app server.  But looking at the current loads (when you don't have connection pooling in place) to make that decision isn't going to be terribly useful, because they could change dramatically once you start pooling.


How do you restore to a point in time between the last log backup before a full backup and the full backup?

how would you restore to a point in time between T3 and T4?

You should restore this way:

T0 = full backup
T1 = log backup
T2 = log backup
T3 = log backup
T5 = log backup

I did not understand what you said here:

  My understanding is that the full backup at T4 should lead the log
  file to mark transactions between T0 and T4 as inactive, so in theory,
  the transaction log may no longer contain transactions from the time
  before T4.

But this one:

  the log backup at T5 will only contain transaction information from
  the time after T4.

is INCORRECT.

Every log backup starts from LSN where previous log backup stops, so it doesn't matter if there was or was not any full backup between T3 and T5.

You could take a "shortcut" by restoring only T4 and T5 in case the time you are interested in was after T4, but it's not your case, so you should restore all the log backups until the last one that covers the time you are interested in, starting from full backup that precedes the time you are interested.

P.S. May be it can be useful to you to know that the only thing that can "truncate" the log in full recovery model is log backup. Full backup has nothing to do with "marking transactions inactive".

This article A SQL Server DBA myth a day: (30/30) backup myths may also help.


Query with "Logical operator (AND)" that does not return the desired result set

As Mustaccio mentioned in the comments, you want rows where dev_type is either 'x1' or 'x2', not where it's 'x1' and 'x2'.
Also you only need the brackets around each of the alternative sets of conditions. The others aren't doing anything and they are just confusing.
with test_table (customer_num, dep_group, dep_type, balance) as
     ( select 1, 'x', 'x1', 10 from dual union all
       select 1, 'x', 'x2', 10 from dual
     )
select *
from  test_table
where (dep_group = 'x' and dep_type = 'x1' and balance > 1)
or    (dep_group = 'x' and dep_type = 'x2' and balance > 1)


What are the different ways to Warmup InnoDB Buffer Pool in MySQL 5.6?

Assuming, you have a 7200 rpm HDD, if you perform random reads and expect the data to be loaded whenever it is required by the application, it will slow down the whole process. Random reads are way slower than sequential reads in case of HDD. So, rather the records must be loaded in sequential access.

It may take a while under a regular workload to store your entire working set data into the buffer pool, after restarting MySQL for instance. You can go with one of these two options:

Manual Warmup - Running SELECT queries against your InnoDB tables will load necessary pages into the memory, i.e., the buffer pool. SELECT COUNT(*) may be very useful, as it will load the whole clustered index into memory (actually as much as available). Secondary indexes may be loaded into the memory with simple queries, for instance by adding a catch-all, i.e., <> 0 WHERE clause on the first column of an index. Using a given index could be forced if needed.
Automatic Warmup - From 5.6, automatic buffer pool content dump at shutdown and restore on startup can be enabled - innodb_buffer_pool_dump_at_shutdown = ON, innodb_buffer_pool_load_at_startup = ON.

Reference - From Dual's article on InnoDB Warmup

Marcelo Altmann's article on MySQL Warmup


How do I initialize transactional replication from a backup at a URL?

This is a very good question but at the moment, initializing a subscription from a backup stored in Windows Azure Blob storage is not currently supported. I believe this would be a very cool feature for replication and I'm sure the folks at Microsoft would agree. I suggest proposing this idea on UserVoice.

However, since you've already restored the backup, and the schema and data are already in place at the subscriber, try executing sp_addsubscription with the @sync_type of 'replication support only' and see if that meets your needs.

More details for initializing a subscription manually can be found in How to: Initialize a Subscription Manually.


Group by minute and two minute intervals

First, a table and some sample data to play with:

USE tempdb;
GO
CREATE TABLE dbo.SomeTable(dt DATETIME);
GO
SET NOCOUNT ON;
GO
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -22, GETDATE());
GO 45
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -19, GETDATE());
GO 32
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -17, GETDATE());
GO 21
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -12, GETDATE());
GO 16
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -5, GETDATE());
GO 55
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -2, GETDATE());
GO 26
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -1, GETDATE());
GO 71
INSERT dbo.SomeTable(dt) SELECT GETDATE();
GO 14

(I'd do this in sqlfiddle but I'm not sure it supports GO <int> to have many rows, and it chokes on INSERT > 8000 characters.)

Now a stored procedure:

CREATE PROCEDURE dbo.GetGroupedIntervals
    @MinuteInterval TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @IntervalCount INT, @StartDate SMALLDATETIME;

    SELECT
        @StartDate = DATEADD(MINUTE, -1, MIN(dt)),
        @IntervalCount = (DATEDIFF(MINUTE, MIN(dt), MAX(dt))
            + @MinuteInterval) / @MinuteInterval
          FROM dbo.SomeTable -- WHERE ...;

    ;WITH dates(s,e) AS
    (
        SELECT
            DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
            DATEADD(MINUTE, @MinuteInterval*(n.n), @StartDate)
        FROM
        (
          SELECT
            TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
            FROM sys.all_objects AS o CROSS JOIN sys.all_columns AS c
            ORDER BY o.[object_id]
        ) AS n(n)
    )
    SELECT StartDate = d.s, c = COUNT(s.dt)
    FROM dates AS d
    LEFT OUTER JOIN dbo.SomeTable AS s
        ON s.dt >= d.s AND s.dt < d.e
        -- AND any filter criteria for dbo.SomeTable?
    GROUP BY d.s
    ORDER BY d.s;
END
GO

And some sample usage:

EXEC dbo.GetGroupedIntervals @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 5;

For brevity, I'll show the results for the last call, but you can play with the others.

StartDate            c
-------------------  ----
2012-05-16 12:51:00  77
2012-05-16 12:56:00  21
2012-05-16 13:01:00  16
2012-05-16 13:06:00  55
2012-05-16 13:11:00  111

Some notes:

The join using s.dt is likely to perform better than any extraction methods using datepart if the datetime column in your base table has an index (or might in the future).
I assumed you wanted to show all the intervals within the range. If you don't want to show the intervals with 0 counts, just change the left outer join to an inner join.
I go down a minute in case the start date was rounded up when converted to SMALLDATETIME. For the 1 minute interval and possibly others, this can lead to 0 counts for the first interval. You can adjust how this rounding happens (e.g. you can use FLOOR() to make sure it always rounds down). All depends on how accurate you need to be.
I didn't include any WHERE clause but you may need to have those to filter. For example your query may want all the intervals for a given day. You may want to change the calculation of dates to produce all the intervals for the day, rather than all the intervals between the min and max time found in dbo.SomeTable on that day. The following variation takes care of this, by presenting the data for a single day starting from midnight and incrementing by @MinutInterval:

...

CREATE PROCEDURE dbo.GetGroupedIntervalsByDay
    @Date           DATE,
    @MinuteInterval TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @IntervalCount INT,
        @StartDate     SMALLDATETIME = @Date;

    SELECT
        @IntervalCount = 1440 / @MinuteInterval;

    ;WITH dates(s,e) AS
    (
        SELECT
            DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
            DATEADD(MINUTE, @MinuteInterval*(n.n),   @StartDate)
        FROM
        (
          SELECT
            TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
            FROM sys.all_columns AS o
            ORDER BY o.[object_id]
        ) AS n(n)
    )
    SELECT StartDate = d.s, c = COUNT(s.dt)
    FROM dates AS d
    LEFT OUTER JOIN dbo.SomeTable AS s
        ON s.dt >= d.s AND s.dt < d.e
        -- AND any filter criteria for dbo.SomeTable?
    GROUP BY d.s
    ORDER BY d.s;
END
GO

Sample calls:

EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 5;

Abridged results from the last call:

StartDate             c
-------------------   ----
2012-05-16 00:00:00   0
2012-05-16 00:05:00   0
2012-05-16 00:10:00   0
...
2012-05-16 12:40:00   0
2012-05-16 12:45:00   0
2012-05-16 12:50:00   45
2012-05-16 12:55:00   53
2012-05-16 13:00:00   16
2012-05-16 13:05:00   55
2012-05-16 13:10:00   111
2012-05-16 13:15:00   0
2012-05-16 13:20:00   0
...
2012-05-16 23:45:00   0
2012-05-16 23:50:00   0
2012-05-16 23:55:00   0

(Again if you don't want to include the intervals with no counts, change the left outer join to an inner join. You also might find some odd results if you choose an interval that doesn't fit nicely inside of 1440. I'll leave that case as an exercise to the reader.)


Is there a setting in MySQL that will allow you to see the columns of a table on a query, even if the table is empty?

This will always return a single row with NULL in all the columns of the table:

SELECT t.*
FROM (SELECT 1) AS dummy
  LEFT JOIN table_name AS t
    ON FALSE ;

This will always return a single row with NULL in all the columns of the table if the table is empty and real data from one row if it isn't empty:

SELECT t.*
FROM (SELECT 1) AS dummy
  LEFT JOIN table_name AS t
    ON TRUE
LIMIT 1 ;


How to install Slony for 64-bit PostgreSQL 9.3 on Windows?

OK,

I posted a link to my compiled Slony 2.2.4 for Windows 64-bit under postgres 9.3.6. However, it was deleted I'm assuming because people may not trust my compile. Hence, here's the link to the Slony-I site with the instructions that I followed to compile Slony.

It's section 3.1.6. Building on Win32

http://www.slony.info/documentation/2.2/administration.html#BUILDINGWIN32


How to specify column positions in insert statements generated by pg_dump?

If you're using pg_dump you can use the --attribute-inserts flag when dumping the data - it adds column lists to the generated INSERT statements:

postgres=# create table test ( col1 integer, col2 integer, col4 integer, col3 integer ) ;
CREATE TABLE
postgres=# insert into test values(1,2,4,3);
INSERT 0 1
postgres=# \q

postgres@ubuntu-xenial:~$ pg_dump --attribute-inserts postgres
--
-- PostgreSQL database dump
--
-- * OUTPUT SNIPPED *
--
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.test (col1, col2, col4, col3) VALUES (1, 2, 4, 3);

By doing this the order of columns does not matter.


How to do safety mirroring innodb table on mysql?

Plan A:  Speed up the Summary by building and maintaining a Summary Table:  http://mysql.rjweb.org/doc.php/summarytables .  In particular, this is likely to segregate the summary select, which will now touch only the summary table, from the updates.  And it will
Plan B:  Use replication on the same server.  Be sure to have a different "Port" (such as 3307) for that Replica.  Direct summary requests to that port.  Continue to direct writes to the Primary (your current instance).
Plan C:  If the "update" is just a "Likes" or "Visits" counter, then do the following to avoid some of the interference.  Move that column to a separate table.  The two tables would have the same PRIMARY KEY for ease of JOINing.  The update will avoid interfering with any selects that don't need also need to counter.
Plan D:  Plans A,B,C are independent; do a combination of them.
The repeated copying of the table will (I predict) be poor for both maintenance and performance.


Is it possible to create a function in SQL server to act as clause e.g. Where or Limit

No, there are no macros in T-SQL to make that syntax look simpler (and non-standard!) or to perform inline substitution, at least in a native non-dynamic query. You do know that you can parameterize it, though, right?

ORDER BY <need an ORDER BY>
  OFFSET ((@PageNumber-1)*@PageSize) ROWS
  FETCH NEXT (@PageSize) ROWS ONLY;

This way you are always adding the OFFSET clause but you can do it in a very standard way and not really have to look at it during everyday coding.

I suppose you could do what you want if you're okay with building your query using dynamic SQL, for example you could create a function like this:

CREATE FUNCTION dbo.Limit(@pn VARCHAR(12), @ps VARCHAR(12))
RETURNS NVARCHAR(255)
WITH SCHEMABINDING
AS
BEGIN
  RETURN (N' OFFSET ' + @pn + ' ROWS FETCH NEXT ' + @ps + ' ROWS ONLY');
END

Then you could call it like this:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... ORDER BY ...' + dbo.Limit(22,5);

PRINT @sql;

This looks kind of like what you typed in your sample, but I don't think it's what you really meant (or should want), unless you already have other reasons (e.g. wildly varying order by parameters or search filters) to use dynamic SQL anyway... still, this obfuscation and simplification doesn't help anybody learn SQL Server-specific syntax, and makes it harder for those people to write this stuff when they can't use the function. I don't think this is the type of "optimization" you should be spending a whole lot of effort on. You type these queries once, and when someone does learn the actual syntax, it is hard to justify using "shortcuts" that might not be any easier to remember in the long run (and can be harder to maintain if, for example, they need to made more flexible later).


ora-04091 table is mutating trigger/function may not see it

This is normal if you have your FK constraint defined with the ON DELETE CASCADE option. When you delete from tab1, that will cause deletes from tab2 also because of the constraint definition. If you disable the constraint, rows from tab2 will not be deleted, the trigger on tab2 will not even run. Below is an example:

create table t1 (t1_id number, value varchar2(10), constraint pk_t1 primary key (t1_id));
create table t2 (t2_id number, t1_id number, constraint fk_t1 foreign key (t1_id) references t1(t1_id) on delete cascade);

insert into t1 select rownum, 'HELLO' from dual connect by level<= 5;
insert into t2 select rownum, rownum from dual connect by level <= 5;
commit;

create or replace trigger t2_trg
after delete or insert or update
on t2
for each row
declare
  n number;
begin
  select count(*) into n from t1;
end;
/

If you try to delete now, you will get the infamous table is mutating error:

SQL> delete from t1 where t1_id = 1;
delete from t1 where t1_id = 1
            *
ERROR at line 1:
ORA-04091: table BP.T1 is mutating, trigger/function may not see it
ORA-06512: at "BP.T2_TRG", line 4
ORA-04088: error during execution of trigger 'BP.T2_TRG'

Now with the constraint disabled:

SQL> alter table t2 disable constraint fk_t1;

Table altered.

SQL> delete from t1 where t1_id = 1;

1 row deleted.

Obviously, this works.

You should rethink/rewrite your trigger or constraint considering the above.


Mysql: Schema/Query Performance Approach for aggregated mailbox folders

The "Best Way" is probably to do it the former way, calculating the accurate count of messages and friends, etc is going to be easier to manage on the application side. Performance will obviously depend on how many messages and friends, some variables on the database server, and indexes.

A third solution would be to use a caching layer (like memcached) to store the values that aren't going to change that much. Memcache can increment and decrement, which will work perfectly for updating your unread counts. Message counts only get updated when new messages are received, friend counts are only updated when new friends are added (and possibly with the added condition of that friend having to send a message). My point is, here, that the values here don't update that frequently, so memcached is a pretty perfect option for a solution here.

That being said, stressing about the performance of that schema early on before you have factual data on how it's being used and how it's performing definitely falls into the camp of "over optimizing."  Implementing approach #2 or #3 is a trivial add once you have the system in place. Build it first, do what you believe is best, don't over optimize or over engineer. Once it's online, monitor performance, and make fixes as you identify bottlenecks.


Oracle SQL Developer - Database Diff tables & constraints without table data

SQL Developer's Database Diff doesn't compare the data in your tables, only the table definitions.

If you check 'Tables' it by default compare ALL tables.

However, if in the proceeding wizard dialog page, you specify one or more tables, it will ONLY compare those tables.

Your output report will look like this - note there is no mention of how rows may differ between any two tables or views.


How to log into a fresh MariaDB install?

Turned out I had to run mysql_install_db before I was able to do anything - although when trying to log in before that everything seemed to work...


New Database and it has a table `dbo.cmd` already

If your main question is  - why do you already have a table (with data) in a newly created database...?

All newly created Sql Server databases use the MODEL database as a template.  Therefore, I would expect to find dbo.cmd (with the data) already existing in the MODEL database.  Can you verify?

My guess is - someone in the past created dbo.cmd 'accidentally' in the MODEL database.


Need to divide single column into multiple columns

For the first case you can use ROW_NUMBER then PIVOT to do this using the modulo operator to divide into columns and the result of integer divsion by 5 to group into rows.

WITH CTE AS
(
SELECT C,
       (ROW_NUMBER() OVER (ORDER BY C) -1)%5 AS Col,
       (ROW_NUMBER() OVER (ORDER BY C) -1)/5 AS Row
FROM YourTable
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX(C) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row

SQL Fiddle

For the second case Oracle has a straight forward syntax to achieve this with PIVOT. SQL Server doesn't but you can do this as below.

WITH CTE AS
(
SELECT *,
       (ROW_NUMBER() OVER (ORDER BY Column1) -1)%5 AS Col,
       (ROW_NUMBER() OVER (ORDER BY Column1) -1)/5 AS Row
FROM YourTable
)
SELECT MAX(CASE WHEN Col = 0 THEN Column1 END),
       MAX(CASE WHEN Col = 0 THEN Column2 END),
       MAX(CASE WHEN Col = 1 THEN Column1 END),
       MAX(CASE WHEN Col = 1 THEN Column2 END),
       MAX(CASE WHEN Col = 2 THEN Column1 END),
       MAX(CASE WHEN Col = 2 THEN Column2 END),
       MAX(CASE WHEN Col = 3 THEN Column1 END),
       MAX(CASE WHEN Col = 3 THEN Column2 END),
       MAX(CASE WHEN Col = 4 THEN Column1 END),
       MAX(CASE WHEN Col = 4 THEN Column2 END)
FROM CTE
GROUP BY Row
ORDER BY Row

SQL Fiddle


In your connection string, specify max pool size=<your desired max pool size>.  So in other words, if you want to change max pool size to the value 500, your connection string could resemble this:

"data source=your_server; initial catalog=your_db; trusted_connection=true; max pool size=500"

Obviously I'm assuming so much with your other parameters, but this should give you a good idea how to proceed.  Connection pooling is client-side provider enforcement.  It's the client that will need to specify this max pool size setting, via the connection string.

Also, make sure you are closing or disposing of your connections properly, otherwise you will tie up connections.  Something like this (C#):

string connectionString = "data source=your_server; initial catalog=your_db; trusted_connection=true; max pool size=500";

using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    using (SqlCommand sqlCmd = new SqlCommand())
    {
        sqlCmd.Connection = dbConn;
        sqlCmd.CommandText = "select 1;";

        // ... so on and so forth
    }
}

The using block (in C#) calls IDisposable.Dispose() upon completion.  You could also implement SqlConnection.Dispose() or SqlConnection.Close() in the finally block of a try/catch/finally block.

Reference:  MSDN Documentation on SqlConnection.ConnectionString property


Difference between a trigger and a transaction

Comparing TRIGGERs and TRANSACTIONs is like comparing apples and oranges! No, wait, it's not, because apples and oranges are similar - they are both fruit and can both be eaten. It's more like chalk and cheese (English expression)!

Transactions are for grouping actions into one unit of work which either succeeds or fails in its entirety!
Triggers are (normally) for performing (procdural/business logic) code when an event (normally an INSERT, UPDATE or DELETE on a particular table) occurs in the database.

TRANSACTIONs.

These units of work are treated in a coherent and reliable way independent of other transactions.

From Wikipedia:

  To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system
  failure, when  execution stops (completely or partially) and many
  operations upon a database  remain uncompleted, with unclear status.
  To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs'
  outcomes are possibly  erroneous.

Note the use of the word "isolation" - this is one of the criteria for the "ACID" test. ACID in this context corresponds to both a play on words in English and a set of properties of database transactions that are considered highly desirable - these are: Atomicicty, Consistency, Isolation and Durablility.

The classic example of this is in banking. Say you want to pay your credit card bill of €100 by transferring money from your current account. So, it's important for both you and the bank that this "transaction" should occur as one single consistent unit of work. Otherwise, either you (money leaves your current account but doesn't get to the CC account), or the bank (your CC get credited but your current account isn't debited) is out of pocket.

So, in SQL, what you would do is:

BEGIN TRANSACTION;
UPDATE current_account ca SET ca.balance = ca.balance - 100 WHERE ca.cust_name = 'Haram';
UPDATE      CC_account cc SET cc.balance = cc.balance + 100 WHERE cc.cust_name = 'Haram';
COMMIT;  -- definitively perform the work.

If any of the steps fail (e.g. the balance in the current_account is insufficient), all of the work of the transaction will be rolled back - thus ensuring consistency.

Final point - if only it were all that simple! :-) There are (at least) four complicating factors:

1) Different isolation levels. This is beyond the scope of this answer - check out the Wiki and use your search engine of choice - there's loads of material out there, and

2) Different implementations by vendors - you'll have to look at your own system(s)' documentation for this, and

3) There are such things as nested TRANSACTIONs and SAVEPOINTs - again, this is getting beyond the scope of this answer - check your documentation, and

4) Some database engines support Transactional DDL (Data Definition Language) and some (notably MySQL) don't.

TRIGGERs.

A database TRIGGER on the other hand is a different animal altogether! A TRIGGERis an action (coded by the developer) which occurs in response to events on a table (again, there are complications which I won't go into here - check the Wiki and/or use your search engine). In the vast majority of cases, the event in question is an INSERT, an UPDATE or a DELETE on a table.

A rather nice analogy can be found here. An action on a database is compared to a row of dominoes - you knock over the first one and rest come tumbling down in a chain reaction. This is similar to a TRIGGER which will cause further events on other tables and so on!

It is best, however, not to take the analogy too far - you want to avoid doing complex multi-table chained updates in so far as is possible - KISS. You can see how circular references (TRIGGER on  A UPDATEs B which in turn UPDATEs A...) might be disastrous!

From here:

You can use triggers to perform the following actions, as well as others that are not found in this list:

  Create an audit trail of activity in the database. For example, you can track updates to the orders table by updating corroborating
  information to an audit table.
  Implement a business rule. For example, you can determine when an order exceeds a customer's credit limit and display a message to that
  effect.
  Derive additional data that is not available within a table or within the database. For example, when an update occurs to the
  quantity column of the items table, you can calculate the
  corresponding adjustment to the total_price column.

(The link also mentions using them to enforce DRI (Declarative Referential Integrity) - I disagree - see end of answer).

A typical (vendor syntax varies considerably here - check your system's documentation) TRIGGER skeleton (example taken from here) would be:

CREATE
  TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
  ON `database`.`table`
  FOR EACH ROW BEGIN
  -- trigger body
  -- this code is applied to every
  -- inserted/updated/deleted row
END;

and the body might look something like this:

IF NEW.deleted THEN
  SET @changetype = 'DELETE';
ELSE
  SET @changetype = 'NEW';
END IF;
INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);

This trigger is INSERTING a record into a table called audit with the id from the audited table (called blog in this case) and also recording whether the action was a DELETEor an INSERT. You could also have TIMESTAMP logic in there as well to keep track of when the action took place. Furthermore, you could add in user_name logic to audit who INSERTed/DELETEd what and when. The possibilities are endless.

Summary.

So, to sum up, a TRANSACTION groups a set of statements into a logical unit and a TRIGGER permits the inclusion of logic beyond the scope of SQL (complex business rules) in the database itself.

One thing I found tricky at the beginning was the idea that (if no TRANSACTION is specified), the server will set up an implicit transaction on your behalf and COMMIT after each statement.

For example, here is the default behaviour of the MySQL server:

mysql> show variables like '%COMMIT%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| autocommit                              | ON    |

Issue the command

mysql> SET autocommit = OFF;
Query OK, 0 rows affected (0.00 sec)

Now, you can experiment with what happens if you issue several commands in a row and issue a COMMIT and also when you exit without COMMITting.

Better yet, you can experiment with isolation levels

mysql> show variables like '%ISOL%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

and then open different terminal sessions and see what happens to record visibility under different conditions.

Very last word: It looks to me that you are on the beginning of your database journey. Even though I have used MySQL for examples here, I would strongly (strongly) urge you to use PostgreSQL whenever possible - it is a vastly superior database server to MySQL and much more standards compliant to boot! Try and use the command line client whenever possible - can be fiddly at the beginning, but much more powerful and worth it in the long run!

Very very last word ( :-) ) - keep reading and get some good texts and study them! p.s. Congratulations on your first questions on dba.stackexchange!

Oooops - almost forgot to answer the question asked!

You can either (if you have one and only one (main) genre per film do this:

create a genre table and have a FOREIGN KEY on your movie table pointing to the genre_name field (fiddle1),

Or (if you wish to have more than one genre per film - a far more interesting and realistic scenario) do this:

create a genre and movie table as above, but also have a movie_genre joining table (or Associative Entity) between the two (fiddle2).


What I am seeing is that in Task Manager, sqlserver.exe is using about 163 Mb of RAM, if I use procexp, the same service shows just under 500 MB of RAM used.

You should never look at task manager to check SQL Server memory utilization. It would never show you correct result. You should use a DMV sys.dm_os_process_memory, if you are using SQL Server 2008 and above, to check SQL Server memory utilization.

select
(physical_memory_in_use_kb/1024) as PhyMemory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 ) as Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 ) as Total_MemoryUsed_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Note:

PhyMemory_usedby_Sqlserver_MB is the committed memory and is backed by physical RAM.

Total_MemoryUsed_in_MB is the total memory used by SQL Server (RAM and page file)

You are making a mistake, which probably many 'newbies' make when referring to memory used by SQL Server. It would never show you correct memory utilization in task manager if Locked pages in memory privilege(LPIM) is there for the account running SQL Server service.  This is because the task manager only shows Process Private Bytes, the memory allocated via Virtual_alloc function which is pageable. Now if the SQL Server service account has LPIM, most part of the memory allocation would be done by AWE API. The memory allocated via AWE API is NOT pageable and thus does not shows up in task manager, which, in the end shows you different (incorrect) values.

Further reading:

Fun with Locked Pages, AWE, Task Manager, and the Working Set… (CSS SQL Server Engineers)


Window Function to get pairs of records and compute the time difference

You need something like

-- get previous status, use -1 value if no previous record (because NULL value needs more complex condition in cte2)
WITH cte1 AS (SELECT Event_Type,
                     Event_Timestamp,
                     Is_Active,
                     COALESCE(LAG(Is_Active) OVER (PARTITION BY Event_Type
                                                   ORDER BY Event_Timestamp ASC), -1) prev_event
              FROM datatable),
     -- remove records where status not altered
     cte2 AS (SELECT Event_Type,
                     Event_Timestamp,
                     Is_Active
              FROM cte1
              WHERE Is_Active != prev_event)
-- get data we need
SELECT Event_Type,
       Event_Timestamp Begin_Timestamp,
       LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type
                                   ORDER BY Event_Timestamp ASC) End_Timestamp,
       LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type
                                   ORDER BY Event_Timestamp ASC) - Event_Timestamp Duration
FROM cte2
WHERE Is_Active = 1

Of course you can combine it into the query using one CTE which calculates all window values. The above long way is used for to explain the algorythm.


Indexed view doesn't exist on the subscriber

The clue to this is in the error, which is telling you that it can't create the view on the subscriber, because the subscriber is missing tables and/or columns that the view relies on.

You can't create the view without the base tables, so make sure you replicate the tables and/or columns involved in the view to the subscriber too, and then the view itself can be replicated.


I don't know Oracle, but these the seems to do different things.

RLS is row-level security. I.e., you see this row, but not that row.

FGAC (fine-grained access control) seems to be about auditing, i.e., "I can see that you did this modification yesterday". That would be more comparable to the Server Audit feature in SQL Server. (Please correct me if I'm wrong, those who know about FGAC.)


Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS

Sounds like statistics on those tables are out of date.  I would invoke the following statements:

purge dba_recyclebin;
exec dbms_stats.gather_dictionary_stats
exec dbms_stats.gather_fixed_objects_stats;

If your application creates and or drops tables frequently, you may need to put the above statements in a scheduled job.

And as @MarkDPowell suggested, you should also consider gathering system stats via DBMS_STATS.GATHER_SYSTEM_STATS and DBMS_STATS.GATHER_PROCESSING_RATE (new in 12c) -- that is important in 11g and more important in 12c.


Your instance is called CFRDB, but you have set ORACLE_SID to AWSDB.

export ORACLE_SID=CFRDB, then try again.


Instead of writing your own backup solution, I would suggest you to look at SQL Server Maintenance Solution by Ola Hallengren.

If you want to use sqlcmd then you can do as below:

Save below file as backup.sql

DECLARE
    @DatabaseName sysname = N'test_kin'
    ,@DatabaseBackupFileName varchar(255);

SET @DatabaseBackupFileName =
    'C:\test\' + @DatabaseName
      + REPLACE(CONVERT(char(8), GETDATE(), 103), '/', '_') + '.bak';

BACKUP DATABASE @DatabaseName
TO DISK = @DatabaseBackupFileName with init, stats =10;

Then using sqlcmd you can do as below :

sqlcmd -S ServerName\InstanceName -i C:\YourScriptDir\Backup.sql


Add article to transaction replication without generating a snapshot at all

There really isn't a supported way to do what you are asking. You can use a backup/restore but that would be to a new database. You could try a filtered article where you only grab data after a certain create date.

Or you could use a manual process such as an SSIS package or T-SQL Scripts to stage the schema and data. But the snapshot is more or less a required item.

I would probably do a single article snapshot or maybe look to filter if you can filter create_date > getdate() and then add in rows manually after if you have such a column, or an identity column, etc.

A few links:

About manually preparing.

About using backups or other methods.

About filtering.


Postgres - Inject 0 values when rows do not exist

You need to create a pseudo date range and join to it...  Like such:

select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date;

Once you have your series, you can join to it and use COALESCE to fill in 0's for where there is no data..

WITH days AS (
  select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date AS day
) SELECT
  d.day, COALESCE(t1.col1, 0) AS value
FROM days d
LEFT JOIN <something with a date> t1 on d.day = t1.day

If you need to join to something that DOESN'T have a date (for example, your "person" table above), then just join with the condition being "TRUE" (to make a cartesian join, so every person gets every day)

WITH days AS (
  select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date AS day
) SELECT
  d.day, COALESCE(t1.col1, 0) AS value
FROM days d
JOIN person p ON TRUE


Replicating from master to slaves and from slaves to master

What you are looking for is multi-master replication.  Multi-master replication has a large number of potential problems and consequently the best anything can offer is a toolkit rather than a guaranteed solution (imagine two concurrent updates on the same row on different servers).

Bucardo will do multi-master replication for two servers, but not three.  You could probably play some fun tricks with Slony and table partitions but this ends up getting complex and is definitely expert territory.

My recommendation is avoid multi-master replication if at all possible.  It is mathematically impossible to make it just work all the time without making it more failure prone than a single system would be.  Try hard to design so that you don't need it.

If you do need it, please look at getting some professional help whether it is a second set of eyes or a consultant (who can also get another consultant to review the design).  There is so much that can go wrong here that you really want additional eyes on the problem.


Your code creates a column with the name PRIMARY_KEY, but this is not the same as a primary key constraint. (It is not enough to be unique.) As Branko Dimitrijevic pointed out only logical constraints can be defined on views, and a primary key constraint will be enforced by Oracle.

On a materialized view where you store your data physically in the database a index may help you to find your data faster. A primary key constraint may be helpful if you want to use replication. But a materialized view is more of a table than a view.

I hope this helps to answer your questions.


I expect I would have a drive_usage fact table with a link to a snapshot time dimension, a drive dimension, a computer dimension and the various numerical facts about the drive at that instant in time.

There should probably be nothing regularly changing in the drive dimension - I guess it depends upon your definition of drive - is it a physical drive or a logical unit or what.  Perhaps your "C" drive has a serial number, and it's replaced - then the dimension will expire and a new dimension is added.  These things about a dimension are not really "facts", they are attributes.  This wouldn't affect reporting because the data for computer X, drive C has continuity.  Similarly if computer X is upgraded from dual core to quad core and so there is a change to the dimension (assuming something beyond number of cores is not tracked in a fact table, like a motherboard revision).  The capacity of a drive would be in the fact table, so changes to that over time are just new facts with new dates.  Sometimes you can even model changes to membership as facts.  i.e. if physical drives 1-5 are in logical drive C one day and then physical drives 1-6 are in logical drive C the next, that might just be a fact change in the physical drive membership fact table.  These are what some people call factless fact tables, since the only fact is the existence of the row shows membership - there is not much to be done except totalling or counting.

When you get into folders, modeling the hierarchy can be a lot more tricky depending upon what you are trying to achieve with rollups.

There is a lot of art to DW modeling in domains which aren't run-of-the-mill scenarios.


Perform group by with random group expression (randomly partitioning rows) MYSQL

Your post piqued my curiosity and I look at the RAND() function - which you can't perform GROUP BYs with. I found this and if your table has a PRIMARY KEY, there is a MySQL pseudo-column (_rowid), much like is found in Oracle, Firebird (and maybe others) then you can issue queries such as this one (see structure and data for comments table (sample) below).

select MOD(_rowid, 3) AS my_field, COUNT(ticket_id)
FROM comments
GROUP BY my_field;

+----------+------------------+
| my_field | count(ticket_id) |
+----------+------------------+
|        0 |                6 |
|        1 |                7 |
|        2 |                7 |
+----------+------------------+

Now, if your PK is some sort of AUTO INCREMENT and "random" with respect to your data, you could perhaps use the _rowid pseudo-column (with MOD) to generate a sufficient degree of randomness for your requirements, and perform your aggregate queries?

---- structure and data in comments table---

CREATE TABLE `comments`
(
  `comment_id` int(11) NOT NULL,
  `ticket_id` int(11) NOT NULL,
  PRIMARY KEY (`comment_id`)
);

mysql> SELECT * FROM comments;
+------------+-----------+
| comment_id | ticket_id |
+------------+-----------+
|          1 |         1 |
|          2 |         1 |
|          3 |         1 |
|          4 |         1 |
|          5 |         2 |
|          6 |         2 |
|          7 |         2 |
|          8 |         2 |
|          9 |         3 |
|         10 |         3 |
|         11 |         3 |
|         12 |         3 |
|         13 |         4 |
|         14 |         4 |
|         15 |         4 |
|         16 |         4 |
|         17 |         5 |
|         18 |         5 |
|         19 |         5 |
|         20 |         5 |
+------------+-----------+


How to check if subscriber replication initialization has timed out

Use SQL Server Replication Monitor in order to review the current state of your Replication topology.

See Monitoring Replication with Replication Monitor


Msg 142, Level 15, State 2 -- Can't figure out what I have wrong

Move the CONSTRAINT DEFAULT next to the field definition:

CREATE TABLE Accounts.AdminUsers ( admID             INT              NOT NULL    IDENTITY(1,1)
                          , admFN            VARCHAR(35)      NULL
                          , admLN            VARCHAR(35)      NULL
                          , admUserName  VARCHAR(64)      NOT NULL
                          , admPass      VARCHAR(128)     NOT NULL
                          , admPassIV        VARCHAR(32)      NOT NULL
                          , admPassResetDate DATETIME2    NULL
                          , admEEID      VARCHAR(30)      NULL
                          , admEmail         VARCHAR(255)     NULL
                          , admAcctEnabled   BIT              NOT NULL CONSTRAINT DF_Accounts_AdminUsers_admAcctEnabled
                            DEFAULT (1)
                          , admHier      HIERARCHYID      NOT NULL
                          , admNumLogins     SMALLINT         NULL
                          , admLastLogin     DATETIME2    NULL
                          , admCreateTS  DATETIME2    NOT NULL CONSTRAINT DF_Accounts_AdminUsers_admCreateTS
                            DEFAULT (CURRENT_TIMESTAMP)
                          , admTID       TINYINT          NOT NULL
                          , CONSTRAINT PK_Accounts_AdminUsers
                            PRIMARY KEY CLUSTERED (admID)
                          );


Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order

This seems to be a working solution (I still wonder if there's a more elegant one):

select name, sum(salary) from (
    select rownum rnum, name, employee_id join_id
    from employees
    connect by manager_id=prior employee_id
    start with manager_id is null
    order siblings by name
  )
  join (
    select salary, connect_by_root employee_id ancestor_id
    from employees
    connect by manager_id=prior employee_id
  ) on join_id=ancestor_id
  group by rnum, name
  order by rnum;


sql web-application Connection String for member of a domain group

You need to configure the IIS Application Pool to use the identity (domain user) you created.

My example below shows the default, out-of-the-box configuration.  You'd need to choose 'Configurable' and specify the username and password there.

Then you need to configure the site to use the AppPool.

The connection string would then be:

server=myserver\sqlexpress;database=mydatabase;Persist Security Info=True;
Integrated Security=SSPI;Trusted_Connection=True; Pooling=True

Just like you have it.


The main problem is that you have 60 GB of data which you want to access quickly on a single slow HDD (I've never heard of 7400 rpm, is it 5400 or 7200?).

You can partition on the time, so the last two weeks of data are grouped together in tight set.

Or you could try clustering on the time instead of partitioning on it.  You could either build on index on the date column, cluster on that, then drop the index; or you could just make a new table by create table asdfkj as select * from sensor_readings order by time.

I would expect the table to already be pretty well clustered on time, but it doesn't seem to be.  How did this table get populated originally?  How does it get kept up to date now?

But all of these methods are pretty expensive (in your time) to avoid buying a better IO system.


Why does Oracle keep working on MySQL 5.1

Oracle are obliged to fix security bugs and updates for MySQL 5.1 until the Premium support period has ended - remember that there are paying customers out there using the product in production.

See page 13 of this document for support end dates by MySQL version.

MySQL 5.5 is more than ready for production use & I'd recommend using that for any new projects going forward.


MySQL general log as table -- Scheduling cleaning

It won't cause problems, but you will lose all the old log entries, so advice above is good.

You can do a backup of logs if you want with:

mysqldump -p --lock_tables=false mysql general_log > genlog.sql

Do you need to have the general log on all the time? I usually only turn it on when I'm troubleshooting performance issues. MySQL logs EVERYTHING there (client connects, disconnects, and EVERY statement). In most systems, the logs get very big very quickly. There is also some performance overhead for this.


How to properly design outgoing/incoming mailserver configuration per user?

Because you're doing this to learn database design, I'd get strict.  I'd also recommend Louis Davidson's book on database design.  He focuses on SQL Server, but it's a really well-done book no matter which database platform you're using, and his chapter on normalization is outstanding.

Your user table has repeating attributes:  two mail configuration IDs.  In a real-world business scenario, this might slide, but it is a violation of first normal form.  The way to correct that is to break those out into a separate table, turning columns into rows.

Looking at hosts and ports, there are a couple of possibilities.  Do you want to limit appropriate ports, or "know" which port is which (e.g., 25 for SMTP, 110 for POP3, 143 for IMAP)?  If so, create a ports table.  In that case, you could use either a surrogate key or the port number as the primary key because well-known mail ports are distinct.  On the other hand, because servers can use "weird" ports, you might not be able to create that table (or you may need to leave it open for users to add new ports to the list).  In a business application, this would be a business decision.  In an academic application, I'd probably create the table and make a foreign key connection to the mail server configuration table.

Regarding hosts, you have a similar question.  If you want to limit hosts, check for common misspellings, or be able to display more information about the particular host (e.g., pop3.google.com's application name is GMail), you would want to create a Host lookup table, as well as application logic which lets users create new hosts.  But there's something else to think about with hosts:  how far down the rabbit hole do you want to go?  Are you going to analyze based on the host's top-level domain (TLD)?  If so, you would want to break out the domain into its own table.  In fact, you might have three separate tables:  host prefixes (mail, pop3, smtp, imap, etc.), host name (google, yahoo, etc.), and host top-level domains (.com, .co.uk, etc.).  Your Host table would then have a Prefix ID, a Hostname ID, and a TLD ID and you could build the final hostname up from that.  Whether this makes any sense at all for you to do depends upon the application:  if you're using the mail server information to shoot e-mails out but aren't analyzing it further, then this is going too far; otherwise, if you do need to split out hostnames or domains as part of your application, keeping it all as "pop3.google.com" would be a violation of first normal form.

Finally, looking at the mail server configuration table, you have port on there.  If your users have different credentials for receiving and sending e-mails, that would make sense, but otherwise, you end up duplicating data:  username Bob with password PW at mail.yahoo.com will be the same regardless of whether he's connecting to port 25 or 110.  The port really is more for communication than authorization, so it makes some sense here to split it out.  You'll still have some "duplication" of sorts because of hosts that have pop3.hostname.com and smtp.hostname.com servers, but I wouldn't worry too much about that, especially if we don't need to split hosts out.

Assuming that you don't need to split out hosts any further, the table design would look a bit like the following:

Host
----------------------------
HostId smallint (PK; surrogate)
Name varchar(75) (AK1)
[additional host attributes]

Port
----------------------------
PortId smallint (PK; surrogate)
Number int (AK1)
Name varchar(20)

Direction { Inbound; Outbound }
----------------------------
DirectionId tinyint (PK; surrogate)
Name varchar(8) (AK1)

User
----------------------------
UserId int (PK; surrogate)
[additional user attributes]

UserHostCredential
----------------------------
UserHostCredentialId (PK; surrogate)
HostId (FK; AK1)
UserId (FK; AK1)
Username
Password

UserMailConnection
----------------------------
UserMailConnectionId int (PK; surrogate)
UserHostCredentialId int (FK; AK1)
DirectionId tinyint (FK; AK1)
PortId (FK)

AK here means alternate key.  You'd put a unique key constraint wherever you see that.  I numbered them to show you instances in which there are multi-column alternate keys, like mail server configuration.

This is a bit more work than your two-table design, but not really that much more.  I would tend toward adding the Host and Port tables simply to get rid of potential update inconsistencies; for example, if Google's new POP3 server is pop3.googlemail.com instead of pop3.google.com, you would have to update all of the rows to match this.  But if somebody misspelled a row (or you didn't catch it in your updating process), you now have a data anamoly.  In the hosts table, you make one update and you're done.

But one important thing to note (and something that I've tried to hint at up above) is that business rules shape database design.  I'm working from one set of assumptions, some of which may not exactly match a particular scenario.  For example, I'm assuming that a username and password typically should relate to one person at one host.  But if a number of different people all use the same credentials (for whatever reason), this may no longer apply.  In that case, I'd move UserId off of UserHostCredential and onto UserMailConnection and then rename UserHostCredential to HostCredential.  Both of those are "valid" database designs, and which one is correct depends upon the business purpose.

Similarly, I'm assuming that Port isn't necessarily determinate of direction (inbound or outbound).  If you are able to make that determination--e.g., that 25 is always outbound, and there are no ports which are both inbound and outbound--then the direction stops being an attribute of a particular connection and instead becomes an attribute of the port itself.  If that is the case, you would move DirectionId from UserMailConnection up to Port and make PortId part of the alternate key for UserMailConnection.  Again, both are valid, fully-normalized table designs, but which business rule holds would determine which is the "correct" one for your purposes.


SQL Server Agent Invokes Failed 'sa' Login

After a few days of research, it looks like this issue is caused by a bug in SQL Server 2016. In the following thread that describes this issue, other DBAs are experiencing the same thing. One person troubleshooted the issue with a Microsoft support engineer who was confident it was a bug.  Maintenance plan - Login failed for user 'sa' on master database

BUG: Use SQL authentication when creating a maintenance plan through the maintenance plan wizard. Run the maintenance plan. It will succeed, but after it succeeds, it will log a number of failed logins.

WORKAROUND: Use Windows authentication instead of SQL authentication. If you have already created the maintenance plan, you will need to right click on it and select modify. Look for "manage connections" at the top window. You will be able to modify the local connection here. You will need to be logged on your machine with that domain account and that domain account will need the proper SQL privileges.


Sybase SQL - Truncation error occurred. Command has been aborted and find CACHE QUALITY

Just in case if any one wants to set up for CACHE QUALITY for Sybase ASE 15 and up, below script will help:

Changes for the script to work fast without overhead of querying MDA tables and the truncation issue is resolved by switching to int datatype.:

The where clause now makes the script work when Sybase ASE is under heavy loads.
where CacheID = 0
The last change is just a logic change for when physical reads = 0, this way we save the logical reads. then @logical_read2 - @logical_read1

    -- CREATE A TABLE FOR DURATION SIMULATION
    if not exists(select 1 from sysobjects where name = 'TimeControl')
    begin
      create table TimeControl (counter int)
    end
    go

    -- CREATE TABLE FOR SQL PIPE RESULTS
    if not exists(select 1 from sysobjects where name = 'monCacheQuality')
    begin
      create table monCacheQuality (
      ServerName sysname
      ,CollectionDateTime smalldatetime not null
      ,PhysicalWrites decimal(15, 0) not null
      ,PhysicalReads decimal(15, 0) not null
      ,LogicalReads decimal(15, 0) not null
      ,CacheQuality decimal(15, 0) not null
      )
    end
    go
    create table cache_Begin (
  CacheID int not null,
  InstanceID tinyint not null,
  RelaxedReplacement int not null,
  BufferPools int not null,
  CacheSearches int not null,
  PhysicalReads int not null,
  LogicalReads int not null,
  PhysicalWrites int not null,
  Stalls int not null,
  CachePartitions smallint not null,
  CacheName varchar(30) null
)
go
create table cache_End (
  CacheID int not null,
  InstanceID tinyint not null,
  RelaxedReplacement int not null,
  BufferPools int not null,
  CacheSearches int not null,
  PhysicalReads int not null,
  LogicalReads int not null,
  PhysicalWrites int not null,
  Stalls int not null,
  CachePartitions smallint not null,
  CacheName varchar(30) null
)
go

use tempdb --- change this to be other than the db being monitored !!
go

declare @physical_read1 int
      ,@logical_read1 int
      ,@physical_write1 int
declare @physical_read2 int
      ,@logical_read2 int
      ,@physical_write2 int

while (select counter from TimeControl) =1
begin
      select @physical_write1 = PhysicalWrites
            ,@physical_read1 = PhysicalReads
            ,@logical_read1 = LogicalReads
      from master..monDataCache
      where CacheID = 0

      waitfor delay '00:01:00'

      select @physical_write2 = PhysicalWrites
            ,@physical_read2 = PhysicalReads
            ,@logical_read2 = LogicalReads
      from master..monDataCache
      where CacheID = 0

      insert monCacheQuality
      select @@servername as ServerName
            ,getUTCdate()
            ,@physical_write2 - @physical_write1
            ,@physical_read2 - @physical_read1
            ,@logical_read2 - @logical_read1
            ,case
                  when @physical_read2 - @physical_read1 = 0
                        then @logical_read2 - @logical_read1
                  else (@logical_read2 - @logical_read1) / (@physical_read2 - @physical_read1)
                  end as CacheQuality
end

-- Report the data that is collected.........
select *
from monCacheQuality

---- reset it back ... (in another Query window)
update TimeControl
set counter = 0
go

--- cleanup ... start and end cache table for next reuse
drop table cache_Begin
go

drop table cache_End
go


Archival copy of table, including overwrote data

There are multiple ways you can solve this problem, but this would be the quickest and dirtiest.

You would create a trigger that executes during every change to a row inside the table you want to essentially "version". The trigger copies the change in information as a completely new row inside another table (I call them an audit table, I.E: users_audit). You would be creating the trigger on your replica (not your master). The replica would contain the new AuditLog table and in return would contain all the rows that were changed for the original table on your master. Of course you would need to version the rows, for which, I recommend using a TIMESTAMP column. This post below gives a good explanation of this technique:

https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records

Hope this helps.


Oracle concatenation of queries and function index usage

This seems to be a limitation of the optimizer (or perhaps a bug).

The plan for this query:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1
union all
(
SELECT * FROM test2 WHERE upper(field1)='FIELD1=1'
union all
SELECT * FROM test2 WHERE field3=1
);

shows that it is not the fault of bad stats, and also demonstrates a workaround using union all (but note you need something like upper(field1)='FIELD1=1 and (field3 is null or field3<>1) for an exactly equivalent query)

(My test was on 10.2)


Can't set up mirroring for a specific database

Create full backup of the primary database
  Create backup of the transaction log, in the same backup file
  Restore that backup on the secondary location, with the norecovery option
  Set partner on mirror, then primary.

When the mirroring session is established the primary must have the current LSN of the mirror-to-be in the active log. This is necessary so that the primary can start sending log records to the mirror to bring it up to date, w/o any interruption.

In your sequence of events you have two LSNs saved: one after the full backup (LSN1), one after the log backup (LSN2). The primary will be able to accommodate a mirror that is at LSN2, but not one at LSN1. If you restore the full backup with no-recovery and then also restore the log, also with no-recovery, that should end up with a proper mirroring candidate database. But if you only restore the full backup then the mirror will be at LSN1 and the primary will reject it.


Deploying to SSIS catalog and get permissions error

Ended up finding an article that mentioned fixing this by running a repair on the Sql Server install. When this was run, the problem was solved, temporarily.   The root cause was a group policy that was overwriting the permission needed... repair fixed the permission until the next group policy update, then it would break again.  Once we identified the group policy and fixed it, the problem went away.


how to get overdue complaints using join two tables

You can't create an interval like that. To have a variable number of months,  create a 1 month interval and multiply it with your number of months.

You also need to compare the result of adding the interval to the completion date to something. I assume you want to compare it to "today":

select comp.complaint_id
from complaints as comp
  JOIN template as tmpl ON comp.templateid = tmpl.id
where comp.completion_date + interval '1 month' * tmpl.template_month < current_date;

Online example: https://rextester.com/JGNX75589


Import data from one Database to another script

Another option that could handle this and possibly allow for a bit more flexibility is to use a SQLCLR function or stored procedure that makes use of the SqlBulkCopy class (which is essentially the same framework used by BCP.EXE, BULK INSERT, etc). While writing such a function/procedure might not be worth the time/effort for a one-off project, there is fortunately such a stored procedure that already exists and is downloadable for free. It is called DB_BulkCopy and is part of the SQL# SQLCLR library (which I am the author of, but again, this stored procedure is in the Free version). It allows you to run any random query (that returns a result set, of course) and send the results to the local instance or a remote one (even to Oracle if need be). It also allows for:

mapping columns if the destination has different names and/or ordering of the fields.
setting a particular batch size.
specifying the following options: KeepIdentity, CheckConstraints, TableLock (allows the operation to happen in Bulk Logged mode), KeepNulls, FireTriggers, and UseInternalTransaction.
optionally reporting the total number of rows migrated.
executing a stored procedure as the source of the data instead of using a SELECT

I am mentioning this because, as compared to running an external package, it is very easy to:

update the parameters being sent to a stored procedure
call a stored procedure in an ad-hoc fashion, or as part of a larger migration script, or scheduled from SQL Agent, etc.
call a stored procedure in a loop to send multiple tables

Example:

EXEC SQL#.DB_BulkCopy
    @SourceQuery = N'SELECT t1.field1, t1.field5, t2.field12
                     FROM   dbo.Table1 t1
                     INNER JOIN dbo.Table2 t2
                             ON t2.FKfield = t1.PKfield
                     WHERe   t2.field8 > something;
                    ',
    @DestinationConnection =
        N'Server=ServerName\Instance; Trusted_Connection=true; Database=NewDB;',
    @DestinationTableName = N'MyNewTable',
    @BatchSize = 2000,
    @NotifyAfterRows = 100, -- print message per every 100 rows
    @TimeOut = 600, -- 10 minutes
    @ColumnMappings = N'field1,NewFieldA|field5,NewFieldB|field12,NewFieldC',
    @BulkCopyOptionsList = N'KeepIdentity|TableLock|KeepNulls';


Passing array from one plpgsql function to another

You can avoid various complications by passing values as values with the USING clause:

CREATE OR REPLACE FUNCTION foo(linktable regclass,
                               inttable  regclass,
                               verttable regclass)
RETURNS void AS
$func$
BEGIN
   EXECUTE format(
      'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t'
     , linktable)
   USING  $1, $2, $3;  -- you could use parameter names as well
END
$func$ LANGUAGE plpgsql;

And pass the generated array directly in a single SELECT.

Note that $1, $2, $3 in EXECUTE reference the USING clause and only happen to be in sync with ordinal numbers of function parameters in USING  $1, $2, $3. Same notation, independent scope!

After fixing the immediate cause for the error (like @Craig commented):

WHERE   int.id ANY %L

WHERE   int.id = ANY (%L)

Simplify the 2nd function in a similar fashion:

CREATE OR REPLACE FUNCTION bar(linktable regclass,
                               inttable  regclass,
                               verttable regclass,
                               intersection_ids int[])

    -- ... SNIP

    EXECUTE format('
        INSERT INTO %1$s
               (int_id, ref_link_id, match_link_id, ref_azimuth, match_azimuth)
        SELECT  int.id, l1.id      , l2.id
              , degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom)))
                degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
        FROM    %2$s int
        JOIN    %3$s v1 ON v1.intersection_id = int.id
        JOIN    %3$s v2 ON v2.intersection_id = int.id
        JOIN    %4$s l1 ON l1.target_node = v1.node_id
        JOIN    %4$s l2 ON l2.source_node = v2.node_id
                     AND l2.road_id <> l1.road_id
        WHERE   int.id = ANY ($1)    -- fix syntax and pass as value!
        AND     l1.road_id IS NOT NULL
        AND     l2.road_id IS NOT NULL
        ', temptable,
           inttable,
           verttable,
           linktable)
    USING intersection_ids;

    ...

The whole setup might be simplified further.


Joining from database A to view in database B; where is processing done?

Views are inlined into the query plan. Optimization then proceeds not even knowing that there once was a view.

The execution algorithm in general is not directly dictated by the way the query is written.

The join order in particular is very indirectly related to the way you wrote the joins.

So the answer depends on your particular schema, data and query. Likely, the answer is that the way you write the joins has no or has little impact.

You can find out by comparing the actual execution plans.


Which one to use? InnoDB Replication or MySQL Cluster?

MySQL Cluster possibly a good fit for you because of your key:value lookup traffic pattern. NDB is traditionally poor with joins or compex queries. I would say however that it is not the same as MySQL and has caveats that you need to understand before jumping into bed with it. It's shared nothing architecture means that you should operate it at scale with decent (and inclusive redundancy) network infrastructure. It is perhaps worth a proof of concept project to fit it out and test it. The guys at severalnines have configuration tools to get you up and running.

Mongo isn't a bad fit either and as you state you cannot give up A_I, why not? What about values like (1902jdd,"/page/id") needs and auto inc? Mongo documents are schemaless and you add superfluous data to a document where you see fit and query in a key:value style too. Statistics moving forward would put you into the map reduce world which could be fun.

Keeping with traditional RDBMS and requiring performance you should look at memcached and the mysql memcached api for rapid key:value access to the data which would retain the relational aspect for aggregation queries (select [count|max|avg]... group by order by blah, blah...


How to install Postgresql on Ubuntu from PPA with data-checksums enabled?

If you do not want to install and drop the cluster on Debian and related, you could:

Install postgresql-common;
Edit the /etc/postgresql-common/createcluster.conf file to allow the use of the --data-checksums option;
Install postgresql-9.6.


Exposing SQL Server database to internet

what I’m really looking to do is create offsite backups automatically

SQL Server can natively backup to Azure Blob Storage.  See Backup to URL.

But whatever offsite storage you use, the right solution is for SQL Server (or code running on the local network) to push the data out.  Not have code running elsewhere pull it out.

That way you don't need to open anything to the internet, or use a VPN.  You just need to open outbound ports, to connect to the internet.


You could try to query the table information_schema.columns and find every table in your MySQL DB Instance that have character fields. Here is the table's layout:

mysql> show create table information_schema.columns\G
*************************** 1. row ***************************
       Table: COLUMNS
Create Table: CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(27) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Here is a query to get every character-based column type for all user-defined tables:

mysql> select DISTINCT COLUMN_TYPE from information_schema.columns
    -> WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
    -> AND (COLUMN_TYPE REGEXP 'text$'
    -> OR COLUMN_TYPE REGEXP '^varchar'
    -> OR COLUMN_TYPE REGEXP '^char');
+--------------+
| COLUMN_TYPE  |
+--------------+
| mediumtext   |
| varchar(512) |
| varchar(20)  |
| longtext     |
| text         |
| varchar(200) |
| varchar(255) |
| varchar(100) |
| tinytext     |
| varchar(25)  |
| varchar(64)  |
| varchar(32)  |
| varchar(60)  |
| varchar(50)  |
| varchar(250) |
| varchar(150) |
| varchar(10)  |
| varchar(45)  |
| char(5)      |
+--------------+
19 rows in set (0.09 sec)

You could join that query to a second query. The second query would have the name of every table with those column types:

SELECT BB.table_schema,BB.table_name,BB.COLUMN_NAME FROM
(select DISTINCT COLUMN_TYPE from information_schema.columns
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND (COLUMN_TYPE REGEXP 'text$'
OR COLUMN_TYPE REGEXP '^varchar'
OR COLUMN_TYPE REGEXP '^char')) AA
INNER JOIN
(SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE
FROM information_schema.columns
WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')) BB
USING (COLUMN_TYPE);

Now, message the output to make it write a generic script for you:

SELECT CONCAT('UPDATE ',db,'.',tb,' SET ',fld,
'=REPLACE(',fld,',''oldchar'',''newchar'');')
UpdateCommand
FROM (SELECT db,tb,fld FROM
(
    SELECT table_schema db,table_name tb,COLUMN_NAME fld FROM
    (select DISTINCT COLUMN_TYPE from information_schema.columns
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
    AND (COLUMN_TYPE REGEXP 'text$'
    OR COLUMN_TYPE REGEXP '^varchar'
    OR COLUMN_TYPE REGEXP '^char')) AAA
    INNER JOIN
    (SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE
    FROM information_schema.columns
    WHERE table_schema NOT IN
    ('mysql','information_schema','performance_schema')) BBB
    USING (COLUMN_TYPE)
) AA) A;

Output should look something like this:

+-----------------------------------------------------------------------------------------------+
| UpdateCommand                                                                                |
+-----------------------------------------------------------------------------------------------+
| UPDATE example.user SET user_name=REPLACE(user_name,'oldchar','newchar');                     |
| UPDATE garbage.rolando SET post_content=REPLACE(post_content,'oldchar','newchar');            |
| UPDATE garbage.rolando SET post_title=REPLACE(post_title,'oldchar','newchar');                |
| UPDATE garbage.rolando SET post_excerpt=REPLACE(post_excerpt,'oldchar','newchar');            |
| UPDATE garbage.rolando SET post_status=REPLACE(post_status,'oldchar','newchar');              |
| UPDATE garbage.rolando SET comment_status=REPLACE(comment_status,'oldchar','newchar');        |
| UPDATE garbage.rolando SET ping_status=REPLACE(ping_status,'oldchar','newchar');              |
| UPDATE garbage.rolando SET post_password=REPLACE(post_password,'oldchar','newchar');          |
| UPDATE garbage.rolando SET post_name=REPLACE(post_name,'oldchar','newchar');                  |

Last step for generic script generation is to output the query to a text file like this:

mysql -u... -p... -A --skip-column-names -e"SELECT CONCAT('UPDATE ',db,'.',tb,' SET ',fld, '=REPLACE(',fld,',''oldchar'',''newchar'');') UpdateCommand FROM (SELECT db,tb,fld FROM (     SELECT table_schema db,table_name tb,COLUMN_NAME fld FROM     (select DISTINCT COLUMN_TYPE from information_schema.columns     WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')     AND (COLUMN_TYPE REGEXP 'text$'     OR COLUMN_TYPE REGEXP '^varchar'     OR COLUMN_TYPE REGEXP '^char')) AAA     INNER JOIN     (SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE     FROM information_schema.columns     WHERE table_schema NOT IN     ('mysql','information_schema','performance_schema')) BBB USING (COLUMN_TYPE) ) AA) A;" > CharUpdateScript.sql

You can customize the generic script from there.


Performance inprovement UDB DB2 v10.5.0.5``

I'm in a bit of a hurry, but I'll try to answer question 1 to some extent.

There are two major parameters that affects locking:

a) LOCKLIST - This is the number of 4K blocks that db2 can use to keep track of locks

b) MAXLOCKS - This is the percent of LOCKLIST that each transaction is allowd to use.

A lock is using bit of memory from the LOCKLIST, and if either the limit for a) or for b) is violated, db2 tries to free up memory by replacing rowlocks with a table lock. To realize that this can cause a deadlock concider:

Tx_a updates a row
Tx_b updates a huge amount of rows and excausts the LOCKLIST. db2 tries to escalates the lock to a table lock, but that is prevented by tx_a's lock
Tx_a tries to update one of the rows locked by Tx_b -> DEADLOCK

Check whether it actually is deadlocks, the error message for -911 says something like "Deadlock or locktimeout". Deadlock is rc=2, timeout is rc=68.

You can try to minimize this problem by either add more resources (increase LOCKLIST, MAXLOCKS) or by reducing the amount of locks that you acquire (commit more frequently). You may also want to have a look at the isolation level that your transactions use, also concider CURRENTLY COMMITED

For your question 2, there's no such thing as an optimum value (otherwise I'm pretty sure that would be the default :-) It all depends on the workload

Question 3's answer is pretty similar to answer 2, it depends. It is way beyond a stackexchange post to walk through all parameters that can affect performance, but I would start looking at statements that read the most rows in your system, and see if they can be improved. In many situations you can find indexes that improve the situation a lot.


Logic rules representation and storage in DB column

I'll write this as an answer, even though it's just a few ideas. Breaking down the logical rules in a relational form is probably a waste of time, I would simply store the rules using XML or JSON. A parser friendly format like:

{ OR
    { AND
          { GE1 has level > 1 }
          { GE2 has level > 1 }
    }
    { GE3 has level > 2 }
}

is maybe beneficial. Note that this is just an example

This format can easily be transformed into SQL like:

WHERE ((ge=1 AND level>1) AND (ge=2 AND level=2))
   OR  (ge=3 AND level>2)

The ge attribute can be constructed as:

WITH T (uid, level, epoch, ge) AS (
    SELECT uid, level, epoch
         , row_number() over (partition by epoch
                              order by uid) as ge
    FROM data.time_series
)

All and all you would end up with a query like:

WITH T (uid, level, epoch, ge) AS (
    SELECT uid, level, epoch
         , row_number() over (partition by epoch
                              order by uid) as ge
    FROM data.time_series
)
SELECT uid, level, epoch
FROM T
WHERE ((ge=1 AND level>1) AND (ge=2 AND level=2))
   OR  (ge=3 AND level>2)

The idea is hence to read all the rules into memory, when a rule is about to be evaluated it is transformed into SQL, and the query is executed against the time_series table.


Slow SQL Server 2008 R2 backups after Windows update

This is a bit of a fishing expedition but here would be my troubleshooting process assuming you don't have your own stats to help clear things up.

Check the history of database size and compressed backup size from your msdb catalogs. Have they changed over the past few weeks.

Sometimes an application will go bezerk and fill a database with garbage; what was 100GB is now 1TB. SCOM and Sharepoint are good examples of this.
Sometimes someone can go in and turn off compression on your instance or in whatever your backup software is, especially if they are trying to force you to use de-duplication on a SAN.

You need to talk to the staff who are managing the Infrastructure and SAN.

If this is a VM they may have loaded the VM server with another host that is chewing up resources.
Their VM monitoring software may be logging useful statistics that will help you narrow things down.
The SAN people usually have their own built in monitors that may be able to add some detail.

If you're still stuck with no metrics of any kind, go back to your msdb information and use the timings to calculate an MB/s for backups. Now take a copy only backup to NUL (note: this will put a negative load on your system of course) and compare.

If your backup to NUL speed is below your previous MB/s speed you'll know your read speed of the database has changed and is likely the bottleneck (though there may be others, it's a starting point).
Otherwise you'll know the server itself is fine and that you need to refocus your attention in the direction of network IO and the destination SAN.

If all of this fails for some reason...

You can see what updates were installed in Add/Remove Programs and do some Googling on the KBs to see what turns up. I don't think you will find anything but who knows; maybe?
Start capturing new metrics. You could use the built in Management Data Warehouse functionality seeing as you have nothing else. You could use a trial of a paid tool.
If you still had to do it manually, you could capture the disk IO DMVs (sys.dm_io_virtual_file_stats) using sample scripts from the net to work out your IO wait times, and see if they're abnormally high like over 100ms which would point at a local disk (or SAN) issue, or intensive queries. Then you can use other DMVs (and sample scripts) to pull out intensive queries from the plan cache and see if you can spot anything strange.
Have any new staff been added, or a new developer, or even to an existing AD group that has been granted access, or a new login on the server (check sys.server_principals)? I've seen new people point Excel and other tools at a production database and kill it without realising the impact.
It's possible, but unlikely, you're in a situation where the first few queries that came in had parameters that created poorly optimised plans which are now doing table scans over and over and dragging down performance. If you got desperate, you can also free your plan cache (seeing as the system is in dire straights anyway) and see what happens. I've never seen this drag a system down before but I have seen it kill individual applications before.

As you can see, if you have no previously recorded information about how the system should be acting, it's a matter of thinking of everything it could possibly be, and working out how you're going to rule that in or out through expensive and time wasting investigation. But them's the breaks.


Yes, SQL Server can, under some circumstances read one column's value from the "old" version of the row, and another column's value from the "new" version of the row.

Setup:

CREATE TABLE Person
  (
     Id      INT PRIMARY KEY,
     Name    VARCHAR(100),
     Surname VARCHAR(100)
  );

CREATE INDEX ix_Name
  ON Person(Name);

CREATE INDEX ix_Surname
  ON Person(Surname);

INSERT INTO Person
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID),
                   'Jonny1',
                   'Jonny1'
FROM   master..spt_values v1,
       master..spt_values v2

In the first connection, run this:

WHILE ( 1 = 1 )
  BEGIN
      UPDATE Person
      SET    Name = 'Jonny2',
             Surname = 'Jonny2'

      UPDATE Person
      SET    Name = 'Jonny1',
             Surname = 'Jonny1'
  END

In the second connection, run this:

DECLARE @Person TABLE (
  Id      INT PRIMARY KEY,
  Name    VARCHAR(100),
  Surname VARCHAR(100));

SELECT 'Setting intial Rowcount'
WHERE  1 = 0

WHILE @@ROWCOUNT = 0
  INSERT INTO @Person
  SELECT Id,
         Name,
         Surname
  FROM   Person WITH(NOLOCK, INDEX = ix_Name, INDEX = ix_Surname)
  WHERE  Id > 30
         AND Name <> Surname

SELECT *
FROM   @Person

After running for about 30 seconds I get :

The SELECT query is retrieving the columns from the non clustered indexes rather than the clustered index (albeit due to the hints).

The update statement gets a wide update plan...

... and updates the indexes in sequence so it is possible to read "before" values from one index and "after" from the other.

It is also possible to retrieve two different versions of the same column value.

In the first connection, run this:

DECLARE @A VARCHAR(MAX) = 'A';
DECLARE @B VARCHAR(MAX) = 'B';

SELECT @A = REPLICATE(@A, 200000),
       @B = REPLICATE(@B, 200000);

CREATE TABLE T
  (
     V VARCHAR(MAX) NULL
  );

INSERT INTO T
VALUES     (@B);

WHILE 1 = 1
  BEGIN
      UPDATE T
      SET    V = @A;

      UPDATE T
      SET    V = @B;
  END

And then in the second, run this:

SELECT 'Setting intial Rowcount'
WHERE  1 = 0;

WHILE @@ROWCOUNT = 0
  SELECT LEFT(V, 10)  AS Left10,
         RIGHT(V, 10) AS Right10
  FROM   T WITH (NOLOCK)
  WHERE  LEFT(V, 10) <> RIGHT(V, 10);

DROP TABLE T;

Straight away, this returned the following result for me

+------------+------------+
|   Left10   |  Right10   |
+------------+------------+
| BBBBBBBBBB | AAAAAAAAAA |
+------------+------------+


How bad is a tempdb drive failure (with multiple tempdb files on different drives)?

Nope, I'm fairly confident SQL Server will fall over - I've had a few SAN attached drives disappear with SQL Server 2005. It's possible that SQL Server may not realise until it accesses TempDB on the drive that's failed.... in pre-prod, you should be able to simulate this by dropping the drive.

Unless you've carefully assessed this, I would also suggest you carefully reconsider your 16 TempDB datafile approach. The smart advice these days is to add TempDB datafiles only when you see TempDB contention (wait stats etc). If you really want to, start with 2 datafiles on the same drive.

The most recent TempDB recommendation I saw - it's Paul Randal, so essentially Mr SQL Server himself:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx


You can use coalesce, it's a function that returns its leftmost non-null argument:

select coalesce(object, subject)
from t;

Coalesce is a shorter form of:

select case when object is not null then object else subject end
from t;


What is hot spotting in the context of adding files to tempdb?

What is hot spotting?

"Hot spotting" in this context means that, even though tempdb has multiple files, all the I/O work is being done in a single file. If tempdb is busy enough to justify adding files, the imbalance that leads to hot-spotting (due to proportional fill) will be short-lived, so I think the warnings may be a little Chicken Little. In my experience, anyway.
What about hot spotting makes things much worse in tempdb?

I think it is deemed to be worse in tempdb because it takes a brunt of the write activity in most workloads. You can certainly suffer similar problems in user databases, but since you're already trying to solve a problem in tempdb...
What specific things in the DB would get much worse?

Write times, mostly. Imagine everyone trying to use the same ATM, even when there are 7 other ATMs nearby. Only so much can be written at any point in time; everything else has to wait. With more files (and enough cores to schedule the work), the I/O can be spread more evenly.

Just make sure:

you have an even number of files
all the files are the same size
all the files have the same autogrow settings
the number of files is somehow driven by number of cores
depending on version (these are enabled by default in more modern versions of SQL Server), you are using trace flags 1117 (grow all data files at once) and 1118 (don't use mixed allocation extents).


One way to achieve BDR (in the PostgreSQL) use is pgPool, which has multi-master replication with failover.
For a complete tutorial, please use the official product site or wiki even at the addresses below:

pgpool

http://www.pgpool.net/mediawiki/index.php/Main_Page

https://wiki.postgresql.org/wiki/Pgpool-II


What is the best way to know the structure of a database

You can use "database Diagram" and add all tables to get a physical representation of the tables with PK and FK and some link between table but this is certainly not as detailed as you would like it to be.

If you don't have any dev documentation for those DB, that's still bether than nothing and with some manual work to check those link between tables, you can get something that is "workable".

Thank you


MySQL Database replication for high availability

There are currently two options for MySQL 'synchronous' replication.

The first option meets your requirement of "windows OS" fairly well: MySQL Cluster, as long as you use MySQL Cluster 7.1.3+, but might have other limitations depending on your use-case.

The second is Galera, which can be used via normal MySQL, using Percona XtraDB Cluster, or MariaDB. This is the reason I put 'synchronous' in quotes above, due to the certification process Galera undergoes.

There are pros and cons of using MySQL Cluster as well as Galera. Therefore, I leave to you the requirement to research them for your purposes.


Query output conditional upon values in dataset

I am pretending your table name is number_table. You query can be divided into two separate ones -- the first excludes rows with 10 in the test column and the second includes only one row with 10 in the test column. I assume the two queries are independent. From interpreting your question, I believe this query will do the job.

SELECT * FROM number_table WHERE
    (EXISTS(SELECT * FROM number_table WHERE number = 5 OR number = 6) AND EXISTS(SELECT chr FROM number_table WHERE chr = X) AND number <> 10)
UNION
SELECT * FROM number_table WHERE
 ((EXISTS(SELECT * FROM number_table WHERE number = 4 OR number = 5))
    AND NOT EXISTS(SELECT chr FROM number_table WHERE chr = X) AND number = 10
 ) LIMIT 1;


Some thoughts on the design of this database:

For portions, is there only ever one per food? If so it belongs in the food table.
Similarly, with the nutritional facts, only per food?  If so, probably just in the food table.  If there are multiple portions, it would seem like the nutritional facts are based on the portion.
And for types, only one per food, or can a food be listed as both a lunch and dinner food?  If you have multiple portions, you might have a lunch portion and a dinner portion, and as such the type would be as part of the portions.

So, if only one portion, one nutritional fact, and one type per table, I'd just have it all in the one Food table, potentially with indexes on fields the user may search on.

(edited after more information)

Based on there being multiple portions per food, and the nutritional facts being dependent on the portion, it would make sense to put the nutritional facts in the portion table.  It would seem like your Food and Type tables can remain as you originally posted, so the Portions table looks something like this:

PORTIONS
==========
ID
PORTION
FOOD_ID
KCAL
FAT
SUGAR
PROTEINS