Lots of small SQL statements in SQL Server and measuring network time between DB and Client I am load testing an application and it appears from New Relic that each transaction executes a significant number SQL statements (approx 250). As we scale up, the service that makes these calls appears to degrade; however New Relic is a bit unclear as to why this is as it does not account for all the time (just the SQL execution time).

In Oracle I would be able to run a trace which would show the latency of the network round trips associated with execution of each SQL statement. Is there an equivalent set of SQL Profiler (or similar) metrics that will help me trace the time between the client service issuing the SQL request and it receiving the response?

It's been a while since my Oracle days but IIRC, that's at the Ora net layer so the equivalent in SQL Server will be ODBC or .NET provider layer. Profiler is a client tool that calls SQL Trace in the server. It can't capture client-side network stats so if you're trying to measure client/server network latency, it won't help you.

You can track ODBC clients statistics by enabling it from the data source administrator. See https://msdn.microsoft.com/en-us/library/ms131411.aspx for details.

For .NET provider statistics see https://msdn.microsoft.com/en-us/library/7h2ahss8(v=vs.110).aspx

Jes has a nice blog about enabling it from SSMS if you are testing specific queries, see https://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/

If you really want to get details, there are a bunch of other tools with varying levels of detail and abilities like netmon, tcping, tracert, etc... but they'll typically require manual correlation so I won't start with this.


sys.dm_exec_procedure_stats missing history? My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, however it only goes back as far as 10:17am September 1st.

My boss assures me that (although he likes to tinker with the server in his spare time) the server has been up since May 5th.

I'm pretty new to this, so explain it like I'm a particularly simple house pet, but is there are reason why my results table only goes back 1 month, instead of the full 6? I would assume that uptime is reset if the server is restarted, but I'm not sure why I wouldn't have any history that far back.

Does viewing the procedure by using right-click -> modify count as an execution?

SQL Query

SELECT qt.text AS 'SP Name',
qt.dbid as 'SP DB',
qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.last_execution_time 'Last Executed'
FROM sys.dm_exec_procedure_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
where qs.database_id = 5
ORDER BY qs.last_execution_time asc

My question relates more to the time frame of the results. I'm finding the information I need in the month of results I have but I'm worried there are 6 months of results that aren't being found found.

This returns May 5th:

select create_date from sys.databases where name = N'tempdb';

The following returns "2015-05-05 08:27:42.080":

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Finally, this one returns "2013-09-15 19:34:34.667":

SELECT top 1 [rs].[destination_database_name], [rs].[restore_date]
FROM msdb..restorehistory rs
where destination_database_name = '%ID%'
ORDER BY [rs].[restore_date] DESC;

From the documentation on sys.dm_exec_procedure_stats:

  Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

Therefore, it is likely that some time on September 1st, the server had an event that caused the procedure cache to be flushed, or at the very least, caused the plans for the procedures you care about to be pushed out of cache. We've already ruled out the most obvious culprit - a failover or service restart - but it could have been anything, really:

a unit test that executed a whole slew of procedures, filling up the cache
an sp_configure change, which - for certain options - has a side effect of clearing the procedure cache
a single query with a huge memory grant
a manual DBCC FREEPROCCACHE;
explicit recompiles or drop/re-create of procedures
...


Is Oracle Wallet available for use with Oracle XE 11gR1 Oracle XE 11gR1
RHEL 6.3

Is Oracle Wallet available with Oracle XE for production environments? or any environment?
Google search turns up nothing specifically declaring it not being available.

According to the Oracle Database Licensing Information 11g Release 2 document

Oracle Wallet
An Oracle Wallet is a PKCS#12 container used to store
authentication and encryption keys. The Oracle database secure
external password store feature stores passwords in an Oracle Wallet
for password-based authentication to the Oracle database. The Oracle
Wallet may also be used to store credentials for PKI authentication to
the Oracle Database, configuration of network encryption (SSL/TLS),
and Oracle Advanced Security transparent data encryption (TDE) master
encryption keys. Strong authentication services (Kerberos, PKI, and
RADIUS) and network encryption (native network encryption and SSL/TLS)
are no longer part of Oracle Advanced Security and are available in
all licensed editions of all supported releases of the Oracle
database.

That last bit about strong authentication services and network encryption being available in all licensed and supported releases of the Oracle database would suggest that it might be.  It just a matter how licensed and supported are defined.  XE is licensed, but it's support is via Oracle's free discussion forum rather than via the paid for My Oracle Support site


How to enforce a column value on duplicate values on postgresql using constraint? I have the following table:
mytable
---------
id Serial PK
action Integer
model VARCHAR
model_id integer
active BOOL

But due to my logic there cannot be duplicate values having the same values for model,model_id,action when active is true.
Therefore, I place a unique constraint:
ALTER TABLE mytable ADD CONSTRAINT constraint_name UNIQUE (model,model_id,action,active);

But that does not avoid me having the unique constraint only when active=true. Also, my table is pre-populated with duplicate values therefore I want to set active=false on dduplicate ones and keep only one record with value=true do you know any good idea on how I can do this beyond just writing an update statement before applying the constraint?

A partial index like suggested by @ypercube is likely the best solution. If you for some reason would like to keep it at the logical level you can generate a column:
If ACTIVE: concatenate the "partially unique" columns, I used a token separator to distinguish columns, result of 'a' || 'bc' should be different from 'ab' || 'c'
If NOT ACTIVE: use the primary key instead of the "unique" columns
I used xyz as the name of the generated column because I did not know better:-)
create table mytable
( id serial primary key
, action integer not null
, model text not null
, model_id integer not null
, active boolean not null
, xyz text GENERATED ALWAYS AS ( case when active
                                      then action::text
                                        || '#'
                                        || model
                                        || '#'
                                        || model_id::text
                                     else id::text
                                 end
                               ) stored
, unique (xyz)
);


row n was truncated; it contained more data than there were input columns: only some rows I am running the following script to load data from a .csv file on MySQL Workbench:

SET foreign_key_checks = 0;

LOAD DATA LOCAL INFILE "C:\\movieDB\\movie_metadata_edited.csv"
INTO TABLE IMDB.TITLE
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
IGNORE 1 LINES

(Title_id, Length, @dummy, Primary_title, @dummy, @dummy, @dummy, Language, Country, Content_rating, @dummy, @dummy)
;

and it loads just fine except for it gives the following warning, for n= 65, 239, 275, 280, 689, 817, 898, 924, 954, 1100, 1216, 1502, 1552, 1776, 1785, 1799, 1847, 1890, 1957, 2035, 2108, 2323, 2371, 2437, 2505, and 2606

  Row n was truncated; it contained more data than there were input
  columns.

the file I'm trying to load is movie_metadata.csv, and my table has columns
Title_id, Title_type, Primary_title, Content_rating, Start_year, Length, Language, Country, Fancenum_in_poster.

I found a solution for when every row comes back with this warning, but no solution was found for when it's only the occasional row.

How can I fix this and get rid of the warning? If I need to fix the file, how to I find each of those rows? The first row in my database doesn't seem to correspond with the first row in the file.

Try opening your file with a text editor, and going to the 66th line (header + 65).

You'll see the movie_title is:

  "The Chronicles of Narnia: The Lion, the Witch and the Wardrobe "

This title includes a comma, which (based on your criteria) means that the above is interpreted as movie_title ("The Chronicles of Narnia: The Lion) and num_voted_users (the Witch and the Wardrobe "). this will push each of the following columns over by one, leaving you with an extra column in this row.

And, of course, that's not a particularly useful num_voted_users value....

However, the solution is already there in the file (for this row at least - presumably for the others). This .csv file appears to be formatted so that values that include the column separator are enclosed by double quotes (").

Try running your script with the following modification:

FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'

If MySQL Workbench handles this like most systems I've worked with, it won't assume that every value is enclosed by double quotes; however, if a value starts with double quotes, it will ignore column separators until it gets to the closing double quote.

Tip: I downloaded your file, opened it in a text editor, and did the following to find lines with problems:

counted the number of commas in the header line (there were 11);
using a regular expression search, marked all lines with exactly 11 commas;
Inverted the marking, so only lines with more or less than 11 commas were marked;
Looked at the first marked line.

(FYI - I use TextPad as my editor; any good text editor should be able to do something like this, though the details may vary).


ORA-01033: ORACLE initialization or shutdown in progress using Oracle Express I am using Oracle Database 11g Express Edition.  I'm on Windows 7-64Bit

I get this error when trying to access the database:

ORA-01033: ORACLE initialization or shutdown in progress

I set up the express edition more than a year ago, and I've used it primarily for a single database instance that I access through SQL Developer.  I really do not do much as far as DBA administration on this, so I am not familiar with the points of failure.  But I know this will not fix itself by waiting longer.  I've tried rebooting and waiting several days.  I cannot log in using any userId I've created.

I've googled solutions and am running a console as a system administrator.  Here is what happens when I try to make repairs.

>sqlplus /nolog
SQL>connect / as sysdba
ERROR:
ORA-01031: insufficient privileges

Others that have had the same ORA-01033 do not seem to get this error.

I really don't even need the databases.  If there is a way to wipe my data clean and start again, I'd be happy.  But it is not obvious to me how to even do that.

Any suggestions would be appreciated.

I believe restarting the applicable windows service will bounce the database.

net stop oracleservicexe
net start oracleservicexe

Alternatively you can connect as sys and provide the password instead of using internal authentication, then bounce the database from there.

>sqlplus /nolog
SQL>connect sys as sysdba
Password:

You may also want to check the alert log for what, if anything, is keeping the database from completing startup or shutdown.


parent child records - cascade deletes or transactions? I'm about to create a parent and child table.  In planning how to do CRUD operations on this data, I was wondering what is better design when it comes to deleting data.

I'm using SQLite.  I know that with many databases, you can set up cascading deletes.  Assuming I set up the relationships properly to support this type of feature, my question is as follows:

Is it better to use cascading delete feature when deleting parent records, or could I stick to using transactions, and manually delete from the parent/child tables myself, with rollbacks when something fails?

The front end is a web application. Any suggestions / articles you can point me to would be great.

This depends on the needs of the data and is also a design preference. Having CASCADE ON DELETE can be a cleaner implementation and better describe the relationship of the data.

The research that this article presents shows that CASCADE ON DELETE actually has a slight performance decrease over a straight delete in SQLite. But unless you have a consistently high load, it shouldn't be an issue.

Database    Speedup    CPU decrease
MySQL         45%        25%
Oracle        28%        11%
PostgreSQL    56%        55%
SQL Server    17%        22%
SQLite        -11%      -75%

http://www.codesynthesis.com/~boris/blog/2012/04/12/explicit-sql-delete-vs-on-delete-cascade/


Why does SQL Server Mangement Studio not script Indexes by default? Recently I realized by default, scripting the database does not including indexes, and I have to change the option every time.

Could anyone please explain the reason? Is there any insight about this default behavior, or just because Microsoft likes to do so?

You don't have to change the option every time. If you change the selection in Tools > Options > Object Explorer > Scripting, it should persist.

As for why not scripting indexes is the default, I have no idea. Not a great question for your peers; if you really want the background, hit up someone on the SSMS team and buy them a beer - maybe they'll be able to share it. You could also consider submitting a suggestion on the SQL Server feedback forum (if one doesn't already exist), and they will possibly tell you why it's the default and/or why they won't be changing it.


