texts: MySQL-5.1 is not updating some rows with a simple UPDATE query

texts:
I'm trying to update a column in a table to be the same as the contents in the column of another table as part of a de-normalization process for a data warehouse project. The data is confidential, so just to cover my ass I'm going to provide an example using abstract table and column names.

I have three tables, call them A, B and C. A has about 15M rows, and is the main data cube table, and B and C are fact tables, with C being a lookup table for descriptions. Table A has a column dtype which is either 1 or 0, where 1 indicates the data is actual (derived from fact tables of actual transactions) and 0 indicates the data is theoretical (derived from projections). If I run the following statements

UPDATE A SET desc = NULL; -- Just for clarity with regards to initial state

UPDATE A, B, C SET A.desc = C.desc WHERE B.id = A.b_id AND C.id = B.desc_id AND A.dtype=1
  -- produces many warnings the last 64 of which are data truncation warnings

SELECT count(*)
FROM
  A
  JOIN B ON B.id = A.b_id
  JOIN C ON C.id = B.desc_id
WHERE
  A.dtype = 1
  AND A.desc <> C.desc

The last select statement returns 200,000+ for the count. How is this possible?

I'm using MySQL version 5.1.67-0ubuntu0.11.10.1. All the tables are MyISAM.


texts: SQL Data warehouse and normalization forms

texts:
I don't know if it's a simple question but does a data warehouse have different rules when it comes to normalization forms?

At the moment I am creating some tables in a data warehouse, and they simply do not fulfill the normalization forms rule. I have tried to find a method to fulfill them, but it seems to be pointless.


texts: Understanding disk requirement to make fail over cluster

texts:
Please take a look at the following screenshot.

I am trying to make SQL Server 2012 Fail Over Cluster using two nodes. As you know we need to have a shared SAN which will be used by both nodes to store data but I have some confusion regarding it.

I was under the impression that we need one drive for e.g. Z for "Temp DB directory" which will be shared by both nodes to store data.

But someone told me that these will be two drives actually for each node and for these nodes it will appear as one. So I am not sure what is true. May be we are both saying same thing!?


texts: How to only update if nothing has changed? I need to validate several things before I insert

texts:
Say I have two tables: runners and waypoint_checkins. I want to add waypoint checkin entries but I can never add a waypoint if the runner has finished the race, (see below for schema). Finally. A runner can never check into a waypoint if the waypoint is listed as closed.

table: runners
id | name | is_finished
---+------+------------
15 | abe  | false

table: waypoints
id  | currently_open
----+---------------
1   | true
2   | true
3   | false
... | ...

table: waypoint_checkins
runner_id | waypoint_id | time
----------+-------------+-------
15        | 1           | 5:00
15        | 2           | 5:23
15        | 4           | 5:52

So on a checkin I'm given (runner_name, waypoint_id, time). First I need to find the runner and make sure they are still running:

SELECT id, is_finished FROM runners WHERE name = %name;

Then I need to check if the runner is at an open waypoint;

SELECT currently_open FROM waypoints WHERE id = %waypoint_id;

Finally I need to insert a new waypoint checkin:

INSERT INTO waypoint_checkins VALUES (%runner_id, %wp_id, %time);

But between query 1 and 3 the runner may have finished. How can I do the insert statement where I can guarantee that the earlier select is still valid?


texts: How to preseve Execute permissions for MySQL procedure with alter procedure (Drop/Create)

texts:
In MySQL you can not alter procedure rather you have to perform drop/create procedure to alter the body of the procedure. The problem is doing drop/create also removes the execute permissions given on that selected procedure.

Is there any way of giving permanent execute permissions that remains even after drop/create procedure.

A sample code to verify is as below

  delimiter //
  create  procedure t1 ()
  select 1;
  //

  grant execute on procedure t1 to test1@'%';

call t1() works well, but if you run the above code after doing drop procedure first it gives permissions error.

Any help is much appreciated


texts: How to Parse a query in Azure Data Studio?

texts:
In Sql Server Management Studio, we can click a "Parse" button (or Ctrl+F5) to verify that a SQL statement is syntactically correct (without executing it).

How can I do it the same in Azure Data Studio?


texts: Create pgAdmin database on local machine

texts:
I am trying to create databases on my local machine using pgAdmin. When I try to connect to a new server using the "New server registration" entry below it gives me the "Server doesn't listen" error (picture below).

I had tried the same procedure on my previous machine and it worked without any trouble. Any thoughts would be very helpful. I've tried some different combinations and nothing has worked yet. Thanks in advance for the help.


texts:
I'm currently studying normalization. I know how to normalize the data for given unnormalized list.

But I found this question in our past papers. I don't know how to answer to it.

Q. Decompose R{a,b,c,d,e,f} into 2NF using following functional dependencies.

a -> b,c,d,e,f
b,c -> a,d,e,f
b -> f
d -> e

Please can anyone tell me how to do this? I went through some earlier questions,
but still have doubts.

For this my answer is:

      R0 = a - > b,c
      R1 = b,c - > a,d,e
      R2 = b - > f


texts:
At my current employer, the developers are branching for development per story and merging for release in Git - and frankly I'm jealous.   ...but I haven't been able to wrap my head around how a SQL Server DB workflow should work in this manner, especially considering tooling that I am aware of / using.   So, I'm curious if anyone has some thoughts.

~Our SQL Server environment

 [Prod] <- [Testing] <- [Dev Release] <-> [Dev01],[Dev02],[Dev03]

Each team has a dev server stack where stories are worked. There are 25+ databases per server.  When product/project determine which stories are to release, those changes are merged to Dev Release - and eventually flow back to team dev. Dev Release is then used to script the deployment.  Thus any ongoing projects (long term / third party integrations / etc) aren't included with the release.  This means that there is not a point guaranteed where dev release and any team dev are fully synchronized.  Also of note, the sequence of changes made to dev release are not related to the sequence that changes are applied within team dev...

Some ponderings:

One way (problem): If we have a single branch for each team dev server - it appears to be easy to keep in sync with tooling (RedGate Source Control, etc)...  ...but prepping for a release (in Git) either means cherry-picking commits (too much work) or git copying files and losing dev commit history (too much work).  (I assume that SVN's file deltas would preserve dev history - but would still be a pain (as its not a merge)...  guessing)   Although dev history is lost, we would still have a list of release changes - but that doesn't work for me.  This feels wrong.

Another way (problem): If we branch per story - then it would be fairly trivial to merge to dev release and preserve object history. However, there wouldn't be a single branch that matches the state of the database, and thus tooling (RedGate, etc) will always be complaining that the DB and the repo are out of sync. This feels annoying.

At my previous employer, we branched for release using SVN. All forward development teams worked towards a common release - thus forward changes were developed against the trunk (or a dev branch reconciled to the trunk), and hotfixes were applied to the release branches. Depending upon the product, we had different workflows for publish - but the idea was similar.  Most all the links I've found (even on this site) seem to point to that workflow - as described very well here:  http://www.infoq.com/articles/agile-version-control

However, all the teams (excluding hotfixes) were working towards a common release - thus the dev branches had to reconcile / merge.  So, if we had a project / integration that wasn't in the release, we'd have to back out changes of the trunk for the freeze - and reapply once the next release cycle started. Annoying - but doable.   Of note, the changes to the database could be applied in a serial fashion - as they were (mostly) all related.  So, it's no surprise that tooling SSDT / RedGate / etc seem to be geared for this...

So, just curious if anybody has any pointers, helpful URLs, or can point out where my ponderings have gone awry, I'd be grateful for the info.


texts:
I'm not sure isolation is the right term, but I think it is. Let me know if not.

I'm doing an insert...select statement, from a table with keys into a lock table. I have some WHERE conditions by which I select the row I want.

From what I understand, first a select happens, then the result is used for an insert. The question is, is there a chance that while the insert is happening, my WHERE conditions no longer apply to the row I insert? ie, can another query change the rows I SELECT...INSERTed, or are they locked between the two queries?


texts: Unable to get rid of orphan temporary tables

texts:
After migrating from MariaDB 5.5 to 10.0, I noticed an error message mentioning a table named piwik/#sql-1526_3a.

From there I started googling and landed on the innodb troubleshooting docs

Indeed, SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; gives the following result:

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
+----------+--------+----------------+------+--------+-------+
| TABLE_ID | SCHEMA | NAME           | FLAG | N_COLS | SPACE |
+----------+--------+----------------+------+--------+-------+
|      323 | piwik  | #sql-1526_3a   |    1 |     11 |   320 |
|      265 | piwik  | #sql-5305_92b0 |    1 |     24 |   262 |
|      321 | piwik  | #sql-7920_48   |    1 |     24 |   318 |
+----------+--------+----------------+------+--------+-------+

I tried to follow MySQL troubleshooting instructions and did:

MariaDB [(none)]> use piwik
Database changed
MariaDB [piwik]> drop table `#mysql50##sql-1526_3a`;
ERROR 1051 (42S02): Unknown table '#mysql50##sql-1526_3a'

From there, I read more blog posts and stack exchange answers and noticed that there are no #sql-1526_3a.idb and #sql-1526_3a.frm files in my /var/lib/mysql/piwik/ folder.

I then tried the following:

$ cd /var/lib/mysql/piwik
$ cp piwik_user.ibd #sql-1526_3a.ibd
$ cp piwik_user.frm #sql-1526_3a.frm

and restarted MariaDB.

Then I tried droping the table again:

MariaDB [piwik]> drop table `#mysql50##sql-1526_3a`;
Database changed
Query OK, 0 rows affected (0.00sec)

Horray?!! Well, SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; still lists the #sql-1526_3a table. On disk, the .frm file got deleted but the .ibd file remains.

So? Does someone know how I can remove these orphan tables for real?

EDIT

It seems to me these trailing temporary tables are "referenced" from /var/lib/mysql/ibdata1 before I shrunk it and enabled innodb_file_per_table = 1.


texts: I am confused to see this tinyint(3) structure. If tinyint can store only 1 byte then what is the purpose of giving the size in brackets? Is there something special with this parameter in MySQL?


texts: How to select attribute from two indexes, both pointing at the same table?

texts:
I have two tables, one with this form:

table teams:

ID, IDPupil1, IDPupil2
1   3000      3001

and the second this form:

table pupils:

ID,   Name,   Forename
3000  Smith   John
3001  Morgan  Lisa

IDPupil1 and IDPupil2 are referencing at pupils.ID.

Now I want to select all this values in one SQL-query.
I tried this:

SELECT teams.ID AS ID, IDPupil1, IDPupil2, pupils.Name AS Name, pupils.Forename AS Forename
FROM teams
LEFT JOIN pupils
ON IDPupil1 = pupils.ID
AND IDPupils2 = pupils.ID

The result is this:

ID,  IDPupil1, IDPupil2, Name, Forename
1    3000      3001      NULL  NULL

When I cancel the last row of the query (AND IDPupil2 = pupils.ID), then the Name and Forename of Pupil1 is shown. But with both indexes it obviously doesn't work.

So how can I get the following result?

ID,  IDPupil1, IDPupil2, Name1, Forename1, Name2, Forename2
1    3000      3001      Smith  John       Morgan Lisa


texts: Database/Storage engine suggestion for a project involving heavy inserts?

texts:
I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which  I need to monitor daily certain activities associated with them.

For example, let there be a set of 100 users say U1,U2,...,U100

I need to insert their daily scores into my database.

Consider the total score obtained for a user U1 for period June 30 - July 6, is as  follows

June 30 - 99
July 1 - 100
July 2 - 102
July 3 - 102
July 4 - 105
July 5 - 105
July 6 - 107

The database should keep daily scores of each users ,like

For user U1,

July 1- 1pt (100-99)
July 2- 2pt (102-100)
July 3- 0pt (102-102)
July 4- 3pt (105-102)
July 5- 0pt (105-105)
July 6- 2pt (107-105)

Similarly the database should hold daily details of the full set of user's.

And on a later phase ,
I envision to take aggregate reports out of these data like total points scored on each day,week,month,etc; and to compare it with older data.

I need to start things from the scratch.I am experienced with PHP as a server side script and MYSQL. I am confused on the database side ?
Since I need to process about a million insertion daily,what all things should be taken care of ?

Does MySQL fits my requirement,If so what storage engine should be used ? Initially I envision to create a user table with a foreign key user id and monthly score tables with dates as fields.And later on I got suggestion to write things into a csv/excel first and then to load them to table after a particular period.

Does file insertion makes things more favorable in this regard.

Or should I try for some other databases,NoSQL methods ?

EDIT

I am summarizing my requirements,I need to have a database of a million user's whose points are to be updated every day as separate entries.
This will be done regularly such that there should a field per day for each using showing daily points,which could be aggregated in weekly/monthly/yearly basis.
I am confused on the database design as well issue which could happen ever after deployment.
A million or more DB operations are done every day.How the server and other things are to be considered in this case.

Any help will be highly appreciated.Thanks in advance.


texts: MySQL - How much physical space will be reduced by changing the column type?

texts:
Lets say I have the following table:

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `relationship_status` varchar(48) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

and lets say this table have lots of records: 100M

I have 2 possible values to relationship_status: 'following' or 'not_following'

since I want to reduce the size of DB (size on hard drive), how would it effect if I would change the relationship_status to Boolean instead of varchar(48) (where if can define 0 as not following and 1 as following)?

which column type would you use? tinyint?


texts: How to prevent the creation of Mysql columns with default values that are not defined

texts:
Is there a Mysql server setting that will prevent the creation of columns that are NOT NULL but do not have a default value specified?

I'm trying to prevent the creation of fields like this:

ALTER TABLE mytable ADD mydate DATE NOT NULL;

I know I can prevent INSERTs by setting sql_mode=STRICT_ALL_TABLES, but I want to prevent the creation of the columns in the first place.


texts: External size of table still not released (as disk space) after issuing truncate command

texts:
In PostgreSQL 9.3.10 (Ubuntu 14.04 OS), I have a table called groupseen whose size is as follows:

              Table               |  Size   | External Size
----------------------------------+---------+---------------
          links_groupseen         | 394 MB  | 362 MB

I understand that External Size is the size that related objects of this table (like indices) take.

Upon issuing the command truncate links_groupseen;, only 394 MB disk space was released (I checked right after, via df -h). My two questions are:

What can I do to get rid of the space taken up by this table's indices (External Size) as well? Shouldn't truncate have taken care of everything?
I was using the following command to check the size of this table. Even though External Size hasn't yet been released, using this command now doesn't show the outstanding 362 MB. How do I monitor the outstanding External Size related to this table?

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
 FROM pg_catalog.pg_statio_user_tables
 ORDER BY pg_total_relation_size(relid) DESC;

In case required, here's how the table looks like:

                            Table "public.links_groupseen"
     Column     |           Type           |                          Modifiers
----------------+--------------------------+--------------------------------------------------------------
 id             | integer                  | not null default nextval('links_groupseen_id_seq'::regclass)
 seen_user_id   | integer                  | not null
 seen_at        | timestamp with time zone | not null
 which_reply_id | integer                  | not null
Indexes:
    "links_groupseen_pkey" PRIMARY KEY, btree (id)
    "links_groupseen_seen_user_id" btree (seen_user_id)
    "links_groupseen_which_reply_id" btree (which_reply_id)
Foreign-key constraints:
    "links_groupseen_seen_user_id_fkey" FOREIGN KEY (seen_user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_groupseen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED

Note: this SO answer doesn't solve  my problem because the OP in the question stated how disk space wasn't released for him at all. In my case, the subtlety is that only External Size is outstanding.


texts: Why do we have Top N Sort in this example?

texts:
In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top?
use tempdb;
go

create table dbo.t1 (id int identity primary key, v int);
create table dbo.t2 (id int identity primary key, v int);

insert into dbo.t1
(v)
select top (1000)
row_number() over (order by 1/0)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

insert into dbo.t2
(v)
select top (10000)
row_number() over (order by 1/0) + 10000
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

set statistics xml, io on;

select
sum(a.v + b.v)
from
dbo.t1 a outer apply
(select top (1) v from dbo.t2 where v >= a.v order by v) b;

set statistics xml,io off;
go

drop table dbo.t1, dbo.t2;
go

It's reproducible in all versions starting from 2008 R2 (I just have no server with earlier versions to test)


texts: Querying Oracle via Linked Server crashes SQL Server

texts:
A bit of an odd one this. I have a couple of instances of SQL Server 2012, SP1, one being Dev Edition and the other Standard Edition and both are VMs running under VMWare.

When executing a Stored Procedure to query Oracle via a Linked Server, the Standard Edition (let's call it P2) crashes SQL Server. The error message in the query window is 'A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)', however that's probably because SQL Server has crashed.

Querying the Dev Edition (let's call it U2), the query partially works but using things like DECODE and ROUND in the query also causes it to crash SQL Server.

On a third instance, again running Dev Edition (call this one D2), the same query works fine.

All three are exactly the same except for the edition difference between P2 and U2. All have 4GB of memory and all seem to have their default, out-of-the-box installation settings intact as far as I can see and all three have Oracle 9.2 clients installed and are using the Oracle Provider for OLE DB. I'm seeing absolutely nothing in the SQL Log to indicate what occurred just before the crashes however there is an entry in the App Event Log:

Faulting application name: sqlservr.exe, version: 2011.110.3128.0, time stamp: 0x50deadad
Faulting module name: ntdll.dll, version: 6.2.9200.17438, time stamp: 0x55a41b15
Exception code: 0xc0000374
Fault offset: 0x00000000000ea539
Faulting process id: 0x4aa0
Faulting application start time: 0x01d121404502766c
Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Faulting module path: C:\Windows\SYSTEM32\ntdll.dll
Report Id: 87c13341-8d33-11e5-9409-005056952126
Faulting package full name:
Faulting package-relative application ID:

And I have intermittently seen other errors, most notably the following:
'Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "UBIX" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7305, Level 16, State 2, Line 1
Cannot create a statement object using OLE DB provider "OraOLEDB.Oracle" for linked server "UBIX".' However this error may well clear after a server reboot as it did previously.

The only piece of information that might be worth looking at so far is a reference to Distributed Transactions Role service not having been installed under the Application Server role. No idea whether that's relevant or not so I was wondering if anyone might be able to point me in the right direction.

Regards,

Gordon.


texts: What's the difference between group roles and login roles?

texts:
It looks like for single user I should create a login role and than respective group roles because I can't directly assign privileges on login role. Is it true? What's the purpose of such separation?

Also as I understand it's only possible to authenticate with login role. So what's the purpose of the password field in a group role? Or it's possible to authenticate as group role? Then why anyone would need login roles?

Also it looks like I can set both login and group roles in pg_hba.


texts: Merging two selects then sort by and remove duplicates

texts:
I have two select statements. Is it possible to merge the two results together and remove duplicates from the final merged result list (as opposed to treating the queries separately).

Query 1

SELECT DISTINCT
    UM.UMKEY,
    OS.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM (OS LEFT JOIN UM ON OS.HOMEKEY = UM.UMKEY)
LEFT JOIN UM AS MUM ON OS.MAILKEY = MUM.UMKEY
WHERE
    (OS.OS_EMAIL != '')
    AND (UM.INTERNATIONAL != 'Y')
    AND (OS.DECEASED != 'Y')
    AND (OS.GENDER  = 'F')
    AND (OS.MAIL_LIST = 'Y')
ORDER BY UM.UMKEY ASC

Query 2

SELECT DISTINCT
    UM.UMKEY,
    DF.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM ST INNER JOIN DF ON (ST.FAMILY = DF.DFKEY)
INNER JOIN UM ON DF.HOMEKEY = UM.UMKEY
WHERE
    ST.STATUS = 'FULL'
UNION
SELECT DISTINCT
    UM.UMKEY,
    DF.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM ST INNER JOIN DF ON (ST.FAMB = DF.DFKEY)
INNER JOIN UM ON DF.HOMEKEY = UM.UMKEY
WHERE ST.STATUS = 'FULL'
ORDER BY UM.UMKEY ASC


texts:
There are numerous question (in this site and elsewhere on the internet) about how to recover innodb database from *.frm and *.ibd file (the point: when you have the undamaged ibdata1).

Situation:

I assigned to a server where the mysql database is just crashed and can't started. To let them start, i saved the whole /var/lib/mysql folder, then deleted the ibdata1, ib_logfile0, and ib_logfile101, in order to let the mysql server start.

That's worked, MyISAM and some InnoDB table got back to online. However there's some databases where some InnoDb table can't back to operation.

It turned out, that there's no backup from the databases (and they operating 3+ years). Everybody excepted that plesk makes dayli database backup, but actually not (Schrödinger's backup).

After this, it's also turned out that the ibdata1 I saved has been previously overwritten or deleted and a new one created by the mysql (I view the file with hexedit and it's filled fully with zeros). I also looked for the previous file in the lost+found but no result.

There are two database which is crutial to restore, one wordpress site and the plesk database.

Then i copied the saved mysql directory to my machine and started trying to restore the broken tables.

Problem:

I have a crashed MySQL database where the ibdata1 file has benn lost and no database backup available.
On my machine is the same mariadb version installed, so i moved the save mysql directory from the server and started to restore as much as i can from the two databases requested by the owner.

I already wrote the innodb_force_recovery = 6 line into /etc/mysql/my.cnf to help recover the broken tables.
As described here: Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

Then i tried to reimport the *.ibd files as described here:
https://medium.com/magebit/recover-innodb-database-from-frm-and-ibd-files-99fdb0deccad [1]. An that's worked on smaller tables. On other bigger tables like wp_posts when execute ALTER TABLE wp_posts IMPORT TABLESPACE the mysql server crashes:
InnoDB: Trying to access page number 68160 in space 15 space name admin_wordpress/wp_posts, which is outside the tablespace bounds. Byte offset 0, len 16384 i/o type 10.

So the Question: How to restore tables with this kind failure?
What i found:

1) https://twindb.com/recover-corrupt-mysql-database/
For this i need ibdata1. So it's a dead end.

