texts: Vacuum increases bloat size as reported by Heroku's bloat SQL query

texts:
I am using using the Heroku command as described here to detect bloat in tables. One of our tables reported a bloat size of around 7GB, but running vacuum on it, then running the same bloat command, reports close to 21GB of "waste". I have no idea how this can happen. I understand the various conditions under which a table can bloat, even this dramatically, but not after a vacuum, I have never seen such a case.

Any ideas why this might happen? The table in question does have a few TOAST tuples, perhaps that might be it. I also can't tell if that query from Heroku accounts for TOAST waste, but my understanding is that vacuum would take care of those as well. Local tests show the "waste" column from that bloat query staying pretty stable for small sizes and decreasing for larger values, but never an increase, which makes this rather strange.


texts: Database creation on Linux with dbca: "command not found"

texts:
I have installed an Oracle on my machine. When I open a session with VNCServer and type dbca,  either as root or as oracle, I get these errors:

-bash : -dbca : Command Not found
Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified

What do I need to do to fix this?


texts:
I am running in OpenVZ container with Ubuntu 14.04 LTS.

MongoDB version:

db version v3.0.4
git version: 0481c958daeb2969800511e7475dc66986fa9ed5

I use clean installation like described here - http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/

top shows around 100% - 150% CPU

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
30480 mongodb   20   0  548096  66920  39884 S 150.2  1.6   4:55.38 mongod

This is while nothing else is going on. Nothing else is running in the container. No queries, no connections.

I have tried lock file in this question: https://stackoverflow.com/questions/10747580/mongodb-service-at-100-cpu. It's not it.

Edit (Log file upon start)

2015-07-09T14:05:48.998+0200 I JOURNAL  [initandlisten] journal dir=/var/lib/mongodb/journal
2015-07-09T14:05:48.998+0200 I JOURNAL  [initandlisten] recover : no journal files present, no recovery needed
2015-07-09T14:05:49.061+0200 I JOURNAL  [durability] Durability thread started
2015-07-09T14:05:49.061+0200 I JOURNAL  [journal writer] Journal writer thread started
2015-07-09T14:05:49.062+0200 I CONTROL  [initandlisten] MongoDB starting : pid=30480 port=27017 dbpath=/var/lib/mongodb 64-bit host=euler-dev
2015-07-09T14:05:49.062+0200 I CONTROL  [initandlisten]
2015-07-09T14:05:49.062+0200 I CONTROL  [initandlisten] ** WARNING: You are running in OpenVZ which can cause issues on versions of RHEL older than RHEL6.
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten]
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] db version v3.0.4
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] git version: 0481c958daeb2969800511e7475dc66986fa9ed5
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1f 6 Jan 2014
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] build info: Linux ip-10-45-73-23 3.13.0-24-generic #46-Ubuntu SMP Thu Apr 10 19:11:08 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] allocator: tcmalloc
2015-07-09T14:05:49.063+0200 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { bindIp: "127.0.0.1", port: 27017 }, storage: { dbPath: "/var/lib/mongodb" }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongodb/mongod.log" } }
2015-07-09T14:05:49.092+0200 I NETWORK  [initandlisten] waiting for connections on port 27017

Edit2 more detailed logs

A log with journaling enabled: pastebin.com/kKx4SJ98

Journaling disabled: pastebin.com/s2GT71w7

With journaling disabled the CPU usage cuts in half (around 40% - 80%). This all when idle.


texts: Why is tempdb consumed in this scenario? (MS SQL Server 2012)

texts:
I've found strange situation running data deletion script.
This script seems to be correct, without side effect of consuming all space in TempDB. And other processes reported problem about insufficient space for tempDB. (there is about 150 000 000 records to delete)

Unfortunately I don't know why.
Could you help me understand why all TempDB space was used?
How to improve this scritp?
It is not possible to truncate or recreating table technique, because this table is extensively used and everything have to be done on living system.

Script

ALTER PROCEDURE [dbo].[UnloadLog]
     @start_dt datetime, @end_dt datetime
AS

BEGIN
    DECLARE @id bigint, @idx int;
    DECLARE cursor_ids CURSOR LOCAL FAST_FORWARD FOR
        SELECT [ID] FROM [Database01].[dbo].[LogTable] WITH(NOLOCK) WHERE @start_dt <= [EventDate]  and [EventDate] < @end_dt;

    SET NOCOUNT ON;
    SET @idx = 0;

    OPEN cursor_ids

    BEGIN TRANSACTION;

    FETCH NEXT FROM cursor_ids INTO @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE FROM [Database01].[dbo].[LogTable] WHERE [ID] = @id;

        SET @idx = @idx + 1;

        IF @idx >=1000
        BEGIN
            COMMIT;
            SET @idx = 0;
            BEGIN TRANSACTION;
        END;
        FETCH NEXT FROM cursor_ids INTO @id
    END

    COMMIT;

    CLOSE cursor_ids;
    DEALLOCATE cursor_ids;
END

and procedure was run from SQL Enterprise Management Stiudio with this script

DECLARE @return_value int

EXEC    @return_value = [dbo].[UnloadLog]
        @start_dt = N'2015-12-01 00:00:00',
        @end_dt = N'2016-05-01 00:00:00'

SELECT  'Return Value' = @return_value

GO


texts: mysql row returned n times based on column value n

texts:
Say I have a given table with the following data:

| id | pid | email           | entries |
|----|-----|-----------------|---------|
| 1  | 1   | foo@example.com | 3       |
| 2  | 1   | bar@example.com | 1       |
| 3  | 1   | baz@example.com | 2       |

id is the primary key.

pid,email has a unique index.

entries indicates the number of entries the person has into a prize draw.

How can I query the above data to get the following result:

| id | pid | email           | entries |
|----|-----|-----------------|---------|
| 1  | 1   | foo@example.com | 3       |
| 1  | 1   | foo@example.com | 3       |
| 1  | 1   | foo@example.com | 3       |
| 2  | 1   | bar@example.com | 1       |
| 3  | 1   | baz@example.com | 2       |
| 3  | 1   | baz@example.com | 2       |

where each row is returned n times based on the entries column a row has?

The reason is to hand the result to a randomized name drawer where the more entries you have the more chances you have of winning.

Currently, we have the setup where each entry creates a new row in the table but this starts having performance issues when certain entrants make a thousand entries in one go. This generates thousands of inserts at a time and when we have a popular prize we are running into performance issues where inserts start taking too long as there are so many.

If we could restructure each how we store the data we can then just have 1 upsert query when, for example, someone gets 1000 entries in one go rather than generating 1000 individual inserts.


texts: Duplicate record inserted - why trigger is not preventing it?

texts:
I have two tables: DebitIUVs and RPTs. I have record inside DebitIUVs which is with state 118001. My business logic must insert new line inside RPTs table. Inside RPTs table there is additional business logic and updates state of in parent table DebitIUVs. The idea is to prevent inserting new row in the RPTs table when the state is changed to be different from 118001 (while changes are in transaction in the trigger). Calls are executed via WEB Service.

For unknown reason we have 2 calls in the same time which cases 2 rows inserted in RPTs table. The code seems logically correct. Also we have heavy load in the database (sometimes it reaches 100000 transactions per day).

Any idea why this can happened?

Simplified code:

CREATE TABLE [dbo].[DebitIUVs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDIUVState] [int] NOT NULL,
    CONSTRAINT [PK_DebitIUVs_ID] PRIMARY KEY NONCLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[RPTs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDDebitIUV] [int] NOT NULL,
    [IDRPTState] [int] NOT NULL CONSTRAINT [DF_RPTs_IDRPTState] DEFAULT ((120001)),
    [PaymentRequestID] [varchar](35) NULL,
    CONSTRAINT [PK_RPTs_ID] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [UK_RPTs_PaymentRequestID] UNIQUE NONCLUSTERED ([PaymentRequestID] ASC)
);
GO

CREATE TRIGGER dbo.trg_aiu_RPTs_RI
    ON dbo.RPTs
    FOR INSERT, UPDATE
AS
BEGIN
    --if no rows are affected - return
    IF ISNULL((SELECT COUNT(ID) FROM inserted), 0) = 0 RETURN;

    DECLARE @dtNow DATETIME = GetDate();

    SET NOCOUNT ON;
    UPDATE RPT SET
        PaymentRequestID =
            CONVERT(VARCHAR(10), @dtNow, 112) + REPLACE(CONVERT(VARCHAR(10), @dtNow, 108), ':', '') +
            RIGHT(REPLICATE('0', 10) + CAST(ins.ID AS VARCHAR(10)), 10)
    FROM inserted ins
        INNER JOIN dbo.RPTs RPT ON
            (RPT.ID = ins.ID)
    WHERE ins.PaymentRequestID IS NULL;

    IF UPDATE(IDRPTState)
    BEGIN
        UPDATE DIUV SET
            DIUV.IDIUVState =
                CASE
                    WHEN ins.IDRPTState = 120101 THEN 118002
                    WHEN ins.IDRPTState = 120201 THEN 118003
                    ELSE DIUV.IDIUVState
                END
        FROM
            inserted ins
            LEFT JOIN deleted del ON del.ID = ins.ID
            INNER JOIN dbo.DebitIUVs AS DIUV ON DIUV.ID = ins.IDDebitIUV
        WHERE (del.ID IS NULL) OR (del.IDRPTState <> ins.IDRPTState);
    END;
END;
GO

CREATE PROCEDURE dbo.sproc_1
    @IDIUV INT,
    @IDIUVState INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION InsertDebit;
    INSERT INTO dbo.RPTs (IDRPTState, IDDebitIUV)
    SELECT @IDIUVState, IUV.ID
    FROM dbo.DebitIUVs IUV
    WHERE
        (IUV.ID = @IDIUV) AND
        (IUV.IDIUVState <> 118002);
    COMMIT TRANSACTION InsertDebit;
END;
GO

INSERT INTO DebitIUVs (IDIUVState) VALUES (118001);
EXEC dbo.sproc_1 1, 120101;


texts:
I have a heap table with around 700k rows.  The rowsize is 34 bytes: (1x BIGINT, 2x SMALLINT, 2x BIT, 4X INT, 1x SMALLDATETIME).  Why does Data Space Used say this table is taking up nearly 4000000KB (4GB) in Object Explorer Details.  It is compressed with PAGE compression.

I know I must be missing something on this calculation because I have another heap table with 12.7 million rows that is only taking up 1.5 GB and it has a row size of 356 bytes.  It is also compressed with PAGE compression.

EDIT:
I just did an SELECT * INTO to see what the size of this data would be in another table and it's only 28MB...


