Does ANSI SQL standard inlcude FORALL predicate? I need to get all predicate of SQL (that was included in ANSI standard any version). Some databases (Oracle) have FORALL predicate. But I am not sure if that is part of the SQL specifications.

Does it include this predicate?

P.S. Please, do not send me to the standard. My English is not so good to I can get it otherwise I wouldn't ask it here.

Searching through a copy (of a draft) of the 2011 SQL Standard, there is no mention at all of a FORALL keyword.

Neither in the main document ("Part 2: Foundation (SQL/Foundation)") which describes the core SQL language nor in the extensions document ("Part 4: Persistent Stored Modules (SQL/PSM)") which describes persistent routines and server modules. There are several control and repeat structures and keywords (DO, ITERATE, FOR, LOOP, WHILE, REPEAT) but not FORALL.

Postgres docs also have an extensive list of SQL Key Words (taken from the 92, 2008 and 2011 versions of the standard). No mention of FORALL there either.

FORALL seems to be a keyword only in Oracle's procedural language extension to SQL.


Significance of has_unchecked_assembly_data due to a persisted computed column after changing compatibility level? This database has a persisted computed column, which is computed by calling a .NET function. When changing compatibility from MSSQL 2005 to 2012, I was presented with error 10334, which looks more like a warning: it's telling me that it had to mark one of the tables as has_unchecked_assembly_data.

What it doesn't tell me is what to do about this. Should I leave it as is? Should I force the column to be recomputed somehow? How?

The documentation for alter assembly unchecked data lists several causes for this, and I think #1 is the only one that applies to this database. It also doesn't explain how to unmark such columns.

Execute DBCC CHECKTABLE or DBCC CHECKDB to set the has_unchecked_assembly_data to zero.  This is documented in the SQL Server Books Online sys.tables reference.


How to install SQL Server on a different machine and set it to be a live replica of the first Configuration:

I've a SQL Server 2008 R2 on a windows 2003 server.
It handles quite well a 300 GB db.

The Problem:

The problem is we are planning to implement a feature in the customer's software that really will over-stress the sql server and our test convinced us it is probably time to migrate from a single r/w node to a r/w node _plus a read only node to do a minimum of load balancing.

It is a completely new scenario for me, and I've no idea of where to start.

I can install another sql server, I've already done it.

Question:

But how to install a SqlServer on a different server and set it to be a live replica of the first (without turn off the first one) ?

I am not a DB Admin, I inherited this 'problem'.

The two most important parts of your question are SQL Server 2008, and this line:

  it is probably time to migrate from a single r/w node to a r/w node
  _plus a read only node to do a minimum of load balancing.

Unfortunately, in SQL Server 2008, your only two options for a readable secondary server are:

Log shipping - which isn't real time. When it's time to update the data, you have to kick everyone out of the database for the next restore to finish. That either means everyone's getting kicked out of the database all the time, or they're querying old data. The business isn't usually okay with either of those. Or...
Transactional replication - which is fairly straightforward in theory, but can be challenging in practice. When set up incorrectly, it can add a lot of load on the primary server, and you may also have to change your database structures in order to really support replication.

Since you noted that you're not a DBA, here's what I would do in your shoes: beef up the primary server instead. It's easier to accomplish, and it's probably more in your comfort zone.

