texts:
I want to find out all tables having a column let's say test. How to obtain this? I need DBName, TableName, testcolumnexist(Yes/No), TableCreateDate and ProductName details.

The tricky part is tablenames. I have tables with IDs appended at the end. Below is the entire scenario:

bColumn to find = test
Tables = Logs#, Details#, Data#

Where # represents IDs coming from different table master. i.e. select id from master where id< 5).

So my tables are like Log1, Details1, Data1 for ID = 1, Log2, Details2, Data2 for ID = 2 and so on

Output: DBName, TableName, testcolumnexist(Yes/No), TableCreateDate, ProductName

Note that ProductName column exist in master table.


texts: Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL

texts:
Possible Duplicate:
  Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL

I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs (149,000,000 rows are decreased to 460,000 rows). The query includes 3 table inner join (each table has about 20,000,000 rows). Further information, the process is completed in 12 seconds for a test file which has 1000 rows(703rows returned). I started the query, but it is still running in the state: "Copying to temp table on disk" for 38000seconds(10hours and 30minutes). I think there is a problem while insertion process. How can I avoid from this situation? The operating system of the computer is Windows7, 3GB ram, Intel core2duo 2.27GHz processor.
The query is:

insert into kdd.contents(adID, descriptionWords, purchasedkeywordWords, titleWords)
SELECT
training.adid,
descriptionid_tokensid.tokensid,
purchasedkeywordid_tokensid.tokensID,
titleid_tokensid.tokensID

FROM kdd.training

inner join kdd.purchasedkeywordid_tokensid
on training.keywordID=purchasedkeywordid_tokensid.purchasedkeywordid

inner join kdd.titleid_tokensid
on training.titleID=titleid_tokensid.titleID

inner join kdd.descriptionid_tokensid
on training.descriptionID=descriptionid_tokensid.descriptionID

group by adID;


texts: OPENROWSET can't import an excel spreadsheet into SQL Server 2005 Express

texts:
I am trying to run the following query to select data from an excel spreadsheet:

SELECT     FT_ID_SOCIETE_FF2C,FT_NOM,FT_ADR1,FT_ADR2,FT_ADR3,FT_CP,FT_VILLE,FT_TEL,FT_FAX,F10 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\SQLFTP\LISTEFF3CFTP.xls;HDR=YES','select * from [sheet1$]');

SSMS is sending back the following error:

  Msg 7399, Level 16, State 1, Line 1 The OLE DB provider
  "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an
  error. The provider did not give any information about the error. Msg
  7303, Level 16, State 1, Line 1 Cannot initialize the data source
  object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
  "(null)".

ProcessMonitor is telling me this :

Date & Time: 21/11/2011 18:41:55
Event Class: File System Operation:
CreateFile Result: NOT A DIRECTORY
Path: D:\SQLFTP\LISTEFF3CFTP.xls
TID: 3496
Duration: 0.0000070
Desired Access: Execute/Traverse,Synchronize
Disposition: Open
Options: Directory, Synchronous IO Non-Alert
Attributes: n/a
ShareMode: Read, Write
AllocationSize: n/a
Impersonating: VM-CHALEUR-FIOU\Administrator

I have also installed the Office 2007 components and tried to use Microsoft.ACE.OLEDB.12.0 and got the same results from both SSMS and process monitor.

I am however able to import a CSV file from the same location so it is not a permissions issue.
I have tried running the same import from other SQL Server 2005 express servers and got the same result.
The Ad Hoc distributed queries option is set to 1 in sp_configure.

Anyone know what I'm doing wrong?


texts:
I'm trying to kind of emulate a folder structure in my application. Now I've thought of two ways to achieve this, but what is the best practice for that?

Table: Attachments

Columns: Id (PK), ParentId (FK), Name, FileContent, IsFolder

or

Table: Attachments

Columns: Id (PK), FolderId (FK), FileContent

Table: AttachmentFolders

Columns: Id (PK), Name


texts: Wasted space in MDF file: Should I just use SHRINKFILE or some other alternative?

texts:
I've got a database which has an 850GB MDF file. Over the course of 20 months a logging mechanism in the application created a variety of huge tables. The maintenance script which was only supposed to retain a weeks data at any one time wasn't executing properly (it is now) so we had to manually clean things up.

We actually cleared about 600GB worth of data. I want to free up some of this space as the drive it is on is near limit. The data file will never grow this large again so it's just wasted space.

I was planning to use SHRINK-FILE to deal with this. I have tested on a clone of the database it takes around 3 hours. There doesn't seem to be any performance degradation on the database. Yeah, the indexes are heavily fragmented afterwards but I can sort those.

My question is this:

The application needs to remain 'up' throughout this process as it's critical. I understand that the SHRINKFILE operation is a fully online one, or am I wrong?

The database has a large amount of write activity occurring pretty much every second.

Will the increased I/O caused by the SHRINKFILE operation affect these writes?

Turning this on its head, will the continuous writes affect the SHRINKFILE operation, i.e. make it slower to complete?

Finally, is there a better way to do this?

  Update

  Just for anyone reading this - I ran SHRINKFILE and monitored I/O and
  CPU. There was a slight rise in both but no latency issues at all with
  my high-writes-per-second database. It remained up, stable and
  trouble-free throughout the 1.5 hour SHRINK operation.

  Indexes were horribly fragmented afterwards which gave the reporting
  side of the application a little grief (graphs were slow to render on
  the client application) but an Online index rebuild later and
  everything was back to normal.


texts:
I have Mongodb 3.2.6 with 4 shards. Each shards has 4 replica.

My db is working perfect but suddenly it is showing $mergeCursors on one collection is running slow and made whole DB response slow!!, sometimes it resolves fast, sometimes it makes Mongo response unstable for more than 30 minutes while I can not see any long-running query.

So question is How can i find reason/Query related to this slowness? For More info i should say CurrentOP don't show query and it is reflecting $mergeCusror as long running.

Slowness log is as below:

  2017-10-18T18:11:33.437+0330 DB.colservice command: aggregate {
  aggregate: "Service", pipeline: [ { $mergeCursors: [ { host:
  "hodt0:27000", id: 798236908 }, { host: "host1:27100", id:
  157434398368 }, { host: "host2:27200", id: 80616205236 }, { host:
  "host3:27300", id: 75977221429 } ] } ] } keyUpdates:0 writeConflicts:0
  numYields:0 reslen:6227 locks:{ Global: { acquireCount: { r: 4 } },
  Database: { acquireCount: { r: 2 } }, Collection: { acquireCount: { r:
  2 } } } protocol:op_command 9759ms


texts: Getting the name of the column that was used in a join?

texts:
This may be a bit of a weird question:

Our project's datamodel has multiple tables with multiple columns any one of which can be used to join with one settingtable. The question here is how to get the name of the column that was used to connect to the settingtable in an OR join?

For instance, let's say the query looks like this:

SELECT FIRST.ID, SEC.ID, TRD.ID, FRTH.ID, SETS.ID, SETS.VALUE
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECDONTABLE_ID = FIRST.SECONDTABLE_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRDTABLE_ID = FIRST.THIRDTABLE_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTHTABLE_ID = TRD.FOURTHTABLE_ID
-- etc...
LEFT JOIN SETTINGTABLE SETS
   ON (SETS.ID = FIRST.ONESETTING_ID OR SETS.ID = FIRST.SECONDSETTING_ID
      OR SETS.ID = FIRST.THIRDSETTING_ID OR SETS.ID = TRD.RANDSETTING_ID
      OR SETS.ID = FRTH.ONESETTING_ID
-- etc...
   )

Now, that's a very parsed example of what's really going on. We may have dozens of tables with several joinin columns on each one to the SETTINGTABLE.

Obviously, merely making a separate join for each table.column would show you the column used. Or alternately, you can use CASE clause in the select. But either one is tricky and requires a lot of manual input. Even just using all the joining ID-columns in the select would enable you to compare the ID's, but since the results need to be readable by non-technical people, that wouldn't help much.

So is there a way to use some sys objects or somesuch to simply return the column name that was actually used to connect to SETTINGTABLE? The design is what it is, and will not change. So I'm simply trying to find the best way to work with what we have. This would significantly speed up our diagnosis of some of our scripts, queries and data.

Thanks!


texts: MSSQL - Lock escalation on partitioned indexed view

texts:
I'm using partitioned tables where I set LOCK_ESCALATION to AUTO to prevent lock escalation to table level, but keep it on partition level.

On the top of that table I have indexed view which is partitioned on the same scheme, but updates on underlying table are escalating locks on whole indexed view, which basically cancels the effect of partition level escalation on the underlying table...

I would also like to escalate the indexed view locks only to partition level, but I can't find a way how to set it on indexed view as ALTER VIEW doesn't allow to specify SET options.

I wasn't able to find any other way how to enable that, so I'm assuming that it's not possible. Or does anyone know about any way how to set it?


texts: Why database snapshot creation is faster than normal database backups?

texts: Could you tell me any reason why creation of database snapshot is faster than database normal database backups in MS SQL server


texts: Join on unnested integer array column and count occurrences in array

texts:
I am trying to join two tables after I have unnested one of them. The tables are:

CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60));

CREATE TABLE training_modules (id SERIAL, course VARCHAR(60), teachers_id INTEGER[]);

I want to write a query that will return the teachers names and the number of training_modules they've participated in (a count of their IDs), like so:

teacher_name  id_count
bob teacher   4
sally lady    3
jimbo jones   5

So far, I've been able to get a count of each ID with this query:

SELECT tid, count(*) as id_count
FROM training_modules tm, unnest(tm.teachers_id) as tid
GROUP BY tid;

All fine and good. I tried to apply that to another query, where I join the teachers table, and it doesn't work as expected. This query:

SELECT t.id, concat(t.first_name, ' ', t.last_name) AS teacher_name, tm.id_count
FROM (
   SELECT unnest(training_modules.teachers_id) AS id_count, count(*)
   FROM training_modules
   GROUP BY id_count
   ) AS tm
INNER JOIN teachers t ON tm.id_count = t.id;

Results in the table below, where the id_count column is no longer a count, but returns the same value as the t.id column.

 id |     t_name      | tc
----+-----------------+----
  5 | Jimbo Jones     |  5
  4 | Frank McGee     |  4
  6 | Sara Sarason    |  6
  2 | Joshua Jesps    |  2
  1 | Larry Bucatin   |  1
  3 | Natalie Fatali  |  3

