How to update table1 field by using other table and function

There are multiple ways to do it in Oracle.  Right of the bat I can think about two.

Straightforward, not Oracle specific (assuming shop_code is unique in table1)

update TABLE2 t2 set SHOP_NAME =
(
  SELECT GET_shop_name(t1.regitry_id)
  FROM TABLE1 T1
  WHERE t2.shop_code = t1.shop_code
);

Oracle specific syntax(requires inline view to be key-preserved - if shop_code is not a PK column in both tables you will very likely get "SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table" )

UPDATE
 (
    SELECT GET_shop_name(t1.regitry_id) AS new_name,
           t2.shop_code, t2.shop_name
    FROM TABLE2 t2
    INNER JOIN t1 ON (t2.shop_code = t1.shop_code)
  )
SET shop_name = new_name;


Connect to sql plus from command line using connection string

did you try

sqlplus username/password@host:port/service
sqlplus x/y@c:a/b

modern versions of sqlplus (version 11 or newer) understand this syntax and you don't need a tnsnames.ora file.


What utilities can I use to simulate I/O load for a DB restore and DBCC check?

SQLIO is good for doing raw disk IO load testing, but a good way to simulate a comprehensive load testing is to use the SQL Profiler tool included with most sql server installations or using the SQL Load Test utility. Here is a link to some different ways to use these utilities to achieve your goal using profiler or other stock utilites.

You can simply run profiler during a normal load that you would like to record, save it off, and replay it against the server to load test under realistic load. If you are using SQL server 2012, you can use the Distributed Replay utility for performing this same kind of testing, but from multiple sources to simulate load. If you are looking for a commercial option, you could look at Quest Software's tool called Benchmark Factory that can be used to perform SQL Server load testing.


It is possible to process individual rows and redirect the records that failed. You could even validate records before you try to insert them.

How do you want it to work?
Unless they specifically need to be processed together as a batch I would be processing individual records and storing failed records for correction and resubmission.


Flatten array with OPENJSON: OPENJSON on a value that may not be an array? [ [1] ], vs [1]

If you are strictly confident that your nested arrays will never go deeper than N levels, you can completely unwrap the array-of-arrays with N uses of APPLY. If you need to handle for arbitrary nesting levels, you can unwrap the array-of-arrays recursively using something like the following, which will produce output similar to the following

|----|-------|---------|-----|-------|------|
| id | level | path    | key | value | type |
|----|-------|---------|-----|-------|------|
| 1  | 1     | /0      | 0   | 7     | 2    |
| 1  | 1     | /1      | 1   | 2     | 2    |
| 1  | 2     | /2/0    | 0   | 6     | 2    |
| 1  | 2     | /2/1    | 1   | 7     | 2    |
| 1  | 1     | /3      | 3   | 2     | 2    |
| 1  | 1     | /4      | 4   | 10    | 2    |
|----|-------|---------|-----|-------|------|

DB Fiddle

declare @ex table (
    i int identity primary key,
    r nvarchar(max)
);
insert @ex (r)
values ('[ 7,2, [6,7], 2, 10 ]')
      ,('[ 7,2, [6,7], 2, 10 , [6,[7,8]]]')
;

with j as (
    select
        x.i as id,
        convert(int,1) as [level],
        convert(nvarchar(max),N'/')+convert(nvarchar(max),a.[key]) as [path],
        a.[key],
        a.[value],
        a.[type]
    from @ex x
    cross apply openjson(x.r) a
    union all
    select
        j. id,
        j.[level] + 1,
        j.[path] + convert(nvarchar(max),N'/')+convert(nvarchar(max),a.[key]),
        a.[key],
        a.[value],
        a.[type]
    from j
    cross apply openjson(j.value) a
    where j.type in (4,5)
       and j.level < 50 --maxrecursion
)
select *
from j
where type not in (4,5)
order by id, [path];

Testing against the 2-level deep nested array from the above example produces the following:

|----|-------|---------|-----|-------|------|
| id | level | path    | key | value | type |
|----|-------|---------|-----|-------|------|
| 2  | 1     | /0      | 0   | 7     | 2    |
| 2  | 1     | /1      | 1   | 2     | 2    |
| 2  | 2     | /2/0    | 0   | 6     | 2    |
| 2  | 2     | /2/1    | 1   | 7     | 2    |
| 2  | 1     | /3      | 3   | 2     | 2    |
| 2  | 1     | /4      | 4   | 10    | 2    |
| 2  | 2     | /5/0    | 0   | 6     | 2    |
| 2  | 3     | /5/1/0  | 0   | 7     | 2    |
| 2  | 3     | /5/1/1  | 1   | 8     | 2    |
|----|-------|---------|-----|-------|------|

See this question also for related material.


Force exact match from postgres ts_query

As filiprem says, you can combine the match and the equality by an AND.  It will use the FTS index to get candidates and then just filter out the ones that it needs to for the equality

But you can also build a regular index on 'journal_name' and then just write the query naturally. The 2nd index will take some maintenance overhead, but less than the index your already have does.  If you do this type of query a lot, you should probably add the extra index.


selecting rows that have n foreign key conditions satisfied

Try if that works for you

SELECT page_id FROM
(
SELECT co.pageid , count(case when year(co.DateContent) =2012 THEN 1 END) AS cnt1,
count(CASE WHEN co.Title='Title' and co.Content like '%test%' THEN 1 END) AS cnt2
FROM cmscontentpublished as co
JOIN cmsnode as cm on co.PageID = cm.ID
WHERE cm.parentid=365 AND ((co.Title='Title' and co.Content like '%test%') OR
(year(co.DateContent) =2012))
)a
WHERE (cnt1 + cnt2 )= 2
-- if you want page_id only if each condition is satisfied exactly once,
-- then where should look "cnt1 = 1 and cnt2 =1"


SQL Server - Separating data, log, and TempDB files on a SAN

One thing to consider is that the log files are sequential writes where as the data files are non sequential.  That is one of the reasons for separate LUNs.  Log files write faster if they are on their own LUN because the spindles don't have to skip around, just write sequential.  If you add in a data file then the spindles have to skip around and you lose some performance.  I'm hoping I got the right terminology there as I'm not all that familiar with SANs themselves.  The idea behind it should be however.

Frequently vendor recommendations are wrong when it comes to SQL Server.  Just because SQL Server has different needs than most applications that use a SAN.


How to select 3 records before x ID and 5 records after x ID with Laravel Eloquent?

(" or mysql")

( SELECT ... FROM ...
        WHERE id < x ORDER BY id DESC LIMIT 3 )
UNION ALL
( SELECT ... FROM ...
        WHERE id > x ORDER BY id ASC LIMIT 5 )
ORDER BY id   -- optional

If you want to include id=x, then change < to <= or > to >=.

How to turn that into Laravel?  I don't know.  Maybe you will need something clumsier.


This sounds like the classic case of History versus CurrentState.  Sure, have two tables.  Insert a new row into History and overwrite the row keyed to the user in the other table.

That does lead to "redundant" data, which is a 'no-no' by DB purists.  So here is another approach:

Insert into History, as above.  Get back the AUTO_INCREMENT id.  Then Update the CurrentState with just the id (and user_id).  Then, for accessing the current info, use a JOIN between the tables.


oracle global index vs local index on a partitioned table

Why would you want to index CREATED_TIME if you're already partitioning on CREATED_TIME?  If you are regularly querying the data looking for date ranges much smaller than your partition grain, that is, you're regularly querying for date ranges of a couple hours, you probably want to adjust your partitioning strategy to create partitions more frequently.  If you query recent data in smaller intervals than old data-- for example, you regularly aggregate by hour over the past day, aggregate by day over the past week, and aggregate by month over the past year-- you may want to merge smaller partitions together as they age (potentially in addition to compressing older partitions).

Additionally, are you sure that you would want to index CREATED_TIME rather than creating a materialized view that pre-aggregates the data by some smaller interval than the partition?  If you're aggregating data in various queries by hour, day, and month, for example, you'd generally be better served by creating a materialized view that pre-aggregated the data at the smallest grain (hour) and an Oracle dimension object that allowed query rewrite to use the materialized view to aggregate the hourly rows into daily or monthly results rather than trying to read all the data from the base table.


Is the "comments" field in pgAdmin 4 a pgAdmin thing, or is it a PostgreSQL feature?

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


How can I assign values (-1, 0, 1) to the difference between consecutive rows in a MySQL table having 20 columns and 20 rows

You can use user defined variables for this. You can store the value of the current row's column in a variable, when the next row is processed, the variable still holds the value of the "previous" row.

Here's how it works.
You need to initialize the variables you need first. You can do this in a subquery.

SELECT
*
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery

Like I already said, the rows are processed one after another. In a relational database there is no order, unless you specify it. This is very important here, when you don't specify an order, you might not get the previous row, but a random or simply wrong row.

SELECT
*
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery
ORDER BY the_column_that_defines_the_order

In the SELECT clause the order is also very important. When you assign first and then do your calculations, the variables actually hold the value of the current row, not the previous one.

SELECT
CASE WHEN @prev_value < your_column THEN 1
     WHEN @prev_value > your_column THEN -1
     ELSE 0 END AS your_comparison
, @prev_value := your_column
FROM your_table
, (SELECT @prev_value := NULL) var_init_subquery
ORDER BY the_column_that_defines_the_order

Okay, now we have a problem. The @prev_value value is NULL, when the very first row is processed. There are two ways to prevent this.

First, use the COALESCE() function to replace the NULL value with something else or the column you're comparing to.

CASE WHEN COALESCE(@prev_value, your_column) < your_column THEN 1
     WHEN COALESCE(@prev_value, your_column) > your_column THEN -1
     ELSE 0 END AS your_comparison

In your case that's actually not necessary, since it would result in the ELSE part anyway, which is probably what you want. I'm just mentioning it.

Second, assign the value of your choice in the initializing subquery.

SELECT
CASE WHEN @prev_value < your_column THEN 1
     WHEN @prev_value > your_column THEN -1
     ELSE 0 END AS your_comparison
, @prev_value := your_column
FROM your_table
, (SELECT @prev_value := your_column FROM your_table ORDER BY the_column_that_defines_the_order LIMIT 1) var_init_subquery
ORDER BY the_column_that_defines_the_order

When you want the columns to be in a different order, you can just put above query in a subquery.


How to join unknown table from Xref table?

One way would be to use a cursor and dynamic sql. Granted the solution below would need to be modified to accommodate your actual data, especially if the number of columns is variable, but for the example you gave it does work.

CREATE TABLE Xref(name varchar(20),TableColumn varchar(10),TableName varchar(20))

CREATE TABLE SensorData12([time] datetime2(0), [2429] decimal(3,1),[2430] decimal(3,1))

INSERT INTO [Xref]
VALUES('Sensor_a1', '2429', 'SensorData12'),
     ('Sensor_a2', '2430', 'SensorData12');