How to avoid duplications using a function I am using this function in a bulk insert to avoid duplication of url paths.

CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4)
  RETURNS "int4"
AS $BODY$
DECLARE
  landing_name TEXT;
BEGIN
landing_name := _name;
LOOP
BEGIN
WITH sel AS (
  SELECT id
  FROM   univ.landings
  WHERE  name = landing_name
  )
, ins AS (
  INSERT INTO univ.landings (name)
  SELECT landing_name
  WHERE  NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
  )
SELECT id
FROM   sel NATURAL FULL OUTER JOIN ins
INTO   landing_id;

EXCEPTION WHEN UNIQUE_VIOLATION THEN
    RAISE NOTICE 'It actually happened!';
END;

EXIT WHEN landing_id IS NOT NULL;
END LOOP;
END$BODY$
LANGUAGE plpgsql;

A path looks like this:

%2faccessories%2fliners.html%26sa%3du%26ei%3df1n2voo3fjk1acowgnao%26ved%3d0cesq9qewbg%26usg%3dafqjcnhvnzccoijbs0zvswwjxexhyd-7xw

My table:

 id        | integer                     | not null default nextval('univ.seq_landings_id'::regclass) | plain
 name      | text                        | not null                                                   | extended
 created_at| timestamp without time zone | not null default now()                                     | plain
Indexes:
"landings_pkey" PRIMARY KEY, btree (id)
"uniq_index_landings_on_name" UNIQUE, btree (name)

I have an unique index on name, but the function is still duplicating. How can I make sure that there will be no duplications?

I have re-tested your function and it seems to work as advertised.

If, in fact a duplicate value was entered, you would get an exception that would roll back the transaction.
The function is designed to trap such an exception (UNIQUE_VIOLATION) and to try again, which would find the (now) existing row and return the existing id.

Your report of "duplications" doesn't seem possible. There must be a misunderstanding somewhere.

I only have minor improvements to offer:

Make that a UNIQUE CONSTRAINT, that's cleaner than a unique index, while doing the same for the case at hand. Since you already have a unique index, you can use the special form:

ALTER TABLE univ.landings ADD CONSTRAINT landings_name_key UNIQUE
USING INDEX index_name univ.uniq_index_landings_on_name;

You can simplify the function a bit. Your additional variable landing_name is just a waste:

CREATE OR REPLACE FUNCTION univ.gc_landing(_name text, OUT landing_id int)
  RETURNS int AS
$func$
BEGIN
LOOP
   BEGIN
   WITH sel AS (
     SELECT id
     FROM   univ.landings
     WHERE  name = _name
     )
   , ins AS (
     INSERT INTO univ.landings (name)
     SELECT _name
     WHERE  NOT EXISTS (SELECT 1 FROM sel)
     RETURNING id
     )
   SELECT id
   FROM   sel NATURAL FULL OUTER JOIN ins
   INTO   landing_id;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN
       RAISE NOTICE 'It actually happened!';
   END;

   EXIT WHEN landing_id IS NOT NULL;
END LOOP;
END
$func$  LANGUAGE plpgsql;

But note that this function is designed for individual inserts. For bulk operations like you mention, a different approach might be much more efficient: fold duplicates in the source before you bulk-insert into the target table.


Exclusively access a table and prevent other queries, even read queries I'm stuck at setting transaction isolation level. Here's my scenario that happens in the application:

Get unprocessed messages (using the IsProcessing flag)
Set their IsProcessing to true (in RAM) and update their IsProcessing status
Do the business
Set their IsProcessing to false (in RAM) and update their IsProcessing status

This works fine when runs sequentially. But when I run multiple instances of my application (concurrency), I see that some messages are processed twice or thrice. Here's what happens:

Instance A gets some unprocessed messages
While instance A is setting the IsProcessing to true in RAM, instance B gets some messages, and chances are that it fetches one or more of the messages which are already fetched by instance A

And this is what I've done in hope of preventing it:

Begin transaction (serializable)
Get unprocessed messages (using the IsProcessing flag)
Set their IsProcessing to true (in RAM) and update their IsProcessing status
Commit transaction
Do the business
Set their IsProcessing to false (in RAM)
Begin transaction (serializable)
Update their IsProcessing status
Commit transaction

I don't know why, but during steps 1 to 4, other instances can still perform read queries. This is not desired. I want to exclusively prevent anything, even read queries from being executed on messages table during step 1 to 4.

How can I do that? What am I missing in my design? The goal is to make sure that while a message is queued for processing, no other instance would process it again.

You will be able to avoid a lot of your race conditions by performing many of your steps in a single statement. By using a TOP() cluse will be able to set the flag on at most one row.  By using the OUTPUT cluse you can return this to the application automically.

I define a simple test table and populate it:

create table dbo.T(id int, IsProcessing bit default 0);

insert dbo.T(id) values (1),(2),(3),(4);

The output clause needs a table variable to receive the changed values:

declare @U table (id int);

A bit of debug code to make the "before" and "after" states obvious:

select 'Before' as B, * from dbo.T;

And the statement itself:

update top (1) dbo.T
set IsProcessing = 1
OUTPUT INSERTED.id
INTO @U(id)
where IsProcessing = 0;

And the results:

select 'After' as A, * from dbo.T;
select * from @U;

This is the output from the second execution of the above.

B       id  IsProcessing
Before  1   1
Before  2   0
Before  3   0
Before  4   0

A       id  IsProcessing
After   1   1
After   2   1
After   3   0
After   4   0

id
2

The row with id=2 had gone from IsProcessing=0 to IsProcessing=1 and that id is returned in the table variable.

With this trivial data the rows are processed in the sequence they were created.  In a more complex environment the optimizer may choose any row that matches the where clause.  If you have a requirement to process rows in, say, time sequence further qualification will be required.

I haven't thought it all the way through but I believe his will work at any isolation level with or without explicit transactions.

It is of course possible that there are no rows with IsProcessing=0 at the time the statement runs. In this case the table variable will have zero rows.

To fully isolate each transaction from the other you could try sp_getapplock. This will add overhead and reduce concurrency.  You have to be careful to release the app lock as soon as you can in both success and failure scenarios.


How to get dependencies order I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem is all the tables have foreign keys so I want to know the order in which I have to execute the tables.

Is there any way to know it?

Solution 1:
Script all of your foreign keys out and create the tables on the new server without foreign keys. Load the data, then rerun the scripts to create the foreign keys.
Solution 2:
Backup the database, the restore it to the new server.
Solution 3:
Run a query similar to this and figure out the dependencies for yourself.
Pick the one that sounds like the least work :)


SQL Server Query Store - What is considered an 'ad-hoc' query? I've been doing a deep dive into the SQL Server Query Store and I often see references to 'ad-hoc' queries. However, I haven't seen what the Query Store determines an ad-hoc query to be. I've seen places where it could be inferred to be a query without parameters or a query executed just one time. Does a formal definition exist for this? I don't mean in general. I mean as it relates to the Query Store.

As an example, this page shows an example of removing ad-hoc queries from the query store, but it appears the criteria it's using is an execution count of just one. This seems to be an odd definition of an ad-hoc query. BTW, if you go to the page, search for 'Delete ad-hoc queries'.

After a little bit of searching, I wasn't able to find a concrete and satisfying source from Microsoft's documentation to answer this question. There are many good third-party descriptions and definitions of adhoc/ad-hoc/ad hoc out there, but for the specificity of this question I think one close to the source is ideal.

Moving past generic (but still accurate) definitions like this SO post (thanks SqlWorldWide), if we look at what the documentation does say on the topic, it agrees with what you've mentioned about the definition being based off of the number of executions, I think to the point that we can take it as fact.

The article on optimizing for ad hoc workflows says,

  When this option is set to 1, the Database Engine stores a small
  compiled plan stub in the plan cache when a batch is compiled for the
  first time, instead of the full compiled plan. This helps to relieve
  memory pressure by not allowing the plan cache to become filled with
  compiled plans that are not reused. The compiled plan stub allows the
  Database Engine to recognize that this ad hoc batch has been compiled
  before but has only stored a compiled plan stub, so when this batch is
  invoked (compiled or executed) again, the Database Engine compiles the
  batch ... and adds
  the full compiled plan to the plan cache.

So it would appear the the ad hoc query server configuration option also uses the definition of a single execution as the definition of ad hoc. If the query continues to get executed and generates the same plan it would no longer be treated as such.

The article on Best Practices for Query Store also  aligns with this,

  Compare the number of distinct query_hash values with the total number
  of entries in sys.query_store_query. If the ratio is close to 1 your
  ad-hoc workload generates different queries.

This, of course, is for queries that are not already used as stored procedures, parameterized, etc. because they can be recognized and treated appropriately right off the bat.

So based on all this we can say that a query is treated as ad hoc if:

It is not parameterized
It is not programmatically stored in the database (stored proc, function, trigger, etc.)
The same query is only executed one time
OR The same query is executed multiple times but generates a different query plan for each subsequent execution.


Do a lot of error checks affect query performance and is it bad practice? If I have a stored procedure with a handful of error checks that come before the queries are executed and throw exceptions as necessary, could this affect performance? Is it bad practice to do this?

For example, say I had 10 logical case checks against the parameters of this stored procedure, would that be fine?...What if I had 100 check cases?

(The functional programmer in me wants to not leave any holes, but the relational developer in me feels like this could lead to a bad query plan being generated.)

Example of how I'm doing the error checking:

IF @Parameter1 IS NULL OR @Parameter2 IS NULL OR @Parameter3 IS NULL
BEGIN
    THROW 50000, 'Error Message 1', 1;
END
ELSE IF @Parameter1 = 'Value1' AND @Parameter2 <> @Parameter3
BEGIN
    THROW 50001, 'Error Message 2', 1;