texts: Upgade from SQL Server 2012 cluster Evaluation to enterprise

texts:
What I understand is that there is a restriction of upgrading a SQL Server 2012 cluster from evaluation edition to licensed standard edition.

Is there a similar limitation for upgrade from cluster evaluation edition to licensed enterprise edition. If no , can you please provide a link for the upgrade process


texts:
I have 2 tables. The first table is feeds and (feeds.ID) is the primary key. The second table is items. feeds.ID is related to items.feed_ID, so with a simple inner join I can extract the data of each feed.

Now, I deleted some of the feeds but didn't delete them from the items table. I want to find the items which have no resource on the feeds table. On the other hand, I want to find the useless items. What is the correct query?

I have tried this:

select * from items where feed_id NOT IN
    (select id from feeds
        inner join items on feeds.id=items.feed_id group by feed_id) group by feed_id

but it shows an error.


texts: should I shrink the filegroup? how? SQL Server

texts: I just have a question. We recently purged 1,5 millions of record from a table. The Current allocated space for the related Filegroup is 700GB, but the space Used is 300GB. Should I perform a shrink for this particular case in order to reduce the Allocated space /disk size? How can we do it? because it is an .ndf file, I have never shrink an .ndf file. Thanks in advance!


texts: Why do I get The transaction log for database 'X' is full due to 'ACTIVE_TRANSACTION' w/ Simple Recovery and auto grow log?

texts:
Why do I get  The transaction log for database 'X' is full due to 'ACTIVE_TRANSACTION' on a database with Simple recovery model and auto-growing log file?

The failing operation is executing many, many Insert statements. The insert statements are not occurring inside a transaction. I don't understand why the transaction log has to be so large for this and I don't understand why it's unable to grow.


texts: Oracle: Creating Case Insensitive Database with NLS_COMP and NLS_SORT

texts:
I try to create an Oracle 12c Database (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production) which ist case insensitive. My first try is just a try to test my aims:

select count(1) from dual where 'a' = 'A';
--0 case sensitive

alter session set nls_sort = 'BINARY_CI';
alter session set nls_comp = 'LINGUISTIC';

select count(1) from dual where 'a' = 'A';
--1 --> Case insensitive

alter session works fine but leads to massive problems with tablescans. Oracle ignores all indexes, which didn't created as BINARY_CI / LINGUISTIC.

As a solution for the tablescan problem I try to create a Database, which has the apropriate nls_sort and nls_comp setted as I needed. I started DBCA (Database Configuration Assitent) and created a Database with the two INI parameters:

nls_comp=LINGUISTIC
nls_sort=BINARY_CI

The Database created but didn't work as expected:

select count(1) from dual where 'a' = 'A';
--0 case sensitive

After some investigation I found:

select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
/*
NLS_COMP    BINARY
NLS_SORT    BINARY
*/

select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
/*
NLS_SORT    BINARY_CI
NLS_COMP    LINGUISTIC
*/

Which means, somehow the NLS Parameter vanished from my Database. I wonder if the reason is the seed Database, which DBCA used as a template. My NLS Parameter were used by the instance but there they have no effect on my query.