INSERT INTO [SensorData12]
VALUES ('2015-10-01 00:00:48', 88.7 ,37.1)

DECLARE @tablename sysname,
       @col1      sysname,
       @col2      sysname,
       @sql       nvarchar(max);

DECLARE c_tables CURSOR LOCAL
FOR SELECT [Tablename],
         MAX(CASE WHEN [name] = 'Sensor_a1' THEN [TableColumn]
            END) AS [col1],
         MAX(CASE WHEN [name] = 'Sensor_a2' THEN [TableColumn]
            END) AS [col2]
    FROM [Xref]
    GROUP BY [TableName];
OPEN c_tables;

WHILE 1 = 1
    BEGIN
       FETCH NEXT FROM c_tables INTO @tablename,
                               @col1,
                               @col2;

       SET @sql = 'select top 1 [time],[' + @col1 + '],[' + @col2 + '] from [' + @tablename + '];';

       EXEC [sp_executesql]
           @sql;

       IF @@FETCH_STATUS < 0
          BEGIN BREAK
          END;

    END;
CLOSE c_tables;
DEALLOCATE c_tables;

DROP TABLE [Xref];
DROP TABLE [SensorData12];


How to Develop Union Reports from Single Tenant Databases?

Given that the data is in 700 separate databases, and likely to stay there, and that you want reporting to show the current state of the transactional database I see basically two choices. One is send the query to the data; the second is send the data to the query.

Your view would be an example of sending the query to the data. Each client DB performs some of the work and the reporting DB collates and formats the results. There will be a couple of challenges with this. Cross-database queries are slower than single database queries. I don't have numbers to quote, but this is the received wisdom. Likely the diverse databases will be accessed serially - first DB1 is read, then DB2 is read, then DB3 etc. - meaning the reporting query's execution time will be (at least) 700 times an individual DB's query time. Ouch. There may be clever ticks to force parallel processing in this circumstance. I don't know of any. Certainly a SQL CLR procedure could be written to spawn a thread for each client DB, then correlate the results. A sort of Hadoop Lite, if you will. If I were going to those lengths, however, I think I'd prefer to have BIML generate the necessary packages and run them on demand. It would be a close-run thing to decide which I disliked more - 700 extra threads running inside the SQL Server process or 700 SSIS packages running simultaneously.

By sending the data to the query I mean having a second copy of the data in a single reporting database. It is against this copy that report queries run. This strategy comes in two flavours - pull and push. Pull is the reporting DB sucking data from client DBs according to the reporting DB's schedule and requirements. Your second suggestion using a stored procedure would fall into this approach. BIML-generated SSIS packages could also be used for this. It will have the normal issues with polling (figuring out what's changed, wasted cycles when nothing has). SQL Server has features to help with this (Change Tracking and Change Data Capture), but still.

Push techniques are generally favoured because they avoid the polling overhead. A simple trigger may suffice, duplicating the data to the reporting DB or a staging table on each write to a client DB. This is extra work in a client-facing transaction, however, and extra opportunity for failure. For this reason asynchronous techniques would be preferable. One option is to have the application put a message on a reliable service which the reporting application picks up and processes. There is even a message service baked into SQL Server which could be used (Service Broker). Processing, coordination, failure and recovery are all you concern in client code, however.

SQL Server comes with a number of replication options. These reliably copy data from a source to a destination. Initiation, synchronisation and recovery are all available. I think this is likely to be your best option. Configure SQL Server to replicate changes in client DBs asynchronously but reliably to the reporting DB. Run queries off this reporting DB.


Why do experienced SQL Server DBA's qualify examples with the default schema dbo?

MS SQL Server uses a fixed search order for unqualified names (the user's default schema, and the dbo schema). So if you do not want your queries to depend on that what the user's default schema happens to be, you always have to use schema-qualified names.

In PostgreSQL, the schema search order can be modified dynmically. The documentation says:

  Qualified names are tedious to write, and it's often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in.

In other words, the Postgres schema lookup mechanism was specifically designed to allow omitting schema names in most queries.

(The default Postgres search path is "$user", public, which is essentially the same as that of MS SQL Server, and can lead to the same problems. But in practice, the default databases privileges do not allow users to actually create their own schemas.)


Can TRUNCATE TABLE be overloaded on SQL Server?

I'm looking for a way to "overload" the TRUNCATE TABLE to be something
  like:

  DELETE FROM X; --remove all rows
  TRUNCATE TABLE X; --truncate the
  empty table

When you delete from a table, you are removing rows. You can specify what rows to delete with a WHERE clause. When you TRUNCATE a table, it is not logging what rows are deleted and it does perform minimal logging. TRUNCATEis a great way to clear a table without causing the Transaction Logs to balloon.

  Or maybe activate some DDL trigger before truncate, so I can check
  which rows are gonna be truncated.

You do not need a trigger for this. A TRUNCATE will remove all rows in the table, you know all rows are going to be removed in the process. No survivors.

If you would like to capture the event to know who truncated what or when, you can do this with SQL Audit, as shown by Mohd Sufian on SQL Ship.

I don't believe you can fire trigger from a Truncate, here is the DDL List on MSDN of what DDL events can be fired from a trigger.

You can also use extended events to capture Truncate events as Jonathan Kehayias shows on MSDN.

If you want to prevent users from running Truncate, you can create a view or another object with SCHEMABINDING. This prevents DDL statements from altering the table in question, which includes Truncate.

One last note about Truncate is that if you have an identity column, it will reseed the value. I have seen people use the RESEED function and Truncate together. They will capture the last identity used, store it into a variable table, truncate the table removing all records with minimal logging and resetting the identity column, then reseed the last incremental. Usually if you truncate though, you don't care about the reseed. (Not always true, but usually.)

I truncate primarily on staging tables or when I need to reset an instance for testing. I rarely truncate tables otherwise because I care about the referential data integrity and which rows are removed from the tables. When I care about neither, I truncate.

I would highly recommend to read more on both Delete and Truncate, they will achieve similar results but are two very different things.

Atul Gaikwad on MSSQLTips wrote a nice quick article on some of those differences.

Now let's look at this question:

  Can I count the rows that will be affected by the truncate from a
  third party application?

Ideally, the truncate event is scheduled or it can be planned for. Even more ideally, you will be able to alter the code or add something in so it can log information for you. From your post, I'm going to assume you can't plan, schedule, or alter the code.

So how do you track what rows will be affected by truncate if you had to, with absolute certainty?

I personally would create two tables, a trigger, and a procedure.

The trigger would be based on Insert or delete tasks and it would insert into a table the number of rows affected, the table name, and the datetime. So if your process inserted 6 rows, the trigger would insert the value 6 indicating 6 new records flowed in. If you deleted 10 rows, it would insert a value of -10 indicating 10 records are now gone.

I would want to create an extra column for notes or events. I would want the audit or extended events to log to this table the day / time the truncate occurred. They won't be able to tell you how many rows, but it can tell you when and who. That's what's really important at this moment.

The second table would be an aggregate of the first table as I assume you may have many transactions in a day. This would tell you the net total of records at the end of the day and also tell you when truncates occurred. This means you would know how many records were in the table just prior to truncation, meeting your requirements.

The procedure would do the aggregation between tables and it would also perform cleanup work on the first table.


In bypass mode Hive clients directly access the metastore database instead of using the Hive Metastore Server for metastore information.

Go to the Hive service.
Click the Configuration tab.
Select Scope > HIVE service_name (Service-Wide)
Select Category > Advanced.
Deselect the Bypass Hive Metastore Server property.
Click Save Changes to commit the changes.
Re-deploy Hive client configurations.
Restart Hive and any Hue or Impala services configured to use that Hive service.

This might work for you.


Rebuild large Oracle 10g index with insufficient TEMP space?

What is TEMP used for in Oracle? As a scratch area for doing sorts that won't fit into main memory. So the issue is that you need to do a big sort in order to create your index, and you don't have a large enough PGA for it. So your options are: a larger PGA or a "temporary temporary" tablespace; simply create one big enough, make it your user's temporary tablespace (ALTER USER xxx TEMPORARY TABLESPACE temptemp;) build the index, then set your user back and drop it.


SQL Server 2008 R2 - Set tempDB size on full disk

By setting Tempdb to be 30GB, you are adding the amount of time required for SQL Server to fully restart.  Every time SQL starts, tempdb is recreated.  Allocating that much disk space may take longer than desired.  I would go with 2GB, but let auto-grow by 64MB at a time.

I would also change the MODEL database to grow by 128MB at a time for data, 64MB for the log.  This setting will be applied to all new databases and will prevent the type of internal fragmentation that can happen with too many auto-growths (i.e. the default 10%)

ALTER DATABASE [MODEL] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH=128000KB )
ALTER DATABASE [MODEL] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH=64000KB )


Where can I get best resources to learn sql?