END

I'm hard-pressed to see how an IF statement before the "meat" of the procedure that is just doing simple tests on the parameters would produce a bad plan.

Unless something really odd is going on, I wouldn't expect those checks to have a meaningful impact on performance unless the actual work the procedure is doing is very minimal.  Even a few hundred equality/ inequality checks should take way less than a millisecond which is almost certainly too small to be meaningful to you.

If there were 100 checks, that would make me suspect that something was off in the procedure and that it was trying to do too much/ take too many parameters and that it was in desperate need of refactoring into multiple smaller procedures.

The one caveat would be if this was a table-valued function (TVF) rather than a procedure.  I would absolutely avoid adding these sorts of checks if that would transform a single-statement TVF into a multi-statement TVF because that's going to prevent the optimizer from merging the query into the rest of the statement that it is being called from.  If you have a multi-statement TVF, that logic is potentially getting called many thousands of times in a single SQL statement in which case you might start to see some performance impact.  Even there, though, if your checks are only 1% of the overall runtime of the object, you probably don't have too much to optimize for.


Trigger to automatically calculate area when adding polygon in PostGIS I need help with writing a trigger to automatically update and insert into a column the area of a polygon being created using PostgreSQL/PostGIS and QGIS.

This is what I have but it's not updating when I add new features.

CREATE OR REPLACE FUNCTION calc_area()
RETURNS trigger AS
$BODY$
BEGIN
NEW.area_ha := ROUND((st_area(NEW.geom::geography)/10000)::numeric,2);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER area_calculate AFTER INSERT OR UPDATE ON public."Guyana_Coastal_Zone"
    FOR EACH ROW EXECUTE PROCEDURE calc_area();

This is not a very good idea, and there is no need to do this. Just create a functional index.

CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) );

I would create the index on the unrounded version.

CREATE INDEX ON table ( ST_Area(geom) );

Then a query like this:

SELECT * ON table WHERE ST_Area(geom) > x;

will use the index. Alternatively, you can create a MATERIALIZED VIEW. Generally, it's not a good idea to cache the result of a calculation on the table.

Shy of that, if you really want to store the computed column on the row your trigger needs to be BEFORE INSERT OR UPDATE. I really wish returning non-null in AFTER would generate a warning, but it's in the docs (emphasis added):

  The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.


Is there any better approach in designing this database? CUSTOMER:

CustomerID (PK)
Name
Email

PAYMENT:

PaymentID (PK)
Amount
Date

PRODUCT:

ProductID (PK)
Type
Name
Price

PURCHASE:

CustomerID (FK)
PaymentID (FK)
ProductID (FK)
Quantity

Or should I move CustomerID in PURCHASE to PAYMENT or else?

If it were me, I would make two changes:

1) Create a "PurchaseID" field on the "Purchase" table (which can double as an order number for your customers to reference).

2) Move "Quantity" to a new table called "Purchase_Line" for each line (item) on the order (purchase).  Otherwise, you're going to repeat the CustomerID and PaymentID for each ProductID included on every order.  This way your customers can order as many products as they want, but their order only appears in the "Purchase" table once.

Otherwise, I think your normalization looks very complete.


Concern with CHECK TABLE on large InnoDB 24x7 table, Will CHECK TABLE on large InnoDb table causing issue with blocking of online transactions? Assuming the table size is 5GB.

According to the MySQL BOL, CHECK TABLE blocks other threads on the table.

  When running CHECK TABLE on large InnoDB tables, other threads may be
  blocked during CHECK TABLE execution. To avoid timeouts, the semaphore
  wait threshold (600 seconds) is extended by 2 hours (7200 seconds) for
  CHECK TABLE operations. If InnoDB detects semaphore waits of 240
  seconds or more, it starts printing InnoDB monitor output to the error
  log. If a lock request extends beyond the semaphore wait threshold,
  InnoDB aborts the process. To avoid the possibility of a semaphore
  wait timeout entirely, run CHECK TABLE QUICK instead of CHECK TABLE.

Thanks!


MariaDB 10.1.16 on Ubuntu fails to start: WSREP Failed to recover position I'm using 10.1.16-MariaDB-1~xenial from the official MariaDB apt repository for 10.1 [stable], via the University of Texas mirror.

I had a perfectly functioning MariaDB Galera cluster setup on 3 Ubuntu 16.04 servers.

Then I upgraded them. Now I have nothing.

The upgrade to 10.1.16 failed, and quickly brought down the whole cluster. I don't have the output, but dpkg failed on setting up mariadb-server and mariadb-server-10.1.

I have backups, so I purged all traces of MariaDB/MySQL/Galera from my servers (including removing /var/lib/mysql/, /etc/mysql/, and /var/log/mysql/) and started over. However, now, with a clean install on each server, none of the standard system startup scripts work. I suspect this is why the upgrade process through apt failed, too.

I've tried each of the following on my first node:

galera_new_cluster
service mysql bootstrap
service mysql bootstrap --wsrep-new-cluster
service mysql bootstrap --wsrep-cluster-address="gcomm://"
service mysql start
service mysql start --wsrep-new-cluster
service mysql start --wsrep-cluster-address="gcomm://"
systemctl start mariadb
systemctl start mariadb --wsrep-new-cluster
systemctl start mariadb --wsrep-cluster-address="gcomm://"

Every single one gives me the same output:

Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

systemctl status mariadb.service:

● mariadb.service - MariaDB database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: failed (Result: exit-code) since Fri 2016-07-22 13:29:45 CDT; 42s ago
  Process: 10799 ExecStartPre=/bin/sh -c VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] &&   systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=1/FAILURE)
  Process: 10794 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 16865 (code=exited, status=0/SUCCESS)

Jul 22 13:29:41 sql2 systemd[1]: Starting MariaDB database server...
Jul 22 13:29:45 sql2 mysqld[10799]: WSREP: Failed to recover position: '2016-07-22 13:29:41 140110745778432 [Note] /usr/sbin/mysqld (mysqld 10.1.16-MariaDB-1~xenial) starting as process 11080 ...'
Jul 22 13:29:45 sql2 systemd[1]: mariadb.service: Control process exited, code=exited status=1
Jul 22 13:29:45 sql2 systemd[1]: Failed to start MariaDB database server.
Jul 22 13:29:45 sql2 systemd[1]: mariadb.service: Unit entered failed state.
Jul 22 13:29:45 sql2 systemd[1]: mariadb.service: Failed with result 'exit-code'.

The only way I can start my servers now is by manually executing:

sudo -u mysql mysqld --wsrep-cluster-address='gcomm://'

On the first node, and then:

sudo -u mysql mysqld --wsrep-cluster-address='gcomm://ip1,ip2,ip3'

On the other two nodes. That works, and I have a working cluster again. But now, systemd/systemctl have no idea the service is running. It seems like the systemd startup scripts can't use the wsrep-cluster-address setting in my configuration files at all. Specifying it to service or systemctl command line does not work either.

How am I supposed to start mariadb?

There was a bug in galera_recovery.sh script.
https://jira.mariadb.org/browse/MDEV-10396


How to inspect global temp table from outside the transaction it is created in To help debugging a batch of SQL (that is run inside a transaction), inside the transaction I dump some data into a global temporary table. The global temporary table is created inside the transaction (I have no choice about that for reasons I am not going into here).

I thought I might be able to select from the temp table from outside the transaction (i.e. under a different connection) by using "with(nolock)". However, the select is blocked from completing.

Is there any way to select from the temp table from outside the transaction?

File this under "just because you can, doesn't mean you should"

If you generate a bind token in the first session, and somehow publish it, you can join its transaction from another session.

Eg from spid 61:

if @@TRANCOUNT > 0 rollback
go
begin transaction

select *
into ##t
from sys.objects

declare @bind_token varchar(255);
exec sp_getbindtoken @bind_token output
declare @bind_token_bin as varbinary(128) = cast(@bind_token as varbinary(128))
set context_info @bind_token_bin

--rollback

then from another session

declare @bind_token varchar(255) = (
    select cast(context_info as varchar(255))
    from sys.dm_exec_sessions
    where session_id = 61
)

exec sp_bindsession @bind_token
go
select * from ##t


Combining two similar SQL queries I'm using the following SQL query twice with different values of limit. In my case, 40 and 100, though it doesn't really matter. My question is - can I combine these two queries into one query? It would probably be faster. sample is a PG array.

I don't know how much more information is needed, but if you need more details, please ask. I'm reluctant to add details about the schema unless necessary, since it will make the question much longer.

SELECT   SUM(motif)
FROM     (SELECT    ROW_NUMBER() OVER (PARTITION BY crossvalnum ORDER BY crossvalnum, pvalue DESC, margstat) AS r,
                    (seqindex IS NOT NULL)::INTEGER                                                        AS motif,
                    crossval.crossvalnum,
                    data.margstat,
                    data.pvalue
         FROM       data
                    INNER JOIN datasubgroup
                    ON         data.datasubgroup_id=datasubgroup.id
                    INNER JOIN crossval
                    ON         datasubgroup.crossval_id=crossval.id
         WHERE      data.seqindex                      =ANY(crossval.sample)
         OR         data.seqindex                  IS NULL
         )
         AS q
WHERE    q.r <= limit
GROUP BY crossvalnum;

The value for 40 is

sum
-----
  25
  22
  19
  16
  24
(5 rows)

The value for 100 is

sum
-----
  32
  28
  24
  23
  31
(5 rows)

Update: motif is defined an an indicator variable, (seqindex IS NOT NULL)::INTEGER. The SUM corresponds with the GROUP BY crossvalnum as an aggregate query. So the sum happens across each crossvalnum group. So there is a window function inside an aggregate query. I want the output to be two columns.

If I understand correctly, the query does a count in greatest-n-per-row type of query where n takes 2 values (40 and 100).

This can probably be solved better by rewriting the subquery. The easier to write might be to do a join of two queries:

SELECT
    a.motif AS cntA, b.motif AS cntB, a.crossvalnum
FROM
    (query with limit = 40) AS a
  JOIN
    (query with limit = 100) AS b
      ON b.crossvalnum = a.crossvalnum ;

Here is another way by moving the counts inside the subquery:

SELECT   MIN(motif) AS cntA,
         MAX(motif) AS cntB,
         crossvalnum