For advice on where to start with performance improvements, download sp_Blitz (disclaimer: I'm one of the authors of that open source tool) and run this:

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1

Copy/paste the output of that into your question text here at Stack, and we can give you advice about where to start for performance improvements. (Feel free to redact lines that your company wouldn't want shown publicly.)


Should I use prometheus or influxdb I know this has been asked a lot of times and I've checked almost all of the links out there but just want to have a third-party opinion on our use case.

So I'm working for an IOT company and we already have prometheus installed on our servers. Primarily we use prometheus to monitor the server and app's health and some sensor-related data. Now we have a new feature where we need to save the data when an IOT device changes location.

I did a bit of reading on prometheus and read about a line on their comparison page: https://prometheus.io/docs/introduction/comparison/#prometheus-vs-influxdb

  Where InfluxDB is better:

  If you're doing event logging.
  Commercial option offers clustering for InfluxDB, which is also better for long term data storage.
  Eventually consistent view of data between replicas.

So did a bit of reading about influxdb and it seems the right tool for our use case. Was wondering if we can just continue using prometheus as our time-series database rather than adding another tool which we need to maintain in the future. Or does it make sense to use a proper time-series database not just for monitoring (as I believe what the original intention of creating prometheus was).

Prometheus is a time-series database. The question is, do you need more data types and a stronger query language? What type of data is saved for location? Geo coordinates?

It also depends on your availability requirements. Both InfluxDB and Prometheus are single node. InfluxDB supports clustering only if you purchase the enterprise version.

For Prometheus, you need multiple instances to ensure high availability and they will be scraping the same data in duplicate. If your data requirements are high, you may have to look into long term remote storage. https://prometheus.io/docs/prometheus/latest/storage/#remote-storage-integrations.

We have developed a new serverless time-series database as a service called Hyprcubd. Full disclosure, I’m the founder.  It was architected with IoT in mind. It supports multiple data types, long term storage, and high availability. https://www.hyprcubd.com


Transaction log backups cleaning I have a quick question regarding the cleaning of no longer necessary transaction log backups. So, I have a database in FULL recovery mode. Every night, at 10 PM a backup takes place (via maintenance plans). Now, I also have a different maintenance plan, that takes a transaction log backup every 15 minutes during the work hours, so at the end of the day, there are quite a few files.

What is the best way to clean these if the 10 PM backup executes successfully? Can I just delete all the transaction log backups from throughout the day? (at this point they are already caught in the backup, right?). Basically I am trying to start the day fresh, so to speak, with all the data backed-up and a new set of transaction log backups.

Thanks!

I would play it safe and delete files older than three days, a week, two weeks, what have you. You never know when management will run to you saying someone accidentally deleted data yesterday or before the weekend and they need to restore to a point in time. Make sure you retain enough data to cover all of those possible scenarios, and if you don't have enough space, buy more - it'll be much cheaper than being up the creek without a paddle, trust me.

Also, make sure you are testing the ability to restore your backups before deleting older ones, and validating they're consistent. You suggested that you would feel comfortable deleting yesterday's logs based on the fact that last night's backup executed successfully. That the backup executed successfully isn't enough. You could be backing up corruption for a week, and how would you know unless you actually tried to restore?


Full and differential backups are taking way longer only from one server I see a perplexing issue on one quasi-production machine (SQL Server 2008 SP2 standard edition) only. Full and differential backup job all of a sudden started taking 48 hours since 2/4/2017. Full backup usually finishes within an hour and differential within minutes. There are four other servers backing up to same share and backup succeeded as usual. They are all virtual machines running on VMWARE and share same underlying SAN.

Sysadmin doesn't see any issues with SAN as well as backup share. I don't see any blocking. Replication from that machine is running fine. Transaction log backup job is running fine as most databases have simple recovery model. I can ping to backup share from machine which doesn't timeout.

I use Ola's maintenance solution. Cleanup jobs ran successfully as well. msdb database size is under half GB. Please let me know if I am missing an elephant in the room.

Here are waits on server. Full backup and differential backup jobs (different databases) are still in progress. I took full backup of a small database (60MB) locally and it finished in no time. Thank you.

Resolved. There was antivirus software trying to scan backup file as it was being created. Once sysadmin fixed it, differential backup finished within minutes as opposed to taking 2-3 hours.


Creating Bitmap Indexes on a Materialized View I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned:

  Also, remember that bitmap indexes are only suitable for static tables and materialized views which are updated at night and rebuilt after batch row loading.  If your tables are not read-only during query time, DO NOT consider using bitmap indexes!

Most of our Materialized Views are updated nightly using the fast refresh method. Does the above quote mean that the MV should be rebuilt, or that the indexes should be rebuilt?

The key phrase is "after batch row loading".  If your process does not meet this criteria then you do not need to drop/rebuild the bitmap indexes.
Jonathan Lewis has an excellent article on this.  A key point he gives is

You can get lucky -- but in general you should start with the
assumption that even a serialized batch update will be most effective
if you drop the bitmap  indexes before the batch and rebuild them
afterwards.

The whole article is good and ends with some key facts to remember about bitmap indexes:

If a B*tree index is not an efficient mechanism for accessing data, it is  unlikely to become more efficient simply because you convert it to a bitmap  index.

Bitmap indexes can usually be built quickly, and tend to be surprisingly  small.

The size of the bitmap index varies dramatically with the distribution of  the data.

Bitmap indexes are typically useful only for queries that can use several  such indexes at once.

Updates to bitmapped columns, and general insertion/deletion of data can  cause serious lock contention.

Updates to bitmapped columns, and general insertion/deletion of data can  degrade the quality of the indexes quite dramatically.


Hindrance in TSQL SVF Call I have a simply Products data table with 100k records (just generates some random data):

set nocount on

create table dbo.temp_Products
(
    [ProductID] int,
    [Type] tinyint,
    [Price] float,
    [Weight] float
)

declare @rowcnt int = 0
while (@rowcnt <= 100000)
begin
    insert into dbo.temp_Products
    select @rowcnt, 1+rand()*4, 1+rand()*100, 1+rand()*10

    set @rowcnt = @rowcnt + 1
end

And a scalar value function which does a simple logical calculation:

create function dbo.usvf_CalculateShipping
(
    @PricePerKG float = 1,
    @Type tinyint,
    @WeightInKG float = 1
)
returns float
as
begin

    return  /*get the appropriate factor to apply*/
            case
                when @Type = 1 then 0.1
                when @Type = 2 then 0.2
                when @Type = 3 then 0.35
                when @Type = 4 then 0.43
            end * @PricePerKG * @WeightInKG

end

However, when I run a query to call the SVF the resulting performance is impaired relative to the query calling the logic inline.  The executed SQL statements and results are as follows:

SQL:

select ProductID, case
                when [Type] = 1 then 0.1
                when [Type] = 2 then 0.2
                when [Type] = 3 then 0.35
                when [Type] = 4 then 0.43
            end *Price*[Weight] from temp_Products
where [Weight] between 2 and 8

select ProductID, dbo.usvf_CalculateShipping(Price, [Type], [Weight]) from temp_Products
where [Weight] between 2 and 8

Results:

Table 'temp_Products'. Scan count 1, logical reads 390, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 109 ms,  elapsed time = 103 ms.

(59938 row(s) affected)
Table 'temp_Products'. Scan count 1, logical reads 390, physical reads 0, read-    ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads     0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 609 ms,  elapsed time = 629 ms.

But if I get the execution plan of running both queries at once, the relative cost of each query is 50%, even though the inline SVF is considerably slower.

Why does SQL server run ths inline SVF query slower - both in CPU and elapsed time?
By centralising some simple logic it appears I impede performance through code reuse.  My problem is I need to call this SVF in numerous places without duplicating logic. If SQL server always impedes performance when using SVF why would I ever use them and is there a different solution?

Why does SQL server run ths inline SVF query slower - both in CPU and
  elapsed time?

Scalar valued functions are executed in a different context than the main query and setting that up for each call takes time.

  By centralising some simple logic it appears I impede performance
  through code reuse.

Yes, for scalar valued functions that is true.

  If SQL server always impedes performance when using SVF why would I
  ever use them

Good question, not sure if I can provide a use case where a scalar valued function would be the obvious answer.

  is there a different solution?

Yes, you can use an inline table valued function instead.

create function dbo.usvf_CalculateShipping2
(
    @PricePerKG float = 1,
    @Type tinyint,
    @WeightInKG float = 1
)
returns table as return
select case
         when @Type = 1 then 0.1
         when @Type = 2 then 0.2
         when @Type = 3 then 0.35
         when @Type = 4 then 0.43
       end * @PricePerKG * @WeightInKG as Shipping

You also have to change the way you call the function.

select ProductID,
       (
       select Shipping
       from dbo.usvf_CalculateShipping2(Price, [Type], [Weight])
       )
from temp_Products
where [Weight] between 2 and 8


Postgres HAVING ilike any wildcard in array_agg I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is:

SELECT p.id, p.name, array_agg(vo.name)
FROM product p
LEFT JOIN product_variation pv
ON p.id = pv.product_id
LEFT JOIN variation_option vo
ON pv.variation_option_id = vo.id
GROUP BY p.id
HAVING $1 ilike any(array_agg('%' || vo.name || '%'));

But it doesn't work. It gets even more complicated: ideally, I'd be able to input an array of strings, where any of them are similar to the aggregate. Any tips?

I realize this kind of works, just not as I need it to. The input value should be able to be a partial match for any of the array elements, but it is the other way around. The wild card would need to be in the input value.

... but it is the other way around. The wild card would need to be in the inputted value.

Yes, it's backwards, but ILIKE (internally operator ~~*) nor the alternative regexp operator ~* have commutators. I.e., you can't turn the expression around, can't switch left and right operand. See:

SELECT * FROM pg_operator WHERE oprname  IN ('~~*', '~*');

oprcom = 0 means no commutator. Details in the manual.

The right operand of (I)LIKE is a pattern, so switching is impossible. And the ANY construct only goes one way. Dead end. See:

Can PostgreSQL index array columns?

You can still turn it around using a different tool: the aggregate function bool_or():

SELECT p.id, p.name, array_agg(vo.name)
FROM   product           p
JOIN   product_variation pv ON p.id = pv.product_id
JOIN   variation_option  vo ON pv.variation_option_id = vo.id
GROUP  BY p.id
HAVING bool_or(vo.name ILIKE ('%' || $1 || '%'));

Also using plain JOIN instead of LEFT JOIN, which would be pointless in view of the later filter.

  ideally, I'd be able to input an array of strings

Now that's also possible for an input array:

...
HAVING bool_or(vo.name ~* ANY($1));

$1 being of type text[] or an array literal like '{foo, bar}'. I chose the regexp operator as we don't need leading and trailing % with it.

Since $1 is a pattern (or an array of patterns), as mentioned above, you may want to escape characters with special meaning (per array element). See:

Escape function for regular expression or LIKE patterns

If your input is selective, meaning only very few products actually qualify, then an alternative formulation with EXISTS might be faster - in combination with a trigram index on vo.name. See:

How is LIKE implemented?

Index:

CREATE INDEX variation_option_name_gin_trgm_idx ON variation_option USING gin (name gin_trgm_ops);

Query:

SELECT p.id, p.name, array_agg(vo.name)
FROM   product           p
JOIN   product_variation pv ON p.id = pv.product_id
JOIN   variation_option  vo ON pv.variation_option_id = vo.id
WHERE  EXISTS (
   SELECT -- list can be empty
   FROM   variation_option vo1
   WHERE  vo1.id = pv.variation_option_id
   AND    vo1.name ILIKE ('%' || $1 || '%')
-- AND    vo1.name ~* ANY($1)  -- for array input
   )
GROUP  BY p.id;

Related:

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


How can I reclaim space after nulling out LONGTEXT records on MySQL? I support an application with a table with a LONGTEXT column. (We actually support multiple DBMSes, but for the purposes of this question, I am asking about MySQL.)

As it turns out, the data in that column comprises a very large chunk of the total usage in the entire database (about 40%!), and that data is simply debugging information anyways.

Now, we want to keep the last month of debug data around, so we can't drop the column entirely, so I assume we can free up space by updating older rows to NULL.

My questions:

When I set the values of that column to NULL, does it actually delete the data? I know that LONGTEXT data is stored separately from the rest of the table. Are the now-orphaned entries "garbage-collected" or otherwise cleaned up at some point?
If this doesn't happen automatically, what do I need to do to trigger a cleanup?
Once the data is actually deleted, will that space be reclaimed by the OS/filesystem, or will it remain claimed by the database? If we wanted to release  the space back to the filesystem (so we can pay for a smaller hosted instance), how would we go about releasing that space?

Yes and no.

In InnoDB, "small" columns are stored with the record in a BTree.  "Large" columns are stored elsewhere.  The cutoff between small and large is complex.  So let me ask how big your LONGTEXT values actually are.

When you shrink something that is stored with the record, it is unlikely to free up any space other than in the one 16KB block in question.  This can be reused if some row is inserted or a column is updated to be bigger.

Or, if two adjacent BTree blocks are only half full, the engine may coalesce them, thereby freeing up a block.  More on free blocks later.

If you are NULLing a large value (let's say more than 3KB), then the space in the off-record storage is freed.  This will free up some number of blocks, depending on the actual size.

What about the freed up block(s)?  Well, they are parts of "extents".  Etc, etc.

Bottom lines:

Nulling a column may free up block(s)
Those blocks may be available for future activity.
The blocks will not be released back to the filesystem of the OS.  That is ibdata1 nor .ibd will shrink.

OK, I told you more than you needed.  Let me re-address the question of "Can I pay for a smaller VM by doing such NULLing?"

Again, Yes and no.

No, the disk allocation will not automatically shrink.

Yes, in that you could take action to give the space back to the FS.  Now, to discuss this...

OPTIMIZE TABLE blocks usage of one table, but shrinks its disk footprint if it had been created with innodb_file_per_table = ON.  Else, no benefit.
Dumping the entire database and starting over by reloading it on a fresh MySQL instance, will release space, regardless of the above setting.  But this is a big task.
Check with the VM provider.  They may have a way of doing that dump+reload.

To get an estimate of the disk space needed, see the SELECT in http://mysql.rjweb.org/doc.php/memory#another_algorithm .  But double what it gives you -- you do need space for various other things.


How to query readonly replica in Always On cluster We have an Always On "cluster" that consists of 2 servers (there will be more), so one is PRIMARY and other(s) is(are) SECONDARY. The idea was to dedicate SECONDARY as a readonly replica, so it would be a somewhat search-server.

I do know how to set ConnectionString in the C# so it will use SECONDARY replica - just add ApplicationIntent=ReadOnly.

But I don't understand how to address the SECONDARY from stored procedure if I'm using distributed query. Is there a way to set some parameters to the query (like WITH statements or something), so query would use only replica and not the PRIMARY node?

The thing is, obviously from the start server1 is PRIMARY and server2 is SECONDARY, but when server1 fails then server2 is PRIMARY and server1 will be SECONDARY after it get fixed. So I cannot just use the static server2's name.

So far I've managed to get current replica's server name as a variable and use it in EXEC:

-- find first available replica
DECLARE @replicaServer nvarchar(50)

SELECT TOP 1
    @replicaServer = RCS.replica_server_name
FROM
    sys.availability_groups_cluster AS AGC
    INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
          ON RCS.group_id = AGC.group_id
    INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
          ON ARS.replica_id = RCS.replica_id
    INNER JOIN sys.availability_group_listeners AS AGL
          ON AGL.group_id = ARS.group_id
WHERE ARS.role_desc = 'SECONDARY'
      AND connected_state = 1

-- and query it
DECLARE @cmd nvarchar(MAX) = 'SELECT * FROM [' + @replicaServer + '].[somebase].[someschema].[sometable]'
EXEC (@cmd)

But that's a shame, I guess.

After some help and hints (especially thanks to Andriy M) I've managed to understand what I actually want and how to do it. This article from Microsoft helped as well: https://technet.microsoft.com/en-us/library/hh403414.aspx

First I had to create a read only listener. And then at the other server add linked server like this (borrowed from: https://stackoverflow.com/a/19585304):

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server     = N'cluster-readonly', -- just a name
    @srvproduct = N'SQL', -- anything except 'SQL Server'
    @provider   = N'SQLNCLI11',
    @datasrc    = N'ClusterListener', -- name of the readonly listener
    @provstr    = N'ApplicationIntent=ReadOnly',
    @catalog    = N'AnyActualDataBase' -- system database like 'master' is no good
GO

How do I check, where am I:

SELECT * FROM OPENQUERY([cluster-readonly], 'SELECT @@SERVERNAME')

Here's some trick - if I don't specify @catalog, then connection will be to the PRIMARY node for some reasons.


Dealing with out-of-range values on a PARTITION BY RANGE I have a table for storing events which is partitioned on a timestamptz column by month using PARTITION BY RANGE.

There are 5 partitions at the moment, containing a span of a month each, starting from FOR VALUES FROM ('2018-01-01') TO ('2018-02-01') and ending with FOR VALUES FROM ('2018-05-01') TO ('2018-06-01').

Most data is entered in a linear and predictable fashion. However, events are consumed by apps who report them, and I do have to allow for a past event to be entered at any time - which could have a timestamp earlier than 2018-01-01, or even a future event (for example a charge that is expected to occur some time in the future).

I was planning on creating one partition for past events that would span much longer than a month since there is no expectation of having too many of those.

I'm not sure what would be the best approach for future events where no partition exists for yet.

Is there a way to get the min/max values I could store within the existing partitions? If not, I could create a reference table that would store these values but I prefer to not have to maintain that.

Should I create a trigger to check for each row being inserted (seems expensive)? Should I capture errors on inserts and deal with those one at a time?

Running on PostgreSQL 10.3.

Is there a way to get the min/max values I could store within the existing partitions?

And you also ask in a comment:

  Do you know of a way to get the lower and upper bounds of the range by query?

I wouldn't know of any dedicated system catalog information function for this particular purpose. But:

Range partitioning is based on inheritance internally:

  Individual partitions are linked to the partitioned table with inheritance behind-the-scenes;

Inheritance trees are stored in pg_inherits:

  one entry for each direct child table

Partition bounds are stored in pg_class.relpartbound in internal format (pg_node_tree).
The system catalog information functions pg_get_expr(pg_node_tree, relation_oid) can:

  decompile internal form of an expression

We can build a query from this set of clues. Based on the example for range partitioning in the manual:

SELECT i.inhrelid::regclass
     , partition_bound
     , split_part(partition_bound, '''', 2) AS lower_bound
     , split_part(partition_bound, '''', 4) AS upper_bound
FROM   pg_inherits i
JOIN   pg_class    c ON c.oid = i.inhrelid
     , pg_get_expr(c.relpartbound, i.inhrelid) AS partition_bound
WHERE  inhparent = 'measurement'::regclass;

inhrelid             | partition_bound                                  | lower_bound | upper_bound
:------------------- | :----------------------------------------------- | :---------- | :----------
measurement_y2006m02 | FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') | 2006-02-01  | 2006-03-01
measurement_y2006m03 | FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') | 2006-03-01  | 2006-04-01

db<>fiddle here

Limitations:

Extracting lower and upper bound from the string based on single quotes is cheap & dirty. There is probably a cleaner way to extract the value from relpartbound directly.
Only including the first level of inheritance. You have to walk the graph in pg_inherits recursively to cover sub-partitioning.
This builds on several implementation details for declarative partitioning, which is a new feature of Postgres 10. While I do not expect this query to break due to changes in one of the next major versions, there is a chance it might.


Which database type for log analysis? I am running a database for log analysis. At the moment I use a MySQL database and the table for my analysis looks like this:

UUID
REQUEST_ID
REQUEST_TIMESTAMP
RESPONSE_TIMESTAMP
RUNTIME
SERVER_NAME
EXSIGHT_NAME

I use this table to create views for each entry, for 5 minute aggregation and for a daily aggregation. I am inserting about 400.000 entries a day. At the moment there are about 70 Million rows in this table.

My actual problem is, that my queries are getting slow, my insert/update queries as well as my aggregation queries.

So I created a second table for my daily aggregation. Once a day a job will run, to make an aggregation for the last day. A second job will delete all entries which are older than 30 days from the original table.

My question:
Is this the right approach or would be a different table structure or even a another database (e.g. NoSQL, Graphdatabase, etc.) better?

[EDIT]

Select for daily aggregation:

select
        date_format(REQUEST_TIMESTAMP,'%Y-%m-%d 00:00:00') as INTERVAL_START,
        null as INTERVAL_END,
        count(REQUEST_ID) as Anzahl,
        FORMAT((sum(RUNTIME)/count(REQUEST_ID))/1000,0) as dStime
    from REQUEST_LOGS
        where EXSIGHT_NAME like (case when '<EXSIGHT>' = 'alle' then '%' else '<EXSIGHT>' end)
        and SERVER_NAME like '<SERVER>'
        and (REQUEST_TIMESTAMP between '<FILTERFROMDATE>' and '<FILTERTODATE>')
    group by INTERVAL_START
    order by INTERVAL_START desc

Indexes:

You need several of my blogs:

Why UUIDs can be very slow - Do you need a UUID?
How to efficiently purge "older than X days" using PARTITIONing;
Incrementally augmenting a Summary Table, though you may have this 'right'.

How big is the table?  Which Engine are you using?  What is the value of innodb_buffer_pool_size?  It should be about 70% of RAM.


How does combining columns save disk space? I am creating a table in SQL server and inserting records. Three columns from this table are AAA int, AAB int and AAC int. If I merge AAB and AAC into one column Comb1 char(2), it consumes 20% less disk space compared with three original columns. But if I merge all three into one column Comb2 char(3), it still saves 20% disk space.

Can anyone explain this? And does the machine sort a combined column faster than sorting two columns independently?

Many thanks!

int and char(1) are not the same.

int stores a 32 signed whole number, 4 bytes
char(1) stores a single characters, 1 byte (on SQL Server, does not use UTF-8 etc)

So, it makes no sense to convert them as it stands.
Which means your "20% less disk space" is wrong.

Did you SUM reserved pages for a freshly re-indexed table that is at least 1MB?

If you are using int to store boolean (true or false), and you are converting to Y or N in the char(1), then a more efficient solution is to use bit. Which will take even less space. 3 bit columns will use one byte.

Edit, explanation

SQL Server has very specfic rules about how data is stored, based on datatype and NULLability. This takes into account variable or fixed length data, as well as precision (for decimals).

The issue here was choosing the wide 4 byte int instead of single byte tinyint.

Using tinyint means the data stays as a number, no conversions, and no fancy combine-in-a-single-colun madness. And is the smallest data type possible for this data


MySQL Local Variable Scope and Triggers I have a couple triggers that automatically update invoice data and set some statuses in the database.

However, it appears that if 2 Triggers have variables that are named the same, they get overwritten when one trigger performs an update the causes the other trigger to run.

For Example,

My BEFORE INSERT trigger on my payments table sets the variable @amountPaid

SET @amountPaid = @totalPayments + NEW.amount

Sometimes the before insert on payments will trigger an update to the main record which fires my BEFORE UPDATE trigger which selects the total payments into @amountPaid for it's own conditionals. However, at this point @amountPaid is 0 from the this perspective because the insert on payments hasn't completed, but this isn't important. What's very odd is that the @amountPaid variable in the payments trigger is now 0.

So now I'm curious as to how scope works in MySQL?

It depends on the scope of the variable.

I once wrote a post Disable trigger for just one table which uses a variable called @TRIGGER_DISABLED.

In order for the variable to disable the trigger, your session has to instantiate the value SET @TRIGGER_DISABLED=0; or SET @TRIGGER_DISABLED=1; before any DML again the table. If you disconnect and reconnect, you have to instantiate the variable each time you reconnect.

In your case, when the DB session starts, @amountPaid and @totalPayments would have to set to 0 before doing anything. You cannot disconnect and reconnect if you are keeping running totals.

If you always disconnect and reconnect, the running totals could be stored in a global table that uses the MEMORY storage engine with the fields and a session ID of your choosing..


Inserting sorted results to another table not working I have been wondering about this tech difficulties

when I executed

(SELECT
  a.id
, a.no_trx
, SUBSTRING_INDEX(a.no_trx,'/',1) AS 'tipe_trx'
, a.value_date
, a.id_inventory
, a.no_lot
, a.leading_char
, a.amount_in_base
, a.total_price_in_base
, a.qty
, a.unit_id
FROM ksm_mutasi_inv a
JOIN ksm_inventory b
    ON a.id_inventory = b.id
WHERE b.standar_inv_type = 5
ORDER BY a.value_date ASC)

the results are

id  no_trx  tipe_trx    value_date  id_inventory    no_lot  leading_char    amount_in_base  total_price_in_base qty unit_id

....

116 TAI/16/03/0062  TAI 2016-03-31  3648    18.51   +   4695.0000   128205426.0000  4695.0000   1
138 TAI/16/03/0084  TAI 2016-03-31  6282    S5620-1298  +   1828.4500   67320204.5455   1828.4500   1
809 TKMR/16/04/0002 TKMR    2016-04-01  6277    1602-03C    -   76.6500 2880366.2427    1.0000  5
857 TKMR/16/04/0004 TKMR    2016-04-01  6278    2602-03C    -   548.3500    17862391.2289   1.0000  5
1585    TKMR/16/04/0050 TKMR    2016-04-01  6278    2602-03C    -   302.5500    9855505.5463    1.0000  5
825 TKMR/16/04/0003 TKMR    2016-04-01  6271    1601-03B    -   1501.5000   47585767.7650   1.0000  5

.....

but when I using

INSERT INTO ksm_mutasi_inv_benang
    (id,no_trx,tipe_trx,value_date,id_inventory,no_lot,leading_char,
     amount_in_base,total_price_in_base,qty,unit_id)
(SELECT
  a.id
, a.no_trx
, SUBSTRING_INDEX(a.no_trx,'/',1) AS 'tipe_trx'
, a.value_date
, a.id_inventory
, a.no_lot
, a.leading_char
, a.amount_in_base
, a.total_price_in_base
, a.qty
, a.unit_id
FROM ksm_mutasi_inv a
JOIN ksm_inventory b
    ON a.id_inventory = b.id
WHERE b.standar_inv_type = 5
ORDER BY a.value_date ASC);

and do select * from ksm_mutasi_inv_benang

the results are

id  no_trx  tipe_trx    value_date  id_inventory    no_lot  leading_char    amount_in_base  total_price_in_base qty unit_id

....

137 TAI/16/03/0083  TAI 2016-03-31  6282    S5620-1298  +   195.9600    7214890.9091    195.9600    1
138 TAI/16/03/0084  TAI 2016-03-31  6282    S5620-1298  +   1828.4500   67320204.5455   1828.4500   1
755 TTB/16/04/0001  TTB 2016-04-14  3648    20.52   +   1814.4000   49545455.0000   10.0000 2
756 TTB/16/04/0002  TTB 2016-04-19  6224    12-45   +   907.2000    31477273.0000   5.0000  2
759 TTB/16/04/0003  TTB 2016-04-19  6224    12-45   +   3628.8000   127272728.0000  20.0000 2
765 TTB/16/04/0004  TTB 2016-04-23  6223    12-40   +   1814.4000   50000000.0500   10.0000 2

....

Why is this happening although I already using sort before I inserted it ?

CREATE TABLE ksm_mutasi_inv_benang (
  id int(11) NOT NULL DEFAULT '0',
  no_trx varchar(255) DEFAULT '' ,
  tipe_trx varchar(255) DEFAULT '',
  value_date date DEFAULT NULL ,
  id_inventory int(11) DEFAULT '0',
  no_lot varchar(255) DEFAULT '' ,
  leading_char char(1) DEFAULT '' ,
  amount_in_base decimal(20,4) DEFAULT '0.0000',
  total_price_in_base decimal(20,4) DEFAULT '0.0000',
  qty decimal(20,4) DEFAULT '0.0000',
  unit_id int(11) DEFAULT '0',
  UNIQUE KEY idx_id (id),
  KEY idx_id_inventory (id_inventory),
  KEY idx_no_lot (no_lot),
  KEY idx_id_inv_no_lot (id_inventory,no_lot),
  KEY idx_tipe_trx (tipe_trx),
  KEY idx_value_date (value_date)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Discovery 1
I just found out by eliminating the index in ksm_mutasi_inv_benang I get the results that I want. Is this really Mysql Bug ?

Short answer:  Use ORDER BY on a SELECT if you want a particular order.

Long answer:

Without an ORDER BY clause on SELECT, the engine is free to order the results in any way it feels like.  This is what you need:

select * from ksm_mutasi_inv_benang
     ORDER BY value_date

But digging a little deeper...  You are copying id from one table to another.  Presumably id is/was AUTO_INCREMENT and the PRIMARY KEY in the source table.  Did you really need to copy it over?  If not, some other ordering may have occurred.

Further...  Using ORDER BY in the INSERT..SELECT was wasted effort since the target table might have its own concept of ordering.  In this particular (and uncommon) case, you have UNIQUE KEY idx_id (id), which was treated as the PRIMARY KEY.  Then, in InnoDB, the data is ordered by the PK.  So, the order of the data in the table is by id.  And the SELECT without an ORDER BY (or WHERE) will simply read the data in PK order.


MySQL to add to cart 1-4 Socket Server When I check for price tags for MySQL, I see a clause called "1-4 socket server" or "5+ Socket Server".. what does this mean?

"Socket" in generic term means Inter Communication Protocol. For MySQL, a socket unix file helps to communicate between MySQL server and MySQL client within local server, and there is only one as far as I know per MySQL server instance.

Please correct me if my understanding is wrong. Kindly shed some light what is meant by 4, 5+ sockets as I see cost increases as socket numbers rises.  Also, what are the advantages of the higher sockets?

Socket here means physical CPU plugged into the motherboard.

Note that a single socket will consist at least one "core".

From the MySQL products page:

  5 Socket: is defined as a slot that houses a chip (or a multi-chip module), which contains a collection of one or more cores. Regardless of the number of cores, each chip (or multi-chip module) counts as a single socket. All occupied sockets on which the Oracle programs is installed and/or running must be licensed.

** I use "plug" loosely nowadays of course


Extracting week from date seems to give incorrect value at the start of the year I have a query about extract(week from {date}) - when switching over to subsequent years, where I would expect it to return 1, it tends to return 53. To illustrate this point, consider the following bit of code:

with
    __steps as(
        select
            unnest(array['2015-12-20', '2015-12-27', '2016-01-03', '2016-01-10', '2016-01-17']::date[]) as step_from,
            unnest(array['2015-12-26', '2016-01-02', '2016-01-09', '2016-01-16', '2016-01-23']::date[]) as step_to
    )
select
    __steps.step_from,
    __steps.step_to,
    extract(week from __steps.step_from) as week
from
    __steps
order by
    __steps.step_from asc

This returns the following results

step_from   step_to     week
----------  ----------  ----
2015-12-20  2015-12-26  51
2015-12-27  2016-01-02  52
2016-01-03  2016-01-09  53
2016-01-10  2016-01-16  1
2016-01-17  2016-01-23  2

I would expect week starting Jan'03 to be week 1, but as you can see it's not. Is this an international standards thing? Or do I just have my Postgres not quite setup right?

show timezone
-------------
Europe/London

PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

I think I actually have an answer to this (typically mere minutes after asking my question), I was being severly thrown off by other extract() functionality.

2016-01-03 was a sunday. select extract(dow from '2016-01-03'::date) returns 0.

I was assuming that because extracting the day-of-week of a Sunday returns 0, then extract would consider weeks to start from Sunday, when in fact it considers weeks to start from Monday, as evidenced by this:

with
    __steps as(
        select unnest(array['2016-01-03', '2016-01-04']::date[]) as step_date
    )
select
    extract(week from step_date) as week,
    extract(dow from step_date) as dow,
    to_char(step_date, 'Day') as dow_name
from
    __steps
order by
    step_date asc

Returning

week    dow     dow_name
----    ---     --------
53      0       Sunday
1       1       Monday


While importing 150 GB file on Amazon RDS, why connection is failing? I am having Postgres database dump file of 150 GB on Amazon EC2 instance. While dumping the data on RDS from EC2 server I am getting error.

The output of the command is as given below. It's giving error

1. psql connection not open
2. connection to server was lost
on RDS dump postgres copy command

Command Output:
SET
SET
SET
SET
SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
psql:filename.sql:1396266: connection not open
psql:filename.sql:1396266: connection to server was lost

Application Configuration:

Ruby 1.9.3
Rails 3
PostgreSQL 9.3

Please help why it is breaking while copying the data. As the connection was established and command is running. Suddenly while executing the copy command it is breaking.

Command/script that I am using to dump data the data is below:

psql -h instance.id.region-2.rds.amazonaws.com -p 5432 -U username -W -d database_name -f filename.sql

Workaround for finding issues as below:

I took head 100 rows and tail 200 rows and made 1 file from the big 150 GB file it converted to 56KB. when I run this command, it's dumped successfully.

So the file size is causing problem. The same command is running for small size file for dumping data on RDS.

How can I resolve this issue?

It's the file size that's the problem.

Essentially there's a timeout expiring somewhere, the server logs will tell you where.

When the timeout expires, no more data is produced, and after a while with no data the connection gets closed as per normal.

The last time I had to tackle something like this (Which is a long time ago now), I wrote a small program that basically pulled the data out of my server in chunks using various SQL criteria.

This is absolutely NOT the best way of doing it, but when you have a table that's so big and takes so long that things start to time out, it's one of the only ways I found to make it work.

The other approach you have is to use an ETL tool such as Pentaho and do a server to server transfer, again using a custom query to do it a chunk at a time.

If you MUST do it using PSQL, then try to break it up.  Instead of one massive 150gb file, try doing the xfer a table at a time or similar.

Also, think about local resources.  You might get better traction if for example, you can dump the file to a server where you have file system access that's inside amazons network, such as an AWS instance.  If you have a local resource, set up an AT job, dump the DB to that instance (optionally G-zipping it in the process) then transfer that file to your PC using a web browser of FTP connection.

Tackling the initial problem head on, your not going to know which approach you have to take until you can figure out why it's timing out.


How to identify what SSIS package(s) use a given Environment Variable? I suspect my Google skills are just insufficient today, but I'm looking for a quick TSQL script that will identify all SSIS packages in the Package Store that are using a given Environment Variable Name.

For instance, when I Configure a package and look at the Paramaters page, I can choose a given Environment Variable.  In the following screen shot, the environment variable I'm concerned with is named SMTP.  I want to find all packages using this environment variable so I can perform some investigation/testing before making any en masse changes.

If someone has a script readily available, I'd be grateful if you could post it, otherwise I'll post something after I code it up.

Thanks!

Since Environment Variables can be used at either the Project or Package level, this query will indicate said level the variable is used and quickly return the results I want.  Hopefully this will come in handy for someone else in the future.

SELECT    objp.[referenced_variable_name] AS [EnvironmentVariable]
        , fldr.name AS FolderName
        , proj.name AS ProjectName
        , COALESCE('Package: ' + pkg.name, 'Project') AS Scope
        , objp.parameter_name COLLATE Latin1_General_CS_AS AS ParameterName
FROM SSISDB.catalog.object_parameters objp
    INNER JOIN SSISDB.catalog.projects proj
        ON objp.project_id = proj.project_id
    INNER JOIN SSISDB.catalog.folders AS fldr
        ON proj.folder_id = fldr.folder_id
    LEFT JOIN SSISDB.catalog.packages pkg
        ON objp.object_name = pkg.name
        AND objp.project_id = pkg.project_id
-- Only search Projects/Packages that reference Environment variables
WHERE objp.value_type = 'R'
    AND objp.referenced_variable_name LIKE '%SMTP%'


How do you correct a corrupted index in Oracle? I have this ORA error in our logs:

Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: '/data/app/oracle/oradata/MYSRVR/datafile/o1_mf_mysrvr_88m82mdj.dbf'

I tried running this in sqlplus:

select segment_name,segment_type,tablespace_name, owner from sys.dba_extents where file_id=8 and 22921 between block_id and block_id + blocks -1;

output is:

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME     OWNER
-----------------------    ------------     ----------------    ---------------
PK_ZXCATSET_CATID      INDEX            MYSRVR_IDX_TB    MYSRVR

Now I am stuck on which index is that actual segment name. (I think :/ ) I tried doing this to rebuild that index:

SQL> alter index PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;

ERROR at line 1: ORA-01418: specified index does not exist

Even though if I do "select index_name from dba_indexes", that index name shows up. What am I doing wrong? This is a 10.2 Oracle server.

Thanks in advance!

EDIT If I do MYSRVR.PK_ZXCATSET_CATID  the alter completes... but I still don't think my problem is fixed!

Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:

[oracle@oca ~]$ rman target=/

RMAN> list faliure;

If there are failures with status OPEN listed, you can ask Data Recovery Advisor (DRA) to analyze them and recommend you the solution to repair the failures:

RMAN> advise failure;

Since you have block corruption, DRA will probably suggest you to recover the corrupted blocks and will create the appropriate script which you can run manually or in the same flow with RMAN:

RMAN> repair failure;

After failures are repaired they're automatically closed by DRA. You can check if all failures are closed with one more list failure command in RMAN, and if not you can get another advice from DRA.

EDIT:

Since the recovery you performed didn't last long (elapsed time: 00:00:00), I infer that the datafile wasn't written to by RMAN, and maybe even checked. Thus I would recommend you to proactively validate the database; to do that, just issue validate database in RMAN and analyze the output. RMAN will check all datafiles (including control file and spfile) and show you if there are any corrupted blocks.

In your comment, you said that RMAN returns the message "Block Media Recovery requires Enterprise Edition" in response to one of your commands, and based on the response from RMAN to validate database command I suspect this feature is also not available in your software edition. If your database is running in ARCHIVELOG mode (check with select log_mode from v$database) you could just completely restore and recover your database without loss of committed transaction, but before doing this I would make sure that your media (hard disk) is not damaged.


Seeing 2 very different execution plans for an UPDATE between test & prod environments I am seeing 2 very different query plans between test/prod for an UPDATE statement that is part of a stored procedure. Obviously table size differs (about 10 million rows difference). Basically the difference is in Prod I am seeing sets of an expensive Sort followed by an Index Update (nc index)

Whereas in Test, I am not seeing these sets of operators at all! Only the Clustered Index Update. I've verified the NC indexes exist etc. etc. I can't figure out what is going on?! I've verified indexes, the sp, I've tried RECOMPILE, different values for the param's etc. There's got to be something I'm missing, I've even checked constraints, everything matches.

Any thoughts ideas? I've never seen this before. Did SQL just grab a bad execution plan!?

The UPDATE is structured in a way: UPDATE [tablename] SET [column]=123 FROM ...

The extra operators in the plan from Prod below (minus Clustered Index Update):

Hover over the clustered index update, you'll see a list of the other indexes involved.

If you use Plan Explorer instead, you'll see the number of nonclustered indexes noted.

See Wide vs. Narrow Plans by Bart Duncan.


Is it normal to UPDATE STATISTICS this frequently? I have a stored procedure that processes data from a source table and is executed many times (about 20,000 executions) throughout the day.

I believe it's getting choked up sometimes, due to parameter sniffing and I've been updating statistics on the relevant tables (8 tables) in the procedure to fix this.

When it gets choked up, a backlog of unprocessed data builds up (which I'm able to monitor the count on.)

Once I update the statistics the backlog is instantly cleared.

I find myself updating statistics about 10 times a day (almost once an hour).
Does this seem normal or is there a bigger issue I should be looking to fix?

There is multiple ways to fix the parameter sniffing issue.

If you update your statistics 10 times a day and especially if that is causing performance issues, then I would look at other options.

There is some good chance that the update of the stats is not what's fixing your parameter sniffing issue. The update of the stats causes the plan to be flushed from the cache (which is probably fixing your issue). Flushing only that plan from the cache would probably work as well without having to rebuild the stats ;)

If I were you, I would take it from the start and find which set of parameters causes SQL to take a bad execution plan. Once you have this, you can then start looking at the way to fix it.

You could use the QueryStore to fix this, forcing SQL to use always the "good" execution plan for example.

Sometimes, A good index can also fix parameter sniffing issue (that can cause SQL to build a different execution plan that would be good  for whatever the parameter is)


Postgresql regex match all digits in column How do I extract all digits from a string with regex in pgsql?

select regexp_matches('dsa8a552a5a2a5?', '\d+');

Will output only 8.

Need to get 8552525

Better use regexp_replace, globally replacing non-digits by an empty string:

select regexp_replace('dsa8a552a5a2a5?', '\D', '', 'g');

 regexp_replace
----------------
 8552525


Enable sysadmin user to restart SQL server from management studio I have SQL server login with sysadmin role, but I cannot stop and start SQL server when I connect from remote management studio, just from local server management studio. How can I enable it?
I use windows server 2008r2, SQL server 2008r2.

If you connect to SQL Server from a Machine M which has SSMS installed but your account is not local admin on Machine M you would find the option of start/stop to be disabled or greyed out. OTOH if you are local admin on the Machine M you would find the option to be enabled. When you do this from server on which SQL Server is installed, you are administrator there and hence you see the option. It does not matter whether your account is admin in SQL Server or not when you connect from machine where you do not have local admin privileges you would see the option disabled, however you would still be able to perform all sysadmin activities in SQL Server. This is not a complete statement their could be other reasons as well, this is what mostly I have observed, please check and let me know. Please also refer to SQL Server agent start stop and restart disabled blog to know more about reasons why this could happen


Execution plan doesn't use clustered index! I'm using "SQL*Plus: Release 10.2.0.1.0 - Production" and have a table foo with a field fooID.

I have created a clustered B+tree index on the field foodID of my table foo.

And I do the following:

explain plan set assignment_id='' for select foo_id from foo;

which creates the following plan:

0  SELECT STATEMENT
1 0   TABLE ACCESS FULL FOO

then I add the following hint:

explain plan set assignment_id='' for select /*+ INDEX(foo) */ foo_id from foo;

but still got the same plan without using my index.

I'm sure the index is correctly created, and I also executed analyze table foo compute statistics;. I also tried the above in three different optimization modes: RULE, CHOOSE, ALL_ROWS.

So why doesn't my hint work?
Any ideas would be rally helpful!

I'm using "SQL*Plus: Release 10.2.0.1.0 - Production"

Which tells us nothing about your Oracle Server version unfortunately! The bit below ("Connected to...") is about the server side

  select foo_id from foo;

The clustered index does not contain a pointer to each row of the table - instead it contains pointers to clusters (groups of rows with the same foo_id). It could in theory be useful for a query like select distinct foo_id from foo; (no idea if it actually would though), but you still need to go to the underlying table to answer select foo_id from foo;.

  So why doesn't my hint work

Because it is just a hint - the CBO is clever enough to ignore it if it means full scanning the clustered index and the table instead of just the table

Also the correct hint for a clustered scan is cluster rather than index eg /*+ CLUSTER(foo) */


I know it is not easy getting an execution plan for an update from MySQL since it only provides those on SELECT statements. But the clue might be in the order in which the records are updated, the evaluation of a WHERE which contains a IN with a large amount of static data, as well as the amount of connected reads and writes, intermediate caching,  associated with that.

The statement

UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000);

is a type of statement we try to avoid when updating larger databases since the parser seems to go wild on them from time to time. IN ( a,b,c,...,ZZZZ ) for me has become a coding style only suitable for very small item numbers in the IN data.
I am working on a open source project where I frequently run into what I call "remote minded join", the second half usually looks exactly like your problem.

SELECT id FROM all_our_customers WHERE happytospam=1 AND LENGTH(email) > 6;
...
Storing result on client side as string like
LOOP over results
$all_ids += ",$next_result";
END_LOOP
$all_ids = SUBSTRING($all_ids,1);
ending up with a string like
"1,2,3,4,5,8,10,100,1000,...,100000" in $all_ids
...
UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN ( $all_ids )

While the first part usually executes lightning fast, the second part takes forever, which is what you describe as well.
These queries can usually be sped up EXTREMELY by rewriting them as:

UPDATE humans_we_respect,all_our_customers
SET humans_we_respect.expressed_interest=1
WHERE all_our_customers.id = humans_we_respect.id
AND all_our_customers.happytospam=1
AND LENGTH(all_our_customers.email) > 6

We also used

UPDATE humans_we_respect
SET expressed_interest=1
WHERE id IN (
SELECT id
FROM all_our_customers
WHERE happytospam=1
AND LENGTH(email) > 6
)

which performed better than the original but not as good as my suggested version.

This all is assuming that you use proper indexes with primary on ID and combined multi column indexes where multiple columns are frequently used together or have a good significance together and are usually present in your queries.

The Clue is that large amounts of static values in IN clauses increase the execution time nearly exponentially on queries with many matching records, since they basically do NOT USE ANY INDEXES OR OPTIMIZATIONS and usually end up in full table scans, in which IMHO the execution will examine every record/line in your table by comparing it with every item in your IN() list ONE by ONE.

The statement like

INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;

however is using the index to locate the record and then updates it, even so not intended for this use, it will run much better due to the use of a index on ID if there is one and will only do a index-lookup for the record rather than thousands of comparisons!
Working directly with two tables might however be faster if the list of IDs is derived from another table on the same server, there are more optimizations that can be used plus you do not have to transfer data out-of and into the mysql server process.

Just as some extra info:

UPDATE humans_we_respect SET expressed_interest=1 WHERE id='1' OR id='2' OR ...

is a good technique to optimize IN() queries with very low counts of elements since it will create a parallel index query for every element, which is great for the first elements but will decrease in performance with more elements a lot, and at some point hit the limit of the parser for optimization (IMHO it might be 255 elements in one query) at which point it will grind to a snails pace again...


What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)? Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?

SELECT id FROM table
    WHERE table.int_col > 1;

I'm specifically interested in knowing if the query is significantly more inefficient than if doing an equality clause (which, if I understand correctly is O(log N)).  I am pretty sure both of them can use a B-tree, so I would expect them to be about the same in terms of complexity / efficiency.

Thanks very much!

I don't know the PostgreSQL engine, however in the real world, doing

WHERE X > y

will cause the engine to seek to the point in the B-tree index where X = y, then scan the rest of the data to return the relevant rows.  With enough data, it will take significant time to return.

Clearly, WHERE X = y will return more quickly since it only has to return a single row, necessitating only the initial seek operation.

This is, of course, assuming X is indexed.


Database mail in Azure VM with SQL Server installed on it We migrated 4 servers with SQL Server instances to Azure VM around 2-3 months ago. We configured "database mail" the same way we did for the on-prem servers. Mail worked fine immediately. All of a sudden, yesterday around 4 pm, mails were not able to be sent.

Error is: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-02-06T08:20:42). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond :25).)

We have researched and all we find is that we should not use port 25, but when we change it to port 587 it failed as well.

The settings we use to configure database mail account are:

Server name: .mail.protection.outlook.com
Port: 25
SMTP Authentication: Anonymous.

By the way, the servers we still have on-premises are working fine using the exact same settings.

As a workaround, we have configured sendgrid to send mail for the azure servers, but my question is if someone can help us to configure the SQL Server database mail correctly in the azure vm using the office 365 exchange.

Thanks!

Check out this link - basically, you need to use an authenticated relay (like sendgrid) when sending from an Azure VM.

There are some exceptions:

Enterprise Agreement subscriptions don't have this restriction
Pay-As-You-Go subscriptions can submit an exemption request and if approved you will not have to use an authenticated relay. Note, this only applies to VMs. PaaS services cannot be exempted.

All other subscriptions do not have the exemption option - you must use an authenticated relay.


Delete all duplicated entries where multiple columns duplicates match I need to add this contrain to my table called 'kraken'
ALTER TABLE kraken ADD UNIQUE unique_index(exchange_pair,timestamp);

this shows me that i have duplicated entries on those 2 columns (exchange_pair and timestamp)
so first i have to delete all the duplicated entries based on those 2 columns
this is what i found but im not sure if my query would work since i need to delete only duplicated entris with column 'exchange_pair' and 'timestamp', i dont want to mess things up with my table
https://www.mysqltutorial.org/mysql-delete-duplicate-rows/

The easiest way to remove duplicates is to join the table with itself:
DELETE z.*
  FROM kraken AS w
  JOIN kraken AS z ON z.exchange_pair = w.exchange_pair
                  AND z.timestamp = w.timestamp
                  AND z.id > w.id -- or what primary key you have
;

All duplicates will be deleted except one with the lowest id.


How can I view how long a query has spent waiting for locks, without a profiler? I'm trying to diagnose a query that is intermittently taking a long time. I suspect it may be blocked trying to acquire a lock. I don't have permissions to use a profiler in the environment experiencing the issue.

Is there any way for me to obtain statistics on how long this individual query is blocked, without using an external profiler?

(If you have access to DMVs then look into sp_whoisactive with @find_block_leaders = 1. Just tell you DBA (if you are not) to deploy it and grant you execute permission.)

SQL server dynamic management views are your best friend :

Below are several ways to find out blocking :

--Ref: https://sqlserverperformance.wordpress.com/category/diagnostic-queries/
select t1.resource_type as 'lock type'
    ,db_name(resource_database_id) as 'database'
    ,t1.resource_associated_entity_id as 'blk object'
    ,t1.request_mode as 'lock req'
    ,--- lock requested
    t1.request_session_id as 'waiter sid'
    ,t2.wait_duration_ms as 'wait time'
    ,-- spid of waiter
    (
        select [text]
        from sys.dm_exec_requests as r -- get sql for waiter
        cross apply sys.dm_exec_sql_text(r.sql_handle)
        where r.session_id = t1.request_session_id
        ) as 'waiter_batch'
    ,(
        select substring(qt.text, r.statement_start_offset / 2, (
                    case
                        when r.statement_end_offset = - 1
                            then LEN(CONVERT(nvarchar(max), qt.text)) * 2
                        else r.statement_end_offset
                        end - r.statement_start_offset
                    ) / 2)
        from sys.dm_exec_requests as r
        cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
        where r.session_id = t1.request_session_id
        ) as 'waiter_stmt'
    ,-- statement blocked
    t2.blocking_session_id as 'blocker sid'
    ,-- spid of blocker
    (
        select [text]
        from sys.sysprocesses as p -- get sql for blocker
        cross apply sys.dm_exec_sql_text(p.sql_handle)
        where p.spid = t2.blocking_session_id
        ) as 'blocker_stmt'
from sys.dm_tran_locks as t1
inner join sys.dm_os_waiting_tasks as t2 on t1.lock_owner_address = t2.resource_address;

A more deeper look into blocking :

-- Pedro Lopes (Microsoft) pedro.lopes@microsoft.com (http://blogs.msdn.com/b/blogdoezequiel/)
-- Waiter and Blocking Report
SELECT -- blocked
    er.session_id AS blocked_spid
    ,ot.task_state AS [status]
    ,owt.wait_type AS blocked_spid_wait_type
    ,owt.wait_duration_ms AS blocked_spid_wait_time_ms
    ,
    -- Check sys.dm_os_waiting_tasks for Exchange wait types in http://technet.microsoft.com/en-us/library/ms188743.aspx.
    -- Wait Resource e_waitPipeNewRow in CXPACKET waits – Producer waiting on consumer for a packet to fill.
    -- Wait Resource e_waitPipeGetRow in CXPACKET waits – Consumer waiting on producer to fill a packet.
    owt.resource_description AS blocked_spid_res_desc
    ,CASE
        WHEN owt.pageid = 1
            OR owt.pageid % 8088 = 0
            THEN 'Is_PFS_Page'
        WHEN owt.pageid = 2
            OR owt.pageid % 511232 = 0
            THEN 'Is_GAM_Page'
        WHEN owt.pageid = 3
            OR (owt.pageid - 1) % 511232 = 0
            THEN 'Is_SGAM_Page'
        WHEN owt.pageid IS NULL
            THEN NULL
        ELSE 'Is_not_PFS_GAM_SGAM_page'
        END AS blocked_spid_res_type
    ,(
        SELECT qt.TEXT AS [text()]
        FROM sys.dm_exec_sql_text(er.sql_handle) AS qt
        FOR XML PATH('')
            ,TYPE
        ) AS [blocked_batch]
    ,es.last_request_start_time AS blocked_last_start
    ,LEFT(CASE COALESCE(er.transaction_isolation_level, es.transaction_isolation_level)
            WHEN 0
                THEN '0-Unspecified'
            WHEN 1
                THEN '1-ReadUncommitted(NOLOCK)'
            WHEN 2
                THEN '2-ReadCommitted'
            WHEN 3
                THEN '3-RepeatableRead'
            WHEN 4
                THEN '4-Serializable'
            WHEN 5
                THEN '5-Snapshot'
            ELSE CONVERT(VARCHAR(30), er.transaction_isolation_level) + '-UNKNOWN'
            END, 30) AS blocked_tran_isolation_level
    ,er.total_elapsed_time / 1000 AS total_elapsed_time_sec
    ,
    -- blocker
    er2.session_id AS blocker_spid
    ,CASE
        -- blocking session is either not blocked or has open trans
        WHEN owt.waiting_task_address IN (
                SELECT owt2.blocking_task_address
                FROM sys.dm_os_waiting_tasks owt2
                )
            AND (
                er2.session_id IS NULL
                OR owt.blocking_session_id IS NULL
                OR owt.[blocking_task_address] IS NULL
                )
            THEN 1
        ELSE 0
        END AS is_head_blocker
    ,(
        SELECT qt2.TEXT AS [text()]
        FROM sys.dm_exec_sql_text(er2.sql_handle) AS qt2
        FOR XML PATH('')
            ,TYPE
        ) AS [blocker_batch]
    ,es2.last_request_start_time AS blocker_last_start
    ,LEFT(CASE COALESCE(er2.transaction_isolation_level, es2.transaction_isolation_level)
            WHEN 0
                THEN '0-Unspecified'
            WHEN 1
                THEN '1-ReadUncommitted(NOLOCK)'
            WHEN 2
                THEN '2-ReadCommitted'
            WHEN 3
                THEN '3-RepeatableRead'
            WHEN 4
                THEN '4-Serializable'
            WHEN 5
                THEN '5-Snapshot'
            ELSE CONVERT(VARCHAR(30), er2.transaction_isolation_level) + '-UNKNOWN'
            END, 30) AS blocker_tran_isolation_level
    ,
    -- other data
    DB_NAME(er.database_id) AS DBName
    ,es.host_name AS blocked_host
    ,es.program_name AS blocked_program
    ,es.login_name AS blocked_login
    ,CASE
        WHEN es.session_id = - 2
            THEN 'Orphaned_distributed_tran'
        WHEN es.session_id = - 3
            THEN 'Deffered_recovery_tran'
        WHEN es.session_id = - 4
            THEN 'Unknown_tran'
        ELSE NULL
        END AS blocked_session_comment
    ,es.is_user_process AS [blocked_is_user_process]
    ,es2.host_name AS blocker_host
    ,es2.program_name AS blocker_program
    ,es2.login_name AS blocker_login
    ,CASE
        WHEN es2.session_id = - 2
            THEN 'Orphaned_distributed_tran'
        WHEN es2.session_id = - 3
            THEN 'Deffered_recovery_tran'
        WHEN es2.session_id = - 4
            THEN 'Unknown_tran'
        ELSE NULL
        END AS blocker_session_comment
    ,es2.is_user_process AS [blocker_is_user_process]
FROM (
    --In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
    --waiting for several different threads.  This will cause that thread to show up in multiple rows
    --which is irrelevant.  Use ROW_NUMBER to select the longest wait for each thread
    SELECT [waiting_task_address]
        ,[session_id]
        ,[wait_duration_ms]
        ,[wait_type]
        ,[blocking_task_address]
        ,[blocking_session_id]
        ,[resource_description]
        ,CASE
            WHEN [wait_type] LIKE 'PAGE%'
                AND [resource_description] LIKE '%:%'
                THEN CAST(RIGHT([resource_description], LEN([resource_description]) - CHARINDEX(':', [resource_description], LEN([resource_description]) - CHARINDEX(':', REVERSE([resource_description])))) AS INT)
            ELSE NULL
            END AS pageid
        ,ROW_NUMBER() OVER (
            PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC
            ) AS row_num
    FROM sys.dm_os_waiting_tasks --ORDER BY session_id
    ) owt
INNER JOIN sys.dm_os_tasks ot ON ot.task_address = owt.waiting_task_address
LEFT OUTER JOIN sys.dm_exec_requests er ON er.session_id = ot.session_id
    AND er.request_id = ot.request_id
LEFT OUTER JOIN sys.dm_exec_sessions es ON es.session_id = er.session_id
LEFT OUTER JOIN sys.dm_exec_sessions es2 ON es2.session_id = owt.blocking_session_id
LEFT OUTER JOIN sys.dm_exec_requests er2 ON er2.session_id = owt.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE owt.row_num = 1
    AND es.session_id <> @@SPID
    AND es.is_user_process = 1
ORDER BY blocked_spid
    ,is_head_blocker DESC
    ,blocked_spid_wait_time_ms DESC
    ,blocker_spid
GO

Using Extended Event or BLOCKED_PROCESS_REPORT, you can find and get alerted when blocking occurs.


Searching a specift data on a table I have to search in a table, all the photos that start with the code "2950".
Example: 2950_1, 2950_2 ...
Something like:

SELECT * FROM `vnd_imovelfoto` WHERE `codigofoto` = 2950

You should use LIKE comparison operator in this way:

SELECT *
FROM   vnd_imovelfoto
WHERE  codigofoto LIKE '2950%'


Cannot GRANT privileges as root Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all rights to root at some local IP. I (as root@localhost) tried

CREATE USER 'root'@'10.0.3.210';
GRANT ALL ON *.* TO 'root'@'10.0.3.210';

The first command works, the second one fails with the message

ERROR 1045 (28000): Access denied for user 'root'@'localhost'

I don't get why root@localhost can't do everything, I'm sure I didn't mess with its privileges. From

SHOW GRANTS FOR 'root'@'localhost'

I get

Grants for root@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `%`.* TO 'root'@'localhost' WITH GRANT OPTION

whatever this means. Am I missing a needed privilege? Can it be fixed?

I'm working with mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (x86_64).

Oh my goodness, I think the problem stems from mixing the mysql schema of different mysql versions. First of all, run this query:
desc mysql.user;

For MySQL 8.0, you get 51 columns
mysql> desc mysql.user;

Field
Type
Null
Key
Default
Extra

Host
char(255)
NO
PRI

User
char(32)
NO
PRI

Select_priv
enum('N','Y')
NO

N

Insert_priv
enum('N','Y')
NO

N

Update_priv
enum('N','Y')
NO

N

Delete_priv
enum('N','Y')
NO

N

Create_priv
enum('N','Y')
NO

N

Drop_priv
enum('N','Y')
NO

N

Reload_priv
enum('N','Y')
NO

N

Shutdown_priv
enum('N','Y')
NO

N

Process_priv
enum('N','Y')
NO

N

File_priv
enum('N','Y')
NO

N

Grant_priv
enum('N','Y')
NO

N

References_priv
enum('N','Y')
NO

N

Index_priv
enum('N','Y')
NO

N

Alter_priv
enum('N','Y')
NO

N

Show_db_priv
enum('N','Y')
NO

N

Super_priv
enum('N','Y')
NO

N

Create_tmp_table_priv
enum('N','Y')
NO

N

Lock_tables_priv
enum('N','Y')
NO

N

Execute_priv
enum('N','Y')
NO

N

Repl_slave_priv
enum('N','Y')
NO

N

Repl_client_priv
enum('N','Y')
NO

N

Create_view_priv
enum('N','Y')
NO

N

Show_view_priv
enum('N','Y')
NO

N

Create_routine_priv
enum('N','Y')
NO

N

Alter_routine_priv
enum('N','Y')
NO

N

Create_user_priv
enum('N','Y')
NO

N

Event_priv
enum('N','Y')
NO

N

Trigger_priv
enum('N','Y')
NO

N

Create_tablespace_priv
enum('N','Y')
NO

N

ssl_type
enum('','ANY','X509','SPECIFIED')
NO

ssl_cipher
blob
NO

NULL

x509_issuer
blob
NO

NULL

x509_subject
blob
NO

NULL

max_questions
int unsigned
NO

0

max_updates
int unsigned
NO

0

max_connections
int unsigned
NO

0

max_user_connections
int unsigned
NO

0

plugin
char(64)
NO

caching_sha2_password

authentication_string
text
YES

NULL

password_expired
enum('N','Y')
NO

N

password_last_changed
timestamp
YES

NULL

password_lifetime
smallint unsigned
YES

NULL

account_locked
enum('N','Y')
NO

N

Create_role_priv
enum('N','Y')
NO

N

Drop_role_priv
enum('N','Y')
NO

N

Password_reuse_history
smallint unsigned
YES

NULL

Password_reuse_time
smallint unsigned
YES

NULL

Password_require_current
enum('N','Y')
YES

NULL

User_attributes
json
YES

NULL

51 rows in set (0.00 sec)
For MySQL 5.6, you get 43 columns
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.15 sec)

For MySQL 5.5, you get 42 columns
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)

mysql>

For MySQL 5.1, you get 39 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)

