texts: Does SQL Server place shared locks on scanned records when using REPEATABLE READ

texts:
Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ.

In this session I start a transaction and execute an UPDATE statement with a WHERE clause on  a non indexed column. This statement should evaluate WHERE clause for each record in the table, but only one will match.

If I examine the locks placed under this transaction after running the UPDATE statement I can only see two IX locks on Table and Page, and a X lock on the row which was updated.

My question is: shouldn't the database engine place shared locks on all the rows it read to ensure REPEATABLE READ? What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

If I execute a SELECT *, then I can see it placing S locks on each row, which are not already locked with X.

Can anyone help me understand this situation?

I've tried with both SQL Server 2008 R2 and 2012, same behavior in both.


texts:
I have a PG 9.6 DB and I need to add some auditing and after googling, I have found pgaudit. Before trying to install and playing a bit with it, I'd like to make sure it complies with all my requirements.

The first concern I have regards data integrity: from my understanding, the extension stores the information in a simple log file without any data integrity check, so If I open the log and I manually delete something nobody will never notice it. Am I correct?

Does PG come with any log integrity mechanism?

Thanks


texts: How to use set-based statement instead of cursor for converting 1 table to two, keeping relations

texts:
I'm relatively new to databases and currently working with SQL. I am doing a conversion for a customer. This conversion points to local files on a filesystem. In the old database there simply was a table containing file details and relations of that document to other entities. In our new standard file details and relation between files and other entities are seperated into 2 different tables. Let's call them "file" and "objectfile". For example, manual as a file. "file" will contain filename, upload date etc. "objectfile" will contain file-id and for example id of a phone or a task.

I have to convert the old to the new. Currently I am using a cursor but there is a lot of debate on cursors. A while loop was slower and 2 insert-into statement with a while-loop linking the two records together was slower than both other options. My search for set-based processing has also shown me that a LOT of time can be saved using set based approach. My cursor works as follows:

Get all rows of old table
For each entry get auto-increment key with
a SP (we use our own system for auto-increment) for table "file" and
"objectfile".
First insert a "file" entry containing some of the
columns from the old table and the new file ID from the custom SP.
Then insert an "objectfile" entry containing some of the columns
from the old table, the new objectfile ID from the custom SP ánd the
file ID used in the previous statement.

Now the file and objectfile entries are related. I have looked into a set-based approach with "insert into" statements but I just can't figure out how to get the relations correct, if possible at all. I've found several links, for example about the output clause:

What I would want is something like the following (simplified compared to original database model):

insert into dba.File (FileCreateDate, FileLocation, FileFileName)
OUTPUT dba.File.FileId, (select GetDate(), PFilePhoneId from dba.PhoneFile)
INTO dba.ObjectFile (ObjFileFileId, ObjFileCreateDate, ObjFilePhoneId)
select PFileDate,PFileLoc, PFileName from dba.PhoneFile

But a subquery is not allowed in the output clause...What other way can I make this quicker? Currently a cursor takes around 30 minutes to complete 88000 rows, I'm curious to how much time-saving can be had.

The cursor (simplified as a lot of columns are involved):

DECLARE @PhoneFile_ID varchar(15)

DECLARE ObjFile_CURSOR CURSOR
FOR
SELECT PhoneFileId
FROM dba.PhoneFile

OPEN ObjFile_CURSOR

FETCH NEXT FROM ObjFile_CURSOR INTO @PhoneFile_ID

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @FileId   NVARCHAR(12);
    EXECUTE dba.applspGetAutoIncValue FileId, @FileId OUTPUT;

    DECLARE @ObjFileId   NVARCHAR(12);
    EXECUTE dba.applspGetAutoIncValue ObjFileId, @ObjFileId OUTPUT;

    insert into dba.File (FileCreateDate, FileLocation, FileFileName)
    select PFileDate,PFileLoc, PFileName from dba.PhoneFile where PhoneFileId = @PhoneFile_ID

    insert into dba.ObjectFile (ObjFileId, ObjFileFileId, ObjFileCreateDate, ObjFilePhoneId)
    select @ObjFileId, @FileId, GetDate(), PFilePhoneId from dba.PhoneFile where PhoneFileId = @PhoneFile_ID

FETCH NEXT FROM ObjDoc_CURSOR INTO @PhoneFile_ID -- Once the work is done we fetch the next result

END

CLOSE ObjFile_CURSOR
DEALLOCATE ObjFile_CURSOR

An example:
PhoneFile table

PFileId - 1
PFileName - 'manual.pdf'
PFileLoc - '../Manual/'
PFileDate - '2015-09-16 07:43:00.567'
PFilePhoneId - '9463'

Should go into:
File table

FileId - 00001
FileCreateDate - '2015-09-16 07:43:00.567'
FileName - 'manual.pdf'
FileLoc - '../Manual/'

ObjectFile table

ObjFileId - 00000001
ObjFileFileId - 00001
ObjFileCreateDate - '2016-01-13 13:19:00.000'
ObjFilePhoneId - 9463


texts: How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase?

texts:
I am stuck on implementing a left join on dates for each individual customer.

I have a table which stores customer purchases:

SELECT purchase_date, customer
FROM purchases

I would like to create a similar table however one that includes ALL dates and not just purchase dates, for example:

SELECT date, customer, purchase_made

Where purchase_made is a BOOLEAN column. I understand that I need to use a LEFT JOIN however I am stuck at making sure that EACH customer has a full set of dates. For example if no customers made a purchase on 2016-01-01, and there are 2000 customers in the database, then 2000 rows of 2016-01-01 should be added (with each row attributed to a customer).

So far I've tried:

WITH dates AS (
    SELECT GENERATE_SERIES('2016-01-01'::TIMESTAMP, '2016-01-15'::TIMESTAMP, '1 day'::INTERVAL) AS date
    ),

SELECT date, customer
FROM purchases
LEFT JOIN dates ON purchases.date=dates.date;

How to implement the BOOLEAN column?

Also is it possible to add the date rows only AFTER a customers first purchase? So for example only insert 2016-01-01 for customers who made a purchase prior to 2016-01-01.


texts: Using IF in T-SQL weakens or breaks execution plan caching?

texts:
It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance.  I'm trying to find some confirmation or validate this assertion.  I'm using SQL Server 2005 and 2008.

The assertion is that with the following batch:-

IF @parameter = 0
 BEGIN
  SELECT ... something
 END

ELSE
 BEGIN
  SELECT ... something else
 END

SQL Server cannot re-use the execution plan generated because the next execution may need a different branch.  This implies that SQL Server will eliminate one branch entirely from execution plan on the basis that for the current execution it can already determine which branch is needed.  Is this really true?

In addition what happens in this case:-

IF EXISTS (SELECT ....)
 BEGIN
  SELECT ... something
 END

ELSE
 BEGIN
  SELECT ... something else
 END

where it's not possible to determine in advance which branch will be executed?


texts: SQL Server 2017 Enterprise vs Standard - Online Indexing and Online Schema Change

texts:
I read through Microsoft's "Editions and supported features of SQL Server 2017" doc to compare the feature differences between Enterprise and Standard edition.

Two things not available in Standard that caught my eye were:

Online indexing
Online schema change

Does this literally mean you can't create and modify indexes or tables (and other objects?) without taking the database offline first, or does Microsoft mean there are only specific operations you can't do and if so where can I learn more about which operations require taking the database offline?


texts:
In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text.

Currently the longest entry has length 360  (found using SELECT MAX(LENGTH(value)) ) and the theoretical biggest value would be coming from a varchar(511) column.
There is no index on the column.

I was advised to try and see if we can move this column from text to something else, but I'm struggling to find out if this will help at all. I have done some research of course, but I would like too see if I'm correct. I know 'random advice' is not too be taken too seriously, but at first glance it seemed to me it has some merit: text is big, and max of 511 chars isn't that big.