FROM     (SELECT    ROW_NUMBER() OVER
                        ( PARTITION BY crossvalnum
                          ORDER BY crossvalnum, pvalue DESC, margstat
                        ) AS r,
                    COUNT(*) OVER
                        ( PARTITION BY crossvalnum
                        ) AS c,
                    COUNT(seqindex) OVER
                        ( PARTITION BY crossvalnum
                          ORDER BY crossvalnum, pvalue DESC, margstat
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                        ) AS motif,
                    crossval.crossvalnum
         FROM       data
                    INNER JOIN datasubgroup
                    ON         data.datasubgroup_id=datasubgroup.id
                    INNER JOIN crossval
                    ON         datasubgroup.crossval_id=crossval.id
         WHERE      data.seqindex                      =ANY(crossval.sample)
         OR         data.seqindex                  IS NULL
         )
         AS q
WHERE    r IN (LEAST(c, @limitA), LEAST(c, @limitB))
GROUP BY crossvalnum ;


Use of log_bin_trust_function_creators in MySQL What is the use of log_bin_trust_function_creators?
Also, Is the value of the parameter dynamic?
Will it change if I restart my MySQL service?

This variable controls whether binary logging should trust the stored function creators for not to create stored functions that will cause unsafe events. Eg. having UUID functions.

This has been explained well in documentation:

  When you create a stored function, you must declare either that it is
  deterministic or that it does not modify data. Otherwise, it may be
  unsafe for data recovery or replication.

  By default, for a CREATE FUNCTION statement to be accepted, at least
  one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified
  explicitly. Otherwise an error occurs:

  ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
  or READS SQL DATA in its declaration and binary logging is enabled
  (you *might* want to use the less safe log_bin_trust_function_creators
  variable)

  To relax the preceding conditions on function creation (that you must
  have the SUPER privilege and that a function must be declared
  deterministic or to not modify data), set the global
  log_bin_trust_function_creators system variable to 1.

Reference: mysql-documentation (read this as it explains with example.)

About your second question, if it's dynamic and will it change if you restart server:

Yes it is a dynamic variable.
The variable will change upon restart, if you do not update the config to reflect the change.

Reference: mysql-documentation-again


The actual number of row of Lazy Spool is huge The SQL Server execution plan shows the Lazy spool has 7935322 actual number of rows, but its child operator only has 32 rows.

Nested Loop (Inner Join, 2 rows)   <--- Hash match(3967661 rows)
       ^
       |
       +--- Lazy Spool (7935322 rows)  <-- Merge Join (32 rows)

If lazy spool sourced from its child rows as a temp table, it should only have 32 rows at maximum. I don't understand why does it have 7935322 rows.

Lazy Spool properties

Actual Rebinds: 16
Actual Rewinds: 3967645
Number of Executions: 3967661

Nested Loop properties:

Actual Number of Rows: 2
Number of Executions: 16

Spool operator ( “Lazy” Spool in this case) will create a temporary table in the TempDB database, and store all data (32 rows in this case) returned from the Merge join.

Nested loop join executed 3967661 times (outer input) and did a join with table spool (holding 32 rows).  Out of 3967661 times 16 times the parameter of the join changed and had to reevaluate the inner result set (rebind) and 3967645 times inner result set was reusable. As I am not seeing the whole plan I believe 7935322 number came form the 16 reevaluating the inner data set.

In most cases creating an index will most likely get rid of the spool and make the query run faster.  Because you do not have to create the temporary table in tempdb.


Include scripts for Indexes on Alter Views After a Developer altered an Indexed View to add a comment, the clustered index that was created on it was dropped with no warning.

This caused a failure as there are a couple of procedures that have the WITH (NOEXPAND) hint.

I have SSMS set up currently so that right-clicking the VIEW and selecting SCRIPT VIEW as, CREATE TO includes the index definition.

Is there a way to script out indexes on VIEWS for ALTER to?

O.K. lets create a nice example for you and show you how to script the index of your indexed view.

first, lets create a temp table called T1 and add some meaningful data into it

lets create a procedure that generates random strings first:

USE TEMPDB
GO

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ''

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

now lets add some data into T1 and test some selects:

use tempdb
go

create table t1 (i int not null identity(1,1) primary key clustered,
                    the_name varchar (50) NOT NULL)

declare @i int
declare @randomString varchar(50)

select @i = 1

while @i  < 1008 begin

exec SpGenerateRandomString 50, @randomString output

insert into t1(the_name) values ( @randomString)

select @i = @i + 1

end

when running some selects from T1:

running selects after clearing the cache for this query plan and this database
collecting statistics io and time, in order to compare performance.

DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
                FROM master.dbo.sysdatabases
                WHERE name = 'TempDB');

print @intDBID
-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%order by the_name desc%';

DBCC FREEPROCCACHE (0x060002002065C706F0BE22630400000001000000000000000000000000000000000000000000000000000000);

set statistics io on
set statistics time on

select * from t1
  where ([i]% 2) = 0
order by the_name desc

This is the query plan generated:

> Now creating an indexed view:

this will be used later on to run the same select and compare the performance

--DROP VIEW VM_01
--GO

create view vm_01
with schemabinding
as

select
   i, the_name
   from DBO.t1
  where ([i]% 2) = 0

GO
CREATE UNIQUE CLUSTERED INDEX PK_VM_01 ON DBO.VM_01 (THE_NAME,I)
GO

Now testing the select and comparing:

DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
                FROM master.dbo.sysdatabases
                WHERE name = 'TempDB');

print @intDBID
-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%order by the_name desc%';

DBCC FREEPROCCACHE (0x060002002065C706F0BE22630400000001000000000000000000000000000000000000000000000000000000);

    set statistics io on
    set statistics time on
    select * from vm_01 WITH (NOEXPAND)
      where ([i]% 2) = 0
    order by the_name desc

set statistics io off
set statistics time off

the query plan generated:

as it was demonstrated the query using the indexed view performed much better, so (because we don't worry about the writes in this example) we want to keep the index.

Here a select to show you the index creation script for the clustered index created to the view VM_01:

SET NOCOUNT ON
SET DATEFORMAT DMY
SET DEADLOCK_PRIORITY NORMAL;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @TABLEID int
declare @index_or_table_name varchar(256)

select @TABLEID = OBJECT_ID('VM_01')
      , @index_or_table_name = 'vm_01'

BEGIN TRY

        SELECT

        i.object_id,
        i.index_id,
        index_name=i.name,

        CASE WHEN I.is_primary_key = 1 THEN
             ' ALTER TABLE ' +
             QUOTENAME(Schema_name(T.Schema_id))+'.'+ QUOTENAME(T.name) +
             ' ADD  CONSTRAINT ' + QUOTENAME(i.name) + ' PRIMARY KEY ' +
             I.type_desc +
             ' ( ' +  KeyColumns + ' )  '

        ELSE

        ' CREATE ' +
            CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +
            I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
            I.name  + ' ON '  +
            QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name)
            + ' ( ' + KeyColumns + ' )  ' +
            ISNULL(' INCLUDE ('+IncludedColumns+' ) ','')
            +  ISNULL(' WHERE  '+I.Filter_definition,'') --sql2005

         END -- case primary key or not

            + ' WITH ( ' +
            CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +
            'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +
            -- default value
            'SORT_IN_TEMPDB = OFF '  + ','  +
            CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +
            CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +

            CASE WHEN I.is_primary_key = 1 THEN

                    -- default value
                    ' ONLINE = OFF '  + ','

            ELSE

                    -- default value
                    ' ONLINE = OFF '  + ','  +

                    -- default value
                    ' DROP_EXISTING = ON '  + ','

            END +

           CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +
           CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +
           DS.name + ' ] '

           AS [CreateIndexScript]

        FROM sys.indexes I

        INNER JOIN (
                      SELECT  Object_id
                             ,Schema_id
                             ,NAME
                             FROM sys.tables

                      UNION ALL

                      SELECT  Object_id
                             ,Schema_id
                             ,NAME
                             FROM sys.views

                    ) T

                   ON T.Object_id = I.Object_id
         INNER JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid

         INNER JOIN (SELECT *
                 FROM (

                        SELECT IC2.object_id , IC2.index_id ,
                            STUFF((SELECT ' , ' + QUOTENAME(C.name) + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
                        FROM sys.index_columns IC1
                        JOIN sys.columns C
                            ON C.object_id = IC1.object_id
                            AND C.column_id = IC1.column_id
                            AND IC1.is_included_column = 0
                        WHERE IC1.object_id = IC2.object_id
                            AND IC1.index_id = IC2.index_id
                        GROUP BY IC1.object_id,C.name,index_id
                        ORDER BY MAX(IC1.key_ordinal)
                            FOR XML PATH('')), 1, 2, '') KeyColumns

                        FROM sys.index_columns IC2
                        --WHERE IC2.Object_id = object_id('xtbUApplication') --Comment for all tables
                        GROUP BY IC2.object_id ,IC2.index_id

                      ) radhe3
                ) radhe4

          ON I.object_id = radhe4.object_id AND I.Index_id = radhe4.index_id
         JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
         JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
         JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id

         LEFT JOIN (SELECT * FROM (
            SELECT IC2.object_id , IC2.index_id ,
                STUFF((SELECT ' , ' + QUOTENAME(C.name)
            FROM sys.index_columns IC1
            JOIN sys.columns C
               ON C.object_id = IC1.object_id
               AND C.column_id = IC1.column_id
               AND IC1.is_included_column = 1
            WHERE IC1.object_id = IC2.object_id
               AND IC1.index_id = IC2.index_id
            GROUP BY IC1.object_id,C.name,index_id
               FOR XML PATH('')), 1, 2, '') IncludedColumns
           FROM sys.index_columns IC2
           GROUP BY IC2.object_id ,IC2.index_id) tmp1
           WHERE IncludedColumns IS NOT NULL ) tmp2
        ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id

        ---------------------------------------------------------------------------------------------------
        -- when @TABLEID = 0 -> all tables
        -- when @TABLEID = -1 -> only the index named = @index_or_table_name
        -- when @TABLEID has the object id of a particular table -> only shows that table
        ---------------------------------------------------------------------------------------------------

        WHERE 1 = CASE WHEN @TABLEID = 0 THEN 1 ELSE
                     CASE WHEN @TABLEID = -1 AND UPPER(I.name) = @index_or_table_name THEN 1 ELSE
                          CASE WHEN @TABLEID = I.object_id THEN 1 ELSE 0
                          END
                     END
                  END

        ORDER BY i.name

END TRY
BEGIN CATCH

        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13)

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH;

when you run the above script you get:

and a copy and paste shows us the script:

CREATE  UNIQUE CLUSTERED INDEX PK_VM_01
ON [dbo].[vm_01] (  [the_name] ASC  , [i] ASC  )
WITH (  PAD_INDEX = OFF ,
FILLFACTOR = 100  ,
SORT_IN_TEMPDB = OFF ,
IGNORE_DUP_KEY = OFF ,
STATISTICS_NORECOMPUTE = OFF ,
ONLINE = OFF ,
DROP_EXISTING = ON ,
ALLOW_ROW_LOCKS = ON ,
ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ]

  there are still some bugs to fix, like formatting, and the
  drop_existing=on,  but hopefully this will help.


Create Temp Table From Stored Procedure Result Is it possible to create a temp table from a stored procedure result set without having to specify column datatypes? If not, is it possible to create a physical table at least from the SP? The reason I am asking is because I have an SP that returns too many columns for me to declare in a create table command.

Is there something like

create table  from

where columns do not need to specified and sql automaticly determines column attributes??

I am running SQL server 2008

See following  question from stack overflow. According to this question you can use

CREATE PROCEDURE getDatabaseList
AS
BEGIN
    SELECT * FROM sys.databases
END

 SELECT * INTO #MyTempTable FROM
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getDatabaseList')

-- create temp table as usual, select , update or drop table


How to convert a single MySql table to utf8mb4 (from latin1) MySql version 5.6.39

i have one table that now needs to support chinese characters. the other tables in the database do not need utf8mb4 encoding.

what have i done thus far:
1) backed up database!
2) alter table <blah> default character set utf8mb4 collate utf8mb4_unicode_ci;
3) alter table <blah> convert to character set utf8mb4 collate utf8mb4_unicode_ci;
4) (for all columns except numeric) alter table <blah> change col1 col1 varchar(xx) character set utf8mb4 collate utf8mb4_unicode_ci;

what i have not done thus far:
1) alter database <blahDB> character set utf8mb4 collate utf8mb4_unicode_ci;

i've seen several questions/answers here...notibly:
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5  Is it necessary to alter the database when all i want is for one table within it to be utf8mb4?

Right now, when i insert a "special" (unicode) char (0x2015) in the middle of a string into the table, it is stored as '?'. Help!!

Going by what you have to do within a PHP-Webserver environment to use utf8 / utf8mb4, you have up to 5 or more construction places. And you covered 2 of them

collation of table and column(s)
overwork existing string-data in said columns

leaves:

make sure your application / tool uses the correct collation as well
make sure you communicate with the database using the correct collation
set the frontend of your application / tool to handle said collation

As JAVA is well prepared I guess its the database connection ...


Delete column on MariaDB slave I have a MariaDB 10.1 master/slave using InnoDB setup running on CentOS 7 which is mostly working as expected. However, somehow the slave has ended up with an extra column in a few tables. This column was deleted on the Master as part of the data import process but that change has not happened on the slave. The column is not required at all so I'd like to delete it.

So my question is (hopefully) simple: Can I safely delete the column directly on the slave without breaking replication?

Many thanks.

yes, you can safely delete the extra column if you are sure it's not in the master.

But the bigger question is why didn't the change carry over to the slave, Any errors when you do a "show slave status" is the "seconds behind master" 0?

You might as well refresh the slave with a newer backup from the master to be sure everything is consistent.


Import dump file schema not found I want to import a dump file but I have this error:

Import: Release 11.2.0.4.0 - Production on Mar. Nov. 28 10:12:56 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connect : Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-39002: invalid operation
ORA-39165: Schema TEST_2 was not found.

My command line:

impdp TEST/TEST@RECT0E DIRECTORY=DUMP_DIR DUMPFILE=Fichier.20171123_151835.dpdmp LOGFILE=TEST_2_27112017.log FULL=N EXCLUDE=STATISTICS REMAP_TABLESPACE=TEST_DATA:TEST_DATA_TEST REMAP_TABLESPACE=TEST_INDEX:TEST_INDEX_TEST REMAP_SCHEMA=TESTPR:TEST_2 SCHEMAS=TEST_2

I confirm my schema exist:

SELECT username FROM all_users ORDER BY username;

Result:

USERNAME
------------------------------
TEST
TEST_2
TEST_3

You are importing TESTPR SCHEMA so, change the value of SCHEMAS parameter to TESTPR as shown below. You have given TEST_2 to the SCHEMAS which is not included in the dump file thus you have hit the error ORA-39165.

...REMAP_SCHEMA=TESTPR:TEST_2 SCHEMAS=TESTPR


Select all overlapping ranges from starting range My question is similar to this one with (I think) significant enough difference. I have a base range and I want to find all other ranges in a table that conflict with it and each other. or rather the items that form the ranges, but it doesn't really make a difference.

The line with a asterisk is the starting range. The ranges 1,2 and 3 are the ones that are supposed to expand it. The result range should be the X.

1 |
3 |    ===1====            ====
5 | ==2==    ====*====           ====
6 |             ====3====     =====
--+-------------------------------------
  | |<--------X-------->|

I have written this:

WITH   cte
AS (
    SELECT DATA1.ID, DATA1.STARTDATE, DATA1.ENDDATE
    FROM DATA1
    WHERE
    DATA1.ID = @PARID AND
    DATA1.STARTDATE > @STARTDATE AND
    DATA1.ENDDATE < @ENDDATE

    UNION ALL

    SELECT DATA1.ID, DATA1.STARTDATE, DATA1.ENDDATE
    FROM cte
    INNER JOIN DATA1 ON (DATA1.ID = cte.ID)
    WHERE
    DATA1.ID = @PARID AND
    (cte.STARTDATE < DATA1.ENDDATE AND cte.ENDDATE > DATA1.ENDDATE)
)
SELECT *
FROM cte

But after some fiddling realized this can't really work, since the 2nd SELECT doesn't know what STARTDATE and ENDDATE it should use from the cte. And I can't use subqueries in recursive SQL.

We have previously calculated this in client in .NET via set of recursive functions, but at O(N^2) it was stupidly slow. The intent here is to move the calculations to the server as well as optimize the calculation.

Is what I'm trying to do here at all viable?

SQL Fiddle. I'm having some trouble getting the query to run as it did on my machine (where I used less simple data structure), but at least I added example data. I will keep trying to get it to produce the same result I got on my machine.

With input range from 2018-08-24 12:00:00 to 2018-08-31 12:00:00, the correct output should be IDs 2, 3, 4, 6.

A straightforward recursive solution splits the task in two parts:

Find the earliest start date by following the chain of overlapping ranges
Find the latest end date by following the chain of overlapping ranges

Details follow:

1. Follow the chain of overlaps leading to the earliest start date

Find the starting range
Find the overlapping range with the latest earlier start date than current
Go to step 2

Example code:

DECLARE @STARTDATE DATETIME = '2018-08-24 12:00:00';
DECLARE @ENDDATE DATETIME = '2018-08-31 12:00:00';

WITH MinStart AS
(
    -- Starting range
    SELECT TOP (1)
        D.ID,
        D.STARTDATE
    FROM dbo.DATA1 AS D
    WHERE
        D.STARTDATE >= @STARTDATE
        AND D.ENDDATE <= @ENDDATE
    ORDER BY
        D.ID ASC

    UNION ALL

    SELECT
        P1.ID,
        P1.STARTDATE
    FROM
    (
        -- Overlapping ranges with an earlier start date
        -- Numbered starting with the highest start date
        -- Tie-break dates using ID
        SELECT
            RN = ROW_NUMBER() OVER (
                ORDER BY D.STARTDATE DESC, D.ID ASC),
            D.ID,
            D.STARTDATE
        FROM MinStart AS R
        JOIN dbo.DATA1 AS D
            ON D.ENDDATE >= R.STARTDATE
            AND D.STARTDATE < R.STARTDATE
    ) AS P1
    WHERE
        -- Highest start date only
        P1.RN = 1
)
SELECT
    MS.ID,
    MS.STARTDATE
FROM MinStart AS MS
OPTION (MAXRECURSION 0);

2. Follow the chain of overlaps leading to the latest end date

Find the starting range
Find the overlapping range with the earliest later end date than current
Go to step 2

Example code:

DECLARE @STARTDATE DATETIME = '2018-08-24 12:00:00';
DECLARE @ENDDATE DATETIME = '2018-08-31 12:00:00';

WITH MaxEnd AS
(
    -- Starting range
    SELECT TOP (1)
        D.ID,
        D.ENDDATE
    FROM dbo.DATA1 AS D
    WHERE
        D.STARTDATE >= @STARTDATE
        AND D.ENDDATE <= @ENDDATE
    ORDER BY
        D.ID ASC

    UNION ALL

    SELECT
        P1.ID,
        P1.ENDDATE
    FROM
    (
        -- Overlapping ranges with a later end date
        -- Numbered starting with the lowest end date
        -- Tie-break dates using ID
        SELECT
            RN = ROW_NUMBER() OVER (
                ORDER BY D.ENDDATE ASC, D.ID ASC),
            D.ID,
            D.ENDDATE
        FROM MaxEnd AS R
        JOIN dbo.DATA1 AS D
            ON D.ENDDATE > R.ENDDATE
            AND D.STARTDATE < R.ENDDATE
    ) AS P1
    WHERE
        -- Lowest end date only
        P1.RN = 1
)
SELECT
    ME.ID,
    ME.ENDDATE
FROM MaxEnd AS ME
OPTION (MAXRECURSION 0);

With suitable indexing, both queries use an execution plan like:

The results given the sample data provided are:

╔════╦═════════════════════════╗
║ ID ║        STARTDATE        ║
╠════╬═════════════════════════╣
║  3 ║ 2018-08-24 12:00:00.000 ║
║  6 ║ 2018-08-23 12:00:00.000 ║
║  2 ║ 2018-08-22 12:00:00.000 ║
╚════╩═════════════════════════╝
╔════╦═════════════════════════╗
║ ID ║         ENDDATE         ║
╠════╬═════════════════════════╣
║  3 ║ 2018-08-29 12:00:00.000 ║
║  6 ║ 2018-08-30 12:00:00.000 ║
║  4 ║ 2018-09-02 12:00:00.000 ║
╚════╩═════════════════════════╝

So IDs 2, 3, 4, and 6 were used. The final range is 2018-08-22 12:00:00.000 (lowest start date found) to 2018-09-02 12:00:00.000 (highest end date found).

Note: The code above includes a tie-break on dates (using the ID column) due to duplicates in the sample data. This may, or may not, be required to solve the real problem.

The indexes used were:

CREATE TABLE dbo.DATA1
(
  ID integer PRIMARY KEY,
  STARTDATE datetime NOT NULL,
  ENDDATE datetime NOT NULL,
);