2) Percona data recovery tool for innodb
Theorically this can connect the existing *.ibd to the new ibdata1
However i can't find binary package, the debian package doesn't contains ibdconnect command. I also found the source: https://github.com/jiramot/percona-data-recovery-tool-for-innodb but this can't be compiled. I tried to resolv the compilation an linking errors but this might tacke more time and maybe this solution doesnot work.

3) And the last chance: Rebuild the site. I can extract string from the ibd files strings admin_wordpress/wp_posts.ibd and that way extract the text of the posts on the wordpress page, nut rebiulding a site this way is a Sisyphean task. (And this mathod can't applied to the other database is should restore)

I there any other working method to extract or restore data from *.ibd files?
Even extracting the records to eg.: JSON or BSON or CSV is gonna be good enought to move forward in the recovering process.

[1]: The external site says: drop the database, recreate the empty tables by using mysqlfrm on the broken database *.frm files (this results create table statements). Then execute ALTER TABLE "table" DISCARD TABLESPACE to release the newly created *.ibd file. Move the old *.ibd to the server directory, then execute ALTER TABLE "table" IMPORT TABLESPACE to acquire the old tablespace and therefore recover the data.


texts:
How often does Postgres or Mysql make the fsync call?

Does Postgres or Mysql fsync for every write to the Write Ahead log?
Does Postgres or Mysql fsync for every transaction? so if I do a SQL Insert will postgres or mysql fsync immediately?


texts: How can delete an orphaned temporary table on Mysql Innodb?

texts:
I was issuing an alter table statement and suddenly the server crashed. Now I can not run any alter table command on that table, because it shows me error 1050, and says that a table named #sql1504d already exists.

How can I drop that InnoDB temporary table?


texts: Merge two tables on partly matching column

texts:
I have below 2 tables.

old_name    old_value
Tom1        100
Kate1       80
Jim1        70

new_name   new_value
Tom2       70
Kate2      100
Jim2       80

I want to make a new table look like

old_name    old_value       new_name    new_value
Tom1        100             Tom2        70
Kate1       80              Kate2       100
Jim1        70              Jim2        80

How to do that in Postgres?


texts: Most Effificient Way to Store Ingredients in a Food Database

texts: I have a database that I've called "food" and I'm a bit stumped on how to store the ingredients for a recipe. Should I create a separate table for the ingredients or just have the ingredients insert into the Recipe table as a comma-delimited list? Different recipes will not be using the "same ingredients" in that, they won't be sharing a value such as onion. If 10 ten different recipes have onions, then onion will be in the database 10 times (is that smart?).


texts: Finding the odd row between two tables in Oracle

texts:
I have two tables with the following schema

id, fruit

In TableA the values are like this:

A01, Apple
A02, Orange
A03, Pineapple
A03, Pineapple
A05, Banana

In TableB the values are like this:

A01, Apple
A02, Orange
A03, Pineapple
A04, Pineapple
A05, Banana

How can I reconcile these two tables to return just the duplicate row A03 in TableA? Like a one-to-one comparison of the tuples and returning the odd tuple.

I tried the minus query like below

Select * from TableA
minus
Select * from TableB

But that's not returning the result set I expected.


texts: Remove multiple joins to same table with where

texts:
I am stucked at below situation. Need expert advice.
I have 3 tables, login_details,deleted_login_details and custom_fields as below.
login_details structure = deleted_login_details
custom-fields have : user_id,custom_name,custom_value

I want following result.
I want only those users who fulfilled all where conditions with multiple joins on custom_fields table. Example

select user_id from login_details union deleted-login_details  as login_table
join
custom_fields as f1 on f1.user_id = login_table.user_id
join
custom_fields as f2 on f2.user_id = login_table.user_id
where
f1.custom_name = '1' and f1.custom_value in (1,2,3)
and
f2.custom_name = '2' and f2.custom_value in (4,5,6)

So only those records should come who have custom_name =1 and value as above and custom_name=2 and value in above.
But I want this using only 1 join to custom_fields table.

Like as

So only 1 ,2 should get and no 3 as 3 dont have custom_name =1 record

Any advice?
Thanks in advance.


texts: Making a domain conditional on other values in the table

texts:
How can I define or change a domain based on another attribute value?

F.e. domain for salary is a numeric value > 500. But if a date the person was hired > January 1st, 2013, then the salary must be > 1000.


texts: Query to retrieve index details for a particular column in all tables

texts:
There is a column created_at in most of tables.

I would like to get the details if that field exists in the table and it is not indexed.

Is there any thing can be done with the information_schema to get this checked?

I'm using MySQL 5.6


texts:
I have a database in MS SQL2005 that I can not backup.
Whenever I try to create a backup of the selected database through SQL Mgm studio I get this error:

  The Backup of full-text catalog in not permitted because is not online

This database was crated by detaching another database, copying the data and log files, and attaching the copies as a new database. How I can make my full-text catalog be online again.
I also tried to set USE FULL TEXT SEARCH to false but without success.

Here is my error


texts:
I need to track usage for 1 table, and log how this table is accessed (select, insert, etc.) and by which logins

What is the proper way to do it using SQL Server Audit and Extended Events ?
If there any other ways to track usage, please share it, too

Thanks!


texts:
I have the following query which is taking up to 2 seconds to run on a data set of less than 100K rows.

My SQL is very rusty but this just looks like it's doing a ton more work than it should. Can anyone offer some pointers on where to look to speed this up?

SELECT
    a.AddressID,
    IsPrincipal = CASE
    WHEN EXISTS(SELECT TOP 1 1 FROM dbo.Setting s WHERE s.SettingValue = a.AddressID AND s.SettingDefinitionID = 3 AND s.ProfileID = 1)
                THEN 1
                ELSE 0
            END,
    IsPickUp = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 2)
                THEN 1
                ELSE 0
            END,
    IsSender = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 3)
                THEN 1
                ELSE 0
            END,
            IsDelivery = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 4)
                THEN 1
                ELSE 0
            END,
    IsReceiver = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 5)
                THEN 1
                ELSE 0
            END
        FROM dbo.[Address] AS a
        WHERE a.MFTID = '12345'


texts: Access Query Design View: Show results in two columns from one table

texts:
Lets say I have the follow Tables:

Outfits:
ID | Top Color ID | Bottom Color ID
1,2,1
2,3,4

Colors:
ID | Name
1, Red
2, Blue
3, Green
4, Orange

I'd like to have a query in design view to have the following result

Outfit ID | Top Color Name | Bottom Color Name
1, Blue, Red
2, Green, Orange

In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name"


texts: Getting Waiting for table metadata lock for select statements

texts:
I am using MySQL server 5.6.23 on CentOS and server configuration is like

RAM - 32 GB
Cores - 6 cores
Innodb buffer pool size = 8 GB
Key cache size = 2 GB

We are using only select queries. But all select queries are getting locked and we are observing "Waiting for table metadata lock".

We have enabled slow query log and we observed the queries are getting locked and taking around more than 30 mins for execution. But when we try to execute the same query, it is getting executed in less than 3 secs.

As these queries are getting locked one by one, during the time, around 200 connections are showing same message as "Waiting for table metadata lock".

I had observed the Show engine innodb status output, in the the output also it is showing all the transactions locked.

But I did not used any CREATE/ALTER/INSERT/UPDATE/DELETE statements.


texts: SQL Server 2008 having issues with HAVING clause

texts:
I have a question regarding a report I am currently writing.

I need this query to only show me line items that have < -5 as mins_elapse

Here is the code. I have left out the FROM clause to prevent forms of business identity.

select
    H.ORDER_NUM
    ,H.CUST_NUM
    ,H.CREATION_DATE
    ,H.DUE_DATE
    ,H.ORDER_PRIORITY
    ,H.ORDER_STATUS
    ,H.ORDER_TYPE
    ,H.TRAILER_ROUTE
    ,S.SHIP_ADDRESS_1
    ,S.SHIP_CITY
    ,S.SHIP_STATE
    ,S.SHIP_COUNTRY
    ,S.SHIP_POSTAL_CODE
    ,C.SERVICE_CODE
    ,C.CODE_TEXT
    ,SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2) as manifest_time
    ,CONVERT(varchar(8), CURRENT_TIMESTAMP, 114) as curr_time
    ,--determine the difference between when the order was manifested and the current time and use it in the WHERE clause below
    DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) as mins_elapsed
where
    cust_num = @customer `
    and @service = s.service_code
    and @add1 = S.SHIP_ADDRESS_1
    and @zip = S.SHIP_POSTAL_CODE
    and H.ORDER_STATUS between 20
        and 90
    and DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) <= 5
    and h.large_order = 'Y' `
    and creation_date > 20120701 `
group by
    H.ORDER_NUM
    ,H.CUST_NUM
    ,H.CREATION_DATE
    ,H.DUE_DATE
    ,H.ORDER_PRIORITY
    ,H.ORDER_STATUS
    ,H.ORDER_TYPE
    ,H.TRAILER_ROUTE
    ,PM.SHIP_TIME
    ,S.SHIP_ADDRESS_1
    ,S.SHIP_CITY
    ,S.SHIP_STATE
    ,S.SHIP_COUNTRY
    ,S.SHIP_POSTAL_CODE
    ,C.SERVICE_CODE
    ,C.CODE_TEXT
    ,H.LARGE_ORDER
    ,H.ASSOC_DATE
    ,H.ASSOC_TIME`