SQL is a declarative data query language. PostgreSQL is a database which supports SQL (as do all RDBMS's). Oracle supports SQL, so does MySQL &c. There are differences - PostgreSQL is very ANSI compliant, whereas Oracle and MySQL not so much.

If you want to learn, Google SQL tutorials and maybe use dbfiddle.uk (more servers) or db-fiddle.com (a bit easier) to test - or just download PostgreSQL/MySQL/Firebird. The three of these are Open Source and free to use for any purpose, bar MySQL: you can't distribute a closed-source application using MySQL without paying a licence fee to Oracle because of the dual GPL/commercial licencing.

From here:

  MySQL Commercial License for OEMs, ISVs and VARs Oracle provides its MySQL database server and MySQL Client Libraries under a dual
  license model designed to meet the development and distribution needs
  of both commercial distributors (such as OEMs, ISVs and VARs) and open
  source projects.

What this means is that basically, if you want to distribute MySQL based apps without paying a fee, your app must be under the GPL version 2.

You can also download Oracle XE for free (latest version as of 05/2018) is still 11g - limitations from here:

  Oracle Database XE can be installed on any size host machine with any
  number of CPUs (one database per machine), but XE will store up to
  11GB of user data, use up to 1GB of memory, and use one CPU on the
  host machine.

There's also:

Microsoft SQL Server Express with limitations 1GB RAM and 10GB data - from here and here.

Your question is very general (too broad) for this forum - you need to read a couple of books/tutorials and come back with specific questions (with what you've tried - use one of the fiddles above for your DDL for your sample data (CREATE TABLE...), DML (INSERT INTO blah VALUES (...);) in your questions.


Duplicate key value violates a unique constraint

The id field in your ny_stations table does not seem to be defined as a serial, so it is expected that pg_get_serial_sequence will return nothing.

The duplicate you get relates to one of the records in your SELECT DISTINCT ... FROM ny_raw_trips ... is returning two rows with the same id:

SELECT start_station_id, COUNT(*) FROM (
  SELECT DISTINCT start_station_id, start_station_name, start_station_latitude, start_station_longitude
  FROM ny_raw_trips
  WHERE start_station_id NOT IN (SELECT id FROM ny_stations)
  ) a
GROUP BY start_station_id
HAVING COUNT(*) > 1;

You could list the rows that are introducing the duplication like this:

WITH src AS (
  SELECT DISTINCT start_station_id, start_station_name, start_station_latitude, start_station_longitude
  FROM ny_raw_trips
  WHERE start_station_id NOT IN (SELECT id FROM ny_stations)
  )
SELECT *
FROM src
WHERE start_station_id IN (SELECT start_station_id FROM src GROUP BY start_station_id HAVING COUNT(*) > 1)
ORDER BY start_station_id;

Edit

Once you find the offending duplicates, should you consider that the first occurrence of each case is good enough (e.g. trivial differences in description or coordinate fields), you can use DISTINCT ON:

INSERT into ny_stations (id, name, latitude, longitude)
SELECT DISTINCT ON (start_station_id) start_station_id, start_station_name, start_station_latitude, start_station_longitude
FROM ny_raw_trips
WHERE start_station_id NOT IN (SELECT id FROM ny_stations)
ORDER BY start_station_id;


Storing Live Timeseries Data in SQL Server Azure Database

With that design it is expected that the query requires a full scan of the columnstore.  That should be very fast at that scale, but the IO stats show that you're reading from disk.  If you look at the wait stats for the actual execution plan you should see that it's more IO waits than CPU utilization.
You could scale up the database to get more cache memory, or perhaps partition the columnstore either by componentId or timestamp.  Whenever you partition a columnstore you want to ensure that partitions have at least 1M rows.


Get the underlying foreign key constrains based upon a view

You can check which tables are used by a view using this statement:

select vtu.table_schema,
       vtu.table_name
from information_schema.view_table_usage vtu
  join pg_class cl on cl.oid = (quote_ident(vtu.table_schema)||'.'||quote_ident(vtu.table_name))::regclass
where (view_schema, view_name) = ('public', 'test');

If that returns only a single table, you can query the system for foreign keys referencing that (single) table.


Elegant way of sending e-mails with Service Broker

is there a way I can send messages directly to the Database Mail queue using Service Broker

When you invoke sp_send_dbmail that is exactly what is happening.

  I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to
  send e-mails to operators for each message on the queue

Then do just that. Create an activated stored procedure on the queue of your service that receives the notifications and have it RECEIVE the message and invokes sp_send_dbmail.

I think you are trying to 'cut out the middleman' and have the EVENT NOTIFICATION send the notification directly to the DB Mail queue. That is not possible because the code that handles the messages arriving in the DB Mail queue expects the messages to have a certain format (ie. the one created by sp_send_dbmail). The messages sent by Event Notifications would have a completely different format and the DB Mail processor would choke as it does not understand them. In order to prevent exactly such situation Service Broker services are bound to specific contracts and DB Mail service does not accept the http://schemas.microsoft.com/SQL/Notifications/PostEventNotification contract used by Event Notifications.


It is most likely because you have specified the size for INT: ID INT( 11 ). Why do you need it? Try this syntax (add NOT NULL for Primary key):

CREATE TABLE IF NOT EXISTS Requests
      (
        ID INT NOT NULL PRIMARY KEY AUTOINCREMENT,
        Sender VARCHAR( 225 ),
        Recipient VARCHAR( 225 )
      )


postgres query performance: view vs function

A view cannot help you producing an aggregate based on unknown parameters (business_id, start_date and end_date).  It is nothing else than a given query, stored permanently in the database for later reuse.  (Well, the implementation is more complicated, but that does not affect their usage.)

Considering your query, the furthest you can get with a view is

CREATE VIEW staff_commission AS
SELECT
    user_id AS staff_id,
    business_id,
    amount,
    start_date,
    end_date
FROM transaction
WHERE
    type = 'staff' AND
    kind = 'commission';

Everything that is known beforehand is there, plus the columns you need for producing the desired output.

For obtaining the latter, you have to create a function in any case (optionally, working from the view).  Building on the view makes sense when you have several queries that filter the date the same way.

The performance of all these will be the very same.  If the indexes makes sense or not depends very much on the actual data.  (I am pretty sure you don't need all of them, though.)  Without knowing anything about those, it is very hard to guess which improvements are needed.  Try what you have, check the EXPLAIN ANALYZE output and see if there is something missing.

Finally about caching: PostgreSQL is very smart about this.  Going into details would fill a chapter or two in a book, but I would not worry about this until I see too many disk reads (which can be seen from EXPLAIN (ANALYZE, BUFFERS)).


Configure Database Mail
Create a sql agent job to run below t-sql (you dont need a stored procedure for what you are asking, but below code can be easily converted into a stored procedure --> I will leave that up to you - hint: just look for Create procedure as ..)

---- first check if there are any rows returned ... as we dont want to send empty email ....
if exists (
        select 1
        from AccountExtensionBase as a
        inner join CustomerAddressBase as b on a.AccountId = b.ParentId
        where New_AccountStage = 7
            and AddressTypeCode is null
        )
begin
    if object_id('#final_results') is not null
        drop table dbo.#final_results;

    select COUNT(case
                when New_accounttype = 1
                    then New_AccountType
                else null
                end) as 'New Connections'
        ,COUNT(case
                when New_accounttype = 2
                    then New_AccountType
                else null
                end) as 'Domestic Metered'
        ,COUNT(case
                when New_accounttype = 3
                    then New_AccountType
                else null
                end) as 'Commercial Metered Low'
        ,COUNT(case
                when New_accounttype = 4
                    then New_AccountType
                else null
                end) as 'Commerical Metered High'
        ,COUNT(case
                when New_accounttype = 5
                    then New_AccountType
                else null
                end) as 'Domestic Keypad'
        ,COUNT(case
                when New_accounttype = 6
                    then New_AccountType
                else null
                end) as 'Generator'
        ,COUNT(case
                when New_accounttype = 7
                    then New_AccountType
                else null
                end) as 'Commercial Keypad'
        ,COUNT(*) as 'Total Live'
    into dbo.#final_results -----<--- put results into temp table for easy processing ...........
    from AccountExtensionBase as a
    inner join CustomerAddressBase as b on a.AccountId = b.ParentId
    where New_AccountStage = 7
        and AddressTypeCode is null

    --- prepare for sending email ... since we have something to send ....
    declare @tableHTML nvarchar(MAX)
        ,@tableHTML2 nvarchar(MAX)
        ,@tableConnErr nvarchar(MAX)
        ,@tableDBConnErr nvarchar(MAX);
    declare @Mailsubject nvarchar(255)
        ,@mail_recipients nvarchar(255)

    set @mail_recipients = 'dbagroup@somecompany.com' --<-- CHANGE HERE
    set @Mailsubject = 'Setting query to run as a stored procedure and email the results' --<-- CHANGE HERE
    set @tableHTML = N'
Below is the report as per [http://dba.stackexchange.com/q/104030/8783] :
'
    set @tableHTML = @tableHTML + N'
' + N'' + N'
New Connections	' + N'Domestic Metered	' + N'Commercial Metered Low	' + N'Commercial Metered High	' + N'Domestic Keypad	' + N'Generator	' + N'Commercial Keypad	' + N'Total Live	' + N'
' + ISNULL(CAST((
                    select td = [New Connections],''
                        ,td = [Domestic Metered],''
                        ,td = [Commercial Metered Low],''
                        ,td = [Commercial Metered High],''
                        ,td = [Domestic Keypad] ,''
                        ,td = [Generator],''
                        ,td = [Commercial Keypad],''
                        ,td = [Total Live],''
                    from (
                        select *
                        from dbo.#final_results
                        ) A
                    order by 1 asc
                    for xml PATH('tr')
                        ,TYPE
                    ) as nvarchar(MAX)), '')
    set @tableHTML = @tableHTML + N'' + N'
';
    set @tableHTML = @tableHTML + N'
Report By: @TheRockStarDBA ;Generated On: ' + convert(varchar, getdate()) + '
'

    --print @tableHTML
    exec msdb.dbo.sp_send_dbmail    @profile_name = 'YOUR MAIL PROFILE'----< CHANGE HERE !
                                   ,@recipients = @mail_recipients
                                    ,@subject = @Mailsubject
                                    ,@body = @tableHTML
                                    ,@body_format = 'HTML';
end

Add a schedule to the above job.


MySQL too many connections on shared server

max_connections is probably set to 151.  And this is probably because of running a newer version of MySQL.
Assuming BlueHost allows you to change the config file (and they may even have a UI for doing such), change that to, say, 1000.
Having hundreds of 'simultaneous' connections is not, in itself, a "bad" thing.  However, it may be indicative of some other problem.
Do the connections hang on longer than necessary?  Is there some form of 'connection pooling'?  What is the client -- does it have a limit on connections to it?  Are you also having CPU or other performance problems?  How long does one user stay online and connected?


Calculate percentage of records that have an association in another table

In order to calculate a percentage like that you need a count of rows in table b divided by a count of rows in table a.

Something like this:

USE tempdb;

DROP TABLE IF EXISTS dbo.b;
DROP TABLE IF EXISTS dbo.a;
GO

CREATE TABLE dbo.a
(
    a_id int NOT NULL
        CONSTRAINT a_pk
        PRIMARY KEY CLUSTERED
    , someData varchar(10) NOT NULL
    , rowDate datetime NOT NULL
);

CREATE TABLE dbo.b
(
    b_id int NOT NULL
        CONSTRAINT b_pk
        PRIMARY KEY CLUSTERED
    , a_id int NOT NULL
        CONSTRAINT b_a_id_fk
        FOREIGN KEY
        REFERENCES a(a_id)
    , someOtherData varchar(10) NOT NULL
);

INSERT INTO dbo.a (a_id, someData, rowDate)
VALUES (1, 'abcdefghij', GETDATE())
    , (2, 'abcdefghik', GETDATE());

INSERT INTO dbo.b (b_id, a_id, someOtherData)
VALUES (1, 1, 'blah');

;WITH a AS
(
    SELECT cntA = COUNT(1)
    FROM dbo.a
)
, b AS
(
    SELECT cntB = COUNT(1)
    FROM dbo.b
        INNER JOIN dbo.a ON b.a_id = a.a_id
)
SELECT a.cntA
    , b.cntB
    , [percentage] = (CONVERT(decimal(10, 2), b.cntB) / a.cntA) * 100
FROM a, b;

The output looks like:

╔══════╦══════╦══════════════════╗
║ cntA ║ cntB ║    percentage    ║
╠══════╬══════╬══════════════════╣
║    2 ║    1 ║ 50.0000000000000 ║
╚══════╩══════╩══════════════════╝

When you JOIN the rows, as in your example query, you're only going to see rows that match in both tables, since JOIN is really INNER JOIN.  If you use a LEFT JOIN instead, you'll get all rows from the table on the left side of the join, and only matching rows from the right side of the join.  Consider:

SELECT *
FROM dbo.a
    INNER JOIN dbo.b ON a.a_id = b.a_id;

Results:

╔══════╦════════════╦═════════════════════════╦══════╦══════╦═══════════════╗
║ a_id ║  someData  ║         rowDate         ║ b_id ║ a_id ║ someOtherData ║
╠══════╬════════════╬═════════════════════════╬══════╬══════╬═══════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:07:33.340 ║    1 ║    1 ║ blah          ║
╚══════╩════════════╩═════════════════════════╩══════╩══════╩═══════════════╝

Note, we're only seeing a single row of results; rows that exist in both tables are returned by the query.

The left join version looks like:

SELECT *
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id;

Results:

╔══════╦════════════╦═════════════════════════╦══════╦══════╦═══════════════╗
║ a_id ║  someData  ║         rowDate         ║ b_id ║ a_id ║ someOtherData ║
╠══════╬════════════╬═════════════════════════╬══════╬══════╬═══════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:07:33.340 ║ 1    ║ 1    ║ blah          ║
║    2 ║ abcdefghik ║ 2019-12-06 07:07:33.340 ║ NULL ║ NULL ║ NULL          ║
╚══════╩════════════╩═════════════════════════╩══════╩══════╩═══════════════╝

As you can see, this is return rows from both tables; where there is no row in b, we see NULL data.

You can capitalize on the left join to get you the results you want:

SELECT [Count_of_a] = COUNT(a.a_id)
    , [Count_of_b] = COUNT(b.a_id)
    , [Percentage] = CONVERT(decimal(10,2), COUNT(b.a_id)) / COUNT(a.a_id) * 100
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id;

Results:

╔════════════╦════════════╦═════════════════╗
║ Count_of_a ║ Count_of_b ║   Percentage    ║
╠════════════╬════════════╬═════════════════╣
║          2 ║          1 ║ 50.000000000000 ║
╚════════════╩════════════╩═════════════════╝

You can extend that to include a group-by on the date column.  But, first lets insert a couple of more rows in our sample tables:

INSERT INTO dbo.a (a_id, someData, rowDate)
VALUES (3, 'abcdefghil', GETDATE())
    , (4, 'abcdefghim', GETDATE());

INSERT INTO dbo.b (b_id, a_id, someOtherData)
VALUES (2, 1, 'blah')
    , (3, 4, 'blah');

SELECT *
FROM dbo.a;

SELECT *
FROM dbo.b;

Contents of both tables:

╔══════╦════════════╦═════════════════════════╗
║ a_id ║  someData  ║         rowDate         ║
╠══════╬════════════╬═════════════════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:27:24.643 ║
║    2 ║ abcdefghik ║ 2019-12-06 07:27:24.643 ║
║    3 ║ abcdefghil ║ 2019-12-06 07:27:24.643 ║
║    4 ║ abcdefghim ║ 2019-12-06 07:27:24.643 ║
╚══════╩════════════╩═════════════════════════╝

╔══════╦══════╦═══════════════╗
║ b_id ║ a_id ║ someOtherData ║
╠══════╬══════╬═══════════════╣
║    1 ║    1 ║ blah          ║
║    2 ║    1 ║ blah          ║
║    3 ║    4 ║ blah          ║
╚══════╩══════╩═══════════════╝

The following query counts the distinct key values from each table, showing the percentage of rows that exist in table b compared to table a:

SELECT a.rowDate
    , a.someData
    , [Count_of_a] = COUNT(DISTINCT a.a_id)
    , [Count_of_b] = COUNT(DISTINCT b.b_id)
    , [Percentage] = CONVERT(decimal(10,2), COUNT(DISTINCT b.b_id)) / COUNT(DISTINCT a.a_id) * 100
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id
GROUP BY a.rowDate, a.someData;

Results:

╔═════════════════════════╦════════════╦════════════╦════════════╦═══════════════════╗
║         rowDate         ║  someData  ║ Count_of_a ║ Count_of_b ║    Percentage     ║
╠═════════════════════════╬════════════╬════════════╬════════════╬═══════════════════╣
║ 2019-12-06 07:27:24.643 ║ abcdefghij ║          1 ║          2 ║ 200.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghik ║          1 ║          0 ║   0.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghil ║          1 ║          0 ║   0.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghim ║          1 ║          1 ║ 100.0000000000000 ║
╚═════════════════════════╩════════════╩════════════╩════════════╩═══════════════════╝

As you can see in the first row, there is a count of 2 rows in b, but only 1 row matching in a; hence we get a percentage value of 200%.

The DISTINCT keywords inside each COUNT(...) aggregate ensure we only count unique values for the keys in a and b.  Without that keyword, SQL Server will count duplicates from a where more rows exist in b for the given a.a_id key value.


Ostensibly, this represents the uniqifier used in the non-unique clustered index. Is that the case?

Yes.

  What is the purpose of this Uniq1002 column in this index scan?

Each row in the nonclustered index must be associated with exactly one row in the base table so that Bookmark Lookups (RID or Key) work correctly. This mapping is provided by the "row locator".

For heap tables, the row locator is the RID. For clustered row store tables, it is the clustering key(including the uniquifier where necessary).

For the Key Lookup in your plan to work, it must have access to the row locator. This includes the uniquifier, so it must be emitted by the nonclustered index scan.

The uniquifier is stored in the variable-length portion of the row so it only takes up space when needed (i.e. when a duplicate key actually exists).

  Is a column named like that always the clustered index uniquifier?

Yes. The uniquifier column is always named UniqXXXX. The row locator associated with heap tables is named BmkXXXX. The row locator for a columnstore table is named ColStoreLocXXXX.

Observing the uniquifier

It is possible to directly observe the values of the uniquifier on SQL Server versions that contain a functional query_trace_column_values Extended Event.

This undocumented and unsupported event is in the Debug channel. It was introduced with SQL Server 2016, and stopped working around CU11 of SQL Server 2017.

For example:

CREATE TABLE #T (c1 integer NULL INDEX ic1 CLUSTERED, c2 integer NULL INDEX ic2 UNIQUE, c3 integer NULL);
GO
INSERT #T
    (c1, c2, c3)
VALUES
    (100, 101, 0),
    (100, 102, 1),
    (100, 103, 2);
GO
DBCC TRACEON (2486);
SET STATISTICS XML ON;
SELECT T.* FROM #T AS T WITH (INDEX(ic2));
SET STATISTICS XML OFF;
DBCC TRACEOFF (2486);
GO
DROP TABLE #T;

Has the plan:

It produces event output like the following on SQL Server 2016:


why this is not work??

Wrong and in UPDATE section, comma needed;
The trigger contains single statement, so BEGIN-END block is excess.

CREATE TRIGGER prosesqc2
AFTER INSERT ON quality2
FOR EACH ROW
INSERT INTO stock_barang
    SET  idbarang     = NEW.idbarang,
         lolos_qc_sfl = NEW.terima,
         dalam_proses = NEW.terima
ON DUPLICATE KEY
UPDATE lolos_qc_sfl = lolos_qc_sfl + NEW.terima,
       dalam_proses = dalam_proses - NEW.terima;


Near identical MySQL deployments behaving very different - High CPU Usage problem

This sounds like a configuration issue. INSERT/UPDATE statements result in writes, so you should look at the write path. Check the following:

Is write caching on the disks enabled?
Are the disks configured the same way (RAID geometry, file system, mount options, controller)?
Are your InnoDB transaction logs the same size and configuration (innodb_log_% options)?
Are your disk flushing options configured the same (sync_%, innodb_flush_%)?


Native checksum table says there's a difference, pt-table-checksum does not

Don't worry, your situation is not unique. I encountered the exact same problem with mk-table-checksum years ago (See my Nov 16, 2012 post Maatkit shows the MySQL replication error in one table, but won't fix it)