CREATE UNIQUE INDEX i1 ON dbo.DATA1 (STARTDATE DESC, ID ASC) INCLUDE (ENDDATE);
CREATE UNIQUE INDEX i2 ON dbo.DATA1 (ENDDATE ASC, ID DESC) INCLUDE (STARTDATE);

Demo: db<>fiddle

Note that truly optimal indexing for these types of queries can be difficult. Even so, the approach above often performs well for many practical tasks in this area.

Until proper support for interval queries and predicates is added to SQL Server, higher performance solutions require significant extra work. See:

Interval Queries in SQL Server Part 4 by Dejan Sarka
Interval Queries in SQL Server by Itzik Ben-Gan


Index strategy for TEXT / NTEXT column to filter out NULL / NOT NULL rows There is a table with TEXT or NTEXT data type column and I need to improve the performance of queries with WHERE clause filtering out NULL or NOT NULL rows in this column.
Unfortunately, I cannot change the data type to NVARCHAR(MAX) nor add a computed column to the table.
What possibilities do I have, please?

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

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

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


Merging two Access DB's Our workflow requires two users to work on the same Access database. The problem is that users are geographically separated and can't work on same .accdb file simultaneously.

So, the only way i see to solve this problem is to periodically merge each user's DB's into one. If there is another way, please share you thoughts.

I've googled for how to do this in Access, but haven't found easy solution. I mean, this operation seems to me quite natural and i've been expecting that Access can do merging "out of the box". But what i found are advices to write special queries for each table being merged.

I'm fine with this, since my tables are simple, but i wanted to know if there's better way and how such problem should be properly solved.

First things first: MS Access was not designed for multi-user access. Every version of Access I've used had a disturbing habit of corrupting tables at a vastly increased frequency if there were >1 users using it.

If the two users are connected to the Internet all the time, I'd recommend shifting your table storage to SQL Server and having the users connect to that (use a VPN or some other form of security! If they're on a company LAN it's even better, you shouldn't need a VPN then). It's a fairly straightforward process to convert to SQL Server.

The users will still use the Access front end, but instead of having the tables stored inside the .accdb file and having to merge them, the Access tables are converted to linked tables to the SQL Server tables.

This is possibly a bit more up-front work, but it'll save you hassle down the road (how often do you need to merge? who's going to do the merging?). Also, if the application ever gets more widely used, you can easily build another front end (in C#, Java, ASP.NET, whatever) and connect it to your SQL Server back end.


MariaDB slave replication server from many sources? can I have one server, which is slave for like 500 masters? All the masters have different content, but they inserting is like once per minute, so I think 8 core slave should be sufficient for it.
And also can I have one master database on this slave server? I don't have to, but it would reduce our servers costs.
Thanks for answering!

I don't know about "500", but there is "multi-source" replication in recent versions of MySQL, plus MariaDB.

So that is 500 inserts per minute on the Slave?  8/second is no problem.  Even 1 core would not be taxed.  Even if all inserts went into the same table.

Even if multi-source did not let you have 500, you could put, say, 25 instances of MySQL on a single server, each receiving data from 20 Masters.


Change MySQL backup script to use on PostgreSQL On a few MySQL databases I have I backup them up ny first;
mysql_config_editor set -h localhost -G dbbkup -P 3306 -u root -p

Then running a wee script;

#!/bin/bash

BACKUPDIR="/home/deploy/backup-db"
BKUPSSH="deploy@backup.com"
BKUPSERVDIR="/home/deploy/backups/databases"

# Remove files older than 30 days
find $BACKUPDIR/ -mtime +31 -exec rm {} \;

# DO NOT BACKUP these databases
IGNOREDB="
information_schema
mysql
test"

#* MySQL binaries *#
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)
GZIP=$(which gzip)

# assuming that BACKUPDIR exists
if [ ! -d $BACKUPDIR ]; then
  mkdir -p $BACKUPDIR
else
  :
fi

# get all database listing
DBS="$(mysql --login-path=dbbkup -Bse 'show databases')"

# Set date and time for the file
NOW=$(date +"%Y-%m-%d_%H.%M"); # year-month-day_hour.minute format

# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.sql.gz";
                echo "BACKING UP $db";
        $MYSQLDUMP --login-path=dbbkup --add-drop-database --opt --lock-all-tables --set-gtid-purged=OFF $db | $GZIP > $FILE
        fi
done

# change permissions on files
chmod -R 755 $BACKUPDIR

# rsync backup to 'Larry' the backup server and append the log file
rsync -azv $BACKUPDIR -e ssh $BKUPSSH:$BKUPSERVDIR >> /home/deploy/db_rsync.log 2>&1
RESULT="$?"

# check result of rsync
if [ "$RESULT" != "0" ]; then
    echo -e "rsync exit Code:" $RESULT "\nFailed to rsync databases" >> /home/deploy/db_rsync.log 2>&1
else
    echo "succesfully rsynced databases" >> /home/deploy/db_rsync.log 2>&1
fi

As I'm not a DBA to trade, does anyone know how I can achieve the same by slightly tweaking the script to use for PostgreSQL?

have a look at pg_dump it's very similar to the dump utility by MySQL
See: pg_dump documentation

I think most of the change is just changing the flags to match the new utility and replacing the call for mysql with a similar call to psql -l to get the db list (to filter out dbs you want to ignore)


How to write a trigger to add some information before update? I want this trigger to insert some information a table named into orders_log (which has six columns orderNumber, orderdate, customerNumber, status  (before update), status (after update), action = ‘update’) every time, after an UPDATE happens into a table called orders.

I have attempted the trigger below, please let me know if this correct:

DELIMITER $$
CREATE trigger after_orders_table_update
after update
on orders
FOR EACH ROW
 BEGIN
INSERT INTO orders_log table
SET
orderNumber = old.orderNumber,
orderdate = old.orderdate,
customerNumber = new.customerNumber,
status(before update)=old.status,
status(after update)=new.status,
action = 'update';
END$$

The fieldnames, which contains spaces and parenthesis, must be enclosed by backticks.

In current state they are treated as UDFs, and you must obtain the "undefined function" error (or syntax error).

The trigger contains only one statement, so you do not need in BEGIN-END block and DELIMITER change.

CREATE TRIGGER after_orders_table_update
AFTER UPDATE
ON orders
FOR EACH ROW
INSERT INTO orders_log table
SET orderNumber             = old.orderNumber,
    orderdate               = old.orderdate,
    customerNumber          = new.customerNumber,
    `status(before update)` = old.status,
    `status(after update)`  = new.status,
    action                  = 'update';


SQL Server : speed up this query I have a rather strange situation. There is a table ProductStock which has 2 triggers: one for afterInsert and one for afterDelete
both do an insert with the changed value in STOCKDEBUGTRIGGERED

afterDelete:

INSERT INTO STOCKDEBUGTRIGGERED
       (ProductID,Amount,StockOld,StockNew)
 select deleted.ProductID, count(deleted.ProductStockID),
 (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = deleted.productid) + count(deleted.ProductStockID)
 , (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = deleted.productid)
 from deleted
 group by deleted.ProductID

afterInsert

INSERT INTO STOCKDEBUGTRIGGERED
       (ProductID,Amount,StockOld,StockNew)
 select inserted.ProductID, count(inserted.ProductStockID),
 (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = inserted.productid) - count(inserted.ProductStockID)
 , (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = inserted.productid)
 from inserted
 group by inserted.ProductID

But this is the query which troubles me:

SELECT TOP 250
    STOCKDEBUGTRIGGERED.ProductID,
    productcode,
    'http://urltomyimages.com/' + ProductPictureName as img,
    Amount,
    STOCKDEBUGTRIGGERED.StockOld,
    STOCKDEBUGTRIGGERED.StockNew,
    ChangeDate,
    (select top 1 STOCKDEBUG.StockDebugWho from STOCKDEBUG
            where STOCKDEBUG.ProductID = STOCKDEBUGTRIGGERED.ProductID
            and STOCKDEBUG.StockOld = STOCKDEBUGTRIGGERED.StockOld
            and STOCKDEBUG.StockNew = STOCKDEBUGTRIGGERED.StockNew
            ) as who
    FROM STOCKDEBUGTRIGGERED
    inner join products on STOCKDEBUGTRIGGERED.productid = products.productid
    order by ChangeDate desc

Please do not advice temporal tables, because this does work and is running on sql 2008. It only has a high load because this gets executed by a monitoring tool every 5 minutes.

Here are the client statistics:

and here is the query plan:

What troubles me is the 44% sort with the yellow exclamation mark:

So is there a thing I missed? I made a view for the query and threw in three WITH(NOLOCK) statements. But that did not make it run faster. No index suggestion.

edit have read https://stackoverflow.com/questions/29001721/is-it-possible-to-allocate-memory-to-a-query-in-ms-sql-server because I wanted to see if there was a way to allocate 5mb memory for this query to skip the use of the tempdb. And have read https://www.mssqltips.com/sqlservertip/4132/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics/ but that is not the case for me I think because I limit it to 250 rows.

edit2 if I remove the subquery:

SELECT TOP 250
    STOCKDEBUGTRIGGERED.ProductID,
    productcode,
    'http://ourimages.com/' + ProductPictureName as img,
    Amount,
    STOCKDEBUGTRIGGERED.StockOld,
    STOCKDEBUGTRIGGERED.StockNew,
    ChangeDate
    FROM STOCKDEBUGTRIGGERED
    inner join products on STOCKDEBUGTRIGGERED.productid = products.productid
    order by ChangeDate desc

the sort increases to 89% and there is still a 15mb memory load because of the tempdb

edit3
as reply on @T.H. his answer:

execution time is now less than 1% or 2% of original!
Why did neither the perf advisor from azure or the ssms suggest these indexes?

edit4 To prove that @T.H. is right. You can see exactly when I created his two suggested indexes. It had a spike every x time because of that query up to 20% dtu. All spikes are gone and it is nice 1% avg dtu.

I believe the basic issue is that the query is doing multiple heavy table scans due to no index support.

Try adding the following indexes:

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUGTRIGGERED (ChangeDate)

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUG(ProductID, StockOld, StockNew)

There may be further tweaks, so please post the stats and execution plan with these indexes added.


Retrieve job Details in SQL Server 2005 How do I retrieve information about a SQL Server Agent job scheduled against databases on SQL server 2005?

Jobs are DB agnostic: the steps that comprise a job may have a database context

sysjobsteps has a database_name column

  Name of the database in which command is executed if subsystem is TSQL.

