texts: How to select recursively in a child parent design situation (in MySQL)?

texts:
Let's take into consideration the following tables:

CREATE TABLE actions
(
    id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    user_id BIGINT(20) unsigned NOT NULL
);

CREATE TABLE recurring_actions
(
    original_action_id BIGINT(20) unsigned NOT NULL,
    recurring_action_id BIGINT(20) unsigned NOT NULL
);

The data in each table just for an example is as follows:

actions

id name user_id

1 fdfdk 3
2 43434 3
3 43334 5
4 sdkk  6
5 zz    7
6 ll    3

recurring_actions

original_action_id recurring_action_id
1                  2
4                  6
2                  3
3                  5

How can someone query and fetch all the chain of recurring action ids that lead to the last child with id 5 ?

Expected result should be [1, 2, 3, 5] (including 5 is ok)

I can solve this so far only by recursive querying by application code. Get the original action then if found, query again and so on. Recursive consecutive queries initiated by PHP/C# or whatever code used.

I want to do this instead in one recursive (or other solution) MySQL query

The answer should focus only on a query solution (if possible) and not in organizing the database in another way. I am aware of other possible database designs which are more suitable for child parent relationships (such as closure tables, nested sets etc).


texts:
I was wondering about specifying id's in CouchDB. For example:

House
- Rooms
-- Kitchen
-- Living room
-- ...
- Windows
- Doors

There are some documents, like "House", where there is and will only be one document of this type. Should I specify the id "house" for this document for quick access or rather use a generated id?


texts: Performance inprovement UDB DB2 v10.5.0.5``

texts:
We are using db2 UDB for our operational data store that is accessed via JAVA service to display information to front end [24*7]

we are facing a lot of performance and deadlock issues.

1)deadlock is something that i can understand,our ETL jobs are such that it reads the data from the source table and writes to the target and update the reads rows as completed within informatica mappings.As per my knowledge DB2 locks the only at ow level but we had instance where row level raises to table level lock.I am not able to understand why it turns into table level lock and eventually falls into deadlock there by failing the process.Do i need to decrease the commit level to 1000 in order to reduce the no of locks.

2) Also we found that the table overflow value is high and PCTFREE parameter was set to -1[means defaulted to zero].what is optimal value it PCTFREE can be set
[planning to re org but it is not possible

3) Is there any other list of things/parameters that can be set in-order to improve the performance ?

WE are looking at the application level SQL optimization but need a help with Admin eye View

Please advice


texts: Single Column index or 3 column index in MS Access

texts:
I am creating a table with a single memo field and either 3 text fields (tablename, fieldname, caseID) or a single text field with the 3 text strings concatenated.

There will be a single NO DUPLICATES index on either the 3 fields or the single concatenated field.

My question is this: from a performance point of view, is it faster to have a single text field with a single column index, or a 3 column index covering the 3 text fields? My guess is the single option, but I don't know how much difference it makes? There will be approx 20 possible table names, 100 possible field names, and a few thousand caseIDs.

I only need to search on a combination of the 3 text fields, never on any of the fields individually.

Thanks for any advice!
Jim


texts: can't change autoincremet variable in mariadb

texts:
I have problem with auto_increment in mariadb. I have 4 galera nodes. but my auto_increment_increment variable is 4.. Can I change dynamic? Is there any problem in big systems when I have billion or more items in table..

MariaDB [kohadata]> show variables like '%auto%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| auto_increment_increment     | 4     |
| auto_increment_offset        | 2     |
| autocommit                   | ON    |
| automatic_sp_privileges      | ON    |
| innodb_autoextend_increment  | 8     |
| innodb_autoinc_lock_mode     | 2     |
| innodb_stats_auto_update     | 1     |
| sql_auto_is_null             | OFF   |
| wsrep_auto_increment_control | ON    |
| wsrep_retry_autocommit       | 1     |
+------------------------------+-------+


texts: Why does the size of fields in a collection in mongodb database affect query performance?

texts:
I had a collection that was growing and with it's growth had a crazy increase in query response times.
The collection records looked something like this:
  {
    orderStat: { type: String },
    items: { type: Array },
    docBigValue: { type: String },
    detailsBigValue: { type: Object }
  }

I did everything I could think of like ensure I was indexing on queried fields, use .lean(), excluded the fields (docBigValue, detailsBigValue) with huge values from queries, limit the number of records returned per query, stopped allowing fuzzy search using like. Everything i did made an improvement but the rate of new records quickly made the value behind each improvement lost.
Today I looked at the collections avgObjSize and found it was over 73,000 while most collections had a value under 500. I took a backup and then deleted one of the large fields from all the records. My query response time went from over 6 seconds to under half a second.
Why does the size of a field in a collection affect query response times even when that field is excluded from the query? Is this a bug or normal?
Mongodb 4.x
In my case a simple db.someCollection.find({}, {docBigValue: 0, detailsBigValue: 0 }) could be used to reproduce the issue. I just can't find anything in the documentation that would suggest the size of a field would affect query performance even when it's excluded.


texts: Query Postgres JSON array for values in text

texts:
How would I query a JSON array column such as this for text within each of the array values and only return those rows that have them?

["Mr Smith","Ms Wellington","Mr Anderson"]

I've tried these but neither return results when I know they exist:

select * from people where names::jsonb ? 'Mr';
select * from people where names::jsonb @> 'Mr';


texts: What is the difference between these two create table statements?

texts:
a)

SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE);

Table created.

SQL> desc xml_tab1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PODOC                                              PUBLIC.XMLTYPE

b)

SQL> CREATE TABLE xml_tab2 of XMLType;

Table created.

SQL> desc xml_tab2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE of PUBLIC.XMLTYPE

Code from here.

In the first one, we are creating a column of type XMLTYPE. What does the second one do? When should I use the first or the second one?


texts: Executing stored procedures asynchronously using tasks in SSIS

texts:
I have a stored procedure which is calling 20-30 stored procedures. I need these stored procedures to run asynchronously. From what I have read, it seems like executing those stored procedures using SQL tasks in SSIS is the appropriate way to do it.

I have installed required software but I am completely new to it.Can I please get some directions on how to do it? i.e run stored procedures asynchronously using SQL tasks in SSIS,please? Since I am completely new to it,please try to elaborate as much as possible as I have no prior knowledge of  this. It will be a great help.

Thanks


texts: should I change mysql platform for higher innodb_buffer_pool_size?

texts:
Currently Im running a 160G MySQL database on Xampp (Windows). But Xampp has upper limit for innodb buffer pool size of 4G. My computer has 16G RAM. So if I want to make full use of resources, should I move database from Xampp to Workbench? Then I could set maybe 10G buffer.

So, here is my plan:

use mysqldump to create a backup file.
uninstall Xampp totally (otherwise MySQL will install in Xampp directory again, I tried before)
install new MySQL and workbench.
recover the dump file into new platform
set innodb buffer pool size to >10G.

Is this practical?


texts: Processing data while keeping frozen frontend database online

texts:
What is the best approach at having a "frontend" database online and working, while processing and generating new data in the back?

I have an scenario that some tables and even whole databases data needs to be manipulated and regenerated. After this processing is done, the new data is promoted to production (frontend). Once the processing is done, all the new data have to be available at once. So it can't be processed online.

I've come up with two different solutions, I'd like feedback on them since I'm not totally happy with either of them:

Work with snapshots while this processing is being performed. Once the processing is finished, re-create the snapshot with the current data and leave the real database ready to process again. This would have the downside of higher IO activity and fragmentation.
Work with an additional backend server and schema of backup-restore from one server to the other and then back. This would have the downside of network traffic, storage requirements, times and is more complex and prone to have issues. It would also require an additional server (thus, costs).

Environment: SQL Server 2014 Enterprise.

Additionally, I've been looking into Snapshot replication. But I'm not familiar with it. Given the data is not updated often (maybe once or twice a month) it looks like a potential solution as well.


texts: Storing a lot of text / string data strategies - analysis and solution

texts:
I have a LOB application backed by SQL Server 2016.

Over the past few weeks, theres been issues whereby the SQL Server start throwing timeout errors - which has led to a bit more digging into the database, running some fixes etc.

As part of this, I've realised that ~85% of the 10GB database is messages. The system will process and store email communications back and forth and the HTML body of emails is being stored. The attachments are being split off and saved as S3 blobs so isn't an issue.

Given there's ~1 million records, ~in 6 months taking up ~8.5GB the rate of growth is a concern, from backups, to additional overheads in the coming years. I may be able to get a business case to delete old messages but this would be political rather the technical.

I've taken a copy and tested compression on the table, but it's had minimal impact... The next option, is to have the application compress and decompress the string as required but that will require quite a bit of scoping and testing.

Is there a best practise method to manage this? Put them (the messages) in a separate file group? Or a separate database altogether!? What is the best means for the storage of large strings of text?


texts:
I want to know the view or query which provides the total number of active and non-active session for a specific snapshot. Currently I am using the below query.

SELECT INSTANCE_NUMBER, count(*)*10 "DB Sessions"
FROM DBA_HIST_ACTIVE_SESS_HISTORY
where snap_id = '78131'
GROUP BY INSTANCE_NUMBER;

Any help will be appreciated.


texts: APEX Installation ORA-01435 while running @apxconf

texts:
I'm trying to configure and use APEX on a Oracle Database 12c. When I try to run @apxconf I get the Error ORA-01435. I don't really now what is wrong since I followed the Documentation. https://docs.oracle.com/database/121/HTMIG/E17958-12.pdf#G5.1084317