mysql>

For MySQL 5.0, you get 37 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.01 sec)

mysql>

For MySQL 4.x, you get 31 columns
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | varchar(60) binary                |      | PRI |         |       |
| User                  | varchar(16) binary                |      | PRI |         |       |
| Password              | varchar(16) binary                |      |     |         |       |
| Select_priv           | enum('N','Y')                     |      |     | N       |       |
| Insert_priv           | enum('N','Y')                     |      |     | N       |       |
| Update_priv           | enum('N','Y')                     |      |     | N       |       |
| Delete_priv           | enum('N','Y')                     |      |     | N       |       |
| Create_priv           | enum('N','Y')                     |      |     | N       |       |
| Drop_priv             | enum('N','Y')                     |      |     | N       |       |
| Reload_priv           | enum('N','Y')                     |      |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     |      |     | N       |       |
| Process_priv          | enum('N','Y')                     |      |     | N       |       |
| File_priv             | enum('N','Y')                     |      |     | N       |       |
| Grant_priv            | enum('N','Y')                     |      |     | N       |       |
| References_priv       | enum('N','Y')                     |      |     | N       |       |
| Index_priv            | enum('N','Y')                     |      |     | N       |       |
| Alter_priv            | enum('N','Y')                     |      |     | N       |       |
| Show_db_priv          | enum('N','Y')                     |      |     | N       |       |
| Super_priv            | enum('N','Y')                     |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     |      |     | N       |       |
| Execute_priv          | enum('N','Y')                     |      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     |      |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     |      |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob                              |      |     |         |       |
| x509_issuer           | blob                              |      |     |         |       |
| x509_subject          | blob                              |      |     |         |       |
| max_questions         | int(11) unsigned                  |      |     | 0       |       |
| max_updates           | int(11) unsigned                  |      |     | 0       |       |
| max_connections       | int(11) unsigned                  |      |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.01 sec)