Any help to get the right count appearing while joining the tables would be much appreciated.


texts: Best Practice to Store an Admin Configured Variable

texts:
Some background:

Users submit a form that has a category.

If (category has manager) {
send manager email
}
else {
send 'default' manager an email
}

For the sake of argument, manager_category has 2 columns; Manager's Email and Category

Admins can update the 'default' manager.

Whats the best practice for storing the 'default' manager?

Imo, adding another row in manager_category with 'default' as the Category is best.

Another option is to create a new table with 1 row and 1 column.

What is the best way to store this value?

EDIT: I should add that there's 4000+ categories. Most of them don't have rows.


texts: convert multiple columns as single row with appreciated data

texts:
See the image below.

The first table is the actual table.

How to get the expected result in the second table using a procedure when I pass formid as parameter?

example:
exec procedure 123


texts: I need to alter a table in H2 db. But we have millions of databases with the same same table. What may be the feasible way to alter such databases?


texts: Hide zero hours and minutes with NLS_DATE_FORMAT

texts:
I'm currently using date format:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI';

The (cosmetic) problem is that this always displays time even if it's zero, for example: "27.03.2014 00:00". Is it possible to hide 00:00 with NLS_DATE_FORMAT, but display the time when it's not zero?

I realize this is not a good idea for any kind of automatic processing but this is only for interactive use. I know I can format individual columns in the query but I'm interested in more general solution.

I did check the format model documentation but I didn't find any relevant parameters so maybe it's not possible.


texts:
I know how to log all queries to a file in mariadb, is there a way for me to log only remote transactions?

I've got a situation where I have a server with black-box software that does a lot of work from a central server and a lot of work on it's own server (side-by-side with mariadb). The central server seems to be doing a crazy load of unnecessary labor and I want to be able to separate what it's doing from what the other processes are doing.

So, how can I log only queries done by users logged in from remote locations?


texts: Filtered Index is not selected once an identical, non-filtered, index is made

texts:
I have a lot of historical data, but 98% of the time, my data entry team only looks up data from the last 5 days.  So I added a filtered index to help those lookups be faster.

My test query looks similar to this:

SELECT CustomerId, OrderId, Col2, Col3, Col4 -- about 30 columns
FROM   CustomerOrder
WHERE  CustomerId = 22000000
OPTION (RECOMPILE)

And I have this filtered index:

CREATE nonclustered index FIX_CustomerOrder_CustomerId
ON     CustomerOrder (CustomerId)
INCLUDE (OrderId, Col2, Col3, Col4) -- about 30 columns
WHERE  CustomerId > 21000000 -- This equates to the last 5 days worth of data

When I run that query, the filtered index is chosen and used.

However, if I then add this index (for intended for the few older rows that will get looked up):

CREATE nonclustered index IX_CustomerOrder_CustomerId
ON     CustomerOrder (CustomerId)
INCLUDE (OrderId, Col2, Col3, Col4) -- about 30 columns

It gets picked instead of the filtered index.
(NOTE: Both indexes are exactly the same except that the second is not a filtered index.)

So I am confused.  The Filtered Index removes 21 million rows from the equation.  Why would the optimizer not use it?

Additional Info:
If I add the option with (Index (FIX_Customer_OrderId)) then the index is used, and the performance is still good (about the same).  But I thought filtering out all those rows would be much faster...

More Additional Info:
If I extend the size of the filtered index to cover more days it eventually gets selected.    The sweet spot seems to be 7 days.  6 uses the normal index, 7 uses the filtered index.  Weird!

(NOTE: I have double checked to make sure that my row I am selected is in the original  filtered index.  The key I am searching on is 21317761.  My 5 day filtered index value (that does not get used) is Id > 21289782.  My 7 day filtered index value (that does get used) is Id > 21232773.)


texts: Querying a table with 200+ million rows by filtering on the clustered index AND additional column is super slow

texts:
We have one table that has clustered index on two columns: first one is NVARCHAR(50) and second one is on DATETIME2(3) type.

There are few other columns. When we query the table with WHERE clause on the clustered index columns, the result is received immediately, even when the result set is tens of thousands of rows.

However, if we add one more predicate on the where clause, the query is super slow. After ten seconds I just stop it, because that is not useful to us.

The table has 200+ million rows.

This is the query that is super fast:

SELECT *
FROM Table
WHERE Col1 = 'blah' AND Col2 BETWEEN 'date1' AND 'date2'

This is the query that is super slow:

SELECT *
FROM Table
WHERE Col1 = 'blah' AND Col2 BETWEEN 'date1' AND 'date2' AND BooleanColumn = 1

In my mind, the second query should use the clustered index to search for rows and then simply scan the result set to filter out what's needed.

Is it possible to (somehow) make the second query work fast without creating additional non clustered index that would include other columns that we need to filter on?

Here is the query plan for the second:

https://www.brentozar.com/pastetheplan/?id=BJj28mjvL

After the first run, it works fast now for different combination of values for the WHERE part, which means probably it's not that results are cached, but the query plan is generated and optimized.


texts: How to move a specific Postgres database to different harddrive?

texts:
I have my postgres db running on harddrive d:\, and also all the tables are placed there by default.

Now I want a specific table to be save to a different drive (say f:\). The table is not yet existing.
How would I do this, if possible at all?


texts: mysql fine tuning: open_tables, opened_tables

texts:
I'm a novice to database administration and have been depending on forum suggestions and answers to help resolve my database hang ups and too many connections errors;  I've managed to reduce the frequency of too many connections errors but I'd like to eliminate them at this level.

According to an answer by RolandoMySQLDBA on  this post, "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."

My Server specs is 2.0Mhz and 484Mem; I've had a client whose having a lot of hits upto 1000 requests per hour and with processing mainly of their 2 InnoDB tables, that translates to roughly 3000 connections made to both tables in an hour.

From advise on another forum, I've made changes to my.cnf and it translates to this:

[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking
key_buffer_size = 30M
max_allowed_packet = 1M
table_cache = 300
sort_buffer_size = 256K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
net_buffer_length = 12K
thread_stack = 1M
innodb_buffer_pool_size=60M
interactive_timeout=300
wait_timeout=300

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 48M
sort_buffer_size = 48M
#key_buffer_size = 256M
#sort_buffer_size = 256M
#read_buffer = 2M
#write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

When I checked the parameters of opened_tables, open_tables and uptime as per the post, I get these values:

Uptime = 78018
Opened_tables = 1606999
Open tables = 300

therefore: Opened_tables / Uptime=1606999/78018= 20.6

However, my Opened_tables variable has not changed in 3 hours; It has been the same.

After these changes, the server load had occurred twice in the past 24Hours (at night) and I didn't record the value for Opened_tables at this period; But as you'll note from the calculation, the Opened_tables value increased drastically last night during when many client requests came through.

Output of thread variables are:

 Threads_cached                    | 7          |
| Threads_connected                 | 14         |
| Threads_created                   | 306        |
| Threads_running                   | 2

and i note from the mysql documentation (http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Threads_connected) that I need to increase the thread_cache_size .

my max_connections is the default;

So basically:

I need to increase thread_cache_size;
I need to increase the
table_open_cache (but a very large value also causes
unreliability)

I've just had a "too many connections" error at the time of posting this and can't get results for Opened_tables and Open_tables; Also, I can't login with another session to the server; I automatically get disconnected.

QUESTIONS:

What method should I use to approximate the values of
thread_cache_size and table_open_cache?
Is my max_connections too low? How can I calculate the best max_connections setting?
How can I reduce the "too many connections" error (without creating another user and having that user be the one making the connections rather than root) ?

NOTE: No I can't upgrade memory as at now. Thanks.
       Right now the only way I deal with this is to close the port form incoming messages (which is not exactly ethical) or to restart server to pending messages in system can be processed and memory freed.

After my now too many connections errors - which I've resolved by blocking the receiving port; my variables are now:

Opened_tables = 1970498
Open_tables =300

| Threads_cached                    | 6          |
| Threads_connected                 | 15         |
| Threads_created                   | 437        |
| Threads_running                   | 2          |

Any advise i dealing with this wisely and efficiently will be much appreciated. Thanks in Advance.

[3]:


texts: How to update a column whose value is being calculated from the values exists in other columns of same table in MySql?

texts:
I've a table User_Engagement having columns :-

I was trying to implement this below formula on my update Query to calculate TotalInteraction of all columns in a single query:-

totalInteraction =   value in totalComments + value in totalLikes * ( SUM of averageLikes / SUM of averageComments)

So far I've tried to implement this query :-

UPDATE User_engagement AS ue
SET
ue.totalInteraction = (ue.totalComments + ue.totalLikes) * (SUM(ue.averageLikes)/SUM(ue.averageComments))

This above query is giving me an error #1111 - Invalid use of group function in MySql.

A help is highly appreciated.


texts: Best Practice : Schema design for Large Number of rows with few columns

texts:
I have about a data set that each entry is about 750,000 rows under it with each row having four fields. Each entry is in its' own CSV file currently and I want to get it into a database. Currently I have a couple hundred entries and the CSV files are about 25 megabytes each.

Each of these rows contain a row label,  a data type label, a start position, and an end position; so about 4 fields per row.

The row label id has over a million potential values (nor do I have a complete list of potential values), and will not appear in the same order in each record nor necessarily even appear.

The data type label is categorical.

The start and end position fields are large integers.

My thoughts are to create a table with Record names and supporting data, another with the categorical label of the
data,  but I am being challenged on how to deal with the 750,000 columns per entry and the start and end positions.

I was thinking of big data table with all 750,000 rows per entry. but it seems like it would be very inefficient to approach it this way; the table looking something like this.

RecordID
RowLabel
CatLabelID
StartPosition
EndPosition

I am running SQL Server 2012 standard edition.

What is the best practice schema to address this type of large data set?


texts: Unique identifier field with a conditional

texts:
I have a database that's not in production, so the main table being CustodyDetails, this table has a ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique identifier that its not referenced in any other table, I would think by taking this in account the content of the column wouldn't be exactly an identity key.

This new identity column has a few specific details though, and here's where my problem starts.
The format is as follows: XX/YY where XX is an auto incrementable value that resets/restarts every new year and YY is the last 2 digits of the current year SELECT RIGHT(YEAR(GETDATE()), 2).

So for example lets pretend one record is added a day starting from the 28/12/2015 ending 03/01/2016, the column would look like:

ID    ID2     DATE_ADDED
1     1/15    2015-12-28
2     2/15    2015-12-29
3     3/15    2015-12-30
4     4/15    2015-12-31
5     1/16    2016-01-01
6     2/16    2016-01-02
7     3/16    2016-01-03

I thought of using the frontend to parse the composite ID (ID2 in the example) get the last 2 digits and compare with the last 2 digits of the current year and then decide whether or not to start a new correlative. Of course it would be grand to be able to do it all on the database side.

EDIT 1: btw, I have also seen people using separate tables just to store parallel identity keys, so one table Identity key becomes a second table secondary Key, this sounds a bit dodgy but maybe this is the case such implementation comes in place?

EDIT 2: This extra ID is a legacy document reference that labels every file/record. I guess one could think of it as an special alias for the main ID.

The number of records this database handles yearly hasn't been out of the 100 in the last 20 years and is highly (really, extremely highly) improbable that it would, of course if it does go over 99 it the field will be able to carry on with the extra digit and the frontend/procedure will be able to go over 99, so its not like it changes things.

Of course some of these details I did't mention at the beginning because they would only narrow down the possibilities of solution to suite my specific need, tried to keep the problem range broader.


texts: Copying all columns of a row from table into another?

texts:
I would like to keep the history of a table using SQL queries without triggers. The simplest way to do that is to fire a SELECT and INSERT into the history table like below:

INSERT INTO history (field1, field2)
SELECT
  field1,
  field2
FROM
  current
WHERE
  field1 = condition

However is it possible to do this without hard-coding the columns i.e a select * equivalent?


texts: How to Select Most Recently Updated Row Based on Sequence ID

texts:
I have a primary ID column but also a sequence ID column for each edit of the row (the initial row is 1, each edit increments the sequence ID but the primary ID column remains the same). What is the best way to select only the most up-to-date (max sequence ID) row in queries? I currently do this programmatically but was wondering if there was a way to do this all in one query with SQL.

Sample Data:

Person_ID   Person_Seq_ID   Person_Name   Person_Favorite_Color
---------   -------------   -----------   ---------------------
1           1               John Doe      Green
1           2               John Doe      Turquoise
2           1               Jane Doe      Blue
3           1               Mike Smith    Red

Let's suppose I have this query:

SELECT * FROM Person

I would only want the most recent John Doe row to return (sequence ID #2 in this case), and Jane's and Mike's single rows should also be returned.

Desired Results:

Person_ID   Person_Seq_ID   Person_Name   Person_Favorite_Color
---------   -------------   -----------   ---------------------
1           2               John Doe      Turquoise
2           1               Jane Doe      Blue
3           1               Mike Smith    Red

What's the best way to do this with one SQL query so that it can be used with different types of queries such as if I only want to return certain columns?


texts:
1/ Description:

I have 2 Centos machine: master, slave (streaming replication). Both of them are built on PostgreSQL 9.3 (image below) and configured failover event (failover PG 9.3 document).

Last WAL in master (pg_xlog): 0000000100000002000000D8
Last WAL in archive log folder in master: 0000000100000002000000D7
Last WAL in slave (pg_xlog):  0000000100000002000000D5 (3 WALs latency between slave and master)  .
recovery.conf   file in slave

standby_mode = 'on'
primary_conninfo = 'host=10.12.10.108 port=5444 user=enterprisedb password=123456 '
trigger_file = '/tmp/failover'
restore_command = 'scp enterprisedb@10.12.10.108:/opt/archivelog/%f "%p"'

Now (3 WALs latency slave & master), the master fails (shutdown by manual) then the slave begin failover procedures..

2/ Questions:

Does slave miss 3 WALs(0000000100000002000000D6, 0000000100000002000000D7, 0000000100000002000000D8) or 1 WAL (0000000100000002000000D8) after failover ? Why ?
If I copy all WAL in pg_xlog from master to pg_xlog in slave . Does slave miss WAL after failover? Why ?
If I copy all WAL in pg_xlog from master to pg_xlog in slave (last WAL in slave: 0000000100000002000000D8 ) . How slave do a recovery when failover ?

EDIT:
 I am using pgpool-II 3.4 to control failover between master-slave (failover_command in pgpool.conf).


texts:
I'm aware of options to re-import data to tables using table_exists_action and can give options like skip,append or replace for the data.

But is there an option to re-import packages (re-create) into the database if they are already existing without throwing up an error like

" Package PKG_1 already exists"

I basically have packages which have some changes in them and re-import to another environment where there packages are already existing.


texts: Postgres logging_collector probably blocks auto_explain output

texts:
Postgres 10.4

Postgres 9.6

Problem on both versions

I want to collect postgresql slow queries plan, so I added to my postgresql.conf setting from documentation

session_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = '5s'

in the end of config

But also we use

logging_collector = true

and if logging_collector is on, no explain in logs

If logging_collector commented, I can see plan of queries
How can I use both options?

UPDATE:
We had about 10 queries runs more than 5 seconds, but finally with grep we found only one output in log with explain instead of all plans
How it could be only one record was logged? neither all plans, no no plans only one explain


texts: Is it possible to fetch or update table data by table field type?

texts: Is it possible to fetch or update table data by field type? If possible please tell me with a sample code.


texts:
I installed PostgreSQL 9.1 under Ubuntu 12.10

And now I have a table called property

I want to add a field that can be used to store photo(link to the photo or import a photo into a column?)

But I do not know how to add this field

Can PostgreSQL do this?

If so, what keywords can I use to find this information?

Update 2013/10/30

I try to use oid to import my files, and it works fine!

But when I want to use a not superuser user to output files,

it's give me an error

ERROR:  must be superuser to use server-side lo_export()
HINT:  Anyone can use the client-side lo_export() provided by libpq.

How to use client-side lo_export()?

I try to google it, but there's almost all server-side tutorial

So I tried to use byteA

it's works fine with other user but cannot export data to a file?

I tried to use bytea_export but bytea_import is make by Jack's sql script not a built-in function

So there is two more question:

1) How to use clent_side lo_export()

2) how to use bytea_export()??

Thanks for answers!


texts:
I'm sure many of you are/were dealing with a ugly database. You know, that database that isn't normalized at all, that database where you have to do a large painfully query to get the most trivial data, that database that is in production and you can't change a bit... you know, "that one".

My question is, how do you deal with it?

Do you try to make a new database?
You give up and leave it alone?
What advice can you give?


texts: Database Mail has incorrect encoding of CSV attachment

texts:
I have a procedure that generates a resultset, which I attach as a CSV file attachment in sp_sendmail.  However, the resulting file is encoded UCS-2 LE BOM (according to notepad++).  This causes the resulting file to not "parse" into columns but opens with all values in column A. If I change the encoding to UTF-8 it opens fine.
Problem:
This is an easy fix for me... but the recipients would have no idea how to do this.  Can I change the file to be produced as UTF-8?
Code used to produce send the mail:
exec msdb.dbo.sp_send_dbmail
            @subject                     = @subject
        ,   @body                        = @message
        ,   @body_format                 = 'html'
        ,   @recipients                  = @recipients_to
        ,   @copy_recipients             = @recipients_cc
        ,   @importance                  = 'High'
        ,   @sensitivity                 = 'Confidential'
        ,   @query                       = N'select * from ##Report'
        ,   @execute_query_database      = @dbName
        ,   @attach_query_result_as_file = 1
        ,   @query_attachment_filename   = @file_name
        ,   @query_result_header         = 1
        ,   @query_result_no_padding     = 1
        ,   @query_result_separator      = ','
        ,   @query_result_width          = 32767;


texts:
I have some code that works in Access but now I want to forward the code to our IT department to make a view in our SQL database.

Could anyone please help me convert it so it is correct for normal SQL and not Access SQL if that makes sense?

I have tried to test it in ORACLE SQL Developer but am getting the error:

  Error at line 1, column 25: ORA-00923: FROM keyword not found where
  expected

SELECT T1.*,
       (SELECT TOP 1 T3.[SMN_DATEC]-1
        FROM
            (SELECT T2.[DET_NUMBERA], T2.[SMN_DATEC]
             FROM [CHRISCS_EMSAL] AS T2
             ORDER BY [DET_NUMBERA],[SMN_DATEC]
            ) AS T3
        WHERE T3.[DET_NUMBERA] = T1.[DET_NUMBERA]
          AND T3.[SMN_DATEC] > T1.[SMN_DATEC]
       ) AS EndDate,
       T1.[DET_NUMBERA],
       T1.[SMN_DATEC]
FROM CHRISCS_EMSAL AS T1
ORDER BY T1.[DET_NUMBERA],
         T1.[SMN_DATEC];


texts: Cannot delete domain login from SQL SERVER 2012

texts:
We are in the process of trying to clean up some old accounts on one of our AlwaysOn clusters.

This particular account refuses to play nice and allow itself to be deleted.

USE [master]
GO
DROP LOGIN [PROD\dba007]
GO

Error:

Msg 15173, Level 16, State 1, Line 4
Server principal 'PROD\dba007' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

The permission it's complaining about is a connect permission to the HADR_ENDPOINT

Select perm.* from sys.server_permissions  perm
INNER JOIN sys.server_principals prin ON perm.grantor_principal_id = prin.principal_id
where prin.name = N'PROD\dba007'

class   class_desc  type    permission_name state   state_desc
105     ENDPOINT    CO      CONNECT         G       GRANT

Now the next logical thing would be to revoke the connect permissions.

USE master;
REVOKE CONNECT ON ENDPOINT ::HADR_endpoint from [PROD\dba007]

But this produces the message

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

The only server role it is a member of is PUBLIC.

How can I find out what exactly is preventing me from revoking these permissions so I can drop the user?

Thanks.


texts: Is there a way to extract a report of or script all of the primary keys in a database?

texts:
I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of the schema still has primary keys, and I would like to generate a script that will add those keys to any deployed databases without them.

The best I can hope for is to just do something to script all primary keys, but I will be happy to also just extract the names of the columns that make up these keys and generate my own script.


texts: What is MySQL Cluster's Practical Memory Limit?

texts:
We are designing a cloud SaaS, and are actively considering MySQL Cluster choice for our DB. We will be writing more than 100M rows/day to the DB. We need to understand if there is any practical memory limit.