having
    DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) <= 5
order by
    H.LARGE_ORDER desc
    ,H.ORDER_STATUS asc
    ,H.ASSOC_DATE asc
    ,H.ASSOC_TIME asc


texts:
I’d needed to stop archive log mode for some reason.

When I tried to change the mode message told me to some file have been missing.

Alter database noarchivelog;

ORA-01111: name for data file 5 is unknown - rename to correct file

ORA-01110: data file 5: 'C:\APP\JAC484\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005'

SQL> select FILE#,ERROR from v$recover_file where FILE#=5;

FILE# ERROR

---------- ------------------------------------------------

 5 FILE MISSING

SQL> select file_id ,file_name from dba_data_files where file_id in (select FILE
    # from v$recover_file where ERROR like 'FILE MISSING' and FILE# = 5);

FILE_ID

----------

FILE_NAME

--------------------------------------------------------------------------------

5

C:\APP\JAC484\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00005

I’d like to recover or delete above MISSING00005 file.

However, there isn’t any physical file.

Does anyone know how to operate?

I've already delete archivelog all; from RMAN.


texts: Wrapping up the repetitive sql statements into transaction in MySQL

texts:
I have a task to cancel more than 90 contracts, and I would like to know if it's possible to do it in one shot, rather than running manually all of them. I have 3 statements which I need to run per each contract. These are the examples below.

When I run the first statement:

UPDATE `dbo2`.`allcontracts` SET `ctr_status`='4' WHERE `ctr_id`='4483';

I'm changing the status of contract with id =  4483. Status will be 4 for all contracts ( more than 90 of them ).

When I run the second statement:

INSERT INTO `dbo2`.`tctrchanges`
      (`chg_ctr_id`, `chg_type`, `chg_date`, `chg_status`,
       `chg_appr_date`, `chg_emp_id`, `chg_req`, `chg_reserved`)
    VALUES
      ('4483', '0', '2017-06-02 11:11:11', '1',
       '2017-06-02 11:11:11', '3', '31', '4483');

I need to insert contract id from the first statement into chg_ctr_id and chg_reserved. I will also need id of column chg_id which is PK AI from this second statement above, to automatically assign it to third statement below in column note_text as URL parameter. ( For example below chg_id=5334 )

 INSERT INTO `dbo2`.`allnotes`
       (`note_dt`, `note_user`, `note_ref_id`, `note_type`,
        `note_text`,
        `note_emp_id`, `note_reserved`)
     VALUES
       ('2017-06-02 11:11:11', 'mikeb', '328515', '2',
        'Automatic Note: Contract was Cancelled ',
        '31', '4483');

As you can notice, the column note_reserved will have the same contract id from the first statement, and column note_emp_id will have the same static value as columnchg_req from the second statement.

All other values can be the same for all contracts.

Any ideas?


texts:
As Replica Sets in MongoDB store data in multiple copies for improved data reliability and high service availability.

My Question is that:

How Can I properly connect a replica set to split write and read with ApsaraDB for MongoDB


texts: Change postgres default behavior for connection to database when dbname not supplied

texts:
According to postgres documentation:

https://www.postgresql.org/docs/10/libpq-connect.html

  dbname
  The database name. Defaults to be the same as the user name. In certain contexts, the value is checked for extended formats; see Section 33.1.1 for more details on those.

for instance, issuing the following command:

psql -U username -h 0.0.0.0 -c "SELECT * from some.table"

will automatically issue the command on a database named 'username' if that database exists.

Is there a way to change the default behavior to require an explicit database name rather than the default behavior?


texts: get datetime difference of a specific time and value of date time column

texts:
I have a datetime column in my mysql table.
I need to get the difference between a definite time everyday say 12.00 pm and the value of that column.


texts: Mysql raising intermittent "system error: 32" errors on connect

texts:
I'm running mysql 5.6.12 on OSX 10.8 and getting an error like this one constantly:

ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32

Once it starts, the error occurs no matter how I try to connect to mysql (console, my Ruby app, etc). Restarting the server is the only thing I've found that fixes it, and then it happens again within an hour, sometimes much more quickly.

I originally had a homebrew install, and it was having this problem. I then deleted that and installed with the official mysql .dmg package and a fresh database. That helped for a while, but now the problem has started again.


texts: What is the best way to reload data in MongoDB

texts:
I am benchmarking MongoDB using YCSB framework using the latest drivers.

During the benchmark, I change the cluster setup (adding/removing shards) extensively and delete/insert workloads in the order of 5GB regularly. To try and make this process quicker I have tried two approaches,

In the first approach I use db.collection.remove() function to remove documents from the data which takes around 20 mins to remove 5GB of documents from one shard. This method preserves the indexes, which results in an increased insertion throughput.

In the second approach I use db.collection.drop() function to remove documents from the data which almost instantly removes the data. However, insertion takes much longer due to balancing the data across shards.

Is there a better approach to do these tasks? If not, which one of the two
is recommended?


texts:
I have not started MySQL on my Mac Mojave in while. When today I ran mysql.server start I got the following error:

2019-10-03T21:42:11.410763Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-10-03T21:42:11.464825Z 1 [ERROR] [MY-013090] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9
2019-10-03T21:42:11.464850Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2019-10-03T21:42:11.465724Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2019-10-03T21:42:11.466028Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-10-03T21:42:11.466203Z 0 [ERROR] [MY-010119] [Server] Aborting

My online research suggested that I need to rename ib_logfile1 and ib_logfile1 in my /usr/local/var/mysql directory.

I did that and then I got the following error:

2019-10-03T21:27:39.506301Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2019-10-03T21:27:40.301335Z 1 [ERROR] [MY-013168] [InnoDB] Cannot upgrade server earlier than 5.7 to 8.0
2019-10-03T21:27:45.304727Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2019-10-03T21:27:45.305119Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-10-03T21:27:45.305571Z 0 [ERROR] [MY-010119] [Server] Aborting

What is going on here? (BTW, I thought I already had mysql8 installed but I am not sure anymore)


texts:
I saw this error when trying to perform the following insert into a PostgreSQL 8.4.20:

java.sql.BatchUpdateException: Batch entry 12 insert into perf_raw_2017_03_16 values ('3129', '182', '818', '2017-03-20 16:01:17.507000 +00:00:00', '4.9E-324') was aborted.  Call getNextException to see the cause.
org.postgresql.util.PSQLException: ERROR: "4.9E-324" is out of range for type double precision

However, when I try the same insert on a different machine with PostgreSQL 8.4.4, the insert finishes fine. When I do a select to verify that the value is there, it also finishes fine:

select * from perf_raw_2017_03_20 where guid_key=3129 and property_key=182;

 guid_key | property_key | instance_id_key |       time_stamp        |         value
----------+--------------+-----------------+-------------------------+-----------------------
     3129 |          182 |             818 | 2017-03-20 16:01:17.507 | 4.94065645841247e-324

The type of the column is double precision, which, according to the documentation, is "15 decimal digits precision". The error makes sense, I am indeed outside of its range, so I am not very surprised by the error. Here are my questions though:

The binary format of the DB does not change with minor releases. Why did this even work in 8.4.4 and not 8.4.20?

I did not find anything in the changelog that would explain this:
https://bucardo.org/postgres_all_versions.html

Why does the DB still allow me to insert the value if I use a select to get it from an existing record?

I will probably post an answer to this question based on what I observed, but it would be cool if somebody could provide a deeper insight into this issue.


texts:
I have a DB which holds very simple documents called "Item":

{_id:<ObjectId>, playerId:int, isRead:bool, content:string}

containing about 80M documents. Each Item belongs to a certain player, defined by a monotonically incrementing playerId. Each player has up to 1,000 documents in the collection.

I want to shard the collection based on hashed playerId, since the read queries are based on playerId. This way, I believe I will be able to get to the right node in the cluster efficiently, then get the relevant documents (worst case, 1000) and retrieve them.

I was advised not to use hashed playerId because it's not unique, and might retrieve a large amount of documents per query, since isRead is not included in the index. Queries that retrieve by playerID and isRead might impose an efficiency issue.

Is this indeed a problem? Are there any other considerations to think of?


texts:
Suppose we have the following situation:

We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A.

Now, everything is ok when we simple insert a row in the table, but what about situations where we insert data through a transaction?. Will the trigger wait until all transactions statements run successfully, or it will be fired the moment it recognizes the insert?. If the trigger fires immediately when it recognizes the first insert, what will happen if the transaction fail on the last row?. Is there some mechanism for that situation?.


texts: Properly configuring Oracle Charset and NLS parameters

texts:
I'm trying to figure out the reason why Oracle SQL Developer shows question marks/rubbish on non-latin symbols. Currently I have 2 DBs on separate severs/instances, one of which works fine, and the other one breaks encoding as written above. Both were checked with same SQL Developer with same environment WITHOUT NLS_LANG set.

Here are NLS* tables for each case:

Normal)

select * from nls_database_parameters;
NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CHARACTERSET    CL8MSWIN1251
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_RDBMS_VERSION   10.2.0.3.0

select * from nls_instance_parameters;
NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE

Strange)

select * from nls_database_parameters;
NLS_RDBMS_VERSION   12.1.0.2.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS    BYTE
NLS_COMP    BINARY
NLS_DUAL_CURRENCY   $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT    BINARY
NLS_DATE_LANGUAGE   AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR    GREGORIAN
NLS_NUMERIC_CHARACTERS  .,
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_CHARACTERSET    CL8MSWIN1251
NLS_ISO_CURRENCY    AMERICA
NLS_CURRENCY    $
NLS_TERRITORY   AMERICA
NLS_LANGUAGE    AMERICAN

select * from nls_instance_parameters;
NLS_LANGUAGE    RUSSIAN
NLS_TERRITORY   RUSSIA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE

Session params)

select * from nls_session_parameters;
NLS_LANGUAGE    ENGLISH
NLS_TERRITORY   UNITED KINGDOM
NLS_CURRENCY    ?
NLS_ISO_CURRENCY    UNITED KINGDOM
NLS_NUMERIC_CHARACTERS  .,
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   ENGLISH
NLS_SORT    BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT  HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY   €
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE

Changing strange's instance NLS-params didn't do the trick. Data integrity is verified - a webapp that connects to this DB works fine if NLS_LANG is explicitly set to RUSSIAN_RUSSIA.CL8MSWIN1251. I just can't figure out why FIRST database works fine as is and the SECOND has to be explicitly configured client-side, even if I change instance settings. What am I missing?


texts: file not found - how to remove its configuration?

texts:
I run oracle 10.2.0.5.

I have the following:

SQL> select file#, status, error, recover, tablespace_name, name
  2  from v$datafile_header
  3  where recover='YES'
  4  or (RECOVER IS NULL AND ERROR IS NOT NULL);

     FILE# STATUS
---------- -------
ERROR                                                             REC
----------------------------------------------------------------- ---
TABLESPACE_NAME
------------------------------
NAME
--------------------------------------------------------------------------------
        15 OFFLINE
FILE NOT FOUND

It's the remnant of a tablespace which was used for some synchronization using streams.

How can I "delete" this file from oracle's memory?

Thanks


texts: Advice to set minimum preferable value for MEMORY_TARGET

texts:
I have a server having 90GB of RAM.

I have set MEMORY_TARGET to 60GB, because OEM showed me the following graph:

Which tells that, if you set the parameter to the value of 60GB, the performance will be the maximum. After 60GB, performance does not change.

Now we want to migrate our server to the virtual machine and my sysadmins want me to say the minimum preferable value for RAM to be allocated for the new server.

For that I need determine the minimum preferable value for MEMORY_TARGET. How to calculate it?


texts: Are JOINs on non-indexed columns faster than sub-queries?

texts:
I know JOIN on indexed columns are faster than sub-queries in a query result.

I am confused if : JOINs on non-indexed columns are also faster than sub-queries or not.


texts:
In my envoriment I am getting the following erro:

ORA-15032: not all alterations performed
ORA-15017: diskgroup "OCR" cannot be mounted
ORA-15040: diskgroup is incomplete

The problem is: I already has droped this diskgroup (I've changed the OCR diskgroup to another diskgroup OCR_NEW). I have two instances, in the first instance it's everything ok, but in the second instance I get this erro.

Where can be the problem? I didn't find anything.

I already tried to disable before to try remove in srvctl command, but not work as well.

[oracle@server2 trace]$ srvctl disable diskgroup -diskgroup OCR
PRCA-1000 : ASM Disk Group OCR does not exist
PRCR-1001 : Resource ora.OCR.dg does not exist


texts: MySQL database takes too long to load page first time

texts:
MySQL database takes too long to load page for the first time, especially if I leave the page for an hour or so. Subsequent refreshes after the first one, load immediately. The first time can take 10 seconds or more.

I am just using below web page for simple testing -

select count(*) from $tablename

Could the dB be on idle or something (its seems like it is) and how can I get the first page loading quickly. Could it be a caching setting for the dB? New to this work. Thanks for any help.


texts: Running Triggers on Slave with Row-based Events in MySQL >=5.6

texts:
We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys

If one uses statement-based replication, they can just create the required triggers on the slave. The slave will run the statements from the binary log, which will cause the triggers to be invoked. With (the suggested) row-based, quote:

  If under row-based replication the slave applied the triggers as well
  as the row changes caused by them, the changes would in effect be
  applied twice on the slave, leading to different data on the master
  and the slave.

https://dev.mysql.com/doc/refman/5.6/en/replication-features-triggers.html

Is there a way to run triggers on slave only with row-based replication, similar to what mariaDB have?


texts: I would like to export SQL data to an XML file so it can be read in COBOL. I am working with SQL Express 2008 r2.


texts: Exclude certain categories from a flag column

texts:
I have a table P that has a Categories column.
The Categories column is a bitwise flag; I have a Categories Table and if a row from P belongs in 2 different categories(say with Id 1 and 3), its column Category(or IntValue) would be 5, a sum of the first and third bit.
I also have a Exclusion table by Ids in P, for example, I can say that I want to exclude from a search on P with Id=1, every category of Id=2
I would like to select All P, excluding categories in the exclusion table.
For the moment I have this condition :
p.Categories & @SUM_OF_EVERY_CATEGORY_INT_VALUE
  - (SELECT COALESCE(SUM(IntValue), 0)
FROM CategoryExceptionByUser
  LEFT JOIN PCategories
    ON CategoryExceptionByUser.PCategoryId = PCategories.PCategoryId
WHERE UserId = @CURRENT_USER) > 0

Which works only if you add every category from a product in the exclusion table. I would like it to work if I add only one of the categories to the exclusion table
I am sorry if it's not clear, english is not my first language. If you need any more detail, please ask away
Here is the link for the example: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1d01677893041ecfd6054e63e5241bad
The query on table P returns Ids 0, 2 and 4, I would like it to return Ids 1, 2 and 3


texts: Speed inserts in database with a table with +47 million data per each year, containing more than 10 years?

texts:
I'm moving my database to mysql but there is one table that is pretty big (more than 47 million rows for each year) and it takes years to insert all the data.

I already tried everything I found in the forum, partitioning, index, fulltext...and is true it works better but its far away from efficient.

For giving more background: The data base is about hydrological data recorded during the past ten years. In my data base there are three tables: one keeping the objects to be saved, another to save the sensors of each object and the last one and the one which is causing the big problem is where I keep hourly all the information collected for each sensor (this table will have 47 million data for each year int the database)

I'm using .NET for the transactions.

Is there any way I can performed the inserts in less than 10 hours?

Thank you very much!


texts: Delete index while query is running, is it possible?

texts:
I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still running, and would this speed things up?

Actually, if this changes anything, it is a uniqueness constraint over three columns that creates the index.


texts:
I am trying to measure various metrics for a stored procedure - that we are tinkering with and using under different loading (i.e. size of tables).

So I have drawn up this list of metrics to plot against the other things we are changing.

These are

disk I/O
Elapsed time
CPU time
Consistent gets
sort memory/disk used
Number of rows accessed

Is there any others that might be useful to capture?

Also when capturing this information it will have an impact on the performance. Is there any documentation as to how much this is the case as I was unable to find any


texts:
i have two tables offer1 and offer2

The two tables are

Offer1
Offer2ID|OfferName|OfferExpiryDate

Offer2
Offer2ID|OfferName|OfferExpiryDate

now i have third table which keeps record

offersentdetail
offerID|senttime|sentCount|UserID

now i want output sent on Specifc userID

Output will be like

UserID | OfferID | OfferName


texts: What is causing my Transaction log to grow

texts:
The TLOG in my SQL Server 2008 is growing faster than usual.
The DB has a Full recovery model and we have a TLOG backup every hour.

Can you please write me or refer me to link that describes a way to identify what is the session/process/query that causes the TLOG growth.

I thought that if I'll know WHEN the growth is occurring, I'll be able to detect the job/process that causes the growth.
I've created a job that saves the output of sp_helpdb 'dbname' to a table, but even after knowing the right time, I wasn't able to find the sordid process.

I would appreciate your help in this matter.

Thanks in advance,

Roni.


texts: Processlist show most of the queries are sending data for select query

texts: I have faced some problem like sending data state in processlist. In my application I used parallel processing which send 50 count at a time, and that will do some MySQL database operation (select and insert), but after few minutes nothing happened. Then I see in database with SHOW PROCESSLIST; most of the query is in SENDING DATA state with 1001 threads. my max query size is 1000. I decrease the size 50 to 10 but no luck. Can anyone suggest me how to solve this problem.


texts:
Our database is running on Postgres 9.3. We have a table called notifications.

Notifications contains id, type, triggering id, source id, and some other information.

What I am trying to do is select the notifications and then group them on their source ID.

So if I have the following:

id     type     source_id     triggering_id
1     comment     1234        4567
2     comment     1234        0123
3     comment     1234        5432

I would like to get the following back:

source_id, triggering_id(s)

I don't know if this is even possible. I'm pretty new to the DBA stuff.


texts:
I created an SSIS package to iterate through all databases on an SSAS instance. It grabs the DatabaseId using AMO in a vbscript, then generates the XMLA to backup each database.

Most databases backup with no problems, but when the ForEach gets to one particular database, the Execute Analysis Services DDL Task seems to hang. The .abf file is created, but the SSIS package doesn't seem to get the response that the command is completed. It's like the backup process is stuck on the file.

When I backup the database using SSMS dialogs, the same problem occurs. I verified that other databases backup successfully using this method.

I should note that the database in question is 0.0MB in "estimated" size according to the db properties in SSMS. Is the database truly empty, or could this be a sign of corruption?

Why doesn't the backup process complete? I get no error messages, just a long-running process that does not end. Is there anything I can do to find the root cause?


texts: Comparison between A non-clustered primary key and a covering index in terms of performance

texts:
I have a table which contains a non-clustered primary key. I intend to remove this primary key and create a unique Covering Index on the same column instead.
Then The table won't have a primary key, but a unique covering index.
I searched google but could not find a relevant topic. Is that fundamentally wrong? or it's ok to be done.
Update:
Why do I need to convert this index to a covering index?
Because this is the primary key and I have many select queries based on this key. At the moment for each select, there will be a need for a RID Lookup because this is a non-clustered key. If I convert it to a covering index, this RID Lookup will be gone for those select queries and hopefully there will be better performance (at least the excecution plan says that).
Of course integrity is also intended and that's why I will make it a unique covering index. The primary key is a uniqueidentifier, generated by the application (which explains why we don't make it clustered)..
What I am wondering is if that has any negative effects on anything else.
Execution Plan Before:

Execution Plan After:

FYI, the primary-key is a uniquidentifier which is generated in the code (application).  In load tests we came up with the conclusion of making it non-clustered.


texts:
We have a monitoring tool that can check if Microsoft SQL Server jobs have run successfully. What I want to do is create a user that has as little permission as possible but it must be able to watch the history log to see if the job completed.

What role should I assign the user?

Edit:
The servers are Microsoft SQL Server 2005 to 2012


texts: Why is to_char left padding with spaces?

texts:
When ever I use 099 indicating 3 digits 0-padded, I'm getting spaces on the left side.

SELECT '>' || to_char(1, '099') || '<';
 ?column?
----------
 > 001<
(1 row)

Why is to_char left padding here? Why are there leading spaces?


texts:
CREATE TRIGGER audit_proc_tr
  AFTER INSERT OR UPDATE OR DELETE
  ON "log".hi
  FOR EACH ROW
  EXECUTE PROCEDURE "log".audit_proc(argument);

CREATE OR REPLACE FUNCTION fn_configpurchaseorder(configpurchaseorder, xmode text, xuserno integer)
  RETURNS text AS

 END;