Should I create a Database without DBCA? How? ( https://docs.oracle.com/cloud/latest/db121/ADMIN/create.htm#ADMIN11073 ) Or can I change somehow the NLS-Parameter inside the Database?

Regards
  Dezsö


texts:
Newbie to dba. I'm creating a php/mysql inventory list and would like some advice before I start, on structuring my table(s) and database.

The inventory database will eventually contain hundreds of items, ever increasing (potentially to thousands). The current data is contained in a .xls sheet which I have access to, it contains the following;

items (dummy data for display purposes)

+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| itemId | invNo | make | model     | ip      | room | dataAdded | dateMod  | description    | location | type   | status   |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 1      | 234   | HP   | 765       | 123.123 | 54   | 01-02-16  | 02-01-16 | some desc here | floor 1  | server | active   |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 2      | 765   | Dell | Precision | 123.123 | 34   | 02-01-16  | 03-01-16 | some desc here | floor 3  | pc     | active   |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+
| 3      | 987   | Dell | Precision | 123.123 | 65   | 05-09-15  | 07-09-15 | some desc here | floor 1  | server | inactive |
+--------+-------+------+-----------+---------+------+-----------+----------+----------------+----------+--------+----------+

Is it ok for me to add all these columns into the one table in my dtabase, or should I seperate the columns across different tables?

For example, should I have a table for location, type and status - if so, why?

Is there any disadvantage to having all the columns in the one table?

The final web application will allow users to create, read, update and delete items. As well as search for items and filter by type/location etc...

I normally use MySql Workbench or PhpMyAdmin to create and design the database.

Any advice is appreciated.


texts: Selecting and Inserting into linked server

texts:
I am having a situation with linked server which I am not able to understand.

So we have a linked server from a 2008R2 server to a 2014 server.
The below sample query is executing from 2008R2 server and it works fine.

SET XACT_ABORT ON;
 Declare @BatchSize int = 10
      DELETE from LINKEDSRV.DB.DBO.Table
      INSERT INTO LINKEDSRV.DB.DBO.Table (ECN)
      SELECT TOP (10) C1 from LINKEDSRV.DB.DBO.Table22 --order by C1
      SELECT * FROM LINKEDSRV.DB.DBO.Table

But when I execute the same thing with the order by C1 it does not return any results.

Second case - If I replace  the TOP(10) with TOP(@BatchSize) and no order by also I dont get any results out. eg

SELECT TOP (@BatchSize) C1 from LINKEDSRV.DB.DBO.Table22

All of the scenarios work if I SET XACT_ABORT OFF.  So does XACT_ABORT has any kind of restriction when it comes to linked server?

Edit - Did some more testing and looks like it has to do something to do with the number of rows also

Possible Repo

On ServerA

use testdb
go
create table t1( c1 int, c2 datetime)
create table t2( c1 int, c2 datetime)

insert into t2 select 1, GETDATE()
insert into t2 select  * from t2 -- insert close to 5000 rows, I found the issue around over around 35000 rows

On ServerB

Create linked server to ServerA

SET XACT_ABORT ON;
Declare @BatchSize int = 10
delete from ServerA.testdb.dbo.t1
insert into ServerA.testdb.dbo.t1 (c1)
select top (@BatchSize) c1 from ServerA.testdb.dbo.t2 --order by c2
select * from ServerA.testdb.dbo.t1

No output.  But if you reduce the number of rows in table t2 to say around 2000, it works fine.


texts:
We have a 16GB RDS-MySQL Multi-AZ instance. Of late, it has unexpectedly failed over multiple times causing the dependent application to be offline for over 15 minutes.

AWS-Support blamed it on high Swap Usage. However, the instance has failed when the Swap Usage was as low as 10 MB. We have started optimizing the queries.

Is there anything else that we can do to avoid such mishap?

===================================================================

EDIT :

We have made one observation. The CPU utilization of the instance remains fairly low. So, we presumed that the issue might be with disk access. The write-throughput is several times that of read-throughput. To add to it, Sort_merge_passes has a value of 79425(which is crazy, right ?). On going through the slow query log, it was found that there are multiple queries with ORDER BY clause not using indexes. It will take time before workarounds are found for all these queries. Can you suggest some immediate fix to overcome the    failover issue ? Should we increase the global value of sort_buffer_size ? Current value is 2MB.

===========================================================

EDIT 2 :

Below is a screenshot of the rate of change of Sort_Merge_Passes(Queries per second) as seen on RDS-Performance Insights.

As can be seen, during periods of load,the value varies from 0.5 to 1(we have observed values higher than 1 as well). A query can cause multiple Sort_merge_passes, 8 being the highest that we have observed.

=======================================================================

EDIT 3 :

We also found that the global status Created_tmp_disk_tables is increasing at the rate of approx 2-3/second. Badly structured queries I guess.


texts: max_stack_depth not changed even stack changed in limits.conf

texts:
The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log:

DETAIL:  "max_stack_depth" must not exceed 7680kB.
HINT:  Increase the platform's stack depth limit via "ulimit -s" or local equivalent.

actually, the below line exists in limits.conf. Have tried updating postgresql process using prlimit and even reboot the server(OS: Ubuntu). It still reports the same error.

*         soft stack   524288

if switch to the user postgres and verify the stack size with ulimit -s it is already set to 512MB.

This shows there is no ulimit -s to change the stack size under /etc.

$ sudo grep -r ulimit /etc/*
/etc/init.d/lxd:        ulimit -n 65536
/etc/vmware-tools/vm-support:runcmd "/tmp/ulimit-a.txt" ulimit -a

Not sure why postgresql still not seeing this change?


texts: Unique key constraint violation not escalated to parent stored procedure

texts:
I have a stored procedure which calls 2 nested sub procedures. For Example,

MyProc
  BEGIN
    BEGIN TRY
      BEGIN TRAN
        EXEC UpdateProc
        EXEC InsertProc
      COMMIT TRAN
    END TRY
    BEGIN CATCH
      ROLLBACK TRAN
      ;THROW
    END CATCH
  END

The insert stored procedure could insert a line with duplicate values violating a unique key constraint. I'm calling the main stored procedure from a .NET application. In case of unique key violation, my application is not receiving the correct exception. The exception received is 'Specified Cast is not valid'.
I've tried adding SET XACT_ABORT ON but this did not correct the problem.


texts:
Where can I see the exact row count of a table in pgAdmin III?

I know where to find the approximate row count:

I am also aware I can SELECT COUNT(*) FROM my_table. But I want to know whether the exact row count can be found somewhere in pgAdmin III's GUI, in the same way we can query the top 100 rows.


texts: oracle database filter with full outer join and coalesce intersect

texts:
in a Oracle Database if I "shoot"

SELECT
    RAP.IDPL,
    C41.TIP_COD_DESEU AS CD41,C41.STOC_VAL AS CANT41,
    C42.TIP_COD_DESEU AS CD42,C42.STOC_VAL AS CANT42,
    C43.TIP_COD_DESEU AS CD43,C43.STOC_VAL AS CANT43
FROM CHEST_UL CHT
    INNER JOIN UL_PUNCT_LUCRU rap
        ON cht.fk_id_raportor = rap.id
    FULL OUTER JOIN CH_41_TAB1 C41
        ON C41.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_42_TAB1 C42
        ON C42.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_43_TAB1 C43
        ON C43.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_44_TAB1 C44
        ON C44.FK_ID_CHEST = CHT.ID
    FULL OUTER JOIN CH_5_TAB1 C5
        ON C5.FK_ID_CHEST= CHT.ID
WHERE COALESCE (C41.STOC_VAL,C42.STOC_VAL,
                  C43.STOC_VAL,C44.STOC_VAL,
                  C5.CANT_PREL)
        IS NOT NULL

the result is:

IDPL    CD1         CANT41  CD2         CANT42  CD3         CANT43
275522                      10.01.01    28.54
275522                                          20.01.01    155
275522  10.01.01    102.766
272343                      10.01.01    123
272343                                          10.01.01    353
272343  10.01.01    102.766

what can I do to obtain a result like this:

IDPL    COD         CANT41      CANT42      CANT43
275522  10.01.01    102.766     28.54
275522  20.01.01                            155
272343  10.01.01    102.766     123         353


texts: How can I select just the 'Create Table' column of SHOW CREATE TABLE?

texts:
MySQL SHOW CREATE TABLE tablename has two columns of output, 'Table' and 'Create Table'.  How can I select just the 'Create Table' data?
I've tried
mysql> SELECT 'Create Table' FROM (SHOW CREATE TABLE tablename);

and
mysql> SELECT `Create Table` FROM (SHOW CREATE TABLE tablename);

Both result in syntax errors.  I'm not sure what else to try.


texts: What are the "additional actions" available for "Recovery Pending" databases?

texts:
I've used powershell to run a sql script on several databases on a test server. Disk space was low, so the script failed on 34 out of 75 databases. All fair enough.

In any case, afterwards 10 of my 75 databases ended in a modus where SSMS appends to the database name "(Recovery Pending)". I've solved the disk space issue and waited for a bit, but the databases remain in this state.

The MSDN carefully explains this state (emphasis mine):

  SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.

Great! But what are these "additional actions", and how can I find out which one's for me?

My Google-fu turned up several hazy forum posts with solutions such as:

detach then attach again
take offline then take online again
restore a backup
setting emergency/single_user and back to multi_user
restart the sql server instance

Some of these worked for me, heck: all of them may work. However, these are all "just try this!" suggestions from questionable sources with (side) effects I may not know about or even want.

So, again: what is the appropriate "additional action" msdn speaks of, and above all: why??


texts: Mysql Create table shows ERROR 1005 errno 150

texts:
I am trying to make a junction table, but I getting a ERROR 1005 errno 150

CREATE TABLE 61furiousFistPokemon(
cardNumber int NOT NULL PRIMARY KEY,
pokemonName VARCHAR(12),
type VARCHAR(10),
stage VARCHAR(10),
evolvesFrom VARCHAR(12),
HP INT,
retreatCost INT,
weakness VARCHAR(10),
weaknessAmount VARCHAR(5),
resistance VARCHAR(5),
resistanceAmount VARCHAR(5)
);

CREATE TABLE cardTags (
tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
tagName VARCHAR(60) NOT NULL
);

CREATE TABLE 61furiousFistPokemonTags (
tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
pokemonCardNumber int,
tagName VARCHAR(60),
FOREIGN KEY (pokemonCardNumber) REFERENCES 61furiousFistPokemon(cardNumber),
FOREIGN KEY (tagName) REFERENCES cardTags(tagName)
);

Any help would be greatly appreciated.


texts: Influence of index rebuild tasks in tempdb on the transaction log?

texts:
I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before...

The goal is to replace some simple rebuild maintenance plans with a more flexible and fragmentation driven solution to follow best practice and perform required actions instead of not required actions. So following suggestions like this by Kendra Little and a lot of other smart people, I headed for Ola Hallengreen's solution.

So far everything is setup properly, following Brent Ozar's suggestion to create two different jobs (one less-invasive e.g. daily running reorg / online rebuild job and one weekend job that does more and maybe offline processing) and ready to go.

But I am unsure and little concerned about the (side) effects.

Because we have couple of very large tables in place I tried to find out the effects of index rebuilds on transaction logs watching the log file size and number of entris growth based on the following code that I found in this question:

backup log myDB to disk = 'NUL'
-- check log space/usage:
dbcc sqlperf (logspace)
-- query index log records
SELECT LogRecordsGenerated = COUNT(*)  FROM sys.fn_dblog(NULL, NULL)  WHERE AllocUnitName = 'dbo.TableName.indexName'
-- COMMINT THE INDEX OPERATION (Online / Offline)
-- Check the above values again...

It is suprinsing that index rebuild OFFLINE tasks require log space and produce transaction log entries while ONLINE does not! I expected it the other way round...?

The questions I can't find answers for are:

How will the rebuild online or offline jobs affect Log File Growth? How can I measure this? Is there a better way?
How much space do I have to reserve in my data files for the rebuild process? I found hints for around 110%-120% of the indexes size (e.g. in this MS document). In case all my indexes together use about 10 GB, does it mean additional size of 12 GB has to be available in the data file? Or wil lthe index rebuild job that sequentially maintains one index after another re-use the available space and release it after finished an index?
In case I use SORT_IN_TEMP, will the transaction log growth happen in the tempdb as well? Also if it is set to SIMPLE recovery mode?


texts: Squirel SQL uses wrong command for MySQL when renaming a column

texts:
I installed Squirel SQL on Ubuntu and a downloaded and put a MySQL driver into the lib folder of the Squirel folder. Then I chose this driver in the "extra class" tab when configuring Squirel MySQL driver. Connecting to the database and viewing data works but when I try to rename a column in Squirel it suggests Oracle-specific code to do it instead of a MySQL-specific variant. What did I do wrong? Doesn't Squirel's refactoring tools support MySQL or how do I configure it to properly rename columns? PhpMyAdmin automaticaly solves the task just fine.

This is the SQL that is shown when I click "show SQL" in the "modify column" menu:

ALTER TABLE some_column ALTER COLUMN "one_name" RENAME TO "another_name";

This doesn't work. mySQL answers with incorrect syntax error.

Exact order of clicks to reproduce is:
right-click on a table in the left panel, then in the context menu "refactoring"->"column"->"modify column". Then you choose the column and edit the name. Button "execute" leads to an incorrect syntax error and "show sql" gives me this sql.


texts: query for COLUMN_NAME LIKE "X" in sybase(Transact SQL)?

texts:
I need to automate writing a query in Sybase.

The table looks like this:

ID, A1, A2, A3, ..., A24, B1, B2, B3, ..., B24, etc.

I would like a query where the user can choose which columns are returned.  For instance, sometimes the user can choose all the A* columns, sometimes they can choose all the B* columns, sometimes they can choose any of the columns.

I need to write a query to read values from all the columns like:

SELECT a*,b* FROM Users

or

SELECT * FROM Users WHERE column_name LIKE a%, b%


texts: mysql, select and sorting by group key + with first row that has that key in another column

texts:
I'm trying to make a select that outputs everything, but is sorted by a group column with a "header column" that matches said group column on another column.

I have no idea how this type of query should be written.  For example, I want the data sorted like this.

id,  unit,  group, col2,  col3,  col4,  col5
---------------------------------------------
99,  ZZX,   null,  test1, test7, test5, test
6,   AAA-B, ZZZ,   test1, test7, test5, test8
77,  AAA-C, ZZX,   test1, test7, test5, test9
101, ZZZ,   null,  test1, test7, test5, test
122, AAB-A, ZZZ,   test1, test7, test5, test11
176, AAB-B, ZZZ,   test1, test7, test5, test12

So basically, the unit should be sorted per each group key, but begins with the row that matches the group key. There are also rows with null for group, but are just header rows. You can even just tell me the high level, and I can write the query, I'm just not sure the best way to approach it.


texts: Extract only single value from multiple rows based on certain condition for same id in Oracle

texts:
I'm an Oracle beginner and I'm having troubleshooting the issue below.

My table:

+-------+------+-------+------ +
| ID    | GRP  | ITDESC| DEN   |
+-------+------+-------+------ +
| 12345 | MANX | Apple | SendV |
+-------+------+-------+------ +
| 12345 | MANX | Apple | Manual|
+-------+------+-------+-------
| 12345 | MANX | Apple | UnVeri|
+-------+------+-------+------ +
| 12346 | MANX | Mango | UnVeri|
+-------+------+-------+------ +
| 12347 | MANX | PineAp| SendV|
+-------+------+-------+------ +
 12348  | MANX | Pine | Manual|

I am expecting:

+-------+------+-------+------ +
| ID    | GRP  | ITDESC| DEN   |
+-------+------+-------+------ +
| 12345 | MANX | Apple | SendV |
+-------+------+-------+------ +
| 12346 | MANX | Mango | UnVeri|
+-------+------+-------+------ +
| 12347 | MANX | PineAp| SendV|
+-------+------+-------+------ +
| 12348  | MANX | Pine | Manual|
+-------+------+-------+------ +

I have multiple rows that have only the DEN column different for the same id. My aim is for the same id perform the check.  If the value of DEN is 'Manual' then check to see if there is 'SendV' in DEN column for that id.  If it is present then consider 'SendV' otherwise consider 'Manual'.
Note in the provided example, the order is random.  The SendV can be in 2nd row or 3rd row or 4th row based on the action, however the text of DEN will be same as said above.

select * from table t1 where DEN IN ('Manual', 'SendV').

I am not aware how to write the condition?


texts:
I have a user who is working with a new manufacturing SCADA platform. He has a simple table with the following design:

Column Name       DataType     AllowNulls
identkey (PK)        INT           NO
Move                 INT           YES
Wind                 FLOAT         YES
Traverse             FLOAT         YES

This is the T-SQL DDL for the table:

CREATE TABLE dbo.tbl_Rv8WindProfile
(
    identkey int IDENTITY(1,1) NOT NULL
    , [Move] int NULL
    , Wind float NULL
    , Traverse float NULL
    , CONSTRAINT PK_tbl_Rv8WindProfile
        PRIMARY KEY CLUSTERED (
             identkey ASC
             )
        WITH (
            PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
            ) ON [PRIMARY]
) ON [PRIMARY];
GO

He currently has only 5 rows of data in the table. In the past, he has right-clicked and selected EDIT TOP 200 ROWS and manually changed the value of the column "Move" by entering an integer via the numeric keypad on his keyboard. It has worked (as he says) for years.

In the past few weeks, he has been getting an error which reads:

  No row was updated.
  The data in row 5 was not committed.
  Error Source .Net SQLClient Data Provider.
  Error Message: Conversion failed when converting the varchar value 'Move' to data type int.
  Correct the errors and retry or press ESC to cancel the change(s).

If I edit the data using this SQL:

UPDATE dbo.tbl_xyzProfile
 SET [move] = 4
 WHERE [identkey] = 5

the data is updated.

If I use this query (with a character instead of an INT):

UPDATE dbo.tbl_Rv8WindProfile
 SET [move] = '5'
 WHERE [identkey] = 5

It recognizes the conversion and it still works.

Things I've checked so far:

I've double-checked and it does not appear that MOVE is a reserved word.
It doesn't appear that he has updated his Microsoft SQL Server install in the past few weeks.
It works in some tables for him, but not others and I cannot see any pattern which would explain why.

He is editing the integer data in column named MOVE. In the example shown here, he is trying to change row 4 (identkey = 4) from a integer value of 4 to 6.

I created the table in another database and replicated the data. It also gave me the same error. Then I renamed the column MoveZ and immediately, it let me modify the integers through the EDIT TOP 200 ROWS option.

I did look at the T-SQL reserved words, ODBC reserved words, Future reserved words and C# reserved words and MOVE doesn't appear on any of the lists. I don't find it on any .NET reserved words list either.


texts:
The below query tells me there is a total of 520 connections to our SQL Server database. Status of all is 'sleeping' except for 4 connections. The login_time goes back to 2 days ago on some connections.

select session_id,  login_time, host_name, program_name, status, cpu_time, total_elapsed_time
from sys.dm_exec_sessions
order by total_elapsed_time desc;

I run the below query to see that there are 61 different logins...

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;

Is this standard? Or should those conenctions that are sleeping be closed?

Thanks.


texts:
According to the documentation datetime2 (Transact-SQL):

  Storage size
  6 bytes for precisions less than 3.
  7 bytes for precisions 3 and 4.
  All other precisions require 8 bytes.

The size of datetime2(0), datetime2(1), datetime2(2) use the same amount of storage (6 bytes).

Would I be correct in saying that I might as well go with datetime2(2) and gain the benefit of the precision without any additional size costs?

Please note:

This column is indexed with the PK to form a compound clustered index (used for table partitioning)
I do not care about milliseconds

Would datetime2(0) be more cpu efficient when used in a where clause, or when seeking through an index?

This is a massive table, so the smallest optimization will make a big difference.


texts:
I followed a tutorial to update My MySql 5.1 to 5.5 (actually i wanted to upgrade to 5.6, but the tutorial mentioned that i should upgrade to 5.5 first and then to 5.6).

However, after removing previous version and updating mysql configuration and path environment variable, i'm stuck at starting the server and upgrading. I'm a newbie i this field and i don't know what to do now.

These are the steps i followed:

Stopped mysql service, Copied mysql data files and configuration files.
Removed version 5.1.
Downloaded 5.5 and installed it.
Updated start-up script to register the new installation.
Updated Path Environment Variable.
Updated MySql Configuration.
Made appropriate changes to path to mysql plugins directory.
Creates mysqld symbolic link in /usr/sbin/ directory.

Now the final step was to start the server and upgrade it. So i tried sudo /usr/sbin/mysqld --skip-grant-tables, but it says sudo: /usr/sbin/mysqld: command not found.

i don't know what to do now. I'm stuck at this from the last 4 hours. I was following this tutorial.

Edit:

i found out that the problem was with the command responsible for the creation of symbolic link in /use/sbin directory.

I had used sudo ln -s /opt/mysql/server5.5/bin/mysqld /usr/sbin/mysqld instead of sudo ln -s /opt/mysql/server-5.5/bin/mysqld /usr/sbin/mysqld. Dash was not present. Now, i am able to execute the command sudo /usr/sbin/mysqld --skip-grant-tables.

After executing, i tried mysql_upgrade -uroot -p --verbose, but it showed an error

The program 'mysql_upgrade' can be found in the following packages:
* mysql-server-core-5.1
* mysql-cluster-server-5.1
* mysql-server-5.0
Try: sudo apt-get install <selected package>


texts: 6GB Table - 37Mi Rows - 'System.OutOfMemoryException' - SQL 2008 R2

texts:
I have a proc, looping through databases( the insert is in a fix database...the select uses the loop ), making inserts (Will hide names for security):

insert into Main_Database..Table(16 Fields)
    select ( there are 16 fields here, DateDiff(s, DtTrabalhoInF8, DtTrabalhoFi) as TempoSegundos
     from  [Database_name].dbo.View as T1 with (nolock)
     where DtTrabalho >= '2015/06/06' and
     not exists(select 1 from PainelControle.dbo.tblLogProjetos T2 Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS and t2.codlog = t1.codlog)

The problem is:

If I run this, it runs in 1 second:

insert into Main_Database..Table(16 Fields)
        select ( there are 16 fields here, DateDiff(s, DtTrabalhoInF8, DtTrabalhoFi) as TempoSegundos
         from  [Database_name].dbo.View as T1 with (nolock)
         where DtTrabalho >= '2015/06/06'

The problem is here:

and
         not exists(select 1 from Database_Name.dbo.tblLogProjetos T2 Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS and t2.codlog = t1.codlog)

This tblLogProjetos has 37Million rows. 6GB ( The simple select 1 from tbllog  trhowing the message) and the job never ends( running the code manually, not inside the job, after 10min it shows me the error ):

  'System.OutOfMemoryException'

I would like ot know how to troubleshoot this. Is this SERVER problem? ( we have 32GB ). Is this MANAGEMENT STUDIO problem:

name                    minimum      maximum       config_value     run_value
max server memory (MB)    16       2147483647        28000           28000

Or is this query problem? One thing that I notice, When I use a query to see unused indexes, every index of this table has 0 reads. How can this be possible?

By the way...tblLogProjetos is a VIEW that uses a linked server to another server. It's just a select ( fields ) from [server5].painelcontrole.dbo.tbllogprojetos


texts: What are the other types of database architectures?

texts:
Oracle, for example has a typical client server architecture, and a memory cache (SGA) with some background processes.

This is one way of designing a database. Are there databases out there which have been designed fundamentally in different ways than this?


texts: Should I use a subquery to help SQL Server find the correct plan

texts:
I have a query (which has components which are built on the fly depending on the selections a user makes in the interface) that runs in SQL Server 2008-R2 that runs in about 100 databases. Users are company employees and each company gets their own database, and both usage patterns and data ratio's between tables vary greatly between companies.  This is a query that directly effects the user's perception of the speed of the system, and almost all users need to run this query every time they use the system, so it is worth my time to get it right.

We've mostly had good luck letting SQL Server choose the plan (since the best plan probably varies on different databases at different times), but as we've expanded to more users and added more databases (and database servers), we have found cases where it simply has decided on the wrong plan, and the performance goes from a half second (which, at this point, is faster than the user interface) to 10 seconds or worse (which is effectively broken as far as the users are concerned).  I've finally been able to isolate a situation where the performance is consistently bad, so I can now try to fix the problem.

Here is what we currently have (simplified considerably, of course):

SELECT
    -- about 100 columns
FROM
    base_table
LEFT JOIN
    limiting_table
INNER JOIN
    problem_table
LEFT JOIN
    tables_only_used_in_select
INNER JOIN CONTAINSTABLE(
    base_table, *, 'extended text to search for', LANGUAGE 1033) AS rank1
ON
    base_table.row_id = rank1.[KEY]
LEFT OUTER JOIN CONTAINSTABLE(
    base_table, *, 'text to search for', LANGUAGE 1033) AS rank2
ON
    base_table.row_id = rank2.[KEY]
WHERE
    various criteria on base table

The "problem_table" in the above query seems to be the key.  The vast majority of the time, this has no affect on the rows returned by the query.  My first fix was to change it from an INNER JOIN to a LEFT JOIN.  That fixed the problem.  Of course, now I'm going to have to make sure to limit the results a different way (which is possible, but is not an easy code change to get that information to the query), and all the joins still give the database a chance to find another slow plan.

However, I've also had good luck trying to isolate the "important" joins into a subquery, such that the plan won't get distracted by the other tables that aren't as critical.  Like this:

SELECT
    -- about 100 columns
FROM (
    SELECT base_table.*
    FROM
        base_table
    LEFT JOIN
        limiting_table
    INNER JOIN CONTAINSTABLE(
        base_table, *, 'extended text to search for', LANGUAGE 1033) AS rank1
    ON
        base_table.row_id = rank1.[KEY]
    LEFT OUTER JOIN CONTAINSTABLE(
        base_table, *, 'text to search for', LANGUAGE 1033) AS rank2
    ON
        base_table.row_id = rank2.[KEY]
    WHERE
        various criteria on base table
    ) base_table
INNER JOIN
    problem_table
LEFT JOIN
    tables_only_used_in_select

Am I looking at problems doing it this way that I just don't have enough experience to know better?  Or is this a reasonable practical strategy in this case (maybe not ideal, perhaps, but worthwhile trying it and seeing if it works)?

UPDATE:

Here is the plan for the original query that runs on one database in 1 second (and returns 46 rows):

And here is the exact same query running in a different database that takes 55 seconds and returns 369 rows:

Changing from INNER JOIN to LEFT JOIN on the problem_table runs in under a second and results in this plan:

And the subquery, which also takes one second, results in this plan:

Note: I really thought I saw something about the correct way to post plans, but I can't seem to find it.  If someone comments with a hint, I'll try to do a better job.


texts: Running Total which Resets when Customer changes (keeping existing RowNumbers)- attempt included

texts:
I have a table where the RowNumber is of essence.
It has a range of clients, and a running total for that client - which resets every time the Client changes.
e.g.

client  rownr   amount  runtotal
Company A   1   1.00    1.00
Company A   2   1.00    2.00  1+1 = 2
Company A   3   5.00    7.00  2+5 = 7
Company B   4   3.00    3.00  Reset Because Customer B <> Previous Row Customer A
Company A   5   1.00    1.00  Reset Because Customer A <> Previous Row Customer B
Company B   6   2.00    2.00  Reset Because Customer B <> Previous Row Customer A
Company B   7   1.00    3.00  2+1 = 3
Company B   8   5.00    8.00  3+5 = 8

I tried using Partition, but it always Groups Client A together, and Client B together- it removes the crucial part of the Row Numbers.

Any help please?

 drop table #Checks
CREATE TABLE #Checks
(
  Client VARCHAR(32),
  RowNr int,
  Amount DECIMAL(12,2),
  RunTotal DECIMAL(12,2),
  Part int
);

INSERT #Checks(Client, RowNr, Amount)
          SELECT 'Company A', '1', 1
UNION ALL SELECT 'Company A', '2', 1
UNION ALL SELECT 'Company A', '3', 5
UNION ALL SELECT 'Company B', '4', 3
UNION ALL SELECT 'Company A', '5', 1
UNION ALL SELECT 'Company B', '6', 2
UNION ALL SELECT 'Company B', '7', 1
UNION ALL SELECT 'Company B', '8', 5;

-- gets the first entries per client - these amounts are
-- the base amounts and the other entries are tallied up

with cte as (
            select
                    c1.client
                  , c1.amount
                  , c1.rowNr
                  , case when c1.client <> c2.client then c1.amount else null end amt
            from #Checks as c1
            left join #Checks as C2 on c1.rownr = (c2.rownr + 1)
  )

select
client
, rownr
, amount
, case when isnull(amt,0) > 0 then amt else total end as runtotal
from cte
cross apply (
                select
                       sum(x.amount) as Total
                from cte as x
                where x.rownr <= cte.rownr and cte.client = x.client
            ) as rt
;

Drop table #Checks


texts: Estimating resource utilization for one database

texts:
I've been tasked with migrating a database and I want to figure out how much resource in terms of CPU and IO does that database need before migrating it.  The target server to which the database is planned to be moved to is critical and I want to figure out if the target server can handle the load that the new db brings.
Things which I am considering

Use plan cache to find the IO and CPU, but I am not sure how to
translate to real CPU and IO usage on the server.
Use profiler/extended events to track activities on the DB.  NOt sure how to translate it to real CPU / IO usage

What is the normal things to do in such situation?

Cheers


texts: SQL Server Query Plans & Offline databases

texts:
I recently asked a question about exporting query plans (here Export Query Plans) and got some great answers.

However, I did run into one issue, during the process of interrogating SQL Server to obtain the plans, the process fails if there are any offline databases.  Simple question, to which I suspect the answer is complex.  How do I have the interrogation ignore the offline databases?

Thank you in advance for your assistance!


texts: Optimizing PostgreSQL query with multiple subqueries

texts:
I have a rather simple question (I think): I have one query with 2 window functions: COUNT() and AVG(). I also have 10 subqueries, each with a window function (COUNT() or AVG()).
Testing in pgadmin, this query works fast. The problem is: I'm currently testing against only 100 rows.
Here is the query:
SELECT
    COUNT(a.review_id)::int AS total_reviews,
    COALESCE(AVG(a.score), 0)::int AS average_score,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 1)::int AS total_1,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 2)::int AS total_2,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 3)::int AS total_3,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 4)::int AS total_4,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 5)::int AS total_5,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW()), 0)::int as today,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 DAY'), 0)::int as yesterday,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 WEEK'), 0)::int as one_week_ago,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 MONTH'), 0)::int as one_month_ago,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '6 MONTH'), 0)::int as six_montsh_ago
FROM
    data.users_pro_reviews a
WHERE
    a.user_id = $1

How can this query be optimized? Using JOINS? And how should I use window functions in JOINs?


texts: MySQL performance tuning + queries stuck on "Copying to tmp table"

texts:
The latter part of the question's title (queries stuck on "Copying to tmp table") has been addressed many times, and I have spent a fair amount of time researching this. I would appreciate it if you guys could help me come to a conclusion - especially with my particular server set up in mind.

Quick overview of server:
 - Dedicated server with 2 cores and 64 GB RAM
 - Only runs MySQL

The setup is in no way tweaked, so current config is to some extent way off. Hopefully your scolding could provide knowledge.

The web application running on the server is a Magento site with 25.000+ products. The query giving the most headache is the one generating sitemaps.

Currently, the following query has been stuck for a little over an hour on "Copying to tmp table":

  Note: I do not really need input on how to increase performance by
  optimizing this query, I would rather see how much I can shave query
  time off the queries that already exist.

SELECT DISTINCT `e`.`entity_id`, `ur`.`request_path` AS `url`, `stk`.`is_in_stock` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_website` AS `w` ON e.entity_id=w.product_id
LEFT JOIN `core_url_rewrite` AS `ur` ON e.entity_id=ur.product_id AND ur.category_id IS NULL AND ur.store_id='1' AND ur.is_system=1
INNER JOIN `catalog_category_product_index` AS `cat_index` ON e.entity_id=cat_index.product_id AND cat_index.store_id='1' AND cat_index.category_id in ('2', '3', '68', '86', '145', '163', '182', '196', '198', '214', '249', '252', '285', '286', '288', '289', '290', '292', '549') AND cat_index.position!=0
INNER JOIN `cataloginventory_stock_item` AS `stk` ON e.entity_id=stk.product_id AND stk.is_in_stock=1
INNER JOIN `catalog_product_entity_int` AS `t1_visibility` ON e.entity_id=t1_visibility.entity_id AND t1_visibility.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `t2_visibility` ON t1_visibility.entity_id = t2_visibility.entity_id AND t1_visibility.attribute_id = t2_visibility.attribute_id AND t2_visibility.store_id='1'
INNER JOIN `catalog_product_entity_int` AS `t1_status` ON e.entity_id=t1_status.entity_id AND t1_status.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `t2_status` ON t1_status.entity_id = t2_status.entity_id AND t1_status.attribute_id = t2_status.attribute_id AND t2_status.store_id='1' WHERE (w.website_id='1') AND (t1_visibility.attribute_id='102') AND ((IF(t2_visibility.value_id > 0, t2_visibility.value, t1_visibility.value)) IN(3, 2, 4)) AND (t1_status.attribute_id='96') AND ((IF(t2_status.value_id > 0, t2_status.value, t1_status.value)) IN(1))

Relevant config:

Server buffers:

max_connections = 1500;
key_buffer_size = 22G;
innodb_buffer_pool_size = 16G;
innodb_additional_mem_pool_size = 2G;
innodb_log_buffer_size = 400M;
query_cache_size = 64M;

Per thread buffers:

read_buffer_size = 2M;
read_rnd_buffer_size = 16M;
sort_buffer_size = 128M;
thread_stack = 192K;
join_buffer_size = 8M;

  Question: Does any of these variables seem way off to any of you?

The above memory limits will allow me to use 130% more RAM than is physically installed on my system. Obviously, something must change.

According to the MySQLTuner Perl script, I am currently in danger of using:
Total buffers: 40.7G global + 28.2M per thread (1500 max threads)
Maximum possible memory usage: 82.0G (130% of installed RAM)

  Questions: What would give the most performance increase of the above variables, or: will it be more useful to increase the total server buffer limit or the per thread buffer limits?

The two variables that seem to affect "Copying to tmp table" the most are:
 - tmp_table_size
 - max_heap_table_size

Mine are both set to 0.25G

  Question: Any recommendations specifically for those to variables?

There are two suggested fixes that seem to get mentioned more that others:
 - Kill process. Do flush table.
 - Kill process. Do repair / optimize tables.

  Question: Which of the two above suggested solution do you think is the most viable?


texts: sql server , set N prefix as unicode for all queries by default

texts:
Is there any option in ms sql server which use N prefix for my database.
There are many query in my application which non of them use N prefix for nvarchar fields , and now I have to set N for all of them and it takes long time to do.
My collatation is Persian_100 and it need unicode (N prefix) so any advise is appreciated

select * from table1 where field1=N'حمید' ;

I want skip N prefix in my query , but it acts as unicode for all type of queries:


texts: Primary Key choice on table with unique identifier

texts:
I'm reconsidering my choice of Primary Key on a table, and would like some input.

Some context:  We are building a REST API, where clients access "Assets" identified by a GUID.  So, the table for Asset in our database resembles this:

CREATE TABLE [dbo].[Asset](
    [Id] [uniqueidentifier] NOT NULL,
    [TypeId] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [Title] [varchar](1000) NULL,
    [Description] [varchar](max) NULL,
    [Created] [datetime] NOT NULL,
    [Modified] [datetime] NOT NULL,
    [PublisherFields] [xml] NULL,
    [StatusId] [int] NOT NULL,
 CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

We are experiencing a few problems with this structure, notably that the clustered index on the table becomes fragmented very quickly, and we have now reached the point where we are no longer able to rebuild it (because of Azure limitations on query time limits).

Further research suggests that using a uniqueidentifier as a clustered index is not always a good choice, because of the way that newid() doesn't generate IDs in sequence (unless you use newsequentialid(), but Azure doesn't allow this either).

The simple solution seems to be to introduce a dummy column and add the clustered index to that.  Design guidelines say that good clustered indexes should be sequential and non-changing, so the simplest answer to that seems to be an Identity column.  This would make my table into this:

CREATE TABLE [dbo].[Asset](
        [Id] [bigint] IDENTITY(1,1), <---- NEW
        [AssetId] [uniqueidentifier] NOT NULL, <---- Renamed
        [TypeId] [int] NOT NULL,
        [Date] [datetime] NOT NULL,
        [Title] [varchar](1000) NULL,
        [Description] [varchar](max) NULL,
        [Created] [datetime] NOT NULL,
        [Modified] [datetime] NOT NULL,
        [PublisherFields] [xml] NULL,
        [StatusId] [int] NOT NULL,
     CONSTRAINT [PK_Asset] PRIMARY KEY NONCLUSTERED <--- Now NonClustered
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    )

The clustered index would also be added to the ID column.  Does this seems a sensibible alternative?  I might question the sense in having an IDENTITY column on the table that is not a primary key, but my reasoning is that an IDENTITY column is used to obtain the auto-incrementing nature, which suits the clustered index.  It might also be confusing...when someone references an "AssetID", which column are they talking about?  The [AssetId] column, or the Asset table's [ID] column?

Suggestions and alternatives welcomed.


texts:
I have the following data:Trollhättan

If I print out and setting up the header to utf-8, then this is the output in the browser.
If I'm not setting up the utf-8 in the header, TrollhÃ¤ttan. However,when I'm storing the data in the database and checking through phpmyadmin, I am getting the following string : TrollhÃƒÂ¤ttan.
When I am setting up the header to utf-8, I'm getting this string : TrollhÃ¤ttan beside this Trollhättan.

Since the table is latin1_swedish_ci, do I have to use in the table UTF-8?


texts:
Let's say my relation is like so

and I wanted to sort by rank, but A's and B's first. So my relation will look like

At first I tried to just have two relations, where I order both of them by rank, then union the two relations, but they just end up getting jumbled together. I've also tried using cases, but I don't understand how it works. I kept getting errors about mixing characters with integers.

Edit: Forgot to mention that the first relation above is what I end up with after a somewhat complex number of views. For example, I created two views, one which only has A and B classes, then I ordered that by rank. Second view just C classes, ordered that by rank. Then I unioned them, but they just get mixed.


texts: How to express inclusive and exclusive relations in relational algebra and relational calculus?

texts:
Let be the following simplified database sketch:

R1(P,S),R2(S,T) and R3(T,S) with P= people S = Sport and T= Sports Center

How to find in relational algebra and relational calculus which couples such that each one has at least a sport that the other do and and another that the other do not.

I tried:

Πp1,p2(σs1!=s2, p1!=p2(R1))P→P1,P→P2, S→S1, S→S2

But I know this is wrong, It doesn't express the second condition at least and I'm not sure on how to express variables renaming.

Can you help me express such inclusive and exclusive relations?


texts: How do I select the value with SERIAL data type in postgres

texts:
I want to know how to select values from a column that has SERIAL data type.

I already try to do that but the output is like this 'Resource id #3', and not just '3'

I use SERIAL so I dont have to input ID like when I use integer


texts:
I am going through the below link and am really interested in watching Paul White's

Understanding the Optimizer and Interpreting Execution Plans

http://www.pass.org/summit/2013/Sessions/Schedule.aspx

But I couldn't find this session anywhere in the pass learning/recording session.

Can someone let me know how I can get this?


texts: Unable to start mysqld service after enabling log_bin

texts:
Hi I am unable to start mysqld service after I make changes in my.cnf to enable log-bin.
After making changes the file looks like below:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

#This is what I added to enable log-bin

log-bin=/var/log/mysql/
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=row
socket=mysql.sock

#That was what I added to enable log-bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I am using:

[ec2-user@pip my.cnf.d]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)

Following is the ownership and permissions:

d]$ ls -ld /var/log/mysql
drwxrwxrwx. 2 mysql mysql 23 Sep  8 09:01 /var/log/mysql

Following is the error that I get:

[ec2-user@i.d]$ sudo service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

MySQL version is:

Server version: 5.7.19 MySQL Community Server (GPL)

Please let me know if any other detail is required


texts: How to install Barman with pg_receivewal and pg_basebackup without install PostgreSQL server?

texts:
Follow the official document, it's only necessary to install barman by yum or apt straightforward:

http://docs.pgbarman.org/release/2.10/#installation

Unnecessary to install PostgreSQL server on the barman server.

But in order to use barman, these 2 libraries are necessary: pg_receivewal and pg_basebackup. They are in the path /usr/pgsql-10/bin/ if install PostgreSQL server.

How to make them out without install PostgreSQL server? Is there anyway?


texts: Full-text: lots of FT_MASTER_MERGE waits in SUSPENDED state after multiple full-text indexes created (server hangs)

texts:
We did a test on SQL Server 2014 when we had 10 databases, 100 different schemas in each database, 10 of small (~50 rows) tables in each schema (so 10K tables in total) and we created full-text indexes on all these tables in all these databases simultaneously.

In several minutes we found that SQL Server stopped to accept any connections (except ADMIN:. connection). If we restart the server, we can connect, but in some time it hangs again. After some investigation we found that it is caused by consuming all working threads, and dm_os_tasks and dm_os_waiting_tasks showed us that there is a lot FT_MASTER_MERGE waits in SUSPENDED state. We googled that "Full-text is waiting on master merge operation", but found no more actual info about it.

We tried different full-text catalog configurations: one catalog per DB, one catalog per schema, one catalog per index. Anyway the server hangs with all these suspended tasks.

What's the root cause of the waits, how this can be fixed/mitigated?

And what is the recommended way to enable full-text on such a large amount of tables?


texts: Postgres automatic insert time stamp with time zone (india)

texts: Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we use zone).pg version 9.1. and how to use to desired time format ?


texts: recover a single mysql database on a busy master-slave replicated system

texts:
Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system.

I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave replicated configuration. A full dump is taken of the read-only slave daily, and there are incremental SQL dumps available, with these backups off-site and the replication status is monitored.

Edit: Tables are a mixtures of InnoDB and myISAM, hence engine specific solutions are not available.

So given a complete failure of the master server, I can break the replication and promote the slave server, I also have the option of rebuilding a new server and configuring from the offside FULL backup, and then apply the differentials taken hourly from the slave.

However I am concerned how to deal with partial failure, or failure of a single database. I can think of 2 scenarios that are quite likely;

database 7 (for example) becomes corrupted, continues to serve some requests until someone notices that it is broken, or alerts from the log files...
Some query like drop database, drop table, "update where..." type query borks a single database, or some subset therein.

At the moment I have a bunch of FULL dumps as FULL-$DATE-all-databases.sql.gz files , and differentials that can be applied to the FULL dumps as DIFF-$DATE-all-databases.sql.gz

To restore database 7 to some point-in-time would require a grep through the FULL and DIFF files, and manual application of that sql.

How should I proceed in order to make it possible to be able to recover to one of the previous DIFF dumps to the master database?

DO I need to backup to individual database files, ie

mysqldump --databases "database1" | gzip > database1.sql.gz
mysqldump --databases "database2" | gzip > database2.sql.gz
mysqldump --databases "database3" | gzip > database3.sql.gz

rather than..

mysqldump --master-data --lock--all-databases --all-databases | gzip > all-databases.sql.gz

If I go for separate mysqldump files, what happens to the master data binary log, and should I even be setting --master-data for the master server recovery dumps?


texts: Why is SELECT USER_NAME() returning my windows credentials and not the database user mapped to a windows security group that I am in?

texts: I am trying to implement Row Level Security and have mapped an AD Security Group to a user in my test Database. When trying to select data from a table that I have granted access to I am not getting my intended result. I have added select USER_NAME() to see what is being checked on my predicate function and I get the Windows login and not the database user.


texts:
Here i have table with indexes and more than 700000 entries.

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `cat_id` int(11) NOT NULL,
  `user_id` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `primary` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_unique` (`id`),
  KEY `user_id_index` (`user_id`),
  KEY `created_index` (`created`),
  KEY `cat_id_index` (`cat_id`),
  KEY `updated_index` (`updated`),
  KEY `visible_index` (`visible`),
  KEY `catid_visible_index` (`cat_id`,`visible`),
  KEY `primary_index` (`primary`),
  KEY `topics_forumid_visible_sticky` (`cat_id`,`visible`,`primary`),
  FULLTEXT KEY `topics_title_fulltext` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I need to do 2 ORDER BY statement. If i use any order by - query runs too long

 SELECT
    `t1`.*
FROM
    `t1`
WHERE
    `t1`.`cat_id` = 24
        AND `t1`.`visible` = 1
ORDER BY `t1`.`primary` DESC , `t1`.`updated` DESC
LIMIT 20

EXPLAIN ordered gives
Extra 'Using index condition; Using where; Using filesort'
Key 'catid_index'

EXPLAIN unordered gives
Key 'catid_index,visible_index'
Extra 'Using intersect(catid_index,visible_index); Using where'


texts:
My database is running on SQL Server 2012 with 32 core processors. As per sp_blitz (by Brent Ozar) we need to enable trace flag 8048.
Should we enable this flag on 2012, or it is recommended only for 2014 onwards?


texts: I have a SQL Server 2012 database which contains a number of views which have become invalid. The underlying tables the views referenced were migrated to another schema for archiving. What's the best way to find the now invalid views and drop them


texts:
I'm trying to come up with a database schema which is going to be used for a social network similar to that of Facebook.
Now for posts I have two schemas in mind which are as follows:

Single-Table Design
In this approach there will only be a singular table for all post types (text, photo, video, etc.).

The structure could be similar to the following:

|-----------------------------------------------|
|  id   |  user_id  |  type  |  content  | ...  |
|-----------------------------------------------|

The content column will contain the json_encoded data for all media types.

Multi-Table Design
This approach would consider different tables for each media type, which will be one main table that contains the main post record and each media oriented table will contains respective media data.

Posts table:

|-----------------------------------------------|
|  id   |  user_id  |  type  |  content  | ...  |
|-----------------------------------------------|

Photos Table:

|------------------------------------------------------------|
|  id   |  post_id  |  user_id  |  url  |  thumb_url  | ...  |
|------------------------------------------------------------|

Videos table:

|-----------------------------------------------------------------|
|  id   |  post_id  |  user_id  |  url  |  screenshot_url  | ...  |
|-----------------------------------------------------------------|

and so on...

I would be delighted if you could guide me of which approach is better or any other schema that would serve the purpose in better condition, performance wise.


texts: Restore a SQL Server bacpac file in Linux

texts:
I'm working to move an Azure database to an open source DB under Linux.  I've received a .bacpac file and, to start with, I thought I'd try to install MS/SQL in Linux and restore the .bacpac file so I could peruse the database.  But a restore command gives me:

$ /opt/mssql/bin/sqlpackage /a:Import /tsn:tcp:localhost /tdn:dbName /tu:SA /tp:thePassword /sf:/home/user/Downloads/dbname.bacpac
An unexpected failure occurred: Data plan execution failed with message One or more errors occurred..

Unhandled Exception: Microsoft.SqlServer.Dac.Data.DataException: Data plan execution failed with message One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.UnauthorizedAccessException: Access to the path 'C:\Users\Client\Temp\tmp4D32.tmp' is denied.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at Microsoft.Data.Tools.Schema.Sql.Dac.Data.TemporaryStorageProvider.CreateTemporaryFile(Int64 maxSize)

Obviously the restore is assuming the existence of Windows paths.  Is there a way to override this that I've missed?  I'm using:

1> select @@version
2> go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64)
    May 10 2017 12:21:23
    Copyright (C) 2017 Microsoft Corporation. All rights reserved.
    Developer Edition (64-bit) on Linux (Linux Mint 18.1)

(1 rows affected)

Any pointers?  My MS/SQL experience dates to a time not too long after SQL server came from Sybase so right away I'm a bit behind.  Thanks for any help.

Edit

I'll not answer my own question as this solution is incomplete but I did figure out how to limp along with this as much as possible.  Part of what I got from the current maintainer was a schema create script.  Using sqlcmd I was able to create the schema.  The .bacpac file is just a zip file and, when you extract it, you have a "Data" directory with a separate directory per table.  There is also a "model.xml" file that is basically the schema - this must be how the sqlpackage import command can create the database tables but I used a .sql file for mine.

For each directory there is one or more BCP files - ah finally something I remember from my Sybase days.  Because I had very few tables I was able to manually use bcp to load each table in succession.

This isn't the best answer as it requires more than just the .bacpac file to work but it did work for me.  If MSFT really wants people to use MS/SQL server on Linux they've got some bugs to fix.


texts: MySQL cluster fast inserts but slow selects

texts:
I have a MySQL cluster with 3 Data Nodes and one SQL node. Currently I have 1 million 1kb records. The inserts are very fast, takes only a few ms. But the selects are very slow >20s.

All machines are m1.large EC2 instances (8GB RAM).

Here is the config.ini:

[NDB_MGMD]
NodeId=49
HostName=host1
DataDir=/var/lib/mysql-mgmd-data
Portnumber=1186

[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=1536M
IndexMemory=2560M
StringMemory=25
MaxNoOfTables=4096
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512
MaxNoOfAttributes=24576
MaxNoOfTriggers=14336

FragmentLogFileSize=256M
InitFragmentLogFiles=SPARSE
NoOfFragmentLogFiles=16
RedoBuffer=48M
MaxNoOfConcurrentOperations=100000
MaxNoOfConcurrentTransactions=16384
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=100
TimeBetweenEpochsTimeout=32000

DiskCheckpointSpeedInRestart=100M
DiskCheckpointSpeed=10M
TimeBetweenLocalCheckpoints=20

HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000

MemReportFrequency=30
BackupReportFrequency=10
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15

SharedGlobalMemory=384M
DiskPageBufferMemory=1G
BatchSizePerLocalScan=512
[NDBD]
NodeId=1
HostName=host2
DataDir=/mnt/mysql-cluster/1/
[NDBD]
NodeId=2
HostName=host3
DataDir=/mnt/mysql-cluster/2/
[NDBD]
NodeId=3
HostName=host4
DataDir=/mnt/mysql-cluster/3/
[MYSQLD DEFAULT]
[MYSQLD]
NodeId=53
HostName=host1

my.cnf

[mysqld]
ndbcluster
ndb-nodeid=53
ndb-connectstring=host1,

Insert:

INSERT INTO mytab(mykey, a, b, c, d, e, f, g, h, i,j)VALUES (1,1,2,3,4,5,6,7,8,9,0);

Select:

SELECT * FROM mytab WHERE mykey = 12345;

Create table statement:

 mytab | CREATE TABLE `mytab` (
  `mykey` varchar(32) NOT NULL,
  `a` varchar(100) DEFAULT NULL,
  `b` varchar(100) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL,
  `d` varchar(100) DEFAULT NULL,
  `e` varchar(100) DEFAULT NULL,
  `f` varchar(100) DEFAULT NULL,
  `g` varchar(100) DEFAULT NULL,
  `h` varchar(100) DEFAULT NULL,
  `i` varchar(100) DEFAULT NULL,
  `j` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`mykey`)
) /*!50100 TABLESPACE mytab_space STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 MAX_ROWS=1000000000
/*!50100 PARTITION BY KEY (mykey) */ |

Has someone an idea why it is so slow?
Has someone experience with MySQL-cluster and EC2 m1.large instances?


texts: Use mysqldump to backup table with a new table name

texts:
Is there a way to back up a table using mysqldump so the table that is being backed up can have a new name in the sql script created by mysqldump.

So that If I back up the table 'test_table' like so.

mysqldump -u user -p db_name test_table > backup.sql

and I want 'test_table' to now be called 'test_table_backup' in the backup.sql that is created.

CREATE TABLE `test_table_backup` (

...

);

I know i can just dump the file then craft some way to do a search and replace on the table name but I was hoping there was a safer way using mysqldump itself.


texts: Is it possible to do a ALL SERVER DML Trigger?

texts:
My department works on converting a client's database when a client switches from another company to us. We have a RDP session they can connect to and see our software with their data. Once they approve the conversion, we copy the data from our server and set it up on site for them.

During the conversion approval phase it would make things a lot simpler for us if we could have some AFTER and INSTEAD OF triggers running on all databases attached to the server (our department is a different department than the one that develops the software and they will not make a version for the testing site that does the stuff for us built in for us).

I see two options:

Create a DML trigger that either performs the action on all databases attached to the server, but when the database is moved to the live server the action no longer occurs because it is run on the server not the database.
Have a DML trigger that deletes itself if it detects that it is no longer attached to the test server and is in a live environment.

From what I read I don't think the first option is possible, but I wanted to check and see. I think I know enough to do the 2nd option, but any examples of how to implement it would be very helpful.


texts: Is it a problem to define a non-UTF8 collation in an NVARCHAR field?

texts:
I am working on an application I need to make cross-database.

One of the things I stumbled on is that SQL Server by default has a case-insensitive collation defined, which makes queries on NVARCHAR/VARCHAR fields match on things that the application doesn't expect it to match on (i.e. case differences).

I tried to change the collation of the database to SQL_Latin1_General_CP1_CS_AS (as I could see that the current one was SQL_Latin1_General_CP1_CI_AS). This caused a different failure because the field names were case sensitive (queries where a field was sometimes lower-case would fail when the original table definitions were all upper-case). Since it's a large application, this is not a change that can be adopted.

I have now adapted the creation scripts to add COLLATE SQL_Latin1_General_CP1_CS_AS to the end of all NVARCHAR and VARCHAR fields of tables. The behavior is now back to what was expected (the queries are case-sensitive). So now, the question...

Will this collation change cause any other problems I cannot see? I can see that there are Unicode (_UTF8) collations. Should I add the _UTF8 prefix for the NVARCHAR field or does it make no difference to simply add the normal SQL_Latin1_General_CP1_CS_AS collation to all VARCHAR and NVARCHAR fields equally?

I'm interested in the behavior of SQL Server 2012 and onwards.


texts:
This is 1/4 of an overall assignment and I need help with this part.
The assignment question:

Create a trigger on the Rental table that will check when a new rental is to be inserted, that the member (MemberId) that is renting a book:
a.    Does not have more than 2 books rented out: the books that are rented out are identified by having ReturnDate as NULL.
b.    That the member does not owe more than 50 dollars to the library (the BalanceOwing attribute of the Member table).

What I have done so far :
CREATE TRIGGER tr_tblRental_ForInsert
ON dbo.Rental
FOR INSERT
AS
BEGIN
    declare @MemberId int
    select  @MemberId int = MemberId from inserted

I would appreciate tips on how to go about this.


texts: What is the correct way to declare and use MarisDB user defined variables?

texts:
I'm using MariaDB version 10.3.28-MariaDB-cll-lve
This site https://mariadb.com/kb/en/user-defined-variables/ says

User-defined variables names must be preceded by a single at character (@)

That's what I usually do but recently I was rushing, forgot the '@ and wrote the following function which seems to work fine without the variables having '@' at the front.
So is having a '@' in front of a user defined variable a firm requirement or not?
If it is, why does my function work correctly without it?
DELIMITER $$
CREATE  FUNCTION get_next_redacted_member_id() RETURNS INT(11)
BEGIN
       DECLARE result INT SIGNED;
       DECLARE lastid INT;
       INSERT INTO redacted_member_id ( date_used) VALUES ( NOW() );
       SET lastid = (SELECT LAST_INSERT_ID() );
       SET result =  -1 * lastid ;
       RETURN result;
END$$
DELIMITER ;


texts: Does creating an empty table in MySql have any effect(downtime) on production?

texts:
Disclaimer: I am, in all probability, paranoid about pushing a patch on a production server.

The patch alters the schema, by creating a new table (which is empty), and changes some stored procedures which do not run instantly when the patch is pushed. The altered stored procedure will not act upon already stored data but only new data that reaches the database.

Does creating an empty table result in any significant amount of downtime or not?


texts: How to create and start a PDB for RCU in Oracle 12c

texts:
So I've made a fresh install on a virtual machine based on OEL 6.5 x64.
All my installs are in /opt/apps

I installed a Oracle Database 12c, with the Desktop Version selected (not server), UNICODE encoding, Enterprise Edition. If I get it right, from this point I only have a CDB named orcl that is running.

Now I want to run the Repository Creation Utility to initialize the database for OIM and OAM but when I do so it tells me it won't run on a CDB.
So I checked the Oracle documentation and created a PDB from the default seed with :

CREATE PLUGGABLE DATABASE PDBFMW ADMIN USER fmw IDENTIFIED BY Passw0rd
FILE_NAME_CONVERT=(
'/opt/apps/oradata/orcl/pdbseed/system01.dbf',
'/opt/apps/oradata/orcl/pdbfmw/system01.dbf',
'/opt/apps/oradata/orcl/pdbseed/sysaux01.dbf',
'/opt/apps/oradata/orcl/pdbfmw/sysaux01.dbf',
'/opt/apps/oradata/orcl/pdbseed/pdbseed_temp01.dbf',
'/opt/apps/oradata/orcl/pdbfmw/pdbfmw_temp01.dbf')
STORAGE UNLIMITED;

ALTER PLUGGABLE DATABASE PDBFMW OPEN READ WRITE;
GRANT ALL PRIVILEGES TO fmw WITH ADMIN OPTION;
GRANT SYS TO fmw;

So far my PDB is not started and won't start with the orcl instance so I made this trigger :

CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE';
END open_pdbs;
\

Which, I was told, was supposed to automatically start my PDBFMW along with ORCL.
 So I checked it out with :

SELECT name, open_mode from v$pdbs;

Which tells me that it's correctly opened and in READ WRITE mode.

But when I try to connect with this :

export ORACLE_SID=pdbfmw
sqlplus / as sysdba

It says connected to an idle instance.


texts: Procedure to find a specific EXPRESSION in all tables in a specific schema in ORACLE

texts:
Hope you're doing well.
Since I'm new to Oracle database and PL/SQL it's a bit difficult for me to understand complicated queries and now I'm trying to figure out what this procedure exactly does.I think it tries to find a specific expression but I do not understand the way  it tries to do that
This is the procedure body:

    create or replace procedure find_expr is
      a number;
      begin
        for rec in (select 'select count(*) from ' || table_name || ' where ' ||
                            column_name || ' like ''%05510%''' col_name
                    from user_tab_columns t) loop
    EXECUTE IMMEDIATE rec.col_name
      into a;

    if a > 0 then
      insert into find_expr_log values (sysdate, substr(rec.col_name, 22, 50));
      commit;
    end if;

  end loop;
exception
  when others then
    null;
end;


texts:
I have a query that is running against a table with 50 million rows.  The phone number is stored as a CHAR(10) and an ETL process is changing the phone number if it has certain values.  We are seeing poor performance of this query and was wondering if anyone has any suggestions on improving performance.  No index currently exists with the phone number.

UPDATE Table SET phone='' WHERE ( phone IS NULL
              OR phone = ''
              OR phone = '0000000000'
              OR phone = '0'
              OR phone like '111%'
              OR phone like '222%'
              OR phone like '333%'
              OR phone like '444%'
              OR phone like '555%'
              OR phone like '666%'
              OR phone like '777%'
              OR phone like '888%'
              OR phone like '999%'
              OR phone like '800%'
              OR phone like '900%'
            )


texts:
I work in a company that is deeply ingrained in .Net programming.  I am responsible for managing a couple SQL Servers, with about a dozen databases in total.  Our programming manager recently demonstrated the Visual Studio Database Projects for my team and I am looking to know if anyone is using it and if so the pros/cons.  Right now I have to manually promote changes from test to production via SSMS which VS would eliminate.  I also have a large collection of SQL scripts on our SAN that are fairly unorganized and it would be nice to include them in a common solution.

My apologies beforehand I know this is probably not the best place for this question since I am looking for opinions rather than a technical answer...


texts:
I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL.  Coming from MS SQL Server, the ability to do database alterations in a transaction locally (that was then rolled back) was an important part of my workflow.  For continuous integration, the rollback was used if the migration hiccuped for any reason, so that at least we did not leave the database in a half-migrated state.

How do people solve these two problems when using MySQL with migrations and continuous integration?


texts: mysqlimport imports NULL values unwantedly as int 0

texts:
I would like to understand how to create a decent back-up file by using mysql and mysqlimport. I know that I can use mysqldump and mysql, but I'm learning to use the combination: mysql and mysqlimport.

I'm on mysql5.7.21 in Windows 10.
I have test table: aircrafts that looks like this:

I would like to export this table to a textfile trough the mysql client tool.
So I do this:

mysql -h localhost -u root -D julian -p -e "SELECT * FROM `aircrafts` " > aircrafts.txt

This creates the back-up file, and content of it is:

id  countryFactoryID    name    type    price
1   1   Cessna 172 Skyhawk  GA  60000
2   43  Diamond DA40    GA  NULL
3   1   Cirrus SR20 GA  NULL
4   1   Piper PA-28 Cherokee    GA  NULL
5   1   Beechcraft A23 Musketeer    GA  NULL
6   1   Grumman American AA-5 Traveller GA  NULL

What I noticed about the back-up file is that it uses NULL instead of \N.
But that's ok I guess.

Next, I import the back-up file like this:

mysqlimport --local -l -v --ignore-lines=1 -p -u root myDB aircrafts.txt

The output in CLI is:

Connecting to localhost
Selecting database myDB
Locking tables for write
Loading data from LOCAL file: aircrafts.txt into aircrafts
myDB.aircrafts: Records: 6  Deleted: 0  Skipped: 0  Warnings: 6
Disconnecting from localhost

Ok, so far so good.

When I look in phpmyadmin, then I see this:

As you can see, the price is 0, but I would like to have a NULL-value, like in the first image. It's strange because my default value for column price is NULL.

What am I doing wrong?


texts: sql server: get all the databases in which a login is being used

texts:
I'm creating a few migration scripts to generate a sql server database from scratch.

In my '0005-create_login.up.sql' script I have something like:

CREATE LOGIN [mylogin] WITH PASSWORD=N'xxx=',
GO

And in my '0005-create_login.down.sql'

DROP LOGIN [appws]
GO

Now I'd like to drop the login only if it is not being used in another database. I had a look at sp_helplogins code with:

sp_helptext 'sp_helplogins'

but it's really complex. I wonder if there's and easier way to get a login with the databases it is being used.


texts: How to design tables for players in a tournament?

texts:
I need help with my database table design.

I have group which contains a list of players. In every group each player plays games with other players. Game has also some result for example 1:0 and there can be overtime too.

I already created some tables but I dont think this is a good solution. There is problem when I create java classes from these tables.

PlayerResult has lists of games. Game has opponent which is again PlayerResult which have listOfGame again etc... I dont know how to solve this cycle because every playerResult should know all their game to count score and point. Please help me improve the table design.

CREATE TABLE IF NOT EXISTS `GROUP` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `NAME` (`NAME`,`GROUP_ID`),
  KEY `TOURNAMENT_ID` (`TOURNAMENT_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `PLAYER_RESULT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `POINTS` int(11) DEFAULT NULL,
  `RANK` int(11) DEFAULT NULL,
  `GROUP_ID` int(11) NOT NULL,
  `PLAYER_ID` int(11) NOT NULL,
  `SCORE` varchar(10) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `PLAYER_ID` (`PLAYER_ID`),
  KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `RESULT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `LEFT_SIDE` int(11) DEFAULT NULL,
  `RIGHT_SIDE` int(11) DEFAULT NULL,
  `OVERTIME` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

CREATE TABLE IF NOT EXISTS `GAME` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PLAYER_RESULT_ID` int(11) NOT NULL,
  `OPPONENT_ID` int(11) NOT NULL,
  `RESULT_ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `VYSLEDKYHRACA_HRACA_ID` (`PLAYER_RESULT_ID`),
  KEY `RESULT_ID` (`RESULT_ID`),
  KEY `OPPONENT_ID` (`OPPONENT_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


texts: Enabling Fragmentation Manager in SQL Sentry causes slowness on SQL instance ? does it take additional resources ?


texts: How to move a fairly large tsql table from one server to another server?

texts:
I have been working on thinking of ways to move a 40gb table from one SQL Server box to another SQL Server box.  They are at separate companies without an intranet linkage and obviously without linked servers.

Initially, I thought the quickest way to do this was to pull the nightly backup file and get this over to the other server.  Create a database from the backup and then transfer my table from that database to the existing.

After thinking about this though, I can't have all the data in that particular database at the other company due to security reasons.  Whereas the table I am after is computational and unrelated to any type of company specific competitive edge.

Either way, what I am looking for is how I can export a table of that size quickly and efficiently and get it into a database that is in no way connected to the existing database.

This is my first time researching something along this line, and I am pretty sure I don't want to script the table.  Initially, I thought the bak file was my best bet, but can't do the entirety of the db.


texts: Using Python to insert JSON into PostgreSQL

texts:
I have the following table:

create table json_table (
    p_id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    p_attribute json,
    quote_content text
)

Now I basically want to load a json object with the help of a python script and let the python script insert the json into the table. I have achieved inserting a JSON via psql, but its not really inserting a JSON-File, it's more of inserting a string equivalent to a JSON file and PostgreSQL just treats it as json.

What I've done with psql to achieve inserting JSON-File:

Reading the file and loading the contents into a variable

\set content type C:\PATH\data.json

Inserting the JSON using json_populate_recordset() and predefined variable 'content':

insert into json_table select * from json_populate_recordset(NULL:: json_table, :'content');

This works well but I want my python script to do the same.
In the following Code, the connection is already established:

connection = psycopg2.connect(connection_string)
cursor = connection.cursor()
cursor.execute("set search_path to public")

with open('data.json') as file:
        data = json.load(file)

query_sql = """
insert into json_table select * from
json_populate_recordset(NULL::json_table, '{}');
""".format(data)

cursor.execute(query_sql)

I get the following error:

Traceback (most recent call last):
  File "C:/PATH", line 24, in <module>
    main()
  File "C:/PATH", line 20, in main
    cursor.execute(query_sql)
psycopg2.errors.SyntaxError: syntax error at or near "p_id"
LINE 3:     json_populate_recordset(NULL::json_table, '[{'p_id': 1, ...

If I paste the JSON content in pgAdmin4 and use the string inside json_populate_recordset() it works. I assume im handling the JSON file wrong...

My data.json looks like this:

[
    {
        "p_id": 1,
        "first_name": "Jane",
        "last_name": "Doe",
        "p_attribute": {
            "age": "37",
            "hair_color": "blue",
            "profession": "example",
            "favourite_quote": "I am the classic example"
        },
        "quote_content": "'am':2 'classic':4 'example':5 'i':1 'the':3"
    },
    {
        "p_id": 2,
        "first_name": "Gordon",
        "last_name": "Ramsay",
        "p_attribute": {
            "age": "53",
            "hair_color": "blonde",
            "profession": "chef",
            "favourite_quote": "Where is the lamb sauce?!"
        },
        "quote_content": "'is':2 'lamb':4 'sauce':5 'the':3 'where':1"
    }
]


texts: What is the most efficient way to make a Locality Entity (Town, City, Village etc) with a Country Entity?

texts:
I am new to Database.

I have a database with a UserProfile Entity. I need to add a Country and Locality(City, Town, Village) to the UserProfile.

What is the most efficient way of doing so?

This is what I have:

Locality

PK UN LocalityID
UN LocalityName

Country

PK UN CountryID
UN CountryName

UserProfile

Username...Password...etc
FK    CountryID
FK    LocalityID


texts: Is it possible to set order in which inherited tables a scanned in postgresql?

texts:
I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125.

When I run explain select * from measurement where city_id = 125 limit 1; I get

Limit  (cost=0.00..3.39 rows=1 width=12)
  ->  Append  (cost=0.00..308.25 rows=91 width=12)
     ->  Seq Scan on measurement  (cost=0.00..0.00 rows=1 width=12)
           Filter: (city_id = 125)
     ->  Seq Scan on measurement_y2006m01  (cost=0.00..34.25 rows=10 width=12)
           Filter: (city_id = 125)
     ->  Seq Scan on measurement_y2006m02  (cost=0.00..34.25 rows=10 width=12)
           Filter: (city_id = 125)
     ->  Seq Scan on measurement_y2006m03  (cost=0.00..34.25 rows=10 width=12)
           Filter: (city_id = 125)

What is the default order in which those Seq Scan actions are performed on inherited tables? And can I explicitly set it so that the latest tables are scanned first as I am expecting to select recently added rows more often?


texts: Passing multiple value for a parameter in my sql

texts:
I have written the query below:

SET @City = 'London,Lyon,Kln';
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@City, ',',@r:=@r+1),',',-1) City
FROM (SELECT @r:=0) x,
(SELECT 'x' xx
    UNION
    SELECT 'v' xx
    UNION
    SELECT 'z' xx) z;

Which returns Cities in table format & now i want to pass it as a parameter to a query like :

SET @City = 'London,Lyon,Kln';
SELECT *
FROM Invoices
WHERE City IN (
    SELECT SUBSTRING_INDEX(
        SUBSTRING_INDEX( @City, ',',@r:=@r+1),',',-1
    ) City
    FROM (SELECT @r:=0) x,
    (SELECT 'x' xx
        UNION
        SELECT 'v' xx
        UNION
        SELECT 'z' xx) z
)

But when I execute this query it returns the records for 'Kln' city only, ie. for last city mentioned in @City variable. So please tell me how to get the records for all cities of @City variable.


texts: Do I really need Re-Build Index Task in SQL Server all the Time?

texts:
I have created Re-build Index task which will run every Sunday night @11:00 PM.

I do not have Re-Organize Maintenance Task created

Do I really need Re-build Index Task or I need it only when the Fragmentation Level is above 30% ?
Do I need Re-Organize Index Task when I have Re-build Index Task ?

My Major concern is, we have the Databases in Full Backup Recovery Model and the T-Log Auto-Growth setting is in '%' .

This is eating up all my T-log drive space and creating mess for SQL Server Functionality .

Please advice ?