texts: Understanding rows_examined_per_scan and rows_produced_per_join

texts:
I'm not mysql guy, but I've got opportunity to work with that recently - I've got to optimize some query on Mysql5.7 (I have to cover that also on 5.6, but started with 5.7 as it has apparently more info in explain) running on AWS Aurora. It takes a lot of time, some joins are involved, etc. I started with cutting branches and choose to start 'debugging' with two tables only. These are not too big (~2M and ~1.5M rows), but well, generally I consider them as not too greatly designed (primary keys on varchar(255) columns, etc.).
The thing is that I wanted to take a look into so I used explain format=json and I'm trying to get any insight from that.
So, let's say that for
select cc.id, cc.col1, cc.col2, ct.col1
from my_table cc
inner join my_table ct on ct.cc_id = cc.id

I am getting something like
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5630369.56"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "cc",
          "access_type": "index",
          "possible_keys": [
            "PRIMARY"
          ],
          "key_length": "258",
          "rows_examined_per_scan": 1248725,
          "rows_produced_per_join": 1248725,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "3732979.00",
            "eval_cost": "249745.00",
            "prefix_cost": "3982724.00",
            "data_read_per_join": "1G"
          }
        }
      },
      {
        "table": {
          "table_name": "ct",
          "access_type": "ref",
          "possible_keys": [
            "cc_id_idx"
          ],
          "key": "cc_id_idx",
          "key_length": "257",
          "ref": [
            "cc.id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1373037,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1373037.97",
            "eval_cost": "274607.59",
            "prefix_cost": "5630369.56",
            "data_read_per_join": "3G"
          },
          "used_columns": [
            "id",
            "col_1",
            "col_2",
            ...
          ]
        }
      }
    ]
  }
}

and I have problems with understanding what happens here exactly. I think that mysql makes nested join where it starts with table cc and then for each row it hits to ct (access_type is ref and "rows_examined_per_scan": 1). It makes it > 1M times ("rows_produced_per_join": 1373037). Is that correct? I was looking for any documentation for that, but I didn't found any specifing info about how read these values in context of joining - maybe just my google-fu is not strong enough. Could any give me any clue about that?
(As I'm looking for any options to speed it up, I wanted to force mysql to make a hash join, which is available on Aurora (I was trying on Aurora 2.09) in form of /*+ HASH_JOIN(cc)  */, but it didn't affect the query plan in any case - but it's rather issue for another question.)


texts: Cassandra goes down after some some days

texts:
I am running cassandra in 3 node cluster with replication factor 3. It was working fine for 2 days and all of a sudden it went down. I am beginner to cassandra database. I went through documentation to understand why it is happening . I could not get it .Below are the logs.

INFO  [main] 2020-02-17 11:52:34,270 CassandraDaemon.java:473 - Hostname: QCS7
INFO  [main] 2020-02-17 11:52:34,271 CassandraDaemon.java:480 - JVM vendor/version: Java HotSpot(TM) 64-Bit Server VM/1.8.0_152
INFO  [main] 2020-02-17 11:52:34,274 CassandraDaemon.java:481 - Heap size: 998.438MiB/998.438MiB
INFO  [main] 2020-02-17 11:52:34,275 CassandraDaemon.java:486 - Code Cache Non-heap memory: init = 2555904(2496K) used = 4815808(4702K) committed = 4849664(4736K) max = 251658240(245760K)
INFO  [main] 2020-02-17 11:52:34,275 CassandraDaemon.java:486 - Metaspace Non-heap memory: init = 0(0K) used = 18448024(18015K) committed = 19005440(18560K) max = -1(-1K)
INFO  [main] 2020-02-17 11:52:34,275 CassandraDaemon.java:486 - Compressed Class Space Non-heap memory: init = 0(0K) used = 2277864(2224K) committed = 2490368(2432K) max = 1073741824(1048576K)
INFO  [main] 2020-02-17 11:52:34,275 CassandraDaemon.java:486 - Par Eden Space Heap memory: init = 214827008(209792K) used = 98821656(96505K) committed = 214827008(209792K) max = 214827008(209792K)
INFO  [main] 2020-02-17 11:52:34,276 CassandraDaemon.java:486 - Par Survivor Space Heap memory: init = 26804224(26176K) used = 0(0K) committed = 26804224(26176K) max = 26804224(26176K)
INFO  [main] 2020-02-17 11:52:34,276 CassandraDaemon.java:486 - CMS Old Gen Heap memory: init = 805306368(786432K) used = 0(0K) committed = 805306368(786432K) max = 805306368(786432K)

INFO  [pool-3-thread-1] 2020-02-17 11:53:24,320 AutoSavingCache.java:262 - Harmless error reading saved cache /var/lib/cassandra/saved_caches/KeyCache-e.db
java.io.IOException: Corrupted key cache. Key length of 83886081 is longer than maximum of 65535
    at org.apache.cassandra.service.CacheService$KeyCacheSerializer.deserialize(CacheService.java:481) ~[apache-cassandra-3.11.2.jar:3.11.2]
    at org.apache.cassandra.cache.AutoSavingCache.loadSaved(AutoSavingCache.java:219) ~[apache-cassandra-3.11.2.jar:3.11.2]
    at org.apache.cassandra.cache.AutoSavingCache$3.call(AutoSavingCache.java:164) [apache-cassandra-3.11.2.jar:3.11.2]
    at org.apache.cassandra.cache.AutoSavingCache$3.call(AutoSavingCache.java:160) [apache-cassandra-3.11.2.jar:3.11.2]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_152]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_152]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_152]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_152]
INFO  [pool-3-thread-1] 2020-02-17 11:53:24,320 AutoSavingCache.java:173 - Completed loading (21 ms; 560 keys) KeyCache cache

INFO  [main] 2020-02-17 11:55:35,746 MessagingService.java:753 - Starting Messaging Service on /172.30.55.233:7000 (br-provider)
INFO  [main] 2020-02-17 11:55:35,827 OutboundTcpConnection.java:108 - OutboundTcpConnection using coalescing strategy DISABLED
WARN  [main] 2020-02-17 11:56:06,859 Gossiper.java:1425 - Unable to gossip with any seeds but continuing since node is in its own seed list
INFO  [main] 2020-02-17 11:56:06,863 StorageService.java:707 - Loading persisted ring state
INFO  [main] 2020-02-17 11:56:06,869 StorageService.java:825 - Starting up server gossip
INFO  [main] 2020-02-17 11:56:07,546 TokenMetadata.java:479 - Updating topology for /172.30.55.233
INFO  [main] 2020-02-17 11:56:07,546 TokenMetadata.java:479 - Updating topology for /172.30.55.233


texts: MySQL column that auto generates data, longer than UUID

texts:
My friend has the problem as shown below. He asked at StackOverFlow and there is no reply yet till now so I wish to help him to try the luck here. Thanks for everyone that helps.

"I would like to ask if its possible to set up a database table that has a column that auto generates random alphanumerical data that is longer that what UUID can do. I've been using UUID for a while now but I would like to have a longer string of random data in my columns, something similar to that of a token authenticator (around 300+ characters). So when I insert values in columns, this particular column will auto generate data by itself. Thanks in advance."

Quoted from Reuben Tan
Source: https://stackoverflow.com/questions/43461771/mysql-column-that-auto-generates-data-longer-than-uuid


texts:
I'm new to AlwaysOn Availability groups and have set it up in my DEV environment.  I currently have it set to do the backups on my primary because we are only using the secondary for read-only reporting purposes. The backups are running but how do I control how often? Currently it does a FULL every night, a DIFF every night at midnight and log backups every hour.  I don't see in the AVG setup where to specify how often...I currently do a FULL at 10pm, DIFF every 4 hours and LOG every 15 minutes.  I also currently use Ola's backup scripts and I'm confused about how to continue to use his scripts when I setup AVG in my production.  I've read that there is a parameter AVAILABILITY_GROUP_DATABASES for Ola's scripts.  Do I use this to be able to control all the backups with Ola's scripts? And then the AVG will not back up the databases...instead they will be controlled by Ola's scripts?

I've tried testing this and no matter what I do, the AVG always does the backups and Ola's scripts are ignored.


texts: calculate average of a class where students offer different number of subjects

texts:
I am trying to calculate the class average of a class where students can hold different amounts of subjects (e.g.: two students in a class of which one takes 3 subjects and the other 2). How do I get the class average that applies to all students? I already have their total term score in a table. I know the formula for calculating the average is simply the total averages of individual students divided by the number of students, but I can't seem to get it working. Can anyone please help me write a query that will calculate and print out this class average?

Here is what I have tried so far:

SELECT student_id, SUM(CA_total)/count(term_total) AS average
FROM score_entry
GROUP BY student_id

BUT I want the final result to be the sum of 25 + 26.5 which will be 51.5 then divided by the 2 students in the class should give 25.75. this is the class average. I want the query or code that will echo out the final answer 25.75. thanks
THIS IS SAMPLE OF THE EXACT DATABASE


texts:
I'm looking at PLE (Page Life Expectancy) across NUMA nodes on our SQL Servers, and came across a rather weird distribution. NUMA node 000 has a very low PLE compared to 001. I'm unsure why this is. I've checked on multiple other SQL Servers in our environment, and the other production servers do not have this behavior.

System is running SQL Server 2012 Enterprise Edition on Dell m620 with 256GB Ram. It's a 2 socket, 6 core (HT enabled) machine. MAXDOP is set at 6. AFAIK memory modules are installed evenly across the memory banks of the CPUs

Something tells me NUMA node 000 has other SQL tasks to perform, that other nodes, but i've forgotten where I heard/saw it.

@@Version shows: Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64)


texts: Can we do the domain migration for 4 node SQL cluster?

texts: Can we do the domain migration for 4 node SQL cluster ?


texts: Is a @CATALOG argument required in linked server to connect to READ-ONLY secondary?

texts:
I have 1 primary and two secondaries.  Node 3 is READ-ONLY.  I created a linked server as follows (as described in Microsoft Docs):

EXEC sp_addlinkedserver
@server=N'Node3',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr = N'ApplicationIntent=ReadOnly',
@datasrc= 'AGListenerName';

When using this linked server it will always connect to Node 1 (primary).  I verify my connection as follows:

SELECT *
FROM OPENQUERY([Node3],
SELECT DATABASEPROPERTYEX (''AnyDatabase'', ''Updateability'' ) ')

This returns:  READ_WRITE

If I specify a database (catalog) in the linked server as follows:

EXEC sp_addlinkedserver
@server=N'Node3',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr = N'ApplicationIntent=ReadOnly',
@datasrc= 'AGListenerName',
@catalog= 'database';

The connection is made to the correct READ-ONLY secondary (Node 3).  I verify in the same manner as above.

It returns:  READ_ONLY

This is the same behavior in find in SSMS when creating a connection to a READ-ONLY instance.

Am I configuring the linked server incorrectly or is the @CATALOG argument required?  Thanks.


texts: Understanding the use of bitwise operators in MySQL?

texts:
Can someone explain the purpose of using bitwise operators(like BIT_OR) in MySQL queries. For example, if have a table such as following:

What is the purpose of aggregate operation like:

SELECT name, value FROM table GROUP BY name HAVING BIT_OR(value) = 0;


texts: Restore with Standby - Tail-log backup is greyed out

texts: I got a .bak file from one of our vendors.  The instructions from them says that I should RESTORE WITH STANDBY and select "Tail-log backup" under options.  Problem is that "Tail-log backup" is greyed out in SSMS.  I am logged in as sysadmin and my SQL Server version is MSSQL14


texts:
I have two percona servers, one running with TokuDB tables and the other with InnoDB tables.

On each server, i have create a single database, with a single table in it containing 10 millions records using sysbench. But using the query below, i noticed that the database on the TOkuDB server was way too large compared to the database on the server with InnoDb tables. The TOkuDB table has a clustering index,  and the InnoDB table has a covered index (covering all fields except for the primary key).

So here is the table structure for the InnoDB table:

 CREATE TABLE `sbtest1` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `k` int(10) unsigned NOT NULL DEFAULT '0',
   `c` char(120) NOT NULL DEFAULT '',
   `pad` char(60) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `k_1` (`k`),
   KEY `covered` (`pad`,`k`,`c`)
   ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4
   MAX_ROWS=1000000 ROW_FORMAT=COMPRESSED

And the data size is shown below with the main interest being on database subtest, which contains one InnoDD table with 10 million records:

mysql> SELECT table_schema "Data Base Name",
    ->     sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
    ->     (index_length) / 1024 / 1024 "Index data in MB"
    -> FROM information_schema.TABLES GROUP BY table_schema ;
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Index data in MB |
+--------------------+----------------------+------------------+
| information_schema |           0.00976563 |       0.00000000 |
| mysql              |           0.77099419 |       0.00390625 |
| performance_schema |           0.00000000 |       0.00000000 |
| sbtest             |        1203.36718750 |      67.36718750 |
| test               |           0.03906250 |       0.00000000 |
+--------------------+----------------------+------------------+
5 rows in set (0.02 sec)

The second table is a TokuDB table also with 10 million records and running on server 2.

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  CLUSTERING KEY `pad` (`pad`)
) ENGINE=TokuDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000 ROW_FORMAT=TOKUDB_QUICKLZ

Data size

mysql> SELECT table_schema "Data Base Name",
    ->     sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
    ->     (index_length) / 1024 / 1024 "Index data in MB"
    -> FROM information_schema.TABLES GROUP BY table_schema ;
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Index data in MB |
+--------------------+----------------------+------------------+
| information_schema |           0.00976563 |       0.00000000 |
| mysql              |           0.77224922 |       0.00390625 |
| performance_schema |           0.00000000 |       0.00000000 |
| sbtest             |       12293.65114307 |    5340.57617188 |
| test               |           0.00000000 |       0.00000000 |
+--------------------+----------------------+------------------+
5 rows in set (0.02 sec)

As can seen, the TokuDB is using Quicklz compression, which should give slightly better compression that the InnoDB compressed mechanism. So why does the TOkuDB table appear to have way much more data than the InnoDB table?

Both servers had the same amount of memory and disc space and hardware before the tables were created.

Here is the data size from the datadir:

TokuDB
-rw-rw---- 1 mysql mysql  12M Feb 17 14:48 ibdata1
-rw-rw---- 1 mysql mysql 2.9G Feb 17 12:30 _sbtest_sbtest1_key_pad_25fbaee_3_1d_B_0.tokudb
-rw-rw---- 1 mysql mysql 3.1G Feb 17 12:27 _sbtest_sbtest1_main_11_1_1d_B_0.tokudb

InnoDB
-rw-rw---- 1 mysql mysql 332M Feb 17 14:48 ibdata1
-rw-rw---- 1 mysql mysql 2.3G Feb 17 12:38 sbtest1.ibd


texts: what is default balancing window in MongoDB

texts: I have read about balancing window in MongoDB document. I see mongo tell about how to modify balancing window but I don't see the default of balancing window. I see only one document about chunk size in collection settings in config database. Do you know the default of balancing window ?


texts: New Table vs Java Beans to allow user to dynamically change data representation?

texts:
I am building a database to store the assessment records of students, along with other relevant information and functionality like reporting on average scores etc.