You can join this back to sysjobs and the other related tables to get what you need.


Disk space full but logical space available in database We have a fairly big MS SQL 2008R2 database that resides on a SSD drive. The drive itself only has ~110Gb of space, and the database files are the only files on the drive.

The database is in "Simple" recovery mode, and only has two files, .MDF and .LDF.

The disk is now nearly full: the MDF is currently 109Gb in size. However, SSMS tells me there's nearly 18Gb of "Space Available" (in the 'General' properties page), and if I go through the motions of Shrinking the file it also tells me there's 18Gb of space free. SSMS also tells me the database size is around 132Gb, which surprises me - that wouldn't fit on the drive!

From what I've read, shrink is a really bad idea. However, I'm starting to see replication errors (could not allocate space for object). We've previously tried shrinking the database, but within a few hours the file was back to it's original size.

How should we proceed - given there's apparently 18Gb of free space, should SQL automatically use that free space? Or is it as simple as: we really need more disk space?

Space is available inside the database because data has been moved around. Perhaps you have very high levels of page splits, or have recently deleted a large portion of data that had previously caused the data file to grow.

SQL Server does not shrink database files automatically when you've freed up space within them, because the logical assumption is that if you've used that space once, you'll use it again. Autogrow can be an expensive event and unnecessary if you've only freed up space temporarily (what were you able to do with all that free space in the meantime?). For the same reasons, you shouldn't try to temporarily reclaim space, either. Just let SQL Server use the 18 GB of available space as you add more data. If you think you will need more than 18 GB of additional space going forward (in which case, you will need to add file(s) on other disks, or move to a bigger disk).