I want to pass xmode is a argument of trigger


texts: What is the memory/cpu/other requirements to run PostgreSQL database server efficiently ? Is a way to appoint a hardware configuration for some sort of requirements - for example number of users, number of queries etc ?


texts: Sending SQL Server query as CSV via e-mail

texts:
Currently I'm using SSIS w/ a hacky solution to get around it not supporting auth on SMTP servers.

Is there a better way I can save a query to a CSV file, then mail it as an attachment?

Thanks


texts: How to merge two columns with incomplete information?

texts:
I have the following schema in my MySQL database, where I have read access:

table1 (customersid*:int, zip1:int)

table2 (customersfid*:int, zip2:int)

Where customersid references some customers and zip1 and zip2 corresponds to zip code area of that particular customers . Ideally the tables have similar information. However, in my tables zip code is sometimes incomplete:

customersfid is the foreign key of customersid:

Minimal Example:

       table1                      table2
customersid  zip1          customersfid      zip2
1            1001                 1          1001
2            700                  2          700
3            20                   3          20
4            60                   4          60
5            0                    5          30
6            NULL                 6          10
7            33                   7          NULL
8            25                   8          0
9            55                   9          66
10            0                   10         0

I would like to merge them into one column with the following idea (always in favor to zip1):

customersid  zip1                          customersfid  zip2
1            1001                           1           1001
2            700                            2           700
3            20                             3           20
4            60                             4           60
5            30           <-30              5           30
6            10           <-10              6           10
7            33                             7           NULL
8            25                             8           0
9            55         always keep zip1    9           66
10            0                             10          0


texts:
Statement 1 : select top 1000 pmch.Columnname1
Statement 2 : select top 1000 pmch.*
Statement 2 executes quicker the  Statement 1.
I know that using "SELECT * " will affect performance so should use column names instead of " * ".
But, here it works differently.
Can anyone suggest under which condition these type of issue occurs?


texts: Memory exhausting when accessing large files of JSONB columns

texts:
I have a table with one of the column data jsonb, it has proper index on the column for filter condition but some times query is running too long and some times DB crashing because of out of memory immediately when starting of the query.

has the table stats -

Table size    51 MB,
Toast table size  4187824128,
Indexes size  137 MB.

Environment:

Postgres v9.6
8 CPUs
32GB RAM on Redhat Linux

the user query is straight forward and it does extract a few fields from the JSON file.

Table DDL:

CREATE TABLE jnb_tab (
load_dttm date,
updt_dttm date,
data jsonb
)

User query:

SELECT (  data -> 'info'::text) ->> 's_time'::text AS info_starttime,
    (  data -> 'info'::text) ->> 's_timeL'::text AS info_starttimel,
    (  data -> 'info'::text) ->> 'convId'::text AS info_convid,
    (  data -> 'info'::text) ->> 'status'::text AS info_status,
    'alltypes'::text AS type,
    json_array_elements(jsonb_array_elements_text(  data -> 'incident'::text)::json -> 'alltypes'::text) ->> 'i_name'::text AS intentname,
    json_array_elements(jsonb_array_elements_text(  data -> 'incident'::text)::json -> 'alltypes'::text) ->> 'c_Score'::text AS confidencescore,
    json_array_elements(jsonb_array_elements_text(  data -> 'incident'::text)::json -> 'alltypes'::text) -> 'versions'::text AS versions,
    jsonb_array_elements_text(  data -> 'incident'::text)::json ->> 'messageId'::text AS messageid
   FROM jnb_tab

Query planner:

Seq Scan on public.jnb_tab as jnb_tab (cost=0..17049269.29 rows=3402810000 width=288)

And here is josn file structure:

{
"info":{},
"incident":[
{
"messageId":"text",
"alltype":[],
"selectedtype":{}
},
{
"messageId":"text",
"alltype":[],
"selectedtype":{}
},
{
"messageId":"text",
"alltype":[],
"selectedtype":{}
}
],
------- want to split here
"campa":{},
"exhange":[],
"monitoring":{},
"ASd":{},
"interact":[],
"r_Time":{},
"sC":[],
"Scores_m":[],
"Records_m":[],
"Statuses_m":[],
"aP":[],
"cS":[],
"cP":[]
}

Any suggestions to fix this issue

and can we create partition on JSON key value "incident": ?


texts: MariaDB views: I want to replace repeated multi-table joins in my queries with a view - are there any issues to watch out for?

texts:
Rather than
SELECT a.pk, b.pk, c.pk, d.name

FROM a
JOIN b on b.pk = a.fk
JOIN c on c.pk = b.fk
JOIN d on d.pk = c.fk

I can do
SELECT a_pk, b_pk, c_pk, d_name

FROM view_a_b_c_d