Picture this scenario:

You install 5.0
You uninstall 5.0
You forgot to move /var/lib/mysql out of the way
You install 5.1

The user permissions start getting shifted. Whenever you run SHOW GRANTS;, it is hardwired to expect columns in specfic places in mysql.user (given the fact that mysql.user is a MyISAM and its ROW_FORMAT is Dynamic (Default)). It is very easy to see a user suddenly lose permissions when you reload a MySQL 5.0 version of mysql.user into a MySQL 5.1 instance.
In the future, should you ever decide to reload users into MySQL, try to dump the users to a text file using pt-show-grants rather than mysqldump.
I actually wrote my own version of pt-show-grants as follows:
mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

This will produce all user grants as SQL, which is far more portable than standard mysql techniques. Simply reload the SQL script and the GRANTS are executed. Of course, such reloading is only forward compatible, never backward.


MS Access Unable to Select Number Field Size I'm using Microsoft Access 2016 32bit on Windows 10. My issue is the Field Size property list is empty for the Number data type. Why is this empty? How can I change a number's field size? I am trying to specify the Byte field size for my field.

Even when starting from scratch and creating a new empty database, with new tables, I am unable to select a field size for the number data type.

With my existing databases I am unable to change field size of existing number type fields. For example, existing byte fields are still bytes however if I open the design view on the table and click the field size drop down list for that field the list is empty.