SQL> @apxconf

PORT
8080

Enter values below for the XDB HTTP listener port and the password for the Appli
cation Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.

ERROR:
ORA-01435: Benutzer ist nicht vorhanden.

Verbindung zu Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production beendet

Verbindung zu Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production beendet => Connection closed

Working Directory is dbhome_1/apex.

I'm logged in with sys as sysdba.


texts:
I'm trying to change the service name of an Oracle 11.2.0.3 installation on a Windows 2003 server.

During installation the service name was defined with the default domain, but we'd like to get rid of that.

What I have done so far (and what has worked before) to change the service name mydb.foo.bar to mydb only:

alter system set service_names = 'mydb' scope = both;
alter database rename global_name to mydb;

Which seems to have worked:

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      mydb
db_unique_name                       string      mydb
global_names                         boolean     FALSE
instance_name                        string      mydb
service_names                        string      mydb
SQL>

(I removed some properties from the above output which are not relevant)

Then use alter system register to re-register with the listener.

This showed no effect so I restarted the database and the listener, still no luck.

The current situation is as follows:

select name from v$active_services returns:

SERVICE_ID | NAME            | NETWORK_NAME
-----------+-----------------+--------------------
1          | SYS$BACKGROUND  |
2          | SYS$USERS       |
3          | mydb            | mydb
5          | mydbXDB         | mydbXDB
6          | mydb.foo.bar    | mydb.foo.bar

So for some reason the old service name is still there and running.

When trying to stop the service using

SQL> exec dbms_service.stop_service('mydb.foo.bar');
PL/SQL procedure successfully completed.

no error is reported, but when trying to delete the service, Oracle won't let me:

SQL> exec dbms_service.delete_service('mydb.foo.bar');
BEGIN dbms_service.delete_service('mydb.foo.bar'); END;

*
ERROR at line 1:
ORA-44305: service mydb.foo.bar is running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 454
ORA-06512: at "SYS.DBMS_SERVICE", line 343
ORA-06512: at line 1

I verified that I have no open connections using that service:

select count(*)
FROM v$session
where service_name = 'mydb.foo.bar';

returned 0 (zero)

I also re-recreated the Windows service using oradim, but to no avail.

At one point I ran

alter system set service_names = 'mydb,mydb.foo.bar' scope = both;

could that be the reason I have both now?

But after changing service_names to only a single name, the second one should have gone away, shouldn't it?

If I can't make the second service go away, it would be enough to make the instance register with the listener using the short service name, rather than the long one.

I'm sure I'm missing something pretty obvious, but I can't figure out what it is.


texts: postgresql: live tuples not matching table row count

texts:
We're experiencing poor performance with a table. The table is regularly updated with insert/updates, and is a critical table in an application.

In looking into the problem I've noticed that the row count of the table returned by

select count(1) from tablename;

returns ~8 million rows, this is expected and matches the source.

However looking at pg_class and pg_stat_user_tables shows a live tuple count of ~30 million (and a dead tuple count of ~26 million).

Shouldn't live tuples match the table row count? Could this discrepancy be causing my performance issues when querying the table?


texts:
How would i be able to find out which queries would gain by adding a new index.

Ex: Lets assume i add a new NCI on a table , how would i be able to find out which queries would benefit from adding this NCI( or any Index ) for that matter of fact?


texts: Can I force mysql server to resolve the external ip to localhost?

texts:
I'm finally succeeded with ssh tunneling. My point is to open the mysql server to local users only (user@localhost etc.) while providing a remote control to my customers through ssh tunneling.

The problem is when I connect through the mysql command line tool (mysql.exe -u root -h 127.0.0.1 --port=8600) I've got a permission denied error for user root@my.servers.ip.address (the tunnel redirects from 127.0.0.1:8600 to my 3603 remote server)

Well it makes sense since my root exists in mysql.user only with the localhost host.

But then, is there a way for me to somehow tell mysql that this ip address is actually the server's address and that the root should be allowed to connect?

I've got no clue where to start. To me if such a thing existed it would be some kind of ip resolving table...


texts: PostgreSQL Replication for small, low-usage database

texts: I have PostgreSQL 8.4 installed on a headless Ubuntu 10.04 server. Sometimes the server is not available. I would like to continue working on the database, so I thought of installing PostgreSQL onto my laptop. I'm familiar with PostgreSQL, but not with replication. Would I setup replication so that the two databases are kept synchronized when the server becomes available again? This does not need to be automated. What is the easiest and simplest approach? The database is not used extensively by others at this stage and is small, so a simple, lightweight solution would be adequate.


texts: Identity Columns or UDF that explicitly generates a unique id?

texts:
I am in middle of a debate about whether it is better to make a PRIMARY KEY out of an Identity Columns, our out of a UDF that explicitly generates a unique id.

I am arguing for the Identity Column.
My partner is arguing for generating the values manually, he claims

by putting the UDF on another table where we can have a UDF

lock the resource
increment an ID table with one field called ID_Value by 1
use this as a global unique identifier

Or have the table do an id+1 when inserting
That it's simpler to move data between servers and/or environments not having the identify constraint; moving from one DB where there is data to another similar DB with lets say staging or dummy data. For testing in non production we may want to pull all records from yesterday down to staging for testing.

Which implementation makes more sense?


texts: How are SQL backups different from IT regular nightly server backups?

texts:
Our IT department backs up the entire server every night (an SQL Server instance is installed on this server), which should be backing up that server as well as the entire network, in case something goes wrong...

So my manager asked what is the significant of my Full, Differential and Log SQL backups versus whatever the IT department backs up? To save more space on our server rather than keeping these files for a couple of weeks and deleting them, she thinks IT will just provide them!

I know it's not right, as I can restore up to the last 30 minutes with my log backups, IT restores it the next day, but is this the only difference?

Since I save/send my database backup files to the same server, IT will restore them but if I don't have these backup jobs in my maintenance plan then IT can just restore the SQL instance without any of our tables, transactions... etc.
Am I getting this right?
Any advice would be really appreciated.


texts: GROUP_CONCAT with ORDER BY , but results are not ordered

texts:
The following query works fine

SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5

but the images column are not ordered as priority. I am unable to understand why it is happening

Structure for table project

CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catID` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`description` varchar(400) NOT NULL,
`url` varchar(255) DEFAULT NULL,
`tags` varchar(255) DEFAULT NULL,
`featured` varchar(3) NOT NULL DEFAULT 'No',
`featured_url` varchar(255) DEFAULT NULL,
`order` int(11) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `catID` (`catID`),
CONSTRAINT `FK_catID` FOREIGN KEY (`catID`) REFERENCES `category` (`catID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;

Structure for table project_photos