But, as far as I can find, (bigger?) varchar/varbinary and text/blob are handled the same when using InnoDB. The issue one might face with large values is that not everything is stored in the same 'row' (page? not sure of the terminology here) on the disc when using text. There are several sources that say this is a misunderstanding as some of the text is actually saved in the same place a normal row would be saved. (I hope I'm describing this right)

In the end, my conclusion was that there isn't any specific extra value in changing this column to some sort of varchar (probably varchar(511)) at the moment.


texts: MAXDOP = 1, Query Hints and Cost Threshold For Parallelism

texts:
If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually go parallel?

I haven’t been able to dig up this specific information although this link suggests that CTFP is ignored if MAXDOP is 1. This makes sense without query hints as no request, regardless of cost, will go parallel when MAXDOP is 1.

Can anyone let me know what the expected behaviour of these two requests will be?

Example 1:

Instance Maxdop: 1
CTFP: 50
Query hint: Maxdop=2
Query cost: 30

Example 2:

Instance Maxdop: 1
CTFP: 50
Query hint: Maxdop=2
Query cost: 70


texts: Full text search through many tables in MySQL

texts:
We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its own table(s).
I decided to create a table that holds all of contents from all types:
CREATE TABLE full_text_search
(
    master_id INT NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( master_id )
);

I generate a unique number as master_id for each content of all types to identify each content_text in full_text_search table.
for example:
News table:
+----+-------------+---------+---------+----------+------------+
| id | news_title  | lead    | subtitle|  content | master_id  |
+----+-------------+---------+---------+----------+------------+
|  1 |  sometitle  |some lead| subtitle|content 1 |     3      |
|  2 |  some title |some lead| subtitle|content 2 |     5      |
+----+-------------+---------+---------+----------+------------+

article table:
+----+-------------+---------+------------------+---------+------------+
| id | title       | author  | short description| content | master_id  |
+----+-------------+---------+------------------+---------+------------+
|  1 |  sometitle  | someone | very short desc  |content1 |     1      |
|  2 |  some title | otherone|  some short desc |content2 |     4      |
+----+-------------+---------+------------------+---------+------------+

As you can see master_id is unique between above tables. When ever a new content from each type inserted, also I should INSERT it in to full_text_search table.
QUESTIONS

For many inserts for a day(about 3000 from all types), is it a good solution or it is anti pattern?
Is it better choice if I separate this table from my other tables, and put it in any other DB such as other RDBMS or NoSQLs?
Any other solutions?


texts: How to sync a table between oracle and sybase

texts:
I have a table in Sybase (destination) that I would like to keep it synced with a table in Oracle (source)

I cannot truncate and populate the table in Sybase because it is used by an application, I can only add new records and update changes, changes only happen to one column.

In my environment I have Sybase, Oracle, SQL Server & .net development

Whats the best, simplest & reliable way to get this task done, should I develop a DTS, a .net app, use linked server of SQL Server or is there a better way?


texts: What are the risks of the parameter PRESERVE_COMMIT_ORDER of Oracle's DBMS_LOGSTDBY?

texts:
We have the following setting, with Oracle 10g:

One primary database
One logical standby database with setup with data guard. The logical standby is only used for generating reports and alike.

Currently the parameter PRESERVE_COMMIT_ORDER of DBMS_LOGSTDBY is set to true. In order to enhance the speed of data replication, we are thinking of setting it to false. But what are the risks, if any, of doing so? (Oracle says: "See the Usage Notes for details and recommendations." ... but I couldn't find these recommendations.)


texts:
I am using the following code to delete a record. It works fine, but MySQL always returns a blank error message. PHP returns "Error: " instead of "1 Record Dropped".

$eventid = intval(mysqli_real_escape_string($dbconnect, $_POST['eventid']));
mysqli_query($dbconnect,"DELETE FROM Events WHERE EventID='$eventid' LIMIT 1");
if (!mysqli_query($dbconnect,$sql)) {
  die('Error: ' . mysqli_error($dbconnect));
}
echo "1 Record Dropped";

When I use the same PHP code and change the SQL to a SELECT or INSERT INTO command instead of a DELETE command, I receive the correct response from PHP.


texts: How do I return an index for each nvarchar col in a table?

texts:
Imagine a simple table as follows

Create table test ( c1 nvarchar(10), c2 nvarchar(10), i1 int, c3 nvarchar(10) );

Is there a way I can query and get The index for the nvarchar columns?

The index would be 0 for c1 and 1 for c2 and 3 for c3 or if it is 1 and 2 and 4 respectively that is also ok


texts: How to grant database creation privilege to a user in MySQL

texts:
Following command provides all privilege in all databases for User 'admin'. But it doesn't allow 'admin' to create database.

GRANT ALL ON * . * TO 'admin'@'localhost';

How can I provide the access for 'admin' to create database?

As I am trying to Create database, I am getting the following error -

  ERROR 1044 (42000): Access denied for user 'admin'@'localhost' to
  database 'newdb'


texts: Create a table with all the columns of inner join of two other tables without copying data

texts:
I have two tables Table1 and Table2 with 10,000 and 15,000 rows each. I want to create a table Table3 that can contain all the data of inner join of Table1 and Table2, yet is empty. I tried something like this:

CREATE TABLE Table3
(LIKE Table1 INNER JOIN Table2 ON Table1.col11 = Table2.col21);

What is the best solution I can have?


texts:
I generally have to restore databases to different servers after important data imports or other important and delicate database operations.
I need to be sure that my backups up to that point in time or transaction mark are reliable.
I don't have the time and the disk space and to do a full restore followed by a dbcc checkdb to completely verify the backup files (including corruption).
The RESTORE VERIFYONLY will check for :

backup set is present   and header fields of pages

Are volumes readable?

checksum (if enabled , BACKUP by default does not use CHECKSUM)

destination drive – space check.

I use a lot the following query:
--query by Jack Vamvas - any suspect database?
SELECT DB_NAME(database_id),[file_id],page_id,
CASE event_type
   WHEN 1 THEN '823 or 824 or Torn Page'
   WHEN 2 THEN 'Bad Checksum'
   WHEN 3 THEN 'Torn Page'
   WHEN 4 THEN 'Restored'
   WHEN 5 THEN 'Repaired (DBCC)'
   WHEN 7 THEN 'Deallocated (DBCC)'
END,
error_count,
last_update_date
FROM msdb..suspect_pages

Other than restore verify only with checksum, is there any other way to check if my backups are reliable?


texts: Access Denied to users after upgrading mysql to 5.5.40

texts:
I recently upgraded mysql from 5.1.0 to 5.5.40 but suddenly some users seem to have lost the previlages. All the projects are live and those with issues are showing database connection error. How can I fix this?? Thanks in advance.

I also noticed that the sites that have problem are using MYISAM engine.

Following is the error message:

MySQL Error : mysql_connect() [function.mysql-connect]: Access denied for user 'xxx'@'xxx' (using password: YES)


texts: Execution plan doesn't use clustered index!

texts:
I'm using "SQL*Plus: Release 10.2.0.1.0 - Production" and have a table foo with a field fooID.

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

And I do the following:

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

which creates the following plan:

0  SELECT STATEMENT
1 0   TABLE ACCESS FULL FOO

then I add the following hint:

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

but still got the same plan without using my index.

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

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


texts: Calling a MySQL stored procedure with parameters in SSRS reporting through ODBC

texts:
In SSRS, when I pass hard-coded values to my stored procedure, it works fine. But it doesn't work when I try to pass the parameters.

Can you please tell me the right syntax for calling MySQL stored procedures in SSRS, through an ODBC datasource?


texts: Effect of an index on update statements where update column is not in an index

texts:
I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute.

While tuning my database to improve performance, I keep coming across this situation that seems to contradict that rule logically for me, and nowhere can I find anyone say or explain in any way otherwise.

In SQL Server, and I believe/presume most other DBMS, your indexes are created based on specific columns you specify. Inserts and deletes will always affect an entire row, so there is no way they will not affect the index, but updates seem a bit more unique, they can specifically affect only certain columns.

If I have columns that are not included on any index and I update them, are they slowed down just because I have an index on other columns in that table?

As an example, say in my User table I have one or two indexes, the primary key which is an Identity/Auto Increment column, and possibly another on some foreign key column.
If I update a column without the index directly on it, like say their phone number or address, is this update slowed down because I have indexes on this table on other columns in either situation? The columns I am updating are not in indexes, so logically, the indexes should not be updated, shouldn't they? If anything, I would think they are sped up if i use the indexes in the WHERE clause.


texts: Why none of the sql_mode(s) are recognized by MySQL?

texts:
When trying to launch MySQL from the terminal using mysql -u <username> -p<password> I get the following error:

  mysql: [ERROR] unknown variable 'sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Out of the possible three places that MySQL is looking for the .cnf file (found them by running sudo /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"), only /etc/mysql/my.cnf exist. In it there is only a directory including !includedir /etc/mysql/conf.d/. There, in /etc/mysql/conf.d/mysql.cnf there are the following lines:

 [mysql]
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Looked around for answers, couldn't find any. Tried, one by one, to rule out specific "bad mode" from the list of modes, they're all unrecognized.

Only by completely commenting out this line, I was able to login and launch MySQL.

Does anyone knows what the problem might be?

Platform and OS aspects:

The MySQL version I am using is 5.7.17
The operating system is Lubuntu version 16.04, 4.7.3-generic, x86_64


texts: Is there a more efficient way to join a big table that this?

texts:
Consider the following. On an e-commerce site I have a sales_order table and a subscription table.

The sales_order table has well over 1 million rows and about 25+ columns. The columns I'm interested in are:

+-----------+---------------+-----------------+
| entity_id | base_subtotal | subscription_id | + many more
+-----------+---------------+-----------------+

In my subscriptions table I have the following

+-----------+----------+----------+
| entity_id | interval | quantity | + more
+-----------+----------+----------+

So basically the subscription created orders in the order table which and the sales_order.subscription_id is used to reference.

I want to retrieve the SUM value of the base_subtotal for each subscription.

+-----------+----------+----------+-----------------+
| entity_id | interval | quantity |  total_revenue  |
+-----------+----------+----------+-----------------+

SO I have the following...

SELECT `main_table`.*, `orderTable`.`base_subtotal` AS `revenue`, SUM(orderTable.base_subtotal) AS `total_revenue`
FROM `subscriptions` AS `main_table`
LEFT JOIN `sales_order` AS `orderTable` ON main_table.entity_id = orderTable.subscription_id
GROUP BY `main_table`.`entity_id`;

This gets the correct data but takes AGES on the live server with the million+ rows.

There is no index on the subscription_id in the sales_order table!

So two questions:
 - 1. Is there a more efficient way to write this query?
 - 2. Is it 'safe' to add an index to a huge table with like this without bringing it down? This would need to be done to a live db so I'm very cautious about it.

Thanks


texts: Case-folded sorting of output columns containing expressions

texts:
Using the following query as simple example,

    SELECT  l.id,
            string_agg(lc.country, ' ' ORDER BY lc.country) AS countries
      FROM  licenses l
            JOIN license_countries lc ON lc.license_id = l.id
     WHERE  l.id > 80
  GROUP BY  l.id

what is the preferred way of sorting the result by the second column in a case-insensitive manner?

A straight ORDER BY LOWER(countries) is not possible, because the expressions in ORDER BY cannot refer to output column names. I can think of several ways to solve this, but I am not sure what the pros/cons are:

Wrap this query into a subquery and SELECT * from it; then ORDER BY LOWER(countries) would work fine.
Copy the string_agg() expression to ORDER BY but apply LOWER() to it.
Add another output column - same as the second line, but with LOWER() applied - then sort by that column.

I'm currently using the first option with the original query added in a CTE (WITH query). Is my assumption correct that this would typically have the least impact on performance?

I've run into similar situations before, usually involving something like SELECT …, COUNT(*) as num_rows … ORDER BY COUNT(*) …. This is short enough, so the duplication of the expression is not as conspicuous, but it now occurs to me that this may not be clean either.


texts: SQL Server Failover Cluster Instances: why current vote is 0?

texts:
I set up a 2 node SQL Server Fail-over Cluster Instances. Each node is EC2 instance.
At the moment, under "Cluster core resources", my cluster is online. But one node is online and the other one is offline.

As i checked the Nodes, i noticed that: Both are Up, assigned vote = 1. But one has current vote = 1, the other one has current vote = 0.

If i set a file share witness from 3rd server (the one is outside of cluster and used to manage), the node with current vote = 0 turned to 1.

Why is that?


texts: SQL Stored Procedure with one parameter and several variables that will change over time

texts:
I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in

Error output:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'file20160303FROMx:'.

(1 row(s) affected)

I am really open to any feedback, be it a complete re-design if need be. Still a junior in this field.

Script:

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[procname]    Script Date: 2016-03-03 09:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 --=============================================
 --Author:      <Author,,Name>
 --Create date: <Create Date,,>
 --Description: <Description,,>
 --=============================================
 ALTER PROC [dbo].[procname]
@p1 nvarchar(8)
AS
BEGIN

SET NOCOUNT ON;
Declare @insertvariable NVARCHAR(MAX)
Declare @var1 NVARCHAR (32) = 'x:\xxxx\xxxx\file\err';
Declare @var2 NVARCHAR (15) = convert(varchar, getdate(),112) + '.' + replace(convert(varchar, getdate(),108),':','')
Declare @var3 NVARCHAR (MAX) = @var1 + @var2 + '.log';
Declare @var4  NVARCHAR(MAX)= 'x:\xxxx\xxxx\file';
Declare @var5 NVARCHAR(MAX) = @var2 + @p1 + '.tab.txt';
Declare @formatFile NVARCHAR (MAX) = 'x:\xxxx\xxxx\formatFile';

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @var3,
   FORMATFILE = @formatFile,
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile
   end


texts: Source control makes sure my code is never lost, how do I make sure my users/orders database is never lost?

texts:
Everyone is recommending the use of git, automated tests, continuous integration, automated deployment and so on, to reduce human error during all stages of web development.
I have yet to come across a clean, automated way of doing this for databases.
Currently, I'm using the Symfony2 framework for PHP and doctrine to create/manage my users database in MySQL. How do I make sure that my users & orders databases will always remain safely backed up without manual intervention?


texts: MYSQL - Why does this SELECT get slower the further down the table?

texts:
My table:

CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`),
  KEY `registration_date` (`user_email`,`user_registered`),
  KEY `user_registered` (`user_registered`)
) ENGINE=MyISAM AUTO_INCREMENT=176329 DEFAULT CHARSET=utf8;

In a stored procedure I need to loop over every user in the table and preform some other queries and I noticed as it gets further in the loop the queires take longer and longer to preform. What I would expect to take a few hours is now taking days...

To loop over one row at a time I am using this query:

SELECT `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT @count,1;

@count starts at 0 and works its way up to the number of users in the database. At a count of 0 the query takes only a few milliseconds but as soon as the count starts increasing the time it takes to do the same query rises to when Im on a count of 170003 for example the query takes over 1 second...

Doing and explain I see:

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 0,1;
1   SIMPLE  wp_users    index   NULL    PRIMARY 8   NULL    1

EXPLAIN SELECT SQL_NO_CACHE `ID`, `user_email` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;
1   SIMPLE  wp_users    ALL NULL    NULL    NULL    NULL    175862  Using filesort

Why is this happening?

I have found that splitting the query into two is quicker:

SELECT `ID` FROM `wp_users` ORDER BY `ID` ASC LIMIT 170003,1;
SELECT `user_email` FROM `wp_users` WHERE ID = 170469;

and only takes a few milli seconds again...

Is doing it the two queries the only quick way of doing this?


texts:
I'm using Ingres Database and I've this procedure that create a text file with a list of tables. How i can call the file into the where condition?

select request_num,table_name,temp_table
from tables
where temp_table in ('$/tmp/dba_tablelist')

Actually when i run the file is fine, the only problem is that the where condition can't see the list of table, also if the table is just one.


texts:
We are having dirty reads or phantom rows in our data warehouse. My question is not how to solve it (it is a long story and I am currently working on it), but I want to know what exactly happens to better understand the behaviour and use the correct terminology. Are these dirty reads or phantom rows?
Let's say we have SourceTableA in the ERP database with a CLUSTERED UNIQUE index on Col1 and Col2. The DWH extraction process that performs the following query:
SELECT Col1, Col2, Col3
FROM ERPdatabase..SourceTableA WITH(NOLOCK);

This is not recommended, because now we can even have dirty reads, but we are forced to do this by the ERP vendor. The vendor knows the implications and uses READ UNCOMMITTED for all their transactions and forces us to use the NOLOCK hint.
Say we have RowA in SourceTableA:
('GreatCompany','WonderfullItem','ColorRed')

Now (I think...) a user session in the ERP system performs an update on RowA while the previous query is running like this:
UPDATE SourceTableA
SET Col3 = 'ColorBlue'
WHERE Col1 = 'GreatCompany'
AND Col2 = 'WonderfullItem'

If I am correct or not, what I see happening is that 2 rows are being read by the extraction process. I am not surprised since we use NOLOCK (although I am unsure if I understand the exact cause, hence this question), but I have 2 questions about this:

Since the row is not moved around in the CLUSTERED index, because the primary key does not change, why is it read a second time? My thinking would be that the extract process is already past that row, so it should not encounter it anymore. Or is that not how it works? The only explanation I can think of is that the extract query is not using the CLUSTERED index to read the data, but maybe a NONCLUSTERED index where the row IS moved around. Or what else am I missing? Or is it just a dirty read? Or is it just 'SQL Server does not guarantee row order unless you use an ORDER BY, DISTINCT or do not use NOLOCK'? Then I am curious why exactly. :-)

Regarding dirty reads, I always thought the behaviour I see were phantom rows, but, to my understanding, for a phantom row to occur we actually need 2 statements within the same transaction that read the same set of data. That is not the case here since it is only 1 statement, so implicitly I think that this should then be a dirty read? On the other hand, for a dirty read to occur we need an update to be rolled back by another transaction. That is not what I assume is happening. Or is that actually the only explanation?


texts: How can I restore SQL Server database to a custom location, if the backup file was renamed?

texts:
Suppose I have an SQL Server database called bubu and the respective backup file is bubu.bak.

If I want to restore it with the backing store in a custom location, I use the following T-SQL statement:

RESTORE DATABASE "bubu2" FROM DISK = N'C:\bubu.bak' WITH REPLACE, STATS = 10,
    MOVE N'bubu' TO N'C:\custom\bubu.mdf', MOVE N'bubu_log' TO N'C:\custom\bubu.LDF'

And so I have a new database bubu2, which happens to be the backup copy of bubu. The important thing here is that I assume that the backup contains the logical files bubu and bubu_log, because the backup file is named bubu.bak.

Now, suppose someone renames bubu.bak to mumu.bak. Doing so, breaks my logic, since now I will try this T-SQL statement:

RESTORE DATABASE "mumu2" FROM DISK = N'C:\mumu.bak' WITH REPLACE, STATS = 10,
    MOVE N'mumu' TO N'C:\custom\mumu.mdf', MOVE N'mumu_log' TO N'C:\custom\mumu.LDF'

And of course, I fail, because mumu.bak does not contain the logical files mumu and mumu_log - renaming the backup file does not rename the logical files contained in the backup (of course).

But then, how do I do what I need if the backup file was renamed?


texts: How to do automated archiving for a SQL Server database based on interpretation of database metadata?

texts:
I am looking for a solution giving me automated archiving of SQL Server databases based on a smart interpretation of table relationships that is able to archive records from related tables minimizing (or making unnecessary) to write custom ETL code.

So far the only solution I have been able to find (by Google) is Informatica Data Archive

In the DataSheet I can read:

  Smart Automated Partitioning: This edition automates database
  partitioning based on business entities, placing records from related
  tables in the same table space, not just individual tables. Your IT
  team can leverage underlying database partitioning functionality to
  manage both partitioning and archiving from a central console.

Sounds pretty much like what I am looking for...¿Are there any others like this from other vendors? According to this book there should be many tools offering this.. but I can not find anything...

If there is no other prepackaged solutions I would also be interested in information on how to build my own... (althought I realize it might not be feasible)

Basically I am looking for a tool that enables a developer to do this (obtained from here):

The ETL developer defines the source and destination of the extraction process, including servers, relational databases, and tables.
For each table, the schema (columns, index, and constraints) can either be automatically retrieved by the system or manually specified by the ETL developer.
The ETL developer specifies the mapping between source and destination at the database, table, and column level.
The ETL developer selects either a full or delta load.
The ETL developer configures an orchestration process. For each table, ETL developers define one or multiple steps for performing the extraction.
The ETL developer specifies how ETL packages should be executed—at a scheduled time or on demand.
The system dynamically generates one or more SSIS packages.
The system deploys the SSIS packages to a distributed execution environment.
The system executes ETL jobs and captures the job status.

I found this abandoned opensource project: MDDE unfortunately it is also pretty much undocumented (this is the usage information I could find) unsupported and obsolete (apparently is designed to work only with SQL Server 2005 and I need something for SQL Server 2008 R2)

OTOH: I am surprised that there is no "archiving" tag in this site...


texts: How to get SQL Server 2012 to use the invariant culture in format()?

texts:
This has now been posted to Connect: The invariant culture identifier is rejected by the FORMAT() function in SQL Server 2012. (Connect has been retired.)
  This has now been posted to feedback.azure.com.

I'm trying to get the built-in format() function in SQL Server 2012 to use the invariant culture.

It is said in the documentation that the function accepts a .NET culture identifier as the third parameter. The identifier for the invariant culture is a blank string:

  You specify the invariant culture by name by using an empty string ("") in the call to a CultureInfo instantiation method.

That does not work with SQL Server however:

select format(getdate(), N'g', '');

  Msg 9818, Level 16, State 1, Line 1
  The culture parameter '' provided in the function call is not supported.

It is also documented that the invariant culture is associated with the English language, but not with any country/region. One would think this allows to pass 'en' as the identifier, but then, in .NET, CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("")) yields true, but CultureInfo.InvariantCulture.Equals(CultureInfo.GetCultureInfo("en")) gives false, so they aren't really the same.