I expected a number field's field size drop down list to contain byte, integer, single, double, etc. however it is empty. This list was previously available to me.

Is there a MS Access setting that I need to change? Or is this a bug?

My computer recently had a windows update, it's unclear to me whether or not this included an office update.

UPDATE
As a workaround I can execute ALTER TABLE sql commands to modify my field's data types. examples

As of 7/27/16 I ran an update for Office and the issue appears resolved.
To run the update in MS Office 2016 go to any MS Office application and go to File -> Account -> Update Options -> Update Now.


Insert into DynamoDB when data has different attributes Is there any way dynamodb can handle dynamic attributes? Depending on the request, I need to store different web query parameters in the same table.

What I want to do is like this, if I make a java hashmap with query parameter's key and value, and just put them into dynamodb, then the dynamodb automatically uses hashmap's key for attribute's name and hashmap's value for attribute's value.

Actually I'm using Scala for server side and I know that Scanamo library can do this in easy way by making case classes for every case but it's quite inefficient for me because I have to make every case class for every combination of parameters and I don't event predict what kind of parameters would come.

Is there any way to do this in scanamo or dynamodb except for using insert JSON format?

val paramsMap = params.iterator.toMap
val item: Item = new Item()
  .withPrimaryKey(Partition_Key_Name, partition_key)
  .withKeyComponent(Sort_Key_Name, sort_key)