I understand that MySQL Cluster supports at max 48 data nodes, but is there practically any limit on the RAM each data node can have? RAM will be anyways expensive but we want to eliminate this if there is any hard limit.

The question stems from this link: http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html, where Storage Limit is mentioned as 3TB. It is slightly ambiguous to understand the way it is written.

Thanks in advance.


texts:
I have a database – call it Jacob. Every day a SQL Agent job copies all records older than 15 days to another database called Jacob_Archive

I would like to move Jacob_Archive to Azure, I’ve created a database with the tables, but when I try to move the data I get:

  Msg 40515, Level 15, State 1, Line 16
  Reference to database and/or server name in
  Jacob_Archive.dbo.MasterResults` is not supported in this version of SQL Server.

Please advise


texts: Filter and Aggregate a column not in the group by clause MySQL


texts: Designing database with Category and Tags for Posts - Seeking for guidance/advice

texts:
I'm not really a database novice but I'm not an expert either.

Problem:

I'm having this difficulty on designing my Post, Category and Tags relationship.

I've read Normalization many times but could not fully digest them and use it as a guide to what I'm trying to achieve. I've also encountered EAV, read about it and likewise. Or maybe I just can't choose which to use for this scenario.

I have few solutions in mind but I could not settle on one. I'm craving for the "BEST" choice. I'm aiming to be able to understand and design good databases!

Scenario

I have a Post table, like in any other website like wordpress with post capability.

I want that post to be associated with 1 or more Categories and Tags.

I want the category to have a hierarchy where categories like Cats and Dogs are under Animals category.

Option 1

Categories
    id -- PK
    pid -- FK to self (id), allow NULL
    name
    description
    slug
Tags
    id -- PK
    name
    description
    slug
Cat_Rel
    id -- PK
    pid -- FK: Post Id
    cid -- FK: Category Id
Tag_Rel
    id -- PK
    pid -- FK: Post Id
    tid -- FK: Tag Id

Notice how those Categories/Tags and their relationship with Post are very similar?

I have this urge that I think I could just combined them which leads to my problems.

Options 2

PostGroup -- I don't know how to call this currently but this should work for now
    id -- PK
    pid -- FK: category use only so NULL for all tags
    name
    description
    slug
    type -- and we add a type where we could explicitly tell if it's a category or a tag
Group_Rel -- Now we only have 1 relationship table
    id -- PK
    pid -- FK: Post Id
    gid -- FK: Group Id

So, which one should I follow? Or none of those? Other options?

On your reply, can you include DO's and DON'T, PROS and CONS.

Anything that you think I need to know, understand and realize.


texts:
We have a SQL server that appears to be leaking memory.  Memory consumption linearly increases over time until the server is completely out of memory, causing major performance issues.

The SQL server houses 6 small (average than 50MB) databases and one that is a little bit larger (about 2GB).

The server is currently a virtual server inside VMware 5.1.  It has very light average workload (~1% cpu, <10 batch requests / sec, < .5 mb/s IO typical; spikes push to ~10% cpu, 300 batch request / sec, 5 mb/s IO).

Currently, it has 8GB allocated to the VM.  Of this, 4GB is set to the maximum server memory in SQL server.  The SQL server process, however, uses significantly more than that - usually around 6GB within 12 hours of a restart.

Apart from its primarily read workload, the server runs many SSIS packages to get data in and out to other systems within the corporate structure.  This utilizes MySQL via ODBC (5.2.6 drivers installed) to connect to an internal MySQL cluster and ConnX to interface with OpenVMS systems.

OS: Windows 2012
SQL: 2012 Enterprise
VM Settings: 4 CPU cores, 8GB, separate virtual volumes for OS, Data, and Logs.

Questions:
1) Any ideas on why the SQL Server process would not be following the limit set on the server?
2) Does anyone know a good way of profiling memory usage within SQL server?  We can see that page life expectancy is dropping and more memory is being allocated to the process, but is there any way to see what SQL is allocating the memory to (i.e. procedure cache, cached results, etc).


texts: How can I configure SQL Server Aliases on a SQL Server Client Machine

texts:
Even after installing SSMS on a SQL Server Client machine, I found that SQL Server Configuration Manager would not run.  Manually loading the mof file via Microsoft's Instructions was sufficient to repair the "Cannot connect to WMI provider" error, but this only caused the 32 bit configuration manager to show up.  I also want to configure 64-bit aliases.

Historically, I've avoided this problem by using the SQL Server Install Media to install SSMS, but I was hoping this was no longer necessary now that SSMS was released separately.  Absent a documented recommendation from Microsoft, I'd rather avoid the commonly suggested hack of copying sqlmgmprovider.dll from other machines.


texts: What are reasonable options for an in-memory multi-core database?

texts:
I'm going to preface this with pointing out that I only need somewhat persistent data. The purpose of this database platform would be to support statistical analysis in R. I usually build my tables from csv files I get from clients and query those tables to build flat files to dump into R. I can either import a .csv type file or run a query from R. So, essentially I'm performing a lot of inner and outer joins on the entire data set to get the resulting output I need. To date, my databases haven't exceeded 5-10GB. I may have projects in the near future that will be larger but I don't see anything that would exceed memory. I need maximum speed for a little while.

To admit a little guilt - I would be happy with sqlite if it supported full joins (without getting too hacky) and if it had good multi-core support. I like it's simplicity - it just doesn't perform well enough. Or I'm too ignorant.

Options I have explored are:

PostgreSQL in a ramdisk - unsure if a ramdisk would actually be necessary but I've seen a lot of info on the topic.
Using MySQL memory tables - I haven't looked to see if other databases have a similar feature. I'm sure they do.
McObject's eXtremeDB - It doesn't quite seem like a good fit for me. It's designed to be an embedded DB.
VoltDB - I was excited about this option until I read that they don't quite have outer joins and self joins working. Their SQL seems a little too limited.

I'm switching from my laptop (running ubuntu) which frequently overheats to an Amazon EC2 instance which I can scale up as much as I need. Thus the need for good multi-core support. I'll likely build my tables in an on-demand instance and do the heavy querying in spot instances. My laptop has already conditioned me for periodic shut-downs so, I'm not too worried about that. I've already built an instance with R and have been having fun playing with AWS for other projects over the last few months.

I'm not beholden to any specific database platform however, I have reached a point of information paralysis. Reasonable solutions and things to consider will be very helpful. I'm not looking for a step-by-step how to - that's what Google and the rest of stack exchange is for. I've also been avoiding Amazon's RDC service for this. I'm not exactly sure why - probably so I can use spot instances.

I'm also open to the idea that I'm looking at my problem all wrong. Should I abandon SQL all together?


texts: SSRS 2017 - Client found response content type of '', but expected 'text/xml'

texts:
I've got SQL Server 2017 Developer installed on my workstation, and just installed SSRS 2017. I am able to navigate in the SSRS portal, and upload files of dummy data, and create KPIs. However, when click "+New", then "Dataset" or "Data Source", I get the message "Something went wrong. Please try again later." The only clue in a log file is in my RSPortal_yyyy_mm_dd......log file which shows the following:

OData exception occurred: Microsoft.SqlServer.ReportingServices2010.RSConnection2010+MissingEndpointException: The attempt to connect to the report server failed.  Check your connection information and that the report server is a compatible version. ---> System.InvalidOperationException: Client found response content type of '', but expected 'text/xml'

I get the same error if trying to upload an .rdl file.

I did a "next/next/next" install of SSRS, keeping the defaults. The only unusual thing is that I am logged in with a machine account, not a user account. This machine account does show in the top right corner of the SSRS web portal, so that is passing through at least.

What could be happening here?


texts: SQL Server 2008 R2 : multiple files and filegroup

texts:
I am getting bit confused regarding advantages of multiple files and filegroups in SQL Server

Using SQL Server 2008 R2, I have following filegroups and files configuration

Drive D: Primary Filegroup   with two files File1.mdf and file2.ndf
Drive E: Secondary Filegroup  with two files File3.ndf and File4.ndf

Both drive uses its own disk underneath a SAN (dedicated IO Path).

Questions:

If I create a table B on secondary file group, does this mean when inserting data proportional fill algorithm is used and data will be equally filled in both the files on secondary file group? answer in Yes or No
If there is a tableA on primary filegroup, if I perform SELECT query with a join between tableA and tableB (secondary filegroup) two threads will be spawn for the query as both tables resides on a separate volume?
Will restore database be any faster as it would spawn two threads for performing restore? I do have instant file initialization ON so to me no gain splitting file groups on separate drives.
What other benefits of having files in a filegroup on separate drives.


texts: Efficient manual cleanup of foreign keys in MySQL

texts:
I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added some manual cleanup maintenance scripts instead. These queries are causing problems as they appear to be very inefficient, so I'm wondering how they can be made to work more efficiently.
A typical arrangement is this:
t1
id, field1
t2
id, t1_id, field1
t1_id in t2 is a foreign key pointing at the id field of t1. Each table has id set as its primary key, and t2 has a regular index on t1_id.
So, instead of a foreign key constraint dealing with cascade deleting records in t2 when a parent record in t1 is deleted, I have a cleanup query like this:
DELETE t2.* FROM t2 LEFT JOIN t1 ON t1.id = t2.t1_id WHERE t2.t1_id IS NOT NULL AND t1.id IS NULL;

in other words, delete any record in t2 that points at a non-existent record in t1.
That works, but it's really slow. Here's the explain for it:
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows    | filtered | Extra                                |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+
|  1 | SIMPLE      | t2    | range  | t1_id         | t1_id   | 5       | NULL          | 2392097 |   100.00 | Using where                          |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.t2.t1_id |       1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+

The obvious problem here is that the row count is very high, and other questions I've seen on this subject suggest the range match type is at the root of the problem. If I omit the check for NOT NULL on t1_id, it matches the whole of the t2 table, making the row count even higher.
Because this is a single query, it tends to lock everything in t2 until it's complete, holding up other queries. I tried replacing it with separate select and delete, but it doesn't help much.
How can this be done better?


texts: SQL Server 2008 R2 reindex job reorganise getting stuck

texts:
I am currently experiencing this job getting stuck in my nightly maintenance solution -

ALTER INDEX [PK_ThirdPartySettleCalled] ON [evo_archive].[dbo].[ThirdPartySettleCalled]
 REORGANIZE WITH (LOB_COMPACTION = ON)

a full reindex of the table works.

but I'm using Ola Hallengren's awesome solution as I do on a number of sites that need maintenance solutions. http://ola.hallengren.com/Documentation.html and the job gets stuck in the middle of the night.

I've tested it as a single statement from within ssms with the same result, without an error I'm a little short of ideas.

Anyone experienced this issue before ?

No blocking,

the table is circa 300 million rows,

basically I say 'stuck' because no other processes are running in SQL server, and its been reorganising for hours with no error.

It is in the same state with 25% fragmentation or virutually 0% (tested after a full rebuild of the index)


texts: PostgreSQL difference between VACUUM FULL and CLUSTER

texts:
I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx index.

So to reclaim the space do I need to use cluster command or vacuum full command?

What is the difference between this two command ?
Is vacuum full order by some column same as cluster command?
Is index recreated in both the commands?
In my case which one will be faster?

The version of PostgreSQL database is 9.1


texts: Optimized query to fetch a record from 10 million entries

texts:
A table has 2 fields
| UniqueKey |  TimeStamp |
-------------------------
| xfsddddq  | 1024125412 |
| xfstttdx  | 1024125413 |<
| xfsdxxau  | 1024125415 |

Estimated, there are 10 million records. Need to verify if UniqueKey is present from within (CURRENT_TIME - 5MINS).

Currently

Flushing my table every hour (cannot be decreased further).
Inserting more than 10 million records in an hour, as time increases the number of rows executed to fetch one record is also increased further leading to increase in execution time.
How to limit query to check one record from (CURRENT_TIME - 5MINS) or effectively fetch the result so that, the time of execution is same at 5th minute and 59th minute.


texts: How to solve multidimensional data relationship

texts:
I'm having a problem, that I could solve programmatically, but for my personal (test) project I'm trying to see if I can fix it in the relational model.

Imagine a complex fashion product Pants with these Properties as dimensions:

Length (35,36,37,38)
Size (32,33,34,35)
Color (White, Red, Blue, Yellow, Green)
Kneepatch (Woodcutter, Brown, Silver)
Linked-belt (Brown, Beige, Black)

So there are 4*4*5*3*3 = 720 product variants. I know this is extreme, but I'm purely doing this to immediately test if my system will be able to handle it in a later phase.

So trimmed down I have:

Products

id
name

Properties

id
name

Thinking of RDBMS logic aside I would get a table like this, which of course isn't possible as it would constantly add properties and adds tons of nulls:

Productvariants

 id
 product_id
 property_1_id
 property_2_id
 property_3_id
...etc.

So this is a bit harder than a usual Color/Size combination you normally have on a product.

Now I can fake this methodology and just dump a serialization of Property ID's in one field on the Productvariants, like so:

Productvariants

id
product_id
properties {1, 2, 3}

.. and then split them in my ORM, but I'm afraid this will greatly increase the needed queries in the future (with tens of thousands of product variants) and optimization doesn't seem possible anymore that way.

Does anyone have experience with this and how to maybe intelligently do this? I'm using MySQL.


texts:
This is the initial query:

SELECT
    DISTINCT clienti.id_cliente AS cliente_id , clienti.cognome AS cognome,clienti.nome AS nome
FROM
    clienti
WHERE
     clienti.id_agenzia = 5782 AND clienti.deleted = 0 AND (clienti.cognome LIKE '%marco%' OR clienti.nome LIKE '%marco%' OR clienti.ragione_sociale LIKE '%marco%' OR clienti.indirizzo LIKE '%marco%' OR clienti.email LIKE '%marco%' OR clienti.email2 LIKE '%marco%' OR clienti.email_pec LIKE '%marco%' OR clienti.note LIKE '%marco%' OR clienti.tel LIKE '%marco%' OR clienti.tel2 LIKE '%marco%' OR clienti.cell LIKE '%marco%' OR clienti.fax LIKE '%marco%') ORDER BY cognome ASC, nome ASC, cliente_id LIMIT 0, 500

which takes 5.7 seconds on my VPS server (16 GB Ram, 8 cores). Table "clienti" contains 2.8 Milion recors.

this is the explain results:

id  select_type     table       partitions  type    possible_keys    key                                                  key_len   ref     rows    filtered    Extra
1   SIMPLE          clienti     NULL        index   id_agenzia-id_agente-external_id_cliente,cognome-n...   cognome-nome    306     NULL    22223   0.28        Using where

I try this:

SELECT cliente_id, cognome, nome FROM (
    SELECT
        DISTINCT clienti.id_cliente AS cliente_id , clienti.cognome AS cognome,clienti.nome AS nome
    FROM
        clienti
    WHERE
         clienti.id_agenzia = 5782 AND clienti.deleted = 0 AND (clienti.cognome LIKE '%marco%' OR clienti.nome LIKE '%marco%' OR clienti.ragione_sociale LIKE '%marco%' OR clienti.indirizzo LIKE '%marco%' OR clienti.email LIKE '%marco%' OR clienti.email2 LIKE '%marco%' OR clienti.email_pec LIKE '%marco%' OR clienti.note LIKE '%marco%' OR clienti.tel LIKE '%marco%' OR clienti.tel2 LIKE '%marco%' OR clienti.cell LIKE '%marco%' OR clienti.fax LIKE '%marco%') ORDER BY cognome ASC, nome ASC, cliente_id LIMIT 0, 500 ) AS tb1

explain:

id  select_type     table   partitions  type    possible_keys                                           key            key_len  ref     rows    filtered    Extra
1   PRIMARY     <derived2>  NULL        ALL     NULL                                                      NULL           NULL   NULL    500     100.00  NULL
2   DERIVED     clienti     NULL        index   id_agenzia-id_agente-external_id_cliente,cognome-n...   cognome-nome    306     NULL    22223   0.28        Using where

nothing changes.

Then I try:

SELECT cliente_id, cognome, nome FROM (
    SELECT
        DISTINCT clienti.id_cliente AS cliente_id , clienti.cognome AS cognome,clienti.nome AS nome
    FROM
        clienti
    WHERE
         clienti.id_agenzia = 5782 AND clienti.deleted = 0 AND (clienti.cognome LIKE '%marco%' OR clienti.nome LIKE '%marco%' OR clienti.ragione_sociale LIKE '%marco%' OR clienti.indirizzo LIKE '%marco%' OR clienti.email LIKE '%marco%' OR clienti.email2 LIKE '%marco%' OR clienti.email_pec LIKE '%marco%' OR clienti.note LIKE '%marco%' OR clienti.tel LIKE '%marco%' OR clienti.tel2 LIKE '%marco%' OR clienti.cell LIKE '%marco%' OR clienti.fax LIKE '%marco%')
ORDER BY cognome ASC, nome ASC, cliente_id

) AS tb1
LIMIT 0, 500

explain:

id  select_type     table   partitions  type    possible_keys                                           key                                         key_len     ref     rows    filtered    Extra
1   PRIMARY     <derived2>  NULL        ALL     NULL                                                    NULL                                        NULL        NULL    4568    100.00      NULL
2   DERIVED     clienti     NULL        ref     id_agenzia-id_agente-external_id_cliente,cognome-n...   id_agenzia-id_agente-flag_storico-deleted   4   const   60384   7.57    Using index condition; Using where; Using filesort

it takes 0.0975 seconds. Unbelievable

Someone could help me understand why those improvements happenend?


texts: How can I UPDATE multiple ROWs in a Single Query with multiple WHERE clauses?

texts:
How can I simplify these Queries

UPDATE `table` SET `col1` = 'abc', `col2` = 'xyz' WHERE `col3` = '1';
UPDATE `elbat` SET `col1` = `a`, `col2` = 'x' WHERE `col3` = '1';
UPDATE `elbat` SET `col1` = `b`, `col2` = 'y' WHERE `col3` = '2';
UPDATE `elbat` SET `col1` = `c`, `col2` = 'z' WHERE `col3` = '3';

Into something like this?

UPDATE `table` a, `elbat` b
SET a.`col1` = 'abc', a.`col2` = 'xyz' WHERE a.`col3` = '1',
SET b.`col1` = `a`, b.`col2` = 'x' WHERE b.`col3` = '1',
SET b.`col1` = `b`, b.`col2` = 'y' WHERE b.`col3` = '2',
SET b.`col1` = `c`, b.`col2` = 'z' WHERE b.`col3` = '3';

to run a Single-Query instead of Four-Queries


texts: How do I remove the excess white space from the values in my SQL table?

texts:
still kinda new to SQL.

My problem is this, say I created a table with one of its columns at nchar(200). This column contains the value 'Logged in.'. Every time I called it from the front-end, it returns 'Logged in                                                  '. Is there any way to remove the white space within the table? Or is there some setting that will remove this excess white space? I want it to return 'Logged in.' only. L/Rtrim don't seem to work.

Thanks


texts: What does changing Max Server Memory do besides clear the plan cache and (obviously) alter the memory setting)?

texts:
Running SQL Server 2012 SP3 on 32 GB ram and 4 cores, 60-80 concurrent connections, with a largely ad-hoc workload, we are seeing the SQL Server process (CPU) spike and remain spiked once or twice per day at unpredictable times. We are working on identifying the root cause of the spike. In the meantime, we've found that altering the Max Memory setting (either up or down) seems to be the only thing that will return the CPU load to normal.

Checking logs, and searching StackExchange (https://dba.stackexchange.com/a/183276) we see that the plan cache is being flushed by changing the Max Memory setting. However, if we flush the plan cache via DBCC FREESYSTEMCACHE('SQL Plans'), the CPU load doesn't return to normal.

Since changing the Max Memory setting resolves the issue no matter weather we increase or decrease it, the issue doesn't seem to be related directly to the Max Server Memory setting. As such, we are trying to understand what else changing the memory setting does, and then use that information to help identify the root cause of our CPU spike.


texts: What happens to an insert when a mysql(innoDB) table is being altered(locked)?

texts:
I am trying to alter a database column by using:

ALTER TABLE [table name] MODIFY id bigint;

I searched and found out that the table gets locked during the ALTER process.

How do the inserts get affected during the ALTER process? Do they wait until the lock is released? Is there a timeout?

If the inserts wait for the lock to be released, is there a limit of how many inserts that could wait?


texts: Why do these concurrent MySQL inserts deadlock?

texts:
I am baffled by this deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
150511 18:42:43
*** (1) TRANSACTION:
TRANSACTION B8972CA4, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 55103359, OS thread handle 0x7f6babefb700, query id 18803072369 db.example.com 127.0.0.1 application update
INSERT INTO users ( created_at, external_record_type, external_id, email ) VALUES ( '2015-05-11 18:42:43', 'person', '212999172', 'user1@example.com' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA4 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION B8972CA5, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 55103302, OS thread handle 0x7f6bf2320700, query id 18803072370 db.example.com 127.0.0.1 application update
INSERT INTO users ( created_at, external_record_type, external_id, email ) VALUES ( '2015-05-11 18:42:43', 'person', '212999170', 'user2@example.com' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA5 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA5 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

The schema:

CREATE TABLE users
(id int NOT NULL AUTO_INCREMENT,
 external_id varchar(255) NOT NULL,
 external_record_type varchar(255) NOT NULL,
 email varchar(255) NOT NULL,
 created_at datetime DEFAULT NULL,
 PRIMARY KEY(id),
 UNIQUE KEY index_users_on_external_record_type_and_external_id (external_record_type(8), external_id),
 KEY index_users_on_email (email))

I see that T1 and T2 both want the insert intention gap lock on the same gap, but I don't see why the transactions can't proceed sequentially. Moreover, I don't see why T2 acquires a non-insert-intention lock on the gap before trying for the insert-intention lock.

I'm using MySQL 5.5 of the Percona flavor.


texts: INSERT INTO #temp taking 1000 times longer than SELECT INTO #temp

texts:
I am shredding an xml blob that is being passed to me and loading a temp table for further processing.  One of the things I noticed while working through the process is that performing an INSERT INTO is taking like 20 minutes while doing a SELECT INTO is taking seconds.  The DB statistics are up to date and we are running daily maintenance plans so not sure what is going on.  I know that SELECT INTO bypasses the logs which would make it faster, but I as not assuming that the INSERT INTO would be so much worse.

EDIT: Based on comments adding some more details

Why would the INSERT INTO be taking so much longer than the SELECT INTO for a temp table.

Query example below.  I am shredding XML and inserting into a temp Table I have both the INSERT INTO and SELECT INTO commented out.  The INSERT INTO for just 100 records is showing 382,000+ logical reads while the SELECT INTO is showing none.

;with cteScen AS (
SELECT    DENSE_RANK() OVER ( ORDER BY scenario_node ) AS scenario ,
        RANK() OVER ( PARTITION BY scenario_node ORDER BY scen_val ) AS level_order ,
        scen_val.value('text-value[1]',
                       'varchar(100)') AS 'scen_value' ,
        scen_val.value('(distribution/@id)[1]',
                       'int') AS 'dist'
FROM      @scenarioXML.nodes('/scenarios/scenario')
        AS scenario ( scenario_node )
        CROSS APPLY scenario.scenario_node.nodes('./values/value')
        AS x ( scen_val ))
--INSERT INTO #tmpTbl(Scenario, Level_Order, Scenario_Value, Distribution_ID)
Select top 100 *
--INTO #tmpTbl
From cteScen

Any thoughts?


texts: Do Selection attributes have to appear in Projection list in Relational Algebra

texts:
In extended relational algebra (aggregation being allowed) do you have to project all attributes that you use in a statement? So for example in the query below the attributes A.FO_ID and A.Arrival are not in the projection. Is this valid?

π A.Arrival_Airport, A.Arrival_Airport_Name ρ A (OF_OFFER) ⨝ A.FO_ID =
Find_Max.FO_ID and A.Arrival = Find_Max.Max_Arrival_Time ρ
Find_Max (π B.FO_ID, Max_Arrival_Time γ B.FO_ID; MAX(B.Arrival) →
Max_Arrival_Time ρ B (OF_OFFER))


texts: How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

texts:
I just switched my Wordpress databases to use the InnoDB engine.

When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work.

I manually ran OPTIMIZE TABLE and the suggested ALTER TABLE but none of them removed the overhead. I ran mysqltuner on my server which showed 14 fragmented tables - I have a total of 14 tables.  Even after running optimize and alter tables, all 14 tables are still fragmented!

How can I properly OPTIMIZE and REPAIR InnoDB tables?


texts: Issue with SQL Server Aliases on a multi-instance installation

texts:
I've a 64-bit installation of SQL 2008 R2 SP2 on Server 2008 R2, with five instances of SQL.

I can get the aliases set up to work fine on the server (i.e. if I remote onto the server, then it works without problems, but from the client machine, there is no joy).

Errors:

SQL Pipes Error 40, Error 53.

Or, if specify port 1433, TCP Error 0, 11004.

If I specify port 63769 (which is mentioned as a port that SQL is listening on), TCP Error 0, 11004.

If I connect using the servername\instancename I have no problems.

I've set up the alias in 32-bit and 64-bit on the server. I've left the ports as dynamic in the TCP settings, because if I define the port as being 1433 for each instance, none of them will start. This is weird, as it's an approach I've used successfully on other servers.

There is no internal firewall, but some ports may, or may not, be shut off...

Any ideas about what to try next?

Thanks!


texts: SQL Server: cold backup, shutting down services?

texts:
One of our collagues told me that they are doing "cold" backups for SQL Server so that they are shutting down services and then taking a backup somehow.  I think this is all wrong! Am I right? I think that they should take full bakcups, differential backups and log backups, while their databases are on FULL mode.

They are also having a problem when they start SQL Server services after a backup, they cannot find .mdf files. Do you have any idea why this is happening to them?


texts: How to monitor general performance and wellbeing of a database?

texts:
I've read @Mike answer to How to tell if database tuning is fruitful (PostgreSQL), and that's the kind of approach I'm thinking about for monitoring the performance of my database (of a web application, many connections, heavy writes and reads all the time, very dynamic data).
The approach sums up like this: I want to know if my database can keep up with the workload, if it has the same readiness over time, and ultimately if my tuning and cleaning is useful. So for a general wellbeing indicator, monitoring the query time (for representative queries used by the web application and data flows) seams sensible and compact to me (query time increases without substantial changes => performance is degrading).
But my question is, is it?
Besides the question linked I've found no other source for such an approach, and I have still some holes on how to implement all this, so knowing more from other examples/experiences or manuals would be very useful. Even knowing that this approach is not worth doing would be interesting.
And FYI, I've read about pgbench, but it seams so aseptic to me, I mean, it surely gives a reference point, but what it says about my tables, my indexes, about the alive environment that is my database? (I hope it makes sense to you what I mean with that)
Maybe I'm looking at pgbench in the wrong way, but that's exactly what I want to find out!
Note: I work with Postgres, so I Postgres specific answer would be awesome... but not necessary!


texts: Using OPENDATASOURCE to insert mdb data into SQL Server

texts:
I tried inserting data from mdb file to 32 bit SQL Server using below code:

USE [master]
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

    USE [ImportTest]
    GO

INSERT INTO [dbo].[PROJECT_IDENTITY]
           ([Licencee_Name]
           ,[JURISDICTION])
        SELECT * FROM OPENDATASOURCE(
        'Microsoft.ACE.OLEDB.12.0',
        'Data Source="C:\\CCNTest.mdb"')...PROJECT_IDENTITY;

The Error Message I got was:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 23
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Any ideas on how to solve this msg?

Thanks


texts: Linked Server already exists in sys.server

texts:
I created Linked Server in ABC server and I can test the connection successfully to Destination server XYZ (Which is part of AOAG).
I created a Linked server from ABC --> XYZ .
I used AOAG Listener (AOAGListener) as server name while creating Linked server from ABC --> XYZ.

I am facing issue :

  Could not find server 'AOAGListener' in sys.servers.Verify that the
  correct server name was specified.If necessary,execute the stored
  procedure sp_addlinkedserver to add server to sys.servers.

Please help as I can see server name in sys.servers


texts:
I have a SQL Table that houses the date that a student was assigned and completes one of three exams.  I am in need of a way to query the table and return the column name of the exam the student needs to take and the date it was assigned.

For example, these are my desired query results:

Joe - exam1date - 01-30-2018
James - exam2date - 03-02-2018
Javier - exam3date - 04-01-2018

And this is DDL for my table:

Create Table Information
(
   fname varchar(100)
  ,exam1date date
  ,exam1complete date
  ,exam2date date
  ,exam2complete date
  ,exam3date date
  ,exam3complete date
)

Insert Into Information (fname, exam1date)
Values ('joe', '2018-01-30')
INSERT INTO Information (fname, exam1date, exam1complete, exam2date)
Values ('james', '2018-02-14', '2018-02-21', '2018-03-02')
INSERT INTO Information (fname, exam1date, exam1complete, exam2date, exam2complete, exam3date)
VALUES ('javier', '2018-01-01', '2018-01-14', '2018-03-01', '2018-03-12', '2018-04-01')

What would a query be that can returned my desired result set from the above table schema?


texts: Need help with SQL Server 2014 and disaster recovery

texts:
I am tasked with building a highly available SQL Server 2014 Enterprise environment for our new policy admin system.  This database will eventually grow to 1TB +.

Background

Our normal method of disaster recovery is that we replicate our storage to a second array at our DR facility.  During a disaster we use a product called Site Recovery Manager by VMWare that mounts the storage LUNS from the replicated SAN, reconfigures the VM’s with local IP addresses and then starts them on the DR network.

I have several products that use SQL Server and work very well with this solution.  I am able to bring up the databases and the application servers on the new network without issue.

Problem

I have several requirements for this new environment that I am not sure how to handle.

We have a requirement for a read only copy of the database for our data warehouse reporting.
They would like very up to date data, so nightly backup is not an option.

Based on some reading it sounds like log shipping is not going to be an option as users will get kicked out when restoring logs.
I have been looking at Always On Availability Groups, but I don’t believe they will work with our current disaster recovery mechanism as all the inter server communication is bases off IP addresses and not DNS names, so when we re-IP the servers and bring them up on the DR network nothing is going to work.

I have also considered an Availability Group with a replica located at our DR facility but not sure how everything would work during a DR test and once we want to return to normal working mode.  We do have domain controllers in DR and we simply shutdown the real one and restore one with our backup and use it during the DR test.  Once we are done with the test we shut down the restored DC and bring the real one back up.

Question

Does SQL Server 2014 support read only copies without Availability Groups and if so will users loose connectivity while replication is occurring?  If it does then I can hopefully use this method and then I believe our DR method will work.  That is if I can use names when configuring read only and not IP addresses.

Any direction would be much appreciated.


texts: How to use OpenLDAP for Oracle database user authentication

texts:
In our enterprise, we have a large number of Oracle databases and (obviously) large number of users with access to different databases. We would like to ease the procedure to create/drop/modify users on an enterprise level. A LDAP solution comes to mind but the solutions from Oracle Corp are too expensive. Needless to say, my mgmt asked me to research and come up with an alternative. I read about OpenLDAP. My questions:

How do I setup OpenLDAP for oracle user security authentication ?
Can I use OpenLDAP as a standalone or does it have to be integrated with any Oracle product like OID, OVD, ODSEE etc?
Are there are any certified install procedures for setting up OpenLDAP w/ Oracle database?

I didn't find anything on the internet for setting up OpenLDAP. Any help/pointer would be appreciated. The goal is to avoid any products from Oracle Corp purely for cost reasons.

Thanks


texts:
I'm trying to run mongodb 2.6, everything followed my 2.4 version setup steps.
but when I start mongos I got the following problems and just cannot make it.

2014-04-24T03:38:31.001+0800 [mongosMain] SyncClusterConnection connecting to [192.168.0.1:27018]
2014-04-24T03:38:31.002+0800 [mongosMain] SyncClusterConnection connecting to [192.168.0.2:27018]
2014-04-24T03:38:31.002+0800 [mongosMain] SyncClusterConnection connecting to [192.168.0.3:27018]
2014-04-24T03:38:31.066+0800 [mongosMain] scoped connection to 192.168.0.1:27018,192.168.0.2:27018,192.168.0.3:27018 not being returned to the pool
2014-04-24T03:38:42.183+0800 [mongosMain] waited 11s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:38:53.288+0800 [mongosMain] waited 22s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:39:04.394+0800 [mongosMain] waited 33s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:39:15.500+0800 [mongosMain] waited 44s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:39:26.604+0800 [mongosMain] waited 55s for distributed lock configUpgrade for upgrading config database to new format v5
2014-04-24T03:39:37.709+0800 [mongosMain] waited 66s for distributed lock configUpgrade for upgrading config database to new format v5

Waited for new format v5?
What's that?


texts:
I'm building an application with MySql DB.

I want to create a simple feed that will look like

Receiver_Id | Sender_Id | Feed_Message | Time

example for output:

row:
1234 | 5678 | User has joined your event | 2014-08-01 15:15

output:
user 5678 has joined your event at 2014-08-01 15:15

It's actually very simple, but the i'm worry that this is a bad practice, since soon my feed table will have millions of records which will be mostly inefficient.

Any idea how to do it right ?


texts: Convert string numeric values with comma as decimal separator to NUMERIC(10, 2)

texts:
I have an SQL table of varchar columns which contain Greek formatted numbers (. as thousand separator and comma as decimal separator)

The classic conversion

CONVERT(numeric(10,2),REPLACE([value],',','.'))

does not work because the . (thousand separator) kills the conversion

E.g try

CONVERT(numeric(10,2),REPLACE('7.000,45',',','.'))

I want to convert such values to numeric(10,2)

Any suggestions of how to handle it?


texts:
How can i automate DIfferential restore using dbatools Powershell
I have configured FUllbackup migration from powershell using dbatools and using multi thread I am able to restore 4.5TB under 4 hours.
As for the downtime I want to also automate the Differential Restore using powershell.
Is this possible?


texts: How to check whether old MDF and LDF files are referenced

texts:
I inherited a setup, and I'm rather new with DB work. I've got a drive on my production server that was created during a big crisis a few years ago at my company. Crisis was navigated and everything restored. But this drive on my has 70 LDF and MDF files sitting in it from that recovery. Is there a quick and easy way to determine if any of these things are being referenced or used? I just don't know what the last DBA here might have used them for afterward.

The Date Modified dates are all from the crisis period, early 2016. Nothing since.

Thanks.


texts:
As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like StackExchange) when working on databases/SQL. But there's always something I do and I'm never sure about such as security & perfomance in large environments.

If a DBA were to fully design/create a database/schema from scratch and maintain it from then on, what do you expect a DBA know? What are things a DBA should never do, or always ensure it is done? What have you experienced in a professional environment that education never prepared you for?


texts: Hands-off Scripted Install of MySQL 5.7 on Linux

texts:
I'm a long time user of MySQL, and have historically used Puppet or Ansible to install it onto boxes. I can automatically remove the test accounts/db, set a root password, add some application and monitoring users and databases and then hand the system over for application deployments, knowing everything's setup and ready to go for them.

Since MySQL 5.7, I'm not sure how to approach this. On installation it generates a random root password which it writes into a log file. Obviously, getting that out and working with the restricted account to get it set to the required password is difficult with a script (or config management system).

I've had a go at hacking the mysqld_pre_systemd script (on Centos 7) and have that create the data files, then start up mysql with a --init-file option, which I point to a bit of SQL that sets the password as I want. Once it's running, I then connect to it and run 'SHUTDOWN' to stop it, then let systemd carry on as it was to start it up normally.

This all seems like a crazy amount of work (although quite possible with Ansible etc). As I've now got to get it going on Centos 6, is there a better way?

One thought: Is there a way to specify the root password before installation?


texts: MySQL only supported on Windows - 3rd party vendor

texts:
We have a 3rd party vendor currently indicating that their application only supports MySQL on Windows, and cannot work with MySQL on Linux or RDS.
As far as I know, MySQL is MySQL, it doesn't matter what OS it is on (and it shouldn't), and I know it supports Windows Auth on Linux as well, if necessary.  I've looked, but I can find no evidence that there is a difference between them.
Unless this vendor has put something in their application that cares about the OS that the database platform is on, is there any other reason why they would believe this to be the case?
Thanks!


texts: Database replication and consistency check

texts:
I got a question about two databases (in Oracle 10g) that I have, let's call them A and B. A have some information (in various tables) and I want B to get a partial copy of some tables from A, and constantly check changes in A and 'sync' them in B.

I want to ask you about some method, technique or maybe ideas, knowing that I can't make any change in A (just selects, no triggers).

I thank you help and patience (for possible edits) in advance.

Additional information:

Thanks for the answers, I don't know if it's relevant, but I found the MINUS operator, though I'm not sure if it works with a "sub table" (select).


texts: Full Text Index STOP POPULATION doesn't work, depending on Track Changes setting

texts:
I had a Full Text Index population going on for too much time and storage, so I decided to stop it, but the population didn't stop.
Notes:

Both index and catalog are created using the Full Text Indexing Wizard (right-click on table name > Full Text Index > Define Full-Text Index).
I reproduced the problem several times (and still can), and the following describes what I experienced with the different Track Changes options: automatic, manual, and no tracking.
Dropping the index and the catalog works without no problem.

Track Changes: Automatically

I first tried to stop it using:
Right-click on table > Full-Text Index > Stop Population

Which popped up a success window, while actually the population continued (confirmed by executing one of the queries posted here, and exec sp_spaceused MyTable which constantly showed a growing storage usage).

I then tried executing:
ALTER FULLTEXT INDEX ON MyTable STOP POPULATION

Which failed with the following error:
Warning: Full-text auto propagation is on. Stop crawl request is ignored.

Although it's only a "warning", the command was in fact ignored, and the population continued.

I then tried another option:
EXEC sp_fulltext_catalog 'MyFullTextCatalog', 'stop'

Which resulted in the same warning.

Track Changes: Manually
I followed the exact three steps above, but the results were somewhat different:

For step 1, I again got a success window, while population still continued.

For step 2, I got an error:
Invalid object name 'MyFullTextCatalog'.

For step 3, I got a warning:
Warning: The ongoing population is necessary to ensure an up-to-date index. If needed, stop change tracking first, and then deactivate the full-text index population.

Track Changes: None (success!)
I followed the exact three steps above, but now experienced success:

For step 1, I got a success window, and the population actually stopped.

For step 2 (after I recreated the index using the wizard), I got the same error as above:
Invalid object name 'MyFullTextCatalog'.

For step 3 (after I recreated the index using the wizard), I got a success message, and the population did stop.

Question
What's causing the warnings\errors which prevent population from stopping (well, at least for auto and manual tracking)?


texts:
I had a table of names, containing 200,000 rows. This has now been updated to include over 11 million rows.

The table is queried using a SOUNDS LIKE 'str' and the result is then passed through levenshtein_ratio() [a custom function that compares string similarity]. This worked quickly with the 200,000 rows, but with 11 million rows it is now taking two seconds.

I believe I can make this work a lot quicker by saving the soundex of each name, indexing that column, and then searching by this. But am concerned that the levenshtein_ratio() will be slow as it is now being few with potentially 2,000-3,000 result.

I am interested to know how database professional would consider approaching this issue. In particular, I am wondering if it might be better to compute the levenshtein_ratio in PHP.


texts:
All,
I am currently putting together a knowledge based article that we can hand to our helpdesk to run through in order to do some preliminary troubleshooting or information gathering. One of the things I did was create the ability (thanks to Brent, https://www.brentozar.com/askbrent) to provide non-sysadmin's the ability to run sp_whoisactive to output some details they could provide to dbas like myself in the ticket. The problem I am running into is I want to exclude "sql_text" from results, but I cannot seem to figure out how?
I understand you have the ability to filter the output when it goes to a table (@output_column_list param) but I am not having the agents export it to a table. Initially, I was under the impression that the "@output_column_list" was where I could adjust the columns I wanted to see in the results, but it is clearly only a parameter used to filter the output to a table. In my desperate attempt, I tried the above parameter anyways and would leave out "sql_text" from the output, but all that did was reposition the column order.
EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'

Therefore, I want them to be able to copy the contents (with headers) to an excel sheet to attach. The less complicated work, the better as I fear if I provide the help desk with too much info it will confuse them.
Am I missing something? They will not have the ability to run any jobs or even look at logs, so their rights are already VERY limited.


texts: Can I query from two databases if my web application is connected to one database?

texts:
I have a local database, DB1, that I created and is connected to my web application. My web application takes in stored procedures from DB1 and queries them. My application connects to SQL Server using my Windows Authentication account.

I have access to another database, DB2, on a different server that I have read only access to using the same windows account. Can I still query from DB2 if my application is only connected to DB1?

When I execute stored procedures in DB1 that also query stuff from DB2 it works on SQL Server Management Studio but I get an error when I try to do it on my application.


texts: Best way to create relationship between multiple tables

texts:
I have a question about how to best handle a table that would be shared among multiple relationships, but shouldn't really be joined with all these tables. For example, suppose I have a table that has US States in it. Then, in this example, I have maybe three or four other tables that have a relationship with the States table, like: a Users table (where the user is from), a Car registration table, an Email table (maybe on this system we track sent and received emails and where they originate from) and some other table. If I joined all of these tables to the State table, it kind of makes it look like all of these table should have some sort of relationship with one-another. Maybe this is so for a few tables (Like User and Car registration) but not necessarily between all the tables (User and Email table for example). Or another example might be that we have two tables, Employees and Work_Office. Each has there own state. And each is joined to one-another, creating a loop (Work_Office has many Employees. Employee belongs to State and Work_Office belongs to State). Now we have a loop.

So, is it better to have multiple "State" tables? It doesn't seem like it would be. But at the same time, I don't want to create relationships that might not really exist? Or is it better to just have the tables but not actually create any sort of relationship?


texts: Oracle 12c fast refreshing nested materialized view problems

texts:
I have a couple of nested materialized views that fast refresh.  They worked fine in Oracle 11g but since upgrading to Oracle 12c (12.1.0.2.0 on Solaris) I am occasionally getting duplicate rows (the rows are not identical, usually some data has changed - I suspect the duplicates reflect the before and after change state).  If I subsequently do a complete refresh the duplicate rows disappear.

I have My Oracle Support access but I haven't been able to find any bugs that I can tie to this behaviour.

I am aware of new out-of-place refreshes that have been added to Oracle 12c.  The documentation says of the out-of-place functionality (edited for brevity).

  It helps to avoid potential problems such as ... intermediate refresh
  results being seen.

Are "intermediate refresh results" what I am seeing now? (I didn't before).

Is anyone aware of any relevant issues/bugs/patches?


texts: PostgreSQL WAL Archiving: Does having archive_mode set to 'on' without copying logs cause performance issues?

texts:
Using PostgreSQL 8.4/CentOS 6: if I set the archive_mode parameter to on in postgresql.conf, but don't actually store the generated WAL files anywhere via the archive_command parameter, is there any performance impact?

This is for testing the potential for use of WAL archiving on a high-throughput production server: I'm hoping to set archive_mode to on, and set archive_command to something like

echo `date` %f %p >> archive_log.txt

so that I can monitor how many WAL files would get created during a given time period without actually copying them and risking disk space overruns. Will this affect my server's performance in any way?


texts: Why does MyISAM support FULLTEXT Searching and InnoDB does not?

texts: I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess is because table storage format or the way that data is indexed. Sorry for my ignorance.


texts: Referencing another table from a computed column

texts:
I am working on a project where many of the entities in the data model do not have a name column, but instead the "name" needs to be constructed from a concatenation of multiple columns.  Initially I thought this might be a good use case for a computed column, as it would allow me to define this logic in the database and only once.

However with a fully normalized data model, the computed columns often require values from other tables.  Querying other tables can be accomplished through a UDF, but I have read in several places that using UDFs in computed columns prevents parallel execution (see here).

I could use views to handle the formatting of these names, but would like to define the logic only once, and if the logic is in a view it would likely result in nesting views, which can also cause problems with performance.

I'm hoping someone has an idea for how I might define this formatting logic only once in the database without causing a performance problem.


texts: pgAdmin4 version 3.1 how to update to the last version

texts:
We use the pgAdmin4 version 3.1 on Red Hat connecting to a server PostgresQL 9.6.
In the log of the server, I notice some errors generated by the pga4dash :

ERROR:  column "waiting" does not exist at character 193

Indeed, the column waiting has been renamed to wait_event in Postgresql 9.6.

I tried to upgrade the pgadmin4 package by using :

yum updgrade pgadmin4

The upgrade was realized without problem but it's still running the oldest version 3.1 instead of the new downloaded (after a reload of httpd daemon)

How can I do to use the newest pgadmin4 version downloaded? I search on the documentation of pgadmin and there are no notes about an upgrade of the pgAdmin4


texts:
We have a big issue in production environment, many tables were created with Int Auto-Inc Primary Key.

Adding to the fact that our insert mechanizes includes using Insert Ignore/Insert.. On Duplicate Update we have reached the Int.MaxValue In a few tables.

I am considering my options and would like to consult:

Switching to innodb_autoinc_lock_mode = 0
Alerting to BigInt in all tables auto-inc column
Switching all our application inserts to  Select + Insert/Update

I am considering option 1 as this is an On-Premise DB and the migration work here is very costly.

As far as I understand from my researching of using innodb_autoinc_lock_mode=0 is that it as somewhat of a performance downgrade when handling concurrent inserts. I am planning a benchmark to understand the performance difference, what do you guys think about this option?


texts: Is $IDENTITY documented and reliable in SQL Server?

texts:
I learned that SQL Server can return the value of the (single) identity column of a table using the pseudo-column $IDENTITY:

SELECT $IDENTITY FROM Table

Is this feature documented and reliable? The only official mention of it is on the IDENTITY page but it's buried in a code sample. This suggest that it might be intended to be undocumented. There are remarkably few Google matches for this feature as well.


texts: SQL Server 2016 - Parameter Sniffing Turned Off - Still seeing where a recompile improves performance

texts:
We are on SQL Server 2016 and have turned Parameter Sniffing off, since at times it caused major performance issues. I am seeing that a few stored procedures run in 30 secs, When they are re-compiled they run in 2 secs. It seems to be an awful lot like the behavior we saw with Parameter Sniffing.
Any ideas why this is still happening?

bill


texts: Optimizing ORDER BY for simple MySQL query

texts:
I'm trying to optimize this really simple query, and it gives me grief for a day now :(

Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a particular order. Here's the query:

SELECT * FROM `po`
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10

However, it runs really slow (half a second to 2 seconds). Here are table structures:

CREATE TABLE `po` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `supplier_id` (`supplier_id`)
) ENGINE=InnoDB AUTO_INCREMENT=457790 DEFAULT CHARSET=latin1

CREATE TABLE `po_suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9386 DEFAULT CHARSET=latin1