sp_spaceused (and in turn the UI dialog you're looking at) may return more space than is possible because of synchronization issues in the metadata about your tables/indexes/files. In order to make sure it reflects accurate space, run this:

DBCC UPDATEUSAGE(0);

I have also seen scenarios where rebuilding indexes was required in order to rectify the counts/space, but I haven't seen that specific case since SQL Server 2000.

(I suspect this isn't a simple case where your database already spans multiple disks, or you surely would have mentioned that in the question.)

That all said, the fact that when you shrink the data file expands almost immediately leads me to believe that you are actually using the space, but must also be performing big deletes or updates that are freeing it up (which is when you see 18 GB free). Unfortunately it's impossible for us to see exactly why the data file is expanding and then clearing itself out - perhaps you have transactions where you are truncating / re-populating large tables, performing massive archive operations, etc.


Using ASPState database in the AG cluster with multiple teams using the same ASPState We are migrating a number of the 2008 Windows clusters to the 2016 servers and consolidating databases in the same AlwaysOn cluster. Our dev teams are currently using ASPState databases specific to their application. As a rule it's only one ASPState database per server but we need it to be able to accommodate multiple Web applications.

Questions

Is it possible to use one ASPState with multiple Web applications? If it is, then how?
Should this ASPState database be a part of the AG group? If it's part of the one AG than other AG's won't be able use it.

As a rule it's only one ASPState database per server but we need it to be able to accommodate multiple Web applications.

Your company just dictated your answers and infrastructure, sadly.

  Is it possible to use one ASPState with multiple Web applications? If it is, then how?

Apparently so:

Stack Overflow Answer
Maybe it is or isn't a good idea?

  Should this ASPState database be a part of the AG group?

If you're going to use AGs then it doesn't have to take part in it but it'll need to be loaded and properly setup on all replicas. If you put it in an AG, then you'll only have a single AG for all applications which may or may not be a good thing, depending on the application requirements.

Since the ASP state database is really just about throw away session state data, if there is a failover it won't make a real difference.

  If it's part of the one AG than other AG's won't be able use it.

That's true. If it's not in an AG but loaded on each replica by itself not in an AG that should work as I reference above.


Error in SSMS when attempting to restore a database We have over a dozen SQL servers, one of which hosts a couple of hundred sage databases, when i right-click on the Databases node in SSMS there is a long delay then i get the following error (and the restore dialogue box doesn’t load):

I presume SSMS is loading backup history from msdb or something in the background and running out of space - does this imply i have excessive backup history in my system databases? If so, what is the best way to maintain it?

I have maintenance plans cleaning up the trn and .bak files on disk but i dont think i maintain any of the logs in the system databases.

From your reply to my question, then it does appear to be too much backup history. For a one off cleardown, something along these lines will do it:

USE msdb
GO
DECLARE @days DATETIME
SET @days = DATEADD(day,-30,current_timestamp)
EXEC sp_delete_backuphistory @days
GO

That example gets rid of everything over 30 days old, so you may need to change the -30 for a value appropriate for your needs. But remember that you'll then have to build up any restores for backups over 30 days old from hand (may or may not be a problem for you, but thought I'd better mention it).

Going forward you can schedule that to run via a scheduled job  to keep you small.


How to organize binary tree in database? Here is the situation, I need to organize some users like an binary tree to explain organization workflow, but organization can change.

Here is how its look like:

         1
      /     \
     2       3
    / \     / \
   4   5   6   7

Its sounds easy:

Lets imagine this is the user table:

id | user  | position | who_contracted
1  | ana   | 1        | null
2  | bob   | 2        | 1
3  | joe   | 3        | 1
4  | Nik   | 6        | 3
5  | Ahn   | 4        | 1
6  | Rob   | 5        | 2
7  | Chloe | 7        | 1

Ana contracted 4 people in organization, IDs [2,3,5,7]
They are organized as they come to business.

But, when Ana was inviting they, Joe[3] invited Awn[5] to business. Its seat goes to be seat [6].

When Ana[1] invite 1 more, the next opened seat is 4, because each invited people get first seat from left to right down the user who contracted him

It makes easy to build an strong sales team.

Now, I need to know how to find next seat to user in position 3.

I know how to do it with math, but do not know how to organize correctly the database.

For sample, if User id 3 invite one more, will get the seat 12, but if user id 6, invite first, seat will be 12 also.

I need to calculate just in time when insert into table.

Any ideas??

ps. One more thing, I'm trying a way, that if I delete user 3, the users down come to up automatically, user on seat 6 will up to seat 3, because it is on low position compared with next user position, that is 7.

I think this question is principally, "how do you incentivize salesmen in a pyramid scheme?" That's cute, so yeah let's do it. Let's assume

We have 7 people in our scheme.
We allocate bonuses based on the tier of pyramid.

You don't have to place them in a visual pyramid to figure this out..

CREATE TABLE my_pyramid_scheme(empid, name, is_active)
AS
  SELECT id::int, (id+64)::int::"char", true
  FROM generate_series(1,7) AS gs(id);

Now you've got

 empid | name | is_active
-------+------+-----------
     1 | A    | t
     2 | B    | t
     3 | C    | t
     4 | D    | t
     5 | E    | t
     6 | F    | t
     7 | G    | t

So first we need to know what tier they're on... For that we use 2n-1. For example, if your number is 1.

2n-1 = 1
2n = 2
n = 1 -- you're on tier one

Now if your number is 6

2n-1 = 6
2n = 7
n = 3.5 -- trunc(3.5) you're on tier three.

So after you know what tier they're on. You need to find how much of a bonus they get.. That's a function of the tier they're on, and the maximum amount of tiers, to find that take the largest number and run it through the same process. With those two things, you typically have some geometric function that determines "incentive."

And, now you can run illicit drug cartel or compete with Herbalife.

Disabling members

If in the above you ever set is_active = false run these super fast operations on the row_number..

SELECT *, row_number() OVER (ORDER BY empid) AS newid
FROM my_pyramid_scheme;


MySql Switching Masters During Failover with log-slave-updates I am still pretty newbie to mysql. This question is regarding promoting slave with option --log-slave-updates to become a master.

The typical schema of promoting slave to master was described in 15.3.6 Switching Masters During Failover. Here you can find very strict steps how to do it :

  Run your slaves with the --log-bin option and without --log-slave-updates. The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master.

Let's say I have the schema like the following (the image was taken from 15.3.5. Improving Replication Performance).

Where Master 2 is actually a slave with --log-slave-updates and bin-log enabled. I need to enable --log-slave-update in order to save bin-log from the Master 1 to bin-log of Master 2 and then transfer it to all slave. In case of failover I need promote Master 2 to the role of Master 1. But there is a contradiction to the standard MySql failover schema I have --log-slave-updates enabled which can cause a problem.

In your opinion. What the action I should take in order to do this?

The warning about --log-slave-updates will only apply if you had multiple intermediary 'Master/Slave' servers.

The warning is this (my emphasis):

  Then it will write updates that it receives from Master to its own binary log. When Slave 2 changes from Master to Slave 1 as its master, it may receive updates from Slave 1 that it has already received from Master

But in your scenario, Slave 2 is not changing masters, it will still point to the same Master 2 server it always was at.

So now, in case of Master 1 failing, you will need to do two things:

Make sure your applications point to Master 2
Follow the instructions on promoting one of the three slaves to be new Master 2
make sure to enable --log-slave-updates on the new Master 2


Aliases in calculations for MySQL I am in the process to move a really small database from Access to MySQL and I have some queries to translate.

I have this really simple query (in Access db):

SELECT InvoiceNumber,
       Field1 + Field2 AS CalcField1,
       Field3 + Field4 AS CalcField2,
       (CalcField1 - CalcField2) / CalcField2 AS Percentage ;

This does not work in MySQL, because it does not allow column aliases in formulas. I learnt here that the solution is to wrap the alias with a (SELECT alias). That works, but the result is really ugly and becomes unreadable. In my case it is:

((SELECT ((SELECT CalcField1) - (SELECT CalcField2))) / (SELECT CalcField2)) AS Percentage

In the former case I can understand immediately the expression, in the latter I cannot.

My question is: is there an alternative way to use aliases in MySQL formulas that have a better readability?

It's more readable if you use a derived table. No need for 4 SELECT clauses, just 2. Plus, it's proper SQL, not some fancy feature that MySQL or Access has added:

SELECT InvoiceNumber,
       CalcField1,
       CalcField2,
       (CalcField1 - CalcField2) / CalcField2 AS Percentage
FROM
    ( SELECT InvoiceNumber,
             Field1 + Field2 AS CalcField1,
             Field3 + Field4 AS CalcField2
      FROM ...
    ) AS x ;

The other option, is not to use a derived table at all but double the calculated expressions:

SELECT InvoiceNumber,
       Field1 + Field2 AS CalcField1,
       Field3 + Field4 AS CalcField2,
       ((Field1 + Field2) - (Field3 + Field4)) / (Field3 + Field4)
         AS Percentage
FROM ... ;


Assigning Port to Listener in AlwaysOn We have multiple instances on single node of the AlwaysOn setup, all are configured as AOAG. Right now we are connecting using the Listener\InstanceName. The listeners for each AG is having a dedicated port designated. This is why we cannot connect just by using the Listener without the port or InstanceName.

I wanted to configure in such a way that just giving the AG_Listener would be enough to connect without using the port explicitly.

  Example : AG-SQL001 instead of AG-SQL001,Port_Number

Can this be done ? If yes then how?

Right now we are connecting using the Listener\InstanceName

The listener does not actually interact with the Browser service, and thus I wouldn't use this way with a listener - ever.

  I wanted to configure in such a way that just giving the AG_Listener would be enough to connect without using the port explicitly.

Set the listener for port 1433. The listener uniquely identifies the instance of SQL Server and there can be multiple listeners all using 1433 as each listener has a unique IPv4 and/or IPv6 address. Note that there are other means to which SQL Server can be connected through when there are multiple instances on the same server which may or may be appropriate or correct ways of connecting.


how to bring in the most recent record in SQL in Teradata I have a table that contains store number, inventory $, inventory year from 2014 through 2018. so if store 111 has inventoried in all the years, I will have 5 records for that store:

Store Inventory year Inventory $
 111      2014         $$$$
 111      2015         $$$$
 111      2016         $$$$
 111      2017         $$$$
 111      2018         $$$$

and if store 121 has inventoried every year but 2018 I would have 4 records for that store:

Store Inventory year Inventory $
 111      2014         $$$$
 111      2015         $$$$
 111      2016         $$$$
 111      2017         $$$$

I would like to write a query that would bring one record per store, and only the one with the most recent inventory - so based on my above example, I would have the 2018 inventory result for store 111 and 2017 inventory result for store 121.

Can you help please?

This is a ranking task, easily done based on RANK/ROW_NUMBER.

select ...
from
 ( select ...
      ,rank() over (partition by Store order by Inventory_year desc) as rn
   from tab
 ) as dt
where rn = 1

Teradata supports an SQL extension to avoid nesting:

select *
from tab
QUALIFY rank() over (partition by Store order by Inventory_year desc) = 1


Transfer image files from MongoDB to S3 I have a NodeJS app that stores all of its data, even images, to MongoDB. Now I want to use an S3 (DigitalOcean Space) to store my app's files (images, 43gb) and so, I need to transfer there all files that I have in my mongo.

I found the s3cmd tool that can transfer my files to the S3 instance (DigitalOcean space), but I can't find a way to actually access those files. I know they are stored in the fs.files and fs.chunks collections, but I can't figure out how to actually use them as input to s3cmd!

Any help will be much appreciated!!!

  EDIT: This is the format the images are being stored with:

fs.files

{
  "_id" : ObjectId("5ae97922c1dabec8d2d0bdb0"),
  "filename" : "2b57455f3878d11dabc9c984da7de314_postImage.jpeg",
  "contentType" : "binary/octet-stream",
  "length" : 2291623,
  "chunkSize" : 261120,
  "uploadDate" : ISODate("2018-05-02T08:38:58.549Z"),
  "aliases" : null,
  "metadata" : null,
  "md5" : "9ad420eaa7c28a73e449199430627802"
}

fs.chunks.findOne()

{
  "_id" : ObjectId("5ae2d77f6616b4a9d93cb4b1"),
  "files_id" : ObjectId("5ae2d77f6616b4a9d93cb4b0"),
  "n" : 0,
  "data" : BinData(0,"iVBORw0KGgoAAAANSUhEUgAAAuAAAAJvCAYAAAA6OGQEAAAABGdBTUEAALGPC/xhBQAAACBjSFJNAAB6JgAAgIQAAPoAAACA6AAAdTAAAOpgAAA6mAAAF3CculE8AAABWWlUWHRYTUw6Y29tLmFkb2JlLnhtcAAAAAAAPHg6eG1wbWV0YSB4bWxuczp4PSJhZG9iZTpuczptZXRhLyIgeDp4bXB0az0iWE1QIENvcmUgNS40LjAiPgogICA8cmRmOlJERiB4bWxuczpyZGY9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkvMDIvMjItcmRmLXN5bnRher38tcAAAAASUVORK5CYII=") }

In MongoDB, this is called GridFS, but s3cmd can only upload files from your operational system filesystem.
You need to make a script to query GridFS, create a temporary file, then use scm3 to upload it.

Read this article describing how to use GridFS on Node.JS: https://mongodb.github.io/node-mongodb-native/api-articles/nodekoarticle2.html

If you want to understand how to make a GridFS to S3 script, see this Perl script: https://github.com/mediaprojects/gridfs-to-s3

And if you have Perl installed, you can just run the script.


Postgresql - newer identical index is used, older one used to be but is not anymore Postgres version 11.2

I've got a table with about 30 million rows and a particular index that up until recently was actively being used.

And then it stopped being used and the query that the index was made for, is now doing sequential scans.

I discovered this when I was investigating the query and forgot to check which indexes the table already had, so I made a new one. Which turned out to be exactly identical in construction to an existing one.

explain showed that the new one was being used just fine. Removing it again, vacuum analyze-ing the table and doing the query again, showed the old index not being used. A sequential scan was opted for instead.

Deleting and re-creating the index worked. Now it suddenly was being used again.

It stayed like this for a few weeks and now I'm at this scenario again.

What's going on here?

Googling for this stuff just gives the old "why postgres may decide not to use your index" which isn't exactly the scenario at hand, I think.

Also, I used recent module "amcheck" https://www.postgresql.org/docs/11/amcheck.html

It comes back fine for this index.

EDIT:

Size of the index not being used is 101MB.
Size of the index being used is 16MB.

Query used for determining this: https://wiki.postgresql.org/wiki/Index_Maintenance#Index_size.2Fusage_statistics

While both small, it seems like a significant difference and I wonder where it came from.

There's other indexes on that table which are over 10GB, that show as being used...

Wasn't sure how to find that, just did select * from pg_index where indisvalid = false; and that returned an empty list.
Here is the output for index used: https://explain.depesz.com/s/H5X9y
Here is the output for index not used: https://explain.depesz.com/s/n6bP

PostgreSQL grossly overestimates how many rows will be returned by the index scan. It estimates the cost for the index scan and the sequential scan are about the same, so the slightly larger size of the index when it gets bloated probably tips the scales.

You should improve the estimates. Since it is a simple inequality condition on a timestamp, PostgreSQL should be able to come up with a decent estimate.

First, run an ANALYZE on the table and see if that does the trick.
If it does, make sure that autoanalyze runs more often:

ALTER TABLE atable SET (autovacuum_analyze_scale_factor = 0.02);

If not, run

SET default_statistics_target = 1000;

Then ANALYZE again and see if that improves the estimate.

If yes, use a statement like the following to persist the setting:

ALTER TABLE atable ALTER acolumn SET STATISTICS 1000;


MySQL performance slows down when accessed from different regions via the Internet I have a MySQL database hosted in a server in Brazil.

When I connect through an ODBC connection (FE is access) with a Brazilian IP everything runs fine.

When I connect from the U.S. with the same connection performance slows down dramatically.

Any suggestions?

If you have a lot of back-and-forth between your front-end and your database,  network link latency could kill your application's performance.

Measure ping times between your US front-end and your Brazilian database server. Your front-end needs to wait for that round-trip time for each and every database access (sometimes more than once per request).

If this is indeed your problem, then I'm afraid there is no easy fix for latency generally speaking (unless you can find a provider that has a more direct route to your Brazilian endpoint). Adding more "bandwidth" (Mb/s) won't solve high latency issues.
You need to keep your front-end closer to your database, or reduce the number of database calls.


conversion of high_value to date datatype I need to comparte the high_value which is of LONG  datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old.
The code is some what like this.

declare
       dt date;
   time_to_stay number:=1; --CONFIGURE AS PER THE REQUIREMENT
    begin
    for x in (select partition_name , high_value, partition_position
          from user_tab_partitions
          where table_name = 'DEMO')
loop
   execute immediate 'select '||x.high_value||' from dual' into dt;
     if to_date(dt) < sysdate-time_to_stay AND x.partition_position <>'1'     then
execute immediate 'ALTER TABLE DEMO DROP PARTITION '|| x.partition_name|| ' UPDATE GLOBAL INDEXES';

This query executes but does not deletes the partitions. However I see that main issue is converison of   LONG   datatype of high_value to DATE  to make the comparision between high_value and sys_date.

How can I fix this?

I think there is another solution to this.

DECLARE

   CURSOR c1 IS
   SELECT
      HIGH_VALUE
   FROM
      USER_TAB_PARTITIONS
   WHERE
      TABLE_NAME = 'MYTAB';

   ls_sql_statement   VARCHAR2(1000);
   ld_date_val        DATE;

BEGIN

   FOR c1rec IN c1 LOOP
      ls_sql_statement := 'SELECT '||c1rec.HIGH_VALUE||' FROM DUAL';
      EXECUTE IMMEDIATE ls_sql_statement INTO ld_date_val;
      --Do more stuff here with ld_date_val
   END LOOP;

END;
/


How to create a VIEW in SQL Server I have a user table and I need to create a VIEW so a user can only see their own data/row.

How could I achieve this? I have been searching online but I can't find anything useful

User with user_id, name, address

You need to know who the user is and that information needs to be accessible through the view.

CREATE VIEW dbo.tPatientForUser
AS
SELECT patient_id, name, address, gender, loginame
FROM tPatient
WHERE loginame = SUSER_SNAME();

Or, depending on your application you might need:

WHERE loginame = ORIGINAL_LOGIN();

This can help you filter the rows to only those with the same user.

EDIT: Per your latest comment:

To create a login for someone, and to make them a user of your database, you can read the KB article at: https://msdn.microsoft.com/en-us/library/aa337562.aspx   (Look at the examples toward the bottom of the page.)

Examples:
-- Create a login for SQL Server by specifying a server name and a Windows domain account name.

CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;
GO

-- Creates the user "shcooper" for SQL Server using the security credential "RestrictedFaculty"
-- The user login starts with the password "Baz1nga," but that password must be changed after the first login.

CREATE LOGIN shcooper
   WITH PASSWORD = 'Baz1nga' MUST_CHANGE,
   CREDENTIAL = RestrictedFaculty;
GO

Remember though that the View or the Stored Procedure must implement your security plans, which you have not really explained.  This tip is just showing you how to make use of a users identity, not how to create your system.