I have a lot of this sort of thing through my code.
I've done a performance test, and the differences seem to be negligible, and I feel it would greatly tidy my codebase up and remove a lot of repetition.
But before I commit to that (as it'd be a big change with a lot of work and testing), I want to check that this IS a good thing to do.  I didn't study computer science and have no formal DBA training.  I'm also a sole dev working on my own closed-source product.  So I don't get much input from the outside world, unless I strike out and ask for it.
Thank you - any opinions/experience appreciated.


texts: SQL Server --- How to use a database alias for quick coding?

texts:
Hopefully I'm using this term correctly.
Okay as most people know if you have long table names like mylongschema.longtablename ...
You can alias the table for quick reference such as mylongschema.longtablename ml ... select ml.* ... etc.
Obviously the same goes for fields/ columns...
I'm wondering if you can alias databases the same way?
I have queries that frequently reference one of three long-named databases.
Is there a way to alias these 3 database as db1, db2, db3 ... or whatever ... at the top of my Query Editor in SSMS .... so I can just type db1.longschema.longtablename ....
Thanks!


texts: Oracle function based index with empty function name

texts:
I'm trying to understand this syntax in the creation of an index in a database I'm using:

CREATE  INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3'));

After searching for a while, I could not find any example using this syntax, and how it is different from a normal multi column index:

CREATE  INDEX IDX_2 ON MYSQ.MYTABLE ('ID_COLUMN1', 'ID_COLUMN2', 'ID_COLUMN3');

I figured out it is a function based index, analyzing the following example I was trying to assemble:

CREATE TABLE t (a number, b number, c number);

insert into t values (1, 2, 3);
insert into t values (4, 5, 6);
insert into t values (7, 8, 9);
insert into t values (10,11, 12);
insert into t values (13, 14, 15);
insert into t values (16, 17, 18);

CREATE INDEX IDX2 ON t (a, b, c);
CREATE INDEX IDX ON t (('d'), ('b'), ('c'));

select * from USER_INDEXES;

INDEX_NAME  INDEX_TYPE  TABLE_OWNER     TABLE_NAME  TABLE_TYPE  UNIQUENESS  COMPRESSION     PREFIX_LENGTH   TABLESPACE_NAME     INI_TRANS   MAX_TRANS   INITIAL_EXTENT  NEXT_EXTENT     MIN_EXTENTS     MAX_EXTENTS     PCT_INCREASE    PCT_THRESHOLD   INCLUDE_COLUMN  FREELISTS   FREELIST_GROUPS     PCT_FREE    LOGGING     BLEVEL  LEAF_BLOCKS     DISTINCT_KEYS   AVG_LEAF_BLOCKS_PER_KEY     AVG_DATA_BLOCKS_PER_KEY     CLUSTERING_FACTOR   STATUS  NUM_ROWS    SAMPLE_SIZE     LAST_ANALYZED   DEGREE  INSTANCES   PARTITIONED     TEMPORARY   GENERATED   SECONDARY   BUFFER_POOL     FLASH_CACHE     CELL_FLASH_CACHE    USER_STATS  DURATION    PCT_DIRECT_ACCESS   ITYP_OWNER  ITYP_NAME   PARAMETERS  GLOBAL_STATS    DOMIDX_STATUS   DOMIDX_OPSTATUS     FUNCIDX_STATUS  JOIN_INDEX  IOT_REDUNDANT_PKEY_ELIM     DROPPED     VISIBILITY  DOMIDX_MANAGEMENT   SEGMENT_CREATED
IDX2    NORMAL  USER_4_AE9EA5   T   TABLE   NONUNIQUE   DISABLED    (null)  FIDDLEDATA  2   255     65536   1048576     1   2147483645  (null)  (null)  (null)  (null)  (null)  10  YES     0   1   6   1   1   1   VALID   6   6   2019-08-19T22:06:23Z    1   1   NO  N   N   N   DEFAULT     DEFAULT     DEFAULT     NO  (null)  (null)  (null)  (null)  (null)  NO  (null)  (null)  (null)  NO  NO  NO  VISIBLE     (null)  YES
IDX     FUNCTION-BASED NORMAL   USER_4_AE9EA5   T   TABLE   NONUNIQUE   DISABLED    (null)  FIDDLEDATA  2   255     65536   1048576     1   2147483645  (null)  (null)  (nul

http://www.sqlfiddle.com/#!4/ae9ea5/5

What is the purpose of this index?


texts: Is there really no way in SQL to update all fields in a table without explicitly typing field names

texts:
As indicated in this post Update all columns from another table?

I need to update a table with 50 columns with values from another table, and then insert new records from a different table (with 50 columns).  I really don't want to list 50 name-value pairs (or use the bulk updating syntax here Bulk update of all columns, which SQL Server doesn't seem to like anyway).

So, I can conclude one of the following:

There IS a way after all,
It's ridiculous to have a table with 50 columns, or
Database Admins are masochists.


texts:
I have a few tables with surrogate keys (SERIAL PRIMARY KEYs unique to each tables).  These tables are used by other tables, with references to these keys.  My usage of these is quite simple :

select * from table_a;
 oid | data
-----+------
  1  |  foo
  2  |  bar

select * from table_c;
 a_id | b_id |   misc
------+------+----------
  1   |   1  | whatever
  2   |   1  | blah

There are no SQL constraints or whatever.

I'm about to make some large modifications to my DB (e.g. addition and merging of tables), so I'm wondering if there is an easy or automatic way to reassign OIDs in related tables ? [Edit : I guess there is no reason such an automatic way should exist, since those keys are assigned manually and not by the DB itself.]

I don't like the idea of having "holes" in my surrogate keys so I'm thinking about "leveling" them (probably using temporary tables with corresponding new/old keys), but is it worth the trouble ?

I'm using PostgreSQL 9.3.5.


texts: Sql Server Server 2008 r2 Installation Error

texts:
I am installing SQL Server 2008 R2 Enterprise Edition on Windows Server 2012. Unexpectedly I come across the error shown below. Installation successfully completes.

Can someone help me identify the problem and resolve the issue.


texts: Remove particular table from being replicated in a Multi-Master replication

texts:
I have followed all the steps in this link to set-up MySQL Master-Master replication for two database servers.

The master-master replication works fine.

Here is my my.cnf file, which contains some additional configuration than that present in the above link for auto-increment:

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

log_bin = "/var/lib/mysql/mysql-bin.log"
binlog_do_db = my_employee_database

log-slave-updates

expire_logs_days = 20

binlog_format = mixed

Similarly this is set-up in the other node with some changes.

my_employee_database has 4 tables:

my_employee
my_company
my_address
my_audit.

Question

Is there a way I can ignore or stop or remove the table my_audit from being replicated among the two server nodes and have the replication run normally for the other 3 tables as it is?

Environment

Database: MySQL 5.6.25
OS: CentOS 6.5 64 bit


texts: Need help phrasing regular expression to find particular T-SQL pattern

texts:
I know this is a Database Forum, but since the text I am trying to find is SQL-specific, I am hoping that somebody can point me in the right direction.  I need to search code to find places where the INSERT queries are written without explicitly specifying table column names.  For example, I need to find things like this:

INSERT tablename SELECT col1, col2 FROM anothertable

INSERT INTO tablename VALUES (1), (2)

In case you wonder, when the column names are not explicitly specified after the table name, the SQL Server will implicitely assume that all of the columns are listed.  As a result, if you add a new column to the table, your code will break.

The regex pattern I envision would look for the following:

INSERT followed by any characters, followed by either SELECT or VALUES where there is no occurrence of closing parenthesis ")" before the SELECT/VALUES.

I was thinking about alternative way to phrase it, but I am not sure it's easier:

INSERT or INSERT INT followed by space/tab/cr/lf, followed by any number of characters, followed by space/tab/cr/lf, NOT followed by open parenthesis "("

I appreciate any pointers.


texts:
I was given an excel file with account numbers in them and told to write a query that returns the account numbers in the list that meet a certain criteria. While I was able to write the query in general without problems I don't know how to reference the excel document in the query. I worked around it by copy and pasting the account numbers into a

WHERE ACCOUNT_NUMBER IN (...)

The problem is that the amount of account numbers I had was over 6000 while the max numbers in a IN list is 1000. I ended up doing it piecewise but there must be a better way.

Any suggestions?


texts: Querying a database efficiently for a huge chunk of data

texts:
Here is my query

SELECT *
FROM
    (
        SELECT a.*, rownum rnum
        FROM
            (
                SELECT id, data
                FROM t
                ORDER BY id
            ) a
       WHERE rownum <= HIGHER
    )
WHERE rnum >= LOWER;

I have a huge set of data and hence I am getting parts of it one at a time, say from 1 to 100 in first attempt, 101-200 in the second attempt and so on.

If I execute the above query it will do the following piece for each portion of data:

SELECT id, data
FROM t
ORDER BY id

I think it is pretty inefficient to do it for each part.  Can't I do something like order my data set once and apply a rownum?  Will it be of any use at all?


texts: Resolving ORA-01033 "initialization or shutdown in progress"

texts:
I followed the instructions at http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php to create a database export.

I used:

expdp scott/tiger@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

to create the export. However I get this error message:

UDE-00008: operation generated ORACLE error 12154
ORA-01033: ORACLE initialization or shutdown in progress

How do I resolve this issue?


texts: Postgres table growing on massive updates

texts:
I have an issue with my postgres database, I'm running massive update queries (1000 per second) to a single table (with 3000 entries) and I can see that the size of that table is growing and growing up.

The way that I'm restoring the size is making these queries:

VACUUM FULL [TABLE];
REINDEX TABLE [TABLE];

This will return the size of my [TABLE] to its original size.

That's working fine for me, but the problem is that I have to shutdown the application server to run those queries; if I run them with the server alive, the [TABLE] size is reduced, but just a bit.

My problem is reduced to 2 questions:
1. Why this table is growing if I'm just making updates?
2. How could I solve this problem without shutting down my application server?

Thank you very much for your time and your responses.

Update: Postgres versions are from 8.3.x up to 9.x

Update-2: The problem was because an IDLE connection was being kept alive by some application, by killing the connection and performing a vacuum all the problems were solved.


texts: mysql, changing tables from myisam to ndb

texts:
Gents,
  Converting a table from myisam to ndb via:

ALTER TABLE piwik_log_profiling ENGINE NDBCLUSTER;

This will fail with:

BLOB column 'query' can't be used in key specification with the used table type

Now, if the 'query' field is changed from text to varchar, then I get the following:

Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Original table:

| log_profiling_backup | CREATE TABLE `log_profiling_backup` (
`query` text NOT NULL,
`count` int(10) unsigned DEFAULT NULL,
`sum_time_ms` float DEFAULT NULL,
UNIQUE KEY `query` (`query`(100))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |


texts:
Is it possible to create a query and ask the following?
I have two tables (each one has one column with data) and I want to obtain as a result all data from the first table MINUS the data from the second table.


texts: SQL Server - Copy data from restored version to live database table

texts:
I am using SQL Server 2014.
I have a database called LabDB.
I have a issue where some data was deleted in the database.
What I like to do is to restore the backed up version of the database in Object Explorer and then
copy the missing data from the backed up version to the live database.

Can somebody please guide me on how to do this.


texts: How to check if Alter view/stored proc script has run successfully

texts:
For the database deployments, I call all the db schema changes in a batch file and run them on the target database.

Sometimes we have scripts for altering the views or stored procs. I'm wondering what is the best way to control whether the view/sp has been altered successfully or not and report it via a message in the output log file.

For example below is the pseudo code of what I'd like to implement:

IF
(
ALTER VIEW vw_abc
.
.
.
.
) Failed THEN

PRINT 'ALTER view vw_abc Failed'.
ELSE
PRINT 'ALTER view vw_abc Completed Successfully'
END

The output message will be recorded in the script log file which is generated by the batch file that has run the script.

If there any error code that we can check to do that? Any idea?

Thanks.


texts: Query column A, order by B - is index needed if A and B together are the PK?

texts:
I have a table (in Postgresql) which has a primary key on columns a and b. I want to do a query:

select * from table where a='XXX' order by b;

Does the existing primary key serve to help with the performance of that query?

Can I improve performance by making one or more other indexes eg. an index on a and/or an index on b?


texts: REPLACE(orig,match,replacement) multiple matches in one query

texts:
I'm trying to categorize Magic The Gathering cards. Here is a simplified version of what I'm doing/trying.

Is it reasonable/doable to get my desired output from mysql?  If so, how?

Or am I going to need to use some php? (which I can figure out pretty easily) I'd rather do it through a query if i can.

I run:

SELECT REPLACE(orig.text, matches.text, matches.replacement)
    FROM orig
JOIN matches
    ON orig.text LIKE CONCAT('%',matches.text,'%') ;

I want:

| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]
| [who] [action] really [speed]

In the actual app, there would be another column, like the card name.

I actually get:

| REPLACE(orig.text,matches.text,matches.replacement) |
+-----------------------------------------------------+
| [who] run really fast                               |
| [who] run really slow                               |
| [who] shop really fast                              |
| [who] shop really qu[who]ckly                       |
| [who] shop really slowly                            |
| i [action] really fast                              |
| i [action] really slow                              |
| i run really [speed]                                |
| i shop really [speed]                               |
| i shop really [speed]                               |
| i shop really [speed]                               |
| i [action] really fast                              |
| i [action] really quickly                           |
| i [action] really slowly                            |

orig table

| i run really fast     |
| i run really slow     |
| i shop really fast    |
| i shop really quickly |
| i shop really slowly

matches table

| text    | replacement |
+---------+-------------+
| i       | [who]       |
| run     | [action]    |
| fast    | [speed]     |
| slowly  | [speed]     |
| quickly | [speed]     |
| shop    | [action]    |


texts: MySQL Trigger On UPDATE "Unknown Column" Error

texts:
I want to log a table when it is updating a row in which the value of the "insertDate" or "updateDate" column is older than 3 months.

I wrote this trigger:

DELIMITER ///
CREATE TRIGGER `trig_before_up_dad_records` BEFORE UPDATE ON `dad_records` FOR EACH ROW
                                                                               BEGIN

SET @beforeThreeMonth = DATE(NOW() - interval 3 month);

IF (ISNULL(OLD.updateDate) OR UNIX_TIMESTAMP(OLD.updateDate) IS NULL) THEN
    SET @lastDate = DATE(OLD.insertDate);
ELSE
    SET @lastDate = DATE(OLD.updateDate);
END IF;

IF(UNIX_TIMESTAMP(lastDate) < UNIX_TIMESTAMP(beforeThreeMonth)) THEN
    INSERT INTO three_month_ago_dad_records SELECT OLD.*;
END IF;
END;
///

When update a row where the updateDate time 2015-04-09 (or anything older than 3 months) I get the following error:

  /* SQL Error (1054): Unknown column 'lastDate' in 'field list' */

However, I didn't write lastDate as a column. Where is my error?


texts: Is there a query to set a table's engine to innodb if it's not already?

texts:
I can set a (MyISAM) table's engine to InnoDB using the query:

alter table tablename engine=InnoDB

which takes some time to complete for large tables. It appears though that if I run the same query again on the same table, it takes some time again (much less but still considerable). I would expect the query to be instantaneous, since the engine is already set to innodb.

My questions are:

Is there a single query that conditionally sets the engine, for example

alter table tablename engine=InnoDB <if not already innodb>
Why does the second query have such a delay? (out of curiosity mostly)


texts: What utilities can I use to simulate I/O load for a DB restore and DBCC check?

texts:
I'm trying to figure out the peak load I can sustain on new hardware I procured for validating backups and performing DBCC checks. I've been using Crystal Diskmark to get throughput stats which helped me benchmark sequential I/O for the copy/restore tasks. I'm having trouble gauging how much random I/O I can sustain for the DBCC check. I'm thinking about using iometer and sqliosim but want to know config would work best to simulate a DBCC check.

The hardware I'm testing consists of one R720 with dual E5-2609s for 8 cores, 32 GB RAM, Windows 2008 R2 Standard, SQL Server 2008 R2 Standard with SP2, and a PowerVault 3620f with 24 15k SAS spindles hooked up to two dual port HBAs on the R720. I've been experimenting with 4, 8 and 12 spindle RAID 0 groups (I can afford to lose the fault tolerance as the DBs have a life expectancy of minutes as part of the testing process).

I'm thinking I can run multiple simultaneous DBCC checks with the above hardware without hitting disk contention. I have the option to upgrade the RAM to 64 GB and the O/S to Enterprise but probably can't upgrade the SQL to Enterprise due to licensing costs.

Any suggestions on how to determine the max random I/O for DBCC using iometer, sqliosim or another utility would be deeply appreciated.


texts: MySQL - Manipulating MyISAM files directly brings down entire database

texts:
Lately, we have noticed (seemingly) random slowdowns on our system, where an innocuous query like

SHOW CREATE TABLE tableName; or SHOW COLUMNS FROM tableName will take upwards of 20 minutes, being stuck in a state like waiting for table meta data lock.

Another common one is a simple select statement like SELECT * FROM someTable WHERE id=123 will take a similarly long time, being stuck in a state like sending data.

Our server is pretty busy, so something like this can easily cause us to exceed our 500 connections limit, which then makes the server crash.

After some headscratching, we have figured out that these issues seem to be coming up when the MyISAM files (*.MYI, *.MYD, *.frm) are being manipulated directly. This is a common occurence as we have several crons that move data back and forth between servers by copying the MyISAM files from one server directly to the /var/lib/mysql/dbname folder on another server as a temp table, then rename the temp table to the name of the main table, and then running a FLUSH TABLE tableName on the destination server. When the files are being manipulated directly on the command line or via a cron (ie, while a file is being copied or unzipped into the destination directory), this is when problems occur.

We have been able to test and recreate this repeatedly, where something like rming a file or even running ls can bring the system to a halt.

So, my main question is: Is this standard documented behavior? Is it always a no-no to manipulate the MyISAM files directly? (Not counting command line tools like myisamchk, but specifically standard file operations on the command line such as cp and rm). I don't remember seeing this in the documentation, and I have even seen this as a recommended way of transfering data (https://stackoverflow.com/questions/879176/how-to-recover-mysql-database-from-myd-myi-frm-files).

The secondary question is: What is the recommended way of sharing data between servers? One way would be to use mysqldump to export and mysql to import, but the reason we are moving the MyISAM directly is because it is (seemingly) a lot faster and doesn't require that extra space for dump files.

Another idea is too keep the process as is, but copy the MyISAM files to a seperate database, and then integrate them into the main database by using RENAME TABLE tempDB.tableName TO mainDB.tableName. Would that make a difference since the files being manipulated belong to another database?

Also noting that we have been doing this for over a year, but problems have been more noticeable in the last month.

EDIT: Note that the files copied from the origin server are guaranteed not to be written to while the copy takes place. That is not the case for the destination server, where the issue is happening. This question (Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)) may be related to mine except it seems to be concerned with the data on the origin server, not the destination server.