Running EXPLAIN reveals the issue:

+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref          | rows | Extra                                        |
+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | index | PRIMARY       | PRIMARY     | 4       | NULL         |  480 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | po    | ref   | supplier_id   | supplier_id | 4       | sergiis.s.id |    1 | Using index                                  |
+----+-------------+-------+-------+---------------+-------------+---------+--------------+------+----------------------------------------------+

Can you help me figure out how I can optimize this to run faster? I have index on the column I sort by. I have index on columns I join by. If I remove the JOIN - it's crazy fast. If I remove ORDER BY, it's crazy fast. Why am I getting this dreaded temporary + filesort?


texts: use variable to determine the name of a column in the select in postgre

texts:
I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it.
The old table is like this:

-- old table (OldTable)
name| col_a | col_b | col_c |
--------|---------|---------|--------|
ABC  |     0    |NULL |    1    |
DEF  |     1    |    1     |    1   |
GHI   | NULL |    1    |     0   |

And, new tables:
-- Table Users

ID | NAME
----| ---------
 1  | ABC
 2  | DEF
 3  | GHI

-- Table Rules
ID | RULE_NAME
--- | ------------------
 1  | col_a
 2  | col_b
 3  | col_c
 4  | col_d

-- Table UserRules
ID_USER | ID_RULE
------------- |-------------