Currently my company uses Excel for all their records but this is rapidly showing its limitations, the major ones being no concurrent access and no easy data validation (I know Excel can, but it isn't as flexible as a programmatic solution).

To this end, I am building a database using Postgres with a Java/JavaFX frontend. The problem comes because my boss loves the flexibility of Excel, despite the strain this imposes on day-to-day business. However, I have been told that if I can design a solution that allows enough flexibility and enforces business rules for the majority of users but allows managers to change to modify them, they will take on my solution.

My current sticking point with this is that every so often the assessments will change for a given course as teachers reflect on the pedagogy. This means that I have to offer them some way of changing the assessments in a table, because they don't want to be locked into me doing it in SQL. I could just allow them to add new columns and when they set up a new term and course, associate those particular columns with that term and course, but the table may become very large and when they are selecting which are the pertinent assessments, it may be very tedious.

For example, say I have course 1 running in term 115 with 12 assessments. I then have course 1 running in term 215, but they want to change just 5 of those assessments to something else with different max mark to others etc. To do this, I could allow them to add a column to the table and just associate the 7 original assessments and 5 new ones with course 1 in term 215, while maintaining the ability to look back at marks in previous terms for the same course for the extant assessments at the time.

However, this means when they simply want to carry over the same assessments from the time the course ran in term 215 to term 315, they will have an ever expanding list of columns to pick that they want to associate with a given term (17 presently in this example). There would also be a lot of null values for each row where previous assessments are no longer used. This question reflects my problem but the solution doesn't because there is still an ever expanding list of assessments to carry over into a new table for a new term. There also have to relationships to other tables that would carry over and this approach seems to be considered bad pratice.

This may just be a necessary evil, but the solution I thought of that might be better, is allowing the managers to 'dynamically' create Java beans. For example, say I have a bean for the assessment fields in 115. When the user wants to change 5 assessments, they just load the old one and modify the assessments that are no longer relevant which creates a new bean blueprint. This would be done in such a way that there is no programming for the user (done through GUI). I would then store the objects in a column with another column to tell the frontend which bean type to use to represent the data in the frontend. Thus, there is one column but with potentially a few different bean types associated with each course and term. When they want to just carry over the same assessments to a new term, they use the previous term's bean. However, storing objects like this seems to be considered bad practice, as well. In addition, it is difficult to generate Java beans dynamically and I might have to use Python, which adds additional complexity to this solution. In short, am I better off just allowing the user to create tables/columns dynamically or should I go this route? Are there any other solutions? Sorry for the long explanation.


texts: Is it possible to use a default partition for all reads?

texts:
I have a legacy application which queries one of its database (MySQL on AWS Aurora) tables extensively. Lots of queries and lots of query types. 90% of the rows in this table are outdated and are marked is_deleted. I want to try and make the performance better so I thought of partitioning the table by is_deleted and making all queries go to the partition where is_deleted=false. The problem is that there are many hundreds, if not thousands, of queries I'd need to rewrite in order to insert is_deleted to the where clause of every query.
Is there a way to define the is_deleted = false partition as the default for all reads? If not is there some other way to do this?


texts: SQL Server - How to determine ideal memory for instance?

texts:
We have some virtual machines that have X memory allocated to them.  This amount of memory is somewhat random.  It was allocated to the machine because that was the amount of memory the physical machine had, because of a vendor recommendation, or because we threw out a best guess as to how much memory we thought the instance would require.

I understand the more memory the better, but I would also like to avoid over allocating memory to the VM when it isn't necessary.  The memory could be better utilized by another machine.

What would be the best way to determine an ideal amount of memory per instance that is actually active and being used?  Are there other counters we should be looking at in addition to page life expectancy?  We have instance that have PLE's of 10k+ and others that have 100k+.

Any insight is much appreciated.
Thanks,
Sam


texts:
I know where clause search and display the records as per criteria comes true, and limit is use to fetch the number of records from a table.

But please help me when I am trying to use them both together.

I have a table with following records

Table: records

+---------+--------+------------+
|  id     |  type  |   Credits  |
+---------+--------+------------+
|    1    | my     |   100      |
|    2    | my     |   200      |
|    3    | other  |   50       |
|    4    | my     |   500      |
+---------+--------+------------+

there are many different such records in the table after on an other.

I want to fetch any 3 records by type columns then I designed a following query!

select * from `records`
where `type` = 'my'
limit 3

I got 2 results with type 'my' however I want to skip the other type data and then top 3 records with type 'my'

  How can I do this with mysql database.

thanks in anticipation!


texts:
For Below Query

SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG
    FROM entry_log
    WHERE TRIM(LEADING 0 FROM card_no)='2948'

I've index on

date
card_no
date,last_entry
date,last_entry,card_no

My Explain Shows

id  select_type     table       type    possible_keys   key             key_len     ref     rows    Extra
1   SIMPLE          entry_log   index   NULL            date_last_card  158         NULL    103766  Using where; Using index

My Explain Extended shows

id  select_type     table           type    possible_keys   key             key_len     ref     rows    filtered    Extra
1     SIMPLE        entry_log       index   NULL            date_last_card  158         NULL    103766  100.00      Using where; Using index

I want to know which index can i remove/use, Am i in right path, How should i improve execution time for above query?

CREATE TABLE `entry_log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `card_no` varchar(50) NOT NULL,
 `date` date NOT NULL,
 `first_entry` time NOT NULL,
 `last_entry` time NOT NULL,
 `all_entry` text NOT NULL,
 `entry_time` datetime NOT NULL,
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9308 DEFAULT CHARSET=latin1


texts:
I recently started working on a project using stored procedures from data vs. ORM's

I haven't seen this project's procedure layout before and was wondering if it's ok / what would be better.

Code would similar to this

procedure getCarsBy

@color
@inProduction
@queryType

case queryType = 1
    Select from Cars where color = @color
case queryType = 2
    Select from Cars where inProduction = @inProduction

Most the procedures have big case blocks for each where clause configuration.

I'd like to break things out of the case statements because it doesn't seem proper to duplicate the select piece all over the place but I'm not sure if dynamic sql or many procedures would be better.

For example getCarsByColor, getCarsByInProduction ect.

Thoughts?


texts: SQL Server table appears locked for no reason

texts:
My database was running just fine this morning, up until it wasn't.

This query:

SELECT TOP (1) *
FROM tblTask;

comes back in no time at all, while this query:

SELECT *
FROM tblTask this_ with (updlock, rowlock);

which is generated by NHibernate in my app, is timing out in my software.  In SSMS, I killed the query after letting it run for 9 minutes.  The table has a total of 6 records.

The problem started coming up after the virtual machine that hosts the software running this query was rebooted this morning.  Given the locking hint, I would have guessed that there was somehow a stray lock stuck on that table.  I restarted the database, and also rebooted the server itself, but I still get the same behavior.

This table is only hit by a Windows service - nothing else should be locking the table and preventing access to it.  The web site (which uses the rest of the database but not this table) appears to be running perfectly fine.

I'm not sure where to go from here.  Any help?


texts: Relational modeled design to store versioned files

texts:
The scenario

I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the directory path which the images are stored, if a original image is stored in dir : 1, the altered version of this exact image would be dir : 1-1.(This I am thinking has room for improvement but I am unsure of anyway of doing it(BLOB and storing within the db is out of the question).

Current model

 Files
+---------+-----------+-----------+--------+
| file_id | file_name | file_hash | folder |
+---------+-----------+-----------+--------+
|    1    |  bmw.png  | d29djkj38 |    1   |
|    2    |  bmw.jpeg | e9sljd551 |    1   |
+---------+-----------+-----------+--------+

Altered_Files
+---------+------------------+
| file_id | original_file_id |
+---------+------------------+
|    2    |         1        |
+---------+------------------+

file_id and original_file_id here are both foreign keys to Table : Files Column : file_id.

file_id is also the Primary Key

I have never used two columns as foreign keys where both reference the same one column in another table, this makes me wonder if its a incorrect design(or perhaps its not even allowed architecturally).

As for the altered images directory folder, we can generate this by joining both tables and then using contact('-') with the results.

Overall concern

I am still new to Database modelling and wondering if this design is ok(won't need refactoring at a later date as the pros say(Edgar Codd, C Date)), perhaps it can be improved even more?

Also what do you suggest for the image directory issue, integrity is not enforced in the database but with this idea it seems possible to maintain with application code and permitting access to the main directory to only the db.


texts:
I have a project whose activity I need to keep private, and I'm not the server administrator.
If my app updates a value for the user with id 1, it would probably say something like UPDATE 'users' SET 'Name' = 'George' WHERE 'Id' = 1; and that will show in the Apache log.
What can I do to avoid this?


texts: Supertype table with disjoint subtypes in MySQL

texts:
I want to have base parent table with few disjoint subtypes. To illustrate what I mean:

CREATE TABLE issues (
  issue_id SERIAL PRIMARY_KEY,
  issue_type CHAR(1) CHECK (issue_type IN ('B', 'F')),
  UNIQUE KEY (issue_id, issue_type)
);

CREATE TABLE bugs (
  issue_id INT PRIMARY_KEY,
  issue_type CHAR(1) CHECK (issue_type = 'B'),
  ...,
  FOREIGN KEY (issue_id, issue_type) REFERENCES issues(issue_id, issue_type)
);

CREATE TABLE features (
  issue_id INT PRIMARY_KEY,
  issue_type CHAR(1) CHECK (issue_type = 'F'),
  ...,
  FOREIGN KEY (issue_id, issue_type) REFERENCES issues(issue_id, issue_type)
);

To achieve this in MySQL, I tried to use ENUMs instead of unsupported CHECKs:

CREATE TABLE issues (
  issue_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  issue_type ENUM('B', 'F') NOT NULL,
  UNIQUE (issue_id, issue_type)
);

CREATE TABLE bugs (
  issue_id INT NOT NULL PRIMARY KEY,
  issue_type ENUM('B') NOT NULL DEFAULT 'B',
  text TEXT,
  FOREIGN KEY (issue_id, issue_type) REFERENCES issues(issue_id, issue_type)
);

CREATE TABLE features (
  issue_id INT NOT NULL PRIMARY KEY,
  issue_type ENUM('F') NOT NULL DEFAULT 'F',
  text TEXT,
  FOREIGN KEY (issue_id, issue_type) REFERENCES issues(issue_id, issue_type)
);

The issue is with the features table where I'm not able to insert any row, because of ERROR (1452): Cannot add or update a child row: a foreign key constraint fails ...

-- OK
INSERT INTO issues (issue_id, issue_type) VALUES (1, 'B');
INSERT INTO issues (issue_id, issue_type) VALUES (2, 'F');
INSERT INTO bugs(issue_id, issue_type, text) VALUES (1, 'B', 'bar');
-- ERROR (1452): Cannot add or update a child row: a foreign key constraint fails (`...`.`features`, CONSTRAINT `features_ibfk_1` FOREIGN KEY (`issue_id`, `issue_type`) REFERENCES `issues` (`issue_id`, `issue_type`) ON DELETE CASCADE ON UPDATE CASCADE)
INSERT INTO features(issue_id, issue_type, text) VALUES (2, 'F', 'baz');

I guess this is caused by the incompatible ENUM definitions in issues and features table.

My question is what do you see as best alternative to do this?

I can think of these options:

create issue_types table and reference it from all tables; in bugs and features check allowed values in trigger
instead of ENUMs use BIT(1) and check allowed values in trigger (without the list in separate table)


texts: What relevance does the table-level ANSI_NULL setting have?

texts:
Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here).

However, when I execute

SELECT * FROM myVeryOldTable WHERE someFieldWithNullValues = NULL

I get zero results, even though the old table was created with ANSI_NULL OFF. So, apparently, SQL Server uses the current connection setting, which is ANSI_NULL ON.

If that is the case, what difference does the setting stored in the table make (except for making trouble in various cases)? Why is it stored at all, if the connection setting always overrides the table setting?


texts: Remote connection scheme without Windows Authentication/Domain in detail

texts:
There is a legacy C++ system that needs to be "translated", that uses ODBC for connecting to remote SQL Server Database.

System is distributed to many different OS and different domains, some always online, some updated on connection, some updated via sending/receiving updates in the form of files.

Each user has been registered in the SQL Server and with certain user roles on the database.

When a user is trying to login to the system, he actually sends his id and password to the application, which in turn are packed with a common/central SA account and query the central / distributed database where a table exists with all the relevant id/passwords, depending on the role of the local domain.

If the user has been registered in the database and his id and pass are a match, then he is given the green light to enter the application and his user id and password are thereafter used on every transaction, somehow variably hard-coded, in order to avoid to system questioning the id and pass on every recordset buildup.

My question is how this scheme should be modified to comply with established best practices on remote SQL Server access.

PS: Should a creation of database users is preferred, how a new user can insert his credentials to create a new profile on the database without elevating his role to more than datareader / datawriter


texts: Does the order of columns in a covering index matter?

texts:
I have the following covering indices:

INDEX (col1, col3); -- index 1
INDEX (col1, col2, col3); -- index 2

because I would like to support the following type of queries:

1)

  SELECT col3
    FROM my_table
   WHERE col1 = ... AND
         col2 = ...
ORDER BY col3

2)

  SELECT col3
    FROM my_table
   WHERE col1 = ...
ORDER BY col3

I am unfamiliar with how a covering index works.  Is index 1 redundant? Or does a covering index requires that the columns be side by side?


texts: Is it safe for me to switch from an 8GB server to a 4GB server? (Stats inside)

texts:
We are looking to cut some of our server costs and the only place I think it might be possible is our db server.

We're currently running an 8GB rackspace cloud that houses a mysql instance and I believe is running a sphinx searchd instance as well.

Here are the stats from free -m:

       total       used       free     shared    buffers     cached
Mem:    7971       4748       3223          0        773       2773
-/+ buffers/cache: 1201       6769
Swap:  16378        467      15911

Here are the stats from top:

top - 15:23:52 up 349 days, 19:28,  1 user,  load average: 0.39, 0.72, 0.92
Tasks: 141 total,   1 running, 139 sleeping,   0 stopped,   1 zombie
Cpu(s):  0.5%us,  0.1%sy,  0.0%ni, 98.1%id,  1.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8162332k total,  4861112k used,  3301220k free,   791832k buffers
Swap: 16771852k total,   478512k used, 16293340k free,  2839936k cached

It looks like were using just about half of our available 8Gigs but it would save us almost $200/mo if we could downgrade to a 4GB instance.

Any advice on this situation is much appreciated.

Thank You


texts:
I installed DB2 Express 10.1 on my Windows 7 (64bit) notebook. Now after the installation finished I cannot connect to the database. During the installation I specified "C:\Daten\db\db2" as the directory to be used for DB2.

When trying to connect, I get the following error:

  SQL1031N  The database directory cannot be found on the indicated file system.

However the database seems to be there:

db2 => connect to db2inst
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031

db2 => list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = DB2INST
 Database name                        = DB2INST
 Local database directory             = C:\Daten\db\db2\IBM\DB2\DB2INST
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

I verified that the file system privileges are correct for the directory (the user running the DB2 service has full control on that directory and I made him the owner as well).

The content of C:\Daten\db\db2\ looks like this:

\---IBM
    \---DB2
        \---DB2INST
            +---cfg
            +---cfgcache
            +---DB2
            |   +---ctrl
            |   +---hmonCache
            |   |   \---DB2
            |   |       \---0
            |   |           \---ossipcres
            |   +---log
            |   +---security
            |   |   \---auditdata
            |   +---spmlog
            |   |   \---SPMLOGSD
            |   +---SQLDBDIR
            |   +---TMP
            |   \---UIF
            +---DB2DAS00
            |   +---cfg
            |   +---dump
            |   +---log
            |   +---metadata
            |   \---tmp
            |       +---dastmp
            |       \---schedtmp
            +---db2dump
            +---function
            |   \---unfenced
            \---license

I then tried to re-catalog the database:

db2 => uncatalog database db2inst
DB20000I  The UNCATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
db2 => catalog database db2inst on "c:\Daten\db\db2\IBM\DB2\DB2INST\"
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

But that didn't help either.

For some reason it seems DB2 can't access the (existing) directory:

db2 => list db directory on "c:\Daten\db\db2\IBM\DB2\DB2INST\"
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031
db2 => list database directory

I'm not experience with DB2 and the last time I installed Express everything went smoothly, so I'm a bit lost now.


texts: Which join to use ? and how to add rank column on the result

texts:
I have two tables

Table4 - Columns (ID, NAME, Money)
Table5 - COlumns(ID, PLACE)

Note: Table 4 has more IDs than table5

I am trying to use a join such that I get the following table

Table6 - Columns(Rank,ID,Place,Name,Money)

Also I want it to be such that when I get this result the ID values that are contained in Table4 whether or not they are contained in Table5 tp retain their ID and Name and Money Values.

In addition I would also like to have a rank column in the new table as the first column where the rank shown is 1 if your Money is the highest in the ID, and 2 if it is second highest etc.

So far I tried using the following join

Select *
From Table4
  left outer join Table5  on Table4.ID = Table5.ID

but the problem here is that the LID values unique to table4 drop their corresponding values for name and money.

Also how can I use the rank function here to get the desired result I tried using this but to no avail

select *
from (
   SELECT  RANK() OVER (PARTITION BY ID ORDER BY Money DESC) as RN,ID,Place,Name,Money
   FROM Table4
)


texts: Need help in incrementing the date in oracle

texts:
Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this.

select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' from DUAL;

Expected output (if :To_date variable is '09-DEC-2014'):

'10-DEC-2014'


texts: MySQL performance, UNION then JOIN, or JOIN on each part of the UNION?

texts:
Which does the optimiser handle better?

SELECT TBL.*,J.* FROM (
    (SELECT ...,T.ThingId FROM ...)
    UNION
    (SELECT ...,T.ThingId FROM ...)
) TBL
LEFT JOIN ThingDetails TD USING(ThingId)
....

Or

(SELECT ...,T.ThingId,TD.* FROM ... LEFT JOIN ThingDetails TD USING(ThingId))
UNION
(SELECT ...,T.ThingId,TD.* FROM ... LEFT JOIN ThingDetails TD USING(ThingId))

I would imagine in simple cases that it doesn't matter much, but I wondered what recommendations you guys may have. I don't want to experiment much here because of the time taken to actually write test queries and even then they'll be very specific cases. I'd like to know what the optimiser can apply here.

As an example of an optimisation I know through experience, replacing an OR with a XOR if you know both operands cannot both be true can help!

I'd also be curious to know about WHERE statements, On each part of the union, or outside? Some testing shows that it actually may matter (an <autokey0> pops up if I use a where outside of union) - I shall keep investigating but a point in the right direction would be great!

Any information on MariaDB or MySQL is welcome. I'm not looking for specific behaviour in each version and going back as far as 5.1 at least MySQL is able to factor in and out various constraints, I'm looking for detailed optimiser behaviour.

My own limited testing showed that MySQL both factored in some joins and factored out others. In fact one of my tests involved MySQL returning the first union in the order of an index of the table it was joining to's primary key. The as-if rule states that any optimisation is okay as long to the observer it is as if what they wrote actually happened. I've had problems with this before. It was actually a bug in MySQL 5.1 -  I now know it's because of how InnoDB works.

This question is really about what 'confuses' the optimiser enough to not be able to make these observations.


texts: What is the difference between setting the directive default-character-set under [client] as opposed to [mysqld]?

texts:
Reference: https://stackoverflow.com/a/8690422/784637

Correct me if I'm wrong, but the best answer suggests a way to force everything being inputted into the database to be encoded in utf-8 and everything going out of the database to be encoded in utf-8.

What's the difference between the following two:

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

Does the directive under [mysqld] force all the writes to the database to be encoded under a certain format and the directive under [client] force all of the output to be encoded in a certain format?

For example if the following two directives are set:

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

and a there's a php string $var that is encoded in latin1, if that string is inserted into the database, it will be stored in the database as utf-8.  But if a select statement is performed to retrieve that value, it would be encoded in latin1.


texts: Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)?

texts:
I am having dozens of MSSQL Servers 2012 R2 on Windows Server 2008 R2 with similar setup, configuration (in virtual machines) in production.  Neither has SSIS installed.

I have to similarly maintain (migrate, restore) the same maintenance plans on all of dozens servers

Any way to import-export-migrate-save-restore maintenance plans without having SQL Server Integration Service (SSIS) and Data Tools (BIDS) installed (and running)? Though I can stage a separate server machine for them

Saw the answer to "Running an SSIS package without the SSIS service installed" but still uncertain


texts: Tablespace 'XXX_SML_INDEX' does not exist using impdb

texts:
I am new in oracle. I am trying to import an Oracle database from one machine to another by using a dump file. I'm using the below command for the import

  impdp MOBILETEST/MOBILETEST directory=MOBILETEST
  dumpfile=MOBILETEST.dmp logfile=MOBILETEST.log full=y;

I get the error: "tablespace 'XXX_SML_INDEX' does not exist"

  ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
  ORA-00959: tablespace 'XXX_SML_INDEX' does not exist Failing sql is:
  DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR :=
  'ALTER USER "MOBILETEST" QUOTA UNLIMITED ON "XXX_SML_INDEX"';  EXECUTE
  IMMEDIATE SQLSTR;EXCEPTION   WHEN OTHERS THEN    IF SQLCODE = -30041
  THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
  WHERE TABLESPACE_NAME = ''XXX_SML_INDEX'' AND CONTENTS =
  ''TEMPORARY''';      EXE ORA-39083: Object type TABLESPACE_QUOTA
  failed to create with error:

I am trying to use remap_tablespace but dont know where I can get
second TABLESPACE_QUOTA name, like remap_tablespace=XXX_SML_INDEX: ?


texts: What is the difference between these two index declarations and do I need both?

texts:
I was examining a script with 'Display Estimated Execution Plan' and it made several recommendations for indexes to add to the database.  I added them and the script did indeed run much faster.  Now I'm looking at putting these into our standard form for inclusion into our regular upgrade scripts and I noticed what might be a redundancy:

CREATE NONCLUSTERED INDEX [TableA_ColA]
ON [dbo].[TableA] ([ColA])
INCLUDE ([ColB])
GO

CREATE NONCLUSTERED INDEX [TableA_ColB_ColA]
ON [dbo].[TableA] ([ColB],[ColA])
GO

I can see from the documentation that there are some differences between the two indexes as far as space usage is concerned, but in this case ColA is int and ColB is nvarchar(255), well under the 900 byte limit, so I'm not sure that the INCLUDE in the first index is necessary.

So is this just an arbitrary result of the execution plan analyser?  It seems to me if I have the second index the first one is redundant?


texts: Create Temp Table From Stored Procedure Result

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

Is there something like

create table  from

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

I am running SQL server 2008


texts: Remove all values of a key in PostgreSQL hstore

texts:
How do I delete/remove the value of a postgresql hstore column?

I've got a table named pages and a column named settings - I need to remove the read_only value (basically, set it to empty string or nil).


texts: Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens?

texts: Assume I have two tables, table A and table B. Table A has an insert trigger that inserts a record into Table B. Table B has an insert trigger that inserts a record into Table A. What would happen in this case? Would the two triggers keep inserting records into the tables over and over until the database filled up? Is there some limit to how many times this will happen?


texts:
I have created a trigger to audit changes to a table:

CREATE TRIGGER [dbo].[iudt_AutoAuditChanges]
   ON  dbo.CPTCategoryMaster
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    Declare @v_AuditID bigint

    IF OBJECT_ID('dbo.AutoAudit','U') IS NULL BEGIN
        CREATE TABLE [dbo].[AutoAudit]
        (   [AuditID] bigint identity,
            [AuditDate] DateTime,
            [AuditUserName] varchar(128),
            [TableName] varchar(128) NULL,
            [OldContent] XML NULL,
            [NewContent] XML NULL
        )

        ALTER TABLE dbo.AutoAudit ADD CONSTRAINT
        PK_AutoAudit PRIMARY KEY CLUSTERED
        (
            [AuditID]
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

        CREATE NONCLUSTERED INDEX [idx_AutoAudit_TableName_AuditDate] ON [dbo].[AutoAudit]
        (   [TableName] ASC,
            [AuditDate] ASC
        )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    END

    Select * Into #AuditDeleted from deleted
    Select * Into #AuditInserted from inserted

    While (Select COUNT(*) from #AuditDeleted) > 0 OR (Select COUNT(*) from #AuditInserted) > 0
    Begin

        INSERT INTO [dbo].[AutoAudit]
            ( [AuditDate], [AuditUserName], [TableName], [OldContent], [NewContent])
        SELECT
            GETDATE(),
            SUSER_NAME(),
            [TableName]=object_name([parent_obj]),
            [OldContent]=CAST((SELECT TOP 1 * FROM #AuditDeleted D FOR XML RAW) AS xml),
            [NewContent]=CAST((SELECT TOP 1 * FROM #AuditInserted I FOR XML RAW) AS xml)
        FROM sysobjects
        WHERE
            [xtype] = 'tr'
            and [name] = OBJECT_NAME(@@PROCID)

        Set @v_AuditID = SCOPE_IDENTITY()

        Delete from AutoAudit
        Where AuditID = @v_AuditID
            AND Convert(varchar(max),oldContent) = Convert(varchar(max),NewContent)

        Delete top(1) from #AuditDeleted
        Delete top(1) from #AuditInserted

    End
END

This produces XML data like:

row CPTCategoryId="1" CPTCategoryName="NEW CHARGE" Remarks="" Status="1" CreatedBy="11" CreatedDate="2014-05-01T12:50:16.947" ModifiedBy="11" ModifiedDate="2014-05-01T14:20:47.793" LockVersion="0"

How can I separate the column data (CPTCategoryId, CPTCategoryName, Remarks... etc) for display in a grid view? Or is there a better way to implement this requirement?


texts: T-SQLRevoke permissions to all tables in the sql server in one go

texts:
How can we revoke a certain permission to all tables?

I've tried (T-SQL):

REVOKE SELECT on schema::DBO TO [public] AS [dbo]

But nothing happens.. All tables still have Select permission.

I can take one by one

REVOKE SELECT on  dbo.table TO [public] AS [dbo]

One by one works ok.

How to revoke permissions from all tables of a specific schema in one go?

Do I need to build  a cursor for this?


texts: Items from last month AND if not possible, items from this month

texts:
So the title is not really clear, but I could not think how to put it into shorter words better.

My problem is:

I have a database, which I use for web-scraping, with different articles and their prices. The prices may change each month. To be able to have a history, I created two tables:

items which has all articles that are currently in this month's crawling. The table has all the infos on every article
log which has only an article-id, price and date

So basically in items every article is unique, while log can have multiple occurrences of an article.

What I want to do now: I have created a view which yields every article from items and among other things calculates the price difference to last month (as the price and date in items is updated each month).

Here's an excerpt from my view:

CREATE VIEW XLSX AS
    SELECT DISTINCT ON(items.article)
           items.article,
           items.price as "price_new",
           ROUND(CAST(items.price - log.price as numeric), 2) as "price_difference",
           log.price as "price_old"
    FROM   items
    INNER JOIN log
    ON     items.article = log.article
    WHERE  (items.date - log.date) < 25
    ORDER BY items.article, log.date

The problem I now have is that new articles are currently not being included in the view. Say for example that at the start of July I have 20 new articles, which are inserted into items and log with date = '2017-07-01'. Obviously they are not included in the view, due to the constraint to only include articles from log that are older than 25 days (since the crawler may run periodically multiple times a month, updating the prices more than once).

Could I use CASE here to get articles that are older than 25 days or if there are none, get those that have the same date?


texts: Where can I learn about MySQL master/slave, master/master, or clustering setups?

texts:
What are the best resources you've read or watched about setting up master/slave or clustering for database replication?

I don't know much about this. I have a database that is getting rather large and would like to make sure that if something goes wrong, I at least have some basic database infrastructure going on so I can recover data.

I'm using MySQL. I'm guessing that I do just as many reads as writes.


texts: How to return rows between two datetimes stored as date and time?

texts:
How to view the result between 2 dates and time ?

My current Code:

SELECT *
FROM [DB].[dbo].[TABLE]
WHERE [USER_ID] = '005'
AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016'
AND [TIME] >= '21:00:00'                   -- for date 03-04-2016
AND [TIME] <= '05:00:00'                   -- for date 04-04-2016

ORDER BY [DATE] ASC, [TIME] ASC

This code does not work and I do not know how to fix it

why doesn't this request work ?


texts:
Given the following select that will count related entities for every record
SELECT MAIN.*, SUB.app_count
FROM my_table_view MAIN
     LEFT JOIN (SELECT MAIN2.p_key,
                       count(DISTINCT T3.app_id) AS app_count
                FROM my_table MAIN2
                         JOIN T1 ...
                         LEFT JOIN T2 ...
                         LEFT JOIN T3 ...
                GROUP BY MAIN2.p_key) SUB ON MAIN.p_key = SUB.p_key
WHERE ...

Trying to simplify it, came up with the following :
SELECT MAIN.p_key, count(DISTINCT T3.app_id) AS app_count -- select MAIN.* errors out - missing GROUP BY
FROM my_table_view MAIN
     LEFT JOIN T1 ON ...
     LEFT JOIN T2 ON ...
     LEFT JOIN T3 ON ...
WHERE ...
GROUP BY MAIN.p_key

How to select all data from the MAIN table without adding a bunch of GROUP BYs ?
Would also appreciate performance hints.
Edit
I think this is related
Postgres GROUP BY id works on table directly, but not on identical view


texts: PostgreSQL: Log statements which do sequential scans

texts:
I use this snippet to detect missing indexes:

https://stackoverflow.com/a/12818168/633961

Example:

SELECT
   relname                                               AS TableName,
   to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
   to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
   to_char(n_live_tup, '999,999,999,999')                AS TableRows,
   pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
 FROM pg_stat_all_tables
 WHERE schemaname = 'public'
       AND 50 * seq_scan > idx_scan -- more then 2%
       AND n_live_tup > 10000
       AND pg_relation_size(relname :: regclass) > 5000000
 ORDER BY relname ASC;

Result:

tablename | totalseqscan | totalindexscan | tablerows | tablesize
----------+--------------+----------------+-----------+----------
 mytable  |      112,479 |      2,978,344 | 1,293,536 |   1716 MB

I am curious - I would like to see which SQL statements actually does a seq scan on table mytable.

Is there a way to let PostgreSQL emit a warning if it does a sequential scan on this table?


texts: How to set delayed replication with MariaDB 10.0.11

texts:
Is it possible to set delayed replication on MariaDB 10.0.11?
I tried the following command:

CHANGE MASTER TO MASTER_DELAY = N;

It returns the following error:

  You have an error in your SQL syntax; check the manual that
  corresponds to your MariaDB server version for the right syntax to use
  near 'MASTER_DELAY = 259200' at line 1

If not supported, are there any other recommended solutions to protect against user mistakes on the master & allow a DBA to roll back a delayed slave to the time just before the disaster ?

Thank you,


texts: COUNT(*) on InnoDB whenever phpMyAdmin loads

texts:
It's a known issue that innodb is slow in

SELECT count(*) FROM Table

Hence I avoided that. But I notice that whenever I login to phpmyadmin, it will automatically execute a count(*) for my innodb table with 19k rows, which can take up to a minute to execute. Yet it doesn't initiate such count(*) for the other innodb table with 4 million rows.

Another clue, in phpmyadmin, under the Records column, the number of records for the 19k-row table is showing the exact value whereas the 4-million-row table is showing an approx.

I understand innodb table gives an estimation of the row count. But it seems that for smaller table, in this case with 19k rows, phpmyadmin decides to do a count(*) instead of just giving an estimation.

Question is, is there a way to stop phpmyadmin from doing such count on my innodb table? It's okay to just give an estimation instead of doing an expensive row count whenever I refresh my phpmyadmin screen.


texts: Is there some sort of shortcut/feature in PostgreSQL to avoid horrible storage waste with duplicate values?

texts:
Let's say that I have this table which keeps track of every page load on my website:

CREATE TABLE "example.com page loads"
(
    id                      bigserial,
    "URL"                   text NOT NULL,
    "IP address"            inet NOT NULL,
    "user agent"            text,
    "timestamp"             timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY             (id)
)

If the same person loads 100 pages, or many other people with the same exact user-agent string load 10,000 pages, the same long "user agent" string will be stored redundantly 100/10,000 times in my poor table, massively inflating it.

This was always a huge problem to me when I used to use plaintext webserver logs, and later when I did the exact same thing as I'm describing right now (a database table in PostgreSQL).

A very obvious and immediate thought that pops up in my head is: "Why can't the user agents be automatically stored just once, internally, and then automatically 'referenced' by PostgreSQL in whatever manner it is comfortable with, while never exposing this internal optimization to me?"

That is, I don't want to have to make a separate table like this:

CREATE TABLE "example.com unique user agents"
(
    id                      bigserial,
    "user agent"            text,
    PRIMARY KEY             (id),
    UNIQUE                  ("user agent")
)

... and then be forced to do expensive and annoying manual queries to look up whether the user-agent is already present in the table of unique user agents and then have a column called "unique user agent id" referencing this table from the "page loads" table instead of a nice, simple text column.

I'm sure you understand exactly what I mean. Basically, it's such a common/obvious thing that I'm 99% sure that this must already have been solved a looong time ago, only I have just never realized it.

There is probably some simple feature to do exactly this, such as (this is just my guess):

CREATE TABLE "example.com page loads"
(
    id                      bigserial,
    "URL"                   text NOT NULL,
    "IP address"            inet NOT NULL,
    "user agent"            text OPTIMIZE_UNIQUELY_INTERNALLY,
    "timestamp"             timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY             (id)
)

That would be lovely, if there is such a "OPTIMIZE_UNIQUELY_INTERNALLY" flag that I can just apply to columns when I want this to be done "under the hood", without me having to think about it!

If there is such a thing, that would save me enormous amounts of storage and headaches.

I don't think that this is the same thing as indexes. Making the "user agent" column into an index won't make PG store each unique value only once, would it? It would only create an additional "look-up" table for quicker queries?


texts: what is the downtime of mongodb cluster when upgrade to 3.0

texts:
I am following  http://docs.mongodb.org/manual/release-notes/3.0-upgrade/ to upgrade mongodb cluster from 2.6 to 3.0. The downtime is less than 1 second? because it takes less than 1 sec to elect one secondary to become primary. Am I right? I need to double check the whole process and the downtime...

thanks.


texts: How do MySQL 5.7 virtual columns differ from views?

texts:
Starting reading point: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/

Running some tests with the most recent percona 5.7  they seem to work well. Explain plans and wall time look pretty good but I'm not seeing any difference in show table status index_length or data_length.

How do you find the true footprint or overhead for using these?

Unlike views they do seem to be faster than bookmarked selects.


texts: ROUTINE TO SELECT FOM A TABLE AND UPDATE ANOTHER TABLE

texts:
I do not understand how to update a table from another table after a table has been modified.

I have a table with 3 columns like id(INT), distname(VARCHAR)UNIQUE and score(INT), and I have another table with id(INT), name(VARCHAR), points(INT), penalizes(INT)

each time a name gets some points they go to the second table.

I want to write a routine that makes the following

SELECT DISTINCT(name) AS distname,
       SELECT(SUM(points)-SUM(penalizes)) AS score
    FROM table2

and updates the table1 each time before someone makes a select on table1, or each time someone enters data into table2

How would this look like?


texts:
I want to format my string in my select statement but I cant seem to find the syntax for this pattern.

I have a column of card_num with 16 digits and i want to have a dash(-) after the first four digits.

Original card_num:
1234567891234567

Desired output:
1234-567891234567

I think regex can do this but cant find elsewhere the right code for this pattern.

Any idea how to do this? Thanks.


texts: Update row with multiple row in another table Error : ORA-01427: single-row subquery returns more than one row?

texts:
I have Table A with 1 row and wanted to update it from another table (which has 3 rows for it and I can take any one of them).

Table A
-----------
TrnID | trn_Status
 2345 | null
 9876 | null
 9878 | null

Table B
-----------
TrnID | trn_Status
 2345 | 2
 2345 | 2
 2345 | 2
 9876 | 4
 9876 | 4
 9878 | 2

I am facing error when i try to do something like this

UPDATE TableA tabA
SET    ( tabA.trn_status ) = (
                            SELECT trn_status
                            FROM   TableB tabB
                            WHERE  (
                                        tabA.trn_id = tabB.trn_id
                                    )
                         ) ;


texts: Deploy materialized view with database link using another user

texts:
Our database deployment process logs in as sys to deploy database objects.  We ran into a problem when we wanted to deploy a private database link for another user (AU).  Our solution was to:

Create a procedure owned by AU that uses an execute immediate to create the link
Run the procedure
Drop the procedure.

This worked great!  Unfortunately it did not work when we tried to script the creating of a materialized view owned by AU that uses its private database link.  It appears that it connects to the remote database using the logged in user (sys) not the user that owns the link (AU) and therefore fails:

ORA-04052: error occurred when looking up remote object
AU.Table_Name@Private_Link_Name
ORA-00604: error occurred at recursive SQL level 2
ORA-01017: invalid username/password; login denied
ORA-02063: preceding line from Private_Link_Name
ORA-06512: at "AU.temporary_procedure", line 3
ORA-06512: at line 1

The materialized view creates fine when done by AU directly.  Here is what the procedure looks like:

create procedure AU."tmp_doit_20180509" is
Begin
   execute immediate 'create materialized view m1 as select 1 from dual@private_link_name';
End;
/

The database link does not have credentials defined, so it uses the credentials of the connected user.  When logged in as AU these are the same between databases, but for sys they are different.

Here are some alternatives I have considered:

Manually log in as each user needing to create materialized views with database links - this breaks our automated deployment process.
Use a dbms_scheduler job to create the mview - this also does not work and fails similarly to the procedure method because the job runs in the context of the user that created it even if it is in another schema.
Store credentials for users and login as each specific user to do the deployment - depending on how this is done, it seems that it would either be complex to build to make it secure or complex to maintain, perhaps both.
Connecting as a proxy user - gives the same error.


texts:
Experts,

I am trying to learn more about the CONVERT_IMPLCIT and Probe Residual.
Can someone explain to me why the following test script cause  Probe Residual?

My understanding is if both join predicate column are of the same datatype and is defined as "NOT NULL" then it should not cause any Residual

Reference and Test script

Join Performance, Implicit Conversions, and Residuals

CREATE TABLE #BuildBigInt
(
    col1    BIGINT NOT NULL
);

CREATE TABLE #ProbeBigInt
(
    col1    BIGINT NOT NULL
);
GO

DECLARE @I INTEGER = 1;

INSERT #BuildBigInt
    (col1)
VALUES
    (CONVERT(BIGINT, RAND(1) * 9999999));

 WHILE @I < 5000
BEGIN
    INSERT #BuildBigInt
        (col1)
    VALUES
      (CONVERT(BIGINT, RAND() * 9999999));
    SET @I += 1;
END;

INSERT #ProbeBigInt
        (col1)
VALUES
    (CONVERT(BIGINT, RAND(2) * 9999999));

WHILE @I < 5000000
BEGIN
    INSERT #ProbeBigInt
        (col1)
    VALUES
        (CONVERT(BIGINT, RAND() * 9999999));

    SET @I += 1;

END;

--test query

SELECT
    COUNT_BIG(*)
FROM #BuildBigInt AS bbi
JOIN #ProbeBigInt AS pbi ON
    pbi.col1 = bbi.col1
OPTION (MAXDOP 1);


texts: Converting a composite primary key to composite unique, and adding an auto-incremented PK column

texts:
Background

I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign keys to their respective tables, and also composite primary keys.

Before this table grows any further, I want to add an id column (NOT NULL AUTO-INCREMENT PRIMARY KEY), and instead let col1, col2 have a composite unique constraint.

The reasons for me wanting to do this lies in the business logic.

Question

Is there anything I should be particularly aware of before doing this? I'm going back and forth as to which order I should do it in.

Any insight appreciated!

Assumptions

The added column will of course increase required storage space. This is not an issue.

Bandwidth and overhead in terms of CRUDing towards the table is also accounted for.

Setup: MySQL / InnoDB

The why

The comments have been deleted, but there were some people asking why I wanted to make this change, and told me that it was a stupid change to make.

I'll answer it to prevent the XY problem (even though I don't think it's necessary in this case), and because the question has remained answerless thus far.

The reason I want to make this change, is because this link table will be linked to, from at least one other table, namely a change log.

Consider the change log table having the columns: id, col1 (FK), col2 (FK), user_id, action, timestamp. Now, if I want to have a changelog, I'm duplicating the entire original link table by virtue of having both col1 and col2 there.

At this point, it's an even trade. Saved the space in the link table, wasted the same amount of space in the change log table.

Now, who's to say I'll never need another table to link to it? That would be a pretty big assumption to make.


texts:
I am trying to copy data from table to another mysql table from two different databases
First I get all data from first table and stored them into php variable
then I loop on that var to insert data to the other table
using this query

INSERT INTO `users` (`nUserId`, `vLoginName`, `vFirstName`, `vLastName`, `vEmail`)
    VALUES ('$entity_id','$name','$firstname','$lastname','$email')
    WHERE NOT EXISTS (SELECT `nUserId`,`vEmail`
                      FROM `users`
                      WHERE `nUserId`='$entity_id'
                      AND   `vEmail` = '$email')

but it's not working

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT `nUserId`,`vEmail` FROM users '

What the correct syntax to make this work ?


texts: Grant select on tables with a specific naming pattern

texts:
How to grant select on all tables starting with VVC_?

grant select on VVC_* to user1


texts: Monitoring Maintenance Tasks in AlwaysOn Clusters

texts:
I have an environment with multiple AGs on a single server and they encounter a split situation in which some end up on one node and some end up on the other node (in a failover event, perhaps). When maintenance tasks (the basic gamut of reindexing, update, etc.) execute on these nodes, they will only effectively perform operations on databases which are read_write on the primary side of their respective AGs.  This causes the job to fail since the databases are read_only on the secondaries.  Therefore, in a split AG situation, both nodes will always show failed maintenance in the job history.

Needless to say, it is difficult to monitor these clusters.  I have proposed two possible solutions to management.  We either script the maintenance tasks using DBCC commands for the individual databases based on their updateability properties, or we simply limit ourselves to one AG per server.  They don't like either solution.  Does anyone know of a way I can keep the built-in SQL Server maintenance tasks and have them target only the primary databases?


texts:
I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise):

SELECT  id,
  sum(hours) AS totalhours
  FROM mytable
  GROUP BY id
  HAVING sum(hours) > 50;

My question now is, whether or not this is suboptimal. As a programmer, this query looks like the DB will calculate the sum twice. Is that so, or should I rely on optimizations the DB engine will do for me?

Update: an explain of a comparable query:

postgres=> explain select sum(counttodo) from orderline group by orderlineid having sum(counttodo) > 100;
                             QUERY PLAN
--------------------------------------------------------------------
 HashAggregate  (cost=1.31..1.54 rows=18 width=8)
   Filter: (sum(counttodo) > 100)
   ->  Seq Scan on orderline  (cost=0.00..1.18 rows=18 width=8)
(3 rows)


texts: Do NT Service accounts need to be SysAdmin when using Domain Accounts

texts:
I'm not sure if I'm googling for the wrong thing but I can't find an answer. We're running SQL 2014. The SQL Server and SQL Agent processes are both running under Domain Managed Service Accounts.

When SQL was installed NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, NT SERVICE\SQLWriter, NT SERVICE\Winmgmt are all setup as SysAdmins.

If I am running SQL and SQL Agent as a Domain user, do I still need these NT Service accounts, and/or do they need to be SysAdmin?


texts: Replicating a remote MySQL database to MS SQL Server 2008

texts:
I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database.

Is this possible? Can anyone outline the steps required in order to achieve this?

Thanks.


texts: LOAD DATA mysql command and use only one column

texts:
I have table t1 with three columns, and a file per line a text.
my tables contains id,sura and aya fields. I need to fill only aya from File.
I use the following command:
load data infile '/home/mohsen/codes/haq/translation-tmp/fa.ghomshei.txt' into table tr_fa_ghomshei LINES TERMINATED BY '\n' ;

Now question is :
How can I limit to aya field?


texts: How to test whether unique index can be created in MongoDB?

texts:
I want to add a new unique compound index to our production system. Before doing the change, I would like to check whether the field combination is unique, i.e. will the index creation succeed or fail. (The combination should be unique, but there's always the chance something has gone wrong earlier.)

How can I check whether creating a unique index would succeed, without actually doing the change?


texts:
Working with traces on production server I found some mysterious entries to me in sys.Traces, When I run below query it shows me status of all traces as ‘Paused’ along with entries in sys.dm_exec_sessions, which scenarios generate these types of records.

SELECT t.id
 ,CASE t.status
 WHEN 0 THEN 'Paused'
 ELSE 'Running'
 END AS status
 ,t.reader_spid
 ,t.start_time
 ,s.HOST_NAME
 ,s.program_name
 ,s.login_name
 ,t.*
FROM sys.traces AS t
 LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = reader_spid
WHERE t.is_rowset = 1
and is_default<>1


texts: MySQL - Consequences of high rate inserts

texts:
I have a cluster of asynchronous workers that will be extracting text from PDFs and inserting them into a single MySQL table with no relations.  This data will then be slurped into a Solr server via a DataImportHandler.

Each worker will parse a PDF containing 50-1000 pages and insert between 1000-18000 rows of data over eight text columns.  Seven are varchar(255) and one is a longtext with an average length of possibly 500 characters.

Each worker will take anywhere from one to three minutes to complete the task.  The insert occurs via Django ORM's bulk_insert operation.

So, at worst case, I can assume about 270K rows inserted per minute to this table.  Can I expect any configuration issues with a default config server?

Hardware is a standard EC2 T2.small instance with a 100GB SSD EBS volume.


texts: Query runs quicker when column calculation is done after initial query

texts:
I have always been led to believe that since SQL evals the SELECT after filtering that putting a calculation in the select of a query should be as fast or faster than running the query and then doing the calculation over the result. So I assume I am doing something wrong in the following query. The JOINS and the WHERE are the same in both, in the first the calculation is done after the result returns (takes about 3 seconds). The second query has the same calculation in the initial query (takes about 1 minute). I have added the query plan. Running on SQL 2008.

  DECLARE @MsgTable TABLE (
    [Type] VARCHAR(64),
    [EventDate] [datetime],
    [LicenseId] bigint,
    [RequestedOn] [datetime]
  );

  -- Calculate AFTER
  INSERT INTO @MsgTable
  SELECT
    Type,
    EventDate,
    EventLog.LicenseId,
    NULL
  FROM EventLog
    INNER JOIN #LTable L ON (EventLog.LicenseId = L.LicenseId)
    INNER JOIN LicenseConfig LC ON LC.LicenseId = EventLog.LicenseId AND ((LC.ConfigKey = 'ConversionStatus'))
  WHERE
    (EventLog.Type IN ('ERPValidationRequest', 'ERPMappingComments'))
    AND
    (EventLog.EventDate >= @StartDate)
    AND
    (EventLog.EventDate < @EndDate)
  ;

  SELECT
    Type,
    EventDate,
    LicenseId,
    (
      SELECT TOP 1 ROEventLog.EventDate
      FROM EventLog ROEventLog
      WHERE
        ROEventLog.LicenseId = MT.LicenseId
        AND
        ROEventLog.Type = 'ERPMapRequest'
        AND
        ROEventLog.EventDate < MT.EventDate
      ORDER BY ROEventLog.EventDate DESC
    ) RequestedOn
  FROM @MsgTable MT;

  DELETE FROM @MsgTable;

  -- Calculate during
  INSERT INTO @MsgTable
    SELECT
      Type,
      EventDate,
      EventLog.LicenseId,
      (
        SELECT TOP 1 ROEventLog.EventDate
        FROM EventLog ROEventLog
        WHERE
          ROEventLog.LicenseId = EventLog.LicenseId
          AND
          ROEventLog.Type = 'ERPMapRequest'
          AND
          ROEventLog.EventDate < EventLog.EventDate
        ORDER BY ROEventLog.EventDate DESC
      ) RequestedOn
    FROM EventLog
      INNER JOIN #LTable L ON (EventLog.LicenseId = L.LicenseId)
      INNER JOIN LicenseConfig LC ON LC.LicenseId = EventLog.LicenseId AND ((LC.ConfigKey = 'ConversionStatus'))
    WHERE
      (EventLog.Type IN ('ERPValidationRequest', 'ERPMappingComments'))
      AND
      (EventLog.EventDate >= @StartDate)
      AND
      (EventLog.EventDate < @EndDate)
  ;

  SELECT * FROM @MsgTable;


texts: What is the best practice for mapping from natural keys to integer-based keys? (ETL)

texts:
This question originates more from an "enterprise architecture" point of view. (Reason being, our company is just starting to get into data management, and actually having DBA's -- I know, horrific that it took so long to get to data management and having DBA's, but bear with me).

I'll lay some background first.

I am researching on how to move data from a legacy system to the new replacement system we are building. This data is more "lookup" data than what i would call "business", "customer", or "domain" data (if that makes sense). I guess the way I think of it is this is data that provides end-user's a choice and is then associated to the other types of data.

The challenge is that until we roll off our legacy systems we don't want to maintain the (legacy) data in two systems. We will need to maintain new data that can't work with the legacy system in the new system (so in that sense we will have dual maintenance). But the data that is common we want to initially push or pull from the legacy system.

This means some form of ETL, whether that be manually coded, or whether we use a product.
(From a code perspective that means for us either RPG-since the legacy system is iSeries and DB2 for i-or Java-since the new system is Java EE-based with DB2 on AIX). From a product standpoint that probably means IBM InfoSphere DataStage (as that is product we have chosen to use for ETL for warehousing).

Now to the actual point of my question: The legacy system has data in non-normalized tables that rely on natural keys. The new system is in normalized tables that look more "object oriented" in their design (because of the heavy use of Hibernate). These tables rely more on the traditional integer-based primary key.

I am wondering, how does one best map from a system with natural keys to one with integer-based keys? How do you maintain this mapping so you know when to update records and when to insert new records into the integer-based system.

Any recommendations or best practices in general?

And then as an add on: Anything specific to RPG, Java, or InfoSphere DataStage?

Any ideas are appreciated, as these may be incorporated into our company's best practices we are attempting to build.


texts: MySQL update a record on duplicate key update (merge)

texts:
I have a table products with the follow schema:
CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `article_id` bigint unsigned NOT NULL,
  `price_cents` int unsigned NOT NULL,
  `quantity` smallint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_products_unique` (`user_id`,`article_id`,`price_cents`),
  KEY `fk_products_article` (`article_id`),
  CONSTRAINT `fk_products_article` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_products_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Now I can do INSERT with this query:
INSERT INTO `products` (`user_id`,`article_id`,`price_cents`,`quantity`)
VALUES (1,1,200,1)
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

So now I have 1 product (ID 1) with quantity 1 and price 200.
Now I insert 2 more products with:
INSERT INTO `products` (`user_id`,`article_id`,`price_cents`,`quantity`)
VALUES (1,1,200,1),(1,1,199,1)
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

Now I have 2 products, one (ID 1) with quantity 2 and price 200 and the other (ID 2) with quantity 1 and price 199.
Good.
The problem comes now: I want to update the product with price 199 and set a new price to 200. What I do is:
INSERT INTO `products` (`id`,`user_id`,`article_id`,`price_cents`,`quantity`)
VALUES (2,1,1,200)
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

and what I would like is a single product with id 1, price 200, and quantity 3, but I get Number:0x426, Message: "Duplicate entry '1-1-200' for key 'products.idx_products_unique' because MySQL does not delete the product with ID 2.
Is there a way to achieve this in MySQL (keep in mind that I want to perform these operations in bulk)?


texts:
I am importing data from SQL server to MsAccess via query

Insert INTO labels (linkage_index,label_name_index,label_value_index,label_line_number,extended_label)

select linkage_index,label_name_index,label_value_index,label_line_number,extended_label
FROM [ODBC;Description=Test;DRIVER=SQL Server;SERVER=Del91077\\ing;Database=SPR;User Id=SPR;Password=SPR].labels

its Working fine on developers m/c but failing on Clients machine which is also Win7.I have checked ODBC Drivers And SQL server is available there but still failing.Please help.

  StackTrace:    at
  System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult
  hr)    at
  System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
  dbParams, Object& executeResult)    at
  System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
  executeResult)    at
  System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
  behavior, Object& executeResult)    at
  System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
  behavior, String method)    at
  System.Data.OleDb.OleDbCommand.ExecuteNonQuery()


