texts: Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

texts:
Consider a table created like this:

CREATE TABLE `someTable` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` bigint(11) unsigned NOT NULL,
  `device_id` bigint(11) unsigned NOT NULL,
  `reviewed` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `owner_id reviewed` (`owner_id`,`reviewed`),
KEY `device_id reviewed` (`device_id`,`reviewed`)
);

This table records events that happen to a device which has an owner. There are ~1000 owners, each of which can have 1-100 devices. Each device can have millions of events in the table.

We need two queries to tell us:

Does a given owner_id have unreviewed events on any device?
Does a given device_id have unreviewed events?

We have been using:

SELECT 1 FROM someTable s WHERE s.reviewed = 0 AND s.owner_id = 0 LIMIT 1;
SELECT 1 FROM someTable s WHERE s.reviewed = 0 AND s.device_id = 0 LIMIT 1;

But we have found that as the number of events grows these queries are slowing down unexpectedly. Running EXPLAIN on them says that we will examine many rows. I admit that I was expecting that it would only need to examine 1 row. My thinking was that MySQL could look up the corresponding index ('owner_id reviewed' index or 'device_id reviewed' index) and tell immediately whether or not there were any corresponding rows.

I understand that EXPLAIN's rows column is just an estimate and that it may not be entirely accurate for queries with LIMIT 1, but I have found that the runtimes of these queries is increasing into many seconds (sometimes minutes) as the data set increases, which leads me to believe that MySQL is indeed examining more than one row.

What am I doing wrong here? How can I restructure my queries (or indexes) to do this efficiently?

We are running MySQL 5.6.21 on Linux. The table is innodb. I have created a simple SQL fiddle here: http://sqlfiddle.com/#!9/f0f4f1/1


texts:
I bought a new server with spec :
HP ProLiant ML310e Gen8 v2
8 GB Single Memory, Intel Xeon E3-1220v3 3,1 GHz, 500 GB SATA 7.5K RPM, Windows Server 2012 R2 x64, SQL Server Standard 2014 x64, RAID 0

Compared with my notebook :
ASUS X200CA
2 GB Single Memory, Intel Celeron Dual Core 1.5 GHz, 500 GB SATA 7.5K RPM, Windows 7 Ultimate x64, SQL Server Express 2014 x64, AHCI

I just compared this notebook because the different too much compared with my old server (old server faster). I've setting few configuration with my new server and getting a little better from this post SQL Server 2012 slower than 2008

Testing : (5 Tries)

select top 9000 * from faktur_detail

New Server :

CPU time = 20 ms,  elapsed time = 756 ms.
CPU time = 12 ms,  elapsed time = 720 ms.
CPU time = 8 ms,  elapsed time = 840 ms.
CPU time = 14 ms,  elapsed time = 689 ms.
CPU time = 17 ms,  elapsed time = 740 ms.

My Notebook :

CPU time = 250 ms,  elapsed time = 2403 ms.
CPU time = 172 ms,  elapsed time = 2541 ms.
CPU time = 219 ms,  elapsed time = 2848 ms.
CPU time = 140 ms,  elapsed time = 2426 ms.
CPU time = 172 ms,  elapsed time = 2410 ms.

I had try update, insert and other normal commands and showed that new server faster than my notebook. The problem is with Cursor :

Testing : (5 Tries)

declare @ketqty varchar (50),
        @ketqtyjual varchar (50),
        @ketqtybonus varchar (50),
        @kd_barang varchar (10),
        @kd_rec int,
        @qty numeric,
        @qtyjual numeric,
        @qtybonus numeric

  DECLARE @RPREKAPJUALALL_Cursor CURSOR
  SET @RPREKAPJUALALL_Cursor = CURSOR SCROLL DYNAMIC FOR

  select kd_rec, qtyjual , qtyBonus , Qty, kd_barang  from RPREKAPJUALALL where NamaKomputer  ='serverhp'

  OPEN @RPREKAPJUALALL_Cursor
  FETCH NEXT FROM @RPREKAPJUALALL_Cursor INTO @kd_rec, @qtyjual,@qtybonus, @qty, @kd_barang
  WHILE @@FETCH_STATUS = 0
  BEGIN
      EXEC sp_countsatuandetail  @KD_BARANG, @qty , @RESULT = @ketqty   OUTPUT
      EXEC sp_countsatuandetail  @KD_BARANG, @qtyjual  , @RESULT = @ketqtyjual    OUTPUT
      EXEC sp_countsatuandetail  @KD_BARANG, @qtybonus , @RESULT = @ketqtybonus   OUTPUT

      update RPREKAPJUALALL set ketqty =@ketqty , ketqtyjual  =@ketqtyjual ,ketqtybonus =@ketqtybonus where NamaKomputer ='serverhp' and kd_rec =@kd_rec

     FETCH NEXT FROM @RPREKAPJUALALL_Cursor INTO @kd_rec, @qtyjual,@qtybonus, @qty, @kd_barang
   END

  CLOSE @RPREKAPJUALALL_Cursor
  DEALLOCATE @RPREKAPJUALALL_Cursor

New Server :

86 seconds.
93 seconds.
85 seconds.
83 seconds.
81 seconds.

My Notebook :

16 seconds.
12 seconds.
14 seconds.
18 seconds.
16 seconds.

I know cursors is really bad for SQL Server, but I can't switched all cursors into normal update soon, because previous programmers left so many cursors in stored procedure.

What possible caused this problem? I've struggling with this 2 weeks now. I've been read so many articles. I come into conclusion that this type of server is not fit become Database Server, because compared with old server, this server slower about 2,8 times even with SELECT command.

OLD Server :
Intel Core i3, 1TB SATA HDD 7.5K RPM, 16GB Dual Memory, Windows 7 Ultimate x64, SQL Server 2008 Express x86.

For some reason, I must use this new server for now. Please help me with this Cursor problem for a while. This new server so slow for now even showing a week data. I can't imagine how this ended after 1 month, years.

Any Help will be appreciated. Thank you very much.


texts: SQL Server and Agent Services not starting automatic in AZURE IaaS

texts:
We migrated an on-premise database to azure IaaS and the machines is on between 7am and 7pm that way we save money in the night when the users are not querying the database, but every time the machine goes up at 7am sql server services and the agent don't get started automatically, we have tried setting the startup tyme to automatic and automatic(Delayed start) but it still not working, any ideas of what we should check to make this happen and not having to start the services manually?

thanks in advance


texts: What are the drawbacks with using UUID or GUID as a primary key?

texts:
I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this design since the UUID/GUID is quite large and they are almost random. The alternative is to use an auto-incremented INT or LONG.

What are the drawbacks with using UUID or GUID as a primary key for my tables?

I will probably use Derby/JavaDB (on the clients) and PostgreSQL (on the server) as DBMS.


texts:
I have the following script:

DROP TABLE IF EXISTS a;
CREATE TABLE a(
val   VARCHAR NOT NULL
);

DO
$do$
BEGIN
    INSERT INTO a(val) VALUES('a1');
    INSERT INTO a(val) VALUES('a2');
END;
$do$

When I place it in file (boo.sql) and execute it with:

psql -v --quiet -X -U some-user --no-password -d some-db -f boo.sql

... the table gets created and the rows are inserted.
When I try to wrap the anonymous block inside a transaction (so that I can explicitly set and control the transaction isolation level):

DROP TABLE IF EXISTS a;
CREATE TABLE a(
val   VARCHAR NOT NULL
);

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DO
$do$
BEGIN
    INSERT INTO a(val) VALUES('a1');
    INSERT INTO a(val) VALUES('a2');
END;
$do$
COMMIT;

... I get instead:

psql:boo.sql:16: ERROR:  syntax error at or near "COMMIT"
LINE 8:     COMMIT;
            ^

Am I doing something nonsensical? Is there a way to wrap a single or multiple anonymous blocks inside a transaction and if not, then on what transactions do these anonymous blocks get executed and how can I control its transaction isolation level?


texts: Convert multiple rows into column base on another column value

texts:
I have search through the answer but could not get what i'd like to have. my table is as below.

No  |    Name            | Relation
1   | A                  | P
1   | B                  | GO
1   | C                  | JB
2   | AH                 | P
3   | TY                 | P
3   | DO                 | JB
3   | DO1                | JB
3   | DO2                | JB
3   | DO3                | JB
3   | DO4                | JB
3   | DO5                | GO
3   | DO6                | GO
3   | DO7                | GO
3   | DO8                | GO
3   | DO9                | GO
3   | DO10               | GO
4   | PA                 | P
4   | PA2                | GO
4   | PA3                | GO
4   | PA4                | GO
4   | PA5                | GO
4   | PA6                | GO
4   | PA7                | GO

Each No will have in Relation only one 'P', max 5 'JB' and max 6 'GO'.

I'd like to get below result.

No  | P    | JB1  | JB2 | JB3 | JB4 | JB5 | GO1 | GO2 |GO3 |GO4 |GO5 |GO6 |
1   | A    | C    | NULL| NULL| NULL| NULL| B   | NULL|NULL|NULL|NULL|NULL|
2   | AH   | NULL | NULL| NULL| NULL| NULL| NULL| NULL|NULL|NULL|NULL|NULL|
3   | TY   | DO   | DO1 | DO2 | DO3 | DO4 | DO5 | DO6 |DO7 |DO8 |DO9 |DO10|
4   | PA   | NULL |NULL |NULL |NULL |NULL | PA2 | PA3 |PA4 |PA5 |PA6 |PA7 |


texts: Using old MySQL data folder on new MySQL installation

texts:
If I could have made a proper SQL dump/backup I would have, but OS gave out and could only log into SafeMode, where I couldn't start the MySQL server. Therefore, I could only manually backup the file directories.

I have a saved file copy of C:\ProgramData\MySQL\MySQL Server 8.0
I have a saved file copy of C:\Program Files\MySQL\MySQL Server 8.0

I'm now running on a new installation of Windows and trying to start this old server up for a few minutes so I can log on from Workbench and make a proper SQL database dump which will I import on a new MySQL installation later.

Consequently, moving the mentioned files in their respective locations - Program Files and ProgramData.

Opening up PowerShell (with Admin rights) and doing:

mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0" --console

and I get a few hundred lines on the screen that contain the following:

2020-04-10T05:28:56.725818Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2020-04-10T05:28:56.734460Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace 'innodb_undo_001' Page [page id: space=4294967279, page number=5] log sequence number 1339677380 is in the future! Current system log sequence number 19081380.

Followed by:

2020-04-10T05:28:56.740834Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2020-04-10T05:28:56.757259Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2020-04-10T05:28:56.769215Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2020-04-10T05:28:57.695386Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-10T05:28:57.765911Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.13'  socket: ''  port: 3306  MySQL Community Server - GPL.
2020-04-10T05:28:57.926850Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060

Great, at least its running.

I can now connect but no databases.

What can I do / how should I proceed to successfully get those databases back temporarily?

UPDATE:

Removed the innodb_undo_001 and innodb_undo_002 files from the Data directory and relaunched.

Getting this:

PS C:\Program Files\MySQL\MySQL Server 8.0\bin> .\mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --console                                                               2020-04-10T05:55:29.555478Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-04-10T05:55:29.558313Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.13) starting as process 3028
2020-04-10T05:55:31.394427Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 1, name 'sys/sys_config', file '.\sys\sys_config.ibd' is missing!
2020-04-10T05:55:32.808645Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-10T05:55:32.856154Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.13'  socket: ''  port: 3306  MySQL Community Server - GPL.
2020-04-10T05:55:32.898055Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060

Running but no databases present.


texts: SQL Server - monitor changes on linked server

texts:
We have a local SQL Server database which we have full control over, and in this local server we are linking a remote SQL Server database, to which we have only read access (no schema changes, no creation of triggers, stored procs, views, etc.).  I'm not sure if we are able to enable distributed transactions on the remote server, but I can check if that is important to know.

A 3rd party applies inserts/updates/deletes to the remote server tables, and we want to know when something changes in the remote server (ideally in real-time, but polling might be sufficient), so we can re-process some data in the local server.

What would be our main options for achieving this?  We would prefer lighter-weight solutions if possible (i.e. not SSIS - because none of us have really used SSIS).


texts: MySQL: can I put the database into a maintenance mode? (i.e. no user connections)

texts:
I'm going to be doing some complicated maintenance on our MySQL servers and I'd like to keep users out during the work.   I'd prefer not to drop and re-create all of the user accounts, but that's the only solution I've come up with so far.  Hmm..  Refusing network connections would work as well, as the maintenance will be done via an os-local mysql CLI.  I'd prefer to not have to stop and re-start the server, but that's not impossible.

What are best practices here?

Thanks in advance,
Brad


texts: How is the default tablespace determined when creating a table?

texts:
The Tablespace parameter is optional when creating tables.

Upon execution of a CREATE TABLE statement, Oracle assigns the default one if it was not defined.

In the default database there is a "USERS" tablespace.  If there were several tablespaces defined, which one would be assigned?


texts: How to prevent SSDT publishing from dropping columns

texts:
I want to create a publishing profile that DOES a complete schema comparison and publishing BUT does not drop any tables or any columns that have been removed between the old and new version.

I am aware of the BLOCK possible data loss option in the settings. AFAIK this stops the publishing process in case DATA will be lost. So the question is, will the schema upgrade process be interrupted (in the middle of nowhere?) or will it be completed but the affected tables will be excluded?

IF a table has added and removed columns, will the new columns be added to the schema although the blocking of dataloss option stops the schema update because of the potential data loss?

EDIT 09.08.2016:

I wanted to add those links to complement the question with additional information about the backgrounds:

MS Connect entry to this BUG
MS Social discussion about this BUG


texts: Update query with two different where conditions

texts:
I would like to set status to false for all rows except one, which should should be set to true. How can I achieve that?

Here is the query I tried:

UPDATE students set status = false where status = true and
set status = true where _id = 1;


texts: WAL log entry for CREATE TABLE statement

texts: In postgresql, for queries like INSERT/UPDATE/DELETE, a WAL record with required information about the tuples is created in the WAL buffer, before doing the required changes in the data buffer. Is it the same for UTILITY statements? I see an option, wal_level, to reduce the WAL entries for certain queries as in case of, create and drop table within a transaction itself. But what happens otherwise? Are the UTILITY statements logged? If so, what is the XLog record structure of those records? Thanks in advance


texts: Can I limit access to postgresql 8.4 databases via ODBC on the server side?

texts:
I have a bit of a tricky problem at the moment that I could really use some help to solve. I'll do my best to explain it but please feel free to ask questions :-)

We have a postgresql 8.4 database that our users typically connect to via a graphical interface that was written in Python many years ago.

Recently some users have been connecting to this database via ODBC and manipulating data via Microsoft Access. This has led to a number of accidental deletions of records and all sorts of troubles, so we wish to stop this from happening.

Here's the real clincher - Some users use MS Access via ODBC for reporting purposes, and we would like them to be able to continue doing that. So we would like to be able to restrict database access via ODBC to only "SELECT" queries.

Does anyone know if this can be achieved on the server side? I know that there is a "readonly" option in the ODBC driver settings on windows, but unfortunately many users have administrator access on their windows machines, so whatever security measures we put in place have to be done on the server side.


texts: Why doesn't pgAgent run my job every minute?

texts:
I have a job and I set the schedule to run every minute.

However, it always skips the next minute after finishing the process, even when the process ends in less than one minute.

The problem is that data is still arriving during that minute so when the job starts, it has to process two minutes of data. I'd rather have two 15 seconds runs, than one long 30 seconds run.

How can I make pgAgent run every minute?

In other cases when I set the schedule to every 3 minutes, pgAgent skips the 4th minute and starts on the 5th.


texts: Stuck in "query end" status every 5 minutes

texts:
I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about.

An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is the query:

UPDATE `user` SET
    `last_active` = NOW(),
    `last_ip` = '<ip>'
WHERE `name` = '<name>'
LIMIT 1

The table:

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  `last_ip` varchar(50) NOT NULL DEFAULT 'none',
  `last_active` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `last_active` (`last_active`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34003;

Usually the query takes ~0.2 ms, but every 5 minutes (accurately every 5 minutes, 10:00:12, 10:05:12, 10:10:12...) it takes over 250 ms, with all time spent with the status "query end".

Any idea what may be causing this? Why would one query run slow every 5 minutes? What is happening every 5 minutes that is affecting "query end"? No other processes are running.

MySQL version 5.5.44


texts:
I have a table just like attached image.

The query i am using to fetch the data is

SELECT  message_text
FROM    messages
WHERE   conversation_id = "69_P"
AND     message_type = "text"
AND     message_text LIKE "%http%"
LIMIT 3

With this query i get the data with links as well as text. So is there any way to grep only links that start with http or www and ends with .domain or any?

Result Output

https://www.1and1.com/
Test  https://www.google.com
Test message with link  https://momentjs.com/docs/

Desired output

https://www.1and1.com/
https://www.google.com
https://momentjs.com/docs/


texts: Enabling InnoDB on an existing MySQL production server

texts:
We have a MySQL 5.1.59 server that has 30-40 existing MyISAM databases. I need to deploy an application that requires InnoDB databases.

Can I just enable/configure InnoDB in /etc/my.cnf and restart MySQL, or is there more to this than meets the eye?

I should add that this is a MySQL environment built from source (running on CentOS 5.5 x64) and we plan to use the InnoDB plugin rather than the built-in InnoDB capability.

The plan is to follow steps as described on this page:

  http://dev.mysql.com/doc/refman/5.1/en/replacing-builtin-innodb.html

We also want to use innodb_file_per_table.

Apart from tuning parameters such as pool sizes and so on, is there anything else I should take into consideration?


texts: How to join a table with a table valued function?

texts:
I have a user defined function:

create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
  -- blah blah
end

Now I want to join this on another table, like so:

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f
join ut_FooFunc(f.ID, 1) u -- doesn't work
where f.SomeCriterion = 1

In other words, for all Foo records where SomeCriterion is 1, I want to see the Foo ID and Desc, alongside the values of Field1 and Field2 that are returned from ut_FooFunc for an input of Foo.ID.

What's the syntax to do this?


texts:
I am unsure if this has been asked already. Tried searching but couldn't find one.
I'll explain with a close example (I call it the tags problem). Consider a basic CMS. A post can have many tags and should have at least one. A tag can have more than one words. A tag, when entered by the user, where they can either select from a list of tags and if tag a tag is not in the system, it has to created by the user.
Now the main problem is replication. There are thousands of tags. Many tags get repeated because of their definition of tag (Eg: let's say a post should have a tag called organisation, but the user adds institution as a tag.). Some of them are spelling mistakes or plurals (like Afforestation, Afforestations and multiple afforestation).
This leads to some unfavorable analytic reports which is one of the main output of the system.
Some solutions I can think of,

A separate responsible user needs to add a tag. Problem with this is, human intervention (small factor), the user has to wait till the tag get approved, and when the amount of tags increase, that user will be unable to track/remember which are new tags.

The user will add the tags, and then a person has to review those tags and edit/merge them. Eg: A user adds a tag called dams, when there is already a tag called dam. This new tag will be accepted by the system and other users can also view/use those. But then a responsible person then can review the tag and have the ability to update the tag in all places where its used to appropriate tag. Same problems as above except one is solved.

What is your approach? How to deal with this kind of replication?
Apology if this is the wrong stack-exchange.


texts: Query Exceeding Query Timeout on Linked Server and Working

texts:
Currently we have a SQL Server Agent job that executes a stored procedure that reads data over a linked server.

Initially on our test server this was timing out at 10m:17s (617 seconds).  We changed the Query Timeout in Linked Server properties->Server Options to 3600 (1 hour) and the SQL Server agent job now works fine with the Agent job taking about 30 minutes.

When this was made live on Production, the Agent job continued to succeed however with the difference in data quantity the job now takes 1h:15m.  This is beyond the timeout setting.  My theory is that the stored procedure is 'finished' with the linked server within 3600 seconds and the extra time is spent processing locally.

My concern is why is this working? Or more to the point preventing it from ceasing to work if the data changes much.

I have researched a number of topics on this, but I haven't encountered any similar posts.


texts: Mongo database error in resource allocation

texts:
pthread_create failed: errno:11 Resource temporarily unavailable

maximum number of open files 64000
maximum number of processes 64000

It is taking 50seconds to complete query.

How to allocate more sources?


texts: Use xQuery to extract attribute values that also exist in SQL variable

texts:
XML structure:

<ns0:message xmlns:ns0='xxx:testing'>
  <ns0:field name='AAA'>...</ns0:field>
  <ns0:field name='BBB'>...</ns0:field>
  <ns0:field name='VVV'>...</ns0:field>
  <ns0:field name='CAR'>...</ns0:field>
</ns0:message>

I have SQL that will extract the values of the attributes titled name:

SELECT
  ( (SELECT ',' + CHAR(13) + P.N.value('@name', 'varchar(max)')
       FROM myTable.message_xml.nodes('declare namespace ns0="xxx:testing";
                                       ns0:message/ns0:field[ @name = "AAA" or
                                                              @name = "BBB"]') P(N)
        FOR XML PATH(''), type).value('substring(text()[1], 3)', 'varchar(max)')) as ATTRIBUTE_VALUES

This returns a column that looks like:

ATTRIBUTE_VALUES
---------------
AAA,
BBB

My problem is that the list of potential attribute values is quite large.
Instead of repeating @name = "AAA" in my query for every attribute value I want to check for, I was hoping I could declare it as a variable like:

DECLARE @ATTRIBUTES VarChar(Max)
SET @ATTRIBUTES = '(AAA,BBB,CAR,XYZ)'

And then just stick the variable in the sql like:

[@name = sql:variable("@ATTRIBUTES")]

but this is not working for any combination of parens,commas,etc I use to build the variable.


texts: Is execution plan cached “better” for stored procedures than for a non-dynamic query?

texts:
Reading different explanations about execution plan caching by Microsoft SQL Server, I'm confused about the benefits of using stored procedures instead of non-dynamic queries.

By a non-dynamic query I mean a fully parametrized query string which doesn't change through multiple invocations.

As I understand it:

The execution plan is cached both for a stored procedure and an ordinary query.
For a stored procedure, the execution plan is precomputed, which leads to a slight benefit over ordinary queries the first time the stored procedure is invoked.

The sources look rather contradictory to me:

Execution Plan Caching and Reuse article on MSDN doesn't make difference between parametrized queries and stored procedures. The subsections emphasize the importance of parametrized queries in order to make it easy for SQL Server to cache the execution plan.
SQL Server query execution plans – Basics claims the opposite (emphasis mine):

  When it comes to executing ad hoc queries, query plans are created based on complete code, so different parameters or any change in code will prevent reuse of the existing plan.

On DBA.StackExchange, the comment on an answer related to the benefits of stored procedures indicates that parametrized queries have exactly the same effect than stored procedures.

So, in the context where the execution plan is not thrown out of the cache and when, for the sake of experiment, I want to run billions of times a rather complicated query which would benefit from an execution plan and which takes one parameter which changes every time, would there be any benefit in terms of execution plan caching¹ to use stored procedures instead of an ordinary parametrized query?

¹ Outside the scope of the execution plan, there would be minor performance benefits of using a stored procedure, for example in terms of network footprint: passing the name of the stored procedure and its parameters is slightly better than passing the whole query. Those benefits are outside the scope of my question, which is purely about execution plan cache.


texts: Oracle TKPROF Insert data doesn't match text results

texts:
I'm trying to make sense of the output from a SQL Trace from an Oracle 12.2 database on Windows. I've used TKPROF to interpret the the resulting trace files. This utility generates a text file output and a SQL file output. But the data don't match up between the two files.

The data was generated using syntax like tkprof Trace.trc trace.trc.txt insert=trace.trc.sql

For a specific query, the TKPROF output via text file is:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     13      0.04       0.03          0          0          1           0
Fetch       13      0.00       0.07          6         26          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       27      0.04       0.11          6         26          1           0

But TKPROF's SQL output generates data for that query that doesn't have the same numbers for the Elapsed column.

Here's the Insert statement:

INSERT INTO tkprof_table (
    date_of_insert, cursor_num, depth, user_id,
    parse_cnt,   parse_cpu, parse_elap, parse_disk, parse_query, parse_current, parse_miss,
    exe_count,   exe_cpu,   exe_elap,   exe_disk,   exe_query,   exe_current,   exe_miss,  exe_rows,
    fetch_count, fetch_cpu, fetch_elap, fetch_disk, fetch_query, fetch_current, fetch_rows,
    ticks, sql_statement
    )
VALUES
(
  SYSDATE, 4, 0, 9,
  1, 0, 86, 0, 0, 0, 0,
  13,46875, 31424, 0, 0, 1, 2, 0,
  13, 0, 79075, 6, 26, 0, 0, 152918
, 'SELECT [...]')

And then converting those SQL columns to match the text file's table layout that becomes:

call     count       cpu    elapsed    disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0       86          0          0          0           0
Execute     13      46875   31424       0          0          1           0
Fetch       13      0       79075       6         26          0           0

Oracle's documentation says the TKPROF Elapsed values are

  "total elapsed time in seconds for all parse, execute, or fetch calls
  for the statement."

And I believe that's accurate for the text file output. But the SQL data must be some other measure. I can't find any documentation that defines a unit of measure for the mismatched data.

There's a "ticks" column, that appears to correspond to CPU ticks. But I don't know if that's what the mismatched values are too. If so, how do I convert that data to seconds?

Or it looks like these values may be microseconds. Is that correct? Is there anywhere that definitively defines that unit of measure?


texts: MySQL join to show room booking availability on different days per slot


texts: After a PC freeze (Windows 7) and disk integrity check, most data from PostgreSQL table was deleted

texts:
My development server is on a PC, using 64-bit Windows 7.  My PC has been randomly freezing on me lately, requiring a hard reboot (holding down power button until PC turns off)

Yesterday, as I was adding some data into my development server's database.  My PC froze again.  While I was turning the PC on again, Windows asked to check my disk integrity during the booting process.  During the check-up, a bunch of corrupted files were deleted.  After the check-up is complete, I started up my development server which asked me to reindex a table.  I did the re-indexing, and realized that my database of over 2 million rows now has only 12 thousand!

I'm guessing the PC freeze corrupted my table, and during the disk integrity check, Windows deleted all those data?  Is there some way for me to retrieve the lost data?


texts:
I have a stored procedure in production which started running slower all of a sudden. I did some investigation and found out that it is using a massive table which has 75 million rows and is growing. Looking at the plan it doesn't show any major missing indexes in the warning. This procedure used to take few minutes earlier however its taking 12 mins now.

Below is the query plan:

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

Any help is appreciated.


texts: Postgres foreign data wrappers weird join behavior

texts:
So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins.

Keep in mind the following snippets are just accessing the foreign database.

When I do:

SELECT *
FROM tds tr
INNER JOIN dq_infos dq ON tr.id = dq.id
WHERE tr.created_time >= '2020-04-22'

it runs very fast, however the result of this join will have duplicated columns so I can't really do anything with it.

When instead do:

SELECT tr.id
FROM tds tr
INNER JOIN dq_infos dq ON tr.id = dq.id
WHERE tr.created_time >= '2020-04-22'

it takes roughly 10 times as long.

I thought this can't be right so I looked at the query plan and it makes even less sense.

The query plan for the first query is:

Foreign Scan  (cost=100.00..177.23 rows=52 width=908) (actual time=398.276..30161.096 rows=449408 loops=1)
  Output: *
  Relations: (public.tds tr) INNER JOIN (public.dq_infos dq)
  Remote SQL: SELECT * FROM (public.tds r1 INNER JOIN public.dq_infos r2 ON (((r1.id = r2.id)) AND ((r1.created_time >= '2020-04-22 00:00:00'::timestamp without time zone))))
Planning Time: 0.605 ms
Execution Time: 30180.511 ms

(I replaced the full column list with * in this execution plan to make it easier to read)

And the query plan for the second query is:

Merge Join  (cost=580.61..799.37 rows=14259 width=4) (actual time=195048.232..195469.693 rows=449617 loops=1)
  Output: tr.id
  Merge Cond: (tr.id = dq.id)
  Buffers: shared hit=4, temp read=176695 written=178369
  ->  Sort  (cost=214.47..216.91 rows=975 width=4) (actual time=4195.650..4355.824 rows=449618 loops=1)
    Output: tr.id
    Sort Key: tr.id
    Sort Method: external merge  Disk: 6552kB
    Buffers: shared hit=4, temp read=2283 written=2375
    ->  Foreign Scan on public.tds tr  (cost=100.00..166.06 rows=975 width=4) (actual time=412.259..3815.536 rows=475864 loops=1)
      Output: tr.id
      Remote SQL: SELECT id FROM public.tds WHERE ((created_time >= '2020-04-22 00:00:00'::timestamp without time zone))
  ->  Sort  (cost=366.15..373.46 rows=2925 width=4) (actual time=188117.092..190081.599 rows=12577570 loops=1)
    Output: dq.id
    Sort Key: dq.id
    Sort Method: external sort  Disk: 221568kB
    Buffers: temp read=174412 written=175994
    ->  Foreign Scan on public.dq_infos dq  (cost=100.00..197.75 rows=2925 width=4) (actual time=1.041..33499.615 rows=12577570 loops=1)
      Output: dq.id
      Remote SQL: SELECT id FROM public.dq_infos
Planning Time: 0.311 ms
Execution Time: 195648.893 ms

This is clearly the reason for it being slower though I can't figure out why it would do this.


texts: mysql logs same query multiple times or is it application problem?

texts:
I have temporarily switched on logging on the server. The site is used in the US and local time there was 4 in the morning so not many people are using it at that time.

What I did was delete any logs (there weren't any):

truncate table mysql.general_log;

Activate logging:
SET global general_log = 1;
SET global log_output = 'table';

Open the page and as soon as it's loaded disable the log again:
SET global general_log = 0;

Then I wait one or two minutes so the mysql activity of the page are separated.
Then switched on log:
SET global general_log = 1;
Open page
Switched off log as soon as the page was loaded
SET global general_log = 0;

Opened 7 pages and have 54317 entries in the log both on the server and after I dumped it and imported it locally.

mysqldump --lock-tables=0 -u root -p..... mysql general_log > log.sql

I was wondering if something could have caused repeatedly logging a query that was actually performed only once. It is unlikely though because the following:

SELECT *
FROM `general_log`
WHERE argument LIKE 'SET global general_log%'
ORDER BY event_time ASC
LIMIT 0 , 30

Gives me only 8 entries. I know the application was written by someone early in their career and it's using Joomla 1.5 but thousands of queries for one page visit seems a bit much to me.

These are logged by mysql so they are actually requested by the application, I suspect mysql sees the queries are the same so won't actually search or the site would have crashed a long time ago.

Server version: 5.1.73-cll MySQL Community Server (GPLv2)

So the question is:

Could mysql be logging queries multiple times that are actually only made once by the application?


texts: What are the possible considerations and drawbacks of storing binary image data directly in a table?

texts:
Although related to this question, I have a slightly different angle to approach this issue from. Here is my situation:

I am writing a web application (either in PHP or Python) that manages addons for a desktop application. Users can browse addons, install them, upload them, etc.

I am planning the schema for the database and found myself with a decision to make:

  Is it better to store icons (for the addons) in the table itself or instead store them in the filesystem and simply store a filename in the table?

The icons are small (48x48 or close to that) and likely won't take up more than 5 or 6 KB at the most. Are there any serious drawbacks to storing the image data in the table? Are there other implications that I should be aware of? Will performance be a concern? Will storage be an issue?

Edit: I am currently looking at MyISAM tables in a MySQL database.


texts:
I have a server with a RAID1 volume (OS + apps) and a RAID10 volume (database).

The RAID10 volume has 6 physical disks. Should I split the SQL Server database into 6 datafiles (one for each physical disk) or leave it as one single .mdf file?

Edit: to clarify, I am not concerned with the theory of having single or multiple data files, I want to know if the fact that having 6 physical disks in the RAID means that I should have exactly 6 data files, or does the fact that they in an array make this irrelevant?

Thanks


texts: Can a AWS PostgresSQL RDS instance be provided with a stop words file?

texts:
I need to provide a custom list of stop words. From the manual section on dictionaries the way to do this is to put a stopwords file in $SHAREDIR/tsearch_data/.

Is it possible to do this when using AWS? If no, can the stopwords file be provided over the command line instead?


texts: Determining why a transaction log file is so large

texts:
I have a SQL Server 2016 Enterprise database server that I have recently inherited.  One instance on this server has an application that uses two different databases.  One of these is the actual application database and the other is used to generate reports and dump data from the first.  The second database has several stored procedures that run at different times, including one that dumps data from a view into a CSV file for offsite storage of the data that runs every 15 minutes.

When I initially got this database, the recovery model was set to simple and a database backup was being taken nightly.  Since, I have changed the recovery model to full and changed the backup plan so that it is taking full backups twice weekly, differential backups twice daily and log backups every three hours.  However, even with this change, the transaction log for the second database is staying around 300GB.  Each transaction log backup is around 80GB.

I have plans to dig into how the stored procedures work and hopefully simplify some the way these things run, but that is going to be a long term project.  In the short term, I need to find a way to determine if the transaction log is being truncated correctly and if there is anything I can do to reduce the size of the transaction log on this database.  I've looked at the log with the fn_dblog function hoping to see if there were old transactions being stored, but most of the Begin Time and End Time records are null.

Other than a complete rewrite of the system (which is in the works), does anyone have any suggestions for getting a handle on this log file?


texts: why use PostgreSQL clusterdb instruction

texts:
I have come across recommendation for 1C:Enterprise on PostgreSQL. It says that we should periodically use clusterdb command but with no details.
PostgreSQL documentation says: "clusterdb is a utility for reclustering tables in a PostgreSQL database", but what does it mean "reclustering"?
As I come from SQL Server world I might assume that it is the same as rebuilding clustered index, but is that true?


texts: Question on EDR: a plant may have one or more harvesting seasons

texts:
I working on the following ERD, trying to understand what is the best way to connect the entities in the example of plants! For now I'm just focusing on the right side of the ERD (yellow, orange, green)!
Logic in English:

Each plant has planting / growing / harvesting requirements!
The planting / growing / harvesting requirements may have different values and descriptions!
All plants have only one table to hold planting requirements.
All plants have only one table to hold growing requirements.
However some plants may have one or more harvesting requirements.

My question:
What is the best way to show the harvesting requirements?
An example:

Cucumber:

planting requirements values
growing requirements values
one harvesting season:

values

Peas

planting requirements values
growing requirements values
two harvesting season:

values
values

UPDATE:
I'm having some issues implementing the above ERD to MySQL!
Here is how I create the two tables:
CREATE TABLE plant(
    plant_id INT AUTO_INCREMENT,
    taxon_name VARCHAR(25),
    common_name VARCHAR(25),
    general_description TEXT,
    image BINARY,
    cultivation_group_id INT,
    nutritional_info_id INT,
    disease_id INT,
    planting_req_id INT,
    growing_req_id INT,
    harvesting_req_id INT,
    PRIMARY KEY (plant_id)
);

CREATE TABLE harvesting_req(
    harvesting_req_id INT AUTO_INCREMENT,
    number_of_harvests INT,
    descriptive_growing_season VARCHAR(25),
    min_length_of_growing_harvest FLOAT,
    max_length_of_growing_harvest FLOAT,
    harvest_start_date DATE,
    PRIMARY KEY(harvesting_req_id)
);

ALTER TABLE plant
ADD FOREIGN KEY(harvesting_req_id)
REFERENCES harvesting_req(harvesting_req_id) ON DELETE SET NULL;

Here is the table for harvesting_req using SELECT * FROM harvesting_req;:

harvesting_req_id
number_of_harvests
descriptive_growing_season
min_length_of_growing_harvest
max_length_of_growing_harvest
harvest_start_date

1
2
Annual
55.0
75.0
2017-01-15

2
2
Annual
55.0
75.0
2017-06-30

But when I try the following query I get only one start date:
SELECT common_name, harvest_start_date
FROM plant, harvesting_req
WHERE plant.harvesting_req_id = harvesting_req.harvesting_req_id;

common_name
harvest_start_date

Peas
2017-01-15


texts: Is content of the pg_autovacuum saved when pg_dumpall is used to backup the database?

texts:
From the docs:
pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not     save these objects.) This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole.
Since pg_dumpall reads tables from all databases you will most likely have to connect as a database superuser in order to produce a complete dump.

I would assume yes, but I am not completely sure.


texts: How to access a fresh install of SQL Server 2012?

texts:
I've performed a fresh install of SQL Server 2012 on my local development PC. I've done this before and it has worked, however this time I'm getting the following error message when I attempt to sign into SQL Server.

"Login failed for user xxxxxxxxxx (Microsoft SQL Server, Error: 18456)"

How can I access my local instance of SQL Server? I'm rather ignorant about SQL Server security.


texts:
In direct reference to the accepted answer in this question

  MySQL doesn't log to the binary log file when you use direct database.table syntax in your update queries (eg. UPDATE Foo.Bar SET ...).

Is the above true? It doesn't say this explicitly in the link the answerer provides. And is the only way to mitigate this is to ensure that your application doesn't prefix the schema name to the table refs in queries?

The reason I am asking is because we recently restored from a recent backup after an accidental drop database. I was also hoping to complete the data by restoring that day's activity from the binlogs. When I extracted the queries from the binlogs I couldn't find a single query before drop database appeared in the log. - I am also certain that I extracted the correct timestamps.


texts: Is there a way to calculate the row size (in bytes) of a particular table in postgresql / pgadmin3?


texts: Copy Database wizard fails but leaves no text file log

texts:
I've been using the copy database wizard to create a development copy of my database.

Unfortunately, the copy database wizard has repeatedly failed.  I suspect the problem is that the sql server agent service account does not have the necessary permissions.  I can't verify this because the log file isn't actually being saved.  I've tried having it save to the default directory where I know the account running the copy database operations must have permissions and to another location on the server.

Any thoughts about how I can resolve this problem?  The new database is showing up but without any tables...


texts:
I just watched the very interesting video from Rob Farley "Designing for simplification" and I was wondering if the 4 rules given in the video for join removal also hold on PostgreSQL.

If a view is doing joins that don't:

add any columns
eliminate rows
duplicate rows
introduce nulls

will the joins be ignored for the queries that don't need them. Or is it a SQL Server thing only ?


texts: Create stored procedure that contains newlines with a oneliner sql statement

texts:
I want to put code in a single line that will create a stored procedure that contains newlines.

Is that possible?
Do I need to use sp_executesql?
How do I escape newlines in a sql statement?
How do I escape newlines in a string?


texts:
Weird things happened lately on one of my SQL Servers:

Select * from View never ends, but the SQL queries inside the view runs well separately.
ALTER procedure never ends.
DELETE procedure cause Error:1222, which suggests a lock from something
Multiple SQL jobs never end once started.(killed manually after run for 3+ days)

I had a feeling that something was not right with the SQL server, so I rebooted the server and everything was fixed.

I still don't know what went wrong. It is like SQL Server wore out. I am wondering if anyone had similar experience or can point me some reading about what happen during the process of rebooting.


texts:
I have master-slave (hot_standby) pgsql configuration and I want to make backups from one of my slaves. I don't want PITR, just good old database dump.

I switched to postgres from MySQL. With MySQL replication I made backups from slave with these steps:

stop slave;
perform mysqldump
start slave

Can I do something similar with postgres?

I tried pg_dump on slave, but it returned errors:

pg_dump: Dumping the contents of table "news_event" failed:   PQgetResult() failed.
pg_dump: Error message from server: ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Also, pg_dump works good on master so I can do backups on master server, but I prefer to make it on slave.


texts: Is there appreciable overhead from having a large proportion of empty table partitions in SQL Server?

texts:
I recently inherited a project using partitioning on date where a daily scheduled task implements a sliding window scheme with 30 days in the past and 60 future dates.

In reality the data that is inserted uses SYSUTCDATETIME() for the partitioning column so the 60 future partitions are always empty.

Is this an issue that needs addressing or should I just let sleeping dogs lie?


texts: How to create a table in a database on a different server

texts:
I need to create Table B in database B on SQL Server B by querying Table A in database A on SQL Server A. Table B does not exist in database B. SQL Server A and B are linked. I wonder how could this be done?

Thanks


texts:
Having the following data in a table:

ID     Category   Value
1234   Cat01      V001
1234   Cat02      V002
1234   Cat03      V003
1234   Cat03      V004
1234   Cat03      V005

I want to have the following output:

ID     Cat01  Cat02   Cat03
1234   V001   V002    V003
1234   V001   V002    V004
1234   V001   V002    V005

The output I want to achieve is a kind of pivot table where I have all the values vertically in a table and I want to have those values, horizontally, having the category as a column. But there are some categories that have multiples values, in that case, I need to repeat the values of all other categories and create a row per each repeated value

How can it be done in PostgreSQL?


texts: Mysql 5.6 and install php5-mysql , mysql-common

texts: I install Mysql 5.6 with this documentaion(in ubuntu 13.04) , now I need to install php5-mysql and mysql-common packages. can I install them with apt-get? I think they will install mysql5.5 compatible, may some error occure! how can I do?


texts:
I have a table with 21 columns. The majority are numerical and the rest are nvarchar.

I need to add 4 columns that will contain XML data for each row. The XML data for each row can be anywhere from 200 to 2,000 lines.

The question is:

Does adding the XML typed columns to the same table change the speed of querying that table?
What are the performance benefits when I add the xml typed columns to another table and join both tables when doing queries?
Is it better to encode the XML data to shrink it and decode it in the application?


texts: How to verify the restored MS SQL DB from Backup matches with the source?

texts: Once you restore the backup, how do you verify that the source and destination are in sync (schema, datatypes, number of records etc)? assuming that the source has not change. Appreciate your insights.


texts: Best approach to store and match multiple data set against another set

texts:
I am developing a match making system where a user has certain data associated with his profile information. he has set of preferences to match against other users in the system. I am in search for better approach to save data and apply search. I can explain with an example: I have an education field in the profile. I can set in preference, multiple education either in 1) single column as comma separated values or in 2) multiple rows . This value in education can be matched aginst other user's education in profile data.

Which method should be the better one from the above scenario in terms of speed, performance, stability?  Is there any other good approach.

I have more than 20 fields like this to match and expecting over 100k user profiles in the system.


texts: Why does JSONB show a different date format than Javascript?

texts:
With Javascript's .toISOString(); and .toJSON() I get

(new Date()).toISOString();
2018-01-30T12:04:13.153Z

Using PostgreSQL when I return a date, I get

# SELECT to_jsonb('2018-01-30T12:04:13.153Z'::timestamp with time zone);
            to_jsonb
---------------------------------
 "2018-01-30T06:04:13.153-06:00"
(1 row)

Why are these two different formats?


texts: Best practices for ranking records in flexible way

texts:
Suppose we have 10000 records of any product.
User search on our website with his given criteria. so we output search results based on given criteria.

Now Admin want to rank products in search.
so simple thing is to make order field on product table and order by it in query.

So is there any alternative and best ways for it?


texts:
How can I ignore the is_enrolled field from distinct? This is the query:

select DISTINCT
`courses`.*, IF(course_enrollments.user_id=12, TRUE, FALSE) as is_enrolled
from `courses`
inner join `course_time_periods` on `course_time_periods`.`course_id` = `courses`.`id`
inner join `org_time_periods` on `time_period_id` = `org_time_periods`.`id`
left join `course_groups` on `course_time_periods`.`id` = `course_groups`.`course_period_id`
left join `course_enrollments` on `course_groups`.`id` = `course_enrollments`.`group_id`
where `org_time_periods`.`started_at` <= "2017-08-12 12:39:05"
    and `org_time_periods`.`finished_at` >= "2017-08-12 12:39:05"
    and `courses`.`is_active` = true
    and `courses`.`organization_id` = 2
    and `course_enrollments`.`deleted_at` is null

This duplicating one course because it's enrolled, so it returns the first row with is_enrolled as trueand next one as false.


texts: Converting irregular string date to date in Sql Server 2016?

texts:
I have a CSV with what I think is an unusual date format:

  Mon Nov 14 2016 11:31:26 GMT+0200 (South Africa Standard Time)

I would like to convert that a DATETIME2 (or other date format) in SQL Server 2016.

Is there an easy way to do this or would I have to do some kind of string manipulation first? I find that if I can shorten the string to SELECT CONVERT(datetime2, 'Nov 14 2016 17:12:18') then I get a result.


texts:
I've setup MongoDB enterprise on CentOS 7 with Kerberos support. Additionally I've setup a Kerberos service on CentOS 7 for the authentication. I know that Kerberos is setup correctly as I now have multiple CentOS 7 clients SSH authenticating against kerberos. When I start MongoDB with Kerberos, I get the following error in the MongoDB Enterprise logs.

2016-04-05T16:30:50.515-0500 F CONTROL  [main] Failed global initialization:
UnknownError: gssapi could not acquire server credential for
mongodb/mongodb.centos7.vm@CENTOS7.VM; Major code 851968; Unspecified GSS failure.
Minor code may provide more information; Minor code 39756033; No key table entry
found for mongodb/mongodb.centos7.vm@CENTOS7.VM;

I've also verified that a ticket has been generated via kinit

[root@mongodb mongodb]# kinit mongodb/mongodb.centos7.vm@CENTOS7.VM
Password for mongodb/mongodb.centos7.vm@CENTOS7.VM:
[root@mongodb mongodb]# klist
Ticket cache: FILE:/tmp/krb5cc_0
Default principal: mongodb/mongodb.centos7.vm@CENTOS7.VM

Valid starting     Expires            Service principal
05/04/16 17:17:11  06/04/16 03:17:11  krbtgt/CENTOS7.VM@CENTOS7.VM
    renew until 06/04/16 17:17:09

I then verified the principals exist on both client and server

[root@mongodb mongodb]# kadmin -p root/admin
Authenticating as principal root/admin with password.
Password for root/admin@CENTOS7.VM:
kadmin:  listprincs
K/M@CENTOS7.VM
...other principals
mongodb/mongo.centos7.vm@CENTOS7.VM
mongodb/mongodb.centos7.vm@CENTOS7.VM
root/admin@CENTOS7.VM

What is preventing mongo to see and use this principal? I've verified my DNS is working correctly and all other Kerberos functionality seems to be working as expected. I'm unbelievably stumped with something that seems like it should be a simple fix.


texts: Query to get current column based on date range

texts:
I have a scenario with the following tables. The issue is when a car is booked in table tBooking it is due to following scenarios.
Example
User books a car in branch (branch is a location of company offices where the cars are kept)

User A books a Car at branch A on date range 4-dec-202- to 6-dec-2020 (tBranch.StartBranchId)  and wants to return it to branch B (tBranch.EndBranchId)

User B is searching for cars available on the 7-dec-2020 in a specific location branch B

How do I write a query where I get the next available car by date range and the requested location?
Let's say am searching for available cars at branch B, this is what I have tried so far:
SELECT * FROM tVehicle WHERE VehicleID NOT IN (
    SELECT VehicleID FROM tBooking
    WHERE StartDate >= @requestedDate
    AND EndDate <= @requestedDate
)
AND BranchId IN (
    SELECT BranchId FROM tBooking WHERE EndBranchId = @branchId
    -- I dont know how to write correct logic for the dates
    AND StartDate >= @requestedDate
    AND EndDate <= @requestedDate
)


texts:
I'm counting on your expertise in this one, I'm developing a scrapper for banking transactions history.
My problem is, scrapping should be done regularly and the bank doesn't supply any unique identification field for each transaction, thus I need to find a way of not importing already imported data..
I can't greedly import if not exists because outliers might ruin the reliability of the application:

1) "01-01-2014", "ABCDEFG", "+100,00", "+1024,56"
2) "01-01-2014", "ABCDEFG", "-57,00", "+967,56"
3) "01-01-2014", "ABCDEFG", "-43,00", "+924,56"
4) "01-01-2014", "ABCDEFG", "+100",   "+1024,56"

Here the 1st and 4th are different operations, that should both be imported into the db. I'm probably looking for a solution that takes into account the whole imported data sequence.
Any ideas on how to implement this, possibly efficiently?! I'm using postgresql, here's my table's DDL's:

CREATE TABLE "extrato" (
    "extrato_id"  VARCHAR(4) PRIMARY KEY,
    "linha"    INTEGER NOT NULL
);

CREATE SEQUENCE linha_extrato_seq;

CREATE TABLE "linha_extrato" (
    "linha_id" INTEGER PRIMARY KEY DEFAULT nextval('linha_extrato_seq'),
    "dt_mov" DATE NOT NULL,
    "dt_val" DATE NOT NULL,
    "descricao" VARCHAR(60) NOT NULL,
    "quantia" NUMERIC(10,2) NOT NULL,
    "saldo" NUMERIC(10,2) NOT NULL,
);
ALTER SEQUENCE linha_extrato_seq OWNED BY linha_extrato.id;

As Craig Ringer said the lack of a timestamp field or unique identifications might ruin the reliability of the application, so I'm doing the best i can to lessen the impact of the "incomplete" data I have.
An important detail is that the synchronization process is to be done frequently, say the last sync was in day 1, then the second sync will take into account the transactions from day 1 and forth, thus overlaps if existent will always happen in the last synced date.

Thus far I tough about the following procedure:

FOR first_row to last_row in to_insert
    IF !exists(cur_row)
        insert([cur_row,remaining_rows]);
        break;
    END IF
END FOR

Any way this can be implemented in PL/pgSQL? can i process the set of inserts as a unique operation?


texts: When do you remove entries from a write-ahead log (WAL)?

texts:
If I understand correctly, when a user updates a database record and commits the transaction the commit() call will block until the following steps are done:

Database documents the change in the write-ahead log (WAL).
Database updates the actual record.
Database removes the WAL entry.

I have the following questions about the WAL:

When does the database know it's safe to remove the entry from the WAL?
Does a WAL implying writing the record to disk twice (once in the WAL, once in the database file)?
Postgres claims that WAL "significantly reduced number of disk writes, because only the log file needs to be flushed to disk" but (coming back to question #1) don't you have to flush the main database file before removing an entry from the WAL?


texts: "Temporary file write failure" when trying to change primary key in MySQL

texts:
In MySQL, When I trying to change primary key by this query:

 ALTER TABLE `resources` DROP PRIMARY KEY, ADD
 PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`);