CREATE TABLE `project_photos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`url` varchar(250) DEFAULT NULL,
`project_id` int(11) DEFAULT NULL,
`priority` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=utf8;


texts:
With my current setup, it's going to be very hard, if not impossible, to get my terminal window to scroll up and down. That means I can only see 25 rows of text at once. If I have to display something longer, I use |more to be able to scroll through it, like this:

ifconfig | more

But what can I do when a MySQL statement returns more than 25 lines?

describe mysql.user;

I can't just add "| more" in MySQL, and "limit 5" would be invalid syntax here. How can I view the rows a few at a time?


texts: SQL Server Express 2014 Installation directory

texts:
I face recently a problem when starting SQL Server Management Studio. As it can't start, I uninstall it. When trying to install it again, I am asked to select the installation folder of SQL Server.

Unfortunately, nor the MSSQL folder, nor its Binn child solved my problem. Can somebody help get the right folder ?

Edit :
I've succeeded installing SSMS again, but I still can not run it. It is showing this message :

Finally, I cannot guess the source of the problem.


texts:
phpmyadmin no longer displays the export templates section after an update

Do you know how to display this section?

Current phpmyadmin version
4.9.5deb2


texts: Making similar points close in distance O(n) query by distance

texts:
I'm trying to create a database in DynamoDB that has many points with high dimensions. For the most part, I have designed the database to the regular access patterns. However, for a search endpoint, I quickly realized that my points need to be close by similarity.
Initially I thought it would be ok for me to use an index that represented a vector. For instance if there is a cat and no dog and a frog in picture 1 the vector would be 101
But in order to get a list of pictures with cats I would have to query for 1XX, where X is a wildcard. In this case it seems fine as there are only 3 dimensions so it would be O(1)
but with a high dimension it quickly becomes a limiting factor as we would have to query m number of dimensions O(m). I get that each of these points are on a different partition so each m dimensions can be queried at the same time. However, this solution is still not acceptable given the future usage.
Is there a way to define the distance function when using the sort index in DynamoDB or a more clever way of defining the feature vector in such a way that the points would be sorted by distance?


texts: MySQL Server 8.0.18 has failed Windows 10

texts:
I am new to MySQL Server. I am trying to install MySQL Server 8.0.18 on Windows 10 laptop.

I am new to MySQL, so I have not tried anything other than to install.

This is the log file:

Beginning configuration step: Backing up MySQL database

Starting MySQL Server in order to run the mysql_upgrade tool.
Warning: There may be some errors thrown by MySQL Server, the mysql_upgrade tool is going to be run next to attempt to fix database incompatibilities.
Starting process for MySQL Server 8.0.18...
Starting process with command: C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe --port=3306 --datadir="C:\ProgramData\MySQL\MySQL Server 8.0\data" --console...
Process for mysqld, with ID 17596, has been started successfully and is running.
Successfully started process for MySQL Server 8.0.18.
2019-10-19T19:36:28.801560Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.18) starting as process 10152
2019-10-19T19:36:28.817090Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2019-10-19T19:36:28.817406Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-10-19T19:36:28.817691Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-10-19T19:36:28.818371Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.........................
Running mysqldump tool to backup the database...
Backup files will be dumped to "C:\ProgramData\MySQL\MySQL Server 8.0\Backup\mysql_dump-2019-10-19T12.39.26.sql".

Starting process with command: C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe --user=root --default-auth=caching_sha2_password --host=localhost --port=3306 --default-character-set=utf8 --routines --events --single-transaction=TRUE --all-databases --result-file="C:\ProgramData\MySQL\MySQL Server 8.0\Backup\mysql_dump-2019-10-19T12.39.26.sql"...
mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect
Process for mysqldump, with ID 6468, was run successfully and exited with code 2.
Ended configuration step: Backing up MySQL database

I expect MySQL Server 8.0.18 should be installed. The error message is:

The configuration step (Backing up MySQL database) is taking longer than expected. The configuration for MySQL Server 8.0.18 has failed.

What do I need to do to get a successful install?

Thanks
-w


texts: MySQL Enums have non-intuitive behaviour, why?

texts:
This very helpful article explains some oddities about how MySQL Enums behave: http://melp.nl/2009/05/mysql-enums-and-booleans/

There is no discussiong about why that behaviour exists. Furthermore, in googling the subject I find only contradictory information and speculation, by 'bloggers' and other blind leading the blind.

Can someone please describe why MySQL Enums suffer from the behaviour outlined in the post. Here is one example of Enum oddities presented in the post, but there are many more:

mysql> insert into t values('1'),('0');
mysql> select b,b=1,b=0 from t;
+---+-----+-----+
| b | b=1 | b=0 |
+---+-----+-----+
| 1 |   0 |   0 |
| 0 |   1 |   0 |
+---+-----+-----+
2 rows in set (0.00 sec)


texts: How to delete global variables set in PostgreSQL

texts:
I had Set a global variable using SET bc.xyz=value
but now I am not able to delete it.

Please help me in it.


texts: Help with SQL Server Error "ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'"

texts:
So we have an interesting issue where users are receiving the following error when attempting to query a view in SQL Server 2008 R2:

  Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider
  'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this
  provider through a linked server.

Researching the issue, I've found more than a few articles listing making changes to the OPENROWSET parameters, registry value changes, AdHoc DIstributed Queries, etc. (Links to articles below)

http://www.johnsoer.com/blog/?p=538

http://www.remotedbaexperts.com/Blog/dba_tips/2010/09/ad-hoc-access-to-ole-db-provider-microsoft-ace-oledb-12-0-has-been-denied/

We're still receiving the error after following the steps in the links above.

The caveat here is that when we created a test account and gave it sa - it ran just fine. My colleagues with admin access are also able to run the query without any issues.

This query is being fed from an excel file local to the server hosting this instance.

Anyone know of any other fixes for 2008 ?


texts: join syntax / style performance consideration

texts:
We recently found on one of our stored procedures that we get significant performance improvement by changing the join syntax/style of our query from this...

SELECT b.bla, c.foo, d.bar
FROM dbo.TableB b
JOIN dbo.TableC c
    JOIN dbo.TableD d  -- <-- Nested join syntax
    ON d.yyy = c.yyy
ON c.xxx = b.xxx

To this...

SELECT b.bla, c.foo, d.bar
FROM dbo.TableB b
JOIN dbo.TableC c
ON c.xxx = b.xxx
JOIN dbo.TableD d   -- <-- Regular way
ON d.yyy = c.yyy

Note: in the real query, there were 10 joined tables including inner and outer joins. The tables were not huge in terms of sql data. No aggregates. There was a DISTINCT on the output. All joins were to a primary key, but the foreign key was not necessarily indexed.

We will certainly change our ways, but I'm still curious as to the proper 'guidance' on such style. I've often used the 'indented' style to indicate a 'more readable' join for such things as lookup tables.


texts: Cumulative Update 3 & 4 Caused Synchronous Commit Failures in Availability Groups

texts:
So this week I was patching our SQL Servers that host our availability groups. Never had an issue with them in the past, fairly standard process. I have two servers (call them SQL1 and SQL2) in the cluster and one Availability Group (AG1) uses SQL1 as the primary, and three Availability Groups (AG2-4) uses SQL2 as its primary. They were running v15.0.4013.40, so Server 2019 and this week I had to install CU3 and CU4.

My process was:

I moved AG1 to use SQL2 as the primary
Proceed to patch SQL1
Switch all availability groups to async commit
Reboot SQL1
Return all availability groups to sync commit
Return AG1 to use
SQL1 as the primary

In step 2 I patched both CU3 and then CU4 successively. Then, when trying to switch all AGs to sync commit, the databases threw errors in the Availability Group Dashboard and they wouldn't allows access to their databases. I couldn't see any logged reason to why it refused to do this. It was only resolved when I then patched SQL2 with both CUs, and it again allowed to sync commit.

My question is did anyone else have this issue? Was this likely due to me trying to be efficient and patch two CUs in the same interval without doing it one by one? Just trying to work out how I don't do this again as it caused me quite the headache


texts: Backup set expire option - Backup SQL Server 2012

texts:
I had seen an option while taking a backup i.e. set backup expire date, I have a doubt. What does this mean:

Backup will be no longer valid after date DD/MM/YYYY?
Backup declared with option, is it valid file to restore?
Can we store this backups for 1 year after expire date option is given and restore?
can Expired Backups(not deleted/purged) restored, any cons or limitations?

Any links to this concept also appreciated, which helps many junior DBA's.

Thanks to all.


texts: Copying distinct rows from one table to another

texts:
In SQL Server, I can copy one table into another by doing:

Select * into desttable from sourcetable

However, there are duplicate rows in sourcetable and I would like to just copy distinct rows. How do I do this?

Thanks


texts: Reconnect mirroring server SQL Server 2008 R2

texts:
The failover server has status (Mirror, Disconnected, In recovery) and principal server has status (Principal, Disconnected).

What is the process to reconnect these servers?


texts: Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

texts:
E.g. consider the SQL query:

SELECT
   A.[Name],
   ROW_NUMBER() OVER(ORDER BY A.[Name] ASC)
FROM
   [FooTable] AS A

Here I observe the results being returned sorted by A.[Name]. If I change the sort column defined in the ROW_NUMBER function to another column then again the results become sorted by that column.

I was expecting the row number to be assigned to the rows but I was not expecting the rows to come back sorted by that same criteria. Is this simply a side effect of how the query is being executed (in my case on SQL Server 2008 R2) or is this behaviour guaranteed? (I could find no reference to such a guarantee).


texts: ENABLE/DISABLE KEYS lag: how to optimize?

texts:
I've got 32 GB of RAM and a mysql table with varying size of rows: 100m - 500m rows (each row is around 30 bytes). Every hour a PHP script DISABLEs KEYS of the table, LOADs a couple of millions of new rows from a CSV file, and ENABLEs KEYS again. This works well (done within 2 mins) as long as the table has less than 200m rows. Once it grows further, extreme lagging is experienced: the disable-enable keys procedure suddenly lasts longer than an hour.

The table is trimmed frequently back to 100m rows (let's say once every day).

For all I care, I am ready to devote 90% of all resources of my server for this script only (only during the execution of the script, naturally).

QUESTIONS

Why this lagging?
What system variables could I define in my PHP script to try to overcome the lagging?


texts: Best way to perform SQL Server replication over multiple servers?

texts:
My current setup requires me to use three separate SQL Server 2012 instances (installed on 3 separate machines, separated geographically) and have the data be replicated across all 3. Any changes made in the 7 tables within my database need to be replicated based on where the change was made, for example, if I add a record on Server A, it needs to be added on Server B and Server C. If a record is added on Server B, it needs to be added on Server A and Server C. Same thing with Server C.

From what I've read, Bidirectional Transactional Replication seems to be the best avenue for me to use in SQL Server 2012 Standard. The issue that I'm having is that no one has a very good guide to Bidirectional Transactional Replication that really explains much. I think I've poured over Hilary Cotter and Microsoft's information a million times and I still can't seem to get it to work. The fluff data that they provide in examples just isn't practical and no one explains how to do it between two physical machines; they assume you're using the same machine and just replicating between another instance.

Am I doing something wrong or am I going about this the wrong way?


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

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

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

The value for 40 is

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

The value for 100 is

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

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


texts:
Charindex returns no record if only one letter is provided.
If parameter matches the whole word it shows the record.
Why???

USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[filter_opombe]
@opombe_par nchar(256)
AS
BEGIN
    SELECT Id_opombe
    FROM opombe
    WHERE (CHARINDEX(@opomba_par, opomba,0) > 0)
END

Thanks for all your replies in advance.


texts: Why is an unindexed range operator (<@) faster than using BETWEEN with an index?

texts:
NB This is the same setup as this question, where here I'm asking specifically about something I was specifically not asking about over there.
I've got a table with a column utc timestamptz, with a "btree" index on the utc column:
CREATE TABLE foo(utc timestamptz)

CREATE INDEX ix_foo_utc ON foo (utc);

This table contains about 500 million rows of data.
When I filter utc using BETWEEN, the query planner uses the index as expected:
> EXPLAIN ANALYZE
SELECT
   utc
FROM foo
WHERE
    utc BETWEEN '2020-12-01' AND '2031-02-15'
;

Bitmap Heap Scan on foo  (cost=3048368.34..11836322.22 rows=143671392 width=8) (actual time=12447.905..165576.664 rows=150225530 loops=1)
  Recheck Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
  Rows Removed by Index Recheck: 543231
  Heap Blocks: exact=43537 lossy=1818365
  ->  Bitmap Index Scan on ix_foo_utc  (cost=0.00..3012450.49 rows=143671392 width=0) (actual time=12436.236..12436.236 rows=150225530 loops=1)
     Index Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
Planning time: 0.127 ms
Execution time: 172335.517 ms

I could write the same query using a range operator without an index at all:
> EXPLAIN ANALYZE
SELECT
   utc
FROM quotation.half_hour_data
WHERE
    utc <@ tstzrange('2020-12-01', '2031-02-15')
;

Gather  (cost=1000.00..9552135.30 rows=2556133 width=8) (actual time=0.179..145303.094 rows=150225530 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on foo  (cost=0.00..9295522.00 rows=1065055 width=8) (actual time=5.321..117837.452 rows=50075177 loops=3)
      Filter: (utc <@ '["2020-12-01 00:00:00+00","2031-02-15 00:00:00+00")'::tstzrange)
      Rows Removed by Filter: 120333718
Planning time: 0.069 ms
Execution time: 153384.494 ms

These are doing the same operation (albeit that <@ is right-hand exlusive and BETWEEN is inclusive.)
How can the unindexed query with <@ be faster than the indexed query with BETWEEN?
Surely if ignore an index is faster, the query planner should know that in advance?
Or is this something specific to do with the amount of memory my PG instance has, and the size of the query (big!)

My Postgres version:
"PostgreSQL 10.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit"


texts:
I've created a stored procedure that creates a database, creates a set of tables, copies all of the data from a linked access database into those tables then creates a user and assigns that user to the db_reader, and db_writer roles. When I run this as sysadmin everything works correctly.  When I try to run it as a SQL Server user I have setup it returns an error saying that user already exists.  A look at security on that database does not show the user.

When I check the sid for the server_principles the sid for the user I'm trying set is the same sid I find used for dbo on the database_principles table.

I'm at a loss for why it would set up the user differently depending on the user that is running the stored procedure. Is there anyway around this? I've attemtped to run the sp_changedatabaseowner 'sa' but the user I'm running the stored procedure as doesn't seam to have rights to that even though I've granted execute on it for that user.

Any ideas how to get around this?


texts: A botched update that failed halfway is causing issues, I was wondering how I could roll back an update for SQL Server? Secondly, I was wondering if there is any other place (aside from)  **Setup Bootstrap\Log**  where  I could see which updates were installing and which one failed. I use SQL Server 2012


texts: TNS-12500: TNS:listener failed to start a dedicated server process Oracle9i Database on Windows 2003 64x server

texts:
My client wants to move his database from Windows 2000 server to Windows 2003 Enterprise Edition 64x server. I have created the Windows servers in VMware and installed the Oracle 9i databases on it. When I try to connect to the database Oracle 9i installed on Windows 2003 using sqlplus, using the System account on the same Windows Server 2003, it is giving following error in the listener.ora log file:

12-JUN-2015 23:30:55 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=HRPRD)(SERVER=dedicated)(CID=(PROGRAM=E:\APPS\ORAHOME\bin\sqlplusw.exe)(HOST=PEOPLETECH-2003)(USER=PSOFT))(SERVER=dedicated)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.59)(PORT=3348)) * establish * HRPRD * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
 TNS-12546: TNS:permission denied
  TNS-12560: TNS:protocol adapter error
   TNS-00516: Permission denied
    32-bit Windows Error: 5: Input/output error

TNSNAMES.ORA entry is:

INST1_HTTP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.59)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = http://HRService)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

HRPRD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.59)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HRPRD)
    )
  )

Listener.ora entry is:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.59)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:\APPS\ORAHOME)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = HRPRD)
      (ORACLE_HOME = E:\APPS\ORAHOME)
      (SID_NAME = HRPRD)
    )
  )

I have read many blogs on the forum, tried:

uninstalling and installing Database again,
tried changing the SID using oradim utility.
Added environment variables TNS_ADMIN,ORACLE_SID,ORACLE_HOME

I am not able to figure out the problem. Even if Windows 2003 support has ended or Oracle higher versions are available still as its my client's environment its his Requirement for which I need it to make it work on Windows 2003 EE 64x, no option.


texts: What does it mean when a merge replication generation has no changes?

texts:
According to MSDN,

  MSmerge_genhistory contains one row for each generation. A generation is a collection of changes that is delivered to a Publisher or Subscriber. Generations are closed each time the Merge Agent runs; subsequent changes in a database are added to one or more open generations.

Reading the above, it seems logical that all generations should have a number of changes. However, when I try to select rows from MSmerge_genhistory, I find a lot of them where the changecount column has a value of 0.

Do these rows indicate a problem? Why do generations exist if no changes are included in them?


texts:
On a production SQL Server 2012 database we are getting some odd locking statistics that I don't understand.

Lock Waits/Min: 0.2
Lock Timeouts/Min: 197.4
Deadlocks/Min: 0.0

@@lock_timeout is set to -1

How can I have Lock Timeouts without having Lock Waits? I recently added some missing indexes, could that be affecting these numbers?


texts: Which folder I use for recovery mysql database

texts: Accidently deleted mysql database and reload again.But ı cant find my old database and no have backup.This is picture that mysql ProgramData folder. Which folder should ı use for recovery? Thanks advice


texts: Numbering rows consecutively for a number of tables

texts:
I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to aid,bid,cid ascending and update column xid with incremented values starting with 0. What's the best way to achieve that?

My current solution involves:

selecting table T
openining a cursor over the ordered tuples of a table
assigning the autoincrement value to xid
inserting the tuple into a temporal table T_temp
deleting all records from T
inserting all records from T_temp into T

Since the tables have different schemas, I programmed half of the code in PL/pgSQL and half of the code with bash scripting.

Question 1: Any comments how to have it program that in pure PL/pgSQL?

Question 2: Any comments how to implement that more elegantly?


texts: Database snapshot "The requested operation could not be completed due to a file system limitation"

texts:
Preface

At the company I'm working for we use database snapshots in our ETL process to ensure the data is in a consistent state. The snapshot files are placed on a separate disk (in our case the X:).

One of the source databases is a 3TB database with data updated frequently. When creating a database snapshot of this database we've encountered moments when the snapshot files increased and we've got the error:

  "Operating system error 112(There is not enough space on the disk.)
  encountered."

That error was always in the index maintenance windows, or when a bulk process ran in the source database. Since then we've increased the available disk space on the X:\ drive and changed the schedule of index maintenance/dbcc checkdb jobs. As expected, the errors didn't occur anymore.

Problem

From time to time we need an initial load of some large tables (600.000.000 rows / 300GB per table). This week we've encountered a different error in the load of these large tables:

  The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000056fb976000 in file 'X:\Data\DatabaseName_snapshot.ss'.

  Additional messages in the SQL Server error log and system event log may provide more detail.

This error occurs only 18 - 25 hours after the snapshot is created.

Things already tried

Disabled all index maintenance/dbcc checkdb jobs and other processes changing a lot of data (except user input).
Monitored the available space on the snapshot disk (X:\ 400GB). About 80% of the available space is free when the error occurs.
Looked for simmilar questions on stackexchange. Found DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2) which was not answered.
Looking into article https://blogs.msdn.microsoft.com/psssql/2015/06/10/operating-system-error-665-file-system-limitation-not-just-for-dbcc-anymore/ right now.

Does anyone have more ideas to tackle this problem?

We are using SQL Server 2014 SP2 CU1, enterprise edition and Windows Server 2012.


texts:
We have a suite of restaurant web applications that stores data in SQL Server 2012 on database server. We have now started to move data for reporting onto a separate data warehouse server. The data needs to get into the data warehouse quite soon after being updated/created.

Knowing when to move transactions to the data warehouse (and into fact tables) is easy -- we do that periodically or when certain events occur (like the manager finalising something).

But how do I know when to move the definitions to the data warehouse (and into dimension tables)? We are storing definitions as SCD Type 1.

We’re considering...

On a schedule, checking all the definitions on the database server with dimensions and updating any changes. And assigning transactions to “Unknown” is the dimension hasn’t arrived yet.
Putting triggers on all the definition table and updating the data warehouse as the changes happen
Updating the stored procedures to update the data warehouse every time a change is made
Using something like Microsoft Sync Framework, and incorporating the transform in there.

The database and data warehouse servers are on separate servers – which we are planning to move to SQL Azure.

So, how do I know when to run the ETL for dimensions? And, any suggestions on technology would be helpful (we are currently using SQL Server linked servers).


texts: MULTIPLE INSERT VALUES with INSERT into another table

texts:
I have 2 tables, events and event_users:

Events

id      int
type    int
date    date
time    time

Event_users

event_id    int
user_id     int
user_role   int
...

Events can have many event_users (one-to-many relationship).

When I insert more than one event in the Events table, the query is:

INSERT INTO events
    (type,date,time)
VALUES
    (1,12-02-14,08:15:00),
    (1,13-02-14,08:15:00),
    (1,14-02-14,08:15:00),
    (1,15-02-14,08:15:00)

But at the time I know the events table info, I also know some of the event_users table info.

So, because I need the events.id to write the event_users record, how can I insert the events and the event_users records in one query? The database is MySQL.


texts: Can we reconstruct/reset a broken Postgresql data directory without losing data?

texts:
I'm trying to restore a database from a broken data directory that have lost many of its files accidentally.

I have tried unsuccessfully to install new instance of the same Postgresql version and then copy (with overwrite) the broken datadir to the new one.
here the remaining datadir that I'm working on :

   |-base
   |---1
   |---16384
   |---17950
   |---19621
   |---pgsql_tmp
   |-global
   |-pg_multixact
   |---members
   |---offsets
   |-pg_notify
   |-pg_stat_tmp
   |-pg_tblspc
   |-pg_xlog
   |---archive_status


texts: How DISCARD the tablespace on mariadb while table is not found?

texts:
I run the mysql_upgrade against my mariadb 10.3 and get the following error - see here for details.

ERROR 1813 (HY000) at line 85: Tablespace for table 'mysql.transaction_registry' exists. Please DISCARD the tablespace before IMPORT

When doing the DISCARD it does not work as the table does not exist

MariaDB [mysql]> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+

MariaDB [mysql]> ALTER TABLE transaction_registry DISCARD TABLESPACE;
ERROR 1146 (42S02): Table 'mysql.transaction_registry' doesn't exist

How to proceed in such a case?


texts: Service Packs and CUs for SQL Server, do these patches make any changes to user database files? or just instance level binaries?

texts:
I am curious about patching on SQL Server.  Does anyone know if SPs and CUs make any changes to user database files or is it just the database engine and instance level binaries that get modified/updated?

Thanks


texts: MySQL says incorrect parameter count for function I've created even tho it's right

texts:
I'm migrating my webapp from local to DigitalOcean, and I'm having a problem using a routine I've created. It works fine locally(version 5.5.50) but on the online MySQL(version 5.7.13) it gives me the following error:

  #1582 - Incorrect parameter count in the call to native function 'DISTANCE'

even tho the call is right.

I have deleted the function and it still gives me the same error, therefore I think it's not even being able to find my function.

I've tried to grant all privileges to my user and it's still not working.


texts:
When I attempt to run on the SQL Server 2000 to a query which was developed for SQL Server 2008, I receive the following the error.

  ambiguous column name SucNo

Here is my query:

 SELECT
   p.SiraNo, p.SucNo, p.Fail, p.SucAdi, p.SucTarihi, p.Musteki, p.Supheli,
   (select f.AdiSoyadi as Kayıp_Şahıs where f.SucYili = '2011') as Kayıp_Şahıs,
   p.EvrakEksigi, p.EvrakSorumlusu, p.SevkTarihi, p.evrakSevkDurumu,
   p.sucYili as Yıl
 FROM pStationTbl p
 LEFT JOIN kayipTbl f ON p.SucNo = f.SucNo
 WHERE p.SucYili = '2011'
 ORDER BY CASE evrakSevkDurumu
   When 'Beklemede' Then 1
   WHEN 'İkmal Edildi' Then 2
   Else 3
 End, SucNo DESC


texts: powershell problem when running on a remote server - access is denied

texts:
as a follow up from this question below:

Run a PowerShell script on a different server - from inside a SQL Server job

there you can see that for me tu run a powershell script from inside a sql server job, I need to create a proxy.

Both the powershell script and the proxy script are on that link.

what the powershell script does is to delete old backup files on a remote server.

the question that I still have is:

for the whole thing to work on windows server 2012, I could find not other way than granting Administrator on the remote box to the Proxy account.
as you can see on the picture below.

another alternative was to
create a powershell script that do both, backups and deletes, and create a sql server job with the following code:

Is this a powershell limitation when running on a remote server?
Are there other ways to work around it, without having to grant admin to the proxy account?

PowerShell Basics: Managing Remote Servers & Sessions


texts: I have seen descriptive text field named as DESCRIPTION, REMARK, and NOTE. They seem to mean the same thing. So what is more appropriate than the others?


texts: Experience using ScaleArc in test or production?

texts:
Has anyone had any experience using ScaleArc?

My CTO has asked my thoughts on it, and I have seen no information out there regarding real-world experiences.


texts: Performance for MySQL InnoDB B+Tree index with many duplicate values

texts:
I'm trying to diagnose seemingly random performance issues with our database server. Below is a simplified scenario, hopefully generic enough to serve as a useful future reference for anyone looking for the same answer.

Suppose I have a (MySQL 5.6 w/ InnoDB) table like

CREATE TABLE Example (
    id INT NOT NULL AUTO_INCREMENT,
    secondary_id INT DEFAULT NULL,
    some_data TEXT NOT NULL,
    PRIMARY KEY (id),
    KEY (secondary_id)
) ENGINE=InnoDB;

with about 15 million rows. However, the secondary_id column is NULL for almost all rows, so the index on secondary_id has very, very low cardinality (in our case about ~30k). In our case, when we experience the performance issue I'm investigating, the process list for the server shows many (100+) queries of the form:

UPDATE Example SET secondary_id = NULL, some_data = '...' WHERE id = 123;

that are taking ~90+ seconds to complete, during which they are in the "updating" state. (These queries would be running in separate transactions.)

I'm specifically wondering if the transition from a not-null secondary_id to a null secondary_id is causing performance slowdowns in the above UPDATE. That is, is it possible that updating the index in this case takes a significant amount of time since there are so many rows (~15mil) that have the same value for that column (NULL)?

I guess this question stems from me not understanding how the B+Tree index will store row pointers for rows having duplicate index values. I'd guess that node would have a linked list (or something similar) with a pretty fast insertion time, so I'd guess the answer to my question is "No". But I'd like to confirm that with the experts, i.e. you all.

I've tried to do a fair amount of research here, but I've come up pretty empty handed. Probably the most comprehensive post is this one, which explains some different techniques for handling duplicate keys, but I'm specifically looking for InnoDB/MySQL's approach.


texts:
When I run the following query in MS-Access:

SELECT a1.ConStateNumber, a2.ConStateNumber
FROM qryW2_yr_sum_slash_qryW2_q4_sum_result a1 INNER JOIN
     qryW2_q3_sum a2 ON a1.ConStateNumber = a2.ConStateNumber

I end up with values in the a2.ConStateNumber column, but only empty values in the a1.ConStateNumber column.  Also this query results in 372 records.

Now I'm pretty sure that the keys are matching up and it is only displaying an empty value in a2 because when I run the following query I get more results than I do when I run the previous query...

SELECT a1.ConStateNumber, a2.ConStateNumber FROM
qryW2_yr_sum_slash_qryW2_q4_sum_result a1 RIGHT JOIN
qryW2_q3_sum a2 ON a1.ConStateNumber = a2.ConStateNumber

...resulting in 402 records, but still no a1.ConStateNumber

And further still when I modify the query to display only the values that have NULL values the originals that appeared in the inner join do not appear.

SELECT a1.ConStateNumber, a2.ConStateNumber
FROM qryW2_yr_sum_slash_qryW2_q4_sum_result a1 RIGHT JOIN
qryW2_q3_sum a2 ON a1.ConStateNumber = a2.ConStateNumber
WHERE a1.ConStateNumber IS NULL

...and this returns 30 rows.

But I still do not understand why the values are not being displayed, there are a bunch of other queries / VIEWs that this query pulls from, so could this in some way effect the queries ability to display the a1.ConStateNumber values?

It doesn't matter that much provided the next query will be able to use the values to key off of, but I just wondered what was going on.


texts: Using mysqldump on RDS to export a large file in TSV format

texts:
I would like to export a single, large table (30GB) from an RDS MySQL server on a regular basis into a TSV file, so that we can load the file into Vertica.

However, RDS doesn't provide a way to use mysqldump with the --tab=path option, so we need to parse/convert the INSERT statements to TAB seperated files.

I would like to convert lines from the dump file like this:

INSERT INTO MyTAble VALUES ('data_1','data_2', 'date_3');

to this
    'data_1'   'data_2'     'data_3'

Since I need to parse through a 30G file, I would like to find the most efficient way of doing this?

Thanks for any help.


texts: Splitting a table with large columns for performance?

texts:
I am working on a system that stores images in its database (SQL Server 2014) using the varbinary datatype. The table looks like this (extra columns removed for brevity):

| ImageDataId | DateUploaded |
|   1         | 1-1-2001     |
|   2         | 1-1-2001     |
|   3         | 1-1-2001     |

There is also a seperate table for the image itself.

| ImageId | ImageFile    |
|   1     | [byte array] |
|   2     | [byte array] |
|   3     | [byte array] |

The database was made with Entity Framework Code First, so the ImageId key in the second table is a foreign key reference to the Id in the first table.

I was told these tables were designed to improve performance. If I only need ImageData, but no image, querying the first table without any imagedata in it would be fast. I'm not an experienced DBA, so I may be missing something, but wouldn't a query be just as fast if I merge both tables:

| ImageDataId | DateUploaded | ImageFile   |
|   1         | 1-1-2001     | [byteArray] |
|   2         | 1-1-2001     | [byteArray] |
|   3         | 1-1-2001     | [byteArray] |

And then do a SELECT without the ImageFile column? Or is this query:

SELECT DateUploaded
FROM Images

Somehow faster with split tables?


texts: Microsoft access to Sql Server Data Migration

texts: I need to migrate some old MS Access data to a SQL Server Database. Which tool is easier for a newbie to work with: SQL Server Import/Export or Migration assistant for access(SSMA)


texts: what is the difference between data file and data block in oracle

texts:
I'd like to know if there is any difference between the data block which is the  smallest logical unit of data storage in Oracle Database and the data file which is a physical file on disk that was created by Oracle Database and contains the data for a database?

Can anybody please tell me that what exactly difference between the data file and the data block?


texts: Is SET INTEGRITY of any use after IMPORT statements, or just after LOAD statements?

texts:
I'm getting the feeling, both from documentation and empirically, that you can only put SET INTEGRITY to good use after a LOAD, not after an IMPORT. LOAD is mentioned for SET INTEGRITY, but not IMPORT.

If you try an IMPORT with INSERT_UPDATE, you may get some rows rejected, as you may with a LOAD. Whereas you can embed the use of an exception table directly in the syntax of the LOAD or in a SET INTEGRITY statement used directly after the LOAD statement, and the exception tables will "get" the rejected rows, but hat doesn't appear to be the case with IMPORT.

There's no ability to embed the use of the exception table in the IMPORT statement, and if you run the SET INTEGRITY statement after the IMPORT has rejected the rows, it's too late.

Am I right or wrong?


texts: Help defining a chained one-to-many relationship tables

texts:
Sorry if the title is confusing but I couldn't think of a way to properly word this question without laying out what I am trying to do.

Basically I have 3 tables and I'm trying to figure out how to best relate them all given the constraint that each Template should have a region but necessarily doesn't have to country.

 Template
  id
  name
  description
  country_id

 Country
  id
  name
  region_id

 Region
  id
  name

Two solutions that I've come up with but am not really satisfied with are:

add region_id column to template table.  The problem I have with this solution is creating the circular relationship between the region_id in country and in template. Enforcing that region_id is the same in country and template tables also seems difficult to enforce.
add region specific country records to the country table that have no real country value but have a relationship to an existing region.  For example:

Country:
 id:5
 name: North America
 region: 1 -> North America

Solution 2 seems the simplest but I also feel like its not the most elegant solution because I am creating these "fake" country records.


texts: Disable MEMORY_TARGET after being locked out of database?

texts:
I'm setting up a local development environment in a virtual machine in which I installed Oracle XE among other software.

When I initially set up the virtual machine, the RAM was set to 4Gb. In this condition, I installed and run Oracle XE smoothly, without problem.

Then 4Gb of RAM proved to be a little to much for the laptops of my teammates, so I stopped the virtual machine, decreased its memory to 2Gb and restarted it.

Since then Oracle XE starts, as far as I can tell from the output of the service, but it refuse incoming connections because it seems MEMORY_TARGET is set too high.

Now, since I'm currently locked out from the database, how can disable the MEMORY_TARGET at all, or at least decrease the amount of space it tries to allocate ? I know I should be able to do it altering some system tables, but I'm not longer able to connect.

Here's some info you can need to give a clearer picture:

cat /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log

WARNING: You are trying to use the MEMORY_TARGET feature.
This feature   requires the /dev/shm file system to be mounted for at
least 1073741824   bytes /dev/shm is either not mounted or is mounted
with   available space less than this size. Please fix this so that
MEMORY_TARGET can work as expected. Current available is 1049767936
and used is 73728 bytes. Ensure that the mount point is /dev/shm for
this directory.
memory_target needs larger /dev/shm

This is instead the status of the service...

$ sudo service oracle-xe status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 21-DEC-2015 12:04:39

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                21-DEC-2015 11:31:33
Uptime                    0 days 0 hr. 33 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/infocertcop/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=infocertcop.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

I know I should be able to fix the issue restarting the virtual machine with 4Gb of RAM, change the MEMORY_TARGET configuration and then decrease again the amount of RAM, but I would really prefer to discover how to do it in another way, if possible.


texts:
If I do a startup restrict on the primary database, does this state effect the standby?  In particular, I am interested in two things:

Does it do anything to hinder archivelog shipment to the standby, or do the logs continue to flow just at a reduced rate due to the limited activity?

Is this state in any way transferred to the standby?  If I were to stop the archive log apply after this the primary is in restricted session and open the standby database with alter database open, would it be in restricted session?


texts: Hundreds of sessions queued in suspended state

texts:
We experience hundreds of sessions going in suspended state on rare occasions.
When it happens there is no increase in terms of resource usage - CPU or memory. Rather there is a small amount of decrease 5-10% on CPU.
There is no single error on the SQL log.
I know this will be a blind question but what might be causing this? Any suggestions to check in SQL Server like plan cache etc..?


texts: Communication between DB servers outside Active Directory

texts:
Dear SQL Server / Windows PROs:

I am trying to set up a solution here (based on SQL Server 2012 Std + Windows Server 2012 R2).
Currently the setup is based on a Cluster (two servers, 1 SAN) and stand alone server.
They all under one single domain so I have no problem with them to talk to each other. Because of business decision I was asked to set up
another two servers outside that domain.
One as mirroring / log shipment recipient, the other one has to communicate with the cluster to keep several objects in sync (few tiny tables total under 10mb) and send some data back (here it will be more data, but we can accept longer delays).

Whatever I am trying I am hitting the wall during the authentication setting as being outside the same Active Directory is just killing the whole concept.
Can you show me a direction of how can I pass it? Doesn't have to be detailed I will do my homework. Thanks!


texts: Formatting query to respond to past column values

texts:
I'm writing a query that follows the process of forms being processed through various SQL functions - they receive status numbers as they go through the process. I want to pull forms that are in a very specific situation: That (at most recent date) have code 599, but have NOT HAD code 550 in the past. For instance if I have the table:

If I performed a query of grabbing the counts that satisfy the table above, the return should be 1. Again, I am trying to figure out how to write a query that pulls forms that most recently have 599, but has not also had 550 in the past. Assume the table name is just "table."

My best attempt can only get the count of forms where the most recent date is 599, but I'm struggling to account for the past 550:

select t1.* FROM table t1
JOIN
(
    select id, MAX(create_date) AS MAXDATE
    FROM table
    GROUP BY id
) t2
ON T1.id = t2.id
AND t1.create_date = t2.MAXDATE
where status = '599'


texts:
I have a table with, say, preferred colors.
For every person, we store the favorite color and the effective date. If the person changes preference, we store the new one with a newer effective date.
Example:

name   color   eff_date
John   Green   2014-03-07
Luis   Red     2014-03-07
Luis   Yellow  2015-05-02
Nina   Blue    2015-07-06

If we want to take the current preferred color of Luis:

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date=( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' )

Now we decide that persons can change their favorite color anytime, but the change will only be effective from 1st January. So even if Luis entered a record that says that his favorite color is Yellow, the query must return the old value (Red) until 1st January 2016.

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date=( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' and eff_date < '1 jan 2015' )

This query seems ok, but will not return any record for Nina. The rules say that a newcomer can choose the favorite color immediately, without waiting for next 1st January.
In other words, "if possible, take a record from 1st January or earlier; if not possible, any record; take the most recent".
This is what I was able to write:

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date= ISNULL(
       ( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' and eff_date < '1 jan 2015' ),
       ( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' )
       )

Is there a more elegant, or more efficient, way to achieve this result?

I have no control on the tables structure, they are from PeopleSoft.

If Nina made two selections this year I want to show the most recent one.


texts:
I am trying to include just one table from another MariaDB server (lets call it A) into the database on another (even physical) server (lets call it B). And I came up with the conclusion that SPIDER engine might be a good option. But I can't make it work. Here is what I did so far:

on server A

I installed the SPIDER engine even though I don't think it is necessary
Port 3306 is visible from outside
MariaDB is configured to allow connections from everywhere
I've created a user test_user with host set to %
I've created the following table in the test database:

CREATE TABLE sss (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50)
) ENGINE=InnoDB;

on server B

I installed the SPIDER engine
I've created the following SERVER:

CREATE SERVER dataNode1 FOREIGN DATA WRAPPER mysql
OPTIONS (
   HOST 'IP-server-A',
   DATABASE 'test',
   USER 'test_user',
   PASSWORD 'passwd',
   PORT 3306);
Then I created the table as follows:

CREATE TABLE sss (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50)
) ENGINE=Spider
COMMENT='wrapper "mysql", srv "dataNode1", table "sss"';

I manage to connect with test_user from server B to server A:

mysql -h IP-server-A -u test_user -ppasswd

But when I log with root to MariaDB on server B and I try to access the table sss, I get:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

What have I missed? Thank you.


texts: Are VIEWS (CREATE VIEW) represented in an ERD?   If so, how do you represent them and specify what tables they came from?


texts: DB2 LUW Query Optimization - IXSCAN timerons greater than total timerons

texts:
Sorry - I feel like this is a newb question, but I can't find anything on it.
In optimizing a query, I was able to apparently reduce the total timerons from 3,700 to 38.
However, on closer examination of the explain plan for the optimized query, I see one IXSCAN that reports 3,707.97 timerons, while the total is still reported as 38.61!

I'm not super familiar with DB2 LUW, and was wondering how this could be.  Even with parallelism, I would not think we could get much smaller than, say, 1/4 of the largest #. Surely not 1/10th. (?)

This is DB2 LUW 9.5 on AIX.  Very plain-vanilla installation for testing environment. No HA, no partitioning, nothing like that.


texts: Postgres privileges: psql \dp - what is about the plus-sign

texts:
I used psql-command "\dp" to find out the given grants.

Versions on test-server: PostgreSQL 9.1.15 on armv7l-unknown-linux-gnueabi, compiled by gcc (Debian 4.6.3-14) 4.6.3, 32-bit

Out of the postgres-handbook:

rolename=xxxx -- privileges granted to a role
    =xxxx -- privileges granted to PUBLIC

        r -- SELECT ("read")
        w -- UPDATE ("write")
        a -- INSERT ("append")
        d -- DELETE
        D -- TRUNCATE
        x -- REFERENCES
        t -- TRIGGER
        X -- EXECUTE
        U -- USAGE
        C -- CREATE
        c -- CONNECT
        T -- TEMPORARY
  arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
        * -- grant option for preceding privilege

    /yyyy -- role that granted this privilege

When I fire up the command there is an additional plus-sign at the end of the acl-column (right after postgres). I could not find any documentation about it.

postgres=# \dp test.*
                               Access privileges
 Schema | Name  | Type  |     Access privileges     | Column access privileges
--------+-------+-------+---------------------------+--------------------------
 test   | films | table | postgres=arwdDxt/postgres+| code:                   +
        |       |       | =arwdDxt/postgres         |   candando=r/postgres

What is the plus sign about?


texts: Availability Group Monitoring Redo Rates

texts:
We currently use Idera Diagnostic Manager to monitor our SQL environment, which now includes a couple of Availability Groups. Idera monitors the redo synch rates and starts to complain when the rates are too low.

My question is this, should we care about the redo rate when there is nothing in the redo queue? It would seem to me that the Log Send Queue and Redo Queue are better indicators of when the replicas are behind on replication. Would an alert based on the these queues be a better alert than alerting on redo alerts?

Thanks.


texts:
In our application we are planning to use SQL Server CE 4.0 using OLE DB. The connection string is shown below.

Dim strConn As String = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=\\pc137\d\desktop\mydb.sdf; SSCE:Database Password='password';"
Dim conn As New OleDbConnection
conn = New OleDbConnection(strConn)
conn.Open()

This one worked for both the local and network share files (.sdf)

We tried to connect using Visual Studio for the network share we get following error

  SQL Server Compact does not support opening database files on a network share.

We have referred this links

So is it allowed to connect SQL Server CE 4.0 using OLE DB in network share?

Does it cause any other problem?


texts:
I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order.

If I run the following query:

select
     col1,col2
from
     tbl1
where
     col3 = something
and
     col4 = something
and
     col5 = something

Is it any different than running:

select
     col3,col2 ,col4
from
     tbl1
where
     col1 = something
and
     col5 = something

?

What I am trying to say is, when we create a covering index like the above, and we change the order in which we are accessing data, will it still take advantage of this covering index?

Also, the order of equality columns (amongst themselves) and non equality columns (amongst themselves)... does it matter? Like where equalitycol2='' and equalitycol1=''.


texts: Why Do My Nonclustered Indexes Use More Space When I Delete Rows?

texts:
I have a large table with 7.5 billion rows and 5 indexes.
When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on.

I wrote a query against dm_db_partition_stats to report the difference (after - before) in pages:

Index 1 is the clustered index, Index 2 is the primary key. The others are nonclustered and non-unique.

Why are the pages increasing on those non-clustered indexes?
I expected the numbers to at worst stay the same.
I do see performance counters report an increase in page-splits during the delete.

When deleting, does the ghost record have to move to another page?
Does this have to do with "uniqueifiers"?

We are in the middle of rolling out RCSI, but right now, RCSI is off.

It is a primary node in an availability group. I know that snapshot is used somehow on secondaries. I'd be surprised if that was relevant. I plan to dig into this (looking dbcc page output) to learn more. Here's hoping someone has seen something similar.


texts:
Inherited a new SQL estate that has never been touched by a full time DBA. Mirroring has been setup and removed on multiple servers but not fully, old witness servers are still active.

Is there anyway from the witness server to determine if a principle/mirror is still communicating or active.

I have picked through the DMV's with the DMV below being the most helpful but one of the four witness instances does report a connection, any advice would be helpful.

sys.dm_db_mirrroing_connections


texts: Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

texts:
I'm wondering if this statement can cause real overhead if many concurrent users
send the same request to mySQL InnoDB storage engine. Let's say I have a table that
has the user_name, and someone want to find whoever that has the word alex in their
username. If I use LIKE as follow with InnoDB engine, do you think it can cause so
much over head ? Let's say I index the username field.

Select name from User where user_name LIKE '%alex%';


texts: Correct way to join row in parent table to row in one of several children tables

texts:
I have a parent table product, and several children tables like phone, tablet, smartwatch etc. The parent and children have 1-1 relationship. This means that for every row in parent table, there will be exactly 1 row in exactly one of the several children tables. This is to basically pull up common fields in a single table (like product.product_name), in my effort to follow the DRY principle.

Now the part where i am worried is that, although i can easily join to the parent row, provided the child row, how would i efficiently do vice versa.
For example: if i have a row from the product table, and now i want to get the rest of the product information, how would i quickly find which child table contains the rest of the information, other than having to check each and every child table in a big query. In simpler words: How to efficiently find the correct child table in order to join a row in parent table to its corresponding row in one of the children tables.

Let's assume i would soon have 100+ children tables. In such a case, an exhaustive query would get bigger and bigger with every new child table.

One obvious solution would be to maintain a 'product_type' field in the parent table. But this is clearly redundant information, violates the Single Source Of Truth rule , and prone to human error (eg; product_type says 'phone' but the product actually was a 'tablet'). So i am not sure if this is the recommended way to do it.

I am meaning to find out the canonical way to solving such a problem when designing schema, and not just an opinion-based answer.

I am sorry if this wasn't the right forum to ask this question.

UPDATE:

I later came across this answer to another post and am really feeling positive about it.


texts: Odd error when stress testing against an AG

texts:
We are running into an odd error when trying to stress test our application that hits an AG.  If we point the application directly to the primary server, we don't see any issues with the test.  However, if you point the application at the listener, we get this error:

  provider: TCP Provider, error: 0 - You were not connected because a
  duplicate name exists on the network. If joining a domain, go to
  System in Control Panel to change the computer name and try again. If
  joining a workgroup, choose another workgroup name.

Our connection string is similar to this:

> connection string= "data source=DB_C_Name; MultiSubnetFailover=True;
> initial catalog=DBName; integrated security=True;
> MultipleActiveResultSets=True; App=EntityFramework;"

The DB_C_Name is an AD CName that points to the Listener, which in turn points to the WFC.

We have double checked and all of the WFC setting seem to be fine and we can't find any duplicate IPs on our network.

We are at a loss as to why this would happen when going through the listener but not going to the server directly.  We've also noticed this happening in a few other locations.


texts: Postgresql upgrade generated vaccumdb script not vacuuming?

texts:
When performing an upgrade, postgresql generates a script with the following command in it:

"/usr/lib/postgresql/11/bin/vacuumdb" --all --analyze-in-stages

On the page: https://www.postgresql.org/docs/11/app-vacuumdb.html

The following options are listed:

  -a
  --all

  Vacuum all databases.

  --analyze-in-stages

  Only calculate statistics for use by the optimizer (no vacuum), like --analyze-only. Run several (currently three) stages of analyze with different configuration settings, to produce usable statistics faster.

  This option is useful to analyze a database that was newly populated from a restored dump or by pg_upgrade. This option will try to create some statistics as fast as possible, to make the database usable, and then produce full statistics in the subsequent stages.

The page also states:

  vacuumdb is a wrapper around the SQL command VACUUM. There is no
  effective difference between vacuuming and analyzing databases via
  this utility and via other methods for accessing the server.

However, after running this script and then going into a database and running:

VACUUM VERBOSE;

The output of that is showing me many unused item pointers, index row versions being removed and per index how many rows it removed.

So it looks to me like that command from the generated script didn't vacuum the tables at all.

Is this right? Or is something else going on?

EDIT:

I just now ran:

"/usr/lib/postgresql/11/bin/vacuumdb" --all --echo --verbose

And it's showing me the same output as VACUUM VERBOSE;, which didn't get printed while running the command generated by the upgrade, even with echo and verbose on.

So it seems to me like it's only executing --analyze-in-stages. Is this a bug that should be reported or am I seeing things wrong here?

EDIT: To clarify, --all clearly is documented to mean Vacuum all databases. And when I run it alone, it does in fact do that. But the second I add an extra option like --analyze-in-stages it skips vacuuming entirely.

I would expect it to perform all provided flags and not ignore --all(which is a vacuum command and it does in fact perform a vacuum if it is provided to vacuumdb without --analyze-in-stages.


texts: Convert a Unix timestamp to a DATETIME in a View

texts:
I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out.

This link describes how to do the conversion, but requires changing the nls_date_format to include the time portion. The default currently only shows the date portion. I need the date and time.

Unfortunately, that solution only works at a session level. As a developer, I'd rather not have to go running off to our managed service provider to ask them to change the value at a database level - especially since it may impact other applications.

Is there a way I can convert the timestamp, in sql, to a datetime without modifying the system?


texts: Innodb composite primary key with 4 columns vs MyIsam

texts:
I've created "MY TABLE" based on Innodb with composite primary key because I am storing many trees inside this table. The PK is for example (a,b,c,d).

1- Does Innodb store rows with same (a,b,c) near together?

2- Does every (a,b,c) add one 'page' (at least 4 kb) and so increase my table size?

3- Does this query almost checks all table?
   Select * from mytable where a='1' and b='2'
So it would be faster if I user 'MyIsam' and use secondary index (a,b) ?

Many Thanks

PS: I edited and removed that question. (yeah,that was really funny :) )


texts: Why is datalength of decimal 5 regardless of precision?

texts:
I'm trying to better understand numeric types in SQL and have read that the decimal type will always require 17 bytes. However, the MS Docs list a table indicating the amount of space used depends on the decimal's precision. So I tried to test it using the datalength function.

create table tbl_TestDec(dec1 decimal(19,4), dec2 decimal(20,4), dec3 decimal(9,4))

insert into tbl_TestDec
select 1, 1, 1

select datalength(dec1), datalength(dec2), datalength(dec3) from tbl_TestDec

This outputs:

5   5   5

I was expecting either 9  13  5 or 17  17  17. I'm using SQL Server 2005. Are all decimals vardecimal or am I misunderstanding the datalength function?


texts: DB2 database -> does it continue to be platform-specific? (iSeries, LUW, z/OS)

texts:
Wikipedia says DB2 is no longer Platform-specific:

  Historically and unlike other database vendors, IBM produced a platform-specific DB2 product for each of its major operating systems. However, in the 1990s IBM changed track and produced a DB2 "common server" product, designed with a common code base to run on different platforms.

If this is the case, why are there different drivers to connect to DB2 for iSeries and DB2 for Linux, UNIX and Windows? (I'm not sure if DB2 for z/OS has its own driver as well.)


texts: SSIS 2008 Connection Managers (Project Specific) and Corresponding Configurations

texts:
These are the requirements that I want to complete:

a. I want the package to only use connection managers that are project/not package bound.

b. I also want these connections to be bound to the ssis config so that I can migrate these packages and they will connect to the correct environment seamlessly

c. I want to make sure this will work for pre-validation runs of the package so that it will validate its meta data against the db in the config rather than the hardcoded values in the package

So, my questions/concerns are:

How could I make my connection managers Project and NOT package specific?

I want to take these project specific connection managers and relate them to SSIS configurations

Is there a way to have both SSIS configuration (Config Tables) and store those values in an Environment Variable?

Any help is well appreciated. I know project bound connection managers are available in SSIS 2012, but i'm looking for an equivalent method in SSIS 2008


texts:
I just installed Oracle XE 18c ... In this page, they list the available features/resources. One of them is the use of two CPU threads ...

But, in practical terms, what does this mean?  Does this mean that only two queries can be executed concurrrently? or what?


texts:
I have a mySql database and produce webpages through use of php.

I have a database that in simplified form collects data of the form: date|name|score which is unique for date|name.

From this database we create a table that produces the top 12 scores per player per year. This query takes a while to run and I was thinking of how one can speed up the process. My intuition tells me that the speed is related to the size of the database.

My thoughts are:

Somehow split the database into past years and this year. Sort of like an archive. Then I will have two databases.
Somehow create a temporary table of just this year's scores and then run the top 12 join query on this table. If so, then how should one go about doing this?

So my question is: which of these two options is best, or even, are there better options that I haven't thought of?


texts:
I have an application which persists itself to a PostgreSQL 11 server. It does this by truncating the tables (removing the old state) and then uploading its current state (many statements) as part of one transaction - So, if something goes wrong the old state will be preserved. It's difficult to calculate a delta, which seemed like a sensible compromise

I've recently noticed occasional SIGKILLs wiping out the DB after my application runs for a long time (the information to save grows), with the Failed process running: being this persistence cycle. I'm wondering if this is due to the single mega transaction being a large blob in memory which eventually OOMs, and if so would breaking it up into subtransactions/savepoints address the problem? Or is there a better way to do this?


texts: MySQL won't start after upgrading to PHP 5.3

texts:
Just upgraded to PHP 5.3 with the latest version of mysql, but now mysql won't start. Below is mysqld.log

110516 22:24:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110516 22:24:46 [ERROR] Error message file '/usr/share/mysql/english/errmsg.sys' had only 480 error messages, but it should contain at least 709 error messages. Check that the above file is the right version for this program!
110516 22:24:46 [Note] Plugin 'FEDERATED' is disabled.
/usr/libexec/mysqld: Unknown error 1146
110516 22:24:46 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110516 22:24:46 InnoDB: The InnoDB memory heap is disabled
110516 22:24:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110516 22:24:46 InnoDB: Compressed tables use zlib 1.2.3
110516 22:24:46 InnoDB: Using Linux native AIO
110516 22:24:46 InnoDB: Initializing buffer pool, size = 128.0M
110516 22:24:46 InnoDB: Completed initialization of buffer pool
110516 22:24:46 InnoDB: highest supported file format is Barracuda.
110516 22:24:46  InnoDB: Waiting for the background threads to start
110516 22:24:47 InnoDB: 1.1.6 started; log sequence number 10818302
110516 22:24:47 [ERROR] Aborting

110516 22:24:47  InnoDB: Starting shutdown...
110516 22:24:48  InnoDB: Shutdown completed; log sequence number 10818302
110516 22:24:48 [Note]
110516 22:24:48 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Any clues?

Thanks. Help Appreciated!


texts: Impact of miss-ordered fields in a composite, clustered primary key?

texts:
I have tables where the primary key is something like this:

-StoreID
-BusinessDate

Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a daily basis and is never updated and almost never deleted.

I think is would be better if the field order would be:

-BusinessDate
-StoreID

My reasoning is that the sort order of this key would more naturally follow the typical insert order. I'm wondering if I should change the order of the fields in the key.

So far performance has been acceptable. Will the current field ordering cause serious performance problems in the future if I don't change anything?


texts: MS Access / MySQL: unlock locked Records

texts:
I have a Microsoft Access database that is connected to a MySQL server via ODBC and is accessed by multiple users.

Over time many records are locked and editing is not possible anymore.

Is there a way to bulk unlock all records?

The Error Message:

  This record has been changed by another user since you started editing
  it. If you save the record, you will overwrite the changes the other
  user made. Copying the changed to the clipboard will let you look at
  the values the other user entered, and then paste your changes back in
  if you decide to make changes.


texts: SQL Server: specifying a unique optional column

texts:
SQL Server has a quirk with optional unique columns: you cannot have more than one null in a table.

For example:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) unique  --  optional, but unique
);

(In Australia, we have a Tax File Number, which is unique to the tax payer).

The following is a workaround:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9)
);
CREATE UNIQUE NONCLUSTERED INDEX uq_employees_tfn ON employees(tfn)
WHERE tfn IS NOT NULL;

Is it possible to name a unique constraint and give it the above condition is a manner similar to the first example? That is, is it possible to include the more complex constraint inline with the column definition?


texts:
I'm running sp_BlitzCache (Brent Ozar) to get some information about database performance. The program that "attacks" this database uses cursors everywhere, so sp_BlitzCache gives me several lines where Query Text is something like 'FETCH API_CURSOR000000000045E513'. Is there any way to know what the select of that cursor is?
   Thanks in advance.


texts: Are there any incompatibilities between Oracle 11 and 12?

texts:
I have an application which has been validated on an Oracle 11.2 database. A client has asked me whether they can use Oracle 12c instead as that is what their IT department would prefer. Is there an official list anywhere of incompatibilities between Oracle 11 and 12? Is there anything I need to look out for?

UPDATE

I was looking through the Oracle database and found the following (conflicting) information:

  Existing applications running in a new release of Oracle Database
  function the same as they did in earlier releases and achieve the
  same, or enhanced, performance. You are not required to modify
  existing applications that do not use features available in the new
  Oracle Database release.

Followed by:

  There might be compatibility issues between different releases of
  Oracle Database that could affect your applications. These
  compatibility issues result from differences in Oracle Database in
  various releases. Also, in each new release of Oracle Database, new
  Oracle reserved words might be added, changes might be made to
  initialization parameters, and changes might be made to the data
  dictionary.


texts: How would one merge two databases that use different keys?

texts:
I have Database 1, and Database 2. Database 1 is the companies native data on clients and Database 2 is an external database being ported over from a different company. I'm trying to find what customers (Database 1) in our database, exist in their database (Database 2).
Database 1 has: customer_id (PK), customer_name, customer_email, customer_phone, customer_location, etc...
Database 2 has: customer_key (PK), customer_name, customer_email, customer_phone, customer_location, etc...
The question is: Find a way to link Database 1 to Database 2 so that each person in Database 1's information is joined with their respective information in Database 2.
The problem is: these databases have two different primary keys (or ids assigned to an actual real life person). So you can't just simple query them based on the PK "customer_id" because "customer_id" does not exist in Database2 and vice versa.
Initially I think of using a variable that is unique (i.e email) and matching on that...
But... what if the email they provided in one of the databases is "shared", meaning, in Database 1, that email is actually just an email the business shares for a bunch of different departments... Then it's not really helpful in linking back to a single person. Or if in Database 2 they provided a shared email, so many different records if grouped together on email will show they have multiple names, and phone numbers associated with them... This gets even more complicated when all the variables in the database (phone, name etc...) also have this "shared" problem for some of their entires.
People can also change companies (changes their email and possibly phone) or how people change their first and last name (more so last name).
The only solution I can think of at the moment is to use some sort of combination of verifying the email matches, then verifying that phone, name and location do as well. The more items that they match on between databases, the more certain one can be that "customer a" in Database 1 is "customer 123" in Database 2.
How do you guys deal with this? Do you use a LIKE condition or something similar and just set a threshold for similarity when joining?


texts: Log all than queries taking more than X seconds

texts:
I have a database Microsoft SQL Server 2008r2. I have some applications in different programming languages.

I would like to log all the queries that take more than X seconds.

I guess that the I should create an event session, but I don't understand them enough to do this specific task. The ideal answer would include a link to an online tutorial with examples on event sessions; even a book recommendation would go.

I did the training 6231A "Maintaining a Microsoft SQL Server Database" at Microsoft few years ago, but I have not practiced since; the courseware covers the argument but I cannot understand it.