paramsMap.map { case (k, v) => item.withString(k, v) }
table.putItem(item)

can be solved like this in scala


Having issues in assigning mysql datatypes I am working first time with database. I had never inserted data into database.  I want to insert below csv into mysql database. But I facing issues with data types. What type of data type I should assign to the variables.

   "cell","id","too","Issue","Valid","DPT","RH","TMP","U","V"
"1",1,22383,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",267.800018310547,91.5999984741211,269.040008544922,-0.529999971389771,-1.78999996185303
"2",2,24532,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",272.600006103516,88.5,274.259979248047,-1.58999991416931,-0.239999994635582
"3",3,23936,"2015-01-15 00:00:00 GMT","2015-01-15 12:00:00 GMT",271,88.2000045776367,272.72998046875,-0.429999977350235,-1.01999998092651

Could anyone help me in figuring out the data types of the columns?

I have assigned datatypes below but I am getting so many values while inserting data:

CREATE TABLE `raster_cell_data` (
  `cell` varchar(100) NOT NULL,
  `id` varchar(100) NOT NULL,
  `too` int(14) NOT NULL,
  `Issue` datetime DEFAULT NULL,
  `Valid` datetime DEFAULT NULL,
  `DPT` varchar(100) DEFAULT NULL,
  `RH` varchar(100) DEFAULT NULL,
  `TMP` varchar(100) DEFAULT NULL,
  `U` varchar(100) DEFAULT NULL,
  `V` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I am loading data using the below command:

LOAD DATA LOCAL INFILE 'C:\\Users\\vkaushik\\Desktop\\Temp.csv'
    INTO TABLE raster_cell_data
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

Getting Error 1234: out of the range for many variables while loading. And only 101 rows are getting inserted out of 901.

Thanks.

To get rid of " GMT" in the fly, use @variables and SET.  Something like

LOAD ...
    ... ,
    ( cell, id, too, @issue, ... )
    SET issue = LEFT(@issue, 19)
    ...;