So, I'm needing populate the table UserRules with the result from a select on TableMix where the user name is equals to the user name on table User and the value for column  in TableMix is equals 1. (select  from OldTable where OldTable.name = Users.name).
Well, I'm trying this:

DO $$
    DECLARE rules CURSOR FOR SELECT column_name FROM information_schema.columns
        WHERE table_schema = 'public' AND table_name   = 'OldTable'
        AND data_type = 'numeric' AND column_name NOT IN ('foo','bar');
        -- "foo" and "bar" are another numerc cols, but not define rules.
    DECLARE users CURSOR FOR SELECT name FROM public.Users;
    BEGIN
        FOR ruleName IN rules LOOP
            FOR userName IN users LOOP
                EXECUTE format('SELECT COALESCE(%I,col,$1) FROM public.OldTable
                    WHERE name = ''$2''', ruleName, username);
                -- insert on... populate the table Rules after get the id of the rule in table Rules if the result of select in OldTable equals 1 (or true if has a "where ... = 1")
            END LOOP;
        END LOOP;
END $$

After this, I don't have progress...
Someone help me? Thans!

UPDATE

This way I can get the real value:

DO $$
DECLARE
    rules CURSOR FOR SELECT column_name FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name   = 'oldtable' AND data_type = 'numeric'
    AND column_name NOT IN ('foo','bar');

    names CURSOR FOR SELECT username FROM public.users;

    res integer;
BEGIN
    FOR rulename IN rules LOOP
        EXECUTE format('SELECT %s FROM oldtable WHERE nome = %L', rulename, 'USER NAME')    INTO res;
    -- IF res > 0 THEN
       RAISE NOTICE '%', res;
    --END IF;
    END LOOP;
END $$

Result the real integer value.

But if I use a loop for usernames, it's doesn't work.

DO $$
DECLARE
    rules CURSOR FOR SELECT column_name FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name   = 'oldtable' AND data_type = 'numeric'
    AND column_name NOT IN ('foo','bar');

    names CURSOR FOR SELECT username FROM public.users;

    res integer;
BEGIN
    FOR name IN names LOOP
        FOR rulename IN rules LOOP
            EXECUTE format('SELECT %s FROM oldtable WHERE nome = %L', rulename, name)   INTO res;
        -- IF res > 0 THEN
           RAISE NOTICE '%', res;
        --END IF;
        END LOOP;
    END LOOP;
END $$

So, the resul is always .
Where am I going wrong?


texts: Diffrence between relational and unrelational database

texts: What is the difference between relational(MySql) and unrelational database(MongoDb)


texts: Is it safe to stop mysql maintenance check in progress?

texts:
I am using HeidiSQL and running maintenance checks on all tables in the database (mysql 5.5.46-0+deb7u1-log) using:

CHECK TABLE `database`.`table` QUICK FAST MEDIUM

It just goes through and runs that command on each table, one at a time. However, I need to restart the server. Is it safe to kill this check without corrupting anything?