texts: Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text

texts:
I have several rows with a lot of content and i'm trying to find some code and extract it. It looks like this:

Some code...

&nbsp;

[flv:http://submarino-libre.s3.amazonaws.com/opciones/basico/webready/Video1CursoOpcionesbasicoWebReady.mp4 http://www.submarinobursatil.com/imagenes/portadas/curso-de-opciones.png 600 369]

Duraci&oacute;n: 09:56

Some other code...

Figured out that I need to find [flv: and extract all the line (2 links) ending with ]

So i came out with this query:

SELECT SUBSTRING(`post_content`, LOCATE('[flv:',`post_content`), LOCATE(']',`post_content`)) AS amazonlinks
FROM  posts

First LOCATE function works fine, it finds [flv: but second LOCATE functions doesn't stop the query extraction on ]. It gets a lot more code from the next lines. It only works when i set a number but there is no way i can know the exact lenght number.

Is it possible to use 2 LOCATE functions as parameters of SUBSTRING ? Or what am i'm doing wrong?

UPDATE: Adding DBFiddle:
https://www.db-fiddle.com/f/m62LBUStp1UbRCk2eVNRrY/0


texts: Can I install SQL server 2012 on a same machine with SQL server 2005?

texts: I have been using SQL Server 2005 standard for quite a while, and it is working great. Now I need to use SQL Server 2012. Will this effect my existing databases? How much risk is involved in installing SQL Server 2012 on a machine that has SQL Server 2005 running?


texts: Backup cut-off time for Full, Differential, Log, File, Partial backups

texts:
My understanding was that all backups contain data up to the time/point when backup operation was completed.

Administering a SQL Database Infrastructure - Exam Ref 70-764 says:

  Full: This contains the entire contents of the database and any changes made to the database during the backup operation. Consequently, a full backup represents the database at the point in time when the backup operation finished.

(emphasis mine)

However, for differential backups, the book implies something different:

  Differential: This contains only the differences between the last full database backup and the point in time when the differential backup operation was executed.

Is this correct? Or is this simply imprecise language? Does "executed" here mean the time the differential backup started or completed?

How about for other backup types (Log, Partial, File backup) the book doesn't say this exactly?


texts: Large database backup files integrity check after or before copy

texts:
I was trying to copy large database backup files(appx. 2TB each) to different region using robocopy. But I have noticed the speeds are drastically slow when using the switch /Z. So I have removed this switch and my copy is now faster. But now the challenge is,

how to verify the consistency of copied files.
what is the best possible way to skip the files if the files are already present in target location.
I have tried using powershell get-filehash command to verify file consistency but it’s too slow on large files especially on files located in different region.

My current command syntax,

robocopy source_directory destination_directory file_name /R:3 /W:10 /LOG+:log_file_location

Any best possible ways to address this?


texts: Postgres role does not inherit permissions of superuser role via membership in that role

texts:
In the snippet below, the role writer is a member of a superuser role named admin. The admin role can insert rows into a table but the writer role cannot insert rows into the same table.  Why does the writer role not inherit permissions from the superuser role admin?

CREATE TABLE test_check_roles( id SERIAL NOT NULL );
CREATE ROLE admin SUPERUSER LOGIN;

-- Insert succeeds as the admin role.
SET ROLE admin;
INSERT INTO test_check_roles (id) VALUES (DEFAULT);

-- Insert fails as the writer role with:
--   ERROR:  permission denied for table test_check_roles
CREATE ROLE writer LOGIN INHERIT;
GRANT admin TO writer;
SET ROLE writer;
INSERT INTO test_check_roles (id) VALUES (DEFAULT);

Based on the documentation below (Postgres 12 - Role Membership), I expected writer to have all privileges of the admin user since the writer role has the INHERIT tag.

  Member roles that have the INHERIT attribute automatically have use of the privileges of roles of which they are members, including any privileges inherited by those roles.

Reproducible example

docker run --name pg-test -d --rm \
    --env POSTGRES_HOST_AUTH_METHOD=trust \
    -p 54321:5432 \
    postgres:12.2

psql -h localhost -p 54321 -U postgres -d postgres \
    -c "CREATE TABLE test_check_roles( id SERIAL NOT NULL )" \
    -c "CREATE ROLE admin SUPERUSER LOGIN" \
    -c "SET ROLE admin" \
    -c "INSERT INTO test_check_roles (id) VALUES (DEFAULT)" \
    -c "CREATE ROLE writer LOGIN INHERIT" \
    -c "GRANT admin TO writer" \
    -c "SET ROLE writer" \
    -c "INSERT INTO test_check_roles (id) VALUES (DEFAULT)"

docker stop pg-test

Related questions

Postgres role permission problem: Related to ALTER DEFAULT PRIVILEGES.
Why is a new user allowed to create a table? - Has a good answer describing that privileges aren't hierarchial.

Observations

If I run CREATE TABLE as the admin role instead of the postgres role, the example works. It seems the permission are somewhere lost between postgres owning the table, the admin having superuser, and writer being a member of admin. I don't follow why a table created by the postgres role is accessible by admin by not by writer.


texts: SSRS 2008 Report toggling between images for various field values

texts:
What is the best way for toggling between images on a SSRS report?

On the report I'm creating, there are lots of questions with Yes Or No answers and based on the value I've got for the question, I'd like to show the correct image.

For example we have gender:

  Famale
  Male

This report is for output only and is not going to be used for performing data entry.

For each record I have the value in the column 'Gender' whether the person is male or female.  I want to show a square with a tick mark in it next to the correct gender, while keeping the other option box empty (an empty square image).  This should function like a radio button.

So if the record has gender = 'Female', I should show a tick image next to Female text box and an empty square next to Male text box and, vice-versa, if gender = 'Male' on the record.

Any suggestions?


texts: Don't know how to make two selects bridge together ad run a single query

texts:
I got the races to work then I added another query in addition to the same statement but when I ran the query and it only show student number, dob, gender and ethnicity but did not show another select with races add to.  I cannot figure it out.

select s.student_number,s.dob,s.gender,s.ethnicity
from students s,
(select lastfirst,grade_level,
   max(case when rn = 1 then racecd end) race1, -- find 1st row
   max(case when rn = 2 then racecd end) race2, -- find 2nd row
   max(case when rn = 3 then racecd end) race3  -- find 3rd row
from
 (
   select
      s.lastfirst,s.grade_level,sr.racecd,
      row_number() -- one row per race, numbered 1,2,3,...
      over (partition by sr.studentid
            order by sr.racecd) rn
   from students s
   join studentrace sr
     on s.id=sr.studentid
   where s.enroll_status=0
 )
 group by lastfirst,grade_level
 order by grade_level asc)


texts: "Alter Table" modify multiple columns vs multiple Alter table single column in INNODB?

texts:
I am updating collations of my database tables and columns from utf8bin to utf8mb4_bin.
Engine is INNODB with file per table enabled.
I used a query to generate identify all utf8 columns from information_schema database and generated corresponding queries for each.
The generated queries contains "ALTER TABLE" queries for each column separately.
Example:
ALTER TABLE mydb.`cwd_group`
MODIFY `active` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
ALTER TABLE mydb.`cwd_group`
MODIFY `local` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

Is it a recommened to join these two as a single statement? What will be the drawbacks?
ALTER TABLE mydb.`cwd_group`
    MODIFY `active` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
   ,MODIFY `local` char CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
;

I am guessing by joining them as single statement, disk IO is reduced as it not required to write the whole table data multiple times (i.e., to temporary ibd file #sql-*) or does it write only the column data to temporary file? Or how much extra space may it use for the temporary file if I merge the statements?
In the end, I want to run these alter tables in parallel that my server can handle.


texts: Overhead of triggers and foreign key constraints?

texts:
I have a sqlite database which is accessed by two programs.  There are a number of invariants/constraints which should always hold.  In addition to the usual foreign key constrains between tables, there is a table where once a row is inserted the row should never be deleted and several of its columns should never be changed.

The programs are written to maintain these conditions.
For example, there is no code which deletes from the table mentioned above.

I could also add triggers to the DB to enforce the required conditions by aborting statements which would violate them.  This seems like a reasonable idea to me, "belt and suspenders"-wise. Also it is some additional "living documentation" of the DB requirements.

I am assuming, but I can not find and concrete evidence, that these triggers would add minimal overhead as they should never be invoked (why would it even look at a BEFORE DELETE or BEFORE UPDATE trigger on a SELECT or INSERT?).

I am less sure about the likely overhead of foreign key constraints as these would seem to get checked on every INSERT/UPDATE/DELETE to their parent table.

Questions:
(1) Are the triggers/foreign key constraints a good idea even if I am very confident that my code will never attempt to violate them?
(2) Where can I get information on their overhead?


texts: Create stored procedure with dynamic number of columns + column names

texts:
I am relatively new to postgresql (I have version 12), and am having some trouble creating my first stored procedure. The real trouble is that I need it to be dynamic in some way, and thus I am having trouble finding previous threads that have asked this question.

Say I have a table with the following information:

Table nominal_dollars:

year       GDP        Dividends
2000      100              20
2001      110              30
2002      120              40

In the above table, the numbers are not adjusted for inflation. A common procedure I want to do is create a new table, in which the numbers are adjusted for inflation. This will mean I need to join the above table with a new table, the consumer price inflation (CPI), which has the following form

Table cpi:

year       cpi_adjustment
2000      1
2001      2
2002      3

This will allow the creation of a new table, in which all of the numbers are adjusted for inflation, i.e. the original numbers are multiplied by the cpi adjustment:

Table nominal_dollars_adj:

year       GDP        Dividends
-----------------------------------------
2000      100              20
2001      220              60
2002      360              120

Where GDP  for the new table equals GDP from the original table * cpi_adjustment, and the same for dividends.

Now, I want to do this CPI adjustment procedure for many different tables --- which may have different numbers of columns.

So I want to create a stored procedure that takes as an input a table (or table name), and inside this function, I want to create a new table with the same exact names and number of columns as the original table, but with all of the values adjusted for inflation as above.

I can select all of the columns names I need to create the new table from the command

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'nominal_dollars'
ORDER BY ordinal_position;

I can then loop through these column names using the following loop

   FOR temprow IN
        SELECT column_name FROM information_schema.columns WHERE table_name = 'test' ORDER BY ordinal_position;
    LOOP
        execute 'UPDATE test SET temprow = temprow / 2'
    END LOOP;

But I'm having trouble putting it together in a stored procedure that will actually work.

I have the following snippet of code which should work for MySQL but does not work for Postgres. Any advice on how to get it to work?

CREATE PROCEDURE [dbo].[Results_Inflation_Adjusted]
@TableName    VARCHAR(50)
AS
BEGIN
DECLARE @sql VARCHAR(5000);

SET @sql = 'SELECT ';

SELECT @sql +=  CASE ColName WHEN 'ID' THEN  @TableName + '.' + ColName WHEN 'Year' THEN  @TableName + '.' + ColName  ELSE @TableName + '.' + ColName + ' * cpi_adjustment' END + ' As ' + ColName + ','

FROM
(SELECT COLUMN_NAME AS Colname
    FROM INFORMATION_SCHEMA. COLUMNS
    WHERE TABLE_NAME = @TableName
    --ORDER BY ORDINAL_POSITION
) temp

SET @sql = LEFT(@sql,DATALENGTH(@sql)-1)  --remove last comma
SET  @sql = @sql + ' From ' + @TableName + ' inner join CPI on ' + @TableName + '.year = ' + ' CPI.year' ;
EXECUTE(@sql)
END


texts: Where to download (Dutch) Postgresql Fulltext Search dictionaries

texts:
PostgreSQL enables me to configure what dictionaries I would like to use when preforming a fulltext search. The following types of dictionaries can be set:

stop
ispell
simple
snowball
synonym
thesaurus

It comes with some .stop dictionaries and example files included, if you install the .deb file through PostgreSQL's servers. Yet nothing to fancy. Are there any locations that one can get good filed dictionaries in any of these categories? I am searching for Dutch dictionaries in particular.

So far I found ispell dictionaries for many languages:
http://cgit.freedesktop.org/libreoffice/dictionaries/tree/
http://fmg-www.cs.ucla.edu/geoff/ispell-dictionaries.html

Any links to good sources?

Update

So far I managed to find the following:

PostgreSQL ships with Snowball and Stop dictionaries for a lot of languages (for sure Dutch, English and German), I have no clue how up-to-date they are.

To get good Ispell/Hunspell dictionaries I would refer you to the following post: "Wrong affix file formate for flag" while loading dictionaries from LibreOffice in PostgreSQL 9.3. In short quite okay once ship with Linux (Debian at least) check out the link on how to set it up.

For Thesaurus and Synonyms I am still hoping for input and my search continues. If I find something be for I hear something I will post it here.


texts: Is reconfiguration required when switching sql server machine to use a FQDN?

texts:
I'm going to be changing the Primary DNS Suffix (not the NETBIOS name) of a Windows 2008R2 Server that does not belong to a windows domain (eg. a WORKGROUP computer) in order to give it a FQDN.

Is it necessary to reconfigure SQL Server in any way after doing so? I'm thinking specifically of the steps that are required when changing the NETBIOS name of the computer located here: http://technet.microsoft.com/en-us/library/ms143799.aspx


texts: Any gotchas with changing IP address of Postgres Master?

texts:
We have to replace our Master Postgres database and due to restrictions in AWS, we cannot use the same IP address for the new server (not 100% true, but cannot do this until the original is terminated and for the sake of safety I'd rather not terminate it before the new one is bedded in).

Aside from the IP changes required in the pg_hba.conf, are there any other points I need to be mindful of?

I'm currently going through the slave and checking for instances of the primary's IP address and it's showing up in some files in $POSTGRES_DATA/main/base/ which is why I'm asking.

Postgres 9.1 on Ubuntu 12.04

Thanks


texts:
I have table blobtable which has a BLOB column blobcol.
A user updated the table using a command similar to this:

UPDATE BLOBTABLE
SET BLOBCOL=BFILENAME('directory/subdirectory', 'filename.txt')
WHERE ROWID='ROWIDVALUE';

Note that the file filename.txt existed in the directory before this update was performed.

Now, the user is saying that when they select from blobtable (using PL/SQL developer), blobcol contains "Value Error" for this row.  Normally, blobcol contains the value "BFILE".  Out of curiosity, I tried selecting the same record in SQL*Plus and I get the following value for blobcol:

bfilename(directory/subdirectory', 'filename.txt')

I'm not very familiar with BLOBs and how they work, so my questions are:

Did the user update the blobcol properly?
What could cause the "Value Error" and how can this be corrected?


texts: Is there any benefit for natively compiled TVFs when used in non native modules?

texts:
I have some schema-bound inline table valued functions that I could natively compile.

However, my workflow does not use store procedures and it would only be invoked from entity framework or ado.net directly.

As the functions must be inlined into our queries, I fail to see a point in marking my functions as natively compiled. However, a colleague of mine wants to use it because it's shiny and new.

From some brief testing there seemed to be no actual benefit. Am I mistaken or could there beneficial scenarios?