I execute this query with MySQL root user, but this error occurred:

  #1878 - Temporary file write failure.

My table design is:

CREATE TABLE `resources` (
    `role_id` INT(11) UNSIGNED NOT NULL ,
    `category_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1 ,
    `lang` BINARY(2) NOT NULL ,
    `model` SMALLINT(5) UNSIGNED NOT NULL ,
    `rule` CHAR(4) NOT NULL ,
    PRIMARY KEY (`category_id`, `lang`, `model`, `rule`, `role_id`)
 )
 ENGINE = InnoDB;

This table have about 2000 rows.

I use MySQL 5.6

Why this error has occurred?

what should I do?


texts: Large MySQL table, inserts with primary key check

texts:
I have a table with 12 million of rows and a following task I should frequently perform:

Get search results from somewhere (50 rows). Each result has a key looking as a md5 hash and a table's PM is built on this field.
Check what rows are currently stored.
Store all other rows

Question is what is the best way to perform steps 2-3. I use PHP and Doctrine so not all tricky queries are possible to use. For example I can't use bulk inserts so need to run INSERT up to 50 times in a row.
I see two possible ways:

run SELECT ... WHERE id IN(...) with all 50 IDs and see what is returned, then run as many inserts as I need
run 50 inserts and catch duplicated ID error


texts: Impact of adjusting Time of Mongodb server?

texts:
Using Ubuntu 14.04 and mongodb 2.6.10

I have time difference between a replica machine and primary , my secondary isn't configured with NTP what are the implications of adjusting time on the secondary machine ?


texts: How to create an Oracle Database Link when the password has special characters

texts:
I'm trying to create a DBLink. SQL Developer is the client I'm using. The password to my remote user has *'s in it. I'm having trouble figuring this out. This syntax isn't working:

CREATE DATABASE LINK my_link
CONNECT TO daniel identified by *password
USING 'hostname:port/servicename';

I tried '\' and wrapping the password in {}'s,', to no avail. I get a "Command Not Properly Ended" error. Any advice would help. I know it's a simple issue but having trouble figuring it out from googling.


texts: Protect postgres database from slow queries

texts:
I'm running a Django web site on Heroku, with the database hosted on RDS. There are some queries out there that take 10 to 15 seconds. Yesterday I got a lot of those and it overwhelmed the database. Write IOPS went above 700, CPU went to 90%, and connections went to 0. I'm upgrading the database as a way to deal with these queries and get them into cache. But I'm wondering if there is a way to protect the database from these instances? Like set a timeout and release the connection rather than keep trying to hang on to it?

Edit: do you think setting CONN_MAX_AGE in Django will help?
 Maybe set it to a low number like 30 seconds?


texts: Why Aren't Deleted Documents Being Pruned When Optimizing InnoDB FullText Tables?

texts:
Following the MySQL 5.6 documentation (Example 14.5), I am under the impression that by setting innodb_optimize_fulltext_only=1 and optimizing a table, both the INNODB_FT_INDEX_CACHE and INNODB_FT_DELETED should decrease (as DML gets "flushed"/"committed").

However, this is not the observed behavior; I have millions of "deleted" documents that are not being touched (and I am suffering an ever increasing performance hit for my fulltext searches).

MYSQL> SET GLOBAL innodb_optimize_fulltext_only=1;
Query OK, 0 rows affected (0.00 sec)

MYSQL> SET GLOBAL innodb_ft_aux_table = 'my_db/my_table';
Query OK, 0 rows affected (0.00 sec)

MYSQL> select count(*) from INNODB_FT_INDEX_CACHE;
+----------+
| count(*) |
+----------+
|      860 |
+----------+
1 row in set (0.00 sec)

MYSQL> select count(*) from INNODB_FT_BEING_DELETED;
+----------+
| count(*) |
+----------+
|  6053404 |
+----------+
1 row in set (3.63 sec)

MYSQL> select count(*) from INNODB_FT_DELETED;
+----------+
| count(*) |
+----------+
|  6070724 |
+----------+
1 row in set (3.63 sec)

MYSQL> OPTIMIZE TABLE my_db.my_table;
+----------------+----------+----------+----------+
| Table          | Op       | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| my_db.my_table | optimize | status   | OK       |
+----------------+----------+----------+----------+
1 row in set (5.30 sec)

MYSQL> select count(*) from INNODB_FT_INDEX_CACHE;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

MYSQL> select count(*) from INNODB_FT_BEING_DELETED;
+----------+
| count(*) |
+----------+
|  6053404 |
+----------+
1 row in set (3.96 sec)

MYSQL> select count(*) from INNODB_FT_DELETED;
+----------+
| count(*) |
+----------+
|  6070820 |
+----------+
1 row in set (3.58 sec)

How can I get MySQL to take care of these deleted records?


texts: Merge row size overflow in SQL Server - "Cannot create a row of size.."

texts:
The target table to which I'm trying to merge the data has ~660 columns.
The code for the merge:

MERGE TBL_BM_HSD_SUBJECT_AN_1 AS targetTable
USING
        (
SELECT *
FROM TBL_BM_HSD_SUBJECT_AN_1_STAGING
WHERE [ibi_bulk_id] in (20150520141627106) and  id in(101659113)
    ) AS sourceTable
ON (...)
WHEN MATCHED AND ((targetTable.[sampletime] <= sourceTable.[sampletime]))
        THEN UPDATE SET ...
WHEN NOT MATCHED
        THEN INSERT (...)
    VALUES (...)

The first time I ran this (i.e when the table is empty) it resulted in success,
and inserted one row.

The second time I ran this, with the same data set, an error was returned:
Cannot create a row of size 8410 which is greater than the allowable maximum row size of 8060.

Why the second time I tried to merge the same row which already was inserted it resulted in an error. If this row exceeded maximum row size, it would expect for it not to be possible to insert it in the first place.

So I tried two things, (and succeeded!):

Removing "WHEN NOT MATCHED" section from merge statement
Running an update statement with the same row I tried to merge

Why does update using merge is not succeeding, while insert does, and direct update also does?

UPDATE:

Managed to find the actual row size - 4978. I've created a new table that has only this row, and find the row size this way:

And I still don't see something exceeding the allowed limit.

UPDATE(2):

Full reproduce

Made an effort that this reproduce will not require any additional auxiliary objects, and that the data will be (somewhat) obfuscated.

Tried this on several servers, from version 2012, and one from 2008, and was able to fully reproduce in all of them.


texts: How to design this kind of relations in a database

texts:
I have a conceptual problem and I would like to get your ideas on how I'll be able to do what I am aiming.

My goal is to create a database with information of persons who work at a place depending on their profession and skills.

I have 3 categories which can have subcategories:

Outsourcing
Technician

welder
turner
assistant

Administrative

supervisor
manager

So each person has its information and the projects they are working on, also one person may do several jobs...

I was thinking about having 3 tables (PERSON, SKILLS, PROYECTS) but I guess there is a better way of doing this.

create table Person
(
    [Person_ID]  int(10),
    [Name]       varchar(30),
    [sex]        varchar(10),
    [address]    varchar(10),
    [profession] varchar(10),
    [Skills_ID]   int(10),
    [Proyect_ID]   int(10),
    [Salary]     float
)

create table Skills
(
    [Skills_ID]  int(10),
    [Person_ID]  int(10),
    [Skills_name] varchar(10),
    [Skills_pay]  float(10),
    [Comments]    varchar(50)
)

create table Proyects
(
    [Proyect_ID]     int(10),
    [Person_ID]      int(10),
    [Proyect_name]   varchar(10),
    [working_Hours]  float(10),
    [Comments]       varchar(50)
)

Is there a way to do this in a more effcient way?  What to add or delete of this small model? I guess I am missing something in the salary - maybe I need another table for that?


texts: Which AWS database services are designed for storing logging records like log4j or log4perl?

texts:
I have been using the file system for log4j and log4perl records and want to start using a database to store the information, especially to lookup records organized by pid. We are using MySQL RDS for most of our databases, so my initial reaction is to continue using so (or experimenting with Aurora RDS), but RDS requires InnoDB for snapshots and I would have preferred MyISAM for speed.

I prefer to keep 30 days of records, at 135M records a day. This could increase by 10x. The most important thing here is speed of inserts, although I would like to be able to retrieve by date or pid within 5 minutes.

RDS services are relational databases, so the retrieval method I require is a good match, but it isn't designed for the huge amount of data being inserted without constraints.

Are SimpleDB or DynamoDB designed for logging records more so than MySQL or Aurora?


texts: Do i need "CHECK CONSTRAINT" when i am using "WITH CHECK ADD CONSTRAINT"

texts:
I am working on SQL Server 2008 R2, where I generated a schema-only database script using SQL Server Management Studio. The generated script is as follows:

ALTER TABLE [dbo].[ConsoleServer]  WITH CHECK ADD  CONSTRAINT [FK_ConsoleServer_RackUnits] FOREIGN KEY([RackUnitID])
REFERENCES [dbo].[RackUnits] ([UnitID])

GO
ALTER TABLE [dbo].[ConsoleServer] CHECK CONSTRAINT [FK_ConsoleServer_RackUnits]

I have these 2 questions:-

I know that the first line is responsible to create a FK between two DB tables. but what is the purpose of the following :

ALTER TABLE [dbo].[ConsoleServer] CHECK CONSTRAINT [FK_ConsoleServer_RackUnits]

I mean since I have specified to add the constraint with check, why do i need to enable the constraint using check constraint ?
Second question: In general, why does the DB script has the word GO. Now if I remove it the script will be executed well on the destination DB, so why it is included in the script prior to any statement?


texts:
I am having trouble setting up the Oracle Wallet on a new box connecting to a new database.  I have added the following lines to my SQLNET.ORA:

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=c:\oracle\Wallet))

The wallet is created without error, the credentials added, and I can list the credentials back from the wallet without a problem, but when I go to use them I get an ORA-12578 as follows:

C:\oracle\Wallet>sqlplus /@MYDB

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 31 16:57:53 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12578: TNS:wallet open failed

I am on a 64 bit box using the 32 bit 11.2 client to connect to an 64 bit 11.2 database.  Logging in without the wallet works fine.  The error message indicates that I should turn tracing on.  I have done so, but don't see anything obvious.  Does anyone have a suggestion before I contact Oracle support?


texts: If function not exist then create function in SQL server

texts:
If not  Exists (Select * from sys.objects where name ='FNLX_getDate'  and type =N'FN')

    Create function [dbo].[FNLX_getDate]() returns Datetime
    as
    Begin
    .................
    End

When I execute the above script I get the error

  Incorrect syntax near the keyword 'function'.

What can be wrong?

Using Microsoft SQL Server to execute above script.


texts: Auto Increment a column that is not unique...?

texts:
I've been thinking on this for a few hours, and I can't work out a viable solution - I can think of solutions, but they are clunky.

Using MySQL, I want to create an invoice table using auto increment for the invoice number. Pretty standard stuff. HOWEVER, my challenge is that I wish to have multiple companies generating invoices in this table, but all still with the unique invoice number that is auto incremented from the previous one.

For example:

Company A's previous invoice is 3010
Company B's previous invoice is 2144
Company C's previous invoice is 7889877

When Company A creates their next invoice I want it to be 3011. B to be 2145 etc.

I had the idea of doing a query WHERE company = 'B' ORDER BY id DESC then using PHP to increase that value++, then insert a new record with the invoice number inserted from PHP. In this case the 'id' field would be irrelevant to the actual invoice number, and just be a unique identifier.

Is there a better way?


texts: Delayed durability - can data loss be prevented in case of crash

texts:
This book suggests that
"Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is filled, that transaction cannot be recovered because it is lost."

However wont the transactions be available on the transaction log and can be replayed for recovery?

https://books.google.co.in/books?id=5Es3DwAAQBAJ&pg=PT159&lpg=PT159&dq="data+loss+is+possible+with+delayed+durability"&source=bl&ots=Mwmh4cKa6G&sig=tctRb18itYQyBxo7lLhkT1w4-z4&hl=en&sa=X&ved=2ahUKEwjAyau784jeAhWHMo8KHW1XCi4Q6AEwAHoECAEQAQ#v=onepage&q="data%20loss%20is%20possible%20with%20delayed%20durability"&f=false


texts: Can Unicode columns include non-Unicode values in SQL Server?

texts:
I need to convert Unicode column values from UTF16-LE to UTF-8 to then import into MySQL.

Is it safe to assume columns of NVARCHAR, NTEXT, NCHAR, BIT, INT, DECIMAL, FLOAT, and DATETIME all must be Unicode and therefore will not have any characters unable to be converted from UTF-16LE to UTF-8 simply by exporting the values to a .txt file and resaving them with the UTF-8 Encoding prior to importing them to MySQL?

Is it safe to assume any unicode columns in SQL Server will not include any characters incapable of being converted from UTF-16LE to UTF-8 after they've been exported to CSV files?

My import fails, regardless if I re-save the CSV with UTF-8 encoding or not. So, I assume either:

SQL Server allows non-unicode character in unicode columns that cannot convert correctly to UTF-8 (which I doubt, hence my question to check my assumption); or
It's failing elsewhere in the process - e.g. re-saving the CSV files adds something MySQL doesn't like.

I'm using bcp to export the values to a CSV. Then, I resave it with the UTF-8 encoding because MS removed the ability to export directly as UTF-8. Finally, I use MySQL's LOAD DATA INFILE to import where it fails.


texts: Replace " " character derived column SSIS

texts:
So I am getting CSV Files and for some reason they are coming as "123-45-9873".

Now I trim the "-" out but the column is an SSN column so I am left with "12345987 taking the 3 out and putting the " character on the table.

What replace function can I use to replace the very character?

For reference, this is what I am doing

(DT_STR,9,1252)REPLACE(SSN,"-","")

and getting "12345678


texts: Over 99 million records with geography column & spatial index - VERY VERY VERY slow queries!

texts:
I’ve been working with SQL Server for a long time but have only recently encountered the need to work with spatial data. I have come into an environment that uses it pretty heavily and my first real challenge is to get queries to run faster (and stop timing out) against a table with a geography data type column (and index).

We use a query that identifies all geography points in this table that are found within polygons and multi polygons. There are over 99 million records in this table and I have NO friggin’ idea how to performance tune this beast!
I have identified the clustered index as being a bit larger than necessary and intend to add an identity column to do two things: 1) Reduce the size of the clustered index. 2) Eliminate page splitting for inserts. Although I expect to get some relief from doing this, I am not optimistic that it will help the spatial queries very much.

Given my almost complete lack of knowledge/experience with spatial data, I am
cannot make this better.

Example query:

    Declare @OrgID int
    Declare @Geog geography

    Set @OrgID =100011

    /* This will return a multi polygon */
    SELECT @Geog = geog
    FROM Organization
    WHERE orgid= @orgid

    Select count(*)
    FROM ProblemChild WITH (INDEX(IDX_geog))
    WHERE Geog.STIntersects(@geog) = 1)

    Table Design:
    CREATE TABLE [dbo].[ProblemChild](
        [Phone] [char](10) NOT NULL,
        [Lat] [float] NOT NULL,
        [Lon] [float] NOT NULL,
        [Geog] [geography] NOT NULL,
        [Recordsource] [varchar](2) NOT NULL
     CONSTRAINT [PK_Phone] PRIMARY KEY CLUSTERED
    ([Phone] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

Index design:

    CREATE SPATIAL INDEX [IDX_geog] ON [dbo].[[ProblemChild]]
    ([Geog]
    )USING  GEOGRAPHY_GRID
    WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = MEDIUM),
    CELLS_PER_OBJECT = 20, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

With:

    Number of records:  99,155,267

    MinLat      MinLon      MaxLat      MaxLon
    18.957356   -166.512394 71.292528   -66.967883


texts: Getting the top count of records from a table for each month and user

texts:
I have a table where translations are stored along with the user_id and when the translation was added.
I want to create a query that shows the 'Translator of the month' (the person who did the most translations in that month).

I have a query to get the top translator of all translations:

SELECT user_id, COUNT(user_id)
FROM gp_translations
WHERE (STATUS='current' OR STATUS='old')
GROUP BY user_id
ORDER BY COUNT(user_id)

and I have a query that shows the year/month:

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month`
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

but when combining both I fail miserably. The query runs endlessly:

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month`, (
SELECT distinct user_id
FROM gp_translations t2
WHERE (STATUS='current' OR STATUS='old') AND     YEAR(t2.date_added)=YEAR(t1.date_added)
AND MONTH(t2.date_added)=MONTH(t1.date_added)
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
LIMIT 1)
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

The table gp_translations has the following structure:

 CREATE TABLE `gp_translations` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`original_id` INT(10) NULL DEFAULT NULL,
`translation_set_id` INT(10) NULL DEFAULT NULL,
`translation_0` TEXT NOT NULL,
`translation_1` TEXT NULL,
`translation_2` TEXT NULL,
`translation_3` TEXT NULL,
`translation_4` TEXT NULL,
`translation_5` TEXT NULL,
`user_id` INT(10) NULL DEFAULT NULL,
`status` VARCHAR(20) NOT NULL DEFAULT 'waiting',
`date_added` DATETIME NULL DEFAULT NULL,
`date_modified` DATETIME NULL DEFAULT NULL,
`warnings` TEXT NULL,
PRIMARY KEY (`id`),
INDEX `original_id` (`original_id`),
INDEX `user_id` (`user_id`),
INDEX `translation_set_id` (`translation_set_id`),
INDEX `translation_set_id_status` (`translation_set_id`, `status`),
INDEX `date_added` (`date_added`),
INDEX `warnings` (`warnings`(1))
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=342587;

I know I am doing something seriously wrong here. But I can't find the way to properly merge both with decent speed. Any help is appreciated. MySQL version is 5.5.34


texts: Fill in missing dates with data value from previous populated date for group

texts:
Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table contains the last department for each ticket for each day it is open on which there is a change in the department (including a row for the date the ticket was initially opened and the date it was closed). The data table looks like this:

CREATE TABLE TicketAssigment (
    TicketId     INT NOT NULL,
    AssignedDate DATE NOT NULL,
    DepartmentId INT NOT NULL);

What I need is to fill in any missing dates for each TicketId, using the DepartmentId from the previous TicketAssigment row ordered by Date.

If I have TicketAssigment rows like this:

1, '1/1/2016', 123 -- Opened
1, '1,4,2016', 456 -- Transferred and closed
2, '1/1/2016', 25  -- Opened
2, '1/2/2016', 52  -- Transferred
2, '1/4/2016', 25  -- Transferred and closed

I want this output:

1, '1/1/2016', 123
1, '1/2/2016', 123
1, '1/3/2016', 123
1, '1/4/2016', 456
2, '1/1/2016', 25
2, '1/2/2016', 52
2, '1/3/2016', 52
2, '1/4/2016', 25

This looks like it might be close to what I need, but I haven't had the patience to let it finish, and the estimated plan cost has 6 digits:

SELECT  l.TicketId, c.Date, MIN(l.DepartmentId)
FROM    dbo.Calendar c
        OUTER APPLY (SELECT TOP 1 TicketId, DepartmentId FROM TicketAssigment WHERE AssignedDate <= c.Date ORDER BY AssignedDate DESC) l
WHERE   c.Date <= (SELECT MAX(AssignedDate) FROM TicketAssigment)
GROUP   BY l.TicketId, c.Date
ORDER   BY l.TicketId, c.Date;

I suspect there is a way to do this using LAG and a window frame, but I haven't quite figured it out. What is a more efficient way of meeting the requirement?


texts: SQL Server 2012 - Multiple named Instances with Always On

texts:
I have two named SQL Server 2012 Standalone Instances on each server. I would like to know what the best way to set up Always On .

INST1(Principal) on SERVER1 --> Replica on INST2

INST2(Principal) on SERVER2 --> Replica on INST1

OR

INST1(Principal) on SERVER1 --> Replica on INST2

INST2(Principal) on SERVER1 --> Replica on INST2

Thank you very much for your time and I really appreciate it!!!


texts: postgres parsing whitespace before vectorizing

texts:
Using PostgreSQL 11 to build a tsvector and create index for better search, and while trying to parse text, there is punctuation and/or whitespace characters that I need to handle differently than parser in PostgreSQL .

For example, the term "R&R" keeps getting separated and counted as two r's when I need "R&R".

Also, another problematic string is "Main St./First St./Second St." which separates into "Main" "St./First"  and "St./Second".

I'm thinking the & and / should be processed upstream of parsing punctuation, which I am just learning this stuff, so maybe it's easier than I am finding.

What is best approach?


texts: Multiple SELECT subqueries in an INSERT statement in a stored procedure

texts:
So I had the following (genericized), which works just fine for me (barring errors in making the example generic) :

DELIMITER //
CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 DATETIME)
BEGIN
  INSERT INTO `db`.`table1` (
        `col1`,
        `col2`,
        `col3`,
        `col4`,
        `col5`
      )
  SELECT val1, date1, date2, 1, `colA` FROM `table2` WHERE `colB` = val1;
END//
DELIMITER ;

But now I want to INSERT into a table (table1) an additional value (col6) SELECTed from a third different table (table3) upon which - as best as I can figure - there is nothing sensible to JOIN in one SELECT statement.

Something like :

DELIMITER //
CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 DATETIME, IN string1 VARCHAR(255))
BEGIN
  INSERT INTO `db`.`table1` (
        `col1`,
        `col2`,
        `col3`,
        `col4`,
        `col5`,
        `col6`
      )
  VALUES
  (
    val1,
    date1,
    date2,
    1,
    (SELECT `colA` FROM `table2` WHERE `colB` = val1),
    (SELECT `colZ` FROM `table3` WHERE `colY` = string1)
  );
END//
DELIMITER ;

colA and colB from table2 are guaranteed UNIQUE within the table.
Similarly, colZ and colY from table3 are guaranteed UNIQUE within the table.

val1/colB is PK in table2; colA is an FK to a fourth table (not important).
colZ is PK in table3; string1/colY is not referenced anywhere else in the database.

In summary : col6 in table1 has an FK to table3's colZ. Hence, I want to INSERT into table1's col6 the INT of colZ based on a known unique, elsewhere unreferenced string in colY, while still being able to SELECT FROM table2 the FK value - colA - of a fourth unrelated table based on a given/known PK value colB and INSERT that into table1's col5.

How can I create a stored procedure that's basically the second example above ... in a form that MySQL will accept ?

(In case it's not super-clear from the example, col1 to col6 belong to table1; colA and colB belong to table2; colZ and colY belong to table3.)


texts: Database design tracking daily personal expense

texts:
I currently have the following in Excel tracking daily expenses:

I would like to turn the following excel sheet into a normalized database. I would also like to capture the descriptions for each expense if its populated. I have 2 approaches in mind:

1) Create a date table and create a separate table for each expense category (as well as a separate corresponding expense description table for each table):

**Date:**
id | date       |
-----------------
1  | 2014-12-01 |
2  | 2014-12-02 |
3  | 2014-12-03 |
4  | 2014-12-04 |

**Rent:**
id |date_fk | amount |
----------------------
1  | 1      | 1000   |

**Restaurant:**
id |date_fk | amount |
----------------------
1  | 2      | 12     |

**Restaurant_desc:**
id | restaurant_fk | description        |
------------------------------------------
1  | 1             | Dinner at Arby's|

2) Create a date table and one general expense table and one expense description table:

**Date:**
id | date       |
-----------------
1  | 2014-12-01 |
2  | 2014-12-02 |
3  | 2014-12-03 |
4  | 2014-12-04 |

**Expense:**
id | date_fk | amount | category     |
--------------------------------------
1  | 1       |  1000  | rent         |
2  | 1       |  120   | parking      |
3  | 2       |  12    | restaurant   |
4  | 2       |  10    | entertainment|

**Expense_description:**
id | expense_fk | description       |
-------------------------------------
1  | 3          | Dinner at Arby's  |

Which approach is better? I'm thinking the 2nd approach is better because its more flexible, if there are additional categories in the future, you won't have to add more tables to accommodate it. Or is there is a better schema for this? please let me know.
Thanks!


texts:
My setup:

Oracle 11g
32G RAM
8 CUP threads

Three oracle instances have been running. But running this:

sqlplus "/as sysdba"

gives me a prompt:

  Connected to an idle instance.

When I issue the startup  command, I get this error:

  ORA-04031 unable to allocate 2048 bytes shared memory  ("shared pool","unknow object","sga heap(1,0)","procs:ksunfy");

But three Oracle instances are working now.

Maybe I changed some config incorrectly. What can I do to fix this?


texts: Difference between now() and current_timestamp


texts: PostgreSQL WHERE clause with two OR'ed conditions, priority to first

texts:
I'm storing user data (username, email, password hash) in PostgreSQL, and I can only enforce uniqueness on username. I'd like to let users provide either their username or their email when they login. However, when matching their input against usernames and email addresses I want to ensure that a username match gets priority over an email match.

For example, in the following contrived instance:

id | username        | email address    | pw_hash
-------------------------------------------------
1    john_doe          john@domain.com    x1j34
2    john@domain.com   john@domain.com    x1j34

if both users enter their username, I want to guarantee that user 2 gets logged in.

If I use:

SELECT * FROM member WHERE (username='john@domain.com' OR email='john@domain.com') AND pw_hash='x1j34';

this matches both users but provide no guarantee on order.

Is there a way to have to conditions, and only check the second condition if the first isn't satisfied? Can this be done in a single query?


texts:
I've been developing a software that runs on hardware and now I need to synchronise DB on them, but only partly - several tables using MySQL Clusters(don't matter which style), each of hardware can act as a node, and we have virtual one as NDB_MGM.
For ex, I have two tables in DB: users and rules.
Users should stay unique to each node, but rules should be synced.

My question is: is it even possible?


texts: Why is mixing column collations in a single database considered bad?

texts:
There are two reasons that prompts me to ask this question:

tSQLt
The T-SQL testing framework tSQLt considers it an issue of "High Severity" when there exists columns with a non-default collation. The author of the test states the following:

  I am NOT suggesting that every string column should have a collation that matches the default collation for the database. Instead, I am suggesting that when it is different, there should be a good reason for it.

Yet, the severity of the failed test is, as mentioned, considered high.

Octopus Deploy
While configuring the Octopus Deploy Server, the setup fails with a FATAL error during the initialization of the OctopusServer-instance. The article related to the error-message does not explain why this is a requirement, but simply states that it will be a requirement for future deployments, from and including Octopus version 3.8.

As a side-note, RedGate's CI-tool package, the DLM Automation Suite, supports deployments with varying collations without complaints.

The recommendation of keeping all column collations to the database default seems more like guidelines or best practices to me. Why is it considered such a serious error by some?


texts: Error: change in schema version between compile time and run time - SQLNCLI11

texts:
This is a head scratcher. I've searched the forums and no one has been able to answer this. Not even GilaMonster on SQLServerCentral has chimed in on this one! One user suggested that it may be referring to a schema version number however it seems to be a bit high of a number for that. I looked for a query that could verify this but wasn't able to find a query to return version number of a schema, only the id of a schema.

The error below (message_id 7359) showed up recently and briefly on our servers that are linked. I'm not looking for a why so much as a what. What datatype is used for the compile time and run time values in this error message and is there a function already for formatting that into something human readable, assuming it is a datetime value? I've got a hunch its relatively new since there's nothing out there, that I could find anyway, that explains what those digits are.

Here's an example of the message we get:

  The OLE DB provider "SQLNCLI11" for linked server "SSQLS01" reported a change in schema version
  between compile time ("186839690094804") and run time ("186852574981615") for table ""SS017"."dbo"."TVHA"".

Thank you !


texts: SQL User vs AAD for Azure SQL Managed instance

texts:
It appears the app response is much slower when it uses the AAD  account to access the Managed instance compared to using a SQL user (SQL Auth).

Can anyone please shed some light on whether this is expected behaviour? or we need to check if there is some issue with the AAD Domain services.

I believe there will be a bit of latency but should not be noticable.

Also how exactly the Authentication works in case of AAD?


texts: Granting access control on specific columns in PostgreSQL

texts:
The following screenshot is from an online learning platform I am using to learn SQL (more specifically PostgreSQL). I am concerned that I'm not understanding the following paragraph correctly.

It states that:

  One shortcoming of SQL's approach to access control is that it only
  works on the granularity of whole tables (with the exception of the
  UPDATE privilege).

However, it appears I am entirely able to do the following to achieve the same result as creating a view and granting the SELECT privilege to the user for that view:

GRANT SELECT(studentId, uosCode) ON Enrolled TO SomeUser

Is the screenshot incorrect or have I misinterpreted something?


texts: Choosing the correct shard key for MongoDB

texts:
I'm new to MongoDB. From the mongod documentation, I know that when choosing shard keys the key should always have high cardinality, so write operations are distributed to different shards (a kind of write scaling). But for query isolation, better query performance will be gained if a query is routed to a single shard server (one single mongos instance).

I need recommendations or guidance on choosing the right shard key based on the JSON data below. My plan is to store the below JSON as one row in the MongoDB collection. ReceiptID is unique for each row, but most of the queries will be on RetailerID. RetailerID is not unique for each row and will be the same for each row. I can use ReceiptID as the shard key which has high cardinality and provides better write scaling. But having an index on this field is not helpful since most of my queries will be on RetailerID but RetailerID won't be unique for each row. Should I use a compound index (RetailerID + timestamp) as the shard key?

Any guidance or recommendations for choosing the correct shard key?

{
  "RetailerID": 2,
  "RetailerName": "Walmart",
  "RetailerLogo": "",
  "RetailerAddress": "yyututiu",
  "RetailerPublicKey": "yutuytytyu",
  "Consumer_id": "1",
   "ReceiptsData": {
    "ReceiptID": 2,
    "order_date": "2017-03-03 18:36:57",
    "total": "39.40",
    "billing_name": "AJ Test",
    "shippingAddress": "35 seconf Street, Moscow, PA, 18444\r\n",
    "cc_brand": "MC",
    "cc_last_4": 9902,
    "ItemDetails": [
      {
        "itemname": "Benj Moore Paintyy",
        "UPC": "12726600",
        "itemPrice": "15.00",
        "qty": "1.00",
        "datetime": "2017-03-03 18:36:57"
      },
      {
        "itemname": "Test Item",
        "UPC": "12726600",
        "itemPrice": "15.00",
        "qty": "1.00",
        "datetime": "2017-03-03 18:36:57"
      }
    ]
  }
}


texts: How can i use the same query for all codes?

texts:
I use this query in PHPmyAdmin, i have date that contain(date,hour),supp, price and code_product (it repeated many times) :

I want to return the last price for the last date of product code

   SELECT invent.code_product,products.supp,products.price,products.date
   FROM products

   LEFT JOIN list_product ON products.supp = list_product.supp
   LEFT JOIN invent on list_product.id_product = invent.fk_product

   WHERE invent.code_product= "XXX185"
   ORDER BY products.date  DESC
   LIMIT 1 ;

This is the result for one code :

The query works for one code and limit it the result in 1.

How can i use the same query to return the last date and price for all codes?


texts:
MyISAM vs InnoDB

I keep reading how InnoDB is faster than MyISAM. I wanted to find out myself.

When I was using a forloop in PHP to fill my MySQL test database with 100,000 numbers using PDO driver.

|a|
+-+
|0|
|1|
|2|
|3|
|4|
...
|99999|

I find out that with InnoDB it went 95 records per second.
After a while I stopped my script cause going to 100,000 is just going to take too long.
Changing my table to MyISAM and re-running the script made a whopping 6000 records per second!
Both tables have no indexes.

How is it that the internet is claiming InnoDB is faster than MyISAM. Could it be that PDO is bottlenecking InnoDB?


texts: update the timestamp on column when data is filled in another

texts:
I am new to Postgresql and SQL in general. I have a table that looks like this

id (primary key)|temperature|temperature_timestamp|humidity|humidity_timestamp|...|parameter|parameter_timestamp

I want the timestamp of a particular "parameter"_timestamp to be automatically updated when I do an 'UPDATE' on a particular parameter.

Eg: when I update the temperature in a particular row the corresponding temperature_timestamp on that row should update

I am following this link currently https://www.revsys.com/tidbits/automatically-updating-a-timestamp-column-in-postgresql/ and am able to update one column how do I extend this to my entire table ?

MY Trigger funtion looks like this :

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.temp_lastupdated = now();
RETURN NEW;
END;
$$ language 'plpgsql';

And my trigger looks like this

CREATE TRIGGER update_timestamp
BEFORE UPDATE
ON sensor_data
FOR EACH ROW EXECUTE PROCEDURE  update_timestamp();