So how do I make SQL Server to use the invariant culture?

(Note: I'm interested in making the built-in thing to work. I already have my own CLR functions to do this, I was going to remove them in favor of the now-built-in functionality).

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
      Dec 28 2012 20:23:12
      Copyright (c) Microsoft Corporation
      Business Intelligence Edition (64-bit) on Windows NT 6.0  (Build 6002: Service Pack 2) (Hypervisor)


texts:
I have a table like this

CREATE TABLE `agentpositions` (
    `idPosition` INT(11) NOT NULL AUTO_INCREMENT,
    `idAgent` INT(11) NULL DEFAULT NULL,
    `hardwareSerial` VARCHAR(128) NOT NULL,
    `LatitudePoint` DECIMAL(18,12) NOT NULL,
    `LongitudePoint` DECIMAL(18,12) NOT NULL,
    `PointDate` DATE NULL DEFAULT NULL,
    `TimeStamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ...
    PRIMARY KEY (`idPosition`),
    INDEX `idAgent` (`idAgent`),
    INDEX `idagent_pointdate` (`idAgent`, `PointDate`) -- next one is getting ignored and this one is getting used
    INDEX `pointdate_idagent` (`PointDate`, `idAgent`) -- this is getting ignored
)
ENGINE=InnoDB

we query this table with both (idAgent,TimeStamp) and because this table is going to get very big, we put index on both of them

but for sake of architecture (we think date-int is faster and more optimized than timestamp-int, is that event true?? ) we avoided putting index on timestamp, instead we added another field which stores only date of the timestamp, and added index on that, but I don't know why it ignores my index

my query looked like this:

SELECT idPosition , idAgent , LatitudePoint , LongitudePoint , TimeStamp AS StartTime
FROM `agentpositions` `t` IGNORE INDEX(`idAgent`, PRIMARY)
WHERE (
   t.idAgent=880 AND t.PointDate >= "2015-04-04"
   AND (t.TimeStamp >= "2015-04-04 00:00:00" and t.TimeStamp < "2015-04-04 23:59:59"))
   AND t.LatitudePoint != 0 and t.LongitudePoint != 0
ORDER BY t.TimeStamp

so after not identifying the index, I managed to change it to this which uses that index!

    SELECT idPosition , idAgent , LatitudePoint , LongitudePoint , TimeStamp AS StartTime
FROM agentpositions t IGNORE INDEX(idAgent, PRIMARY)
WHERE
t.idAgent=880 AND t.PointDate in ("2015-04-04","2015-04-05","2015-04-03" )
AND t.LatitudePoint != 0 and t.LongitudePoint != 0
order by t.TimeStamp

I want to be able to give time range like >='2014-01-01'

what is wrong with this index? how can I achive this?

UPDATE : I totally forgot to include my first index that did not get used, I updated the table


texts: Access to performance counters from T-SQL procedure

texts:
I want to write a stored procedure, which is returning CPU utilisation, Memory utilisation and Count of rows in table T1. So it should look like:

SELECT cpu, memory, (SELECT COUNT(*) FROM T1) as T1RowCount
FROM perfmon

But the problem is that I can't access cpu and memory from script: I looked at sys.dm_os_ring_buffers, sys.dm_os_performance_counters and others, but they don't provide actual information (for example, every counter in Resource Pool Stats group always returns 0).

Another workaround is configuring PerfMon to write directly in SQL table and then just fetch them in this stored procedure, but it has two minuses: it's complex and it requires external tool configuration when I really want to collect required info using vanilla T-SQL.

I have a program which is reporting its health (CPU usage, Number of allocated objects of type T and so on). But I currently do not provide such information about SQL Server which may be installed on another machine so I want to. But for unknown reason Resource Pool Stats object just do not provide any information: counters are just empty.

Rowcount tell me how many elements are not processed. Rows are added by producer and are deleted by consumer. So if this number is 0 then producer is not working. If this number is growing then something happened with consumer... There are several cases when this information may be useful.

What am I currently getting from counters is... nothing:


texts: Data for the latest previous effective date sub query

texts:
Imagine issues with linked

Time entries to track the time spent
Journal entries to track progression evolution

(Actually a part of Redmine schema)
Let's take some sample data:
Issues

id
subject
estimated_hours

1
First issue
4

2
Second issue
8

Time entries

id
issue_id
spent_on
hours

1
1
2020-12-02
1

2
1
2020-12-05
1.5

3
2
2020-12-06
0.5

4
1
2020-12-10
1

5
2
2020-12-10
1

6
2
2020-12-15
2

7
2
2020-12-15
1

Journal

id
issue_id
updated_on
done_ratio

1
1
2020-11-20
0

2
2
2020-11-20
5

3
1
2020-12-01
20

4
1
2020-12-03
40

5
2
2020-12-06
30

6
1
2020-12-07
70

7
1
2020-12-11
100

8
2
2020-12-12
50

Now for each time entry I need to know the latest done ratio.
Here is what I'm expecting

id
subject
spent_on
hours
done_ratio

1
First issue
2020-12-02
1
20

2
First issue
2020-12-05
1.5
40

3
Second issue
2020-12-06
0.5
30

4
First issue
2020-12-10
1
70

5
Second issue
2020-12-10
1
30

6
Second issue
2020-12-15
2
50

7
Second issue
2020-12-15
1
50

I thought I could get that with this query:
SELECT
  tie.id
, iss.subject
, tie.spent_on
, tie.hours
, jou.done_ratio
FROM time_entry AS tie
JOIN issue AS iss ON (tie.issue_id = iss.id)
JOIN (
  SELECT
    issue_id
  , updated_on
  , done_ratio
  FROM journal
  ORDER BY updated_on DESC
) AS jou ON (
  jou.issue_id = tie.issue_id
  AND jou.updated_on <= tie.spent_on
)
GROUP BY tie.id, tie.spent_on
ORDER BY tie.spent_on

But it's returning the first done ratio for every time entry.
Here is a playground to fiddle with the mentioned data
How can I get the done ratio of the latest journal prior to (<=) each time entry ?
I'm using mariadb:10.2.9 here but this should pretty much be standard ANSI SQL


texts: Are there mysql client environment variables for USER and DB on Linux?

texts: I see that http://dev.mysql.com/doc/refman/5.7/en/environment-variables.html has an option for setting the USER on Windows, but is it possible on Linux? You can do it via the MYSQL_TEST_LOGIN_FILE, but it's a bit convoluted, and requires setting up files with just the right permissions and one for every user you want to have access. Also, is there an environment variable for what database to start using?


texts: SQLAlchemy: table version already exists. pgAdmin4 fails on Linux Mint 20 and 20.1

texts:
I already found the solution, I just want to share it in case someone has the same problem.
Situation
Install pgAdmin4 version 4.30 on Linux Mint 20 (Ulyana) and Linux Mint 20.1 (Ulyssa) inside virtualenv. The bulk of pgAdmin4 is a Python web application written using the Flask framework. Therefore, it is an ideal candidate to be implemented in a Python virtualenv as indicated in:
How to Install PostgreSQL with pgAdmin4 on Linux Mint 20
And,
Installing pgAdmin 4 on Linux Mint 20 (ulyana)
And in Spanish,
Cómo instalar PostgreSQL con pgAdmin4 en Linux Mint 20
Error
After finishing the process, when trying to run pgAdmin4 for the first time, the following error appears:
Traceback (most recent call last):
  File "venv/lib/python3.8/site-packages/pgadmin4/pgAdmin4.py", line 87, in <module>
    exec(open(file_quote(setup_py), 'r').read())
  File "<string>", line 449, in <module>
  File "<string>", line 372, in setup_db

  [...]

  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/flask_sqlalchemy/__init__.py", line 575, in get_options
    self._sa.apply_driver_hacks(self._app, sa_url, options)
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/flask_sqlalchemy/__init__.py", line 908, in apply_driver_hacks
    sa_url.database = os.path.join(app.root_path, sa_url.database)
AttributeError: can't set attribute

In the second attempt to execute pgAdmin4 the error shown is:
Traceback (most recent call last):
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 681, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: table version already exists

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "venv/lib/python3.8/site-packages/pgadmin4/pgAdmin4.py", line 94, in <module>
    app = create_app()

  [...]

  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 681, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table version already exists
[SQL:
CREATE TABLE version (
    name VARCHAR(32) NOT NULL,
    value INTEGER NOT NULL,
    PRIMARY KEY (name)
)

]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

This situation started to occur in March 2021 and as far as I know, it is identical on both Linux Mint 20 and Linux Mint 20.1.

Note: a more suitable location to put the virtualenv would have been /opt, but for multiple tests I opted for /home from a live USB with Linux Mint 20. Then apply the final changes to my Linux Mint 20.1


texts: How can I create rows without an underlying table in IBM DB2 SQL?

texts:
In T-SQL, I can execute the following:

SELECT      *
FROM        (SELECT 1 AS n UNION ALL SELECT 2 AS n) AS t;

In IBM DB2 SQL, this fails with:

  [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 - Keyword
  UNION not expected. Valid tokens: , FROM INTO.

A SELECT statement seems to require a FROM clause. Indeed, a reductive SQL statement such as:

SELECT 1 AS n

Also errors with:

  [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token
   was not valid. Valid tokens: , FROM INTO.

There is an UNNEST function which can create rows from an array, but it seems the array must already be represented by something like a variable or parameter.

I understand I could work around this by doing a SELECT from an arbitrary table with an impossible criterion such as 1 = 0, but that seems to be a silly requirement.

So how can I construct rows without an underlying table?

(keywords: tally table; derived table)


texts: SQL Server varbinary column modifying inserted data

texts:
We have a table in our database for storing photos, the datatype of the actual photo data is varbinary(max).

When inserting a photo from the application, the data is correctly stored and the photo can be viewed as normal.

However, I am updating another record to have the same photo and when I update/insert the existing data, an extra leading 0 seems to be applied to the data thus breaking the decoding of the image.

When the image is inserted by the application, the binary data is correct and starts with: 0xFFD8

When I copy that binary data, and use an update/insert statement to apply this photo to another record, the data changes and starts: 0x0FFD

This means that the data cannot be converted back to an image.

Google has let me down on this occasion, though it may be that I'm not using the correct search terms.

Any assistance greatly appreciated, and if I can add any more info to assist you, in assisting me, please let me know and I'll add whatever I can.

Link to a pastebin showing data before/after insert/update: http://pastebin.com/7jrtivZ8


texts: Using a NULL value to signify that the value does not mattter. Not that it isn't known

texts:
Say I have a table with computers where one column is a boolean "fan". If true the computer has a fan, if false it does not. It can never be null.
Now I have another table with a list of recommendations for improvements to computers.
I want some recommendations to apply to all computers which have a fan. For this I have a column "fan" which when true will only apply to computers with a fan. When false will only apply to computers without a fan.
Is it good database design to have the column set to null when the recommendation should not care if the computer has a fan or not? As in the recommendation should apply whether the computer has a fan or not.
The purpose of null values is that the column is not known. Which is not the case here. It's not that I don't know if the recommendation is for a computer with a fan or without a fan, it's that I specifically don't care either way. This makes me think that it's not a good idea and is just changing the meaning of null.
On the other hand I think it makes sense that if the "fan" filter on the recommendation isn't defined it means that it doesn't need to filter based on that.
The other way to define the recommendation is have a applies_to_fanned_computers and applies_to_not_fanned_computers columns. If both are true then it applies to either, if only one is true then it applies to one of them.
Which option is best? Am I right that the first option is a bastardization of the purpose of null values in a database?


texts: PostgreSQL visibility of records changes from other transaction inside the current one

texts:
I have a table "tst" which has a "status" column using which we process records in that table.
Once the records are processed we set the status to 'Y';

    #\d tst
                 Table "core.tst"
     Column |  Type   | Modifiers
    --------+---------+-----------
     id     | integer | not null
     status | text    |
    Indexes:
            "amn2_pkey" PRIMARY KEY, btree (id)

Issue: At the start of the long(approx 20 mins) transaction I have 3 records in tst table as shown below

    #select * from tst;
     id | status
    ----+--------
        1 | N
        2 | N
        3 | N
    (3 rows)

But during the transaction I have couple of more records being inserted into the tst table, but I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing.

Session 1:

    DO $$
    BEGIN
        perform pg_sleep(20) ;
        UPDATE amn2 SET des = 'Y' ;
    END $$;

Session 2:

    INSERT INTO tst VALUES ( 4, 'N');

Post the Session 1, all the records were updated to status 'Y' including id 4.

Is this expected? Or some DB/table serializable set up missing?


texts: Grant user permission to create new schema but not view existing schema

texts:
Is there a way to allow the user to create new schemas (and acquire privileges to schemas he created), but not view existing schemas (or a way to blacklist a list of schemas that the user is not able to see)?

The CREATE privilege will allow the user to create new schemas, as well as viewing all the existing schemas (though not altering them), which is undesirable.


texts:
We've experienced an sql injection attack which corrupted our main db.

The main db had two partitioned tables into filegroups.

The corrupt database entered into Suspect mode, and it's been impossible for us to use.

I was able to restore a backup of the db with partial (no filegroups, since we didn't have backup of those.. I know bad thing).

I was now able to remove the db in suspect mode so we are left with the restored db and the file groups that were linked to the corrupt db.

Now I'm trying to figure out how to bring those file groups back online.

I've tried removing them so I can then add them back on, but I can't remove them since I get

Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup 'FG_Audit' because the filegroup is not online.

Is there any work around? I've found posts where they talk about restoring from backup but we don't have one of the filegroups.

Have we lost that data? How do we remove the filegroups? Backups now stopped working because of this.

UPDATE

If I run this

SELECT
 f.name file_group,
 d.name file_name,
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

All the file groups that the DB is complaining about being off line they say "RECOVERY_PENDING"


texts: PostgreSQL: Recommendation size for shared_buffers

texts:
I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago.

I am looking for help about the proper memory values for the configuration file.

Currently, I have a modest dedicated server (2GB of RAM) running Apache, MySQL and PostgreSQL under Linux. The main MySQL database are about 3 times the size of the main one in PostgreSQL, and the same could be said for the data traffic.

I still have the default configuration file for PostgreSQL, having the parameter  shared_buffers = 32MB. I assume that I should increase this value, but I do not want to penalize the other databases. I have read something about dedicating a 25% of physical available memory to PostgreSQL, but I am not sure about that.

The new database contains seven tables, having the biggest one about 3 MM rows. The machine has currently a low traffic (serving about 10000 ~ 20000 selects a day), but these values are expected to grow.

I was thinking about increasing shared_buffers to 256MB, but I would like to hear some opinions from users having more experience with this RDBMS.


texts: What is better in IN()? Sub-query or list of values

texts:
If search or update rows and use where statement IN() - is better to select values with sub-query or define values or there is no efficiency difference between this? For example:

SELECT a FROM b WHERE c IN( SELECT c FROM x )

OR

SELECT a FROM b WHERE c IN(1,2,3,4,5,6,...hundreds values...)


texts:
Does changing the TDE Master Key (DB Master Key and/or the DB encryption key) always require decryption and re-encryption?  If not, at what version did SQL Server begin to allow you to change the Master Key and not have to decrypt/re-encrypt?

My background is in Oracle, which handles TDE a little differently.


texts: Why does my hard-drive fill up when executing a query?

texts:
I have a very complex SQL query that I execute in SQL Server Management Studio. As the query is running, I notice that my harddisk is filling up, until the point it's completely full (I'm talking a 10+GB increase in data).

I tried finding where this data is located, but can't find any large files and trying to find it by modified data through Explorer does not gives me thousands of folders and files. I went through a lot of them, but have a hard time identifying what I'm looking for.

So where does SQL Server typically hold all this data when executing a query?

ps. I know I need to do something about the query itself, but for now I need to know where data is stored.


texts: What is the load impact of the Zabbix DB size query?

texts:
I am in the process of implementing Zabbix monitoring on multiple servers at an organization.  I have recently run into some resistance from an Oracle / MySQL DBA.  He claims that the following query is resource intensive because his database server contains thousands of tables.  It's a one liner, but I've split it across multiple lines to help with readability:

echo "select sum($(case "$3" in both|"") echo
"data_length+index_length";; data|index)
echo "$3_length";; free) echo "data_free";; esac)) from
information_schema.tables$([[ "$1" = "all" || ! "$1" ]] ||
echo " where table_schema=\"$1\"")$([[ "$2" = "all" || ! "$2" ]] ||
echo "and table_name=\"$2\"");" | HOME=/var/lib/zabbix mysql -N

which looks like it reduces to:

select sum(data_length+index_length) from information_schema.tables;

My specific question is, how resource intensive is this query and should the organization be worried about the performance impact it will have on the DB server if it runs once per minute?

As an experienced UNIX/Linux sysadmin and from reading a little bit from the MySQL manual, it seems like it should be simple addition of values retrieved from the already in memory "information_schema", but I'm hoping for an answer that can definitively describe the internal actions MySQL has to take and the associated impact.


texts:
I have a mySQL table with a town_id and 3 categories - cat1, cat2, cat3.

I need to be able to come up with a distinct list of all the categories in the table selected by town_id, not duplicated and in alphabetical order.

Thanks!


texts: Data Relationship between Provinces and Cities

texts:
Does anybody have experience or know how to correctly  design database relationships for where

Provinces ->hasmany cities
City ->hasOne Province

the problem is different cities (based on geography / province location) may have the same name. Thanks for the help.


texts:
What my query is doing:

I have two SQL tables and I want to copy datarows from the one table to the other table. I am doing this for statistics. The source table includes all current member-sessions/logins and with my statistics table I also want to see member-logins of the past. Thus I want to store these data into my bot_sessions_statistics table as well to make sure they are not being updated anymore:

The original query:

INSERT INTO bot_sessions_statistics (member_id, session_token, username, ip_address)
SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
LEFT JOIN
(
    SELECT stats.member_id, stats.session_token, stats.username, stats.ip_address
    FROM bot_sessions_statistics as stats
    WHERE date_active >= date(NOW())
) AS stats
ON sessions.member_id = stats.member_id
WHERE latest_renewal >= date(NOW())
AND stats.member_id IS NULL

The performance of the original query:

Query_time: 86.364613  Lock_time: 0.000085 Rows_sent: 0  Rows_examined: 1088312551

The table structures:

Table bot_sessions:

Table bot_sessions_statistics:

SHOW CREATE of both tables: http://pastebin.com/UUzEPX5v

The performance of single queries:

SELECT sessions.member_id, sessions.session_token, sessions.username, sessions.ip_address
FROM bot_sessions AS sessions
WHERE latest_renewal >= date(NOW())

Returns 44.2k rows (Duration: 0.078s / Fetch: 1.607s)

SELECT stats.member_id, stats.session_token, stats.username, stats.ip_address
FROM bot_sessions_statistics as stats
WHERE date_active >= date(NOW())

Returns 23.3k rows (Duration: 0.047s / Fetch 0.920s)


texts:
I am reading through SQL server books online for the first team whilst learning how to use the service broker and I came across this statement:

  The application that implements the service reads the message, runs the query, and returns the results. All three of these operations take place in the same transaction. If a failure occurs before the transaction commits, the entire transaction rolls back and the message returns to the queue. When the computer recovers, the application restarts and processes the message again.

I am guessing that this will block in the same way as usual transactions? Ie. row/page locks. Am I correct? Is this something that I should worry about or just leave it until I have a problem?


texts: MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

texts:
I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records)

I'm going to be converting this table to InnoDB in a production environment.

The DB is on a cloud server with 8GB of RAM (will likely upgrade to 30GB RAM just for the conversion).

One method to do this is to simple write an ALTER query:

  What settings and configs would be optimal to convert in this way as quickly and smoothly as possible?

Some of the suggested settings I've read:

large innodb_buffer_pool_size (is 4GB the largest possible?)
preread your old myisam data/index files using shell commands
increase innodb_log_file_size (what's best size in the case?)
Do the alter table in X parallel threads, where X is the qty of CPU cores on your server (how can you do this?)
other minor tweaks (innodb_doublewrite=0, innodb_flush_log_at_trx_commit=0)

  Note: Similar question here (but much smaller table example with
  different server size):
  https://stackoverflow.com/questions/2081823/speeding-up-conversion-from-myisam-to-innodb/2081871#2081871

Of course, there are other methods to do this like:

Do a MYSQLDUMP of the old table and reinsert into a new clean InnoDB table,
Run "SELECT * ... INSERT INTO... " query, or
Run "SELECT INTO OUTFILE..." query on old table and then a "LOAD DATA INFILE... " query into the new table

1. How would you configure your settings for the purpose of making this alter go as quickly and as smoothly as possible?

2. Which method do you think is the best alternative?


texts: Update trigger seemingly won't fire if multiple rows are updated

texts:
I have table_a and table_b, below is the data from table_a.

table_a

Bill_No   P_Name  Price   Stock_in  Stock_out  Stock_in_hand  P_Value
1         fish     100       20        10            10          1000
2         water    50        40        30            10          500
3         soda     30        50        10            40          1200
4         cake     5         10        10             0          0

Now whenever Stock_out gets updated I want to insert those updated rows into table_b via a TRIGGER. Bill_No is identity column and primary key here.
This is what I have tried so far.

ALTER TRIGGER [dbo].[copytblab]
   ON  [dbo].[table_a]
   AFTER UPDATE
AS
IF (UPDATE(Stock_out))
BEGIN
  SET NOCOUNT ON;
  INSERT table_b(Bill_No,P_Name,Price,Stock_in,Stock_out,Stock_in_hand, P_Value)
    SELECT i.Bill_No,i.P_Name,i.Price,i.Stock_in,i.Stock_out,i.Stock_in_hand,i.P_Value FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.Bill_No = d.Bill_No
    AND i.Stock_out <> d.Stock_out
END

The code works fine if I update a single row but If i update more than one row at one time then it does not send Stock_out, P_Value and Stock_in_hand values to table_b. I don't understand where the problem is.


texts:
Why MyISAM uses 3 files per table?

I know one of the file contains table definition, another contains primary key and all other indexes, third the data.

What is unclear for me, what is the benefit to keep the data in 3 files, instead in one, or in many - every index in separate file like dbase.


texts:
I have the following database:

id | remote_id | title | description
-------------------------------------
1  | 12341234  | test  | test-description
2  | 4535234   | blah  | testestetetst
3  | 12341234  | test  | test-description2
4  | 3454656   | qsffq | testqfsfqgfgsdffgfd

I'm trying to select the description (and ONLY the description) of each unique remote_id. The 'winning' description should be the highest id. like this:

id | remote_id | title | description
-------------------------------------
2  | 4535234   | blah  | testestetetst
3  | 12341234  | test  | test-description2
4  | 3454656   | qsffq | testqfsfqgfgsdffgfd

using the following code returns each unique remote_id correctly

SELECT DISTINCT(remote_id) FROM table ORDER BY id DESC

but this returns the duplicates

SELECT DISTINCT(remote_id),description FROM table ORDER BY id DESC

How do can I filter distinct values on remote_id, but only select the description with the highest id in my query?

any help would be greatly appreciated!

EDIT 1: formatting

EDIT 2: using following version: 5.7.27-0ubuntu0.18.04.1


texts: Is there any interruption of service when switching recovery models?

texts:
I need to switch to FULL recovery model for several SQL Server 2008 databases. However these are in production. Whilst doing these things out of hours is good practice I wanted to plan for any down time or interruption in service, no matter how brief.

Does anybody know the impact changing the recovery model to Full has on a production database?

Clarification: I have a whole process scripted (backup's and logging) and tested for log shipping and restoring the other end. However the one thing I couldn't find details on was if changing the model would have an impact on availability or performance during the process.


texts: SQL Server 2005 - Generate and Bulk Insert 3 million rows across 2 tables

texts:
I have a task to programmatically generate 3 million different rows of data and insert them into a database table for test purposes.

I have written a VB.NET program that handles the generation of row data which can then be either INSERT-ed a row at a time straight into my database or written as a comma delimited row in a .csv file.

My issue is that some of the rows required a joining row added in another table, and the join is done an IDENTITY column value from my first table, and I won't know what that value is until after the row has been inserted.

So I have been using the VB.NET program to insert rows one by one, adding the joining row in the other table if required. This was working at a reasonable speed until the main table reached around 2 million rows, at which point it started taking much longer to INSERT a new row.

What is the best practice for achieving my goal? I have looked a little at Bulk Insert and OPENROWSET, but cannot figure out how to use them to populate two tables with different amounts of rows and a joining column which I don't know the value of.


texts: Returning case-sensitive input of case-insensitive array match

texts:
Taking a JSON table with some simple data:

CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings(d) VALUES('{"id":1,"email":"Test1@test.com"}');
INSERT INTO jsonthings(d) VALUES('{"id":2,"email":"Test2@test.com"}');
INSERT INTO jsonthings(d) VALUES('{"id":3,"email":"Test3@test.com"}');

I have a requirement to obtain the rows which case-insensitively match a set of email addresses:

SELECT d FROM jsonthings
WHERE (d->>'email')::CITEXT = ANY(ARRAY['TEST1@TEST.COM','test3@test.com']::CITEXT[]);

However, the data I return should contain the email addresses in the case in which they were passed to the query rather than the case in which they are stored within the database. For example, the above query provides the results:

{"id": 1, "email": "Test1@test.com"}
{"id": 3, "email": "Test3@test.com"}

But I need it to return the results:

{"id": 1, "email": "TEST1@TEST.COM"}
{"id": 3, "email": "test3@test.com"}

How can I obtain the matching item in the array and use it in the results?


texts: Does SQL Server cache the result of a multi-statement table-valued function?

texts:
A multi-statement table-valued function returns its result in a table variable.

Are these results ever reused, or is the function always fully evaluated every time it is called?


texts: How to avoid row locks on both tables joined in an update?

texts:
I have little knowledge about MySQL internals, but I'm experiencing that in a query like:

UPDATE t1
  JOIN t2 ON t1.id = t2.id
SET t1.a = x, t1.b = y, [, ...]
WHERE [ ... ]

rows from both t1 and t2 are locked (they're blocking other SELECTs), although values from table t2 are only read in, they never change.

Since I usually do updates joining critical tables (that are involved in several queries and whose data is most of the times only read, I must get rid of those locks: is there any way to do that?
Should I rewrite those updates with subselects instead of joins?


texts: how to check whether or not queries are doing partition elimination when accessing my partitioned table?

texts:
I am considering partitioning a table.

I am trying to think in terms of how we query that table so that I achieve partition elimination.

Suppose I have a table that has a partition by month, what would I do to cause a partition elimination in my queries?

how could I test whether or not the query is causing partition elimination?

I heard I could even use a calculate field, as long as it is persisted.


texts: Storing the last updated date of a row in a different table

texts:
I need to do a daily export of all of the new rows or updated rows from a legacy database into the new database.

The problem I am running into is that not all of the tables on the legacy database have last modified date columns and we don't want to break anything by adding new columns to the legacy tables.

The worry is that since there are a lot of different processes inserting records, that the columns might not have been specified in those processes, and since we are switching to the new schema it's not worth it to rewrite those processes yet.

See this code contributed by Evan Carroll as to the problem caused by adding another column.

CREATE TABLE f (a int);
INSERT INTO f VALUES (1);
ALTER TABLE f ADD b int;
INSERT INTO f VALUES (2);

This causes the error,

Error Code: 1136. Column count doesn't match value count at row 1

My current idea is to to create a new table in the legacy database and then create a trigger on the old table to insert the date and primary key of any new rows created or updated in the old tables.

Then I can export just the new/updated rows and import them into the new database.

Would there be better ways of doing this?


texts: Say I'm using SQL Server and have set sa password in security. Can the database files be copied to another computer (running same SQL Server setup but with different sa password security) and restored as database of same name and get access to all the data?


texts: Local login impersonation not working with a linked server

texts:
I have created a linked server on my server using the following:

exec sp_addlinkedserver
    @server=N'MyOldDB',
    @srvproduct=N'',
    @provider = N'SQLNCLI',
    @datasrc=N'MYSERVER',
    @catalog=N'TheDefaultDatabase';

Now, despite the following description of how logon credentials are determined (from MSDN):

  A default mapping between all logins on the local server and remote
  logins on the linked server is automatically created by executing
  sp_addlinkedserver. The default mapping states that SQL Server uses
  the user credentials of the local login when connecting to the linked
  server on behalf of the login. This is equivalent to executing
  sp_addlinkedsrvlogin with @useself set to true for the linked server,
  without specifying a local user name.

I am getting the following error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

When I try to do this:

select top 10 * from MyOldDB.TheDefaultDatabase.dbo.Batches

Can anyone point me in the right direction here?


texts:
I'm creating a simple query, a simple select like select x,y,z from etc etc but in the software, there are start date and end date.

If the user fills in only the first date, then it will use a where clause like this:

where....
and...
and x.date >= Startdate

If the user fills in both dates, it will use:

where....
    and...
    and x.date between @Startdate and @enddate

If the user fills in only end date:

 where....
    and...
    and x.date < @enddate

I'm getting a syntax error with everything I'm trying to do.

 where....
        and...
        case when @startdate is not null and @enddate is null
           and x.date >= Startdate

Or even using if. What is the correct syntax for this? Or this is something programmers need to fix inside the software's code?


texts: Count all products (with qty > 0) group by all brands. But get aslo count zero for brand which have 0 products

texts:
I have table:

brands

brand_id  name
1         Apple
2         Sony
3         Nokia
4         HTC

products

id  brand_id qty
1   1        3
2   3        0
4   1        1
2   2        1

I need to get (all brands from brands table and products.qty > 0)

brand_id   count_products
1          2
2          1
3          0
4          0

I tried

SELECT `p`.`brand_id` AS `id`,
       COUNT(p.brand_id) AS COUNT
FROM `products` AS `p`
WHERE AND `p`.`qty` > 0
GROUP BY `p`.`brand_id`

But I get results only for brands that available in products_table.
Thanks for help.


texts: Query Optimizer using clustered index seek the wrong time

texts:
I am supporting a SQL Server 2012 Enterprise edition with SP3. One of the tables in our ERP database has 8 million rows.  It has a clustered index and 1 non CI. For simplicity, let's say the clustered index is on column Size and the non CI is on columns Size, Weight, Color.

We have a query that goes like:

select top 1 from table_name where size=@P1 and weight=@P2 and plant=@P3

It's a Prepared object type. Historically, the query optimizer does an Index Seek on the non CI and do a key lookup on the CI.

Earlier this week, users started complaining that the database response time seems to be slower than normal.

When I look at the plan cache, this is when I found out the query optimizer has started doing a index seek on the CI instead.  You would think it should be faster but the I/O cost is over 200.  When I look at the table, there is only one distinct Size value (10) in the table. So now, SQL server has to traverse the 8 million rows to find the second and third fields in the predicate.

I did a DBCC FREEPROCCACHE and when I look back at the plan cache it went back to using the non CI which made the query fast again and users happy. I am able to reproduce the issue by clearing the cache and running the query with different parameters.

Would this be a SQL Server query optimizer bug?


texts:
I have an aggregate query that has a lot of columns and generate a huge data set:

SELECT      column1,
            column2,
            ...
            column20,
            sum(column21),
            ...
            sum(column40)
INTO        #Output
FROM        #Ledger
GROUP BY    column1,
            ...
            column20

The input table (#Ledger) has 18m rows and the result table (#Output) has 600k rows. The query took 9 minutes. Is there anything I can do to make it faster? Here's the execution plan:

There's no index on the #Ledger table and the exclamation point in Sort gives the following warning:

  Operator used tempdb to spill data during execution with spill level 1 and 4 spilled thread(s), Sort wrote 1541592 pages to and read 1541592 pages from tempdb with granted memory 3752160KB and used memory 3681824KB


texts: Why is ASC 100x faster than DESC and what can I do about it?

texts:
I'm on the cusp of understanding this, but I don't understand the fix that I need. The following query takes about minute, give or take, to return 20 records:
explain (analyze, buffers)
select "search_docket"."id"
FROM "search_docket"
WHERE "search_docket"."court_id" = 'delctcompl'
ORDER BY "search_docket"."id" desC
LIMIT 20;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2791.05 rows=20 width=4) (actual time=74.950..41059.055 rows=20 loops=1)
   Buffers: shared hit=38524709 dirtied=10
   ->  Index Scan Backward using search_docket_pkey on search_docket  (cost=0.56..8837737.95 rows=63342 width=4) (actual time=74.947..41059.022 rows=20 loops=1)
         Filter: ((court_id)::text = 'delctcompl'::text)
         Rows Removed by Filter: 41862720
         Buffers: shared hit=38524709 dirtied=10
 Planning time: 0.450 ms
 Execution time: 41059.233 ms

Flipping the sort order makes it take about 60ms:
explain (analyze, buffers)
select "search_docket"."id"
FROM "search_docket"
WHERE "search_docket"."court_id" = 'delctcompl'
ORDER BY "search_docket"."id" asC
LIMIT 20;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2791.05 rows=20 width=4) (actual time=63.701..63.939 rows=20 loops=1)
   Buffers: shared hit=45783
   ->  Index Scan using search_docket_pkey on search_docket  (cost=0.56..8837737.95 rows=63342 width=4) (actual time=63.698..63.933 rows=20 loops=1)
         Filter: ((court_id)::text = 'delctcompl'::text)
         Rows Removed by Filter: 67080
         Buffers: shared hit=45783
 Planning time: 0.426 ms
 Execution time: 63.971 ms

You can see that in the first one, it gets nasty right here:
         Rows Removed by Filter: 41862720
         Buffers: shared hit=38524709 dirtied=10

Here are the indexes on the table (omitting ones that are not related to this query):
Indexes:
    "search_docket_pkey" PRIMARY KEY, btree (id)
    "search_docket_7a46e69c" btree (court_id)
    "search_docket_court_id_2d2438b2594e74ba_like" btree (court_id varchar_pattern_ops)

Stray thoughts:

The court column has very low cardinality. It has about 500 values across about 50M rows.

I guess I could add a descending index on court_id, and maybe that'd fix it, but that doesn't seem right.

Maybe I need a multi-column index here on search_docket.id and search_docket.court_id? Seems off.

Is there something better I should be doing here that would make the index work better?


texts: Is Oracle 12.2 backward compatible with Oracle 12.1

texts:
Current Oracle DB I am using is 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit' and we have a use case for using 'Oracle 12.2.X.X.0'. We want to use the elongated object name lengths of 128 which come with Oracle 12.2

Is Oracle 12.2 backward compatible and supports everything on Oracle 12.1? Like Stored Procs, JBDC connections etc.

If new tables & columns with long names on 12.2 start coming in, Can those tables be used in conjunction with tables created on 12.1 for joins etc., and can they start residing in the same schemas?


texts: How to convert a VARCHAR DD/MM/YYYY H:MM:SS AM into datetime and then to Json date?

texts:
So I know this question is crazy. I just spent days trying to solve this but i think it's impossible.

I have a table with a CreatedDate as a VARCHAR field.

10/7/2019 7:12:59 AM

What I need to to is to convert this date into those dates we see on MongoDB or Json files. It should be like this:

2019-07-04T15:35:41.958UTC-03:00

is there a way to do this? I tried to convert to datetime, I tried substring, left right. I can force the -03:00 at the end.

SELECT CONVERT(VARCHAR(33), getdate(), 126)

With this I can convert a datetime field and thats the problem.


texts: How can I track down what's filling my 'tempdb' transaction log?

texts:
We have a large and fairly well tuned SQL Server 2016 Enterprise Edition. Given the number of cores we have, our tempdb is currently comprised of 16 2,950 MB files running on a ram disk with a 16 GB transaction log file. All of the files are set to not autogrow and, as a general rule, this has run fine.

We have recently started to get some random "transaction log for tempdb database is full" errors. There's no set time when this is occurring so it is probably some user interaction. Since all interaction is through stored procedures, it's most likely some strange set of parameters or data that's causing the problem but we are unable to track down exactly what might be causing it. Any suggestions that might help us identify the culprit are appreciated.

I have been using the information in How to identify which query is filling up the tempdb transaction log? to track this down. And that gives valuable information but it doesn't give me a way to actually determine what the spurious circumstances are that is causing the transaction log to fill up.

Is there some way to trigger a snapshot or log what is actually causing the issue. Worst case I can write a monitoring program that pings the server every half second and uses variations on those queries to capture anything that is huge and has an open transaction but that still doesn't guarantee that I will actually catch whatever is causing the issue.


texts:
I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:

╔══════════╦══════════╗
║ PersonID ║ OptionID ║
╠══════════╬══════════╣
║        1 ║ A        ║
║        1 ║ B        ║
║        2 ║ C        ║
║        3 ║ B        ║
║        4 ║ A        ║
║        4 ║ C        ║
╚══════════╩══════════╝

I need to pivot that data so the results are:

╔══════════╦═════════╦══════════╦══════════╗
║ PersonID ║ OptionA ║ Option B ║ Option C ║
╠══════════╬═════════╬══════════╬══════════╣
║        1 ║       1 ║        1 ║          ║
║        2 ║         ║          ║        1 ║
║        3 ║         ║        1 ║          ║
║        4 ║       1 ║          ║        1 ║
╚══════════╩═════════╩══════════╩══════════╝

Any suggestions?


texts:
I've been tasked with writing an analysis of a database that is currently used on a small scale. The intent of the document is to show to the business (as well as technically) that the database can scale to support other business divisions. I've struggled to find any database documentation standards on the web. It seems as though most databases are not documented; let alone have a basic ER diagram.

I'm more specifically concerned with the technical side and this is the outline I've come up with:

Non-functional Requirements

Storage needs
Server reliability

Schema Design & Normalization

Relational Integrity
Domain Integrity

Data types
(FOREIGN KEY, DEFAULT, NOT NULL constraints)

Referential Integrity

Parent/child references

Query Design

Is there any aspects that I'm missing or should focus more on?


texts: Pass variable as an input argument to a function inside another function in PostgreSQL

texts:
I am new to PostgresQL and having some trouble in feeding a function with a variable from an outer function. (maybe it is not possible...)
Inside a function I am trying to assign a value from calling another function like this:
left_bipolar_n:=public.convert_to_decimal(left_bipolar);
When I call the function, If I use something like '122' as an argument instead of "left_bipolar" it works just fine but just as it is I get:
Error: invalid input syntax for type numeric: "  "
I should say that Function convert_to_decimal expects text
Complete code:
-- FUNCTION: public.AmateurExercise(numeric)
--drop FUNCTION public."AmateurExercise"(numeric);

CREATE OR REPLACE FUNCTION public."AmateurExercise"(
    n numeric)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
bipolar text;
tamanho integer;
left_bipolar text;
right_bipolar text;
left_bipolar_n numeric;
right_bipolar_n numeric;

begin
bipolar:=public.convert_to_bipolar8(n);
tamanho=length(bipolar);
if tamanho/2 =floor (tamanho/2) then
begin
left_bipolar=left(bipolar,tamanho/2);
right_bipolar=right(bipolar, tamanho/2);
end;
else
left_bipolar=left(bipolar,(tamanho-1)/2);
right_bipolar=right(bipolar, ((tamanho-1)/2)+1);
end if;

left_bipolar_n:=public.convert_to_decimal(left_bipolar);
return left_bipolar_n;

end;
$BODY$;

ALTER FUNCTION public."AmateurExercise"(numeric)
    OWNER TO postgres;


texts: Do I need to rebuild heap table after rebuilding a non-clustered index?

texts:
Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe someone had tested it before.

Do I need to rebuild a heap table after rebuilding a non-clustered index?


texts: Insert into table with column same values as auto increment column ( computed column )

texts:
Say I have a table with "column1" (Primary Key, Auto Increment) and "column2".

How can insert into the table with column2 will have the same values as column1 which is auto increment column?


texts: Conformed Fields or Snowflake? - Shop and Customer Location

texts:
I have shop dimension and customer dimension which both has location information.

Should I create location dimension to correlate shop location and customer location? I believe this one is called snowflake schema. I heard that it is hard to maintain.

|----------|       |---------|       |----------|
| Dim_Cust | ----- | Dim_Loc | ----- | Dim_Shop |
|----------|       |---------|       |----------|

Or, should I maintain definition that shop and customer dimension is conformed by each location field? For this one, I denormalized location information to each dimension.

|----------|       |----------|
| Dim_Cust | ----- | Dim_Shop |
|----------|       |----------|


texts:
We have SSRS 2005 that publishes reports. The reports are often as big as 1000-3000 pages. So the problem is that when the users try to print the report, it prints some of the pages  and stops. And this happens randomly. For instance, 10 people can print at the approximately the same time and 4 could face the problem. When I look at the even log it shows an ASP.Net warning that says:

  'The report execution <'session ID'> has expired or cannot be found. (rsExecutionNotFound)'.

In a development setting, I tried upgrading it to SSRS 2012 and moved all the reports.  But I got the same error message:

  An error occurred during printing. (0x80004005)`.

And the error log is:

  'The report execution <'session ID'> has expired or cannot be found. (rsExecutionNotFound)'.

What I have already done is make changes to the config file and set EnableAuthPersistance to FALSE and CleanupCycleMinutes to 30 on the SSRS 2012. I have already ruled out network and hardware related issues by trying it on different servers.

Do you know why this is happening and why it is happening so randomly?


texts: Getting sql statement to refer to more than 2 tables?


texts: Why is Include Actual Execution Plan not included in @@Rowcount when it produces a row count?

texts:
While updating a column in a table the last day I was shown a new technique by our Senior DBA to reduce reads and contention.

The pseudo-code is

WHILE
  [always true condition]
BEGIN
  UPDATE [...]
IF @@ROWCOUNT = 0 BREAK;
END

However while running this, I had accidently left Include Actual Execution Plan on so in the Messages tab I was seeing repeating occurrences of this:

(1000 row(s) affected)

(1 row(s) affected)

However the code worked and the WHILE loop worked!

My question is, if including the Actual Execution Plan returns 1 row affected, shouldn't this create an infinite loop?

Why is it not getting counted?

Examples tables below:

USE [tempdb];
GO

CREATE TABLE [dbo].[UpdateMePlease]
([ID] INT IDENTITY(1, 1), [UselessNote] VARCHAR(10) DEFAULT 'Delete me!', [UpdateValue] VARCHAR(10) NULL)
GO

INSERT INTO [dbo].[UpdateMePlease] ([UselessNote]) DEFAULT VALUES;
GO 10000

SELECT * FROM [dbo].[UpdateMePlease];

/* turn Actual Execution Plan on here */

WHILE 1 = 1 BEGIN

UPDATE TOP (1000) [dbo].[UpdateMePlease]
SET [UpdateValue] = [UselessNote]
WHERE [UpdateValue] IS NULL;

IF @@ROWCOUNT = 0 BREAK

END

-- Clean up
DROP TABLE [dbo].[UpdateMePlease];


texts: Take advantage of monotonic columns in PostreSQL

texts:
I have a PostgreSQL table with, among the other, two columns named col1 and col2, both of integer type (there are around 10M rows in the table). I want to perform SQL queries like:

SELECT * FROM table WHERE col1 >= val1 AND col2 <= val2;

(for certain val1 and val2 that I know a query time).

If I put btree indices on col1 and col2 PostgreSQL tries to execute the query performing an index scan on one of the two columns and then filtering on the other. This means that in most cases it has to sweep through around half of the table, even when the number of matching rows is very little. Adding a multicolumn index is useless, because PostgreSQL can effectively use it only when at least one of the two columns is tested for equality.

One important assumption that I can make on the values, though, is that the two columns are monotonic one respect to the other. This means that if in a row col1 is greater then or equal two col1 in another row, then the same relation is valid between the two corresponding col2 entries.

This means that in line of principle the query execution could be sped up by performing an index scan on one of the two columns, filtering on the other and stopping the execution as soon as a non matching value is found on the second column. In this case the query would read just exactly the rows to be returned.

Is there any way to setup indices or whatever other invariant in PostgreSQL so that the query planner is able to detect this?

(of course the problem can be easily solved performing two queries, the first one to translate the inequality on col2 to an inequality on col1; I am asking if there is a way to avoid this workaround and let PostgreSQL manage the mess by itself)


texts:
I need to do this table transformation, but its little hard for me.

I have original table wich looks like this

and i need to transform the table into this

How can i do that ?

Here i prepared example database http://sqlfiddle.com/#!3/0f324

Thank you for helping.


texts:
We are using Load data infile query to load txt files in the database (mysql) .

we have a continues flow of text files to process using load query and is dumped in the table

but sometimes the load query just stops processing the files ..

we are not able to get what is causing this issue ..

is there anyway we could generate the sql exception or server exception to know what could have caused the problem to distinguish if its a server problem or the file problem ..


texts: Will creating a view on a MySQL replication slave break the replication?

texts:
We need to run some very ugly queries on a MySQL replication cluster.

Does anyone know if creating a view directly on the slave will break the replication of the other, otherwise untouched data synced with the master?


texts: What is the difference between Microsoft SQL Server 2008 R2 and MySql Server 5.6 for handling Select ISNULL(MIN(StartDate, '1/1/1900 10:00'))?

texts:
I would like to know the difference between Microsoft SQL Server 2008 R2 and MySql Server 5.6 for parsing the following SQL statements.

Microsoft SQL Server 2008 R2

SELECT ISNULL(MIN(StartDate),'1/1/1900')
FROM FileData
WHERE ArchiveDeleteDate IS NULL

  MySql Server 5.6

SELECT IFNULL(MIN(StartDate),'1/1/1900')
FROM FileData
WHERE ArchiveDeleteDate IS NULL

Here is description of the table FileData:

mysql> describe FileData;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| FileID            | bigint(20)          | NO   | MUL | NULL    |       |
| FileName          | varchar(250)        | YES  |     | NULL    |       |
| StartDate         | datetime            | NO   | PRI | NULL    |       |
| FrameCount        | smallint(6)         | YES  |     | NULL    |       |
| CameraID          | int(11)             | NO   | PRI | NULL    |       |
| ArchiveDeleteDate | datetime            | YES  | MUL | NULL    |       |
| VideoLength       | decimal(10,2)       | YES  |     | 0.00    |       |
| DriveLetter       | varchar(1)          | YES  | MUL | NULL    |       |
| FileType          | tinyint(3) unsigned | NO   | PRI | 0       |       |
| FrameRate         | smallint(6)         | NO   |     | 0       |       |
+-------------------+---------------------+------+-----+---------+-------+

Here is the MySql Connector .NET C# reader code:

public DateTime SelectEarliestRecordedDate()
        {
            #region // SQL - Select //
            const string CommandText = "SELECT ifnull(MIN(StartDate),'1/1/1900 10:00') AS X From FileData WHERE ArchiveDeleteDate IS NULL";  //fc 3/4/2016
            #endregion

            using (MySqlCommand command = new MySqlCommand(CommandText, m_connection))
            {
                // Execute the command object
                bool isOpen = this.EnsureOpen();
                try
                {
                    using (var reader = command.ExecuteReader())
                    {
                        try
                        {
                             DateTime t = DateTime.Now;
                             if (reader.Read())
                             {
                                t = reader.GetDateTime(0);
                             }
                             return t;
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine("This needs investigating " + e.ToString());
                            return DateTime.Now;
                        }
                        finally { reader.Close(); }
                    }// reader.Dispose();
                }
                finally { if (!isOpen) this.Close(); }
            }// command.Dispose();
        }

which throws this exception only in the case of MySql Client and Server 5.6 on Ubuntu Linux 15.10 and not for Microsoft SQL Server 2008 R2?

Why does this difference occur?

System.IndexOutOfRangeException: Array index is out of range.   at MySql.Data.Types.MySqlDateTime.ParseMySql (System.String s)


texts: Querying system health data becomes slow as event volume increases

texts:
We get the data from system health XE from SQL server and load into table for later querying.

Part of that statements which gets pretty slow is

INSERT INTO TABLE1
      (
         [object_name]
         ,[col1)
       )

SELECT [Object_name], CAST (event_data as XML) AS eventcol1

FROM sys.fn_xe_file_target_read_file (N'D:\SQL\system_health*.XEL', NULL,NULL,NULL)

Above is real slow when fetching events over 200k

Table structure is

CREATE TABLE [dbo].[Table_1](
    [object_name] [nvarchar](60) NOT NULL,
    [col1] [xml] NULL
) ON [DEMO] TEXTIMAGE_ON [DEMO]
GO

The only index on the table is  clustered indexon object_name [non unique]

I see from msdn that columnevent_data     is nvarchar(max)

Update
I am able to repro this situation for 42K records in system health which runs for 7-8 seconds and same goes over 50 minutes when there are over 0.5 million records to fetch from system health

link - PLan-- This on all versions starting SQL2014 and above where conversion warning is displayed.

Warning does not show up on SQL 2012 therefore i used trace flag 9481 but process is still slow

Plan without warning

Please suggest


texts: Chance of skipping extended events when using an offset for the file target

texts:
I am exploring extended events and came across a question I can't solve using the Microsoft documentation:

When reading events from the file target we can specify and initial offset so we can read the logs in in a differential manner. For example:

sys.fn_xe_file_target_read_file('log*.xel', 'log*.xem', 'specific.xel', 984576)

Now all events up to and including 984576 will be ignored. Events after the offset will be returned.

From the documentation:

  initial_offset
  Used to specify last offset read previously and skips all events up to the offset (inclusive). Event enumeration starts after the offset specified. initial_offset is bigint. If null is specified as the argument the entire file will be read.

My question is how can we be sure that we don't skip events given the fact that file_offset is not unique and shared:

If we use an offset there is a change that new events with a share offset are not returned right?

Does the function fn_xe_file_target_read_file bump the offset so that this possibility cannot occur?


texts:
Using Firebird, I have two tables, Orders and DespatchedParts.

Orders table:

ID  CustomerID  Total_number_parts
--  ----------  ------------------
1   1           5
2   1           4
3   2           10
4   1           5
5   3           20

DespatchedParts table:

ID  OrdersID  PartQty
--  --------  -------
1   1         2
2   1         3
3   2         10
4   3         10
5   3         10

So in the Orders table the sum of Total_number_parts for Cust 1's orders is 5+4+5=14.

Then looking in DespatchedParts, we want the sum of PartQty for Customer 1's, so 2+3=5.

So Customer 1 has ordered 14 parts and 5 have been dispatched. I want to get all the Customer ID's who have received all their parts.

Below is what I have got so far. I don't know where to put the sum(total_number_parts) = sum(qtyparts) part.

Select a.customerID, sum(a.Total_number_parts)
From Orders a
Join (Select sum(PartQty) from OrderParts Group by customerID) B
   on B.customerID = a.customerID
Group By a.CustomerID


texts: Separating a date column in Access into Month, Day and Year while keeping the new columns ordered by a seperate column(primary key)

texts: I have a column titled DateOpen which lists the date files were opened in d/m/yyyy format. I want to split the entries in that column into new columns titled MonthOpen, DayOpen and YearOpen while keeping the fields ordered by the primary key column titled FileNumber.  Thanks in advance.


texts:
I am currently importing a pretty big MySQL database dump. This takes time and during this time I need some extra disk space. Will I break something in MySQL if I temporarily gzip a couple of it's binlogs? I'm assuming I'm not going to compress binlogs that are purged, nor written, nor read by the MySQL instance.

Example:

$ ls
mysql-bin.000340
mysql-bin.000341
mysql-bin.000342
mysql-bin.000343.gz
mysql-bin.000344.gz
mysql-bin.000345.gz
mysql-bin.000346
mysql-bin.000347
mysql-bin.000348
mysql-bin.index


texts: How to create a table with multiple data type like char short int string in mysql?

texts:
Now I have two way to do this as below, but I do not know which is better and is there some better way?

CREATE TABLE `params_value1` (
  `params_id` int(11) NOT NULL AUTO_INCREMENT,
  `params_value_int8` tinyint(4) DEFAULT NULL,
  `params_value_int16` smallint(1) DEFAULT NULL,
  `params_value_int32` int(11) DEFAULT NULL,
  `params_value_string` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`params_id`),
  UNIQUE KEY `params_id_UNIQUE` (`params_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

OR

CREATE TABLE `params_value2` (
  `params_id` int(11) NOT NULL AUTO_INCREMENT,
  `params_value` varchar(32) NOT NULL,
  PRIMARY KEY (`params_id`),
  UNIQUE KEY `params_id_UNIQUE` (`params_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The follow picture is main tab used:


texts: Do I need to add a new single column index to a table if a multi-column index on that field already exists?

texts:
I've a table with a multi-column UNIQUE index on _job_id__ and __keyword_id__.

Would I also need to add another index to __job_id__ if I have a frequent query which performs a GROUP BY on that column?

(at 100 million rows it could take a while.This is why I'm asking instead of just doing)