Using MySQL 5.5.25 Planning to upgrade to 5.7 soon but can't yet


texts:
I created SQL server scheduled backup as follow :

Full: 2 times a week
Differential: 6 hours a day
Log: 3 hours

The problem I am having is that after one month I end up with a backup file of size  30GB and that is too heavy to restore, knowing that one full backup is only 1 GB max.

Can I, for example, each week save the .bak file (created as described above) somewhere and delete it from the SQLSERVER backup folder (delete it just after the last log backup that is before the next full backup occurs), so that the next week a new Full backup will be created then Differential and Log.

Or you may have a better strategy, then please elaborate.


texts: Column in INSERT query needs to increment if matching data on rows is different

texts: I tried to simplify what I am trying to do.  This is for work, we sell metal.  When an order is placed material, coating, masking, etc. is chosen.  Then someone allocates material for the order.  They want to create 2 programs.  One where a user will enter in an order number, it will verify it is a correct order  number.  It will then look at 4 tables with existing data.  It needs to take certain specs requested from those 4 tables and put them in 4 other tables that are currently empty.  Bottom line is they want to find out what was allocated for the order.  If that particular combination has already been requested on a different order number they just want to add a 1 to times_used column.  End result would be a different program that looks through these new tables.  They want to be able to determine what needs to be allocated for an order by looking at what was previously allocated on other orders. Is there any way this can be one program and not two?  The information is already in the tables I don' know why I couldn't just do this with 1 program.


texts: Creating a relationship between one field in a table with two fields in another

texts: Is the picture below the only way to create the relationship described in the title with referential integrity? Why does Access not allow me to do this without the "duplicate" table? Thanks!


texts: Why SELECT COUNT() query execution plan includes left joined table?

texts:
In SQL Server 2012 I have table valued function with join to another table I need to to count number of rows for this 'table valued function'. When I inspect the execution plan, I can see the left join table. Why? How can left joined table influence number of rows returned? I would expect that the db engine does not need to evaluate left joint table in SELECT count(..) query.

Select count(realtyId) FROM [dbo].[GetFilteredRealtyFulltext]('"praha"')

The execution plan:

The table valued function:

CREATE FUNCTION [dbo].[GetFilteredRealtyFulltext]
(@criteria nvarchar(4000))
RETURNS TABLE
AS
RETURN (SELECT
realty.Id AS realtyId,
realty.OwnerId,
realty.Caption AS realtyCaption,
realty.BusinessCategory,
realty.Created,
realty.LastChanged,
realty.LastChangedType,
realty.Price,
realty.Pricing,
realty.PriceCurrency,
realty.PriceNote,
realty.PricePlus,
realty.OfferState,
realty.OrderCode,
realty.PublishAddress,
realty.PublishMap,
realty.AreaLand,
realty.AreaCover,
realty.AreaFloor,
realty.Views,
realty.TopPoints,
realty.Radius,
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.krajId,
realty.okresId,
realty.obecId,
realty.cobceId,
IsNull(CONVERT(int,realty.Ranking),0) as Ranking,

realty.energy_efficiency_rating,
realty.energy_performance_attachment,
realty.energy_performance_certificate,
realty.energy_performance_summary,

Category.Id AS CategoryId,
Category.ParentCategoryId,
Category.WholeName,
okres.nazev AS okres,
ruian_obec.nazev AS obec,
ruian_cobce.nazev AS cobce,
ExternFile.ServerPath,
Person.ParentPersonId,
( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0)) AS FtRank

FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
Left JOIN CONTAINSTABLE(Realty, *, @criteria) ftR ON realty.Id = ftR.[Key]
Left JOIN CONTAINSTABLE(ruian_obec, *, @criteria) ftObec ON realty.obecId = ftObec.[Key]
Left JOIN CONTAINSTABLE(Okres, *, @criteria) ftOkres ON realty.okresId = ftOkres.[Key]
Left JOIN CONTAINSTABLE(pobvod, *, @criteria) ftpobvod ON realty.pobvodId = ftpobvod.[Key]
WHERE Person.ConfirmStatus = 1
AND ( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0))  > 0
)

UPDATE:

I add unique index to follow idea of Rob Farley:

 Create unique nonclustered index ExternFileIsMainUnique ON ExternFile(ForeignId) WHERE IsMain = 1 AND ForeignTable = 5

And indexed suggested by DB Engine:

CREATE NONCLUSTERED INDEX [RealtyOwnerLocation] ON [dbo].[Realty]

(
    [OwnerId] ASC
)
INCLUDE (   [Id],
    [okresId],
    [obecId],
    [pobvodId])
GO

For simplicity I remove condition

WHERE Person.ConfirmStatus = 1

from tabled valued function above.

Now the execution plan is much simpler but it still touch table ExternFile:

Maybe sql server is not clever enough?


texts: Providing an ad-hoc report facility for business using in MySQL and PHP?

texts:
We are using MySQL 5.2 CE. We need to provide the business users a way to create their own ad-hoc report.

Could you give a suggestion and an example, if this can de done using PHP?


texts:
I need to fix a problem. I have dropped a database. I have restored the *.frm and *.ibd files.

I haven't copied them into the mysql folder yet - can I just copy the files back and bring the server back up ?

ibdata1, ib_logfile0 and ib_logfile1 are still intact from when I quit the server with a kill -9 in a panic.

What's the best way to proceed - I haven't tried anything yet as I was worried I might lose the data (if it's not lost already).


texts:
I am converting Oracle tables into a sqlite3 Database and stuck at the "-Infinity"-value in a float field.

The snippet of the Oracle "Create Table" statement is

"X1" FLOAT(126)

How do I convert the "-Infinity" value correctly into a sqlite3 table?


texts: Can you combine database mirroring with AlwaysOn Availability Groups?

texts:
I assume the answer is "no" but the SQL Server Management Studio 2012 GUI implies that you might be able to. In other words, if you right-click on a database that is an AlwaysOn Availability Group primary replica, you are given the option to mirror it (Tasks --> Mirror...).

Is that just a SSMS GUI 'feature'..? I've experimented by trying to mirror the database to a different instance (i.e. an instance that is not an availability replica) and the GUI lets me fill in all the necessary details and takes me right up to the point where you choose your mirroring type (High Performance or High Safety) and click Start Mirroring. An error message appears saying that the database is already enabled for mirroring (presumably because it is in an AlwaysOn availability group).

Probably I am the only person daft enough to attempt this but I'd welcome a confirmation that indeed it is impossible to seperately mirror an AlwaysOn availability group database. (I can't find any confirmation either way when Googling).

By the way, (just by way of background), in case you wonder I don't just set up another AlwaysOn Availability Group secondary replica (instead of attempt database mirroring), the issue is that my intended mirror instance is another instance on the same Windows Server Failover Cluster (as my AlwaysOn Availability Groups primary/secondary instance). It transpires that you can’t have more than one availability replica (per availability group) on a single WSFC cluster server node. This is the precise error message:

SQL Server instance 2 is installed on the same computer as server instance 1, which is already selected for this availability group. Each Windows Server Failover Clustering (WSFC) node can host only one availability replica per availablity group. Specify a server instance on a different WSFC node.


texts: SQL Server 2008 R2 .mdfs write protected

texts:
I had to replace my hard drive, XP pro sp3, and all my applications - including SQL Server 2008 R2 developer edition. My databases are all on a separate physical drive. I find I cannot move or copy them to a different folder, I cannot flip the read-only flag, and I cannot attach them to the database server.

I am logged in as administrator and accessing management studio as sa.  None of the other user files are write protected on that drive.