Here is something frightening from the MySQL Documentation on CHECKSUM TABLE

  The checksum value depends on the table row format. If the row format
  changes, the checksum also changes. For example, the storage format
  for VARCHAR changed between MySQL 4.1 and 5.0, so if a 4.1 table is
  upgraded to MySQL 5.0, the checksum value may change.

  Important If the checksums for two tables are different, then it is
  almost certain that the tables are different in some way. However,
  because the hashing function used by CHECKSUM TABLE is not guaranteed
  to be collision-free, there is a slight chance that two tables which
  are not identical can produce the same checksum.

I meniotned this caveat before back in January 2012 : How to check the consistency of tables in mysql

Sometimes, I just like directly using mk-table-sync (sorry Percona, I'm old fashioned)  with the --print options and tell me the SQL differences. As the MySQL Documentation says, there could be differences because of the VARCHAR alignment or ROW FORMAT. If mk-table-sync/pt-table-sync insists there are no changes, you could trust it 99.9 % of the time. If you don't trust it, mysqldump the table and reload it. Chances are the checksum may not match again.


Finding reason for transaction log 'tempdb' full due to 'ACTIVE_TRANSACTION'

You can use this query to get a list of the actively running queries throughout the server including their process ID, how long they've been running for, which database they're running in, who ran them, wait stats info, blocking query IDs, etc. I recommend putting it inside of a stored procedure so that you can easily call it as needed (even with it's default parameters) and you can routinely schedule it with a SQL Agent job to log the results to a table.
DECLARE @Database VARCHAR(100) = NULL
DECLARE @EntityName VARCHAR(100) = NULL
DECLARE @ExcludeMyQueries BIT = 0
DECLARE @RunningThreadsOnly BIT = 0
DECLARE @LocksOnly BIT = 0
DECLARE @MinDuration INT = 0
DECLARE @OutputMode INT = 0 -- TODO: Setup formally documented modes, and clean up usage below, can even have different kinds of modes like Verbose, vs EmergenciesOnly, etc

DECLARE @ExecutionDate DATETIME = GETDATE();

SELECT
    SP.HostName,
    SP.SPID,
    ER.request_id AS RequestId,
    ER.percent_complete AS PercentComplete,
    DATEDIFF(s, start_time, @ExecutionDate) AS Duration,
    CAST(((DATEDIFF(s, start_time, @ExecutionDate)) / 3600) AS VARCHAR) + ' hour(s), '
        + CAST((DATEDIFF(s, start_time, @ExecutionDate) % 3600) / 60 AS VARCHAR) + 'min, '
        + CAST((DATEDIFF(s, start_time, @ExecutionDate) % 60) AS VARCHAR) + ' sec' AS RunningTime,
    CAST((estimated_completion_time / 3600000) AS VARCHAR) + ' hour(s), '
        + CAST((estimated_completion_time % 3600000) / 60000 AS VARCHAR) + 'min, '
        + CAST((estimated_completion_time % 60000) / 1000 AS VARCHAR) + ' sec' AS EstimatedTimeRemaining,
    DATEADD(SECOND, estimated_completion_time/1000, @ExecutionDate) AS EstimatedCompletionDate,
    ER.Command,
    ER.blocking_session_id AS BlockingSessionId,
    LastWaitType,
    SP.[DBID],
    DB_NAME(SP.[DBID]) AS DbName,
    --[TEXT] AS EntityText,
    CPU,
    ER.plan_handle AS PlanHandle,
    ER.query_plan_hash AS QueryPlanHash,
    LOGIN_TIME AS LoginTime,
    LOGINAME AS LoginName,
    SP.[Status],
    [PROGRAM_NAME] AS ProgramName,
    NT_DOMAIN AS NT_Domain,
    NT_USERNAME AS NT_Username,
    @@SERVERNAME AS ServerName,
    @ExecutionDate AS ExecutionDate
INTO #ExecutingQueries
FROM sys.sysprocesses SP
INNER JOIN sys.dm_exec_requests ER
    ON sp.spid = ER.session_id
WHERE --TEXT NOT LIKE N'%spGetRunningQueries%'
    --AND
    DB_NAME(SP.dbid) NOT IN ('msdb','master','Distribution')
    AND
    (
        @Database IS NULL
        OR (@Database IS NOT NULL AND @Database = DB_NAME(SP.[DBID]))
    )
    AND
    (
        @ExcludeMyQueries = 0
        OR (@ExcludeMyQueries = 1 AND hostname <> HOST_NAME())
    )
    AND
    (
        @RunningThreadsOnly = 0
        OR (@RunningThreadsOnly = 1 AND SP.[Status] = 'RUNNABLE')
    )
    AND
    (
        @LocksOnly = 0
        OR (@LocksOnly = 1 AND ER.blocking_session_id <> 0) -- TODO: Show the source running query that IS the blocking session ID (will need to join / union this in somehow?)
    )
    AND DATEDIFF(s, start_time, @ExecutionDate) >= @MinDuration

-- TODO: Clean this up and DON'T USE SELECT *
IF (@OutputMode = 0) -- Everything mode
BEGIN
    SELECT *
    FROM #ExecutingQueries
END
ELSE IF (@OutputMode = 1) -- Lightweight mode
BEGIN
    SELECT
        HostName,
        SPID,
        RequestId,
        RunningTime,
        BlockingSessionId,
        LastWaitType,
        DbName,
        LEFT(EntityText, 100) AS EntityText,
        PlanHandle,
        [Status]
    FROM #ExecutingQueries
END

Using this, you can look at the longest running queries (the Duration or RunningTime columns) and also see why they're running for so long by looking at the LastWaitType and/or BlockingSessionId columns.
This is a work-in-progress that I've created off of some queries I've found in the past (don't recall exactly where, but if someone recognizes it and knows the source, I'd love to cite it), that I regularly use to debug issues on SQL Server.


You can hard-code AllType string for the product_type_name value and  UNION this with your existing DISTINCT values you can get your expected result.

Working Demo with the given sample data:

DECLARE @d_product TABLE (product_group VARCHAR (200), product_type_name VARCHAR (200));

INSERT INTO @d_product( product_group, product_type_name) VALUES
('BARE_SILICON', 'BARE_SILICON'),
('Black_Mamba', 'Black_Mamba'),
('BURIN-IN_WAFER', 'BURIN-IN_WAFER'),
('CALVIN', 'CALVIN_BLK');

SELECT DISTINCT [product_group], [product_type_name] FROM @d_product
UNION
SELECT DISTINCT [product_group], 'AllType' FROM @d_product

Output:

product_group      product_type_name
---------------------------------------
BARE_SILICON       AllType
BARE_SILICON       BARE_SILICON
Black_Mamba        AllType
Black_Mamba        Black_Mamba
BURIN-IN_WAFER     AllType
BURIN-IN_WAFER     BURIN-IN_WAFER
CALVIN             AllType
CALVIN             CALVIN_BLK


pg_upgrade from Postgres 9.5.5 to Postgres 12.3 errors out

The documentation says:

File cloning is only supported on some operating systems and file systems. If it is selected but not supported, the pg_upgrade run will error. At present, it is supported on Linux (kernel 4.5 or later) with Btrfs and XFS (on file systems created with reflink support), and on macOS with APFS.

Obviously you are using different software.
You could use the --link option for a fast upgrade, but that will make the old cluster unusable after the upgrade.
Don't upgrade to 12.3. Use the latest supported minor release, currently 12.5.


Granting access to all tables for a user

First, you have to be able to connect to the database in order to run queries.  This can be achieved by

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT
ON DATABASE database_name
TO user_name;

The REVOKE is necessary because

  The key word PUBLIC indicates that the privileges are to be granted to
  all roles, including those that might be created later. PUBLIC can be
  thought of as an implicitly defined group that always includes all
  roles. Any particular role will have the sum of privileges granted
  directly to it, privileges granted to any role it is presently a
  member of, and privileges granted to PUBLIC.

If you really want to restrict your user to DML statements, then you have a little more to do:

REVOKE ALL
ON ALL TABLES IN SCHEMA public
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO user_name;

These assume that you will have only one schema (which is named 'public' by default).

As Jack Douglas pointed out, the above only gives the privileges for the already existing tables.  To achieve the same for future tables, you have to define default privileges:

ALTER DEFAULT PRIVILEGES
    FOR ROLE some_role   -- Alternatively "FOR USER"
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

Here, some_role is a role that creates the tables, while user_name is the one who gets the privileges.  Defining this, you have to be logged in as some_role or a member of it.

And, finally, you have to do the same for the sequences (thanks to PlaidFan for pointing it out) - here it is the USAGE privilege that you need.


"Column check constraint cannot reference other columns"

Your syntax is not correct for row level CHECK constraints.

  CHECK (DELY_DATE > S_ORDER_DATE) needs to be at the table level.

  -- from Martin Smith's comments

Within the CREATE TABLE statement:

CREATE TABLE SALES_ORDER
(
   S_ORDER_NO   VARCHAR2(6) PRIMARY KEY CHECK (S_ORDER_NO LIKE 'O'),
   S_ORDER_DATE DATE,
   DELY_ADDR    VARCHAR2(25),
   SALESMAN_NO  VARCHAR2(6) REFERENCES SALESMAN_MASTER(SALESMAN_NO),
   DELY_TYPE    CHAR(1) DEFAULT 'F' CHECK (DELY_TYPE IN ('P', 'F')),
   BILLED_YN    CHAR(1),
   DELY_DATE    DATE,
   ORDER_STATUS VARCHAR2(10) CHECK (ORDER_STATUS IN ('PROCESS', 'FULFILLED', 'BLOCKORDER', 'CANCELED')),
   constraint <CNAME> CHECK (DELY_DATE > S_ORDER_DATE)
);

Outside of the CREATE TABLE statement:

CREATE TABLE SALES_ORDER
(
   S_ORDER_NO   VARCHAR2(6) PRIMARY KEY CHECK (S_ORDER_NO LIKE 'O'),
   S_ORDER_DATE DATE,
   DELY_ADDR    VARCHAR2(25),
   SALESMAN_NO  VARCHAR2(6) REFERENCES SALESMAN_MASTER(SALESMAN_NO),
   DELY_TYPE    CHAR(1) DEFAULT 'F' CHECK (DELY_TYPE IN ('P', 'F')),
   BILLED_YN    CHAR(1),
   DELY_DATE    DATE,
   ORDER_STATUS VARCHAR2(10) CHECK (ORDER_STATUS IN ('PROCESS', 'FULFILLED', 'BLOCKORDER', 'CANCELED'))
);

-- from Lennart's comments
ALTER TABLE SALES_ORDER ADD CONSTRAINT <CNAME> CHECK (DELY_DATE > S_ORDER_DATE);


Is it bad practice to use separate columns for parts of date values?

It is a bad practice in a transactional/operational database and a good practice in an analytic/data warehouse database.

In a data warehouse I recommend using an integer "smart key"† for your date dimension (like 20160328), but also including an actual date-type column, as well as the broken-down date parts.

The broken down date parts are more for ease of analysis, than speed of querying.

† This makes it easy to partition your fact tables.


Where can I learn about MySQL master/slave, master/master, or clustering setups?

Forgive me for sounding somewhat biased, but you can learn about setting up Master/Slave and Master/Master Replication right here in the DBA StackExchange.

Here are some of my posts

May 26, 2012 : Trying to understand SHOW SLAVE STATUS\G
Jan 05, 2012 : MySQL Replication Error
Dec 23, 2011 : How to make MySQL replication reliable?
Dec 19, 2011 : Best way to setup master to multi master replication
Feb 23, 2011 : MySQL Replication - Introduce new Slave to replication

I also have posts concerning

MySQL and DRBD Clustering
Percona XtraDB Cluster

Click here to see all MySQL Replication posts in the DBA StackExchange (not just mine)

There is only one thing I cannot give advice on : MySQL Cluster

While I have answered some questions, there are three MySQL Cluster Experts here

Frazer Clement
Mat Keep
Andrew Morgan

Feel free to post your MySQL Replication questions or simply search through the already posted questions. You may find that what you are looking for is already answered.


What are the "GDR" vs "QFE" hotfixes for SQL Server 2008 R2 SP3?

To understand the jargons, Aaron Bertrand has a #BackToBasics blog post

Also, refer to this decision matrix for TLS 1.2 support or MS KB - TLS 1.2 support for Microsoft SQL Server

10.50.6220 => SQL Server 2008R2 SP3 GDR

10.50.6529 =>  SQL Server 2008R2 SP3 QFE

From the decision matirx, if you dont want TLS 1.2 support, dont install 10.50.6542. TLS 1.2 support got introduced in 10.50.6537.

So to answer your question which one to install ... it will be 10.50.6220.

As always, test out your application behavior when installing a new fix.


I created a Stored Procedure solution to perform the following:

Convert the txt field of t1 into an extended INSERT list delimiting by '|'
Execute the extended INSERTs
Convert the value field of t2 into an extended INSERT list delimiting by ','
Execute the extended INSERTs

Here is the sample data

DROP DATABASE IF EXISTS all_denorm;
CREATE DATABASE all_denorm;
USE all_denorm
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1
(
    id INT NOT NULL,
    txt TEXT
) ENGINE=InnoDB;
INSERT INTO  t1 (id,txt) VALUES
(23,'first,second|third,fourth,fifth|sixth'),
(24,'seventh,eighth|ninth|tenth,eleven|twelfth'),
(25,'thirteenth|fourteenth|fifteenth|sixteen|seventeenth'),
(26,'eighteenth,nineteenth|twentieth');
CREATE TABLE t2
(
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT NOT NULL,
    VALUE VARCHAR(255),
    PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE t3 LIKE t2;

Here is the Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `all_denorm`.`process_denorm` $$
CREATE PROCEDURE `all_denorm`.`process_denorm` ()
BEGIN

    DECLARE given_id,current_id,last_id,processing INT;
    DECLARE delimiter_char CHAR(1);

    SET current_id = 0;
    SET processing = 1;
    SET delimiter_char = '|';
    WHILE processing = 1 DO
        SELECT MIN(id) INTO given_id FROM t1 WHERE id > current_id;
        IF ISNULL(given_id) THEN
            SET processing = 0;
        ELSE
            SET current_id = given_id;
            SELECT
                REPLACE(REPLACE(CONCAT('(''',REPLACE(txt,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
                INTO @insert_list FROM t1 WHERE id = given_id;
            SELECT CONCAT('insert into t2 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
            PREPARE stmt FROM @insert_SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END WHILE;

    SET current_id = 0;
    SET processing = 1;
    SET delimiter_char = ',';
    WHILE processing = 1 DO
        SELECT MIN(id) INTO given_id FROM t2 WHERE id > current_id;
        IF ISNULL(given_id) THEN
            SET processing = 0;
        ELSE
            SET current_id = given_id;
            SELECT
                REPLACE(REPLACE(CONCAT('(''',REPLACE(value,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
                INTO @insert_list FROM t2 WHERE id = given_id;
            SELECT CONCAT('insert into t3 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
            PREPARE stmt FROM @insert_SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END WHILE;

    SELECT * FROM t1;
    SELECT * FROM t2;
    SELECT * FROM t3;

END $$

DELIMITER ;

Here it is executed

mysql> DROP DATABASE IF EXISTS all_denorm;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE all_denorm;
Query OK, 1 row affected (0.00 sec)

mysql> USE all_denorm
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1
    -> (
    ->     id INT NOT NULL,
    ->     txt TEXT
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO  t1 (id,txt) VALUES
    -> (23,'first,second|third,fourth,fifth|sixth'),
    -> (24,'seventh,eighth|ninth|tenth,eleven|twelfth'),
    -> (25,'thirteenth|fourteenth|fifteenth|sixteen|seventeenth'),
    -> (26,'eighteenth,nineteenth|twentieth');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     parent_id INT NOT NULL,
    ->     VALUE VARCHAR(255),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t3 LIKE t2;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `all_denorm`.`process_denorm` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `all_denorm`.`process_denorm` ()
    -> BEGIN
    ->
    ->     DECLARE given_id,current_id,last_id,processing INT;
    ->     DECLARE delimiter_char CHAR(1);
    ->
    ->     SET current_id = 0;
    ->     SET processing = 1;
    ->     SET delimiter_char = '|';
    ->     WHILE processing = 1 DO
    ->         SELECT MIN(id) INTO given_id FROM t1 WHERE id > current_id;
    ->         IF ISNULL(given_id) THEN
    ->             SET processing = 0;
    ->         ELSE
    ->             SET current_id = given_id;
    ->             SELECT
    ->                 REPLACE(REPLACE(CONCAT('(''',REPLACE(txt,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
    ->                 INTO @insert_list FROM t1 WHERE id = given_id;
    ->             SELECT CONCAT('insert into t2 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
    ->             PREPARE stmt FROM @insert_SQL;
    ->             EXECUTE stmt;
    ->             DEALLOCATE PREPARE stmt;
    ->         END IF;
    ->     END WHILE;
    ->
    ->     SET current_id = 0;
    ->     SET processing = 1;
    ->     SET delimiter_char = ',';
    ->     WHILE processing = 1 DO
    ->         SELECT MIN(id) INTO given_id FROM t2 WHERE id > current_id;
    ->         IF ISNULL(given_id) THEN
    ->             SET processing = 0;
    ->         ELSE
    ->             SET current_id = given_id;
    ->             SELECT
    ->                 REPLACE(REPLACE(CONCAT('(''',REPLACE(value,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
    ->                 INTO @insert_list FROM t2 WHERE id = given_id;
    ->             SELECT CONCAT('insert into t3 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
    ->             PREPARE stmt FROM @insert_SQL;
    ->             EXECUTE stmt;
    ->             DEALLOCATE PREPARE stmt;
    ->         END IF;
    ->     END WHILE;
    ->
    ->     SELECT * FROM t1;
    ->     SELECT * FROM t2;
    ->     SELECT * FROM t3;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

Here is the output:

mysql> CALL process_denorm();
+----+-----------------------------------------------------+
| id | txt                                                 |
+----+-----------------------------------------------------+
| 23 | first,second|third,fourth,fifth|sixth               |
| 24 | seventh,eighth|ninth|tenth,eleven|twelfth           |
| 25 | thirteenth|fourteenth|fifteenth|sixteen|seventeenth |
| 26 | eighteenth,nineteenth|twentieth                     |
+----+-----------------------------------------------------+
4 rows in set (0.01 sec)

+----+-----------+-----------------------+
| id | parent_id | VALUE                 |
+----+-----------+-----------------------+
|  1 |        23 | first,second          |
|  2 |        23 | third,fourth,fifth    |
|  3 |        23 | sixth                 |
|  4 |        24 | seventh,eighth        |
|  5 |        24 | ninth                 |
|  6 |        24 | tenth,eleven          |
|  7 |        24 | twelfth               |
|  8 |        25 | thirteenth            |
|  9 |        25 | fourteenth            |
| 10 |        25 | fifteenth             |
| 11 |        25 | sixteen               |
| 12 |        25 | seventeenth           |
| 13 |        26 | eighteenth,nineteenth |
| 14 |        26 | twentieth             |
+----+-----------+-----------------------+
14 rows in set (0.01 sec)

+----+-----------+-------------+
| id | parent_id | VALUE       |
+----+-----------+-------------+
|  1 |         1 | first       |
|  2 |         1 | second      |
|  3 |         2 | third       |
|  4 |         2 | fourth      |
|  5 |         2 | fifth       |
|  6 |         3 | sixth       |
|  7 |         4 | seventh     |
|  8 |         4 | eighth      |
|  9 |         5 | ninth       |
| 10 |         6 | tenth       |
| 11 |         6 | eleven      |
| 12 |         7 | twelfth     |
| 13 |         8 | thirteenth  |
| 14 |         9 | fourteenth  |
| 15 |        10 | fifteenth   |
| 16 |        11 | sixteen     |
| 17 |        12 | seventeenth |
| 18 |        13 | eighteenth  |
| 19 |        13 | nineteenth  |
| 20 |        14 | twentieth   |
+----+-----------+-------------+
20 rows in set (0.04 sec)

Query OK, 0 rows affected (0.10 sec)

mysql>

Give it a Try !!!


There are two approaches that generally work well.

Option 1: add a clause to make an ineffectual change when duplicates are found:

INSERT INTO spider_367.expedient
SELECT * FROM spider.expedient
ON DUPLICATE KEY UPDATE x=x

Replace x with one of your field names.

Option 2: do a left join and only insert the records that don't match

INSERT INTO spider_367.expedient
SELECT x1.* FROM spider.expedient x1
LEFT JOIN spider_367.expedient x2 ON x1.id = x2.id
WHERE x2.id IS NULL

Replace x1.id and x2.id with your key fields.


SQL Server : If a query is cancelled, where to get data on it?

You'd have to be polling sys.dm_exec_requests rather frequently, or using trace / extended events to target queries that ended with abort. With the disclaimer that I work for SQL Sentry, Performance Advisor - among many other things - tracks all aborted queries for you, as long as they exceed the Top SQL threshold (5 seconds by default, but that is configurable). It's possible other monitoring tools might do something similar.


You cannot make a check condition a part of the type definition syntactically, but you can create a rule:

CREATE RULE OrdinalTRule AS @value >= 0

and bind it to the data type:

sp_bindrule OrdinalTRule, OrdinalT

In order to modify the rule later, you will have to drop and recreate it. Before being dropped, the rule must be unbound:

sp_unbindrule OrdinalT

Note that the argument in the last statement is not the rule itself but the type to which the rule is bound.


Consider using de-duplication if you have lots of identical XML - documentation link.

I wrote a huge unit test, but it turns out that SecureFile LOBs are always stored in a Lob Segment outside the row!


For BCNF decomposition, is it okay to have a relation with none of the original functional dependencies?

Your solution is correct, and correctly in R8 no non-trivial dependency holds.

This happens since the classical so-called “analysis” algorithm for BCNF, works by removing from the original relation (and the decomposed relations) the dependencies that violates the Normal Form, leaving behind only the left hand side of the dependency.

In this case, at the end of the algorithm, the relation that remains is {c, f, a} in which there are no non-trivial dependencies (so that {c, f, a} is the only candidate key of the relation).

One can note that these three attributes are also a candidate key (actually the only candidate key) of the original relation.

In fact, there is a theorem that says that to have a loss-less decomposition it is necessary to have at least a relation that contains all the attributes of a candidate key.

Finally, note that the decomposition also preserves the functional dependencies (and this is not obvious since the algorithm to decompose in BCNF sometimes produces a decomposed schema with a loss of dependencies).


My answer is : It depends.
If you are accessing files by _id field, which is already indexed then you don't need to add more memory soon.

The _id field which is type of ObjectID is 12 byte in size. That means it can hold up to 2^(12*8) files. 3 byte is for machine ID which is a hash value and has a fixed vale on the machine can be subtracted which gives you approx 2^72 files. For the reference, 2^20 is 1,048,576.

In terms of the memory, the index on the _id field needs 10,000,000 x 12 byte = 114 MiBytes. To be honest, I don't now how much overhead there will be for an index which holds 10 millions value but I don't think that it will need more than 1 Gigabyte.

Now, if your _id field is not a type of ObjectID than do the math.

In the gridfs, filename value of the files collection is also indexed. If you are not accessing files using filename, then you may leave it blank and drop the index for the filename.

On the other side, if you will add some metadata to the files you added and want to query the files according to those metadata, then you should have indexes for those metadata and do the math again.

I have a production environment which has over 3,000,000 pdf files (takes 180 Gig space on the disk). My server is a virtual server which has 4 vCPU and 4 Gig RAM, still no problem. The specs you provided is way too high for your needs. You can save billions of files with those servers. Especially if you have SSD. Because even if your indexes do not fit into the memory, swapping will be very fast, you won't even notice a slowdown.


Vertica performance on select from view with union

Vertica query performance depends highly on the predicate used in the query .

To get the gist of your performance , try getting the projection name of the selected columns of the query you are firing . The columns in the order by clause of the projection is very important in deciding the performance of your select. you can get that by running explain on your query .

Vertica improves performance by sorting the columns , compressing via encoding them so that they use minimal memory while running .

Also run analyze_histogram(tablename, 100) on all your tables . this will ensure statistics over complete data sample, not just 10% of data sample which is taken by analyze_statistics .

Also , since you are doing union you should try to keep the sort order of all the projections same as after union it might be meaningless .


Exporting SQL Server 2008 R2 Activity Monitor Log

Well you can (on a dev system of course) run a trace against yourself running activity monitor and then isolate the queries you want to use for monitoring.  Then you could either create custom jobs or use custom collectors with management data warehouse to execute those queries on your desired schedule to log the activity you want to another table.  Then you can just analyze the results there.


I was able to figure out which file in the database storage was the culprit, by copying all the files to /dev/null.

cp -vR /usr/lib/postgresql/8.4 /dev/null

(The path to your DB files might differ)

The currupt file could not be copied, but there was nothing I could do to change that. (so it was most probably a FS error or hardware failure)

So I restarted the server with a forced fsck (e.g. touch /forcefsck), to make sure the FS would do the best to fix itself. This might not be the way you'll want to go, since it is possible to have a total data loss afterwards, but I was able to preserve the most precious data already beforehand, so I took the risk.

After reboot I could finally access the inaccessable table again, but I am not sure, if the data contained is corrupted or not. Anyway, I do have a backup now, which I can disect to find out, and my server can go back online for now...

I recommend reading the wiki of postgres about corruption and the slides of this FOSDEM presentation for some more info on DB corruption


Match Against search not doing exact search?

Plan A:
As you put the data into the table, change - to, say, _ so that such items will be on "word", not two.  Do a similar change when constructing the AGAINST clause.
If necessary, make an extra column with that and do any other cleansing of the data -- Then index that column with FULLTEXT, but use the original table for output.
Plan B:
MATCH (col) AGAINST ('+CE20' in boolean mode)
AND col LIKE '%CE20-2019%'

Or, if you need a regexp to handle a letter/digit before:
MATCH (col) AGAINST ('+CE20' in boolean mode)
AND col REGEXP '\\bCE20-2019'

(That's for MySQL 8; use [[:<:]] instead of \\b for other versions.  That is "word boundary" and should eliminate most of the 20 extra rows.)
That will run nearly as fast as the original query should have run.
Alas, this requires understanding the incoming data and making programmatic decisions as to how best to construct a fast query.
Note that the use of + when IN BOOLEAN MODE may be the 'right' thing to do for your app.


Postgres jsonb vs composite type performance differences

It's not really a question of performance,

Composite Types are strongly typed meaning you have to define their shape.
JSON is weakly typed.

Moreover, JSON has a use case, but with a composite type you almost never want to use them because it's almost always better normalizing them into their own relations. There is an exception to that though -- when you're going to create a whole library around a set of data then composite types can make everything and nicer. For example stdaddr in PostGIS is one such example. That type represents a physical address and multiple things can take that type (like the PostGIS geocoder).

So you can do

CREATE TABLE foo ( foo_id int, address postgis.stdaddr );

now you can get the geocoded information relatively easily and you only have to pass around one value rather than 15.


Are multiple indexes on MYSQL table reason for slow UPDATES and INSERTS?

To answer the specific question:

No, an extra index on (entry_date) or two would not hurt performance of updating a different, title column.

Additionally:

No, the version of MySQL, 5.6, is not too ancient, even if some modern features (like window functions) are missing. You should have decent performance with decent hardware.

We can only speculate on the issue in hand but what could be wrong or explain it:

old, inefficient hardware: check the disk specifications, measure it's preformance.

fragmented index/table. Check MySQL docs and old questions/answers here of how to defragment MyISAM tables (OPTIMIZE TABLE).

Last but not least:

Your table is using MyISAM engine, which is old news. InnODB has replaced it as the default engine in MySQL years ago. There is no active development of this engine. It lacks several features compared to InnoDB (transactions, foreign key constraints, etc) and performance in most work loads. I strongly suggest you change your table (after testing of course that your apps and procedures do not break) to use InnoDB.

Importantly to your Question, InnoDB can perform SELECT and UPDATE at the same time (usually).  MyISAM completely locks the table; the update or select must finish before the select or update can even start.

When switching from MyISAM to InnoDB, be sure to adjust key_buffer_size and innodb_buffer_pool_size.


Keeping MySQL staging database on same server updated from production

I can see mysqldump being a little pain in the neck because of transaporting data out and then in.

Let's say you are copying data from proddb to stagedb. Here is a query to create the script for lateral copying of every table:

select
concat
(
    'drop table if exists ',db2,'.',tbl,'; ',
    'create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ',
    'insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';'
)
from (select table_schema db1,table_name tbl
from information_schema.tables where table_schema='stack_dev') tbls,
(select 'stack_test' db2) dbname;

Just generate the script as a SQL text file and import the text file to the target server:

PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" > /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql

This should work just fine for MyISAM. It should also work for InnoDB if there are no constraints. If there are constraints, you may have to disable them for the import session:

PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
echo "SET FOREIGN_KEY_CHECKS=0;" > /root/CopyFromProdToStage.sql
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" >> /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql

UPDATE 2012-03-09 17:14 EST

I have a stored procedure that will accomplish this. Run the following only once:

DROP DATABASE IF EXISTS utility;
CREATE DATABASE utility;
DELIMITER $$
DROP PROCEDURE IF EXISTS `utility`.`CopyDB` $$
CREATE PROCEDURE `utility`.`CopyDB` (sourceDB VARCHAR(64),targetDB VARCHAR(64))
TheStoredProcedure:BEGIN

    DECLARE found_count,ndx,ndx_last INT;
    DECLARE sqlcmd VARCHAR(1024);

    SELECT COUNT(1) INTO found_count
    FROM information_schema.tables
    WHERE table_schema = sourceDB;
    IF found_count = 0 THEN
        LEAVE TheStoredProcedure;
    END IF;

    DROP TABLE IF EXISTS DBTablesToCopy;
    CREATE TABLE DBTablesToCopy
    (
        id INT NOT NULL AUTO_INCREMENT,
        src  VARCHAR(64),
        tgt  VARCHAR(64),
        tbl  VARCHAR(64),
        PRIMARY KEY (id)
    ) ENGINE=MyISAM;
    DROP TABLE IF EXISTS SQLScriptToCopyTables;
    CREATE TABLE SQLScriptToCopyTables
    (
        id INT NOT NULL AUTO_INCREMENT,
        sqltext VARCHAR(1024),
        PRIMARY KEY (id)
    ) ENGINE=MyISAM;

    INSERT INTO DBTablesToCopy (src,tgt,tbl)
    SELECT sourceDB,targetDB,table_name
    FROM information_schema.tables
    WHERE table_schema = sourceDB;

    INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
    (CONCAT('DROP DATABASE IF EXISTS ',targetDB));
    INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
    (CONCAT('CREATE DATABASE ',targetDB));
    SELECT MAX(id) INTO ndx_last FROM DBTablesToCopy;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        INSERT INTO SQLScriptToCopyTables (sqltext)
        SELECT CONCAT('CREATE TABLE ',tgt,'.',tbl,' LIKE ',src,'.',tbl)
        FROM DBTablesToCopy WHERE id = ndx;
    END WHILE;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        INSERT INTO SQLScriptToCopyTables (sqltext)
        SELECT CONCAT('INSERT INTO ',tgt,'.',tbl,' SELECT * FROM ',src,'.',tbl)
        FROM DBTablesToCopy WHERE id = ndx;
    END WHILE;

    SELECT MAX(id) INTO ndx_last FROM SQLScriptToCopyTables;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        SELECT sqltext INTO @stmt FROM SQLScriptToCopyTables WHERE id = ndx;
        PREPARE s1 FROM @stmt;
        EXECUTE s1;
        DEALLOCATE PREPARE s1;
    END WHILE;

END $$
DELIMITER ;

then anytime you feel like copying stack_dev to stack_test, do this:

call utility.copydb('stack_dev','stack_test');

and that's all.

Give it a Try !!!


Question regarding 2NF partial key dependency

Assuming your relation is ABCD and assuming you meant the following functional dependencies:

AB-->C
BC-->D

Then you need to decompose this relation to get it to third normal form (3NF).  This is because D is not determined by the key of ABCD.  To bring this to 3NF you need to have two relations: ABC and BCD.  Now each non-key attribute is fully dependent on the key of its relation (and nothing else).


Defragmenting SQL Server data and log files live with MoveFile API

Personally, I wouldn't want any antivirus or defrag software anywhere near a database file. Ask yourself, is this going to cause more problems than it's going to solve?

From A tale of CHECKDB failures cause by 3rd party file-system drivers:

  At the end of last week, Diskeeper 2009 was upgraded to Diskeeper 2010
  on the servers by our server team.  Diskeeper 2009 had been there for
  at least a year without problems so who would have expected it to be
  the issue.  Turns out the new version has an option enabled called
  IntelliWrite that causes this problem to occur.  Disabling this option
  immediately resolve the problem.  Paul blogged about this problem a
  long time ago, and recalled reading this when I clicked on it but it
  was the last thing that I would have thought about.

From Beware 3rd party file-system drivers with DBCC CHECKDB

  I don't want to cry 'wolf' here, but be careful of attributing massive
  corruption problems to the real IO subsystem if there's any kind of
  file-system filter driver installed.

With proper planning and management, you shouldn't need to defrag your database files.


MySQL is insanely slow on fresh installed Ubuntu 17.10

long_query_time = 1 (or possibly a lower value); this will help get something useful in the slowlog.

With rare exceptions, "You can't tune you way out of a performance problem."

How much RAM?

Find some slow SQL so we can discuss it.  Please provide the corresponding SHOW CREATE TABLE so we know what the table(s) look like.


Selecting all fields makes no sense - server will output one RANDOM field value from all values in the group. Use

SELECT testtable.*
FROM testtable
JOIN ( SELECT SUBSTRING_INDEX(urlTest,'/',3) AS domain,
              field2
       FROM testtable
       GROUP BY SUBSTRING_INDEX(urlTest,'/',3), field2
       HAVING COUNT(*) > 1 ) domains
WHERE SUBSTRING_INDEX(testtable.urlTest,'/',3) = domains.domain
  AND testtable.field2 = domains.field2

fiddle


CentOS 7, PostgreSQL failed to read permission data directory in mounted partition

You are obviously missing some permissions in the new location.

I have tried to reproduce your error on my local test cluster.  I could get several different errors along the way but never the one you got.  However, here are the permissions you need on your directories to run the cluster:

the data_directory (in your case, pgsql_data) must be owned by postgres (the group seems making no difference)
it must have 0700 as permissions (no group and world level permissions are allowed)
all directories under this need 07XX, where XX refers to the group and world permissions - I haven't found any restrictions to these (but obviously didn't try all possibilities)

Based on my experiments, these should be enough for starting PostgreSQL.


What are the penalties of using many (thousands) of column families or keyspaces in Cassandra?

Cassandra 1.0 uses a minimum of 1MB of heap per CF.  So, 1000 or 2000 CFs will be okay for typical heap sizes, but 10000 will probably not be.  JVM GC does poorly with very large heaps; I recommend staying under 8GB.


Was my Postgres cluster built with the ICU libraries available for Postgres 10 and later?

Does this mean I have the ICU libraries installed, or not?

The configure option that really brings in the library is --with-icu. Currently, checking for this one seems the more reliable way to tell whether ICU has been included in the build.

ICU_CFLAGS and ICU_LIBS are paths that are required only if the library is installed where the compiler does not search by default.

  Does that happen only if the ICU libraries are in place?

It does, but pg_collation also has a new collprovider field indicating that a collation is from ICU (i) or the libc (c)


Do I have to pay a license fee for Berkeley DB?

http://www.oracle.com/technetwork/database/berkeleydb/downloads/licensing-098979.html

Our open source license permits you to use Berkeley DB, Berkeley DB Java Edition or Berkeley DB XML at no charge under the condition that if you use the software in an application you redistribute, the complete source code for your application must be available and freely redistributable under reasonable conditions. If you do not want to release the source code for your application, you may purchase a license from Oracle. For pricing information, or if you have further questions on licensing, please contact us at berkeleydb-info_us@oracle.com.


Production SQL Server High CPU for a Single Query

Perhaps we can pull out the execution plan when it seems that particular query is running slow and compare it to the execution plan for when you run it.  If you recently updated indexes it might still have the old exec plan cached, however, it does not answer the question as to why it runs fast when you execute the stored procedure from SQL Server, unless you're using different parameters.

Did you check if AUTO_UPDATE stats is on?

Is your tempdb optimized?  Sometimes PAGELATCH_SH issues will be intermittent.

Can you view the wait stats, clear them, run the query, and then see the wait stats for when it was slow?

Viewing the plan cache:
http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory


Is reply to client about transaction commit, before binary log or after?

In general, Mysql writes to the binary log at the end of the transaction. So if a client opens an explicit transaction the statements will be written into the binary log only after a commit. But if the statements involve nontransactional tables (MyIsam) it is possible that they are written to the binary log before the end of the transaction. If you have autocommit enabled,  Mysql replies to client after a statement is written to the binary log, because every statement is a transaction.

Using mysqlbinlog command utility you can do the following experiment. Open a shell with a command like this:

mysqlbinlog --read-from-remote-server --stop-never -h<host> -P<port> -u<user> -p<passwordd> binary-log.000003

Into another shell you can launch mysql client.

mysql> create table tr (i int) engine=innodb;
mysql> insert into tr values(1),(2),(3);

You could see the output of mysqlbinlog scrolling immediately with a text like this:

# at 1016
[..]
SET TIMESTAMP=1458127130/*!*/;
create table tr (i int) engine=innodb
[...]
# at 1281
[..]
SET TIMESTAMP=1458127136/*!*/;
BEGIN
/*!*/;
# at 1384
[..]
SET TIMESTAMP=1458127136/*!*/;
insert into tr values(1),(2),(3)
/*!*/;
# at 1514
[...]
COMMIT/*!*/;

If you try:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tr values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

the output of mysqlbinlog scrolls down only after the commit.

If you try create table notr (i int) engine=myisam; and try to insert into notr , mysql writes immediately to binlog independently if the insert is inner a transaction. And the behavior can be different if you mix notransaction and transactional tables.
Hope this help.


Is using the MongoDB _id field index as a time range query possible?

Yes, of course you can use _id to query created time. You just need to query "range" where lower limit is wanted time (with needed resolution, msec, sec, min...) where rest of bytes (_id) are zero and upper limit with rest of bytes set to FF.

I'm not sure is it most effective way or is saved db space worth of it...


Should I use SQL Server DMVs with NOLOCK

What are the consequences of reading DMVs in a production live OLTP database?

Negligible as far as my experience goes. DMV's as the name suggests are basically views or functions ( like sys.dm_db_index_physical_stats ) which get information from various caches and system tables. DMV's are very light and read data from caches. They do not cause blocking and depending on amount of data you need to fetch they very very quick. The point here is always only query data from caches which you actually need.

You may use NOLOCK hint in your DMV query you would see some people using it but frankly I do not see much of need to use it with DMV's. Its function though still remains same. So when you use NOLOCK hint you say to SQL Server that give me the data I am asking no matter what is state of the data is. It may have been changed and yet not committed so you get "dirty" data. NOLOCK hint avoids select queries taking shared lock while reading data. The drawbacks are many

  Additionally, with NOLOCK, is there higher chance query will continue forever in high volume environment since I am not locking rows, pages, and things will continue be added into DMV TableView?

Query will only continue for duration until it finishes its task, unless you run a never ending loop. The NOLOCK only ensures that query is minimally blocked and also note that when using NOLOCK if required other locks like schema stability  are taken


Having a backup of the master key, how can I restore a database that was encrypted by master key?

The screenshots show a backup of the service master key for the instance but that is not the same as the database master key that the AAG wizard is asking for. See Encryption Hierarchy in SQL.

Your situation sounds similar to adding SSISDB to an AlwaysOn AG, in that a password protected DMK exists on the database, and so you must provide that password in order for the DB to be restored and accessible on the other replica.

If you don't know the password, you need to go back to the live server and alter the DMK password:

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '####';

Once this has been done, record the new DMK password in a safe place, like a password safe. Backup the DMK to this location if possible as well, so that you can recover the DMK if need be.

Once this is done, take a new backup use that to join the secondary in the AG, providing the password when prompted. An alternative to this is to use a symmetric key created by a certificate instead of a password which is protected by the DMK, and then simply backup and restore that certificate on all replicas to enable encrypted databases to be joined to the AG with relative ease. More info here.


8 hours to analyze 700GB seems like a really long time.  So, what makes it so slow?  Do you have expression indexes which use slow user-defined-functions?  That will certainly slow things down.  Do you have a very high setting for default_statistics_target?  Do you have a massively under-provisioned disk subsystem?

Are you using analyze-in-stages or just analyze-only?

Are you doing it in parallel with -j option?  Probably not if you are only upgrading to 9.4 (which is itself a mistake--if the upgrade is traumatic for you, why not leap several versions at once so you don't need to do it as often?)

As for running it in the background, often a database can get by with no statistics or crude statistics for ordinary operations, and only needs detailed statistics for reporting or analytics such as weekly, end-of-month, or end-of-year reports.  So then you can get away with opening the database for ordinary use before the analyze is done. (Or if you do analyze-in-stages, open it after the first stage is complete).  On the other hand, some databases need good statistics right from the beginning because otherwise even routine queries will use such horrible plans that they essentially never finish, and will hog up so much CPU and disk resources that they slow down the vacuumdb script.

There is no way to know which case you will fall into without an intimate knowledge of the particulars of your database.  If you have a sampling of some typical and/or critical queries from your production system, you can replay them against a test server with no statistics and see what happens.


Deadlock error isn't returning the deadlock SQL

The data you need is recorded in the default extended events trace.

DECLARE @xml XML

SELECT @xml = target_data
FROM   sys.dm_xe_session_targets
       JOIN sys.dm_xe_sessions
         ON event_session_address = address
WHERE  name = 'system_health'
       AND target_name = 'ring_buffer'

SELECT
             XEventData.XEvent.query('(data/value/deadlock)[1]')  AS DeadlockGraph,
             CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML) AS DeadlockGraph,
              XEventData.XEvent.value('(./@timestamp)[1]', 'DATETIME2') AS [DateTime]
FROM   (SELECT @xml AS TargetData) AS Data
       CROSS APPLY
       TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
ORDER BY [DateTime] DESC

Though it won't be there any more if you have restarted the service -e.g. to apply a trace flag or if the buffer has cycled in the meantime.

You can set up your own extended events trace that stores the deadlock graph to a file target for persistent non volatile storage. Example Code here. I personally find the deadlock graph XML more friendly than the trace flag output.

Edit

@MartinC points out in the comments that on instances of SQL Server that don't have all the updates there might be a problem with it generating invalid XML. The fix for that is to do some search and replace and use CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph in the SELECT list as described here.
Wayne Sheffield has posted a useful script to shred the deadlock graph XML into tabular format here.


Resources for SQL Server developer migrating to Oracle

Speaking more from a DBA perspective than a developer, this course is very comprehensive; there may be some things in there that are of value to you, particularly the Data Objects and Data Access chapters. It's written from an Oracle → SQL Server perspective but it works either way, it's about mapping concepts you already know to a slightly different vocabulary.

But, speaking from experience here, the biggest factor is not learning what datatypes correspond to what, but the shift in thinking about your application and particularly how to optimize it. For example, if you have come from SQL Server, you most likely believe that cursors are expensive and to avoid using them. Well, in Oracle every SELECT is a cursor "under the hood"; all using a cursor does is give you a handle to it. Similarly in SQL Server you will be used (unless you have been running in optimistic mode, which few people do) to readers blocking writers and writers blocking readers and coding to avoid that scenario; Oracle behaves very differently as it only supports MVCC anyway. Clustered indexes in SQL Server are use far more often than IOTs in Oracle (I don't actually know why this is, it's just something I've observed, Oracle people are missing a trick there).

Things like this are why apps ported from one to the other run into trouble... It's not just as simple as changing the SQL syntax (if it was you might as well have just used MySQL as you won't be getting the advantage of all these features you've paid for!)


Database role to allow .NET program to retrieve MSDB table and view rows

Try making your user account a member of the msdb database's SQLAgentReaderRole.

The rights granted by that role membership are described here:

https://msdn.microsoft.com/en-us/library/ms188283.aspx

Look at the description of rights under SQLAgentReaderRole Permissions.  You will see that it has rights to read the information on the jobs.  This does not require being a member of the db_datareader role.  It does allow the user to enumerate jobs, view properties, and view job history.

The user account can only make changes to a job if that account is the owner of the SQL Agent job.  Otherwise, it is read-only.


Is there any way to recover  a database after dropping from phpmyadmin?

Sorry, that you had such an accident.

If you have binary logs on, you could try the recovery options described here:
Point in time recovery

Another thing is to restore a backup, hopefully, you have one.
Changes made after this, could be restored as mentioned above.

Good luck!


How can I track down an issue with a locked table, endless query?

As mentioned by others use spwhoisactive or spblitzwho to find the behavior.

However I am suspecting you do not have sufficient index on that table. A better index will just make the lead blockers query run fast and avoid blocking the query scanning same table.

Also check for open transactions with sleeping status . Sometimes they create those blocking scenarios when application did not close the connection well


Where do you go to request new features in SSMS?

To color code the pages in SSMS based on the server they are connected to

In the registered servers pane, right-click on server, go to properties, then the connection properties tab. Select "custom color".

This is also visible in the connection dialog. Click options -> Connection Properties tab -> Use custom color (at the lower left).

  Make it go ding when the query results are returned.

Tools | Options... | Query Results | Play the Windows default beep when a query batch completes.

The latest version of SQL Server Management Studio is available at Download SQL Server Management Studio (SSMS).


Untraceable error importing Huge dataset with SQLCMD

It's possible that you hit SQL's size limit for batch size. Check out https://msdn.microsoft.com/en-us/library/ms143432.aspx.

If your file is just line after line of INSERT INTO [Table] (field, field) VALUES (value, value); then you have some better options than splitting the file.

Add GO statements every few thousand lines, which helps break things up into smaller batches.
Use Powershell or another language to create a stream reader send every line to sqlcmd. Chrissy LeMaire created a great script to do this for CSV files that you could use for inspiration: https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9. I've used a variation on this method to import files with a half-million INSERT statements in less than a minute.